ЛАБОРАТОРНАЯ РАБОТА № 5

ТЕХНОЛОГИЯ ОБРАБОТКИ ЧИСЛОВОЙ ИНФОРМАЦИИ

ЗАДАНИЕ 1. ВВОД, РЕДАКТИРОВАНИЕ И ФОРМАТИРОВАНИЕ ДАННЫХ........................................		75
ЗАДАНИЕ 2. ФОРМАТИРОВАНИЕ ТАБЛИЦЫ..............................................................................................		78
ЗАДАНИЕ 3.	ВЫЧИСЛЕНИЕ ПО ФОРМУЛАМ..............................................................................................	79
ЗАДАНИЕ 4.	ОТНОСИТЕЛЬНОЕ И АБСОЛЮТНОЕ КОПИРОВАНИЕ....................................................	81
ЗАДАНИЕ 5.	ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ..................................................................................................	83
ЗАДАНИЕ 6.	ПОСТРОЕНИЕ И ФОРМАТИРОВАНИЕ ДИАГРАММ..........................................................	84

ВПРОЦЕССЕ ВЫПОЛНЕНИЯ ЛАБОРАТОРНОЙ РАБОТЫ ФОРМИРУЮТСЯ СЛЕДУЮЩИЕ УМЕНИЯ И НАВЫКИ:

1.Вводить, редактировать, удалять, копировать данные.

2.Форматировать данные.

3.Создавать и редактировать формулы.

4.Использовать функции в формулах.

5.Пользоваться графикой для наглядного представления данных.

6.Применять для анализа данных различные виды обработки.

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1.Перечислите основные функциональные возможности табличных процессоров.

2.Опишите структуру интерфейса табличного процессора.

3.Укажите название, обозначение и назначение элементов интерфейса.

4.Какие форматы данных можно обрабатывать в среде электронных таблиц?

5.Что входит в понятие «диапазон»?

6.Перечислите способы обработки данных.

7.Какими способами можно заполнить ячейки данными?

8.К какой категории относится функция ЕСЛИ?

9.Расскажите правила копирования формул с абсолютными и относительными ссылками.

10.Что нужно сделать для построения диаграммы?

73

ОСНОВНЫЕ ПОНЯТИЯ

Microsoft Excel – это электронная таблица, при помощи которой можно выполнять следующие действия:

•анализировать большие массивы данных, используя свыше 400 математических, статистических, финансовых и других функций;

•связывать различные таблицы между собой;

•выбирать произвольные форматы представления данных;

•создавать и редактировать рисунки и диаграммы.

Структура электронной таблицы основана на принципе хранения различных элементов данных в матричном формате (в столбцах и строках). Каждый элемент информации записывается в своей собственной "ячейке".

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

Интерфейс табличного процессора Excel

1

2

3

		4
6		5
		7
	8	9

1.Кнопка «Office» для вызова списка возможных действий с книгой

вцелом (создание, сохранение, печать, открытие, закрытие).

2.Заголовок приложения.

3.Ленточное меню.

4.Строка формул.

5.Линейка с именами столбцов.

6.Линейка с номерами строк.

7.Рабочая область окна книги – сетка.

8.Ярлычки листов книги.

9.Строка состояния.

Книга – документ Microsoft Excel, которая может состоять из нескольких связанных между собой таблиц, размещенных на отдельных листах книги.

74

Лист (электронная таблица) – первичный документ, максимальный размер которого 256 столбцов и 16384 (65536)строк.

Диапазон – прямоугольная область таблицы, определяемая

•адресами начальной и конечной ячеек, расположенных в диагональных углах прямоугольной области (А1:D4)

•номерами начальной и конечной строк (1:4)

•именами начального и конечного столбцов (A:D)

Текущая ячейка – ячейка, в которой находится курсор.

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

Замечание. Выбрать лист, столбец, строку – щелкнуть мышкой по ярлычку листа, имени столбца, номеру строки.

Текущий столбец – выбранный столбец, либо столбец, содержащий текущую ячейку.

Текущая строка – выбранная строка, либо строка, содержащая текущую ячейку.

Маркер автозаполнения – черный квадратик в правом нижнем углу текущей ячейки.

