§9. Способы адресации

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

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

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

Например, Исходная ячейка В2 с формулой =С2+1. На рисунке 94 приведен образец изменения формулы.

Рисунок 94 – Результат копирования формулы В2

Абсолютная ссылка – это не изменяющийся при копировании и перемещении формулы адрес ячейки. Для указания абсолютной адресации перед именем столбца и номером строки вводится символ $. Например: $B$4, $C$2:$P$48 и т.д..

Если при копировании формулы требуется сохранить ссылку на конкретную ячейку или область, то и следует воспользоваться абсолютной адресацией. Знак «доллара» устанавливается при однократном нажатии на функциональную клавишу F4 в формуле.

84

Например, требуется определить стоимость путевки в рублях при поездке в разные страны (см. рисунок 95). Стоимость зависит от курса доллара.

Рисунок 95 – Пример таблицы

В ячейке С1 хранится размер курса доллара, выраженный в рублях. Формула, для расчета стоимости путевки вводится в ячейку D3, и выглядит как умножение стоимости в долларах на курс доллара, т.е. =В3*В1. При копировании формулы в ячейки D4:D6 необходимо, чтобы ячейка В1 не изменялась. Для этого устанавливается абсолютная ссылка клавишей F4 и тогда формула примет вид: =В3*$В$1.

Смешанная ссылка - то частично изменяющийся при копировании и перемещении формулы адрес ячейки, т.е. столбец или строка. Для указания смешанной адресации перед обозначением столбца или строки вводится символ $, например, A$1, $C8. Та часть адреса ячейки, перед которой стоит знак $ в смешанной ссылки при копировании формулы меняться не будет. Знак $, так же, как в абсолютной ссылке устанавливается функциональной клавишей F4, только при установке $ перед строкой требуется нажать F4 – 2 раза, а перед столбцом – 3 раза.

Например, требуется рассчитать стоимость некоторого количества товара, приведенного на рисунке 96.

Рисунок 96 – Условие задачи

Для определения стоимости покупки за два насоса, в ячейку С3 вводится формула =С2*В3. При копировании формулы вправо или вниз, произойдет изменение как в адресе строки, так и в адресе столбца. В задаче же необходимо так использовать смешанную ссылку, чтобы независимо от того, куда произойдет копирование формулы, данные брались из столбца В и строки 2. Для этого на соответствующие данные (те, которые не должны меняться) в адресе ячейки необходимо установить знак $. В результате

85

исходная формула примет вид: =С$2*$В3 (см. рисунок 96). Тогда, с помощью одной формулы можно заполнить всё поле вычисляемой таблицы.

Задание 1. Подсчитать продажу билетов. Этапы работы над заданием:

1Создать таблицу, приведенную на рисунке 97;

2Установить денежный формат, выполнив: Формат Ячеек →

вкладка Число → установить формат и рядом с числом нужно, чтобы стояла буква «р.»

3 Заполнить пустые ячейки, используя статистические функции на

вкладке Главная, в меню кнопки ; 4 Для вычисления % от общего необходимо установить процентный

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

Рисунок 97 – Образец таблицы к заданию 1

b) Построить диаграмму по исходным данным, т.е. по диапазону А6:D9, оформить её заливками и шрифтами.

Задание 2. Выполните расчеты начисления заработной платы для г. Иркутска.

1. Создать таблицу вида, как на рисунке 98.

86

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

10.Оплата со скидкой по льготе находится умножением общей стоимости на коэффициент скидки.

11.Итого к оплате: если есть льгота, то выводится сумма со скидкой, иначе - общая стоимость

12.Построить две диаграммы:

а). Для первой взять Фамилии плательщиков, Плату за квартиру и Итого к

88

б). Вторую диаграмму представить в виде, как на рисунке 101.

13. Все диаграммы отформатировать соответствующим образом и разместить на отдельных листах

Задание 4. Подготовить таблицу по рисунку 102 (введите не менее 10 записей). Применить к ячейкам нужные способы выравнивания, установить границы. Применить к ячейкам, содержащим цены денежный формат числа с разделением на разряды и двумя десятичными знаками.

Рисунок 102 – Фрагмент таблицы к заданию 4

Задание 5. КАЛОРИЙНОСТЬ ОБЕДА.

1. Продумать структуру и создать таблицу для решения следующей задачи:

Из одного грамма углеводов человек получает 4,1 ккал, из одного грамма жиров – 9,3 ккал, из одного грамма белков – 4,2 ккал.

Порция борща со свежей капустой содержит 3,6 г. белков, 12 г. жиров и 24 г. углеводов.

Порция картофеля с маслом – 2,7 г белков, 7 г жиров и 39 г углево-

дов.

Порция компота из свежих фруктов содержит 0,4 г белков, 36 г углеводов, не содержит жиров.

Сколько энергии вы получите:

