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