Pembelajaran fungsi Excel: Cara menggunakan fungsi carian INDEX()

Artikel ini akan memperkenalkan anda kepada fungsi INDEX! INDEX juga merupakan ahli keluarga carian Disebabkan fungsi kedudukan koordinat yang berkuasa, kadangkala VLOOKUP terpaksa mengetepikan!

1 Memahami fungsi INDEX

Fungsi indeks: Dalam julat sel tertentu, kembalikan sel di persimpangan a baris dan baris tertentu Nilai atau rujukan sel.

Struktur fungsi: indeks (kawasan sel, nombor baris, nombor lajur)

Kawasan, nombor baris, nombor lajur, sama seperti menyasar melalui koordinat. Sama seperti animasi di bawah, cari lajur, cari baris, klik dan tekan!

Kami ingin mencari "囡", dan anda boleh melihat bahawa koordinatnya ialah baris 4 dan lajur 3.

Jadi formulanya: =INDEX(B2:G11,4,3) boleh dapat "囡".

2. Penggunaan asas fungsi INDEX

1. hanya satu nilai koordinat diperlukan

Jika kawasan yang diberi ialah satu baris atau lajur, maka koordinat tidak memerlukan dua nombor, hanya satu.

Sebagai contoh, kita kini perlu mendapatkan "Li Hui" daripada A17:A21 dalam F17.

Masukkan formula: =INDEX(A17:A21,2).

Contoh lain ialah kita perlu mendapatkan gaji pokok Li Hui daripada A18:D18 di G17.

Masukkan formula: = INDEX(A18:D18,4).

2 Ekstrak nilai dari kawasan berbilang baris dan berbilang lajur: dua nilai koordinat dalam baris dan lajur diperlukan

Perkara ini tidak akan disenaraikan. Beginilah cara anda mencari perkataan "囡" di hadapan.

Seperti yang dapat dilihat daripada contoh di atas, INDEX mengembalikan nilai melalui koordinat, seperti peluru berpandu berpandu ketepatan, menunjukkan tempat untuk dipukul (kembali ke mana). Walau bagaimanapun, semakan koordinat manual semata-mata dan kemudian memasukkan koordinat tidak selaras dengan "kemodenan". Dalam operasi sebenar, data selalunya terdiri daripada berpuluh-puluh lajur, berpuluh-puluh baris atau bahkan berpuluh-puluh ribu baris Pada masa ini, adalah terlalu tidak realistik untuk kami menyemak koordinat secara manual dan memasukkan koordinat seperti yang diperlukan. Oleh itu, INDEX memerlukan pembantu dan pasukan untuk menakluk dunia.

3. Penggunaan praktikal INDEX

1 carian

(1) Kumpulan COLUMN boleh terus memulangkan berbilang data rakan yang sama

Sebagai contoh, kita perlu terus mendapatkan nama, umur, dan nombor kerja C23 dari jadual Masa Onboarding.

Dalam kawasan data A17:E21, nombor kerja C23 terletak di baris ke-3 Nombor lajur nama, umur dan masa menyertai ialah 2 dan 3 dari kiri ke kanan ,4. Kita boleh menggunakan COLUMN (B1) untuk menggantikan 2, 3, dan 4 untuk mencapai kesan separa automatik. Formulanya adalah seperti berikut:

=INDEX($A17:$E21,3,COLUMN(B1))

Kemudian tarik ke kanan untuk mengisinya.

Masa penyertaan yang diperolehi adalah nombor, cuma tukar format kepada tarikh yang singkat.

(2) Pengumpulan dengan ROW boleh terus mengembalikan berbilang data dalam lajur yang sama

Sebagai contoh, di bawah, kami menggunakan formula: =INDEX(A:E,ROW(A3),2) pengisian lungsur turun untuk mendapatkan nama tiga nombor kerja .

(3) Kumpulan bersama COLUMN dan ROW pada masa yang sama

Sebagai contoh, kita boleh menggunakan formula: =INDEX($A:$E,ROW(A3),COLUMN(B1)) Tarik kanan ke bawah dan isi untuk mendapatkan Nama, umur, dan tarikh penyertaan nombor kerja C23, C08 dan C10.

Dengan mengumpulkan dengan COLUMN dan ROW, kesan separa automatik dicapai. Selagi ia adalah nilai berterusan dan tetap, ia boleh dicapai dengan INDEX+ROW+COLUMN.

Sebagai contoh, kita perlu mendapatkan nilai dalam baris dan lajur ganti untuk mendapatkan nama dan tarikh penyertaan nombor kerja C15, C23 dan C10. Formulanya ialah:

=INDEX($A:$E,ROW(A1)*2-1,COLUMN(A1)*2)

dan kemudian tarik ke bawah ke kanan untuk diisi.

