エクセルで条件が一致する文字列を一つのセルに記述

質問

エクセルで 元データから以下のような形に配置変更がしたく、
関数か何かを使って一括でできる方法を教えていただけないでしょうか？

・元データ
　　Ａ　　　　　Ｂ
１　荒川区　300
２　江東区　300
３　中央区　400
４　稲城市　400
５　江戸川区　400
６　品川区　500
７　北区　　500
８　狛江市　500

・希望の形　
金額にマッチするものを列に分けて、かつ五十音順に同一セル内に記述したいです。　

　　Ａ　　　　Ｂ　　　　Ｃ　　　　　　　Ｄ
１　　　　　　300　　　　400　　　　　500
２　あ　　　　荒川区　　稲城市
　　　　　　　　　　　　　江戸川区　　
３　か　　　　江東区　　　　　　　　北区
　　　　　　　　　　　　　　　　　　　　狛江市
４　さ　　　　　　　　　　　　　　　　　品川区
５　た　　　　　　　　　　中央区

エクセル2003、windowsXPです。
どうぞ宜しくお願いします

回答No.2

KURUMITO · ベストアンサー

かなり内容が複雑になりますので作業列を作ったり、基本となる表を準備したりすることが必要ですね。
シート1のA2セルから下方には荒川区などの地名が手入力で入力されているとします。手入力でないと漢字に振り仮名が付けられておりません。A列を選択してから「ふり仮名」を表示させ、そのフリガナがひらがな表示になるように設定します。
B2セルから下方には300,400,500などの数値が入力されているとします。
基本となる表を作成するためJ2セルから下方にあ、か、さ、た、な、は・・・・・のように入力します。
K2セルには次の式を入力して下方にドラッグコピーします。

=IF(J2="","",CODE(J2))

その上でCやD列には別の項目のデータが有るとしてE2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",CODE(LEFT(PHONETIC(A2),1)))

これでA列での地区名のひらがなの頭文字のコード番号が表示されます。
F2セルには次の式を入力して下方にドラッグコピーします。

=IF(E2="","",INDEX(J:J,MATCH(E2,K:K,1)))

G2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(F$2:F2,F2)=1,E2,"")

H2セルには次の式を入力して下方にドラッグコピーします。

=IF(F2="","",F2&B2&"/"&COUNTIF(H$1:H1,F2&B2&"*"))

これでシート1での作業は終了です。
お望みの表をシート2に表示させることにして
B1セルから横方向の列には３００，４００，５００・・のように数値を入力します。
A2セルには次の式を入力して右横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IF(ROW(A1)>COUNT(Sheet1!$G:$G),"",IF(COLUMN(A1)=1,INDEX(Sheet1!$F:$F,MATCH(SMALL(Sheet1!$G:$G,ROW(A1)),Sheet1!$G:$G,0)),IF(AND(COLUMN(A1)>1,A$1<>""),IF(COUNTIF(Sheet1!$H:$H,$A2&A$1&"/0")=0,"",INDEX(Sheet1!$A:$A,MATCH($A2&A$1&"/0",Sheet1!$H:$H,0)))&IF(COUNTIF(Sheet1!$H:$H,$A2&A$1&"/1")=0,"",CHAR(10)&INDEX(Sheet1!$A:$A,MATCH($A2&A$1&"/1",Sheet1!$H:$H,0)))&IF(COUNTIF(Sheet1!$H:$H,$A2&A$1&"/2")=0,"",CHAR(10)&INDEX(Sheet1!$A:$A,MATCH($A2&A$1&"/2",Sheet1!$H:$H,0)))&IF(COUNTIF(Sheet1!$H:$H,$A2&A$1&"/3")=0,"",CHAR(10)&INDEX(Sheet1!$A:$A,MATCH($A2&A$1&"/3",Sheet1!$H:$H,0)))&IF(COUNTIF(Sheet1!$H:$H,$A2&A$1&"/4")=0,"",CHAR(10)&INDEX(Sheet1!$A:$A,MATCH($A2&A$1&"/4",Sheet1!$H:$H,0)))&IF(COUNTIF(Sheet1!$H:$H,$A2&A$1&"/5")=0,"",CHAR(10)&INDEX(Sheet1!$A:$A,MATCH($A2&A$1&"/5",Sheet1!$H:$H,0)))&IF(COUNTIF(Sheet1!$H:$H,$A2&A$1&"/6")=0,"",CHAR(10)&INDEX(Sheet1!$A:$A,MATCH($A2&A$1&"/6",Sheet1!$H:$H,0))),"")))

この式では一つのセルに7個の地域名を表示させることができます。
一つのセルに行を変えて地域名を表示させるためにはB列から右の列を選択してから右クリックして「セルの書式設定」で「配置」をクリックし、「折り返して表示する」にチェックをします。これでお望みに表が完成します。

お礼

すごい式を作っていただき、ありがとうございます！とても助かりました！

回答No.1

keithin

Ｃ列に，各地名がどこにグループされるのか記入します
ア
カ
タ
ア
ア
サ
カ
カ
などのように。

Ｄ列に
Ｄ１：
=C1&B1
以下コピーして
ア300
カ300
タ400
ア400
ア400
サ500
カ500
カ500
のように列記させます

Ｅ列に
Ｅ１：
=A1&IF(COUNTIF(D2:D999,D1),CHAR(10)&VLOOKUP(D2,D2:E999,2,FALSE),"")
以下コピーします

シート２で
Ａ２以下にグループ名を列記します
ア
カ
サ
タ
ナ
のように

Ｂ１，Ｃ１，Ｄ１に300，400，500を列記します
Ｂ２に
=IF(COUNTIF(Sheet1!$D:$D,$A2&B$1),VLOOKUP($A2&B$1,Sheet1!$D:$E,2,FALSE),"")
と記入し，右に下にコピー，折り返して全体を表示させて完成。

お礼

ありがとうございます、できました！非常に助かりました！