エクセルで関数を使い注文品別の一覧表を作るには？

質問

まず、元になるデータとして、縦軸1～80に発注者氏名、横軸A～Ｊに注文品を記した表があり、発注した個数を入力してあります。縦軸最後にはSUM関数で注文品別の合計発注数が出ています。
この表を基に、注文品別の発注者がわかる一覧表を作成したいのですが、ご教授お願いします。
VLOOKUP関数等考えてみましたが、うまくいきません。（個数を入力したセルから氏名をひっぱってくる方法が不明です）エクセル上級者の皆様、宜しくお願い致します。（アクセスがないのでエクセルでお願いします）

回答No.6

Nouble · ベストアンサー

こう言う事ですか？

=IF(SUMPRODUCT(SIGN(OFFSET(Sheet1!$A$1,1,MATCH(A$3,Sheet1!$B$1:$E$1,0),80,1)))<ROW(A1),"",OFFSET(Sheet1!$A$1,SMALL(INDEX(SIGN(OFFSET(Sheet1!$A$1,1,MATCH(A$3,Sheet1!$B$1:$E$1,0),80,1))*ROW(Sheet1!$A$1:$A$80)+(1-SIGN(OFFSET(Sheet1!$A$1,1,MATCH(A$3,Sheet1!$B$1:$E$1,0),80,1)))*9999,,),ROW(A1)),0,1,1))

お礼

すばらしい！
まさに神！
本当にありがとうございました！

回答No.5

imogasi

VBAはやってないという質問者は多いので、下記は質問者には無駄かもしれないが。
色んな人が質問と回答を見ている・今後見る思って挙げてみます。
泥くさい（繰り返し法）手法でやってます。
関数での回答を見るにつけ、エクセルの関数で、「表の組換えをやる」（この質問の類型）のは難しいと思います（式が複雑になる）。
ーー
サンプルデータ
Sheet1に元データがあるとします。
人別集計 ごはん みそ汁 たまご焼き サラダ
佐藤 1 1 1
高橋 1 1 1
鈴木 1 1 1
計
ーー
VBAを使うために
シートでALT＋F11でVBEの画面が出る。そこで挿入ー標準モジュールで標準module画面を出す。
その標準モジュールに、下記をコピペ。
Sub test01()
Set sh1 = Worksheets("Sheet1") '元データのあるシート
Set sh2 = Worksheets("Sheet2") 'アウトプット（結果）を作るシート
'データ最右列番号検出
ce = sh1.Cells(1, 100).End(xlToLeft).Column 'データの最終列番号を取得
'MsgBox ce
re = sh1.Cells(1000, "A").End(xlUp).Row 'データの最終行を取得
'MsgBox re
'i,j はSheet1の行と列のポインター
'k はSheet2の行のポインター

'--
k = 2 'Sheet2の開始行
sh2.Cells.Clear 'Sheet2のシートデータをクリア
'---
For j = 2 To ce '2列から最終列まで繰り返し
sh2.Cells(k, 1) = sh1.Cells(1, j) 'Kに+1しない

For i = 2 To re '2行目から最終行まで繰り返し
If sh1.Cells(i, j) <> "" Then 'そのセルにデータがあれば
sh2.Cells(k, "B") = sh1.Cells(i, "A") 'データをSheet1からSheet2のセルに転記
sh2.Cells(k, "C") = sh1.Cells(i, "B") 'データをSheet1からSheet2のセルに転記
k = k + 1
End If

Next i
Next j

End Sub
ーーー
実行すると、Sheet2に
結果
ごはん 佐藤 1
高橋 1
鈴木 1
みそ汁 高橋 1
鈴木 1
たまご焼き 佐藤 1
鈴木 1
サラダ 佐藤 1
高橋 1
ーー
見出しや罫線はVBAでも簡単に作れます。（略）
「ごはん」などの、表の塊を、質問のような、配置にVBAでするには
もう少しの行数が必要です。（略）

お礼

ありがとうございました。
VBAでもチャレンジしてみます。

回答No.4

msMike

[No.3]の補遺、
ステップ１、２は無視してください。
(途中で方針変更したので、不要になった次第。ただし、必要なくなったけど。あっても支障はありません)

お礼

ありがとうございました。
早速コピーして使ってみます！

回答No.3

msMike

添付図参照(Excel 2013)
１．範囲 B1:E4 を選択して、[数式]→[定義された名前 <選択範囲から
￣￣作成>]
２．"上端行"だけのチェックを残して[OK]をツン
３．セル B9 に次の配列数式を入力
￣￣ =IFERROR(INDEX($A$1:$A$4,SMALL(IF(OFFSET($A$1,1,MATCH(A$9,$B$1:$E$1,0),3,)>0,ROW(A$2:A$4),""),ROW(A1))),"")
４．セル B13 に次の配列数式を入力
￣￣ =IFERROR(INDEX($A$1:$A$4,SMALL(IF(OFFSET($A$1,1,MATCH(A$13,$B$1:$E$1,0),3,)>0,ROW(A$2:A$4),""),ROW(A1))),"")
５．セル B9、B13 をそれぞれ下方に２行オートフィル
６．範囲 B9;B11、B13:B15 を[コピー]して、それぞれＥ列の同行に
￣￣[貼り付け]
【お断り】上式は何れも配列数式として入力のこと

回答No.2

bunjii

貼付画像が変なのですが「ごはん」の表の左上をA9とし、A9:B12と読み替えます。
また、「みそ汁」の表をA14:B17と読み替えます。
「卵焼き」と「サラダ」は「ごはん」、「みそ汁」に準じてセル範囲を読み替えた形で数式を提示します。
B9=IFERROR(INDEX($A$1:$A$4,SMALL(INDEX(($B$2:$E$4<>"")*ROW($B$2:$B$4),0,MATCH($A$9,$B$1:$E$1))+INDEX(($B$2:$E$4="")*(ROW($B$4)+1),0,MATCH(A$9,$B$1:$E$1,0)),ROWS(A$9:A9))),"")
この数式は配列数式になりますので、確定時にCtrlとShiftを押しながらEnterキーを打鍵してください。
B9セルを下へB12までコピーします。
B14=IFERROR(INDEX($A$1:$A$4,SMALL(INDEX(($B$2:$E$4<>"")*ROW($B$2:$B$4),0,MATCH($A$9,$B$1:$E$1))+INDEX(($B$2:$E$4="")*(ROW($B$4)+1),0,MATCH(A$14,$B$1:$E$1,0)),ROWS(A$14:A14))),"")
B14の数式はB9セルのMATCH関数で検索値のA$9をA$14に変更しただけです。
A9:B17を選択してコピー＆ペーストでC9セルへ貼り付ければ目的の結果が得られるはずです。
但し、Excel 2007以降のバージョンであることが必要条件です。
Excel 2003以前のバージョンでは組み込み関数にIFERRORがありませんのでエラー処理を別の方法で処理しなければなりません。
尚、実際のデータへの応用では検索するセル範囲は異なると思いますので実情に合わせて変更してください。
解説が必要のときは分からない部分を提示してください。

お礼

ありがとうございました。
早速コピーして使ってみます！

回答No.1

aokii

元になるデータのセルを全て選んでから、挿入→ピボットテーブルを使ってみてください。

お礼

ありがとうございました。
ピボットテーブル初めてですが、チャレンジしてみます。