Ссылка в формуле на множество листов

Пользователь

Сообщений: 17 Регистрация: 16.05.2021

10.07.2021 13:16:17

Здравствуйте!

При создании формулы, которая выводит результат, попадающий под несколько условий, столкнулся с проблемой. Эта проблема заключается в том, что данные проверяются по формуле, в которой идет ссылка на определенный лист.. этих листов много и в будущем они будут добавляться.

Код

=ЕСЛИ(
СУММПРОИЗВ(($C8=[Книга1.xlsx]Лист1!$B:$B)*(N8=[Книга1.xlsx]Лист1!$A:$A)*[Книга1.xlsx]Лист1!$C:$C)<>0;
СУММПРОИЗВ(($C8=[Книга1.xlsx]Лист1!$B:$B)*(N8=[Книга1.xlsx]Лист1!$A:$A)*[Книга1.xlsx]Лист1!$C:$C);
СУММПРОИЗВ(($C8=[Книга1.xlsx]Лист2!$B:$B)*(N8=[Книга1.xlsx]Лист2!$A:$A)*[Книга1.xlsx]Лист2!$C:$C))

во-первых формула будет очень громоздкая, если в ней множество раз копировать строчку и заменять номер листа
во-вторых ее постоянно придется редактировать для проверки по новым листам

Как можно это предотвратить?

2 книги эксель:
Книга1, множество листов с одинаковым расположением данных: 3 столбца, два для проверки(А, B) и третий © для вывода результата в книгу2
Книга2, один лист: 3 столбца с данными, два для проверки(C, N) и третий (D) для вывода результата из книги1

Прикрепленные файлы

Книга1.xlsx (11.05 КБ)
Книга2.xlsx (10.35 КБ)

Вадим Катыкин

Пользователь

Сообщений: 20 Регистрация: 01.07.2021

12.07.2021 16:21:58

Код

=
LET(
книга_1_лист_1;"[Книга1.xlsx]Лист1!";
книга_1_лист_2;"[Книга1.xlsx]Лист2!";
ЕСЛИ(
СУММПРОИЗВ(($C4=ДВССЫЛ(СЦЕП(книга_1_лист_1;"$B:$B")))*(N4=ДВССЫЛ(СЦЕП(книга_1_лист_1;"$A:$A")))*ДВССЫЛ(СЦЕП(книга_1_лист_1;"$C:$C")))<>0;
СУММПРОИЗВ(($C4=ДВССЫЛ(СЦЕП(книга_1_лист_1;"$B:$B")))*(N4=ДВССЫЛ(СЦЕП(книга_1_лист_1;"$A:$A")))*ДВССЫЛ(СЦЕП(книга_1_лист_1;"$C:$C")));
СУММПРОИЗВ(($C4=ДВССЫЛ(СЦЕП(книга_1_лист_2;"$B:$B")))*(N4=ДВССЫЛ(СЦЕП(книга_1_лист_2;"$A:$A")))*ДВССЫЛ(СЦЕП(книга_1_лист_2;"$C:$C")))))

В массивности формула не теряет, к тому же, в силу особенности ДВССЫЛ "Книга1" должна быть постоянно открыта на время нахождения совпадений, но из плюсов - адреса меняются только в одном месте руками, запутаться в формуле на порядок сложнее.

Изменено: Вадим Катыкин - 12.07.2021 16:23:15

Иван Ж Пользователь Сообщений: 17 Регистрация: 16.05.2021	#3 27.07.2021 23:01:38 Вадим, благодарю за ответ! Условие "постоянно открыта" не проблема, т.к. большинство рабочих книг открыто постоянно при работе.