Создаем собственный числовой формат в MS Excel


 

Знаете сколько числовых форматов предлагает MS Excel? 9 штук! А ведь почти каждый из них имеет ещё и свои внутренние настройки – количество знаков после запятой, спецобозначения валют и т.п. Казалось бы во всем этом многообразии могут найтись варианты на все случаи жизни… Однако это далеко не так.

Ладно, дроби, числа с особой точностью, даты и валюты – с этим MS Excel прекрасно справляется и без нашей помощи. Но как прикажете объяснить ему, что номера кредитных карт выводятся исключительно в формате “ХХХХ ХХХХ ХХХХ ХХХХ”, а телефоны в формате “+7-9ХХ-ХХХ-ХХ-ХХ”? Впрочем, номера и телефоны это ещё не самый запущенный случай. Гораздо хуже приходится несчастным забивающим в таблицы сложные составные артикулы содержащие не только и цифры и буквы, но ещё и тире, косые черточки, точки и символы.

Если вас “накрыло” именно такой работой, самый лучший выход из положения – не проклинать начальство, а создать в MS Excele свой собственный числовой формат.

Создаем новый числовой формат в MS Excel

Создаем новый числовой формат в MS Excel

Как создать новый числовой формат в Excel

В примере приведенном далее, я буду использовать за образец такие требования: мне необходимо создать новый формат для ввода телефонных номеров. Причем в ячейку я буду вводить простой ряд цифр, а к соответствующему формату (“+7-9ХХ-ХХХ-ХХ-ХХ“) этот ряд цифр должен будет привести уже сам MS Excel.

Первым делом не мешало бы выделить столбец нашего будущего шаблона, а затем на вкладке главная вызвать выпадающий список “Числовой формат” (в группе “число”) и выбрать самый нижний пункт списка – “Другие числовые форматы“.

форматы чисел в экселе

Выбираем шаблон похожий на то что нам нужно и модифицируем его

Нужная нам вкладка “Число” доступна здесь по умолчанию. Во всем многообразии предложенных числовых форматов я разбираться не стану и сразу же выберу пункт “(все форматы)”, чтобы увидеть всё и сразу.

Обратите внимание: в самом низу в этом списке уже есть шаблон телефонного номера который выглядит как “(###) ###-####”. Очевидно, что значки “решетки” в этом шаблоне заменяют цифры, и введи я реальный номер, например, в виде числа 9141108501, он отобразился бы как “(914) 110-8501”.

Как видите – неплохо, однако на мой задуманный шаблон походит только отчасти. Поэтому я просто переписываю шаблон на “+7 (###) ###-##-##” и нажимаю кнопку “Ок” (обратите внимание – если в экспериментальной ячейке введено число, при изменении шаблона вы сможете видеть как оно будет выглядеть после изменений).

Ввожу обычный числовой ряд, а MS Excel делает всю остальную работу по введенному ранее шаблону

Ввожу обычный числовой ряд, а MS Excel делает всю остальную работу по введенному ранее шаблону

Все отлично работает, особенно хорошо это заметно по строке формул – в ней мой телефонный номер выглядит как обычный числовой ряд, а в ячейке отображается как “нормальный” телефон.

Также обратите внимание: если второй раз открыть окно “Другие числовые форматы”, то вы обнаружите в нем не только “новый” шаблон для ввода телефонных номеров, но и “старый”. Сохранение это происходит автоматически и служит надежной страховкой от того, чтобы всерьез что-то в программе “сломать”.

собственные числовые форматы в excel

Копирую форматирование на соседние числовые ряды…

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

как создать свой формат чисел в экселе

…и получаю аккуратный список телефонных номеров

Проблемы пользовательских числовых форматов MS Excel

К сожалению, описываемый метод не решает некоторых проблем, самая главная из которых заключается в отсутствии фильтрации ввода и как следствия, возможности ошибок и опечаток. Пользовательский формат контролирует ТОЛЬКО расположение символов, вводимых в пределах этого формата, но не будет учитывать, сколько символов было введено.

Например, если рабочий лист содержит артикулы продуктов, такие как “Заготовка: А15-22”, то создание пользовательского формата “”Заготовка:” А##-##” и последующий ввод числа “1522” приведет к формированию ожидаемого результата. Но если вместо “1522” я по ошибке введу 311522, шаблон все равно окажется заполнен и выдаст итоговый результат вида “Заготовка: А3115-22”.

Однако… нам ведь уже знакома такая штука как проверка ввода данных? Так давайте разберемся с её помощью с возникшей проблемой.

 Используем проверку данных в MS Excel

Используем проверку данных в MS Excel, чтобы исключить неправильные варианты ввода

Выделите столбец в котором будет осуществляться проверка ввода данных (в данном случае столбец с моими артикулами).

Перейдите на вкладку «Данные» в группе «Работа с данным» откройте инструмент «Проверка данных«, и на вкладке «Параметры» укажите “тип”: “Длина текста”, “значение”: “Равно”, а в поле “длина”, введите число 4. Нажмите «Ок».

как создать собственный числовой формат в MS Excel

Пытаюсь ввести в свой шаблон заведомо неверную комбинацию из 5 цифр и получаю ожидаемое сообщение об ошбке

Отлично, теперь в любую ячейку этого столбца нельзя ввести число длина которого превышает 4 знака, в противном случае на экран будет выведена ошибка.

Форматируйте, фильтруйте и наслаждайтесь!

 

Также вас может заинтересовать:

 
Сайт создан: ООО "Эйфос". Информационные технологии

Наверх