Что такое ссылка на ячейку в excel
Абсолютные и относительные ссылки в Excel
В Excel существует два типа ссылок: абсолютные и относительные. Эти ссылки ведут себя по-разному при копировании и заполнении ячеек. Относительные ссылки изменяются когда вы копируете формулу из одной ячейки в другую, а абсолютные ссылки, напротив, не меняются вне зависимости от того, куда бы вы их ни скопировали.
Относительные ссылки в Excel
Как создать и скопировать формулу с относительными ссылками
На примере ниже мы хотим создать формулу, которая поможет в калькуляции итогового счета за заказ в ресторане. Мы хотим в колонке «Итог» рассчитать сумму к оплате за каждое блюдо, в зависимости от его стоимости за единицу и количества. Для этого, мы создадим одну формулу с относительной ссылкой и скопируем ее на все строки таблицы. Для этого проделаем следующие шаги:
Абсолютные ссылки в Excel
Часто, при расчетах нам нужно, чтобы при копировании формул, ссылки на ячейки не изменялись. В отличие от относительных ссылок, абсолютные позволяют зафиксировать при расчетах ячейки в определенных строках и столбцах, что делает процесс вычисления в таблицах более простым и эффективным.
Для создания абсолютной ссылки используется знак доллара «$». С его помощью вы можете зафиксировать от изменений столбец, строку или всех вместе:
$A$2 — столбец и строка не изменяются при копировании формулы;
A$2 — при копировании формулы не меняется только строка;
Используя абсолютные ссылки в Excel, вы можете быстро изменять настройки фиксации столбца и строки, с помощью клавиши F4. Для этого нужно дважды кликнуть на ячейку с формулой, затем, левой клавишей мыши поставить курсор на значение ячейки и с помощью клавиши F4 настроить фиксацию строки и столбца.
Как создать и скопировать формулу с абсолютными ссылками
Как создать ссылки на другие листы в Excel
Зачастую, нам в расчетах требуется задействовать данные с разных листов файла Excel. Для этого, при создании ссылки на ячейку из другого листа нужно использовать название листа и восклицательного знака на конце ( ! ). Например, если вы хотите создать ссылку на ячейку A1 на листе Sheet1, то ссылка на эту ячейку будет выглядеть так:
ВАЖНО! Если в название листа, на ячейку с которого вы ссылаетесь есть пробелы, то название этого листа в ссылке должно быть заключено в кавычки (‘ ‘). Например, если название вашего листа Бюджет Финал, то ссылка на ячейку A1 будет выглядеть так:
На примере ниже, мы хотим добавить в таблицу ссылку на ячейку, в которой уже произведены вычисления между двумя листами Excel файла. Это позволит нам использовать одно и то же значение на двух разных листах без перезаписи формулы или копирования данных между рабочими листами. Для этого проделаем следующие шаги:
Если, в дальнейшем, вы переименуете лист, на который вы ссылались, то система автоматически обновит формулу.
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера 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 ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. «морской бой»), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:
Смешанные ссылки
Абсолютные ссылки
Все просто и понятно. Но есть одно «но».
Действительно абсолютные ссылки
Если ввести в ячейку формулу:
=ДВССЫЛ(«C5»)
=INDIRECT(«C5»)
то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:
=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))
=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))
Создание и изменение ссылки на ячейку
ссылка на ячейку указывает на ячейку или диапазон ячеек листа. Ссылки можно применять в формула, чтобы указать приложению Microsoft Office Excel на значения или данные, которые нужно использовать в формуле.
Ссылки на ячейки можно использовать в одной или нескольких формулах для указания на следующие элементы:
данные из одной или нескольких смежных ячеек на листе;
данные из разных областей листа;
данные на других листах той же книги.
Значение в ячейке C2
Значения во всех ячейках, но после ввода формулы необходимо нажать сочетание клавиш Ctrl+Shift+Enter.
Примечание. Эта функция не работает в Excel в Интернете.
Ячейки с именами «Актив» и «Пассив»
Разность значений в ячейках «Актив» и «Пассив»
Диапазоны ячеек «Неделя1» и «Неделя2»
Сумма значений в диапазонах ячеек «Неделя1» и «Неделя2» как формула массива
Ячейка B2 на листе Лист2
Значение в ячейке B2 на листе Лист2
Щелкните ячейку, в которую нужно ввести формулу.
В строка формул 
введите = (знак равенства).
Выполните одно из следующих действий.
Создайте ссылку на одну или несколько ячеек. Чтобы создать ссылку, выделите ячейку или диапазон ячеек на том же листе.
Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
Создайте ссылку на определенное имя. Чтобы создать ссылку на определенное имя, выполните одно из указанных ниже действий.
Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.
Примечание: Если в углу цветной границы нет квадратного маркера, значит это ссылка на именованный диапазон.
Выполните одно из указанных ниже действий.
Если требуется создать ссылку в отдельной ячейке, нажмите клавишу ВВОД.
Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.
Ссылка может быть одной ячейкой или диапазоном, а формула массива может возвращать одно или несколько значений.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Щелкните ячейку, в которую нужно ввести формулу.
В строка формул введите = (знак равенства) и формулу, которую нужно использовать.
Щелкните ярлычок листа, на который нужно сослаться.
Выделите ячейку или диапазон ячеек, на которые нужно сослаться.
Примечание: Если имя другого листа содержит знаки, не являющиеся буквами, необходимо заключить имя (или путь) в одинарные кавычки ( ‘).
Также можно скопировать и вставить ссылку на ячейку, а затем воспользоваться командой Ссылки на ячейки для создания ссылки на ячейку. Эту команду можно использовать в указанных ниже случаях.
Для отображения важных данных в более заметном месте. Предположим, существует книга с множеством листов, на каждом из которых есть ячейка, отображающая итоговые данные по другим ячейкам этого листа. Чтобы выделить такие итоговые ячейки, можно создать ссылки на них с первого листа книги, которые позволят увидеть итоговые данные из всей книги на ее первом листе.
Для упрощения ссылок на ячейки между листами и книгами. Команда Ссылки на ячейки автоматически вставляет выражения с правильным синтаксисом.
Выделите ячейку с данными, ссылку на которую необходимо создать.
Нажмите клавиши CTRL+C или перейдите на вкладку Главная и в группе Буфер обмена щелкните Копировать 
.
Нажмите клавиши CTRL+V или перейдите на вкладку Главная и в группе Буфер обмена щелкните Вставить 
.
По умолчанию при вставке скопированных данных отображается кнопка Параметры вставки 
.
Нажмите кнопку Параметры вставки, а затем выберите пункт Вставить связь 
.
Дважды щелкните ячейку, содержащую формулу, которую нужно изменить. Каждая ячейка или диапазон ячеек в Excel, на которые ссылается формула, выделяются своим цветом.
Выполните одно из указанных ниже действий.
Чтобы переместить ссылку на ячейку или диапазон, перетащите цветную границу к новой ячейке или диапазону.
Чтобы изменить количество ячеек в диапазоне, перетащите угол границы.
В строка формул выделите ссылку в формуле и введите новую ссылку .
Нажмите клавишу F3, выберите имя в поле Вставить имя и нажмите кнопку ОК.
Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если после ввода ссылки на ячейку в формулу задается имя для ссылки на ячейку, иногда требуется заменить существующие ссылки на ячейки определенными именами.
Выполните одно из указанных ниже действий.
Выделите диапазон ячеек, содержащих формулы, в которых необходимо заменить ссылки на ячейки определенными именами.
Чтобы заменить ссылки именами во всех формулах листа, выделите одну пустую ячейку.
На вкладке Формулы в группе Определенные имена щелкните стрелку рядом с кнопкой Присвоить имя и выберите команду Применить имена.
Выберите имена в поле Применить имена, а затем нажмите кнопку ОК.
Выделите ячейку с формулой.
В строке формул строка формул выделите ссылку, которую нужно изменить.
Для переключения между типами ссылок нажмите клавишу F4.
Дополнительные сведения о разных типах ссылок на ячейки см. в статье Обзор формул.
Щелкните ячейку, в которую нужно ввести формулу.
В строка формул введите = (знак равенства).
Выделите ячейку или диапазон ячеек на том же листе. Можно переместить границу выделения, перетащив границу ячейки, или перетащить угол границы, чтобы расширить выделение.
Выполните одно из указанных ниже действий.
Если требуется создать ссылку в отдельной ячейке, нажмите клавишу ВВОД.
Если требуется создать ссылку в формула массива (например A1:G4), нажмите сочетание клавиш CTRL+SHIFT+ВВОД.
Ссылка может быть одной ячейкой или диапазоном, а формула массива может возвращать одно или несколько значений.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Щелкните ячейку, в которую нужно ввести формулу.
В строка формул введите = (знак равенства) и формулу, которую нужно использовать.
Щелкните ярлычок листа, на который нужно сослаться.
Выделите ячейку или диапазон ячеек, на которые нужно сослаться.
Примечание: Если имя другого листа содержит знаки, не являющиеся буквами, необходимо заключить имя (или путь) в одинарные кавычки ( ‘).
Дважды щелкните ячейку, содержащую формулу, которую нужно изменить. Каждая ячейка или диапазон ячеек в Excel, на которые ссылается формула, выделяются своим цветом.
Выполните одно из указанных ниже действий.
Чтобы переместить ссылку на ячейку или диапазон, перетащите цветную границу к новой ячейке или диапазону.
Чтобы изменить количество ячеек в диапазоне, перетащите угол границы.
В строка формул выделите ссылку в формуле и введите новую ссылку.
Нажмите клавишу ВВОД или, в случае формула массива, клавиши CTRL+SHIFT+ВВОД.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Выделите ячейку с формулой.
В строке формул строка формул выделите ссылку, которую нужно изменить.
Для переключения между типами ссылок нажмите клавишу F4.
Дополнительные сведения о разных типах ссылок на ячейки см. в статье Обзор формул.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Относительные и абсолютные ссылки – как создать и изменить
В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в 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.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге!