【Excel2003】件数を抜き出す方法について

質問

【Excel2003】件数を抜き出す方法について

左表のようなデータがあります。
ここから右表を作りたいのです。

金額はSUMIFで良いと思うのですが、悩んでいるのは件数と多金額の日数です。
金額の0は件数として含めません。
プラスでもマイナスでも金額が入ったセル数を店別に出したいのです。

それと、「多金額の日数」はプラスでもマイナスでも300円以上の金額が入った日数を数えます。

ご教授お願いいたします。

回答No.3

tom04 · ベストアンサー

こんばんは！
一例です。
とりあえず1000行目までデータがあっても対応できる数式です。

H2セルは
=SUMPRODUCT(($C$2:$C$1000=G2)*($D$2:$D$1000<>0))

I2セルは
=SUMIF($C$2:$C$1000,G2,$D$2:$D$1000)

J2セルは
=SUMPRODUCT(($C$2:$C$1000=G2)*($D$2:$D$1000>=300))+SUMPRODUCT(($C$2:$C$1000=G2)*($D$2:$D$1000<=-300))

として、H2～J2セルを範囲指定しJ2セルのフィルハンドルで下へコピーではどうでしょうか？
尚、エラー処理はしていません。

以上、参考になれば良いのですが・・・m(__)m

お礼

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

スッキリしていて良いですね。
私にも解読できる計算式です。

また機会ありましたらよろしくお願いいたします。大変助かりました。

回答No.6

KURUMITO

回答Ｎｏ5です。
申し訳ありません。訂正します。Ｆ2セルには次の式を入力し下方にオートフィルドラッグしてください。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,IF(D2<>0,1,0)&"a"&D2&"b"&IF(ABS(D2)>=300,1,0),IF(D2=0,F1,LEFT(F1,FIND("a",F1)-1)+1&"a"&MID(F1,FIND("a",F1)+1,FIND("b",F1)-FIND("a",F1)-1)+D2&"b"&IF(ABS(D2)>=300,MID(F1,FIND("b",F1)+1,5)+1,MID(F1,FIND("b",F1)+1,5)))))

回答No.5

KURUMITO

今後店の数が増加しても自由に対応できる方法です。
作業列をＥおよびＦ列に設けてお求めの表はＧ列からＫ列に表示させることにします。
Ｅ２セルには次の式を入力します。

=IF(B2<>B3,MAX(E$1:E1)+1,"")

Ｆ2セルには次の式を入力します。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,1&"a"&D2&"b"&IF(ABS(D2)>=300,1,0),LEFT(F1,FIND("a",F1)-1)+1&"a"&MID(F1,FIND("a",F1)+1,FIND("b",F1)-FIND("a",F1)-1)+D2&"b"&IF(ABS(D2)>=300,MID(F1,FIND("b",F1)+1,5)+1,MID(F1,FIND("b",F1)+1,5))))

Ｅ2セルからＦ2セルまでを選んでそれらの式を下方にオートフィルドラッグします。

Ｇ1セルからＫ1セルには項目名をそれぞれ入力します。
Ｇ2セルには次の式を入力してＫ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF($E:$E,ROW(A1))=0,"",IF(COLUMN(A1)<=2,INDEX($B:$C,MATCH(ROW(A1),$E:$E,0),COLUMN(A1)),IF(COLUMN(A1)=3,LEFT(INDEX($F:$F,MATCH(ROW(A1),$E:$E,0)),FIND("a",INDEX($F:$F,MATCH(ROW(A1),$E:$E,0)))-1)*1,IF(COLUMN(A1)=4,MID(INDEX($F:$F,MATCH(ROW(A1),$E:$E,0)),FIND("a",INDEX($F:$F,MATCH(ROW(A1),$E:$E,0)))+1,FIND("b",INDEX($F:$F,MATCH(ROW(A1),$E:$E,0)))-FIND("a",INDEX($F:$F,MATCH(ROW(A1),$E:$E,0)))-1)*1,IF(COLUMN(A1)=5,MID(INDEX($F:$F,MATCH(ROW(A1),$E:$E,0)),FIND("b",INDEX($F:$F,MATCH(ROW(A1),$E:$E,0)))+1,5)*1,"")))))

式は多少複雑になりますが計算速度はSUMPRODUCT関数の場合などに比べて速いでしょう。また、店の増加にも対応しています。ＥおよびＦ列が目障りでしたら列を非表示にすればよいでしょう。

回答No.4

noname#204879

範囲 H2:J2 に次の[条件付き書式]を設定した後で、下記の数式を入力して、此れを下方にズズーッとドラッグ＆ペースト
　　　セルの値が　　0
　　　フォント色　　白
H2: =SUMPRODUCT((B$2:B$1000=F2)*(D$2:D$1000<>0))
I2: =SUMPRODUCT((B$2:B$1000=F2)*(D$2:D$1000))
J2: =SUMPRODUCT((B$2:B$1000=F2)*(ABS(D$2:D$1000)>=300))

回答No.2

aduken

こんにちは
COUNTIF関数ではだめでしょうか？
COUNTは範囲のセルの数字の入ったセル数を返す関数です。

=COUNTIF( 範囲 : 範囲 , 条件 )

で記述する事ができ、

>300

と書けば、300以上の判定ができます

回答No.1

mshr1962

J3=SUMPRODUCT(($B$2:$B$31=$F3)*(ABS($D$2:$D$31)>=300))
で出来ませんか？