Excel SUM dan Formula Julat Dinamik Tidak Sesuai

Microsoft Excel mempunyai beberapa helah yang sejuk dan menggunakan SUM dan formula pelbagai dinamik INDIRECT hanya dua cara untuk mudah memanipulasi data yang anda miliki.

SUM - Tinjauan Formula Tidak Sesuai

Menggunakan fungsi INDIRECT dalam formula Excel menjadikannya mudah untuk menukar rentang rujukan sel yang digunakan dalam formula tanpa perlu mengedit formula itu sendiri.

INDIRECT boleh digunakan dengan beberapa fungsi yang menerima rujukan sel sebagai hujah seperti fungsi OFFSET dan SUM.

Dalam kes yang kedua, menggunakan INDIRECT sebagai argumen untuk fungsi SUM boleh mencipta pelbagai rujukan dinamik sel yang fungsi SUM kemudian menambah.

TIDAK LANGSUNG melakukannya dengan merujuk kepada data dalam sel secara tidak langsung melalui lokasi perantaraan.

Contoh: SUM - TIDAK LANGSUNG Formula digunakan untuk Jumlah Julat Nilai Dinamik

Contoh ini didasarkan pada data yang ditunjukkan dalam imej di atas.

SUM - formula INDERECT yang dibuat dengan menggunakan langkah tutorial di bawah adalah:

= SUM (TIDAK LANGSUNG ("D" & E1 & ": D" & E2))

Dalam formula ini, hujah fungsi INDIRECT bersarang mengandungi rujukan kepada sel E1 dan E2. Nombor-nombor dalam sel tersebut, 1 dan 4, apabila digabungkan dengan hujah INDIRECT yang lain, membentuk rujukan sel D1 dan D4.

Akibatnya, julat bilangan yang dianggarkan oleh fungsi SUM ialah data yang terkandung dalam julat sel D1 hingga D4 - iaitu 50.

Dengan menukar nombor yang terdapat di sel E1 dan E2; Walau bagaimanapun, julat yang boleh dijumlahkan boleh dengan mudah diubah.

Contoh ini akan mula menggunakan rumus di atas untuk jumlah data dalam sel D1: D4 dan kemudian mengubah julat yang dijumlahkan kepada D3: D6 tanpa menyunting formula dalam sel F1.

01 dari 03

Memasuki Formula - Pilihan

Pilihan untuk memasukkan formula termasuk:

• menaip formula di atas terus ke dalam sel F1 dan menekan kekunci Enter pada papan kekunci
• memasuki fungsi INDIRECT sebagai hujah menggunakan kotak dialog fungsi SUM

Kebanyakan fungsi dalam Excel mempunyai kotak dialog, yang membolehkan anda memasukkan setiap hujah fungsi pada baris berasingan tanpa perlu risau tentang sintaks .

Dalam kes ini, kotak dialog fungsi SUM boleh digunakan untuk menyederhanakan formula pada tahap tertentu. Kerana fungsi INDIRECT sedang bersarang di dalam SUM, fungsi INDIRECT dan hujahnya masih harus dimasukkan secara manual.

Langkah-langkah di bawah menggunakan kotak dialog SUM untuk memasukkan formula.

Memasuki Data Tutorial

Data Sel D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. Masukkan data berikut ke dalam sel D1 hingga E2

Bermula SUM - Formula INDIRECT - Membuka Peti Dialog Fungsi SUM

1. Klik pada sel F1 - di sinilah hasil dari contoh ini akan dipaparkan
2. Klik pada tab Rumus pada menu reben
3. Pilih Math & Trig dari reben untuk membuka senarai drop-down fungsi
4. Klik pada SUM dalam senarai untuk membuka kotak dialog fungsi

02 dari 03

Memasuki Fungsi INDIRECT - Klik untuk Lihat Imej yang Lebih Besar

Formula INDIRECT perlu dimasukkan sebagai hujah untuk fungsi SUM.

Dalam kes fungsi bersarang, Excel tidak membenarkan membuka kotak dialog fungsi kedua untuk memasukkan argumennya.

Oleh itu, fungsi INDIRECT mesti dimasukkan secara manual dalam baris Number1 kotak dialog SUM Fungsi.

1. Dalam kotak dialog, klik pada nombor Number1
2. Masukkan fungsi INDIRECT yang berikut: INDIRECT ("D" & E1 & ": D" & E2)
3. Klik OK untuk menyelesaikan fungsi dan tutup kotak dialog
4. Nombor 50 sepatutnya muncul dalam sel F1 kerana ini adalah jumlah untuk data yang terdapat dalam sel D1 hingga D4
5. Apabila anda mengklik pada sel F1, formula yang lengkap = SUM (INDEEPCT ("D" & E1 & ": D" & E2)) muncul di bar formula di atas lembaran kerja

Memecahkan Fungsi Tidak LANGKAH

Untuk membuat rentang dinamik dalam lajur D menggunakan INDIRECT, kita mesti menggabungkan huruf D dalam argumen fungsi INDIRECT dengan nombor yang terdapat dalam sel E1 dan E2.

Ini dicapai oleh yang berikut:

• ampersand ( & ) digunakan untuk menggabungkan atau menyatukan data teks (dalam kes ini huruf D) dengan rujukan sel (E1 dan E2)
• selanjutnya, data teks yang sedang dirangkum dengan rujukan sel mesti dikelilingi oleh tanda petikan ganda ( "" )
• Akhirnya, titik akhir julat dipisahkan oleh kolon (:)

Oleh itu, titik permulaan julat ditentukan oleh aksara: "D" & E1 .

Set aksara kedua: ": D" & E2 menggabungkan kolon dengan titik akhir. Ini dilakukan kerana kolon adalah watak teks dan, oleh itu, mesti dimasukkan di dalam tanda petikan.

Ambang ketiga di tengah digunakan untuk menggabungkan dua bahagian menjadi satu hujah :

"D" & E1 & ": D" & E2

03 dari 03

Secara dinamik Menukar Julat Fungsi SUM

Titik keseluruhan formula ini adalah untuk menjadikannya mudah untuk mengubah julat yang berjumlah oleh fungsi SUM tanpa perlu mengedit hujah fungsi.

Dengan menyertakan fungsi INDIRECT dalam formula, mengubah nombor dalam sel E1 dan E2 akan mengubah julat sel yang dibaca oleh fungsi SUM.

Seperti yang dapat dilihat dalam imej di atas, ini juga menghasilkan jawapan formula yang terdapat dalam sel F1 yang berubah kerana ia menyimpulkan pelbagai data baru.

1. Klik pada sel E1
2. Taip nombor 3
3. Tekan kekunci Enter pada papan kekunci
4. Klik pada sel E2
5. Taip nombor 6
6. Tekan kekunci Enter pada papan kekunci
7. Jawapan dalam sel F1 harus berubah menjadi 90 - iaitu jumlah nombor yang terkandung dalam sel D3 hingga D6
8. Selanjutnya ujian formula dengan mengubah kandungan sel B1 dan B2 ke mana-mana nombor antara 1 dan 6

TIDAK LANGSUNG dan #REF! Nilai Ralat

#REF! nilai ralat akan muncul dalam sel F1 jika hujah fungsi INDIRECT:

• bukan rujukan sel yang sah
• mengandungi rujukan luar untuk buku kerja yang berbeza dan buku kerja itu tidak terbuka
• merujuk kepada julat sel di luar had lembaran kerja (baris 1,048,576 atau lajur XFD)