VLOOKUP. Menggunakan VLOOKUP. Excel - CDF

Program permohonan Excel adalah popular kerana kemampuan dan kesederhanaan, kerana ia tidak memerlukan apa-apa pengetahuan dan kemahiran khas. paparan jadual tanggapan maklumat kepada mana-mana pengguna, dan benar-benar pelbagai alat, termasuk "fungsi Master" membenarkan mana-mana manipulasi dan pengiraan dengan data yang disediakan.

Salah satu formula Excel terkenal adalah pandangan menegak. Menggunakan VLOOKUP pada pandangan pertama kelihatan agak rumit, tetapi ia hanya permulaan.

Bagaimana Excel VLOOKUP

Apabila bekerja dengan formula CDF perlu mengambil kira bahawa ia akan mencari nilai yang dikehendaki sahaja dalam ruangan bukannya baris. Untuk memohon fungsi memerlukan bilangan minimum lajur - dua, maksimum yang ada.

fungsi CDF mencari pelbagai kriteria yang telah ditetapkan yang boleh menjadi apa-apa format (teks, angka, wang, tarikh dan masa, t. D.) Dalam jadual. Dalam kes rakaman output dapatan (sisipan) nilai yang dicatatkan pada barisan yang sama, tetapi dengan tajuk lajur, iaitu sepadan dengan kriteria yang diberikan. Jika nilai itu tidak dijumpai, maka ralat dikeluarkan # N / A (di dalam Bahasa Inggeris versi # N / A).

keperluan untuk menggunakan

VLOOKUP datang ke bantuan pengendali, apabila anda mahu untuk mencari dengan cepat dan digunakan dalam pengiraan lanjut, analisis atau ramalan jadual nilai tertentu yang lebih besar. Perkara utama apabila anda menggunakan formula ini - memastikan bahawa skop yang ditetapkan carian telah dipilih dengan betul. Ia sepatutnya mengandungi semua rekod, iaitu, dari yang pertama untuk yang terakhir.

Penggunaan yang paling kerap CDF (fungsi Excel) - perbandingan dan penambahan data dalam dua jadual, menggunakan kriteria tertentu. Selain itu, pelbagai carian boleh menjadi besar dan mengandungi beribu-ribu telah disiapkan, diletakkan di atas kertas atau buku yang berbeza.

Menunjukkan fungsi CDF, bagaimana untuk menggunakannya, bagaimana untuk menjalankan pengiraan, sebagai contoh dalam rajah di atas. Di sini kita mempertimbangkan meja runcit saiz bergantung kepada rantau ini dan pengurus. Carian kriteria adalah pengurus tertentu (nama penuh), dan nilai yang dikehendaki adalah jumlah jualannya.

Hasil daripada fungsi CDF (VLOOKUP), jadual baru di mana pengurus dikehendaki tertentu dengan cepat dipadankan jumlah jualan beliau.

Mengisi formula algoritma

Terletak CDF formula dalam "Fungsi Wizard" dan "Rujukan dan Perlengkapan." pilihan dialog adalah seperti berikut:

dalam formula hujah-hujah yang dimasukkan dalam barisan:

• Nilai diingini - iaitu untuk mencari fungsi, dan varian yang merupakan nilai-nilai sel, alamatnya, nama yang diberikan kepadanya oleh operator. Dalam kes kami - ia adalah nama dan nama pengurus.
• Table - pelbagai baris dan lajur, di mana kriteria itu diminta.
• nombor lajur - nombor siri, yang adalah jumlah jualan, yang merupakan hasil daripada formula.
• pandangan selang. Beliau memiliki nilai sama ada palsu atau benar. Dan pulangan FALSE hanya perlawanan yang tepat, TRUE - membolehkan mencari nilai anggaran.

Contoh fungsi

VLOOKUP contoh penggunaan mungkin seperti berikut: apabila berurusan syarikat perdagangan dalam hamparan Excel dalam lajur A nama ditulis produk, dan dalam lajur B - harga yang sama. Untuk merangka cadangan dalam lajur C anda perlu mencari kos produk tertentu yang anda mahu dipaparkan dalam lajur D.

Formula, yang ditulis dalam D, akan kelihatan seperti ini: = VLOOKUP (C1, A1: B5, 2, 0), iaitu, = VLOOKUP (nilai lookup, pelbagai data jadual, nombor siri lajur; 0). Sebagai hujah keempat boleh digunakan selain daripada 0 adalah FALSE.

Untuk mengisi formula ini menawarkan meja, anda perlu menyalin seluruh lajur D.

kawasan data selamat julat kerja adalah mungkin dengan cara rujukan mutlak. Untuk melakukan ini secara manual dijadualkan $ tanda-tanda di hadapan nilai abjad dan berangka oleh sel-sel kiri dan kanan melampau meja. Dalam kes ini formula menjadi: = CDF (C1; $ A $ 1: $ B $ 5, 2, 0).

menggunakan ralat

