Tutorial tidak langsung Excel ini menerangkan sintaks fungsi, penggunaan asas dan menyediakan beberapa contoh formula yang menunjukkan cara menggunakan tidak langsung dalam Excel.

Sebilangan besar fungsi wujud di Microsoft Excel, ada yang mudah difahami, yang lain memerlukan keluk pembelajaran yang panjang, dan bekas yang digunakan lebih kerap daripada yang terakhir. Namun, Excel tidak langsung adalah salah satu jenis. Fungsi Excel ini tidak melakukan apa -apa pengiraan, dan tidak menilai sebarang syarat atau ujian logik.

Nah, apakah fungsi tidak langsung dalam Excel dan apa yang saya gunakan untuknya? Ini adalah soalan yang sangat baik dan semoga anda mendapat jawapan yang komprehensif dalam beberapa minit apabila anda selesai membaca tutorial ini.

Fungsi Excel tidak langsung - sintaks dan penggunaan asas

Seperti namanya, Excel tidak langsung digunakan untuk sel -sel rujukan secara tidak langsung, julat, lembaran lain atau buku kerja. Dalam erti kata lain, fungsi tidak langsung membolehkan anda membuat sel dinamik atau rujukan julat dan bukannya pengekodan keras mereka. Akibatnya, anda boleh mengubah rujukan dalam formula tanpa mengubah formula itu sendiri. Selain itu, rujukan tidak langsung ini tidak akan berubah apabila beberapa baris atau lajur baru dimasukkan ke dalam lembaran kerja atau apabila anda memadam mana -mana yang sedia ada.

Semua ini mungkin lebih mudah difahami dari contoh. Walau bagaimanapun, untuk dapat menulis formula, bahkan yang paling mudah, anda perlu mengetahui hujah -hujah fungsi, bukan? Oleh itu, mari kita lihat dengan cepat sintaks tidak langsung Excel terlebih dahulu.

Sintaks fungsi tidak langsung

Fungsi tidak langsung dalam Excel mengembalikan rujukan sel dari rentetan teks. Ia mempunyai dua hujah, yang pertama diperlukan dan yang kedua adalah pilihan:

Ref_text - adalah rujukan sel, atau rujukan kepada sel dalam bentuk rentetan teks, atau julat bernama.

A1 - adalah nilai logik yang menentukan jenis rujukan yang terkandung dalam argumen ref_text:

• Jika benar atau ditinggalkan, ref_text ditafsirkan sebagai rujukan sel gaya A1.
• Jika palsu, ref_text dianggap sebagai rujukan R1C1.

Walaupun jenis rujukan R1C1 mungkin berguna dalam situasi tertentu, anda mungkin mahu menggunakan rujukan A1 yang biasa pada kebanyakan masa. Bagaimanapun, hampir semua formula tidak langsung dalam tutorial ini akan menggunakan rujukan A1, jadi kami akan menghilangkan hujah kedua.

Penggunaan asas fungsi tidak langsung

Untuk masuk ke dalam wawasan fungsi, mari kita tulis formula mudah yang menunjukkan bagaimana anda menggunakan tidak langsung dalam Excel.

Katakan, anda mempunyai nombor 3 dalam sel A1, dan teks A1 dalam sel C1. Sekarang, letakkan formula =INDIRECT(C1) di mana -mana sel lain dan lihat apa yang berlaku:

• Fungsi tidak langsung merujuk kepada nilai dalam sel C1, iaitu A1.
• Fungsi ini diarahkan ke sel A1 di mana ia memilih nilai untuk kembali, iaitu nombor 3.

Oleh itu, apakah fungsi tidak langsung dalam contoh ini adalah menukar rentetan teks ke dalam rujukan sel .

Jika anda fikir ini masih mempunyai rasa praktikal yang sangat sedikit, sila berikan kepada saya dan saya akan menunjukkan kepada anda lebih banyak formula yang mendedahkan kuasa sebenar fungsi tidak langsung Excel.

Cara Menggunakan Contoh Formula tidak langsung - Formula

