Gunakan SUM dan OFFSET untuk mencari jumlah untuk julat data dinamik
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 terus berubah - seperti jumlah jualan untuk bulan - fungsi OFFSET membolehkan anda menyediakan rangkaian dinamik yang terus berubah kerana angka penjualan setiap hari ditambah.
Dengan sendirinya, fungsi SUM biasanya dapat menampung sel-sel baru data yang dimasukkan ke dalam jajaran yang dijumlahkan.
Satu pengecualian berlaku apabila data dimasukkan ke dalam sel di mana fungsi sedang berada.
Dalam contoh contoh yang mengiringi artikel ini, angka jualan baru untuk setiap hari ditambah ke bahagian bawah senarai, yang memaksa jumlahnya untuk terus beralih ke satu sel setiap kali 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 yang menyertai artikel ini untuk diikuti bersama dengan 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))Start Range - (diperlukan) titik permulaan bagi julat sel yang akan dianggarkan oleh fungsi SUM. Dalam contoh imej, ini adalah sel B2.
Rujukan - (diperlukan) rujukan sel yang digunakan untuk mengira titik akhir julat yang terdapat banyak baris dan lajur. Dalam contoh imej, hujah Rujukan adalah rujukan sel untuk rumusan itu sendiri kerana kita sentiasa menginginkan rentang untuk menamatkan satu sel di atas formula.
Baris - (diperlukan) bilangan baris di atas atau di bawah hujah Rujukan yang digunakan dalam mengira offset. Nilai ini boleh positif, negatif, atau ditetapkan kepada sifar.
Jika lokasi offset berada di atas argumen Rujukan , nilai ini adalah negatif. Sekiranya di bawah, hujah Baris adalah positif. Sekiranya offset berada di baris yang sama, hujah ini adalah sifar. Dalam contoh ini, offset bermula satu baris di atas argumen Rujukan , jadi nilai untuk hujah ini adalah satu negatif (-1).
Cols - (diperlukan) bilangan lajur ke kiri atau kanan argumen Rujukan yang digunakan dalam mengira offset. 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 supaya 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 sel B7 dan menambah sel B6 ke julat yang disimpulkan oleh formula.
Memasuki Form SUM OFFSET
- Klik pada sel B6, yang merupakan lokasi di mana keputusan formula pada mulanya akan dipaparkan.
- Klik pada tab Rumus pada menu reben .
- Pilih Math & Trig dari reben untuk membuka senarai drop-down fungsi.
- Klik pada SUM dalam senarai untuk memaparkan kotak dialog fungsi.
- Dalam kotak dialog, klik pada nombor Number1 .
- Klik pada sel B2 untuk memasukkan rujukan sel ini ke dalam kotak dialog. Lokasi ini ialah titik akhir statik untuk formula;
- Dalam kotak dialog, klik pada nombor Number2 .
- Masukkan fungsi OFFSET berikut: OFFSET (B6, -1,0) untuk membentuk titik akhir dinamik untuk formula.
- Klik OK untuk menyelesaikan fungsi dan tutup kotak dialog.
Jumlah $ 5679.15 muncul dalam sel B7.
Apabila anda mengklik pada sel B3, fungsi lengkap = SUM (B2: OFFSET (B6, -1,0)) muncul di bar formula di atas lembaran kerja.
Menambah Data Jualan Hari Seterusnya
Untuk menambah data jualan hari berikutnya:
- Klik kanan pada tajuk baris untuk baris 6 untuk membuka menu konteks.
- Dalam menu, klik pada Insert untuk memasukkan baris baru ke dalam lembaran kerja.
- Akibatnya, formula SUM OFFSET bergerak ke bawah ke sel B7 dan baris 6 kini kosong.
- Klik pada sel A6 .
- Masukkan angka 5 untuk menunjukkan bahawa jumlah jualan untuk hari kelima sedang dimasukkan.
- Klik pada sel B6.
- Taipkan nombor $ 1458.25 dan tekan kekunci Enter pada papan kekunci.
Sel B7 mengemas kini jumlah baru sebanyak $ 7137.40.
Apabila anda mengklik pada sel B7, formula yang dikemas kini = SUM (B2: OFFSET (B7, -1,0)) muncul di bar formula.
Nota : Fungsi OFFSET mempunyai dua hujah pilihan: Ketinggian dan Lebar, yang telah ditinggalkan dalam contoh ini.
Argumen-argumen ini boleh digunakan untuk memberitahu fungsi OFFSET bentuk output dari segi itu menjadi begitu banyak baris 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.