Kako spriječiti Excel da automatski mijenja brojeve u datume

Excel je dizajniran za prepoznavanje unosa sličnih datumu, što je korisno sve dok se šifra proizvoda, razlomak, broj dijela ili identifikator tiho ne protumači kao datum. Cilj kvalitete je jednostavan: vrijednost koju unesete trebala bi ostati točno onakva vrijednost koju ste namjeravali i trebala bi i dalje biti tamo nakon što spremite, ponovno otvorite, sortirate, filtrirate ili uvezete radnu knjigu.

Za većinu ručno unesenih podataka, najpouzdanije rješenje je formatirati odredišne ​​ćelije kao tekst prije unosa ili lijepljenja vrijednosti . Microsoft posebno preporučuje ovaj pristup za unose koji sadrže kose crte ili crtice. Za povremene jednokratne vrijednosti, vodeći apostrof je brži. Za CSV ili druge uvezene podatke koristite kontrole uvoza ili Power Query kako bi se zahvaćeni stupac tretirao kao tekst, umjesto da Excel nagađa.

Prvo provjerite da Excel zapravo pretvara vrijednost

Promijenjeni prikaz ne znači uvijek isto. Ako upišete 12/2 i ćelija postane 2-Dec ili neki drugi format datuma, Excel je unos interpretirao kao datum. Točan prikaz ovisi o vašim regionalnim postavkama. Microsoft dokumentira ovo ponašanje i napominje da se unosi temeljeni na kosim crtama i crticama mogu automatski formatirati kao datumi. Pogledajte Microsoftove smjernice o zaustavljanju automatskih promjena broja u odnosu na datum .

Korisna provjera je kliknuti na ćeliju i pregledati traku formule ili privremeno promijeniti ćeliju u Općenito. Excel pohranjuje stvarne datume kao serijske vrijednosti, pa nakon što se unos pretvori u datum, jednostavnim prebacivanjem ćelije u Tekst kasnije neće se rekonstruirati izvorni znakovi koje ste upisali. Microsoft u svojoj dokumentaciji za izračun i oblikovanje datuma objašnjava da Excel pohranjuje datume kao sekvencijalne serijske brojeve .

Kako bi trebao izgledati uspješan popravak

Metoda 1: Oblikujte ćelije kao tekst prije tipkanja ili lijepljenja

Ovo je najbolja zadana vrijednost kada cijeli stupac sadrži identifikatore, a ne količine koje planirate izračunati. Primjeri uključuju brojeve dijelova, ID-ove slučajeva, kodove modela, razlomke koji moraju ostati doslovni i kodove poput 11-53.

U programu Excel za Windows odaberite ćelije, pritisnite Ctrl+1 , odaberite Tekst u dijaloškom okviru Oblikovanje ćelija i odaberite U redu. Na Macu, Microsoft dokumentira Control+1 ili Command+1 za postavke formata brojeva. U programu Excel za web odaberite raspon i upotrijebite Početna > Format brojeva > Tekst .

Zatim ponovno unesite vrijednosti. Dobar rezultat nije samo da ćelije izgledaju ispravno: kliknite nekoliko reprezentativnih ćelija i provjerite sadrži li traka formule još uvijek točno onaj željeni tekst. Ako pripremate radni list za višekratnu upotrebu, formatirajte cijeli ulazni stupac kao tekst prije distribucije datoteke.

Ova metoda ima kompromis. Tekstualne vrijednosti nisu numeričke vrijednosti, pa ih aritmetičke funkcije možda neće tretirati kao brojeve. To je obično poželjno za ID-ove i kodove, ali ne i za mjerenja ili stvarne datume. Ako stupac miješa identifikatore i brojeve koji moraju sudjelovati u izračunima, razmislite o njihovom odvajanju u različite stupce umjesto da se jedan format prisiljava da služi dvjema svrhama.

Metoda 2: Koristite vodeći apostrof za nekoliko pojedinačnih unosa

Ako trebate unijeti samo nekoliko vrijednosti sličnih datumu, upišite apostrof ispred vrijednosti, na primjer '11-53 ili '1/47 . Excel pohranjuje unos kao tekst i ne prikazuje apostrof u ćeliji. Microsoft preporučuje pristup s apostrofom za povremene unose i napominje da funkcije pretraživanja kao što su MATCH ili VLOOKUP ne tretiraju apostrof kao dio vidljive vrijednosti.

Ovo je dobra metoda kada je brzina važnija od dosljednosti na razini stupca. Postaje nezgodno kada su uključene stotine redaka, kada podaci stižu iz drugog sustava ili kada drugi korisnici mogu zaboraviti prefiks. U tim slučajevima, unaprijed formatirajte odredišni raspon ili umjesto toga kontrolirajte proces uvoza.

Metoda 3: Za doslovne razlomke upišite nulu i razmak

Ako vam je cilj unijeti matematički razlomak, a ne sačuvati tekstualni identifikator, Microsoft preporučuje upisivanje nule i razmaka ispred razlomka, kao što je 0 1/2 ili 0 3/4 . Excel tada rezultat tretira kao razlomak, a ne kao datum. Vodeća nula ne ostaje u prikazanoj ćeliji.

Ovu metodu koristite samo kada vam je zapravo potreban numerički razlomak koji se može izračunati. Ako je 1/2 šifra proizvoda ili oznaka i mora ostati točno kao tekst, umjesto toga upotrijebite oblikovanje teksta ili apostrof.

