縦持ちデータのクエリを横持ちデータに変換したい

Question

実現したいこと

画像のようにデータの縦横を変えたいです。

前提

集積した売上情報を指定した期間分Excelで出力するフォームを作成したいです。最終的に以下の画像のようにExcel出力をしたいと考えていて、色毎にテーブル・クエリを分割して作成し、出力用テーブルに追加してExcel出力する構想です。

発生している問題・エラーメッセージ

現在は第一段階として青の部分、商品マスターテーブルの縦横を入れ替えた表を作成したいのですが、自身の知識では実現方法が浮かばなく、手詰まりを起こしています。

試したこと

ネットで縦持ち・横持ちの変換方法について調べてみましたが、あまり理解が及ばず・・・ヒントや参考になるサイトを教えて頂けないでしょうか。

補足情報（FW/ツールのバージョンなど）

Microsoft　Access2019

Answer 1

そのような処理はAccessのようなデータベースでは苦手な処理です。できないことはないですが、面倒だし処理も重くなります。
最終的にエクセルに出力するのなら、
そのままの形でエクセルに出力してエクセルの方で縦横変換するのが簡単かつ高速です。

「エクセル 行列の入れ替え」とか「エクセル 縦横変換」で検索すれば解説ページが多数見つかります。

Comments

[退会済みユーザー]

Accessだと苦手な処理っぽいなぁというのは私自身も感じております…。
ただ、Excel出力・データを加工するのは私ではなく客先で、操作マニュアルにExcelで行列の入れ替えやデータの組み直しを入れるのは不親切だと感じています。
客先の人間が行う操作は、Access上で①出力したい期間を選択し②Excelに出力ボタンをクリック
この2つに留めたいなあと思うのです。

前提のようにAccessから出力できるようご助言いただけないでしょうか？

[hatena19]

客先にはAccessファイルとして渡すのですか。

> 客先の人間が行う操作は、Access上で①出力したい期間を選択し②Excelに出力ボタンをクリック

ということなら、その出力ボタンに割り当てたVBAで下記のような処理をするのがいいでしょう。

エクセルファイルにテーブルを出力する。
そのエクセルのデータを縦横変換する。

Accessからエクセルを操作する方法も検索すれば見つかります。例えば、
https://officevba.info/accessvba-exceloperation/

下記も参考になるでしょう。
レコードセットの内容をそのままExcelのシートに出力する方法
https://tsware.jp/tips/tips_686.htm

[退会済みユーザー]

はい、Accessファイルとして提供し、売上情報の入力・データの出力・加工も客先側の作業となります。

ファイルの中身を作ってから出力ではなく出力してから体裁を整える、ということが可能なんですね。
そちらの実現方法をリンク先の情報を確認しながらやってみたいと思います！

[退会済みユーザー]

2個目のリンク、リンク先のリンク「#432 Excelセルへ個別出力する基本パターン」を参考にして記述してみたのですが、出力出来るのがレコードのみでフィールド名を出力できませんでした。
https://teratail.com/questions/lkiy9t40xkoo9b
こちらの自己解答に、フィールドとレコードを分けて出力したコード記述しました。

見た目上は理想に近づいたのですが、テーブルやクエリをフィールド名ごと取り込む方法ご存じでしたらご教示いただけないでしょうか？

Answer 2

遅くても構わないなら、Djoin関数が使えるかもしれません。

後は、動的にクロス集計クエリーを作成して処理するとか。

追記
質問を読み返してみると、集計は必要なく行列を入れ替えるだけですね。
であれば、行数分を列を持ったワークテーブルを生成し、元データを列ごとに読み込んで追加するという処理の方が簡潔ですね。

Comments

[退会済みユーザー]

回答ありがとうございます。
クロス集計クエリで実現できたらクエリのインポートだけで済むので便利だなと思って試してみたのですが理想形にならず…どのような手順で作成するのかご教示いただけないでしょうか？
商品マスターテーブルのフィールドは品番・会社名・品名・価格です

[sazi]

変換後の行毎のクロス集計をunionするイメージです。

Answer 3

2023/4/1
- 購入者番号0001,割引券4,割引額400
- - 株式会社A,りんご,価格100,購入数1
- - 株式会社C,すいか,価格300,購入数1
————————————-小計:400
————————————-自己負担額:0
2023/4/2
- 購入者番号0002,割引券10, 割引額1000
- - 株式会社B,みかん,価格200,購入数4
- - 株式会社C,すいか,価格300,購入数1
————————————-小計:1100
————————————-自己負担額:100
2023/4/3
- 購入者番号0003,割引券5, 割引額500
- - 株式会社A,りんご,価格100,購入数1
- - 株式会社B,みかん,価格200,購入数3
————————————-小計:700
————————————-自己負担額:200

合計金額:2200;自己負担額:300;

---

　Accessのレポートは、使わないのでしょうか？

Comments

[退会済みユーザー]

前提の画像の様式で出力したExcelデータを客先が更に加工するので、レポートではなくこの様式でExcel出力したいです。

[logres_Fan]

Excel側で処理する方向ではだめでしょうか。
Excelの行と列を入れ替える関数（数式）
https://office-hack.com/excel/transpose/

[退会済みユーザー]

データを出力・加工するのが客先のため、Excel上で表の作成を客先にさせるのは不親切かなと感じております。客先側の操作は期間の選択・出力ボタンのクリックのみで前提の様式に出力できるようにAccess側でデータを加工できればと考えています。

[logres_Fan]

