Excelが使えるとは何か

LWP | Excelが使えるとは何か

Excelが使えるとは何か

図解1|Excelの力を3つの設計から考える

フナさんとナマズさんが担当者別かつ商品別の売上表を前に、参照、明細から集計への表変換、関数の結果の受渡しを考える

図1|同じ売上表を、参照・表の構造・関数の組合せという3つの観点で見ていきます。

解説1|関数を使うところから仕事の表を組み立てるところへ

ストーリー

「集計の関数は知ってるんだけど、商品の列が増えたら、どこを直すんだろう」。ナマズさんは、担当者別・商品別の売上を比較する表をフナさんへ見せました。

「まず、何を動かして、何を集めるか見ましょう」。フナさんは、売上一覧と、ナマズさんが欲しい集計表を並べます。ナマズさんが比較したいのは、誰が、どの商品を、いくら売ったか。関数を選ぶ前に、その対応を確かめることになりました。

仕事で使う力を考える一つの目安

この記事では、Excelを仕事で使う力を、参照を設計すること、表の構造を変えること、関数を組み合わせることから考えます。知っている機能を、目の前のデータと目的へ結び付けるための見方です。

職種や目的によって、必要な技能は異なります。この3つができるかだけで、すべての人の能力を判定するものではありません。表を作る、グラフを見やすくする、入力を整えるといった技能にも、それぞれの役割があります。

今回の例では、担当者と商品の条件を数式へ渡し、明細から集計表を作り、その集計表から必要な値を取り出します。参照・構造・処理が、同じ仕事の中でどうつながるかを見ていきましょう。

図解2|コピーするのはセルとの位置関係

B2の=A1をC3へコピーすると=B2になる相対参照と、行または列を固定する絶対参照と複合参照の比較

図2|数式をコピーすると、固定していない行と列が、コピー先に合わせて変わります。

解説2|どの行と列を動かすかを決める

相対参照は位置関係を保つ

B2に次の式があるとします。

01

=A1

B2からA1は、1列左・1行上にあります。この式をC3へコピーすると、C3から1列左・1行上のB2を参照するため、式は=B2になります。Excelは、コピー元の番地を文字列としてそのまま残すのではなく、数式のあるセルとの位置関係を保ちます。

固定する部分を指定する

列記号や行番号の前に$を付けると、その部分はコピーで変わらなくなります。下の表は、数式を右へ1列、下へ1行コピーした場合です。

元の参照

コピー後の参照

固定する部分

A1

B2

なし

$A$1

$A$1

列Aと行1

$A1

$A2

列A

A$1

B$1

行1

$A1とA$1は、行と列の一方を固定する複合参照です。たとえば、左端の担当者は列を固定し、上端の商品は行を固定すると、縦横へ数式を展開できます。

ここで扱っているのは、セルの数式をコピーする場合の変化です。セルの切り取り移動や、行列の挿入による参照の調整とは分けて考えてください。

図解3|明細の一行と集計の一セル

一行が一件の売上を表す4行の明細と、一セルが担当者と商品の条件に合う売上合計を表す2行2列の集計表。双方の合計は7500

図3|一覧表の明細を残しながら、担当者と商品の組合せごとの合計を作ります。

解説3|表を変える前に一行と一セルの意味を見る

入力は一件ずつの売上明細

「明細」シートのA1:D5へ、次の表を置きます。一行は、一つの日付に一人の担当者が一つの商品を売った明細です。金額は説明用の例です。

日付

担当者

商品

売上

10/1

A

商品1

1000

10/1

B

商品2

2000

10/2

A

商品2

1500

10/2

B

商品1

3000

このような一覧を、ここではデータベース形式の表と呼びます。データベース製品を使うという意味ではなく、同じ列構成で明細を縦に並べた形を指しています。

結果は条件ごとの合計

「集計」シートのA1:C3は、次の形にします。

担当者

商品1

商品2

A

1000

1500

B

3000

2000

この表の一セルは、同じ担当者・同じ商品の売上合計です。たとえばC2の1500は、担当者A・商品2という二条件に対応します。同じ組合せの明細が複数あれば、それらを足し合わせます。単に表を回転させているわけではありません。

元明細の合計は7500、集計表の4セルの合計も7500です。今回は全明細を同じ条件で含めているため、両者を照合できます。合計が合っていても各セルの配分が正しいとは限らないので、担当者A・商品2など、個別の条件でも確認します。

