Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Суммирование количества продаж недели в зависимости от месяца
 
#1
10.04.2013 14:52:09
Доброго времени суток, коллеги- форумчане!
Прошу Вашей компетентной помощи в весьма интересном вопросе:
Мне необходимо прописать формулу, которая будет суммировать продажи всех дней недели.

Более подробно:
В прилагающемся файле имеется лист "Результаты".
В него заносятся данные по количеству продаж "FTTB" и "IPTV" разнесенные по дням месяца.

Столбцы С:G представляют данные одного дня (в моем случае 1ое апреля), H:L следующий день, и т. д.
В столбец C вносятся данные по количеству ОФОРМЛЕННЫХ заявок "FTTB" (за один день 1ое апреля)
В столбец D вносятся данные по количеству ПОДКЛЮЧЕННЫХ заявок "FTTB" (за один день 1ое апреля).

На листе "KPI" рассчитываются показатели продаж по- недельно.

ЗАДАЧА: необходимо прописать формулу, при помощи которой в столбце "ФАКТ FTTB" (лист KPI, столбец T [отмечено красным] ;)
будут суммироваться значения ПОДКЛЮЧЕННЫХ заявок (из листа Результат, столбцов D, I, N, ..., [отмечены красным] ;) , В ЗАВИСИМОСТИ от дней в неделе.
Тоесть на листе KPI необходимо считать показатели подключенных заявок с разнесение по неделям.

З.Ы.: коллеги, буду рад, если вы мне поможете с такой задачей. Сам проделал уже много работы с этим листом KPI, научил его просчитывать дни начала и конца недели в зависимости от месяца, проставлять номер недели, считать прогноз, переносить "недовыполнные" заявки в план на следующую неделю,
НО вот никак не могу научить его считать факт выполнения продаж недели в зависимости от того, какие даты приходятся на тот или иной месяц (проставлять вручную ячейки суммирования нет особого желания, так как недели в каждом месяце начинается не всегда с понедельника 1ого числа)...

Я за автоматизацию))))
ЗАРАНЕЕ БЛАГОДАРЕН вам, знатоки
Познаю цзен Excel...
 
 
 
#2
10.04.2013 16:20:36
Больше нудная, чем интересная. Если Вы готовы кинуть денежку на телефон, то помогу.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#3
10.04.2013 16:30:49
Хмм... попробую ответить на ваш запрос в корректной форме:
Если вы хотите получать деньги за выполненную работу, то дорога вам на сайт фриланса!
Здесь же люди помогают друг другу на добровольных началах и в виде альтруизма...
Соответственно денег никаких я кидать НЕ СОБИРАЮСЬ.

Я просто прошу квалифицированной помощи в решении, как мне кажется, интересной задачи.
А интересной потому, что
1) есть массив данных
2) из этого массива необходимо "выделить" нужный диапазон значений (дней, которые входят в определенную неделю)
3) должны соблюдаться многие условия (в том числе и направленные на автоматизацию просчетов)
4) мне эта задача интересна в плане развития своих знаний, гостям и посетителям форума в качестве "пищи для ума".

Я НЕ ПРОШУ ДОДЕЛАТЬ мне таблицу, я прошу дать мне понимание того, как это можно реализовать!
Заранее благодарен за ответ ТЕХ, кого интересуют "головоломки"...))))
Познаю цзен Excel...
 
 
 
#4
10.04.2013 23:56:27
Меня головоломки интересуют. Люблю я это дело. Спасибо за благодарность :)

Разбираться в могобуквии да еще в файле, который в архиве весит 88 кБ? Это не головоломка - головомойка. Оно мне надо?
Решение нужно Вам? Так почему бы не создать простой пример, понятный с первого взгляда?
 
 
 
