Bagaimana cara menggunakan fungsi pencarian di excel?

file sampel

1. Fungsi ALAMAT

Fungsi ADDRESS mengembalikan referensi sel yang valid berdasarkan alamat kolom dan baris. Secara sederhana, Anda dapat membuat alamat sel menggunakan nomor baris dan nomor kolomnya.

Sintaksis

ALAMAT(angka_baris,angka_kolom,angka_abs,A1,teks_lembar)

Argumen

• row_num: angka untuk menentukan nomor baris.
• Column_num: angka untuk menentukan nomor kolom.
• [abs_num]: Jenis referensi.
• [A1]: gaya referensi.
• [sheet_text]: Nilai teks sebagai nama sheet.

Komentar

• Secara default, fungsi ADDRESS mengembalikan referensi absolut pada hasilnya.

Contoh

Pada contoh di bawah, kami menggunakan argumen berbeda untuk mendapatkan semua jenis hasil.

Dengan gaya referensi R1C1 :

• Referensi relatif.
• Referensi baris relatif dan referensi kolom absolut.
• Referensi baris absolut dan kolom relatif.
• Referensi mutlak.

Dengan gaya referensi A1:

• Referensi relatif.
• Referensi baris relatif dan referensi kolom absolut.
• Referensi baris absolut dan kolom relatif.
• Referensi mutlak.

2. Fungsi ZONA

Fungsi AREAS mengembalikan angka yang mewakili jumlah rentang dalam referensi yang Anda tentukan. Secara sederhana, ini sebenarnya menghitung area lembar kerja berbeda yang Anda referensikan dalam fungsi tersebut.

sintaksis

BIDANG (referensi)

Argumen

• referensi: referensi ke sel atau rentang sel.

Komentar

• Referensi dapat berupa sel, rentang sel, atau rentang bernama.
• Jika Anda ingin merujuk ke beberapa referensi sel, Anda harus mengapit semua referensi tersebut dalam beberapa kumpulan tanda kurung dan menggunakan koma untuk memisahkan setiap referensi dari referensi lainnya.

Contoh

Pada contoh di bawah, kami menggunakan fungsi zona untuk mendapatkan referensi numerik dalam rentang bernama.

Seperti yang Anda lihat, ada tiga kolom dalam rentang tersebut dan menghasilkan 3 kolom.

3. PILIH fungsinya

Fungsi SELECT mengembalikan nilai dari daftar nilai berdasarkan nomor posisi yang ditentukan. Sederhananya, ia mencari nilai dalam daftar berdasarkan posisinya dan mengembalikannya ke hasil.

Sintaksis

PILIH(angka_indeks,nilai1,nilai2,…)

Argumen

• nomor_indeks: nomor yang digunakan untuk menentukan posisi nilai dalam daftar.
• nilai1: Rentang sel atau nilai masukan yang dapat Anda pilih.
• [nilai2]: Rentang sel atau nilai masukan yang dapat Anda pilih.

Komentar

• Anda bisa mereferensikan sel atau Anda juga bisa memasukkan nilai langsung ke dalam fungsinya.

Contoh

Pada contoh di bawah, kami menggunakan fungsi CHOOSE dengan daftar drop-down untuk menghitung empat hal berbeda (jumlah, rata-rata, maks, dan campuran). Jadi kami menggunakan rumus di bawah ini untuk menghitung empat hal:

=PILIH(VLOOKUP(K2,Q1:R4,2,FALSE),JUMLAH(O2:O9),RATA-RATA(O2:O9),MAX(O2:O9),MIN(O2:O9))

Kami memiliki tabel kecil ini dengan nama empat perhitungan yang kami inginkan dan nomor seri untuk masing-masing perhitungan di sel yang sesuai.

Setelah itu kami memiliki daftar drop-down untuk keempat perhitungan. Sekarang untuk mendapatkan nomor indeks dalam fungsi pilih dari tabel kecil ini, kita memiliki rumus pencarian yang akan mengembalikan nomor seri sesuai dengan nilai yang dipilih dari daftar drop-down.

Dan alih-alih nilai, kami menggunakan empat rumus untuk 4 penghitungan berbeda.

4. Fungsi KOLOM

