Перейти к содержимому
8 (495) 660-36-72 8 (800) 600-36-72 (по РФ бесплатно)

Формат ячеек в Excel. Как отличить число, дату и код с нулями в начале

Формат ячейки Excel определяет, как выглядит значение. Число 127 можно показать как 00127, а долю 0,12 — как 12 %. Но для артикула с обязательными нулями и скидки, которая участвует в расчёте, нужны разные решения. Проверим их на небольшой накладной.
Формат ячеек в Excel. Как отличить число, дату и код с нулями в начале

Начните с роли каждого столбца

В накладной есть артикул, количество, дата и скидка. У всех четырёх значений могут быть цифры, но только количество имеет смысл складывать по строкам. Дата участвует в вычислении срока, скидка умножается на цену, а артикул нужен для точного поиска товара. Если заранее назвать назначение столбца, выбор формата станет понятнее, чем при настройке ячеек по внешнему виду.

На пустом листе заведите четыре такие колонки. Для артикула выберите пример «00127», для количества возьмите восемь упаковок, для скидки — двенадцать процентов. Дату поставки возьмите любую знакомую и введите в принятом у вас региональном формате. Эту маленькую копию легко исправить, если Excel распознает ввод неожиданно. Исходную накладную для эксперимента менять не требуется.

Что покажет строка формул

Щёлкните ячейку один раз и сравните то, что видите на листе, с полем строки формул. Для числового артикула с пользовательской маской вы можете увидеть на листе «00127», а сверху — «127». Так заметна разница между самим числом и его представлением. Если ячейка содержит формулу, в строке формул вы увидите её выражение, тогда как на листе будет результат вычисления.

Строка формул не всегда показывает внутреннее машинное представление буквально. Например, для распознанной даты Excel может показать дату в привычном виде. Поэтому отдельная проверка нужна, когда важно понять, можно ли с данными считать. Создайте рядом временную формулу или измените формат учебной копии ячейки на общий. Дата, которую программа действительно распознала, может отобразиться числовым порядковым значением. Текстовая запись от такой смены не станет датой.

Где открыть форматы в русской версии Excel

В настольном Excel для Windows выделите нужную ячейку или диапазон. На вкладке «Главная» найдите группу «Число». Там удобно выбрать распространённые варианты. Для подробных настроек откройте окно «Формат ячеек», например сочетанием Ctrl и 1, и перейдите на вкладку «Число». Названия отдельных пунктов зависят от редакции приложения. Описание назначений форматов есть в справке Microsoft для Excel.

Сначала выделите только данные нужной колонки. Если выделить весь лист и назначить денежный вид, количества, скидки и служебные значения тоже могут получить денежные обозначения. В учебном примере присваиваем формат одному диапазону за раз. После каждого изменения проверяем результат хотя бы по двум строкам, включая самую неудобную — с нулями в начале или дробным значением.

Когда артикул нужно хранить как текст

Пусть у поставщика «00127» и «127» обозначают разные товары. Нам необходимо сохранить исходную строку символ за символом. До ввода выделите соответствующие ячейки и назначьте им текстовый формат. Затем введите код заново с нужными нулями. Для разового ручного ввода можно начать с апострофа: в обычном режиме отображения ячейка покажет следующие за ним символы как текст.

Если файл уже открыт и исходный код успел превратиться в число 127, одно переключение в текстовый формат не подскажет, сколько нулей было слева. Вернитесь к исходной выгрузке или справочнику товаров. Особенно опасно автоматически дополнять все коды до одинаковой длины, когда поставщик использует артикулы разной длины. Вместо восстановления исходных идентификаторов вы создадите новые, похожие на настоящие.

Когда подходит маска 00000

В ячейке B2 виден номер 00127, в строке формул — 127
Русская версия Excel. Слева хранится текст, справа тот же вид на листе получен числовым форматом.

У другой организации номер документа числовой, а на печатной форме его принято показывать пятью знаками. Теперь обе записи обозначают один номер, и задача относится только к оформлению. Выберите числовую ячейку со значением 127, откройте пользовательский формат и укажите код 00000. На листе появится 00127. При этом расчёты и сравнения по-прежнему используют числовое значение.

