Contents [show]
- Cari dalam Excel mengikut berbilang kriteria
- Kami mengekstrak nilai ke-2, ke-3, dsb. menggunakan VLOOKUP
- Dapatkan semua ulangan nilai yang dikehendaki
- Carian XNUMXD mengikut baris dan lajur yang diketahui
- Menggunakan berbilang VLOOKUP dalam satu formula
- Penggantian dinamik data daripada jadual berbeza menggunakan VLOOKUP dan TIDAK LANGSUNG
Dalam bahagian kedua tutorial kami mengenai fungsi VPR (VLOOKUP) dalam Excel, kami akan menganalisis beberapa contoh yang akan membantu anda mengarahkan semua kuasa VPR untuk menyelesaikan tugas Excel yang paling bercita-cita tinggi. Contoh mengandaikan bahawa anda sudah mempunyai pengetahuan asas tentang cara ciri ini berfungsi. Jika tidak, anda mungkin berminat untuk memulakan dengan bahagian pertama tutorial ini, yang menerangkan sintaks dan penggunaan asas. VPR. Baiklah, mari kita mulakan.
Cari dalam Excel mengikut berbilang kriteria
fungsi VPR dalam Excel ialah alat yang sangat berkuasa untuk melakukan carian untuk nilai tertentu dalam pangkalan data. Walau bagaimanapun, terdapat had yang ketara - sintaksnya membolehkan anda mencari hanya satu nilai. Bagaimana jika anda ingin mencari mengikut berbilang syarat? Anda akan dapati penyelesaiannya di bawah.
Contoh 1: Cari mengikut 2 kriteria berbeza
Katakan kita mempunyai senarai pesanan dan kita ingin mencari Kuantiti barang (Qty.), berdasarkan dua kriteria - Nama pelanggan (Pelanggan) dan Название продукта (Produk). Perkara ini rumit oleh fakta bahawa setiap pembeli memesan beberapa jenis barang, seperti yang dapat dilihat dari jadual di bawah:
fungsi biasa VPR tidak akan berfungsi dalam senario ini kerana ia akan mengembalikan nilai pertama yang ditemuinya yang sepadan dengan nilai carian yang diberikan. Sebagai contoh, jika anda ingin mengetahui kuantiti sesuatu barang gula-gula'dipesan oleh pembeli Bukit Jeremy, tulis formula berikut:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– formula ini akan mengembalikan hasilnya 15sepadan dengan produk Apples, kerana ia adalah nilai pertama yang sepadan.
Terdapat penyelesaian yang mudah - buat lajur tambahan untuk menggabungkan semua kriteria yang dikehendaki. Dalam contoh kami, ini ialah lajur Nama pelanggan (Pelanggan) dan Название продукта (Produk). Jangan lupa bahawa lajur yang digabungkan mesti sentiasa menjadi lajur paling kiri dalam julat carian, kerana lajur kiri berfungsi VPR mendongak apabila mencari nilai.
Jadi, anda menambah lajur tambahan pada jadual dan menyalin formula berikut pada semua selnya: =B2&C2. Jika anda mahu rentetan menjadi lebih mudah dibaca, anda boleh memisahkan nilai gabungan dengan ruang: =B2&» «&C2. Selepas itu, anda boleh menggunakan formula 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;ЛОЖЬ)
Di mana sel B1 mengandungi nilai gabungan hujah lookup_value (nilai_carian) dan 4 – Hujah col_index_num (column_number), iaitu nombor lajur yang mengandungi data yang hendak diambil.
Contoh 2: VLOOKUP mengikut dua kriteria dengan jadual dilihat pada helaian lain
Jika anda perlu mengemas kini jadual utama (Jadual utama) dengan menambah data daripada jadual kedua (Jadual Carian), yang terletak pada helaian lain atau dalam buku kerja Excel lain, maka anda boleh mengumpul nilai yang dikehendaki terus dalam formula yang anda masukkan. ke dalam jadual utama.
Seperti dalam contoh sebelumnya, anda memerlukan lajur tambahan dalam jadual Carian dengan nilai gabungan. Lajur ini mestilah lajur paling kiri dalam julat carian.
Jadi formula dengan VPR boleh jadi begini:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Di sini, lajur B dan C mengandungi nama pelanggan dan nama produk, masing-masing, dan pautan Pesanan!$A&$2:$D$2 mentakrifkan jadual untuk dicari dalam helaian lain.
Untuk menjadikan formula lebih mudah dibaca, anda boleh memberi nama julat paparan, dan kemudian formula akan kelihatan lebih mudah:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Untuk formula berfungsi, nilai dalam lajur paling kiri jadual yang anda lihat mesti digabungkan dengan cara yang sama seperti dalam kriteria carian. Dalam rajah di atas, kami menggabungkan nilai u2bu2band meletakkan ruang di antara mereka, dengan cara yang sama anda perlu lakukan dalam hujah pertama fungsi (BXNUMX& “” & CXNUMX).
Ingat! fungsi VPR terhad kepada 255 aksara, ia tidak boleh mencari nilai yang panjangnya melebihi 255 aksara. Ingat perkara ini dan pastikan panjang nilai yang dikehendaki tidak melebihi had ini.
Saya bersetuju bahawa menambah lajur tambahan bukanlah penyelesaian yang paling elegan dan tidak selalu boleh diterima. Anda boleh melakukan perkara yang sama tanpa lajur pembantu, tetapi itu memerlukan formula yang lebih kompleks dengan gabungan fungsi INDEKS (INDEX) dan MATCH (LEBIH TERDEDAH).
Kami mengekstrak nilai ke-2, ke-3, dsb. menggunakan VLOOKUP
Anda sudah tahu bahawa VPR boleh mengembalikan hanya satu nilai yang sepadan, lebih tepat lagi, yang pertama ditemui. Tetapi bagaimana jika nilai ini diulang beberapa kali dalam tatasusunan yang dilihat, dan anda mahu mengekstrak nilai ke-2 atau ke-3 daripadanya? Bagaimana jika semua nilai? Masalahnya nampak rumit, tetapi penyelesaiannya wujud!
Katakan satu lajur jadual mengandungi nama pelanggan (Nama Pelanggan) dan lajur lain mengandungi produk (Produk) yang mereka beli. Jom cuba cari barang ke-2, ke-3 dan ke-4 yang dibeli oleh pelanggan tertentu.
Cara paling mudah ialah menambah lajur tambahan sebelum lajur Nama Pelanggan dan isikannya dengan nama pelanggan dengan nombor ulangan setiap nama, contohnya, John Doe1, John Doe2 dll. Kami akan melakukan silap mata dengan penomboran menggunakan fungsi tersebut COUNTIF (COUNTIF), memandangkan nama pelanggan berada dalam lajur B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Selepas itu anda boleh menggunakan fungsi biasa VPRuntuk mencari pesanan yang diperlukan. Sebagai contoh:
- Cari 2hb barang yang ditempah oleh pelanggan dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Cari 3hb barang yang ditempah oleh pelanggan dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Malah, anda boleh memasukkan rujukan sel sebagai nilai carian dan bukannya teks, seperti yang ditunjukkan dalam rajah berikut:
Jika anda hanya mencari 2-e pengulangan, anda boleh melakukannya tanpa lajur tambahan dengan mencipta formula 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 formula ini:
- $F$2 – sel yang mengandungi nama pembeli (ia tidak berubah, sila ambil perhatian – pautan adalah mutlak);
- $ B $ - kolum Nama Pelanggan;
- Table4 – Meja anda (tempat ini juga boleh menjadi julat biasa);
- $C16 – sel akhir jadual atau julat anda.
Formula ini hanya mencari nilai padanan kedua. Jika anda perlu mengekstrak ulangan yang tinggal, gunakan penyelesaian sebelumnya.
Jika anda memerlukan senarai semua padanan – fungsinya VPR ini bukan pembantu, kerana ia hanya mengembalikan satu nilai pada satu masa – tempoh. Tetapi Excel mempunyai fungsi INDEKS (INDEX), yang boleh mengatasi tugas ini dengan mudah. Bagaimana formula sedemikian akan kelihatan, anda akan belajar dalam contoh berikut.
Dapatkan semua ulangan nilai yang dikehendaki
Seperti yang dinyatakan di atas VPR tidak boleh mengekstrak semua nilai pendua daripada julat yang diimbas. Untuk melakukan ini, anda memerlukan formula yang lebih kompleks sedikit, terdiri daripada beberapa fungsi Excel, seperti INDEKS (INDEX), KECIL (KECIL) dan ROW (LINE)
Sebagai contoh, formula di bawah mencari semua ulangan nilai daripada sel F2 dalam julat B2:B16 dan mengembalikan hasil daripada baris yang sama dalam lajur 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 formula tatasusunan ini ke dalam berbilang sel bersebelahan, seperti sel F4: F8seperti yang ditunjukkan dalam rajah di bawah. Bilangan sel mestilah sama atau lebih besar daripada bilangan maksimum pengulangan nilai yang dicari. Jangan lupa klik Ctrl + Shift + Enteruntuk memasukkan formula tatasusunan dengan betul.
Jika anda berminat untuk memahami cara ia berfungsi, mari kita menyelami butiran formula sedikit:
1 bahagian:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – bandingkan nilai dalam sel F2 dengan setiap nilai dalam julat B2:B16. Jika padanan ditemui, maka ungkapan STRING(C2:C16)-1 mengembalikan nombor baris yang sepadan (nilai -1 membenarkan anda untuk tidak memasukkan baris pengepala). Jika tiada padanan, fungsinya IF (IF) mengembalikan rentetan kosong.
Hasil fungsi IF (JIKA) akan ada tatasusunan mendatar sedemikian: {1,"",3,"",5,"","","","","","",12,"","",""}
2 bahagian:
ROW()-3
СТРОКА()-3
Di sini fungsinya ROW (LINE) bertindak sebagai kaunter tambahan. Oleh kerana formula disalin ke dalam sel F4:F9, kami menolak nombor itu 3 daripada hasil fungsi untuk mendapatkan nilai 1 dalam sel F4 (baris 4, tolak 3) untuk mendapatkan 2 dalam sel F5 (baris 5, tolak 3) dan seterusnya.
3 bahagian:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
fungsi KECIL (KECIL) pulangan n-oh nilai terkecil dalam tatasusunan data. Dalam kes kami, kedudukan mana (dari yang terkecil) untuk dikembalikan ditentukan oleh fungsi ROW (LINE) (lihat Bahagian 2). Jadi, untuk sel F4 fungsi KECIL({array},1) pulangan 1hb elemen tatasusunan (terkecil), iaitu 1. Untuk sel F5 pulangan 2hb elemen terkecil dalam tatasusunan, iaitu 3, Dll
4 bahagian:
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 tatasusunan C2:C16. Untuk sel F4 fungsi INDEKS ($C$2:$C$16) akan kembali ApplesUntuk F5 fungsi INDEKS ($C$2:$C$16) akan kembali gula-gula' dan sebagainya.
5 bahagian:
IFERROR()
ЕСЛИОШИБКА()
Akhirnya, kami meletakkan formula di dalam fungsi IFERROR (IFERROR), kerana anda mungkin tidak berpuas hati dengan mesej ralat #AT (#N/A) jika bilangan sel di mana formula disalin adalah kurang daripada bilangan nilai pendua dalam julat yang dilihat.
Carian XNUMXD mengikut baris dan lajur yang diketahui
Melakukan carian XNUMXD dalam Excel melibatkan pencarian nilai dengan nombor baris dan lajur yang diketahui. Dalam erti kata lain, anda mengekstrak nilai sel di persimpangan baris dan lajur tertentu.
Jadi, mari kita beralih ke jadual kami dan tulis formula dengan fungsi VPR, yang akan mencari maklumat tentang kos lemon yang dijual pada bulan Mac.
Terdapat beberapa cara untuk melakukan carian XNUMXD. Semak pilihan dan pilih yang paling sesuai dengan anda.
Fungsi VLOOKUP dan MATCH
Anda boleh menggunakan sekumpulan fungsi VPR (VLOOKUP) dan LEBIH TERDEDAH (MATCH) untuk mencari nilai di persimpangan medan Название продукта (tali) dan bulan (lajur) tatasusunan yang dipersoalkan:
=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);ЛОЖЬ)
Formula di atas adalah fungsi biasa VPR, yang mencari padanan tepat nilai "Lemon" dalam sel A2 hingga A9. Tetapi oleh kerana anda tidak tahu lajur mana jualan Mac berada, anda tidak akan dapat menetapkan nombor lajur untuk hujah fungsi ketiga. VPR. Sebaliknya, fungsi itu digunakan LEBIH TERDEDAHuntuk menentukan lajur ini.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Diterjemah ke dalam bahasa manusia, formula ini bermaksud:
- Kami sedang mencari watak "Mar" - hujah lookup_value (nilai_cari);
- Melihat dalam sel dari A1 hingga I1 – hujah lookup_array (susun_cari);
- Mengembalikan padanan tepat – hujah padanan_jenis (jenis_perlawanan).
Menggunakan 0 dalam hujah ketiga, anda sebut fungsi LEBIH TERDEDAH cari nilai pertama yang betul-betul sepadan dengan nilai yang anda cari. Ini bersamaan dengan nilai SALAH (SALAH) untuk hujah keempat VPR.
Beginilah cara anda boleh mencipta formula carian dua hala dalam Excel, juga dikenali sebagai carian dua dimensi atau carian dua arah.
Fungsi SUMPRODUCT
fungsi SUMPRODUCT (SUMPRODUCT) mengembalikan jumlah produk tatasusunan 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
Dalam artikel seterusnya saya akan menerangkan fungsi ini secara terperinci, jadi buat masa ini anda hanya boleh menyalin formula 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))
Nama julat dan pengendali persimpangan
Jika anda tidak menyukai semua formula Excel yang kompleks itu, anda mungkin menyukai cara visual dan tidak dapat dilupakan ini:
- Pilih jadual, buka tab Formula (Formula) dan klik Buat daripada Pilihan (Buat daripada pilihan).
- Tandakan kotak Barisan teratas (pada baris di atas) dan Lajur kiri (dalam lajur di sebelah kiri). Microsoft Excel akan memberikan nama kepada julat daripada nilai di baris atas dan lajur kiri hamparan anda. Kini anda boleh mencari menggunakan nama-nama ini secara langsung tanpa membuat formula.
- Dalam mana-mana sel kosong, tulis =nama_baris nama_lajur, contohnya seperti ini:
=Lemon Mar
… atau sebaliknya:
=Lemon Mar
Ingat bahawa nama baris dan lajur mesti dipisahkan oleh ruang, yang dalam kes ini berfungsi seperti pengendali persimpangan.
Apabila anda memasukkan nama, Microsoft Excel akan menunjukkan petua alat dengan senarai nama yang sepadan, sama seperti semasa anda memasukkan formula.
- Keratan Akhbar Masukkan dan semak hasilnya
Secara umum, mana-mana kaedah di atas yang anda pilih, hasil carian dua dimensi adalah sama:
Menggunakan berbilang VLOOKUP dalam satu formula
Ia berlaku bahawa jadual utama dan jadual carian tidak mempunyai satu lajur yang sama, dan ini menghalang anda daripada menggunakan fungsi biasa VPR. Walau bagaimanapun, terdapat satu lagi jadual yang tidak mengandungi maklumat yang kami minati, tetapi mempunyai lajur biasa dengan jadual utama dan jadual carian.
Mari kita lihat contoh berikut. Kami mempunyai jadual Utama dengan lajur SKU (baharu), tempat anda ingin menambah lajur dengan harga yang sepadan daripada jadual lain. Selain itu, kami mempunyai 2 jadual carian. Yang pertama (Jadual Carian 1) mengandungi nombor yang dikemas kini SKU (baharu) dan nama produk, dan yang kedua (Jadual Carian 2) – nama produk dan nombor lama SKU (lama).
Untuk menambah harga daripada jadual carian kedua kepada jadual utama, anda mesti melakukan tindakan yang dikenali sebagai berganda VPR atau bersarang VPR.
- Tulis fungsi VPR, yang mencari nama produk dalam jadual Jadual carian 1menggunakan SKU, sebagai nilai yang dikehendaki:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Di sini SKU_baharu – julat bernama $A:$B di dalam jadual Jadual carian 1, 2 – ini adalah lajur B, yang mengandungi nama barang (lihat gambar di atas)
- Tulis formula untuk memasukkan harga daripada jadual Jadual carian 2 berdasarkan nama produk yang terkenal. Untuk melakukan ini, tampalkan formula yang anda buat sebelum ini sebagai nilai carian untuk fungsi baharu VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Di sini Harga – julat bernama $A:$C di dalam jadual Jadual carian 2, 3 ialah lajur C yang mengandungi harga.
Rajah di bawah menunjukkan hasil yang dikembalikan oleh formula yang kami buat:
Penggantian dinamik data daripada jadual berbeza menggunakan VLOOKUP dan TIDAK LANGSUNG
Mula-mula, mari kita jelaskan apa yang kita maksudkan dengan ungkapan "Penggantian dinamik data daripada jadual yang berbeza" untuk memastikan kita memahami satu sama lain dengan betul.
Terdapat situasi apabila terdapat beberapa helaian dengan data format yang sama, dan adalah perlu untuk mengekstrak maklumat yang diperlukan daripada helaian tertentu, bergantung pada nilai yang dimasukkan dalam sel tertentu. Saya rasa lebih mudah untuk menerangkan perkara ini dengan contoh.
Bayangkan anda mempunyai laporan jualan untuk beberapa wilayah dengan produk yang sama dan dalam format yang sama. Anda ingin mencari angka jualan untuk wilayah tertentu:
Jika anda hanya mempunyai dua laporan sedemikian, maka anda boleh menggunakan formula mudah yang memalukan dengan fungsi VPR и IF (JIKA) untuk memilih laporan yang dikehendaki untuk dicari:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Di mana:
- $D$2 ialah sel yang mengandungi nama produk. Ambil perhatian bahawa kami menggunakan rujukan mutlak di sini untuk mengelakkan perubahan nilai carian apabila menyalin formula ke sel lain.
- $D3 ialah sel dengan nama rantau. Kami menggunakan rujukan lajur mutlak dan rujukan baris relatif kerana kami merancang untuk menyalin formula ke sel lain dalam lajur yang sama.
- FL_Sales и CA_Jualan – nama jadual (atau julat bernama) yang mengandungi laporan jualan yang sepadan. Anda boleh, sudah tentu, menggunakan nama helaian biasa dan rujukan julat sel, sebagai contoh 'FL Sheet'!$A$3:$B$10, tetapi julat yang dinamakan adalah lebih mudah.
Walau bagaimanapun, apabila terdapat banyak jadual sedemikian, fungsinya IF bukanlah penyelesaian terbaik. Sebaliknya, anda boleh menggunakan fungsi tersebut TIDAK LANGSUNG (TIDAK LANGSUNG) untuk mengembalikan julat carian yang dikehendaki.
Seperti yang anda ketahui, fungsinya TIDAK LANGSUNG digunakan untuk mengembalikan pautan yang diberikan oleh rentetan teks, iaitu apa yang kita perlukan sekarang. Jadi, dengan berani menggantikan dalam formula di atas ungkapan dengan fungsi IF untuk menghubungkan dengan fungsi TIDAK LANGSUNG. Berikut adalah gabungan VPR и TIDAK LANGSUNG berfungsi hebat dengan:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Di mana:
- $D$2 – ini adalah sel dengan nama produk, ia tidak berubah kerana pautan mutlak.
- $D3 ialah sel yang mengandungi bahagian pertama nama rantau. Dalam contoh kami, ini FL.
- _Jualan – bahagian biasa nama semua julat atau jadual yang dinamakan. Apabila digabungkan dengan nilai dalam sel D3, ia membentuk nama yang layak sepenuhnya bagi julat yang diperlukan. Di bawah adalah beberapa butiran untuk mereka yang baru menggunakan fungsi tersebut TIDAK LANGSUNG.
Cara TIDAK LANGSUNG dan VLOOKUP berfungsi
Pertama, izinkan saya mengingatkan anda sintaks fungsi tersebut TIDAK LANGSUNG (TIDAK LANGSUNG):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Argumen pertama boleh menjadi rujukan sel (gaya A1 atau R1C1), nama julat atau rentetan teks. Argumen kedua menentukan gaya pautan yang terkandung dalam argumen pertama:
- A1jika hujahnya KOD BENAR (BENAR) atau tidak dinyatakan;
- R1C1, Jika FAS E (SALAH).
Dalam kes kami, pautan mempunyai gaya A1, jadi anda boleh meninggalkan hujah kedua dan fokus pada yang pertama.
Jadi mari kita kembali kepada laporan jualan kami. Jika anda masih ingat, maka setiap laporan adalah jadual berasingan yang terletak pada helaian berasingan. Untuk formula berfungsi dengan betul, anda mesti menamakan jadual (atau julat) anda dan semua nama mesti mempunyai bahagian yang sama. Sebagai contoh, seperti ini: CA_Jualan, FL_Jualan, TX_Jualan dan sebagainya. Seperti yang anda lihat, "_Sales" terdapat dalam semua nama.
fungsi TIDAK LANGSUNG menghubungkan nilai dalam lajur D dan rentetan teks "_Sales", dengan itu memberitahu VPR dalam jadual mana yang hendak dicari. Jika sel D3 mengandungi nilai "FL", formula akan mencari jadual FL_Jualan, jika “CA” – dalam jadual CA_Jualan dan sebagainya.
Hasil daripada fungsi VPR и TIDAK LANGSUNG akan menjadi berikut:
Jika data terletak dalam buku Excel yang berbeza, maka anda perlu menambah nama buku sebelum julat yang dinamakan, contohnya:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Sekiranya fungsinya TIDAK LANGSUNG merujuk kepada buku kerja lain, buku kerja itu mesti dibuka. Jika ia ditutup, fungsi akan melaporkan ralat. #REF! (#SSYL!).