我正在尋求一些幫助來解決我過去幾年在 LibreOffice Calc 中遇到的資料輸入和匯總電子表格的混亂問題。電子表格相關部分的整體佈局如下：
表：“數據輸入”
|A |B |... |L |
|-----|-----|-----|-----|
|Date |Name |... |Value|
表：“摘要”
|A |... |E |G |
|-----|-----|------------|------------------------|
|Name |... |Total Values|Values from last 90 days|
目前，一切正常，但我希望在摘要表（即G 列）中新增一個新函數。一行輸入欄 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|
編輯：以下給出了 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 天的日期。這假設您的資料不能包含未來日期，因為如果您不擴展公式來限制這些日期，那麼這些日期也會被包含在內。整個表達式是一個邏輯測試，傳回 TRUE ( 1) 或 FALSE ( 0)。 SUMPRODUCT 將陣列結果的其餘部分乘以這些值，得到零或 SUMPRODUCT 中其他參數的結果。