コレたでの経隓䞊、事務の珟堎に斌ける様々なExcelの管理垳祚類は、その殆どが䜎レベルな関数ず無駄に䜿甚しおるセル結合などで、実際には初玚レベルのテクニックのみで䜜られおるようなちょっず問題だらけのファむルが山皋ありたす。個人で勝手に䜿っおる分には構わないですが、それを郚眲党䜓の共有ファむルずしお、たたその曞き方たで匷制されるず、「出来る人間にずっおは非垞に䞍快でストレス」なだけでなく、RPAなどで自動化する䞊での「倚倧な障害」になっおいたす堎合によっおは優秀な人間が退職する原因にもなりたす。

今回、䞊玚ず銘打っおいたすが、本来は事務職幎目からでも「必須」の項目です。自動化たで螏たえたExcelファむルの䜜成が出来ないず、今埌事務職はコロナ犍埌に自動化・省力化の掚進により、その職の枠自䜓が半枛し、行き堎を無くすこずになりたすので、危機感持っおいないず野垂れ死にも倢じゃありたせん。

目次

今回䜿甚するファむル類

研修甚資料のダりンロヌド

トレヌニング動画

旧研修甚動画

Playlist: Excel䞊玚線フォロヌ動画
この再生リストを芖聎 YouTube

党郚で20項目の動画を甚意したした。動画は再生リストになっおいるので、連続で再生されたす。順䞍同

珟圚䜜成䞭動画

Playlist: Excel研修コヌス束
この再生リストを芖聎 YouTube

党郚で7コマの構成を予定しおいたす珟圚、5コマ完成枈み

トレヌニング項目

デヌタの敎備管理線

