Ссылки на ячейки текущего рабочего листа

Пример: =А1 =В5+С2

Ссылки на другие листы той же книги

Пример: =Лист2!А3 =Экзамен!D5 =‘32эи’!H3

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

Ссылки на листы других книг

Возможны 2 случая:

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

Пример: =[Имя книги.xls]Лист5!$A$3 т.е. по умолчанию создается абсолютная ссылка.

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

Пример: =‘C:\Имя папки\[Имя книги.xls]Лист5’!$A$3.

Объемные ссылки

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

Пример:

Создано 100 листов в книге. Необходимо определить сумму значений из диапазона (В1:В15), расположенного во всех ста листах.

=СУММ(Лист1:Лист100!В1:В15) или =СУММ(‘29 эи:32 эи’!В1:В15).

При работе с объемными ссылками можно использовать до 11 функций, например: СУММ, СРЗНАЧ, МАКС, МИН.

 

Циклические ссылки

Циклическая ссылка – это ссылка, которая зависит от своего собственного значения.

Пример:

В ячейку А1 введена формула =В5+С3·А1.

MS Excel выдаст сообщение об ошибке, которую необходимо устранить.

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

По умолчанию ссылки на ячейки в формулах относительные, то есть адрес ячейки определяется на основе расположения этой ячейки относительно ячейки с формулой. При копировании ячейки с формулой относительная ссылка автоматически изменяется. Именно возможность использования относительных ссылок и позволяет копировать формулы. Абсолютные, относительные и смешанные ссылки являются частным случаем рассмотренных выше пяти видов ссылок (таблица 10).

Таблица 10 – Типы ссылок

Тип ссылки	Описание	Пример
Абсолютная	Использует для указания на ячейку ее фиксированное положение на листе. В абсолютной ссылке перед именем столбца и номером строки ставится знак $.	=$A$5
Относительная	Указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например, на две строки выше.	=А1
Смешанная	Содержит относительную и абсолютную ссылки. В смешанной ссылке знак $ ставится перед абсолютной частью. Смешанные ссылки используются, чтобы при копировании зафиксировать только часть ссылки на ячейку.	=$A1

 

На рис. 16 показан результат копирования формулы, содержащей абсолютную (а), относительную (б) или смешанную (в, г) ссылки. Исходная формула расположена в ячейке C2 и ссылается на ячейку А1. Затем формула в ячейке C2 копируется в ячейку D5. Серым цветом показаны ячейки на которые ссылается исходная формула (в ячейке C2) и формула полученная после копирования (в ячейке D5).

Рис. 16. Результат копирования формулы

 

Задание по работе

 

Документы сохранять с указанными ниже именами в своей папке на диске D.

 

Задание 1

1. Создайте рабочую книгу и сохраните ее с именем Ссылки_1.

2. На Листе1 создайте таблицу Товары (таблица 11).

3. Заполните столбцы Стоимость: в ячейке D2 должны быть относительные ссылки, а в ячейках E2 смешанные. Cкопируйте содержимое ячейки D2 на диапазон D2:D4, а ячейки E2 на диапазон E2:E4.

 

Таблица 11 - Товары

4. Сохраните документ.

 

Задание 2

1. Создайте рабочую книгу и сохраните ее с именем Ссылки_2.

2. На Листе1 создайте таблицу, образец заполнения которой представлен в таблице 12. Для этого:

· в ячейку B4 введите формулу, которая произведет расчет функции в соответствии с вариантом. Формула должна содержать смешанные ссылки, в противном случае таблица будет заполнена неправильно;

· скопируйте содержимое ячейки B4 на диапазон B4:E9.

 

Таблица 12 – Расчет функции

 

Варианты задания:

1.		7.
2.		8.
3.		9.
4.		10.
5.		11.
6.		12.

3. Сохраните документ.

 

Задание 3

1. Создайте рабочую книгу и сохраните ее с именем Каталог. Лист1 переименуйте в Курсы валют и создайте таблицу 13.

 

Таблица 13 – Курсы валют

