曖昧な複数の条件でデータを抽出する方法：Excel活用プロモーティング

「先月に売り上げた、大阪に住む、やまもとさんという人の発送明細は？」、「半年ほど前に送ってもらった商品をもう一度注文したいのだけど、その時は、やまもとという名前で注文したと思う」など、あまりハッキリしない曖昧な情報を元にデータを絞り込んだり、抽出する必要はありませんか？

小企業では誰もが忙しく、問合せを受けて、これまでの伝票を１枚ずつめくるなどという気の滅入る作業は極力避けたいもんです。必要データを日ごろから整理・整頓しておけば、Excelという優れた道具を使って、パパっと、必要な情報を絞り込み、場合によれば、即答できるようになります。Excelによる業務改善！というほどのものではないと思えますが、Excelの活用で、こういう、メンドクサイ作業でも、臨機応変に、即時対応できるようになれば、その繰り返しが新たな受注につながってくれるかもしれません。ここでは、曖昧な複数の条件でデータを抽出するExcelの使い方の例について記します。

曖昧な条件での問合せ実務で必要になるデータ抽出例

下図のような、1,000行レコードの売上明細を持つ会社があるとします。

このような売上明細データで、社内では、商品別や地域別に売上集計を行いますが、苗字や住所の一部だけで、特定のデータを尋ねられる際にも、この同じデータを使って、曖昧な複数の条件で検索・抽出できるようになります。例えば、期待されるのは、以下のようなデータ抽出例です。

曖昧な条件抽出例1

「大阪」と「やまもと」さんという苗字だけで過去の売上明細を抽出する例です。顧客名の読み仮名に「やまもと」、住所に「大阪」と入力することで、蓄積されたレコードが1000行あっても、該当の情報がすぐに表示されるようになればとても便利です。

曖昧な条件抽出例2

「大阪」の「やま」が名前に含まれるお客さんの記録を抽出する例です。「やま〇〇」さんだったか、「〇〇やま」さんだったかが不明でも、わかる情報だけを入力することで、関係のありそうなデータをすぐに抽出してくれれば、絞り込んだデータから、目的の情報がつかみやすくなります。

曖昧な条件抽出例3

「年」「月」と「なか」という名前の一部だけで抽出する例です。売上などの年月が分かる場合は、望む情報にもっと早くリーチできることもあります。

年月、顧客名ふりがな、住所の一部から、該当データを抽出するしくみの例を紹介していますが、検索項目を郵便番号やメルアドなど、その他の蓄積されたデータの項目を参照するようにすれば、様々な実務に、より適した検索ができるようになるはずです。レポート作成のための集計方法ではなくて、問い合わせや、ちょっとした疑問に即応できるようにする、Excelという道具の活用方法を考えようという訳です。

Excel活用、曖昧な条件でデータを検索・抽出する例

小企業では、Windows10ベースでExcel2016を使うところがあります。FILTER関数が使えないのは残念ですが、ここでは、SEARCH関数、COUNTIF関数、MATCH関数、INDEX関数などを使うしくみの例となります。データを検索・抽出するワークシートと蓄積したデータに作業列を加えたワークシートのふたつのExcelワークシートを用意して、曖昧条件の検索・抽出を行います。

• 「検索」Excelワークシート
  • グリーンの枠検索条件の値を入力
    • グリーンの枠：曖昧な条件を入力
    • ブルーの枠：条件に該当するデータを表示

• 「売上明細」Excelワークシート
  • イエローの枠：売上データ（H列、I列は、「注文日付」を参照して年と月の値を表示）
  • ブルーの枠：数式が入力された列

以上ふたつのワークシートを利用します。

検索Excelワークシート

上記で表示した「検索」ワークシートの画像は、実務で使うために、A列、B列、6行目の行が非表示になっています。全ての行列を表示すると下の画像のようになっています。

A列と6行目の行の値は、1,2,3・・・と数値が入っているだけです。

カレントセルB8の中身が、数式バーに表示されています。

• IFERROR(〇〇,””)
  • 〇〇の結果がエラーであれば何も表示しない
• MATCH(A8,売上明細!$F$2:$F$1001,0)
  • A8セル＝図では１という値を、明細シートのF列２行目から1001行目の範囲と照合して、全く同じ値となる、最初のセルの行数を表示する

