I.Подготовьте таблицу по образцу, поместив ее в ячейках a1 – e3.

В ячейке В3 находится цена за единицу товара.

В ячейке C3 - формула =B3 * C2 (цена за единицу товара умножить на количество).

	A	B	C	D	E
1	Наименование товара	a)Количество
2		1	2	3	4
3	Мороженное	12,70	=B3 * C2

С помощью маркера заполнения распространите формулу вправо для получения стоимости товара за 3 и 4 единицы. Сравните свой результат с приведенным ниже.

	A	B	C	D	E
1	Наименование товара	b)Количество
2		1	2	3	4
3	Мороженное	12,70	25,40	76,20	304,80

Можно заметить, что вычисленная по формуле стоимость товара за три единицы неверна. Если выделить ячейку D3, то в Строке формул появится формула C3*D2, а должна быть формула B3*D2.

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

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

Абсолютные ссылки имеют вид: $F$9; $C$45. Для фиксации координат применяется знак $.

Следовательно, для того, чтобы получить верные результаты в нашем примере, в ячейке C3 - формула =$B$3 * C2.

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

Абсолютные ссылки имеют вид: $А$5, $F$5, $G$3 и т.п.

Примечание

Для того, чтобы относительную ссылку преобразовать в абсолютную, достаточно после ввода ссылки нажать клавишу F4 – и знаки доллара появятся автоматически.

Задание 3

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

(1)Оплата коммунальных услуг задержана на				дней
Вид оплаты	Начисленная сумма	Пени	Всего к оплате
(2)Квартплата
Газ
Электричество
Телефон

Пени высчитывается по формуле – 1% от начисленной суммы за каждый задержанный день.

Всего к оплате считается как сумма начисления плюс пени.

Добавьте в таблицу строку для подсчета итоговых показателей:

всего начислено, всего пени, всего к оплате.

Задание 4

1. Выполните ввод исходных данных по образцу.

2. Введите соответствующие смыслу формулы в ячейки E4 и F4 (для вычисления налога используйте абсолютную ссылку В$11).

3. Скопируйте вниз формулы: из ячейки E4 в блок ячеек E5:E9; из ячейки F4 в блок ячеек F5:F9 (по стрелке).

4. Введите по смыслу формулы в ячейки F13, E16, E19.

5. Отформатируйте числа в столбце F, оставив две цифры в дроби. (Формат, Ячейки, Числовой …)

6. Постройте диаграмму на отдельном листе для блока F4:F9.

7. Выполните сортировку данных по столбцу F.

	A	B	C	D	E	F
1	Прибыль по предприятиям
2	Название предприятия	Доход (р.)				Налог (р.)
3		апрель	май	июнь	квартал	за квартал
4	ОТЗ	15000	12000	8400
5	Петрозаводскмаш	24446	19800	15500
6	Северянка	6380	4700	5200
7	БОП	38000	34900	28000
8	Славмо	14580	13800	15670
9	Хлебозавод	12300	11700	13400
10
11	Налог (в %)	13
12	Сумма налогов за квартал (р.)
13
14
15	Максимальный до- ход за квартал (р.)
16
17
18	Средний доход за квартал (р.)
19

Задание 5.

Создайте ЭТ “Доставка груза” по образцу:

	A	B	C	D		E		F
1	НАРЯД-ЗАДАНИЕ НА ДОСТАВКУ ГРУЗА
2	Номер заявки	Наименование груза	Стоимость груза (р.)		Расстояние км		N этажа	Доставка р.
3
4	1	Диван	4300		3		1
5	2	Книжный шкаф	3750		6		4
6	3	Холодильник	8500		10		5
7	4	Мягкая мебель	13200		5		8
8	5	Стол	1500		5		8
9	ВСЕГО
10	Наценка (%) зависит от стоимости груза				5
11	Наценка (р./км) зависит от расстояния				3
12	Наценка (р./этаж) зависит от N этажа				2,5

Пояснение.

Формула в ячейке F4 вводится с учетом того, что стоимость доставки груза складывается из следующих трех величин (наценок):

а) 5% от стоимости груза,

б) от расстояния (3 р./км), в) номера этажа (2,5 р./этаж).

Задание 6

