Microsoft Excel でタイムスタンプから最初と最後の時刻を返す方法

Microsoft Office Excel アプリケーションメニュー — 画像: PixieMe/Adobe Stock

コールセンターを運営していて、着信者と通話時間を追跡している場合、その情報をすべてMicrosoft Excelに保存できます。シートには発信者IDとタイムスタンプ値が保存されます。

しかし、通報は毎日リストを印刷するだけでは簡単ではありません。なぜなら、発信者は1日に何度も電話をかけてくる可能性があるからです。だからこそ、情報を必要とする人にとって意味のある情報となるのです。

このチュートリアルでは、タイムスタンプとは何か、そしてExcel関数のMIN()とMAX()を使ってタイムスタンプからその日の最初と最後の通話を返す方法を学びます。そして、各発信者の最初と最後の通話を返すグループ化されたレコードセットを作成します。

Windows 10 64ビットシステムでMicrosoft 365を使用しています。使用している関数の一部は、Microsoft 365とWeb版Excelでのみ利用可能です。このExcelチュートリアルのデモをダウンロードしてください。

参照: 誰もが知っておくべき Windows、Linux、Mac のコマンド (無料 PDF) (TechRepublic)

Excelで最初の値を返す方法

タイムスタンプは、特定の時点を示す日付と時刻を組み合わせたものです。セルの書式を「標準」または「数値」に変更すると、日付の代わりに数値が表示されます。数値の整数は日付を表し、小数値はその日付内の時刻を表します。

例えば、図Aは日付と時刻として表示されるようにフォーマットされたタイムスタンプの列を示しています。その隣の列には、各タイムスタンプの基になる値が表示されています。

図A

2 つの列に整理された複数の日付と数値を含む Excel テストデータ — 図 A: 整数は日付、小数値は時刻です。

さて、ヘルプデスクのコールセンターで、発信者と着信時刻別に通話を追跡しているとします。1日の終わりには、その日の最初の通話と最後の通話を把握したいとします。図Bに示すシンプルなデモシートでは、通話が順番にリストアップされているため、最初の通話と最後の通話は簡単に確認できますが、担当者が通話記録を入力する方法によっては、必ずしもそうとは限りません。

図B

セル 3F がハイライト表示された Excel のテストの日付 — 図 B: 担当者は各通話の通話記録をヘルプデスクセンターに入力します。

オペレーターが受信から数分遅れて通話記録を入力することは容易であり、そうなると記録は時間順に整理されなくなってしまいます。そのため、このソリューションではそのような状況を想定していません。

幸いなことに、ExcelのMIN()関数は、その日の最速（最小の時間値）の呼び出しを返します。このシンプルな関数に必要な引数は1つだけで、評価対象となる値を含む範囲または構造化参照です。

機能

=MIN(TableCalls[通話日])

データ範囲がTableCallsというExcelのテーブルオブジェクトであるため、構造化参照を使用しています。通常のデータ範囲を評価する場合は、参照を使用します。

=MIN(C3:C12)。

機能

=VLOOKUP(F3,テーブル呼び出し,1)

図Cに示すように、最初の呼び出しを行ったクライアントを返します。構造化参照TableCallsはテーブル名です。F3は最初の呼び出し時刻（右側）を参照し、引数1はTableCallsの最初の列の対応する値を返します。

図C

セル3Eが強調表示されたExcelテストデータ — 図 C: XLOOKUP() 関数は、その日の最初の通話を行ったクライアントを返します。

それでは、本日最後の電話を折り返しさせていただきます。

Excelで最後の時刻の値を返す方法

最も古い通話とその通話を行ったクライアントを返す関数を処理すれば、その日の最後の通話についても同じように処理するのは簡単です。最後の通話を返すためにMAX()関数を使用し、その通話を行ったクライアントを返すために別のXLOOKUP()関数を使用します。

図 D は両方の機能を示しています。

E5: =VLOOKUP(F5,テーブル呼び出し,1)

F5: =MAX(TableCalls[通話日])

図D

セル5Eが選択されたExcelテストデータ — 図 D: XLOOKUP() 関数と MAX() 関数の両方が使用されます。

XLOOKUP()関数は、F5キーの時刻値を検索し、顧客ID列から対応する値を返すことで、最後に電話をかけた顧客を返します。MAX()関数は、通話日列（C5:C12）の時刻値から、最新の（時刻値が最大の）通話を返します。