Ｃ8セルの中身は上図の数式バーの通り=IF($B8=””,””,INDEX(売上明細!$G$2:$T$1001,$B8,C$6))

• IF($B8=””,””,〇〇)
  • 〇〇B8＝項目名「行数」の値がなければ、何も表示しない
• 〇〇＝INDEX(売上明細!$G$2:$T$1001,$B8,C$6)
  • 売上明細シートのG2～T1001（既存の販売売上データ）全体を参照し、B8のセルの行数（この例では590行目）で、C6セルの列数（この例では１列目）にある値を、売上明細シートから抜き出して表示

D8、E8・・・は、このC8の数式をそのままコピペしています。同じ列の6行目を参照するので、数値によって、売上明細シート上で参照する列数が異なります。

売上明細Excelワークシート

A列からF列までは数式が組まれていて、G列から右側が既存の売上データです。但し、H列にYEAR関数、I列にMONTH関数を使って、G列の注文日付から年と月の値を数値として表示させています。

A591セルの数式は、=IFERROR(IF(検索!$D$4=H591,1,0),0)です。

• IFERROR(〇〇,0)
  • 〇〇の結果がエラーであれば何も表示しない
• 〇〇＝IF(検索!$D$4=H591,1,0)
  • もし、検索シートのD4の値（この例では2023）が、H591（売上明細シートH591の値＝2023）と同じなら１を、一致しないなら０を表示
• 売上明細シートB591セルも同様の数式で、異なるのは、月に関するセルを参照するということだけです。

C591セルの数式は、=IFERROR(IF(SEARCH(検索!$H$4,売上明細!L591)>0,1,0),0)

• IFERROR(〇〇,0)
  • 〇〇の結果がエラーであれば何も表示しない
• 〇〇＝IF(●●>0,1,0)
  • ●●が０よりも大きい値なら、１を、そうでなければ０を表示
• ●●＝SEARCH(検索!$H$4,売上明細!L591)
  • 検索シートのH4の文字（この例では、や）が、L591の文字の頭から何番目に出てくるか、結果として数値を表示します。つまり、文字が含まれるなら値は０よりも大きい数値として表示されます。

E列には、同じ行のA、B、C、D列それぞれの積算（掛け算）式があります。この例では、591行目は、A591xB591xC591xD591=1x1x1x1=1となります。

以上を、相対参照、絶対参照に注意しながら、必要な範囲にコピペすることで、曖昧な複数の条件で、蓄積されたデータから、条件に当てはまるデータを検索・抽出・表示してくれるExcelの道具が仕上がります。

曖昧検索のしくみExcel組立書

以上は、Excel2016、Excel2019など、これまでのExcelの関数を利用した、曖昧な条件による検索のしくみでした。Microsoft Office 365のExcelなど、新しいバージョンのExcelから使えるようになった、新しい関数を利用すると、よりシンプルに、手早く同様のしくみを作ることができます。

検索といっても、A:検索値と同じという条件、B：検索値を含むという条件の2種類があり、AかつBのデータを抽出するという複合条件にしたい場合の、新しい関数（filter関数）の使い方や、元になるデータをパワークエリーで取込む方法などを、ebookにまとめました。

「曖昧な複数の条件で検索・抽出するExcelのしくみ組立書」

検索のしくみをテーマに、これまでの関数と新しい関数を利用したしくみの作り方を、関数に慣れない方でもわかるように、細かく、具体的な操作を記しています。

日本でも海外でも、Excelのしくみは、詳しい人がいるかいないか、あるいは、個人の努力に依存することが多くあります。しかし、「曖昧な複数の条件で検索・抽出するExcelのしくみ組立書」のような、自分や自社だけの、ユニークな組立書を持つことで、関数に慣れない人でもしくみが分かるようになり、仕事の道具として誰もが利用、改良することができるようになります。Excelリテラシーの維持向上、仕事のしくみのカイゼンが、業務や仕事のカイゼンにつながっていきます。その反芻によって、自分自身を含めた、チーム全体のExcelリテラシーアップにもつながることでしょう。

どうもお疲れさまでした。