Учебное пособие, 2003 г. Учебное пособие разработано ведущим специалистом учебно-методического отдела по информационным технологиям Заикиным И. А

Вид материалаУчебное пособие

Содержание


Вычисления с использованием абсолютных адресов
Упражнение №4. Вычисления с использованием абсолютных адресов
На основании этих данных необходимо пересчитать цену продаваемого товара в рублях.
Наименование товара
Теперь приступайте к расчетам. В ячейку С3 введите формулу: =В3*С1. После ввода этой формулы (нажав клавишу ) в ячейке появится
Наименование товара
Плов из кальмаров
Раскладка порций.xls
Расход электроэнергии и сумма оплаты за год
Оплата электроэнергии.xls
Подобный материал:
1   2   3   4   5   6   7   8   9   ...   12

Вычисления с использованием абсолютных адресов


В предыдущих упражнениях вы познакомились с выполнением расчетных операций, в которых используются относительные адреса ячеек. При некоторых операциях копирования, удаления, вставки Excel автоматически изменяет этот адрес в формулах. Иногда это служит источником ошибок. Чтобы отменить автоматическое изменение адреса данной ячейки, вы можете назначить ей абсолютный адрес. Для этого необходимо проставить перед номером столбца и (или) перед номером строки знак доллара «$». Например, в адресе $А3 не будет меняться номер столбца, в адресе В$5 — номер строки, а в адресе $D$10 — ни тот, ни другой номер.

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

Упражнение №4. Вычисления с использованием абсолютных адресов


Задание 1

Исходные данные нашей задачи ­– цена продаваемого товара (в долларах) и текущий курс доллара. На основании этих данных необходимо пересчитать цену продаваемого товара в рублях.

Цена (руб.)=Цена ($)* Курс ($/руб.)

Прежде всего введите в таблицу исходные данные (см. таблицу).






1$=

29,0р.

Наименование товара

Цена ($)

Цена (руб)

Стул для компьютера

20

580

Стеллаж

40

1160

Кресло рабочее

40

1160

Стол приставной

50

1450

Стойка компьютерная

60

1740

Стол рабочий

80

2320

Тумба выкатная

90

2610

Шкаф офисный

100

2900

В ячейки A2-С2 введите заголовки столбцов:

А2 – Наименование товара

В2 – Цена ($)

C2 – Цена (руб.)

В ячейку В1 ввести текст "1$=", а в ячейку С1 значение 29,0 (или по своему усмотрению курс доллара на сегодняшний день).

Ячейки A3-A10; B3-B10 заполните соответствующими текстовыми и числовыми данными. При этом для ячеек B3-B10 установите денежный формат и выберите обозначение рублях: р.

Выполните обрамление созданной таблицы. Выберите нужный элемент оформления (в данном случае ).
Теперь приступайте к расчетам. В ячейку С3 введите формулу: =В3*С1. После ввода этой формулы (нажав клавишу ) в ячейке появится результат: 580.

Введя правильную формулу в первую ячейку (в данном случае С3), скопируйте ее удобным вам способом в ячейки, в которые необходимо вводить аналогичные формулы. Посмотрите, что у вас получилось.





1$=

29,0р.

Наименование товара

Цена ($)

Цена (руб)

Стул для компьютера

20

580,00р.

Стеллаж

40

#ЗНАЧ!

Кресло рабочее

40

23 200,00р.

Стол приставной

50

#ЗНАЧ!

Стойка компьютерная

60

1 392 000,00р.

Стол рабочий

80

#ЗНАЧ!

Тумба выкатная

90

125 280 000,00р.

Шкаф офисный

100

#ЗНАЧ!


В некоторых ячейках высвечивается непонятное сообщение #ЗНАЧ!, а в других ячейках – полученные числовые значения не соответствуют расчетным значениям. Так вот, #ЗНАЧ! – недопустимый тип операнда формулы (например, вместо числа введен текст) (более подробно об этой и других ошибках изложено в следующей теме). Почему это произошло? Выделите ячейку С4 и посмотрите формулу, которая введена в эту ячейку. В результате копирования вниз по вертикали в каждой следующей ячейке номера строк в формуле увеличились на единицу и в ячейке С4 получилась формула: =В4*С2. Однако в ячейке С2 находится текст "Цена (руб.)", который определяется как недопустимый тип операнда формулы (вместо числа введен текст) и при выполнении умножения числового значения на текстовое выдается сообщение #ЗНАЧ! В остальных ячейках С5-С10 аналогичные несоответствия. Таким образом, возникает необходимость закрепления адреса ячейки С1 (куда введено значение курса доллара), чтобы при копировании формулы эта ячейка была строго фиксирована. Для этого нужно выделить ячейку С3, и в строке формул в формуле =В3*С1 исправить относительный адрес ячейки С1 на абсолютный. Для этого необходимо проставить перед номером строки знак доллара «$». В этом случае в адресе С$1 не будет меняться номер строки. Введение этого изменения зафиксируйте нажатием клавиши <Enter>. Внешне в ячейке С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)




Плов из кальмаров




Всего порций

20

Продукт

Раскладка на 1 порцию (г)

Всего (г)

Кальмары

48

960

Лук репчатый

17

340

Масло растительное

8

160

Морковь

9

180

Рис

12

240







1880

После выполнения всех операций сохраните документ в файле с именем Раскладка порций.xls в своей папке.


Задание 3.

Создать расчетную таблицу по определению приходно-расходной части.

Для этого:

Ввод исходных значений выполните в колонки Месяц, Дата, Показания счетчика и ячейку, соответствующую нормативу по электроэнергии.

Расход и сумму рассчитайте в соответствии со следующими зависимостями:

Расход = показание счетчика (последующее) – показание счетчика (предыдущее)

Сумма = расход * значение норматива по электроэнергии

Диапазоны ячеек определите самостоятельно.


Расход электроэнергии и сумма оплаты за год































Норматив по электроэнергии

55

коп/кВт-ч



















Месяц

Дата

Показание счетчика

Расход (кВт-ч)

Сумма (руб, коп)

 

26.12.00

3750

 

 

Январь

30.01.01

3900

150

82,50

Февраль

25.02.01

4100

200

110,00

Март

23.03.01

4300

200

110,00

Апрель

27.04.01

4450

150

82,50

Май

29.05.01

4600

150

82,50

Июнь

23.06.01

4700

100

55,00

Июль

22.07.01

4750

50

27,50

Август

30.08.01

4850

100

55,00

Сентябрь

30.09.01

4950

100

55,00

Октябрь

29.10.01

5100

150

82,50

Ноябрь

28.11.01

5300

200

110,00

Декабрь

23.12.01

5600

300

165,00










ИТОГО

1 017,50

После выполнения всех операций сохраните документ в файле с именем Оплата электроэнергии.xls в своей папке.