что такое ссылка в электронной таблице

Использование относительных и абсолютных ссылок

По умолчанию ссылка на ячейку является относительной. Например, если вы ссылаетесь на ячейку 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 на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация

history 18 ноября 2012 г.

Абсолютная адресация (абсолютные ссылки)

Какая формула лучше? Все зависит от вашей задачи: иногда при копировании нужно фиксировать диапазон, в других случая это делать не нужно.

Другой пример.

Относительная адресация (относительные ссылки)

Теперь примеры.

Пусть в столбце А введены числовые значения. В столбце B нужно ввести формулы для суммирования значений из 2-х ячеек столбца А : значения из той же строки и значения из строки выше.

Альтернативное решение

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

Относительная адресация при создании формул для Условного форматирования.

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

2. С помощью клавиши F4 (для ввода абсолютной ссылки):

Если после ввода =СУММ(А2:А5 в формуле передвинуть курсор с помощью мыши в позицию левее,

а затем вернуть его в самую правую позицию (также мышкой),

3. С помощью клавиши F4 (для ввода относительной ссылки).

«СуперАбсолютная» адресация

Вопрос: можно ли модифицировать исходную формулу из С2 ( =$B$2^$D2 ), так чтобы данные все время брались из второго столбца листа и независимо от вставки новых столбцов?

Небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ() выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО() :

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Источник

Организация вычислений в электронных таблицах. Абсолютные и относительные ссылки

Урок 25. Информатика 9 класс ФГОС

В данный момент вы не можете посмотреть или раздать видеоурок ученикам

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

Получите невероятные возможности

Конспект урока «Организация вычислений в электронных таблицах. Абсолютные и относительные ссылки»

· Что такое ссылка в электронных таблицах?

· Какие типы ссылок существуют?

· Как в электронных таблицах осуществляются вычисления?

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

Есть несколько типов ссылок:

· Относительные – это ссылки в формулах, которые изменяются при копировании.

· Абсолютные – это ссылки в формулах, которые не изменяются при копировании.

· Смешанные – это ссылки в формулах, содержащие абсолютную и относительную части.

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

1. выделить ячейку с формулой;

2. на вкладке Главная в разделе Буфер обмена нажать копировать (или нажать сочетание клавиш ctrl+С);

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

4. на вкладке Главная в разделе Буфер обмена нажать вставить (или нажать сочетание клавиш ctrl+V);

Разберемся подробнее с типами ссылок.

Использовать относительные ссылки – это все равно, что следовать указанию направления движения по улице. Например: Прогуливаясь по городу, вы поинтересовались у прохожего: «Как пройти к обелиску?». Прохожий ответил: «Вам нужно пройти прямо по улице, затем свернуть налево». Однако, если каждый раз выходить из различных начальных мест и придерживаться этих инструкций, к обелиску вы не попадёте.

То есть относительная ссылка – это ссылка, которая зависит от того, где находится формула.

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

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

· если вставлять формулы вдоль строки, то в ссылке изменяется буква в имени столбца;

· если вставлять формулы вдоль столбца, то в ссылке изменяется номер строки;

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

Таким образом: Относительные ссылки – это ссылки в формулах, которые изменяются при копировании.

Решим задачу с использование относительных ссылок.

Средняя заработная плата по данным Федеральной службы государственной статистики на 2014 г. составила 32 600 рублей. Анализ сведений по отчетам ФСГС показывает, что ежегодно средняя зарплата увеличивается на 8 %. Анализ сделан на основе сравнения зарплат по месяцам за последние 3 года. Сделаем в электронных таблицах расчет предполагаемой средней зарплаты в ближайшие 5 лет.

Внесем в таблицу исходные данные. В ячейку А1 запишем «Год», в А2 – «Текущий», далее в ячейки А3, А4, А5, А6 и А7 соответственно 1, 2, 3, 4, 5.

В ячейку В2 – 32 600. Затем в ячейку В3 запишем формулу: =В2+0,08*В2 и скопируем формулу в диапазон ячеек В4:В7.

Мы в электронной таблице сделали расчет предполагаемой заработной платы через 5 лет. Предположительно зарплата составит в среднем 47 900 рублей.

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

Абсолютная ссылка — это ссылка, которая не зависит от того, где находится формула.

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

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

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

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

Решим задачу с использованием абсолютных ссылок.

На одном из заводов в некотором городе работают Иванов, Петров и Сидоров. Известно, что Иванов отработал 40 часов, Петров – 30, а Сидоров – 32 часа. Нужно рассчитать зарплату работникам за отработанное время. Известно также, что 1 час рабочего времени стоит 100 рублей.

Заполним таблицу. В ячейку E2 запишем формулу: =$C$1*D3 и скопируем ее в ячейки E3 и E4.

Включим режим отображения формул. Для этого нажимаем вкладку Формула, в разделе зависимости формул выберем показать формулы. Теперь мы видим, что абсолютная ссылка C1 не изменялась, а относительная ссылка D2 – изменялась.

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

Рассмотрим следующий тип ссылок – Смешанные ссылки.

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

Пример со смешанными ссылками. В ячейке С2 записана формула: =$В2+3. Здесь ссылка на столбец абсолютная, а ссылка на строку –относительная. При копировании формулы в другие ячейки вдоль строки или столбца таблица будет выглядеть следующим образом.

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

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

Решим задачу с использованием смешанных ссылок.

В магазины поступил товар с артикулами ТВ01, ТВ02 и ТВ03 с соответствующими закупочными ценами 306 руб., 228 руб. и 237 руб. В первом магазине наценка на товар – 17 %, а во втором – 25 %. Нам требуется рассчитать отпускную стоимость товара при наценках, с учётом того, что закупочная цена фиксирована.

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

Включим режим отображения формул. Для этого нажимаем вкладку Формула, в разделе зависимости формул выберем «Показать формулы».

Обратите внимание! Мы рассчитывали отпускную стоимость товара по одной и той же формуле, исходные данные для которой находились в разных ячейках. У смешанных ссылок при копировании относительная часть адреса изменяется, а абсолютная часть адреса не изменяется.

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

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

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

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

Источник

Относительные и абсолютные ссылки – как создать и изменить

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

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

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

Рабочий лист в Excel состоит из ячеек. На каждую из них можно ссылаться, указав значение строки и значение столбца. Зачем это нужно? Чтобы получить значение, записанное в ней, и затем использовать его в вычислениях.

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

Например, A1 относится к адресу на пересечении столбца A и строки 1; B2 относится ко второй ячейке в столбце B и так далее.

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

Например, если вы введете простейшее выражение =A1 в клетку C1, Эксель продублирует данные из A1 в C1:

Чтобы сложить числа в ячейках A1 и A2, используйте: =A1 + A2

Что такое ссылка на диапазон?

В Microsoft Excel диапазон – это блок из двух или более ячеек. Ссылка на диапазонпредставлена адресами верхней левой и нижней правой его ячеек, разделенных двоеточием.

Например, диапазон A1:C2 включает 6 ячеек от A1 до C2.

Как создать ссылку?

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

Например, чтобы сложить значения в A1 и A2, введите знак равенства, щелкните A1, введите знак плюса, щелкните A2 и нажмите Enter:

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

Например, чтобы сложить значения в A1, A2 и A3, введите знак равенства, затем имя функции СУММ и открывающую скобку, выберите ячейки от A1 до A3, введите закрывающую скобку и нажмите Enter:

Чтобы обратиться ко всей строке или целому столбцу, щелкните номер строки или букву столбца соответственно.

Например, чтобы сложить все ячейки в строке 1, начните вводить функцию СУММ, а затем кликните заголовок первой строки, чтобы включить ссылку на строку в ваш расчёт:

Как изменить ссылку?

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

Как сделать перекрестную ссылку?

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

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

Например, вот как вы можете создать ссылку на адрес A1 на листе Лист2 в той же книге Excel:

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

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

Как сослаться на другую книгу?

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

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

Как и в случае ссылки на другой лист, вам не обязательно вводить всё это вручную. Более быстрый способ – начать писать формулу, затем переключиться на другую книгу и выбрать в ней ячейку или диапазон. Нажать Enter.

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

В Экселе есть три типа ссылок на ячейки: относительные, абсолютные и смешанные. В ваших расчётах вы можете использовать любой из них. Но если вы собираетесь скопировать записанное выражение на другое место в вашем рабочем листе, то здесь нужно быть внимательным. Важно использовать правильный тип адреса, поскольку относительные и абсолютные ссылки ведут себя по-разному при переносе и копировании.

Относительная ссылка на ячейку.

Относительная ссылка является самой простой и включает координаты строки и столбца, например А1 или А1:D10. По умолчанию все адреса ячеек в Экселе являются относительными.

Это простейшее выражение сообщает программе, что нужно показать значение, которое записано в первой колонке (A) и второй строке (2). Используя скриншот чуть ниже, если бы эта формула была помещена в ячейку D1, она отобразила бы число «8», поскольку это значение находится по адресу A2.

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

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

Например, чтобы сложить числа в A2 и B2, вы вводите это в C2: =A2+B2. При копировании из строки 2 в строку 3 выражение изменится на = A3+B3.

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

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

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

Вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3: D8). Когда вы это сделаете, вы заметите, что адрес автоматически настраивается, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 становится B3*C3, а в D4 теперь записано: B4*C4.

