Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Сквозной поиск и формулы сквозного действия
 
#1
07.11.2009 08:49:41
Добрый день (утро, вечер, ночь - ненужное зачеркнуть :-)) уважаемые форумчане!  
 
Вопрос мой прост: Применение каких формул возможно по принципу сквозного суммирования =СУММ('1:8'!A1) from http://www.planetaexcel.ru/forum.php?thread_id=955   ?  
 
Меня, конкретно, интересуют формулы поиска и возврата значений (ИНДЕКС(ПОИСКПОЗ()), ВПР(), ГПР()...), но для общего развития хотелось бы знать и о других :)  
 
Спасибо, всем кто поможет!
 
 
 
#2
07.11.2009 11:05:39
Serge 007, наберите в поиске по справке экселя "трехмерные" или "о формулах".  
В результатах увидите "о формулах". Там сказано следующее:  
Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА
Bite my shiny metal ass!      
 
 
 
#3
07.11.2009 11:39:43
{quote}{login=Лузер™}{date=07.11.2009 11:05}{thema=}{post}Serge 007, наберите в поиске по справке экселя "трехмерные" или "о формулах".  
В результатах увидите "о формулах". Там сказано следующее:  
Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА{/post}{/quote}  
 
Спасибо, хрен бы я догадался про "трхмерные" :)))  
А вот "о формулах" выдаёт всё что угодно, но только не то что просим (см вложение.) :(((  
 
Ну раз нельзя пользоваться тем что есть, то может подскажете как это сделать иным способом?
 
 
 
#4
07.11.2009 12:06:00
через двссыл  
в одном столбце список имен консолидированных листов,в другом ДВССЫЛ(ячейки или диапазона) и все, что хотите
 
 
 
#5
07.11.2009 12:57:36
{quote}{login=}{date=07.11.2009 12:06}{thema=}{post}через двссыл  
в одном столбце список имен консолидированных листов,в другом ДВССЫЛ(ячейки или диапазона) и все, что хотите{/post}{/quote}  
 
А Вы можете показать на примере?  
Сейчас формула для трёх листов представляет из себя такое чудовище:  
"=ЕСЛИ(ЕНД(ИНДЕКС(Tаблица!$1:$1048576;ПОИСКПОЗ(B2;Tаблица!B:B;0);ПОИСКПОЗ($C$1;Tаблица!$1:$1;0)));ЕСЛИ(ЕНД(ИНДЕКС(Таблица!$1:$1048576;ПОИСКПОЗ(B2;Таблица!B:B;0);ПОИСКПОЗ($C$1;Таблица!$1:$1;0)));ЕСЛИ(ЕНД(ИНДЕКС(Таблицa!$1:$1048576;ПОИСКПОЗ(B2;Таблицa!B:B;0);ПОИСКПОЗ($C$1;Таблицa!$1:$1;0)));1;ИНДЕКС(Таблицa!$1:$1048576;ПОИСКПОЗ(B2;Таблицa!B:B;0);ПОИСКПОЗ($C$1;Таблицa!$1:$1;0)));ИНДЕКС(Таблица!$1:$1048576;ПОИСКПОЗ(B2;Таблица!B:B;0);ПОИСКПОЗ($C$1;Таблица!$1:$1;0)));ИНДЕКС(Tаблица!$1:$1048576;ПОИСКПОЗ(B2;Tаблица!B:B;0);ПОИСКПОЗ($C$1;Tаблица!$1:$1;0)))"
 
 
 
#6
07.11.2009 12:58:23
.
 
 
 
#7
07.11.2009 13:24:49
{quote}{login=Serge 007}{date=07.11.2009 12:57}{thema=Re: }{post}{quote}{login=}{date=07.11.2009 12:06}{thema=}{post}через двссыл  
в одном столбце список имен консолидированных листов,в другом ДВССЫЛ(ячейки или диапазона) и все, что хотите{/post}{/quote}  
 
А Вы можете показать на примере?  
Сейчас формула для трёх листов представляет из себя такое чудовище:  
"=ЕСЛИ(ЕНД(ИНДЕКС(Tаблица!$1:$1048576;ПОИСКПОЗ(B2;Tаблица!B:B;0);ПОИСКПОЗ($C$1;Tаблица!$1:$1;0)));ЕСЛИ(ЕНД(ИНДЕКС(Таблица!$1:$1048576;ПОИСКПОЗ(B2;Таблица!B:B;0);ПОИСКПОЗ($C$1;Таблица!$1:$1;0)));ЕСЛИ(ЕНД(ИНДЕКС(Таблицa!$1:$1048576;ПОИСКПОЗ(B2;Таблицa!B:B;0);ПОИСКПОЗ($C$1;Таблицa!$1:$1;0)));1;ИНДЕКС(Таблицa!$1:$1048576;ПОИСКПОЗ(B2;Таблицa!B:B;0);ПОИСКПОЗ($C$1;Таблицa!$1:$1;0)));ИНДЕКС(Таблица!$1:$1048576;ПОИСКПОЗ(B2;Таблица!B:B;0);ПОИСКПОЗ($C$1;Таблица!$1:$1;0)));ИНДЕКС(Tаблица!$1:$1048576;ПОИСКПОЗ(B2;Tаблица!B:B;0);ПОИСКПОЗ($C$1;Tаблица!$1:$1;0)))"{/post}{/quote}  
 
если кол-во листов может измениться, то строится таблица с возможностью наращивания сверху вниз  
если листа с таким именем нет, т е ошибка ССЫЛКА!, то не считать  
каждый консолидированный лист обсчитывается индивидуально, а потом собираете
 
 
 
#8
07.11.2009 13:37:24
"...если кол-во листов может измениться, то строится таблица с возможностью наращивания сверху вниз  
если листа с таким именем нет, т е ошибка ССЫЛКА!, то не считать  
каждый консолидированный лист обсчитывается индивидуально, а потом собираете..."  
 
Количество листов не меняется...  
 
И всё-же, если возможно, покажите на примере, потому что я саму идею не улавливаю...
 
 
 
#9
07.11.2009 14:10:27
А зачем такое именование листов? Формулы не читабельны совсем) это чтобы враг не догадался?:)
 
 
 
#10
07.11.2009 14:15:24
{quote}{login=Dophin}{date=07.11.2009 02:10}{thema=}{post}А зачем такое именование листов? Формулы не читабельны совсем) это чтобы враг не догадался?:){/post}{/quote}  
 
