タグ別アーカイブ: クエリの関数化

意外にも知られていないエクセルの便利機能・便利技~ガッツの日記 第13回

【エクセルはあまりに機能が多すぎるので、知られていない便利機能・便利技があります】

仕事柄、色々なところで色々なエクセルの活用の仕方に出会います

エクセルの活用者と話していて気づくのは、意外にも知られていない機能や便利技が結構あることです

今回の記事では「便利」だけど意外に「あまり知られていない」機能や便利技について、私の独断と偏見でピックアップして紹介します

計算の手動化

ワークシート内に数式を膨大に使用している場合、セルに入力するたびに膨大な計算が走ります

ですので、ワークシートが所謂「重たい」という状態になります

この場合、解決方法としては本来はデータベース等を導入することが考えられます

ただすぐに導入できる方法もあります

それが「計算の手動化」です

文字通り、数式の計算を止めます

そして、必要な時だけ計算します

数式タブには「計算方法の設定」というメニューがあり、こちらで数式の計算を止めることができます

ちなみに、通常は「自動」になっています

そして必要な時だけ「F9」ボタンを押せばファイル内(Shiftを押しながらだとシート内)の関数が計算されます

いや、入力するたびに計算結果が見たいんです、もしくは数式の計算をシート内に絞っても計算が遅いんです、という場合もあると思います

この場合は、VBAを使う方法があります

詳細はこの記事では書きませんが、VBAコードを入力というイベントに応じて動かす方法があります(イベントプロシージャーと言います)

イベントプロシージャーは、該当シートで右クリックすると表示される「コードの表示」で設定できます

次に開いた画面では、下の画像のように画面上の2つの欄を「Worksheet」「Change」に設定します

これで、入力したセルの行位置及び列位置を取得できます

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long
Dim myCol As Long

myRow = Target.Row ←行位置
myCol = Target.Column ←列位置

下の画像の事例では「手動で計算」の設定のままC列に入力した場合、1つ右のセルだけ計算されます

実際のVBAコードは次のようになります

If myCol = 3 Then
ActiveSheet.Cells(myRow, myCol + 1).Calculate

End If

入力位置が3列目(C列)だった場合、該当シートの入力位置がの一つ右を再計算(Calculate)

するわけです

これであれば、入力位置の行だけを計算できますので、かなり計算は軽くなります

テーブル機能

この機能を知ったのは恐らく5年ぐらい前だと思いますが、意外と浸透していないという実感があります

データが集まった表をテーブルにすると、何かと表内のデータを便利に扱えるようになります

下の画像では、1つのセルに関数を入力したことで、全ての行に関数が反映されています

つまり、データを扱う単位がセルではなく行単位になっています

テーブル機能には、上記のような便利な機能が満載なのです

この「満載」を具体的な一言で言い換えると、位置から名前への変換ということかもしれません

従来、エクセルを使おうとしたらセル単位という縛りの中でしか動けません

A列の5行目、C列の10行目といった感じでセルの位置をいちいち指定するしかありません

例えば、以下の画像のB列の合計を計算しようとしたら、マウスを動かしてC列の該当範囲を選択するしかありません

しかも、C列の範囲が拡大したら選択し直す必要があります

ところが、テーブル機能を使えば名前でセル位置を指定できます

テーブル機能では、作成したテーブルに名前を付けることができ、テーブル内の列名も自動的に活用できるからです

実は列名の指定もワンクリックでできます

上の画像のように、テーブルの該当「列名」の上にカーソルを置き、すっと上に少し動かすと、下向きの矢印がでます

これでテーブルの列名が指定できます

テーブルの列名で指定すれば、列を移動しようが、行の範囲が拡大しようが適切な範囲を合計できます

このようにテーブル機能は便利な機能が満載ですが、表をテーブルにするのは「Ctrl+T」のショートカットキー一つだけで行えます

