analytic-vba のすべての投稿

ピボット解除を複数列を軸にして実行する

パワークエリのピボット解除はとても便利ですね

 1つ列を軸として選択 ⇒ その他の列のピボット解除、これだけでデータの縦横/↓→並びを縦縦/↓↓に変更できます

でも、複数列を軸にしてピボット解除をする場合にはどうするんだろう?

実はこちらについてもとても簡単にできます

ピボット解除の前に軸にする列を指定するだけです

今回の内容

ピボット解除を行うデータは下の画像です

上の画像の黄色い箇所、カテゴリー地区を軸にして「4月~6月」を横並びから縦並びにかえます

複数列を軸にしたピボット解除

解説はPower Queryエディターからはじめさせて頂きます

ピボット解除を行う前に、軸にする列を下の画像のように選択しておいてください

複数列を同時に選択する場合には、Ctrlキーを押しながら選択してください

このまま「その他の列のピボット解除」をクリックしましょう

そうすれば、選択した列以外の並びが変わります

<まとめ>

ピボット解除はとても便利です

データの並べ替えによるデータのクリーニングはとても重要な作業です。ミスが起こっては困ります

このピボット解除を適切に実行すれば、データクリーニングの質は格段に向上します

今回の内容はぜひ有効活用して頂きたいと思います


にほんブログ村

とてつもなく大きい表を扱いやすくする~動く条件付き書式~

【カオスな現場経験 x 分析力を組み合わせた、少し尖ったマクロのレシピ集】

とてつもなく大きい表を使っているときに迷子になることがありませんか?

「今、どの行どの列セルを扱っているんだろう・・・」

しまいには、ミスが怖いので表を印刷して「定規」を当てたりしたことはありませんか?

今回、この迷子対策として参考にしたのがこの記事です

参考記事

今回は迷子対策として、上の記事を基にした「動く条件付き書式」の事例を紹介します

この動く条件付き書式では、カーソルを置いた位置の行列の色が変わるので、「迷子対策」にはバッチリです!

紹介する事例は2つあります!

1つ目の事例は、CELL関数とROW関数、そしてCOLUMN関数を使用して条件付き書式を設定します

こちらは、手動で条件付き書式を設定する内容になっています

 *イベントプロシージャ―に1行コードを書く必要があります

この方法の場合には、重たいファイルの場合だと「条件付き書式」の動きがかなり重たくなります 

 2つ目の内容は、イベントプロシージャ―を使用して、カーソルが動く都度「条件付き書式」をマクロで自動設定します

 こちらは条件付き書式の範囲をで指定するわけではなく、「行列」双方向の2本の線で範囲を設定しますので、あまり動きを重くしないですみます

 後、2つ目の方法の場合には色をシート上で設定できるようにするなどの、実践的に使えるようにする為の工夫を複数行っています

こちらは、サンプルファイルをダウンロードして、実際の動きを確認できます!!

①条件付き書式を全て手動で設定

まずは、条件付き書式を設定する範囲を手動で指定します

その後、条件付き書式の設定画面を開き、ルールの種類の選択を「数式を使用して、書式設定する設定を決定」にします

「次の数式を満たす場合に値を書式設定」には、画像のような数式を設定します

数式:=OR(CELL(“ROW”)=ROW(),CELL(“COL”)=COLUMN())

*上の数式をそのままコピーする際には、が半角になっていることをご確認ください

これで条件付き書式の設定は終了です

実はもう一つ、仕掛けが必要です

イベントプロシージャ―を使用し、セルの選択が変更になった場合に「更新処理」を行う必要があります

 更新処理を設定するには、まずは、上の画像のように該当のシートで右クリックをして「コード表示」を選択します

 次に開いたVBE(Visual Basic Editor)画面では、下の画像の黄色の印の箇所がそれぞれ「Worksheet」「SelectionCange」になっていることを確認します

プロシージャ―には1行だけコード*を入れれば、条件付き書式の更新処理が設定されます

 *Application.ScreenUpdating = True

②都度、条件付き書式の範囲をマクロで設定

動く条件付き書式

こちらは、前述のようにこの「動く条件付き書式」を実践的に使えるように様々な工夫を行っています

各種設定シートというシートを設けてあり、様々な設定が行えます

工夫一覧

1.シート上での色設定

各種設定シートにて、色を指定すれば、条件付き書式で設定する色を変更できます

2.開始行や開始列の設定

条件付き書式の設定を行う開始行と開始列はシート上で調整できます

上の画像では開始行が6行目、開始列が1列目ですので下の画像のように色が変わります

3.停止・再開処理他

この動く条件付き書式は「停止」したい時があると思います

この際は、停止ボタンを押せば停止ができる仕掛けになっています

停止処理を行った場合には、停止前に設定された色が残ってしまっています

ですので、上の画像の消去ボタンを押せば「設定残り」の色は消去できます

コード上の工夫

こちらは、ごく簡単なポイントだけを解説させて頂きます

この事例は、前述のように条件付き書式の範囲が拡大したり縮小する場合も想定しつつ、処理が重たくならないようにします

こちらの場合は、色の付け方が、1番目の方法と変えてあります

カーソルがある位置が、色のつく範囲の最終行と最終列になります(1番目の方法も可能ではありますが、今回は極力色設定する範囲を小さくするという趣旨でこの方法にしました)

ポイント1 ⇒面ではなく、行方向1行、列方向1列で範囲設定

カーソルを移動させた際は、イベントプロシージャ―にてカーソル位置を取得します(Targetを引数にして取得)

カーソル位置を取得したら、2つ範囲を設定します

1つ目は行方向です。範囲の開始列は1列目などを固定で指定して範囲設定します

2つ目は列方向です。範囲の開始行は1行目などを固定で指定して範囲設定します

ポイント2 ⇒設定済みの条件付き書式は事前にクリアしておく