Seperti yang ditunjukkan dalam contoh di atas, anda boleh menggunakan fungsi Excel tidak langsung untuk meletakkan alamat satu sel ke dalam yang lain sebagai rentetan teks biasa, dan dapatkan nilai sel 1 ^ST dengan merujuk 2 ^ND . Walau bagaimanapun, contoh remeh itu tidak lebih daripada satu petunjuk pada keupayaan tidak langsung.

Apabila bekerja dengan data sebenar, fungsi tidak langsung boleh mengubah rentetan teks ke dalam rujukan termasuk rentetan yang sangat kompleks yang anda bina menggunakan nilai -nilai sel -sel lain dan hasil yang dikembalikan oleh formula Excel yang lain. Tetapi jangan letakkan kereta sebelum kuda, dan berjalan melalui beberapa formula tidak langsung Excel, satu demi satu.

Mewujudkan rujukan tidak langsung dari nilai sel

Seperti yang anda ingat, fungsi tidak langsung Excel membolehkan gaya rujukan A1 dan R1C1. Biasanya, anda tidak boleh menggunakan kedua -dua gaya dalam satu helaian pada satu masa, anda hanya boleh menukar antara dua jenis rujukan melalui File> Options> Formula > R1c1 ceck box . Inilah sebab mengapa pengguna Excel jarang mempertimbangkan menggunakan R1C1 sebagai pendekatan rujukan alternatif.

Dalam formula tidak langsung, anda boleh menggunakan sama ada jenis rujukan pada lembaran yang sama jika anda mahu. Sebelum kita bergerak lagi, anda mungkin ingin mengetahui perbezaan antara gaya rujukan A1 dan R1C1.

Gaya A1 adalah jenis rujukan biasa dalam Excel yang merujuk kepada lajur diikuti dengan nombor baris. Sebagai contoh, B2 merujuk kepada sel di persimpangan lajur B dan baris 2.

Gaya R1C1 adalah jenis rujukan yang bertentangan - baris diikuti oleh lajur, yang mengambil sedikit masa untuk digunakan untuk :) sebagai contoh, R4C1 merujuk kepada sel A4 yang berada dalam baris 4, lajur 1 dalam lembaran. Jika tiada nombor datang selepas huruf, maka anda merujuk kepada baris atau lajur yang sama.

Dan sekarang, mari kita lihat bagaimana fungsi tidak langsung mengendalikan rujukan A1 dan R1C1:

Seperti yang anda lihat dalam tangkapan skrin di atas, tiga formula tidak langsung yang berbeza mengembalikan hasil yang sama. Adakah anda sudah tahu mengapa? Saya yakin anda mempunyai :)

• Formula dalam sel D1: =INDIRECT(C1)

Ini adalah yang paling mudah. Formula merujuk kepada sel C1, mengambil nilainya - String teks A2 , menukarkannya menjadi rujukan sel, kepala ke sel A2 dan mengembalikan nilainya, iaitu 222.

• Formula dalam sel D3: =INDIRECT(C3,FALSE)

Palsu dalam hujah 2 ^ND menunjukkan bahawa nilai yang dirujuk (C3) harus dianggap seperti rujukan sel R1C1, iaitu nombor baris diikuti dengan nombor lajur. Oleh itu, formula tidak langsung kami menafsirkan nilai dalam sel C3 (R2C1) sebagai rujukan kepada sel pada konjungsi baris 2 dan lajur 1, iaitu sel A2.

Membuat rujukan tidak langsung dari nilai sel dan teks

Begitu juga dengan bagaimana kami membuat rujukan dari nilai sel, anda boleh menggabungkan rentetan teks dan rujukan sel dalam formula tidak langsung anda, diikat bersama dengan pengendali penggabungan (&).

Dalam contoh berikut, formula: = tidak langsung ("B" & C2) mengembalikan nilai dari sel B2 berdasarkan rantai logik berikut:

Fungsi tidak langsung menggabungkan unsur -unsur dalam argumen ref_text -teks b dan nilai dalam sel C2 -> nilai dalam sel C2 adalah nombor 2, yang membuat rujukan kepada sel B2 -> Formula pergi ke sel B2 dan mengembalikan nilainya, iaitu nombor 10.

Menggunakan fungsi tidak langsung dengan julat bernama

