Jika lembaran kerja Excel anda termasuk pengiraan yang berdasarkan julat sel yang berubah-ubah, gunakan fungsi SUM dan OFFSET bersama-sama dalam formula SUM OFFSET untuk memudahkan tugas memastikan pengiraan dikemas kini.
Arahan dalam artikel ini digunakan pada Excel untuk Microsoft 365, Excel 2019, Excel 2016, Excel 2013 dan Excel 2010.
Buat Julat Dinamik Dengan Fungsi SUM dan OFFSET
Jika anda menggunakan pengiraan untuk tempoh masa yang sentiasa berubah - seperti menentukan jualan untuk bulan tersebut - gunakan fungsi OFFSET dalam Excel untuk menyediakan julat dinamik yang berubah apabila angka jualan setiap hari ditambah.
Dengan sendirinya, fungsi SUM biasanya boleh menampung sisipan sel baharu data ke dalam julat yang dijumlahkan. Satu pengecualian berlaku apabila data dimasukkan ke dalam sel di mana fungsi itu berada pada masa ini.
Dalam contoh di bawah, angka jualan baharu untuk setiap hari ditambahkan di bahagian bawah senarai, memaksa jumlah tersebut terus beralih ke bawah satu sel setiap kali apabila data baharu ditambahkan.
Untuk mengikuti tutorial ini, buka lembaran kerja Excel kosong dan masukkan data sampel. Lembaran kerja anda tidak perlu diformat seperti contoh, tetapi pastikan anda memasukkan data dalam sel yang sama.
Jika hanya fungsi SUM digunakan untuk menjumlahkan data, julat sel yang digunakan sebagai hujah fungsi perlu diubah suai setiap kali data baharu ditambahkan.
Dengan menggunakan fungsi SUM dan OFFSET bersama-sama, julat yang dijumlahkan menjadi dinamik dan berubah untuk menampung sel data baharu. Penambahan sel data baharu tidak menyebabkan masalah kerana julat terus diselaraskan apabila setiap sel baharu ditambahkan.
Sintaks dan Hujah
Dalam formula ini, fungsi SUM digunakan untuk menjumlahkan julat data yang dibekalkan sebagai hujah. Titik permulaan untuk julat ini adalah statik dan dikenal pasti sebagai rujukan sel kepada nombor pertama yang akan dijumlahkan dengan formula.
Fungsi OFFSET bersarang di dalam fungsi SUM dan mencipta titik akhir dinamik kepada julat data yang dijumlahkan mengikut formula. Ini dicapai dengan menetapkan titik akhir julat kepada satu sel di atas lokasi formula.
Sintaks formula ialah:
=SUM(Julat Mula:OFFSET(Rujukan, Baris, Kol))
Argumennya ialah:
- Mula Julat: Titik permulaan untuk julat sel yang akan dijumlahkan oleh fungsi SUM. Dalam contoh ini, titik permulaan ialah sel B2.
- Rujukan: Rujukan sel yang diperlukan digunakan untuk mengira titik akhir julat. Dalam contoh, hujah Rujukan ialah rujukan sel untuk formula kerana julat menamatkan satu sel di atas formula.
- Rows: Bilangan baris di atas atau di bawah argumen Rujukan yang digunakan dalam mengira offset diperlukan. Nilai ini boleh menjadi positif, negatif atau ditetapkan kepada sifar. Jika lokasi offset berada di atas hujah Rujukan, nilainya adalah negatif. Jika offset berada di bawah, hujah Rows adalah positif. Jika offset terletak dalam baris yang sama, hujahnya adalah sifar. Dalam contoh ini, offset bermula satu baris di atas argumen Rujukan, jadi nilai untuk argumen adalah negatif satu (-1).
- Cols: Bilangan lajur di sebelah kiri atau kanan argumen Rujukan yang digunakan untuk mengira offset. Nilai ini boleh menjadi positif, negatif atau ditetapkan kepada sifar. Jika lokasi offset berada di sebelah kiri hujah Rujukan, nilai ini adalah negatif. Jika offset adalah ke kanan, hujah Cols adalah positif. Dalam contoh ini, data yang dijumlahkan adalah dalam lajur yang sama dengan formula, jadi nilai untuk hujah ini ialah sifar.
Gunakan Formula JUMLAH OFFSET untuk Jumlah Data Jualan
Contoh ini menggunakan formula SUM OFFSET untuk mengembalikan jumlah bagi angka jualan harian yang disenaraikan dalam lajur B lembaran kerja. Pada mulanya, formula telah dimasukkan ke dalam sel B6 dan menjumlahkan data jualan selama empat hari.
Langkah seterusnya ialah mengalihkan formula SUM OFFSET ke bawah satu baris untuk memberi ruang kepada jumlah jualan hari kelima. Ini dicapai dengan memasukkan baris 6 baharu, yang memindahkan formula ke baris 7.
Sebagai hasil daripada perpindahan itu, Excel secara automatik mengemas kini hujah Rujukan ke sel B7 dan menambahkan sel B6 pada julat yang dijumlahkan dengan formula.
- Pilih sel B6, iaitu lokasi di mana keputusan formula akan dipaparkan pada mulanya.
-
Pilih tab Formula pada reben.
Image -
Pilih Math & Trig.
Image -
Pilih SUM.
Image - Dalam kotak dialog Function Arguments, letakkan kursor dalam kotak teks Number1.
-
Dalam lembaran kerja, pilih sel B2 untuk memasukkan rujukan sel ini dalam kotak dialog. Lokasi ini ialah titik akhir statik untuk formula.
Image - Dalam kotak dialog Function Arguments, letakkan kursor dalam kotak teks Number2.
-
Masukkan OFFSET(B6, -1, 0). Fungsi OFFSET ini membentuk titik akhir dinamik untuk formula.
Image -
Pilih OK untuk melengkapkan fungsi dan menutup kotak dialog. Jumlahnya muncul dalam sel B6.
Image
Tambahkan Data Jualan Hari Seterusnya
Untuk menambah data jualan pada hari berikutnya:
- Klik kanan pengepala baris untuk baris 6.
-
Pilih Insert untuk memasukkan baris baharu ke dalam lembaran kerja. Formula SUM OFFSET bergerak ke bawah satu baris ke sel B7 dan baris 6 kini kosong.
Image - Pilih sel A6 dan masukkan nombor 5 untuk menunjukkan bahawa jumlah jualan untuk hari kelima sedang dimasukkan.
-
Pilih sel B6, masukkan $1458.25, kemudian tekan Enter.
Image - Kemas kini sel B7 kepada jumlah baharu sebanyak $7137.40.
Apabila anda memilih sel B7, formula yang dikemas kini muncul dalam bar formula.
=JUMLAH(B2:OFFSET(B7, -1, 0))
Fungsi OFFSET mempunyai dua argumen pilihan: Tinggi dan Lebar, yang tidak digunakan dalam contoh ini. Argumen ini memberitahu fungsi OFFSET bentuk output dari segi bilangan baris dan lajur.
Dengan meninggalkan hujah ini, fungsi menggunakan ketinggian dan lebar hujah Rujukan sebaliknya, yang, dalam contoh ini ialah satu baris tinggi dan satu lajur lebar.
