Letysite.ru

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

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

Практическая работа. «Microsoft Excel 2007. Абсолютная и относительная адресация»

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

«Microsoft Excel 2007. Абсолютная и относительная адресация»

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

При изменении позиции ячейки, содержащей формулу, изменяется и ссылка.

При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется.

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

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

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

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

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

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.

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

Создайте следующую таблицу. Заполните нужные ячейки формулами, воспользуйтесь относительными, абсолютными или смешанными ссылками при автозаполнении формул. Для товаров, стоимость которых с учетом их количества превышает 500$, установите скидку в 1%, используя функцию «ЕСЛИ» (информацию о данной функции найдите в справке).

Расчет приобретенных компанией канцелярских средств оргтехники

Курс $ = 26,89 руб.

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

Построить электронную таблицу расчета величины динамики установления равновесия Y n+1 (см. рис. ниже) и исследовать изменения данной величины в зависимости от величины параметра C, а также начального значения Y n , для этого:

Внести в таблицу начальные значения для параметра С (значение равно 6,5) и цены (значение равно 2,8).

Заполнить временной столбец n значениями от 0 до 100.

Произвести по формуле расчет величины динамики установления равновесия

Рассчитать среднюю цену и дисперсию цены, по соответствующим формулам.

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

Изменяя начальные значения параметра С, выявить влияние параметра С на процесс установления равновесной рыночной цены.

Переименуйте новый лист в книге Excel , например, назовите его Ссылки . Зайдите в него, и в ячейки от A1 до A5 , а также от B1 до B5 , введите какие-нибудь числа. В ячейке C1 напишите: =A1+B1 Нажмите Enter . Ячейка покажет сумму.

Теперь выделите эту ячейку, наведите курсор на нижний правый угол (там, где стоит точка), нажмите левой клавишей мыши и, не отпуская, протяните вниз до ячейки C5 . В ячейках от C1 до C5 появятся суммы, причем в ячейке C2 будет сумма ячеек A2 и B2 , в ячейке C3 будет сумма ячеек A3 и B3 и так далее. То же самое произойдет, если Вы скопируете ячейку C1 в ячейку C5 , например. Вы видите, что адреса ячеек в формулах изменяются. Это потому, что данные адреса ячеек в формулах являются относительными ссылками Excel .

Теперь представьте себе ситуацию: все ячейки с суммой нужно умножить на содержимое ячейки D2 . Введите в ячейку D2 какое-нибудь число, в ячейке C1 вставьте курсор в строку формул Excel, заключите сумму в скобки, и допишите *D2 . Должно получиться: =(A1+B1)*D2 Результат в ячейке C1 Вы увидите, но если Вы скопируете ячейку C1 в ячейки ниже, ничего не получится, потому что ссылка на ячейку D2 превратится в ссылку на ячейку D3 и так далее.

Как быть в этой ситуации? Нужно относительную ссылку D2 превратить в абсолютную. В абсолютную ссылку Excel она превращается путем добавления знака $ перед D и перед 2 , то есть абсолютная ссылка выглядит так: $D$2 То есть в ячейке C1 формула должна выглядеть так: =(A1+B1)*$D$2

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

Кроме относительных и абсолютных ссылок в Excel есть еще смешанные ссылки вида: $D2 или D$2 Для иллюстрации работы со смешанными ссылками Excel сделаем таблицу умножения. Создайте новый лист, на нем в ячейку A1 поставьте цифру 1 , в ячейку B1 поставьте цифру 2 , выделите обе ячейки, наведите курсор на точку в правом нижнем углу обрамления, и протяните в сторону, до ячейки I1 . У Вас получится ряд цифр от 1 до 9 . Точно так же поставьте цифры от 1 до 9 в ячейки от A1 до A9 . В ячейку B2 поставьте: =B1*A2 и протяните до ячейки I9 (сразу не получится, протяните сначала по горизонтали, потом по вертикали). То, что Вы увидите, явно не будет таблицей умножения, потому что относительные ссылки Excel в формуле каждой ячейки изменяются не так, как нам нужно.

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

Например, в ячейке C3 будет: =C2*B3 А должно быть: =C1*A3

Заметьте, при переходе из ячейки B2 в ячейку C3 в формуле

первый множитель B1 должен был преобразоваться в C1 , а

второй множитель A2 должен был преобразоваться в A3 .

