01北海道 既存事業 既存製品 単独
04宮城 新規事業 新規製品 提携
11埼玉 新規事業 新規製品 提携
・
13東京 新規事業 新規製品 単独
・
上記のようなエクセルデータ(「01北海道」「既存事業」「既存製品」「単独」といった
列は、各々セルが分かれています)が、300行位あります。
この中で、例えば、
「埼玉」(00行A列)「新規事業」(00行B列)「新規製品」(00行C列)「単独」(00行C列)
といった条件、つまりパターンで、そのデータの個数を出したいです。
例えば、CountIFで
=COUNTIF(C3:G196,A00,B00,C00,D00)
とすると、エラーが出てしまいます。
そこで、カッコを追加すると、
=COUNTIF(C3:G196,(A00,B00,C00,D00))
とすると、「0」件となってしまいます。実際はあるのですが。
良い方法はございますでしょうか?
couuntif、でなくてもかまいません。業務で使用しているため、かなりあせっています。
よろしくお願いいたします。
例えば下記のように条件の為の表を作ります。
条件を指定するために必ず必要になります。
F | G | H | I | J | |
---|---|---|---|---|---|
1 | 埼玉 | 新規事業 | 新規製品 | 単独 | |
2 | 北海道 | 既存事業 | 既存製品 | 単独 | |
3 | 宮城 | 新規事業 | 新規製品 | 提携 | |
4 | 北海道 | 新規事業 | 新規製品 | 提携 | |
5 | 東京 | 新規事業 | 新規製品 | 単独 | |
6 | 神奈川 | 新規事業 | 新規製品 | 単独 | |
31 | 東京 | 新規事業 | 新規製品 | 単独 | |
32 | 山形 | 新規事業 | 新規製品 | 単独 |
そして、データ表をA~D列などに作り絶対参照にすればいいです。
上の例では、J1セルの数式を
=SUM(IF((A$1:A$8=F1)*(B$1:B$8=G1)*(C$1:C$8=H1)*(D$1:D$8=I1),1,0))
として、Ctrl+Shift+Enterとし、下にコピー。
(この部分はSUMPRODUCTを使っても同じです。)
こうすれば、条件の表とデータ表の2つになります。
下記のようなExcelシートを作成したとします。
A | B | C | D | |
1 | 埼玉 | 新規事業 | 新規製品 | 単独 |
---|---|---|---|---|
2 | 北海道 | 既存事業 | 既存製品 | 単独 |
3 | 宮城 | 新規事業 | 新規製品 | 提携 |
4 | 埼玉 | 新規事業 | 新規製品 | 提携 |
5 | 東京 | 新規事業 | 新規製品 | 単独 |
6 | 埼玉 | 新規事業 | 新規製品 | 単独 |
7 | 東京 | 新規事業 | 新規製品 | 単独 |
8 | 埼玉 | 新規事業 | 新規製品 | 単独 |
1行目のパターンに合致する件数を計算する式は、以下の通り。
=SUMPRODUCT(((A2:A8)=A1)*((B2:B8)=B1)*((C2:C8)=C1)*((D2:D8)=D1))
できました。本当にありがとうございました。
別の方法も紹介しておきます。
pahooさんと同じ条件で、
=SUM(IF((A2:A8=A1)*(B2:B8=B1)*(C2:C8=C1)*(D2:D8=D1),1,0))
と入力して、Ctrl+Shift+Enterで配列数式を使います。
ありがとうございます。この方法だと、1行目「埼玉」「新規事業」「新製品」「単独」を条件
として、以下に、一覧表をつけることになりますね。条件が、全部(県別、事業別など)で32個
ありまして、32個表を作る作業が必要になってしまいます。
もう少し、良い方法はございませんでしょうか?
ご多忙の折、恐縮ではございますが、ご教授いただきたく存じます。
例えば下記のように条件の為の表を作ります。
条件を指定するために必ず必要になります。
F | G | H | I | J | |
---|---|---|---|---|---|
1 | 埼玉 | 新規事業 | 新規製品 | 単独 | |
2 | 北海道 | 既存事業 | 既存製品 | 単独 | |
3 | 宮城 | 新規事業 | 新規製品 | 提携 | |
4 | 北海道 | 新規事業 | 新規製品 | 提携 | |
5 | 東京 | 新規事業 | 新規製品 | 単独 | |
6 | 神奈川 | 新規事業 | 新規製品 | 単独 | |
31 | 東京 | 新規事業 | 新規製品 | 単独 | |
32 | 山形 | 新規事業 | 新規製品 | 単独 |
そして、データ表をA~D列などに作り絶対参照にすればいいです。
上の例では、J1セルの数式を
=SUM(IF((A$1:A$8=F1)*(B$1:B$8=G1)*(C$1:C$8=H1)*(D$1:D$8=I1),1,0))
として、Ctrl+Shift+Enterとし、下にコピー。
(この部分はSUMPRODUCTを使っても同じです。)
こうすれば、条件の表とデータ表の2つになります。
ありがとうございます。とても分かりやすいです。
スマートじゃないんですが、表の中に条件をあげて行ったらどうでしょうか?
例えばAB列にB列の条件判定を書いていきます。(AB列使っていると思いますので実際はたら列をずらしてください。)
AB1 に検索文字を入れる 例:北海道
AB2 に=if(b2=Ab$1,1,0)
AB3 以降にAB2の式をコピー
これでAB2以降はB列が北海道だったら1、そうでなければ0です。
同様に他の条件も入れていって、最後に全体の判別用の列を用意して、各セルをかける式つくれば全ての条件に当てはまれば1そうでなければ0となります
=AA2*ABC2*AC2*AD2*・・・・
あとは、この列を足せば条件にあったデータが何件か分ります。
この方法だとセルに無駄なデータが入りますが、条件を変えるのにAB1セルのような1行目の検索条件を変えるだけでいろいろな条件を作れますし、見やすいです。
いろいろな方法があるのですね。本当に参考になります。本当にありがとうございます。
COUNTIFは複数の条件でのカウントができないので、ユーザー定義関数で行ごとに同じ行があればカウントする関数を考えてみます。
標準モジュールを追加して次のコードをコピペしてください。
Function COUNTIFROW(r1 As Range, r2 As Range) As Long Dim myColumn As Integer Dim myRow As Long Dim c As Long Dim f As Boolean If r1.Columns.Count <> r2.Columns.Count Then Exit Function If r2.Rows.Count <> 1 Then Exit Function For myRow = r1.Row To r1.Row + r1.Rows.Count - 1 f = True For myColumn = 1 To r2.Columns.Count If Cells(r2.Row, r2.Column + myColumn - 1).Value <> Cells(myRow, r1.Column + myColumn - 1).Value Then f = False Exit For End If Next myColumn If f Then c = c + 1 End If Next myRow COUNTIFROW = c End Function
この関数はの書式は、
COUNTIFROW(データ範囲、検索行範囲)
です。
例えば、pahooさんの回答のサンプルならば、
=COUNTIFROW(A2:D8,A1:D1)
となります。
この関数の利点は、条件項目が増えても数式が長くならないことです。
注)とうぜんデータ範囲と検索行範囲の列数が違う場合は0が返ります。また、検索行範囲が1でない場合も0が返ります。
わからないことがありましたら、コメント欄でお答えします。
こういう方法もあるのですね。参考になりました。ありがとうございました。
ありがとうございます。とても分かりやすいです。