使える関数～VLOOKUP関数

VLOOKUP関数は、セルに入力した値を元に指定したデータ範囲から指定列番のデータを検索し、表示する関数です。引数は次の通りです。

VLOOKUP（①検索値,②検索に使うデータ範囲,③列番号,④検索方法）

どんな時使うかというと、名簿を作成する時コードを入力すると自動で名前が表示されるとか、あるいは見積書を作る時、商品コードを入力すると商品名や単価が自動で表示されるなんていうのも考えられますね。

だいだい、企業では顧客や商品のデータは社員が使えるようにデータベースとして保管していると思います。個人の方でもいつも使う商品のデータなどはExcelとかに入力してますよね。そのデータを有効に使えたら仕事が楽になります。

実際の使い方を説明していきます。

VLOOKUP関数はデータ入力をする範囲と検索の対象となる範囲（参照範囲）と２つ使います。
このような表を作ってみました。

※図はクリックすると大きくなります。


注文書と商品の一覧データです。

注文書の商品コード欄にコードを入力すると商品一覧のデータ範囲から検索し自動で商品名と単価を表示します。

まずは商品名から設定しましょう。

商品名の欄「B3」を選択します。

「＝」に続けて「VLOOKUP（　」まで入力します。（「vlookup」のように小文字でもかまいません。）




最初の引数は①検索値です。検索値は商品コードを入力するセル、この場合は「A3」になります。マウスで「A3」をクリックします。そして「,」で区切ります。

次はセル「A3」に入力された検索値を元に②検索するデータ範囲を指定します。これは商品一覧になりますから、マウスで「G2」から「I5」をドラックします。（選択した範囲が点滅します）

このデータ範囲は以降変わらないのでこの状態で「F4」キーを押して絶対参照（わからない方は絶対・相対参照の回をご覧ください。）にします。「$G$2:$I$5」になればOKです。そして「,」で区切ります。



次は、表示する列番号を指定します。これは直接数値を入力します。今回表示したいのは商品名ですね。

検索するデータ範囲に指定した「商品一覧」の表に注目すると（下の図）、商品名は商品コードの次の列、２列目ですね。



ですので「2」と直接入力します。「,」で区切ります。



最後は検索方法です。この方法は２種類あります。完全一致と近い値を含めて参照する方法です。

完全一致はその名の通り検索値が商品一覧の商品コードと一字一句同じでない場合はエラーになります。

そして近い値を含めて参照する方法は、もし検索値と同じ値の商品コードがない場合、最も近い値のデータを表示します。いわゆる「あいまい検索」です。

今回は商品名や単価なので、完全に一致するものでなければいけません。あいまいでは困ります。したがって完全一致を意味する「FALSE」という文字を入力します。めんどくさい方は数字の「０」でも同じ意味です。

あとは「）」で閉じてください。

ちなみにあいまい検索の場合は「TRUE」という文字を使います。めんどくさい方は「１」でいいです。
ただ、文字で覚えておく方がいいですよ。なぜなら突然どっちが「０」でどっちが「１」かわからなくなったりします（笑）。

図のように入力されていればOKです。

図をクリックし、大きく表示して確認してみてください。



図を見ながらもう一度おさらいします。

検索値「A3」を元にデータ範囲「G2」から「I5」の間で検索し、完全一致した行の２列目のデータを表示する。という意味になります。

言葉で覚えるのも非常に大事です。自分で言いながら図をなぞってみてください。

Enterキーを押します。



今のところ検索値セル「A3」に商品コードが入力されてないのでエラーになりましたね。

では正しく入力されたか試してみましょう。

「A3」にりんごの商品コード「１０１０」を入力！



きちんと「りんご」と表示されました。

このように、検索値を元にデータ範囲から完全に一致するデータを行方向に探して、見つかったら指定された列番に入力されている値を返す関数がVLOOKUP関数です。言葉だとややこしいですね。



図でもややこしいか（笑）。イメージはこんな感じで覚えてください。

では、単価の欄もVLOOKUP関数を入力しましょう。

「C3」を選択して、データ範囲までは商品名を作ったときと同じです。違うのは列番号ですね。単価は商品名の次の列３列目ですので「3」と入力し、完全一致の「FALSE」でOK！



