Artikel ini didedikasikan untuk Fungsi VLOOKUP. Ia akan mempertimbangkan arahan langkah demi langkah untuk fungsi VLOOKUP, bertajuk "". Dalam artikel ini kita akan melihat secara terperinci perihalan, sintaks dan Contoh fungsi VLOOKUP dalam Excel. Kami juga akan melihat cara menggunakannya dan melihat kesilapan utama dan sebabnya. Fungsi Vlookup tidak berfungsi.

Sintaks dan perihalan fungsi VLOOKUP dalam Excel

Jadi, memandangkan tajuk kedua artikel ini ialah “ Fungsi VLOOKUP dalam Excel untuk boneka", mari kita mulakan dengan mengetahui apakah fungsi VLOOKUP dan apakah fungsinya? Fungsi VLOOKUP dalam bahasa Inggeris VLOOKUP, mencari nilai yang ditentukan dan mengembalikan nilai yang sepadan dari lajur lain.

Bagaimanakah fungsi VLOOKUP berfungsi?? Fungsi VLOOKUP dalam Excel mencari senarai data anda berdasarkan pengecam unik dan memberi anda sekeping maklumat yang dikaitkan dengan pengecam unik itu.

Huruf "V" dalam VPR bermaksud "menegak". Ia digunakan untuk membezakan Fungsi VLOOKUP dan GLOOKUP, yang mencari nilai dalam baris atas tatasusunan ("H" bermaksud "mendatar").

Fungsi VLOOKUP tersedia dalam semua versi Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Sintaks Fungsi VLOOKUP seperti berikut:

VLOOKUP(nilai_carian, jadual, nombor_lajur, [pencarian_selang])

Seperti yang anda lihat, Fungsi VLOOKUP mempunyai 4 parameter atau argumen. Tiga parameter pertama diperlukan, yang terakhir adalah pilihan.

1. nilai_cari ialah nilai carian.

Ini boleh sama ada nilai (nombor, tarikh atau teks) atau rujukan sel(rujukan kepada sel yang mengandungi nilai carian), atau nilai yang dikembalikan oleh beberapa fungsi Excel yang lain. Sebagai contoh:

• Cari nombor: =VLOOKUP(40; A2:B15; 2) - formula akan mencari nombor 40.
• Carian teks: =VLOOKUP(“epal”, A2:B15, 2) - formula akan mencari teks “epal”. Ambil perhatian bahawa anda sentiasa memasukkan nilai teks dalam "petikan berganda".
• Mencari nilai dari sel lain: =VLOOKUP(C2, A2:B15, 2) - formula akan mencari nilai dalam sel C2.

1. jadual ialah dua atau lebih lajur data.

ingat itu Fungsi VLOOKUP sentiasa mencari nilai yang dikehendaki dalam lajur pertama jadual. Jadual anda boleh mengandungi pelbagai nilai seperti teks, tarikh, nombor atau nilai boolean. Nilai kes tidak sensitif, yang bermaksud bahawa huruf besar dan huruf kecil dianggap sama.

Jadi formula kami =VLOOKUP(40, A2:B15, 2) akan mencari "40" dalam sel A2 hingga A15 kerana A ialah lajur pertama jadual A2:B15.

1. lajur_nombor - nombor lajur dalam jadual dari mana nilai dalam baris yang sepadan harus dikembalikan.

Lajur paling kiri dalam jadual yang ditentukan ialah 1, lajur kedua ialah 2, yang ketiga ialah 3, dsb.

4. interval_view menentukan sama ada anda sedang mencari padanan tepat (SALAH) atau padanan anggaran (BENAR atau ditinggalkan). Parameter terakhir ini adalah pilihan, tetapi sangat penting.

Fungsi VLOOKUP dalam contoh Excel

Sekarang mari kita lihat beberapa kes penggunaan Fungsi VLOOKUP untuk data sebenar.

Fungsi VLOOKUP pada helaian yang berbeza

Dalam amalan, formula VLOOKUP jarang digunakan untuk mencari data pada satu helaian. Lebih kerap daripada tidak, anda perlu mencari dan menarik data yang berkaitan dari helaian lain.

