EXCEL 表の分類化について

質問

下記のような　トラブル報告書がございます。
セルA～F列には各項目が記載されています。

セルA　日付
セルB　階数
セルC　場所1
セルD　場所2
セルE　時間
セルF　状況

日付 　　　階数 　　場所1 　　　　　　　　　　　　場所2 　　　　　時間 　　状況
4月1日(火)　　1F　 従業員用男子トイレ (個室右から1) 17:00 嘔吐物有り 　
4月1日(火)　　B2F 客用駐車場トイレ 　　　　　　(洗面台上) 18:00ペーパータオルの紛失
4月1日(火)　　B3F 従業員用男子トイレ (トイレ内上) 19:00 電球切れ
4月1日(火)　　4F 従業員用男子トイレ (個室右から1) 20:00 排泄物有り
4月1日(火)　　5F 客用駐車場トイレ 　　　　　　(洗面台上) 21:00 蛍光灯切れ
4月1日(火)　　6F 客用男子トイレ 　　　　(トイレ内上) 　　　　　　22:00 USBメモリの拾得
4月1日(火)　　8F 従業員用男子トイレ (大便器) 　　　　　　23:00ガム付着の清掃依頼有り
4月1日(火)　　9F 従業員用男子トイレ (大便器) 　　　　　　 0:00 配管より漏水有り 　　4月1日(火)　　8F 駐車場 　　　　　　　　(101ロット) 　　　　　　 0:00 ミラーの歪み有り

セルF「状況」の項目にあるトラブル報告を幾つかのカテゴリに分けて、
表示させたいと考えております。

カテゴリ　　　　　セルFの項目　　　　　　　文字列の検索1(セルF)　　　　　　　　　　　　　　　　　文字列の検索2(セルC)
1.「電球切れ」　電球切れ・蛍光灯切れ　　　　　*切れ　　　　　　　　　　　　　　　　　　　　　　　　　なし
2.「嘔吐・排泄物」　嘔吐物有り・排泄物有り　　*物有り　　　　　　　　　　　　　　　　　　　　　　　　なし
3.「清掃依頼」　　　ガム付着の清掃依頼有り　　*清掃依頼有り　　　　　　　　　　　　　　　　　　　　　なし
4.「拾得物有り」　　財布の拾得　　　　　　　　*の拾得　　　　　　　　　　　　　　　　　　　　　　　なし
5.「遺失物」　　　　ペーパータオルの紛失　　　*の紛失　　　　　　　　　　　　　　　　　　　　　　　なし
6.「衛生器具故障」　配管より漏水有り　　　　　*不具合有り・*脱落有り・*ぐら付き有り・*漏水有り　　　*トイレ
7.「その他」　　　　ミラーの歪み有り　　　　　なし　　　　　　　　　　　　　　　　　　　　　　　　　 なし

カテゴリ1「電球切れ」はトラブル状況(セルF)の電球切れと蛍光灯切れに該当します。
トラブル状況に「何々切れ」と記入されたら、カテゴリ1になります。

カテゴリ3の「清掃依頼」も同様に、ガム付着の・・または、ジュース付着の、あるいは散乱物の、など様々な清掃依頼に対応する
ために、語尾の検索文字列を「清掃依頼有り」としています。

カテゴリ6の「衛生器具故障」は、故障状態が幾つか分かれるため、「何々の不具合有り、脱落有り、ぐら付き有り、漏水有り」など
の文字列を条件に該当します。
さらに「衛生器具故障」は場所がトイレのみに該当しておりますので、セルCの「場所1」の中に「何々トイレ」と記入される
ことを２つめの該当の条件としています。

カテゴリ7の「その他」は該当する条件が有りません。カテゴリ1～6に該当しないものが7となります。

上記の条件から各報告を分類化するためにはどうすれば良いか方法をアドバイスをよろしくお願いします。

回答No.3

bunjii · ベストアンサー

>↓このように分類分けして一括表示させたい・・・
回答No.2のH3からN3の数式で"✔"をROW()に置き換えるとカテゴリに適合するトラブルの行を取り出せます。
H列からN列を作業用に使えば目的の分類ができます。
作業用のテーブルを使わない方法もありますが数式が長大になるのでお勧めできません。

