Хотя Microsoft Excel предлагает множество инструментов для форматирования данных различными способами, иногда встроенные инструменты не совсем подходят мне или требуют слишком много времени для выполнения. В таких сценариях я использую пользовательское форматирование чисел, чтобы быстро создать числовой формат, который соответствует моим потребностям.
Отказ от ответственности: есть некоторые сценарии, в которых использование условного форматирования предпочтительнее использования пользовательского числового форматирования, например, раскрашивание целых строк на основе значения. Однако в ситуациях, когда я мог бы использовать любой из них для получения тех же или похожих результатов, я выбираю последний. В этой статье я объясню, почему.
Что такое пользовательское форматирование чисел и как оно работает?
В Excel каждая ячейка имеет свой собственный числовой формат, который можно просмотреть, выбрав ячейку и посмотрев на группу «Число» на вкладке «Главная» на ленте.
Одним из вариантов числового формата, который можно применить к ячейке, является пользовательский числовой формат, доступ к которому можно получить, щелкнув кнопку запуска диалогового окна «Формат чисел», прежде чем нажать «Пользовательский» в диалоговом окне «Формат ячеек».
Если вы предпочитаете использовать сочетания клавиш Excel, нажмите Ctrl+1, чтобы открыть диалоговое окно «Формат ячеек», нажмите Tab, чтобы активировать меню «Категория», и введите Кас для перехода к пользовательскому.
К концу этого руководства вы сможете использовать пользовательское форматирование чисел для создания электронных таблиц, которые будут выглядеть примерно так:
Если вы никогда раньше не использовали пользовательское форматирование, поначалу это может показаться запутанным, и я понимаю, почему вы могли подумать, что использование условного форматирования вместо этого было бы лучшим вариантом. Однако Excel обрабатывает пользовательское форматирование чисел гораздо быстрее, чем условное форматирование, и — как только вы поймете его логику — вы, вероятно, обнаружите, что оно быстрее и проще в использовании.
Лично я, одна из главных причин, по которой предпочитаю использовать пользовательское форматирование чисел, заключается в том, что все происходит в одном месте — нет необходимости переходить между различными диалоговыми окнами для добавления различных правил, поэтому процесс экономит время и является простым.
Прежде чем я перейду к рассмотрению нескольких примеров, позвольте мне объяснить, как работает пользовательское форматирование чисел.
Пользовательские числовые форматы вводятся в поле «Тип» в разделе «Пользовательский» диалогового окна «Формат ячеек».
Код, который вам нужно здесь вставить, имеет строгий порядок:
ПОЛОЖИТЕЛЬНЫЙ;ОТРИЦАТЕЛЬНЫЙ;НОЛЬ;ТЕКСТ
Другими словами, первый аргумент в поле пользовательского числового формата определяет, как форматируются положительные числа, второй аргумент определяет, как форматируются отрицательные числа, третий аргумент определяет, как форматируются нейтральные числа, а четвертый аргумент определяет, как форматируется текст. Обратите внимание также на то, как используются точки с запятой для разделения каждого аргумента.
Например, введите:
#.00;(#.00);-;»TXT»
в поле Тип отобразится:
- Число с двумя десятичными знаками для положительных чисел (#.00),
- Число с двумя десятичными знаками в скобках для отрицательных чисел (#.00),
- Тире для нулей (-),
- И буквенная строка «TXT» для всех текстовых значений («TXT»).
Если вы пропустите какой-либо из аргументов, добавив точку с запятой, но не введя никакого кода, соответствующие ячейки в вашей электронной таблице будут пустыми.
Например, введите:
#.00;;;»TXT»
в поле Тип отображает число с двумя десятичными знаками для положительных чисел, создает пустую ячейку для отрицательных чисел и нулей и отображает «TXT» для всех текстовых значений.
Несмотря на то, что значения, отображаемые в каждой ячейке, зависят от того, что вы указываете в поле пользовательского числового формата Тип, фактические значения ячеек остаются неизменными. В приведенном выше примере, несмотря на то, что ячейка C2 выглядит пустой (потому что я опустил аргумент ОТРИЦАТЕЛЬНЫЙ в поле Тип), когда я выбираю ячейку, строка формул показывает ее истинное значение.
Это значит, что при необходимости я все равно смогу использовать эту ячейку в формулах.
Помимо использования пользовательского форматирования чисел для изменения отображаемого в ячейке значения, вы также можете изменить цвет значений и добавить символы для визуализации данных. Давайте рассмотрим это более подробно.
Изменение цвета шрифта с помощью пользовательского форматирования чисел
Пользовательский числовой формат Microsoft Excel позволяет вам определять цвет значений в выбранных ячейках. Более того, для основных восьми цветов (черный, белый, красный, зеленый, синий, желтый, пурпурный и голубой) вам не нужно запоминать какой-либо код — вы можете просто ввести название цвета. Цвета должны быть первым аргументом для каждого типа данных, и они должны быть заключены в квадратные скобки.
Давайте рассмотрим этот пример, который показывает, достигли ли десять сотрудников своих целей по продажам. Вы хотите, чтобы все положительные числа в столбце C были зелеными, отрицательные числа — красными, а нули — синими.
Для этого выделите ячейку C2, откройте диалоговое окно «Формат ячеек» (Ctrl+1) и перейдите в раздел «Пользовательский».
В поле «Тип» введите:
[Зеленый]#;[Красный]#;[Синий]0;
чтобы указать Excel сохранять положительные и отрицательные числа такими, какие они есть (#) — но отформатированными зеленым и красным соответственно — и нули как «0» — но отформатированными синим. Поскольку в этом диапазоне нет текста, вам не нужно указывать числовой формат для текстовых значений, поэтому требуются только первые три аргумента.
Когда вы нажмете «OK», вы увидите, что текст изменит цвет соответствующим образом. Затем вы можете щелкнуть и перетащить маркер заполнения в последнюю строку данных, чтобы применить этот пользовательский числовой формат к оставшимся значениям. Это не изменит значения в столбце C, поскольку они были рассчитаны с использованием формулы.
Если вы хотите использовать цвета, отличные от стандартных восьми, предлагаемых для шрифта, вместо ввода названия цвета в квадратных скобках введите код цвета:
Например, введите:
[Цвет10]#;[Цвет30]#;[Цвет16]0;
для положительных чисел будет использоваться темно-зеленый шрифт, для отрицательных чисел — малиновый шрифт, а для нулей — серый шрифт.
Чтобы вернуть исходное форматирование чисел, выделите ячейки, содержащие отформатированные данные, и в группе «Число» вкладки «Главная» на ленте выберите в раскрывающемся списке пункт «Общие».
На этом этапе уделите немного времени тому, чтобы оценить, как достижение того же результата с помощью условного форматирования потребует от вас создания трех отдельных правил. С другой стороны, используя пользовательское форматирование, вы можете внести эти изменения, добавив несколько символов в поле Тип.
Отображение символа с использованием пользовательского форматирования чисел
Подобно использованию условного форматирования, вы можете отображать символы в соответствии с соответствующими значениями с помощью пользовательского форматирования чисел.
Используя тот же пример, что и выше, предположим, что теперь вы хотите преобразовать числа в столбце «Результат» (столбец C) в стрелки вверх для положительных чисел, стрелки вниз для отрицательных чисел и знак равенства (=) для нулей.
Для этого вам сначала нужно найти символы или знать их сочетания клавиш.
На вкладке «Вставка» нажмите «Символ» и вставьте символы в другую область электронной таблицы, чтобы вы могли скопировать и вставить их в свой пользовательский код.
Либо используйте сочетание клавиш Windows+точка (.), чтобы запустить клавиатуру эмодзи, и вставьте их прямо в поле «Тип».
С другой стороны, я знаю, что сочетание клавиш Windows для ▲ — Alt+30, а для ▼ — Alt+31, и я могу использовать эти сочетания клавиш при вводе текста в поле «Тип».
Итак, выберите ячейку C2 и в разделе «Пользовательский» диалогового окна «Формат» (Ctrl+1 > Tab > Кас), введите эту очень короткую строку кода:
▲;▼;»=»;
чтобы указать Excel заменить положительные числа на «▲», отрицательные числа на «▼», а нули на «=».
Любые текстовые строки или введенные символы (включая математические или любые символы клавиатуры) должны быть заключены в двойные кавычки.
Теперь нажмите Enter и используйте маркер заполнения, чтобы применить пользовательское форматирование чисел к оставшимся ячейкам в столбце.
Далее, вы можете захотеть раскрасить стрелки, чтобы визуализировать ваши данные более наглядно. Здесь вы комбинируете цветовой код с символами стрелок, помня, что цветовой код всегда должен быть помещен в начале аргумента.
Для этого введите:
[Зеленый]▲;[Красный]▼;»=»;
в поле Тип. Вот что вы увидите:
Использование пользовательского форматирования чисел для цветов шрифта, чисел и символов
Теперь пришло время объединить все вышеперечисленные шаги, чтобы получить результат, который одновременно будет отображать цветные символы и стрелки.
В поле Тип для ячейки C2 введите:
[Зелёный]#▲;[Красный]#▼;0″ =»;
где:
- Первый аргумент ([Green]#▲) окрашивает положительные значения в зеленый цвет, отображая как число, так и направленный вверх треугольник,
- Второй аргумент ([Red]#▼) окрашивает отрицательные значения в красный цвет, отображая как число, так и треугольник, направленный вниз, и
- Третий аргумент (0″ =») не имеет цветового форматирования (поэтому принимает ручное форматирование ячеек) и отображает число «0», за которым следует пробел и знак «=».
Чтобы выровнять число по левому краю ячейки, а символ — по правому краю ячейки, введите звездочку (*) и пробел между символом и числом в каждом аргументе (например, [Зелёный]#* ▲;[Красный]#* ▼;0* » =»;). Звездочка сообщает Excel, что следующий символ нужно повторять бесконечно (пока не будет достигнута граница ячейки), а поскольку следующий символ — пробел, зазор между числом и символом увеличивается и уменьшается в зависимости от ширины ячейки.
В последнем примере предположим, что вы вычислили процентное изменение в общем количестве очков для различных спортивных команд и хотите быстро отформатировать положительные и отрицательные цифры так, чтобы они стали зелеными или красными, имели стрелку вверх или вниз, включали число и считались процентами. Вы также хотите, чтобы нули отображались как тире.
Для этого в поле Тип для ячеек столбца C введите:
[Зеленый]▲#%;[Красный]▼#%;»-«
Вот что вы получите в результате:
Как только вы попрактикуетесь в использовании пользовательских числовых форматов для добавления цвета и символов в свои ячейки, вы поймете, насколько это быстрый и простой процесс, и, надеемся, в будущем вы будете чаще использовать пользовательское числовое форматирование!
