Panduan lengkap untuk menggunakan fungsi carian dan rujukan dalam Excel

Jika anda pernah bekerja dengan Excel, anda mungkin menghadapi keperluan untuk mencari atau merujuk data dengan cekap dan cepat. Walaupun ramai orang mengehadkan diri mereka untuk menggunakan fungsi asas, fungsi carian dan rujukan Dalam Excel, formula ialah alat berkuasa yang boleh mengoptimumkan cara anda bekerja dan menganalisis maklumat secara luar biasa. Pemahaman yang baik tentang cara formula ini berfungsi akan membolehkan anda menjimatkan masa, mengelakkan ralat dan membuat keputusan yang lebih termaklum.

Dalam artikel ini, kita akan meneroka dalam semua fungsi carian dan rujukan utama yang wujud dalam Excel, mengetahui cara ia digunakan, ciri khusus mereka dan helah praktikal untuk membantu anda memanfaatkannya sepenuhnya. Selain menyemak contoh dan sintaks, kami akan menjelaskan tujuan setiap fungsi dan memberi anda petua tentang cara menyepadukannya ke dalam hamparan anda sendiri, sama ada anda seorang pemula atau sudah mempunyai pengalaman tetapi ingin menambah baik.

Apakah fungsi carian dan rujukan dalam Excel?

Dalam alam semesta Excel berfungsi, kumpulan carian dan rujukan Ia menonjol kerana membenarkan kami mencari maklumat khusus dalam julat, jadual atau set matriks, mengembalikan keputusan berdasarkan padanan atau kedudukan relatif. Fungsi ini direka bentuk untuk mengautomasikan pengambilan data berkaitan, memudahkan analisis yang kompleks, maklumat rujukan silang antara jadual, penjanaan laporan dinamik dan banyak lagi.

artikel berkaitan:

Cara Menguasai Analisis Data dengan Alat Excel Lanjutan

Fungsi carian dan rujukan utama dalam Excel

Excel termasuk pelbagai fungsi jenis ini, masing-masing dengan penggunaan khususnya sendiri. Ada yang dikenali secara meluas, seperti LIHAT o HLOOKUP, manakala yang lain seperti SEARCHX o INDEKS telah semakin penting dengan versi terkini. Berikut ialah penerangan tentang ciri yang paling penting:

• LIHAT: Mencari nilai dalam lajur pertama tatasusunan dan mengembalikan nilai daripada baris yang sama dalam lajur lain yang anda tentukan.
• HLOOKUP: Sama seperti VLOOKUP, tetapi melakukan carian dalam baris pertama tatasusunan, mengembalikan nilai dari baris lain.
• CARIAN: Membolehkan anda mencari dalam satu baris atau lajur dan mencari nilai dalam kedudukan yang sama dalam baris atau lajur yang lain.
• SEARCHX: Versi yang lebih baik daripada yang sebelumnya, yang membolehkan carian mendatar atau menegak, pilihan padanan lanjutan dan lebih fleksibel.
• INDEKS: Mengembalikan nilai sel tertentu dalam tatasusunan, memandangkan kedudukan baris dan lajurnya.
• TAMADUN: Mengembalikan kedudukan relatif sesuatu nilai dalam julat atau tatasusunan.
• TIDAK LANGSUNG: Mencipta rujukan berdasarkan nilai teks, berguna untuk rujukan dinamik.
• OFFSET: Mengembalikan offset rujukan daripada sel permulaan, sangat berguna untuk rujukan berubah-ubah.
• PILIH: Membolehkan anda memilih nilai daripada senarai argumen berdasarkan indeks yang anda takrifkan.
• KAWASAN-KAWASAN: Mengembalikan bilangan kawasan dalam rujukan.
• LAjur y FILA: Mengembalikan nombor lajur atau baris rujukan yang diberikan.
• BARIS y COLUMNAS: Mengembalikan jumlah bilangan baris atau lajur dalam rujukan.
• HYPERLINK: Cipta akses pantas ke dokumen, tapak web atau lokasi lain dalam buku kerja itu sendiri.
• TRANSPOSE: Tukar baris dan lajur dalam matriks.
• UNICOS: Mengembalikan senarai nilai unik daripada tatasusunan atau julat.
• PENAPIS: Mengekstrak data daripada julat berdasarkan kriteria yang anda tentukan.

