Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Абсолютная относительная и смешанная адресация ячеек

Абсолютная относительная и смешанная адресация ячеек

Как было показано выше в примере 7, электронная таблица позволяет автоматически пересчитывать значения ячеек при изменении исходных данных. Однако табличные процессоры позволяют вычислять значения не только в старых, но и в новых ячейках по формулам, ранее введённым в другие ячейки. Данное свойство табличного процессора напрямую связано с разными видами адресаций ячеек в таблице, о которых пойдёт речь дальше.

Адреса ячеек, использующиеся в формулах ЭТ, могут быть трёх видов – абсолютными, относительными и смешанными. Рассмотрим каждый из них.

В случае относительной адресации адреса ячеек, используемые в формулах, определены относительно места расположения формулы . При копировании формулы в новое положение таблицы адреса используемых в формуле ячеек меняются соответственно новому месту положения формулы. Например, в таблице на рисунке справа формулу в ячейке `»C»1` табличный процессор воспринимает так: умножить значение ячейки, расположенной на две ячейки левее на значение ячейки, расположенной на одну ячейку левее данной формулы. Тогда при копировании формулы в ячейку `»C»2` табличный процессор умножит значение ячейки `»A»2` на значение ячейки `»B»2`. Преимущество относительной адресации состоит в том, что при копировании ячейки в новое положение ссылки в копируемой формуле меняются автоматически. Однако на практике бывает так, что адрес ячейки, используемой в формуле, не должен меняться при копировании. В этом случае используется абсолютная адресация . В абсолютных адресах перед неизменяемым значением адреса ячейки ставится знак `$`, например `$»B»$2` – это абсолютный адрес ячейки `»B»2`. Поясним преимущества относительного адреса и использование абсолютного адреса на следующем примере.

Турфирма «Кругосвет» предоставляет путевки в Грецию, на Мальту и в Италию по ценам, указанным в долларах США. Составьте формулу для расчёта цен путёвок в европейской валюте ЕВРО согласно курсу, указанному в таблице.

Составим формулу расчёта цены путёвки в Грецию в ячейке `»C»4` так, чтобы, скопировав её в ячейки `»C»5` и `»C»6`, можно было автоматически вычислить значения цены на путёвки на Мальту и в Италию. Положение ячейки `»B»1` относительно ячеек `»C»4`, `»C»5` и `»C»6` различное, значит необходимо, чтобы в формуле `»C»4` адрес ячейки `»B»1` был абсолютным. Положение ячеек `»B»4`, `»B»5` и `»B»6` относительно `»C»4`, `»C»5`, `»C»6` одинаковое, а значит, адрес ячейки `»B»4` в формуле должен быть относительным. Тогда выражение `=»B»4^(**)$»B»$1` является формулой расчёта цены путёвки в Грецию. Использование относительного адреса `»B»4` и абсолютного адреса `$»B»$1` позволяет скопировать формулу в ячейки `»C»5` и `»C»6` и автоматически вычислить цены путёвок в остальные страны.

В формулах возможно использование смешанной адресации, при которой один из компонентов адреса абсолютный, а другой — относительный. Например, в адресе `»B»$2` компонент по столбцу относительный, а компонент по строке абсолютный.

В ячейке `»B»1` электронной таблицы находится формула `=»E»1+`$`»E»2`. Какой вид приобретёт формула после того, как содержимое ячейки `»B»1` скопируют в ячейку `»C»1`?

Так как относительное положение ячейки по строкам не изменилось, и в формуле используется относительная адресация по строкам, то строковая компонента нового адреса остаётся прежней и имеет вид `=»X»1+»Y»2`, где адрес по столбцам `»X»` и `»Y»` необходимо определить. Адрес столбца ячейки `»E»1` относительный, и так как ячейка `»B»1` копируется в `»C»1` со смещением в один столбец вправо, то новый адрес столбца ячейки `»E»1` будет `»F»`. Адрес столбца ячейки `$»E»2` абсолютный, значит, он остаётся неизменным и равным `»E»`. Итак, получаем, что формула в новой ячейке принимает вид `=»F»1+`$`»E»2`.

