Технология обработки баз данных с помощью электронных таблиц MS Excel

Цели работы:

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

Работа в лаборатории

Для успешного проведения практической работы студент должен выполнить следующие требования:

1. Получить допуск к практической работе при предъявлении преподавателю отчёта по текущей практической работе
2. Уметь ответить на контрольные вопросы по практической работе, то есть проявить свои знания по теоретической части курса
3. Продемонстрировать свои навыки и умения работать на персональном компьютере, выполнив задание по текущей практической работе
4. Соблюдать правила по технике безопасности

Содержание отчёта:

1. Название практической работы
2. Фамилия и номер группы
3. Цель практической работы
4. Результаты, полученные в ходе выполнения практической работы

Контрольные вопросы

1. Опишите, как происходит процесс сортировки в базе данных
2. Опишите, как происходит процесс фильтрации в базе данных
3. Опишите процесс создания сводных таблиц
4. Опишите, как происходит процесс построения сводной диаграммы
5. Опишите средства автоматической проверки, используемые в Excel

Ход выполнения практической работы

ЗАДАНИЕ №1. Создание базы данных

Алгоритм выполнения задания.

Запустите программу Excel (Пуск / Программы / Microsoft Excel) и откройте рабочую книгу Расходы, созданную ранее.

Создайте новый рабочий лист (Вставка / Лист), дважды щёлкните на его ярлычке и присвойте ему имя Сведения о поставках.

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

В ячейки A1-D1 введите заголовки полей базы данных, соответственно: Месяц, Поставщик, Товар, Объем.

Введите несколько десятков записей, имеющих описанную выше структуру. Реальные “объемы поставки” значения не имеют см. рис. 1

Общая сортировка базы данных.

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

В списке Сортировать по выберите пункт Месяц и режим по возрастанию.

В списке Затем по (вторичная сортировка) выберите пункт Поставщик и режим по возрастанию.

В списке В последнюю очередь по, выберите пункт Товар и режим по возрастанию. Щёлкните на кнопке OK.

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

Последовательная сортировка базы данных.

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

Выберите любую ячейку в столбце Товар и щёлкните на кнопке Сортировка по возрастанию на панели инструментов.

Выберите любую ячейку в столбце Поставщик и щёлкните на кнопке Сортировка по возрастанию на панели инструментов.

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

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

Активизируйте любую ячейку внутри таблицы, щёлкнув на ней мышкой

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

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

Чтобы отобрать наиболее крупные разовые поставки, щёлкните на раскрывающей стрелке у поля Объём и выберите в списке вариант Первые 10.

Выберите с помощью счётчика число 10 и далее пункт наибольших и вариант % от количества элементов. Щёлкните на кнопке OK. В результате будет отобрано 10% записей, содержащих наибольшие значения объёма поставок.

Чтобы отменить режим фильтрации записей, еще раз дайте команду Данные / Фильтр  / Автофильтр.

Покажите результат работы преподавателю.

ЗАДАНИЕ №2.  Получение итоговых сведений на основе существующей базы данных, построение сводной диаграммы

Алгоритм выполнения задания.

Запустите программу Excel (Пуск / Программы / Microsoft Excel) и откройте рабочую книгу Расходы, созданную ранее.

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

Сделайте текущей ячейку в пределах таблицы. Дайте команду Данные / Сводная таблица.

Убедитесь, что установлены переключатели в списке или базе данных Microsoft Excel и сводная таблица. Щёлкните на кнопке Далее.

Убедитесь, что диапазон базы данных выбран правильно. Щёлкните на кнопке Далее.

Убедитесь, что установлен переключатель Новый лист. Щёлкните на кнопке Макет.

Перетащите кнопки в соответствующие области макета сводной таблицы: кнопку Месяц – в область Страница, кнопку Поставщик – в область Столбец, кнопку Товар – в область Строка, кнопку Объём – в область Данные.

Кнопка в области Данные будет иметь вид Сумма по полю Объём. Нас это устраивает. Щёлкните на кнопке OK.

Щёлкните на кнопке Параметры. В поле Имя введите текст Сводная таблица поставок. Щёлкните на кнопке OK. Щёлкните на кнопке Готово.

