リレーションを活用してVLOOKUP頼りのデータ接続を卒業【Excel Power Pivot Table Relationship Data Model】
HTML-код
- Опубликовано: 21 июл 2024
- こちらの動画では、リレーションと呼ばれるテーブル同士の関連付けのやり方を解説します。
ピボットテーブルでデータを集計する際、必要な項目を用意するためにVLOOKUPやINDEX&MATCHやXLOOKUPといった関数を使いデータの接続をすることが多いですが、テーブル同士のリレーションを設定すると、そういった数式を使ったデータ接続する必要がなくなります。
千葉悠市
=========================
■ タイムスタンプ
=========================
[ 00:00 ] はじめに
[ 04:28 ] 元データのテーブルにする
[ 09:04 ] パワーピボットのアドインを有効化
[ 09:53 ] データモデルにテーブルを読み込む
[ 10:51 ] リレーションを設定する
[ 13:58 ] リレーションを使うメリット
[ 16:01 ] もっとパワーピボットを使いこなす
[ 16:54 ] さいごに
=========================
■ ファイルのダウンロード
=========================
動画内で使用しているExcelファイルは、noteに投稿している記事内で提供しています。
note.com/excel_dokata/n/nb056...
=========================
■ Excelドカタの集会所
=========================
RUclipsには投稿していないパワークエリやパワーピボットも含むExcel解説動画を投稿しています。
excel-dokata.mish.tv/
======================
■ Twitter
======================
動画の更新状況などをつぶやいています。
@Chiba_U1
bit.ly/3V9fvGK
=========================
■ ストアカでオンラインレッスンをしています
=========================
Excelの使い方を学ぶことができるZoomを使ったオンライン講座をストアカで開催しています。
▼オンラインによるExcel個別相談
bit.ly/3IZTwxF
法人向けに既存のExcel業務に関するアドバイス等も可能です。
ご不明点等ある場合には以下のメールアドレスまでご連絡ください。
mailofelgiss+youtube@gmail.com
個別の機能に関するオンライン講座
▼Excel パワークエリ 入門編
bit.ly/3rrU3gB
▼Excel パワークエリ 初級編
bit.ly/3cA0UQO
▼Excelパワーピボット 初級編
bit.ly/3cA0UQO
▼Excelピボットテーブル 初心者向け
bit.ly/36GoKXs
ストアカで開催中のすべての講座は以下のURLからご覧いただけます。
bit.ly/3tvkMKG
=========================
■ noteで記事を投稿しています
=========================
Excelの関数、パワークエリ、パワーピボット、DAXに関する記事を投稿しています。
note.com/excel_dokata
=========================
■ お仕事に関するお問い合わせ先
=========================
外注、業務委託、講師依頼、コンサル、個人レッスン等、お仕事に関するお問い合わせはこちらへご連絡ください。
mailofelgiss+youtube@gmail.com
=========================
■ Excelのバージョン
=========================
動画で使用しているExcelはMicrosoft365 Personal(Office Insider ベータ版 動画公開時点の最新版)になります。
=========================
■ ほしいものリスト
=========================
www.amazon.jp/hz/wishlist/ls/...
=========================
■ 使用している機材
=========================
▼カメラ(WebCamとして使用)
Sony α6400
amzn.to/3HGhttQ
▼カメラレンズ
Sony SEL35F18
amzn.to/3W2D2ZN
▼HDMIキャプチャー(カメラとPCを接続)
アイ・オー・データ GV-HUVC
amzn.to/3Fx4A2x
▼マイク
オーディオテクニカ AT2020USB+
amzn.to/3FDLGau
▼照明
Neewer NL660S/1Y
amzn.to/3Fv6m4c
▼照明スタンド
Neewer ST-200
amzn.to/3PrH3EV
▼ショートカットボタンデバイス
Elgato Stream Deck MK.2
amzn.to/3Ysu21W
▼マウス
Logicool G903
amzn.to/3BAFGhu
▼キーボード
Logicool G913 TKL
amzn.to/3PsK5su
▼ペンタブ
Wacom CTL-672/K0-C
amzn.to/3Fwyzb1
▼テンキー
FILCO FTKP22M/B2
amzn.to/3hv4Ire
▼Zoomを使ったExcelに関するオンライン個別相談を実施しています。
bit.ly/3IZTwxF
すげええええ
素晴らしいです。手元でデータを作りながらやってみると、すごさがわかりました。
ありがとうございます!
とても便利な機能の説明ありがとうございます。凄く分かりやすかったです。勤務先の生産管理部に革命を起こすべく、自分なりのデータ処理を作成出来そうです。
大変勉強になりました。チャンネル登録させていただきました。
これからも応援しています!
とても役に立つなと感じました!
仕事に使ってみたいと思います!
毎月数千行のデータをvlookupの列を追加するだけで10MB近くのデータになると処理速度や保存先の容量課金の課題がありました。今回のご説明で解決できそうです。
講習ありがとうございました
とても分かりやすかったです
これは凄いです。早速チャンネル登録しました。
シンプルに凄すぎて、
ずっと、ほおえーー、すげぇーーーー、しかゆーてない
活用します🌟
毎回毎回すごくわかりやすく説明されており本当に助かっています。エクセルでの膨大なデータ管理と集計が簡単にできるようになりました。ありがとうございます。また、新しい動画楽しみにしています。
凄い!大変参考なります。
知識の供与感謝です。
明日から早速チャレンジしてみます。ありがとうございました
ExcelでAccessみたいなリレーションシップが出来るんですね!とても参考になりました。
EXCEL でAcsessのDBエンジン呼び出せるし、ORACLEのDBエンジンも呼び出せるからね。
VBAから呼び出せば、かなり使えますし。処理速度も安定性も早い。
今のエクセルこんなになってるのか。
ほぼアクセスですね。これは便利。
かっこいいから使ってみたい機能1位にあがりました!
何度かやらないと無理そうですね〜💦
理屈は分かりやすかったですが、いざやるとなると迷いそう。頑張って覚えたら毎月の集計作業すごーく楽になるのはわかってるので、チャレンジしてみます。
最高です!毎月の工数が1/10になりました!!
ありがとうございます!勉強になりました
知らなかった機能です。かなりの生産性向上が期待できますね。ありがとうございます。
すごい、、、、感動しました
非常に分かりやすい説明で、理解することができました。 似たようなことをやろうとAWSにEC2を立てて、mySQL、Java Jetty Instance, html5の画面を自作しましたが、この動画に早く出会えていればと後悔しています。
むしろそちらの方がスキルアップにつながっているような気はしますが。。。
いやいやそこまでやるとプログラマー領域だろ
これは凄い~。ありがとうございます!早速活用させて頂きます!
ありがとうございました。
これからもよろしくお願いします🙏
20年前ほしかった機能!!またExcelに戻ろうかな?
全く知りませんでした
早く知りたかったです
ありがとうございます
Excelにリレーションが有ったので使えるのだろうなとは思ってましたが使い方が分かりませんでした。勉強になりました。
Excelドカタのワードセンス良
と思っていたらチャンネル登録していました
これってExcelでやること?と思う仕事でもExcelでやらないといけない縛りプレイを強いられた者。
そんなExcelドカタを助けたいチャンネルです。
これ、超便利!!
ありがとうございます。
今年に入ってからテーブルって何?って言う状態からVlookup を使ってデータ処理する所までできるようになりました。ありがとうございます。
今回もパワーピボットのボタンがない?どこ?から始めてましたが調べて発見しました🤗。色々使えそうですね。毎回エクセルの機能を知るたびにexcel ってすげーーーー!って感動してます。プログラム勉強する前にエクセルを必修にしてくれればいいのに・・・、宝の持ち腐れでした(笑
めちゃくちゃタメになりました!
ありがとうございます!
説明が丁寧かつ冗長になりすぎず、聞きやすくて大変参考になります。
これからもよろしくお願いしますー!
おお!!これはすごい、一瞬アクセスかと思った!表計算ソフトからデータベースソフトへの進化ですね。どうもありがとうございます。
参考になりました。ありがとうございました。 17:22
分かりやすい説明でとても参考になりました!
エクセルでアクセスみたいな画面出てきて度肝を抜かれた。3年ブランクがあっての来月からエクセル仕事で使うのでおさらいしていたら、世の中の進み具合は早いなぁー。
ひたすらvlookup使ってました。あとからカテゴリ増やせるのは便利です!
VLOOKUP大量投入の集計表を作り込んでから修正が入るとキー!!ってなりますよね。
PowerPivotはピボットテーブルのすごいやつなんだろう程度にしか思っていなくて、まさかデータベースのようにリレーションが組めるとは思ってもいませんでした。早速使ってみたいと思います。動画解説、ありがとうございました!
名前がピボットテーブルと似てますからね。あるあるです。
AccessやSQLといったデータベースの基本知識がある方であれば、すぐに使えると思います。
Accessと同じようなことがExcelで出来てしまうんてすね!これは手軽でいいですね。
ただ、仕事で使う場合は自分一人が使うわけではないので、他の人に説明して理解してもらわないといけないのが難点なんですよね…。ここのハードルか高くて、便利機能を使いたくても使えないことが多いです。
使える機能はあるのに他の人との兼ね合いで使えない、というのはあるあるですね。他の人を教育するための負荷の方がしんどかったり。。。
ピボット集計がメインというのであれば、確かにこの方法が最短ですね。
列の新規追加にも笑顔で対応できる、というのはまさに驚嘆です。
その前に、私としては基幹データを(Power Query経由で) 直にデータ加工できる(元データ変更の危険なく)という恩恵が有難すぎて、
世界中の業務が変わる予感です。Oracle提供ドライバでのODBC接続も自宅でできて(四苦八苦の上)、感激しております。
次は、それを職場でやらせてもらえるか交渉ですね。そこは今は出先なものでして。
Excel新時代にもう、足を踏み入れていますね。それを周りの人ともども享受していきたいです。
紹介有難うございます。
PowerQueryにはOracle含め、各種サーバーから直接データを取得する機能はありますが、それが会社のサーバーからとなると、会社のセキュリティポリシー的な話になってきますね。
TAさん
同じ気持ちです。とても共感できます。
ハードルは上にいちいち交渉して許可をもらわなくてはいけないことですよね・・・
ほえー、すっごい便利
AccessのRDBがExcelで出来るようになるんだ。すごい!
ありがとうございました。
ありがとうございます!会社のPCにアクセスが入っていないのでこの機能は役にたちます。
SuperThanks ありがとうございます❗❗😆
凄い!
ACCESS等のDB側テーブルとかクエリをソースにしたい機能ですね。
PowerQueryを使うとAccess含め各種DBから直接テーブルデータを取得できますので、それを自動でPowerPivotに読み込むことができます。
動画の冒頭に、
どんな利点があるかまとめてもらえると嬉しいです
ありがとうございます!
SuperThanksありがとうございます!
便利だわ。。。
ありがとうございます。これはとてもよい講座でした!! 今回の売上データテーブルを複数のシートに持つときの方法あれば教えてください。
売上データのテーブルが複数のシートに存在する場合には、パワーピボットだけでは無理なので、パワークエリで売上データを取得し1つのテーブルに結合してからデータモデルに読み込む必要があります。
@@ExcelDokataCH ありがとうございます。調べてやってみます!
すばらしい
エクセルの機能って保守性とのバランスが難しい。
使い方知らない人がさわれない資料になっちゃうし。
時代が変わった、、、ACCESSで使ってたリレーションの機能がExcelで使えるとは😮
クエリ使えるのかなー
VLOOK使いこなせない自分から見ると
これめちゃくちゃ覚えやすいです
新しいバージョンのExcelだとXLOOKUPという関数があり、そちらの方がVLOOKUPよりも便利なので、関数でデータ接続する必要があるときは、そちらがオススメです。
過去に動画をあげていますので、よろしければご覧ください。
ruclips.net/video/6Rn3EYtnTG8/видео.html
EXCELは関数電卓としてしか使っていないけど
こういう機能があると知っていれば、いつか役に立つかも。
これでvlookup地獄から解放されます😂
accessの機能をExcelでしているような感じですね
まさにそんな感じです。
なのでACCESS使ったことがある人であれば、Excelのリレーションも違和感なくすぐに使えると思います。
エクセルでリレーションDBやれるんだ!
凄い時代、、、
30年近く前から出来ました。
安定性にかなりの難が有りましたけど。
明日の業務で早速使ってみます‼️仕事が楽しみです。
次月度に行数や数値が違う売上データで同じ処理をしたい場合、再度テーブル化、リレーション作業が必要ですか?
前月度の売上データテーブルに上から値貼付けで更新はできないですよね?
列構成が全く同じであれば、売上データを値貼り付けしてから更新すれば貼ったデータでちゃんとリレーション&集計されますよ。
ただし、リレーションするためのキーとなっているコードが売上データに新規で増えている場合などは、マスタにもコードを追加してやらないといけないです。
Power Query や Power Pivot にフォーム機能やレポート機能が加わればACCESSが不要になるのでしょうか。
いずれにしてもクエリ機能だけならACCESSより柔軟な機能が多い感じがしますし、多くの企業ではAccessではなくExcelでやってくれ!と言われますから、今後需要が増えると思います。
Power Query/Power Pivotはデータベースを構築することはできませんので、Accessが不要になることはないかと。
ただ、データベース構築はAccess、クエリによる抽出や集計はPower Query/Power Pivotのように使い分けることはあるかもしれませんが。
はじめまして!こちらの動画に巡り合えてとてもわかりやすく、今までやってた事は何だったんだと目から鱗状態でした!
ただ、会社にてさっそく実践してみようと思ったらエラー出てしまいすごくもどかしいです😭管理されたパソコンだとできない場合もあるのでしょうか…
どの時点でエラーになったのか分からないため、なんとも言えないです。
会社によっては、Excelでアドインを勝手に有効化することができないように制限されていることはあるみたいですね。
@@ExcelDokataCH ご返信ありがとうございます。どうやらそもそも元データに空欄があったため反映されなかったようです。別のデータで試したところ、無事に作成することができました!ありがとうございます🙇🙏
今回は別シートにマスタを作ってますが、他のファイルにあるマスタとリレーションすることもできるのでしょうか?
他のExcelブック内のテーブルをデータモデルに追加する場合には、パワークエリを使います。
エクセルってすげーんだな...
データモデルに指定するマスタの並びは1番左をキーになる項目にしとかないとリレーションしてくれない感じですかね?
VLOOKUPみたいに、リレーションのキー項目がテーブルの左端にある必要はないです。
テーブルの真中にある列項目でもキーにできます。
ただしリレーションのキー項目にはデータの重複があってはいけない点で注意が必要ですね。
動画のお題と直接関係のない質問でごめんなさい。
1:26~
「この数式を一番下までコピペしてあげれば…」
の部分はどのような操作を行っているのでしょうか?
まずG2セルをコピーしたあと、いったんF5セルを選択、その後一気にF10001セルに移動、
次の瞬間にはG列に一気にコピペされています。
この操作を教えてください。
以下の順番でキーボード操作しています。
G2を選択してCtrl + C でコピー、F2を選択、Ctrl + ↓でF10001までジャンプ、G10001を選択、Ctrl + Shift + ↑ でG2~G10001を選択、Ctrl + V で貼り付け
データをテーブルに設定していれば、もっと簡単に数式をコピーできますが、今回はテーブルにはしなかったので、少し数式をコピペするのがめんどかったですね。
@@ExcelDokataCH
再現できました!!
キーボード操作で隣列の分だけコピペする方法で悩んでいたのでまさにこの動画は渡りに船でした。こんな質問にご回答いただき大変恐縮です。
これからも勉強させていただきます。ありがとうございました!
Excel慣れていない人は使い方が分からないと思うのですが、こう言った機能を業務で使う場合は個人的に使用するのが一般的なのでしょうか。
一般的かどうかはなんとも言えません。
分からない人に合わせるか効率を優先するかは、職場の方針にもよると思いますので。
職場のエクセル、2016だしなぁ…最初にXLOOKUPが出たところでおったまげた
これSQLも書けるんだとしたらアクセスいらなくなるのでは。
MySQLに慣れてる人には待望の機能ですね。
Vlookup しまくってエクセルがとても重くなっていました。これで幾分かサクサクになりますか?ファイルサイズに違いが出てくるのでしょうか?
ピボットテーブル更新時に少し時間がかかるようになるかもしれませんが、少なくとも『再計算中。。。』のままExcelウインドウが頻繁に固まることはなくなると思います。(VLOOKUP以外の数式がいっぱいあれば別ですが)
ファイルサイズは、単純にVLOOKUPの列がなくなるので、その分減りますね。
かつてMacintoshのキラーアプリだったClaris FileMaker を彷彿とさせます MS Accessとはどのように差別化していくのでしょう
こちらは、あくまで既存のデータをかき集めてリレーションするだけなので、Accessのようにデータベースを作ったり、テーブルのレコードをいじったりはできないです。
@@ExcelDokataCH さん
確かに 本格的なDBではありませんがリレーションがつけられるだけで、個人的な用途はほぼほぼカバーできそうです
1:nやn:nの関係までは 個人の範疇では扱いませんから
n:nにならないようにデータを整理しない事の方が個人ベースの場合は問題です
アクセスと同じ機能ですね
アクセスの機能がエクセルで使えるようなったのですね
ACCESSのリレーションシップとは微妙に仕様がことなりますが、最低限のリレーションはできる、といった感じですね。
accessと同じことできるんや いい事聞きました
単価×人数という2列があった場合に、売上という項目を再計算させることはこれでできるのでしょうか?
PowerPivotのウインドウで売上の計算列を追加するか、メジャーを作成することで可能です。
vlookupとリレーションだとどちらがデータ量重いのでしょうか?
データ量というのがEXCELブックのファイルサイズということであれば、VLOOKUPの方が重くなります。
過去の動画でそのへんを比較していますので、よければご覧ください。
ruclips.net/video/ep-aQquyA88/видео.html
要はアクセスですね。VLOOKすら使えない人が9.5割。生きたデータはVLOOKで引っ張る方が良い気はする。ピボットも生きたデータに使いにくい。
レコードが一意とならないテーブル同士(多対多)はどのようにすればリレーションできるでしょうか。
Excelでは多対多のリレーションは設定できませんので、中間テーブルを作成してそちらとリレーションする、といった方法をとる必要があります。
ありがとうございます。
中間テーブルの概念はなんとなく理解しているつもりなのですが、中間テーブルを作成した上でどのようにリレーションを作るのかイメージが持てません。参考にしたらよい動画や、webサイトなど紹介いただくことは可能でしょうか?
丁度先日Excelドカタの集会所に、中間テーブルを利用して多対多のリレーションを作成する動画を投稿したところなので、参考までにお送りします。
excel-dokata.mish.tv/ja/videos/Doz28d1LJ3l3MeBGQY6C5ADO
初心者で勉強中です。商品名、などの項目名はリレーションするものどおし、全く同一の項目名ではにといけないでしょうか
リレーションのキーにする列は全く同じ値である必要があります。
見出しは、違っても良いが、キーにする列は全く同じでないとダメですね、ありがとうございます(聞く前にやってみるべきでした)
外国通貨を円に変換しようと、為替レートのテープルをリレーションしたのがうまくいかず。(1USDを✕145円みたいな)どうやらメジャーの作り方の問題みたいでした(Related関数?)
とりあえずクエリのマージで対応しつつ、脱xlookup関数だらけな資料を目指し精進します
テーブルのinner joinや
凄い!
さっそく週明けにVLOOKやINDEXMATCHだらけの集計表を改造したいです。
ところでこれはピボットテーブルなので、やはりデータが更新された時にはピボットテーブルを手動で更新する必要があるという事ですよね?
更新についてはそのとおりです。
人の意識をデータ化してAI化したら、職場内の意識を統一できるかも
vlookでやるのとファイルサイズはどのくらいの違いがあるのでしょうか?
扱うデータの行数等によって全然違ってはきますが、以前50万行のデータでVLOOKUPによるデータ接続をすべてPowerPivotのリレーションに切り替えたところ60MB→5MBに減りました。
@@ExcelDokataCH
早速のコメントありがとうございます!
相当期待出来るので、お正月の暇つぶしに動画拝見させていただき、いじり倒したいと思います!
データはコードではなく名前で入れても大丈夫ですか⁇
できなくはないですが、あまりオススメはしないです。
名前をキーにするとデータが重複してリレーションがエラーになる可能性が高いので。
@@ExcelDokataCH わかりました。ありがとうございました。
すごい説明と思いますが、初心者には全くわかりません。初心者向けの動画もお願いします。
物凄く参考になりました。早速仕事で使い始めようとしています。
ただ、数値をテキストとしてしか認識してくれず、以下3点のような事を試してみたのですが解決できず苦戦しています。
お恥ずかしいはなしですが、もし宜しければアドバイスいただけないでしょうか?
試してみたこと:
元テーブルの数値データの書式を数値に変更
元テーブルの数値データ列をvalue関数で数値化し、元の列に書式をコピペ
元テーブルの数値データを区切り位置を使って数値化
①データモデルにテーブルを読み込んだ後に、PowerPivotの書式設定でデータ型を整数/10進数に変更する
②PowerQueryでテーブルを取得してデータ型を整数/10進数に設定してからデータモデルにテーブルを読み込む
のどちらかになると思います。
@@ExcelDokataCH ご返信ありがとうございます。すみません、パワークエリを学習する前に質問しておりました。。勉強して出直します!
パワークエリ、ExcelドカタCHさんの動画で勉強させていただきました。結局、①でご指摘頂いた内容で難なく解決できました。ありがとうございました!
爺婆世代がいる限りテーブル使っても使いにくい(覚える気がない)って言われて終わり
頑なにテーブルを使わない人っていますよね。。。
わ
Excelが日々アップデートされていくのに、Accessがあまりアップデートされない…
動画のリレーションのKeyを結ぶ方向に違和感があります。データテーブルから見て1対多にする事が圧倒的に多いかと思います。(まあどこで品質チェックをするかの問題ですが)
おっしゃる通り、Accessのようにリレーション時に参照整合性も含めて設定する場合はデータテーブル→マスタテーブルの方が正しいですね。
EXCELでも、1対多のクエリ使えますよ。データの取得→その他のデータソース→テーブルまたは範囲でできます。
エクセル🛋無さる、
わざわざAccess使わなくても良いのですね!
これがMOSの試験範囲にないのは。。。
ピボットテーブルはまだEXCEL標準機能ではないので。。。アドインですから。
もうAccessいらねーやん
正直、ちょっと勉強してDB覚えた方が後々楽で早い。
0:50 データ表とマスター表が同じシート上にあるのがなんだかなぁ。
エクセルとは「表計算ソフト」だったはずなんだが、アクセスの使い勝手をなんとかしてくれんかね。
私も実際に仕事では同じシートに複数のテーブルを置いたりはしないですね。
動画撮影時にシートをコロコロ切り替えると説明がしにくかったので、一緒のシートに配置しています。