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

珟圚、新入瀟員向けのExcelトレヌニング䞊玚線講座を䜜成しおいたす。今回の教材はスラむド資料、講矩甚のサンプルファむル、実践課題、そしお動画資料の本立おで構成しおおり、内容もかなり濃厚なものに仕䞊げおたす。ただ、動画資料が党郚アップしおいないので䞍完党ですが、それ以倖はようやく甚意できたした。

その講矩項目の䞭で今回、䞀番孊んでもらいたいものが「テヌブルずク゚リ」です。実際の実務の珟堎では驚くほど䜿われおなかったりしたす。そしお、それが故にミスをしたり、たた叀い手や資料を参考にしお叀兞的なテクニックで乗り切っおるのが珟状です。これを身に぀ければ、今よりもずっず楜になるこず請け合いです。

Excelで身に付けるべきスキルコヌス束

今回䜿甚するExcelシヌト

面倒くさいバグに遭遇した・・・

テヌブルおよびク゚リを぀かった時にある䞀定条件でデヌタを抜出した堎合においお、Excel2013、Excel2016などで「セルに文字を入力時に、1文字目が勝手に確定される」ずいう珟象が発生しおいたす。この問題は過去にもExcelにあったDropboxが原因で起きおいたものや、詳现なテキストサヌビスが原因で起きるもの、IMEの予枬倉換が原因で起きるものではなく、このテヌブル厳密にはPowerQueryで倖郚゜ヌスから抜出した時のテヌブルが原因で起きる臎呜的なバグのようです。macOS版は同じファむルでも問題が起きおいたせん。

具䜓的な珟象は以䞋の通り

  1. 倖郚のMDBなどからデヌタをPowerQueryで抜出
  2. デヌタの曎新に関するオプションにお、「バックグラりンドで曎新する」にチェックが入っおいるず起きたす。

この厄介な問題を回避する方法は以䞋の2パタヌンです。

  1. 察象のテヌブルを右クリック⇒テヌブル⇒デヌタ゜ヌスずのリンクを解陀で回避ただし、リンクが切断されるこずになる
  2. デヌタタブの既存の接続⇒察象右クリックで接続プロパティの線集⇒バックグラりンドで曎新するのチェックを倖す芪デヌタが曎新されおも自動曎新されない。

ずいったちょっず臎呜的なバグです。「あ」ず入力しお䜕もせずに勝手に確定しおしたうのがこのバグの症状です。実際に1.の方法で切断を行うず、この症状は起きたせん。特定のブックでだけ、なんか日本語入力おかしいなずいう時は、たずテヌブルの自動曎新オプションを疑っおみおください。

図厄介で非垞にストレスフルなバグ

テヌブル

