[[20140724183028]]　『データを関数で取り出したい』（みり）　ページの最後に飛ぶ

『データを関数で取り出したい』（みり）

こんにちは。考え方のとっかかりもわからないので教えていただけないでしょうか
下記のような表があります。カンマがセル区切りです

　品番1,役割1,氏名1,役割2,氏名2,………役割10,氏名10
　品番2,役割1,氏名1,役割2,氏名2,………役割10,氏名10

品番は10桁です
役割は３つ（監督、検閲、専任）があります
氏名は100名程度で全てフルネームです

1品番に対して監督が0～3名、検閲が0～3名、専任が0～10名いらっしゃいます。トータルでは1～10名です

品番、役割、氏名以外にも膨大なデータが入っており、別シートにvlookupで作成しようとしたのですがうまくいきません
セキュリティの関係（？）でVBAは使用禁止とのことです
最終的には品番を入れると下記のようになって欲しいです。どうすればよいでしょうか

　品番,監督1,監督2,監督3,検閲1,検閲2,検閲3,専任1,専任2,専任3………専任10
　1234567890,Aさん,,,Bさん,Cさん,,Fさん,,,,,,,,,

もし重複質問できたら申し訳ありません
どうぞよろしく御願い致します

＜使用 Excel：Excel2003、使用 OS：Windows7 ＞

 ＞別シートにvlookupで作成しようとしたのですがうまくいきません 
 みた限り、通常はVLOOKUPで作成できます。
 どのような式を入れたら「うまくいかなかった」のですか？
（稲葉） 2014/07/25(金) 08:33

 面倒かとは思いますが

 ＞　品番,監督1,監督2,監督3,検閲1,検閲2,検閲3,専任1,専任2,専任3………専任10 
 ＞　1234567890,Aさん,,,Bさん,Cさん,,Fさん,,,,,,,,,

 となる様な元データ

 ＞　品番1,役割1,氏名1,役割2,氏名2,………役割10,氏名10 
 ＞　品番2,役割1,氏名1,役割2,氏名2,………役割10,氏名10 
 の部分を書いておかれるのが良いと思います。

 また、セル番地を明確にするのと
 サンプルデータも一つではなく、いくつかのパターンを作ってもらえると良いと思います。
 　 
（HANA） 2014/07/25(金) 09:22

説明が足りず申し訳ないです

実データですが、例えば以下のようになります
監督,Aさん,検閲,Bさん,監督,Cさん,監督,Dさん,専任,Eさん,専任,Fさん,検閲,Aさん,専任,Gさん
このようになります
vlookupだとどうやって一回目に出てきた監督か二回目に出てきた監督か判定できずに困っていました

どうぞよろしく御願い致します
（みり） 2014/07/25(金) 11:06

作業列を使う案ですが、・・・。

元のデータ範囲がSheet1のA1:Q100として、

１行目をタイトル行、２行目以降、A列に品番、B列からQ列に役割毎の氏名を抽出表示させる

１　Match関数で品番に対応した行を検索
S2: =MATCH(A2,Sheet1!$A$1:$A$100,0)

２　当該行に、表頭の役割の人がいるか、いるとすればそれは何列目のセルにあるかを検索
T2:={=SMALL(IF(OFFSET(Sheet1!$A$1,$S2-1,0,1,17)=LEFT(B$1,2),COLUMN($A$1:$Q$1),999),SUBSTITUTE(B$1,LEFT(B$1,2),""))}
式の前後の{}は配列数式（Ctrl＋Shift＋Enterで確定）これをAI2まで右にコピー

３　上記の結果を基にINDEX関数で表示させる。
B2:=IF(T2=999,"",INDEX(Sheet1!$A$1:$A$100,$S2,T2+1))
これをQ2までコピー

もっと良い方法があると思いますが、これくらいしか思いつきません。

（jjj） 2014/07/25(金) 12:17

 １）元データをずっと管理して、別シートに計算式で表示させる
 ２）一度きりの処理として、別シートに一度値として出力し、今後は別シートのフォーマットで管理する

 これによってずいぶん変わってくると思います。
 ２）なら並べ替えなどを駆使して簡単に出来ますが、１）の場合「膨大なデータ」とのことですので、
 処理がかなり重くなると思います。

 どちらになりますか？

