Microsoft Excel memiliki beberapa trik keren dan menggunakan rumus jangkauan dinamis SUM dan INDIRECT hanya dua cara untuk dengan mudah memanipulasi data yang Anda miliki.
SUM - Tinjauan Formula Tidak LANGSUNG
Menggunakan fungsi INDIRECT dalam rumus Excel memudahkan untuk mengubah rentang referensi sel yang digunakan dalam rumus tanpa harus mengedit rumus itu sendiri.
INDIRECT dapat digunakan dengan sejumlah fungsi yang menerima referensi sel sebagai argumen seperti fungsi OFFSET dan SUM.
Dalam kasus terakhir, menggunakan INDIRECT sebagai argumen untuk fungsi SUM dapat membuat rentang dinamis referensi sel yang kemudian ditambahkan fungsi SUM.
INDIRECT melakukan ini dengan mengacu pada data dalam sel secara tidak langsung melalui lokasi perantara.
Contoh: SUM - INDIRECT Formula yang digunakan untuk Total Rentang Nilai Dinamis
Contoh ini didasarkan pada data yang ditunjukkan pada gambar di atas.
Rumus SUM - TIDAK LANGSUNG dibuat dengan menggunakan langkah tutorial di bawah ini adalah:
= SUM (TIDAK LANGSUNG ("D" & E1 & ": D" & E2))
Dalam rumus ini, argumen fungsi INDIRECT yang bersarang mengandung referensi ke sel E1 dan E2. Angka-angka dalam sel-sel itu, 1 dan 4, bila dikombinasikan dengan sisa argumen INDIRECT, membentuk referensi sel D1 dan D4.
Akibatnya, kisaran angka yang dijumlahkan oleh fungsi SUM adalah data yang terdapat dalam rentang sel D1 hingga D4 - yang adalah 50.
Dengan mengubah angka yang terletak di sel E1 dan E2; Namun, kisaran yang akan dijumlahkan dapat dengan mudah diubah.
Contoh ini pertama akan menggunakan rumus di atas untuk total data dalam sel D1: D4 dan kemudian mengubah rentang yang dijumlahkan ke D3: D6 tanpa mengedit rumus dalam sel F1.
01 03
Memasuki Formula - Opsi
Opsi untuk memasukkan rumus termasuk:
- mengetik rumus di atas langsung ke sel F1 dan menekan tombol Enter pada keyboard
- memasukkan fungsi INDIRECT sebagai argumen menggunakan kotak dialog fungsi SUM
Sebagian besar fungsi di Excel memiliki kotak dialog, yang memungkinkan Anda untuk memasukkan masing-masing argumen fungsi pada baris terpisah tanpa harus khawatir tentang sintaks .
Dalam hal ini, kotak dialog fungsi SUM dapat digunakan untuk menyederhanakan rumus sampai batas tertentu. Karena fungsi TIDAK LANGSUNG sedang bersarang di dalam SUM, Fungsi INDIRECT dan argumennya harus tetap dimasukkan secara manual.
Langkah-langkah di bawah ini menggunakan kotak dialog SUM untuk memasukkan rumus.
Memasukkan Data Tutorial
Data Sel D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Masukkan data berikut ke dalam sel D1 ke E2
Memulai Formula SUM - TIDAK LANGSUNG - Membuka Kotak Dialog Fungsi SUM
- Klik pada sel F1 - ini adalah di mana hasil dari contoh ini akan ditampilkan
- Klik pada tab Rumus dari menu pita
- Pilih Math & Trig dari ribbon untuk membuka daftar drop-down fungsi
- Klik pada SUM di daftar untuk membuka kotak dialog fungsi
02 03
Memasukkan Fungsi TIDAK LANGSUNG - Klik untuk Melihat Gambar Lebih Besar
Formula INDIRECT perlu dimasukkan sebagai argumen untuk fungsi SUM.
Dalam kasus fungsi bertingkat, Excel tidak mengizinkan membuka kotak dialog fungsi kedua untuk memasukkan argumennya.
Fungsi INDIRECT, oleh karena itu, harus dimasukkan secara manual di baris Number1 dari kotak dialog Fungsi SUM.
- Di kotak dialog, klik pada baris Number1
- Masukkan fungsi INDIRECT berikut: INDIRECT ("D" & E1 & ": D" & E2)
- Klik OK untuk menyelesaikan fungsi dan menutup kotak dialog
- Nomor 50 harus muncul di sel F1 karena ini adalah total untuk data yang terletak di sel D1 ke D4
- Ketika Anda mengklik sel F1 rumus lengkap = SUM (TIDAK LANGSUNG ("D" & E1 & ": D" & E2)) muncul di bilah rumus di atas lembar kerja
Memecahkan Fungsi TIDAK LANGSUNG
Untuk membuat rentang dinamis di kolom D menggunakan INDIRECT, kita harus menggabungkan huruf D dalam argumen fungsi INDIRECT dengan angka-angka yang terdapat dalam sel E1 dan E2.
Ini dicapai dengan hal-hal berikut:
- ampersand ( & ) digunakan untuk menggabungkan atau menggabungkan data teks (dalam hal ini huruf D) dengan referensi sel (E1 dan E2)
- lebih lanjut, data teks yang digabung dengan referensi sel harus dikelilingi oleh tanda kutip ganda ( "" )
- terakhir, titik akhir rentang dipisahkan oleh titik dua (:)
Oleh karena itu, titik awal rentang ditentukan oleh karakter: "D" & E1 .
Kumpulan karakter kedua: ": D" & E2 menggabungkan titik dua dengan titik akhir. Ini dilakukan karena titik dua adalah karakter teks dan, oleh karena itu, harus dimasukkan ke dalam tanda kutip.
Ampersand ketiga di tengah digunakan untuk menggabungkan dua bagian menjadi satu argumen :
"D" & E1 & ": D" & E203 03
Dinamis Mengubah Rentang Fungsi SUM
Inti dari rumus ini adalah untuk membuatnya mudah untuk mengubah rentang yang dijumlahkan oleh fungsi SUM tanpa harus mengedit argumen fungsi.
Dengan memasukkan fungsi INDIRECT dalam rumus, mengubah angka dalam sel E1 dan E2 akan mengubah rentang sel yang dibaca oleh fungsi SUM.
Seperti dapat dilihat pada gambar di atas, ini juga menghasilkan jawaban rumus yang terletak di sel perubahan F1 karena total rentang data baru.
- Klik pada sel E1
- Ketikkan angka 3
- Tekan tombol Enter pada keyboard
- Klik pada sel E2
- Ketikkan angka 6
- Tekan tombol Enter pada keyboard
- Jawaban dalam sel F1 harus berubah menjadi 90 - yang merupakan jumlah total yang terdapat dalam sel D3 hingga D6
- Selanjutnya uji rumus dengan mengubah isi sel B1 dan B2 ke angka mana saja antara 1 dan 6
TIDAK LANGSUNG dan #REF! Nilai Kesalahan
The #REF! nilai kesalahan akan muncul di sel F1 jika argumen fungsi TIDAK LANGSUNG:
- bukan referensi sel yang valid
- berisi referensi eksternal ke buku kerja yang berbeda dan buku kerja itu tidak terbuka
- mengacu pada rentang sel di luar batas-batas lembar kerja (baris 1.048.576 atau kolom XFD)