Microsoft Officeの小技集　③エクセル　関数編　後編

はじめに

今回使用するエクセル関数シートは こちら です。
ぜひダウンロードしてみてくださると幸いです。
実物を見ながら確認していただくほうが分かりやすいかと思います。

VLOOKUP

あなたは、「10月分の売り上げ情報を、顧客がいつ、いくら購入したのか、分かりやすく表にしてくれ。」と要望を受けました。

そこで、下図のような表を作ることにしました。（「購入金額マトリクス」シート参照）

購入があった箇所に金額が表示されるので、ぱっと見て実績がわかりやすくなっています。
これを一か所ずつ手入力で入力するのは大変ですが、こちらも関数で実現することができます。この表をどうやって実現するか、解説していきます。
このような表（マトリクス表）を作るには、日付とユーザー名が一致する情報を購入履歴から取り出せばよさそうです。まずは、情報を取り出すための事前準備から始めます。

事前準備

まずは検索用のキーとなる列を作成しておきます。
また、セルA3にこのように入力し、下まで転写します。（「購入履歴」シート参照）

=TEXT(B3,”yyyy/m/d”)&C3

これをキーとして使用します。“yyyy/mm/dd”と記載することで日付の形式を整えています。

本題

結論から申し上げますと、「購入金額マトリクス」シートB3にこのように入力し、G33まで転写します。

=IF(ISERROR(VLOOKUP(TEXT($A3,”yyyy/m/d”)&B$2,購入履歴!$A$3:$G$15, 6,FALSE)), “”, VLOOKUP(TEXT($A3,”yyyy/m/d”)&B$2,購入履歴!$A$3:$G$15, 6,FALSE)

複雑ですが、ひとつずつ解説していきます。
はじめに、こちらの赤文字の箇所を解説します。

=IF(ISERROR(VLOOKUP(TEXT($A3,”yyyy/m/d”)&B$2,購入履歴!$A$3:$G$15, 6,FALSE)), “”, VLOOKUP(TEXT($A3,”yyyy/m/d”)&B$2),購入履歴!$A$3:$G$15, 6,FALSE))

こちらは文字列の結合ですので、下記のようになります。

TEXT($A3,”yyyy/m/d”)&B$2　＝　2021/10/1タナカタロウ

分かりやすくするため、最初の式をこのように書き換えます。

=IF(ISERROR(VLOOKUP(2021/10/1タナカタロウ, 購入履歴!$A$3:$G$15, 6,FALSE)), “”, VLOOKUP(2021/10/1タナカタロウ,購入履歴!$A$3:$G$15, 6,FALSE))

【参考（絶対参照）】

$(絶対参照)の解説をいたします。
$を指定しないと、ドラッグで関数をコピーした際に、参照位置が自動的に変わります。この変更を防ぐため、参照する箇所を固定するのが絶対参照です。 $A3 とここでは書かれていますが、これは常にA列を参照します。ということです。
$の直後が固定になります。また
B$2 とその後にありますが、これは常に２行目を参照します。ということです。
$の書く場所によって絶対参照する箇所が変わります。そして
$B$4　このように書きますと、常にB4セルを参照します。ということになります。

次にこちらの青文字の箇所を解説します

=IF(ISERROR(VLOOKUP(2021/10/1タナカタロウ, 購入履歴!$A$3:$G$15, 6,FALSE)), “”, VLOOKUP(2021/10/1タナカタロウ,購入履歴!$A$3:$G$15, 6,FALSE))

いよいよ、VLOOKUPですね。
VLOOKUPは、表を指定した範囲で検索し、特定のデータに対応する値を取り出す関数です。VLOOKUPの使用方法は下記のようになります。

VLOOKUP（検索値、範囲、列番号、検索の型）
ここでは

• 検索値　＝　2021/10/1タナカタロウ　（事前準備で作成したキーを指定することで表のA列から検索できます）
• 範囲　＝　購入履歴!$A$3:$G$15　（購入履歴シートのＡ３からＧ１５の中を検索）
• 列番号　＝　6　（検索結果があったところから、6個目の列）
• 検索の型　＝　ＦＡＬＳＥ　（完全一致）

よって1200が選択されます。

ここまでの式を書き変えてみます。

=IF(ISERROR(1200, “”, 1200))

だいぶ、すっきりしましたね。
ではラストです。

=IF(ISERROR(1200), “”, 1200)

IFは前回もご説明しましたが、ＩＦ（論理式、真の時、偽の時）ですね。
その内部にISERRORが入っています。
ISERRORとは「セルの値がエラーか判定する」というものです。
現状は1200が取得できているため偽ですが、取得に失敗したら真となります。

よって今回は

IF(偽, “”, 1200)

となり、最終的に表示される値は
1200
となります。