ExcelでSheet1のデータをSheet2に1時間ごとにランダム表示する方法｜NOW・INDEX関数と自動更新の注意点

Excelで「Sheet1に登録した候補の中から1件をランダムに選び、Sheet2へ表示し、1時間ごとに内容を切り替えたい」という場合は、INDEX関数と現在時刻を返すNOW関数を組み合わせることで実現できます。

ただし注意したいのは、単純にRANDBETWEEN関数やRAND関数を使うだけでは「1時間だけ同じ値を表示する」動作にならないことです。これらの乱数関数はExcelが再計算されるたびに値が変化するため、ほかのセルを編集しただけでも表示内容が変わる可能性があります。

1時間単位で結果を固定したい場合は、「現在日時を1時間単位の番号に変換し、その番号から表示する行を決める」という方法が扱いやすいです。この記事では、Microsoft 365など新しいExcelで使いやすい数式と、古いExcel向けの数式、さらに「何もしなくても毎時自動更新」させたい場合の注意点まで解説します。

Sheet1から1時間ごとに1件表示する基本の考え方

例えばSheet1のA2:A100に、ランダム表示したい文章や名前などが入力されているとします。そしてSheet2のA1に、その中から1件だけ表示したいとします。

ここでNOW関数を使うと現在の日付と時刻を取得できます。Microsoftによると、NOW関数は現在の日付と時刻に対応するシリアル値を返します。[参照] Microsoft「NOW関数」

Excelでは1日を「1」として時刻を小数で表現するため、NOW()に24を掛けて整数部分だけを取り出すと、1時間進むごとに1ずつ増える番号を作れます。

INT(NOW()*24)

例えば13時台と14時台では、この式から得られる整数が1つ違います。この「1時間ごとに変わる値」を利用して、表示する候補を決定します。

Microsoft 365・Excel 2024ならFILTERとLETを使うと便利

Sheet1のA2:A100に候補があり、途中に空白セルが含まれる可能性がある場合は、Microsoft 365などで利用できるFILTER関数とLET関数を組み合わせると扱いやすくなります。

Sheet2の表示したいセルへ、次の式を入力します。

=LET(list,FILTER(Sheet1!A2:A100,Sheet1!A2:A100<>""),h,INT(NOW()*24),INDEX(list,MOD(INT(ABS(SIN(h*12.9898))*1000000),ROWS(list))+1))

この式では、まずFILTER関数によってSheet1!A2:A100から空白を除外し、その候補の中から現在の「1時間単位の時刻」をもとに1件を選択しています。

同じ1時間の範囲で再計算しても基本的に同じ候補が選ばれ、次の時間帯になると別の候補が選ばれる仕組みです。ただしこれは厳密な乱数ではなく、現在時刻から作った値を使う「疑似ランダム」の選択です。

候補に空白がないならもっと簡単な式でもよい

Sheet1のA2:A20がすべて埋まっていて、途中に空白がないことが分かっている場合は、COUNTA関数を利用して次のように簡略化できます。

=INDEX(Sheet1!A2:A20,MOD(INT(ABS(SIN(INT(NOW()*24)*12.9898))*1000000),COUNTA(Sheet1!A2:A20))+1)

INDEX関数は指定した範囲から行番号に対応する値を取得する関数です。MicrosoftもINDEX関数について、テーブルまたはセル範囲から指定した位置の値を返す関数と説明しています。[参照] Microsoft「INDEX関数」

またCOUNTA関数は空白ではないセルの数を数えます。[参照] Microsoft「COUNTA関数」

ただし、この簡略版は候補範囲の途中に空白があると、その空白セルを拾う可能性があります。そのため、Microsoft 365などFILTER関数が使える環境なら前述の式のほうが安全です。

RANDBETWEENを使う式では「1時間固定」にならない

Excelでランダムに1件選ぶ式としては、次のような式を思いつくかもしれません。

=INDEX(Sheet1!A2:A20,RANDBETWEEN(1,COUNTA(Sheet1!A2:A20)))

この式そのものは、Sheet1の候補からランダムに1件選択する用途では利用できます。しかし「1時間おき」という条件には向いていません。

MicrosoftのExcel再計算に関する資料では、NOWやRANDBETWEENなどは再計算時に再評価される揮発性関数として扱われています。[参照] Microsoft「Excel Recalculation」

そのためRANDBETWEENを使うと、1時間経過していなくてもExcelが再計算されたタイミングで別の候補へ変わる可能性があります。「10:00～10:59はA、11:00～11:59はB」のように時間帯ごとに固定したい用途では、現在時刻から選択位置を計算する方式が適しています。

重要：NOW関数は時計のように毎秒自動更新されるわけではない

ここは特に注意が必要です。NOW関数を使ったからといって、Excelを開いたまま何も操作していない状態で、10:59から11:00になった瞬間に必ずセルが自動更新されるとは限りません。

