Стрелки в ячейках
Этот простой прием пригодится всем, кто хоть раз делал в своей жизни отчет, где нужно наглядно показать изменение какого-либо параметра (цен, прибыли, расходов) по сравнению с предыдущими периодами. Суть его в том, что прямо внутрь ячеек можно добавить зеленые и красные стрелки, чтобы показать куда сдвинулось исходное значение:
Реализовать подобное можно несколькими способами.
Способ 1. Пользовательский формат со спецсимволами
Предположим, что у нас есть вот такая таблица с исходными данными, которые нам надо визуализировать:
Для расчета процентов динамики в ячейке D4 используется простая формула, вычисляющая разницу между ценами этого и прошлого года и процентный формат ячеек. Чтобы добавить к ячейкам нарядные стрелочки, делаем следующее:
Выделите любую пустую ячейку и введите в нее символы "треугольник вверх" и "треугольник вниз", используя команду Вставить - Символ (Insert - Symbol):
Лучше использовать стандартные шрифты (Arial, Tahoma, Verdana), которые точно есть на любом компьютере. Закройте окно вставки, выделите оба введенных символа (в строке формул, а не ячейку с ними!) и скопируйте в Буфер (Ctrl+C).
Выделите ячейки с процентами (D4:D10) и откройте окно Формат ячейки (можно использовать Ctrl+1). На вкладке Число (Number) выберите в списке формат Все форматы (Custom) и вставьте скопированные символы в строку Тип, а потом допишите к ним вручную с клавиатуры нули и проценты, чтобы получилось следующее:
Принцип прост: если в ячейке будет положительное число, то к ней будет применяться первый пользовательский формат, если отрицательное - второй. Между форматами стоит обязательный разделитель - точка с запятой. И ни в коем случае не вставляйте никаких пробелов "для красоты". После нажатия на ОК наша таблица будет выглядеть почти как задумано:
Добавить красный и зеленый цвет к ячейкам можно двумя способами. Первый - вернуться в окно Формат ячеек и дописать нужные цвета в квадратных скобках прямо в нашем пользовательском формате:
Лично мне не очень нравится получающийся в этом случае ядовитый зеленый, поэтому я предпочитаю второй вариант - добавить цвет с помощью условного форматирования. Для этого выделите ячейки с процентами и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Больше (Home - Conditional formatting - Highlight Cell Rules - Greater Then), введите в качестве порогового значения 0 и задайте желаемый цвет:
Потом повторите эти действия для отрицательных значений, выбрав Меньше (Less Then), чтобы отформатировать их зеленым.
Если немного поэкспериментировать с символами, то можно найти много аналогичных вариантов реализации подобного трюка с другими символами:
Способ 2. Условное форматирование
Выделите ячейки с процентами и откройте на вкладке Главная - Условное форматирование - Наборы значков - Другие правила (Home - Conditional formatting - Icon Sets - More). В открывшемся окне выберите нужные значки из выпадающих списков и задайте ограничения для подстановки каждого из них как на рисунке:
После нажатия на ОК получим результат:
Плюсы этого способа в его относительной простоте и приличном наборе различных встроенных значков, которые можно использовать:
Минус же в том, что в этом наборе нет, например, красной стрелки вверх и зеленой вниз, т.е. для роста прибыли эти значки использовать будет логично, а вот для роста цен - уже не очень. Но, в любом случае, этот способ тоже заслуживает, чтобы вы его знали ;)
Ссылки по теме
- Видеоурок по условному форматированию в Excel
- Как создавать пользовательские нестандартные форматы ячеек в Excel (тыс.руб, шт., чел. и т.д.)
- Как построить диаграмму "План-Факт"
P.S. Пробел "для красоты" между стрелкой и значением поставить можно, но только 1 (больше не ставится:)).
Или просто копировать формат "кисточкой" с уже сделанных ячеек на новые.
Во-первых, спасибо за первый способ - очень познавательно
Во-вторых, частенько пользовался вариантом №2 и стыкался с проблемой, что к наборам значков невозможно задать условие формулой, содержащей относительные ссылки... Можно этот как-нибудь обойти или исправить?
Небольшое дополнение: если динамики нет - то можно добавить ►◄0%
Стрелки
спасибо за видео урок . Есть одно но , после сохранения когда заново открываешь файл , вместо стрелки стоят вопросительные знаки . что надо сделать ? У меня на компе стоит офис 2010 .