［INDEX関数MATCH関数］フォームから送信した最新の記録を表示したい

Question

前提・実現したいこと

社員Ａさんがお昼に体温が上昇した場合に再度フォームを送ったら最新の体温が一覧に表示されるようにしたいです。

Googleフォームから体温を記録し、スプレッドシートに反映させています。
別のシートにINDEX関数とMATCH関数を使って社員全員の体温を確認できるようにしています。

=iferror(INDEX('フォームの回答01'!$E:$E,MATCH($E25 & F$3,'フォームの回答01'!$C:$C & 'フォームの回答01'!$A$2:$A,0)),"未登録")

フォームの回答シートのA列はタイムスタンプ、C列は氏名、E列は体温
現在のシートのE列は氏名、F3には「TODAY*」のシリアル値
が入力されています。
「名前」と「日付」が一致したら「体温」を反映し、一致しなければ「未登録」という数式です。

タイムスタンプから最新の情報を抜き出したいです。

###試したこと
MAX関数が日付にも有効と知り、INDEX・MATCH関数に組み込んでみましたが、うまくいきませんでした。

=iferror(INDEX('フォームの回答01'!$E:$E,MATCH(MAX($E25 & F$3),'フォームの回答01'!$C:$C & 'フォームの回答01'!$A$2:$A,0)),"未登録")
これだと未登録になってしまいました。
タイムスタンプにMAXが適用させたいので「MAX'フォームの回答01'!$A$2:$A」もやってみましたが、こちらは一番上に記録されていた前日の体温が表示されてしまいました。

MAX関数で調べるとMAXを２か所入れる数式が出てきたので試してみましたがうまくいかず、MATCH関数で「氏名」と「日付」の二つになっているのが自分でも上手く変換できていないのかもしれません。

###補足
社員一覧に最新の体温が表示されればいいので、INDEX関数やMATCH関数にこだわりはありません。まったく別の関数でも大丈夫です。
よろしくお願いします。

Answer 1

社員一覧に最新の体温が表示されればいいので、INDEX関数やMATCH関数にこだわりはありません。まったく別の関数でも大丈夫です。

おそらく、コロナ禍対応の１つで「毎日の社内の検温記録」の仕組みを用意されているでしょうね。

INDEXやMATCH を使用せず、query関数で、本日のタイムスタンプのみ表示させてみてはいかがでしょうか？

私の担当している企業様でも同様のニーズがあり、現在、Google フォームを使って、
記録結果をGoogle Site で見れるようにしてあります。

このとき、、query関数を採用しました

これが単純な発想でして、
「フォームの投稿結果が記録されるシートとは別にシートを用意して
本日のタイムスタンプのみ、氏名順 かつ　タイムスタンプ順でソートして抽出表示する」というものです

Query 関数なら

1.本日のタイムスタンプのみ抽出が可能

where C = date '"&TEXT(TODAY(),"YYYY-MM-DD")&"'　

2.任意の列でソートが可能

select A,B,C order by Bコード

3.任意の列に任意の列のみチョイス可能　

select A, C order by Bコード

運用開始してみると、必要十分な結果だったので、そのまま数か月利用しています。

ただし、この方式では、未登録を判別することはできません。

Vlookup や Match を利用することも考えましたが、実用性を考えると、たいして重要ではないことに気づいたため、未登録を検出することはやめました。

これは、「未登録であっても、実際には有給休暇で出社していない可能性」があり、
きちんど処理するには、別途、出勤簿データと照らし合わせてやる必要が生じる。

ことや

「そもそも、全員が測定結果を記録するので、未登録の状態は時間経過で解決する」

という考えです。

その変わりに、スプレッドシートには、現在までの報告件数と社員総数を表示させ、どのくらい報告が上がっているのかわかりやすくしました。

これなら、もし全員出社していた場合、報告件数と社員数が一致するからです。

また、氏名の列で値が重複したものを条件付き書式で、背景色を変えるようにしました。
パッと見て、測定結果が変わった人を見つけやすくするためです。

本日の分だけ表示させる。というアイデアは、ほかの用途でも利用できると思いますので、ご紹介させていただきました。
参考になれば幸いです。

Comments

[Yosakoi_D]

Yoshi88さんありがとうございました。
別の関数でもいいと書きつつ、query関数を使ったことがなく躊躇していたところ、別の方の回答でできました。
また別の機会があれば、query関数を使ってみたいと思います。

素早い回答ありがとうございました。

Answer 2

フォームの回答01と同じスプレッドシートに「COPY」という名前のシートを作成します。

COPYシートのA1セル、A2セルに、それぞれ次のような式を入れます。

「COPY」シート
A1セル：　={'フォームの回答01'!A1:E1}
A2セル：　=sort('フォームの回答01'!A2:E1000,1,false)

さらにCOPYシートのF1セルに「key」と入れ、
F2セルに下記の数式を入れます。

「COPY」シート
F2セル：　=if(A2<>"",C2&date(year(A2),month(A2),day(A2)),"")

（F列3行目以降は、F2セルの数式をコピーする）

これでCOPYシートに、フォームの回答01シートの内容がタイムスタンプ降順に並び、
F列には、名前＋日付シリアル値のキーが並びます（画像）

　

体温一覧を表示するシートは下記のように設定します。（ここでは「一覧」という名前のシートであると仮定します）
質問文に記載の通り、氏名はE列に、日付シリアル値（=TODAY()）はF3セルにあるという前提です。

「一覧」シートで、体温を表示したい列（ここではG2以降とします）に
下記の数式を入れて下の行にコピーします。

「一覧」シート
G2セル：　=IF(ISERROR(INDEX(COPY!$E:$E,MATCH($E2&F$3, COPY!$F:$F, 0))),"未登録",INDEX(COPY!$E:$E, MATCH($E2&F$3, COPY!$F:$F, 0)))

（一覧シートのイメージ）

---

補足：
同じ日に同じ人が再度フォームから体温を送信した場合でも、COPYシートにはタイムスタンプの降順（新しい順）に並んでいるため、
一覧シートには最新の体温データが表示されることになります。

Comments

[Yosakoi_D]

ありがとうございます！
qnoirさんのやり方でうまくいきました。
上が最新の情報が来るようにしないといけないんですね。