Microsoft Excel 97. Работа № 7 Табличные базы данных (списки). Сортировка данных, фильтрация данных, подведение промежуточных итогов, разделение и закрепление областей.

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

Списки, или табличные базы данных – это такие таблицы, в которых все строки, за исключением заголовков, имеют одинаковую структуру и типы данных.

Для работы со списками в Eхcel имеются дополнительные возможности, которые перечислены в заголовке лабораторной работы. В программе Excel они реализованы в меню Данные.

Рассмотрим на примере рабочей книги (файла) Продажа быттехникитакую таблицу на листеТовары все перечисленные цели работы.

Запустите программу MicrosoftEхcel, затем откройте заготовку файла:С \ Мои документы \ Шаблон для Eхcel \ Продажа быттехники

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

Упражнение 1. Сортировка данных в электронных таблицах.

Проанализируем содержимое ячеек таблицы.

В столбцах A, B, C, DиEвнесены наименования товаров, их модели, присвоены коды по порядку, цена поступления и количество каждого товара, в столбцахF, G и Hвведены формулыдля расчета стоимости приобретенного товара, определения цены реализации с 20% наценкой и вычисления прибыли.

Теперь рассмотрим, как можно выполнить сортировку данных в таблице Товары по полю Наименование товара.

Поместите курсор на первую ячейку в столбце Наименование товара.Затем на панели инструментовСтандартнаянажмите кнопку Сортировка по возрастанию (рис. 1).

Рис. 1. Вид таблицы Товарыперед началом сортировки.

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

Рис. 2. Вид таблицы после сортировки по столбцу Наименование товара.

Действуя по аналогии, отсортируйте данные таблицы по возрастанию значений в столбцах Количество, Цена поступления, Планируемая прибыль.

Из полученного результата видно, что первую позицию занял товар с нулевой прибылью (рис. 3).

Рис. 3. Вид списка товаров после сортировки по столбцу Планируемая прибыль.

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

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

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

Поместите курсор на любую ячейку столбца А, затем в менюВставкавыберите пунктСтолбцы(рис. 4). В таблице должен появиться новый пустой столбец (столбецA). При этом вся таблица сместилась вправо.

Обратите внимание, что формулы, по которым, выполнялись расчеты в трех последних столбцах, по-прежнему работают правильно, несмотря на то, что имена столбцов изменились (с C, D и E на D, E и F). Это произошло благодаря относительным ссылкам на ячейки в формулах.

Рис. 4

Теперь, нужно выделить весь столбец D и вырезать его в буфер обмена, вызвав правой кнопкой мыши контекстное меню (рис. 5).

Рис. 5

Затем вырезанный столбец нужно вставить на место столбца A (рис. 6).

Для этого выделите весь столбец А, правой кнопкой мыши вызовите контекстное меню и дайте командуВставить.

Рис. 6

Теперь первым столбцом в таблице стал столбец Код, а столбецDостался пустой.Удалите столбец D. Для этого выделите весь столбецDи в контекстном меню выберите пунктУдалить(рис. 7).

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

Рис. 7

Теперь требуется отсортировать данные в таблице по Наименованию товара, не сортируя при этом столбец с кодами.

Выделите все ячейки с данными, исключая Код (диапазон ячеек В2:Н24), затем вызовите диалоговое окно Сортировка в меню Данные (рис. 8).

А) первоначальный вид окна Б) окончательный вид окна Рис. 8. Диалоговое окно Сортировка диапазона

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

Три области с полями для выбора критерия сортировки, причем каждая область имеет два направления сортировки – по возрастаниюипо убыванию, также имеется область для выбора признака, по которому будут идентифицированы выделенные в таблице ячейки:

а) по подписям первой строки выделенного диапазона ячеек (рис. 8А);

б) по обозначениям столбцов листа(рис. 8Б).

В нашем случае удобнее идентифицировать поля по обозначениям столбцов листа(по названиям столбцов рабочей книгиExcel) и выбрать первый критерий сортировкиСтолбец В, а направление сортировки –по возрастанию. Установите все параметры для сортировки как показано на рис. 8Б. Нажмите кнопкуOK.

В результате этой операции все товары должны "выстроиться" по алфавиту, а Коды, поскольку они не попали в выделенный диапазон ячеек, так и остались располагаться по возрастанию. Вспомните, что происходило, когда просто сортировали товары по алфавиту!

