CYBIRD Advent Calendar 2022 6日目担当、 今回初参加の@cy-yuka-WPです。2022年に新卒入社し、現在はイケメン戦国にてサーバー兼クライアントのエンジニアを担当しています。
前回は@yoooosuさんのUnityで制作したアプリをMeta Quest2(OculusQuest2)でビルドする方法でした。
はじめに
今回は、Google スプレッドシートやExcelの関数についてお話ししようと思います。今回このテーマにしたのは、実は私、入社するまでGoogle スプレッドシートにもExcelにも苦手意識があり、あまり触ってきませんでした。（セルの参照とか自動計算とか表とか、全然よくわからない…）
しかし！エンジニアって意外とGoogle スプレッドシートもExcelも業務で触るんですよ。
わからないままにもできず、頑張って学んだ中で「これ便利だな！」と感じた関数をまとめてみたので、学生時代の私のように苦手意識がある方や今から始めてみようかなという方の参考にもなればいいなと思います。
概要
Google スプレッドシートをメインにExcelでも使っていて便利だなと感じた関数を初級編、中級編、上級編にわけてまとめてみました。
本題
初級編
SUM(),SUMIF()
すごく簡単な関数なのですが、Google スプレッドシートやExcelの便利さを感じるところから始めるならまずはここかなと！
指定範囲内の値の合計値を取るならこの二つは覚えておくと便利です！
書き方 SUM():SUM("セルの範囲")
SUM()はこんな風に指定した範囲内の値を合計してくれます。
今回はE列全体にしていますが、行ごとだったり、セルで範囲を指定することも可能です！
大量のデータの合計値を求める時に電卓だと、一つミスすると１からやり直しだったのが解消されるので業務以外でも計算がいるときは使ってます！ちなみにセルにマイナス値で値を入れると引き算もできるので便利ですよ！
書き方 SUMIF():SUMIF("検索範囲","検索値","表示させたい範囲")
SUMIF()は先ほどのSUM()とあまり変わらないのですが、なんと計算する条件を指定できます！
一つ一つのセルを指定せずに範囲でまとめて計算したいけど、この条件の値しか計算したくないという時におすすめです！
同じような関数でSUMPRODUCT()があるのですが、こちらは少し中級者向けなので、まずはSUMIF()で慣れてから、これでは出来ない条件の指定がしたい！となった時に使うといいと思うので今回は割愛します。
余談ですが、スプレッドシートって実はこの上のところに関数の結果を表示してくれる便利機能があったりします。
ここに思った結果が出ないと式が間違っているのがわかるので、参考にしてください！
COUNT(),COUNTA(),COUNTIF()
続いてこちらの３つはデータの個数を調べる時に使います！
数値の合計じゃなくて、データがいくつあるか知りたい！そんな時に便利な関数です。
書き方 COUNT():COUNT("セルの範囲")
まずはCOUNT()。指定した範囲内に数値がいくつあるかが調べられます。
ただし、数値しか調べられないので、
文字列だと「データは見つからなかったよー」という結果になります。
そんなときは、
COUNTA()を使ってあげるとデータの数をちゃんと返してくれます。
COUNT()とCOUNTA()の両方にあるこの"値1,[値2,…]"はなんぞやって思いますよね。
実はこれ、SUM()にもあるんですけど、",(カンマ)"区切りで値や範囲をいくつも指定できちゃいます！という表示なんです。これも是非活用してほしいです。
書き方 COUNTIF():COUNTIF("検索範囲","検索値")
３つ目は、COUNTIF()。これは「この範囲の中に、このデータはいくつあるのか」を調べられる関数になっています。
今回だと「（範囲）カテゴリーの欄の中に（条件）フルーツはいくつあるのか」を調べています。
無作為に集めたデータを集計する際に結構便利だったりします。
ROUNDDOWN(),ROUNDUP()
計算をしていたり集計をしていると少数が出てきたけど、整数だけにしたいとか少数第○位までしか表示させたくないことってあるじゃないですか。
そんな時に便利なのがROUNDDOWN()とROUNDUP()です！
書き方 ROUNDDOWN():ROUNDDOWN（対象の値,桁数）
ROUNDDOWN()は指定した値以下の少数は切り捨てる関数です。
ROUNDDOWN（51.5,0）と入れると小数点以下は切り捨てられ「51」だけが残ります。
書き方 ROUNDUP():ROUNDUP（対象の値,桁数）
また、書き方はほとんど同じだけど、少し動作が違う、ROUNDUP()。こちらは指定した値以下の少数を切り上げる関数です。
ROUNDDOWN（51.5,0）と入れると小数点以下は切り上げられ「52」だけが残ります。
IF()
書き方 IF():IF(条件、trueの処理、falseの処理)
IF()はある条件に対して、あっているか間違っているか、それに対してどういう動きをさせるのかを指定する関数です。
例えば、IF("セル名"=2,"OK","NG")、この場合は指定したセルに2が入っていれば「OK」が表示され、他の値が入っていれば「NG」が表示されます。
中級編
IFERROR()
書き方 IFERROR():IFERROR(対象の値、エラー時の処理)
処理的にエラーは出てしまうが、そんな時に別の表示をさせたいことってあるじゃないですか！（データが未入力でエラーになる時は空白にするとかエラーの場合は別の内容を表示させたい時とか）
例えば、画像のように参照先の値が未確定なので仮で0を入れておきたいがこのセルでは割り算の処理がしたい。
もちろん0÷0はエラーになります。
ですが、=C2/C3だった部分を=IFERROR(C2/C3,"NG")に書き換えると、
エラーが表示されていた部分に「NG」が表示されるようになります！
エラーではなく正しい動作をする場合は通常の値が表示されます。
意外とここはエラーになるのは仕方ないんだけど、そういう時はこの値を表示させたいや空白にしたいんだよねという時って使っていると出てくるので、知っていると便利ですよ！
SUBSTITUTE()
書き方 SUBSTITUTE():SUBSTITUTE(文字列、検索文字列、置換文字列)
参照先のデータは使いたいけど、この文字は別の文字にしたい…！なんてことないですか？（書き換えると困る別のシートからデータを引っ張ってくる時とか結構ある。日付の表記とか。すごくある。）
そんなん時に使えるのがSUBSTITUTE()！この関数とっても便利なんです。
例えば、参照元では2022-12-6とあるが、このセルでは2022/12/6と表示したい！しかし、参照元の数値は使いたいから-だけを/に変更できたら…という時は、
=SUBSTITUTE("参照元のセル名","-","/")というふうに入力すると、参照元の2022-12-6が、2022/12/6に変換されて表示されるようになります！こんなにお手軽に文字の置き換えができる関数があると知った時とても感動しました。
ぜひ、文字の置き換えができたらそのままこのデータを参照したい！という時などに使ってみてください。
MATCH()
書き方 MATCH():MATCH(検索値、検索範囲、[照合の種類])
MATCH()は、検索値が検索範囲内で相対的に何番目の行または列の位置を表す数値を返してくれる関数です。
このデータって、この表で何番目の行(または列)にあるんだろう？と思った時に使えるのがこの関数です！
この関数の便利なところは検索範囲の指定の仕方次第で、行も列も取得できるところです。
まずは行を取得したい時。この時は、検索の範囲を単一列で指定してあげると検索することができます。
２列以上での指定をするとエラーになるので必ず単一列で指定します。
次に、列を取得したい時。この時は、検索の範囲を単一行で指定してあげると検索することができます。
横を知りたいときは縦に、縦を知りたい時は横に…ややこしいですね。頑張りましょう…！
こちらも２行以上での指定をするとエラーになるので必ず単一行で指定します。
上記二つで0を入力している、[照合の種類]ですが、こちらは入力値が３種類あります。
1:検索値以下の最大の値の箇所を返してくれます。こちらは範囲内のデータを昇順に並べ替えている必要があります。
0:検索値と完全一致するデータの値を返してくれます。こちらは範囲内のデータを並べ替えている必要はありません。
-1:検索値以上の最小の値の箇所を返してくれます。こちらは範囲内のデータを降順に並べ替えている必要があります。
MATCH()は単体でも使われることが多いのですが、INDEX()と一緒に使われることも多いです。INDEX()に関しては今回は説明しませんが、気になる方はよければ検索してみてください！
REGEXMATCH()
書き方 REGEXMATCH():REGEXMATCH(テキスト、正規表現)
REGEXMATCH()はテキスト内に正規表現の内容が含まれているか否かを知ることができる関数です。
単体でも使えますが、IF()と一緒に使うことが多い印象が多いです。
関数の名前的になかなか難しそうなものに見えますが、書き方や処理の内容はそんなに複雑なものじゃないので、是非使ってみてほしいです！
上級編
INDIRECT()
ついに上級編！こちらの関数は、別シートからデータを参照したいときに利用する関数です。
別ファイルから参照するなら、='参照元シート名'!セル名でできるのですが、
画像のように、動的にC２に入れたシート名のデータを取得したい！と思ったときに、先ほどの方法だとエラーが出ます。
そんな時に、INDIRECT()を使うとこのエラーが解消できます。
書き方 INDIRECT():INDIRECT("参照元シート名!セル名")
動的にC２に入れたシート名のデータを取得をするには=INDIRECT("'"&C2&"'!B2")を入れると参照ができ、無事、「みかん」シートのB2にある、「デコポン」が取得できました。これを使うと、
C２の部分を「りんご」に書き換えると「青リンゴ」は取得！というように簡単に別シートのデータを取得できます。
INDIRECT()でも='参照元シート名'!セル名と同じように=INDIRECT("参照元シート名!セル名")で直接シート名を指定することも可能です。
別シートの参照なので難しそうに思えますが、実際記述する内容はかなりシンプルで、使えるとできることが広がると思うので、ぜひ使ってみてください！
また、スプレッドシートでのみ使える関数なのですが、IMPORTRANGE()を使うと別ファイルの参照も行うことができます！
QUERY()
最後にQUERY()。こちらの関数はスプレッドシートで使用できる関数となっています！
指定した範囲のデータから条件を指定して抽出してくれる関数です。
書き方 QUERY():QUERY("参照元シート名!検索範囲",クエリ,[見出し])
ある表のこの列とこの列は欲しいけど、他の部分は使わないなぁという時や、
ある表のこの条件に当てはまる行のデータだけで一覧にしたいという時に使えるのがこの関数なんです。
例えば、果物屋さんというシートに上の画像のような一覧があったとします。
この中で、店舗名、商品、売り上げ個数だけがほしい！
そんな時は、
=QUERY('果物屋さん'!B:H,"select C,F,H")というようにクエリの部分に"select C,F,H"とかくと、C列とF列とH列のみを選んで表示させますよという処理を書くことができます。また、書いた順番に左から表示されるので、実際の参照元と表示させる順番を入れ替えることも可能です。
「select」の後ろは列のみ指定できます。
select *にすると全ての列を選択できます。
次に先ほどの、果物屋さんシートの一覧の渋谷店のデータだけを表示させたい！
そんな時は、
=QUERY('果物屋さん'!B:H,"select * where C = '渋谷店'")というようにクエリの部分に"select * where C = '渋谷店'とかくと、全ての列で、C列が店舗名になっているデータのみが表示されます。「where」の部分は複数の条件をつけることができます！
ちなみに、このC = '渋谷店'の部分を一工夫すると、
↓
O2の部分を書き換えるだけで、渋谷店のデータから池袋店のデータに切り替えられるようになります。
これは、C = '渋谷店'をC = '"&O2&"'というふうに"&&"で指定したいセル名を挟んであげると動的な変更ができるようになります！
もちろん列と行どっちも表示範囲を選択することも可能です。
この関数は結構できることが多く、他にも昇順降順に並べ替えたりと全部書くと大変なことになるので、今回は一番よく使われる書き方飲みの説明だけにしようと思います！でも、使えると表の作成が便利になったりするので、気になった方やもっと使えるようになりたい方は調べてみてくださいね！
最後に
今回の記事はいかがだったでしょうか？少しでもスプレッドシートやExcelへの興味が湧いたり、苦手意識が減ったや面白いかもしれないと思ってもらえたらいいなと思います！今回書いたもの以外にも便利な関数はたくさんあるので、少しでも前向きな気持ちを持つことができて、色々知りたいと思ったら調べていただけたらなと思います！
明日は7日目！@tomoko_ishizakaさんの記事です！AWSのIAM周りとアカウントについての記事のようなので、是非是非読んでみてください！