以前、バイト先でハンドスキャナで読み取ったシリアルを出荷先別にシートに分けて出力するというツールをVBAで作ったのですが、データベースへの上書きフローの箇所で重複データを量産してしまうというバグを出してしまいまして、、、ひとまず読み取ったデータは間違ってないので、データベースの重複データを整理しながらシート別に分けて出力するものをパッパッと関数だけで作ってリカバーしたんですね。
で、バグの原因もループ処理の判定ミスだったんですぐに修正して、使えるようにしたんですが、、、、、社員さんがバグにびっくりしちゃって、もう使わないってなっちゃんです（笑）まあ、しょうがないですね。。。。ツール自体は自分でも会心の出来栄えで、現場作業員の間では「すごい楽になった！」「考えずにスキャンしても間違えない！」と、とても好評だったんですが、、、、残念です。もったいないですが削除しました（泣）
で、そのときリカバーに使った、関数だけでデータ一覧から必要な分だけデータを抽出し、別のシートへ上詰してリスト表示する、、、、これぐらいは記事にして残してやろうかなって思いまして、、、、
悔しいので（笑）
さて、このような動きをさせる状況って結構ありますよね。販売データ一覧から請求書や見積書にしたりと、、、領収書なんかもそんな感じですかね。。。
今回、リカバーで急遽つくったのは、出荷データを出荷先別に分けて出力するっていう動きでした。
どうでしょうか？こういった場面は、みなさんも何度か対応したことあるのではないでしょうか？多分、いろいろ方法ありますよね。
私の過去記事でも可変する範囲を使ってリスト化する話を書いてます。「関数だけでランキングを作成する」なんかはいい例だと思います。ただ、この可変する範囲を使う方法だと、範囲設計を考えないといけないから、ちょっと面倒か。。。
今回は、時間も余裕もない中、簡単に、すばやくリカバーしなければならない！という危機的状況でしたので、もっと原始的で簡単な方法にしてます。なので、「こんなアイデアもあるよ！」ってことでみていただければと思います。
それでは、簡単なサンプルを作りながら説明していきます。
データを次のように準備しまして、
次のように、出荷日、出荷先を入力して指定すると、それに合ったリストが表示されるってやつを作ります。
主に表示に使う関数はINDEX関数とMATCH関数ですが、今回のポイントは、COUNTIFS関数の使い方です。
検索してヒットした項目を上詰めしてリスト化するという事を、そのまま考えると、リストを上に詰めるには？、、、リストを移動させるには？、、、、と、いろいろ難しそうな問題がありそうに思えるのですが、実は、表示される結果を１行ずつ考えてみるとそうでもないことがわかります。
表示リストの１行目にくるのは、、、「１番目」にヒットしたもの。
じゃあ、２行目にくるのは？
はい、正解！
「２番目」ですね! （笑）
そうなんです。ヒットした順番がわかれば表示する場所が決定できるわけです。
そこで使う関数が、COUNTIFS関数です！この関数なら検索条件が複数あっても、条件にヒットしたものを数えることができます。
ただ、順番を出すためには、使い方に工夫が必要です。
普通に、データ全体から条件に合うものを数えちゃうと順番はわかりません。
なぜなら、条件が一致するものが3つあるとして、それぞれを全体から数えると、どれも同じ「３」になってしまいますね。これだと、どれが何番目になるのか？は、わからないです。
そこで、カウントする範囲を自分から上だけにします。自分より下は数えないのです！
ということは、、、関数で指定する範囲は、行が変わるごとに全部違う必要がある。。。。ということになります。
「えっ？！いちいち関数の範囲を変えないといけないなら、とんでもなく面倒なんですけど、、、、」
大丈夫です!
参照形式を変えればオートフィルでいけます。ほら、よく累計出す時に使う方法ですよ！知っているかたは多いんじゃないですかね？
次のように入力してオートフィルします。
このようにセルの範囲の始まりは絶対参照で固定し、終わりは相対参照で動くようにします。こうすることでオートフィルするだけで、下方向に範囲が自動で増えていきます。どうですか？「ああ、あれかぁ！」って思いました？！（笑）
はい、これでそれぞれのデータが何番目にヒットしたのか？が、わかるようになりました。このヒットした順番＝表示行なので、データの項目が「行」になっているんですね。
次は、リストに表示させましょう。
表示させるシートから、条件を見返してみます。
このように下方向に行が移動するにつれて、「行数」は同じように増えていきますが、「出荷日」「納品先」「行数」の3つの条件が一致するものを表示すればいいわけですね。
では、まず、データから整備します。
検索条件は３つありますが、１つずつ条件判定させる方法はとりません！
３つの条件がすべてそろった判定用の列を作ってしまいましょう！
とても簡単です。次のようにします。
はい、このように判定する条件をただ文字列としてつなげるだけです。「＋」を使って、数字として計算してしまうと、期待した結果になりませんので注意してください。
この方法なら判定条件が文字でも同じ方法が使えますね。まあ、英文字の場合は、全角半角、大文字小文字の対策は必要ですが、、、
これで判定する目印は完成しました。次は表示側です。
考えなくてはいけないのは、この３つの条件の中で変動する「行数」です。他の２つは固定なので指定するだけでいいですね。
さて、どうやって変動する「行数」を関数の中で表現するか？ですが、、、、、よく見てください。「行数」がわかればいいんです！
そうです！ROW関数で「行数」はわかりますね！
なので、次のようにすれば解決です。
うわっ、、、、細かくなっちゃいました。これ見てわかるでしょうか（笑）
INDEX関数は列と行をそれぞれ指定できます。
行の判定には、先ほどデータ側で整備した、文字列結合している判定列を使います。
この判定は、MATCH関数で行いますので、比較するデータ側と同じように、式の中で文字列を作る作業をしています。このとき、変化する「行数」を、ROW関数で得て、実際の行数[6]と表示行[1]のズレを「−５」して合わせてます。
そして、INDEXに指定する列は、指定範囲を表示範囲に合わせていますので、そのままCOLUMN関数を使うだけでOKというわけです。
で、大外にExcelのお約束処理です。検索一致の処理は、VLOOKUPも含めなんであれ、一致しない時はエラー表示されます。表示には邪魔なので、IFERROR関数で消します。
以前の記事で、「エラーの原因がわかる場合はなるべく直接消す対応をした方がよい」と書いたことがあります。全くそのとおりで、今回も一致しなかったらという条件分岐を大外につけるのが一番よいです。ここで一致するものがないことを判定するには、MATCH関数の中で作る文字列がデータI列の中にいくつあるかをCOUNTIF関数で数え、戻り値が０なら一致するものがないことになりますから、これを大外につけてやればいいですね。
、、、、、ですが、今回は急ぐ必要がある！！、、、、ということでした（笑）
なので、「エラーは全部回避されちゃうから気をつけてないといかんよ」って思いながら操作します（笑）私は、こういうときは、ちょいちょい結果を確認するようにしてます（笑）
さて、いかがでしたでしょうか？同じような動きをさせようと悩んでいた方にとって解決策の一つにでもなったでしょうか？？そうであれば嬉しいです。消滅してしまった私のツールも成仏するでしょう（笑）
自分で言うものなんですが、消してしまったツールは、本当によく考えられたいいツールだったんですよ。。。
ハンドスキャナが入力デバイスになるので、マウス、キーボート操作をほぼさせないように、操作用バーコードを使って動くようにしたり、シリアルの重複スキャンを自動チェックしたり、スキャナからの入力が文字化けしたら自動キャンセルしたりと、、、、Excelのわりに動きもスムーズだし、、、、、ただ、入力データを保存するときのループ処理を間違えちゃったんですよね。。。。しかも、レアケースのときだけ出るバグだったんで、デバッグをすり抜けちゃったんですよ。。。。あーあ、入力データの正確性にはかなり自信あったんで、出力だけの問題なら修正は簡単でどうにでもなったのになぁ。。。。。
まあ、、、、いいか。
それでは、愚痴も含め、ここまで読んでいただきありがとうございます。
