Excel: Konvertieren von Daten in Quartale
Excel verfügt über integrierte Funktionen zum Zurückgeben von Jahr, Monatsnummer, Monatsname, Wochennummer, Wochentag oder Tag aus jeder Zelle, die ein Datum enthält. Es gibt jedoch keine integrierte Möglichkeit, eine Viertelzahl zurückzugeben. Quartale sind schwieriger, da die Definition eines Quartals je nach Enddatum des Geschäftsjahres variiert.
FÜR KALENDERJAHRE
Wenn Ihr Geschäftsjahr am 31. Dezember endet, können Sie eine kurze Formel verwenden. (Abbildung 1 zeigt die Excel-Funktionen, mit denen Sie ein Datum in Jahre, Monate, Wochen oder Wochentage konvertieren können.) Verwenden Sie zunächst die Funktion =MONTH(), um das Datum in eine Monatszahl umzuwandeln. Dividiere das Ergebnis durch 3. Die Daten im Januar werden 0,33, Februar 0,67, März 1,00, April 1,33 usw. zurückgeben. Wenn Sie die Funktion =ROUNDUP() verwenden, haben Sie 1 für Januar, Februar und März; 2 für April bis Juni; und so weiter.
Um das Quartal als „Q1“ oder „Qtr 1″ anzuzeigen, verketten Sie den entsprechenden Text vor dem Ergebnis. In Abbildung 2 verwendet Zelle A7 eine Formel von =“Q“&ROUNDUP(MONAT (A2)/3,0)&“-„& JAHR(A2) zurück „Q1-2017.“
FÜR ANDERE GESCHÄFTSJAHRE
Geschäftsjahre, die am 31.Januar enden, sind in Einzelhandelsunternehmen üblich. Staatliche Auftragnehmer könnten ein Geschäftsjahr Ende September 30 verwenden. Um ein Geschäftsquartal zu berechnen, konvertieren Sie zuerst das Datum mit =MONTH() in eine Monatszahl. Dieses Ergebnis ist auf 12 ganze Zahlen von 1 bis 12 begrenzt.
Sie können eine VLOOKUP-Formel verwenden, um die Monatszahl in die Quartalszahl umzuwandeln. Aber die Formel =VLOOKUP(MONAT(A2), {1,4;2,1;5,2;8,3;11,4},2) ist schwer zu merken und zu tippen. Die Array-Konstante in den geschweiften Klammern enthält eine 5-Zeilen- und 2-Spalten-Nachschlagetabelle.
Stattdessen können Sie mit der Funktion =CHOOSE() 12 Quartalszahlen angeben, die den Monaten 1 bis 12 entsprechen. Wenn Ihr Geschäftsjahr im Januar endet, verwenden Sie =CHOOSE(MONTH(A2),4,1,1,1,2,2,2,3,3,3,4,4). Für ein Geschäftsjahr, das im März endet, verwenden Sie =CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3).
Wenn Sie das Quartal lieber als Q1 anstelle von 1 anzeigen möchten, können Sie das kaufmännische Und verwenden, um ein „Q“ vor der CHOOSE-Funktion zu verketten. Die Ergebnisse sind in den Zellen A13 und A20 von Abbildung 2 mit den Formeln in D13 und D20 dargestellt.
ANFÜGEN EINES GESCHÄFTSJAHRES AN DAS QUARTAL
In einigen Unternehmen wird das Geschäftsjahr durch das Kalenderjahr bestimmt, in dem der 12. Beispielsweise hätte ein Geschäftsjahr, das von Februar 2017 bis Januar 2018 dauert, Quartale mit den Bezeichnungen Q1-2018 bis Q4-2018. In anderen Unternehmen kann das Geschäftsjahr als „17-18″ bezeichnet werden.“
Für den Fall, dass das Geschäftsjahr das Jahr des Endmonats ist, verwenden Sie eine IF() -Funktion, um zu testen, ob die Monatsnummer kleiner als der Startmonat ist. Wenn beispielsweise ein Geschäftsjahr im Januar endet, lautet das Geschäftsjahr = JAHR (A2), wenn das Datum vor Februar liegt, und = JAHR (A2) + 1 im Februar und später. Um das Geschäftsjahr in das Quartal einzubeziehen, können Sie also =IF(MONTH(A2)<2,0,1)+YEAR(A2) .
Für ein Jahr, das im März endet, würden Sie den <2-Test in <4 ändern: =IF(MONTH(A2)<4,0,1)+YEAR(A2) . Für ein Jahr, das im September endet, würden Sie auf <10 testen: =IF(MONTH(A2)<10,0,1)+YEAR(A2) .
Zelle A21 in Abbildung 2 zeigt, wie das Geschäftsquartal und das Geschäftsjahr mit dieser langen Formel verkettet werden: =“Q“&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&“-„&IF(MONTH(A17)<2,0,1)+YEAR(A17). Das würde das Quartal in einem Format von „Q4-2017.“
Wenn Sie stattdessen 17-18 sagen müssen, wird die lange Formel noch länger. Nur um den Jahresanteil der Formel zu erhalten, würden Sie verwenden: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&“-„&TEXT(A2,“YY“),TEXT(A2,“YY“)&“-„&MOD(YEAR(A2)+1,100)).
RÜCKGABE EINER PERIODE STATT EINES MONATS
Ich habe kürzlich ein Seminar für IMA® in Nashville durchgeführt. Da die Regierung des Bundesstaates Tennessee ein Geschäftsjahr hat, das im Juni endet, Sie zogen es vor, eine Periodennummer anstelle eines Monats zu verwenden. Juli ist Periode 1, Dezember ist Periode 6, Januar ist Periode 7 und so weiter. In diesem Fall verwendeten viele Mitarbeiter eine VLOOKUP-Tabelle auf der rechten Seite der Daten. Aber sie könnten auch verwenden =CHOOSE(MONTH(A2),7,8,9,10,11,12,1,2,3,4,5,6) um das gleiche Ergebnis zu erzielen.
DATEN KÖNNEN KEIN TEXT SEIN
Die Einschränkung für alle diese Formeln besteht darin, dass Sie Ihre Daten als echte Daten und nicht als Text speichern. Eine schnelle Möglichkeit zur Überprüfung: Drücken Sie Strg und die Grave Accent-Taste (`), um in den Show Formulas-Modus zu wechseln. Reale Daten werden in eine Seriennummer wie 42783 konvertiert. Wenn Sie immer noch ein Textdatum sehen, funktionieren die Formeln nicht. Drücken Sie erneut Strg +`, um Formeln anzeigen zu beenden.
Ich bin kürzlich auf ein Arbeitsblatt gestoßen, in dem der Manager Daten als 1. Februar 2017, 2. Februar 2017, 3. Februar 2017, 4. Februar 2017 anzeigen wollte. Die Suffixe nach dem Datum („st“, „nd“, „rd“ und „th“) konvertieren eine Kardinalzahl in eine Ordnungszahl. Während Sie mit dem Excel-Füllgriff problemlos eine Spalte mit Kardinalzahlen erstellen können, werden die Formeln in diesem Artikel weitaus komplexer. Aus einer 41-stelligen Formel wurden 2.438 Zeichen, um die Textdaten zu verarbeiten (siehe Abbildung 3).
