【EXCEL】条件に一致した最新データのみを抽出

質問

名前・住所・電話番号・注文日・注文内容を記入したExcelファイルを使用しています。
電話でお問い合わせがあった際、電話番号で検索し、
過去のお問い合わせのうち、一番直近のデータで住所やお名前を抽出したいのですが
電話番号を入力すると、名前と住所を一番直近のデータ１つのみ表示させる方法はありますか？
オートフィルタではなく関数を使用して、抽出したいです。

どなたか分かる方、ご指導願います。
VLOOKUPでは古いデータしか反応しなかったので困っております。

回答No.1

keithin · ベストアンサー

簡単のため，日付については上から昇順で記入できているとして。
Ａ列に名前
Ｂ列に住所
Ｃ列に電話
Ｄ列に注文日
Ｅ列に注文内容
として。（こういった具体的な姿とかも，手抜きせず情報提供して下さい）

Ｈ１に電話番号を記入するとして
=IF(COUNTIF($C:$C,$H$1),INDEX(A:A,SUMPRODUCT(MAX(($C1:$C3000=$H$1)*ROW($C1:$C3000)))),"初見")
と記入，右にコピー。

＃参考
＞VLOOKUPでは古いデータしか反応しなかったので困っております。

全体を「日付の降順で並べ替えて」VLOOKUPしてやれば，簡単に最新のデータが計算できます。
マクロ等を併用し，「新しく追記したデータを常にリストの上から挿入していく」といった手段を併用するのも，VLOOKUP関数など簡単な方策で計算できる手助けになります。

お礼

回答ありがとうございます。

顧客リストの列番号を補足させて頂きます。

注文日：E列
住所：I列
名前：Ｏ列
注文内容：Ｌ列
電話番号：Ｍ列

となっております。
（他にも社内で使っているコードや担当者名など複数使用していますが検索には使用しませんので省略します）

また抽出するファイルはこれから作成する予定で、
たとえばA2に電話番号を入力すると、B2に最新の名前、C2に最新の住所を出す、という風にするというのが目的です。

直接この顧客リストを編集することは不可能となっておりますので、
注文日に関しては古い日付⇒新しい日付という順番以外使用できません。
したがって日付の降順で並べ替えてVLOOKUPというのは選択肢としては無効となってしまいます。

まずはkeithin様のおっしゃっている方法を試してみたいと思います。
ありがとうございました。

回答No.4

MarcoRossiItaly

「オートフィルタではなく関数を使用して、抽出したい」という理由を想像すると、電話番号を指定して抽出したときに、同じ電話番号を持つ過去の多数の行が一緒に表示されて、最新の行にアクセスしにくいから、という意味かなと思いました。

そこでまず、ご質問の方針とは異なってしまうのですが、お勧めな方法から。

やはりオートフィルタは便利なので、フラグを立てることにより、先に、最新の行のみに絞り込まれて表示されている状態を標準としておきます。その状態から更に電話番号を指定。つまり 2 列で絞り込むことになります。以上により、最新の日付で電話番号が特定された行がごく簡単に表示されます。

フラグ用に追加する 1 列には、次式を記入します。日付が昇順だそうですから、行番号を利用しています。「3000」の行番号は、適当に増減させてください。

=0+(row(e2)=max(index(row(E$2:E$3000)*(M$2:M$3000=m2),)))

※ E 列……注文日、M 列……電話番号

上式は、一覧の中で同じ電話番号を持つ行のうち、行番号が最大である行のみ「1」を、それ以外の行では「0」を算出します。多くの種類の電話番号があり、それぞれの電話番号の最新の行に「1」が表示される状況となります。ですからオートフィルタでいつも「1」に絞り込んでおけば、冒頭の目的が果たせますね？

日付と電話番号が共に一致する行が複数ある場合、行番号が大きいほうしか「1」にならないことに注意してください。

私が仕事で頻繁に使っている ToDo リストも、マクロを併用してはいますが、言ってみれば上の説明と同様の仕組みです。したがって（笑）ラクでお勧めです。案件の「発生日」の他に、「期限」の列や「処理日」の列を設けていて、処理日というフラグが空欄である行のみオートフィルタで抽出した状態、を標準にしています。

日付を扱う表では、当日の日付をセルに記入するショートカットキー Ctrl+; が大活躍しますね。もしもご存じなかった場合は、お試しください。

元のご質問どおり、抽出データの表示部を別の場所、例えば別シートに設けるなら、上式をちょっと改造する感じになります。抽出の対象の表があるシート名を Sheet1 とします。また、抽出のために指定する電話番号は、別シートの C2 セルに記入します。

別シート
C1　　 電話番号
A4　　 最新度
A5～7　1～3
B4　　 名前
B5　　 =indirect("sheet1!o"&large(index(row(sheet1!$E$2:$E$3000)*(sheet1!$M$2:$M$3000=$C$2),),$A5))
C4　　 住所

※ B5 をドラッグして C5 にオートフィルした後、C5 に記入された数式中、「sheet1!o」を「sheet1!i」に書換え。
　さらに、B5:C5 をドラッグして 7行目までオートフィル。

どこに抽出データの表示部を設けるのか、抽出する表より上の辺りか、あるいは別シートにするか、ですが。上に置く場合は、抽出する表の位置が下にずれて、スクロールせずに画面に表示される行数が減ってしまうことになりますね。別シートだと、抽出する表のあるシートと抽出結果のシートを一回一回切り替えないと、見れないことになってしまいます。そうした煩わしさがあるかもしれません。

＞抽出するファイルはこれから作成する予定で、

なるべく、別シートなど、同一ファイル内で完結する仕組みをお勧めします。複数ブックをまたがると、リンクが切れないようにするなど、管理が大変になりがちです。全体の書式が殆ど変化しないファイルならまだいいのですが、「最新」データのみを…とおっしゃるくらいですから、頻繁に加工・編集されるわけですよね。格納するフォルダとかファイル名を変更したりすれば、もちろん簡単に切れてしまいます。

もしも抽出データを誰かに提供する場合もあるなら、同一ファイル内で完結しているファイルごとコピーして値複写で数式を除去し、余計なシートや行、列も全部削除してから渡せばよいでしょう。あるいは PDF 化、DocuWorks 化したものを渡すという手もなくはないですね。

回答No.3

MackyNo1

元データがSheet1にあり、別シートのA2セルに電話番号（M列のデータ）を入力した場合、最新の名前（O列のデータ）を抽出するなら以下のような関数になります。

=INDEX(Sheet1!O:O,MAX(INDEX((Sheet1!$M$2:$M$100=$A2)*ROW($2:$100),)))&""

回答No.2

tom04

こんばんは！
一例です。
↓の画像でやり方だけ説明します。
上側が元データでSheet1・下側が表示させたいSheetでSheet2とします。

Sheet1に作業用の列を1列設けます。
（今回はP列にしていますが、実際は遠く離れた使用していない列にします）

作業列のP2セルに
=IF(OR(E2="",M2<>Sheet2!A$2),"",E2)
という数式を入れこれ以上データはない！というくらいしっかりオートフィルで下へコピーしておきます。

そしてSheet2のB2セルに
=IF(COUNT(Sheet1!$P:$P),INDEX(Sheet1!$E:$O,MATCH(MAX(Sheet1!$P:$P),Sheet1!$P:$P,0),MATCH(B1,Sheet1!$E1:$O1,0)),"")

という数式を入れ隣のC2セルまでオートフィルでコピー！
これで画像のような感じになります。

※　もっと表示させたい項目は増えても構いませんが、
Sheet1の項目とSheet2の項目は同一名にしておきます。m(_ _)m