Selain membuat rujukan dari nilai sel dan teks, anda boleh mendapatkan fungsi tidak langsung Excel untuk merujuk kepada julat yang dinamakan .

Katakan, anda mempunyai julat bernama berikut dalam helaian anda:

• Epal - B2: B6
• Pisang - C2: C6
• Lemons - D2: D6

Untuk membuat rujukan dinamik Excel kepada mana -mana julat yang dinamakan di atas, masukkan namanya dalam beberapa sel, katakan G1, dan merujuk kepada sel itu dari formula tidak langsung =INDIRECT(G1) .

Dan sekarang, anda boleh mengambil langkah lebih jauh dan menyerap formula tidak langsung ini ke dalam fungsi Excel yang lain untuk mengira jumlah dan purata nilai dalam julat bernama yang diberikan, atau mencari nilai maksimum / minimum dalam kemarahan:

• =SUM(INDIRECT(G1))
• =AVERAGE(INDIRECT(G1))
• =MAX(INDIRECT(G1))
• =MIN(INDIRECT(G1))

Sekarang bahawa anda mempunyai idea umum tentang cara menggunakan fungsi tidak langsung dalam Excel, kita boleh bereksperimen dengan formula yang lebih kuat.

Formula tidak langsung untuk merujuk secara dinamik ke lembaran kerja lain

Kegunaan fungsi tidak langsung Excel tidak terhad kepada rujukan sel "dinamik". Anda juga boleh menggunakannya untuk merujuk kepada sel -sel dalam lembaran kerja lain "dengan cepat", dan inilah caranya.

Katakan, anda mempunyai beberapa data penting dalam Lembaran 1, dan anda ingin menarik data tersebut dalam Lembaran 2. Tangkapan skrin berikut menunjukkan bagaimana formula tidak langsung Excel dapat mengendalikan tugas ini:

Mari memecahkan formula yang anda lihat di tangkapan skrin dan faham.

Seperti yang anda ketahui, cara biasa untuk merujuk lembaran lain dalam Excel adalah menulis nama lembaran yang diikuti oleh tanda seru dan rujukan sel / julat, seperti SheetName! Range . Oleh kerana nama lembaran sering mengandungi ruang, anda lebih baik melampirkannya (nama, bukan ruang :) dalam petikan tunggal untuk mengelakkan kesilapan, contohnya 'lembaran saya!' $ A $ 1 .

Dan sekarang, yang perlu anda lakukan ialah memasukkan nama lembaran dalam satu sel, alamat sel di lain, menggabungkannya dalam rentetan teks, dan memberi makan rentetan itu ke fungsi tidak langsung. Ingat bahawa dalam rentetan teks, anda perlu melampirkan setiap elemen selain alamat sel atau nombor dalam petikan berganda dan menghubungkan semua elemen bersama -sama menggunakan pengendali concatenation (&).

Memandangkan perkara di atas, kami mendapat corak berikut:

Kembali ke contoh kami, anda meletakkan nama lembaran dalam sel A1, dan taipkan alamat sel dalam lajur B, seperti yang ditunjukkan dalam tangkapan skrin di atas. Hasilnya, anda mendapat formula berikut:

INDIRECT("'" & $A$1 & "'!" & B1)

Juga, sila perhatikan bahawa jika anda menyalin formula ke dalam pelbagai sel, anda perlu mengunci rujukan kepada nama lembaran menggunakan rujukan sel mutlak seperti $ A $ 1.

Nota

• Jika salah satu sel yang mengandungi nama dan alamat sel ^ND (A1 dan B1 dalam formula di atas) kosong, formula tidak langsung anda akan mengembalikan ralat. Untuk mengelakkan ini, anda boleh membungkus fungsi tidak langsung dalam fungsi IF:

  IF(OR($A$1="",B1=""), "", INDIRECT("'" & $A$1 & "'!" & B1))

• Untuk formula tidak langsung yang merujuk kepada lembaran lain untuk berfungsi dengan betul, lembaran yang dirujuk harus dibuka, jika tidak, formula akan mengembalikan ralat #REF. Untuk mengelakkan kesilapan, anda boleh menggunakan fungsi Iferror, yang akan memaparkan rentetan kosong, apa sahaja ralat berlaku:

  IFERROR(INDIRECT("'" & $A$1 & "'!" &B1), "")

