Foreversoft.ru

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

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

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

Одной из функций программы excel 2010 является работа с формулами, а поскольку в формулах указываются не значения, а адреса ячеек, в которых находятся значения, значит, этот вопрос достоин внимания. Почему нужны абсолютные и относительные адреса ячеек в Excel и как вообще назначаются адреса?

Для чего нужно указывать абсолютный адрес ячейки?

Если бы использовались абсолютные адреса, Excel понял бы формулу в ячейки A3 таким образом: взять данные из A1, прибавить их к данным в A2. Но благодаря относительной адресации программа понимает формулу так: взять данные из ячейки, которая находится в том же столбце, что и текущая, на две строки выше. Далее, прибавить эти данные к тем, что находятся в ячейке, расположенной в том же столбце на одну строку выше.

То есть, если вы скопируете формулу из ячейки A3 в B3, то она автоматически изменится на «=B1+B2«. Это и есть относительная адресация.

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

Абсолютные ссылки строятся по следующей схеме:

$A1 — Знак доллара означает необходимость использовать адрес столбца А в качестве абсолютного, строка может меняться.

B$1 — Знак доллара в этом примере означает использовать адрес строки 1в качестве постоянного, столбец может меняться.

$C$1 — двойное использование знака доллара в формуле означает абсолютный адрес ячейки.

Откройте файл Лаборатоная2.

На листе Задание 1 приведен прайс лист с оптовыми ценами за товар.

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

В столбце F вычислите розничную цену товара используя абсолютный адрес ячейки D1 (Торговая наценка). Для этого в ячейку F12 введите формулу: =С12*$D$1. Растяните формулу при помощи маркера автозаполнения до конца диапазона. Посмотрите как изменяется формула в разных ячейках диапазона.

Укажите, что все цены указаны в рублях.

На листе Задание 2 приведена часть прайса. Необходимо вычислить стоимость каждого товара в зависимости от его количества.

Формула должна быть введена в ячейку D6 и при помощи маркера автозаполнения ей нужно заполнить всю таблицу.

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

Подсказка!

Если данные располагаются в столбце, то фиксируется адрес столбца, а если данные располагаются в строке, то и в формуле фиксируется адрес этой строки.

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

На листе Задание 4 при помощи представленной таблицы создадим Таблицу умножения Пифагора.

Введите в ячейку D5 следующую формулу:

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

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Только сон приблежает студента к концу лекции. А чужой храп его отдаляет. 9274 — | 7852 — или читать все.

Абсолютные относительные адреса ячеек в формуле

Перед началом работы с формулами первое, что нужно изучить – это адресацию ссылок на ячейки. Чтобы уметь правильно обращаться к данным. И только потом уже следует изучать функции.

Если в ячейке введен первый символ знак «=», то все что будет введено дальше, для Excel является одним из параметров:

Причину ошибки иногда очень сложно отследить в длинных формулах и тогда их следует прослеживать пошагово.

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

Допустим, нам нужно посчитать стоимость за сутки проживания в отелю Еврозоне. Все цены представлены в валюте евро сконвертируем в рубли по курсу 1 EUR = 74 RUB. Курсы валют изменяются каждый день, поэтому нужно сделать прайс так, чтобы можно было легко и просто вычислить актуальные цены в рублях. Вычислим цены с помощью формул с абсолютными или смешанными адресами ячеек так, чтобы не нужно было изменять в них формулы при каждом изменении курса валют. Изменения будут вноситься только в одну ячейку. Для этого введем формулу: =B2*$D$2 или: =B2*D$2, в ячейку С2 как показано на рисунке:

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

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

Горячие клавиши абсолютной ссылки

Чтобы упростить ввод разного типа ссылок в Excel можно воспользоваться горячей клавишей F4, которая при поочередном нажатии работает как переключатель между типами ссылок:

  • абсолютный;
  • смешанный с фиксированной строкой;
  • смешанный с фиксированным столбцом;
  • относительный.

Чтобы убедиться, как это удобно работает, сделайте следующее:

  1. В любой пустой ячейке введите знак равно (=).
  2. Щелкните левой кнопкой мышки по любой дугой ячейке, на которую будет ссылаться исходная ячейка. В строе формул отобразиться ее адрес.
  3. Переодически нажимайте клавишу F4 и наблюдайте над тем, как меняется тип ссылки (сначала на абсолютный тип и т.д.).

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