Упражнение 2. Использование фильтров в электронных таблицах.

В Excelфильтры позволяют показать в таблице только нужные данные, а ненужные скрыть. Самый простой способ фильтрации списков – использование встроенного в программуExcelАвтофильтра.

Отобразим в таблице ТоварытолькоХолодильники. Выделите весь столбец B с наименованиями товаров, и в менюДанныевыберите пунктФильтр–Автофильтр (рис. 9).

Рис. 9

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

Рис. 10. Список значений для определения критерия фильтрации.

Если мы выберем в списке значение Холодильник, то в результате получим следующую таблицу (рис. 11).

Обратите внимание, что кнопка Автофильтраизменила цвет на синий.Это означает, что фильтр включен.

Рис. 11. Вид таблицы после назначения фильтра.

Для закрепления навыка по использованию фильтра назначьте фильтр на другие наименования товаров. Отменить фильтр можно, выбрав в списке фильтра значение (Все).

Фильтрация данных может быть и более сложной.

Выделите все столбцы таблицы и назначьте для них Автофильтр. Теперь каждый столбец с данными может быть отфильтрован самостоятельно.

В фильтре Наименование товара выберите значениеГазовая плита, а в фильтреЦена поступления– значение(Условие...).

Затем в открывшемся окне Пользовательский автофильтр, настройте тип условия для фильтрации цены (например, меньше или равно) и назначьте предельное значение для этого условия – 3 000 (рис. 12).

Рис. 12. Вид окна Пользовательский автофильтр.

В полученной таблице будут работать два фильтра. Один отображает только газовые плиты, другой из этих газовых плит покажет только те, цена у которых меньше или равна выбранному значению 3 000р.(рис. 13).

Рис. 13. Вид таблицы с двумя включенными фильтрами.

Задания для самостоятельного выполнения.

Задание 1. Проведите сложную фильтрацию по каким-либо полям таблицы Товары.

Задание 2. Для фильтрации поляКоличествоназначьте два условия в Пользовательском автофильтре – первый тип условия –меньшес предельным значением30и второй тип условия –большес предельным значением10.

Задание 3. Отключите режим фильтрации для выполнения следующего Упражнения. Снятие режимаАвтофильтрс таблицы выполняется так же, как и его назначение, повторным выбором в менюДанныепунктаФильтр–Автофильтр.

Упражнение 3. Подведение промежуточных итогов.

Основным условием списковой таблицы является одинаковая структура строк и типов данных в них для всех ячеек таблицы.

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

Допустим, необходимо просуммировать количество товаров одного наименования и всего товара в целом. А заодно просуммировать стоимость этих товаров. В меню Данные выберем пунктИтоги...(рис. 14).

Рис. 14.

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

В поле "При каждом изменении в:"–выберитеиз спискаНаименование товара.

В поле "Операция:" – выберите из списка Сумма.

В поле "Добавить итоги по:" – поставьте флажки напротив Количество, Стоимость, Планируемаяприбыль.

Затем проверьте, стоят ли флажки в нижней части окна у параметров отображения итогов– Заменить текущие итоги, Итоги под данными.

Рис. 15. Диалоговое окно Промежуточные итоги.

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

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

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

На рис. 16 показан лист с развернутой структурой групп товаров. А на рис. 17 показан результат скрытия всех групп, кроме Группы Пылесосы.

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

Рис. 16. Вид таблицы с развернутой структурой групп товаров и Промежуточными итогами по ним.

Рис. 17. Вид таблицы со скрытой структурой всех групп товаров, кроме Группы Пылесосы

Как видно из рисунка 17 символ минус после скрытия группы меняется на плюс. Нажав на кнопку с плюсом, можно вновь открыть группу.

Примечание.

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

Поупражняйтесь в этом на своей таблице.

Для отмены режима Промежуточные итогиследует снова открыть окно настройки (Данные–Итоги...и нажать кнопку Убрать все).

Таблица вновь примет вид обычного списка.

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

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

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

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

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

Рис. 18. Пример таблицы при ручном структурировании.

Задания для самостоятельного выполнения.

Задание 1. Произведитегруппировку различных групп товаров таблицы Товары.

Задание 2. С помощью кнопокУровни структурыскройте и отобразите сгруппированные данные.

Задание 3. Отмените созданные структурные группы

(Данные–Группа и структура–Удалить структуру).

Упражнение 4. Разделение и закрепление областей