【Excel】数式・関数の超速記術とスピード編集
こんにちは、HARUです。
今回は、数式を素早く入力・編集するために必要な知識を解説します。
表計算ソフトであるExcelでは、集めた値を合計したり、蓄積されたデータから必要な情報を検索したり、条件を満たす場合の表現を指示したりすることがよくあります。
この記事では、値の計算に不可欠な数式のしくみと関数の速記術を演習していきましょう。
記事の後半では、実務で非常に使用頻度の高いIF関数とVLOOKUP関数をサンプルに、数式をスピーディーに編集するテクニックについても触れていきます。
これまでのセル移動や範囲選択の基本動作、セルのモード切り替えやセル内データ編集の操作を余すことなく生かすことができますので、 確実におさえておきましょう。
↓各基本操作を解説した記事はこちらです↓
数式のしくみと構成
「数式」とは、数や量を表す値や文字列を計算記号で結んで数学的な意味を持たせた式のことです。Excelでワークシート上に数式を入力する際に考慮すべき要素は大きく4つあります。
①関数
指定の値や文字列に対し、あらかじめ定められた処理を行う機能のことです。 Excelには数百種類の関数が実装されています。
簡易的な計算式であれば関数を用いなくても数式は成り立ちますが、IF関数に代表される論理関数や、VLOOKUP関数のような検索関数は、蓄積された情報の評価や検索が効率的に行える優れものです。（サンプルではIF関数を使って、達成率が105％以上の支店は○、105%未満の支店は×を返す条件を指定しています）
②セル参照
数学の世界では計算する値を数式に直接記述しますが、Excelの場合、対象 の値はセルに入力されていることがほとんどです。
このセル参照を数式にうまく活用すれば、たとえ参照元の値が変わったとしても、参照先の計算結果も自動で更新できます。
（サンプルではE3セルを参照することで、E3セルにある110％という値が計算対象となっています）
※Excelにおいて他のセルにある数式によって参照されているセルを「参照 元」、数式が入力されているセルを「参照先」と呼びます。
たとえばB2セルに「=A1」と入力されている場合、参照元はA1セル、参照先はB2セルとなります。
③演算子
数式の値をどのように計算するかを示す記号のことです。
数学の世界で使用され る×や÷、≠などの代わりに、Excelの数式特有の記号も用意されています。以下にまとめたExcelにおける演算子の種類と意味を理解して使っていきましょう。
（サンプルでは＞＝（大なりイコール）記号を用いて参照したセルの値が達成率105％以上かどうかの計算／判定をしています）
④固定値
数式には固定の値を直接入力することも可能です。
セル参照と固定値は計算対象の更新頻度に応じて使い分けていきましょう。
（サンプルでは評価基準となる105%や判定結果を直接入力しています）
数式の入力・修正 基礎編
数式を入力するときは"="(イコール)から始めることが多いですが、"+"(プラス)や"-"(マイナス)でも計算式の作成が始まるんだとExcelに認識されます。
キーボード上の"="は[Shift]を押さないと入力されないため、テンキーが使える環境なら代わりにテンキーの"+"でスタートするのもおすすめです。
数式の入力
数式の記述は半角英数入力モードで行います。
販売実績の計画達成率を求めるために、「A支店の実績値をA支店の計画値で割る」という計算をしてみます。
①E3セルに"="(イコール)を入力します。
②方向キーを動かすと、セルのモードが「参照」モードになり、計算対象のセルを指定できます。まずはD3セルにカーソルを合わせます。
③割り算を示す"/"(スラッシュ)を入力します。
④方向キー操作で、今度はC3セルを指定します。
※参照セルごとに色分けされるので参照元がどこにあるかわかりやすいですね！
⑤最後に[Enter]で実行すると、計算結果が返されます。
数式の修正
誤った数式を入力してしまうと、異なる計算結果が返ったり、エラーが表示されたりします。
[F2]で数式を確認すると、C3セルの計画値で割るべきところを、B3セルの文字列で割ってしまっているためエラーになっているようです。
一度確定した数式の修正方法には大きく3つあります。
▶最初から入力し直す
前述の入力方法からやり直します。
▶直接修正する
[F2]を押して数式の修正を試みると「編集」モードになります。
この状態で方向キー操作をすると、カーソルはセル内（数式内）を移動します。今回の場合は"B"を"C"に修正すれば、C3セルが参照されます。
▶参照し直す
「編集」モードの状態で、修正したい部分を選択するか、削除しておきます。
ここで[F2]を押すと、セルのモードが「参照」モードに切り替わり、方向キー操作でセル参照が可能になります。
C3セルを指定して[Enter]で実行します。
関数の入力
簡易的な計算式と同じく、関数も直接書き上げることが鉄則です。
実務でよく使う関数の構造をおさえておけば、関数の挿入機能を使わずに効率的な入力作業が可能です。
IF関数とVLOOKUP関数を例に、関数の速記術を見ていきましょう！
代表的な関数
引数とはそれぞれの関数を機能させるために必要な判断材料のことです。
関数名の後のカッコ内に正しい順序で当てはめていきます。
関数の記術① IF関数
本記事の冒頭にサンプルとして掲載したIF関数を入力（達成率 105％を基準に各支店の実績を評価）していきましょう。
①"="(イコール)に続けて、アルファベットの"I"を入力します。
"I"で始まる関数の候補がヒットし、リストの1番上にあるIF関数が選択されます。
②この状態で[Tab]を押すと、関数が挿入され、引数のガイダンスが展開されます。
③[←]でE3セルを参照します。
④">"(大なり)、"="(イコール)に続けて"105%"と入力します。
ここまでで第1引数の論理式が完成するので、","(カンマ)を入れて第2引数の入力に移ります。
⑤"“○”"と入力し、再度","(カンマ)で区切ります。
※数式に文字列を直接入力する場合は必ずダブルクォーテーションマークで囲むんでしたね！
⑥“×”と入力し[Enter]で実行します。
※関数内に二重、三重とカッコを使用している場合は、その数だけカッコを閉じてから決定しますが、今回は一重なのでカッコで閉じなくても計算がなされます。
⑦この数式を[Ctrl]＋[D]で下へコピーします。
関数の記述② VLOOKUP関数
左側の表から管理番号を検索し、VLOOKUP関数で必要な情報(価格/生産国)を取り出していきます。その後、F3セルに入力した数式を、他の範囲にもコピーしましょう。
①"="(イコール)に続けて、アルファベットの"V"、"L"を入力すると、VLOOKUP関数が候補として選択されます。
②[Tab]を押して関数を挿入します。
③[←]を使い検索値としてE3セルを参照し、","(カンマ)で区切ります。
④セル移動と範囲選択の基本操作を使いながら、検索対象範囲としてA3セル ～C12セルを指定し、","(カンマ)で区切ります。
※このとき、検索値(管理番号)が必ず範囲の左端にくるように選択しましょう。
⑤現在数式を入力しているセルには価格情報を返したいので、範囲における検索値の列から数えて2つ目の列からデータを抽出します。
"2"を入力し、","(カンマ)で区切ます。
⑥検索方法は"FALSE"(完全一致)を指定します。
表示されるリストから[↓]で選択して[Tab]で確定することもできますが、"FALSE"は数字の「0」で代用できます。
VLOOKUP関数の第4引数は「0」を入力すると覚えておきましょう！
⑦[Enter]で決定すると「PC-009」の価格が返されます。
※前述のIF関数と同じく、数式内のカッコが1件のみであれば")"で式を閉じなくても計算結果を求められます。
関数の編集（絶対参照と複合参照）
完成した数式をすべての欄にコピーしてみると、一部の検索結果にエラーが返ってしまいます。
この原因は大きく2つです。
①数式を下方向にコピーしたことで参照元の検索対象範囲もスライドし、 「PC-001」が範囲から外れてしまった。
②数式を右方向にコピーしたことで管理番号の欄で固定しておきたい検索値もスライドし、意図しない検索値を参照してしまった。
このように、参照元を固定しない参照方法を「相対参照」といい、参照元を固定する参照方法を「絶対参照」または「複合参照」といいます。
入力した数式を他の範囲にコピーする場合は、共通の参照セルや参照範囲がズレないように、参照方法を工夫する必要があります。
参照方法は、数式内に指定したセルまたは範囲の列番号・行番号の前に"$"をつけることで変更でき、この切り替えは[F4]で行います。
絶対参照
最初に入力した数式の参照方法を修正します。[F2]で「編集」モードにして第2引数の範囲を更新しましょう。
検索対象範囲は、数式を下方向、右方向いずれにコピーする場合でも一切ズレてほしくないため、列番号も行番号も固定する「絶対参照」にします。
ちなみに、数式の入力時に初めてセル範囲を参照するタイミングで[F4]を使えば参照方法をまとめて切り替えられますが…、
今回のように事後的に編集している場合は、始点と終点のセル番地にそれぞれカーソルをおいて参照方法を1つずつ切り替える必要があります。
こんなときは、中心の":"(コロン)を選択した状態で[F4]を1回押します。
これによって、第2引数の参照方法をまとめて絶対参照に変更できます。
複合参照
続けて、第1引数(検索値)の参照方法を修正します。
検索値は数式を下方向にコピーするにしたがって参照元をスライドさせたいですが、右方向にはズレてほしくないですよね。
そのため、列番号のみを固定する「複合参照」にしましょう。
第1引数(検索値)にカーソルを合わせて、[F4]を3回押します。
すると列番号の前だけ に"$"がついた複合参照に変更できます。
修正できたら、数式を他のセルにもコピーします。
ただ、第3引数の列番号が「2「の数式を全範囲にコピーすると、すべての検索結果が価格情報になってしまいます。
G列にコピーする分は、価格情報ではなく生産国情報を取り出したいので、F3セルからG3セルに1つだけコピーし、列番号を「3」に変更してから下方向にコピーしましょう。
※他の関数と組み合わせることで、こうした手作業による調整も不要となるしくみを構築することができます。いずれ他の記事でご紹介しますね！
各管理番号の情報を正しく取り 出すことができました。
参考情報
IF関数とは？
IFは「もし～だったら、」を意味する英語です。
今回の演習では、販売実績の計画達成率に応じて返す文字列をIF関数で指示しました。
もし実績が計画比105％以上なら「○」、そうでなければ(105％未満であれば)「×」を表記する設定をしたように、こちらから与えた条件を満たしている場合と満たしていない場合の処理を分岐させることができる関数です。
条件分岐を論理的に行う役割から「論理関数」に分類されます。
VLOOKUP関数とは？
VLOOKUPは、Vertical(垂直に) Lookup(調べる)の略です。
今回の演習では、VLOOKUP関数を使って管理番号を検索し、該当する価格や生産国を取り出してくるように指示しました。
商品リストや名簿において検索したい文字列や値を縦方向に探し、一致するデータを見つけたら、そこから横に●列進んだお目当ての情報を抽出する役割を持っています。
VLOOKUP関数は、INDEX関数やMATCH関数、XLOOKUP関数などとともに「検索関数」に分類されています。
最後に、今回新たに登場したキー操作をおさらいしておきましょう。
本日は数式の速記術や関数のスピード編集テクニックをご紹介しました。
表計算ソフトであるExcel業務を効率化するために欠かせない操作ばかりですので、この機会に必ずおさえておきましょう！
↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。
↓↓Excel操作をとにかく高速化したい方へ↓↓
この記事が気に入ったらサポートをしてみませんか？