☰

Отображение значений в электронной таблице на срок до X дней от текущей даты

tag-icon • tag-icon worksheet-function spreadsheet libreoffice-calc openoffice-calc
Отображение значений в электронной таблице на срок до X дней от текущей даты

Я ищу помощи в распутывании путаницы, которая представляет собой ввод данных и сводную таблицу, которая у меня была последние несколько лет в LibreOffice Calc. Общая структура соответствующих частей таблицы выглядит следующим образом:

Лист: «Ввод данных»

|A    |B    |...  |L    |
|-----|-----|-----|-----|
|Date |Name |...  |Value|

Лист: "Резюме"

|A    |...  |E           |G                       |
|-----|-----|------------|------------------------|
|Name |...  |Total Values|Values from last 90 days|

На данный момент все работает, но я хочу добавить новую функцию в таблицу «Сводка», которая находится в столбце G. Столбец E запускает сложную формулу для поиска в таблице «Ввод данных» каждой строки, где ячейки в столбце «Сводка» A соответствуют ячейкам в столбце «Ввод данных» B. Вот эта формула:

=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A1)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)))

Это совершенно запутанная путаница, я знаю. Я попытаюсь упростить формулу ниже:

=SUMPRODUCT((B1:B100=A1)*ISNUMBER(L1:L100),(L1:L100))

Мне было интересно, как можно изменить эту формулу для столбца «Сводка» G, чтобы поиск ограничивался только последними 90 днями.

Пример того, что я должен увидеть:

Лист: «Ввод данных»

|A       |B    |...|L|
|--------|-----|---|-|
|19-08-13|Name1|...|2|
|19-07-25|Name2|...|1|
|19-01-01|Name1|...|3|

Лист: "Резюме"

|A    |...|E|G|
|-----|---|-|-|
|Name1|...|5|2|
|Name2|...|1|1|

EDIT: Следующий код выдает мне ошибку 502:

=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A6)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91))

решение1

Вопрос описывает две проблемы. Одна из них заключается в попытке отладки длинной формулы. Обычно ключ к этому — разбить формулу на ее составные части и проверить каждую часть, чтобы увидеть, что она делает. Начните с сохранения логических фрагментов нетронутыми (т. е. нескольких выражений, которые работают вместе), чтобы определить, какой раздел формулы не работает. Если ошибка не очевидна из этого, разбейте раздел на его составные части.

Сделайте это, скопировав и вставив каждую часть, а затем отрегулируйте вставленную часть, чтобы сделать ее отдельной формулой (например, добавьте знак равенства). В случае, если проблема заключается в несбалансированных скобках, скопируйте все выражение, содержащее все скобки, затем удалите другие внутренние выражения и их скобки, которые будет легче обнаружить; несбалансированные скобки в том, что осталось, могут стать очевидными только из этого упражнения. Копирование и вставка гарантируют, что проблема будет включена в то, что вы тестируете; повторный ввод формулы может исправить ошибки, и все работает, когда вы тестируете, поэтому это не служит диагностической цели.

Другая проблема заключалась в ограничении результатов последними 90 днями. Это можно сделать аналогично тому, как формула уже ограничивает результаты совпадающим именем — добавьте проверку в список выражений в SUMPRODUCT. Чтобы не усложнять, я опущу сложность использования INDIRECT для построения диапазонов и просто покажу фиксированные диапазоны. В контексте дополнительный аргумент может выглядеть так:

=SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )

(L1:L100) просто показывает местоположение в упрощенном примере в вопросе.

Даты хранятся как количество дней, поэтому значения указываются в единицах дней. Вы можете напрямую добавлять или вычитать дни. Выражение >TODAY()-91проверяет даты, которые старше сегодняшней не более чем на 90 дней. Это предполагает, что ваши данные не могут содержать будущие даты, поскольку они будут включены, если вы не расширите формулу, чтобы ограничить и это. Все выражение представляет собой логическую проверку, которая возвращает либо ИСТИНА ( 1), либо ЛОЖЬ ( 0). СУММПРОИЗВ умножает остаток результата массива на эти значения, в результате чего получается либо ноль, либо результат других аргументов в СУММПРОИЗВ.

Связанный контент