Excel Trik untuk Jumlah Jumlah Sel dengan Rentang Variabel

Di Excel, Anda dapat menjumlahkan sejumlah sel menggunakan rentang variabel dengan fungsi TIDAK LANGSUNG. Fungsi TIDAK LANGSUNG secara otomatis memperbarui rentang sel yang telah Anda rujukan tanpa mengedit secara manual rumus itu sendiri. Anda dapat menggunakan fungsi INDIRECT dengan sejumlah fungsi Excel, tetapi yang paling umum (dan berguna) adalah ketika Anda menggunakan fungsi SUM.

Catatan: Informasi dalam artikel berlaku untuk versi Excel 2019, 2016, 2013, 2010, dan Excel untuk Mac.

01 05

Cara Menghitung Jumlah Sel Menggunakan Rentang Variabel

Anda dapat menanamkan fungsi INDIRECT sebagai argumen di dalam fungsi SUM untuk membuat rentang variabel referensi sel untuk fungsi SUM untuk ditambahkan. Fungsi TIDAK LANGSUNG melakukan hal ini dengan merujuk rentang sel secara tidak langsung, melalui referensi sel menengah.

Format kedua fungsi yang digunakan bersama terlihat seperti ini:

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

Ini mengunci rentang untuk memulai pada D1 dan memungkinkan D4 berubah jika Anda memasukkan atau menghapus sel apa pun di kolom D.

Anda juga dapat menggunakan sel lain di spreadsheet untuk mengubah referensi sel. Misalnya, jika Anda menggunakan E1 untuk mereferensikan sel pertama rentang dan E2 untuk mereferensikan sel terakhir rentang, rumusnya akan terlihat seperti ini:

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

Dengan mengubah angka yang terletak di sel E1 dan E2, Anda dapat mengubah rentang dalam rumus tanpa harus mengedit rumus secara manual.

02 dari 05

Coba fungsi SUM dan TIDAK LANGSUNG

Buat spreadsheet untuk menguji fungsi SUM dan INDIRECT sendiri. Mulailah dengan membuat spreadsheet kosong dan memasukkan data berikut ke dalam kolom D dan E:

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

Selanjutnya, buat rumus di sel F1. Begini caranya:

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

Memilih Rumus.

Memilih Matematika & Trig untuk membuka daftar drop-down fungsi.

MemilihJUMLAH dalam daftar untuk membuka kotak dialog Argumen Fungsi SUM.

03 dari 05
Sarang Fungsi TIDAK LANGSUNG ke dalam Fungsi SUM

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

Dalam Nomor 1 field, masukkan fungsi INDIRECT berikut:
TIDAK LANGSUNG ("D" & E1 & ": D" & E2)

Memilih baik untuk menyelesaikan fungsi dan menutup kotak dialog.

Angka 50 muncul di sel F1. Ini adalah jumlah sel D1 ke D4.

Saat Anda memilih sel F1, bidang rumus menunjukkan:
= SUM (TIDAK LANGSUNG ("D" & E1 & ": D" & E2))

Saat Anda memahami cara memformat SUM dan fungsi INDIRECT yang bersarang, Anda dapat mengetikkan seluruh fungsi yang ditunjukkan pada langkah 4 langsung ke sel hasil (dalam contoh ini, sel F1).
04 dari 05
Bangun Argument untuk Fungsi TIDAK LANGSUNG

Dalam contoh di atas, referensi ke sel E1 dan E2 berada di luar tanda kutip. Ini, dikombinasikan dengan & menggabungkan simbol, menyematkan apa pun yang terkandung di dalam sel E1 dan E2 ke dalam rumus itu sendiri.

Beginilah cara kerja elemen rumus:

& digunakan untuk menggabungkan data teks bersama (dalam hal ini huruf D) dengan referensi sel (E1 dan E2) yang berisi nilai angka.

' ' harus mengelilingi data teks di dalam referensi sel (dalam contoh ini, huruf D).

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

Dalam contoh di atas, titik awal rentang ditentukan oleh karakter: "D" & E1.

Titik akhir rentang ditentukan oleh karakter: ": D" & E2

Kolon perlu dimasukkan ke dalam tanda kutip.

Ampersand ketiga di tengah digunakan untuk menggabungkan dua segmen menjadi satu argumen:

"D" & E1 & ": D" & E2 05 dari 05
Rentang Variabel Fungsi SUM

Fungsi INDIRECT bersarang di dalam fungsi SUM membuatnya mudah untuk mengubah awal dan akhir rentang total oleh fungsi SUM, tanpa harus mengedit fungsi itu sendiri.

Menggunakan teknik penggabungan pada langkah 3, gunakan angka yang disimpan dalam sel E1 dan E2 dalam spreadsheet untuk mengubah rentang sel untuk fungsi SUM.

Uji ini di spreadsheet Anda sendiri dengan memodifikasi sel E1 dan E2 dan perhatikan bagaimana hasil fungsi SUM berubah.

Pilih sel E1.

Ketik nomornya 3.

tekan Memasukkan.

Pilih sel E2.

Ketik nomornya 6.

tekan Memasukkan.

Jawaban dalam sel F1 berubah menjadi 90. Ini adalah jumlah dari angka yang terdapat dalam sel D3 hingga D6.

Untuk melihat fungsi INDIRECT dalam aksi, masukkan sel baru ke dalam sel D3. Ini menggeser semua sel lain ke bawah. Jumlah yang baru adalah total sel D3 ke D7. Fungsi INDIRECT secara dinamis mengubah seluruh rentang untuk memasukkan seluruh daftar yang Anda masukkan pada awalnya, ditambah sel baru yang Anda masukkan juga.

The #REF! Nilai Kesalahan

The #REF! nilai kesalahan muncul di sel F1 jika fungsi TIDAK LANGSUNG:

Berisi referensi sel yang tidak valid.

Berisi referensi eksternal ke lembar kerja berbeda yang tidak ada.

Mengacu pada rentang sel di luar batas-batas lembar kerja.

Mengacu pada sel yang kosong.