関数ドリル（実践演習）
実データのシート上で、自分の手で数式を組み立てる関数ドリル121問。集計・論理/検索・テキスト/正規表現・日付・配列/スピル・QUERY・LAMBDA高階関数・総合まで、実務の組み合わせを難易度別に網羅します。
関数は「知っている」だけでは使えません。 この章は、実データの並んだシート上で、あなた自身が数式を組み立てる実践ドリルです。単純な知識ではなく、現場で本当に必要になる 関数の使い方と組み合わせ を、関数群ごと・難易度別（基礎→応用→発展）に 全121問 用意しました。
使う関数と解説リンク（早見表）
各問で使う関数の意味や構文を忘れたら、ここから該当する解説章へ飛べます。関数名をクリックすると、その関数を説明しているセクションが開きます。
| 分野 | 関数（クリックで解説へ） |
|---|---|
| 集計 | SUM ・ AVERAGE ・ COUNTA ・ COUNTBLANK ・ SUMIF ・ SUMIFS ・ COUNTIF ・ COUNTIFS ・ AVERAGEIF ・ MAXIFS ・ SUBTOTAL ・ LARGE ・ RANK ・ SUMPRODUCT ・ ROWS |
| 論理・検索 | IF ・ IFS ・ SWITCH ・ AND ・ OR ・ IFERROR ・ IFNA ・ VLOOKUP ・ INDEX ・ MATCH ・ XLOOKUP ・ XMATCH ・ CHOOSE ・ INDIRECT ・ LOOKUP |
| テキスト・正規表現 | LEFT ・ RIGHT ・ MID ・ LEN ・ FIND ・ TRIM ・ UPPER ・ SUBSTITUTE ・ REPLACE ・ VALUE ・ TEXT ・ TEXTJOIN ・ SPLIT ・ REGEXMATCH ・ REGEXEXTRACT ・ REGEXREPLACE |
| 日付・時刻 | DATE ・ MONTH ・ EDATE ・ EOMONTH ・ DATEDIF ・ WEEKDAY ・ WEEKNUM ・ WORKDAY ・ NETWORKDAYS ・ DATEVALUE |
| 配列・スピル | ARRAYFORMULA ・ FILTER ・ SORT ・ SORTN ・ UNIQUE ・ SEQUENCE ・ FLATTEN ・ TRANSPOSE ・ ARRAY_CONSTRAIN |
| QUERY | QUERY |
| LAMBDA・高階関数 | LAMBDA ・ MAP ・ REDUCE ・ SCAN ・ BYROW ・ BYCOL ・ MAKEARRAY ・ LET |
集計
合計・件数・平均・最大最小から、条件付き集計・順位・中央値・加重和まで、実データで数式を組み立てます。各問とも、模範解答を実際のスプレッドシートに入力した結果と答えが一致するよう設計しています。
1週間の売上です。合計金額を求めます。
B7 に、月〜金の売上（B2:B6）の合計を求める数式を入力してください。| A | B | |
|---|---|---|
| 1 | 曜日 | 売上 |
| 2 | 月 | 12000 |
| 3 | 火 | 8000 |
| 4 | 水 | 15000 |
| 5 | 木 | 9000 |
| 6 | 金 | 11000 |
| 7 | 合計 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B7 で解いてみましょう。
=SUM(B2:B6) SUM は連続範囲の数値をまとめて足します。12000+8000+15000+9000+11000=55000。手作業の足し算より範囲指定が確実です。
経費の明細です。カテゴリごとに金額を集計します。
E2 に、カテゴリが「交通費」の金額合計を求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | カテゴリ | 金額 | 交通費の合計 | ||
| 2 | 交通費 | 800 | |||
| 3 | 食費 | 1200 | |||
| 4 | 交通費 | 450 | |||
| 5 | 食費 | 900 | |||
| 6 | 交通費 | 600 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=SUMIF(A2:A,"交通費",B2:B) SUMIF は「条件範囲, 条件, 合計範囲」の順。交通費の行は800・450・600で合計 1850。条件範囲と合計範囲の位置に注意します。
アンケートの回答欄です。回答済みの人数を数えます。
D2 に、回答欄（B2:B6）のうち空でないセルの個数を求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 氏名 | 回答 | 回答済み件数 | ||
| 2 | 佐藤 | はい | |||
| 3 | 鈴木 | ||||
| 4 | 田中 | いいえ | |||
| 5 | 高橋 | はい | |||
| 6 | 伊藤 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=COUNTA(B2:B6) COUNTA は空でないセルの個数を数えます（文字でも数値でもOK）。回答があるのは佐藤・田中・高橋の 3 件。数値だけを数える COUNT とは区別します。
受注一覧です。特定ステータスの件数を数えます。
D2 に、ステータスが「完了」の件数を求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 注文ID | ステータス | 完了件数 | ||
| 2 | 1001 | 完了 | |||
| 3 | 1002 | 処理中 | |||
| 4 | 1003 | 完了 | |||
| 5 | 1004 | 完了 | |||
| 6 | 1005 | 処理中 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=COUNTIF(B2:B,"完了") COUNTIF は「範囲, 条件」で条件に合うセルを数えます。「完了」は1001・1003・1004の 3 件。合計ではなく件数を返します。
テストの点数です。平均点を求めます。
B7 に、5人の点数（B2:B6）の平均を求める数式を入力してください。| A | B | |
|---|---|---|
| 1 | 氏名 | 点数 |
| 2 | 佐藤 | 80 |
| 3 | 鈴木 | 70 |
| 4 | 田中 | 90 |
| 5 | 高橋 | 60 |
| 6 | 伊藤 | 100 |
| 7 | 平均 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B7 で解いてみましょう。
=AVERAGE(B2:B6) AVERAGE は数値の平均（合計÷個数）を返します。(80+70+90+60+100)/5=400/5=80。空セルは個数に数えない点が SUM/個数 と違います。
売上明細です。店舗と商品の2条件で数量を合計します。
F2 に「店舗が渋谷」かつ「商品がコーヒー」の数量合計を求める数式を入力してください。| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 数量 | 渋谷×コーヒー 合計 | ||
| 2 | 渋谷 | コーヒー | 3 | |||
| 3 | 新宿 | コーヒー | 5 | |||
| 4 | 渋谷 | 紅茶 | 2 | |||
| 5 | 渋谷 | コーヒー | 4 | |||
| 6 | 新宿 | 紅茶 | 1 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に F2 で解いてみましょう。
=SUMIFS(C2:C,A2:A,"渋谷",B2:B,"コーヒー") SUMIFS は「合計範囲, 条件範囲1, 条件1, …」の順で、SUMIF と引数の並びが逆です。該当は3と4の行で合計 7。
問い合わせ記録です。部署と緊急度の2条件で件数を数えます。
F2 に「部署が営業」かつ「緊急度が高」の件数を求める数式を入力してください。| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 部署 | 緊急度 | 内容 | 営業×高 件数 | ||
| 2 | 営業 | 高 | A | |||
| 3 | 開発 | 高 | B | |||
| 4 | 営業 | 低 | C | |||
| 5 | 営業 | 高 | D | |||
| 6 | 営業 | 高 | E |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に F2 で解いてみましょう。
=COUNTIFS(A2:A,"営業",B2:B,"高") COUNTIFS は「条件範囲1, 条件1, …」を繰り返して、すべて満たす行を数えます。営業かつ高は1・4・5行目の 3 件（3行目は緊急度が低なので除外）。
商品レビューです。カテゴリ別の平均評価を求めます。
E2 に、カテゴリが「家電」の評価の平均を求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 商品 | カテゴリ | 評価 | 家電の平均評価 | |
| 2 | 扇風機 | 家電 | 4 | ||
| 3 | 小説 | 書籍 | 5 | ||
| 4 | 掃除機 | 家電 | 2 | ||
| 5 | 雑誌 | 書籍 | 3 | ||
| 6 | 電球 | 家電 | 3 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=AVERAGEIF(B2:B,"家電",C2:C) AVERAGEIF は「条件範囲, 条件, 平均範囲」。家電は4・2・3でその平均は (4+2+3)/3=9/3=3。書籍の行は計算に含まれません。
営業成績です。地域ごとの最高売上を調べます。
E2 に、地域が「東日本」の中での売上の最大値を求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 担当 | 地域 | 売上 | 東日本の最高売上 | |
| 2 | A | 東日本 | 120 | ||
| 3 | B | 西日本 | 200 | ||
| 4 | C | 東日本 | 180 | ||
| 5 | D | 西日本 | 90 | ||
| 6 | E | 東日本 | 150 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=MAXIFS(C2:C,B2:B,"東日本") MAXIFS は「最大範囲, 条件範囲, 条件, …」で条件を満たす中の最大を返します。東日本は120・180・150で最大は 180。西日本の200は対象外です。
売上ランキングを作ります。上位3位の値を一気に出します。
D2 に、売上（B2:B7）の大きい順で1位・2位・3位の値を縦に並べる数式を入力してください（スピル）。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 売上 | 上位3位 | |
| 2 | A | 300 | ||
| 3 | B | 500 | ||
| 4 | C | 100 | ||
| 5 | D | 450 | ||
| 6 | E | 200 | ||
| 7 | F | 350 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=LARGE(B2:B7,{1;2;3}) LARGE(範囲, n) は n 番目に大きい値を返します。配列定数 {1;2;3} を渡すと1〜3位を縦にスピル展開でき、500・450・350 が並びます。逆順は SMALL です。
提出状況の表です。未提出（空白）の人数を数えます。
D2 に、提出欄（B2:B6）の空白セルの個数を求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 氏名 | 提出物 | 未提出件数 | ||
| 2 | 佐藤 | レポート | |||
| 3 | 鈴木 | ||||
| 4 | 田中 | レポート | |||
| 5 | 高橋 | ||||
| 6 | 伊藤 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=COUNTBLANK(B2:B6) COUNTBLANK は範囲内の空白セルの個数を返します。空欄は鈴木・高橋・伊藤の 3 件。COUNTA（空でない数）の裏返しの役割です。
フィルタで一部の行を非表示にした表です。表示中の行だけを合計したい場面です。ここでは4行目を手動で非表示にしたと想定します。
B7 に、フィルタや行の非表示に連動し「画面に見えている行」だけを合計する数式を入力してください（集計種別は合計）。| A | B | |
|---|---|---|
| 1 | 項目 | 金額 |
| 2 | A | 100 |
| 3 | B | 200 |
| 4 | C | 300 |
| 5 | D | 400 |
| 6 | E | 500 |
| 7 | 表示分合計 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B7 で解いてみましょう。
=SUBTOTAL(109,B2:B6) SUBTOTAL(集計種別, 範囲) はフィルタで隠れた行を自動で除外します。種別 9 は合計、109 は「手動で非表示にした行も除外」する合計です。全行表示なら 100+200+300+400+500=1500。行を隠すと合計が自動で減るのが通常の SUM との違いです。
科目ごとに点数と単位数（重み）があります。単位数で重み付けした合計点を求めます。
D2 に、各科目の「点数×単位数」をすべて足し合わせた値（加重合計）を1つの数式で求めてください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 科目 | 点数 | 単位数 | 加重合計 | |
| 2 | 国語 | 80 | 3 | ||
| 3 | 数学 | 90 | 4 | ||
| 4 | 英語 | 70 | 2 | ||
| 5 | 理科 | 60 | 1 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=SUMPRODUCT(B2:B5,C2:C5) SUMPRODUCT は対応する要素どうしを掛けてから合計します。80×3+90×4+70×2+60×1 = 240+360+140+60 = 800。掛けて足す処理を1セルで完結できます。
点数表に順位列を作ります。降順（高いほど1位）で各人の順位を出します。
C2 に、佐藤の点数（B2）が全員（B2:B6）の中で大きい順に何位かを求める数式を入力してください。下方向にコピーする前提で、範囲は絶対参照にします。| A | B | C | |
|---|---|---|---|
| 1 | 氏名 | 点数 | 順位 |
| 2 | 佐藤 | 80 | |
| 3 | 鈴木 | 95 | |
| 4 | 田中 | 70 | |
| 5 | 高橋 | 95 | |
| 6 | 伊藤 | 60 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=RANK(B2,$B$2:$B$6) RANK(値, 範囲, 順序) は順序を省略または0で降順（大きいほど上位）。点数は95・95・80・70・60なので、95が2人で1位タイ、佐藤の80は 3 位になります。コピーに備え範囲は $B$2:$B$6 と固定します。
論理・検索
条件分岐と検索の関数を、実務シナリオで手を動かしながら身につけます。IF 系の論理判定から、VLOOKUP・INDEX+MATCH・XLOOKUP までを段階的に扱います。
基礎 — 論理と単純検索
試験の点数表です。60点以上を合格とします。
B2 に、A2 の点数が60以上なら「合格」、そうでなければ「不合格」を返す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 点数 | 判定 |
| 2 | 72 | |
| 3 | 58 | |
| 4 | 60 | |
| 5 | 95 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=IF(A2>=60,"合格","不合格") IF は「条件, 真のとき, 偽のとき」の3つ。>=（以上）は等号を含むので、ちょうど60点は合格になります。境界値の扱いは要件をよく確認しましょう。
出席率と点数の両方を満たした人だけを合格にします。
C2 に、出席率が80以上「かつ」点数が60以上なら「合格」、それ以外は「不合格」を返す数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 出席率 | 点数 | 判定 |
| 2 | 90 | 70 | |
| 3 | 75 | 80 | |
| 4 | 85 | 55 | |
| 5 | 100 | 60 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=IF(AND(A2>=80,B2>=60),"合格","不合格") AND は全条件が真のときだけ TRUE を返します。1行目は90/70で両方クリア、4行目は100/60でちょうど合格。2行目は出席率不足、3行目は点数不足でいずれも不合格です。
VIP判定です。会員ランクがゴールド、または年間購入が100以上なら優待対象とします。
C2 に、ランクが「ゴールド」または購入額が100以上なら「優待」、それ以外は「通常」を返す数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | ランク | 購入額 | 区分 |
| 2 | シルバー | 120 | |
| 3 | ゴールド | 30 | |
| 4 | シルバー | 80 | |
| 5 | ブロンズ | 200 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=IF(OR(A2="ゴールド",B2>=100),"優待","通常") OR はどれか1つでも真なら TRUE。1行目は購入額、2行目はランク、4行目は購入額で条件を満たします。3行目はシルバーかつ80なので両方とも外れ、通常です。
商品マスタから、入力したコードの単価を引きます。
E2 に、D2 のコードに対応する単価を、完全一致で返す数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | コード | 単価 | 検索コード | 単価 | |
| 2 | P01 | 120 | P03 | ||
| 3 | P02 | 80 | |||
| 4 | P03 | 200 | |||
| 5 | P04 | 150 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=VLOOKUP(D2,A2:B,2,FALSE) VLOOKUP の第4引数は必ず FALSE（完全一致）を指定するのが実務の鉄則です。省略や TRUE は近似一致になり、誤った値を返す事故のもとになります。P03 の単価 200 が返ります。
在庫表から数量を引きますが、登録されていない商品コードが混ざることがあります。
E2 に、D2 の商品の在庫数を返し、見つからなければ「在庫なし」と表示する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 商品 | 在庫 | 検索 | 結果 | |
| 2 | ペン | 40 | ノート | ||
| 3 | 消しゴム | 25 | |||
| 4 | 定規 | 10 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=IFERROR(VLOOKUP(D2,A2:B,2,FALSE),"在庫なし") IFERROR は「数式, エラー時の値」の形で、エラーをまるごと拾って代替値に置き換えます。「ノート」はマスタに無いため #N/A になりますが、それを 在庫なし に変換しています。検索専用なら IFNA の方が他のエラーを隠さず安全です。
応用 — 検索の組み合わせと多分岐
点数を A / B / C の3段階で評価します。90以上がA、70以上がB、それ未満がCです。
B2 に、点数に応じて「A」「B」「C」を返す数式を、IFS で入力してください。| A | B | |
|---|---|---|
| 1 | 点数 | 評価 |
| 2 | 95 | |
| 3 | 72 | |
| 4 | 88 | |
| 5 | 60 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=IFS(A2>=90,"A",A2>=70,"B",TRUE,"C") IFS は条件を上から順に評価し、最初に真になった結果を返します。範囲分岐では大きい順（または小さい順）に並べるのが鉄則。最後の TRUE,"C" は「どれにも当てはまらない場合」を受ける番人です。88点は90未満・70以上なので B。
曜日コード（1〜7）を曜日名に変換します。
B2 に、A2 の番号を 1→月, 2→火, 3→水 に変換し、それ以外は「？」を返す数式を、SWITCH で入力してください。| A | B | |
|---|---|---|
| 1 | 番号 | 曜日 |
| 2 | 2 | |
| 3 | 1 | |
| 4 | 3 | |
| 5 | 5 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=SWITCH(A2,1,"月",2,"火",3,"水","？") SWITCH は「対象, 値1, 結果1, 値2, 結果2, …, 既定値」の形で、特定の値との一致を簡潔に書けます。等号の比較が並ぶときは IFS より読みやすくなります。5は定義外なので末尾の既定値 ？ が返ります。
商品名が左、コードが右に並んでいます。VLOOKUP は右にしか進めません。
E2 に、D2 のコードに対応する商品名（左の列）を返す数式を、INDEX と MATCH で入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 商品名 | コード | 検索コード | 商品名 | |
| 2 | りんご | A001 | A003 | ||
| 3 | みかん | A002 | |||
| 4 | ぶどう | A003 | |||
| 5 | もも | A004 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=INDEX(A2:A,MATCH(D2,B2:B,0)) MATCH が「A003 は何行目か」（3行目）を返し、INDEX がその位置の商品名を取り出します。MATCH の第3引数 0 が完全一致。左方向の検索でも列の挿入に強く、XLOOKUP(D2,B2:B,A2:A) でも同じ結果になります。
社員マスタから部署を引きます。退職者IDなどマスタに無いIDが来ても止めません。
E2 に、D2 のIDの部署を返し、見つからなければ「不明」と表示する数式を、XLOOKUP で入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | 部署 | 検索ID | 部署 | |
| 2 | E01 | 営業 | E09 | ||
| 3 | E02 | 開発 | |||
| 4 | E03 | 総務 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=XLOOKUP(D2,A2:A,B2:B,"不明") XLOOKUP の第4引数 if_not_found に既定値を直接渡せるのが強みです。IFERROR で包むより、見つからない場合だけを正確に扱えます。E09 はマスタに無いので 不明。
購入金額に応じた会員ランクの区分表です。区分表は昇順に並んでいます。
E2 に、D2 の金額が属するランクを、近似一致の VLOOKUP で返してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 下限 | ランク | 金額 | ランク | |
| 2 | 0 | レギュラー | 250 | ||
| 3 | 100 | シルバー | |||
| 4 | 300 | ゴールド | |||
| 5 | 500 | プラチナ |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=VLOOKUP(D2,A2:B,2,TRUE) 近似一致（TRUE）は「検索値以下で最大の値」を探します。範囲分けの区分表はこの用途にぴったりですが、検索列が昇順に並んでいることが大前提です。250は100以上300未満なので シルバー。
評価番号（1〜3）を文章のコメントに変換します。
B2 に、A2 の番号で 1→要改善, 2→標準, 3→優秀 を返す数式を、CHOOSE で入力してください。| A | B | |
|---|---|---|
| 1 | 評価 | コメント |
| 2 | 3 | |
| 3 | 1 | |
| 4 | 2 | |
| 5 | 3 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=CHOOSE(A2,"要改善","標準","優秀") CHOOSE は「位置, 値1, 値2, …」の形で、1から始まる連番のインデックスで値を選びます。連続した整数の対応づけならとても簡潔です。番号3は3番目の 優秀。
発展 — 2次元検索と複合判定
店舗×四半期の売上クロス表です。指定した店舗・四半期の交点の値を取り出します。
F2 に、F1 の店舗かつ G1 の四半期に対応する売上を、INDEX と MATCH の組み合わせで返してください。| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 店舗 | Q1 | Q2 | Q3 | 新宿 | Q3 | |
| 2 | 渋谷 | 100 | 120 | 140 | |||
| 3 | 新宿 | 200 | 180 | 210 | |||
| 4 | 池袋 | 90 | 110 | 130 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に F2 で解いてみましょう。
=INDEX(B2:D4,MATCH(F1,A2:A4,0),MATCH(G1,B1:D1,0)) 2次元検索は INDEX に行番号と列番号の両方を渡すのが定石です。1つ目の MATCH が行（新宿=2行目）、2つ目の MATCH が列（Q3=3列目）を求め、INDEX がその交点 210 を取り出します。表に行や列を挿入してもズレにくいのが利点です。
商品ごとの在庫数と、必要数を比べます。在庫が必要数を下回るものを発注対象にします。
F2 に、E2 の商品の在庫を引き、それが E3 の必要数を下回るなら「発注」、足りていれば「OK」を返す数式を入力してください。| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 商品 | 在庫 | 検索商品 | 判定 | ||
| 2 | A | 30 | B | |||
| 3 | B | 15 | 50 | |||
| 4 | C | 80 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に F2 で解いてみましょう。
=IF(XLOOKUP(E2,A2:A,B2:B)<E3,"発注","OK") 検索した値をそのまま IF の条件に組み込む実務頻出パターンです。商品Bの在庫15を引き、必要数50と比較。15 < 50 なので 発注。XLOOKUP の代わりに VLOOKUP でも同様に書けます。
月ごとのシートではなく、1枚に縦並びのデータがあります。集計したい範囲を文字列で指定して合計します。
E1 に、D1 に入力された範囲文字列（例 B2:B5）が指す範囲の合計を、INDIRECT で求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 月 | 売上 | B2:B5 | ||
| 2 | 1月 | 100 | |||
| 3 | 2月 | 150 | |||
| 4 | 3月 | 200 | |||
| 5 | 4月 | 50 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E1 で解いてみましょう。
=SUM(INDIRECT(D1)) INDIRECT は「B2:B5」という文字列を実際のセル参照に変換します。範囲を後から変数のように差し替えられる強力な関数ですが、参照が文字列依存になるため再計算が重く、追跡しづらい点には注意。100+150+200+50 = 500。
完成です。論理判定（IF/AND/OR/IFS/SWITCH/CHOOSE）と検索（VLOOKUP/INDEX+MATCH/XLOOKUP/INDIRECT）を、左方向の逆引き・近似一致・2次元検索・複合判定まで段階的に練習しました。検索は「完全一致は FALSE/0 を明示」「区分は昇順＋近似一致」「逆引き・2次元は INDEX+MATCH か XLOOKUP」を押さえれば、実務のほとんどに対応できます。
テキスト・正規表現
文字列の抽出・整形・判定を、住所・氏名・コード・メールなどの実データで鍛えます。正規表現の記号があやしいときは、正規表現 完全ガイド（RE2） で使える記号と「RE2に無い機能」を確認できます。
商品コードの先頭3文字は「カテゴリ記号」です。ここだけ取り出します。
B2 に、A2 の文字列の左から3文字を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 商品コード | カテゴリ記号 |
| 2 | ABC-00123 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=LEFT(A2,3) LEFT(文字列, 文字数) は左端から指定文字数を取り出します。ABC-00123 の左3文字は ABC。文字数を省略すると1文字になります。
ファイル名の末尾「.pdf」を取り出して拡張子を確認します。
B2 に、A2 の右から4文字を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | ファイル名 | 拡張子 |
| 2 | report_2026.pdf |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=RIGHT(A2,4) RIGHT(文字列, 文字数) は右端から取り出します。report_2026.pdf の右4文字は .pdf（ドットを含む4文字）。
入力されたパスワードが規定の文字数かをチェックするため、長さを数えます。
B2 に、A2 の文字数を返す数式を入力してください。| A | B | |
|---|---|---|
| 1 | パスワード | 文字数 |
| 2 | Pass1234! |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=LEN(A2) LEN(文字列) は文字数を返します。Pass1234! は半角9文字。日本語は全角でも1文字として数えます（バイト数を数える LENB とは別）。
顧客が入力した商品コードの大小がバラバラなので、すべて大文字に統一します。
B2 に、A2 をすべて大文字に変換する数式を入力してください。| A | B | |
|---|---|---|
| 1 | 入力値 | 統一後 |
| 2 | ab-12cd |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=UPPER(A2) UPPER は英字を大文字に、LOWER は小文字に変換します。数字や記号はそのまま。各単語の頭だけ大文字にするには PROPER を使います。
コピペで前後や途中に余分なスペースが入った氏名を整えます。
B2 に、A2 の余分な空白を除去する数式を入力してください。| A | B | |
|---|---|---|
| 1 | 入力された氏名 | 整形後 |
| 2 | 山田 太郎 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=TRIM(A2) TRIM は前後の空白を全て、語間の連続空白を1つに詰めます。 山田 太郎 → 山田 太郎。表示上は中央のスペース1つだけが残ります。
「姓-名」形式のコードから、ハイフンの後ろの「名」だけを取り出します。
B2 に、A2 の - より後ろの文字列を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | コード | 名 |
| 2 | yamada-taro |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=MID(A2,FIND("-",A2)+1,LEN(A2)) FIND("-",A2) でハイフンの位置（7）を求め、その +1 文字目から取り出します。MID の文字数は実際の残りより大きくても末尾までで止まるので LEN(A2) で安全に取れます。
電話番号のハイフンを取り除いて、数字だけの形にします。
B2 に、A2 から - をすべて削除する数式を入力してください。| A | B | |
|---|---|---|
| 1 | 電話番号 | ハイフン無し |
| 2 | 090-1234-5678 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=SUBSTITUTE(A2,"-","") SUBSTITUTE(文字列, 検索, 置換) は該当文字を全て置き換えます。置換文字を ""（空）にすると削除になります。REGEXREPLACE でも同じことができます。
日付文字列の2つ目の区切りだけを変えたい、というように出現回数を指定して置換します。
B2 に、A2 の中で2番目の / だけを - に置き換える数式を入力してください。| A | B | |
|---|---|---|
| 1 | 元データ | 置換後 |
| 2 | 2026/06/03 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=SUBSTITUTE(A2,"/","-",2) SUBSTITUTE の第4引数は「何番目の出現を置換するか」。2 を指定すると2つ目の / だけが - になり、2026/06-03 になります。省略すると全置換です。
会員IDの先頭2桁を、固定の支店コードに丸ごと差し替えます。
B2 に、A2 の1文字目から2文字分を TK に置き換える数式を入力してください。| A | B | |
|---|---|---|
| 1 | 旧ID | 新ID |
| 2 | 00-998877 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REPLACE(A2,1,2,"TK") REPLACE(文字列, 開始位置, 文字数, 新文字列) は「位置で指定して」差し替えます。文字を探して置換する SUBSTITUTE との違いに注意。1文字目から2文字（00）が TK になります。
都道府県・市区町村・番地を、空欄は飛ばしつつ1つの住所文字列にまとめます。
D2 に、A2:C2 を 空欄を無視して スペース区切りで連結する数式を入力してください。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 都道府県 | 市区町村 | 番地 | 住所 |
| 2 | 東京都 | 渋谷1-2-3 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=TEXTJOIN(" ",TRUE,A2:C2) TEXTJOIN(区切り, 空欄無視, 範囲...)。第2引数 TRUE で空セルを飛ばすため、市区町村が空でも区切りが重複しません。=A2&" "&B2&" "&C2 だと空欄分のスペースが残ってしまいます。
達成率 0.1234 を「12.3%」という見せ方の文字列にして、レポートに埋め込みます。
B2 に、A2 を小数第1位までのパーセント表記の文字列にする数式を入力してください。| A | B | |
|---|---|---|
| 1 | 達成率 | 表示用 |
| 2 | 0.1234 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=TEXT(A2,"0.0%") TEXT(値, 書式) は数値を指定書式の文字列に変換します。0.0% は「100倍して小数第1位まで＋%」。0.1234 → 12.3%（四捨五入）。結果は数値ではなく文字列になります。
入力された文字列が、ざっくりメールアドレスの形（@とドメインのドット）になっているか判定します。
B2 に、A2 がメール形式なら TRUE を返す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 入力値 | メール形式か |
| 2 | [email protected] |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXMATCH(A2,"^[^@\s]+@[^@\s]+\.[^@\s]+$") REGEXMATCH はパターンに一致すれば TRUE を返します（RE2 構文）。[^@\s]+ は「@と空白以外が1文字以上」。@ を挟みドメイン部にドットがある形を満たすので TRUE。
住所文字列の先頭に紛れた郵便番号（3桁-4桁）だけを正確に取り出します。
B2 に、A2 から 123-4567 形式の郵便番号を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 住所 | 郵便番号 |
| 2 | 〒150-0001 東京都渋谷区 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"\d{3}-\d{4}") \\d{3}-\\d{4} は「数字3桁・ハイフン・数字4桁」。REGEXEXTRACT は最初に一致した部分だけを返すので、〒記号や後続の住所は無視され 150-0001 が取れます。
メールアドレスの @ より後ろのドメイン部分だけを取り出して集計に使います。
B2 に、A2 のメールアドレスからドメイン（@の後ろ）を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | メール | ドメイン |
| 2 | [email protected] |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"@(.+)$") パターンに丸括弧（キャプチャグループ）があると、REGEXEXTRACT は一致全体ではなくグループ部分を返します。@(.+)$ は「@の後ろから末尾まで」をグループ化するため、@ を含まずドメインだけが取れます。
金額の数値に3桁ごとのカンマを付け、頭に円記号を付けた表示用文字列を作ります。
B2 に、A2 を ¥1,234,567 の形の文字列にする数式を入力してください。| A | B | |
|---|---|---|
| 1 | 金額 | 表示用 |
| 2 | 1234567 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=TEXT(A2,"¥#,##0") 桁区切りは TEXT の書式 #,##0 が定番です（REGEXREPLACE の後読みは RE2 が非対応なので不向き）。書式内に ¥ を入れれば通貨記号も付き、1234567 → ¥1,234,567。
「姓 名」をスペースで分割し、2番目の「名」だけを1セルに取り出します（SPLIT単体は横に展開してしまうため INDEX で1つ選びます）。
B2 に、A2 をスペースで分割し2番目の語を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 氏名 | 名 |
| 2 | 田中 花子 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=INDEX(SPLIT(A2," "),1,2) SPLIT(A2," ") は 田中 花子 を横（2列）に展開します。そのままだと縦1セルに収まらないので、INDEX(..., 1, 2) で1行2列目だけを取り出して 花子 を得ます。
正規表現の実戦ドリル（RE2）
正規表現 完全ガイド（RE2） の内容を、実データで手を動かして固めます。Google スプレッドシートで実際に動く RE2 構文だけを扱います（先読み・後読み・後方参照は使いません）。
入力値が「数字ちょうど7桁」になっているかを厳密に判定します。
B2 に、A2 が数字ちょうど7桁のときだけ TRUE を返す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 値 | 判定 |
| 2 | 1234567 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXMATCH(A2,"^\d{7}$") ^ が先頭、$ が末尾。挟むことで「全体が一致」を要求します。^/$ を付けないと部分一致になり、長い文字列の中に7桁数字があるだけで TRUE になってしまいます。
電話番号からハイフンなどを除いて数字だけにします。
B2 に、A2 から数字以外をすべて削除する数式を入力してください。| A | B | |
|---|---|---|
| 1 | 電話 | 数字のみ |
| 2 | 03-1234-5678 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXREPLACE(A2,"[^0-9]","") [^0-9] は「数字以外の1文字」。REGEXREPLACE は 全置換 なので、合う文字をすべて空文字に置き換えれば数字だけが残ります。\D（数字以外）でも同じです。
複数の数字の並びがある文字列から、最初のかたまりを取り出します。
B2 に、A2 から最初に現れる連続した数字を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 元データ | 抽出 |
| 2 | A12 B34 C56 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"\d+") REGEXEXTRACT は 最初に一致した部分だけ を返します。\d+（数字1個以上）は「12」「34」「56」のどれにも当たりますが、返るのは最初の 12 です。
決まった前置きのあとに続く番号だけが欲しい場面です。
B2 に、A2 の「注文: 」に続く数字だけを取り出す数式を、グループ () を使って入力してください。| A | B | |
|---|---|---|
| 1 | 元データ | 注文番号 |
| 2 | 注文: 12345 (確定) |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"注文: (\d+)") パターンに () があると、REGEXEXTRACT は そのグループの中身 を返します。注文: は位置合わせのための目印で、結果には含まれません。返るのは捕捉した 12345 だけです。
MM/DD/YYYY 形式の日付文字列を、ISO の YYYY-MM-DD に並べ替えます。
B2 に、A2（月/日/年）を 年-月-日 の順に並べ替える数式を、3つのグループと $1 記法で入力してください。| A | B | |
|---|---|---|
| 1 | 元(MM/DD/YYYY) | ISO(YYYY-MM-DD) |
| 2 | 01/15/2026 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXREPLACE(A2,"(\d{2})/(\d{2})/(\d{4})","$3-$1-$2") 置換文字列の $1 $2 $3 は、1番目・2番目・3番目のグループに対応します。Google スプレッドシートでは ドル記号 $（RE2 のバックスラッシュではなく）を使うのがポイント。$3-$1-$2 で「年-月-日」に組み替わります。
ログ文字列に「error」が（大文字小文字を問わず）含まれるか判定します。
B2 に、A2 に「error」が大文字小文字を区別せず含まれていれば TRUE を返す数式を入力してください。| A | B | |
|---|---|---|
| 1 | ログ | errorを含む? |
| 2 | ERROR: disk full |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXMATCH(A2,"(?i)error") 既定では大文字小文字を区別します。パターンの 先頭 に (?i) を置くと、その正規表現全体が大小を無視します。ERROR でも Error でも一致します。
タグが連続する文字列から、最初のタグだけを取り出します。
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"<.*?>") .* は貪欲で、できるだけ長く一致しようとして <a><b> 全体を取ってしまいます。末尾に ? を付けた .*? は 控えめ になり、最初の > で止まるので <a> だけが返ります。
日本語まじりの文字列から、ひらがなの連なりを取り出します。
B2 に、A2 から最初に現れるひらがなの連続を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 元データ | ひらがな |
| 2 | りんご(Apple)123 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"[ぁ-ん]+") \w は ASCII 専用で日本語に一致しません。ひらがなは 文字コード範囲 [ぁ-ん] で表します（\p{...} のような Unicode プロパティは Google スプレッドシートでは使えませんが、この範囲指定は動きます）。
部門名などからカタカナ部分を抜き出します。
B2 に、A2 から最初に現れるカタカナの連続を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 元データ | カタカナ |
| 2 | 部門：カメラ事業部 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"[ァ-ン]+") カタカナは [ァ-ン]。長音符「ー」も拾いたいときは [ァ-ンー] のように範囲へ足します。「事業部」は漢字なので一致せず、最初のカタカナの連なり カメラ が返ります。
会社名から法人格（株式会社／有限会社）を抜き出します。
B2 に、A2 から「株式会社」または「有限会社」を取り出す数式を、選択 | を使って入力してください。| A | B | |
|---|---|---|
| 1 | 会社名 | 法人格 |
| 2 | 有限会社ナカヤ |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"株式会社|有限会社") A|B は「A または B」。株式会社|有限会社 でどちらかに一致します。先頭にある 有限会社 が取り出されます。
入力時に空いてしまった連続スペースを1つに整えます。
B2 に、A2 の連続した半角空白を半角1つにまとめる数式を入力してください。| A | B | |
|---|---|---|
| 1 | 元データ | 整形後 |
| 2 | a b c |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXREPLACE(A2,"\s+"," ") \s+ は「1個以上の空白」。これを半角スペース1つに置換します。注意：RE2 の \s は ASCII の空白のみ で、全角スペース「 」には一致しません。全角空白は先に SUBSTITUTE(A2," "," ") で半角に直してから処理します。
メールアドレスのユーザー名部分（@ の前）だけが欲しい場面です。
B2 に、A2 の先頭から @ の直前までを取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | メール | ユーザー名 |
| 2 | [email protected] |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"^[^@]+") [^@] は「@ 以外の1文字」。先頭 ^ から「@ 以外」が続く限り取れば、ユーザー名 info が得られます。
短い番号は無視して、3桁以上の数字の並びだけを取り出します。
B2 に、A2 から最初に現れる「3桁以上」の連続した数字を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 元データ | 抽出 |
| 2 | x12 y3456 z78 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=REGEXEXTRACT(A2,"\d{3,}") {3,} は「3回以上」。12 は2桁なので無視され、最初に条件を満たす 3456 が返ります。{n} ちょうど、{n,m} 範囲、と合わせて覚えましょう。
日付・時刻
締め日・支払期日・年齢・勤続年数・納期・営業日を、実行日に左右されないよう基準日を明示して計算します。結果は原則 TEXT で文字列化し、表示が崩れないようにします。
フォームから受け取った年・月・日が別々のセルに入っています。1つの日付にまとめます。
D2 に、A2（年）・B2（月）・C2（日）から日付を作り、yyyy/mm/dd 形式の文字列で表示する数式を入力してください。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 年 | 月 | 日 | 日付 |
| 2 | 2026 | 3 | 15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=TEXT(DATE(A2,B2,C2),"yyyy/mm/dd") DATE(年,月,日) はバラバラの数値を日付シリアル値に変換します。表示形式に依存しないよう、最後に TEXT で yyyy/mm/dd に固定しています。落とし穴：DATE の引数は数値であること。文字列のままだと別の関数が必要になります。
売上日から集計用の「月」を抜き出します。
B2 に、A2 の日付の月（数値）を取り出す数式を入力してください。| A | B | |
|---|---|---|
| 1 | 売上日 | 月 |
| 2 | 2026-03-15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=MONTH(A2) MONTH(日付) は月を 1〜12 の整数で返します。同様に YEAR は年、DAY は日を返します。A2 が日付として認識されていれば、文字列でも自動変換されることが多いですが、確実にするなら DATEVALUE で数値化します。
請求の締めは月末です。基準日が属する当月の月末を求めます。
B2 に、A2 の日付が属する月の月末日を yyyy/mm/dd 形式の文字列で表示する数式を入力してください。| A | B | |
|---|---|---|
| 1 | 基準日 | 月末日 |
| 2 | 2026-02-10 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=TEXT(EOMONTH(A2,0),"yyyy/mm/dd") EOMONTH(日付,0) は当月末を返します。2026年は閏年ではないため、2月末は 28日（29日ではない）です。月によって日数が違うので、月末は手で書かず EOMONTH に任せます。
契約は3ヶ月ごとの更新です。基準日の3ヶ月後の応当日を求めます。
B2 に、A2 の日付の3ヶ月後の同じ日を yyyy/mm/dd 形式の文字列で表示する数式を入力してください。| A | B | |
|---|---|---|
| 1 | 基準日 | 3ヶ月後 |
| 2 | 2026-03-15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=TEXT(EDATE(A2,3),"yyyy/mm/dd") EDATE(日付,N) は N ヶ月後の応当日を返します。2026/03/15 の3ヶ月後は 2026/06/15。落とし穴：起点が月末（例 1/31）の場合、応当日が無い月では自動的に月末に丸められます（1/31 の1ヶ月後は閏年でない2026年は 2/28）。
シフト表に曜日を「日」「月」のように表示したいです。
B2 に、A2 の日付の曜日を日本語1文字（例：日）で表示する数式を入力してください。| A | B | |
|---|---|---|
| 1 | 日付 | 曜日 |
| 2 | 2026-03-15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=TEXT(A2,"aaa") TEXT(日付,“aaa”) は曜日を日本語1文字（日・月・火…）で返します。"aaaa" なら「日曜日」、英語表記なら "ddd"（Sun）や "dddd"（Sunday）です。2026年3月15日は実際に日曜日です。
会員名簿で、基準日時点の満年齢を自動計算します。
C2 に、生年月日 A2 から基準日 B2 時点の満年齢（数値）を求める数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 生年月日 | 基準日 | 満年齢 |
| 2 | 1990-07-20 | 2026-03-15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=DATEDIF(A2,B2,"Y") DATEDIF(開始,終了,“Y”) は満年数を返します。1990/7/20 生まれは 2026/3/15 時点ではまだ誕生日前なので 35歳（36ではない）。落とし穴：引数は必ず「開始 < 終了」の順。逆だとエラーになります。
人事システムで勤続期間を「○年○ヶ月」と表示します。
C2 に、入社日 A2 から基準日 B2 までの勤続を 7年11ヶ月 のような文字列で表示する数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 入社日 | 基準日 | 勤続 |
| 2 | 2018-04-01 | 2026-03-15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=DATEDIF(A2,B2,"Y")&"年"&DATEDIF(A2,B2,"YM")&"ヶ月" “Y” は満年数、“YM” は「年を無視した残りの月数」（0〜11）を返します。2018/4/1〜2026/3/15 は 7年11ヶ月。落とし穴：合計月数が欲しいときは “M”（=95ヶ月）を使い、“YM” と混同しないこと。
発注日から検収日までの経過日数を求めます。
C2 に、開始日 A2 から終了日 B2 までの経過日数（数値）を求める数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 開始日 | 終了日 | 日数 |
| 2 | 2026-03-01 | 2026-03-15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=DATEDIF(A2,B2,"D") DATEDIF(開始,終了,“D”) は両日の差の日数を返します。3/1 から 3/15 は 14日（終了日の当日は含まない引き算）。単純に =B2-A2 でも同じ整数になりますが、表示形式が日付に化けたら「数値」に直してください。
土日を判定するため、日付から曜日番号を取り出します。
B2 に、A2 の日付の曜日番号を求める数式を入力してください（type は既定。日曜=1〜土曜=7）。| A | B | |
|---|---|---|
| 1 | 日付 | 曜日番号 |
| 2 | 2026-03-15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=WEEKDAY(A2) 既定の WEEKDAY(日付)（type=1）は日曜=1, 月曜=2 … 土曜=7。2026/3/15 は日曜なので 1。落とし穴：「月曜=1」にしたい場合は type に 2 を指定します（type を変えると土日判定の条件式も変わる点に注意）。
受注日から土日を除いて5営業日後を納期とします（祝日は考慮しない）。
B2 に、受注日 A2 の5営業日後を yyyy/mm/dd 形式の文字列で表示する数式を入力してください。| A | B | |
|---|---|---|
| 1 | 受注日 | 納期 |
| 2 | 2026-03-13 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=TEXT(WORKDAY(A2,5),"yyyy/mm/dd") WORKDAY(開始,N) は土日を除いた N 日後を返します。起点 3/13（金）の5営業日後は、土日2回をまたいで 3/20（金）。落とし穴：起点日は数えません。祝日を除きたいときは第3引数に祝日リストの範囲を渡します。
プロジェクト期間の実働日数（土日を除く）を算出します。
C2 に、開始日 A2 から終了日 B2 までの営業日数（両端を含む、土日を除く）を求める数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 開始日 | 終了日 | 営業日数 |
| 2 | 2026-03-02 | 2026-03-13 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=NETWORKDAYS(A2,B2) NETWORKDAYS(開始,終了) は土日を除いた営業日数を両端を含めて数えます。3/2（月）〜3/13（金）はちょうど2週間ぶんの平日 10日。落とし穴：WORKDAY が「日付」を返すのに対し、こちらは「日数」を返します。祝日は第3引数で除外できます。
他システムからの取込みで日付が文字列 2026/03/15 のまま入っており、引き算ができません。
C2 に、文字列の日付 A2 と B2 の差の日数（数値）を、文字列を日付に変換したうえで求める数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 開始(文字列) | 終了(文字列) | 日数 |
| 2 | 2026/03/01 | 2026/03/15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=DATEVALUE(B2)-DATEVALUE(A2) DATEVALUE(文字列) は日付らしい文字列をシリアル値（数値）に変換します。数値化して初めて引き算や DATEDIF が正しく働き、3/1〜3/15 は 14。落とし穴：左寄せ表示の日付は「文字列」の疑いあり。計算が合わないときはまず DATEVALUE で数値化を確認します。
週次レポートで、日付がその年の第何週に当たるかを自動採番します。
B2 に、A2 の日付がその年の第何週か（既定の週番号。1月1日を含む週を第1週、週は日曜始まり）を求める数式を入力してください。| A | B | |
|---|---|---|
| 1 | 日付 | 週番号 |
| 2 | 2026-03-15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に B2 で解いてみましょう。
=WEEKNUM(A2) 既定の WEEKNUM(日付) は、1月1日を含む週を第1週とし、週は日曜始まりで数えます。2026年は1/1が木曜で、3/15（日）は 第12週。落とし穴：月曜始まりや ISO 週番号（ISOWEEKNUM）とは番号がずれます。集計の定義に合わせて type を選んでください。
配列・スピル
1つの数式で範囲全体を処理し、結果が複数セルへ自動展開される「スピル」を身につけます。各問とも、模範解答を実際のスプレッドシートに入力した結果と答えが一致するよう設計しています。
商品ごとの単価と数量から、金額（単価×数量）の列を1つの数式でまとめて作ります。
C2 に、各行の単価×数量を一気に計算して縦に並べる数式を入力してください（C3以降は自動でスピルします）。| A | B | C | |
|---|---|---|---|
| 1 | 単価 | 数量 | 金額 |
| 2 | 100 | 3 | |
| 3 | 200 | 2 | |
| 4 | 150 | 4 | |
| 5 | 300 | 1 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=ARRAYFORMULA(A2:A5*B2:B5) ARRAYFORMULA は範囲どうしの計算を1セルで行い、結果を各行へスピルします。100×3=300、200×2=400、150×4=600、300×1=300。1行ずつ数式をコピーする必要がなくなります。
受注リストの商品名から、重複しない商品の一覧を作ります。
C2 に、A列の商品名（A2:A6）から重複を除いた一覧を出す数式を入力してください（複数行にスピルします）。| A | B | C | |
|---|---|---|---|
| 1 | 商品名 | 一覧 | |
| 2 | みかん | ||
| 3 | りんご | ||
| 4 | みかん | ||
| 5 | ばなな | ||
| 6 | りんご |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=UNIQUE(A2:A6) UNIQUE は重複を取り除き、最初に登場した順番のまま残します。みかん・りんご・ばななの3件。並べ替えはしないので、登場順がそのまま出ます。
名簿に通し番号を振ります。5人分のNo.を一気に生成します。
A2 に、1から5までの連番を縦に並べる数式を入力してください（スピル）。| A | B | |
|---|---|---|
| 1 | No. | 氏名 |
| 2 | 佐藤 | |
| 3 | 鈴木 | |
| 4 | 田中 | |
| 5 | 高橋 | |
| 6 | 伊藤 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に A2 で解いてみましょう。
=SEQUENCE(5) SEQUENCE(n) は1からnまでの連番を縦1列に生成します。SEQUENCE(行数, 列数, 開始値, 増分) の形で、行数だけ指定すれば縦方向に展開されます。1〜5が並びます。
1行に横並びで入力された曜日を、縦1列に並べ替えます。
A3 に、1行目の曜日（A1:E1）を縦方向に並べ替える数式を入力してください（スピル）。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 月 | 火 | 水 | 木 | 金 |
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に A3 で解いてみましょう。
=TRANSPOSE(A1:E1) TRANSPOSE は行と列を入れ替えます。横1行（A1:E1）を渡すと縦1列にスピルし、月・火・水・木・金が縦に並びます。縦↔横の向き変換に使います。
受注一覧から、ステータスが「完了」の注文IDだけを抜き出します。
D2 に、ステータス（B列）が「完了」の行の注文ID（A列）だけを縦に抽出する数式を入力してください（スピル）。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 注文ID | ステータス | 完了の注文ID | |
| 2 | 1001 | 完了 | ||
| 3 | 1002 | 処理中 | ||
| 4 | 1003 | 完了 | ||
| 5 | 1004 | 完了 | ||
| 6 | 1005 | 処理中 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=FILTER(A2:A6,B2:B6="完了") FILTER(範囲, 条件) は条件が TRUE の行だけを残します。ステータスが「完了」なのは1001・1003・1004の3件。条件には範囲どうしの比較を書きます。
売上明細から、店舗が「渋谷」かつ商品が「コーヒー」の数量だけを抜き出します。
E2 に「店舗が渋谷」かつ「商品がコーヒー」の行の数量（C列）を抽出する数式を入力してください（スピル）。条件は * でつなぎます。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 数量 | 渋谷×コーヒーの数量 | |
| 2 | 渋谷 | コーヒー | 3 | ||
| 3 | 新宿 | コーヒー | 5 | ||
| 4 | 渋谷 | 紅茶 | 2 | ||
| 5 | 渋谷 | コーヒー | 4 | ||
| 6 | 新宿 | 紅茶 | 1 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=FILTER(C2:C6,(A2:A6="渋谷")*(B2:B6="コーヒー")) FILTER で複数条件を AND するには各条件を * で掛けます（TRUE=1 同士の積が1のときだけ残る）。渋谷かつコーヒーは1行目の3と4行目の4。各条件は丸括弧で囲みます。
問い合わせ記録から、緊急度が「高」または「中」の内容を抜き出します。
D2 に、緊急度（B列）が「高」または「中」の行の内容（C列）を抽出する数式を入力してください（スピル）。条件は + でつなぎます。| A | B | C | D | |
|---|---|---|---|---|
| 1 | No. | 緊急度 | 内容 | 抽出結果 |
| 2 | 1 | 高 | A | |
| 3 | 2 | 低 | B | |
| 4 | 3 | 中 | C | |
| 5 | 4 | 低 | D | |
| 6 | 5 | 高 | E |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=FILTER(C2:C6,(B2:B6="高")+(B2:B6="中")) FILTER で OR は各条件を + で足します（どちらかが TRUE=1 なら合計1以上で残る）。高または中はA・C・Eの3件。AND の * と使い分けます。
売上の数値を、小さい順（昇順）に並べ替えて一覧にします。
C2 に、A列の売上（A2:A6）を昇順（小さい順）に並べ替える数式を入力してください（スピル）。| A | B | C | |
|---|---|---|---|
| 1 | 売上 | 昇順 | |
| 2 | 300 | ||
| 3 | 100 | ||
| 4 | 500 | ||
| 5 | 200 | ||
| 6 | 400 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=SORT(A2:A6) SORT(範囲) は既定で昇順（小さい順）に並べます。100・200・300・400・500の順。降順にしたいときは SORT(範囲,1,FALSE) のように並べ替え順を FALSE にします。
受注リストの商品名から、重複しない一覧を昇順で作ります。
C2 に、A列の商品名から重複を除き、昇順に並べた一覧を出す数式を入力してください（スピル）。| A | B | C | |
|---|---|---|---|
| 1 | 商品名 | 一覧 | |
| 2 | みかん | ||
| 3 | りんご | ||
| 4 | みかん | ||
| 5 | ばなな | ||
| 6 | りんご |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=SORT(UNIQUE(A2:A6)) UNIQUE で重複排除、SORT で昇順に整列します。文字列の昇順は文字コード順で、ひらがなは概ね五十音順になるため、ばなな→みかん→りんごの順に3件スピルします。
売上ランキングで、上位3件の売上額だけを大きい順に取り出します。
C2 に、A列の売上（A2:A7）から上位3件を降順で取り出す数式を入力してください（スピル）。| A | B | C | |
|---|---|---|---|
| 1 | 売上 | 上位3件 | |
| 2 | 300 | ||
| 3 | 500 | ||
| 4 | 100 | ||
| 5 | 450 | ||
| 6 | 200 | ||
| 7 | 350 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=SORTN(A2:A7,3,0,1,FALSE) SORTN(範囲, 件数, 同順位の扱い, 並べ替え列, 昇順か) は並べ替えたうえで上位N件だけを返します。降順（FALSE）で上位3件は500・450・350。TOP N 抽出を1関数で行えます。
午前と午後で別々の列に入力された担当者名を、1列の名簿にまとめます。
D2 に、A列の担当者（A2:A4）の下にB列の担当者（B2:B4）を続けて、縦1列の名簿にまとめる数式を入力してください（スピル）。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 午前 | 午後 | 名簿 | |
| 2 | 佐藤 | 高橋 | ||
| 3 | 鈴木 | 伊藤 | ||
| 4 | 田中 | 渡辺 | ||
| 5 | ||||
| 6 | ||||
| 7 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=FLATTEN(A2:A4,B2:B4) FLATTEN は複数の範囲を渡した順に、それぞれ縦に並べて1列へ連結します。ここでは A列（佐藤・鈴木・田中）の下に B列（高橋・伊藤・渡辺）が続き、縦6件になります。複数列を1列の名簿に統合する場面で便利です（配列リテラル {A2:A4;B2:B4} でも同じ結果になります）。
東店と西店、別々の表にある商品名を、1つの数式で縦に結合した一覧にします。
D2 に、東店リスト（A2:A4）の下に西店リスト（B2:B3）を続けて縦結合する数式を入力してください（スピル）。配列リテラル {'{ }'} を使います。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 東店 | 西店 | 統合一覧 | |
| 2 | りんご | みかん | ||
| 3 | ばなな | ぶどう | ||
| 4 | もも | |||
| 5 | ||||
| 6 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
={A2:A4;B2:B3} 配列リテラルで {範囲1;範囲2} のようにセミコロン区切りにすると、範囲を縦に積み重ねます（カンマだと横結合）。東店3件の下に西店2件が続き、合計5件が縦に並びます。日本語ロケールでは区切りに ; ではなく \（縦）を使う設定もありますが、ここでは標準の ; を用います。
社員一覧から、部署が「営業」の人の点数だけを抜き出し、高い順に並べたランキングを作ります。
E2 に、部署（B列）が「営業」の行の点数（C列）を抽出し、降順（高い順）に並べる数式を入力してください（スピル）。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 氏名 | 部署 | 点数 | 営業の点数(降順) | |
| 2 | 佐藤 | 営業 | 80 | ||
| 3 | 鈴木 | 開発 | 95 | ||
| 4 | 田中 | 営業 | 70 | ||
| 5 | 高橋 | 営業 | 90 | ||
| 6 | 伊藤 | 開発 | 60 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=SORT(FILTER(C2:C6,B2:B6="営業"),1,FALSE) FILTER で営業の点数（80・70・90）を抜き出し、その結果を SORT に渡して降順（FALSE）に整列します。90→80→70の順。関数を入れ子にして「抽出してから並べ替え」を一気に行えます。
QUERY
SQL風の文でデータを抽出・集計します。=QUERY(範囲, “クエリ”, 見出し行数) の形で、列は select A や Col1 で指定します。SheetExでは結果が縦1列または1セルになる問いを扱います（複数列になる group by / pivot は解説でのみ触れます）。クエリ内の文字列は半角シングルクオート ’…’ で囲みます。
売上表から、店舗が渋谷の行の商品名だけを取り出します。
E2 に、A列(店舗)が渋谷である行のB列(商品)を抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 金額 | 結果 | |
| 2 | 渋谷 | 茶 | 150 | ||
| 3 | 新宿 | 水 | 80 | ||
| 4 | 渋谷 | 菓子 | 200 | ||
| 5 | 池袋 | 茶 | 120 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select B where A='渋谷'",1) select B で取り出す列を指定し、where A=‘渋谷’ で行を絞ります。文字列条件はシングルクオートで囲みます。渋谷の行は2行あり、商品は 茶・菓子 が縦に並びます。第3引数の 1 は見出しが1行あることの指定です。
売上表から、金額が100以上の店舗名を金額の大きい順に並べます。
E2 に、C列(金額)が100以上のA列(店舗)を、金額の降順で抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 金額 | 結果 | |
| 2 | 渋谷 | 茶 | 150 | ||
| 3 | 新宿 | 水 | 80 | ||
| 4 | 池袋 | 菓子 | 200 | ||
| 5 | 上野 | 茶 | 120 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select A where C>=100 order by C desc",1) where C>=100 で80の新宿を除外し、order by C desc で金額の降順に並べます。池袋200・渋谷150・上野120の順で店舗名が並びます。
受注一覧から、ステータスが完了の件数を1つの数で求めます。
E2 に、B列(ステータス)が完了の行数を求める数式を入力してください。結果が1セルになるよう、ラベルは空にします。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 注文 | ステータス | 完了件数 | ||
| 2 | 1001 | 完了 | |||
| 3 | 1002 | 処理中 | |||
| 4 | 1003 | 完了 | |||
| 5 | 1004 | 完了 | |||
| 6 | 1005 | 処理中 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:B,"select count(B) where B='完了' label count(B) ''",1) count(B) は条件に合う行数を返します。完了は1001・1003・1004の 3 件。集計関数は既定で「count」などのラベル行が結果に付くため、label count(B) ” でラベルを空にすると結果が1セルだけになります。
商品の価格表から、安い順に3件の商品名を取り出します。
E2 に、C列(価格)の安い順でA列(商品)を3件だけ抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 商品 | 区分 | 価格 | 結果 | |
| 2 | ペン | 文具 | 300 | ||
| 3 | 消しゴム | 文具 | 100 | ||
| 4 | ノート | 文具 | 250 | ||
| 5 | 定規 | 文具 | 200 | ||
| 6 | 付箋 | 文具 | 150 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select A order by C asc limit 3",1) order by C asc は昇順（asc は省略可）。limit 3 で先頭3件に絞ります。価格は消しゴム100・付箋150・定規200・ノート250・ペン300なので、安い順3件は 消しゴム・付箋・定規 です。
店舗別の売上から、東京の売上合計だけを1つの数で求めます。
E2 に、A列(店舗)が東京のC列(金額)の合計を求める数式を入力してください。結果が1セルになるようラベルは空にします。| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 金額 | 東京の合計 | ||
| 2 | 東京 | 茶 | 150 | |||
| 3 | 大阪 | 水 | 80 | |||
| 4 | 東京 | 菓子 | 200 | |||
| 5 | 名古屋 | 茶 | 120 | |||
| 6 | 東京 | 水 | 90 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select sum(C) where A='東京' label sum(C) ''",1) sum(C) に where A=‘東京’ を合わせると東京だけの合計になります。150+200+90=440。label sum(C) ” で「sum 金額」のラベル行を消し、結果を1セルにしています。
売上明細から、渋谷かつ金額が100より大きい行の商品名を取り出します。
E2 に、A列(店舗)が渋谷で、かつC列(金額)が100より大きい行のB列(商品)を抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 金額 | 結果 | |
| 2 | 渋谷 | 茶 | 150 | ||
| 3 | 渋谷 | 水 | 80 | ||
| 4 | 新宿 | 菓子 | 200 | ||
| 5 | 渋谷 | 菓子 | 120 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select B where A='渋谷' and C>100",1) and は両方の条件を満たす行だけを残します。渋谷の行は3つですが、金額が100より大きいのは茶150と菓子120の行。水80は除外され、結果は 茶・菓子 です。
会員リストから、ランクがゴールドまたはプラチナの会員名を取り出します。
E2 に、B列(ランク)がゴールドまたはプラチナの行のA列(氏名)を抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 氏名 | ランク | 結果 | ||
| 2 | 佐藤 | ゴールド | |||
| 3 | 鈴木 | シルバー | |||
| 4 | 田中 | プラチナ | |||
| 5 | 高橋 | シルバー | |||
| 6 | 伊藤 | ゴールド |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:B,"select A where B='ゴールド' or B='プラチナ'",1) or はどちらかを満たせば残します。ゴールドの佐藤・伊藤、プラチナの田中が該当し、シルバーは除外。元の並び順のまま 佐藤・田中・伊藤 が返ります。
売上ランキングから、上位を除いた「4位以降」の店舗を順に取り出します。
E2 に、C列(売上)の降順に並べたうえで、上位3件を飛ばし次の2件のA列(店舗)を抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 売上 | 結果 | |
| 2 | A店 | 茶 | 500 | ||
| 3 | B店 | 水 | 300 | ||
| 4 | C店 | 菓子 | 450 | ||
| 5 | D店 | 茶 | 200 | ||
| 6 | E店 | 水 | 350 | ||
| 7 | F店 | 菓子 | 100 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select A order by C desc limit 2 offset 3",1) 売上の降順は A店500・C店450・E店350・B店300・D店200・F店100。offset 3 で上位3件（A・C・E）を飛ばし、limit 2 で続く2件を取ります。結果は4位 B店 と5位 D店 です。
気温の記録から、期間中の最高気温だけを1つの数で求めます。
D2 に、B列(気温)の最大値を求める数式を入力してください。結果が1セルになるようラベルは空にします。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 日付 | 気温 | 最高気温 | ||
| 2 | 6/1 | 24 | |||
| 3 | 6/2 | 28 | |||
| 4 | 6/3 | 22 | |||
| 5 | 6/4 | 31 | |||
| 6 | 6/5 | 27 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=QUERY(A1:B,"select max(B) label max(B) ''",1) max(B) は最大値を返します。24・28・22・31・27のうち最大は 31。集計関数の結果には「max 気温」のラベルが付くので、label max(B) ” で消すと1セルだけになります。最小なら min() を使います。
範囲の左端を基準にした列番号 ColN でも列を指定できます。担当が営業の案件名を取り出します。
E2 に、ColN表記を使い、B列(担当=Col2)が営業である行のA列(案件=Col1)を抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 案件 | 担当 | 結果 | ||
| 2 | α | 営業 | |||
| 3 | β | 開発 | |||
| 4 | γ | 営業 | |||
| 5 | δ | 総務 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:B,"select Col1 where Col2='営業'",1) 範囲が A1:B のとき、左端のA列が Col1、B列が Col2 です。A・B といった列文字の代わりに ColN で書けます（参照範囲が動いても番号は変わりません）。営業はαとγの行で、結果は α・γ。
売上明細から、商品が茶でかつ金額100以上の行を、金額の高い順に2件だけ店舗名で取り出します。
E2 に、B列(商品)が茶で、かつC列(金額)が100以上の行を、金額の降順で2件だけA列(店舗)として抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 金額 | 結果 | |
| 2 | 渋谷 | 茶 | 150 | ||
| 3 | 新宿 | 水 | 300 | ||
| 4 | 池袋 | 茶 | 90 | ||
| 5 | 上野 | 茶 | 250 | ||
| 6 | 品川 | 茶 | 180 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select A where B='茶' and C>=100 order by C desc limit 2",1) まず where B=‘茶’ and C>=100 で絞ると、渋谷150・上野250・品川180の3行（池袋90は除外、新宿は水）。order by C desc で250・180・150の順、limit 2 で上位2件。結果は 上野・品川 です。where→order→limit の順に評価されます。
商品評価から、カテゴリが家電の平均評価だけを1つの数で求めます。
E2 に、B列(カテゴリ)が家電のC列(評価)の平均を求める数式を入力してください。結果が1セルになるようラベルは空にします。| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 商品 | カテゴリ | 評価 | 平均評価 | ||
| 2 | 扇風機 | 家電 | 4 | |||
| 3 | 小説 | 書籍 | 5 | |||
| 4 | 掃除機 | 家電 | 2 | |||
| 5 | 電球 | 家電 | 3 | |||
| 6 | 雑誌 | 書籍 | 1 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select avg(C) where B='家電' label avg(C) ''",1) avg(C) に where B=‘家電’ を組み合わせると家電だけの平均になります。家電は4・2・3で (4+2+3)/3=9/3=3。書籍の行は計算に含まれません。label avg(C) ” でラベルを消し1セルに収めています。複数カテゴリ別に出すなら group by ですが、結果が複数列になるためここでは1カテゴリに絞っています。
申込ログから、3月1日以降の申込を日付の古い順に取り出します。
E2 に、C列(日付)が2026-03-01以降の行のA列(氏名)を、日付の昇順で抽出する数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 氏名 | 区分 | 日付 | 結果 | |
| 2 | 佐藤 | 新規 | 2026-02-20 | ||
| 3 | 鈴木 | 新規 | 2026-03-05 | ||
| 4 | 田中 | 継続 | 2026-04-10 | ||
| 5 | 高橋 | 新規 | 2026-03-01 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select A where C >= date '2026-03-01' order by C asc",1) 日付の比較は date ‘2026-03-01’ のように date リテラルを使います（書式は YYYY-MM-DD 固定）。佐藤の2/20は除外され、3/1の高橋・3/5の鈴木・4/10の田中が残ります。order by C asc で古い順に並べ、結果は 高橋・鈴木・田中。列が日付として認識されている必要があります。
LAMBDA・高階関数
ARRAYFORMULA は「セルごとに同じ式」を一括適用するだけで、「行をまとめて1値にする」「前の結果を引き継いで累積する」といった処理はできません。LAMBDA とその仲間（MAP・REDUCE・SCAN・BYROW・BYCOL・MAKEARRAY）、そして LET を使い、実務の集計・累計・スコア計算を1つの数式で組み立てます。各問とも、模範解答をスプレッドシートに入力した結果と答えが一致するよう設計しています。
四半期の売上表です。担当ごと（行ごと）の合計を1列で出します。ARRAYFORMULA(SUM(B2:D4)) では全体が1つに畳まれてしまい、行別になりません。
E2 に、各行（B〜D）の合計を行ごとに縦1列で出す数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 担当 | 1月 | 2月 | 3月 | 合計 |
| 2 | 佐藤 | 10 | 20 | 30 | |
| 3 | 鈴木 | 5 | 15 | 25 | |
| 4 | 田中 | 40 | 0 | 5 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=BYROW(B2:D4,LAMBDA(r,SUM(r))) BYROW は範囲を1行ずつ取り出して r に渡し、LAMBDA(r,SUM(r)) で各行を1値に集約します。結果は行数ぶんの縦1列。佐藤60・鈴木45・田中45。ARRAYFORMULA では「行内の横方向の集計」を行ごとに分けられないため、BYROW が必要です。
受注明細です。各行の「数量×単価」を金額として一括で出します。
D2 に、数量（B列）と単価（C列）を行ごとに掛けた金額を縦1列で出す数式を入力してください。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 数量 | 単価 | 金額 |
| 2 | A | 3 | 100 | |
| 3 | B | 5 | 200 | |
| 4 | C | 2 | 150 | |
| 5 | D | 4 | 250 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=MAP(B2:B5,C2:C5,LAMBDA(q,p,q*p)) MAP は複数の範囲を同じ位置どうしで取り出し、LAMBDA の引数（ここでは q と p）に渡します。3×100=300、5×200=1000…と要素ごとに計算。単純な掛け算なら ARRAYFORMULA でも書けますが、行内に条件分岐や複数ステップを挟むと MAP の方が読みやすくなります。
入金リストです。初期値0から順に足し込み、最終的な合計を1セルに出します。
D2 に、初期値0から B2:B5 を順に足し込んだ合計（スカラ）を REDUCE で求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 日付 | 入金 | 合計 | ||
| 2 | 1 | 120 | |||
| 3 | 2 | 80 | |||
| 4 | 3 | 200 | |||
| 5 | 4 | 50 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=REDUCE(0,B2:B5,LAMBDA(acc,v,acc+v)) REDUCE(初期値, 範囲, LAMBDA(累積, 現在値, 式)) は前の結果（acc）に各値を畳み込み、最後の1値だけを返します。0→120→200→400→450。SUM で済む場面ですが、REDUCE は累積に好きな処理（条件付き加算など）を差し込めるのが強みです。
家計の入出金です。入金はプラス・出金はマイナスで並んでいます。各時点の残高（累計）を縦1列で出します。
C2 に、初期値0から B2:B6 を順に足し込み、各行時点の累計残高を縦1列で出す数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 項目 | 増減 | 残高 |
| 2 | 給与 | 100 | |
| 3 | 食費 | -30 | |
| 4 | 臨時収入 | 50 | |
| 5 | 光熱費 | -20 | |
| 6 | 賞与 | 80 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=SCAN(0,B2:B6,LAMBDA(acc,v,acc+v)) SCAN は REDUCE と同じ畳み込みですが「途中経過をすべて」返すのが違いです。0+100=100、100−30=70、70+50=120、120−20=100、100+80=180。各行で「直前までの結果」を参照するため、ARRAYFORMULA のような同時計算では作れません。
3科目のテスト結果です。生徒ごとの最高得点を1列で出します。
E2 に、各行（B〜D）の最大値を行ごとに縦1列で出す数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 氏名 | 国語 | 数学 | 英語 | 最高点 |
| 2 | 佐藤 | 40 | 55 | 30 | |
| 3 | 鈴木 | 60 | 20 | 90 | |
| 4 | 田中 | 15 | 15 | 15 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=BYROW(B2:D4,LAMBDA(r,MAX(r))) LAMBDA の中身を MAX(r) に変えるだけで、行ごとの最大が取れます。佐藤55・鈴木90・田中15。BYROW は「各行→1値」の縦1列を返すので、行単位の集約（合計・最大・平均など）に向きます。
模試の3回分の点数です。生徒ごとに「80点以上が何回あったか」を1列で出します。
E2 に、各行（B〜D）で 80 以上の個数を行ごとに縦1列で出す数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 氏名 | 1回 | 2回 | 3回 | 80以上の回数 |
| 2 | 佐藤 | 80 | 75 | 90 | |
| 3 | 鈴木 | 60 | 85 | 80 | |
| 4 | 田中 | 70 | 65 | 50 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=BYROW(B2:D4,LAMBDA(r,COUNTIF(r,">=80"))) 行を r に取り、COUNTIF(r,">=80") で行内の条件付き件数を数えます。佐藤は80・90で2回、鈴木は85・80で2回、田中は0回。COUNTIF を「行ごと」に効かせるには BYROW の枠が要り、ARRAYFORMULA(COUNTIF(…)) では行別になりません。
ある1か月の日次売上が縦に並んでいます。BYCOL で列を丸ごと1値に集約します。
D2 に、売上の列（B2:B6）を BYCOL で受け取り、その合計（スカラ）を返す数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 日 | 売上 | 月合計 | ||
| 2 | 1 | 1200 | |||
| 3 | 2 | 800 | |||
| 4 | 3 | 1500 | |||
| 5 | 4 | 900 | |||
| 6 | 5 | 1100 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=BYCOL(B2:B6,LAMBDA(c,SUM(c))) BYCOL は範囲を1列ずつ取り出して c に渡します。ここでは列が1本なので結果はスカラ（1値）に。1200+800+1500+900+1100=5500。複数列を渡せば各列を集約した横1行を返すので、列ごとの月次集計などに使えます。
補助テーブルとして、1〜5の二乗（1,4,9,16,25）の列を式だけで作ります。
A2 に、5行×1列で「行番号の二乗」を生成する数式を入力してください（縦1列にスピル）。| A | |
|---|---|
| 1 | 二乗 |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に A2 で解いてみましょう。
=MAKEARRAY(5,1,LAMBDA(r,c,r^2)) MAKEARRAY(行数, 列数, LAMBDA(r,c,式)) は指定サイズの配列を、行番号 r・列番号 c から計算して作ります。5行1列で r^2 なので 1・4・9・16・25。元データが無くても「形」から表を生成できるのが特徴です。
センサーの測定値です。初期値0から畳み込み、最大値を1セルに出します（最大の自作ロジックの練習）。
D2 に、初期値0から B2:B6 を畳み込み、最大値（スカラ）を REDUCE で求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 時刻 | 値 | 最大 | ||
| 2 | 1 | 34 | |||
| 3 | 2 | 78 | |||
| 4 | 3 | 12 | |||
| 5 | 4 | 90 | |||
| 6 | 5 | 56 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=REDUCE(0,B2:B6,LAMBDA(acc,v,MAX(acc,v))) MAX(acc,v) を畳み込むと、各ステップで「今までの最大」を保持します。0→34→78→78→90→90で結果は 90（値はすべて0以上なので初期値0は安全）。MAX 関数そのままでも求まりますが、REDUCE は「累積判定」の型を理解する好例です。
参加者名を1つのセルに「・」区切りでまとめます。TEXTJOIN を使わず、REDUCE で連結ロジックを組みます。
C2 に、A2:A4 の氏名を「・」でつないだ1つの文字列（スカラ）を REDUCE で作る数式を入力してください。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 氏名 | 連結結果 | ||
| 2 | 佐藤 | |||
| 3 | 鈴木 | |||
| 4 | 田中 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=REDUCE("",A2:A4,LAMBDA(acc,v,IF(acc="",v,acc&"・"&v))) 初期値を空文字 "" にし、最初だけそのまま・2件目以降は acc&"・"&v で区切り文字を挟みます。結果は 佐藤・鈴木・田中。REDUCE は数値だけでなく文字列の畳み込みにも使え、区切りや条件を自在に組み込めます。
毎月の売上です。その月までの「過去最高売上（自己ベスト）」を各行で更新しながら縦1列に出します。
C2 に、B2:B6 を先頭から走査し、各行時点での「それまでの最大値」を縦1列で出す数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 月 | 売上 | 過去最高 |
| 2 | 1 | 30 | |
| 3 | 2 | 55 | |
| 4 | 3 | 40 | |
| 5 | 4 | 80 | |
| 6 | 5 | 70 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=SCAN(0,B2:B6,LAMBDA(acc,v,MAX(acc,v))) SCAN で MAX(acc,v) を累積すると「ここまでの最高記録」が列で残ります。30→55→（40は更新せず）55→80→（70は更新せず）80。各行が前行の結果に依存するため、SCAN ならではの処理です（売上はすべて0以上なので初期値0でOK）。
税抜価格に消費税10%を乗せた税込価格を、行ごとに出します。式の意味が読み取りやすいよう LET で名前を付けます。
C2 に、LET で税率を変数 rate として定義し、B2:B4 に (1+rate) を掛けた税込価格を縦1列で出す数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 商品 | 税抜 | 税込 |
| 2 | A | 1000 | |
| 3 | B | 2500 | |
| 4 | C | 800 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=LET(rate,0.1,ARRAYFORMULA(B2:B4*(1+rate))) LET(名前, 値, …, 式) は途中の値に名前を付けて式を読みやすくします。rate を 0.1 と定義し (1+rate)=1.1 を掛けるので、1000→1100、2500→2750、800→880。税率を1か所で変えれば全体に反映でき、保守性が上がります。
総合実践（複合問題）
ここまで学んだ関数を、複数組み合わせて解きます。検索の失敗を既定値に逃がす、条件で絞ってから整列する、行ごとに評価を付ける——現場で本当に出てくる集計・抽出を、1つの数式で組み立てます。各問とも、模範解答を Google スプレッドシートに入力した実際の結果と答えが一致するよう設計しています。
商品マスタ(A:B)から、問い合わせコード(E2)の単価を引きます。マスタに無いコードでもエラーにせず「該当なし」と返します。
F2 に、E2 のコードに対応する単価を返し、見つからなければ 該当なし と表示する数式を入力してください。| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | コード | 単価 | 問い合わせ | 結果 | ||
| 2 | A001 | 120 | X999 | |||
| 3 | A002 | 80 | ||||
| 4 | A003 | 300 | ||||
| 5 | A004 | 250 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に F2 で解いてみましょう。
=IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"該当なし") XLOOKUP は一致が無いと既定では #N/A を返します。これを IFERROR で包み、第2引数に代替値を渡すと、失敗時だけ「該当なし」に差し替えられます。E2 の X999 はマスタに無いので結果は 該当なし。XLOOKUP 自体の第4引数で既定値を指定する手もありますが、IFERROR 版はあらゆるエラーをまとめて受けられるのが利点です。
受注表です。数量×単価で金額を一括計算しますが、未入力の行（数量が空）は 0 ではなく空欄のままにします。
D2 に、各行の数量(B)×単価(C)を縦1列で出し、数量が空の行は空欄にする数式を入力してください。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 数量 | 単価 | 金額 |
| 2 | A | 3 | 100 | |
| 3 | B | 200 | ||
| 4 | C | 2 | 150 | |
| 5 | D | 4 | 250 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=ARRAYFORMULA(IF(B2:B5="","",B2:B5*C2:C5)) ARRAYFORMULA が範囲どうしの掛け算を行ごとに展開し、その内側の IF が「数量が空なら空文字、そうでなければ掛け算」を判定します。2行目は数量が空なので空欄、それ以外は 3×100=300、2×150=300、4×250=1000。空欄を 0 と扱わせない定番パターンです。
アクセスログの参照元です。direct を除いた流入元を、重複を除き五十音（昇順）で一覧にします。
C2 に、A列の参照元から direct 以外を抽出し、重複を除いて昇順に並べた一覧を出す数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 参照元 | 結果 | |
| 2 | |||
| 3 | direct | ||
| 4 | yahoo | ||
| 5 | |||
| 6 | bing | ||
| 7 | yahoo |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=SORT(UNIQUE(FILTER(A2:A7,A2:A7<>"direct"))) 内側から読みます。FILTER が direct 以外を残し、UNIQUE が重複を畳み、SORT が並べ替えます。残るのは google・yahoo・bing で、重複除去後に昇順整列して bing・google・yahoo。抽出→重複排除→整列という三段の処理を、関数の入れ子で一気通貫に書けます。
売上明細です。カテゴリが 食品 の金額合計だけを1セルに出します。
E2 に、B列(カテゴリ)が食品である行の C列(金額)の合計を返す数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 商品 | カテゴリ | 金額 | 食品合計 | |
| 2 | 茶 | 食品 | 150 | ||
| 3 | ノート | 文具 | 80 | ||
| 4 | 菓子 | 食品 | 200 | ||
| 5 | ペン | 文具 | 120 | ||
| 6 | 米 | 食品 | 300 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=QUERY(A1:C,"select sum(C) where B='食品' label sum(C) ''",1) QUERY の select sum(C) で金額を集計し、where B='食品' で対象行を絞ります。label sum(C) '' は集計列の見出しを消し、結果を 1セルのスカラにするための指定です。150+200+300=650。SUMIF でも同じ結果になりますが、QUERY は条件と集計を SQL 風に1か所で書けるのが強みです。
科目ごとに配点（重み）が違うテストです。点数×配点の合計を配点の合計で割り、加重平均点を出します。
E2 に、点数(B)と配点(C)の加重平均（Σ(点数×配点)÷Σ配点）を求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 科目 | 点数 | 配点 | 加重平均 | |
| 2 | 国語 | 80 | 2 | ||
| 3 | 数学 | 60 | 3 | ||
| 4 | 英語 | 90 | 1 | ||
| 5 | 理科 | 70 | 4 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) SUMPRODUCT は2つの範囲を要素ごとに掛けて合計します。分子は 80×2+60×3+90×1+70×4=160+180+90+280=710、分母は配点合計 2+3+1+4=10。710÷10=71。重み付き集計は SUMPRODUCT と SUM の組み合わせが定石です。
在庫マスタ(A:B)を引き、注文数(E2)と比べて出荷可否を返します。
F2 に、E2 の商品の在庫数を引き、注文数 3 以上なら 出荷可、不足なら 在庫不足 と返す数式を入力してください。| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 商品 | 在庫 | 注文 | 判定 | ||
| 2 | りんご | 5 | みかん | |||
| 3 | みかん | 2 | ||||
| 4 | ぶどう | 8 | ||||
| 5 | もも | 1 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に F2 で解いてみましょう。
=IF(XLOOKUP(E2,A2:A5,B2:B5)>=3,"出荷可","在庫不足") まず XLOOKUP で E2(みかん)の在庫 2 を取り出し、その値を IF で 3 と比較します。2 は 3 未満なので 在庫不足。検索した値をそのまま条件式に渡せるので、引く処理と判定する処理を1つの式に畳めます。注文数を変えれば判定も自動で切り替わります。
出欠表です。ステータスが 欠席 のメンバーだけを、読点（、）でつないだ1行のテキストにします。
D2 に、C列が 欠席 の氏名(A列)を、、区切りで連結した文字列を出す数式を入力してください。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 氏名 | 状態 | 欠席者 | |
| 2 | 佐藤 | 出席 | ||
| 3 | 鈴木 | 欠席 | ||
| 4 | 田中 | 欠席 | ||
| 5 | 高橋 | 出席 | ||
| 6 | 伊藤 | 欠席 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=TEXTJOIN("、",TRUE,FILTER(A2:A6,C2:C6="欠席")) FILTER がステータス＝欠席の氏名（鈴木・田中・伊藤）だけを縦に取り出し、それを TEXTJOIN が区切り文字「、」でつなぎます。第2引数の TRUE は空セルを飛ばす指定です。結果は 鈴木、田中、伊藤。「条件に合うものを抜き出して1文にする」通知文・宛先リスト作りの定番です。
点数を A/B/C/D の4段階に振り分けます。90以上=A、70以上=B、50以上=C、それ未満=D です。
C2 に、B列の点数を 90→A / 70→B / 50→C / それ未満→D で評価する数式を、行ごとに縦1列で出してください。| A | B | C | |
|---|---|---|---|
| 1 | 氏名 | 点数 | 評価 |
| 2 | 佐藤 | 92 | |
| 3 | 鈴木 | 68 | |
| 4 | 田中 | 75 | |
| 5 | 高橋 | 40 | |
| 6 | 伊藤 | 50 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=ARRAYFORMULA(IFS(B2:B6>=90,"A",B2:B6>=70,"B",B2:B6>=50,"C",TRUE,"D")) IFS は条件を上から順に評価し、最初に真になった分岐を返します。大きいしきい値から並べるのがコツで、最後の TRUE,"D" が「どれにも当たらない場合」を受けます。92→A、68→C、75→B、40→D、50→C。ARRAYFORMULA で包むことで、全行を1式で一括評価できます。
120円 のように単位付きで入力された価格列があります。数字部分だけを取り出し、合計します。
D2 に、A列の各セルから数字部分を取り出して数値化し、その合計（スカラ）を求める数式を入力してください。| A | B | C | D | |
|---|---|---|---|---|
| 1 | 価格 | 合計 | ||
| 2 | 120円 | |||
| 3 | 80円 | |||
| 4 | 300円 | |||
| 5 | 50円 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に D2 で解いてみましょう。
=SUMPRODUCT(VALUE(REGEXEXTRACT(A2:A5,"\d+"))) REGEXEXTRACT が各セルから \d+（連続する数字）を抜き出し、それは文字列なので VALUE で数値に変換します。120+80+300+50=550。配列を返すため SUMPRODUCT（または ARRAYFORMULA+SUM）でまとめて合計できます。単位付き・記号混じりの入力を計算可能な数値に直す典型処理です。
単価が左、商品名が右という並びの表です。VLOOKUP では左方向に引けないため、INDEX と XMATCH を組みます。
E2 に、商品名(B列)が ぶどう の行の単価(A列)を返す数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 単価 | 商品名 | 検索 | 単価 | |
| 2 | 120 | りんご | ぶどう | ||
| 3 | 80 | みかん | |||
| 4 | 300 | ぶどう | |||
| 5 | 250 | もも |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=INDEX(A2:A5,XMATCH(D2,B2:B5)) XMATCH が D2(ぶどう)の位置（上から3番目）を返し、INDEX がその位置の単価列の値を取り出します。結果は 300。検索列より左の列を返したいとき、INDEX+XMATCH（または XLOOKUP）が必要です。VLOOKUP は「検索列の右」しか返せないため、この配置では使えません。
受注ログです。店舗が東京 かつ 金額が100以上 の件数を1セルで数えます。
E2 に、A列が 東京 かつ C列が 100以上 である行の件数を求める数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 店舗 | 商品 | 金額 | 件数 | |
| 2 | 東京 | 茶 | 150 | ||
| 3 | 大阪 | 水 | 200 | ||
| 4 | 東京 | 菓子 | 80 | ||
| 5 | 東京 | 米 | 300 | ||
| 6 | 大阪 | 茶 | 120 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=SUMPRODUCT((A2:A6="東京")*(C2:C6>=100)) SUMPRODUCT に渡した2つの条件式は、各行で TRUE/FALSE の配列になります。掛け算すると「両方 TRUE の行だけ 1×1=1」になり、その合計が件数です。東京×100以上は1行目(150)と4行目(300)の 2件（3行目は東京だが80で除外）。AND条件のカウントは COUNTIFS でも書けますが、SUMPRODUCT は加重や複雑な条件にも拡張しやすい万能型です。
クレーム一覧から「未対応」だけを抜き出します。ただし1件も無い場合に #N/A を出さず「該当なし」と表示します。
C2 に、B列が 未対応 の案件名(A列)を抽出し、0件なら 該当なし と返す数式を入力してください。| A | B | C | |
|---|---|---|---|
| 1 | 案件 | 状態 | 抽出結果 |
| 2 | 案件1 | 対応済 | |
| 3 | 案件2 | 対応済 | |
| 4 | 案件3 | 対応済 | |
| 5 | 案件4 | 対応済 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に C2 で解いてみましょう。
=IFERROR(FILTER(A2:A5,B2:B5="未対応"),"該当なし") FILTER は条件に合う行が1つも無いと #N/A を返してしまいます。これを IFERROR で受け、代替テキストに差し替えると画面がエラー表示で荒れません。今回は全件「対応済」で未対応はゼロ件なので 該当なし。抽出系の数式は「0件のとき」を IFERROR/IFNA で必ずケアするのが実務の作法です。
会員の購入額です。会員ランク表(D:E)の各ランクについて、そのランク会員の購入額合計を一覧にします。
E2 に、各ランク(D列)について A列がそのランクである行の購入額(B列)合計を、ランクごとに縦1列で出す数式を入力してください。| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ランク | 購入額 | ランク表 | 合計 | |
| 2 | 金 | 500 | 金 | ||
| 3 | 銀 | 300 | 銀 | ||
| 4 | 金 | 200 | 銅 | ||
| 5 | 銅 | 100 | |||
| 6 | 銀 | 400 |
「データをコピー」→ 新しいスプレッドシートのセル A1 に貼り付け → 実際に E2 で解いてみましょう。
=MAP(D2:D4,LAMBDA(g,SUMIF(A2:A6,g,B2:B6))) MAP がランク表 D2:D4 の各ランクを g に取り出し、その都度 SUMIF で「A列＝g の購入額合計」を計算します。金は 500+200=700、銀は 300+400=700、銅は 100。集計キーの一覧に対して同じ集計を繰り返し適用する、ミニ集計表の作り方です（ARRAYFORMULA(SUMIF(…)) でも同じ結果になります）。
次は 第14章 GASドリル で、関数では届かない自動化の領域を試します。