fungsi CDF tidak berfungsi, dan kemudian mesej muncul dalam hasil output lajur ralat (# N / A atau # N / A). Ini berlaku dalam kes-kes seperti:

1. formula diperkenalkan, dan lajur kriteria yang dikehendaki kosong (dalam kes ini, lajur C).
2. Dalam lajur C membuat nilai yang tidak hadir dalam lajur A (search pelbagai data). Untuk memeriksa nilai yang diperlukan harus diperuntukkan dan kriteria lajur dalam menu tab "Edit" - "Cari" untuk memasukkan rekod ini, memulakan carian. Jika program ini tidak merasa, maka ia tidak wujud.
3. Format lajur sel A dan C (kriteria yang diperlukan) adalah berbeza, sebagai contoh, satu - teks, dan lain - nombor. Menukar format sel boleh, jika kita pergi ke editing sel (F2). masalah itu biasanya berlaku apabila anda mengimport data dari aplikasi lain. Untuk mengelakkan kesilapan seperti dalam formula CDF adalah mungkin untuk menggabungkan ciri-ciri berikut: VALUE atau TEXT. Pelaksanaan algoritma ini secara automatik menukar format sel-sel.
4. Kod fungsi mengandungi aksara tidak boleh cetak atau ruang. Maka anda perlu berhati-hati memeriksa formula di hadapan kesilapan input.
5. Tetapkan carian anggaran, iaitu hujah keempat VLOOKUP adalah 1 atau TRUE, tetapi jadual tidak disusun mengikut nilai yang semakin meningkat. Dalam kes ini, kriteria yang diperlukan untuk lajur yang anda mahu untuk menyusun dalam tertib menaik.

Dan apabila jadual ringkasan baru organisasi adalah kriteria yang diperlukan boleh dalam mana-mana perintah dan urutan dan tidak semestinya menerima senarai lengkap (pemilihan sebahagian).

Ciri-ciri penggunaan selang tontonan 1 atau TRUE

Ralat bawah №5 adalah perkara biasa dan jelas ditunjukkan dalam rajah di bawah.

Dalam contoh ini, senarai nama itu disusun mengikut penomboran tidak dalam tertib menaik, dan nilai yang jatuh. Selain itu, seperti selang yang digunakan memandangkan kriteria TRUE (1), yang sebaik mengganggu carian apabila ia mengesan nilai yang lebih besar daripada yang diperlukan, jadi ralat dikeluarkan.

Dalam pemakaian 1 atau benar dalam hujah keempat keperluan untuk memastikan bahawa ruang dengan kriteria yang dikehendaki telah disusun dalam tertib menaik. Apabila menggunakan 0 atau LIE, ini tidak lagi keperluan, tetapi juga hilang, maka kemungkinan pandangan selang.

Hanya perlu diingat bahawa selang adalah penting untuk menyusun meja. Jika tidak VLOOKUP akan memaparkan data yang tidak betul di dalam sel.

nuansa lain bekerja dengan VLOOKUP

Untuk kemudahan, seperti formula yang boleh berhak Rajah pelbagai yang dicari (hujah kedua), seperti yang ditunjukkan dalam Rajah.

Dalam kes ini, kawasan jualan meja berhak. Untuk tujuan ini, ia berdiri meja, kecuali tajuk lajur, dan dalam bidang nama (di bawah bar tab untuk sebelah kiri) yang diberikan nama.

Satu lagi pilihan - tajuk - bermaksud peruntukan julat data, dan kemudian beralih ke menu "Insert" - "Nama" - "Peruntukkan".

Dalam usaha untuk menggunakan data diletakkan di lain lembaran buku kerja, menggunakan fungsi PPS, adalah perlu dalam hujah kedua pelbagai lokasi data formula daftar. Sebagai contoh, CDF = (A1; Sheet 2 $ A $ 1: $ B $ 5; 2; 0), di mana Sheet2! - merujuk kepada senarai buku yang diminta, dan $ A $ 1: $ B $ 5 - alamat julat carian data.

Contoh proses pendidikan dengan CDF

Agak mudah untuk fungsi Excel VLOOKUP memohon bukan sahaja kepada syarikat-syarikat yang terlibat dalam perdagangan, tetapi juga institusi pendidikan untuk mengoptimumkan murid-murid proses pemadanan (pelajar) dengan anggaran mereka. Contoh tugas-tugas ini adalah seperti yang ditunjukkan dalam rajah di bawah.

Terdapat dua senarai pelajar meja. Satu dengan anggaran mereka, dan yang kedua menunjukkan umur. Ia adalah perlu untuk membandingkan dua jadual supaya setaraf dengan umur pelajar dipaparkan penilaian mereka, iaitu untuk menambah lajur tambahan dalam senarai kedua.

VLOOKUP mengatasi permasalahannya dengan tugas ini. Dalam "markah" G lajur bertajuk formula sepadan direkodkan: = CDF (E4, B3: C13, 2, 0). Beliau perlu untuk menyalin seluruh lajur jadual.

Hasil daripada fungsi VLOOKUP akan dapat menilai yang diperoleh oleh pelajar tertentu.

Contoh enjin carian dengan CDF

Satu lagi contoh penggunaan fungsi CDF - sebuah organisasi enjin carian di dalam pangkalan data apabila mengikut kriteria yang dinyatakan adalah untuk mencari nilai yang sama. Oleh itu, angka menunjukkan senarai nama panggilan haiwan dan milik mereka kepada jenis tertentu.

Dengan bantuan CDF, jadual baru yang mudah untuk mencari, digelar haiwan bentuknya. enjin carian yang relevan seperti apabila bekerja dengan senarai besar. Untuk mengkaji semula semua entri secara manual, anda boleh menggunakan carian dan mendapatkan hasil yang dikehendaki.