曖昧な複数の条件でデータを抽出する方法：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リテラシーアップにもつながることでしょう。

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

アナタの仕事や経営のためのExcel活用プロモーティング

Excelの使い方を習うのは、今の自分の仕事や経営に必要な集計や一覧表を考え、そのための作り方や使い方を、実際にしてみて、結果を見ながら改善するのが一番です。VBAなどプログラム言語を使わず、Excelの標準機能、関数、ピボットテーブル、パワークエリーなどを利用するだけでも、ほぼ自分の思う通りの自分だけのしくみ作りが実現できるでしょう。

業務の組立て方やExcelの使い方に迷い、悩む方があれば、アイプロモティストが後押し致します。ご連絡下さい。アナタの今の仕事のためにExcelのしくみ作りをプロモーティングします。

Excel外貨記帳簿記データベース　Kindle出版