重複して条件付き書式が設定されないように、設定済みの条件付き書式はクリアしておきます

ポイント3 ⇒マクロ処理の途中でイベントプロシージャ―は止めておきます

1つの範囲に条件付き書式を設定した際に、更にイベントが起動してしまいますので、次の1行のコードでイベントを中止しておきます(後で再開が必要です)

Application.EnableEvents = False

<まとめ>

 今回はとてつもなく大きい表を扱う際、迷子にならないようにカーソルがある行と列の色を変える方法を紹介しました

但し、

特に2番目の方法などが当てはまるのですが、実際に業務で活用する場合は、さらに一工夫が必要です

 例えば、2番目の方法は設定済みの条件付き書式をクリアしてしまいますので、「迷子対策」以外の条件付き書式の設定が消えてしまうケースも想定されます

ですので、別途「迷子対策以外」の条件付き書式も自動設定する必要があります

 ちなみに、2番目の事例のサンプルファイルですが、こちらは複数のシートで「動く条件付き書式」を試せるようになっています

ぜひ、各種設定シートで「シート名」を変えながら試してみてください!

アイコン

動く条件付き書式 211.93 KB 4 downloads

...

なお、コードについては非公開とさせて頂いていますので、予めご了承ください 

それでは、最後まで長文にお付き合い頂きありがとうございました!

 今回紹介した仕組みは、実務では更に工夫の余地があると思いますが、エクセルVBAならではの便利さが生み出せると思います!

少しでも、大きい表を扱う際の「イライラ」を消すことに貢献できたら幸いです

もし、質問や要望等がありましたら「お問合せ」からご連絡をお願い致します


にほんブログ村


にほんブログ村

ゲームではじめるマクロ/VBA_セル変更

今回は3番目の迷路に取り組みます。今回のテーマは「セル変更」です

セル変更と聞くと、何をイメージしますか?

 セルの中の値でしょうか?こちらが一般的ですね。でもフォントの大きさや色などの変更もセル変更には違いないですよね

 VBAコードを書く際、セルが操作対象の場合にはセルの「何」を「どのように変えるか」を明確に指定する必要があります

 今回はセルという操作対象の値を変えたり、フォントのサイズを変えたりするVBAコードに触れて、VBAコードの作法に慣れていきましょう!

アイコン

めいろ3 182.86 KB 1 downloads

...

めいろ3を動かしてみる

今回、オバケをつかまえるのに書くコードはわずか2コードだけです!

右に行くか、下に行くかそれだけです

オバケをつかまえるまで、このコードをひたすら繰り返します

では、何を基準にして「オバケをつかまえた」とするのでしょうか?

それはカーソルの位置でしょうか?こちらはぜひ、事前に想像してみておいてください

ペンギンのサイズ

1回目の時に「.」は「~の」という風に解釈してみてくださいという記事を書きました

今回はこの点を深堀しつつ、「=」を使って「~に変える」という操作にも注目してみましょう!

さて、

めいろ3を実際に動かしてみると、ペンギンが小さくなったり大きくなったりします

こちらはセルのフォントを変更している訳ですが、表示されるコードに注目してみましょう!

Selection.Font.Size = 14

Selection.Font.Size = 20

こちらの2つのコードは「Selection/選択個所」「フォント」「サイズ」を「14/20に変える

そんな風に解釈してください

オバケをつかまえる

めいろ3を動かして、オバケの手前までくると、下の画像のようなメッセージがでます

Valueについては中味、もしくは値を解釈してみてください

ちなみにSelection.Offset(0,1)は「選択個所」「一つ右」という意味です

つまり、一つ動いた先のセルの中味/値がオバケの場合に「オバケをつかまえた」ということでこのゲームは動いているのです

下の画像がオバケをつかまえた際に出てくるコード一覧です

黄色の箇所に注目して頂きたいのですが、「選択個所値」と「オバケがいるセル値」が等しくなるまで(Do Until)2つのコードを繰り返すという意味になっています

<まとめ>

今回は、めいろ3を通じて「.」と「=」の使い方に慣れて頂きたいと思いました。この「.」と「=」の使い方を理解することは、マクロ/VBAのマスターへのかなりの近道になるはずです。

次回は実際に「.」と「=」を使ってコードを書いてみましょう


にほんブログ村

オンライン化に対応した未来を創ります!

【新たなオンラインWEBスクール「HINT」様と新たな「学び」を提供します!】

コロナ禍がもたらしたもの

 新型コロナウイルス感染症が日本で最初に発見されたのは、2020年1月15日だそうです

このコロナ禍により、我々の生活は大きく変化しました

 我々の生活の主体は「リアル」から「オンライン」主体に移行しました

 ネットショッピングを主体とした巣ごもり消費が浸透するとともに、仕事の主戦場が「オフィス」から「リモート」に移行しました

 つまり、我々の生活・仕事はリアルへの対応からオンライン仕様への変化が求められているのです

HINTのサービス

好きな時間に」「学びたい内容だけピンポイントで」「大人数ではなくマンツーマンで

*運営会社:株式会社DEX

 HINTは「学び」についても、ネットショッピングのように手軽に「オンラインで」行えることを目指した新サービスです

 Excellent仕事術では「オンラインで学習するメリット」を追求したこのサービスに参画し、「仕事のオンライン化に対応したエクセル術」の普及を進めます!

オンラインに対応したエクセル術

 オンラインに仕事の主戦場も移行したことにより、個人の仕事の仕方も大きな変更がもたらされています

 会話や人間の経験、勘よりも、データがより重視されるようになりました

 個人が扱うデータ量が増え、データの説明もペーパーではなく、画面を利用して説明する機会が増えました

 一方で、これまでであれば職場でデータの扱いに詳しい人に聞いたり、IT部門に相談して解決できたようなこともなかなか解決が難しくなっています

 Excellent仕事術では「仕事のオンライン化」に対応した新たなエクセル術を、新たなオンラインサービスHINTを通じて提供します!

