Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Нарастающий итог при интересных условиях
 
#1
01.11.2008 06:58:15
Прошу помочь в подсчёте нарастающего итога количества клиентов, проявивших активность.  
Чую, решение должно быть очень простым...  
 
Условия:  
1. Нарастающим итогом суммируется количество клиентов, проявивших активность.  
2. Клиент может не проявлять активность в каком-либо месяце, но всё равно должен быть учтён во всех последующих периодах, начиная с месяца активности.  
3. Не использовать макросы, только формулы.  
 
СУММПРОИЗВ(), по-моему, не подойдёт, т.к. считает только заполненные ячейки, а клиент может не проявлять активности в каком-либо месяце.    
Добавить скрываемую таблицу, в которой с помощью ЕСЛИ() или других функций будет проверяться и учитываться факт активности клиента, - можно, но не нужно, т.к. надо будет проверять каждый месяц, а с учётом того, что в таблице 1500 строк, файл получиться слишком тяжёлым (файл в Excel 2003, в 2007 нет возможности сделать).  
 
Прошу помочь разобраться с этой задачей.  
Заранее благодарен.
 
 
 
#2
01.11.2008 08:34:36
Если я правильно понял задание, то решение в прикрепленном файле.    
Зря Вы недооцениваете функцию =суммпроизв().  
С уважением, Александр.
 
 
 
#3
01.11.2008 08:52:30
{quote}{login=Sh_Alex}{date=01.11.2008 08:34}{thema=Нарастающий итог при интересных условиях}{post}Если я правильно понял задание, то решение в прикрепленном файле.    
Зря Вы недооцениваете функцию =суммпроизв().  
С уважением, Александр.{/post}{/quote}  
 
Спасибо, Александр. Я не учёл, что можно писать СУММ(СУММПРОИЗВ();).    
НО! В предложенном Вами примере считается количество месяцев активности нарастающим итогом, но не количество уникальных клиентов нарастающим итогом. Поясню: в моём примере каждая строка с "Барнаул" означает нового клиента (наименование клиента не указываю).  
Т.о., 4 строки с "Барнаул" означает, что есть 4 клиента, которые в разное время проявили активность. При этом считать нужно количество клиентов нарастающим итогом.    
 
Если не до конца понятно, что мне нужно получить, пожалуйста, задайте вопросы, я отвечу.  
 
Заранее благодарен за помощь в решении.
 
 
 
#4
01.11.2008 08:56:09
2_Sh_Alex  
Извините что вмешиваюсь, но по моему кадру нужно суммирование не по всем обращениям по условию город, а подсчёт количества уникальных клиентов обратившихся из города к заданному месяцу. При этом индентификация уникальных клиентов у него идет с помощью строк.  
В рамках его организации данных вопрос звучит так  
посчитать для заданного месяца количество строк с индентификатором город, в которых есть данные об обращении в колонках с месяцем<=заданному.  
то есть если строка до текщего месяца пустая она не учитывается, как только в ней появилась цифра то +1 к количеству строк  
 
Правильная организация таких данных  
город/клиент/список дат обращений  
 
2 КАДР формулу то сочинить можно, но если вы боитесь что из-зп заполненных ячеек книга утяжелиться, то почему вы не боитесь того что из-за того что каждая расчётная ячейка зависит от большой области данных книга начнёт тормозить - медленно считать.  
Конечно можно поставить пересчёт только по команде, но всё равно какие нибудь проблемы остануться....  
 
--------  
dl
 
 
 
#5
01.11.2008 09:06:20
{quote}{login=ded_luka}{date=01.11.2008 08:56}{thema=}{post}  
2 КАДР формулу то сочинить можно, но если вы боитесь что из-зп заполненных ячеек книга утяжелиться, то почему вы не боитесь того что из-за того что каждая расчётная ячейка зависит от большой области данных книга начнёт тормозить - медленно считать.  
Конечно можно поставить пересчёт только по команде, но всё равно какие нибудь проблемы остануться....  
 
--------  
dl{/post}{/quote}  
 
ded_luka, спасибо за правильное понимание решаемой мной задачи.  
Не уверен, что книга начнёт существенно тормозить. Чтобы убедиться в этом, надо попробовать. Есть предложения по формулам?
 
 
 
