Dette er brandmandens budgetproblem. Folk i et brandhus har gjort deres budgetter forkert i Excel. En fantastisk Power Query-transformation giver løsningen.
Se video
- Steve skal sammenfatte numre, der er indtastet i en tekstkolonne
- Der er flere linjer i hver celle, adskilt af alt = "" + Enter
- Brug for at opdele disse linjer i rækker og derefter analysere dollarbeløbet fra midten af hver celle
- Opsummer efter omkostningscenter
- Opret et opslagstabel
- Få totaler fra opslagstabellen ved hjælp af IFNA til at ignorere fejlene i den tomme række
- Bonus: Tilføj en begivenhedsmakro for at opdatere regnearket, når de ændrer en celle.
Videoudskrift
Lær Excel fra, Podcast Episode 2160: SUM-data, der er blevet Alt + indtastet.
Hej. Velkommen tilbage til netcast. Jeg er Bill Jelen. Jeg finder ikke på dette. Jeg har et spørgsmål fra en person, der har data - budgetdata - der ser sådan ud. Nu satte jeg falske ord her, så vi ikke har deres budgetoplysninger, men personens nye i regnskabsafdelingen gik til et firma, og dette firma har i årevis lavet deres budgetter som dette. De er ikke regnskabsførere, der laver budgettet, de er linjefolk, men det er sådan, de har gjort det, og han kan ikke få dem til at ændre sig. Så her er vores mål. Han siger, at dette er lige så slemt som at indtaste budgettet i Word.
Nå næsten, men heldigvis takket være strømforespørgsel vil det redde vores problem. Her er vores mål. For hvert KOSTCENTRUM herover vil vi rapportere det samlede antal af alle disse tal. Så der er udgiftsnavn, et -, rutinemæssigt et -, så et $ -tegn, og så bare for at gøre livet interessant, en gang imellem, en tilfældig note efter; ikke hele tiden, bare noget af tiden. Tom række mellem hver. Tonsvis og masser af data.
Så her er hvad jeg skal gøre. Jeg kommer ned til bunden, den sidste celle, jeg vælger alle disse ting inklusive overskrifterne. Jeg skal oprette et NAVN. Jeg kalder det MyData. MyData, sådan, okay? I orden. Nu skal vi bruge strømforespørgsel, som er gratis i 2010 eller 2013, indbygget i Office 365 i 2016 og 2016. Det kommer fra en TABEL ELLER RANGE. I orden. Først og fremmest, når som helst vi har disse emner i KOLONNE A, alle de NULLER, vi vil slippe af med. Så jeg vil fjerne markeringen i NULL. Fantastisk. Okay. Virkelig, i disse data, i denne version af dataene, fordi jeg skal opbygge en VLOOKUP, har vi ikke brug for denne kolonne. Så jeg skal højreklikke og slippe af med den kolonne, så FJERN kolonnen.
I orden. Nu, her er hvor den freaking magi vil ske. Vælg denne kolonne, SPLIT KOLONNE AF EN DELIMITER, og vi vil helt sikkert gå ind i AVANCERET. Afgrænseren vil være en speciel karakter, og vi vil opdele hver forekomst af afgrænseren. Så her tror jeg, de faktisk allerede har fundet ud af det, fordi jeg udvidede det, men jeg vil vise dig det. INDSÆT SPECIALTEGN. Jeg vil sige, at det er en linjefeedning, okay, så ved hver forekomst af linjefeedningen, og jeg vil splitte i rækker. Okay, og lige hvad der skal ske her er, 1, 2, 3, 4, 5, jeg får 5 rækker, eller jeg siger 1001, men i hver række vil den have en anden linje fra denne celle. Dette er forbløffende. Der er 1, 2, 3, 4, 5, 1001. Okay. Nu skal vi bare analysere denne dårlige dreng. I orden,så vælg den kolonne, SPLIT KOLONNE AF EN DELIMITER. Denne gang vil en afgrænsning være et $ -tegn. Det er perfekt, en gang ved det første $ -tegn, vi finder, i tilfælde af at der er et $ -tegn derude i den fremtidige del. Vi skal splitte i kolonner. Klik på OK. I orden. Så der er detaljer. Her er vores penge.
Nu vil jeg opdele dette på RUMMET. Så vælg denne kolonne, SPLIT KOLONNE AF EN DELIMITER, og afgrænseren bliver et RUM, ja, en gang ved VENSTRE DELIMITERER, klik OK, og jeg har ikke brug for disse kommentarer derude, så de kommentarer vi ' går til FJERN. Faktisk behøver du heller ikke dette, fordi jeg bare prøver at få i alt alt det der, så jeg vil FJERNE.
Transformer nu. GRUPPE FOR KOSTNADSCENTRUM, NYT KOLONNNAVN kaldes I ALT, OPERATIONEN bliver SUM, og hvilken kolonne skal vi SUM? DETALJER 2.1. Smuk. Klik på OK, okay, og det, vi ender med, er en linje pr. KOSTNADSCENTER med SUM i alt disse linjeposter. HJEM, LUK & LAD. Det vil sandsynligvis indsætte et nyt regneark. Jeg håber, det indsætter et nyt regneark, og det gør det, og det regneark hedder MYDATA_1. MYDATA_1.
I orden. Nu kommer vi tilbage her til de originale data og gør disse trin. På den allerførste = VLOOKUP på 1001 i vores resultater. Dette er som at oprette en cirkulær reference, men det vil ikke give os en cirkulær reference. , 2, FALSK. Jeg vil have det nøjagtige match. Okay, men det vil vi ikke gøre for de tomme celler. Så jeg vil sige, ja, lad os bare kopiere det hele vejen ned. CONTROL + C, gå helt ned for at se, hvad vi får. Måske får vi N / As, og jeg kan slippe af med IFNA. Ja, smuk, okay. Så lad os bare slippe af med N / As. Hvis N / A, så vil vi bare have "". Vi vil ikke have noget derinde. CONTROL + ENTER. I orden. Nu skal det være DET TOTALE. Lad os se, om vi kan finde en kort og bare lave matematikken. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, og I ALT 27742,23 er det. Totalt fedt. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), ""))
Her er aftalen. Så vi har de linjemennesker, der er herude og skifter ting, okay, og så lad os sige, at de går igennem, og de ændrer budgettet, 40294.48, og de kommer op her og ændrer denne til 6000, sådan, og de tilføjer en ny, ALT + ENTER, NOGET - $ tegn, $ 1000 lige tilføjet. I orden. Når jeg nu trykker på ENTER, vil dette nummer, 40294.48, naturligvis ikke opdatere, okay, men hvad vi skal gøre er at gå til fanen DATA, og vi vil OPFREMSKE ALLE. Så, 40294.48. Se, se, se, se. OPFRISK ALLE. Freaking fantastisk.
Jeg elsker magtforespørgsel. Strømforespørgsel er den mest fantastiske ting. Disse data, som i det væsentlige er ligesom orddata i en celle, har vi nu opdateret. Du kan sandsynligvis endda lave en slags makro, der siger, at hver gang nogen ændrer noget i KOLONN C, fortsætter vi og klikker på OPFRISK ALLE ved hjælp af makroen og bare har disse resultater konstant, konstant opdaterende.
Hvilket forfærdeligt spørgsmål, der er sendt ind. Jeg har det dårligt for Steve, der skal håndtere dette, men nu har du en meget, meget nem måde at løse dette på ved hjælp af strømforespørgsel i Office 365 eller downloadet til 2010 eller 2013.
Vente. Okay, et tillæg: lad os gøre det endnu bedre. Dette ark hedder DATA, og jeg har gemt projektmappen som makroaktiveret, så xlsm. Hvis du er xlsx, skal du ikke springe over at gemme som xlsm. ALT + F11. Find projektmappen kaldet DATA, dobbeltklik, øverst til venstre, ARBEJDSBLAD, og SKIFT derefter når som helst vi ændrer regnearket, og vi siger ACTIVEWORKBOOK.REFRESHALL, og luk derefter, okay, og lad os nu prøve det. Lad os redigere noget. Så vi tager de hindbær, der i øjeblikket er 8.000, og vi ændrer det til 1000, så vi reducerer med 7000. Når jeg trykker på ENTER, vil jeg se, at 42.000 går ned til 35.000. Ah. Fantastisk.
Nå, hej. Det er her, jeg normalt beder dig om at købe min bog, men i dag vil jeg bede dig om at købe mine venners bog - Ken Puls og Miguel Escobar - M er til (DATA) ABE. Alt hvad jeg lærte om strømforespørgsel, lærte jeg af denne bog. Det er en fantastisk bog. Tjek det ud.
Afslutning af episoden: Steve har tal til sum, der er indtastet i en tekstkolonne; flere linjer i hver celle, adskilt af ALT + ENTER; har brug for at opdele disse linjer i rækker og derefter analysere dollarbeløbet fra midten af hver celle; opsummere af COST CENTER; opbyg et opslagstabel få totaler fra opslagstabellen ved hjælp af IFNA til at ignorere fejlene i den tomme række; og derefter en bonus, makro i slutningen, en begivenhedsmakro til opdatering af regnearket, når de ændrer en celle.
Jeg vil takke Steve for at sende dette spørgsmål, og jeg er så glad for, at jeg har et svar - før strømforespørgsel ville det have været rigtig, virkelig hårdt - og jeg vil gerne takke dig, fordi du kom forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2160.xlsm