Separa automatik adalah lebih mudah daripada semakan koordinat manual sepenuhnya dan memasukkan koordinat, tetapi sebab mengapa ia dipanggil separa automatik ialah ia masih memerlukan kerja manual untuk mencari corak dalam data. Jika corak data nilai adalah kompleks atau tidak teratur, kita tidak boleh menjadi separa automatik. Pada masa ini, anda perlu membentuk pasukan dengan pembantu besar MATCH untuk melaksanakan kerja automatik sepenuhnya.

2 Kumpulan dengan pembantu PERLAWANAN: merealisasikan carian nilai automatik sepenuhnya

(1) kumpulan INDEX+MATCH

Peraturan carian data berikut adalah tidak kemas.

Masukkan formula dalam C28:

=INDEX($A:$E,MATCH($B28,$A:$A,0),MATCH(C,$A:$E,0))

Kemudian tarik ke bawah ke kanan untuk mengisi formula.

Menggunakan fungsi MATCH untuk menanyakan kedudukan baris dan lajur dalam kawasan tetap berdasarkan syarat menggantikan carian manual untuk koordinat atau corak data sepenuhnya, mencapai automasi penuh.

(2) Perbezaan antara INDEX+MATCH dan VLOOKUP+MATCH

Masukkan formula:

=VLOOKUP($B28,$A:$E,MATCH(C,$A:$E,0),0)

Tarik ke kanan dan lungsur ke bawah untuk mengisi.

Dari segi panjang formula, VLOOKUP+MATCH adalah lebih ringkas daripada INDEX+MATCH. Jadi mengapa kita masih memerlukan INDEX+MATCH? Sebabnya ialah fungsi INDEX boleh mencari data selagi ia menerima nilai koordinat baris dan lajur Tiada perbezaan antara carian hadapan dan carian terbalik sama sekali. VLOOKUP tidak akan berfungsi Secara lalai, ia hanya boleh mencapai carian hadapan, iaitu, ia hanya boleh mencari dari kiri ke kanan dalam kawasan carian, tetapi bukan dari kanan ke kiri. Jika VLOOKUP ingin melaksanakan carian terbalik dari kanan ke kiri, ia perlu menggunakan fungsi IF atau fungsi CHOOSE untuk membina kawasan carian baharu.

Sebagai contoh, jika kita perlu menyemak nombor kerja mengikut nama, ia adalah seperti berikut:

Gunakan kombinasi INDEX+MATCH untuk menulis terus formula: =INDEX(A:B,MATCH(G17,B:B,0),1), dan kemudian tariknya ke bawah

Jika anda menggunakan VLOOKUP untuk mencari, kerana ia adalah carian terbalik, anda perlu menggunakan fungsi IF untuk membina semula kawasan carian, dan formulanya menjadi:

=VLOOKUP(G17,IF({1,0},B:B,A:A),2,0)

Jadi, sebagai perbandingan, apabila mencari ke hadapan, anda boleh menggunakan INDEX+MATCH dan VLOOKUP+MATCH adalah lebih mudah carian terbalik INDEX+MATCH adalah yang paling ringkas, terutamanya apabila kawasan carian terbalik mempunyai tiga atau empat lajur data, INDEX+MATCH ialah pilihan terbaik.

Baiklah, selepas menjawab soalan dalam Kelas Fungsi 2, kita terus melihat kumpulan praktikal INDEX.

3 Bentuk kumpulan dengan tetamu khas pembantu saiz tambah KECIL dan JIKA: wujudkan carian satu-ke-banyak

Format formula selepas pengumpulan ialah =INDEX(查找区域,SMALL(IF(),ROW()),MATCH())<.>

Seperti yang ditunjukkan dalam animasi di bawah:

Formulanya sangat panjang:

=INDEX($A:$D,SMALL(IF($C:$C=$F,ROW(:),99),ROW(A1)),MATCH(F,$A:$D,0))

Letakkannya pada Fungsi IFERROR kalis ralat adalah lebih lama lagi:

=IFERROR(INDEX($A:$D,SMALL(IF($C:$C=$F,ROW(:),99),ROW(A1)),MATCH(F,$A:$D,0)),"")

Formula gabungan sedemikian, kita sering memanggilnya formula minyak ular, digunakan terutamanya untuk carian satu-ke-banyak .

Ok, itu sahaja yang kami perkenalkan tentang penggunaan praktikal INDEX. Fungsi INDEX mempunyai kelebihan menggunakan koordinat untuk mendapatkan nilai dengan tepat, tetapi ia tidak mempunyai fungsi mencari koordinat secara automatik berdasarkan keadaan Ia pincang, jadi dalam pertempuran sebenar ia memerlukan bantuan pembantu untuk mencari koordinat. Ia adalah peluru berpandu berpandu ketepatan dalam fungsi, dan ia adalah Crip, Crip yang berkuasa!

Cadangan pembelajaran berkaitan:

tutorial cemerlang

Atas ialah kandungan terperinci Pembelajaran fungsi Excel: Cara menggunakan fungsi carian INDEX(). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!