・エクセル新機能/パワークエリ x データベース理論 ➡ 詳細

 ITのプロの様に、大量のデータを効率良く扱えるようにする「お得な2時間」のレッスンです!

・エクセルダッシュボード ➡ 詳細

 レーシングカーのコクピットにまるで座っているかのように、1画面で組織に必要な数字を魅せるテクニックです!画面内容をボタン一つで切り替えられるので、オンラインでの数字説明に最適です

・関数術 ➡ 詳細

 関数が苦手な人は、関数独自の「同時に複数の参照」が実感できていません

 この同時参照の動きをエクセルシート上で動くアニメで実感できるようにします

新たな未来を創ろう!

コロナ禍がもたらしたのは、実は生活の変化だけではありません

オンラインの浸透により「時間」の感覚が変わってきています

 1年前の常識は通用せず、変化に対応していかないとどんどん取り残されていきます

「思い立ったが吉日」

 もし、この記事を読んで少しでも「響く」ものがあったらぜひご連絡をください!

初心者から上級者まで幅広く対応させて頂きます!

事前問合せは無料です!

新たな未来創りに向け、ぜひお力にならせてください!!


にほんブログ村

弾丸チャート作成マクロ

【今回は、プレゼンの場などで注目されること間違いなしの「弾丸チャート」を自動作成するマクロを紹介します】

読者の皆さんは、会議でこんなグラフを見かけたことはありませんでしょうか?

こんなグラフを出されると、どこから見始めていいかすら悩んでしまいます

本来、グラフは一目で必要な数字を分かるようなするためのものです

ですので、グラフは無駄な情報はなるべく省き、とことんシンプルにすべきです

「そうはいっても、表示すべき情報は5種類あるんですけど・・・」

という方には、ぜひこちらの「弾丸グラフ」をお勧めします

この弾丸チャートには、シンプルに魅せるための3つの工夫があります

①折れ線グラフは表示せず、グラフの形を統一する

②色種類を統一し、データの違いを濃淡で表現する

③テキスト表示を組み合わせる

この3つの工夫により、データ種類が5つあっても「一目で」伝わりやすくなります

今回は、この弾丸チャートを自動作成するマクロを提供します

ぜひ、プレゼンなどの場で有効活用してください!

使い方

データの入力

ファイルの中に「グラフデータ入力」というシートがあります

こちらのシートで数字データを入力します

上の画像で、赤い点を付けた箇所が「数字データ」を入力する範囲です

グラフの横軸に表示する内容(上の画像の東京等)は、A列の5行目から9行目までに入力してください

尚、横軸の内容の行数と数字データの行数に相違があると、グラフの表示などが狂うので注意してください

グラフの凡例の表記やデータラベル/テキストボックスの表記を変える場合には、下の画像の黄色のセルを変更してください

画像に alt 属性が指定されていません。ファイル名: image-22.png

グラフ作成

実際のグラフ作成は「弾丸チャート作成」シートで行います

まずは、基調となる色を3つから選びます

 その後、「グラフ作成ボタン」を押すと「基調とする色」と「データの最大値」が反映されたグラフが作成されます

ここでいうデータの最大値とは、数字データの最大値です

こちらの最大値が軸の最大値になるような仕掛けになっています

次に、グラフの右にあるテキストボックスを各グラフの上に配置します

 もし、テキストボックスを配置するスペースが狭いとおもったら、グラフエリアを下のGIF画像のように上限に調整してください

 仮にテキストボックスの小数点の表示数を変えたい場合には「グラフデータ入力」シートの下の画像の箇所を変更してください

 例えば、小数点の第二位まで表示したい時には、TEXT関数の中の第二引数を「#,##0%」から「#,##0.00%」に変えます

<まとめ>

記事の冒頭でも解説しましたが、グラフはシンプルで見やすいのが一番です

今回紹介した弾丸チャートは、予実対比のようなデータ種類が多い場合には最適です

実は、記事の冒頭で紹介させて頂いた事以外にも、グラフの見た目をシンプルにするための工夫をしています

軸や目盛り線を表示せず、一目で数字が分かるように数字を横軸に表示したりしています!

残念なのは、今回のマクロだとデータラベルを手動で配置しなくてはならない点です

本来であれば、下のGIF画像のように自動で配置されます

データラベルも自動配置したい場合には、ぜひ過去の記事をご参照ください!

こちらの記事では、4つのグラフを複合して、弾丸チャートにする方法を解説しています

グラフが3つでなく4つなのがポイントです

4つ目のグラフは透明にしてあります

3つの系列の最大値(実績100、前年80、予算50の場合は実績)を透明なグラフにしてデータラベルだけ表示します

最大値を取ることで、データラベルが他のグラフに重ねないように表示できます

弾丸チャートは一見、難しそうですが、実は上記のような簡単な仕組みです

ぜひ記事を参照してチャレンジしてみてください

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

にほんブログ村

M言語に慣れる_1回目~コード構造の把握~

【M言語は難しくない!】M言語への理解を深めて、Power Queryの「まだ触れたことのない便利機能」を有効活用できるようになりましょう!!

https://youtu.be/HMcsg_B-gm0

 パワークエリは直感的なクリック操作で一括処理が行えるのが大きなメリットです。ところが、エクセルシート上では簡単に行えていた「セル単位での操作」や、「別シートの参照」、「関数を組み合わせた処理/ネスト」に相当する処理が行えません

要は、Power Queryエディタ内のメニューにある、行列単位の一括処理しか行えないのです

 Power Queryエディタ内のメニューにある処理以外の事をしようとすると、M言語の領域にまで踏み込むしかありません

ところが、詳細エディタや数式バーを見ると、小難しそうなコードが並んでいます

M言語は一見、取っ付き難いのは確かです

但し、特定のルールや使用パターンさえ押さえてしまえば、実はそんなに難しくないはずです

