Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7全般 : Excel 2010)
クロス集計表から指定フォーマット(クロス集計表)へ転記
投稿日時: 19/06/04 11:39:29
投稿者: namidaneko

Excelのクロス集計表から指定フォーマットへ転記をしたいのですが、
なにか効率のいい方法はないでしょうか。
 
●状況
・Excelの表はフォーマットは固定。
(行や列の追加は可能ですが使用する表は変えれない。)
 
●元データのクロス集計表
 
(列)2019年1月 2019年2月 2019年3月…               
(行)客先番号 客先名 品番 品目名 (値) 金額
 
●集計表のフォーマット
 
(列)1月 2月 3月…               
(行)客先番号 客先名 品番 品目名 (値) 金額
 
●内容
・Excelのクロス集計表から、指定フォーマットのExcelのクロス集計表へ、
客先番号(行)と品番(行)と売上月(列)が一致する欄へ金額を転記したい。
・集計表フォーマットにある、
客先×品番の売上が毎月必ず発生するわけではなく、
売上が発生しない場合もある。
・元データから、集計表のフォーマットに転記したとき、
新しい客先や新しい品番が発生し、
客先番号・品番がExcel集計表の行項目にない場合も考えられる。
その場合、自動でExce集計表lの一番下の列に追記されるようにしたい。
 
以上となります。
 
元データのクロス集計表は、
Accessで作成してExcelに変換してます。
クロス集計表は、
もちろんAccessのままで使用することも可能です。
クロス集計をやめ、リスト形式で出すことも可能です。
ただ、抽出条件があるので、必ずAccessは経由し、
必ずExcelのフォーマットに集約されるようにします。
 
すみません、初心者のため、
なるべく簡単な方法を教えていただけると幸いです。
よろしくお願いいたします。

回答
投稿日時: 19/06/04 13:57:52
投稿者: WinArrow
投稿者のウェブサイトに移動

ここの掲示板の規約では、コードの作成依頼は禁止されています。
https://www.moug.net/faq/kiyaku.html
 
まず、手作業で実施する場合の操作を、箇条書きにしてみましょう。
各々の行ごとに
VBAでは、どのようなコードになるのか、考えてみましょう。
 
マクロの記録などでコードを作成してみることも可能です。
 
 
説明は、概略わかるような気がしますが、
部分的に理解しにくいところがあります、

回答
投稿日時: 19/06/04 13:59:15
投稿者: Suzu

行方向が決まっているという事ですよね。VBAは必要ないです。
初心者とご自分で言うのであれば、
一般機能で済む話なのであれば一般機能で済ませた方が良いです。
何が不具合が起きた時に何もできなくなります。
 
 
Excel側で、転記する位置を検索し、値を入れ込んでも良いでしょうけど
Accessにてクロス集計クエリを使用し表を作成しているのであれば
行方向に出てくる項目を固定させてしまえば良いのでは?
列方向も列見出しで固定できますね。
 
行見出しの元となるテーブルを用意します。その上で
 
案.1 : 行見出しの元となるテーブルと、クロス集計クエリ結果を外部結合にて連結し
         行見出しを固定してしまう。
 
案.2 : クロス集計クエリの前に全レコードを表示させる選択クエリを作成し
         そのクエリを元にクロス集計クエリを作成
         (本来表示されないデータを作成していまいます)
 
案1の方が簡単でしょうね。
その上で 出来たクエリをExcelに吐き出し、必要部分をコピペしましょう。
 
Excel最終フォーマットの行方向に集計行があるならコピペを複数回行う事になりますが

回答
投稿日時: 19/06/04 14:03:49
投稿者: WinArrow
投稿者のウェブサイトに移動

追記
 
指定のフォーマットに転記ですから、指定のフォーマットには、キー項目以外は空白なんですよね?
見たところ、元データ側は「年月」集計表側は「月」のところだけのような感じですが、
集計表のフォーマットと何が違うのでしょう?
 
見出しだけだとしたら、
Accessから出力するとき
指定のフォーマットになるようにできないのですか?
 
 
 
 

投稿日時: 19/06/04 15:30:11
投稿者: namidaneko

わかりづらく、すみません。
集計用のフォーマットですが、
左右にデータが続いてます。
 
