【動画付き・中級編】Excelで数万行の大量データを扱うときの便利テクニック4選

この記事を読んでわかること
  • 絶対参照と複合参照の違いと使い方
  • IF関数
  • 検索・並べ替え・フィルター機能

Excel中級編として「絶対参照と複合参照」「IF関数」「条件付き書式」「情報検索のテクニック3選」をご紹介します。

特に、「条件付き書式」と「フィルター」機能をマスターすると、Excelのデータをひと目でわかりやすく伝えたり、複雑なデータの並べ替えも行えるようになります。

Excel業務でよく用いられる機能ばかりですので、ぜひマスターして活用してみましょう。

本記事はYouTube『ユースフル / スキルの図書館』チャンネルより、以下の動画を記事にまとめたものです。動画だけではなく、テキストで復習したい方もいるはず。本記事と動画を組み合わせて、Excelの基本操作をマスターしていきましょう。

前回はExcelの基本機能の使い方4選として「表示形式」「グラフ」「エラー回避」「印刷設定」について学習しました。まだお読みになっていない方はこちらもぜひご覧ください。

目次

絶対参照と複合参照の使い方

Excelを扱っていると、あるセルの数式をほかのセルに反映させることがよくあります。このようにあるセルの数式をほかのセルにコピーアンドペースト(コピペ)することを「セル参照」といいます。

セル参照の基本である絶対参照複合参照についてそれぞれ確認していきましょう。

絶対参照

絶対参照を活用すると、計算に使用するセルを固定することができます。はじめに、どんなときに絶対参照を活用するのかを確認していきましょう。

たとえば、「品目」「価格」「数量」「総額」が並んでいる下記の表があります。

Excel中級編1

各品目を3割引きするので、「価格×数量×割引率」を計算して、3割引き後の価格を「総額」に表示します。これをExcelでは「=C3*D3*G3」と表すことができます。

Excel中級編2

そして、[Enterキー]を押下すると、下記のように瞬時に計算を行うことができます。

Excel中級編3

オートフィル機能を活用して、これを下の消しゴム、ボールペンの総額にも適用します。すると、下記のように「0」と表示されます。

Excel中級編4

「E4」セルを選択して、[F2]キーを押下して数式を確認してみましょう。すると、下記のように「=C4*D4*G4」と割引率を記入していない「G4」セルが数式に組み込まれていることがわかります。

Excel中級編5
※「E5」セルでは割引率に「G5」が適用されて、同様に総額が「0」になる

正しく計算するためには、割引率が示された「G3」セルだけ固定する必要があります。そこで活用できる機能が絶対参照です。絶対参照を活用することで、参照元のセルを固定することができます。

絶対参照を活用する手順を見ていきましょう。

絶対参照を活用する手順
  1. 「=」を入力して、参照したいセルをクリックする
  2. 固定したいセルの前後どちらかにカーソルを置く
  3. [F4キー]を押下する
Excel中級編6
2.固定したいセルの前後どちらかにカーソルを置く

これで下記のように「G3」が「$G$3」と表示が変わります。

Excel中級編7

先ほどと同様にオートフィル機能を活用すると、消しゴム、ボールペンの総額も適切に表示することができます。

Excel中級編8

また、「E4」セルで数式を確認してみると、「$G$3」とセルを固定できることがわかります。

Excel中級編9

複合参照

複合参照は列だけ、または行だけを固定しながらセル参照する方法です。はじめに、複合参照を活用できないと、どのような問題が生じるか確認してみましょう。

たとえば、掛け算の100マス計算の表があります。

Excel中級編10

「C4」セルは2×1を計算したいので、「=B4*C3」と入力できます。

Excel中級編11

「C4」セルは「2」になり、「C4」セルをオートフィル機能を活用して右に適用します。すると、下記のように間違った値が入力されます。

Excel中級編12

数式を確認してみると、下記のように計算したいセルとずれてしまっていることがわかります。

Excel中級編13

そこで、「=$B$4*C3」と「B2」セルの「2」を絶対参照で固定します。

Excel中級編14

もう一度オートフィル機能を適用すると、下記のように適切に計算できます。

Excel中級編15

同様に、列にオートフィル機能を適用します。すると、また間違った計算が行われてしまいます。

Excel中級編16

数式を確認してみると、先ほどと同様に計算したいセルとずれてしまっていることがわかります。

Excel中級編17

このような問題を解決できるのが複合参照です。複合参照を活用する手順を見ていきましょう。

複合参照を活用して列だけを固定する手順
  1. 「=B4*C3」と入力する
  2. 固定したいセルのセル番号にカーソルを合わせる
  3. [F4]キーを3回押下する

これで固定したいセルのアルファベットの前に「$」をつけることができます。今回はB列を固定したいので、「$B4」となります。

複合参照を活用して行だけを固定する手順
  1. 「=B4*C3」と入力する
  2. 固定したいセルのセル番号にカーソルを合わせる
  3. [F4]キーを2回押下する

これで固定したいセルの番号の前に「$」をつけることができます。今回は3行目を固定したいので、「C$3」となります。この2つ手順で下記のように「=$B4*C$3」と入力できます。

