Excelのカウントについて（複数条件）

質問

Excelのカウントについて質問です。

　　　　　A列　
1行目　1-2-3　
2行目　2-3-1
3行目 4-5-1
4行目　3-2-1
5行目　1-3-2
6行目　( )

上記の表があり、A列6行目にA1～A5のカウントを表示したいと思います。
その際、1-2-3、2-3-1、3-1-2など、同じ数字でできているものであれば、順番が違っていても同じものとみなしてカウントしたいのです。
※1-2-3は文字列です。

COUNTIFを使って下記のようにすればカウントできなくはないのですが、もっと効率よく短縮して書けないかと思っています。

=COUNTIF(A1:A5,"1-2-3")+COUNTIF(A1:A5,"1-3-2")+COUNTIF(A1:A5,"2-1-3")+COUNTIF(A1:A5,"2-3-1")+COUNTIF(A1:A5,"3-1-2")+COUNTIF(A1:A5,"3-2-1")

簡潔にする方法がありましたらおしえてください。

回答No.2

prius770c · ベストアンサー

下の式が、数式的には一番シンプルかと・・・配列使っているのでシンプルと言っていいのかな？

=SUMPRODUCT(ISNUMBER(FIND(1,$A$1:$A$5))*(ISNUMBER(FIND(2,$A$1:$A$5)))*ISNUMBER(FIND(3,$A$1:$A$5)))

簡単に説明するとSUMPRODUCT関数は、1,2,3,4といった配列の積を計算する関数です。
また、trueを「１」としてfalseを「０」として認識するようにできています。

上記の式を簡単に説明するとしたの4ステップを踏んで計算しています。
(1)　「１」をあるかどうかをfind関数で調べる
(2)　　(1)の結果が数値であるかどうかをISNUMBERで調べる　
　　　ない場合は、(1)でエラーとなっていますのでfalseが返るので「０」あれば、trueなので「１」です。

(3)　(1)(2)の内容を「１」「２」「３」それぞれに対して行っていて、その結果をすべて掛けます。
　　すると、「１」「２」「３」のすべて含まれていれば「１×１×１=１」となります。
　　しかし、一つでも含まれていいなければどこかで「０」を掛けることになるので
　　結果は「０」となります。

(4)　(1)～(3)の内容を参照しているセルぞれぞれに行ってその結果を配列として扱いますので
　　結果的に「０」が「１」しか含まない配列が出来上がっています。
　　その配列を、積するので「１」の総数が出てきます。
　　提示いただいたケースだと「１、１、０、１、１」の配列が出来上がっていますので
　　「１＋１＋０＋１＋１＝４」となります。

上記の説明でイメージできなかったら、SUMPRODUCT関数の詳細な説明がされているHPなどを
参照されるといいと思います。

う～ん、配列を文書だけで説明しきる文章力がなくてすいません・・・ｗ

お礼

=SUM(COUNTIF(A1:A5,"1-2-3","1-3-2","2-1-3","2-3-1","3-1-2","3-2-1"}))
と、すっきりできたものの組合せを書かなきゃ行けないのが面倒でしたが、教えていただいた方法ですと簡単ですね。また、詳細な解説もありがとうございます。参考にさせていただきます。

回答No.5

KURUMITO

お示しの表が2行目から下方にあるとします。
B列は作業列でB2セルには次の式を入力して下方にオートフィルドラッグします。A6セルまでデータがあるとしたらB6セルまでオートフィルドラッグします。

=IF(SUMPRODUCT((ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),1,10)),A$2:A2))*ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),100,10)),A$2:A2))*ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),200,10)),A$2:A2))))=1,MAX(B$1:B1)+1,"")

A7セルにカウントを表示させるのでしたらA7セルには次の式を入力すればよいでしょう。

=MAX(B:B)

お礼

いろいろな方法がありますね。参考にさせていただきます。

回答No.4

kagakusuki

ANo.3の

(1)1-2-3と1-2-30の識別
(2)1-2-3と1-2-3-3の識別

も可能にする関数は次の様になります。

=SUMPRODUCT(ISNUMBER(FIND("-1-","-"&$A$1:$A$5&"-"))*ISNUMBER(FIND("-2-","-"&$A$1:$A$5&"-"))*ISNUMBER(FIND("-3-","-"&$A$1:$A$5&"-"))*(LEN($A$1:$A$5)-LEN(SUBSTITUTE($A$1:$A$5,"-",))=2))

お礼

追記ありがとうございます。複数桁にも対応できるようにしていただき感謝です。

回答No.3

prius770c

No.２です。

追記ですが、先ほどの関数には次のような制約がありますのご注意を

(1)1-2-3と1-2-30の識別はできません。
(2)1-2-3と1-2-3―3の識別はできません。

そのため、使用する場合の前提として

(1)ハイフン以外の数値は一桁であること
(2)一桁の数値は、３つであること　※こちらは、先ほどの回答の(3)の部分を3回から変更すれば対応できます。

お礼

詳細にお知らせいただきありがとうございます。

回答No.1

tom04

こんばんは！
なかなかいい案が浮かばないので、VBAで無理矢理やってみました。
尚、余計なお世話かもしれませんがA列データが何行あっても対応できるようにしています。

画面左下にある操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Columns("B:F").Insert
Dim i As Long
i = Cells(Rows.Count, 1).End(xlUp).Row
If Not Cells(i, 1) Like "*-" Then
Cells(i, 1).ClearContents
End If
Range(Cells(1, 1), Cells(i, 1)).Copy
Cells(1, 2).Select
ActiveSheet.Paste
Columns(2).Select
Selection.TextToColumns Destination:=Cells(1, 2), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
"-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
Cells(i, 5) = WorksheetFunction.Min(Range(Cells(i, 2), Cells(i, 4))) & "-" & _
WorksheetFunction.Small(Range(Cells(i, 2), Cells(i, 4)), 2) & "-" & _
WorksheetFunction.Max(Range(Cells(i, 2), Cells(i, 4)))
Next i
For i = 1 To Cells(Rows.Count, 5).End(xlUp).Row
If WorksheetFunction.CountIf(Range(Cells(1, 5), Cells(i, 5)), Cells(i, 5)) = 1 Then
Cells(i, 6) = 1
End If
Next i
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
Selection = WorksheetFunction.Sum(Columns(6))
Columns("B:F").Delete
End Sub 'この行まで

一部マクロの記録をそのまま使っています。
尚、A列データ内の「-」は二つまでとしています。（それ以上ある場合はコードが変わってきます）

他に良い方法があればごめんなさいね。m(__)m

お礼

ありがとうございます。
VBAまでは考えてませんでしたが、こういった方法もあるのかと思いました。参考にさせていただきます。