Metoda 4: Kontrola konverzija prilikom otvaranja ili uvoza CSV datoteka

CSV datoteke su čest izvor problema jer ne pohranjuju formate ćelija programa Excel. Kada Excel otvori ili uveze datoteku, može zaključiti tipove podataka iz teksta. Za ponovljive uvoze upotrijebite Podaci > Dohvati podatke > Iz datoteke > Iz teksta/CSV-a , zatim odaberite Pretvori podatke i postavite tip podataka zahvaćenog stupca na Tekst u Power Queryju. Microsoft dokumentira ovaj tijek rada u svojim smjernicama za uvoz teksta i CSV- a te objašnjava kako definirati stupac kao Tekst u dokumentaciji o tipovima podataka u Power Queryju .

Za Excel za Microsoft 365 i Excel 2024, Microsoft također nudi kontrole za automatsku pretvorbu podataka. U podržanim verzijama sustava Windows, one se nalaze pod Datoteka > Mogućnosti > Podaci . Jedna opcija može spriječiti Excel da pretvori neprekidna slova i brojeve poput JAN1 u datum. Druga opcija može upozoriti kada će CSV ili slična datoteka proći automatsku pretvorbu. Pogledajte Microsoftove mogućnosti uvoza i analize podataka .

Postoji važno ograničenje: ova postavka ne znači da se svaki uzorak sličan datumu može globalno onemogućiti. Microsoft posebno napominje da se unosi s razmacima ili drugim znakovima, kao što su JAN 1 ili JAN-1, i dalje mogu tretirati kao datumi. Njegov zaseban članak podrške za unos brojeva temeljen na kosim crtama i crticama i dalje preporučuje prethodno formatiranje ćelija kao teksta. Drugim riječima, postavke automatske pretvorbe podataka su korisne, ali formatiranje teksta ostaje sigurniji izbor kada je potrebno točno očuvanje.

Što učiniti ako je Excel već promijenio vrijednosti

Ako se pretvorba upravo dogodila, poništavanje je najčišći oporavak jer može vratiti stanje prije nego što je Excel interpretirao unos. Zatim formatirajte ciljne ćelije kao tekst i ponovno unesite ili zalijepite izvorne podatke.

Ako je radna knjiga već spremljena i izvorne vrijednosti više nisu dostupne, promjena formata ćelije u tekst neće pouzdano oporaviti ono što je izvorno uneseno. Nakon što Excel pohrani prepoznati datum kao serijsku vrijednost, više izvornih nizova moglo bi se potencijalno mapirati na isti datum, ovisno o lokaciji i formatiranju. Oporavite vrijednosti iz izvornog CSV-a, izvoza, baze podataka, e-pošte ili drugog izvornog sustava kad god je točnost važna.

Za veliki skup podataka nemojte ručno pogađati stotine izvornih kodova iz prikazanih datuma. Ponovno uvezite izvor s ispravnom vrstom teksta, a zatim usporedite broj redaka i uzorak poznatih identifikatora. To je brže za reviziju i manja je vjerojatnost da će se pojaviti nove pogreške.

Kada biste trebali prijeći na drugačiji pristup?

• Ostanite s formatiranjem teksta kada je stupac prvenstveno ID-ovi, kodovi, oznake ili doslovni nizovi.
• Apostrof koristite kada je potrebno zaštititi samo nekoliko pojedinačnih unosa.
• Koristite 0 plus razmak kada je vrijednost realni numerički razlomak, a ne identifikator.
• Koristite Power Query kada opetovano uvozite CSV, TXT, JSON, web ili druge strukturirane podatke i potrebno vam je pravilo tipa koje se može reproducira.
• Koristite kontrole za automatsku pretvorbu podataka kada imate Microsoft 365 ili Excel 2024 i neželjena pretvorba odgovara jednoj od pretvorbi koje Microsoft nudi kao opciju.

Kontrolna lista kvalitete prije nego što smatrate da je problem riješen

• Unesite barem tri problematična primjera, uključujući vrijednost kose crte i vrijednost crtice.
• Potvrdite da vidljiva ćelija i traka formule prikazuju željene znakove.
• Spremite, zatvorite i ponovno otvorite radnu knjigu.
• Ako podaci dolaze iz CSV-a, ponovite stvarni put uvoza umjesto da testirate samo ručno tipkanje.
• Provjerite formule ili pretraživanja koja ovise o vrijednostima; tekstualne i numeričke/datumske vrijednosti su različite vrste podataka.
• Prije popravka već pretvorenih podataka, sačuvajte kopiju izvorne datoteke.

Zaključak

Ako je cilj točno očuvanje, najbolji rezultat dolazi od određivanja vrste podataka prije nego što Excel vidi vrijednosti. Unaprijed formatirajte identifikatore kao tekst, koristite apostrof za povremene unose i definirajte uvezene stupce kao tekst u Power Queryju. Novije postavke automatske pretvorbe podataka mogu smanjiti neke neželjene pretvorbe, ali nisu univerzalni prekidač za svaki obrazac sličan datumu.

Završni test je praktičan: vaša vrijednost treba ostati nepromijenjena nakon unosa, uvoza, spremanja, ponovnog otvaranja i bilo kojeg pretraživanja koje ovisi o njoj. Ako se to ne dogodi, promijenite metodu unosa umjesto da više puta ispravljate prikaz nakon što se konverzija već dogodila.