Адресация ячеек в электронных таблицах
Когда вы пишете формулу, каждая ссылка на ячейку — это адрес. От типа адресации зависит, что произойдёт с формулой при копировании. Это одна из самых важных тем: без неё невозможно эффективно работать с таблицами.
Зачем это нужно? Вы написали формулу для одной строки и хотите скопировать её на 100 строк вниз. Если адресация настроена правильно — всё заработает автоматически. Если нет — придётся править каждую ячейку вручную.
Три типа адресации
| Тип |
Запись |
При копировании |
Когда нужен |
| Относительный |
A1 |
Адрес сдвигается вместе с формулой |
Однотипные расчёты |
| Абсолютный |
$A$1 |
Адрес не меняется |
Фиксированная константа |
| Смешанный |
$A1 / A$1 |
Фиксирована только часть |
Таблицы умножения, прайсы |
Знак $ означает «заморозить». Он ставится перед тем, что нужно зафиксировать: перед буквой столбца, перед номером строки или перед обоими.
Относительная адресация
По умолчанию все ссылки — относительные. Таблица запоминает не конкретный адрес, а смещение: «ячейка на 1 столбец левее и в той же строке».
Пример: копируем формулу вниз
В C2 стоит =A2+B2. Что будет, если скопировать в C3, C4, C5?
| |
A |
B |
C |
| 1 |
Цена |
Кол-во |
Итого |
| 2 |
100 |
5 |
=A2+B2 |
| 3 |
200 |
3 |
? |
| 4 |
150 |
8 |
? |
| 5 |
300 |
2 |
? |
Формула сдвинется вниз
Ссылки A2 и B2 автоматически станут A3+B3 и так далее.
Почему это удобно? Одна формула — и она работает для всех строк. Написали одну, скопировали вниз, готово.
Абсолютная адресация
Иногда нужно, чтобы адрес не менялся при копировании. Например, ставка НДС лежит в одной ячейке, и все формулы должны ссылаться именно на неё.
$B$1
$ — «заморозить» столбец B строка 1
Пример: умножаем на ставку НДС
НДС = 20% в ячейке B1. Формула в C2: =A2*$B$1. Копируем вниз:
| |
A |
B |
C |
| 1 |
Сумма |
20% |
НДС |
| 2 |
1000 |
|
=A2*$B$1 |
| 3 |
2500 |
|
? |
| 4 |
800 |
|
? |
| 5 |
3200 |
|
? |
$B$1 останется на месте
A2 сдвинется (относительная), а $B$1 — нет (абсолютная).
Частая ошибка: забыть $ перед фиксированной ячейкой. После копирования формула будет ссылаться на пустую ячейку — результат 0 или ошибка.
Смешанная адресация
Можно заморозить только столбец или только строку.
Фиксирован столбец
$A1
Столбец A заморожен, строка свободна. Вниз: $A1 → $A2 → $A3. Вправо: $A1 → $A1 → $A1.
Фиксирована строка
A$1
Строка 1 заморожена, столбец свободен. Вправо: A$1 → B$1 → C$1. Вниз: A$1 → A$1 → A$1.
Классический пример: таблица умножения
Формула в B2: =$A2*B$1 — столбец A заморожен ($A), строка 1 заморожена ($1). Наведите на ячейку, чтобы увидеть формулу:
Формула: =$A2*B$1 — одна формула на всю таблицу.
Клавиша F4 — переключение типа
Не нужно вручную вводить $. Встаньте курсором на ссылку в формуле и нажимайте F4 — тип будет переключаться по кругу:
1
A1 — относительный (по умолчанию)
2
$A$1 — абсолютный (всё зафиксировано)
3
A$1 — смешанный (строка зафиксирована)
4
$A1 — смешанный (столбец зафиксирован)
Попробуйте — нажмите кнопку или клавишу F4:
B3
Относительный — всё сдвигается
B3 $B$3 B$3 $B3
F4
Кликните или нажмите F4 на клавиатуре
Песочница: копирование формулы
Выберите тип адресации и посмотрите, как ссылка из ячейки B2 (на C3) изменится при копировании в каждую ячейку таблицы:
Ссылка из B2: C3 $C$3 C$3 $C3
Синяя — исходная формула (B2). Фиолетовые — результат копирования. Красные $ — замороженные части.
Шпаргалка
1
$ перед буквой фиксирует столбец: при копировании вправо столбец не сдвигается.
2
$ перед числом фиксирует строку: при копировании вниз строка не сдвигается.
3
$ перед обоими — полная фиксация. Адрес не меняется ни при каком копировании.
4
F4 переключает тип: A1 → $A$1 → A$1 → $A1 → A1
5
Если формула после копирования даёт 0 или #ССЫЛ! — проверьте, не забыли ли $.
Итог: относительные ссылки сдвигаются при копировании, абсолютные — нет, смешанные — частично. Клавиша F4 мгновенно переключает тип. Освоив это, вы сможете строить любые формулы за секунды.