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

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

はじめに

前回に続き、スプレッドシートの関数を紹介していきます!
今回はスプレッドシートにしかない関数もあり、それが非常に強力な関数ですのでぜひ参考にしてもらえればと思います!実際の業務でも凄く使う関数なので、ぜひ覚えていってください!
↓↓ビジボットの前回記事↓↓

それでは早速始めます!

16.VLOOKUP関数

さぁ、超有名な関数ですね!VLOOKUP関数!
これは、指定したキーに一致する値と同じ行にある様々な情報を取得することができます。

今回の例で言えば、ロボットIDをキー(セルE2)として、そのキーに一致する値セルE2をA列の中から探し、C列の情報まで参照できるようにしておきます。

そして、1番目の値(A列)、もしくは2番目の値(B列)、もしくは3番目の値(C列)と、指定して求める結果を表示します!

ロボットID数式結果
1001=VLOOKUP(E2,A:C,1,0)1001
1002=VLOOKUP(E3,A:C,2,0)ロボ巳
1003=VLOOKUP(E4,A:C,3,0)2005/04/19

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

例えば、『VLOOKUP(E4,A:C,3,0)』の場合、キーに一致する値セルE4をA列で探し、一致した値の3番目の値を取得する。ってことになります。したがってスプレッドシートの図を見ればわかりますが『2005/04/19』が表示されます。

関数説明
ビジボット
VLOOKUP(A2,A:C,1,0)
A2→検索キー – 検索する値です
A:C→範囲 – 検索対象の範囲です。範囲の先頭列で検索キーとして指定したキーを検索します。
1→番号 – 値を返す列の番号です。範囲の先頭列を 1 とします
最後は0 or FALSEが一般的です。それ以外は1 or TRUEですが私は使ったことがほぼありません..

17.ARRAYFORMULA関数

これ、めちゃくちゃ便利な関数です。
配列で情報を一気に受け渡すことができます!また、Excelでも同様の配列を使った関数がありますが、処理めちゃくちゃ遅くなります…
以前Excelの処理をなるべく軽くするために配列を使った方が本来は軽くなると思いやったのですが、逆にものすごく重くなり使い物にならなくなりました。

しかし、そのExcelとは比較にならないくらいスプレッドシートの配列関数は優秀です!
ぜひ活用してみてください!

数式結果結果結果
=ARRAYFORMULA(A2:C4)1001ロボ太2000/02/10
1002ロボ巳2002/06/11
1003ロボ子2005/04/19

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

関数を入れるのはセルF2の青くなっているところに入れるだけで、取得した値が自動的に展開されます!

関数説明
ビジボット
ARRAYFORMULA(A2:C4)
1 つの範囲か、1 つのセル範囲または同じサイズの複数範囲を使用する数式か、1 つのセルより大きい結果を返す関数を指定します。

様々な利用方法に転換できる関数なので、以下のような使い方もできます。
VLOOKUP(Z1,(ARRATFORMULA(A2:C4)),2,0)

18.IMPORTRANGE関数

これもめちゃくちゃ便利な関数です!
Excelでは他のブック参照って色々不便だったりしますが、これは別のスプレッドシート情報をとっても簡単に取得できるので非常に重宝します!
※テーブル表示が間延びしちゃうので『数式』は以下に直接書きます

 =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1656FCvnUwNNMOcRxwbXUkARzzeV2ktzs6VTCTcdllEM/edit#gid=1006007648″,”‘SUMIFS②’!A:E”) 

これは簡単に説明すると、取得したいシートがあるスプレッドシートのURLと、そのスプレッドシートのどの範囲を取得するか?を指定すると以下結果が取得できます!

結果結果結果結果結果
ロボットID個数@単価販売者売上 (=SUM(B2*C2))
10012450Aさん900
10024500Bさん2,000
10012450Aさん900
10031300Bさん300
10025500Aさん2,500
10014450Aさん1,800
10013450Aさん1,350
100410550Bさん5,500
10034300Aさん1,200
10023500Aさん1,500
10011450Bさん450
10042550Aさん1,100

このIMPORTRANGE関数は必ず最初エラーになります。
それはシート間でリンクをするためのアクセス許可が必要になるからです。
最初はなんでエラーなんだ…ってなりがちですが、関数をクリックすると以下表示がされるので、『アクセスを許可』してあげましょう!

アクセス許可を求められる - bzbot(ビジボット)
アクセス許可を求められる – bzbot(ビジボット)

あと、もう一つ結構間違えやすいのが、URLが入る部分や範囲指定する部分は全て「””」で囲みます。

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

シート名を省略することは可能!しかし、一番始めのシートが対象になります。

この関数の一番嬉しいところは取得元のデータを更新すると、読み込んでいるデータ側も自動的に更新がかかり同期されたデータが表示されることです!
これにより、わざわざコピペをしないでも同じ内容が表示されてるってことです!

関数説明
ビジボット
IMPORTRANGE(“https://docs.google.com/spreadsheets/d/abcd123abcd123”, “シート1!A1:C10”)
URL→スプレッドシートキー – データの読み込み元となるスプレッドシートの URL を指定します。
シート範囲→スプレッドシートキーの値は二重引用符で囲むか、適切なテキストを含むセルへの参照にする必要があります。

19.UNIQUE関数

これもめちゃくちゃ便利な関数です!
Excelでは重複データの削除ってぽちぽちマウスをするか、重複データをCOUNTIFSなどでカウントしてユニークな値だけを取り出すってことをしなければなりません。

関数で列を指定したらユニークな値に自動的に表示される関数がないかなぁ〜?
って、思ったら全くそのまま理想な関数がありました!!(笑)

数式結果
=UNIQUE(A:A)1001
1002
1003
1004

以下ロボットIDのユニークな値のみ(重複のないデータ)を表示したいと思った場合、以下のように指定する。

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

この関数のメリットはA:A列に入っている値が自動的に増えたとして場合、自動的に結果も増えてユニークな値として表示されるんです。
なんか今まで関数の最後まで入っている行を凄く気にしたりしてた時が懐かしいな…(笑)

関数説明
ビジボット
UNIQUE(A:A)
A:A→範囲 – 一意の要素でフィルタ処理するデータを指定します。

本日はここまでです。
明日以降はこれら紹介した関数を使って実際にどういうことを業務に活かせるか?などをトライしてみようと思います。
しかし、Googleスプレッドシートの関数はぶっ壊れたような機能が多いです!!(笑)
他にも協力な関数はありますが、一旦はここまでとさせてください。(笑)

最後までお付き合い頂きありがとうございました!