スプレッドシートの関数① – ★☆☆

スプレッドシートの関数① – ★☆☆
スプレッドシートの関数① – ★☆☆
ビジネス

はじめに

今回のbzbot(ビジボット)では、「スプレッドシートの関数」を書いていきます。
まずは基本的な関数の紹介をさせていただきます。体力があればExcelにはない、Googleスプレッドシートには備わっている超強力な関数を紹介できればと思います!(笑)
まずは基本的なところからなのでこれから始めます!って方も一緒にやってもらえればと思います!

それではさっそく始めていきます!

1.SUM関数

まず始めにSUM関数とは?というところですが、データを足したり引いたり、計算する関数です。みなさん流石にこの関数はご存知ですよね。(笑)
以下数式と結果を右側に表示して、それぞれの答えが以下のようになってます。

数式結果
=SUM(A1+B1)3
=SUM(A1:A10)55
=SUM(A:A)155

SUM計算した実際の画面 - bzbot(ビジボット)
SUM計算した実際の画面 – bzbot(ビジボット)

SUM関数自体は殆どの方が触れたことがある関数だと思うので、とりあえず基本の理解ということで次に進みます。データ自体は増えることを前提とした設計にする場合は特定列全てをSUMする。って場合が多いかな?

関数説明
ビジボット
SUM(A2+B2)
→セルA2にある値とセルB2にある値を足した値が表示される

SUM(A2:A11)
→セルA2〜A11にある値を足した値が表示される

SUM(A:A)
→セルA列にある値全てを足した値が表示される

2.SUMIF関数

SUMIF関数は特定条件に合致する値をSUM(計算)する関数です。
以下を参考にしてみると、A列の中でF2に一致する条件でB列にある値の個数合計を求めてます。

ロボットID数式結果
1001=SUMIF(A:A,F2,B:B)12
1002=SUMIF(A:A,F3,B:B)12
1003=SUMIF(A:A,F4,B:B)5
1004=SUMIF(A:A,F5,B:B)12

SUMIF計算した実際の画面 - bzbot(ビジボット)
SUMIF計算した実際の画面 – bzbot(ビジボット)

SUMIFは実は私個人的にはほぼ使ってないです。。基本SUMIFSで使えちゃうので使う関数を統一してます!
使い方の詳細はパターンを分けて紹介しますね!

関数説明
ビジボット
SUMIF(A:A,F2,B:B)
→セルA列の値の中で、セルF2と一致する値のセルB列にある値の合計を表示する

3.SUMIFS関数

今回は2つのパターンを紹介します。基本SUMIFを使わずSUMIFSで宣言して使ってます。
以前はこの場合はSUMIF、この場合はSUMIFSって使い分けてましたが、全く書き方を覚えられなかったのですが、使う回数が多くなってくるとどんどんSUMIFSが便利だなって気づきました。

さっそくパターン①の紹介をします。
やっていることはSUMIFと同じです。ただ、書く順番が異なります。

先ほどは「ロボットID」列のある、ロボット1001の〜という風に描き始めてましたが、今回は求めたい値から書き始めてます。今回の場合でいうと個数の合計を求めたいので「個数」列があるB列を指します。

このあとに、条件範囲①と、条件①を入れます。

ロボットID数式結果
1001=SUMIFS(B:B,A:A,F2)12
1002=SUMIFS(B:B,A:A,F3)12
1003=SUMIFS(B:B,A:A,F4)5
1004=SUMIFS(B:B,A:A,F5)12

SUMIFS計算した実際の画面① - bzbot(ビジボット)
SUMIFS計算した実際の画面① – bzbot(ビジボット)

次にSUMIFSで複数系の「S」がついてるからには条件をさらに追加することも可能です。
そのケースがパターン②です。

先ほどは条件がシンプルだったので「SUMIF」でも「SUMIFS」でもどちらでも対応できましたが、今回のケースは条件を複数個指定する必要があります。

売り上げの合計をロボットごとで尚且つ販売者ごとにみたいという要望の場合です。

ロボットID販売者数式結果
1001Aさん=SUMIFS(E:E,A:A,G2,D:D,H2)4,950
1001Bさん=SUMIFS(E:E,A:A,G3,D:D,H3)450
1002Aさん=SUMIFS(E:E,A:A,G4,D:D,H4)4,000
1002Bさん=SUMIFS(E:E,A:A,G5,D:D,H5)2,000
1003Aさん=SUMIFS(E:E,A:A,G6,D:D,H6)1,200
1003Bさん=SUMIFS(E:E,A:A,G7,D:D,H7)300
1004Aさん=SUMIFS(E:E,A:A,G8,D:D,H8)1,100
1004Bさん=SUMIFS(E:E,A:A,G9,D:D,H9)5,500
SUMIFS計算した実際の画面② - bzbot(ビジボット)
SUMIFS計算した実際の画面② – bzbot(ビジボット)

SUMIFSは様々なところで使えるのでぜひ覚えておく必要がある関数です!

関数説明
ビジボット
SUMIFS(B:B,A:A,F2)・・・パターン①
→セルB列の合計をする条件は、セルA列のセルF2に一致する値

