Contents [show]
tahniah! Anda berjaya ke hari terakhir maraton 30 fungsi Excel dalam 30 hari. Ia merupakan satu perjalanan yang panjang dan menarik di mana anda telah mempelajari banyak perkara berguna tentang fungsi Excel.
Pada hari ke-30 maraton, kami akan menumpukan kajian fungsi TIDAK LANGSUNG (INDIRECT), yang mengembalikan pautan yang ditentukan oleh rentetan teks. Dengan fungsi ini, anda boleh membuat senarai juntai bawah bergantung. Contohnya, apabila memilih negara daripada senarai lungsur turun menentukan pilihan yang akan muncul dalam senarai lungsur bandar.
Jadi, mari kita lihat dengan lebih dekat bahagian teori fungsi tersebut TIDAK LANGSUNG (TIDAK LANGSUNG) dan terokai contoh praktikal aplikasinya. Jika anda mempunyai maklumat atau contoh tambahan, sila kongsikannya dalam ulasan.
Fungsi 30: TIDAK LANGSUNG
fungsi TIDAK LANGSUNG (INDIRECT) mengembalikan pautan yang ditentukan oleh rentetan teks.
Bagaimanakah anda boleh menggunakan fungsi INDIRECT?
Sejak fungsi TIDAK LANGSUNG (INDIRECT) mengembalikan pautan yang diberikan oleh rentetan teks, anda boleh menggunakannya untuk:
- Buat pautan awal yang tidak berubah.
- Buat rujukan kepada julat bernama statik.
- Buat pautan menggunakan maklumat helaian, baris dan lajur.
- Buat tatasusunan nombor yang tidak berubah.
Sintaks TIDAK LANGSUNG (TIDAK LANGSUNG)
fungsi TIDAK LANGSUNG (INDIRECT) mempunyai sintaks berikut:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- teks_ref (link_to_cell) ialah teks pautan.
- a1 – jika sama dengan BENAR (BENAR) atau tidak dinyatakan, maka gaya pautan akan digunakan A1; dan jika PALSU (PALSU), maka gayanya R1C1.
Perangkap TIDAK LANGSUNG (TIDAK LANGSUNG)
- fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) dikira semula apabila nilai dalam lembaran kerja Excel berubah. Ini boleh melambatkan buku kerja anda jika fungsi itu digunakan dalam banyak formula.
- Sekiranya fungsinya TIDAK LANGSUNG (TIDAK LANGSUNG) mencipta pautan ke buku kerja Excel yang lain, buku kerja itu mesti dibuka atau formula akan melaporkan ralat #REF! (#LINK!).
- Sekiranya fungsinya TIDAK LANGSUNG (TIDAK LANGSUNG) merujuk julat yang melebihi had baris dan lajur, formula akan melaporkan ralat #REF! (#LINK!).
- fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) tidak boleh merujuk julat bernama dinamik.
Contoh 1: Buat pautan awal yang tidak berubah
Dalam contoh pertama, lajur C dan E mengandungi nombor yang sama, jumlahnya dikira menggunakan fungsi SUM (SUM) juga sama. Walau bagaimanapun, formulanya sedikit berbeza. Dalam sel C8, formulanya ialah:
=SUM(C2:C7)
=СУММ(C2:C7)
Dalam sel E8, fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) mencipta pautan ke sel permulaan E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Jika anda memasukkan baris di bahagian atas helaian dan menambah nilai untuk Januari (Jan), maka jumlah dalam lajur C tidak akan berubah. Formula akan berubah, bertindak balas terhadap penambahan baris:
=SUM(C3:C8)
=СУММ(C3:C8)
Walau bagaimanapun, fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) membetulkan E2 sebagai sel mula, jadi Januari dimasukkan secara automatik dalam pengiraan jumlah lajur E. Sel akhir telah berubah, tetapi sel permulaan tidak terjejas.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Contoh 2: Pautan ke julat bernama statik
fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) boleh membuat rujukan kepada julat bernama. Dalam contoh ini, sel biru membentuk julat NumList. Di samping itu, julat dinamik juga dibuat daripada nilai dalam lajur B NumListDyn, bergantung pada bilangan nombor dalam lajur ini.
Jumlah untuk kedua-dua julat boleh dikira dengan hanya memberikan namanya sebagai hujah kepada fungsi SUM (SUM), seperti yang anda lihat dalam sel E3 dan E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Daripada menaip nama julat ke dalam fungsi SUM (SUM), Anda boleh merujuk kepada nama yang ditulis dalam salah satu sel lembaran kerja. Sebagai contoh, jika nama NumList ditulis dalam sel D7, maka formula dalam sel E7 akan menjadi seperti ini:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Malangnya fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) tidak boleh membuat rujukan julat dinamik, jadi apabila anda menyalin formula ini ke dalam sel E8, anda akan mendapat ralat #REF! (#LINK!).
Contoh 3: Buat pautan menggunakan maklumat helaian, baris dan lajur
Anda boleh membuat pautan dengan mudah berdasarkan nombor baris dan lajur, serta menggunakan nilai FALSE (FALSE) untuk hujah fungsi kedua TIDAK LANGSUNG (TIDAK LANGSUNG). Beginilah cara pautan gaya dibuat R1C1. Dalam contoh ini, kami juga menambahkan nama helaian pada pautan – 'Pautan Saya'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Contoh 4: Buat tatasusunan nombor yang tidak berubah
Kadangkala anda perlu menggunakan tatasusunan nombor dalam formula Excel. Dalam contoh berikut, kami ingin purata 3 nombor terbesar dalam lajur B. Nombor boleh dimasukkan ke dalam formula, seperti yang dilakukan dalam sel D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Jika anda memerlukan tatasusunan yang lebih besar, maka anda tidak mungkin mahu memasukkan semua nombor dalam formula. Pilihan kedua ialah menggunakan fungsi tersebut ROW (ROW), seperti yang dilakukan dalam formula tatasusunan yang dimasukkan dalam sel D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Pilihan ketiga ialah menggunakan fungsi tersebut ROW (STRING) bersama-sama dengan TIDAK LANGSUNG (TIDAK LANGSUNG), seperti yang dilakukan dengan formula tatasusunan dalam sel D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Keputusan untuk kesemua 3 formula adalah sama:
Walau bagaimanapun, jika baris disisipkan di bahagian atas helaian, formula kedua akan mengembalikan hasil yang salah disebabkan fakta bahawa rujukan dalam formula akan berubah bersama-sama dengan peralihan baris. Kini, bukannya purata tiga nombor terbesar, formula mengembalikan purata nombor terbesar ke-3, ke-4 dan ke-5.
Menggunakan fungsi TIDAK LANGSUNG (TIDAK LANGSUNG), formula ketiga menyimpan rujukan baris yang betul dan terus menunjukkan hasil yang betul.