Jak změnit měsíc v Excelu. Zadávání a formátování dat a časů v Excelu. Seřadit v Excelu podle data a měsíce

Každá transakce se provádí v určitém čase nebo období a poté se váže k určitému datu. V Excelu jsou data převedena na celá čísla. To znamená, že každé datum má své vlastní celé číslo, například 01/01/1900 je číslo 1 a 01/02/1900 je číslo 2 atd. Definování let, měsíců a dnů není nic jiného než vhodný typ formátování pro další číselné hodnoty. Z tohoto důvodu se i ty nejjednodušší operace s daty prováděné v Excelu (například řazení) ukazují jako velmi problematické.

Seřadit v Excelu podle data a měsíce

Na příkladu tabulky zobrazené na obrázku níže si ukážeme, jak ji připravit na pohodlné třídění dat. Náš seznam datových transakcí pokrývá rozsah buněk B1:C15.

Nejprve si vytvoříme sloupec, do kterého očíslujeme všechny transakce. Považujte tuto akci za dobrou formu pro jakýkoli typ třídění. V takových případech, pokud jsou data nesprávně zamíchána, máme totiž vždy možnost vrátit se k původní podobě tabulky. Postup:

V důsledku toho se sloupec automaticky vyplní sekvencí čísel transakcí od 1 do 14.

Užitečná rada!

V Excelu má většina problémů více řešení. Chcete-li automaticky normalizovat sloupce v Excelu, můžete použít pravé tlačítko myši. Chcete-li to provést, přesuňte kurzor na značku kurzoru na klávesnici (v buňce A2) a podržte pouze pravé tlačítko myši a přetáhněte značku podél sloupce. Po uvolnění pravého tlačítka myši se automaticky zobrazí kontextová nabídka, ze které musíte vybrat možnost „Vyplnit“. A sloupec bude automaticky vyplněn posloupností čísel, podobně jako u první metody automatického vyplňování.

1. Nyní je čas vytvořit pomocné sloupce, které výrazně pomohou efektivně třídit seznam transakcí. Musíte provést několik jednoduchých kroků:
2. Do buněk D1, E1, F1 vyplňte názvy nadpisů: „Rok“, „Měsíc“, „Den“.

• Podle každého sloupce zadejte odpovídající funkce pod nadpisy a zkopírujte je podél každého sloupce:
• D1: =ROK(B2);
• El: =MĚSÍC(B2);

F1: =DEN(B2).

Naše tabulka je připravena a poskytuje možnost provádět vícesměrné řazení transakcí podle data. Udělejme nejprve zkušební řazení, abychom se ujistili, že vše funguje.

Řekněme, že chceme seřadit data transakcí podle měsíce. V tomto případě nezáleží na pořadí dnů a let. Chcete-li to provést, jednoduše přejděte do libovolné buňky ve sloupci „Měsíc“ (E) a vyberte nástroj: „DATA“ - „Třídit a filtrovat“ - „Seřadit vzestupně“.

Nyní, chcete-li resetovat řazení a vrátit data tabulky do původní podoby, přejděte do libovolné buňky sloupce „Ne“ (A) a znovu vyberte stejný nástroj „Seřadit vzestupně“.



Jak seřadit data podle více podmínek v Excelu

Nyní můžete začít s komplexním řazením dat podle několika podmínek. Úloha je následující – transakce musí být seřazeny v následujícím pořadí:

1. Roky vzestupně.
2. Měsíce v určitých letech jsou v sestupném pořadí.
3. Dny v určitých měsících jsou v sestupném pořadí.

Způsob realizace úkolu:

V důsledku toho jsme provedli komplexní třídění dat podle několika podmínek:

Chcete-li seřadit hodnoty tabulky ve formátu data, aplikace Excel nabízí možnosti rozevíracího seznamu, jako jsou Staré na nové a Nové na staré. V praxi ale při práci s velkými objemy dat výsledek ne vždy naplní očekávání. Vzhledem k tomu, že pro Excel jsou data celá čísla, je bezpečnější a efektivnější je třídit pomocí metody popsané v tomto článku.

