エクセルでの条件式等による数値集計

質問

エクセルの条件式＆集計についての質問です。
A列に売上年月日　C～F列には1行目は仕入年月日、2行目以降は仕入額が入っています。C～F列は同じ年月日は発生しません。
A列が07/2/28で商品1の場合、A列と同じ年月日のD列までの仕入額合計”30”をG列に表示させたいのです。
G列にどのような計算式を入力すればよいのでしょうか？
マクロなどを使用せずに処理したいのですが…宜しくお願い致します。

A:売上日 B:品名 C:07/1/31 D:07/2/28　E:07/3/31 F:07/4/30 G:計
07/2/28 商品1　　　　10　　　　　20　　　　　　15　　　　　　　5　　　　30　
07/2/28 商品2　　　　60　　　　　90　　　　　　20　　　　　　　70　　　150
07/3/31 商品3　　　　20　　　　　50　　　　　　10　　　　　　　90　　　 80
07/4/30 商品4　　　　0　　　　　200　　　　　　10　　　　　　　80　　　290

回答No.7

ufuidotcom · ベストアンサー

offset関数は、基準に指定したセルから相対位置を行数、列数で指定して該当セル（又は該当セル範囲）を特定するために使います。（Excelのヘルプでキーワード検索でoffsetを参照してみてください。）
match関数は、指定したセル範囲から該当する指定データが何番目のセルにあるか検索する関数です。
OFFSET(C2,0,0,0,MATCH(A2,C$1:F$1,0))
ということになので、C2セルを基準にA2に入っている日付に該当するセルをC1～F1で探しています。D1が該当するセルなので2ということになります。
OFFSETの( )の中、前の2つの0はC2からのずれで、行と列（0なのでずれ無し）、3つ目の0は拾う行数、MATCH関数で得られる値は拾う列数ということになります。したがって、C2から2列分のセル＝C2:D2という範囲が得られます。
OFFSETの前でSUMしてますので、合計されるということになります。
こんなもんで理解できますか？

なお、OFFSETを使ったこの式では、同一商品名・同一売上日が複数（飛び飛びに）出てきた場合には、合計できません。

お礼

ありがとうございます。
大変分かりやすく丁寧にご説明頂き感謝申し上げます。

また、「OFFSETを使ったこの式では、同一商品名・同一売上日が複数（飛び飛びに）出てきた場合には、合計できません。」

こんな事まで分かってしまうのですね…驚きです！！

本当に有難うございました。

回答No.6

A88No8

No.2 です。
>ちなみに式中のROWは何の意味なのでしょうか？
　Ｑ＆Ａで色々な回答を見てきて、初めて自分で考えてみたんです(^^)

「ROW」は、行の英語です。
　エクセルでは、式のあるセルの行の順序数（１～）を返します。

　OFFSET関数では、引数に左から基準位置（セルアドレス）、行のシフト値、列のシフト値を指定するのを基本とする関数です。今回、行のシフト値を即値（１とか２とか）で入れてしまうと式をコピーするのが大変です。
　そこで、ROW関数を使いました。

>No.4の方の計算式はA88No.8さんの式を一部省いているのですが、計算結果は同じでした。
びっくりですね～。Excelウィザードさんですね。
２つ勉強になりました。
一つは、基準値を適切に選ぶと式が簡単になる（-1とか+1とか省ける）。もう一つは、私はアドレスを得ることに着目した、No.4の方は、範囲を得ることに着目した。この目の付け所と同じOFFSET関数の理解度の深さの違いです。
　ただ、=OFFSET($C2,,,,MATCH($A2,$C$1:$F$1,0))だと、"#VALUE!"エラーになってしまうし、ＨＥＬＰからもヒントないし、私のような素人には、想像すらできないです。

あと、MATCH関数の引数を「FALSE」にしてしまいましたが「0」が正しいですm(__;m

お礼

ありがとうございました。非常に勉強になりました。

私からすると全く分からない関数でしたが、多少理解できました。
感謝です。

回答No.5

ufuidotcom

No3の補足回答
＞実は仕入データは50列位、行は2000行位のデータ量になるかも知れませんが、SUMPRODUCTで対応できますでしょうか？
　行数には関係なく計算できます（よほどメモリが少なくなければ）。

＞品名のところも条件を付けていますがこれは何故でしょうか？
　品名の絞込み条件を付けなければ、売上日までの仕入れが全部合計されてしまいます。
(B$2:B$5=B2)で商品1の行に絞込みを行ない、
($C$1:$F$1<=A2)で仕入れ日2/28までの列（CとD）に絞込みを行なって、($C$2:$F$5)をかぶせることによって絞り込んだ行と列に対応したセルの値を取り出しているという構造です。

補足

ありがとうございます。勉強になりました。
専門家の方のようですのでもう1点ご教示頂けますでしょうか？
No.4の方にご教示頂いた下記計算式とSUMPRODUCTとの違いはどこにあるのでしょうか？OFFSETとMATCH関数は初めて知りましたので、式の構成が今一つ理解できません。
=SUM(OFFSET(C2,,,,MATCH(A2,C$1:F$1,0)))

回答No.4

noname#204879

G2: =SUM(OFFSET(C2,,,,MATCH(A2,C$1:F$1,0)))

補足

ありがとうございました。
こんな簡単な式でOKなのですね。勉強になりました。

No.3の方にも同じ質問をしましたが、後出しで申し訳ないのですが、実は仕入データは50列位、行は2000行位のデータ量になるかも知れませんが、この計算式で対応できますでしょうか？

回答No.3

ufuidotcom

行番号を項目名から順に1～5ということにして、
G2の式　=SUMPRODUCT((B$2:B$5=B2)*($C$1:$F$1<=A2)*($C$2:$F$5))
G3～G5の式はG2をコピペでOK
sumproduct関数を使った配列の計算で簡単に求めることが出来ます。

補足

ありがとうございました。
SUMPRODUCT使った事はありましたが、「($C$1:$F$1<=A2)」日付の数値が大なりイコールとういのがミソなのですね。
ちなみに後出しで申し訳ないのですが、実は仕入データは50列位、行は2000行位のデータ量になるかも知れませんが、SUMPRODUCTで対応できますでしょうか？
また、品名のところも条件を付けていますがこれは何故でしょうか？
後学のためにご教示ください。

回答No.2

A88No8

nao37330さん、こんにちは
無い知恵しぼって考えてみました。
=SUM($C2:OFFSET($A$1,ROW()-1,MATCH($A2,$C$1:$F$1,FALSE)+1))

補足

ありがとうございました。

大変勉強になりました。

No.4の方の計算式はA88No.8さんの式を一部省いているのですが、計算結果は同じでした。
ちなみに式中のROWは何の意味なのでしょうか？
後学のためにご教示頂けますでしょうか？

回答No.1

lindberg19

単純に
G2セルに　=C2+D2
G3セルに　=C3+D3
G4セルに　=C4+D4+E4
G5セルに　=C5+D5+E5+F5
と、計算式を入力すれば良いと思いますが・・・