В ячейке `»C»2` записана формула `=$»B»$3+»D»2`. Какой вид она приобретёт после того, как содержимое ячейки `»C»2` скопируют в ячейку `»B»1`?

Адрес первой ячейки `$»B»$3` абсолютный, значит, он не изменится. Адрес ячейки `»D»2` относительный по строке и по столбцу. Ячейка `»D»2` располагается в той же строке, что и ячейка `»C»2`, но в другом столбце `»D»` со смещением вправо на один столбец. Значит, адрес столбца ячейки `»D»2` в ячейке `»B»1` станет `»C»`, а по строке станет `1`, и полный вид формулы будет выглядеть `=$»B»$3+»C»1`.

Абсолютная, относительная и смешанная адресация ячеек и блоков

При обращении к ячейке можно использовать описанные ранее способы: ВЗ, А1:С9 и т. д. Такая адресация называется относи­тельной. При ее использовании в формулах Excel запоминает рас­положение относительно текущей ячейки. Так, например, когда вы вводите формулу =В1+В2в ячейку В4, то Excel интерпретирует формулу как «прибавить содержимое ячейки, расположенной тре­мя рядами выше, к содержимому ячейки, расположенной двумя рядами выше относительно ячейки В4».

Если протянули формулу по строке из ячейки В4 =В1+В2 в ячейку С4. Excel также интерпретирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячей­ки двумя рядами выше, но уже по отношению к ячейке С4». Таким образом, формула в ячейке С4примет вид =С1+С2.Если протянули формулу по столбцуизячейки В4 в В5то формула в ячейкеВ5примет вид=В2+В3Как правило, формулу протягивают по столбцу, чтобы ее не повторять для каждой строкиданных.

Читать еще:  Замена mac адреса сетевой карты

Если при протягивании формул вы пожелаете сохранить ссылку на конкретнуюячейку или область, то вам необходимо воспользо­ваться абсолютной адресацией. Для ее задания необходимо перед именем столбца и веред номером строки ввести символ $. Напри­мер: $В$4или $C$2:$F$48и т д.

Смешанная адресация. Символ $ ставится только там, где он необходим. Например: В$4 или $С2. Тогда при копировании один параметр адреса изменяется, а другой — нет.

Задание 2. Заполните основную и вспомогательную таблицы.

2.1. Заполните шапку основной таблицы, начиная с ячейки А1:

— в ячейку А1 занесите №;

— в ячейку В1 занесите х;

— в ячейку С1 занесите к и т. д.

— установите ширину столбцов такой, чтобы надписи были вид­ны полностью.

2.2.Заполните вспомогательную таблицу начальными исходными данными, начиная с ячейки H1:

где x0 — начальное значение x;

step — шаг изменения x;

k — коэффициент (константа).

2.3. Используя функцию автозаполнения, заполните столбец А числами от 1 до 21, начиная с ячейки А2 и заканчивая ячейкой А22.

2.4. Заполните столбец В значениями х. Для этого:

— в ячейку В2 занесите =$Н$2. Это означает, что в ячейку В2
заносится значение из ячейки Н2 (начальное значение x), знак $
указывает на абсолютную адресацию;

— в ячейку ВЗ занесите =В2+$1$2. Это означает, что начальное значение x будет увеличено на величину шага, которая берется из ячейки I2;

— протяните формулу из ячейки ВЗ в ячейки В4: В22 с по­-
мощью операции заполнения. Столбец заполнится значениями x от
-2 до 2 с шагом 0,2.

2.5. Заполните столбец C значениями коэффициента k. Для этого:

— в ячейку С2 занесите =$J$2;

— в ячейку СЗ занесите =С2. Посмотрите на введенные формулы. Почему они так записаны?

-протяните формулу из ячейки СЗ в ячейки С4:С22. Весь
столбец заполнился значением 10.

2.6. Заполните столбец D значениями функции у1=х ^ 2-1. Для этого:

— в ячейку D2 занесите =В2^2 -1;

— протяните формулу из ячейки D2 в ячейки DЗ:D22. Столбец заполнился как положительными, так и отрицательными зна­чениями функции y1. Начальное и конечное значения равны3.