SUMIFS(E:E,A:A,G2,D:D,H2)・・・パターン②
→セルE列の合計をする条件は、セルA列のセルG2に一致する値と
セルD列のセルH2に一致する値

4.AVERAGE関数

AVERAGEは字のまんま平均を出す関数です。
以下数学のテストをした結果をその数学の平均点を求めたいときなどに使います。

数式結果
=AVERAGE(B2:B5)77
=AVERAGE(B:B)77

AVERAGE計算した実際の画面 - bzbot(ビジボット)
AVERAGE計算した実際の画面 – bzbot(ビジボット)

これも使ったことある人が多いかもしれませんね!

関数説明
ビジボット
AVERAGE(B2:B5)
→セルB2〜B5にある値を足して平均した値が表示される

AVERAGE(B:B)
→セルB列にある値全てを足して平均した値が表示される

5.COUNTA関数

次はカウント系の関数です。COUNTAは値が入っているセルを全てカウントします。
B2:B5は4つのデータが入っているから4ですが、B:Bの場合は「数学」というヘッダーにあたる部分もカウントされてしまうので5という結果になってます。

数式結果
=COUNTA(B2:B5)4
=COUNTA(B:B)5

COUNTAでカウントした実際の画面 - bzbot(ビジボット)
COUNTAでカウントした実際の画面 – bzbot(ビジボット)

Excelなどで使うときに、関数が入っている列とデータが入っている列の比較をするときに使ったりしてました。
データが関数の個数を上回ったとき、関数の範囲が足りないなどちょっとアナログチックに判断してた時もありました。(笑)

関数説明
ビジボット
COUNTA(B2:B5)
→セルB2〜B5に値が入っているセルの個数を表示する

COUNTA(B:B)
→セルB列に値が入っているセルの個数を表示する

6.COUNTIF関数

COUNTIFSはもしこの条件に一致すれば…という要望に一致した個数を返してくれます。
今回の場合で言えば試験を受けた回数を調べています。A列に入っている名前の数を検索して、重複して出てきた結果であれば複数回受けているということで、何度試験を受けているかが確認できます。

氏名数式結果
Aさん=COUNTIF(A:A,E2)1
Bさん=COUNTIF(A:A,E3)1
Cさん=COUNTIF(A:A,E4)2
Dさん=COUNTIF(A:A,E5)2

COUNTIFでカウントした実際の画面 - bzbot(ビジボット)
COUNTIFでカウントした実際の画面 – bzbot(ビジボット)

SUMIFと同様、私はCOUNTIFを使わず、次に紹介するCOUNTIFSで処理を書いちゃうことが多いです。
書き方はCOUNTIFもCOUNTIFSも全く一緒です!

関数説明
ビジボット
COUNTIF(A:A,E2)
→セルA列の中でセルE2一致する値の個数を表示する

7.COUNTIFS関数

COUNTIF同様の関数ですが、違う点としては複数条件に対応ができます。
先ほどはテストを何回受けたかチェックをしましたが、今回は各それぞれのメンバーに対して「✔︎」がついている数(追試を受けた数)を表示することができました!

氏名数式結果
Aさん=COUNTIFS(A:A,E2,C:C,”✔︎”)0
Bさん=COUNTIFS(A:A,E3,C:C,”✔︎”)0
Cさん=COUNTIFS(A:A,E4,C:C,”✔︎”)1
Dさん=COUNTIFS(A:A,E5,C:C,”✔︎”)1

COUNTIFSでカウントした実際の画面 - bzbot(ビジボット)
COUNTIFSでカウントした実際の画面 – bzbot(ビジボット)

COUNTIFを使うくらいならCOUNTIFSで複数条件もできるし、複数条件がなくても同じ記述ルールで記載出来るのでSUMIFSとCOUNITFSは本当によく使います!(笑)

関数説明
ビジボット
COUNTIFS(A:A,E2,C:C,”✔︎”)
→セルA列の中でセルE2一致する値と
セルC列の中で”✔︎”に一致する個数を表示する

8.IF関数

もし75点未満のテスト結果だった場合、関数の結果で「補習」が表示され、もし75点以上の場合は補習のない空欄という結果を表示させます。

氏名数式結果
Aさん=IF(B2<75,”補習”,””)
Bさん=IF(B3<75,”補習”,””)
Cさん=IF(B4<75,”補習”,””)補習
Dさん=IF(B5<75,”補習”,””)補習
IFで分岐処理をした実際の画面 - bzbot(ビジボット)
IFで分岐処理をした実際の画面 – bzbot(ビジボット)

IF文はかなり複雑な条件を指定したいときに役に立ちます。
あまり使わないと書き方とか忘れがちなのでしっかりと復習しましょう!!

関数説明
ビジボット
IF(B2<75,”補習”,””)
→セルB2の値が75より小さい場合、Trueだったら”補習”を表示
Falseだったら””を表示

9.TODAY関数

TODAY()関数は入力するだけで本日の日付が表示されます。

TODAY()関数の画面 - bzbot(ビジボット)
TODAY()関数の画面 – bzbot(ビジボット)

