Excel Berguna untuk mengekstrak baris atau lajur dari array

Bertemu Excel baru mengambil fungsi yang boleh mendapatkan bilangan baris atau lajur yang ditentukan dari julat atau array dalam trice.

Apabila bekerja dengan array data yang besar, anda kadang -kadang perlu mengekstrak bahagian yang lebih kecil untuk pemeriksaan yang lebih dekat. Dengan fungsi array dinamik baru yang diperkenalkan di Excel 365, ia akan berjalan -jalan di taman untuk anda. Tentukan berapa banyak baris dan lajur yang ingin anda ambil dan tekan kekunci Enter :)

• Excel mengambil sintaks fungsi
• Cara menggunakan berfungsi dalam excel
• Ambil contoh formula
  • Ekstrak baris dari julat atau array
  • Ambil lajur dari pelbagai atau julat
  • Ekstrak sebilangan baris dan lajur
  • Dapatkan baris N terakhir
  • Kembalikan lajur N lepas
• Ambil baris / lajur dari pelbagai julat
• Ambil fungsi alternatif
• Excel mengambil fungsi tidak berfungsi

Berfungsi dalam Excel

Excel mengambil fungsi mengekstrak bilangan baris dan/atau lajur yang ditentukan dari array atau julat.

Sintaks adalah seperti berikut:

Ambil (array, baris, [lajur])

Di mana:

Array (diperlukan) - Sumber atau julat sumber.

Baris (Pilihan) - Bilangan baris untuk kembali. Nilai positif mengambil baris dari permulaan array dan nilai negatif dari akhir array. Jika ditinggalkan, lajur mesti ditetapkan.

Lajur (Pilihan) - Bilangan lajur untuk kembali. Integer positif mengambil lajur dari permulaan array dan integer negatif dari akhir array. Jika ditinggalkan, baris mesti ditakrifkan.

Inilah cara fungsi mengambil seperti:

Petua:

• Untuk mengembalikan baris yang tidak bersebelahan dari julat, gunakan fungsi Chooserows.
• Untuk menarik lajur yang tidak bersebelahan, gunakan fungsi choosecols.
• Untuk mendapatkan sebahagian daripada array dengan mengeluarkan bilangan baris atau lajur yang diberikan, memanfaatkan fungsi drop.

Ambil ketersediaan fungsi

Fungsi mengambil hanya disokong dalam Excel untuk Microsoft 365 (Windows dan Mac) dan Excel untuk Web.

Dalam versi Excel terdahulu, anda boleh menggunakan formula offset sebagai penyelesaian alternatif.

Cara menggunakan berfungsi dalam excel

Untuk menyelaraskan jangkaan dan realiti apabila menggunakan fungsi mengambil dalam lembaran kerja anda, perhatikan perkara -perkara berikut:

1. Hujah array boleh menjadi pelbagai sel atau pelbagai nilai yang dikembalikan oleh formula lain.
2. Argumen baris dan lajur boleh menjadi bilangan bulat positif atau negatif. Nombor positif mengambil subset data dari permulaan array; Nombor negatif - dari akhir.
3. Argumen baris dan lajur adalah pilihan, tetapi sekurang -kurangnya salah satu daripada mereka harus ditetapkan dalam formula. Yang ditinggalkan satu kegagalan kepada jumlah baris atau lajur dalam array.
4. Jika baris atau nilai lajur lebih besar daripada terdapat baris atau lajur dalam array sumber, semua baris / lajur dikembalikan.
5. Ambil adalah fungsi array dinamik. Anda memasuki formula hanya dalam satu sel dan ia secara automatik menumpahkan ke dalam sel -sel jiran yang diperlukan.

Excel mengambil contoh formula

Sekarang anda mempunyai pemahaman umum tentang bagaimana fungsi mengambil, mari kita lihat beberapa contoh praktikal untuk menggambarkan nilai sebenar.

Ekstrak baris dari julat atau array

Untuk mengembalikan bilangan baris bersebelahan dari permulaan array atau julat 2D, membekalkan nombor positif untuk hujah baris .

Sebagai contoh, untuk mengambil 4 baris pertama dari julat A3: C14, formula adalah:

