入力リストから分類別で自動転記

質問

分類　名称　・・
A　●●
B　×
A　△
C　■
・・
・・

上記入力シート１の分類ABCDEを別シート２で
A　　B　　C　　D　　E
●●　 × 　　■
△
・・
と自動で振り分けする関数を探しています。
シート１は随時追加されるので自動で振り分けたいです。
マクロでもOK（最終登録ボタンを設定すれば良いので）

色々と調べましたが、どれも不具合が生じて先に進みません。
ご教授いただけますようよろしくお願いします。

回答No.11

ushi2015

こんにちは
No1、No9、No10です。
ソートされてないとダメだったので、
Sheet2のセルA2の数式を、
=IF(COUNTIF(Sheet1!$A$2:$A$10000,A$1)<ROW(A1),"",INDEX(Sheet1!$B$2:$B$10000,SMALL(IF(Sheet1!$A$2:$A$10000=A$1,ROW(Sheet1!$A$2:$A$10000)),ROW(A1))-1))
と入力して、Ctrl+Shift+Enterで確定して配列数式にして下さい。
あとは下方・右方にフィルコピーで。

回答No.10

ushi2015

こんにちは
No1、No9です。
済みません、出来てなかったですね。
無視して下さい。

回答No.9

ushi2015

こんにちは
No1です。
出来ませんでしたか？

回答No.8

kagakusuki

回答No.3、5、6です。

>マクロでもOK

というお話ですので、VBAのマクロの一例も回答しておきます。

Sub QNo9124944_入力リストから分類別で自動転記()

Const ItemRow = 1 '元データのシートにおいて項目名が入力されている行の行番号
Const ClassColumn = "A" '元データのシートにおいて分類名が入力されている列の列番号
Const NameColumn = "B" '元データのシートにおいて名称が入力されている列の列番号
Const ClassRow = 1 '出力先のシートにおいて分類名の出力先となる行の行番号
Const FirstColumn = "A" '出力先のシートにおいて結果の出力先となる最初の列の列番号
Dim temp As Variant, mySheetName(1, 1) As String, mySheet(2) As Worksheet _
, LastRow As Long, myCloumn As Long, i As Long, c As Range

mySheetName(0, 0) = "入力シート１" '元データのシートのシート名
mySheetName(1, 0) = "別シート２" '出力先のシートのシート名
mySheetName(0, 1) = "元データが入力されている"
mySheetName(1, 1) = "出力先の"

For i = 0 To 1
If IsError(Evaluate("ROW('" & mySheetName(i, 0) & "'!A1)")) Then
MsgBox mySheetName(i, 1) & "シートとして設定されている" _
& vbCrLf & vbCrLf & mySheetName(i, 0) & vbCrLf & vbCrLf & _
"というシート名のシートが見つかりません。" & vbCrLf _
& "マクロを終了します。", vbExclamation, "存在しないシート"
Exit Sub
Else
Set mySheet(i) = Sheets(mySheetName(i, 0))
End If
Next i

With mySheet(0)
LastRow = .Range(ClassColumn & Rows.Count).End(xlUp).row
If LastRow <= ItemRow Then
MsgBox "処理の対象となるデータがありません。" & vbCrLf _
& "マクロを終了します。", vbExclamation, "データ無し"
Exit Sub
End If

temp = Chr(29)
For Each c In .Range(ClassColumn & ItemRow + 1 & ":" & ClassColumn & LastRow)
If c.Value <> "" And Not temp Like "*" & Chr(29) & c.Value & Chr(29) & "*" Then
If Chr(29) & c.Value & Chr(29) > Mid(temp, 2, InStr(2, temp, Chr(29))) Then
temp = Chr(29) & c.Value & temp
Else
temp = temp & c.Value & Chr(29)
End If
End If
Next c
End With
temp = Split(Mid(temp, 2, Len(temp) - 2), Chr(29))

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With

With mySheet(1).Range(FirstColumn & ClassRow & ":" _
& mySheet(1).Cells.SpecialCells(xlCellTypeLastCell).Address)
.ClearContents
.Resize(1, UBound(temp) + 1) = temp
End With
For Each c In mySheet(0) _
.Range(NameColumn & ItemRow + 1 & ":" & NameColumn & LastRow)
temp = mySheet(0).Range(ClassColumn & c.row).Value
If c.Value <> "" And temp <> "" Then
With mySheet(1)
myCloumn = Application.WorksheetFunction _
.Match(temp, .Range(ClassRow & ":" & ClassRow), 0)
.Cells(.Rows.Count, myCloumn).End(xlUp).Offset(1).Value = c.Value
End With
End If
Next c

With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With

End Sub

回答No.7

imogasi

