Jak zabránit automatické změně čísel na data v Excelu

Excel je navržen tak, aby rozpoznával položky podobné datu, což je užitečné, dokud není kód produktu, zlomek, číslo dílu nebo identifikátor tiše interpretován jako datum. Cíl kvality je jednoduchý: zadaná hodnota by měla zůstat přesně taková, jakou jste zamýšleli, a měla by tam zůstat i po uložení, opětovném otevření, seřazení, filtrování nebo importu sešitu.

U většiny ručně zadávaných dat je nejspolehlivějším řešením formátovat cílové buňky jako text před zadáním nebo vložením hodnot . Společnost Microsoft tento přístup konkrétně doporučuje pro položky, které obsahují lomítka nebo pomlčky. Pro občasné jednorázové hodnoty je rychlejší použití úvodního apostrofu. Pro CSV nebo jiná importovaná data použijte ovládací prvky importu nebo Power Query, aby byl dotčený sloupec považován za text, a nenechávejte Excel hádat.

Nejprve se ujistěte, že Excel skutečně převádí hodnotu

Změněné zobrazení neznamená vždy totéž. Pokud zadáte 12/2 a buňka se změní na 2-Dec nebo jiný formát data, Excel interpretoval položku jako datum. Přesné zobrazení závisí na vašem místním nastavení. Společnost Microsoft toto chování dokumentuje a uvádí, že položky založené na lomítkách a pomlčkách lze automaticky formátovat jako data. Viz pokyny společnosti Microsoft k zastavení automatických změn čísla od data .

Užitečným ověřením je kliknout na buňku a zkontrolovat řádek vzorců nebo dočasně změnit buňku na Obecné. Excel ukládá skutečná data jako pořadová čísla, takže jakmile je položka převedena na datum, pouhé pozdější přepnutí buňky na Text nerekonstruuje původní zadané znaky. Společnost Microsoft ve své dokumentaci k výpočtu a formátování data vysvětluje, že Excel ukládá data jako pořadová čísla .

Jak by měla vypadat úspěšná oprava

Metoda 1: Před psaním nebo vkládáním naformátujte buňky jako text

Toto je nejlepší výchozí nastavení, pokud celý sloupec obsahuje identifikátory, nikoli množství, která plánujete vypočítat. Příklady zahrnují čísla dílů, ID případů, kódy modelů, zlomky, které musí zůstat doslovné, a kódy jako například 11–53.

V Excelu pro Windows vyberte buňky, stiskněte Ctrl+1 , v dialogovém okně Formát buněk vyberte Text a klikněte na OK. Na Macu dokumentuje Microsoft Ctrl+1 nebo Command+1 pro nastavení formátu čísel. V Excelu pro web vyberte oblast a použijte Domů > Formát čísla > Text .

Poté zadejte hodnoty znovu. Dobrým výsledkem není jen to, že buňky vypadají správně: klikněte na několik reprezentativních buněk a ověřte, zda řádek vzorců stále obsahuje přesně zamýšlený text. Pokud připravujete opakovaně použitelný list, naformátujte před distribucí souboru celý vstupní sloupec jako Text.

Tato metoda má jeden kompromis. Textové hodnoty nejsou číselné hodnoty, takže aritmetické funkce s nimi nemusí zacházet jako s čísly. To je obvykle žádoucí pro ID a kódy, ale ne pro měření nebo skutečná data. Pokud sloupec obsahuje identifikátory a čísla, která se musí účastnit výpočtů, zvažte jejich oddělení do různých sloupců, místo abyste vynucovali jeden formát pro dva účely.

Metoda 2: Použití úvodního apostrofu pro několik jednotlivých položek

Pokud potřebujete zadat pouze několik hodnot podobných datu, zadejte před hodnotu apostrof, například '11-53 nebo '1/47 . Excel uloží položku jako text a apostrof v buňce nezobrazí. Společnost Microsoft doporučuje použití apostrofu pro občasné položky a poznamenává, že vyhledávací funkce, jako je POZVYHLEDAT nebo VLOOKUP, nepovažují apostrof za součást viditelné hodnoty.

Toto je dobrá metoda, když je rychlost důležitější než konzistence v rámci sloupců. Stává se to nepraktickým, když se jedná o stovky řádků, když data přicházejí z jiného systému nebo když ostatní uživatelé zapomenou prefix. V takových případech raději předformátujte cílový rozsah nebo ovládejte proces importu.

Metoda 3: Pro zlomky ve formě literálů zadejte nulu a mezeru.

Pokud je vaším cílem zadat matematický zlomek, nikoli zachovat textový identifikátor, společnost Microsoft doporučuje zadat před zlomek nulu a mezeru, například 0 1/2 nebo 0 3/4 . Excel pak výsledek považuje za zlomek, nikoli za datum. Úvodní nula v zobrazené buňce nezůstane.

Tuto metodu použijte pouze tehdy, pokud skutečně chcete číselný zlomek, který lze vypočítat. Pokud je 1/2 kód produktu nebo popisek a musí zůstat přesně jako text, použijte místo toho formátování textu nebo apostrof.

