Excel　数式作成について（文字列一致・日付または文字列の最大値のデータ取得）

前提・実現したいこと

食事の主食に何を食べるか、
変更履歴を蓄積したデータシートから
別シートに個人別の最新データを一覧表示するのが目的です。

変更履歴・人数とも多いためフィルタでのソートやVBAは使わず、
関数のみで実現したいと思います。

※「変更日」が同じデータが複数ある場合、
　「変更開始」の時間帯が後の順つまり
　夕食・昼食・朝食の順で優先表示
　（順位付けのために先頭に数字コードを振っています）

発生している問題・エラーメッセージ

シート２のA列の「氏名」を入力すると
「最終変更」「変更開始」「主食」を自動取得する数式を作成したいです。
上図の赤字部分に入力する数式が作成できず悩んでいます。

試したこと

■シート2のB2に
=MAX(IF(T_履歴[氏名]=[@氏名],T_履歴[変更日],""))　と設定し、
氏名が一致する最新日付を取得できましたが
C2,D2に設定すべき数式が分かりませんでした。

■「変更日」と「変更開始」を連結した「作業列」を作成

作業列の数式
：TEXT(変更日,"yyyy/mm/dd")&" "&変更開始

　氏名が一致する作業列の降順で最新のデータ1件のみを取得できればと思い
どこかのサイトを参考に下の数式を作ったのですが、
「氏名が一致する」という条件をこの数式にどう組込めばいいかわかりませんでした。

　=INDEX(T_履歴[作業列],MATCH(COUNTA(T_履歴[作業列]),INDEX(COUNTIF(T_履歴[作業列],"<="&T_履歴[作業列])+ISTEXT(T_履歴[作業列])*COUNT(T_履歴[作業列]),),0))

できる限りシンプルな方法で実現できればと思っております。
ご教授いただけますと幸いです。よろしくお願いいたします。

行動規範の内容に同意します

回答2件

ベストアンサー

作業列を更に追加して対応してみました。

★シート1
※D列に「作業列2」を追加

C列：作業列
数式：=VALUE(TEXT([@変更日],"yyyymmdd")&LEFT([@変更開始],1))

D列：作業列2
数式：=[@作業列]&[@氏名]

★シート2
※こちらも作業列をB列に追加しています。
数式が長くても良ければ作業列なしでも問題ありません。
表の形はシート1に合わせています。

B列：作業列
数式：=SUMPRODUCT(MAX((T_履歴[氏名]=[@氏名])*T_履歴[作業列]))

C列：最終変更日
数式：=INDEX(T_履歴,MATCH([@作業列]&[@氏名],T_履歴[作業列2],0),MATCH("変更日",T_履歴[#見出し],0))

D列：変更開始
数式：=INDEX(T_履歴,MATCH([@作業列]&[@氏名],T_履歴[作業列2],0),MATCH("変更開始",T_履歴[#見出し],0))

E列：主食
数式：=INDEX(T_履歴,MATCH([@作業列]&[@氏名],T_履歴[作業列2],0),MATCH("主食",T_履歴[#見出し],0))

Excelのバージョンによっては違うやり方もあるかもしれません。
よければバージョンも記載してくださいね。

投稿2020/01/17 03:19

radames1000

総合スコア1923

ntm

2020/01/17 08:34

教えていただいた数式で実現できました！ C列：作業列数式：=VALUE(TEXT([@変更日],"yyyymmdd")&LEFT([@変更開始],1)) この発想がありませんでした。また、SUMPRODUCTの使い方も大変勉強になります。バージョンの記載忘れ、失礼いたしました。 Office2013～最新版まで混在する環境のため、 Office2013で実現できるご回答をいただいて助かりました。数式の可読性との兼ね合いでどこまで作業列を利用するか検討したいと思います。とてもご丁寧に分かりやすく教えていただき、本当にありがとうございました。

行動規範の内容に同意します