Формула разности в Excel на примере печатных заготовок: порядок вычитания, нулевой и отрицательный остаток, копирование строк и общий резерв с закреплённым адресом.
Денис
Продакт LibraChat
8 мин чтения
Ты считаешь пачку заготовок перед печатью, записываешь выдачу и хочешь понять, что осталось. В таблице для одной партии получается положительное число, для другой ноль, для третьей минус. Пока не ясно, что из чего вычитали, эти ответы легко прочитать неправильно. Разберём, как сделать разность в Экселе: формула будет сопровождать один учебный день работы с заготовками.
Я использую условные количества, чтобы каждую строку можно было проверить без программы. Утром зададим смысл остатка, днём разберём подозрительный минус, перед завершением работы отделим фактический запас от резерва. Во всех этих ситуациях достаточно обычного оператора `-`.
Утро: как сделать разность в Экселе и выбрать формулу
Сначала я подписываю исходные столбцы. «Было» и «Стало» выглядят коротко, но плохо объясняют, к какому моменту относятся числа. Для нашего дня беру более точные названия: утренний запас, выдано за день, расчётный остаток. Все количества заготовок считаются в штуках; форматы не пересчитываем друг в друга.
Разместим данные в A1:D4. В первой строке заголовки, ниже три партии.
Последний столбец должен содержать формулы, а не ответы, набранные вручную. Для первой партии запись в D2 такая:
```excel
=B2-C2
```
Читается она буквально: запас из B2 минус выдача из C2. Для исходных 120 штук и выдачи 45 результат равен 75. Начальный знак `=` сообщает Excel, что введено выражение для вычисления. Между адресами стоит обычный минус с клавиатуры, а не длинное тире из текста.
Для разовой проверки можно набрать `=120-45`, но в рабочей таблице удобнее ссылки на ячейки. Тогда изменение числа выданных заготовок в C2 не потребует переписывать саму формулу. Я оставляю исходные значения видимыми рядом, чтобы можно было проверить смысл ответа.
Если поменять адреса местами и написать `=C2-B2`, получится −75. Это верная разность, но другой вопрос: насколько выдача превышает утренний запас. Для привычного остатка у нас принят порядок «запас минус выдача». Название столбца и формула должны описывать одну и ту же операцию.
Перед первой выдачей я проверяю три вещи:
исходные количества записаны числами, без слова «шт.» внутри ячеек;
в одной строке находятся запас и расход одной партии;
вычитаем выдачу из запаса, а не наоборот.
Если значение ещё неизвестно, не подставляй ноль только ради заполненной клетки. Ноль расхода означает, что выдачи не было. Отсутствие записи означает, что её ещё предстоит выяснить. Наш учебный расчёт начинается с заполненных числовых данных.
Днём: читаем ноль и ищем причину отрицательного остатка
У партии A5 выдано столько же, сколько было утром: 90 − 90 = 0. В рамках этого расчёта запас исчерпан. Нулевой ответ не является ошибкой формулы и не означает, что строку нужно убрать. Наоборот, она показывает партию, по которой больше нечего выдавать без нового поступления.
У A4 ответ −8: 60 − 68. Физически нельзя достать из пачки минус восемь заготовок. Формула обнаружила несогласованность данных с принятой моделью «один утренний запас и только выдача». Причина может быть в числе выдачи, утреннем пересчёте или неучтённом поступлении. Один знак не выбирает причину за нас.
Я сначала проверил арифметику: 60 меньше 68 на восемь, поэтому знак отрицательный. Формула правильно выполнила вычитание, но не объяснила происхождение исходных чисел. Затем в учебном сценарии сверяю запись выдачи с подтверждёнными данными. Допустим, выяснилось, что правильное количество в C4 — 52, а 68 внесено ошибочно. После исправления исходной ячейки расчётный остаток станет 8.
Не заменяй −8 на 8 в ячейке D4 вручную. Так исчезнет формула, а неверная выдача останется в C4. При следующем изменении таблица перестанет пересчитываться как задумано. Исправлять нужно подтверждённый источник ошибки, сохраняя возможность восстановить, какое исходное значение поменялось.
Если же выдача 68 верна, случай другой. Возможно, в течение дня пришла новая пачка, которой нет в нашей простой таблице. Тогда нужно уточнить модель учёта и отдельно добавить поступление, а не прятать его в отрицательной выдаче. Для текущего примера дальше принимаем именно подтверждённое исправление C4 на 52.
Я сделал это разделение до любого оформления цветом. Красная заливка может привлечь внимание к минусу, но не объясняет, к каким двум исходным числам он относится. Сначала причина и запись, потом оформление.
Перед закрытием: копируем строки и выделяем общий резерв
Формулу из D2 можно скопировать в D3:D4. Относительные ссылки должны измениться по строкам: в D3 получится `=B3-C3`, в D4 — `=B4-C4`. Номера строк остаются парными. Если одна ссылка случайно указывает на соседнюю партию, арифметика может дать правдоподобный, но чужой остаток.
Я проверяю после копирования не только результат первой строки. Смотрю последнюю: у A4 теперь B4 = 60 и C4 = 52, значит, D4 должно равняться 8. Затем выбираю среднюю строку с нулём. Эти два случая лучше обнаруживают неверную ссылку, чем повторный взгляд на знакомые 75.
Теперь отдельная задача: из текущего остатка нужно оставить резерв по 10 заготовок каждого формата. Это условие примера, одинаковое для трёх партий. В H1 запишем «Резерв каждого формата, шт.», в H2 число 10. В E1 назовём новый показатель «Сверх резерва, шт.».
Для E2 используется:
```excel
=D2-$H$2
```
Здесь D2 должно меняться при копировании вниз, а H2 оставаться общим адресом. Доллары закрепляют и столбец H, и строку 2. В E3 ожидается `=D3-$H$2`, в E4 — `=D4-$H$2`. Если оставить H2 относительной ссылкой, следующая строка начнёт смотреть уже в H3.
После дневного исправления таблица для планирования выглядит так:
Партия · Текущий остаток D, шт. · Общий резерв, шт. · Сверх резерва E, шт.
A6 — 75 — 10 — 65
A5 — 0 — 10 — −10
A4 — 8 — 10 — −2
Здесь отрицательные значения уже имеют другой смысл. У A4 физический расчётный остаток положителен: 8 штук. До установленного резерва не хватает двух, поэтому показатель «Сверх резерва» равен −2. Это не тот же сигнал, что утренние −8 в колонке остатка.
Я подписываю оба столбца полностью. Если назвать D и E одинаково «Остаток», читатель увидит рядом 8 и −2 и решит, что таблица противоречит сама себе. На самом деле меняется вычитаемое и, вместе с ним, вопрос к данным.
Вечером: сверяем формулы с тем, что хотим передать
До отправки сводки я открываю несколько ячеек с формулами и читаю адреса. От первой партии к третьей меняются строки запаса и выдачи; резерв везде берётся из H2. Видимое число само по себе не показывает, что в ячейке сохранилась формула. После ручной правки ответа она могла исчезнуть.
Мой порядок вечерней сверки:
Пересчитать одну разность вручную по исходным числам этой же строки.
Проверить пару ссылок у нулевого и отрицательного результата.
Убедиться, что дневное исправление внесено в исходный расход, а не поверх ответа.
Сверить общий резерв и его закреплённый адрес в каждой скопированной формуле.
Если вместо числа появилась #ЗНАЧ!, посмотри, какие значения находятся в исходных ячейках. Текст, лишние пробелы или скрытые символы могут мешать обычному вычитанию. Не меняй все данные листа наугад: начни с двух ячеек, на которые ссылается проблемная формула.
Если выдача хранится несколькими отдельными записями, её сначала нужно согласованно собрать по партии. Для этой отдельной операции пригодится материал про формулу суммы в Excel. После этого разность снова считается из двух понятных величин: запаса и общей выдачи.
Открой чат LibraChat, если нужно проверить смысл своих колонок:
В учебной таблице B — утренний запас, C — выдача, D = B−C. После сверки у одной партии остаток 8 штук. Общий резерв H2 равен 10, в E считаю =D2-$H$2. Объясни различие отрицательного остатка и отрицательной величины сверх резерва. Сначала попроси уточнить, какие поступления входят в данные.
Частоту таких проверок можно соотнести с условиями тарифов LibraChat.
Проверь себя строже: сможешь объяснить соседние 8 и −2 человеку, который не видел формул? Если для этого приходится говорить, что «программа просто так посчитала», вернись к названиям столбцов и назови каждое вычитаемое словами.
