Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности
51
Microsoft Excel 2013. Уровень 2. Расширенные возможности
Расширенный фильтр
Расширенный фильтр позволяет:
1.Построить более сложные условия отбора.
2.Разместить отфильтрованные данные в другом диапазоне.
3.Из списка повторяющихся значений выбрать только уникальные.
Последовательность действий:
1.Построить таблицу условий отбора данных на любом листе текущей книги (можно и в другой книге). Название столбца должно совпадать с одним из заголовков таблицы (лучше копировать из исходной таблицы). Порядок расположения заголовков таблицы значения не имеет. Условия отбора в одной строке работают как И, а в разных строках – как ИЛИ.
Условия фильтрации могут быть на совпадение (искомое значение просто вводится в ячейку) или на сравнение (в ячейку вводится оператор сравнения и значение).
Если по одному столбцу надо поставить условие между, то следует добавить этот заголовок еще раз и задать второе ограничение.
2.На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter] выбрать команду
Дополнительно [Advanced].
3.Выделить Исходный диапазон (фильтруемая таблица) [List range] и Диапазон условий
(условия фильтрации) [Criteria range] вместе с названиями столбцов (заголовками).
4.Выбрать вариант обработки [Action]:
фильтровать список на месте [Filter the list, in-place]
скопировать результат в другое место [Copy to another Location]. Указать ячейку для размещения результата отбора в поле Поместить результат в диапазон [Copy to].
5.Установить флажок Только уникальные записи [Unigue records only], если необходимо получить результат отбора без повторений.
6. ОК.
Центр Компьютерного обучения «Специалист» |
www.specialist.ru |
52
Microsoft Excel 2013. Уровень 2. Расширенные возможности
ПРАКТИКУМ:
Открыть файл 02_4 Фильтрация данных.
На листе ЗАДАНИЕ4, ЗАДАНИЕ5 выполнить соответствующие задания.
Сохранить сделанные изменения в файле и закрыть его.
САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ
Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 13-1 и 13-2.
Подведение промежуточных итогов
Один из способов обработки и анализа базы данных состоит в подведении различных итогов. Итоги – это быстрый способ вставки функций в определенные столбцы таблицы с группировкой данных в столбце, по которому подводятся итоги.
1.Отсортировать таблицу по столбцу, по значениям которого нужно подвести итог.
2.Выделить любую ячейку таблицы.
3.На вкладке Данные [Data], в группе Структура [Outline], выбрать Промежуточный итог
[Subtotal].
4.В поле При каждом изменении в [At each change in] выбрать столбец, который сортировали.
5.В поле Операция [Use function] выбрать нужную функцию.
6.В окне Добавить итоги по: [Add subtotal to] поставить флажки для тех полей, к которым будет применена операция.
7.ОК.
Команда Промежуточный итог [Subtotal] вставляет в таблицу новые строки, содержащие специальную функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(Номер_функции; Ссылка)
[SUBTOTAL(Function_num; Ref)].
www.specialist.ru |
Центр Компьютерного обучения «Специалист» |
53
Microsoft Excel 2013. Уровень 2. Расширенные возможности
Номер_функции [Function_num] – это число от 1 до 11, которое указывает, какая функция использована при вычислении итогов внутри списка. Ссылка [Ref]- это интервал или ссылка, для которой подводятся итоги.
|
НОМЕР |
|
|
ФУНКЦИЯ |
|
|
FUNCTION |
|
|
НОМЕР |
|
|
ФУНКЦИЯ |
|
|
FUNCTION |
|
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
ФУНКЦИИ |
|
|
|
|
|
|
ФУНКЦИИ |
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
1 |
|
|
СРЗНАЧ |
|
AVERAGE |
7 |
|
|
СТАНДОТКЛОН |
|
STDEV |
||||||
2 |
|
|
СЧЁТ |
|
COUNT |
8 |
|
|
СТАНДОТКЛОНП |
|
STDEVP |
||||||
3 |
|
|
СЧЁТЗ |
|
COUNTA |
9 |
|
|
СУММ |
|
SUM |
||||||
4 |
|
|
МАКС |
|
MAX |
10 |
|
|
ДИСП |
|
VAR |
||||||
5 |
|
|
МИН |
|
MIN |
11 |
|
|
ДИСПР |
|
VARP |
||||||
6 |
|
|
ПРОИЗВЕД |
|
PRODUCT |
|
|
|
|
|
|
|
|
|
|||
Вразделе структуры имеются элементы управления трех типов:
Кнопки Скрыть детали [Hide Detail] – когда строки в группе отображаются, рядом с группой появляется кнопка.
Кнопки Отобразить детали [Show Detail] – когда группа строк скрыта, кнопка рядом
сгруппой становится кнопкой Отобразить детали. Нажав кнопку Отобразить детали, можно просмотреть строки таблицы данной группы.
Кнопки уровня. Каждая из пронумерованных кнопок уровня 
(максимум 8) представляет уровень организации в таблице; нажав кнопку уровня, можно скрыть все уровни деталей, относящихся к нажатой кнопке.
Например, на иллюстрации:
1 – Общий итог
2 – Промежуточные итоги для каждой группы
3 – Отдельные строки таблицы
Если к одним итогам (например, сумма) добавляются другие (среднее), то при добавлении новых итогов нужно снять флажок Заменить текущие итоги [Replace current subtotals].
Для удаления итогов с листа выбрать в окне Промежуточные итоги [Subtotals] команду Убрать все
[Remove All].
Многоуровневые итоги
Если необходимо подвести итоги по нескольким полям (например, по Наименованию товара, а затем внутри товаров по Поставщикам), то нужно:
1.Сделать многоуровневую сортировку (например, сперва по Наименованию товара, а затем внутри товаров по Поставщикам).
2.В окне Промежуточные итоги [Subtotal] подвести итоги по первому полю сортировки (например, Наименование товара).
3.Подвести итоги по второму полю сортировки (например, Поставщик), при этом обязательно снять флажок Заменить промежуточные итоги [Replace current subtotals].
Центр Компьютерного обучения «Специалист» |
www.specialist.ru |
54
Microsoft Excel 2013. Уровень 2. Расширенные возможности
ПРАКТИКУМ:
Открыть файл 02_5 Промежуточные итоги.
На листе ЗАДАНИЕ1, ЗАДАНИЕ2, ЗАДАНИЕ3, ЗАДАНИЕ4, ЗАДАНИЯ5 решить соответствующие задачи.
Сохранить сделанные изменения в файле и закрыть его.
САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ
Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 14-1, 14-2 и 14-3.
Консолидация данных
Консолидация – объединение значений из нескольких диапазонов в один новый диапазон с выполнением операции. Объединяемые диапазоны могут находиться как на разных листах, так и
вразных книгах.
1.Выделить пустую ячейку листа, начиная с которой будет размещен консолидируемый диапазон.
2.На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Консолидация
[Consolidate].
3.В окне Консолидация [Consolidate] задать параметры:
www.specialist.ru |
Центр Компьютерного обучения «Специалист» |
55
Microsoft Excel 2013. Уровень 2. Расширенные возможности
В поле Функция [Function]выбрать функцию, которая будет применена к объединяемым данным.
Поставить курсор в поле Ссылка [Reference] и выделить первый диапазон консолидации вместе с заголовками, нажать Добавить [Add], и т.д. повторить для всех исходных диапазонов.
Чаще всего исходные диапазоны выделяются вместе с заголовками, особенно если порядок расположения данных может отличаться. В случае если в источнике присутствуют объединенные ячейки в строке заголовка, то следует выделять без заголовков (заголовки потом копируются дополнительно).
В группе Использовать в качестве имен [Use labels in] поставить флажки подписи верхней строки [Top row] (если исходные диапазоны были выделены с заголовками) и значения левого столбца [Left column].
4.OK.
Если необходимо консолидировать таблицы, сохранив связь с исходными данными, то выбрать флажок Создавать связи с исходными данными [Create links to source data]. При изменении данных в исходных таблицах, консолидированная таблица будет также изменяться. Связь работает, если консолидированная таблица находится на отдельном листе от исходных данных. Обновление происходит связанных ячеек, если в исходных таблицах будут изменяться критерии (например, наименование) или изменится количество строк (записей), то необходимо построить консолидацию заново.
Если исходные данные находятся в других файлах, то предварительно требуется открыть все эти файлы, а потом выполнять команды консолидации.
ПРАКТИКУМ:
Открыть файл 02_6 Консолидация.
На листе ЗАДАНИЕ1, ЗАДАНИЕ2, ЗАДАНИЕ 3выполнить задания.
Центр Компьютерного обучения «Специалист» |
www.specialist.ru |