#6
01.11.2008 09:40:17
{quote}{login=КАДР}{date=01.11.2008 09:06}{thema=Re: ded_luka, спасибо за правильное понимание решаемой мной задачи.  
Не уверен, что книга начнёт существенно тормозить. Чтобы убедиться в этом, надо попробовать. Есть предложения по формулам?{/post}{/quote}  
 
задача  
посчитать для заданного месяца количество строк с индентификатором город, в которых есть данные об обращении в колонках с месяцем<=заданному.  
сводиться  
1)определить для каждого клиента = строки первое обращение = месяц  
2)посчитать для заданного месяца количество строк город=выбранный город + первое обращение<=выбранный месяц  
 
Напрашивается решение с дополнительным столбцом для индентификатора ID первого обращения.  
 
Добавляем столбец В  
в него в первую строку таблицы исходных данных формула массива  
=МИН(ЕСЛИ(C2:N2="";266;СТОЛБЕЦ(C2:N2)))-1  
протягиваем на всю высоту таблицы  
 
В первую ячейку расчётной таблицы    
=СУММПРОИЗВ(($A$2:$A$21=$A28)*1;($B$2:$B$21<СТОЛБЕЦ())*1)  
протягиваем на всю таблицу  
 
решение в файле  
можно обойтись и без дополнительного столбца, но формулы существенно осложняться.
 
 
 
#7
01.11.2008 09:54:13
{quote}{login=ded_luka}{date=01.11.2008 09:40}{thema=Re: Re: }{post}{quote}{login=КАДР}{date=01.11.2008 09:06}{thema=Re: ded_luka, спасибо за правильное понимание решаемой мной задачи.  
Не уверен, что книга начнёт существенно тормозить. Чтобы убедиться в этом, надо попробовать. Есть предложения по формулам?{/post}{/quote}  
Добавляем столбец В  
в него в первую строку таблицы исходных данных формула массива  
=МИН(ЕСЛИ(C2:N2="";266;СТОЛБЕЦ(C2:N2)))-1  
протягиваем на всю высоту таблицы  
 
В первую ячейку расчётной таблицы    
=СУММПРОИЗВ(($A$2:$A$21=$A28)*1;($B$2:$B$21<СТОЛБЕЦ())*1)  
протягиваем на всю таблицу  
 
решение в файле  
можно обойтись и без дополнительного столбца, но формулы существенно осложняться.{/post}{/quote}  
 
Спасибо, ded_luka!!! А давайте попробуем обойтись без доп. столбца? Пожалуйста, покажите пример!
 
 
 
#8
01.11.2008 10:27:36
Извините...  
Спасибо, ded_luka!!! А давайте попробуем обойтись без доп. столбца? Пожалуйста, покажите пример!
 
 
 
#9
01.11.2008 10:29:23
ваще запарился :)
 
 
 
#10
01.11.2008 12:36:17
{quote}{login=КАДР}{date=01.11.2008 10:29}{thema=}{post}ваще запарился :){/post}{/quote}  
 
вводите в первую ячейку расчётной таблицы формулу массива  
=СУММ(ЕСЛИ(МУМНОЖ(МУМНОЖ(($A$2:$A$21=$A28)*$B$2:$M$21;ЕСЛИ(СТРОКА(СМЕЩ($A$1;0;0;12;20))>СТОЛБЕЦ(СМЕЩ($A$1;0;0;12;20));0;1));ЕСЛИ(СТРОКА($B$2:$B$21)=СТОЛБЕЦ();1;0))=0;0;1))  
протягиваете на всю расчётную таблицу  
здесь  
$B$2:$M$21 - исходная таблица  
$A$2:$A$21 - список городов  
$A28 -выбранный город  
12 -ширина исходной таблицы  
20 -высота исходной таблицы  
$B$2:$B$21 диапазон с высотой равной высоте таблицы и начинающийся со второй строки - соответствует номеру столбца с которого начинается расчётная таблица  
 
как работает формула обяснять не буду и так времения из-за неохоты и неумения экселя строить формулу массива от формулы массива убил кучу.  
 