Получите таблицу умножения (см. образец).

Примечание. Для первой строки и столбца А используйте Заполнение рядов чисел. Формулу, введенную в ячейку В2, скопируйте вначале вниз по столбцу (по стрелке),  а затем, выделив этот столбец, скопируйте формулу из ячеек этого столбца вправо.

Таблица умножения

	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	7	8	9
2	1	1	2	3	4	5	6	7	8	9
3	2	2	4	6	8	10	12	14	16	18
4	3	3	6	9	12	15	18	21	24	27
5	4	4	8	12	16	20	24	28	32	36
6	5	5	10	15	20	25	30	35	40	45
7	6	6	12	18	24	30	36	42	48	54
8	7	7	14	21	28	35	42	49	56	63
9	8	8	16	24	32	40	48	56	64	72
10	9	9	18	27	36	45	54	63	72	81

В ячейку В2 следует ввести формулу: =$A2*B$1

Из справочной системы MS Excel 2000

Заполнение рядов чисел, дат или других элементов

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

2. Выделите ячейку или ячейки, содержащие начальные значения.

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

Копирование данных внутри строки или столбца

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

2. Протащите маркер заполнения по заполняемым ячейкам.

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

Задание 7. Работа с электронной таблицей как с базой данных

1. Заполните таблицу, содержащую информацию о планетах солнечной системы, согласно рис. 1 и сохраните ее под именем work6.xls.

Единицы измерения, используемые в таблице:

- период обращения по орбите, в земных годах; - среднее расстояние от Солнца, млн. км; - экваториальный диаметр, тыс. км; - масса - 1024 кг.

 

Рис. 1

Основные понятия баз данных

Область таблицы А2:F12 можно рассматривать как базу данных. Столбцы A, B, C, D, E, F этой таблицы называются полями, а строки 3-12 называются записями. Область А2:F2 содержит имена полей.

Существуют ограничения, накладываемые на структуру базы данных:

- первый ряд базы данных должен содержать неповторяющиеся имена полей; - остальные ряды базы данных содержат записи, которые не должны быть пустыми рядами; - информация по полям (столбцам) должна быть однородной, т.е. только цифры или только текст.

Основная функция любой базы данных - поиск информации по определенным критериям. С увеличением количества записей поиск определенной информации затрудняется. Ехсеl позволяет упростить этот процесс путем фильтрации данных.

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

Команды ДАННЫЕ, ФИЛЬТР позволяют выделять (фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр АВТОФИЛЬТР, так и через РАСШИРЕННЫЙ - ручной.

Автофильтр

При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных или выделить ее. Затем нужно выполнить команды: ДАННЫЕ, ФИЛЬТР, АВТОФИЛЬТР. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. В появляющемся подменю пункт ВСЕ отключает фильтрацию, а пункт УСЛОВИЕ вызывает диалоговое окно, в котором можно установить параметры фильтрации (рис. 2). Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ.

Рис 2

2. С использованием Автофильтра осуществить поиск планет, начинающихся на букву "С" или букву "Ю" с массой менее 600*10²⁴ кг.

2.1. Выполните команды: Данные, Фильтр, Автофильтр. На полях появились кнопки.

2.2. Нажмите на кнопку на поле Планета. Выберите пункт УСЛОВИЕ.

2.3. В диалоговом окне ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР задайте критерии согласно рис.6.2 и нажмите на кнопку <ОК>.

Проверьте! В базе данных остались планеты: Солнце, Юпитер, Сатурн.

2.4. Нажмите на кнопку на поле Масса . Выберите пункт УСЛОВИЕ.

2.5. В диалоговом окне задайте критерий: Масса < 600.

Проверьте! Остался только Сатурн.

2.6. Выполните команды меню: ДАННЫЕ, ФИЛЬТР, ПОКАЗАТЬ ВСЕ.

3. С использованием Автофильтра самостоятельно:

1) осуществите поиск планет, имеющих экваториальный диаметр менее 50 тыс. км и массу менее 4*10²⁴кг (Меркурий, Марс, Плутон)'

2) осуществите поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3*10²⁴ до 500*10²⁴ кг, а также не более 2 спутников (Венера, Земля, Нептун).