図解4|縦と横の条件を一つの式へ

SUMIFSに明細の売上と担当者と商品の条件を渡し、$A2で列Aを、B$1で行1を固定して、集計表の右下へ数式をコピーする

図4|左の担当者と上の商品を条件として、一つの数式を表へ展開します。

解説4|複合参照とSUMIFSで集計表を作る

数式に渡す条件

集計シートのB2は、A2の担当者AとB1の商品1に対応するセルです。A2は縦へコピーするとA3へ変えたい一方、右へコピーしてもA列を見ていてほしいため、$A2と書きます。B1は右へコピーするとC1へ変えたい一方、下へコピーしても1行目を見るため、B$1と書きます。

B2へ入力する数式

01

=SUMIFS(

02

    明細!$D$2:$D$5,

03

    明細!$B$2:$B$5,$A2,

04

    明細!$C$2:$C$5,B$1

05

)

SUMIFSは、複数の条件に合う行の金額を合計します。最初のD2:D5が売上、B2:B5が担当者、C2:C5が商品です。元明細の範囲は、右下へコピーしても変わらないよう、列と行の両方を固定しています。

コピー後の条件と結果

集計先

担当者の参照

商品の参照

結果

B2

$A2 → A

B$1 → 商品1

1000

C2

$A2 → A

C$1 → 商品2

1500

B3

$A3 → B

B$1 → 商品1

3000

C3

$A3 → B

C$1 → 商品2

2000

B2の式をC3までコピーすれば、同じ規則で4セルを作れます。合計範囲と各条件範囲は、同じ大きさで、同じ明細行に対応させてください。範囲の開始行がずれると、担当者と金額を正しく組み合わせられません。

図解5|ほしい値までを関数の部品に分ける

商品コードP02をMATCHでB2:B4の2番目と求め、その位置2をINDEXへ渡して左のA2:A4からレンチを取り出す

図5|MATCHが返す位置を、INDEXが値を取り出すために使います。

解説5|足りない情報を別の関数で求める

入力と欲しい値

別のシートのA1:B4に、次の商品表があるとします。E2へP02を入れ、そのコードの商品名「レンチ」を取り出したい場面です。

商品名

コード

スパナ

P01

レンチ

P02

ドライバー

P03

VLOOKUPの標準的な使い方では、検索範囲の左端から探し、右側の列の値を返します。今回の商品名は検索するコードの左にあるので、別の組合せを考えます。

位置を求めて値を取る

MATCHは、指定範囲の中で項目が何番目にあるかを返します。次の式の結果は2です。末尾の0は完全一致の指定です。

01

=MATCH(E2,$B$2:$B$4,0)

P02があるのはシートの3行目ですが、B2:B4の中では2番目です。その2を、取得する範囲もA2:A4にそろえたINDEXへ渡します。

01

=INDEX($A$2:$A$4,2)

INDEXが返すのは「レンチ」です。位置2を手入力する代わりにMATCHを入れると、次の式になります。

01

=INDEX($A$2:$A$4,

02

    MATCH(E2,$B$2:$B$4,0))

検索列と取得列は、同じ行順で対応させます。式の形を覚えるだけでなく、内側のMATCHが2を返し、外側のINDEXがその2を使う、と具体的な値で読めることが大切です。

図解6|行と列を別々に探して交点を取る

集計表の担当者Aの行位置1と商品2の列位置2を別々のMATCHで求め、INDEXで本体B2:C3の交点1500を取り出す

図6|集計済みの表から、担当者と商品を指定して一つの値を取り出します。

解説6|二つの位置をINDEXへ渡す

行と列の見出しで選ぶ

図3で作った集計シートを使います。A2:A3には担当者A・B、B1:C1には商品1・商品2、B2:C3には4つの集計値があります。F2をA、G2を商品2にすると、欲しい値はC2の1500です。

ここでは、すでにある集計値を選びます。明細を合計するSUMIFSの処理とは役割が異なります。

二方向の検索を組み合わせる数式

01

=INDEX($B$2:$C$3,

02

    MATCH(F2,$A$2:$A$3,0),

03

    MATCH(G2,$B$1:$C$1,0))

最初のMATCHが担当者の行位置、二つ目のMATCHが商品の列位置を返します。

