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

Вход

Регистрация

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

 

= Мир MS Excel/Расчет использования ранее проданных сертификатов - Мир MS Excel

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

Excel 2013
Друзья, добрый день.

Не уверен, что заголовок понятно описывает задачу, если у уважаемых модераторов есть более общепринятое названия, буду признателен за исправление.

Есть следующая бизнес-ситуация. Компания каждый месяц продает некоторые сертификаты. Стоит задача спрогнозировать, когда и в каком объеме эти сертификаты будут "востребованы" (обналичены, поменяны на товары и услуги). Имеется прогноз, что 50% проданных сертификатов будут использованы в тот же месяц, еще 30% на следующий месяц, еще 10% в третьем месяце, 5% в четвертом, а оставшиеся 5% не будут востребованы никогда. Схема примерна, может быть и другая.

Как можно описать кол-во использованных (востребованных) сертификатов за каждый месяц, ссылаясь на плановый график продаж и схему использования сертификатов по месяцам.

Пример в приложении, строка "кол-во использований" посчитана вручную в лоб, а надо ее как-то автоматизировать, т.к. кол-во месяцев большое, процент использований, по идее, может быть задан на период больше 4-х месяцев, да и точек со своими продажами может быть много.

Буду невероятно признателен, если сможете подсказать (и показать формулу), как это сделать красиво. Ранее форум не раз подкидывал идеи.

Заранее благодарю!
К сообщению приложен файл: 9024547.xls (35.5 Kb)
 
Ответить
СообщениеДрузья, добрый день.

Не уверен, что заголовок понятно описывает задачу, если у уважаемых модераторов есть более общепринятое названия, буду признателен за исправление.

Есть следующая бизнес-ситуация. Компания каждый месяц продает некоторые сертификаты. Стоит задача спрогнозировать, когда и в каком объеме эти сертификаты будут "востребованы" (обналичены, поменяны на товары и услуги). Имеется прогноз, что 50% проданных сертификатов будут использованы в тот же месяц, еще 30% на следующий месяц, еще 10% в третьем месяце, 5% в четвертом, а оставшиеся 5% не будут востребованы никогда. Схема примерна, может быть и другая.

Как можно описать кол-во использованных (востребованных) сертификатов за каждый месяц, ссылаясь на плановый график продаж и схему использования сертификатов по месяцам.

Пример в приложении, строка "кол-во использований" посчитана вручную в лоб, а надо ее как-то автоматизировать, т.к. кол-во месяцев большое, процент использований, по идее, может быть задан на период больше 4-х месяцев, да и точек со своими продажами может быть много.

Буду невероятно признателен, если сможете подсказать (и показать формулу), как это сделать красиво. Ранее форум не раз подкидывал идеи.

Заранее благодарю!