БЛОК ОСНОВНЫХ ЗАДАНИЙ

ЗАДАНИЕ 1. ВВОД, РЕДАКТИРОВАНИЕ И ФОРМАТИРОВАНИЕ ДАННЫХ

НАЧАЛЬНЫЙ УРОВЕНЬ
1.	На Листе 1 новой книги ввести данные по образцу, начиная с ячейки А1.
№ уч.нед.
№ лаб.р.			1	2		3		4			5			6			7	8
Вес лаб.р			0,1	0,1		0,1		0,1			0,1			0,2			0,2	0,1
Рейтинг
защиты
Взвешенный
рейтинг
2.	Выделить полужирным первый столбец.
3.	Центрировать числовые данные в строке № лаб.р.
4.	Ввести произвольные числовые данные в строке Рейтинг защиты.
Диапазоны баллов: 0-24 – «2», 25-49 – «3», 50-74 – «4», 75-100 – «5».
5.	Сохранить книгу.

БАЗОВЫЙ УРОВЕНЬ

1.Заполнить строку № уч.нед. числами от 1 до 17 с помощью арифметической прогрессии с шагом 1.

2.Сохранить книгу.

ПОВЫШЕННЫЙ УРОВЕНЬ

1. Применить к ячейкам строки Взвешенный рейтинг числовой формат с

75

нулевым количеством знаков в дробной части. 2. Сохранить книгу.

СПРАВОЧНЫЙ МАТЕРИАЛ

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

В Microsoft Excel текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например, 10AВС109, 127XYZ, 12-976, 208 4675.

При использовании формата "Дата " программа электронных таблиц показывает запись в формате даты, сохраняя ее при этом в числовом виде. Число, соответствующее дате, основывается на количестве дней, истекших с какой-либо определенной даты (как правило, 1 января 1900 года).

В Microsoft Excel число может состоять из символов: 0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e

Ввод данных

•Установить курсор в нужную ячейку.

•Набрать данные.

•Завершить ввод клавишей Enter (переводит курсор вниз) или Tab

(переводит курсор вправо).

Правка содержимого ячейки Способ 1.

•Дважды щелкнуть мышью по ячейке (появится текстовый курсор |).

•Изменить содержимое ячейки.

Для сохранения изменений нажать Enter. Для отмены изменений – Esc.

Способ 2.

•Выделить ячейку.

•Щелкнуть по строке формул (появится текстовый курсор |).

•Изменить содержимое ячейки.

Для сохранения изменений нажать Enter. Для отмены изменений – Esc.

Очистка ячейки, строк и столбцов

•Выделить ячейку, строку или столбец.

•На вкладке Главная в разделе Редактирование щелкнуть список Очистить

•Выбрать нужную команду: Все, Форматы, Содержимое или Примечания.

Замечание 1. При нажатии клавиши Delete уничтожается только содержимое ячейки, строки или столбца.

Замечание 2. Если ячейка очищена, ее значение равно нулю, и формула, ссылающаяся на эту ячейку, получит нулевое значение в качестве аргумента.

76

Выделение ячеек, строк и столбцов
Одну ячейку	Щелкнуть мышью по ячейке или переместить
	курсорную рамку с помощью клавиш управления
	курсором
Диапазон ячеек	Протащить указатель мыши от первой ячейки
	диапазона к последней
Несмежные ячейки	Выделить первую ячейку или первый диапазон ячеек
или несмежные	и при нажатой клавише Ctrl выделить остальные
диапазоны ячеек	ячейки
Большой диапазон	Щелкнуть мышью по первой ячейке диапазона и при
ячеек	нажатой клавише Shift щелкнуть последнюю ячейку
	диапазона
Всю строку	Щелкнуть номер строки
Весь столбец	Щелкнуть имя столбца
Смежные строки	Протащить указатель мыши по заголовкам строк или
или столбцы	столбцов
Несмежные строки	Выделить первую строку или первый столбец и при
или столбцы	нажатой клавише Ctrl выделить остальные строки или
	столбцы
Все ячейки на листе	Щелкнуть кнопку в верхнем левом углу листа над
	номером первой строки

Изменение формата данных