入力したらEnterキーを押します。



見事りんごの単価「50」が表示されました。

それでは注文書を完成させましょう。

金額の欄には「単価」×「数量」の数式「C3*D3」を入力しましょう。



数量の欄に適当に数字を入れて出来上がりです。



関数と数式の入ったセルを２～３行ほどコピーして、商品コードや数量を変えて入力してみました。



この関数を使うと、商品コードと数量のみの入力で済みます。作業の効率がかなりアップしますね。

今回は同じシートに入力用（注文書）と参照用（商品一覧）の表を作ってみましたが、参照用を別なシートに作ってもできます。

新規にデータを入力する時はVLOOKUPを思い出してくださいね。

作業が二度手間にならないよう、普段から顧客や商品のデータを効率よく使いましょう。

使える関数～IF関数

IF関数は指定した条件を元に表示する値を返す関数です。引数は次の通りです。

IF（①条件の数式,②条件を満たしたときの処理,③満たさなかったときの処理）

どんな時使うのかなあ・・といろいろ考えました。やはりデータのチェックに使うのが一番わかりやすいかなと思います。

こんな表作ってみました。

※図はクリックすると大きく表示されます。


３年B組の中間テスト５科目の結果です。

このテストの結果、平均で７０点以上をAランク、それ以外はBランクで評価したい。

それでは評価の欄に実際にIF関数を入力していきます。必ず「＝」から始めます。

まず条件ですが、５科目の平均ですから、「合計点数/５」という式が考えられますね。

さらにその平均が７０点以上ということなので、条件式は「C4/5>=70」となります。「C4（合計点数）を５で割り、その結果が７０以上ならば・・・という意味になります。「＞＝」は比較演算子といいます。～以上という意味です。

そして、その条件を満たしたとき、Aランクになりますので、「,」で区切り、「"A"」と入力します。文字を表示する場合は必ず「"」（ダブルクウォテーション）で文字を囲みます。それ以外の場合は「B」ですので、「,」で区切り、「"B"」と入力します。
（入力するとき「if」ように小文字でも気にせず入力してください。正しく入力されていれば、自動で大文字に変換されます。）



「Enter」キーを押します。

入力が間違っていなければこのように表示されます。

鈴木陽子さんは平均７０点以上なのでAランクですね。



あとは下方向へコピーしましょう。



出来上がりです。

平均７０点以下はBランクになってます。それぞれ正しく評価されてます。

今回は２つの評価でしたが、３段階に評価したい。このような欲求も当然出てきますよね。そういう場合は、関数をネストするという方法があります。ネストとは重ねるという意味です。IF関数を重ねて条件を何個も指定することができます。

それでは、７０点以上をAランク、６０点以上をBランク、それ以外をCランクとするIF関数に修正しましょう。

「IF(C4/5>=70,"A",」ここまでは同じです。ここから関数のネストになります。６０点以上はBランクですので、またIFから始めます。「IF（C4/5>=60,"B"」そして２つの条件を満たさなかった場合はCランクというこで、「,"C"」と入力します。最後「))」となっているのは、IF関数を２回重ねている（ネスト）からです。もし３つ使ったら「)))」となりますからね。

この関数の意味をおさらいです。

１つ目の条件は平均が７０点以上ならAランク、その条件を満たさない場合で６０点以上ならばBランク、それ以外はCランクという意味になります。



入力できたらコピーします。



これで３段階評価の完成です。

どうですか？便利ですよね。

このパターンで４段階５段階の評価も可能です。

この評価を使ってクラス分けしたりもできますね。あとは、会社だと各部門の売上や費用のチェックなどにも使えそうですね。

どんな場面に使えそうか、いろいろ考えてみるのも面白いですね。コメント欄に遠慮なく書き込んでください。いい意見はみんなで共有しましょう！！そして支配されない自立するお仕事をしましょうね。

使える関数～絶対・相対参照

関数と聞くだけで、なんか難しそうという方はけっこう多いです。確かに、使う関数によっては何度解説を聞いてもわからないものもあります。ただ、すべての関数を使いこなすというのは、Excelがかなり得意という方でも無理です。ですから普段自分の仕事に使えそうな関数を徹底して覚える、というのがいいと思います。

