スプレッドシートの関数：業務で使うXLOOKUP・QUERY・IMPORTRANGE

Googleスプレッドシートで業務の台帳を作ると、すぐに「別のシートから名前を引きたい」「月ごとに数えたい」「別のファイルの数字をこっちに持ってきたい」という場面が来ます。この3つが関数で書ければ、小さな会社の台帳はほとんど回ります。この記事では、業務で実際に使う関数を15個に絞り、どの場面で何を使うかを表にしたうえで、XLOOKUP・QUERY・IMPORTRANGEを中心に式をそのまま載せます。例は全記事で共通の、スタッフ6名ほどの予約制の小さな料理教室「サンプル料理教室」の会員台帳と出席表です。前提として、台帳のシートがすでにあり、1行1件・1列1項目で入っている状態から始めます。台帳の作り方そのものは別の記事に分けています。

この記事の内容

1. 先に決めること
2. 業務でよく使う関数の一覧
3. 手順1：会員IDから名前を引く
4. 手順2：月ごとに集計する
5. 手順3：別のファイルから取り込む
6. 応用
7. Excelと違うところ
8. できないことと、その代わり
9. よく詰まるところ
10. 次にやること

先に決めること

関数の書き方より先に、台帳の形を決めておかないと、どの関数を使っても途中で崩れます。式を書く前に次の4つを決めておきます。

1つ目は、キーにする列です。会員を引くときに「名前」で引くと、同姓同名や表記ゆれ（「山田 花子」と「山田花子」）で必ず外れます。サンプル料理教室では会員台帳のA列に「会員ID」（M0001 のような文字列）を置き、出席表や月謝表には名前ではなく会員IDを入れます。名前は関数で引いて表示するだけにします。

2つ目は、1行目を見出しにして、2行目からデータにすることです。QUERYやFILTERは見出し行を前提にした書き方が楽なので、見出しを2行にしたり、表の上にタイトル行を入れたりしないでおきます。タイトルを置きたいときは別のシートに置きます。

3つ目は、日付の列に日付以外を入れないことです。「10/5（予定）」「未定」のような文字が1つ混ざるだけで、QUERYはその列を日付として扱えなくなります。備考は別の列に分けます。

4つ目は、集計を同じファイルでやるか、別のファイルでやるかです。体験レッスンの申込はフォームの回答先のファイル、会員台帳と月謝は別のファイル、と分けていることが多いはずです。ファイルが分かれていると、集計にはIMPORTRANGEが要ります。サンプル料理教室では、受付が触る「体験予約」のファイルと、代表と経理が触る「会員台帳」のファイルを分け、月の集計は会員台帳のファイルの「集計」シートで、体験予約をIMPORTRANGEで取り込んで行います。ファイルを分けるのは、受付に月謝の金額を見せないためです。

業務でよく使う関数の一覧

まず全体像です。小さな会社の台帳では、この15個でほとんど足ります。関数リストには数百の関数が載っていますが、全部覚える必要はありません。

関数	何をするか	サンプル料理教室で使う場面
SUM	合計	月謝の今月の合計
COUNTIF	条件に合う件数を数える	在籍中の会員の人数
SUMIFS	複数の条件に合う行だけ合計	10月分の平日夜コースの月謝合計
IF	条件で表示を分ける	入金日が空なら「未入金」と出す
IFERROR	エラーのときに代わりの値を出す	会員IDが見つからないとき空欄にする
VLOOKUP	表の1列目で探して、右の列の値を返す	古いシートの名前引き
XLOOKUP	検索範囲で探して、結果範囲の値を返す	出席表で会員IDから名前を引く
FILTER	条件に合う行だけ取り出す	未対応の体験申込だけ一覧にする
UNIQUE	重複を除く	講師の名前の一覧を作る
SORT	並べ替える	体験申込を希望日順に並べる
QUERY	表に対して集計・抽出の命令を書く	コース別の人数と月謝合計
IMPORTRANGE	別のスプレッドシートから範囲を取り込む	体験予約のファイルを集計シートに取り込む
ARRAYFORMULA	1つの式を列全体に広げる	出席表の名前を全行まとめて引く
TODAY	今日の日付	今日の体験予約だけ出す
TEXT	数値や日付を指定した形の文字にする	日付を「2026-10」の月の形にする

