Защо Ексел превръща числата в дати?

Днес ще разясним един проблем, който се е случвал поне веднъж на всички нас, които работим ежедневно с Ексел.

Excel ми превръща числата в дати …. ЗАЩО? Как да го оправя? Помогнете… :/

Получавали сме този въпрос многократно във форума на Аула, и съответно сме помагали за разрешаването му.

За да достигне решението на случая до още повече хора решихме да го споделим и тук в блога на Аула.

Кога се получава този проблем? И какво е решението му? – ще видите в това видео:

 

Ето в няколко стъпки решението на този толкова често срещан проблем в Excel, а именно: „Защо Excel превръща числата в дати?“

 

Имаме един документ, направен на Excel, в който си водим отчет на разходите за ток, вода и т.н. по месеци.

Нека започнем да нанасямe сумите, които сме заплатили за съответните месеци.

Въвеждаме число!

Започваме да нанасяме стойности за месец Януари, нанасяме в сметката за ток 12.15 . Натискаме Enter.

Какво получаваме обаче!?!

Вместо числова стойност Ексел, записва в клетка дек.15 .

„Какво се случва и как да го оправя?“

Това са въпросите, които си задаваме и изпадаме в големи терзания.

Ето отговор на въпроса:
„Какво се случва?“

Първопричината за проблема идва най – вероятно при нанасянето на стойностите на сметката за ток за месец Януари. Вместо запетая (,), използвахме точка (.), а регионалната настройка (или ръчно настроената само за Ексел) – десетичен разделител, е запетая (,). Тази настройка е довела до възприемането на точката (.) от Ексел не като десетичен разделител на числова стойност, а като разделител на друга категория формат; в конкретния, а и в 99 % от случаите на всички колеги – дата.

Така вместо числото в клетката, се записва датата, която Ексел предполага, че сме искали да запишем. В конкретния случай стойността е 12.15. и Excel смята, че съм задал 12-ти месец (дек.) и съответно  15 е възприето за 2015 година.

Какво трябва да направим?

I.

Първо, ще проверим дали твърдението ни описано горе наистина е вярно.

За да проверим каква точно е настройката на десетичния сепаратор, нека нарпавим следното:

1. Кликваме на Start бутона на Windows – а;
2. В лентата за търсене изписваме Control Panel
3. Избираме Control Panel

След което в новоотворения прозорец влизаме в раздела Clock, Language and Region.

След това в новоотворения прозорец:

1. Кликваме на  Region;
2. В отворилият се прозорец Region отиваме на раздела Formats
3. В раздела Formats кликваме на бутона Additional settings…
4. В новоотворилия се прозорец Customize Format, отиваме на раздела Numbers;
5. За Decimal Symbol / в нашият случай зададеният десетичният разделител е (,), което означава, че предположението ни, че задавайки ( . ) вместо ( , ), Ексел е възприел стойността не като число, а като дата.

II.

Проверка настройката на Ексел.

Нека проверим каква е и настройката за десетичен разделител на самия Excel.

За целта да отидем на бутона File.

След това отиваме в раздела Options.

В новоотворения прозорец Word Options (позиция 1) нека да отидем на раздела Advanced  (позиция 2) и в раздела Editing Options (позиция 3) и да проверим какво е зададено на Use system separator (позиция 4). В случая е зададена отметка, което означава, че Ексел взима за десетичен разделител регионалните настройки, които ние проверихме, и съответно там за разделител беше посочен запетайка (,).

Намерихме къде е проблема, как да го разрешим?

Ето го и решението:

Отново сме в раздела Advanced в опциите на Ексел (Excel Option).

1. Махаме отметката на Use system separators.Така вече десетичния разделител ще бъде зададен от нас и няма да се контролира от регионалните настройки на компютъра, т.e. ние ще зададем десетичен разделител на локално ниво – само за Excel.
2. За Decimal separator (десетичен разделител) ще зададем точка (.). Така вече когато изпишем 12.15 следва да получим число, а НЕ дата.
3. За да приложим промените затваряме прозореца Excel Option с ОК.

Решихме ли напълно проблема?

НЕ!

Макар да направихме промените така, че точката (.) да бъде десетичен разделител, ТО пак в клетката ни е записано дек.15.

Проблемът идва от това, че формата на клетките е custom (по точно определен формат на дата). Станал е custom поради факта, че преди това е бил Genеral и веднъж възприел Excel формата за дата, в тази клетка той прави формата Custom и с каквито и да било сепаратори ( (,) , (.), (/) ), ще възприема стойността за дата и ще си я превръща по негово усмотрение.

1. Кликваме на стрелкичката в панела Number, за да отворим прозореца Format Cells;
2. Отиваме на раздела Number;
3. Настройката е Custom (персонална настройка) ;
4. Съответно виждаме че тази настройка е направена така, че форматирането в клетката да е тип „Дата“.

Как да се справим сега?

Изтриваме въведените стойности за месец Януари.

1. Маркираме целия диапазон, в който предполагаме, че ще задаваме само числови стойности;
2. Кликваме на стрелкичката, посочена на позиция 2 на снимката;
3. Кликваме в отворилия се прозорец (Format Cells);
4. Кликваме на раздела Number;
5. Задаваме формат за избрания диапазон да е Number;
6. За да потвърдим направените настройки избираме ОК.

 

Дали най-накрая решихме проблема?

Да видим: 🙂

Въвеждаме отново стойността 12.15, като използваме десетичен разделител (.) точка.

ДА!

Вече получаваме числова стойност, а не дата.

Сигурни ли сме?

За да сме сигурни нека да попълним реда със сметките за ток, за всички месеци и да ги сумираме.

 

След въвеждане на формулата за сумиране получихме сума от числовите стойности.

Да, определено имаме числови стойности, а не дати, текст или някакъв друг формат стойности.

 

Ако този урок ви е харесал, разгледайте нашият курс Тайните на Excel,  в който има множество трикове, които изглеждат за напреднали, но са много лесни и полезни в практиката. Разгледайте курса може да ви е много полезен.

 

Ако тази статия Ви е била полезна и интересна, може да я споделите и със своите приятели във Facebook. 🙂