Kaji angka ini:
Katakan anda ingin menghasilkan laporan dari ini seolah-olah anda menyaring di Wilayah. Maksudnya, jika anda menapis di Utara, anda akan melihat:
Tetapi bagaimana jika anda mahukan versi berasaskan formula dari perkara yang sama?
Inilah hasil yang anda cari di lajur I: K:
Jelas, laporannya sama, tetapi tidak ada item yang disaring di sini. Sekiranya anda mahukan laporan baru di Timur, lebih baik menukar nilai di G1 ke Timur:
Inilah caranya. Pertama sekali, ia tidak dilakukan dengan menggunakan VLOOKUP. Oleh itu, saya berbohong mengenai tajuk teknik ini!
Lajur F tidak ditunjukkan sebelumnya, dan boleh disembunyikan (atau dipindahkan ke tempat lain sehingga tidak mengganggu laporan).
Apa yang ditunjukkan dalam lajur F ialah nombor baris di mana G1 dijumpai di lajur A; iaitu, baris apa yang mengandung nilai "Utara"? Teknik ini melibatkan penggunaan sel di atas, jadi ia mesti bermula pada sekurang-kurangnya baris 2. Ia sepadan dengan nilai yang "North" terhadap lajur A, tetapi bukan seluruh lajur, gunakan fungsi yang OFFSET: OFFSET($A$1,F1,0,1000,1).
Oleh kerana F1 adalah 0, ini OFFSET(A1,0,0,1000,1)adalah A1: A1000. (1000 adalah sewenang-wenangnya, tetapi cukup besar untuk melakukan tugas - anda boleh menjadikannya nombor lain).
Nilai 2 dalam F2 adalah tempat "Utara" pertama. Anda juga ingin menambahkan kembali nilai F1 pada akhir, tetapi sejauh ini adalah sifar.
"Sihir" itu hidup di dalam sel F3. Anda sudah tahu bahawa Utara pertama dijumpai di Baris 2. Oleh itu, anda ingin mula mencari dua baris di bawah A1. Anda boleh melakukannya dengan menetapkan 2 sebagai argumen kedua fungsi OFFSET.
Rumus dalam F3 akan secara automatik menunjuk ke 2 yang dihitung dalam sel F2: Apabila anda menyalin formula ke bawah, anda akan melihat =OFFSET($A$1,F2,0,1000,1)yang OFFSET($A$1,2,0,1000,1)mana adalah A3: A1000. Oleh itu, anda sepadan dengan Utara dengan julat baru ini dan ia menemukan Utara di sel ketiga julat baru ini, jadi PERTANDINGAN memberikan 3.
Dengan menambahkan kembali nilai dari sel di atas, F2, anda akan melihat 3 ditambah 2, atau 5, yang merupakan baris yang mengandungi Utara kedua.
Formula ini diisi cukup jauh untuk mendapatkan semua nilai.
Itu akan memberi anda nombor baris di mana semua rekod Utara dijumpai.
Bagaimana anda menerjemahkan nombor baris tersebut ke hasil di lajur I hingga K? Semuanya dilakukan dengan satu formula. Masukkan formula ini dalam I2: =IFERROR(INDEX(A:A,$F2),””). Salin ke kanan dan kemudian salin ke bawah.
Mengapa menggunakan IFERROR? Di mana ralat? Perhatikan sel F6 - ia mengandungi # N / A (itulah sebabnya anda mahu menyembunyikan lajur F) kerana tidak ada lagi baris utara selepas 15. Oleh itu, jika lajur F adalah ralat, kembalikan kosong. Jika tidak, ambil nilai dari lajur A (dan apabila diisi dengan betul, B & C).
$ F2 adalah rujukan mutlak pada lajur F sehingga isian kanan masih merujuk pada lajur F.
Artikel tetamu ini adalah dari Excel MVP Bob Umlas. Ini adalah salah satu teknik kegemarannya dari bukunya, Excel Outside the Box.
Excel Di Luar Kotak »