Переименуйте рабочий лист со сводной таблицей, дважды щёлкнув на его ярлычке. Дайте ему имя Сводная таблица поставок.

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

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

Перетащите кнопку Месяц в область Столбец, а кнопку Товар – в область Страница. Сводная таблица автоматически перестроится в соответствии с новой структурой.

Дважды щёлкните на кнопке Сумма по полю объём. В открывшемся диалоговом окне Вычисление поля сводной таблицы щёлкните на кнопке Дополнительно. В раскрывающемся списке Дополнительные вычисления выберите пункт Доля от суммы по строке. Щёлкните на кнопке OK.

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

 Нажимая кнопку Отменить вернитесь к исходной сводной таблице

Построение сводной диаграммы

Сохраните рабочую книгу Расходы.

Щёлкните на кнопке Мастер диаграмм на панели инструментов Сводная таблица. Сводная диаграмма строится автоматически на новом рабочем листе. Результат представлен на рис. 3

Переименуйте этот рабочий лист, дав ему имя, Сводная диаграмма.

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

Выполните фильтрацию отображаемых данных.

Измените вид диаграммы.

Измените формат отображения элементов диаграммы.

Покажите результат работы преподавателю.

ЗАДАНИЕ №3. Настройка режима проверки вводимых данных

Алгоритм выполнения задания.

Запустите программу Excel (Пуск / Программы / Microsoft Excel) и откройте рабочую книгу Расходы, созданную ранее.

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

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

Для последующих экспериментов внесите произвольные искажения в базу данных: задайте в одной-двух записях неверное имя поставщика, в одной-двух записях используйте неверное наименование материала, в одной-двух записях укажите завышенный объем поставки (более 10 тонн).

За пределами базы данных, например в столбце F, в ячейках F2-F6 укажите имена поставщиков по одному в ячейке.

Аналогичным образом в ячейках G2-G6 укажите правильные наименования товаров.

Выделите все ячейки базы данных в столбце В (Поставщик), кроме заголовка столбца. Выполните команду Данные / Проверка.

В раскрывающемся списке Тип данных выберите вариант Список. Переключитесь на поле Источник и выделите мышкой диапазон F2:F6. Если диапазон выбран, ссылка автоматически берётся как абсолютная. После этого щелкните на кнопке OK.

Аналогичным способом выберите допустимые значения для поля Товар. В поле Источник необходимо указать диапазон G2:G6.

Выберите все ячейки в столбце D. Дайте команду Данные / Проверка. В поле Тип данных выберите вариант Действительное. В полях Минимум и Максимум укажите, соответственное, значение 0 и 10000. Щёлкните на кнопке OK.

Сделайте текущей любую ячейку в столбце B. Убедитесь, что ввести в неё произвольное значение, отсутствующие в списке, теперь невозможно. Щёлкните на раскрывающей кнопке, чтобы выбрать допустимое значение из списка.

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

Так как данные вводились в базу до включения проверки, они могут содержать ошибки. Дайте команду Сервис / Зависимости  / Панель зависимостей. Результат представлен на рис. 4

Щёлкните на кнопке Обвести неверные данные. Убедитесь, что ячейки с ошибками обнаружены и обведены красным цветом.

Исправьте ошибки и еще раз щёлкните на кнопке Обвести неверные данные. Убедитесь, что пометки исчезли.

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

Покажите результат работы преподавателю.

Список литературы

1. А. Ю. Гарнаев. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ – Петербург, 2006 г.
2. С.В. Симонович. Информатика для юристов и экономистов. - СПб.: Питер – , 2007 г.
3. Экономическая информатика. Учебник под ред. П.В. Конюховского и Д.Н. Колесова- СПб.: Питер – , 2005 г.

Цели работы:

• Научиться использовать электронную таблицу Excel в качестве базы данных.

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

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

• Узнать, исходя из каких данных, получается элемент сводной таблицы

• Научиться фильтровать данные сводной таблицы, динамически перестраивать сводную таблицу

• Научиться изменять способ отображения области данных

• Научиться создавать сводные диаграммы на основе сводных таблиц.

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

• Понять, что сводные диаграммы объединяют средства анализа и средства графического представления информации, имеющейся в базе данных.

• Научиться использовать средства автоматической проверки вводимых данных.

• Узнать, что при этом гарантируется соответствие введенных данных определенному условию.

• Познакомиться с новым способом ввода данных при наличии ограничений.

• Научиться находить ошибки в том случае, если условие на данные задано уже после того, как данные были введены.

Для успешного проведения практической работы студент должен выполнить следующие требования:

1. Получить допуск к практической работе при предъявлении преподавателю отчёта по текущей практической работе

2. Уметь ответить на контрольные вопросы по практической работе, то есть проявить свои знания по теоретической части курса

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

4. Соблюдать правила по технике безопасности

Содержание отчёта:

1. Название практической работы

2. Фамилия и номер группы

3. Цель практической работы

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

Контрольные вопросы

1. Опишите, как происходит процесс сортировки в базе данных

2. Опишите, как происходит процесс фильтрации в базе данных

3. Опишите процесс создания сводных таблиц

4. Опишите, как происходит процесс построения сводной диаграммы

5. Опишите средства автоматической проверки, используемые в Excel

Ход выполнения практической работы

ЗАДАНИЕ №1. Создание базы данных

Алгоритм выполнения задания.

• Запустите программу Excel (Пуск / Программы / Microsoft Excel) и откройте рабочую книгу Расходы, созданную ранее.

• Создайте новый рабочий лист (Вставка / Лист), дважды щёлкните на его ярлычке и присвойте ему имя Сведения о поставках.

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

• В ячейки A1-D1 введите заголовки полей базы данных, соответственно: Месяц, Поставщик, Товар, Объем.

• Введите несколько десятков записей, имеющих описанную выше структуру. Реальные “объемы поставки” значения не имеют см. рис. 1

Общая сортировка базы данных.

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

• В списке Сортировать по выберите пункт Месяц и режим по возрастанию.

• В списке Затем по (вторичная сортировка) выберите пункт Поставщик и режим по возрастанию.

• В списке В последнюю очередь по, выберите пункт Товар и режим по возрастанию. Щёлкните на кнопке OK.

• Убедитесь, что база данных отсортирована по указанным критериям.

Последовательная сортировка базы данных.

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

• Выберите любую ячейку в столбце Товар и щёлкните на кнопке Сортировка по возрастанию на панели инструментов.

• Выберите любую ячейку в столбце Поставщик и щёлкните на кнопке Сортировка по возрастанию на панели инструментов.

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

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

• Активизируйте любую ячейку внутри таблицы, щёлкнув на ней мышкой

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

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

• Чтобы отобрать наиболее крупные разовые поставки, щёлкните на раскрывающей стрелке у поля Объём и выберите в списке вариант Первые 10.

• Выберите с помощью счётчика число 10 и далее пункт наибольших и вариант % от количества элементов. Щёлкните на кнопке OK. В результате будет отобрано 10% записей, содержащих наибольшие значения объёма поставок.

• Чтобы отменить режим фильтрации записей, еще раз дайте команду Данные / Фильтр / Автофильтр.

Покажите результат работы преподавателю.

Результаты выполнения 1 части практической работы представлены на рис. 1

Рисунок 1. База данных

Алгоритм выполнения задания.

• Запустите программу Excel (Пуск / Программы / Microsoft Excel) и откройте рабочую книгу Расходы, созданную ранее.

• Откройте рабочий лист Сведения о поставках.

• Сделайте текущей ячейку в пределах таблицы. Дайте команду Данные / Сводная таблица.

• Убедитесь, что установлены переключатели в списке или базе данных Microsoft Excel и сводная таблица. Щёлкните на кнопке Далее.

• Убедитесь, что диапазон базы данных выбран правильно. Щёлкните на кнопке Далее.

• Убедитесь, что установлен переключатель Новый лист. Щёлкните на кнопке Макет.

• Перетащите кнопки в соответствующие области макета сводной таблицы: кнопку Месяц – в область Страница, кнопку Поставщик – в область Столбец, кнопку Товар – в область Строка, кнопку Объём – в область Данные.

• Кнопка в области Данные будет иметь вид Сумма по полю Объём. Нас это устраивает. Щёлкните на кнопке OK.