#4です。
＃４で述べた、ＶＢＡでのオフライン・バッチ処理的な処理は
例データ
Ａ　Ｂ列
チーム 氏名
A 太田
A 篠原
A 木村
A 本山
A 小山
B 近藤
B 大野
B 板山
C 田中
C 栗田
C 砂川
Ｄ 木下
処理
標準モジュールに
Sub test01()
Worksheets("Sheet2").Cells.Clear
rl = Worksheets("Sheet1").Range("a10000").End(xlUp).Row
'－
Worksheets("Sheet1").Range("A2:B" & rl).Sort key1:=Rows(1)
t = Worksheets("Sheet1").Range("A2")
'--第１行目初期処理
j = 1
Worksheets("Sheet2").Cells(j, "A") = Worksheets("Sheet1").Range("A" & 2)
k = 1
Worksheets("Sheet2").Cells(k, "B") = Worksheets("Sheet1").Range("B" & 2)
'---第２行目以下
For i = 2 To rl
If Worksheets("Sheet1").Range("A" & i) = t Then
k = k + 1
Worksheets("Sheet2").Cells(k, j) = Worksheets("Sheet1").Range("B" & i)
Else
j = j + 1 '次列へ
Worksheets("Sheet2").Cells(1, j) = Worksheets("Sheet1").Range("A" & i)
k = 2
Worksheets("Sheet2").Cells(k, j) = Worksheets("Sheet1").Range("B" & i)
End If
t = Worksheets("Sheet1").Range("A" & i)
Next i
End Sub
ーーー
結果
A B C Ｄ
太田　 近藤　 田中　 木下
篠原 　大野 栗田
木村 　板山 砂川
本山
小山
ーー
表全体的に、その回の入力作業の完了のときクリックする、ボタンをつくって、押させて起動させるか、Ｓｈｅｅｔ２をアクティヴにしたときに自動実行はどうですか。
Private Sub Worksheet_Activate()　＜－－本件例ではＳｈｅｅｔ２のイベント
MsgBox "Sheet2がオープン"
'本コードを入れる
End Sub
個々のセルデータが変わったごとに実行するのは、処理時間的にもうっとうしい。
かといって、ＶＢＡで、１行データの入力完成や行挿入や削除の機会の察知も煩雑で、むつかしいので。

回答No.6

kagakusuki

>シート３の内容を別シート２へ作業セルとして登録してはダメですか？

　それでも構いませんが、別シート2のA2セルに入力する関数を、作業列の変更に合わせて適時修正して下さい。

回答No.5

kagakusuki

回答No.3です。

　もし、作業列を使わずに関数のみで処理を行いたいという様な場合には、Sheet3は使わずに、別シート２のA2セルに入力する関数を次の様なものにして下さい。(入力シート１や別シート２のレイアウトは回答No.3と同様とします)

=IF(A$1="","",IF(ROWS(A$2:A2)>COUNTIF(入力シート１!$A:$A,A$1),"",OFFSET(入力シート１!$B$1,SUMPRODUCT(ISNUMBER(ROW(入力シート１!$B$1:$B$999))*(COUNTIF(OFFSET(入力シート１!$A$1,,,ROW(入力シート１!$B$1:$B$999)-ROW(入力シート１!$B$1)+1),A$1)<ROWS(A$2:A2))),)))

　尚、SUMPRODUCT関数は、繰り返し処理を行う関数ですので、行数が多くなりますと処理が重くなる傾向がありますので注意して下さい。

回答No.4

imogasi

このタイプの問題は、「表のデータの、組換え！」に当たるのです。
エクセル関数では、表の組換え（縦のものを横にするの（Ｔｒａｎｓｐｏｓｅ間数以外では））は苦手な分野です。
また関数では、部分的には「抜出し問題」（例Ａの行の抜き出し）にあたります。
条件つき抜出になります。実は、関数を使うのは、アイデアと経験が必要で、結果の式も長いものになります。初心者が思いつくのは無理です。
回答例も出ていますので、それを自分の場合に修正して、「結果良ければよし」と思うならそれでよい。
関数は（式が適当に作られておれば）データの追加などに即座に反応してくれます。
ーー
ＶＢＡでは「、即座に反応してくれる」には、イベント」という仕組みに頼ることになりますが、それなりの経験と技量が要り、初心者にはむつかしい部類の課題です。
（１）定期的な起動の処理で我慢する・
（２）コード列（Ａ，Ｂなどの列）でソートする。
（３）ＶＢＡでＡのデータ区切りで、同じコードのものをＶＢＡで一括して決まった列に移す、プログラムを組むのが、一番やりやすいと思う。

回答No.3

kagakusuki

今仮に、シート１において「分類」と入力されているセルがA1セルであり、「名称」と入力されているセルがB1セルであり、別シート２において「A」と入力されているセルがA1セルであるものとします。
　また、Sheet3のA列を作業列として使用する元します。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(OR(INDEX(入力シート１!$A:$A,ROW())="",INDEX(入力シート１!$B:$B,ROW())=""),"",INDEX(入力シート１!$A:$A,ROW())&"◆"&COUNTIF(A$1:A1,INDEX(入力シート１!$A:$A,ROW())&"◆*?"))

　次に、Sheet3のA2セルをコピーして、Sheet3ののA3以下に貼り付けて下さい。

　次に、別シート２の1行目のセルにA1セルから右方向に向かって「A」～「C」等の各分類名を入力して下さい。
　次に、別シート２のA2セルに次の関数を入力して下さい。

=IF(A$1="","",IFERROR(INDEX(入力シート１!$B:$B,MATCH(A$1&"◆"&ROW()-ROW(A$2),Sheet3!$A:$A,0)),""))

　次に、別シート２のA2セルをコピーして、別シート２の2行目のセルの内、A2セルよりも右側にあるセル範囲に貼り付けて下さい。
　次に、別シート２の2行目全体を纏めてコピーして、別シート２の3行目以下に貼り付けて下さい。

　以上です。

補足

シート３の内容を別シート２へ作業セルとして登録してはダメですか？

回答No.2

ushi2015

こんにちは
No1です。
これって、Excelの質問ですよね？
シート１、シート２とか、関数って事は。

補足

エクセルの質問です。カテゴリ違いであれば申し訳ございません＞＜
ご回答もありがとうございました＾＾