Excelの関数について

質問

Excelの関数に教えてください。

シートA
工場名　地域　従業員数　金額の列があります。

シートBに地域（３地域しかありません）に該当する
工場のカウントを集計します。

１地域につき、下記のカウントをしたいのです。
売上が500万以上で従業員数1000人以上
売上が～500万未満で従業員数1000人以上
売上が1～100万未満で従業員数1000人以上

売上が500万以上で従業員数300～999人
売上が～500万未満で従業員数300～999人
売上が1～100万未満で従業員数300～999人

売上が500万以上で従業員数300人未満
売上が～500万未満で従業員数300人未満
売上が1～100万未満で従業員数300人未満

INDIRECTやCOUNTIFを使えばできるような気がするの
ですが、うまくできません。

分かる方教えてください！

回答No.2

mshr1962 · ベストアンサー

シートＡの１行目は項目ですよね。データの範囲を選択してから
「挿入」「名前」「作成」で「上端列」にチェックしてＯＫしてください。
項目名で計算できるようになります。
その上で
=SUMPRODUCT((地域="地域")*(従業員数>=1000)*(金額>=500))
=SUMPRODUCT((地域="地域")*(従業員数>=1000)*(金額>=100)*(金額<500))
=SUMPRODUCT((地域="地域")*(従業員数>=1000)*(金額<100))

=SUMPRODUCT((地域="地域")*(従業員数>=300)*(従業員数<1000)*(金額>=500))
=SUMPRODUCT((地域="地域")*(従業員数>=300)*(従業員数<1000)*(金額>=100)*(金額<500))
=SUMPRODUCT((地域="地域")*(従業員数>=300)*(従業員数<1000)*(金額<100))

=SUMPRODUCT((地域="地域")*(従業員数<300)*(金額>=500))
=SUMPRODUCT((地域="地域")*(従業員数<300)*(金額>=100)*(金額<500))
=SUMPRODUCT((地域="地域")*(従業員数<300)*(金額<100))

※"地域"は先頭の行に入力してセル参照してください。

補足

お返事ありがとうございます。
やってみたのですが、うまくいきません。
この式でシートAを参照していることになりますか？
（SUMPRODUCT初めて使いました）

回答No.6

imogasi

配列数式の利用回答ですが
（＃４のご回答と同じ方式の回答を上げる理由は後記）
例データ　Ａ１：Ｄ１０
工場名 地域 従業員数 売上金額
Ａ Ｘ 1356 1100
Ａ Ｘ 400 2000
Ｂ Ｙ 100 500
Ｃ Ｚ 1000 600
Ａ Ｙ 600 250
Ｄ Ｘ 301 600
Ｂ Ｘ 134 1700
Ｃ Ｘ 1200 1800
Ｄ Ｚ 1300 900
上記以外の範囲のセルで、答えを求めたいセルに
=SUM(IF((C2:C100>1000)*(D2:D100>1000),1,0))
ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲを同時に押す。
結果　２
なお「＊｝はＡＮＤ条件にするとき使います。
－－－－
＞Aシートはデータが増えていくので（＃１の補足の一部から）
データは第１０行までしかないのに、１００行まで指定してます。
データは追加しても、その１００行までの限度では、式を変えなくても良いです。
配列数式の回答が出ているのに、上げたのは、式を簡単にできないか考えて、やってみて、その理由からです。
－－－
他の条件も（Ａ）５００や１０００の数字を変える
（Ｂ）以上未満で、＞、＜、＞＝で適当なものに置き換えてください。
－－－－
「名前」の利用
式を判りやすくするために、＃２のお答えのように、配列数式でも、上記例Ｃ２：Ｃ１００などを名前定義で置き変えることもできます。
=SUM(IF((従業員数>1000)*(売上金額>1000),1,0))
ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲを同時に押す。
これも結果　２
http://www.excel7.com/chotto21-4.htm

お礼

お返事ありがとうございます。
配列数式を名前定義したあとにやってみると
分かりやすいですね。
ありがとうございました。

回答No.5

mshr1962

