Ambil kecocokan ke-n dengan INDEX
Untuk menemukan kecocokan nilai ke-n dari rentang dan mengambil data terkaitnya, Anda dapat menggunakan rumus berdasarkan pada INDEKS, BARIS, KECIL, dan IF fungsi.
Bagaimana cara mengambil informasi yang terkait dengan kecocokan nilai ke-n dengan INDEX?
Untuk mendapatkan informasi tentang pertandingan ke-3 Usain Bolt pada tabel seperti gambar di atas, Anda dapat menggunakan fungsi ROW dan IF bersama-sama untuk mendapatkan larik yang terdiri dari nomor baris semua nilai Usain Bolt. Kemudian gunakan fungsi SAMLL untuk mengambil nomor baris dari nilai ke-n yang cocok yang Anda tentukan. Terakhir, masukkan hasilnya ke fungsi INDEX untuk mendapatkan informasi yang sesuai pada baris yang sama.
Sintaks umum
=INDEX(return_range,SMALL(IF(lookup_array=lookup_value,ROW(lookup_array-ROW(INDEX(lookup_array,1,1))+1),n))
Catatan: Ini adalah rumus array yang mengharuskan Anda memasukkan dengan Ctrl + perubahan + Enter.
- rentang_kembali: Rentang untuk mengembalikan informasi yang sesuai dengan kecocokan ke-n. Di sini mengacu pada tahun atau rentang waktu.
- pencarian_array: Rentang untuk mencari posisi baris ke-n yang cocok. Di sini mengacu pada rentang pemenang.
- nilai lookup: Nilai yang Anda tentukan untuk mencari kecocokan ke-n. Di sini mengacu pada Usain Bolt.
- n: Tunjukkan kecocokan ke-n. Untuk menemukan kecocokan pertama dari suatu nilai, atur n sebagai 1; untuk menemukan kecocokan ke-2, atur n sebagai 2.
Untuk mendapatkan informasi tentang pertandingan ke-3 Usain Bolt, silakan salin atau masukkan rumus di bawah ini di sel G8 dan G9, dan tekan Ctrl + perubahan + Enter untuk mendapatkan hasil:
Tahun (Sel G8)
=INDEKS(B6: B12,KECIL(JIKA(C6: C12=G5,BARIS(C6: C12-BARIS(INDEKS(C6: C12,1,1))+1),G6))
Waktu (Sel G9)
=INDEKS(D6: D12,KECIL(JIKA(C6: C12=G5,BARIS(C6: C12-BARIS(INDEKS(C6: C12,1,1))+1),G6))
Penjelasan rumus
Di sini kita menggunakan rumus di bawah ini sebagai contoh:
=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))
- BARIS (C6:C12): Fungsi ROW mengembalikan nomor baris setiap sel dalam rentang C6: C12 dalam array seperti ini: {6;7;8;9;10;11;12}.
- BARIS(INDEKS(C6:C12,1,1)): Fungsi INDEX dan ROW mengembalikan nomor baris pertama dari rentang pencarian C6: C12 dalam lembar kerja Excel ini, yaitu 6.
- JIKA(C6:C12=G5,BARIS (C6:C12)-BARIS(INDEKS(C6:C12,1,1))+ 1) = JIKA(C6:C12=G5,{6;7;8;9;10;11;12}-6+ 1): Fungsi IF memeriksa apakah setiap nilai dalam rentang C6: C12 cocok dengan nilai dalam sel G5, Usain Bolt. Jika ya, fungsi akan mengembalikan hasil “nomor baris yang sesuai-6+1”; Jika tidak, kembalikan FALSE. Jadi hasilnya akan menjadi array seperti ini: {SALAH;2;3;4;SALAH;SALAH;SALAH}.
- KECIL(JIKA(C6:C12=G5,BARIS (C6:C12)-BARIS(INDEKS(C6:C12,1,1))+ 1),G6) = KECIL({SALAH;2;3;4;SALAH;SALAH;SALAH},G6): Cuplikan mengembalikan 3rd (nilai dalam sel G6) nilai terkecil dari array, yaitu 4.
- INDEKS(B6:B12,KECIL(JIKA(C6:C12=G5,BARIS (C6:C12)-BARIS(INDEKS(C6:C12,1,1))+ 1),G6)) = INDEKS(B6:B12,4) Fungsi INDEX mengembalikan 4nilai ke-th dalam rentang tahun B6: B12, Yang Beijing 2008.
Fungsi terkait
Fungsi INDEX Excel mengembalikan nilai yang ditampilkan berdasarkan posisi tertentu dari rentang atau larik.
Fungsi IF adalah salah satu fungsi paling sederhana dan paling berguna di buku kerja Excel. Ini melakukan tes logika sederhana yang bergantung pada hasil perbandingan, dan mengembalikan satu nilai jika hasilnya BENAR, atau nilai lain jika hasilnya SALAH.
Fungsi Excel ROW mengembalikan nomor baris referensi.
Rumus Terkait
Ambil kecocokan ke-n dengan VLOOKUP
Misalkan Anda memiliki daftar nilai dan informasi terkaitnya, untuk mengambil informasi terkait kecocokan nilai ke-n, Anda dapat menggunakan rumus berdasarkan fungsi VLOOKUP.
Ambil informasi yang terkait dengan nilai n terendah
Untuk mengambil data yang sesuai dengan nilai terkecil, terkecil ke-2, atau terkecil ke-n dalam daftar, tabel, atau baris di Excel, Anda bisa menggunakan rumus INDEX dan MATCH bersama dengan fungsi KECIL.
Dapatkan informasi yang sesuai dengan nilai maksimum
Untuk mengambil data yang sesuai dengan nilai maksimum dalam daftar, tabel, atau baris di Excel, Anda bisa menggunakan rumus INDEX dan MATCH bersama dengan fungsi MAX.
Dapatkan informasi yang sesuai dengan nilai minimum
Untuk mengambil data yang sesuai dengan nilai minimum dalam tabel, Anda bisa menggunakan rumus INDEX dan MATCH bersama dengan fungsi MIN.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
