Lost your password?

Хитрости »

Как 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 (FileOptionsAdvancedUse 1904 date system).
    Если в ячейку ввести число 43587 и присвоить этой ячейке формат Даты - "ДД.ММ.ГГГГ" (правая кнопка мыши по ячейке→ Формат ячеек→ вкладка ЧислоДата (Format Cells→ вкладка NumberDate)), то в ячейке будет отображено 02.05.2019. Т.е. 43587 в переводе на дату равно 02.05.2019.
    43587 - это количество дней, прошедших с даты 01.01.1900. Следовательно 1 - это одни целые сутки.

  • ВРЕМЯ
    Т.к. целые числа для Excel это даты, то логично предположить, что дробная часть чисел будет восприниматься как время. Например, 0,5 = 12:00, а 0,1242: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) точки вместо запятой.


Статья помогла? Сделай твит, поделись ссылкой с друзьями!
Обсуждение: 8 комментариев
  1. Юрий:

    Супер! Спасибо большое. Все оказывается так просто.

  2. Екатерина:

    Спасибо!! Очень полезная штука!!

  3. Михаил:

    А как быть, если данные вставляются в таблицу методом Copy-Paste, да к тому же из другого приложения?
    Мне ничего не помогает. Так данные 2.5 Excel 2007 воспринимает только как дату, изменяя формат ячейки и записывая 02.май, а при повторном изменении формата на текстовый изменяет значение на 41031

  4. Михаил, внимательно прочитайте пару последних абзацев статьи - там описывается этот случай. Сначала измените формат ячеек на Текстовый, а затем вставляйте данные через Специальную вставку и формат не будет изменяться автоматом.

  5. Сергей Б.:

    При использовании ссылки на ячейку с датой в формуле СЦЕПИТЬ - результат выдает в числовом формате даты. Ведь мне необходимо чтобы в итоге получалась текстовая строка с датой а не числом...

  6. Вопрос мало отношения имеет к теме. СЦЕПИТЬ всегда возвращает результатом текст. Следовательно у Вас в ячейке получается текст, хоть и выглядит как число. А вообще Ваш вопрос малоинформативен.

  7. Анна:

    Спасибо!

  8. Аня:

    Спасибо. Интересно!

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти