Googleスプレッドシートの関数で良く使うものをピックアップ【なるべく逆引き】

当ページのリンクには広告が含まれています。

何かと便利なスプレッドシートですが、関数を使うとさらに便利! 使い方次第ではこんなことも…

  • 企業のDX化
  • EC関連の在庫管理
  • 業務に必要な自動変換
  • 情報交換

など、あらゆる場面で活躍する事ができます。実際にスプレッドシートで多くの作業を行わせて頂いてきた経験からよく使う関数を中心に、テキスト少なめ&具体的な内容のまとめ。

  • Googleスプレッドシートの関数が分かる
  • 応用した関数の記載方法が分かる
目次

Google公式 スプレッドシートについて

用語とスプレッドシートの説明用の定義

説明にはスプレッドシートとタブで分けられたシートがあり、ごっちゃになりやすいので、エクセルと同じ様(たぶん)に以下の定義で説明します。

  • 最小の1コマを「セル」、セルが「行」と「列」で纏まったものを「シート」、シートが1つのファイルにまとまったものが「ブック」。
呼び方の定義

Google スプレッドシートの関数リスト

当たり前ですが、公式の一覧が一番確実です。Excelの関数からイメージして入る人がほとんどだと思いますが、スプレッドシート独自の関数もあり、より複雑な処理が簡単に出来ます。

利用条件(2022年10月1日現在)

・1ブックで使えるセル数は1000万セル。

・列数は1万8278列(列 ZZZ)まで。

・Excelからインポートしたスプレッドシートの場合、上限はExcelとCSV のインポートで同一。

・ドキュメントをExcelからGoogleスプレッドシートに変換すると、5万文字を超える文字が入力されているセルはすべてスプレッドシートから削除。

Googleドライブに保管可能なファイルより(一部要約)

Googleスプレッドシートの関数の秀逸な使い方まとめ

各関数の基礎的な方法はGoogle検索で多くありますので、ここでは経験の中で一捻りした使い方を中心にまとめました。

接続関連

複数要素をタテかヨコにつなげる

サンプル19(タテ)

{F9;G9}

サンプル19(ヨコ)

{F9,G9}

CONCATENATE

セルに入力されている文字要素からキーになる文字を取得し、新しい文字列を作る

サンプル1

CONCATENATE("SIZE",RIGHT(A2,LEN(A2) - (FIND("-",A2))))

文字数をカウントして連結して改行する(100文字のうち何文字あるか)

サンプル20

CONCATENATE("入力",len(E9),"文字")&CHAR(10)&CONCATENATE("残り",100-len(E9),"文字")

ARRAYFORMULA

「E9+F9」を「23行目」まで繰り返しする。

サンプル4

ARRAYFORMULA(E9:E23+F9:F23)

ISBLANK

E列が「空白の時」はF列の値を「空白でなかったら」E列の値を置く事を「E列の23行まで」繰り返す。

サンプル5

ARRAYFORMULA(IF(ISBLANK(E9:E23),F9:F23,E9:E23))

SPLIT

「F列」の値から「部」を削除しつつ「空白」以外の値を取得する。

サンプル17

ARRAYFORMULA(IF(ISBLANK(F9:F23),"",SPLIT(F9:F23,"部")))

IFS

E9からE23で「11」だったら「あたり」、「22」だったら「はずれ」を表示する。

サンプル7

ARRAYFORMULA(IFS(ISBLANK(E9:E23),"",E9:E23 = 11,"あたり",E9:E23 = 22,"はずれ"))

ROW

行数より1少ない値を取得する。

サンプル6

ARRAYFORMULA((ROW(C9:C23)-1))

MONTH

「F列」の日付から「月日」の値から「月」を取得し「月」を付けて表示する。

サンプル18

ARRAYFORMULA(IF(ISBLANK(F9:F23),"",MONTH(F9:F23)&"月"))

VLOOKUP

