Analisis mendalam tentang formula saringan Excel Tiger Balm 'INDEX-SMALL-IF-ROW'

Artikel ini berkongsi cara Excel menggunakan penapisan formula untuk melengkapkan carian satu-ke-banyak Ia adalah formula fungsi penapisan excel yang agak klasik untuk mencari data formula secara automatik.

Saya selalu dengar pakar cakap ada formula minyak ular, tapi apa formula minyak ular, dan apa formula Excel ni boleh buat? Anda juga boleh melihat pemaparan berikut:

Contoh ini ialah carian satu-ke-banyak yang biasa. Syarat carian ialah jabatan, dan setiap satu jabatan sepadan dengannya dalam sumber data Terdapat berbilang data, dan tujuan utama formula Balsem Harimau adalah untuk menyelesaikan beberapa masalah yang agak rumit seperti carian satu-ke-banyak. Formula dalam animasi di atas ialah:

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

Melihat formula ini, ramai rakan mungkin terkejut: Saya tidak dapat memahami formula yang begitu panjang!

Hari ini, saya akan bekerjasama dengan anda untuk memecahkan rutin formula yang tidak dapat difahami tetapi sangat berkuasa ini Sila bersabar dan baca terus...

Formula di atas menggunakan sejumlah enam fungsi: IFERROR, INDEX , SMALL, IF, ROW dan MATCH, yang mana IFERROR dan MATCH ialah dua fungsi tambahan dalam contoh ini, dan baki empat INDEX-SMALL-IF-ROW ialah formula minyak ular.

Jadi mari kita pelajari prinsip bahagian teras ini dahulu:

Formula sel F4 ialah:

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

Mari kita mulakan dengan INDEX Fungsi asas fungsi ini adalah untuk memberikan kawasan, dan kemudian mengembalikan hasil carian mengikut kedudukan baris dan lajur yang sepadan Dalam rajah di atas, kawasan data yang dicari oleh INDEX ialah kawasan di mana nama itu terletak $A$2: $A$21.

Struktur asas fungsi INDEX ialah: INDEX (kawasan carian, baris dan lajur Jika kawasan itu adalah satu baris atau lajur, salah satu daripada dua parameter terakhir boleh diabaikan). Dalam istilah orang awam, apabila anda mengambil tiket wayang untuk mencari tempat duduk, tempat duduk di seluruh dewan adalah kawasan, dan baris dan tempat duduk ialah dua parameter terakhir dalam formula Dengan cara ini, lokasi sasaran boleh ditemui dengan tepat .

Dalam contoh di atas, kawasan itu berada dalam lajur, jadi kita hanya perlu menentukan baris mana setiap data berada.

Memahami perkara ini, tumpuan kita harus tertumpu pada parameter kedua INDEX:

SMALL(IF($C:$C=$F,ROW(:),99),ROW(A1))

Lihat gambar di atas. Terdapat empat rekod di bahagian jualan, iaitu di baris 5, 8, 9 dan 16 kawasan data (kawasan data bermula dari baris kedua).

Jadi kami berharap apabila formula ditarik ke bawah, parameter kedua INDEX ialah empat nombor 5, 8, 9 dan 16 (ini mesti difahami).

Perhatian, kami akan bersentuhan dengan bahagian teras Tiger Balm, sila kekalkan tumpuan yang tinggi...

Struktur asas fungsi KECIL: SMALL（一组数，第几小的数）

Adalah disyorkan untuk mensimulasikan data mudah untuk memahami sepenuhnya fungsi ini. Kaedahnya adalah seperti berikut:

Masukkan beberapa nombor dalam lajur A. Formula. bermakna nombor terkecil dalam lajur ini, hasilnya adalah 2, ia mudah difahami, bukan?

Second to last Yang kecil ialah 4.
Jika anda ingin terus mendapatkan nombor ketiga terkecil, saya rasa semua orang boleh memikirkan apa yang perlu dilakukan, tetapi akan ada masalah Kami hanya boleh mengubah suai parameter kedua secara manual dan tidak boleh mencapai parameter ini melalui penurunan -Tukar, jika anda ingin boleh tarik ke bawah, parameter kedua perlu menggunakan fungsi ROW, yang diubah suai seperti ini:

The Fungsi ROW adalah sangat mudah, apa yang anda dapat ialah Nombor baris parameter Melalui formula ini, kita boleh menyusun data dalam lajur A dari kecil kepada besar Adakah anda fikir ia menarik?
Berbalik kepada formula Balsem Harimau kami, ingat apakah maksud empat nombor 5, 8, 9 dan 16 Kita perlu menggunakan fungsi KECIL untuk mendapatkan empat nombor ini dalam urutan Selaras dengan F2, jika nombor baris adalah sama, jika ia berbeza, nombor yang lebih besar daripada nombor baris maksimum diperolehi (tujuan untuk mengelakkannya daripada ditemui):

Untuk mencapai matlamat ini, campur tangan fungsi IF diperlukan, jadi terdapat:

IF($C:$C=$F,ROW(:),99), gunakan perenggan ini sebagai parameter pertama KECIL.

Berkenaan JIKA ini, lebih mudah kita fahami F9 untuk melihat hasil formula ini:

Sebab kita Ada. hanya 20 keping data, jadi cukup untuk menggunakan 99 sebagai parameter ketiga IF Jika jumlah data agak besar, anda boleh menggunakan 9^9, yang bermaksud 9 dinaikkan kepada kuasa ke-9 cukuplah.

Jika anda faham IF ini, maka lihat perenggan ini SMALL(IF($C:$C=$F,ROW(:),99),ROW(A1)) untuk mengetahui sama ada anda berasa kurang pening.

Mengenai bahagian KECIL, anda mesti faham bahawa apabila formula ditarik ke bawah, kita mendapat nombor yang kita ingin dapatkan satu demi satu, dan kemudian menggunakan nombor ini sebagai parameter kedua INDEX untuk mendapatkan apa yang kita akhirnya. perlukan.

Inti Balsem Harimau ialah INDEX, SMALL, IF dan ROW Sila pastikan anda memikirkannya berulang kali untuk memahami bahagian prinsip ini. Terdapat satu lagi perkara yang sangat penting yang perlu ditekankan Formula balsem harimau ialah formula tatasusunan, jadi kita perlu menahan Ctrl dan shift dan tekan Enter.

Bagi formula awal, memandangkan anda perlu mencari kandungan berbilang lajur, kawasan data INDEX menggunakan $A$2:$D$21 Apabila terdapat berbilang lajur, anda perlu menyediakan kedudukan lajur untuk mencari nilai sasaran , jadi gunakan MATCH(F$3,$A$1:$D$1,0) untuk menentukan lajur mana data berada.

Setiap jabatan mempunyai data yang berbeza Kita perlu menurunkan formula beberapa baris lagi Pada masa ini, beberapa nilai ralat akan dihasilkan nilai ralat, supaya Hasil pertanyaan kelihatan sangat bersih.

Hari ini saya hanya menggunakan contoh carian satu-ke-banyak untuk menerangkan prinsip formula Tiger Balm Sebenarnya, terdapat banyak helah untuk Tiger Balm Jika anda suka, teruskan berkongsi contoh yang berkaitan pada masa akan datang, sudah tentu, jika anda selesai membaca artikel ini. Adalah lebih baik jika anda boleh mentafsir beberapa formula kompleks sendiri.

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Analisis mendalam tentang formula saringan Excel Tiger Balm 'INDEX-SMALL-IF-ROW'. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!