Табличный процессор Microsoft Excel: создание базы данных

 

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

Формируемые компетенции

 

Для студентов, обучающихся по н аправлени ю подготовки 09.03.03 – «Прикладная информатика», профиль подготовки «Прикладная информатика в юриспруденции».

Способность использовать современные информационные технологии для решения учебных, исследовательских и профессиональных задач в области юриспруденции (ПВК-3):

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

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

- владеть навыками компьютерной обработки служебной документации.

Для студентов, обучающихся по специальности 40.05.01 «Правовое обеспечение национальной безопасности» и направлению подготовки «Юриспруденция».

Способность работать с различными источниками информации, информационными ресурсами и технологиями, применять основные методы, способы и средства получения, хранения, поиска, систематизации, обработки и передачи информации (ОК-16):

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

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

ЗАДАНИЕ

 

1. Загрузить процессор электронных таблиц Мiсrоsоft Excel.

2. Присвоить новому листу имя «лаб-раб-18-1».

3. Создать базу данных, пользуясь следующими данными.

Предполагается, что УМВД получает три вида отчетов по незаконному обороту оружия: 1) незаконное приобретение, передача, сбыт, хранение, пере возка, ношение (НППСХПН) оружия, его основных частей, боеприпасов, взрывчатых веществ и взрывных устройств; 2) хищение или вымогательство огнестрельного оружия, боеприпасов, взрывчатых веществ и взрывных устройств; 3) незаконное изготовление оружия – от двух краев страны: Приморский край и Краснодарский край. Каждый из отчитывающихся может поставлять любой вид отчетов. Отчеты выполняются одни раз в год, единица измерения – штуки (см. § 2.1 блока «Помощь»).

4. Выполнить сортировку базы данных: по виду преступлений, затем по году, в последнюю очередь – по краю (см. § 2.2 блока «Помощь»).

5. Создать сводную таблицу (см. § 2.1 блока «Помощь»).

6. Построить сводную диаграмму (см. § 2.1 блока "Помощь").

7. Сохранить работу.

 

ПОМОЩЬ

Алгоритм действий

1. Для создания базы данных в ячейки A1-D1 ввести заголовки полей базы данных, соответственно: Год, Край, Вид преступления, Количество.

2. Ввести записи для двух лет, например, за 2014 и 2015 годы, имеющих описанную выше структуру (рис. 33). Реальное количество преступлений значения не имеет (придумать самим).

3.   Для сортировки базы данных сделать текущей любую ячейку базы данных и вызвать из меню ДАННЫЕ команду СОРТИРОВКА. Убедитесь, что при этом выделяется вся база данных (кроме заголовков полей). Для этого в окошке строки МОИ ДАННЫЕ СОДЕРЖАТ ЗАГОЛОВКИ надо установить флажок.

4. В списке СОРТИРОВАТЬ ПО выбрать пункт ВИД ПРЕСТУПЛЕНИЙ и порядок от А до Я. После этого ДОБАВИТЬ УРОВЕНЬ и в списке ЗАТЕМ ПО (вторичная сортировка) – ГОД и порядок ПО ВОЗРАСТАНИЮ. Еще ДОБАВИТЬ УРОВЕНЬ и установить для сортировки ПО ВОЗРАСТАНИЮ пункт КРАЙ. Щелкнуть по кнопке [ ОК ].

 

Рис. 33. Пример заполнения базы данных

 

1. Для создания сводной таблицы и сводной диаграммы сделать текущей ячейку в пределах базы данных и вызвать команду СВОДНАЯ ДИАГРАММА с помощью кнопки-команды СВОДНАЯ ТАБЛИЦА вкладки ВСТАВКА.

5. В окне СОЗДАТЬ СВОДНУЮ ТАБЛИЦУ и сводную диаграмму переключатель должен быть установлен на строках ВЫБРАТЬ ТАБЛИЦУ ИЛИ ДИАПАЗОН и НА НОВЫЙ ЛИСТ. Проверить, чтоб диапазон базы данных был выбран правильно и щелкнуть по кнопке [ ОК ].

6. В открывшейся на новом листе панели СПИСОК ПОЛЕЙ СВОДНОЙ ТАБЛИЦЫ, перетащить из окна ВЫБЕРИТЕ ПОЛЯ ДЛЯ ДОБАВЛЕНИЯ В ОТЧЕТ поле ГОД в окно области ФИЛЬТР ОТЧЕТА, поле КРАЙ – в окно области ПОЛЯ ЛЕГЕНДЫ, кнопку ВИД ПРЕСТУПЛЕНИЯ – в окно области ПОЛЯ ОСЕЙ, поле КОЛИЧЕСТВО – окно области ∑ ЗНАЧЕНИЯ. Сводная таблица создастся автоматически. Вручную необходимо заменить в таблице заголовок НАЗВАНИЯ СТРОК на ВИД ПРЕСТУПЛЕНИЯ и заголовок НАЗВАНИЯ СТОЛБЦОВ на КРАЙ.

7. На диаграмме вызвать команду ИЗМЕНИТЬ ТИП ДИАГРАММЫ и выбрать ОБЪЁМНУЮ ГИСТОГРАММУ С ГРУППИРОВКОЙ.

8. Переименовать рабочий лист со сводной таблицей, дважды щелкнув на его корешке. Дать ему имя СТиД. Сводная таблица и диаграмма (рис. 34) показывают, сколько преступлений определенного вида совершено в конкретном крае в определенном году. Раскрывающаяся кнопка рядом с именами поля ГОД позволяет увидеть (фильтр) данные по определенному году.

Рис. 34.   Сводная таблица и сводная диаграмма

Методические указания

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