#2のmshr1962です。
＞この式でシートAを参照していることになりますか？
「挿入」「名前」「定義」で工場名、地域、従業員数、金額がそれぞれあるか？
範囲の上から下まで「=シートＡ!$A$2:$A$500」のようになっているか？
ご確認ください。
範囲がずれている場合は、「削除」で一度消してから#2の操作で再設定してください。
「工場名」が「シートＡ!$A$2:$A$500」と書くのと同じ設定になります。

お礼

再度お返事ありがとうございます。
範囲がずれていましたので、再設定で答えが
出ました。
ありがとうございました。

回答No.4

bdr

配列関数がよい気がします
シートＢのA1に地域名が入っているとして、シートＢのB3(売上が500万以上で従業員数1000人以上)に以下の数式をいれます
=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536>=1000,IF(シートＡ!$D$2:$D$65536>=5000000,1,0),0),0))
シフト+コントロールを押しながらエンターで確定します
同じように
B4:=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536>=1000,IF(シートＡ!$D$2:$D$65536>=1000000,IF(シートＡ!$D$2:$D$65536<5000000,1,0),0),0),0))
B5:=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536>=1000,IF(シートＡ!$D$2:$D$65536<1000000,1,0),0),0))
C3:=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536>=300,IF(シートＡ!$C$2:$C$65536<=999,IF(シートＡ!$D$2:$D$65536>=5000000,1,0),0),0),0))
C4:=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536>=300,IF(シートＡ!$C$2:$C$65536<=999,IF(シートＡ!$D$2:$D$65536>=1000000,IF(シートＡ!$D$2:$D$65536<5000000,1,0),0),0),0),0))
C5:=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536>=300,IF(シートＡ!$C$2:$C$65536<=999,IF(シートＡ!$D$2:$D$65536<1000000,1,0),0),0),0))
D3:=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536<300,IF(シートＡ!$D$2:$D$65536>=5000000,1,0),0),0))
D4:=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536<300,IF(シートＡ!$D$2:$D$65536>=1000000,IF(シートＡ!$D$2:$D$65536<5000000,1,0),0),0),0))
D5:=SUM(IF(シートＡ!$B$2:$B$65536=Sheet2!$A$1,IF(シートＡ!$C$2:$C$65536<300,IF(シートＡ!$D$2:$D$65536<1000000,1,0),0),0))

必ずShift+Ctrl+Enterで数式を確定してください

お礼

お返事ありがとうございます。
配列関数、難しいですね。
がんばってみます。

回答No.3

pbforce

> シートA
> 工場名　地域　従業員数　金額の列

の隣の列を使って良いのでしたらこんな方法があります。
工場名→A列～金額→D列とし、地域名がエリアA、B、Cとしておきます。
E列に
=If(C2>=5000000,"高",if(C2<1000000,"低","中"))
F列に
=If(D2>=1000,"多",If(D2<300,"少","中"))
G列に
=B2 & E2 & F2

として下さい。

G列に対してCountIf関数を使用すれば目的の工場の数が求められます。

シートBのセルB2には
=COUNTIF(SeetA!G:G,A1 & "高多")
と入力すれば良いと思います。

お礼

お返事ありがとうございます。
別の列を使う場合は、分かりやすいですね。
残念ながら余計な列は増やすことができないので
別の機会に使わさせていただきます。

回答No.1

tgn1013

従業員数の後ろの列にA/B/Cのランク付けをIf文で行い
同じく売上げの後ろの列で3ランクに分け
ピボットテーブルで抜くのが一番簡単で早いと思うのですが・・・。

関数でも出来なかないですけど、面倒で汎用性が・・・
ランク付けも、別表で人数範囲を指定してあげれば、
別表変更だけで自由に変更が出来ますし・・・・

もう少し詳しく書いた方が良いですか？

補足

お返事ありがとうございます。
Excelシートに表がすでに作成してありますので、
ピボットテーブルは使えないんです。涙

Aシートはデータが増えていくので、関数の方が
いい感じです。

こんか感じの表です。
　　A　　　　　B　　　　　　C　　　　　D
地域名
　　　　　　1000人以上　300～999人　300人未満
500万以上
100万～500万未満
1～100万未満