Fungsi COLUMN mengembalikan nomor kolom untuk referensi sel tertentu. Seperti yang Anda ketahui, setiap referensi sel terdiri dari nomor kolom dan nomor baris. Jadi dibutuhkan nomor kolom dan mengembalikannya ke hasilnya.

Sintaksis

KOLOM([referensi])

Argumen

• referensi: referensi sel yang ingin Anda dapatkan nomor kolomnya.

Komentar

• Anda tidak dapat mereferensikan banyak referensi.
• Jika Anda mengacu pada array, fungsi kolom juga akan mengembalikan nomor kolom dalam array.
• Jika Anda mengacu pada rentang beberapa sel, ini akan mengembalikan nomor kolom dari sel paling kiri. Misalnya, jika Anda mengacu pada rentang A1:C10, ini akan mengembalikan nomor kolom sel A1.
• Jika Anda melewatkan penentuan referensi, ini akan mengembalikan nomor kolom sel saat ini.

Contoh

Pada contoh di bawah ini, kami menggunakan COLUMN untuk mendapatkan nomor kolom sel A1.

Seperti yang telah saya sebutkan, jika Anda mengabaikan referensi sel, nomor kolom sel saat ini akan dikembalikan. Pada contoh di bawah ini, kami menggunakan COLUMN untuk membuat header dengan nomor seri.

5. Fungsi KOLOM

Fungsi COLUMNS mengembalikan jumlah kolom yang direferensikan dalam referensi yang diberikan. Sederhananya, ini menghitung jumlah kolom dalam rentang yang disediakan dan mengembalikan nomor tersebut.

Sintaksis

KOLOM(tabel)

Argumen

• tabel: Tabel atau rentang sel yang ingin Anda peroleh jumlah kolomnya.

Komentar

• Anda juga dapat menggunakan rentang bernama.
• Fungsi COLUMNS tidak peduli dengan nilai dalam sel, ia hanya akan mengembalikan jumlah kolom dalam referensi.

Contoh

Pada contoh di bawah, kami menggunakan COLUMN untuk mendapatkan jumlah kolom dalam rentang A1:F1.

6. Fungsi FORMULATEKS

Fungsi FORMULATEXT mengembalikan rumus untuk sel yang direferensikan. Dan jika tidak ada rumus di sel yang direferensikan, nilai atau kosong, maka #N/A akan dikembalikan.

Sintaksis

FORMULA TEKS (referensi)

Argumen

• referensi: Referensi sel tempat Anda ingin mendapatkan rumus sebagai teks.

Komentar

• Jika Anda mereferensikan buku kerja lain, buku kerja tersebut harus terbuka, jika tidak maka rumus tidak akan ditampilkan.
• Jika Anda mengacu pada rentang lebih dari satu sel, rumus untuk sel kiri atas rentang tertentu akan dikembalikan.
• Ini akan mengembalikan nilai kesalahan “#N/A” jika sel yang Anda gunakan sebagai referensi tidak berisi rumus apa pun, memiliki rumus dengan lebih dari 8192 karakter, sel dilindungi, atau buku kerja eksternal tidak terbuka.
• Jika Anda mereferensikan dua sel dalam referensi melingkar, hasil dari keduanya akan dikembalikan.

Contoh

Pada contoh di bawah, kami telah menggunakan teks rumus dengan tipe referensi berbeda. Saat Anda merujuk ke sel yang tidak memiliki rumus, nilai kesalahan “#N/A” akan dikembalikan.

7. Fungsi HSEARCH

Fungsi HLOOKUP mencari nilai di baris atas tabel dan mengembalikan nilai di kolom yang sama dengan nilai yang cocok menggunakan nomor indeks. Sederhananya, ia mencari secara horizontal.

Sintaksis

HLOOKUP(nilai_pencarian, array_tabel, jumlah_indeks_baris, [pencarian_kisaran])

Argumen

• lookup_value: Nilai yang ingin Anda cari.
• table_array: Tabel atau array data yang ingin Anda cari nilainya.
• row_index_num: Nilai numerik yang mewakili jumlah baris ke bawah dari baris atas tempat Anda ingin mendapatkan nilainya. Misalnya, jika Anda menentukan 2 dan nilai pencarian Anda berada di A10 dalam tabel data, maka nilai di sel B10 akan dikembalikan.
• [range_lookup]: Nilai logis untuk menentukan jenis pencarian. Jika ingin melakukan pencarian pencocokan tepat gunakan FALSE dan jika ingin melakukan pencarian pencocokan tidak tepat gunakan TRUE (default).