MicrosoftはNOW関数について、値が変化するのはワークシートが計算されたときであり、継続的に更新されるわけではないと説明しています。[参照] Microsoft「NOW function」

例えば10:30にファイルを開いて、その後Excelを一切操作せず11:30まで放置した場合、表示が10時台の結果のまま残る可能性があります。11時台になった後にセルを編集したりF9キーで再計算したり、ブックを開き直したりするとNOWが再評価され、その時間帯に対応する候補へ変わります。

つまり「数式だけで1時間単位の表示を決める」ことはできますが、「Excelを放置していても毎時00分に必ず更新するタイマー」としてNOW関数だけを使うことはできません。

毎時00分に自動で切り替えたいならVBAなどが必要

Excelを開きっぱなしにして、「13:00になったら自動変更、14:00になったらまた自動変更」という動作を確実に行いたい場合は、数式だけではなくVBAなどで再計算を定期実行する方法を検討します。

デスクトップ版Excelでは、VBAのApplication.OnTimeを利用して指定時刻に処理を実行する仕組みを作ることができます。例えば1時間ごとにSheet2を再計算させれば、NOW関数を使った式も更新されます。

ただしマクロを使用する場合はブックを「Excelマクロ有効ブック（.xlsm）」として保存する必要があり、組織や学校のPCではマクロ実行が制限されている場合があります。そのため、単純な用途なら「ファイルを開いたときや操作したときに時間帯に応じて更新されればよい」のか、「完全なタイマー動作が必要なのか」を先に決めるとよいでしょう。

同じ時間帯で同じ結果になる仕組み

時間帯固定式のポイントは、NOW()そのものを乱数として使うのではなく、INT(NOW()*24)によって「現在が何番目の1時間なのか」を表す整数を作ることです。

例えば10:05と10:55ではNOW()の値自体は異なりますが、INT(NOW()*24)は同じ整数になります。そのため、そこから計算される候補番号も同じになります。

11:00を過ぎてExcelが再計算されると整数が1増えるため、別の候補番号が計算されます。これにより「再計算するたびにランダム」ではなく「1時間を1つの単位として候補を変える」という動作を作れます。

同じ候補が2時間連続で出る可能性はある

ランダム表示という性質上、候補が多くても同じ内容が連続して選ばれる可能性を完全には否定できません。例えば候補が「りんご・みかん・バナナ」の3件なら、10時台にりんご、11時台にもりんごという結果になることはあり得ます。

「毎時間必ず前の時間とは違う項目を表示したい」「全候補を1回ずつ表示してからシャッフルしたい」という要件になると、単純なランダム抽出とは別の仕組みが必要になります。

特に「一度表示した候補を一定期間出さない」という処理では、過去に何を表示したかを保存する必要があります。その場合は数式だけより、VBAやOffice Scriptsなどを使って履歴を管理するほうが適しています。

複数列をセットでランダム表示したい場合

Sheet1が単純な1列ではなく、「商品名・説明・価格」のように複数列で1件のデータになっているケースもあります。

例えばSheet1のA2:C100を1行単位で選びたい場合、Microsoft 365ではFILTERで空白行を除外し、INDEXの行番号を時間から決めることで、選ばれた行の複数列をまとめてSheet2へ表示することもできます。

この場合は「A列だけランダムに選んでB列とC列を別の乱数で選ぶ」のではなく、一つの行番号を決めて、その行のA～C列をセットで取得することが重要です。そうしないと商品名と価格などの組み合わせがずれてしまいます。

目的別のおすすめ方法

単に「Excelを開いたとき、その時刻に応じた1件を表示したい」のであれば数式だけで十分です。一方、デジタルサイネージのようにExcelを長時間開きっぱなしにし、必ず1時間ごとに自動変更したい場合はタイマー処理が必要になります。

まとめ：1時間ごとに固定するならNOWから時間単位の番号を作る

Sheet1の候補からSheet2へ1時間ごとに1件をランダム風に表示したい場合は、単純にRANDBETWEENを使うより、NOW関数から1時間単位の値を作ってINDEX関数の行番号を決める方法が適しています。

Microsoft 365などでSheet1のA2:A100を対象にするなら、空白を除外できる次の式が一つの実用例です。

=LET(list,FILTER(Sheet1!A2:A100,Sheet1!A2:A100<>""),h,INT(NOW()*24),INDEX(list,MOD(INT(ABS(SIN(h*12.9898))*1000000),ROWS(list))+1))

この式なら同じ時間帯では同じ結果になり、時間帯が変わってExcelが再計算されると別の候補が選ばれます。

ただしNOW関数は時間の経過だけで常時再計算される関数ではありません。Excelを何も操作せず開きっぱなしにしていても毎時00分に必ず切り替えたい場合は、VBAなどで1時間ごとに再計算を実行する仕組みを追加する必要があります。「時間帯に応じて表示する」のか「時計どおりに自動更新する」のかを分けて考えることが、正しい仕組みを作るポイントです。