Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


9432 / 13644 ツリー ←次へ | 前へ→

【27396】住所別に振分・・・ エクセル初心者 05/8/8(月) 9:23 質問[未読]
【27398】Re:住所別に振分・・・ ちくたく 05/8/8(月) 10:33 回答[未読]
【27399】Re:住所別に振分・・・ Jaka 05/8/8(月) 11:37 回答[未読]
【27402】Re:住所別に振分・・・ りん 05/8/8(月) 12:55 回答[未読]
【27404】Re:住所別に振分・・・ エクセル初心者 05/8/8(月) 13:13 質問[未読]
【27407】Re:住所別に振分・・・ りん 05/8/8(月) 14:33 回答[未読]
【27405】Re:住所別に振分・・・ エクセル初心者 05/8/8(月) 13:26 発言[未読]

【27396】住所別に振分・・・
質問  エクセル初心者  - 05/8/8(月) 9:23 -

引用なし
パスワード
   お願い致します。

あるシートにお客様情報(名称・住所・電話番号)の表(3万件ほど)があります。

それをどうしても、都道府県別に一発で振り分けれないかと思いました。
どなたか良い方法をご存知ないでしょうか?
1シート1県とかでもいいです。

エクセルのオートフィルタのオプションでやると2条件が限界で、今まではそれを手で何回も繰り返してやっていましたが(頻度が少なかったため)が毎月やらなければならなくなったのでとても苦になっています。

どうかお助けください。

よろしくお願い致します。

【27398】Re:住所別に振分・・・
回答  ちくたく  - 05/8/8(月) 10:33 -

引用なし
パスワード
   どんなデータかわかりませんけど、
例えば、下のような感じですかね?
A列を精査して、東京都のものをシート2にコピーします。

Sub 一定の数字以上の行を抜き出す()

Dim i As Integer
Dim sheet2Columns As Integer

sheet2Columns = 1

For i = 1 To 100

  If Sheets(1).Range("A" & i).Value = "東京都" Then
  
    Worksheets(1).Rows(i).Copy Worksheets(2).Rows(sheet2Columns)
    sheet2Columns = sheet2Columns + 1

  End If

Next i

End Sub


▼エクセル初心者 さん:
>お願い致します。
>
>あるシートにお客様情報(名称・住所・電話番号)の表(3万件ほど)があります。
>
>それをどうしても、都道府県別に一発で振り分けれないかと思いました。
>どなたか良い方法をご存知ないでしょうか?
>1シート1県とかでもいいです。
>
>エクセルのオートフィルタのオプションでやると2条件が限界で、今まではそれを手で何回も繰り返してやっていましたが(頻度が少なかったため)が毎月やらなければならなくなったのでとても苦になっています。
>
>どうかお助けください。
>
>よろしくお願い致します。

【27399】Re:住所別に振分・・・
回答  Jaka  - 05/8/8(月) 11:37 -

引用なし
パスワード
   >それをどうしても、都道府県別に一発で振り分けれないかと思いました。
>どなたか良い方法をご存知ないでしょうか?
>1シート1県とかでもいいです。
>エクセルのオートフィルタのオプションでやると2条件が限界で、今まではそれを手で何回も繰り返してやっていましたが(頻度が少なかったため)が毎月やらなければならなくなったのでとても苦になっています。
意味が良くわかりません。
同僚にでも、この説明で何をしたいのか解るか聞いてみてください。
具体的に説明してください。


こういうのを
東京都新宿区…
大阪府〇〇…
京都府〇〇…
北海道〇〇…
島根県〇〇…
和歌山県〇〇…
鹿児島県〇〇…
神奈川県〇〇…

こういう風にしたいということでしょうか?
 C    D
東京都  新宿区…
大阪府  〇〇…
京都府  〇〇…
北海道  〇〇…
島根県  〇〇…
和歌山県 〇〇…
鹿児島県 〇〇…
神奈川県 〇〇…

これだったら、
都道府県の抽出
=IF(MID(A1,4,1)="県",LEFT(A1,4),LEFT(A1,3))
都道府県以降の抽出(剥出す文字数は、50文字で不十分でしたら増やしてください。)
=IF(MID(A1,4,1)="県",MID(A1,5,50),MID(A1,4,50))

都道府県名は、4文字と3文字の物しかないので、4文字目が県かどうかで振り分けられます。
都道府県名が4文字の物は、和歌山県、鹿児島県、神奈川県の3県のみ他は全部3文字。

それとも、これ?
オートフィルタの絞込み
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=84;id=FAQ

【27402】Re:住所別に振分・・・
回答  りん E-MAIL  - 05/8/8(月) 12:55 -

引用なし
パスワード
   エクセル初心者 さん、こんにちわ。

・1行目は見出し
・C列に住所が都道府県から入っている
・IV列まで使われていない
アクティブシートの内容が、上記条件を満たすとして。