Чтобы содержимое рабочего листа рассматривалось как БД Excel, необходимо придерживаться строгих правил:

1) каждому полю записи соответствует один столбец рабочего листа;

2) столбцы БД должны идти подряд, без промежутков между ними;

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

4) заголовок поля должен занимать не более одной ячейки. Содержимое ячейки заголовка должно быть уникально в

5) пределах рабочего листа;

6) записи базы данных должны идти непосредственно ниже строки заголовков;

7) пустые строки не допускаются. Пустая строка рассматривается как признак окончания базы данных.

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

Сортировка базы данных. Для сортировки базы данных используется команда СОРТИРОВКА из меню ДАННЫЕ.

Диалоговое окно СОРТИРОВКА служит для выбора поля, по которому производится сортировка. Первое из выбранных полей определяет порядок первичной сортировки. Затем, добавив уровень, выбираются поля для следующей сортировки. По любому из полей порядок сортировки может выполняться в порядке ВОЗРАСТАНИЯ (от А до Я) или УБЫВАНИЯ (от Я до А) и настраиваемым списком. Кроме того сортироваться могут значения, цвет ячейки, цвет шрифта и значок ячейки.

Фильтрация базы данных. Так как БД может включать огромное число записей (в программе Excel количество строк достигает нескольких десятков тысяч), не всегда требуется отображать все эти записи. Выделение подмножества общего набора записей называется фильтрацией. Включение режима фильтрации осуществляется с вкладки ДАННЫЕ командой ФИЛЬТР. При этом для каждого поля БД автоматически создается набор стандартных фильтров, доступных через раскрывающиеся списки. Раскрывающие кнопки этих списков отображаются возле поля заголовка каждого столбца. По умолчанию используется вариант ВСЕ, указывающий, что записи базы данных должны отображаться без фильтрации. Фильтр предоставляет возможность отбора записей, имеющих в нужном поле конкретное значение.

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

Работа со сводной таблицей. Если необходимо создать только сводную таблицу следует открыть рабочий лист с первой (общей таблицей) вызвать команду СВОДНАЯ ТАБЛИЦА с вкладки ВСТАВКА. В ОКНЕ СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ переключатель должен быть установлен на строках ВЫБРАТЬ ТАБЛИЦУ ИЛИ ДИАПАЗОН и НА НОВЫЙ ЛИСТ. Проверить, чтоб диапазон базы данных был выбран правильно и щелкнуть по кнопке [ ОК ]. В открывшейся на новом листе панели СПИСОК ПОЛЕЙ СВОДНОЙ ТАБЛИЦЫ, перетащить из окна ВЫБЕРИТЕ ПОЛЯ ДЛЯ ДОБАВЛЕНИЯ В ОТЧЕТ поле ГОД в окно области ФИЛЬТР ОТЧЕТА, поле КРАЙ – в окно области НАЗВАНИЯ СТОЛБЦОВ, кнопку ВИД ПРЕСТУПЛЕНИЯ – в окно области НАЗВАНИЯ СТРОК, поле КОЛИЧЕСТВО – окно области ∑ ЗНАЧЕНИЯ. Сводная таблица создастся автоматически.

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

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

 

КОНТРОЛЬНЫЕ ВОПРОСЫ

 

1. Перечислите правила создания базы данных.

2. Что такое сводная таблица?

3. Какие виды сортировки данных существуют в БД, созданной в Excel?

4. Для чего применяется фильтрация данных?

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

 

Литература: [3, 5].

 

СПИСОК ЛИТЕРАТУРЫ

 

1. ABBYY FineReader 12.0.101 Combo Edition Lite [Электронный ресурс]. – Режим доступа: http://cwer.ws/node/366746/.

2. Finereader. Знакомство с программой ABBYY FineReader [Электронный ресурс]. – Режим доступа: http://finereader.helpmax.net/ru/знакомст-во-с-программой-abbyy-finereader/.

3. Бурцева Е.В. Прикладные программы [Электронный ресурс]: учебное пособие для студ. вузов напр. 230700.62, 030900.62 / Е. В. Бурцева, И. П. Рак, Э. В. Сысоев. - Электрон. дан. (85,7 Мб). – Тамбов: ФГБОУ ВПО "ТГТУ", 2014.

4. Бурцева Е.В. Прикладные программы для решения юридических задач: лабораторные работы для студ. 2 курса спец. 030501 и 351400 всех форм обучения. Ч. 2 / Е. В. Бурцева, А. В. Селезнев, Э. В. Сысоев; Тамб. гос. техн. ун-т. – Тамбов: Изд-во ТГТУ, 2007. – 32 с.

5. Добро пожаловать в справочное руководство LibreOffice! [Электронный ресурс]. – Режим доступа: https://help.libreoffice.org/ Main_Page/ru [Обновление 2013-04-09].

6. Прикладные программы: лабораторные работы. Ч. I. / авт.-сост. Е.В. Бурцева. – Тамбов: Изд-во Тамб. гос. техн. ун-та, 2006. – 32 с.

7. Прокуратура РМ: О состоянии преступности в Мордовии Прокуратурой республики проведен анализ состояния преступности на территори... [Электронный ресурс]. – Режим доступа: http://www.prokrm.ru/content/view/ 7130/.

8. Ракитина, Е.А. Логика в информатике: метод. пособие / Е.А. Ракитина, В.Ю. Лыскова. – М.: Лаборатория Базовых Знаний, 2001. – 160 с.

9. Состояние преступности - январь-октябрь 2015 года 13 Ноября 2015 12:59 [Электронный ресурс]. – Режим доступа: https://mvd.ru/folder/101762/ item/6765514/.