Мини-курс "5 чудес Excel"
Нули чёрточкой в числовом формате
Сейчас соединим преимущества числового и финансового формата, а также сделаем новый универсальный формат доступным в любом файле Excel.
Для начала позвольте напомнить Вам несовершенства.
1. Нет горячей клавиши для быстрой настройки числового формата без знаков после запятой и чтобы отрицательные числа выделялись красным цветом. Горячая клавиша CTRL+SHIFT+1 умеет только разделитель групп разряда добавлять (пробел между каждыми тремя цифрами) и оставляет два знака после запятой.
2. В числовом формате без знаков после запятой мы пропустим число 0,25 - оно будет выглядеть на экране как ноль, а при расчёте отклонение это совсем нехорошо.
3. Конечно, финансовый формат показывает только абсолютный ноль в виде чёрточки и за это я очень его люблю. Но он имеет другие неудобства – отрицательные числа чёрные, да и минус находится далеко от числа, в левой части столбца, к этому нужно привыкнуть (шеф может не оценить и потребует вернуть прежние настройки).
Выход из этой ситуации есть.
Действие 1.
Формат, учитывающий все наши потребности, можно сделать следующим образом – в открытом файле Excel зайдите в окно формат ячеек через контекстное меню или CTRL+1 и настройте числовой формат в разделе Числовой (разделитель групп разрядов, без знаков после запятой, отрицательное красным).
Действие 2.
После перейдите в раздел (все форматы) и в конце поля Тип поставьте точку с запятой и тире (это кодировка внешнего вида нулевого значения). Нажмите ОК.
Действие 3.
Идеальный формат готов. Но применять его к другим таблицам будет проблематично. Формат по образцу не подходит, так как потянет за собой и границы, и заливку, и параметры шрифта. Нам же это не нужно. Поэтому продолжаем.
Для начала позвольте напомнить Вам несовершенства.
1. Нет горячей клавиши для быстрой настройки числового формата без знаков после запятой и чтобы отрицательные числа выделялись красным цветом. Горячая клавиша CTRL+SHIFT+1 умеет только разделитель групп разряда добавлять (пробел между каждыми тремя цифрами) и оставляет два знака после запятой.
2. В числовом формате без знаков после запятой мы пропустим число 0,25 - оно будет выглядеть на экране как ноль, а при расчёте отклонение это совсем нехорошо.
3. Конечно, финансовый формат показывает только абсолютный ноль в виде чёрточки и за это я очень его люблю. Но он имеет другие неудобства – отрицательные числа чёрные, да и минус находится далеко от числа, в левой части столбца, к этому нужно привыкнуть (шеф может не оценить и потребует вернуть прежние настройки).
Выход из этой ситуации есть.
Действие 1.
Формат, учитывающий все наши потребности, можно сделать следующим образом – в открытом файле Excel зайдите в окно формат ячеек через контекстное меню или CTRL+1 и настройте числовой формат в разделе Числовой (разделитель групп разрядов, без знаков после запятой, отрицательное красным).
Действие 2.
После перейдите в раздел (все форматы) и в конце поля Тип поставьте точку с запятой и тире (это кодировка внешнего вида нулевого значения). Нажмите ОК.
Действие 3.
Идеальный формат готов. Но применять его к другим таблицам будет проблематично. Формат по образцу не подходит, так как потянет за собой и границы, и заливку, и параметры шрифта. Нам же это не нужно. Поэтому продолжаем.
Зачем нужны стили ячеек Excel
Запишите этот формат в память файла.
Оставаясь на ячейке на вкладке Главная нажмите справа кнопку со стилями ячеек и выберите внизу команду Создать стиль. В появившемся окне оставьте первый флажок, остальные снимите, чтобы новый формат не запоминал бы заливку, параметры шрифта и пр.
Созданный формат доступен теперь на любом листе Вашего файла. Выделите оформляемые ячейки и щёлкните на вкладке Главная справа по кнопке своего формата (он будет показываться первым).
Но только в этом файле. Если новый формат необходим в быстром доступе в любых своих файлах - и в уже созданных, и в будущих – читаем дальше.
Действие 4.
Перебросить созданные стили из одного файла в другой можно командой Объединить стили.
Действие 5.
Для полной автоматизации задачи поможет простой макрос, код ниже. Рекомендую завести отдельный файл (если у Вас его ещё нет), где Вы будете накапливать полезные макросы, сохранить в удобной папке под любым понятным именем, главное – тип файла выбирайте .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, сохранив изменения.
Оставаясь на ячейке на вкладке Главная нажмите справа кнопку со стилями ячеек и выберите внизу команду Создать стиль. В появившемся окне оставьте первый флажок, остальные снимите, чтобы новый формат не запоминал бы заливку, параметры шрифта и пр.
Созданный формат доступен теперь на любом листе Вашего файла. Выделите оформляемые ячейки и щёлкните на вкладке Главная справа по кнопке своего формата (он будет показываться первым).
Но только в этом файле. Если новый формат необходим в быстром доступе в любых своих файлах - и в уже созданных, и в будущих – читаем дальше.
Действие 4.
Перебросить созданные стили из одного файла в другой можно командой Объединить стили.
Действие 5.
Для полной автоматизации задачи поможет простой макрос, код ниже. Рекомендую завести отдельный файл (если у Вас его ещё нет), где Вы будете накапливать полезные макросы, сохранить в удобной папке под любым понятным именем, главное – тип файла выбирайте .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. Теперь он останется в этом файле навсегда, дальше – только пользоваться.
Действие 5.
Вызовите контекстное меню на панели быстрого доступа и выберите Настройка панели быстрого доступа. В появившемся окне вверху слева выберите Макросы, выделите макрос BestNumberFormat в списке ниже и кнопкой Добавить перебросьте его в правый список. Положение кнопки можно изменить стрелками справа. Иконку и подсказку настройте с помощью кнопки Изменить.
Файл с макросом можете закрыть. Откройте свою рабочую книгу и нажмите на свой пользовательский значок на панели быстрого доступа – он сам найдёт файл (не переименовывайте его и не перекладывайте в другие места) и запустит макрос. Если появится окошко с запросом Включить макросы – нажмите на одноимённую кнопку. И в группе стилей, как результат, Вы увидите стиль BestNumber. Теперь он останется в этом файле навсегда, дальше – только пользоваться.
Числовой формат без знаков после запятой
А вот код макроса для создания нашего идеального числового формата без знаков после запятой. Если прочерк вместо нуля не нужен, то просто уберите перед закрывающей кавычкой символы ;-
Sub BestFormatCreat()
Selection.NumberFormat = "#,##0_ ;[Red]-#,##0 ;-"
End Sub
Коллеги, я с удовольствием познакомлю Вас с макросами "на ты" на моём онлайн курсе «Макросы на VBA с нуля", простым языком и на рабочих примерах. До встречи!
Sub BestFormatCreat()
Selection.NumberFormat = "#,##0_ ;[Red]-#,##0 ;-"
End Sub
По описанному выше алгоритму можно разместить его в своём файле с полезностями и вывести кнопку на панель быстрого доступа.
Расположите эту кнопку в начале панели, посчитайте её порядковый номер и используйте соответствующую цифру клавиатуры в паре с горячей клавишей ALT. Например, если расположить кнопку для запуска макроса первой, то запускать макрос можно комбинацией клавиш ALT+1. В любом файле на этом компьютере. Только сначала выделите диапазон - всё как обычно. Почти :)
Коллеги, я с удовольствием познакомлю Вас с макросами "на ты" на моём онлайн курсе «Макросы на VBA с нуля", простым языком и на рабочих примерах. До встречи!