Konten
- Cari di Excel dengan beberapa kriteria
- Kami mengekstrak nilai ke-2, ke-3, dst menggunakan VLOOKUP
- Ambil semua pengulangan dari nilai yang diinginkan
- Pencarian XNUMXD berdasarkan baris dan kolom yang diketahui
- Menggunakan beberapa VLOOKUP dalam satu formula
- Substitusi dinamis data dari tabel yang berbeda menggunakan VLOOKUP dan INDIRECT
Di bagian kedua dari tutorial kami tentang fungsi VPR (VLOOKUP) di Excel, kami akan menganalisis beberapa contoh yang akan membantu Anda mengarahkan semua kekuatan VPR untuk menyelesaikan tugas Excel yang paling ambisius. Contoh mengasumsikan bahwa Anda sudah memiliki pengetahuan dasar tentang cara kerja fitur ini. Jika tidak, Anda mungkin tertarik untuk memulai dengan bagian pertama dari tutorial ini, yang menjelaskan sintaks dan penggunaan dasar. VPR. Nah, mari kita mulai.
Cari di Excel dengan beberapa kriteria
fungsi VPR di Excel adalah alat yang sangat ampuh untuk melakukan pencarian nilai tertentu dalam database. Namun, ada batasan yang signifikan – sintaksnya memungkinkan Anda untuk mencari hanya satu nilai. Bagaimana jika Anda ingin mencari berdasarkan beberapa kondisi? Anda akan menemukan solusinya di bawah ini.
Contoh 1: Cari dengan 2 kriteria berbeda
Misalkan kita memiliki daftar pesanan dan kita ingin mencari Jumlah barang (Jumlah), berdasarkan dua kriteria – Nama Klien (Pelanggan) Название продукта (Produk). Hal tersebut diperumit dengan kenyataan bahwa setiap pembeli memesan beberapa jenis barang, seperti terlihat pada tabel di bawah ini:
fungsi reguler VPR tidak akan berfungsi dalam skenario ini karena akan mengembalikan nilai pertama yang ditemukan yang cocok dengan nilai pencarian yang diberikan. Misalnya, jika Anda ingin mengetahui jumlah suatu barang permen'dipesan oleh pembeli Jeremy Bukit, tuliskan rumus berikut:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– rumus ini akan mengembalikan hasilnya 15sesuai dengan produk Apel, karena itu adalah nilai pertama yang cocok.
Ada solusi sederhana – buat kolom tambahan untuk menggabungkan semua kriteria yang diinginkan. Dalam contoh kita, ini adalah kolom Nama Klien (Pelanggan) Название продукта (Produk). Jangan lupa bahwa kolom gabungan harus selalu menjadi kolom paling kiri dalam rentang pencarian, karena kolom kiri itulah yang berfungsi VPR melihat ke atas ketika mencari nilai.
Jadi, Anda menambahkan kolom tambahan ke tabel dan menyalin rumus berikut ke semua selnya: =B2&C2. Jika Anda ingin string lebih mudah dibaca, Anda dapat memisahkan nilai gabungan dengan spasi: =B2&» «&C2. Setelah itu, Anda dapat menggunakan rumus berikut:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Dimana selnya? B1 berisi nilai gabungan dari argumen nilai lookup (nilai_pencarian) dan 4 – Argumen col_index_num (column_number), yaitu jumlah kolom yang berisi data yang akan diambil.
Contoh 2: VLOOKUP dengan dua kriteria dengan tabel dilihat di lembar lain
Jika Anda perlu memperbarui tabel utama (Main table) dengan menambahkan data dari tabel kedua (Tabel pencarian), yang terletak di lembar lain atau di buku kerja Excel lain, maka Anda bisa mengumpulkan nilai yang diinginkan secara langsung dalam rumus yang Anda sisipkan ke dalam tabel utama.
Seperti pada contoh sebelumnya, Anda akan memerlukan kolom tambahan di tabel Pencarian dengan nilai gabungan. Kolom ini harus menjadi kolom paling kiri dalam rentang pencarian.
Jadi rumus dengan VPR bisa seperti ini:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Di sini, kolom B dan C masing-masing berisi nama pelanggan dan nama produk, serta tautannya Pesanan!$A&$2:$D$2 mendefinisikan tabel untuk mencari di lembar lain.
Untuk membuat rumus lebih mudah dibaca, Anda bisa memberi nama rentang tampilan, lalu rumus akan terlihat lebih sederhana:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Agar rumus berfungsi, nilai di kolom paling kiri dari tabel yang Anda lihat harus digabungkan dengan cara yang persis sama seperti dalam kriteria pencarian. Pada gambar di atas, kami menggabungkan nilai-nilai u2bu2band memberi spasi di antara mereka, dengan cara yang sama yang perlu Anda lakukan pada argumen pertama fungsi (BXNUMX& “" & CXNUMX).
Ingat! fungsi VPR terbatas pada 255 karakter, tidak dapat mencari nilai yang panjangnya lebih dari 255 karakter. Ingatlah hal ini dan pastikan panjang nilai yang diinginkan tidak melebihi batas ini.
Saya setuju bahwa menambahkan kolom tambahan bukanlah solusi yang paling elegan dan tidak selalu dapat diterima. Anda dapat melakukan hal yang sama tanpa kolom pembantu, tetapi itu akan membutuhkan rumus yang jauh lebih kompleks dengan kombinasi fungsi INDEKS (INDEX) dan MATCH (LEBIH TERBUKTI).
Kami mengekstrak nilai ke-2, ke-3, dst menggunakan VLOOKUP
Anda sudah tahu itu VPR dapat mengembalikan hanya satu nilai yang cocok, lebih tepatnya, yang pertama ditemukan. Tetapi bagaimana jika nilai ini diulang beberapa kali dalam array yang dilihat, dan Anda ingin mengekstrak yang ke-2 atau ke-3? Bagaimana jika semua nilai? Masalahnya tampak rumit, tetapi solusinya ada!
Misalkan satu kolom tabel berisi nama-nama pelanggan (Customer Name), dan kolom lainnya berisi produk (Product) yang mereka beli. Mari kita coba mencari item ke-2, ke-3 dan ke-4 yang dibeli oleh pelanggan tertentu.
Cara termudah adalah dengan menambahkan kolom bantu sebelum kolom Nama Pelanggan dan mengisinya dengan nama pelanggan dengan nomor pengulangan masing-masing nama, misalnya, John Doe1, John Doe2 dll. Kami akan melakukan trik dengan penomoran menggunakan fungsi COUNTIF (COUNTIF), mengingat nama pelanggan ada di kolom B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Setelah itu Anda dapat menggunakan fungsi normal VPRuntuk menemukan pesanan yang diperlukan. Sebagai contoh:
- Menemukan tanggal 2 barang yang dipesan oleh pelanggan Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Menemukan tanggal 3 barang yang dipesan oleh pelanggan Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Bahkan, Anda bisa memasukkan referensi sel sebagai nilai pencarian alih-alih teks, seperti yang ditunjukkan pada gambar berikut:
Jika Anda hanya mencari 2-e pengulangan, Anda dapat melakukannya tanpa kolom bantu dengan membuat rumus yang lebih kompleks:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Dalam rumus ini:
- $ F $ 2 – sel yang berisi nama pembeli (tidak berubah, harap dicatat – tautannya mutlak);
- $B $ – kolom Nama Pelanggan;
- Table4 – Meja Anda (tempat ini juga bisa menjadi tempat biasa);
- $ C16 – sel akhir tabel atau rentang Anda.
Rumus ini hanya menemukan nilai pencocokan kedua. Jika Anda perlu mengekstrak pengulangan yang tersisa, gunakan solusi sebelumnya.
Jika Anda memerlukan daftar semua kecocokan – fungsinya VPR ini bukan pembantu, karena hanya mengembalikan satu nilai pada satu waktu – periode. Tapi Excel memiliki fungsi INDEKS (INDEX), yang dapat dengan mudah mengatasi tugas ini. Bagaimana formula seperti itu akan terlihat, Anda akan belajar dalam contoh berikut.
Ambil semua pengulangan dari nilai yang diinginkan
Seperti yang disebutkan di atas VPR tidak dapat mengekstrak semua nilai duplikat dari rentang yang dipindai. Untuk melakukan ini, Anda memerlukan rumus yang sedikit lebih rumit, yang terdiri dari beberapa fungsi Excel, seperti: INDEKS (INDEKS), KECIL (KECIL) dan BARIS (GARIS)
Misalnya, rumus di bawah ini menemukan semua pengulangan nilai dari sel F2 dalam rentang B2:B16 dan mengembalikan hasil dari baris yang sama di kolom C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Masukkan rumus array ini ke beberapa sel yang berdekatan, seperti sel F4: F8seperti yang ditunjukkan pada gambar di bawah ini. Jumlah sel harus sama dengan atau lebih besar dari jumlah pengulangan maksimum yang mungkin dari nilai yang dicari. Jangan lupa klik Ctrl + Shift + Enteruntuk memasukkan rumus array dengan benar.
Jika Anda tertarik untuk memahami cara kerjanya, mari selami sedikit detail rumusnya:
Bagian 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – bandingkan nilai di sel F2 dengan masing-masing nilai dalam rentang B2:B16. Jika kecocokan ditemukan, maka ekspresi STRING(C2:C16)-1 mengembalikan jumlah baris yang sesuai (nilai -1 memungkinkan Anda untuk tidak menyertakan baris header). Jika tidak ada kecocokan, fungsinya IF (IF) mengembalikan string kosong.
Hasil fungsi IF (JIKA) akan ada larik horizontal seperti itu: {1,"",3,"",5,"","","","","","",12,"","",""}
Bagian 2:
ROW()-3
СТРОКА()-3
Disini fungsinya BARIS (LINE) bertindak sebagai counter tambahan. Karena rumus disalin ke dalam sel F4:F9, kita kurangi angkanya 3 dari hasil fungsi untuk mendapatkan nilai 1 di dalam sel F4 (baris 4, kurangi 3) untuk mendapatkan 2 di dalam sel F5 (baris 5, kurangi 3) dan seterusnya.
Bagian 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
fungsi KECIL (KECIL) kembali t-oh nilai terkecil dalam larik data. Dalam kasus kami, posisi mana (dari yang terkecil) untuk dikembalikan ditentukan oleh fungsi BARIS (LINE) (lihat Bagian 2). Jadi, untuk sel F4 fungsi KECIL({array},1) Pengembalian tanggal 1 (terkecil) elemen array, yaitu 1. Untuk sel F5 Pengembalian tanggal 2 elemen terkecil dalam array, yaitu 3, Dll
Bagian 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
fungsi INDEKS (INDEX) hanya mengembalikan nilai sel tertentu dalam array C2:C16. Untuk sel F4 fungsi INDEKS($C$2:$C$16) akan kembali ApelUntuk F5 fungsi INDEKS($C$2:$C$16) akan kembali permen' dan seterusnya.
Bagian 5:
IFERROR()
ЕСЛИОШИБКА()
Akhirnya, kami menempatkan rumus di dalam fungsi KESALAHAN (IFERROR), karena Anda tidak akan senang dengan pesan kesalahan #PADA (#N/A) jika jumlah sel tempat rumus disalin kurang dari jumlah nilai duplikat dalam rentang yang sedang dilihat.
Pencarian XNUMXD berdasarkan baris dan kolom yang diketahui
Melakukan pencarian XNUMXD di Excel melibatkan pencarian nilai dengan nomor baris dan kolom yang diketahui. Dengan kata lain, Anda mengekstrak nilai sel di persimpangan baris dan kolom tertentu.
Jadi, mari kita beralih ke tabel kita dan menulis rumus dengan fungsi VPR, yang akan menemukan informasi tentang harga lemon yang dijual di bulan Maret.
Ada beberapa cara untuk melakukan pencarian XNUMXD. Lihat opsi dan pilih salah satu yang paling cocok untuk Anda.
Fungsi VLOOKUP dan MATCH
Anda dapat menggunakan banyak fungsi VPR (VLOOKUP) dan LEBIH TERBUKTI (MATCH) untuk menemukan nilai di persimpangan bidang Название продукта (tali) dan Bulan (kolom) dari array yang dimaksud:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Rumus di atas adalah fungsi reguler VPR, yang mencari kecocokan persis dari nilai "Lemon" di sel A2 hingga A9. Tetapi karena Anda tidak tahu di kolom mana penjualan bulan Maret berada, Anda tidak akan bisa menyetel nomor kolom untuk argumen fungsi ketiga. VPR. Sebagai gantinya, fungsi tersebut digunakan LEBIH TERBUKTIuntuk menentukan kolom ini.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Diterjemahkan ke dalam bahasa manusia, rumus ini berarti:
- Kami mencari karakter "Mar" – argumen nilai lookup (nilai lookup);
- Mencari di sel dari A1 ke I1 – argumen pencarian_array (pencarian_array);
- Mengembalikan pencocokan tepat – argumen kecocokan_type (jenis_pertandingan).
Menggunakan 0 dalam argumen ketiga, Anda mengatakan fungsi LEBIH TERBUKTI cari nilai pertama yang sama persis dengan nilai yang Anda cari. Ini setara dengan nilai SALAH (SALAH) untuk argumen keempat VPR.
Ini adalah bagaimana Anda bisa membuat rumus pencarian dua arah di Excel, juga dikenal sebagai pencarian dua dimensi atau pencarian dua arah.
fungsi SUMPRODUCT
fungsi SUMPRODUK (SUMPRODUCT) mengembalikan jumlah produk dari array yang dipilih:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Fungsi INDEX dan MATCH
Pada artikel selanjutnya saya akan menjelaskan fungsi-fungsi tersebut secara detail, jadi untuk saat ini Anda bisa langsung menyalin rumus ini:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Rentang bernama dan operator persimpangan
Jika Anda tidak menyukai semua rumus Excel yang rumit itu, Anda mungkin menyukai cara visual dan mudah diingat ini:
- Pilih tabel, buka tab Rumus (Rumus) dan klik Buat dari Seleksi (Buat dari pilihan).
- Centang kotak Baris teratas (pada baris di atas) dan Kolom kiri (di kolom sebelah kiri). Microsoft Excel akan menetapkan nama ke rentang dari nilai di baris atas dan kolom kiri spreadsheet Anda. Sekarang Anda dapat mencari menggunakan nama-nama ini secara langsung tanpa membuat rumus.
- Di sel kosong mana pun, tulis =baris_namakolom_nama, misalnya seperti ini:
= Lemon Mar
… atau sebaliknya:
=Mar Lemon
Ingat bahwa nama baris dan kolom harus dipisahkan oleh spasi, yang dalam hal ini berfungsi seperti operator persimpangan.
Saat Anda memasukkan nama, Microsoft Excel akan menampilkan tooltip dengan daftar nama yang cocok, seperti saat Anda memasukkan rumus.
- Tekan Enter dan cek hasilnya
Secara umum, metode mana pun di atas yang Anda pilih, hasil pencarian dua dimensi akan sama:
Menggunakan beberapa VLOOKUP dalam satu formula
Kebetulan tabel utama dan tabel pencarian tidak memiliki satu kolom yang sama, dan ini mencegah Anda menggunakan fungsi yang biasa VPR. Namun, ada tabel lain yang tidak berisi informasi yang kami minati, tetapi memiliki kolom yang sama dengan tabel utama dan tabel pencarian.
Mari kita lihat contoh berikut. Kami memiliki tabel Utama dengan kolom SKU (baru), tempat Anda ingin menambahkan kolom dengan harga yang sesuai dari tabel lain. Selain itu, kami memiliki 2 tabel pencarian. Yang pertama (Tabel pencarian 1) berisi nomor yang diperbarui SKU (baru) dan nama produk, dan yang kedua (Tabel pencarian 2) – nama produk dan nomor lama SKU (lama).
Untuk menambahkan harga dari tabel pencarian kedua ke tabel utama, Anda harus melakukan tindakan yang dikenal sebagai double VPR atau bersarang VPR.
- Tulis fungsi VPR, yang menemukan nama produk di tabel Tabel pencarian 1menggunakan SKU, sebagai nilai yang diinginkan:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Sini Baru_SKU – rentang bernama $A:$B di meja Tabel pencarian 1, 2 – ini adalah kolom B, yang berisi nama-nama barang (lihat gambar di atas)
- Tulis rumus untuk memasukkan harga dari tabel Tabel pencarian 2 berdasarkan nama produk yang terkenal. Untuk melakukan ini, rekatkan rumus yang Anda buat sebelumnya sebagai nilai pencarian untuk fungsi baru VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Sini Harga – rentang bernama $ A: $ C di meja Tabel pencarian 2, 3 adalah kolom C yang berisi harga.
Gambar di bawah ini menunjukkan hasil yang dikembalikan oleh rumus yang kita buat:
Substitusi dinamis data dari tabel yang berbeda menggunakan VLOOKUP dan INDIRECT
Pertama, mari kita perjelas apa yang kita maksud dengan ungkapan “Substitusi dinamis data dari tabel yang berbeda” untuk memastikan kita saling memahami dengan benar.
Ada situasi ketika ada beberapa lembar dengan data dengan format yang sama, dan perlu untuk mengekstrak informasi yang diperlukan dari lembar tertentu, tergantung pada nilai yang dimasukkan dalam sel tertentu. Saya pikir lebih mudah untuk menjelaskan ini dengan sebuah contoh.
Bayangkan Anda memiliki laporan penjualan untuk beberapa wilayah dengan produk yang sama dan dalam format yang sama. Anda ingin menemukan angka penjualan untuk wilayah tertentu:
Jika Anda hanya memiliki dua laporan seperti itu, maka Anda dapat menggunakan rumus sederhana yang memalukan dengan fungsi VPR и IF (JIKA) untuk memilih laporan yang ingin dicari:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Dimana:
- $D $2 adalah sel yang berisi nama produk. Perhatikan bahwa kami menggunakan referensi absolut di sini untuk menghindari perubahan nilai pencarian saat menyalin rumus ke sel lain.
- $ D3 adalah sel dengan nama wilayah. Kami menggunakan referensi kolom absolut dan referensi baris relatif karena kami berencana untuk menyalin rumus ke sel lain di kolom yang sama.
- FL_Sales и CA_Penjualan – nama tabel (atau rentang bernama) yang berisi laporan penjualan terkait. Anda dapat, tentu saja, menggunakan nama lembar biasa dan referensi rentang sel, misalnya 'Lembar FL'!$A$3:$B$10, tetapi rentang bernama jauh lebih nyaman.
Namun, ketika ada banyak tabel seperti itu, fungsinya IF bukanlah solusi terbaik. Sebagai gantinya, Anda dapat menggunakan fungsi TIDAK LANGSUNG (INDIRECT) untuk mengembalikan rentang pencarian yang diinginkan.
Seperti yang mungkin Anda ketahui, fungsinya TIDAK LANGSUNG digunakan untuk mengembalikan tautan yang diberikan oleh string teks, yang persis seperti yang kita butuhkan sekarang. Jadi, ganti dengan berani dalam rumus di atas ekspresi dengan fungsi IF untuk menghubungkan dengan fungsi TIDAK LANGSUNG. Berikut adalah kombinasi VPR и TIDAK LANGSUNG bekerja sangat baik dengan:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Dimana:
- $D $2 – ini adalah sel dengan nama produk, tidak berubah karena tautan absolut.
- $ D3 adalah sel yang berisi bagian pertama dari nama wilayah. Dalam contoh kita, ini FL.
- _Penjualan – bagian umum dari nama semua rentang atau tabel bernama. Ketika digabungkan dengan nilai di sel D3, itu membentuk nama yang sepenuhnya memenuhi syarat dari rentang yang diperlukan. Di bawah ini adalah beberapa detail untuk mereka yang baru mengenal fungsi ini TIDAK LANGSUNG.
Cara kerja INDIRECT dan VLOOKUP
Pertama, izinkan saya mengingatkan Anda tentang sintaks fungsi TIDAK LANGSUNG (TIDAK LANGSUNG):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Argumen pertama dapat berupa referensi sel (gaya A1 atau R1C1), nama rentang, atau string teks. Argumen kedua menentukan gaya tautan apa yang terkandung dalam argumen pertama:
- A1jika argumennya adalah KODE BENAR (BENAR) atau tidak ditentukan;
- R1C1, Jika FSEBAGAI E (SALAH).
Dalam kasus kami, tautan memiliki gaya A1, sehingga Anda dapat mengabaikan argumen kedua dan fokus pada argumen pertama.
Jadi mari kita kembali ke laporan penjualan kita. Jika Anda ingat, maka setiap laporan adalah tabel terpisah yang terletak di lembar terpisah. Agar rumus berfungsi dengan benar, Anda harus memberi nama tabel (atau rentang), dan semua nama harus memiliki bagian yang sama. Misalnya seperti ini: CA_Penjualan, FL_Penjualan, TX_Penjualan dan seterusnya. Seperti yang Anda lihat, "_Sales" hadir di semua nama.
fungsi TIDAK LANGSUNG menghubungkan nilai di kolom D dan string teks “_Sales”, dengan demikian memberi tahu VPR di mana tabel untuk mencari. Jika sel D3 berisi nilai "FL", rumus akan mencari tabel FL_Penjualan, jika “CA” – dalam tabel CA_Penjualan dan seterusnya.
Hasil dari fungsi VPR и TIDAK LANGSUNG akan menjadi berikut:
Jika data terletak di buku Excel yang berbeda, maka Anda perlu menambahkan nama buku sebelum rentang bernama, misalnya:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Jika fungsi TIDAK LANGSUNG merujuk ke buku kerja lain, buku kerja itu harus terbuka. Jika ditutup, fungsi akan melaporkan kesalahan. # REF! (#SSIL!).