не работают конструкции вида сумм(индекс(матрица;все_строки;выбранный столбец)  
 
Для того кто подскажет как обойтись без мумнож или хотя бы ограничиться одним мумнож -СТАНЦУЮ ДЖИГУ!!!!! (не зависимо от длины формулы)  
 
а вобще такие извращения не более чем плод досужего ума...  
 
Кадр! измените структуру данных и ввода и жить станет легче!
 
 
 
#11
01.11.2008 12:52:20
Дед Лука, спасибо!!!
 
 
 
#12
01.11.2008 13:00:15
{quote}{login=ded luka}{date=01.11.2008 12:36}{thema=Re: }{post}{quote}{login=КАДР}{date=01.11.2008 10:29}{thema=}{post}ваще запарился :){/post}{/quote}  
 
12 -ширина исходной таблицы  
20 -высота исходной таблицы  
$B$2:$B$21 диапазон с высотой равной высоте таблицы и начинающийся со второй строки - соответствует номеру столбца с которого начинается расчётная таблица  
 
{/post}{/quote}  
 
Точно 12 - ширина, а 20 - высота таблицы? Судя по формуле, всё наоборот.
 
 
 
#13
01.11.2008 14:17:22
{quote}{login=КАДР}{date=01.11.2008 01:00}{thema=Re: Re: }{post}{quote}{login=ded luka}{date=01.11.2008 12:36}{thema=Re: }{post}{quote}{login=КАДР}{date=01.11.2008 10:29}{thema=}{post}ваще запарился :){/post}{/quote}  
 
12 -ширина исходной таблицы  
20 -высота исходной таблицы  
$B$2:$B$21 диапазон с высотой равной высоте таблицы и начинающийся со второй строки - соответствует номеру столбца с которого начинается расчётная таблица  
 
{/post}{/quote}  
 
Точно 12 - ширина, а 20 - высота таблицы? Судя по формуле, всё наоборот.{/post}{/quote}  
Точно! 12 - ширина, а 20 - высота исходной таблицы  
Вас смутило матричное умножение, дело в том что, чтобы умножить матрицу на матрицу ширина первой должна быть равна высоте второй, поэтому в смещ 12 подставлено как высота  
ну а дальше действительно провокация  
надо не так как было итоговые матрицы шириной 20  
а шириной 12 по числу столбцов остальное был лишний кусок  
 
итого надо так  
=СУММ(ЕСЛИ(МУМНОЖ(МУМНОЖ(($A$2:$A$21=$A28)*$B$2:$M$21;ЕСЛИ(СТРОКА(СМЕЩ($A$1;0;0;12;12))>СТОЛБЕЦ(СМЕЩ($A$1;0;0;12;12));0;1));ЕСЛИ(СТРОКА($B$2:$B$13)=СТОЛБЕЦ();1;0))=0;0;1))
 
 
 
#14
02.11.2008 14:00:29
{quote}{login=ded luka}{date=01.11.2008 12:36}{thema=Re: }{post}{quote}{login=КАДР}{date=01.11.2008 10:29}{thema=}{post}ваще запарился :){/post}{/quote}  
Для того кто подскажет как обойтись без мумнож или хотя бы ограничиться одним мумнож -СТАНЦУЮ ДЖИГУ!!!!! (не зависимо от длины формулы)  
{/post}{/quote}  
 
Давай, дедуля, тряхни стариной - станцуй-ка мне Джигу:))
 
 
 
#15
02.11.2008 19:25:50
Блин че за глюки - у меня при обычном просмотре и при написании ответа совершенно разные содержания постов:) Вообще изначально хотел попросить дедулю тряхнуть стариной и станцевать мне Джигу:)
 
 
 
#16
06.11.2008 11:46:36
{quote}{login=Raptor}{date=02.11.2008 07:25}{thema=}{post}Блин че за глюки - у меня при обычном просмотре и при написании ответа совершенно разные содержания постов:) Вообще изначально хотел попросить дедулю тряхнуть стариной и станцевать мне Джигу:){/post}{/quote}  
 
Raptor, Вам в форме макроса или натурой?  
Согдасен за мной должок, за недогадостость в построение динамических матриц с помощью СМЕЩ(...КОЛОНКА()...)
 
 
 
Страницы: 1
Читают тему
Наверх