• 締切済み

エクセルを使ったリストの整備について

エクセルを使ったリストの整備について教えてください。 図の様にABCD列にそれぞれ県、市、区とエリアデータが約100地域あり、 F列に住所氏名を記したデータが約10000あります。 これらを最終的に、エリアごとの該当人数と、該当者一覧に分けたいのですが、 どの様に行うのが一番効率的でしょうか? 教えてください。よろしくお願いします。

みんなの回答

  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.4

関数を使って処理する方法です。 データが多いので作業列を多くしてできるだけ分かり易く簡単に処理することにします。作業列が目障りでしたら作業後にそれらの列を非表示にすればよいでしょう。 お示しのデータがシート1のA列からF列までで1行目からではなく2行目から下方に入力されているとします。 初めにE列は作業列としてE2セルには次の式を入力して下方にオートフィルドラッグコピーします。 =IF(C2="","",LOOKUP("ーー",A$2:A2)&LOOKUP("ーー",B$2:B2)&LOOKUP("ーー",C$2:C2)) ここではーーについては半角の--のようになっていてはいけません。必ずひらがな入力モードでーーと入力してください。式では一旦半角のまますべてを入力してからひらがな入力モードにして--の部分をーーに変えるとよいでしょう。 次に該当者の数値をF列に表示させることにして列の挿入を行います。これによって住所氏名のF列はG列に移動することになります。 F2セルには次の式を入力して下方にオートフィルドラッグコピーします。 =IF(E2="","",COUNTIF(G:G,E2&"*")) これでF列には該当者の数が表示されることになります。 H2セルにはそれぞれの住所氏名がパターン幾つになっているかを表示させるために次の式を入力し下方にオートフィルドラッグコピーします。 =IF(G2="","",IF(COUNTIF(G2,"*区*"),INDEX(D:D,MATCH(LEFT(G2,FIND("区",G2)),E:E,0)),IF(COUNTIF(G2,"*町*"),INDEX(D:D,MATCH(LEFT(G2,FIND("町",G2)),E:E,0)),""))) I2セルには次の式を入力して下方にオートフィルドラッグコピーします。 =IF(H2="","",IF(COUNTIF(H$2:H2,H2)=1,INT(MAX(I$1:I1)/10000)*10000+10001,INT(INDEX(I$1:I1,MATCH(H2,H:H,0))/10000)*10000+COUNTIF(H$1:H2,H2))) これでシート1での作業は終わって、シート2にはパターンごとの住所氏名を表示させることにします。 シート2のA2セルには次の式を入力して右横方向にオートフィルドラッグコピーします。 =IF(COUNTIF(Sheet1!$I:$I,COLUMN(A1)*10000+1)=0,"",INDEX(Sheet1!$H:$H,MATCH(COLUMN(A1)*10000+1,Sheet1!$I:$I,0))) A3セルには次の式を入力して右横方向にオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。 =IF(A$2="","",IF(COUNTIF(Sheet1!$I:$I,COLUMN(A1)*10000+ROW(A1)),INDEX(Sheet1!$G:$G,MATCH(COLUMN(A1)*10000+ROW(A1),Sheet1!$I:$I,0)),""))

全文を見る
すると、全ての回答が全文表示されます。
  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.3