関数の種類でいうと、XLOOKUPとVLOOKUPは「参照」、FILTER・UNIQUE・SORTは「フィルタ」、QUERYとARRAYFORMULAは「Google」、IMPORTRANGEは「ウェブ」に分類されています。この3つはExcelから来た人がいちばん戸惑う関数です。

手順1：会員IDから名前を引く

サンプル料理教室の会員台帳ファイルには、次の2つのシートがあるとします。

シート	A列	B列	C列	D列	E列	F列	G列
会員台帳	会員ID	氏名	ふりがな	入会日	コース	月謝	状態
出席	日付	会員ID	氏名	レッスン名	講師	月

出席シートのB列に会員IDを入れたら、C列に名前が出るようにします。

XLOOKUPで1行ずつ引く

1. 出席シートのC2セルをクリックします。
2. 次の式を入力して、Enterキーを押します。

=XLOOKUP(B2, 会員台帳!A:A, 会員台帳!B:B, "未登録")

1. C2セルの右下の青い四角をつかんで、下の行まで引っぱってコピーします。

XLOOKUPの形は XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード) です。検索範囲は1列か1行で指定し、結果の範囲は検索範囲と同じ行数にします。4つ目の「見つからない場合の値」を省くと、見つからないときに #N/A が出ます。上の式では「未登録」と出るようにしています。一致モードは省略すると 0（完全一致）なので、会員IDのような完全一致の検索では書かなくて済みます。

検索モードに -1 を入れると、表の下から探します。同じ会員IDが何度も出てくる出席表から「その会員の最後の出席日」を取りたいときに使えます。

=XLOOKUP(A2, 出席!B:B, 出席!A:A, "出席なし", 0, -1)

会員台帳のH列にこの式を入れると、会員ごとの最終出席日が出ます。日付が数字で出たら、表示形式を日付にします（後述のよく詰まるところを参照）。

VLOOKUPとの違い

前任者が作ったシートにはVLOOKUPが残っていることが多いので、読めるようにしておきます。

=VLOOKUP(B2, 会員台帳!A:G, 2, FALSE)

VLOOKUPは VLOOKUP(検索キー, 範囲, 番号, 並べ替え済み) で、範囲の1列目で探して、左から数えて「番号」列目の値を返します。最後の FALSE が完全一致の指定です。これを省くと既定値の TRUE（近似一致）になり、会員IDの並びによっては別の人の名前が出ます。VLOOKUPで書くなら FALSE は必ず付けます。

XLOOKUPに置き換えたほうがよい理由は3つあります。キーより左の列も引けること、台帳に列を1本足しても「番号」がずれないこと、見つからないときの値を式の中で決められることです。新しく書く式はXLOOKUPで書き、古いVLOOKUPは触る機会があったときに置き換えます。

列全体にまとめて引く（ARRAYFORMULA）

出席表は毎日行が増えます。行が増えるたびに式をコピーするのを忘れると、名前の出ない行ができます。1つの式で列全体に広げると、この漏れがなくなります。

1. 出席シートのC列のC3以下に入っている式をすべて消します。
2. C2セルに次の式を入れます。

=ARRAYFORMULA(IF(B2:B="", "", IFERROR(VLOOKUP(B2:B, 会員台帳!A:B, 2, FALSE), "未登録")))

B列が空の行は空欄、会員IDが見つからない行は「未登録」、見つかった行は名前が出ます。IF(B2:B="", "", ...) を外すと、データの無い最終行まで「未登録」が並ぶので必ず入れます。ARRAYFORMULAは、数式の編集中に Ctrl+Shift+Enter（Macは ⌘+Shift+Enter）を押すと先頭に自動で付きます。

この式の下（C3以下）に何か入力すると、式が広がれなくなってエラーになります。ARRAYFORMULAを入れた列には手で入力しない、と担当者に伝えておきます。

手順2：月ごとに集計する

COUNTIFS・SUMIFSで1マスずつ数える

集計シートのA列に月の初日（2026/10/1、2026/11/1…）を入れ、B列に出席数、C列に月謝の合計を出します。

