Я сталкивался с этой потребностью несколько раз и могу только заключить, что должен быть более простой и масштабируемый способ сделать то, что я пытаюсь сделать здесь.
По сути, мне нужна трехмерная таблица, по которой я могу составить итоги ...
Даны две таблицы:
| 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
или что-то подобное.