СУММЕСЛИМН с промежуточной функцией ВПР в критериях

У меня есть 3 таблицы, 1 из которых я хочу заполнить столбцами с данными на основе других 2. Таблицы примерно структурированы следующим образом:

Table 1 (Semi-Static Data)

SubGroup        Group
-----------     -----------
subgroup(1)     group(a)
subgroup(2)     group(b)
subgroup(3)     group(b)
subgroup(4)     group(c)
etc.

Table 2 (Variable Data)

SubGroup        DataValue
-----------     -----------
subgroup(1)     datavalue(i)
subgroup(2)     datavalue(ii)
subgroup(3)     datavalue(iii)
subgroup(4)     datavalue(iv)
etc.

Table 3 (Results)

Group           TotalValue
-----------     -----------
group(a)        totalvalue(m)
group(b)        totalvalue(n)
group(c)        totalvalue(o)
etc.

Где TotalValue представляет собой сумму всех значений DataValue для всех подгрупп, принадлежащих этой конкретной группе.

например для группы (b) ---> общее значение (n) = значение данных (ii) + значение данных (iii)

Я хочу выполнить этот расчет без добавления каких-либо дополнительных столбцов в таблицы данных и без использования VBA.

В основном мне нужно выполнить COUNTIFS, где есть дополнительный VLOOKUP, соответствующий диапазону критериев подгруппы группе, к которой он принадлежит, а затем только суммирование для значений данных, которые соответствуют оцениваемой группе. Я пытался использовать формулы массива, но у меня возникли проблемы с их работой. Любая помощь будет очень признательна. Спасибо,

РЕДАКТИРОВАТЬ: Хотел добавить некоторые детали, связанные с моим вопросом. Во-первых, все поиски в Google не дали подходящего ответа. Во всех ссылках были решения немного другой проблемы, когда термин ВПР не зависит от критериев СУММЕСЛИМН, а скорее от другой одиночной статической переменной. Stack Overflow предложил похожие решения. Пожалуйста, дайте мне знать, если требуется дополнительная информация, чтобы мой пост подходил для этого форума. Еще раз спасибо.


excel excel-formula worksheet-function
person nbayly    schedule 29.12.2014    source источник

Ответы (4)


arrow_upward
0
arrow_downward

Вы можете использовать функцию СУММПРОИЗВ, чтобы сделать все сразу. Первая ссылка $B$2:$B$5 предназначена для имен групп, вторая ссылка $E$2:$E$5 — для значений данных. Ссылка G2 предназначена для имен групп в третьей таблице, вы можете ввести эту формулу для первой ссылки, а затем перетащить и заполнить остальные.

=СУММПРОИЗВ($E$2:$E$5 * (G2 = $B$2:$B$5))

person Lance Roberts    schedule 30.12.2014
comment
Спасибо за ответ, Ланс. Я думаю, что это будет неприменимо, в основном потому, что мои примеры не очень полезны. Table1 и Table2 не имеют одинаковых размеров, и подгруппы в них не упорядочены одинаково (в отличие от моего примера). Таким образом, G2 = $B$2:$B$5 не создаст массив, который можно было бы умножить на значения DataValues. Пожалуйста, поправьте меня, если я неправильно понимаю ваше решение, и мои извинения за двусмысленный пример. - person nbayly; 30.12.2014
comment
Нет, вы правы, мое решение было основано на вашем примере, показывающем, что в первых двух таблицах просто перечислены все подгруппы, так что количество строк в каждой из них будет одинаковым. Порядок не имеет значения. - person Lance Roberts; 30.12.2014

arrow_upward
0
arrow_downward

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

=SUMIF(C:C,"="&INDEX(A:A,MATCH(E5,B:B,0)),D:D)
person pnuts    schedule 31.12.2014

arrow_upward
0
arrow_downward

WADR & IMHO, это просто плохой дизайн рабочего листа. Из-за отсутствия единственного столбца перекрестных ссылок в Таблице 2 любое решение должно было бы представлять собой определяемую пользователем формулу VBA или слишком сложную формулу массива (последняя из которых мне нравится). даже не уверен, что это возможно). Таблицы данных не являются нормализованными таблицами базы данных, которые вы можете INNER JOIN или GROUP BY ... HAVING.

