Dengan jumlah data yang besar lembar excel cepat atau lambat Anda harus mencari nilai yang ditransfer. Pencarian sederhana dilakukan dengan menggunakan perintah: "Temukan dan Pilih", yang terletak di grup "Pengeditan" pada tab "Beranda".

Lihat juga artikel versi videonya.

Anda juga dapat memanggil kotak dialog pencarian menggunakan pintasan tombol pintas " Ctrl+F«.

Faktanya, kotak dialog disebut " Temukan dan Ganti", karena ini menyelesaikan dua masalah terkait ini dan jika dipanggil melalui kombinasi panas" Ctrl+H“, maka semuanya akan sama, hanya jendela yang akan terbuka dengan tab “Ganti” diaktifkan.

Sekilas, semuanya sederhana di sini: masukkan kata dan temukan sel yang diperlukan di lembar Excel. Tentu saja, kami tidak dapat mengecualikan opsi pengembangan seperti itu ketika pencarian selesai dengan cepat, namun, situasi yang lebih mungkin terjadi adalah ketika Anda memasukkan nilai yang diinginkan, dan pengolah meja tidak mencarinya, atau Anda perlu melakukan pencarian longgar ketika teks yang dicari diingat kira-kira, yang sering terjadi ketika mencari orang dengan nama belakang.

Pada materi ini kita akan melihat nuansa dan rahasia jendela pencarian yang tidak diketahui sebagian besar pengguna.

Beberapa pengaturan yang dapat membantu dalam menyelesaikan tugas tersembunyi di balik tombol “Opsi”.

Mari kita pertimbangkan situasi ketika Anda perlu mencari karyawan Alexander Ivanovich Komarov, tetapi ada beberapa faktor yang menyebabkan kesulitan:

• kita tidak tahu cara penulisannya, dengan penguraian lengkap nama dan patronimiknya, atau hanya inisialnya;
• ada ketidakpastian tertentu tentang huruf kedua mana yang “a” atau “o”;
• Tidak jelas bagaimana tepatnya nama belakang karyawan tersebut dicatat, lalu nama depan dan tengahnya, atau sebaliknya.

Komarov Alexander Ivanovich dapat ditulis sebagai: A. Komarov, Komarov Alexander, K A Marov Alexander I., dll. pilihan sudah cukup.

Untuk melakukan pencarian tidak ketat, Anda harus menggunakan simbol wildcard, disebut juga simbol liar. Jendela pencarian di Excel mendukung dua karakter berikut: “*” dan “?”:

• "*" cocok dengan sejumlah karakter;
• "?" cocok dengan karakter apa pun.

Di sini langsung muncul pertanyaan, apa yang harus dilakukan jika perlu mencari tanda tanya atau tanda bintang (disebut tanda perkalian)? Sederhana saja, cukup letakkan tanda tilde “~” di depan karakter yang Anda cari, masing-masing, jika Anda perlu mencari tilde, maka Anda perlu meletakkan dua tilde.

Jadi, untuk melakukan pencarian yang tidak akurat dengan nama keluarga yang disebutkan di atas, Anda harus menulis “K?marov” di jendela pencarian dan klik tombol “Temukan semua”, dalam hal ini semua catatan yang memenuhi kondisi yang ditentukan akan ditampilkan di jendela pencarian, tetapi hanya jika opsi "Seluruh sel" tidak dicentang.

Parameter "Seluruh sel" berarti mencari secara ketat string teks yang dimasukkan di bidang temukan dan tidak lebih; ​​dengan kata lain, jika ada inisial selain nama belakang, maka catatan tidak akan ditemukan.

Pada saat yang sama, jika Anda sedikit mengatur ulang kueri “*K?marov*”, maka catatan akan ditemukan terlepas dari aktivitas parameter “Seluruh sel”.

Ada beberapa aturan penting yang perlu diingat saat mencari di Excel:

1. Pencarian dilakukan di area yang dipilih, dan jika tidak ada pilihan, maka di seluruh lembar. Jadi jangan heran jika Anda secara tidak sengaja memilih beberapa sel kosong, meluncurkan jendela pencarian dan tidak menemukan apa pun.
2. Pencarian tidak memperhitungkan pemformatan, oleh karena itu, Anda tidak boleh menambahkan simbol mata uang apa pun.
3. Saat bekerja dengan tanggal, lebih baik mencarinya dalam format default untuk sistem tertentu, dalam hal ini, Excel akan menemukan semua tanggal yang memenuhi kondisi tersebut. Misalnya, jika sistem menggunakan format d/m/y, maka permintaan pencarian*/12/2015 akan menampilkan semua tanggal untuk Desember 2015, terlepas dari formatnya (28/12/2015, 28/12/2015, atau 28 Desember 2015, dll.).

Cara mencari nilai di tabel lain atau kekuatan VLOOKUP