Kepada gunakan fungsi VLOOKUP daripada helaian Excel yang lain, anda mesti memasukkan nama lembaran kerja dan tanda seru dalam argumen jadual sebelum julat sel, contohnya, =VLOOKUP(40;Sheet2!A2:B15,2). Formula menunjukkan bahawa julat carian A2:B15 berada dalam Helaian2.

Sudah tentu, anda tidak perlu memasukkan nama helaian secara manual. Hanya mula menaip formula, dan apabila ia datang kepada hujah jadual, beralih kepada lembaran kerja carian dan pilih julat dengan tetikus anda.

Formula yang anda lihat dalam imej di bawah mencari teks dalam sel A2 ("Produk 3") dalam lajur A (lajur pertama julat carian A2:B9) pada lembaran kerja "Harga":

VLOOKUP(A2,Harga!$A$2:$B$8,2,FALSE)

Fungsi VLOOKUP dalam Excel - Fungsi VLOOKUP pada helaian berbeza

Cara menggunakan julat atau jadual bernama dalam formula VLOOKUP

Jika anda akan menggunakan julat carian yang sama dalam berbilang formula VLOOKUP, anda boleh mencipta dan memasukkan nama untuknya terus dalam argumen jadual formula VLOOKUP anda.

Untuk mencipta julat bernama, hanya pilih sel dan masukkan sebarang nama dalam kotak Nama di sebelah kiri panel Formula.

Fungsi VLOOKUP dalam Excel - Menamakan Julat

Kini anda boleh menulis formula VLOOKUP berikut untuk mendapatkan harga Produk 1:

VLOOKUP("Produk 1";Produk;2)

Fungsi VLOOKUP dalam Excel - Contoh fungsi VLOOKUP dengan nama julat

Kebanyakan nama julat dalam Excel digunakan pada keseluruhan buku kerja, jadi anda tidak perlu menentukan nama lembaran kerja, walaupun julat carian anda berada pada lembaran kerja yang berbeza. Formula sedemikian lebih mudah difahami. Selain itu, menggunakan julat bernama boleh menjadi alternatif yang baik kepada sel. Oleh kerana julat yang dinamakan tidak berubah apabila formula disalin ke sel lain, anda boleh yakin bahawa julat carian anda akan sentiasa betul.

Jika anda telah menukar julat sel kepada jadual Excel berciri penuh (Sisip tab -> Jadual), anda boleh memilih julat carian dengan tetikus anda dan Microsoft Excel akan secara automatik menambah nama lajur atau nama jadual pada formula :

Fungsi VLOOKUP dalam Excel - Contoh fungsi VLOOKUP dengan nama jadual

Formula penuh mungkin kelihatan seperti ini:

VLOOKUP("Produk 1";Jadual6[[Produk]:[Harga]];2)

atau pun =VLOOKUP("Produk 1";Jadual6;2).

Seperti julat yang dinamakan, nama lajur adalah tetap dan rujukan sel tidak akan berubah tidak kira di mana formula VLOOKUP disalin.

Fungsi VLOOKUP dengan pelbagai syarat

Mari kita pertimbangkan contoh fungsi VLOOKUP dengan beberapa syarat. Kami mempunyai data input berikut:

Fungsi VLOOKUP dalam Excel - Jadual data sumber

Katakan kita perlu menggunakan Fungsi VLOOKUP dengan beberapa syarat. Sebagai contoh, untuk mencari harga produk menggunakan dua kriteria: nama produk dan jenisnya.

1. Untuk menggunakan Fungsi VLOOKUP dengan beberapa syarat adalah perlu untuk memasukkan lajur tambahan pada permulaan yang akan menyimpan maklumat dengan nama dan jenis produk.

Jadi, pada helaian "Harga", masukkan lajur dan dalam sel A2 masukkan formula berikut:

Menggunakan formula ini, kami memperoleh nilai lajur "Produk" dan "Jenis". Isi semua sel.

Sekarang jadual carian kelihatan seperti ini:

Fungsi VLOOKUP dalam Excel - Menambah Lajur Pembantu

1. Sekarang dalam sel C2 pada helaian "Jualan" kami akan menulis formula VLOOKUP berikut:

VLOOKUP(A2Harga!$A$1:$D$8,4,FALSE)

Kami mengisi sel yang tinggal dan hasilnya kami mendapat harga untuk setiap produk mengikut jenis:

Fungsi VLOOKUP dalam Excel - Contoh VLOOKUP dengan Berbilang Syarat

