関数の組み合わせ〜IFの中にVLOOKUPを入れてエラーを回避する〜

スプレッドシートの活用力を高めるため関数の使い方に慣れましょう。
ここまでよく使われる関数 IF と、VLOOKUPを学んできました。

VLOOKUPを使った時、該当なしのエラーメッセージが表示されると解説しましたが、検索されるセルが空白だったときに、エラーメッセージを表示せず、空白を表示するようにセルの関数の定義を編集していきましょう。
 B8セルをクリック、Deleteキーを押して商品 IDを削除、B8セルに入力がなくなり A8セルは空白、A9セルは1、C8セルとD8セルは、該当なしが表示されました。
C8セルをダブルクリック、C8セルに半角で
=IF(B8<>””,VLOOKUP(B8,$J$8:$L$25,2,0),””)
※すでに入力されているVLOOKUP計算式 → VLOOKUP(B8,$J$8:$L$25,2,0)
を入力し 「Enter」 キーで入力を確定します。
商品名の列が空白になりました。

続いて同じように、商品IDが空白だった時、単価の列を空白にするように設定してみましょう。
D8セルをダブルクリック、 D8セルに半角で
= IF(B8<>””,VLOOKUP(B8,$J$8:$L$25,3,0),””)
※すでに入力されているVLOOKUP計算式 → VLOOKUP(B8,$J$8:$L$25,3,0)

を入力し 「Enter」 キーで入力を確定、単価の列が空白になりました。

D8セルに入力した関数について解説します。
一致しないという条件を表すために、<> を使用しています。
そして""が空白を意味するので、ここでの条件は B8セルの値が、空白と一致しないならば、条件と合致する時の処理として、VLOOKUPの結果を表示し、条件と合致しない時の処理として、空白を表示する、としています。
最後に数式を入力した C8、D8 セルを選択し、C17 、D17セルまでの各セルに反映しましょう。

解説は以上です。

VLOOKUP関数の使い方〜データを参照して表示させる便利なVLOOKUP関数入門〜

スプレッドシートの活用力を高めるため関数の使い方に慣れましょう。
データを抽出する関数VLOOKUPを使用し、より効率的にデータを入力する方法を学んでいきましょう。

これにより、今後は商品IDさえ入力すれば、商品名、単価が自動で表示されるようになりますので、請求の都度、手入力しなくてすみますね。

では行っていきましょう。
シート名の請求書の右側の下「▽」をクリック。
「コピーを作成」をクリック、請求書シートの右隣に請求書のコピーシートが作成されました。
請求書のコピーシート名をダブルクリック、シート名を請求書2に編集します。
 B7セルからD17セルの範囲を指定してコピーをし、G7セルをクリックし、貼り付けます。
これで請求書の表の中で商品 ID が入力された時に、商品 ID に対応する商品名、単価を表示させるための一覧ができました。
もし調達する商品のリストが追加された時はこの表の一番下、J18セルから商品 ID商品名単価を入力して商品情報を追加します。
 B列に商品 ID が入力されたら商品名が自動で表示されるように、C列のセルの内容を編集していきましょう。
C8セルをダブルクリック、
=VLOOKUP(B8,$J$8:$L$25,2,0)
を入力し 「Enter」 キーで入力を確定します。
ここで参照する範囲を、L25セルまでにしているのは、商品一覧が追加された時も関数を変更しないで新しい商品のデータを参照できるように配慮しているためです。


次にC17セルまでの各セルに、同じ計算式を適用しましょう。
ここまでで書いたVLOOKUP関数を見てみましょう。
C8セルにこのように入力しました。
これはデータを検索するセル、B8セルの値、データを検索する範囲、 J 8セルからL 25セルまでという意味になります。

ここで使用している$マーク、絶対参照について説明します。
通常セルをコピーして他のセルに貼り付けるとき、セルが移動した分数式で設定したセルの位置も移動します。
他のセルにコピーしたとしても、セルの位置を移動させない、という機能が$マークによって設定されます。
セルの列、行それぞれの前に$マークをつけると、セルを他の列や行に貼り付けた時に、行や列の移動に関係なく、同じ列や行の値を参照することができます。
またVLOOKUPの第3引数に指定した”2”ですが、検索範囲の何列目のデータを参照するかを指定しています。
最後にVLOOKUPの第4引数に指定した"0"は、検索方法の指定で"0"を指定することで、検索するセルと全く同じ値だけを検索範囲から検索し、"1"を指定することで検索するセルと全く同じ値がない場合は一番似ている値を検索するとしています。
続いて同じように B列に商品IDが入力されたら、単価が自動で表示されるようにD列のセルの内容を編集していきます。
D8セルをダブルクリック
D8セルに半角で
=VLOOKUP(B8,$J$8:$L$25,3,0)
と入力し 「Enter」 キーで入力を確定します。
検索範囲に指定した、3列目が適用されました。

次に D17セルまでの各セルに同じ計算式を適用しましょう。
これで商品 IDを入力すると、商品名、単価が自動で入力される請求書を定義できました。

最後に各列の動作を確認しておきましょう。
まずは、定義されていない商品 IDを指定したときの動作です。
B8セルをダブルクリック、商品 IDを S-100から S-900 に変更します。
 C列 D 列に #n/a  と表示されました。
これは該当なし、ということを表しています。

続いて、検索範囲に新しい商品を追加した場合の挙動を見ていきましょう。
商品 IDを S-900 として商品を登録します。
商品名をパイナップル、単価を198と入力しましょう。
 C8セル、D8セルの値が該当なしから、パイナップル198と表示されました。

解説は以上です。

IF関数の使い方〜条件によって処理を関数IF入門〜

スプレッドシートの活用力を高めるため、関数の使い方に慣れましょう。
今回は、条件ごとに処理を変える関数 IF について学んでいきます。

スムージー販売をするための調達について、数量が多い場合は割引をしてもらえることにします。
数量が100以上の商品は10%値引きになる、という数式をセルに設定して、
割引率を計算しましょう。
G8セルをクリック
= IF(E8>=100,10%,"")
と半角で入力し 「Enter」 キーを押して入力を確定しましょう。
10%と等しい、 「0.1」が表示されました。

続いて表示形式にパーセントを適用します。
G8セルをクリック＞「表示形式」をクリック＞「数字」をクリック＞「パーセント」をクリックすると、表示形式がパーセントになりました。
小数点以下の桁数を減らし、整数の表記にしておきましょう。

次にG17セルまでの各セルに、同じ計算式を適用しましょう。
関数を入力したG8セルをクリック＞右下の隅にマウスを合わせて、カーソルを「+」の状態にし、右下をクリック＞G17セルの位置までドラックアンドドロップします。
G17セルまでの各セルに関数が適用されました。
数量が100以上のセルには「10%」と表示され、数量が100未満のセルは空白のままとなっています。
これは IF関数の引数に条件として、E8セルの値が100以上ならば、合致する時の処理として、10%と入力し、合致しない時の処理として、何も設定しない、としているためです。
ここまでで調達数量に応じて、割引率を定義することが出来ました。

それでは割引後金額に、割引率を反映させていきましょう。
H8セルをダブルクリック、半角で
=(1-G8)*F8
と入力し 「Enter」 キーを押して入力を確定します。
割引が適用されました。

次にH17セルまでの各セルに同じ計算式を適用しましょう。
関数を入力したH8セルをクリック、右下の隅にマウスを合わせて、カーソルをプラスの状態にし、右下をクリック、H17の位置までドラックアンドドロップします。
H17セルまでの各セルに関数が適用されました。

さて、計算式で引き算の部分を()で囲い、計算の優先順位を上げていますが、これは算数と同様に、スプレッドシートでも掛け算の演算子の優先順位が、引き算の演算子の優先順位より高いためです。
掛け算や割り算がある時には、()の付け方や、数式の計算の順番を間違えないように注意しましょう。

解説は以上です。

四捨五入や切り上げ／切り捨て〜ROUND関数の使い方〜

スプレッドシートの活用力を高めるため関数の使い方に慣れましょう。
今回は四捨五入する関数ROUNDについて学んでいきます。

現在は表を見やすくするために小数点以下の表示を行わないようにしている状態です。
ここでもう一度小数点部分を表示させ、小数点以下の値について関数ROUNDを使って四捨五入していきましょう。

H19セルをダブルクリック。
既に入力されている計算式を
=ROUND()の中に記述し、「,0」とします。
小数第一位の数字が四捨五入されました。
ROUND関数の第2引数で指定した「0」ですが、省略した場合は「0」を指定するのと同じ結果が得られます。

また、小数第二位を四捨五入したい場合には、「1」を指定、
小数第三位の場合には「2」を指定というように指定します。

それでは、税込総額も同じように関数 ROUND を使って四捨五入してみましょう・
今度は第2引数を省略する形で試してみます。
H20セルをダブルクリック。
すでに入力されている計算式を
=ROUND()
の中に記述します。
先ほどと同じように小数第一位の数字が四捨五入されました。

