Идеальный формат ячейки
Гибрид числового и финансового формата





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

MS Office для Windows

Все мои курсы

Шаблоны


Смотреть курс
Теги: Вопрос-ответ по Excel.

Гибрид числового и финансового формата

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

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

 

Для начала позвольте напомнить Вам несовершенства.

1. Нет горячей клавиши для быстрой настройки числового формата БЕЗ знаков после запятой и чтобы отрицательные числа выделялись красным цветом. Горячая клавиша CTRL+SHIFT+1 умеет только разделитель групп разряда добавлять (пробел между каждыми тремя цифрами) и оставляет два знака после запятой.

2. В числовом формате без знаков после запятой мы пропустим ошибку 0,25 - она будет выглядеть на экране как ноль.

3. Конечно, финансовый формат показывает только абсолютный ноль в виде чёрточки и за это я очень его люблю. Но он имеет другие неудобства – отрицательные числа чёрные, да и минус находится далеко от числа, в левой части столбца, к этому нужно привыкнуть (шеф может не оценить и потребует вернуть прежние настройки).

Выход из этой ситуации есть.

 

Действие 1.

Формат, учитывающий все наши потребности, можно сделать следующим образом – в открытом файле Excel зайдите в окно формат ячеек через контекстное меню или CTRL+1 и настройте числовой формат в разделе Числовой (разделитель групп разрядов, без знаков после запятой, отрицательное красным).

 

Действие 2.

После перейдите в раздел (все форматы) и в конце поля Тип поставьте точку с запятой и тире (это кодировка внешнего вида нулевого значения). Нажмите ОК.


Действие 3.

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

Запишите этот формат в память файла. Оставаясь на ячейке на вкладке Главная нажмите справа кнопку со стилями ячеек и выберите внизу команду Создать стиль. В появившемся окне оставьте первый флажок, остальные снимите, чтобы новый формат не запоминал бы заливку, параметры шрифта и пр.


Созданный формат доступен теперь на любом листе Вашего файла. Выделите оформляемые ячейки и щёлкните на вкладке Главная справа по кнопке своего формата (он будет показываться первым).

Но только в ЭТОМ файле. Если новый формат необходим в быстром доступе в любых своих файлах - и в уже созданных, и в будущих – читаем дальше.

 

Действие 4.

Поможет простой макрос, код ниже. Рекомендую завести отдельный файл (если у Вас его ещё нет), где Вы будете накапливать полезные макросы, сохранить в удобной папке под любым понятным именем, главное – тип файла выбирайте .xlsm (с поддержкой макросов).

Sub BestNumberFormat()
'создаёт стиль идеального числового формата в активном файле
    ActiveWorkbook.Styles.Add Name:="BestNumber"
    With ActiveWorkbook.Styles("BestNumber")
        .IncludeNumber = True
        .IncludeFont = False
        .IncludeAlignment = False
        .IncludeBorder = False
        .IncludePatterns = False
        .IncludeProtection = False
    End With
    ActiveWorkbook.Styles("BestNumber").NumberFormat = "# ##0_ ;[Red]-# ##0\ ;-"
End Sub

 

Нажмите в таком файле ALT+F11, откроется редактор VBA. В нём слева на любом модуле листа щёлкните правой кнопкой мыши, выберите пункт Insert и в открывшийся в правой части чистый лист вставьте приведённый выше код (выделен заливкой). Закройте редактор VBA, сохранив изменения.

 

Действие 5.

Вызовите контекстное меню на панели быстрого доступа и выберите Настройка панели быстрого доступа. В появившемся окне вверху слева выберите Макросы, выделите макрос BestNumberFormat в списке ниже и кнопкой Добавить перебросьте его в правый список. Положение кнопки можно изменить стрелками справа. Иконку и подсказку настройте с помощью кнопки Изменить.

Файл с макросом можете закрыть. Откройте свою рабочую книгу и нажмите на свой пользовательский значок на панели быстрого доступа – он сам найдёт файл (не переименовывайте его и не перекладывайте в другие места) и запустит макрос. Если появится окошко с запросом Включить макросы – нажмите на одноимённую кнопку. И в группе стилей, как результат, Вы увидите стиль BestNumber. Теперь он останется в этом файле навсегда, дальше – только пользоваться.

Коллеги, я с удовольствием познакомлю Вас с макросами "на ты" на моём онлайн курсе «Макросы на VBA с нуля», простым языком и на рабочих примерах. До встречи!

Полезные материалы

Macro-функция поиска заголовка по данным из таблицы
Function VBA Поиск заголовка
Разбираем различные задачи из реальной практики в Microsoft Excel и находим успешные решения. К примеру вложен файл с образцом, его можно скачать по кнопке.

Запаролить только один лист файла Excel
Заблокировать доступ к определённому листу книги
Стандартная защита умеет только блокировать ячейки на отдельно взятом листе, либо операции с листами, причём со всеми разом, что в работе бывает крайне неудобно.

Функция для подсчёта уникальных значений в отфильтрованной таблице
VBA Function UNIQ
Максимально сокращаем время на ручную работу. Даже в мелочах.

Автоматическое сохранение копии файла при открытии
Макрос вместо F12 [Файл - Сохранить как]
Максимально сокращаем время на ручную работу. Даже в мелочах.

Мои ученики работают здесь

Я сотрудничаю с крупными известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.