Sekarang mari kita lihat Ralat fungsi VLOOKUP.

Mengapa fungsi VLOOKUP tidak berfungsi?

Dalam bahagian artikel ini kita akan melihat mengapa Fungsi Vlookup tidak berfungsi dan mungkin Ralat fungsi VLOOKUP.

Jenis ralat	sebab	Penyelesaian
	Lokasi lajur yang sedang dicari adalah salah	Lajur jadual di mana carian dilakukan MESTIlah yang paling kiri. Alihkan lajur yang anda cari ke hujung kiri jadual. Atau buat lajur pendua tambahan di sebelah kiri dalam jadual.
	Julat jadual tidak tetap	Jika nilai pertama dipaparkan dengan betul dan selepas meregangkan formula VLOOKUP, ralat #N/A ditemui dalam beberapa sel, maka julat jadual tidak tetap. Gunakan ($) untuk mengunci julat jadual supaya formula menggunakan julat yang sama apabila diisi. Atau gunakan
	Tidak dapat mencari padanan tepat (jika dalam tontonan selang masa cari nilai tepat yang dipilih (0)
	DALAM tontonan selang masa carian untuk nilai terdekat (1) dilakukan, dan jadual yang mana carian dilakukan tidak diisih.	Isih lajur pertama jadual dalam susunan menaik nama.
		Gunakan fungsi TYPE atau SPACE.
	Nilai nombor lajur melebihi bilangan lajur dalam jadual	Semak nombor lajur yang mengandungi nilai pulangan.
	Petikan tiada dalam formula	Jika anda menggunakan sebagai nilai yang dikehendaki bukan pautan sel, tetapi teks, maka ia mesti disertakan dalam petikan. Sebagai contoh: VLOOKUP("Produk 1"; Harga!$A$2:$B$8,2,0)

Saya berharap bahawa sekarang juga untuk fungsi VLOOKUP dummies dalam Excel akan menjadi jelas.

Batyanov Denis Sebagai pengarang tetamu, dia bercakap dalam siaran ini tentang cara mencari data dalam satu jadual Excel dan mengekstraknya ke dalam yang lain, dan juga mendedahkan semua rahsia fungsi paparan menegak.

Menggunakan fungsi COLUMN untuk menentukan lajur pengekstrakan

Jika jadual yang anda ambil semula data menggunakan VLOOKUP mempunyai struktur yang sama seperti jadual carian, tetapi hanya mengandungi lebih sedikit baris, maka VLOOKUP boleh menggunakan fungsi COLUMN() untuk mengira nombor lajur untuk diambil secara automatik. Dalam kes ini, semua formula VLOOKUP akan sama (dilaraskan untuk parameter pertama, yang berubah secara automatik)! Sila ambil perhatian bahawa parameter pertama mempunyai koordinat lajur mutlak.

Mencipta kunci komposit menggunakan &»|»&

Sekiranya terdapat keperluan untuk mencari dalam beberapa lajur pada masa yang sama, maka perlu membuat kunci komposit untuk carian. Jika nilai yang dikembalikan bukan teks (seperti yang berlaku dengan medan "Kod"), tetapi angka, maka formula SUMIFS yang lebih mudah akan sesuai untuk ini dan kunci lajur komposit tidak diperlukan sama sekali.

Ini adalah artikel pertama saya untuk Lifehacker. Jika anda menyukainya, maka saya menjemput anda untuk melawat laman web saya, dan saya juga akan gembira membaca dalam komen tentang rahsia anda menggunakan fungsi VLOOKUP dan seumpamanya. Terima kasih. :)

Bekerja dengan jadual ringkasan melibatkan penarikan nilai dari jadual lain ke dalamnya. Jika terdapat banyak jadual, pemindahan manual akan mengambil masa yang besar, dan jika data sentiasa dikemas kini, maka ini akan menjadi tugas Sisyphean. Nasib baik, terdapat fungsi VLOOKUP yang menawarkan keupayaan untuk mengambil data secara automatik. Mari lihat contoh khusus tentang cara fungsi ini berfungsi.

