Excel SUM dan Formula OFFSET

Sekiranya lembaran kerja Excel anda termasuk pengiraan berdasarkan rangkaian sel berubah, menggunakan fungsi SUM dan OFFSET bersama-sama dalam formula SUM OFFSET memudahkan tugas membuat pengiraan terkini.

Buat Julat Dinamik Dengan Fungsi SUM dan OFFSET

Sekiranya anda menggunakan pengiraan untuk tempoh masa yang berubah secara berterusan - seperti jumlah jualan untuk bulan - fungsi OFFSET dalam Excel membolehkan anda menetapkan jarak dinamik yang berubah kerana setiap angka jualan setiap hari ditambah.

Dengan sendirinya, fungsi SUM biasanya dapat menampung penyisipan sel-sel data baru ke dalam jangkauan yang dijumlahkan.

Satu pengecualian berlaku apabila data dimasukkan ke dalam sel di mana fungsi sedang berada.

Dalam imej contoh di atas, angka jualan baru untuk setiap hari ditambah di bahagian bawah senarai, memaksa jumlahnya untuk terus beralih ke satu sel setiap kali apabila data baru ditambah.

Sekiranya fungsi SUM digunakan sendiri dengan jumlah keseluruhan data, perlu mengubah suai rangkaian sel yang digunakan sebagai hujah fungsi setiap kali data baru ditambah.

Dengan menggunakan fungsi SUM dan OFFSET bersama-sama, bagaimanapun, julat yang dianggarkan menjadi dinamik. Dalam erti kata lain, ia berubah untuk menampung sel-sel baru data. Penambahan sel data baru tidak menyebabkan masalah kerana julat terus menyesuaikan setiap sel baru ditambah.

Sintaks dan Argumen

Rujuk kepada imej di atas untuk mengikuti tutorial ini.

Dalam formula ini, fungsi SUM digunakan untuk merangkumi pelbagai data yang dibekalkan sebagai hujahnya. Titik permulaan untuk julat ini adalah statik dan dikenalpasti sebagai rujukan sel kepada nombor pertama yang akan dianggarkan oleh formula.

Fungsi OFFSET bersarang di dalam fungsi SUM dan digunakan untuk membuat titik akhir dinamik untuk julat data yang dianggarkan oleh formula. Ini dicapai dengan menetapkan titik akhir julat ke satu sel di atas lokasi formula.

Sintaks formula:

= SUM (Permulaan Rentang: OFFSET (Rujukan, Rows, Cols))

• Mula Permulaan: Titik permulaan bagi julat sel yang akan dianggarkan oleh fungsi SUM diperlukan. Dalam contoh imej, ini adalah sel B2.
• Rujukan: Rujukan sel yang diperlukan untuk mengira titik hujung julatnya terletak banyak baris dan lajur. Dalam contoh imej, hujah Rujukan adalah rujukan sel untuk formula itu sendiri kerana anda selalu mahu julat untuk menamatkan satu sel di atas formula.
• Baris: Bilangan baris di atas atau di bawah hujah Rujukan yang digunakan dalam mengira offset diperlukan. Nilai ini boleh positif, negatif, atau ditetapkan kepada sifar. Jika lokasi offset di atas Rujukan hujah, nilai itu negatif. Sekiranya di bawah, hujah Baris adalah positif. Jika offset terletak di baris yang sama, argumen adalah sifar. Dalam contoh ini, offset bermula satu baris di atas argumen Rujukan, jadi nilai untuk hujah ini adalah satu negatif (-1).
• Cols: Bilangan lajur ke kiri atau kanan argumen Rujukan yang digunakan dalam mengira offset diperlukan. Nilai ini boleh positif, negatif, atau ditetapkan kepada sifar. Jika lokasi offset di sebelah kiri hujah Rujukan, nilai ini adalah negatif. Jika di sebelah kanan, hujah Cols adalah positif. Dalam contoh ini, data yang dianggarkan berada dalam lajur yang sama dengan formula, jadi nilai untuk hujah ini adalah sifar.

Menggunakan Formula SUM OFFSET untuk Jumlah Jualan Data

Contoh ini menggunakan formula SUM OFFSET untuk mengembalikan jumlah bagi angka jualan harian yang disenaraikan dalam ruang B dalam lembaran kerja.

Pada mulanya, formula itu dimasukkan ke dalam sel B6 dan jumlah data jualan selama empat hari.

Langkah seterusnya adalah untuk memindahkan formula SUM OFFSET ke bawah untuk membuat ruang untuk jumlah jualan kelima hari. Ini dicapai dengan memasukkan baris baru 6, yang memindahkan formula ke baris 7.

Sebagai hasil dari langkah itu, Excel secara automatik mengemas kini hujah Rujukan ke selB7 dan menambah selB6 kepada julat yang dijumlahkan oleh formula.

Memasuki Form SUM OFFSET

1. Klik pada sel B6, yang merupakan lokasi di mana keputusan formula pada mulanya akan dipaparkan.
2. Klik padaFormula tab menu reben.
3. PilihMath & Trigdari reben untuk membuka senarai drop-down fungsi.
4. Klik padaSUM dalam senarai untuk memaparkan kotak dialog fungsi.
5. Dalam kotak dialog, klik padaNombor 1talian.
6. Klik pada sel B2 untuk memasukkan rujukan sel ini ke dalam kotak dialog. Lokasi ini ialah titik akhir statik untuk formula.
7. Dalam kotak dialog, klik padaNumber2talian.
8. Masukkan fungsi OFFSET berikut: OFFSET (B6, -1,0) untuk membentuk titik akhir dinamik untuk formula.
9. Klik okey untuk menyelesaikan fungsi dan menutup kotak dialog.

Jumlahnya muncul dalam sel B7.

Menambah Data Jualan Hari Seterusnya

Untuk menambah data jualan hari berikutnya:

1. Klik kanan pada tajuk baris untuk baris 6 untuk membuka menu konteks.
2. Dalam menu, klik padaMasukkan untuk memasukkan baris baru ke dalam lembaran kerja.
3. Akibatnya, formula SUM OFFSET bergerak ke bawah ke sel B7 dan baris 6 kini kosong.
4. Klik pada sel A6.
5. Masukkan nombor itu5 untuk menunjukkan bahawa jumlah jualan untuk hari kelima sedang dimasukkan.
6. Klik pada sel B6.
7. Taipkan nombor itu$1458.25 dan tekanMasukkan kekunci pada papan kekunci.

Sel B7 mengemas kini jumlah baru$7137.40.

Apabila anda mengklik pada sel B7, formula yang dikemas kini= SUM (B2: OFFSET (B7, -1,0)) muncul di bar formula.

Fungsi OFFSET mempunyai dua hujah pilihan: Ketinggian dan Lebar , yang tidak digunakan dalam contoh ini.

Argumen-argumen ini boleh digunakan untuk memberitahu fungsi OFFSET bentuk output dari segi banyak baris yang tinggi dan banyak lajur yang luas.

Dengan meninggalkan hujah-hujah ini, fungsi, secara lalai, menggunakan ketinggian dan lebar hujah Rujukan sebaliknya, dalam contoh ini adalah satu baris tinggi dan satu lajur lebar.