Работа со списками и базами данных

Работа со списками

Необходимые теоретические сведения

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

Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на небольших предприятиях учет данных о сотрудниках ведется в электронных таблицах Microsoft Excel.

Понятие списка

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

1. Создайте новую книгу. Для этого выполните команду Файл / Создать / Новая книга и нажмите кнопку Создать.

2. Курсор мыши поместите в рабочую область программы. Наберите в ячейках А1-Е1 имена полей: фамилия, имя, отчество, адрес, телефон.

3. Выделите ячейки А1-Е1 и установите шрифт Times New Roman 14 пунктов, а затем выровняйте по центру.

4. Заполните все остальные ячейки текстом шрифта Arial 10 пунктов.

5. Установите внутренние и внешние границы для таблицы. Для этого выделите таблицу и выберите команду Главная / (ячейки) Формат / Формат ячеек. Затем выберите вкладку Граница и щелкните на кнопки: Внешние и Внутренние.

6. Отрегулируйте ширину столбцов по содержимому. Для этого выделите таблицу и выполните команду Главная / (ячейки) Формат /Автоподбор ширины столбца.

Таким образом, строки списка становятся записями, а заголовки столбцов – именами полей списка. Имена полей должны располагаться в верхней части списка и находиться в отдельных ячейках. Нужно помнить, что пустая строка указывает на окончание списка, поэтому не следует оставлять пустых строк между заголовками столбцов.

 

Сортировка

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

 

Рис.1. Адресная книга

 

1. Для того чтобы программа Microsoft Excel 2010 обнаружила список нужно щелкнуть по любой ячейке, относящейся к списку.

2. Выполните команду Данные / (сортировка и фильтр) Сортировка. После чего программа сама выделит таблицу.

3. В окне Сортировка выберите следующие значения: сортировать по - Фамилия, затем Сортировка - значения, в последнюю очередь Порядок - от А до Я. Нажмите кнопку ОК.

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

 

Рис. 2. Учетная книга продаж телефонов

 

Получится документ следующего вида:

Создадим документ следующего вида:

 

 

 

9. Сохраните полученный документ на диске С: в папке Учебные документы.

 

Использование фильтров

 

Общие сведения

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

Чтобы установить фильтр, нужно нажать на любую ячейку списка с данными и выполнить команду Данные / (Сортировка и Фильтр) Фильтр.

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

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

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

Чтобы завершить работу с автофильтром необходимо еще раз выполнить команду Данные/(Сортировка и Фильтр)Фильтр.

 

Фильтрация

Создадим таблицу следующего вида для изучения приемов фильтрации.

ЗАДАНИЕ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

1. Задание 3.1. Пользуясь вышеприведенным описанием создайте документы аналогичные приведенным на рисунках 1 и 2. Количество строк в документах для каждого варианта свой -определяется по формуле:

N_{кол. строк} = N_{варианта} + 10,

где N_вар. – номер вашего варианта.

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

ВНИМАНИЕ! Вы должны придумать свой оригинальный пример и подготовить текст, описывающий технологию работы с таблицами в соответствии с Вашим примером.

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

 

 

Используя Фильтр оставьте только иногородних студентов.

3. Создайте список с формулами и диаграммой следующего вида.

 

4. Ответить на нижеследующие вопросы в письменном виде с демонстрацией конкретных примеров

4.1. Что такое пользовательский порядок сортировки?

4.2. Как применить Автофильтр к списку?

4.3. Как отменить результаты фильтрации?

4.4. Сколько условий можно наложить на один столбец с помощью команды Автофильтр?

4.5. Каков порядок применения Расширенного фильтра?

4.6. Как отменить результат применения Расширенного фильтра?

4.7. Как задать несколько условий для одного столбца списка с помощью расширенного фильтра?

4.8. Как задать одно условие для нескольких столбцов с помощью расширенного фильтра?

4.9. Как задать разные условия для разных столбцов с помощью расширенного фильтра?

5.Работа со списками

Для демонстрации возможностей работы со списками в электронной таблице MS Excel необходимо подготовить примеры и разместить их на листе рабочей книги. Например, “Автокаталог”.

На рисунках 3-9 показаны фрагменты таблиц списка (база данных): на тему «Автокаталог», размещенных на листе "Задача 3" рабочей книги Excel.

Рис.3 – Таблица с исходным списком

Рис.4 – Таблица после сортировки

 

Рис.5 – Использование фильтра

 

Рис.6 – Использование расширенного фильтра

 

Рис. 7 – Подведение итогов и создание структуры данных

 