Формула, которую вы пытаетесь достичь, похожа на

=SUMPRODUCT(SUMIF(D:D, {"subgroup(2)","subgroup(3)"}, E:E))

Это работает только с жестко заданными значениями в виде констант массива (например, {"subgroup(2)","subgroup(3)"}). Я не знаю способа вернуть динамический список обратно в формулу, используя дополнительные встроенные функции Excel, но VBA предлагает некоторые возможности.

ОДНАКО

Простое добавление еще одного столбца в Table2 с очень простым VLOOKUP сводит все ваши проблемы к SUMIF.

Слишком сложное решение

Формула в новом столбце D, строка 2:

=VLOOKUP(E2, A:B, 2, FALSE)

Формула в I2 такова:

=SUMIF(D:D, H2,F:F )

Заполните каждую по мере необходимости. Извините, если это не то, что вы хотели услышать.

person Community    schedule 31.12.2014
comment
Я ценю ваш честный ответ. Я дошел до того, что думаю, что этого можно добиться с помощью формул массива. Я создал формулу, которая выводит массив, представляющий все подгруппы в данной группе: {=ЕСЛИОШИБКА(ИНДЕКС([Таблица1.xlsx]Лист1!Подгруппа,МАЛЕНЬКИЙ(ЕСЛИ([Таблица1.xlsx]Лист1!Группа=G2,СТРОКА([Таблица1 .xlsx]Sheet1!SubGroup)-1),ROWS([Table1.xlsx]Sheet1!SubGroup))),)} Теперь мне осталось использовать этот массив для выполнения условной суммы для всех элементов, содержащихся в этом массиве, но Я не в состоянии продвинуться дальше этого. Любая дальнейшая помощь будет очень признательна. - person nbayly; 31.12.2014

arrow_upward
0
arrow_downward

Спасибо всем, кто откликнулся и прокомментировал этот пост. Мне удалось решить эту проблему, используя формулу массива и некоторую матричную алгебру. Обратите внимание, что я не использую функцию ВПР (этот оператор нельзя выполнять с массивами) и СУММЕСЛИМН, как указано в заголовке.

Моя окончательная формула выглядит так:

{=SUM(IF([Table2.xlsx]Sheet1!SubGroup=TRANSPOSE(IF([Table1.xlsx]Sheet1!Group=G2,[Table1.xlsx]Sheet1!SubGroup,"")),[Table2.xlsx]Sheet1!DataValue))}

Очень просто я создаю переменную массива, которая сравнивает оцениваемую группу (например, ячейку G2) со столбцом групп для таблицы 1 и выводит соответствующие подгруппы. В результате получается массив с таким количеством строк, сколько было в таблице 1 (N), и 1 столбцом: Nx1. Затем я транспонирую этот массив (1xN) и сравниваю его со столбцом SubGroups (Mx1, M — количество строк в таблице 2) и вывожу столбец DataValues ​​для строк, которые имеют соответствующую SubGroup (MxN). Затем я выполняю сумму всего массива, чтобы вернуть одно значение.

Обратите внимание, что, поскольку я не включил выходное значение value_if_false ни для одного из операторов IF, оно просто будет заполнено значением FALSE в массивах, если условия не выполняются. Это не имеет значения, хотя для конечного результата. В первом ЕСЛИ FALSE не будет соответствовать подгруппам, поэтому будет проигнорировано. Во втором случае все значения FALSE, переданные в SUM, будут вычислены как 0. Более сложный вопрос заключается в том, что при этом увеличивается объем памяти, необходимый для обработки, поскольку мы не фильтруем только те значения, которые нам нужны.

Для этого приложения я отказался от фильтрации подмассива, поскольку компромисс в использовании ресурсов был приемлемым. Если бы наборы данных были больше, я бы определенно попробовал это сделать. Еще одна проблема заключалась в том, что я не полностью понимал логику фильтрации, которую использовал на основе http://exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/ поэтому решил упростить. Позже я вернусь к этой концепции, так как думаю, что она сработает. Возможно, я завершил это решение, но мне не хватало переноса массива для правильного сравнения, поэтому я отказался от этого маршрута.

person nbayly    schedule 07.01.2015