エクセルの学校：エクセル質問ボード[[20240424110159]]

エクセル	の学校
8.一覧	9.HOME
1.Top	2.Last

[[20240424110159]]

[ 初めての方へ | 一覧(最新更新順) |

| | 全文検索 | 過去ログ | エクセルの学校HOME ]

『2つのテーブルの集計と整序』（あお）

お世話になります。以下のようなテーブル2つを1つにマージして、結果を個別の行ごとに整序したいです。

テーブル1 姓名コース1 コース2 コース3 コース4 コース5 コース6 コース7 コース8 コース9 コース10 田中 D_I-8-2 D_I-8-3 D_I-8-4 D_I-8-5 鈴木 F_I-2-1 F_I-2-2 F_I-2-3 山田 D_I-8-1 D_I-8-6 D_I-8-7 D_I-8-8 佐藤 P-1-1 P-1-11 F_I-4-1 F_I-4-2 F_I-4-3 F_I-4-4 F_I-4-5 F_I-4-6 F_I-4-7 F_I-4-8

テーブル2 姓名コース1 コース2 コース3 コース4 コース5 コース6 コース7 コース8 コース9 コース10 田中 D_I-8-1 D_I-8-6 D_I-8-7 D_I-8-8 鈴木 F_I-2-4 F_I-2-5 F_I-2-6 山田 D_I-8-2 D_I-8-3 D_I-8-4 D_I-8-5 佐藤 C_I-1-1 C_I-1-3 C_I-1-4 C_I-1-5

↓↓ 結果テーブル姓名コース1 コース2 コース3 コース4 コース5 コース6 コース7 コース8 コース9 コース10 コース11 コース12...... 田中 D_I-8-1 D_I-8-2 D_I-8-3 D_I-8-4 D_I-8-5 D_I-8-6 D_I-8-7 D_I-8-8 鈴木 F_I-2-1 F_I-2-2 F_I-2-3 F_I-2-4 F_I-2-5 F_I-2-6 山田 D_I-8-1 D_I-8-2 D_I-8-3 D_I-8-4 D_I-8-5 D_I-8-6 D_I-8-7 D_I-8-8 佐藤 C_I-1-1 C_I-1-3 C_I-1-4 C_I-1-5 P-1-1 P-1-11 F_I-4-1 F_I-4-2 F_I-4-3 F_I-4-4 F_I-4-5 F_I-4-6 F_I-4-7 F_I-4-8

実際は数100行のデータです。

定時的な作業のためパワークエリなどを使えればと考えておりますが、上手いやり方がわからず知恵をお貸しいただけますと有難いです。

クエリのマージや追加をして行列の入れ替えとソートを使うのかなとあたりをつけておりますが上手く行きませんでした。

情報過不足あればご指摘ください。どうぞよろしくお願いいたします。

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

    A       B       C       D ・・・
  1 姓名    コース1 コース2 コース3 ・・・
  2 田中
  3 鈴木
  4 山田
  5 佐藤
  6
  7 姓名    コース1 コース2 コース3 ・・・
  8 田中
  9 鈴木
 10 山田
 11 佐藤
 12
 13 姓名    コース1 コース2 コース3 ・・・
 14 田中
 15 鈴木
 16 山田
 17 佐藤

 だとすると

 B14 =TOROW(SORT(UNIQUE(TOCOL(HSTACK(B2:K2,B8:K8)))))&""
 B17までフィルコピー

 とか

（通りすがり） 2024/04/24(水) 11:57:03

 なお、上の2つのブロックがテーブル1とテーブル2で、3つ目のブロックが結果テーブルの想定です。

（通りすがり） 2024/04/24(水) 12:01:07

通りすがりさん

ありがとうございます。実際には2つのシートにわかれており、それぞれテーブル化されています。

結果はまた別のシートに吐きたいです。

それぞれ100行以上あるデータで、日々データ数が変わるので縦に繋いて関数、という方法は避けたいと思うのですが、私の理解はあっていますでしょうか。（あお） 2024/04/24(水) 13:42:22

ほんじゃ使えないですかね。ただ、結果テーブルがテーブルではなく通常の範囲で、かつ、姓名のメンツが一定で、テーブル1・2のフォーマットが同じでデータの中身だけが変わるのであればいけそうな気はしますが・・・。申し訳ないです。

ということで、これより先は私は無理ですが、賢者の方々が提案してくださると思います。

（通りすがり） 2024/04/24(水) 14:46:40

ありがとうございました。

