Excel

Cara mencari perlawanan pertama dan terakhir

How Lookup First

Salah satu aspek fungsi pencarian yang lebih membingungkan di Excel adalah memahami cara mendapatkan perlawanan pertama atau terakhir dalam satu set data dengan lebih dari satu padanan. Ini kerana tingkah laku Excel berubah bergantung pada dua faktor: sama ada anda melakukan padanan tepat atau anggaran, dan sama ada data disusun atau tidak.



Sebagai contoh, katakan kita menggunakan VLOOKUP untuk mencari harga 'hijau' dalam data di bawah. Harga mana yang akan kita dapat?

VLOOKUP perlawanan mana yang akan kita dapat?





Baca terus untuk jawapan dan contoh yang lebih menarik.

Catatan:



Padanan tepat = pertama

Apabila melakukan perlawanan yang tepat, anda akan selalu mendapat perlawanan pertama, noktah. Tidak kira sama ada data disusun atau tidak. Pada skrin di bawah, nilai carian di E5 adalah 'merah'. The Fungsi VLOOKUP , dalam mod padanan tepat, mengembalikan harga untuk perlawanan pertama:

 
= VLOOKUP (E5,data,2,FALSE)

Pertandingan tepat VLOOKUP mencari perlawanan pertama

Perhatikan argumen terakhir dalam VLOOKUP adalah PALSU untuk memaksa padanan tepat.

Padanan anggaran = terakhir

Sekiranya anda melakukan perkiraan, dan data disusun mengikut nilai carian , anda akan mendapat perlawanan terakhir. Kenapa? Oleh kerana semasa pertandingan hampir, Excel mengimbas nilai sehingga nilai yang lebih besar daripada nilai carian dijumpai, maka ia 'mengundurkan diri' ke nilai sebelumnya.

Pada layar di bawah, VLOOKUP diatur ke mod pencocokan perkiraan, dan warna disusun. VLOOKUP mengembalikan harga untuk 'hijau' terakhir:

buang 3 watak pertama di excel
 
= VLOOKUP (E5,data,2,TRUE)

Pertandingan anggaran VLOOKUP menemui perlawanan terakhir

Perhatikan argumen terakhir dalam VLOOKUP adalah BENAR untuk perkiraan hampir.

Padanan anggaran + data yang tidak disusun = bahaya

Dengan carian padanan anggaran standard, data mesti disusun mengikut nilai carian . Dengan data yang tidak diurutkan, anda mungkin melihat hasil yang kelihatan normal yang sama sekali tidak betul. Masalah ini lebih mungkin berlaku dengan VLOOKUP kerana Lalai VLOOKUP untuk hampir sama apabila tidak ada hujah keempat yang diberikan.

Untuk menggambarkan masalah ini, lihat contoh di bawah. Data adalah tidak disusun dan VLOOKUP, tanpa argumen keempat yang disediakan, default untuk hampir sama. Perhatikan tidak ada 'merah' dengan harga $ 17,00, namun VLOOKUP dengan senang hati mengembalikan hasil yang tidak sah ini:

 
= VLOOKUP (E5,data,2)

Contoh keputusan perlawanan VLOOKUP yang salah

Atas sebab ini, saya cadangkan agar selalu menetapkan argumen terakhir untuk VLOOKUP secara eksplisit: BENAR = hampir sama, FALSE = padanan tepat. Hujahnya adalah pilihan, tetapi memberikan nilai membuat anda memikirkannya, dan memberikan peringatan visual pada masa akan datang.

Kami akan melihat bagaimana mengatasi masalah perlawanan terakhir dan data yang tidak disusun di bawah.

Padanan anggaran 'Normal'