カテゴリ１
=IF(COUNTIF(Sheet1!$H$3:$H$11,">0")>=ROWS(Sheet1!F$3:F3),INDEX(Sheet1!A$1:A$11,SMALL(Sheet1!$H$3:$H$11,ROWS(Sheet1!F$3:F3)),1),"")
カテゴリ２
=IF(COUNTIF(Sheet1!$I$3:$I$11,">0")>=ROWS(Sheet1!F$3:F3),INDEX(Sheet1!A$1:A$11,SMALL(Sheet1!$I$3:$I$11,ROWS(Sheet1!F$3:F3)),1),"")
　　　・
　　　・
　　　・
カテゴリ６
=IF(COUNTIF(Sheet1!$M$3:$M$11,">0")>=ROWS(Sheet1!F$3:F3),INDEX(Sheet1!A$1:A$11,SMALL(Sheet1!$M$3:$M$11,ROWS(Sheet1!F$3:F3)),1),"")
カテゴリ７
=IF(COUNTIF(Sheet1!$N$3:$N$11,">0")>=ROWS(Sheet1!F$3:F3),INDEX(Sheet1!A$1:A$11,SMALL(Sheet1!$N$3:$N$11,ROWS(Sheet1!F$3:F3)),1),"")
各カテゴリのA列に上記数式をセットして右にF列まで、下へ必要数をコピーします。

貼付画像はExcel 2013で検証した結果です。

回答No.5

tom04

たびたびごめんなさい。
No.4の投稿で

＞No.2です。
＞No.3さんの補足を読ませていただきました。
は
＞No.1です。
＞No.2さんの補足を読ませていただきました。

の間違いです。失礼しました。m（_ _）m

回答No.4

tom04

No.2です。

No.3さんの補足を読ませていただきました。
今回もVBAになってしまいます。
元データがSheet1にあり、Sheet2に表示するようにしてみました。
前回アップした画像通りの配置で、Sheet2の1行目項目は入力済みだとします。

今回は
Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample2() 'この行から
Dim i As Long, k As Long, n As Long, lastRow1 As Long, lastRow2 As Long, wS As Worksheet
Dim myFlg1 As Boolean, myFlg2 As Boolean, myArray, myArray1, myArray2
Set wS = Worksheets("Sheet2") '←Sheet2は実際のSheet名に！
myArray = Array(".「電球切れ」", ".「嘔吐・排泄物」", ".「清掃依頼」", _
".「拾得物」", ".「遺失物」", ".「衛生器具故障」", ".「その他」")
myArray1 = Array("切れ", "物有り", "清掃依頼有り", "の拾得", "の紛失")
myArray2 = Array("不具合有り", "脱落有り", "ぐら付き有り", "漏水有り")
With Worksheets("Sheet1") '←Sheet1は実際のSheet名に！
Application.ScreenUpdating = False
lastRow1 = .Cells(Rows.Count, "A").End(xlUp).Row
lastRow2 = wS.Cells(Rows.Count, "A").End(xlUp).Row
If lastRow2 > 1 Then
Range(wS.Cells(2, "A"), wS.Cells(lastRow2, "F")).ClearContents
End If
For i = 2 To lastRow1
myFlg1 = False
myFlg2 = False
For k = 0 To UBound(myArray1)
If InStr(.Cells(i, "F"), myArray1(k)) > 0 Then
myFlg1 = True
.Cells(i, "G") = k + 1
Exit For
End If
Next k
If myFlg1 = False Then
For n = 0 To UBound(myArray2)
If InStr(.Cells(i, "F"), myArray2(n)) > 0 Then
myFlg2 = True
.Cells(i, "G") = 6
Exit For
End If
Next n
End If
If myFlg1 = False And myFlg2 = False Then
.Cells(i, "G") = 7
End If
Next i
For i = 1 To 7
wS.Cells(Rows.Count, "A").End(xlUp).Offset(1) = i & myArray(i - 1)
.Range("A1").AutoFilter field:=7, Criteria1:=i
If .Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
Range(.Cells(2, "A"), .Cells(lastRow1, "F")).SpecialCells(xlCellTypeVisible).Copy wS.Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
Next i
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　前回書いたように関数でないので、データ変更があるたびにマクロを実行する必要があります。
※　今回もSheet1のG列を作業用の列で使用していますので、
作業列が目障りであればG列を非表示にしておいてください。m（_ _）m

回答No.2

bunjii

>上記の条件から各報告を分類化するためにはどうすれば良いか方法をアドバイスをよろしくお願いします。
どのような結果が欲しいかを提示されないとアドバイスが困難です。
勝手解釈でチェック表を作成してみました。

貼付画像のH3からN3には次のような数式をセットしました。
H3=IF(ISERR(FIND("切れ",F3)),"","✔")
I3=IF(ISERR(FIND("物有り",F3)),"","✔")
J3=IF(ISERR(FIND("清掃依頼有り",F3)),"","✔")
K3=IF(ISERR(FIND("の拾得",F3)),"","✔")
L3=IF(ISERR(FIND("の紛失",F3)),"","✔")
M3=IF(OR((ISERR(FIND("トイレ",C3))),AND((ISERR(FIND("不具合",F3))),(ISERR(FIND("脱落",F3))),(ISERR(FIND("ぐら付き",F3))),(ISERR(FIND("漏水",F3))))),"","✔")
N3=IF(COUNTIF(H3:M3,"✔"),"","✔")
H3からN3を選択して、下へ必要数オートフィルでコピーしました。