2. Лист2 переименуйте в Товар и наберите таблицу Товары (таблица14). Столбцы E, F, G рассчитайте формулам: Цена продажи = Цена покупки*2, столбцы E, G переведите цену в белорусские рубли.

3. Сохраните документ.

Таблица 14 - Товары

 

4. Создайте рабочую книгу и сохраните ее с именем Товар. Лист1 переименуйте в Покупка и создайте таблицу Покупка (таблица 15). В столбцы D:J самостоятельно введите количество купленного товара, столбец K рассчитать как сумму за неделю.

 

Таблица 15 - Покупка

 

5. Лист2 книги Товар переименуйте в Продажи и создайте аналогичную таблицу по продажам.

6. Сохраните документ.

7. Создайте новую рабочую книгу и сохраните ее с именем Баланс. На Листе1 создайте таблицу Баланс (таблица 16). Рассчитайте столбцы C:G по формулам используя смешанные и абсолютные ссылки на книги Каталог и Товар.

 

Таблица 16 - Баланс

 

Остаток, кг = Покупка, кг – Продажа, кг;

Закупки,? = Сумма покупок по каждому товару в переводе в?;

Продажи,? = Сумма продаж по каждому товару в переводе в?;

Прибыль,? = Σ Продажи,? - Σ Закупки,?;

Прибыль, BR = перевести из? в белорусские рубли.

Итого – сумма по столбцам D, E, F, G.

8. Сохраните документ и закройте книги Каталог, Товар и Баланс.

 

 

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

 

1. Виды ссылок в MS Excel.

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

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

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

5. Объемные и циклические ссылки. Приведите примеры.

6. Абсолютная, относительная и смешанная адресация. Приведите примеры.

 

 

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

Создание таблиц. Условное форматирование

 

Методические рекомендации

 

Чтобы упростить управление взаимосвязанной группой данных и их анализ, диапазон ячеек можно преобразовать в таблицу Microsoft Office Excel (раньше она называлась списком Excel). Таблица представляет собой набор строк и столбцов листа, содержащих взаимосвязанные данные. Функции работы с таблицами позволяют независимо управлять данными, находящимися в разных строках и столбцах (рис. 17).

Рис. 17. Структура таблицы

 

Таблица может включать следующие элементы:

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

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

Строка итогов. В таблицу можно добавить строку итогов, которая обеспечивает доступ к функциям подведения итогов (например, к функциям СРЗНАЧ, СЧЁТ или СУММ). В каждой ячейке строки итогов отображается раскрывающийся список, что позволяет быстро вычислять нужные итоговые значения.

Маркер изменения размера. Маркер изменения размера в нижнем правом углу таблицы позволяет путем перетаскивания изменять размеры таблицы.

Управление данными таблицы

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

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

Форматирование данных таблицы. Данные таблицы можно быстро отформатировать, применив встроенный или пользовательский стиль таблицы. Можно также использовать экспресс-стили.

Вставка и удаление строк и столбцов таблицы. Можно добавить пустую строку в конец таблицы, включить в таблицу смежные строки или столбцы листа или вставить строки или столбцы таблицы в любое ее место.

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

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

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

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

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

Форматирование с помощью стилей. Выделяется диапазон ячеек, которые необходимо включить в таблицу. На вкладке Главная в группе Стили нажимается кнопка Форматировать как таблицу и выбирается нужный стиль. Редактирование таблицы можно производить с помощью контекстной вкладки Работа с таблицами/Конструктор.

Для добавления в таблицу итоговой строки необходимо поставить курсор в любую ячейку таблицы, а затем выбрать команду Строка итогов (Работа с таблицами/Конструктор ® Параметры стилей таблиц ® Строка итогов).

Формулы можно копировать. Например, при создании строки итогов, будет рассчитана сумма только последнего столбца. Эту формулу можно скопировать на остальные столбцы.

Удаление стилей форматирования таблицы производится командой Преобразовать в диапазон (Работа с таблицами/Конструктор ® Сервис ® Преобразовать в диапазон).