はじめに(この資料全体のはじめに)
Excelテキスト Excel編・テーブル章
社内向け・初学者向け。Excel編の本文(はじめに、A〜H)と、続くテーブル章をまとめたものです。サンプルの列名やシート名は仮置きで、共通データができたら差し替えます。
はじめに(この資料全体のはじめに)
はじめに ― Excelを学ぶということ
1. Excelは単なる表計算ソフトではない
Excelと聞くと、表を作成したり、計算式を入力したり、データを集計したりするためのソフトウェアを思い浮かべるかもしれません。
もちろん、それらはExcelの基本的な機能です。しかし、Excelを学ぶ本当の価値は、操作方法や関数を覚えることだけではありません。
Excelには、ITを活用するうえで重要となる考え方が数多く詰まっています。
例えば、大量のデータを扱うためには、情報を整理し、一定のルールに従って構造化する必要があります。複雑な計算を行うためには、処理の仕組みを理解し、適切な手順に分解する必要があります。また、業務を効率化するためには、そもそも何が問題なのかを把握し、解決するための方法を考えなければなりません。
これらはExcelに限った話ではありません。Power Query、Power Pivot、Power BI、VBA、さらには生成AIやシステム開発にも共通する考え方です。
Excelを学ぶことは、単にソフトウェアの使い方を覚えることではなく、ITを活用するための基礎的な思考力を身につけることでもあるのです。
2. ITを活用するうえで大切な4つの考え方
本教材では、Excelの操作や機能を学ぶ前提として、次の4つの考え方を大切にします。
① 情報を整理し、構造化する
ITの基本は、情報を整理することです。
例えば、顧客情報、売上情報、契約情報などがバラバラの形式で管理されていたら、必要な情報を探したり、正確に集計したりすることは困難です。
そこで、情報を一定のルールに従って整理し、誰が見ても理解できる形にします。
Excelであれば、データを適切な表形式に整えること、マスタを分離すること、データ同士の関係性を明確にすることなどが該当します。
情報が整理されていなければ、どれだけ高度な関数やツールを使っても、正しい結果を得ることは難しくなります。
複雑な情報を整理し、誰もが扱える形にすること。これがIT活用の出発点です。
② 技術は課題を解決するための手段である
Excelには、関数、ピボットテーブル、マクロなど、さまざまな機能があります。
しかし、機能を使うこと自体が目的になってはいけません。
例えば、毎月発生するデータ集計に時間がかかっているという課題があったとします。
このとき、いきなりマクロを作成するのではなく、まずはExcelの標準機能で解決できないかを考えることが重要です。
場合によっては、データの入力方法や業務の手順を見直すだけで、問題が解決するかもしれません。
大切なのは、どの機能を使うかではなく、何を解決したいのかを明確にすることです。
技術は目的ではなく、課題を解決するための手段です。
③ 優れた設計は単純だが、簡単ではない
複雑な処理を実現することと、優れた仕組みを作ることは同じではありません。
例えば、長大な数式を一つのセルに詰め込めば、目的の結果を得られるかもしれません。しかし、その数式を作成した本人でさえ、後から内容を理解できなくなる可能性があります。
一方で、処理を適切に分割し、それぞれの役割を明確にすれば、全体の仕組みは分かりやすくなります。
これはExcelの数式だけでなく、データ管理や業務設計にも共通する考え方です。
• 役割を明確に分ける
• 同じ情報を重複して管理しない
• 複雑な処理を適切に分割する
• 誰が見ても理解できる状態にする
• 後から修正・変更できるようにする
複雑なものを複雑なまま扱うのではなく、整理して単純な仕組みにすることが重要です。
作ることだけでなく、分かりやすく、継続的に使える形にすることを意識しましょう。
④ 個別の操作ではなく、本質を理解する
Excelには数百種類もの関数があり、さまざまな機能が存在します。
それらをすべて暗記する必要はありません。
重要なのは、個々の機能がどのような仕組みで動いているのか、その根底にある原理を理解することです。
例えば、関数を学ぶ際には、関数名や書き方を覚えるだけでなく、次のような共通構造を理解することが重要です。
• 何を入力するのか(引数)
• どのような処理を行うのか
• 何が返されるのか(戻り値)
この仕組みを理解していれば、初めて見る関数であっても、比較的容易に使い方を理解できます。
また、Excelで学んだデータの整理方法や処理の考え方は、Power QueryやPower BIなど、別のツールを学ぶ際にも応用できます。
操作方法を暗記するのではなく、仕組みや原理を理解すること。それが、新しい技術を自分のものにする近道です。
3. Excelから始めて、IT活用の幅を広げる
本教材では、Excelの基本操作や関数だけでなく、データの取得・加工・分析、さらにはPower BIを活用した可視化まで、段階的に学習していきます。
それぞれのツールには異なる役割がありますが、根底にある考え方は共通しています。
学習領域
主な役割
身につける考え方
Excel
データの入力・計算・集計
データの扱い方、計算の仕組み
Power Query
データの取得・加工・整形
情報の整理、処理の自動化
Power Pivot・DAX
データの関連付け・集計・分析
データ構造、関係性、計算ロジック
Power BI
データの可視化・共有
情報の伝達、分析結果の活用
これらは単独で存在するものではなく、相互に連携することで、より高度なデータ活用を実現できます。
例えば、Excelで管理していたデータをPower Queryで自動的に加工し、Power Pivotで複数のデータを関連付け、DAXで分析指標を定義し、Power BIで可視化する。
この一連の流れを理解することで、単なるExcel作業から、データを活用した業務改善へと発展させることができます。
本教材の学習体系を、次の図に示します。右端の「Lv.」は、次の節で説明する、Excelスキルのレベルです。
本教材の学習体系
図の中で色が付いている部分が、「繰り返す作業が、仕組みになる」領域です。
4. Excelのスキルレベルと、効率化の到達点
Excelのスキルには、段階があります。自分が今どの段階にいるのかを知ると、次に何を学べばよいかが見えてきます。
本教材では、Excelのスキルを、次の5つのレベルに分けて考えます。
レベル
できること
効率化の目安
本教材での該当箇所
Lv.1 入力・書式
データを入力し、表を作る。書式を整える
手作業が中心。効率化は、まだ難しい
Excel編の「はじめに」
Lv.2 基本の計算・集計
四則計算、SUMやAVERAGE、並べ替え、フィルター
単純な計算の手間を、減らせる
Excel編 A章(集計)、G章(割合)
Lv.3 関数・ピボットを使いこなす
SUMIFS、XLOOKUP、IFS、FILTER、ピボットテーブル、テーブル
毎回の手計算やコピーを、減らせる。ただし、毎月の取り込みや整形は、手作業のまま
Excel編 A〜H章、テーブル章
Lv.4 データを整え、つなぐ
データベースの考え方、Power Queryでの取得・加工
毎月の取り込みと整形が、「更新ボタン」だけになる
データベースの基礎、Power Query編、M言語 番外編
Lv.5 分析・可視化・共有
データモデル、DAX、Power BI
集計から報告までが、一連の仕組みになり、共有できる
Power Pivot / DAX編、Power BI編
どこまで行くと、効率化できるのか
• 1回きりの作業なら、Lv.3までで十分です
• 毎月・毎週繰り返す作業は、Lv.4から、効果が大きく変わります
• 複数の表をまたぐ分析や、他の人への共有は、Lv.5が対象です
ここで大切なのは、Lv.3とLv.4の間に、大きな段差があることです。Lv.3までは、「関数や操作を覚えて、作業を速くする」段階です。Lv.4からは、「作業の手順を、仕組みにする」段階です。
効率化の分かれ目は、その作業が「1回きり」か「繰り返し」かにあります。
上のレベルのほうが優れている、ということではありません。作業に合ったレベルを選ぶことが大切です。技術は目的ではなく、課題を解決するための手段だからです。
自分のレベルを、確かめてみましょう
• 表を作って、SUMやAVERAGEで、合計や平均を出せる → Lv.2
• 別の表から値を探したり(XLOOKUP)、条件つきで集計したり(SUMIFS)できる → Lv.3
• 毎月、同じ取り込みや整形を、手作業で繰り返している → Lv.4で、解決できる可能性が高い
• 複数の表をつないで、いろいろな切り口で分析したい → Lv.5
5. AI時代だからこそ、基礎を理解する
近年、生成AIの進化によって、プログラミングやデータ分析、資料作成など、これまで専門的な知識が必要だった作業も、AIの支援によって実現しやすくなりました。
Excelの関数やPower Queryの処理、DAXの数式についても、AIに質問すれば、書き方や具体例を提示してもらえます。
しかし、AIが答えを出してくれることと、その答えを正しく活用できることは別の問題です。
例えば、AIに数式を作成してもらったとしても、その数式が何を計算しているのか、結果が正しいのか、実際の業務に適しているのかを判断できなければ、安心して利用することはできません。
また、AIを効果的に活用するためには、そもそも自分の業務を理解し、何を解決したいのかを明確にする必要があります。
AI活用の基本的な流れは、次のように整理できます。
1. 業務を分解する:仕事を工程や作業単位に分ける
2. AIに任せられる部分を見つける:AIの能力と業務の特性を照らし合わせる
3. AIへの入力を設計する:必要な情報や指示を適切に与える
4. 出力を評価する:期待した結果になっているか確認する
5. 必要なら人間が修正する:人間の知識や判断を加える
6. 業務プロセスに組み込む:継続的に活用できる仕組みにする
この流れは、ExcelやPower Queryなどのツールを活用した業務改善にも通じるものです。
重要なのは、特定のツールの使い方を覚えることではありません。
自分の業務を理解し、問題を整理し、適切な技術を選択し、その結果を評価できること。
こうした能力があれば、AIや新しいツールが登場しても、それらを自分の仕事に応用していくことができます。
6. AIでExcelを効率化するには?
生成AIを使えば、Excelの作業は速くなります。ただし、使い方によって、効果は大きく変わります。
AIが得意なことと、人がやること
作業
AIが得意なこと
人がやること
数式・関数を書く
書き方の提示、別の書き方の提案
何を求めるかを決める。結果を検算する
エラーを直す
メッセージの読み解き、修正案
エラーの前後の、データの形を伝える
データの加工手順を考える
Power Queryの手順、M言語のコード
業務の流れと、表の構造を説明する
DAXなどの専門的な式
書式に沿った、式の作成
知りたい指標を、業務の言葉で定義する
レポートの案を出す
構成や、見せ方の案
誰が、何を判断するのかを決める
AIは、「書き方」を担当します。人は、「業務の見方」と「検算」を担当します。
AIで効率化するための、4つのポイント
1. 繰り返す作業を見つける:効率化の効果が大きいのは、毎月・毎週繰り返している作業です
2. 表の形を整えてから、AIに渡す:1行が何を表すのか、列は何なのかが整理されていないデータでは、AIも間違えます
3. 指示は、4点セットで出す:現状(表と列)、やりたいこと、変換前後の例、条件を伝えます。詳しくは、Excel編のH章、M言語 番外編、Power Pivot / DAX編の6章で学びます
4. 必ず、検算する:総計の一致、1件の手計算、境界の値、更新後の確認を行います
AIは、階段を登るのを助けてくれるが、階段を飛ばしてはくれない
AIは、各レベルで、次のように役立ちます。
• Lv.1〜2:関数の使い方や、操作の方法を聞ける
• Lv.3:複数の条件を組み合わせた、式を作ってもらえる
• Lv.4:Power Queryの、M言語の細かい書き方を任せられる
• Lv.5:DAXの式や、レポートの案を相談できる
ただし、表の形、キーの考え方、結果の検算といった「型」を理解していないと、AIへの指示も、答えの評価もできません。基礎の理解は、AIを使いこなすための前提です。
注意:機密情報の扱い
機密情報や個人情報を、AIに入力してよい範囲は、社内のルールに従います。迷うときは、実データではなく、ダミーのデータで質問します。
7. 本教材で目指すこと
本教材の目的は、Excelの操作方法や関数を暗記することではありません。
Excelを入り口として、データを正しく扱い、情報を整理し、仕組みを理解し、業務を改善するための基礎力を身につけることです。
そのため、学習する際には、次のことを意識してください。
• 何ができるか:機能や操作方法を理解する
• なぜ必要なのか:その機能が生まれた背景や目的を理解する
• どのような仕組みなのか:根底にある原理や構造を理解する
• どこで応用できるか:実際の業務や別のツールに活用する
個々の機能を覚えることも大切ですが、それ以上に、それぞれの機能がどのようにつながっているのかを理解することが重要です。
技術やツールは時代とともに変化します。しかし、情報を整理すること、課題を解決すること、仕組みを設計することといった本質的な考え方は変わりません。
Excelを使える人から、Excelを活用して仕事を改善できる人へ。
そして、Excelで身につけた考え方を、Power BIや生成AIなど、さまざまな技術へと応用できる人を目指しましょう。
はじめに:Excelは表計算ソフト
Excelは、セルに入れた数値や文字を、計算・集計するための表計算ソフトです。この編では、Excelを「データを入れて、計算して、見せる道具」として使いこなす方法を、やりたいこと別に学びます。
0-1. セルと範囲
• 表はセルの集まりです。縦が列(A、B、C…)、横が行(1、2、3…)で、セルの場所は「B3」のように表します
• 複数のセルをまとめて指すのが範囲です。B2:B10 は、B2からB10までを表します
• 別のシートのセルは、売上明細!B2:B10 のようにシート名を付けて指します
• 列全体は B:B のように指します。行を追加しても自動で含まれるので、この編ではよく使います
0-2. データ型
セルの中身には種類(データ型)があります。見た目が同じでも中身が違うと、計算や集計が合わなくなります。
型
例
覚えておくこと
数値
1200
標準では右寄せで表示される。計算に使える
日付
2026/4/1
中身は数値(シリアル値)で、書式で日付の見た目にしている
文字列
営業部
標準では左寄せ。数字に見える文字列(001、電話番号など)がある
コラム:条件に使う記号(比較演算子)
IFやSUMIFSなど、条件を書く場面で何度も出てくる記号です。2つの値を比べて、結果を「TRUE(正しい)」か「FALSE(正しくない)」で返します。
記号
意味
例
結果
=
等しい
=B2="営業部"
B2が営業部ならTRUE
<>
等しくない
=B2<>"営業部"
営業部以外ならTRUE
>
より大きい
=E2>100000
10万円を超えたらTRUE(ちょうど10万円はFALSE)
>=
以上
=E2>=100000
10万円以上ならTRUE
<
より小さい(未満)
=E2<100000
10万円未満ならTRUE
<=
以下
=E2<=100000
10万円以下ならTRUE
覚えておくこと
• 「=」には2つの役割があります。セルの先頭の「=」は「ここから数式」の合図で、数式の中の「=」は「等しいか」の比較です
• 記号は半角で入力します。全角(=、>など)は使えません
• 「以上」「以下」は、イコールが後ろに付きます(>=、<=)。=> は誤りです
• SUMIFSなどの条件欄では、記号と値を文字列として書きます(">=100000"、"<>営業部")。セルの値と組み合わせるときは、& でつなぎます(">="&B1)
• 「空白かどうか」は、=B2="" で判定できます
• 条件を組み合わせるときは、AND関数・OR関数を使います(D章では、* が「かつ」、+ が「または」として登場します)
• 「より大きい」と「以上」の違いは、境界の値(ちょうど10万円など)で結果が変わります。E章の練習問題で確かめます
0-3. 1行が何を表すか
同じ「表」でも、役割は2種類あります。
• 見せる表(報告書):人が読むための表。結合セル、小計行、空白行、月が横に並ぶ形などがあります
• 扱う表(データ):関数やピボットテーブル、Power Queryが読むための表。1行が1つの出来事(1回の販売、1件の受注など)、1列が1つの項目を表します
見せる表とデータの表 · 2種類の表
左は人が読むための表、右は関数やピボットテーブルが読むための表です。
Excelはどちらも作れます。ただし、集計や自動化の元になるのは「扱う表」です。この見方は、後半のデータベースの章で「表をどう設計するか」を学ぶときの土台になります。
0-4. この編の進め方
この編は、関数名ではなく「やりたいこと」から探せるようにしてあります。
節
やりたいこと
A
集計する
B
別の表から探して持ってくる
C
2つの表を照合する(重複・差分)
D
絞り込み・並べ替え・一覧を作る
E
判定・分類する
F
文字や日付を加工する
G
比べる・割合を見る
H
AIで関数を作る・エラーを直す
各節は「やりたいことと型 → 基本のやり方 → 関数の紹介 → 応用 → 留意事項 → 練習問題」の順で進みます。関数の書き方を暗記する必要はありません。大切なのは、各節の「型」を自分の言葉で言えることです。
この編の前提:XLOOKUP、FILTER、UNIQUEなどを使います。これらはMicrosoft 365やExcel 2021以降で使える関数です。受講者の環境で使えるかは、事前に確認してください。サンプルのデータ(列名・シート名)は仮のものです。
A. 集計する
A-1. やりたいことと完成イメージ
売上明細から、次の3つの表を作ります。
• 部署ごとの売上合計
• 月ごとの売上合計
• 部署 × 月のクロス集計
使うデータ(仮):シート「売上明細」のA〜E列(A:日付、B:担当部署、C:商品、D:数量、E:売上金額)。1行目が見出しで、2行目からが明細です。1行が「1回の販売」を表します。集計表は別のシート「集計」に作ります(同じシートに作ると、列全体を指定した式が自分自身を含んでしまうためです)。
この節の型
集計は、次の3つの穴埋めで考えます。
1. どの列で区切るか(例:担当部署)
2. どの列を計算するか(例:売上金額)
3. どう計算するか(合計・件数・平均)
この3つが言えれば、関数もピボットも同じ考え方で作れます。
集計の型 · 3つの穴と結果
3つの穴が決まれば、関数でもピボットテーブルでも、同じように作れます。
A-2. 基本のやり方(まず手作業で)
1. 明細をフィルターで「営業部」だけに絞る
2. 売上金額の列を選び、画面下のステータスバーで合計を見る
3. 結果を別の場所に書き留める
部署が10個あれば、これを10回繰り返します。この「絞る → 足す → 書き留める」の繰り返しを自動化するのが、これから紹介する関数とピボットテーブルです。
A-3. 関数の紹介
SUMIFS(条件に合うものを合計)
読み方は「何を足す・どこを見て・何と一致したら」です。
=SUMIFS(売上明細!$E:$E, 売上明細!$B:$B, $A2)
A2に部署名を置き、下にコピーすると、部署ごとの合計ができます。条件は2つ目以降を追加でき、1つだけでも使えるため、まずSUMIFSだけ覚えれば十分です。
売上明細!$E:$E は「売上明細シートのE列全体」を表します。列全体で指定しておくと、明細に行を追加しても自動で集計に含まれます。$ は、式をコピーしても指定がずれないようにするための記号です。
COUNTIFS(件数)/ AVERAGEIFS(平均)
=COUNTIFS(売上明細!$B:$B, $A2)
=AVERAGEIFS(売上明細!$E:$E, 売上明細!$B:$B, $A2)
月ごとの集計(日付の範囲を条件にする)
A列に「月初日」(例:2026/4/1)を置き、「その月初以上、翌月初未満」で挟みます。
=SUMIFS(売上明細!$E:$E, 売上明細!$A:$A, ">="&$A2, 売上明細!$A:$A, "<"&EDATE($A2,1))
クロス集計(部署 × 月)
行見出しに部署、列見出しに月初日を置き、1つの式を全体にコピーします。$の位置が、コピーしても見出しを指し続けるための工夫です。
=SUMIFS(売上明細!$E:$E, 売上明細!$B:$B, $A2, 売上明細!$A:$A, ">="&B$1, 売上明細!$A:$A, "<"&EDATE(B$1,1))
ピボットテーブル(関数を書かない方法)
「区切る列」を行や列に、「計算する列」を値にドラッグします。型の3つの穴埋めを、そのまま画面で置いていく操作です。
• 形を変えながら眺める → ピボット
• 決まったレイアウトの報告書を毎月作る → 関数
• 注意:ピボットは元データを変えたら「更新」が必要。関数は自動で再計算される
GROUPBY / PIVOTBY(新しい関数)
利用できる環境であれば、1つの式で集計表を作れます。受講者のExcelで使えるかは事前に確認してください。範囲はデータの行数に合わせて指定します(自動で追従させる方法は、後の章のテーブルで扱います)。
=GROUPBY(売上明細!B2:B1001, 売上明細!E2:E1001, SUM)
AIに頼む場合
型の3つの穴(区切る列・計算する列・計算方法)と、表の列名を伝えるだけで、式は作ってもらえます。ここで大切なのは式の書き方ではなく、3つの穴を自分で言葉にできることです。
A-4. 別の問題への応用
同じ型に当てはめてみます。
やりたいこと
区切る列
計算する列
計算
担当者別の受注件数
担当者
受注ID
件数(COUNTIFS)
商品別の平均売上
商品
売上金額
平均(AVERAGEIFS)
月別・商品別の販売数量
商品 × 月
数量
合計(SUMIFS)
考えてみよう:「商品別の平均単価」を、単価列のAVERAGEIFSで出すと正しいでしょうか?(数量が違う販売を同じ重さで平均してよいか。金額の合計 ÷ 数量の合計との違いを比べてみる)
A-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
結果が0になる
条件と明細で、数値と文字列が不一致/全角半角・前後のスペース違い
条件の値と明細の値を、同じ見た目でも別物でないか確認。表記を統一する
月別で拾えない
日付が文字列として入っている/時刻が付いている
日付が右寄せか、数値に直せるかを確認
合計が合わない
小計行が範囲に入っている(二重計上)/空白行・結合セルで範囲が切れている
明細は「1行1事実」に直す。小計は別の表で作る
#VALUE ! が出る
合計範囲と条件範囲の大きさが違う
範囲の行数を揃える(列全体で指定するとずれにくい)
行を追加したら集計に反映されない
範囲を$E$2:$E$100のように固定している
列全体($E:$E)で指定する。範囲を自動で追従させる方法は、後の章(テーブル)で扱う
ピボットの数字が古い
更新していない
「更新」を押す
月が横に並んだ表で集計できない
1行1事実になっていない
縦に並べ直す(Power Queryで自動化する方法は後の章で扱う)
検算の型:集計表の総合計が、明細全体の合計と一致するかを必ず確認します。一致しない場合は、どこかで拾えていない行があるサインです。
A-6. 練習問題
1. 部署ごとの売上合計・件数・平均を出す
2. 月ごとの売上合計を出す
3. 部署 × 月のクロス集計を作る
4. あえて詰まる課題:日付が文字列の行と表記ゆれが入った明細で月別集計を作り、全体合計と一致しない原因を自力で突き止める
5. 同じ集計をSUMIFSとピボットの両方で作り、結果が一致するか比べる
A-7. この節の振り返り
• 集計は「区切る列・計算する列・計算方法」の3つの穴で考える
• 関数とピボットは、同じ型を別の形で実行している
• 合わないときは、データの形(粒度・データ型・表記ゆれ)を疑う
• 最後は、総合計との突き合わせで検算する
B. 別の表から探して持ってくる
B-1. やりたいことと完成イメージ
売上明細の「商品」をもとに、別の表「商品マスタ」から「カテゴリ」と「単価」を探して、明細の横に追加します。
使うデータ(仮):シート「商品マスタ」のA列:商品、B列:カテゴリ、C列:単価。売上明細の商品とマスタの商品を結びつける列を「キー」と呼びます。
この節の型
「キーで突き合わせて、欲しい列を取る」。次の3つの穴で考えます。
1. 何を探すか(明細の商品)
2. どこから探すか(マスタのキー列)
3. 何を持ってくるか(マスタの欲しい列)
B-2. 基本のやり方(まず手作業で)
1. 明細の商品名(例:A商品)を見る
2. マスタの商品列から、同じ名前を探す
3. 同じ行のカテゴリと単価を、明細に書き写す
明細が1000行あれば、これを1000回繰り返します。この「探して写す」を自動化するのが、これから紹介する関数です。
B-3. 関数の紹介
XLOOKUP(探して、同じ行の別の列を返す)
=XLOOKUP($C2, 商品マスタ!$A:$A, 商品マスタ!$C:$C, "未登録")
読み方は「C2の商品を、マスタのA列から探して、見つかった行のC列(単価)を返す。なければ『未登録』」です。3つの穴が、そのまま引数の順番になっています。
複数の列をまとめて持ってくる
戻り値の範囲に複数の列を指定すると、結果が右にあふれて表示されます(スピル)。
=XLOOKUP($C2, 商品マスタ!$A:$A, 商品マスタ!$B:$C, "未登録")
数式の右側のセルが空いている必要があります。
複数の条件で探す
部署と商品の組み合わせで単価が決まる場合は、条件を & でつないで1つのキーにします。
=XLOOKUP($B2&"|"&$C2, 料金表!$A$2:$A$100&"|"&料金表!$B$2:$B$100, 料金表!$C$2:$C$100, "なし")
間の「|」は、別の組み合わせが同じ文字列になるのを防ぐための区切りです。
VLOOKUPとの違い
古いファイルでは、VLOOKUPがよく出てきます。読めるようにしておきましょう。
=VLOOKUP($C2, 商品マスタ!$A:$C, 3, FALSE)
VLOOKUPは、探す列が表の左端にある必要があります。また、戻す列を番号(3列目)で指定するため、列を挿入するとずれます。最後のFALSE(完全一致)も書き忘れやすい点です。XLOOKUPは、この3つの弱点がありません。
AIに頼む場合
3つの穴(何を探す・どこから探す・何を持ってくる)と、見つからないときにどうしたいかを言葉にして伝えます。
B-4. 別の問題への応用
やりたいこと
何を探す
どこから探す
何を持ってくる
担当者コードから担当者名を出す
担当者コード
担当者表のコード列
担当者名
郵便番号から住所を出す
郵便番号
住所表の郵便番号列
住所
部署と商品から単価を出す
部署+商品
料金表のキー(組み合わせ)
単価
売上金額から手数料率を出すように「範囲」で探す場合は、E章の区分表を使います。
B-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
「未登録」(#N/A)になる
検索値とキー列で、表記が違う(全角半角、前後の空白、数値と文字列)
両方の値を見比べて、表記をそろえる。空白はTRIMで除く
単価が0になる
マスタ側の戻り値のセルが空欄
マスタを補う。空欄を参照すると0が返る
別の行の値が返る
マスタ側に同じキーが重複している(最初の1件だけ返る)
C章の方法で、キーの重複を確認する
マスタを直したら過去の値も変わった
数式は、常に今のマスタを見ている
過去の値を残したいときは、値として貼り付けるか、期間つきの料金表にする
古いExcelで動かない
XLOOKUPはMicrosoft 365やExcel 2021以降の関数
環境を確認し、必要ならVLOOKUPで代替する
検算の型:結果の列に「未登録」がいくつあるかを数えます。
=COUNTIF(F:F, "未登録")
0であること、または、一覧にして原因を確認できることが目安です。
B-6. 練習問題
1. 明細に、カテゴリと単価の列を追加する
2. 単価×数量を計算し、売上金額の列と一致するか比べる
3. XLOOKUPとVLOOKUPで、同じ結果を出す
4. あえて詰まる課題:マスタの商品名の末尾に空白が入ったデータで「未登録」が出る。原因を自力で見つけて直す
B-7. この節の振り返り
• 「何を探す・どこから探す・何を持ってくる」の3つの穴で考える
• キーの表記がそろっていることが、いちばん大切
• 結果の「未登録」の数を数えて検算する
C. 2つの表を照合する(重複・差分)
C-1. やりたいことと完成イメージ
2つの顧客リスト(旧リスト「名簿A」と新リスト「名簿B」)を比べて、次の3つに分けます。
• 両方にある
• Aにだけある(Bには無い)
• Bにだけある(Aには無い)
あわせて、同じリストの中で重複して登録されているものも見つけます。
使うデータ(仮):シート「名簿A」「名簿B」の、それぞれA列:顧客ID、B列:顧客名。
この節の型
「キーで突き合わせて、あり/なしを判定する」。B章と同じ「突き合わせ」ですが、取り出す値ではなく「あるかどうか」を使います。
照合の型 · 表Aと表Bの3つの領域
「Aだけ」「両方」「Bだけ」の3つに分けられれば、照合はできたといえます。
C-2. 基本のやり方(まず手作業で)
1. 名簿Aの1件目の顧客IDを見る
2. 名簿Bで検索(Ctrl+F)して、あるかどうかを確かめる
3. 結果をメモする
件数が多いと、見落としが出ます。これを関数に任せます。
C-3. 関数の紹介
COUNTIF(その値が何件あるかを数える)
=IF(COUNTIF(名簿B!$A:$A, $A2) > 0, "あり", "なし")
名簿Aの各IDが、名簿Bに何件あるかを数え、1件以上なら「あり」とします。名簿Bの各IDが名簿Aにあるかも、同じ形で判定できます。
• 両方にある:どちらの判定も「あり」
• Aにだけある:名簿Aの判定が「なし」
• Bにだけある:名簿Bの判定が「なし」
同じリストの中の重複
=IF(COUNTIF($A:$A, $A2) > 1, "重複", "")
自分自身も1件と数えるので、2件以上なら重複です。
FILTER(結果を一覧で取り出す)
両方にあるIDの一覧:
=FILTER(名簿A!A2:A101, ISNUMBER(MATCH(名簿A!A2:A101, 名簿B!A2:A101, 0)), "なし")
AにだけあるIDの一覧:
=FILTER(名簿A!A2:A101, ISNA(MATCH(名簿A!A2:A101, 名簿B!A2:A101, 0)), "なし")
MATCHは、見つかった位置を返します。見つからないと#N/Aになります。ISNUMBERは「位置が返った=ある」、ISNAは「#N/A=ない」を判定します。範囲は、データの行数に合わせます(空白行を含めない)。
UNIQUE(重複をまとめた一覧)
=UNIQUE(名簿A!A2:A101)
条件付き書式(見た目で確認する)
ホーム → 条件付き書式 → セルの強調表示ルール → 重複する値、の順に選ぶと、重複を色で確認できます。見た目だけの機能で、集計には使えません。
AIに頼む場合
「2つの表」「キーになる列」「知りたいこと(両方にある・片方にだけある)」の3つを言葉にして伝えます。
C-4. 別の問題への応用
やりたいこと
表A
表B
キー
入金漏れを見つける
請求一覧
入金一覧
請求番号
欠席者を見つける
申込者名簿
出席者名簿
社員番号
先月と今月の在庫の差を見る
先月の在庫リスト
今月の在庫リスト
商品コード
C-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
あるはずなのに「なし」になる
表記ゆれ(全角半角、前後の空白、数値と文字列)
表記をそろえる。空白はTRIMで除く
16桁以上の数字(カード番号など)が、違うのに「あり」と出る
COUNTIFは、数字の文字列を15桁までの数値として扱う
XLOOKUPで探す
「*」「?」を含む値が合わない
COUNTIFがワイルドカードとして扱う
XLOOKUPで探す
大文字小文字の違いを見分けたい
COUNTIF・MATCH・XLOOKUPは区別しない
区別が必要ならEXACT関数を使う
一覧に空白行が混ざる
FILTERの範囲に空白行を含めている
範囲をデータの行数に合わせる
結果の件数が合わない
キーが重複している
先に、同じリストの中の重複を確認する
検算の型:キーに重複がなければ、次の2つが成り立ちます。
• 両方にある件数 + Aにだけある件数 = Aの件数
• 両方にある件数 + Bにだけある件数 = Bの件数
C-6. 練習問題
1. 名簿Aの各IDについて、名簿Bに「あり/なし」を判定する
2. 両方にあるID、Aにだけあるものの一覧をFILTERで取り出す
3. 名簿Aの中の重複を見つける
4. あえて詰まる課題:表記ゆれ(全角の数字、末尾の空白)を含む名簿で、件数の検算が合わない。原因を自力で見つける
C-7. この節の振り返り
• 照合は「キーで突き合わせて、あり/なしを判定する」型で考える
• B章(値を取る)とC章(あるかを見る)は、同じ「突き合わせ」の使い方違い
• 件数の検算で、漏れや重複に気づく
D. 絞り込み・並べ替え・一覧を作る
D-1. やりたいことと完成イメージ
売上明細から、次の3つを作ります。
• 「営業部で10万円以上」の販売だけを抜き出した一覧
• 売上金額の大きい順に並べた一覧
• 部署の一覧(重複なし)
この節の型
「条件で抜き出し、順に並べる」。抜き出す条件、並べる基準、重複をまとめるかの3点を決めます。
D-2. 基本のやり方(まず手作業で)
• 絞り込み:データ → フィルター。見出しのボタンから条件を選びます
• 並べ替え:データ → 並べ替え。表全体を選んでから実行します(一部の列だけだと、行がずれます)
• 重複の削除:データ → 重複の削除。元のデータが変わってしまうので、コピーした表で行います
元のデータが変わったら、また手作業でやり直しです。関数なら、元のデータに追従します。
D-3. 関数の紹介
FILTER(条件に合う行を抜き出す)
=FILTER(売上明細!A2:E1001, (売上明細!B2:B1001="営業部")*(売上明細!E2:E1001>=100000), "該当なし")
読み方は「この範囲から、この条件に合う行を取り出す。なければ『該当なし』」です。条件の「かつ(AND)」は *、「または(OR)」は + でつなぎます。
(売上明細!B2:B1001="営業部")+(売上明細!B2:B1001="開発部")
SORT(並べ替える)
=SORT(FILTER(…), 5, -1)
2つ目が「何列目で並べるか」、3つ目が順序(1が昇順、-1が降順)です。上の例は、5列目(売上金額)の大きい順です。
UNIQUE(重複をまとめた一覧)
=SORT(UNIQUE(売上明細!B2:B1001))
部署の一覧を、重複なしで、並べ替えて作ります。
スピル(結果があふれて表示される)
これらの関数は、先頭のセルに数式を1つ入れるだけで、結果が下や右に広がって表示されます。広がった範囲全体を指すには、先頭セルの後ろに # を付けます(例:A2#)。
A章の集計表とつなぐ
A2に部署の一覧を =SORT(UNIQUE(売上明細!B2:B1001)) で作り、B2に次の式を入れます。
=SUMIFS(売上明細!$E:$E, 売上明細!$B:$B, A2#)
部署が増えても、集計表が自動で増えます。
AIに頼む場合
「どの表から」「どんな条件で」「どう並べるか」「重複をまとめるか」を言葉にして伝えます。
D-4. 別の問題への応用
やりたいこと
条件・基準
使う関数
今月分だけを抜き出す
日付が月初以上、翌月初未満
FILTER
売上の上位10件を出す
売上金額の大きい順に10件
SORTとTAKE(Microsoft 365)
取引のあった商品の一覧を作る
重複をまとめる
UNIQUE
D-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
#SPILL ! が出る
結果が広がる先に、値や結合セルがある
広がる範囲を空ける
一覧の最後に0や空白が混ざる
範囲に空白行を含めている
範囲をデータの行数に合わせる
並べ替えたら行がずれた
表の一部の列だけを選んで並べ替えた
表全体を選んでから並べ替える
元データを変えたら結果も変わった
関数は常に元データを見ている
結果を残したいときは、値として貼り付ける
古いExcelで動かない
FILTER・SORT・UNIQUEは、Excel 2021以降(2019以前は非対応)
環境を確認し、必要ならフィルター機能で代替する
検算の型:抜き出した件数が、COUNTIFSで数えた件数と一致するかを確認します。
D-6. 練習問題
1. 営業部の販売だけを抜き出す
2. 営業部で10万円以上の販売を、売上金額の大きい順に並べる
3. 部署の一覧を作り、A章の集計表につなぐ
4. あえて詰まる課題:範囲に空白行が含まれた状態で一覧を作り、混ざった0の原因を突き止める
D-7. この節の振り返り
• 「条件・並べ方・重複のまとめ方」の3点を決めてから、関数を選ぶ
• 関数は元データに追従する。手作業は、やり直しが必要
• 抜き出した件数で検算する
E. 判定・分類する
E-1. やりたいことと完成イメージ
次の3つを作ります。
• 売上金額に応じた「S・A・B・C」のランクを、明細に付ける
• 期限を過ぎた案件を、赤く目立たせる
• 入力ミスを防ぐ、選ぶだけの入力欄(ドロップダウン)
この節の型
「条件に応じてラベルを付ける」。条件は、式の中に埋め込まずに、表に出して管理します。条件が変わっても、表を直すだけで済むためです。
E-2. 基本のやり方(まず手作業で)
まず、条件を言葉にします。
• 20万円以上なら S
• 10万円以上なら A
• 5万円以上なら B
• それ未満なら C
明細の1行ごとに、この順番で当てはめていきます。上から順に確かめて、最初に当てはまったところで決める、という考え方です。
E-3. 関数の紹介
IF(2つに分ける)
=IF(E2>=100000, "高", "低")
IFS(3つ以上に分ける)
=IFS(E2>=200000, "S", E2>=100000, "A", E2>=50000, "B", TRUE, "C")
上から順に判定し、最初に当てはまったものを返します。最後の TRUE は「どれにも当てはまらなければ」の受け皿です。
AND / OR(条件を組み合わせる)
=IF(AND(B2="営業部", E2>=100000), "対象", "")
ANDは「すべて満たす」、ORは「どれかを満たす」です。
区分表とXLOOKUP(条件を表に出す)
シート「区分表」に、A列:下限(0、50000、100000、200000)、B列:ランク(C、B、A、S)を作ります。
=XLOOKUP($E2, 区分表!$A$2:$A$5, 区分表!$B$2:$B$5, , -1)
最後の -1 は「完全一致がなければ、次に小さい値を使う」という指定です。ランクの基準が変わったら、区分表だけを直します。
条件付き書式(見た目で目立たせる)
期限(C列)を過ぎた行を赤くする例です。対象の範囲を選び、ホーム → 条件付き書式 → 新しいルール → 「数式を使用して、書式設定するセルを決定」を選んで、次の式を入れます。
=$C2<TODAY()
見た目だけの機能で、集計には使えません。集計に使いたい判定は、列として持ちます。
入力規則(入力ミスを防ぐ)
データ → データの入力規則 → 入力値の種類で「リスト」を選び、元の値に選択肢の範囲を指定します。表記ゆれを、入口で防げます。
AIに頼む場合
判定の条件を、上から順に、箇条書きで言葉にして伝えます。条件の順番と、どれにも当てはまらない場合の扱いも、一緒に伝えます。
E-4. 別の問題への応用
やりたいこと
判定の元
結果のラベル
納期遅れを見つける
納期と今日の日付
遅延 / 期限内
年齢層に分ける
年齢
20代、30代…
合否を判定する
点数と合格点
合格 / 不合格
E-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
判定が思った結果と違う
IFSの条件の順番が悪い(小さい順に並べている)
条件は、厳しいものから先に書く
#N /A が出る
どの条件にも当てはまらない
最後に TRUE の受け皿を入れる
数値の判定が合わない
数値が文字列として入っている
データ型を確認する(はじめに 0-2)
空白のセルが「0」扱いになる
空白は、比較では0として扱われる
空白を先に判定する(B2="" を最初の条件にする)
条件付き書式の結果を集計したい
条件付き書式は見た目だけ
判定結果を列として作る
検算の型:ランクごとの件数を合計して、全体の件数と一致するかを確認します。
E-6. 練習問題
1. 売上金額をもとに、S・A・B・Cのランクを付ける(IFS)
2. 同じランク付けを、区分表とXLOOKUPで作る
3. ランクごとの件数を、A章の方法で集計する
4. あえて詰まる課題:区分表の下限に5万円ちょうどの売上を入れ、どのランクになるかを確かめる。境界の扱いを自分の言葉で説明する
E-7. この節の振り返り
• 条件は、言葉にして、順番を決めてから式にする
• 条件は式に埋め込まず、区分表に出す
• 判定の結果は列として持ち、集計に使えるようにする
F. 文字や日付を加工する
F-1. やりたいことと完成イメージ
他の部署から集めたデータや、システムから出力したデータを、集計できる形に直します。
• 表記ゆれを直す(全角半角、余分な空白、「(株)」と「株式会社」)
• コードを分ける(「営業-001」を「営業」と「001」に)
• 日付から、月初日や年月を作る(A章の月別集計のキーになる)
この節の型
「決まった規則で、分解・変換する」。元の列は残し、新しい列(作業列)に結果を作ります。
F-2. 基本のやり方(まず手作業で)
• 置換:Ctrl+H で、文字を別の文字に置き換えます
• 区切り位置:データ → 区切り位置で、区切り文字を使って列を分けます
• フラッシュフィル:1行目の結果を手で入力し、次のセルで Ctrl+E を押すと、同じ規則で残りを埋めてくれます
手作業は手軽ですが、データが更新されるたびにやり直しになります。繰り返す加工は、関数や、後の章のPower Queryに任せます。
F-3. 関数の紹介
やりたいこと
関数
例
余分な空白を除く
TRIM
=TRIM(A2)
全角を半角にする
ASC
=ASC(A2)
文字を置き換える
SUBSTITUTE
=SUBSTITUTE(A2,"(株)","株式会社")
先頭や途中から取り出す
LEFT / MID / RIGHT
=LEFT(A2,2)
区切り文字で分ける
TEXTSPLIT
=TEXTSPLIT(A2,"-")
文字をつなぐ
&、TEXTJOIN
=A2&"-"&B2
文字列を数値にする
VALUE
=VALUE(A2)
日付の部品を取り出す
YEAR / MONTH / DAY
=YEAR(A2)
月初日を作る
EOMONTH
=EOMONTH(A2,-1)+1
月末日を作る
EOMONTH
=EOMONTH(A2,0)
年月の文字列を作る
TEXT
=TEXT(A2,"yyyy-mm")
覚えておくこと
• TRIMは、全角スペースは除きません。全角スペースは、=SUBSTITUTE(A2," "," ") で半角に直してからTRIMにかけます
• EOMONTH(日付, 0) は「その月の末日」、EOMONTH(日付, -1) は「前月の末日」です。これに1を足すと、その月の月初日になります
• TEXTの結果は、見た目が日付でも「文字列」です。集計のキーには、月初日(日付)を使うと安全です
AIに頼む場合
「加工前の例」と「加工後の例」を、2〜3行ずつ見せるのが一番伝わります。
F-4. 別の問題への応用
やりたいこと
考え方
例
氏名を姓と名に分ける
空白で分ける
=TEXTSPLIT(A2," ")
電話番号のハイフンを除く
置き換えて空にする
=SUBSTITUTE(A2,"-","")
4月始まりの年度を出す
3か月戻して、年を取る
=YEAR(EDATE(A2,-3))
F-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
元のデータが消えた
元の列を上書きして加工した
作業列に結果を作り、元の列は残す
見た目は日付なのに、集計できない
中身が文字列になっている
標準で左寄せなら文字列。区切り位置やDATEVALUEで日付にする
先頭の0が消える(001が1になる)
数値として入力された
文字列として入力するか、表示形式を「文字列」にしてから入力する
空白を除いたのに、合わない
全角スペースが残っている
SUBSTITUTEで全角スペースを置き換える
元の列を消したら、結果が壊れた
作業列の結果が、元の列を参照している
結果を固定したいときは、値として貼り付ける(Ctrl+Alt+V → 値)
橋渡し:毎月同じ加工を繰り返すなら
毎月届くデータに、同じ加工を何列分も繰り返す場面では、関数を並べる作業が負担になります。加工の手順を記録して、更新ボタンだけで再現できる仕組みが、後の章のPower Queryです。
検算の型:加工の前後で、行数が変わっていないことと、サンプル数件の結果を、手で確認します。
F-6. 練習問題
1. 部署名の表記ゆれ(全角半角、空白)を直す
2. 「営業-001」の形のコードを、部署とコードに分ける
3. 日付から、月初日の列を作り、A章の月別集計に使う
4. あえて詰まる課題:見た目は日付なのに、月別集計で拾えないデータがある。原因を見つけて、集計できる形に直す
F-7. この節の振り返り
• 加工は「元の列を残して、作業列に結果を作る」
• 加工の前後の例を示せば、AIにも、自分にも、規則が伝わる
• 繰り返す加工は、次の段階のPower Queryに任せる
G. 比べる・割合を見る
G-1. やりたいことと完成イメージ
A章で作った部署別の集計表に、次の列を足します。
• 構成比(各部署が全体の何%か)
• 累計(上から順に足していった合計)
• 前月比(前の月から何%増減したか)
• 順位(売上の大きい順)
この節の型
「何と比べるか(分母・基準)を決める」。割合は割り算なので、何で割るのか(分母)を、言えることが一番大切です。
G-2. 基本のやり方(まず電卓で)
営業部の売上が300万円、全体が1000万円なら、300 ÷ 1000 = 0.3、つまり30%です。これを、部署の数だけ繰り返します。
G-3. 式の紹介
構成比
=B2/SUM($B$2:$B$6)
表示形式を%にします(ホーム → %ボタン)。分母の範囲に $ を付けて固定すると、下にコピーしても分母がずれません。
累計
=SUM($B$2:B2)
始点を固定し、終点だけを動かします。下にコピーすると、範囲が1行ずつ広がります。
前月比(増減率)と差額
=IF(C2=0, "", C3/C2-1)
=C3-C2
1つ目は、前月が0のときに計算せず空白にします(0で割るとエラーになるため)。2つ目は、増減した金額です。
順位
=RANK.EQ(B2, $B$2:$B$6, 0)
最後の 0 は、大きい順の指定です。
AIに頼む場合
「何を、何で割りたいか」(分子と分母)を言葉にして伝えます。
G-4. 別の問題への応用
やりたいこと
分子
分母
目標達成率
実績
目標
前年同月比
今年の売上
前年同月の売上
部署内の商品別構成比
部署内のその商品の売上
その部署の売上合計(SUMIFSで求める)
G-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
構成比の合計が100%にならない
分母が全体の合計になっていない、範囲がずれている
分母の範囲と $ の位置を確認する
割合が「0.25」と表示される
表示形式が%になっていない
表示形式を%にする(中身は0.25のまま)
#DIV /0! が出る
分母が0
IFで、分母が0のときの表示を決める
累計の範囲がずれる
始点を固定していない
始点に $ を付ける
割合の平均が、全体の割合と合わない
割合の平均は、全体の割合とは別物
分子の合計 ÷ 分母の合計で求める(A章のA-4と同じ考え方)
前年同月比のように、日付をまたいで比べる計算は、後の章のPower Pivot(時間インテリジェンス)で、より簡単にできるようになります。
検算の型:構成比の合計が100%になること、累計の最後が全体の合計と一致することを確認します。
G-6. 練習問題
1. 部署別の売上に、構成比と順位を付ける
2. 月別の売上に、累計と前月比を付ける
3. 部署内の商品別構成比を求める(SUMIFSを2つ使う)
4. あえて詰まる課題:前月の売上が0の月がある表で前月比を出し、エラーの原因と対処を自分で考える
G-7. この節の振り返り
• 割合は、まず「分母は何か」を決める
• 平均や割合は、平均の平均にしない。合計同士で割る
• 構成比の合計と累計の最後で検算する
H. AIで関数を作る・エラーを直す
H-1. やりたいことと完成イメージ
関数の書き方や、エラーの原因探しを、AI(Copilotなど)に手伝ってもらいます。関数の細かい書式やエラーの直し方は、AIが得意です。人の仕事は、「何を求めているか」を言葉にすることです。
この節の型
「AIに渡すのは『業務の見方』」。A〜G章で身につけた型(何で区切り、何を計算するか、キーは何か、1行は何を表すか)を言葉にできれば、AIは役に立ちます。
AIとの進め方 · 4ステップと戻り道
合わないときは、式を直す前に「何が違うか」を言葉にして、②に戻ります。
H-2. 基本のやり方:人がやること、AIに任せること
• 人がやること:表の構造を説明する。やりたいことを、型の穴埋めで言葉にする。出てきた結果を検算する
• AIに任せること:関数の書式、細かい引数、エラーメッセージの読み解き
H-3. 指示文の4点セット
項目
書くこと
例
1. 表の構造
シート名、列名、データ型、1行が何を表すか、データの範囲
「シート『売上明細』のA〜E列。日付、担当部署、商品、数量、売上金額。1行が1回の販売。2行目から」
2. やりたいこと
区切る列、計算する列、計算方法(型の穴埋め)
「担当部署ごとに、売上金額を合計したい」
3. 出力先
どのセルに、どんな形で出したいか
「シート『集計』のA2に部署名、B2に合計」
4. 環境
Excelのバージョン、使える機能
「Microsoft 365のExcel。XLOOKUPは使える」
例:
シート「売上明細」のA〜E列(日付、担当部署、商品、数量、売上金額)に、2行目から、1回の販売ごとに1行ずつ入っています。担当部署ごとの売上金額の合計を、シート「集計」のB2に出す数式を教えてください。A2に部署名が入っています。Microsoft 365のExcelです。
H-4. エラーを直してもらうときの4点セット
項目
書くこと
1. エラーの内容
セルに出ているメッセージ(#N/A、#SPILL! など)
2. 入れた数式
そのままコピーして貼る
3. 期待した結果と、実際の結果
「部署ごとの合計が出るはずが、全体の合計が出る」
4. サンプルデータ
数行分。個人情報や機密は伏せる
H-5. 出力の検証(検算の型)
AIの答えは、そのまま信じず、必ず確かめます。
1. 全体の合計と一致するか(A章の検算)
2. 1件を、手計算で確かめる
3. 境界の値(月初・月末、0、空白)で試す
4. エラーの行や、空白の行がないか見る
5. 数式を選び、参照している範囲が思った場所かを確かめる
結果が違っていたら、数式の直し方を考える前に、「何が違うか」を言葉にしてAIに返します(例:「部署ごとではなく、全体の合計になっている」)。
H-6. AIが間違えやすいこと
起こりがちなこと
対処
使えない関数や、バージョンにない関数を出す
環境(バージョン)を、最初に伝える
範囲がずれている
表の構造と、データの範囲を具体的に伝える
日付や数値が文字列であることを知らない
データ型を、先に伝える
全角半角や空白の違いを見落とす
サンプルデータを見せる
H-7. 社内で使うときの注意
• 機密情報や個人情報を、AIに入力してよい範囲は、社内のルールに従います
• 迷うときは、実データを使わず、ダミーのデータで質問します
H-8. 練習問題
1. A章の部署別集計を、4点セットの指示文でAIに頼み、検算で正しさを確認する
2. わざとエラーが出る数式を作り、4点セットでAIに直してもらう
3. AIの答えが期待と違ったときに、「何が違うか」を言葉にして伝え直す
4. あえて詰まる課題:日付が文字列のデータで、AIが出した月別集計が合わない。原因を、まず自分で見つけ、その後にAIへ伝える
H-9. この編の振り返り
• A〜Gの型は、AIへの指示文の中身そのもの
• 細部はAIに任せてよい。ただし、結果を検算できる人でいる
• 繰り返す加工や、より大きなデータは、次の章(テーブル、Power Query)で扱う
テーブル章:範囲の管理をExcelに任せる
Excel編では、売上明細!$E:$E のように、セルの範囲を自分で指定してきました。この章では、データのかたまりに名前を付けて、範囲の管理をExcelに任せる「テーブル」機能を学びます。Excel編で「後の章で扱う」と書いた課題は、ここで解決します。Power QueryやPower Pivotを使うときの入口にもなります。
T-1. やりたいことと完成イメージ
• 売上明細に行を追加しても、集計表の式を直さずに、自動で集計に含まれる
• 式を「E列」ではなく「売上金額」という名前で読めるようにする
• 行が増えても、式や書式が自動で次の行にも入る
使うデータ(仮):シート「売上明細」のA〜E列(日付・担当部署・商品・数量・売上金額)を、テーブル「tbl売上」にします。
この節の型
「データのかたまりに名前を付け、範囲の指定と更新をExcelに任せる」。次の3点を決めます。
1. どこからどこまでを、1つのかたまりと見るか(1行1事実の表)
2. そのかたまりを、何という名前で呼ぶか
3. どの列を使うか(列名で指す)
範囲の指定方法 · セル範囲とテーブルの比較
左のようにセル番地で固定すると、行を足したときに範囲がずれます。右のようにテーブルで指定すると、範囲を直す必要がありません。
T-2. 基本のやり方(Excel編の方法の限界)
Excel編では、次の2つの方法で範囲を指定していました。
• $E$2:$E$100 のようにセル番地で固定する:行を足すと、範囲の外になってしまう
• $E:$E のように列全体で指定する:行が増えても追従するが、見出しや下の空白も範囲に含まれるので、GROUPBYやFILTERのような関数では、余計な行が混ざる
どちらも、「どこまでがデータか」をExcelが知らないことが原因です。テーブルは、それをExcelに教える機能です。
T-3. 機能の紹介
テーブルにする
1. 表の中のセルを1つ選ぶ
2. Ctrl+T を押す
3. 「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確かめて、OKを押す
4. 「テーブルデザイン」タブで、テーブル名を tbl売上 に変える
自動で広がる
テーブルの最後の行のすぐ下に入力すると、テーブルが自動で1行広がります。
構造化参照(列名で範囲を指す)
=SUMIFS(tbl売上[売上金額], tbl売上[担当部署], $A2)
tbl売上[売上金額] は、「tbl売上の、売上金額の列」という意味です。行が増えても、この指定はデータ全体を指し続けます。数式を入力中にセルをクリックすると、自動でこの形になります。
計算列(同じ行の値を使う)
テーブルの中の列に式を入れると、列全体に自動で同じ式が入ります。同じ行の他の列は、[@列名] で指します。
=[@数量]*[@単価]
集計行とスライサー
• 集計行:テーブルデザイン → 集計行にチェックを入れると、テーブルの下に合計などの行が出ます
• スライサー:テーブルデザイン → スライサーの挿入。ボタンのクリックで、表を絞り込めます
ピボットテーブルの元データにする
テーブルを元にすると、行が増えたときに「更新」を押すだけで、ピボットテーブルに反映されます。
AIに頼む場合
テーブル名と列名を伝えると、構造化参照の形で式を作ってもらえます。「テーブル『tbl売上』の列は、日付・担当部署・商品・数量・売上金額です」と書き添えます。
T-4. 別の問題への応用:Excel編の式をテーブルにする
Excel編の式
テーブル版
=SUMIFS(売上明細!$E:$E, 売上明細!$B:$B, $A2)
=SUMIFS(tbl売上[売上金額], tbl売上[担当部署], $A2)
=XLOOKUP($C2, 商品マスタ!$A:$A, 商品マスタ!$C:$C, "未登録")
=XLOOKUP([@商品], tbl商品マスタ[商品], tbl商品マスタ[単価], "未登録")
=GROUPBY(売上明細!B2:B1001, 売上明細!E2:E1001, SUM)
=GROUPBY(tbl売上[担当部署], tbl売上[売上金額], SUM)
=FILTER(売上明細!A2:E1001, 売上明細!B2:B1001="営業部", "該当なし")
=FILTER(tbl売上, tbl売上[担当部署]="営業部", "該当なし")
• 別のシートにあるテーブルも、シート名を付けずに、テーブル名だけで指せます
• GROUPBYやFILTERで、行数の指定が要らなくなります。空白行が混ざる問題も解決します
• テーブルの中の式(計算列)では、同じ行を [@商品] のように指します
T-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
見出しが「列1」「列2」になる
見出しの行に、空白のセルがある
見出しをすべて入力する
見出しの名前が勝手に変わる
テーブルの見出しは、重複できない
見出し名をそれぞれ別の名前にする
テーブルにできない
範囲に結合セルがある
結合を解除する(はじめに 0-3の「1行1事実」)
テーブルの中で #SPILL ! が出る
テーブルの中では、FILTERやUNIQUEのように結果があふれる式が使えない
テーブルの外に作る
追加した行が集計に入らない
テーブルのすぐ下ではなく、離れた場所に入力した
テーブルの直下に入力するか、右下の角をドラッグして範囲を広げる
式が、セル番地ではなく名前で表示される
自動で構造化参照になる
仕様どおり。「数式でテーブル名を使用する」の設定で、切り替えられる
テーブルを、ふつうの表に戻したい
—
テーブルデザイン → 範囲に変換
テーブル名の付け方:スペースは使えません。「tbl売上」のように、tbl を付けておくと、他の名前と区別しやすくなります。
検算の型(追従テスト):明細の最後の行のすぐ下に1行足して、集計表の合計がその分だけ増えるかを確かめます。
T-6. 練習問題
1. 売上明細をテーブルにして、名前を tbl売上 にする
2. A章の部署別の集計を、構造化参照で書き換える
3. 明細の最後の行の下に1行足し、集計が自動で増えることを確かめる
4. 商品マスタから単価を持ってくる列を、計算列として追加する
5. あえて詰まる課題:テーブルの下に空白行を1行挟んで入力する。集計に入らない理由を調べ、直す
T-7. この節の振り返り
• テーブルは、「1行1事実の表」に名前を付けたもの
• 範囲の管理はExcelに任せ、式は列名で読む
• 行を足して確かめる「追従テスト」で検算する
• 次の章では、表を複数に分けて設計する考え方(データベースの基礎)を学びます
データベースの基礎:表を分けて、キーでつなぐ
テーブル章までで、Excelの表を「扱う表」として使えるようになりました。この章では、その表を「どう設計するか」を学びます。「はじめに」で見た「扱う表」を、さらに複数の表に分けて、つなげる考え方です。この考え方は、Power Query、Power Pivot、Power BIのすべての土台になります。
DB-1. やりたいことと完成イメージ
次のような、1枚の大きな売上表があるとします。
日付
顧客名
顧客の担当者
商品名
単価
数量
4/1
山田商事
佐藤
A商品
1,200
10
4/1
山田商事
佐藤
B商品
800
5
4/3
鈴木工業
田中
A商品
1,200
3
同じ顧客名・担当者・単価が、何度も繰り返し入っています。これには、次のような困りごとがあります。
• 山田商事の担当者が変わると、山田商事のすべての行を直す必要がある
• 入力のたびに、「山田商事」と「(株)山田商事」のような表記ゆれが起きる
• 単価を直したときに、直し漏れが起きる
完成イメージは、この1枚の表を、「売上明細」「顧客」「商品」の3つの表に分けて、IDでつなぐ形です。
この章の型
「同じ情報の繰り返しを見つけたら、表を分けて、キーでつなぐ」。次の3ステップで考えます。
1. 繰り返している情報を探す
2. それを、別の表に切り出す
3. 行を識別するID(キー)を決めて、つなぐ
表の分け方 · 1枚の表から3つの表へ
真ん中の「売上明細」が、出来事の記録(ファクト)です。上下の2つが、切り口になるマスタ(ディメンション)です。
DB-2. 基本の考え方:表計算の表とデータベースの表
• 表計算の表:セルに自由に入力でき、見た目も自由に作れる
• データベースの表:「1行が1つの出来事」「1列が1つの項目」という決まった形の表を、複数持ち、キーでつなぐ
この章で扱うのは、RDB(リレーショナルデータベース)という考え方です。「関係(リレーション)でつながった表の集まり」という意味です。Excelは表計算ソフトですが、テーブルとPower Pivotを組み合わせると、データベースのように使えます。
まず、手作業で体験します。1枚の表を見て、同じ内容が繰り返し出てくる列に、色を付けてみてください。それが、切り出す候補です。
DB-3. 用語と考え方
用語
意味
Excelでの対応
テーブル(表)
データの集まり
シートの表、テーブル機能
レコード(行)
1件のデータ
行
カラム(列・フィールド)
1つの項目
列
主キー
行を1つに特定できる列
顧客ID、商品コード
外部キー
別の表の主キーを指す列
明細の顧客ID(XLOOKUPの検索値)
主キーの条件
• 重複しない
• 空白でない
• 変わらない(名前は変わるので、主キーに向かない。IDやコードを使う)
表と表の関係
• 1対多:1人の顧客に、多数の売上がある。もっともよく使う関係
• 多対多:1つの商品が多数の注文に入り、1つの注文に多数の商品が入る。この場合は、間に「明細」の表を挟んで、1対多を2つに分ける
正規化(表を整える)
• 第1正規形:1つのセルに1つの値だけを入れる。「商品1、商品2、商品3」のような、繰り返しの列は作らない。月が横に並んだ表を縦に直すのも、この考え方(Power Queryの「列のピボット解除」で自動化できる)
• 第2正規形:主キーの一部だけで決まる列は、別の表に切り出す(例:明細の中の商品名や単価は、商品コードだけで決まる)
• 第3正規形:主キー以外の列で決まる列は、別の表に切り出す(例:顧客名から担当者が決まるなら、顧客の表に持つ)
最初は、「同じ情報の繰り返しを切り出す」だけで、ほとんどの場面は足ります。
Excelでの「つなぐ」方法
外部キーでつなぐ操作は、Excelでは3つのやり方があります。
• XLOOKUP(B章):1つの値を持ってくる
• クエリのマージ(Power Query):表と表を結合して、新しい表を作る
• リレーション(Power Pivot):表のままつないで、集計の切り口に使う
AIに頼む場合
業務の流れ(誰が、何を、いつ記録するか)を説明すると、表の分け方の案を出してもらえます。ただし、案をそのまま使わず、この章の留意事項の観点で確認します。
DB-4. 応用:明細(ファクト)とマスタ(ディメンション)
• ファクト:出来事の記録。売上明細のように、数量や金額が入り、行が増え続ける
• ディメンション:分析の切り口。顧客、商品、日付、部署など。行は比較的変わらず、属性(名前や担当者など)が入る
集計は、「ファクトの数値を、ディメンションの切り口で区切る」ことです。A章の型(区切る列・計算する列)と同じです。区切る列がディメンション側、計算する列がファクト側にあります。
表
種類
主キー
主な列
売上明細
ファクト
明細ID
日付、顧客ID、商品コード、数量
顧客
ディメンション
顧客ID
顧客名、担当者
商品
ディメンション
商品コード
商品名、単価、カテゴリ
ファクトの周りに、ディメンションが星形に並ぶこの形を、スタースキーマと呼びます。Power PivotとPower BIの、標準的なデータの形です。日付も切り口の1つで、日付の表(カレンダー)は、Power Pivotの章で作ります。
DB-5. 留意事項:うまくいかないケース
症状
主な原因
確認と対処
つないだら行が増えた、または減った
キーが重複している、表記ゆれがある
キーの重複を確認する(C章の方法)
顧客の名前をキーにしたら、同姓同名や改名で壊れた
名前は、変わる・重複する
IDやコードをキーにする
過去の単価が、マスタを直したときに変わってしまった
単価をマスタだけに持っている
その時点の価格は、明細にも持つ(設計の判断)
つなげない行がある
外部キーが空白、またはマスタにない
必須入力にする(入力規則)。マスタ側に登録する
分けすぎて、1つの集計に何枚も必要になった
正規化は目的ではなく手段
「直す場所が何か所になるか」で、分ける必要を判断する
検算の型:分ける前の行数と、売上明細の行数が一致するかを確認します。また、分けた表をつないで、元の1枚の表を再現できるか(XLOOKUPで顧客名や商品名を持ってきて、元の表と見比べる)を確かめます。
DB-6. 練習問題
1. 渡された1枚の売上表で、繰り返している情報に色を付ける
2. 3つの表に分ける設計図を書く(各表の主キーと、外部キーを書く)
3. 設計どおりにテーブルを3つ作り、明細に、XLOOKUPで顧客名と商品名を持ってくる
4. 月が横に並んだ表を、第1正規形(縦持ち)に直す方法を考える(Power Queryの章で自動化する)
5. あえて詰まる課題:顧客の担当者が変わったとき、1枚の表では何行を直すか数える。分けた表では何か所か、比べる
DB-7. この章の振り返り
• 「繰り返しを切り出して、キーでつなぐ」が、データベースの基本
• キーは、名前ではなくIDやコードにする
• 集計は、ファクトの数値を、ディメンションの切り口で区切る
• 次の章では、分けた表を集めて、つなげて、整える手順を記録する(Power Query)