> 客先の人間が行う操作は、Access上で①出力したい期間を選択し②Excelに出力ボタンをクリック
例えば、シート1のA列からC列に10,000行出力される場合、シート2の1行から4行に10,000列に数式を入れておけば、行列を入れ替える操作をしなくても自動的に表示されるはずです。
　それはさておき、ユーザと改善・リセット・撤退などもっと話し合うべきです。古代王朝絵巻物のように横にながーくのびーる画面は、見やすいですか？緑の表は、本当に実装可能でしょうか？結局、Accessの役割はどうなるのでしょう。Excelに一本化しないのでしょうか？市販品、例えば、Excelと連携するならフュージョンプレイスが議題になると思いますが、提案されましたか？
　最終的にExcel表は誰が何のために見るのでしょう？購入数合計値、自己負担額小計・合計って制度会計上必要ですか？管理会計の役に立ちますか?
　

[退会済みユーザー]

出力するExcelシートに予め数式を入れておく方法もあるのですね、参考になります。

Access取り扱いの勉強として、以前客先に提供したデータベースファイルを再現しているところなので実際に客先と話し合う機会はないのですが…それはともかくとして、これを作ってみろと言われて、扱い辛い表だなぁとは自分自身も感じておりました。logresさんのレポート形式の見やすいこと…

Answer 4

例えば、Excel.Range オブジェクトの CopyFromRecordset メソッドによって
レコードセットの内容を一旦ワークシートに出力してから、出力された範囲を
Excel.Range オブジェクトの PasteSpecial メソッドか
Excel.WorksheetFunction オブジェクトの Transpose メソッドを用いて
行列変換した結果を別のワークシート/セル範囲に出力する、
という流れでもよさそうですが。

ただ、PasteSpecial メソッドだとクリップボードを仲介させるので
場合によっては貼り付けに失敗して実行時エラーが発生する恐れがありますし、
Transpose メソッドによって行列変換した2次元配列を得ても
値のみの行列変換になってしまう（書式は行列変換されない）ので、
どの道面倒くさいことになる可能性が高いです。

Option Compare Database
Option Explicit

Sub TestMacro1()

    Dim daoDatabase As DAO.Database
    
    Set daoDatabase = CurrentDb
    
    Dim daoRecordset As DAO.Recordset
    Dim strSQL As String
    
    '[商品マスターテーブル]の全てのレコードを[品番]の昇順に並べ替えた結果を得るSQL文
    strSQL = "SELECT *" & _
             " FROM [商品マスターテーブル]" & _
             " ORDER BY [品番];"
    
    'SQL文の実行結果をレコードセットとして取得
    Set daoRecordset = daoDatabase.OpenRecordset(strSQL, dbOpenSnapshot)
    
    'レコードがない場合は終了する
    If daoRecordset.EOF Then
        MsgBox "出力対象となるレコードがありません。", vbExclamation, "レコードなし"
        Set daoRecordset = Nothing
        Set daoDatabase = Nothing
        Exit Sub
    End If
    
    Dim lngRecordCount As Long
    
    'レコード件数の取得
    daoRecordset.MoveLast
    lngRecordCount = daoRecordset.RecordCount
    daoRecordset.MoveFirst
    
    Dim xlsApp As Object
    
    'Excelアプリケーションの新規インスタンスを生成
    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Visible = True
    xlsApp.ScreenUpdating = False
    
    Dim xlsWorkbook As Object
    Dim xlsWorksheet As Object
    
    '新規ブックの作成
    Set xlsWorkbook = xlsApp.Workbooks.Add
    '作成したブックの1つめのワークシートを参照
    Set xlsWorksheet = xlsWorkbook.Worksheets(1)
    
    Dim daoField As DAO.Field
    Dim xlsFirstColumnCell As Object
    Dim lngRow As Long
    Dim lngColumn As Long
    Dim strFormatString As String
    
    With xlsWorksheet
        
        lngRow = 1
        lngColumn = 1
        
        '行見出し列の出力、および各セルの表示形式の設定
        .Cells(lngRow, lngColumn).Resize(daoRecordset.Fields.Count, 1).NumberFormat = "@"
        For Each daoField In daoRecordset.Fields
            Set xlsFirstColumnCell = .Cells(lngRow, lngColumn)
            xlsFirstColumnCell.Value = daoField.Name
            Select Case daoField.Type
                Case dbText, dbMemo
                    strFormatString = "@"
                Case dbDate
                    strFormatString = "yyyy/mm/dd hh:mm:ss"
                Case Else
                    strFormatString = "General"
            End Select
            xlsFirstColumnCell.Resize(1, lngRecordCount).Offset(0, 1).NumberFormat = strFormatString
            Set xlsFirstColumnCell = Nothing
            lngRow = lngRow + 1
        Next
        
        'レコードセットを行列変換しながら出力
        Do Until daoRecordset.EOF
            lngRow = 1
            lngColumn = lngColumn + 1
            For Each daoField In daoRecordset.Fields
                .Cells(lngRow, lngColumn).Value = daoField.Value
                lngRow = lngRow + 1
            Next
            daoRecordset.MoveNext
        Loop
        
        '列の自動調整
        .UsedRange.EntireColumn.AutoFit
        
    End With
    
    xlsApp.ScreenUpdating = True
    
    Set xlsWorksheet = Nothing
    Set xlsWorkbook = Nothing
    Set xlsApp = Nothing

    Set daoField = Nothing
    Set daoRecordset = Nothing
    Set daoDatabase = Nothing
    
End Sub

パフォーマンスを度外視するなら、上記のサンプルのようなコードが挙げられます。