Komentar

• Anda dapat menggunakan wildcard.
• Anda dapat melakukan pencocokan tepat dan pencocokan fuzzy.
• Saat melakukan pencocokan fuzzy, pastikan untuk mengurutkan data dalam urutan menaik dari kiri ke kanan, dan jika data tidak dalam urutan menaik, maka akan memberikan hasil yang tidak akurat.
• Jika range_lookup benar atau dihilangkan, ia akan melakukan pencocokan tidak persis namun mengembalikan pencocokan tepat jika nilai pencarian ada dalam rentang pencarian.
• Jika range_lookup benar atau dihilangkan, dan nilai pencarian tidak berada dalam rentang pencarian, nilai terdekat akan dikembalikan yang lebih kecil dari nilai pencarian.
• Jika range_lookup salah, rentang data tidak perlu diurutkan.

Contoh

Pada contoh di bawah, kita menggunakan fungsi HLOOKUP dengan MATCH untuk membuat rumus dinamis, lalu menggunakan daftar drop-down untuk mengubah nilai pencarian sel.

Nama field dari sel C7 digunakan sebagai nilai pencarian. Rentang B1: F5 sebagai array tabel dan untuk row_index_num kami menggunakan fungsi match untuk mendapatkan nomor baris.

Setiap kali Anda mengubah nilai di sel C9, itu akan mengembalikan nomor baris tabel. Anda tidak perlu mengubah formula berulang kali. Ubah saja nilainya dengan dropdown dan Anda akan mendapatkan nilainya.

8. Fungsi HYPERLINK

Fungsi HYPERLINK mengembalikan string dengan hyperlink yang dilampirkan padanya. Dengan kata sederhana, seperti opsi HYPERLINK yang Anda miliki di Excel, fungsi HYPERLINK membantu Anda membuat hyperlink.

Sintaksis

HYPERLINK(link_lokasi,[nama_ramah])

Argumen

• Link_Location: Lokasi yang ingin Anda tambahkan HYPERLINK. Hal ini dapat dibagi lagi menjadi dua istilah.
  1. tautan: Ini bisa berupa alamat sel atau rentang sel di lembar kerja yang sama atau di lembar kerja atau buku kerja lainnya. Kami juga dapat menautkan bookmark dari dokumen Word.
  2. lokasi: Ini bisa berupa tautan ke hard drive, server yang menggunakan jalur UNC, atau URL apa pun dari Internet atau intranet. (Di Excel online, Anda hanya bisa menggunakan alamat web untuk fungsi HYPERLINK). Anda dapat menyisipkan link ke fungsi dengan menyisipkannya sebagai teks dengan tanda kutip atau dengan mereferensikan sel yang berisi link sebagai teks. Pastikan untuk menggunakan “HTTPS://” sebelum alamat web.
• [nama_ramah]: Ini adalah bagian opsional dari fitur ini. Ini bertindak sebagai wajah tautan koneksi.
  1. Anda dapat menggunakan jenis teks apa pun, angka, atau keduanya.
  2. Anda juga merujuk ke sel yang berisi nama_ramah.
  3. Jika Anda mengabaikannya, fungsi tersebut akan menggunakan alamat tautan untuk ditampilkan.
  4. Jika nama_ramah mengembalikan kesalahan, fungsi akan menampilkan kesalahan.

Komentar

• Tautkan file yang disimpan ke alamat web: Anda dapat menggunakan file yang disimpan ke alamat web. Ini membantu kami berbagi file secara efisien.
• Tautkan file yang disimpan di hard drive: Anda juga dapat menggunakan fungsi ini saat bekerja offline. Anda dapat menautkan file yang disimpan di hard drive Anda dan mengaksesnya melalui satu lembar Excel, tidak perlu membuka setiap folder untuk membukanya.
• Tautan File Dokumen Word: Ini juga merupakan fitur luar biasa dari fitur HYPERLINK. Anda dapat menautkan file dokumen Word atau lokasi tertentu dalam file dokumen Word menggunakan penanda.
• Tautkan file tanpa menggunakan nama yang mudah diingat: Jika Anda ingin menampilkan tautan sebenarnya ke file atau lokasi kepada pengguna. Dalam situasi ini, Anda hanya perlu mengabaikan deklarasi nama ramah di fungsi HYPERLINK.

