Excel。VLOOKUP関数の範囲をAccessのテーブルでやりたいけどどうしたらいい？

Excel。VLOOKUP関数の範囲をAccessのテーブルでやりたいけどどうしたらいい？

＜AccessのデータとVLOOKUP関数＞

先日、セミナーでVLOOKUP関数を使ったアレコレをご紹介した中で、
ブック間でのVLOOKUP関数のご紹介をしたら、
Accessのテーブルとかクエリとかを範囲にして使うことは出来ませんか？
というご質問がありました。

確かに、Accessでデータを管理しているということもありますよね。

そこで、今回は、Accessのデータをどうやったら、
VLOOKUP関数で使えるのかをご紹介してみたいと思います。

まず、VLOOKUP関数のブック間で出来るのかを確認してみましょう。

次のデータが商品マスターという、別のブックにあります。

このデータをVLOOKUP関数で設定してみると、

検索値は、C4
範囲は、商品マスター.xlsx!A$2:C$5
列番号は、2
検索方法は、FALSE
これで、OKボタンをクリックしてみましょう。

問題なく、ブック間でのVLOOKUP関数を設定できますね。
ちなみに、数式は、

=VLOOKUP(C4,商品マスター.xlsx!A$2:C$5,2,FALSE)

では、いよいよ本題の

AccessのテーブルとVLOOKUP関数

をやっていきましょう。

VLOOKUP関数に限りませんが、
直接、他のアプリケーションを接続させることは出来ません。


そこで、一度、AccessのテーブルやクエリをExcelに取り込み、
それを使ってVLOOKUP関数で使うことは可能になるのです。

Accessのテーブルのデータを確認しておきましょう。

このテーブルデータを使います。

では、Excelブックには、別のシートを用意します。シート名はFROMをしております。

A1をクリックして、
データタブの外部データの取り込みにあるAccessデータベースをクリックしましょう。

ファイルの場所を確認して、接続したいAccessファイルを選択します。

なお、Accessが開いている場合には、
データリンクプロパティダイアログボックスが表示されます。

この場合は、そのままOKボタンをクリックしましょう。

Accessを閉じた状態ですと、次のデータのインポートダイアログボックスが表示されます。

今回は、テーブルでインポートさせたいと思いますので、このままOKボタンをクリックします。

これで、Accessのテーブルを接続することが出来ました。

テーブル名も、テーブル_VLOOKUP.accdbとなっていますね。

あとは、VLOOKUP関数を作成すればOK。というわけです。

検索値は、C4
範囲は、テーブル_VLOOKUP.accdb
列番号は、2
検索方法は、False
あとは、OKボタンをクリックしましょう。

これで、AccessとVLOOKUP関数のコラボレーションが出来ましたね。

なお、Accessでデータが増えたとしても、問題はありません。

一度、Excelを閉じて、Accessのテーブルのデータを増やしてみましょう。

BR-700 パイナップル　\500　沖縄

を追加しました。

では、Accessを閉じて、Excelファイルを開きます。

デザインタブの更新ボタンをクリックしましょう。

VLOOKUP関数は何もしないでよいことも確認できましたね。

Excelで管理していなくてもAccessでも
この方法を知っておくと色々作成することが出来ますので、
機会があれば、挑戦してみてくださいね。

今週のFacebookページの投稿 2016/9/5-2016/9/11

今週のFacebookページの投稿　2016/9/5-2016/9/11

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

9月5日
Excel2013。デザインが一新されて白っぽくなりましたね

9月6日
Excel2013。データ分析能力がパワーアップされましたね。

9月7日
Excel2013。クイック分析機能が追加されましたね。

9月8日
Excel2013。フラッシュフィル機能が追加されましたね。
データによっては、イマイチ使えないような気もしますが…

9月9日
Excel2013。おすすめグラフが追加されましたね。
けど、どう表現したいのかは大事なので、ここまでExcelに頼るのは…

9月10日
Excel2013。Excel2010で追加されたスライサーがパワーアップしましたね。

9月11日
Excel2013。念願かなった！？
Wordみたいに、１つのブックを１つのウインドウで表示できるようになりました。

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

Excel。Print。無駄な印刷範囲をいちいち変えるのが面倒！自動で印刷範囲選択する方法

Excel。無駄な印刷範囲をいちいち変えるのが面倒！自動で印刷範囲選択する方法

＜OFFSET関数とPrint_Area＞

