Как изменить адрес ячейки в Excel

Обновлено: 05.07.2024

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

Как финансовый аналитик Финансовый аналитик Описание работы Приведенное ниже описание работы финансового аналитика дает типичный пример всех навыков, образования и опыта, необходимых для найма на работу аналитика в банке, учреждении или корпорации. Выполняйте финансовое прогнозирование, отчетность и отслеживайте операционные показатели, анализируйте финансовые данные, создавайте финансовые модели. АДРЕС ячейки можно использовать для преобразования номера столбца в букву или наоборот. Мы можем использовать эту функцию для обращения к первой или последней ячейке в диапазоне.

Формула

=АДРЕС(номер_строки, номер_столбца, [номер_абс.], [a1], [текст_листа])

В формуле используются следующие аргументы:

Row_num (обязательный аргумент). Это числовое значение, указывающее номер строки, который будет использоваться в ссылке на ячейку.
Column_num (обязательный аргумент) — числовое значение, указывающее номер столбца, который будет использоваться в ссылке на ячейку.
Abs_num (необязательный аргумент). Это числовое значение, указывающее тип возвращаемой ссылки:

Abs_num	Возвращает этот тип ссылки
1 или опущен< /td>	Абсолютный
2	Абсолютный ряд; относительный столбец
3	Абсолютный столбец; относительная строка
4	Относительная

<р>4. A1 (необязательный аргумент) — это логическое значение, определяющее стиль ссылок A1 или R1C1. В стиле ссылок R1C1 и столбцы, и строки имеют числовые метки. Это может быть либо ИСТИНА (ссылка должна быть A1), либо ЛОЖЬ (ссылка должна быть R1C1).

Если этот параметр не указан, он принимает значение по умолчанию TRUE (стиль A1).

Sheet_text (необязательный аргумент) — указывает имя листа. Если мы опустим аргумент, он возьмет текущий рабочий лист.

Как использовать функцию АДРЕС в Excel?

Чтобы понять, как используется функция АДРЕС ячейки, рассмотрим несколько примеров:

Пример 1

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

Используемая формула будет следующей:

Мы получаем следующие результаты:

Функция ADDRESS сначала создаст адрес, содержащий номер столбца. Это было сделано путем предоставления 1 для номера строки, номера столбца из B6 и 4 для аргумента abs_num.

После этого мы используем функцию ПОДСТАВИТЬ, чтобы убрать число 1 и заменить его на «».

Пример 2

Функция АДРЕС может использоваться для преобразования буквы столбца в обычное число, например 21, 100, 126 и т. д. Мы можем использовать формулу, основанную на функциях ДВССЫЛ и СТОЛБЦ.

Предположим, что нам даны следующие данные:

Используемая формула будет следующей:

Мы получаем следующие результаты:

Функция ДВССЫЛ преобразует текст в правильную ссылку Excel и передает результат функции СТОЛБЦ. Затем функция COLUMN оценивает ссылку и возвращает номер столбца для ссылки.

Несколько замечаний о функции Cell ADDRESS

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ.Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

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

Данные из одной или нескольких смежных ячеек на листе.

Данные, содержащиеся в разных областях рабочего листа.

Данные на других листах в той же книге.

Эта формула:

И возвращает:

Значение в ячейке C2.

Ячейки от A1 до F4

Значения во всех ячейках, но вы должны нажать Ctrl+Shift+Enter после ввода формулы.

Примечание. Эта функция не работает в Excel для Интернета.

Ячейки с именами "Активы и обязательства"

Значение в ячейке с именем "Обязательство" вычитается из значения в ячейке с именем "Актив".

Диапазоны ячеек с именами Week1 и Week2

Сумма значений диапазонов ячеек с именами Week1 и Week 2 в виде формулы массива.

Ячейка B2 на Листе2

Значение в ячейке B2 на Листе2.

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства).

Выполните одно из следующих действий:

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

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

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

Нажмите F3, выберите имя в поле "Вставить имя" и нажмите "ОК".

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

Выполните одно из следующих действий:

Если вы создаете ссылку в одной ячейке, нажмите Enter.

Если вы создаете ссылку в формуле массива (например, A1:G4), нажмите Ctrl+Shift+Enter.

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

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

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

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства) и нужную формулу.

Перейдите на вкладку листа, на который нужно сослаться.

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

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

