カテゴリー
VLOOKUP 関数

誰でもできるVLOOKUPをマスターしよう(サンプルデータ配布)

EXCELの基本となるVLOOKUPをマスターして、仕事の業務効率化をしましょう

VLOOKUP(ブイルックアップ)は指定した値を参照し、すぐ隣にあるデータを引っ張ってくる関数です。この関数を使いこなせば1つの値を入れればデータを複数個飛ばすことができるのでデーター入力の効率化ができます。

用途
・情報整理
・売上票の作成
・データーベースからの参照

関数

=VLOOKUP(①検索値, ②範囲, ③列番号, ④検索方法)

関数の説明
①検索値 入力したデーターの参照元になる値を入れます。
②範囲  データーベースの表の範囲を指定します。
③列番号 データーベースの左から数えた番号を入れます。
④検索方法 FALSEまたはTRUEのどちらかを入れます。

サンプルとしまして、商品情報を入れれば自動で売上金額を自動で計算される表を用意しました。

こちらをクリックしてダウンロード


サンプルでの表の見方
データーベース部分

左側にある部分がデーターベースの部分になります。

VLOOKUP関数が入っている箇所は右側のデーター入力箇所の黄色に塗ったところになります。

茶色の情報入力欄の部分に各項目を入れると自動で黄色の値(商品名と単価)が表示され、緑色の金額が自動で計算されるという仕組みになっています。

黄色の表のL5にはこのようにVLOOKUP関数を入れています。

=VLOOKUP(K5,$D$4:$F$9,2,FALSE)
これを分解すると・・・
①検索値 →K5
セル番号K5に入っている商品コード1と、範囲で合致した値を検索する。

②範囲  →$D$4:$F$9
データーベースの表D4からF9までの範囲までを参照する。

③列番号 →2
データーベースの2列目の値(商品名)を出すこと。

④検索方法→FALSE
一致しないものはエラーを表示する。

としています。

隣のM5には・・・
=VLOOKUP(K5,$D$4:$F$9,3,FALSE)
が入っています。

①検索値 →K5
セル番号K5に入っている商品コード1と、範囲で合致した値を検索する。

②範囲  →$D$4:$F$9
データーベースの表D4からF9までの範囲までを参照する。

③列番号 →3
データーベースの3列目の値(単価)を出すこと。

④検索方法→FALSE
一致しないものはエラーを表示する。

関数の引用の画面だとこのような形になります。

L5とM5の関数が出来上がれば、オートフィルで下に式をコピーすれば検索値が自然と移動するので簡単にできあがります。

・データーベース作成の注意点
①検索値の値を左側のセルに持ってくること
データーベースを作成するには次の注意点が必要です。VLOOKUP関数では①検索値から右側にある値しかもっていきません。そのため、①検索値になるようなコード類は必ず左側に持っていき、必ず②範囲指定で最左にするようにしてください。検索値はデーターベースと入力項目で一致していれば記号でも文字列でも結果が返ってきます。結果が返ってくるといっても検索値にするのはできるだけシンプルな数字や英語を使ったコードにすると良いです。

②データーベースの検索値部分は同一の値を存在させないこと
データーベース内に同一のコードが存在すると、VLOOKUP関数の性質から上位にあるコードを優先して値を返すようになっています。重複させないようにしてください。

このようにデーターベース部分にコード2と書いたバナナとレモンがあったときに、データーべースから返ってくる値はデーターベース上部にあるコードのバナナを優先して値が返ってきます。レモンには他のコードとは重複しないコードを与えれば正しい結果が返ってくるようになります。

・VLOOKUP関数でよくあるエラー
TRUEとFALSEの使い分け
FALSEを使う場合
完全にデーターベースと一致するコードを参照したい場合はFALSEを使ってください。

TRUEを使う場合
検索値に近いコードで結果を求めている場合はTRUEを選択してください。(TRUEを使う用途があまり思いつかないです。)
試しにサンプルのL5の関数にて④検索方法部分をFALSEからTRUEに修正し、K5を2.4にすると2に近いバナナが表示されます。FALSEだとエラー表示になります。この幅は2.0~2.9の値であればすべてバナナが表示されます。こういった不確定な幅で検索結果を求める場合はTRUEを使うと良いです。

・②範囲指定がずれる
同じデーターベースを参照して関数を作る場合は、②範囲の指定は他のセルにコピーしたときでも、範囲がズレないように②範囲に入れたセルの部分を&で囲うと便利です。