まずは簡単な例にて、M言語を活用することが必ずしも難しくないことを紹介したいと思います

下の画像は、あるクエリのPower Queryエディタ内の画像です

このエディタ内で<赤印の「販売金額」列の1行目の「54000」>の箇所を抽出してみます

エクセルシート上で言えば、「=セル名(例:A9など)」を数式バーに入れる処理です

ちなみに適用したステップには、まだ1つのステップ「ソース」しかありません

この場合、値の抽出は数式バーに簡単な1行を入れるだけで行えます

下のGIF画像の数式バーに注目してください!

=ステップ名[列名]{行位置}という単純なコードを入力しただけです

 但し、コード内の角括弧:[]と波括弧:{}の使い方のルールが分からないと小難しく感じてしまうただそれだけなのです

逆に、[]{}の意味が分かるだけでも「かなりのことが行えそう!」と感じませんか?

少し種明かしをすると、[]がセル名のA列やB列に相当するものです

{}が「A9セル」や「B2セル」の行番号に相当するものです

そしてコード内のステップ名が、セル名の前にを伴って付くシート名(例:平均値の計算!A2)みたいなものです

ルールやパターンが分かれば、エクセルシート上での処理とそれほど違いはないのです

この回では、M言語に慣れるためのウォーミングアップとして、M言語の特徴を3つに絞り解説します

そして、最後にPower Queryの「まだ触れたことのない便利機能」の事例を1点解説します

今回解説する「まだ触れたことのない便利機能」 の便利さを肌で理解した時には、「M言語理解へのハードル」が確実に少し下がっているはずです

1.コードのカラー

まず、Power Queryエディタ(以降、エディタ)で「列のマージ」をしてみます

マージする内容は、下の画像の「姓」と「名」です

「姓」と「名」の間には、スペースを入れます

すると、数式バーが次のような表示になります

上の画像には、黒を除いた「3色」が含まれています

この3色にはそれぞれ意味があります

①赤➡文字列

列をマージする際に、新しく追加される列の「列名」として指定した文字列「氏名」が赤色になっています

②青➡システム予約語

後の回で、個々の用語の意味などは詳細を解説しますが、「each」や「type」などが青色になっています

これらは「システム予約語」と言われるものです

他に「if」「else」なども該当します

システム予約語は、システム言語として、予め使用することが決まっている用語です

③緑➡ハードコード(自動記録)された値

こちらは、エディタ内でステップを追加した際に、ハードコード(自動記録)されたです

上の画像は、列をマージした時、自動的にデータ形式が「文字列」として自動記録されたことを表しています

2.大文字と小文字の区別

M言語を扱う際に、意識しておかなくてはならないことの一つとして「大文字と小文字」の区別があります

仮に、下の画像の「T」を小文字の「t」に置き換えてみます

すると、下のGIF画像のようにエラーになります

M言語を扱う際には、大文字と小文字の区別は特に意識しましょう!

3.ステップの引継ぎ

前述の1.で列のマージを行ったエディタ内には、下の画像のように2つの適用したステップがあるものとして以降の解説を行います

上の画像の2つ目のステップ「挿入された結合列」の数式バーの表示には、「ソース」という名前があるのが確認できます

では、3つ目のステップとして、既存の列を削除したらどうなるでしょうか?

3つ目のステップの中には、2つ目のステップの名前「挿入された結合列」が含まれています

つまり、各適用したステップは、前のステップ名を通じ、基本的には<一つ前ステップの内容>を自動的に引き継いでいきます

4.参照ステップ作成

前述の3.ではエディタ内で<一つ前のステップの内容>を自動的に引き継ぐというPower Queryの特徴について解説しました

但し、これはあくまでも”基本的には”、”自動的には”という話しであり、前ステップをスキップして参照することもできます

では、この「参照のスキップ」を利用した、新たな数字の集計方法を紹介します

エディタ内の話しに戻り、一つステップを追加します

上の画像の「注文金額」の列から全体平均を算出します

すると下の画像のように、「削除された列」ステップを引き継いだ「計算された平均」というステップが追加されます

この「計算された平均」で算出した値「53609」を、他のステップで参照して活用できるようにします

この、他のステップで参照できるようにしたステップを、以降「参照ステップ」と呼ぶことにします

では、この「計算された平均」ステップを「参照ステップ」として確定するための処理を数式バーで行います

下のGIF画像のように「関数のマーク」を左クリックすると、新たなステップが追加されます

「カスタム1」というステップが新たに追加されましたが、このステップはある種、ダミーのステップです

更にこのステップから、下の図のように「前のステップ」をスキップして「削除された列」ステップを参照します

前のステップをスキップして参照するには、下のGIF画像のように数式バーでステップ名「削除された列」を入力し、Enterを押します

すると、エディタの画面が「削除された列ステップ」の内容になります

エディタ画面からは下の画像の「平均金額」は消えましたが、これで「平均金額」を他の画面で参照して活用できるようになりました

今回は、「平均金額」を参照ステップとして活用したカスタム列を作成してみます

作成する内容は「個々の注文金額-平均」です

注文金額については、カスタム列作成の右側から挿入できます

「平均」については、前述のステップ名「計算された平均」を参照します

つまり、「計算された平均」ステップで算出された「53609」が「注文金額」から差し引かれます

カスタム列を作成した後のエディタ画面は、次の画像のようになります

このカスタム列を作成するまでに、ステップを新たに追加したり、ステップをスキップしたりしたので、図で整理して今回の処理を振り返ります

ⅰ)カスタム1の列をダミーとして追加

この処理で追加されたステップにより、「計算された平均」ステップを残したまま、エディタ画面を「削除された列」ステップの画面に戻せています

つまり、「削除された列」➡「計算された平均」というステップの順序が、「計算された平均」➡「削除された列」という順序に変えることができました

ⅱ)カスタム列の作成