Membuat rujukan dinamik Excel ke buku kerja lain

Formula tidak langsung yang merujuk kepada buku kerja Excel yang berbeza adalah berdasarkan pendekatan yang sama sebagai rujukan kepada spreadsheet lain. Anda hanya perlu menentukan nama buku kerja tambahan kepada nama lembaran dan alamat sel.

Untuk membuat perkara lebih mudah, mari kita mulakan dengan membuat rujukan kepada buku lain dengan cara yang biasa (apostrophes ditambah sekiranya buku dan/atau nama lembaran anda mengandungi ruang): '[book_name.xlsx] sheet_name'!

Dengan mengandaikan bahawa nama buku berada dalam sel A2, nama lembaran berada di B2, dan alamat sel berada di C2, kita mendapat formula berikut:

=INDIRECT("'[" & $A$2 & "]" & $B$2 & "'!" & C2)

Oleh kerana anda tidak mahu sel -sel yang mengandungi nama buku dan lembaran berubah apabila menyalin formula ke sel -sel lain, anda mengunci mereka dengan menggunakan rujukan sel mutlak, $ A $ 2 dan $ B $ 2, masing -masing.

Dan sekarang, anda boleh dengan mudah menulis rujukan dinamik anda sendiri kepada buku kerja Excel yang lain dengan menggunakan corak berikut:

Nota. Buku kerja formula anda merujuk harus selalu terbuka, jika tidak, fungsi tidak langsung akan membuang ralat #REF. Seperti biasa, fungsi iferror dapat membantu anda mengelakkannya:

=IFERROR(INDIRECT("'[" & A2 & "]" & $A$1 & "'!" & B1), "")

Menggunakan fungsi tidak langsung Excel untuk mengunci rujukan sel

Biasanya, Microsoft Excel mengubah rujukan sel apabila anda memasukkan baris atau lajur sedia ada baru atau memadam dalam lembaran. Untuk mengelakkan ini berlaku, anda boleh menggunakan fungsi tidak langsung untuk bekerja dengan rujukan sel yang harus tetap utuh dalam apa jua keadaan.

Untuk menggambarkan perbezaannya, sila lakukan yang berikut:

1. Masukkan sebarang nilai dalam mana -mana sel, katakan, nombor 20 dalam sel A1.
2. Rujuk kepada A1 dari dua sel lain dengan cara yang berbeza: =A1 dan =INDIRECT("A1")
3. Masukkan baris baru di atas baris 1.

Lihat apa yang berlaku? Sel dengan sama dengan pengendali logik masih mengembalikan 20, kerana formulanya telah diubah secara automatik kepada = A2. Sel dengan formula tidak langsung kini kembali 0, kerana formula tidak berubah apabila baris baru dimasukkan dan ia masih merujuk kepada sel A1, yang kini kosong:

Selepas demonstrasi ini, anda mungkin berada di bawah tanggapan bahawa fungsi tidak langsung lebih banyak gangguan daripada bantuan. Baiklah, mari cuba dengan cara lain.

Katakan, anda ingin jumlah nilai dalam sel A2: A5, dan anda boleh melakukan ini dengan mudah menggunakan fungsi jumlah:

=SUM(A2:A5)

Walau bagaimanapun, anda mahu formula tetap tidak berubah, tidak kira berapa banyak baris dipadam atau dimasukkan. Penyelesaian yang paling jelas - penggunaan rujukan mutlak - tidak akan membantu. Untuk memastikan, masukkan formula =SUM($A$2:$A$5) dalam beberapa sel, masukkan baris baru, katakan pada baris 3, dan ... cari formula yang ditukar kepada =SUM($A$2:$A$6) .

Sudah tentu, kesopanan seperti Microsoft Excel akan berfungsi dengan baik dalam kebanyakan kes. Walau bagaimanapun, mungkin terdapat senario apabila anda tidak mahu formula berubah secara automatik. Penyelesaiannya adalah menggunakan fungsi tidak langsung, seperti ini:

