Bisheriger Monat - Excel-Tipps

So zeigen Sie die monatlichen Verkäufe in einer Pivot-Tabelle an. Dies ist eine Duell-Excel-Episode.

Schau Video

Bills Methode
Fügen Sie eine Hilfszelle mit einer MTD-Formel hinzu
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Fügen Sie dieses Feld als Slicer hinzu, wobei = True
Bonus-Tipp: Gruppentagesdaten bis zu Jahren
Fügen Sie eine Berechnung außerhalb der Pivot-Tabelle hinzu, ohne GetPivotData zu verwenden
Mikes Ansatz:
Verwandeln Sie die Daten mit Strg + T in eine Tabelle. Dadurch können der Tabelle weitere Daten hinzugefügt und die Formeln aktualisiert werden.
SUMIFS mit den Funktionen DATE, MONTH, DAY
Durch dreimaliges Drücken von F4 wird ein Verweis nur auf die Spalte gesperrt.
Achtung - wenn Sie eine Tabellenformel zur Seite ziehen, ändern sich die Spalten. Kopieren & Einfügen - keine Probleme
Verwenden von TEXT (Datum, Format. Schöner Trick mit 1, um die Nummer 1 in den Text einzufügen

Video-Transkript

Bill Jelen: Hey, willkommen zurück. Es ist Zeit für einen weiteren Duell-Excel-Podcast. Ich bin Bill Jelen von. Ich werde von Mike Girvin von Excel Is Fun begleitet.

Dies ist unsere Pivot-Tabelle zu Episode 181: Monat bis heute.

Nun, hey, die heutige Frage - die heutige Idee für dieses Duell wird von Mike eingesandt. Er sagt: "Können Sie einen Monatsbericht in einer Pivot-Tabelle erstellen?"

Okay, lass uns gehen. Wir haben also zwei Jahre im Wert von zwei Jahren von Januar 2016 bis 2017. Jetzt nehme ich das natürlich im April auf, es ist jetzt der 15. April, wenn ich mein Stück des Duells aufnehme. Und so haben wir hier eine Pivot-Tabelle mit den Tagen auf der linken Seite, der Kategorie oben und dem Umsatz im Herzen der Pivot-Tabelle.

Um nun einen Monatsbericht zu erstellen, werde ich sagen, dass ich hier zu meinen Originaldaten eine neue Hilfsspalte hinzufügen werde, die zwei Dinge überprüft. Und weil ich nach zwei Dingen suche, werde ich die UND-Funktion verwenden, müssen beide Dinge wahr sein, damit es Monat bis Datum ist. Und ich werde hier eine Funktion namens HEUTE verwenden. HEUTE, in Ordnung, also möchte ich wissen, ob der MONAT VON HEUTE () = dem MONAT dieses Datums dort in Spalte A entspricht. Wenn das stimmt, wenn es der aktuelle Monat ist, mit anderen Worten, wenn es April ist, dann Überprüfen Sie, ob der Tag dieses Datums dort in A2 <= TAG VON HEUTE ist. Das Schöne ist, wenn wir diese Arbeitsmappe morgen oder in einer Woche öffnen, wird der Tag von heute automatisch aktualisiert und wir doppelklicken, um ihn nach unten zu kopieren.

Okay, jetzt müssen wir diese zusätzlichen Daten in unsere Pivot-Tabelle aufnehmen, damit ich hierher komme. Pivot-Tabelle, Analysieren und es ist nicht so schwer, die Datenquelle zu ändern. Klicken Sie einfach dort auf diese große Schaltfläche und sagen Sie, dass wir zu Spalte D übergehen möchten , OK klicken. Okay, jetzt haben wir dieses zusätzliche Feld. Ich werde einen Slicer basierend auf diesem Feld "Monat bis Datum" einfügen und ich möchte nur sehen, wie unser Monat bis Datum wahr ist. Brauchen wir dieses Slice, um so groß zu sein? Nein, wir können es wahrscheinlich zu zwei Spalten machen und es auf der rechten Seite einfach unauffällig machen. Was wir jetzt haben, sind alle Daten im Jahr 2016 und alle Daten im Jahr 2017; obwohl es wirklich cool wäre, diese nebeneinander zu vergleichen. Also werde ich dieses Datumsfeld nehmen und analysieren. Ich werde das Feld gruppieren, ich werde es auf nur Jahre gruppieren. Ich nichtIch kümmere mich nicht wirklich um die einzelnen Tage. Ich möchte nur den bisherigen Monat kennen. Wo sind wir jetzt? Also werde ich es in Jahre gruppieren und wir werden diese 2 Jahre dort enden und ich werde dies dann neu ordnen, diese Jahre übergehen, Kategorien fallen lassen. Und jetzt sehe ich, wo wir letztes Jahr waren und wo wir dieses Jahr waren. Okay, da ich die Gruppierung abgeschlossen habe, darf ich kein berechnetes Feld mehr in der Pivot-Tabelle erstellen. Wenn ich dort einen Jahresvergleich haben wollte, würde ich mit der rechten Maustaste auf "Gesamtsumme entfernen" klicken, in Ordnung, und jetzt befinden wir uns in% Change außerhalb einer Pivot-Tabelle, die in die Pivot-Tabelle zeigt . Wir müssen sicherstellen, dass Sie entweder GetPivotData deaktivieren oder einfach eine Formel wie die folgende erstellen: = J4 / I4-1, und dass eine Formel erstellt wird, die wir problemlos kopieren können.Okay, Mike, mal sehen, was du hast.

Mike Girvin: Danke. Ja, ich habe die Frage an gesendet, weil ich sie mit Formeln erstellt habe und nicht herausfinden konnte, wie man sie mit einer Standard-Pivot-Tabelle macht. Dann erinnerte ich mich, dass ich im Laufe der Jahre ein paar coole Videos über Hilfsspalten und Pivot-Tabellen gemacht habe . Das ist eine schöne Formel und eine schöne Lösung. So geht das mit einer Pivot-Tabelle. Lassen Sie uns sehen, wie es mit einer Formel geht.

Jetzt mache ich das zwei Tage nachdem er es getan hat. F2 Ich habe die TODAY-Funktion, die immer die Datumsinformationen für das aktuelle Datum von heute sein wird, die von den Formeln hier unten verwendet werden, da wir möchten, dass sie aktualisiert werden. Ich habe auch eine Excel-Tabelle verwendet, die FSales heißt. Wenn ich Strg + Abwärtspfeil drücke, sehe ich, dass es 4/14 ist, aber ich möchte in der Lage sein, die neuesten Datensätze hinzuzufügen und unsere Formeln zu aktualisieren, wenn wir zum nächsten Monat springen. Strg + Aufwärtspfeil. Okay, ich habe Jahreskriterien als Spaltenüberschriften, die Kategorie als Zeilenüberschriften und dann die Angaben für Monat und Tag aus dieser Zelle. Ich werde einfach die SUMIFS-Funktion verwenden, da wir mit mehreren Bedingungen hinzufügen. Der Summenbereich hier ist der Umsatz. Wir werden diesen großartigen Trick für eine Excel-Tabelle verwenden.Ganz oben sehen wir den schwarzen nach unten zeigenden Pfeil, BAM! Das setzt den richtigen Tabellennamen und dann in eckigen Klammern den Feldnamen Komma. Kriterienbereich, wir müssen Datum zweimal verwenden, also beginne ich mit Datum. Klicken Sie, es gibt die Datumsspalte, Komma. Jetzt bin ich im April, also muss ich die Bedingung> = bis zum 1. April erstellen. Also Vergleichsoperatoren "> =" in doppelten Anführungszeichen und ich werde mich anschließen. Jetzt muss ich eine Datumsformel erstellen, die immer hier aussieht und den ersten des Monats für dieses bestimmte Jahr erstellt. Also werde ich die DATE-Funktion verwenden. Jahr, nun, ich habe das Jahr richtig als Spaltenüberschrift und ich drücke zweimal die F4-Taste, um die Zeile zu sperren, aber nicht die Spalte. Wenn sie hierher verschoben wird, gehen wir zu 2017, Komma, die Monat - ich 'Ich werde die MONTH-Funktion verwenden, um die Monatsnummer 1 bis 12 zu erhalten. Das ist der Monat in dieser Zelle, F4, um ihn in alle Richtungen zu sperren, Klammern und dann Komma zu schließen. 1 Es wird immer der 1. des Monats sein Monat, egal welcher Monat dies ist, schließen Sie Klammern.

Okay, das sind die Kriterien. Es wird immer> = der erste des Monats sein, Komma, Kriterienbereich zwei Ich bekomme meine Datumsspalte, Komma. Kriterium zwei, nun, dies wird <= die Obergrenze sein, also in "<=" und dem &. Ich werde schummeln, schau dir das an. Ich werde dies nur von hier oben kopieren, da es dasselbe ist, Strg-C Strg-V, außer für den Tag. Wir müssen die DAY-Funktion verwenden und erhalten immer unsere Obergrenze, unabhängig vom Tag dieses bestimmten Monats . F4, um es in alle Richtungen zu sperren, schließen Sie die Klammern am Datum. Okay, das sind unsere Kriterien zwei: Komma. Kriterienbereich 3, es ist Kategorie. Da ist es, Komma und da ist unser Zeilenkopf. In diesem Fall müssen wir zwei Mal dreimal F4, die Spalte, aber nicht die Zeile sperren. Wenn wir die Formel nach unten kopieren, wechseln wir zu Gizmo und Widget.Klammer schließen und das ist die Formel. Ziehen Sie über, doppelklicken Sie und senden Sie es nach unten. Ich kann sehen, dass es Ärger gibt. Ich komme besser zu der letzten Zelle, die diagonal am weitesten entfernt ist. Drücken Sie F2. Das Standardverhalten für die Tabellenformelnomenklatur lautet nun: Wenn Sie die Formeln zur Seite kopieren, werden die tatsächlichen Spalten so verschoben, als wären sie gemischte Zellreferenzen. Jetzt könnten wir sie sperren, aber das werde ich diesmal nicht tun. Beachten Sie jetzt, wenn Sie es nach unten kopieren, dass es gut funktioniert, aber wenn Sie zur Seite kopieren, verschieben sich die tatsächlichen Spalten. Sehen Sie sich das an, ich gehe zu Strg + C und Strg + V und das verhindert dann, dass F in die Spalten verschoben wird, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.Ziehen Sie über, doppelklicken Sie und senden Sie es nach unten. Ich kann sehen, dass es Ärger gibt. Ich komme besser zu der letzten Zelle, die diagonal am weitesten entfernt ist. Drücken Sie F2. Das Standardverhalten für die Tabellenformelnomenklatur lautet nun: Wenn Sie die Formeln zur Seite kopieren, werden die tatsächlichen Spalten so verschoben, als wären sie gemischte Zellreferenzen. Jetzt könnten wir sie sperren, aber diesmal werde ich das nicht tun. Beachten Sie jetzt, wenn Sie es nach unten kopieren, dass es gut funktioniert, aber wenn Sie zur Seite kopieren, werden die tatsächlichen Spalten verschoben. Sehen Sie sich das an, ich gehe zu Strg + C und Strg + V und das verhindert, dass sich F zu den Spalten bewegt, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.Ziehen Sie über, doppelklicken Sie und senden Sie es nach unten. Ich kann sehen, dass es Ärger gibt. Ich komme besser zu der letzten Zelle, die diagonal am weitesten entfernt ist. Drücken Sie F2. Das Standardverhalten für die Tabellenformelnomenklatur lautet nun: Wenn Sie die Formeln zur Seite kopieren, werden die tatsächlichen Spalten so verschoben, als wären sie gemischte Zellreferenzen. Jetzt könnten wir sie sperren, aber das werde ich diesmal nicht tun. Beachten Sie jetzt, wenn Sie es nach unten kopieren, dass es gut funktioniert, aber wenn Sie zur Seite kopieren, verschieben sich die tatsächlichen Spalten. Sehen Sie sich das an, ich gehe zu Strg + C und Strg + V und das verhindert, dass sich F zu den Spalten bewegt, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.Ich komme besser zu der letzten Zelle, die diagonal am weitesten entfernt ist. Drücken Sie F2. Das Standardverhalten für die Tabellenformelnomenklatur lautet nun: Wenn Sie die Formeln zur Seite kopieren, werden die tatsächlichen Spalten so verschoben, als wären sie gemischte Zellreferenzen. Jetzt könnten wir sie sperren, aber diesmal werde ich das nicht tun. Beachten Sie jetzt, wenn Sie es nach unten kopieren, dass es gut funktioniert, aber wenn Sie zur Seite kopieren, verschieben sich die tatsächlichen Spalten. Sehen Sie sich das an, ich gehe zu Strg + C und Strg + V und das verhindert, dass sich F zu den Spalten bewegt, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.Ich komme besser zu der letzten Zelle, die diagonal am weitesten entfernt ist. Drücken Sie F2. Das Standardverhalten für die Tabellenformelnomenklatur lautet nun: Wenn Sie die Formeln zur Seite kopieren, werden die tatsächlichen Spalten so verschoben, als wären sie gemischte Zellreferenzen. Jetzt könnten wir sie sperren, aber diesmal werde ich das nicht tun. Beachten Sie jetzt, wenn Sie es nach unten kopieren, dass es gut funktioniert, aber wenn Sie zur Seite kopieren, verschieben sich die tatsächlichen Spalten. Sehen Sie sich das an, ich gehe zu Strg + C und Strg + V und das verhindert, dass sich F zu den Spalten bewegt, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.Die tatsächlichen Spalten bewegen sich so, als wären sie gemischte Zellreferenzen. Jetzt könnten wir sie sperren, aber das werde ich diesmal nicht tun. Beachten Sie jetzt, wenn Sie es nach unten kopieren, dass es gut funktioniert, aber wenn Sie zur Seite kopieren, verschieben sich die tatsächlichen Spalten. Sehen Sie sich das an, ich gehe zu Strg + C und Strg + V und das verhindert, dass sich F zu den Spalten bewegt, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.Die tatsächlichen Spalten bewegen sich so, als wären sie gemischte Zellreferenzen. Jetzt könnten wir sie sperren, aber diesmal werde ich das nicht tun. Beachten Sie jetzt, wenn Sie es nach unten kopieren, dass es gut funktioniert, aber wenn Sie zur Seite kopieren, verschieben sich die tatsächlichen Spalten. Sehen Sie sich das an, ich gehe zu Strg + C und Strg + V und das verhindert, dass sich F zu den Spalten bewegt, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.Ich gehe zu Strg + C und Strg + V und das verhindert dann, dass sich F zu den Spalten bewegt, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.Ich gehe zu Strg + C und Strg + V und das verhindert dann, dass sich F zu den Spalten bewegt, wenn Sie es zur Seite kopieren. Doppelklicken Sie und senden Sie es nach unten. Jetzt ist unsere% Change-Formel = der Endbetrag / der Anfangsbetrag -1, Strg + Eingabetaste, doppelklicken Sie und senden Sie es nach unten.

Bevor wir es testen, fügen Sie einige neue Datensätze hinzu. Ich möchte dieses Label hier oben erstellen, damit es dynamisch ist. Und so werde ich das machen = unterschreiben und wir werden eine Textformel machen. Wenn wir also Text und eine Formel wollen, müssen Sie sie einfügen: „und ich bin Ich werde Sales Between, space eingeben “und jetzt muss ich von diesem einzelnen Datum dort den ersten des Monats bis zum Ende des Monats extrahieren. Ich werde die TEXT-Funktion verwenden. Die TEXT-Funktion kann Datums- oder Seriennummern, Kommas und benutzerdefinierte Nummernformatierungen verwenden. “ Ich möchte immer eine dreistellige Abkürzung für den Monat sehen, mmm, ich möchte sie immer als erste. Wenn ich hier eine 1 setze, Komma Leerzeichen JJJ, funktioniert das nicht. Will sehen, dass das uns einen Wert gibt oder weil es das nicht mag 1. Aber wir 'Es ist zulässig, ein einzelnes Zeichen einzufügen, wenn wir einen Schrägstrich verwenden. Dies erfolgt in der Formatierung der benutzerdefinierten Nummer. Das mm und das yy werden unter der Formatierung der benutzerdefinierten Nummer als Monat und Jahr verstanden, und jetzt wird das Format der benutzerdefinierten Nummer das Einfügen der Nummer 1 verstehen. F2 und jetzt gehen wir einfach zu: & "-" & TEXT dieses Kommas und jetzt wir Verwenden Sie einfach die gerade Zahlenformatierung: "mmm spaceD, yyy") Strg + Eingabetaste.

Now let's just, before we add some data, let's just change this. Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z. Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record. I'm in the last cell of the data set, I hit Tab to add a new record to our data set. I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference. If we wanted to check these formula values against the ones did: =relative cell reference= click on the sheet, we're going to click right in I4. We could see our formula up there, Ctrl+Enter. Actually I'm going to drag it down. Ctrl+Enter just populated everything I had highlighted. And of course, FALSE FALSE. Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.

That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun. Alright, throw back to.

Bill Jelen: Alright, Mike, that's awesome. So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions. Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem. I never knew that one.

And then nice trick there using the heading with the text date format and that 1 to insert a number 1 in the text, in any character. I would allow to insert something, so you might have to do something like COOL to get an entire word in there but it would work.

Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match. Add that field as a Slicer, set the Slicer =True. And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData. And I'm interested, I still don't know how Mike did it with his formula. He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData. Maybe, maybe he's turned it off.

Okay, hey, ich möchte allen dafür danken, dass sie vorbeigekommen sind. Wir sehen uns beim nächsten Mal für einen weiteren Duell-Excel-Podcast von und Excel macht Spaß.

Download-Datei

Laden Sie die Beispieldatei hier herunter: Duel181.xlsm