1 metodas. Formulės
Apšilimui pradėkime nuo paprasčiausio varianto – formulių. Jei kaip įvestį turime nedidelę lentelę, surūšiuotą pagal datą, tada norint apskaičiuoti einamąją sumą atskirame stulpelyje, mums reikia elementarios formulės:
Pagrindinis bruožas čia yra sudėtingas diapazono fiksavimas funkcijos SUM viduje – nuoroda į diapazono pradžią daroma absoliuti (su dolerio ženklais), o į pabaigą – santykinė (be dolerių). Atitinkamai, nukopijuodami formulę į visą stulpelį, gauname besiplečiantį diapazoną, kurio sumą apskaičiuojame.
Šio metodo trūkumai yra akivaizdūs:
- Lentelė turi būti surūšiuota pagal datą.
- Pridedant naujas eilutes su duomenimis, formulė turės būti išplėsta rankiniu būdu.
2 metodas. Sukamoji lentelė
Šis metodas yra šiek tiek sudėtingesnis, bet daug malonesnis. O kad dar labiau paaštrintų, panagrinėkime rimtesnę problemą – 2000 duomenų eilučių lentelę, kurioje nėra rūšiavimo pagal datos stulpelį, bet yra pasikartojimų (ty galime parduoti kelis kartus tą pačią dieną):
Pradinę lentelę paverčiame „išmaniuoju“ (dinaminiu) sparčiuoju klavišu "Ctrl"+T arba komanda Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę), tada su komanda sukuriame suvestinę lentelę Įterpimas – PivotTable (Įterpti – Suvestinė lentelė). Suvestinėje į eilučių sritį įdedame datą, o verčių srityje – parduotų prekių skaičių:
Atminkite, kad jei turite ne visai seną „Excel“ versiją, datos automatiškai sugrupuojamos pagal metus, ketvirčius ir mėnesius. Jei jums reikia kitokio grupavimo (arba jo visai nereikia), galite jį ištaisyti dešiniuoju pelės klavišu spustelėdami bet kurią datą ir pasirinkę komandas Grupuoti / Išgrupuoti (Grupuoti / išgrupuoti).
Jei atskirame stulpelyje norite matyti gautas sumas pagal taškus ir einamąją sumą, tikslinga lauką mesti į vertės sritį Parduota vėl gauti lauko dublikatą – jame įjungsime einamųjų sumų rodymą. Norėdami tai padaryti, dešiniuoju pelės mygtuku spustelėkite lauką ir pasirinkite komandą Papildomi skaičiavimai – suminė suma (Rodyti reikšmes kaip – einamąsias sumas):
Ten taip pat galite pasirinkti parinktį didinti sumas procentais, o kitame lange reikia pasirinkti lauką, kuriam bus kaupiama – mūsų atveju tai yra datos laukas:
Šio metodo pranašumai:
- Greitai nuskaitomas didelis duomenų kiekis.
- Nereikia rankiniu būdu įvesti formulių.
- Keičiant šaltinio duomenis, pakanka atnaujinti suvestinę dešiniuoju pelės mygtuku arba komanda Data – Refresh All.
Trūkumai išplaukia iš to, kad tai yra santrauka, o tai reiškia, kad joje negalite daryti ko norite (įterpti eilutes, rašyti formules, kurti kokias nors diagramas ir t.t.) nebeveiks.
3 būdas: Power Query
Įkelkime savo „protingą“ lentelę su šaltinio duomenimis į „Power Query“ užklausų rengyklę naudodami komandą Duomenys – iš lentelės/diapazono (Duomenys – iš lentelės/diapazono). Naujausiose „Excel“ versijose, beje, ji buvo pervadinta – dabar vadinasi Su lapais (Iš lapo):
Tada atliksime šiuos veiksmus:
1. Su komanda rūšiuokite lentelę didėjimo tvarka pagal datos stulpelį Rūšiuoti didėjančia tvarka lentelės antraštėje esančiame išskleidžiamajame filtrų sąraše.
2. Šiek tiek vėliau, norint apskaičiuoti einamąją sumą, mums reikia pagalbinio stulpelio su eilės eilės numeriu. Pridėkime su komanda Pridėti stulpelį – rodyklės stulpelį – nuo 1 (Pridėti stulpelį – rodyklės stulpelį – nuo 1).
3. Be to, norint apskaičiuoti einamąją sumą, mums reikia nuorodos į stulpelį Parduota, kur yra mūsų apibendrinti duomenys. Power Query stulpeliai dar vadinami sąrašais (sąrašu) ir norėdami gauti nuorodą į jį, dešiniuoju pelės mygtuku spustelėkite stulpelio antraštę ir pasirinkite komandą Detaliau (Rodyti išsamią informaciją). Reikalinga išraiška bus rodoma formulės juostoje, kurią sudarys ankstesnio veiksmo pavadinimas #„Indeksas pridėtas“, iš kur paimame lentelę ir stulpelio pavadinimą [Pardavimai] iš šios lentelės laužtiniuose skliaustuose:
Nukopijuokite šią išraišką į mainų sritį, kad galėtumėte toliau naudoti.
4. Ištrinkite nereikalingus paskutinius veiksmus Parduota ir vietoj to pridėkite apskaičiuotą stulpelį, skirtą einamajai sumai apskaičiuoti su komanda Stulpelio pridėjimas – pasirinktinis stulpelis (Pridėti stulpelį – tinkintas stulpelis). Mums reikalinga formulė atrodys taip:
Čia funkcija Sąrašas.Apygarda paima pradinį sąrašą (stulpelis [Pardavimas]) ir iš jo ištraukia elementus, pradedant nuo pirmojo (formulėje tai yra 0, nes Power Query numeracija prasideda nuo nulio). Elementų, kuriuos reikia gauti, skaičius yra eilutės numeris, kurį paimame iš stulpelio [Indeksas]. Taigi ši pirmosios eilutės funkcija grąžina tik vieną pirmąjį stulpelio langelį Parduota. Antrai eilutei – jau pirmosios dvi ląstelės, trečiajai – pirmosios trys ir t.t.
Na, tada funkcija Sąrašas.Suma susumuoja išgautas vertes ir kiekvienoje eilutėje gauname visų ankstesnių elementų sumą, ty bendrą sumą:
Belieka ištrinti mums nebereikalingą stulpelį Indeksas ir įkelti rezultatus atgal į Excel su komanda Home – Close & Load to.
Problema išspręsta.
Greiti ir įsiutę
Iš principo tai buvo galima sustabdyti, bet yra maža muselė tepalu – mūsų sukurta užklausa veikia vėžlio greičiu. Pavyzdžiui, mano ne pačiame silpniausiame kompiuteryje tik 2000 eilučių lentelė apdorojama per 17 sekundžių. O jei yra daugiau duomenų?
Norėdami pagreitinti, galite naudoti buferį naudodami specialią funkciją List.Buffer, kuri įkelia jai kaip argumentą pateiktą sąrašą (sąrašą) į RAM, o tai labai pagreitina prieigą prie jo ateityje. Mūsų atveju prasminga saugoti sąrašą #„Added index“[Parduota], kurį „Power Query“ turi pasiekti apskaičiuodama bendrą kiekvienos 2000 eilučių lentelės eilutės sumą.
Norėdami tai padaryti, skirtuke Pagrindinis esančiame Power Query redaktoriuje spustelėkite mygtuką Išplėstinė rengyklė (Pagrindinis – Išplėstinė redaktorius), kad atidarytumėte mūsų užklausos šaltinio kodą M kalba, integruota į Power Query:
Tada pridėkite eilutę su kintamuoju Mano sąrašas, kurios reikšmę grąžina buferio funkcija, o kitame žingsnyje sąrašo iškvietimą pakeičiame šiuo kintamuoju:
Atlikus šiuos pakeitimus, mūsų užklausa taps žymiai greitesnė ir susidoros su 2000 eilučių lentele vos per 0.3 sekundės!
Kitas dalykas, tiesa? 🙂
- Pareto diagrama (80/20) ir kaip ją sukurti programoje Excel
- Raktinių žodžių paieška tekste ir užklausų buferizavimas naudojant Power Query