自動化されたスプレッドシートを作成するプロジェクトがあります。最初のシートには、ユーザーが手動で入力したデータが含まれます。たとえば、ユーザーが XX/XX/XX の日付に受け取ったものの X 金額を入力するとします (Excel でテキストを列に入力し、区切り記号を付け、タブとスペースをクリックして終了します)。
11/19/2015 11/20/2015 11/25/2015 11/30/15 1/2/2016
Apples 4 5 2 7 1
Bananas 6 4 5 7 3
Oranges 3 0 4 0 0
さて、これはすべてユーザーが入力したデータです。このユーザーが入力したデータから、週の日付 (週を定義する土曜日の日付) とその週に受け取ったすべてのものの合計を表示するために 2 番目のシートが必要です。ユーザーが入力したデータからシート 2 に表示する必要があるのは次の内容です。
11/21/2015 11/28/2015 1/2/2016
Total 22 11 4
Apples 9 2 1
Bananas 10 5 3
Oranges 3 4 0
お気づきかもしれませんが、2015 年 11 月 19 日と 2015 年 11 月 20 日は 2015 年 11 月 21 日の週に該当します。そのため、2015 年 11 月 21 日は 1 つのセルにのみ表示され、その週のすべてのものが合計されます。ユーザーが入力する日付はいつでも離れている可能性があるため、2016 年 1 月 2 日を含めました。
これで、合計の計算式を簡単に理解できるはずですが、週の日付が 1 回しか表示されないという問題が発生しています。発生する可能性のあることの 1 つは、ユーザーが誤って同じ日付を 2 回入力することです。次の式を使用してこれを確認しました。
=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",__________)
最初の週の日付セルは、ユーザーが最初に入力した日付に応答するので簡単です。次のようになります。
=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",B1-weekday(B1)+7)
したがって、問題は次の日付が再び 11/21/2015 と表示されないようにすることです。
アップデート Google で検索してみたところ、私の問題は基本的に、指定された数値より k 番目に大きい数値を見つけることと同じであることがわかりました。ありがたいことに、役立つ情報を提供してくれる Web サイトがたくさんありました。最初の週の日付に続く週の日付を表示するために私が思いついた式は次のとおりです。
=IF(IFERROR(MODE.SNGL(1:1)>=1,0),"CHECK DATES",IFERROR(LARGE(1:1,COUNTIF(1:1,">"&B17))-WEEKDAY(LARGE(1:1,COUNTIF(1:1,">"&B17)))+7,""))
この数式では、最後の週の日付に続くセルに "" を表示することもできます。必要に応じて、#N/A 値を表示するように変更できます。次に、適切な合計に進みます。
答え1
あなたの質問を「3 番目のセルから始まる水平配列の 7 番目のセルごとの値が欲しい」と解釈しています。
その場合は、セル A2 に次の数式を使用して、行 2 に入力します。
=INDEX($A1:$J1,1,7*(B1-$A1)-4)
次のことを確認してください:
A: 入力データが Excel によってテキストではなく日付として認識されていることを確認します。正常に機能させるには、入力した日付をローカル形式 (例: dd/mm/yy) に変更する必要がある場合があります。B
: $A1:$J1 の範囲をデータ範囲の全幅まで拡大します。