Лабораторная №5. Автозаполнение. Автозавершение. Работа со списками. Создание пользовательских списков. Использование имен ячеек в формулах.

Цель работы: Ознакомиться с особенностями работы с именами диапазонов ячеек; получить навыки автоматизации заполнения однотипными данными электронные таблицы, получить навыки составления списков, использования имен ячеек в расчетах.

Для ввода повторяющихся или однотипных данных, электронная таблица MS Excel предлагает следующие средства для автоматизации ввода: автозаполнение числами и автозаполнение формулами. Этот инструмент MS Excel позволяет автоматизировать пользователю ввод списков, часто повторяющихся фрагментов текста, поэтому такие списки называются пользовательскими списками.

1. Автозавершение применяется для автоматизации ввода текстовых данных. В ходе ввода текста в очередную ячейку программа проверяет соответствие введенных символов строкам, имеющимся в этом столбце выше. Если обнаружено однозначное совпадение, введенный текст автоматически дополняется. Нажатие клавиши Enter подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.

2. Автозаполнение числами используется при работе с числами. В правом нижнем углу рамки текущей ячейки имеется черный квадратик – это маркер заполнения. При наведении на него указатель мыши приобретает форму тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция «размножения» содержимого ячейки в горизонтальном или вертикальном направлении. Чтобы точно сформулировать условия заполнения ячеек следует воспользоваться командой Правка–Заполнить–Прогрессия. В открывшемся диалоговом окне выбирается тип прогрессии, шаг и предельное значение.

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

ЗАДАНИЕ 1.

1. В ячейку А1 впишите название таблицы

2. Составьте проект торгового бюджета фирмы «Алатау», для этого:

3. Присвойте рабочему листу Лист1 имя «Бюджет». Прежде чем заполнить названия столбцов и строк, создайте новый список данных:

а) Выберите команды: Сервис, Параметры, вкладка Списки;

б) Щелкните в область Элементы списка в окне Параметры и наберите элементы списка, завершая ввод каждого элемента нажатием на Enter:

Приход

Затраты на товары

Полная выручка

Статьи расходов

Реклама

Аренда помещений

Налоги и выплаты

Проценты по кредитам

Расходы Всего

Прибыль

в) щелкните на опции Добавить, затем ОК.

4. Введите названия месяцев с помощью Автозаполнения, для этого введите название первого месяца списка, выделите эту ячейку и «протяните» мышью вниз.

5. В соответствующей ячейке, предположительно В9, наберите Приход и нажмите кла­вишу ЕNTER.

6. Снова выделите эту ячейку и «протяните» маркер заполнения вниз на девять строк к ячейке, так как Ваш список состоит из 10 элементов.

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

8. Для прибыли (С19) введите формулу =С10-С18 (разность значений строк Приход и Расходы всего) и скопируйте эту формулу в остальные ячейки строки Прибыль

9. Введите текущую дату в ячейку В3: =СЕГОДНЯ( )

10. В ячейке К8 наберите Среднее, а в К10 определите средние затраты на товары: =СРЗНАЧ(С11:Н11)

11. Создайте имена для строк таблицы, выбрав в качестве имен названия столбцов и строк

12. В ячейке L8 наберите Максимальное значение, а в ячейке М8 – Минимальное значение. Найдите максимальное и минимальное значения для ряда данных Затраты на товары, используя имена блоков в со­ответствующих формулах. При корректном присвоении имен и использовании функции из библиотеки Мастер функций, Вы должны получить следующие формулы =МАКС(Затраты_на_товары), =МИН(Затраты_на_товары) (обратите внимание на то, что пробелы в именах ячеек заменены нижним подчёркиванием)

13. Измените формат ячеек. Для этого используйте команду Формат - Ячейки, вкладка Число, выбрать числовой формат – (все форматы), для типа ввести # ## 00“тыс.тг’’

14. В ячейку В4 вставьте текущу дату, используя функцию Сегодня ()

15. Попробуйте вычислить сумму значений строк в столбце Всего, обратите внимание на то, что это не корректная операция после присвоения имен ячейкам.

Таблица 21 – Торговый Бюджет: финансовый год

Фирма алатау	Торговый Бюджет
Составил
Дата
	Темпы роста
	Рост объема продаж	1,50%
	Удорожание товаров	0,9