最後に小数点以下の桁数を減らして整数のみの表示に戻しておきましょう。
ここまでで ROUND 関数を用いて指定した桁で四捨五入する方法を学びました。
他にも、切り上げを行う ROUNDUP や
切り捨てを行うROUNDDOWNなどの関数があります。
使い方はROUNDと一緒ですので、ぜひ試してみてくださいね。

解説は以上です。

スプレッドシートの四則演算〜別のセルの値をそのまま表示する「=」演算子、足し算〜

スプレッドシートの活用力を高めるため、関数の使い方に慣れましょう。

まずは、別のセルの値をそのまま表示します。
割引後金額の列に、同じ行の金額のセルの値を表示させ、
すべての商品の合計金額を求めていきます。
H8セルに半角で
=F8
を入力し 「Enter」 キーで入力を確定。
H8セルに指定したセルと同じ値が表示されました。
下に続くセルにも同様の計算式を入力しましょう。
数式を入力したH8セルをクリック、
H8セルの右下の隅にマウスを合わせ、カーソルを「+」の状態にし
H17セルの位置までドラッグ&ドロップすると
H17セルまでの各セルに数式が自動で入力されます。

続いて全ての商品の合計金額を、関数SUMを使って計算してみましょう。
G18セルに小計と入力。
H18セルに半角で
=SUM(H8:H17)
を入力して「Enter」キーを押して入力を確定します。
または
H18セルをダブルクリック
=SUM()
とし、H8をクリックして
ShiftとControl
Macの場合はCommandを押しながら下を押すことで
入力が連続しているセルの端までを選択できますので
「Enter」キーを押して入力を確定します。
どちらの方法でも使いやすい方法を入力してください。
ここまでで全ての商品の合計金額が計算できました。

次に、消費税額を求め支払総額を計算しましょう。
G19セルに「消費税(8%)」と入力。
H19セルに半角で
=H18*0.08
と入力し 「Enter」 キーを押して入力を確定します。
H19セルに計算結果が表示されました。
全ての商品の合計金額である小計と、消費税を合わせることで、総額を求めることができます。
これまで、指定したセルの数値を合計するのに関数SUMを使ってきましたが、
ここでは足し算の記号を使う方法を試してみましょう。
G20セルに「税込総額」と入力。
H20セルに半角で
=H18+ H19
を入力して「Enter」キーを押して入力を確定します。
H20セルに計算結果が表示されました。

解説は以上です。

スプレッドシートの四則演算！〜隣のセルとのかけ算〜

スプレッドシートの活用力を高めるため関数の使い方に慣れましょう。

これまでに行ったデータの入力や書式の設定についても振り返りながら代表的な関数の使い方を覚えていきます。
まずは隣のセルとの掛け算を行っていきましょう。
ここでは前回までで使用していたスムージーの屋台の売上の管理の請求書を作成します。
「+」ボタン「シートを追加」をクリック。
新しくできたシートのシート名をダブルクリックし、シート名を「請求書」に変更しましょう 。H1 から H7セルまでを次のように入力してください。
動画ではあらかじめ用意した文字をペーストしていますが、皆さんは動画と同じように入力してくださいね。

続いて「スムージー」シートの商品に関連する調達品を入力しましょう。
ここまでで、商品ごとの単価と調達する数量が入力されました。
商品ごとの調達の合計金額は、単価と数量を掛け算すると求められますので、
金額が記載されている列では掛け算をしていきます。
F8セルに半角で
=D8*E8
を入力し 「Enter」 キーで入力を確定します。
F8セルに計算結果が表示されました。
このようにスプレッドシートでは掛け算を「*」を使って行いますが、
足し算では「+」
引き算では「-」
割り算では「/」
を使用することを覚えておきましょう。

さて F8セル、キウイの購入金額の総額が求められましたので、
下に続くセルにも同様の計算式を入力していきます。
数式を入力したセルをクリック。
F8セルの右下の隅にマウスを合わせ
カーソルを「+」の状態にします。
F17セルの位置までドラッグ&ドロップすると
F17セルまでの各セルに数式が自動的に入力されました。

解説は以上です。

関数を使った合計の計算〜SUM関数を使ってみよう〜

関数を使った合計の計算を解説していきます。

ここまでで入力してきた日別の商品の売り上げ数をすべての日で合計した数の計算をします。

関数を使用することでデータの変更や追加があったときに自動的に計算結果が更新されるため、とても便利ですね。

ここではSUMという合計を計算する関数を使ってみましょう。

E6セルをクリックし 「Enter」 キーを押してセルを編集モードにします。