=COUNTIFS(出席!$A:$A, ">="&A2, 出席!$A:$A, "<"&EDATE(A2, 1))

「その月の1日以上、翌月の1日未満」で数えています。月末の日付を条件にすると、時刻が入った日付が漏れるので、翌月1日の「未満」で書くのが安全です。

=SUMIFS(会員台帳!$F:$F, 会員台帳!$G:$G, "在籍", 会員台帳!$E:$E, "平日夜コース")

在籍中で平日夜コースの会員の月謝の合計です。SUMIFSは合計する範囲を先に書き、そのあとに「条件の範囲, 条件」を組で並べます。SUMIFは合計範囲が最後に来るので、並びを間違えやすいところです。

在籍人数だけなら =COUNTIF(会員台帳!G:G, "在籍") で足ります。

QUERYでまとめて集計する

月が増えるたびに行を足すのが面倒なら、QUERYで一度に出します。QUERYは QUERY(データ, クエリ, 見出し) の形で、2つ目の引数にGoogle Visualization APIのクエリ言語という、SQLに似た命令を文字で書きます。

まず出席シートのF列（月）に、日付から「2026-10」の形の月を作ります。F1に見出し「月」と入れ、F2に次の式を入れます。

=ARRAYFORMULA(IF(A2:A="", "", TEXT(A2:A, "yyyy-mm")))

集計シートの空いたセルに次の式を入れます。

=QUERY(出席!A1:F, "select F, count(B) where B is not null group by F order by F label count(B) '出席数'", 1)

月ごとの出席数が、月の昇順で並んで出ます。

QUERYの命令は、句の順番が決まっています。select、where、group by、pivot、order by、limit、offset、label、format、options の順で、順番を入れ替えるとエラーになります。業務でよく使うのは次の5つです。

句	意味	書き方の例
select	どの列を出すか	select A, B, E
where	どの行を出すか	where G = '在籍' and E = '平日夜コース'
group by	どの列でまとめるか	group by E
order by	並べ替え	order by D desc
label	見出しの名前を変える	label count(A) '人数'

集計に使える関数は count()・sum()・avg()・max()・min() の5つです。

コース別の在籍人数と月謝の合計は次のとおりです。

=QUERY(会員台帳!A1:G, "select E, count(A), sum(F) where G = '在籍' group by E label count(A) '人数', sum(F) '月謝合計'", 1)

入会日が2026年10月1日以降の会員だけを出すときは、日付を date 'yyyy-MM-dd' の形で書きます。

=QUERY(会員台帳!A1:G, "select A, B, D, E where D >= date '2026-10-01' order by D", 1)

日付をセルから取りたいとき（H1に日付を入れておく場合）は、TEXTで文字にしてつなぎます。

=QUERY(会員台帳!A1:G, "select A, B, D, E where D >= date '"&TEXT(H1, "yyyy-mm-dd")&"' order by D", 1)

where の中の文字は大文字と小文字を区別します。「Abc」と「abc」は別物として扱われます。区別したくないときは where lower(B) contains 'abc' のように lower() をかけます。contains（含む）と starts with（で始まる）も使えます。

QUERYの3つ目の引数は見出しの行数です。見出しが1行なら 1 と書きます。省くと中身から推測されますが、推測が外れると見出しがデータの1行目とくっついて出ることがあるので、毎回書きます。

日付から月を作らずにQUERYで集計する

F列を足したくない場合は、QUERYの中で year() と month() を使えます。ただし month() は1月が 0、2月が 1 という0始まりの値を返します。そのまま出すと「9」が10月を意味することになるので、+1 します。

=QUERY(出席!A1:E, "select year(A), month(A)+1, count(B) where A is not null group by year(A), month(A)+1 label year(A) '年', month(A)+1 '月', count(B) '出席数'", 1)

式が長くなり、label にも同じ書き方を繰り返す必要があります。現場で直す人のことを考えると、TEXTで月の列を作るほうが読みやすいです。

手順3：別のファイルから取り込む

体験予約のファイルの「予約一覧」シートを、会員台帳のファイルの集計シートへ取り込みます。

IMPORTRANGEの書き方と許可

