Excel 同じ値を別のセルに反映したい

質問

エクセル上で、部屋割りの編集をしたいと思っております。

なまえ コード 　　同室(1) 　　同室(2)

ねずみ 1 　　うし 　　とら
うさぎ　　　 2 　　りゅう 　　へび
うし　　　　　1 　　ねずみ 　　とら
りゅう　　　　2 　　うさぎ 　　へび
とら 　　　　1 　　ねずみ 　　うし
へび 　　　　2 　　うさぎ 　　りゅう

・「なまえ」「コード」の入力は終えていて、同室(1)と同室(2)にセルを反映したい

です。方法を教えてください。

回答No.5

bunjii · ベストアンサー

>つまり、シングルの人もいれば、ツイン or トリプル になる組も分かれます。
当初に提された条件はトリプルと言うことでしょうか？
Office 2003以前のバージョンは近日中にサポート終了になりますのでExcel 2007以降を対象にさせて頂きます。

コードが無い場合をシングル、コードが同じの組み合わせの個数が2の場合はツイン、同じく3の場合はトリプルとし、同じく4以上の組み合わせは無いものとすれば数式の一部修正で対応できます。

C2=IF(OR(COUNTIF(A$1:D1,INDEX(A3:A$1500,MATCH(B2,B3:B$1500,0)))>0,ISERROR(MATCH(B2,B3:B$1500,0))),IF(B2="","シングル",""),INDEX(A3:A$1500,MATCH(B2,B3:B1500,0)))

D2=IF(OR(COUNTIF(A$1:D1,INDEX(A3:A$1500,MATCH(B2,B3:B1500,0)))>0,ISERROR(MATCH(B2,B3:B$1500,0))),"",IFERROR(OFFSET(INDIRECT(ADDRESS(MATCH(C2,A$1:A$1500,0),1)),MATCH(B2,INDIRECT(ADDRESS(MATCH(C2,A$1:A$1500,0)+1,2)&":B1500"),0),0),""))

尚、数式の論理が理解できないと修正の必要が生じたとき同様な質問を繰り返さなければならないので、数式の論理を学習して応用できるようにしてください。

お礼

早速のご返事誠に有難うございました。
数式を読み解いて、なんとかうまく解決できました。
感謝申し上げます。

回答No.4

bunjii

回答No.3の追加です。
Excel 2013で検証した画像を添付します。

C2=IF(OR(COUNTIF(A$1:D1,INDEX(A3:A13,MATCH(B2,B3:B13,0)))>0,ISERROR(MATCH(B2,B3:B13,0))),"",INDEX(A3:A13,MATCH(B2,B3:B13,0)))

D2=IF(OR(COUNTIF(A$1:D1,INDEX(A3:A13,MATCH(B2,B3:B13,0)))>0,ISERROR(MATCH(B2,B3:B13,0))),"",OFFSET(INDIRECT(ADDRESS(MATCH(C2,A$1:A$13,0),1)),MATCH(B2,INDIRECT(ADDRESS(MATCH(C2,A$1:A$13,0)+1,2)&":B13"),0),0))

提示のデータを増やして順番を入れ替えた状態で検証しました。
実データに合わせて最小範囲で計算しています。

お礼

詳しい解説、有難うございます。
さらにデータがupdateしてしまい、ご相談です。

・データ数ですが、約1500ほどあります。
・さらにB列の「コード」が"ない人"と"ある人"が混在しております。
・また、"同室(1)のみ"もいれば、"同室(1)と同室(2)"が入るかたもいます。
　⇒つまり、シングルの人もいれば、ツイン or トリプル になる組も分かれます。

この場合の数式を是非、教えて頂けないでしょうか。

回答No.3

bunjii

>・「なまえ」「コード」の入力は終えていて、同室(1)と同室(2)にセルを反映したいです。
コードが同じの名前を組み合わせれば良いのでしょうか？

C列は次の式で良いと思います。

C2=INDEX(A3:A100,MATCH(B2,B3:B100,0))

D列は少々面倒な処理になります。

D2=OFFSET(INDIRECT(ADDRESS(MATCH(C2,A$1:A$100,0),1)),MATCH(B2,INDIRECT(ADDRESS(MATCH(C2,A$1:A$100,0)+1,2)&":B100"),0),0)

但し、提示のデータでは2組のみが有効であり、"りゅう　2　うさぎ　へび"以降の組み合わせは不要なので、非表示にする方法も別途考慮する必要があるでしょう。
また、対象データの行数は100までにしてありますので必要に応じて変更してください。

回答No.1

tom04

こんばんは！

↓の画像のような配置になっているという解釈で・・・

画像ではC2セルに
=IF(COUNTIF($B:$B,$B2)-1<COLUMN(A1),"",INDEX($A$1:$A$100,SMALL(IF(($A$1:$A$100<>$A2)*($B$1:$B$100=$B2),ROW($A$1:$A$100)),COLUMN(A1))))
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は、
上記数式をドラッグ＆コピー　→　C2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
CtrlキーとShiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

C2セルを列・行方向にオートフィルでコピーすると
画像のような感じになります。m(_ _)m

お礼

ありがとうございます。
実は、約640ほどデータがあり、
オートフィルでコピーしたところ、後半から"#NUM!"とエラーになりました。