手順に従っているときに、F3 と F5 の時間値に日付と時刻の両方が表示されている場合は、これらのセルの書式を変更して、時刻のみを表示することができます。

まずF3セルを選択し、「ホーム」タブの「数値」グループの「書式」ドロップダウンをクリックします。ドロップダウンリストから「時刻」を選択します。F5セルでもこの手順を繰り返します。

1日の最初の通話と最後の通話だけが必要な場合は、これは簡単でうまく機能します。では、各クライアントが複数の通話を行った場合に、その日の最初と最後の通話を返すレコードを作成したいとします。この要件はより複雑です。

Excelで発信者と通話履歴を1つのレコードにまとめる方法

経営陣は、顧客が複数回電話をかけた場合、最初の通話と最後の通話を含む全顧客リストを確認したいと考えるかもしれません。いくつかの簡単な関数だけではこの要求を満たすことはできませんが、実現可能です（図E）。

図E

H列に整理されたテストデータが強調表示されたExcelテストデータ — 図 E: この単純なグループ化により、各顧客 ID の最初の呼び出しと最後の呼び出しが返されます。

最初のステップは、顧客IDの一意のリストを返すことです。そのためには、次の動的配列関数をH3に入力します。

=SORT(UNIQUE(TableCalls[顧客ID]))

この関数は、顧客ID値のソートされた一意のリストを動的配列として返します。つまり、式は1つだけであり、それはH3に格納されます。列の残りの部分はスピル範囲、つまり式を満たすために必要な結果です。

各顧客 ID の最初の呼び出しを返すには、次の関数を I3 に入力し、残りのセルにコピーします。

=XLOOKUP($H3,TableCalls[顧客ID],TableCalls[通話日])

この関数は、列 H の対応する顧客 ID の最初の呼び出しを返します。

各顧客 ID の最後の呼び出しを返すには、次の関数を J3 に入力し、残りのセルにコピーします。

=XLOOKUP($H3,TableCalls[顧客ID],TableCalls[通話日],”結果なし”,,-1)

最後の引数 -1 は下から上へ検索を実行するため、最後の呼び出しを返すことができます。呼び出しを降順で並べ替える場合は、両方の関数を変更し、J3 の引数から -1 を削除し、I3 の引数に追加する必要があります。

この設定は機能しますが、複数回電話をかけている顧客は1人だけなので、関数は最初の通話を最後の通話として重複させてしまいます。これは混乱を招くだけでなく、むしろ悪い結果です。そこで、最後の通話列の重複した通話を非表示にする条件付き書式を追加しましょう。

まず、J3:J9を選択し、「ホーム」タブの「スタイル」グループにある「条件付き書式」をクリックし、ドロップダウンから「新しいルール」を選択します。表示されるダイアログで、上部のペインの最後の選択肢「数式を使用して書式設定するセルを決定する」をクリックします。

数式コントロールに「=$J3=$I3」と入力します（図F）。「書式」をクリックし、「フォント」タブをクリックしてパレットから白を選択し、「OK」を2回クリックしてシートに戻ります。

図F

Excelで「数式を使用して書式設定するセルを決定する」を選択した状態でルールの種類メニューを開きます — 図 F: 条件付き書式の数式を入力します。

図Gを見るとわかるように、顧客ID 101 の最終値のみが表示されています。他の値も表示されていますが、フォントが背景色と同じなので見えません。何かを隠してしまうのは好きではありませんが、これは定期的に更新されるため、便利な解決策だと思います。

図G

条件付き書式が適用された Excel データで、繰り返しデータは表示されません — 図 G: 条件付き書式では、最初の呼び出し値と一致しない場合に最後の呼び出し値が表示されます。

乞うご期待

手間がかかるように思えますが、使用した関数はすべて簡単に実装できます。唯一の難点は、Tableオブジェクトでは動的配列関数を使用できないことです。つまり、必要に応じてI列とJ列の関数と条件付き書式の参照を更新する必要があります。そのため、ピボットテーブルで同じことを実現する方法については、今後の記事で紹介します。

XLOOKUP() の使い方がわからない場合は、「Excel で新しい XLOOKUP() 動的配列関数を使用する方法」をご覧ください。UNIQUE() について詳しくは、「Excel で UNIQUE() 関数を使用して一意の値の数を返す方法」をご覧ください。

TechRepublic Academy の以下のコースで Excel をマスターしましょう。