Excelで最終行と最上位のデータを常に表示させるサンプルアプリ
エクセルの表に最新データが追加されていくような場合、そのデータを常に表の先頭や所定の位置に表示させたい場合があります。Excel2021および最新のMicrosoft365で新たに使用できるようになった「XLOOKUP関数」を利用することで簡単に最終行のデータを取得することができるようになりましたのでサンプルアプリを使ってご紹介します。
最終行の値を取得するサンプルアプリ
以下に示すように表の最終行のデータを常に表のヘッダー部分に表示させるようにしています。
ヘッダーの表示箇所には「XLOOKUP関数」が埋め込まれており最終行のデータを引っ張ってくるようになっています。具体的なXLOOKUP関数の使用方法については次項の「サンプルコードの処理内容」をご覧ください。
サンプルコードの処理内容
表のヘッダー部分の各セルには下記関数がそれぞれ埋め込まれています。
No「015」が表示されているセル → =XLOOKUP(“?*“,C:C,C:C,,2,-1)
名前「加藤紘一」が表示されているセル → =XLOOKUP(“?*",D:D,D:D,,2,-1)
役職「係長」が表示されているセル → =XLOOKUP(“?*“,E:E,E:E,,2,-1)
部署「経理部」が表示されているセル → =XLOOKUP(“?*",F:F,F:F,,2,-1)
勤務地「東京都」が表示されているセル → =XLOOKUP(“?*“,G:G,G:G,,2,-1)
通勤手当を表示するセルは数字を扱っているので、下で説明しているワイルドカードで検索できません。今回は以下のように検索範囲をC列に変更しています。
通勤手当「11,500」が表示されているセル → =XLOOKUP(“?*",C:C,H:H,,2,-1)
XLOOKUP関数の引数箇所の説明は以下の通りです。
「=XLOOKUP（第1引数, 第2引数, 第3引数, 第4引数, 第5引数, 第6引数）」
第1引数（検索値）
「"?*"」とし、ワイルドカードでデータが入っている箇所を検索します。
「?」は指定した場所の任意の 1 文字と一致します。
「b?ll」では、ball 、bell、bill が検索されます。
「*」は任意の数の文字と一致します。 アスタリスク は文字列のどの場所でも使用できます。
wh* では、white や why は検索されますが、awhile や watch は検索されません。
第2引数（検索範囲)
「C:C」はC列を検索範囲として指定してします。
第3引数 (値を返す範囲)
「C:C」でC列の値を返します。数値は数値として取得することができます。
第4引数 (見つからない場合]のオプション)
第2引数（検索範囲）で該当するデータが見つからなっか場合にどのように
表示するか指定できます。
例えば次の場合「見つかりませんでした」と表示させることができます。
第5引数（比較モード）
「2」とすることで「1文字以上の文字列」を検索するワイルドカードの検索となります。
*、?、および 〜 が特別な意味を持つワイルドカードの一致を検索します
第6引数 (検索モード)
「-1」とすることで下からの検索を行っています。
下からの検索により最終行に入っているデータをもってくることができます。
XLOOKUP関数の詳細については以下のサイトをご覧ください。
https://support.microsoft.com/ja-jp/office/xlookup-%E9%96%A2%E6%95%B0-b7fd680e-6d10-43e6-84f9-88eae8bf5929
最上位のデータを取得する
最上位行のデータを取得するには、青丸で示した第6引数 (検索モード)を「-1」から「1」に変更することでできます。「1」に変更したことで上から検索するモードになり最上位行のデータがヒットします。ただし、検索する範囲を「C:C」のままにしておくと対象データの上にあるタイトル欄の値を取得してしまうので、検索範囲を「C:C」から「C6:C22」 に変更しています。
No「001」が表示されているセル → =XLOOKUP(“?“,C6:C22,C6:C22,,2,1)
名前「佐藤太郎」が表示されているセル → =XLOOKUP(“?“,D6:D22,D6:D22,,2,1)
役職「部長」が表示されているセル → =XLOOKUP(“?“,E6:E22,E6:E22,,2,1)
部署「営業部」が表示されているセル → =XLOOKUP(“?“,F6:F22,F6:F22,,2,1)
勤務地「福井県」が表示されているセル → =XLOOKUP(“?“,G6:G22,G6:G22,,2,1)
通勤手当「5,200」が表示されているセル → =XLOOKUP(“?“,C6:C22,H6:H22,,2,1)
INDEX関数とMATCH関数、またはLOOKUP関数を使用しても最上位行と最終行のデータを取得することができます。具体的な方法については以下のサイトをご覧ください。
https://scodebank.com/?p=1288