Читать еще:  Процессор pentium шина адреса

Карта урока

Класс 10 «а»

Тема урока «Абсолютная и относительная адресация»

Цели урока

Научить использовать абсолютную и относительную адресацию в решении задач.

Задачи урока:

· изучить понятия абсолютной и относительной адресации;

· продолжить формирование общеучебных умений и навыков ( навыки вычислительной работы в ЭТ, н авык оценки своих возможностей);

· развивать представление об ЭТ как инструменте для решения задач из разных сфер человеческой деятельности.

Тип урока комбинированный.

Образовательная технология компьютерная технология.

Методы и приемы обучения:

· словесный: рассказ, объяснение;

· наглядный: демонстрация приемов работы, создание опорного конспекта;

· практический: тест, профессиональная проба.

Материально-техническое обеспечение урока:

· тестирующая программа My TestX .

План урока:

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

II . Проверка домашнего задания (электронный дневник);

II I . Актуализация знаний (компьютерное тестирование);

I V . Изучение нового материала;

V . Практическая работа (профессиональная проба);

V I . Подведение итогов урока;

V I I . Домашнее задание.

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

Здравствуйте, очень рада вас видеть! Садитесь.

II Проверка домашнего задания

(Результаты домашнего задания демонстрируются через электронный дневник)

Домашнее задание, заданное к сегодняшнему уроку я проверила. Оценки вы можете видеть на экране – большинство с заданием справились, а это значит, что у вас не составит труда выполнить тест, основные понятия которого необходимы для успешной работы на этом уроке. Сейчас у вас открыт тест, который состоит из 5 вопросов, которые отображаются в случайном порядке, поэтому у рядом сидящих не будет одинаковых вопросов. Обращаю ваше внимание на то, что вопросы разные: с одиночным выбором ответа – в виде круглого чекбокса, и с множественным выбором — квадратным. Результаты тестирования будут автоматически сохранены.

III Актуализация знаний

Учащиеся выполняют компьютерный тест «Формулы»

Укажите неверную формулу:

2. Данные в электронной таблице могут быть:
текстом
числом
оператором
формулой

3. Формула в электронных таблицах может включать в себя:

знаки арифметических операций

4.Записать арифметическое выражение

=2^3*2,5/15*22
5. Укажите последовательность, в которой будут выполняться математические операции в формуле А=В2*3-А2/В3

Заканчиваем с выполнением теста…

Я вижу вы закончили. Можете закрыть.

Встаньте те, кто получил 4 и 5. Результаты тестирования сохранены.

IV Объяснение нового материала

На столах у учащихся рабочие тетради.

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

Как вы думаете, в каких профессиях необходимы знания электронных таблиц?

Статист, бухгалтер, инженер, кассир – все эти профессии объединяет принадлежность к типу профессиональной направленности «Человек – знаковая система». В современном мире любой бухгалтер, статист должен обладать навыками оператора ЭВМ.

В мире насчитывается более 50 тыс. профессий. Как выбрать одну единственную? Как вы поймете, что она вам подходит? (проф тестирование, консультирование, советы родителей, ориентация на свои ощущения)

В этом вам помогут профессиональные пробы, которые моделируют основные моменты профессии. На сегодняшнем уроке я вам предлагаю почувствовать себя в роли оператора ЭВМ, чтобы удовлетворить вашу познавательную потребность – профпригодны ли вы к данной профессии.

В рамках профпробы у нас стоит цель: создать продукт деятельности оператора ЭВМ: составить смету расходов.

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

Откройте рабочие тетради. Тема урока «Абсолютная и относительная адресация».

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

Для вычисления заработка нужно просто перемножить попарно числа из второй (столбец В) и третей (столбец С) колонок. Результаты вычислений должны быть в четвертой колонке (столбец D ). Итак, как будет выглядеть формула в ячейке D 4?

Вспоминаем правила записи формул =В4*С4

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

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

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

Если изменить какие-то числа в столбцах В и С, то числа в столбце D будут автоматически обновляться.

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

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

Сумму налога легко сосчитать по правилу «Сумма налога = заработок*ставка_налога». Указав соответствующие адреса ячеек, в ячейке Е4 записываем формулу = D4*С1 и копируем ее во все оставшиеся ячейки.