Sub TEST()
  Dim Rmax As Long, Cmax As Long, RR1 As Long, RR2 As Long
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim r1 As Range
  '
  ActiveWorkbook.ActiveSheet.Copy '表示しているシートを新しいブックにコピー
  Set ws1 = ActiveWorkbook.ActiveSheet 'セット
  With ws1.UsedRange
   Rmax = .Cells(.Count).Row
   Cmax = .Cells(.Count).Column
  End With
  With ws1
   .Cells(1, Cmax + 1).Value = 1
   .Cells(2, Cmax + 1).Value = 2
   '連番をふる
   Set r1 = .Range(.Cells(1, Cmax + 1), .Cells(Rmax, Cmax + 1))
   .Range(.Cells(1, Cmax + 1), .Cells(2, Cmax + 1)).AutoFill _
    Destination:=r1
   '1行目は見出し、C列でソート
   r1.EntireRow.Sort Key1:=.Range("C1"), Order1:=xlAscending, Header:=xlYes, _
     OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
     :=xlStroke
   '
   Do
     RR1 = 2: RR2 = 2
     'C列が住所(都道府県が必ず入っていること)
     If .Cells(RR1, 3).Value = "" Then Exit Do
     Do
      '左3文字で比較する
      If Left(.Cells(RR1, 3).Value, 3) <> _
        Left(.Cells(RR2 + 1, 3).Value, 3) Then Exit Do
      RR2 = RR2 + 1
     Loop
     '
     With .Parent
      Set ws2 = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
     End With
     '
     ws2.Name = Left(.Cells(RR1, 3).Value, 3)
     '
     .Rows(1).Copy
     ws2.Cells(1, 1).PasteSpecial Paste:=-4104
     ws2.Cells(1, 1).PasteSpecial Paste:=8 '列幅
     With .Range(.Cells(RR1, 1), .Cells(RR2, 1)).EntireRow
      .Cut Destination:=ws2.Cells(2, 1)
      .Delete
     End With
     '元の並びに戻す
     With ws2
      .UsedRange.Sort Key1:=.Cells(1, Cmax + 1), Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
      :=xlStroke
      .Columns(Cmax + 1).Delete 'ソートキー削除
     End With
     Application.CutCopyMode = False
   Loop
   Application.DisplayAlerts = False
   .Delete '元データをコピーしたシートを削除
   Application.DisplayAlerts = True
  End With
  Set ws2 = Nothing: Set ws1 = Nothing
End Sub

こんな感じです。
動作としては、
 連番をふる
 都道府県列でソート(ふりがなは使わない)

その後、 

 連続範囲を判定して新しいシートに振り分ける
 元の並びにソート
 ソートキーを削除
を繰返しています。

【27404】Re:住所別に振分・・・
質問  エクセル初心者  - 05/8/8(月) 13:13 -

引用なし
パスワード
   皆さん本当に迅速な回答ありがとうございます。

早速使って見ます!!
本当にありがとうございます!!

それと、説明不足の補足をさせていただきます。

表の内容は

A〜I列まではお客様データ そしてJ列に住所が 「何々県何々区何々町・・・」といった感じにあります。
そこでどんな方法でも良いので、都道府県ごとのシート振分をしたいのです。

質問するのも初心者で誠に申し訳ございませんが何卒よろしくお願い致します。

【27405】Re:住所別に振分・・・
発言  エクセル初心者  - 05/8/8(月) 13:26 -

引用なし
パスワード
   りん さんへ

私の使い方がおかしいのか、うまく実行できません。

そのままコピーして貼り付けて使っています。
りんさんからの条件もすべて満たしていますが・・・

「実行時エラー'1004'
ブックのシートをすべて削除または非表示にすることは出来ません。
選択したシートを非表示、削除、または移動するには、まず新しいシートを挿入するか、非表示のシートを表示してください。」

というエラーが出ます。

デバックすると、下から五行目

.Delete '元データをコピーしたシートを削除

が黄色で塗りつぶされています。


お助け下さい。

よろしくお願い致します。


