エクセルで条件が一致する文字列をセルに記述(2)

質問

すいません、以前も質問をさせていただいた内容なのですが
ちょっと結果が求めていたものと異なっていたため
再度の質問をさせてください。

http://okwave.jp/qa/q7613199.html

・KURUMITOさんの、フリガナをもとに計算するやり方は前の作業の都合上できなくなってしまいました
・keithinさんのやり方だと、同じ地名が別のセルにも出てきてしまいました

どなたか教えていただければ幸いです。よろしくお願いします。

ーーーーーーーーーーーーーーーーーーーーーーーー

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

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

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

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

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

回答No.6

kagakusuki

ANo.5の続きです。

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

=IF(COUNTIF($A1:A1,MAX(Sheet1!$B:$B)),"",SMALL(Sheet1!$B:$B,COUNTIF(Sheet1!$B:$B,"<="&MAX($A1:A1))+1))

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

=IF(ROWS($2:2)>COUNT(Sheet3!$C:$C),"",LEFT(VLOOKUP(ROWS($2:2),Sheet3!$C:$D,COLUMNS(Sheet3!$C:$D)),1))

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

=IF(COUNTIF(Sheet3!$D:$D,$A2&":"&B$1&"#*"),VLOOKUP($A2&":"&B$1&"#"&COUNTIF(Sheet3!$D:$D,$A2&":"&B$1&"#*"),Sheet3!$D:$F,COLUMNS(Sheet3!$D:$F),FALSE),"")

　次に、Sheet2のB1～B2の範囲をコピーして、同じ行内において、B列よりも右側にある列のセルに貼り付けて下さい。
　次に、Sheet2の2行目全体をコピーして、３行目以下に貼り付けて下さい。

　後は、Sheet1の表に、住所と金額を入力しますと、Sheet２に配置変更された表が自動的に表示されます。

回答No.5

kagakusuki

>フリガナをもとに計算するやり方は前の作業の都合上できなくなってしまいました

との事ですが、「ふりがな」という何と読めば良いのかという情報が無ければ、五十音順に並べ替える事は出来ません。
　ですから、PHONETIC関数を使用して、作業列上（下記の例ではSheet3のB列）にふりがなを自動表示させるという方法を使います。
　但し、PHONETIC関数でフリガナが表示出来るのは、文字列をキーボード入力した場合か、PHONETIC関数でフリガナが表示されるセルをコピー＆ペーストした場合であり、住所データを入力する際に、他のセルそのものではなく、他のセルの中に入力されている文字列データを、コピー＆ペーストしたのではフリガナは表示されません。（例：添付画像の8行目にある「荒川区」）
　ですから、入力方法を間違えて、フリガナが表示されないままになっているセルが存在しない様にするために、条件付き書式や入力規則を使用して、入力方法の間違いを防ぎ、フリガナが不明なセルを判別し易くした方が良いと思います。（その方法については後述します）

　具体的には以下の様な作業となります。
　今仮に、Sheet１のA2以下に住所の元データが、Sheet１のB2以下に金額の元データが、それぞれ入力されていて、Sheet3のA列～F列を作業列として使用して、Sheet2に配置変更した表を表示させるものとします。
　尚、作業列の値に関して説明しますと、Sheet3のA列は並べ替えの基準となる数値、B列はフリガナ、C列は「あかさたな」のどの文字を表示させれば良いのかの目印となる数値、D列はデータを検索する際の基準となる文字列を五十音順に並べ替えたもの、E列は五十音順に並べ替えた住所をそれぞれ表示していて、F列は「住所のふりがなの頭文字」と「金額」が同じもの毎に、住所を一纏めにするのに必要な前処理を行うための列です。

　まず、Sheet１を表示させてから、以下の操作を行って下さい。

Sheet1のA2セルを選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に次の数式を入力

=LENB(SUBSTITUTE(SUBSTITUTE(ASC(PHONETIC(A2)),"゛",),"゜",))>LEN(PHONETIC(A2))

　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
好きな色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
選択されているセルを変えないまま、メニューの[データ]ボタンをクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリックし、現れた選択肢の中にある[ユーザー設定]をクリック
　　↓
「数式」欄に

=LENB(SUBSTITUTE(SUBSTITUTE(ASC(PHONETIC(A2)),"゛",),"゜",))=LEN(PHONETIC(A2))

と入力
　　↓
「データの入力規則」ダイアログボックスの[エラーメッセージ]タブをクリック
　　↓
「スタイル」欄が「停止」となっている事を確認
　　↓
「タイトル」欄に「住所の読み方が不明です」と入力
　　↓
「エラーメッセージ」欄に
「文字列のコピー＆ペーストのみで入力しますと、コンピューターは読み方を判断する事が出来ません。
住所の入力は、セルのコピー＆ペーストか、或いはキーボード入力で行って下さい。」
と入力
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック
　　↓
Sheet1のA2セルにカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
Sheet1のA2以下のセル範囲（住所を入力する可能性のある範囲）を選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ダイアログボックスの中にある「書式」と記されている箇所をクリックして、チェックを入れる
　　↓
「形式を選択して貼り付け」ダイアログボックスの[OK]ボタンをクリック
　　↓
選択範囲を変えずに、選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ダイアログボックスの中にある「入力規則」と記されている箇所をクリックして、チェックを入れる
　　↓
「形式を選択して貼り付け」ダイアログボックスの[OK]ボタンをクリック

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