片方のテーブルにしか名前のない方もいたり、 2つのテーブルの行数と列数は異なります。

どなたかお知恵をお貸しいただければ幸いです。（あお） 2024/04/24(水) 14:58:58

 確認ですが、これはどんなソートなんですか？
 佐藤 C_I-1-1 C_I-1-3 C_I-1-4 C_I-1-5 P-1-1 P-1-11 F_I-4-1 F_I-4-2 F_I-4-3 F_I-4-4 F_I-4-5 F_I-4-6 F_I-4-7 F_I-4-8
 FよりもPが先に来るんですか?
 ソートのルールを明確に書いて下さい。
 また、この例は実際のコードですか?　それとも質問の為に作成した例ですか?

（xyz） 2024/04/24(水) 15:46:34

    Sub main()
    '結果吐出先はSheet3とする
    'ソート基準はExcel標準の昇順
    Dim c As Range, r As Range, rg As Range, col As Range, ar As Variant
    With Sheets("Sheet3")
        .Cells.Delete
        For Each ar In Array("テーブル1", "テーブル2")
            Set rg = Sheets(Range(ar).Parent.Name).Range(Range(ar).Address)
            rg.Copy
            .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Next ar
        For Each c In .Range("A:A").SpecialCells(2)
            If WorksheetFunction.CountIf(.Range("A1:A" & c.Row), c.Value) > 1 Then
                Set r = .Range("A:A").Find(c.Value, , , xlWhole)
                c.EntireRow.SpecialCells(2).Offset(, 1).Copy .Cells(r.Row, Columns.Count).End(xlToLeft).Offset(, 1)
                c.EntireRow.ClearContents
            End If
        Next c
        Set r = .Range("A2").CurrentRegion
        r.Copy
        .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
        r.EntireRow.Delete
        Set r = .Range("A2").CurrentRegion.Offset(1)
        For Each col In r.Columns
            With .Sort
                .SortFields.Clear
                .SortFields.Add2 Key:=col _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                xlSortTextAsNumbers
                .SetRange col
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        Next col
        Set r = .Range("A2").CurrentRegion
        r.Copy
        .Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial Transpose:=True
        r.EntireColumn.Delete
        Set r = .Range("A2").CurrentRegion
        For Each c In r.Offset(-1).Resize(1)
        c.Value = "コース" & c.Column - 1
        Next c
        .Range("A1").Value = "姓名"
    End With
    End Sub

（mm） 2024/04/24(水) 15:52:07

xyzさん

こちらは質問のための例です。ソートのルールはABC順、半角英数字順です。 Pのところは誤記で申し訳ございません。 Fより後に来るのが理想です。

よろしくお願いいたします。（あお） 2024/04/24(水) 16:05:16

 P_10-3   P_1-1  P_11-2
 といったデータがあれば、それはどう並ぶんですか?

 いやこんなデータは無い、というなら実際のデータをいくつか示してください。

（xyz） 2024/04/24(水) 16:15:21

xyzさん

D_I-8-01やB_B-2-02のようにアルファベット1文字_アルファベット1文字-数字1文字-数字2文字のコードです

数字1文字のところは最大9、2文字のところは99です。

よろしくお願いいたします。（あお） 2024/04/24(水) 16:23:02

 返答ありがとうございました。
 最初からそういう説明があると良かったですね。
（xyz） 2024/04/24(水) 16:56:54

xyzさん

ご説明不足しており恐れ入ります。もし何か良い方策ありましたらよろしくお願いいたします。（あお） 2024/04/24(水) 17:52:23

コンサートに出かけたので暫く無理です。 mmさんから回答ありました。どうぞ頑張って下さい。（xyz） 2024/04/24(水) 18:05:18

 Power Queryでやってみた。

 let
    ソース = Table.NestedJoin(テーブル1, {"姓名"}, テーブル2, {"姓名"}, "テーブル2", JoinKind.FullOuter),
    TB2ColumnNames=List.Skip(Table.ColumnNames(テーブル2),1),
    Expand = Table.ExpandTableColumn(ソース, "テーブル2", TB2ColumnNames,List.Transform(TB2ColumnNames, each Text.Combine({"TB2_",_}))),
    カスタム列 =Table.AddColumn(Expand, "カスタム", each {[姓名]}&List.Sort(List.RemoveNulls(List.Skip(Record.ToList(_),1)))),
    削除された他の列 = Table.SelectColumns(カスタム列,{"カスタム"}),
    列数 =  List.Max(List.Transform(削除された他の列[カスタム], each List.Count(_))),
    ColumnNames={"姓名"}&List.Transform({1..列数-1},each Text.Combine({"コース",Text.From(_)})),
    抽出した値 = Table.TransformColumns(削除された他の列, {"カスタム", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    区切り記号による列の分割 = Table.SplitColumn(抽出した値, "カスタム", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ColumnNames)
 in
    区切り記号による列の分割

 <結果>
     __A_  __B____  __C____  __D____  __E____  __F____  __G____  __H____  __I____  __J____  __K_____  __L_____  __M_____  __N_____  __O_____
 1   姓名  コース1  コース2  コース3  コース4  コース5  コース6  コース7  コース8  コース9  コース10  コース11  コース12  コース13  コース14
 2   田中  D_I-8-1  D_I-8-2  D_I-8-3  D_I-8-4  D_I-8-5  D_I-8-6  D_I-8-7  D_I-8-8                                                           
 3   鈴木  F_I-2-1  F_I-2-2  F_I-2-3  F_I-2-4  F_I-2-5  F_I-2-6                                                                             
 4   山田  D_I-8-1  D_I-8-2  D_I-8-3  D_I-8-4  D_I-8-5  D_I-8-6  D_I-8-7  D_I-8-8                                                           
 5   佐藤  C_I-1-1  C_I-1-3  C_I-1-4  C_I-1-5  F_I-4-1  F_I-4-2  F_I-4-3  F_I-4-4  F_I-4-5  F_I-4-6   F_I-4-7   F_I-4-8   P-1-1     P-1-11  
（まる2021） 2024/04/24(水) 19:41:13

 数式だと
 =LET(a,テーブル1,b,テーブル2,c,UNIQUE(VSTACK(TAKE(a,,1),TAKE(b,,1))),
F,LAMBDA(i,x,IFERROR(TOROW(XLOOKUP(i,TAKE(x,,1),DROP(x,,1)),1),"")),
d,DROP(REDUCE("",c,LAMBDA(s,t,LET(y,SORT(HSTACK(F(t,a),F(t,b)),,,TRUE),
 IFERROR(VSTACK(s,FILTER(y,y<>"","")),"")))),1),
VSTACK(HSTACK("姓名","コース"&SEQUENCE(,COLUMNS(d))),HSTACK(c,d)))

（んなっと） 2024/04/24(水) 19:51:04

まる2021さん

ありがとうございます。これはテーブルのマージから始まる工程という理解でよろしいでしょうか。（あお） 2024/04/25(木) 11:36:17

クエリのマージ以降のステップの理解が追い付かず。

このあたりが特にどういうステップを踏んでいるのかがわかりませんでした。完全外部でマージしたテーブル2を展開する前に何かしているのでしょうか。

TB2ColumnNames=List.Skip(Table.ColumnNames(テーブル2),1), Expand = Table.ExpandTableColumn(ソース, "テーブル2", TB2ColumnNames,List.Transform(TB2ColumnNames, each Text.Combine({"TB2_",_}))),

（あお） 2024/04/25(木) 11:47:43

 >これはテーブルのマージから始まる工程という理解でよろしいでしょうか。
 Yes。テーブル1,2がクエリに存在する前提です。
 クエリに存在しない場合は、以下の2行を先頭に追加してください。

 let
    テーブル1 = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    テーブル2 = Excel.CurrentWorkbook(){[Name="テーブル2"]}[Content],
    ソース = Table.NestedJoin(......

 >完全外部でマージしたテーブル2を展開する前に何かしているのでしょうか。
 理由は2つあります。
 (1)展開時、同名の列があるとエラーとなるので列名の先頭にプレフィックスとして「TB2_」を付加してます。(現在は「コース1から10」で重複している)
 (2)列数が「姓名」+「コース1から10」の「11」固定か不明だったので、列数の変更に対応する為。

 WEBから似た事案を検索するのではなく、以下のMSのリファレンスを見て「各関数がどのような引数をとり、戻り値は何か」を確認するようにしてください。
https://learn.microsoft.com/ja-jp/powerquery-m/power-query-m-function-reference

 全てを、説明するのは辛いため、自分はこのあたりで失礼します。
（まる2021） 2024/04/25(木) 12:49:58

 あと、「mmさん」や「んなっとさん」からの回答に対しても、返事を書きましょう。
（まる2021） 2024/04/25(木) 13:26:23

[ 一覧(最新更新順) |

]

キーボードヒント：[Home]または[Fn+Home]キーで一番上へ戻ります