=SUM(INDIRECT("A2:A5"))

Oleh kerana Excel melihat "A1: A5" sebagai rentetan teks semata -mata dan bukannya rujukan pelbagai, ia tidak akan membuat apa -apa perubahan apabila anda memasukkan atau memadam baris (s).

Menggunakan tidak langsung dengan fungsi Excel lain

Selain daripada jumlah, tidak langsung sering digunakan dengan fungsi Excel lain seperti baris, lajur, alamat, vlookup, sumif, untuk menamakan beberapa.

Contoh 1. Fungsi tidak langsung dan baris

Sering kali, fungsi baris digunakan dalam Excel untuk mengembalikan pelbagai nilai. Sebagai contoh, anda boleh menggunakan formula array berikut (ingat ia memerlukan menekan CTRL Shift Enter ) untuk mengembalikan purata 3 nombor terkecil dalam julat A1: A10:

=AVERAGE(SMALL(A1:A10,ROW(1:3)))

Walau bagaimanapun, jika anda memasukkan baris baru dalam lembaran kerja anda, di mana -mana di antara baris 1 dan 3, julat dalam fungsi baris akan berubah menjadi baris (1: 4) dan formula akan mengembalikan purata 4 nombor terkecil dan bukannya 3.

Untuk mengelakkan ini berlaku, sarang tidak langsung dalam fungsi baris dan formula array anda akan sentiasa betul, tidak kira berapa banyak baris dimasukkan atau dipadamkan:

=AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:3"))))

Berikut adalah beberapa contoh lagi menggunakan tidak langsung dan baris bersempena dengan fungsi yang besar: bagaimana jumlah nombor terbesar dalam julat.

Contoh 2. Fungsi tidak langsung dan alamat

Anda boleh menggunakan Excel tidak langsung bersama -sama dengan fungsi alamat untuk mendapatkan nilai dalam sel tertentu dengan cepat.

Seperti yang anda ingat, fungsi alamat digunakan dalam Excel untuk mendapatkan alamat sel oleh nombor baris dan lajur. Sebagai contoh, formula =ADDRESS(1,3) mengembalikan rentetan $ C $ 1 kerana C1 adalah sel di persimpangan baris ¹ dan 3 ^RD .

Untuk membuat rujukan sel tidak langsung, anda hanya menanamkan fungsi alamat ke dalam formula tidak langsung seperti ini:

=INDIRECT(ADDRESS(1,3))

Sudah tentu, formula remeh ini hanya menunjukkan teknik. Dan inilah beberapa contoh yang mungkin terbukti sangat berguna:

• Formula alamat tidak langsung - Cara menukar baris dan lajur.
• Vlookup dan tidak langsung - Bagaimana untuk menarik data secara dinamik dari lembaran yang berbeza.
• Tidak langsung dengan Indeks / Perlawanan - Cara Membawa Formula VLOOKUP Sensitif Kes ke Kesempurnaan.
• Excel tidak langsung dan countif - Cara menggunakan fungsi countif pada julat yang tidak bersesuaian atau pemilihan sel.

Menggunakan tidak langsung dengan pengesahan data dalam Excel

Anda boleh menggunakan fungsi Excel tidak langsung dengan pengesahan data untuk membuat senarai drop down cascading yang memaparkan pilihan yang berbeza bergantung pada nilai apa yang dipilih pengguna dalam lungsur pertama.

Senarai drop-down bergantung mudah adalah sangat mudah dibuat. Apa yang diperlukan adalah beberapa julat bernama untuk menyimpan item dropdown dan formula mudah =INDIRECT(A2) di mana A2 adalah sel yang memaparkan senarai drop-down pertama anda.

Untuk membuat menu atau drop-down yang lebih kompleks dengan entri pelbagai perkataan, anda memerlukan formula tidak langsung yang lebih kompleks dengan fungsi pengganti bersarang.

Untuk panduan langkah demi langkah terperinci mengenai cara menggunakan Pengesahan Data Excel secara tidak langsung, sila lihat tutorial ini: Cara Membuat Senarai Drop Down Dependent dalam Excel.

Fungsi Excel tidak langsung - kemungkinan kesilapan dan masalah