とにかく簡単にテーブル機能は活用できますので、ぜひ今日から使用してみてください(→詳細記事

表をテーブルにしたら、テーブルに名前を付けるのもお忘れなく!

最後に、もう一点だけ付け加えさせてください

表をテーブルにするとMicrosoft のメニュー自体をうまく扱えるようになるという点です

これは具体的にはどういうことかというと、Microsoft のPower Platformではエクセルのデータをテーブルで使用するように設計されているからです

Power Appsなんかもテーブルからアプリがワンクリックで作れるようになっています→関連記事

ぜひテーブル機能は使いこなしていきましょう!

クエリの関数化

こちらはPower Queryの活用の仕方の話です

関数というと、SUM関数とかVLOOKUP関数を思い浮かべる方が多いと思います

数学でいうと、Xを使った(X+1)x(X+2)などの公式が関数と言えます

Xの値が何になっても一度作成した関数を使いまわして計算が行えます

クエリの関数化とは、一度作成したクエリをXを絡める関数にして使いまわすという意味です

Power Queryは大分、浸透したと思いますが、こちらのクエリの関数化はまだ知名度はいまいちという感じです

どういう場面で使うかというと、一番使えるのがシート別もしくはファイル別にデータが散らばっているケースです

例えば、以下のようにシート別にデータが散らばっていたとします

しかも、欲しいデータの上に無駄なデータがあります

これがもし、100シートあったとしたらデータをまとめあげる作業はどう行いますか?

手動であればお手上げだと思います

ところが、

クエリの関数化であれば1シートに対して行った処理を、ほぼワンクリックで複数シートに使いまわすことにより、簡単にデータの一元化が行えます

詳細についてはこちらの記事(→クエリの関数化)を参照して頂くとして、今回の記事では概念だけ解説いたします

上の画像は1シートからデータを取得し、無駄な2行を取り除いてテーブルを出力するクエリのソースコードです

実は黄色の箇所に「A」というシート名が入っています

つまり、ファイルの中のシートを取得した中からAシートを処理しているという訳です

この黄色の箇所を数学の関数でいうXに代えるわけです

以下の画像は関数化したクエリの一部です

このXにどういう風に各シートを連続的に割り当てるかというと、このソースステップの列を使用します

今回の事例では、実際にはItem列にある各シート名をフィルターして使用します

これでシート別などにデータが分散している問題をVBAなしで一気に解決できます

フリーアドイン

フリーアドイン機能もあまり知られていないと思います

スマホの便利アプリを利用するような形で、外部から便利機能を補って使用できます

こちらについては、アドインが沢山あるので「Formula Forge」というアプリの概要だけ今回は紹介します

例えば、こんな複雑なIF関数があったとします

読者の皆さんの中には「こんなの序の口だよ」と思われた方もいらっしゃると思います

Formula Forgeを使うと複雑な数式でも図解を一瞬でしてくれます

前任者の複雑なエクセルを引きついで苦しんでいる方にはおすすめのアドインです

<まとめ>

今回は意外と知られていない便利機能・便利技を大きく分けて4種類を紹介しました

とにかく知っているか知っていないかでこの辺りは差が大きく出ますのでぜひ押さえておいて欲しいところです

ところで、

私が思うに、エクセルの機能を最大限使いこなすために一番大事なことは「エクセルの限界」を知ることだと思います

どうしてもエクセルの方が相対的に劣る機能があります

例えば、エクセルは表計算ソフトなので大量のデータを扱うのはあまり得意でないですし、Power Platformなどかなり便利なクラウド機能も出てきています

ですので、最終的にはエクセルの機能を深堀するだけでなく「正しい脱エクセル」も意識する必要があると思っています

この点はまた別途詳しく書きたいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

クエリの関数化事例~複数シートを一括で編集する

【一度作成したクエリを関数化して複数シートに使いまわし、結果的に一括処理する】

エクセルファイル内で複数シートに分かれていると、一括処理ができなくて困ることがあります

一番、困るのは各シートにヘッダーデータがあるようなケースです

このような場合はまずは1シートを処理するクエリを作成します

そして、そのクエリを関数化します

関数化するということは、Y=2X+1のような式で表せるということです

こちらのXには複数シート名を入れて使いまわせるようにします

SUM関数/SUM()では()の中身を変えれば、それぞれの合計が出力されます

今回は、()の中身が次から次へと格納されて、連続的に出力されるイメージになります

使用データ

前述のように各シートにはヘッダーデータがあります

その下に欲しい一括処理したいデータがあります

そして、上記のようなシートがファイル内に複数あります

各シートの開始行などは一致している前提です

上の画像は東京のシートですが、次の画像は大阪のシートです

関数化するクエリの作成

まずは1シートを処理するクエリを作成します

こちらのクエリを前述のように関数化します

今回はテーブル化からはじめず、ファイルからデータを取得します

指定するファイルは「今処理しているファイル」です

ファイルを指定したら、ナビゲーター画面が開きます

こちらでは1つのシートを指定し「データの変換」をクリックします

ここでクエリは複製しておきます(1つは関数を作成した後に、関数を適用するクエリになります

下の画像がPower Queryエディタを開いた状態です

必要なのは11行目から下です

こちらは「行の削除」アクションで10行削除します

ここからもう1アクションあります

変換タブで「1行目をヘッダーとして使用」アクションを使用して、見出しを整えます

これで関数化のもとになるクエリは完成です

関数化

関数化は詳細エディタで行います

具体的に関数化する箇所は「可変の箇所」、シート名の箇所です

ここでソースステップの内容を確認しておきましょう

要はファイル内の複数シートを取得しています

ここからナビゲーション画面で1つのシートを選択しています

ですので、作成した関数を適用する場合は「東京、大阪、名古屋」と関数化した箇所に連続して格納されるようにすればいいのです

では実際に記号Xを埋め込んでみましょう

(X)=>はXを記号として使用するという合図になります⇒詳細

ここまで行うとクエリが関数に変ります

関数は「myFunction」と名前を変えておきましょう

ここでパラメーターに「名古屋」と入れてみましょう

これで名古屋のシートデータが変換されて出力されます

関数の適用

ここで関数の複製元のクエリを選択し直します

その後は、適用したステップでソースデータを残して後は全て削除します

ここから列の追加タブで作成済みの関数を呼び出します

関数のXに入るのは「Item」になります

これでXに東京、大阪、名古屋と値が格納され、クエリが作成されていくことになります

結果、次の画像のようになります

ここからmyFunctionを展開すれば完成です

次の画像が展開した後のものです

<まとめ>

今回は、ヘッダー行がある複数シートを、クエリの関数化を使用して一括処理しました

クエリの関数化のポイントは可変の箇所を見つけることです

今回は、1度作成したクエリから可変の箇所を見つけました

そうはいっても、なかなか最初は慣れないかと思いますが、次第にツボが見えてくると思います

もう少し詳しく「クエリの関数化」について知りたいという方はぜひUdemy講座(動画)もご利用ください

期間限定で「4,800⇒1,200」の割引クーポンが利用できます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

UDEMYの動画コース「Power Queryを極めたい人必見!!M関数講座」の無料・勉強会の開催報告

昨日、リリース済みのUdemy動画コース「Power Queryを極めたい人必見!!M関数講座」の無料勉強会を開催しました

参加して頂いた皆様には感謝の一言です!

今後の動画コースの内容、並びに、Power Queryの技術改善に多くの示唆を得ることができました!

今回の勉強会では2点「let式を使用した複数ステップの集約」「クエリの関数化」に絞ってお話させて頂きました

長いクエリの対策

Power Queryは便利なので、Power Queryに慣れてくるとどうしても適用したステップが長くなります

ステップが長くなると、後で見返してもどこがどうなっているのかわからなくなったりします

この点が、皆でPower Queryを共有して使う時にも問題となります

今回、日常的に業務でPower Queryを使用している参加者の皆さんと議論させて頂き「長いクエリ」への対策が必要なことがよく認識できました

そして「let式を使用した複数ステップの集約」の技術が長いクエリへの対策に役立つことも、参加者の皆さんに意見や感想をお聞きしてよく認識できました

下の画像のように、カスタム列作成画面でlet式を使用すれば複数ステップを1つのステップに集約することができます

しかも、コメントなども入れれば処理が複数ステップに分かれている場合よりもステップの意図がわかりやすくなります

let式の文法に慣れる必要がありますが、この方式を積極的に使えば、長くクエリをシンプルにわかりやすくできます

クエリの関数化

「販売単価の列」「生存原価の列」と「販売個数の列」を乗算し「利益を計算する列」を作成するなどのカステム関数はこのブログでも紹介しました

今回解説したのは、クエリ自体を関数化する方法です

通常のカスタム関数では各行にて1つの値(販売単価、製造原価、販売個数⇒利益)が出力されますが、クエリの関数化の場合には値の代わりにテーブルが算出されます

今回紹介したのは、ファイル内のテーブルを取得するクエリの関数化ですが、思った以上に使い道がありそうです

複数ファイルを取得するのは、フォルダ内の複数ファイルを取得する方法でも行えます

参加者からおききしたのですが、この方法だとクエリが重くなり、フォルダからファイルを幾つか抜き出すケースもあるそうです

確かにフォルダ内のファイル一括取得は便利ですが、日常業務では確かに頻発しそうな問題だと感じました

今回紹介したデータ取得するクエリの関数化ではフォルダ内のファイルを細分化して取得することができるので、クエリが重くなる可能性を低くできます

データ取得以外にもクエリの関数化の用途がありそうなので、今後も積極的に研究していきたいと思います

<まとめ>

今回は本当に貴重な意見をいくつもお聞きすることができました

今後もユーザーの方との交流しながら、自分自身のスキルアップを図っていきたいと思います

Udemyの動画も頂いた指摘を基にして修正を行っていきたいと思います

⇒Udemyの動画・詳細はこちらから

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