Хитрости »

Как Excel воспринимает данные?

Ячейка в Excel может содержать следующие типы данных: дата, время, число, текст, формула

ТЕКСТ И ФОРМУЛЫ
Любой произвольный набор символов, если он не представляет собой число или дату, будет воспринят как текст: «привет», «12 штук».
Если данных в ячейке начинаются со знака =, -, + - Excel попытается вычислить выражение, следующее за знаком, т.к. посчитает это формулой.

ЧИСЛА, ПРОЦЕНТЫ, ДАТА, ВРЕМЯ

• ЧИСЛА
  Для представления чисел в Excel используется 15 цифр. Если введено число длиной более 15 цифр (как до, так и после десятичной запятой), Excel сохранит его с точностью до 15 цифр, заменив оставшиеся разряды нулями. Например, если в ячейку с общим или числовым форматом ввести число: 123456789123456789, то последние три цифры(789) будут заменены на нули: 123456789123456000.
  Очень большие числа автоматически представляются в экспоненциальном формате: 1,2Е+19 = 1,2*1019 или 1,2Е-19 = 1,2*10-19. Наибольшее положительное число 9,9Е+307; наименьшее положительное число 1 Е-307 (числа, большие 9,9Е+307 становятся текстом, меньшие 1Е-307 – нулем)
  Для определения целой и дробной частей чисел используется разделитель: в российском языковом стандарте это запятая; в английском – точка
  Возможность Excel работать только с числами не более 15-ти знаков и такого числа разрядов после запятой порождает некоторые недопонимания в расчетах со стороны пользователей: Excel неправильно считает. Почему?
• ПРОЦЕНТЫ
  Главное здесь помнить, что 100% это по сути 1. Отсюда вывод - проценты это те же числа, только при вводе в ячейку значения со знаком процента(%) Excel сам автоматом делит введенное число на 100 и присваивает формат "Процентный", который визуально уже обратно умножает на 100. Но это только виртуальное умножение, на самом деле в ячейке хранится уже поделенное число. Т.е. если мы введем в ячейку значение 20%, то Excel запомнит его как значение 0,2 и применит процентный формат. И дальнейшие расчеты будут вестись именно со значением 0,2.

• ДАТА
  Дата для Excel это тоже число, только представленное иным форматом.
  В Excel отсчет даты и времени начинается с 01.01.1900. Для компьютеров под управлением Macintosh в Excel отсчет начинается с 02.01.1904. Для корректной совместимости с такими компьютерами в Excel предусмотрена возможность включения исчисления дат 1904: Файл→ Параметры→ Дополнительно→ Использовать систему дат 1904 (File→ Options→ Advanced→ Use 1904 date system).
  Если в ячейку ввести число 43587 и присвоить этой ячейке формат Даты - "ДД.ММ.ГГГГ" (правая кнопка мыши по ячейке→ Формат ячеек→ вкладка Число→ Дата (Format Cells→ вкладка Number→ Date)), то в ячейке будет отображено 02.05.2019. Т.е. 43587 в переводе на дату равно 02.05.2019.
  43587 - это количество дней, прошедших с даты 01.01.1900. Следовательно 1 - это одни целые сутки.

• ВРЕМЯ
  Т.к. целые числа для Excel это даты, то логично предположить, что дробная часть чисел будет восприниматься как время. Например, 0,5 = 12:00, а 0,124 — 2:58:34.
  Следовательно, если записать в ячейку 43587,124 и назначить формат "ДД.ММ.ГГГГ чч:мм:сс", то в перевод на дату/время это будет - 02.05.2019 2:58:34.

  По умолчанию, если количество часов больше 23, введенное время преобразуется в формат «дата время». Например, 24:12:15 автоматом будет отражено как 01.01.1900 0:12:15
  Чтобы увидеть сумму часов, а не дата+время, необходимо для ячейки установить формат "[ч]:мм:сс"(ч в квадратных скобках [ч] говорит Excel, что необходимо отображать суммарно часы, а не превращать их в дни)

