Суммирование ячеек с заданного диапазона листов

[skivpokemon]

Добрый день всем.

Встала задача суммировать ячейки с диапазона листов, который задается выпадающими списками.

вроде бы с этим справляется СУММ
=СУММ(Лист2:Лист14!A2:A15)

Но никак не получается подставить выбранные листы в СУММ.
Пробовал через СМЕЩ построить выражение вида (подставляя в ячейки Е17, G17 необходимые листы)
=СУММ(СМЕЩ(E17;0;0;1;1):СМЕЩ(G17;0; 0;1;1)!А2:А15)
но тщетно.

Возможно ли вообще решение задачи только функциями excel?

[vikttur]

Функция ДВССЫЛ()

[The_Prist]

Сообщение от vikttur

Функция ДВССЫЛ()

И неправильно! Виктор, уж Вы-то знаете, что ДВССЫЛ не работает с трехмерными ссылками.
Мое мнение - проще процедурой или функцией пользователя.

[vikttur]

YНе дописал. Приблизительно так:
=СУММ(ДВССЫЛ(E17&":"&G17&"!А2:А15") )

[vikttur]

Сообщение от The_Prist

И неправильно! Виктор, уж Вы-то знаете...

Забыл и покраснел от стыда

[The_Prist]

Вот. Элементарная функция пользователя:

Function Summ_Few_Sheets(Начальный_лист As Range, Последний_лист As Range, Диапазон_Суммирования As Range) As Double
Summ_Few_Sheets = Evaluate("=Sum('" & Начальный_лист.Value & ":" & Последний_лист.Value & "'!" & Диапазон_Суммирования.Address & ")")
End Function

Начальный_лист - ссылка на ячейку с именем первого листа
Последний_лист - ссылка на ячейку с именем последнего листа
Диапазон_Суммирования - выделяете диапазон на листе, ячейки которого необходимо суммировать на указанных листах.

Создаете новый стандартный модуль, вставляете в него приведенный код. в диспетчере функций впоявиться категория - Определенные пользователем, а в ней эта функция - Summ_Few_Sheets.

Для работы функции необходимо разрешить работу макросов.

[skivpokemon]

vicctur ДВССЫЛ() как раз попробовал с тем же успехом, я и не знал что она не работает с трёхмерными ссылками.

The_Prist большое спасибо, сейчас попробую.

Как я понимаю решение без применения макроса, если и есть, очень громоздкое?

[The_Prist]

Сообщение от skivpokemon

Как я понимаю решение без применения макроса, если и есть, очень громоздкое?

Да. Получиться нечто вроде того, что тупо перечислять все необходимые листы в СУММ.

[mchip]

Вот такая формула все считает:

=СУММПРОИЗВ(--Ч(ДВССЫЛ("Лист"&СТРОКА(ДВССЫЛ(A1))&"!A2")))

Если в ячейку A1 вводить значения ну например 1:3 считает (с 1 по 3 лист) или 1:14 (с 1 по 14 лист) сумму ячеек А2

[The_Prist]

Выход, конечно, но имеет и ряд своих ограничений:

• все листы должны иметь одинаковый префикс(в данном случае "Лист").Если где-то внутри выбранного диапазона листов будет лист с другим префиксом - #ССЫЛКА!;
• невозможность указать префикс листа в самой ячейке, из которой берется значение.

Больше всего меня самого смущает именно первый пункт, т.к. многие при работе с листами стараются давать им более осмысленные имена, нежели стандартные(я не исключение).
Но сама формула хорошая, многим пригодится, я думаю. Можно её и чуть подправить:

=СУММПРОИЗВ(--Ч(ДВССЫЛ("Лист"&СТРОКА(ДВССЫЛ(A1&":"&B1))&"!A2:A15")))

Тогда номер первого листа в ячейке А1, номер второго в В1.