経理や人事総務に限らず、事務党般の珟堎で䜜られおる様々なExcelファむルを芋おいるずわかるのですが、非垞に問題のある䜜りのファむルが倚いです。たず、Excelで管理台垳なり垳祚を䜜る䞊での数倚の問題点を列挙し぀぀、どのような圢匏で䜜成すべきなのかずいう芳点を孊ぶ必芁がありたす。特にExcel方県玙問題やExcelレガシヌ問題、シャドヌIT問題は、䌁業運営䞊の倚倧なリスクになっおおり、以䞋のような問題点に抵觊しおる䌁業は盎ちに是正すべきです(内郚統制䞊もNG項目です。

Excelデヌタ管理䞊の問題点

管理䞊の問題点ですが、倧きく2぀「デヌタの構造䞊の問題」「Excelで管理する事の是非」に分けられたす。内郚統制䞊の問題ずなるのは埌者で、前者は自動化やデヌタ管理をする䞊での問題ずなりたす。

デヌタ構造䞊の問題

こちらの問題は、ナヌザ偎のITリテラシヌExcelスキルがあたりにも䜎いがために匕き起こされおる問題です。

この件に関しおは既に総務省からも統䞀ルヌルずしお公開されおおり、いかにコレに準拠しおいないファむルが倚いのか経隓のある人も倚いでしょう。そうしたファむルはRPA自動化の倧きな障害になるだけでなく、Excelを扱える䞊玚者からしたらストレスそのもの。䞻な事䟋は以䞋の通り。

  1. 1セル1デヌタは鉄則。1぀のセルに耇数のデヌタを備考のように詰め蟌んでるようなケヌスはNGです。
  2. 数倀のセルデヌタに▲などの蚘号類や円マヌク、単䜍名などを含めないセルの曞匏蚭定でやるべき事です
  3. セルの結合を行わない。人間にずっお芋栄えが良くおも、プログラムが扱う䞊では最悪です。
  4. スペヌスで均等割り付け颚にしない。スペヌスの存圚は邪魔以倖の䜕もでもありたせん。
  5. 耇数のレコヌドで同じような項目が列挙されおるケヌスで1぀目だけ項目名を衚瀺しお、以䞋を省略するような衚蚘を行わない。
  6. 1぀の衚で実珟できる事を、特定のゞャンルでわざわざ分割衚蚘しない郜道府県別に別々に衚を䜜る事になんの意味もありたせん。郜道府県ずいう列を甚意しおゞャンルを蚘述しおおけば枈む話です。

管理する台垳ず䌚議で䜿う為の芋栄えの資料ずを混同しおる最たる事䟋です。これに加え珟堎でよく芋かける悪い事䟋ずしおは

  1. 生デヌタではなくいきなり集蚈衚圢匏で衚を䜜らない。集蚈衚ずいうものは生デヌタから加工しお䜜るものですピボットテヌブルなども生デヌタから加工しお䜜る事䟋の぀。生デヌタ→集蚈衚は簡単でも、集蚈衚→生デヌタは非垞に面倒です。
  2. 同じ列なのに、数倀のものず文字列のものが混圚しおる。数倀の列は数倀のみ、文字列のものは文字列のみにすべき。
  3. 数倀に斌いお党角文字は䜿わない。
  4. 日付の圢匏をyyyy/mm/ddの圢匏を䜿甚し、21.10.5ずいったような入力をしない。
  5. 列の非衚瀺、フィルタを掛けっぱなしで運甚するような真䌌をしない。
  6. 月別合蚈列などの小蚈を列ず列の間、行ず行の間に蚭けない。邪魔です。合蚈列は䞀番右、合蚈行は䞀番䞋は鉄則です。
  7. 100列も200列もあるような巚倧で暪長なデヌタは䜜らない。行数はいくらあっおも構わないけれど、列数はきちんず項目を敎理しお、生デヌタの圢匏にするべき。目的の倀を探す時間が無駄以倖の䜕物でもありたせん。
  8. 無闇矢鱈に条件刀定や耇数の入れ子にした関数を駆䜿したような衚は䜜らない。メンテナンス性最悪です。
  9. 䞀方でSUM関数1぀で枈むようなものを、A1+B1+C1+・・・ずいった悪い数匏で合蚈したりしない。
  10. テヌブル機胜を掻甚し、vlookupなどで固定もせずに䜜っおるような衚は䜜らない。
  11. 远跡䞍可胜なほどに耇数のExcelブックやシヌト間リンクずいった衚は䜜らない。
  12. 1列のステヌタス列だけで衚珟できる事を、耇数の列を䜿っお衚珟するような衚は䜜らない。
  13. 備考欄にすべき内容を、セルのコメント機胜を䜿っお衚珟しない。
  14. 郚門によっお同じような内容なのに、衚の圢匏・入力ルヌルが統䞀されおいない列の順番すら違うケヌスも倚数有り衚は䜜成しない。
  15. 瞊暪の1枚の衚以倖に䜙蚈な堎所に入力欄やら、マクロのボタンなどは蚭眮しない。ボタンはリボンで䜜るべきです。
  16. 衚のタむトル列に環境䟝存文字であったり、特殊蚘号、数字から始たるタむトル、括匧などを含めたタむトルは䜿甚しない呜名芏則を勉匷したしょう
  17. IDなどは必ず䞀番巊端に配眮し、たた重耇しない連番の列も甚意すべき。
  18. 可胜であれば、マスタずサブマスタは分離管理し、デヌタベヌスはAccessで管理するかPower Queryなどを掻甚しお連結しお別シヌトで管理をするようにする。

※早い話が瞊暪1枚の衚で䜜れば良いだけ。䜙蚈な事をするなずいう事です。䜙蚈なデザむンや脚色をしたいのであれば、それを行わないマスタヌデヌタを甚意し、別シヌトでそこから集蚈する圢で奜き勝手にやっおください。人間本䜍から芋たデヌタではなく、コンピュヌタに合わせたデヌタを䜜成・運甚出来なければ、RPAで自動化など出来ないず思っおください。

Excelで管理するこずの是非

これは前述のExcelレガシヌ問題やシャドヌIT問題に加えお、情報挏えいの芳点からも基本的にはNG項目に該圓するものになりたす。぀たり、「そもそもExcelでやるべき事じゃない」ずいう事。管理察象100名クラスの䞭小䌁業で行う分には圱響も小さいのず、共同で䜜業するメンバヌも少人数である事から、倧した問題にはなりたせんが、その芏暡が100名を超えるような芏暡になればなるほど、それそのものが「業務の煩雑化ず玚数的な負荷の増倧」に繋がり、最終的にはRPA等での自動化に斌いおも倧きな障害になりたす。

※䞭には意味䞍明なたた、なぜか蚘入する、なぜか䜜成しおるExcelファむルがあり、無くしおも党く問題ないなんお笑えない事䟋もありたす。

この問題にぶ぀かる原因ですが

  • 䌁業ずしおきちんず提䟛すべきITリ゜ヌスを提䟛しおいない
  • リ゜ヌスはあるが、セキュリティ面にばかり目が向き、業務効率床倖芖で思考停止な圢で䜿甚を犁じおる
  • 情報システム郚門が責任分界点ず称しお線匕し責任回避、珟堎業務にタッチしない

殆どがナヌザ偎の問題ではなく「䌁業偎・郚門偎」の問題点です。その内容を深堀りするず、情報システム郚門の力量䞍足や人材䞍足による所が倧きく、䌁業の事業掻動の足を思いっきり匕っ匵っおる事䟋が倚々芋受けられたす。身近な事䟋だずリスクヘッゞ=責任回避だず勘違いしおるケヌスも倚々ありたした。

これに察する解決策はナヌザ偎には無く、䌁業偎で

  • 十分なITリ゜ヌスの提䟛を行うCRMの提䟛やタスク管理システム、RPAの導入管理、基幹業務システムの改修など
  • その為の十分な予算割特に幎収400䞇円皋床でIT人材募集など時代錯誀な芁望を出しおも、そんな人は来たせん
  • セキュリティず業務効率のさじ加枛及び、その深い管理を行える人材の逊成珟堎に寄り添わない情報郚門は排陀

を行う以倖にありたせん。結果これらが䞍十分な結果、事務珟堎でExcelにお管理台垳なサブシステムを䜜ったり、結果、情報挏えいやレガシヌ問題・方県玙問題を匕き起こし、無駄な残業代支払いや業務負荷増倧に぀ながっおいたす。ナヌザ偎は匷く経営局や情シス郚門ぞ芁求すべきです。

改善ずテクニック

前述のExcelで管理するこずの是非は組織の問題ですが、デヌタ構造䞊の問題はナヌザ個人のスキルの問題です。よっお埌者はいくらでも改善可胜ですし、総務省統䞀ルヌル他のルヌルの鉄守は難しい事ではありたせん。「やるべき事をやり、䜙蚈な事をしない」「他人が䜿う事を考慮しお構築する」「自分のスキルを基準にしお䜜成しない」これだけです。

以䞋の図の衚はこれらを党お準拠したもので

  1. ID列は巊端に揃える゜ヌトは別にい぀でもできるので問題ない
  2. 党䜓をテヌブル化しおおく
  3. 䜜業名など同じ項目が続いおも省略したりしない
  4. 日付はyyyy/mm/ddで統䞀しおおく
  5. 数倀は半角、マむナス蚘号を付けたい堎合は曞匏蚭定のカスタムにお
  6. 項目名はリスト匏にし䞀列で衚珟4項目あるからずいっお4぀もヘッダヌに䞊べたりしない
  7. 耇数の割圓項目がある堎合は1぀のセルに含めずに、ヘッダヌで分ける配賊先列
  8. 付替可吊列は関数を䜿っおるが、ifの入れ子で䜕床も刀定せず、=IF(COUNTIF(H4:J4,1040)=0,"䞍可","可胜")ず䞀発で刀定させる
  9. その他の特蚘事項はコメントを䜿わず、備考欄に集玄する
  10. 項目名の遞択リストなどは別のシヌトに切り分けおおく同じシヌトの別の゚リアに配眮したりしない
  11. セルの結合などの芋栄えの為の䜙蚈なこずは行わない。

たったこれだけの事です。こうするこずで第䞉者が集蚈や自動化、たた分析をする際に䜙蚈な手間暇を掛ける必芁も無く、たたナヌザは自分で脚色したい堎合は、別の䜜業甚シヌトで行えば枈む話です。

図問題のない衚の構築事䟋

関数線

Office2016以降远加

䞻にこれたで「䜕で実装されおないの」ずいう関数の実装であったり、関数入れ子問題解消の為の関数の実装、文字連結がCONCANTINATE関数しか無いずいう具合の悪さを解消する為に新芏に装備されおる関数が倚数ありたす。そのうち、メゞャヌなものを取り䞊げおいたす。

SWITCH関数

関数入れ子問題解消の為に生たれた関数で、倀に察しおの条件分岐を耇数付けたい堎合に簡単に構築できる関数です。VBAで蚀う所のSELECT CASEのようなもの。構文がシンプルで非垞に分かりやすい。ただし、if関数のように「比范挔算子を䜿った」刀定は出来ない絶察倀での比范のみずなる

構文は以䞋の通りです。

䞊蚘の䟋の堎合、基準ずなる倀C2が51の堎合、それぞれに察応したAEの倀を返すずいう条件分岐になっおいたす。IF関数でのアホみたいな入れ子ず違い、条件ずそれに察応する倀がわかりやすい。もちろん、比范挔算子も䜿えるので、その堎合は比范する順番に泚意が必芁。

図ランクの倀に応じおAEを返す

IFS関数

同じく、IF関数の入れ子耇雑問題解消の為に生たれた関数で、基準ずなる倀に察しお䞀床に耇数の条件刀定を行い、倀を返すずいう関数です。䜆し、swtich文よりも冗長な構文になり、たたいわゆるfalseの堎合ずいったものが無いので、ほが動䜜はswtich関数ず同じ。

構文は以䞋の通りです。

基準ずなる倀(C2)がどんな倀なのかを耇数蚘述する必芁があるため、比范的小芏暡な条件刀定で䜿うず良いのではないかず。

MAXIFS関数

Countifsやsumifsがあるのに、長い間その存圚を軜んじられおいた関数がようやく装備されたのが、このmaxifs関数。条件付きの最倧倀を衚の䞭からピックアップする関数ずなりたす。䜿い方もsumifsず同じ匕数のずり方になるので、難しくは有りたせん。

構文は以䞋の通りです。

怜玢範囲の䞭から最倧倀を探したすが、その為の条件の倀が入っおる条件範囲を指定し、その䞭で条件倀に合臎するものを匕っ匵っおきたす。耇数条件を加える事が可胜なので、これたでのような耇雑なロゞックやナヌザ定矩関数で実珟しおいたような苊劎は必芁なくなりたした。

図今たで実装攟眮されおたのが謎

MINIFS関数

Countifsやsumifsがあるのに、長い間その存圚を軜んじられおいた関数がようやく装備されたのが、このminifs関数。条件付きの最小倀を衚の䞭からピックアップする関数ずなりたす。䜿い方もsumifsず同じ匕数のずり方になるので、難しくは有りたせん。

構文は以䞋の通りです。

怜玢範囲の䞭から最小倀を探したすが、その為の条件の倀が入っおる条件範囲を指定し、その䞭で条件倀に合臎するものを匕っ匵っおきたす。耇数条件を加える事が可胜なので、これたでのような耇雑なロゞックやナヌザ定矩関数で実珟しおいたような苊劎は必芁なくなりたした。

図今たで実装攟眮されおたのが謎その

TEXTJOIN関数

これたで長い間、文字列を連結する関数はCONCANTINATE関数しかなく、たたアンパサンド(&)を䜿った結合など、非垞に冗長な䞊に䜿いにくい状態でしたが、Office2016以降に぀いおは、今回のTEXTJOINやCONCATなどの文字列連結関数が充実した為、これらの問題がだいぶ解消したした。TEXTJOIN関数は文字ず文字ずの間の匕甚笊䟋えばハむフンやカンマなどを加えながら連結しおくれる非垞に䟿利な関数です。

構文は以䞋の通りです。

最初に匕甚笊を指定今回はハむフン、その埌空癜セルを無芖するか吊かを指定、その埌は連結するセルを列挙するだけで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関数などが次々実装予定です。

Microsoft365のExcelで䜿える関数は知らないず損をする

FILTER関数

今回の365専甚関数の䞭でも最も利甚䟡倀の高い関数で、基本はGoogleスプレッドシヌトのFILTER関数ず同じ。指定した範囲に察しお様々なタむプの抜出条件で刀定した結果のレコヌドの塊を返しおくれるスピル関数です。

この条件刀定が基本は列に察しお行い、その刀定で別の関数を利甚したり、たた特定の列だけ結果ずしお返したり、結果を別の関数に食わせお䞞ごず蚈算させたりず、ダむナミックな蚈算が可胜になっおいたす。基本的な数匏のスタむルは以䞋の通りです。

条件は耇数付ける事が可胜であり、オヌトフィルタで人間がごちゃごちゃ操䜜するのではなく、関数で機械的に操䜜が可胜。ただし、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を䜿わずずも可胜になっおいたす今たでコレだけの為にバッドノりハりずしおピボットテヌブル䜿っおいたようなダサい事䟋も芋おきおたす

指定する数匏のスタむルは以䞋の通り。

通垞は列だけを指定したすが、耇数列範囲を指定した堎合には、その぀の列で持っおナニヌクな倀ずしお抜出しおくれるので、組み合わせの䞀芧を取り出す事も可胜です。たた、この関数で抜出したデヌタをsumifなどの関数に食わせる時に入れ子ではなく、G2がその䞀芧ならばG2#ずする事で条件範囲ずしお䜿えたす。これをスピル範囲挔算子ず呌びたす。本来単䞀の答えしか返せないSUMIFS関数で䜿うず、耇数の蚈算結果ずしお返しおくれるようになりたす。

※=SUMIF(D2:D21,UNIQUE(D2:D21),E2:E21)ずいった入れ子を䜿わなくおも枈むのが、スピル範囲挔算子の良い点

図地味に利䟿性の高い関数です

SORT・SORTBY関数

オヌトフィルタは䟿利ではあるのですが、この時ID列などが無いデヌタで゜ヌトを掛けおしたうず、元の順番に戻すのが困難になっおしたいたす。そういったケヌスも含めお指定の範囲内のデヌタに察しお゜ヌトを掛けた結果を返しおくれる関数がSORT関数で、さらに耇数の゜ヌト条件に察応させたのがSORTBY関数になりたす。オリゞナルのデヌタは汚さずに゜ヌトを掛けお二次利甚可胜になるため、いちいちシヌトごずに同じマスタデヌタをメンテナンスなんお事も䞍芁になりたす。

数匏のスタむルは以䞋のような感じになりたす。

䞡者の関数䌌おるようで、ちょっず匕数の指定方法が異なるので芁泚意です。-1で降順ずなり1で昇順ずなるずいうオプション指定がちょっずオカシむ点も芁泚意。SORT関数の堎合は列番号で゜ヌトする列を指定したすが、SORTBY関数の堎合は範囲で゜ヌトする列を指定する点が異なるのが困った点です。

SORTBY関数の堎合はさらに、゜ヌトを掛ける順番ずいうものがあるので、掛ける順番を間違えるず党く異なる結果が返っおくるこずになるので、よく芋極めお匕数指定をする必芁がありたす。

図゜ヌトするだけずは蚀え、結構䜿い所倚し

XLOOKUP関数

FILTER関数に続いお非垞に重芁な関数が装備されたした。これたで、vlookup関数で苊しんできた瞊暪を瞊暪無尜に怜玢しお倀を持っおくるテクニックずしお、index + match関数がありたしたが、Microsoft365専甚関数ずしお「XLOOKUP」関数が装備されたした。Googleスプレッドシヌトにも最近装備がなされたした。

この関数の特城は

  1. vlookupず異なる指定の列の巊偎も怜玢察象にする事が可胜だからずいっお、ID列などを適圓な列に眮いお良いずいうわけではない⇒総務省統䞀ルヌル参照
  2. index +matchのような耇雑な瞊暪の座暙特定が必芁ないので関数の芋通しが良い
  3. 返り倀がない堎合のデフォルト倀を指定出来るiferror関数が䞍芁
  4. 怜玢結果の衚瀺察象の列指定は、列番号での指定ではなく、範囲での指定になっおいる。
  5. 暙準でスピル察応しおるため、個の倀ではなくそのレコヌドに含たれる別の列の倀もたずめお持っおこれる単玔に答えを返す範囲を耇数列範囲で指定するだけ

数匏の指定スタむルずしおは以䞋のような圢になりたす。

これたでの珟堎でのバッドノりハり的な郚分や゚ラヌ時の察応、その他諞々痒いずころに手が届く関数の仕様ずなっおいる為、今埌倀のルックアップ系はvlookupやindex+matchではなく、xlookupが䞻圹になるず思われたす。

※vlookupず違い、空欄に察する挙動が以䞋のように異なるので泚意

  • VLOOKUP空欄は無芖しお#N/A
  • XLOOKUP空欄は空欄に䞀臎する

図長幎苊しめられた苊行から開攟される

テヌブルずPowerQuery

テヌブルの掻甚

抂芁

未だに事務員レベルで掻甚されおいない代衚䟋の機胜がこの「テヌブル」機胜。基本的には事務の珟堎で管理垳祚類を䜜っお業務で利甚するのであれば、デヌタは「テヌブル化」すべきなのですが、問題なのは、事務員レベルでは「テヌブル化」する意味を理解出来おいないケヌスが倚々ありたす。結果、原始的な垳祚で知った範囲での関数頌りの問題の倚いファむルが䜿われ、結果業務の煩雑化ずミスの誘発を招いおいる状態です。歎史は叀く、2003からリストずしお装備され、2007からはテヌブルず呌ばれお実装されおる機胜です。

Excelではテヌブルずク゚リを掻甚するず楜になる

テヌブル化のメリットは

  1. 関数の単玔化特にvlookupなどでは範囲の固定などが䞍芁になる
  2. デヌタ範囲の自動拡倧これは参照しおる別のシヌトからも範囲で指定が䞍芁なので、いちいち手盎しが必芁なくなる
  3. PowerQueryで掻甚出来るようになる

があげられたす。いい加枛、手䜜業での範囲手盎し、関数手盎し、ピボットテヌブルからは卒業しおテヌブルを掻甚したしょう。これらが蚱されるのは新入瀟員たでです。

テヌブル化

デヌタの塊をテヌブル化する手順は簡単です。以䞋の手順でテヌブル化し、テヌブルに名前を付け、できればク゚リ化もしおおきたしょう。

  1. タむトル行たで含めおデヌタの塊党範囲を範囲指定する
  2. 挿入タブ -> テヌブルをクリック
  3. 先頭行をテヌブルの芋出しずしお䜿甚するにチェックを入れお、OKをクリック
  4. デザむンタブが開かれるので、巊䞊のテヌブル名にわかりやすい名前を付けおおく

できれば、続けお以䞋の䜜業を行いこのテヌブルをク゚リ化しおおいお、PowerQueryで利甚可胜な状態にしおおく。

  1. タむトル行たで含めおテヌブルのデヌタを党郚範囲指定する
  2. デヌタタブを開き、「テヌブルたたは範囲から」をクリックする。
  3. PowerQueryが開かれるので、プロパティに適圓なク゚リ名を入れる。䜕か倉曎を加えたら、䞋の適甚したステップが自動で远加されおいくので、こちらもわかりやすいように「ステップ名」を入れおいく。
  4. そのたた閉じお、保持をクリック
  5. 新しくシヌトが远加されおデヌタが反映されるが、そのシヌトは䞍芁なので削陀しおしたう。

これで、PowerQueryで利甚可胜なク゚リ化が完了です。このデヌタは再線集したい堎合は、ク゚リず接続を開くず䞀芧が出おくるので、そちらをダブルクリックすればOKです。

図テヌブル化は業務で利甚は必須の䜜業

図ク゚リ化しおおく

関数の単玔化

未だ倚いのが、結構なデヌタ量にもかかわらず、デヌタの远加の床に数匏を手盎し察象範囲の拡倧、数匏を新たな行に远加などの手䜜業をしおいる人がいたす。䟋えば、vlookupなどでは、参照先の範囲に新たなデヌタが远加された堎合、圓たり前ですが関数の怜玢範囲に぀いお「手盎し」が発生したす。たた、参照元にデヌタ行を远加した堎合、圓たり前ですが、vlookupの数匏を「曞き足し」しなければなりたせん。

たたこの時、「参照先の範囲をきちんず固定」せずに、vlookup結果がズレおいくずいった臎呜的ミスをした経隓をしたこずがない人はいないでしょう。

テヌブル化しおいる堎合は参照範囲は「テヌブル名」ずなるので、参照先もテヌブル化しおおけば手盎しが䞍芁です。その結果、参照先範囲の固定も䞍芁です。この時の参照元でのvlookupの数匏は以䞋のような構造化参照ずなりたす。党行の数匏が同じ数匏ずなりたす。

䞊蚘の数匏を芋おもらえればわかるように、A2ずいった範囲指定はなくテヌブルの列の指定、そしお怜玢先もテヌブル化しおいるため、範囲ではなくテヌブル名での指定ずなっおいたす。よっお、参照先のテヌブルデヌタが増えおも、たた地域コヌドの列を移動させおも数匏の倉曎は必芁有りたせん。

図匏が単玔化され手盎し䞍芁ずなる

デヌタ範囲の自動拡倧

テヌブルで右クリック -> 挿入 -> テヌブルの行䞋を実行するず、新たなテヌブル行が远加されテヌブル範囲も自動で拡倧、関数も自動で補完されたす。よっお、ナヌザがデヌタを远加時に、そのテヌブル内で新たに関数を入力したり、たた、このテヌブルを参照しおる別の匏偎で数匏の手盎しは䞍芁です。

※単玔にテヌブルの䞀番䞋にデヌタを入力しおもテヌブル範囲は自動で拡匵されたすが、基本は䞊蚘の手順で行いたす。

䞀番䞋の行に曞いおも自動拡匵されない堎合はオヌトコレクトの蚭定がオフになっおるので以䞋の手順でオンにしおおくず良いでしょう。

  1. Excelを起動しお、ファむル -> オプションを開く
  2. 文曞構成 -> オヌトコレクトのオプションを開く
  3. テヌブルに新しい列ず行を含めるのチェックをオンにする

これで、行列に盎接蚘述しおもテヌブルが自動で拡匵するようになるので、デヌタの远加などが容易くなりたす。

図新たな行远加でも関数は補完される

図オヌトコレクトのオプション蚭定

プログラムからの操䜜が簡単になる

テヌブルおよびPowerQueryを䜿うこずによっお、これたでならば様々な凊理を自前でVBAやVBScriptにお実装しおいたものを装備する必芁性が無くなりたす。Access VBA等の堎合にはExcelず違っおコヌドの量がぐっず䜎いのは、ク゚リずいう機胜で予めデヌタを凊理しおおける為です。Excelに斌いおもPower Queryで予め凊理をさせおおいた結果に察しおだけ、VBAやVBSで凊理をする事で、コヌド自䜓曞かずに枈むだけでなく、Rangeの操䜜ではなくTableに察しおの操䜜は非垞に簡玠なメ゜ッドのみで行えるようになるため、メリットが非垞に倧きいです。

VBScriptにおテヌブルやPower Queryを操䜜する実䟋に぀いおは以䞋の゚ントリヌで蚘述しおいたすので、参考にしおみおください。非垞に短いコヌドで盎感的に操䜜が可胜になるため、積極的にテヌブルやPowerQueryを䜿っおいきたしょう。

Excel自動化ならばPythonよりもVBSを䜿いたしょう

名前付き範囲リスト

Excelに埓来よりある「名前付き範囲」ずテヌブルを組み合わせる事で、ドロップダりンリストを自動拡匵する事が可胜です。埓来のドロップダりンリストの堎合、リスト項目を増やした堎合、それを甚いおるドロップダりンリスト偎でも「範囲の修正」を行わなければなりたせん。しかし、テヌブルず名前付き範囲を䜵甚した堎合、このドロップダりンリストは自動的に範囲が拡匵されるので、手盎し自䜓が䞍芁になりたす。

以䞋の手順で䜜成したす。

  1. ドロップダりンリストの元になるデヌタはテヌブル化しおおく
  2. そのテヌブルのうち、ドロップダりンリストで䜿う範囲をタむトル行含めお指定する
  3. 「数匏タブ」を開き、定矩された名前 -> 遞択範囲から䜜成をクリック
  4. 䞊端行にチェックが入っおるのでこのたたOKをクリックするず、タむトル列が名前付き範囲の名前ずなる
  5. 名前の管理から䜜成した名前付き範囲は色々ず管理線集が可胜
  6. 次にドロップダりンを蚭眮したいセルを遞び、デヌタタブ -> デヌタの入力芏則をクリック -> デヌタの入力芏則を開く
  7. 入力倀の皮類は「リスト」を遞択
  8. 元の倀は、=蚘号を぀けたら続けお、4.の名前付き範囲の名前を入れるF3キヌで名前が出おくる
  9. 名前付き範囲がリストずなる。この状態でテヌブルに新しくデヌタ行を远加するず、名前付き範囲も自動で拡匵されるため、ドロップダりンリストの内容も拡匵されたす。

テヌブルを掻甚する事でこのように無駄な䜜業を省力化できるだけでなく、メンテナンスフリヌになりミスも枛らせる為、積極的にテヌブルは掻甚すべきずいう良い事䟋です。

図名前付き範囲の管理画面

図リスト範囲は名前付き範囲ずする

PowerQueryでやるべき事

Excel2013時代にはアドむンずしお、2016からは暙準機胜ずしお搭茉され、珟圚Power BIにも搭茉されおる高床なデヌタの加工を関数ではなく、Accessのク゚リのように行える匷力なツヌルですが、事務の珟堎での利甚床は非垞に䜎い印象です。しかし、このツヌルを掻甚しないのはもはや怠慢ずも蚀えたす。い぀たでも関数頌り、ピボットテヌブル頌りの非生産的なデヌタ管理はせず、卒業しおPower Queryを利甚したしょう。

Power BIでデヌタ解析を芖芚化する 掻甚線

マトリクス倉換

デヌタで頂戎っお蚀ったら、出された゚クセルデヌタがいわゆるリスト衚ではなく集蚈された「マトリックス衚」だったこずが䜕床もありたす。人間にずっおは集蚈されお瞊暪のクロス集蚈は芋やすいのかもしれたせんが、プログラムで扱うデヌタずしおは非垞によろしくありたせん。しかもその衚は元デヌタがあっお䜜られたピボットテヌブルではなく、sumifsなどで加工されたものだったりするわけです。

これをプログラムで䜿いやすいリスト衚に倉換する必芁がありたすがそのためにVBAを曞くのも面倒。ずいうこずでPower Queryには「列のピボット解陀」ずいう玠晎らしい機胜がありたす。今回のサンプルにはe-Statの昭和59幎郜道府県別収入統蚈のデヌタを入れおありたすが、これを本来の生デヌタであるリスト衚に倉換しおみたいず思いたす。

  1. 事前にデヌタはテヌブル化し、ク゚リ化しおおきたすサンプルはこの凊理は終わっおたす⇒ 郜道府県別収入シヌトが元デヌタになりたす
  2. デヌタタブのク゚リず接続をクリック
  3. 収入1984元デヌタずいうク゚リを開きたす。
  4. ピボット解陀をする列を遞択。今回の堎合、倀ずなる集蚈䞖垯数から右偎の党郚の列を遞択したす。
  5. 倉換⇒列のピボット解陀をクリックする
  6. タむトル行は属性に代わり、数倀は倀ずなっおリスト衚ずなりたす。
  7. 適圓にステップに名前を぀けおおく
  8. ファむル⇒閉じお次に読み蟌むをクリックする
  9. 新芏ワヌクシヌトを遞び、OKをクリックするず倉換されたデヌタがそのシヌトにテヌブルずしお曞き出される⇒ピボット解陀シヌトが曞き出したテヌブルになりたす

出力デヌタはテヌブルそのものなので、元デヌタの郜道府県別収入シヌトにデヌタが远加された堎合、右クリック⇒曎新するだけでピボット解陀シヌトのデヌタも曞き換わりたす。

図ピボット解陀する列の指定

図リスト衚になったデヌタ

単玔集蚈

Accessなどではおなじみの元デヌタを特定の列でグルヌピングしお、倀の集蚈を取る集蚈衚集蚈ク゚リ。これたでのExcelでは実はこの単玔な集蚈衚は䜜成するのが非垞に面倒であったのが、Power Queryを䜿った堎合簡単に実珟可胜になる。わざわざナニヌクな倀を別に切り出しおから、sumifs関数でゎリゎリ曞くなんお「ダサい」真䌌をする必芁もない。圓然デヌタの远加があっおも、元がテヌブルなので修正する必芁性もありたせん。

郜道府県別収入シヌトのク゚リを元に、「幎収階玚で平均」のデヌタを陀倖しお、「幎間収入千円」の合蚈を、五倧郜垂圏だけ切り出すずいうものを䜜っおみたす。

  1. 郜道府県別収入シヌトのク゚リ収入1984 元デヌタを開く
  2. たず、幎収階玚の「▌」をクリックしお、平均を取り陀くフィルタヌされた行ずいうステップが远加される
  3. 次に地域の「▌」をクリックしお、北海道、東京、愛知、倧阪、犏岡の5倧郜垂圏のみを衚瀺する
  4. 幎収階玚の列を削陀する削陀された列ずいうステップが远加される
  5. 地域コヌドず地域名の列だけ遞択し、倉換タブのグルヌプ化をクリック
  6. ダむアログにお、操䜜は「合蚈」ずし、列は「幎間収入千円」を遞ぶ。新しい列名は「収入総合蚈」ずでも付けおおき、OKをクリックする
  7. グルヌプ化された行ずいうステップが远加されるので、適圓に名前を倉えおおく
  8. そのたたPower Queryを閉じお、保持をクリックするず、新しいシヌトにデヌタが吐き出される。⇒五倧郜垂圏シヌトがこの䜜業を行ったもの

実際に生デヌタを手蚈算で怜算しおみるずわかりたすが、平均の倀をきちんず陀去した状態で、五倧郜垂圏の幎間の収入倀が算出出来おいたす。Power Queryを䜿う事で぀たり「この䜜業を自動化」出来おいるずいう事です。オヌトフィルタや切り貌り、関数だけでは到底出来ない「手順の自動化」が出来るのがPower Queryの匷みです。

図グルヌピングで重耇列の倀をたずめる

図ク゚リず接続を右クリックからでも曞き出しは可胜

カりントず合蚈を1床に行う

単玔なデヌタの衚があった時に、そのデヌタに斌いおIDを基準ずした個数のカりントおよび各IDにある数倀の合蚈を同時に行いたい堎合がありたす。ID毎にグルヌプ化しお衚瀺する必芁があり、これもAccessのク゚リではよく䜜るパタヌンです。

これをPower Queryで行いたい堎合には前述の単玔集蚈で同時に集蚈列を2぀远加するだけで可胜です。

  1. ID列を遞択しおグルヌプ化をクリック
  2. 新しい列で「カりント」ず呜名し、操䜜は行数のカりントずするこれでID毎のカりント数が取れる
  3. 集蚈の远加をクリックする
  4. 新しい列で「合蚈」ず呜名し、操䜜は合蚈ずし、列に金額を指定する
  5. OKをクリックするず1぀のデヌタからカりントず金額合蚈の2぀の列を持っおグルヌプ化した集蚈

個別に䜜る事なく1床に䜜成出来おしたうので、耇雑に考えなくおも䜜れおしたうのがPower Queryの良い点です。

図1デヌタからカりントず合蚈の2぀を取る

図぀集蚈列を远加する

ファむル接続

事務珟堎のExcelでよく芋かけるものの぀ずしお「ファむル間リンク」がありたす。かなり昔からある機胜で、他のExcelファむルのデヌタ矀を参照しお、デヌタを取っおくる機胜なのですが、そもそもメンテナンス性が非垞に悪い機胜で、Accessのク゚リずは異なり参照先のデヌタ矀ずいうより、特定のセルの蚈算結果を個取っおくるだけでも䜿われる為、䞀䜓どこのセルがリンクされおるのかたた、もう利甚しおいないのに残っおるなんおケヌスもありたす。

そろそろこういった管理は卒業し、Power Queryでデヌタ゜ヌスずしお自身のExcelファむルに持っおきおから䜜業をするべきでしょう。今回はAccessのデヌタベヌスからデヌタを取り出しおみようず思いたす。

  1. デヌタタブの「デヌタの取埗」⇒「デヌタベヌスから」⇒「Microsoft Accessデヌタベヌスから」をクリック
  2. 目的のaccdbファむルを遞択する
  3. ナビゲヌタヌが出お、テヌブル䞀芧が出おくるので、テヌブルを遞び、読み蟌みの▌をクリックしお、読み蟌み先をクリック
  4. テヌブル⇒新芏ワヌクシヌトを遞ぶずテヌブルにおExcelにデヌタが曞き出されたす。この時点でテヌブル化ク゚リ化がなされおいたす
  5. テヌブルを右クリック⇒テヌブル⇒ク゚リの線集をクリック -> 他のファむル接続シヌトが出力したデヌタのシヌトになりたす
  6. デヌタ゜ヌスの蚭定をクリックするず、このExcelで利甚されおる倖郚ファむルの䞀芧が出おきたす。

これを元にすれば耇数のデヌタ゜ヌスを埌に玹介する他のク゚リずの結合で利甚すれば、vlookupなど䜿わずに耇数ファむルを簡単に結合可胜になりたす。

図利甚するテヌブルを遞ぶ

図デヌタ゜ヌスの倉曎もここから可胜

デヌタ゜ヌスを盞察パスに倉曎する

自分個人で利甚する分には、デヌタ゜ヌスのファむル等もそうそう移動する事がない為、いわゆる絶察パスで指定したたたでも問題はありたせんずいうよりも、Power Query自䜓は絶察パスでないずデヌタにアクセス出来ない。しかし、第䞉者ず共有するケヌスの堎合Box Drive等で利甚する堎合も含む、そのデヌタ゜ヌスのファむルの絶察パスは必ずしも自分ず盞手ずで同じずは限りたせん。これは叀い機胜であるブック間リンクでも同じこずが蚀えたす。

そこで、デヌタ゜ヌスの指定に぀いおは、絶察パスではなく盞察パスに倉曎しおおく事で、他のナヌザが利甚した堎合であっおも、問題なくデヌタ゜ヌスに接続出来るようにしおおく事が可胜です盞察パスであればデヌタの曎新をしおも゚ラヌになりたせん。

以䞋はExcelのファむルずデヌタ゜ヌスのAccessファむルが同じフォルダに有るケヌスを想定しお、䜜っおいたす。

  1. Excelの適圓なシヌト今回はexcelpathずしたのB1セルをクリックする
  2. 以䞋の数匏を入れるずそのブックのフォルダたでの絶察パスが蚘述される
  3. B1セルを遞択した状態で、数匏タブ⇒名前の定矩を開く
  4. 名前に適圓な名前をセットする今回はexcelmanずしたした
  5. デヌタ゜ヌスず接続しおるテヌブルを右クリック⇒テヌブル⇒ク゚リの線集でPower Queryを起動する
  6. デヌタ゜ヌス蚭定ではなく、ク゚リにある「詳现゚ディタヌ」をクリック
  7. ゜ヌス=ずいった蚭定内容が出おきたす。file.Contents以䞋に絶察パスが蚘述されおいるので、ここを以䞋のような圢で曞き換えたす。
  8. ファむルパスには4.のパスが入り、゜ヌスではそれに加えお゜ヌスのファむル名を&で぀なげたものを倉数ずしおFile.Contentsに指定したす。
  9. 完了をクリックしお閉じる
  10. Power Queryも閉じお保持し、䞊曞き保存しお䞀旊、Excelを閉じる
  11. Excelファむルのあるフォルダのフォルダ名を倉曎しおみお、Excelを再床起動しおみる
  12. 再床、デヌタ゜ヌスず接続しおるテヌブルを右クリック⇒曎新を実行しお゚ラヌがでなければ成功です。

ファむル毎にこのような凊理をしおおく事で、同じフォルダ内にデヌタ゜ヌスファむルがある限り、絶察パスが異なっおいおも問題なくデヌタ゜ヌスにアクセス出来るようになるので、たずファむルを䜜っおPower Queryを䜿った堎合には、デヌタ゜ヌスはこのように曞き盎しおおくず良いでしょう。

※たた、䞊蚘の2.におけるパスからBox盎䞋たでのパスに削りたいケヌスが倚々ありたす。出おきたパスから盎䞋たで削るには以䞋のような数匏で別に結果を出し、そちらに名前付き範囲を蚭定しお、゜ヌスで利甚するようにしたら、応甚範囲が広がりたすそのファむルがいる堎所だけじゃなく、Box内にあちこち散らばっおおも、問題がなくなる

日付が毎日倉動するようなものであるならば、「=TEXT(B2,"yyyymmdd")」ずいった数匏で「20220826」ずいった日付を取り出しお䞊蚘のフルパスに結合しおみたりなど、この蟺りは応甚次第でかなり自由にファむルの接続ができるようになるのでおすすめです。

図絶察パスでないず受け付けない

図珟圚のパスを取る数匏をセットするのが肝

図これで起動する床にフルパスを取埗し盎したす

指定の期間でデヌタを絞り蟌む

前述の盞察パスにするずいうテクニックでは、名前付き範囲の倀をもっお、゜ヌスを動的に倉曎する事が出来たした。同様の仕組みを利甚しおセルに蚘述された日付を持っお、フィルタをする事が可胜です。M蚀語で䜜る必芁がありたす。

  1. セルの開始日付の堎所を名前付き範囲ずしおそれぞれ、開始日付(startdate)、終了日付(enddate)を定矩する
  2. 元デヌタの幎月日列の日付の圢匏は今回は yyyy/MM/dd hh:mm:ssの圢匏になっおいたす
  3. Power Queryで元デヌタを開き、詳现゚ディタを開く。
  4. 適圓に幎月日列の倀を「指定の倀の間」におフィルタする

    図適圓に日付でフィルタする
  5. 以䞋のような圢で、名前付き範囲の倀を取埗しフィルタをする今回は、Datetime.Fromを䜿いたす。yyyy/mm/ddの堎合はDate.Fromを利甚したす
  6. 完了をクリックするず、セルの倀でもっお、デヌタが期間指定でフィルタされる

セルの倀を曞き換えお、テヌブルを右クリック⇒曎新をするず元デヌタから絞り蟌みが実行されお、指定期間内のデヌタだけにフィルタされお衚瀺されるようになりたす。

※この方法の他に、開始日付ず終了日付のセルを単独でク゚リ化⇒パラメヌタ化しお同様の事を行う方法もありたす。

図無事にセルの倀で動的フィルタが出来るようになった

クロス集蚈

珟堎のファむルを芋おいお結構芋かけるものの䞀぀に「ピボットテヌブルでクロス集蚈した結果に、手動で䜜った衚をくっ぀けお管理祚を䜜る」ずいうバッドノりハりを芋かけるこずがありたす。コレ最悪なのが

  1. ピボットテヌブルのデヌタず䞀䜓化しおるわけじゃない
  2. ピボットテヌブル郚分は本来getpivotdata関数で構成されおる為、容易に取り出しが出来ない
  3. 継ぎ足しされおるデヌタはピボットテヌブルのデヌタ元の瞊暪拡匵が党く考慮されおいないただただ結果に足しおるだけずいう・・・
  4. そもそもピボットテヌブルは「芋せる資料の為のクロス集蚈」デヌタなのであっお、「管理垳祚で二次利甚する為のクロス集蚈」では無い

ずいう事を本人が党く理解出来おいない点。結果、垞に手動でのメンテナンスを必芁ずしおるだけでなく、クロス集蚈列項目が増えた堎合デヌタが砎壊されるこずになる。たたその為にオカシナ「列項目は増やしおはならない」ずいう党く持っお理解䞍胜なロヌカル運甚ルヌルが付け足されおたりする始末の悪いデヌタです。そのルヌルのために事前にデヌタを消すずいったしょうもない䜜業が手順曞に曞いおあったりするのがありたす

管理垳祚で二次利甚する事を目的ずしたクロス集蚈はPower Queryで䜜成するのが定石です。

  1. 今回はピボット解陀のシヌトデヌタを党遞択し、デヌタタブ -> テヌブルたたは範囲からをクリックし、新しいク゚リを䜜る
  2. 瞊および暪に取りたい列ず、倀の列だけを残しお列を削陀しおおく。
  3. 属性列暪に取りたい列を遞択し、倉換タブの䞭にある「列のピボット」をクリックする
  4. 倀列の指定のダむアログが出おくるので、「倀」ずいう列を指定しおOKをクリックする。
  5. Power Queryを閉じお保持をクリック

結果的にはもずもずクロス集蚈されおる郜道府県別収入シヌトず同じようなテヌブルが生成されたすが、こうするこずによっお、デヌタの増枛に察応できるだけでなく、メンテナンスフリヌになりたす。テヌブルなので他のシヌトからの参照も非垞に容易で、Power Query䞊で蚈算列を远加したり、グルヌピングも行えるわけで、ピボットテヌブルに無理やりデヌタをくっ぀けるずいったダサいこずをやらず、スマヌトにデヌタを衚瀺する事が可胜です。

図クロス集蚈する為のダむアログが出おくる

図テヌブルなので二次利甚が容易

他のク゚リず結合

通垞、Excelで぀のテヌブルの間でデヌタを繋いでいく堎合、぀のテヌブルに共通のIDを持たせお、列ず぀vlookupで参照させお぀のレコヌドを構築するような事をこれたでやっおきた人は倚いかず思いたす。列の数だけvlookupで匏を構築するずいう手間は地味に面倒です。Accessの堎合はリレヌションシップで耇数のテヌブルを連結しお぀のク゚リを䜜るなど日垞茶飯事で、この有甚性が分かっおるので、Excelは䜿わないずいう人も倚いです。

Excelにも叀い機胜のリレヌションシップの管理ずいう機胜があるのですが、これで䜜った堎合結果がピボットテヌブルで生成されおしたうので利䟿性が非垞に悪いです。そこでPower Queryのク゚リのマヌゞずいう機胜で結合させお぀のテヌブルにするのがベストですvlookup関数を䜿う必芁がなく、耇数列を持っおこれたす。

  1. 結合元郜道府県別収入䞀芧ず結合先人口動態統蚈 -> birth2020ず呜名しおありたすの぀のテヌブルを甚意し、いずれもク゚リ化しおおきたす。
  2. 結合元ク゚リを開き、ホヌムタブの「ク゚リのマヌゞ」の▌をクリック -> 新芏ずしおク゚リをマヌゞをクリック
  3. 䞋のプルダりンにお、birth2020を遞択するこれが結合先
  4. 結合元ず結合先、それぞれで「地域コヌド」列を遞択する
  5. 結合の皮類はデフォルトの「巊倖郚」や「内郚䞀臎する行のみ」を遞択する。今回は埌者を遞択。
  6. OKをクリックするず、結合元の右端に「Table」ず曞かれた列が出珟する
  7. その列の矢印の぀いたボタンをクリックしお、衚瀺する結合先の列を遞択する今回は総数、男子、女子のみ遞びたした
  8. OKをクリックするず、地域コヌドで連結されお、birth2020のデヌタが呌び出されお結合されたす。
  9. Power Queryを閉じお、保持をクリック
  10. 新芏ワヌクシヌトに結合したテヌブルずしお衚瀺される -> 結合テヌブルずいうシヌトがその出力シヌトになる

vlookupずいう関数に頌らず、たたデヌタベヌス特有の内郚結合、倖郚結合ずいった぀のテヌブルをリレヌションで結合するずいうvlookupでは出来ない高床なテクニックが簡単に実珟出来たすAccessの䜿い手ならこの蟺りは普段からやっおる事なので新鮮味がないですが

※結合の皮類で右反や巊反を䜿うこずで、どちらかのテヌブルを基準ずした「䞍䞀臎ク゚リ」を䜜るこずも可胜。䞍䞀臎のものだけを取り出せるたす。

図結合する列の遞択ず皮類を遞ぶ

図結合先の衚瀺する列を遞択

蚈算列の远加

Power Queryで商品単䟡列ず数量列があり、これらを蚈算した結果を衚瀺する「合蚈列」を䜜りたいシヌンがたくさんありたす。元のテヌブルにはそのような列がないので、Power Query䞊で䜜成し、以䞋のように蚈算させお列を远加したす。

  1. 予めク゚リ化しおおいたテヌブルをPower Queryで開きたす。
  2. 列の远加タブ内にある「カスタム列」をクリック
  3. 列名に「合蚈」ず名付けお、カスタム列の匏には、参照できる列から遞び぀぀、数匏を構築したす
  4. OKをクリックするず蚈算列が远加されたす。

図カスタム列で蚈算匏を組んで名前を付ける

埩数テヌブルを䞀枚に

顧客毎に同じ圢匏のExcelデヌタをそれぞれの顧客ず共有しおいるケヌスで、実際に業務の凊理の堎合は、枚の別のExcelに郜床コピペしおから䜜業するなんおケヌスを芋かけたす。しかし、このコピペずいう䜜業が実に無駄であり、たたこういった䜜業をRPAで組むのは愚策です。Accessデヌタベヌスの䞖界ではこれら埩数のテヌブルを1枚に合䜓する「ナニオンク゚リ」ずいうものが存圚したすが、Power Queryでも同様の事が可胜です。

  1. 埩数のExcelシヌトの同じ圢匏のデヌタを枚のExcelシヌトに「ファむル接続」におデヌタ゜ヌスずしお远加しおおきたす今回は3枚
  2. これらのシヌトはそれぞれ䌁業1䌁業3ずしおク゚リの名前を振っおいたす。
  3. 倧元の䌁業1ク゚リを開き、ホヌムタブ内にある「ク゚リの远加」をクリック
  4. 今回は3぀あるので、「3぀以䞊のテヌブル」をチェック
  5. 远加のダむアログが出おくるので、接続枈みの利甚可胜テヌブルから必芁なテヌブルをすべお遞択する
  6. OKをクリックし所定の䜍眮にテヌブルで出力するず、合䜓したテヌブルずしお出力される
  7. 元の枚のテヌブルにデヌタを加え、「右クリック」⇒曎新をするず再床合䜓されお最新の状態になる

これで垞に最新の個々のExcelデヌタを参照し぀぀、合䜓したデヌタを元に䜜業をすすめる事になるのでコピペずいう䜜業そのものが䞍芁になりたす。合わせお、すべおを曎新の䞭にある「接続のプロパティ」にお、開く時に自動曎新や○分毎に自動曎新などの蚭定する事で、別途違うアプリから利甚するようなケヌスではテヌブルの曎新䜜業も䞍芁になるので、セットしおおくず良いでしょう。

図ク゚リの远加で埩数テヌブルを遞択する

図合䜓した枚のテヌブルに

䜜業の自動化

Power Queryは画面を芋るずわかりたすが、列を削陀したりカスタム列を远加したり、たたグルヌプ化を実行するず郜床、右偎のパネルにその䜜業毎のステップが远加され、過去のステップを芋るずその時点でのデヌタの状況がわかるようになっおいたす。

぀たり、本来の元デヌタからPower Queryで様々な集蚈や远加を行う䜜業そのものが「自動化」されおいるず蚀えたす。特に本来ならば埩数の䜜業衚を甚意しお行っおいた䜜業をこれらの手順に埓っお結果的に、最終的な答えのク゚リを䜜り䞊げおいるので、途䞭途䞭で必芁な䞭間の衚を別途テヌブルで持぀必芁がありたせん。具䜓的な䟋瀺をするず

  1. 元の衚から特定芁玠でフィルタをする
  2. フィルタした結果、必芁な列以倖を削陀する
  3. 残った列に斌いお特定芁玠でグルヌプ化ず合蚈列を远加する
  4. 最埌に必芁な列以倖すべお削陀する
  5. 4.を元にクロス集蚈する
  6. テヌブルで出力する
  7. 元デヌタが曎新されたら、「曎新」をクリックしお、䞊蚘の1.6.が実行されお最新のデヌタが6.のテヌブルに反映する

ずいった圢です。これたでの衚の堎合は列の削陀などを行っおしたうず䞊蚘の順番通りにならなくなるため、欲しい答えずは違うものになるため、䞭間の衚を別シヌトでたず出しおから、本来の結果シヌトに別途䜜業をしお出すずいった冗長な䜜業を行っおいたした。これらが䞍芁になるため、耇雑な敎圢凊理をノヌコヌドで自動化しおいるず蚀えるため、数匏に頌った手法よりも、効果的で堎合によっおは単玔なマクロやRPAは䞍芁ずなりたす。

図もう䜜成手順を芚える必芁はない

項目別の环蚈衚を䜜る

経理で蚀えば商品別の商品有高垳や、人事で蚀えば人別の残業時間の环蚈合蚈ずいったように、぀のデヌタの塊に察しお、䞊から順番に加算しおいった环蚈合蚈を付けたい堎合がありたす。単玔な环蚈だけであれば䞊の項目に珟圚の倀の合蚈を出すようにSUM関数で出せば良いですが、項目別ずなるずそう単玔ではありたせん。

以䞋の手順で环蚈の列を远加しおいきたす。今回は瀟員毎の环蚈残業時間合蚈を求めたす。

  1. Power Queryを開いお察象のテヌブルを開く
  2. デヌタは瀟員番号、日付の列で昇順で゜ヌトする
  3. 瀟員番号の列を遞択しお、倉換タブにある「グルヌプ化」をクリック。ダむアログが出るので列名は「瀟員別テヌブル」ずし、操䜜は「すべおの行」ずしお実行

    図瀟員番号でグルヌプ化され他の列が䞀纏めになる
  4. 远加タブを開いお、カスタム列の远加をクリック
  5. ダむアログが衚瀺されるので、蚈算匏は以䞋のような匏を入れお瀟員番号毎日付毎に連番のindexを降るようにする。index察象は3.の瀟員別テヌブルずし、から開始で1ず぀加算ずしたす。むンデックス列の名前はカスタムずしたす。このフィヌルド自䜓はカスタムずいう名前を付けおいたす。

    図むンデックスを人毎に甚意する
  6. 5.のカスタムの右䞊のボタンをクリックし、瀟員番号、瀟員名、日付、残業時間、INDEXにチェックを入れお展開する

    図瀟員番号毎に連番のむンデックスが远加された
  7. 列名がカスタム.列名ずなっおるので、適圓な列名に倉曎しおおく
  8. さらに远加タブを開いお、カスタム列の远加をクリック
  9. ダむアログ出たら以䞋の数匏を入れお、瀟員毎のINDEX毎の残業時間の环蚈をする列を䜜る环蚈残業ずいう名前にしたした

    図List.FirstNにお各行でリストを䜜り、List.Sumでそれらを合蚈する

  10.  出力するのにindex列はいらないので削陀し、閉じお読み蟌むにおシヌト䞊に出力する

これで、既存のテヌブルデヌタに察しお、各行毎䞔぀人毎の环蚈が取れるので、环蚈で掚移のグラフなどを描くのに利甚できたす。

図瀟員別テヌブルは展開しないのがポむント

文字の䞀括眮き換え

膚倧なデヌタを扱っおるず、避けられないのがデヌタクリヌニング。䟋えば党角半角が統䞀されおいなかったり、特定の文字の衚蚘法がデヌタによっお揺らぎが生じおいお統䞀性が無かったりするデヌタを、Excelを開いおいちいちゎリゎリ修正するずいうのは非生産的です。よくあるパタヌンは、党角半角倉換や株匏䌚瀟を(æ ª)に省略する等の䜜業。

これらをそれぞれの関数等でゎリゎリ行うより、䞀括しお倉換甚のテヌブルを甚意しおたずめお凊理したい所。Power Queryでは元デヌタに察しお倉換テヌブルをぶ぀けるこずで䞀括でたずめお文字列の眮換をする事が可胜です。

  1. Power Queryで倉換したい元デヌタを読み蟌む今回は䌚瀟名デヌタ- companyずいう名前にしたした。
  2. たた、䌚瀟名を倉換する為のデヌタも読み蟌むこれが倉換テヌブルになる - okikaeずいう名前にしたした。
  3. companyク゚リを開き、列の远加タブを開き、「カスタム列」を远加する
  4. 列名を眮換埌ず呜名しお、列の匏ずしお以䞋のようなものを入力する

    図List.AccumulateずReplaceで文字を眮き換える
  5. するず倉換した結果が眮換埌列に衚瀺されおるので、このたた閉じお読み蟌むでシヌトに曞き出す

株匏䌚瀟を(æ ª)にし、埌株だろうず前株だろうず倉換したす。同じ理屈で、半角カナず察応する党角カナを登録すれば、カナの半角党角倉換や、英数字などの党角半角倉換なども可胜です。そういった文字の揺らぎを䞀括で倉換するのは関数では手間なので、Power Queryが非垞に掻躍したす。

図倉換テヌブルで䞀括倉換

テヌブルに小蚈を远加する

Excelのテヌブルは非垞に䟿利でメリットが倧きいのですが、1぀匱点ずしお「小蚈」が远加できない点があげられたす。総合蚈の「集蚈行」は機胜ずしお远加が出来るのですが、各項目ごずの「小蚈」はテヌブルではなく、通垞の範囲でなければ远加できない欠点がありたす。

Excel暙準の小蚈機胜行の非衚瀺等は䜿えないですが、テヌブルそのものに各セクション単䜍で小蚈を远加するには、Power Queryを䜿うこずで実珟が可胜です。たたメリットずしお、通垞の小蚈は範囲指定が必芁であるため、デヌタの増枛が合った堎合範囲の修正が必芁ですが、Power Queryの堎合はテヌブルを利甚する事になるので、デヌタの増枛で手修正が䞍芁であり、自動化が出来るのでメリットもありたす。

あずで結合した際に゜ヌトしお、元デヌタの䞊び順に察しお小蚈を入れたいので、支店名に察しお支店コヌドを入れおおきたしょう。

図元デヌタの図

  1. 元になるデヌタをPower Queryで開く
  2. 曎にそのク゚リを巊サむドバヌから右クリック⇒耇補を遞択しおコピヌを䜜る今回はfish集蚈ずいう名前を付けたした

    図たずはク゚リを耇補する
  3. 2.のク゚リにお、今回は支店コヌド、支店名でのグルヌプ化したい列を遞択しお、倉換タブの䞭の「グルヌプ化」をクリック

    図グルヌプ化でたずは集蚈する
  4. ダむアログが出るので、新しい列名を「金額」、操䜜を「合蚈」、列は「金額」を指定したす。列名を金額にする理由はあずで結合する際に、金額合蚈を金額の列に連結するため。
  5. 支店名毎の売䞊合蚈金額が集蚈されるので、次に列の远加タブの䞭の「カスタム列」をクリック
  6. 新しい列名には今回は「適圓な名前」ず入れお、匏には「[支店名] & "小蚈"」ずいった数匏をいれお、OKをクリックこれが小蚈名になる

    図郚門合蚈の列を䜜成する
  7. 支店名の列はもう䞍芁なので遞択しお削陀する
  8. 6で付けた適圓な名前を「支店名」に倉曎する。列名を支店名にする理由は、あずで連結した時に支店名列の所に小蚈名が来るようにする為です。
  9. 䜜成された支店名列を支店コヌドの隣に移動させる
  10. 数匏バヌにあるfxずいうアむコンをクリックする。するず新しいステップが远加されるので゜レを開くカスタム1ずいった名前が぀いおる。数匏は䞊べ替えられた列ずいったものが入っおいるハズです。
  11. 数匏バヌ内に、以䞋の数匏を入力しお、総合蚈の行を䜜成する。支店コヌドに察しお、9999を指定しおる理由は、゜ヌトした時に䞀番䞋に来るようにするためです。

    図総合蚈行が䜜成される
  12. 1.の元デヌタのク゚リを開く
  13. 数匏バヌにあるfxずいうアむコンをクリックする。するず新しいステップが远加されるので゜レを開くカスタム1ずいった名前が぀いおる。数匏は倉曎された型ずいうものが入っおるハズです。
  14. 数匏バヌ内に、以䞋の数匏を入れお2぀のク゚リを結合する

    図぀のク゚リを単玔に結合する
  15.  支店コヌドを昇順で゜ヌト、次に支店名を昇順で゜ヌトする。するず各支店の最期に小蚈が入り、総合蚈の行が䞀番䞋に来るようになる。
  16. 閉じお読み蟌むでシヌトに出力する

これでデヌタの増枛や支店の増枛に察しお自動で各支店毎の小蚈が入るようになるので、以降はメンテナンスフリヌで自動化が可胜になりたす。

図無事に各支店毎の小蚈ず総合蚈が入るようになった

差分比范

これたで出おきた項目に曎にテクニックを加えるこずで、2枚のシヌトの差分のあるセルの倀だけを抜出するずいったような事をPower Queryでやらせるこずもできたす。

  1. 型の倉曎
  2. 倀の眮換
  3. ピボット解陀
  4. むンデックス列の远加
  5. ク゚リのマヌゞ
  6. カスタム列

以䞊の6぀を利甚するこずで可胜になりたす。詳现は以䞋の゚ントリヌに蚘茉しおいたすので、挑戊しおみおください。本゚ントリヌでは型の倉曎ず倀の眮換に぀いおは取り䞊げおいたせんが、これも地味ながら必須のテクニックになりたす。

Power Queryで぀のExcelシヌトの差分を取り出しおみる

UTF8のCSVを取り蟌み

クラりドのシステムから゚クスポヌトしたCSVファむルの倚くは、そのたたダブルクリックするず、Excelが起動し文字化けした状態で開かれお二床手間を取られるこずも倚いです。たた、それらを毎回むンポヌトする䜜業を行うのも生産的じゃありたせん。そこで、毎回同じファむル名で同じ堎所から取り蟌むのであれば、Power Queryであれば文字化けせずに自動化しお取り蟌む事が可胜です。

Power Queryでこの凊理を実珟する堎合は、以䞋の゚ントリヌを参考に実装しおみおください。

ExcelでUTF-8のCSVを文字化けなく取り蟌む

Box䞊で䜿う堎合の察応

Box䞊でPowerQueryにお䜿う䞊で自分が匕っ掛かっお、察凊が必芁であったケヌスに付いお蚘述しおいたす。

デヌタ゜ヌスを盞察パスに倉曎する

Box䞊では、耇数名で共有しお䜿う為、デヌタの曎新を行う堎合にはナヌザ毎に曎新元のファむルのパスが異なりたす。そこで、前述の盞察パスの仕組みを䜿うわけですが、必ずしも曎新元のファむルず曎新先のファむルが同じフォルダ内にあるずは限りたせん。そこで、Box䞊で䞀番最初のルヌトパスを曎新するナヌザ毎に曞き換えるようにしたす。

  1. 前述の盞察パスの仕掛けに察しお、3列目C1セルに根っこのパスを取埗するセルに以䞋の関数を远加する
  2. 前述の関数で「C:\Users\ナヌザ名\」が取埗できるので、このセルに察しお名前付き範囲で「excelman」を蚭眮する
  3. デヌタ゜ヌスず接続しおるテヌブルを右クリック⇒テヌブル⇒ク゚リの線集でPower Queryを起動する
  4. デヌタ゜ヌス蚭定ではなく、ク゚リにある「詳现゚ディタヌ」をクリック
  5. ゜ヌス=ずいった蚭定内容が出おきたす。file.Contents以䞋に絶察パスが蚘述されおいるので、ここを以䞋のような圢で曞き換えたす。

    フルパス以䞋に、Boxフォルダ以䞋のファむルたでのフルパスを残しお蚘述するのがポむントです。
  6. ファむルパスには4.のパスが入り、゜ヌスではそれに加えお゜ヌスのファむル名を&で぀なげたものを倉数ずしおFile.Contentsに指定したす。
  7. 完了をクリックしお閉じる
  8. Power Queryも閉じお保持し、䞊曞き保存しお䞀旊、Excelを閉じる
  9. Excelファむルのあるフォルダのフォルダ名を倉曎しおみお、Excelを再床起動しおみる
  10. 再床、デヌタ゜ヌスず接続しおるテヌブルを右クリック⇒曎新を実行しお゚ラヌがでなければ成功です。

図Boxフォルダ前たでのナヌザ名含めたパスを切り出す

埩数テヌブルを䞀枚に

Box䞊で耇数テヌブルを1枚にする堎合、必ずしも盞手のファむルが同じフォルダ内にあるずは限りたせん。たた埌から新芏に远加されるこずを考慮するず前述の方法ではうたく管理が出来ないです。ずいう事で、以䞋の手順で耇数のテヌブルを1枚にするク゚リを䜜成したす。Excelのケヌスで蚘述したす。

  1. デヌタ⇒デヌタの取埗⇒ブックからを開く
  2. ファむルを指定しお、ナビゲヌタが開かれる。テヌブルを遞択し、読み蟌み先をクリックする
  3. デヌタのむンポヌトではどこかに出力するのではなく、「接続の䜜成のみ」を遞択したす
  4. ク゚リず接続にク゚リが䜜成されたす。これを必芁な枚数分繰り返したす。
  5. 右サむドパネルのク゚リず接続から適圓なク゚リをダブルクリックで開いお、Power Queryを開く
  6. 詳现゚ディタで前述の「盞察パスぞ倉曎」を斜しおおく。コレを枚数分繰り返したす。
  7. 巊パネルの「ク゚リ」をクリックしおパネルを開き、右クリック⇒「新しいク゚リ」⇒「結合」⇒「ク゚リを新芏ク゚リずしお远加」を遞択
  8. 3぀以䞊のテヌブルを遞択し、必芁な接続枈みのク゚リを党お远加する結合ク゚リずいう名称にしたした
  9. 閉じお次に読み蟌むで出力先のセルを指定しお完了
  10. 埌からこの統合ク゚リをシヌトに出力したい堎合には、デヌタ⇒既存の接続⇒結合ク゚リを遞んで開く⇒既存のワヌクシヌトの指定先を遞びOKを抌せばテヌブルずしお出力されたす。

図接続のみでシヌトに出力はしない

図新たに統合圹のク゚リを远加する

VBA入門

事前準備

VBAやナヌザ定矩関数、マクロの蚘録を䜜るためには、事前準備が必芁です。以䞋の䜜業で、Excelに「関数タブ」を衚瀺する必芁性がありたす。

  1. Excelを起動し、ファむル⇒オプションをクリックする
  2. リボンのナヌザ蚭定を開く
  3. 右のパネル、右偎のリボンのナヌザ蚭定偎にある「関数」のチェックを入れる
  4. OKをクリックしお閉じる

関数タブが衚瀺されるので、これでVBAの蚘述やマクロの蚘録が行えるようになりたす。

図デフォルトでは非衚瀺になっおる

図開発タブの内容が衚瀺された

ナヌザ定矩関数

ナヌザ定矩関数ずは、Excelの関数をナヌザが自身でVBAを䜿っおコヌディングし、独自の関数ずしお䜿えるようにする為の機胜です。機胜ずいっおも、VBAでFunctionを定矩するだけなので、通垞のVBAコヌディングずなんら倉わらないです。

メリット・デメリット

通垞は、暙準で搭茉されおいるExcelの関数の組み合わせで殆どの堎合察凊可胜なのですが、問題なのは察凊できるずいう事ず利䟿性は䞀臎しない事。無数の入れ子や耇雑な数匏の繰り返しなどを行った結果、関数が耇雑怪奇になりメンテナンスが困難になるケヌスなどがあげられたす。メリットをたずめるず

  • 耇雑怪奇な入れ子やメンテナンス性の悪い関数頌りの凊理を、芋通しの良いVBAで䜜成できる。
  • 結果、䜜成される数匏は非垞にシンプルになり、数匏の砎壊が行われおも修埩が容易になる。
  • VBAのメ゜ッドを利甚出来るので、関数よりも高床な凊理を行わせる事が可胜になる。

逆にデメリットずしおは

  • ファむルの拡匵子はxlsxではなく、VBAを䜿うのでxlsmにしお保存する必芁がある。
  • 基本、䜜成した関数はアドむン化しない限り、そのブックの䞭だけでしか䜿えない。
  • 僅かなコヌドで䜜れるずはいえ、VBAの知識が必芁になる

しかし、関数で曞くコヌドの量はせいぜいよくお、10数行、必芁ずするVBAの知識もわずかで枈むので、VBA孊習の入門ずしおは最適なものです。

䜜成方法

䜜成方法ずいっおも、ナヌザ定矩関数では耇雑な凊理を構築したりは通垞行わないのでシンプルに䜜成する事が可胜です。以䞋の手順で䜜成したす。今回は偏差倀を算出する独自の関数を䜜っおみようず思いたす。ちなみに、これを暙準関数で䜜る堎合には以䞋の通り。

  1. 開発タブのVisual Basicを開く
  2. 巊のプロゞェクトのパネルで右クリック⇒挿入⇒暙準モゞュヌルを远加する
  3. 右のコヌドを曞くパネルに以䞋のようなコヌドを曞き、ブックを閉じる
  4. xlsx圢匏ではブックを保存出来ないので、名前を付けお保存にお、xlsm圢匏で保存する
  5. これでもうHENSACALC関数が䜿えるようになっおいたす。

今回䜜成した関数を解説しおみたすず

  • ByValが関数の匕数で、範囲指定の堎合 As Rangeを、゜レ以倖はStringやInteger、Variantを付ける型指定
  • 倀を返す必芁があるので、関数名の終わりにもVariantなどの型指定を付ける
  • Application.WorkSheetFunctionでExcel暙準関数をVBAで利甚可胜になりたす。
  • 耇雑な凊理や条件分岐などをここに蚘述し、最埌にHENSACALC = で蚈算結果を返しおあげる

図耇雑な凊理手順も理解しやすくなる

関数ずしお登録

ナヌザ定矩関数は䜜成者は䜿い方を知っおいるので、特にこれ以倖で䜜業は䞍芁なのですが、第䞉者でも䜿えるようにするずいった堎合、どんな匕数が必芁でどんな結果になるのかずいう説明が無いず䜿い勝手が非垞に悪いです。ナヌザ定矩関数は数匏入力䞭にはむンテリセンスが効かないので、暙準関数ず違いどんな匕数を指定すべきかは衚瀺されないのですが、以䞋の䜜業を远加する事で、関数の挿入を䜿った堎合には、詳现な情報を衚瀺させる事が可胜です。

  1. Visual Basic画面を開く
  2. 巊のプロゞェクト画面に「空のクラスモゞュヌル」を䞀個远加する
  3. 2.のクラスモゞュヌルに以䞋の説明文甚の関数を蚘述する名前はClass1ずしお远加する
  4. プロゞェクトのThisworkbookを開く
  5. 以䞋のコヌドを远加するClass1を呌び出しお、Excelが開かれる時に自動でApp_WorkbookOpenがこれで開かれる
  6. 䞊曞き保存しお、Visual Basic画面を閉じる

今回の措眮を远加するこずで、数匏タブ⇒関数の挿入、察象の関数を遞ぶず、App_WorkbookOpenで芏定した内容が衚瀺されるようになり、利甚者が関数の利甚に斌いお迷いが少なくなりたす。この時利甚しおいるApplication.MacroOptionsで指定する内容は色々ありたすが

  • Description - 説明文
  • Macro - 関数名自分で䜜成した関数名ず䞀臎する必芁がある
  • Category - どの関数区分に属するかを指定する無くおも良い
  • ArgumentDescriptios - 匕数に䜕を取るのかずいった内容を配列で指定する
  • HelpFile - ヘルプ時のリンク先URL

基本は䞊蚘の぀を指定するだけで十分です。

図説明文甚の関数ず自動で登録するコヌドを曞く

図説明文が衚瀺されたした

䜿い方

䜜成した関数の䜿い方は、暙準の関数ず倉わらないです。䜆し、暙準関数ず違っお入力䞭の匕数に䜕を入れるかなどは衚瀺されないので、難しい点はそこだけ。今回のHENSACALC関数は、「倀の範囲」「察象の倀」の぀を匕数に取っおいるので

ずいう圢になりたす。C列がいろいろな人の埗点が入っおる倀の範囲でC2がその䞭で察象者の埗点を指定するずいう圢。暙準関数で組み合わせるよりも圧倒的に短く単玔になりたした。

図特に耇雑な条件分岐で力を発揮したす

アドむン化

自䜜した関数は基本的にはそのExcelファむルでしか利甚する事が出来たせん。たた、そのExcelファむルは拡匵子がxlsmの圢匏にする必芁があるため、「他のファむルでも利甚したい」「他の方にも利甚しおもらいたい」ずいう堎合には、毎回Visual Basic画面から関数の䜜成ず登録が必芁になるのは煩雑です。そこで、これらの関数を「アドむン化」するこずで、アドむンを入れおいるPCでは、導入する事で即利甚でき、拡匵子がxlsxのファむルでも動䜜するようになりたす。

アドむン化する手順は以䞋の通り

  1. 関数を䜜ったexcelファむルを開く
  2. 名前を付けお保存を実行
  3. ファむルの皮類を「Excelアドむン*.xlam」圢匏を遞択
  4. なぜか、保存堎所がアドむンのフォルダになっおしたうので、ここで保存先を改めお指定する
  5. xlam圢匏のファむルにはシヌトが存圚せず、たたダブルクリックしおも䜕も開かれたせん。

3.で保存したアドむンを以䞋の導入手順で導入する事で、䜜成した自䜜の関数をそのPCで䜿うすべおのExcelファむルで利甚できたす。アドむンをオフにするず䜿えなくなりたす。よっお、倖郚に配垃するExcelファむルの堎合は、盞手がアドむンを入れおいないず䜿えないので、その堎合は埓来通りのExcelファむルに盎接関数を埋め蟌む必芁があるので泚意。

図xlam圢匏で保存するだけ

アドむンの導入方法は以䞋の通り

  1. Excelを起動する
  2. 開発タブを開き、Excelアドむンをクリックする
  3. アドむンはドキュメントフォルダにでも保存しおおく
  4. 2.で出おきたダむアログに斌いお、参照をクリックする
  5. アドむンがオンになった状態でアドむンが導入されたす。

アドむンの説明文を入れたい堎合には、

  1. Excelのメニュヌからファむル⇒情報⇒プロパティ⇒詳现プロパティを開く
  2. コメント欄に説明文を曞いお、アドむンずしお保存する

ずいった圢になりたす。

図アドむンが導入された

マクロの蚘録

Excel5.0からVBA自䜓は搭茉されおおり、その埌マクロの蚘録も装備されおいたず思うので、マクロの蚘録機胜自䜓は盞圓叀い時代から装備されおいた事になる。元々はVBAでゎリゎリ曞けない事務方の人等が、手軜に操䜜を蚘録しお再珟する、今のRPAそのものであり、RPAなんかより遥か昔から実装されいたものです。

VBAで蚘述されおいるので、マクロ有りのファむルはこれたで同様xlsm圢匏で保存する必芁がありたす。

ナヌザ定矩関数同様、VBAの入門ずしおはたず䜿うべき登竜門であり、ここを足がかりに现かい修正でVBAを䜿っおいくのが王道ず蚀えたす。たた、Excelの自動化であればRPAではなくたず「マクロの蚘録」でしっかり動くように勉匷するほうが、コスト面でも汎甚性でもRPAよりもメリットが倧きいので䜿わないのはもったないず蚀えたす。䜿い方ですが以䞋の通り

  1. 開発タブを開く
  2. マクロの蚘録ボタンをクリックする
  3. マクロ名は分かりやすい名前を付けお、付けたい堎合はショヌトカットキヌも指定する。
  4. OKをクリックするず蚘録がスタヌトする
  5. 蚘録終了をクリックするず蚘録が終了しマクロが保存される
  6. マクロの削陀は、マクロボタンをクリックするず䞀芧が出おくるので、遞んで削陀する

蚘録したマクロの実行は、マクロをクリックしお䞀芧から遞んで「実行」をクリックするこずで再珟される。今回以䞋のようなマクロをmacromanずいう名称ショヌトカットキヌはCtrl+jで蚘録したした。実行するず

  1. 偏差倀シヌトから党デヌタをコピヌ
  2. マクロ曞き蟌み先シヌトをアクティブ
  3. A1に倀で貌り付ける
  4. 3.の党範囲に眫線を远加
  5. タむトル行の色を黄色にし、オヌトフィルタを远加
  6. A2を基準にりィンドり枠を固定を远加

ずいう手順を蚘録しおいたす。䞀瞬ですべおの䜜業が終わるのでわかりにくいですが、このように䜜業を色々蚘録しおいくわけですが、䟋ずしおシヌトの远加などでそこにコピペずなるず毎回新芏远加のシヌト名が異なるので、色々マクロの蚘録をする䞊での癖ずいうものを芋に付けお修正する胜力が必芁になりたす。

たた、今回はCtrl+jでマクロを実行できたすが、ボタンを配眮しおクリックのみで実行したい堎合には

  1. 開発タブを開く
  2. 挿入⇒フォヌムコントロヌル⇒ボタンを遞ぶ
  3. シヌト䞊で適圓な倧きさを指定する
  4. マクロの登録ダむアログが出るので、䜜ったマクロ名今回はmacromanを遞ぶ
  5. OKをクリックするず、ボタンにマクロ実行が割り圓おられる

これで、ボタンひず぀で蚘録したマクロを実行できるようになりたす。

図マクロの蚘録開始画面

図ボタンにマクロを割り圓おる

高床な自動化

珟圚は昔ずは違い、マクロ、VBAだけに限らずPower QueryやPower Automte、RPAずいった別の自動化の手段も出おきおおり、ケヌスに応じお別の手段を利甚するのも良いでしょう。しかし、別の手段はそれだけの為に「远加のコストの支出」を䌎い、たた自分で䜜らないのであるならば、事務員ずしおは単玔に仕事が消えスキルも残らないずいう結末しか埅っおいたせん自動化しお淘汰されるだけ。

故に手軜で远加コストの無いマクロやVBA, VBSを身に぀ける事で、以䞋のような他のアプリケヌションの操䜜や管理アプリの䜜成も可胜になりたす。

VBAで他のアプリケヌションを操䜜する

その他の機胜

アドむン

前述の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 ず Office Scripts を䜿っお Excel 芋積曞のデヌタを 抜出し、SharePoint の列ずしお利甚する
元サむトで動画を芖聎: YouTube.

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に぀いおは、すでに぀ほど゚ントリヌを䜜成しおいる為、どんなものなのかむメヌゞしやすいのではないかず思いたす。

Formsからの投皿でOneDriveのExcel曞き蟌みずカレンダヌ登録

Boxファむル監芖をPower AutomateでExcelに曞蟌み – Webhook v2ç·š

Microsoft Power Automate DesktopでRPAを実珟しおみる

Power Automate Desktopで孊ぶRPAテクニック

Power Automate DesktopでExcelからSQLを䜿っおデヌタを振り分け
元サむトで動画を芖聎: YouTube.

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掻甚の先を行きたいのであれば、孊習は必須ず蚀えるでしょう。

Power BIでデヌタ解析を芖芚化する 䜜成線

Power BIでデヌタ解析を芖芚化する 基瀎線

Power BIでデヌタ解析を芖芚化する 掻甚線

Formsを䜿っお自動でタスク管理

䜕故か未だに圓たり前のようにFAXや電話で業務を続けおいる日本。既に海倖では党廃の動きたで出おいたす囜内でもコヌルセンタヌ廃止する䌁業も出おきおいたす。そこで利甚するのがMicrosoft Forms。䞻に以䞋のような問題を解決したす。

  1. 24時間365日、仕事の䟝頌を匕き受けるこずが可胜
  2. 盞手にわざわざ電話を䜿わせるずいった「匷制的に時間を奪う行為」をさせずに枈む
  3. FAXやOutlookず違い、宛先間違いなどで情報挏えいをする心配が無い
  4. フォヌム内容はExcel Onlineに蚘録でき、そのたたタスク管理・Teams通知・カレンダヌやPlanner登録、リマむンダヌ登録などが可胜
  5. Outlookず異なり、メヌルが埋もれおタスク取りこがしや、結果的にお客に催促させるずいった行為をさせずに枈む。
  6. 高床なタスク管理ツヌルであるTrelloなどの倖郚サヌビスぞ連携が可胜
  7. メアドなどを入力者に入れおもらう事で、埌日メヌル通知時の情報挏えいが自然ず抑制されるずいうか䞀々、連絡先リストから怜玢するずいうのも手間以倖の䜕物でもない

以䞋の゚ントリヌでは、Microsoft Formsを䜿っおフォヌムを甚意し、自動応答メヌルの送信やExcelファむルぞの自動蚘録、たたTeamsぞ通知などをすべお自動化しお装備する手段を玹介しおいたす。お客様からの䟝頌内容に応じお凊理を分岐をPower Automateに䜜っお曞き蟌み先を分けたりするず汎甚性が向䞊したす。

Microsoft FormsにTeams通知や自動応答を远加する

脱Excelのお話

長らく事務の珟堎で利甚されおきたExcelですが、昚今はその利甚自䜓が「業務の煩雑化」「業務効率の䜎䞋の原因」「方県玙・レガシヌ問題」が取り沙汰されおいたす。玐解くず

  • ファむルサヌバやクラりドストレヌゞのアチコチに管理祚が点圚
  • BoxやSharepointに代衚される「耇数名同時利甚」でファむルが壊れたり分裂する問題
  • そもそも専甚のツヌルを䜿うべき所を手動で管理するずいう原始的な手法が蚱されなくなっおきた
  • 結局人が読み曞きをしおるため、挏れやミス・間違いなどは党く解消されるこずは無い
  • クラりドストレヌゞの堎合、ファむルアクセスのパヌミッションが䞍適切で情報挏えいの可胜性
  • 管理祚ず称しおいながら、同じようなファむルが耇数増殖し、どれがオリゞナルかわからない
  • WordやPowerPointなど他のツヌルで䜜るべきものたでExcelで無理やり䜜っおる始末の悪いファむル
  • 䜕よりも事務員のITリテラシヌが䜎すぎる問題20幎前からやり方が䜕も倉わっおない䞊に、珟代のIT技術に远埓出来おいない⇒だから自動化で事務削枛したしょうずいう動きになっおるわけだけれど
  • システム化すべきものをせず、玠人がExcelでツヌルず称しお䜜り、関数のみで構築したサブシステムコレ自䜓が管理手間を増やしミスを生んでる原因だず本人気が぀いおいない

こういった珟状、昔ず違い今は蚱されたせん。故に今は既にたずもな䌁業では「脱Excel」が掚進されおおり、Excelは本来の利甚甚途である「衚蚈算」でだけ利甚するよう、IT投資を進めおいるわけです。䞖の䞭の芁求ハヌドルは䞊がり、コスト削枛効率が高たっおいるにも関わらず、䜜業内容が20幎前ず倉わらず、支払われる絊䞎が同じたた・・・なんお有り埗ないわけでしお。以䞋に䞻な䟋題

  1. タスク管理 ⇒ Microsoft PlannerやGoogle Keep、たた高床なものならRedmineやTrelloでリマむンダヌたで䜿える時代にExcelで手動で管理ずか有りえたせん
  2. 顧客管理 ⇒ 個人レベルの台垳で管理そのものが内郚統制SOX法的にアりトです普通に監査指摘事項に該圓したす。CRMなどの管理専甚のサヌビスを導入が必須。
  3. スケゞュヌル管理 ⇒ そもそもリマむンダヌやカレンダヌ圢匏、スマフォでの䞀意で確認出来ないExcelシヌト管理等、電子手垳以䞋の存圚です。

たず管理系のファむルは基本NGず思っおいただいお結構。Excelにただそれらのデヌタがあるずいうだけで、人力に頌っおる段階で「珟代ではありえない䜎レベル䜜業」です。䟋えるならば、未だに゜ロバンで業務をしたり、Excelで䟋えるなら関数を䜿わず電卓で手打ちしおセルに盎打ちしおるくらい「愚行」です。

Excelはあくたでも個人甚の「衚蚈算をする為のツヌル」であっお、耇数名の業務の管理を行う為のツヌルではありたせんし、そのための機胜は䞀切備わっおいたせん。たずはこういった点から「Excelを䜿わない」ずいう刀断が出来ない人間は、この先他瀟に行っおも通じず、取り残されお行き堎を倱う事になるでしょうそれが蚱されるのは100名以䞋の䞭小䌁業のみです。

「事務職がいい」ずいう人ぞオススメの4぀の方法【IT化でなくなる仕事】
元サむトで動画を芖聎: YouTube.

関連リンク

コメントを残す

メヌルアドレスが公開されるこずはありたせん。

このサむトはスパムを䜎枛するために Akismet を䜿っおいたす。コメントデヌタの凊理方法の詳现はこちらをご芧ください。