•Выделить ячейку или диапазон.

•На вкладке Главная раскрыть список раздела Число.

•На вкладке Число выбрать нужный формат.

Заполнение ячеек с помощью прогрессии

•В ячейке набрать первое значение ряда.

•Выделить заполняемый диапазон, начиная с этой ячейки.

•На вкладке Главная в разделе Редактирование раскрыть список

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

•В окне диалога Прогрессия

o включить радиокнопки нужного типа прогрессии и способа расположения данных на листе;

o указать шаг.

Замечание. Предельное значение ряда задается, если не выделяется диапазон заполнения.

77

ЗАДАНИЕ 2. ФОРМАТИРОВАНИЕ ТАБЛИЦЫ

НАЧАЛЬНЫЙ УРОВЕНЬ

1.В таблице из Задания 1 удалить столбцы, в которых отсутствуют данные (номер лабораторной работы, вес, рейтинг защиты).

2.Сохранить книгу

БАЗОВЫЙ УРОВЕНЬ

1.Изменить ширину всех столбцов по максимальной ширине расположенных

вних данных.

2.Сохранить книгу

ПОВЫШЕННЫЙ УРОВЕНЬ

1.Отформатировать ячейки первого столбца, установив перенос внутри ячеек по словам.

2.Сохранить книгу

СПРАВОЧНЫЙ МАТЕРИАЛ

Автоматическое изменение ширины столбца

•Указать мышью на правую границу столбца в линейке имен столбцов

(указатель мыши должен стать черным перекрестьем).

•Дважды щелкнуть левой кнопкой мыши.

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

Перенос внутри ячейки по словам

•На вкладке Главная раскрыть список раздела Число.

•На вкладке Выравнивание установить флажок

; Переносить по словам.

Рекомендация. Для того, чтобы при вводе данных начать в ячейке новую строку, надо нажать комбинацию Alt+Enter.

Вставка строки

•Выделить строку, на месте которой должна быть новая (пустая) строка.

•На вкладке Главная в разделе Ячейки щелкнуть кнопку Вставить.

Вставка столбца

•Выделить столбец, на месте которого должен быть новый (пустой) столбец.

•На вкладке Главная в разделе Ячейки щелкнуть кнопку Вставить.

Удаление строки/столбца

•Выделить строку/столбец.

•На вкладке Главная в разделе Ячейки щелкнуть кнопку Удалить.

78

ЗАДАНИЕ 3. ВЫЧИСЛЕНИЕ ПО ФОРМУЛАМ

НАЧАЛЬНЫЙ УРОВЕНЬ

1.В таблице из Задания 2 рассчитать взвешенный рейтинг для первой лабораторной работы по формуле:

Взвешенный рейтинг = Рейтинг защиты * Вес лабораторной работы.

2.Сохранить книгу.

БАЗОВЫЙ УРОВЕНЬ

1.Скопировать формулу взвешенного рейтинга на все остальные ячейки этой строки таблицы.

2.В ячейке А6 посчитать сумму всех весов.

2.Сохранить книгу.

ПОВЫШЕННЫЙ УРОВЕНЬ

1.В ячейках А7, А8, А9 посчитать промежуточные рейтинги для периодов

с1-й по 7-ю недели, с 8-й по 16-ю недели и общий рейтинг за весь семестр.

Рейтинг периода = Сумма взвешенных рейтингов периода / Сумма весов периода.

2.Сохранить книгу.

СПРАВОЧНЫЙ МАТЕРИАЛ

Правила создания формул Правило 1. При создании формулы курсорная рамка должна находиться в

пустой ячейке.

Правило 2. Формула начинается со знака «равно» (=). Применяются знаки арифметических операций: «+» – сложение, «–» – вычитание, «*» – умножение, «/» – деление, «^» – степень и круглые скобки ( ) для задания приоритета операций.

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

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

	А		В			С
1	10			3	=a1+b1		Правильная формула
2						=10+3	Неправильная формула

Правило 4. Адреса ячеек можно набирать латинскими буквами или щелкать

мышкой по нужным ячейкам для автоматического получения адреса.

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

79

