Excel: Převod Dat do Kajuty
Excel je vestavěné funkce pro vrácení rok, měsíc, číslo, název měsíce, číslo týdne, ve všední den, nebo den z libovolné buňky, která obsahuje data. Ale není žádný vestavěný způsob, jak vrátit čtvrtinové číslo. Čtvrtletí jsou obtížnější, protože definice čtvrtletí se liší v závislosti na datu ukončení fiskálního roku.
pro kalendářní roky
pokud váš finanční rok končí 31. Prosince, můžete použít krátký vzorec. (Obrázek 1 ukazuje funkce aplikace Excel, které můžete použít k převodu data na roky, měsíce, týdny nebo pracovní dny.) Nejprve pomocí funkce = MONTH() převeďte datum na číslo měsíce. Vydělte tento výsledek 3. Data v lednu se vrátí 0.33, únor 0.67, Březen 1.00, Duben 1.33 a tak dále. Pomocí funkce = ROUNDUP () budete mít 1 za leden, únor a Březen; 2 za duben až červen; a tak dále.
ukázat čtvrtletí jako „Q1“ nebo „Čtv 1,“ concatenate odpovídající text, než výsledek. Na obrázku 2 buňka A7 používá vzorec = “ Q “ &ROUNDUP (měsíc (A2)/3,0)&“-„&rok (A2) pro návrat “ Q1-2017.“
PRO DALŠÍ FISKÁLNÍ ROKY
Fiskální roky končící 31. ledna jsou běžné v maloobchodní společnosti. Vládní dodavatelé by mohli využít fiskální rok do konce 30. Září. Chcete-li vypočítat fiskální čtvrtletí, nejprve převeďte datum na číslo měsíce pomocí =měsíc(). Tento výsledek je omezen na 12 celých čísel od 1 do 12.
můžete použít vzorec VLOOKUP pro převod čísla měsíce na číslo čtvrtletí. Ale vzorec =VLOOKUP (měsíc (A2), {1,4;2,1;5,2;8,3;11,4},2) je těžké si pamatovat a psát. Konstanta pole uvnitř složených závorek obsahuje vyhledávací tabulku 5 řádků po 2 sloupcích.
místo toho vám funkce =CHOOSE() umožní zadat 12 čísel čtvrtletí, která odpovídají měsícům 1 až 12. Pokud váš fiskální rok skončí v lednu, použijte =CHOOSE (MONTH (A2),4,1,1,1,2,2,2,3,3,3,4,4). Pro fiskální rok končící v březnu použijte =CHOOSE (MONTH (A2),4,4,4,1,1,1,2,2,2,3,3,3).
Pokud byste raději ukázat čtvrtletí Q1 místo 1, můžete použít ampersand zřetězit „Q“ předtím, než VYBERETE funkci. Výsledky jsou uvedeny v buňkách A13 a A20 na obrázku 2, se vzorci uvedenými v D13 a D20.
připojení fiskálního roku ke čtvrtletí
v některých společnostech je fiskální rok určen kalendářním rokem, ve kterém nastane 12. měsíc roku. Například fiskální rok, který trvá od února 2017 do ledna 2018, by měl čtvrtletí nazvané Q1-2018 až Q4-2018. V jiných společnostech může být fiskální rok označován jako něco jako “ 17-18.“
v případě, že fiskální rok je rokem končícího měsíce, použijte funkci IF() k testování, zda je číslo měsíce menší než počáteční měsíc. Například s fiskálním rokem končícím v lednu bude fiskální rok =rok (A2), pokud je datum před únorem a =rok(A2)+1 v únoru a později. Chcete-li tedy zahrnout fiskální rok do čtvrtletí, můžete použít =IF(měsíc(A2)<2,0,1)+rok(A2).
pro rok končící v březnu byste změnili test <2 na <4: = IF (měsíc (A2)<4,0,1)+rok(A2). Pro rok končící v září byste testovali <10: =IF(měsíc(A2)<10,0,1)+rok(A2).
Buňky A21 v Obrázku 2 ukazuje, jak zřetězit fiskální čtvrtletí a rok spolu pomocí tohoto dlouhého vzorce: =“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 by vrátilo čtvrtletí ve formátu “ Q4-2017.“
pokud místo toho potřebujete říci 17-18, dlouhý vzorec se ještě prodlouží. Jen získat roční část vzorce, použili byste: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&“-„&TEXT(A2,“YY“),TEXT(A2,“YY“)&“-„&MOD(YEAR(A2)+1,100)).
vracím období místo měsíce
nedávno jsem vedl seminář pro IMA® v Nashvillu. Protože vláda státu Tennessee Má fiskální rok končící v červnu, raději hlásili pomocí čísla období místo měsíce. Červenec je období 1, prosinec je období 6, Leden je období 7 atd. V tomto případě mnoho spolupracovníků použilo tabulku VLOOKUP na pravé straně dat. Ale také mohli použít =CHOOSE (měsíc (A2),7,8,9,10,11,12,1,2,3,4,5,6) k dosažení stejného výsledku.
data nemohou být TEXT
upozornění pro všechny tyto vzorce je, že ukládáte data jako skutečná data a ne jako text. Rychlý způsob kontroly: stiskněte Ctrl A Grave Accent key ( ` ) pro přepnutí do režimu Zobrazit vzorce. Skutečná data se převedou na sériové číslo, například 42783. Pokud stále vidíte textové Datum, vzorce nebudou fungovat. Stiskněte Ctrl + ‚ znovu ukončit Zobrazit vzorce.
nedávno jsem narazil na pracovní list, ve kterém Správce chtěl data zobrazit jako 1st Feb 2017, 2nd Feb 2017, 3rd Feb 2017, 4th Feb 2017. Přípony za datem („st“, „nd“, „rd“ a „th“) převádějí kardinální číslo na pořadové číslo. Zatímco popisovač výplně aplikace Excel vám umožní snadno vytvořit sloupec kardinálních čísel, vzorce v tomto článku se stanou mnohem složitějšími. Vzorec 41 znaků se rozrostl na 2 438 znaků, aby bylo možné zpracovat textová data (viz obrázek 3).
