Вопрос или проблема
Я работаю над рабочей книгой Excel, в которой данные распределены по нескольким листам (например, “Уголь”, “Сталь”, “Турбодизель”, “Компьютер”). Каждый лист содержит массивы данных, относящихся к различным ресурсам (таким как уголь, вода, железо и т. д.), с сопутствующими значениями, такими как Производство, Использование и Избыток. Я успешно собрал все уникальные названия ресурсов в одном столбце на сводном листе, используя формулы, но столкнулся с проблемой, когда попытался суммировать сопутствующие значения по листам. Проблема возникает из-за того, что массивы на каждом листе не одинакового размера, а некоторые ресурсы повторяются на разных листах. Это приводит к ошибкам при попытке объединить их в один сводный столбец. Мне нужно суммировать значения для каждого уникального ресурса на разных листах, но разные размеры массивов и разбросанные данные делают это трудно достижимым. Буду признателен за любые советы по эффективному суммированию этих значений в одном массиве! Я также предпочел бы сделать это без VBA, так как у меня были с ним проблемы в прошлом.
Моя формула для сбора уникальных текстовых данных на сводной странице:
UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>", TRUE, $AA$4#, $AB$4#, $AC$4#, $AD$4#)&"</s></t>", "//s")) и AA по AD найдены:
LAMBDA(c,
FILTER(
INDIRECT(c & ":" & c ),
(INDIRECT(c & ":" & c ) <> "") *
(ROW(INDIRECT(c & ":" & c)) > 2)
)
)(CHAR(AA$3 + 64))
где третья строка – это номер столбца найденных данных. Мне не нравится этот подход, и я предпочел бы использовать одну формулу для поиска всех уникальных данных.
Я получаю каждый столбец данных на своей сводной странице следующим образом:
LAMBDA(x,c,
IFERROR(
FILTER(INDIRECT("'" & x & "'!$" & c &":$" & c), INDIRECT("'"&x&"'!$S:$S") = TRUE),
NA()
)
)
(INDIRECT(ADDRESS(1, COLUMN())),
INDIRECT(ADDRESS(2, COLUMN())))
Моя сводная страница:
изображение сводной страницы
Ответ или решение
Для успешного агрегирования данных из множества листов в единую сводную таблицу без использования VBA, вам необходимо использовать формулы Excel, которые позволяют эффективно работать с уникальными значениями и суммировать данные, даже если они размещены в массивах различного размера. Рассмотрим поэтапно процесс решения вашей задачи.
Шаг 1: Получение уникальных ресурсов
Для начала нужно получить список уникальных ресурсов. Вы можете использовать более послушную и компактную формулу, чем та, что вы предложили. Например:
=UNIQUE(FILTER(СШАД!A:A, СШАД!A:A <> ""))Здесь СШАД — это имя одного из ваших листов, который вы хотите обработать. Не забудьте дополнить формулу для всех остальных листов. Если ваши уникальные ресурсы содержатся в нескольких разных листах, объедините результаты с помощью TEXTJOIN.
Шаг 2: Суммирование значений для каждого ресурса
Теперь нужно суммировать значения для каждого уникального ресурса. Вы можете использовать комбинацию SUMIF и FILTER, чтобы аккуратно управлять значениями из различных листов. Например:
=SUMIF(INDIRECT("'" & Лист1 & "'!$A:$A"), A2, INDIRECT("'" & Лист1 & "'!$B:$B")) +
SUMIF(INDIRECT("'" & Лист2 & "'!$A:$A"), A2, INDIRECT("'" & Лист2 & "'!$B:$B")) +
СУММПРОИЗВ( ... ) ' Повторить для всех листовЗдесь предполагается, что в каждой таблице ресурс в столбце A и соответствующие значения в столбце B.
Шаг 3: Объединение формул для всех ресурсов
Чтобы собрать всё вместе, вы можете создать формулу в вашем сводном листе, которая будет автоматически суммировать все значения для уникальных ресурсов. Сделайте что-то подобное для каждого ресурса в итоговом списке:
=SUM(SUMIF(INDIRECT("'" & Лист1 & "'!$A:$A"), D2, INDIRECT("'" & Лист1 & "'!$B:$B")),
SUMIF(INDIRECT("'" & Лист2 & "'!$A:$A"), D2, INDIRECT("'" & Лист2 & "'!$B:$B")),
...)Шаг 4: Упрощение с помощью массива
Если ваши массивы не одинакового размера, но вы хотите избежать ошибок типа "spill", можно использовать:
=SUMPRODUCT(SUMIF(INDIRECT("'" & Листи & "'!A:A"), D2, INDIRECT("'" & Листи & "'!B:B")))Здесь необходимо заменить Листи на массив имен листов.
Шаг 5: Проверка данных
После того как вы написали все необходимые формулы, проверьте корректность введенных данных. Убедитесь, что на каждом листе детали размещены одинаково, чтобы формулы работали корректно и всевозможные ошибки были исключены.
Используя приведенные рекомендации, вы сможете эффективно агрегировать данные из различных листов и избежать ошибок, связанных с размером массивов. Надеюсь, это поможет вам в вашей задаче!