Абсолютная и относительная адресация

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

Основное свойство электронной таблицы: изменение числового значения в ячейке приводит к мгновенному пересчету формул, содержащих ссылку на эту ячейку.

Ссылки могут быть абсолютные, относительные и смешанные.

По умолчанию для указания адресов ячеек создаются относительные ссылки. Это означает, что ссылки на ячейки изменяются при перемещении формулы на новое место. Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула. После копирования формулы относительные ссылки будут указывать на другие ячейки так, чтобы сохранить прежние отношения в соответствии с новым местоположением. Например формула =A3+B3, хранящаяся в ячейке С3, при перемещении в ячейку С4 примет вид: =A4+B4.

В тех случаях, когда ссылки не должны изменяться при копировании формулы, следует использовать абсолютные ссылки. Абсолютные ссылки обозначаются знаком доллара, например $A$4. При вводе абсолютных ссылок удобно использовать клавишу [F4] после ввода ссылки.

Ссылки вида $A4 или A$4 называются смешанными.

Назначение имен ячейкам

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

Присвоение имени выделенной ячейке: Вставка→Имя→Присвоить или щелкнуть по полю имени (левое поле в строке формул).

Вставка имени в формулу производиться при выборе именованной ячейки или по команде Вставка→Имя→Вставить.

Ошибки при вычислении формул

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

Сообщение об ошибке	Описание
#####	ячейка содержит число, дату или время, число знаков которого больше ширины ячейки. Следует увеличить ширину столбца (можно дважды щелкнуть на разделителе столбцов в строке заголовка.
#ИМЯ?	В формуле используется имя, отсутствующее в списке имен (Вставка→Имя→Присвоить). Текстовые константы в формуле должны быть заключены в двойные кавычки.
#ДЕЛ/0!	Попытка деления на ноль. Делитель в формуле ссылается на пустую ячейку.
#ЗНАЧ!	Введена математическая формула, которая ссылается на текстовое значение.
#ССЫЛКА!	Отсутствует диапазон ячеек, на который ссылается формула.
#Н/Д	Нет данных для вычислений.
#ЧИСЛО!	Задан неправильный аргумент функции или значение формулы слишком велико или мало и не может быть представлено на листе.
#ПУСТО!	В формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек. Возможно пропущена запятая при перечислении диапазонов.

Работа с листами

Контекстное меню, связанное с ярлычком любого листа, позволяет:

· добавить новый лист или удалить имеющийся,

· переименовать лист (можно также щелкнуть дважды на ярлычке),

· переместить или скопировать лист в исходную или другую книгу,

· изменить цвет ярлычка.

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

Ознакомьтесь с приведенным выше текстом. Откройте MS Excel и найдите основные элементы окна, ответьте на контрольные вопросы. Выполните задания, внимательно следуя указаниям, не забывайте регулярно сохранять текст работы.

Задание 1 Создание таблицы

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

	A	B	C	D	E	F
	Мониторы. Учет
		Мониторы Samsung	Мониторы ViewSonic	Мониторы LG	Итого, шт
	Склад 1
	Склад 2
	Склад 3
	Склад 4
	Склад 5
	Итого, шт

· Наберите в ячейке А1 текст Мониторы. Учет. Выделите диапазон А1:F1, объедините ячейки и расположите текст в центре (кнопка Объединить и поместить в центре на панели инструментов Форматирование).

· В ячейки A3:A7 введите номера складов методом автозаполнения. Обратите внимание на то, что текст всегда прижимается к левому краю ячейки.

· Введите текст в ячейки В2:E2. Двойной щелчок мыши на линии, разделяющей столбцы в строке заголовка, автоматически установит нужную ширину ячейки. Для создания в ячейке текста, состоящего из нескольких строк, следует при вводе нажать [Alt+Enter].

· Введите числа в диапазон B3:D7. Числа при вводе всегда прижимаются к правому краю ячейки.

· Введите формулы для итоговых расчетов: в ячейку E3 введите =B3+С3+D3 или =СУММ(C3:E3). Для указания адреса ячейки щелкните 1 раз в нужной ячейке. Формулы всегда начинаются со знака =. Выделите ячейку E3, подведите курсор к маркеру заполнения (черный квадрат в правом нижнем углу выделенной ячейки), не отпуская кнопку мыши, протащите курсор вниз до Е7. Ячейки E3:E7 заполнятся вычисленными значениями. При автозаполнении в формулу автоматически подставляются относительные адреса ячеек. Адреса ячеек в формулах определены относительно места расположения формулы.

· В ячейку В8 внесите формулу расчета общего количества, указав адреса ячеек, либо с помощью Автосуммы (кнопка ∑, при этом выделится автоматически нужный столбец). Используя автозаполнение, заполните ячейки С8 и E8.