Абсолютная ссылка на ячейку.

Символ доллара, добавленный перед любой из координат, делает адрес абсолютным (т. е. предотвращает изменение номера строки и столбца).

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

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

Например, чтобы умножить числа в столбце B на величину скидки из F2, вы вводите следующую формулу в строке 2, а затем копируете её вниз, перетаскивая маркер заполнения:

Относительная ссылка (B2) будет изменяться в зависимости от относительного положения строки, в которую она копируется, в то время как абсолютная ($F$2) всегда будет зафиксирована на одном и том же адресе:

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

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

Абсолютные ссылки всегда указывают на конкретный адрес, независимо от того, где они находятся.

Смешанная ссылка.

Смешанные ссылки немного сложнее, чем абсолютные и относительные.

Может быть два типа смешанных ссылок:

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

Может быть много ситуаций, когда нужно фиксировать только одну координату: либо столбец, либо строку.

Например, чтобы умножить колонку с ценами (столбец В) на 3 разных значения наценки (C2, D2 и E2), вы поместите следующую формулу в C3, а затем скопируете ее вправо и затем вниз:

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

А вот во втором множителе знак доллара мы поставили перед номером строки. Поэтому при копировании формулы в D3 координаты столбца изменятся и вместо C$2 мы получим D$2. В результате в D3 получим:

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

