HOME > Q&A > Excel （VBA）

Excel （VBA）
Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Microsoft 365)
ピボット列の解除？
投稿日時: 23/03/03 11:10:00
投稿者: mild2

お尋ねします。
 
表１
列1    列2    列3    列4    列5    列6
a1    b1    c1    d1    e1    f1
a2    b2    c2    d2    e2    f2
a3    b3    c3    d3    e3    f3
a4    b4    c4    d4    e4    f4
a5    b5    c5    d5    e5    f5
 
表２
列1    列2    列3
a1    b1    c1
a2    b2    c2
a3    b3    c3
a4    b4    c4
a5    b5    c5
d1    e1    f1
d2    e2    f2
d3    e3    f3
d4    e4    f4
d5    e5    f5
 
表１を表２のように加工したいと思っています。
列４から列６までを列１から列３までに統合したいのです。
パワークエリでできると思います。パワークエリでのやり方を教えて下さい。

回答
投稿日時: 23/03/03 11:21:29
投稿者: simple

この場合は、カットアンドペイストのほうがよくありませんか？
投稿日時: 23/03/03 11:32:41
投稿者: mild2

はい、カットアンドペーストで出来ます。
あくまでサンプルは分かりやすく、かつシンプルに例示したもので、実際は行数も遥かに多いし、列ももっと多いのです。ピボット列の解除でできるとは思っているので
ピボット列の解除での方法を教えてもらいたいと思っています。
でもそれより、他の方法があったらそれも検討したいと思っています。
 
回答
投稿日時: 23/03/03 13:05:37
投稿者: sk

引用:
表１を表２のように加工したいと思っています。
列４から列６までを列１から列３までに統合したいのです。

引用:
実際は行数も遥かに多いし、列ももっと多いのです

1 つのテーブルを 3 列 ごとに区切って複数のテーブルに分割した上、
それら全てをマージなさりたいのだとして、元のテーブルの列数が
3 の倍数であることは保証されているのでしょうか。
投稿日時: 23/03/03 13:18:10
投稿者: mild2

はい、列は３の倍数です。
そして列１列目と４列目は商品分類、２列目と５列目は商品名、３列目と６列目は売上
というように列の属性は統一しています。
回答
投稿日時: 23/03/03 14:16:08
投稿者: sk

引用:
列１列目と４列目は商品分類、２列目と５列目は商品名、３列目と６列目は売上
というように列の属性は統一しています

テーブルの各列の命名規則に関しても同様でしょうか。
例えば、次のような列見出しがつけられているか否か。
 
------------------------------------------------------------------------ 
商品分類1 商品名1 売上1 商品分類2 商品名2 売上2 商品分類3 商品名3 売上3
------------------------------------------------------------------------
 
列見出しの名前に一定の規則性がないと多少面倒な方法を取ることになります。
投稿日時: 23/03/03 14:43:32
投稿者: mild2

テーブルの各列の命名規則に関しても同様でしょうか。
⇛はい、YESです。
回答
投稿日時: 23/03/03 15:03:50
投稿者: sk

テーブルの各列の列見出しが「属性を表す文字列」と
「属性ごとの添え字（整数）」の組み合わせによって
構成されていると仮定するのであれば、例えば
次のようなクエリを作成なさればよろしいのではないかと。
 
（詳細エディタ）
----------------------------------------------------------------
let
    // 元テーブルの参照
    Source = Excel.CurrentWorkbook(){[Name="テーブル名"]}[Content],
    // 行インデックス列の挿入
    AddedRowIndex = Table.AddIndexColumn(Source, "行インデックス", 1, 1, Int64.Type),
    // [行インデックス]列以外の全ての列のピボット解除
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedRowIndex, {"行インデックス"}, "属性", "値"),
    // [属性]の値（元の列名）の接頭辞を[項目名]、添え字を[列インデックス]として2つの列に分割
    SplitColumnByCharacterTransition = Table.SplitColumn(UnpivotedOtherColumns, "属性", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"項目名", "列インデックス"}),
    // [列インデックス]列のデータ型を整数型に変換
    ChangedTypeOfColumnIndex = Table.TransformColumnTypes(SplitColumnByCharacterTransition,{{"列インデックス", Int64.Type}}),
    // [項目名]列に含まれる名前を使用して[値]列をピボット
    PivotedColumn = Table.Pivot(ChangedTypeOfColumnIndex, List.Distinct(ChangedTypeOfColumnIndex[項目名]), "項目名", "値"),
    // 全ての行を[列インデックス]列の昇順、[行インデックス]列の昇順に並べ替える
    SortedRows = Table.Sort(PivotedColumn,{{"列インデックス", Order.Ascending},{"行インデックス", Order.Ascending}}),
    // [行インデックス]列と[列インデックス]列の削除
    RemovedIndexColumns = Table.RemoveColumns(SortedRows,{"行インデックス", "列インデックス"})
in
    RemovedIndexColumns
----------------------------------------------------------------
 
投稿日時: 23/03/03 15:43:02
投稿者: mild2

あっ、こちらはVBAでしたね。
私はパワークエリの勉強中で、こちらのカテゴリーでパワークエリに関する質問を探しました。
一般機能の中では見浸かられなくて、こちらのVBAの方で散見されたので、こちらで質問しました。
まだ初心者ですので、コード記述までは出来ないのですよ。すみません。😢
回答
投稿日時: 23/03/03 16:56:19
投稿者: simple

折角頂いた回答ですから、テーブル名を変更したうえで、詳細エディターに貼り付け、
段階ごとの動作を学習されたらいかがですか？
 
# マクロでは数行で実行できることなので、私ならマクロでやります。

回答
投稿日時: 23/03/03 16:57:46
投稿者: sk

引用:
あっ、こちらはVBAでしたね。

例示したのは VBA のコードではなく、
Power Query エディターの[詳細エディター]上に記述される
クエリのソース（ Power Query M 数式言語）です。
 
引用:
まだ初心者ですので、コード記述までは出来ないのですよ。すみません。

既に Power Query エディター上において元のテーブルが
参照されている状態からの手順としては、概ね次のような流れとなります。
 
1. [列の追加]タブ　-> [全般]グループ -> [インデックス列]
   -> [1 から]をクリックしてインデックス列を追加する。
 
2. 手順 1 で追加したインデックス列全体を選択し、
   [変換]タブ -> [任意の列]グループ -> [列のピボット解除]
   -> [その他の列のピボット解除]をクリックする。
 
3. [属性]列全体を選択し、[変換]タブ -> [テキストの列]グループ
   -> [列の分割] -> [数字以外から数字による分割]をクリックする。
 
4. 手順 3 によって分割された列のうち、数字列（[属性.2]）全体を選択して
   [変換]タブ -> [任意の列]グループ -> [データ型] -> [整数]をクリックする。
 
5. 手順 3 によって分割された列のうち、テキスト列（[属性.1]）全体を選択して
   [変換]タブ -> [任意の列]グループ -> [列のピボット]をクリックする。
 
6. [値列]コンボボックスのリストから「値」を選択し、更に
   [詳細設定オプション]を展開して[値の集計関数]コンボボックスの
   リストから「集計しない」を選択して[OK]ボタンをクリックする。
 
7. 手順 4 で型変換された整数列（[属性.2]）全体を選択し、
   [ホーム]タブ -> [並べ替え]グループ -> [昇順で並べ替え]をクリックする。
 
8. 手順 1 で追加したインデックス列全体を選択し、
   [ホーム]タブ -> [並べ替え]グループ -> [昇順で並べ替え]をクリックする。
 
9. 手順 7 と 8 において並べ替えのキーとされた 2 つの列を範囲選択し、
   [ホーム]タブ -> [列の管理]グループ -> [列の削除] -> [列の削除]をクリックする。

回答
投稿日時: 23/03/03 20:26:01
投稿者: んなっと

こんな方法も。PowerQueryの詳細エディターをクリックして、
 
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    Trs = Table.TransformColumnTypes(ソース,
            List.Transform(Table.ColumnNames(ソース), each {_,type text})),
    Tbl1= Table.SelectColumns(Trs,List.Range(Table.ColumnNames(Trs),0,3)),
    Tbl2= Table.RemoveColumns(Trs,List.Range(Table.ColumnNames(Trs),0,3)),
    Tbl3= Table.RenameColumns(Tbl2,
            List.Zip({Table.ColumnNames(Tbl2), Table.ColumnNames(Tbl1)})),
    Cmb =Table.Combine({Tbl1,Tbl3})
in
    Cmb
 
 
※普通のワークシート上の数式だと簡単。
 
=LET(a,テーブル1[#すべて],VSTACK(TAKE(a,,3),DROP(DROP(a,,3),1)))
投稿日時: 23/03/04 16:33:33
投稿者: mild2

ご回答内容を確認と検証していますが初心者ですので
時間がかかりそうです。そこでひとまず締めさせて頂きます。
ご回答頂いた皆さん、ありがとうございました。