Задание 2. Построение диаграммы

Следуя указаниям, постройте по полученной таблице диаграмму:

· Выделите диапазон A2:D7. Нажмите кнопку Мастер диаграмм.

· Последовательно выполняя предлагаемые Мастером диаграмм шаги, постройте диаграмму типа Стандартная гистограмма и поместите её на имеющемся листе.

Готовую диаграмму можно изменить или отредактировать с помощью контекстного меню при ее выделении (можно дважды щелкнуть на диаграмме).

Переименуйте Лист 1, дав ему название “Таблица 1”.

Задание 3. Применение абсолютных ссылок. Построение круговой диаграммы

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

	A	B	C	D	E	F	G	H	I	J
	Мониторы. Учет
		Мониторы Samsung	Мониторы ViewSonic	Мониторы LG	Итого, шт	Итого, у.е
	Склад 1							Стоимость мониторов в у.е.
	Склад 2							Samsung	ViewSonic	LG
	Склад 3
	Склад 4
	Склад 5

Итого, шт

 

Цены в условных единицах занесите в ячейки H5:J5.

Формула в ячейке F3: =$H$5*B3+$I$5*C3+$J$5*D3. Адреса ячеек со знаком $ не будут изменяться при автозаполнении. Такая ссылка называется абсолютной, знак $ будет вставлен автоматически, если после ввода адреса ячейки нажать клавишу [F4].

Для построения диаграммы Стоимость мониторов выделите два несмежных диапазона (произведите выделение с нажатой клавишей [Ctrl]) A2:A6 и F2:F6 и вызовите Мастера диаграмм. На шаге 1 выберите тип диаграммы Круговая объёмная. На шаге 3 на вкладке Подписи данных выберите Категория и доля установите галочку в поле Линии выноски, а на вкладке Заголовки очистите поле Добавить легенду.

 

Назовите новый лист "Таблица 2".

Задание 4. Работа со ссылками на смежные листы

Для последней таблицы введите столбец стоимости оборудования в рублях. Курс доллара разместите на новом листе (назовите лист, например, Курс_доллара). Тогда ссылки на курс доллара должны содержать дополнительно номер или имя листа, например, для размера курса доллара, занесенного в ячейку C3 листа Курс_доллара, может быть сделана ссылка: =F2*Курс_доллара!$С$3. Если присвоить ячейке С3 имя, например, курс, то ссылки на номер листа можно не делать, и формула примет вид: =F2*курс.

Задание 5. Действия с датами

Перейдите на новый лист (при необходимости вставьте лист) и дайте ему название "Дата".Подсчитайте количество прожитых Вами к данному моменту дней, оформите расчеты в виде таблицы, содержащей дату рождения (ДР), текущую дату (ТД) и формулу, соответствующую разности ТД - ДР. К результату примените числовой формат ячеек Общий. Текущую дату Excel вставит в ячейку при вызове функции СЕГОДНЯ().

Функции работы с датами представлены в категории Дата и время вкладки Мастера функций.

Определите день недели для любой памятной Вам даты (например, для дня Вашего рождения), пользуясь функцией ДЕНЬНЕД(). В качестве аргумента функции укажите дату, заключив ее в кавычки, например ДЕНЬНЕД(“12.03.86”) или укажите адрес ячейки, содержащей дату.

Подсчитайте количество прожитых Вами часов.

Задание 6.

На новом листе постройте одну из таблиц:

§ календарь на текущий год,

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

§ таблицу в соответствии с заданием преподавателя.

Задание для самостоятельной работы

С помощью настройки параметров (Сервис→Параметры) выполните следующие действия:

· добавьте свой собственный текстовый список (вкладка Список),

· перейдите от просмотра значений формул к просмотру самих формул (вкладка Вид). "Проявить"/убрать все формулы на листе можно и сочетанием клавиш [CTRL+`].

· Введите/отмените пароль для открытия Вашего файла (вкладка Безопасность). Установив защиту файла, не забудьте пароль!

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

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

1. Какие виды работ позволяет выполнить табличный процессор Excel?

2. Что такое диапазон данных?

3. Как выделить несмежные диапазоны для совместного их форматирования?

4. Какие отличия имеют панели инструментов Стандартная и Форматирование программы Excel от соответствующих панелей текстового редактора Word?

5. Какие виды диаграмм можно построить в Excel?

6. Как Excel работает с датами?

7. Приведите примеры использования абсолютной и относительной адресации.

8. Какие преимущества дает использование имен ячеек?

9. Какое расширение имеют файлы, созданные с помощью Excel?

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

11. Как защитить свой файл от постороннего доступа?

12. Какие последовательности поддерживает Excel при автозаполнении?

13. Как ввести собственный список (последовательность) для автозаполнения?

Лабораторная работа № 5