Как использовать ссылку на ячейку в Excel (Инструкции и часто задаваемые вопросы)
При создании формулы в Excel вы можете захотеть сослаться на ячейки вместо того, чтобы вводить числа. Эта функция позволяет обновлять формулы, изменяя ссылочную ячейку вместо редактирования всей формулы. Понимание относительных и абсолютных ссылок на ячейки может облегчить копирование формул в другие ячейки, обеспечивая при этом правильность вычислений. В этой статье мы объясним, как использовать ссылку на ячейку в Excel, и ответим на некоторые часто задаваемые вопросы, чтобы помочь вам лучше понять эту функцию.
Что такое ссылка на ячейку в Excel?
Ссылка на ячейку в Excel — это комбинация буквы столбца и номера строки, которая ссылается на значение в ячейке. Например, A1 — это ссылка на значение в ячейке A1. Пользователи вставляют ссылки на ячейки в формулы, позволяя функции использовать значение ячейки как часть вычисления. Например, если вы хотите сложить значения A1 и B1, вы можете использовать A1 и B1 в качестве ссылок на ячейки для создания следующей формулы: =SUM(A1+B1).
Обратите внимание, что ссылки на ячейки могут быть либо одной ячейкой, как A1 и B1, либо диапазоном ячеек, как A1:B1.
Следует отметить, что ссылки на ячейки могут быть как относительными, так и абсолютными. Относительные ссылки на ячейки используются по умолчанию, и они изменяются в зависимости от того, куда вы их вставляете в рабочем листе. Например, если вы скопируете и вставите =SUM(D2:D10) из D11 в E11, оно изменится на =SUM(E2:E10). Если вы хотите, чтобы формула осталась неизменной, вы можете включить абсолютную ссылку на ячейку, добавив $ символы перед буквами и цифрами ячеек. Например, если вы скопируете и вставите =SUM($D$2:$D$10) из D11 в E11, то останется =SUM($D$2:$D$10).
Как использовать относительную ссылку на ячейку в Excel
Вот как использовать относительную ссылку на ячейку в Excel:
1. Определите, подходит ли относительная ссылка на ячейку для вашей задачи
Прежде чем использовать относительную ссылку на ячейку, определите, подходит ли она для задачи, которую вы хотите решить в своей электронной таблице.
Относительные ссылки на ячейки идеально подходят для того, чтобы различные ячейки использовали значения в своей строке или столбце для выполнения одного и того же вычисления. Например, если вы хотите рассчитать общую стоимость каждого пункта меню в этой электронной таблице для мероприятия по организации питания, вам пригодится ссылка на относительную ячейку. Вы можете использовать цену за единицу и количество в качестве относительных ссылок в формуле для расчета общей стоимости каждого пункта меню.
| Пункт меню | Цена за единицу | Количество | Общая стоимость |
| Гамбургер | $3.44 | 50 | |
| Тако | $2.75 | 75 | |
| Пицца | $9.60 | 10 |
2. Введите формулу, которую вы хотите скопировать в другие ячейки
Выберите ячейку, которая находится выше или левее других ячеек, где вы хотите, чтобы появилась формула. Введите нужную формулу, используя ссылки на ячейки, и нажмите кнопку Введите на клавиатуре.
В приведенном выше примере вы можете выбрать D2, потому что она находится над D3 и D4, двумя ячейками, в которых вы хотите, чтобы также появилась формула. Затем введите =SUM(B2*C2) в ячейку D2 и нажмите кнопку Введите. Этот расчет умножает цену за единицу гамбургера на его количество, чтобы вывести общую стоимость в $172.
| Пункт меню | Цена за единицу | Количество | Общая стоимость |
| Гамбургер | $3.44 | 50 | $172 |
| Тако | $2.75 | 75 | |
| Пицца | $9.60 | 10 |
3. Копирование формулы в другие ячейки
Выберите ячейку, которую вы выбрали в предыдущем шаге. Нажмите на ручку заливки, которая появится в правом нижнем углу ячейки, и перетащите ее в нужные ячейки. Это действие копирует введенную вами формулу, но делает ссылки на ячейки относительными для каждой строки или столбца.
Например, в приведенном выше примере вы можете перетащить формулу в ячейке D2 в ячейки D3 и D4.
Вместо отображения $172, D3 и D4 отображают общую стоимость тако и пиццы соответственно. Вы можете проверить формулу в ячейках, щелкнув по каждой из них и посмотрев на панель формул Excel. Например, когда вы щелкните на D3, в строке формул появится =SUM(B3*C3). В строке формул отображается =SUM(B4*C4) при нажатии на D4.
| Пункт меню | Цена за единицу | Количество | Общая стоимость |
| Гамбургер | $3.44 | 50 | $172 |
| Тако | $2.75 | 75 | $206.25 |
| Пицца | $9.60 | 10 | $96 |
Как использовать абсолютную относительную ссылку на ячейку в Excel
Вот как использовать абсолютную ссылку на ячейку в Excel:
1. Определите, подходит ли абсолютная ссылка на ячейку для вашей задачи
Абсолютная ссылка на ячейку уместна, когда в нескольких ячейках используется одна и та же переменная в формуле. Например, если вы хотите рассчитать налог с продаж для каждого пункта меню, вы можете использовать абсолютную ссылку на ячейку.
Цена за единицу и количество будут относительными ссылками на ячейки, но ставка налога с продаж будет абсолютной ссылкой на ячейку, поскольку все пункты меню имеют одинаковую ставку налога с продаж.
| Пункт меню | Цена за единицу | Количество | Налог с продаж |
| Гамбургер | $3.44 | 50 | |
| Тако | $2.75 | 75 | |
| Пицца | $9.60 | 10 | |
| Ставка налога с продаж: | 7.5% |
2. Введите формулу, используя абсолютную ссылку на ячейку
Введите нужную формулу в ячейку, используя абсолютную ссылку на ячейку. Например, если вы хотите рассчитать налог с продаж для каждого пункта меню, вы можете начать с расчета налога с продаж для гамбургеров. Выберите D2 и введите эту формулу: =SUM((B2*C2)*$B$6). Эта формула умножает произведение цены единицы товара и количества на ставку налога с продаж.
Сайт $ символы перед номером строки и буквой столбца указывают формуле использовать значение в ячейке B6 даже при копировании формулы в другие ячейки, что гарантирует, что во всех расчетах используется один и тот же налог с продаж.
| Пункт меню | Цена единицы | Количество | Налог с продаж |
| Гамбургер | $3.44 | 50 | $12.90 |
| Тако | $2.75 | 75 | |
| Пицца | $9.60 | 10 | |
| Налог с продаж | 7.5% |
3. Скопируйте формулу в другие ячейки
Выберите ячейку, в которую вы ввели формулу, нажмите на ручку заливки в правом нижнем углу и перетащите формулу в другие ячейки. Это действие копирует формулу, позволяя каждой ячейке использовать абсолютную ссылку на ячейку, которую вы ввели. Например, если вы скопируете формулу, написанную в D2, в D3 и D4, программа использует 7.
5% ставка для расчета налога с продаж для тако и пиццы.
Проверьте формулу, щелкнув по каждой ячейке. Когда вы щелкаете на D3, в строке формул отображается =SUM((B3*C3)*$B$6). Эта формула верна, потому что цена за единицу и количество были относительными ссылками на ячейки, а налог с продаж — абсолютной ссылкой на ячейку.
| Пункт меню | Объединенная цена | Количество | Налог с продаж |
| Гамбургер | $3.44 | 50 | $12.90 |
| Тако | $2.75 | 75 | $15.47 |
| Пицца | $9.60 | 10 | $7.20 |
| Ставка налога с продаж: | 7.5% |
Часто задаваемые вопросы о том, как использовать ссылку на ячейку в Excel
Вот несколько часто задаваемых вопросов о том, как использовать ссылку на ячейку в Excel:
Какое сочетание клавиш используется для добавления абсолютной ссылки на ячейку?
Клавиатурное сочетание для добавления абсолютной ссылки на ячейку — F4.
Введите ячейку, которую вы хотите обозначить как абсолютную ссылку, и нажмите кнопку F4. Это действие добавляет $ символ перед буквой и номером ячейки, который обеспечивает постоянство столбца и ячейки.
Если вы хотите, чтобы только строка оставалась неизменной, нажмите кнопку F4 снова. Нажмите F4 снова, чтобы сохранить постоянным только столбец. Нажатие F4 четвертый раз возвращает значение к относительной ссылке на ячейку. Если эта функция не работает на вашем компьютере, попробуйте нажать клавишу Fn на клавиатуре при нажатии клавиши F4.
Какой тип ссылки на ячейку используется по умолчанию?
Относительные ссылки на ячейки — это тип ссылок на ячейки по умолчанию. Если вы ссылаетесь на ячейку в формуле и копируете формулу в другие ячейки, программа автоматически корректирует ссылки на ячейки в соответствии со строкой и столбцом каждой ячейки. Единственный способ сделать абсолютную ссылку на ячейку — это добавить $ символы или с помощью сочетания клавиш F4.
Что происходит при редактировании абсолютной ссылки на ячейку?
Если вы редактируете абсолютную ссылку на ячейку, формула корректируется для учета нового значения. Например, представьте, что D4 содержит такую формулу: =SUM(B4*$C$4). Если B4 содержит значение 2, а C4 содержит значение 4, то D4 отображает 8. Если изменить значение ячейки C4 с 4 на 8, в ячейке D4 появится значение 16.
Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с Indeed.
46. Понятие книги, листа ячейки электронной таблицы. Абсолютная и относительная ссылка на ячейку. (ms Excel)
Оглавление
46. Понятие книги, листа ячейки электронной таблицы. Абсолютная и относительная ссылка на ячейку. (MS Excel) 1
47. Абсолютная и относительная ссылка на ячейку. (MS Excel) 2
48. Ввод и редактирование данных в электронных таблицах (MS Excel) 3
49. Форматы строки, столбца, ячеек электронной таблицы и их установка. 4
50.
Основные форматы данных.
(MS Excel ). 4
51. Организация вычислений в электронной таблице: ввод и копирование формулы. 5
52. Организация вычислений в электронной таблице: основные функции. Мастер функций (MS Excel) 6
53. Создание и редактирование диаграмм и графиков в электронной таблице: этапы построения (MS Excel) 8
54. Список в электронной таблице, назначение, структура, средства обработки. 10
55. Выполнение аналитической обработки данных в списке: сортировка, отбор данных по критерию, подведение итогов. (MS Excel) 11
56. Защита данных в электронной таблице. (MS Excel) 14
Программа Microsoft Excel предназначена для работы с таблицами данных,
преимущественно числовых. При формировании таблицы выполняют ввод, редактирование и форматирование текстовых и числовых данных, а также формул. Наличие средств автоматизации облегчает эти операции. Созданная таблица может быть выведена на печать.Документ
Excel называется рабочей книгой.
Рабочая
книга представляет собой набор рабочих листов,
каждый из которых имеет табличную
структуру и может содержать одну или
несколько таблиц. В окне документа в
программе Excel отображается только
текущий рабочий лист, с которым и ведется
работа Каждый рабочий лист имеет
название, которое отображается на
ярлычке листа, отображаемом в его нижней
части. С помощью ярлычков можно
переключаться к другим рабочим листам,
входящим в ту же самую рабочую книгу.
Чтобы переименовать рабочий лист, надо
дважды щелкнуть на его ярлычке.
латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от А до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536 (максимально допустимый номер строки).
Ячейки и их адресация. Ячейка — это основной элемент электронной таблицы только в ней может содержаться какая-либо информация (текст, значения, формулы )
На
пересечении столбцов и строк образуются
ячейки таблицы.
Они являются минимальными
элементами для хранения данных.
Обозначение отдельной ячейки сочетает
в себе номера столбца и строки (в этом
порядке), на Пересечении которых она
расположена, например: А1 или DE234.
Обозначение ячейки (ее номер) выполняет
функции ее адреса. Адреса ячеек
используются при записи формул,
определяющих взаимосвязь между
значениями, расположенными в разных
ячейках.
Одна из ячеек всегда является активной и выделяется рамкой активной ячейки. Операции ввода и редактирования всегда производятся в активной ячейке. Переместить рамку активной ячейки можно с помощью курсорных клавиш или указателя мыши.
Диапазон
ячеек. На данные, расположенные в соседних
ячейках, можно ссылаться в формулах как
на единое целое. Такую группу ячеек
называют диапазоном. Наиболее часто
используют прямоугольные диапазоны,
образующиеся на пересечении группы
последовательно идущих строк и группы
последовательно идущих столбцов.
Диапазон ячеек обозначают, указывая
через двоеточие номера ячеек, расположенных
в противоположных углах прямоугольника,
например: А1 :С15.
Рамка текущей ячейки при этом расширяется, охватывая весь выбранный диапазон. Чтобы выбрать столбец или строку целиком, следует щелкнуть на заголовке(столбца, строки)Протягиванием указателя по заголовкам можно выбрать несколько идущих подряд столбцов или строк.
Адресация ячеек. Существует 2 вида ячеек: 1) относительная (пример с В3)- при проведении копирования и перемещении, относительный адрес ячейки всегда меняется: при копировании вниз № строки увеличивается на 1, при копировании вправо имя столбца изменяется на 1 букву в сторону увеличения; 2) абсолютная- не изменяется при проведении любых манипуляций с ячейками, обозначенных $B$3, не изменится № строки, ни название столбца. Если B$2-не изменится № строки, $B2-не изменится название столбца.
Ссылки на ячейки Excel — относительные и абсолютные ссылки в правилах условного форматирования
Недавно мы опубликовали несколько статей, посвященных различным аспектам условного форматирования Excel.
Неожиданно оказалось, что не создание правила и даже не составление правильной формулы представляет собой наибольшую проблему. Использование правильных ссылок на ячейки в формулах Excel кажется наиболее сложной частью и частым источником проблем.
«Мое условное правило было правильным, за исключением смешанных ссылок.» Об этом часто сообщают в комментариях читатели нашего блога. Итак, почему бы нам не потратить несколько минут, чтобы разобраться в этом? Это, безусловно, сэкономит вам гораздо больше времени в долгосрочной перспективе!
Как относительные и абсолютные ссылки на ячейки работают в правилах условного форматирования
Во всех формулах Excel, включая правила условного форматирования, ссылки на ячейки могут быть следующих типов:
- Абсолютные ссылки на ячейки (со знаком $, например, $A$1) всегда остаются постоянными, независимо от того, куда они копируются.
- Относительные ссылки на ячейки (без знака $, например, A1) изменяются в зависимости от относительного положения строк и столбцов при копировании в несколько ячеек.
- Ссылки на смешанные ячейки (абсолютный столбец и относительная строка (например, $A1) или относительный столбец и абсолютная строка (например, A$1). В правилах условного форматирования Excel чаще всего используются смешанные ссылки на ячейки, указывающие на то, что буква столбца или номер строки должен оставаться фиксированным, когда правило применяется ко всем другим ячейкам в выбранном диапазоне.
В условном форматировании Excel ссылок на ячейки относятся к верхней левой ячейке в применяемом диапазоне . Таким образом, при создании нового правила вы можете просто представить, что пишете формулу только для верхней левой ячейки, и Excel «скопирует» вашу формулу во все остальные ячейки в выбранном диапазоне. Если ваша формула ссылается на неправильную ячейку, возникнет несоответствие между активной ячейкой и формулой, что приведет к условному форматированию выделения неправильных ячеек.
Теперь позвольте мне показать вам несколько примеров, демонстрирующих, как идентичные на первый взгляд формулы дают разные результаты в зависимости от того, какие типы ссылок на ячейки используются.
Пример 1. Абсолютный столбец и относительная строка
Этот шаблон наиболее типичен для правил условного форматирования, и в 90% случаев ссылки на ячейки в ваших правилах условного форматирования Excel будут относиться к этому типу.
Давайте составим очень простое правило, которое сравнивает значения в столбцах A и B и выделяет значение в столбце A, если оно больше, чем значение в столбце B в той же строке.
Если вам нужны подробные инструкции о том, как создавать правила условного форматирования с помощью формул, вот вам — Создание правила условного форматирования Excel с помощью формулы. В этом случае формула очевидна:
=$A1>$B1
Поскольку вы всегда сравниваете значения в столбцах A и B, вы «исправляете» эти столбцы, используя абсолютные ссылки на столбцы , обратите внимание на знак $ перед буквами столбцов в приведенной выше формуле. И, поскольку вы сравниваете значения в каждой строке по отдельности, вы используете относительные ссылки на строки , без $.
Пример 2. Относительный столбец и абсолютная строка
Этот тип ссылки на ячейку противоположен предыдущему. В этом случае номер строки всегда остается постоянным, а столбец изменяется. Вы используете такие ссылки, когда хотите сравнить значения в данной строке с определенным значением или со значениями в другой строке.
Например, приведенная ниже формула сравнивает значения в строках 1 и 2, и правило выделяет значение в строке 1, если оно больше, чем значение в строке 2 в том же столбце:
=1$>2$
Поскольку вы хотите, чтобы номера строк были фиксированными, вы используете абсолютные ссылки на строки со знаком $. А поскольку вы хотите сравнивать значения в каждом столбце по отдельности, вы создаете правило для крайнего левого столбца (A) и используете относительных ссылок на столбцы без знака $.
Пример 3. Абсолютный столбец и абсолютная строка
Вы используете абсолютные ссылки на строки и столбцы, если хотите сравнить все значения в выбранном диапазоне с каким-либо другим значением.
Например, давайте создадим правило, которое выделяет все значения в столбце A, превышающие значение в ячейке B1. Формула выглядит следующим образом:
=$A1>$B$1
Обратите внимание на использование следующих артикулов:
- $A1 — вы используете абсолютные ссылки на столбцы и относительные строки, потому что мы хотим сверить значения во всех ячейках столбца A со значением в ячейке B1.
- $B$1 — вы используете абсолютный столбец и абсолютную строку , потому что ячейка B1 содержит значение, с которым вы хотите сравнить все остальные значения, и вы хотите, чтобы эта ссылка на ячейку была постоянной.
Пример 4. Относительный столбец и относительная строка
Этот тип ссылки меньше всего используется в правилах условного форматирования Excel. Вы используете ссылок на относительный столбец и относительную строку , когда хотите проверить все ячейки выбранного диапазона на соответствие определенному значению.
Предположим, вы хотите выделить все ячейки в столбцах A и B, которые больше значения в ячейке B1. Вы можете просто скопировать формулу из предыдущего примера и заменить $A1 на A1, так как вы не хотите исправлять ни строку, ни столбец:
=$A1>$B$1
Помните, вы пишете формулу для верхней левой ячейки вашего диапазона, в нашем случае A1. Когда вы создаете правило с приведенной выше формулой и применяете его к некоторому диапазону, скажем, A1:B10, результат будет выглядеть примерно так:
Совет. Чтобы быстро переключаться между абсолютными и относительными ссылками, выберите ссылку на ячейку в строке формул и нажмите функциональную клавишу F4. Ссылка будет чередоваться между четырьмя типами от относительной к абсолютной, например: A1 > $A$1 > A$1 > $A1, а затем обратно к относительной ссылке A1.
Надеюсь, эти простые примеры помогли вам понять сущность относительных и абсолютных ссылок на ячейки в Excel. Теперь, когда вы знаете, как определить подходящий тип ссылки для ваших правил, приступайте к использованию возможностей условного форматирования Excel для своих проектов.
Следующие ресурсы могут оказаться полезными.
Полезные ресурсы
- Формулы Excel для условного форматирования на основе другой ячейки — в этом руководстве представлено несколько примеров, демонстрирующих, как форматировать отдельные ячейки или целые строки на основе значения другой ячейки.
- Как условно форматировать даты в Excel — в статье объясняется, как можно применять условное форматирование Excel к датам с помощью встроенных правил и формул.
- Как изменить цвет строки на основе значения ячейки — в этой статье вы узнаете, как выделять целые строки на основе числового или текстового значения одной ячейки.
- Чередовать цвета строк и затенение столбцов в Excel — в этом руководстве объясняется, как чередовать каждую вторую строку или столбец в Excel. Вы также найдете формулу для чередования цветов строк в зависимости от изменения значения.
- Изменение цвета фона в зависимости от значения ячейки — два быстрых способа изменить цвет фона ячеек в зависимости от их значений.
- Как автоматически выделять повторяющиеся значения в Excel — узнайте, как использовать условное форматирование Excel для выделения повторяющихся значений.
Как использовать абсолютную и относительную ссылку в Excel и Google Sheets на Mac
Знак доллара ($) в Excel сообщает Excel, что следует изменить или не изменить ссылку на ячейку , когда формула копируется в другую ячейку. Этот учебник поможет пользователям Excel всех уровней на как управлять абсолютными и относительными ссылками в Excel и Mac.
Рис. 1. Абсолютная и относительная ссылка в Excel и Google Sheets на Mac
*** Чтобы скопировать формулу, нажмите на ячейку с формулой, справа внизу появится маленькое окошко. Наведите курсор на поле. Теперь вы увидите знак +. Щелкните левой кнопкой мыши и перетащите знак + вниз.
Что такое относительная ссылка на ячейку в Excel ? Относительная ссылка на ячейку Excel — это Адрес ячейки без знака доллара в строке и столбце, когда мы копируем формулу.
Когда мы копируем формулу с относительной ссылкой на ячейку, ссылка на ячейку изменяется в зависимости от относительного положения строк и столбцов.
Рис. 2. Относительная ссылка на ячейку в Excel
Как показано на рис. 2, возвращаемый результат в ячейке C4 для умноженных значений, на которые ссылаются в ячейке A4 и ячейке B4 . Формула выглядит так:
Ячейка D4: =A4*B4
Ячейка D5: =A5*B5
Ячейка D6: =A6*B6
Ячейка D7: =A7*B7
Ячейка D8: =A8*B8
Что такое Абсолютная ссылка?
Абсолютная ссылка на ячейку Excel — это Адрес ячейки со знаком доллара в строке и столбце, когда мы копируем формулу. Когда мы копируем формулу с абсолютной ссылкой на ячейку, ссылка на ячейку остается неизменной независимо от строки и/или столбца.
Как сделать абсолютную ссылку в Excel
Рисунок 3: Абсолютная ссылка на ячейку в Excel
Как показано на рисунке 3, возвращаемый результат в Ячейка C4 предназначена для абсолютных умноженных значений, на которые ссылаются в ячейке A4 и ячейке B4 . Формула при копировании выглядит так:
. Ячейка D4: =$A$4*$B$4
Ячейка D5: =$A$4*$B$4
Ячейка D6: =$A$4*$B$4
Ячейка D7: =$A$4*$B$4
Ячейка D8: =$A$4*$B$4
Смешанная ссылка на ячейку в Excel — это адрес ячейки , в котором фиксирована либо буква столбца, либо номер строки. Это означает, что либо буква столбца, либо номер строки заблокированы или абсолютная ссылка , например.
в $A1 столбец A заблокирован, но строка 1 является относительной.
Рисунок 4a: Ссылка на смешанную ячейку в Excel
Как показано на рис. 4а, возвращаемый результат в Ячейка C4 предназначена для умноженных значений со ссылкой на смешанный столбец (столбец A) в ячейке A4 и ячейке B4 . Формула при копировании выглядит так:
. Ячейка D4: =$A4*B4
Ячейка D5: =$A5*B5
Ячейка D6: =$A6*B6
Ячейка D7: =$A7*B7
Ячейка D8: =$A8*B8
Если мы добавим знак доллара к номеру строки столбца A, результат будет выглядеть так:
Рисунок 4b: Ссылка на смешанную ячейку в Excel
Как показано на рис. 4b, возвращаемый результат в ячейке C4 представляет собой умножение значений с заблокированной ссылкой для столбца A и строки 4 в ячейке A4 и ячейке B4 .
Формула при копировании выглядит так:
Ячейка D4: =$A$4*B4
Ячейка D5: =$A$4*B5
Ячейка D6: =$A$4*B6
Ячейка D7: =$A$4*B7
Ячейка D8: =$A$4*B8
В приведенном ниже примере мы увидим, что хотя столбец B является относительной ссылкой, поскольку строка 4 заблокирована в формуле, наш результат всегда возвращается как 4 .
Рисунок 4c: Ссылка на смешанную ячейку в Excel
Формула на рис. 4c остается равной $A$4*B$4 при копировании.
Ярлык для переключения между смешанными, абсолютными и относительными ссылками- Мы выберем ячейку с нашей формулой, например. Ячейка C4 на рисунке 2
- Мы нажмем КЛАВИШУ F2 , чтобы войти в режим редактирования. Мы также можем дважды щелкнуть ячейку .
