Results of 1 - 1 of about 2421 for ピボットテーブル (0.004 sec.)
- [[20220925130324]]
- #score: 4769
- @digest: cea86bc7903a077e0b7420fd1cc12411
- @id: 92387
- @mdate: 2022-10-17T14:00:30Z
- @size: 27498
- @type: text/plain
- #keywords: 属一 (78759), ー昇 (73530), 数除 (49440), 舗", (43480), table (41496), 除= (38787), column1 (36868), 昇格 (35225), プ別 (34044), ト解 (33856), 者", (29636), 列削 (23386), 部署 (19765), currentworkbook (18865), transformcolumntypes (18139), ス= (16312), 除算 (14826), クエ (14706), 店舗 (12901), 連番 (12753), ・[ (12493), content (12279), 務時 (12227), ヘッ (11957), 所属 (11806), 展開 (11606), ッダ (10781), エリ (10460), 署名 (9112), ル1 (8816), ソー (8702), 当者 (8371)
- 『Power Queryを使ってみました』（マナ）
Power Queryを勉強したいと考えている方向けです。 他スレの質問をお題として借用し、 Power Queryでなら、どうするかを考えます。 そんなの関数でもできるよとか マクロのほうが便利とかは気にしないで。 ＜ 使用 Excel:Office365、使用 OS:Windows10 ＞ ---- 現在、トップページにあるものから選ぶと [[20220913163504]] 『VBAで2つのブック間で、キーをもとにデータを取得』（まき） [[20220918094537]] 『表の中から特定の行だけ削除して表を作り直したい』（まさ） [[20220922123301]] 『マクロでフィルタ検索で切取をしたい』（教えて下さい） [[20220921091242]] 『複数のシートの値を転記したい』（黒ネコ） （マナ） 2022/09/25(日) 18:37 ---- 回答していいのかよく分からないんですが [[20220918094537]] は「テーブル1」を既に読み込んでいるものとして 除外ランク = Excel.CurrentWorkbook(){[Name="テーブル2"]}[Content][除外ランク], 行の抽出 = Table.SelectRows(テーブル1, each List.RemoveItems(除外ランク, {[ランク]})=除外ランク) でどうでしょう。 （d-q-t-p） 2022/09/26(月) 09:25 ---- [[20220918094537]] 関連 リストの使い方の勉強にさせていただきます。 初心者が、基本機能（手作業）だけを使うと倍以上になります。 「テーブル1」を既に読み込んで、「テーブル2」を除外リストで読み込んで、 let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], 変更された型 = Table.TransformColumnTypes(ソース,{{"品名", type text}, {"生産地", type text}, {"ランク", type text}}), マージされたクエリ数 = Table.NestedJoin(変更された型,{"ランク"},テーブル2,{"除外ランク"},"テーブル2",JoinKind.LeftAnti), 削除された列 = Table.RemoveColumns(マージされたクエリ数,{"テーブル2"}) in 削除された列 （まっち） 2022/09/26(月) 11:18 ---- d-q-t-pさん、まっちさん、ありがとうございます。 ＞回答していいのかよく分からないんですが ぜひともお願いいたします。 わたしだけでなく、皆さんとても勉強になるはずです。 思い出したとき、暇つぶしにでも結構です。 引き続きよろしくお願いします。 勉強始めたばかりの方でも、投稿された内容でわからいことがあれば質問してください。 横入りも遠慮は不要です。 質問だけでなく、ご自分でもチャレンジしてみて その結果を投稿していただけると、もっとよいです。 チャレンジして、上手くいかないので質問、というのもありです。 ただし投稿は、あくまで既存スレ（このスレも含む）に関してでお願いします。 それ以外は、新規スレを立ちあげてください。 まだまだ、この便利機能の存在すら知らない方も多そうなので 認知度をあげる意味でも、Power Query関連のスレが増えることを期待します。 （マナ） 2022/09/26(月) 12:38 ---- d-q-t-pさん いつもハイレベルな回答という感じです。 わたしには、そんな式をささっとかける日が来るのでしょうか。 提示されれば理解はできるのですが… まっちさん クエリのマージを使う発想はありませんでした。 操作だけで完結できるというのが好みです。 （マナ） 2022/09/26(月) 20:12 ---- 検索してみました。（Power Query セル値 フィルター） 指定した範囲の値を使用してフィルタリング https://analytic-vba.com/power-query/m-code/filter-range-values/ 今回は、除外リストなので、抽出するものが逆。 そこで、trueでなくfalseにしてみました。 こんな感じ。 = Table.SelectRows(変更された型, each List.Contains(テーブル2,[ランク]) = false) 注意:False（大文字）にしないこと （マナ） 2022/09/26(月) 20:18 ---- [[20220913163504]] 『VBAで2つのブック間で、キーをもとにデータを取得』（まき） 実はこれ、Power Queryの前に、関数だけでできないか と考えているうちに、トップページから消えてしまい、諦めました。 お題（詳細は元スレで確認してください）: ・明細シート（別ブック）のF列の名前を、所属部署一覧シートから探して ・部署別に見つかった名前を並べ替える ・1列目は部署名で、2列目以降に名前 ・各部署1列あたり4人まで（つまり4行） ・所属部署一覧シートにない名前（未登録者）もある クエリの概要: ・所属一覧データを取得 ・部署名に連番を付加 ・ピボット解除で縦に並べ替え ・ファイルから/ブックから で明細シートのデータを取得 ・クエリのマージ ・部署名でグループ化 ・部署ごとに連番列作成 ・連番から、剰余を計算: 0,1,2.3,0,1,2,3,0,1,2,3,… ・連番から、整数除算を計算: 0,0,0,0,1,1,1,1,2,2,2,2,… ・整数除算の列で、ピボット ・部署名の繰り返しを削除:剰余の列が0以外の行は、部署名をnullに置換 （マナ） 2022/09/27(火) 22:02 ---- クエリ;所属一覧 let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], ヘッダー降格 = Table.DemoteHeaders(ソース), 転置テーブル = Table.Transpose(ヘッダー降格), 連番 = Table.AddIndexColumn(転置テーブル, "連番", 0, 1, Int64.Type), 連番2桁化 = Table.TransformColumns(連番, {{"連番", each Number.ToText(_, "00"), type text}}), 連番付き部署名 = Table.CombineColumns(連番2桁化,{"連番", "Column1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"部署"), 転置テーブル2 = Table.Transpose(連番付き部署名), ヘッダー昇格 = Table.PromoteHeaders(転置テーブル2, [PromoteAllScalars=true]), ピボット解除 = Table.UnpivotOtherColumns(ヘッダー昇格, {}, "部署", "名前"), データ型変更 = Table.TransformColumnTypes(ピボット解除,{{"名前", type text}}) in データ型変更 表示する部署の順番が、一覧シートと同じになるように2桁の連番を付加しています。 白茶さん、d-q-t-pさんからNumber.ToTextで、Format指定できることを教わったので 早速、2桁の連番作成で利用してみました。 [[20220912092843]] 『PowerQueryで時間の計算』（Aru） （マナ） 2022/09/27(火) 22:06 ---- クエリ:明細の名前を所属別に分類 let ソース = Excel.Workbook(File.Contents("D:¥○○¥△△¥社内工数投入状況_全体 .xlsx"), null, true), 明細_Sheet = ソース{[Item="明細",Kind="Sheet"]}[Data], ヘッダー昇格 = Table.PromoteHeaders(明細_Sheet, [PromoteAllScalars=true]), 変更された型 = Table.TransformColumnTypes(ヘッダー昇格,{{"名前", type text}}), 名前以外の列削除 = Table.SelectColumns(変更された型,{"名前"}), 重複削除 = Table.Distinct(名前以外の列削除), 所属一覧とマージ = Table.NestedJoin(重複削除, {"名前"}, 所属一覧, {"名前"}, "所属一覧", JoinKind.LeftOuter), 展開 = Table.ExpandTableColumn(所属一覧とマージ, "所属一覧", {"部署"}, {"部署"}), 部署空欄は未登録者 = Table.ReplaceValue(展開,null,"99:未登録者",Replacer.ReplaceValue,{"部署"}), 部署グループ化 = Table.Group(部署空欄は未登録者, {"部署"}, {{"group", each _, type table [名前=nullable text, 部署=nullable text]}}), 部署ごとに連番 = Table.AddColumn(部署グループ化, "連番テーブル", each Table.AddIndexColumn([group],"連番")), 展開2 = Table.ExpandTableColumn(部署ごとに連番, "連番テーブル", {"名前", "連番"}, {"名前", "連番"}), 剰余 = Table.AddColumn(展開2, "剰余", each Number.Mod([連番], 4), type number), 整数除算 = Table.AddColumn(剰余, "整数除算", each Number.IntegerDivide([連番], 4), Int64.Type), ピボット用の列名 = Table.TransformColumns(整数除算, {{"整数除算", each "名前" & Text.From(_ + 1, "ja-JP"), type text}}), 作業列削除1 = Table.SelectColumns(ピボット用の列名,{"部署", "名前", "剰余", "整数除算"}), ピボット = Table.Pivot(Table.TransformColumnTypes(作業列削除1, {{"整数除算", type text}}, "ja-JP"), List.Distinct(Table.TransformColumnTypes(作業列削除1, {{"整数除算", type text}}, "ja-JP")[整数除算]), "整数除算", "名前"), 部署名は1行のみ = Table.ReplaceValue(ピボット, each [部署],each if [剰余] = 0 then [部署] else "",Replacer.ReplaceText,{"部署"}), 作業列削除2 = Table.RemoveColumns(部署名は1行のみ,{"剰余"}), 部署名の番号削除 = Table.TransformColumns(作業列削除2, {{"部署", each Text.AfterDelimiter(_, ":"), type text}}) in 部署名の番号削除 部署名の繰り返しを削除するのに、以下を参考にしました。 Power Query - Conditionally Replace Values in a Column with Values from another Column https://blog.magnetismsolutions.com/blog/colinmaitland/2022/05/27/power-query---conditionally-replace-values-in-a-column-with-values-from-another-column （マナ） 2022/09/27(火) 22:09 ---- 他の方が作成したコードは最高の参考書ですね。 こうやるとこうなるのかと読み解くだけでも 本当に勉強になります。感謝。 （まっち） 2022/09/28(水) 09:45 ---- 昨日の続き 2桁の連番作成は、こんな感じでもできます。 let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], ヘッダー降格2回 = Table.DemoteHeaders(Table.DemoteHeaders(ソース)), 転置テーブル = Table.Transpose(ヘッダー降格2回), 連番 = Table.ReplaceValue(転置テーブル,"Column","",Replacer.ReplaceText,{"Column1"}), 連番2桁化 = Table.TransformColumns(連番, {{"Column1", each Text.End("0" & _, 2), type text}}), 連番付き部署名 = Table.CombineColumns(連番2桁化,{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"部署"), 転置テーブル2 = Table.Transpose(連番付き部署名), ヘッダー昇格 = Table.PromoteHeaders(転置テーブル2, [PromoteAllScalars=true]), ピボット解除 = Table.UnpivotOtherColumns(ヘッダー昇格, {}, "部署", "名前"), データ型変更 = Table.TransformColumnTypes(ピボット解除,{{"名前", type text}}) in データ型変更 '----- Text.End("0" & _, 2) よりは Text.PadStart(_, 2, "0") のほうが、意味的にもズバリの関数で、わかりやすいかも。 ただ、Text.Endのほうは、メニュー操作から同じようなことができて好きです。 Text.End https://learn.microsoft.com/ja-jp/powerquery-m/text-end Text.PadStart https://learn.microsoft.com/ja-jp/powerquery-m/text-padstart （マナ） 2022/09/28(水) 21:03 ---- ピボット解除について メニューには3種類あります。 1）列のピボット解除 2）その他の列のピボット解除 3）選択した列のみをピボット解除 今回、すべての列でピボット解除したかったので、1）を使用しています。 なぜ、1）なのか。 試してみるとわかりますが、 1）を選んでも、実行されるのは、 Table.UnpivotOtherColumns（その他の列のピボット解除）のようです。 では、1）と2）の違いは? 記録されたコード（数式バー）をみると 1）は、「選択した列以外の列」の「それ以外の列」のピボット解除 2）は、「選択した列」の「それ以外の列」のピボット解除 「選択した列以外の列」の「それ以外の列」って 結局「選択した列」です。 では、1）と3）は、どう違うの? すべての列を選んだ状態で、 3）を実行すると、確かにすべての列がピボット解除されますが 数式バーをみると、「選択した列」= すべての列 の名前が列挙されています。 これだと、列名や列数が変更になるとエラーになるので面白くないです。 = Table.Unpivot(ヘッダー昇格, {"01:システム一部", "02:システム二部", …}, "属性", "値") ^^^^^^^^^^^^^^↑^^^^^^^^^^^^^^^^^^^^^^^^ Unpivotだと、ここに「選択した列」が列挙される 1）の場合は、 すべての列を選んだ状態では「選択した列以外の列」は、何もないので 「それ以外の列」= すべての列 がピボット解除されます。 数式バーをみると、こんな感じ = Table.UnpivotOtherColumns(ヘッダー昇格, {}, "属性", "値") ^↑^^ UnpivotOtherColumnsだと、ここに「選択した列以外の列」が列挙される（何もないけど） つまり、 ・すべての列をピボット解除するときは1）を使い、 ・それ以外は、2）または3）を使えばよい ということかなぁと。（たぶん） （マナ） 2022/09/28(水) 21:10 ---- 補足 ＞Text.Endのほうは、メニュー操作から同じようなことができ こんな操作です。 ・変換/テキストの列/書式/プレフィックスの追加 で、値:0 ・変換/テキストの列/抽出/最後の文字 で、カウント:2 連番 = Table.ReplaceValue(転置テーブル,"Column","",Replacer.ReplaceText,{"Column1"}), 追加されたプレフィックス = Table.TransformColumns(連番, {{"Column1", each "0" & _, type text}}), 抽出される最後の文字 = Table.TransformColumns(追加されたプレフィックス, {{"Column1", each Text.End(_, 2), type text}}), （マナ） 2022/09/28(水) 21:35 ---- クエリのマージについて 「グループ化」とか「ピボット解除」などと同様 とてもによく使う便利な機能です。 https://hamachan.info/excel2019-powerquery-kyotu/ 初めて使うときは、操作に少し戸惑うかもしれませんが 2回めからは、「結合の種類」にどれを選ぶかで考えるくらい。 それも使っているうち、なんとくわかってきますが、 デフォルトの「左外部」で試して、だめなら他のをといった感じでも大丈夫です。 簡単にやり直しができるのもPower Queryのよいところです。 今回も、何も考えずに、「左外部」（JoinKind.LeftOuter）でOKでした。 このステップです。 ＞ 所属一覧とマージ = Table.NestedJoin(重複削除, {"名前"}, 所属一覧, {"名前"}, "所属一覧", JoinKind.LeftOuter), 上で、まっちさんが、「左反」（JoinKind.LeftAnti）を使った事例を紹介されてます。 確かに、そのケースは「左反」だよね、と納得です。 今回は1列ですが、照合列は、2列指定することも可能です。 そういった事例があれば取り上げます。 たぶん、これ。 [[20220921091242]] 『複数のシートの値を転記したい』（黒ネコ） （マナ） 2022/09/29(木) 21:24 ---- 余談です。 クエリのマージには、「あいまい一致オプション」というのがあります。 例えば、下記スレとかで使えないかと思って、試してみるのですが、全然違う。 [[20220704122531]] 『似た並びの文字列の検索•抽出』（nanana） [[20220812224147]] 『PowerQuery(powerpivot? dax?)ででワイルドカード』（りり） というか使えない機能という印象。 なので、このオプションどんな場面で使えるのか、未だにわかっていません。 いつか使ってみたいと思っているのですが… （マナ） 2022/09/29(木) 21:33 ---- まっちさん、書き込みありがとうございます。 ＞他の方が作成したコードは最高の参考書ですね。 同感です。 ですが、わたしのは「操作の自動記録」をちょこっと編集しているだけです。 その点で、わたしと違う、（´・ω・`）さんが回答されるようになって、随分と勉強させていただいてます。 回答してもらった質問者さん（まっちさんもその一人です）以上に、わたしのほうが得たものが多い自信があります。 最近はさらに、d-q-t-p さんも加わってくださり回答の質が上がっているのを実感しています。 まさに、こことか。 [[20220926171802]] 『条件一致引き算（パワークエリ）』（.:*.ゆ ゅ） （マナ） 2022/09/29(木) 22:12 ---- [[20220922123301]] 『マクロでフィルタ検索で切取をしたい』（教えて下さい） お題:3条件のあいまい検索で、フィルター抽出 （未解決のまま、放置状態なので、よくわからないけど） ・対象列の見出し右端の▼をクリックし、「テキストフィルター」を選択 ・●詳細設定 を選択 これで、3条件以上を設定できます。 デフォルトの「●基本」では、2条件まで。 「指定の値を含む」とか「指定の値で始まる」といったあいまい検索も オートフィルター同様に可能 条件をセル値で指定する場合は、 指定した範囲の値を使用してフィルタリング https://analytic-vba.com/power-query/m-code/filter-range-values/ （マナ） 2022/10/02(日) 20:29 ---- まだ最初の宿題をこなせていませんが、 [[20220927134419]] 『シフト表から今日出勤している人と出勤時間を詰めて表示させたい』（おかもと） [[20220927175834]] 『同一項目内で最大値を持つ行の参照』（ほかほか） [[20220928112513]] 『ピボットテーブルinputboxでフィール追加方...』（ちゃりできてない） [[20220929025914]] 『チーム別管理』（ぐっさん） [[20221002193557]] 『複数のセルを検索キーとして、データシートから行』（びぎなー） （マナ） 2022/10/02(日) 23:27 ---- 周回遅れの「使ってみた」ですけど [[20220913163504]]の所属一覧。 先に列名を変更してからピボット解除してみました。 let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], 列数 = Table.ColumnCount(ソース)-1, 連番 = List.Transform({0..列数}, each Number.ToText(_, "00:")), 旧見出し = Table.ColumnNames(ソース), 新見出し = List.Transform(List.Zip({連番, 旧見出し}), each _{0} & _{1}), 列名の変更 = Table.RenameColumns(ソース, List.Zip({旧見出し, 新見出し})), 列のピボット解除 = Table.UnpivotOtherColumns(列名の変更, {}, "部署", "名前") in 列のピボット解除 （d-q-t-p） 2022/10/03(月) 08:55 ---- [[20220927175834]] 『同一項目内で最大値を持つ行の参照』（ほかほか） を簡単にできるんじゃないかと思ってやってみましたが、あれれ?案外難しいぞ?と。 d-q-t-pさんに教えてもらった技をつかってみました。 let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], 変更された型 = Table.TransformColumnTypes(ソース,{{"店舗", type text}, {"担当者", type text}, {"勤務時間", Int64.Type}}), グループ化された行 = Table.Group(変更された型, {"担当者"}, {{"グループ別", each [最大時間=List.Max([勤務時間]),最大店舗=Table.SelectRows(_ ,each [勤務時間]=最大時間)][最大店舗] , type table}}), #"展開された グループ別" = Table.ExpandTableColumn(グループ化された行, "グループ別", {"店舗", "勤務時間"}, {"グループ別.店舗", "グループ別.勤務時間"}) in #"展開された グループ別" （´・ω・`） 2022/10/03(月) 17:15 ---- 再度 [[20220927175834]] 『同一項目内で最大値を持つ行の参照』（ほかほか） について、私の中で何が難しかったかというと、each の中で再度 each をつかうと _ がどっちの _ ? と混乱しました。 引数にちゃんと名前をつければ出来ました let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], 変更された型 = Table.TransformColumnTypes(ソース,{{"店舗", type text}, {"担当者", type text}, {"勤務時間", Int64.Type}}), グループ化された行 = Table.Group(変更された型, {"担当者"}, {{"グループ別", (t)=> Table.SelectRows(t,each [勤務時間]=List.Max(t[勤務時間])), type table}}), #"展開された グループ別1" = Table.ExpandTableColumn(グループ化された行, "グループ別", {"店舗", "勤務時間"}, {"グループ別.店舗", "グループ別.勤務時間"}) in #"展開された グループ別1" （´・ω・`） 2022/10/03(月) 17:37 ---- d-q-t-p さん 書き込みありがとうございます。 こういうのが、なかなか書けないんですよね。 でも、わかりやすいので応用できそうです。 ＞ 新見出し = List.Transform(List.Zip({連番, 旧見出し}), each _{0} & _{1}), （マナ） 2022/10/03(月) 19:02 ---- （´・ω・`）さん ありがとうございます。 皆さん、どんどんレベルアップして、一人おいてかれてます。 進歩のない、わたしからは、操作で完結する方法です。 ・[勤務時間]列で降順ソート ・インデックス列追加 ・[担当者]列で重複の削除 ・[担当者][店舗]列を選んで、その他の列の削除 let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], 変更された型 = Table.TransformColumnTypes(ソース,{{"店舗", type text}, {"担当者", type text}, {"勤務時間", Int64.Type}}), 並べ替えられた行 = Table.Sort(変更された型,{{"勤務時間", Order.Descending}}), 追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1, Int64.Type), 削除された重複 = Table.Distinct(追加されたインデックス, {"担当者"}), 削除された他の列 = Table.SelectColumns(削除された重複,{"担当者", "店舗"}) in 削除された他の列 （マナ） 2022/10/03(月) 19:04 ---- 補足 ＞ ・[担当者][店舗]列を選んで、その他の列の削除 「その他の列の削除」を実行すると、 選択した順番で、列が並び替えられます。 これを利用すると、列の並び替えのステップが省略できます。 （マナ） 2022/10/03(月) 19:15 ---- 列名の一括変更については、 ↓で、さいさい さんが紹介してくれたページがわかりやすかったです。 [[20220723112444]] 『パワークエリ データ型変更』（ハシビロコウ） （マナ） 2022/10/03(月) 19:35 ---- ＞進歩のない、わたしからは、操作で完結する方法です わたしのは、最長時間が同じ店舗が複数存在するとだめでした。 （マナ） 2022/10/03(月) 19:50 ---- 操作だけで完結する方法（再挑戦） ・テーブルデータ取得 ・データ型変更 ・クエリ名変更;元データ ・「元データ」を選んで、クエリの「参照」 ・クエリ名変更:最長滞在店 ・[担当者]列で、グループ化 新しい列名:最長時間 操作:最大 列:勤務時間 ・クエリのマージ 左:最長滞在店 照合列:[担当者][最長時間] 右:元データ 照合列:[担当者][勤務時間] 結合の種類:左外部 ・展開 ・不要列削除 '----- クエリ:元データ let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], 変更された型 = Table.TransformColumnTypes(ソース,{{"店舗", type text}, {"担当者", type text}, {"勤務時間", Int64.Type}}) in 変更された型 '----- クエリ:最長滞在店 let ソース = 元データ, グループ化された行 = Table.Group(ソース, {"担当者"}, {{"最長時間", each List.Max([勤務時間]), type nullable number}}), マージされたクエリ数 = Table.NestedJoin(グループ化された行, {"担当者", "最長時間"}, 元データ, {"担当者", "勤務時間"}, "元データ", JoinKind.LeftOuter), #"展開された 元データ" = Table.ExpandTableColumn(マージされたクエリ数, "元データ", {"店舗"}, {"店舗"}), 削除された列 = Table.RemoveColumns(#"展開された 元データ",{"最長時間"}) in 削除された列 （マナ） 2022/10/03(月) 20:49 ---- 補足 ＞・「元データ」を選んで、クエリの「参照」 https://www.kurumico.com/excel-powerquery-querycopy/5361/ （マナ） 2022/10/03(月) 20:55 ---- [[20220921091242]] 『複数のシートの値を転記したい』（黒ネコ） お題: ・複数シート（転記元）の値を、同じブックの一覧シートに転記 ・一覧シートA列とE列の値が、転記元のA列、B列の値と一致した場合に 転記元のC列、D列の値を、一覧シートF列とG列に転記 ・転記不要のシートもある（シート名:除外1. 除外2 とする） ・シートのレイアウトは、下記の通りとする 一覧シート -A- -B- -C- -D- -E- 1 T1 T2 T3 T4 T5 ←見出し 2 以下データ 転記元シート -A- -B- -C- -D- 1 T6 T7 T8 T9 ←見出し 2 以下データ （マナ） 2022/10/04(火) 22:02 ---- 操作手順 ★クエリ:対象ブック（接続専用） ・データの取得/ファイルから/Excelブックから で、自ブックを指定 ・[Kind]列でフィルター:Sheetを選択 ・[Name][Data]列を選んで右クッリク/他の列の削除 ・クエリ名を、「対象ブック」に変更 ★クエリ:対象シート（接続専用） ・「対象ブック」を選んで右クッリク/参照 ・クエリ名を「対象シート」に変更 ・[Name]列でテキストフィルター:指定の値と等しくない（一覧、除外1、除外2） ・[Name]列削除 ・[Data]列展開 ・[Column1][Column2]列を選んで、行の削除/重複の削除 ・1行目をヘッダーとして使用 ★クエリ:一致データ抽出 ・「対象ブック」を選んで右クッリク/参照 ・クエリ名を「一致データ抽出」に変更 ・[Name]列でフィルター:一覧のみ選択 ・[Name]列削除 ・[Data]列展開 ・1行目をヘッダーとして使用 ・列の追加/インデックス列 ・クエリのマージ 左:一致データ抽出 照合列:[T1], [T5] 左:一致データ抽出 照合列:[T6], [T7] 照合の種類:左外部 ・展開;[T6], [T7 ・インデックス列で昇順並べ替え ・列削除 ・閉じて次に読み込む （マナ） 2022/10/04(火) 22:06 ---- ★クエリ:対象ブック let ソース = Excel.Workbook(File.Contents("D:¥W〇〇¥△△¥□□.xlsx"), null, true), フィルター = Table.SelectRows(ソース, each ([Kind] = "Sheet")), 列削除 = Table.SelectColumns(フィルター,{"Name", "Data"}) in 列削除 '----- ★クエリ:対象シート let ソース = 対象ブック, フィルター = Table.SelectRows(ソース, each [Name] <> "一覧" and [Name] <> "除外1" and [Name] <> "除外2"), 列削除 = Table.RemoveColumns(フィルター,{"Name"}), Data展開 = Table.ExpandTableColumn(列削除, "Data", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}), 重複削除 = Table.Distinct(Data展開, {"Column1", "Column2"}), ヘッダー昇格 = Table.PromoteHeaders(重複削除, [PromoteAllScalars=true]) in ヘッダー昇格 '----- ★クエリ:一致データ抽出 let ソース = 対象ブック, フィルター = Table.SelectRows(ソース, each ([Name] = "一覧")), 列削除 = Table.RemoveColumns(フィルター,{"Name"}), Data展開 = Table.ExpandTableColumn(列削除, "Data", {"Column1", "Column5"}, {"Column1", "Column5"}), ヘッダー昇格 = Table.PromoteHeaders(Data展開, [PromoteAllScalars=true]), 元の順番 = Table.AddIndexColumn(ヘッダー昇格, "インデックス", 0, 1, Int64.Type), マージ = Table.NestedJoin(元の順番, {"T1", "T5"}, 対象シート, {"T6", "T7"}, "対象シート", JoinKind.LeftOuter), マージtbl展開 = Table.ExpandTableColumn(マージ, "対象シート", {"T8", "T9"}, {"T8", "T9"}), 元の順番に戻す = Table.Sort(マージtbl展開,{{"インデックス", Order.Ascending}}), 列削除2 = Table.SelectColumns(元の順番に戻す,{"T8", "T9"}) in 列削除2 （マナ） 2022/10/04(火) 22:11 ---- ＞・[Column1][Column2]列を選んで、行の削除/重複の削除 元スレで指摘されていますが、一致する行が、複数になることはないのか不明? 今回は、最初に出現したものを転記することにしました。 （マナ） 2022/10/04(火) 22:18 ---- クエリのマージで、照合列を複数選択した場合 テーブル内の並び順どおりの組み合わせで照合します。 今回の例では、[T1]と[T6] 、[T5]と[T7]の組み合わせで照合。 もし、[T1]と[T7] 、[T5]と[T6]の組み合わせにしたいときは、 事前に、列を並び替えるステップを挿入するか いったん、そのまま操作した後で、数式バーを編集してください。 = Table.NestedJoin(元の順番, {"T1", "T5"}, 対象シート, {"T6", "T7"}, "対象シート", JoinKind.LeftOuter) ↓ = Table.NestedJoin(元の順番, {"T5", "T1"}, 対象シート, {"T6", "T7"}, "対象シート", JoinKind.LeftOuter) （マナ） 2022/10/04(火) 22:22 ---- [[20220927175834]] 『同一項目内で最大値を持つ行の参照』（ほかほか） もう一例 ＞ グループ化された行 = Table.Group(変更された型, {"担当者"}, {{"グループ別", (t)=> Table.SelectRows(t,each [勤務時間]=List.Max(t[勤務時間])), type table}}), （´・ω・`）さん や d-q-t-p さんと違って どうやら、長くてネストする式をさらっと書くのは、わたしには無理みたい。 ということで、外に出してみました。（関数化） let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], 型変更 = Table.TransformColumnTypes(ソース,{{"店舗", type text}, {"担当者", type text}, {"勤務時間", Int64.Type}}), fn最大項目 = (テーブル as table, 評価列 as text, 抽出列 as text) as text => let 最大値 = List.Max(Table.Column(テーブル, 評価列)), フィルター = Table.SelectRows(テーブル, each Record.Field(_, 評価列) = 最大値), 抽出 = Text.Combine(Table.Column(フィルター, 抽出列), ",") in 抽出, グループ化 = Table.Group(型変更, {"担当者"}, {"店舗", each fn最大項目(_, "勤務時間", "店舗"), type text}) in グループ化 （マナ） 2022/10/05(水) 19:32 ---- [[20220927175834]]については数式でやったほうが楽なんですよね…… =LET( _hl,{"担当者","店舗"}, _c1,UNIQUE(テーブル1[担当者]), _c2,SORTBY(テーブル1,テーブル1[勤務時間],-1), _c3,BYROW(_c1,LAMBDA(_a,INDEX(FILTER(_c2,INDEX(_c2,,2)=_a),1,1))), VSTACK(_hl,HSTACK(_c1,_c3)) ) （d-q-t-p） 2022/10/06(木) 09:24 ---- d-q-t-p さん ありがとうございます。 ちょっと脱線しますが、せっかくなので、教えてください。 2店舗が同じ時間で最大値の場合に列挙するには、どうしたらよいでしょう。 列挙方法は問いません。 今回も回答をみると、わかりやすく、理解はできるのですが、 頭が硬いのか、応用ができません。 ちなみに、元スレの、どん さんの数式を組み入れてみましたがうまくいきませんでした。 現在の式が、わたしが理解できる限界に近いので、 とても複雑になるようなら結構です。 =LET( _ hl,{"担当者","店舗"}, _c1,テーブル1[店舗], _c2,テーブル1[担当者], _c3,テーブル1[勤務時間], _c4,UNIQUE(_c2), _c5,BYROW(_c4,LAMBDA(_a,TEXTJOIN(",",,FILTER(_c1,(MAXIFS(_c3,_c2,_a)=_c3)*(_c2=_a))))), VSTACK(_hl,HSTACK(_c4,_c5))) （マナ） 2022/10/06(木) 23:08 ---- マナさん すごいですねー。 ここは貸し切りですか。 ブログですか。 （IYAMI） 2022/10/06(木) 23:19 ---- 目障りのようなので、ここまでとします。 （マナ） 2022/10/06(木) 23:30 ---- ありゃ残念です （まっち） 2022/10/07(金) 09:27:55 ---- # ちょっと見逃してしまっていました。遅ればせながら、コメント。 そんなことないですよ。 凄く有益なやりとりができていると思います。 Power Queryを使う人にはとても参考になる記事だと思います。 みなさんから寄せられた質問に対して若干タイミングがずれるかもしれないが、 纏めて応えているわけで、質疑応答になっていて、何の問題も無いと思いました。 # 興味が無い方は見なければいいだけですよ。 # それはほかのスレだって同じでしょう? （γ） 2022/10/17(月) 23:00:30... - http://www.excel.studio-kazu.jp/wiki/kazuwiki/202209/20220925130324.txt - [detail] - similar
PREV
NEXT
Powered by Hyper Estraier 1.4.13, with 97012 documents and 608123 words.
訪問者：