Упражнения по теме 10

10.7.1. Сортировка и фильтрация списка

1. Откройте книгу Управление данными97 из папки Инф. Технологии. Перейдите на лист Полный ассортимент. Выполните преобразование книги.

2. Выполните сортировку списка по двум ключам: по полю Количество, затем по полю Сумма.

3. Выполните сортировку списка по трем ключам: по полю Товар, затем по полю Название, затем по полю Сумма.

4. Составьте список из 10 наиболее доходных видов товара. Для этого щелкните ячейку С9, выполните команду Фильтр. Щелкните стрелку в поле Сумма и выберите команду Первые 10.

5. Снова отобразите весь список, выбрав команду Все в поле Сумма.

6. Выведите список товаров с названиями Профессиональный или Деловой, используя команду Условие из поля Название.

7. Отобразите весь список товаров и выключите Фильтр.

8. Найдите, используя поле имени ячеек и диапазонов, два диапазона ячеек с именами Фильтр_1, Фильтр_2. Изучите созданные в них условия отбора строк списка товаров.

9. Щелкните ячейку С9, чтобы Excel обнаружил список на рабочем листе.

10. Выполните команду Данные / Фильтр / Расширенный фильтр, используя диапазон условий Фильтр_1.

11. Отобразите все строки списка. Снова выполните фильтрацию списка, но используя условия Фильтр_2. Отобразите все строки списка.

12. Измените диапазон условий Фильтр_2, добавив к существующим условиям отбор по полю Название. Условия отбора должны быть соединены логическим условием ИЛИ. Выполните фильтрацию списка полного ассортимента товаров.

13. Сохраните книгу Управление данными97.

10.7.2. Создание промежуточных итогов в списке

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

2. Выполните команду Промежуточные итоги. При каждом изменении в поле Товар выберите операцию Среднее (AVG) по полям Стоимость и Цена. Для этого установите флажки напротив этих полей. Установите флажки Заменить текущие итоги и Итоги под данными.

3. Добавьте вложенные итоги, выполнив команду Промежуточные итоги. При каждом изменении в поле Название выбирайте операцию Среднее (AVG) по полям Стоимость и Цена. Снимите флажок Заменить текущие итоги.

4. Обратите внимание, что в строках 43 и 44 содержатся общие итоговые значения. Слева от списка появились линии структуры. Щелкните на кнопке 3-го уровня, затем на кнопке 1-го уровня.

5. Уберите все промежуточные итоги в списке товаров. Для этого выполните команду Промежуточные итоги. Щелкните кнопку Убрать все.

6. Щелкните ячейку А45, в которой записана функция СЧЕТЕСЛИ. Эта функция выводит количество наименований товара, имеющих стоимость выше средней стоимости. Введите рядом аналогичную функцию, возвращающую количество наименований товара, имеющих название Деловой.

7. Сохраните книгу Управление данными 97.

10.7.3. Консолидация списков

1. Выполните консолидацию данных, расположенных на листах Ксероксы и Факсы. Для этого перейдите на имеющийся лист Отчет и щелкните ячейку С9. Выполните команду Консолидация на вкладке Данные. Обратите внимание на то, что список консолидируемых диапазонов уже введен, остается только нажать кнопку OK.

2. Изучите действие команд Группировать и Разгруппировать, на примере диапазона консолидированных данных на листе Отчет.

3. Проанализируйте полученную таблицу. Перейдите на лист Факсы и измените данные в поле Количество для любой модели. Убедитесь в том, что на листе Отчет эти изменения мгновенно отражаются.

10.7.4. Построение сводной таблицы

1. Для построения сводной таблицы перейдите на лист Полный ассортимент, щелкните ячейку С9.

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

3. В окне справа сформируйте макет сводной таблицы. Для этого кнопку поля Название перетащите в область Названия строк. Кнопку поля Товар перетащите в область Названия столбов.

4. В область макета Значения перетащите кнопку поля Сумма. В качестве вычисляемой функции Excel автоматически выбирает функцию суммирования данных по указанному полю Сумма. Раскройте список в области Значения. Просмотрите другие функции. В поле Пользовательское имя введите имя поля сводной таблицы: Выручка.

5. Просмотрите полученную сводную таблицу. Добавьте данные в таблицу. Перетащите кнопку поля Количество в область Значения и расположите ее над кнопкой поля Сумма. Введите пользовательское имя нового поля: Продано всего.

6. Просмотрите полученную сводную таблицу. Теперь реорганизуйте сводную таблицу. В окне макета сводной таблицы перетащите кнопку поля Товар из области Названия столбов в область Фильтр отчета.

7. Просмотрите данные сводной таблицы, используя фильтр отчета.

8. Удалите фильтр отчета, для этого перенесите кнопку поля Товар в область Названия строк, ниже кнопки поля Название.

9. Просмотрите результат последнего действия. Переместите кнопку поля Товар выше кнопки поля Название. Просмотрите полученную сводную таблицу.

10. Измените данные на листе Полный ассортимент в поле Количество, для товара Факс, Деловой. Обновите данные сводной таблицы, при необходимости.

11. Сохраните книгу Управление даными97. Выполните копирование листов с результатами выполненных упражненийв новую книгу с и сохраните ее под названием Упражнение 10_6_1- 6_4.

Контрольные вопросы

1. Перечислите правила создания и ведения списков в Excel.

2. Как сортировать список по трем полям одновременно?

3. Как создается пользовательский порядок сортировки списка?

4. Для чего используется команда Фильтр?

5. Как создается диапазон условий для расширенного фильтра?

6. Какие предварительные операции надо выполнить со списком, чтобы получить промежуточные итоги с помощью команды Промежуточные итоги?

7. Что такое сводная таблица и как она создается?

8. Как переименовать поле сводной таблицы в области Значения?

9. Как обновить данные в сводной таблице?