Пример 2. Пусть дан числовой столбец Количество товаров на складе. Надо увеличить запасы каждого товара в 3 раза.

	А		В		С		D
1	Наименование		Количество		3		Увеличенный
	товаров		товаров				запас товаров
			на складе
2	Товар 1	15					=B2*$C$1
3	Товар 2	20
4	Товар 3	50
5	Товар 4	3

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

Пример 3. Чтобы в примере 2 получить формулы в ячейках с D3 по D5, надо выделить ячейку D2 курсором, указать мышкой на маркер автозаполнения и при нажатой левой кнопке мыши выделить диапазон ячеек D3:D5.

1

2

3

4

5

А	В		С		D
Наименование	Количество		3		Увеличенный
товаров	товаров				запас товаров
	на складе
Товар 1	15			=B2*$C$1
Товар 2	20			=B3*$C$1
Товар 3	50			=B4*$C$1
Товар 4	3			=B5*$C$1

Изменение типа ссылки

•Выделить ячейку с формулой.

•В строке формул щелкнуть по изменяемой ссылке.

•Нажимать клавишу F4, прокручивая варианты адресных ссылок, до получения нужного формата.

Замечание. Наиболее часто встречаются ошибки:

#ИМЯ? – в формуле есть адрес ячейки, набранный русскими буквами Для устранения ошибки надо набрать адрес в латинском регистр).

#ЗНАЧ! – в формуле есть ссылка на ячейку с текстом. Для устранения ошибки надо исправить некорректный адрес.

#ДЕЛ0! – в формуле используется деление на пустую ячейку, либо на ячейку с нулевым значением. Для устранения ошибки надо изменить содержимое ячейки.

80

ЗАДАНИЕ 4. ОТНОСИТЕЛЬНОЕ И АБСОЛЮТНОЕ КОПИРОВАНИЕ

На Листе 1 новой книги выполнить различные виды копирования.

НАЧАЛЬНЫЙ УРОВЕНЬ Копирование ячейки на диапазон

Требуется скопировать содержимое ячейки А1 на диапазон С1:D2.

									1.	Ввести в ячейку А1 значение 10.
		А		В		С		D
									2.	Установить курсор в копируемую ячейку А1.
	1	10			10		10
									3.	Скопировать в буфер.
	2				10		10
									4.	Выделить диапазон ячеек для заполнения С1:D2.
5.		Вставить из буфера.

Копирование диапазона ячеек

Требуется скопировать содержимое диапазона A1:B2 в ячейки, начиная с D1.

		А		В		С		D		E		F	1.	В ячейки А1, А2, В1, В2 ввести значение 10.
													2.	Выделить копируемый диапазон ячеек A1:B2.
	1	10	10				10		10
													3.	Скопировать в буфер.
	2	10	10				10		10
													4.	Установить курсор в начальную ячейку D1

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

Сохранить книгу.

БАЗОВЫЙ УРОВЕНЬ Относительное копирование формулы по вертикали

1. Ввести значение в исходную ячейку А1.

				А				Комментарий	2.	Установить курсор в ячейку А2, где должен
	1			10					быть получен результат расчета по формуле.
	2			15				=А1+5
									3.	Ввести расчетную формулу: = А1+5.
	3			20				=А2+5
									4.	Установить курсор в ячейку А2.
	4			25				=А3+5	5.	Установить указатель мыши на маркер

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

Замечание. При относительном копировании формулы по вертикали в адресах автоматически изменяется номер строки в зависимости от нового расположения формулы в таблице. Имя столбца остается неизменным.

Относительное копирование формулы по горизонтали
									1. Ввести	значение	в
		А		В		С		D
									исходную ячейку А1.
	1	10	15		20		25
									2. Установить курсор		в
		Комментарий	=A1+5		=B1+5			=C1+5	ячейку В1, где должен быть
получен результат расчета по формуле.
3.		Ввести расчетную формулу: = А1+5.

81

4.Установить курсор в ячейку В1.

5.Установить указатель мыши на маркер автозаполнения и при нажатой левой кнопке мыши протянуть выделение на нужное количество столбцов текущей строки 1.