При этом получается неожиданный результат. В этом случае использование относительной адресации привело к ошибке. Почему?

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

Читать еще:  Адрес блока состоит из

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

Тогда формула для расчета суммы налога приобретает вид =D4*$C$1

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

Абсолютным может быть и часть адреса ячейки (только номер строки или номер столбца).

Пример смешанного адреса: $D1, B$6

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

Прежде чем начать профпробу, мне хочется напомнить Вам о том как важна хорошая осанка, так как позвоночник — это одна из проблемных зон оператора ЭВМ.

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

А вот теперь приступаем к профессиональной пробе. Не за горами самый любимый и долгожданный праздник. Какой? Новый год.

Итак, вы оператор ЭВМ, и вам поступил заказ от председателя профсоюзного комитета рассчитать стоимость расходов на новогодние детские подарки для членов профсоюза. Известно, что детей 27 человек. Задание в рабочей тетради.

Абсолютные и относительные ссылки в Excel

Абсолютные и относительные ссылки в Excel используются при создании формул, которые ссылаются на другую ячейку или диапазон. Если в формуле Excel используется знак доллара ($) в ссылках на ячейку, то многих пользователей Excel это путает. Но этот знак имеет простое объяснение – он обозначает, следует ли изменять или нет ссылку, когда формула копируется в другие ячейки, и в свою очередь информирует пользователя, что данная ссылка является абсолютной ссылкой. В данной статье мы подробно рассмотрим, как использовать данную возможность, чем отличаются относительные ссылки от абсолютных ссылок, и что такое смешанные ссылки.

Когда вы разберетесь в различии между абсолютными, относительными и смешанными ссылками, можно сказать, что вы на полпути к освоению мощности и универсальности формул и функций Excel.

Все вы, вероятно, видели знак доллара ($) в формулах Excel и задавались вопросом, что это такое. Действительно, вы можете ссылаться на одну и ту же ячейку четырьмя различными способами, например A1, $A$1, $A1 и A$1.

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

Что такое ссылка на ячейку?

Проще говоря, ссылка на ячейку в Excel является адресом ячейки. Он сообщает MS Excel, где искать значение, которое вы хотите использовать в формуле.

Например, если вы введете простую формулу =A1 в ячейку C1, Excel вытащит значение из ячейки A1 в C1:

Абсолютные и относительные ссылки в Excel – Ссылка на ячейку в Excel

Как уже упоминалось, если вы пишете формулу для одной ячейки , то вы можете использовать любой ссылочный тип, со знаком доллара(смешанная или абсолютная ссылка) или без него (относительная ссылка), результат будет таким же:

Абсолютные и относительные ссылки в Excel – Использование $ в формулах Excel

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

Относительная ссылка в Excel

Относительная ссылка – это обычная ссылка, которая содержит в себе букву (столбец) и номер (строка) без знака $, например, D14, G5, A3 и т.п. Основная особенность относительных ссылок заключается в том, что при копировании (заполнении) ячеек в электронной таблице, формулы, которые в них находятся, меняют адрес ячеек относительно нового места. По умолчанию все ссылки в Excel являются относительными ссылками. В следующем примере показано, как работают относительные ссылки.

Предположим, что у вас есть следующая формула в ячейке B1:

Если вы скопируете эту формулу в другую строку в том же столбце, например, в ячейку B2, формула будет корректироваться для строки 2 (A2*10), потому что Excel предполагает, что вы хотите умножить значение в каждой строке столбца А на 10.

Абсолютные и относительные ссылки в Excel – Относительная ссылка в Excel

Если вы копируете формулу с относительной ссылкой на ячейку в другой столбец в той же строке, Excel соответственно изменит ссылку на столбец :

Абсолютные и относительные ссылки в Excel – Копирование формулы с относительной ссылкой в другой столбец

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

Абсолютные и относительные ссылки в Excel – Копирование формулы с относительной ссылкой в другой столбец и другую строку

Как вы видите, использование относительных ссылок на ячейки в формулах Excel является очень удобным способом выполнения одних и тех же вычислений на всем рабочем листе. Чтобы лучше проиллюстрировать это, давайте рассмотрим конкретный пример относительной ссылки.

Пример относительных ссылок в Excel

Пусть у нас есть электронная таблица, в которой отражены наименование, цена, количество и стоимость товаров.

