4. Условное форматирование. Сортировка и фильтры

Выделение ячеек таблицы по заданному критерию, например, выделить ячейки с числами, большими чем 5 или выделить ячейки с повторяющимися данными. Сортировка ячеек таблицы по алфавиту. Удаление повторяющихся данных. Фильтрация данных, например, отображение данных только одного или двух наименований. Фильтрация данных по нескольким условиям. Суммирование ячеек нескольких листов с помощью консолидации

Условное форматирование, применяется для построения таблиц, ячеек в зависимости от наших условий. Например, ячейка содержит данные о наличии той или иной продукции, и можно эту ячейку подкрасить цветом. Откроем файл Условное форматирование, лист тест.

Выделим ячейки, числа которых больше 5:

Выделим ячейки A2:C6, затем на вкладке Главная/Стили/Условное форматирование/Правила выделения ячеек/Больше(появится диалог в котором будет выполнено вычисление по умолчанию). В левое поле вводим 5, в списке справа выбираем любой цвет:

Выделим ячейки, числа которых выше среднего значения: Выделим ячейки E2:G6, затем на вкладке Главная/Стили/Условное форматирование/Правила отбора первых и последних значений. В списке выбираем Выше среднего. Появится диалог для выбора цвета и в ячейках E2:G6, будут выделены ячейки согласно условию цветом из предыдущего шага условного форматирования.

Выделим ячейки, с совпадающими значениями . Выделим ячейки I2:K6, на вкладке Главная/Стили/Условное форматирование/Правила выделения ячеек/Повторяющиеся данные. В диалоге в списке слева убеждаемся, что выбрано значение повторяющиеся, а справа выбираем любой цвет.

Выделим ячейки, содержащие текст Манго: выделим ячейки A9:C11, на вкладке Главная/Стили/Условное форматирование/Правила выделения ячеек/Текст содержит. В диалоге в левом поле вводим Манго, а справа выбираем любой цвет.

Построим внутри ячеек с числовыми значениями гистограммы: Выделим ячейки E9:G11, на вкладке Главная/Стили/Условное форматирование/Гистограммы/Градиентная заливка. После анализа чисел, программа построит диаграмы.

Опция Наборы значков для условного форматирования содержит графические наборы, которые выделяют числа в диапазоне по определённому критерию. Например, три стрелки делят диапазон чисел на минимальные, максимальные и средние относительно друг друга значения: Выделим ячейки I9:K11, на вкладке Главная/Стили/Условное форматирование/Наборы значков/Направления. Выбираем три стрелки

Удалить форматирование для выделенных ячеек поможет команда Главная/Стили/Условное форматирование/Удалить правила/Удалить правила из выделенных ячеек

Перейдём на лист Значки

Отобразим красный флажок для ячеек, значения которых больше 60: Выделим диапазон A2:D9. На вкладке Главная/Стили/Условное форматирование/Наборы значков/Другие правила.

В диалоге в списке Стиль значка находим три флажка. Ниже три кнопки Значок: для первого выбираем красный флажок, для второго и третьего выбираем пункт Нет значения. Тип для двух полей выбираем число. В первое и второе поле Значения, выбираем 60

Откроем файл Сортировка и фильтры, лист Дежурство. Отсортируем колонку Дежурство, по алфавиту:

Курсор в любую ячейку колонки Дежурство, затем вкладка Главная/Редактирование/Сортировка и фильтр/Сортировка от А до Я. Или, вкладка Данные/Сортировка и фильтр/От А до Я

Отменим сортировку комбинацией [Ctrl+Z]

Теперь проведём сортировку по дням недели: курсор внутрь таблицы с данными, вкладка Данные/Сортировка и фильтр/Сортировка. В диалоге для поля Столбец выбираем Дежурство, Сортировка – значения, Порядок – Настраиваемый список. Появится ещё диалог, где в поле Списки выбираем дни недели

Перейдём на лист Дубликаты

Удалим повторяющиеся наименования: курсор внутрь таблицы с данными, вкладка Данные/Работа с данными/Удалить дубликаты. В диалоге снимаем галки со столбцов, кроме Наименование. Получилась таблица, где не оказалось города Минск. Поскольку в этот город поставили наименования, которые отобрали ранее.

