Лабораторная работа Адресация. Относительная и абсолютная адресации. Ссылки. Формулы Excel

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

Тема: Адресация. Относительная, абсолютная и смешанная ссылки, внешние ссылки. Формулы Excel.

Цель: Научиться применять принципы относительной и абсолютной адресации, а также правила формирования выражений для вычислений в электронных таблицах.

Ссылка, относительная ссылка

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

В2

Ссылка на ячейку В2

(А1:Н135)

Ссылка на диапазон

(А1:Н135;А235:Н325)

Ссылка на два диапазона

Лист2!В2

Ссылка на ячейку В2 Листа2

[Книга 2]Лист2!В2

Ссылка на ячейку В2 Листа2 Книги2

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

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

При перемещении формулы ссылки не изменяются.

Абсолютные и смешанные ссылки

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

Чтобы сделать ссылку абсолютной, необходимо ввести знак $ перед каждой из составляющих адреса - буква столбца и номер строки: $D$1.

Последовательное нажатие на клавишу F4 - осуществляет перебор типов ссылок.

В некоторых случаях используется смешанные (полуабсолютные) ссылки вида $D1, D$1, в которых не меняется (абсолютна) только один из составляющих адреса, то есть при копировании изменяется только незафиксированный элемент.

Внешние ссылки

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

Формат записи внешней ссылки

[имя рабочей книги] имя листа! Адрес ячейки (диапазона)

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

= Лист3!А1 + [Книга1.xls] Лист1! $А$1

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

'[Общество Знание.xls] Реклама'!$А$1

'[Знание]Реклама ТВ'! $А$1

'Реклама ТВ'!А2

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

'Е:\Проект КОВ\[Общество Знание.xls]Реклама'! $А$2

Ввод ссылки на другой лист данной книги:

1. Ввести знак «=» в текущую ячейку;

2. Активизировать лист;

3. Щёлкнуть по ячейке с нужными данными;

4. Нажать клавишу Enter.

Ввод ссылки на другую рабочую книгу:

1. Открыть книгу с нужными данными;

2. Ввести знак «=» в текущую ячейку;

3. Активизировать книгу с нужными данными на Панели задач;

4. Активизировать Лист;

5. Щёлкнуть по ячейке с нужными данными;

6. Нажать на клавишу Enter

Формулы Excel

Формула - это последовательность числовых значений, адресов ячеек, имен, функций и стандартных арифметических операций, позволяющая получить новое значение.

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

Арифметические операторы:

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

Формула всегда должна начинаться со знака = (равно), иначе значение будет воспринято как текст. Максимальная длина формул - 1024 символа.

Последовательность вычислений в формуле:

1. действие в скобках;

2. умножение;

3. сложение, вычитание.

Ввод формул

Адрес ячейки, используемый в формуле называется ссылкой. Ссылка служит идентификатором ячейки и указывает программе, из какой ячейки взять значение для выполнения действий, определённых формулой.

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

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

Команда Автосумма

В Excel часто создаются таблицы, в которых в последнем столбце/строке подводится итог. Обычно требуется просуммировать данные столбца/строки. Для быстроты выполнения этой операции используется кнопка Автосумма стандартной панели инструментов.

При активизации этой кнопки происходит автоматическая вставка функции суммирования = СУММ (аргументы), которая суммирует значения указанных ячеек.

Последовательность выполнения команды Автосумма:

1. Сделать активной ячейку, в которой надо получить результат;

2. Нажать кнопку Автосумма на панели инструментов;

3. Изменить (если необходимо) диапазон;

4. Завершить действие - клавиша Enter.

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

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

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

Задание:

1. Составить таблицу, показывающую цену нетто и валютную цену продажи серии изделий, обложенных налогом на добавленную стоимость (НДС);

2. Ввести заголовок таблицы в ячейку А1 - ЦЕННИК С НАЛОГОМ;

3. Начиная со строки 5, ввести следующие заголовки колонок:

Код Цена Налог Общая

изделия нетто стоимость

4. Подогнать ширину столбцов и произвести центровку заголовков;

5. Начиная со строки 7, ввести следующие данные в колонках А,В:

СУ 340 20,54

СУ 341 31,45

СУ 342 14,65

СУ 343 22,44

СУ 344 25,50

СУ 345 31,20

СУ 346 39,50

СУ 347 28,40

6. Сформировать колонки Цена нетто, Налоги, Общая стоимость так, чтобы эти данные были указанны в денежном формате;

7. В ячейку В3 ввести: Справка налога;

8. В ячейку D3 ввести 17,5;

9. Сформировать эту ячейку так, чтобы показать с 1 знаком десятичную дробь;

10. Ввести формулу в ячейку С7 (=B7*$D$3/100). Эта формула должна ссылаться на ячейку D3, используя абсолютную адресацию;

11. Скопировать эту формулу вниз по колонке С;

12. Вести формулу для колонки Общая стоимость и скопировать эту формулу вниз по колонке;

13. Сохранить составленную таблицу. Дав имя книге Ценник;

14. Объединить ячейки E5:F5, ввести заголовок Скидка. Подогнать ширину столбца. В ячейку Е6 ввести 10%, а в ячейку F6 ввести 15%. Сформировать ячейки E7:F14 как денежные величины;

15. В ячейку Е7 ввести формулу для подсчёта 10% скидки от Общей продажи. В формуле нужно использовать абсолютную ссылку на ячейку E6;

16. В ячейке F7 соответствующую формулу подсчёта скидки. Скопировать эту формулу вниз по колонке;

17. Вставить колонку между E и F. Ввести заголовок Цена со скидкой. Ввести соответствующую формулу для этой колонки и скопировать эту формулу вниз по колонке;

18. Вставить новую колонку Цена со скидкой в ячейку ввести 15% за колонкой Скидка. Ввести соответствующую формулу и скопировать эту формулу вниз по колонке. Сохранить составленную таблицу, назвав книгу - Ценнник_1;

19. В ячейке Е6 изменить содержимое на 7,5%, а в ячейке G6 на 12,5%;

20. Сохранить изменения. Дать имя Книги «Ценнник_этолон»;

21. Сделать предварительный просмотр таблицы;

22. Скопируйте содержимое ячейки D3 на Лист 2 в клетку А1;

23. Вместо ссылок на ячейку D3 в таблице, введите ссылку на ячейку А1 Листа2.

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

1. Раскрыть суть понятий относительная и абсолютная адресация;

2. Алгоритм ввода ссылки на другой лист Книги;

3. Алгоритм ввода ссылки на другую Рабочую книгу;

4. Формулы Excel;

5. Перечислите последовательность выполнения команды Автосумма.