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

Сортировка данных

Ввод данных в список

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

Команда Данные→Сортировка позволяет упорядочивать данные в списке. Курсор должен находиться в области списка. В диалоговом окне Сортировка следует выбрать последовательность полей сортировки. Например, при выборе в поле Сортировать по: возраст, а в поле Затем по: ФИО для приведенного выше списка получим:

Сотрудники
ФИО	Возраст	Должность	Стаж	Оклад, руб
Алексеева А.Г.		лаборант
Алексеев Н.Г		программист
Пронина Е.Е.		бухгалтер
Воронин Н.П.		гл. бухгалтер

Команда Данные→Фильтр позволяет выделять нужные записи. Фильтрация возможна через автоматический фильтр Автофильтр (для простых условий отбора) и чере з Расширенный – ручной (для более сложных условий отбора). Курсор должен находится в области списка.

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

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

Для отмены фильтрации следует повторно вызвать команду Данные→Фильт р и убрать галочку в поле Автофильтр.

Команда Расширенный фильтр позволяет:

§ задавать условия, соединенные логическим оператором ИЛИ для нескольких столбцов (для объединения условий с помощью логического оператора И проще дважды использовать команду Автофильтр);

§ задавать три и более условий для конкретного столбца с использованием, по крайней мере, одного логического оператора ИЛИ. Например, можно оставить на экране записи о сотрудниках, чьи фамилии начинаются с букв А, В или Л;

§ задавать вычисляемые условия;

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

При использовании расширенного фильтра необходимо в диалоговом меню указать три области:

§ Исходный диапазон – диапазон данных из списка, для которого необходимо выполнить фильтрацию;

§ Диапазон условий – диапазон ячеек рабочего листа, в котором указаны критерии отбора;

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

Правила для задания текстовых условий:

§ единственная буква – поиск всех значений, начинающихся с этой буквы;

§ символы < или > ‑ поиск всех значений, которые находятся по алфавиту до или после введенного значения;

§ формула =”=текст” ‑ поиск всех значений, которые точно совпадают со строкой символов текст. Например, при задании =”=Алексеев”, будут найдены все строки, содержащие фамилию Алексеев, при задании фамилии Алексеев без формулы выберутся также строки с фамилиями Алексеева, Алексеевич и т. д.

Пример результата фильтрации с использованием фильтра для выбора всех сотрудников младше 20 лет, имеющих стаж работы меньше 5 лет, или старше 40 лет со стажем работы меньше 20 лет:

Сотрудники
ФИО	Возраст	Должность	Стаж	Оклад, руб
Алексеева А.Г.		лаборант
Алексеев Н.Г		программист
Пронина Е.Е.		бухгалтер
Воронин Н.П.		гл. бухгалтер
	Возраст		Стаж
	< 20		<10
	>40		<20
ФИО	Возраст	Должность	Стаж	Оклад, руб
Алексеева А.Г.		лаборант
Воронин Н.П.		гл. бухгалтер

Отменить фильтрацию можно командой Данные→Фильтр→Отобразить все.

Содержание работы

Задания выполняйте на отдельных листах в книге с предыдущей лабораторной работой.

Задание 1

Постройте таблицу "Список слушателей", приведенную в описании лабораторной работы, и выполните для нее все приведенные в описании примеры вычислений с применением условных функций.

Добавьте в эту таблицу еще три столбца с оценками по дисциплинам Информатика, Математика, Английский язык перед столбцом Баллы ( оценки выставляются по пятибалльной системе), подсчитайте значение среднего балла в столбце Баллы. Добавьте еще несколько строк. Заполните столбец с информацией о зачислении следующим образом: зачисленными считаются все, набравшие не менее 13 баллов либо набравшие 12 баллов, но имеющие по математике оценку 5. Ячейки столбца о зачислении должны иметь значение зачислен, остальные ячейки должны остаться пустыми.

Зачисленных слушателей выделите полужирным шрифтом с помощью условного форматирования. Набравших не менее11 баллов и не более 12 баллов выделите в столбце Баллы заливкой зеленого цвета.

Выполните сортировку:

· по алфавиту,

· по среднему баллу (по убыванию).

Подсчитайте общее количество зачисленных, количество зачисленных учащихся школы № 12, количество набравших не менее 11 и не более 12 баллов с помощью функции СЧЕТЕСЛИ и функции суммирования для массивов.

Выполните фильтрацию данных:

• с помощью команды Автофильтр, оставив видимыми только тех, кто зачислен на курсы и учится в лицее №1;
• с помощью команд Автофильтр и Расширенный фильтр, выделить всех зачисленных, обучающихся в лицее № 1 или в школе № 12,
• с помощью команды Расширенный фильтр, выделив всех учащихся лицея № 1, получивших пятерку по математике и всех учащихся лицея № 4, получивших пятерку по информатике.

Задание 2

Постройте таблицу с данными о сборе фруктов, добавьте в нее несколько новых строк (не вводите новые виды фруктов). Выполните все приведенные в описании примеры расчетов.

Вычислите вес каждого вида собранных фруктов с помощью функции СУММЕСЛИ.

Вычислите вес собранных фруктов в английских фунтах (1 фунт = 0,4536 кг) с помощью формулы для массивов:

• введите новую ячейку с текстом Вес,фунты,
• выделите диапазон значений для размещения результата (столбец Вес,фунты), наберите нужную формулу, сделав ссылку на весь диапазон исходных данных (Вес, кг),
• завершите ввод формулы нажатием [Ctrl+Shift+Enter].

Задание 3

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

Задание 4

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

Выполнить сортировку и фильтрацию данных в списке по заданию преподавателя.

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

1. Какие логические функции имеются в Excel?

2. Что такое массив, как выполняется работа с массивами в Excel?

3. В чем заключается различие между функцией СУММЕСЛИ() и функцией условного суммирования для массивов?

4. Для чего используется условное форматирование?

5. Дайте определение базы данных.

6. Как называется база данных в Excel?

7. Как должен быть оформлен список в Excel?

8. Какие действия с базами данных выполняются в Excel?

9. В чем заключаются различия между сортировкой и фильтрацией?

10. В каких случаях используется расширенный фильтр?

Дата добавления: 2014-11-20