Значит, делаем вывод: в первом множителе должна изменяться только буква, а во втором — только цифра.

Теперь измените формулу в ячейке B2 , чтобы она была такой:

=B$1*$A2 Таким образом, Вы делаете неизменными в первом множителе букву, а во втором множителе — цифру с помощью смешанных ссылок Excel . Протяните теперь ячейку B2 до ячейки I9 . Вы увидите, что результат будет достигнут: таблица умножения будет сделана правильно.

Относительная и абсолютная адресация в MS Excel

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

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

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

Обозначение ячейки, составленное из номера столбца и номера строки, называется относительным адресом или просто ссылкой или адресом. Например, А1, С12.

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

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

Если ссылка на ячейку не должна изменяться ни при каких копированиях, то вводят абсолютный адрес ячейки. Абсолютный адрес создается из относительной ссылки путем вставки знака доллара ($) перед заголовком столбца и/или номером столбца. Например, $A$1, $B$2. Иногда используют смешанный адрес, в котором постоянным является только один из компонентов. Например, на рисунке 2 при копировании формулы из ячейки С1 в ячейку С2 получится следующая формула: =$А2+В$1.

На рисунке 3 показан пример использования абсолютной адресации.

Ячейки могут содержать данные различного формата. Например, на рисунке 6 в ячейке В2 данные имеют процентный формат, в В5 – денежный, в С5 – числовой, в А2 – текстовый.

Для форматирования ячеек необходимо:

— выделить одну или несколько ячеек;

— открыть окно «Формат ячейки»;

— выбрать формат на вкладке Число.

В окне «Формат ячейки» на вкладке Выравнивание можно задать не только выравнивание по вертикали и горизонтали, но и объединение ячеек, перенос по словам, ориентацию текста.

Для заполнения пустых ячеек данными используют маркер заполнения. Маркер заполнения – небольшой черный квадрат, расположенный в нижнем правом углу выделенной ячейки или диапазона ячеек . Маркер заполнения используется для копирования или автозаполнения соседних ячеек данными выделенного диапазона по правилам, зависящим от содержимого выделенных ячеек. Например, на рисунке 4 показан результат копирования данных ячеек А1-В1 маркером заполнения.

а) до копирования б) после копирования

В Excel существует множество стандартных функций, правильно использовать которые помогает мастер функций (Рисунок 5). Вызвать мастера функций можно пиктограммой или через меню Вставка / Функция.

Рассмотрим некоторые функции.

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

Функция ЕСЛИприменяется для вывода в ячейку значения в зависимости от выполнения условия. Окно для определения аргументов функции представлено на рисунке 7. В результате функция будет иметь вид: ЕСЛИ(B4>=$B$1;»Выполнила»; «Не выполнила»). Реализация этой функции показана на рисунке 8. Как проведено форматирование ячеек А3-С3 этого документа, показано на рисунке 7.

Функция СЧЕТЕСЛИ вычисляет количество ячеек диапазона, удовлетворяющих заданному условию. Например, чтобы определить количество бригад, выполнивших план (Рисунок 9), можно определить аргументы функции так, как показано на рисунке 13. Функция будет иметь вид: =СЧЁТЕСЛИ(C4:C7;»Выполнила»).

Функция ВПРпозволяет выбрать значение в таблице по заданному ключу. Например, на рисунке 10 ячейки С11-С14 заполнены с помощью функции ВПР. Окно определения аргументов функции показано на рисунке 11. Ячейки В3-С8 определяют таблицу выбора (тарифную сетку) для каждой ячейки С11-С14 (тарифной ставки), поэтому перед копированием формулы на ячейки В3 и С8 установлена смешенная адресация (В$3:С$8).

МS Excel.Относительная и абсолютная адресация ячеек.

Ячейки и их адресация. На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (в этом порядке), на пересечении которых она расположена, например: А1 или DE234. Обозначение ячейки (ее номер) выполняет функции ее адреса. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках.

Читать еще:  Отправка на внешние адреса запрещена

Одна из ячеек всегда является активной и выделяется рамкой активной ячейки. Эта рамка в программе Excel играет роль курсора. Операции ввода и редактирования всегда производятся в активной ячейке. Переместить рамку активной ячейки можно с помощью курсорных клавиш или указателя мыши.

Диапазон ячеек.На данные, расположенные в соседних ячейках, можно ссылаться в формулах, как на единое целое. Такую группу ячеек называют диапазоном. Наиболее часто используют прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например: А1:С15.

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

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