Nama fungsi VLOOKUP bermaksud "fungsi melihat menegak". Dalam bahasa Inggeris namanya VLOOKUP. Fungsi ini mencari data dalam lajur kiri julat yang sedang diperiksa dan kemudian mengembalikan nilai yang terhasil dalam sel yang ditentukan. Ringkasnya, VLOOKUP membolehkan anda menyusun semula nilai dari sel dalam satu jadual ke jadual lain. Mari ketahui cara menggunakan fungsi VLOOKUP dalam Excel.

Contoh penggunaan VLOOKUP

Mari kita lihat bagaimana fungsi VLOOKUP berfungsi menggunakan contoh khusus.

Kami mempunyai dua meja. Yang pertama ialah jadual pembelian di mana nama produk makanan diletakkan. Di ruangan seterusnya selepas nama adalah nilai kuantiti produk yang perlu dibeli. Seterusnya datang harga. Dan dalam lajur terakhir - jumlah kos pembelian produk tertentu, yang dikira menggunakan formula yang telah dimasukkan ke dalam sel untuk mendarabkan kuantiti dengan harga. Tetapi kita hanya perlu menaikkan harga menggunakan fungsi VLOOKUP dari jadual bersebelahan, iaitu senarai harga.

Seperti yang anda lihat, harga kentang telah ditambah ke jadual dari senarai harga. Untuk tidak melalui prosedur yang rumit dengan nama produk lain, kami hanya berdiri di sudut kanan bawah sel yang diisi supaya salib muncul. Kami melukis salib ini ke bahagian paling bawah meja.

Oleh itu, kami menarik semua data yang diperlukan dari satu jadual ke jadual lain menggunakan fungsi VLOOKUP.

Seperti yang anda lihat, fungsi VLOOKUP tidaklah begitu rumit seperti yang kelihatan pada pandangan pertama. Memahami penggunaannya tidak begitu sukar, tetapi menguasai alat ini akan menjimatkan banyak masa apabila bekerja dengan jadual.

Microsoft Excel mempunyai banyak fungsi berbeza yang menjadikan kerja pengguna lebih mudah, dan dalam artikel ini kita akan membincangkan salah satu daripadanya. Ia dipanggil VLOOKUP, dan jika dalam bahasa Inggeris, maka VLOOKUP.

Fungsi VLOOKUP memindahkan nilai dari satu jadual ke sel tertentu dalam yang lain. Biar saya terangkan dengan lebih terperinci - dalam jadual pertama anda memilih nilai yang anda perlu cari dalam lajur kiri kedua. Jika terdapat padanan, maka VLOOKUP memindahkan nilai dari lajur yang anda tentukan dalam baris ini ke jadual pertama. Walaupun definisinya sedikit mengelirukan, menggunakan fungsi itu tidaklah begitu sukar. Mari lihat beberapa contoh.

Memandangkan fungsi ini paling kerap digunakan untuk mengisi lajur dengan harga, yang ditunjukkan dalam senarai harga individu, mari kita ambil contoh berikut. Sebagai contoh, terdapat meja dengan buah-buahan (oren), di mana untuk setiap satu ditunjukkan berapa kilogram yang ingin dipesan. Sehubungan itu, bagi setiap buah anda perlu menulis harga dari senarai harga (hijau) yang diberikan oleh pembekal, dan kemudian mengira berapa kos pembelian itu. Sukar untuk melihat setiap item dan memindahkan data, terutamanya jika terdapat beribu-ribu baris dengan barangan, jadi kami menggunakan VLOOKUP.

Klik pada sel atas dalam jadual pertama dalam lajur Harga, dan kemudian butang "fx" dalam bar formula untuk membuka tetingkap Function Wizard.

Perkara seterusnya yang kita lakukan ialah menulis hujah ke dalam medan yang dicadangkan.

Letakkan huruf condong dalam medan "Nilai_carian" dan serlahkan dalam jadual pertama nilai yang akan kita cari. Saya mempunyai epal ini.

Dalam baris "Jadual", anda perlu memilih satu daripada mana data akan diambil - jangan pilih pengepala. Ambil perhatian bahawa lajur paling kiri harus terdiri daripada nilai yang kita cari. Iaitu, saya memerlukan epal dan buah-buahan lain, yang bermaksud senarai mereka harus berada di lajur kiri kawasan yang dipilih.

