Detta är brandmanens budgetproblem. Folket i ett brandhus har gjort sina budgetar fel i Excel. En fantastisk Power Query-transformation ger lösningen.
Kolla på video
- Steve måste summera siffror som har skrivits in i en textkolumn
- Det finns flera rader i varje cell, åtskilda av alt = "" + Enter
- Behöver dela upp dessa rader till rader och analysera sedan dollarbeloppet från mitten av varje cell
- Sammanfatta efter kostnadscenter
- Skapa ett uppslagstabell
- Få totala från uppslagstabellen med IFNA för att ignorera felen i den tomma raden
- Bonus: Lägg till ett händelsemakro för att uppdatera kalkylbladet när de byter cell.
Videoutskrift
Lär dig Excel från, Podcast avsnitt 2160: SUM-data som har blivit Alt + in.
Hallå. Välkommen tillbaka till netcast. Jag heter Bill Jelen. Jag gör inte det här. Jag fick en fråga från någon som har data - budgetdata - som ser ut så här. Nu lägger jag in falska ord här så att vi inte har deras budgetinformation, men personen är ny på redovisningsavdelningen, gick till ett företag, och det här företaget har i flera år gjort sina budgetar så här. De är inte revisorer som gör budgeten, de är linjefolk, men det är så de har gjort det och han kan inte få dem att förändras. Så här är vårt mål. Han säger att detta är lika illa som att skriva in budgeten i Word.
Tja, nästan, men lyckligtvis tack vare kraftfrågan kommer det att rädda vårt problem. Här är vårt mål. För varje KOSTNADSCENTER här, vill vi rapportera summan av alla dessa siffror. Så, det finns utgiftsnamn, ett -, rutinmässigt ett -, sedan ett $ -tecken, och sedan, bara för att göra livet intressant, en gång i taget, en slumpmässig anteckning efter; inte alla gånger, bara en del av tiden. Tom rad mellan var och en. Massor av massor av data.
Så här är vad jag ska göra. Jag kommer ner till botten, den allra sista cellen, jag ska välja alla dessa saker, inklusive rubrikerna. Jag ska skapa ett NAMN. Jag ska kalla det MyData. MyData, så, okej? OK. Nu ska vi använda strömförfrågan som är gratis 2010 eller 2013, inbyggd i 2016 och 2016 Office 365. Det kommer att komma från en TABELL ELLER RANGE. OK. Först och främst, när som helst när vi har dessa tomma i KOLONN A, alla NULLER vi vill bli av med. Så jag ska avmarkera NULL. Grymt bra. Okej. Verkligen, i dessa data, i den här versionen av data, eftersom jag ska bygga en VLOOKUP, behöver vi inte den här kolumnen. Så jag ska högerklicka och bli av med den kolumnen, så ta bort kolumnen.
OK. Nu, här är där den otroliga magin kommer att hända. Välj den här kolumnen, SPLIT COLUMN BY A DELIMITER, och vi kommer definitivt att gå in på ADVANCED. Avgränsaren kommer att vara en speciell karaktär och vi kommer att dela upp varje förekomst av avgränsaren. Så här tror jag att de faktiskt redan har tänkt på det för att jag utvidgade det, men jag ska visa dig. INSÄTTA SPECIALKARAKTER. Jag ska säga att det är en LINE FEED, okej, så vid varje förekomst av LINE FEED, och jag ska SPLITTA IN I RADER. Okej, och precis vad som kommer att hända här är, 1, 2, 3, 4, 5, jag kommer att få 5 rader eller jag ska säga 1001, men i varje rad kommer det att ha en annan linje från den här cellen. Det här är otroligt. Det finns 1, 2, 3, 4, 5, 1001. Okej. Nu behöver vi bara analysera den dåliga pojken. OK,så välj den kolumnen, SPLIT COLUMN BY A DELIMITER. Den här gången kommer en avgränsare att vara ett $ -tecken. Det är perfekt, en gång, vid det första $ -tecknet vi hittar, bara om det finns ett $ -tecken där ute i framtiden. Vi ska SPLITTA I KOLONNER. Klicka på OK. OK. Så det finns detaljer. Här är våra pengar.
Nu ska jag dela upp detta på RUMMET. Så välj den här kolumnen, SPLIT COLUMN BY A DELIMITER, och avgränsaren kommer att bli ett RUM, ja, en gång vid VÄNSTER-DELIMITERaren, klicka på OK, och jag behöver inte dessa kommentarer där ute så de kommentarerna vi kommer att ta bort. Egentligen behöver inte det här heller för jag försöker bara få totalt allt det där, så jag ska ta bort.
Förvandla nu. GRUPP PÅ KOSTNADSCENTRUM, NYTT KOLONNNAMN kommer att hetas TOTALT, VERKSAMHETEN kommer att vara SUMMA, och vilken kolumn ska vi SUMMA? DETALJERNA 2.1. Skön. Klicka på OK, okej, och vad vi slutar med är en rad per KOSTNADSCENTRAL med TOTALT av alla dessa rader. HEM, STÄNG & LADDA. Det kommer antagligen att infoga ett nytt kalkylblad. Jag hoppas att det infogar ett nytt kalkylblad, och det gör det, och det kalkylbladet heter MYDATA_1. MYDATA_1.
OK. Nu ska vi komma tillbaka hit till de ursprungliga uppgifterna och göra dessa steg. På den allra första, = VLOOKUP av 1001 i våra resultat. Det här är ungefär som att ställa in en cirkulär referens men det kommer inte att ge oss en cirkulär referens. , 2, FALSE. Jag vill ha exakt matchning. Okej, men vi kommer inte att vilja göra det för de tomma cellerna. Så jag ska säga, ja, faktiskt, låt oss bara kopiera ner det hela vägen. CONTROL + C, gå hela vägen ner för att se vad vi får. Kanske vi får N / As och jag kan bli av med IFNA. Ja, vackert, okej. Så, låt oss bara bli av med N / As. Om N / A, vill vi bara "". Vi vill inte ha något där inne. CONTROL + ENTER. OK. Nu borde det vara TOTALT. Låt oss se om vi kan hitta en kort och bara göra matte. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, och TOTALT, 27742,23 är det. Riktigt grymt. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), “”))
Nu, här är affären. Så vi har de här linjemänniskorna som är här och byter saker, okej, och så låt oss säga att de går igenom och de ändrar budgeten, 40294.48, och de kommer upp hit och ändrar den här till 6000, så, och de lägger till en ny, ALT + ENTER, NÅGOT - $ tecken, $ 1000 har just lagts till. OK. Nu, naturligtvis, när jag trycker på ENTER kommer det här numret, 40294.48, inte att uppdateras, okej, men vad vi måste göra är att gå till DATA-fliken och vi vill UPPFRISKA ALLA. Så, 40294.48. Titta, titta, titta, titta. UPPFRISKA ALLA. Helt fantastiskt.
Jag älskar kraftfråga. Power query är det mest fantastiska. Dessa data, som i huvudsak är precis som orddata i en cell, har nu uppdaterats. Du kan antagligen till och med göra ett slags makro som säger att varje gång någon ändrar något i KOLONN C, fortsätter vi och klickar på UPPFRISKA ALLA med hjälp av makrot och bara har dessa resultat hela tiden, ständigt uppfriskande.
Vilken hemsk fråga skickades in. Jag känner mig dålig för Steve som måste ta itu med detta, men nu använder du strömförfrågan i Office 365 eller laddas ner för 2010 eller 2013, har du ett mycket, mycket enkelt sätt att lösa detta.
Vänta. Okej, ett tillägg: låt oss göra det ännu bättre. Det här arket heter DATA och jag har sparat arbetsboken som makroaktiverad, så xlsm. Om du är xlsx, hoppa inte över att spara som xlsm. ALT + F11. Hitta arbetsboken som heter DATA, dubbelklicka, uppe till vänster, ARBETSBLAD, och ÄNDRA när som helst när vi ändrar kalkylbladet, och vi kommer att säga ACTIVEWORKBOOK.REFRESHALL, och stäng sedan, okej, och nu ska vi prova. Låt oss redigera något. Så vi tar de hallon som för närvarande är 8 000 och vi ändrar det till 1000, så vi minskar med 7000. När jag trycker på ENTER vill jag se att 42 000 går ner till 35 000. Ah. Grymt bra.
Men Hej. Det är här jag vanligtvis ber dig att köpa min bok, men i dag ska jag be dig att köpa mina vänners bok - Ken Puls och Miguel Escobar - M är för (DATA) APA. Allt jag lärde mig om kraftförfrågan, lärde jag mig från den här boken. Det är en fantastisk bok. Ta en titt på det där.
Avslutning av avsnitt: Steve har siffror att summera som har skrivits in i en textkolumn; flera rader i varje cell, åtskilda av ALT + ENTER; måste dela upp dessa rader till rader och sedan analysera dollarbeloppet från mitten av varje cell; sammanfatta av COST CENTER; bygga en uppslagstabell; få summor från uppslagstabellen, använd IFNA för att ignorera felen i den tomma raden; och sedan en bonus, makro i slutet, ett händelsemakro för att uppdatera kalkylbladet när de byter cell.
Jag vill tacka Steve för att ha skickat in den frågan och jag är så glad att jag har ett svar - innan kraftförfrågan skulle det ha varit riktigt, riktigt tufft - och jag vill tacka dig för att du kom in. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2160.xlsm