・③列番号の指定
列番号を指定を誤るとVLOOKUPの値の結果が誤った値が出てきてしまいます。サンプルのようにデーターベースの上に列番号を表記しておくと便利です。データーベースが20列になるとゴチャゴチャになってしまいますからね。

VLOOKUP関数と他の関数を組み合わせて使う
#N/Aの表示を無くす
①検索値にある値の箇所に数値が入ってないとこのようにエラー表記になってしまいます。表の作りとしては問題ありませんが、見栄えが悪いです。

これを回避するために、VLOOKUP関数と組わせでIFERROR関数を組み合わせると①検索値が空白であっても#N/Aが表示されなくなります。
サンプルデータのL5を
=IFERROR(VLOOKUP(K5,$D$4:$F$9,2,FALSE),” “)
にすると#N/Aの表示が消えます。
これはエラー表示だった場合にどのような表記をするかという関数を組み合わせた結果でエラー表示を消しています。
エラー部分を空白としていますが、上記の組み合わせ関数で赤く塗った箇所を
=IFERROR(VLOOKUP(K5,$D$4:$F$9,2,FALSE),”記入なし”)
とすると、無記入の①検索値に対しては記入なしと自動で表示されます。

カテゴリー
クレジットカード 用語集

クレジットカードの有効期限とは 有効期限を確認する方法

クレジットカードには有効期限が設けられています。有効期限が切れるとそのカード自体が使えなくなります。カードと紐付いていた自動引き落とし設定が全て使えなくなり、全て新しい有効期限に更新する手続きが必要です。この作業は手間がかかるものですが、怠ると支払いが滞りサービスの提供が受けられなくなります。有効期限の1ヶ月程前からカード会社から新しいカードが送られてきますので届いた時点でそのカードは使用できるので、切り替え作業ができます。カード発行してから引っ越しをして住所変更手続きをしていないといつまで経っても新しいカードを受け取ることができませんので引っ越ししたら必ず住所変更手続きをするようにして下さい。

クレジットカードの有効期限を確認する方法

カードの表面に書いてあります。下方の中央付近に有効期限と記載があります(この表記がないカードもあります)また年月を表すGOOD THRUと記載があります。これは”Good through”(グットスルー)の略で、カードが使える年月が記載してります。例えば、03/22と記載があれば、2022年3月まで使えるという意味になります。

有効期限の長さについて

有効期限はカード会社によって異なります。2年だったり5年だったりします。新入社員の方で借りたカードを作ったばかりの方は短かったりクレジットカードを長く利用し実績がある人はは長い期間だったります。 (利用実績がない場合は更新案内が届かないことがあるそうです。 その場合は自動的な解約みたいな状態になります。)

カードに有効期限が設けられている理由はいくつかあります。
①カードの交換
何年間か使用しているとカードが汚れてきます。カードには写真が入っていたり、チップが入っているので使用すると損耗していきます。定期的に新しいカードに交換することで耐久性を維持しています。

②盗難カードを使用できなくする
もしカードを盗難されたことに気づかずにずっと使われていた場合、有効期限が切れることでそのカードが無効化されます。

③引き落としに気づく機会を与えている
引き落とししていた手続きを新しい有効期限に切り替える手続きをする際に、自分がどういった引き落としをしていたのかという事に気づきます。見直しする良い機会です。

カードの更新時期が近づいたとしても、使用していないカードであれば解約しても良いかもしれません。無駄に保有していても年会費が取られるだけで良いことはありません。カードの存在を忘れ、年会費を払い忘れてしまいクレジットカードヒストリーに傷が付いてしまうかもしれません。使い勝手の良いカードだけを手元に残し有効活用して下さい。

カテゴリー
カード活用 クレジットカード

貯めたポイントの使い道

クレジットカード払いにしたときのポイントの使い道を紹介します。

貯まるポイントは主に3種類あります。
・自社サイトのみで使うポイント
有名なのは楽天カードで貰える楽天ポイントです。
楽天が運用しているサイトであれば大抵の支払いであればカードで貰ったポイントで支払いが可能になります。特に商品が膨大にある楽天市場では何でも売っているので使い勝手が良いですね。最近では楽天ポイントを使って投資ができるサービスが始まったみたいです。ポイントを投資して利益が出れば戻ってきます。貰ったポイントをさらに増やすサービスです。元金?は貰ったポイントを使って投資するようなもので気軽に始められますね。

