Пользовательские форматы данных в Excel.

Всем привет.

Его величество MS Excel имеет кучу форматов данных. Каждый раз при нашем вводе новых данных он пытается угадать наиболее подходящий. Не всегда удачно. Но у нас всегда есть возможность его поправить. Однако бывают логические конфузы.

Например, MS Excel считает любую дату от 1-го января 1900 года. И вот что получится если он наступит на 0 или отрицательное число.

Нам видно что "0" это для него как Начало сотворения цифрового мира, т.е. это дата 00.01.1900 которой никогда и не было. А вот с "-1" вы получите ряд из символов "#", и ширина колонки вам никак не поможет этого избежать.

К счастью кроме предопределенных форматов в Excel есть еще и так называемые "Все форматы" (Custom), которые поддаются изменению.

В появившееся справа поле "Тип:" вводится маска нужного нам формата из последнего столбца таблицы:

Как это работает?

На самом деле все очень просто. Как вы уже, наверное, заметили, Excel использует несколько спецсимволов в масках форматов:

0 (ноль) - одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 12 применить маску 0000, то получится 0012, а если к числу 1,3456 применить маску 0,00 - получится 1,35.

# (решетка) - одно необязательное знакоместо - примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится
(пробел) - используется как разделитель групп разрядов по три между тысячами, миллионами, миллиардами и т.д.

[ ] - в квадратных скобках перед маской формата можно указать цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.

Плюс пара простых правил:

Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) - надо обязательно заключать в кавычки.

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

Свой формат данных можно использовать и для скрытия содержимого ячейки. Предположим, что у нас есть несколько ячеек, содержимое которых мы хотим скрыть от беглого взгляда чужого пользователя, не скрывая сами строки или столбцы с данными и не устанавливая пароль, который можно забыть. Можно, конечно, отформатировать их в стиле "белый шрифт на белом фоне", но это не комильфо, да и цвет заливки ячеек не всегда будет белым. Первое же выделение мышкой все покажет. И как поступить?

А просто. Достаточно отформатировать данные в формате ";;;" (3 раза точка с запятой).

Почему именно так?

Любой пользовательский формат может состоять из 4 фрагментов-масок, разделенных точкой с запятой, где каждый фрагмент применяется в определенном случае:
1. Первый - если в ячейке число больше нуля
2. Второй - если меньше
3. Третий - если в ячейке ноль
4. Четвертый - если в ячейке текст.

Три подряд точки с запятой Excel воспринимает как четыре пустых маски для всех четырех возможных случаев, т.е. выводит пустоту при любом значении ячейки. 


А можно такой формат для скрытия вставить в Условное форматирование.

Вот так.