To je problém rozpočtu hasičů. Lidé v hasičské zbrojnici dělají v aplikaci Excel své rozpočty špatně. Řešení poskytuje úžasná transformace Power Query.
Sledovat video
- Steve musí sečíst čísla, která byla zadána do textového sloupce
- V každé buňce je několik řádků oddělených alt = "" + Enter
- Je třeba rozdělit tyto řádky na řádky a poté analyzovat částku dolaru ze středu každé buňky
- Shrnutí podle nákladového střediska
- Vytvořte vyhledávací tabulku
- Získejte součty z vyhledávací tabulky pomocí IFNA k ignorování chyb v prázdném řádku
- Bonus: Přidejte makro události a aktualizujte list, když změní buňku.
Přepis videa
Learn Excel from, Podcast Episode 2160: SUM Data That's Been Alt + Entered.
Ahoj. Vítejte zpět na netcastu. Jsem Bill Jelen. To si nevymýšlím. Dostal jsem otázku od někoho, kdo má data - rozpočtová data - která vypadá takto. Nyní jsem sem vložil falešná slova, abychom neměli informace o jejich rozpočtu, ale osoba, která je v účetním oddělení nová, šla do společnosti a tato společnost roky dělá své rozpočty takhle. Nejsou to účetní, kteří dělají rozpočet, jsou to přímí lidé, ale je to tak, jak to dělají, a on je nemůže přimět ke změně. Takže, tady je náš cíl. Říká, že je to stejně špatné jako psaní rozpočtu do Wordu.
No, téměř, ale naštěstí to díky dotazu na sílu zachrání náš problém. Tady je náš cíl. Pro každé COST CENTER zde, chceme nahlásit součet všech těchto čísel. Existuje tedy název výdajů, a -, běžně a -, pak znak $, a pak, jen aby byl život zajímavý, jednou za čas, náhodná nota za; ne vždy, jen občas. Mezi každým je prázdný řádek. Tuny a tuny dat.
Takže, tady budu dělat. Půjdu dolů na samé dno, úplně poslední buňku, vyberu všechny tyto věci, včetně nadpisů. Chystám se vytvořit JMÉNO. Říkám tomu MyData. MyData, takhle, dobře? V pořádku. Nyní použijeme power query, který je zdarma v letech 2010 nebo 2013, zabudovaný do 2016 a 2016 Office 365. To bude pocházet z TABULKY NEBO ROZSAHU. V pořádku. První věc, kdykoli máme ty prázdné mezery ve SLOUPCI A, všechny NULY, kterých se chceme zbavit. Takže zruším zaškrtnutí NULL. Skvělý. Dobře. Opravdu, v těchto datech, v této verzi dat, protože budu vytvářet VLOOKUP, tento sloupec nepotřebujeme. Takže kliknu pravým tlačítkem a zbavím se tohoto sloupce, takže REMOVE sloupec.
V pořádku. Tady je místo, kde se děje šílená magie. Vyberte tento sloupec SPLIT COLUMN BY A DELIMITER a my určitě půjdeme do ADVANCED. Oddělovač bude speciální znak a každý výskyt oddělovače rozdělíme. Takže tady si myslím, že na to už přišli, protože jsem to rozšířil, ale ukážu vám to. VLOŽTE ZVLÁŠTNÍ CHARAKTER. Řeknu, že je to LINE FEED, dobře, takže při každém výskytu LINE FEEDu se chystám SPLIT INTO ROWS. Dobře, a to, co se tady stane, je 1, 2, 3, 4, 5, dostanu 5 řádků nebo řeknu 1001, ale v každém řádku to bude mít jiný linka z této buňky. To je úžasné. K dispozici je 1, 2, 3, 4, 5, 1001. Dobře. Teď už musíme toho zlého chlapce analyzovat. V pořádku,tak vyberte ten sloupec, SPLIT COLUMN BY A DELIMITER. Tentokrát bude oddělovač znakem $. To je perfektní, jednou, při prvním znaku $, který najdeme, pro případ, že by tam byl znak $ v budoucí části. Jdeme na SPLIT DO SLOUPCŮ. Klikněte na OK. V pořádku. Existují tedy podrobnosti. Tady jsou naše peníze.
Teď to rozdělím na VESMÍRU. Takže vyberte tento sloupec, SPLIT COLUMN BY A DELIMITER, a oddělovač bude MEZERNÍK, ano, jednou u LEVĚ NEJVĚTŠÍHO ODDĚLOVAČE, klikněte na OK a tyto komentáře tam nepotřebuji, takže ty komentáře my ' se chystám ODSTRANIT. Vlastně to také nepotřebuji, protože se jen snažím získat celkem všechny ty věci, takže se ODSTRÁNÍM.
Nyní se transformujte. SKUPINA PODLE NÁKLADNÍHO CENTRA, NOVÉ JMÉNO SLOUPCE se bude jmenovat CELKEM, OPERACE bude SOUČET a do kterého sloupce se dostaneme? PODROBNOSTI 2.1. Krásná. Klikněte na OK, dobře, a nakonec skončíme s jedním řádkem na CENTRUM NÁKLADŮ s CELKEM všech těchto řádkových položek. HOME, CLOSE & LOAD. Pravděpodobně vloží nový list. Doufám, že vloží nový list, a to udělá, a ten list se jmenuje MYDATA_1. MYDATA_1.
V pořádku. Nyní se vrátíme k původním datům a provedeme tyto kroky. Úplně první = VLOOKUP 1001 do našich výsledků. Je to něco jako nastavení kruhového odkazu, ale kruhový odkaz nám to neposkytne. , 2, FALSE. Chci přesnou shodu. Dobře, ale nebudeme to chtít udělat pro prázdné buňky. Takže řeknu, no, vlastně si to celé zkopírujme. CONTROL + C, jděte úplně dolů, abyste viděli, co dostáváme. Možná dostáváme N / As a můžu se toho zbavit pomocí IFNA. Jo, krásně, dobře. Pojďme se tedy zbavit N / As. Pokud N / A, pak chceme jen „“. Nechceme tam nic. CONTROL + ENTER. V pořádku. To by mělo být CELKEM. Uvidíme, jestli najdeme krátkou a uděláme matematiku. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94 a CELKEM 27742,23 to je. Sakra parádní. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), „“)))
Tady je dohoda. Takže máme lidi z řad, kteří tady mění věci, dobře, a tak řekněme, že projdou a změní rozpočet, 40294,48, a přijdou sem a změní tohle na 6000, a tak přidají nový, ALT + ENTER, NĚCO - znak $, $ 1000 právě přidáno. V pořádku. Samozřejmě, když stisknu ENTER, toto číslo, 40294.48, se neaktualizuje, dobře, ale to, co musíme udělat, je přejít na kartu DATA a chceme REFRESH ALL. Takže 40294,48. Sledujte, sledujte, sledujte, sledujte. OSVĚŽTE VŠE. Je to úžasné.
Miluji moc dotaz. Moc dotaz je ta nejúžasnější věc. Tato data, která jsou v podstatě stejná jako slovní data v buňce, nyní provádíme jejich aktualizaci. Pravděpodobně byste dokonce mohli vytvořit nějaký druh makra, které říká, že pokaždé, když někdo něco změní ve sloupci C, pokračujeme a klikneme na REFRESH ALL pomocí makra a tyto výsledky budeme mít neustále, neustále osvěžující.
Jaká hrozná otázka byla zaslána. Cítím se špatně pro Steva, který se s tím musí vypořádat, ale nyní, pomocí dotazu na moc v Office 365 nebo staženého pro rok 2010 nebo 2013, máte velmi, velmi snadný způsob, jak to vyřešit.
Počkejte. Dobře, dodatek: udělejme to ještě lepší. Tento list se jmenuje DATA a sešit jsem uložil jako makro, takže xlsm. Pokud jste xlsx, nevynechávejte ukládání jako xlsm. ALT + F11. Najděte sešit s názvem DATA, poklepejte, vlevo nahoře, PRACOVNÍ LIST a poté ZMĚNIT, kdykoli změníme list, a řekneme ACTIVEWORKBOOK.REFRESHALL, a poté zavřete, v pořádku, a teď to zkusme. Pojďme něco upravit. Vezmeme tedy ty maliny, kterých je v současné době 8 000, a změníme to na 1000, takže se zmenšíme o 7 000. Když stisknu ENTER, chci vidět, že 42 000 klesne na 35 000. Ah. Skvělý.
No, hej. To je místo, kde vás obvykle žádám, abyste si koupili moji knihu, ale dnes vás požádám, abyste si koupili knihu mých přátel - Ken Puls a Miguel Escobar - M je pro (DATA) MONKEY. Všechno, co jsem se o energetickém dotazu dozvěděl, jsem se dozvěděl z této knihy. Je to úžasná kniha. Podívejte se na to.
Shrnutí epizody: Steve má součet čísel, která byla zadána do textového sloupce; více řádků v každé buňce oddělených klávesami ALT + ENTER; je třeba rozdělit tyto řádky na řádky a poté analyzovat částku dolaru ze středu každé buňky; shrnout podle COST CENTRA; sestavit vyhledávací tabulku; získejte součty z vyhledávací tabulky pomocí IFNA k ignorování chyb v prázdném řádku; a poté bonusové makro na konci, makro události k aktualizaci listu při změně buňky.
Chci poděkovat Stevovi za zaslání této otázky a jsem tak rád, že mám odpověď - před dotazem na moc by to bylo opravdu, opravdu těžké - a chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2160.xlsm
