1. metodoa. Formulak
Has gaitezen, berotzeko, aukerarik errazenarekin: formulak. Sarrera gisa dataren arabera ordenatutako taula txiki bat badugu, zutabe bereizi batean kalkulatzeko, oinarrizko formula bat behar dugu:
Hemen ezaugarri nagusia SUM funtzioaren barruko barrutiaren konponketa zaila da - barrutiaren hasierako erreferentzia absolutua egiten da (dolar seinaleekin) eta amaieraraino - erlatiboa (dolarrik gabe). Horren arabera, formula zutabe osora kopiatzean, hedapen-barruti bat lortzen dugu, zeinaren batura kalkulatzen dugun.
Ikuspegi honen desabantailak agerikoak dira:
- Taula dataren arabera ordenatu behar da.
- Datuekin errenkada berriak gehitzean, formula eskuz luzatu beharko da.
2. metodoa. Taula dinamikoa
Metodo hau apur bat konplexuagoa da, baina askoz atseginagoa. Eta areagotzeko, har dezagun arazo larriago bat: 2000 datu-lerrotako taula bat, non ez dagoen data-zutabearen arabera ordenatzen, baina errepikapenak dauden (hau da, hainbat aldiz sal dezakegu egun berean):
Gure jatorrizko taula teklatu lasterbide "adimentsu" (dinamiko) bihurtzen dugu Ctrl+T edo taldea Hasiera - Taula gisa formatu (Hasiera — Taula gisa formatu), eta gero taula dinamiko bat eraikiko dugu komandoarekin Txertatu - Taula dinamikoa (Txertatu — taula dinamikoa). Laburpeneko errenkaden eremuan data jarri dugu eta balioen eremuan saldutako ondasun kopurua:
Kontuan izan Excel-en bertsio zaharrik ez baduzu, datak automatikoki urte, hiruhileko eta hilabeteka multzokatzen direla. Beste talde bat behar baduzu (edo ez baduzu batere behar), orduan konpon dezakezu edozein datatan eskuineko botoiarekin klik eginez eta komandoak hautatuta. Talde / Desagrupar (Taldetu / Destaldekatu).
Emaitzen den guztirakoak puntuen arabera eta guztizko osoa zutabe bereizi batean ikusi nahi badituzu, zentzuzkoa da eremua balio eremura botatzea. Agortuta berriro eremuaren bikoiztua lortzeko - bertan guztirako guztirakoen bistaratzea piztuko dugu. Horretarako, egin klik eskuineko botoiarekin eremuan eta hautatu komandoa Kalkulu osagarriak – Guztira metatua (Erakutsi balioak honela: guztirakoak):
Bertan, guztira, ehuneko gisa hazteko aukera ere hauta dezakezu, eta hurrengo leihoan metaketa zein eremura joango den hautatu behar duzu; gure kasuan, hau da data eremua:
Ikuspegi honen abantailak:
- Datu kopuru handia azkar irakurtzen da.
- Ez da formularik eskuz sartu behar.
- Sorburuko datuak aldatzean, nahikoa da laburpena eguneratzea saguaren eskuineko botoiarekin edo Datuak – Freskatu guztiak komandoarekin.
Desabantailak laburpen bat izatearen ondorio dira, hau da, bertan nahi duzuna egin ezin duzula (lerroak sartu, formulak idatzi, diagramak eraiki, etab.) ez dira gehiago funtzionatuko.
3. metodoa: Power Query
Kargatu dezagun gure taula "adimentsua" iturriko datuekin Power Query kontsulta-editorean komandoa erabiliz Datuak - Taula/Barrutitik (Datuak — Taula/Barrutitik). Excel-en azken bertsioetan, bide batez, izena aldatu zitzaion - orain deitzen zaio Hostoekin (Fitxatik):
Ondoren, urrats hauek egingo ditugu:
1. Ordenatu taula goranzko ordenan data-zutabearen arabera komandoarekin Goranzko ordenatu taulako goiburuko iragazkien goitibeherako zerrendan.
2. Pixka bat geroago, guztizkoa kalkulatzeko, errenkada-zenbaki ordinala duen zutabe laguntzaile bat behar dugu. Gehi dezagun komandoarekin Gehitu zutabea - Indizearen zutabea - 1tik aurrera (Gehitu zutabea — Indizearen zutabea — 1tik aurrera).
3. Gainera, guztira kalkulatzeko, zutabearen erreferentzia bat behar dugu Agortuta, non dauden gure datu laburtuak. Power Query-n, zutabeei zerrendak (zerrenda) deitzen zaie eta esteka bat lortzeko, egin klik eskuineko botoiarekin zutabearen goiburuan eta hautatu komandoa. zehaztuz (Erakutsi xehetasunak). Formula-barran agertuko da behar dugun adierazpena, aurreko urratsaren izenaz osatua #"Aurkibidea gehitu da", hortik taula eta zutabearen izena hartzen ditugu [Salmentak] taula honetatik kortxete artean:
Kopiatu esamolde hau arbelean gehiago erabiltzeko.
4. Ezabatu beharrezkoa ez den azken urratsa Agortuta eta gehitu horren ordez kalkulatutako zutabe bat komandoarekin guztizkoa kalkulatzeko Zutabe bat gehitzea - Zutabe pertsonalizatua (Gehitu zutabea — Zutabe pertsonalizatua). Behar dugun formula honela izango da:
Hemen funtzioa Zerrenda.Barrutia jatorrizko zerrenda hartzen du (zutabea [Salmentak]) eta bertatik elementuak ateratzen ditu, lehenengotik hasita (formulan, hau 0 da, Power Query-n numerazioa zerotik hasten baita). Berreskuratu beharreko elementu kopurua zutabetik hartzen dugun errenkada-zenbakia da [Aurkibidea]. Beraz, lehen errenkadako funtzio honek zutabearen lehen gelaxka bat bakarrik itzultzen du Agortuta. Bigarren lerrorako - lehen bi gelaxkak dagoeneko, hirugarrenerako - lehen hirurak, etab.
Tira, orduan funtzioa Zerrenda.Batura ateratako balioak batzen ditu eta errenkada bakoitzean aurreko elementu guztien batura jasoko dugu, hau da, guztira metatua:
Jada behar ez dugun Indize-zutabea ezabatzea geratzen da eta emaitzak Excel-era kargatu berriro Hasiera - Itxi & Kargatu komandoarekin.
Arazoa konponduta dago.
Azkar eta haserre
Printzipioz, hori gelditu zitekeen, baina ukenduan euli txiki bat dago: guk sortutako eskaerak dortoka baten abiaduran funtzionatzen du. Adibidez, nire ordenagailurik ahulena ez denean, 2000 errenkada baino ez dituen taula bat prozesatzen da 17 segundotan. Eta datu gehiago egongo balira?
Bizkortzeko, buffering-a erabil dezakezu List.Buffer funtzio berezia erabiliz, zeinak argumentu gisa ematen zaion zerrenda (zerrenda) RAMra kargatzen du, eta horrek asko azkartzen du etorkizunean sarbidea. Gure kasuan, zentzuzkoa da #”Gehitutako indizea”[Saldua] zerrenda bufferatzea, zeina atzitu behar duen Power Query-k gure 2000 errenkadako taulako errenkada bakoitzean guztizko exekutiboa kalkulatzean.
Horretarako, Fitxa Nagusiko Power Query editorean, egin klik Editore aurreratua botoian (Hasiera – Editore aurreratua) gure kontsultaren iturburu-kodea Power Query-n integratutako M hizkuntzan irekitzeko:
Eta gero aldagai batekin lerro bat gehitu hor Nire Zerrenda, horren balioa buffering funtzioak itzultzen du, eta hurrengo urratsean zerrendarako deia aldagai honekin ordezkatuko dugu:
Aldaketa hauek egin ondoren, gure kontsulta nabarmen azkarragoa izango da eta 2000 errenkadako taulari aurre egingo dio 0.3 segundotan!
Beste gauza bat, ezta? 🙂
- Pareto diagrama (80/20) eta nola eraiki Excel-en
- Hitz-gakoen bilaketa testuan eta kontsulta-bufferean Power Query-n