Gunakan SUM dan OFFSET untuk menemukan total untuk rentang data dinamis
Jika lembar kerja Excel Anda menyertakan perhitungan berdasarkan rentang sel yang berubah, menggunakan fungsi SUM dan OFFSET bersama-sama dalam rumus SUM OFFSET menyederhanakan tugas menjaga penghitungan hingga saat ini.
Buat Rentang Dinamis Dengan Fungsi SUM dan OFFSET
Jika Anda menggunakan perhitungan untuk jangka waktu yang terus berubah - seperti total penjualan untuk bulan - fungsi OFFSET memungkinkan Anda untuk mengatur rentang dinamis yang terus berubah karena angka penjualan setiap hari ditambahkan.
Dengan sendirinya, fungsi SUM biasanya dapat mengakomodasi sel-sel baru dari data yang dimasukkan ke dalam rentang yang dijumlahkan.
Satu pengecualian terjadi ketika data dimasukkan ke dalam sel di mana fungsi saat ini berada.
Dalam contoh gambar yang menyertai artikel ini, angka penjualan baru untuk setiap hari ditambahkan ke bagian bawah daftar, yang memaksa total untuk terus menggeser satu sel setiap kali data baru ditambahkan.
Jika fungsi SUM digunakan sendiri untuk total data, perlu untuk mengubah rentang sel yang digunakan sebagai argumen fungsi setiap kali data baru ditambahkan.
Dengan menggunakan fungsi SUM dan OFFSET bersama-sama, bagaimanapun, jangkauan yang dijumlahkan menjadi dinamis. Dengan kata lain, ia berubah untuk mengakomodasi sel-sel data baru. Penambahan sel-sel baru data tidak menimbulkan masalah karena rentang terus menyesuaikan karena setiap sel baru ditambahkan.
Sintaks dan Argumen
Lihat gambar yang menyertai artikel ini untuk mengikuti tutorial ini.
Dalam rumus ini, fungsi SUM digunakan untuk total rentang data yang disediakan sebagai argumennya. Titik awal untuk rentang ini adalah statis dan diidentifikasi sebagai referensi sel ke nomor pertama yang akan dihitung oleh rumus.
Fungsi OFFSET bersarang di dalam fungsi SUM dan digunakan untuk membuat titik akhir dinamis ke kisaran data yang dijumlahkan oleh rumus. Ini dilakukan dengan mengatur titik akhir rentang ke satu sel di atas lokasi rumus.
Sintaks rumusnya:
= SUM (Jangkauan Mulai: OFFSET (Referensi, Baris, Cols))Range Start - (diperlukan) titik awal untuk rentang sel yang akan dihitung oleh fungsi SUM. Pada gambar contoh, ini adalah sel B2.
Referensi - (diperlukan) referensi sel yang digunakan untuk menghitung titik akhir rentang yang terletak banyak baris dan kolom pergi. Pada gambar contoh, argumen Referensi adalah referensi sel untuk rumus itu sendiri karena kami selalu menginginkan rentang untuk mengakhiri satu sel di atas rumus.
Baris - (diperlukan) jumlah baris di atas atau di bawah argumen Referensi yang digunakan dalam menghitung offset. Nilai ini bisa positif, negatif, atau diatur ke nol.
Jika lokasi offset berada di atas argumen Referensi , nilai ini negatif. Jika di bawah, argumen Rows positif. Jika offset terletak di baris yang sama, argumen ini nol. Dalam contoh ini, offset dimulai satu baris di atas argumen Referensi , jadi nilai untuk argumen ini adalah negatif (-1).
Cols - (diperlukan) jumlah kolom ke kiri atau kanan dari argumen Referensi yang digunakan dalam menghitung offset. Nilai ini bisa positif, negatif, atau diatur ke nol
Jika lokasi offset berada di sebelah kiri argumen Referensi , nilai ini negatif. Jika ke kanan, argumen Cols positif. Dalam contoh ini, data yang dijumlahkan berada di kolom yang sama dengan rumus sehingga nilai untuk argumen ini adalah nol.
Menggunakan SUM OFFSET Formula untuk Total Data Penjualan
Contoh ini menggunakan rumus SUM OFFSET untuk mengembalikan total untuk angka penjualan harian yang tercantum di kolom B lembar kerja.
Awalnya, formula dimasukkan ke dalam sel B6 dan total data penjualan selama empat hari.
Langkah selanjutnya adalah memindahkan formula SUM OFFSET ke bawah berturut-turut untuk memberi ruang bagi total penjualan hari kelima.
Ini dilakukan dengan memasukkan baris baru 6, yang memindahkan rumus ke bawah ke baris 7.
Sebagai hasil dari langkah tersebut, Excel secara otomatis memperbarui argumen Referensi ke sel B7 dan menambahkan sel B6 ke kisaran yang dijumlahkan oleh rumus.
Memasuki Formula SUM OFFSET
- Klik pada sel B6, yang merupakan lokasi di mana hasil dari rumus pada awalnya akan ditampilkan.
- Klik pada tab Rumus dari menu pita .
- Pilih Math & Trig dari ribbon untuk membuka daftar drop-down fungsi.
- Klik pada SUM dalam daftar untuk memunculkan kotak dialog fungsi.
- Di kotak dialog, klik pada baris Number1 .
- Klik pada sel B2 untuk memasukkan referensi sel ini ke dalam kotak dialog. Lokasi ini adalah titik akhir statis untuk rumus;
- Di kotak dialog, klik pada baris Number2 .
- Masukkan fungsi OFFSET berikut: OFFSET (B6, -1,0) untuk membentuk titik akhir dinamis untuk rumus.
- Klik OK untuk menyelesaikan fungsi dan menutup kotak dialog.
Total $ 5679.15 muncul di sel B7.
Ketika Anda mengklik sel B3, fungsi lengkap = SUM (B2: OFFSET (B6, -1,0)) muncul di bilah rumus di atas lembar kerja.
Menambahkan Data Penjualan Hari Berikutnya
Untuk menambahkan data penjualan hari berikutnya:
- Klik kanan pada tajuk baris untuk baris 6 untuk membuka menu konteks.
- Di menu, klik pada Sisipkan untuk menyisipkan baris baru ke dalam lembar kerja.
- Akibatnya, rumus SUM OFFSET bergerak ke bawah ke sel B7 dan baris 6 sekarang kosong.
- Klik pada sel A6 .
- Masukkan angka 5 untuk menunjukkan bahwa total penjualan untuk hari kelima sedang dimasukkan.
- Klik pada sel B6.
- Ketik nomor $ 1458.25 dan tekan tombol Enter pada keyboard.
Sel B7 memperbarui ke total baru $ 7137.40.
Ketika Anda mengklik sel B7, rumus diperbarui = SUM (B2: OFFSET (B7, -1,0)) muncul di bilah rumus.
Catatan : Fungsi OFFSET memiliki dua argumen opsional: Tinggi dan Lebar, yang dihilangkan dalam contoh ini.
Argumen-argumen ini dapat digunakan untuk memberitahu fungsi OFFSET bentuk output dalam hal itu menjadi begitu banyak baris tinggi dan banyak kolom yang luas.
Dengan mengabaikan argumen ini, fungsi, secara default, menggunakan tinggi dan lebar dari argumen Referensi, yang, dalam contoh ini adalah satu baris tinggi dan satu kolom lebar.