9. Fungsi TIDAK LANGSUNG

Fungsi INDIRECT mengembalikan referensi valid dari string teks yang mewakili referensi sel. Secara sederhana, Anda bisa merujuk ke rentang sel menggunakan alamat sel sebagai nilai teks.

Sintaksis

TIDAK LANGSUNG(ref_text, [a1])

Argumen

• ref_text: teks yang mewakili alamat sel, alamat rentang sel, rentang bernama, atau nama tabel. Misalnya, A1, B10:B20 atau MyRange.
• [a1]: Angka atau nilai Boolean untuk mewakili tipe referensi sel yang Anda tentukan di ref_text. Misalnya, jika Anda ingin menggunakan gaya referensi A1, gunakan TRUE atau 1, dan jika Anda ingin menggunakan gaya referensi R1C1, gunakan FALSE atau 0 untuk gaya referensi R1C. Dan jika Anda gagal menentukan tipe referensi sel, maka gaya A1 akan digunakan secara default.

Komentar

• Ketika Anda telah mereferensikan buku kerja lain, buku kerja itu harus dibuka.
• Jika Anda menyisipkan baris atau kolom dalam rentang yang Anda referensikan, INDIRECT tidak akan memperbarui referensi tersebut.
• Jika Anda ingin menyisipkan teks langsung ke dalam fungsi, Anda harus mengapitnya dalam tanda kutip ganda atau Anda juga bisa mereferensikan sel berisi teks yang ingin Anda gunakan sebagai referensi.

Contoh

1. Referensi ke lembar kerja lain

Anda juga bisa merujuk ke lembar kerja lain menggunakan INDIRECT dan Anda perlu memasukkan nama lembar kerja di sana. Pada contoh di bawah ini, kami menggunakan fungsi tidak langsung untuk mereferensikan lembar kerja lain dan memiliki nama lembar di sel A2 dan referensi sel di sel B2.

Di sel C2, kami menggunakan rumus berikut untuk menggabungkan teks.

=TIDAK LANGSUNG(“’”&A2&”’!”&B2)

Kombinasi ini menghasilkan teks yang digunakan oleh fungsi INDIRECT untuk merujuk ke sel A1 di sheet1 dan bagian terbaiknya adalah ketika Anda mengubah nama lembar kerja atau alamat sel, referensi akan otomatis berubah.

Sel A1 di “Sheet1” memiliki nilai “Ya” dan itulah mengapa tidak langsung mengembalikan nilai “Ya”.

2. Referensi ke buku kerja lain

Anda juga bisa merujuk ke buku kerja lain, sama seperti yang kami lakukan untuk lembar kerja lain. Yang perlu Anda lakukan hanyalah menambahkan nama buku kerja di teks yang Anda gunakan sebagai referensi.

Pada contoh di atas, kami menggunakan rumus berikut untuk mendapatkan nilai sel A1 buku kerja “Buku1”.

=TIDAK LANGSUNG(“[“&A2&”]”&B2&”!”&C2)

Karena kita memiliki nama buku kerja di sel “A2”, nama lembar kerja di sel “B2” dan nama sel di sel “C2”. Kami menggabungkannya untuk digunakan sebagai teks masukan dalam fungsi tidak langsung.

Catatan: Saat menggabungkan referensi sel sebagai teks, pastikan Anda mengikuti struktur referensi yang benar.

3. Menggunakan Rentang Bernama

Ya, Anda juga bisa merujuk ke rentang bernama menggunakan fungsi tidak langsung. Itu mudah. Setelah Anda membuat rentang bernama, Anda harus memasukkan rentang bernama tersebut sebagai teks dalam INDIRECT.

Pada contoh di atas, kita memiliki daftar drop-down di sel E1 yang berisi daftar rentang bernama, dan di sel E2 kita telah menggunakan nama tersebut. Karena rentang B2:B5 diberi nama “Kuantitas” dan rentang C2:C5 diberi nama “Jumlah”.

