Excel。テーブルとスピル機能で、必要な列だけを簡単に抽出することができます。【Table】

Excel。テーブルとスピル機能で、必要な列だけを簡単に抽出することができます。

＜テーブルとスピル機能＞

Microsoft365のExcelやExcel2021にあるスピル機能ですが、テーブルと相性がいいので、ちょっとしたことを知っていると、便利に使うことができます。

例えば、次のテーブルがあります。

A1:D11に「売上表」というテーブル名をつけたテーブルがあります。

このテーブル全体をデータをコピーというか抽出したい場合、通常ならば、範囲選択してコピー&ペーストという流れだと思いますが、スピル機能によって、テーブル名を参照させるだけで、OKになりました。

では、F2に次のように数式を設定します。

=売上表

確定すると、F2を起点として、テーブルデータがコピー（抽出）することができました。

G列は、日付なので、シリアル値に戻ってしまっていますが、大きなデータを別シートなどにコピーしたい時には、テーブル名参照だけで、完了することができます。

また、特定の列（フィールド）だけコピー（抽出）したい場合は、テーブル名参照にフィールド名を合わせることで、簡単に抽出できます。

F2には、

=売上表[日付]

G2には、

=売上表[売上高]

と設定するだけです。

なお、手入力だと、テーブルのフィールド名が一覧で表示されるので、選ぶのは簡単になっています。

このように、スピル機能が追加されたことで、いままで行っていた作業が、より効率的に処理することができるようになったかもしれませんので、確認するといいかもしれませんね。

Excel。入力規則のリスト。アイテム増えても手早く更新できるようにしたい【Input rule】

Excel。入力規則のリスト。アイテム増えても手早く更新できるようにしたい

＜入力規則・テーブル＞

VLOOKUP関数と相性の良い、入力規則の「リスト」。

入力規則のリストをつかうことで、入力ミスを抑制することも出来ますし、入力自体も文字数が長い時などは、楽になります。

このデータの入力規則のリストは、データタブの「データの入力規則」をつかって設定するだけなので、設定自体も簡単です。

例えば、今回設定してある、A2には、どのような入力規則のリストが設定されているのかを確認しておきましょう。

設定タブの「元の値」には、=$A$7:$A$9と設定されているので、この範囲のデータがリストとして、表示されてくるわけですね。

ただ、便利ではあるのですが、リスト一覧のアイテムが増えたときには、当然、増やさないといけません。

どのようにしたら手早く、リストに追加することができるのでしょうか。

当然、データの入力規則ダイアログボックスの設定タブにある「元の値」を修正すればいいわけですが、イチイチ、設定しなおすのは面倒ですね。

ある機能をつかうと、特に何もしなくても、データを追加するためでリストの範囲を拡張することができます。

それは、商品リストをテーブルにすることです。

では、A6をクリックします。

テーブルやピボットテーブルをはじめ、データベース系の処理は基本的に、範囲選択をしません。

テーブルの作成ダイアログボックスが表示されますので、OKボタンをクリックします。

では、データを追加してみましょう。

 

このように、データの入力規則の「リスト」に、追加したデータが加わっていることが確認できました。

テーブルには、自動拡張機能があるおかげで、リストも拡張されたわけです。

なお、B2のVLOOKUP関数の「範囲」も自動的に拡張されています。

=IFERROR(VLOOKUP(A2,$A$7:$B$10,2,FALSE),"")

とテーブルにすることで、数式も修正する必要はありません。

テーブルは、抽出や並べ替えだけの機能ではありませんので、色々と使ってみると、意外と改善できるかもしれませんね。

Excel。VLOOKUP関数。商品数が増えるたびに数式を修正するのは面倒なのでどうにかしたい。【VLOOKUP】

Excel。VLOOKUP関数。商品数が増えるたびに数式を修正するのは面倒なのでどうにかしたい。

＜VLOOKUP関数・テーブル＞

納品書や請求書などでお馴染みの「VLOOKUP関数」は確かに便利なのですが、ちょっと困ったことがありまして、取扱商品が増えるたびに、数式を修正しないといけないという問題が発生します。

どういうことかというと、

C2には、VLOOKUP関数の数式が設定しています。

=VLOOKUP(B2,$E$2:$F$6,2,FALSE)

なお、今回は「#N/A」エラーに対応する、IF+VLOOKUP関数やIFERROR+VLOOKUP関数の形での数式にはしておりません。

「範囲」が問題の原因でして、$E$2:$F$6を設定しています。

これは、商品リストに該当する場所を設定しているわけですが、商品が増えれば、範囲で設定した$E$2:$F$6も範囲選択を拡張しなければなりません。

試しに、新しく「C001 ボールペン」を追加してみて確認すると、

 

「#N/A」エラーが表示されました。範囲は自動的に拡張されるわけではないので、当然、新しく追加したものは、範囲外になりますので、エラーになってしまうわけですね。

「範囲」で設定した、$E$2:$F$6を商品が追加されたら、修正すればいいのですが、追加のたびに修正するのは、面倒ですし、ミスを起こす可能性も発生します。

