Первую большую таблицу с зарплатами я считал в Excel лет пятнадцать назад, ещё в банке. Сделал формулу для одной строки, скопировал вниз на сорок человек — и половина премий превратилась в нули. Я сидел и не понимал: формула же правильная, вот она. Через полчаса дошло: я забыл про один значок — доллар. Из-за него ссылка на процент премии уезжала вниз, в пустые клетки. Тот вечер научил меня главному про Excel: важно не только что ты пишешь в формуле, но и как поведёт себя ссылка, когда формулу растащат по всей таблице.
После урока сможешь:
— по формуле =A1*B1 сказать, какой она станет после копирования в соседнюю ячейку;
— отличить относительную ссылку от абсолютной и объяснить, зачем нужен доллар;
— увидеть, почему без доллара результаты превращаются в нули, и починить формулу;
— прочитать коды ошибок #ДЕЛ/0!, #ССЫЛКА!, #ИМЯ?, #ЗНАЧ! и понять, что не так.
Вспомни: в теме 2.1 мы разобрали, что такое формула и как её ввести. Дальше без этого никак — вся адресация держится на том, что формула ссылается на ячейки, а не на переписанные числа.
Формула не считает числа, а ссылается на ячейки
Вместо =5*10 пишут =A1*B1: «возьми, что лежит в A1, умножь на то, что лежит в B1». Поменял число в A1 — результат пересчитался сам, руками ничего трогать не нужно. Именно поэтому таблицу с сотней строк считают одной формулой, а не сотней.
Относительная ссылка едет вместе с формулой
Обычная ссылка вида A1 называется относительной. Она запоминает не сам адрес, а направление: «возьми число на две клетки левее в той же строке». Поэтому при копировании формулы ссылка съезжает вслед за ней.
ОТНОСИТЕЛЬНАЯ ССЫЛКА - едет вместе с формулой
A B C
+--------+--------+------------------+
1 | 5 | 10 | =A1*B1 -> 50 | формулу написали здесь
+--------+--------+------------------+
2 | 7 | 3 | =A2*B2 -> 21 | скопировали на строку вниз
+--------+--------+------------------+
Копируем C1 в C2. Ссылка A1 стала A2, ссылка B1 стала B2.
Формула опустилась на строку вниз, и вместе с ней
опустились адреса. Это и есть относительная ссылка.
Абсолютная ссылка: доллар прибивает адрес
Иногда ссылка должна стоять на месте, сколько бы раз формулу ни копировали. Классический случай: столбец цен умножаем на курс валюты, а сам курс лежит в одной-единственной ячейке. Для этого ссылку делают абсолютной — ставят знак доллара: $B$1.
АБСОЛЮТНАЯ ССЫЛКА - доллары держат адрес на месте
A B C
+--------+--------+---------------------+
1 | 100 | 90 | =A1*$B$1 -> 9000 | $B$1 - курс валюты
+--------+--------+---------------------+
2 | 200 | | =A2*$B$1 -> 18000 | скопировали вниз
+--------+--------+---------------------+
Ссылка A1 -> A2 меняется: цена в каждой строке своя.
Ссылка $B$1 не двигается: доллары прибили и столбец B, и строку 1.
Между этими двумя крайностями есть смешанная ссылка: доллар прибивает что-то одно. В записи A$1 закреплена только строка, в $A1 — только столбец. Переключает виды клавиша F4: A1 → $A$1 → A$1 → $A1 и обратно к A1.
Поломка без доллара, автозаполнение и коды ошибок
Забыл доллар у курса или процента — при копировании ссылка уедет на пустые клетки, и в ответах будут нули. Лечится одним движением: курс нужно прибить долларами — $B$1.
КОДЫ ОШИБОК В ЯЧЕЙКЕ
КОД ЧТО ОЗНАЧАЕТ КАК ЧИНИТЬ
--------------------------------------------------------------------
#ДЕЛ/0! формула делит на ноль или на убрать деление на ноль,
пустую ячейку проверить делитель
--------------------------------------------------------------------
#ССЫЛКА! формула ссылается на ячейку, вернуть данные или
которую удалили переписать ссылку
--------------------------------------------------------------------
#ИМЯ? опечатка в имени функции исправить название
(например, СУМ вместо СУММ) функции
--------------------------------------------------------------------
#ЗНАЧ! в арифметике участвует текст убрать текст, оставить
вместо числа в расчёте числа
Проверь себя
Контрольный вопрос. Ты хочешь, чтобы Excel сам пересчитывал результат, когда меняются исходные числа. С чего должна начинаться запись в ячейке, чтобы Excel понял: это формула, а не текст?
Подсказка: Пока в начале нет особого знака, Excel считает содержимое обычной надписью и не вычисляет её.
Онлайн-проверка ответа появится позже
Контрольный вопрос. В ячейке C1 стоит формула =A1*B1. Её копируют на одну строку вниз, в C2. Что произойдёт со ссылками A1 и B1?
Подсказка: Относительная ссылка помнит направление, а не точный адрес.
Онлайн-проверка ответа появится позже
Контрольный вопрос. Какой станет формула в ячейке C2 (была =A1*B1 в C1, скопирована в C2)?
Подсказка: Ссылки относительные и едут вниз вместе с формулой на одну строку.
Онлайн-проверка ответа появится позже
Контрольный вопрос. Формула =A1+B1 в C1 скопирована в E4. Какой она станет?
Подсказка: Формула уехала с первой строки на четвёртую: номера строк в ссылках вырастают на столько же.
Онлайн-проверка ответа появится позже
Контрольный вопрос. Что делает знак доллара в ссылке $A$1?
Подсказка: Доллар здесь не про деньги.
Онлайн-проверка ответа появится позже
Контрольный вопрос. В смешанной ссылке A$1 доллар стоит только перед номером строки. Что это значит при копировании формулы?
Подсказка: Доллар прибивает то, что стоит сразу после него.
Онлайн-проверка ответа появится позже
Задание. Какая клавиша в Excel переключает вид ссылки по кругу: A1 → $A$1 → A$1 → $A1? Впиши обозначение клавиши — буква и цифра, без пробела.
Подсказка: Это одна из функциональных клавиш верхнего ряда клавиатуры, между третьей и пятой функциональными.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
Контрольный вопрос. В ячейке D1 записана формула =C1*$B$1 (курс валюты в B1). Формулу скопировали в D2. Какой она станет?
Подсказка: Курс лежит в одной ячейке — её адрес закреплён долларами и не должен сдвинуться.
Онлайн-проверка ответа появится позже
Контрольный вопрос. Цены в столбце A (A2:A4), курс в B1. В C2 написали =A2*B1 (обе относительные) и протянули вниз до C4 — в C3/C4 нули (курс съехал на пустые B2/B3). Какой должна стать формула в C2, чтобы курс не съезжал?
Подсказка: Цена в каждой строке своя — её ссылку не закрепляй. Курс один на всех — только его адрес нужно закрепить долларами.
Онлайн-проверка ответа появится позже
Контрольный вопрос. В ячейке C2 записана смешанная формула =B$1*A2. Её скопировали в ячейку D3. Какой она станет?
Подсказка: Копирование идёт на столбец вправо и на строку вниз. Доллар держит номер строки на месте, а буква столбца всё равно сдвигается.
Онлайн-проверка ответа появится позже
Контрольный вопрос. Что произойдёт, если выделить ячейку со словом «Январь», схватить маркер заполнения в правом нижнем углу и протянуть на пять ячеек вниз?
Подсказка: Автозаполнение узнаёт привычные ряды — месяцы, дни недели, числа по порядку — и продолжает их само.
Онлайн-проверка ответа появится позже
Контрольный вопрос. Отметь все верные пары «код ошибки → причина» (не менее двух вариантов).
Выберите все верные варианты.
Подсказка: Читай код как подсказку. «ДЕЛ» — про деление, «ИМЯ» — про название функции, «ЗНАЧ» — про значение не того типа.
Онлайн-проверка ответа появится позже
Итог
Итог. Формула ссылается на ячейки, а не на переписанные числа: поменял исходное — результат пересчитался сам. Относительная ссылка (A1) при копировании съезжает вместе с формулой. Абсолютная ($A$1) стоит на месте. Смешанная (A$1, $A1) держит что-то одно, переключает виды клавиша F4. Забыл доллар у курса — при копировании ссылка уедет на пустые клетки, и в ответах будут нули. Коды ошибок читаются как подсказки: #ДЕЛ/0! — деление на ноль, #ИМЯ? — опечатка в имени функции, #ЗНАЧ! — текст в расчёте, #ССЫЛКА! — удалённая ячейка.
← Назад к главе 2 · ↑ В начало урока · ⌂ В начало курса · Вперёд: Функции →
