Работа с базами данных и списками
Excel предоставляет возможность работать с базами данных, которые здесь называются списками. Список – таблица, построенная по принципу базы данных: множество однотипных строк-записей, разделенных на поля. Одна или две верхних строки списка должны содержать заголовки столбцов (имена полей). Приведенная ниже таблица, содержащая данные о сотрудниках, представляет собой список:
Сотрудники | ||||
ФИО | Возраст | Должность | Стаж | Оклад, руб |
Алексеев Н.Г | 22 | программист | 1 | 2000 |
Пронина Е.Е. | 35 | бухгалтер | 15 | 1800 |
Воронин Н.П. | 41 | гл. бухгалтер | 19 | 4500 |
Алексеева А.Г. | 19 | лаборант | 2 | 1200 |
Имена полей выделены серым цветом. Запись хранит данные об одном сотруднике.
Основные функции при работе с любой базой данных – поиск информации по определенным критериям и сортировка данных.
В Excel все операции по работе с данными содержатся в меню Данные.
Сортировка данных
Команда Данные→Сортировка и фильтр→Сортировка позволяет упорядочивать данные в списке. Курсор должен находиться в области списка. В диалоговом окне Сортировка следует выбрать последовательность полей сортировки. Например, при выборе в поле Сортировать по: возраст, а в поле Затем по: ФИО для приведенного выше списка получим:
Сотрудники | ||||||
ФИО | Возраст | Должность | Стаж | Оклад, руб | ||
Алексеева А.Г. | 19 | лаборант | 2 | 1200 | ||
Алексеев Н.Г | 22 | программист
| 1 | 2000 | ||
Пронина Е.Е. | 35 | бухгалтер | 15 | 1800 | ||
Воронин Н.П. | 41 | гл. бухгалтер | 19 | 4500 |
Фильтрация данных
Команда Данные→Фильтр позволяет выделять нужные записи. Фильтрация возможна через автоматический фильтр Автофильтр (для простых условий отбора) и через Расширенный – ручной (для более сложных условий отбора). Курсор должен находится в области списка.
В отфильтрованном списке отображаются только строки, отвечающие условиям отбора, заданным для столбца.
При использовании Автофильтра на именах полей появятся кнопки, нажимая на которые можно задавать критерии фильтрации. В появившемся подменю пункт Первые 10 оставляет 10 наибольших или наименьших элементов в списке, пункт Все оставляет все элементы списка, а пункт Условие… вызывает диалоговое окно, в котором можно установить параметры фильтрации.
Для отмены фильтрации следует повторно вызвать команду Данные→Фильт р и убрать галочку в поле Автофильтр.
Команда Расширенный фильтр позволяет:
§ задавать условия, соединенные логическим оператором ИЛИ для нескольких столбцов (для объединения условий с помощью логического оператора И проще дважды использовать команду Автофильтр);
§ задавать три и более условий для конкретного столбца с использованием, по крайней мере, одного логического оператора ИЛИ. Например, можно оставить на экране записи о сотрудниках, чьи фамилии начинаются с букв А, В или Л;
|
|
§ задавать вычисляемые условия;
§ извлекать строки из списка и вставлять эти копии в другую часть текущего листа. При извлечении строк с помощью Автофильтра копирование и вставку автоматически выполнить невозможно.
При использовании расширенного фильтра необходимо в диалоговом меню указать три области:
§ Исходный диапазон – диапазон данных из списка, для которого необходимо выполнить фильтрацию;
§ Диапазон условий – диапазон ячеек рабочего листа, в котором указаны критерии отбора;
§ Поместить результат в диапазон – диапазон рабочего листа, предназначенный для вывода отобранных записей. Установка диапазона возврата результатов становится доступной только при выбранном переключателе скопировать результат в другое место. В противном случае список отфильтруется на месте исходного диапазона.
Правила для задания текстовых условий:
§ единственная буква – поиск всех значений, начинающихся с этой буквы;
§ символы < или > ‑ поиск всех значений, которые находятся по алфавиту до или после введенного значения;
|
|
§ формула =”=текст” ‑ поиск всех значений, которые точно совпадают со строкой символов текст. Например, при задании =”=Алексеев”, будут найдены все строки, содержащие фамилию Алексеев, при задании фамилии Алексеев без формулы выберутся также строки с фамилиями Алексеева, Алексеевич и т. д.
Пример результата фильтрации с использованием фильтра для выбора всех сотрудников младше 20 лет, имеющих стаж работы меньше 5 лет, или старше 40 лет со стажем работы меньше 20 лет:
Сотрудники | ||||||
ФИО | Возраст | Должность | Стаж | Оклад, руб | ||
Алексеева А.Г. | 19 | лаборант | 2 | 1200 | ||
Алексеев Н.Г | 22 | программист | 1 | 2000 | ||
Пронина Е.Е. | 35 | бухгалтер | 15 | 1800 | ||
Воронин Н.П. | 41 | гл. бухгалтер | 19 | 4500 | ||
|
|
|
| |||
| Возраст
|
| Стаж |
| ||
| < 20 |
| <10 |
| ||
| >40 |
| <20 |
| ||
|
|
|
| |||
ФИО | Возраст | Должность | Стаж | Оклад, руб | ||
Алексеева А.Г. | 19 | лаборант | 2 | 1200 | ||
Воронин Н.П. | 41 | гл. бухгалтер | 19 | 4500 |
Отменить фильтрацию можно командой Данные→Фильтр→Отобразить все.
Содержание работы
Задания выполняйте на отдельных листах в книге с предыдущей лабораторной работой.
Задание 1
Постройте таблицу "Список слушателей", приведенную в описании лабораторной работы, и выполните для нее все приведенные в описании примеры вычислений с применением условных функций.
Добавьте в эту таблицу еще три столбца с оценками по дисциплинам Информатика, Математика, Английский язык перед столбцом Баллы (оценки выставляются по пятибалльной системе), подсчитайте значение среднего балла в столбце Баллы. Добавьте еще несколько строк. Заполните столбец с информацией о зачислении следующим образом: зачисленными считаются все, набравшие не менее 13 баллов либо набравшие 12 баллов, но имеющие по математике оценку 5. Ячейки столбца о зачислении должны иметь значение зачислен, остальные ячейки должны остаться пустыми.
Зачисленных слушателей выделите полужирным шрифтом с помощью условного форматирования. Набравших не менее11 баллов и не более 12 баллов выделите в столбце Баллы заливкой зеленого цвета.
Выполните сортировку:
· по алфавиту,
· по среднему баллу (по убыванию).
Подсчитайте общее количество зачисленных, количество зачисленных учащихся школы № 12, количество набравших более 12 баллов с помощью функции СЧЕТЕСЛИ и функции суммирования для массивов.
Выполните фильтрацию данных:
- с помощью команды Автофильтр, оставив видимыми только тех, кто зачислен на курсы и учится в лицее №1;
- с помощью команд Автофильтр и Расширенный фильтр, выделить всех зачисленных, обучающихся в лицее № 1 или в школе № 12,
- с помощью команды Расширенный фильтр, выделив всех учащихся лицея № 1, получивших пятерку по математике и всех учащихся лицея № 4, получивших пятерку по информатике.
Задание 2
Постройте таблицу с данными о сборе фруктов, добавьте в нее несколько новых строк (не вводите новые виды фруктов). Выполните все приведенные в описании примеры расчетов.
Вычислите вес каждого вида собранных фруктов с помощью функции СУММЕСЛИ.
Вычислите вес собранных фруктов в английских фунтах (1 фунт = 0,4536 кг) с помощью формулы для массивов:
- введите новую ячейку с текстом Вес,фунты,
- выделите диапазон значений для размещения результата (столбец Вес,фунты), наберите нужную формулу, сделав ссылку на весь диапазон исходных данных (Вес, кг),
- завершите ввод формулы нажатием [Ctrl+Shift+Enter].
Контрольные вопросы
1. Какие логические функции имеются в Excel?
2. Что такое массив, как выполняется работа с массивами в Excel?
3. В чем заключается различие между функцией СУММЕСЛИ() и функцией условного суммирования для массивов?
4. Для чего используется условное форматирование?
5. Дайте определение базы данных.
6. Как называется база данных в Excel?
7. Как должен быть оформлен список в Excel?
8. Какие действия с базами данных выполняются в Excel?
9. В чем заключаются различия между сортировкой и фильтрацией?
10. В каких случаях используется расширенный фильтр?
Лабораторная работа № 8
Дата добавления: 2018-04-15; просмотров: 529; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!