Metoda 4: Řízení konverzí při otevírání nebo importu souborů CSV

Soubory CSV jsou častým zdrojem problémů, protože neukládají formáty buněk aplikace Excel. Když Excel soubor otevře nebo importuje, může z textu odvodit datové typy. Pro opakovatelné importy použijte Data > Získat data > Ze souboru > Z textu/CSV , poté zvolte Transformovat data a nastavte datový typ příslušného sloupce na Text v Power Query. Společnost Microsoft tento pracovní postup dokumentuje ve svých pokynech k importu textu a CSV a vysvětluje, jak definovat sloupec jako Text v dokumentaci k datovým typům Power Query .

Pro Excel pro Microsoft 365 a Excel 2024 nabízí společnost Microsoft také ovládací prvky pro automatický převod dat. V podporovaných verzích systému Windows se nacházejí v nabídce Soubor > Možnosti > Data . Jedna možnost může zabránit aplikaci Excel v převodu souvislých písmen a číslic, například JAN1 , na datum. Další možnost může upozornit, že se u souboru CSV nebo podobného souboru chystá automatický převod. Viz Možnosti importu a analýzy dat od společnosti Microsoft .

Existuje důležité omezení: toto nastavení neznamená , že každý vzor podobný datu lze globálně zakázat. Společnost Microsoft výslovně upozorňuje, že položky s mezerami nebo jinými znaky, jako například JAN 1 nebo JAN-1, mohou být stále považovány za data. Samostatný článek podpory pro zadávání čísel pomocí lomítka a pomlčky i nadále doporučuje předformátování buněk jako text. Jinými slovy, nastavení automatického převodu dat je užitečné, ale formátování textu zůstává bezpečnější volbou, pokud je vyžadováno přesné zachování.

Co dělat, když Excel již hodnoty změnil

Pokud k převodu právě došlo, je funkce Zpět nejčistší obnovou, protože dokáže obnovit stav před interpretací položky v Excelu. Poté naformátujte cílové buňky jako text a znovu zadejte nebo vložte původní data.

Pokud byl sešit již uložen a zdrojové hodnoty již nejsou k dispozici, změna formátu buňky na text nespolehlivě obnoví původně zadaný text. Jakmile Excel uloží rozpoznané datum jako pořadové číslo, může se na stejné datum v závislosti na národním prostředí a formátování mapovat více původních řetězců. V případě potřeby obnovte hodnoty z původního souboru CSV, exportu, databáze, e-mailu nebo jiného zdrojového systému, kdykoli je důležitá přesnost.

U velké datové sady nehádejte ručně stovky původních kódů ze zobrazených dat. Znovu importujte zdroj se správným typem textu a poté porovnejte počty řádků a vzorek známých identifikátorů. Audit se tak urychlí a s menší pravděpodobností se zavedou nové chyby.

Kdy byste měli přejít na jiný přístup?

• Pokud sloupec obsahuje primárně ID, kódy, popisky nebo literální řetězce, ponechte formátování textu .
• Apostrof použijte, pokud je potřeba chránit pouze několik jednotlivých záznamů.
• Pokud je hodnota zlomek reálného čísla, nikoli identifikátor, použijte 0 plus mezeru .
• Použijte Power Query , když opakovaně importujete CSV, TXT, JSON, webová nebo jiná strukturovaná data a potřebujete reprodukovatelné typové pravidlo.
• Ovládací prvky automatického převodu dat použijte, pokud máte Microsoft 365 nebo Excel 2024 a nežádoucí převod odpovídá jednomu z převodů, které Microsoft nabízí jako možnost.

Kontrolní seznam kvality, než problém považujete za vyřešený

• Uveďte alespoň tři problematické příklady, včetně hodnoty s lomítkem a hodnoty s pomlčkou.
• Ověřte, zda viditelná buňka i řádek vzorců zobrazují požadované znaky.
• Uložte, zavřete a znovu otevřete sešit.
• Pokud data pocházejí z CSV, opakujte skutečnou cestu importu, místo abyste testovali pouze ruční psaní.
• Zkontrolujte vzorce nebo vyhledávání, které závisí na hodnotách; textové a číselné/datumové hodnoty jsou různé datové typy.
• Před opravou již převedených dat si uschovejte kopii původního zdrojového souboru.

Sečteno a podtrženo

Pokud je cílem přesné zachování, nejlepší výsledek dosáhnete, když určíte datový typ dříve, než Excel hodnoty uvidí. Identifikátory předformátujte jako text, pro občasné položky používejte apostrof a importované sloupce definujte jako text v Power Query. Novější nastavení automatického převodu dat mohou omezit některé nežádoucí převody, ale nejsou univerzálním vypínačem pro všechny datové vzorce.

Závěrečný test je praktický: vaše hodnota by měla zůstat nezměněna po zadání, importu, uložení, opětovném otevření a jakémkoli následném vyhledávání, které na ní závisí. Pokud se tak nestane, změňte metodu příjmu, místo abyste opakovaně opravovali zobrazení po provedení konverze.