#5
11.04.2013 09:16:34
для факт FTTB
в ячейку U5
формула
=СУММПРОИЗВ((KPI!R$2<=Результаты!$C$1:$FA$1)*(KPI!R$2+7>=Результаты!$C$1:$FA$1)*(KPI!$A5=Результаты!$B$3:$B$20);Результаты!$D$3:$FB$20)
 
 
 
#6
11.04.2013 10:14:04
vikttur, мы не ищем легких путей)))
а файл в практически полном объеме (в оригинале там еще больше информации) я выложил для того, чтобы у вас было представление полной картины. упростить еще больше некуда... ну может только оставить одного человека для примера...
Познаю цзен Excel...
 
 
 
#7
11.04.2013 10:52:59
Max Zh, благодарю за вариант, но, либо он неверный, либо я чего- то не пойму:

1) *(KPI!R$2+7>=Результаты!$C$1:$FA$1) - как я понимаю вы предлагаете к первому дню недели прибавлять еще 7 дней.... НЕ ФАКТ, что в любой неделе месяца (номерной неделе года) будет 7 дней.
например: в 14 неделе 2013 года - 7 дней, а в 18 неделе - 2 дня))

2) ;Результаты!$D$3:$FB$20) - мне необходимо суммировать не всю строку одного дня (C3:G3; H3:L3...), а именно показатель фактически ПОДКЛЮЧЕННЫХ заявок, тоесть значения в ячейках D3;N3;I3... [выделено красным]

а) именно для того, чтобы в зависимости от любой недели любого месяца, любого года, я прописал формулы для автоматического определения количества дней в неделе (ячейки V1; AB1; AH1; AN1; AT1), автоматического определения начальной даты недели (ячейка R2) и конечной даты недели (ячейка U2).
Необходимо расчеты подвязать ИМЕННО к этой информации))

б) по поводу суммирования именно ПОДКЛЮЧЕННЫХ заявок у меня есть такое представление:
можно суммировать каждую 5ую ячейку.
Познаю цзен Excel...
 
 
 
#8
11.04.2013 11:06:22
1) по данному пункту вместо KPI!R$2+7 сделайте ссылку на ячейку с датой окончания недели, закрепите строку
2) суммируется диапазон со смещением на один столбец от даты в исходнике, по моему именно так у Вас организованы данные, ну или я что-то не понимаю

Если скажете где (в какой ячейке)считается не правильно, можно будет понять как скорректировать формулу
 
 
 
#9
11.04.2013 11:12:16
Ниасилил. Для автоматизации расчетов организация данных слабовата  ;)
 
 
 
#10
11.04.2013 11:28:54
Цитата
MaximilianimuS пишет:
Здесь же люди помогают друг другу на добровольных началах и в виде альтруизма...

не всегда.
есть ветка и для оплачиваемых задач
OfficeProPlus 365x64
Win64forWorkstation
 
 
 
#11
11.04.2013 11:51:01
Цитата
Для автоматизации расчетов организация данных слабовата  ;)
Согласен, недоработки в организации некоторые есть,
над этим сейчас и работаю)))
Познаю цзен Excel...
 
 
 
#12
11.04.2013 11:51:41
Цитата
Евгений пишет:
Цитата
MaximilianimuS пишет:
Здесь же люди помогают друг другу на добровольных началах и в виде альтруизма...

не всегда.
есть ветка и для оплачиваемых задач

но данный пост висит же не в "оплачиваемой ветке" ;)
Познаю цзен Excel...
 
 
 
#13
11.04.2013 11:54:17
Цитата
Max Zh пишет:
1) по данному пункту вместо KPI!R$2+7 сделайте ссылку на ячейку с датой окончания недели, закрепите строку
2) суммируется диапазон со смещением на один столбец от даты в исходнике, по моему именно так у Вас организованы данные, ну или я что-то не понимаю

Если скажете где (в какой ячейке)считается не правильно, можно будет понять как скорректировать формулу

господин Max Zh,
БОЛЬШУЩЕЕ вам спасибо!!!!
"тест- драйв" формулы, после поправки конечной даты, проходит успешно)))
Все работает корректно... (в дальнейшем сбоев, надеюсь не будет)

