検索値に紐づいた値を抽出するのにVLOOKUP関数が便利です。
しかし検索値は1列しか指定できません。
隣のセルどおしをそのまま使って良いならばセルを繋げるだけなので簡単ですが、セルがバラバラで抽出したいものがどこにあるかわからない、さらに重複もある場合は困難です。
そういった場合の指定方法はVLOOKUPを使わずに以下のような式で対応すると良いです。
例えば下図があります。
この中から「担当者が山田」「評価がA」のものを抽出したいとき、単純にA列とB列を繋げただけでは対応できません。
重複するものが出てきてしまいます。
ではどうしたらよいか。
以下のように配列を使っていきます。
同列に日付のデータがあるときに、日は異なるが同月だったら何件あるか数えたい、といった場合。
COUNTIFSがよく使われますが、文字列だった場合は検索文字を1回指定すれば良いので楽ですが、日時の場合は「○日以上○日以下」と2回指定する必要があり面倒です。
これを簡単にまとめて計算する方法を試してみましょう。
VLOOKUP関数で文字列のあいまい検索をさせたい場合にワイルドカードを使いますね。
「*」アスタリスクは、文字数を決めないで検索可能
例：「アップル*」と指定すると「アップルジュース」も「アップルパイ」も検索できます。
「?」クエスチョンマークは、1文字を検索
例：「アップル?」と指定すると「アップルA」「アップルB」と検索できますが「アップルCC」は検索できません。
上記の例は検索する単語が決まっている場合です。
では検索する単語が都度変化する場合、セルを指定することが良くあります。
A1に文字入力して、そこを検索値とするケースです。
この場合よくやりがちなのが「*A1*」や「"*"A1"*"」と指定してしまいます。
しかしこれでは検索できません。
正しくは「"*"&A1&"*"」と「&」の記号で繋げるようにします。
例を見てみましょう。
データの件数を数えるならば通常はCOUNT、COUNTA、COUNTIF・・・などがあります。
しかし重複データを1件として数えたい場合には、これらの式だけでは対応できません。
下図のように重複したデータがあります。
個数を数えるだけなら13件ですが、重複は1件として数えると5件となります。
セル内に同じ記号があった場合、最後に来る記号は文字列の何個目になるのか数えます。
右から検索する、といったイメージです。
セル内の文字位置は違えど表示される記号は必ず3つなどと個数が決まっている場合はこちらで対応できます。
【セル内に同じ記号が複数ある中でn個目の記号が文字列の何番目にあるか数える】
今回は、表示される記号の個数がバラバラで決まってない場合に使えます。
近似値を抽出する場合には、指定数の範囲内で抽出することが多いと思います。
この場合の式もページ下部に記載しましたが、今回は指定数を超えない場合と超える場合を両方含んだものを複数件抽出してみます。
例えば「1000」の近似値を4件抽出してみます。
下図のように数値が並んでいます。
（「1000」の近似値をわかりやすく色付けしてます）
各行でそれぞれの点数を計算し最後にまとめて合計する、といった方法ならばIF関数だけでできます。
これを各行での計算を使わずに、合計だけを一発で求める方法です。
下図のような表があった時に、○は5点、△は3点、×は0点、として合計を出します。
SUMIFS関数では同列に複数の条件を指定する場合、日時や数値で「○以上」「○以下」のような指定は可能ですが、特定の文字列を複数指定することができません。
例えば下図のようにA列では「A会社」を指定
C列では「りんご」または「みかん」を指定
B列を合計します。
!--more-->
F5に以下の式を挿入します。
=SUM(SUMIFS($B$1:$B$10,$C$1:$C$10,{"りんご","みかん"},$A$1:$A$10,"A会社"))
値は「58」と返ります。
※合計ではなく個数を数えたい場合はこちら↓
〇と△の数を数える、同列内の複数項目の個数を数える COUNTIF
例えば、”セルが結合されてないシート”があり、ここにデータが入っているとします。
このデータを、”セルが結合されているシート”の方へコピペしようと思っても、エラーで「結合されたセルの一部を変更することはできません。」と出てしまったり、セルの数が合っていて貼り付けできたとしても結合が解除されてしまったり、とコピペすることができません。
では「＝」イコールを使ってセルを参照しようと思っても、参照するセルが飛び飛びになってしまい、これも上手くいきません。
そこで以下のような式を使ってセルをコピーしてみたいと思います。
例えばセル内にハイフンが複数個あるとします。
n個目に出てきたハイフンが、文字列の先頭から数えて何番目に出現しているかを数えたい、という時に使える式です。
下図のA1のようなデータがあったときに、4個目のハイフンは文字列何番目にあるか数えます。
