Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Суммирование данных с разных листов
 
#1
24.06.2018 13:20:22
Доброго времени суток уважаемые участники форума!
Стоит такая задача собрать сведения с разных листов и просуммировать и по условию, условие такое: наименование строки и наименование столбца, какой должен быть конечный результат - в примере столбцы выделены желтым цветом, понимаю, что скорее всего решается с помощью формул массива, с помощь формулы суммеслимн, но могу корректно составить формулы...помогите пожалуйста решить задачу с помощью формул, так свод нужно сделать с большого кол-ва листов (более 15) строк более 200 в на каждом листе, свод содержит все наименования строк и столбцов...
 
 
 
#2
24.06.2018 17:25:46
Форумяне, помогите пжл...очень нуждаюсь в вашей помощи!
 
 
 
#3
25.06.2018 00:10:46
Добрый день!

Я не очень большой специалист, но мне придумалось решение проблемы с использованием функций АДРЕС, ДВССЫЛ и ВПР. Пример во вложении. Предварительно нужно создать колонку с названием листов. Далее в ячейку D4 на сводной ставим знак равно и вставляем формулу:
Код
ВПР($B4;ДВССЫЛ(АДРЕС(4;2;;;$D$12)&":V500");СТОЛБЕЦ(D:D)-1;0)+

её надо скопировать столько раз, сколько у Вас имеется листов, на которых находятся данные для суммирования. Всё это пишется в одной ячейке. В последний раз плюсик стираем. При этом формулы нужно немного поменять, кроме первого раза. Во втором копировании вместо $D$12 нужно поставить $D$13 (одну цифру поменять), в третьем $D$14 - это ссылки на названия листов. В принципе само написание этой формулы тоже можно автоматизировать. Если захотите, я Вам напишу как - просто, может, Вам уже начало не понравится. Дальше формула протягивается вниз и первая колонка копируется в остальные, в которых должна стоять сумма по листам.
"Просите, и дано будет вам; ищите, и найдете; стучите, и отворят вам" (Мф. 7. 7-9)
 
 
 
#4
25.06.2018 03:24:59
Спасибо большое! Сегодня попробую....
Я почему-то думал, что это решается помощью формулы СУММЕСЛИМН...
 
 
 
#5
25.06.2018 10:59:04
Цитата
nastya2018 написал:
Добрый день!Я не очень большой специалист, ....
Пробовал немного усовершенствовать и вместо ДВССЫЛ(АДРЕС(4;2;;;$D$12) заменить ПОИСКПОЗ, но что-то пошло не так - выдает ошибку или моё мнение ошибочное...
Изменено: avar99 - 25.06.2018 11:22:04
 
 
 
#6
25.06.2018 11:03:22
avar99, Вам знаком смысл слова "цитата"?
 
 
 
#7
25.06.2018 11:09:40
Цитата
Юрий М написал:
Да, знаком, хотел полнее выразить суть ранее изложенного, прошу извинить, если что-то не так сделал...
Изменено: avar99 - 25.06.2018 11:24:04
 
 
 
#8
25.06.2018 11:16:28
"Добрый день! Я не очень большой специалист," - на этом хотели сделать акцент? У Вас не цитата, а полная копия сообщения.
Вернитесь в #5 и приведите его в порядок.
И #7 аналогично. Зачем там вообще цитата?
 
 
 
#9
25.06.2018 11:23:49
Замечания принял и устранил....
 
 
 
#10
25.06.2018 11:29:21
ДВССЫЛ(АДРЕС(...)) <- этой формулой задаётся диапазон для ВПР. ПОИСКПОЗ - это если ИНДЕКС использовать, но в функции ИНДЕКС же нельзя дать ссылку на название листа, если я правильно понимаю..  
"Просите, и дано будет вам; ищите, и найдете; стучите, и отворят вам" (Мф. 7. 7-9)
 
 
 
#11
25.06.2018 11:31:40
avar99, Если таблицы идентичные и расположение данных одинаково тогда вариант такой.
Берете пишите в первой ячейку =СУММ(название первого листа : название последнего листа который нужно просуммировать и ячейка которая суммируется)
Предварительно пришлось переименовать ваш лист где на конце цифра после дефиса и скрытые листы.

=СУММ(МБУ2:ДЮСШ4!D4)

З.Ы. ссылка на метод

Изменено: a.i.mershik - 25.06.2018 11:34:20
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#12
25.06.2018 11:41:19
a.i.mershik,к сожалению данные там на разных листах располагаются по разному

мой вариант с удф
Изменено: VideoAlex - 26.06.2018 11:04:55
 
 
 
#13
25.06.2018 11:53:39
Цитата
a.i.mershik написал:
Если таблицы идентичные и расположение данных одинаково
...нет расположение не одинаковое, например где-то данные в столбце  D, где-то в C...
 
 
 
#14
25.06.2018 11:58:14
Цитата
VideoAlex написал:
мой вариант с удф
у Вас с макросом реализовано?
 
 
 
#15
25.06.2018 12:00:27
Цитата
avar99 написал:
нет расположение не одинаковое,
тогда
Код
=ИНДЕКС(МБУ2!$1:$1048576;ПОИСКПОЗ('СВОД УЭ'!$B4;МБУ2!$B:$B;0);ПОИСКПОЗ(D$2;МБУ2!$2:$2;0))+
для каждого листа точно такую же формула меняя только название... листа.
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#16
25.06.2018 12:01:43
Цитата
avar99 написал:
у Вас с макросом реализовано?
так точно
 
 
 
#17
25.06.2018 12:07:44
a.i.mershik, спасибо - попробую...
 
 
 
#18
25.06.2018 12:17:11
Еще один вариант со вспомогательными вычислениями.
В добавленных отдельных столбцах сводной таблицы вычисляется номер соответствующей строки для каждой исходной таблицы. А затем, в самой самой таблице с их помощью подтягиваются данные из источников данных.
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ($B3;ИНДЕКС(ДВССЫЛ(AB$2);0;2);0);"")
Код
=ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ($AB$2);$AB4;4);ИНДЕКС(ДВССЫЛ($AC$2);$AC4;4));"")

Обратите внимание на:
* Используются именованные диапазоны (близкие к названиям листов) - поэтому не получается отказаться от летучей функции ДВССЫЛ() - на больших объемах рекомендуется {временно} отключить автоматический пересчет листов
* В Вашем примере тариф вычисляется как среднее значение из суммы нескольких величин - т.е. сама эта сумма. Либо откажитесь от функции СРЗНАЧ(), либо передавайте ей перечень значений, а не их сумму (см. пример)
Изменено: IKor - 25.06.2018 12:18:01
 
 
 
#19
26.06.2018 11:00:39
Всем БОЛЬШОЕ человеческое спасибо!
 
 
 
Страницы: 1
Наверх