Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Расчет плана продаж - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет плана продаж (Формулы/Formulas)
Расчет плана продаж
reznikovavaleria99 Дата: Среда, 27.03.2024, 13:42 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Ребята, помогите пожалуйста, написать формулу!
Считаю план продаж. Есть общее количество заказов в месяц (180 шт). Нужно формулой распределить это количество по всем дням. Распределение должно быть нарастающим.
К сообщению приложен файл: 5863390.xlsx (16.1 Kb)
 
Ответить
СообщениеРебята, помогите пожалуйста, написать формулу!
Считаю план продаж. Есть общее количество заказов в месяц (180 шт). Нужно формулой распределить это количество по всем дням. Распределение должно быть нарастающим.

Автор - reznikovavaleria99
Дата добавления - 27.03.2024 в 13:42
_Boroda_ Дата: Среда, 27.03.2024, 14:12 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Код
=B$38*A7/ДЕНЬ(КОНМЕСЯЦА("1-"&B$5;0))
К сообщению приложен файл: 5863390_1.xlsx (16.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Код
=B$38*A7/ДЕНЬ(КОНМЕСЯЦА("1-"&B$5;0))

Автор - _Boroda_
Дата добавления - 27.03.2024 в 14:12
Gustav Дата: Среда, 27.03.2024, 14:17 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2740
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Уточнимся:
* Каждый день нужно продавать кол-во, не меньшее, чем в предыдущий день ?
* Кол-во в день - всегда целое число ?
* В первый день месяца кол-во = 1 или есть какое-то заданное минимальное кол-во больше 1 ?


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеУточнимся:
* Каждый день нужно продавать кол-во, не меньшее, чем в предыдущий день ?
* Кол-во в день - всегда целое число ?
* В первый день месяца кол-во = 1 или есть какое-то заданное минимальное кол-во больше 1 ?

Автор - Gustav
Дата добавления - 27.03.2024 в 14:17
Gustav Дата: Среда, 27.03.2024, 16:57 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2740
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Пожалуй, продолжу потихоньку. Мне было удобно "развернуться" не в Excel, но в таблицах Гугл, чтобы наглядно пощупать задачу, в том виде, как я ее понял. Вот какая формула у меня ПОКА получилась ("ПОКА" - потому что изыскания, скорее всего, продолжу):
[vba]
Код
=LET(
колвоВсего; 180;
днейВмесяц; 30;
колвоМин1д; 1;
дниМесяца;  SEQUENCE(днейВмесяц);
вДеньДробн; MAP(дниМесяца; LAMBDA(x; колвоМин1д + 2 * (колвоВсего/днейВмесяц - колвоМин1д) / (днейВмесяц-1) * (x-1)));
вДеньОкругл; MAP(вДеньДробн; LAMBDA(x; ROUND(x)));
{{"День месяца" \ "Кол-во в день (точное дробное)" \ "Кол-во в день (приблиз.округл.)"};
{дниМесяца \ вДеньДробн \ вДеньОкругл}}
)
[/vba]
Формулу нужно ввести в ячейку A1 таблицы Гугл, после чего она сгенерирует в трёх столбцах все интересующие данные. Думаю, исходные данные (3 параметра) просматриваются в начале формулы более, чем наглядно. В Excel последних версий формулу придется слегка подправить (в основном перевести названия функций на русский). В Excel древних версий (2013 и старее) формулу придётся разобрать на части и разместить получившиеся простые формулы в нескольких соседних столбцах.

Почему выше упомянуто "ПОКА"? Потому что в общем случае сумма по второму столбцу "Кол-во в день (точное дробное)" в точности будет равна распределяемому "колвоВсего" (180 штук), а сумма по третьему столбцу "Кол-во в день (приблиз.округл.)" может отличаться от 180 на 1-2 единицы (и иногда даже больше). И вот эту разницу (положительную или отрицательную) нужно как-то АВТОМАТИЧЕСКИ распределить внутри месяца (придумать стратегию), не нарушая принципа возрастания (или, наверное, точнее - "НЕ убывания") продаж день ото дня.

Сейчас в формуле приведены такие исходные параметры (180, 30, 1), при которых сумма третьего столбца будет в точности равна общей сумме 180 (что есть хорошо и в этом случае ничего делать не надо). Такой же точной она будет и при максимальной длине месяца (180, 31, 1). А вот для месяца февраля сумма третьего столбца будет равна 179 - хоть при (180, 28, 1), хоть при високосном варианте (180, 29, 1).

Вот эту разность "180-179" и хочется научиться как-то пристраивать в третий столбец автоматически, скорректировав на 1 кол-во для одного или нескольких дней. Как я уже говорил, разность общей суммы за месяц может быть и больше единицы, например, для набора параметров (180, 31, 2).


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 27.03.2024, 17:05
 
Ответить
СообщениеПожалуй, продолжу потихоньку. Мне было удобно "развернуться" не в Excel, но в таблицах Гугл, чтобы наглядно пощупать задачу, в том виде, как я ее понял. Вот какая формула у меня ПОКА получилась ("ПОКА" - потому что изыскания, скорее всего, продолжу):
[vba]
Код
=LET(
колвоВсего; 180;
днейВмесяц; 30;
колвоМин1д; 1;
дниМесяца;  SEQUENCE(днейВмесяц);
вДеньДробн; MAP(дниМесяца; LAMBDA(x; колвоМин1д + 2 * (колвоВсего/днейВмесяц - колвоМин1д) / (днейВмесяц-1) * (x-1)));
вДеньОкругл; MAP(вДеньДробн; LAMBDA(x; ROUND(x)));
{{"День месяца" \ "Кол-во в день (точное дробное)" \ "Кол-во в день (приблиз.округл.)"};
{дниМесяца \ вДеньДробн \ вДеньОкругл}}
)
[/vba]
Формулу нужно ввести в ячейку A1 таблицы Гугл, после чего она сгенерирует в трёх столбцах все интересующие данные. Думаю, исходные данные (3 параметра) просматриваются в начале формулы более, чем наглядно. В Excel последних версий формулу придется слегка подправить (в основном перевести названия функций на русский). В Excel древних версий (2013 и старее) формулу придётся разобрать на части и разместить получившиеся простые формулы в нескольких соседних столбцах.

Почему выше упомянуто "ПОКА"? Потому что в общем случае сумма по второму столбцу "Кол-во в день (точное дробное)" в точности будет равна распределяемому "колвоВсего" (180 штук), а сумма по третьему столбцу "Кол-во в день (приблиз.округл.)" может отличаться от 180 на 1-2 единицы (и иногда даже больше). И вот эту разницу (положительную или отрицательную) нужно как-то АВТОМАТИЧЕСКИ распределить внутри месяца (придумать стратегию), не нарушая принципа возрастания (или, наверное, точнее - "НЕ убывания") продаж день ото дня.

Сейчас в формуле приведены такие исходные параметры (180, 30, 1), при которых сумма третьего столбца будет в точности равна общей сумме 180 (что есть хорошо и в этом случае ничего делать не надо). Такой же точной она будет и при максимальной длине месяца (180, 31, 1). А вот для месяца февраля сумма третьего столбца будет равна 179 - хоть при (180, 28, 1), хоть при високосном варианте (180, 29, 1).

Вот эту разность "180-179" и хочется научиться как-то пристраивать в третий столбец автоматически, скорректировав на 1 кол-во для одного или нескольких дней. Как я уже говорил, разность общей суммы за месяц может быть и больше единицы, например, для набора параметров (180, 31, 2).

Автор - Gustav
Дата добавления - 27.03.2024 в 16:57
Gustav Дата: Среда, 27.03.2024, 20:50 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2740
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Что ж, вроде, выстроилась формула, распределяющая по дням месяца заданное количество заказов без остатка, связанного с погрешностью округления, которая устраивает, по крайней мере, меня, т.е. находится в согласии с моими первоначальными замыслами и ожиданиями:
[vba]
Код
=LET(
колвоВсего; 180;
днейВмесяц; 30;
колвоМин1д; 1;
дниМесяца;  SEQUENCE(днейВмесяц);
вДеньДробн; MAP(дниМесяца; LAMBDA(x; колвоМин1д + 2 * (колвоВсего/днейВмесяц - колвоМин1д) / (днейВмесяц-1) * (x-1)));
вДеньОкругл; MAP(вДеньДробн; LAMBDA(x; ROUND(x)));

вДеньРазн;  MAP(вДеньДробн; вДеньОкругл; LAMBDA(x; y; x-y));
разнСумм;   SUM(вДеньДробн)-SUM(вДеньОкругл);
фильтИсорт; SORT(FILTER({дниМесяца \ вДеньРазн}; ISBETWEEN(вДеньОкругл; MIN(вДеньОкругл); MAX(вДеньОкругл);0;0));2;1-SIGN(разнСумм));
дниЗамен;   ARRAY_CONSTRAIN(фильтИсорт;ABS(разнСумм);1);
добавка;    MAP(дниМесяца; LAMBDA(x; IF(ISNA(XMATCH(x; дниЗамен));0;SIGN(разнСумм))));

{{"День месяца" \ "Кол-во в день"};{дниМесяца \ ArrayFormula(вДеньОкругл + добавка)}}
)
[/vba]
Вкратце происходит следующее. Вычисляются суммы столбцов - точного и округленного. Разность между ними - кол-во несоответствия, которое нужно "как-то" распределить по дням. Для реализации этого "как-то" рассчитывается разность столбцов [точное] - [округл] по отдельным дням и по значению этой разности выполняется сортировка дней месяца. Если общее несоответствие - отрицательное, то разности сортируются по возрастанию, с учетом знака, и наоборот - если положительное, то по убыванию. После сортировки берутся номера дней месяца из нескольких первых строк отсортированного виртуального массива. Кол-во этих первых строк, заслуживающих внимания, равно кол-ву несоответствия. Для всех дней месяца, которые попали в эти несколько первых строк, выполняется коррекция округленного дневного кол-ва на единицу: если несоответствие - положительное, то "единички" добавляются, если отрицательное, то вычитаются.

ВАЖНО! Формула распределяет заказы без остатка в пределах некоторых разумных ограничений. Так при кол-ве дней в месяце от 28 до 31 и минимальном числе заказов в первый день от 1 до 5 формула успешно справляется. Если задать минимальное кол-во = 7, то последовательность хоть и посчитается, но будет она не возрастающей, а убывающей. Т.е. общий смысл этого замечания - не пытаться перепилить стальной лом японской бензопилой для лесорубов (как в известном анекдоте).


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЧто ж, вроде, выстроилась формула, распределяющая по дням месяца заданное количество заказов без остатка, связанного с погрешностью округления, которая устраивает, по крайней мере, меня, т.е. находится в согласии с моими первоначальными замыслами и ожиданиями:
[vba]
Код
=LET(
колвоВсего; 180;
днейВмесяц; 30;
колвоМин1д; 1;
дниМесяца;  SEQUENCE(днейВмесяц);
вДеньДробн; MAP(дниМесяца; LAMBDA(x; колвоМин1д + 2 * (колвоВсего/днейВмесяц - колвоМин1д) / (днейВмесяц-1) * (x-1)));
вДеньОкругл; MAP(вДеньДробн; LAMBDA(x; ROUND(x)));

вДеньРазн;  MAP(вДеньДробн; вДеньОкругл; LAMBDA(x; y; x-y));
разнСумм;   SUM(вДеньДробн)-SUM(вДеньОкругл);
фильтИсорт; SORT(FILTER({дниМесяца \ вДеньРазн}; ISBETWEEN(вДеньОкругл; MIN(вДеньОкругл); MAX(вДеньОкругл);0;0));2;1-SIGN(разнСумм));
дниЗамен;   ARRAY_CONSTRAIN(фильтИсорт;ABS(разнСумм);1);
добавка;    MAP(дниМесяца; LAMBDA(x; IF(ISNA(XMATCH(x; дниЗамен));0;SIGN(разнСумм))));

{{"День месяца" \ "Кол-во в день"};{дниМесяца \ ArrayFormula(вДеньОкругл + добавка)}}
)
[/vba]
Вкратце происходит следующее. Вычисляются суммы столбцов - точного и округленного. Разность между ними - кол-во несоответствия, которое нужно "как-то" распределить по дням. Для реализации этого "как-то" рассчитывается разность столбцов [точное] - [округл] по отдельным дням и по значению этой разности выполняется сортировка дней месяца. Если общее несоответствие - отрицательное, то разности сортируются по возрастанию, с учетом знака, и наоборот - если положительное, то по убыванию. После сортировки берутся номера дней месяца из нескольких первых строк отсортированного виртуального массива. Кол-во этих первых строк, заслуживающих внимания, равно кол-ву несоответствия. Для всех дней месяца, которые попали в эти несколько первых строк, выполняется коррекция округленного дневного кол-ва на единицу: если несоответствие - положительное, то "единички" добавляются, если отрицательное, то вычитаются.

ВАЖНО! Формула распределяет заказы без остатка в пределах некоторых разумных ограничений. Так при кол-ве дней в месяце от 28 до 31 и минимальном числе заказов в первый день от 1 до 5 формула успешно справляется. Если задать минимальное кол-во = 7, то последовательность хоть и посчитается, но будет она не возрастающей, а убывающей. Т.е. общий смысл этого замечания - не пытаться перепилить стальной лом японской бензопилой для лесорубов (как в известном анекдоте).

Автор - Gustav
Дата добавления - 27.03.2024 в 20:50
reznikovavaleria99 Дата: Среда, 27.03.2024, 21:47 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Gustav, это гениально! Хоть и сложно для понимания мне, как пользователю с базовым уровнем google и excel .

Попробовала, работает! hands
 
Ответить
СообщениеGustav, это гениально! Хоть и сложно для понимания мне, как пользователю с базовым уровнем google и excel .

Попробовала, работает! hands

Автор - reznikovavaleria99
Дата добавления - 27.03.2024 в 21:47
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет плана продаж (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!