・他社が発行しているポイントに変換する
クレジットカード会社から貰ったポイントを他の会社が発行しているポイントに変換できます。変換割合は会社によって異なりますが、貯めているポイントがある場合一本化させたいときに便利なサービスといえます。

・商品カタログから好きな商品を選びポイントと交換する
カタログギフトのようなもので、ポイントを消費して好きな商品と交換できるものです。普段自分では購入できないグッツや食べ物があれば試せる良いチャンスとなります。上の二つと比べるとポイントの使い道が狭まってしまいますが、ポイントの使い道はあまり重視してなければここでこまめに交換しても良いですし、高額商品と交換する事を目的で貯めるのも楽しみになるかもしれません。一番気軽に貯められるものかもしれません。

管理人がよく利用するのは一番上の自社のみで使うポイントになります。ポイントは基本的には貯めていますが、楽天市場でちょっとした日用品を購入するときや、知人にプレゼントを購入するときにポイントで支払ったりしています。楽天だとポイント支払いによる決済だとその商品に対するポイント付与がなくなってしまうので、ポイントが付かない商品を買うか低額な商品を購入してポイントを貰えなくなるチャンスを最小限に抑えています。今後はポイントを使った投資にチャレンジしてみようかなと考えています

カテゴリー
クレジットカード 用語集

カードの一括払い/1回払い

購入した商品を1回(一括)で支払うことを言う。銀行口座から引き落とされるタイミングは、購入した店の売上がカード会社へ決済情報が伝わったとき。カードの締め日に合わせて引き落としされる。締め日の間際でカード払いした場合は、翌月ではなく2ヶ月後になる場合もある。
1回払いだと通常に現金で購入したのと同じで利息の支払いはない。カード払は基本的にこの1回払いで済ますのが良いが高額な商品を購入した時に月々の返済を楽にするために分割払いやボーナス一括払いの支払い方法も用意されている。

カテゴリー
クレジットカード 用語集

カードの後払い方式とは

クレジットカードの支払い方法の事をいう。
商品を購入したときにその場で支払うのではなく、クレジットカードを使って翌月の決められた日に預金口座より支払いします。購入時に現金を持っていなくても買い物ができるため金銭感覚が薄れる方もいますが購入したものは必ず支払わなくてはいけません。
後払い方式ではない支払い方法としてプリペイドカードが用意されています。プリペイドカードはカードを使用したタイミングで決済されるので現金と似たような扱いになります。その代り口座に資金をおいておかないと決済ができなくなります。

カテゴリー
カード活用 クレジットカード

クレジットカードを徹底的に使う12の方法

クレジットカードを徹底的に使う事を想定してみました。

普段の生活費をカード払いにして効率良くポイントを貯めてちょっと贅沢なご褒美をしてみませんか

概要

年間で120万円の支払いを全てカード払いにすれば1パーセントのポイントが付けば1万2千円分のポイントが獲得できます。支払い方法を少し変えただけでこれだけのポイントが貰えば嬉しいですよね

公共料金の支払・・・年間12万円

水道光熱費の支払をカード払いにします。

毎月、実家以外に住む場合は必ず水道光熱費が発生します。

1人暮らしで1ヶ月の水道光熱費は大体1万円程度になります。家族で住んでいても2万円程度にはなるかと思います。銀行引き落としかコンビニ払いで支払っているかと思いますが簡単に切り替えできるのでオススメです。

携帯電話代、インターネット代・・・ 年10万円

携帯電話の平均的な支払額は5000円から6000円程度です。

年間にすると意外と大きな出費となりますが、現代では必需品となった携帯ですのでこの費用は無くすことができないですね。自宅に固定回線(インターネット)を引いている方も必要経費となりますので是非カード払にしてください。

家賃 駐車場代・・・年78万円

取り扱っている不動産会社によりますが、家賃払いをカードOKとしているところもあります。月6万5千円の家賃として計算すると年間で78万円にもなります。今借りている不動産会社がカード払いに対応しているか確認してみて下さい。また、これから引越しを考えている方は不動産会社がカード払い可能かどうかも家選びの選考基準にしてみると良いと思います。もしカード払いにできればラッキーです。

交際費・・・年間12万円

仕事の付き合いや友人、恋人との飲食の支払いをカード払いにします。