2.7. Аналогичным образом заполните столбец Е значениями
функции y2=x ^ 2+1.

Проверьте! Все значения положительные, начальное и конечное зна­чения равны 5.

2.8. Заполните столбец F значениями функции y=k*(x ^ 2-
1)/(x^2+1).
Для этого:

— в ячейку F2 занесите =С2*(D2/Е2);

-протяните формулу из F2 в ячейки F2:F22. используя
операцию заполнения.

Проверьте! Значения функции как положительные, так и отрица­тельные; начальное и конечное значения равны 6.

Задание 3.Посмотрите за изменениями в основной таблице при смене данных во вспомогательной. Для этого:

— измените во вспомогательной таблице начальное значение
x : в ячейку H2 занесите -5.

— измените значение шага: в ячейку I2 занесите 2.

— измените значение коэффициента: в ячейку J2занесите 1.
Внимание! При всех изменениях данных во вспомогательной табли­це в основной таблице пересчет производится автоматически.

— прежде чем продолжить работу, верните прежние началь­ные значения во вспомогательной таблице: x0=-2, step=0,2,к=10.

Задание 4. Оформите основную и вспомогательную таблицы.

4.1. Вставьте две пустые строки сверху для оформления заголовков. Для этого:

— установите курсор в любую ячейку строки номер 1;

— выполните команды меню Вставка, Строки (2 раза).

4.2. Введите заголовки:

— в ячейку А1: Таблицы;

— в ячейку А2: основная;

— в ячейку Н2: вспомогательная.

4.3.Объедините ячейки А1:J1 и разместите заголовок «Таб­лицы» по центру. Для этого:

— выделите блок А1: J1;

— используйте кнопку Объединить и поместить в центре
на панели инструментов Форматирование.

4.4. Аналогичным образом разместите по центру заголовки «основная»и«вспомогательная».

Шрифтовое оформление текста

Символы любой ячейки или блока можно оформить разными шрифтами. Для этого необходимо выделить ячейку или блок, а за­тем воспользоваться кнопками на панели инструментов Формати­рование или командой меню Формат, Ячейки. Шрифт.

4.5 .Оформите заголовки определенными шрифтами:

— для заголовка «Таблицы» задайте шрифт Courier New Cyr,
размер шрифта 14, полужирный. Используйте кнопки панели
инструментов Форматирование;

— для заголовков «основная» и «вспомогательная» задайте шрифт, Courier New Cyr размер шрифта 12, полужирный.
Используйте команды меню Формат, Ячейки, Шрифт;для шапок таблиц установите шрифт Courier New Cyr, размер шрифта 12, курсив. Любым способом.

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

Выравнивание

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

Для задания необходимой ориентации используются кнопки в панели инструментов Форматированиеили команда меню Фор­мат. Ячейки, Выравнивание.

4.7. Произведите выравнивание надписей шапок по центру.

Рамки

Для задания рамки используется кнопка Границыв панели Форматированиеили команда меню Формат, Ячейки, Граница.

4.8.Оформитерамки для основной и вспомогательной таблиц.

Фон

Содержимое любой ячейки или блока может иметь необходи­мый фон (тип штриховки, цвет штриховки, цвет фона).

Для задания фона используется кнопка Цвет заливкив панели Форматированиеили команда меню Формат, Ячейки, Вид.

Читать еще:  Лист ip адресов

4.9. Задайте фон заполнения внутри таблиц — светло-желтый,
фон заполнения шапок таблиц — лиловый.

Вид экрана после выполнения работы представлен на рис. 2. 1.

Рис. 2.1

Задание 5. Сохраните результаты работы на диске C:Мои документы Работа в файле work2-1

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

Защита ячеек

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

Установка защиты выполняется в два действия:

1) отключают защиту (блокировку) с ячеек, подлежащих по­следующей корректировке;

2) включают защиту листа или книги.

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

Отключение защиты ячеек.

Выделите блок. Выполните команду Формат, Ячейки. За­щита, а затем в диалоговом окне выключите (включите) параметр Защищаемая ячейка.

Относительные, абсолютные и смешанные ссылки на ячейки в Excel

