Учебно-методическое пособие для студентов вузов Павлодар (стр. 40 из 76)

- то же самое выполните для листов Склад2 и Склад3;

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

Освоение технологии анализа данных при помощи сводной таблицы

20 Команда Данные – Сводная таблица вызывает Мастера сводных таблиц для построения сводов, то есть итогов определенных видов на основании данных списков, других сводных таблиц. Предположим, нам необходимо узнать, на какую сумму закупила товар какая-нибудь организация.

21 Добавим в рабочую книгу еще один лист и назовем его Сводная таблица. Скопируйте на него исходную таблицу с листа 1 (диапазон ячеек A1:H18).

22 Выполните команду Данные – Сводная таблица. Первые два шага работы с мастером выполните самостоятельно.

23 На третьем шаге перетащите значки с названиями столбцов следующим образом:

- для информации, которую мы хотим разместить в строках, то есть поле Организация, необходимо захватить соответствующий значок мышью и перетащить его в область Строка. Перенести значок Сумма в область Данные, а значок Товар в область столбцов.

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

25 Сохраните рабочую книгу.

Лабораторная работа №21

Работа с базами данных в Microsoft Excel. Сортировка и фильтрация

Цель: привить навыки использования электронных таблиц в качестве простейшего средства ввода, хранения, обработки информации.

Краткие теоретические сведения

База данных - это средство, использующееся для хранения, организации и поиска информации. В современных «компьютерных» базах данных информация обычно содержится во многих таблицах, определенным образом связанных между собой. Таблицы в таких базах данных состоят из записей(строк) и полей(столбцов). Простые базы данных, состоящие из одной таблицы, можно создавать средствами Excel.

Списком называется набор строк таблицы, содержащий связанные данные, например, перечень телефонов клиентов при этом:

- столбцы списков становятся полями базы данных;

- заголовки столбцов становятся именами полей базы данных;

- каждая строка списка преобразуется в запись данных.

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

- на листе не следует помещать более одного списка;

- между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец;

- в самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка;

- заголовки столбцов должны находиться в первой строке списка;

- список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные;

- перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку;

- не следует помещать пустую строку между заголовками и первой строкой данных.

Сортировка. Для упорядочения ячеек по значениям (без учета формата) предусмотрен определенный порядок сортировки.

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

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

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

Для сортировки списков в одном столбце следует использовать кнопки По возрастанию

и По убыванию .

Фильтрация. Фильтрация в Excel осуществляется при помощи средства, называемого Автофильтром.

Для применения автофильтра необходимо:

- щелкнуть любую ячейку внутри списка;

- выбрать команду Данные – Фильтр - Автофильтр. В ячейках с именами полей списка должны появиться кнопки со стрелками вниз;

- чтобы отфильтровать строки, содержащие определенное значение, надо нажать кнопку со стрелкой в столбце, в котором содержатся искомые данные;

- выбрать значение в списке;

- повторить шаги 3 и 4, чтобы ввести дополнительные ограничения для значений в других столбцах.

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

Если необходимо отобрать строки, удовлетворяющие одновременно двум условиям отбора, то надо щелкнуть флажок И, а затем сформировать второе условие в нижней части окна.

Если необходимо отобрать строки, удовлетворяющие одному из двух условий отбора, то надо щелкнуть флажок ИЛИ, а затем сформировать второе условие в нижней части окна.

Чтобы удалить фильтр для одного столбца служит команда Все в раскрывающемся списке отфильтрованного столбца. Для отображения всей базы данных в меню Данные нужно выбрать команду Отобразить все.

Чтобы удалить автофильтр из списка, выберите пункт Фильтр в меню Данные, а затем — команду Автофильтр.

Расширенный фильтр. При необходимости отобрать данные из БД по рем и более критериям можно использовать возможности расширенного фильтра.

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

- скопируйте из списка заголовки фильтруемых столбцов;

- вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора;

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

- укажите ячейку в списке;

- выберите команду Данные – Фильтр - Расширенный фильтр;

- чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение Фильтровать список на месте.

Чтобы скопировать отфильтрованные строки в другую область листа, установите переключатель Обработка в положение Скопировать результаты в другое место, и укажите верхнюю левую ячейку области вставки.

7 Введите в поле Диапазон условий ссылку на диапазон условий отбора, включающий заголовки столбцов.

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

Примеры

1 На ячейки одного столбца накладываются три или более условий отбора:

Таблица 45 – Пример фильтрации по трем и более условиям

ПРОДАВЕЦ

Белов

Батурин

Сушкин

Чтобы задать для отдельного столбца три или более условий отбора, введите условия в ячейки, расположенные в смежных строках. В приведенном примере будут отобраны строки, содержащие либо «Белов», либо «Батурин» либо «Сушкин» в столбце «Продавец».

2 Условие отбора накладывается на ячейки двух или более столбцов

Чтобы наложить условия отбора не несколько столбцов одновременно, введите условия в ячейки, расположенные в одной строке диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие «Продукты» в столбце «Товар», «Белов» в столбце «Продавец», и имеющие сумму реализации больше 1000.

Таблица 46 – Результат отбора

Товар	Продавец	Продажи
Продукты	Белов	>1000

3 Чтобы выбрать строки, удовлетворяющие одному из нескольких условий, наложенных на разные столбцы, введите условия в ячейки, расположенные в разных строках диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие либо «Продукты» в столбце «Товар», либо «Белов» в столбце «Продавец», либо имеющие сумму реализации больше 1000.

Таблица 47 –Пример задания расширенного фильтра

Товар	Продавец	Продажи
Продукты
Белов
>1000

4 Чтобы наложить сложное условие отбора, введите его составные части в отдельные строки диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие «Белов» в столбце «Продавец» и имеющие сумму реализации больше 3000 или строки, содержащие «Батурин» в столбце «Продавец» и имеющие сумму реализации больше 1500.

Таблица 48 – Пример задания фильтра

ПРОДАВЕЦ	ПРОДАЖИ
БЕЛОВ	>3000
БАТУРИН	>1500

5 В условии отбора используется возвращаемое формулой значение: