Как сделать формулу в эксель: скидка съехала в доставку
Как сделать формулу в эксель для заказа со скидкой: умножение, постоянная ссылка и доставка одним платежом. Разбор ошибки копирования и запрос для своей таблицы.
Д
Денис
Продакт LibraChat
8 мин чтения
В заказе на 1740 рублей у меня появился минус 128880. Поставщик ничего не дарил. Я просто протянул формулу вниз, и она вместо скидки взяла стоимость доставки. Если тебе нужно разобраться, как сделать формулу в эксель для своей сметы, этот провал полезнее десятка сложных функций: на одной покупке видно, что должна считать ячейка и что происходит при копировании.
Мне нужно было отдать организаторам одну понятную сумму. Для этого я разделил стоимость каждой покупки, общую скидку и разовую доставку. Перемножить количество и цену оказалось недостаточно: после первой удачной строки пришлось проверить следующую и решить, какие адреса должны оставаться на месте. Так в смете появилась постоянная ссылка `$H$2`.
Как сделать формулу в эксель для одного заказа
Я собрал список материалов для мастер-класса по коллажу. Бумага, ножи и клей уже лежали в списке; оставалось понять, сколько денег внести организаторам. Продавец дал скидку на товары, а доставка оплачивалась отдельно. Записать всё в калькулятор можно. Но список менялся: то добавляли клей, то убирали лишний нож.
Мне нужна была смета, которую не придётся пересчитывать с нуля после каждой такой реплики. Поэтому количество и цену я разнёс по разным столбцам. Название положил в A, количество в B, цену одной штуки в C. Стоимость до скидки заняла D. Первой строке достались заголовки, записи начались со второй.
Для начала введи эти значения в обычный лист Excel. Они останутся перед глазами, пока будем разбирать выражения.
Строка · A: покупка · B: количество, шт. · C: цена, руб. · D: до скидки, руб.
Числа в D здесь показывают ожидаемый результат. В своей книге поставь вместо них формулы. Для D2 нужно выражение `=B2*C2`: звёздочка означает умножение. Сначала знак равенства. Без него Excel не получает команду вычислить выражение.
Я бы вводил первую формулу так:
Щёлкни D2, где должна появиться стоимость бумаги.
Напечатай `=`, выбери B2, добавь `*`, затем выбери C2.
Нажми Enter и проверь, что получилось 840.
Вернись к D2 и протяни маркер заполнения до D4.
При копировании вниз адреса меняются. В D3 будет `=B3*C3`, в D4 получится `=B4*C4`. Для отдельных покупок это правильно: шесть ножей нужно умножать на цену ножа, а не листа бумаги. Такой адрес называют относительной ссылкой.
Я проверил три произведения отдельно: 24 × 35 = 840, 6 × 120 = 720, 10 × 18 = 180. Складываем и получаем 1740 рублей до скидки. Пока всё сходится. Вот здесь я и поспешил решить, что дальнейшее копирование тоже не принесёт сюрпризов.
Соседняя ячейка не знает, что такое скидка
В H2 я записал 10%, а в H3 оставил стоимость доставки: 180. Столбец E отвёл под товары со скидкой. В E2 ввёл `=D2*(1-H2)`. Получилось 756 рублей. Верно: от 840 отняли десятую часть.
Я скопировал выражение ниже. И получил ту самую отрицательную сумму за ножи.
При переносе Excel изменил обе ссылки. Формула в E3 стала `=D3*(1-H3)`. Но в H3 находился уже не процент. Там стояло 180. Поэтому программа честно вычислила 720 × (1 − 180), то есть −128880. Ошибка не в умножении. Я не указал, какой адрес должен оставаться постоянным.
Мне помогло прочитать выражение вслух: «Стоимость этой строки умножить на остаток после скидки из H2». Первое должно меняться от покупки к покупке. Второе относится ко всему заказу. Значит, у этих ссылок разные обязанности.
Исправленное выражение для E2: `=D2*(1-$H$2)`. Доллары перед буквой и номером закрепляют оба компонента адреса. Копируешь в E3, получаешь `=D3*(1-$H$2)`. Стоимость переходит к ножам, ставка остаётся в H2.
Я раньше воспринимал эти доллары как странную пунктуацию. Теперь смотрю на них как на ответ на вопрос: что именно разрешено сдвигать? Полезно различать четыре записи, даже если в твоей первой смете понадобятся только две.
`H2`: строка и столбец могут измениться при копировании.
`$H$2`: оба компонента закреплены.
`H$2`: строка 2 сохраняется, столбец может смениться.
`$H2`: сохраняется столбец H, номер строки может измениться.
Не закрепляй всё подряд. Если поставить доллары ещё и в `$D$2`, каждая следующая позиция начнёт брать стоимость бумаги. Суммы перестанут выглядеть страшно, но от этого не станут подходящими для ножей и клея.
С процентом тоже не спеши. В H2 вводится именно `10%`. Число `10` без процентного формата означает десять целых, а не десятую часть. В выражении `1-10` получится отрицательный множитель. Если сомневаешься, посмотри на саму запись ставки, прежде чем исправлять остальную формулу.
После переноса правильного выражения в E2:E4 я получил 756, 648 и 162 рубля. Их сумма равна 1566. Разница с исходными 1740 составляет 174 рубля, то есть ровно десятую часть стоимости товаров. Здесь уже можно выдохнуть. Но платёж ещё не готов.
Доставка относится к посылке, а не к каждой покупке
Следующая ловушка оказалась спокойнее. Я чуть не добавил `+$H$3` к каждой формуле столбца E. По отдельности суммы смотрелись прилично: товар со скидкой плюс доставка. Только посылка одна. А строк три.
Так мы заплатили бы за перевозку 540 рублей вместо 180. Итог стал бы 2106 рублей. Ни отрицательных значений, ни сообщения об ошибке. Таблица может выглядеть совершенно нормальной, пока ты не назовёшь, за что именно платишь.
Доставку надо прибавить после сложения товарных позиций. В E5 можно написать `=E2+E3+E4+$H$3`. Получится 1746 рублей: 1566 за товары после скидки и 180 за перевозку. Строке E5 дай ясную подпись, например «К оплате с доставкой», чтобы её не спутали с товарным итогом.
На трёх позициях сложение отдельных адресов удобно читать. Для длинного заказа разумнее использовать функцию суммы по диапазону, но сначала разберись с границей самого расчёта. Доставка должна попасть в оплату один раз независимо от того, три у тебя позиции или тридцать.
Я сделал две короткие проверки. Сначала временно поставил скидку 0%. Товары вернулись к 1740 рублям, а платёж с доставкой вырос до 1920. Затем вернул скидку и временно обнулил доставку. К оплате осталось 1566 рублей. Значит, эти составляющие действительно отделены друг от друга.
После проверки восстанови H2 и H3. Не оставляй испытательные значения в файле, который отправляешь организатору. Я для такой сметы держу рядом короткую запись исходных условий: скидка касается всех трёх товаров, перевозка отдельная. Если продавец изменит условия, менять придётся смысл расчёта, а не только число процента.
В чужом прайсе скидка может действовать лишь на часть корзины. Тогда одна общая ставка для всех строк не подходит. Уточни условия до того, как протянешь формулу: например, выдели для каждой позиции собственный процент. Удобство копирования не повод приписывать скидку товару, на который её не давали.
Что я прошу объяснить, прежде чем доверять расчёту
Когда не понимаю выражение, мне нужен разбор по адресам. Просьба «сделай правильную формулу» слишком мало говорит о листе. В ней отсутствует даже место, где лежит скидка. Поэтому в запросе я описываю раскладку и называю условие доставки.
В LibraChat можно разбирать задачи и формулы. Вставь в чат раскладку столбцов B–D и значения двух служебных ячеек. Так в запрос попадут именно связи, которые потерялись у меня при протягивании. Имена участников мастер-класса для этого не нужны.
В Excel B2:B4 содержит количество товаров, C2:C4 цену за штуку, D2:D4 стоимость до скидки. В H2 стоит скидка 10%, в H3 доставка 180 рублей за весь заказ. Нужны E2:E4 со стоимостью товаров после скидки и E5 с платежом за весь заказ. Объясни каждую ссылку, укажи постоянные адреса и покажи, как меняется формула при копировании из второй строки в третью. Количества 24, 6, 10; цены 35, 120, 18. Отдельно проверь, что доставка прибавляется один раз. Не добавляй налогов и других условий.
Прежде чем переносить выражение из ответа в книгу, я разбираю его по трём вопросам: нужная ли покупка взята из строки, там ли находится процент, сколько раз добавлена перевозка. Если адреса отличаются от твоего листа, сначала исправь раскладку запроса. Иначе можно аккуратно скопировать чужую правильную формулу в своё неправильное место.
Если кроме объяснения планируешь работу с файлами, доступные условия перечислены в тарифах LibraChat. Возможности ответа не отменяют проверки итоговой книги человеком.
Подробнее о подготовке табличной задачи есть в разборе таблиц GPT: к нему удобно обратиться, если пока не можешь описать расположение данных в своём листе.
В смете для мастер-класса я в итоге оставил две разные подписи: «Товары после скидки» и «К оплате с доставкой». Теперь мне не приходится вспоминать, что означает последнее число. А если снова увижу невероятно щедрую скидку, первым делом загляну в адрес после минуса.