Sebenarnya, dalam artikel ini saya ingin berbicara tentang kemungkinan tidak hanya fungsi VLOOKUP, tapi saya juga ingin menyentuhnya MENCARI, sebagai fungsi yang sangat terkait dengan VPR. Masing-masing fungsi ini memiliki kelebihan dan kekurangannya masing-masing. Singkatnya, VLOOKUP mencari nilai tertentu yang kita tentukan di antara sekian banyak nilai yang terletak dalam satu kolom. Mungkin paling sering kebutuhan VLOOKUP muncul ketika Anda perlu membandingkan data, mencari data di tabel lain, menambahkan data dari satu tabel ke tabel lainnya, berdasarkan beberapa kriteria, dll.
Untuk memahami prinsip kerja sedikit lebih baik VLOOKUP lebih baik mulai dengan sesuatu contoh praktis. Ada tabel seperti ini:
Gambar.1

dan dari tabel pertama harus diganti menjadi tanggal kedua untuk setiap nama keluarga. Untuk tiga catatan, ini bukan masalah dan melakukannya secara manual sudah jelas. Namun dalam kehidupan nyata, ini adalah tabel dengan ribuan catatan, dan pencarian dengan substitusi data secara manual dapat memakan waktu lebih dari satu jam. Ditambah beberapa lagi yang menarik: tidak hanya nama-namanya yang ditempatkan sepenuhnya di dalamnya dalam urutan yang berbeda Pada kedua tabel, jumlah record dalam tabel berbeda, dan tabel terletak pada lembar/buku yang berbeda. Saya yakin saya telah meyakinkan Anda bahwa substitusi manual bukanlah suatu pilihan sama sekali. Tetapi VLOOKUP akan sangat diperlukan di sini. Dalam hal ini, praktis tidak ada yang perlu dilakukan - cukup tulis di sel pertama kolom C pada tabel kedua (di mana Anda perlu mengganti tanggal dari tabel pertama) rumus ini:
=VLOOKUP($A2 ;Lembar1!$A$2:$C$4 ;3,0)
Anda dapat menulis rumus baik secara langsung ke dalam sel, atau menggunakan manajer fungsi, dengan memilih kategori Tautan dan Array VPR dan secara terpisah menunjukkan kriteria yang diperlukan. Sekarang kita salin ( Ctrl+C) sel dengan rumus, pilih semua sel di kolom C sampai akhir data dan masukkan ( Ctrl+V).

Pertama, prinsip dasar operasi: VLOOKUP mencari di kolom pertama argumen Tabel untuk nilai yang ditentukan oleh argumen tersebut Nilai_penelusuran . Ketika Anda menemukan nilai yang diinginkan fungsi mengembalikan nilai yang berlawanan dengan nilai yang ditemukan, tetapi dari kolom yang ditentukan oleh argumen Nomor_kolom . Kita akan membahas tampilan selang waktu nanti. VLOOKUP hanya dapat mengembalikan satu nilai - nilai pertama yang cocok dengan kriteria. Jika nilai yang dicari tidak ditemukan (tidak ada di tabel), maka hasil fungsinya adalah #T/A . Tidak perlu takut akan hal ini - ini bahkan berguna. Anda akan mengetahui dengan pasti catatan mana yang hilang dan dengan demikian dapat membandingkan dua tabel satu sama lain. Terkadang yang Anda lihat: ada data di kedua tabel, tetapi VLOOKUP menghasilkan #N/A. Artinya data di tabel Anda tidak identik. Beberapa di antaranya memiliki spasi ekstra yang tidak mencolok (biasanya sebelum atau sesudah nilai), atau karakter Sirilik dicampur dengan karakter Latin. Juga #T/A jika kriterianya berupa angka dan pada tabel yang diinginkan dituliskan dalam bentuk teks (biasanya di sebelah kiri sudut atas sel seperti itu muncul segitiga hijau), dan pada hasil akhir - sebagai angka. Atau sebaliknya.