---

Именно такое хранение Excel-ем дат и времени дает нам возможность производить с датами и временем математические операции. Например, можно узнать, какая дата будет через 7 дней:
=A1+7
где в A1 записана текущая дата =СЕГОДНЯ()
Или объединить ячейки с датой и временем так, чтобы получилась одна ячейка в формате даты-времени. Например, в столбце А у нас записаны даты, а в столбце B - время. И необходимо в столбце С получить дату и время вместе. Использовать для этого текстовые функции(вроде СЦЕПИТЬ(CONCATENATE) или амперсанда(&)) не получится, т.к. это все же числа. И правильно их именно складывать, а не объединять:
=A1+B1
где
A1 - дата
B1 - время
Если же необходимо именно объединять дату и произвольный текст(="Отчет по магазину за "&B1, где в B1 - дата), то можно получить совсем не то, что ожидалось. Подробнее об этом и о том, как это можно исправить я описывал в статье: Видимое значение ячейки в реальное

---

Важно знать, что при изменении данных, Excel автоматически пытается определить тип вводимых данных и преобразовать их в максимально приближенный формат (на свое усмотрение). Порой это может сыграть очень плохую шутку. Например, если попытаться в русской локализации записать в ячейку текст вида 2.1, то Excel непременно преобразует его в дату - 02.янв. Причина тому тот факт, что разделителем целой и дробной части является запятая, а разделителем дней, месяцев и лет в дате – точка. Так же подобное преобразование может происходить и при вводе значения вроде "02-12" - Excel переведет его в дату "02.дек". В данном случае дату мы получаем потому, что Excel понимает формат даты "ДД-ММ-ГГ" и умеет распознавать в нем именно дату. Чтобы Excel не делал подобных преобразований необходимо перед внесением данных установить формат ячейки — Текстовый (Text) и только после этого вводить в ячейку данные. Или ставить перед такими числами апостроф — '2.1
Так же преобразование в число будет происходить, если мы постараемся ввести в ячейку значение, которое Excel может в него преобразовать(не только дата). Например, когда необходимо записать числа с ведущими нулями(вроде такого "00023543"). Как правило это артикулы и различные ID, но может быть что угодно. Так вот, при вводе в ячейку с общим или любым числовым форматом значения "00023543", Excel автоматом преобразует это значение в 23543, т.е. удалит ведущие нули. При этом сразу эту ошибку можно и не заметить, если при этом для ячейки установлен числовой формат вроде Табельный номер или просто такой: "00000000". Иногда это действительно лучше, т.к. в ячейке на самом деле число, а отображается как некий ID. Но проблема в том, что тогда отображаемое значение "00023543" не будет равно такому же текстовому значению, т.к. фактически там число 23543. И если необходимо этого избежать, то здесь так же необходимо сначала назначить ячейке формат Текстовый (Text), и только после этого начинать вводить данные в эти ячейки.

Есть и другой вариант: когда вы открываете файл(в основном это относится к текстовым файлам и CSV), в котором уже есть подобные "числа"(2.1, 5.10 и т.п.). Они уже в момент открытия в большинстве случаев будут преобразованы в даты и менять формат ячеек уже поздно. Но можно попробовать схитрить и установить на время открытия таких файлов разделитель целой и дробной части - точка. Сделать это можно через Параметры:

• Excel 2003: меню Сервис -Параметры... -вкладка Международные
• Excel 2007: Кнопка Офис -Параметры(Excel Options) -Дополнительно(Advanced)
• Excel 2010 и выше: Файл(File) -Параметры(Options) -Дополнительно(Advanced)

Снять галку с "Использовать системные разделители(Use system separators)" и установить в полях ниже свои. Как правило помогает установка в качестве разделителя целой и дробной части(decimal separator) точки вместо запятой.