Замечание. При относительном копировании формулы по горизонтали в адресах автоматически изменяется имя столбца в зависимости от нового расположения формулы в таблице. Номер строки остается неизменным.

Сохранить книгу.

ПОВЫШЕННЫЙ УРОВЕНЬ Абсолютное копирование формулы по вертикали

Пусть надо скопировать точную формулу из ячейки А2, в ячейки A3, А4 и А5 так, чтобы она оставалась по-прежнему = А1+5. Такая копия будет являться абсолютной копией содержимого ячейки А2 по столбцу.

		A		Комментарий	1.	Ввести значение в исходную ячейку А1.
					2.	В ячейку А2	ввести расчетную		формулу: =
	1	10
					А$1+5.
	2	15		=A$l+5
					3.	Установить	указатель	мыши	на	маркер
	3	15		=А$1+5
					автозаполнения и при нажатой левой кнопке мыши
	4	15		=A$l+5
					протянуть выделение на			нужное	число	строк
	5	15		=A$l+5
					текущего столбца А.

Абсолютное копирование формулы по горизонтали

Пусть надо скопировать точную формулу из ячейки В1 в ячейки С1 и D1 так, чтобы она оставалась по-прежнему = А1+5. Такая копия будет являться абсолютной копией содержимого в ячейке В1 по строке.

1. Ввести значение в исходную ячейку А1.

									2.	В ячейку	B1 ввести
		А		В		С		D
									расчетную		формулу:
	1	10	15		15		15
									= $А1+5.
			=$A1+5
		Комментарий				=$A1+5		=$A1+5	3.	Установить указатель

мыши на маркер автозаполнения и при нажатой левой кнопке мыши протянуть выделение на нужное количество столбцов текущей строки 1.

Абсолютное копирование формулы в любом направлении

Пусть надо скопировать точную формулу из ячейки В1 на диапазон В1:С4 так, чтобы она оставалась по-прежнему = А1+5. Такая копия будет являться абсолютной копией содержимого ячейки В1 по строке и столбцу.

							1.	Ввести значение в исходную
		A	В	С
							ячейку А1.
	1	10	15	15		копирование
							2.	В ячейку В1 ввести расчетную
	2		15	15		формулы = $A$1+5
							формулу: = $А$1+5.
	3		15	15		дает точную
							3.	Скопировать формулу из
	4		15	15		формулу = A1+5 в
							ячейки В1 на диапазон В1:С4.
						обоих столбцах
							4.	Сохранить книгу.

82

СПРАВОЧНЫЙ МАТЕРИАЛ

Ссылка – адрес ячейки (А1), номер строки (1) или имя столбца (А).

Абсолютная ссылка – точный адрес ячейки ($А$1).

Относительная ссылка – расположение ячейки относительно других окружающих ее ячеек (А1).

Смешанная ссылка содержит абсолютные и относительные адреса

(А$1, $А1).

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

Замечание. Технологию копирования по вертикали и горизонтали с использованием маркера автозаполнения можно применять не только для формул, но и для простых данных (текстовых и числовых).

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

•скопировать содержимое ячейки в буфер;

•установить курсор в ячейку, где нужна копия;

•в меню щелкнуть Правка, Специальная вставка;

•установить переключатель в положение Значение.

ЗАДАНИЕ 5. ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ

В книге из Задания 3 выполнить обработку данных с использованием встроенных функций.

НАЧАЛЬНЫЙ УРОВЕНЬ

1.Определить минимальное значение веса (функция МИН).

2.Найти максимальное значение рейтинга защиты (функция МАКС).

3.Найти среднее значение рейтинга защиты по всем лабораторным работам (функция СРЗНАЧ).

4.Сохранить книгу

БАЗОВЫЙ УРОВЕНЬ

1.Определить, сколько лабораторных работ защищено на отлично (функция СЧЕТЕСЛИ).

2.Определить сумму одинаковых весов по выбору (функция СУММЕСЛИ).

3.Сохранить книгу.

ПОВЫШЕННЫЙ УРОВЕНЬ

1.Применить дифференцированную надбавку к рейтингу защиты: если рейтинг в диапазоне 50-74 балла, то надбавка 3%; если свыше 74, то надбавка 5% (функция ЕСЛИ).

