すべてのカテゴリ

おはようございます。こんにちは。こんばんわ。 改善が大好きな、齋藤正夫です。 ブログをご覧いただき、ありがとうございます。 今日は、Excel に ビルド されている機能 「Power Query」について、語ろうと思います。Excelでは、たぶん、2013のアドインから使用可能になりましたね。ところで、みなさん、Power Query を使った事ありますか？私は、なにかとExcel で データ分析となると、かならず Power Query を活用します。ただ・・・、普通のExcel ユーザーから見たPower Query はこんな感じなイメージです。「はぁ？？？　なにこれ、関数も機能も使えないじゃん、こんなの使えない。」という具合で、Excelしか触ったことがないユーザーは、かならずこうなります。当然です。Power Query は、データベースソフトですから。表計算ソフトではありません。当然、VLOOKUP関数なんて使えません。その代替で、データベースの強みとなる機能を扱えます。例えば、データの統合、データとデータのマージ、グループ化、表崩し。それが、高速で処理が可能になるのです。Excelでこんな経験はしていませんか？「再計算・・・何分かかってるの？」データが数十万件、数百万件を、SUMIFで集計をすると、よく起きる現象です。だったら、その集計を、Power Query でやらせてみませんか？劇的に処理速度が上がります。その代表の機能が「グループ化」です。SUMIF(SUMIFS)と同じことが出来るようになります。※ ただし、明細データは見れなくなります。処理も早くなり、

おはようございます。こんにちは。こんばんわ。 改善が大好きな、齋藤正夫です。 ブログをご覧いただき、ありがとうございます。今回は、PowerQueryの思い、私の考えです。あなたは、Excel の Power Queryを使った事あるでしょうか？私が初めて触ったのは、会社で、Office 365（たぶん2年前）が導入された日です。速攻で、Excel で、PowerQuery を使いました。その初めて触ったPower Queryの感想は、「これは、Excelしか触ったことがない人には、使えない代物だ。ただ、Accessを触ったことがある人なら、使いこなせる。」です。ま、ようするに「簡単な機能じゃねーぞ、こりゃ！」です。データベースの基本的な知識が必要と思ったのです。一般事務員は、データベースなぞ教育されるわけがありません。ただ、私の感覚では「もしかして、今まで、マクロを作らないと自動化できなかったことも、マクロ不要で、自動化できるかも？」と思いました。数日たって、とあるデータを印刷するAccessがあって、そいつの改修要求がありました。Windows 7から、Windows 10 にする為のアプリのコンバージョン依頼です。直感的に「あっ、これ、Excel+Power Queryで、マクロ不要で自動化できるじゃん！」と思い、早速、Excelで、一から制作。ま、４時間程度で完成。思ったように、Excel で、マクロ無しで、自動化が実現できました。Excelでデータ分析することも多いと思います。そんな方は、是非、Power Queryを使ってほしいです。でも、簡単ではないので、お勉強す

作成 　Excel　　テーブル　　　現在のブックExcel.CurrentWorkbook(){[Name="テーブル名"]}[Content]　　　ファイルを指定Excel.Workbook(File.Contents("ファイルのフルパス")){[Item="テーブル名"]}[Data]　AccessAccess.Database(File.Contents("ファイルのフルパス")){[Item="テーブル名"]}[Data]結合 　縦 Table.Combine({テーブル1, ...})

チェック　等しい値 = null　等しくない値 <> null

リテラル"文字列"　改行"#(cr,lf)"　タブ"#(tab)"連結"s1" & "s2"

作成Table.Pivot(テーブル, 列にする値のリスト, "列にする値が入ってる列名", "入力する値の列名") ex)　列１　の値をすべて列にして　列２の値を入力するリスト = Table.Column(テーブル, "列1")Table.Pivot(テーブル, リスト, "列１", "列２")

each　関数の省略表現同じ意味の関数定義　ex1)　値 + 1　を返す関数関数 = (値) => 値 + 1関数 = (_) => _ + 1関数 = each _ + 1　ex2)　行の列の値　を返す関数関数 = (行) => 行[列名]関数 = (_) => _[列名]関数 = each _[列名]関数 = each [列名]

let式　let　に変数を宣言して値を割り当てながら 　最終的に　in　に記述する　変数n　をつくる 　宣言した変数は　let, in　内で使える let     変数1 = 式1,    ...    変数n = 式nin    変数n　　※ in　の直前の行末は　,　なしスペースを含む変数名　入力#"変 数" = 値　取得#"変 数"

作成　リテラル{値1, ...}値　取得　　インデックスを指定　　　インデックスは　0～リスト{i}　　最初List.First(リスト)　　最後List.Last(リスト)要素数List.Count連結リスト１ & リスト２フィルタList.Select(リスト, 要素を受け取って真偽値を返す関数)　関数　　要素を受け取って真偽値を返す(要素) => 要素に対する条件式　each　を使った省略記法　　ex) 要素が1より大きいeach _ > 1チェック　含まれるか List.Contains(リスト, 値)

日付　作成#date(年, 月, 日)　値 　　日 Date.Day(日付) 　　月 Date.Month(日付) 　計算日付 + 期間日付 - 期間期間　作成#duration(日, 時, 分, 秒)

　等しい 値1 = 値2　等しくない 値1 <> 値2論理　AND条件1 and 条件２

真true 偽false

計算　剰余Number.Mod(数, 割る数)型変換　文字列　→　数値Number.FromText(文字列)

作成　空 ①データタブ ②データの取得③その他のデータソースから④空のクエリ

追加Table.AddColumn(テーブル, "列名", 値を指定するeach式)　集計列　　合計 Table.Group(テーブル, "基準列名", {"集計列名", each List.Sum([集計対象列])})　　複数の基準列Table.Group(テーブル, {"基準列名１", "基準列名２"}, {"集計列名", each式})　　複数の集計列Table.Group(テーブル, "基準列名", {{"集計列名１", each式１}, ...})削除Table.RemoveColumns(テーブル, {"列名1", ...})列名を変更Table.RenameColumns(テーブル, {{"変更前", "変更後"}})値 　取得 行[列名]　　すべての値をリスト化Table.Column(テーブル, "列名")フィルタTable.SelectColumns(テーブル, "列名") 　複数の列 Table.SelectColumns(テーブル, {"列名1", ...}) データ型 　指定 Table.TransformColumnTypes(テーブル, {{"列名1", 型1}, ...})

基本構文　let　にステップ変数を宣言して値を割り当てながら 　in　の式を作る　in　の式は結果として表示される　宣言したステップ変数は　let, in　内で使えるlet     ステップ1 = 式1,    ...    ステップn = 式nin    式　　※ in　の直前の行末は　,　なし

取得　インデックス　　インデックスは　0～テーブル{i}フィルタTable.SelectRows(テーブル, フィルタ条件のeach式) 並べ替えTable.Sort(テーブル, {"列名"})　複数Table.Sort(テーブル, {"列名１", "列名２"})

Power Queryエディター 　開く ①データ ②データの取得③Power Queryエディターの起動詳細エディター　開く①Power Queryエディター ②表示③詳細エディター

文字列type text 数値 type number 　整数 Int64.Type 　小数 Double.Type 　% Percentage.Type 日付 type date

ifif 条件1 then     処理1else if 条件2 then    処理2...else    その他の処理

定義 関数名 = (引数1, ...) => 戻り値関数名 = (引数1, ...) =>    let式引数　型指定(引数 as 型) => ...　省略可能(optional 引数) => if (引数 = null) then 引数なしの処理 else 引数ありの処理戻り値　型指定(引数) as 型 => ...