No.2です! たびたびごめんなさい。 前回のコードで一部間違いがありました。 下から○行目といっても判り難いと思いますので、 もう一度コードを載せておきます。 (前回のコードではSheet2の表示数が極端にすくなくなってしまいます。) Sub test() 'この行から Dim i, j As Long Dim ws2, ws3 As Worksheet Set ws2 = Worksheets(2) Set ws3 = Worksheets(3) Application.ScreenUpdating = False Worksheets(1).Cells.Copy Destination:=ws3.Cells(1, 1) ws3.Cells.UnMerge For i = 2 To ws3.Cells(Rows.Count, 3).End(xlUp).Row If ws3.Cells(i, 1) = "" Then ws3.Cells(i, 1) = ws3.Cells(i - 1, 1) End If If ws3.Cells(i, 2) = "" Then ws3.Cells(i, 2) = ws3.Cells(i - 1, 2) End If ws3.Cells(i, 5) = WorksheetFunction.CountIf(ws3.Columns(6), _ ws3.Cells(i, 1) & ws3.Cells(i, 2) & ws3.Cells(i, 3) & "*") Next i ws3.Columns(5).Copy Destination:=Worksheets(1).Cells(1, 5) For i = 2 To ws3.Cells(Rows.Count, 6).End(xlUp).Row For j = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row If ws3.Cells(i, 6) Like ws3.Cells(j, 1) & ws3.Cells(j, 2) & _ ws3.Cells(j, 3) & "*" Then ws3.Cells(i, 7) = ws3.Cells(j, 4) End If Next j Next i j = 0 For i = 2 To ws3.Cells(Rows.Count, 4).End(xlUp).Row If WorksheetFunction.CountIf(ws2.Rows(1), ws3.Cells(i, 4)) = 0 Then j = j + 1 ws2.Cells(1, j) = ws3.Cells(i, 4) End If Next i For i = 2 To ws3.Cells(Rows.Count, 6).End(xlUp).Row '←この行を訂正しました。 For j = 1 To ws2.Cells(1, Columns.Count).End(xlToLeft).Column If ws3.Cells(i, 7) = ws2.Cells(1, j) Then ws2.Cells(Rows.Count, j).End(xlUp).Offset(1) = ws3.Cells(i, 6) End If Next j Next i ws2.Columns.AutoFit ws2.Rows(1).HorizontalAlignment = xlCenter ws3.Cells.Delete Application.ScreenUpdating = True End Sub 'この行まで 何度も失礼しました。m(_ _)m

全文を見る
すると、全ての回答が全文表示されます。
  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.2

こんにちは! VBAでの一例です。 ↓の画像のようにSheet1にデータがあり、データは2行目以降に入っているとします。 セルの結合があると難しいので、Sheet3を作業用のSheetとして使っています。 ※ Sheet3はデータはなく白紙状態にしておきます。 人数はSheet1のE列に表示させ、 パターンごとのデータはSheet2に表示させるようにしてみました。 画面左下のSheet1のSheet見出し上で右クリック → コードの表示 → VBE画面が出ますので ↓のコードをコピー&ペーストしてマクロを実行してみてください。@ (Alt+F8キー → マクロ → マクロ実行です) Sub test() 'この行から Dim i, j As Long Dim ws2, ws3 As Worksheet Set ws2 = Worksheets(2) Set ws3 = Worksheets(3) Application.ScreenUpdating = False Worksheets(1).Cells.Copy Destination:=ws3.Cells(1, 1) ws3.Cells.UnMerge For i = 2 To ws3.Cells(Rows.Count, 3).End(xlUp).Row If ws3.Cells(i, 1) = "" Then ws3.Cells(i, 1) = ws3.Cells(i - 1, 1) End If If ws3.Cells(i, 2) = "" Then ws3.Cells(i, 2) = ws3.Cells(i - 1, 2) End If ws3.Cells(i, 5) = WorksheetFunction.CountIf(ws3.Columns(6), _ ws3.Cells(i, 1) & ws3.Cells(i, 2) & ws3.Cells(i, 3) & "*") Next i ws3.Columns(5).Copy Destination:=Worksheets(1).Cells(1, 5) For i = 2 To ws3.Cells(Rows.Count, 6).End(xlUp).Row For j = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row If ws3.Cells(i, 6) Like ws3.Cells(j, 1) & ws3.Cells(j, 2) & _ ws3.Cells(j, 3) & "*" Then ws3.Cells(i, 7) = ws3.Cells(j, 4) End If Next j Next i j = 0 For i = 2 To ws3.Cells(Rows.Count, 4).End(xlUp).Row If WorksheetFunction.CountIf(ws2.Rows(1), ws3.Cells(i, 4)) = 0 Then j = j + 1 ws2.Cells(1, j) = ws3.Cells(i, 4) End If Next i For i = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row For j = 1 To ws2.Cells(1, Columns.Count).End(xlToLeft).Column If ws3.Cells(i, 7) = ws2.Cells(1, j) Then ws2.Cells(Rows.Count, j).End(xlUp).Offset(1) = ws3.Cells(i, 6) End If Next j Next i ws2.Columns.AutoFit ws2.Rows(1).HorizontalAlignment = xlCenter ws3.Cells.Delete Application.ScreenUpdating = True End Sub 'この行まで ※ For~Next を多用していますので、少し時間がかかるかもしれません。 お役に立ちますかね?m(_ _)m