Nejjednodušší a nejrychlejší způsob, jak zadat aktuální datum nebo čas do buňky, je stisknout kombinaci kláves CTRL+“;“ (aktuální datum) a CTRL+SHIFT+“;“ (aktuální čas).

Mnohem efektivnější je použít funkci TODAY(). Koneckonců nejen nastavuje, ale také automaticky aktualizuje hodnotu buňky každý den bez zásahu uživatele.

Jak vložit aktuální datum do Excelu

Chcete-li vložit aktuální datum do aplikace Excel, použijte funkci TODAY(). Chcete-li to provést, vyberte nástroj "Vzorce" - "Datum a čas" - "DNES". Tato funkce nemá žádné argumenty, takže můžete do buňky jednoduše napsat „=TODAY()“ a stisknout ENTER.

Aktuální datum v buňce:

Pokud je nutné, aby buňka automaticky aktualizovala hodnotu nejen aktuálního data, ale i času, pak je lepší použít funkci “=TDATE()”.

Aktuální datum a čas v buňce.



Jak nastavit aktuální datum v Excelu na záhlaví a zápatí

Vložení aktuálního data do Excelu je implementováno několika způsoby:

1. Nastavením možností záhlaví a zápatí. Výhodou této metody je, že aktuální datum a čas jsou vyraženy na všech stránkách současně.
2. Pomocí funkce TODAY().
3. Pomocí kombinace kláves CTRL+; – pro nastavení aktuálního data a CTRL+SHIFT+; – pro nastavení aktuálního času. Nevýhoda - u této metody se při otevření dokumentu hodnota buňky automaticky neaktualizuje na aktuální indikátory. V některých případech je ale nedostatek dat výhodou.
4. Použití maker VBA pomocí funkcí v kódu programu: Date();Time();Now().

Záhlaví a zápatí umožňují nastavit aktuální datum a čas v horní nebo dolní části stránek dokumentu, který bude vytištěn na tiskárně. Zápatí nám navíc umožňuje očíslovat všechny stránky dokumentu.

Chcete-li nastavit aktuální datum v Excelu a číslování stránek pomocí záhlaví a zápatí, postupujte takto:

Záhlaví a zápatí nám umožňují více než jen nastavovat data a čísla stránek. Můžete také přidat místo pro podpis osoby odpovědné za zprávu. Upravme nyní například levou dolní část stránky v oblasti záhlaví a zápatí.

Termíny jsou často důležitou součástí analýzy dat. Často si kladete otázky jako: kdy byl produkt zakoupen, jak dlouho bude trvat dokončení úkolu v projektu nebo jaký je průměrný příjem za fiskální čtvrtletí? Správné zadání dat je nezbytné pro zajištění přesných výsledků. Abyste však těmto výsledkům správně porozuměli, musíte data naformátovat, aby byly srozumitelnější.

Důležité: Protože pravidla, jimiž se řídí, jak jsou data interpretována jakýmkoli výpočetním programem, jsou složitá, musíte být u každého záznamu co možná nejkonkrétnější. To zajistí, že výpočty data budou co nejpřesnější.

O výpočtech a formátech data

Excel ukládá data jako sekvenční čísla nazývaná sekvenční hodnoty. Například v Excelu pro Windows je 1. leden 1900 pořadové číslo 1 a 1. leden 2008 pořadové číslo 39448, protože je 39 448 dní po 1. lednu 1900.

Excel ukládá časové hodnoty jako desetinná místa, protože čas je považován za zlomek dne. Desetinné číslo je hodnota v rozsahu od 0 (nula) do 0,99999999, která představuje čas od 0:00:00 (00:00:00) do 23:59:59 (23:59:59).

Protože hodnoty data a času jsou hodnoty, lze je sčítat, odečítat a zahrnout do dalších výpočtů. Datum můžete zobrazit jako řadovou hodnotu a čas jako desetinnou hodnotu změnou formátu buňky obsahující datum nebo čas na běžný formát.

Pochopení dvou datových systémů

Excel pro Mac a Excel pro Windows podporují datové systémy 1900 a 1904 Výchozí datový systém pro Excel pro Windows je 1900; a výchozí datový systém pro Excel pro Mac je 1904.

