Тема 3.2. Фильтрация, консолидация, сводные таблицы

• Фильтрация, консолидация и сводные таблицы используются для работы со списками и базами данных в Excel.  

• Список в Excel состоит из столбцов и строк, первая строка содержит имена полей.  

• Рекомендуется располагать информацию, не относящуюся к списку, на расстоянии от таблицы.  

• Между таблицами на одном листе должен быть пустой столбец или строка.  

• Список не должен иметь пустых столбцов или строк.  

• Для удобства работы со списком рекомендуется присвоить ему имя и закрепить заголовки.  

• Фильтры позволяют выбирать записи из списка, скрывая те, которые не соответствуют критериям.  

• Существуют два типа фильтров: автофильтр и расширенный фильтр.  

• Автофильтр прост в использовании, но не позволяет задавать сложные условия.  

• Пример фильтрации студентов по факультету и оценке по информатике.  

• Автофильтр позволяет отбирать записи по нескольким условиям.  

• Пример использования пользовательского фильтра для сложных условий.  

• Расширенный фильтр используется для сложных условий и размещения результатов отдельно от списка.  

• Для применения расширенного фильтра создается таблица критериев.  

• Пример создания таблицы критериев для фильтрации студентов по факультету и оценке.  

• Для использования расширенного фильтра нужно установить курсор внутри списка и выполнить команду "Данные сортировка или фильтр дополнительно".  

• Система выводит диалоговое окно для ввода информации о диапазоне списка и условий.  

• Можно фильтровать список на месте или копировать результат в другое место.  

• Функция "промежуточные итоги" производит вычисления только с отображенными записями.  

• В отличие от функций "счет", "сумма", "среднее значение", "промежуточные итоги" учитывают только отображенные записи.  

• Основное назначение фильтра – отбор информации, удовлетворяющей определенным критериям.  

• Консолидация позволяет сводить данные из разных мест одной или нескольких рабочих книг.  

• Консолидация бывает двух видов: по категориям и по расположению.  

• Сводные таблицы – удобный инструмент для анализа и представления данных в виде отчетов и диаграмм.  

• Для создания сводной таблицы выберите команду "Вставка" и "Таблица".  

• Вставьте таблицу или диапазон данных.  

• Excel добавит пустой отчет сводной таблицы на новый лист.  

• В сводной таблице перечислены поля из вашей базы данных.  

• Можно настроить макет и макет отчета.  

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

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

• Использование фильтра для отбора льготных кредитов с определенными условиями.  

• Применение числовых фильтров для отбора кредитов с суммой от 10 до 20 тысяч.  

• Использование функции "Промежуточные итоги" для расчета количества, общей суммы, средней величины и среднего срока кредита.  

• Применение функции "Среднее значение" для расчета средней величины и среднего срока кредита.  

• Отмена фильтра возвращает таблицу к исходному виду.  

• Решение второй задачи: отбор кредитов с суммой меньше 50 тысяч или больше 100 тысяч, срок кредита от 5 до 10 дней.  

• Создание таблицы критериев для расширенного фильтра.  

• Последовательность полей таблицы критериев должна соответствовать исходной таблице.  

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

• Выделение ячейки и применение расширенного фильтра.  

• Копирование результата в другое место для сравнения.  

• Вставка диапазона условий для фильтрации.  

• Объединение данных по датам и суммам кредитов.  

• Использование инструмента консолидации для создания двух таблиц.  

• Выделение полей дата выдачи и дата возврата.  

• Подсчет общей суммы кредитов с помощью функции "сумма".  

• Подсчет количества выданных кредитов с помощью функции "количество".  

• Очистка списка диапазонов перед каждой операцией.  

• Добавление столбца "месяц" для группировки по месяцам.  

• Подсчет общей суммы и количества кредитов по месяцам.  

• Использование функции "просмотр данных" для преобразования числового формата в текстовый.  

• Присвоение имени исходной таблице.  

• Создание новой таблицы на новом рабочем листе.  

• Группировка данных по датам и месяцам с помощью сводной таблицы.  

• Удобство использования сводной таблицы для группировки данных.  

• Возможность быстрого пересчета и группировки данных.  

• Необходимость умения пользоваться инструментом консолидации в некоторых случаях.