ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 21.07.2024
Просмотров: 92
Скачиваний: 0
ЛАБОРАТОРНАЯ РАБОТА № 10
Работа с таблицей excel как с базой данных: автофильтр, расширенный фильтр, сортировка данных, создание сводных таблиц
Цель работы: Изучить:
Автофильтрация.
Расширенный фильтр.
Сортировка данных.
Создание сводной таблицы.
Задание к работе:
К работе допущен:
Работу выполнил:
Работу защитил:
Теоретическая часть
База данных - это совокупность хранимых в памяти ЭВМ и специальным образом организованных взаимосвязанных данных, отображающих состояние предметной области. Примерами простейших баз данных могут служить телефонный справочник, расписание движения поездов и т. п.
В Microsoft Excel в качестве базы данных можно использовать список.
Список - таблица, данные в строках которой имеют однородную структуру, т.е. в каждом столбце списка содержатся данные одного типа (число, текст, дата и т.д.)
Размер и расположение списка
На листе не следует помещать более одного списка.
Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец.
В самом списке не должно быть пустых строк и столбцов.
Важные данные, не относящиеся к списку, не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.
Заголовки столбцов
Заголовки столбцов должны находиться в первой строке списка.
Шрифт, выравнивание, формат, шаблон, граница и формат прописных и строчных букв, присвоенные заголовкам столбцов списка, должны отличаться от формата, присвоенного строкам данных.
Для отделения заголовков от расположенных ниже данных следует использовать границы ячеек, а не пустые строки или прерывистые линии.
Содержание строк и столбцов
Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.
Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.
Не следует помещать пустую строку между заголовками и первой строкой данных.
Основным назначением баз данных является быстрый поиск содержащейся в них информации.
Для работы с базами данных Excel содержит следующий набор специальных средств:
Организация ввода, просмотра и поиска данных с помощью форм;
Сортировка данных;
Фильтрация данных;
Подведение итогов и создание сводных таблиц.
Организация ввода, просмотра и поиска данных с помощью форм
Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы.
Сортировка списков
Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки.
По умолчанию списки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки.
Фильтрация данных
Фильтрация позволяет выбирать данные, удовлетворяющие определенным критериям. Существует три способа фильтрации: поиск с помощью формы (мы его уже рассмотрели), автофильтр и расширенный фильтр.
Автофильтр
Поместите курсор в область, содержащую базу данных или выделите её.
Затем выполнить команды меню Данные/ Фильтр
На именах полей появятся кнопки с изображением стрелок вниз, которыми можно задать критерии фильтрации.
Пункт Условие позволяет применить отличные от равенства операторы сравнения .
Для одного поля могут быть заданы два условия одновременно, связанные логическими и или или (рис. 27). Допускается использование специальных символов подстановки:
? - Любой символ в той же позиции, что и знак вопроса.
Например: д?м задает поиск "дым" и "дом"
* - Любую последовательность символов в той же позиции, что и звездочка.
Например: *ино задает поиск "Люблино" и "Выхино"
Строчные и прописные буквы при фильтрации данных не различаются.
Если данные уже отфильтрованы по одному из столбцов, то при использовании фильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке
В результате получим таблицу(в зависимости от исходных данных) (рис. 2.4.3).
В условии отбора используется возвращаемое формулой значение
Возвращаемое формулой значение можно использовать не только в условиях на равенство (как в предыдущем примере), но и с другими операторами сравнения.
В этом случае в качестве заголовка условия нужно ввести строку, которая не является заголовком столбца списка (например, 'критерий') или оставить заголовок условия незаполненным.
Используемая в условии формула должна ссылаться либо на заголовок столбца (например, 'цена(у.е.)' ), либо на соответствующее поле в первой записи(D3).
Аналогичный результат получится при задании условия в виде:
Подведение итогов
Для подведения итогов по всей базе данных, а также по отдельным группам данных можно использовать функцию Excel ПРОМЕЖУТОЧНЫЕ ИТОГИ (рис. 2.4.6), а также создание сводных таблиц.
и
Порядок работы:
Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог. (Например, чтобы просуммировать стоимость материала, отпущенного со склада одному человеку, нужно отсортировать список по полю [получил].)
Укажите ячейку в этом списке.
Выберите команду меню Данные/ Промежуточные итоги.
Выберите столбец При каждом изменении в, содержащий группы, по которым необходимо подвести итоги. Это должен быть тот столбец, по которому проводилась сортировка списка. (Например, получил.)
Выберите функцию, необходимую для подведения итогов, из списка Операция. (Например, сумма.)
Выберите столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по. (Например, стоимость.)
Детализацию данных можно регулировать с помощью кнопок структуры слева от таблицы.
Любая строка итогов может быть удалена из списка, это не повлияет на другие данные. Чтобы удалить все строки с итогами нужно выбрать меню Данные/ Итоги/ Убрать все.
Чтобы подвести итоги по вложенным группам (например, для каждого получателя подсчитать сумму количества и стоимости каждого материала), нужно сначала провести сортировку по всем необходимым столбцам (получил и материал), а затем два раза подвести итоги (сначала При каждом изменении в: получил, затем При каждом изменении в: материал)
Сводные таблицы
Сводная таблица служит для обобщения информации из базы данных, и представления её в удобном для анализа виде
Порядок создания сводной таблицы:
Для этого выполнить команду Сводная таблица, указать диапазон, содержащий исходные данные (выделить всю таблицу с данными), двойной щелчок по кнопке в области Данные приведет к отображению диалогового окна Вычисление поля сводной таблицы, которое позволяет установить операцию, на основе которой будут подводиться итоги
Выбрать поля, которые будут образовывать строки сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Строка.
Выбрать поля, которые будут образовывать столбцы сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Столбец.
Если требуется создать сводную таблицу в виде подшивки страниц, то перетащить кнопку, соответствующую полю, по которому будут строиться страницы, в область Страница.
Для перерасчета сводной таблицы, если изменились данные, на основе которых строилась эта сводная таблица: выделить ее, команда Обновить данные.
Для группировки даты по месяцам нужно выбрать команду Группировать из меню Данные или панели инструментов.