Что хранитсяКак показываемДля какой задачи подходит
Текст 0012700127Точный артикул из внешнего справочника
Число 12700127 благодаря маскеНомер с установленным видом для печати
Число 127127Обычное числовое значение

Проверьте, какую форму ожидает соседняя система. При передаче данных другой программе оформление книги может утратиться, а числовой номер окажется коротким. С другой стороны, при экспорте текстового формата файла результат зависит от способа выгрузки и повторного открытия. Полезная проверка — прочитать экспорт как обычный текст и отдельно проверить импорт на принимающей стороне.

Почему скидка превратилась в 1200 процентов

В пустую ячейку общего формата введите 0,12, если в вашей локали запятая служит десятичным разделителем. Назначьте процентный формат. Excel отобразит 12 %. То же значение можно сразу ввести как 12 %. Если же сначала ввести в ячейку общего формата число 12, а затем применить процентный вид, на листе получится 1200 %. Процентное отображение показывает число в долях от единицы.

Проверьте скидку на сумме заказа. Цена до скидки равна 1 000 рублей. При скидке 12 % уменьшение должно составить 120 рублей, а остаток к оплате — 880. Если результат неожиданно отрицательный или во много раз больше цены, посмотрите исходную ячейку, формулу и знак операции. Для закрепления этой связки пригодится разбор формул Excel с примерами.

Что происходит с датой и количеством

Для даты сначала добейтесь правильного распознавания, затем выбирайте короткое или более подробное отображение. В присланном файле запись со знаком дроби может трактоваться по-разному в разных региональных настройках. Возьмите строку, где смысл однозначен по первичному документу, и сравните её с источником. Изменение ширины столбца или названия месяца не исправляет переставленные при импорте день и месяц.

Для количества установите нужное число видимых знаков. Показ целых упаковок не округляет исходные дробные значения для обычных последующих вычислений. Если две строки выглядят как целые, а итог отличается от суммы видимых чисел, временно покажите больше знаков. Определите, требуется ли по правилам операции округление самого результата. Это отдельное решение, которое при необходимости отражают в формуле.

Как проверить данные перед поиском и сводной таблицей

Возьмите в справочнике код и найдите соответствующую строку накладной. Если внешне одинаковые записи не сопоставились, проверьте тип, ведущие нули и лишние пробелы. Формат отображения мог сделать похожими число и текст. Для поиска точного совпадения это существенно. Разбор ошибок ВПР поможет установить, в каком месте цепочки потерялось соответствие.

Затем соберите небольшой итог по количеству. Если вместо суммы Excel предлагает посчитать записи, посмотрите, нет ли в исходном столбце текстовых чисел или пояснений вроде «8 шт.» в той же ячейке. Единицу лучше хранить отдельно либо показывать подходящим форматом, сохраняя число. Так данные легче анализировать и собирать в сводные таблицы.

Что спросить у человека, приславшего файл

Если перед вами только готовая книга, попросите небольшой образец исходных данных и описание полей. Выясните, встречаются ли в артикулах буквы, пробелы или коды неодинаковой длины. Узнайте, означает ли скидка в выгрузке число процентов или долю единицы. Даже один контрольный заказ с известной итоговой стоимостью существенно упрощает проверку. Сохраните рядом оригинал и работайте с копией. Тогда сможете объяснить коллеге, где изменилось исключительно представление, а где вы действительно преобразовали тип и значение, сверив их с документом.

Приёмка готовой накладной

Перед передачей файла коллеге выполните три независимые проверки. Найдите конкретный артикул в справочнике, пересчитайте стоимость одной строки со скидкой и сравните дату с накладной. После этого сохраните и снова откройте именно тот файл, который получит коллега. Книга Excel и выгрузка для загрузки в учётную программу могут предъявлять разные требования к типам и отображению.

Чтобы закрепить навык, составьте соседнюю таблицу с номером документа для печати и внешним артикулом. Пусть в обеих колонках на экране видно 00127. Объясните, почему в одной колонке допустимо число, а в другой важен текст. На курсе Excel и Google Таблиц этот навык пригодится при переходе от оформления листа к поиску, вычислениям и обработке выгрузок.

Практика вместо теории

Excel + «Google Таблицы» с нуля до PRO +ИИ

Изучите программу курса и примеры задач, которые разбирают на занятиях. На странице есть формат обучения и условия записи.

Вопросы и ответы

По теме