=PHONETIC(INDEX(Sheet1!$A:$A,ROW()))

　次に、以下の操作を行って下さい。

Sheet3のB2セルを選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に次の数式を入力

=AND(INDEX(INDIRECT("Sheet1!A:A"),ROW())=$B1,$B1<>"")

　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
好きな色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック

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

=IF(AND($B2<>"",ISNUMBER(INDEX(Sheet1!$B:$B,ROW())),LENB(SUBSTITUTE(SUBSTITUTE(ASC($B2),"゛",),"゜",))=LEN($B2)),COUNTIF($B:$B,"<"&$B2)+COUNTIF($B$1:$B1,$B2)-COUNTIF($B:$B,"<>")+COUNTIF($B:$B,"*?"),"")

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

=IF(ROWS($2:2)>COUNT($A:$A),"",LOOKUP(VLOOKUP(SMALL($A:$A,ROWS($2:2)),$A:$B,2,FALSE),{"あ","か","さ","た","な","は","ま","や","ら","わ"})&":"&INDEX(Sheet1!$B:$B,MATCH(SMALL($A:$A,ROWS($2:2)),$A:$A,0))&"#"&COUNTIF($D$1:$D1,LOOKUP(VLOOKUP(SMALL($A:$A,ROWS($2:2)),$A:$B,2,FALSE),{"あ","か","さ","た","な","は","ま","や","ら","わ"})&":"&INDEX(Sheet1!$B:$B,MATCH(SMALL($A:$A,ROWS($2:2)),$A:$A,0))&"#*")+1)

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

=IF(COUNTIF($D$1:$D2,LEFT($D2,1)&"*")=1,COUNT(C$1:C1)+1,"")

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

=IF($D2="","",INDEX(Sheet1!$A:$A,MATCH(SMALL($A:$A,ROWS($2:2)),$A:$A,0)))

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

=IF(ISNUMBER(1/COUNTIF($D$1:$D1,LEFT($D2,FIND("#",$D2))&"*")),VLOOKUP(LEFT($D2,FIND("#",$D2))&COUNTIF($D$1:$D1,LEFT($D2,FIND("#",$D2))&"*"),$D:$E,COLUMNS($D:$E),FALSE)&CHAR(10),"")&$E2

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

※まだ途中なのですが、回答欄に入力可能な文字数の限界に達しそうになりましたので、残りはまた後で投稿させて頂きます。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

いきなり間違ってました

× 1. [Ctrl]+[F11]VBE
　 　 　 　↓
○ 1. [Alt]+[F11]VBE

回答No.3

某HN クロメート(Chromate)（@CoalTar）

>・KURUMITOさんの、フリガナをもとに計算するやり方は前の作業の都合上できなくなってしまいました
ふりがなをふればよいと思います。(ひらがなとして)
どちらにせよ、五十音順にするにはふりがなの情報は欠かせません

1. [Ctrl]+[F11]VBE
2. 挿入 - 標準モジュール
3. 下記貼り付け
Sub ふりがなをふるって表示する()
Selection.SetPhonetic
Selection.Phonetics.CharacterType = xlHiragana
Selection.Phonetics.Visible = True
End Sub

4. A列を選択して
5. [Alt]+[F8]マクロの実行 - ふりがなをふるって表示する
※ただし、間違って降られる可能性もあるので確認が必要です

>・keithinさんのやり方だと、同じ地名が別のセルにも出てきてしまいました
添付図の 8行目、9行目のようなダブったデータがあるのでは？
そして、ちょっと、改良。
ふりがなを振ったデータの頭文字を分類分けします。濁点、半濁点は無視。
C1セルに =LOOKUP(PHONETIC(A1),{"あ","か","さ","た","な","は","ま","や","ら","わ"})
下へオートフィル

添付図参照 参考まで

回答No.2

K Kazz（@JazzCorp）

Sheet1（データ元）は１行目からデータです。
Ｅ１の式にはちょっとだけミスプリがありますね。そのまま書いてはダメです。
そのくらいは、自分でデバッグしてください。

（正）
=A1&IF(COUNTIF(D2:D$999,D1),CHAR(10)&VLOOKUP(D1,D2:E$999,2,FALSE),"")

（誤）
=A1&IF(COUNTIF(D2:D999,D1),CHAR(10)&VLOOKUP(D2,D2:E999,2,FALSE),"")

回答No.1

since_1968

エクセルのピポッドテーブルを使えば、別の表（シート）にデータを変換できるはずです。一度設定すればデータ更新で元データの変更にかわります。一度試してみてください