全文を見る
すると、全ての回答が全文表示されます。
  • misawajp
  • ベストアンサー率24% (918/3743)
回答No.1

何が一番効率的かは、質問者の主観に依存します A,B,Cでソートし Cを基準にSUBTOTAL で 集計してみたら

全文を見る
すると、全ての回答が全文表示されます。

関連するQ&A

  • エクセルでのリストの並べ替えができない・・・

    エクセル初心者です。 それぞれ氏名、住所、金額、摘要という項目を作ってリスト(以下のような)を作成し、一番最後に合計人数と合計金額の欄を作りました。 ○田○子 △△市△番地△号 1000円 交通費 ◇川◇男 ■■町■番地■号 3000円 交通費 このリストを氏名のあいうえお順ですべて並べ替えるようにしたいのですが、 並べ替えボタンを押しても「この操作には、同じサイズの結合セルが必要です」という表示が出て並べ替えができません。 氏名の列のみ選択してあいうえお順に並べ替えることはできるのですが、 それでは住所や金額がバラバラになってしまいます。 私が作ったリストでは並べ替えができないのでしょうか? できれば、初心者にもわかるようなアドバイスで教えていただけるとありがたいです。 宜しくお願いします。

  • エクセルでの 「入力規則」→「リスト」に関する質問

    (1)セルA列に氏名を、B列に氏名に付随する固有の番号を一覧表的に入力しておき、 (2)セルD1に、入力規則→リスト でA列のデータを選べるように設定したときに(A列を「元の値」にするときに) (3)セルD1に選んだA列のデータを表示したとき、その次行のセルD2に、A列の氏名に付随するB列のデータ(Aに付随する固有の番号)を自動的に表示する方法がありますか。

  • マクロでリスト選択削除したい

    無知な私に教えて下さい 現在以下の内容で、名前と住所と電話番号が転記されるマクロを使用しています。 入力時は、ダイアログが表示されます。  I列に名前 H列に住所 J列に電話番号 Private Sub AddButton_Click() '住所録追加 Dim rowscount As Long '行数獲得 rowscount = Range("住所録一覧").Rows.Count '最下行追加 Range("住所録一覧").Cells(rowscount, 1).EntireRow.Insert 'データーセル入力 Range("住所録一覧").Cells(rowscount, 1) = Texts Range("住所録一覧").Cells(rowscount, 2) = Textj Range("住所録一覧").Cells(rowscount, 3) = Textt Unload Me End Sub 上記で出来たリストをデータ入力規則に下記式を入れリストとして使用しています。 =OFFSET(住所録,0,0,ROWS(住所録一覧)-1,1) 今困っているのが、削除する際に3セルを手動で削除しています。 希望は 名前の列を検索し(リスト選択)、住所と電話番号の3つのセルを削除し、上に行を詰めたいです。(msgboxで確認後) リストは、”住所録一覧”と範囲に名前をつけています。 I列の名前の欄には、住所録と別の範囲の名前をつけてます。 リストから検索し、消していいかのダイアログ確認後、該当セル3つの列を削除し、上に行を詰める式をご教授下さいませ。 もう二日悩んでます。 宜しくお願い致します。

  • リストボックスのイベントプロシージャー

    「氏名一覧」というフォームに、該当する氏名が リストボックスの中に表示されています。 リストボックスから1名氏名を選んだ時(ダブルクリックした時)に、 その選んだ人の個人情報のフォームを開くようにしたいのですが、 「氏名一覧」リストボックスの [ダブルクリック時]イベントプロシージャーでのコードの記述の仕方が分かりません。どなたか教えて下さい。宜しくお願いします。 ACCESS97を使っています。

  • エクセルVABでリストボックスで選択

    左右にリストボックスを2つ並べて、左のリストボックスのリストからコマンドボタンを使って右のリストボックスに移すようなダイアログはよく見かけます。エクセルVBAで、実現するには、やはりシートにデータを保存するのでしょうか?また、順番を元通り(ソートされたものではない)にするためには、元のデータのリストを表すセル列を用意しておいて、リストがチェンジするたびに、左側のリストの列と右側のリストの列のセルを整備するしかないのでしょうか? どなたか、詳しい方いらっしゃいましたらよろしくお願いいたします。

  • EXCELで入力と同時に昇順表示がしたい

    お世話になります、エクセル初心者です。 氏名を入力と同時に、昇順表示にしたいのですが ネットでいろんな所を探しても分かりません。 こちらでも探してみたのですが見つからず、お知恵拝借させていただければと思い 投稿させていただきました。 仕事で、ひと月の実績を昇順で氏名をリスト作成しているのですが 空き時間に実績がある方を入力できれば、少しでも効率化が図れるので助かります。 (現在ある2種類のシート) (1)氏名入力をすると住所が自動表示されるようにVLOOKUPで関数を入れたシート。 (2)(1)にデータが飛ぶようにするための、指名・住所の一覧 これとは別に氏名入力をしていくだけで、そのつど昇順になるシートができれば (1)のシートに氏名貼り付けをして、住所を引っ張ってこれるようにしたいのです。

  • エクセルでこのリストの加工をしたいのですが

    A列(ID)  B列(エラーコード)  C列(日付)に A000  01;02  4/1 A019  03  4/10 A008  02;05;07  3/29 ・ ・ ・ のように入っているデータがあり、 B列には「;」セミコロンで区切られたデータが1~25個入っています。 この全リストを下記のように加工したいのですが可能でしょうか? A列  B列  C列に A000  01  4/1 A000  02  4/1 A019  03  4/10 A008  02  3/29 A008  05  3/29 A008  07  3/29 ・ ・ ・ B列のエラーコード毎(「;」毎)のレコードに加工したいのです。 (エラー数、エラー種別数ランキング等集計のため) 標準関数と多少の手作業でもマクロでもVBAでも構わないのですが、 元リストが8000レコードあるので効率よく加工する方法があれば教えてください。

  • リストボックスの使い方

    お世話になります。 VBAで下記コードを書き、リストボックスにカラムを2列 表示させたいのですが、"番号"しかリストボックスに 表示されません。 Me.lst入居者一覧.ColumnCount = 2 Me.lst入居者一覧.RowSource = "SELECT 氏名,番号 FROM 一覧" Me.lst入居者一覧.ColumnWidths = "2;2" リストボックスのプロパティー等はちゃんと設定して いるつもりなのですが、何が原因なのでしょう? どうかよろしくお願いいたします。

  • excelのリスト絞り込みで困っています。

    下記のようなデータがシートにあった場合 A列         B列         C列 商品コード     商品コード1     品番 AA          AA          A1 AB          AA          A2 AC          AA          A3 ZZ          AB          B1            AC          C1 D列に商品コードの入力規則によるリストを指定。 E列に品番の入力規則によるリストを指定し 選ばれた商品コードに該当する品番を E列のリストに表示したい場合はどうしたらよいのかご教授ください。 D列のリストでAAを選んだら E列の品番のリストにはA1とA2とA3が出るというイメージです。 その際にZZを選ばれた場合は、品番のリストが無いのでエラーとする方法も あわせて教えてください。

  • Excelでリストを並べ替えるには?

    Excelでの下記のやり方がわかりません。 たくさんの人の属性が書かれたリストがあります。(氏名、住所、性別、備考等) 行方向にズラっと多数の人が任意に打ち込まれ、列方向に属性の内訳があります。 Q1.性別の「男女」が任意でバラバラにリスト化されているので、「男」が表の上部分にまとめて表示されるように並べ替えてください。 という作業をする場合、手順はどうすればいいのでしょうか? 範囲指定して、「並べ替え」とかを使うのでしょうか? サクっと並び替える方法をご指南のほど、どうぞよろしくお願い致します。 ちなみに自分のスキルは、関数やグラフ作成等ができ、Excel経験は長いのですが、この作業を前に悩んでいます。

このQ&Aのポイント
  • パソコンからの印刷が基本カラーなので、基本設定を白黒にしたい
  • お使いのパソコンはWindows10で、印刷機器はMFC-J738DNです
  • 接続は無線LANで、電話回線はひかり回線です
回答を見る

専門家に質問してみよう