Абсолютные и относительные ссылки в Excel: что блокирует знак доллара

1. Вилка: эта часть должна двигаться или оставаться?

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

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

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

Ответ второй: должно остаться. Ссылки указывают на одну вещь, которая является общей для каждой строки. Ставка комиссии, процент налога, обменный курс, верхний левый угол справочной таблицы. Если это правда, вы хотите, чтобы каждая копия формулы запрашивала одну и ту же ячейку, и вы записываете это как $G$1. Это абсолютный ссылка.

Между ними решается не формула и не ячейка. Это предложение, которое вы бы сказали вслух по поводу этой ссылки. «Сумма в этой строке» перемещается. «Ставка» остается.

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

2. Что на самом деле хранит относительная ссылка

Именно этот факт делает остальную часть страницы очевидной, а не запоминаемой. Тип =D2 в ячейку Е2. Excel не запоминает «D2». Он помнит «одну клетку слева от меня».

Скопируйте эту формулу в E5, и она по-прежнему будет означать одну ячейку слева от меня, которая теперь является D5. Формулу не знал, там написано D2. Он всегда знал только расстояние.

Скажите это вслух, потому что от этого предложения зависит вся тема: ссылка — это направление и расстояние, а не адрес.

Именно поэтому заполнение столбца вообще работает. Вы пишете одну формулу для строки 2 и копируете ее в строку 17, и каждая копия спокойно указывает на свою строку, потому что «одна ячейка слева от меня» верна для каждой строки.

Знак доллара отключает это поведение для половины адреса, перед которым он находится. $G означает столбец G, всегда, независимо от того, где заканчивается эта формула. $1 всегда означает строку 1. Поместите оба, и ссылка перестанет быть расстоянием и станет местом.

3. Ставка в одной ячейке и 513,60 она стоит

Вот и провал, работало впритык на реальных цифрах.

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

Теперь комиссия за заказ идет в столбце E. В E2 вы пишете =D2*G1 и заливаем до Е17.

Первый ряд правый. E2 читается как 96,00 и 1200 × 0,08 = 96,00. Таким образом, формула получает одобрение, а заливке доверяют.

Вот что сделали следующие три строки.

Итоги по столбцу 1,840.00. Оно должно составить 1,326.40, потому что сумма шестнадцати сумм составляет 16 580 и 16 580 × 0,08 = 1 326,40. Лист закончился на 513,60, и 1840,00 минус 1326,40 = 513,60.

Прежде чем читать дальше, скажите, почему вторая строка оказалась верной. Изложить это своими словами стоит больше, чем само исправление.

Строка 2 верна, потому что строка 2 — это строка, для которой вы написали формулу. Расстояние от E2 до G1 на единицу вверх, на две вправо. В строке 3 то же расстояние находится на G2, что соответствует ставке налога. В строке 4 указан коэффициент подъема. Начиная с строки 5, она оказывается в пустом пространстве, а пустая ячейка рассматривается в арифметике как ноль.

Два из этих неправильных чисел, 190,00 и 1554,00, выглядят как деньги. Это та часть, которую стоит запомнить. Эта ошибка не дает о себе знать, и рецензент обычно проверяет только одну строку, которая является правильной.

Исправление — одно нажатие клавиши. В E2 нажмите на G1 и нажмите F4, что превратит его в $G$1, затем заполните снова.

=D2*$G$1

Теперь каждая строка запрашивает G1, общая сумма столбца составляет 1326,40, а изменение скорости в G1 обновляет сразу все шестнадцать строк. Эта вторая часть — настоящая награда. Ставка указана в ячейке, которую кто-то может ввести, что делает лист моделью, а не распечаткой.

4. F4 и четыре состояния по порядку

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

На Мак тот же переключатель — Cmd+T, и F4 также работает, если для этого настроены ваши функциональные клавиши.

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

5. Смешанный: зафиксируйте одну половину, освободите другую.

Прежде чем объяснение, предсказание. Если $G$1 скрепляет обе половинки и G1 булавок нет, скажи, что думаешь G$1 булавки.

Ряд. G$1 свободно перемещается вбок и застрял в ряду 1. Это зеркало, $G1, застрял в столбце G и может свободно перемещаться вверх и вниз.

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

Эмпирическое правило короткое. Закрепите ту часть, которая не должна меняться в направлении копирования.

• Ваши якоря сидят в Строка вверху и копируете вниз: зафиксируйте строку, G$1.
• Ваши якоря сидят в Столбец вниз сбоку и копируете поперек: зафиксируйте столбец, $D2.
• Оба сразу — это сетка в следующем разделе, и для нее нужно по одному каждому.

6. Девять ответов из одной формулы

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

Поместите три ставки вверху в J1, K1 и L1: 0,06, 0,08 и 0,10. Суммы уже находятся в столбце D. Впишите это в J2, затем заполните до L2 и до строки 4.

=$D2*J$1

Прочтите это как два решения, а не как знаки препинания. $D2 привязан к столбцу D и свободен в строке, поскольку сумма всегда находится в D, но меняется построчно. J$1 привязан к строке 1 и свободен в столбце, поскольку ставка всегда находится в строке 1, но меняется столбец за столбцом.

Вот что содержат девять ячеек с арифметикой.

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

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

7. Четыре вещи, которых не делает знак доллара

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

Это не меняет ответ там, где вы его набрали. =D2*G1 и =D2*$G$1 вернуть точно такое же число в E2. Разница появляется только тогда, когда формула копируется куда-то еще. Вот почему тестирование одной ячейки ничего не доказывает о заполнении.

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

Это не останавливает изменение ссылки при физическом перемещении цели. Это удивляет людей, которые думают, что абсолютное означает постоянное. Вставьте строку выше строки 1, и каждый $G$1 в рабочей книге незаметно перезаписывается на $G$2, поскольку Excel следует указанному вами значению. Абсолют означает «не смещается при копировании формулы», а не «никогда не меняется».

Он не выдерживает разреза. Вырезание ячейки привязки с помощью Ctrl+X и вставка ее в другое место перетаскивает каждую ссылку на нее, знаки доллара и все такое. Копирование этого не дает. Если модель ломается в тот день, когда кто-то приводил в порядок макет, обычно именно поэтому.

8. Когда вам вообще не нужен знак доллара

Есть два способа получить одинаковую защиту без каких-либо знаков препинания, и оба впоследствии читаются лучше.

Назовите ячейку. Выберите G1, введите CommissionRate в поле имени рядом со строкой формул и нажмите Входить. Теперь формула =D2*CommissionRate, что уже является абсолютным и говорит, что это значит для любого, кто откроет файл в следующем году. Определенные имена не изменяются при копировании формулы.

Поместите данные в таблицу. Внутри Таблица Excel, созданная с помощью Ctrl+Т, записывается ссылка на столбец [@Amount] и это означает «сумма в этой строке» по имени, а не по расстоянию. Нечего блокировать и нечего возвращать назад, и ссылка продолжает работать при вставке столбцов. Это одна из веских причин нажать Ctrl+Т в диапазоне, для которого вы собираетесь писать формулы.

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