営業の案件管理用のフォーマットなので、
データ左側には、カテゴリーや営業担当者名、エリア、ランキング
データ右側には、四半期の売り上げ金額合計
か入ってます。
 
これが、この書式でければならない理由です。
 
●集計表のフォーマット
  
(列)1月 2月 3月…               
(行)カテゴリー 営業担当者名 エリア 客先番号 ランキング 客先名 品番 品目名 (値) 金額 四半期合計
 
すみません、関数等考えたのですが、できませんでした。
indexとmatcgでできないか、やってみました。
 
丸投げをするつもりはなく、ヒントをいただければと思います。

回答
投稿日時: 19/06/04 17:03:35
投稿者: WinArrow
投稿者のウェブサイトに移動

>丸投げをするつもりはなく、ヒントをいただければと思います。
 
VBAでも、INDEX,MATCHを使用することになると思いますが・・・・
 
「年月」のセルのデータはどのよういなっているか?・・・・に依存しますが
シリアル値だったら、「月」のマッチングは、工夫が必要でしょう。

投稿日時: 19/06/04 19:58:00
投稿者: namidaneko

日付についてですが、
元データのExcel、集計表ともに日付です。
シリアル値ではありません。
 
元データ→1999/01/01
集計表→01
 
もし、形式を揃えなければならないのであれば、
集計表も1999/01のように合わせることも可能です。

回答
投稿日時: 19/06/05 08:26:00
投稿者: WinArrow
投稿者のウェブサイトに移動

namidaneko さんの引用:
日付についてですが、
元データのExcel、集計表ともに日付です。
シリアル値ではありません。
 
元データ→1999/01/01
集計表→01
 
もし、形式を揃えなければならないのであれば、
集計表も1999/01のように合わせることも可能です。

 
少し説明が不足していたようです。
 
「日付」と「シリアル値」は同義語です。
日付に見えているのは、シリアル値を「yyyy年m月」のように表示形式で見sているだけです。
日付の「値」はシリアル値なのです。
 
そして、他のデータとの照合は、シリアル値で行われます。
 
ですから、照合するMATCH関数では、お互いのデータ型を合わせる必要があります。
 
元データの日付を集計表の数値に合わせるのは、
行=MATCH(MONTH(元データの日付),集計表のセル範囲,0)
とすることで、お互いのデータ型を合わせることができます。・・・・工夫
その逆は無理です。・・・・集計表側のデータを日付(=シリアル値)には変換できませんから・・・
 
これは、VBAを使っても同じです。
 
 

回答
投稿日時: 19/06/05 08:35:28
投稿者: WinArrow
投稿者のウェブサイトに移動

>(行)客先番号 客先名 品番 品目名 (値) 金額
と書かれていますが、
 行側のレイアウトは、どのようになっているのですか?
お互いに照合できるキーがどこかということを含めて、照合キーが存在するか・・・ということです。
 
>品目名 (値)
とは、どのような意味ですか?
 
例えば・・・
「客先番号」+「品番」の両方を照合キーと考えるならば、
作業列を(左側)追加して、文字列結合してキーを作成します。
 
 
 

回答
投稿日時: 19/06/05 09:00:25
投稿者: WinArrow
投稿者のウェブサイトに移動

今更・・・なんですが、
 
元データ側と集計表側の形式が同一に見える・・・(違いが分からない)
ので、照合など面倒くさいこと考えずに、そっくりコピペすることはできないのかな?

投稿日時: 19/06/05 09:02:02
投稿者: namidaneko

(行)客先番号 客先名 品番 品目名 (値) 金額
のなかで、照合キーのなるのは、客先番号と品番です。
 
品目名 (値) についてですが、
うまく改行できていなかったようですみません。
(列)1月 2月 3月…               
(行)客先番号 客先名 品番 品目名
   (値) 金額
となり、上記の列と行が交わる箇所に(値)として金額を入れたいという意味です。

投稿日時: 19/06/05 10:49:30
投稿者: namidaneko

WinArrow さんの引用:
今更・・・なんですが、
 
元データ側と集計表側の形式が同一に見える・・・(違いが分からない)
ので、照合など面倒くさいこと考えずに、そっくりコピペすることはできないのかな?

 
 
