Чтобы производить точные вычисления в Excel, важно понимать, как работают различные типы ссылок на ячейки.
A1 против R1C1 Ссылки
Листы Excel содержат много ячеек, и (по умолчанию) каждая ячейка идентифицируется своим буква столбца за которым следует номер его строки. Это называется привязкой в стиле A1. Примеры: A1, B4, C6.
Эталонный стиль A1
При желании вы можете переключиться на Опорный режим R1C1 для ссылки на строку ячейки & номер столбца. Вместо ссылки на ячейку A1 вы должны ссылаться на R1C1 (строка 1, столбец 1). Ячейка C4 будет отнесена к R4C3.
Ссылочный стиль R1C1
Ссылки в стиле R1C1 крайне редко встречаются в Excel. Если у вас нет веской причины, вам, вероятно, следует придерживаться эталонного режима по умолчанию в стиле A1. Однако, если вы используете VBA, вы, скорее всего, столкнетесь с этим ссылочным стилем.
Переключиться на эталонный стиль R1C1
Чтобы изменить справочный стиль, перейдите к Файл> Параметр> Формула. Установите флажок рядом с Ссылочный стиль R1C1.
Именованные диапазоны
Одна из наиболее редко используемых функций Excel - это именованные диапазоны. Вместо того, чтобы ссылаться на ячейку (или группу ячеек) по ее местоположению (например, B3 или R3C2), вы можете назвать этот диапазон и просто указать имя диапазона в своих формулах.
Чтобы назвать диапазон:
- Выберите ячейку или ячейки, которые вы хотите назвать
- Щелкните внутри поля имени диапазона
- Введите желаемое имя
- Нажмите Enter
Теперь вы можете ссылаться на ячейку A1, набрав вместо нее = range_name1. Это очень полезно при работе с большими книгами с несколькими листами.
Диапазон ячеек
При использовании встроенных функций Excel вам может потребоваться ссылаться на диапазоны ячеек. Диапазоны ячеек имеют вид "A1: B3". Эта ссылка относится ко всем ячейкам между A1 и B3: ячейкам A1, A2, A3, B1, B2, B3.
Чтобы выбрать диапазон ячеек при вводе формулы:
- Введите диапазон (разделите начальный и конечный диапазон точкой с запятой)
- Используйте мышь, чтобы щелкнуть ссылку на первую ячейку, удерживая кнопку мыши, перетащите нужный диапазон.
- Удерживая нажатой клавишу Shift, перемещайтесь с помощью клавиш со стрелками, чтобы выбрать свой диапазон.
Абсолютные (замороженные) и относительные ссылки
При вводе ссылок на ячейки в формулах можно использовать относительные или абсолютные (замороженные) ссылки. Относительные ссылки будут перемещаться пропорционально при копировании формулы в новую ячейку. Абсолютные ссылки останутся без изменений. Давайте посмотрим на несколько примеров:
Относительная ссылка
Относительная ссылка в Excel выглядит так
= A1
Когда вы копируете и вставляете формулу с относительными ссылками, относительные ссылки перемещаются пропорционально. Если вы не укажете иное, ссылки на ячейки будут относительными (незамороженными) по умолчанию.
Пример: если вы скопируете ‘= A1’ на одну строку вниз, ссылка изменится на ‘= A2’.
Абсолютные (замороженные) ссылки на ячейки
Если вы не хотите, чтобы ссылки на ячейки перемещались при копировании формулы, вы можете «заморозить» ссылки на ячейки, добавив знаки доллара ($ s) перед ссылкой, которую вы хотите зафиксировать. Теперь, когда вы копируете и вставляете формулу, ссылка на ячейку остается неизменной. Вы можете закрепить ссылку на строку, ссылку на столбец или и то, и другое.
A1: Ничего не заморожено
$ A1: столбец заморожен, но строка не заморожена
A $ 1: строка заморожена, но столбец не заморожен
$ A $ 1: и строка, и столбец заморожены.
Абсолютная ссылка
Добавление знаков доллара ($ s) в формулы вручную не очень практично. Вместо этого при создании формулы используйте клавишу F4 для переключения между абсолютными / относительными ссылками на ячейки.
Пример абсолютной ссылки на ячейку
Когда вам действительно нужно заморозить ссылку на ячейку? Один из распространенных примеров - это когда у вас есть входные ячейки, на которые часто ссылаются. В приведенном ниже примере мы хотим рассчитать налог с продаж для каждого количества пунктов меню. Налог с продаж является постоянным по всем позициям, поэтому мы будем повторно ссылаться на ячейку налога с продаж.
Чтобы узнать общую сумму налога с продаж, введите формулу «= (B3 * C3) * $ C $ 1» в столбце D и скопируйте формулу вниз.
Смешанная ссылка
Возможно, вы слышали о смешанных ссылках на ячейки. Смешанная ссылка - это когда ссылка на строку или столбец заблокирована (но не одновременно).
Смешанная ссылка
Помните, что с помощью клавиши «F4» вы можете циклически перемещаться по относительным, абсолютным ссылкам на ячейки.
Ссылки на ячейки - вставка и удаление строк / столбцов
Вам может быть интересно, что происходит со ссылками на ячейки, когда вы вставляете или удаляете строки / столбцы?
Ссылка на ячейку автоматически обновится, чтобы ссылаться на исходную ячейку. Это происходит независимо от того, заморожена ли ссылка на ячейку.
3D ссылки
Иногда вам может потребоваться работать с несколькими листами с одинаковыми шаблонами данных. Excel позволяет вам обращаться к нескольким листам одновременно без необходимости вручную вводить каждый лист. Вы можете ссылаться на диапазон листов аналогично тому, как ссылались бы на диапазон ячеек. Пример «Sheet1: Sheet5! A1» будет ссылаться на ячейки A1 на всех листах от Sheet1 до Sheet5.
Давайте рассмотрим пример:
Вы хотите сложить общее количество проданных единиц каждого продукта во всех магазинах. У каждого магазина есть свой рабочий лист, и все рабочие листы имеют одинаковый формат. Вы можете создать формулу, подобную этой:
Это не так уж сложно с четырьмя листами, но что, если бы у вас было 40 листов? Вы действительно хотите вручную добавить ссылку на каждую ячейку?
Вместо этого вы можете легко использовать трехмерную ссылку для одновременной ссылки на несколько листов (аналогично тому, как вы можете ссылаться на диапазон ячеек).
Будь осторожен! Порядок ваших листов имеет значение. Если вы переместите другой лист между ссылочными листами (StoreA и StoreD), этот лист будет включен. И наоборот, если вы переместите лист за пределы диапазона листов (до StoreA или после StoreD), он больше не будет включен.
Ссылка на круговую ячейку
Круговая ссылка на ячейку - это когда ячейка ссылается на себя. Например, если результат ячейки B1 используется в качестве входных данных для ячейки B1, создается круговая ссылка. Ячейке не обязательно ссылаться непосредственно на себя. Могут быть промежуточные шаги.
Пример:
В этом случае формула для ячейки B2 - «A2 + A3 + B2». Поскольку вы находитесь в ячейке B2, вы не можете использовать B2 в уравнении. Это вызовет циклическую ссылку, и значение в ячейке «B2» автоматически будет установлено на «0».
Обычно циклические ссылки являются результатом ошибки пользователя, но в некоторых случаях вы можете использовать циклические ссылки. Основной пример использования круговой ссылки - итеративное вычисление значений. Для этого вам нужно перейти в Файл> Параметры> Формулы а также Включено итеративное вычисление:
Внешние ссылки
Иногда при вычислении данных вам может потребоваться обратиться к данным за пределами вашей книги. Это называется внешней ссылкой (ссылкой).
Чтобы выбрать внешнюю ссылку при создании формулы, перейдите к внешней книге и выберите ссылку, как обычно.
Чтобы перейти к другой книге, вы можете использовать сочетание клавиш CTRL + TAB или перейти к Просмотр> Переключить Windows.
Выбрав ячейку, вы увидите внешнюю ссылку, которая выглядит следующим образом:
Обратите внимание, что имя книги заключено в квадратные скобки [].
После того, как вы закроете книгу, на которую указывает ссылка, в ссылке будет показано расположение файла:
Когда вы повторно откроете книгу, содержащую внешнюю ссылку, вам будет предложено включить автоматическое обновление ссылок. Если вы это сделаете, Excel откроет справочное значение с текущим значением книги. Даже если он закрыт! Будь осторожен! Вы можете этого захотеть, а можете и не захотеть.
Именованные диапазоны и внешние ссылки
Что происходит с вашей внешней ссылкой на ячейку, когда строки / столбцы добавляются или удаляются из справочной книги? Если обе книги открыты, ссылки на ячейки обновляются автоматически. Однако, если обе книги не открыты, ссылки на ячейки не обновятся и станут недействительными. Это серьезная проблема при связывании с внешними книгами. Это вызвано множеством ошибок.
Если вы действительно ссылаетесь на внешние книги, вы должны назвать ссылку на ячейку именованным диапазоном. (см. предыдущий раздел для получения дополнительной информации). Теперь ваша формула будет ссылаться на именованный диапазон независимо от того, какие изменения происходят во внешней книге.