ということで、「使える関数」シリーズでは、思いついたら何個でも「これは便利！」という関数を紹介していきたいと思います。

では、さっそくと言いたいところですが、今回はまだ関数そのものの紹介はしません。まず関数を覚える上で覚えておく事があります。それは絶対参照と相対参照です。

関数は、難しい計算や検索に対し、条件（引数といいます。）を指定することによって簡単に値を算出または取得できるという、お得な入れ物です。

この引数に指定するデータ範囲の参照方法に絶対と相対という考え方があります。

まずは相対参照です。相対とは辞書によると向き合っていることとか、他との関係において存在するという意味だそうです。（難しいですね。）

このような表があります。

A地区とB地区の合計を出す場合はセル「C2」にSUM関数を入力します。そのSUM関数の（）内に入れるのが引数です。この場合は（A2:B2）というデータ範囲が入ります。



A2とB2が合計されます。

あと2行ありますから、セル「C2」を下方向へ2行コピーします。

そして、2行目「C3」を選択してみてください。先ほど指定したデータ範囲が自動で（A3:B3）になってませんか？



もし、セル「C2」に指定した、SUM関数の引数のデータ範囲が変化せずコピーされていたら、答はみんな同じになってしまいますね。

このように通常Excelでは、関数や数式が入力されたセルをコピーすると、コピー元からの行列の移動数によって自動でデータ範囲が変化します。いわゆる相対的に変化するわけです（えらそに言ってみました）。

今回の例で見ると、一行下にコピーした数式はセル「C2」のデータ範囲（A2：B2）に1行プラスした範囲（A3:B3）に変更されてますね。これが相対参照です。これは普段意識しなくてもExcelが自動で行ってくれます。

ところが相対参照では困る場合があるんです。その例がこちらの図です。

ここでの計算式は、全体に占める各商品の割合を算出するので、A品の場合、A品の在庫数割る合計の在庫数となります。式は図のように入力します。（％表示にしましょう）



できたら、相対参照の時と同様に下方向にコピーしてみましょう。





エラーになりましたね。

これは０除算エラー。つまり、なにも値が入力されていないセルを使って割算しようとしているという意味です。

なぜこうなったか。それは前項で説明した相対参照が関係しています。

通常Excelでは、数式や関数が入力されたセルをコピーすると自動で相対参照になると説明しました。この場合も下の行に向かってコピーをしたので、当然数式は相対的に変化し、A品の数式に１行分プラスした数式になります。

B品の占有率を算出するにはB品の在庫数割る全体の合計、「=B3/B5」です。ですが実際は「=B3/B6」となっており、「B3」は相対で変化して正しいですが、割る側の合計数のセルも１行下のセルへ変更されていしまいました。なにもデータが入力されていないセルを計算に使用してしまっています。これではエラーになりますね。



この場合、全体の合計のセルは相対的に変化しては困ります。絶対に動いてはいけない、そう絶対参照にしなければならないのです。

それではもう一度A品の数式を修正しましょう。

やり方は簡単です。まずは「＝B2/」までは同じです。次に絶対参照の対象となるセルの値「B5」を入力した後、「F4」キー（キーボードの上段にF1～F12まであります。）を押します。


そうすると「$B$5」なりませんか？この＄マークは固定の意味を表します。これで合計のセルの位置は絶対動かなくなります。



あらためて、下方向へコピーすると、今度はエラーも出ずに正常にコピーされました。



ちなみにB品の数式を確認してみると、しっかり合計セル「$B$5」は固定されてますね。


絶対参照とは、計算の対象となるセルまたは範囲を固定すること、つまり絶対に動かない参照先のことです。

相対参照と絶対参照は関数を使いこなす上で必要な知識ですので、がんばって覚えましょう。

※ちなみに、列を絶対参照にして行を相対参照するなどの複合参照というのもあります。これも後日実践編で説明します。

データベース化

Excelで仕事というと請求書や見積もり、日報、月報や在庫の集計。ちょっとした計算式が入っていて、型が決まったものに入力するというのが定番です。その場限り対応してるというのが多いですね。