• Щёлкните на кнопке Параметры. В поле Имя введите текст Сводная таблица поставок. Щёлкните на кнопке OK. Щёлкните на кнопке Готово.

• Результат представлен на рис. 2

• Переименуйте рабочий лист со сводной таблицей, дважды щёлкнув на его ярлычке. Дайте ему имя Сводная таблица поставок.

Рисунок 2. Сводная таблица

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

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

• Перетащите кнопку Месяц в область Столбец, а кнопку Товар – в область Страница. Сводная таблица автоматически перестроится в соответствии с новой структурой.

• Дважды щёлкните на кнопке Сумма по полю объём. В открывшемся диалоговом окне Вычисление поля сводной таблицы щёлкните на кнопке Дополнительно. В раскрывающемся списке Дополнительные вычисления выберите пункт Доля от суммы по строке. Щёлкните на кнопке OK.

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

• Нажимая кнопку Отменить вернитесь к исходной сводной таблице

Построение сводной диаграммы

• Сохраните рабочую книгу Расходы.

• Щёлкните на кнопке Мастер диаграмм на панели инструментов Сводная таблица. Сводная диаграмма строится автоматически на новом рабочем листе. Результат представлен на рис. 3

• Переименуйте этот рабочий лист, дав ему имя, Сводная диаграмма.

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

• Выполните фильтрацию отображаемых данных.

• Измените вид диаграммы.

• Измените формат отображения элементов диаграммы.

Рисунок 3. Сводная диаграмма

Покажите результат работы преподавателю.

Алгоритм выполнения задания.

• Запустите программу Excel (Пуск / Программы / Microsoft Excel) и откройте рабочую книгу Расходы, созданную ранее.

• Откройте рабочий лист Сведения о поставках.

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

Для последующих экспериментов внесите произвольные искажения в базу данных: задайте в одной-двух записях неверное имя поставщика, в одной-двух записях используйте неверное наименование материала, в одной-двух записях укажите завышенный объем поставки (более 10 тонн).

• За пределами базы данных, например в столбце F, в ячейках F2-F6 укажите имена поставщиков по одному в ячейке.

• Аналогичным образом в ячейках G2-G6 укажите правильные наименования товаров.

• Выделите все ячейки базы данных в столбце В (Поставщик), кроме заголовка столбца. Выполните команду Данные / Проверка.

• В раскрывающемся списке Тип данных выберите вариант Список. Переключитесь на поле Источник и выделите мышкой диапазон F2:F6. Если диапазон выбран, ссылка автоматически берётся как абсолютная. После этого щелкните на кнопке OK.

• Аналогичным способом выберите допустимые значения для поля Товар. В поле Источник необходимо указать диапазон G2:G6.

• Выберите все ячейки в столбце D. Дайте команду Данные / Проверка. В поле Тип данных выберите вариант Действительное. В полях Минимум и Максимум укажите, соответственное, значение 0 и 10000. Щёлкните на кнопке OK.

• Сделайте текущей любую ячейку в столбце B. Убедитесь, что ввести в неё произвольное значение, отсутствующие в списке, теперь невозможно. Щёлкните на раскрывающей кнопке, чтобы выбрать допустимое значение из списка.

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

• Так как данные вводились в базу до включения проверки, они могут содержать ошибки. Дайте команду Сервис / Зависимости / Панель зависимостей. Результат представлен на рис. 4

Рисунок 4. Результат работы режима проверки

• Щёлкните на кнопке Обвести неверные данные. Убедитесь, что ячейки с ошибками обнаружены и обведены красным цветом.

• Исправьте ошибки и еще раз щёлкните на кнопке Обвести неверные данные. Убедитесь, что пометки исчезли.

• Сохраните текущую рабочую книгу

Покажите результат работы преподавателю.

1. А. Ю. Гарнаев. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ – Петербург, 2006 г.

2. С.В. Симонович. Информатика для юристов и экономистов. - СПб.: Питер – , 2007 г.

3. Экономическая информатика. Учебник под ред. П.В. Конюховского и Д.Н. Колесова- СПб.: Питер – , 2005 г.

5