コレまでの経験上、事務の現場に於ける様々なExcelの管理帳票類は、その殆どが低レベルな関数と無駄に使用してるセル結合などで、実際には初級レベルのテクニックのみで作られてるようなちょっと問題だらけのファイルが山程あります。個人で勝手に使ってる分には構わないですが、それを部署全体の共有ファイルとして、またその書き方まで強制されると、「出来る人間にとっては非常に不快でストレス」なだけでなく、RPAなどで自動化する上での「多大な障害」になっています（場合によっては優秀な人間が退職する原因にもなります）。
今回、上級と銘打っていますが、本来は事務職１年目からでも「必須」の項目です。自動化まで踏まえたExcelファイルの作成が出来ないと、今後事務職はコロナ禍後に自動化・省力化の推進により、その職の枠自体が半減し、行き場を無くすことになりますので、危機感持っていないと野垂れ死にも夢じゃありません。
目次
今回使用するファイル類
研修用資料のダウンロード
- 総務省統一ルールExcelファイルサンプル
- テーブル・Power Query Excelサンプル
- Microsoft365専用関数のExcelファイルサンプル
- VBAの入ったExcelサンプルファイル
- ユーザ定義関数をアドイン化したファイル
- 離島気象データaccdb - DB接続用の素材（Accessデータベース）
- 練習問題用ファイル
- 練習問題の解答
- PowerPoint形式のスライド資料
トレーニング動画
旧研修用動画
全部で20項目の動画を用意しました。動画は再生リストになっているので、連続で再生されます。順不同
現在作成中動画
全部で7コマの構成を予定しています（現在、5コマ完成済み）
トレーニング項目
データの整備管理編
経理や人事総務に限らず、事務全般の現場で作られてる様々なExcelファイルを見ているとわかるのですが、非常に問題のある作りのファイルが多いです。まず、Excelで管理台帳なり帳票を作る上での数多の問題点を列挙しつつ、どのような形式で作成すべきなのか？という観点を学ぶ必要があります。特にExcel方眼紙問題やExcelレガシー問題、シャドーIT問題は、企業運営上の多大なリスクになっており、以下のような問題点に抵触してる企業は直ちに是正すべきです(内部統制上もNG項目です）。
Excelデータ管理上の問題点
管理上の問題点ですが、大きく2つ「データの構造上の問題」「Excelで管理する事の是非」に分けられます。内部統制上の問題となるのは後者で、前者は自動化やデータ管理をする上での問題となります。
データ構造上の問題
こちらの問題は、ユーザ側のITリテラシー（Excelスキル）があまりにも低いがために引き起こされてる問題です。
この件に関しては既に総務省からも統一ルールとして公開されており、いかにコレに準拠していないファイルが多いのか経験のある人も多いでしょう。そうしたファイルはRPA自動化の大きな障害になるだけでなく、Excelを扱える上級者からしたらストレスそのもの。主な事例は以下の通り。
- 1セル1データは鉄則。1つのセルに複数のデータを備考のように詰め込んでるようなケースはNGです。
- 数値のセルデータに▲などの記号類や円マーク、単位名などを含めない（セルの書式設定でやるべき事です）
- セルの結合を行わない。人間にとって見栄えが良くても、プログラムが扱う上では最悪です。
- スペースで均等割り付け風にしない。スペースの存在は邪魔以外の何もでもありません。
- 複数のレコードで同じような項目が列挙されてるケースで1つ目だけ項目名を表示して、以下を省略するような表記を行わない。
- 1つの表で実現できる事を、特定のジャンルでわざわざ分割表記しない（都道府県別に別々に表を作る事になんの意味もありません）。都道府県という列を用意してジャンルを記述しておけば済む話です。
管理する台帳と会議で使う為の見栄えの資料とを混同してる最たる事例です。これに加え現場でよく見かける悪い事例としては
- 生データではなくいきなり集計表形式で表を作らない。集計表というものは生データから加工して作るものです（ピボットテーブルなども生データから加工して作る事例の１つ）。生データ→集計表は簡単でも、集計表→生データは非常に面倒です。
- 同じ列なのに、数値のものと文字列のものが混在してる。数値の列は数値のみ、文字列のものは文字列のみにすべき。
- 数値に於いて全角文字は使わない。
- 日付の形式をyyyy/mm/ddの形式を使用し、21.10.5といったような入力をしない。
- 列の非表示、フィルタを掛けっぱなしで運用するような真似をしない。
- 月別合計列などの小計を列と列の間、行と行の間に設けない。邪魔です。合計列は一番右、合計行は一番下は鉄則です。
- 100列も200列もあるような巨大で横長なデータは作らない。行数はいくらあっても構わないけれど、列数はきちんと項目を整理して、生データの形式にするべき。目的の値を探す時間が無駄以外の何物でもありません。
- 無闇矢鱈に条件判定や複数の入れ子にした関数を駆使したような表は作らない。メンテナンス性最悪です。
- 一方でSUM関数1つで済むようなものを、A1+B1+C1+・・・といった悪い数式で合計したりしない。
- テーブル機能を活用し、vlookupなどで固定もせずに作ってるような表は作らない。
- 追跡不可能なほどに複数のExcelブックやシート間リンクといった表は作らない。
- 1列のステータス列だけで表現できる事を、複数の列を使って表現するような表は作らない。
- 備考欄にすべき内容を、セルのコメント機能を使って表現しない。
- 部門によって同じような内容なのに、表の形式・入力ルールが統一されていない（列の順番すら違うケースも多数有り）表は作成しない。
- 縦横の1枚の表以外に余計な場所に入力欄やら、マクロのボタンなどは設置しない。ボタンはリボンで作るべきです。
- 表のタイトル列に環境依存文字であったり、特殊記号、数字から始まるタイトル、括弧などを含めたタイトルは使用しない（命名規則を勉強しましょう）
- IDなどは必ず一番左端に配置し、また重複しない連番の列も用意すべき。
- 可能であれば、マスタとサブマスタは分離管理し、データベースはAccessで管理するか？Power Queryなどを活用して連結して別シートで管理をするようにする。
※早い話が縦横1枚の表で作れば良いだけ。余計な事をするなという事です。余計なデザインや脚色をしたいのであれば、それを行わないマスターデータを用意し、別シートでそこから集計する形で好き勝手にやってください。人間本位から見たデータではなく、コンピュータに合わせたデータを作成・運用出来なければ、RPAで自動化など出来ないと思ってください。
Excelで管理することの是非
これは前述のExcelレガシー問題やシャドーIT問題に加えて、情報漏えいの観点からも基本的にはNG項目に該当するものになります。つまり、「そもそもExcelでやるべき事じゃない」という事。管理対象100名クラスの中小企業で行う分には影響も小さいのと、共同で作業するメンバーも少人数である事から、大した問題にはなりませんが、その規模が100名を超えるような規模になればなるほど、それそのものが「業務の煩雑化と級数的な負荷の増大」に繋がり、最終的にはRPA等での自動化に於いても大きな障害になります。
※中には意味不明なまま、なぜか記入する、なぜか作成してるExcelファイルがあり、無くしても全く問題ないなんて笑えない事例もあります。
この問題にぶつかる原因ですが
- 企業としてきちんと提供すべきITリソースを提供していない
- リソースはあるが、セキュリティ面にばかり目が向き、業務効率度外視で思考停止な形で使用を禁じてる
- 情報システム部門が責任分界点と称して線引し責任回避、現場業務にタッチしない
殆どがユーザ側の問題ではなく「企業側・部門側」の問題点です。その内容を深堀りすると、情報システム部門の力量不足や人材不足による所が大きく、企業の事業活動の足を思いっきり引っ張ってる事例が多々見受けられます。身近な事例だとリスクヘッジ=責任回避だと勘違いしてるケースも多々ありました。
これに対する解決策はユーザ側には無く、企業側で
- 十分なITリソースの提供を行う（CRMの提供やタスク管理システム、RPAの導入管理、基幹業務システムの改修など）
- その為の十分な予算割（特に年収400万円程度でIT人材募集など時代錯誤な要望を出しても、そんな人は来ません）
- セキュリティと業務効率のさじ加減及び、その深い管理を行える人材の養成（現場に寄り添わない情報部門は排除）
を行う以外にありません。結果これらが不十分な結果、事務現場でExcelにて管理台帳なサブシステムを作ったり、結果、情報漏えいやレガシー問題・方眼紙問題を引き起こし、無駄な残業代支払いや業務負荷増大につながっています。ユーザ側は強く経営層や情シス部門へ要求すべきです。
改善とテクニック
前述のExcelで管理することの是非は組織の問題ですが、データ構造上の問題はユーザ個人のスキルの問題です。よって後者はいくらでも改善可能ですし、総務省統一ルール他のルールの鉄守は難しい事ではありません。「やるべき事をやり、余計な事をしない」「他人が使う事を考慮して構築する」「自分のスキルを基準にして作成しない」これだけです。
以下の図の表はこれらを全て準拠したもので
- ID列は左端に揃える（ソートは別にいつでもできるので問題ない）
- 全体をテーブル化しておく
- 作業名など同じ項目が続いても省略したりしない
- 日付はyyyy/mm/ddで統一しておく
- 数値は半角、マイナス記号を付けたい場合は書式設定のカスタムにて
- 項目名はリスト式にし一列で表現（4項目あるからといって4つもヘッダーに並べたりしない）
- 複数の割当項目がある場合は1つのセルに含めずに、ヘッダーで分ける（配賦先列）
- 付替可否列は関数を使ってるが、ifの入れ子で何度も判定せず、=IF(COUNTIF(H4:J4,1040)=0,"不可","可能")と一発で判定させる
- その他の特記事項はコメントを使わず、備考欄に集約する
- 項目名の選択リストなどは別のシートに切り分けておく（同じシートの別のエリアに配置したりしない）
- セルの結合などの見栄えの為の余計なことは行わない。
たったこれだけの事です。こうすることで第三者が集計や自動化、また分析をする際に余計な手間暇を掛ける必要も無く、またユーザは自分で脚色したい場合は、別の作業用シートで行えば済む話です。
図：問題のない表の構築事例
Excelが遅いと称する人たちへ
これまで、Excelが遅いと称する人たちのシートを山のように見てきたりヒアリングをした内容の多くがExcelとは無関係の内容で遅かったり、自ら遅くするような真似をしてるケースが非常に多く見受けられました。その代表例は特に在宅ワークになってから顕著です。とりわけ、自宅から会社オンプレのNASやGoogle Drive, OneDrive, Boxにアクセスするにあたってといったケースが目立ちます。
- 在宅ワークで自宅のAPが2GHz帯域。周囲のAPと干渉しまくりでそもそもネットワークが不安定でVPN越しにファイルを開いていた
- VPNはそもそも通信が遅い。にもかかわらず、在宅ワークで遅い遅いと称するのは基本的なリテラシー不足。
- 特に夏、在宅ワークでルーターがオーバーヒートしてるにもかかわらず、使い続けてる。遅くなって当たり前。
- そもそも、自宅の通信環境が劣悪。4Gがほとんど入らないのにテザリングや、公称値だけ高いだけで実際にはほとんどスピードの出ていない集合住宅のVDSL。遅くて当たり前です。
- Google Driveアプリ等のローカルキャッシュ系のアプリを利用して、キャッシュが山のように積み上がり圧迫してるケース。
- 極めつけはアホみたいにバカでかいスマフォの画像をリサイズリサンプルもせずに張り付けたり、山のようにシェープなどのオブジェクトを多用したり
- 複雑な入れ子の関数を何万行も利用したりといった致命的にダメな利用方法をしてるケース。PowerQueryでやるべき事です。関数多用する技で悦に浸ってる場合じゃありません。
- 在宅ワークでシート間リンクが切れてるのに、参照しに行こうとするので当然オーバーヘッドが掛かってるケース
- 会社側の愚かな発想で貧弱なVPN越しでなければ、Boxなどのウェブサービスにアクセスさせないといった結果、VPNがパンクしてるケース
これらはもはや今時の事務では論外です。リテラシーが低くて許される時代は20年前に終わっています。典型的ダメパターンですので、VPNパンクのケース以外はその習慣からまず直しましょう。
関数編
Office2016以降追加
主にこれまで「何で実装されてないの？」という関数の実装であったり、関数入れ子問題解消の為の関数の実装、文字連結がCONCANTINATE関数しか無いという具合の悪さを解消する為に新規に装備されてる関数が多数あります。そのうち、メジャーなものを取り上げています。
SWITCH関数
関数入れ子問題解消の為に生まれた関数で、値に対しての条件分岐を複数付けたい場合に簡単に構築できる関数です。VBAで言う所のSELECT CASEのようなもの。構文がシンプルで非常に分かりやすい。ただし、if関数のように「比較演算子を使った」判定は出来ない（絶対値での比較のみとなる）
構文は以下の通りです。
=SWITCH(C2,5,"A",4,"B",3,"C",2,"D",1,"E")
上記の例の場合、基準となる値（C2）が5～1の場合、それぞれに対応したA～Eの値を返すという条件分岐になっています。IF関数でのアホみたいな入れ子と違い、条件とそれに対応する値がわかりやすい。もちろん、比較演算子も使えるので、その場合は比較する順番に注意が必要。
図：ランクの値に応じてA～Eを返す
IFS関数
同じく、IF関数の入れ子複雑問題解消の為に生まれた関数で、基準となる値に対して一度に複数の条件判定を行い、値を返すという関数です。但し、swtich文よりも冗長な構文になり、またいわゆるfalseの場合といったものが無いので、ほぼ動作はswtich関数と同じ。
構文は以下の通りです。
=IFS(C2=5,"A",C2=4,"B",C2=3,"C",C2=2,"D",C2=1,"E")
基準となる値(C2)がどんな値なのか？を複数記述する必要があるため、比較的小規模な条件判定で使うと良いのではないかと。
MAXIFS関数
Countifsやsumifsがあるのに、長い間その存在を軽んじられていた関数がようやく装備されたのが、このmaxifs関数。条件付きの最大値を表の中からピックアップする関数となります。使い方もsumifsと同じ引数のとり方になるので、難しくは有りません。
構文は以下の通りです。
=MAXIFS(検索範囲,条件範囲,条件値)
検索範囲の中から最大値を探しますが、その為の条件の値が入ってる条件範囲を指定し、その中で条件値に合致するものを引っ張ってきます。複数条件を加える事が可能なので、これまでのような複雑なロジックやユーザ定義関数で実現していたような苦労は必要なくなりました。
図：今まで実装放置されてたのが謎
MINIFS関数
Countifsやsumifsがあるのに、長い間その存在を軽んじられていた関数がようやく装備されたのが、このminifs関数。条件付きの最小値を表の中からピックアップする関数となります。使い方もsumifsと同じ引数のとり方になるので、難しくは有りません。
構文は以下の通りです。
=MINIFS(検索範囲,条件範囲,条件値)
検索範囲の中から最小値を探しますが、その為の条件の値が入ってる条件範囲を指定し、その中で条件値に合致するものを引っ張ってきます。複数条件を加える事が可能なので、これまでのような複雑なロジックやユーザ定義関数で実現していたような苦労は必要なくなりました。
図：今まで実装放置されてたのが謎その２
TEXTJOIN関数
これまで長い間、文字列を連結する関数はCONCANTINATE関数しかなく、またアンパサンド(&)を使った結合など、非常に冗長な上に使いにくい状態でしたが、Office2016以降については、今回のTEXTJOINやCONCATなどの文字列連結関数が充実した為、これらの問題がだいぶ解消しました。TEXTJOIN関数は文字と文字との間の引用符（例えばハイフンやカンマ）などを加えながら連結してくれる非常に便利な関数です。
構文は以下の通りです。
=TEXTJOIN("-",TRUE,D2,E2,F2)
最初に引用符を指定（今回はハイフン）、その後空白セルを無視するか否かを指定、その後は連結するセルを列挙するだけでOKです。非常に簡潔で簡単に構築できるので、CONCAT関数と使い分けしながら覚えておきたい関数です。
図：文字列連結がより簡便になった
スピル
これまでのExcelは基本的に関数で返せる値というものは、「単一の結果」のみであり、Googleスプレッドシートのように「複数の結果」を返すような仕組みを持っていませんでした。そのため、条件判定した結果該当するレコードを表示となると後述の「Power Query」等を使って取り出すのが定石で、関数でお手軽にという訳にはいかなかったのが、Office2019 Insider以降、Microsoft 365限定ですが、関数ではなく「複数の結果を返す機能」としてスピルというものが実装されました。
また、このスピルに準じて、Googleスプレッドシートで先行装備されてた関数をExcel側が逆に導入するという事も行われ、関数のみでかなり複雑なレコード抽出が実現出来るようになっています。
使い方そのものは非常に単純で、例えば = 範囲 * 範囲にて九九を作ったり、=範囲にて、そのエリアのデータをゴッソリ持ってくるといった事が可能になっています。スピルに準じた関数は後述の365専用関数を御覧ください。Excelの使い方が激変します。
図：縦横を掛けて九九を作るのも数式一発で可能
365専用関数編
買い切り版のExcel2019と違い、サブスクリプションのMicrosoft365のExcelは独自の関数を装備しています。いくつかは2019リリース時に2016時代の関数を買い切り版に反映されましたが、スピルおよびスピルに準じた関数は現在、Microsoft365を利用していないと使うことが出来ません。（パッケージ版では、2021/10/05発売のExcel2021版でも使えるらしいという話もありますが未確認）
これらの関数はGoogleスプレッドシートから逆導入された関数で、Excelの使い方やVBAでのコードの組み方などが激変するような関数となるため、これまでの古い見識のまま事務仕事をしていると指摘を受ける可能性があります。すでにGoogleスプレッドシートでは使えて当たり前の機能であるため、学習必須の項目となります。
※他にもまだ一部でのみ使えるようにリリースされてるLAMBDA関数や同じ関数の繰り返しを不要にするLET関数などが次々実装予定です。
FILTER関数
今回の365専用関数の中でも最も利用価値の高い関数で、基本はGoogleスプレッドシートのFILTER関数と同じ。指定した範囲に対して様々なタイプの抽出条件で判定した結果のレコードの塊を返してくれるスピル関数です。
この条件判定が基本は列に対して行い、その判定で別の関数を利用したり、また特定の列だけ結果として返したり、結果を別の関数に食わせて丸ごと計算させたりと、ダイナミックな計算が可能になっています。基本的な数式のスタイルは以下の通りです。
//条件つけて判定（ベーシックな使い方）
=FILTER(A2:E21,E2:E21>80)
//複数条件をつけて判定
=FILTER(A2:E21,(E2:E21>80)*(C2:C21="栃木"))
//結果のうち特定の列だけを表示する
=FILTER(FILTER(A2:E21,E2:E21>80),{1,1,0,0,1})
//E列が80より上の値の合計（別の関数に食わせる事例）
=SUM(FILTER(E:E,E:E>80))
//複数条件で尚且OR条件も併用する事例（大括弧で条件自体をくくり、＋でつなげる）
=FILTER(A2:E21,((C2:C21="栃木")*(E2:E21>50))+(C2:C21="神奈川")*(E2:E21>70))
条件は複数付ける事が可能であり、オートフィルタで人間がごちゃごちゃ操作するのではなく、関数で機械的に操作が可能。ただし、Googleスプレッドシートと異なり、カンマ区切りではなく
- AND条件は、「条件ひとつずつ括弧で括り、アスタリスクでつなげる」
- OR条件は、「条件ひとつずつ括弧で括り、プラス記号でつなげる」
必要があります
また、引数にて{1,1,0,0,1}と指定していますが（第二引数で取るので、FILTER関数の入れ子で実現）、1なら表示、0なら非表示を指定する事で特定列だけを結果として表示する事が可能（オートフィルタでは出来ない芸当）。当然、他の関数に抽出結果を食わせて合計を取るといった芸当も可能であり、AND条件・OR条件や他の関数を抽出条件に使って、高度な抽出なども可能にしています。
FILTER関数を使う事でVBAやPower Queryで操作する前処理をしておける為、複雑なロジックや抽出テクをVBAやPower Queryで行う必要がなくなり、また、セルの値で動的にフィルタが可能となるため、オートフィルタよりも遥かに高い利便性を有しています。
これまでのように何個も集計関数やらなにやらを使ってゴチャゴチャと手動で表を作るような非生産的な作業はこれで卒業です（VBAからもWorksheetFunctionとしてFilterが使えるので配列で取得がめちゃくちゃ簡単になりました）。
図：Excelの概念を覆す関数です
UNIQUE関数
こちらも、Googleスプレッドシートからの逆輸入関数で指定範囲内のユニークな値を列挙する為の関数です。例えば商品仕入台帳にて計上されてる仕入れ一覧からユニークな商品を列挙するなどして、取引のあった商品はどれになるのか？といった事がPower Queryを使わずとも可能になっています（今までコレだけの為にバッドノウハウとしてピボットテーブル使っていたようなダサい事例も見てきてます）
指定する数式のスタイルは以下の通り。
//単純にユニークな値を列挙する =UNIQUE(D2:D21) //スピル範囲演算子で利用する =SUMIF(D2:D21,G2#,E2:E21) //スピル範囲演算子で利用する２ =COUNTIF(D2:D21,G2#) //複数列を使ってユニークな値を列挙する =UNIQUE(C2:D21)
通常は１列だけを指定しますが、複数列範囲を指定した場合には、その２つの列で持ってユニークな値として抽出してくれるので、組み合わせの一覧を取り出す事も可能です。また、この関数で抽出したデータをsumifなどの関数に食わせる時に入れ子ではなく、G2がその一覧ならばG2#とする事で条件範囲として使えます。これをスピル範囲演算子と呼びます。本来単一の答えしか返せないSUMIFS関数で使うと、複数の計算結果として返してくれるようになります。
※=SUMIF(D2:D21,UNIQUE(D2:D21),E2:E21)といった入れ子を使わなくても済むのが、スピル範囲演算子の良い点
図：地味に利便性の高い関数です
SORT・SORTBY関数
オートフィルタは便利ではあるのですが、この時ID列などが無いデータでソートを掛けてしまうと、元の順番に戻すのが困難になってしまいます。そういったケースも含めて指定の範囲内のデータに対してソートを掛けた結果を返してくれる関数がSORT関数で、さらに複数のソート条件に対応させたのがSORTBY関数になります。オリジナルのデータは汚さずにソートを掛けて二次利用可能になるため、いちいちシートごとに同じマスタデータをメンテナンスなんて事も不要になります。
数式のスタイルは以下のような感じになります。
//単一列に対して、5列目を降順でソート =SORT(A2:E21,5,-1) //複数列に対してC列とE列を昇順でソート =SORTBY(A2:E21,C2:C21,-1,E2:E21,1)
両者の関数似てるようで、ちょっと引数の指定方法が異なるので要注意です。-1で降順となり1で昇順となるというオプション指定がちょっとオカシイ点も要注意。SORT関数の場合は列番号でソートする列を指定しますが、SORTBY関数の場合は範囲でソートする列を指定する点が異なるのが困った点です。
SORTBY関数の場合はさらに、ソートを掛ける順番というものがあるので、掛ける順番を間違えると全く異なる結果が返ってくることになるので、よく見極めて引数指定をする必要があります。
図：ソートするだけとは言え、結構使い所多し
XLOOKUP関数
FILTER関数に続いて非常に重要な関数が装備されました。これまで、vlookup関数で苦しんできた縦横を縦横無尽に検索して値を持ってくるテクニックとして、index + match関数がありましたが、Microsoft365専用関数として「XLOOKUP」関数が装備されました。Googleスプレッドシートにも最近装備がなされました。
この関数の特徴は
- vlookupと異なる指定の列の左側も検索対象にする事が可能（だからといって、ID列などを適当な列に置いて良いというわけではない⇒総務省統一ルール参照）
- index +matchのような複雑な縦横の座標特定が必要ないので関数の見通しが良い
- 返り値がない場合のデフォルト値を指定出来る（iferror関数が不要）
- 検索結果の表示対象の列指定は、列番号での指定ではなく、範囲での指定になっている。
- 標準でスピル対応してるため、１個の値ではなくそのレコードに含まれる別の列の値もまとめて持ってこれる（単純に答えを返す範囲を複数列範囲で指定するだけ）
数式の指定スタイルとしては以下のような形になります。
//数式の指定 =XLOOKUP(検索値,検索範囲,答えを返す範囲,"データがなかった場合の値") //複数列の値を同時に取ってくる =XLOOKUP(I2,A2:A21,D2:E21,"no data")
これまでの現場でのバッドノウハウ的な部分やエラー時の対応、その他諸々痒いところに手が届く関数の仕様となっている為、今後値のルックアップ系はvlookupやindex+matchではなく、xlookupが主役になると思われます。
※vlookupと違い、空欄に対する挙動が以下のように異なるので注意
- VLOOKUP：空欄は無視して#N/A
- XLOOKUP：空欄は空欄に一致する
図：長年苦しめられた苦行から開放される
テーブルとPowerQuery
テーブルの活用
概要
未だに事務員レベルで活用されていない代表例の機能がこの「テーブル」機能。基本的には事務の現場で管理帳票類を作って業務で利用するのであれば、データは「テーブル化」すべきなのですが、問題なのは、事務員レベルでは「テーブル化」する意味を理解出来ていないケースが多々あります。結果、原始的な帳票で知った範囲での関数頼りの問題の多いファイルが使われ、結果業務の煩雑化とミスの誘発を招いている状態です。歴史は古く、2003からリストとして装備され、2007からはテーブルと呼ばれて実装されてる機能です。
テーブル化のメリットは
- 関数の単純化（特にvlookupなどでは範囲の固定などが不要になる）
- データ範囲の自動拡大（これは参照してる別のシートからも範囲で指定が不要なので、いちいち手直しが必要なくなる）
- PowerQueryで活用出来るようになる
があげられます。いい加減、手作業での範囲手直し、関数手直し、ピボットテーブルからは卒業してテーブルを活用しましょう。これらが許されるのは新入社員までです。
テーブル化
データの塊をテーブル化する手順は簡単です。以下の手順でテーブル化し、テーブルに名前を付け、できればクエリ化もしておきましょう。
- タイトル行まで含めてデータの塊全範囲を範囲指定する
- 挿入タブ -> テーブルをクリック
- 先頭行をテーブルの見出しとして使用するにチェックを入れて、OKをクリック
- デザインタブが開かれるので、左上のテーブル名にわかりやすい名前を付けておく
できれば、続けて以下の作業を行いこのテーブルをクエリ化しておいて、PowerQueryで利用可能な状態にしておく。
- タイトル行まで含めてテーブルのデータを全部範囲指定する
- データタブを開き、「テーブルまたは範囲から」をクリックする。
- PowerQueryが開かれるので、プロパティに適当なクエリ名を入れる。何か変更を加えたら、下の適用したステップが自動で追加されていくので、こちらもわかりやすいように「ステップ名」を入れていく。
- そのまま閉じて、保持をクリック
- 新しくシートが追加されてデータが反映されるが、そのシートは不要なので削除してしまう。
これで、PowerQueryで利用可能なクエリ化が完了です。このデータは再編集したい場合は、クエリと接続を開くと一覧が出てくるので、そちらをダブルクリックすればOKです。
図：テーブル化は業務で利用は必須の作業
図：クエリ化しておく
関数の単純化
未だ多いのが、結構なデータ量にもかかわらず、データの追加の度に数式を手直し（対象範囲の拡大）、数式を新たな行に追加などの手作業をしている人がいます。例えば、vlookupなどでは、参照先の範囲に新たなデータが追加された場合、当たり前ですが関数の検索範囲について「手直し」が発生します。また、参照元にデータ行を追加した場合、当たり前ですが、vlookupの数式を「書き足し」しなければなりません。
またこの時、「参照先の範囲をきちんと固定」せずに、vlookup結果がズレていくといった致命的ミスをした経験をしたことがない人はいないでしょう。
テーブル化している場合は参照範囲は「テーブル名」となるので、参照先もテーブル化しておけば手直しが不要です。その結果、参照先範囲の固定も不要です。この時の参照元でのvlookupの数式は以下のような構造化参照となります。全行の数式が同じ数式となります。
//テーブルを使って構造化参照 =VLOOKUP([@地域コード],birth2020[#すべて],3,0)
上記の数式を見てもらえればわかるように、A2といった範囲指定はなくテーブルの列の指定、そして検索先もテーブル化しているため、範囲ではなくテーブル名での指定となっています。よって、参照先のテーブルデータが増えても、また地域コードの列を移動させても数式の変更は必要有りません。
図：式が単純化され手直し不要となる
データ範囲の自動拡大
テーブルで右クリック -> 挿入 -> テーブルの行（下）を実行すると、新たなテーブル行が追加されテーブル範囲も自動で拡大、関数も自動で補完されます。よって、ユーザがデータを追加時に、そのテーブル内で新たに関数を入力したり、また、このテーブルを参照してる別の式側で数式の手直しは不要です。
※単純にテーブルの一番下にデータを入力してもテーブル範囲は自動で拡張されますが、基本は上記の手順で行います。
一番下の行に書いても自動拡張されない場合はオートコレクトの設定がオフになってるので以下の手順でオンにしておくと良いでしょう。
- Excelを起動して、ファイル -> オプションを開く
- 文書構成 -> オートコレクトのオプションを開く
- テーブルに新しい列と行を含めるのチェックをオンにする
これで、行列に直接記述してもテーブルが自動で拡張するようになるので、データの追加などが容易くなります。
図：新たな行追加でも関数は補完される
図：オートコレクトのオプション設定
プログラムからの操作が簡単になる
テーブルおよびPowerQueryを使うことによって、これまでならば様々な処理を自前でVBAやVBScriptにて実装していたものを装備する必要性が無くなります。Access VBA等の場合にはExcelと違ってコードの量がぐっと低いのは、クエリという機能で予めデータを処理しておける為です。Excelに於いてもPower Queryで予め処理をさせておいた結果に対してだけ、VBAやVBSで処理をする事で、コード自体書かずに済むだけでなく、Rangeの操作ではなくTableに対しての操作は非常に簡素なメソッドのみで行えるようになるため、メリットが非常に大きいです。
VBScriptにてテーブルやPower Queryを操作する実例については以下のエントリーで記述していますので、参考にしてみてください。非常に短いコードで直感的に操作が可能になるため、積極的にテーブルやPowerQueryを使っていきましょう。
Power Query自体の作成も自動化出来る
Power Queryは大変便利な一方、ハードルが高いのも事実。また複数のファイルへ接続して加工して作業してといったケースの場合、それらを一個ずつ作ったり、また増減が発生した場合手直しが必要になる。
ということで、これら個別のクエリの作成や作業などのクエリ自体も自動化して増減に自動対応させるといったことも可能です。以下のエントリーでそういった事例の為のPower QueryをVBAから作成するテクニックをまとめてあります。
名前付き範囲リスト
Excelに従来よりある「名前付き範囲」とテーブルを組み合わせる事で、ドロップダウンリストを自動拡張する事が可能です。従来のドロップダウンリストの場合、リスト項目を増やした場合、それを用いてるドロップダウンリスト側でも「範囲の修正」を行わなければなりません。しかし、テーブルと名前付き範囲を併用した場合、このドロップダウンリストは自動的に範囲が拡張されるので、手直し自体が不要になります。
以下の手順で作成します。
- ドロップダウンリストの元になるデータはテーブル化しておく
- そのテーブルのうち、ドロップダウンリストで使う範囲をタイトル行含めて指定する
- 「数式タブ」を開き、定義された名前 -> 選択範囲から作成をクリック
- 上端行にチェックが入ってるのでこのままOKをクリックすると、タイトル列が名前付き範囲の名前となる
- 名前の管理から作成した名前付き範囲は色々と管理編集が可能
- 次にドロップダウンを設置したいセルを選び、データタブ -> データの入力規則をクリック -> データの入力規則を開く
- 入力値の種類は「リスト」を選択
- 元の値は、=記号をつけたら続けて、4.の名前付き範囲の名前を入れる（F3キーで名前が出てくる）
- 名前付き範囲がリストとなる。この状態でテーブルに新しくデータ行を追加すると、名前付き範囲も自動で拡張されるため、ドロップダウンリストの内容も拡張されます。
テーブルを活用する事でこのように無駄な作業を省力化できるだけでなく、メンテナンスフリーになりミスも減らせる為、積極的にテーブルは活用すべきという良い事例です。
図：名前付き範囲の管理画面
図：リスト範囲は名前付き範囲とする
PowerQueryでやるべき事
Excel2013時代にはアドインとして、2016からは標準機能として搭載され、現在Power BIにも搭載されてる高度なデータの加工を関数ではなく、Accessのクエリのように行える強力なツールですが、事務の現場での利用度は非常に低い印象です。しかし、このツールを活用しないのはもはや怠慢とも言えます。いつまでも関数頼り、ピボットテーブル頼りの非生産的なデータ管理はせず、卒業してPower Queryを利用しましょう。
マトリクス変換
データで頂戴！！って言ったら、出されたエクセルデータがいわゆるリスト表ではなく集計された「マトリックス表」だったことが何度もあります。人間にとっては集計されて縦横のクロス集計は見やすいのかもしれませんが、プログラムで扱うデータとしては非常によろしくありません。しかもその表は元データがあって作られたピボットテーブルではなく、sumifsなどで加工されたものだったりするわけです。
これをプログラムで使いやすいリスト表に変換する必要がありますがそのためにVBAを書くのも面倒。ということでPower Queryには「列のピボット解除」という素晴らしい機能があります。今回のサンプルにはe-Statの昭和59年都道府県別収入統計のデータを入れてありますが、これを本来の生データであるリスト表に変換してみたいと思います。
- 事前にデータはテーブル化し、クエリ化しておきます（サンプルはこの処理は終わってます）⇒ 都道府県別収入シートが元データになります
- データタブのクエリと接続をクリック
- 収入1984元データというクエリを開きます。
- ピボット解除をする列を選択。今回の場合、値となる集計世帯数から右側の全部の列を選択します。
- 変換⇒列のピボット解除をクリックする
- タイトル行は属性に代わり、数値は値となってリスト表となります。
- 適当にステップに名前をつけておく
- ファイル⇒閉じて次に読み込むをクリックする
- 新規ワークシートを選び、OKをクリックすると変換されたデータがそのシートにテーブルとして書き出される⇒ピボット解除シートが書き出したテーブルになります
出力データはテーブルそのものなので、元データの都道府県別収入シートにデータが追加された場合、右クリック⇒更新するだけでピボット解除シートのデータも書き換わります。
図：ピボット解除する列の指定
図：リスト表になったデータ
単純集計
Accessなどではおなじみの元データを特定の列でグルーピングして、値の集計を取る集計表（集計クエリ）。これまでのExcelでは実はこの単純な集計表は作成するのが非常に面倒であったのが、Power Queryを使った場合簡単に実現可能になる。わざわざユニークな値を別に切り出してから、sumifs関数でゴリゴリ書くなんて「ダサい」真似をする必要もない。当然データの追加があっても、元がテーブルなので修正する必要性もありません。
都道府県別収入シートのクエリを元に、「年収階級で平均」のデータを除外して、「年間収入（千円）」の合計を、五大都市圏だけ切り出すというものを作ってみます。
- 都道府県別収入シートのクエリ（収入1984 元データ）を開く
- まず、年収階級の「▼」をクリックして、平均を取り除く（フィルターされた行というステップが追加される）
- 次に地域の「▼」をクリックして、北海道、東京、愛知、大阪、福岡の5大都市圏のみを表示する
- 年収階級の列を削除する（削除された列というステップが追加される）
- 地域コードと地域名の列だけ選択し、変換タブのグループ化をクリック
- ダイアログにて、操作は「合計」とし、列は「年間収入（千円）」を選ぶ。新しい列名は「収入総合計」とでも付けておき、OKをクリックする
- グループ化された行というステップが追加されるので、適当に名前を変えておく
- そのままPower Queryを閉じて、保持をクリックすると、新しいシートにデータが吐き出される。⇒五大都市圏シートがこの作業を行ったもの
実際に生データを手計算で検算してみるとわかりますが、平均の値をきちんと除去した状態で、五大都市圏の年間の収入値が算出出来ています。Power Queryを使う事でつまり「この作業を自動化」出来ているという事です。オートフィルタや切り貼り、関数だけでは到底出来ない「手順の自動化」が出来るのがPower Queryの強みです。
図：グルーピングで重複列の値をまとめる
図：クエリと接続を右クリックからでも書き出しは可能
カウントと合計を1度に行う
単純なデータの表があった時に、そのデータに於いてIDを基準とした個数のカウントおよび各IDにある数値の合計を同時に行いたい場合があります。ID毎にグループ化して表示する必要があり、これもAccessのクエリではよく作るパターンです。
これをPower Queryで行いたい場合には前述の単純集計で同時に集計列を2つ追加するだけで可能です。
- ID列を選択してグループ化をクリック
- 新しい列で「カウント」と命名し、操作は行数のカウントとする（これでID毎のカウント数が取れる）
- 集計の追加をクリックする
- 新しい列で「合計」と命名し、操作は合計とし、列に金額を指定する
- OKをクリックすると1つのデータからカウントと金額合計の2つの列を持ってグループ化した集計
個別に作る事なく1度に作成出来てしまうので、複雑に考えなくても作れてしまうのがPower Queryの良い点です。
図：1データからカウントと合計の2つを取る
図：２つ集計列を追加する
ファイル接続
事務現場のExcelでよく見かけるものの１つとして「ファイル間リンク」があります。かなり昔からある機能で、他のExcelファイルのデータ群を参照して、データを取ってくる機能なのですが、そもそもメンテナンス性が非常に悪い機能で、Accessのクエリとは異なり参照先のデータ群というより、特定のセルの計算結果を１個取ってくるだけでも使われる為、一体どこのセルがリンクされてるのか？また、もう利用していないのに残ってるなんてケースもあります。
そろそろこういった管理は卒業し、Power Queryでデータソースとして自身のExcelファイルに持ってきてから作業をするべきでしょう。今回はAccessのデータベースからデータを取り出してみようと思います。
- データタブの「データの取得」⇒「データベースから」⇒「Microsoft Accessデータベースから」をクリック
- 目的のaccdbファイルを選択する
- ナビゲーターが出て、テーブル一覧が出てくるので、テーブルを選び、読み込みの▼をクリックして、読み込み先をクリック
- テーブル⇒新規ワークシートを選ぶとテーブルにてExcelにデータが書き出されます。（この時点でテーブル化＆クエリ化がなされています）
- テーブルを右クリック⇒テーブル⇒クエリの編集をクリック -> 他のファイル接続シートが出力したデータのシートになります
- データソースの設定をクリックすると、このExcelで利用されてる外部ファイルの一覧が出てきます。
これを元にすれば複数のデータソースを後に紹介する他のクエリとの結合で利用すれば、vlookupなど使わずに複数ファイルを簡単に結合可能になります。
図：利用するテーブルを選ぶ
図：データソースの変更もここから可能
データソースを相対パスに変更する
自分個人で利用する分には、データソースのファイル等もそうそう移動する事がない為、いわゆる絶対パスで指定したままでも問題はありません（というよりも、Power Query自体は絶対パスでないとデータにアクセス出来ない）。しかし、第三者と共有するケースの場合（Box Drive等で利用する場合も含む）、そのデータソースのファイルの絶対パスは必ずしも自分と相手とで同じとは限りません。これは古い機能であるブック間リンクでも同じことが言えます。
そこで、データソースの指定については、絶対パスではなく相対パスに変更しておく事で、他のユーザが利用した場合であっても、問題なくデータソースに接続出来るようにしておく事が可能です（相対パスであればデータの更新をしてもエラーになりません）。
以下はExcelのファイルとデータソースのAccessファイルが同じフォルダに有るケースを想定して、作っています。
- Excelの適当なシート（今回はexcelpathとした）のB1セルをクリックする
- 以下の数式を入れるとそのブックのフォルダまでの絶対パスが記述される
=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1) - B1セルを選択した状態で、数式タブ⇒名前の定義を開く
- 名前に適当な名前をセットする（今回はexcelmanとしました）
- データソースと接続してるテーブルを右クリック⇒テーブル⇒クエリの編集でPower Queryを起動する
- データソース設定ではなく、クエリにある「詳細エディター」をクリック
- ソース=といった設定内容が出てきます。file.Contents以下に絶対パスが記述されているので、ここを以下のような形で書き換えます。
let ファイルパス = Excel.CurrentWorkbook(){[Name="excelman"]}[Content]{0}[Column1], ソース = Access.Database(File.Contents(ファイルパス & "離島気象データ.accdb"), [CreateNavigationProperties=true]), _島の気象情報 = ソース{[Schema="",Item="島の気象情報"]}[Data] in _島の気象情報 - ファイルパスには4.のパスが入り、ソースではそれに加えてソースのファイル名を&でつなげたものを変数としてFile.Contentsに指定します。
- 完了をクリックして閉じる
- Power Queryも閉じて保持し、上書き保存して一旦、Excelを閉じる
- Excelファイルのあるフォルダのフォルダ名を変更してみて、Excelを再度起動してみる
- 再度、データソースと接続してるテーブルを右クリック⇒更新を実行してエラーがでなければ成功です。
ファイル毎にこのような処理をしておく事で、同じフォルダ内にデータソースファイルがある限り、絶対パスが異なっていても問題なくデータソースにアクセス出来るようになるので、まずファイルを作ってPower Queryを使った場合には、データソースはこのように書き直しておくと良いでしょう。
※また、上記の2.におけるパスからBox直下までのパスに削りたいケースが多々あります。出てきたパスから直下まで削るには以下のような数式で別に結果を出し、そちらに名前付き範囲を設定して、ソースで利用するようにしたら、応用範囲が広がります（そのファイルがいる場所だけじゃなく、Box内にあちこち散らばってても、問題がなくなる）
//B1のパスから\マーク3つ分のパスを削る
=MID(B1,1,FIND("*",SUBSTITUTE(B1,"\","*",3)))
日付が毎日変動するようなものであるならば、「=TEXT(B2,"yyyymmdd")」といった数式で「20220826」といった日付を取り出して上記のフルパスに結合してみたりなど、この辺りは応用次第でかなり自由にファイルの接続ができるようになるのでおすすめです。
図：絶対パスでないと受け付けない
図：現在のパスを取る数式をセットするのが肝
図：これで起動する度にフルパスを取得し直します
指定の期間でデータを絞り込む
前述の相対パスにするというテクニックでは、名前付き範囲の値をもって、ソースを動的に変更する事が出来ました。同様の仕組みを利用してセルに記述された日付を持って、フィルタをする事が可能です。M言語で作る必要があります。
- セルの開始日付の場所を名前付き範囲としてそれぞれ、開始日付(startdate)、終了日付(enddate)を定義する
- 元データの年月日列の日付の形式は今回は yyyy/MM/dd hh:mm:ssの形式になっています
- Power Queryで元データを開き、詳細エディタを開く。
- 適当に年月日列の値を「指定の値の間」にてフィルタする
図：適当に日付でフィルタする - 以下のような形で、名前付き範囲の値を取得しフィルタをする（今回は、Datetime.Fromを使います。yyyy/mm/ddの場合はDate.Fromを利用します）
startdate = DateTime.From(Excel.CurrentWorkbook(){[Name="startdate"]}[Content]{0}[Column1]), enddate = DateTime.From(Excel.CurrentWorkbook(){[Name="enddate"]}[Content]{0}[Column1]), フィルターされた行 = Table.SelectRows(変更された型, each [年月日] >= startdate and [年月日] <= enddate) - 完了をクリックすると、セルの値でもって、データが期間指定でフィルタされる
セルの値を書き換えて、テーブルを右クリック⇒更新をすると元データから絞り込みが実行されて、指定期間内のデータだけにフィルタされて表示されるようになります。
※この方法の他に、開始日付と終了日付のセルを単独でクエリ化⇒パラメータ化して同様の事を行う方法もあります。
図：無事にセルの値で動的フィルタが出来るようになった
クロス集計
現場のファイルを見ていて結構見かけるものの一つに「ピボットテーブルでクロス集計した結果に、手動で作った表をくっつけて管理票を作る」というバッドノウハウを見かけることがあります。コレ最悪なのが
- ピボットテーブルのデータと一体化してるわけじゃない
- ピボットテーブル部分は本来getpivotdata関数で構成されてる為、容易に取り出しが出来ない
- 継ぎ足しされてるデータはピボットテーブルのデータ元の縦横拡張が全く考慮されていない（ただただ結果に足してるだけという・・・）
- そもそもピボットテーブルは「見せる資料の為のクロス集計」データなのであって、「管理帳票で二次利用する為のクロス集計」では無い
という事を本人が全く理解出来ていない点。結果、常に手動でのメンテナンスを必要としてるだけでなく、クロス集計列項目が増えた場合データが破壊されることになる。またその為にオカシナ「列項目は増やしてはならない」という全く持って理解不能なローカル運用ルールが付け足されてたりする始末の悪いデータです。（そのルールのために事前にデータを消すといったしょうもない作業が手順書に書いてあったりするのがあります）
管理帳票で二次利用する事を目的としたクロス集計はPower Queryで作成するのが定石です。
- 今回はピボット解除のシートデータを全選択し、データタブ -> テーブルまたは範囲からをクリックし、新しいクエリを作る
- 縦および横に取りたい列と、値の列だけを残して列を削除しておく。
- 属性列（横に取りたい列）を選択し、変換タブの中にある「列のピボット」をクリックする
- 値列の指定のダイアログが出てくるので、「値」という列を指定してOKをクリックする。
- Power Queryを閉じて保持をクリック
結果的にはもともとクロス集計されてる都道府県別収入シートと同じようなテーブルが生成されますが、こうすることによって、データの増減に対応できるだけでなく、メンテナンスフリーになります。テーブルなので他のシートからの参照も非常に容易で、Power Query上で計算列を追加したり、グルーピングも行えるわけで、ピボットテーブルに無理やりデータをくっつけるといったダサいことをやらず、スマートにデータを表示する事が可能です。
図：クロス集計する為のダイアログが出てくる
図：テーブルなので二次利用が容易
他のクエリと結合
通常、Excelで２つのテーブルの間でデータを繋いでいく場合、２つのテーブルに共通のIDを持たせて、１列ずつvlookupで参照させて１つのレコードを構築するような事をこれまでやってきた人は多いかと思います。列の数だけvlookupで式を構築するという手間は地味に面倒です。Accessの場合はリレーションシップで複数のテーブルを連結して１つのクエリを作るなど日常茶飯事で、この有用性が分かってるので、Excelは使わないという人も多いです。
Excelにも古い機能のリレーションシップの管理という機能があるのですが、これで作った場合結果がピボットテーブルで生成されてしまうので利便性が非常に悪いです。そこでPower Queryのクエリのマージという機能で結合させて１つのテーブルにするのがベストです（vlookup関数を使う必要がなく、複数列を持ってこれます）。
- 結合元（都道府県別収入一覧）と結合先（人口動態統計 -> birth2020と命名してあります）の２つのテーブルを用意し、いずれもクエリ化しておきます。
- 結合元クエリを開き、ホームタブの「クエリのマージ」の▼をクリック -> 新規としてクエリをマージをクリック
- 下のプルダウンにて、birth2020を選択する（これが結合先）
- 結合元と結合先、それぞれで「地域コード」列を選択する
- 結合の種類はデフォルトの「左外部」や「内部（一致する行のみ）」を選択する。今回は後者を選択。
- OKをクリックすると、結合元の右端に「Table」と書かれた列が出現する
- その列の矢印のついたボタンをクリックして、表示する結合先の列を選択する（今回は総数、男子、女子のみ選びました）
- OKをクリックすると、地域コードで連結されて、birth2020のデータが呼び出されて結合されます。
- Power Queryを閉じて、保持をクリック
- 新規ワークシートに結合したテーブルとして表示される -> 結合テーブルというシートがその出力シートになる
vlookupという関数に頼らず、またデータベース特有の内部結合、外部結合といった２つのテーブルをリレーションで結合するというvlookupでは出来ない高度なテクニックが簡単に実現出来ます（Accessの使い手ならこの辺りは普段からやってる事なので新鮮味がないですが）
※結合の種類で右反や左反を使うことで、どちらかのテーブルを基準とした「不一致クエリ」を作ることも可能。不一致のものだけを取り出せるます。
図：結合する列の選択と種類を選ぶ
図：結合先の表示する列を選択
計算列の追加
Power Queryで商品単価列と数量列があり、これらを計算した結果を表示する「合計列」を作りたいシーンがたくさんあります。元のテーブルにはそのような列がないので、Power Query上で作成し、以下のように計算させて列を追加します。
- 予めクエリ化しておいたテーブルをPower Queryで開きます。
- 列の追加タブ内にある「カスタム列」をクリック
- 列名に「合計」と名付けて、カスタム列の式には、参照できる列から選びつつ、数式を構築します
- OKをクリックすると計算列が追加されます。
図：カスタム列で計算式を組んで名前を付ける
復数テーブルを一枚に
顧客毎に同じ形式のExcelデータをそれぞれの顧客と共有しているケースで、実際に業務の処理の場合は、１枚の別のExcelに都度コピペしてから作業するなんてケースを見かけます。しかし、このコピペという作業が実に無駄であり、またこういった作業をRPAで組むのは愚策です。Accessデータベースの世界ではこれら復数のテーブルを1枚に合体する「ユニオンクエリ」というものが存在しますが、Power Queryでも同様の事が可能です。
- 復数のExcelシートの同じ形式のデータを１枚のExcelシートに「ファイル接続」にてデータソースとして追加しておきます（今回は3枚）
- これらのシートはそれぞれ企業1～企業3としてクエリの名前を振っています。
- 大元の企業1クエリを開き、ホームタブ内にある「クエリの追加」をクリック
- 今回は3つあるので、「3つ以上のテーブル」をチェック
- 追加のダイアログが出てくるので、接続済みの利用可能テーブルから必要なテーブルをすべて選択する
- OKをクリックし所定の位置にテーブルで出力すると、合体したテーブルとして出力される
- 元の３枚のテーブルにデータを加え、「右クリック」⇒更新をすると再度合体されて最新の状態になる
これで常に最新の個々のExcelデータを参照しつつ、合体したデータを元に作業をすすめる事になるのでコピペという作業そのものが不要になります。合わせて、すべてを更新の中にある「接続のプロパティ」にて、開く時に自動更新や○分毎に自動更新などの設定する事で、別途違うアプリから利用するようなケースではテーブルの更新作業も不要になるので、セットしておくと良いでしょう。
図：クエリの追加で復数テーブルを選択する
図：合体した１枚のテーブルに
作業の自動化
Power Queryは画面を見るとわかりますが、列を削除したりカスタム列を追加したり、またグループ化を実行すると都度、右側のパネルにその作業毎のステップが追加され、過去のステップを見るとその時点でのデータの状況がわかるようになっています。
つまり、本来の元データからPower Queryで様々な集計や追加を行う作業そのものが「自動化」されていると言えます。特に本来ならば復数の作業表を用意して行っていた作業をこれらの手順に従って結果的に、最終的な答えのクエリを作り上げているので、途中途中で必要な中間の表を別途テーブルで持つ必要がありません。具体的な例示をすると
- 元の表から特定要素でフィルタをする
- フィルタした結果、必要な列以外を削除する
- 残った列に於いて特定要素でグループ化と合計列を追加する
- 最後に必要な列以外すべて削除する
- 4.を元にクロス集計する
- テーブルで出力する
- 元データが更新されたら、「更新」をクリックして、上記の1.～6.が実行されて最新のデータが6.のテーブルに反映する
といった形です。これまでの表の場合は列の削除などを行ってしまうと上記の順番通りにならなくなるため、欲しい答えとは違うものになるため、中間の表を別シートでまず出してから、本来の結果シートに別途作業をして出すといった冗長な作業を行っていました。これらが不要になるため、複雑な整形処理をノーコードで自動化していると言えるため、数式に頼った手法よりも、効果的で場合によっては単純なマクロやRPAは不要となります。
図：もう作成手順を覚える必要はない
項目別の累計表を作る
経理で言えば商品別の商品有高帳や、人事で言えば人別の残業時間の累計合計といったように、１つのデータの塊に対して、上から順番に加算していった累計合計を付けたい場合があります。単純な累計だけであれば上の項目に現在の値の合計を出すようにSUM関数で出せば良いですが、項目別となるとそう単純ではありません。
以下の手順で累計の列を追加していきます。今回は社員毎の累計残業時間合計を求めます。
- Power Queryを開いて対象のテーブルを開く
- データは社員番号、日付の列で昇順でソートする
- 社員番号の列を選択して、変換タブにある「グループ化」をクリック。ダイアログが出るので列名は「社員別テーブル」とし、操作は「すべての行」として実行
図：社員番号でグループ化され他の列が一纏めになる - 追加タブを開いて、カスタム列の追加をクリック
- ダイアログが表示されるので、計算式は以下のような式を入れて社員番号毎日付毎に連番のindexを降るようにする。index対象は3.の社員別テーブルとし、１から開始で1ずつ加算とします。インデックス列の名前はカスタムとします。このフィールド自体はカスタムという名前を付けています。
図：インデックスを人毎に用意する//インデックス列を追加 =Table.AddIndexColumn([社員別テーブル],"INDEX",1,1)
- 5.のカスタムの右上のボタンをクリックし、社員番号、社員名、日付、残業時間、INDEXにチェックを入れて展開する
図：社員番号毎に連番のインデックスが追加された - 列名がカスタム.列名となってるので、適当な列名に変更しておく
- さらに追加タブを開いて、カスタム列の追加をクリック
- ダイアログ出たら以下の数式を入れて、社員毎のINDEX毎の残業時間の累計をする列を作る（累計残業という名前にしました）
図：List.FirstNにて各行でリストを作り、List.Sumでそれらを合計する
//累計を1行毎に作る = List.Sum(List.FirstN([社員別テーブル][残業時間],[index]))
- 出力するのにindex列はいらないので削除し、閉じて読み込むにてシート上に出力する
これで、既存のテーブルデータに対して、各行毎且つ人毎の累計が取れるので、累計で推移のグラフなどを描くのに利用できます。
図：社員別テーブルは展開しないのがポイント
文字の一括置き換え
膨大なデータを扱ってると、避けられないのがデータクリーニング。例えば全角半角が統一されていなかったり、特定の文字の表記法がデータによって揺らぎが生じていて統一性が無かったりするデータを、Excelを開いていちいちゴリゴリ修正するというのは非生産的です。よくあるパターンは、全角半角変換や株式会社を(株)に省略する等の作業。
これらをそれぞれの関数等でゴリゴリ行うより、一括して変換用のテーブルを用意してまとめて処理したい所。Power Queryでは元データに対して変換テーブルをぶつけることで一括でまとめて文字列の置換をする事が可能です。
- Power Queryで変換したい元データを読み込む（今回は会社名データ）- companyという名前にしました。
- また、会社名を変換する為のデータも読み込む（これが変換テーブルになる） - okikaeという名前にしました。
- companyクエリを開き、列の追加タブを開き、「カスタム列」を追加する
- 列名を置換後と命名して、列の式として以下のようなものを入力する
図：List.AccumulateとReplaceで文字を置き換える//okikaeテーブルで会社名列をリプレース List.Accumulate(Table.ToRows(okikae),[会社名],(x,y)=>Text.Replace(x,y{0},y{1})) - すると変換した結果が置換後列に表示されてるので、このまま閉じて読み込むでシートに書き出す
株式会社を(株)にし、後株だろうと前株だろうと変換します。同じ理屈で、半角カナと対応する全角カナを登録すれば、カナの半角全角変換や、英数字などの全角半角変換なども可能です。そういった文字の揺らぎを一括で変換するのは関数では手間なので、Power Queryが非常に活躍します。
図：変換テーブルで一括変換
テーブルに小計を追加する
Excelのテーブルは非常に便利でメリットが大きいのですが、1つ弱点として「小計」が追加できない点があげられます。総合計の「集計行」は機能として追加が出来るのですが、各項目ごとの「小計」はテーブルではなく、通常の範囲でなければ追加できない欠点があります。
Excel標準の小計機能（行の非表示等）は使えないですが、テーブルそのものに各セクション単位で小計を追加するには、Power Queryを使うことで実現が可能です。またメリットとして、通常の小計は範囲指定が必要であるため、データの増減が合った場合範囲の修正が必要ですが、Power Queryの場合はテーブルを利用する事になるので、データの増減で手修正が不要であり、自動化が出来るのでメリットもあります。
あとで結合した際にソートして、元データの並び順に対して小計を入れたいので、支店名に対して支店コードを入れておきましょう。
図：元データの図
- 元になるデータをPower Queryで開く
- 更にそのクエリを左サイドバーから右クリック⇒複製を選択してコピーを作る（今回はfish集計という名前を付けました）
図：まずはクエリを複製する - 2.のクエリにて、今回は支店コード、支店名でのグループ化したい列を選択して、変換タブの中の「グループ化」をクリック
図：グループ化でまずは集計する - ダイアログが出るので、新しい列名を「金額」、操作を「合計」、列は「金額」を指定します。列名を金額にする理由はあとで結合する際に、金額合計を金額の列に連結するため。
- 支店名毎の売上合計金額が集計されるので、次に列の追加タブの中の「カスタム列」をクリック
- 新しい列名には今回は「適当な名前」と入れて、式には「[支店名] & "小計"」といった数式をいれて、OKをクリック（これが小計名になる）
図：部門合計の列を作成する - 支店名の列はもう不要なので選択して削除する
- 6で付けた適当な名前を「支店名」に変更する。列名を支店名にする理由は、あとで連結した時に支店名列の所に小計名が来るようにする為です。
- 作成された支店名列を支店コードの隣に移動させる
- 数式バーにあるfxというアイコンをクリックする。すると新しいステップが追加されるのでソレを開く（カスタム1といった名前がついてる）。数式は並べ替えられた列といったものが入っているハズです。
- 数式バー内に、以下の数式を入力して、総合計の行を作成する。支店コードに対して、9999を指定してる理由は、ソートした時に一番下に来るようにするためです。
図：総合計行が作成される= Table.InsertRows( #"並べ替えられた列",Table.RowCount(#"並べ替えられた列"),{[支店コード=9999,支店名="総合計",金額=List.Sum(#"並べ替えられた列"[金額])]}) - 1.の元データのクエリを開く
- 数式バーにあるfxというアイコンをクリックする。すると新しいステップが追加されるのでソレを開く（カスタム1といった名前がついてる）。数式は変更された型というものが入ってるハズです。
- 数式バー内に、以下の数式を入れて2つのクエリを結合する
図：２つのクエリを単純に結合する= Table.Combine({変更された型,fish集計}) - 支店コードを昇順でソート、次に支店名を昇順でソートする。すると各支店の最期に小計が入り、総合計の行が一番下に来るようになる。
- 閉じて読み込むでシートに出力する
これでデータの増減や支店の増減に対して自動で各支店毎の小計が入るようになるので、以降はメンテナンスフリーで自動化が可能になります。
図：無事に各支店毎の小計と総合計が入るようになった
差分比較
これまで出てきた項目に更にテクニックを加えることで、2枚のシートの差分のあるセルの値だけを抽出するといったような事をPower Queryでやらせることもできます。
以上の6つを利用することで可能になります。詳細は以下のエントリーに記載していますので、挑戦してみてください。本エントリーでは型の変更と値の置換については取り上げていませんが、これも地味ながら必須のテクニックになります。
UTF8のCSVを取り込み
クラウドのシステムからエクスポートしたCSVファイルの多くは、そのままダブルクリックすると、Excelが起動し文字化けした状態で開かれて二度手間を取られることも多いです。また、それらを毎回インポートする作業を行うのも生産的じゃありません。そこで、毎回同じファイル名で同じ場所から取り込むのであれば、Power Queryであれば文字化けせずに自動化して取り込む事が可能です。
Power Queryでこの処理を実現する場合は、以下のエントリーを参考に実装してみてください。
Box上で使う場合の対応
Box上でPowerQueryにて使う上で自分が引っ掛かって、対処が必要であったケースに付いて記述しています。
データソースを相対パスに変更する
Box上では、複数名で共有して使う為、データの更新を行う場合にはユーザ毎に更新元のファイルのパスが異なります。そこで、前述の相対パスの仕組みを使うわけですが、必ずしも更新元のファイルと更新先のファイルが同じフォルダ内にあるとは限りません。そこで、Box上で一番最初のルートパスを更新するユーザ毎に書き換えるようにします。
- 前述の相対パスの仕掛けに対して、3列目（C1セル）に根っこのパスを取得するセルに以下の関数を追加する
//B1はこのファイルがあるフルパス（Box上） =MID(B1,1,FIND("*",SUBSTITUTE(B1,"\","*",3))) - 前述の関数で「C:\Users\ユーザ名\」が取得できるので、このセルに対して名前付き範囲で「excelman」を設置する
- データソースと接続してるテーブルを右クリック⇒テーブル⇒クエリの編集でPower Queryを起動する
- データソース設定ではなく、クエリにある「詳細エディター」をクリック
- ソース=といった設定内容が出てきます。file.Contents以下に絶対パスが記述されているので、ここを以下のような形で書き換えます。
let ファイルパス = Excel.CurrentWorkbook(){[Name="excelman"]}[Content]{0}[Column1], ソース = Access.Database(File.Contents(ファイルパス & "Box\気象データ\南大東島\離島気象データ.accdb"), [CreateNavigationProperties=true]), _島の気象情報 = ソース{[Schema="",Item="島の気象情報"]}[Data] in _島の気象情報フルパス以下に、Boxフォルダ以下のファイルまでのフルパスを残して記述するのがポイントです。
- ファイルパスには4.のパスが入り、ソースではそれに加えてソースのファイル名を&でつなげたものを変数としてFile.Contentsに指定します。
- 完了をクリックして閉じる
- Power Queryも閉じて保持し、上書き保存して一旦、Excelを閉じる
- Excelファイルのあるフォルダのフォルダ名を変更してみて、Excelを再度起動してみる
- 再度、データソースと接続してるテーブルを右クリック⇒更新を実行してエラーがでなければ成功です。
図：Boxフォルダ前までのユーザ名含めたパスを切り出す
復数テーブルを一枚に
Box上で複数テーブルを1枚にする場合、必ずしも相手のファイルが同じフォルダ内にあるとは限りません。また後から新規に追加されることを考慮すると前述の方法ではうまく管理が出来ないです。という事で、以下の手順で複数のテーブルを1枚にするクエリを作成します。Excelのケースで記述します。
- データ⇒データの取得⇒ブックからを開く
- ファイルを指定して、ナビゲータが開かれる。テーブルを選択し、読み込み先をクリックする
- データのインポートではどこかに出力するのではなく、「接続の作成のみ」を選択します
- クエリと接続にクエリが作成されます。これを必要な枚数分繰り返します。
- 右サイドパネルのクエリと接続から適当なクエリをダブルクリックで開いて、Power Queryを開く
- 詳細エディタで前述の「相対パスへ変更」を施しておく。コレを枚数分繰り返します。
- 左パネルの「クエリ」をクリックしてパネルを開き、右クリック⇒「新しいクエリ」⇒「結合」⇒「クエリを新規クエリとして追加」を選択
- 3つ以上のテーブルを選択し、必要な接続済みのクエリを全て追加する（結合クエリという名称にしました）
- 閉じて次に読み込むで出力先のセルを指定して完了
- 後からこの統合クエリをシートに出力したい場合には、データ⇒既存の接続⇒結合クエリを選んで開く⇒既存のワークシートの指定先を選びOKを押せばテーブルとして出力されます。
図：接続のみでシートに出力はしない
図：新たに統合役のクエリを追加する
VBA入門
事前準備
VBAやユーザ定義関数、マクロの記録を作るためには、事前準備が必要です。以下の作業で、Excelに「関数タブ」を表示する必要性があります。
- Excelを起動し、ファイル⇒オプションをクリックする
- リボンのユーザ設定を開く
- 右のパネル、右側のリボンのユーザ設定側にある「関数」のチェックを入れる
- OKをクリックして閉じる
関数タブが表示されるので、これでVBAの記述やマクロの記録が行えるようになります。
図：デフォルトでは非表示になってる
図：開発タブの内容が表示された
ユーザ定義関数
ユーザ定義関数とは、Excelの関数をユーザが自身でVBAを使ってコーディングし、独自の関数として使えるようにする為の機能です。機能といっても、VBAでFunctionを定義するだけなので、通常のVBAコーディングとなんら変わらないです。
メリット・デメリット
通常は、標準で搭載されているExcelの関数の組み合わせで殆どの場合対処可能なのですが、問題なのは対処できるという事と利便性は一致しない事。無数の入れ子や複雑な数式の繰り返しなどを行った結果、関数が複雑怪奇になりメンテナンスが困難になるケースなどがあげられます。メリットをまとめると
- 複雑怪奇な入れ子やメンテナンス性の悪い関数頼りの処理を、見通しの良いVBAで作成できる。
- 結果、作成される数式は非常にシンプルになり、数式の破壊が行われても修復が容易になる。
- VBAのメソッドを利用出来るので、関数よりも高度な処理を行わせる事が可能になる。
逆にデメリットとしては
- ファイルの拡張子はxlsxではなく、VBAを使うのでxlsmにして保存する必要がある。
- 基本、作成した関数はアドイン化しない限り、そのブックの中だけでしか使えない。
- 僅かなコードで作れるとはいえ、VBAの知識が必要になる
しかし、関数で書くコードの量はせいぜいよくて、10数行、必要とするVBAの知識もわずかで済むので、VBA学習の入門としては最適なものです。
作成方法
作成方法といっても、ユーザ定義関数では複雑な処理を構築したりは通常行わないのでシンプルに作成する事が可能です。以下の手順で作成します。今回は偏差値を算出する独自の関数を作ってみようと思います。ちなみに、これを標準関数で作る場合には以下の通り。
=(C11-AVERAGE($C$2:$C$30))*10/STDEV.P($C$2:$C$30)+50
- 開発タブのVisual Basicを開く
- 左のプロジェクトのパネルで右クリック⇒挿入⇒標準モジュールを追加する
- 右のコードを書くパネルに以下のようなコードを書き、ブックを閉じる
'偏差値を算出する関数 Public Function HENSACALC(ByVal avgman As Range, ByVal tarval As Variant) As Variant '平均値を出す Dim rangavg As Variant rangavg = Application.WorksheetFunction.Average(avgman) '標準偏差を出す Dim rangstd As Variant rangstd = Application.WorksheetFunction.StDev_P(avgman) 'ターゲットの値を元に標準偏差を計算して返す HENSACALC = (tarval - rangavg) * 10 / rangstd + 50 End Function - xlsx形式ではブックを保存出来ないので、名前を付けて保存にて、xlsm形式で保存する
- これでもうHENSACALC関数が使えるようになっています。
今回作成した関数を解説してみますと
- ByValが関数の引数で、範囲指定の場合 As Rangeを、ソレ以外はStringやInteger、Variantを付ける（型指定）
- 値を返す必要があるので、関数名の終わりにもVariantなどの型指定を付ける
- Application.WorkSheetFunctionでExcel標準関数をVBAで利用可能になります。
- 複雑な処理や条件分岐などをここに記述し、最後にHENSACALC = で計算結果を返してあげる
図：複雑な処理手順も理解しやすくなる
関数として登録
ユーザ定義関数は作成者は使い方を知っているので、特にこれ以外で作業は不要なのですが、第三者でも使えるようにするといった場合、どんな引数が必要でどんな結果になるのか？という説明が無いと使い勝手が非常に悪いです。ユーザ定義関数は数式入力中にはインテリセンスが効かないので、標準関数と違いどんな引数を指定すべきかは表示されないのですが、以下の作業を追加する事で、関数の挿入を使った場合には、詳細な情報を表示させる事が可能です。
- Visual Basic画面を開く
- 左のプロジェクト画面に「空のクラスモジュール」を一個追加する
- 2.のクラスモジュールに以下の説明文用の関数を記述する（名前はClass1として追加する）
Public WithEvents App As Application '関数の説明文を登録する関数 Private Sub App_WorkbookOpen(ByVal Wb As Workbook) '関数の説明、引数、ヘルプへのリンクを定義 Application.MacroOptions Macro:="HENSACALC", _ Description:="範囲と対象値を指定する事で、偏差値を計算し返す関数です。", _ Category:="数学/三角", _ ArgumentDescriptions:=Array("値の範囲", "対象の値"), _ HelpFile:="https://office-hack.com/excel/standard-score/" End Sub - プロジェクトのThisworkbookを開く
- 以下のコードを追加する（Class1を呼び出して、Excelが開かれる時に自動でApp_WorkbookOpenがこれで開かれる）
Dim x As New Class1 'ユーザ定義関数の説明文を登録 Private Sub Workbook_Open() Set x.App = Application End Sub
- 上書き保存して、Visual Basic画面を閉じる
今回の措置を追加することで、数式タブ⇒関数の挿入、対象の関数を選ぶと、App_WorkbookOpenで規定した内容が表示されるようになり、利用者が関数の利用に於いて迷いが少なくなります。この時利用しているApplication.MacroOptionsで指定する内容は色々ありますが
- Description - 説明文
- Macro - 関数名（自分で作成した関数名と一致する必要がある）
- Category - どの関数区分に属するか？を指定する（無くても良い）
- ArgumentDescriptios - 引数に何を取るのか？といった内容を配列で指定する
- HelpFile - ヘルプ時のリンク先URL
基本は上記の５つを指定するだけで十分です。
図：説明文用の関数と自動で登録するコードを書く
図：説明文が表示されました
使い方
作成した関数の使い方は、標準の関数と変わらないです。但し、標準関数と違って入力中の引数に何を入れるか？などは表示されないので、難しい点はそこだけ。今回のHENSACALC関数は、「値の範囲」「対象の値」の２つを引数に取っているので
=hensacalc($C$2:$C$30,C2)
という形になります。C列がいろいろな人の得点が入ってる値の範囲でC2がその中で対象者の得点を指定するという形。標準関数で組み合わせるよりも圧倒的に短く単純になりました。
図：特に複雑な条件分岐で力を発揮します
アドイン化
自作した関数は基本的にはそのExcelファイルでしか利用する事が出来ません。また、そのExcelファイルは拡張子がxlsmの形式にする必要があるため、「他のファイルでも利用したい」「他の方にも利用してもらいたい」という場合には、毎回Visual Basic画面から関数の作成と登録が必要になるのは煩雑です。そこで、これらの関数を「アドイン化」することで、アドインを入れているPCでは、導入する事で即利用でき、拡張子がxlsxのファイルでも動作するようになります。
アドイン化する手順は以下の通り
- 関数を作ったexcelファイルを開く
- 名前を付けて保存を実行
- ファイルの種類を「Excelアドイン（*.xlam）」形式を選択
- なぜか、保存場所がアドインのフォルダになってしまうので、ここで保存先を改めて指定する
- xlam形式のファイルにはシートが存在せず、またダブルクリックしても何も開かれません。
3.で保存したアドインを以下の導入手順で導入する事で、作成した自作の関数をそのPCで使うすべてのExcelファイルで利用できます。アドインをオフにすると使えなくなります。よって、外部に配布するExcelファイルの場合は、相手がアドインを入れていないと使えないので、その場合は従来通りのExcelファイルに直接関数を埋め込む必要があるので注意。
図：xlam形式で保存するだけ
アドインの導入方法は以下の通り
- Excelを起動する
- 開発タブを開き、Excelアドインをクリックする
- アドインはドキュメントフォルダにでも保存しておく
- 2.で出てきたダイアログに於いて、参照をクリックする
- アドインがオンになった状態でアドインが導入されます。
アドインの説明文を入れたい場合には、
- Excelのメニューからファイル⇒情報⇒プロパティ⇒詳細プロパティを開く
- コメント欄に説明文を書いて、アドインとして保存する
といった形になります。
図：アドインが導入された
マクロの記録
Excel5.0からVBA自体は搭載されており、その後マクロの記録も装備されていたと思うので、マクロの記録機能自体は相当古い時代から装備されていた事になる。元々はVBAでゴリゴリ書けない事務方の人等が、手軽に操作を記録して再現する、今のRPAそのものであり、RPAなんかより遥か昔から実装されいたものです。
VBAで記述されているので、マクロ有りのファイルはこれまで同様xlsm形式で保存する必要があります。
ユーザ定義関数同様、VBAの入門としてはまず使うべき登竜門であり、ここを足がかりに細かい修正でVBAを使っていくのが王道と言えます。また、Excelの自動化であればRPAではなくまず「マクロの記録」でしっかり動くように勉強するほうが、コスト面でも汎用性でもRPAよりもメリットが大きいので使わないのはもったないと言えます。使い方ですが以下の通り
- 開発タブを開く
- マクロの記録ボタンをクリックする
- マクロ名は分かりやすい名前を付けて、付けたい場合はショートカットキーも指定する。
- OKをクリックすると記録がスタートする
- 記録終了をクリックすると記録が終了しマクロが保存される
- マクロの削除は、マクロボタンをクリックすると一覧が出てくるので、選んで削除する
記録したマクロの実行は、マクロをクリックして一覧から選んで「実行」をクリックすることで再現される。今回以下のようなマクロをmacromanという名称（ショートカットキーはCtrl+j）で記録しました。実行すると
- 偏差値シートから全データをコピー
- マクロ書き込み先シートをアクティブ
- A1に値で貼り付ける
- 3.の全範囲に罫線を追加
- タイトル行の色を黄色にし、オートフィルタを追加
- A2を基準にウィンドウ枠を固定を追加
という手順を記録しています。一瞬ですべての作業が終わるのでわかりにくいですが、このように作業を色々記録していくわけですが、例としてシートの追加などでそこにコピペとなると毎回新規追加のシート名が異なるので、色々マクロの記録をする上での癖というものを見に付けて修正する能力が必要になります。
また、今回はCtrl+jでマクロを実行できますが、ボタンを配置してクリックのみで実行したい場合には
- 開発タブを開く
- 挿入⇒フォームコントロール⇒ボタンを選ぶ
- シート上で適当な大きさを指定する
- マクロの登録ダイアログが出るので、作ったマクロ名（今回はmacroman）を選ぶ
- OKをクリックすると、ボタンにマクロ実行が割り当てられる
これで、ボタンひとつで記録したマクロを実行できるようになります。
図：マクロの記録開始画面
図：ボタンにマクロを割り当てる
高度な自動化
現在は昔とは違い、マクロ、VBAだけに限らずPower QueryやPower Automate、RPAといった別の自動化の手段も出てきており、ケースに応じて別の手段を利用するのも良いでしょう。しかし、別の手段はそれだけの為に「追加のコストの支出」を伴い、また自分で作らないのであるならば、事務員としては単純に仕事が消えスキルも残らないという結末しか待っていません（自動化して淘汰されるだけ）。
故に手軽で追加コストの無いマクロやVBA, VBSを身につける事で、以下のような他のアプリケーションの操作や管理アプリの作成も可能になります。
その他の機能
アドイン
前述のVBA入門の項目にもあるように、世には様々な方々が作成してくれた作業の効率改善や便利な機能を形にした色々な「アドイン」が有償・無償で提供されています。アドインの導入方法については、前述のVBA入門のアドイン化を参照してください。ここでは、それらの内、業務で使う上で効果的且つフリーソフトとして提供されてるアドインについて紹介します。
※あまり入れすぎると、起動時に読み込む為、Excel自体が重くなりますのでアドインの入れすぎは要注意。
※現在はOfficeも64bit化してる為、旧来の32bit用で作られてるアドインは64bit環境では利用出来ません。以下で紹介してるものは動作確認済みです。
クラシックスタイルメニュー for Office 2016
長年、Officeを使っている人にとってはOffice2007より始まったあの悪しき「リボンスタイル」は扱いにくい事この上無いものです。そこで、旧来のOffice2003の頃までのメニュースタイルを「リボン」で再現したアドインがこのアドインで、Office2019でも引き続き利用可能です。ExcelだけでなくWordやPowerPointにも用意されており、旧来のスタイルでOfficeを継続利用する事が可能です。
図：旧式スタイルでExcelを利用できる
Excel CSV I/O
今現在のウェブサービスやシステムから出力されるCSVはその殆どが「UTF-8」でエンコードされたテキストで出力されるのがデフォルトになってきています。しかし、驚くべきことにExcelは未だにUTF-8でエンコードされたCSVファイルをダブルクリックすると、文字化けした状態で表示されるという始末。これまでは、Shift-JIS（ANSI）でエンコードされてきた事がネックとなっているようで、一度メモ帳で開いてANSIで保存し直すという無駄な手間暇が発生している現状です。
そんなUTF-8のCSVデータを取り込んだり、001といった文字を勝手に数字で取り込まないようにインポートしてくれるアドインがこのツール。
図：標準で対応してほしい所だが
正規表現検索
Excelの検索や文字列の置換は原始的な機能しか装備しておらず、基本完全一致（大文字小文字区別はあるけれど）での検索しか対応していません。その為、似たようなワードの置換やあいまい検索をしたい場合には、何度も検索したり検索文字列を変えてみたりと非常に面倒な事が結構あります。
この面倒な検索を「正規表現」を使っての検索や置換を行う事が出来ます。正規表現自体高度なテクニックではありますが、例えばメールアドレスがきちんとメアドの規則に従っているかどうか？をチェックしたり、全角を半角に変えるなどなど様々なテクニックが用意されており、メリットが大きいです。
基本はVBScriptの正規表現構文を使うと良いでしょう。
図：正規表現は別途勉強が必要です。
完全コピーアドイン
通常、Excelでシートの範囲や行列単位などでコピーし、相手のシートに貼り付けた場合、セルの行列の高さや幅、一部の書式、ページ設定などは反映されず、手作業でコピー前のページの内容を自分の手で反映し直さなければならないというとてつもなく面倒な作業が待っています。
そこでこのアドインを導入する事で、普通にコピペするだけでこれらの情報まで反映された状態でペーストをしてくれるという非常に有り難いものです。特にUIや設定などが無いので導入して、いつものようにコピペするだけです。
kt関数アドイン
Excelに標準で搭載されていない様々な便利機能を提供してくれるアドインとしては、RelaxTools Addinが有名ですが、正直ちょっと機能が多すぎて逆に使うのが正直億劫になるレベルです。
そこで使いたいのが、適度な機能と独自に構築されたユーザ定義関数集が入っているこのアドインです。主に日付（カレンダー）や時刻関係に特化して作られているもので、Excelに機能追加と独自関数追加の両方を行っている為、詳しくは作者サイトを閲覧してください。主に使えそうだなと思ったものは
- カレンダーから日付入力機能
- 年間カレンダーの一括作成機能
- 営業日算出関数などの独自関数
図：この手のカレンダー手で作ってませんか？
Excel Online
オンライン版のExcelである「Excel Online」は、OneDriveやSharePoint上に配置したExcelファイルから利用する事ができ、複数名同時書き込みなど「Googleスプレッドシート」と同様の作業を行う事が可能。しかし、オフライン版とは異なり自動化や補助などを行わせる為の「VBA」は搭載されていない(Googleスプレッドシートは当初より、Google Apps Scriptを搭載している)。
しかし、2021年6月、それまで一部ユーザでのみ公開されていた「Office Script」というJavaScriptベースでの言語が搭載され、Microsoft365ユーザであれば誰でも利用可能になっています（MicrosoftアカウントではNG）
主に実現できる内容としては
- 操作レコーダーを使ってマクロの記録同様、操作手順を自動化する機能
- 直接スクリプトを記述し、レコーダやExcel Onlineが標準装備していない機能を実現する機能
- Power Automate連携を構築し、脇役として利用する（関数は直接スクリプトで用意が必要だけれど）
但し、利用者が制限されている事もあって、直接記述に関しては知見が殆ど作られておらず、Google Apps Scriptのような汎用性は全く無いので、そういった機能の実現はOfficeアドインで対応するのが定石になっている。
むしろ、メインの利用は後述のPower Automate連携であり、この中にある「スクリプトの実行」をPower Automate側からイベントトリガー（OneDriveにファイルがアップロードされたら等の様々なイベント）から、Excelの作業へとタスクランナーさせるのがメインの使い方になってくると思われる。
Power Automate
Microsoft365の自動化分野の肝となるソリューションが「Power Automate」。タスクランナーと呼ばれるサービスでウェブサービス同士を繋いで連携させて、処理を自動化する為の仕組みです。ここに2020年にデスクトップRPAとして「Power Automate for Desktop」が加わり、ローカル環境の自動化およびローカルとクラウドを連携させての自動化まで実現可能になりました。
Windows11からはPower Automate for Desktopが標準搭載されて基本無償で利用可能（Microsoftアカウントでも利用可能）であるため、現在既にもう事務の現場で活用され知見がウェブにどんどん増えている状況で、将来的にRPAとしてはPower Automate for Desktopが圧倒的になるでしょう。
正直、Power Automateのみでもあまりにも範囲と活用幅が広いためいずれ、Power Automate活用については別エントリーで作成予定です。Excel分野に特化してみると
- Excel Onlineに自動的にデータを追加
- 他のアクションをトリガーにしてExcel Online上のOffice Scriptを実行し、処理を自動化
- 逆にExcel Onlineのデータを元にPower Automateで連続メール送信等の自動化
といった事が可能です。Boxのイベント⇒PAで受け取り⇒Excel Onlineに書き込みは以下のエントリーで実現しています。また、Power Automate for Desktopについては、すでに２つほどエントリーを作成している為、どんなものなのか？イメージしやすいのではないかと思います。
Power BIで活用する
Microsoft365ユーザであれば基本無償で利用できるBIツールがPower BI。Web版とDesktop版の２つがあり、わざわざ高額なTableauを導入するまでもないレベルであれば、活用しない手はありません。また、いちいちExcelでゴリゴリ手動でグラフの作成やら分析表を毎回作るという無駄な作業をやめ、さらにPower AutomateでExcelにデータを自動追記させているのであれば、リアルタイムにこのツールを使ってBI分析が可能になっています。
データソースとしてExcel、Excel Online、Access、SAP、SQL Serverなど多種多様なものを利用する事が可能になっており、このツール一つだけとってもPower Automate同様非常に奥が深い為、以下のように全3回でエントリーを作成しています。特にExcel Onlineとの相性は良く低コストで実現可能であるため、Excel活用の先を行きたいのであれば、学習は必須と言えるでしょう。
Formsを使って自動でタスク管理
何故か未だに当たり前のようにFAXや電話で業務を続けている日本。既に海外では全廃の動きまで出ています（国内でもコールセンター廃止する企業も出てきています）。そこで利用するのがMicrosoft Forms。主に以下のような問題を解決します。
- 24時間365日、仕事の依頼を引き受けることが可能
- 相手にわざわざ電話を使わせるといった「強制的に時間を奪う行為」をさせずに済む
- FAXやOutlookと違い、宛先間違いなどで情報漏えいをする心配が無い
- フォーム内容はExcel Onlineに記録でき、そのままタスク管理・Teams通知・カレンダーやPlanner登録、リマインダー登録などが可能
- Outlookと異なり、メールが埋もれてタスク取りこぼしや、結果的にお客に催促させるといった行為をさせずに済む。
- 高度なタスク管理ツールであるTrelloなどの外部サービスへ連携が可能
- メアドなどを入力者に入れてもらう事で、後日メール通知時の情報漏えいが自然と抑制される（というか一々、連絡先リストから検索するというのも手間以外の何物でもない）
以下のエントリーでは、Microsoft Formsを使ってフォームを用意し、自動応答メールの送信やExcelファイルへの自動記録、またTeamsへ通知などをすべて自動化して装備する手段を紹介しています。お客様からの依頼内容に応じて処理を分岐をPower Automateに作って書き込み先を分けたりすると汎用性が向上します。
脱Excelのお話
長らく事務の現場で利用されてきたExcelですが、昨今はその利用自体が「業務の煩雑化」「業務効率の低下の原因」「方眼紙・レガシー問題」が取り沙汰されています。紐解くと
- ファイルサーバやクラウドストレージのアチコチに管理票が点在
- BoxやSharepointに代表される「複数名同時利用」でファイルが壊れたり分裂する問題
- そもそも専用のツールを使うべき所を手動で管理するという原始的な手法が許されなくなってきた
- 結局人が読み書きをしてるため、漏れやミス・間違いなどは全く解消されることは無い
- クラウドストレージの場合、ファイルアクセスのパーミッションが不適切で情報漏えいの可能性
- 管理票と称していながら、同じようなファイルが複数増殖し、どれがオリジナルかわからない
- WordやPowerPointなど他のツールで作るべきものまでExcelで無理やり作ってる始末の悪いファイル
- 何よりも事務員のITリテラシーが低すぎる問題（20年前からやり方が何も変わってない上に、現代のIT技術に追従出来ていない⇒だから自動化で事務削減しましょうという動きになってるわけだけれど）
- システム化すべきものをせず、素人がExcelでツールと称して作り、関数のみで構築したサブシステム（コレ自体が管理手間を増やしミスを生んでる原因だと本人気がついていない）
こういった現状、昔と違い今は許されません。故に今は既にまともな企業では「脱Excel」が推進されており、Excelは本来の利用用途である「表計算」でだけ利用するよう、IT投資を進めているわけです。世の中の要求ハードルは上がり、コスト削減効率が高まっているにも関わらず、作業内容が20年前と変わらず、支払われる給与が同じまま・・・なんて有り得ないわけでして。以下に主な例題
- タスク管理 ⇒ Microsoft PlannerやGoogle Keep、また高度なものならRedmineやTrelloでリマインダーまで使える時代にExcelで手動で管理とか有りえません
- 顧客管理 ⇒ 個人レベルの台帳で管理そのものが内部統制（SOX法）的にアウトです（普通に監査指摘事項に該当します）。CRMなどの管理専用のサービスを導入が必須。
- スケジュール管理 ⇒ そもそもリマインダーやカレンダー形式、スマフォでの一意で確認出来ないExcelシート管理等、電子手帳以下の存在です。
まず管理系のファイルは基本NGと思っていただいて結構。Excelにただそれらのデータがあるというだけで、人力に頼ってる段階で「現代ではありえない低レベル作業」です。例えるならば、未だにソロバンで業務をしたり、Excelで例えるなら関数を使わず電卓で手打ちしてセルに直打ちしてるくらい「愚行」です。
Excelはあくまでも個人用の「表計算をする為のツール」であって、複数名の業務の管理を行う為のツールではありませんし、そのための機能は一切備わっていません。まずはこういった点から「Excelを使わない」という判断が出来ない人間は、この先他社に行っても通じず、取り残されて行き場を失う事になるでしょう（それが許されるのは100名以下の中小企業のみです）。
関連リンク
- 統計表における機械判読可能なデータの表記方法の統一ルールの策定
- 全国民に配るべき！総務省が示した【データ入力の統一ルール】
- Excel派の私が「脱Excel」を決意した5つの成功事例を紹介します。
- VSTACK 関数 / HSTACK 関数
- （Excel）FILTER関数でのAND検索とOR検索
- 電話は、かける側にしかメリットがない、かけられる側にとっては最悪のコミュニケーションツール
- ホワイトカラーのモラル向上、生産性向上に必要なこと
- 「FAXの使用を2020年までに全面的に廃止する」とイギリスの国営サービスが決定
- 日本の労働生産性は、なぜOECDで21位と低迷しているのか？／田口佳孝氏
- 一般事務職が消える！転職市場から素早く抜け出すための施策とは？
- 将来なくなる仕事が”事務職”である３つの理由がこちら【解説】
- 会計事務職が100万人分も減少──この15年で増えた仕事、減った仕事
- Excel方眼紙をどうやってやめさせられるかを考えてみる
- 「Excel（エクセル）レガシー問題」とは？
- 「禁止」より「管理」が重要！シャドーITを防ぐ4つの対策
- Excel(エクセル)で複数のデータファイルを結合させる方法【フリーソフト】
- 令和２年（2020）人口動態統計（確定数）の概況
- 全国家計構造調査（旧全国消費実態調査） 昭和59年全国消費実態調査 都道府県別
- 偏差値とは？エクセルで偏差値の簡単な求め方（関数での計算方法）
- Application.MacroOptions メソッド (Excel)
- Excel ユーザー定義関数に機能説明や独自ヘルプをリンクする方法
- Web版の「Excel」で“シンプル リボン”が一般リリース ～機能性を損なわずにコンパクトに
- PowerAutomate Excel Online Buisinessのアクション一覧
- 生産農業所得統計 / 確報 平成29年生産農業所得統計
- Power Queryテクニック集その1
- Power BIでSUMIFSを?!(修正版)
- Power Queryで相対的な日付範囲フィルタを設定する計算式チートシートと解説
- 【Power Query】パラメータ設定で指定した期間のカレンダー作成（Power BI）
- Power Queryで正規表現を使ってデータを整える