>エクセル初心者 さん、こんにちわ。
>
>・1行目は見出し
>・C列に住所が都道府県から入っている
>・IV列まで使われていない
>アクティブシートの内容が、上記条件を満たすとして。
>
>Sub TEST()
>  Dim Rmax As Long, Cmax As Long, RR1 As Long, RR2 As Long
>  Dim ws1 As Worksheet, ws2 As Worksheet
>  Dim r1 As Range
>  '
>  ActiveWorkbook.ActiveSheet.Copy '表示しているシートを新しいブックにコピー
>  Set ws1 = ActiveWorkbook.ActiveSheet 'セット
>  With ws1.UsedRange
>   Rmax = .Cells(.Count).Row
>   Cmax = .Cells(.Count).Column
>  End With
>  With ws1
>   .Cells(1, Cmax + 1).Value = 1
>   .Cells(2, Cmax + 1).Value = 2
>   '連番をふる
>   Set r1 = .Range(.Cells(1, Cmax + 1), .Cells(Rmax, Cmax + 1))
>   .Range(.Cells(1, Cmax + 1), .Cells(2, Cmax + 1)).AutoFill _
>    Destination:=r1
>   '1行目は見出し、C列でソート
>   r1.EntireRow.Sort Key1:=.Range("C1"), Order1:=xlAscending, Header:=xlYes, _
>     OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
>     :=xlStroke
>   '
>   Do
>     RR1 = 2: RR2 = 2
>     'C列が住所(都道府県が必ず入っていること)
>     If .Cells(RR1, 3).Value = "" Then Exit Do
>     Do
>      '左3文字で比較する
>      If Left(.Cells(RR1, 3).Value, 3) <> _
>        Left(.Cells(RR2 + 1, 3).Value, 3) Then Exit Do
>      RR2 = RR2 + 1
>     Loop
>     '
>     With .Parent
>      Set ws2 = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
>     End With
>     '
>     ws2.Name = Left(.Cells(RR1, 3).Value, 3)
>     '
>     .Rows(1).Copy
>     ws2.Cells(1, 1).PasteSpecial Paste:=-4104
>     ws2.Cells(1, 1).PasteSpecial Paste:=8 '列幅
>     With .Range(.Cells(RR1, 1), .Cells(RR2, 1)).EntireRow
>      .Cut Destination:=ws2.Cells(2, 1)
>      .Delete
>     End With
>     '元の並びに戻す
>     With ws2
>      .UsedRange.Sort Key1:=.Cells(1, Cmax + 1), Order1:=xlAscending, Header:=xlYes, _
>      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
>      :=xlStroke
>      .Columns(Cmax + 1).Delete 'ソートキー削除
>     End With
>     Application.CutCopyMode = False
>   Loop
>   Application.DisplayAlerts = False
>   .Delete '元データをコピーしたシートを削除
>   Application.DisplayAlerts = True
>  End With
>  Set ws2 = Nothing: Set ws1 = Nothing
>End Sub
>
>こんな感じです。
>動作としては、
> 連番をふる
> 都道府県列でソート(ふりがなは使わない)
>
>その後、 
>
> 連続範囲を判定して新しいシートに振り分ける
> 元の並びにソート
> ソートキーを削除
>を繰返しています。

【27407】Re:住所別に振分・・・
回答  りん E-MAIL  - 05/8/8(月) 14:33 -

引用なし
パスワード
   エクセル初心者 さん、こんにちわ。

>表の内容は
>A〜I列まではお客様データ そしてJ列に住所が 「何々県何々区何々町・・・」といった感じにあります。

C列でチェック→J列でチェックにしました。
Sub TEST()
  Dim Rmax As Long, Cmax As Long, RR1 As Long, RR2 As Long
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim r1 As Range
  '
  ActiveWorkbook.ActiveSheet.Copy '表示しているシートを新しいブックにコピー
  Set ws1 = ActiveWorkbook.ActiveSheet 'セット
  With ws1.UsedRange
   Rmax = .Cells(.Count).Row
   Cmax = .Cells(.Count).Column
  End With
  With ws1
   .Cells(1, Cmax + 1).Value = 1
   .Cells(2, Cmax + 1).Value = 2
   '連番をふる
   Set r1 = .Range(.Cells(1, Cmax + 1), .Cells(Rmax, Cmax + 1))
   .Range(.Cells(1, Cmax + 1), .Cells(2, Cmax + 1)).AutoFill _
    Destination:=r1
   '1行目は見出し、J列でソート
   r1.EntireRow.Sort Key1:=.Cells(1, 10), Order1:=xlAscending, Header:=xlYes, _
     OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
     :=xlStroke
   '
   Do
     RR1 = 2: RR2 = 2
     'J列が住所(都道府県が必ず入っていること)
     If .Cells(RR1, 10).Value = "" Then Exit Do
     Do
      '左3文字で比較する
      If Left(.Cells(RR1, 10).Value, 3) <> _
        Left(.Cells(RR2 + 1, 10).Value, 3) Then Exit Do
      RR2 = RR2 + 1
     Loop
     '
     With .Parent
      Set ws2 = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
     End With
     '
     ws2.Name = Left(.Cells(RR1, 10).Value, 3)
     '
     .Rows(1).Copy
     ws2.Cells(1, 1).PasteSpecial Paste:=-4104
     ws2.Cells(1, 1).PasteSpecial Paste:=8 '列幅
     With .Range(.Cells(RR1, 1), .Cells(RR2, 1)).EntireRow
      .Cut Destination:=ws2.Cells(2, 1)
      .Delete
     End With
     '元の並びに戻す
     With ws2
      .UsedRange.Sort Key1:=.Cells(1, Cmax + 1), Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
      :=xlStroke
      .Columns(Cmax + 1).Delete 'ソートキー削除
     End With
     Application.CutCopyMode = False
   Loop
   If .Parent.Worksheets.Count = 1 Then
     MsgBox "J2セルを確認", vbExclamation, "振り分け失敗?"
   Else
     Application.DisplayAlerts = False
     .Delete '元データをコピーしたシートを削除
     Application.DisplayAlerts = True
   End If
  End With
  Set ws2 = Nothing: Set ws1 = Nothing
End Sub

シートを削除する際にエラーになったのは、振り分けに失敗した状態で(2行目が空欄だったりとか)、新しいシートに振り分けできていないのに、元のシートを削除しようとしたからです。

9432 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free