検証はExcel 2013で行いましてがすべてのバージョンで同様な結果を得られると思います。
数式中の検索文字列を各カテゴリに設定すればセル対象に変更できます。

お礼

アドバイスいただき誠にありがとうございました。
質問の詳細をはっきりと伝えず申し訳ございません。
「トラブル報告を幾つかのカテゴリに分けて表示させたい」について、
下記の報告書を別のシートにカテゴリ別に分類させたいというものです。

日付 　　　　階数 　　場所1 　　　　　場所2 　　　　　時間 　　状況
4月1日(火) 1F 従業員用男子トイレ (個室右から1) 17:00 嘔吐物有り 　

4月1日(火) B2F 客用駐車場トイレ (洗面台上) 18:00 ペーパータオルの紛失

4月1日(火) B3F 従業員用男子トイレ (トイレ内上) 19:00 電球切れ

4月1日(火) 4F 従業員用男子トイレ (個室右から1) 20:00 排泄物有り

4月1日(火) 5F 客用駐車場トイレ (洗面台上) 21:00 蛍光灯切れ

4月1日(火) 6F 客用男子トイレ 　　　　(トイレ内上) 22:00 USBメモリの拾得

4月1日(火) 8F 従業員用男子トイレ (大便器) 23:00 ガム付着の清掃依頼有り

4月1日(火) 9F 従業員用男子トイレ (大便器) 0:00 配管より漏水有り

4月1日(火) 8F 駐車場 　　　　　　　　(101ロット) 0:00 ミラーの歪み有り

↓このように分類分けして一括表示させたい・・・

日付 　　　　階数 　　場所1 　　　　　場所2 　　　　　時間 　　状況
1.「電球切れ」
4月1日(火) B3F 従業員用男子トイレ (トイレ内上) 19:00 電球切れ
4月1日(火) 5F 客用駐車場トイレ (洗面台上) 21:00 蛍光灯切れ

2.「嘔吐・排泄物」
4月1日(火) 1F 従業員用男子トイレ (個室右から1) 17:00 嘔吐物有り
4月1日(火) 4F 従業員用男子トイレ (個室右から1) 20:00 排泄物有り

3.「清掃依頼」
4月1日(火) 8F 従業員用男子トイレ (大便器) 23:00 ガム付着の清掃依頼有り

4.「拾得物」
4月1日(火) 6F 客用男子トイレ 　　　　(トイレ内上) 22:00 USBメモリの拾得

5.「遺失物」
4月1日(火) B2F 客用駐車場トイレ (洗面台上) 18:00 ペーパータオルの紛失

6.「衛生器具故障」
4月1日(火) 9F 従業員用男子トイレ (大便器) 0:00 配管より漏水有り

7.「その他」
4月1日(火) 8F 駐車場 　　　　　　　　(101ロット) 0:00 ミラーの歪み有り

　以上宜しくお願いします。

回答No.1

tom04

こんばんは！
お望みの方法とは異なると思いますが、一案です。

とりあえずG列に「カテゴリ」の分類を表示してみてはどうでしょうか？
↓の画像のようになれば後はG列のオートフィルタで簡単に別Sheetにコピー＆ペーストはできると思います。
VBAになってしまいますが、やってみました。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, n As Long, lastRow As Long
Dim myFlg1 As Boolean, myFlg2 As Boolean, myArray1, myArray2
myArray1 = Array("切れ", "物有り", "清掃依頼有り", "の拾得", "の紛失")
myArray2 = Array("不具合有り", "脱落有り", "ぐら付き有り", "漏水有り")
lastRow = Cells(Rows.Count, "G").End(xlUp).Row
If lastRow > 1 Then
Range(Cells(2, "G"), Cells(lastRow, "G")).ClearContents
End If
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
myFlg1 = False
myFlg2 = False
For k = 0 To UBound(myArray1)
If InStr(Cells(i, "F"), myArray1(k)) > 0 Then
myFlg1 = True
Cells(i, "G") = k + 1
Exit For
End If
Next k
If myFlg1 = False Then
For n = 0 To UBound(myArray2)
If InStr(Cells(i, "F"), myArray2(n)) > 0 Then
myFlg2 = True
Cells(i, "G") = 6
Exit For
End If
Next n
End If
If myFlg1 = False And myFlg2 = False Then
Cells(i, "G") = 7
End If
Next i
End Sub 'この行まで

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。
※　例えば検索文字の「の拾得」は「拾得」とか「清掃依頼有り」は「依頼」だけで大丈夫だと思いますが、
質問通りの文字列を含むという条件でカテゴリ分けしています。

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