締切済み

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

2012/08/14 11:54

すいません、以前も質問をさせていただいた内容なのですがちょっと結果が求めていたものと異なっていたため再度の質問をさせてください。 http://okwave.jp/qa/q7613199.html ・KURUMITOさんの、フリガナをもとに計算するやり方は前の作業の都合上できなくなってしまいました・keithinさんのやり方だと、同じ地名が別のセルにも出てきてしまいましたどなたか教えていただければ幸いです。よろしくお願いします。ーーーーーーーーーーーーーーーーーーーーーーーーエクセルで元データから以下のような形に配置変更がしたく、関数か何かを使って一括でできる方法を教えていただけないでしょうか？・元データ　　Ａ　　　　　Ｂ１　荒川区　300 ２　江東区　300 ３　中央区　400 ４　稲城市　400 ５　江戸川区　400 ６　品川区　500 ７　北区　　500 ８　狛江市　500 ・希望の形　金額にマッチするものを列に分けて、かつ五十音順に同一セル内に複数記述したいです。　　　Ａ　　　　Ｂ　　　　Ｃ　　　　　　　Ｄ１　　　　　　300　　　　400　　　　　500 ２　あ　　　　荒川区　　稲城市　　　　　　　　　　　　　江戸川区　　３　か　　　　江東区　　　　　　　　北区　　　　　　　　　　　　　　　　　　　　狛江市４　さ　　　　　　　　　　　　　　　　　品川区５　た　　　　　　　　　　中央区エクセル2003、windowsXPです。どうぞ宜しくお願いします

wenze
お礼率25% (2/8)

その他MS Office製品
回答数6
ありがとう数0

みんなの回答 （6）
専門家の回答

みんなの回答

kagakusuki
ベストアンサー率51% (2610/5101)

2012/08/15 15:23 回答No.6

　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２に配置変更された表が自動的に表示されます。

kagakusuki
ベストアンサー率51% (2610/5101)

2012/08/15 15:09 回答No.5

>フリガナをもとに計算するやり方は前の作業の都合上できなくなってしまいましたとの事ですが、「ふりがな」という何と読めば良いのかという情報が無ければ、五十音順に並べ替える事は出来ません。　ですから、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行目以下に貼り付けて下さい。 ※まだ途中なのですが、回答欄に入力可能な文字数の限界に達しそうになりましたので、残りはまた後で投稿させて頂きます。

某HN クロメート(Chromate)（@CoalTar）
ベストアンサー率40% (705/1742)

2012/08/14 16:46 回答No.4

いきなり間違ってました × 1. [Ctrl]+[F11]VBE 　　　　↓ ○ 1. [Alt]+[F11]VBE

某HN クロメート(Chromate)（@CoalTar）
ベストアンサー率40% (705/1742)

2012/08/14 16:45 回答No.3

>・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),{"あ","か","さ","た","な","は","ま","や","ら","わ"}) 下へオートフィル添付図参照参考まで

K Kazz（@JazzCorp）
ベストアンサー率31% (549/1751)

2012/08/14 16:08 回答No.2

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),"")