2.Сохранить книгу.

83

СПРАВОЧНЫЙ МАТЕРИАЛ

Вызов мастера функций

• На вкладке Главная в разделе Редактирование раскрыть список кнопки со значком суммы (∑) и выбрать Другие функции

• Либо в начале строки формул щелкнуть кнопку Вставка функции .

Замечание. Функции МАКС, МИН, СРЗНАЧ относятся к статистическим функциям, ЕСЛИ – логическая функция.

ЗАДАНИЕ 6. ПОСТРОЕНИЕ И ФОРМАТИРОВАНИЕ ДИАГРАММ

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

НАЧАЛЬНЫЙ УРОВЕНЬ

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

2.Добавить подписи на диаграмму.

3.Произвольно изменить исходные данные в строке Рейтинг защиты. Посмотреть изменения на диаграмме.

4.Сохранить книгу.

БАЗОВЫЙ УРОВЕНЬ

1.Изменить тип диаграммы на гистограмму.

2.Изменить фон области диаграммы.

3.Сохранить книгу.

ПОВЫШЕННЫЙ УРОВЕНЬ

1.Построить график по данным строки Взвешенный рейтинг. Разместить график на отдельном листе.

2.Изменить цвет и толщину линии графика.

3.Добавить/удалить легенду.

4.Сохранить книгу.

СПРАВОЧНЫЙ МАТЕРИАЛ

Вставка диаграммы

• На вкладке Главная в разделе Диаграммы выбрать нужный тип диаграммы.

Изменение типа диаграммы

•Выделить диаграмму.

•На вкладке Работа с диаграммами, Конструктор в разделе Тип выбрать Изменить тип диаграммы.

Форматирование диаграммы

•Щелкнуть правой кнопкой мыши по изменяемому элементу диаграммы (область диаграммы, легенда, ряд данных и т.п.)

•В контекстном меню выбрать команду Формат для данного элемента.

84

БЛОК ВАРИАТИВНЫХ ЗАДАНИЙ

ЗАДАНИЕ 1. СОЗДАНИЕ РЯДОВ

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

Создание рядов дат и времени

1.В ячейках столбца А набрать начальные значения:

9:00

понедельник

январь янв, апр

2.Продолжить ряды (скопировать) вправо.

Создание текстовых рядов с цифрой на конце

1.В ячейках столбца А набрать начальные значения:

Товар 1, Заказ: Цех №001

2.Продолжить ряды (скопировать) вправо.

Создание числовых рядов с заданным шагом

1.В ячейках столбца А набрать 2 начальных значения ряда арифметической прогрессии

1, 2 100, 95 -30, -10

2.Продолжить ряды (скопировать) вправо.

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

1. В ячейках столбца А набрать начальные значения ряда геометрической прогрессии. В диалоге Прогрессия указать шаг и предельное значение.

Начальное значение	Шаг	Предельное значение
2	2	200
2	3	450

СПРАВОЧНЫЙ МАТЕРИАЛ

В рядах дат и времени используются

•приращения по дням, неделям или месяцам;

•стандартные последовательности, например, дни недели, названия месяцев или кварталы.

Рекомендация. Для создания рядов с произвольным шагом (например, ряд нечетных чисел 1 3 5 7 …) надо в соседних ячейках набрать первые два значения ряда. Затем выделить обе ячейки. После чего указать на маркер автозаполнения и при нажатой кнопке мыши выделить нужный диапазон для заполнения.

85

ЗАДАНИЕ 2. СОЗДАНИЕ ФОРМУЛ С ВЛОЖЕННЫМИ ФУНКЦИЯМИ

1. В новой книге создать табличный документ по образцу.

Сводная экзаменационная ведомость

	A	B	C	D
1	Фамилия	Экзамен 1	Экзамен 2	Экзамен 3
2	Сдудент 1	4	4	4
3	Сдудент 2	5	5	5
4	Сдудент 3	3	3	4
5	Сдудент 4	5	5	4
6	Сдудент 5	3	3	3

2. Если у Студента 4 нет троек, то выдать сообщение, что он "молодец", иначе выдать сообщение "3!".

Вариант-1 (вложенная функция И):