Supaya selepas kami menulis formula dan meregangkannya ke seluruh lajur, julat yang dipilih tidak bergerak ke bawah, kami perlu menjadikan pautan mutlak: pilih data dalam medan dan tekan "F4". Kini alamat sel telah menjadi mutlak, tanda "$" telah ditambahkan padanya, dan julat tidak akan beralih.

Di mana nombor lajur adalah, letakkan nombor yang sepadan dalam jadual kedua kepada lajur yang anda ingin pindahkan data. Senarai harga saya terdiri daripada buah dan harga, saya perlukan yang kedua, jadi saya meletakkan nombor "2".

DALAM "Masa berlalu" Kami menulis "PALSU" - jika anda perlu mencari padanan tepat, atau "BENAR" - jika nilainya boleh menjadi anggaran. Untuk contoh kami, kami memilih yang pertama. Jika anda tidak menyatakan apa-apa dalam medan ini, yang kedua akan dipilih secara lalai. Kemudian klik "OK".

Di sini, perhatikan perkara berikut: jika anda bekerja dengan nombor dan tentukan "Benar", maka jadual kedua (ini senarai harga kami) mesti diisih dalam tertib menaik. Sebagai contoh, apabila mencari 5.25, 5.27 akan ditemui dan data dari baris ini akan diambil, walaupun mungkin terdapat juga nombor di bawah 5.2599 - tetapi formula tidak akan melihat lebih jauh, kerana ia berpendapat bahawa nombor di bawah hanya lebih besar .

Bagaimanakah VLOOKUP berfungsi? Ia mengambil nilai yang dikehendaki (epal) dan mencarinya di lajur paling kiri julat yang ditentukan (senarai buah-buahan). Jika terdapat padanan, nilai diambil dari baris yang sama, hanya lajur yang dinyatakan dalam argumen (2), dan dipindahkan ke sel yang kami perlukan (C2). Formulanya kelihatan seperti ini:

VLOOKUP(A2,$G$2:$H$12,2, FALSE)

Kini anda boleh meregangkannya ke bawah bilangan baris yang diperlukan dengan menarik sudut kanan bawah.

Semua harga telah dipindahkan dari senarai harga ke jadual perolehan mengikut nama buah-buahan.

Jika anda mempunyai dalam jadual pertama nama produk yang tiada dalam senarai harga, bagi saya ini adalah sayur-sayuran, maka bertentangan dengan titik ini formula VLOOKUP akan menghasilkan ralat #N/A.

Semasa anda menambah lajur pada lembaran kerja, data untuk argumen Jadual fungsi akan berubah secara automatik. Dalam contoh, harga dianjakkan 2 lajur ke kanan. Mari pilih mana-mana sel dengan formula dan lihat bahawa bukannya $G$2:$H$12 ia kini $I$2:$J$14 .

Sekarang mari kita fikirkan cara untuk bekerja dengan fungsi VLOOKUP dalam Excel dan dengan senarai juntai bawah. Mula-mula anda perlu membuat senarai juntai bawah. Pilih sel, buka "Data" di bahagian atas dan klik pada butang "Pemeriksaan data".

Dalam tetingkap "Jenis Data" yang terbuka akan terdapat "Senarai", di bawah kami menunjukkan kawasan sumber - ini adalah nama buah-buahan, iaitu, lajur yang terdapat dalam kedua-dua jadual pertama dan kedua. Klik "OK".

Saya pilih F2 dan masukkan fungsi VLOOKUP. Hujah pertama ialah senarai yang dibuat (F1).

Yang kedua ialah jadual pembekal dengan harga yang dinyatakan. Ingat bahawa di sini lajur kiri mesti sepadan dengan data yang membentuk senarai juntai bawah.

Ternyata sesuatu seperti carian: kami memilih buah dan VPR mendapati harganya dalam senarai harga.

Jika anda mempunyai harga yang sama, dan kemudian pembekal memberi anda senarai baharu, maka anda perlu melihat apa yang telah berubah. Melakukan ini secara manual mengambil masa yang lama, tetapi dengan bantuan fungsi yang dimaksudkan, semuanya boleh dilakukan dengan cepat. Anda perlu menambah satu lajur dan memindahkan nilai baharu ke dalamnya, dan kemudian hanya membandingkan data.

Klik pada mana-mana sel dalam lajur D dan masukkan yang baharu.