Рис. 8 – Сводная таблица

 

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

Рис.9 – Диаграмма

 

Фрагмент описания технологии работы со списками в MS Excel приведен ниже. Предметная область списка (база данных): “Автокаталог”.

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

В строке 4 листа рабочей книги Excel (рис. 8) введены заголовки полей списка (базы данных): «Номер»; «Марка»; «Цвет»; «Пробег»; «Год выпуска»; «Объем двигателя»; «Стоимость, тыс. руб.». Заполним таблицу данными как показано на рисунке 10. Всего заполнено 20 строк списка.

Рис. 10 – Список (база данных) «Автокаталог»

 

Сортировка.

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

Если был задан возрастающий порядок сортировки, все числа столбца сортируются в порядке от наименьшего отрицательного числа к наибольшему положительному; значения даты и времени располагаются в хронологическом порядке от самого раннего к самому позднему; текстовые данные сортируются по алфавиту, при этом в начале списка окажутся заданные в качестве текста числовые значения; список логических значений будет начинаться со значения ЛОЖЬ и заканчиваться значением ИСТИНА; а значения ошибок будут отсортированы в том порядке, в котором они были обнаружены. При возрастающем порядке сортировки пустые ячейки будут сдвинуты в конец списка.

При убывающем порядке сортировки все числа столбца сортируются от наибольшего положительного к наименьшему отрицательному; значения даты и времени располагаются в порядке от самого позднего к самому раннему; текстовые данные сортируются в обратном алфавитном порядке; список логических значений будет начинаться со значения ИСТИНА и заканчиваться значением ЛОЖЬ. Пустые ячейки будут сдвинуты в конец списка.

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

Отсортируем список “Автокаталог” по двум уровням - сначала по столбцу «Год выпуска», затем по столбцу «Стоимость». Сортировку выполняем по возрастанию. Для сортировки используем меню: «Данные\Сортировка». Окно выбора параметров сортировки показано на рис. 11.

Рис. 11 – Сортировка данных

Результат выполненной сортировки списка "Автокаталог" показан на рисунке 12.

 

Рис. 12 – Результат сортировки

Аналогично должны быть описаны технологии выполнения: − фильтрации (автофильтр и расширенный фильтр) (рисунки 5 и 6); − подведения промежуточных и общих итогов (рисунок 8); − создания структуры данных (рисунок 9); − создания сводной таблицы (рисунок 8); − создания диаграммы (рисунок 9);.

 

 

3. Задание 3.3

Создайте в Microsoft Excel таблицу, как на рисунке 13..Количество строк в документе рассчитывается по формуле:

N_{кол. строк} = N_вар + 15,

где N_вар. – номер вашего варианта

Рассчитайте стаж работы. Просмотрите данные о продавцах женского пола, работающих на предприятии больше 4-х лет. Отсортируйте данные таким образом, чтобы работники располагались по возрастанию разряда, а те, в свою очередь, по алфавиту. Выведите на экран список сотрудников, проживающих в Пятигорске. Сформируйте список сотрудников – женщин, проживающих в Пятигорске, имеющих 3-ий разряд.

Рис. 13. Сведения о сотрудниках

 

Порядок выполнения задания

ВНИМАНИЕ! Вы должны придумать свой оригинальный пример и подготовить текст, описывающий технологию работы с таблицами в соответствии с Вашим примером.

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

Переименуйте лист. Для этого дважды щелкните мышью по названию текущего рабочего листа. Название листа выделится. Введите База данных – ENTER.

В строке «2» наберите шапку таблицы как на рисунке 13.

Установите в столбце Ф.И.О. формат ячейки текстовый (выделите столбец – нажмите правой кнопкой мыши на обозначение столбца (например D), выберите пункт меню ФОРМАТ ЯЧЕЕК…, вкладку ЧИСЛО, выберите «числовые форматы» - текстовый), установите формат ячейки – текстовый – для столбцов «должность», «адрес», «пол». Для столбца «телефон» таким же образом установите тип «номер телефона» из формата дополнительный. Для столбцов содержащих даты – установите формат «дата», для столбцов №п/п, Разряд – числовой формат, 0 знаков после запятой.

Справа от столбца «Стаж работы» в ячейке К2 введите название столбца «стаж работы – округленный».

Выделите ячейку А1 и введите текст «текущая дата», в ячейку рядом введите текущую дату, задайте формат ячейке «дата».