Отчет		Март	Апрель	Май	Июнь	Июль	Август	Всего
	Приход	32550	33038	33534	34037	34547	35066
	Затраты на товары	19316	19490	19665	19842	20021	20201
	Полная выручка	13234	13548	13869	14195	14526	14866
	Статьи расходов:
	Реклама	4000	4000	4000	4000	4000	4000
	Аренда помещений	500	500	500	500	500	500
	Налоги и выплаты	240	241	242	243	244	245
	Проценты по кредитам	800	807	814	821	828	835
	Расходы всего
	Прибыль

ЗАДАНИЕ 2. Построение и редактирование диаграммы (Рисунок 2).

Постройте столбиковую диаграмму для проекта торгового бюджета фирмы алатау

• Добавьте данные ряда Прибыль

• удалите ряд данных Полная выручка

Рисунок 2

ЗАДАНИЕ 3.

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

2. Введите исходные данные. Графу «Период» заполните, используя операцию Автозаполнения. Далее между каждыми тремя месяцами вставьте две пустые строки.

3. Отформатируйте таблицу в соответствии с приведенным образцом

4. Расчёты в ячейках, в которых стоит знак «Х», нельзя проводить

5. Для организации вычислений в пустых ячейках создайте и определите имена для диапазонов ячеек:

4.1. Выделите ячейки с объемами за первые три месяца (В4:В6) и, используя команду Вставка - Имя - Присвоить, задайте для выделенного диапазона имя «Квартал_1». Определите соответствующие имена для других диапазонов ячеек.

4.2. Для ячеек с итоговыми значениями объемов создайте имена, используя информацию из первого столбца таблицы. Присвойте имена для строк «Итого за 1 квартал» и «В среднем за 1 квартал» во всех кварталах (А7:В7, А8:В8, А12:В12, A13:B13, А17:В17, A18:B18, А22:В22, A23:B23), командой Вставка – Имя - Создать

5. Введите формулы. В ячейках, где стоит знак «Х» расчеты не производить. Имена диапазонов ячеек, используемые в качестве аргументов, задайте командой Вставка – Имя - Вставить.

5.1.В ячейку В7 введите формулу =СУММ(Квартал_1), в В8: =СРЗНАЧ(Квартал_1).

5.2.Введите аналогичные формулы для других кварталов.

5.3. В ячейку С8 введите формулу =СУММ(С4:С6)/СЧЕТ(С4:С6). Затем скопируйте ее в буфер и вставьте в соответствующие ячейки во всех остальных кварталах.

5.4. Для определения значений итоговых строк введите формулы:

в ячейку В24: =СУММ(Итого_за_1_квартал; Итого_за_2_квартал; Итого_за_3_квартал; Итого_за_4_квартал),

в В25: =СРЗНАЧ(В_среднем_за_1_квартал; В_среднем_за_2_квартал; В_среднем_за_3_квартал; В_среднем_за_4_квартал),

в С25: =СРЗНАЧ(С8;С13;С18;С23).

6. В ячейку А27 введите текст «Минимальная цена», а в С27 формулу: =МИН(С4:С6; С9:С11; С14:С16; С19:С21).

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

8. Используя возможности изменения параметров электронной таблицы, отобразите расчётные формулы в ячейках

9. Установите собственный формат для таблицы:

9.1. Выделите всю таблицу

9.2. Выберите цвет, отобразите границы таблицы

9.3. Разместите название таблицы над всей таблицей, объединив соответствующие ячейки

9.3. Отменив выделение всей таблицы, выделите жирным шрифтом названия столбцов, название таблицы, разместите их по центру

Таблица 22 – Объемы и цены реализации молока в Акмолинской области

Период	Объем, т	Цена реализации
Январь	568	17,8
Февраль	709	18,1
Март	1105	17,5
Итого за 1 квартал	?	Х
В среднем за 1 квартал	?	?
Апрель	1236	17,1
Май	1574	16,8
Июнь	3922	14,9
Итого за 2 квартал	?	Х
В среднем за 2 квартал	?	?
Июль	2416	15
Август	3566	15
Сентябрь	2071	15,4
Итого за 3 квартал	?	Х
В среднем за 3 квартал	?	?
Октябрь	1782	16
Ноябрь	1262	16,5
Декабрь	1504	16,7
Итого за 4 квартал	?	Х
В среднем за 4 квартал	?	?
Всего	?	Х
В среднем по кварталам	?	?