Cara 1. Rumus
Ayo miwiti, kanggo pemanasan, kanthi pilihan sing paling gampang - rumus. Yen kita duwe tabel cilik sing diurutake miturut tanggal minangka input, banjur kanggo ngetung total mlaku ing kolom sing kapisah, kita butuh rumus dhasar:
Fitur utama ing kene yaiku ndandani kisaran sing angel ing fungsi SUM - referensi kanggo wiwitan kisaran digawe mutlak (kanthi tandha dolar), lan nganti pungkasan - relatif (tanpa dolar). Patut, nalika nyalin rumus mudhun kanggo kabeh kolom, kita entuk sawetara ngembangaken, jumlah kang kita ngetung.
Kerugian saka pendekatan iki jelas:
- Tabel kudu diurutake miturut tanggal.
- Nalika nambahake baris anyar kanthi data, rumus kasebut kudu ditambah kanthi manual.
Cara 2. Tabel pangsi
Cara iki rada rumit, nanging luwih nyenengake. Lan kanggo exacerbate, ayo nimbang masalah sing luwih serius - tabel 2000 larik data, ing ngendi ora ana urut-urutan miturut kolom tanggal, nanging ana repetitions (yaiku, kita bisa adol kaping pirang-pirang ing dina sing padha):
Kita ngowahi tabel asli dadi trabasan keyboard "pinter" (dinamis). Ctrl+T utawa tim Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel), banjur kita mbangun tabel poros ing karo printah Sisipake - PivotTable (Sisipake - Tabel Pivot). Kita sijine tanggal ing area baris ing ringkesan, lan jumlah barang sing didol ing area nilai:
Wigati dimangerteni manawa sampeyan duwe versi Excel sing durung lawas, mula tanggal kasebut kanthi otomatis diklompokake miturut taun, kuartal lan wulan. Yen sampeyan butuh klompok sing beda (utawa ora mbutuhake kabeh), sampeyan bisa ndandani kanthi ngeklik-tengen ing tanggal lan milih printah. Klompok / Ungroup (Grup / Ungroup).
Yen sampeyan pengin ndeleng loro total asil dening periode lan total mlaku ing kolom kapisah, banjur iku ndadekake pangertèn kanggo uncalan lapangan menyang area nilai Sold maneh kanggo njaluk duplikat saka lapangan - ing kita bakal nguripake tampilan total mlaku. Kanggo nindakake iki, klik-tengen ing lapangan banjur pilih printah Petungan Tambahan - Total Kumulatif (Tampilake Nilai minangka - Total Running):
Ing kana, sampeyan uga bisa milih pilihan kanggo nambah total minangka persentase, lan ing jendela sabanjure sampeyan kudu milih lapangan sing bakal ditindakake akumulasi - ing kasus iki, iki minangka lapangan tanggal:
Keuntungan saka pendekatan iki:
- A jumlah gedhe saka data cepet diwaca.
- Ora ana rumus sing kudu diketik kanthi manual.
- Nalika ngganti data sumber, cukup kanggo nganyari ringkesan kanthi tombol mouse tengen utawa kanthi printah Data - Refresh Kabeh.
Kerugian kasebut amarga kasunyatan manawa iki minangka ringkesan, tegese sampeyan ora bisa nindakake apa wae sing dikarepake (garis masang, nulis rumus, nggawe diagram, lsp) ora bakal bisa digunakake maneh.
Cara 3: Query Daya
Ayo mbukak tabel "pinter" karo data sumber menyang editor query Power Query nggunakake printah Data - Saka Tabel / Range (Data — Saka Tabel/Rentang). Ing versi paling anyar saka Excel, kanthi cara, jenenge diganti - saiki diarani Kanthi godhong (Saka Lembar):
Banjur kita bakal nindakake langkah-langkah ing ngisor iki:
1. Urut tabel ing urutan munggah dening kolom tanggal karo printah Urut munggah ing dhaftar gulung-mudhun Filter ing header Tabel.
2. A little mengko, kanggo ngetung total mlaku, kita kudu kolom tambahan karo nomer baris ordinal. Ayo ditambahake karo printah Tambah Kolom - Kolom Indeks - Saka 1 (Tambah kolom - Kolom indeks - Saka 1).
3. Uga, kanggo ngetung total mlaku, kita kudu referensi kanggo kolom Sold, ing ngendi data ringkesan kita dumunung. Ing Power Query, kolom uga disebut dhaptar (dhaptar) lan kanggo njaluk link menyang, klik-tengen ing header kolom banjur pilih printah Detail (Tampilake rincian). Ekspresi sing dibutuhake bakal katon ing garis rumus, sing kalebu jeneng langkah sadurunge # "Indeks ditambahake", saka ngendi kita njupuk tabel lan jeneng kolom [dodolan] saka tabel iki ing kurung kothak:
Salin ekspresi iki menyang clipboard kanggo nggunakake luwih.
4. Mbusak langkah pungkasan sing ora perlu Sold lan nambah tinimbang kolom diwilang kanggo ngitung total mlaku karo printah Nambahake Kolom - Kolom Kustom (Tambah kolom - Kolom khusus). Rumus sing kita butuhake bakal katon kaya iki:
Kene fungsi Daftar. Range njupuk dhaptar asli (kolom [Dodolan]) lan ngekstrak unsur kasebut, diwiwiti saka sing pertama (ing rumus, iki 0, amarga nomer ing Power Query diwiwiti saka nol). Jumlah unsur sing bakal dijupuk yaiku nomer baris sing dijupuk saka kolom [Indeks]. Dadi fungsi iki kanggo baris pisanan mung ngasilake siji sel pisanan saka kolom Sold. Kanggo baris kapindho - wis rong sel pisanan, kanggo katelu - telung pisanan, etc.
Inggih, banjur fungsi Daftar.Sum jumlah nilai sing diekstrak lan kita entuk ing saben baris jumlah kabeh unsur sadurunge, yaiku total kumulatif:
Iku tetep mbusak kolom Index sing ora perlu maneh lan upload asil bali menyang Excel karo Ngarep - Nutup & Muat kanggo printah.
Masalah wis ditanggulangi.
Cepet lan Ngamuk
Ing asas, iki bisa wis mandegake, nanging ana fly cilik ing ointment - panjalukan kita digawe ing kacepetan saka penyu. Contone, ing PC dudu sing paling lemah, tabel mung 2000 baris diproses sajrone 17 detik. Apa yen ana data liyane?
Kanggo nyepetake, sampeyan bisa nggunakake buffering nggunakake fungsi List.Buffer khusus, sing ngemot dhaptar (dhaftar) sing diwenehake minangka argumentasi menyang RAM, sing bakal nyepetake akses menyang ing mangsa ngarep. Ing kasus kita, iku ndadekake pangertèn kanggo buffer dhaftar #"Indeks Added" [Sold], kang Power Query kudu ngakses nalika ngetung total mlaku ing saben baris saka 2000-baris tabel kita.
Kanggo nindakake iki, ing editor Power Query ing tab Utama, klik tombol Editor Lanjut (Ngarep - Editor Lanjut) kanggo mbukak kode sumber pitakon kita ing basa M sing dibangun ing Power Query:
Banjur nambah baris karo variabel ana Daftarku, Nilai sing dibalekake dening fungsi buffering, lan ing langkah sabanjure kita ngganti telpon menyang dhaptar kanthi variabel iki:
Sawise nggawe owah-owahan kasebut, pitakon kita bakal dadi luwih cepet lan bakal ngrampungake tabel 2000 baris sajrone 0.3 detik!
Liyane bab, bener? 🙂
- Bagan pareto (80/20) lan carane mbangun ing Excel
- Panelusuran tembung kunci ing teks lan buffering pitakon ing Power Query