Jika Anda bekerja dengan kumpulan data yang disimpan di buku kerja atau lembar kerja yang berbeda, seringkali pusing akan menemukan cara menggabungkan informasi dari beberapa lembar berdasarkan kolom kunci yang ditentukan Show
Anda mungkin sering menjumpai situasi di mana beberapa bagian dari kumpulan data disimpan di lembar dan beberapa bagian data terkait lainnya ada di lembar lain Anda mungkin ingin menggabungkan data di beberapa lembar tersebut bersama-sama dalam satu lembar untuk melakukan analisis lebih lanjut Menggabungkan data yang tersebar di beberapa sheet menjadi satu sheet biasanya berarti Anda perlu mencari data berdasarkan kolom umum untuk mencocokkannya dengan data terkait Ini dikenal sebagai pencarian data. Anda dapat mencari data di beberapa tabel, sheet, atau spreadsheet. Anda bahkan dapat mencari gambar di dalam sel di Google Sheets Misalnya, Anda memiliki nama pelanggan dan ID email mereka yang disimpan dalam satu lembar. Lembar lain berisi id email dan jumlah yang mereka pesan dari produk tertentu dari organisasi Anda Anda ingin menggabungkan informasi ini ke dalam satu tabel yang berisi nama pelanggan, id email, dan jumlah yang mereka pesan Untuk melakukan tugas ini di dalam Google Sheets, Anda perlu mencari data dari sheet lain berdasarkan kolom tertentu yang umum di kedua tabel (ID email) Sepanjang artikel ini, Anda akan mempelajari tentang tujuh cara berbeda untuk mencari data di dalam Google Sheets. Unduh contoh buku kerja untuk diikuti DatasetContoh di seluruh posting ini menggunakan dua tabel data yang sama
Setiap tabel data terdiri dari sepuluh pelanggan, dan tangkapan layar di atas akan memberi Anda gambaran yang lebih baik tentang strukturnya Cari Data Menggunakan Fungsi VLOOKUP di Google SheetsFungsi VLOOKUP sejauh ini merupakan fungsi paling populer yang tersedia di Google Sheets Fungsi VLOOKUP digunakan untuk mencari data dari satu tabel ke tabel lainnya. Fungsi ini mendapatkan namanya dari singkatan Vertical Lookup, dan seperti namanya, fungsi ini mencari kecocokan secara vertikal dari atas tabel ke bawah. Fungsi VLOOKUP merupakan bagian integral dari Google Sheets. Fungsi mencari kunci atau nilai unik dari kolom pertama rentang tertentu dan mengembalikan nilai yang terletak di kolom lain untuk baris yang sama Anda mungkin menemukan fungsi ini sulit untuk ditangani pada awalnya. Namun, begitu Anda melewatinya, Anda akan setuju bahwa itu tidak terlalu sulit untuk dipahami Sintaks untuk Fungsi VLOOKUP
Perhatikan bahwa indeks untuk kolom dimulai dari 1. Jika Anda menentukan nilai indeks kurang dari 1, rumus akan memberikan #VALUE. Kesalahan Di sisi lain, jika Anda menentukan nilai indeks yang berada di luar nomor kolom dari rentang yang diberikan, Anda akan mendapatkan #REF. Kesalahan
ContohMeskipun argumen-argumen ini terlihat agak membingungkan, cara kerja rumus sebenarnya cukup sederhana. Mari kita lihat bagaimana Anda dapat menggunakan fungsi VLOOKUP untuk mencari nilai dari lembar yang berbeda Masalah yang Anda hadapi saat ini adalah Anda memiliki dua database, seperti yang dibahas di atas. Database 1 memiliki id email pelanggan dan nama mereka, sedangkan Database 2 memiliki id email pelanggan dan jumlah pesanan Anda sekarang ingin menarik jumlah pesanan di Database 1 berdasarkan id email pelanggan sehingga seluruh data berada di satu tempat untuk analisis lebih lanjut. Anda dapat menggunakan fungsi VLOOKUP untuk menyelesaikan tugas ini _Rumus di atas akan mencari email di tabel 2 dan mengembalikan jumlah pesanan yang sesuai
Perhatikan bahwa menyimpan atau menghapus sel header di dalam rumus VLOOKUP tidak mengubah atau mengubah keluaran
Rumus yang melakukan tugas ini adalah seperti yang ditunjukkan di atas, dan Anda dapat langsung menyalin dan menempelkannya di spreadsheet asalkan Anda memiliki struktur data yang sama seperti yang saya miliki di demo spreadsheet Anda perlu menyeret rumus dari C2 melintasi baris untuk mendapatkan Jumlah Pesanan untuk setiap pelanggan. Anda dapat melakukannya dengan mengarahkan kursor mouse ke kotak biru di sel C2 dan klik dua kali untuk memperluas rumus ke bawah baris Anda juga dapat memilih rentang C2. C11 lalu tekan Ctrl + D sebagai jalan pintas untuk mengisi rumus di bawah sel. Atau Anda cukup menyalin dan menempelkannya di baris Setelah Anda menyeret rumus ke bawah, akan ada beberapa nilai kesalahan #N/A. Ini adalah kesalahan karena rumus tidak dapat menemukan email tersebut di dalam lembar Database 2. Dan memang demikian, pelanggan tersebut sama sekali tidak ada dalam daftar kedua Selain itu, untuk pelanggan dengan pesanan rangkap, rumus hanya mengembalikan nilai pertama yang ditemukan Catatan
Cari Data Menggunakan Fungsi HLOOKUP di Google SheetsFungsi HLOOKUP, di sisi lain, tidak sepopuler dan banyak digunakan seperti VLOOKUP. Namun, kedua fungsi tersebut bekerja hampir identik HLOOKUP dapat digunakan sebagai pengganti VLOOKUP saat menangani kumpulan data yang dialihkan H di dalam nama fungsi adalah singkatan dari Horizontal. Fungsi ini mencari nilai secara horizontal dari kiri ke kanan berturut-turut. Argumen untuk fungsi ini juga sama dengan fungsi VLOOKUP dengan sedikit perubahan Fungsi ini dirancang secara eksplisit untuk mencari nilai secara horizontal. Fungsi mencari kunci atau nilai unik dari baris pertama rentang atau tabel tertentu dan mengembalikan nilai yang terletak di baris lain untuk kolom yang sama Sintaks untuk Fungsi HLOOKUP _
ContohMisalkan di dalam sheet Database 2 Anda memiliki data horizontal yang disimpan seperti yang ditunjukkan di atas. Anda ingin mencari dan menarik Jumlah Pesanan berdasarkan Email di dalam lembar Database 1 Ikuti langkah-langkah di bawah ini untuk mencari nilai menggunakan fungsi HLOOKUP di dalam Google Sheets
Untuk argumen kunci_penelusuran, masukkan sel A20 sebagai nilai yang ingin Anda cari hasilnya Rentang berasal dari sheet Database 2, dan merupakan tabel yang disimpan secara horizontal. Nilai untuk argumen ini harus B27. K28 Kisaran ini tidak akan berubah, dan karenanya bagus untuk memperbaikinya. Gunakan tombol keyboard F4 untuk memperbaiki rentang ini. Rumus akan mencari nilai kunci di baris pertama setiap kolom untuk mencari data Karena Anda ingin menarik Jumlah Pesanan, yang disimpan di baris kedua setiap kolom, gunakan nilai 2 untuk argumen indeks Terakhir, Anda ingin pencocokan tepat ditemukan dan karenanya menambahkan FALSE untuk argumen is_sorted Rumus yang melakukan tugas ini adalah seperti yang ditunjukkan di atas. Jangan ragu untuk menyalin dan menggunakannya saat Anda bekerja dengan spreadsheet demo Anda harus menyeret rumus ke bawah melintasi sel C20. C29 untuk menerapkan rumus di semua sel, dan Anda mendapatkan semua Jumlah Pesanan berdasarkan Email Sekali lagi, kesalahan #N/A adalah untuk pelanggan yang jumlah pesanannya tidak ada dalam daftar kedua Jika terjadi pesanan rangkap, kecocokan pertama yang ditemukan diambil dengan rumus HLOOKUP. Sama seperti fungsi VLOOKUP Catatan
Mencari Data Menggunakan Fungsi INDEX MATCH di Google SheetsUntuk mencari data di dalam Google Sheets, berbagai fungsi dapat digunakan. Tapi tidak ada yang mendekati efisiensi fungsi INDEX dan MATCH Kedua fungsi ini bila digunakan secara terpisah, tidak menghasilkan terlalu banyak hasil yang berharga. Namun, jika digunakan bersama, mereka dapat melakukan keajaiban dan menghasilkan hasil pencarian yang canggih bahkan lebih canggih daripada fungsi VLOOKUP konvensional Google Sheets INDEX MATCH merupakan kombinasi dua fungsi yang, jika digunakan dalam urutan tertentu, dapat menghasilkan keluaran yang serupa dengan yang dihasilkan oleh fungsi VLOOKUP atau HLOOKUP Anda dapat menggunakannya sebagai alternatif dari kedua fungsi ini dengan lebih banyak kemungkinan untuk mencari data karena fungsi ini bekerja lebih baik daripada VLOOKUP atau HLOOKUP Sintaks untuk Fungsi MATCHFungsi MATCH cukup sederhana untuk dipahami Fungsi Google Sheet MATCH mencari kunci atau nilai unik di seluruh rentang atau larik sel, lalu mengembalikan posisi atau indeks relatif nilai tersebut. Ini memiliki sintaks seperti yang ditunjukkan di bawah ini
PERTANDINGAN Contoh
Berikut adalah contoh di mana Anda ingin menemukan posisi relatif ID email untuk pelanggan Rajan Arora di dalam daftar Anda. Rumus mencari indeks baris yang berisi ID email adalah seperti di atas
Karena tampilan pertama ID email ada di baris keenam, rumus akan mengembalikan 6 sebagai output 📝 Catatan. ID email ini digandakan dan disimpan di baris keenam dan kedelapan. Fungsi menemukan tampilan pertama pada baris keenam dan mengembalikan posisi ini. Itu tidak memeriksa baris berikutnya setelah kecocokan pertama ditemukan Sintaks untuk Fungsi INDEXBerbeda dengan fungsi MATCH, fungsi INDEX mengembalikan nilai daripada referensi posisi
Contoh INDEKSMisalkan Anda menginginkan nilai Jumlah Pesanan yang terkait dengan Rajan Arora dari daftar kedua. Anda bisa mendapatkannya menggunakan fungsi INDEX sebagai berikut
Anda menyelesaikan rumus dengan menambahkan tanda kurung tutup dan Bingo. Fungsi mengembalikan 18 sebagai nilai dari referensi sel Fungsi INDEX dan MATCH Untuk Mencari NilaiKarena Anda tahu bagaimana fungsi INDEX dan MATCH bekerja secara individual, Anda dapat menggunakannya bersama-sama untuk mencari Jumlah Pesanan dari Database 2 untuk pelanggan dari Database 1 berdasarkan Email mereka Pertama, Anda akan menggunakan fungsi MATCH untuk menemukan posisi relatif ID email dari Database 1 ke Database 2. Rumus untuk melakukan tugas ini adalah seperti yang ditunjukkan di bawah ini
Fungsi ini akan mengembalikan posisi relatif Email yang disimpan di sel A35 di dalam lembar Database 2. Itu ada di baris ke-4 di sana
Sekarang, Anda akan menggunakan rumus MATCH ini di dalam fungsi INDEX sebagai argumen baris
Selamat. Anda telah berhasil mencari Jumlah Pesanan untuk setiap Pelanggan berdasarkan Email mereka menggunakan kombinasi kuat dari fungsi INDEX dan MATCH Sekarang seret rumus ini ke bawah melintasi baris C35. C44 untuk mengembalikan nilai untuk setiap Pelanggan dalam daftar Anda akan melihat nilai #N/A di beberapa sel. Jangan khawatir. Kesalahan tersebut adalah karena sistem tidak dapat menemukan Email tersebut dan Jumlah Pesanan relatifnya di dalam Database 2 Catatan
Cari Data Menggunakan Fungsi DGET di Google SheetsFungsi kuat lainnya untuk mencari data di dalam Google Sheets adalah DGET Fungsi ini milik keluarga Database dan merupakan satu-satunya fungsi pencarian dari keluarga tersebut. Semua fungsi lain yang berasal dari keluarga Database adalah fungsi agregat Fungsi ini adalah tambahan yang berharga dan pengganti yang baik untuk fungsi VLOOKUP saat mencari hasil Fungsi DGET membantu Anda mengembalikan satu nilai berdasarkan kriteria dengan cara seperti SQL. Perhatikan bahwa fungsi ini dapat mencari nilai dari beberapa kolom tetapi tidak dapat mengembalikan dari beberapa baris Sintaks untuk Fungsi DGET
ContohSekarang, pertimbangkan situasi ini. Anda ingin mencari Jumlah Pesanan untuk pelanggan dengan id email “emily@rbc. com” di dalam sel B65 menggunakan fungsi DGET _0Fungsi di atas akan mengambil nilai yang diperlukan dari daftar lain menggunakan fungsi DGET di dalam Google Spreadsheet
Anda dapat menyalin rumus di bawah ini sambil mengerjakan lembar demo yang Anda unduh. Ubah saja nilai kriteria untuk mendapatkan Jumlah Pesanan untuk pelanggan lain Catatan
Cari Data Menggunakan Fungsi LOOKUP di Google SheetsFungsi LOOKUP Google Sheets agak membingungkan bagi sebagian pengguna karena perbedaan sintaks dan cara melakukan pencarian baik secara vertikal maupun horizontal Selain itu, sintaks fungsi yang membingungkan menyebabkan beberapa keraguan. Namun, jika Anda menguasai fungsi ini, ini adalah alternatif praktis untuk VLOOKUP dan HLOOKUP Fungsi LOOKUP Google Sheets mencari nilai unik atau kritis di dalam rentang pencarian tertentu, baik secara vertikal menurut baris atau horizontal menurut kolom Ini kemudian mengembalikan nilai sel berdasarkan posisi yang sesuai dari nilai unik tersebut dari rentang hasil Sintaks untuk Fungsi LOOKUP _1
Kelemahan terbesar dengan fungsi LOOKUP adalah, ia tidak dapat menghasilkan kecocokan yang tepat Fungsi ini selalu mengembalikan perkiraan kecocokan. Jika 7 tidak ada di 1. 2, fungsi ini akan menemukan kemungkinan kecocokan terdekat dan kemudian mengembalikan nilai pencarian yang terkait dengan perkiraan kecocokan tersebutJika ada nilai duplikat, fungsi ini selalu mengembalikan hasil berdasarkan kecocokan terakhir yang ditemukan. Semua ini karena ini berfungsi pada rentang yang diurutkan (urutan menaik akan memberikan hasil terbaik) Contoh _2Anda dapat melihat bagaimana fungsi ini mengembalikan Hitungan Pesanan pada contoh di atas
Catatan
Cari Data Menggunakan Fungsi FILTER di Google SheetsCara lain untuk mencari data untuk satu nilai kunci adalah menggunakan fungsi FILTER Fungsi FILTER pertama-tama mengambil seluruh data dari tempat Anda ingin mengekstrak hasilnya. Kemudian, itu akan memfilter hasil berdasarkan kondisi yang disediakan Anda dapat menggunakan ini sebagai pencarian Jumlah Pesanan dengan memfilter email yang diberikan Sintaks untuk Fungsi FILTER _3
Contoh _4
Rumus akan mengembalikan Jumlah Pesanan sebagai 35 untuk “emily@rbc. com” CatatanMenariknya, ketika Anda memiliki nilai duplikat, fungsi tersebut akan mengembalikan semua hasil Misalnya, jika Anda ingin mencari Jumlah Pesanan untuk rajan@pzk. com, rumus akan mengembalikan dua nilai masing-masing 18, dan 30. Anda hanya perlu mengubah ID email di sel A18 Cari Data Menggunakan Fungsi QUERY di Google SheetsFungsi QUERY Google Sheets adalah fungsi dari Database Family seperti DGET dan memungkinkan Anda menulis kode SQL-Like untuk mengubah data Anda Fungsi ini juga dapat digunakan untuk mencari data. Anda dapat menggunakan fungsi QUERY untuk memfilter data Anda pada satu nilai yang serupa dengan cara kerja fungsi FILTER Sayangnya, Anda tidak dapat menggunakan fungsi QUERY untuk mencari nilai di berbagai sheet karena keterbatasan struktur kueri Fungsi ini dapat melakukan lebih dari sekadar memfilter data, jadi lihat panduan lengkap untuk fungsi QUERY untuk mempelajari semua yang dapat dilakukannya Sintaks untuk Fungsi QUERY _5
Contoh _6Misalkan Anda ingin mencari Jumlah Pesanan untuk Email rozy@rbc. com menggunakan fungsi QUERY di dalam sel B24. Referensi atau nilai kritis disimpan di sel A24
Catatan📝 Catatan. Mirip dengan fungsi FILTER, jika Anda mencari nilai duplikat, fungsi ini akan mengembalikan semua kecocokan yang tersedia KesimpulanMencari data dari tabel lain adalah tugas yang sering dilakukan. Artikel ini menunjukkan kepada Anda tujuh fungsi berbeda yang dapat melakukannya untuk Anda
Fungsi apa yang Anda gunakan untuk mencari data? Bagaimana cara mencari di dalam kolom di Google Sheets?Gunakan temukan dan ganti di spreadsheet . Di komputer Anda, buka spreadsheet di Google Spreadsheet Klik Sunting. . Di samping "Temukan", ketikkan kata yang ingin Anda temukan, Jika Anda ingin mengganti kata, masukkan kata baru di samping "Ganti dengan. " Untuk mencari kata, klik Temukan. . Opsional. Persempit pencarian Anda dengan menggunakan opsi di bawah ini Bagaimana cara mencari kolom dalam string di Google Sheets?Anda dapat dengan mudah menemukan string teks di spreadsheet menggunakan alat Temukan dan Ganti. Arahkan ke menu paling atas dan klik Edit > Temukan dan Ganti. Di prompt Temukan, masukkan string teks yang ingin Anda cari. Kemudian klik tombol Temukan untuk menemukan sel yang berisi string teks
Bagaimana cara memilih kolom tertentu dalam kueri Google Sheets?Pilih (semua atau kolom tertentu)
.
Bagaimana cara memfilter kolom tertentu di Google Sheets?Filter data Anda . Di komputer Anda, buka spreadsheet di Google Spreadsheet Pilih rentang sel Klik Data. Buat filter Untuk melihat opsi filter, buka bagian atas rentang dan klik Filter. Filter berdasarkan kondisi. Pilih kondisi atau tulis sendiri. . Untuk menonaktifkan filter, klik Data. Hapus filter |