Андрей, да на формулы в примере не обращай внимание, они пример того что есть, а получить надо тоже самое, только проще :)  
 
Листы можно переименовать, если не удобно (это из образца файла), рабочие всё-равно называются по другому, ну и конечно по разному ;-)
 
 
 
#11
07.11.2009 14:17:03
а просто суммесли() не проще использовать вместо индекса?
 
 
 
#12
07.11.2009 14:23:51
=СУММ(ЕСЛИ('3'!$B$2:$B$10=Всего!B2;'3'!$C$2:$C$10)+ЕСЛИ('2'!B2:B10=Всего!B2;'2'!C2:C10)+ЕСЛИ('1'!B2:B10=Всего!B2;'1'!C2:C10)) короче но массив)  
 
 
хотя больше чем уверен что в итоге окажется что нужная формула будет выглядеть примерно так: =СУММПРОИЗВ(('1:3'!B2:B9=Всего!B2)*('1:3'!C2:C9))  
 
:)
 
 
 
#13
07.11.2009 14:26:26
{quote}{login=Dophin}{date=07.11.2009 02:17}{thema=}{post}а просто суммесли() не проще использовать вместо индекса?{/post}{/quote}  
 
"Показатель" в разных столбцах могёт быть, для этого и индексирую...  
Сори, виноват, не написал сразу...
 
 
 
#14
07.11.2009 14:35:00
=СУММЕСЛИ('1'!$B$2:$B$10;Всего!B2;ИНДЕКС('1'!$B$2:$F$24;;ПОИСКПОЗ(Всего!$C$1;'1'!$B$1:$F$1;0)))+СУММЕСЛИ('2'!$B$2:$B$10;Всего!B2;ИНДЕКС('2'!$B$2:$F$24;;ПОИСКПОЗ(Всего!$C$1;'2'!$B$1:$F$1;0)))+СУММЕСЛИ('2'!$B$2:$B$10;Всего!B2;ИНДЕКС('2'!$B$2:$F$24;;ПОИСКПОЗ(Всего!$C$1;'2'!$B$1:$F$1;0)))  
 
такой вариант попроще по идее будет)
 
 
 
#15
07.11.2009 14:41:48
или вот с допстолбцом)
 
 
 
#16
07.11.2009 14:45:06
{quote}{login=Dophin}{date=07.11.2009 02:35}{thema=}{post}=СУММЕСЛИ('1'!$B$2:$B$10;Всего!B2;ИНДЕКС('1'!$B$2:$F$24;;ПОИСКПОЗ(Всего!$C$1;'1'!$B$1:$F$1;0)))+СУММЕСЛИ('2'!$B$2:$B$10;Всего!B2;ИНДЕКС('2'!$B$2:$F$24;;ПОИСКПОЗ(Всего!$C$1;'2'!$B$1:$F$1;0)))+СУММЕСЛИ('2'!$B$2:$B$10;Всего!B2;ИНДЕКС('2'!$B$2:$F$24;;ПОИСКПОЗ(Всего!$C$1;'2'!$B$1:$F$1;0)))  
 
такой вариант попроще по идее будет){/post}{/quote}  
 
Спасибо, действительно попроще!  
Если у кого будут более оптимальные идеи - спасибо, если нет - в понедельник буду тестить Dophin version :-)
 
 
 
#17
07.11.2009 14:46:24
{quote}{login=Dophin}{date=07.11.2009 02:41}{thema=}{post}или вот с допстолбцом){/post}{/quote}  
 
А вот и ДВССЫЛ  :-)  
 
Спс!
 
 
 
#18
07.11.2009 17:25:12
Такие варианты
KL
 
 
 
#19
07.11.2009 21:52:41
{quote}{login=KL}{date=07.11.2009 05:25}{thema=}{post}Такие варианты{/post}{/quote}  
 
Вау KL, супер! Есть чему поучиться!!!  
Спасибо ОГРОМНОЕ, буду разбираться!
 
 
 
Страницы: 1
Читают тему
Наверх