Excel pro Windows byl původně založen na datovém systému 1900, protože zlepšil kompatibilitu s jinými tabulkovými programy vyvinutými pro operační systémy MS-DOS a Microsoft Windows, a proto se stal standardním datovým systémem. Excel pro Mac byl původně založen na datovém systému 1904, protože zlepšil kompatibilitu s dřívějšími počítači Macintosh, které nepodporovaly data před 2. lednem 1904, takže se stal výchozím datovým systémem.

Níže uvedená tabulka ukazuje první datum a datum posledního období pro každý datový systém a sekvenční význam spojený s každým datem.

Protože dva datové systémy používají různé počáteční dny, je stejné datum v každém datovém systému reprezentováno různými sériovými čísly. Například 5. červenec 2007 může mít dva různé sekvenční významy v závislosti na použitém datovém systému.

Rozdíl mezi dvěma datovými systémy je 1 462 dní; To znamená, že pořadové číslo data v datovém systému 1900 je vždy o 1 462 dní menší než sériové číslo stejného data v datovém systému 1904. Naopak pořadové číslo data v datovém systému 1904 je vždy 1 462 dní menší než sériová hodnota stejných dat v datovém systému z roku 1900 1 462 dní se rovná 4 rokům a jednomu dni (včetně jednoho přestupného dne).

Změna způsobu interpretace dvoumístného roku

Důležité: Chcete-li zajistit, aby byly hodnoty roku interpretovány správně, zadejte čtyři číslice (například 2001 namísto 01). Pokud zadáte roky se čtyřmi číslicemi, Excel nebude interpretovat století.

Pokud zadáte dvoumístné datum roku do textové buňky nebo textového argumentu ve funkci, například "=rok" ("1/1/31"), Excel interpretuje rok takto:

00 – 29 se interpretuje jako roky 2000 až 2029. Například při zadání data 5/28/19 Excel si myslí, že datum by mohlo být 28. května 2019.

od 30 do 99 interpretováno jako roky 1930 až 1999. Například při zadání data 5/28/98 Excel si myslí, že datum by mohlo být 28. května 1998.

V systému Microsoft Windows můžete změnit způsob interpretace dvoumístného roku pro všechny nainstalované programy systému Windows.

Windows 10

Ovládací panel a poté vyberte Ovládací panel.

V sekci hodiny, jazyk a region klikněte na .

Klepněte na ikonu Jazyk a regionální normy.

V dialogovém okně kraj klikněte na tlačítko Další možnosti.

Otevřete kartu Datum .

V terénu

Klepněte na tlačítko OK.

Windows 8

Vyhledávání Vyhledávání), zadejte do vyhledávacího pole Prvek ovládacího panelu Ovládací panel.

V sekci hodiny, jazyk a region klikněte na .

V dialogovém okně kraj klikněte na tlačítko Další možnosti.

Otevřete kartu Datum .

V terénu po zadání dvou číslic roku jej interpretujte jako rok mezi rámy, změna horní hranice pro oční víčko.

Se změnou horní hranice se rok automaticky změní na spodní hranici.

Klepněte na tlačítko OK.

Windows 7

Klepněte na tlačítko Start a vyberte Ovládací panel.

Vyberte položku.

V dialogovém okně kraj klikněte na tlačítko Další možnosti.

Otevřete kartu Datum .

V terénu po zadání dvou číslic roku jej interpretujte jako rok mezi rámy, změna horní hranice pro oční víčko.

Se změnou horní hranice se rok automaticky změní na spodní hranici.

Klepněte na tlačítko OK.

Změňte výchozí formát data tak, aby se rok zobrazoval jako čtyři číslice

Ve výchozím nastavení jsou při zadávání dat do sešitu data formátována tak, aby zobrazovala dva číslice roků. Když pomocí tohoto postupu změníte výchozí formát data na jiný formát, zobrazení dat dříve zadaných v sešitu se změní na nový formát, dokud nebudou formátována v dialogovém okně. Formát buňky" "(na kartě Domov ve skupině číslo klikněte na tlačítko vyvolejte dialogové okno).