1. 体験予約のファイルを開き、ブラウザのアドレスバーのURLをコピーします。
2. 会員台帳のファイルに「体験取込」というシートを作ります。
3. 体験取込シートのA1セルに次の式を入れます。URLの部分は手順1でコピーしたものに置き換えます。

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXXXXXXXX/edit", "予約一覧!A1:H")

1. 数秒すると、セルに #REF! が出ます。セルにカーソルを合わせると「これらのシートをリンクする必要があります」と表示されます。
2. 「アクセスを許可」をクリックします。
3. 予約一覧のデータが、A1から右と下に広がって表示されます。

形は IMPORTRANGE(スプレッドシートのURL, 範囲の文字列) です。URLと範囲はどちらも " で囲みます。範囲の文字列でシート名を省くと、取り込み元の最初のシートから読み込まれます。シートの並びを変えたときに中身が変わってしまうので、シート名は必ず書きます。

手順5の「アクセスを許可」が出るのは、取り込み元のファイルに自分がアクセスできる場合です。取り込み元のオーナーが別の人で、自分に共有されていないときは、取り込み元のURLを開いてアクセスをリクエストし、オーナーが許可するまで待ちます。

一度許可すると、取り込み先のファイルを編集できる人は全員、取り込み元のどの範囲でも読み込めるようになります。ここは見落としやすいところです。会員台帳のファイルの編集者が、体験予約のファイルに直接アクセスする権限を持っていなくても、IMPORTRANGEの式を書き換えれば体験予約の別のシートを読めます。取り込み元に、取り込み先の編集者に見せたくないシートがあるなら、そのシートは別のファイルに移しておきます。

この許可は、取り込み元へのアクセスを許可した人がファイルから外されるまで続きます。許可した担当者が退職してアカウントを消すと、ある日いきなり #REF! になることがあります。取り込み元のファイルは共有ドライブに置き、許可は管理を担当する人のアカウントで行っておきます。

取り込んだデータをQUERYで絞る

IMPORTRANGEで取り込んだあと、別のシートでさらにQUERYをかけるより、IMPORTRANGEをQUERYで包むと1つの式で済みます。このとき、列は A・B ではなく Col1・Col2 の形で書きます。QUERYのヘルプにはどちらの表記も受け付けるとだけ書かれていますが、実際に運用していると、IMPORTRANGEを包んだQUERYで A・B と書くと列が見つからないエラーになり、Col1・Col2 に直すと通ります。

予約一覧の列が「タイムスタンプ・氏名・メール・希望日・コース・経路・対応状況・入会」だとすると、入会した人だけを希望日順に出す式は次のとおりです。

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXXXXXXXX/edit", "予約一覧!A1:H"), "select Col2, Col4, Col5 where Col8 = '入会' order by Col4", 1)

月ごとの体験数と入会数をデータポータル（旧Looker Studio）でグラフにするときも、この取り込み済みのシートをデータソースにすると、受付のファイルを直接つながずに済みます。

FILTER・SORT・UNIQUE・TODAYで一覧を作る

QUERYの命令文が読みにくいと感じる担当者には、FILTERのほうが分かりやすいです。予約一覧から対応状況が「未対応」の行だけを出すには次の式です。

=FILTER(予約一覧!A2:H, 予約一覧!G2:G = "未対応")

条件の範囲は、取り出す範囲と同じ長さにします。A2:H に対して G2:G のように、開始行と終了行をそろえます。条件に合う行が1つも無いと #N/A が返るので、受付が見る画面では IFERROR で包みます。

=IFERROR(SORT(FILTER(予約一覧!A2:H, 予約一覧!G2:G = "未対応"), 4, TRUE), "未対応の申込はありません")

SORTの 4, TRUE は「4列目（希望日）を昇順で」という意味です。

今日が希望日の申込だけを出すときは、TODAYを使います。希望日の列に時刻が入っている場合に備えて、INT で日付だけにして比べます。

=FILTER(予約一覧!A2:H, INT(予約一覧!D2:D) = TODAY())

講師の名前の一覧（プルダウンの元にする場合など）は、UNIQUEとSORTで作ります。

=SORT(UNIQUE(FILTER(出席!E2:E, 出席!E2:E <> "")))

応用

IFで状態を表示する