1)Отдельно из белков, жиров и углеводов, содержащихся в каждом

блюде;

2)Из каждого блюда;

3)Отдельно из белков, жиров и углеводов, входящих в обед;

4)Из всего обеда?

2.Ввести необходимые формулы и провести по ним расчёты.

Задание 6. Выполнить индивидуальное задание по вариантам,

указанного преподавателем.

Вариант 1 Один стакан лимонада содержит 15 калорий, 1 кусок торта - 150 ка-

лорий, 1 драже «Тик-Так» - 2 калории. Во время праздничного обеда Стае выпил 5 стаканов лимонада, съел 20 драже «Тик-Так» и 4 куска торта. Светлана съела 2 драже «Тик-Так», 1 кусок торта и выпила 1 стакан лимонада. Максим выпил 2 стакана лимонада и съел 2 куска торта. Виктор съел 3 куска торта и выпил 2 стакана лимонада.

Построить электронную таблицу, из которой будет видно: сколько всего стаканов лимонада было выпито, кусков торта и драже «Тик-Так» съедено; сколько калорий употребил каждый участник праздничного обе-

89

да; сколько калорий содержалось во всем выпитом лимонаде, всех съеденных кусках торта и драже «Тик-Так».

Вариант 2 На складе компьютерной техники хранятся компьютеры по цене 100

монет, принтеры по цене 55 монет, сканеры по цене 78 монет. Фирмы «Мальва», «Скат», «Прорыв» и «Алиса» имеют магазины по продаже компьютерной техники. «Мальва» продала 10 сканеров и 5 компьютеров. «Скат» - 11 принтеров, 3 компьютера и 2 сканера. «Прорыв» - 7 компьютеров и 4 принтера. «Алиса» - 8 компьютеров, 1 сканер и 6 принтеров.

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

Вариант 3 Завуч премирует учеников своей школы за хорошую учебу: за ре-

шенную задачу ученик получает 5 дискет, за выученное стихотворение - 4 дискеты, за прочитанную книгу - 33 дискеты. Василий решил 1 задачу, прочитал 2 книга и выучил 3 стихотворения. Михаил выучил 25 стихотворений и прочитал 10 книг. Артем решил 15 задач и прочитал 3 книги.

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

Вариант 4 В мастерской школы изготавливается упаковка для подарков: паке-

ты, коробки, мешки. За изготовленный пакет школьник получает 2 книга, за коробку - 4 ручки, за мешок - 3 общих тетради. Борис изготовил 12 пакетов, 3 коробки и 1 мешок. Петр - 5 пакетов, 5 коробок и 2 мешка. Артем - 13 пакетов. Сам учитель - 2 коробки и 5 мешков.

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

Вариант 5 Компания по обслуживанию жилых домов установила следующие

тарифы на свои услуги: 1 литр воды стоит 2 монеты; 1 кВт./ч электроэнергии стоит 0,15 монет; 1 кубометр газа - 5 монет. Малинины израсходовали за месяц 300 литров воды, 60 кВт./ч электроэнергии и 0,5 кубометров газа Бураевы - 50 литров воды, 200 кВт./ч электроэнергии. Петровы - 150 литров воды, 150 кВт./ч электроэнергии и 0,2 кубометров газа. Думовы - 200 литров воды и 0,3 кубометра газа.

Построить электронную таблицу, из которой будет видно: сколько было израсходовано воды, электроэнергии, газа всеми жильцами; сколько

90

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

Вариант 6 Собираясь на пляж, студенты решили запастись питьем. Николай

взял с собой 2 литра кваса и литр газировки, Павел - литр газировки и 3 литра малинового сиропа, Виктор и Михаил вместе взяли 3 литра кваса и 2 литра газировки, Толя - 3 литра газировки. Петр - 3 литр кваса и 1 литр колы. 1 литр кваса в городе стоит 1 монету, 1 литр газировки - 3 монеты, 1 литр колы - 2 монеты, 1 литр сиропа - 6 монет.

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

Вариант 7 Охотник всегда берет с собой на охоту собаку, которая загоняет для

него зверя. Для того чтобы загнать зайца, собака должна пробежать 8 км, чтобы загнать волка - 15, лису - 10. За декабрь собака добыла 5 зайцев и одного волка, за январь - 8 зайцев, 2 волков и 2 лис, за февраль - лису, 6 зайцев и 2 полков, за март - 4 волков, 3 лис и 5 зайцев.

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

Вариант 8 Летом студенты отправились путешествовать на разных видах

транспорта. Никита проплыл 50 км на пароходе, проехал 40 км на поезде и пролетел 100 км на самолете. Стас проплыл на пароходе 100 км и проехал на поезде 20 км. Толя пролетел на самолете 200 км и проехал поездом 10 км. Маша проехала на поезде 30 км и проплыла на пароходе 60 км. Стоимость проезда на поезде составляет 1 монету за км, на пароходе - 2 монеты за км, па самолете - 4 монеты за км.

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

Вариант 9 Студенты решили сходить в поход. Павел испек для всех пирожки с

мясом, яблоками, капустой и повидлом и разложил их по пакетам, кулькам и коробкам. Пирожков с мясом оказалось 3 коробки, 2 кулька и 3 пакета. Пирожков с яблоками - 5 пакетов, 1 кулек и 2 коробки. Пирожков с по-

91

видлом - 6 кульков и коробка. Пирожков с капустой - 2 пакета, 1 коробка и 1 кулек. В коробку вмещается 20 пирожков, в пакет - 25, в кулек - 15.

Построить электронную таблицу, из которой будет видно: сколько всего пирожков испек Павел и сколько среди них было пирожков каждого сорта; сколько пирожков было уложено в упаковку каждого вида; сколько всего упаковок понадобилось Павлу и сколько среди них было упаковок каждого вида.

Вариант 10 Автомобиль Виктора и Стаса работает на бензине разных сортов и

дизельном топливе. На одном литре бензина 92 он проезжает 120 км, на одном литре бензина 96-100 км, на одном литре дизельного топлива - 150 км. Цена одного литра бензина 92 в городе - 3 монеты за литр, бензина 96- 1 монета за литр, дизельного топлива - 2 монеты за литр. Готовясь к путешествию, Николай закупил 10 литров бензина 92, 5 литров бензина 96 и 10 литров дизельного топлива. Виктор и Стас вместе купили 10 литров бензина 92 и 10 литров бензина 96. Толя ~ 5 литров дизельного топлива, 10 - бензина 92 и 10 -бензина 96. Максим - 20 литров бензина 92.

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

Вариант 11 Федор, Максим и Гарик летом жили на Кипре, а родители слали им

письма, посылки, телеграммы и бандероли, которые доставлял почтальон Петров. Каждое письмо весило в среднем 100 г, каждая посылка - 5 кг, каждая телеграмма - 50 г, каждая бандероль - 500 г.

Федор получил 10 писем, 2 посылки, 10 телеграмм и 1 бандероль. Максим получил 4 письма, 1 посылку, 2 телеграммы и 1 бандероль. Гарик не получил ни одного письма, ни одной телеграммы, зато получил 4 посылки и 2 бандероли.

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

Вариант 12 В лесу у реки поселился охотник. Всех, кто пытался его прогнать, он

прогонял сам, стреляя в них. Количество выстрелов зависело от того, в кого он стрелял. В медведя он стрелял 5 раз, в волка - 4 раза, в лису - 3.

За первый месяц охотника пытались прогнать 2 медведя, 3 волка и 5 лисиц. За второй месяц на него покушались 3 медведя, 2 волка и 7 лисиц.

92

За третий месяц беспокоили 7 медведей, 5 волков и 6 лисиц. За четвертый месяц охотнику пришлось иметь дело с 3 медведями, 6 волками и 10 лисицами. После чего охотника в конце концов оставили в покое.

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

Вариант 13 Владимир взялся помочь своим друзьям сдать экзамены. Для того,

чтобы написать шпаргалку на один вопрос по географии, он должен потратить 6 минут, чтобы написать правильный ответ на один вопрос по математике - 10 минут, правильный ответ на один вопрос по русскому языку - 8 минут.

Алеше досталось: на экзамене по географии - 3 вопроса, на экзамене по математике - 5 вопросов, на экзамене по русскому языку - 2 вопроса.

Женьке досталось: на экзамене по географии - 4 вопроса, на экзамене по математике - 3 вопроса, на экзамене по русскому языку - 4 вопроса.

Гоге досталось: на экзамене по географии - 2 вопроса, на экзамене по математике - 4 вопроса, на экзамене по русскому языку - 5 вопросов. И наконец, самому Владимиру: на экзамене по географии - 5 вопросов, на экзамене по математике - 2 вопроса, да экзамене по русскому языку - 3 вопроса.

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

Вариант 14 Наталья решила испечь на свой день рождения 3 торта: яблочный,

ореховый и шоколадный. Для приготовления одного яблочного торта требуется 200 г сливочного масла, 200 г муки, 2 яйца, 300 г сахара и 8 яблок. Для приготовления одного орехового торта надо 200 г орехов, 400 г муки, 300 г сахара, 300 г масла и 3 яйца. На один шоколадный торт тратится 3 шоколадки, 2 яйца, 300 г муки, 200 г масла и 100 г сахара. Масло в городе стоит 100 монет за кг, сахар - 20 монет за кг, яйца - 20 монет за десяток, мука - 30 монет за кг, орехи - 100 монет за кг, яблоки - 2 монеты за штуку, шоколадки - 10 монет за штуку.

Построить электронную таблицу, из которой будет видно, сколько будет стоить каждый торт; сколько продуктов каждого вида Наталья

93