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

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

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

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

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

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

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

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

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

絶対参照

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

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

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

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

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

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

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

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

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

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

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

複合参照

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

行も列も固定したいとき	[F4]キーを１回押下する
行のみ固定したいとき	[F4]キーを２回押下する
列のみ固定したいとき	[F4]キーを３回押下する

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

IF関数の使い方

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

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

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

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

条件	論理式
○○が××だったら（と等しい）	○○=××
○○は××より大きい	○○>××
○○は××以上	○○>=××
○○は××より小さい	○○<××
○○は××以下	○○<=××
○○が××でないとき	○○<>××

IF関数の活用

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

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

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

条件付き書式の使い方

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

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

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

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

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

検索

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

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

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

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

並べ替え

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

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

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

フィルター

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

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

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

ショートカットキー	操作
「Ctrlキー＋L」	フィルターを設置

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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