月謝シートで、入金日が空なら「未入金」、入っていれば「入金済」と出すには次の式です。D列が入金日です。

=ARRAYFORMULA(IF(A2:A="", "", IF(D2:D="", "未入金", "入金済")))

月末のGASによる請求書の作成では、この列が「未入金」の行だけを対象にすると、二重請求を防げます。

TEXTで日付や金額の見せ方をそろえる

請求書やメールの本文に日付を差し込むときは、TEXTで文字にしておくと表示がぶれません。

式	結果の例
=TEXT(D2, "yyyy年m月d日")	2026年10月5日
=TEXT(D2, "yyyy-mm")	2026-10
=TEXT(D2, "m/d（ddd）")	10/5（月）
=TEXT(F2, "#,##0円")	12,000円

曜日が英語で出るときは、ファイルの言語と地域の設定が日本になっていません。「ファイル」→「設定」→「全般」の「言語と地域」を「日本」にして「設定を保存」をクリックします。言語と地域を変えると、通貨・日付・数値の既定の表示形式も変わります。

長い式を読みやすくする

同じ範囲を何度も書く式は、LETで名前を付けると読みやすくなります。

=LET(id, B2, 台帳, 会員台帳!A:G, XLOOKUP(id, CHOOSECOLS(台帳, 1), CHOOSECOLS(台帳, 2), "未登録"))

小さな会社の台帳ではここまでやらなくても回ります。式を直す人が自分しかいない会社では、短い式を複数のセルに分けたほうが、あとから読む人に親切です。

Excelと違うところ

Excelのファイルを持ち込んだり、Excelで覚えた書き方をそのまま入れたりすると、ここで引っかかります。

違い	Googleスプレッドシート	Excel	実務での扱い
独自の関数	QUERY・IMPORTRANGE・GOOGLEFINANCE・GOOGLETRANSLATE・IMAGE・SPARKLINEがある	同名の関数はない	Excelに書き出すとこれらの式は動かない
使えない関数	CALL・CUBE系・INFO・REGISTER.ID・RTD・WEBSERVICEは動かない	動く	マクロの代わりはGAS
配列の書き方	{1, 2; 3, 4} のように中括弧で作れる。列はカンマ、行はセミコロンで区切る	同じ考え方	小数点がカンマの地域では区切りがバックスラッシュになる
配列の広がり	多くの配列の式は、ARRAYFORMULAなしでも隣のセルへ自動で広がる	Excel側の挙動は版によって違う	広がる先は空けておく
別ファイルの参照	IMPORTRANGEでURLを指定する	ファイルのパスで参照する	Excelの外部参照は変換後に壊れるので書き直す
マクロ	GAS	VBA	VBAはそのままでは動かない

Excelで作ったファイルをGoogleスプレッドシートに変換すると、上の「使えない関数」の入ったセルはエラーになります。業務の台帳でこれらを使っていることはまれですが、取引先から受け取った集計表に入っていることがあります。そのときは、エラーの出たセルだけ値に置き換えるか、GASで作り直します。

一方で、Excelにある関数でGoogleスプレッドシートに無いものもあります。2026年10月時点の関数リストで探して見つからないときは、Excelの書き方を持ち込まず、FILTER・QUERY・SPLITなど手元にある関数で組み直します。Excelでしか使わない取引先とファイルをやりとりする会社は、Google WorkspaceとMicrosoft 365の違いの記事で、どちらを主に使うかの判断を書いています。

できないことと、その代わり

関数で無理をすると、ファイルが重くなり、直せる人がいなくなります。次の場面では関数をやめて別の手を使います。

やりたいこと	関数でやると	代わりの手
フォームの回答が来たらメールを返す	関数では送れない	GASのトリガー
毎月決まった日に請求書のPDFを作る	関数では作れない	GASで作って送る
何万行もある表を毎日集計する	重くなって開くのに時間がかかる	データポータルで集計し、表は分ける
何十ファイルからIMPORTRANGEで集める	Loading... が続く	GASで1日1回まとめて値を書き込む
kintoneやLINEのデータと合わせる	関数では取れない	n8nでスプレッドシートに書き込む
取り込んだ時点の値を残す	IMPORTRANGEは元が変わると変わる	値のみ貼り付けか、GASで値を書き込む

