Дуже часто можна зіткнутися з такою проблемою: коли ви вставляєте в комірки Excel числа, в яких як десятковий роздільник використовується точка, то деякі такі числа Excel перетворює в дати, а інші візуально схожі на ті ж числа, але насправді є текстом, з яким неможливо працювати надалі як із числами.

 

Причини цього:

1. Невідповідність регіонального формату вихідних даних регіональним параметрам вашої операційної системи. У вихідних даних як десятковий роздільник використовується точка (американський формат), а у вас – регіональні налаштування, в яких десятковим роздільником є кома, а точка сприймається як роздільник окремих складових дати – день, місяць, рік (наприклад, 17.09.202).
2. Особлива поведінка Excel для осередків у «Загальному» числовому форматі (який встановлений за замовчуванням): якщо вводити в комірку щось схоже на число, то таке значення зберігається як число, якщо вводити щось, схоже на дату – таке значення зберігається як дата. Якщо значення, що вводиться, не схоже ні на число, ні на дату – то це текст.

У цьому прикладі (вище) пошкоджені дані:

Вихідні дані збереглися як текст:

• Комірка D3 - вихідне значення (179.3287) мало схоже на дату, так як номер місяця 179 не існує (а ось рік 3287 можливий, у далекому майбутньому).
• Комірка D4 – вихідне значення схоже на дату (липень 1275). Однак у Excel точкою відліку дат є 1 січня 1900 року. Тому число 1275 (раніше 1900) не сприймається як рік.

Вихідні дані були перетворені на дати (і, відповідно, втрачені):

• Комірка D5 – у вихідному значенні (7.1972) число до точки схоже на номер місяця 7 (липень), а число після точки – на рік 1972 (насправді відбулося перетворення на дату 01.07.1972)
• Коміркак D6 – у вихідному значенні (11.2024) число до точки сприймається як номер місяця (листопад), а після точки – рік 2024 (відбулося перетворення на дату 01.11.2024)

Які є способи вирішення цієї проблеми?

Почнемо з найкращих і, відповідно, не найкращих.

 

Спосіб-1. Змінити регіональні налаштування операційної системи (на американські).
 

Так, але така глобальна зміна вплине на роботу інших додатків і служб (не тільки Excel).

Спосіб-2. Локально в Excel (в параметрах Excel) заздалегідь змінити десятковий роздільник із комою на точку:

 

 

Спосіб-3. Заздалегідь встановити «текстовий» формат осередків (замість «загального»), до яких вставлятимуться значення.

Тоді ми позбавимося автоперетворення «чисел», схожих на дати, безпосередньо в дати, і отримаємо значення у вихідному вигляді, тільки у форматі тексту. Далі цей текст легко буде перетворити на числа за допомогою простої формули:

=SUBSTITUTE(A3;".";",")*1 (в діапазоні C3:C9)

або ще краще, формули:

=NUMBERVALUE(A3;".") (в діапазоні E3:E9)

Російські варіанти цих формул:

=ПОДСТАВИТЬA3;".";",")*1

і

=ЧЗНАЧ(A3;"."),

відповідно

 

Спосіб-4. Формулою (без жодної попередньої підготовки):

Можна спробувати відновити вже зіпсовані числа формулою

Формула в комірці G3:

=1*IF(CELL("format";D3)="G"; SUBSTITUTE(D3;".";","); TEXT(D3;"M,YYYY"))

або російський варіант:

=1*ЕСЛИ(ЯЧЕЙКА("формат";D3)="G"; ПОДСТАВИТЬ(D3;".";","); ТЕКСТ(D3;"М,ГГГГ"))

 

Формула працює наступним чином:

CELL() (комірка()) – функція з категорії «Інформаційні». Якщо перший її аргумент (тип інформації) вказано "format", то результат роботи функції - "G" - для тексту чи чисел і "D3" - для дат.

Якщо в комірці знаходиться текст, то функція SUBSTITUTE() (ПОДСТАВИТЬ()) замінює точку на кому і множення на 1 на початку формули (застосування арифметичної операції) призводить до перетворення тексту, що складається з цифр з комою, - в число.

Якщо в комірці не текст (а дата), то функція TEXT() (ТЕКСТ()) перетворює дату в текст відповідно до коду формату, вказаного як другий аргумент. І далі такий текст легко перетворюється на число шляхом множення на 1 (на початку формули).

Використовуючи код формату функції TEXT, потрібно враховувати те, що і навіть в англомовній версії, залежно від регіональних налаштувань, замість “M,YYYY”, можливо, потрібно вказувати “М,РРРР” (де “M” – кириличне).

 

ВАЖЛИВО! Формула не є універсальним рішенням (на всі випадки життя)

Вона добре працює, якщо після точки знаходиться від 3-х чи більше цифр.

А що, якщо маємо таке:

У перших трьох осередках вже цифри до крапки сприймаються як номер дня (а не номер місяця), а цифри після крапки – як номер місяця (а не номер року).

А в останніх двох осередках різні вихідні числа призвели до отримання однієї й тієї ж дати.

Якщо в першому випадку можна було б ще додати перевірку додаткових умов у формулі, то в останньому – вихідні значення безповоротно втрачені і дізнатися які вони були – вже неможливо. Очевидно, в такому разі, єдиний варіант – це не допустити автоматичного перетворення «ніби чисел» у дати, тобто зазначений вище спосіб 3 – чи не єдине рішення (крім кардинальних змін регіональних налаштувань).

 

Ці та подібні тонкощі роботи в Excel ми проробляємо на курсі "Excel бізнес-аналіз та прогнозування" 

За 27 годин вивчення Excel у вас не залишиться жодних питань щодо його роботи, а якщо вони і виникнуть, то наші тренери підтримки онлайн допоможуть з відповідями.

 

Автор статті CEO та тренер DATAbi Євген Довженко

 

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"