「C5」のブックIDで「サンプル8」シートの「F9からH23」の値の中でF列が「E9」であるG列の値を取得する。

サンプル8

VLOOKUP(E9,IMPORTRANGE(C5,"サンプル8!F9:H23"),2,FALSE)

IMPORTRANGE

他のスプレッドシートからデータを取得する

サンプル2

IMPORTRANGE(C5, "サンプル1!x1:y2")

QUERY

「参照先」の「サンプル1」の「X1からZ2」を「ZがNULLでないものだけ」を左端として「ZYX」の順番で参照する

サンプル3

query(importrange(C5, "サンプル1!X1:Z2"),"select Col3,Col2,Col1 WHERE Col1 IS NOT NULL", -1)

F列の1行目をタイトルとし「F2からF23」まで何の値が入っていて、それがいくつあるかを取得する。

サンプル9

query(F9:H23,"select F,count(F) WHERE F='あ' OR F='い' OR F='う' OR F='え' OR F='お' group by F",1)

「L9からP23」の範囲の値を「P列」の日付の降順で「O、P、L、M、N」の順番で並べる。

サンプル10

query(L9:P23,"select O,P,L,M,N WHERE L='あ' OR L='い' OR L='う' OR L='え' OR L='お' order by P Asc",-1)

「C5」のブックID「サンプル11」シートの「L9からP15」で「P列(Col5)」が空欄で無いものを元の順番で並べ、「L16からP23」を同じ条件でその下の行に追加する。

サンプル11

{
query(importrange(C5, "サンプル11!L9:P15"),"WHERE Col5 IS NOT NULL", -1);
query(importrange(C5, "サンプル11!L16:P23"),"WHERE Col5 IS NOT NULL", -1)
}

「O9からS23」のデータから「日付(Q列)」が2022年4月1日以降2023年3月31日以内のものを「Q,O,P,R,S」の順番で日付順に取得する。

サンプル16

query(O9:S23,"select Q,O,P,R,S WHERE Q>=date'2022-04-01' and Q<date'2023-03-31'", -1)

COUNTIFS

「E9からE23]が「A」で「F9からF23」が「11」の組み合わせである数を取得する。

サンプル12

COUNTIFS(E9:E23,"A",F9:F23,"=11")

IFERROR

対象月の最終木曜日の日付(4週目の木曜日を最終とする)

サンプル13

IFERROR(FILTER(SEQUENCE(7,1,D7),WEEKDAY(SEQUENCE(7,1,D7))=5)+21," ")

「E列」の日付から「4か月前」の「1日」を取得する。日付以外の場合は空欄にする。

サンプル15

IFERROR(EOMONTH(E9,-5) +1," ")

IF

「E列」と「F列」を比較して、同じだったら「空欄」、違ったら「F列」の値を取得する。

サンプル14

IF(EXACT(E9,F9),"",F9)

指定日付のセルが空欄で無かったら指定日までの状況(日付)を取得

サンプル22

IF(E11-1<>"", TEXT(E11-1,"yyyy-mm-dd")&"まで処理済", "")

DATEDIF

2つの日付から期間を計算する

サンプル21

DATEDIF(E11,F11,"Y")

最後に…

この手の情報をまとめたいのですが、なかなか良いと思える見せ方が見つからないです。気が付いた時にアップデートしていきます。また、GoogleActionScriptも記事化する予定です。ですが関数だけでも出来ることは多いので、書き足していきます。

<ご注意>上記の紹介は、提供者や販売・運営者様及びその企業を差別や批判するものではありません。個人的な見解結果としての紹介となりますので、最終判断は皆さまご自身でお願いいたします。また、掲載内容は変更されていくのでこの記事記載日以降は内容が異なる場合もあります事ご了承ください。

随時更新していきますので、是非ブックマークをお願いいたします!

楽天市場で注目の商品![ sponsored ]

  • URLをコピーしました!
目次