Скопировал формулу — и всё поехало: 7 причин, почему Excel ломает расчёты. Разбираем, почему формулы в Excel ломаются при копировании: абсолютные и смешанные ссылки, именованные диапазоны, структурированные таблицы и сдвиги диапазонов.
Вы сделали формулу. Проверили — работает. Протянули вниз — и итог стал другим. Excel не “глючит”. Он честно смещает ссылки. Вопрос — понимаете ли вы, как именно.
1. Относительные ссылки: невидимый сдвиг
Формула:
=B2*C2
В строке 3 станет:
=B3*C3
Это нормально. Проблема начинается, когда нужно зафиксировать часть ссылки. Если ставка НДС в ячейке F1, формула:
=B2$F$1*
Без фиксации столбца и строки при копировании вниз ставка “уплывёт”.
2. Абсолютные ссылки: не всегда нужно фиксировать всё
Абсолютная ссылка выглядит так:
=$B$2
Но иногда нужно зафиксировать только столбец или только строку. Пример расчёта по матрице:
=$B2*C$1
Здесь столбец B фиксирован, а строка 2 меняется. Строка 1 фиксирована, а столбец C меняется. Это называется смешанная ссылка. И 80% ошибок — именно здесь.
3. Копирование вправо и вниз — разная логика
Вы написали формулу для столбца. Потом скопировали её вправо. Если в формуле есть относительные столбцы, они сместятся. Пример:
=B2-C2
При копировании вправо станет:
=C2-D2
Иногда это нужно. Иногда — разрушает расчёт.
4. Именованные диапазоны: удобство, которое путает
Вы создали имя “Ставка” для ячейки F1. Формула:
=B2*Ставка
Вроде безопасно. Но если имя создано относительным — оно может вести себя иначе при копировании на другой лист. Проверьте в “Диспетчере имён”, что ссылка абсолютная.
5. Структурированные ссылки в умных таблицах
В таблице формула выглядит так:
=[@Продажи]*(1-[@Скидка])
При переносе за пределы таблицы логика может измениться. При копировании на другой лист — тоже. Если расчёт критичен, иногда лучше вынести его в обычный диапазон.
6. Сдвиг диапазона в суммах
Формула:
=СУММ(B2:B10)
Вы вставили строку сверху. Теперь данные в B3:B11. Формула может остаться прежней — и одна строка выпадет. Проверка:
— щёлкните по формуле
— посмотрите, выделяется ли весь диапазон
7. Протягивание через “дырки”
Если в столбце есть пустые строки, протягивание может остановиться раньше. Excel ориентируется на соседние данные. Иногда лучше копировать вручную или использовать двойной клик по маркеру заполнения.
Как копировать формулы безопасно
Мини-алгоритм:
- Перед копированием определить, что должно быть фиксировано
- Использовать F4 для переключения типов ссылок
- После копирования проверить крайние строки
- Проверить один случай вручную
Если в части 9 мы проверяли отчёт перед отправкой, то здесь мы защищаем формулу ещё до появления ошибки.
В Telegram лежит файл с практикой:
— примеры относительных ссылок
— смешанные ссылки в матрице
— задачи “найди ошибку”
— вариант с правильной фиксацией
В следующей части разберём, как проверить таблицу перед отправкой руководителю — системно и без паники.