Выделение ячеек таблицы по заданному критерию, например, выделить ячейки с числами, большими чем 5 или выделить ячейки с повторяющимися данными. Сортировка ячеек таблицы по алфавиту. Удаление повторяющихся данных. Фильтрация данных, например, отображение данных только одного или двух наименований. Фильтрация данных по нескольким условиям. Суммирование ячеек нескольких листов с помощью консолидации
Условное форматирование, применяется для построения таблиц, ячеек в зависимости от наших условий. Например, ячейка содержит данные о наличии той или иной продукции, и можно эту ячейку подкрасить цветом. Откроем файл Условное форматирование, лист тест.
Выделим ячейки, числа которых больше 5:
![1](wp-content/uploads/excel/exel-04-04.jpg)
Выделим ячейки A2:C6, затем на вкладке Главная/Стили/Условное форматирование/Правила выделения ячеек/Больше(появится диалог в котором будет выполнено вычисление по умолчанию). В левое поле вводим 5, в списке справа выбираем любой цвет:
![1](wp-content/uploads/excel/exel-04-01.jpg)
![1](wp-content/uploads/excel/exel-04-02.jpg)
![1](wp-content/uploads/excel/exel-04-03.jpg)
Выделим ячейки, числа которых выше среднего значения: Выделим ячейки E2:G6, затем на вкладке Главная/Стили/Условное форматирование/Правила отбора первых и последних значений. В списке выбираем Выше среднего. Появится диалог для выбора цвета и в ячейках E2:G6, будут выделены ячейки согласно условию цветом из предыдущего шага условного форматирования.
Выделим ячейки, с совпадающими значениями . Выделим ячейки I2:K6, на вкладке Главная/Стили/Условное форматирование/Правила выделения ячеек/Повторяющиеся данные. В диалоге в списке слева убеждаемся, что выбрано значение повторяющиеся, а справа выбираем любой цвет.
Выделим ячейки, содержащие текст Манго: выделим ячейки A9:C11, на вкладке Главная/Стили/Условное форматирование/Правила выделения ячеек/Текст содержит. В диалоге в левом поле вводим Манго, а справа выбираем любой цвет.
Построим внутри ячеек с числовыми значениями гистограммы: Выделим ячейки E9:G11, на вкладке Главная/Стили/Условное форматирование/Гистограммы/Градиентная заливка. После анализа чисел, программа построит диаграмы.
Опция Наборы значков для условного форматирования содержит графические наборы, которые выделяют числа в диапазоне по определённому критерию. Например, три стрелки делят диапазон чисел на минимальные, максимальные и средние относительно друг друга значения: Выделим ячейки I9:K11, на вкладке Главная/Стили/Условное форматирование/Наборы значков/Направления. Выбираем три стрелки
Удалить форматирование для выделенных ячеек поможет команда Главная/Стили/Условное форматирование/Удалить правила/Удалить правила из выделенных ячеек
Перейдём на лист Значки
Отобразим красный флажок для ячеек, значения которых больше 60: Выделим диапазон A2:D9. На вкладке Главная/Стили/Условное форматирование/Наборы значков/Другие правила.
В диалоге в списке Стиль значка находим три флажка. Ниже три кнопки Значок: для первого выбираем красный флажок, для второго и третьего выбираем пункт Нет значения. Тип для двух полей выбираем число. В первое и второе поле Значения, выбираем 60
Откроем файл Сортировка и фильтры, лист Дежурство. Отсортируем колонку Дежурство, по алфавиту:
Курсор в любую ячейку колонки Дежурство, затем вкладка Главная/Редактирование/Сортировка и фильтр/Сортировка от А до Я. Или, вкладка Данные/Сортировка и фильтр/От А до Я
![1](wp-content/uploads/excel/exel-04-05.jpg)
Отменим сортировку комбинацией [Ctrl+Z]
Теперь проведём сортировку по дням недели: курсор внутрь таблицы с данными, вкладка Данные/Сортировка и фильтр/Сортировка. В диалоге для поля Столбец выбираем Дежурство, Сортировка – значения, Порядок – Настраиваемый список. Появится ещё диалог, где в поле Списки выбираем дни недели
![1](wp-content/uploads/excel/exel-04-06.jpg)
![1](wp-content/uploads/excel/exel-04-07.jpg)
Перейдём на лист Дубликаты
Удалим повторяющиеся наименования: курсор внутрь таблицы с данными, вкладка Данные/Работа с данными/Удалить дубликаты. В диалоге снимаем галки со столбцов, кроме Наименование. Получилась таблица, где не оказалось города Минск. Поскольку в этот город поставили наименования, которые отобрали ранее.
Отменим предыдущую команду комбинацией [Ctrl+Z]
Перейдём на лист Фильтр. Простое условие. Выберем в метро ВДНХ всех машинистов:
Курсор внутрь таблицы, далее вкладка Данные/Сортировка и фильтр/Фильтр(в заголовках должны появиться кнопки):
![1](wp-content/uploads/excel/exel-04-08.jpg)
![1](wp-content/uploads/excel/exel-04-09.jpg)
Раскрываем список для поля METRO, снимаем все галки(клик по строке Выделить всё), затем активируем флажок ВДНХ:
Курсор внутрь таблицы, далее вкладка Данные/Сортировка и фильтр/Фильтр(в заголовках должны появиться кнопки). Раскрываем список для поля METRO, снимаем все галки(клик по строке Выделить всё), затем активируем флажок ВДНХ.
![1](wp-content/uploads/excel/exel-04-10.jpg)
Аналогично повторим для колонки OTDEL, только теперь будем активировать только флажок машинист.
Сложное условие.
Отменим фильтр, снова нажав на кнопку Фильтр:
![1](wp-content/uploads/excel/exel-04-08.jpg)
Теперь нужно выбрать для метро ВДНХ машинистов, а для метро Южная — водителей. Если поступить предыдущим способом, то получатся лишние данные. Применим расширенный фильтр. Для этого создадим таблицу условий: Скопируем поля METRO и OTDEL правее нашей таблицы. Далее введём в поле METRO значения: ВДНХ и Южная, а в поле OTDEL – машинист и водитель:
![1](wp-content/uploads/excel/exel-04-11.jpg)
Теперь курсор внутрь первой таблицы и затем вкладка Данные/Сортировка и фильтр/Дополнительно:
![1](wp-content/uploads/excel/exel-04-12.jpg)
В диалоге выбираем флажок скопировать результат в другое место, курсор в поле Исходный диапазон и мышью выделяем диапазон нашей большой таблицы (включая заголовки), для поля Диапазон условий выбираем новую таблицу и для поля поместить результат в диапазон, выберем любую пустую ячейку правее большой таблицы и ОК.
![1](wp-content/uploads/excel/exel-04-13.jpg)
Должна получиться ещё одна таблица с машинистами из ВДНХ и водителями из метро Южная
![1](wp-content/uploads/excel/exel-04-14.jpg)
Задание для сложного условия. Активируем лист ФильтрСложн.
Отобразить жителей метро Смоленская и Перово которые в 3-м квартале 2015 года, получили оклад более 22000 рублей:
Правее создадим таблицу с полями METRO, DATA, DATA и OKLAD. Заполним новую таблицу: в поле METRO, вводим Смоленская и Перово, в первое поле DATA и для метро Смоленская и для Перово вводим — >=01.07.2015, для следующего поля DATA и для метро Смоленская и для Перово вводим — <=30.09.2015, для поля OKLAD вводим >22000:
![1](wp-content/uploads/excel/exel-04-15.jpg)
Из этого изображения и вспоминая алгебру логики которая изучалась в курсе информатики, делаем вывод, что два поля DATA для одной строки реализуют логическую операцию И, т.е. так записывается интервал для III-го квартала. Другая запись интервала: >=01.07.2015 И <=30.09.2015. И наоборот, две строки в одном поле METRO, реализуют логическую операцию ИЛИ.
Аналогично действиям предыдущей задачи: курсор внутрь большой таблицы, далее вкладка Данные/Сортировка и фильтр/Дополнительно:
![1](wp-content/uploads/excel/exel-04-12.jpg)
В диалоге выбираем флажок скопировать результат в другое место, курсор в поле Исходный диапазон и мышью выделяем диапазон нашей большой таблицы, для поля Диапазон условий выбираем новую таблицу для поля поместить результат, выберем любую пустую ячейку правее большой таблицы и ОК.
![1](wp-content/uploads/excel/exel-04-16.jpg)
![1](wp-content/uploads/excel/exel-04-17.jpg)
Известно, что произвести вычисления между несколькими листами одной рабочей книги, или между несколькими рабочими книгами позволяет формула вида:
имя листа ! имя ячейки + имя листа ! имя ячейки
В данном случае суммируются две ячейки в двух листах. Консолидация позволит суммировать ячейки более удобным способом. Откроем файл Консолидация, убедимся что он состоит из листов 2013, 2014, 2015 и Итого:
![1](wp-content/uploads/excel/exel-04-18.jpg)
Просуммируем продажи 4-х кварталов за три года:
Итоговые суммы будут находится в диапазоне C3:G7, поэтому на листе Итого курсор в ячейку C3, далее вкладка Данные/Работа с данными/Консолидация
![1](wp-content/uploads/excel/exel-04-19.jpg)
Появится диалог Консолидация, настроим его:
В поле Функция – Сумма, курсор в поле Ссылка, затем заходим на лист 2013 и выделяем ячейки с данными – C3:G7, в диалоге Консолидация нажимаем Добавить (в поле Список диапазонов должна появиться строка – ‘2013’! C3:G7):
![1](wp-content/uploads/excel/exel-04-20.jpg)
Повторим эти шаги для листов 2014 и 2015, затем в диалоге Консолидация, активируем флажок Создавать связи с исходными данными и ОК:
![1](wp-content/uploads/excel/exel-04-21.jpg)
В листе Итого, мы получим итоговые данные за три года в каждом квартале. Например, ячейка C6 содержит сумму продаж телевизоров в I-м квартале 2013, 2014 и 2015 годов. Кнопка + правее строк таблицы позволит подробно отобразить суммы за каждый год
![1](wp-content/uploads/excel/exel-04-22.jpg)
0 Responses to “4. Условное форматирование. Сортировка и фильтры”