例えば請求書や見積もりの場合、毎回、名前を修正して、明細を入力し直して印刷してたり、会議の資料も毎回、同じ表の金額を修正して出したりと・・・・。
もちろん緊急で作成しなきゃならない資料もありますから、すべてがダメということではないですよ。やはり普段からの積み重ねが、いろんな資料になって作成されるというのが理想です。請求書の名前は顧客のリストから自動で表示されたり、月次の資料は日々発生する伝票のデータを集計していつでも出せるというのがいいですよね。

そのためには、普段から必要と思うデータはExcelを使ってデータベース化しておきましょう。データベースというと難しいと思う方もいるかもしれませんが、ぜんぜん簡単です。



こんな感じで必要な項目を設定してデータを入力しておけばいいんです。（図では取引先を入力しています。）
このように同じ目的をもったデータをデータベース化しておくことによって、後々いろんなものに化けます。
例えば図のように取引先を入力しておくことにによって、見積もりや請求書先を自動で表示したり、封筒に住所を印刷したり様々な用途が想定されます。

次回のExcelはデータベース化したデータを使い見積もり書を作成してみます。

Wordで名刺印刷

第一回目の投稿です。このブログはパソコンでお仕事をする方が、必ずと言っていいほど使う、Office製品（Word,Excel,PowerPoint,Access）の小ネタから大ネタまでを紹介できればと思い作りました。
ブログ形式にして、日々気づいたことや、「これは便利だ！」と思う機能をどんどん更新していきたいと意気込んでおります（笑）。よろしく。

さて本日は、一回目ということで名刺代わりにWord2007で名刺印刷（笑）。
会社勤めの方は印刷屋さんに発注してしまうなんてのが普通ですが、自営業の方や会社勤めでも名刺は自腹でなんて方は自分で名刺をつくりましょう。

名刺専用の用紙はK'ｓ電気等で買ってきてください。一応１枚に１０面のパターンを想定しております。メーカーは「KOKUYO」とか「A-ONE」というのが一般的です。だいだい１０枚（１００枚）くらいで１，０００円しないと思います。たまにプラスティックみたいな素材で３枚（３０枚）くらいで１，２００円とか、高級なものもありますので店員さんに確認してくださいね。

まずWordを起動しましょう。

①起動した最初の画面は名刺には使用できないので、テンプレート（ひな形）を使うと楽ちんにできます。

左上にあるオフィスボタンをクリックして、表示されたメニューから「新規作成」をクリックしましょう。

②左側の領域「Microsoft　Office Online」の下（５行目くらい）に「名刺」という項目があるのでクリックします。（インターネットへの接続がされているこを前提にしています。）



③真ん中の領域に何面のテンプレートを使うか表示されます。今回は「１０面」を選択してください。そして次に表示される「A-ONEセレクトシリーズ～」を選択後、右下のダウンロードボタンをクリック。
（他のメーカーのテンプレートもいろいろ用意されてます。）




このように１０面に区切られた入力画面が表示されます。これでもう準備はOKです。


④一番左上の枠に会社名や氏名、住所、電話番号、メールアドレスなど入力します。文字種や文字の大きさ、配置はいろいろ考えて入力しましょう。
わたしの名刺はシンプルに作成しました。背景に色や模様を施してもいいでしょう。ただあまりカラフルにするとインクを使いすぎてしまいますので注意したほうがいいですね。
私はOffice製品のインストラクターをしているので、インターネット上からOfficeや自分の取得した資格のロゴマークをコピーして貼り付けてます。会社の方は自社のホームページから自社のロゴをコピーするのもいいですね。

それから、Wordにはクリップアートという強い味方もあります。自分のイメージにあったイラストや画像を使いましょう。



⑤体裁が整ったら、入力した枠を他の枠へコピーして出来上がり。（一応保存しましょうね。）
あとは、紙をセットして印刷！ １枚１枚切り出せるようになってます。



インターネット上には様々なサービスがあります。画像やイラストは、GoogleやYahooで「フリー素材」とか「無料アイコン」等で検索するといろんなサイトが表示されます。ダウンロードして名刺やはがき、ラベル等に使ってみてください。著作権の関係がありますから、必ず「使用フリー」というものをお使いください。

ちなみにこんなサイトありました。


いろいろ使えそうですね。