Пусть, например, в ячейке В2 имеется ссылка на ячейку АЗ. В относительном представлении можно сказать, что ссылка указывает на ячейку, которая располагается на один столбец левее и на одну строку ниже данной. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку ЕА27 ссылка будет продолжать указывать на ячейку, располагающуюся левее и ниже, в данном случае на ячейку DZ28.

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

Относительные, абсолютные и смешанные ссылки на ячейки в 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 такая формула дает правильные значения в каждой ячейке диапазона.

Читать еще:  Url адрес сервера mdm

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

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

Относительная ссылка – ссылка, в которой координаты ячейки меняются при копировании формулы в другую ячейку.

Абсолютная ссылка – ссылка, в которой координаты ячейки зафиксированы и не меняется при копировании.

Относительные ссылки

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

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

Вот что нам нужно сделать:

  1. Переходим в самую верхнюю ячейку результирующего столбца (не считая шапки таблицы), ставим знак “равно” (“=”) и пишем в ней формулу: = B2*C2 .
  2. Когда выражение готово, нажимаем клавишу Enter на клавиатуре, после чего получаем результат в ячейке с формулой.
  3. Остается выполнить аналогичные расчеты в других ячейках столбца. Конечно же, если таблица небольшая, можно перейти в следующую ячейку и выполнить шаги 1-2, описанные выше. Но что делать, когда данных слишком много? Ведь на ручной ввод формул во все ячейки уйдет немало времени. На этот случай в Excel предусмотрена крайне полезная функция, позволяющая скопировать формулу в другие ячейки. Для этого наводим указатель мыши на правый нижний угол ячейки с результатом, и когда появится небольшой черный крестик (маркер заполнения), зажав левую кнопку мыши тянем его вниз, тем самым копируя формулу в другие ячейки.
  4. Отпустив кнопку мыши мы получим результаты во всех ячейках столбца, на которые растянули формулу.
  5. Если мы перейдем, например, в ячейку D3, то увидим в строке формул следующее выражение: =B3*C3 .Т.е. при копировании изменились координаты ячеек, участвующих в исходной формуле, которую мы записали в ячейку D2. Это результат того, что ссылки были относительными.

Возможные ошибки при работе с относительными ссылками

Безусловно, благодаря относительным ссылкам существенно упрощаются многие расчеты в Эксель. Однако, они не всегда помогают решить поставленную задачу.

Допустим, нам нужно посчитать долю каждого наименования в общих продажах.

  1. Встаем в первую ячейку столбца для расчетов, где пишем формулу: =D2/D13 .
  2. Нажимаем Enter, чтобы получить результат. После того, как мы скопируем формулу на оставшиеся ячейки столбца, вместо результатов увидим следующую ошибку: #ДЕЛ/0! .

Дело в том, что из-за того, что все ссылки на ячейки в формуле, которую мы скопировали, относительные, координаты в последующих ячейках сдвинулись. Т.е. для ячейки E3 формула выглядит следующим образом: =D3/D14 . Но, как мы видим, ячейка D14 – пустая, из-за чего программа и выдает ошибку, информирующую о том, что делить на цифру нельзя.

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

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

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

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

  1. Для начала пишем формулу в привычном виде в требуемой ячейке. В нашем случае она выглядит так: = D2/D13 .
  2. Когда формула готова, не спешим нажимать клавишу Enter. Теперь нам нужно зафиксировать координаты ячейки D13. Для этого перед названием столбца и порядковым номером строки печатаем символ “$”. Или же можно просто после ввода адреса сразу нажать клавишу F4 на клавиатуре (курсор может находиться до, после или внутри координат). В итоге формула должна выглядеть следующим образом: D2/$D$13 .
  3. Теперь можно нажать Enter, чтобы вывести результат в ячейку.
  4. Остается только скопировать формулу с помощью маркера заполнения на нижние строки. На этот раз, благодаря тому, что мы зафиксировали ячейку с итоговой суммой, результат появится и в других ячейках.

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

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

  • Если мы напишем ссылку как “$G5”, это означает, что будет меняться строка, а столбец будет зафиксирован.
  • Если мы укажем “G$5”, в этом случае, фиксироваться будет номер строки, в то время, как столбец будет меняться.

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

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

Заключение

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

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