Deskripsi argumen VLOOKUP
$A2 - argumen Nilai_penelusuran(sebut saja Kriteria untuk singkatnya). Inilah yang kami cari. Itu. untuk rekor pertama tabel kedua adalah Petrov S.A. Di sini Anda dapat menentukan teks kriteria secara langsung (dalam hal ini harus dalam tanda kutip - =VLOOKUP("Petrov S.A" ;Sheet1!$A$2:$C$4;3;0) atau tautan ke sel dengan teks ini (seperti pada contoh fungsi). Ada sedikit nuansa: Anda juga dapat menggunakan karakter wildcard: "*" dan "?". Ini sangat berguna jika Anda perlu mencari nilai hanya untuk sebagian string. Misalnya, Anda tidak boleh memasukkan “Petrov S.A” secara lengkap, tetapi hanya memasukkan nama belakang dan tanda bintang - “Petrov*”. Kemudian entri apa pun yang dimulai dengan “Petrov” akan ditampilkan. Jika Anda perlu menemukan catatan di mana nama keluarga “Petrov” muncul di mana saja pada baris tersebut, maka Anda dapat menentukannya seperti ini: “*Petrov*”. Jika Anda ingin mencari nama keluarga Petrov dan tidak peduli apa inisial nama depan dan patronimiknya (jika nama lengkap ditulis dalam bentuk Ivanov I.I.), maka tampilan ini tepat: “Ivanov?.?. ” . Seringkali perlu untuk menunjukkan nilainya untuk setiap baris (di kolom A Nama Keluarga dan Anda perlu menemukan semuanya). Dalam hal ini, referensi ke sel kolom A selalu ditunjukkan, misalnya, di sel A1 tertulis: Ivanov. Diketahui juga bahwa Ivanov ada di tabel lain, tetapi setelah nama belakang, nama depan dan tengah (atau yang lainnya) dapat ditulis. Tapi kita hanya perlu mencari string yang dimulai dengan nama belakang. Maka Anda perlu menulisnya sebagai berikut: A1 &"*" . Entri ini akan setara dengan "Ivanov*". A1 ditulis Ivanov, ampersand(&) digunakan untuk menggabungkan dua nilai teks menjadi satu string. Tanda bintang diapit tanda petik (sebagaimana seharusnya teks di dalam rumus). Jadi kita mendapatkan:
A1&"*" =>
"Ivanov"&"*" =>
"Ivanov*"
Sangat nyaman jika ada banyak nilai yang harus dicari.
Jika Anda perlu menentukan apakah ada kata di suatu tempat dalam satu baris, beri tanda bintang di kedua sisi: "*"& A1 &"*"

Sheet1!$A$2:$C$4 - argumen Meja. Menentukan rentang sel. Hanya rentang yang harus berisi data dari sel data pertama hingga terakhir. Kisaran ini tidak harus berupa kisaran yang ditunjukkan dalam contoh. Jika ada 100 baris, maka Sheet1!$A$2:$C$100. Penting untuk mengingat tiga hal: pertama, ini Meja harus selalu dimulai dengan kolom yang kita cari Kriteria . Dan tidak ada yang lain. Jika tidak, tidak akan ditemukan apa pun atau hasilnya tidak akan seperti yang Anda harapkan. Kedua: argumen Meja harus "diperbaiki" . Apa maksudnya? Apakah Anda melihat tanda dolar? Ini adalah konsolidasi (lebih tepatnya, ini disebut referensi rentang absolut). Bagaimana hal itu dilakukan. Sorot teks tautan (hanya satu rentang - satu kriteria) dan tekan F4 sampai Anda melihat dolar muncul sebelum nama kolom dan nomor baris. Jika hal ini tidak dilakukan, maka saat menyalin rumus, argumen Tabel akan “keluar” dan hasilnya akan salah lagi. Dan terakhir, tabel harus berisi kolom dari yang pertama (yang kita cari) hingga yang terakhir (yang darinya kita perlu mengembalikan nilai). Dalam contoh Lembar1!$A$2:$C$4- ini berarti tidak mungkin mengembalikan nilai dari kolom D(4), karena tabel hanya memiliki tiga kolom.

3 - Nomor_kolom. Di sini kita cukup menunjukkan nomor kolom dalam argumen Meja, nilai yang perlu kita substitusikan sebagai hasilnya. Dalam contoh, ini adalah Tanggal Penerimaan - mis. kolom nomor 3. Jika kita membutuhkan sebuah departemen, maka kita akan menunjukkan 2, dan jika kita hanya perlu membandingkan apakah ada nama dari satu tabel di tabel lain, maka kita dapat menentukan 1. Penting: argumen Nomor_kolom tidak boleh melebihi jumlah kolom dalam argumen Meja . Jika tidak, rumusnya akan menghasilkan kesalahan #LINK!. Misalnya, jika rentang $B$2:$C$4 ditentukan sebagai rentang dan Anda perlu mengembalikan data dari kolom C, maka menentukan 2 adalah benar. Karena argumen Meja($B$2:$C$4) hanya berisi dua kolom - B dan C. Jika Anda mencoba menentukan nomor kolom 3 (seperti yang terlihat pada lembar), Anda akan mendapatkan kesalahan #LINK!, Karena tidak ada kolom ketiga dalam rentang yang ditentukan.

Saran praktis: jika argumen Tabel memiliki terlalu banyak kolom dan Anda perlu mengembalikan hasil dari kolom terakhir, maka jumlahnya sama sekali tidak perlu dihitung. Anda dapat menentukannya seperti ini: =VLOOKUP($A2 ;Sheet1! $A$2:$C$4 ;COLUMN NUM(Sheet1! $A$2:$C$4);0) . Omong-omong, dalam hal ini Sheet1! juga dapat dihapus jika tidak perlu: ​​=VLOOKUP($A2 ;Sheet1! $A$2:$C$4 ;COLUMN NUM($A$2:$C$4);0) .

0 - Tampilan_selang waktu- argumen yang sangat menarik. Bisa BENAR atau SALAH. Pertanyaan yang langsung muncul: kenapa rumus saya ada 0? Ini sangat sederhana - Excel dalam rumus dapat memperlakukan 0 sebagai SALAH dan 1 sebagai BENAR. Jika Anda menentukan parameter ini di VLOOKUP sama dengan 0 atau FALSE, maka kecocokan persis dengan Kriteria yang ditentukan akan dicari. Ini tidak ada hubungannya dengan wildcard ("*" dan "?"). Jika Anda menggunakan 1 atau TRUE (atau tidak menentukan argumen terakhir sama sekali, karena secara default adalah TRUE), maka... Ceritanya sangat panjang. Singkatnya - VLOOKUP akan mencari nilai paling mirip yang cocok Kriteria . Terkadang sangat berguna. Namun, jika Anda menggunakan parameter ini, maka daftar dalam argumen Tabel harus diurutkan dalam urutan menaik. Harap dicatat bahwa pengurutan hanya diperlukan jika argumen Interval_lookup adalah TRUE atau 1. Jika argumennya 0 atau FALSE, pengurutan tidak diperlukan.

Banyak orang mungkin memperhatikan bahwa dalam gambar saya terdapat departemen untuk nama lengkap yang tertukar. Ini bukan kesalahan pencatatan. Contoh yang dilampirkan pada artikel menunjukkan bagaimana Anda bisa mengganti keduanya dan tanggal dengan satu rumus, tanpa mengubah argumen secara manual Nomor_kolom. Bagi saya, contoh seperti itu bisa sangat berguna.

Bagaimana cara menghindari kesalahan #N/A (#N/A) di VLOOKUP?
Lagi masalah umum- banyak orang tidak ingin melihat #N/A sebagai hasilnya jika tidak ditemukan kecocokan. Ini mudah untuk dilakukan:
=IF(END(VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0));"";VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0)))
Sekarang jika VLOOKUP tidak menemukan kecocokan, sel tersebut akan kosong.
Dan pengguna Excel 2007 dan versi yang lebih tinggi dapat menggunakan IFERROR:
=IFERROR(VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0);"")

