Лабораторная работа № 3
Работа со справочниками и сводными таблицами Microsoft Excel
Цель работы Приобретение навыков использования электронных таблиц Microsoft Excel для работы с базой данных, сортировки информации и поиска по различным критериям.
Указания к выполнению работы
Общие сведения
Microsoft Excel располагает средствами для обработки информации в базе данных, которая представляет собой упорядоченную информацию, объединенную в единое целое. Столбцы таблицы можно рассматривать как поля, а строки - как записи. Совокупность данных в виде таблицы полей и записей в таком случае называется списком или базой данных. В Excel понятия список и база данных взаимозаменяемы, и под ними понимают прямоугольную область ячеек, в которой строки электронной таблицы имеют фиксированную структуру заполнения.
Существуют ограничения, накладываемые на структуру базы данных:
· Первый ряд базы данных должен содержать неповторяющиеся имена полей, которые могут состоять из нескольких слов, обязательно размещенных в одной ячейке;
· Остальные ряды базы данных содержат записи, которые не должны быть пустыми рядами;
· И информация по полям (столбцам) должна быть однородной, то есть только цифры или только текст.
По отношению к базе данных могут быть выполнены различные операции обработки. Для определения списка перед выполнением операций обработки достаточно установить курсор в любую ячейку списка. В том случае, когда список сформирован неверно или нужно работать с частью области списка, требуется выделение области списка. Данные, представленные в виде такой таблицы, можно сортировать, осуществлять выборку по условию, анализировать, обрабатывать и представлять результаты в нужном виде.
Применение электронной таблицы обеспечивает успешное решение относительно небольших, хорошо сформулированных задач. Но когда таблица содержит несколько сотен строк, а документ состоит из многих листов, то работать с ним становится довольно трудно. Если электронная таблица предназначена для других пользователей, то осложняется процесс контроля ввода новых и использования существующих данных. Например, когда в электронной таблице в одной ячейке должна храниться дата, а в другой – денежное значение, пользователь может легко ошибиться и ввести в них текстовые значения. При комплексной проверке данных, а также при необходимости коллективного использования информации функций электронной таблицы недостаточно для работы с базой данных. Таким образом, следует помнить, что электронные таблицы Excel могут быть успешно использованы для работы с базами данных только в задачах с небольшими объемами данных.
Основные правила работы с базой данных (списком)
Базу данных в электронной таблице Excel рекомендуется размещать на отдельном рабочем листе. По мере увеличения базы данных новые строки будут добавляться в конец рабочего листа, поэтому если на этом же листе будут размещены другие данные, не имеющие отношение к базе данных, то может возникнуть опасность их уничтожения.
Для создания базы данных необходимо поместить курсор в ячейку верхней части таблицы и ввести в одну строку названия полей. Строка с названиями полей должна предшествовать списку. Оставлять пустую строку между строкой с названиями полей и фактическими данными недопустимо. Название каждого поля должно быть уникальным. В верхней части списка может быть несколько строк с введенной в них информацией, однако только одна строка будет восприниматься как содержащая названия полей. Название поля не должно превышать 255 символов, для удобства лучше выбирать короткие названия.
Дальнейшие шаги по созданию базы данных – это ввод в каждую ячейку в строке, которая находится непосредственно под строкой с названиями полей, фактических данных по каждому полю. Необходимо следить, чтобы данные, вводимые в один столбец, имели одинаковый формат. Записи базы могут добавляться, редактироваться и удаляться вручную путем выполнения операций непосредственно над ячейками. Кроме того, Excel предоставляет возможность находить и редактировать данные с помощью формы. Чтобы просмотреть данные на экране с помощью формы, необходимо поместить рамку выделения в любую ячейку базы данных и выполнить команду Данные|Форма из меню программы. Кнопки Добавить и Удалить используются соответственно для ввода и удаления записей, а кнопки Назад и Далее – для поиска нужной. Движение по записям также можно осуществлять при помощи полосы прокрутки на форме.
Основная функция любой базы данных – поиск информации по определенным критериям. Критерии поиска данных представляются в виде некоторой формулы, которая задает условие принадлежности данных этому критерию. Чтобы найти в списке данных отдельные записи, используя критерий поиска, следует воспользоваться кнопкой Критерии на форме. После нажатия на нее Excel предложит задать для каждого поля критерий поиска. В формуле критерия могут быть использованы следующие операторы сравнения: <, >, =, <>, <=, >=. Кроме того, можно использовать символы шаблона * и ?. Вопросительный знак ? означает любой символ, а звездочка * – последовательность произвольных символов. Если ввести несколько условий в различные поля формы в режиме критерия, то поиск информации осуществится по этим критериям, связанным операцией «логическое И». Критерии с «логическим ИЛИ» указывать в форме данных нельзя. Для выполнения поиска необходимо нажать клавишу Enter или щелкнуть на кнопке Правка (которая появилась на форме вместо кнопки Критерии). Excel вернет пользователя к исходной форме, в которой можно двигаться между выбранными по критерию записями.
В электронной таблице можно осуществить сортировку базы данных, то есть упорядочивание данных по убыванию или по возрастанию. Для выполнения сортировки необходимо выделить область базы данных или поместить в нее курсор, а затем выполнить команду Сортировка в меню Данные. В диалоговом окне «Сортировка диапазона» можно задать до трех полей, по которым будет последовательно проводиться сортировка (ключи сортировки). Для проведения сортировки более, чем по трем полям, соответствующую операцию необходимо выполнить несколько раз. Кроме того, можно быстро отсортировать записи по одному полю как по возрастанию, так и по убыванию, выбрав любую ячейку этого поля и щелкнув на кнопке Сортировка по возрастанию или Сортировка по убыванию на стандартной панели инструментов.
Выделение (фильтрация) нужных записей осуществляется командой Фильтр в меню Данные. Фильтрация возможна как через автоматический фильтр Автофильтр, так и через Расширенный фильтр - ручной. При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных, или выделить ее, а затем выполнить команды Данные| Фильтр| Автофильтр. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. Критерии фильтрации находятся в списке в алфавитном порядке. В дополнении к ним имеются еще пять ключей: Все, Первые 10, Условие, Пустые, Непустые. Эти ключи позволяют найти в списке соответственно все записи (отключить фильтр); первые десять записей, отобранных по выбранному показателю; записи, удовлетворяющие заданному пользователем условию; записи, не содержащей никакой информации в данном поле (пустые записи); записи, содержащие информацию. После выбора критерия фильтрации, все записи, не удовлетворяющие этому критерию, становятся невидимыми. С помощью ключа Условие можно выполнить отбор записей, удовлетворяющих сложным критериям. Например, записи только определенного диапазона или записи, удовлетворяющие критериям с логическим ИЛИ. Вывод полного списка осуществляется выбором ключа фильтрации Все, либо командой Фильтр |Отобразить все.
Одновременная фильтрация по нескольким полям с сохранением условия отбора записей возможна при выполнении команды Данные|Фильтр|Расширенный фильтр. При использовании Расширенного фильтра необходимо сначала определить (создать) три области:
· интервал списка (область базы данных);
· интервал критериев (область, где задаются критерии фильтрации);
· интервал извлечения (область, в которой будут появляться результаты фильтрации).
Имена полей во всех интервалах должны точно совпадать.
Для выполнения действий по фильтрации необходимо выбрать команду Фильтр в меню Данные, пункт Расширенный фильтр, в результате чего появится диалоговое окно «Расширенный фильтр». В диалоговом окне необходимо указать координаты соответствующих интервалов. Если необходимо получить результаты фильтрации в интервале извлечения, нужно поставить переключатель скопировать результат в другое место. Таким образом, для проведения расширенной фильтрации необходимо задать:
· Интервал, который содержит список фильтруемых данных (включая верхнюю строку с именами полей);
· Интервал, который содержит строки критериев фильтрации для каждого поля списка данных (включая верхнюю строку с именами полей для фильтруемого списка);
· Интервал, в который будут скопированы результаты фильтрации (включая верхнюю строку с именами полей для фильтруемого списка).
В случае, если интервал извлечения не был задан, то в базе данных будут показаны только те записи, которые удовлетворяют условиям фильтрации. Вернуться к исходному списку данных можно с помощью команды Фильтр |Отобразить все.
Функции для работы с базой данных (списком)
Сумм(диапазон) - суммирует данные из диапазона;
СуммЕсли (диапазон1; условие; диапазон2) - просматривает ячейки из диапазона1, и суммирует ячейки с тем же индексом из диапазона2, если условие выполняется;
СчетЕсли (диапазон; условие) - подсчитывает в диапазоне количество ячеек, отвечающих условию;
Если (условие; оператор1; оператор2) - если условие истинно, выполняется оператор1, иначе оператор2;
И(условие1;…;условиеK) - истина, если все K условий истинны, иначе ложь;
ПоискПоЗ (значение; массив;0) - ищет значение в массиве (0 - ищется точное совпадение) и возвращает номер строки, где оно встретилось;
Просмотр(искомая строка; диапазон просмотра; диапазон результатов) – ищет значение в диапазоне просмотра и возвращает соответствующее ему значение из диапазона результатов
Индекс(массив; номер строки) - выбирает значение из массива по индексу.
ВПР (искомое_значение;массивтаблица;номер_столбца;способ поиска) – ищет значение в крайнем левом столбце массива таблицы и возвращает значение в той же строке из указанного столбца массива.
ГПР (искомое_значение;массивтаблица;номер_строки;способ поиска) - Ищет значение в верхней строке массива значений и возвращает значение в том же столбце из заданной строки массива.
Задания к лабораторной работе
1. Создать таблицу «Ведомость» по следующему образцу:
ФИО | Должность | № офиса | Раб. тел. | Дата рожд. | Принят на работу | Оклад | Премия |
2. Заполнить указанную таблицу данными (15-20 записей). Величину оклада варьировать в размере от 4000 до 15000 рублей.
3. С помощью меню Данные| Сортировка отсортировать базу по:
а) Должности сотрудника;
б) дате принятия на работу и дате рождения сотрудника;
в) окладу и фамилии сотрудника.
4. С помощью меню Данные|Форма организовать форму для ввода и просмотра информации в списке данных. Ввести в базу 2 новые записи, используя форму.
5. С помощью меню Данные| Фильтр| Автофильтр отфильтровать записи в базе данных. Вывести следующие записи:
а) только сотрудники, имеющие рабочий телефон;
б) сотрудники старше 30 лет, имеющие оклад больше 10000 рублей;
в) сотрудники, работающие в организации свыше 5 лет;
г) сотрудники с окладом от 6000 до 10000 рублей.
6. Подсчитать среднюю зарплату сотрудников. Добавить к таблице столбец, в котором вывести, сколько процентов составляет зарплата данного сотрудника от средней зарплаты.
7. Сформировать отчет по ведомости, в котором отразить следующую информацию:
а) Фамилия сотрудника с максимальной заработной платой;
б) Количество сотрудников с заработной платой выше средней;
8. Подготовить отчет о выполнении лабораторной работы, в котором указать цель работы, отметить ход ее выполнения, а также дать краткий письменный ответ на контрольный вопрос № 1.
Контрольные вопросы:
Можно ли назвать Microsoft Excel а) информационной системой; б) информационной тезнологией. Ответ обосновать. Средства поиска информации при работе с базами данных в Excel. Основные достоинства и недостатки Microsoft Excel при работе с базами данных.