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

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

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

Таблица Больше 5

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

1

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

1
1
1
Таблица Выше среднего

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

Таблица Одинаковые данные

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

Таблица Текст содержит Манго

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

Таблица Гистограммы

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

Таблица Стрелки

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

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

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

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

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

Сортировка и фильтры

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

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

1

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

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

1
1

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

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

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

Фильтрация

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

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

1
1

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

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

1

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

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

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

1

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

1

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

1

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

1

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

1

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

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

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

1

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

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

1

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

1
1

Консолидация

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

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

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

1

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

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

1

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

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

1

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

1

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

1

0 Responses to “4. Условное форматирование. Сортировка и фильтры”


Comments are currently closed.



Яндекс.Метрика
Rambler's Top100 Рейтинг@Mail.ru Топ Разработка игр