PERTANDINGAN yang Dijanjikan

Fungsi ini mencari nilai yang ditentukan oleh parameter Nilai_penelusuran dalam argumen Lihat_array. Dan hasil dari fungsinya adalah nomor posisi dari nilai yang ditemukan Lihat_array. Ini adalah nomor posisi, bukan nilai itu sendiri. Pada prinsipnya saya tidak akan menjelaskannya secara detail, karena poin utamanya sama persis. Jika kita ingin menerapkannya pada tabel di atas maka akan menjadi seperti ini:
=MATCH($A2,Lembar1! $A$2:$A$4,0)
$A2 - Nilai_penelusuran. Semuanya di sini persis sama dengan VPR. Karakter wildcard juga diperbolehkan dan dalam desain yang persis sama.

Lembar1! $A$2:$A$4 - Array yang akan dilihat. Perbedaan utama dari VLOOKUP adalah Anda dapat menentukan array dengan hanya satu kolom. Ini seharusnya menjadi kolom yang akan kita cari Nilai_penelusuran . Jika Anda mencoba menentukan lebih dari satu kolom, fungsi tersebut akan mengembalikan kesalahan.

tipe_cocok(0) - sama seperti di VPR Tampilan_selang waktu . Dengan fitur yang sama. Bedanya hanya pada kemampuan mencari yang terkecil dari yang diinginkan atau yang terbesar. Tapi saya tidak akan membahasnya di artikel ini.

Kami telah memilah dasar-dasarnya. Tapi kita tidak perlu mengembalikan nomor posisinya, tapi nilainya sendiri. Artinya SEARCH dalam bentuknya yang murni tidak cocok untuk kita. Setidaknya satu, dengan sendirinya. Namun jika digunakan bersamaan dengan fungsi INDEX maka inilah yang kita perlukan bahkan lebih.
=INDEX(Lembar1! $A$2:$C$4 ;MATCH($A2 ;Lembar1! $A$2:$A$4 ;0);2)
Rumus ini akan memberikan hasil yang sama seperti VLOOKUP.

Argumen Fungsi INDEKS
Lembar1! $A$2:$C$4 - Himpunan. Sebagai argumen ini, kami menentukan rentang dari mana kami ingin mendapatkan nilainya. Bisa ada satu kolom atau beberapa. Jika hanya ada satu kolom, maka argumen fungsi terakhir tidak perlu ditentukan. Omong-omong, argumen ini mungkin tidak sesuai sama sekali dengan argumen yang kita tentukan dalam argumen Lookup_array pada fungsi MATCH.