こちらについて回答します。
集計表のデータの並びが元データと一致しないため、コピペが難しいんです。
元データは客先番号順です。
集計表は、案件のランキング順です。
 
なので照合をしたく、質問させていただいてます。

回答
投稿日時: 19/06/05 11:02:49
投稿者: WinArrow
投稿者のウェブサイトに移動

namidaneko さんの引用:
WinArrow さんの引用:
今更・・・なんですが、
 
元データ側と集計表側の形式が同一に見える・・・(違いが分からない)
ので、照合など面倒くさいこと考えずに、そっくりコピペすることはできないのかな?

 
 
こちらについて回答します。
集計表のデータの並びが元データと一致しないため、コピペが難しいんです。
元データは客先番号順です。
集計表は、案件のランキング順です。
 
なので照合をしたく、質問させていただいてます。

でも、集計表に存在しないデータは、追加するという説明がありました。
それは案件順になりませんよね?
データの中に、「案件」に関連する項目が見当たりませんが・・・・
 
左側に列を挿入する提案は、検討されましたか?

回答
投稿日時: 19/06/05 19:59:46
投稿者: WinArrow
投稿者のウェブサイトに移動

せっかく、コードを作ったので、アップしておきます。
 
前提条件
レイアウト
【元データ】・・・リスト形式です。
[A] [B] [C] [D] [E] [F]
客先番号 客先名     品番    品目名    売上月     金額
A001    A001 9000    9000 2019/1/1 2000
※F列の表示形式:yyyy年m月
 
 
【集計表】
[A] [B] [C]  [D]   [E] [F] [G]
照合キー 客先番号 客先名 品番    品目名 2019/1/1 2019/2/1 2019/3/1
A0019000 A001    A001 9000    9000
 
※A列には、数式で照合キーを作成します。
 例=OFFSET(A2,,1)&OFFSET(A2,,3)
※E1〜右日付の表示形式は、"yyyy年m月"でも"m月"でも OK
 
【標準モジュール】
Option Explicit
 
Sub sample()
Dim sht1 As Worksheet, dRX As Long
Dim sht2 As Worksheet
Dim Rowx As Long, Colx As Long
 
    Set sht1 = Worksheets("元データ")
    Set sht2 = Worksheets("集計表")
    Names.Add "集計表", sht2.UsedRange '実際のエリアを適用してください。
    With sht1
        For dRX = 1 To .Range("A" & .Rows.Count).End(xlUp).Row
            If WorksheetFunction.CountIf(Range("集計表").Columns("A"), .Cells(dRX, "A").Value & .Cells(dRX, "C").Value) Then
                Rowx = WorksheetFunction.Match(.Cells(dRX, "A").Value & .Cells(dRX, "C").Value, Range("集計表").Columns("A"), 0)
                Colx = WorksheetFunction.Match(.Cells(dRX, "E").Value * 1, Range("集計表").Rows(1), 0)
                Range("集計表").Cells(Rowx, Colx).Value = .Cells(dRX, "F").Value
            ElseIf dRX > 1 Then
                Rowx = Range("集計表").Rows.Count + 1
                With Names("集計表")
                    .RefersTo = Left(.RefersTo, Len(.RefersTo) - 1) & Mid$(.RefersTo, InStrRev(.RefersTo, "$") + 1) + 1
                End With
                For Colx = 1 To 4
                    Range("集計表").Cells(Rowx, Colx + 1).Value = .Cells(dRX, Colx).Value
                Next
                Range("集計表").Cells(Rowx, 1).Formula = "=OFFSET(" & Range("集計表").Cells(Rowx, 1).Address(0, 0) & ",,1)" & _
                                                            "&OFFSET(" & Range("集計表").Cells(Rowx, 1).Address(0, 0) & ",,3)"
                 
                Colx = WorksheetFunction.Match(.Cells(dRX, "E").Value * 1, Range("集計表").Rows(1), 0)
                Range("集計表").Cells(Rowx, Colx).Value = .Cells(dRX, "F").Value
            End If
        Next
    End With
    Names("集計表").Delete
 
End Sub
 
 
 

トピックに返信