=ЕСЛИ(И(В5>3;С5>3;D5>3);"молодец";"3!")

Вариант-2 (вложенная функция СЧЕТЕСЛИ):

=ЕСЛИ(СЧЁТЕСЛИ(B5:D5;3)=0;"молодец";"3!")

3. Если Студент 2 имеет все пятерки, то вывести его фамилию.

Вариант-1 (вложенная функция СУММ)

=ЕСЛИ(СУММ(B3:D3)=20;A3;" ")

Вариант-2 (вложенная функция СЧЕТЕСЛИ)

=ЕСЛИ(СЧЁТЕСЛИ(B3:D3;5)=4;A3;" ")

ЗАДАНИЕ 3. ФИЛЬТРАЦИЯ ДАННЫХ

1. В новой книге создать табличный документ по образцу.

Сводная экзаменационная ведомость

	A	B	C	D
1	Фамилия	Экзамен 1	Экзамен 2	Экзамен 3
2
3
4
5
6

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

Втаблице должно быть 5 записей (строк).

Взаписях должны быть повторяющиеся данные. Оценки за экзамен – в рейтинговой шкале 0-100 баллов.

Например, два однофамильца Ивановых или Петров и Сидоров, имеющие итоговый рейтинг 85 баллов за первый экзамен.

3.Получить список отличников за первый экзамен с помощью Автофильтра.

86

СПРАВОЧНЫЙ МАТЕРИАЛ

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

Установка автофильтра

•Выделить любую ячейку в фильтруемом списке.

•В меню щелкнуть Данные, Фильтр, Автофильтр.

Врезультате Microsoft Excel выведет кнопки со стрелками u справа от каждого заголовка столбца. Щелчок по такой кнопке раскрывает список значений, которые могут быть использованы для задания условий отбора строк.

Фильтрация строк, содержащих определенное значение

•Щелкнуть кнопку u в заголовке столбца, в котором содержатся искомые данные.

•Выбрать нужное значение в списке.

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

Замечание 1. Фильтрация отображает только те строки, которые удовлетворяют условиям отбора.

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

Снятие фильтра с конкретного столбца

•Раскрыть список столбца.

•Выбрать в списке Все.

Снятие фильтра со всех столбцов

•Выделить любую ячейку списка.

•В меню щелкнуть Данные, Фильтр, Показать все.

Отмена режима фильтрации

•Выделить любую ячейку списка.

•В меню щелкнуть Данные, Фильтр, Автофильтр.

ЗАДАНИЕ 4. СОРТИРОВКА ДАННЫХ

1. В новой книге создать табличный документ по образцу.

	A	B	C	D
1	Наименование	Производитель	Дата	Количество
	товара		поставки	(шт)
2	Кирпич	ООО «Камень»	12.06.2012	30000
3	Плитка	ЧП Иванов И. И.	10.07.2012	500
4	Шифер	ОАО «Кровля»	25.05.2012	25
5	Панели	ООО «Стройвест»	17.03.2012	15
6	Блоки	ЗЖБИ-2	30.06.2012	5000

87

2. Определить, по какому столбцу выполнена сортировка, и воспроизвести ее.

А)	A	B	C	D
1	Наименование	Производитель	Дата	Количество
	товара		поставки	(шт)
2	Шифер	ОАО «Кровля»	25.05.2012	25
3	Плитка	ЧП Иванов И. И.	10.07.2012	500
4	Панели	ООО «Стройвест»	17.03.2012	15
5	Кирпич	ООО «Камень»	12.06.2012	30000
6	Блоки	ЗЖБИ-2	30.06.2012	5000

Б)	A	B	C	D
1	Наименование	Производитель	Дата	Количество
	товара		поставки	(шт)
2	Шифер	ОАО «Кровля»	25.05.2012	25
3	Панели	ООО «Стройвест»	17.03.2012	15
4	Кирпич	ООО «Камень»	12.06.2012	30000
5	Плитка	ЧП Иванов И. И.	10.07.2012	500
6	Блоки	ЗЖБИ-2	30.06.2012	5000

В)	A	B	C	D
1	Наименование	Производитель	Дата	Количество
	товара		поставки	(шт)
