В предыдущих упражнениях вы познакомились с выполнением расчетных операций, в которых используются относительные адреса ячеек. При некоторых операциях копирования, удаления, вставки Excel автоматически изменяет этот адрес в формулах. Иногда это служит источником ошибок. Чтобы отменить автоматическое изменение адреса данной ячейки, вы можете назначить ей абсолютный адрес. Для этого необходимо проставить перед номером столбца и (или) перед номером строки знак доллара «$». Например, в адресе $А3 не будет меняться номер столбца, в адресе В$5 — номер строки, а в адресе $D$10 — ни тот, ни другой номер.
Эти операции применяются при пересчете цен товаров из одних денежных единиц в другие (в соответствии с курсом обмена валют), раскладке порций при приготовлении блюд, пересчете различных величин в зависимости от нормативного показателя и т.д.
Упражнение №4. Вычисления с использованием абсолютных адресов
Задание 1
Исходные данные нашей задачи – цена продаваемого товара (в долларах) и текущий курс доллара. На основании этих данных необходимо пересчитать цену продаваемого товара в рублях.
Цена (руб.)=Цена ($)* Курс ($/руб.)
Прежде всего введите в таблицу исходные данные (см. таблицу).
| 1$= | 29,0р. | |
| Наименование товара | Цена ($) | Цена (руб) |
| Стул для компьютера | ||
| Стеллаж | ||
| Кресло рабочее | ||
| Стол приставной | ||
| Стойка компьютерная | ||
| Стол рабочий | ||
| Тумба выкатная | ||
| Шкаф офисный |
В ячейки A2-С2 введите заголовки столбцов:
А2 – Наименование товара
В2 – Цена ($)
C2 – Цена (руб.)
В ячейку В1 ввести текст 1$=, а в ячейку С1 значение 29,0 (или по своему усмотрению курс доллара на сегодняшний день).
Ячейки A3-A10; B3-B10 заполните соответствующими текстовыми и числовыми данными. При этом для ячеек B3-B10 установите денежный формат и выберите обозначение рублях: р.
Выполните обрамление созданной таблицы. Выберите нужный элемент оформления (в данном случае 
).
Теперь приступайте к расчетам. В ячейку С3 введите формулу: =В3*С1. После ввода этой формулы (нажав клавишу ) в ячейке появится результат: 580.
Введя правильную формулу в первую ячейку (в данном случае С3), скопируйте ее удобным вам способом в ячейки, в которые необходимо вводить аналогичные формулы. Посмотрите, что у вас получилось.
| 1$= | 29,0р. | |
| Наименование товара | Цена ($) | Цена (руб) |
| Стул для компьютера | 580,00р. | |
| Стеллаж | #ЗНАЧ! | |
| Кресло рабочее | 23 200,00р. | |
| Стол приставной | #ЗНАЧ! | |
| Стойка компьютерная | 1 392 000,00р. | |
| Стол рабочий | #ЗНАЧ! | |
| Тумба выкатная | 125 280 000,00р. | |
| Шкаф офисный | #ЗНАЧ! |
В некоторых ячейках высвечивается непонятное сообщение #ЗНАЧ!, а в других ячейках – полученные числовые значения не соответствуют расчетным значениям. Так вот, #ЗНАЧ! – недопустимый тип операнда формулы (например, вместо числа введен текст) (более подробно об этой и других ошибках изложено в следующей теме). Почему это произошло? Выделите ячейку С4 и посмотрите формулу, которая введена в эту ячейку. В результате копирования вниз по вертикали в каждой следующей ячейке номера строк в формуле увеличились на единицу и в ячейке С4 получилась формула: =В4*С2. Однако в ячейке С2 находится текст Цена (руб.), который определяется как недопустимый тип операнда формулы (вместо числа введен текст) и при выполнении умножения числового значения на текстовое выдается сообщение #ЗНАЧ! В остальных ячейках С5-С10 аналогичные несоответствия. Таким образом, возникает необходимость закрепления адреса ячейки С1 (куда введено значение курса доллара), чтобы при копировании формулы эта ячейка была строго фиксирована. Для этого нужно выделить ячейку С3, и в строке формул в формуле =В3*С1 исправить относительный адрес ячейки С1 на абсолютный. Для этого необходимо проставить перед номером строки знак доллара «$». В этом случае в адресе С$1 не будет меняться номер строки. Введение этого изменения зафиксируйте нажатием клавиши . Внешне в ячейке С3 ничего не изменилось. Однако скопировав измененную формулу в нижестоящие ячейки (С4:С10), увидите, что расчетные результаты значений оказались правильными и соответствуют образцу задания.
После внесенных изменений сохраните документ в файле с именем Пересчет.xls в своей папке.
Задание 2.
Cоставьте расчетную таблицу для раскладки порций при приготовлении блюд.
Для этого:
1. Ячейки A1:С1 объедините и поместите в центре текст Плов из кальмаров.
2. В ячейку В2 ввести текст Всего порций, а в ячейку С2 значение 20 (или значение по своему усмотрению).
3. В ячейки A3:С3 введите названия колонок (столбцов).
4. В ячейки A4:A8; B4:B8 введите значения.
5. В ячейку С4 ввести формулу: =B4*С2 (всего=раскладка на 1 порцию * кол-во порций).
6. Изменить в формуле адрес ячейки С2 с относительного на абсолютный адрес: С$2 (окончательная формула должна иметь вид : =B4*С$2.
7. Выделить ячейку С4 и скопировать данную формулу в ячейки С5:С9.
8. В ячейке С10 ввести формулу автосуммы: = СУММ(С4:С9)
| Плов из кальмаров | ||
| Всего порций | ||
| Продукт | Раскладка на 1 порцию (г) | Всего (г) |
| Кальмары | ||
| Лук репчатый | ||
| Масло растительное | ||
| Морковь | ||
| Рис | ||
После выполнения всех операций сохраните документ в файле с именем Раскладка порций.xls в своей папке.
Задание 3.
Создать расчетную таблицу по определению приходно-расходной части.
Для этого:
Ввод исходных значений выполните в колонки Месяц, Дата, Показания счетчика и ячейку, соответствующую нормативу по электроэнергии.
Расход и сумму рассчитайте в соответствии со следующими зависимостями:
Расход = показание счетчика (последующее) – показание счетчика (предыдущее)
Сумма = расход * значение норматива по электроэнергии
Диапазоны ячеек определите самостоятельно.
| Расход электроэнергии и сумма оплаты за год | ||||
| Норматив по электроэнергии | коп/кВт-ч | |||
| Месяц | Дата | Показание счетчика | Расход (кВт-ч) | Сумма (руб, коп) |
| 26.12.00 | ||||
| Январь | 30.01.01 | 82,50 | ||
| Февраль | 25.02.01 | 110,00 | ||
| Март | 23.03.01 | 110,00 | ||
| Апрель | 27.04.01 | 82,50 | ||
| Май | 29.05.01 | 82,50 | ||
| Июнь | 23.06.01 | 55,00 | ||
| Июль | 22.07.01 | 27,50 | ||
| Август | 30.08.01 | 55,00 | ||
| Сентябрь | 30.09.01 | 55,00 | ||
| Октябрь | 29.10.01 | 82,50 | ||
| Ноябрь | 28.11.01 | 110,00 | ||
| Декабрь | 23.12.01 | 165,00 | ||
| ИТОГО | 1 017,50 |
После выполнения всех операций сохраните документ в файле с именем Оплата электроэнергии.xls в своей папке.
Статьи к прочтению:
- Вычисления с помощью формул и функций
- Вычислите минимальное, максимальное и среднее значения блоков ячеек
Быстрое вычисление квадратных корней
Похожие статьи:
-
Вопрос 22. абсолютные и относительные адреса. копирование формул.
Относительными называются ссылки, которые при копировании в составе формулы в другую ячейку автоматически изменяются При копировании формулы с…
-
Использование имен ячеек и диапазонов
Лабораторная работа №2 Электронные таблицы Вычисления по формулам с использованием абсолютных ссылок и имен ячеек. Теория В некоторых ситуациях в…