Автор - el_gorro
Дата добавления - 22.02.2016 в 16:08
Udik Дата: Понедельник, 22.02.2016, 17:31 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Так можно, если в обратном прядке проценты записать
Код
=СУММПРОИЗВ($F$5:$I$5;H2:K2)
К сообщению приложен файл: 0t.xls (38.0 Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеТак можно, если в обратном прядке проценты записать
Код
=СУММПРОИЗВ($F$5:$I$5;H2:K2)

Автор - Udik
Дата добавления - 22.02.2016 в 17:31
el_gorro Дата: Понедельник, 22.02.2016, 18:21 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Так можно, если в обратном прядке проценты записать


Супер! Большое спасибо, очень красивое и простое решение, не додумался..(
Один дополнительный вопрос, можно ли как-то обойти необходимость иметь много пустых строк слева? Например, если продажи идут с первого месяца, а график процентов имеет не 4, а 8 или 12 периодов.
К сообщению приложен файл: 0t_2.xls (38.5 Kb)
 
Ответить
Сообщение
Так можно, если в обратном прядке проценты записать


Супер! Большое спасибо, очень красивое и простое решение, не додумался..(
Один дополнительный вопрос, можно ли как-то обойти необходимость иметь много пустых строк слева? Например, если продажи идут с первого месяца, а график процентов имеет не 4, а 8 или 12 периодов.

Автор - el_gorro
Дата добавления - 22.02.2016 в 18:21
Udik Дата: Понедельник, 22.02.2016, 18:25 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
тогда надо формулу менять, чтоб ошибки не выскакивали из-за ссылок не туда :)
==
проще столбцы скрыть


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Понедельник, 22.02.2016, 19:13
 
Ответить
Сообщениетогда надо формулу менять, чтоб ошибки не выскакивали из-за ссылок не туда :)
==
проще столбцы скрыть

Автор - Udik
Дата добавления - 22.02.2016 в 18:25
el_gorro Дата: Вторник, 23.02.2016, 03:30 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
проще столбцы скрыть


Я в итоге так и сделал, самое простое и изящное решение из всего предложенного друзьями и на двух форумах. Еще раз огромное Вам спасибо! hands
 
Ответить
Сообщение
проще столбцы скрыть


Я в итоге так и сделал, самое простое и изящное решение из всего предложенного друзьями и на двух форумах. Еще раз огромное Вам спасибо! hands

Автор - el_gorro
Дата добавления - 23.02.2016 в 03:30
Pelena Дата: Вторник, 23.02.2016, 08:13 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19165
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
простое и изящное решение

А у меня не простое и тем более не изящное, но не требующее пустых столбцов слева и перестановки процентов
Код
=СУММ(ИНДЕКС($B$2:B2;МАКС(B1-СЧЁТ(3:3);0)+1):B2*НАИМЕНЬШИЙ(3:3;ТРАНСП(СТРОКА(ДВССЫЛ(МАКС(СЧЁТ(3:3)-B1;0)+1&":"&СЧЁТ(3:3))))))
К сообщению приложен файл: 3160821.xls (46.5 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
простое и изящное решение

А у меня не простое и тем более не изящное, но не требующее пустых столбцов слева и перестановки процентов
Код
=СУММ(ИНДЕКС($B$2:B2;МАКС(B1-СЧЁТ(3:3);0)+1):B2*НАИМЕНЬШИЙ(3:3;ТРАНСП(СТРОКА(ДВССЫЛ(МАКС(СЧЁТ(3:3)-B1;0)+1&":"&СЧЁТ(3:3))))))

Автор - Pelena
Дата добавления - 23.02.2016 в 08:13
el_gorro Дата: Вторник, 23.02.2016, 15:28 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Pelena,
Вау, пока даже не могу понять как оно работает, но ведь работает же! Спасибо большое!
 
Ответить
СообщениеPelena,
Вау, пока даже не могу понять как оно работает, но ведь работает же! Спасибо большое!

Автор - el_gorro
Дата добавления - 23.02.2016 в 15:28
vikttur Дата: Вторник, 23.02.2016, 17:08 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Кросс на Планете
http://www.planetaexcel.ru/forum....fikatov
 
Ответить
СообщениеКросс на Планете
http://www.planetaexcel.ru/forum....fikatov

Автор - vikttur
Дата добавления - 23.02.2016 в 17:08
_Boroda_ Дата: Среда, 24.02.2016, 11:01 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А почему решили, что проценты обязательно будут по убывающей? А если не 50-30-10-..., а 50-10-30-...?
Предлагаю такую формулу (немассивная, неволатильная и без изменений в исходном файле)
Код
=СУММПРОИЗВ(ИНДЕКС(3:3;Ч(ИНДЕКС(СТОЛБЕЦ()-СТОЛБЕЦ(B2:ИНДЕКС(2:2;СТОЛБЕЦ()-СЧЁТ($A3:B3)+1))+2;)))*(B2:ИНДЕКС(2:2;СТОЛБЕЦ()-СЧЁТ($A3:B3)+1)))
К сообщению приложен файл: 3160821_1.xls (50.0 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА почему решили, что проценты обязательно будут по убывающей? А если не 50-30-10-..., а 50-10-30-...?
Предлагаю такую формулу (немассивная, неволатильная и без изменений в исходном файле)
Код
=СУММПРОИЗВ(ИНДЕКС(3:3;Ч(ИНДЕКС(СТОЛБЕЦ()-СТОЛБЕЦ(B2:ИНДЕКС(2:2;СТОЛБЕЦ()-СЧЁТ($A3:B3)+1))+2;)))*(B2:ИНДЕКС(2:2;СТОЛБЕЦ()-СЧЁТ($A3:B3)+1)))

Автор - _Boroda_
Дата добавления - 24.02.2016 в 11:01
el_gorro Дата: Суббота, 26.03.2016, 16:54 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, Спасибо Вам большое!

На тот момент я сделал по простой формуле перемножения диапазонов, но сейчас понадобилось в модель добавить еще один расчет по схожей схеме, где схема длиннее, задается не 5-9 периодами, а сразу 36 (на три года). Пустых столбцов в этом случае не напасешься, поэтому обратился к Вашей формуле. Но поскольку несколько пустых столбцов уже предусмотрено, не понимаю, как заставить работать Вашу формулу в Моем файле. Полагаю, это задается введенными вручную значениями +1 и +2 (в вашей формуле), и возможно, смещением диапазона с закреплением левой границы. Но поскольку понять формулу до конца я так и не смог, не выходит каменный цветок =(

В файле Ваша формула, которая перестает работать при добавлении пустых столбцов, а также новый пример, где есть задача Вашу формулу применить.

Буду очень признателен, если сможете подсказать, как заставить это работать.
К сообщению приложен файл: 3160821_1_upd.xls (71.0 Kb)


Сообщение отредактировал el_gorro - Суббота, 26.03.2016, 17:04
 
Ответить
Сообщение_Boroda_, Спасибо Вам большое!

На тот момент я сделал по простой формуле перемножения диапазонов, но сейчас понадобилось в модель добавить еще один расчет по схожей схеме, где схема длиннее, задается не 5-9 периодами, а сразу 36 (на три года). Пустых столбцов в этом случае не напасешься, поэтому обратился к Вашей формуле. Но поскольку несколько пустых столбцов уже предусмотрено, не понимаю, как заставить работать Вашу формулу в Моем файле. Полагаю, это задается введенными вручную значениями +1 и +2 (в вашей формуле), и возможно, смещением диапазона с закреплением левой границы. Но поскольку понять формулу до конца я так и не смог, не выходит каменный цветок =(

В файле Ваша формула, которая перестает работать при добавлении пустых столбцов, а также новый пример, где есть задача Вашу формулу применить.

Буду очень признателен, если сможете подсказать, как заставить это работать.

Автор - el_gorro
Дата добавления - 26.03.2016 в 16:54
_Boroda_ Дата: Воскресенье, 27.03.2016, 16:16 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Формулу можно переписать вот так
Код
=СУММПРОИЗВ(ИНДЕКС(3:3;СТОЛБЕЦ($J6)-1+Ч(ИНДЕКС(СТОЛБЕЦ()-СТОЛБЕЦ(K2:ИНДЕКС(2:2;СТОЛБЕЦ()-СЧЁТ($A3:K3)+1))+2;)))*(K2:ИНДЕКС(2:2;СТОЛБЕЦ()-СЧЁТ($A3:K3)+1)))

для строки 18 вроде так
Код
=СУММПРОИЗВ(ИНДЕКС(17:17;СТОЛБЕЦ($J6)-1+Ч(ИНДЕКС(СТОЛБЕЦ()-СТОЛБЕЦ(K16:ИНДЕКС(16:16;СТОЛБЕЦ()-СЧЁТ($A17:K17)+1))+2;)))*(K16:ИНДЕКС(16:16;СТОЛБЕЦ()-СЧЁТ($A17:K17)+1)))
К сообщению приложен файл: 3160821_1_upd_1.xls (70.0 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеФормулу можно переписать вот так
Код
=СУММПРОИЗВ(ИНДЕКС(3:3;СТОЛБЕЦ($J6)-1+Ч(ИНДЕКС(СТОЛБЕЦ()-СТОЛБЕЦ(K2:ИНДЕКС(2:2;СТОЛБЕЦ()-СЧЁТ($A3:K3)+1))+2;)))*(K2:ИНДЕКС(2:2;СТОЛБЕЦ()-СЧЁТ($A3:K3)+1)))

для строки 18 вроде так
Код
=СУММПРОИЗВ(ИНДЕКС(17:17;СТОЛБЕЦ($J6)-1+Ч(ИНДЕКС(СТОЛБЕЦ()-СТОЛБЕЦ(K16:ИНДЕКС(16:16;СТОЛБЕЦ()-СЧЁТ($A17:K17)+1))+2;)))*(K16:ИНДЕКС(16:16;СТОЛБЕЦ()-СЧЁТ($A17:K17)+1)))

Автор - _Boroda_
Дата добавления - 27.03.2016 в 16:16
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет использования ранее проданных сертификатов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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