Windows 10

Do vyhledávacího pole na hlavním panelu zadejte Ovládací panel a poté vyberte Ovládací panel.

V sekci hodiny, jazyk a region klikněte změnit formát data, času a čísla .

Klepněte na ikonu Jazyk a regionální normy.

V dialogovém okně kraj klikněte na tlačítko Další možnosti.

Otevřete kartu Datum .

Na krátkém seznamu Formát data

Klepněte na tlačítko OK.

Windows 8

Přejeďte prstem od pravého okraje obrazovky a klepněte na Vyhledávání(nebo pokud používáte myš, přesuňte ji do pravého horního rohu obrazovky, posuňte ji dolů a klikněte Vyhledávání), zadejte do vyhledávacího pole Prvek ovládacího panelu a poté stiskněte nebo Vybrat Ovládací panel.

V sekci hodiny, jazyk a region klikněte změnit formát data, času nebo čísla.

V dialogovém okně kraj klikněte na tlačítko Další možnosti.

Otevřete kartu Datum .

Na krátkém seznamu Formát data vyberte formát, který používá čtyři číslice pro rok (yyyy).

Klepněte na tlačítko OK.

Windows 7

Klepněte na tlačítko Start a vyberte Ovládací panel.

Vyberte položku Regionální a jazykové možnosti.

V dialogovém okně kraj klikněte na tlačítko Další možnosti.

Otevřete kartu Datum .

Na krátkém seznamu Formát data vyberte formát, který používá čtyři číslice pro rok (yyyy).

Klepněte na tlačítko OK.

Změna datového systému v Excelu

Systém dat se automaticky změní, když otevřete dokument vytvořený na jiné platformě. Pokud například pracujete v Excelu a otevřete dokument vytvořený v Excelu pro Mac, " datový systém 1904" se nainstaluje automaticky.

Systém data můžete změnit podle následujících kroků.

Vybrat Soubor > Možnosti > Navíc.

V sekci při výpočtu této knihy vyberte požadovanou knihu a poté zaškrtněte nebo zrušte zaškrtnutí políčka použít datový systém 1904 .

Problém: Problémy s daty mezi knihami, které používají různé datové systémy

Můžete narazit na problémy při kopírování a vkládání dat a při vytváření externích propojení mezi sešity založenými na dvou různých datových systémech. Data lze vytisknout o čtyři roky dříve nebo později, než je očekávané datum. K těmto problémům může dojít, pokud používáte Excel pro Windows, Excel pro Mac nebo obojí.

Pokud například zkopírujete datum 5. července 2007 ze sešitu, který používá datový systém 1900, a poté jej vložíte do sešitu, který používá datový systém 1904, datum se zobrazí jako 5. července 2011, což je 1 462 dní. později. Pokud navíc zkopírujete datum 5. července 2007 ze sešitu, který používá datový systém 1904, a poté jej vložíte do sešitu, který používá datový systém 1900, bude datum 4. července 2003, což je o 1 462 dní dříve. Obecné informace najdete v tématu Datové systémy v Excelu.

Opravte chybu kopírování a vkládání

Chcete-li nastavit datum na čtyři roky a jeden den později, přidejte k němu 1 462

List1! $A$1+1462

Chcete-li datum nastavit na čtyři roky a dříve, odečtěte od něj 1 462. Příklad

List1! $A$1-1462

Více informací

Vždy můžete položit otázku v komunitě Excel Tech Community, požádat o pomoc v komunitě odpovědí nebo navrhnout novou funkci či vylepšení na webu Excel User Voice

Excelové tabulky poskytují možnost pracovat s různými typy textových a číselných informací. K dispozici je také zpracování data. V tomto případě může být potřeba z celkové hodnoty izolovat konkrétní číslo, například rok. K tomu existují samostatné funkce: ROK, MĚSÍC, DEN a WEEKDAY.

Příklady použití funkcí pro zpracování dat v Excelu

