13. Абсолютные ссылки

Абсолютная ссылка применяется для указания на ячейку, используя ее фиксированное положение на листе. Для указания абсолютной ссылки используется символ $, который добавляется перед именем столбца и строки. ! Пример: $D$4.

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

Задание:

1. Откройте книгу Командировки, лист Надбавка.

2. Щелкните в ячейку F2 и введите формулу =Е2+Е2*Е10, что соответствует «суммарно»+процент надбавки от соответствующего значения «суммарно».

3. Чтобы ссылка на ячейку была закреплена (стала абсолютной), нажмите F4 в верхнем ряду функциональных клавиш на клавиатуре. К адресу ячейки добавились служебные значки $E$10.

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

5. Сделайте формат ячейки F2 – Денежный.

6. Двойным щелчком по маркеру автозаполнения скопируйте данные до F7 включительно. Весь столбец рассчитан!

7. Щелкните по ячейке F10. Убедитесь, что расчет в ней производился по формуле =E7+E7*$E$10.

8. Измените в ячейке Е10 величину надбавки на 15%. Электронная таблица пересчитала столбец С учетом надбавки.

14. Создание формул

Задание:

1. Откройте книгу Командировки, лист Бензин.

2. В ячейку С12 введите цену на бензин.

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

4. Сохраните изменения.

15. Вычисления с данными других листов

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

[Имя Книги].‘Имя листа’!Адрес ячейки.

При вводе в формулу ссылки на данные с листов другой книги книга должна быть открыта. Для ввода формулы с ссылками на данные из других листов или с листов другой книги надо:

• установить курсор в нужную ячейку, куда будет вводиться формула;

• ввести знак = и начать ввод формулы;

• при добавлении ссылки на другой лист щелкнуть нужный лист;

• в открытом листе щелкнуть нужную ячейку;

• продолжить ввод формулы или нажать клавишу ENTER для ее завершения.

Задание:

1. Откройте книгу Ведомости, лист Январь.

2. Введите формулы для Иванова И.Д. По этим формулам будет рассчитана ведомость для всех сотрудников:

   1. в ячейке С4 формула =B4*0.2 рассчитает премию (используется относительная адресация);

   2. в ячейке D4 формула =B4+C4 рассчитает сумму Всего (используется относительная адресация);

   3. в ячейке E4 формула =D4*’Ставка налогов’!$B$2 рассчитает подоходный налог каждого, используя абсолютную ссылку на лист Ставка налогов и ячейку B2;

   4. в ячейке F4 формула =D4*’Ставка налогов’!$B$3 рассчитает налог в Пенсионный фонд, используя абсолютную ссылку на лист Ставка налогов и ячейку B3. Скопируйте строку, рассчитанную для Иванов И.Д до Макаров М.Т.;

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

16. Автоматические итоговые функции

Для ускорения и удобства расчетов Excel предоставляет более 300 встроенных функций. Весь список функций можно просмотреть в Мастере функций, н а вкладке Формулы имеется группа инструментов Библиотека функций, где всевозможные функции распределены по критериям. Кнопка Вставка функции дает возможность открыть диалоговое окно Мастер функций, в котором собраны все функции Excel по категориям. Им удобно пользоваться, если известна задача, но не известно какой функцией следует воспользоваться. Также в этом окне можно осуществлять поиск нужной функции. Функции – специальные, заранее созданные формулы, описывающие алгоритмы сложных расчетов.

Наиболее часто используемые функции (суммировать, среднее, количество, максимум, минимум) вынесены на вкладку Главная и доступны в раскрывающемся списке кнопки Σ(Автосумма) группы Редактирование.

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

Для того чтобы рассчитать автоматическую итоговую функцию в ячейках столбца или строки таблицы, надо:

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

• выбрать нужную итоговою функцию в списке кнопки Автосуммирование;

• проверить правильность выделения диапазона и при необходимости изменить диапазон, выделив его с помощью мыши;

• нажать клавишу ENTER.

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

Задание:

1. Откройте книгу Ведомости лист Январь.

2. Просуммируйте данные в строке Итого.

3. Добавьте строки В среднем и Количество записей и рассчитайте результат для столбца Оклад.