Excel: Konwersja dat na kwartały
program Excel ma wbudowane funkcje do zwracania roku, numeru miesiąca, nazwy miesiąca, numeru tygodnia, dnia tygodnia lub dnia z dowolnej komórki zawierającej datę. Ale nie ma wbudowanego sposobu na zwrócenie ćwierćnuty. Kwartały są trudniejsze, ponieważ definicja kwartału różni się w zależności od daty zakończenia roku podatkowego.
dla lat kalendarzowych
jeśli twój rok finansowy kończy się 31 grudnia, możesz użyć krótkiej formuły. (Rysunek 1 pokazuje funkcje programu Excel, za pomocą których można przekonwertować datę na lata, miesiące, tygodnie lub dni powszednie.) Najpierw użyj funkcji = MONTH (), aby przekonwertować datę na numer miesiąca. Podziel ten wynik przez 3. Daty w styczniu zwrócą 0.33, luty 0.67, marzec 1.00, kwiecień 1.33 i tak dalej. Używając funkcji =ROUNDUP (), będziesz miał 1 dla stycznia, lutego i marca; 2 dla kwietnia do czerwca; i tak dalej.
aby pokazać ćwiartkę jako ” Q1 ” lub „Qtr 1″, Połącz odpowiedni tekst przed wynikiem. Na fig. 2, komórka A7 używa wzoru = ” Q ” &ROUNDUP(MONTH (A2)/3,0)&”-„&rok (A2) do powrotu ” Q1-2017.”
w przypadku innych lat podatkowych
lata podatkowe kończące się 31 stycznia są powszechne w przedsiębiorstwach detalicznych. Wykonawcy rządowi mogą skorzystać z końca roku podatkowego 30 września. Aby obliczyć kwartał podatkowy, najpierw przekonwertuj datę na liczbę miesiąca za pomocą =MONTH (). Wynik ten jest ograniczony do 12 liczb całkowitych od 1 do 12.
możesz użyć formuły VLOOKUP do konwersji numeru miesiąca na numer kwartału. Ale wzór =VLOOKUP(miesiąc (A2), {1,4;2,1;5,2;8,3;11,4},2) jest trudny do zapamiętania i pisania. Stała tablicy wewnątrz nawiasów klamrowych zawiera 5-rzędową 2-kolumnową tabelę wyszukiwania.
zamiast tego, funkcja = CHOOSE() pozwala na podanie 12 liczb kwartalnych odpowiadających miesiącom od 1 do 12. Jeśli twój rok obrotowy kończy się w styczniu, użyjesz =CHOOSE(MONTH (A2),4,1,1,1,2,2,2,3,3,3,4,4). Dla roku podatkowego kończącego się w marcu, użyj = wybierz(miesiąc (A2),4,4,4,1,1,1,2,2,2,3,3,3).
jeśli wolisz pokazać kwartał jako Q1 zamiast 1, możesz użyć ampersand, aby połączyć „Q” przed funkcją CHOOSE. Wyniki pokazano w komórkach A13 i A20 na fig. 2, ze wzorami pokazanymi w D13 i D20.
dołączanie roku podatkowego do kwartału
w niektórych spółkach rok podatkowy jest określany przez rok kalendarzowy, w którym przypada dwunasty miesiąc roku. Na przykład w roku podatkowym trwającym od lutego 2017 r.do stycznia 2018 r. kwartały będą nazywane kwartałami od I do IV kw. W innych firmach rok podatkowy może być określany jako ” 17-18.”
w przypadku, gdy rok podatkowy jest rokiem kończącego się miesiąca, użyj funkcji IF (), aby sprawdzić, czy numer miesiąca jest mniejszy niż miesiąc początkowy. Na przykład, gdy rok podatkowy kończy się w styczniu, rok podatkowy będzie =rok(A2), jeśli data jest przed lutym, a =rok(A2)+1 w lutym i Później. Tak więc, aby uwzględnić rok podatkowy z kwartałem, możesz użyć =if(MONTH(A2)<2,0,1)+YEAR(A2).
przez rok kończący się w marcu zmieniłbyś test <2 na<4: =IF(MONTH(A2) <4,0,1)+YEAR(A2). Dla roku kończącego się we wrześniu, należy przetestować dla <10: =IF(miesiąc(A2)<10,0,1)+rok(A2).
Komórka A21 na rysunku 2 pokazuje, jak połączyć kwartał i rok podatkowy razem za pomocą tego długiego wzoru: =”Q”&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&”-„&IF(MONTH(A17)<2,0,1)+YEAR(A17). To zwróciłoby kwartał w formacie ” Q4-2017.”
jeśli zamiast tego potrzebujesz, aby powiedzieć 17-18, długa formuła staje się jeszcze dłuższa. Aby uzyskać porcję roku formuły, użyłbyś: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-„&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-„&MOD(YEAR(A2)+1,100)).
zwracając okres zamiast miesiąca
niedawno przeprowadziłem seminarium dla IMA® w Nashville. Ponieważ rząd stanu Tennessee ma rok fiskalny kończący się w czerwcu, woleli zgłaszać za pomocą numeru okresu zamiast miesiąca. Lipiec to okres 1, grudzień to okres 6, Styczeń to okres 7 i tak dalej. W tym przypadku wielu współpracowników użyło tabeli VLOOKUP po prawej stronie danych. Ale mogą też użyć =CHOOSE(MONTH (A2),7,8,9,10,11,12,1,2,3,4,5,6) aby osiągnąć ten sam wynik.
daty nie mogą być tekstem
zastrzeżenie dla wszystkich tych formuł polega na tym, że przechowujesz daty jako prawdziwe daty, a nie jako tekst. Szybki sposób sprawdzenia: naciśnij Ctrl i Klawisz Grave Accent ( ` ), aby przełączyć się w tryb pokaż Formuły. Rzeczywiste daty zostaną przekonwertowane na numer seryjny, taki jak 42783. Jeśli nadal widzisz datę tekstową, formuły nie będą działać. Naciśnij Ctrl+ ’ ponownie, aby zakończyć Pokazywanie formuł.
ostatnio natknąłem się na arkusz roboczy, w którym menedżer chciał wyświetlać daty jako 1st Feb 2017, 2nd Feb 2017, 3rd Feb 2017, 4th Feb 2017. Przyrostki po dacie („st”,” nd”,” rd „I” th”) zamieniają liczbę kardynalną na liczbę porządkową. Podczas gdy uchwyt wypełniania programu Excel pozwoli Ci łatwo utworzyć kolumnę liczb kardynalnych, formuły w tym artykule staną się znacznie bardziej złożone. 41-znakowa formuła rozrosła się do 2438 znaków w celu obsługi dat tekstowych (patrz rysunek 3).