Читать еще:  Как правильно написать эл адрес

Абсолютные и относительные ссылки в Excel – Исходные данные

Нам нужно рассчитать стоимость для каждого товара. В ячейке D2 введем формулу, в которой перемножим цену товара А и количество проданных единиц. Формула в ячейке D2 ссылается на ячейку B2 и C2, которые являются относительными ссылками. При перетаскивании маркера заполнения вниз на ячейки, которые необходимо заполнить, формула автоматически изменяется.

Абсолютные и относительные ссылки в Excel – Относительные ссылки

Ниже представлены расчеты с наглядными формулами относительных ссылок.

Абсолютные и относительные ссылки в Excel – Относительные ссылки (режим формул)

Таким образом,относительная ссылка в Excel — это ссылка на ячейку, когда при копировании и переносе формул в другое место, в формулах меняется адрес ячеек относительно нового места.

Абсолютная ссылка в Excel

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

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

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка в Excel

Например, если у вас есть значение 10 в ячейке A1, и вы используете абсолютную ссылку на ячейку ($A$1), формула = $A$1+5 всегда будет возвращать число 15, независимо от того, в какие ячейки копируется формула. С другой стороны, если вы пишете ту же формулу с относительной ссылкой на ячейку (A1), а затем скопируете ее в другие ячейки в столбце, для каждой строки будет вычисляться другое значение. Следующее изображение демонстрирует разницу абсолютных и относительных ссылок в MS Excel:

Абсолютные и относительные ссылки в Excel – Разница между абсолютными и относительными ссылками в Excel

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

Пример использования абсолютной и относительных ссылок в Excel

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

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка

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

Абсолютные и относительные ссылки в Excel – Переключение между относительной, абсолютной ссылкой и смешанными ссылками

Или вы можете сделать абсолютную ссылку, введя символ $ вручную с клавиатуры.

Если вы используете Excel for Mac, то для преобразования относительной в абсолютную ссылку или в смешанные ссылки используйте сочетание клавиш COMMAND+T.

Абсолютные и относительные ссылки в Excel – Абсолютная ссылка (режим формул)

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

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

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

Как вы помните, абсолютная ссылка в Excel содержит 2 знака доллара ($), которые блокируют как столбец, так и строку. В смешанной ссылке фиксируется только одна координата (абсолютная), а другая (относительная) изменяется в зависимости от относительного положения строки или столбца:

  • Абсолютный столбец и относительная строка, например $A1. Когда формула со смешанной ссылкой копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы он никогда не менялся. Ссылка относительной строки, без знака доллара, изменяется в зависимости от строки, в которую копируется формула.
  • Относительный столбец и абсолютная строка, например A$1. В этой смешанной ссылке ссылка на строку не изменится, а ссылка на столбец будет меняться.

Абсолютные и относительные ссылки в Excel – Смешанные ссылки

Ниже вы представлен пример использования обоих типов смешанных ссылок.

Пример смешанных ссылок в Excel

Если нам нужно узнать какая будет стоимость с учетом 10%, 25% и 30% скидки, то в ячейку Е3 вводим формулу =$D3*(1-E$2), фиксируя таким образом столбец D (стоимость) и строку 2 (скидку). В данной формуле используются две смешанные ссылки:

$D3 — Абсолютный столбец и относительная строка

E$2 — Относительный столбец и абсолютная строка

Абсолютные и относительные ссылки в Excel – Пример использования смешанных ссылок

Заполняем ячейки по столбцам и по строкам. В режиме формул расчеты со смешанными ссылками будут выглядеть так:

Абсолютные и относительные ссылки в Excel – Пример использования смешанных ссылок (режим формул)

При заполнении диапазона E3:G6 такая формула со смешанными ссылками дает соответствующие значения в каждой ячейке.

Таким образом, абсолютная ссылка на ячейку при копировании или перемещении формулы не изменяется, а относительная ссылка автоматически перенастраивается на новую ячейку. В смешанной ссылке адрес ячейки закрепляют только либо по строке, либо по столбцу. Надеюсь, теперь вы в полной мере разобрались, что такое относительные и абсолютные ссылки на ячейки, а формула Excel с знаками $ больше не является для вас загадкой.

Ссылка на основную публикацию
Adblock
detector