表示形式は「yyyy/mm/dd」で表示されます。

関数説明
ビジボット
TODAY()
→yyyy/mm/ddで今日の日付が表示

10.NOW関数

TODAY()関数とほぼ一緒だけど、細かい時間帯が表示される関数です。

NOW()関数の画面 – bzbot(ビジボット)

TEXT関数を組み合わせることで、様々な表示形式で時間だけ抜き取ることができたりと様々便利です!

関数説明
ビジボット
NOW()
→yyyy/mm/dd hh:mm:ssで今の時刻が表示

11.TEXT関数

TEXT関数はNOW関数で話していた様々な表示形式に変更することができます。
例えば以下のような日付情報のみだけどそこから曜日を求めたり、時間だけ取得したりできます!

数式結果
=TEXT(TODAY(),”ddd”)
=TEXT(TODAY(),”dddd”)土曜日
=TEXT(“2019/02/09″,”yyyy”)2019
=TEXT(TODAY(),”mm”)02
=TEXT(TODAY(),”dd”)09
=TEXT(TODAY(),”yyyy-mm-dd”)2019-02-09
=TEXT(TODAY(),”yymmdd”)190209
=TEXT(NOW(),”hh:mm”)05:42
TEXT()関数の画面 - bzbot(ビジボット)
TEXT関数の画面 – bzbot(ビジボット)

この関数もかなりの頻度で使ってます。日付の横に曜日を入れたり、時間表示をhh:mmの時間だけにして例えば時間ごとの件数も拾えたりします!

関数説明
ビジボット
TEXT(“2019/02/09”,”ddd”)
→左辺の値を右辺で指定した形式で表示

12.DATE関数

DATEは特定の数字を日付データに変更します。
またEOMONTHは-1とか0とかを指定して、今月の最終日(毎月の末日)を把握できたりも可能です。

数式結果
=DATE(1991,6,19)1991/06/19
=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))1991/07/19
=EOMONTH(DATE(1991,6,19),-1)1991/05/31
=EOMONTH(DATE(1991,6,19),0)1991/06/30
=EOMONTH(DATE(1991,6,19),1)1991/07/31
=EOMONTH(DATE(1991,6,19),2)1991/08/31

DATE関数の画面 – bzbot(ビジボット)

DATE関数もよく使いますね。使い方は人それぞれだと思いますが、ここでの使い方例は控えておきます。(笑)EOMONTHは覚えておくと便利なので使い方は以下に記載しております!

関数説明
ビジボット
DATE(1991,6,19)
→指定した値を左から1991/6/19の日付データとして表示

【余談】
EOMONTH(DATE(1991,6,19),0)
→日付データの最終月を表示(0は当月,-1は前月,1は翌月..)

13.MID/LEFT/RIGHT関数

文字の抽出する関数です。今回は特定文字に対してMID、LEFT、RIGHTの取得を実施してもらいました。

数式結果
=MID(“2019年は恋愛の年”,5,10)年は恋愛の年
=LEFT(2019年は恋愛の年,4)2019
=RIGHT(2019年は恋愛の年,4)恋愛の年

MID/LEFT/RIGHT関数の画面 - bzbot(ビジボット)
MID/LEFT/RIGHT関数の画面 – bzbot(ビジボット)

それぞれ詳しく説明したデータは以下に設けてます!

関数説明
ビジボット
MID(“2019年は恋愛の年”,5,10)
→指定した値の5番目の位置から10文字まで表示

LEFT(“2019年は恋愛の年”,4)
→指定した値の左から4番目までを表示

RIGHT(“2019年は恋愛の年”,4)
→指定した値の右から4番目までを表示

14.LEN/ASC関数

文字数のカウントや全角データを半角に戻す関数です。LENは文字数のカウント、ASCが全角を半角に直します。

数式結果
=LEN(A2)4
=LEN(A3)7
=LEN(A4)4
=ASC(A2)1991
=ASC(A3)1 9 9 1
=ASC(A4)1991
LEN/ASC関数の画面 - bzbot(ビジボット)
LEN/ASC関数の画面 – bzbot(ビジボット)

ASCはあまり使う機会はないですが、LEN関数は複数関数と組み合わせて非常〜〜にお世話になってます..(笑)

関数説明
ビジボット
LEN(A2)
→値の文字数を表示

ASC(A4)
→値を全角から半角に変換

15.ROW/COLUMN関数

行と列の番号を取得することが可能です。

数式結果
=ROW(A2)2
=ROW(A3)3
=ROW(A4)4
=COLUMN(A2)1
=COLUMN(A3)1
=COLUMN(A4)1
 ROW()/COLUMN関数の画面 - bzbot(ビジボット)
ROW()/COLUMN関数の画面 – bzbot(ビジボット)

行と列の番号を取得する関数です。

関数説明
ビジボット
ROW(A2)
→行番目の値を表示

COLUMN(A4)
→列番目の値を表示

今回は基本的な関数についての説明でした。
結局体力がなくて書けなかったので、次回はさらに使い勝手がいい関数の紹介をしていきます!(笑)

最後までビジボットにお付き合いいただき、ありがとうございました!