画像のように整列した表にするにしたいのですが

質問

画像のように左の表から右の表に変更する数式みないなものがあればご教授いただきたく思っております。

クラスは複数あると過程して（数百～数千）、また数字もこの画像ですと５つまでですが、

実際のものは数十あります。

以上よろしくお願いいたします。

回答No.4

bunjii

>クラスは複数あると過程して（数百～数千）、また数字もこの画像ですと５つまでですが、
「過程して」は「仮定して」の誤変換ですか？
H列のクラス名は昇順でソートされていると考えて良ければ次のようにすると良いでしょう。
A1=H1
A2=IF(A1="","",INDEX(H:H,MATCH(A1,H:H)+1)&"")
A2セルを下へ必要数コピーすれば組名の単一化ができます。
他にはH列をA列へコピーしてA列のみ「重複の削除」を行って単一化することでも良いでしょう。
B1=IF(AND($A1<>"",COUNTIF($H:$H,$A1)>COLUMN()-2),INDEX($I:$I,SMALL(INDEX(($H$1:$H$5000=$A1)*ROW(B$1:B$5000)+($H$1:$H$5000<>$A1)*ROW(B$5001),0),COLUMN()-1)),"")
B1セルを右と下へ必要数コピーすると目的に合います。
但し、処理対象のH列とI列は最大5000行としてありますので、それより多い場合は範囲を変更してください。
尚、この数式は配列値を大量に扱いますので処理範囲が大きくなると応答がなくなりシステムが壊れたと勘違いすることになります。
VBAで処理される方が良いかも知れません。

回答No.3

noname#204879

非常に原始的(?)な方法を紹介しておきます。
ただし、此れは Excel 2002 に依るものなので、貴方は自分のバージョンに合わせてお読みください。
Sheet1 の表を考えます。
１．Sheet1 のＧ列を文字列 # で埋める。(Fig-1)
以下は、Sheet2 についての説明。
２．次の各セルにそれぞれ右側の式を入力
　　C2: =COUNTIF(B$2:B2,"＃")
　　A2: =OFFSET(Sheet1!A$1,C2,)
　　B2: =OFFSET(Sheet1!$B$1,(ROW(A1)-1)/6,MOD(ROW(A1)-1,6))
　　D2: =OR(B2="＃",B2=0)
３．範囲 C2:D2 を下方にズズーッとドラッグ＆ペースト。(Fig-2)
４．列Ａ～Ｄ全体を選択して、[コピー]→[値の貼り付け]を実行
５．[オートフィルタ]でＤ列の TRUE でレコードを抽出して、全レコード行を選択(Fig-3)
６．[編集]→[行の削除]を実行
７．[データ]→[フィルタ]→[すべて表示]を実行(Fig-4)
８．必要に応じて、列Ｃ、Ｄ、１行目を削除

回答No.2

tom04

こんにちは！

関数でもできないことはないと思いますが、
VBA向きの質問のようですので、VBAでの一例です。
画像のように元データはSheet1のA列1行目からあるとし、Sheet2に表示させています。

Alt+F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, cnt As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
wS.Cells.Clear
With Worksheets("Sheet1")
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row 'Sheet1のA列1行目～最終行まで★
For j = 2 To .Cells(i, Columns.Count).End(xlToLeft).Column 'Sheet1のB列～データが入っている最終列まで★
If .Cells(i, j) <> "" Then
cnt = cnt + 1
wS.Cells(cnt, "A") = .Cells(i, "A")
wS.Cells(cnt, "B") = .Cells(i, j)
End If
Next j
Next i
End With
End Sub 'この行まで

※　関数でないので、データ変更があるたびにマクロを実行する必要があります。m(_ _)m

回答No.1

weboner

計算式なら
H1 =IFERROR(INDEX(クラス,SMALL(IFERROR(1/ISNUMBER(データ範囲)*ROW(クラス),""),ROW())),"")

※配列計算です[Shift]+[Ctrl]+「Enter]で確定
データ範囲＝ B1：F12　クラス ＝ A1：A12　にそろぞれ名前付けされています

I1　=IFERROR(VLOOKUP(H1,表全体,COUNTIF($H$1:H1,H1)+1,0),"")

こちらは普通の計算式
表全体＝ A1：F12 に名前付け

小さな表ならこれで問題はないけど
数十列ｘ数千行の表の場合配列計算だと再計算で時間がかかる可能性があります(PCの性能次第ですが)
あまり長くかかるようならマクロを組んだほうがいい