Excelで印刷すると、余計な範囲があったり、
あるいは、逆に、データが印刷範囲に入ってなかったり、
一度だけならいいのですが、使いまわすデータでの印刷の場合、
データ量によって、当然印刷範囲が変わってくれた方がいいわけですよね。

そこで、今回は、データ量に合わせて、
自動的に範囲選択が出来ちゃうテクニックをご紹介します。

まず、確認ですが、印刷範囲の設定は、別に問題はないと思われます。

次のデータを印刷しようとすると、

2ページ目が空白のまま印刷されてしまい、もったいない。
というケースがあったりするわけですね。

コストのことも考えると、無駄な用紙は抑制したいものですよね。
そこで、今回の自動範囲が登場するわけです。

そこで、今回登場するのは、OFFSET関数と名前の定義。

そして、Print_Areaを修正してきます。

Print_Areaという名前の定義は、通常のままだとありません。

印刷範囲を設定すると登場する名前の範囲なので、
まずは、適当でいいので、印刷の範囲を設定します。

これで、まず、Print_Areaを定義できました。

続いて、自動で範囲選択するために、OFFSET関数を使っていきます。

OFFSET関数は、範囲を指定できる関数です。

名前の定義で、関数を直接入力するのは大変なので、一度作成して、
その数式をコピーしていくことにしましょう。

どのセルでもいいので、クリックして、OFFSET関数ダイアログボックスを表示しましょう。

参照には、$A$1　これは、範囲選択の始点です。
行数と列数は、指定しませんので、0（ゼロ）
高さは、COUNTA($A:$A)　データ量によって変動させます。
幅は、COUNTA($1:$1)　列数（見出し数）も変動可能にします。

数式は、

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

この数式をコピーして、名前の管理でPrint_Areaに貼り付けます。

Print_Areaを選択して、参照範囲に数式を貼り付けます。

OKしたあと、シート名が加わってきますが、そのままで大丈夫です。
あとは、閉じるボタンをクリックしましょう。

では、改めて、ファイルタブの印刷で確認してみましょう。

自動的に範囲選択されましたね。

このように、データ量に応じて、印刷範囲が連動してくれたのが確認できましたね。

名前の定義（Print_Area）+OFFSET関数のコラボレーションテクニックを
知っているのと知っていないのでは、
作業効率が大幅に変わることが実感できるテクニックの一つですね。

OFFSET関数は、使い方によって様々なテクニックに使える関数の一つですので、
機会がありましたら、少し使ってみるといいですよね。

今週のFacebookページの投稿 2016/8/29-2016/9/4

今週のFacebookページの投稿　2016/8/29-2016/9/4

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントをです。

8月29日
Excel。他のアプリケーションをExcel内に取り込むことをインポートといいます。

8月30日
Excel。複数のコメントをまとめて削除する場合は、
対象となるコメントが挿入されているセル範囲を選択してから操作を行うと、
まとめて削除出来ちゃいます。

8月31日
Excel。シートを作業グループにしておくと、
特定の複数シートが一度に印刷することが出来ちゃうんです。

9月1日
Excel。さて質問。Excel2010で「余白の表示」ってボタン。
どこにあるか知っていますか？

9月2日
Excel。「余白の表示」ボタンの正解は、
ファイルタブ→印刷→右下の２つあるボタンの左側です。

9月3日
Excel。印刷。【先頭ページのみ別指定】にチェックをすると、
先頭ページのヘッダーとフッターを他のページと違う設定に出来ますね。

9月4日
Excel。ワークシートに規定で設定されているフォントを
【標準フォント】といったりします。
時々本に出ていて、ご質問を受ける事があります。

Excelテクニック and  MS-Office recommended by PC training

https://www.facebook.com/exceltechniqueandmsoffice/

Excel。Wordでの差し込み印刷。しかし、表示形式が変？どうやったらいいの？

Excel。Wordでの差し込み印刷。しかし、表示形式が変？どうやったらいいの？

＜Excel→TEXT関数 Word→差し込み印刷＞

Wordにある、【差し込み印刷】という便利な機能がありますよね。
ExcelやAccessなどのファイル。

例えば、顧客名のファイルなどを使うと、あっという間に、大量のラベルやら、
個別あてに文書を作成することが出来るというすぐれものなのですが、
Excelのデータのまま、差し込めないで困っている。

というご質問をいただきましたので、
そのリカバリー方法をご紹介したいと思います。

まずは、下記のようなExcelファイルがあります。

このファイルをWordに差し込み印刷で使うというわけですね。

そして、こちらが、Word文書。

では、早速Wordで差し込み印刷を行っていきましょう。

