Упражнение 7.43

1. Откройте рабочую книгу Свод и добавьте в нее лист с именем База.

2. Разместите на листе таблицу следующего содержания

3. Используя форму данных, добавьте в список еще две записи: Цаплин Виктор 35 Наладчик Среднее 19.04.06 9150 пригород и Аксиненко Надежда 52 Швея Высшее 19.04.06 8450 пригород.

4. Используя форму данных, найдите информацию о Голубкиной и измените размер ее оклада на 12300.

5. Используя форму данных, просмотрите все данные списка о швеях, чьи оклады превышают 9500 руб..

6. Используя Автофильтр, отобразите все данные списка по лицам с высшим образованием.

7. Используя Автофильтр, отобразите все данные списка по мотористам, имеющим среднее образование.

8. По каждому виду должности подсчитайте итог по полю Оклад, воспользовавшись командой Данные\Итоги, предварительно отменив Автофильтр и рассортировав данные списка.

9. Присвойте имя (например, Ателье) диапазону ячеек, содержащему все данные списка (включая заглавную строку).

10. Вставьте перед диапазоном со списком 9 пустых строк.

11. В ячейки A1 : H1 скопируйте шапку таблицы.

12. Используя Расширенный фильтр, отобразите все данные лиц с высшим образованием, используя для области критериев ячейки A1:H2. Изменив область критериев, отобразите все данные списка по электрикам.

13. Внесите изменения в область критериев, чтобы отобразить лишь записи списка с окладом только от 96000 до 12500 руб..

14. Используя Расширенный фильтр, поместите в любую свободную (пустую) область рабочего листа все данные списка по лицам, зачисленным в 2004 год, предварительно изменив область критериев, либо создав новую.

15. Используя Расширенный фильтр и новую область критериев, рядом с полученным результатом поместите данные по среднему образованию, указав фамилию, образование, дату зачисления и оклад, предварительно создав шапку новой таблицы.

16. Измените область критериев, добавив в качестве критерия только должность – швея.

17. Для заданного критерия отбора, т.е. швея, вычислите общую сумму:

• в ячейку В6 введите формулу расчета суммы БДСУММ, используя Мастер функции. Для указания диапазона базы данных выберите из списка имен ячеек (в левой части строки формул) имя соответствующего диапазона (Ателье), для задания поля укажите ячейку с названием поля Оклад, для задания диапазона критериев также воспользуйтесь мышью;

• в ячейку В5 введите текст «Сумма по критерию швея».

18. Используя функцию БСЧЕТ, подсчитайте в ячейке С6 количество окладов. Имя поля, указанного в окне Мастера функций, оставьте прежним. В ячейку С5 введите текст «Количество окладов».

19. Используя функцию ДСРЗНАЧ, определите в ячейке E6 средний оклад швеи. Введите в ячейку E5 текст “Средний оклад швеи”.

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

Сводные таблицы. Инструмент под названием Сводные таблицы позволяет анализировать, объединять большие объемы данных, представленных в виде таблиц, быстро рассчитывать промежуточные и общие итоги, находить и отбирать необходимые данные, изменять структуру их представления.

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

Создание сводных таблиц и изменение их структуры осуществляется после установки курсора в любую ячейку списка в пошаговом режиме с помощью Мастера сводных таблиц, окно которого появляется на экране после ввода команды Данные\Сводная таблица. Процесс включает три шага (окна).

На первом шаге в открывшемся диалоговом окне Мастер сводных таблиц указывается опция – источник данных, например в списке или базе данных Мiсrosоft Office Excel (рис.7.57).

Рис 7.57. Первое окно Мастера сводных таблиц на фоне списка

На втором шаге в окне задается диапазон ячеек, с которым будет работать Мастер сводных таблиц (рис. 7.58).

Рис. 7.58. Окно для задания диапазона

В окне третьего шага определяется место размещения сводной таблицы – на существующем или новом листе (рис. 7.59),

Рис. 7.59. Окно третьего шага

а после щелчка на поле Макет надо определить, значения каких полей списка будут использоваться в качестве заголовков строк (зона Строка), каких - в качестве заголовков столбцов (зона Столбец) и каких - в качестве данных (зона Данные), по которым следует подвести необходимые итоги (рис.7.60).

Рис. 7.60. Окно создания макета сводной таблицы

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

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

Рис. 7.61. Пример построенной сводной таблицы

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