Стрелки в ячейках

Этот простой прием пригодится всем, кто хоть раз делал в своей жизни отчет, где нужно наглядно показать изменение какого-либо параметра (цен, прибыли, расходов) по сравнению с предыдущими периодами. Суть его в том, что прямо внутрь ячеек можно добавить зеленые и красные стрелки, чтобы показать куда сдвинулось исходное значение:

стрелки в ячейках листа excel

Реализовать подобное можно несколькими способами.

Способ 1. Пользовательский формат со спецсимволами

Предположим, что у нас есть вот такая таблица с исходными данными, которые нам надо визуализировать:

arrows-in-cells2.png

Для расчета процентов динамики в ячейке D4 используется простая формула, вычисляющая разницу между ценами этого и прошлого года и процентный формат ячеек. Чтобы добавить к ячейкам нарядные стрелочки, делаем следующее:

Выделите любую пустую ячейку и введите в нее символы "треугольник вверх" и "треугольник вниз", используя команду Вставить - Символ (Insert - Symbol):

arrows-in-cells3.png

Лучше использовать стандартные шрифты (Arial, Tahoma, Verdana), которые точно есть на любом компьютере. Закройте окно вставки, выделите оба введенных символа (в строке формул, а не ячейку с ними!) и скопируйте в Буфер (Ctrl+C).

Выделите ячейки с процентами (D4:D10) и откройте окно Формат ячейки (можно использовать Ctrl+1). На вкладке Число (Number) выберите в списке формат Все форматы (Custom) и вставьте скопированные символы в строку Тип, а потом допишите к ним вручную с клавиатуры нули и проценты, чтобы получилось следующее:

arrows-in-cells4.png

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

arrows-in-cells5.png

Добавить красный и зеленый цвет к ячейкам можно двумя способами. Первый - вернуться в окно Формат ячеек и дописать нужные цвета в квадратных скобках прямо в нашем пользовательском формате:

arrows-in-cells6.png

Лично мне не очень нравится получающийся в этом случае ядовитый зеленый, поэтому я предпочитаю второй вариант - добавить цвет с помощью условного форматирования. Для этого выделите ячейки с процентами и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Больше (Home - Conditional formatting - Highlight Cell Rules - Greater Then), введите в качестве порогового значения 0 и задайте желаемый цвет:

arrows-in-cells7.png

Потом повторите эти действия для отрицательных значений, выбрав Меньше (Less Then), чтобы отформатировать их зеленым.

Если немного поэкспериментировать с символами, то можно найти много аналогичных вариантов реализации подобного трюка с другими символами:

arrows-in-cells8.png

Способ 2. Условное форматирование

Выделите ячейки с процентами и откройте на вкладке Главная - Условное форматирование - Наборы значков - Другие правила (Home - Conditional formatting - Icon Sets - More). В открывшемся окне выберите нужные значки из выпадающих списков и задайте ограничения для подстановки каждого из них как на рисунке:

arrows-in-cells9.png

После нажатия на ОК получим результат:

arrows-in-cells10.png

Плюсы этого способа в его относительной простоте и приличном наборе различных встроенных значков, которые можно использовать:

arrows-in-cells11.png

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

Ссылки по теме

 





02.02.2015 08:26:09
Николай, всё просто и наглядно. Спасибо! Способ с форматами ячеек понравился больше т.к. его 1 раз прописал, а потом просто применяй на нужные ячейки и ничего не надо настраивать.
P.S.  Пробел "для красоты" между стрелкой и значением поставить можно, но только 1 (больше не ставится:)).
05.02.2015 08:43:40
Пробел (в русском Excel) будет восприниматься как разделитель групп разрядов (тысяч).
02.02.2015 13:53:05
А есть способ последний вариант с персональным условным форматированием сохранить отдельной кнопкой на быстрой панели?
05.02.2015 08:42:19
Только если записать макрос и повесить на кнопку.
Или просто копировать формат "кисточкой" с уже сделанных ячеек на новые.
02.02.2015 22:18:41
Здравствуйте!
Во-первых, спасибо за первый способ - очень познавательно

Во-вторых, частенько пользовался вариантом №2 и стыкался с проблемой, что к наборам значков невозможно задать условие формулой, содержащей относительные ссылки... Можно этот как-нибудь обойти или исправить?
05.02.2015 08:42:49
Насколько я знаю - нет :(
05.02.2015 07:56:51
Добрый день, первый способ очень интересный, а цвет можно выбрать любой из 56, например [Цвет3]▲0%;[Цвет10]▼0%
Здесь палитра.
05.02.2015 08:41:23
Спасибо, Юрий! Ценное замечание!
15.04.2015 17:34:59
Николай, большое спасибо за способ!
Небольшое дополнение: если динамики нет - то можно добавить ►◄0%
29.04.2015 17:01:58

Стрелки
Selection.NumberFormat = " " & ChrW(8593) & " 0%;" & ChrW(8595) & "-0%;0%"
 
Треугольники

Selection.NumberFormat = " " & ChrW(9650) & " 0%;" & ChrW(9660) & "-0%;0%"
 
09.05.2015 12:47:06
   Здравствуйте , Николай .
спасибо за видео урок . Есть одно но , после сохранения когда заново открываешь файл , вместо стрелки стоят вопросительные знаки . что надо сделать ?  У меня на компе стоит офис 2010 .
07.04.2016 10:53:44
Доброго времени суток! Спасибо большое за данный урок! Мне на паре по информатике это очень помогло, буквально спасло жизнь! Буду и дальше пользоваться этим сайтом и советовать друзьям!
17.08.2016 14:01:41
26.02.2018 08:34:42
Добрый день! Возможно ли поставить стрелки не только где проценты, но и для ячейки в которой в разы больше или ниже значение
Наверх