求める情報

検索範囲

今回の結果

Aの行位置

A2:A3

1

商品2の列位置

B1:C1

2

本体の1行目・2列目

B2:C3

1500

二つのMATCHを結果の数字に置き換えると、=INDEX($B$2:$C$3,1,2)になります。この行位置1・列位置2も、シート全体の行番号や列番号ではなく、B2:C3の中で数えた位置です。

成立する条件

行見出しと本体の行順、列見出しと本体の列順をそろえます。今回の見出しはそれぞれ一意です。MATCHは完全一致の候補が複数あれば最初の位置を返し、見つからなければ#N/Aになります。重複や表記揺れを、数式で自動的に解消できたと考えないようにしましょう。

図解7|新しい関数でも受け渡す情報を見る

XLOOKUPでP02から左側の商品名レンチを取り出す例と、AI生成式でも参照、表の意味、受け取る情報と返す情報を確かめる観点

図7|使える機能が増えても、数式とデータの対応を読む力が役立ちます。

解説7|短く書ける方法も同じ条件で確かめる

XLOOKUPを使う場合

図5の商品表では、XLOOKUPを使って次のようにも書けます。

01

=XLOOKUP(E2,$B$2:$B$4,$A$2:$A$4)

探す値はE2のP02、検索範囲はコードのB2:B4、戻り範囲は商品名のA2:A4です。XLOOKUPは、返す列が検索列の左右どちらにあっても指定できます。この例は既定の完全一致で「レンチ」を返します。XLOOKUPはExcel 2016・2019では使えないため、利用する環境を確認してください。

長い式を作れること自体が目的ではありません。目的に合い、利用環境で動き、後から読める方法を選びます。INDEXとMATCHは、必要な位置を求め、それを次の関数へ渡す考え方を学ぶ例にもなります。

AIに頼むときも表の意味を伝える

「担当者別、商品別に売上を合計したい」と依頼するなら、元表の一行は何か、どの列が担当者・商品・金額か、結果のどこに各見出しを置くかを伝えます。返ってきた式では、次を確かめます。

参照では、コピー先で動く行と列を確かめます。

表の構造では、結果の一セルが、どの明細をまとめたものかを確かめます。

処理では、関数が何を受け取り、どんな値を返すかを確かめます。

LETで中間結果に名前を付ける場合も、LAMBDAで処理を再利用する場合も、入力と結果の対応を理解することが役立ちます。AIの提案は、自分の表の値に照らして確認していきましょう。

図解8|自分の表で何をするか説明できる

完成した売上集計表の合計7500を照合し、参照、明細から集計への構造変換、MATCHからINDEXへの情報の受渡しを自分の表で説明する

図8|自分の表のどこが変わり、何が集まり、どんな情報が次へ渡るのかを説明します。

解説8|関係と構造と処理を仕事につなげる

まとめ

「『Excelができます』の後に、何ができるか話せばいいんだね」。ナマズさんは完成した表を見返します。担当者と商品を条件に合計できること。コピーで動く部分を説明できること。必要な値を取り出すまでを、関数の役割に分けて考えられることです。

参照を学ぶと、セルの関係を決められます。明細と集計表の意味が分かると、表の構造を変えられます。関数の戻り値を次の引数へ渡せると、処理を組み合わせられます。参照→表変換→関数合成という順は、学習の進め方の一案にもなります。

今回の仕掛け

一つの売上表を使い、$A2とB$1で条件を選び、SUMIFSで明細を集計し、INDEXと二つのMATCHで交点を取り出しました。関数名では別々に見える技能が、同じ表の設計でつながっています。

仕事によってはピボットテーブル、Power Queryなどが合う場合もあります。何を使うときも、対象の一行・一セルの意味、必要な条件、結果の確かめ方を説明できると、表の変更に対応しやすくなります。

出典と関連回

関連回のT249「Excelが普通に使えるとは何か」は、商品Noと見出しを使うVLOOKUP+MATCHと、式の展開を扱います。今回は、参照・明細から集計への変換・関数の組合せをつなぐ観点へ広げました。

仕様はMicrosoft公式の参照の種類、SUMIFS、MATCH、INDEX、関数の入れ子、XLOOKUPで確認できます。参照・表変換・関数合成という整理は、実務で必要な力を考える一つの見方です。

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。