Excel中級編18

これで下記のようにすべてのマスで適切に計算できるようになります。

Excel中級編19

この方法を知らなければ、適切に計算ができないのでぜひ覚えて活用してみましょう。

また、[F4]キーによるショートカットは下記の通りなので、ぜひこちらもマスターしましょう。

スクロールできます
行も列も固定したいとき[F4]キーを回押下する
行のみ固定したいとき[F4]キーを回押下する
列のみ固定したいとき[F4]キーを回押下する
タイトル:[F4]キーと参照方法の切り替え
[F4]キーを押下して参照方法を切り替える方法

セル参照についてはこちらの記事でも詳しくご紹介しています。まだ読んでいない方はぜひご覧になってください。

IF関数の使い方

IF関数の概要と論理式の仕組み

IF関数は指定した条件に合わせて異なる結果を返す関数です。「もしも〜ならば」と条件を指定して、「この場合はA、この場合はB」と条件応じて場合分けすることができます。

IF関数

構造:「=IF(論理式,[値が真の場合],[値が偽の場合])」

論理式が条件を満たすかどうかに応じて、表示する値を変えることができます

このIF関数を組み立てるにあたって、重要となるのが論理式の部分です。

論理式は「C5>=$C$2」のように「○○ (記号) ××」の形で表されます。そして、記号の部分には、以下の6つの比較演算子のいずれかを使います。

スクロールできます
条件論理式
○○が××だったら(と等しい)○○=××
○○は××より大きい○○>××
○○は××以上○○>=××
○○は××より小さい○○<××
○○は××以下○○<=××
○○が××でないとき○○<>××

IF関数の活用

たとえば、下記のようにクラスの教科ごとの成績一覧表があります。合計点が300点以下の生徒にチェックをつけたいというとき、IF関数を活用できます。

Excel中級編20

IF関数を活用して、合計点が300点以下の生徒には「補習」の列に「○」と返す手順を見ていきましょう。

