Работа с базами данных в Microsoft Excel
База данных представляет собой упорядоченную последовательность наборов данных, описывающих характеристики отдельных объектов. Набор характеристик одного объекта является отдельной записью. Для каждой характеристики в записях отводится отдельное место, называемое полем. Поля имеют уникальные имена. Записи нумеруются.
Характерными операциями для обработки информации в базах данных являются сортировка, поиск, фильтрация, выборка записей. Для выполнения этих операций разрабатываются специальные прикладные программы, называемые системами управления базами данных, однако с небольшими базами данных можно работать и в табличном процессоре, т.к. структура электронной таблицы позволяет организовывать ее блоки в виде базы данных, располагая поля в столбцах, а записи – в строках таблицы.
Таблицы Excel
Удобным средством для организации базы данных являются таблицы Excel.
Таблица Excel представляет собой прямоугольный блок ячеек со структурированными данными, имеющий строку заголовков, обозначающих содержимое столбцов.
В заголовке каждого столбца имеется раскрывающийся список, который можно использовать для сортировки и фильтрации данных.
При прокрутке строк таблицы заголовки столбцов замещают буквенные заголовки столбцов рабочего листа, вследствие чего они всегда видны на экране.
В формулах вместо адресов ячеек используются заголовки столбцов (структурированные ссылки).
Формула, введенная в одну ячейку, автоматически копируется во все ячейки столбца.
В таблицу легко добавляются новые строки и столбцы.
Создание таблицы
I способ:
1. Выделить диапазон ячеек, предназначенный для преобразования в таблицу. Ячейки могут быть как пустыми, так и содержать данные (в последнем случае достаточно поместить курсор в любую ячейку диапазона).
2. На вкладке Вставка в группе Таблицы щелкнуть по кнопке Таблица. Можно воспользоваться клавишами быстрого доступа Ctrl+L или Ctrl+T.
3. В открывшемся диалоговом окне указать диапазон ячеек таблицы. По умолчанию предлагается диапазон, который был выделен перед обращением к команде.
4. Если первая строка выделенного диапазона должна стать строкой заголовков, установить флажок Таблица с заголовками. Если этот флажок не будет установлен, к выделенному диапазону добавится строка со стандартными заголовками. Стандартные заголовки можно изменить, дважды щелкнув по ячейке и введя нужный текст.
II способ:
1. Выделить диапазон ячеек, предназначенный для преобразования в таблицу (пустой или заполненный данными).
2. На вкладке Главная в группе Стили открыть список Форматировать как таблицу.
3. Выбрать нужный стиль таблицы.
4. В открывшемся диалоговом окне указать диапазон ячеек таблицы и при необходимости установить флажок Таблица с заголовками.
Выделение строк и столбцов таблицы
Чтобы выделить столбец таблицы, надо установить указатель мыши на верхнюю границу выделяемого столбца так, чтобы указатель принял вид направленной вниз черной стрелки, и нажать кнопку мыши.
Для выделения нескольких подряд идущих столбцов надо переместить указатель мыши при нажатой кнопке по верхней границе этих столбцов.
Для выделения нескольких несмежных столбцов надо выделить один из них, а затем выделять остальные при нажатой клавише Ctrl.
Чтобы выделить строку таблицы, надо установить указатель мыши на левую границу выделяемой строки так, чтобы указатель принял вид направленной вниз черной стрелки, и нажать кнопку мыши.
Для выделения нескольких подряд идущих строк надо переместить указатель мыши при нажатой кнопке по левой границе этих строк.
Для выделения нескольких несмежных строк надо выделить одну из них, а затем выделять остальные при нажатой клавише Ctrl.
Добавление и удаление строк и столбцов в таблицах
Чтобы добавить новую строку или новый столбец в конец таблицы, можно воспользоваться одним из следующих способов:
- ввести данные в эту строку или столбец: Excel автоматически расширит таблицу;
- для добавления пустой строки в конец таблицы в последней ячейке последнего столбца нажать клавишу Tab;
- установить указатель мыши на метку изменения размера в правом нижнем углу таблицы и переместить ее вправо (для добавления столбцов) или вниз (для добавления строк).
Добавить новые строки в конец таблицы или новые столбцы слева и (или) справа от таблицы можно, выбрав на вкладке Работа с таблицами – Конструктор в группе Свойства команду Изменить размер таблицы и указав новый диапазон данных для таблицы.
Чтобы вставить новые строки в середину таблицы, надо:
1. Выделить несколько строк таблицы, над которыми надо вставить новые (количество вставленных строк будет равно количеству выделенных).
2. На вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить строки таблицы сверху или в контекстном меню выделенных строк в списке Вставить выбрать команду Вставить строки таблицы сверху.
Если выделена последняя строка таблицы, то новые строки можно вставить как сверху, так и снизу выделенных строк. В последнем случае надо выбрать команду Вставить строки таблицы снизу.
Чтобы вставить новые столбцы в середину таблицы, надо:
1. Выделить несколько столбцов таблицы, слева от которых надо вставить новые (количество вставленных столбцов будет равно количеству выделенных).
2. На вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить столбцы таблицы слева или в контекстном меню выделенных столбцов в списке Вставить выбрать команду Вставить столбцы таблицы слева.
Если выделен последний столбец таблицы, то новые столбцы можно вставить как слева, так и справа от выделенных столбцов. В последнем случае надо выбрать команду Вставить столбцы таблицы справа.
Вычисляемые столбцы
Чтобы создать вычисляемый столбец, надо вставить в таблицу пустой столбец, а затем ввести формулу в любую ячейку этого столбца. Формула будет автоматически скопирована во все ячейки столбца.
Ввод, перемещение или копирование формулы в столбец, уже содержащий данные, не приводит к автоматическому созданию вычисляемого столбца.
В вычисляемый столбец можно включать формулы, отличающиеся от формулы столбца, которые становятся исключениями.
Исключения вычисляемого столбца создаются при выполнении любого из следующих действий.
- ввод в ячейку вычисляемого столбца данных, не являющихся формулой;
- удаление формулы из одной или нескольких ячеек вычисляемого столбца;
- копирование в вычисляемый столбец данных, не соответствующих формуле столбца;
- ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений;
- ввод формулы в ячейку вычисляемого столбца и нажатие кнопки Отменить на панели быстрого доступа.
В формулах вычисляемых столбцов используются структурированные ссылки на табличные данные. Структурированная ссылка имеет следующие составные части:
- имя таблицы;
- указатель специального элемента таблицы, с помощью которого можно ссылаться на конкретные части таблицы, например, на строку;
- указатель столбца.
Имя присваивается таблице при создании.
Указатель специального элемента начинается с символа # и заключается в квадратные скобки.
Указатель столбца представляет собой заключенный в квадратные скобки заголовок столбца.
Указатель специального элемента и указатель столбца вместе составляют указатель таблицы. Указатель таблицы заключается в квадратные скобки.
Пример структурированной ссылки:
Таблица4[[#Эта строка];[Должность]]
Ссылки на табличные данные в формуле будут автоматически преобразованы в структурированные, если при вводе формулы не набирать адреса ячеек на клавиатуре, а указывать их мышью или клавишами управления курсором.
Изменить формулу в вычисляемом столбце, не содержащем исключений, можно одним из следующих способов:
- отредактировать формулу в любой ячейке столбца;
- скопировать другую формулу в любую ячейку столбца.
Чтобы удалить вычисляемый столбец, надо на вкладке Главная в группе Ячейки в списке Удалить выбрать команду Удалить столбцы таблицы.
Строка итогов таблицы
Для добавления к таблице итоговой строки надо:
1. Выделить любую ячейку таблицы.
2. На вкладке Работа с таблицами – Конструктор в группе Параметры стилей таблиц установить флажок Строка итогов.
3. В строке итогов щелкнуть по ячейке столбца, для которого нужно рассчитать итог, и из раскрывающегося списка выбрать нужную функцию.
Форма данных
Форма – это такой режим базы данных, в котором записи выводятся на экран по одной.
Форма данных выводится на экран с помощью кнопки Форма. Этой кнопки нет среди ленточных команд, поэтому сначала ее необходимо добавить на панель быстрого доступа:
1. Щелкнуть мышью по кнопке раскрытия списка на панели быстрого доступа.
2. Выбрать строку Другие команды.
3. В поле Выбрать команды из выбрать вариант Команды не на ленте.
4. Выбрать в списке команду Форма.
5. Щелкнуть по кнопке Добавить.
Чтобы вывести на экран форму данных, надо поместить курсор в любое место таблице и щелкнуть по кнопке Форма на панели быстрого доступа.
В Microsoft Excel форма данных представляет собой диалоговое окно, в котором поочередно выводятся отдельные полные записи списка.
К основным компонентам диалогового окна формы данных относятся: названия столбцов (имена полей), поля, формирующие запись, кнопки команд, полоса прокрутки.
Поля, формирующие запись, выводятся в виде поля ввода в том случае, если они содержат значения констант, которые можно отредактировать. Вычисляемые поля, содержащие результаты вычислений по формулам, не подлежат редактированию и не выводятся в полях ввода.
С помощью кнопок команд в диалоговом окне формы данных можно выполнять следующие операции:
- добавить новую запись: щелкнуть по кнопке Добавить и ввести данные в поле ввода;
- удалить выведенную на экран запись: щелкнуть по кнопке Удалить;
- отменить изменения в выведенной на экран записи: щелкнуть по кнопке Вернуть;
- перейти к предыдущей или последующей записи: с помощью кнопок Назад и Далее, кроме того, для перехода к другим записям можно использовать полосу прокрутки;
- найти запись по заданным критериям: с помощью кнопки Критерии.
Поиск записей по заданным критериям
После выбора в диалоговом окне формы данных кнопки Критерии Microsoft Excel изменяет форму данных таким образом, что вводимые в поля данные интерпретируются как условия поиска, называемые критериями сравнения.
Критерий может представлять собой последовательность символов, которым должна соответствовать запись, или выражение. Если критерии задаются в нескольких полях, то будут найдены записи, удовлетворяющие всем заданным критериям.
Чтобы найти удовлетворяющие критериям записи, следует щелкать по кнопкам Далее и Назад. Чтобы выйти из режима поиска, надо щелкнуть по кнопке Правка.
Сортировка данных в таблице
В процессе сортировки таблицы Microsoft Excel переупорядочивает строки в соответствии с содержимым одного или более столбцов. Сортировка по нескольким столбцам используется для группировки данных с одинаковыми значениями в одном столбце и последующей сортировки данных другого столбца в группах с одинаковыми значениями.
Таблицы можно сортировать в возрастающем (от 0 до 9, от А до Я, от A до Z) или убывающем (от 9 до 0, от Я до А, от Z до A) порядке.
При создании таблицы в заголовке каждого столбца появляется кнопка, раскрывающая список, который можно использовать для сортировки и фильтрации данных. Эту кнопку можно вывести на экран или убрать с экрана также одним из следующих способов (предварительно поместив курсор в любую ячейку таблицы):
- на вкладке Главная в группе Редактирование в списке Сортировка и фильтр выбрать команду Фильтр;
- на вкладке Данные в группе Сортировка и фильтр щелкнуть по кнопке Фильтр;
- нажать клавиши Ctrl + Shift + L.
Для сортировки таблицы по значениям одного столбца надо открыть раскрывающийся список в заголовке этого столбца выбрать порядок сортировки.
Отсортировать таблицу по нескольким столбцам можно одним из двух способов.
I способ: сначала выполнить сортировку по последнему сортируемому столбцу, затем по предпоследнему и т.д. до первого сортируемого столбца.
II способ:
1. На вкладке Главная в группе Редактирование в списке Сортировка и фильтр выбрать команду Настраиваемая сортировка или в контекстном меню любой ячейки таблицы выбрать команду Сортировка, затем ‑ Настраиваемая сортировка.
2. В открывшемся диалоговом окне указать первый по значимости столбец для сортировки, критерий и порядок сортировки.
3. Щелкнуть по кнопке Добавить уровень и в новой строке указать следующий по значимости столбец для сортировки, критерий и порядок сортировки для этого столбца.
4. Повторить п. 3 для остальных сортируемых столбцов.
5. Щелкнуть по кнопке ОК.
После сортировки таблицы на копках раскрытия списка в заголовках столбцов, по которым проводилась сортировка, появляются маленькие вертикальные стрелки.
Фильтрация данных в таблице
Фильтрация – это отбор записей, удовлетворяющих заданным условиям. Фильтрация выводит на экран нужные записи, временно скрывая остальные.
Можно применять одновременно несколько фильтров. Фильтры действуют по дополнительному принципу, т. е. каждый новый фильтр накладывается на фильтр, примененный до него, и в еще большей степени ограничивает подмножество данных.
После фильтрации на кнопках раскрытия списка в заголовках столбцов, по которым задавались условия отбора, появляются специальные значки.
Отфильтрованные данные можно копировать в другие диапазоны ячеек, изменять, форматировать, использовать для построения диаграмм.
В Microsoft Excel имеется несколько способов фильтрации данных в таблицах.
Фильтр по выделенному
Этот способ позволяет использовать в качестве условия отбора данные, содержащиеся в какой-либо ячейке таблицы. При этом условием отбора может быть значение ячейки, цвет, цвет шрифта, значок.
1. Выбрать ячейку, данные которой нужно использовать в качестве условия отбора.
2. В контекстном меню этой ячейки выбрать команду Фильтр.
3. Выбрать нужный вариант фильтра.
Автофильтр
С помощью этого способа фильтрации также можно использовать в качестве условия отбора данные из столбца таблицы, но в отличие от фильтра по выделенному можно задать несколько значений столбца.
1. Щелкнуть по кнопке раскрытия списка в заголовке столбца, по которому нужно отфильтровать данные.
2. В открывшемся списке выбрать значения, которые нужно использовать в качестве условия отбора, т.е. установить флажки рядом с этими значениями и снять флажки у остальных значений.
3. Щелкнуть по кнопке ОК.
В этом же списке есть команда для снятия фильтра.
Пользовательский автофильтр
Пользовательский фильтр позволяет создавать условия отбора с помощью операторов сравнения и логических операторов.
1. Щелкнуть по кнопке раскрытия списка в заголовке столбца, по которому нужно задать условия отбора.
2. В зависимости от типа данных столбца выбрать команду Текстовые фильтры, Числовые фильтры или Фильтры по дате.
3. В открывшемся списке выбрать нужный оператор сравнения или команду Настраиваемый фильтр.
4. В открывшемся диалоговом окне Пользовательский автофильтр ввести условие отбора.
Чтобы отфильтровать записи, удовлетворяющие одному условию по данному полю, выбрать необходимый оператор сравнения в первом поле и значение – справа от него.
Чтобы отфильтровать записи, удовлетворяющие одновременно двум условиям по данному полю, выбрать оператор и значение в первой строке диалогового окна, переключатель И, оператор сравнения и значение во второй строке диалогового окна.
Чтобы отфильтровать записи, удовлетворяющие одному из двух условий по данному полю, выбрать оператор и значение в первой строке диалогового окна, переключатель ИЛИ, оператор сравнения и значение во второй строке диалогового окна.
5. Щелкнуть по кнопке ОК.
Расширенный фильтр
Для фильтрации списка по более сложным условиям используется расширенный фильтр.
При использовании расширенного фильтра условия вводятся в блок таблицы. Простые условия отбора составляются из имени поля, оператора сравнения и значения. Сложные условия включают несколько простых условий, связанных логическими операторами И, ИЛИ.
Для использования расширенного фильтра сначала надо подготовить блок условий:
1. Для блока условий отвести пустой блок рабочего листа, расположенный вне таблицы. Блок условий и таблицу должна разделять хотя бы одна пустая строка.
2. В верхнюю строку блока условий скопировать имена полей, для которых надо задать условия отбора. Если для одного поля задается несколько условий, то имя этого поля копируется несколько раз.
3. В следующих строках (под строкой с именами полей) ввести условия отбора в соответствии со следующими правилами:
- каждое условие вводится в столбце с именем того поля, для которого задается условие;
- если искомые записи должны удовлетворять нескольким условиям одновременно, то эти условия вводятся в одной строке (соответствует логическому оператору И);
- если искомые записи должны удовлетворять хотя бы одному из условий, то эти условия вводятся в разные строки (соответствует логическому оператору ИЛИ).
Примеры блоков условий:
1. Фамилии сотрудников начинаются на букву С:
| Фамилии |
| С* |
2. Стаж работы больше 5 лет:
| Стаж работы |
| >5 |
3. Оклад от 12000 до 20000 руб.:
| Оклад | Оклад |
| >12000 | <20000 |
4. Стаж работы меньше 5 лет или оклад меньше 15000 руб.:
| Стаж работы | Оклад |
| <5 | |
| <15000 |
5. Техники конструкторского отдела, у которых либо стаж работы больше 5 лет, либо оклад больше 12000 руб.:
| Отдел | Должность | Стаж работы | Оклад |
| конструкторский | техник | >5 | |
| конструкторский | техник | >12000 |
Чтобы применить расширенный фильтр
1. Подготовить блок условий.
2. Выделить любую ячейку из таблицы.
3. На вкладке Данные в группе Сортировка и фильтр выбрать команду Дополнительно.
4. В поле Исходный диапазон ввести ссылку на диапазон таблицы.
5. В поле Диапазонусловий ввести ссылку на блок условий.
6. Чтобы показать результат фильтрации, скрыв ненужные строки в списке, установить переключатель Обработка в положение Фильтровать список на месте.
Чтобы скопировать отфильтрованные строки в другую область листа, надо установить переключатель Обработка в положение Скопировать результаты в другое место, а затем в поле Поместить результат в диапазон указать левую верхнюю ячейку области вставки.
При копировании отобранных строк в другое место можно указать столбцы, включаемые в копию. Для этого перед фильтрацией надо скопировать заголовки нужных столбцов в первую строку области вставки результатов, а при фильтрации в поле Поместить результат в диапазон ввести ссылку на эту строку. Тогда отфильтрованная таблица будет включать только столбцы, заголовки которых были скопированы.
7. Щелкнуть по кнопке ОК.
В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы.
Синтаксис условия отбора в этом случае:
= Ссылка Оператор сравнения Выражение
Здесь ссылка – ссылка либо на ячейку с именем столбца, на значение которого накладывается условие, либо на ячейку с первой записью в этом столбце; выражение составляется из ссылок на ячейки, значений, арифметических операторов, функций.
В качестве заголовка условия нельзя использовать заголовок столбца. Можно оставить его пустым или использовать заголовок, не совпадающий ни с одним из заголовков столбцов таблицы.
Пример: блок условия для отбора данных о сотрудниках, зарплата которых превышает среднюю:
| =G50>СРЗНАЧ(Таблица4[Оклад]) |
Функции для работы с базами данных
В Microsoft Excel имеются функции для работы с базами данных. Каждая из этих функций использует три аргумента: база данных, поле, критерий:
- База данных − это диапазон ячеек, в которых располагается база данных;
- Поле определяет столбец, используемый функцией; аргумент Поле может быть задан как адрес ячейки, содержащей имя поля, как текст с названием поля (столбца) в кавычках или как число, задающее положение столбца в списке;
- Критерий − ссылка на диапазон ячеек, задающих условие отбора.
Примеры использования функций
Дана база данных:
1. Вычислить количество сотрудников в конструкторском отделе.
Используется функция БСЧЕТА() с аргументами:
- база данных ‑ A1:F8;
- поле ‑ A1 (адрес ячейки с заголовком столбца Фамилии);
- 
критерий ‑ H1:H2 (диапазон ячеек, содержащих условие отбора).
2. Вычислить сумму выплат всех инженеров.
Используется функция =БДСУММ(A1:F8; F1; J1:J2).
3. 
Используя таблицу подстановки, вычислить количество сотрудников в каждом отделе.
Подготавливается таблица:
В ячейку M1 вводится формула
=БСЧЕТА(A1:F8; A1; H1:H2).
В диалоговом окне команды Таблица данных в поле Подставлять значения по строкам в дается ссылка на ячейку H2.
4. 
Используя таблицу подстановки, вычислить сумму зарплат и выплат для всех инженеров.
Подготавливается таблица:
В ячейку P1 вводится формула
= БДСУММ (A1:F8; F1; J1:J2).
В диалоговом окне команды Таблица данных в поле Подставлять значения по строкам в дается ссылка на ячейку F1.
Используя таблицу подстановки, вычислить сумму зарплат и налогов по каждому отделу.
Подготавливается таблица:
В ячейку R1 вводится формула =БДСУММ (A1:F8; E1; H1:H2).
В диалоговом окне команды Таблица данных в поле Подставлять значения по строкам в дается ссылка на ячейку H2, в поле Подставлять значения по столбцам в − ссылка на ячейку E1.
Практическая работа 9
Работа с базами данных в Microsoft Excel
1. Создать таблицу, содержащую сведения о сотрудниках предприятия. Столбцы таблицы: Фамилия, Отдел, Должность, Дата найма.
Заполнить таблицу произвольными данными (10 строк); в столбце Отдел использовать 3−4 названия (например, бухгалтерия, отдел кадров, транспортный отдел, конструкторский отдел), в столбце Должностьиспользовать 5−6 названий (например, техник, инженер, экономист, водитель и т. д.).
2. Добавить к таблице вычисляемые столбцы: Стаж работы, Оклад, Надбавка, Премия, Всего начислено, Пенсионный фонд, Налог, Выплатить.
Значения столбца Оклад определить с помощью функции просмотр, составив отдельную таблицу должностных окладов.
Стаж работы рассчитывается как частное от деления разности между текущей датой и датой найма на количество дней в году;
Надбавка составляет 5% от оклада для работников со стажем от 5 до 10 лет и 10% от оклада для работников со стажем более 10 лет;
Остальные столбцы рассчитываются по следующим формулам:
Премия = 20% (Оклад + Надбавка);
Всего начислено = Оклад + Надбавка + Премия;
Пенсионный фонд = 1% Всего начислено;
Налог = 13%(Всего начислено – Пенсионный фонд);
Выплатить = Всего начислено– Пенсионный фонд – Налог.
3. Добавить к таблице еще 5 строк.
4. Добавить к таблице итоговую строку, содержащую суммы значений столбцов Оклад, Надбавка, Премия, Всего начислено, Пенсионный фонд, Налог, Выплатить.
5. Назначить всем столбцам, содержащим денежные величины, финансовый формат.
6. Присвоить рабочему листу имя Сведения о сотрудниках. Это же название можно использовать в качестве заголовка таблицы.
7. Используя форму данных, добавить в таблиц еще 5 записей.
8. Используя форму данных, выполнить поиск записей по следующим критериям:
- заданная должность;
- заданный отдел;
- стаж работы больше заданного;
- заданная должность и оклад меньше заданного;
- заданный отдел и стаж работы больше заданного.
9. Отсортировать таблицу по фамилиям.
10. Отсортировать таблицу по отделам (от А до Я), а внутри отделов – по стажу работы сотрудников (от максимального к минимальному). Использовать способ многократной сортировки от менее значимых к более значимым столбцам.
11. Отсортировать таблицу по отделам, внутри каждого отдела – по должностям, а для одинаковых значений поля Должность – по фамилиям. Порядок сортировки по всем столбцам ‑ от А до Я. Использовать диалоговое окно команды Настраиваемая сортировка.
12. Используя фильтр по выделенному, отобрать данные о сотрудниках заданного отдела.
13. Используя автофильтр, отобрать данные:
- о сотрудниках двух заданных отделов;
- о сотрудниках, занимающих одну из трех заданных должностей;
- о сотрудниках заданного отдела, занимающих одну из двух заданных должностей.
14. Используя пользовательский автофильтр, отобрать данные:
- о сотрудниках, чья фамилия начинается с заданной буквы;
- о сотрудниках со стажем работы больше заданного;
- о сотрудниках, стаж работы которых находится в заданном диапазоне;
- о сотрудниках с окладом выше среднего;
- о 5-ти сотрудниках с наибольшими премиями;
- о сотрудниках, дата найма которых находится в заданном диапазоне;
- о сотрудниках, нанятых в прошлом году;
- о сотрудниках, нанятых во 2-м квартале;
- о сотрудниках заданного отдела со стажем работы выше среднего.
15. Используя расширенный фильтр, отобрать данные о сотрудниках с окладом в заданном диапазоне. В отфильтрованную таблицу включить поля Фамилия, Должность, Отдел, Оклад.
16. Используя расширенный фильтр, отобрать данные о сотрудниках, у которых либо стаж работы больше заданного, либо оклад меньше заданного. В отфильтрованную таблицу включить поля Фамилия, Отдел, Стаж работы, Оклад.
17. Используя расширенный фильтр, отобрать данные о сотрудниках со стажем работы в заданном диапазоне и с окладом в заданном диапазоне. В отфильтрованную таблицу включить поля Фамилия, Должность, Стаж работы, Оклад.
18. Используя расширенный фильтр, отобрать данные о сотрудниках заданного отдела, занимающих заданную должность, у которых либо стаж работы больше заданного, либо оклад больше заданного. В отфильтрованную таблицу включить поля Фамилия, Отдел, Должность, Стаж, Оклад.
19. Используя расширенный фильтр, отобрать данные о сотрудниках, у которых стаж работы либо меньше 2 лет, либо от 10 до 15 лет. В отфильтрованную таблицу включить поля Фамилия, Отдел, Стаж, Оклад.
20. Используя расширенный фильтр, отобрать данные о сотрудниках:
- оклад которых выше среднего;
- оклад которых выше среднего, а стаж работы от 5 до 15 лет;
- оклад которых выше среднего, а стаж работы – меньше среднего;
В условиях отбора использовать функцию СРЗНАЧ.
В отфильтрованные таблицы включить поля Фамилия, Стаж работы, Оклад.
21. Используя функции базы данных, вычислить:
- количество сотрудников со стажем работы больше заданного;
- количество сотрудников с окладом меньше среднего;
- количество сотрудников заданного отдела с заданной должностью;
- сумму окладов всех сотрудников с заданной должностью;
- сумму надбавок всех сотрудников заданного отдела со стажем больше 10 лет;
- наименьшую премию среди сотрудников со стажем работы от 5 до 15 лет;
- наименьший налог среди сотрудников с окладом больше среднего.
22. Используя таблицу подстановки и функции базы данных, создать таблицы, отображающие:
- количество сотрудников, занимающих каждую должность;
- сумму выплат, окладов, надбавок и премий в каждом отделе;
- сумму выплат, «всего начислено» и налогов по каждой должности;
- количество различных должностей по отделам.
Создание сводных данных
Суммирование данных списка
Под списком понимается набор строк, содержащий однотипные данные и удовлетворяющий следующим условиям:
- первая строка диапазона данных содержит заголовки столбцов;
- столбцы содержат однотипные данные;
- отсутствуют пустые строки и столбцы.
Для быстрого суммирования данных списка и разбиения списка на группы программа Microsoft Excel предлагает процедуру вставки автоматически рассчитанных итогов.
При подведении промежуточных итогов список разбивается на группы: в одну группу объединяются записи, имеющие одинаковые значения в заданном столбце.
При автоматическом подведении итогов строки промежуточного и общего итогов вставляются в список, который структурируется.
Microsoft Excel рассчитывает промежуточные итоги с помощью выбранной итоговой функции. Можно одновременно вывести на экран промежуточные итоги, рассчитанные разными способами.
Нельзя вставить промежуточные итоги в диапазон данных, преобразованный в таблицу Excel.
Подведение промежуточных итогов
1. Отсортировать список по столбцу, по которому надо образовать группы.
2. Выделить любую ячейку в списке.
3. На вкладке Данные в группе Структура выбрать командуПромежуточные итоги.
4. В открывшемся диалоговом окне в поле При каждом изменении в выбрать имя столбца, по которому необходимо образовать группы.
5. В поле Операция выбрать одну или несколько функций, необходимых для подведения итогов:
- сумма – сумма чисел (используется по умолчанию для подведения итогов по числовым полям);
- количество − количество записей (используется по умолчанию для подведения итогов по нечисловым полям);
- среднее – среднее чисел;
- максимум – максимальное число;
- минимум – минимальное число.
Сведения обо всех функциях можно получить, воспользовавшись справочной системой.
6. В поле Добавить итоги по:выбрать столбцы, по которым необходимо подвести итоги.
7. Если нужно разместить итоговые строки под данными, включить режим Итоги под данными, иначе – выключить этот режим.
8. Чтобы вставить разрывы страницы после каждой группы, включить режим Конец страницы между группами.
9. Если нужно заменить ранее вычисленные итоги новыми, включить режим Заменить текущие итоги.
10. Щелкнуть по кнопке ОК.
В результате операции для каждой группы одинаковых элементов в выбранном поле будет вставлена строка промежуточного итога, а в конец списка добавлена строка общего итога.
Группы можно разделить на подгруппы и вставить вложенные уровни промежуточных итогов для подгрупп. Для этого надо:
1. Отсортировать данные по тем столбцам, по которым надо образовать группы и подгруппы.
2. Выполнить операцию подведения итогов, указав имя столбца, по которому данные надо разделить на группы.
3. Повторно выполнить операцию подведения итогов, указав имя столбца, по которому группы надо разделить на подгруппы, и выключив режим Заменить текущие итоги.
Чтобы подвести итоги, рассчитанные разными способами, надо несколько раз выполнить операцию подведения итогов, выбирая разные функции. При этом режим Заменить текущие итоги должен быть выключен. В результате для каждой группы будет вставлено несколько строк промежуточных итогов (столько, сколько использовано функций).
Чтобы удалить итоговые строки, надо на вкладке Данные в группе Структура выбрать командуПромежуточные итоги и в открывшемся диалоговом окне щелкнуть по кнопке Убрать все.
Структурирование рабочих листов
Данные имеют иерархическую организацию, если их можно подразделить на подробные и итоговые данные. Подробные данные – это строки (или столбцы) с исходными данными, по которым вычисляются итоги.
Если данные подразделяются на группы, группы – на подгруппы, и т.д., то они имеют многоуровневую иерархическую организацию. При этом каждый внутренний уровень содержит подробные данные для соответствующего внешнего уровня.
Для группировки и суммирования данных с иерархической организацией можно создавать многоуровневую структуру.
Структура листа – это инструмент, позволяющий управлять скрытием или выводом на экран подробных данных.
Рис. 11
Microsoft Excel позволяет создавать многоуровневую структуру с числом уровней в пределах восьми. Структура может быть создана из строк, из столбцов или из строк и столбцов одновременно.
При структурировании рабочего листа на экран выводятся панели с элементами управления структурой: слева ‑ для структуры, созданной из строк, и сверху – для структуры из столбцов. На панелях изображаются лини, объединяющие группы, называемые линейками уровня. На этих линиях расположены кнопки Скрыть детали:
Щелчок по такой кнопке скрывает все строки группы, оставляя только итоговую строку. После этого кнопка Скрыть детали заменяется кнопкой Отобразить детали , щелчок по которой возвращает на экран скрытые строки. Кроме того, для управления структурой можно использовать команды меню: на вкладке Данные в списке Структура кнопки Отобразить детали и Скрыть детали.
Над структурными линиями расположены кнопки уровней, щелчок по которым скрывает все строки ниже выбранного уровня: чтобы увидеть только общий итог, надо щелкнуть по кнопке первого уровня, а чтобы увидеть все данные − по кнопке последнего уровня.
На рисунках представлен структурированный по столбцам и по строкам рабочий лист с отображением разных уровней структуры: на рис. 11 показан третий уровень вертикальной (по строкам) и горизонтальной (по столбцам) структур.
На рис. 12 – вторые уровни обеих структур:
На рис. 13 – третий уровень вертикальной и второй уровень горизонтальной структуры:
Создание структуры
Существует несколько способов создания структуры.
I способ – при помощи процедуры подведения итогов: создавая промежуточные итоги, Microsoft Excel автоматически структурирует данные.
Этим способом можно создать только вертикальную структуру. Кроме того, если для разных столбцов требуются различные итоговые функции, вставляется несколько итоговых строк, по одной для каждой функции, что загромождает таблицу.
II способ – командой Создание структуры:
1. 
После каждой группы данных вставить строку (для вертикальной структуры) или столбец (для горизонтальной структуры) и ввести в них итоговые формулы. В случае создания многоуровневой структуры вставить итоговые строки или столбцы для всех уровней.
2. Поместить курсор в любую ячейку диапазона данных.
3. На вкладке Данные в группе Структура в списке Группировать выбрать команду Создание структуры.
Таким способом можно создать и вертикальную, и горизонтальную структуру. Различные итоговые функции можно разместить в одной строке (если вставлять их вручную).
Для удаления структуры надо на вкладке Данные в группе Структура в списке Группировать выбрать команду Удалить структуру.
Программа Excel создает структуру описанным способом на основании анализа формул. Если таблица не содержит формул, а это может случиться, например, при импортировании данных из другой программы, структуру можно создать способом группировки данных вручную.
III способ – группировка данных вручную.
1. После каждой группы данных вставить строку и вручную ввести в нее итоговые формулы.
2. Выделить строки (или столбцы), которые надо включить в группу, кроме строки с итоговыми формулами.
3. На вкладке Данные в группе Структура в списке Группировать выбрать команду Группировать или нажать комбинацию клавиш Alt+Shift+®.
4. Повторить п. 2 и 3 для каждой группы.
При создании многоуровневой структуры группировка производится несколько раз, начиная с самого нижнего уровня иерархии: сначала подробные данные объединяются в подгруппы, затем подгруппы – в группы более высокого уровня и т.д. до верхнего уровня.
Неправильно сгруппированные данные можно разгруппировать. Для этого надо выделить их и на вкладке Данные в группе Структура в списке Группировать выбрать команду Разгруппироватьили нажать комбинацию клавиш Alt+Shift+.
Консолидация данных
Консолидация данных – это объединение значений из нескольких диапазонов данных. Диапазоны, данные которых используются для консолидации, называются исходными областями. Диапазон, в который помещаются консолидированные данные, называется конечной областью. Исходные области могут находиться на одном рабочем листе, на разных листах, в разных рабочих книгах.
Исходные области могут представлять собой таблицы, состоящие из одинаковых заголовков столбцов и строк, а также из одинаковых столбцов, но разных строк или одинаковых строк, но разных столбцов.
Данные объединяются с помощью функций: Сумма, Произведение,Минимум, Максимум, Среднее и др.
В Microsoft Excel существует два метода консолидации данных:
- по расположению;
- по категориям.
Для консолидации по расположению исходные области должны иметь одинаковые заголовки столбцов и строк и располагаться идентично, т. е. в одинаковых блоках ячеек на разных листах.
Для консолидации по категориям исходные области могут иметь различные заголовки столбцов и строк и располагаться в произвольных блоках ячеек.
Чтобы консолидировать данные, надо;
1. Выделить левую верхнюю ячейку конечной области консолидируемых данных.
2. На вкладке Данные в группе Работа с данными выбрать команду Консолидация.
3. В открывшемся диалоговом окне в поле Функция выбрать функцию для обработки данных.
4. В поле Ссылка ввести ссылку на первую исходную область, причем, если выполняется консолидация по расположению, заголовки строк и столбцов не должны входить в исходную область, если же выполняется консолидация по категориям, то в исходную область должны входить либо заголовки строк, либо заголовки столбцов, либо то и другое.
5. Щелкнуть по кнопке Добавить.
6. Так же выделить остальные исходные области.
7. В случае консолидации по расположению режимы Использовать в качестве имен подписи верхней строки и значения верхнего столбца должны быть отключены, в случае консолидации по категориям включается либо один из этих режимов, либо оба в зависимости от того, какие заголовки входят в исходные области.
8. Для автоматического обновления данных конечной области при изменении данных исходных областей включить режим Создавать связи с исходными данными.При включении этого режимав конечной области создается структура рабочего листа и вставляются итоговые формулы.
Сводные таблицы
Сводная таблица ‑ это динамическая таблица итоговых данных, извлеченных или рассчитанных на основе информации, содержащейся в базе данных.
Сводные таблицы позволяют создавать динамические перекрестные таблицы, данные в которых обобщаются по нескольким измерениям.
С помощью сводной таблицы можно подсчитать промежуточные итоги с любым уровнем детализации.
На рис. 14,15 представлены примеры сводных таблиц.
Рис. 14
Основными элементами сводных таблиц являются:
- область Значения – ячейки сводной таблицы, в которых содержатся итоговые данные; над значениями полей, помещенных в эту область, выполняются сводные вычисления (обобщающие, консолидирующие, итоговые и т.д.); в приведенных примерах в эту область помещено поле Выплатить;
- 
область Названия столбцов – значения полей, помещенных в эту область, размещаются вдоль верхнего края сводной таблицы в качестве заголовков столбцов; в таблице на рис.14 это поле Должность, в таблице на рис.15 эта область не заполнена;
- область Названия строк – значения полей, помещенных в эту область, размещаются вдоль левого края сводной таблицы в качестве заголовков строк; в таблице на рис.14 в эту область помещено поле Фамилии, в таблице на рис.15 – поля Отдел и Фамилии;
- 
область Фильтр отчета – эта область как бы добавляет третье измерение в сводную таблицу; поле, помещенное в эту область создает раскрывающийся список своих значений; в таблице на рис.14 в эту область помещено поле Отдел и из списка выбрано значение бухгалтерия; в таблице на рис.15 эта область не заполнена;
- область Общие итоги – строка или столбец, в которых показаны общие итоги для всех ячеек строки или столбца;
- область Промежуточные итоги – строка или столбец, в которых отображаются промежуточные итоги для отдельных строк или столбцов; в таблице на рис.14 такой области нет; в таблице на рис.15 есть три строки промежуточных итогов (для каждого отдела).
Области Названия столбцов и Названия строк могут быть иерархическими, т.е. иметь вложенные подобласти.
Создание сводной таблицы
Перед созданием сводной таблицы рекомендуется преобразовать диапазон данных в таблицу Excel.
Чтобы создать сводную таблицу, надо:
1. Выделить любую ячейку диапазона данных, для которых надо создать сводную таблицу.
2. На вкладке Вставка в группе Таблицы в списке Сводная таблица выбрать команду Сводная таблица.
3. 
В открывшемся диалоговом окне включить режим Выбрать таблицу или диапазон и в поле Таблица или диапазон указать диапазон данных.
4. Выбрать вариант размещения сводной таблицы: На новый лист или На существующий лист. В случае размещения на существующем листе в поле Диапазон указать левую верхнюю ячейку диапазона сводной таблицы.
5. Щелкнуть по кнопке ОК.
На экран будет выведена панель Список полей сводной таблицы (рис. 16). В верхней части панели выведен список полей выбранного диапазона данных, в нижней части – области сводной таблицы.
6. Разместить поля диапазона данных в областях сводной таблицы. Это можно сделать одним из двух способов:
- перетащить мышью имя поля из списка в нужную область;
- в контекстном меню поля выбрать область его местоположения.
Группировка элементов сводной таблицы
В полях и строках сводной таблицы можно группировать элементы произвольным образом. Группировка элементов позволяет выделить набор данных, который сложно выделить другим способом, например, путем сортировки или фильтрации.
Excel предлагает два способа группировки: вручную и автоматически.
Чтобы сгруппировать элементы вручную, надо:
1. В сводной таблице выделить элементы, которые надо объединить в одну группу.
2. На вкладке Работа со сводными таблицами – Параметры в списке Группировать выбрать команду Группировать по выделенному.
Если элементами поля являются числовые значения или значения даты или времени, их можно сгруппировать автоматически:
1. В сводной таблице выделить любой элемент поля, элементы которого надо сгруппировать.
2. На вкладке Работа со сводными таблицами – Параметры в списке Группировать выбрать команду Группировать по полю.
3. В открывшемся диалоговом окне в поле начиная с ввести значение первого элемента группы (по умолчанию предлагается минимальное значение поля).
4. В поле по ввести значение последнего элемента группы (по умолчанию предлагается максимальное значение поля).
5. В поле с шагом ввести значение интервала группы.
Работа со сводной таблицей
Отображение или скрытие элементов.
Эта операция выполняется посредством фильтрации. Заголовки областей Названия столбцов и Названия строк снабжены кнопками, раскрывающими меню для сортировки и фильтрации. Кроме того, команды для фильтрации содержатся в контекстном меню элементов.
Развертывание или свертывание уровней детализации.
Если поле имеет подчиненные (вложенные) поля, рядом с ним появляется кнопка, позволяющая скрывать ( 
)или выводить на экран ( 
)вложенные поля (рис.15). Для этих же целей в контекстном меню поля имеется группа команд Развернуть/свернуть.
Отображение или скрытие подробных сведений о значениях.
Эта операция выполняется с помощью команды Показать детали контекстного меню ячейки. Другой способ – двойной щелчок мыши по ячейке. Подробные данные, на которых базируется значение этой ячейки, будут помещены на новый лист. Для скрытия подробных данных надо удалить этот лист.
Изменение функции подведения итогов.
По умолчанию в области Значения для числовых значений используется функция СУММ, для текстовых – функция СЧЁТ. Для изменения итоговой функции надо:
1. В области Значения выделить поле, для которого требуется изменить итоговую функцию.
2. На вкладке Работа со сводными таблицами – Параметры в списке Активное поле выбрать команду Параметры поля.
3. В открывшемся диалоговом окне выбрать нужную операцию.
Добавление, изменение и удаление полей.
Добавление или удаление полей из областей сводной таблицы выполняется с помощью панели Список полей сводной таблицы, которая всегда появляется на экране при выделении любой ячейки сводной таблицы (если щелкнуть мышью вне диапазона сводной таблицы, панель исчезнет с экрана).
Чтобы добавить новое поле, надо перетащить его из списка в нужную область.
Для изменения порядка полей (если область содержит несколько полей), надо расставить поля в нужном порядке при помощи мыши.
Чтобы удалить поле из области, надо вытащить его за пределы панели.
Можно временно удалить поле из сводной таблицы, сняв метку с флажка этого поля в списке полей верхней части панели; если снова установить флажок, имя этого поля появится в той же области, где было раньше.
Изменение формы сводной таблицы: компактной, табличной или структурной.
Для выбора формы надо на вкладке Работа со сводными таблицами – Конструктор в группе Макет в списке Макет отчета выбрать нужный вариант.
Изменение макетов столбцов, строк и итогов.
Для отключения или включения отображения заголовков полей, строк и столбцов предназначена кнопка Заголовки полей, расположенная на вкладке Работа со сводными таблицами – Параметры в группе Показать или скрыть.
Команды для отключения или включения отображения промежуточных и общих итогов, а также выбора варианта их размещения находятся на вкладке Работа со сводными таблицами – Конструктор в группе Макет.
Изменение формата.
Для изменения формата чисел надо выделить любую ячейку с числовым значением, на вкладке Работа со сводными таблицами – Параметры в списке Активное поле выбрать команду Параметры поля, в открывшемся диалоговом окне щелкнуть по кнопке Числовой формат и выбрать нужный формат.
Изменить общий стиль сводной таблицы можно с помощью стилей, приведенных на вкладке Работа со сводными таблицами – Конструктор в группе Стили сводной таблицы.
Практическая работа 10
Подведение промежуточных итогов
1. Скопировать данные из таблицы на листе Сведения о сотрудниках на новый лист. Для этого любым способом скопировать таблицу (без итоговой строки) в буфер обмена, перейти к новому листу, на вкладке Главная в группе Буфер обмена в списке Вставить выбрать команду Специальная вставка и в открывшемся диалоговом окне выбрать вариант Вставить значения и форматы чисел. Присвоить листу имя Итоги 1
2. Отсортировать таблицу на листе Итоги 1 по отделам, а внутри отделов – по должностям.
3. Используя инструмент подведения промежуточных итогов, разбить список на группы по отделам и подвести промежуточные и общие итоги по полю Фамилия, используя операцию Количество(подсчитать количество сотрудников в каждом отделе).
4. Скопировать лист Итоги 1 под именем Итоги 2.
5. В таблицу на листе Итоги 1 добавить промежуточные и общие итоги по полям Оклад, Надбавка, Премия, Пенсионный фонд, Налог, Выплатить (операция Сумма).
6. В таблице на листе Итоги 2 разбить отделы на подгруппы по должностям и подвести промежуточные итоги по полю Фамилия, используя операцию Количество(подсчитать количество сотрудников в каждом отделе, занимающих каждую должность).
Структурирование рабочих листов
7. На примере листов Итоги 1 и Итоги 2 освоить операции по управлению структурой листа: отображать разные уровни структуры, скрывать и выводить на экран подробные данные отдельных групп.
8. Открыть новый лист и составить таблицу со столбцами: Фамилия, Отдел, Оклад, Премия,Налог, Пенсионный фонд.
Ввести в таблицу 10-15 строк с данными:
Поля Фамилия, Отдел и Оклад заполнить произвольными значениями. В поле Отделиспользовать3-4 значения.
Премия составляет 15% от оклада, налог – 13% от суммы оклада и премии, пенсионный фонд – 1% от суммы оклада и премии.
Присвоить листу имя Структура 1.
9. Создать вертикальную и горизонтальную структуру листа. Для этого:
1) отсортировать данные по отделам;
2) для горизонтальной структуры вставить итоговые столбцы:
- после столбца Премия – столбец Начислено (сумма оклада и премии);
- после столбца Пенсионный фонд – столбец Удержано (сумма налога и отчислений в пенсионный фонд.);
- после столбца Удержано – столбец Выплатить (разность столбцов Начислено и Удержано.
3) для вертикальной структуры и вставить итоговые строки после каждого отдела, а также общую итоговую строку для всей таблицы: в столбцах с денежными величинами использовать функцию суммирования, в столбце Одел – функцию СЧЁТЗ (для подсчета количества сотрудников в каждом отделе и в целом); в первых ячейках итоговых строк ввести поясняющие надписи (например, «Всего по бухгалтерии);
4) для создания структуры воспользоваться командой Создание структуры.
10. На примере листа Структура 1 освоить операции по отображению разных уровней горизонтальной и вертикальной структуры.
11. Скопировать данные из таблицы на листе Структура 1 на новый лист, вставив только значения и форматы чисел. Присвоить листу имя Структура 2.
12. Создать вертикальную и горизонтальную структуру на листе Структура 2 (аналогичную структуре на листе Структура 1) способом группировки данных вручную.
Консолидация данных
13. Таблицу на листе Сдельная оплата скопировать на новый лист (включить в копируемый блок константы: норму выработки, расценку, процент повышения расценки, процент налога). Константы должны располагаться вне таблицы. Листу присвоить имя 1.
14. Скопировать данные из таблицы на листе 1 на новый лист, вставив только значения и форматы чисел. Присвоить листу имя Апрель.
15. В таблице на листе 1 изменить несколько значений в столбце Выработка и скопировать значения и форматы чисел таблицы на листе 1 на новый лист. Листу присвоить имя Май.
16. Внести еще несколько изменений в таблицу на листе 1 и скопировать значения и форматы чисел таблицы на листе 1 на новый лист. Листу присвоить имя Июнь.
17. Открыть новый лист, присвоить ему имя II квартал. На этом листе выполнить консолидацию по расположению данных из таблиц на листах Апрель, Май иИюнь. Для этого предварительно следует скопировать строку заголовков и столбец Фамилии И. О.на лист II квартал, а затем, выполняя консолидацию, выделять только численные данные таблиц без заголовков и без столбца Фамилия И. О.Включить режим Создавать связи с исходными данными.
18. Таблицу на листе Сдельная оплата (вместе с константами) скопировать на новый лист. Листу присвоить имя 2.
19. Скопировать значения и форматы чисел таблицы на листе 2 на новый лист (не включать в копируемый блок константы и итоговую строку). Листу присвоить имя Июль.
20. В таблицу на листе 2 добавить две новые строки и заполнить их данными, затем скопировать значения и форматы чисел таблицы на листе 2 на новый лист. Листу присвоить имя Август.
21. Из таблицы на листе 2 удалить три строки и скопировать значения и форматы чисел таблицы на листе 2 на новый лист. Листу присвоить имя Сентябрь.
22. Открыть новый лист, присвоить ему имя III квартал. На этом листе выполнить консолидацию по категориям данных из таблиц на листах Июль, Август иСентябрь, используя в качестве имен значения левого столбца. Для этого предварительно следует скопировать строку заголовков на лист III квартал, а затем, выполняя консолидацию, выделять данные таблиц без строки заголовков, но со столбцом Фамилия И. О. Включить режим Создавать связи с исходными данными.
23. Открыть новый лист и присвоить ему имя 3. На этот лист три раза скопировать значения и форматы столбцов Фамилии И. О. и Выработка (вместе с заголовками). В каждую из трех таблиц внести различные изменения: удалить несколько строк, добавить новые строки, изменить значения столбцаВыработка.
24. Открыть новый лист, присвоить ему имя IV квартал. На этом листе выполнить консолидацию по категориям данных из таблиц на листе 3, используя в качестве имен и значения левого столбца, и подписи верхней строки. Для этого, выполняя консолидацию, выделять данные таблиц со строкой заголовков и левым столбцом. Включить режим Создавать связи с исходными данными.
Сводные таблицы
25. Открыть таблицу на листе Сведения о сотрудниках.
26. Создать сводную таблицу, отображающую количество сотрудников в каждом отделе. Для этого в область Названия строк поместить поле Отдел, в область Значения ‑ поле Фамилии.
27. Создать сводную таблицу, которая показывает суммарные выплаты по каждому отделу с детализацией по сотрудникам. Для этого в область Названия строк поместить поля Отдел и Фамилии в область Значения ‑ поле Выплатить.
28. Создать сводную таблицу, которая показывает суммарные значения полей Оклад, Надбавка, Премия, Пенсионный фонд, Налог, Выплатить по каждому отделу с детализацией по сотрудникам.
29. Создать сводную таблицу, отображающую количество сотрудников, занимающих каждую должность в каждом отделе. Для этого в область Названия строк поместить поле Должность, в область Названия столбцов ‑ поле Отдел, в область Значения ‑ поле Фамилии.
30. Создать сводную таблицу, которая показывает суммарные выплаты в каждом отделе по каждо