エクセルについて

質問

エクセルについて、
山田太郎　　150 154 155　156
佐藤花子　　151 153 158
高橋二郎　　152 159 162　163 164　165
　 　： (それぞれ別のセルに入力)
というようなシート(1)の横書きのデータをもとにして、別のシート(2)の
150
151
152
153
154
：
という縦書きの数列の横に、シート(1)の対応する名前
150　山田太郎
151　佐藤花子　
152　高橋二郎
153　佐藤花子
154　山田太郎
　：
を表示させる方法をご教授願います。
実際のデータはシート(1)、シート(2)ともに膨大なデータのため、手作業の処理は
困難な状況です。
よろしくお願いいたします。m(_　_)m

回答No.2

tom04 · ベストアンサー

こんばんは！
数値に重複はないのですよね？

そういうことだとしての一例です。
とりあえず50列・100行目まで対応出来る数式です。

↓の画像でSheet2のA2セルに
=IF(COUNT(Sheet1!B:AX)<ROW(A1),"",SMALL(Sheet1!B:AX,ROW(A1)))
B2セルに
=IF(A2="","",INDEX(Sheet1!A:A,SUMPRODUCT((Sheet1!$B$1:$AX$100=A2)*ROW($A$1:$A$100))))
という数式を入れB2・C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピー！

これで画像のような感じになります。

的外れならごめんなさいね。m(_ _)m

お礼

画像まで添付していただき、大変懇切な回答をいただきありがとうございます。
早速、明日職場で実験させていただきます！

回答No.5

keithin

膨大と言っても単に程度問題なので，「具体的にどれだけのデータがある」と今実際にアナタが困っている様子を挙げて質問をして貰った方が，慣れた回答者なら「それならこういう方法の方がよいですよ」と当たりも付けて回答してくれます。（折角皆さんが考えてくれた数式も，教わったヒトが実地に合わせて修正できずに役に立たないご相談も多いです）
ホントに膨大なら，確かにマクロを使った方が便利ですが，それなりに気をつけないとマクロでも遅いです。

ところで。
＞別のシート(2)の…縦書きの数列の横に、シート(1)の対応する名前…を表示させる

シート２にどういう具合にデータを並べたいのか，あなたのご相談はヤリタイ事が説明不足です。
○シート１にある番号だけを並べたい
○シート１に有る無し関係なくシート２はシート２で番号が列記してあるので，そこに名前を埋めたい
前回回答したのは後者を想定していますし，下記に参考で書いたマクロは前者を想定しています。

＃ついでに
ご利用のエクセルのバージョンによって，色々出来ること出来ないこと操作の段取りが変わる場合があります。ご相談投稿時には，ご利用のソフト名は当然として，ご利用のソフトのバージョンまで明記することを憶えてください。

作成例：シート１のA列に名前，B列以降数字をシート２に移動する
sub macro1()
　dim i as long
　dim n as long
　application.screenupdating=false
　worksheets("Sheet2").select
　range("A:B").clearcontents
’データのある行を調べて
　with worksheets("Sheet1")
　for i = 1 to .range("A65536").end(xlup).row
　if application.count(.rows(i)) > 0 then
　n = .cells(i, "IV").end(xltoleft).column - 1
’転記して
　range("A65536").end(xlup).offset(1).resize(n, 1).value = _
　　application.transpose(.cells(i, "B").resize(1, n))
　range("B65536").end(xlup).offset(1).resize(n, 1).value = .cells(i, "A")
　end if
　next i
　end with
’並べ替える
　range("A1:B1").value = array("Number", "Name")
　range("A:A").specialcells(xlcelltypeblanks).entirerow.delete shift:=xlshiftup
　range("A:B").sort key1:=range("A1"), order1:=xlascending, header:=xlyes
　application.screenupdating=true
end sub

お礼

ご指摘ありがとうございます。
次回から質問の仕方には注意します。

回答No.4

kagakusuki

元データの行数や列数が不明な場合でも関係なく有効な方法です。
　但し、A列に名前が入力されている人数が何千人にもなる場合には、計算が重くなります。（2千人前後までなら許容範囲だと思います）
　今仮に、元データの中で、山田太郎と入力されているセルがSheet1のA2セルであり、別のシート(2)とはSheet2の事であるものとします。
　まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(OFFSET(Sheet1!$1:$1,,,MATCH("゛",Sheet1!$A:$A,-1)-ROW(Sheet1!$A$1)+1)),"",SMALL(OFFSET(Sheet1!$1:$1,,,MATCH("゛",Sheet1!$A:$A,-1)-ROW(Sheet1!$A$1)+1),ROWS($2:2)))

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(ISNUMBER($A2),INDEX(Sheet1!$A:$A,SUMPRODUCT(ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("゛",Sheet1!$A:$A,-1)))*(COUNTIF(OFFSET(Sheet1!$1:$1,ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("゛",Sheet1!$A:$A,-1)))-ROW(Sheet1!$A$1),),$A2)>0))),"")

　次に、Sheet2のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

お礼

懇切丁寧なご回答ありがとうございました！

回答No.3

tom04

No.2です！
投稿後に気づいたのですが、No.1のkeithinさんとほとんど同じ内容になってしまいましたので
余計なお世話かもしれませんが、VBAでの一例です。
Sheet1のデータをSheet2に表示するようにしてみました。
Sheet1のデータは2行目からあるとしています。

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

Sub test() 'この行から
Dim i, j, k As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet2") '←sheet2の部分は実際のSheet名に！
ws.Cells.ClearContents
Application.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To Cells(i, Columns.Count).End(xlToLeft).Column
ws.Cells(Rows.Count, 1).End(xlUp).Offset(1) = Cells(i, j)
Next j
Next i
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To Cells(i, Columns.Count).End(xlToLeft).Column
For k = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If ws.Cells(k, 1) = Cells(i, j) Then
ws.Cells(k, 2) = Cells(i, 1)
End If
Next k
Next j
Next i
Application.ScreenUpdating = True
ws.Columns("A:B").Sort key1:=ws.Cells(1, 1), order1:=xlAscending
ws.Rows(1).Insert
With ws.Cells(1, 1)
.Value = "データ" '←項目名は適当に変更
.Offset(, 1) = "氏名" '←こちらの項目名も！
End With
ws.Columns("A:B").AutoFit
End Sub 'この行まで

どうも何度も失礼しました。m(_ _)m

お礼

Ｎｏ２のご回答にて解決いたしました。
ありがとうございました。

回答No.1

keithin

とりあえずシート１に100行×100列の例で
とりあえずシート２のA1に150等があるとして
B1に
=IF(COUNTIF(Sheet1!$1:$100,A1)=1,INDEX(Sheet1!A:A,SUMPRODUCT((Sheet1!$B$1:$CV$100=A1)*ROW($A$1:$A$100))),"")

お礼

ご回答ありがとうございます。
明日職場で試してみます！