Youtubeで公開している内容をまとめたものです。
照合作業の概要とデータ準備
こんにちは、JIMOVEの山口です。今回は照合作業についてお話ししたいと思います。
照合作業はさまざまですが、今回はデータを4つ用意しています。
1.２つのデータでキー（基準）となる行の場所が違うデータ
たとえば、会社名と合計金額が記載された表を2つ以上用意し、それらを比較する想定です。
データ1とデータ2は、並びが変更されていたり、別のシステムからダウンロードされたことを想定しています。他にも、日付や商品名、数量や単価、合計金額が記載されたデータもあります。
これらのデータを会社ごと、または商品ごとに集計したデータと比較します。
実際には、
・請求書データ
・営業管理データ
・経理システムと営業システムなど
を突き合わせる作業がイメージしやすいかと思います。今回は、会社ごと、または会社と商品ごとに集計された表を比較する作業をしていきます。
関数の活用と比較作業
実際には、それぞれの状況に応じて、今回は5つの関数を使用して説明します。まず、データ1（会社名と合計金額が記載されたもの）とデータ2を比較する作業から始めます。
データが1行に1つずつある点が重要です。たとえば、会社名1に対するデータがそれぞれのシートに含まれています。
VLOOKUP関数の使用
この場合、VLOOKUP関数を使用するのが適切です。
データ2の内容をデータ1に取り込む作業を行います。関数を入力する際は、直接入力を推奨します。まずは半角のイコールを入力し、VLOOKUP関数を使います。この関数は、指定された範囲の1列目で特定の値を検索し、指定した列と同じ行にある値を返します。
VLOOKUP関数
別の表からキーとなる文字などを探してきて、一致した「同じ行」にある指定した「何列目」かの内容を反映させることができる関数検索値の設定とデータの反映
「（」（左括弧）を入れて検索値、範囲、列番号、検索方法を指定します。検索値は、データ1の会社名を基に、データ2の会社名一覧から探します。
データを並べて分かりやすくします。データ1に対して、データ2の表からデータを反映させていきます。検索値としては、この行に表示させたい内容をデータから探します。
=VLOOKUP（検索値,範囲,列番号,検索方法)VLOOKUP関数の詳細な使用方法
1.検索値を設定する
まず、探してきたい内容が書いてあるキーとなる「A2」をクリックし、「,（カンマ）」を入力します。
2.範囲を選択する
続いて範囲を指定する必要がありますが、一般的な使い方として、VLOOKUP関数では表全体を範囲として選択します。これにより、A列の1行目からB列までの範囲（A1:B10のように表現されます）を選択します。
3.列番号の指定
次に、列番号を指定します。VLOOKUP関数では、指定した範囲内で何列目のデータを表示するかを決定します。
今回は会社名（キー）を探し、「2」列目のB列の内容を表示するため、列番号として「2」を入力します。
4.検索方法を指定
続いて検索方法を指定します。完全一致を求める場合は、FALSEを入力するか、ダブルクリックで設定します。この後、「)」（括弧）を閉じてEnterキーを押します。
完全一致
会社名が完全に一致した場合に、●列目の値を表示します。
１文字でも異なる場合には、表示されません。結果の反映と範囲の固定
ここまで設定すると、会社名を基準に会社名から見て、2列目の内容が反映されます。1行目で設定した内容を、2行目以降にも同じ処理を適用する場合は、セルの右下のカーソルが「黒い十字マーク」に変化した状態で、「ダブルクリック」します。これをオートフィル（下へコピーする機能）と言います。
しかし、エラーが表示される場合があります。これは、Excelがデータをコピーする際に内容が1行ずつずれるため、検索する範囲がずれてしまうことが原因です。
たとえば、範囲をA1:B10としていた場合、Excelの特性として、オートフィルすると自動的に1行ずれれば、A2:B11のように1行ずれるという特性があります。範囲選択の問題点とその解決方法
オートフィルをすることで、選択した範囲が移動することが問題です。これを防ぐためには、範囲を固定する必要があります。
固定する方法としては、範囲を選択した際にF4キー（ファンクションキーの4）を押すことです。これにより、例えば「$A$1:$B$10」といったようにドルマークが付き、セルを固定します。列の前と行の前にドルマークを付けることで、それぞれ列と行を固定します。
この状態でEnterを押し、改めてコピーすると、オートフィルをし、行が移動しても「$A$1:$B$10」という範囲が変わらないようになります。範囲指定をする際は、行や列がずれないように設定することが重要です。
桁区切りスタイル設定
現在、B列には桁区切りスタイルが適用されており、3桁ごとに「,（カンマ）」が入っています。
比較のために他の範囲でも同様のスタイルを適用したい場合、範囲を選択し、ホーム画面にある「数値」セクションの「桁区切りスタイル」をクリックします。
また、ショートカットキーを使用する方法もあり、Altキーを押してホーム画面のコマンドを使用することができますが、桁区切りスタイルに対応する「K」キーを押すことで適用できます。つまり、「Alt + H + K」の順で押すことで桁区切りスタイルのショートカットが可能です。
データの照合とエラー処理
会社のデータに関しては、たとえば「会社6」がエラーとなっており、データ1の表には存在するがデータ2の表にはないことがわかります。
このため、どちらのデータが正しいかを確認し、（エラーとなっている）会社6が不要であれば削除、データ2に会社6が必要であれば追加（本来はデータ2にないといけないがない場合）する必要があります。
また、数値の正確性も確認が必要です。会社が10社程度の場合は目視で確認可能ですが、20社、100社など数が増えると目視では難しくなってきます。
IF関数の使用
比較をするために、よく使用される関数としてIF関数があります。
この関数は、特定の条件を「満たす場合」と「満たさない場合」で異なる値を返します。
今回の場合、データ1とデータ2のそれぞれの合計金額の列を比較して、それが等しいかどうかを判断したいと考えていきます。
条件として「B2=C2」（B2に元の値、C2にVLOOKUPで呼び出した値がある）という論理式を使用し、条件が真の場合は「◯」、偽の場合は「×」を返す（表示する）よう設定します。
文字列を使用する場合は、「””（ダブルクォーテーション）」で囲む必要があります。
Enterを押すと、条件に合致する場合には「◯」が表示されます。
範囲指定で行や列がずれても、ドルマークで固定したので、問題ありません。
ダブルクリックしてオートフィル（下へコピー）で関数を下の行へ適用すると、一致していない行では「×」が表示されます。
例えば「会社8」では金額に差異があり、3万1,600円と3万1,680円のため、金額の違いが明確になります。
フィルター機能の活用
データが100行、200行と増えた場合、目視での確認には限界があります。このような場合に役立つのがフィルター機能です。
（表の中の）セルを選択した状態で、データタブの「フィルター」をクリックすると、選択したセルにフィルターが適用されます。逆三角形のマークが表示され、これをクリックすることで必要なデータだけを選択できます。
誤っているデータのみを残したい場合は、「◯」印を非表示にしてOKを押します。これにより、一致しなかったデータのみが残ります。
フィルターのショートカットキーは、「Ctrl + Shift + L」です。これを押すと、フィルターの適用と解除が可能です。
IF関数以外の比較関数：EXACT関数
IF関数はよく知られていますが、もう一つの関数であるEXACT関数もあります。
この関数は2つの文字列を比較し、同じであればTRUE、異なればFALSEを返します。大文字と小文字も区別されます。
使用方法は、左括弧の後に文字列1と文字列2を入力し、右括弧で閉じます。実際には括弧を省略してもEnterを押すと自動的に付けられます。
この関数をコピーすると、IF関数と同様に、エラーがある場所ではFALSEが表示されます。
=EXACT(文字列1,文字列2)
文字列1と文字列2を比較し、同じであればTRUE、違えばFALSEとなる。
IF関数で設定した◯はTRUE 、×がFALSEになるイメージ。まとめ
VLOOKUP関数の使用法とルール
VLOOKUP関数の使用タイミングは、同じ行にあるデータを表示したい場合や、何かをキーにして同じ行のデータを呼び出す時に利用できます。
ルールとしては、「一番左に検索する列が来るように設定すること」が重要です。
例えば、今回の場合はA列からスタートし、B列を選択しました。何列目かを指定する際は、選択した範囲内で左から数えます。B列からスタートする場合は、B列を基準にして列を数えます。
VLOOKUP関数を使用する際にはいくつかの条件があります。
まず、反映する内容は検索キーの右側になければなりません。この条件を満たさない場合、VLOOKUP関数は使用できません。
VLOOKUP関数を利用する際は、検索値、範囲、列番号、検索方法の4つを入力することが必要です。これらを正しく設定することで、データを呼び出すことができます。
VLOOKUP(検索値,範囲,列番号,検索方法)IF関数の活用
IF関数は条件に応じて処理を分けることが可能な関数です。
条件が合致するか否かに基づき、一致する場合の動作と一致しない場合の動作を設定できます。条件に一致する場合のみ特定の操作をさせることも可能です。使用法としては、If関数の後に括弧をつけ、「条件」、「条件が真の時の処理」、「偽の時の処理」を指定します。
IF(条件,真の時の処理,偽の時の処理)EXACT関数の使用法
もう一つの関数、EXACT関数は、2つの値を比較する際に使用されます。
数字や文字列などを比較する際に便利です。使い方は非常にシンプルで、比較したい2つの値を指定するだけです。EXACT関数は、2つの内容が完全に一致するかどうかを判断するシンプルな関数です。
EXACT(文字列1,文字列2)VLOOKUP、IF、EXACT関数の使い分け
VLOOKUP関数は一行に一つのデータを呼び出す際に使用し、IF関数とEXACT関数を使ってデータを比較します。これにより、各データが正しいかどうかを判断します。
IF関数はより一般的に使用され、条件に一致するかどうかを判断するのに適しています。一方、EXACT関数は2つの値が完全に一致するかどうかを確認する際に使用します。
コメント