Jika lembar kerja Excel Anda menyertakan penghitungan yang didasarkan pada rentang sel yang berubah, gunakan fungsi SUM dan OFFSET bersama-sama dalam rumus SUM OFFSET untuk menyederhanakan tugas agar penghitungan tetap mutakhir.
Petunjuk dalam artikel ini berlaku untuk Excel untuk Microsoft 365, Excel 2019, Excel 2016, Excel 2013, dan Excel 2010.
Membuat Rentang Dinamis Dengan Fungsi SUM dan OFFSET
Jika Anda menggunakan perhitungan untuk periode waktu yang terus berubah - seperti menentukan penjualan untuk bulan tersebut - gunakan fungsi OFFSET di Excel untuk menyiapkan rentang dinamis yang berubah saat angka penjualan setiap hari ditambahkan.
Dengan sendirinya, fungsi SUM biasanya dapat mengakomodasi penyisipan sel data baru ke dalam rentang yang dijumlahkan. Satu pengecualian terjadi ketika data dimasukkan ke dalam sel tempat fungsi tersebut saat ini berada.
Pada contoh di bawah, angka penjualan baru untuk setiap hari ditambahkan di bagian bawah daftar, memaksa total untuk terus-menerus bergeser ke bawah satu sel setiap kali data baru ditambahkan.
Untuk mengikuti tutorial ini, buka lembar kerja Excel kosong dan masukkan data sampel. Lembar kerja Anda tidak perlu diformat seperti contoh, tetapi pastikan untuk memasukkan data dalam sel yang sama.
Jika hanya fungsi SUM yang digunakan untuk menjumlahkan data, rentang sel yang digunakan sebagai argumen fungsi perlu diubah setiap kali data baru ditambahkan.
Dengan menggunakan fungsi SUM dan OFFSET secara bersamaan, range yang dijumlahkan menjadi dinamis dan berubah untuk mengakomodasi sel data baru. Penambahan sel data baru tidak menimbulkan masalah karena rentang terus menyesuaikan setiap sel baru ditambahkan.
Sintaks dan Argumen
Dalam rumus ini, fungsi SUM digunakan untuk menjumlahkan rentang data yang diberikan sebagai argumen. Titik awal untuk rentang ini adalah statis dan diidentifikasi sebagai referensi sel ke angka pertama yang dijumlahkan oleh rumus.
Fungsi OFFSET bersarang di dalam fungsi SUM dan membuat titik akhir dinamis ke rentang data yang dijumlahkan oleh rumus. Ini dilakukan dengan menyetel titik akhir rentang ke satu sel di atas lokasi rumus.
Sintaks rumusnya adalah:
=SUM(Rentang Mulai:OFFSET(Referensi, Baris, Kolom))
Argumennya adalah:
- Range Start: Titik awal untuk rentang sel yang akan dijumlahkan oleh fungsi SUM. Dalam contoh ini, titik awalnya adalah sel B2.
- Referensi: Referensi sel yang diperlukan yang digunakan untuk menghitung titik akhir rentang. Dalam contoh, argumen Referensi adalah referensi sel untuk rumus karena rentang berakhir satu sel di atas rumus.
- Rows: Jumlah baris di atas atau di bawah argumen Referensi yang digunakan dalam menghitung offset diperlukan. Nilai ini bisa positif, negatif, atau disetel ke nol. Jika lokasi offset berada di atas argumen Referensi, nilainya negatif. Jika offset di bawah, argumen Baris positif. Jika offset terletak di baris yang sama, argumennya adalah nol. Dalam contoh ini, offset dimulai satu baris di atas argumen Referensi, jadi nilai argumennya negatif satu (-1).
- Cols: Jumlah kolom di sebelah kiri atau kanan argumen Referensi yang digunakan untuk menghitung offset. Nilai ini bisa positif, negatif, atau disetel ke nol. Jika lokasi offset berada di sebelah kiri argumen Referensi, nilai ini negatif. Jika offset ke kanan, argumen Cols adalah positif. Dalam contoh ini, data yang dijumlahkan berada di kolom yang sama dengan rumus, jadi nilai untuk argumen ini adalah nol.
Gunakan Rumus SUM OFFSET untuk Total Data Penjualan
Contoh ini menggunakan rumus SUM OFFSET untuk mengembalikan total angka penjualan harian yang tercantum di kolom B lembar kerja. Awalnya, rumus dimasukkan ke sel B6 dan dijumlahkan data penjualan selama empat hari.
Langkah selanjutnya adalah memindahkan rumus SUM OFFSET ke bawah satu baris untuk memberi ruang bagi total penjualan hari kelima. Ini dilakukan dengan menyisipkan baris baru 6, yang memindahkan rumus ke baris 7.
Sebagai hasil dari pemindahan, Excel secara otomatis memperbarui argumen Referensi ke sel B7 dan menambahkan sel B6 ke rentang yang dijumlahkan oleh rumus.
- Pilih sel B6, yang merupakan lokasi di mana hasil rumus pertama kali akan ditampilkan.
-
Pilih tab Rumus pada pita.
Image -
Pilih Matematika & Trigonometri.
Image -
Pilih SUM.
Image - Dalam kotak dialog Function Arguments, letakkan kursor di kotak teks Number1.
-
Dalam lembar kerja, pilih sel B2 untuk memasukkan referensi sel ini di kotak dialog. Lokasi ini adalah titik akhir statis untuk rumus.
Image - Dalam kotak dialog Function Arguments, letakkan kursor di kotak teks Number2.
-
Masukkan OFFSET(B6, -1, 0). Fungsi OFFSET ini membentuk titik akhir dinamis untuk rumus.
Image -
Pilih OK untuk menyelesaikan fungsi dan menutup kotak dialog. Total muncul di sel B6.
Image
Tambahkan Data Penjualan Hari Berikutnya
Untuk menambahkan data penjualan hari berikutnya:
- Klik kanan header baris untuk baris 6.
-
Pilih Insert untuk menyisipkan baris baru ke dalam lembar kerja. Rumus SUM OFFSET bergerak turun satu baris ke sel B7 dan baris 6 sekarang kosong.
Image - Pilih sel A6 dan masukkan nomor 5 untuk menunjukkan bahwa total penjualan untuk hari kelima sedang dimasukkan.
-
Pilih sel B6, masukkan $1458.25, lalu tekan Enter.
Image - Pembaruan Sel B7 ke total baru $7137.40.
Saat Anda memilih sel B7, rumus yang diperbarui muncul di bilah rumus.
=SUM(B2:OFFSET(B7, -1, 0))
Fungsi OFFSET memiliki dua argumen opsional: Tinggi dan Lebar, yang tidak digunakan dalam contoh ini. Argumen ini memberi tahu fungsi OFFSET bentuk output dalam hal jumlah baris dan kolom.
Dengan menghilangkan argumen ini, fungsi menggunakan tinggi dan lebar argumen Referensi sebagai gantinya, yang dalam contoh ini adalah tinggi satu baris dan lebar satu kolom.
