Dit is het budgetprobleem van de brandweerman. De mensen in een brandweerkazerne hebben hun budgetten verkeerd gedaan in Excel. Een geweldige Power Query-transformatie biedt de oplossing.
Bekijk video
- Steve moet getallen optellen die in een tekstkolom zijn ingevoerd
- Er zijn meerdere regels in elke cel, gescheiden door alt = "" + Enter
- U moet die regels opsplitsen in rijen en vervolgens het dollarbedrag in het midden van elke cel ontleden
- Vat samen op kostenplaats
- Bouw een opzoektabel
- Haal totalen op uit de opzoektabel en gebruik IFNA om de fouten in de lege rij te negeren
- Bonus: voeg een gebeurtenismacro toe om het werkblad bij te werken wanneer ze van cel veranderen.
Videotranscriptie
Leer Excel van, Podcast Episode 2160: SUM-gegevens die zijn Alt + ingevoerd.
Hallo. Welkom terug bij de netcast. Ik ben Bill Jelen. Ik verzin dit niet. Ik heb een vraag van iemand die gegevens heeft - budgetgegevens - die er zo uitziet. Nu, ik stop hier nepwoorden in zodat we hun budgetinformatie niet hebben, maar iemand is nieuw op de boekhoudafdeling, ging naar een bedrijf, en dit bedrijf doet al jaren zijn budgetten op deze manier. Het zijn geen accountants die de begroting doen, het zijn lijnmensen, maar dit is de manier waarop ze het hebben gedaan, en hij kan ze niet zover krijgen dat ze veranderen. Dus hier is ons doel. Hij zegt dat dit net zo erg is als het budget in Word typen.
Nou ja, bijna, maar gelukkig, dankzij power query, gaat het ons probleem redden. Hier is ons doel. Voor elk COST CENTER hier willen we het totaal van al die cijfers rapporteren. Er is dus een uitgavennaam, een -, gewoonlijk een -, dan een $ -teken, en dan, om het leven interessant te maken, zo nu en dan een willekeurige noot erna; niet altijd, maar een deel van de tijd. Lege rij tussen elk. Tonnen en tonnen gegevens.
Dus hier is wat ik ga doen. Ik ga helemaal naar beneden, de allerlaatste cel, ik ga al deze dingen selecteren, inclusief de titels. Ik ga een NAAM maken. Ik ga het MyData noemen. MyData, zoals dat, oké? Oké. Nu gaan we Power Query gebruiken die gratis is in 2010 of 2013, ingebouwd in 2016 en 2016 Office 365. Dat komt uit een TABEL OF BEREIK. Oké. Ten eerste, elke keer dat we die lege plekken in KOLOM A hebben, alle NULLS die we willen verwijderen. Dus ik ga NULL uitschakelen. Geweldig. Oke. Echt, in deze gegevens, in deze versie van de gegevens, omdat ik een VERT.ZOEKEN ga bouwen, hebben we deze kolom niet nodig. Dus ik ga met de rechtermuisknop klikken en die kolom verwijderen, dus VERWIJDER de kolom.
Oké. Nu, hier zal de freaking magie gebeuren. Kies deze kolom, SPLIT KOLOM MET EEN DELIMITER, en we gaan zeker naar GEAVANCEERD. Het scheidingsteken wordt een speciaal teken en we gaan elk voorkomen van het scheidingsteken splitsen. Dus hier, ik denk dat ze het eigenlijk al hebben bedacht omdat ik het heb uitgebreid, maar ik ga het je laten zien. SPECIAAL KARAKTER INVOEGEN. Ik ga zeggen dat het een LIJNVOER is, oké, dus bij elk voorkomen van de LIJNVOER, en ik ga in RIJEN SPLITSEN. Oké, en wat er hier gaat gebeuren is 1, 2, 3, 4, 5, ik krijg 5 rijen of ik zeg 1001, maar in elke rij zal het een andere hebben lijn uit deze cel. Dit is geweldig. Er zijn 1, 2, 3, 4, 5, 1001. Oké. Nu moeten we deze slechte jongen ontleden. Oké,dus kies die kolom, SPLIT KOLOM MET EEN DELIMITER. Deze keer wordt een scheidingsteken een $ -teken. Dat is perfect, eens, bij het eerste $ -teken dat we vinden, voor het geval er in het toekomstige deel een $ -teken is. We gaan SPLITSEN IN KOLOMMEN. Klik OK. Oké. Er zijn dus details. Hier is ons geld.
Nu ga ik dit splitsen in de RUIMTE. Dus, kies deze kolom, SPLIT KOLOM DOOR EEN DELIMITER, en het scheidingsteken wordt een SPATIE. gaat naar VERWIJDEREN. Eigenlijk heb ik dit ook niet nodig, want ik probeer gewoon al die dingen in totaal te krijgen, dus ik ga VERWIJDEREN.
Nu transformeren. GROEP VOOR KOSTENCENTRUM, NIEUWE KOLOMNAAM wordt TOTAAL genoemd, de OPERATIE wordt de SOM, en welke kolom gaan we SOMEN? De DETAILS 2.1. Mooi. Klik op OK, oké, en we eindigen met één regel per COST CENTER met het TOTAAL van al die regelitems. THUIS, DICHT & LADEN. Het gaat waarschijnlijk een nieuw werkblad invoegen. Ik hoop dat het een nieuw werkblad invoegt, en dat doet het, en dat werkblad heet MYDATA_1. MYDATA_1.
Oké. Nu gaan we hier terugkomen op de oorspronkelijke gegevens en deze stappen uitvoeren. Op de allereerste, = VERT.ZOEKEN van 1001 in onze resultaten. Dit is zoiets als het opzetten van een kringverwijzing, maar het geeft ons geen kringverwijzing. , 2, ONWAAR. Ik wil de exacte overeenkomst. Oké, maar dat willen we niet doen voor de lege cellen. Dus ik ga zeggen, laten we het eigenlijk gewoon helemaal kopiëren. CONTROL + C, ga helemaal naar beneden om te zien wat we krijgen. Misschien krijgen we N / A's en kan ik er vanaf komen met de IFNA. Ja, mooi, oké. Dus laten we gewoon de N / A's verwijderen. Indien nvt, dan willen we gewoon “”. We willen daar niets in. CONTROL + ENTER. Oké. Nu, dat zou het TOTAAL moeten zijn. Laten we eens kijken of we een korte kunnen vinden en gewoon de wiskunde doen. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, en het TOTAAL, 27742,23 is dat. Super gaaf. (= IFNA (VERT.ZOEKEN (A2, MyData_1,2, FALSE), ""))
Nu, hier is de deal. Dus we hebben die lijnmensen die hier dingen veranderen, oké, en laten we zeggen dat ze doorgaan en het budget veranderen, 40294.48, en ze komen hierheen en veranderen deze in 6000, zoals dat, en ze voegen een nieuwe, ALT + ENTER, SOMETHING - $ sign, $ 1000 zojuist toegevoegd. Oké. Als ik nu op ENTER druk, wordt dit nummer, 40294.48, natuurlijk niet bijgewerkt, oké, maar wat we moeten doen is naar het tabblad GEGEVENS gaan en we willen ALLES VERNIEUWEN. Dus 40294.48. Kijk, kijk, kijk, kijk. VERVERS ALLES. Super geweldig.
Ik hou van machtsvragen. Power-query is het meest verbazingwekkende. Deze gegevens, die in wezen net als woordgegevens in een cel zijn, worden nu bijgewerkt. Je zou waarschijnlijk zelfs een soort macro kunnen maken die zegt dat elke keer dat iemand iets verandert in KOLOM C, we doorgaan en op ALLES VERNIEUWEN klikken met de macro en die resultaten gewoon constant hebben, constant vernieuwen.
Wat een vreselijke vraag. Ik voel me slecht voor Steve die hiermee te maken heeft, maar nu, met behulp van Power Query in Office 365 of gedownload voor 2010 of 2013, heb je een heel, heel gemakkelijke manier om dit op te lossen.
Wacht. Oké, een addendum: laten we het nog beter maken. Dit blad heet DATA en ik heb de werkmap opgeslagen als macro-enabled, dus xlsm. Als u xlsx bent, sla het opslaan als xlsm dan niet over. ALT + F11. Zoek de werkmap met de naam GEGEVENS, dubbelklik, linksboven, WERKBLAD en vervolgens VERANDER wanneer we het werkblad wijzigen, en we zeggen ACTIVEWORKBOOK.REFRESHALL, en sluit dan, oké, en laten we het nu proberen. Laten we iets bewerken. Dus we nemen die frambozen die momenteel 8.000 zijn en we veranderen het in 1000, dus we verminderen met 7000. Als ik op ENTER druk, wil ik zien dat die 42.000 dalen tot 35.000. Ah. Geweldig.
Hallo daar. Dit is waar ik je meestal smeek om mijn boek te kopen, maar vandaag ga ik je vragen het boek van mijn vrienden te kopen - Ken Puls en Miguel Escobar - M is voor (DATA) AAP. Alles wat ik heb geleerd over machtsvraagstukken, heb ik uit dit boek geleerd. Het is een geweldig boek. Moet je dat zien.
Afronding van de aflevering: Steve moet getallen optellen die in een tekstkolom zijn ingevoerd; meerdere regels in elke cel, gescheiden door ALT + ENTER; u moet die regels in rijen splitsen en vervolgens het dollarbedrag uit het midden van elke cel ontleden; samenvatten door COST CENTER; bouw een opzoektabel; totalen ophalen uit de opzoektabel, IFNA gebruiken om de fouten in de lege rij te negeren; en dan een bonus, macro aan het einde, een gebeurtenismacro om het werkblad bij te werken wanneer ze van cel veranderen.
Ik wil Steve bedanken voor het insturen van die vraag en ik ben zo blij dat ik een antwoord heb - vóór de machtsvraag zou het echt heel moeilijk zijn geweest - en ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2160.xlsm
