Perhatikan gambar ini:
Misalkan Anda ingin membuat laporan dari ini seolah-olah Anda memfilter di Kawasan. Artinya, jika Anda memfilter di Utara, Anda akan melihat:
Tetapi bagaimana jika Anda menginginkan versi berbasis formula dari hal yang sama?
Inilah hasil yang Anda cari di kolom I: K:
Jelas, ini adalah laporan yang sama, tetapi tidak ada item yang difilter di sini. Jika Anda menginginkan laporan baru tentang East, alangkah baiknya jika hanya mengubah nilai di G1 menjadi East:
Begini caranya. Pertama-tama, ini belum selesai menggunakan VLOOKUP. Jadi saya berbohong tentang judul teknik ini!
Kolom F tidak ditampilkan sebelumnya, dan dapat disembunyikan (atau dipindahkan ke tempat lain sehingga tidak mengganggu laporan).
Apa yang ditampilkan di kolom F adalah nomor baris di mana G1 ditemukan di kolom A; artinya, baris apa yang berisi nilai "Utara"? Teknik ini melibatkan menggunakan sel di atas, sehingga harus dimulai pada setidaknya baris 2. Ini sesuai dengan nilai “Utara” terhadap kolom A, tapi bukannya seluruh kolom, menggunakan fungsi OFFSET: OFFSET($A$1,F1,0,1000,1).
Karena F1 adalah 0, ini OFFSET(A1,0,0,1000,1)adalah A1: A1000. (1000 itu sewenang-wenang, tetapi cukup besar untuk melakukan pekerjaan itu - Anda dapat membuatnya dengan nomor lain).
Nilai 2 di F2 adalah tempat "Utara" pertama berada. Anda juga ingin menambahkan kembali nilai F1 di akhir, tetapi sejauh ini nilainya nol.
"Ajaib" menjadi hidup di sel F3. Anda sudah tahu bahwa Utara pertama ditemukan di Baris 2. Jadi, Anda ingin mulai mencari dua baris di bawah A1. Anda dapat melakukannya dengan menetapkan 2 sebagai argumen kedua dari fungsi OFFSET.
Rumus di F3 secara otomatis akan menunjuk ke 2 yang dihitung di sel F2: Saat Anda menyalin rumus ke bawah, Anda akan melihat =OFFSET($A$1,F2,0,1000,1)yang OFFSET($A$1,2,0,1000,1)mana adalah A3: A1000. Jadi Anda mencocokkan Utara dengan rentang baru ini dan Utara berada di sel ketiga dari rentang baru ini, sehingga MATCH memberikan 3.
Dengan menambahkan kembali nilai dari sel di atas, F2, Anda akan melihat 3 ditambah 2, atau 5, yang merupakan baris yang berisi Utara kedua.
Rumus ini diisi cukup jauh untuk mendapatkan semua nilai.
Itu akan memberi Anda nomor baris di mana semua catatan Utara ditemukan.
Bagaimana Anda menerjemahkan nomor baris tersebut ke hasil di kolom I hingga K? Semuanya dilakukan dengan satu rumus. Masukkan formula ini dalam I2: =IFERROR(INDEX(A:A,$F2),””). Salin ke kanan lalu salin.
Mengapa menggunakan IFERROR? Dimana kesalahannya? Perhatikan sel F6 - ini berisi # N / A (itulah sebabnya Anda ingin menyembunyikan kolom F) karena tidak ada lagi Utara setelah baris 15. Jadi jika kolom F adalah kesalahan, kembalikan kosong. Jika tidak, ambil nilai dari kolom A (dan jika diisi dengan benar, B & C).
$ F2 adalah referensi absolut untuk kolom F sehingga hak mengisi tetap mengacu pada kolom F.
Artikel tamu ini berasal dari Excel MVP Bob Umlas. Ini adalah salah satu teknik favoritnya dari bukunya, Excel Outside the Box.
Excel Di Luar Kotak »