Cara fungsi carian popular berfungsi

Mari kita pecahkan fungsi carian yang paling popular, terangkan sintaksnya dan lihat cara ia berkelakuan dalam situasi biasa. Ini akan membantu anda memahami perbezaan dan menerapkan setiap satu mengikut kesesuaian.

Fungsi VLOOKUP

Fungsi ini LIHAT (atau VLOOKUP dalam bahasa Inggeris) mungkin adalah yang paling banyak digunakan apabila ia datang untuk mencari maklumat dalam jadual secara menegak.

Sintaks: BUSCARV(valor_buscado, matriz_buscar_en, indicador_columnas, )

• nilai_cari: Data yang anda ingin cari.
• matrix_search_in: Julat sel tempat carian dilakukan, dengan lajur pertama mengandungi nilai yang dicari.
• lajur_penunjuk: Nombor lajur dari mana nilai yang terhasil akan diambil, bermula dari kiri.
• diperintahkan: Pilihan. Betul (lalai) untuk padanan anggaran atau Palsu untuk padanan tepat.

Contohnya, jika anda mempunyai jadual produk dan anda ingin mencari harga rujukan tertentu, LIHAT boleh mengembalikan nilai itu secara automatik tanpa anda perlu mencarinya secara manual.

Fungsi HLOOKUP

Adik perempuan yang terdahulu, HLOOKUP, mencari baris atas julat dan mengembalikan data daripada lajur yang ditentukan.

Sintaks: BUSCARH(valor_buscado, matriz_buscar_en, indicador_filas, )

Ia sesuai untuk jadual di mana maklumat utama disusun secara mendatar.

Fungsi CARIAN

Fungsi ini CARIAN Ia berguna apabila anda ingin mencari satu baris atau lajur dan mengembalikan nilai dalam kedudukan yang sama dari julat lain. Ia boleh digunakan dalam dua cara: sebagai vektor atau sebagai matriks.

• Bentuk vektor: Membolehkan anda mencari dalam julat satu baris atau lajur (vektor) dan mendapatkan nilai daripada vektor lain dalam kedudukan yang sama.
• Bentuk matriks: Mencari baris atau lajur pertama matriks dan mengembalikan nilai daripada baris atau lajur terakhir yang sepadan.

Sintaks (vektor): BUSCAR(valor_buscado; vector_de_comparación; )

Sintaks (susunan): BUSCAR(valor_buscado; matriz)

Dalam kedua-dua kes, jika nilai yang dicari tidak ditemui dengan tepat, fungsi tersebut mengembalikan nilai terdekat yang kurang daripada atau sama dengan nilai yang dicari. Jika nilai yang dicari adalah kurang daripada sebarang nilai dalam tatasusunan atau vektor, ia mengembalikan #N/A.

Fungsi XLOOKUP

Salah satu penambahan terbaru, SEARCHX (XLOOKUP) menambah baik dan memudahkan carian. Ia membolehkan anda mencari secara mendatar dan menegak, menyokong padanan anggaran dan tepat, carian terbalik, lalai jika tiada padanan ditemui dan banyak lagi. Untuk mengetahui lebih lanjut tentang faedahnya, lihat kami.

Fungsi ini lebih fleksibel dan mengelakkan banyak batasan VLOOKUP dan HLOOKUP, contohnya, ia tidak memerlukan nilai carian berada dalam lajur atau baris pertama tatasusunan.

fungsi INDEX dan MATCH

Gabungan INDEKS y TAMADUN membolehkan carian lanjutan jauh melebihi apa yang boleh dilakukan oleh VLOOKUP. INDEKS membolehkan anda mengekstrak nilai daripada tatasusunan berdasarkan kedudukannya. TAMADUN cari kedudukan nilai dalam julat dan bersama-sama, anda boleh mencari sebarang nilai yang anda inginkan dalam mana-mana baris atau lajur jadual tanpa mengira susunannya.

artikel berkaitan:

Cara Mengurus Tarikh dan Masa Dengan Betul dalam Excel: Panduan Lengkap Langkah demi Langkah

Sintaks INDEX: ÍNDICE(matriz, núm_fila, )

Sintaks MATCH: COINCIDIR(valor_buscado, rango_buscado, )

Bersama-sama, ini membolehkan anda mencari nilai secara dinamik dan membina carian yang kompleks.

Fungsi rujukan lain yang berguna

• OFFSET membolehkan anda mendapatkan julat mengimbangi daripada sel permulaan, kunci kepada mencipta julat dinamik dan mempelajari lebih lanjut Cara membuat analisis bersyarat dalam Excel.
• TIDAK LANGSUNG Ia amat berguna jika anda perlu membina rujukan menggunakan teks, membantu anda bekerja dengan formula berubah-ubah.
• LAjur y FILA Ia menunjukkan nombor lajur atau baris rujukan, sangat praktikal untuk pengiraan automatik.
• TRANSPOSE membolehkan anda menukar baris untuk lajur, sekiranya jadual anda berada dalam arah yang bertentangan dengan apa yang anda perlukan.

Fungsi carian dan rujukan moden dan lanjutan

Dari masa ke masa, Excel telah menggabungkan ciri baharu yang meningkatkan lagi keupayaan analisis dan automasinya:

• PENAPIS: Membolehkan anda mendapatkan jadual yang ditapis mengikut keadaan atau set syarat yang anda tentukan.
• UNICOS: Mengeluarkan semua nilai unik daripada julat, mengalih keluar pendua.
• PESANAN y SUSUN MENGIKUT: Mereka memudahkan penyusunan data dalam matriks atau berdasarkan julat lain.
• TAKE, GUGUR, TOCOL, TOROW: Pelbagai fungsi yang membolehkan anda memanipulasi matriks, mengembalikan baris atau lajur tertentu sahaja, atau mengubah julat menjadi lajur/baris tunggal.
• WRACOLS y WRAPROWS: Susun semula matriks dengan membahagikannya mengikut bilangan unsur yang anda tentukan.

Fungsi ini membolehkan anda membuat jadual dan analisis yang lebih dinamik, mengautomasikan banyak tugas tanpa memerlukan sumber luaran yang lain.

Contoh praktikal: CARI dalam vektor dan matriks

Bayangkan anda mempunyai lajur dengan nilai berangka dan satu lagi dengan warna yang berkaitan. Anda ingin tahu warna apa yang sepadan dengan nilai 4,19.

1. Letakkan data anda dalam dua lajur, contohnya, A dan B, seperti ini:
   

   Kekerapan	warna
   4,14	merah
   4,19	oren
   5,17	kuning
   5,77	hijau
   6,39	azul

2. Sekarang, dalam sel lain anda boleh menulis formula: =BUSCAR(4,19; A2:A6; B2:B6)

Excel akan melihat dalam lajur A untuk nilai 4,19 dan mengembalikan warna yang berada dalam baris yang sama dalam lajur B: oren.

Jika anda mencari nilai yang tiada dalam jadual, warna dengan nombor yang paling hampir akan dikembalikan. Jika nilai carian kurang daripada lajur minimum, #N/A akan dikembalikan.

Jenis carian ini penting dalam analisis data, inventori, kawalan stok, dan sebarang situasi di mana anda memerlukan maklumat rujukan silang.

Petua utama untuk menguasai fungsi carian dan rujukan

