今回は、Excelの基礎知識第3弾です。
お題は、関数(かんすう)と比較演算子(ひかくえんざんし)です。
第2弾から読みたい場合は、下のリンクから読んでみて下さい。
なんだか、難しそうな名前が出てきましたね。
でも、難しいのは漢字だけです。
この関数と比較演算子は、Excelになくてはならない機能で使いこなせば複雑な条件での計算などが超絶的に早くなります。
私は、よく電卓片手にExcelを使って簡単なシミュレーターを作ることがあります。
上手く出来るともう手放せなくなるくらいです。もう中毒ですね。
もちろん、危険性は0%ですよ。
初心者のうちから抑えておきたい「比較演算子」と「関数」について早速触れていきましょう。
Here we go!!!!
目次を使って読みたい項目へ
便利な「見積書」を作ってみよう！！
さて、今回は、自動計算をしてくれる【便利な見積書】を作ってみましょう
見積書は、第1・第2弾で紹介した方法でも作れるのでチャレンジしてみたい場合は、最初から作ってみてもOKです。
そこからは大変だなと思ったら、今回も練習用のファイルを用意しているのでダウンロードしておいて下さいね。
「ミニ四駆パーツの見積書」をダウンロードする
今回実際に作る「見積書」はこんな感じです。
見積書の「コード」を入力すると、「品名」・「単価」が自動的に表示されて、数量を入力すれば、「税額や合計金額」を自動的に計算してくれる機能を作っちゃいます。
なんとなくで良いので完成版をイメージしておきましょう。
今回の見積書は、無料のテンプレートを元に作成させていただきました。
また、ミニ四駆のパーツの単価などは、「TAMIYA様」の公式ホームページより、流用させていただきました。
▼ 見積書のテンプレートは、【board様】公式サイトよりダウンロードさせて頂きました。
https://template.the-board.jp/estimate_templates/article/estimate_template_001
▼ミニ四駆の各パーツデータは、【TAMIYA様】の公式サイトより流用させて頂きました。
https://tamiyashop.jp/shop/c/c303030_srd/
準備ができたら、早速作業をしていきましょう！！
関数(かんすう)って一体何？
Excelを使いこなせるようになるには、関数を覚えましょう。
じゃあ、そもそも関数ってなんでしょう？ 学生時代に数学でやらなかったっけ？ と思うかもしれませんがExcelの関数は、数学の関数とは少し違います。
関数とは「=関数名()」という式の中にセル範囲を指定してあげると「関数の種類に合わせて計算結果を表示してくれる機能」のことです。
基本的な説明は、前回の第2弾も参考にしてみて下さい。
最初は、難しいと感じますが落ち着いて基本を抑えてしまえば、使い方はどの関数もほとんど同じです。
関数には、たくさんの種類がありますが最初に覚えるのは3つで十分です。
- SUM(サム)関数
- IF(イフ)関数
- VLOOKUP(ブイルックアップ)関数
この3つの関数は、よく使う関数であり抑えておけば色々なことができます。
今回は、SUM関数とVLOOKUP関数の使い方について触れていきますね。
応用すれば、難しい計算や複雑な条件でもサクッとこなしてくれますが、まずは、基本的な使い方をマスターしちゃいましょう。
実際に関数について基本を抑えてみましょう。
SUM(サム)関数を使って関数の基本的な使い方を覚えよう
まずは、一番最初に覚えておきたい「SUM(サム)関数」を使って基本を抑えちゃいましょう。
早速、SUM関数を設定して使い方から覚えてみましょう。
①商品A～商品Cの合計金額を「E3セル」に表示させましょう。
まずは、「E3セル」をクリックしておきましょう。
②「数式タブ」の「関数ライブラリグループ」の「数学／三角」ボタンをクリックして、「SUM」をクリックしましょう。
③「関数の引数ダイアログ」が表示され、「数値1」に「A3:D3」と自動で入力されます。
「A3:D3」の意味は、「A3～D3」のセル範囲を対象(合計)にするよという意味ですね。
ただ、今回は「A3～C3セル」の合計を出したいので、選択されている範囲が違うので修正しましょう。
④合計範囲を修正するには、シートのセル範囲をドラッグします。
今回は、「A3～C3のセル」をドラッグしましょう。
⑤「数値1」の内容が「A3:C3」に変更されればOKです。
⑥仕上げに「OK」ボタンをクリックしましょう
⑦「E3セル」に合計金額が表示されれば完成です。
「数式バー」に「=SUM(A3:C3)」と入力されているので実際は、「\27,529」という金額ではなく「関数という式」が入力されていることが分かりますね。
さて、SUM関数の使い方は分かってもらえましたか？
今、紹介したのはダイアログを使った方法ですが、実は関数というのは直接入力して使うこともできるんです。
というよりも、私はダイアログで覚えるよりも直接入力する方法で覚えることをオススメします。
理由は、関数の場所を覚える方が大変だからです。
関数の種類は、現在500個近くあります。
もちろん。全ての種類を覚えるのはほぼ不可能で、必要な関数を「数式タブ」から選ぶ場合は、関数のある場所を覚える必要があるので大変ですよね。
初心者の方や私のように覚えるのが苦手な人にとっては、場所よりも入力方法を覚えた方が楽だったりします。
もちろん。ダイアログで覚えた方がいい場合はそれでも良いんですが、将来的にネスト(入れ子)と呼ばれる応用的な使い方をする場合など、複雑になるほど面倒になってしまうので、直接入力する方法がオススメです。
それでは、関数を直接入力する方法をみていきましょう。
特に難しいことは無いので心配無いですよ。
ただし、一つ注意点があります。それは、必ず「半角」で入力することです。
Excelでは、半角英数で入力するということが大切で、「全角=文字」「半角=数字や数式」という扱いになるので、覚えておきましょう。
では早速、関数を直接入力してみましょう。
ダイアログと同じ設定をしてみます。
①「E3セル」に「=sum」と入力しましょう。
そうすると自動的に「sum」から始まる関数が一覧で表示されえるので、目的の「SUM」をダブルクリックします。
この時に「sum」の文字は小文字で大丈夫です。
もし、余裕があれば、十字キーの「↓」で関数を選んで「Enterキー」で決定しても結果は同じです。
慣れるとキー操作の方が作業が早いので覚えておいても良いですね。
②選択すると、自動的に「=SUM(」と入力されます。
③合計したい「A3～C3」のセルをドラッグします。
操作と同時に「=SUM(A3:C3」と自動で入力されます。
④ 範囲の選択が終わったら「Enterキー」を押して終わりです。
ちゃんと、「E4セル」に合計額が表示されていればOKです。
⑤ちゃんと設定できてる証拠に「E3」セルをクリックすると、数式バーに「=SUM(A3:C3)」と入力されていることが分かりますね。
説明が少し長くなってしまいましたが、こんな感じで関数は直接入力した方が早い気がしませんか？
一般的には、ダイアログを使って関数の設定をするのが初心者さん向けと言われていますが、私はこちらの方法をオススメします。
他の関数でも基本操作は変わらないので、何度か練習して感覚を掴んでおきましょう。
見積書に自動計算機能を追加してみよう！！
関数の基本的な使い方を覚えたら、実践あるのみです。
さっそく「見積書」に自動計算機能を追加して実用的な関数の使い方をマスターしちゃいましょう。
まずは、基本的な内容を確認してみましょう。
今回は、オレンジ色のエリアは手動で入力します。
赤色のエリアは、関数を入力して自動的に「品名」を表示したり、「金額の合計」を計算してくれるように設定します。
今回は、「コードの列」に「任意のコード番号を入力」すると、品名が自動的に表示されるように設定します。
「コード番号と品名・金額」は、見積書の右隣に別途用意しておきます。
見積書の自動化をするためには、例えば、コード「123」は「商品A」で「￥12,345」ですよという一覧表が必要です。
品名の自動表示をさせるためには、VLOOKUP関数を設定すれば出来るんです。
実際に設定すると品名が自動的に表示されます。
見ただけでは、普通に入力したように見えますが、「数式バー」には実際に入力されているVLOOKUP関数がちゃんと表示されています。
では、実際の設定方法のコツとVLOOKUP関数の使い方について見ていきましょう。
VLOOKUP関数の基本
SUM関数もVLOOKUP関数も基本は同じですが、設定出来る条件が多いので難しく感じるかもしれません。
ただ、そこまで構える必要は無いんですよ。
基本さえ抑えてしまえば、使い方は簡単なのでまずは、基本を抑えちゃいましょう。
VLOOKUP関数の使い方と仕組みはこんな感じです。
今回の場合は、「A18セルに入力されたコード番号」を右側のコード対応表の中から、調べてきてねという設定です。
調べる範囲は、「S18～U46のセル範囲」つまりコード対応表の範囲を対象にしてね。
という部分が、【VLOOKUP(A18,$S$18:$U$46】ココまでの式です。
検索の仕方は、こんな感じです。
この場合は、「123」というコード番号を コード対応表の一番上から順番に下方向に向かって調べます。
ココでVLOOKUP関数の重要な決まりがあります。
それは、参照したい表(範囲)の「左端の列に検索する番号などを入力すること」です。
ということで、コード対応表の一番左には 必ず 「コード番号」を入力する必要があります。
次に、VLOOKUP関数がどうやって「コード番号だけで品名を表示できるのか？」 という仕組みについて整理してみましょう。
まず、今回指定した範囲は、 「$S$18:$U$46」という範囲でしたね。
これは、「S18～U46のセル範囲」という意味です。
じゃあ、「$(ドル)マーク」は一体何でしょう？ まずはこの記号の意味を整理してみましょう。
「$S $18 : $U $46」
範囲部分をバラしてみるとこんな感じです。
「$＋列記号 $＋行番号 ： $＋列記号 $＋行番号」こういうルールです。
まず、「：」のマークの前半の「＄S」と「$18」はセットで覚えます。
「＄S」→「S列に固定」してねという意味です。
「$18」→「18行に固定」してねという意味です。
後半も同じ意味なので、「S18～U46」の範囲を固定してねという意味になります。 これを「絶対参照」と呼びます。
絶対参照については、別の機会で詳しく説明しますが、今回は検索範囲を固定する方法なんだということを覚えておけばOKです。
ココまでで参照範囲(調べて反映させたい値が含まれた範囲)の指定まで終わりました。
次は、参照範囲のどの列から「値(あたい)」を調べるか？ ということを指定します。
値とは、文字・数字・記号などそのセルの内容の事です。
今回の場合は、「値=品名」ですね。
じゃあ、品名が入力されている列って何列目でしょうか？
答えは、2列目ですね。
列を指定するには、「=VOLLKUP(….,2,.)」の部分です。
これは、指定した範囲の左から2列目の値を調べて表示してねという意味になります。
なのでこの式を 「=VOLLKUP(….,3,.)」にすると、「単価」が表示されるようになります。
分かりやすいように図解にてみましょう。
例えば、コード「987」を調べる場合の動きは、①の縦方向に番号を調べて、該当する番号を見つけたら、左から２つ目の列に入力されている品名の②の方向に移動して「品名」を見つけてくれるという仕組みです。
最後の項目を整理しましょう。
「=VLOOKUP(….,.,0)」最後の「0」の意味です。
これは、どんなルールで検索するか？ という条件を設定しています。
「0」→「完全一致」、「1」→「あいまい一致」という意味です。
もしくは、 「False」→「完全一致」、「True」→「あいまい一致」 という使い方でもOKです。
ただ、「完全一致」の場合は、検索する「値」は昇順で並んでいないとエラーが表示されてしまうというルールになっているので気をつけましょう。
基本的には、検索の値に「0」を設定しましょう。
「あいまい一致」の場合は、似たような値があると間違えて検索してしまうことがあるので「0」を設定しましょう。
以上で「VLOOKUP関数」の基本的な使い方は以上です。
では、VLOOKUP関数の入力方法と関数のコピーの方法を見てみましょう。
品名を自動的に表示させる仕組みを作っちゃおう！！
①「B18のセル」に半角で「=vloo」と入力するとその下に「VLOOKUP」と表示されるので文字をダブルクリックします。
※関数を入力するときは、直接入力の小文字で入力しましょう。
②自動的に「VLOOKUP」と入力され、すぐ下に「VLOOKUP(検索値,範囲,列番号,[検索の方法])」と入力のヒントが表示されます。
(太字部分は今入力している設定場所はココだよ。というお助け機能です)
このヒントを上手く使うと初心者でも関数の理解が比較的簡単にできるようになるので、慣れておきましょう。
③まずは、「検索したい値」を設定するので、「A18のセル」をクリックします。
その後に、「,(カンマ)」を入力しておきましょう。
この「,」は次の設定をするよという区切りの意味です。
④次は、範囲の設定なので、右側の「コード対応一覧表」の範囲である「S18～U46のセル」を範囲選択しましょう。そのまま次の操作にいきます。
⑤ココで「F4キー」を押します。
すると、絶対参照が自動的に設定されます。
もし、「F4キー」を押し過ぎて表示方法がおかしくなってしまったら、慌てずにゆっくり「F4」キーを何度か押して絶対参照の表示になるまで押しましょう。
設定が終わったら、「,」を押して次の設定に進みましょう。
⑥次に「2」と入力します。
2列目の品名を表示させたいので入力はこれだけです。
「,」を入力して次の設定に進みます。
⑦次は検索の方法を設定しますが、自動的に「TRUE」か「FALSE」を選択するように入力補助機能が表示されますが無視します。
ココは、直接「0」と入力します。
これ以上設定するものが無いので「,」は入力しません
⑧仕上げに「)」を入力して完成です。
「Enterキー」を押して結果を確認しましょう。
⑨きちんとコードに対応する品名が表示されればOKです。
⑩オートフィル機能を使って関数をコピーします。
セルの右下にマウスカーソルをあわせて「+」になったら、29行目までドラッグします。
この「+」マークはオートフィル機能を使うよという意味で、この形のままドラッグ操作をすることで、セルの内容をコピーしてくれます。
普通のコピペと違う点は、日付や関数の内容をそのまま複製するのではなく、
例えば、「=VLOOKUP(A18,$S$18:$U$46,2,0)」→「 =VLOOKUP(A19,$S$18:$U$46,2,0) 」のようにオートフィルのコピー機能は、行列に1行、列分を自動で足してくれるなど、すごく頭のいい機能なので上手く活用することがポイントです。
⑪オートフィル機能を使うと、関数がコピーされてコード番号に合った「品名」を表示してくれます。
ここでポイントですが、オートフィルでコピーしたら「オートフィルオプション」が表示されるので、「▼」をクリックします。
⑫「オートフィルオプション」の「書式なしコピー(フィル)(O)」という項目をクリックしましょう。
⑬これで完成です。
さっきのオプションは、オートフィル機能を使うと表に「実線と点線」が混じっていた場合、デザインを崩してしまいます。
「書式なしコピー」を選ぶことでこの問題を防ぐことが出来るので覚えておきましょう。
さて、今回一番難しいVLOOKUP関数の使いは以上です。
手順は長いですが、実務では必須と言っていいほど使いみちが多い関数なので初心者のうちにマスターしてしまいましょう。
関数の中では難易度が高めなので、最初は覚えるのに苦労するとは思いますが、何度も使って慣れてしまいましょう。
ココまでくれば、あとはそこまで難しくはないですよ。
もう少しで完成するので早速次にいってみましょう！！
見積書を完成させよう
さあ、ここまでくれば、完成はもう少しです。
あとは、同じような作業を他の場所でもしていけば良いので、練習だと思って気軽に行きましょう。
まずは、単価を自動的に表示するように設定してみましょう。
やり方は、めっちゃ簡単です。
単価を自動表示するように設定する
「L18セル」に「=VLOOKUP(A18,$S$18:$U$46,3,0)」を入力して「L29セル」までオートフィル機能でコピーすればOKです。
さっき、品名の設定でも入力しましたね。
今回との違いは、 「=VLOOKUP(A18,$S$18:$U$46,2,0)」→ 「=VLOOKUP(A18,$S$18:$U$46,3,0)」 これだけです。
今回は、コード対応表の3列目に「単価」が入力されているので、3列目の単価を探して表示してね。という意味にですね。
これで終わりです。簡単ですね。
次は、金額を自動で計算するように設定しましょう。
金額を自動で計算してくれるように設定してみよう
金額の自動計算を設定するのは、一番簡単です。
まず、金額の計算方法は、「数量」✕「単価」=「金額」で計算できますよね。
今回のポイントは、この算数での式をExcelでは、どうやって設定すればいいのかということです。
そこで出てくるのが「四則演算(しそくえんざん)」という言葉です。
難しい言葉ですが、簡単に言うと「足し算」・「引き算」・「掛け算」・「割り算」のことを言います。(厳密には少し違います)
四則演算での計算方法は、算数とは少し違うんですよ。
まず、記号が違うんです。
「足し算」→「+」 「引き算」→「-」 「掛け算」→「*」 「割り算」→「/」
こんな感じです。
つまり、「✕」→「*(アスタリスク)」 「÷」→「/(スラッシュ)」
使う記号が違いますね。
次に知っておくことは、算数とExcelでは、式の入力方法が違います。
算数でExcelの計算方法を表現すると「J18✕L18=O18」となりますが、Excelでは「=J18*L18」となります。
つまり、「=」記号が先頭に付くわけです。
ひょっとしてもう気づきました？ 実はこの表現方法は、もう何度もやっているんです。
そう。関数の設定はみんなこの形でしたよね。
つまり、四則演算も関数も基本は同じということです。
ココまで分かれば、計算方法の設定はめっちゃ簡単だと思いません？
では、早速設定してみましょう。
①O18セルに 「=J18*K18」 と入力します。(数量には、適当な数を直接入力しています。)
②式を入力すると、金額が自動で計算されればOKです。
これを「O29のセル」までフィルコピー(オートフィル機能でコピー)しましょう。
③自動で計算できていれば、金額の自動計算機能の設定はOKです。
どうです？ 簡単ですよね。
では、最後の仕上げをしていきましょう。
「小計」「消費税」「合計」を自動で計算するように設定しよう
最後の仕上げをしていきましょう。
設定するのは、「小計」「消費税」「合計」の計算ですね。
それぞれの計算の設定方法を確認してみましょう。
「小計」は、SUM関数を使って「O18～O29」の範囲のセルを合計すればOKです。
「消費税」は、小計に0.08を掛ければ、計算できますね。
つまり、「=O30*0.08」でOKです。
今回は、8%の消費税の場合なので税率が変われば、数字を変更する必要がありますね。
合計金額は、「小計と消費税」を合計すればいいので、SUM関数を使って「=SUM(O3:Q30)」と設定すればOKです。
最後は、簡単でしたね。
以上で自動計算してくれる便利な見積書の完成です！！
まとめ
今回は、どうでしたか？
初心者には難しくない？ と思ったかもしれませんね。
確かに、一般的には、これくらいのものを作るのは中級者以上だという場合がほとんどでしょう。
じゃあ、どうして初心者向けにしたのか？ 実は理由があります。
それは、多くの人は「自動的に計算できるように設定して下さい」と言ってもできない人がほとんどなので、最低でもこれくらいの設定はできるようになってほしいからです。
偉そうな言い方で申し訳ないんですが、自動化と言ってもそんなに難しくないですよね。
要は、四則演算と関数をいくつか覚えるだけで基本的な自動化はできちゃいます。
でも、残念ながら実務に通用するテクニックを中心に教えてくれるところは多くないのが現状です。
理由は、パソコンの起動からWordやExcelの基本的な使い方をまず覚えてから実務で使う方法は、職場など仕事に合わせて現場で教わって下さいというスタンスが多いからなんです。
それ自体は間違ってはいないんですが、私はココまでのことを最初に覚えてほしいと考えています。
これを基礎にして、「使いやすく」「使っていて便利」「作業の手間を減らす」ということをできるようになって中級・人に教えられるようになって上級だと思っているので、初級とさせてもらっています。
ただ、今回の本当の目的は、初心者でも「便利で楽しい」Excelを作れるんだ。ということを実感してもらうことです。
完成したら、実際に使ってみて下さいね。
便利だったり、これなら簡単な操作でサクサク面倒な書類でも作れるじゃないか～ ～ ～ 。って思ってもらえたら大成功です！！
私のモットーはとにかく楽しいことを増やすことです。
楽しいと得意になります。テンションも上がります。やる気も出てきます。
そうなったら、こっちのものです。
楽しい上に便利になっていく。なんだか夢のようですが結構簡単に実現できちゃうものなんですよ。
そして、関数など一見難しそうなことでも覚えることを絞ってどうすれば便利になるか。この基礎をまず覚えてしまえば上達はぐっと早くなるので、今回のブログの内容を繰り返しやってみて自分のものにしてみて下さいね。
最後まで読んでいただきありがとうございます。
こんな場合はどうすればいいの？ とかもう少し女性向けの内容にしてよなどご要望があればぜひご意見下さい。
それではまた次回お会いしましょう。 バイ、にゃら！！
苦手意識がありましたが、大変わかりやすく短時間でクリアできました。
何故なのかという理由まで詳しくわかったので、楽しくでき忘れにくいので、
もっと教えてほしくなりました。
ありがとうございました。
「まー」さん
コメントを頂きありがとうございます。
「楽しくできて忘れにくい」といっていただけると、この記事を書いて良かった！！！！
と思います。 めちゃくちゃ嬉しいです。
私は、常に「楽しく・便利に・感動できる体験」を気楽生活を通して実感してもらえるように精進するようにしています。
これからも、EXCELやWordを楽しく使えるようになったと思っていただけるような記事を書いていきたいと思います。
本当にありがとうございました。