Saat Anda memilih kuantitas dari daftar drop-down, fungsi tidak langsung langsung mereferensikan rentang bernama. Dan ketika kamu memilih jumlah dari menu drop-down, kamu akan mendapatkan jumlah dari rentang sel C2:C5.

11. Fungsi PENCARIAN

Fungsi SEARCH mengembalikan nilai (yang Anda cari) dari baris, kolom, atau tabel . Sederhananya, Anda dapat mencari suatu nilai dan SEARCH akan mengembalikan nilai tersebut jika berada di baris, kolom, atau tabel tersebut.

Sintaksis

CARI(nilai, rentang_pencarian, [rentang_hasil])

Ada dua jenis fungsi SEARCH.

• bentuk vektor
• Bentuk tabel

Argumen

• value: nilai yang ingin Anda cari dalam kolom atau baris.
• lookup_range: Kolom atau baris yang ingin Anda cari nilainya.
• [result_range]: Kolom atau baris yang ingin Anda kembalikan nilainya. Ini adalah argumen opsional.

Komentar

• Daripada menggunakan bentuk array, lebih baik menggunakan VLOOKUP atau HLOOKUP.

12. Fungsi PERTANDINGAN

Fungsi MATCH mengembalikan nomor indeks nilai array. Secara sederhana, fungsi MATCH mencari nilai dalam daftar dan mengembalikan nomor posisi nilai tersebut dalam daftar.

Sintaksis

COCOK (nilai_pencarian, array_pencarian, [tipe_pencocokan])

Argumen

• lookup_value : Nilai yang posisinya ingin Anda dapatkan dari daftar nilai.
• lookup_array : Rentang sel atau array berisi nilai.
• [match_type] : Angka (-1, 0, dan 1) untuk menentukan cara Excel mencari nilai dalam daftar nilai.
  1. Jika Anda menggunakan 1, ini akan mengembalikan nilai terbesar yang sama dengan atau kurang dari nilai pencarian. Nilai-nilai dalam daftar harus diurutkan dalam urutan menaik.
  2. Jika Anda menggunakan -1, ini akan mengembalikan nilai terkecil yang sama atau lebih besar dari nilai pencarian. Nilai-nilai dalam daftar harus diurutkan dalam urutan menaik.
  3. Jika Anda menggunakan 0 itu akan mengembalikan daftar yang sama persis.

Komentar

• Anda dapat menggunakan wildcard .
• Jika tidak ada nilai yang cocok dalam daftar, if akan mengembalikan #N/A.
• Fungsi pencocokan tidak peka huruf besar-kecil.

Contoh

Pada contoh di bawah ini, kami menggunakan 1 sebagai jenis pencocokan dan kami mencari nilai 5.

Seperti yang telah saya sebutkan, jika Anda menggunakan 1 pada jenis pencocokan, ini akan mengembalikan nilai terbesar yang sama dengan atau kurang dari nilai pencarian. Di seluruh daftar ada 3 nilai kurang dari 5 dan 4 adalah yang tertinggi.

Makanya pada hasilnya dikembalikan 3 yang posisinya bernilai 4.

13. Fungsi peralihan

Fungsi OFFSET mengembalikan referensi ke rentang yang merupakan jumlah baris dan kolom tertentu dari sel atau rentang sel. Secara sederhana, Anda bisa merujuk ke sel atau rentang sel menggunakan baris dan kolom yang dimulai dari sel awal.

Sintaksis

OFFSET(referensi, baris, kolom, [tinggi], [lebar])

Argumen

• reference : Referensi yang ingin Anda offset untuk memulai. Ini bisa berupa sel atau serangkaian sel yang berdekatan.
• baris : Jumlah baris yang memberitahu OFFSET untuk bergerak ke atas atau ke bawah dari referensi. Untuk turun memerlukan bilangan positif dan untuk naik memerlukan bilangan negatif.
• cols : Jumlah kolom memberitahu OFFSET untuk berpindah ke kiri atau kanan dari referensi. Untuk ke kanan memerlukan bilangan positif dan untuk ke kiri memerlukan bilangan negatif.
• [height] : nomor untuk menentukan baris mana yang akan disertakan dalam referensi.
• [lebar]: Nomor untuk menentukan kolom mana yang akan disertakan dalam referensi.

Komentar