Excelové tabulky ukládají data, která jsou reprezentována jako sekvence číselných hodnot. Začíná 1. ledna 1900. Toto datum bude odpovídat číslu 1. Navíc 1. leden 2009 je v tabulkách uveden jako číslo 39813. To je přesně počet dní mezi dvěma určenými daty.

Funkce ROK se používá podobně jako u příbuzných:

• MĚSÍC;
• DEN;

Všechny zobrazují číselné hodnoty podle gregoriánského kalendáře. I když byl kalendář Hijri vybrán pro zobrazení zadaného data v excelové tabulce, pak při izolaci roku a dalších hodnot komponent pomocí funkcí aplikace zobrazí číslo, které je ekvivalentní v systému gregoriánské chronologie.

Chcete-li použít funkci YEAR, musíte zadat následující vzorec funkce s jedním argumentem v buňce:

ROK (adresa buňky s datem v číselném formátu)

Argument funkce je povinný. Lze jej nahradit „date_in_numeric_format“. Na níže uvedených příkladech je to jasně vidět. Je důležité si uvědomit, že při zobrazení data jako textu (automatická orientace k levému okraji buňky) se funkce ROK nespustí. Jeho výsledkem bude zobrazení #HODNOTA. Formátovaná data proto musí být uvedena v číselné podobě. Dny, měsíce a roky lze oddělit tečkou, lomítkem nebo čárkou.

Podívejme se na příklad práce s funkcí ROK v Excelu. Pokud potřebujeme získat rok ze zdrojového data, funkce RIGHT nám nepomůže, protože nepracuje s daty, ale pouze s textovými a číselnými hodnotami. Pro oddělení roku, měsíce nebo dne od celého data nabízí Excel funkce pro práci s daty.

Příklad: Existuje tabulka se seznamem dat a v každém z nich je nutné oddělit hodnotu pouze roku.

Zadáme zdrojová data do Excelu.

Chcete-li problém vyřešit, musíte do buněk sloupce B zadat vzorec:

ROK (adresa buňky od data, ze kterého potřebujete extrahovat hodnotu roku)

Výsledkem je, že extrahujeme roky z každého data.

Podobný příklad funkce MONTH v Excelu:

Příklad práce s funkcemi DAY a WEEKDAY. Funkce DAY umožňuje vypočítat den libovolného dne z data:

Funkce WEEKDAY vrací číslo dne v týdnu (1-pondělí, 2-úterý... atd.) pro libovolné datum:

Ve druhém volitelném argumentu funkce WEEKDAY byste měli zadat číslo 2 pro náš formát pro počítání dne v týdnu (od pondělí-1 do neděle-7):

Pokud vynecháte druhý volitelný argument, použije se výchozí formát (anglicky Sunday-1 až Saturday-7).

Vytvořme vzorec z kombinací funkcí INDEX a WEEKDAY:

Pojďme si udělat srozumitelnější formu implementace této funkce.



Příklady praktické aplikace funkcí pro práci s daty

Tyto primitivní funkce jsou velmi užitečné při seskupování dat podle: let, měsíců, dnů v týdnu a konkrétních dnů.

Řekněme, že máme jednoduchý přehled prodeje:

Potřebujeme rychle uspořádat data pro vizuální analýzu bez použití kontingenčních tabulek. K tomu uvádíme přehled v tabulce, kde můžete pohodlně a rychle seskupit data podle roku, měsíce a dne v týdnu:

Nyní máme nástroj pro práci s touto zprávou o prodeji. Údaje můžeme filtrovat a segmentovat na základě určitých časových kritérií:

Kromě toho můžete vytvořit histogram pro analýzu nejprodávanějších dnů v týdnu, abyste pochopili, který den v týdnu představuje největší počet prodejů:

V této podobě je velmi vhodné segmentovat reporty prodeje na dlouhé, střední a krátké časové úseky.

Okamžitě stojí za zmínku, že abychom získali rozdíl mezi dvěma daty, žádná z výše popsaných funkcí nám nepomůže. Pro tento úkol byste měli použít speciálně navrženou funkci RAZNDAT:

Typ hodnot v buňkách data vyžaduje zvláštní přístup při zpracování dat. Proto byste měli v Excelu používat funkce vhodné pro tento typ.