過去数年間、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|
編集: 次の場合、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
この質問は 2 つの問題を説明しています。1 つは、長い数式をデバッグすることです。一般的に、その鍵は、数式を構成要素に分解し、各部分をテストして動作を確認することです。論理的なチャンク (つまり、連携して動作する複数の式) をそのままにして、数式のどのセクションが動作していないかを特定することから始めます。そこからエラーが明らかでない場合は、セクションを構成要素に分解します。
各部分をコピーして貼り付け、貼り付けた部分を調整してスタンドアロンの数式にします (等号を追加するなど)。問題が不均衡な括弧である場合は、括弧をすべて含む式全体をコピーし、他の内部式とその括弧を削除します。これにより、見つけやすくなります。この演習だけで、残った括弧の不均衡が明らかになる場合があります。コピーして貼り付けると、問題がテスト対象に含まれるようになります。数式を再入力すると間違いが修正され、テスト時にすべてが機能するため、診断目的には役立ちません。
もう 1 つの問題は、結果を過去 90 日間に制限することでした。これは、数式が既に結果を一致する名前に制限している方法と同様に実行できます。つまり、SUMPRODUCT の式のリストにテストを追加します。簡単にするために、範囲を構築するために INDIRECT を使用する複雑さは省略し、固定範囲のみを表示します。コンテキストでは、追加の引数は次のようになります。
=SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )
(L1:L100) は、質問の簡略化された例で位置を示すためだけのものです。
日付は日数として保存されるため、値は日数単位です。日数を直接加算または減算できます。式は、>TODAY()-91今日より 90 日以内の日付をテストします。これは、データに将来の日付が含まれていないことを前提としています。数式を拡張して将来の日付も制限しない場合は、将来の日付も含まれるためです。式全体は、TRUE ( 1) または FALSE ( 0) を返す論理テストです。SUMPRODUCT は、配列結果の残りにそれらの値を乗算し、ゼロまたは SUMPRODUCT の他の引数の結果を返します。