Pada ketika ini, anda mungkin merasa sedikit bingung dan bingung mengenai idea bahawa pertandingan yang hampir dapat mengembalikan pertandingan terakhir dalam beberapa kes. Sekiranya demikian, jangan risau. Menggunakan padanan anggaran untuk mendapatkan perlawanan terakhir bukanlah kes 'normal'. Biasanya, anda akan melihat padanan anggaran yang digunakan untuk menetapkan nilai mengikut jenis skala. Contoh klasik adalah menggunakan VLOOKUP dalam mod padanan anggaran untuk memberikan nilai, yang berfungsi dengan baik:

bagaimana menjalankan ejaan check in excel
 
= VLOOKUP (E5,key,2,TRUE)

VLOOKUP padanan anggaran untuk memberikan nilai

Dalam kes seperti ini, jadual carian dengan sengaja tidak termasuk nilai pendua, jadi keseluruhan idea 'nilai padanan terakhir' tidak relevan. Maklumat lanjut mengenai formula ini di sini .

Maklumat di atas adalah untuk memberikan latar belakang dan konteks bagaimana pencocokan berfungsi di Excel, sehingga pendekatan yang dijelaskan di bawah ini masuk akal.

Aplikasi praktikal

Bagaimana kita boleh menggunakan tingkah laku yang dinyatakan di atas dalam situasi praktikal? Nah, satu senario biasa adalah mencari entri 'terkini' atau 'terakhir' untuk item. Sebagai contoh, di bawah ini kita menggunakan VLOOKUP dalam mod padanan anggaran untuk mencari harga terkini untuk Sandal. Data pemberitahuan disusun mengikut item, kemudian berdasarkan tarikh, sehingga harga terbaru untuk item tertentu muncul terakhir:

 
= VLOOKUP (F5,data,3,TRUE)

Padanan anggaran VLOOKUP + data yang disusun = harga terkini

INDEX dan PERTANDINGAN

Fungsi pencarian lain boleh digunakan dengan cara ini juga. Di bawah ini, kami menggunakan yang setara INDEKS dan PERTANDINGAN formula cari harga terkini dengan data yang sama. Notice MATCH dikonfigurasi untuk mendekati pencocokan untuk item yang disusun dalam urutan menaik dengan menetapkan argumen ketiga menjadi 1:

 
= INDEX (price, MATCH (F5,item,1))

INDEX dan MATCH perkiraan untuk mencari harga terkini

Fungsi LOOKUP

The Fungsi LOOKUP juga boleh digunakan dalam kes ini. LIHAT selalu melakukan padanan anggaran, jadi ia berfungsi dengan baik dalam senario 'perlawanan terakhir'. Rumusannya agak mudah:

 
= LOOKUP (F5,item,price)

Fungsi LOOKUP untuk mencari harga terkini

Perlawanan terakhir dengan data yang tidak disusun

Bagaimana jika anda mahukan pertandingan terakhir, tetapi data tidak disusun berdasarkan nilai carian? Dengan kata lain, anda ingin menerapkan kriteria untuk mencari padanan, dan anda hanya mahu item terakhir dalam data yang sesuai dengan kriteria anda? Ini sebenarnya adalah kes di mana fungsi LOOKUP bersinar, kerana LOOKUP dapat menangani operasi array secara semula jadi, tanpa kawalan + shift + enter. Ini bermaksud kita dapat membina array pencarian secara dinamik untuk mencari data yang kita inginkan dengan menggunakan ungkapan logik sederhana.

Contohnya, lihat formula di bawah:

 
= LOOKUP (2,1/(item=F5),price)

Fungsi LOOKUP untuk mencari padanan terakhir dengan data yang tidak disusun

Formula ini mendapat harga terkini untuk Sandal di tidak disusun data.

Anda mungkin tidak pernah melihat formula seperti ini sebelumnya, jadi mari kita memecahnya secara berperingkat. Bekerja dari dalam ke luar, pertama-tama kami menerapkan kriteria dengan ungkapan logik sederhana:

 
item=F5