Кроме того, вы можете скопировать и вставить ссылку на ячейку, а затем использовать команду «Связать ячейки», чтобы создать ссылку на ячейку. Вы можете использовать эту команду для:

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

Упростите создание ссылок на ячейки между листами и книгами. Команда «Связать ячейки» автоматически вставляет правильный синтаксис.

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

Нажмите Ctrl+C или перейдите на вкладку "Главная" и в группе "Буфер обмена" нажмите "Копировать" .

Нажмите Ctrl+V или перейдите на вкладку "Главная", в группе "Буфер обмена" нажмите "Вставить" .

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

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

Выполните одно из следующих действий:

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

Чтобы включить в ссылку больше или меньше ячеек, перетащите угол границы.

В строке формул выберите ссылку в формуле, а затем введите новую ссылку.

Нажмите F3, выберите имя в поле "Вставить имя" и нажмите "ОК".

Нажмите клавишу ВВОД или, чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ВВОД.

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

Выполните одно из следующих действий:

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

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

На вкладке "Формулы" в группе "Определенные имена" щелкните стрелку рядом с пунктом "Определить имя" и выберите "Применить имена".

В поле "Применить имена" выберите одно или несколько имен, а затем нажмите "ОК".

Выберите ячейку, содержащую формулу.

В строке формул выберите ссылку, которую нужно изменить.

Нажмите F4 для переключения между типами ссылок.

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

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства).

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

Выполните одно из следующих действий:

Если вы создаете ссылку в одной ячейке, нажмите Enter.

Если вы создаете ссылку в формуле массива (например, A1:G4), нажмите Ctrl+Shift+Enter.

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения.Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Нажмите на ячейку, в которую хотите ввести формулу.

В строке формул введите = (знак равенства) и нужную формулу.

Перейдите на вкладку листа, на который нужно сослаться.

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

Выполните одно из следующих действий:

Чтобы включить в ссылку больше или меньше ячеек, перетащите угол границы.

В строке формул выберите ссылку в формуле, а затем введите новую ссылку.

Нажмите клавишу ВВОД или, чтобы ввести формулу массива, нажмите клавиши CTRL+SHIFT+ВВОД.

Выберите ячейку, содержащую формулу.

В строке формул выберите ссылку, которую нужно изменить.

Нажмите F4 для переключения между типами ссылок.

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

При работе с электронными таблицами необходимо знать об относительных и абсолютных ссылках на ячейки.

Вот проблема: когда вы КОПИРУЕТЕ ФОРМУЛУ, содержащую ссылки на ячейки, что происходит со ссылками на ячейки?

Обычно ССЫЛКИ НА КЛЕТКИ ИЗМЕНЯЮТСЯ! Если скопировать формулу на 2 строки вправо, то ссылки на ячейки в формуле сместятся на 2 ячейки вправо. Если скопировать формулу на 3 строки вниз и на 1 строку влево, то ссылки на ячейки в формуле сместятся на 3 строки вниз и на 1 строку влево. Такие ссылки называются «относительными» ячейками, поскольку они изменяются относительно того места, куда вы копируете формулу.

Если вы не хотите, чтобы ссылки на ячейки менялись при копировании формулы, сделайте эти ссылки на ячейки абсолютными ссылками на ячейки. Поместите «$» перед буквой столбца, если вы хотите, чтобы она всегда оставалась неизменной. Поместите «$» перед номером строки, если вы хотите, чтобы он всегда оставался неизменным. Например, «$C$3» относится к ячейке C3, а «$C$3» будет работать точно так же, как «C3», если вы скопируете формулу. Примечание: при вводе формул вы можете использовать клавишу F4 сразу после ввода ссылки на ячейку для переключения между различными относительными/абсолютными версиями этого адреса ячейки.

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

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

Относительные и абсолютные ссылки на ячейки

Карин Стилл

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

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

Это наиболее широко используемый тип ссылки на ячейку в формулах. Относительные ссылки на ячейки — это базовые ссылки на ячейки, которые корректируются и изменяются при копировании или использовании автозаполнения.

=СУММ(B5:B8), как показано ниже, изменяется на =СУММ(C5:C8) при копировании в следующую ячейку.

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

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

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

Более сложный пример:

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