Seperti yang ditunjukkan dalam contoh di atas, fungsi tidak langsung sangat membantu apabila berurusan dengan rujukan sel dan pelbagai. Walau bagaimanapun, tidak semua pengguna Excel dengan penuh semangat memeluknya kerana penggunaan secara tidak langsung dalam formula Excel mengakibatkan kekurangan ketelusan. Fungsi tidak langsung adalah sukar untuk dikaji semula kerana sel yang dirujuknya bukanlah lokasi utama nilai yang digunakan dalam formula, yang cukup mengelirukan, terutama ketika bekerja dengan formula kompleks yang besar.

Sebagai tambahan kepada perkara di atas, seperti mana -mana fungsi Excel yang lain, tidak langsung boleh membuang kesilapan jika anda menyalahgunakan hujah fungsi. Berikut adalah senarai kesilapan yang paling biasa:

Excel tidak langsung #ref! ralat

Selalunya, fungsi tidak langsung mengembalikan #REF! Ralat dalam tiga kes:

1. Ref_text bukan rujukan sel yang sah . Jika parameter ref_text dalam formula tidak langsung anda bukan rujukan sel yang sah, formula akan menghasilkan #REF! nilai ralat. Untuk mengelakkan masalah yang mungkin, sila periksa hujah fungsi tidak langsung.
2. Had julat melebihi . Jika argumen ref_text formula tidak langsung anda merujuk kepada pelbagai sel di luar had baris 1,048,576 atau had lajur sebanyak 16,384, anda juga akan mendapat kesilapan #REF dalam Excel 2007, 2010 dan Excel 2013.
3. Lembaran atau buku kerja yang dirujuk ditutup. Sekiranya formula tidak langsung anda merujuk kepada buku kerja atau lembaran kerja Excel yang lain, buku kerja / spreadsheet lain mesti dibuka, jika tidak, tidak langsung mengembalikan #REF! ralat.

Excel tidak langsung #name? ralat

Ini adalah kes yang paling jelas, menyiratkan bahawa terdapat beberapa kesilapan dalam nama fungsi, yang membawa kita ke titik seterusnya :)

Menggunakan fungsi tidak langsung di kawasan bukan bahasa Inggeris

Anda mungkin ingin tahu bahawa nama Inggeris fungsi tidak langsung telah diterjemahkan ke dalam 14 bahasa, termasuk:

Jika anda berminat untuk mendapatkan senarai penuh, sila lihat halaman ini.

Masalah biasa dengan lokalisasi bukan bahasa Inggeris bukanlah nama fungsi tidak langsung, tetapi tetapan serantau yang berbeza untuk pemisah senarai . Dalam konfigurasi Windows standard untuk Amerika Utara dan beberapa negara lain, pemisah senarai lalai adalah koma. Semasa di negara -negara Eropah, koma dikhaskan sebagai simbol perpuluhan dan pemisah senarai ditetapkan ke titik koma.

Akibatnya, apabila menyalin formula antara dua kawasan Excel yang berbeza, anda boleh mendapatkan mesej ralat " Kami mendapati masalah dengan formula ini ... " kerana pemisah senarai yang digunakan dalam formula adalah berbeza daripada apa yang ditetapkan pada mesin anda. Sekiranya anda mengalami ralat ini apabila menyalin beberapa formula tidak langsung dari tutorial ini ke dalam Excel anda, hanya gantikan semua koma (,) dengan titik koma (;) untuk membetulkannya.

Untuk memeriksa senarai pemisah dan simbol perpuluhan yang ditetapkan pada mesin anda, buka panel kawalan , dan pergi ke Wilayah dan Bahasa> Tetapan Tambahan .

Mudah -mudahan, tutorial ini telah memberikan sedikit cahaya menggunakan tidak langsung dalam Excel. Sekarang anda tahu kekuatan dan batasannya, sudah tiba masanya untuk memberikannya satu pukulan dan melihat bagaimana fungsi tidak langsung dapat memudahkan tugas Excel anda. Terima kasih kerana membaca!

Atas ialah kandungan terperinci Fungsi Excel tidak langsung - contoh penggunaan asas dan formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!