2	Блоки	ЗЖБИ-2	30.06.2012	5000
3	Кирпич	ООО «Камень»	12.06.2012	30000
4	Панели	ООО «Стройвест»	17.03.2012	15
5	Плитка	ЧП Иванов И. И.	10.07.2012	500
6	Шифер	ОАО «Кровля»	25.05.2012	25

СПРАВОЧНЫЙ МАТЕРИАЛ

Сортировка списка

•Выделить любую ячейку в списке.

•В меню щелкнуть Данные, Сортировка.

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

Список Затем по позволяет выбрать столбец для вторичной сортировки. Список В последнюю очередь позволяет указать третий ключ для сортировки. Например, список сотрудников может содержать однофамильцев Андреев Олег и Андреев Андрей. Если первичная сортировка выполняется по полю Фамилия в порядке возрастания (то есть по алфавиту от А до Я), то очередность записей зависит от второго ключа Имя. Если выбрать это поле в списке Затем по и установить переключатель в положение по возрастанию, то первым в списке будет Андреев Андрей. А если переключатель установлен по убыванию, то первым будет Андреев Олег.

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

88

ЗАДАНИЕ 5. РАБОТА С ЛИСТАМИ

1.В новой книге переименовать Лист 1. Новое имя листа Ведомость

2.Добавить новый лист в книгу.

3.Переместить лист Ведомость в конец книги.

4.Изменить цвет ярлычка листа Ведомость.

5.Удалить за один прием все пустые листы.

СПРАВОЧНЫЙ МАТЕРИАЛ

Перемещение или копирование листов в другую книгу

•Выбрать листы, которые следует переместить или скопировать.

•В контекстном меню выбрать команду Переместить/Скопировать. Для перемещения листов

•в списке Переместить выбранные листы в книгу выбрать нужное значение.

Для копирования листов

•установить флажок Создавать копию.

Перемещение или копирование листов в книге

•Выбрать лист, который следует переместить или скопировать. Для перемещения листов

•перетащить мышью ярлычки листов на новое место.

Для копирования листов

•при нажатой клавише Ctrl перетащить мышью ярлычки листов на новое место;

•отпустить кнопку мыши, затем клавишу Ctrl.

Вставка отдельного листа

•Щелкнуть лист, слева от которого надо вставить новый.

•В меню щелкнуть Вставка, Лист.

Рекомендация. Чтобы вставить несколько листов, надо выбрать необходимое количество листов, удерживая нажатой клавишу Shift.

Переименование листа

•Установить указатель на ярлычок листа, который следует переименовать, и дважды щелкнуть мышью.

•Ввести новое название в поле Имя листа.

Удаление листа

•Щелкнуть ярлычок листа, который надо удалить.

•В контекстном меню выбрать команду Удалить Лист.

Изменение цвета ярлычка листа

• Щелкнуть правой кнопкой мыши по ярлычку листа и в контекстном меню выбрать Цвет ярлычка…

89

Выбор листов в книге

Отдельный лист	Щелкнуть ярлычок листа
Два смежных листа или	Щелкнуть ярлычок первого листа и при
более	нажатой клавише Shift щелкнуть ярлычок
	последнего листа
Два несмежных листа или	Щелкнуть ярлычок первого листа и при
более	нажатой клавише Ctrl щелкать ярлычки
	остальных листов
Все листы книги	Щелкнуть правой кнопкой мыши по ярлыку
	любого листа и в контекстном меню выбрать
	команду Выделить все листы

Перемещение и прокрутка внутри листа

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

Чтобы перейти				Сделайте следующее
На одну	строку	вверх	На вертикальной полосе прокрутки щелкнуть
или вниз			кнопку 5 или 6
На один столбец влево			На горизонтальной полосе прокрутки щелкнуть
или вправо			кнопку 3 или 4
На один	экран	вверх	Щелкнуть	вертикальную	полосу	прокрутки
или вниз			выше или ниже бегунка
На один	экран	влево	Щелкнуть	горизонтальную	полосу	прокрутки
или вправо			слева или справа от бегунка
На большое расстояние			Перетащить бегунок мышью на необходимую
			позицию

90