Этот материал предназначен для начинающих и подготовлен с участием Анны Ивановой

Ссылка в Excel – это адрес ячейки или диапазона ячеек.

В Excel есть два вида стиля ссылок:

  • Классический (или А1)
  • Стиль ссылок R1C1; здесь R — row (строка), C — column (столбец).

Включить стиль ссылок R1C1 можно в настройках Сервис —> Параметры Excel —> закладка Формулы —> галочка Стиль ссылок R1C1:

Рис. 1. Настройка стиля ссылок

Скачать заметку в формате Word, примеры в формате Excel

Стиль R1C1 используется реже, в основном из-за того, что он менее нагляден. Однако он становится незаменим, если адрес ячейки является результатом вычислений (см. пример использования стиля R1C1 в заметке Excel. Использование ДВССЫЛ для транспонирования строк в столбцы с сохранением формул)

Ссылки в Excel бывают трех типов:

  • Относительные ссылки; например, A1;
  • Абсолютные ссылки; например, $A$1;
  • Смешанные ссылки; например, $A1 или A$1 (они наполовину относительные, наполовину абсолютные).

«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной (рис. 2А). Здесь в ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше. При «протаскивании» формулы, например, в ячейку А7 (рис. 2Б) формула самопроизвольно изменяется.

Рис. 2. Относительные ссылки

Знак $ перед буквой или цифрой в обозначении ячейки говорит о том, что эта часть обозначения является абсолютной, то есть не будет изменяться при изменении ячейки, из которой делается ссылка. Сравните, как ведут себя формулы на рис. 2 и рис. 3. При «протаскивании» формула не меняется: и из ячейки А6, и из ячейки А7 ссылка идет на ячейки С2 и С3.

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

Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и номером строки, например $A$1.Более быстрый способ – выделить относительную ссылку и нажать один раз клавишу F4, при этом Excel сам проставит знак $. Если второй раз нажать F4, ссылка станет смешанной типа A$1, если третий раз – смешанной типа $A1, если в четвертый раз – ссылка опять станет относительной. И так по кругу.

Смешанные ссылки

Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Иногда возникает необходимость закрепить адрес ячейки только по строке или только по столбцу. В таких случаях на помощь приходят смешанные ссылки. Рассмотрим их подробнее.

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

Рис. 4. Расчет значений в таблице с использованием смешанных ссылок; цена за штуку – закупочная цена; в столбцах D, E и F показаны отпускные цены при различных наценках.

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

При «протаскивании» формулы по столбцам нам необходимо, чтобы столбец С был зафиксирован. Аналогично, при «протаскивании» формулы по строкам, нам необходимо зафиксировать строку 3. В ячейке D4 таким образом получилась формула =$C4*(1+D$3); абсолютные ссылки я выделил жирностью и цветом. При протаскивании по диапазону D4:F6 такая формула дает правильные значения в каждой ячейке диапазона.

Понятие абсолютной, смешанной и относительной адресации

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

Относительные: ссылка полностью относительна. При копировании формулы ссылка на ячейку обновляется в соответствии с новыми ячейками. Например: А1.

Рис.9. Пример копирования формул с относительными ссылками

(копировалась формула =А1+1)

Абсолютные: ссылка полностью абсолютна. При копировании формулы в другую ячейку ссылка не изменяется. Пример: $A$1.

Рис.10. Пример копирования формул с абсолютными ссылками

Читать еще:  Виды адресации ячеек

Смешанные: абсолютная строка и абсолютный столбец.

Абсолютная строка: ссылка частично абсолютна. При копировании формулы изменяется только часть ссылки, относящаяся к столбцу. Та часть, которая относится к строке, остается неизменной. Пример: A$1.

Рис.11. Пример копирования формул со смешанной ссылкой

Абсолютный столбец: ссылка частично абсолютна. При копировании формулы изменяется только часть ссылки, относящаяся к строке. Та часть, которая относится к столбцу, остается неизменной. Пример: $A1.

Рис.12. Пример копирования формул со смешанной ссылкой

ПРИМЕР 1. Определить стоимость товара.

