Dies ist das Budgetproblem des Feuerwehrmanns. Die Leute in einem Feuerwehrhaus haben ihre Budgets in Excel falsch gemacht. Eine erstaunliche Power Query-Transformation bietet die Lösung.
Schau Video
- Steve muss Zahlen summieren, die in eine Textspalte eingegeben wurden
- In jeder Zelle befinden sich mehrere Zeilen, die durch alt = "" + Enter getrennt sind
- Sie müssen diese Zeilen in Zeilen aufteilen und dann den Dollarbetrag aus der Mitte jeder Zelle analysieren
- Nach Kostenstelle zusammenfassen
- Erstellen Sie eine Nachschlagetabelle
- Rufen Sie Summen aus der Nachschlagetabelle ab und ignorieren Sie mithilfe von IFNA die Fehler in der leeren Zeile
- Bonus: Fügen Sie ein Ereignismakro hinzu, um das Arbeitsblatt zu aktualisieren, wenn eine Zelle geändert wird.
Video-Transkript
Lernen Sie Excel aus, Podcast Episode 2160: SUM-Daten, die Alt + Eingegeben wurden.
Hallo. Willkommen zurück im Netcast. Ich bin Bill Jelen. Ich mache das nicht nach. Ich habe eine Frage von jemandem bekommen, der Daten hat - Budgetdaten - die so aussehen. Jetzt habe ich hier falsche Worte eingefügt, damit wir keine Budgetinformationen haben, aber die Person, die neu in der Buchhaltungsabteilung ist, ist zu einer Firma gegangen, und diese Firma hat jahrelang ihre Budgets so gemacht. Sie sind keine Buchhalter, die das Budget verwalten, sie sind Linienleute, aber so haben sie es gemacht, und er kann sie nicht dazu bringen, sich zu ändern. Hier ist unser Ziel. Er sagt, das sei so schlimm wie das Eingeben des Budgets in Word.
Nun, fast, aber zum Glück wird es dank der Stromabfrage unser Problem retten. Hier ist unser Ziel. Für jedes KOSTENZENTRUM hier möchten wir die Summe aller dieser Zahlen angeben. Es gibt also einen Kostennamen, ein -, routinemäßig ein -, dann ein $ -Zeichen und dann, nur um das Leben interessant zu machen, hin und wieder eine zufällige Notiz danach; nicht immer, nur manchmal. Leere Reihe zwischen jedem. Tonnen und Tonnen von Daten.
Also, hier ist was ich tun werde. Ich werde ganz nach unten kommen, in die allerletzte Zelle. Ich werde all diese Dinge auswählen, einschließlich der Überschriften. Ich werde einen NAMEN erstellen. Ich werde es MyData nennen. MyData, so, okay? In Ordung. Jetzt verwenden wir die Power-Abfrage, die 2010 oder 2013 kostenlos ist und in Office 365 für 2016 und 2016 integriert ist. Diese wird aus einer TABELLE ODER BEREICH stammen. In Ordung. Als erstes, wann immer wir diese Leerzeichen in SPALTE A haben, alle NULLEN, die wir loswerden wollen. Also werde ich NULL deaktivieren. Genial. Okay. Wirklich, in diesen Daten, in dieser Version der Daten, weil ich ein VLOOKUP erstellen werde, brauchen wir diese Spalte nicht. Also werde ich mit der rechten Maustaste klicken und diese Spalte entfernen, also Spalte ENTFERNEN.
In Ordung. Hier wird die verdammte Magie passieren. Wählen Sie diese Spalte, SPLIT COLUMN BY A DELIMITER, und wir werden definitiv auf ADVANCED eingehen. Das Trennzeichen wird ein Sonderzeichen sein und wir werden jedes Vorkommen des Trennzeichens aufteilen. Ich denke, sie haben es tatsächlich schon herausgefunden, weil ich es erweitert habe, aber ich werde es Ihnen zeigen. SPEZIALZEICHEN EINFÜGEN. Ich werde sagen, dass es ein LINE FEED ist, in Ordnung, also werde ich bei jedem Auftreten des LINE FEED IN ROWS SPLITEN. Okay, und genau das, was hier passieren wird, ist, 1, 2, 3, 4, 5, ich werde 5 Zeilen bekommen oder ich werde 1001 sagen, aber in jeder Zeile wird es eine andere geben Linie aus dieser Zelle. Das ist großartig. Es gibt 1, 2, 3, 4, 5, 1001. In Ordnung. Jetzt müssen wir nur noch diesen bösen Jungen analysieren. In Ordung,Wählen Sie also die Spalte SPLIT COLUMN BY A DELIMITER. Dieses Mal wird ein Trennzeichen ein $ -Zeichen sein. Das ist einmal beim ersten $ -Zeichen, das wir finden, perfekt, nur für den Fall, dass es im zukünftigen Teil ein $ -Zeichen gibt. Wir werden in Spalten spalten. OK klicken. In Ordung. Es gibt also Details. Hier ist unser Geld.
Jetzt werde ich das im SPACE aufteilen. Wählen Sie also diese Spalte, SPLIT COLUMN BY A DELIMITER, und das Trennzeichen wird ein SPACE sein. Ja, klicken Sie im LINKS-MOST DELIMITER auf OK, und ich brauche diese Kommentare nicht, also diese Kommentare, die wir ' Ich werde entfernen. Eigentlich brauche ich das auch nicht, weil ich nur versuche, all das Zeug zu bekommen, also werde ich ENTFERNEN.
Jetzt verwandeln. GRUPPE NACH KOSTENZENTRUM, NEUER SPALTENNAME wird GESAMT heißen, der BETRIEB wird die SUMME sein, und welche Spalte werden wir summieren? Die DETAILS2.1. Wunderschönen. Klicken Sie auf OK, in Ordnung, und wir erhalten eine Zeile pro KOSTENZENTRUM mit der GESAMT aller dieser Positionen. HAUS, SCHLIESSEN & LADEN. Es wird wahrscheinlich ein neues Arbeitsblatt einfügen. Ich hoffe, es fügt ein neues Arbeitsblatt ein, und das tut es, und dieses Arbeitsblatt heißt MYDATA_1. MYDATA_1.
In Ordung. Jetzt kehren wir zu den Originaldaten zurück und führen diese Schritte aus. Beim allerersten = VLOOKUP von 1001 in unsere Ergebnisse. Dies ähnelt dem Einrichten eines Zirkelverweises, gibt uns jedoch keinen Zirkelverweis. , 2, FALSE. Ich möchte die genaue Übereinstimmung. Okay, aber wir werden das nicht für die leeren Zellen tun wollen. Also, ich werde sagen, nun, lass es uns einfach ganz nach unten kopieren. STEUERUNG + C, gehen Sie ganz nach unten, um zu sehen, was wir bekommen. Vielleicht bekommen wir N / As und ich kann es mit der IFNA loswerden. Ja, schön, in Ordnung. Also, lasst uns einfach die N / As loswerden. Wenn N / A, dann wollen wir nur "". Wir wollen nichts drin haben. STEUERUNG + EINGABETASTE. In Ordung. Nun, das sollte das GESAMT sein. Mal sehen, ob wir einen kurzen finden und einfach rechnen können. = 627,37 + 7264,25 + 6066.01 + 4010.66 + 9773.94, und das GESAMT, 27742.23 ist das. Absolut fantastisch. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), ""))
Hier ist der Deal. Wir haben also diese Leute, die hier draußen sind, um Dinge zu ändern, okay, und sagen wir, sie gehen durch und ändern das Budget, 40294.48, und sie kommen hierher und ändern dieses auf 6000, und sie fügen hinzu ein neues, ALT + ENTER, ETWAS - $ Zeichen, $ 1000 gerade hinzugefügt. In Ordung. Wenn ich jetzt die EINGABETASTE drücke, wird diese Nummer, 40294.48, natürlich nicht aktualisiert, aber wir müssen nur zur Registerkarte DATEN gehen und ALLE ERNEUERN. Also 40294.48. Beobachten, beobachten, beobachten, beobachten. ALLE ERFRISCHEN. Wirklich unglaublich.
Ich liebe Power Query. Power Query ist das Erstaunlichste. Diese Daten, die im Wesentlichen wie Wortdaten in einer Zelle sind, werden jetzt aktualisiert. Sie könnten wahrscheinlich sogar eine Art Makro erstellen, das besagt, dass jedes Mal, wenn jemand etwas in SPALTE C ändert, wir mit dem Makro auf ALLE ERNEUERN klicken und diese Ergebnisse ständig und ständig aktualisieren.
Was für eine schreckliche Frage. Ich fühle mich schlecht für Steve, der sich damit befassen muss, aber jetzt, wenn Sie Power Query in Office 365 verwenden oder für 2010 oder 2013 heruntergeladen haben, haben Sie eine sehr, sehr einfache Möglichkeit, dies zu lösen.
Warten. Okay, ein Nachtrag: Machen wir es noch besser. Dieses Blatt heißt DATA und ich habe die Arbeitsmappe als makrofähig gespeichert, also xlsm. Wenn Sie xlsx sind, überspringen Sie das Speichern als xlsm nicht. ALT + F11. Suchen Sie die Arbeitsmappe mit dem Namen DATA, doppelklicken Sie oben links auf WORKSHEET und ändern Sie sie jedes Mal, wenn wir das Arbeitsblatt ändern. Wir sagen ACTIVEWORKBOOK.REFRESHALL und schließen Sie dann, in Ordnung, und versuchen wir es jetzt. Lassen Sie uns etwas bearbeiten. Also nehmen wir die Himbeeren, die derzeit 8.000 sind, und ändern sie auf 1000, sodass wir sie um 7000 reduzieren. Wenn ich die EINGABETASTE drücke, möchte ich sehen, dass 42.000 auf 35.000 sinken. Ah. Genial.
Nun, hey. Hier bitte ich Sie normalerweise, mein Buch zu kaufen, aber heute werde ich Sie bitten, das Buch meiner Freunde zu kaufen - Ken Puls und Miguel Escobar - M ist für (DATA) MONKEY. Alles, was ich über Power Query gelernt habe, habe ich aus diesem Buch gelernt. Es ist ein erstaunliches Buch. Schau dir das an.
Episodenabschluss: Steve hat Zahlen zu summieren, die in eine Textspalte eingegeben wurden. mehrere Zeilen in jeder Zelle, getrennt durch ALT + ENTER; Sie müssen diese Zeilen in Zeilen aufteilen und dann den Dollarbetrag aus der Mitte jeder Zelle analysieren. zusammenfassen durch KOSTENZENTRUM; eine Nachschlagetabelle erstellen; Abrufen von Summen aus der Nachschlagetabelle, wobei IFNA verwendet wird, um die Fehler in der leeren Zeile zu ignorieren. und dann ein Bonusmakro am Ende, ein Ereignismakro zum Aktualisieren des Arbeitsblatts, wenn eine Zelle geändert wird.
Ich möchte Steve dafür danken, dass er diese Frage eingeschickt hat, und ich bin so froh, dass ich eine Antwort habe - vor der Stromabfrage wäre es wirklich sehr, sehr schwierig gewesen - und ich möchte Ihnen dafür danken, dass Sie vorbeigekommen sind. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2160.xlsm