• Dalam versi Excel yang lebih baharu, bertaruh pada SEARCHX apabila boleh, kerana ia menangani banyak batasan VLOOKUP dan HLOOKUP. Untuk mengetahui cara menggunakannya, kami mengesyorkan melawati .
• Apabila anda bekerja dengan tatasusunan dinamik atau data yang sering berubah, menggabungkan INDEX dan PADANAN untuk hasil yang mantap dan fleksibel.
• Jangan lupa untuk memanfaatkan ciri seperti PENAPIS, UNICOS dan fungsi matriks baharu untuk mengautomasikan laporan.
• Jika anda perlu merujuk julat yang berubah dalam saiz, gunakan OFFSET atau TIDAK LANGSUNG untuk memastikan formula anda dikemas kini.
• Untuk menjadikan formula anda lebih mudah dibaca, gunakan nama julat dan terangkan dengan jelas perkara yang dilakukan oleh setiap fungsi, terutamanya jika anda berkongsi fail dengan orang lain.

Aplikasi praktikal dan contoh kehidupan sebenar

Fungsi carian dan rujukan ialah asas untuk mengautomasikan tugas harian dalam Excel, daripada menyepadukan data daripada hamparan berbeza kepada menjana laporan tersuai dan mencipta papan pemuka. Beberapa aplikasi praktikal termasuk:

• Jualan rujukan silang dan data pelanggan untuk mendapatkan maklumat yang berkaitan dengan cepat.
• Buat templat inventori yang dikemas kini secara automatik berdasarkan input produk.
• Buat laporan dinamik yang memaparkan nilai berdasarkan pilihan tersuai.
• Bandingkan senarai pekerja, pelajar atau produk, mengesan pendua dan perbezaan.
• Tapis jumlah maklumat yang besar untuk mendapatkan hanya rekod yang memenuhi kriteria tertentu.

Kesilapan biasa dan cara mengelakkannya

Adalah perkara biasa untuk membuat kesilapan tertentu apabila menggunakan fungsi ini, terutamanya jika anda tidak memberi perhatian kepada sintaks dan julat:

• #N/A jenis ralat: Ini berlaku apabila nilai yang dicari tidak ditemui atau apabila julat carian tidak mencukupi.
• Masalah dengan rujukan relatif dan mutlak: Berhati-hati apabila menyeret formula; gunakan simbol $ untuk menambat lajur atau baris.
• Tidak padan dalam bilangan baris/lajur: Apabila menggunakan LOOKUP atau INDEX/MATCH, pastikan julat sepadan dengan saiz.
• Carian sensitif huruf besar-besaran: Fungsi ini biasanya tidak peka huruf besar-besaran, jadi 'John' dan 'john' akan menjadi setara.
• Selalu ingat mengisih data jika anda akan melakukan carian anggaran, kerana kekurangan pesanan boleh memberikan hasil yang tidak dijangka.

Petua untuk meningkatkan produktiviti anda dengan ciri ini

• Berlatih dengan contoh jadual, dengan cara ini anda akan belajar mengendalikan ralat dan memahami tingkah laku setiap fungsi.
• Gunakan wizard formula baharu dalam Excel untuk membina fungsi kompleks langkah demi langkah.
• Sentiasa fikirkan tentang cara anda boleh mengautomasikan proses berulang menggunakan formula rujukan, menjimatkan masa dan mengelakkan kesilapan manusia.
• Mengambil kesempatan Bantuan Excel dan dokumentasi rasmi untuk mempelajari varian lanjutan dan kemungkinan setiap fungsi.

artikel berkaitan:

Cara Isih dan Penapis Data Seperti Pakar dalam Excel: Panduan Profesional dan Praktikal

Menguasai fungsi carian dan rujukan Excel ialah kemahiran yang membezakan antara hanya mengisi hamparan dan menjadi pakar analisis data yang sebenar. Jika anda memanfaatkan alatan ini dan berlatih dengan contoh yang berbeza, anda akan mengautomasikan tugas anda, mengurangkan ralat dan meningkatkan produktiviti anda, supaya anda boleh menumpukan pada perkara yang benar-benar penting: membuat keputusan yang lebih baik dan memudahkan kerja harian anda. Berani bereksperimen dengan mereka dan anda akan melihat perubahan dalam masa yang singkat! Kongsi panduan supaya lebih ramai orang tahu cara melakukannya..