なおかつ、単価など複数個所にVLOOKUP関数をつかっていたら、すべてのVLOOKUP関数を修正しなければなりません。

そこで、ちょっとしたことを追加するだけで、この問題から解放されます。

範囲に該当する場所を【テーブル】に設定してVLOOKUP関数を作るだけです。

最初に、今回は商品リストに当たる場所をテーブルにしますので、E1をクリックしておいて、挿入タブの「テーブル」をクリックします。

テーブルの作成ダイアログボックスが表示されますので、範囲を確認して、OKボタンをクリックします。

 

テーブルに設定されました。

このままでもいいのですが、テーブル名を設定しておくと何かと便利なので、テーブルデザインタブの「テーブル名」に任意で名前を設定します。

今回は、「商品マスタ」としました。

あとは、VLOOKUP関数をいつものように作成するだけです。

C2にVLOOKUP関数を設定してきます。

検索値には、B2。

範囲ですが、E2:F6を範囲選択すると、先程設定したテーブル名に変更されます。

列番号は、2

検索方法は、FALSE

あとは、OKボタンをクリックします。

ここからが「本番」。

改めて、「C001 ボールペン」を追加してどうなるのか確認してみましょう。

今回は、きちんと商品名が検索抽出されました。

追加した時点でわかるのですが、テーブルは自動拡張されますので、テーブルの範囲が広がりました。

このテーブルの自動拡張機能というのがポイントで、数式で使用すると、セル番地で設定するのと異なり、自動的に範囲を拡張して算出することができます。

テーブルというと、どうしてもオートフィルターなどのデータベース機能が中心と思ってしまいがちですが、色々組み合わせみると、作業を効率化することができたりするかもしれませんね。

Excel。入力規則のリスト。アイテムが増えたら自動的にリストの範囲も拡張したい【Input rules】

Excel。入力規則のリスト。アイテムが増えたら自動的にリストの範囲も拡張したい

＜入力規則のリストと名前の定義とテーブル＞

請求書や見積書などで使用される、入力規則のリスト。

お世話になっている人も多いかと思います。

ところで、アイテムが増えたら、自動的にリストは連動してくれませんので、色々設定をし直さないといけません。

作業自体は簡単でも、できることなら、設定の変更をしないで、アイテムを増やしたら、自動的にリストも拡張反映されるといいですよね。

そこで、今回は、アイテムが増えても自動的に、入力規則のリストも拡張される方法をご紹介していきます。

現在の設定を確認しておきましょう。

データタブの入力規則からデータの入力規則ダイアログボックスを表示させて、入力値の値を「リスト」にして、元の値には、商品コードの一覧がある、「=$E$2:$E$7」を設定しております。

 

E8にアイテムが増えたら、自動的にこの「=$E$2:$E$7」が広がってくれればいいわけですね。

念のため、E8に「D002」という商品コードを入力して確認してみましょう。

リストは自動拡張しないことが確認できました。

アイテムが増えた場合、先程の「=$E$2:$E$7」を修正すればいいのですが、その都度、データの入力規則ダイアログボックスを表示して修正するのは面倒です。

ここでポイントになるのが、Excelのバージョン。

元の値に入力する、E列の場所が、同じシート状にある場合と、別のシートにある場合では、Excelのバージョンによって、設定方法がかわります。

Microsoft365のExcelでは、同じシートでも別のシートでも、同じシートと同じやり方でいいのですが、Excel2016では、設定方法が異なります。

最初は、同じシート上にある場合から紹介していきます。

同じシート上ならば、とても簡単です。単純に、テーブルにすればいいだけです。

E1をクリックして、挿入タブのテーブルをクリックします。

テーブルの作成ダイアログボックスが表示されますので、先頭行をテーブルの見出しとして使用するにチェックマークをいれてOKボタンをクリックします。

テーブルに変わりましたので、アイテムを増やしてみます。「D002」を追加します。

テーブルは自動拡張される特性があります。

この機能を使うことで、入力規則のリストも自動拡張される仕組みです。

では、入力規則のリストを確認してみましょう。

このように、自動的に入力規則のリストが拡張されました。

【Excel2016の場合】

E1:F7までの商品リストが別のシートにある場合は、残念ながらできません。

E1:F7を別シート（シート名：商品リスト）のA1にコピーします。

データの入力規則のリストの設定も修正しておきます。

元の値には、「=商品リスト!$A$2:$A$7」と変更しております。

では、商品リストを、テーブルにしてみましょう。

新しいアイテム。「D002」を追加してみます。

Microsoft365のExcelだと、問題なくリストに追加されているのですが、Excel2016では追加されていません。

そこで、次のような処理を追加する必要があります。

A2:A7に名前の定義を行います。名前ボックスに「商品NO」と名前を定義しました。

データの入力規則の設定を修正します。

元の値に、「=商品NO」と設定しなおします。

これで、アイテムを増やしてみると、リストに「D002」が追加されていることが確認できます。