И если ваши наценки вдруг изменятся, просто поменяйте числа в C2:E2, и проблема будет решена почти мгновенно.

Как изменить ссылку с относительной на абсолютную (или смешанную)?

Примечание. Если вы нажмете F4, не выбрав ничего конкретного, ячейка слева от указателя мыши будет выбрана автоматически и там будет изменён тип ссылки.

Имя как разновидность абсолютной ссылки.

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

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

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

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

Формула же при этом становится более понятной и читаемой.

Ссылка на столбец.

Как и на отдельные ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:

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

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

А теперь давайте посмотрим это на примере.

Предположим, у вас есть некоторые числа в колонке B, и вы хотите узнать их общее и среднее значение. Проблема в том, что новые данные добавляются в таблицу каждую неделю, поэтому писать обычную формулу СУММ() или СРЗНАЧ() для фиксированного диапазона ячеек – не лучший вариант. Вместо этого вы можете ссылаться на весь столбец B:

=СУММ($D:$D)— используйте знак доллара ($), чтобы создать абсолютную ссылку на весь столбец, которая привязывает формулу к столбцу B.

Примечание. При использовании ссылки на весь столбец никогда не вводите формулу в том же столбце, на который ссылаетесь. Например, может показаться хорошей идеей ввести =СУММ(D:D) в одну из самых нижних пустых ячеек в этом же столбце D, чтобы получить итоговый результат в конце таблицы. Не делайте этого! Это создаст так называемую циклическую ссылку, и вы получите результат 0.

Ссылка на строку.

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

Пример 2. Ссылка на всю строку (абсолютная и относительная)

Если данные в вашем листе расположены горизонтально, а не по вертикали, вы можете ссылаться на всю строку. Например, вот как мы можем рассчитать среднюю цену в строке 2:

=СРЗНАЧ($3:$3) – абсолютная ссылка на всю строку зафиксирована с помощью знака доллара ($).

=СРЗНАЧ(3:3) – относительная ссылка на строку изменится при копировании вниз.

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

Для бананов (B13) расчет уже будет такой: СРЗНАЧ(4:4). Как видите, номер строки автоматически изменился.

Ссылка на столбец, исключая первые несколько строк.

Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения, шапку даблицы или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа D3:D, которые включали бы все данные в столбце D, только начиная со строки 3. Если вы попытаетесь добавить такую ​​конструкцию, ваша формула, скорее всего, вернет ошибку #ИМЯ?.

Вместо этого вы можете указать максимальную строку, чтобы ваша ссылка включала все возможные адреса в данном столбце. В Excel с 2019 по 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии программы имеют максимум 65 536 строк и 256 столбцов.

Итак, чтобы найти сумму продаж в приведенной ниже таблице (колонка «Стоимость»), можно использовать выражение:

Как вариант, можно вычесть из общей суммы те данные, которые хотите исключить:

Но первый вариант предпочтительнее, так как СУММ(D:D) выполняется дольше и требует больше вычислительных ресурсов, чем СУММ(D3:D1048576).

Смешанная ссылка на весь столбец.

Как я упоминал ранее, вы также можете создать смешанную ссылку на весь столбец или целую строку:

В результате Эксель сложит все числа в столбцах B и C. Ну и, двигаясь далее вправо, далее можно найти сумму уже трёх колонок.

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

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге!

Источник