Berikutnya adalah Nomor_Baris dan Nomor_Kolom. Sebagai Row_Number kita mengganti MATCH, yang mengembalikan kita nomor posisi dalam array. Di sinilah segala sesuatunya dibangun. INDEX mengembalikan nilai dari Array yang ada di baris tertentu (RowNumber) dari Array dan kolom tertentu (ColumnNumber) jika terdapat lebih dari satu kolom. Penting untuk diketahui bahwa dalam kombinasi ini, jumlah baris dalam argumen Array dari fungsi INDEX dan jumlah baris dalam argumen Lookup_array dari fungsi MATCH harus cocok. Dan mulai dari baris yang sama. Ini dalam kasus biasa, kecuali Anda mengejar tujuan lain.
Seperti halnya VLOOKUP, INDEX mengembalikan #N/A jika nilai yang diinginkan tidak ditemukan. Dan Anda dapat mengatasi kesalahan tersebut dengan cara yang sama:
Untuk semua versi Excel (termasuk 2003 dan sebelumnya):
=IF(END(MATCH($A2,Sheet1! $A$2:$A$4,0));"";INDEX(Sheet1! $A$2:$C$4;MATCH($A2,Sheet1! $A$2: $A$4 ;0);2))
Untuk versi 2007 dan lebih tinggi:
=IFERROR(INDEX(Lembar1! $A$2:$C$4,MATCH($A2,Lembar1!$A$2:$A$4,0),2);"")

Bekerja dengan kriteria yang lebih panjang dari 255 karakter
INDEX-SEARCH juga memiliki satu keunggulan lagi dibandingkan VLOOKUP. Intinya VLOOKUP tidak bisa mencari nilai panjang baris yang berisi lebih dari 255 karakter. Hal ini jarang terjadi, namun memang terjadi. Anda tentu saja dapat melakukan cheat VLOOKUP dan mengurangi kriterianya:
=VLOOKUP(PSTR($A2,1,255),PSTR(Lembar1!$A$2:$C$4,1,255),3,0)
tapi ini adalah rumus array. Selain itu, rumus seperti itu tidak selalu memberikan hasil yang diinginkan. Jika 255 karakter pertama identik dengan 255 karakter pertama dalam tabel, lalu karakternya berbeda, rumus tidak akan melihat hal ini lagi. Dan rumusnya hanya mengembalikan nilai teks, yang sangat tidak nyaman jika angka harus dikembalikan.

Oleh karena itu, lebih baik menggunakan rumus rumit ini:
=INDEX(Lembar1!$A$2:$C$4,SUMPRODUK(MATCH(TRUE,Sheet1!$A$2:$A$4 =$A2,0));2)
Di sini saya menggunakan rentang yang sama dalam rumus keterbacaan, tetapi dalam contoh pengunduhan, rentang tersebut berbeda dari yang ditunjukkan di sini.
Rumusnya sendiri dibangun berdasarkan kemampuan fungsi SUMPRODUK untuk bertransformasi menjadi perhitungan besar-besaran dari beberapa fungsi di dalamnya. Dalam hal ini, MATCH mencari posisi baris yang kriterianya sama dengan nilai pada baris tersebut. Karakter wildcard tidak dapat digunakan lagi di sini.

Dalam contoh yang dilampirkan pada artikel, Anda akan menemukan contoh penggunaan semua kasus yang dijelaskan dan contoh mengapa INDEX dan MATCH terkadang lebih disukai daripada VLOOKUP.

Unduh contoh

(26,0 KiB, 14.082 unduhan)

