ПРАКТИЧЕСКОЕ РУКОВОДСТВО · ЛОГИКА В ЯЧЕЙКАХ
Формулы для таблиц: выбор функции, ссылки и ошибки
Узнайте, какая функция подходит к вашей задаче, что происходит при копировании ссылок и как найти причину неверного результата. Каждый приём разобран на конкретных ячейках.
Попробовать
ПОДГОТОВИТЬСЯ
Как выбрать формулу для суммы, условия или поиска
Выбор формулы начинается с вопроса к данным. Если нужен общий остаток игр, подходит сумма. Если важно число позиций ниже минимального запаса, нужна проверка условия и подсчёт. Поиск названия по коду — отдельная задача, где важны ключ и режим совпадения.
На одной и той же таблице разные функции дают разные полезные ответы. SUM складывает числовые значения, COUNTIF считает подходящие ячейки, IF выбирает результат по правилу, VLOOKUP ищет значение в справочнике. На учебных примерах удобно проследить связь выражения и результата. Не подставляйте функцию только потому, что её название знакомо: сначала определите, что именно должно получиться.
Данные к иллюстрации
| Задача | Функция | Результат |
|---|---|---|
| Общий остаток | SUM | Количество игр |
| Позиции ниже порога | COUNTIF | Число позиций |
| Нужен ли заказ | IF | Ответ по условию |
| Название по коду | VLOOKUP | Значение справочника |
ОСНОВА ТЕМЫ
Принципы, на которые стоит опираться
Ссылка связывает формулу с данными
Формула начинается со знака равенства. Вместо повторного ввода цены используйте ссылку на её ячейку: при изменении источника результат обновится. В примере склада количество и цена определяют стоимость остатка, а минимум запаса — необходимость заказа.
Сумма, количество и среднее отвечают на разные вопросы
SUM складывает значения, COUNT считает числовые ячейки, COUNTA — непустые. AVERAGE находит среднее числовых значений. Если номер заказа записан числом, его сумма редко имеет смысл: сначала определите, что означает столбец.
Условие возвращает результат по правилу
IF выбирает между двумя результатами. Например, если остаток ниже минимального запаса, появляется рекомендация проверить заказ. Условие не заменяет деловую логику: нулевой остаток снятого с продажи товара не обязательно означает необходимость закупки.
Закрепляйте ссылку только там, где это нужно
При копировании относительная ссылка смещается. Знак доллара фиксирует столбец, строку или оба. Для общей ставки удобно закрепить ячейку настройки; для количества текущей строки ссылку обычно оставляют относительной. Проверьте первую и последнюю скопированную формулу.
Ошибку сначала объясните, потом скрывайте
Неверный разделитель, текст вместо числа и пустой источник требуют разных исправлений. Не оборачивайте все выражения обработчиком ошибок ради пустого экрана. Примеры используют английские имена функций; названия и разделители в вашем редакторе могут зависеть от локали.
РАЗОБРАТЬСЯ
Как меняются ссылки при копировании формулы
При копировании формулы относительная ссылка перемещается вместе с ячейкой, а закреплённая остаётся на месте. Пусть ставка 6 записана в F2, а суммы находятся в A2 и A3. В выражении =A2*$F$2/100 значение суммы меняется по строкам, но ставка продолжает браться из F2.
Если скопировать формулу на строку ниже, получится =A3*$F$2/100. При копировании на столбец вправо относительная A2 станет B2. Знаки доллара фиксируют столбец F и строку 2 одновременно. Проверяйте несколько первых результатов вручную: случайно закреплённая сумма или незакреплённая ставка могут дать правдоподобные, но неверные числа.
Данные к иллюстрации
| Положение | Формула | Что изменилось |
|---|---|---|
| Исходная | A2 × $F$2 / 100 | Сумма из A2 |
| На строку ниже | A3 × $F$2 / 100 | Строка суммы |
| На столбец вправо | B2 × $F$2 / 100 | Столбец суммы |
ПРИМЕР ДЛЯ САМОСТОЯТЕЛЬНОГО РАЗБОРА
Склад игр: значение и формула связаны
Проследите связь исходных значений и результата. Данные вымышленные и служат для объяснения подхода.
| Товар | Остаток | Минимум | Вывод |
|---|---|---|---|
| Шахматы | 4 | 6 | Проверить заказ |
| Лото | 12 | 5 | Достаточно |
| Домино | 0 | 4 | Проверить заказ |
ПРОВЕРИТЬ РЕЗУЛЬТАТ
Как найти ошибку в формуле, прежде чем скрывать её
Ошибка формулы сообщает о проблеме, которую сначала нужно понять. Деление на ноль может означать отсутствие наблюдений; число текстом — неверный импорт; отсутствующий код — неполный справочник или опечатку. Замена любого такого результата нулём способна скрыть причину.
Проверяйте исходные ячейки и ожидаемый смысл ответа. При отсутствии продаж средний чек не равен автоматически нулю: возможно, его нельзя вычислить. Если код не найден, покажите понятное сообщение и проверьте ключ. Обработка ошибок нужна после определения правильного поведения, а не вместо него. Проверяйте выражение на простом наборе с заранее известным ответом.
Данные к иллюстрации
| Симптом | Возможная причина | Проверка |
|---|---|---|
| Деление на ноль | Нет наблюдений | Проверить знаменатель |
| Число не суммируется | Значение хранится текстом | Проверить тип |
| Код не найден | Нет совпадения | Сверить справочник |
ВОПРОСЫ И ОТВЕТЫ
Что ещё важно знать
01Чем число отличается от числа, записанного текстом?
Текст может выглядеть как число, но не участвовать в вычислении ожидаемым образом. Проверьте пробелы, разделитель дробной части и тип ячейки.
02Когда ставить знак доллара?
Когда ссылка на общий параметр должна оставаться на месте при копировании. Например, $F$2 закрепляет и столбец F, и строку 2.
03Почему формула использует точку с запятой?
Разделители зависят от локали редактора. В примерах указан вариант записи; при переносе проверьте язык функций и настройки файла.
04Как обработать пустую ячейку?
Сначала решите, означает ли пустота отсутствие данных или ноль. Подмена неизвестного значения нулём может изменить итог и среднее.
05Почему формула не переносится между редакторами без изменений?
Различаются имена функций, разделители, версии и поддержка отдельных операций. Сравните синтаксис со справкой выбранного редактора.
ПРИМЕНИТЕ ПОДХОД