名前の定義で設定した範囲は、テーブルの拡張と連動しているようになっているので、アイテムを追加しても自動的に追加することが出来たわけです。

Excelのバージョンによって、設定の方法は異なりますが、アイテムが増えたら設定を変更しなくても、入力規則のリストを追加することができます。

Excel。Accessのクエリのように、3つのテーブルからピボットテーブルとPower Pivotで表を作る【TABLE】

Excel。Accessのクエリのように、3つのテーブルからピボットテーブルとPower Pivotで表を作る

＜Power Pivot＞

Excel2013からピボットテーブルにリレーションシップの機能が追加されました。Accessを使わなくても、複数のテーブルからAccessのクエリのように表を作れるようになっています。

VLOOKUP関数を多用するのも悪くありませんが、複数テーブルからでも、表が作れますので確認しておきましょう。

【テーブルにしてテーブル名を設定しておく】

今回は3つのテーブルを用意しました。
Excelの表をそのまま使用することができないので、テーブルにして、テーブル名を設定しておきます。

最初は、売上リストという売上データの表です。

売上コード・販売日・店舗コード・商品コード・販売数
というフィールドで構成されています。
2つ目のテーブルは、店舗リストという店舗名の表です。

店舗コード・店舗名・座席数
というフィールドで構成されています。
最後は、商品リストという商品管理の表です。

NO・商品コード・商品名・商品分類・金額
というフィールドで構成されています。

【売上リストを使える表にしたい】

テーブル：売上リストですが、店舗コード渡河はありますが、店舗名がありません。
商品名もないのでわかりにくく、販売数はわかるものの、販売金額も算出されていません。そこで、それらを表示した表を別のシートにつくっていきます。

基軸となる売上リストの中のセルをアクティブにして、ピボットテーブルを挿入していきます。

ピボットテーブルの作成ダイアログボックスで、「このデータをデータモデルに追加する」にチェックマークをいれるのを忘れないようにしましょう。ここがポイントです。

OKボタンをクリックすると、ピボットテーブルを作成するための新しいシートが挿入されます。

ピボットテーブルのフィールド作業ウインドウが表示されますので、「アクティブ」から「すべて」に切り替えます

こうすることで、このブック上にある、テーブルを使うことができるようになります。

【レイアウトは無視してフィールドを設定する】

Accessのクエリを作ったことがあれば、イメージしやすいと思いますが、必要なフィールドをピックアップしていきます。

なお、ピボットテーブルのレイアウトがおかしくなりますが、気にせず、設定していきます。

売上リストから「売上コード」を設定しますが、値フィールドに入ってしまうので、行フィールドに移動させます。

販売日を設定すると、列フィールドに挿入されてしまうので、行フィールドの「売上コード」の下に移動させます。

店舗コードを設定すると、行フィールドに挿入されます。

店舗名を表示したいわけですが、テーブルを変更する必要がありますので、店舗リストから「店舗名」を設定します。

売上リストの「商品コード」を設定します。

商品名を表示したいので、商品リストにある「商品名」を設定します。

今のところ、設定したフィールドは、行フィールドに挿入されているのが確認できます。ピボットテーブルのレイアウトは気になりますが、気にしないようにします。

商品の金額がないと、金額×数量で販売金額を算出することができませんので、商品リストにある「金額」を設定します。

「テーブル間のリレーションシップが必要である可能性があります。」と表示されてきたら、「自動検出」ボタンをクリックして、リレーションシップを作成します。

事前に作成しておいてもいいですし、自分で作成してもいいのですが、自動検出が便利です。

リレーションシップの管理ダイアログボックスで確認しておきます。

上側が、「一対多」の多で、下側が「一対多」の一で設定されています。

値ボックスにある「合計/金額」フィールドを行ボックスの一番下に移動します。
【レイアウトを変更する】

レイアウトを変更します。ピボットテーブルのデザインタブの「小計」から「小計を表示しない」をクリックします。

「レポートのレイアウト」から「表形式で表示」をクリックします。

レイアウトが変更されますので、列幅を調整します。

「合計/販売数」というフィールド名はわかりにくいので、「販売件数」に変更します。「販売数」にしたいのですが、すでに使用されているフィールド名なので、重複することができません。

【PowerPivotを使って販売金額を算出】

I列に金額×販売件数とすれば、販売金額を算出することができますが、ピボットテーブルの外側になってしまいます。

そこで、『PowerPivot』を使うことで販売金額のフィールドを作成し、作成したフィールドをピボットテーブルに挿入していきます。

PowerPivotタブの管理をクリックして、PowerPivotを起動させます。

列の追加に、数式を設定します。
='売上リスト'[販売数]*RELATED('商品リスト'[金額])
と設定すると、販売金額が算出できますので、見出しを「販売金額」に変更します。

変更したら、PowerPivotを閉じます。

再び、ピボットテーブルのシートに戻って、ピボットテーブルのフィールドを確認すると、売上リストが更新されて、先程作成した「販売金額」が表示されているので、設定しましょう。

これで、完成しました。

Excelには、様々な機能がありますので、色々試してみるといいかもしれませんね。