IMPORTRANGEは1回の読み込みあたり10MBまでです。ファイルを開いている間は1時間ごとに更新を確認し、ファイルを開き直しても読み込み直しはされません。受付が予約一覧を直してすぐ集計シートを見ても、反映されていないことがあります。数分のずれが困る業務（当日の予約の確認など）は、IMPORTRANGEを通さずに元のファイルを見ます。

IMPORT系の関数の呼び出しが多すぎると、セルに Loading... と出たままになります。この上限はファイルの作成者に対してかかり、共同編集者の編集も作成者の分として数えられます。IMPORTRANGEの数を減らし、範囲の指定を毎回変わらない固定の文字にしておくと出にくくなります。同じファイルから何か所も取り込んでいるなら、IMPORTRANGEは1つのシートに1回だけ置き、ほかのシートはそのシートを参照します。

IMPORTRANGEの中からNOW・RAND・RANDBETWEENを直接または間接に参照すると #ERROR! になります。TODAYは使えますが、1日に1回までしか更新されません。

よく詰まるところ

症状	原因	直し方
XLOOKUPやVLOOKUPが #N/A になる	会員IDが見つからない。前後の空白、全角と半角の違い、数値と文字の違いが多い	TRIM で空白を除く。会員IDの列の表示形式を「書式なしテキスト」にそろえる
VLOOKUPで別の人の名前が出る	4つ目の引数を省いて近似一致になっている	FALSE を付ける。XLOOKUPに置き換える
VLOOKUPが #REF! になる	「番号」が範囲の列数より大きい	範囲を広げるか、番号を直す
ARRAYFORMULAやFILTERが #REF! になる	結果が広がる先のセルに値や式が入っている	広がる先のセルを空ける。スペース1つでも止まる
日付が 46300 のような数字で出る	日付は内部では数値で、表示形式が数値になっている	「表示形式」→「数字」→「日付」を選ぶ
QUERYで日付の条件が効かない	列に文字の日付が混ざって日付の列として扱われていない。または日付の書き方が違う	列の値をすべて日付にする。date '2026-10-01' の形で書く
QUERYで一部の値が空になる	1つの列に数値と文字が混ざり、少ないほうの型が空として扱われる	列の型をそろえる。会員IDは全部文字にする
QUERYで見出しとデータがくっつく	見出し行の数を推測させている	3つ目の引数に 1 を書く
QUERYで月が1つずれる	month() が0始まり	month(A)+1 にするか、TEXTで月の列を作る
IMPORTRANGEが #REF! のまま	アクセスを許可していない。または許可した人が取り込み元から外れた	セルにカーソルを合わせて「アクセスを許可」。取り込み元の共有を確認
IMPORTRANGEが Loading... のまま	IMPORT系の呼び出しが多すぎる	IMPORTRANGEを1か所にまとめ、ほかはそこを参照する
IMPORTRANGEをQUERYで包むとエラー	列を A・B で書いている	Col1・Col2 で書く
COUNTIFSで月の件数が少ない	月末の日付で <= を書き、時刻つきの日付が漏れた	翌月1日の < で書く
式の結果が「未登録」だらけ	ARRAYFORMULAで空の行まで計算している	IF(B2:B="", "", ...) で空の行を除く
曜日や通貨の表示が英語になる	ファイルの言語と地域が日本以外	「ファイル」→「設定」→「全般」で「日本」にする

会員IDの「数値と文字の違い」は、本番の環境で何度も起きます。会員IDを 1001 のような数字だけにすると、手入力の行は数値、フォームやCSVから入った行は文字になり、見た目は同じなのにXLOOKUPが外れます。最初から M1001 のように英字を付けておくと、この問題は起きません。

次にやること

関数で台帳がつながったら、次は台帳そのものの形と、見る人への出し方を整えます。

• スプレッドシートで業務の台帳を作る：顧客・予約・在庫
• フォームの回答をスプレッドシートで管理する：確認・集計・対応状況
• データポータル（旧Looker Studio）の使い方：スプレッドシートの数字をグラフにして共有する
• Google WorkspaceとMicrosoft 365の違い：どちらを選ぶか、乗り換えるとき