Сложный поиск / запрос, должен быть более простой способ

Question

Я сталкивался с этой потребностью несколько раз и могу только заключить, что должен быть более простой и масштабируемый способ сделать то, что я пытаюсь сделать здесь.

По сути, мне нужна трехмерная таблица, по которой я могу составить итоги ...

Даны две таблицы: | People | One | Two | Three | Four | Five | Six | |--------|-----|-----|-------|------|------|-----| | John | x | x | | | | | | James | | x | x | | | | | Jim | | | x | x | | | | Jean | | | | x | x | | | Jammie | | | | | x | x | | Janis | x | | | | | x | и | Event | Data | |-------|------| | One | 1 | | Two | 2 | | Three | 3 | | Four | 4 | | Five | 5 | | Six | 6 |

Я генерирую итоги на основе непустых ячеек в 1-й таблице и соответствующих значений во второй таблице для получения: | Totals | |--------| | 3 | | 5 | | 7 | | 9 | | 11 | | 7 |

Я использую сложный vlookup для создания этого: =SUM( IF(NOT(ISBLANK(Table1[@One])),VLOOKUP(Table1[[#Headers],[One]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Two])),VLOOKUP(Table1[[#Headers],[Two]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Three])),VLOOKUP(Table1[[#Headers],[Three]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Four])),VLOOKUP(Table1[[#Headers],[Four]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Five])),VLOOKUP(Table1[[#Headers],[Five]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Six])),VLOOKUP(Table1[[#Headers],[Six]],Table2,2,FALSE),0) )

Очевидно, что это не масштабируется, и это оставляет много места для человеческой ошибки. На практике реальные приложения включают добавление или удаление пользователей и "события". Я чувствую, что я сталкивался с этим достаточно раз, что я не могу быть один и что я должен делать это трудным путем. Есть ли более простой вариант?

* Обратите внимание, что в этом примере я использую сумму, но в некоторых случаях у меня может быть текст, который я хочу сделать TEXTJOIN или что-то подобное.

Answer 1

Эта формула массива, заполненная с H2 на скриншоте ниже, дает показанные результаты.

=SUM(NOT(ISBLANK($B2:$G2))*TRANSPOSE(B$10:B$15))

Поскольку это формула массива, ее необходимо вводить с помощью клавиши CTRL Shift Enter, а не просто Enter.

Как это работает: NOT(ISBLANK($B2:$G2)) выдает массив значений True/False со значением False везде, где ячейка в этом диапазоне пуста (см. Примечание ниже). Если вы выделите эту часть формулы в строке формул и нажмете клавишу F9 , вы увидите массив {TRUE,TRUE,FALSE,FALSE,FALSE,FALSE} . (Введите CTRL -Z, чтобы отменить это).

(B$10:B$15) - это просто список чисел в столбце данных, и TRANSPOSE() преобразует его из вертикального массива в горизонтальный.

В умножении значения True/False обрабатываются как 1 и 0 , поэтому в результате получается массив со значением Data, где бы ни был x , а в других местах ноль. Затем SUM() просто складывает массив и возвращает итоги по мере заполнения.

Редактировать:
OP добавил к своему вопросу, сказав, что он может иметь текстовые значения в B6:B10 и работать с ними не с помощью SUM() , а с другой функцией, возможно, TEXTJOIN() .

Чтобы обработать возможность нечисловых значений, это выражение:

IF(NOT(ISBLANK($B2:$G2)),TRANSPOSE(B$10:B$15),)

возвращает массив со значением Data, где бы ни было "x", и False другом месте. Он работает правильно как для текстовых, так и для числовых значений.

Включив значение value_if_false в IF() (после последней запятой), значения False можно заменить на 0 или blank или на то, что может потребоваться функции, действующей в массиве.

Заметки:

1. ISBLANK() должен действительно называться ISEMPTY() , потому что ячейка должна быть действительно пустой, чтобы ISBLANK() возвращала True . Если ячейка содержит формулу, но выглядит пустой, ISBLANK() вернет False . ISTEXT() имеет ту же проблему, поэтому, если у вас есть формулы в "пустых" ячейках, вы можете изменить первый массив на (($B2:$G2)="x")
2. Если бы TRANSPOSE() не было, умножение (и функция IF() ) дало бы двумерный массив, который бы запутался.

Я надеюсь, что это помогает и удачи.

Answer 2

Подумав немного об этом, мы нашли гораздо более простое решение с использованием SUMIF() . Но ваш второй стол должен быть расположен горизонтально.

=SUMIF(B2:G2,"x",B$10:G$10)