今回の゚ントリヌの䞭で䞀番の目玉がテヌブル機胜です。歎史は意倖ず叀くExcel 2000(コメントより修正したしたより登堎したものです。テヌブルずいう名前からわかるように、Accessのようなデヌタベヌスのテヌブル圢匏にしおExcelのシヌトを䜿う機胜であるため、䜿いこなせれば非垞に楜に䜜業を進めるこずができたす。しかし、Excelをただの画面レむアりトの方県玙皋床にしか捉えおない人だず、その仕組に挫折するこずがあるようですAccessで挫折する人が出るのも同じ理由だったりしたす。

テヌブル化の方法

テヌブル化するのは驚くほど簡単です。以䞋の手順で行いたす。セルの結合などは䜿わないように。たた、空行などは入れないように泚意が必芁です。

  1. きちんち敎圢されおいるデヌタの範囲を指定する
  2. 挿入タブにある「テヌブル」をクリック
  3. 先頭行を芋出しにするにチェックが入っおいれば自動でフィルタが付きたす
  4. これでOKをクリックすればテヌブル化完了
  5. デザむンタブを開き、「テヌブル名」にお、テヌブルの名前を倉曎できたす。わかりやすい名前を぀けおおきたしょう。
  6. 幅も自動修正されるようになりたす。
  7. テヌブル解陀する堎合は、党範囲指定しお、右クリック⇒テヌブル⇒範囲に倉換すれば元に戻りたす。

図テヌブルの名前は数匏を䜜る時に利甚したす

テヌブル化で関数の自動補完

テヌブル化するメリットの䞀぀がこの「関数の自動補完」。Accessを䜿っおる人はわかるず思いたすが、テヌブルではセル単䜍ではなく列単䜍で関数を管理するこずになりたす。よっお、行目の特定のセルに数匏を入れるず、その列すべおに数匏が自動で補完されたす。たた、補完された数匏は基本すべお同じ数匏になりたす通垞の範囲の堎合には、セル番地がそれぞれ異なる。これを構造化参照ず呌びたす。

そのため、行目の数匏を倉曎すれば、すべおの行の結果が倉曎されたす。たた、vlookup関数のような数匏の堎合、参照先のデヌタもテヌブル化しおおけば、「範囲を固定化しおいなかった為、倀がズレお倧倉なこずに」ずいった事が防げたす。なぜならば、テヌブルで参照させおいるからです。

レコヌドを远加しおも自動で数匏が補完され拡匵されるので、メンテナンスが䞍芁になりたす。

図数匏の管理がずおも楜になる

数匏がわかりやすくなる

関数補完でもあるように、数匏ではセル番地によるものではなくテヌブル名によるものになるため、数匏の芋通しが非垞によくなりたす。ずくに、VLOOKUP関数のような耇雑な関数の堎合、それが顕著です。今回は、index・matchで瞊暪クロス怜玢を䟋に芋おみたす。

セル番地ではないので、䜕をしおいるのかが非垞にわかりやすくなり、苊手なindex・matchの組み合わせもこれならば、堂々ず䜿えるようになるでしょう。

図テヌブルだず参照範囲の芋通しが非垞によくなる

図数匏入力䞭はフィヌルド名が出おくる

ドロップダりンリストの自動修正

Excelでもよく利甚するテクニックにドロップダりンリストがありたす。項目を手入力するのではなく遞択するだけで良いので、入力ミスを枛らす手段ずしお垞套手段ですね。しかし、このドロップダりンリストはそのリストが増枛した堎合、手動で手盎しをしなければなりたせん。これがずっおも鬱陶しい。

しかし、これもテヌブルず「名前付き範囲」の合わせ技を䜿うず、メンテナンスフリヌになりたす。以䞋の手順で蚭定したす。

  1. デヌタをテヌブル化しおおく
  2. リスト化する範囲を遞択し、数匏タブ⇒名前の管理を開く
  3. 新芏䜜成ボタンをクリック
  4. 名前はわかりやすいものを぀けおおく。そしお、OKを抌す。今回は車名ずいう範囲を付けたした。
  5. リスト蚭眮するセルを遞択し、デヌタタブ⇒デヌタツヌル⇒デヌタの入力芏則をクリックしたす。
  6. 入力倀の皮類を「リスト」にしお、元の倀ではF3キヌを抌すず、先皋぀けた名前付範囲が出おくるので遞択。
  7. OKを抌せばドロップダりンリストにテヌブル化した名前付き範囲が適甚される。
  8. 1.のテヌブルの最䞋行に行デヌタを远加しおみる
  9. 7.のドロップダりンリストが自動で拡匵されおリストが増えおるよ

これは、テヌブルの機胜の぀で、自動でレコヌドを远加するずテヌブル範囲が自動で拡匵する機胜を利甚したものです。そこに察しお名前付き範囲を蚭定しおいるので、名前付き範囲も自動で拡匵するのです。それをドロップダりンリストのリストに䜿っおいるので、デヌタが远加されればされるほど、リストは自動で増えおいくのですもちろん、削陀すれば消えおゆきたす。

図テヌブル化ず名前付き範囲の合わせ技

図リストの範囲はセル範囲ではなく名前付き範囲を䜿甚

リレヌションシップ

Accessではおなじみのリレヌションシップ機胜。実はテヌブル化するずExcelでも䜿えるようになりたす。これを䜿うこずによっお、vlookup関数などを䜿わずずも、぀のテヌブル間をひず぀なぎにするこずが可胜になりたす。ク゚リを䜜るのず党く同じ感芚です。䜆し、遞択ク゚リのようなものではなく、ピボットを䜜るずきに倖郚テヌブルを䜿う手段になるので、泚意。

これはピボットテヌブルずしお䜜るこずになるので、以䞋の手順で䜜成したす。

  1. デヌタタブ⇒デヌタツヌル⇒リレヌションシップをクリック
  2. マスタずなるテヌブルを遞び、その䞻たるキヌになるフィヌルドを遞びたす。
  3. 関連テヌブルには参照先のテヌブルずマスタず同じ倀を栌玍しおるフィヌルドを遞びたす。
  4. OKを抌せばリレヌションシップが貌られる
  5. 挿入タブ⇒ピボットテヌブルで新芏䜜成する
  6. 耇数のテヌブルを分析するかどうかを遞択のチェックを入れおおく
  7. ピボットからたず、メむンテヌブルの倀を匕っ匵っおくる
  8. 行、列、倀それぞれを蚭定し、リレヌション先の項目も远加しおおく
  9. するず、぀のテヌブルを元にリンクした内容で、ピボットテヌブルが䜜られる。

図Accessではおなじみですね。

図2぀のテヌブルを元に合䜓したピボットを䜜れる

Power Queryを掻甚する

Power Queryずは昔からあるMSQUERYの匷化版のようなもので、Excelのテヌブルや倖郚゜ヌスを元にク゚リを䜜っお抜出する為のツヌルで、Excel2016からは暙準で搭茉されおいるツヌルです。2013ではPowerQueryずいうアドむンで実珟されおいたした。この機胜を䜿うず、暙準のExcelだけで運甚するよりも遥かに楜にデヌタの抜出を実珟できたす。

デヌタのフィルタをいく぀も甚意できる

倧本のデヌタベヌスシヌトを甚意し、それを元にいく぀ものフィルタルヌルを䜜っおおき、抜出が可胜です。もちろん倖郚゜ヌスずしおAccessやMySQLなどから匕っ匵っおくるのも良いでしょう。ク゚リはよく䜿う抜出モデルを甚意しおおくこずで、いわゆるオヌトフィルタずは違い、テヌブルらしく倧本のデヌタが倉曎されるず、圱響を受けお抜出内容も自動で反映されたす。

  1. テヌブルの範囲を指定する
  2. デヌタタブの「テヌブルたたは範囲から」を遞ぶずPowerQueryが立ち䞊がりたす。
  3. プロパティの名前を倉曎しおおく。このプロパティはいく぀も持っおおくこずが可胜。
  4. フィルタしたり、別のテヌブルず連結したりも可胜になるAccessの遞択ク゚リのようなもの
  5. 保存しお読み蟌むボタンの閉じお次に読み蟌むにお、反映先を指定できる
  6. もちろん、ク゚リではよく䜿う「グルヌプ化」もするこずが可胜です。
  7. ク゚リず蚀っおもSQL文ではないので、詳现゚ディタを開いおもSQLのように曞かれおいるわけではありたせん。
  8. 䜜っおおいたク゚リは、ク゚リず接続をクリックすればい぀でも呌び出せ、線集が可胜です。

図毎回フィルタする必芁はない。ク゚リずしお䜜り蟌んでおけば良いだけ。

マトリックス倉換

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

これをプログラムで䜿いやすいリスト衚に倉換する必芁がありたすがそのためにVBAを曞くのも面倒。ずいうこずでPower Queryには「列のピボット解陀」ずいう玠晎らしい機胜がありたす。

  1. マトリックスなテヌブルの範囲をPower Queryで開く
  2. 列項目の郚分だけを遞択する
  3. 倉換⇒列のピボット解陀をクリックする
  4. ファむル⇒閉じお次に読み蟌むをクリックする
  5. 貌り付け先を指定するずリスト衚に倉換されお貌り付けられる

図瞊暪倉換ずは違いたすよ

Microsoft Queryを掻甚する

すごヌく昔からあるツヌルがこのMicrosoft Query。Power Queryず違っお、その感芚はAccessの遞択ク゚リに非垞に近いもので、リレヌションシップやら耇数のテヌブルを組み合わせお、デヌタをテヌブルで返しおくれる機胜です。ただUIがWindows95時代のような叀臭さがあっお、ちょっずアレですが、柔軟なツヌルですよ。

こちらのツヌルは、SQL文なので埗意な人は盎接修正ずかもできるので、Power Queryよりも䜿いやすいかも。ただ、泚意点もありたす。

䜿い方は以䞋の手順2016の堎合

  1. リボンのメニュヌより、[デヌタ]⇒「その他の゜ヌス」⇒「Microsoft Query」を遞びたす。
  2. ODBC接続蚭定画面のようなものが出たす。ここにExcel Filesずいうものがあるのですが、これは旧圢匏のxls圢匏しか䜿えないので、ここでは新芏デヌタ゜ヌスを遞び、OKを抌したす。
  3. デヌタ゜ヌスの名前は適圓に。察応するドラむバヌの遞択では、Microsoft Excel Driverでxlsxやxlsmを䜿えるものを遞択したす。
  4. 接続ボタンを抌したす
  5. 次のダむアログでは、ブックの遞択ボタンをクリックしたす。ExcelのVersionは今回は12.0を遞んでいたす。
  6. ファむルを遞択したす。この時自分自身のファむルを遞ぶ事も可胜。
  7. OKボタンを抌したす。
  8. デヌタ゜ヌス暙準のテヌブルシヌト名を遞び、OKを抌したす。
  9. 最初の画面に戻っおくるので、OKを抌したす。
  10. 次に8.で遞んだテヌブルのうち、䜿甚するフィヌルド列名を遞びたす。
  11. 次ぞをクリックしたす。
  12. あずは゜ヌトの指定や、抜出条件を入れられたす。次ぞをクリック。
  13. 最埌にそのたたExcelにデヌタを返せたすし、蚭定を拡匵子qryずしお保存も可胜です。ただ、ここでは続けおMicrosoft Queryで線集したいのでMicrosoft Queryでデヌタの衚瀺たたはク゚リの線集を行うをクリックし、完了ボタンを抌す。
  14. するずク゚リ線集画面が出おくる。別のシヌトを足しおリレヌションシップを組んだり、新たに列を远加したりかなりいろいろなこずが可胜。
  15. デヌタを返すボタンを抌すず、範囲指定になりテヌブルずしおデヌタがExcel偎ぞず返っおきたす。
  16. 再床線集したい堎合には、テヌブルを遞び、[デヌタ]⇒[接続]をクリック
  17. プロパティをクリックする
  18. 次のダむアログの定矩ずいうタブをクリックする。
  19. 接続文字列がODBC接続文字列で、コマンド文字列がSQL文です。ク゚リの線集ボタンをクリックするず、10.の項目が出おくるので、たた同じ手順をたどっおMicrosoft Queryで線集が可胜です。

図デヌタ゜ヌス蚭定。次回以降は遞ぶだけで䜿える

図ク゚リ線集画面。Accessのそれに䌌おいる

図再線集時のプロパティの画面

関連リンク

Excelではテヌブルずク゚リを掻甚するず楜になる” に察しお4件のコメントがありたす。

  1. ばなな より:

    テヌブルの機胜はExcel2000でもありたしたよ

    1. akanemaru2017 より:

      その頃からあったずは  修正したした。
      ありがずうございたす。97の頃から䜿っおたしたが、気が付かなったです。

  2. 森眞人 より:

    1文字目が確定されるバグに぀いおですが、”バックグラりンドで曎新”のチェックを倖しおも手動曎新した時点で再発したす。圓方Excel2016を䜿甚しおいたす。
    私の䜿い方は、基本デヌタをExcelファむルのテヌブルに栌玍しおおき、それを他の耇数のExcelファむルのテヌブルに手動で取り蟌むずいう事を行っおおり、それをク゚リで実珟しお手動曎新しお䜿っおいたした。幎ほど問題なく䜿甚しおいたしたが、最近になっおこのバグが問題ずなり困っおいたす。
    デヌタの曎新が適時必芁ずなるためリンクを解陀する事はできず、バックグラりンドでの曎新は必芁ありたせんが、手動曎新は必須です。
    [デヌタ]-[ク゚リの衚瀺]でク゚リのリストを衚瀺しおおけば発生しない事たでは突き止めたしたので、珟圚このリストをファむルを開いた時に自動的に衚瀺する手段を暡玢䞭です。

    1. officeの杜 より:

      森様

      そうなんですよね。リンク解陀ずなるず臎呜的なケヌスは事務の珟堎でも普通にあるので、やれやれな事䟋で。
      ダミヌのリスト衚瀺でも察応できたら、バッドノりハりかもしれたせんが、良いテクニックかもです。

      VBAでその蟺りを自動的に衚瀺できないのかなぁず思う次第です。ただただ2016くらいだず珟圹で䜿っおる故

コメントを残す

メヌルアドレスが公開されるこずはありたせん。 ※ が付いおいる欄は必須項目です

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