Проверьте формулу в ячейке E4. Сделав ссылку на первую ячейку $C4, вы предотвратите изменение столбца при копировании, но позволите строке измениться при копировании вниз, чтобы приспособиться к снижению цен на различные товары. Сделав ссылку на последнюю ячейку A$12, вы предотвратите изменение номера строки при копировании вниз, но позволите столбцу измениться и отразить скидку B при копировании. Смущенный? Посмотрите на рисунок ниже и результаты ячеек.

Теперь вы можете подумать, а почему бы просто не использовать 10% и 15% в реальных формулах? Разве это не было бы проще? Да, если вы уверены, что процент скидки никогда не изменится, что крайне маловероятно. Скорее всего, в конечном итоге эти проценты необходимо будет скорректировать. Ссылаясь на ячейки, содержащие 10% и 15%, а не на фактические числа, когда процент изменяется, все, что вам нужно сделать, это изменить процент один раз в ячейке A12 и/или B12 вместо того, чтобы перестраивать все ваши формулы. Excel автоматически обновит цены со скидками, чтобы отразить изменение процента скидки.

Краткий обзор использования абсолютных ссылок на ячейки:

$A1	Разрешает изменение ссылки на строку, но не ссылка на столбец.
A$1	Разрешает изменять ссылку на столбец, но не на строку.
$A$1	Не позволяет изменять ни столбец, ни ссылку на строку.

Существует ярлык для размещения абсолютных ссылок на ячейки в ваших формулах!

При вводе формулы после ввода ссылки на ячейку нажмите клавишу F4. Excel автоматически делает ссылку на ячейку абсолютной! Продолжая нажимать F4, Excel будет циклически перебирать все возможности абсолютной ссылки. Например, в первой формуле абсолютной ссылки на ячейку в этом руководстве, =B4*$B$10, я мог бы ввести =B4*B10, а затем нажать клавишу F4, чтобы изменить B10 на $B$10. Продолжая нажимать F4, вы получите 10 B$, затем B10 и, наконец, B10. Нажатие F4 изменяет только ссылку на ячейку непосредственно слева от точки вставки.

Я надеюсь, что этот учебник сделал эти типы ссылок на ячейки «абсолютно» понятными!

Функция АДРЕС Excel возвращает адрес ячейки на основе заданного номера строки и столбца. Например, =АДРЕС(1,1) возвращает $A$1. АДРЕС может возвращать адрес в относительном, смешанном или абсолютном формате и может использоваться для создания ссылки на ячейку внутри формулы.

номер_строки – номер строки, который будет использоваться в адресе ячейки.
col_num — номер столбца, который будет использоваться в адресе ячейки.
abs_num – [необязательно] Тип адреса (т. е. абсолютный, относительный). По умолчанию абсолютное значение.
a1 – [необязательно] Эталонный стиль, A1 и R1C1. По умолчанию используется стиль A1.
лист – [необязательно] имя используемого рабочего листа. По умолчанию используется текущий лист.

Функция АДРЕС возвращает адрес ячейки на основе заданного номера строки и столбца. Например, =АДРЕС(1,1) возвращает $A$1. ADDRESS может возвращать относительную, смешанную или абсолютную ссылку и может использоваться для создания ссылки на ячейку внутри формулы. Важно понимать, что ADDRESS возвращает ссылку в виде текстового значения. Если вы хотите использовать этот текст внутри ссылки на формулу, вам нужно будет привести текст к правильной ссылке с помощью функции ДВССЫЛ. Если вы хотите указать номер строки и столбца и получить обратно значение по этому адресу, используйте функцию ИНДЕКС.

Функция АДРЕС принимает пять аргументов: строка, столбец, abs_num, a1 и лист_текст. Строка и столбец являются обязательными, другие аргументы необязательны. Аргумент abs_num определяет, является ли возвращаемый адрес относительным, смешанным или абсолютным, со значением по умолчанию 1 для абсолютного.Аргумент a1 — это логическое значение, которое переключает ссылки на стили A1 и R1C1 со значением по умолчанию TRUE для ссылок на стили A1. Наконец, аргумент sheet_text предназначен для хранения имени листа, которое будет добавлено к адресу.

Параметры АБС

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

< tr>

Примеры

Используйте ADDRESS, чтобы создать адрес из заданного номера строки и столбца. Например:

Читайте также:

abs_num	Результат
1 (или опущен)	Абсолютный ($A$1)
2	Абсолютная строка, относительный столбец (A$1)
3	Относительная строка, абсолютный столбец ($A1)
4	Относительная (A1)