Для определения стоимости товара нужно количество товара умножить на цену. Введем в клетку D3 формулу =В3*С3 и скопируем ее в клетки D4 и D5. При копировании относительные адреса изменяются и в клетке D4 будет записана формула =В4*С4, а в клетке D5 — формула =В5*С5.

ПРИМЕР 2. Определить стоимость электроэнергии за каждый месяц и общую стоимость за весь год.

Стоимость 1 квт/часа величина постоянная и нет необходимости целый столбец заполнять одним и тем же значением. Запишите его в клетку D2 – число 1,23.

Для определения стоимости электроэнергии нужно количество кВт умножить на стоимость 1 кВт/часа.

Если в клетку С5 ввести формулу =В5*D2, а затем скопировать, то в клетке С6 будет формула =В6*D3, в клетке С7 — формула =В7*D4 и т.д., т.е. результат будет неверным. Чтобы этого не произошло, координату клетки D2 нужно записать в абсолютных адресах. Формула будет иметь вид =В5*$D$2.

Введите в ячейку С5 формулу, по которой рассчитывается стоимость электроэнергии, потребленной за январь.

Скопируйте формулу в ячейки С6:С16. В ячейку С17 введите функции, подсчитывающую суммарную стоимость электроэнергии. Для определения итоговой стоимости в клетке С17 используется Автосуммирование — кнопка на вкладке Главная в группе команд Редактирование.

Абсолютная и относительная адресация ячеек

Презентация к уроку

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

Обучающие цели урока:

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

Развивающие цели урока:

  1. Развитие умения обобщать полученные знания и последовательно их применять в процессе выполнения работы.
  2. Развитие умения пользоваться различными видами адресации при решении различных типов задач

Воспитательные цели урока:

  1. Привитие навыков вычислительной работы в ЭТ Excel.
  2. Воспитание аккуратности и точности при записи математических формул.

Тип урока: освоение и закрепление нового материала.

План урока

  1. Организационный момент
  2. Активизация опорных ЗУН учащихся
  3. Тест по основным терминам электронных таблиц
  4. Приобретение новых умений и навыков.
  5. Практическая работа.
  6. Подведение итогов, выставление оценок.

Ход урока

I. Организационный момент

На ваших столах лежат карточки двух цветов: красного и зеленого.

— Карточка красного цвета означает:

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

— Карточка зеленого цвета означает:

«Пользы от урока было мало: я не очень понимал, о чем идет речь, к ответу на уроке я был не готов»

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

II. Активизация опорных ЗУН учащихся

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

После повторения основных базовых понятий электронных таблиц каждый ученик получает контрольный лист (Приложение 1) и отвечает на вопросы (7 мин.)

III. Приобретение новых умений и навыков

Формулы представляют собой выражения, по которым выполняются вычисления на рабочем листе. Формула начинается со знака равенства (=). В качестве аргументов формулы обычно используются значения ячеек, например: =A1+B1.

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

Адрес ячейки, изменяющийся при копировании формулы называется относительным. Он имеет вид В1, А3. По умолчанию программа Excel рассматривает адреса ячеек как относительные.
Относительная адресация ячеек – обычное явление при вычислениях по формулам. При копировании такой формулы вправо или влево будет изменяться заголовок столбца в имени ячейки, а при копировании вверх или вниз – номер строки.

Абсолютная адресация используется в том случае, когда нужно использовать значение, которое не будет меняться в процессе вычислений. Тогда записывают, например, так: =$А$5. Соответственно, при копировании такой формулы в другие ячейки текущего рабочего листа, в них всегда будет значение =$А$5. Для того, чтобы задать ячейке абсолютный адрес, необходимо перед номером строки и номером столбца указать символ “$” либо нажать клавишу F4.

Смешанная адресация представляет собой комбинацию относительной и абсолютной адресаций, когда одна из составляющих имени ячейки остается неизменной при копировании. Примеры такой адресации: $A3, B$1.

IV. Практическая работа

Учащимся предлагается выполнить практическую работу «Накладная на покупку канцтоваров». Для выполнения задания каждому учащемуся раздается задание с необходимым комментарием к выполнению задания

Дополнительное задание «Таблица умножения»:

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×