("Bilah bawah":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"kiri bawah","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"kanan","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"tampilan:blok; perataan teks:kiri;","textbgcss":"tampilan:absolute; kiri:0px; warna latar:#333333; ","titlecss":"tampilan:blok; posisi:relatif; font:tebal 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; warna:#fff;","deskripsicss":"tampilan:blok; posisi:relatif; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; warna:#fff; margin-top:8px;","buttoncss":"display:block; posisi:relatif; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"ukuran font:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Cukup sulit untuk dideteksi informasi yang diperlukan pada lembar kerja dengan sejumlah besar data. Namun, kotak dialog Temukan dan Ganti membuat pencarian informasi menjadi lebih mudah. Selain itu, ia memiliki beberapa fitur berguna yang tidak disadari oleh banyak pengguna.

Jalankan perintah Pengeditan Beranda Temukan dan sorot Temukan(atau klik Ctrl+F) untuk membuka kotak dialog Temukan dan Ganti. Jika Anda perlu mengganti data, maka pilih perintah Pengeditan Beranda Temukan dan pilih Ganti(atau klik Ctrl+H). Perintah persis yang Anda jalankan menentukan di antara dua tab mana kotak dialog terbuka.

Jika pada kotak dialog yang terbuka Menemukan dan tekan tombol untuk mengganti Pilihan, maka mereka akan ditampilkan opsi tambahan mencari informasi (Gbr. 21.1).

Dalam banyak kasus, lebih baik menentukan perkiraan daripada teks persisnya dalam pencarian, misalnya gantungan kunci dinding. Misalnya, untuk mencari data klien Ivan Smirnov, tentu saja Anda dapat memasukkan teks persisnya di kolom pencarian. Namun, kemungkinan besar Anda tidak akan menemukan apa pun, karena Anda mungkin memasukkan nama klien secara berbeda, seperti Ivan Smirnov atau I. Smirnov, atau bahkan membuat kesalahan pada nama belakang. Saat mencari nama seperti itu, yang terbaik adalah menggunakan wildcard.

Memasuki aku *menyeringai* di lapangan Menemukan lalu klik tombolnya Temukan semuanya. Menggunakan wildcard tidak hanya mengurangi jumlah kata yang Anda ketik, namun juga memastikan bahwa Anda menemukan data pelanggan jika ada di lembar kerja. Tentu saja, hasil pencarian mungkin berisi entri yang tidak sesuai dengan tujuan pencarian Anda, tapi ini lebih baik daripada tidak sama sekali.

Saat mencari menggunakan kotak dialog Temukan dan Ganti dua wildcard dapat digunakan:

• ? - cocok dengan karakter apa pun;
• * - cocok dengan sejumlah karakter.

Selain itu, karakter wildcard ini juga dapat digunakan saat mencari nilai numerik. Misalnya, jika Anda menentukan di bilah pencarian 3* , hasilnya akan menampilkan semua sel yang berisi nilai yang dimulai dengan 3, dan jika Anda memasukkan 1?9, Anda akan mendapatkan semua entri tiga digit yang dimulai dengan 1 dan diakhiri dengan 9.

Untuk mencari tanda tanya atau tanda bintang, awali dengan tanda gelombang (~).
Misalnya, baris berikutnya pencarian menemukan teks *NONE*: -*N0NE~*
Untuk menemukan karakter tilde, letakkan dua tilde di bilah pencarian.

Jika menurut Anda pencarian Anda tidak berfungsi dengan benar, periksa tiga pengaturan berikut (yang terkadang berubah dengan sendirinya).

• kotak centang Kasus pertandingan- atur agar huruf besar/kecil teks yang dicari cocok dengan huruf besar/kecil teks yang ditentukan. Misalnya, jika Anda memasukkan kata Ivan dalam pencarian dan mencentang kotak yang ditentukan, kata Ivan tidak akan muncul di hasil pencarian.
• kotak centang Seluruh sel- atur untuk menemukan sel yang berisi teks persis seperti yang ditentukan di bilah pencarian. Misalnya, jika Anda mengetik Excel di bilah pencarian dan mencentang kotaknya, Anda tidak akan menemukan sel yang berisi frasa Microsoft Excel.
• Daftar tarik-turun Daerah pencarian- daftar berisi tiga item: nilai, rumus, dan catatan. Misalnya, jika Anda memasukkan angka 900 di bilah pencarian dan di daftar drop-down Daerah pencarian Jika Anda memilih item nilai, Anda tidak akan melihat sel berisi nilai 900 di hasil pencarian jika diperoleh dengan menggunakan rumus.

Ingatlah bahwa pencarian dilakukan dalam rentang sel yang dipilih. Jika Anda perlu mencari seluruh lembar, pilih hanya satu sel sebelum Anda mulai mencari.

Juga, perhatikan bahwa menggunakan jendela Temukan dan Ganti nilai numerik yang diformat tidak dapat ditemukan. Misalnya saja jika Anda masuk ke dalam bilah pencarian $5* , nilai yang menerapkan format mata uang dan sepertinya $54,00 tidak akan ditemukan.

Bekerja dengan tanggal dapat menjadi tantangan karena Excel mendukung begitu banyak format tanggal. Jika Anda mencari tanggal yang menerapkan format default, Excel akan menemukan tanggal meskipun tanggal tersebut diformat dalam berbagai cara. Misalnya, jika sistem Anda menggunakan format tanggal m/d/y, string pencarian 10/*/2010 akan menemukan semua tanggal di bulan Oktober 2010, apa pun formatnya.

Gunakan bidang kosong Ganti dengan untuk dengan cepat menghapus beberapa informasi dari lembar kerja. Misalnya, masukkan - * di kolom Menemukan dan meninggalkan lapangan Mengganti kosong. Kemudian klik tombolnya Ganti semua agar Excel menemukan dan menghapus semua bintang di lembar kerja.

Katakanlah Anda perlu mencari ekstensi telepon karyawan menggunakan nomor teleponnya dan juga memperkirakan dengan tepat rasio Komisi untuk jumlah penjualan. Anda mencari data untuk menemukan data spesifik dalam daftar dengan cepat dan efisien dan secara otomatis memeriksa apakah data tersebut digunakan dengan benar. Setelah Anda melihat data, Anda dapat melakukan penghitungan dan menampilkan hasilnya, yang menunjukkan nilai yang dikembalikan. Ada beberapa cara untuk mencari nilai dalam daftar data dan menampilkan hasilnya.

Dalam artikel ini

Temukan nilai dalam daftar secara vertikal dengan pencocokan tepat

Anda dapat menggunakan fungsi VLOOKUP atau kombinasi fungsi INDEX dan MATCH untuk melakukan tugas ini.

Contoh penggunaan fungsi VLOOKUP

fungsi VLOOKUP.

Contoh INDEKS dan PERTANDINGAN

Apa artinya:

=INDEX(Anda perlu mengembalikan nilai dari C2:C10, yang akan cocok dengan MATCH(nilai pertama "Kubis" dalam array B2:B10))

Rumusnya mencari nilai pertama di sel C2:C10 yang sesuai kubis(di B7), dan mengembalikan nilai di C7 ( 100 ) - nilai pertama yang sesuai kubis.

Untuk informasi lebih lanjut, lihat Fungsi INDEX dan Fungsi MATCH.

Temukan nilai dalam daftar secara vertikal dengan perkiraan kecocokan

Untuk melakukan ini, gunakan fungsi VLOOKUP.

Penting:

Pada contoh di atas, fungsi VLOOKUP mencari nama siswa yang mempunyai 6 keterlambatan pada rentang A2:B7. Tidak ada entri di tabel untuk 6 terlambat, jadi fungsi VLOOKUP mencari kecocokan tertinggi berikutnya di bawah 6 dan menemukan nilai 5 yang terkait dengan nama depan Dave, dan karena itu kembali Dave.

Untuk informasi lebih lanjut, lihat Fungsi VLOOKUP.

Menemukan nilai vertikal dalam daftar yang ukurannya tidak diketahui dengan pencocokan tepat

Untuk melakukan tugas ini, gunakan fungsi OFFSET dan MATCH.

Catatan: Pendekatan ini digunakan jika data berada dalam rentang data eksternal yang Anda perbarui setiap hari. Anda tahu bahwa kolom B memiliki Harga, tetapi Anda tidak tahu berapa banyak baris data yang dikembalikan server, dan kolom pertama tidak diurutkan berdasarkan abjad.

C1 adalah sel kiri atas rentang (disebut juga sel awal).

Cocok("jeruk"; C2:C7; 0) mencari warna oranye pada rentang C2:C7. Anda tidak boleh menyertakan sel awal dalam rentang tersebut.

1 - jumlah kolom di sebelah kanan sel awal yang nilai kembaliannya harus dikembalikan. Dalam contoh kita, nilai yang dikembalikan ada di kolom D, Penjualan.

Temukan nilai dalam daftar secara horizontal dengan pencocokan tepat

Untuk melakukan tugas ini, fungsi GLOOKUP digunakan. Di bawah ini adalah contohnya.

Fungsi LOOKUP mencari kolom Penjualan dan mengembalikan nilai dari baris 5 dalam rentang yang ditentukan.

Untuk informasi lebih lanjut, lihat Fungsi LOOKUP.

Menemukan nilai dalam daftar secara horizontal menggunakan perkiraan kecocokan

Untuk melakukan tugas ini, fungsi GLOOKUP digunakan.

Penting: Pastikan nilai pada baris pertama diurutkan dalam urutan menaik.

Pada contoh di atas, fungsi GLOOKUP mencari nilai 11000 pada baris 3 dalam rentang yang ditentukan. Ia tidak menemukan 11000 dan oleh karena itu mencari nilai terbesar berikutnya yang kurang dari 1100 dan mengembalikan angka 10543.

Untuk informasi lebih lanjut, lihat Fungsi LOOKUP.

Membuat rumus pencarian menggunakan Panduan Pencarian (khusus Excel 2007)

Catatan: Add-in Wizard Pencarian telah dihentikan di Excel 2010. Ini fungsionalitas digantikan oleh fungsi wizard dan fungsi pencarian dan referensi (referensi) yang tersedia.

Di Excel 2007, Panduan Pencarian membuat rumus pencarian berdasarkan data lembar kerja yang memiliki judul baris dan kolom. Wizard Pencarian membantu Anda menemukan nilai lain dalam satu baris ketika Anda mengetahui nilai dalam satu kolom, dan sebaliknya. Panduan Pencarian menggunakan indeks dan MATCH dalam rumus yang dibuatnya.

Jika Anda mencari fungsi wildcard, Google akan menunjukkan bahwa VLOOKUP jauh lebih populer daripada fungsi INDEX. Hal ini dapat dimengerti, karena untuk memberikan fungsi INDEX fungsi yang sama dengan VLOOKUP, Anda perlu menggunakan rumus lain - SEARCH. Bagi saya, selalu merupakan tantangan untuk mencoba dan menguasai dua fitur baru secara bersamaan. Namun mereka memberikan lebih banyak peluang dan fleksibilitas dalam berkreasi spreadsheet. Tapi hal pertama yang pertama.

fungsi VLOOKUP()

Katakanlah Anda memiliki tabel dengan data karyawan. Kolom pertama menyimpan nomor personel karyawan, sisanya - data lainnya (nama lengkap, departemen, dll). Jika Anda memiliki nomor personel, Anda dapat menggunakan fungsi VLOOKUP untuk mengembalikan informasi tertentu tentang karyawan tersebut. Sintaks rumus =VLOOKUP(nilai_pencarian, tabel, nomor_kolom, [pencarian_interval]). Dia memberi tahu Excel: "Temukan meja baris yang sel pertamanya cocok nilai_pencarian, dan mengembalikan nilai sel dengan nomor seri nomor_kolom».

Namun ada kalanya Anda memiliki nama karyawan dan Anda perlu mengembalikan nomor personelnya. Pada gambar di sel A10 terdapat nama karyawan dan Anda perlu menentukan nomor personel di sel B10.

Jika kolom kunci berada di sebelah kanan data yang Anda inginkan, VLOOKUP tidak akan membantu. Jika, tentu saja, ada kemungkinan untuk bertanya nomor_kolom-1, maka tidak akan ada masalah. Salah satu solusi umum adalah menambahkan kolom A baru, menyalin nama karyawan ke kolom tersebut, mengisi nomor personel menggunakan VLOOKUP, menyimpannya sebagai nilai, dan menghapus kolom A sementara.

fungsi INDEKS()

Untuk menyelesaikan soal kita dalam satu langkah, kita perlu menggunakan rumus INDEX dan MATCH. Kompleksitas pendekatan ini adalah Anda perlu menggunakan dua fungsi yang mungkin belum pernah Anda gunakan sebelumnya. Untuk menyederhanakan pemahaman, kami akan menyelesaikan masalah ini dalam dua tahap.

Mari kita mulai dengan fungsi INDEX. Nama yang buruk. Ketika seseorang mengatakan "indeks", tidak ada satu pun asosiasi yang muncul di kepala saya tentang fungsi fungsi ini. Dan itu memerlukan tiga argumen: =INDEX(array, nomor_baris, [nomor_kolom]).

Secara sederhana, Excel memasukkan array data dan mengembalikan nilai pada perpotongan baris dan kolom tertentu. Tampaknya sederhana. Jadi, rumus =INDEX($A$2:$C$6,4,2) akan mengembalikan nilai yang ditemukan di sel B5.

Sehubungan dengan masalah kita, untuk mengembalikan nomor personel karyawan, rumusnya akan terlihat seperti ini =INDEX($A$2:$A$6;?;1). Kelihatannya tidak masuk akal, tapi jika kita mengganti tanda tanya dengan rumus MATCH, kita punya solusinya.

fungsi PERTANDINGAN()

Sintaks untuk fungsi ini adalah: =MATCH(nilai_pencarian, array_pencarian, [tipe_pencocokan]).

Dia memberi tahu Excel: "Temukan nilai_pencarian dalam larik data dan kembalikan nomor baris larik tempat nilai ini muncul." Jadi, untuk mencari baris mana nama karyawan di sel A10, Anda perlu menulis rumus =MATCH(A10, $B$2:$B$6, 0). Jika nama "Colin Farel" ada di sel A10, maka MATCH akan mengembalikan baris ke-5 dari array B2:B6.

Pada dasarnya, itu saja. Fungsi MATCH memberi tahu fungsi INDEX baris mana yang harus dicari nilainya. Ganti tanda tanya pada rumus INDEX dengan rumus SEARCH dan Anda akan mendapatkan padanan VLOOKUP dengan kemampuan mencari data yang terletak di sebelah kiri kolom kunci. Ini akan menjadi seperti ini:

INDEKS($A$2:$B$6,MATCH(A10,$B$2:$B$6,0),1)

Pada awalnya hal ini tidak biasa, ada godaan besar untuk memasukkan kolom lain dan bekerja seperti biasa. Namun seiring berjalannya waktu, penggunaan fungsi INDEX menjadi sebuah kebiasaan. Ternyata lebih cepat dan memerlukan lebih sedikit manipulasi. Jadi lain kali Anda ingin menyetel nomor kolom menjadi angka negatif di VLOOKUP, gunakan kombinasi dua fungsi aneh, INDEX dan MATCH, untuk menyelesaikan masalah Anda.