ⅰ)で変えた順序を活かして、個々の注文金額マイナス全体の平均を計算します

<まとめ>

今回は「M言語に慣れる」の1回目として、M言語の特徴を3つに絞って解説しました

1.コードのカラー

2.大文字と小文字の区別

3.ステップの引継ぎ

 上の3つを意識してPower Queryを活用してもらえると、M言語が必ずしも難解なわけではないことが、分かって頂けると思います

そして、3つの特徴の後には3.ステップの引継ぎを応用した「参照ステップ」について解説しました

こちらでは、ステップの順序を入れ替えて「ステップで算出した値」を有効活用できるようにしました

ⅰ)一旦、注文金額の平均値を計算➡参照ステップ

ⅱ)カスタム列・作成画面で「各行の注文金額-平均値/参照ステップ」を計算

この上のⅰ)ⅱ)の処理はエクセルシート上で例えるなら、以下の画像のような処理です

 データが存在するシート/画像左とは別のシート/画像右で平均値を計算しておき、元のデータが存在するシートで注文金額から平均値を引いています

ですので、今回使用した参照ステップの値はエクセルシートでの操作でいえば別シートでの計算です 

 M言語を使用していけば上の事例と同じ様に、エクセルシート上での細かい操作に近いことが一括で行えることが理解できてきたと思います

 では、次回からはM言語を有効活用してPower Queryの魅力を新たに引き出す方法について、もっと具体的に解説していきます

M言語の記事一覧については、ここから見ることができます

記事を最後までお読み下さり、誠にありがとうございました

参考までに解説に使用したエクセルファイルを添付します

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

にほんブログ村

M言語に慣れる_2回目~カスタム列~

【M言語は難しくない!】今回は、前回紹介したM言語の特徴を意識しながら、実際に「M関数」を使用したカスタム列を作成してみましょう!

では、前回紹介したM言語の特徴を振り返ってみましょう!

ⅰ.コードのカラー

赤は「文字列」、青はeachなどの「予約語」、緑は「自動記録された値」です

ⅱ.大文字と小文字の区別

M言語は、大文字と小文字の違いを認識します

ⅲ.ステップの引継ぎ

適用したステップは「ステップ名」を通じて、前ステップの内容を引き継ぎます

今回はこの3つの特徴に加え、次の4つ目の特徴も意識しましょう!!

ⅳ.データ形式

 エクセルは表計算ソフトであって、Accessのようなデータベースソフトではありません

 よって、過去の記事で紹介したように、Power Queryではデータ形式を意識した「データの構造化」がエディタを開く際に、自動的に行われます

 当然、Power Queryエディタ(以降、エディタ)内で列を追加する時にも「データ形式」を意識する必要があります

 では、今回意識すべきPower Queryの4つの特徴を確認したところで、M関数を使用した「カスタム列」の追加についての解説を始めたいと思います

今回、解説に使用するデータは次の画像のデータです

ファイルデータはこちらになります

アイコン

Mコード2回目_練習 21.42 KB 31 downloads

...

こちらのデータから、次の列をM関数を使用して作成します

A.「姓」と「入会年」を組み合わせたログインID

「入会年」は「整数」のため、M関数を使用して「文字列」に変換します

B.「入会年」「入会月」と「1日」を組み合わせた入会日

M関数にて、3つの整数から日付を作成します

エクセル関数のDATE関数と同じ要領です

なお、上のABの列を作成する前に、ウォーミングアップとこれまでの復習を兼ねて、簡単なカスタム列を作成してみましょう

1.全ての行が「1」の列

まず、データテーブル上で右クリックし、下の画像の黄色の箇所からエディタを開きます

エディタが開いたら、「列の追加」タブから「カスタム列」をクリックします

次に開いた「カスタム列」の画面で「1」を指定します

上の画像のように「1」を指定したら、右下のOKボタンをクリックします

すると全ての行が「1」になっている新たな列「1」が追加されます

では、エディタ画面上の数式バーの中味を詳細に確認してみましょう

まず、()の中の左から1番目の「変更された型」についてです

①ステップ名

こちらは、前ステップの「変更された型」を引き継いでいます

②新しい列名

文字列”1”としてで表示されています

③システム予約語

「each」はシステム予約語なので、で表示されています

④自動記録された値

最後の”1”は自動記録された値なので、で表示されています

①から④まで数式の中味を確認しました

ここで、数式バーに「1」と入力してみましょう

すると、③のeachの意味合いがよく分かると思います

数式バーに1と入力すると「1」が1つだけ表示されます

ですので、「each」というシステム予約語により「それぞれ~」「各々~」のような意味になるのが御理解頂けたと思います

2.ログインID の作成/文字列への変換

2.ログインIDの作成では、記事の冒頭のA.で前述したように「姓」と「入会年」を組み合わせ、ログインIDの列を作成します

なお、「姓」と「入会年」を組み合わせる際には「」を使います

では、1.でも行ったように「カスタム列」の画面から作成を行います

「姓」と「入会年」は、ともに既存の列なので、画面右から挿入します

次の画像の内容で式を記入したら、画面右下のOKボタンをクリックします

ちなみに、上記の内容であれば、画面左下にエラーメッセージは出ません

ところが、出力された新たな列は、全てエラーになります

このエラーが何故発生するかというと、記事の冒頭ので紹介したように、M言語では「データ形式」を意識する必要があるからです

「姓」は「文字列型式」ですが、実は「入会年」は「整数」です

ですので、「入会年」は文字列に変換する必要があります

ここで、M関数を使用します

エクセル関数では次の画像の画面から、該当の関数を探すことができます

M関数でも同じような画面があります

上の画像の「Power Queryの式についての詳細」をクリックすると、下の画像のMicrosoft社の画面に遷移します

上の画像の下に「カテゴリ別の関数」とありますが、こちらの中に「テキスト関数」という関数があります

