まりふのひと

Excel2021のXLookup、COVID-19／田布施町内新規感染者数で使ってバージョンアップもした

Excel

　9月７日の 窓の杜「いまさら聞けないExcelの使い方講座」に「XLOOKUP関数はもう常識！ VLOOKUP関数から切り替えるべき理由」が載った。

forest.watch.impress.co.jp

◆XLOOKUP関数の基本
　構文を見てみましょう。引数は6つありますが、最初の［検索値］［検索範囲］［戻り範囲］の3つを指定すれば動作します。4つめの引数［見つからない場合］は検索値が見つからない場合に表示するメッセージなどを指定できます。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)
◆XLOOKUP関数の構文
　検索文字列があるセル範囲を指定する［検索値］、検索対象のセル範囲［検索範囲］、対応する値を取得するセル範囲［戻り範囲］の3つを指定すれば動作します。

  • ［一致モード］は［検索値］との一致の判定基準です。「0」または省略で「完全一致」です。完全一致での検索がほとんどだと思いますので、省略と覚えてしまっていいでしょう。
  • 一般的な用途で［検索モード］を気にする必要はありません。「1」または省略で先頭から末尾に向かって検索します。

初代「田布施町内新規感染者数」

　Excel2021で作っている新型コロナウィルスの「田布施町内 新規感染者数」（右図）
個人的ではあるが、毎日使っているものを不具合がないのに VLookupをXLookupに変えるのは抵抗がある...
幸か不幸か台風14号で外に出られない日が続いたのを機に、作成手順を作る積りで変えることにした。

XLookup化のための準備

　この表は VLookupを多用しているため、引数の［範囲］は「名前の定義」で行っていた。

VLookup関数の構文：=VLOOKUP(検索値,範囲,列番号,検索方法）

XLookupの引数［検索範囲］は VLookupの［範囲］とは異なり、転活用が利かない。（たぶん）
更に［戻り範囲］もあるので、新たに「名前の定義」を行った。
　表のソースは「データ」シート

にあるので、名前を定義を追加した。

関数 引数 定義の名前 範囲 備考
VLookup 範囲 発生件数 =データ!$B$5:$C$238 何れ削除する
XLookup
　		 検索範囲
戻り範囲		 発生日
件数		 =データ!$B$5:$B$238
=データ!$C$5:$C$238		 　

≪変更例≫

  • 変更前：=IFERROR(VLOOKUP(B3,発生件数,2,FALSE),0)
  • 変更後：=XLOOKUP(B3,発生日,件数,0)

田布施町内 新規感染者数の作り方

　（メモ書き）

1行目
  1. セルB1：タイトル（太字）
    • セルB1～H1 を結合して中央揃え
2行目
  1. セルA2：セルB3の項目名で任意
  2. セルB2～H2：1（日曜日）～7（土曜日）の weekday関数の戻り値
    • セルの書式設定 aaa、日曜日の赤色は、フォントの色で設定。
3行目
  1. セルA3：第1週（セルB3～H3）を決める日付を yyyy/m/d で入力する。
    • 強いて日曜日の日付を入力する必要はなく、第1週に入る日付でよい。
      例えば "2022/8/10" と入力すると、セルB3は 8/7 になる。
  2. セルB3： "=$A$3-WEEKDAY($A$3)+B2"
    • セルの書式設定は mm/dd
    • フォント：8pt、フォントの色は日曜日のみ赤、左揃え、斜体
  3. セルC3～H3：セルB3のフィルハンドルで書式なしコピー
    • フォント：8pt、左揃え、斜体
4行目
  1. セルB4： "=XLOOKUP(B3,発生日,件数,0)"
    • セルの書式設定は #（0は表示しない）
    • 旧は、 "=IFERROR(VLOOKUP(B3,発生件数,2,FALSE),0)" としていた。
  2. セルC4～H4：セルB4のフィルハンドルで書式なしコピー
5行目
  1. セルB5： "=B3+7"
    • セルB3の一週間後
  2. セルC5～H5：セルB5のフィルハンドルで書式なしコピー
6行目
  1. セルB6： セルB4をコピーし、貼り付け
  2. セルC6～H6：セルB6のフィルハンドルで書式なしコピー
7行目
  1. セルB7：セルB5をコピーし、貼り付け
  2. セルC7～H7：セルB7のフィルハンドルで書式なしコピー
8行目
  1. セルB8： セルB6をコピーし、貼り付け
  2. セルC8～H8：セルB8のフィルハンドルで書式なしコピー
9～18行目 以下
  • 9行目：7行目に準ずる。
  • 10行目：8行目に準ずる。
  • 11行目：9行目に準ずる。
  • 12行目：10行目に準ずる。
  • 13行目：11行目に準ずる。
  • 14行目：12行目に準ずる。
  • 15行目：13行目に準ずる。
  • 16行目：14行目に準ずる。
  • 17行目：15行目に準ずる。
  • 18行目：16行目に準ずる。
19行目（注釈）
  1. セルB19： "■は前の週の同じ曜日より上回った日"
    • セルB19を選択し、数式バーの ■ を選択⇒フォントの色を橙にする。
    • セルB19～H19 を結合して中央揃え
関数の確認

　Excelの オプション⇒詳細設定 で ☑計算結果の代わりに数式をセルに表示する にチェックを入れ、VLookup が XLoopup に代わっているか確認した。

  • E列以降はオートフィルでコピーしているので省略した。
条件付き書式

　感染者数が「前の週の同じ曜日」より上回った日が判るようにセルに色を付ける処理。
今回は関数の変更なので、
入力する式は "=B4-XLOOKUP(B3-7,発生日,件数,0)>0"

  1. 対象セル（感染者数を表示するセル）を選択する
    　やってみて判ったことであるが‥‥
    • B列～H列の 4,6,8,10,12,14,16,18行目を下から（18行目→4行目）選択する。
      • 最後にセルB4が選択された状態にしないと、次の「式」がうまく動作しない...
  2. ホーム タブ⇒条件付き書式⇒ルールの管理 と選択し、
  3. ［新規ルール］をクリックする。
  4. 書式ルールの編集：
    1. 「数式を使用して、書式設定るセルを決定」を選択し、
    2. 「次の書式を満たす場合に値を書式設定」に
      　 "=B4-XLOOKUP(B3-7,発生日,件数,0,FALSE)>0"
      を入力する。
    3. ［書式］をクリックし、
    4. 塗りつぶし タブを選択する。
    5. 背景色を選択し、
    6. ［OK］
    7. 書式ルールの編集に戻ったら、［OK］で閉じる。

　もっと上手い方法があると思う‥‥ が、今は結果を優先する...