IF関数を活用する手順
  1. 「=IF」と入力する
  2. 論理式に「I4<=300」と入力する(「I4」に合計点が記載されているため)
  3. 第二引数([値が真の場合])に「”○”」と入力する
  4. 第三引数([値が偽の場合])に「” ”」と入力する(合計点が300点を超える場合は空白のままでいいので、スペースをダブルクォーテーションで挟む
Excel中級編21
1.「=IF」と入力する
Excel中級編22
2.論理式に「I4<=300」と入力する(「I4」に合計点が記載されているため)

これで「=IF(I4<=300,”○”,” “)」と入力できていれば完了です。これをオートフィル機能で下にも適用します。すると、下記のように合計点が300点以下の生徒には「○」と表示できます。

Excel中級編23

条件付き書式の使い方

条件付き書式は設定した条件を満たしたセルに色をつけたいときに用いる機能です。

たとえば、先ほどまで活用していた表で40点以下の点数と90点以上の点数を見やすくするために色をつけてみましょう。

Excel中級編24
条件に合わせてセルに色をつける手順
  1. 各教科の点数すべてを範囲選択する
  2. [ホーム]タブにある「条件付き書式」を選択して、「セルの強調表示ルール」を選択する
  3. 「指定の値より小さい」を選択する
  4. このダイアログボックスが表示されるので、「40」と入力する
  5. 「ユーザー設定の書式」から好みのカスタムに変更する
    (今回は、背景色:赤、文字のフォント:白の設定)
  6. 「条件付き書式」を選択して、「ルールの管理」を選択する
  7. 「新規ルール」を選択する
  8. 「指定の値を含むセルだけを書式設定」を選択して、「次のセルのみを書式設定」で左から「セルの値」「次の値以上」「90」と入力する
  9. 「セルの書式設定」ダイアログボックスから好みのカスタムに変更する(今回は、背景色:青、文字のフォント:白の設定)
Excel中級編25
1.各教科の点数すべてを範囲選択する
Excel中級編26
2.[ホーム]タブにある「条件付き書式」を選択して、「セルの強調表示ルール」を選択する
Excel中級編27
3.「指定の値より小さい」を選択する
Excel中級編28
4.このダイアログボックスが表示されるので、「40」と入力する
Excel中級編29
5.「ユーザー設定の書式」から好みのカスタムに変更する(今回は、背景色:赤、文字のフォント:白の設定)
Excel中級編30
6.「条件付き書式」を選択して、「ルールの管理」を選択する
Excel中級編31
7.「新規ルール」を選択する
Excel中級編32
8.「指定の値を含むセルだけを書式設定」を選択して、「次のセルのみを書式設定」で右から「セルの値」「次の値以上」「90」と入力する
Excel中級編33
9.「セルの書式設定」ダイアログボックスから好みのカスタムに変更する(今回は、背景色:青、文字のフォント:白の設定)

これで下記のように40点以下は赤色、90点以上は青色で表示でき、非常に見やすい表を作成できるようになります。

Excel中級編34

情報を検索するテクニック3選

情報を素早く検索するときに活用できる「検索」「並び替え」「フィルター」について見ていきます。

検索

検索したいときは、「Ctrlキー+F」のショートカットで「検索と置換」ダイアログボックスを表示します。

Excel中級編35
ショートカットキー操作
「Ctrlキー+F」「検索と置換」ダイアログボックスを表示

たとえば、野村さんの点数を知りたいとき、このダイアログボックスで「野村」と検索します。すると、下記のように野村孝明のセルが表示されます。

Excel中級編36

また、文字だけでなく値で検索することもできます。たとえば、「90」という値で検索すると、下記のように「90」と入力したセルがすべて表示されます。

Excel中級編37

並べ替え

下記の表は出席番号順に氏名が並んでいます。

Excel中級編38

たとえば、出席番号順でなく、数学の点数が高い順に並び替えたいとき、並べ替え機能を活用できます。数学の点数が高い順に並べ替える手順を見ていきましょう。

数学の点数が高い順に並び替える手順
  1. [データ]タブにある「並べ替え」を選択する
  2. 「並べ替え」ダイアログボックスが表示されるので、「最優先されるキー」で「数学」を選択して、「順序」で「大きい順」を選択する
Excel中級編39
1.[データ]タブにある「並べ替え」を選択する
Excel中級編40
2.「並べ替え」ダイアログボックスが表示されるので、「最優先されるキー」で「数学」を選択して、「順序」で「大きい順」を選択する

これで下記のように数学の点数が高い順に並べ替えることができます。

Excel中級編41

フィルター

フィルターを設定する方法

はじめに表の一番上の項目をすべて選択します。

Excel中級編42

そして、「Ctrlキー+L」のショートカットを活用すると、下記のように矢印のアイコンが表示されます。これをフィルターといいます。

Excel中級編43
ショートカットキー操作
「Ctrlキー+L」フィルターを設置

フィルターを活用すると、表に表示する人を自由に選択できます。

「氏名」のフィルターを選択して、下記のようにすべて選択をクリックして、一度全員のチェックを外します。そして、相田さん、浅村さんの2人だけを選択します。

Excel中級編44

すると、下記の選択した2名のみを表示することができます。

Excel中級編45

国語のフィルターをクリックして、「昇順」「降順」を選択すると、先ほど並べ替えと同様に点数の低い順・高い順に並べ替えることもできます。

Excel中級編46

フィルターを活用して並べ替える方法

鎌田さん、山田さんは国語の点数は同じですが、物理の点数は山田さんの方が高いです。そこで、山田さんが鎌田さんよりも上に並べ替える手順をみていきましょう。

Excel中級編47
フィルターを活用して並べ替える手順
  1. 「Ctrlキー+F」でフィルターを解除する
  2. 「並べ替え」を選択して、「最優先されるキー」を右から「国語」「セルの値」「大きい順」にする
  3. 「レベルの追加」をクリックして、「次に優先されるキー」を左から「物理」「セルの値」「大きい順」にする
Excel中級編48
1.「Ctrlキー+F」でフィルターを解除する
Excel中級編49
2.「並べ替え」を選択して、「最優先されるキー」を左から「国語」「セルの値」「大きい順」にする
Excel中級編50
3.「レベルの追加」をクリックして、「次に優先されるキー」を左から「物理」「セルの値」「大きい順」にする

これで国語の点数が同じでも、物理の点数が高い山田さんが上になります。

Excel中級編51

ウィンドウ枠を固定する方法

下記のように下にスクロールしても表の見出し部(氏名や教科など)を固定することができます。これをウィンドウ枠の固定といいます。

ウィンドウ枠は始めから固定されていません。たとえば、先ほどから扱っている成績表を下にスクロールしてみます。すると、D列、F列それぞれがどの教科を表しているのかわからなくなります。

Excel中級編52

そこで、ウィンドウ枠を固定するショートカットを覚えましょう。「Altキー+W+Fキーを2回押下」でウィンドウ枠を固定することができます。

Excel中級編53
ショートカットキー操作
「Altキー+W+Fキーを2回押下」ウィンドウ枠を固定する

これでウィンドウ枠が固定され、下にスクロールしても、氏名や教科がひと目でわかるようになります。

Excel中級編54

まとめとおすすめ講座の紹介

今回のポイントは以下の3点です。

  • 絶対参照と複合参照の違いを理解して、セル参照に応用する
  • IF関数の論理式の仕組みを理解する
  • 検索・並べ替え・フィルター機能をマスターしてデータを素早く検索できるようにする

Excel中級編ということで、実務のExcel業務で活用できる機能をご紹介してきました。「条件付き書式」「ウィンドウ枠を固定する方法」はデータを見やすくするひと工夫として非常に有効なので、ぜひ活用しましょう。

気に入ったらシェア!
URLをコピーする
URLをコピーしました!

この記事を書いた人

記事をお読みいただきありがとうございます!ユースフル編集部では、Excelをはじめとするマイクロソフト製品の活用法をわかりやすく紹介しています。ご興味ある方は、ぜひ他の記事もご覧ください!

目次
閉じる