交際費は付き合いの頻度によったりカード払いにできない店もあるので月1万円程度としました。仕事の付き合いで飲食してカード払にすると同僚から冷たい目で見られるかもしれません。(お金の回収の煩わしさやポイント目的と思われる)これを避ける為には数回に一回程度にするか、積極的に幹事をしてお金のまとめ役になれば何も思われなくなります。人数が多い飲み会だと支払額も大きくなりますので、ポイント獲得の大きなチャンスになります。

日常の買い物・・・年間24万円

車のガソリン代、日常の買い物、ネット通販の買い物をカード払いにします。個人経営店ではカード扱いしてない所もありますが、大型店舗ならほぼカードを使えます。スーパーでちょっとした買い物や出先で飲み物を買うなどで積極的にカードを使えばコツコツと貯める事ができます。ネット通販でカード払いするのは怖い方は、誰もが知っているような大手のサイトや大企業が運営している会社のサイトで使うといった自分ルールを作れば安心して使えると思います。

通勤定期代、出張旅費代・・・年間15万円

会社から支給される通勤交通費(電車、バスの定期代)をカード払いにします。また会社の命令で遠方に出張することになり自分で出張先までの切符や宿泊宿を手配すると結構な精算額になると思います。会社の費用を個人のカード払いにすることについては会社それぞれの考え方がありますので、事前に上司や同僚に相談してみると良いと思います。

税金関係の支払

税金の支払もカード払いにできます。住民税と源泉税を納められます。ネットで事前に手続きした上で支払いができるようになります。手数料がかかるので税金の支払額の全額に対してポイントが付くと考えない方が良いですが、少しは残るかな?といった程度です。税金の支払いは普通のサラリーマンをしている方であれば住民税と源泉税は給与から毎月天引きされますしあまり関係ないかもしれません。今年は少し収入があったから確定申告して税金を納めなければいけなくなったとなったときに、カード払いができる事を思い出してみて下さい。税金の支払でメリットを受けるのは、決済をカードの引き落とし日まで伸ばせるところにあります。

国民年金

こちらもサラリーマンをしている方は会社から天引きされているのであまり関係ないかと思います。国民年金もカード払いにできます。フリーランスや大学生の方で給与天引きされてない方はぜひカード払いにしてみて下さい。

保険料・・・36万円

任意で入る医療保険料、自動車保険、火災保険をカード払いにします。個々の保険料は月に数千円程度かも知れませんが、色々な保険を合算すると月に数万円支払っているかもしれません。こちらも削る事ができない固定費ですのでカード払いは必須となります。

旅行代金・・・10万円

旅行会社に支払う旅行代金、車で移動する際の高速道路の費用をカード払いにします。高速道路ではETCカードがあり高速道路専用のクレジットカードが用意されています。1人当りの旅行代金は数万円でも複数人で行けば10万円を超える事もあると思います。またカードによっては旅行代金の支払いをカード払いにするだけで保険が自動的に付与されるものもあります。万が一トラブルがあっても安心できると思いますのでカード払いをオススメします

ふるさと納税

ふるさと納税をカード払いで決済できます。毎年利用している方はぜひ使ってみて下さい。ふるさと納税自体が節税対策みたいなものですが、意外なことにカード払いに対応しているところもあります。

病院の医療費

病院で治療したときの医療費をカードで払える病院もあります。大きな病院か新しい病院だと対応していることが多いです。個人経営の小さな病院や古い病院だと対応していなところが多い印象です。急いで治療する必要がなければ一度調べてから行くと良いかもしれません。病院の医療費扱いはポイント対象外になる場合もありますので確認してみてください。

最後に

年間でこれら10項目の支払い全てをカード払いにできれれば相当のポイントを貯める事ができますね。金額を書いた項目を足すと180万円近くになります。ポイントを貰えるのは18000ポイントにもなります。今の時代、銀行に180万円を預けても利息がこんなに付かないので、ポイント還元率の良さを実感できると思います。お金を使って還元されるのは不思議な感覚かもしれませんが、ちょっとしたお小遣いを貰った気分になりますね。

ポイントはクレジットカードの最大のメリットといえますが、ポイントを貯める事を目的で買い物はしないようにして下さい。せいぜい1パーセント程度ですので、リターンが少なく損してしまいます。必要な支払いのもので意識的にカード払いを進んで使って下さい。カード払いにすると暗証番号を入力したり、サインしなければいけない事がありますが、ポイント獲得の為の手続きだと思って事務的に対応するしかないです。ポイントはカード会社からのささやかな御礼といった程度で受け取り、気付いてたらこんなに貯まっていてラッキーという程度に留めて置くのが良いと思います。