E6セルに半角で

=SUM(B6:D6)

と入力して「Enter」キーを押して入力を確定させましょう。

B6からD6までを合計した数値がE6セルに表示されました。

また、

=SUM(

と入力してB6をクリック、 Shift を押しながら右キーを2回押して「Enter」を押すと、同じようにSUMが入力されます。

このように関数SUMは指定した範囲の数字を合計する関数です。

現在は B6セルからD6セルまでの範囲を指定していますが、データが飛び飛びになっている時には合計したいセルを1つずつ半角のカンマで区切って値を指定することができます。

元の値に戻しておきます。

入力した関数を下方向にも入力すると商品ごとに合計数を計算することができるようになります。

その際セルの内容をコピーすることで簡単に計算を入力することができます。

関数を入力したE6セルをクリック＞入力セルの右下にマウスを合わせカーソルを「+」のような状態にして、E6セルの右下をクリック＞E10セルまでドラッグ&ドロップします。

E6セルに設定していた関数が下のセルにも設定されます。E10セルまでの各セルにSUM関数が自動的に入力されました。

ここまでで、ある商品のすべての期間にわたる売上数の合計を計算しました。

次に、すべての商品でのすべての期間にわたる売上数の合計を計算しましょう。

D11セルをダブルクリック＞「」総計と入力して 「Enter」 キーを押して入力を確定します。

E1111セルをダブルクリック＞

=SUM(

を入力し、E6セルをクリックして

Windows の場合はShiftキーとControl

Macの場合はShiftキーとCommandキー

を押しながら下を押すと入力が連続しているセルの端までを選択できます。

「Enter」 キーを押して入力を確定しましょう。

E11セルに合計の数値が表示されました。

この動画ではSUMで複数のセルの合計を求める関数の使い方を学びました。

関数についてはスプレッドシート関数で検索し、1番上のリンクをクリックすると全ての関数を見ることができます。

解説は以上です。

スプレッドシートで四則演算の数式を試してみよう！

以前の記事で、
http://spreadsheetapp.blogspot.jp/2016/04/blog-post_6.html

スプレッドシートのセル同士の掛け算や、セルの数字と指定した数値の掛け算をしました。
いきなり掛け算の操作を行ったので、掛け算以外の計算も解説しておこうと思います。
では、スプレッドシートを開きましょう。

スプレッドシートの数式をたくさん試してみよう！

数式の計算結果をわかりやすくするために、
以下のようにデータを入力してみてください。

このように、入力してみました。

掛け算の計算は、

=(セル) * (セル)

計算結果を表示したいセルを指定して、=(イコール)を入力し、
掛けあわせたいセルとセルの間に、*(アスタリスク)を入力しました。

この、「*」のことを演算子といい、計算の仕方をこの演算子が指定します。

計算をする演算子には、例えば以下のものがあります。
+ （プラス）：足し算
- （マイナス）：引き算
* （アスタリスク）：掛け算
/ （スラッシュ）：割り算

四則演算してみよう！

掛け算のやり方と同様に、
セルとセルを指定して、四則演算（足し算、引き算、掛け算、割り算）をしてみましょう。
以下のように、入力してみてください。
D2セル：「=A2+B2」
D3セル：「=A3-B3」
D4セル：「=A4*B4」
D5セル：「=A5/B5」

なんということでしょう！

それぞれのセルに指定した計算の結果が表示されます。

数式では、2つのセルだけで計算だけではなく、3つ以上のセルを複数の演算子を使って計算させることができます。

計算の順番はどうなるんだろう？演算子の優先順位

足し算、引き算、掛け算、割り算の計算が行われる優先順位は、以下のようになります。

1.掛け算と割り算
2.足し算と引き算

では、最初に足し算や引き算をしてから、その値に掛け算や割り算を使いたい時にはどうしたらよいでしょう？

そう！「()」カッコで、先に計算したい箇所を囲うことで、
掛け算や割り算よりも先に、足し算や引き算を行うことができるようになります。

試してみましょう。
以下のように、データを入力してみてください。

A7セルから、E11セルまでに、このように入力してみました。

では、カッコをつけないで、複数のセルで計算をしてみましょう。
E8セルに、「=A8+B8*C8」
E9セルに、「=A9-B9*C9」
と、入力してみましょう。

すると、
E8セルには、1 + 6（2かける3を先に計算） の、7が、
E9セルには、1 - 6（2かける3を先に計算） の、-5が、
それぞれ表示されます。

次に、カッコをつけて複数のセルで計算をしてみましょう。
E10セルに、「=(A10+B10)*C10」
E11セルに、「=(A11-B11)*C11」
と、入力してみましょう。

すると、
E10セルには、3（1 + 2 を先に計算）かける3の、9が、
E11セルには、3（1 - 2 を先に計算）かける3の、-3が、
それぞれ表示されます。

複数のセルを使って計算をしたい時は、
()カッコを使って計算を行うことで、数式を確認する時に見やすくなったり、
優先順位を間違えずに設定できるので、ぜひ覚えておきましょう！

スプレッドシートの基本的な使い方を体験してみよう！（数式の入力）

前の記事では、

1. スプレッドシートにアクセスして、
2. セルに文字を入力すること、
3. スプレッドシートのファイル名を変更すること

を学びました。

「おいおい！表計算ソフトっていうくらいだから、便利な計算だってできるんだろぃ？」

「表を可愛く作ることもできるんざましょ？」

と、感じられているかと思いますので、
本記事では、スプレッドシートの基本的な操作を解説していきます。

スプレッドシートに数字を入力してみよう！

新しいスプレッドシートを作成して、こんな感じで入力してみましょう！

列（表の上のところのアルファベットのところ）が「A」、
行（表の左のところの数字のところ）が「1」、
のところ、これを、セルA1（あるいはA1セル）といいます。
セルA1に、「買うもの」と入力して、「Enter」キーを押します。

同じように、
B1セルに、「値段」
C1セルに、「買う数」
D1セルに、「合計」
E1セルに、「合計（税込）」
と入力しましょう。

セルに文字を入力する練習になりますね！
では、さらに入力をして・・・

こんな感じで、
A2セルに「シャンプー」、B2セルに「1000」、C2セルに「2」
と、入力してみます。
1000円のシャンプーを、2つ買う、ということを表で表してみました。

同じように、
A3セルに「ティッシュ」、B3セルに「200」、C3セルに「5」

A4セルに「綿棒」、B4セルに「100」、C4セルに「1」

A5セルに「電動歯ブラシ」、B5セルに「8000」、C5セルに「1」

A6セルに「歯磨き粉」、B6セルに「500」、C6セルに「1」

と入力します。

スプレッドシートで数式を入力してみよう！

さて、ここで、「合計」と書かれたセルには、「値段」のセルと、「買う数」のセルの

数字を掛け算したらいいんじゃないか？

と、ひらめいたかと思います。

では、D2セルに、このように入力してみてください。

「=B2*C2」
と、入力して、「Enter」キーを押します。
すると・・・

なんということでしょう！？
D2セルには、1000 × 200 の計算の答えの2000が表示されます。

同じように、D3セルに、「=B3*C3」と入力してみましょう。

すると、このように計算された答えが表示されます。

同じように、D4セル、D5セル、D6セルにも直接数式を入力したいところですが、
便利な技を紹介します。
その名も、「コピー＆ペースト（略してコピペ）！」

D3セルを選択して、
Windowsの方は、「Ctrl + C」、
Macの方は、「Command + C」
を同時に押してみましょう。

そうしたら、D4セルを選択して、
Windowsの方は、「Ctrl + V」、
Macの方は、「Command + V」

を同時に押してみましょう。

なんということでしょう！

D4セルに、数式が自動的に入力されているのです。

この、

Windowsの方は、「Ctrl + C」、
Macの方は、「Command + C」

のことを、「コピー」

Windowsの方は、「Ctrl + V」、
Macの方は、「Command + V」

のことを、ペースト、といいます。

セルを選択して、コピー＆ペーストをすると、

入力された値や数式を複製することができるのです。

同じ要領で、D5セル、D6セルにも、数式をコピー&ペーストしましょう。

これで、セル同士の掛け算についてはマスターできましたね？

では、Dのセルに、消費税(8%)を足した金額を出すにはどうしたらいいでしょうか？

E2セルに、このように入力してみてください。

「=D2*1.08」

と、入力して、「Enter」キーを押します。
すると・・・

なんということでしょう！？
E2セルには、2000 × 1.08（消費税8%を足した数） の計算の答えの2160が表示されます。

同じように、E3セルからE6セルには、E2セルを選択して、数式をコピペしましょう！

ここまでで、

• セルを選択して入力をすること
• セル同士の掛け算をすること
• セルと指定した数字の掛け算をすること
• セルの内容をコピペすること

を学びました。

とても、パワーアップしたことと実感しているでしょう！

まだまだ、たくさんの楽しい機能がスプレッドシートには秘められていますので、

次の記事以降の解説を楽しみにお待ちください！