VBA練習問題
VBA100本ノック 74本目:1シート複数表をDB形式に変換

VBAを100本の練習問題で鍛えます
最終更新日:2021-02-22

VBA100本ノック 74本目:1シート複数表をDB形式に変換


1シートで取引先ごとに別表になっているデータをまとめてDB形式に出力する問題です。


ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。


出題

出題ツイートへのリンク

#VBA100本ノック 74本目
「売上」シートには、A列B列に取引先コードと名称があり、その下に見出し行から始まるデータがあります。
「DB」シートにデータベース形式で出力してください。
見出し行は文字列も含め統一されています。
取引先ごとの行数は不定です。
※「DB」は既存で見出しも書式も設定済

マクロ VBA 100本ノック

マクロ VBA 100本ノック


サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_74.xlsm
https://excel-ubara.com/vba100sample/VBA100_74.zip


VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。


他の人の回答および解説を見て、書いたVBAを見直してみましょう。


頂いた回答

解説

この処理は書き方が人それぞれ様々だと思います。
どのセルをどのように判定して各行を決定するか、シートの状態から判断することになります。
絶対的な正解はありませんので、まずはサンプルが処理出来れば良いと思います。
さらには、できれば多少のレイアウト変更にも対応したいところです。

Sub VBA100_74_01()
  Dim ws売上 As Worksheet: Set ws売上 = Worksheets("売上")
  Dim wsDB As Worksheet:  Set wsDB = Worksheets("DB")
  wsDB.Range("A1").CurrentRegion.Offset(1).ClearContents
  
  Dim tCd As String    '取引先CD
  Dim tName As String   '取引先名
  Dim i As Long      '売上の行
  Dim j As Long      '売上の列
  Dim ix As Long     '見出し行
  Dim maxRow As Long   '最終行
  Dim maxCol As Long   '最終列
  Dim oRow As Long    'DBの行
  Dim ary(1 To 6)     '出力1行分の配列
  
  'シートの最終行列の取得:とりあえず全部調べようという事
  maxRow = ws売上.Cells.SpecialCells(xlCellTypeLastCell).Row
  maxCol = ws売上.Cells.SpecialCells(xlCellTypeLastCell).Column
  
  oRow = 2
  For i = 1 To maxRow
    Select Case True
      Case ws売上.Cells(i, 1) = ""
        '空行
      Case ws売上.Cells(i, 3) = ""
        '取引先の行
        tCd = ws売上.Cells(i, 1)        '取引先CD
        tName = ws売上.Cells(i, 2)       '取引先名
      Case ws売上.Cells(i, 1) Like "商品*"
        '見出し行
        ix = i                 '見出し行の退避
      Case Else
        'データ行
        For j = 3 To maxCol
          If IsDate(ws売上.Cells(ix, j)) Then '四半期計は除外
            ary(1) = tCd          '取引先CD
            ary(2) = tName         '取引先名
            ary(3) = ws売上.Cells(i, 1)   '商品CD
            ary(4) = ws売上.Cells(i, 2)   '商品名
            ary(5) = ws売上.Cells(ix, j)  '年月
            ary(6) = ws売上.Cells(i, j)   '金額
            wsDB.Cells(oRow, 1).Resize(, 6) = ary
            oRow = oRow + 1 '出力行を次へ
          End If
        Next
    End Select
  Next
End Sub


決してこれが正解という事ではなく、私なりの処理にしてみたという事です。
空行の有無や四半期計の有無、これらの自由度を少し高めてみました。
VBAにコメントを細かく入れたので、解説の代わりとします。
記事にVBAを掲載しています。


補足

補足はありません。


サイト内関連ページ

第16回.繰り返し処理(For Next)
VBAのForNextは、同じ処理を繰り返し行うためのVBA構文です。繰り返し処理はループ処理とも呼ばれます。マクロでのループ処理の記述は何通りかありますが、まず最初に覚えるべきものが、今回説明するForNextです。
第17回.繰り返し処理(Do Loop)
繰り返し処理として、前回はFor~Nextをやりました、今回はDo~Loopです。For~Nextに比べると使用頻度は落ちますが、必ず覚える必要があるものです。For~Nextは、繰り返す回数をあらかじめ指定するものでしたが、Do~Loopは、繰り返す回数ではなく、繰り返す条件を指定するものです。
第18回.最終行の取得(End,Rows.Count)
Excelワークシートにおける表の最終行の取得は、マクロVBAの必須技術になります、エクセルVBAにおける最終行取得の必要性 エクセルは表計算ソフトです、つまり縦横の表を扱います、データは横に項目があり、縦に項目に対するデータが入っている事が一般的です。しかし、そのデータ行数は決まった行数ではない事が普通です。
最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange)
エクセルの表をマクロVBAで扱う時は、データ部分の先頭から最終行までの、開始列から最終列まで処理する事が多いでしょう。開始行や開始列は、ほとんどの場合、見出し行や見出し列の次からになります。単純な話として、1行目に見出しがあれば、2行目から 1列目に見出しがあれば、2列目から では、ここで、最終行や最終列は、




同じテーマ「VBA100本ノック」の記事

71本目:パワーポイントへグラフを貼り付け
72本目:ITをDXに変換(文字列操作)
73本目:新規ブックにボタン作成しマクロ登録
74本目:1シート複数表をDB形式に変換
75本目:ログインフォームの作成
76本目:ボタンの表示名の位置へ移動
77本目:シート挿入イベント
78本目:グラフのデータ範囲拡張
79本目:Wordにセル範囲を図として挿入
80本目:読み取り専用で保存できないブックにする
81本目:全フィルターの絞り込解除


新着記事NEW ・・・新着記事一覧を見る

エクセル馬名ダービー|エクセル雑感(2021-07-21)
在庫を減らせ!毎日棚卸ししろ!|エクセル雑感(2021-07-05)
日付型と通貨型のValueとValue2について|エクセル雑感(2021-06-26)
DXってなんだ? ITと何が違うの?|エクセル雑感(2021-06-24)
エクセルVBA 段級位 目安|エクセル雑感(2021-06-21)
ローカル版エクセルが「Office Scripts」に変わる日|エクセル雑感(2021-06-10)
新関数SORTBYをVBAで利用するラップ関数を作成|VBA技術解説(2021-06-12)
VBA今日のひとこと/VBA今日の教訓 on Twitter|エクセル雑感(2021-06-10)
VBAの演算子まとめ(演算子の優先順位)|VBA技術解説(2021-06-09)
画像が行列削除についてこない場合の対処|VBA技術解説(2021-06-04)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.Excelショートカットキー一覧|Excelリファレンス
3.変数宣言のDimとデータ型|VBA入門
4.RangeとCellsの使い方|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.マクロって何?VBAって何?|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
9.セルに文字を入れるとは(Range,Value)|VBA入門
10.とにかく書いてみよう(Sub,End Sub)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