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 di Excel memungkinkan Anda untuk mengatur rentang dinamis yang berubah karena angka penjualan setiap hari ditambahkan.
Dengan sendirinya, fungsi SUM biasanya dapat mengakomodasi penyisipan sel-sel data baru ke dalam rentang yang dijumlahkan.
Satu pengecualian terjadi ketika data dimasukkan ke dalam sel di mana fungsi saat ini berada.
Dalam contoh gambar di atas, angka penjualan baru untuk setiap hari ditambahkan di bagian bawah daftar, sehingga memaksa total untuk terus menggeser satu sel setiap kali saat 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 di atas 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))
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 itu, Excel secara otomatis memperbarui argumen Referensi ke selB7 dan menambahkan selB6 ke kisaran dijumlahkan oleh rumus. Total muncul di sel B7. Untuk menambahkan data penjualan hari berikutnya: 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. Fungsi OFFSET memiliki dua argumen opsional: Tinggi dan Lebar , yang tidak digunakan dalam contoh ini. Argumen-argumen ini dapat digunakan untuk memberi tahu fungsi OFFSET bentuk output dalam hal begitu banyak baris tinggi dan banyak kolom yang lebar. 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.
Menggunakan SUM OFFSET Formula untuk Total Data Penjualan
Memasuki Formula SUM OFFSET
Menambahkan Data Penjualan Hari Berikutnya