Материал: Методические указания по выполнению лабораторных и самостоятельных работ. Морозов В.П., Свиридова Т.А

Если исходные данные изменяются, то можно обновить результирующие данные, активизируя данные, которые надо обновить, нажав на кнопку «!» на панели «мастера сводных таблиц» или выбрав соответствующий пункт в меню, вызываемого нажатием правой кнопки мыши.

Задача 12. Создать таблицу ПОСТАВКИ, создать сводные таблицы, представляющие данные в различных разрезах. Изменить структуру таблицы, используя «мастер сводных таблиц». Изменить исходные данные и сводную таблицу.

Замечание. Чтобы создаваемая таблица была представительной, необходимо чтобы в ней содержалось m₁m₂m₃ строк, где m₁ — число различных значений поля «поставщик», а m₂ и m₃ — аналогичные значения для полей «потребитель», «товар».

Задача 13. Создать таблицу регистрации температур воздуха, состоящую из полей: месяц, число месяца, город, температура. Получить сводную таблицу данных о температурах в масштабе месяцев.

6. Сортировка данных.

Многие методы обработки данных и поиска работают эффективнее с отсортированными данными. В Excel имеется простой способ сортировки строк таблицы. Чтобы им воспользоваться, достаточно указать столбец, который будет ключом сортировки, и выбрать пункты меню данные>сортировка. Предусмотрена возможность задания одновременно до трех ключей сортировки.

Задача 14. Отсортировать таблицу ПОСТАВКИ по столбцу «поставщик».

7. Группирование данных и создание итоговой строки.

В ряде случаев бывает необходимым разбить таблицу на группы, включив в каждую группу все строки с одним и тем же значением одного из столбцов. Например, все данные о банковских вкладах желательно разбить на группы, включив в каждую группу данные об определенном виде вклада (депозит, облигация, срочный вклад и т. д.). В Excel имеется возможность указать столбец группировки и подсчитать итоговые данные для каждого значения из этого столбца.

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

Задача 15. В таблице ПОСТАВКИ просуммировать «количество» по каждому значению поля «поставщик».

8. Фильтрация данных.

Фильтр может быть создан в любом столбце таблицы. Для его создания необходимо активизировать часть столбца для размещения списка выбора фильтра, причем размещать его надо прямо поверх текста столбца. Затем переместить таблицу с данными на новый лист и выбрать пункты меню данные>фильтр> автофильтр. Этим создается фильтр, который можно использовать для поиска строк таблицы по запросам. Повторное выполнение указанных действий уничтожает фильтр.

Задача 16. Для таблицы ПОСТАВКИ в столбце «поставщик» создать автофильтр и реализовать поиск по всевозможным условиям. Обратите внимание на возможность использования символов «?» и «*».

9. Расширенный фильтр.

При расширенной фильтрации для отбора строк таблицы используется вспомогательная таблица (фильтр), где указываются некоторые из столбцов и строк исходной таблицы. Например, можно создать таблицу:

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

Установите курсор мыши в пределах исходной таблицы и выберите пункты меню данные> фильтр> расширенный фильтр. Введите ссылки на фильтр в открывшемся окне «расширенный фильтр» и нажмите кнопку ОК.

Задача 17. Создайте расширенный фильтр для своей таблицы и испытайте его для различных запросов и режимов.

Указанный выше фильтр наиболее простой из возможных. Прежде всего, в фильтре каждое поле исходной (фильтруемой) таблицы можно представить дважды, а в строке под ней могут быть использованы числовые значения поля со знаками неравенства (<, >, <=, <=). Это дает возможность записать неравенство 150 < количество < 250 в виде таблицы:

С помощью таблицы

можно выделить записи о поставках мороженого в количестве, удовлетворяющем неравенству 150 < количество < 250. Это условие можно записать также с помощью следующего логического выражения (товар=’мороженое’)И(150<количество<250).

Можно создать таблицу-фильтр, содержащую несколько строк. Каждой строке соответствуют элементарные условия, объединяемые союзом «И», а условия соответствующие различным строкам объединяются союзом «ИЛИ». Например, таблица-фильтр

определяет все записи, удовлетворяющие логическому условию ((150<количество<250) И (товар=’мороженое’)) ИЛИ ((количество >200) И (товар=’вафли’)), т. е. те записи, которые относятся к сделкам по поставке мороженного в количестве от 150 до 250 единиц или поставкам вафель в количестве превосходящем 200 единиц.

Задача 18. Создать фильтры, указанные выше, и профильтровать записи исходной таблицы ПОСТАВКИ.

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

Для работы с таблицами (базами данных) в Excel предлагается несколько встроенных функций, с помощью которых можно подсчитать сумму элементов заданного столбца (поля) – БДСУММ, среднее значение – ДСРЗНАЧ, наибольшее и наименьшее значение – ДМАКС и ДМИН и другие. Способ обращения ко всем перечисленным функциям унифицирован. Он включает три аргумента: имя базы данных (таблицы), имя поля, по значениям которого происходят вычисления, и адрес таблицы-фильтра, задающей подмножество записей, по которым и происходит расчет. Например:

БДСУММ(база_данных, поле, критерий).

Присвоим таблице, содержащей данные о поставках, имя ПОСТАВКИ, ячейке, содержащей слово «количество», присвоим имя КОЛИЧЕСТВО, и создадим фильтр. Сформируем итоговую таблицу из двух столбцов. В левый столбец поместим слова «сумма», «среднее», «максимум» и «минимум», а в правый — соответствующие функции:

Задача 19. Для данных ПОСТАВКИ создать таблицу подсчета итогов, используя функции для работы с базами данных (таблицами). Таблицу итогов сформировать в следующем виде:

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

Задания для самостоятельной работы

1. На листе, содержащем таблицу базы данных, две первые строки займите критериями поиска. В первой строке введите несколько раз имя любого текстового поля таблицы, а во второй строке введите значения этого поля. Каждая пара: «имя поля — значение» будет образовывать критерий отбора записей. Назовите эти пары ячеек «критерий 1», «критерий 2» и т. д. Ниже, в столбце А, введите заголовки: сумма, среднее, максимум, минимум. В столбце В введите соответствующие функции работы с базой данных. Таблице присвойте имя «база данных». Ячейке, содержащей имя числового поля, присвойте имя «чисполе1». В качестве аргументов функций используйте переменные: база данных; чисполе1; критерий1. Также поступите с другими критериями. В результате выполнения этих функций должен быть получен аналитический отчет о хранимых в таблице данных.

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