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

Лабораторная работа

Дополнительные возможности MS Excel

1. Добавление стрелок в ячейки

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

Способ 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). В открывшемся окне выберите нужные значки из выпадающих списков и задайте ограничения для подстановки каждого из них как на рисунке:

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

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

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

2. Выделение дубликатов цветом

Среди стандартных средств Microsoft Excel есть много разных способов выделить дубликаты цветом. Самый простой и быстрый - с помощью условного форматирования. Для этого достаточно выделить диапазон ячеек и выбрать на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional Formatting - Highlight Cells Rules - Duplicate Values):

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

При большом количестве повторяющихся ячеек оттенки различить будет трудно, но при относительно небольшом количестве дубликатов этот способ сработает отлично.

Чтобы использовать этот макрос нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставьте новый пустой модуль через меню Insert - Module и скопируйте туда код этого макроса:

Теперь можно выделить любой диапазон с данными на листе и запустить наш макрос с помощью сочетания клавиш Alt+F8 или через кнопку Макросы (Macros) на вкладке Разработчик (Developer).

3. Сортировка данных

Сортировка - функция Excel до боли знакомая и привычная почти всем. Тем не менее, есть несколько нестандартных и интересных случаев ее применения.