Skip to main content

Excel SUM dan Formula OFFSET

Excel OFFSET Function for Dynamic Calculations - Explained in Simple Steps (Maret 2024)

Excel OFFSET Function for Dynamic Calculations - Explained in Simple Steps (Maret 2024)
Anonim

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))

  • Mulai Kisaran: Titik awal untuk rentang sel yang akan dihitung oleh fungsi SUM diperlukan. Pada gambar contoh, ini adalah sel B2.
  • Referensi: Referensi sel yang diperlukan digunakan untuk menghitung titik akhir jangkauan terletak banyak baris dan kolom. Pada gambar contoh, argumen Referensi adalah referensi sel untuk rumus itu sendiri karena Anda selalu menginginkan rentang untuk mengakhiri satu sel di atas rumus.
  • Baris: Jumlah baris di atas atau di bawah argumen Referensi yang digunakan dalam menghitung offset diperlukan. Nilai ini bisa positif, negatif, atau diatur ke nol. Jika lokasi offset berada di atas Referensi argumen, nilainya negatif. Jika di bawah, argumen Rows positif. Jika offset terletak di baris yang sama, argumennya nol. Dalam contoh ini, offset dimulai satu baris di atas argumen Referensi, jadi nilai untuk argumen ini adalah negatif (-1).
  • Cols: Jumlah kolom ke kiri atau kanan dari argumen Referensi yang digunakan dalam menghitung offset diperlukan. 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 itu, Excel secara otomatis memperbarui argumen Referensi ke selB7 dan menambahkan selB6 ke kisaran dijumlahkan oleh rumus.

Memasuki Formula SUM OFFSET

  1. Klik pada sel B6, yang merupakan lokasi di mana hasil dari rumus pada awalnya akan ditampilkan.
  2. Klik padaRumus tab dari menu pita.
  3. MemilihMatematika & Trigdari pita untuk membuka daftar drop-down fungsi.
  4. KlikJUMLAH dalam daftar untuk memunculkan kotak dialog fungsi.
  5. Di kotak dialog, klik padaNomor 1baris.
  6. Klik pada sel B2 untuk memasukkan referensi sel ini ke dalam kotak dialog. Lokasi ini adalah titik akhir statis untuk rumus.
  7. Di kotak dialog, klik padaNomor 2baris.
  8. Masukkan fungsi OFFSET berikut: OFFSET (B6, -1,0) untuk membentuk titik akhir dinamis untuk rumus.
  9. Klik baik untuk menyelesaikan fungsi dan menutup kotak dialog.

Total muncul di sel B7.

Menambahkan Data Penjualan Hari Berikutnya

Untuk menambahkan data penjualan hari berikutnya:

  1. Klik kanan pada tajuk baris untuk baris 6 untuk membuka menu konteks.
  2. Di menu, klikMemasukkan untuk memasukkan baris baru ke dalam lembar kerja.
  3. Akibatnya, rumus SUM OFFSET bergerak ke bawah ke sel B7 dan baris 6 sekarang kosong.
  4. Klik pada sel A6.
  5. Masukkan nomor5 untuk menunjukkan bahwa total penjualan untuk hari kelima sedang dimasukkan.
  6. Klik pada sel B6.
  7. Ketik nomornya$1458.25 dan tekan tombolMemasukkan kunci 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.

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.