Ini menghasilkan susunan nilai TRUE dan FALSE, di mana TRUE sesuai dengan item yang 'sandal' dan FALSE sesuai dengan semua nilai lain:

 
{FALSETRUEFALSETRUEFALSEFALSETRUEFALSE}

Seterusnya, kami membahagikan nombor 1 dengan tatasusunan ini. Semasa pembahagian, BENAR menjadi 1 dan SALAH menjadi sifar, jadi anda harus menggambarkan operasi seperti ini:

 
1/{01010010}

Satu dibahagi satu adalah satu, dan satu dibahagi dengan sifar adalah # DIV / 0, jadi hasilnya adalah susunan yang lain, yang satu ini hanya mengandungi ralat 1s dan # DIV / 0:

 
{#DIV/0!1#DIV/0!1#DIV/0!#DIV/0!1#DIV/0!}

Jangan risau, ada kaedah untuk kegilaan ini :)

hitung bilangan hari bekerja antara dua tarikh

Sekarang, anda mungkin menyedari bahawa nilai pencarian adalah nombor 2. Ini mungkin kelihatan membingungkan. Bagaimana LOOKUP akan menemui nombor 2 dalam array yang hanya mengandungi 1s dan kesalahan? Tidak akan. Kami menggunakan 2 sebagai nilai pencarian untuk memaksa LOOKUP mengimbas ke akhir data .

Fungsi LOOKUP secara automatik akan mengabaikan ralat, jadi satu-satunya perkara yang tersisa adalah 1s. Ia akan mengimbas sepanjang 1 untuk mencari 2 yang tidak akan dijumpai. Apabila mencapai akhir array, ia akan 'mundur' ke nilai terakhir yang valid - 1 terakhir - yang sesuai dengan perlawanan terakhir berdasarkan kriteria yang diberikan.

Versi array INDEX dan MATCH

Keindahannya Fungsi LOOKUP adakah ia dapat menangani operasi array yang dijelaskan di atas secara asli, tanpa memerlukan anda memasukkan sebagai formula larik dengan kawalan + shift + enter. Walau bagaimanapun, anda pasti boleh menggunakan formula larik jika anda suka. Inilah formula INDEX dan MATCH yang setara, yang mesti dimasukkan dengan kawalan + shift + enter:

 
{= INDEX (price, MATCH (2,1/(item=F5),1))}

Sel tidak kosong terakhir

Pendekatan di atas ternyata sangat berguna. Contohnya, dengan mengubah logik sedikit, kita boleh melakukan perkara seperti mencari sel kosong yang terakhir dalam lajur:

 
= LOOKUP (2,1/(B:B''),B:B)

Menggunakan LOOKUP untuk mencari sel kosong yang terakhir

Formula ini adalah diterangkan dengan lebih terperinci di sini .

Cari perlawanan ke-9? Semua perlawanan?

Sekiranya anda berjaya sejauh ini, anda mungkin tertanya-tanya bagaimana anda akan menemui perlawanan kedua atau ketiga, atau bagaimana anda akan mendapatkan semua perlawanan? Berikut adalah beberapa pautan untuk anda:

Anda akan melihat formula seperti ini menjadi rumit. Terdapat beberapa fungsi baru yang hebat yang akan datang ke Excel pada tahun 2019 yang akan menjadikan penyelesaian ini lebih mudah. Nantikan. Sementara itu, jangan lupa itu Jadual Pangsi adalah kaedah terbaik untuk meneroka data tanpa formula .

Apa yang akan datang?

Berikut adalah panduan untuk mengetahui lebih lanjut mengenai formula Excel. Kami juga menawarkan latihan video dalam talian .

  • Asas formula - jika anda baru memulakan
  • 500 contoh formula dengan penjelasan penuh
  • 101 fungsi Excel penting
  • Panduan untuk semua fungsi Excel (kerja dalam proses)
  • Kriteria formula - 50 contoh
  • Formula untuk pemformatan bersyarat
Pengarang Dave Bruns


^