Gunakake SUM lan OFFSET kanggo nemokake total kanggo data dinamis
Yen lembar kerja Excel kalebu kalkulasi adhedhasar macem-macem sel sing ganti, nggunakake fungsi SUM lan OFFSET bebarengan ing rumus SUM OFFSET sing ndadekake tugas tetep ngitung kalkulasi.
Nggawe Dynamic Range Kanthi fungsi SUM lan OFFSET
Yen nggunakake kalkulasi kanggo periode wektu sing terus-terusan diganti - kayata total penjualan kanggo sasi - fungsi OFFSET ngidini sampeyan nyiyapake sawetara dinamis sing terus ganti amarga saben dodolan dodolan ditambahake.
Dadi, fungsi SUM biasane nyetirake sel-sel data anyar sing disisipake ing sawetara sing summed.
Siji pangecualian ana nalika data kasebut dilebokake ing sèl endi fungsi kasebut saiki.
Ing gambar kayata ing artikel iki, angka-angka dodolan anyar kanggo saben dina ditambahake ing dhaptar ngisor, sing meksa terus-terusan ngalih sel siji saben wektu data anyar ditambahake.
Yen fungsi SUM digunakake dhewe kanggo total data, perlu kanggo ngowahi jangkoan sel digunakake minangka argumen fungsi saben wektu data anyar ditambahake.
Kanthi nggunakake fungsi SUM lan OFFSET bebarengan, sawetara sing dinane dadi dinamis. Kanthi tembung liyane, owah-owahan kanggo nampung data sel anyar. Saliyané sel data anyar ora nyebabake masalah amarga sawetara terus nyetel saben sel anyar ditambahake.
Sintaks lan Argumen
Deleng gambar sing kasengsem karo artikel iki kanggo ngetutake tutorial iki.
Ing formula iki, fungsi SUM digunakake kanggo total jangkoan data sing diwenehake minangka argumen. Titik wiwitan jangkoan iki statis lan diidhentifikasi minangka referensi sel menyang nomer pisanan bakal diklumpukake dening rumus.
Fungsi OFFSET disusun ing jero fungsi SUM lan digunakake kanggo nggawe endpoint dinamis kanggo jangkoan data kanthi rumus. Iki wis rampung kanthi nyetel endpoint saka kisaran menyang siji sel ing ndhuwur lokasi rumus.
Sintaks rumus:
= SUM (Range Start: OFFSET (Referensi, Rows, Cols))Range Start - (dibutuhake) titik wiwitan kanggo jangkoan sel sing bakal diisi dening fungsi SUM. Ing gambar kayata, iki sel B2.
Referensi - (dibutuhake) referensi sèl sing digunakake kanggo ngitung titik endahing jangkoan lan akeh kolom. Ing gambar conto, Argumentasi referensi yaiku referensi sel kanggo rumus kasebut amarga kita tansah pengin jangkoan kanggo ngakhiri siji sel ing ndhuwur rumus.
Rows - (dibutuhake) nomer larik ndhuwur utawa ngisor pitakonan pitakonan digunakake ing ngitung ing nutup. Nilai kasebut bisa positif, negatif, utawa nyetel nol.
Yen lokasi offset luwih dhuwur tinimbang pitakonan Referensi , nilai iki negatif. Yen ing ngisor iki, argumen Rows positif. Yen offset dumunung ing baris sing padha, pitakonan iki kosong. Contone, umpamane, ngimbangake siji baris ing ndhuwur argumentasi Referensi , supaya Nilai kanggo pitakonan iki negatif siji (-1).
Cols - (dibutuhake) jumlah kolom ing sisih kiwa utawa tengen argumen Referensi sing digunakake kanggo ngitung offset. Nilai kasebut bisa positif, negatif, utawa nyetel nol
Yen lokasi offset ing sisih kiwa saka pitakonan Referensi , nilai iki negatif. Yen ing sisih tengen, argumentasi Cols positif. Ing conto iki, data sing diklumpukake ana ing kolom sing padha karo formula supaya nilai kanggo argumen iki kosong.
Nggunakake Formula SUM OFFSET kanggo Data Total Sales
Conto iki migunakake formula SUM OFFSET kanggo ngasilake total angka dodolan saben dina sing didaftar ing kolom B lembar kerja.
Awalipun, rumus kasebut dipasrahake menyang sel B6 lan ngemot data dodolan kanggo patang dina.
Langkah sabanjure yaiku mindhah formula SUM OFFSET mudhun kanggo nggawe kamar kanggo total sales kalima.
Iki wis rampung kanthi nyisipake baris anyar 6, sing ngarahake formula mudhun menyang baris 7.
Minangka asil pamindhahan, Excel kanthi otomatis nganyarake argumentasi Referensi menyang sel B7 lan nambah sel B6 menyang jumlahe sing ditulis kanthi rumus.
Mlebu menyang Formula Formula Sikat
- Klik ing sel B6, yaiku lokasi ing ngendi asil saka formula kasebut bakal pisanan ditampilake.
- Klik tab Rumus saka menu pita .
- Pilih Math & Trig saka pita kanggo mbukak dhaptar gulung fungsi.
- Klik ing SUM ing dhaptar kanggo ngunggahake kothak dialog fungsi.
- Ing kothak dialog, klik ing baris Number1 .
- Klik ing sel B2 kanggo ngetik referensi sel menyang kothak dialog. Lokasi iki minangka titik akhir statis kanggo rumus;
- Ing kothak dialog, klik ing baris Number2 .
- Ketik fungsi OFFSET ing ngisor iki: OFFSET (B6, -1,0) kanggo mbentuk endpoint dinamis kanggo rumus.
- Klik OK kanggo ngrampungake fungsi lan nutup kothak dialog.
Total $ 5679.15 katon ing sel B7.
Nalika sampeyan ngeklik ing sel B3, fungsi lengkap = SUM (B2: OFFSET (B6, -1,0)) katon ing garis rumus ing ndhuwur lembaran.
Nambahake Data Sales ing Sabanjure
Kanggo nambah data dodolan ing dina sabanjure:
- Klik tengen ing baris row for row 6 kanggo mbukak menu konteks.
- Ing menu, klik Insert kanggo masang baris anyar menyang lembar kerja.
- Akibaté, rumus SUM OFFSET dipérang dadi sel B7 lan baris 6 saiki kosong.
- Klik ing sel A6 .
- Ketik nomer 5 kanggo nunjukake yen total dodolan kanggo dina sing kelima lagi dilebokake.
- Klik ing sel B6.
- Ketik nomer $ 1458.25 lan pencet tombol Ketik ing keyboard.
B7 Cell nganyari kanggo total anyar $ 7137.40.
Nalika sampeyan ngeklik ing sel B7, formula sing dianyari = SUM (B2: OFFSET (B7, -1,0)) katon ing garis rumus.
Cathetan : Fungsi OFFSET nduweni rong argumen opsional: Dhuwur lan Width, sing ditolak ing conto iki.
Argumen kasebut bisa digunakake kanggo nemtokake fungsi OFFSET wangun saka output ing syarat-syarat iku supaya akeh larik dhuwur lan akeh kolom sudhut.
Kanthi mbatalake argumen kasebut, fungsi, kanthi standar, nggunakake dhuwur lan jembar argumentasi Referensi tinimbang, ing conto iki minangka siji baris dhuwur lan siji kolom lebar.