Saya memanggilnya Harga Baharu - harga baharu akan ada di sini, dan harga lama akan ditunjukkan dalam lajur di sebelah kiri. Jadual baharu saya terletak pada helaian lain, supaya anda boleh memahami cara menggunakan VLOOKUP jika data terletak pada helaian berbeza. Dalam lajur yang ditambahkan, pilih sel kosong pertama.

Kami memasukkan fungsi dan menentukan hujah. Pertama, apa yang kita akan cari, dalam contoh epal (A2). Untuk memilih julat daripada senarai harga baharu, letakkan kursor dalam medan "Jadual" dan pergi ke helaian yang dikehendaki, saya mempunyai "Helaian1".

Gunakan tetikus untuk memilih lajur dan baris yang diperlukan, tanpa tajuk.

Seterusnya kita membuat rujukan mutlak kepada sel: "Sheet1!$A$2:$B$12" . Pilih baris dan tekan "F4" untuk menambah tanda dolar pada alamat sel. Kami menunjukkan lajur (2) dan tulis "PALSU".

Pada penghujungnya, klik butang "OK".

Kini dua lajur dengan harga baharu dan lama terletak bersebelahan dan anda boleh membuat sama ada perbandingan visual atau dengan menggunakan formula tertentu atau pemformatan bersyarat.

Saya harap saya telah memberikan arahan langkah demi langkah tentang cara menggunakan dan menggunakan fungsi VLOOKUP dalam Excel, dan kini semuanya jelas kepada anda.

Nilaikan artikel ini:

(13 penilaian, purata: 5,00 daripada 5)

Juruweb. Pendidikan tinggi dengan ijazah dalam Keselamatan Maklumat. Pengarang kebanyakan artikel dan pelajaran literasi komputer

Kita semua tahu betapa bergunanya. Mungkin separuh daripada semua tindakan dalam Excel dilakukan menggunakannya. Walau bagaimanapun, fungsi ini mempunyai beberapa batasan. Sebagai contoh, VLOOKUP hanya melihat pada lajur paling kiri dalam jadual atau mencari hanya satu syarat. Tetapi bagaimana jika kita perlu mengembalikan nilai yang sepadan dengan dua syarat. Dalam kes ini, kita perlu menggunakan beberapa helah. Kami akan bercakap tentang helah ini dalam artikel hari ini.

Jadi, kita akan melihat empat pilihan untuk mencipta fungsi penggantian dengan dua syarat:

1. Menggunakan fungsi SUMPRODUCT

Nah, kita akan mulakan dengan perkara yang paling mudah.

Menggunakan lajur tambahan

Dalam kebanyakan kes, masalah kompleks menjadi lebih mudah dan lebih mudah diurus apabila anda memecahkannya kepada kepingan kecil. Perkara yang sama berlaku apabila membina formula dalam Excel.

Mari lihat contoh klasik. Kami mempunyai jadual dengan jualan mengikut bulan dan bandar. Dan kita perlu menentukan nilai jualan yang sepadan dengan dua syarat: bulan - Februari dan bandar - Samara.

Menggunakan fungsi VLOOKUP dalam bentuk klasiknya tidak akan membantu kami, kerana ia hanya boleh mengembalikan nilai yang sepadan dengan hanya satu syarat. Lajur tambahan akan membantu kami keluar daripada situasi ini, di mana kami akan menggabungkan nilai lajur Bulan dan Bandar. Untuk melakukan ini, tulis formula =B2&C2 dalam sel A2 dan lanjutkan formula ini ke sel A13. Sekarang kita boleh menggunakan nilai lajur A untuk mengembalikan nilai yang diperlukan. Kami menulis formula dalam sel G3:

VLOOKUP(G1A2:D13;4;0)

Formula ini menggabungkan dua keadaan sel G1 dan G2 ke dalam satu baris dan melihatnya dalam lajur A. Setelah keadaan yang dikehendaki ditemui, formula mengembalikan nilai dari lajur keempat jadual A1:D13, i.e. kolum Jualan.



Menggunakan fungsi SELECT untuk mencipta jadual carian baharu

Jika atas sebab tertentu menggunakan lajur tambahan bukan pilihan untuk kami, kami boleh menggunakan .

Menggunakan fungsi SELECT melibatkan mencipta jadual baharu untuk melihat nilai lajur bulan Dan Bandar sudah bergabung. Formula kami akan kelihatan seperti ini:

