22. Адресация в Excel: абсолютная, относительная и смешанная. Использование формул и функций – определения и примеры. Работа с Мастером формул
Адресация — это указание на данные находящиеся в определённой ячейке.
Использование адресации облегчает расчёты в таблицах Excel примерно в 2 раза.
Относительная — это адресация, при которой формула читается относительно текущего положения ячейки.
=а1*в1 – данную формулу, находящуюся в ячейке С1, ЭВМ для «себя» читает следующим образом: содержимое ячейки, находящейся на два столбца слева в той же строке, перемножить с содержимым ячейки находящейся на один столбец слева в той же строке.
Если эту формулу скопировать из ячейки С1 в ячейку С2, то ее «понимание для ЭВМ» остается точно таким же. Т.е. она возьмет ячейку, находящуюся на 2 столбца слева (а это будет ячейка а2), и перемножит ее с ячейкой находящейся на 1 столбец слева (это будет ячейка в2). Формула в ячейке С2 примет вид =а2*в2
Если эту формулу скопировать в ячейку С3, то она примет вид = а3*в3
Абсолютная – это адресация, при которой идёт указание на конкретную ячейку, адрес которой не изменяется.
$ — признак абсолютной адресации. $ — комбинация кнопок ( Shift + 4 ) в англ. языке.
Знак $ ставится в двух местах — и перед буквой столбца и перед номером строки.
Как видно формула в ячейке С2 содержит два вида адресации: и относительную (B2), и абсолютную ($D$2).
При копировании формулы из ячейки С2 в ячейку С3 относительная адресация измениться с B2 на B3. Абсолютная адресация останется такой же $D$2, т.к. абсолютная адресация не изменяется, она остается постоянной. Формула примет вид = B3*$D$2.
Если эту формулу скопировать в ячейку С4, то она примет вид = B4*$D$2.
Смешанная – адресация, при которой идёт изменение только одного адреса. Знак $ ставится только в одном месте, или перед буквой столбца или перед номером строки.
При копировании формул:
Относительная — адреса изменяются, относительно текущего положения ячейки.
Абсолютная — адрес не изменяется.
Смешанная — один адрес изменяется, другой нет.
23. Графические возможности Excel. Диаграмма и графики. Построение диаграмм в Excel.
Диаграмма- это графическое представление числовых данных. В Excel используются следующие основные понятия и определения, которые необходимо знать для правильного и эффективного построения диаграмм.
Ряды данных — это наборы значений, которые требуется изобразить на диаграмме (значения функции на оси Y). Например, при построении диаграммы дохода компании за последнее десятилетие рядом данных является набор значений дохода за каждый год.
Категории (аргументы функции на оси Х) служат для упорядочения значений в рядах данных.
Легенда — это условные обозначения значений различных рядов данных на диаграмме.
В Excel имеется около 20 типов двухмерных и трехмерных диаграмм, каждая из которых имеет несколько разновидностей. Рассмотрим некоторые из них:
диаграмма с областями — отдельные ряды данных представлены в виде закрашенных разными цветами областей.
линейчатая диаграмма — отдельные значения ряда данных представлены полосами различной длины, расположенными горизонтально вдоль оси X. Длина полосы соответствует величине числового значения.
гистограмма — линейчатая диаграмма, в которой отдельные значения представлены вертикальными столбиками различной длины.
график — все отдельные значения ряда данных соединены между собой линиями, полученными путем интерполяции. График не стоит использовать, если отдельные абсолютные значения не взаимосвязаны.
круговая диаграмма — сумма всех значений принимается за 100%,
а процентное соотношение величин изображается в виде круга, разбитого на несколько секторов разного цвета. В круговой диаграмме допускается только один ряд данных.
кольцевая диаграмма — это особый вид круговой диаграммы. В этом случае сумма всех значений принимается за 100%, а ряды данных представляют собой вложенные кольца, разделенные на сегменты в процентном отношении. Преимущество кольцевой диаграммы перед круговой состоит в возможности одновременного изображения нескольких рядов данных.
радар — это диаграмма , в которой категории представляются лучами, расходящимися из одной точки. Каждое значение ряда данных выделено на оси и соединено с другими замкнутой линией.
XY (точечная) диаграмма — отдельные значения таблицы представляются точками в декартовой системе координат, которые не соединены линиями. Этот вид диаграммы больше всего подходит для представления независимых отдельных значений.
При создании объемных диаграмм с областями используется система координат с тремя осями. Объемная гистограмма, объемная линейчатая и объемная круговая диаграммы позволяют достичь эффекта пространственного представления данных. Объемный (трехмерный) график приобретает форму лент. Принцип построения объемных поверхностных диаграмм напоминает применяемый в картографии способ представления на физических картах высоты участка местности над уровнем моря.
Для создания диаграммы необходимо выделить блок ячеек с данными, а также названиями рядов и категорий. После этого можно воспользоваться командой Диаграмма из меню Вставка или кнопкой Мастер диаграмм на панели инструментов.
Процесс создания диаграммы с помощью Мастера диаграмм состоит из 4 шагов, переход от одного шага к другому осуществляется с помощью кнопки Далее:
1. Ввод интервала данных, выполняется при выделении блока ячеек.
2. Выбор типа диаграммы (стандартной или нестандартной).
3. Выбор подтипа диаграммы (графика, гистограммы и др).
4. 3адание параметров, определяющих интерпретацию выделенных данных. Добавление легенды, названий для диаграммы , ее осей и др.
После нажатия кнопки Готово на рабочем листе будет создана диаграмма. За черные квадратики рамки её можно растянуть, а ухватившись мышью за Область диаграммы — переместить в другое место.
Excel может построить диаграмму только по ячейкам, в которых уже имеются числа, поэтому, если нужно построить график по его уравнению, то сначала эти числа нужно рассчитать и занести в ячейки. Это удобно делать следующим образом:
Пример 1. Вычислить значение функции у = е х · sin(x) для -1=< х <=1 c с шагом ?х=0,2.
Заполним столбец А значениями аргумента функции. Чтобы не вводить их вручную , применим следующий прием. Введите в ячейку А1 начальное значения аргумента (-1) . В меню Правка выберите команду Заполнить, затем Прогрессия и в открывшемся диалоговом окне укажите предельное значение
(1),шаг(0.2 ) и направление (По столбцам). После нажатия кнопки ОК в столбце А будут введены все значения аргумента . В ячейку В1 введите формулу : =exp(А1)* sin(AI). Размножьте эту формулу на остальные ячейки столбца В, ухватив левой мышью черный квадратик в правом нижнем углу рамки выделенной ячейки В1 и протащив рамку до конца изменения аргумента . В итоге будут вычислены и введены в ячейки соответствующие значения функции.
Для редактирования внедренной диаграммы необходимо сначала активизировать редактируемый элемент двойным щелчком мыши, после чего вокруг нее появится рамка, а в строке меню добавятся команды для редактирования диаграмм. С помощью мыши или клавиш управления курсором можно выделить для редактирования любой элемент диаграммы, а именно: область диаграммы, область построения, основание и стенки объемных диаграмм, легенду, оси, текст, стрелки, линии сетки, любой из рядов данных и др.
Для добавления на диаграмме названий, легенд или меток данных можно воспользоваться командой Параметры диаграммы из меню Диаграмма. Если названия на диаграмме уже присутствуют, то после выделения их можно откорректировать как обычные текстовые поля.
Часто возникает необходимость дополнить диаграмму новыми данными. Для этого используется команда Добавить данные из меню Диаграмма , после чего в таблице исходных данных выделяется интервал с новыми данными, а в открывшемся диалоговом окне задается способ их интерпретации.
Большой интерес представляют комбинированные диаграммы, на которых разные ряды данных отображаются в виде разных типов диаграмм. Для создания такой диаграммы в окне Мастера диаграмм необходимо выбрать тип Настандартные, подтипГрафик\Гистограмма. Мастер диаграмм разбивает исходные ряды данных примерно пополам и отображает их в виде двух разных диаграмм. Такие пропорции не всегда бывают удобны, как например, для представления на диаграмме ежемесячных объемов продаж трех видов товаров, а также их суммарной величины. В этом случае можно использовать следующий прием:
— создать обычную диаграмму для всех четырех категорий данных, включая суммарный объем продаж;
— одним из описанных выше способов выделить на гистограмме ряд данных суммарного объема продаж;
— в меню Диаграмма выбрать команду Тип диаграммы,
— в открывшемся окне выбрать новый тип диаграммы для суммарного объема продаж.
В результате получится комбинированная диаграмма, отображающая гистограмму объемов продаж отдельных товаров на фоне области суммарных продаж.
Если данные на диаграмме имеют слишком большой диапазон значений, то удобно использовать логарифмическую шкалу. Для этого в Мастере Диаграмм выбирается подтип диаграммы Логарифмическая из типа Нестандартные.
Иногда, как в рассмотренном выше примере, для отдельного ряда данных удобно использовать вспомогательную ось. Для этого выбирают подтип График(2 оси).
В чем отличие между абсолютными и относительными ссылками в ms excel кратко
По умолчанию ссылка на ячейку является относительной. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы указываете адрес ячейки в том же ряду (2), но отстоящей на два столбца влево (C минус A). Формула с относительной ссылкой изменяется при копировании из одной ячейки в другую. Например, вы можете скопировать формулу =A2+B2 из ячейки C2 в C3, при этом формула в ячейке C3 сдвинется вниз на один ряд и превратится в =A3+B3.
Выделите ячейку со ссылкой на ячейку, которую нужно изменить.
В строка формул щелкните ссылку на ячейку, которую вы хотите изменить.
Для перемещения между сочетаниями используйте клавиши +T.
В следующей таблице огововодятся сведения о том, что происходит при копировании формулы в ячейке A1, содержаной ссылку. В частности, формула копируется на две ячейки вниз и на две ячейки справа, в ячейку C3.
Текущая ссылка (описание):
$A$1 (абсолютный столбец и абсолютная строка)
$A$1 (абсолютная ссылка)
A$1 (относительный столбец и абсолютная строка)
C$1 (смешанная ссылка)
$A1 (абсолютный столбец и относительная строка)
$A3 (смешанная ссылка)
A1 (относительный столбец и относительная строка)
Абсолютные и относительные ссылки в Excel
В Excel существует два типа ссылок: абсолютные и относительные. Эти ссылки ведут себя по-разному при копировании и заполнении ячеек. Относительные ссылки изменяются когда вы копируете формулу из одной ячейки в другую, а абсолютные ссылки, напротив, не меняются вне зависимости от того, куда бы вы их ни скопировали.
Относительные ссылки в Excel
Как создать и скопировать формулу с относительными ссылками
На примере ниже мы хотим создать формулу, которая поможет в калькуляции итогового счета за заказ в ресторане. Мы хотим в колонке “Итог” рассчитать сумму к оплате за каждое блюдо, в зависимости от его стоимости за единицу и количества. Для этого, мы создадим одну формулу с относительной ссылкой и скопируем ее на все строки таблицы. Для этого проделаем следующие шаги:
Абсолютные ссылки в Excel
Часто, при расчетах нам нужно, чтобы при копировании формул, ссылки на ячейки не изменялись. В отличие от относительных ссылок, абсолютные позволяют зафиксировать при расчетах ячейки в определенных строках и столбцах, что делает процесс вычисления в таблицах более простым и эффективным.
Для создания абсолютной ссылки используется знак доллара “$”. С его помощью вы можете зафиксировать от изменений столбец, строку или всех вместе:
$A$2 – столбец и строка не изменяются при копировании формулы;
A$2 – при копировании формулы не меняется только строка;
Используя абсолютные ссылки в Excel, вы можете быстро изменять настройки фиксации столбца и строки, с помощью клавиши F4. Для этого нужно дважды кликнуть на ячейку с формулой, затем, левой клавишей мыши поставить курсор на значение ячейки и с помощью клавиши F4 настроить фиксацию строки и столбца.
Как создать и скопировать формулу с абсолютными ссылками
Как создать ссылки на другие листы в Excel
Зачастую, нам в расчетах требуется задействовать данные с разных листов файла Excel. Для этого, при создании ссылки на ячейку из другого листа нужно использовать название листа и восклицательного знака на конце ( ! ). Например, если вы хотите создать ссылку на ячейку A1 на листе Sheet1, то ссылка на эту ячейку будет выглядеть так:
ВАЖНО! Если в название листа, на ячейку с которого вы ссылаетесь есть пробелы, то название этого листа в ссылке должно быть заключено в кавычки (‘ ‘). Например, если название вашего листа Бюджет Финал, то ссылка на ячейку A1 будет выглядеть так:
На примере ниже, мы хотим добавить в таблицу ссылку на ячейку, в которой уже произведены вычисления между двумя листами Excel файла. Это позволит нам использовать одно и то же значение на двух разных листах без перезаписи формулы или копирования данных между рабочими листами. Для этого проделаем следующие шаги:
Если, в дальнейшем, вы переименуете лист, на который вы ссылались, то система автоматически обновит формулу.
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!
Программа Microsoft Excel: абсолютные и относительные ссылки
При работе с формулами в программе Microsoft Excel пользователям приходится оперировать ссылками на другие ячейки, расположенные в документе. Но, не каждый пользователь знает, что эти ссылки бывают двух видов: абсолютные и относительные. Давайте выясним, чем они отличаются между собой, и как создать ссылку нужного вида.
Определение абсолютных и относительных ссылок
Что же представляют собой абсолютные и относительные ссылки в Экселе?
Абсолютные ссылки – это ссылки, при копировании которых координаты ячеек не изменяются, находятся в зафиксированном состоянии. В относительных ссылках координаты ячеек изменяются при копировании, относительно других ячеек листа.
Пример относительной ссылки
Покажем, как это работает на примере. Возьмем таблицу, которая содержит количество и цену различных наименований продуктов. Нам нужно посчитать стоимость.
Делается это простым умножением количества (столбец B) на цену (столбец C). Например, для первого наименования товара формула будет выглядеть так «=B2*C2». Вписываем её в соответствующую ячейку таблицы.
Теперь, чтобы вручную не вбивать формулы для ячеек, которые расположены ниже, просто копируем данную формулу на весь столбец. Становимся на нижний правый край ячейки с формулой, кликаем левой кнопкой мыши, и при зажатой кнопке тянем мышку вниз. Таким образом, формула скопируется и в другие ячейки таблицы.
Но, как видим, формула в нижней ячейке уже выглядит не «=B2*C2», а «=B3*C3». Соответственно, изменились и те формулы, которые расположены ниже. Вот таким свойством изменения при копировании и обладают относительные ссылки.
Ошибка в относительной ссылке
Но, далеко не во всех случаях нам нужны именно относительные ссылки. Например, нам нужно в той же таблице рассчитать удельный вес стоимости каждого наименования товара от общей суммы. Это делается путем деления стоимости на общую сумму. Например, чтобы рассчитать удельный вес картофеля, мы его стоимость (D2) делим на общую сумму (D7). Получаем следующую формулу: «=D2/D7».
В случае, если мы попытаемся скопировать формулу в другие строки тем же способом, что и предыдущий раз, то получим совершенно неудовлетворяющий нас результат. Как видим, уже во второй строке таблицы формула имеет вид «=D3/D8», то есть сдвинулась не только ссылка на ячейку с суммой по строке, но и ссылка на ячейку, отвечающую за общий итог.
D8 – это совершенно пустая ячейка, поэтому формула и выдаёт ошибку. Соответственно, формула в строке ниже будет ссылаться на ячейку D9, и т.д. Нам же нужно, чтобы при копировании постоянно сохранялась ссылка на ячейку D7, где расположен итог общей суммы, а такое свойство имеют как раз абсолютные ссылки.
Создание абсолютной ссылки
Таким образом, для нашего примера делитель должен быть относительной ссылкой, и изменяться в каждой строке таблицы, а делимое должно быть абсолютной ссылкой, которая постоянно ссылается на одну ячейку.
С созданием относительных ссылок у пользователей проблем не будет, так как все ссылки в Microsoft Excel по умолчанию являются относительными. А вот, если нужно сделать абсолютную ссылку, придется применить один приём.
После того, как формула введена, просто ставим в ячейке, или в строке формул, перед координатами столбца и строки ячейки, на которую нужно сделать абсолютную ссылку, знак доллара. Можно также, сразу после ввода адреса нажать функциональную клавишу F7, и знаки доллара перед координатами строки и столбца отобразятся автоматически. Формула в самой верхней ячейке примет такой вид: «=D2/$D$7».
Копируем формулу вниз по столбцу. Как видим, на этот раз все получилось. В ячейках находятся корректные значения. Например, во второй строке таблицы формула выглядит, как «=D3/$D$7», то есть делитель поменялся, а делимое осталось неизменным.
Смешанные ссылки
Как видим, при работе с формулами в программе Microsoft Excel для выполнения различных задач приходится работать как с относительными, так и с абсолютными ссылками. В некоторых случаях используются также смешанные ссылки. Поэтому, пользователь даже среднего уровня должен четко понимать разницу между ними, и уметь пользоваться этими инструментами.
Помимо этой статьи, на сайте еще 12470 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Помогла ли вам эта статья?
Поделиться статьей в социальных сетях:
Еще статьи по данной теме:
Итог часы:минуты образован формулой =СЦЕПИТЬ(ЛЕВСИМВ(H1764;2);»:»;ПРАВСИМВ(H1764;ДЛСТР(H1764)-2))
03:26
05:15
06:01
04:03
04:21
10:59
Задача: как сложить время в данном столбце с учетом, что Excel считает 24 часа за 1, а мне надо общее количество часов или количество дней, часов и минут.
Спасибо,
Валерий
Здравствуйте, Валерий. Попробуйте в той ячейке, в которую будет выводится общая сумма, установить формат «[ч]:мм». Просто откройте окно форматов, перейдите в раздел «Все форматы» и в поле «Тип» пропишите вышеуказанное значение. Затем жмите «OK».
Спасибо за отзыв, но это я пробовал. Не помогло. В сумее получаются нули.
Валерий, можете тогда приложить два скриншота:
1. На одном скриншоте должна быть выделена одна из ячеек слагаемых, чтобы я мог увидеть, какая формула содержится там.
2. На втором скриншоте выделите ячейку, в которой производится общее суммирование.
Отправьте каждый скриншот в отдельном сообщении.
Максим, в наименовании файлов ответ на Вашу просьбу
Ответ на второй вопрос
Валерий, к сожалению, в таком формате сложить не получится, так как в содержащих формулу ячейках, значения не воспринимаются, как время. Но есть, один вариант.
1. Выделите все ячейки столбца, в которых содержится формула «=СЦЕПИТЬ(ЛЕВСИМВ(H1764;2);»:»;ПРАВСИМВ(H1764;ДЛСТР(H1764)-2))»
2. Кликните по выделению правой кнопкой мыши и выберите вариант «Копировать».
3. Тут же не снимая выделение опять кликайте правой кнопкой мыши по выделению. На этот раз в контекстном меню в параметрах вставки выберите «Значения». У разных версий Эксель этот пункт может выглядеть по-разному. У меня он выглядит, как на прикрепленном скриншоте.
4. После этого все данные в ячейках превратятся из формул в значения. После этого. чтобы сработало суммирование, нужно их всех перекликать, применив последовательное нажатие F2 и Enter. Но я вас советую просто удалить формулу в общей ячейке и вписать её заново. Так будет гораздо быстрее. И не забывайте в ячейке вывода общей суммы установить формат «[ч]:мм». Иначе корректно считать не будет.
Но данный способ содержит один недостаток, о котором вам нужно знать. Вы уберете форму, а это значит, что при изменении данных в связанных ячейках, данные в ячейках, в которых содержится время автоматически изменятся не будут, так как связь фактически будет разорвана. Но если таблица статическая и никаких изменений в тех ячейках, откуда тянет данные функция «=СЦЕПИТЬ(ЛЕВСИМВ(H1764;2);»:»;ПРАВСИМВ(H1764;ДЛСТР(H1764)-2))» не предвидится, то и никаких негативных последствий не будет. А вот если данные в ячейке H1764 и др. будут постоянно изменятся, то тогда этот вариант не подойдет. Но вы можете поступить по другому. Справа от столбца с датами добавить ещё один столбец, и скопировав содержимое с формулами, вставить его, как значения, не в ту же колонку, а в соседний только что созданный столбец. Правда, опять же, данные автоматически обновляться не будут в этом столбце, но вы всегда сможете отследить изменения в соседнем столбце и скопировать из него данные, как значения в тот столбец, где будет производиться суммирование.
Респект, все получилось! Большое спасибо!
Здравствуйте, а почему вы пишете при задании абсолютной ссылки нажать F7? Она мне ничего не выдает, а вот F4 делает ссылку абсолютной, может у вас опечатка?
Примеры сложные полегче нельзя
Там скажем а1 это 2 б1 5 чё нето не сложное
Задайте вопрос или оставьте свое мнение Отменить комментарий
Разница между абсолютной и относительной ссылкой
Абсолютные и относительные ссылки могут использоваться в самых разных IT-инструментах. Чаще всего — при конструировании веб-страниц, а также в электронных таблицах. Каким образом выглядят данные ссылки? Чем отличается абсолютная ссылка от относительной?
Абсолютные и относительные ссылки в Excel
Итак, в числе тех распространенных IT-инструментов, в которых применяются абсолютные и относительные ссылки, — программа Excel. Элемент первого типа представляет собой фиксированный (не меняющийся при копировании содержимого ячейки в другое место таблицы) набор координат ячеек, которые определены в формуле. То есть при использовании абсолютной ссылки перемещение ячейки A1 в любую сторону не влияет на содержание формулы — координаты прописанных в ней ячеек не изменятся. Формула будет оставаться в том виде, в котором вписана пользователем в таблицу Excel изначально.
В свою очередь, относительная ссылка — это набор координат ячеек в формуле, меняющихся при копировании в другое место таблицы ячейки, в которую вписана формула. То есть если изначально в ячейке A1 записана формула = B1+B2, то в случае с использованием относительной ссылки при копировании данной ячейки на клетку, расположенную правее, — на A2, формула автоматически изменится на = B2+B3.
Ссылки в структуре веб-страницы
Понятие абсолютных и относительных ссылок используется также в сфере веб-разработки. Дело в том, что и те и другие могут размещаться в структуре веб-страниц. Каким образом?
Под относительной ссылкой, размещенной в структуре веб-страницы, понимается неполный адрес файла, выглядящий примерно как /page1234.html. Предполагается, что браузер, обнаружив относительную ссылку, автоматически добавит перед знаком / недостающую часть адреса файла для того, чтобы он загрузился.
Важно, чтобы переход по относительной ссылке осуществлялся с веб-страницы, расположенной на том же сайте, где размещен файл. То есть если на портале http://www.page1.com размещается страница с относительной ссылкой вида /page1234.html, то браузер, после того как пользователь кликнет по данной ссылке, начнет загружать файл по адресу http://www.page1.com/page1234.html.
Если, в свою очередь, разместить ее, к примеру, на портале http://www.page2.com, то браузер начнет загружать страницу http://www.page2.com/page1234.html. Поэтому веб-мастеру нужно быть внимательным с размещением относительных ссылок — главное, чтобы им соответствовал реальный файл на сервере.
Под абсолютной ссылкой в веб-странице понимается, в свою очередь, полный адрес файла, выглядящий как http://www.page.com/page1.html. Браузер будет обеспечивать переход к файлу только по этому адресу и никакому другому. Абсолютную ссылку можно встроить в веб-страницу, расположенную на любом сайте, — переход по ней на указанный файл всегда будет корректным.
Сравнение
Главное отличие абсолютной ссылки от относительной в Excel в том, что при использовании первой содержание формулы, скопированной из одной ячейки в другую, сохраняется, при задействовании второй — меняется.
В другой области IT — веб-разработке — под абсолютной ссылкой понимается полный адрес файла, расположенного на сервере. Под относительной — лишь часть адреса, достаточная, однако, для того, чтобы браузер автоматически достроил полный путь к файлу, исходя из текущего адреса веб-страницы, на которой размещена относительная ссылка.
Определив, в чем разница между абсолютной и относительной ссылкой в двух основных IT-сферах, где данные понятия используются, зафиксируем выводы в таблице.
В чем отличие между абсолютными и относительными ссылками в ms excel кратко
Ссылка указывает на ячейку или диапазон ячеек, которые содержат данные и используются в формуле. Ссылки позволяют:
использовать данные в одной формуле, которые находятся в разных частях таблицы.
использовать в нескольких формулах значение одной ячейки.
Имеются два вида ссылок:
Относительные ссылки. Относительная ссылка определяет расположение ячейки с данными относительно ячейки, в которой записана формула. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка.
При копировании формулы вдоль столбца или строки относительная ссылка корректируется:
смещение на один столбец — изменение в ссылке одной буквы в имени столбца.
смещение на одну строку — изменение в ссылке номера строки на единицу.
Например при копировании формулы из ячейки А2 в ячейку В2, С2 и D2 относительная ссылка автоматически изменяется и рассмотренная выше формула приобретает вид: =B1^2, =C1^2, =D1^2. При копировании этой же формулы в ячейки А3 и А4 получим соответственно =A2^2, =A3^2 (рисунок 3).
Типы ссылок EXCEL на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация
В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы введете адрес в формулу, будет зависеть, как он будет модифицироваться при ее копировании в другие ячейки листа. Это пригодится при как построении обычных формул на листе, так и при создании Именованных формул , задания правил Условного форматирования и при формировании условий Проверки данных .
В подавляющем большинстве формул EXCEL используются ссылки на ячейки. Например, если в ячейке В1 содержится формула =А1+5 , то означает, что в ячейку В1 будет помещено значение ячейки А1 находящейся на пересечении столбца А и строки 1 , к которому прибавлено число 5. Также в формулах используются ссылки на диапазоны ячеек, например, формула =СУММ(А2:А11) вычисляет сумму значений из ячеек А2 , А3 , . А11 . Однако, формула =СУММ($А$2:$А$11) также вычисляет сумму значений из тех же ячеек. Тогда в чем же разница? Разница проявляется при копировании этой формулы в соседние ячейки.
Абсолютная адресация (абсолютные ссылки)
Для создания абсолютной ссылки используется знак $. Ссылка на диапазона записывается ввиде $А$2:$А$11 . Абсолютная ссылка позволяет при копировании формулы зафиксировать адрес диапазона или адрес ячейки. Рассмотрим пример.
Пусть в ячейке В2 введена формула =СУММ( $А$2:$А$11 ) , а в ячейке С2 формула =СУММ(А2:А11). Скопировав формулы вниз, например с помощью Маркера заполнения, во всех ячейках столбца В получим одну и ту же формулу =СУММ( $А$2:$А$11 ) , т.е. ссылка на диапазон ячеек при копировании не изменилась . А в столбце С получим другой результат: в ячейке С3 будет формула =СУММ(A3:A12) , в ячейке С4 будет формула =СУММ(A4:A13) и т.д. Т.е. при копировании ссылка была модифицирована .
Какая формула лучше? Все зависит от вашей задачи: иногда при копировании нужно фиксировать диапазон, в других случая это делать не нужно.
Другой пример.
Пусть в диапазоне А1:А5 имеются числа (например, зарплата сотрудников отдела), а в С1 – процент премии установленный для всего отдела. Для подсчета премии каждого сотрудника необходимо все зарплаты умножить на % премии. Рассчитанную премию поместим в диапазоне В1:В5 . Для этого введем в ячейку В1 формулу =А1*С1 . Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в В2:В5 нули (при условии, что в диапазоне С2:С5 нет никаких значений). В ячейке В5 будем иметь формулу =А5*С5 (EXCEL при копировании формулы модифицировал ссылки на ячейки, т.к. их адреса не были записаны в виде абсолютных ссылок).
Чтобы выйти из ситуации — откорректируем формулу в ячейке В1 .
- выделите ячейку В1 ;
- войдите в режим правки ячейки (нажмите клавишу F2 ) или поставьте курсор в Строку формул ;
- поставьте курсор на ссылку С1 (можно перед С , перед или после 1 );
- нажмите один раз клавишу F4 . Ссылка С1 выделится и превратится в $C$1 (при повторных нажатиях клавиши F4 ссылка будет принимать последовательно вид C$1, $C1,C1, $C$1 , …). Ссылка вида $C$1 называется абсолютно , C$1, $C1 – смешанными, а С1 — относительной .
Такм образом, введем в В1 формулу =А1*$С$1 . Это можно сделать и в ручную, введя знак $ перед буквой столбца и перед номером строки.
Нажмем ENTER и протянем ее вниз. Теперь в В5 будет правильная формула =А5*$С$1 . Всем сотрудникам теперь достанется премия :).
Относительная адресация (относительные ссылки)
Введем в ячейку B1 формулу =А1 , представляющую собой относительную ссылку на ячейку А1 . Что же произойдет с формулой при ее копировании в ячейки расположенные ниже В1 ? После протягивания ее вниз Маркером заполнения , в ячейке В5 будет стоять формула =А5 , т.е. EXCEL изменил первоначальную формулу =A1 . При копировании вправо в ячейку С1 формула будет преобразована в =В1.
Теперь примеры.
Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А : значения из той же строки и значения из строки выше.
Т.е. в B2 должна быть формула: =СУММ(A1:A2) , в B3 : =СУММ(A2:A3) и т.д.
Решить задачу просто: записав в B2 формулу =СУММ(A1:A2) , протянем ее с помощью Маркера заполнения в ячейку B3 и ниже.
Альтернативное решение
Другим вариантом решения этой задачи является использование Именованной формулы . Для этого:
- выделите ячейку B2 (это принципиально при использовании относительных ссылок в Именах ). Теперь B2 – активная ячейка;
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите, например Сумма2ячеек ;
- убедитесь, что в поле Диапазон введена формула =СУММ(A1:A2)
- Нажмите ОК.
Теперь в B2 введем формулу = Сумма2ячеек . Результат будет тот, который мы ожидали: будет выведена сумма 2-х ячеек из столбца слева (см. файл примера , лист пример1 ). Если формулу ввести в ячейку B5 , то она будет суммировать ячейки A4:A5 , если ввести в D10 , то – ячейки С9:С10 .
Другими словами, будут суммироваться 2 ячейки соседнего столбца слева, находящиеся на той же строке и строкой выше. Ссылка на диапазон суммирования будет меняться в зависимости от месторасположения формулы на листе, но «расстояние» между ячейкой с формулой и диапазоном суммирования всегда будет одинаковым (один столбец влево).
Относительная адресация при создании формул для Условного форматирования.
Пусть необходимо выделить в таблице, содержащей числа от 1 до 100, значения больше 50, причем, только в четных строках (см. файл примера , лист пример2 ). Построим такую таблицу:
- выделите диапазон таблицы B2:F11 , так, чтобы активной ячейкой была B2 (важно выделить диапазон начиная с B2 , а не с F11 . Во втором случае, активной ячейкой будет F11 );
- вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило/ использовать формулу для … );
- введите формулу =И(ОСТАТ($A2;2)=$I$1;B2>50) ;
- выберите Формат ;
- нажмите ОК
Важно отметить, что, если бы, при создании правила, активной ячейкой была F11 , то формулу необходимо было переписать: =И(ОСТАТ($A11;2)=$I$1;F11>50) . Поменять необходимо только ссылки незафиксированные знаком $: B2 на F11 и $A2 на $A11 .
Внимание! При использовании относительной адресации в Именованных формулах , Именованных диапазонах , Условном форматировании , Проверке данных (примеры см. в соответствующих статьях) необходимо следить, какая ячейка является активной в момент создания формулы (активной может быть только одна ячейка на листе, не смотря на то, что выделено может быть несколько).
Смешанные ссылки
Смешанные ссылки имеют формат =$В3 или =B$3 . В первом случае при копировании формулы фиксируется ссылка на столбец B , а строка может изменяться в зависимости при копировании формулы.
Предположим, у нас есть столбец с ценами в диапазоне B 3: B 6 (см. файл примера , лист пример3 ). В столбцах С, D , Е содержатся прогнозы продаж в натуральном выражении по годам (в шт.). Задача: в столбцах F , G , H посчитать годовые продажи в рублях, т.е. перемножить столбцы С, D , Е на столбец B . Использование механизма относительной адресации позволяет нам ввести для решения задачи только одну формулу. В ячейку F вводим: =$В3*C3 . Потом протягиваем формулу маркером заполнения вниз до F 6 ,
а затем весь столбец таблицы протягиваем вправо на столбцы G и H .
Обратите внимание, что в формуле =$В3*C3 перед столбцом B стоит значок $. При копировании формулы =$В3*C3 в ячейки столбцов F, G и H , этот значок $ говорит EXCEL о том, что ссылку на столбец B модифицировать не нужно. А вот перед столбцом С такого значка нет и формула в ячейке H6 примет вид =$В6*E6 .
Вводим знак $ в адрес ячейки
Существует несколько возможностей при вводе формулы ввести знак $ в адрес ячейки или диапазона. Рассмотрим ввод на примере формулы =СУММ($А$2:$А$5)
1. Ввести знак $ можно вручную, последовательно вводя с клавиатуры все знаки =СУММ($А$2:$А$5)
2. С помощью клавиши F4 (для ввода абсолютной ссылки):
- Введите часть формулы без ввода $: =СУММ(А2:А5
- Затем сразу нажмите клавишу F4 , знаки $ будут вставлены автоматически: =СУММ( $А$2:$А$5
- Для окончания ввода формулы нажмите ENTER.
Если после ввода =СУММ(А2:А5 в формуле передвинуть курсор с помощью мыши в позицию левее,
а затем вернуть его в самую правую позицию (также мышкой),
то после нажатия клавиши F4 , знаки $ будут автоматически вставлены только во вторую часть ссылки! =СУММ( А2:$А$5
Чтобы вставить знаки $ во всю ссылку, выделите всю ссылку А2:$А$5 или ее часть по обе стороны двоеточия, например 2:$А , и нажмите клавишу F4. Знаки $ будут автоматически вставлены во всю ссылку $А$2:$А$5
3. С помощью клавиши F4 (для ввода относительной ссылки).
- Введите часть формулы без ввода $: =СУММ(А2:А5
- Затем сразу нажмите клавишу F4 , будут автоматически вставлены знаки $: =СУММ( $А$2:$А$5
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ( А$2:А$5 (фиксируются строки)
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ($ А2:$А5 (фиксируется столбец)
- Еще раз нажмите клавишу F4 : ссылка будет модифицирована в =СУММ( А2:А5 (относительная ссылка). Последующие нажатия изменяют ссылку заново по кругу.
- Для окончания ввода нажмите ENTER.
Чтобы изменить только первую или втрорую часть ссылки — установите мышкой курсор в нужную часть ссылки и последовательно нажимайте клавушу F4.
«СуперАбсолютная» адресация
В заключении расширим тему абсолютной адресации. Предположим, что в ячейке B 2 находится число 25, с которым необходимо выполнить ряд вычислений, например, возвести в разные степени (см. файл примера , лист пример4 ). Для этого в столбце C напишем формулу возведения в степень (значения степени введем в столбец D ): =$B$2^$D2 .
Мы использовали абсолютную ссылку на ячейку B 2 . При любых изменениях положения формулы абсолютная ссылка всегда будет ссылаться на ячейку, содержащую наше значение 25 :
- при копировании формулы из С3 в Н3 – формула не изменится, и мы получим правильный результат 625 ;
- при вставке нового столбца между столбцами А и В – формула превратится в =$C$2^$E3 , но мы снова получим правильный результат 625 .
Все правильно, т.к. это и есть суть абсолютной адресации: ссылки автоматически модифицируются для сохранения адресации на нужные ячейки при любых модификациях строк и столбцах листа (ну, кроме удаления ячейки с формулой, конечно). Однако бывают ситуации, когда значения на лист попадают из внешних источников. Например, когда созданный пользователем макрос вставляет внешние данные в ячейку B 2 (т.е. всегда во второй столбец листа). Теперь, при вставке столбца между столбцами А и В – формула как и раньше превратится в =$C$2^$E3 , но т.к. исходное число (25) будет вставляться макросом не в С2 , а по прежнему в ячейку B 2 , и мы получим неправильный результат.
Вопрос: можно ли модифицировать исходную формулу из С2 ( =$B$2^$D2 ), так чтобы данные все время брались из второго столбца листа и независимо от вставки новых столбцов?
Решение заключается в использовании функции ДВССЫЛ() , которая формирует ссылку на ячейку из текстовой строки. Если ввести в ячейку формулу: =ДВССЫЛ(«B2») , то она всегда будет указывать на ячейку с адресом B2 вне зависимости от любых дальнейших действий пользователя, вставки или удаления столбцов и т.д.
Небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ() выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО() :
При ссылке на ячейку В2 с другого листа =ДВССЫЛ(«пример4!B2») может возникнуть и другая сложность: при изменении названия листа пример4 – формула перестает работать. Но это также можно обойти – см. пример из статьи Определяем имя листа .
Другим способом заставить формулу ссылаться на один и тот же столбец является использование функции СМЕЩ() – об этом читайте статью Как заставить формулу все время ссылаться на один и тот же столбец .
Типы ссылок в Excel – как использовать абсолютную, относительную и смешанную адресацию
Каждый человек работающий в Excel встречался с понятием адресаций или использовал ссылки в своих документах. Благодаря своим возможностям табличный процессор Эксель, входящий в состав стандартного пакета MS Office, обеспечивает надежное хранение и быструю обработку больших массивов данных. Частью этого функционала являются ссылки.
Типы адресаций
Ссылки в Excel передают в себе адрес определенных ячеек или связанных диапазонов. На одном рабочем листе одновременно могут находиться более 2 миллиардов ячеек, образующих рабочую область таблицы. Адрес активной определяют название столбца и строки, в пересечении которых и расположена ячейка. Например, С8. Это означает, что данные находятся в восьмой строке и третьем столбце под названием C. Ссылка на Range (диапазон) задается через адреса двух его углов (верхний левый и нижний правый) Для примера, B2:C6.
В Excel есть четыре типа ссылок, каждый из которых предназначен для своих операций — относительные, смешанные, абсолютные и ссылки на отдельные листы.
Относительная адресация
Использование относительных ссылок может помочь сэкономить много часов монотонной работы связанной с копированием. По умолчанию, любые созданные ссылки относительны. При копировании значений и расчетов из разных ячеек, адреса также меняются. Это главным образом отличает их от абсолютных, остающихся неизменными при копировании.
Абсолютная адресация
Иногда происходит так, что изменять адрес при растягивании формулы не нужно. Для этого подходят абсолютные ссылки в Excel. С их помощью могут быть реализованы константы и значения, которые не должны изменяться при любых условиях. Создается такая ссылка путем добавления знака американского доллара ($). Его необходимо установить перед адресом, как строки, так и столбца.
Смешанная адресация
В случае, когда знак абсолютной адресации фиксирует значение только столбцов или строк, ссылка стает смешанной. Она обладает свойствами двух других видов, то есть может копировать или ограничивать адрес ячеек по столбцам или строкам. Это самый запутанный тип адресации в Excel.
Использование относительных ссылок
Для того чтобы создать относительную адресацию в формуле, необходимо в любой свободной ячейке напечатать знак «=», без пробела вписать адрес или кликнуть по той ячейке, которая должна быть использована в вычислениях. Пример создания относительной ссылки:
- Кликаем на D2:
- Для подсчета «Итого» необходимо умножить данные из столбцов B и C, т.е вписываем формулу =B2*C2.
- Нажимаем Ввод (Enter), после чего формула будет вычислена и результат запишется в D2.
- Значение из D2 можно растянуть на все строки. Делается это с помощью функции автозаполнения, которая представляет собой квадрат, расположенный справа внизу в выделенной ячейке. «Протягиваем» маркер с помощью мыши только до пятой строки. После нее данных нет, и в последующих ячейках будут нули.
Часто при создании такого рода адресаций появляются ошибки, поскольку пользователи забывают об относительности адреса ячейки. Используя в формуле какую-либо константу, ее просто добавляют без фиксирования символом $. Это приводит к таким ошибкам как «#ПУСТО!», «#ДЕЛ/0» и другим. Иногда бывает так, что нужно растянуть формулу с зафиксированной строкой или столбцом. В таких случаях смешанная адресация может сэкономить время.
Использование абсолютных ссылок
С помощью одно простого знака $ ссылка стандартного типа превращается в абсолютную, т.е. ссылка закрепляется за ячейкой. Поставьте доллар перед строкой или столбцом в нужном адресе или выделите его и нажмите клавишу F4 для автоматического проставления знака в формуле. Вышеприведенный пример с использованием абсолютной адресации будет выглядеть следующим образом:
- Кликаем на D2.
- Вписываем уже знакомую формулу =B2*C2, выделяем ее и нажимаемF4.
- НажимаемEnter и получаем то же самое.
- Однако теперь протягивая маркер, будет получаться везде одно и то же значение равное 4,00 р., т.е. жестко зафиксированные адреса.
В этом случае можно создать смешанную ссылку, например =B2*$C$2 будет означать, что С2 будет всегда 4, независимо от того, куда будет перемещена формула, а В2 будет изменяться.
Сквозная ссылка через лист
Использование данных из второго листа возможно благодаря реализованной сквозной адресации. Для использования значения, расположенного на другом листе, нужно после «=» в поле вписать название листа, восклицательный знак и адрес ячейки, которая содержит это значение. Например, «=’Лист2’!A1». Можно и просто после «=» перейти на следующий лист, выбрать необходимую ячейку, вернуться обратно и нажать Ввод.