差し込み文書タブの差し込み印刷の開始をクリックして、
標準のWord文書が選択されていることを確認します。

続いて、宛先の選択から、

既存のリストを使用を選択します。

ここで、先程のExcelファイルをしていします。

では、差し込みフィールドの挿入をクリックして、該当するフィールドを選択しましょう。

それぞれ、日付と売上金額を挿入しました。

では、結果のプレビューをクリックしましょう。

無事にフィールドが挿入されましたね。これで完成…では、ありませんよね。

よくご覧ください。

日付が、2/1/2017　と月・日・年という表記になっていますよね。

売上金額は、10000　と桁区切りのカンマがありませんよね。

そう、Excelの表示形式のまま、挿入できていませんよね。

Wordのコントロールしている、表示形式になってしまっています。

では、どうしたらいいのでしょうか？三桁区切りのカンマぐらいなら、
まだいいと思ってくれるかもしれませんが、日付は、全然違っています。

月・日・年では困ってしまいますし、和暦の場合は、和暦でなくなってしまいます。

そこで、Excelでアレンジを加えると、この問題から解放されるのです。

Wordでアレコレ考えるよりも、明らかに、簡単ですので、早速紹介していきます。

Excelの表を次のように修正していきます。

H列に、差し込み用の差し込み日付
I列には、同じく差し込み用の差し込み売上金額
という差し込み用のフィールド（列）を作成します。

ただ、セル参照させているわけではありません。

実は、TEXT関数を使って算出しているのです。

H2には、

=TEXT(B2,"mm月dd日")

という数式が、

I2には、

=TEXT(G2,"#,##0")

という数式が設定されています。

では、このExcelを使って、改めて差し込み印刷を行ってみましょう。

差し込み日付と差し込み売上金額フィールドをそれぞれ挿入して、
結果のプレビューで見てみましょう。

すると、今度は、Excelの表示形式のままで挿入することが出来ましたね。

今回のように、差し込み印刷を行うときに、
表示形式がうまくいかない場合には、
是非、TEXT関数というテクニックを使うと解決しますので、
覚えておくといいテクニックかもしれませんね。

Excel。COPY。オートフィルでコピー。アレレ？なんか変。そしてコリャ。便利な連番技も紹介。

Excel。オートフィルでコピー。アレレ？なんか変。そしてコリャ。便利な連番技も紹介。

＜オートフィル＞

町内会のお祭りがあって、今年はビンゴ大会ではなくて、
クイズ大会を開催することになって、
Excelを使って問題文の一覧を入力して作っていこうとしたら、
不思議なことが起こったという話を聞きました。

どういうものだったのかというと、次のような表を作ったらしいのですが…

B列には、問題文が書いてあったそうです。

見た感じ、別におかしなところはなしのですが、おかしかったのは、
作成途中のことだったそうでして、
A2にQ1と入力して、オートフィルでコピーしたら、
オートフィルのコピーが変になったそうです。


ということで、下記のデータを用意して、オートフィルをやってみることにしましょう。

A1にQ1と入力して、下方向にオートフィルを使ってコピーしてみると…

アレレ！Q4の次がQ1にもどってしまっています！

なんで、Q5が出てこないのか？摩訶不思議ですね。

どうして、こうなってしまったのか？

どうやら、四半期のquarterのQ1～Q4というように、判断しているようですね。

Q1は、第一四半期ってことで、Q4は、第四四半期。

なので、Q4までいくと、Q1に戻ってしまうようです。

その証拠といってはなんですが、B1にQuarter1と入力してオートフィルでコピーしてみましょう。

先程のQ1と同じように4までいったら、1に戻りましたね。

ファイルタブのオプションの詳細設定にある、
ユーザー設定リストには、見受けられないのですが、隠れで存在しているようですね。

ということで、結論としては、今回のようにQ1～Q10のようなことをしようとしたら、
Q5を入力して、それを使って、オートフィルでコピーして、
Q5以降を作成する方法がいいようですね。

折角なので、四半期以外でも日付関係で便利なオートフィルでコピーがありますので、
合わせてご紹介したいと思います。

例えば、支払日などの一覧を作るにあたり、
月末日の一覧を作成するに、いちいち入力して作るのは、面倒だったりします。

こういう表だとして、なかなか面倒。

D列もオートフィルでコピー簡単に作成することが出来るんですよ。

まず、D1に4/30と入力してD2に、5/31として、D1:D2を範囲選択して、
オートフィルでコピーするだけで、このような、月末日だけの一覧を作ることが出来るわけです。