маленькая просьба только:
могли бы вы мне "на пальцах" объяснить принцип работы вашей формулы? ну так, для общего развития))
Познаю цзен Excel...
 
 
 
#14
11.04.2013 12:02:21
=СУММПРОИЗВ((KPI!R$2<=Результаты!$C$1:$FA$1)*(KPI!R$2+7>=Результаты!$C$1:$FA$1)*(KPI!$A5=Результаты!$B$3:$B$20);Результаты!$D$3:$FB$20)
ну здесь вот это все можно воспринимать как условия суммирования
(KPI!R$2<=Результаты!$C$1:$FA$1)*(KPI!R$2+7>=Результаты!$C$1:$FA$1)*(KPI!$A5=Результаты!$B$3:$B$20)
в результате расчета формируется как бы матрица из 1 и 0,

а вот это диапазон суммирования Результаты!$D$3:$FB$20, из него берутся цифры и умножаются на предыдущую матрицу 1, 0, в результате все что не подходит по условиям обнуляется, все что подходит умножается на 1, и дальше суммируется. единственный ньюанс - я уже говорил об этом выше
матрица значений для суммирования смещена относительно матрицы условий на 1 столбец, тк. в том столбце, откуда надо брать цифру нет даты - она расположена левее
 
 
 
#15
11.04.2013 12:11:17
Цитата
Max Zh пишет:
=СУММПРОИЗВ((KPI!R$2<=Результаты!$C$1:$FA$1)*(KPI!R$2+7>=Результаты!$C$1:$FA$1)*(KPI!$A5=Результаты!$B$3:$B$20);Результаты!$D$3:$FB$20)
ну здесь вот это все можно воспринимать как условия суммирования
(KPI!R$2<=Результаты!$C$1:$FA$1)*(KPI!R$2+7>=Результаты!$C$1:$FA$1)*(KPI!$A5=Результаты!$B$3:$B$20)
в результате расчета формируется как бы матрица из 1 и 0,

а вот это диапазон суммирования Результаты!$D$3:$FB$20, из него берутся цифры и умножаются на предыдущую матрицу 1, 0, в результате все что не подходит по условиям обнуляется, все что подходит умножается на 1, и дальше суммируется. единственный ньюанс - я уже говорил об этом выше
матрица значений для суммирования смещена относительно матрицы условий на 1 столбец, тк. в том столбце, откуда надо брать цифру нет даты - она расположена левее

принцип понял, спасибо!
только пару вопросов:
1) единственный ньюанс - на что он может повлиять?
2) выяснилась вот какая подробность: у меня в файле есть скрытые строки (например 10, 23, 24...) в них результат вычислений по формуле совершенно некорректный! с чем это может быть связано и как исправить?

СПАСИБО заранее
Познаю цзен Excel...
 
 
 
#16
11.04.2013 12:12:59
MaximilianimuS, попробуйте обойтись без цитирования - ну вот для чего оно в данном случае?
 
 
 
#17
11.04.2013 12:33:16
1)
его надо учитывать, когда будете применять формулу для расчета других показателей, т.е. данное смещение производить согласно структуры Вашего исходника - на сколько колонок показатель смещен от даты
2)
посмотрел - в скрытых строках пусто, поэтому суммируются все пустые строки из диапазона, когда проставите туда фамилии, все будет считаться, если надо чтоб не считалось, добавьте в так сказать "условия" следующее *(Результаты!$B$3:$B$20<>"")
 
 
 
#18
11.04.2013 12:49:10
правда, Вам большое спасибо!
вы научили меня кое- чему)) все исправно работает!
если будут вопросы, к вам можно обращаться?
Познаю цзен Excel...
 
 
 
#19
11.04.2013 12:55:09
почему нет? по возможности помогу чем смогу
 
 
 
Страницы: 1
Читают тему
Loading...