『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
ということで、これより先は私は無理ですが、賢者の方々が提案してくださると思います。
(通りすがり) 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
こちらは質問のための例です。
ソートのルールは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
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
ご説明不足しており恐れ入ります。
もし何か良い方策ありましたらよろしくお願いいたします。
(あお) 2024/04/24(水) 17:52:23
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
ありがとうございます。
これはテーブルのマージから始まる工程という理解でよろしいでしょうか。
(あお) 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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.