• OFFSET adalah fungsi “volatile”, yang menghitung ulang setiap kali ada perubahan dalam spreadsheet.
• Ini menampilkan #REF! nilai kesalahan jika offset berada di luar tepi lembar kerja.
• Jika tinggi atau lebar dihilangkan, tinggi dan lebar referensi digunakan.

Contoh

Pada contoh di bawah, kami menggunakan SUM dengan OFFSET untuk membuat rentang dinamis yang menjumlahkan nilai semua bulan untuk produk tertentu.

14. Fungsi GARIS

Fungsi ROW mengembalikan nomor baris sel yang direferensikan. Sederhananya, dengan fungsi ROW Anda bisa mendapatkan nomor baris sel dan jika Anda tidak merujuk ke sel mana pun, fungsi ini akan mengembalikan nomor baris sel tempat Anda menyisipkannya.

Sintaksis

GARIS([referensi])

Argumen

• referensi: Referensi sel atau rentang sel yang nomor barisnya ingin Anda periksa.

Komentar

• Ini akan mencakup semua jenis lembar (lembar bagan, lembar kerja, atau lembar makro).
• Anda dapat merujuk ke folio meskipun terlihat, tersembunyi, atau sangat tersembunyi.
• Jika Anda tidak menentukan nilai apa pun dalam fungsi tersebut, ini akan memberi Anda nomor lembar lembar tempat Anda menerapkan fungsi tersebut.
• Jika Anda menentukan nama sheet yang tidak valid, maka #N/A akan dikembalikan.
• Jika Anda menentukan referensi sheet yang tidak valid, maka #REF! akan dikembalikan.

Contoh

Pada contoh di bawah ini, kita telah menggunakan fungsi baris untuk memeriksa nomor baris dari sel yang sama tempat kita menggunakan fungsi tersebut.

Pada contoh di bawah, kami telah mereferensikan sel lain untuk mendapatkan nomor baris sel tersebut.

Anda dapat menggunakan fungsi baris untuk membuat daftar nomor seri di spreadsheet Anda. Yang perlu Anda lakukan hanyalah memasukkan fungsi baris ke dalam sel dan menyeretnya ke sel tempat Anda ingin menambahkan nomor seri.

15. Fungsi GARIS

Fungsi ROWS mengembalikan jumlah baris dalam rentang yang direferensikan. Sederhananya, dengan fungsi ROWS, Anda dapat menghitung jumlah baris dalam rentang yang Anda referensikan.

Sintaksis

GARIS (tabel)

Argumen

• array: Referensi sel atau array untuk memeriksa jumlah baris.

Komentar

• Anda juga dapat menggunakan rentang bernama.
• Ini tidak berkaitan dengan nilai dalam sel, ini hanya akan mengembalikan jumlah baris dalam referensi.

Contoh

Pada contoh di bawah, kami mereferensikan rentang vertikal 10 sel dan hasilnya menghasilkan 10 karena rentang tersebut memiliki 10 baris.

16. Fungsi TRANSPOSE

Fungsi TRANSPOSE mengubah orientasi rentang. Sederhananya, dengan menggunakan fungsi ini Anda dapat mengubah data dari baris ke kolom dan dari kolom ke baris.

Sintaksis

TRANSPOSE (tabel)

Argumen

• array: Array atau rentang yang ingin Anda ubah urutannya.

Komentar

• Anda perlu menerapkan TRANSPOSE sebagai fungsi array, menggunakan jumlah sel yang sama dengan yang Anda miliki dalam rentang sumber dengan menekan Ctrl+Shift+Enter.
• Jika Anda memilih sel yang lebih kecil dari rentang sumber, data hanya akan dialihkan untuk sel tersebut.

Contoh

Di sini kita perlu mengubah urutan data dari rentang B2:D4 ke rentang G2 hingga I4:

Untuk melakukan ini, pertama-tama kita perlu pergi ke sel G2 dan memilih rentang sel hingga I4.

Selanjutnya, masukkan (=TRANSPOSE(B2:D4)) di sel G2 dan tekan Ctrl+Shift+Enter.

TRANSPOSE akan mengkonversi data dari baris ke kolom, dan rumus yang kita terapkan adalah rumus array, Anda tidak dapat mengubah satu sel pun di dalamnya.