VLOOKUP(G1SELECT((1,2),B2:B13&C2:C13,D2:D13),2,0)

Perkara utama formula ini ialah bahagian CHOICE((1;2);B2:B13&C2:C13;D2:D13), yang melakukan dua perkara:

1. Menggabungkan nilai lajur bulan Dan Bandar dalam satu tatasusunan: JanMoscow, FebMoscow...
2. Menggabungkan dua tatasusunan ke dalam jadual dua lajur.

Hasil daripada fungsi ini akan menjadi jadual yang kelihatan seperti ini:



Sekarang formula telah menjadi lebih jelas.

PENTING: Memandangkan kami menggunakan formula tatasusunan, apabila anda selesai memasukkan formula, tekan Ctrl+Shift+Enter untuk memberitahu program tentang niat kami. Selepas menekan kombinasi kekunci ini, program akan memasang pendakap kerinting secara automatik pada permulaan dan penghujung formula.

Menggunakan fungsi INDEX dan MATCH

Kaedah ketiga, yang akan kami pertimbangkan, juga melibatkan penggunaan formula tatasusunan dan menggunakan fungsi INDEX dan SEARCH.

Formula akan kelihatan seperti ini.

INDEKS(D2:D13,MACH(1,(B2:B13=G1)*(C2:C13=G2),0))

Mari lihat apa yang dilakukan oleh setiap bahagian formula ini.

Mula-mula, pertimbangkan fungsi MATCH(1;(B2:B13=G1)*(C2:C13=G2);0). Dalam kes ini, nilai sel G1 dibandingkan secara berurutan dengan setiap nilai sel dalam julat B2:B13 dan mengembalikan BENAR jika nilai sepadan dan FALSE jika tidak. Perbandingan yang sama dibuat dengan nilai sel G2 dan julat C2:C13. Seterusnya kita membandingkan kedua-dua tatasusunan ini yang terdiri daripada BENAR dan SALAH. Gabungan TRUE * TRUE memberikan kita keputusan 1 (TRUE). Mari lihat gambar di bawah, yang akan membantu menerangkan prinsip operasi dengan lebih jelas.



Sekarang kita boleh memberitahu di mana rentetan yang memenuhi kedua-dua syarat terletak. Fungsi MATCH mencari kedudukan 1 dalam tatasusunan hasil dan mengembalikan 6 kerana 1 berlaku dalam baris keenam. Seterusnya, fungsi INDEX mengembalikan nilai baris keenam julat D2:D13.

Menggunakan SUMPRODUCT

Salah satu formula Excel yang paling berkuasa. Saya juga mempunyai artikel berasingan khusus untuk formula ini. Cara keempat kami untuk menggunakan berbilang syarat ialah menulis formula dengan fungsi SUMPRODUCT. Dan ia akan kelihatan seperti ini:

SUMPRODUCT((B2:B13=G1)*(C2:C13=G2);D2:D13)

Prinsip operasi formula ini adalah serupa dengan prinsip operasi pendekatan sebelumnya. Jadual maya dicipta di mana nilai sel G1 dan G2 dibandingkan dengan julat B2:B13 dan C2:C13, masing-masing. Seterusnya, kedua-dua tatasusunan ini dibandingkan dan tatasusunan satu dan sifar diperoleh, di mana satu ditetapkan pada baris di mana kedua-dua keadaan sepadan. Seterusnya, tatasusunan maya ini didarab dengan julat D2:D13. Memandangkan tatasusunan maya kami hanya akan mempunyai satu 1 dalam baris keenam, formula akan mengembalikan hasil 189.

Fungsi ini tidak akan berfungsi jika terdapat nilai teks dalam julat D2:D13.

Untuk memahami cara formula ini berfungsi, saya mengesyorkan membaca artikel tentang fungsi SUMPROIZ.

KEPUTUSAN

Jadi kaedah mana yang patut anda gunakan? Walaupun semuanya berfungsi dengan pasti, saya lebih suka kaedah pertama. Dalam kerja harian saya, saya lebih suka bekerja dengan fail yang mudah difahami dan boleh diubah. Kedua-dua keperluan ini memenuhi syarat pendekatan pertama.

Untuk pemahaman yang lebih baik tentang formula, anda boleh melihatnya dalam artikel hari ini.