Métode 1. Rumus
Hayu urang mimitian, pikeun pemanasan, kalayan pilihan pangbasajanna - rumus. Lamun urang boga tabel leutik disusun dumasar titimangsa salaku input, lajeng keur ngitung total ngajalankeun dina kolom misah, urang peryogi rumus dasar:
Fitur utama di dieu nyaéta perbaikan tricky tina rentang di jero fungsi SUM - rujukan ka awal rentang dijieun mutlak (kalawan tanda dollar), sarta nepi ka ahir - relatif (tanpa dollar). Sasuai, nalika nyalin rumus ka sakabéh kolom, urang meunang rentang ngembangna, jumlah nu urang ngitung.
Karugian tina pendekatan ieu atra:
- tabél kudu diurutkeun dumasar titimangsa.
- Nalika nambihan baris anyar sareng data, rumusna kedah diperpanjang sacara manual.
Métode 2. méja pangsi
Metoda ieu saeutik leuwih pajeulit, tapi leuwih pikaresepeun. Jeung exacerbate, hayu urang nganggap masalah anu leuwih serius - tabel 2000 jajar data, dimana teu aya asihan dumasar kolom tanggal, tapi aya pangulangan (ie urang bisa ngajual sababaraha kali dina dinten anu sami):
Urang ngarobah tabel aslina urang kana potong kompas keyboard "pinter" (dinamis). Ctrl+T atawa tim Imah - Format salaku méja (Imah - Format salaku Méja), lajeng urang ngawangun tabel pangsi dina eta kalawan paréntah Selapkeun - PivotTable (Selapkeun - Pivot Table). Urang nempatkeun tanggal di wewengkon baris dina kasimpulan, sarta jumlah barang dijual di wewengkon nilai:
Punten dicatet yén upami anjeun gaduh vérsi Excel anu henteu lami, maka tanggalna otomatis dikelompokkeun dumasar taun, suku sareng bulan. Upami anjeun peryogi grup anu béda (atanapi henteu peryogi pisan), anjeun tiasa ngalereskeunana ku ngaklik katuhu dina tanggal naon waé sareng milih paréntah. Grup / Ungroup (Grup / Ungroup).
Upami anjeun hoyong ningali jumlahna hasilna ku période sareng total jalan dina kolom anu kapisah, maka masuk akal pikeun ngalungkeun sawah kana daérah nilai. dijual deui pikeun meunangkeun duplikat lapangan - di dinya urang bakal ngahurungkeun tampilan total ngajalankeun. Jang ngalampahkeun ieu, klik katuhu dina widang tur pilih paréntah Itungan tambahan - Total kumulatif (Témbongkeun Nilai salaku - Total Ngajalankeun):
Di dinya anjeun ogé tiasa milih pilihan pikeun ngagedekeun jumlahna salaku persentase, sareng dina jandela salajengna anjeun kedah milih lapangan dimana akumulasi bakal angkat - dina kasus urang, ieu mangrupikeun lapangan tanggal:
Kaunggulan tina pendekatan ieu:
- A jumlah badag data gancang dibaca.
- Henteu aya rumus anu kedah diasupkeun sacara manual.
- Nalika ngarobah dina sumber data, éta cukup pikeun ngapdet kasimpulan ku tombol mouse katuhu atawa jeung Data - Refresh Sadaya paréntah.
Karugian nuturkeun kanyataan yén ieu mangrupikeun kasimpulan, anu hartosna anjeun moal tiasa ngalakukeun naon waé anu anjeun pikahoyong (baris selapkeun, nyerat rumus, ngawangun diagram, jsb) moal tiasa dianggo deui.
Métode 3: Query Power
Hayu urang ngamuat tabel "pinter" kami sareng data sumber kana pangropéa pamundut Power Query nganggo paréntah Data - Ti Table / rentang (Data — Tina Tabél/Rentang). Dina versi panganyarna tina Excel, ku jalan kitu, éta diganti ngaranna - ayeuna disebut Kalayan daun (Ti Lambaran):
Teras urang bakal ngalakukeun léngkah-léngkah ieu:
1. Susun tabel dina urutan naek ku kolom tanggal jeung paréntah Urut naek dina daptar turun-handap filter dina lulugu tabel.
2. Sakedap deui, pikeun ngitung total jalan, urang peryogi kolom bantu kalayan nomer baris ordinal. Hayu urang tambahkeun ku paréntah Tambahkeun Kolom - Kolom Indéks - Tina 1 (Tambahkeun kolom - Kolom indéks - Tina 1).
3. Ogé, pikeun ngitung total jalan, urang peryogi rujukan kana kolom dijual, dimana data diringkeskeun urang perenahna. Dina Power Query, kolom disebut ogé daptar (daptar) sareng pikeun kéngingkeun tautan, klik katuhu dina lulugu kolom teras pilih paréntahna. Rincian (Témbongkeun rinci). Babasan anu urang peryogikeun bakal muncul dina bar rumus, anu diwangun ku nami léngkah sateuacana #"Indéks ditambahkeun", Ti mana urang nyandak tabel sarta ngaran kolom [Penjualan] tina tabél ieu dina kurung pasagi:
Salin ekspresi ieu ka clipboard pikeun pamakéan salajengna.
4. Hapus léngkah terakhir anu teu perlu dijual sarta tambahkeun gantina kolom diitung keur ngitung total ngajalankeun kalawan paréntah Nambahkeun Kolom - Kolom Adat (Tambihkeun kolom - Kolom khusus). Rumus anu urang butuhkeun bakal katingali sapertos kieu:
Di dieu fungsina Daptar. Rentang nyokot daptar aslina (kolom [Penjualan]) sarta extracts elemen ti dinya, mimitian ti mimiti (dina rumus, ieu 0, saprak panomeran dina Power Query dimimitian ti enol). Jumlah elemen pikeun dipulut nyaeta jumlah baris urang nyokot tina kolom [Indéks]. Jadi fungsi ieu pikeun baris kahiji ngan mulih hiji sél mimiti kolom dijual. Pikeun baris kadua - geus dua sél munggaran, pikeun katilu - tilu kahiji, jsb.
Muhun, lajeng fungsi Daptar.Sum nyimpulkeun nilai-nilai anu diekstrak sareng kami nampi dina unggal baris jumlah sadaya elemen sateuacana, nyaéta total kumulatif:
Tetep ngahapus kolom Indéks anu urang henteu peryogi deui sareng unggah hasil deui ka Excel nganggo Imah - Tutup & Beban pikeun paréntah.
Masalahna direngsekeun.
Gancang tur ngamuk
Sacara prinsip, ieu bisa geus dieureunkeun, tapi aya laleur leutik dina salep nu - pamundut urang dijieun jalan dina laju kuya. Salaku conto, dina PC kuring sanés anu paling lemah, méja ngan ukur 2000 baris diolah dina 17 detik. Kumaha upami langkung seueur data?
Pikeun nyepetkeun, anjeun tiasa nganggo buffering ngagunakeun fungsi List.Buffer husus, nu beban daptar (daptar) dibikeun ka eta salaku argumen kana RAM, nu greatly speeds up aksés ka hareup. Dina kasus urang, masuk akal pikeun nyanggakeun daptar #"Indéks Ditambah" [Dijual], anu kedah diaksés ku Power Query nalika ngitung total jalan dina unggal baris tabel 2000-baris kami.
Jang ngalampahkeun ieu, dina pangropéa Power Query dina tab Utama, klik tombol Éditor Maju (Imah – Éditor Maju) pikeun muka kodeu sumber pamundut urang dina basa M anu diwangun dina Power Query:
Lajeng nambahkeun hiji garis kalawan variabel dinya Daptar Abdi, nilai anu dipulangkeun ku fungsi panyangga, sareng dina lengkah satuluyna urang ngagentos sauran kana daptar ku variabel ieu:
Sanggeus nyieun parobahan ieu, query urang bakal jadi nyata gancang sarta bakal Cope jeung tabel 2000-baris dina ngan 0.3 detik!
Lain hal, bener? 🙂
- Bagan pareto (80/20) sareng cara ngawangunna dina Excel
- Milarian kecap konci dina téks sareng panyangga kueri dina Power Query