更に、こちらの「テキスト関数」の中を下にスクロールすると、「Text.From」という関数があります

こちらの関数で「入会年」を「整数」から「文字列」に変換できます

このText関数では、変換対象をText.Form()の”()“の中に入れます

なお、

関数は下の画像の数式バーに直接入力してしまいましょう!

Text関数を使用する際、「Text.Form」の中のTとFのいずれかを小文字にするとエラーになりますので、注意して入力しましょう!

3.入会日の作成/整数から日付作成

こちらは、記事の冒頭のB.にて紹介した、下の画像の「#date」関数を使用した内容になります

こちらのM関数は、#date()()の中に、整数の「年」「月」「日」を設定します

下の画像のエクセル関数の「DATE関数」と同じ要領になりますので、こちらのM関数は取り組みやすいと思います

但し、2.で扱ったText.From関数と違い、この関数は小文字dで始まります

この点については、エラーにならないように注意して入力しましょう!

作成する画面についてですが、こちらは、カスタム列の作成画面で作成します

()の中の最後の「日」は手動で「1」を入力します

新たに作成された列は、次の画像のように出力されます

<まとめ>

今回はカスタム関数の作成画面にて、3つの列を作成しました

1.全て1の列

2.文字列/姓と整数/入会年を組み合わせたログインID

3.整数/「年」「月」「日」を組み合わせた入会日

 1.では、前回、M言語の特徴として紹介した「each」などのコードカラー()についても解説を行いました

 ちなみに、「each」はM言語を扱う際には頻繁に出てきますので、感覚として慣れて置いた方が良いです

 2.では、入会年をM関数のText.Fromを使用して「整数」から「文字列」に変換しました

 記事の冒頭でも紹介したように、M言語では「データ形式」を意識する必要があります

 M言語を扱う際には、今回のようにデータ形式を変換する場面が出てくるはずですので、Text.From関数はぜひ習得しておきましょう!

最後は、#date関数を使用して「入会日」を作成しました

こちらの関数はText.From関数と違い、小文字から始まります

 前回もM言語の特徴として解説しましたが、M言語では大文字と小文字を区別する必要があります

 筆者も何度か、この大文字と小文字の区別を間違えてエラーを出しましたが、意外とエラー原因が些細すぎて原因に気づかないものです

この「大文字と小文字の区別」については特に注意しましょう!

記事を最後まで読んで下さり、誠にありがとうございました

参考までに今回使用したファイル(完成版)を添付します

 エディタ画面の「適用したステップ」の表示が、解説に使用した内容とは若干相違がありますので、この点はご了承をお願いします

アイコン

Mコード2回目_完成 24.89 KB 13 downloads

...

では、次回もよろしくお願いします

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

にほんブログ村

ゲームではじめるマクロ/VBA_1

 今回からはじめるこのコーナーでは、エクセルマクロ/VBAにて作成したゲームを楽しみながら、マクロ/VBAの学習を無理なく、そして、挫折することなく学習していけるように工夫していきます

エクセルマクロ/VBAを覚えると、相当な業務削減の効果が見込めます!

「でも、マクロ/VBAは難しそう・・・」

もし、そんな風にマクロ/VBAの学習をはじめることを躊躇している方は、ぜひこのゲームで楽しみながら「慣れ」を作ってみて下さい

 ゲームにはペンギンとオバケが登場します。ペンギンが迷路の中で壁にぶつからないように、事前に用意してあるコードを選択してオバケをつかまえられるようにしてください!

*めいろゲームは3部構成です

ところで、

何故、「エクセルの話にゲームが出てくるのか?」

その事についてはポイントが2点あります

1点目は、マクロ/VBAはコンピューターとのコミュニケーション言語である点です

2点目は、「英語映画の英語字幕」による学習についてです

まず1点目のマクロ/VBAが言語である点についてです

マクロ/VBAではコンピューターにコードで指示を出します

しかも、コンピューターに指示が的確に伝わるようにしなくてはなりません

つまり、他のプログラミング言語も同様ですが、エクセルマクロ/も言語なのです

しかも、英語でコードを書きます

ですので、英語学習の要領でのマクロ/VBAの学習方法があってもいいのです

次に2点目、「英語映画の英語字幕」による学習についてです

これは英語の映画を、英語の字幕のまま見ながら学習する方法です

こちらは、映画の登場人物とスクリーンを介して疑似対話を行いながら、タイムリーに表示される英語字幕を通じて生きた英語が学べます

このゲームでは映画の字幕の代わりにコードが表示されます

通常のマクロだと一瞬で終了しますが、このゲームは違います

登場人物の動きに合わせて、コードを表示できます

ゲーム内の登場人物の動きに合わせてコードが表示されると、その意味合いを体感しやすくなります

今回、ゲーム内で表示されるコードについては、学習を始めやすい2つのコードに絞ってあります

①セルの選択/カーソルをセルに置く、②1つカーソルを移動する

*めいろ2とめいろ3ではコードは表示されません

尚、

この記事では2つファイルが添付されています

ゲームの他に演習用ファイルがあります

演習用ファイルには、ゲームで出てくるコードが2つ書かれています

ぜひ、ご自身でコードを修正してみたりして、コードの動きを体感してみてください!

アイコン

めいろゲーム_1 200.40 KB 5 downloads

...

アイコン

演習_1 132.96 KB 2 downloads

...

ゲームを楽しむ

前述の「めいろゲーム_1」をダウンロードしたら「めいろ1」のシートを選択してください

このシートではまず、コードをご自身で選択して設定します

選べるのは、ペンギンを右に1つ進めるか、下に1つ進めるか、だけです

コードを選択したら、下の画像の一番左にある「スタート」ボタンを押して下さい

もし、やり直す場合には、上の画像の真ん中にある「やりなおす」ボタンを押して下さい

 ペンギンが動く際には、前述のように①セルの選択/カーソルをセルに置く、②1つカーソルを移動するのコードが表示されます

 もし、ペンギンがオバケをつかまえるのに成功したら、カーソルの動きのみですが、全コードがまとめて表示されます

