Excel Trick untuk Jumlah Bilangan Sel dengan Julat Variabel

Dalam Excel, anda boleh menjumlahkan sejumlah sel yang menggunakan pelbagai variabel dengan fungsi INDIRECT. Fungsi INDIRECT secara automatik mengemas kini julat sel yang anda rujuk tanpa menyunting formula secara manual itu sendiri. Anda boleh menggunakan fungsi INDIRECT dengan beberapa fungsi Excel, tetapi yang paling umum (dan berguna) adalah apabila anda menggunakan fungsi SUM.

Catatan: Maklumat dalam artikel ini terpakai kepada versi Excel 2019, 2016, 2013, 2010, dan Excel untuk Mac.

01 dari 05

Bagaimana Menyebut Sebilangan Sel Menggunakan Julat Variabel

Anda boleh membenamkan fungsi INDIRECT sebagai hujah di dalam fungsi SUM untuk membuat pelbagai rujukan sel variabel untuk fungsi SUM untuk ditambahkan. Fungsi INDIRECT melakukan ini dengan merujuk rangkaian sel secara tidak langsung, melalui rujukan sel perantaraan.

Format kedua-dua fungsi yang digunakan bersama kelihatan seperti ini:

= SUM (TIDAK LANGSUNG ("D1": D4)

Ini mengunci rangkaian untuk memulakan di D1 dan membolehkan D4 berubah jika anda memasukkan atau memadam mana-mana sel dalam lajur D.

Anda juga boleh menggunakan sel lain dalam hamparan untuk mengubah suai rujukan sel. Sebagai contoh, jika anda menggunakan E1 untuk merujuk sel pertama julat dan E2 untuk merujuk sel terakhir rangkaian, formula seperti ini:

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

Dengan menukar nombor yang terdapat di sel E1 dan E2, anda boleh mengubah suai julat dalam formula tanpa perlu mengedit formula secara manual.

02 dari 05

Cuba SUM dan TIDAK LANGSUNG Fungsi

Buat spreadsheet untuk menguji fungsi SUM dan INDAKAN sendiri. Mulailah dengan membuat hamparan kosong dan masukkan data berikut ke dalam lajur D dan E:

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

Selanjutnya, buat formula dalam sel F1. Ini caranya:

Pilih sel F1. Di sinilah hasil dari contoh ini akan dipaparkan.

Pilih Formula.

Pilih Math & Trig untuk membuka senarai drop-down fungsi.

PilihSUM dalam senarai untuk membuka kotak dialog Fungsi Fungsi SUM.

03 dari 05
Sarang Fungsi INDIRECT ke dalam Fungsi SUM

Seterusnya, masukkan fungsi INDIRECT ke dalam fungsi SUM menggunakan kotak dialog ini.

Di dalam Nombor 1 medan, masukkan fungsi INDIRECT yang berikut:
TIDAK LANGSUNG ("D" & E1 & ": D" & E2)

Pilih okey untuk menyelesaikan fungsi dan menutup kotak dialog.

Nombor 50 muncul di sel F1. Ini adalah jumlah sel D1 hingga D4.

Apabila anda memilih sel F1, medan formula menunjukkan:
= SUM (TIDAK LANGSUNG ("D" & E1 & ": D" & E2))

Apabila anda memahami cara memformat SUM dan bersarang fungsi INDIRECT, anda dapat menaip keseluruhan fungsi yang ditunjukkan dalam langkah 4 terus ke dalam sel hasil (dalam contoh ini, sel F1).
04 dari 05
Bina Argumen untuk Fungsi TIDAK LANGSUNG

Dalam contoh di atas, rujukan kepada sel E1 dan E2 adalah di luar petikan. Ini, digabungkan dengan & melambangkan simbol, memasukkan apa sahaja yang terkandung di dalam sel E1 dan E2 ke dalam formula itu sendiri.

Begini bagaimana unsur formula berfungsi:

& digunakan untuk menyatukan data teks (dalam kes ini huruf D) dengan rujukan sel (E1 dan E2) yang mengandungi nilai nombor.

' ' mesti mengelilingi data teks di dalam rujukan sel (dalam contoh ini, huruf D).

: adalah pemisah antara sel pertama dan sel terakhir dalam julat SUM.

Dalam contoh di atas, titik permulaan julat ditentukan oleh aksara: "D" & E1.

Titik akhir julat ditakrifkan oleh aksara: ": D" & E2

Kolon perlu dimasukkan ke dalam tanda petikan.

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

"D" & E1 & ": D" & E2 05 dari 05
Julat Fungsi Fungsi SUM

Fungsi INDIRECT yang bersarang di dalam fungsi SUM menjadikannya mudah untuk mengubah permulaan dan akhir julat yang berjumlah oleh fungsi SUM, tanpa perlu mengedit fungsi itu sendiri.

Menggunakan teknik penggabungan dalam langkah 3, gunakan nombor yang disimpan dalam sel E1 dan E2 dalam hamparan untuk mengubah julat sel untuk fungsi SUM.

Uji ini dalam spreadsheet anda sendiri dengan mengubah suai sel E1 dan E2 dan tonton bagaimana hasil fungsi SUM berubah.

Pilih sel E1.

Taipkan nombor itu 3.

Tekan Masukkan.

Pilih sel E2.

Taipkan nombor itu 6.

Tekan Masukkan.

Jawapan dalam sel F1 berubah kepada 90. Ini adalah jumlah nombor yang terkandung dalam sel D3 hingga D6.

Untuk melihat fungsi INDIRECT dalam tindakan, masukkan sel baru ke sel D3. Ini mengalihkan sel-sel lain ke bawah. Jumlah baru adalah jumlah sel D3 hingga D7. Fungsi INDIRECT secara dinamik mengubah keseluruhan julat untuk memasukkan keseluruhan senarai yang anda masukkan pada mulanya, ditambah dengan sel baru yang anda masukkan juga.

#REF! Nilai Ralat

#REF! nilai ralat muncul di sel F1 jika fungsi INDIRECT:

Mengandungi rujukan sel tidak sah.

Mengandungi rujukan luar untuk lembaran kerja yang berbeza yang tidak wujud.

Merujuk kepada julat sel di luar had lembaran kerja.

Merujuk kepada sel yang kosong.