=TAKE(A3:C14, 4)

Formula mendarat di sel E3 dan tumpahan menjadi empat baris dan banyak lajur seperti yang terdapat dalam julat sumber.

Ambil lajur dari pelbagai atau julat

Untuk mendapatkan beberapa lajur bersebelahan dari permulaan array atau julat 2D, berikan nombor positif untuk hujah lajur .

Sebagai contoh, untuk menarik 2 lajur pertama dari julat A3: C14, formula adalah:

=TAKE(A3:C14, ,2)

Formula ini pergi ke sel E3 dan tumpahan ke dalam dua lajur dan banyak baris seperti yang terdapat dalam julat yang dibekalkan.

Ekstrak sebilangan baris dan lajur

Untuk mendapatkan bilangan baris dan lajur yang diberikan dari permulaan array, anda memberikan nombor positif untuk kedua -dua baris dan argumen lajur .

Sebagai contoh, untuk mengambil 4 baris pertama dan 2 lajur dari dataset kami, formula adalah:

=TAKE(A3:C14, 4, 2)

Dimasukkan dalam E3, formula mengisi empat baris (seperti yang ditetapkan dalam hujah 2 ^ND ) dan dua lajur (seperti yang ditakrifkan dalam hujah 3 ^RD ).

Dapatkan baris N terakhir

Untuk menarik sejumlah baris dari akhir array, berikan nombor negatif untuk hujah baris . Contohnya:

Untuk mengambil baris terakhir , gunakan -1:

=TAKE(A3:C14, -1)

Untuk mendapatkan 3 baris terakhir , bekalan -3:

=TAKE(A3:C14, -3)

Dalam tangkapan skrin di bawah, anda boleh memerhatikan hasilnya.

Kembalikan lajur N lepas

Untuk mengekstrak beberapa lajur dari akhir array atau julat, gunakan nombor negatif untuk hujah lajur . Contohnya:

Untuk mendapatkan lajur terakhir , tetapkan hujah ke -3 ke -1:

=TAKE(A3:C14, , -1)

Untuk menarik 2 lajur terakhir , tetapkan hujah ke -3 ke -2:

=TAKE(A3:C14, , -2)

Dan inilah hasilnya:

Petua. Untuk mengambil baris dan lajur dari akhir array, berikan nombor negatif untuk kedua -dua argumen baris dan lajur .

Cara mengambil baris / lajur dari pelbagai julat

Dalam keadaan apabila anda ingin mengekstrak beberapa lajur atau baris dari beberapa julat yang tidak bersesuaian, ia mengambil masa dua langkah untuk menyelesaikan tugas:

1. Menggabungkan pelbagai julat ke dalam satu secara menegak atau mendatar menggunakan fungsi VStack atau HStack.
2. Kembalikan bilangan lajur atau baris yang dikehendaki dari array gabungan.

Bergantung pada struktur lembaran kerja anda, gunakan salah satu penyelesaian berikut.

Stack Ranges secara menegak dan mengambil baris atau lajur

Katakan anda mempunyai 3 julat berasingan seperti yang ditunjukkan dalam imej di bawah. Untuk menambahkan setiap julat berikutnya ke bahagian bawah yang sebelumnya, formula adalah:

=VSTACK(A4:C6, A10:C14, A18:C21)

Sarang dalam argumen array Take, nyatakan berapa banyak baris untuk kembali, dan anda akan mendapat hasil yang anda cari:

=TAKE(VSTACK(A4:C6, A10:C14, A18:C21), 4)

Untuk mengembalikan lajur , taipkan nombor yang sesuai dalam hujah 3 ^RD :

=TAKE(VSTACK(A4:C6, A10:C14, A18:C21), ,2)

Output akan kelihatan seperti ini:

Stack ranges secara mendatar dan mengambil baris atau lajur

Sekiranya data dalam julat sumber disusun secara mendatar dalam baris, gunakan fungsi HStack untuk menggabungkannya ke dalam satu array. Contohnya:

=HSTACK(B3:D5, G3:H5, K3:L5)

