Dette er brannmannens budsjettproblem. Menneskene i et brannhus har gjort budsjettene sine gale i Excel. En fantastisk Power Query-transformasjon gir løsningen.
Se på video
- Steve må oppsummere tall som er lagt inn i en tekstkolonne
- Det er flere linjer i hver celle, atskilt med alt = "" + Enter
- Trenger å dele disse linjene i rader, og deretter analysere dollarbeløpet fra midten av hver celle
- Oppsummer etter kostnadssenter
- Bygg et oppslagstabell
- Få totaler fra oppslagstabellen, og bruk IFNA til å ignorere feilene i den tomme raden
- Bonus: Legg til en hendelsesmakro for å oppdatere regnearket når de endrer en celle.
Videoutskrift
Lær Excel fra, Podcast Episode 2160: SUM Data That's Been Alt + Entered.
Hei. Velkommen tilbake til netcast. Jeg er Bill Jelen. Jeg finner ikke på dette. Jeg fikk et spørsmål fra noen som har data - budsjettdata - som ser slik ut. Nå la jeg falske ord inn her, slik at vi ikke har budsjettinformasjonen deres, men personen er ny i regnskapsavdelingen, gikk til et selskap, og dette selskapet har i flere år gjort budsjettene sine som dette. De er ikke regnskapsførere som gjør budsjettet, de er linjefolk, men det er slik de har gjort det, og han kan ikke få dem til å endre seg. Så her er målet vårt. Han sier dette er like ille som å skrive budsjettet i Word.
Vel, nesten, men heldigvis, takket være strømforespørsel, vil det redde problemet vårt. Her er målet vårt. For hvert KOSTSENTRUM her borte, vil vi rapportere summen av alle disse tallene. Så det er utgiftsnavn, et -, rutinemessig et -, så et $ -tegn, og så, bare for å gjøre livet interessant, en gang i blant, en tilfeldig note etterpå; ikke hele tiden, bare noen ganger. Tom rad mellom hver. Tonnevis og tonnevis av data.
Så her er hva jeg skal gjøre. Jeg kommer til å komme helt til bunns, den aller siste cellen, jeg skal velge alt dette, inkludert overskriftene. Jeg skal lage et NAVN. Jeg skal kalle det MyData. MyData, sånn, ok? Ok. Nå skal vi bruke strømforespørsel som er gratis i 2010 eller 2013, innebygd i Office 365 i 2016 og 2016. Det kommer fra en TABELL ELLER OMRÅDE. Ok. Først når som helst når vi har de tomme feltene i KOLONN A, alle NULLER vi vil bli kvitt. Så jeg skal fjerne merket for NULL. Rått. Greit. Virkelig, i disse dataene, i denne versjonen av dataene, fordi jeg skal bygge en VLOOKUP, trenger vi ikke denne kolonnen. Så jeg skal høyreklikke og bli kvitt den kolonnen, så FJERN kolonnen.
Ok. Nå, her er den freaking magien kommer til å skje. Velg denne kolonnen, SPLIT KOLONN AV EN DELIMITER, så kommer vi definitivt til å gå videre til AVANSERT. Avgrenseren kommer til å være en spesiell karakter, og vi skal dele hver forekomst av avgrenseren. Så her, jeg tror de faktisk allerede har funnet ut av det fordi jeg utvidet det, men jeg skal vise deg det. Sett inn spesialtegn. Jeg skal si at det er en linjefôring, ok, så ved hver forekomst av linjefôringen, og jeg skal splitte i rader. Greit, og akkurat det som kommer til å skje her er, 1, 2, 3, 4, 5, jeg skal få 5 rader eller jeg skal si 1001, men i hver rad vil det ha en annen linje fra denne cellen. Dette er utrolig. Det er 1, 2, 3, 4, 5, 1001. OK. Nå trenger vi bare å analysere denne dårlige gutten. Ok,så velg den kolonnen, SPLIT KOLONN AV EN DELIMITER. Denne gangen vil en skilletegn være et $ -tegn. Det er perfekt en gang ved det første $ -tegnet vi finner, bare i tilfelle det er et $ -tegn der ute i den fremtidige delen. Vi skal SPLITTE I KOLONNER. Klikk OK. Ok. Så det er detaljer. Her er pengene våre.
Nå skal jeg dele dette på RUM. Så velg denne kolonnen, SPLIT KOLONNE AV EN DELIMITER, og skilletegnet blir et mellomrom, ja, en gang ved VENSTRE DELIMITERER, klikk OK, og jeg trenger ikke de kommentarene der ute, så kommentarene skal du FJERNE. Trenger faktisk ikke dette heller fordi jeg bare prøver å få totalt alt det, så jeg skal FJERNE.
Transformer deg nå. GRUPPE FOR KOSTNADSSENTER, NYTT KOLONNNAVN skal hetes TOTAL, OPERASJONEN blir SUM, og hvilken kolonne skal vi SUM? DETALJENE 2.1. Vakker. Klikk OK, ok, og det vi ender med er én linje per KOSTNADSSENTER med TOTALT av alle disse ordrelinjene. HJEM, Lukk og last. Det kommer sannsynligvis til å sette inn et nytt regneark. Jeg håper det setter inn et nytt regneark, og det gjør det, og det regnearket heter MYDATA_1. MYDATA_1.
Ok. Nå skal vi komme tilbake til de originale dataene og gjøre disse trinnene. På den aller første = VLOOKUP på 1001 i resultatene våre. Dette er som å sette opp en sirkulær referanse, men det vil ikke gi oss en sirkulær referanse. , 2, FALSE. Jeg vil ha nøyaktig samsvar. Greit, men vi vil ikke ønske å gjøre det for de tomme cellene. Så jeg skal si, vel, faktisk, la oss bare kopiere det ned hele veien. CONTROL + C, gå helt ned for å se hva vi får. Kanskje vi får N / As, og jeg kan bli kvitt det med IFNA. Ja, vakkert, ok. Så la oss bare kvitte oss med N / As. Hvis ikke, vil vi bare ha "". Vi vil ikke ha noe der inne. CONTROL + ENTER. Ok. Nå, det burde være TOTALT. La oss se om vi kan finne en kort og bare gjøre matte. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, og TOTALT, 27742,23 er det. Råkult. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), “”))
Nå, her er avtalen. Så vi har de linjemennene som er her og endrer ting, ok, så la oss si at de går gjennom og de endrer budsjettet, 40294.48, og de kommer opp hit og endrer denne til 6000, slik, og de legger til en ny, ALT + ENTER, NOE - $ tegn, $ 1000 nettopp lagt til. Ok. Nå, selvfølgelig, når jeg trykker ENTER, kommer ikke dette nummeret, 40294.48, til å oppdateres, ok, men det vi må gjøre er å gå til DATA-fanen, og vi vil OPPFYRE ALLE. Så, 40294.48. Se, se, se, se. OPPFYR ALLE. Freaking fantastisk.
Jeg elsker kraftforespørsel. Power query er det mest fantastiske. Disse dataene, som egentlig er akkurat som orddata i en celle, har vi nå oppdatert. Du kan sannsynligvis til og med lage en slags makro som sier at hver gang noen endrer noe i KOLONN C, fortsetter vi og klikker på OPPFYR ALLE ved hjelp av makroen og bare har disse resultatene kontinuerlig, kontinuerlig forfriskende.
For et fryktelig spørsmål sendt inn. Jeg føler meg dårlig for Steve som må takle dette, men nå, ved å bruke strømforespørsel i Office 365 eller lastet ned for 2010 eller 2013, har du en veldig, veldig enkel måte å løse dette på.
Wait. Okay, an addendum: let's make it even better. This sheet is called DATA and I've saved the workbook as macro-enabled, so xlsm. If you’re xlsx, don't skip saving as xlsm. ALT+F11. Find the workbook called DATA, double-click, top left, WORKSHEET, and then CHANGE anytime we change the worksheet, and we're going to say ACTIVEWORKBOOK.REFRESHALL, and then close, alright, and now let's try it. Let's edit something. So, we'll take those raspberries which are currently 8,000 and we’ll change it to 1000, so we're reducing by 7000. When I press ENTER, I want to see that 42,000 go down to 35,000. Ah. Awesome.
Vel hei. Det er her jeg vanligvis ber deg om å kjøpe boken min, men i dag skal jeg be deg om å kjøpe vennenes bok - Ken Puls og Miguel Escobar - M er for (DATA) ABE. Alt jeg lærte om kraftforespørsel, lærte jeg fra denne boka. Det er en fantastisk bok. Sjekk det ut.
Episodeavslutning: Steve har tall å oppsummere som er skrevet inn i en tekstkolonne; flere linjer i hver celle, atskilt med ALT + ENTER; trenger å dele disse linjene i rader, og deretter analysere dollarbeløpet fra midten av hver celle; oppsummer av COST CENTER; bygge et oppslagstabell; få totaler fra oppslagstabellen, ved å bruke IFNA til å ignorere feilene i den tomme raden; og deretter en bonus, makro på slutten, en hendelsesmakro som oppdaterer regnearket når de endrer en celle.
Jeg vil takke Steve for at han sendte det spørsmålet inn, og jeg er så glad for at jeg har et svar - før strømforespørsel, ville det vært veldig, veldig tøft - og jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2160.xlsm