もし、コードを表示せずに純粋にゲームを楽しみたい場合には、下の画像の箇所のチェックを外すようにしてください

表示される2つのコードの概要解説

コード内に出てくる「.」については、「の」と置き換えて理解してみてください

*ちなみに今の段階では、コードについては完全に覚える必要はありません

①セルの選択/カーソルをセルに置く

ActiveSheet.Range(“B4”).Select

こちらのコードは、↓のような感じで「意訳」してみてください

カーソルがあるSheet内 範囲/Range 選択/Select

 ②1つカーソルを移動する

Selection.Offset(1,0).Select

こちらのコードは、↓のような感じで「意訳」してみてください

カーソルが選択してあるセル範囲/Selection 別位置 選択/Select

Offset(1,0)の場合は1つ下に、Offset(0,1)の場合には1つ右に移動します

演習

演習用ファイルで実際にご自身でコードを動かして、コードの動きを体感して見ましょう!

開発タブの設定

まだ開発タブを設定していない方は、まずは動画か、演習ファイルの解説シートを見てタブを設定してください

*開発タブの設定用動画(ファイルタブ➡オプション➡リボンのユーザー設定)

https://youtu.be/bjTGdVs77w8

コードを動かしてみる

演習ファイルの演習1シートにカーソルを置いてください

次の画像のマクロボタンをクリックすると「Sample1」という文字が見えるはずです

Sample1の文字は次ような感じで見えているはずです

Sample1が選択されているのを確認したら、右にある実行ボタンを押して下さい

すると、上の画像のようにB5セルが「選択されている/カーソルが置かれている」はずです

マクロを実行してみた後は、コードの中味を実際に見て見ましょう!

コードの中味の確認

では、下の画像の「編集」をクリックして、コードの中味を見て見ましょう!

「編集」をクリックすると下の画像の内容が見えます

ここからはご自身で修正してみて、コードの動きを試してみて下さい

例えば、上の画像だと「Selection.Offset(1,0).Select」と入力されています

ですのでコードを実行すると、上のコードでB4セルを選択した後に、1つ下のセルに移動しています

この「Selection.Offset(1,0).Select」「Selection.Offset(0,1).Select」と修正してみてください

すると、1つ目のコードで選択されたB4セルの一つ右隣りのC4セルが選択されているはずです

ちなみに、

下の画像のようにOFFSET関数は、通常の関数でもあります

=OFFSET(セル範囲,行指定,列指定)

この関数では、引数1番目で指定したセル範囲から行方向、もしくは列方向に移動した箇所を指定できますので、興味があれば試してみてください

なお、通常のエクセルシートに戻る際には、下の黄色につけた印の箇所をクリックしてください

<まとめ>

今回は、ゲームと演習ファイルを通じてVBAコードを2つ体験して頂きました

 たった2つのコードですが、マクロ/VBAの本質にこれから迫っていく「取っ掛かり」にはとてもいい内容だと思います

記事の冒頭でも述べましたが、マクロ/VBAはコンピューターとのコミュニケーションであり、的確に指示をコンピューターに伝えなくてはなりません

この2つのコードには「どこの/どのセル範囲」を操作対象にして、その操作範囲の「何を」「どうするのか」というコンピューターへの「的確な指示」に必要な要素が端的に詰まっています

この「的確な指示」とは具体的にはどういうことか?ということについては、また別な記事で解説したいと思います

まずは、コードを動かしてみる事、そしてマクロ/VBAを楽しむことが重要です!

ちなみに、サンプルファイルは「.xlsm」になっている点にも注目して下さい

この拡張子「xlsm」のように、マクロ/VBAの場合には通常のエクセル作業とは違ったルールがあったりしますが、それは後々、解説していきます

!ストリート・アカデミーにてゲームを使ったマクロ/レッスンも行っています ➡詳細

にほんブログ村

ゲームではじめるマクロ/VBA~繰返処理

前回は、めいろゲーム1を試してみて頂いた上で、カーソルを動かす簡単なマクロを試して頂きました

今回、まずはエクセルマクロ/VBA醍醐味である「繰り返し処理/ループ」を試してみて頂きます

この「繰り返し処理」はシステム全般醍醐味といっても過言ではありません

この繰り返し処理のおかげで、人間は退屈な処理から解放されています

ぜひ、めいろゲーム2を通じて、この繰り返し処理を体感してみてください

この繰り返し処理は通常のマクロ/だと一瞬で終わってしまい、結果でしか、実感する暇がありません
このゲーム形式だと、登場人物に合わせて表示されるコードにて、はっきりと繰り返し処理が行われている様子を実感できるのが大きなメリットです!

アイコン

めいろ2 182.98 KB 2 downloads

...

めいろゲーム2の遊び方

迷路ゲーム2のシートにて、L列の下にある「やり方」ボタンを押すとゲームのコード設定の方法が表示されます

このめいろ2では、めいろ1と違い「2つのコード」とそれぞれの処理回数の指定だけで「ペンギン」が「オバケ」を壁にぶつからないでつかまえることができるようにします

めいろ1とめいろ2は「めいろの内容」と「ペンギンとオバケの配置」は全く同じです

ところが、繰り返し処理を行うめいろ2の場合は僅か2行のコードを設定するだけで済みます

この2行へのコードの集約がまさに「繰り返し」処理の醍醐味です

ペンギンがオバケをつかまえた後は、カーソルの動きの動き(ペンギンの移動)について実行された「全てのコード」が表示されます

上の画像を見て「なんのことかさっぱり??」と思われたかもしれませんが、

次の章で、中身について解説していきます

繰り返し処理の中味を見る

繰り返し処理には様々な種類がありますが、今回はFor ~Next文に絞って解説を行います