Dan kemudian, anda meletakkan formula di atas di dalam fungsi mengambil dan tetapkan argumen baris atau lajur , atau kedua -duanya, mengikut keperluan anda.

Sebagai contoh, untuk mendapatkan 2 baris pertama dari array yang disusun, formula adalah:

=TAKE(HSTACK(B3:D5, G3:H5, K3:L5), 2)

Dan formula ini akan membawa 5 lajur terakhir:

=TAKE(HSTACK(B3:D5, G3:H5, K3:L5), ,5)

Ambil Fungsi Alternatif untuk Excel 2010 - 365

Dalam Excel 2019 dan versi terdahulu di mana fungsi mengambil tidak disokong, anda boleh menggunakan Offset sebagai alternatif. Walaupun formula offset tidak begitu intuitif dan mudah, ia menyediakan penyelesaian kerja. Dan inilah cara anda menetapkannya:

1. Untuk hujah 1 ^ST , membekalkan pelbagai nilai asal.
2. Argumen 2 ^ND dan 3 ^RD atau kedua -duanya ditetapkan kepada sifar atau ditinggalkan, dengan mengandaikan anda mengeluarkan subset dari permulaan array. Secara pilihan, anda boleh menentukan bagaimana baris dan lajur boleh diimbangi dari sel kiri atas array.
3. Dalam hujah ^ke -4, tunjukkan bilangan baris untuk kembali.
4. Dalam hujah ^ke -5, tentukan bilangan lajur untuk kembali.

Menyimpulkan, formula generik mengambil bentuk ini:

Mengimbangi ( array ,, baris , lajur )

Sebagai contoh, untuk mengekstrak 6 baris dan 2 lajur dari permulaan julat A3: C14, formula berjalan seperti berikut:

=OFFSET(A3:C14, , , 6, 2)

Dalam semua versi kecuali Excel 365 dan 2021 yang mengendalikan array secara asli, ini hanya berfungsi sebagai formula array CSE tradisional. Terdapat dua cara untuk memasukkannya:

• Pilih julat sel saiz yang sama seperti output yang dijangkakan (6 baris dan 2 lajur dalam kes kami) dan tekan F2 untuk memasukkan mod edit. Taipkan formula dan tekan CTRL Shift masukkan untuk memasukkannya ke dalam semua sel yang dipilih sekaligus.
• Masukkan formula dalam mana -mana sel kosong (E3 dalam contoh ini) dan tekan CTRL Shift Enter untuk menyelesaikannya. Selepas itu, seret formula ke bawah dan ke kanan di seberang baris dan lajur yang diperlukan.

Hasilnya akan kelihatan serupa dengan ini:

Nota. Harap maklum bahawa Offset adalah fungsi yang tidak menentu, dan ia mungkin melambatkan lembaran kerja anda jika digunakan dalam banyak sel.

Excel mengambil fungsi tidak berfungsi

Sekiranya formula mengambil tidak berfungsi dalam Excel anda atau mengakibatkan kesilapan, kemungkinan besar menjadi salah satu sebab di bawah.

Ambil tidak disokong dalam versi Excel anda

Ambil fungsi baru dan ia mempunyai ketersediaan terhad. Jika versi anda selain Excel 365, cuba formula offset alternatif.

Array kosong

Jika sama ada argumen baris atau lajur ditetapkan kepada 0, #calc! Ralat dikembalikan menunjukkan array kosong.

Bilangan sel kosong yang tidak mencukupi untuk diisi dengan hasilnya

Sekiranya tidak terdapat sel -sel kosong yang cukup untuk formula untuk menumpahkan hasilnya, ralat #Spill berlaku. Untuk memperbaikinya, hanya membersihkan sel -sel jiran di bawah atau/dan ke kanan. Untuk maklumat lanjut, lihat bagaimana untuk menyelesaikan #spill! Ralat dalam Excel.

Itulah cara menggunakan fungsi mengambil dalam Excel untuk mengekstrak baris atau lajur dari pelbagai sel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Excel Ambil Formula - Contoh (fail .xlsx)

Atas ialah kandungan terperinci Excel Berguna untuk mengekstrak baris atau lajur dari array. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!