Рассчитайте стаж работы. Для этого в столбце «стаж работы» введите формулу «(текущая дата - дата найма)/365». Установите ячейку «текущая дата» в формуле - абсолютной (используйте знак $). «Протяните» формулу. В столбце «стаж работы округленный» необходимо округлить полученный результат так, чтобы учитывались только полные годы работы. Для этого выделите ячейку в столбце «стаж работы округленный», вызовите список функций, в Математических найдите формулу ОКРУГЛВНИЗ. Откройте окно формулы, в строке число введите ту ячейку, число в которой надо округлить (J3), в строке число_разрядов введите 0, ОК. «Протяните» формулу.

Поиск необходимых сведений в базе данных

Выделите всю таблицу. Задайте ей имя «Штат_сотрудников». Для этого нажмите кнопку (вкладка ФОРМУЛЫ – группа ОПРЕДЕЛЕННЫЕ ИМЕНА). Затем выполните команду ФОРМА. В Excel 2007 данная команда доступна в разделе ПАРАМЕТРЫ EXCEL. Для добавления команды на панель быстрого доступа нажмите кнопку «OFFICE» и щелкните ПАРАМЕТРЫ EXCEL. Далее в окне «ПАРАМЕТРЫ EXCEL» выберите пункт НАСТРОЙКА. В списке ВЫБРАТЬ КОМАНДЫ ИЗ выберите ВСЕ КОМАНДЫ и найдите нужную команду (Рисунок 14).Нажмите ДОБАВИТЬ и ОК.

Нажмите кнопку на панели быстрого доступа. Появится окно, отражающее аргументы созданной таблицы. В появившемся окне просмотрите данные о продавцах женского пола работающих на предприятии больше 4-х лет. Для этого нажмите кнопку КРИТЕРИИ и введите в соответствующие ячейки заданные условия (в «стаж работы» - >4). Нажмите ДАЛЕЕ. Просмотрите список полученных результатов отбора (Рисунок 15). Закройте окно.

 

Рис. 14. Добавление кнопки ФОРМА на панель быстрого доступа

 

Для того, чтобы расположить информацию в таблице в определенном порядке используйте команду СОРТИРОВКА. Для этого выделите ячейку «Ф.И.О.», выберите команду СОРТИРОВКА И ФИЛЬТР – НАСТРАИВАЕМАЯ СОРТИРОВКА (вкладка ГЛАВНАЯ – группа РЕДАКТИРОВАНИЕ), в появившемся окне установите «Сортировать по» - разряду - по возрастанию, нажмите кнопку ДОБАВИТЬ УРОВЕНЬ и установите «Затем по» Ф.И.О. – от А до Я. Просмотрите отсортированные данные.

 

Рис. 15. Просмотр данных с помощью приложения Microsoft Excel – ФОРМА

 

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

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

 

Рис. 16. Использование приложения АВТОФИЛЬТР для обработки данных

 

 

Рис. 17. Использование приложения РАСШИРЕННЫЙ ФИЛЬТР для обработки данных

 

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

 

4. Задание 3.4. Создайте таблицу, содержащую следующие сведения о сотрудниках ор­ганизации аналогичные тем, которые приведены на рис. 18 (количество строк документа должно быть не менее 25).

4.1.Отсортируйте записи по:

- вариант №1 – по алфавиту фамилий и уменьшению разряда;

- вариант №2 – по алфавиту фамилий и увеличению разряда;

- вариант №3 – по дате рождения и уменьшению разряда;

- вариант №4 – по дате рождения и уменьшению разряда;

- вариант №5 – по № телефона и по алфавиту фамилии;

- вариант №6 – по адресу проживания и уменьшению разряда;

- вариант №7 – по адресу проживания и увеличению разряда;

- вариант №8 – по году рождения и месту проживания;

- вариант №9 – по году рождения и стажу работы в организации;

- вариант №10 – по стажу работы в организации и месту проживания;

 

4.2. Выдайте список сотрудников организации:

- вариант №1 – проживающих в Пятигорске или Ессентуках;

- вариант №2 – проживающих в Ессентуках или Кисловодске;

- вариант №3 – проживающих в Иноземцево или Железноводске;

- вариант №4 – проживающих в Ессентуках или ст. Ессентукской;

- вариант №5 – проживающих в Ессентуках или Железноводске;

- вариант №6 – проживающих в Пятигорске или Железноводске;

- вариант №7 – проживающих в Иноземцево или Железноводске;;

- вариант №8 – проживающих в Лермонтове или Железноводске;;

- вариант №9 – проживающих в Лермонтове или Кисловодске;