（稲葉） 2014/07/25(金) 13:27

 １）の案
 jjjさんと同じことですが・・・

 Sheet1（元データ）
	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	[I]	[J]	[K]	[L]	[M]	[N]	[O]	[P]	[Q]
[1]	品番1	検閲	Bさん	監督	Aさん												
[2]	品番2	監督	Aさん	検閲	Bさん	監督	Cさん	監督	Dさん	専任	Eさん	専任	Fさん	検閲	Aさん	専任	Gさん

 【出力結果】
 Sheet2（別シート）
	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	[I]	[J]	[K]	[L]	[M]	[N]	[O]	[P]	[Q]
[1]		監督1	監督2	監督3	検閲1	検閲2	検閲3	専任1	専任2	専任3	専任4	専任5	専任6	専任7	専任8	専任9	専任10
[2]	品番1	Aさん			Bさん												
[3]	品番2	Aさん	Cさん	Dさん	Bさん	Aさん		Eさん	Fさん	Gさん

 B2=IFERROR(VLOOKUP($A2,Sheet1!$A$1:$AA$100,SMALL(IF(OFFSET(Sheet1!$A$1:$AA$1,MATCH($A2,Sheet1!$A$1:$A$100,0)-1,0)=LEFT(B$1,2),COLUMN($A$1:$AA$1),""),RIGHT(B$1,LEN(B$1)-2))+1,0),"")
 Ctrl+Shift+Enterで配列数式
 必要な範囲にコピーでいかがですか？

 尚、別シートの項目名「監督1」や「検閲1」は、必ず役職名が2文字、数字が入っているものとしています。
（稲葉） 2014/07/25(金) 13:50

2003でIFERROR関数は使えなかったように記憶してましたが。
使えましたっけ？

なので、IF関数でエラー回避しようとすると、やたらと長い式になるので、
作業列を提案したところでした。

ちなみに、どうしても作業列を使わないでやるとすれば、
B2:{=IF(ISERROR(SMALL(IF(OFFSET(Sheet1!$A$1:$AA$1,MATCH($A2,Sheet1!$A$1:$A$100,0)-1,0)=LEFT(B$1,2),COLUMN($A$1:$AA$1),""),RIGHT(B$1,LEN(B$1)-2)),0)),"",VLOOKUP($A2,Sheet1!$A$1:$AA$100,SMALL(IF(OFFSET(Sheet1!$A$1:$AA$1,MATCH($A2,Sheet1!$A$1:$A$100,0)-1,0)=LEFT(B$1,2),COLUMN($A$1:$AA$1),""),RIGHT(B$1,LEN(B$1)-2))+1,0)))}　でしょうか？（カッコの数あってるかな？）

（jjj） 2014/07/25(金) 15:24

あるいは、

{=VLOOKUP($A2,Sheet1!$A$1:$AA$100,SMALL(IF(OFFSET(Sheet1!$A$1:$AA$1,MATCH($A2,Sheet1!$A$1:$A$100,0)-1,0)=LEFT(B$1,2),COLUMN($A$1:$AA$1),""),RIGHT(B$1,LEN(B$1)-2))+1,0)}

とエラー回避せず、条件付き書式でエラーが出たセルの文字色を白にして、
見かけ上ブランクになるようにするとか？

（jjj） 2014/07/25(金) 15:49

 あ、2003を見逃していました。
 ご指摘ありがとうございます。
 エラーは無視した方がいい感じですね・・・
（稲葉） 2014/07/25(金) 16:07

お礼が遅くなり申し訳ありませんでした。
急ぎのものが入ってしまいまだ手が付いていないのですが、
色々教えていただきありがとうございました。
今後ともどうぞよろしくお願いいたします。
（みり） 2014/07/28(月) 12:38

コメント返信：

[ 一覧(最新更新順) ]

[[20140724183028]] 『データを関数で取り出したい』（みり） ページの最後に飛ぶ

[[20140724183028]]　『データを関数で取り出したい』（みり）　ページの最後に飛ぶ