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

ЛАБОРАТОРНАЯ РАБОТА №11 ОБРАБОТКА ДАННЫХ, ПРЕДСТАВЛЕННЫХ В ВИДЕ

СПИСКА

1. Понятие о списке

Список (база данных) – это одно из средств организации данных на рабочем листе. Список создается как непрерывная прямоугольная область клеток, которая состоит из строк с однотипными данными (см. Рисунок 1).

Структура списка:

строка списка – запись базы данных;

столбец списка – поле базы данных;

название столбца – имя поля базы данных;

все ячейки строки с именами полей базы данных – область имен полей базы данных;

весь блок ячеек с данными (без области имен полей базы данных) – область данных.

Рисунок 1. Пример списка (базы данных)

2. Сортировка данных в списке

Цель сортировки данных – упорядочивание данных в списке.

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

ставим курсор мыши в область данных1

↓

на вкладке Данные в группе Сортировка и фильтр выбираем ИЛИ

на вкладке Главная в группе Редактирование

1 Если в области имен полей есть объединенные ячейки, то выделяем область данных, которые необходимо отсортировать.

● - сортировка по возрастанию;

● - сортировка по убыванию;

● - настраиваемая сортировка, относительно нескольких полей, где → выбираем имя поля относительно, которого необходимо провести сортировку данных (название столбца) → выбираем порядок сортировки (по возрастанию или убыванию)

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

3. Условное форматирование данных в списке

Цель условного форматирования данных в списке – выделение элементов поля базы данных (цветом, шрифтом, размером), удовлетворяющих определенному условию.

Условное форматирование данных:

выделяем поле базы данных (поле, несколько полей или область данных), данные из которого должны выделиться некотором цветом (шрифтом), если будет выполняться некоторый критерий (условие) → на вкладке Главная в группе Стили выбираем

● позволяет создавать сложные критерии для отбора;

● позволяет изменять уже созданные правила по условному форматированию, добавлять новые правила и удалять не нужные

Примеры (на создания правила).

1) Выделить красным цветом ту цену, которая меньше 1000 рублей.

2)Выделить красным цветом те наименования товаров, продано которых менее 30 штук.

3)Выделите красным цветом те наименования товаров и выручку, величина выручки которых принимает свое значение меньше средней выручки2.

4. Фильтрация данных в списке

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

Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр

и Расширенный фильтр.

Автофильтрация данных

Автофильтр для каждого столбца строит список значений, который используется для задания условий фильтрации.

2 При составлении данного условия фигурирует средняя выручка, о которой в пределах нашего списка нет речи, поэтому это значения можно было вычислить вне области списка, как мы и сделали, а можно было это значение вычислить в момент проверки условия, т.е вместо условия «=$F2<$I$1» записать «=$F2<срзнач($F$2:$F$6)».

Автофильтр:

выделяем область имен полей → на вкладке Главная в группе Редактирование выбираем

ИЛИ

на вкладке Данные в группе Сортировка и фильтр выбираем

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

список всех уникальных элементов соответствующего столбца;

только те записи, значение полей которых находятся в границах некоторого интервала (Настраиваемый фильтр …);

только те записи, значения которых принимают наибольшее или наименьшее значение (Первые 10 …).

Расширенный фильтр

Расширенный фильтр позволяет использовать при фильтрации записей списка критерии сравнений и вычисляемые критерии.

Расширенный фильтр:

вставляем несколько строк в верхней части рабочего листа

↓

в ячейке одной из вставленных пустых строк вводим имя поля3, по которому следует

отфильтровать данные, а под этой ячейкой условие фильтрации (например, )

↓

на вкладке Данные в группе Сортировка и фильтр выбираем

Расширенный фильтр в отличие от Автофильтра отфильтрованные записи может выводить в другое место рабочего листа Ехсel, не испортив начальный список.

Правила формирования множественного критерия (см. Таблица 1Рисунок 1):

если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И;

если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Таблица 1

Символьное				Графическое	Условие связи			Пример
обозначение				изображение	критерий
					И
a	x	b			(условия
					выполняются
			а	b
					вместе)
					ИЛИ
x	a , x	b			(выбор одного из
				а b	условий)

ЗАДАНИЯ ДЛЯ ВЫПОЛНЕНИЯ

Упражнение 1

Закрепите навык сортировки данных представленных в виде списка, выполнив следующее:

в вашей папке в файле Работа_в_Excel.xlsx на листе с названием «список» выполните сортировку, согласно своего варианта (

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

Таблица 2).

	Таблица 2