- вариант №10 – проживающих в Ессентуках или Минводах;

 

4.3. Выдайте список сотрудников организации:

- варианты №1, 5 и 9 – чей мобильник начинается на 8928;

- варианты №2, 6 и 10 – чей мобильник начинается на 8961;

- варианты №3, 7 – чей мобильник начинается на 8938;

- варианты №4, 8 – чей мобильник начинается на 8918;

 

4.4. Проживающих:

- варианты №1, 7 и 11 – в Пятигорске, старше 30 лет на момент осуществления поиска информации, принятых после 01.09.2010;

- варианты №3, 8 и 12 – в Железноводске, не старше 30 лет на момент осуществления поиска информации, принятых до 25.09.2008.

- варианты №4, 9 и 6 – в Ессентуках или Пятигорске, не старше 40 лет на момент осуществления поиска информации, принятых после 30.07.2010.

- варианты №5, 10 и 2 – в Пятигорске или Минводах, старше 25, но младше 50 лет на момент осуществления поиска информации, принятых после 01.06.2011.

 

Рис. 18. Данные о сотрудниках

5. Задание 3.5.Предприятие «Альфа» осуществляет оптовую реализацию бытовой техники со складов. Имея сведения о количестве проданной продукции в феврале, определите суммы выручки предприятия за месяц.

Создайте таблицу, отражающую реализацию:

- варианты №3, 9 - кофеварок и миксеров;

- варианты №4, 10 - чайников и СВЧ печей за месяц;

- варианты №1, 5 и 7 - миксеров и аэрогрилей за месяц;

- варианты №2, 6 и 8 - кофеварок и чайников за месяц;

 

Создайте таблицу, показывающую все поставки (количество строк документа должно быть не менее 35): предприятия ООО «Авангард» с 15.02.14 на сумму, превышающую 1000000 руб. на:

- склады №1, №2 или №5 (варианты №2, 10);

- склады №2, №4 или №7 (варианты №3, 1);

- склады №1, №3 или №6 (варианты №4, 6 и 9);

- склады №4, №5 или №6 (варианты №5, 7 и 8).

 

Рис. 19. Продажи предприятия «Альфа» за февраль

 

УЧЕБНО-МЕТОДИЧЕСКОЕ И ИНФОРМАЦИОННОЕ ОБЕСПЕЧЕНИЕ ДИСЦИПЛИНЫ

3.1. Рекомендуемая литература

3.1.1. Основная литература:

1. Информационные технологии в юридической деятельности: учебник для бакалавров / под ред. П. У. Кузнецова; Урал. гос. юрид. акад. – 2-е изд., перераб. и доп. – М.: Юрайт, 2013. – 422 с. – (Бакалавр. Базовый курс). – Гриф: Доп. МО. – Библиогр.: с. 429-441. – ISBN 978-5-9916-2481-7

2. Гаврилов, М. В. Информатика и информационные технологии: учебник для прикладного бакалавриата / М. В. Гаврилов, В. А. Климов; Сарат. гос. юрид. академия. – 4-е изд., перераб. и доп. – Москва: Юрайт, 2014. – 383 с.: ил. – (Бакалавр. Прикладной курс). – Библиогр.: с. 383. – ISBN 978-5-9916-3666-7

 

3.1.2. Дополнительная литература:

1. Современные Интернет-технологии в коммерческой деятельности: учебное пособие/Н.А. Инькова. – 4-е изд., стер. – М.: Омега – Л, 2010. – 188с.

2. Алгулиев, Р. Информационное общество: интересные хронологические факты / Расим Алгулиев, Парвана Салманова; Нац. Акад. Наук Азербайджана, Ин-т информационных технологий. – Баку: Информационные технологии, 2014. – 169 с.: ил. – ISBN 978-9952-434-47-7.

 

3.1.3. Методическая литература:

1. методические указания к выполнению лабораторных работ;

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

 

3.1.4. Интернет-ресурсы:

1. http://www.intuit.ru – сайт дистанционного образования в области информационных технологий

2. http://www.iqlib.ru - интернет библиотека образовательных изданий, в которой собраны электронные учебники, справочные и учебные пособия;

3. http://www.biblioclub.ru - электронная библиотечная система «Университетская библиотека – on-line»: специализируется на учебных материалах для ВУЗов по научно-гуманитарной тематике, а так же содержит материалы по точным и

4. http://window.edu.ru – образовательные ресурсы ведущих вузов

5. http://ncfu.ru – сайт СКФУ

 

Программное обеспечение

1. Пакет прикладных программ MS Office.