上の画像の赤字部分が、構文になります(➡後は可変になります)

ForとNextの間に書いてあるコードを繰り返し処理をさせます

「繰り返し処理を何回行うか?」

の指定については、変数という概念を使います

今回はこの変数については「魔法の呪文」と割り切って話を進めさせて頂きます

上の画像の「Dim myCount As Long」とは、「myCount」を「整数の型式」にて変数として使うという意味になります

この変数の「myCount」は中学の数学で使う「X」のような意味で考えて頂いて結構です

数学の問題ではXに様々な値が入り、Yの値が変わっていきます

今回の繰返し処理のコードでは「As Long」として指定することにより、「myCount」に整数だけが入るというルールになります

ちなみに、myCountという名前は「i」などの他の名前にして頂いても結構です

さて、

この「myCount」はFor~Next文の中で、繰り返し処理回数をコントロールするのに重要な役割を果たします

「For myCount = 1」と指定することで、myCountは整数の1から始まり、

その後に「to 4」と指定することで、myCountを1つずつ増やして4になるまでFor ~ Nextの中に入るコードを繰り返し処理するという意味になります

実際にコードをいじってみよう

今回は演習2というサンプルファイルを添付します

アイコン

演習2 133.64 KB 1 downloads

...

サンプルファイルの中には3つのマクロが入っています

このうち「Sample1」は前回に使用したものです

今回は、Sample2とSample3を使用します

まず、Sample2です

Sample2を実行すると「メッセージ」が表示されます

では、「編集」ボタンを押してSample2の中味をみてみましょう!

本当に簡単な一文が書かれているはずです

MsgBox~この一文だけで「メッセージ」を表示させることができます

では、Sample3を実行してみましょう!

今度は3回メッセージが出てきました

Sample2と同じ様に中味をみてみましょう

めいろ2と同じ様な繰り返し処理のコードが書かれています

今回の場合には、Toの後が4からに変わっています

ですので、メッセージを出すコードが3回繰り返し実行されています

繰返し処理を体感する意味で、この「」を他の数字に変更してみましょう!

変更した上で実行すると、繰り返し処理を体感しやすいはずです

<まとめ>

いかがでしたでしょうか?

今回は、繰り返し処理について体験して頂きました

記事の冒頭でも書きましたが、この繰り返し処理はシステムの醍醐味です

この繰り返し処理を使いこなせると、エクセルを使う景色が変わってきます

今回の内容は、繰り返し処理の中でも一番、基礎的な内容です

この基礎形を理解しておくと、様々な形で応用が効きますのでぜひマスターしておきましょう!

!ストリート・アカデミーにてゲームを使ったマクロ/レッスンも行っています➡ 詳細


にほんブログ村

ゲームではじめるマクロ/VBA~条件分岐

前々回に試して頂いた「めいろ1」ですが、「X」の壁にぶつからないで「オバケ」をつかまえられたでしょうか?

もし、壁にぶつかると条件分岐処理によりペンギンは止まります

今回は、この条件分岐の処理を体験して頂きます

アイコン

めいろゲーム_1 200.40 KB 5 downloads

...

アイコン

演習2 133.64 KB 1 downloads

...

通常のIF関数の復習

今回は、前回使用した演習2のファイルからはじめます

このファイルのK8セルには上記のようなIF関数が埋め込まれています

ですので、「迷路の一の二/C4セル」に”X”が入力されていると”終了”の文字を出力します

このIF関数と同じ仕組みが、記事の冒頭の「カベにぶつかったとき」のマクロの処理に組み込まれています

壁にペンギンをぶつける

では、今回はめいろ1にて敢えてペンギンを壁/Xにぶつけてみましょう!

上の画像のように、コードの1行目で下に進むようにすれば、スタートボタンを押すのとほぼ同時に壁にぶつかります

上の画像では、数式バーに「X」と出ています

つまり、これはカーソルがある箇所にXが入力されているということになります

カーソルがある箇所のセルの中味が「X」の場合には、処理が止まるようにマクロが組み込まれているわけですが、コードにすると次のようになります

2行目の「End」は「マクロ処理」の終了という意味です

この「End」というコードが、1行目のIfからはじまるコードと3行目のEnd Ifに囲まれています

1行目のコードでは、IfとThenの間に「Selection.Value」というコードが入力されています

「Selection.Value」については、今の段階では次のように解釈しておいてください

「選択/Selectionした箇所 中味/Value」

つまり、「もし/If、カーソルが選択されているセルの中味が”X”の場合には」という条件式が「If Then」の の箇所に書いてあります

そして、条件に合う場合には、次の行から書いてあるコードを実行します

3行のEnd Ifは、条件に合う場合に実行されるコードの終点を示しています

条件分岐処理を体験する

では演習2のファイルに戻ります

こちらのファイルにあるSample3のマクロを開いてみて下さい

今回は、下の画像の「Visual Basic」のところをクリックしてみましょう!

すると「Sub Sample3()」の箇所がみえるはずです

こちらのマクロは、実行するとカウンターとして設定した「myCount」が1から3まで増える間にメッセージボックスを3回繰り返し表示します

では、今回はFor ~ Next文の中に次の3行を入力してみましょう

こちらのコードは「もし/If、1からはじまるmyCountが1の場合には、マクロを終了する」という意味になります

ですので、このIf文をMsgBox~の上に書くと、メッセージボックスは1度も表示されません

<まとめ>

今回は、条件分岐の処理を体験して頂きました

前回の繰返し処理もそうですが、この条件分岐処理は操作を記録して再生する「記録マクロ」では実行できません

ですので、繰り返し処理と同様にシステムの醍醐味といってもいい処理だと思います

ぜひ、手を動かして体感してみてください

尚、記事の中で無造作にマクロという言葉を使用していますが、こちらはVBAコードを集めたセット/一式ぐらいに思っていただければ結構です

ではまた次回もよろしくお願いします


にほんブログ村