Вариант	Отсортировать по
1	убыванию, относительно поля с именем «Кол–во, кг.»
2	возрастанию, относительно поля с именем «ФИО»
3	убыванию, относительно поля с именем «Итого»
4	возрастанию, относительно поля с именем «Стоимость путевки, руб.»
5	убыванию, относительно поля с именем «ФИО»
6	возрастанию, относительно поля с именем «Средний балл»
7	убыванию, относительно поля с именем «Кол–во, кг.»
8	возрастанию, относительно поля с именем «Кол–во минут»
9	убыванию, относительно поля с именем «ФИО сотрудника»
10	возрастанию, относительно поля с именем «Стоимость 1 посещения, руб.»

Упражнение 2

Научитесь условно форматировать данных представленных в виде списка, выполнив следующее:

в вашей папке в файле Работа_в_Excel.xlsx на листе с названием «список» выполните условное форматирование, согласно своего варианта (Таблица 3).

			Таблица 3
	Вариант		Задание
		1)	выделите красным цветом то количество собранных яблок, которое
	1		больше их среднего количества яблок;
		2)	выделите желтым цветом те ФИО рабочих у которые выручка больше
			1500 руб, в противном случае выделите их зеленым цветом.
		1)	выделите голубым цветом те ФИО клиентов, у которых сумма к оплате
			меньше их среднего значения;
	2	2)	выделите розовым цветом те значения количества электроэнергии,
			которые меньше 300 кВтч, в противном случае выделите их оранжевым
			цветом.
		1)	выделите, синим цветом то количество поступивших журналов в
			киоски, которое больше среднего их количества журналов;
	3	2)	выделите зеленым цветом те ФИО продавцов, у которых количество
			поступивших журналов больше 15 шт., в противном случае выделите их
			салатовым цветом.
		1)	выделите лиловым цветом те туристические фирмы, у которых
	4		количество купленных путевок меньше их среднего количества;
		2)	выделите бирюзовым цветом ту стоимость путевок, которая меньше
			3000 руб., в противном случае выделите их изумрудным цветом.
		1)	выделите сиреневым цветом то количество деталей, которое больше их
	5		среднего количества;
		2)	выделите красным цветом те ФИО рабочих, которые произвели больше
			100 шт. деталей, в противном случае выделите их синим цветом.
		1)	выделите сизым цветом те ФИО студентов, которые получили оценку за
			экзамен по «Информатике» больше средней оценке по этой дисциплине;
	6	2)	выделите желтым цветом то значение среднего балла, которое больше
			3,5, в противном случае, выделите значения среднего балла оранжевым
			цветом.

	Вариант		Задание
		1)	выделите коричневым цветом то количество собранных студентами
			овощей, которое меньше из среднего количества;
	7	2)	выделите малиновым цветом те ФИО студентов, которые получили
			выручку больше 3000 руб., в противном случае выделите их сиреневым
			цветом.
		1)	выделите серым цветом те ФИО абонентов, которые проговорили по
	8		телефону больше среднего значения;
		2)	выделите зеленым цветом то количество минут, которое меньше 15
			минут, в противном случае выделите желтым цветом.
		1)	выделите, синим цветом тот оклад преподавателей, который меньше
			среднего оклада;
	9	2)	выделите голубым цветом те ФИО сотрудников, которые получили к
			выдаче больше 8000 руб., в противном случае выделите их сиреневым
			цветом.
		1)	выделите коричневым цветом тех врачей, у которых количество
	10		посещений больше их среднего значения;
		2)	выделите розовым цветом то количество посещений которое меньше 5,
			в противном случае выделите количество голубым цветом.

Упражнение 3

Научитесь фильтровать данные представленные в виде списка с помощью Автофильтра, закрепите навыки копирования объектов и навыки работы с листами, выполнив следующее:

1)в вашей папке в файле Работа_в_Excel.xlsx создайте лист с названием «Автофильтр» и поместите на него копию таблицы с листа «таблица»;

2)отфильтруйте таблицу на листе «Автофильтр» с помощью Автофильтра, согласно своего варианта (Таблица 4)

Таблица 4

Вариант		Задание
1	выберите 20% записей, содержащих наибольшее значение выручки
2	выбери те записи, у которых сумма к оплате больше 1000 руб. и меньше 2000
	руб.
3	выберите	2 записи, содержащие наименьшее количество	поступивших
	журналов
4	выберите те записи, у которых стоимость путевок больше 5000 руб.
5	выберите	30% записей, содержащих наименьшее значение количества
	произведенных деталей
6	выберите те записи, у которых средний балл больше 4
7	выберите 3 записи, содержащие наибольшее количество собранных овощей
8	выберите те записи, у которых количество минут произведенных разговоров
	меньше 30 и больше 40
9	выберите 25% записей, содержащих наибольшее значении суммы к выдаче
10	выберите те записи, у которых количество посещений больше 5