Отменим предыдущую команду комбинацией [Ctrl+Z]

Перейдём на лист Фильтр. Простое условие. Выберем в метро ВДНХ всех машинистов:

Курсор внутрь таблицы, далее вкладка Данные/Сортировка и фильтр/Фильтр(в заголовках должны появиться кнопки):

Раскрываем список для поля METRO, снимаем все галки(клик по строке Выделить всё), затем активируем флажок ВДНХ:

Курсор внутрь таблицы, далее вкладка Данные/Сортировка и фильтр/Фильтр(в заголовках должны появиться кнопки). Раскрываем список для поля METRO, снимаем все галки(клик по строке Выделить всё), затем активируем флажок ВДНХ.

Аналогично повторим для колонки OTDEL, только теперь будем активировать только флажок машинист.

Сложное условие.

Отменим фильтр, снова нажав на кнопку Фильтр:

Теперь нужно выбрать для метро ВДНХ машинистов, а для метро Южная — водителей. Если поступить предыдущим способом, то получатся лишние данные. Применим расширенный фильтр. Для этого создадим таблицу условий: Скопируем поля METRO и OTDEL правее нашей таблицы. Далее введём в поле METRO значения: ВДНХ и Южная, а в поле OTDEL – машинист и водитель:

Теперь курсор внутрь первой таблицы и затем вкладка Данные/Сортировка и фильтр/Дополнительно:

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

Должна получиться ещё одна таблица с машинистами из ВДНХ и водителями из метро Южная

Задание для сложного условия. Активируем лист ФильтрСложн.

Отобразить жителей метро Смоленская и Перово которые в 3-м квартале 2015 года, получили оклад более 22000 рублей:

Правее создадим таблицу с полями METRO, DATA, DATA и OKLAD. Заполним новую таблицу: в поле METRO, вводим Смоленская и Перово, в первое поле DATA и для метро Смоленская и для Перово вводим — >=01.07.2015, для следующего поля DATA и для метро Смоленская и для Перово вводим — <=30.09.2015, для поля OKLAD вводим >22000:

Из этого изображения и вспоминая алгебру логики которая изучалась в курсе информатики, делаем вывод, что два поля DATA для одной строки реализуют логическую операцию И, т.е. так записывается интервал для III-го квартала. Другая запись интервала: >=01.07.2015 И <=30.09.2015. И наоборот, две строки в одном поле METRO, реализуют логическую операцию ИЛИ.

Аналогично действиям предыдущей задачи: курсор внутрь большой таблицы, далее вкладка Данные/Сортировка и фильтр/Дополнительно:

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

Известно, что произвести вычисления между несколькими листами одной рабочей книги, или между несколькими рабочими книгами позволяет формула вида:

имя листа ! имя ячейки + имя листа ! имя ячейки

В данном случае суммируются две ячейки в двух листах. Консолидация позволит суммировать ячейки более удобным способом. Откроем файл Консолидация, убедимся что он состоит из листов 2013, 2014, 2015 и Итого:

Просуммируем продажи 4-х кварталов за три года:

Итоговые суммы будут находится в диапазоне C3:G7, поэтому на листе Итого курсор в ячейку C3, далее вкладка Данные/Работа с данными/Консолидация

Появится диалог Консолидация, настроим его:

В поле Функция – Сумма, курсор в поле Ссылка, затем заходим на лист 2013 и выделяем ячейки с данными – C3:G7, в диалоге Консолидация нажимаем Добавить (в поле Список диапазонов должна появиться строка – ‘2013’! C3:G7):

Повторим эти шаги для листов 2014 и 2015, затем в диалоге Консолидация, активируем флажок Создавать связи с исходными данными и ОК:

В листе Итого, мы получим итоговые данные за три года в каждом квартале. Например, ячейка C6 содержит сумму продаж телевизоров в I-м квартале 2013, 2014 и 2015 годов. Кнопка + правее строк таблицы позволит подробно отобразить суммы за каждый год