進捗管理表のある条件なかで最新日程を検索したい

質問

こんにちは。
以下の様な項目で営業の進捗管理表を作っています。
いま検索したい条件は「面談出来た最新日程」です。

色々と試して、表示できたと思うと。イレギュラーが拾えません。
例）２回目の訪問が不在だけれども、３回目の訪問が面談だった日程など

まだまだ関数を使いこなせていないです。
何方かお知恵を貸していただけませんか？
エクセルは2013です。

宜しくお願いします。

　　 Ａ　　　　　B　　　　　C　　　　　D　　　　　E　　　　　F　　　　　G　　　　　Ｅ　　　　　Ｆ　　　　　Ｇ　　　
1　 社名　　　訪問　　面・不　　面談者　　訪問日　面・不　　面談者　　訪問日　　面・不　　面談　
2　 A社　　　4/20　　　面談　　担当　　　　5/9　　　面談　　　担当　　　　5/15　　　面談　　担当
3　 B社　　　4/20　　　面談　　決裁者 　4/25　　　TEL　　　決裁者　　　5/2　　　TEＬ　　決裁者
4　 Ｃ社　　　6/6　　　面談　　　　　　　　　7/11　　　不在　　　　　　　　　　7/14　　　不在
5　 Ｄ社　　　4/5　　　面談　　決裁者　　　6/6　　　不在　　　　　　　　　　6/15　　　面談　決裁者
6　 Ｅ社　　　5/29
7　 Ｆ社　　　5/29
8　 Ｇ社
9　 Ｈ社
10　 Ｉ社　　　4/17　　　不在　　　　　　　　　4/18　　　不在　　　　　　　　　4/20　　　　不在

回答No.2

bunjii · ベストアンサー

>ところが更に上司から、Ｍ２の表示は「面談」もしくは「TEL」のどちらかの最新日程で。と追加条件が入りました。
前回の数式に一部誤りがありましたので追加条件も含めて次の数式を提言します。

M2=IFERROR(INDEX(A2:I2,,SUMPRODUCT(MAX((B2:I2="面談")*COLUMN(B2:I2)+(B2:I2="TEL")*COLUMN(B2:I2)))-1),"")
N2=IFERROR(INDEX(A2:I2,,SUMPRODUCT(MAX((A2:I2="面談")*COLUMN(A2:I2)+(A2:I2="TEL")*COLUMN(A2:I2)))),"")
N2をO2へコピーし、M2からO2までを一括して下へ必要数コピーします。

IFERROR関数はExcel 2007以降のバージョンから追加されましたのでExcel 2003以前のバージョンでは使えません。
この数式でSUMPRODUCT関数を使う理由は配列数式を特別な操作をしないで扱うためです。
基本的にはINDEX関数に与える列番号をMAX関数で抽出しています。
MAX関数の括弧内の数式は"面談"または"TEL"と記載されたセルの列番号を羅列するための配列計算式になっています。
SUMPRODUCT関数を使わない場合は数式を確定するときにCtrlとShiftを押しながらEnterキーで確定します。
結果を数式バーで確認すると=を含めた計算式が大括弧で括られています。
{=MAX((B2:I2="面談")*COLUMN(B2:I2)+(B2:I2="TEL")*COLUMN(B2:I2))} → MAX({0,3,0,0,6,0,0,9,0}) → 9

お礼

bunjiiさん

お礼が遅れてスミマセン。
頂いた式で見事に、希望通りの管理表が出来ました。
ありがとうございます。

上司も非常に気に入り、他の部署にまで自慢する有り様。
（そこの部署の管理表まで作ることになりそうです）

エクセルは奥が深いですね。
もっと上達したいなぁ。

回答No.3

tom04

こんばんは！
横からお邪魔します。

＞面談」もしくは「TEL」のどちらかの最新日程で・・・

一例です。
↓の画像で上側がSheet1で下側のSheet2に表示するとします。
尚、訪問日が何度あっても（何列あっても）対応できるようにしてみました。

↓の画像でSheet2のB2セルに
=IF(OR(COUNTIF(Sheet1!2:2,"面談"),COUNTIF(Sheet1!2:2,"TEL")),INDEX(Sheet1!2:2,,MAX(MAX(IF(Sheet1!2:2="面談",COLUMN(2:2))),MAX(IF(Sheet1!2:2="TEL",COLUMN(2:2))))+COLUMN(A1)-2),"")
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のB2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
これを列・行方向にフィルハンドルでコピー！
B列の表示形式は日付にしておきます。

※　Sheet1が空白の場合、「0」が表示されてしまいますので、
Sheet2をアクティブにし　→　メニュー　→　ファイル　→　オプション　→　詳細設定　→　次のシートで作業するときの表示設定
→　「ゼロ値のセルにゼロを表示する」のチェックを外しておきます。

これで画像のような感じになります。m(_ _)m

お礼

tom04さん

ありがとうございます。お礼が遅れてスミマセン。
目的は同じでも、様々な関数で導くことができるのですね。
奥が深い・・・。

まだまだ瞬時で適切な関数を組めず、苦戦しています。

教えて頂き恐縮ですが、ベストアンサーは２度も回答を
頂いたbunjiiさんとさせて頂きました。
ご了承下さい。

回答No.1

bunjii

貼付画像はExcel 2013で検証した結果です。
L2=A2
M2=INDEX(A2:J2,,SUMPRODUCT(MAX((B2:J2="面談")*COLUMN(B2:J2)))-1)
N2=INDEX(A2:J2,,SUMPRODUCT(MAX((B2:J2="面談")*COLUMN(B2:J2)))+1)
M2セルの書式で数値の表示形式を日付に設定します。
L2からN2まで選択して下へ必要数だけコピーします。
尚、C社の6/6で面談者が空欄なので抽出結果に0が表示されてしまいます。これを避けるため仮に？を入力してあります。

お礼

ありがとうございます！　本当に嬉しいです。
早速やってみます。

また報告させていただきます。

補足

頂いた式で大枠は理解できたつもりでした。
ところが更に上司から、Ｍ２の表示は「面談」もしくは「TEL」のどちらかの
最新日程で。と追加条件が入りました。

MAXやSUMPRODUCTのもしくはでネット検索等し、色々と試してみましたが、
数式エラーとなってしまいます。

お手数ですが、再度ご教授頂けると幸いです。