Perisian hasad adalah perisian yang menjengkelkan atau berbahaya yang...
Sudah pasti lebih mudah untuk mencari melalui satu, walaupun besar, tetapi keseluruhan jadual atau dalam julat sel bersebelahan, daripada melalui beberapa jadual yang dibahagikan kepada bahagian yang bertaburan merentasi julat bukan bersebelahan yang berbeza atau bahkan helaian individu. Walaupun anda melakukan carian automatik pada beberapa jadual pada masa yang sama, halangan yang ketara boleh timbul. Dan meletakkan semua data ke dalam satu jadual adalah sukar, kadang-kadang hampir mustahil. Menggunakan contoh khusus, kami akan menunjukkan penyelesaian yang betul untuk carian serentak merentas berbilang jadual dalam Excel.
Carian serentak dalam pelbagai julat
Untuk contoh visual, mari buat tiga jadual berasingan ringkas yang terletak dalam julat bukan bersebelahan helaian yang sama:
Anda harus mencari jumlah yang diperlukan untuk menghasilkan 20 keping produk. Malangnya, data ini berada dalam lajur dan baris yang berbeza. Oleh itu, pertama sekali, anda perlu menyemak berapa lama masa yang diperlukan untuk menghasilkan produk ini (jadual pertama).
Berdasarkan data yang diperoleh, adalah perlu untuk segera pergi ke carian dalam jadual lain dan mencari bilangan pekerja yang perlu terlibat dalam volum pengeluaran tertentu. Keputusan yang diperoleh hendaklah dibandingkan dengan data jadual ketiga. Oleh itu, dalam satu operasi carian dalam tiga jadual, kami akan segera menentukan kos (jumlah) yang diperlukan.
Rata-rata pengguna Excel akan mencari penyelesaian menggunakan formula berdasarkan fungsi seperti VLOOKUP. Juga akan memenuhi carian dalam 3 peringkat (berasingan untuk setiap jadual). Ternyata anda boleh segera mendapatkan hasil siap dengan melakukan carian hanya dalam 1 peringkat menggunakan formula khas. Untuk ini:
- Dalam sel E6, masukkan nilai 20, yang merupakan syarat untuk pertanyaan carian.
- Dalam sel E7, masukkan formula berikut:
Kos pengeluaran untuk 20 pcs. sesuatu produk.
Cara formula berfungsi dengan VLOOKUP dalam berbilang jadual:
Prinsip operasi formula ini adalah berdasarkan carian berurutan semua argumen untuk fungsi VLOOKUP utama (yang pertama). Pertama, fungsi VLOOKUP ketiga mencari jadual pertama untuk jumlah masa yang diperlukan untuk menghasilkan 20 keping produk yang dinyatakan sebagai nilai untuk sel E6 (yang kemudiannya boleh ditukar jika perlu). Kemudian fungsi VLOOKUP kedua mencari nilai untuk argumen pertama fungsi utama.
Hasil daripada mencari fungsi ketiga, kita mendapat nilai 125, yang merupakan hujah pertama untuk fungsi kedua. Setelah menerima semua parameter, fungsi kedua melihat dalam jadual kedua untuk bilangan pekerja yang diperlukan untuk pengeluaran. Akibatnya, nilai 5 dikembalikan, yang akan digunakan oleh fungsi utama. Berdasarkan semua data yang diterima, formula mengembalikan hasil akhir pengiraan. Iaitu jumlah $1,750 yang diperlukan untuk menghasilkan 20 keping produk tertentu.
Mengikut prinsip ini, anda boleh menggunakan formula untuk fungsi VLOOKUP dari beberapa helaian.
Microsoft Excel selalunya berfungsi dengan sejumlah besar maklumat. Ia mencipta jadual besar dengan beribu-ribu baris, lajur dan kedudukan. Ia boleh menjadi sukar untuk mencari sebarang data khusus dalam tatasusunan sedemikian. Dan kadang-kadang ia adalah mustahil sama sekali. Tugasan ini boleh dipermudahkan. Ketahui cara mencari perkataan yang betul dalam Excel. Ini akan memudahkan anda menavigasi dokumen. Dan anda boleh dengan cepat melompat ke maklumat yang anda cari.
Untuk memaparkan alamat semua sel yang mempunyai perkara yang anda cari, lakukan perkara berikut:
- Jika anda mempunyai Office 2010, pergi ke Menu - Edit - Cari.
- Tetingkap akan dibuka dengan medan input. Tulis frasa carian di dalamnya.
- Dalam Excel 2007, butang ini berada pada menu Laman Utama dalam panel Penyuntingan. Dia berada di sebelah kanan.
- Keputusan yang sama dalam semua versi boleh dicapai dengan menekan kekunci Ctrl + F.
- Dalam medan, taip perkataan, frasa atau nombor yang ingin anda cari.
- Klik "Cari Semua" untuk mencari keseluruhan dokumen. Jika anda mengklik "Seterusnya", program akan memilih sel satu demi satu yang berada di bawah kursor sel Excel.
- Tunggu proses selesai. Semakin besar dokumen, semakin lama sistem akan mencari.
- Senarai akan muncul dengan hasil: nama dan alamat sel yang sepadan dengan frasa yang diberikan dan teks yang ditulis di dalamnya.
- Apabila anda mengklik pada setiap baris, sel yang sepadan akan diserlahkan.
- Untuk kemudahan, anda boleh "meregangkan" tingkap. Jadi ia akan menunjukkan lebih banyak baris.
- Untuk mengisih data, klik pada nama lajur di atas hasil carian. Jika anda mengklik pada "Helaian", garisan akan berbaris mengikut abjad bergantung pada nama helaian, jika anda memilih "Nilai" - ia akan diletakkan mengikut nilai.
- Lajur ini juga "diregangkan".
Anda boleh menetapkan syarat anda sendiri. Sebagai contoh, mula mencari berbilang aksara. Begini cara mencari perkataan dalam Excel yang anda tidak ingat sepenuhnya:
- Masukkan hanya sebahagian daripada label. Anda boleh mempunyai sekurang-kurangnya satu huruf - semua tempat di mana ia ada akan dipilih.
- Gunakan simbol * (asterisk) dan? (tanda soal). Mereka menggantikan watak yang hilang.
- Soalan menunjukkan satu item yang hilang. Jika anda menulis, sebagai contoh, "P???", sel akan dipaparkan yang mempunyai perkataan empat aksara bermula dengan "P": "Plow", "Field", "Pair", dan seterusnya.
- Bintang (*) menggantikan sebarang bilangan aksara. Untuk mencari semua nilai yang mengandungi akar "rast", mula mencari kunci "*rast*".
Anda juga boleh pergi ke tetapan:
- Dalam tetingkap Cari, klik Pilihan.
- Dalam bahagian "Semak imbas" dan "Kawasan carian", nyatakan di mana dan mengikut kriteria yang anda mahu cari padanan. Anda boleh memilih formula, nota atau nilai.
- Untuk membolehkan sistem membezakan antara huruf kecil dan huruf besar, tandai kotak "sensitif huruf besar".
- Jika anda menandai kotak "Seluruh sel", keputusan akan menunjukkan sel yang mengandungi hanya frasa carian yang ditentukan dan tiada yang lain.
Pilihan format sel
Untuk mencari nilai dengan isian atau gaya tertentu, gunakan tetapan. Berikut ialah cara untuk mencari perkataan dalam Excel jika ia kelihatan berbeza daripada teks yang lain:
- Dalam kotak carian, klik "Pilihan" dan klik pada butang "Format". Menu dengan beberapa tab akan dibuka.
- Anda boleh menentukan fon tertentu, jenis bingkai, warna latar belakang, format data. Sistem akan mencari tempat yang sepadan dengan kriteria yang diberikan.
- Untuk mengambil maklumat daripada sel semasa (dipilih pada ketika ini), klik "Gunakan format sel ini". Kemudian program akan mencari semua nilai yang mempunyai saiz dan jenis aksara yang sama, warna yang sama, sempadan yang sama, dan seumpamanya.
Carian berbilang perkataan
Dalam Excel, anda boleh mencari sel mengikut keseluruhan frasa. Tetapi jika anda memasukkan kekunci "Blue ball", sistem akan berfungsi tepat pada permintaan ini. Nilai dengan "Blue Crystal Ball" atau "Blue Glitter Ball" tidak akan muncul dalam keputusan.
Untuk mencari lebih daripada satu perkataan dalam Excel, tetapi beberapa sekali gus, lakukan perkara berikut:
- Tulis mereka dalam bar carian.
- Letakkan bintang di antara mereka. Ia akan menjadi "*Teks* *Teks2* *Teks3*". Ini akan menemui semua nilai yang mengandungi inskripsi yang ditentukan. Tidak kira sama ada terdapat watak antara mereka atau tidak.
- Dengan cara ini, anda boleh menetapkan kunci walaupun dengan huruf individu.
Penapis
Berikut ialah cara mencari dalam Excel menggunakan penapis:
- Pilih mana-mana sel yang diisi.
- Klik Laman Utama - Isih - Penapis.
- Anak panah akan muncul di baris atas sel. Ini ialah menu lungsur. Membukanya.
- Masukkan pertanyaan anda dalam medan teks dan klik OK.
- Lajur hanya akan memaparkan sel yang mengandungi frasa carian.
- Untuk menetapkan semula keputusan, tandai "Pilih Semua" dalam senarai juntai bawah.
- Untuk mematikan penapis, klik padanya sekali lagi dalam pengisihan.
Kaedah ini tidak akan berfungsi jika anda tidak tahu di baris mana nilai yang anda perlukan.
Untuk mencari frasa atau nombor dalam Excel, gunakan ciri antara muka terbina dalam. Anda boleh memilih pilihan carian tambahan dan mendayakan penapis.
Tutorial ini bercakap tentang faedah utama fungsi INDEKS dan PERLAWANAN dalam Excel, yang menjadikannya lebih menarik berbanding dengan VLOOKUP. Anda akan melihat beberapa contoh formula yang akan membantu anda menangani dengan mudah banyak tugas kompleks yang dihadapi oleh fungsi tersebut. VLOOKUP tidak berdaya.
Dalam beberapa artikel baru-baru ini, kami telah berusaha sedaya upaya untuk menerangkan asas fungsi kepada pengguna baru. VLOOKUP dan tunjukkan contoh formula yang lebih kompleks untuk pengguna lanjutan. Sekarang kami akan cuba, jika tidak untuk menghalang anda daripada menggunakan VLOOKUP, maka sekurang-kurangnya tunjukkan cara alternatif untuk melaksanakan carian menegak dalam Excel.
Mengapa kita memerlukan ini? - anda bertanya. Ya kerana VLOOKUP bukanlah satu-satunya fungsi carian dalam Excel, dan banyak batasannya boleh menghalang anda daripada mendapatkan hasil yang anda inginkan dalam banyak situasi. Sebaliknya, fungsi INDEKS dan PERLAWANAN– lebih fleksibel dan mempunyai beberapa ciri yang menjadikannya lebih menarik daripada VLOOKUP.
Maklumat asas tentang INDEX dan MATCH
Memandangkan tujuan tutorial ini adalah untuk menunjukkan keupayaan fungsi INDEKS dan PERLAWANAN untuk melaksanakan carian menegak dalam Excel, kami tidak akan memikirkan sintaks dan aplikasinya.
Kami memberikan di sini minimum yang diperlukan untuk memahami intipati, dan kemudian kami akan menganalisis secara terperinci contoh formula yang menunjukkan kelebihan menggunakan INDEKS dan PERLAWANAN bukannya VLOOKUP.
INDEX - sintaks dan aplikasi fungsi
Fungsi INDEKS(INDEX) dalam Excel mengembalikan nilai daripada tatasusunan pada nombor baris dan lajur yang diberikan. Fungsi ini mempunyai sintaks berikut:
Setiap hujah mempunyai penjelasan yang sangat mudah:
- tatasusunan(array) ialah julat sel untuk mengekstrak nilai.
- row_num(nombor baris) ialah nombor baris dalam tatasusunan untuk mengekstrak nilai. Jika tidak dinyatakan, hujah diperlukan bilangan_lajur(nombor_lajur).
- bilangan_lajur(column_number) ialah nombor lajur dalam tatasusunan untuk mengekstrak nilai. Jika tidak dinyatakan, hujah diperlukan row_num(nombor garisan)
Jika kedua-dua hujah diberikan, maka fungsi INDEKS mengembalikan nilai dari sel di persimpangan baris dan lajur yang ditentukan.
Berikut adalah contoh fungsi mudah INDEKS(INDEX):
INDEKS(A1:C10,2,3)
=INDEX(A1:C10,2,3)
Formula mencari dalam julat A1:C10 dan mengembalikan nilai sel dalam ke-2 baris dan 3m lajur, iaitu dari sel C2.
Sangat mudah, bukan? Walau bagaimanapun, dalam amalan, anda tidak selalu tahu baris dan lajur yang anda perlukan, dan oleh itu anda memerlukan bantuan fungsi tersebut PERLAWANAN.
MATCH - sintaks dan aplikasi fungsi
Fungsi PERLAWANAN(MATCH) dalam Excel mencari nilai yang ditentukan dalam julat sel dan mengembalikan kedudukan relatif nilai tersebut dalam julat.
Sebagai contoh, jika dalam julat B1:B3 mengandungi nilai New-York, Paris, London, maka formula berikut akan mengembalikan digit 3 , kerana "London" ialah item ketiga dalam senarai.
MATCH("London",B1:B3,0)
=MATCH("London",B1:B3,0)
Fungsi PERLAWANAN(MATCH) mempunyai sintaks berikut:
MATCH(lookup_value, lookup_array,)
MATCH(lookup_value, lookup_array, [match_type])
- nilai_cari(lookup_value) ialah nombor atau teks yang anda cari. Hujah boleh menjadi nilai, termasuk boolean, atau rujukan sel.
- lookup_array(lookup_array) ialah julat sel untuk mencari.
- padanan_jenis(match_type) - Hujah ini memberitahu fungsi PERLAWANAN sama ada anda ingin mencari padanan tepat atau padanan anggaran:
- 1 atau tidak dinyatakan– mencari nilai maksimum kurang daripada atau sama dengan yang dikehendaki. Tatasusunan yang dicari mesti diisih dalam tertib menaik, iaitu, daripada terkecil kepada terbesar.
- 0 - mencari nilai pertama sama dengan yang dikehendaki. Untuk gabungan INDEKS/PERLAWANAN anda sentiasa mahukan padanan tepat, jadi hujah fungsi ketiga PERLAWANAN sepatutnya sama 0 .
- -1 - mencari nilai terkecil lebih besar daripada atau sama dengan nilai yang dikehendaki. Tatasusunan yang dilihat mesti diisih mengikut tertib menurun, iaitu daripada terbesar kepada terkecil.
Pada pandangan pertama, faedah fungsi PERLAWANAN menimbulkan keraguan. Siapa yang perlu mengetahui kedudukan unsur dalam julat? Kami ingin mengetahui nilai elemen ini!
Biar saya ingatkan anda bahawa kedudukan relatif bagi nilai yang anda cari (iaitu nombor baris dan/atau lajur) ialah apa yang perlu kami tentukan untuk hujah row_num(nombor_baris) dan/atau bilangan_lajur(column_number) fungsi INDEKS(INDEX). Seperti yang anda ingat, fungsi INDEKS boleh mengembalikan nilai di persimpangan baris dan lajur yang diberikan, tetapi ia tidak dapat menentukan baris dan lajur yang kami minati.
Cara menggunakan INDEX dan MATCH dalam Excel
Sekarang setelah anda mengetahui maklumat asas tentang kedua-dua fungsi ini, saya percaya bahawa ia sudah menjadi jelas bagaimana fungsi tersebut PERLAWANAN dan INDEKS boleh bekerjasama. PERLAWANAN mentakrifkan kedudukan relatif nilai yang dicari dalam julat sel yang diberikan, dan INDEKS menggunakan nombor itu (atau nombor) dan mengembalikan hasil daripada sel yang sepadan.
Masih tidak jelas sepenuhnya? Mewakili Ciri INDEKS dan PERLAWANAN dalam bentuk ini:
INDEKS(,(PERLAWANAN ( nilai yang dikehendaki,ruangan yang kami cari,0))
=INDEX( lajur untuk diekstrak;(PERLAWANAN( nilai yang dikehendaki;ruangan yang kami cari;0))
Saya fikir ia akan menjadi lebih mudah untuk difahami dengan contoh. Katakan anda mempunyai senarai ibu negeri berikut:
Mari cari populasi salah satu ibu kota, katakan Jepun, menggunakan formula berikut:
INDEKS($D$2:$D$10,MATCH("Jepun",$B$2:$B$10,0))
=INDEX($D$2:$D$10;MATCH("Jepun";$B$2:$B$10;0))
Sekarang mari kita pecahkan perkara yang dilakukan oleh setiap elemen formula ini:
- Fungsi PERLAWANAN(MATCH) mencari nilai "Jepun" dalam lajur B, dan khususnya dalam sel B2:B10, dan mengembalikan nombor 3 , memandangkan "Jepun" berada di tempat ketiga dalam senarai.
- Fungsi INDEKS(INDEX) menggunakan 3
untuk hujah row_num(line_number), yang menentukan dari baris mana nilai harus dikembalikan. Itu. formula mudah diperolehi:
INDEKS ($D$2:$D$10.3)
=INDEX($D$2:$D$10,3)Formula mengatakan sesuatu seperti ini: lihat dalam sel dari D2 sebelum ini D10 dan ekstrak nilai dari baris ketiga iaitu dari sel D4, kerana pengiraan bermula dari baris kedua.
Berikut adalah keputusan dalam Excel:
Penting! Bilangan baris dan lajur dalam tatasusunan yang digunakan oleh fungsi INDEKS(INDEX), mesti sepadan dengan nilai argumen row_num(nombor_baris) dan bilangan_lajur(column_number) fungsi PERLAWANAN(PERLAWANAN). Jika tidak, keputusan formula akan menjadi salah.
Berhenti, berhenti... mengapa kita tidak boleh menggunakan fungsi tersebut VLOOKUP(VLOOKUP)? Adakah ada gunanya membuang masa cuba memikirkan labirin PERLAWANAN dan INDEKS?
VLOOKUP("Jepun",$B$2:$D$2,3)
=VLOOKUP("Jepun",$B$2:$D$2,3)
Dalam kes ini, ia tidak masuk akal! Tujuan contoh ini adalah ilustrasi semata-mata, supaya anda boleh memahami bagaimana fungsinya PERLAWANAN dan INDEKS kerja dalam pasangan. Contoh berikut akan menunjukkan kepada anda kuasa sebenar berkas tersebut. INDEKS dan PERLAWANAN, yang mudah menghadapi banyak situasi sukar apabila VLOOKUP tersangkut.
Mengapa INDEX/MATCH lebih baik daripada VLOOKUP?
Apabila memutuskan formula yang hendak digunakan untuk carian menegak, kebanyakan guru Excel percaya bahawa INDEKS/PERLAWANAN jauh lebih baik daripada VLOOKUP. Walau bagaimanapun, ramai pengguna Excel masih menggunakan VLOOKUP, kerana fungsi ini lebih mudah. Ini kerana sangat sedikit orang yang memahami sepenuhnya manfaat penuh beralih daripada VLOOKUP pada sekumpulan INDEKS dan PERLAWANAN, dan tiada siapa yang mahu meluangkan masa untuk mengkaji formula yang lebih kompleks.
4 faedah teratas menggunakan MATCH/INDEX dalam Excel:
1. Cari dari kanan ke kiri. Seperti mana-mana pengguna Excel yang celik tahu, VLOOKUP tidak boleh melihat ke kiri, yang bermaksud bahawa nilai yang dikehendaki mestilah berada di lajur paling kiri julat yang sedang dikaji. Dalam kes PERLAWANAN/INDEKS, lajur carian boleh sama ada di sebelah kiri atau di sebelah kanan julat carian. Contoh: akan menunjukkan ciri ini dalam tindakan.
2. Penambahan atau penyingkiran lajur yang selamat. Formula dengan fungsi VLOOKUP berhenti bekerja atau kembalikan nilai yang salah jika anda mengalih keluar atau menambah lajur pada jadual carian. Untuk fungsi VLOOKUP mana-mana lajur yang dimasukkan atau dialih keluar akan mengubah hasil formula kerana sintaks VLOOKUP memerlukan anda untuk menentukan keseluruhan julat dan nombor lajur khusus yang anda ingin keluarkan data.
Sebagai contoh, jika anda mempunyai meja A1:C10, dan anda mahu mengekstrak data daripada lajur B, maka anda perlu menetapkan nilai 2 untuk hujah kol_index_num(column_number) fungsi VLOOKUP, seperti ini:
VLOOKUP("nilai carian", A1:C10,2)
=VLOOKUP("nilai carian";A1:C10;2)
Jika anda kemudiannya memasukkan lajur baharu antara lajur A dan B, maka nilai hujah perlu ditukar daripada 2 pada 3 , jika tidak, formula akan mengembalikan hasil daripada lajur yang baru dimasukkan.
menggunakan PERLAWANAN/INDEKS, Anda boleh mengalih keluar atau menambah lajur pada julat yang sedang dikaji tanpa memutarbelitkan hasilnya, kerana lajur yang mengandungi nilai yang dikehendaki ditakrifkan secara langsung. Sesungguhnya, ini adalah kelebihan yang hebat, terutamanya apabila anda perlu bekerja dengan jumlah data yang besar. Anda boleh menambah dan mengalih keluar lajur tanpa perlu risau tentang perlu membetulkan setiap ciri yang anda gunakan VLOOKUP.
3. Tiada had pada saiz nilai yang dicari. menggunakan VLOOKUP, ambil perhatian tentang had 255 aksara pada panjang nilai yang dicari, jika tidak, anda berisiko mendapat ralat #NILAI!(#VALUE!). Jadi, jika jadual mengandungi baris yang panjang, satu-satunya penyelesaian yang berdaya maju ialah menggunakan INDEKS/PERLAWANAN.
Katakan anda menggunakan formula berikut: VLOOKUP, yang mencari dalam sel daripada B5 sebelum ini D10 nilai yang dinyatakan dalam sel A2:
VLOOKUP(A2,B5:D10,3,PALSU)
=VLOOKUP(A2,B5:D10,3,PALSU)
Formula tidak akan berfungsi jika nilai berada dalam sel A2 lebih daripada 255 aksara. Sebaliknya, anda perlu menggunakan formula yang sama INDEKS/PERLAWANAN:
INDEX(D5:D10,MACH(BENAR,INDEX(B5:B10=A2,0),0))
=INDEX(D5:D10,MACH(BENAR,INDEX(B5:B10=A2,0),0))
4. Kelajuan larian yang lebih tinggi. Jika anda bekerja dengan hamparan kecil, maka perbezaan dalam prestasi Excel kemungkinan besar tidak akan ketara, terutamanya dalam versi terkini. Jika anda bekerja dengan jadual besar yang mengandungi beribu-ribu baris dan beratus-ratus formula carian, Excel akan berjalan lebih pantas apabila anda menggunakan PERLAWANAN dan INDEKS bukannya VLOOKUP. Secara umum, penggantian sedemikian meningkatkan kelajuan Excel dengan 13% .
Pengaruh VLOOKUP Prestasi Excel amat ketara jika buku kerja mengandungi beratus-ratus formula tatasusunan yang kompleks, seperti VLOOKUP+SUM. Hakikatnya ialah menyemak setiap nilai dalam tatasusunan memerlukan panggilan fungsi yang berasingan VLOOKUP. Oleh itu, lebih banyak nilai yang terkandung dalam tatasusunan dan lebih banyak formula tatasusunan yang terkandung dalam jadual anda, semakin perlahan Excel.
Sebaliknya, formula dengan fungsi PERLAWANAN dan INDEKS hanya melakukan carian dan mengembalikan hasilnya, melakukan kerja yang sama dengan ketara lebih cepat.
INDEX dan PADANAN - contoh formula
Sekarang anda memahami sebab mengapa anda perlu mempelajari fungsi PERLAWANAN dan INDEKS, mari kita beralih kepada yang paling menarik dan lihat bagaimana anda boleh menggunakan pengetahuan teori dalam amalan.
Cara mencari di sebelah kiri menggunakan MATCH dan INDEX
Mana-mana buku teks VLOOKUP mengatakan bahawa fungsi ini tidak boleh melihat ke kiri. Itu. jika lajur yang dicari bukan lajur paling kiri dalam julat carian, maka tiada peluang untuk mendapatkan daripada VLOOKUP hasil yang diingini.
Fungsi PERLAWANAN dan INDEKS Excel adalah lebih fleksibel dan tidak peduli di mana lajur dengan nilai untuk diekstrak berada. Sebagai contoh, mari kita kembali ke meja dengan ibu negeri dan penduduk. Kali ini kita tulis formulanya PERLAWANAN/INDEKS, yang akan menunjukkan tempat yang diduduki ibu negara Rusia (Moscow) dari segi populasi.
Seperti yang anda lihat dalam rajah di bawah, formula melakukan tugasan ini dengan baik:
INDEX($A$2:$A$10,MATCH("Rusia",$B$2:$B$10,0))
Kini anda sepatutnya tiada masalah memahami cara formula ini berfungsi:
- Pertama, mari kita gunakan fungsi tersebut PERLAWANAN(PERLAWANAN) yang mencari kedudukan "Rusia" dalam senarai:
MATCH("Rusia",$B$2:$B$10.0))
=MATCH("Rusia",$B$2:$B$10,0)) - Seterusnya, tetapkan julat untuk fungsi tersebut INDEKS(INDEX) untuk mendapatkan semula nilai daripada. Dalam kes kami, ini A2:A10.
- Kemudian kami menyambung kedua-dua bahagian dan dapatkan formula:
INDEX($A$2:$A$10;MATCH("Rusia";$B$2:$B$10;0))
=INDEX($A$2:$A$10,MATCH("Rusia",$B$2:$B$10,0))
Petunjuk: Penyelesaian yang betul ialah sentiasa menggunakan rujukan mutlak untuk INDEKS dan PERLAWANAN supaya julat carian tidak hilang semasa menyalin formula ke sel lain.
Pengiraan menggunakan INDEX dan MATCH dalam Excel (PURATA, MAX, MIN)
Anda boleh memasukkan fungsi Excel lain INDEKS dan PERLAWANAN, sebagai contoh, untuk mencari minimum, maksimum atau paling hampir dengan nilai purata. Berikut ialah beberapa pilihan untuk formula, seperti yang digunakan pada jadual daripada:
1. MAX(MAX). Formula mencari maksimum dalam lajur D C baris yang sama:
INDEKS($C$2:$C$10,MATCH(MAX ($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
Keputusan: Beijing
2. MIN(MIN). Formula mencari minimum dalam lajur D dan mengembalikan nilai dari lajur C baris yang sama:
INDEKS($C$2:$C$10,MACH(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
Keputusan: Lima
3. PURATA(PURATA). Formula mengira purata dalam julat D2:D10, kemudian cari yang paling dekat dengannya dan mengembalikan nilai dari lajur C baris yang sama:
INDEKS($C$2:$C$10,PERLAWANAN(PURATA ($D$2:D$10),$D$2:D$10,1))
=INDEX($C$2:$C$10,MACH(PURATA ($D$2:D$10),$D$2:D$10,1))
Keputusan: Moscow
Perkara yang perlu diingat apabila menggunakan fungsi AVERAGE dengan INDEX dan MATCH
Menggunakan fungsi PURATA dalam kombinasi dengan INDEKS dan PERLAWANAN, sebagai hujah fungsi ketiga PERLAWANAN selalunya anda perlu nyatakan 1 atau -1 sekiranya anda tidak pasti bahawa julat yang dilihat mengandungi nilai yang sama dengan purata. Jika anda pasti bahawa terdapat nilai sedemikian, letakkan 0 untuk mencari padanan yang tepat.
- Jika anda nyatakan 1 , nilai dalam lajur carian mesti diisih dalam tertib menaik, dan formula akan mengembalikan nilai maksimum kurang daripada atau sama dengan min.
- Jika anda nyatakan -1 , nilai dalam lajur carian hendaklah diisih mengikut tertib menurun, dan nilai minimum yang lebih besar daripada atau sama dengan purata akan dikembalikan.
Dalam contoh kami, nilai dalam lajur D diisih dalam tertib menaik, jadi kami menggunakan jenis padanan 1 . Formula INDEKS/SEARCHPOW mengembalikan "Moscow" kerana populasi Moscow adalah nilai terendah yang paling hampir kepada min (12,269,006).
Cara menggunakan INDEX dan MATCH untuk mencari pada baris dan lajur yang diketahui
Formula ini bersamaan dengan carian 2D VLOOKUP dan membolehkan anda mencari nilai di persimpangan baris dan lajur tertentu.
Dalam contoh ini, formula INDEKS/PERLAWANAN akan sangat serupa dengan formula yang telah kita bincangkan dalam pelajaran ini, dengan hanya satu perbezaan. Tahu tak?
Seperti yang anda ingat, sintaks fungsi INDEKS(INDEX) membenarkan tiga hujah:
INDEX(tatasusunan,nombor_baris,)
INDEX(tatasusunan, nombor_baris, [nombor_lajur])
Dan tahniah kepada anda yang mengetahuinya!
Mari mulakan dengan menulis templat formula. Untuk melakukan ini, kami mengambil formula yang sudah biasa INDEKS/PERLAWANAN dan tambahkan fungsi lain padanya PERLAWANAN, yang akan mengembalikan nombor lajur.
INDEX(Jadual anda ,(MATCH(, lajur untuk dicari,0)),(PERLAWANAN(, rentetan untuk mencari,0))
=INDEX(Jadual anda ,(MATCH( nilai untuk carian menegak,lajur untuk dicari,0)),(PERLAWANAN( nilai untuk carian mendatar,rentetan untuk mencari,0))
Sila ambil perhatian bahawa untuk carian dua dimensi, anda perlu menentukan keseluruhan jadual dalam hujah tatasusunan(tatasusunan) fungsi INDEKS(INDEX).
Sekarang mari kita uji corak ini. Di bawah anda melihat senarai negara yang paling ramai penduduknya di dunia. Katakan tugas kita adalah untuk mengetahui populasi Amerika Syarikat pada tahun 2015.
Baiklah, mari kita tulis formulanya. Apabila saya perlu mencipta formula kompleks dalam Excel dengan fungsi bersarang, saya mula-mula menulis setiap satu bersarang secara berasingan.
Jadi mari kita mulakan dengan dua fungsi PERLAWANAN, yang akan mengembalikan nombor baris dan lajur untuk fungsi tersebut INDEKS:
- PADAN untuk lajur- kami sedang mencari dalam lajur B, untuk menjadi lebih tepat, dalam julat B2:B11, nilai yang dinyatakan dalam sel H2(USA). Fungsinya akan kelihatan seperti ini:
PERLAWANAN($H$2,$B$1:$B$11.0)
=PERLAWANAN($H$2,$B$1:$B$11,0)4 , memandangkan "AS" ialah elemen ke-4 senarai dalam lajur B(termasuk tajuk).
- PERLAWANAN untuk rentetan- kami sedang mencari nilai sel H3(2015) selaras 1
, iaitu dalam sel A1:E1:
PERLAWANAN($H$3,$A$1:$E$1,0)
=PERLAWANAN($H$3,$A$1:$E$1,0)Hasil daripada formula ini akan menjadi 5 kerana "2015" berada dalam lajur ke-5.
Sekarang kita masukkan formula ini ke dalam fungsi INDEKS dan voila:
INDEKS($A$1:$E$11,MATCH($H$2,$B$1:$B$11.0),MATCH($H$3,$A$1:$E$1.0))
=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
Jika kita menggantikan fungsi PERLAWANAN pada nilai yang mereka kembalikan, formula akan menjadi mudah dan difahami:
INDEKS ($A$1:$E$11,4,5))
=INDEX($A$1:$E$11,4,5))
Formula ini mengembalikan nilai di persimpangan ke-4 garisan dan ke-5 lajur dalam julat A1:E11, iaitu nilai sel E4. cuma? Ya!
Cari mengikut berbilang kriteria dengan INDEX dan MATCH
Dalam buku teks VLOOKUP kami menunjukkan contoh formula dengan fungsi VLOOKUP untuk mencari mengikut pelbagai kriteria. Walau bagaimanapun, had ketara penyelesaian ini ialah keperluan untuk menambah lajur tambahan. Berita Baik: Formula INDEKS/PERLAWANAN boleh mencari mengikut nilai dalam dua lajur, tanpa perlu membuat lajur tambahan!
Katakan kita mempunyai senarai pesanan dan kita ingin mencari jumlah dengan dua kriteria − nama pembeli(Pelanggan) dan produk(Produk). Perkara ini rumit oleh fakta bahawa seorang pembeli boleh membeli beberapa produk yang berbeza sekaligus, dan nama pembeli dalam jadual pada helaian jadual carian disusun secara rawak.
Inilah formulanya INDEKS/PERLAWANAN menyelesaikan masalah:
(=INDEX("Jadual Carian"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3))
(=INDEX("Jadual carian"!$A$2:$C$13;MATCH(1;(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3))
Formula ini lebih rumit daripada yang lain yang kita bincangkan sebelum ini, tetapi bersenjata dengan pengetahuan tentang fungsinya INDEKS dan PERLAWANAN Anda akan mengatasinya. Bahagian yang paling sukar ialah fungsinya PERLAWANAN Saya rasa ia perlu dijelaskan terlebih dahulu.
MATCH(1,(A2="Lookup table"!$A$2:$A$13),0)*(B2="Lookup table"!$B$2:$B$13)
MATCH(1;(A2="Lookup table"!$A$2:$A$13);0)*(B2="Lookup table"!$B$2:$B$13)
Dalam formula yang ditunjukkan di atas, nilai yang anda cari ialah 1 , dan tatasusunan carian ialah hasil pendaraban. Okay, jadi apa yang perlu kita darabkan dan mengapa? Mari kita ambil semuanya mengikut urutan:
- Dapatkan nilai pertama dalam lajur A(Pelanggan) setiap helaian meja utama dan bandingkan dengan semua nama pembeli dalam jadual pada helaian jadual carian(A2:A13).
- Jika padanan ditemui, persamaan itu kembali 1 (BENAR), dan jika tidak - 0 (BERBOHONG).
- Seterusnya, kami melakukan perkara yang sama untuk nilai lajur B(Produk).
- Kemudian kita darabkan hasil yang diperolehi (1 dan 0). Hanya jika padanan ditemui dalam kedua-dua lajur (iaitu kedua-dua kriteria adalah benar) anda akan dapat 1 . Jika kedua-dua kriteria adalah palsu, atau hanya satu daripadanya dipenuhi, anda akan mendapat 0 .
Sekarang anda faham mengapa kami bertanya 1 sebagai nilai yang dikehendaki? Memang betul fungsinya PERLAWANAN mengembalikan kedudukan hanya apabila kedua-dua kriteria dipenuhi.
Catatan: Dalam kes ini, anda mesti menggunakan hujah fungsi pilihan ketiga INDEKS. Ia perlu, kerana dalam hujah pertama, kami menentukan keseluruhan jadual dan mesti memberitahu fungsi lajur untuk mengekstrak nilai. Dalam kes kami, ini adalah lajur C(Jumlah) dan kami memperkenalkan 3 .
Dan akhirnya, kerana kita perlu menyemak setiap sel dalam tatasusunan, formula ini mestilah formula tatasusunan. Anda boleh melihat ini dengan pendakap kerinting yang disertakan. Jadi apabila anda selesai memasukkan formula, jangan lupa untuk menekan Ctrl+Shift+Enter.
Jika semuanya dilakukan dengan betul, anda akan mendapat keputusan seperti dalam rajah di bawah:
INDEX dan MATCH digabungkan dengan IFERROR dalam Excel
Seperti yang anda mungkin telah perasan (dan lebih daripada sekali), jika anda memasukkan nilai yang salah, contohnya, yang tidak terdapat dalam tatasusunan yang sedang dilihat, formula INDEKS/PERLAWANAN melaporkan ralat #T/A(#N/A) atau #NILAI!(#VALUE!). Jika anda ingin menggantikan mesej sedemikian dengan sesuatu yang lebih mudah difahami, anda boleh memasukkan formula dengan INDEKS dan PERLAWANAN menjadi fungsi ESLIOSHIBKA.
Sintaks Fungsi ESLIOSHIBKA sangat ringkas:
IFERROR(value,value_if_error)
IFERROR(nilai, nilai_jika_ralat)
Di mana hujahnya nilai(nilai) ialah nilai yang diperiksa untuk ralat (dalam kes kami, hasil formula INDEKS/PERLAWANAN); dan hujahnya nilai_jika_ralat(value_if_error) ialah nilai untuk dikembalikan jika formula melemparkan ralat.
Sebagai contoh, anda boleh memasukkan fungsi ESLIOSHIBKA seperti ini:
IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11.0),MATCH($G$3,$A$1:$E$1.0)),
"Tiada padanan ditemui. Sila cuba lagi!") =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1 :$E$1;0));
"Tiada padanan ditemui. Sila cuba lagi!")
Dan sekarang, jika seseorang memasukkan nilai yang salah, formula akan memberikan hasil berikut:
Jika anda lebih suka membiarkan sel kosong sekiranya berlaku ralat, anda boleh menggunakan tanda petikan (“”) sebagai nilai hujah fungsi kedua ESLIOSHIBKA. seperti ini:
IFERROR(INDEX(array,MATCH(lookup_value, lookup_array,0),"")
IFERROR(INDEX(array,MATCH(lookup_value,lookup_array,0),"")
Saya berharap sekurang-kurangnya satu daripada formula yang diterangkan dalam tutorial ini kelihatan berguna kepada anda. Jika anda telah menemui tugas carian lain yang anda tidak dapat mencari penyelesaian yang sesuai antara maklumat dalam pelajaran ini, jangan ragu untuk menerangkan masalah anda dalam ulasan, dan kita semua akan cuba menyelesaikannya bersama-sama.
Katakan anda perlu mencari sambungan telefon pekerja menggunakan nombor mereka dan menganggarkan kadar Komisen dengan betul untuk jumlah jualan. Anda mencari data untuk mencari data tertentu dengan cepat dan cekap dalam senarai dan menyemak secara automatik sama ada data itu digunakan dengan betul. Selepas anda melihat data, anda boleh melakukan pengiraan dan memaparkan keputusan dengan menentukan nilai pulangan. Terdapat beberapa cara untuk mencari nilai dalam senarai data dan memaparkan hasilnya.
Dalam artikel ini
Mencari nilai dalam senarai secara menegak mengikut padanan tepat
Anda boleh menggunakan fungsi VLOOKUP atau gabungan fungsi INDEX dan MATCH untuk menyelesaikan tugas ini.
Contoh penggunaan fungsi VLOOKUP
Fungsi VLOOKUP.
Contoh INDEX dan PADANAN
Apakah maksudnya:
=INDEX(perlu mengembalikan nilai daripada C2:C10 yang akan sepadan dengan MATCH(nilai pertama "Kubis" dalam tatasusunan B2:B10))
Formula mencari nilai pertama dalam sel C2:C10 yang sepadan dengan kobis(dalam B7), dan mengembalikan nilai dalam C7 ( 100 ) ialah nilai pertama yang sepadan dengan kobis.
Untuk maklumat lanjut, lihat Fungsi indeks dan fungsi MATCH.
Mencari nilai dalam senarai secara menegak mengikut padanan anggaran
Untuk melakukan ini, gunakan fungsi VLOOKUP.
Penting: Pastikan nilai dalam baris pertama diisih dalam tertib menaik.
Dalam contoh di atas, fungsi VLOOKUP mencari nama pelajar yang mempunyai 6 kelewatan dalam julat A2:B7. Tiada catatan dalam jadual untuk 6 lewat, jadi VLOOKUP mencari padanan tertinggi seterusnya di bawah 6 dan mencari nilai 5 yang dikaitkan dengan nama pertama Dave, dan oleh itu kembali Dave.
Untuk maklumat lanjut, lihat bahagian fungsi VLOOKUP.
Mencari nilai menegak dalam senarai saiz yang tidak diketahui dengan padanan tepat
Untuk menyelesaikan tugas ini, gunakan fungsi OFFSET dan MATCH.
Catatan: Pendekatan ini digunakan jika data berada dalam julat data luaran yang anda kemas kini setiap hari. Anda tahu lajur B mempunyai Harga, tetapi anda tidak tahu berapa banyak baris data yang dikembalikan oleh pelayan dan lajur pertama tidak diisih mengikut abjad.
C1 ialah sel kiri atas julat (juga dipanggil sel permulaan).
Padankan("oren"; C2: C7; 0) mencari warna oren dalam julat C2:C7. Anda tidak boleh memasukkan sel permulaan dalam julat.
1 - bilangan lajur di sebelah kanan sel permulaan yang mana nilai pulangan harus dikembalikan. Dalam contoh kami, nilai pulangan adalah dalam lajur D, Jualan.
Mencari nilai dalam senarai secara mendatar mengikut padanan tepat
Untuk melaksanakan tugas ini, fungsi HLOOKUP digunakan. Di bawah adalah contoh.
Fungsi HLOOKUP menjalankan carian lajur Jualan dan mengembalikan nilai dari baris 5 dalam julat yang ditentukan.
Untuk maklumat lanjut, lihat bahagian fungsi HLOOKUP.
Cipta Formula Carian Menggunakan Wizard Carian (Excel 2007 sahaja)
Catatan: Tambah masuk Wizard Carian telah ditamatkan dalam Excel 2010. Fungsi ini telah digantikan oleh Wizard Fungsi dan fungsi carian dan rujukan (rujukan) yang tersedia.
Dalam Excel 2007, wizard carian mencipta formula carian berdasarkan data lembaran kerja yang mempunyai tajuk baris dan lajur. Wizard carian membantu anda mencari nilai lain dalam satu baris apabila anda mengetahui nilai dalam satu lajur, dan sebaliknya. Wizard carian menggunakan indeks dan MATCH dalam formula yang diciptanya.
Tujuan utama program pejabat Excel adalah pelaksanaan pengiraan. Dokumen program ini (Buku) boleh mengandungi banyak helaian dengan jadual panjang yang diisi dengan nombor, teks atau formula. Carian pantas automatik membolehkan anda mencari sel yang diperlukan di dalamnya.
Carian mudah
Untuk mencari nilai dalam hamparan Excel, pada tab Laman Utama, buka senarai lungsur turun alat Cari dan Ganti dan klik item Cari. Kesan yang sama boleh diperoleh menggunakan pintasan papan kekunci Ctrl + F.
Dalam kes paling mudah, dalam tetingkap Cari dan Ganti yang muncul, masukkan nilai yang anda cari dan klik Cari Semua.
Seperti yang anda lihat, hasil carian telah muncul di bahagian bawah kotak dialog. Nilai yang ditemui digariskan dengan warna merah dalam jadual. Jika bukannya "Cari Semua" anda mengklik "Cari Seterusnya", maka sel pertama dengan nilai ini akan dicari dahulu, dan apabila anda mengklik sekali lagi, sel kedua.
Teks dicari dengan cara yang sama. Dalam kes ini, teks carian ditaip dalam bar carian.
Jika data atau teks tidak dicari dalam keseluruhan hamparan Excel, maka kawasan carian mesti dipilih terlebih dahulu.
carian terperinci
Katakan anda ingin mencari semua nilai antara 3000 dan 3999. Dalam kes ini, anda akan menaip 3??? dalam rentetan carian. Kad bebas "?" menggantikan yang lain.
Menganalisis hasil carian yang dilakukan, dapat diperhatikan bahawa, bersama-sama dengan 9 keputusan yang betul, program ini juga menghasilkan yang tidak dijangka, digariskan dengan warna merah. Mereka dikaitkan dengan kehadiran nombor 3 dalam sel atau formula.
Anda boleh berpuas hati dengan kebanyakan keputusan yang diperoleh, mengabaikan yang salah. Tetapi fungsi carian dalam Excel 2010 dapat berfungsi dengan lebih tepat. Ini ialah alat Pilihan dalam kotak dialog.
Dengan mengklik "Pilihan", pengguna dapat melakukan carian lanjutan. Pertama sekali, mari kita perhatikan item "Kawasan carian", di mana nilai "Formula" ditetapkan secara lalai.
Ini bermakna carian telah dilakukan, termasuk dalam sel yang tidak mempunyai nilai, tetapi formula. Kehadiran nombor 3 di dalamnya memberikan tiga keputusan yang salah. Jika anda memilih "Nilai" sebagai skop carian, maka hanya data akan dicari dan hasil yang salah yang dikaitkan dengan sel formula akan hilang.
Untuk menyingkirkan satu-satunya hasil yang salah pada baris pertama, dalam tetingkap carian lanjutan, pilih item "Seluruh sel". Selepas itu, hasil carian menjadi 100% tepat.
Keputusan sedemikian boleh dicapai dengan segera memilih item "Seluruh Sel" (walaupun meninggalkan nilai "Formula" dalam "Kawasan Carian").
Sekarang mari kita beralih kepada item "Cari".
Jika bukannya "Pada helaian" lalai anda memilih nilai "Dalam buku", maka tidak perlu berada pada helaian sel yang anda cari. Tangkapan skrin menunjukkan bahawa pengguna memulakan carian semasa pada helaian kosong 2.
Item seterusnya dalam tetingkap carian lanjutan ialah "Semak imbas", yang mempunyai dua makna. Secara lalai, ia ditetapkan kepada "mengikut baris", yang bermaksud jujukan mengimbas sel mengikut baris. Memilih nilai lain - "mengikut lajur", hanya akan mengubah arah carian dan urutan hasil.
Apabila mencari dalam dokumen Microsoft Excel, anda juga boleh menggunakan kad bebas lain - "*". Jika dipertimbangkan "?" bermaksud sebarang aksara, kemudian "*" menggantikan bukan satu, tetapi sebarang bilangan aksara. Di bawah ialah tangkapan skrin carian untuk Louisiana.
Kadangkala carian perlu sensitif huruf besar. Jika perkataan louisiana ditulis dengan huruf besar, maka hasil carian tidak akan berubah. Tetapi jika anda memilih "Sensitif huruf besar-besaran" dalam tetingkap carian lanjutan, carian itu tidak akan berjaya. Program ini akan menganggap perkataan Louisiana dan louisiana berbeza, dan, sudah tentu, tidak akan menemui yang pertama daripada mereka.
Cari Varieti
Mencari padanan
Kadang-kadang perlu untuk mengesan nilai pendua dalam jadual. Untuk mencari padanan, anda perlu menyerlahkan julat carian terlebih dahulu. Kemudian, pada tab Laman Utama yang sama, dalam kumpulan Gaya, buka alat Pemformatan Bersyarat. Seterusnya, pilih item "Peraturan pemilihan sel" dan "Nilai pendua" secara berurutan.
Hasilnya ditunjukkan dalam tangkapan skrin di bawah.
Jika perlu, pengguna boleh menukar warna paparan visual sel yang dipadankan.
Penapisan
Satu lagi jenis carian ialah penapisan. Katakan pengguna ingin mencari nilai angka dalam lajur B antara 3000 dan 4000.
Seperti yang anda lihat, hanya baris yang memenuhi syarat yang dimasukkan dipaparkan. Semua yang lain disembunyikan buat sementara waktu. Untuk kembali ke keadaan awal, ulangi langkah 2.
Pelbagai pilihan carian telah dipertimbangkan menggunakan Excel 2010 sebagai contoh. Bagaimana untuk mencari dalam Excel versi lain? Terdapat perbezaan dalam peralihan kepada penapisan dalam versi 2003. Dalam menu "Data", pilih arahan "Penapis", "Autopenapis", "Keadaan" dan "Penapis Auto Pengguna" dalam urutan.
Video: Cari dalam hamparan Excel