同じように、E1に4/20。E2に5/20と入力して、オートフィルでコピーすれば、
20日払いの一覧の時などの表を作成する時、
連番で作ってくれるので、知っておくと、いいかもしれませんね。

Excel。DATE。数値を元号表示の日付に変えたいけど、うまくいかないので、どうしたらいい？

Excel。数値を元号表示の日付に変えたいけど、うまくいかないので、どうしたらいい？

＜TEXT関数・LEFT関数・MID関数・RIGHT関数&DATE関数＞

最初から日付として入力されているデータならば、
単純に表示形式を変更することで、和暦にしたり、西暦にしたり、
年月日にしたりなどできるのですが、

下記のようなデータの場合、なかなか、大変な作業が必要になるのです。

では、このようなことをしたいわけです。

A列に申込日というのがあって、単純に日付を数値として入力してあるそうです。

さらに、年は元号というものなのですが、
これをB列のように西暦表示にしたいというのが今回のやりたいことなのです。

別に難しくはないでしょう？と思った方も多いかと思いますが、
今回の曲者は年が元号ということなのです。

西暦の場合だと、どうなるのかを確認してみましょう。

次の表を見てみましょう。

こちらの場合は、A列は、西暦で数値を入力しております。

B列のように、こちらは、/（スラッシュ）で区切られている西暦表示にしてあります。

B8の数式は、

=DATE(C8,D8,E8)

関数に慣れている人でしたら、
LEFT関数などをネストしながら算出することもできると思いますが、

今回は、C列に年。D列に月。E列に日。
を求めて、DATE関数を使って日付を算出させています。

C8には、=LEFT(A8,4)
左から4文字分ということですね。

D8には、=MID(A8,5,2)
5文字目から2文字分ということですね。

E8には、=RIGHT(A8,2)
右から2文字分ということですね。

という数式がそれぞれ設定されております。

ということで、西暦の場合は、それほど困らずに算出することができます。

なお、表示形式で、/（スラッシュ）が入る表示形式に変えたらいいのでは？
と思われるかもしれませんが、それはできません。

日付。Excelではシリアル値というもので管理しております。
1を1900年1月1日として、一日を1としてカウントするようになっております。
ですから、1900年1月1日から20160614日後を指すことになってしまうので、
表示形式では対応できないわけです。

そこで、本題に戻りますが、このシリアル値のこともあり、表示形式は使えません。

C列の年。D列の月。E列の日。は先ほどと同じように算出しております。

先ほどの西暦は、DATE関数を使って算出できましたが、今回はどうでしょうか？

B3をクリックして、DATE関数ダイアログボックスを表示しましょう。

年には、C3
月には、D3
日には、E3
これで、OKボタンをクリックしましょう。

残念ながら、DATE関数ではダメでした。要するに年が元号という認識がないわけです。

だから、今回のように簡単に思われるかもしれませんが、一筋縄では、いかないわけです。

今回は、TEXT関数と表示形式を使って算出していきます。

C3の数式は、

=TEXT("H"&C3&-D3&-E3,"yyyy/mm/dd")*1

というように設定していきます。

それでは、C3をクリックして、TEXT関数ダイアログボックスを表示しましょう。

値の"H"&C3&-D3&-E3　ですが、
"H"は元号の頭文字です。平成なのでHです。

もし昭和の場合は、Sで作成する必要があります。&で年月日を結合させるのですが、
-（ハイフン）を年月日の間にいれることで、文字タイプの日付型に変えることができます。

/（スラッシュ）を入れたいところですが、/（スラッシュ）では、結合することができません。

この時点で、値の結果は、H27-5-9　となるのですが、
西暦表示にしたいので、TEXT関数を使って、表示形式を"yyyy/mm/dd"とします。

そしてOKボタンをクリックしましょう。オートフィルを使って数式をコピーしましょう。

とりあえず、完成しました。

ただ、完成ではあるのですが、先ほど作りました、
B8は、右揃えになっていますが、B3は左揃えになっていますよね。

本来日付は、右揃えになるはずですよね。

実は、TEXT関数を使って求めたB3:B5は、文字タイプの日付になっているのです。

そこで、×1をすることで、シリアル値にすることができるようになりますので、

B3の数式の後ろに×1を追加します。
よって、
B3の数式は、

=TEXT("H"&C3&-D3&-E3,"yyyy/mm/dd")*1

となっているわけです。

このように、日付を用途に合わせて、表示をかけたい場合、
簡単にできないケースもありますので、最初から、
日付形式で入力するように心がけるといいかもしれませんね。