前提
例えば、今回はこんなログについて調査していたとします。
| 日時 | IPアドレス | MACアドレス | ホスト名 |
|---|---|---|---|
| 2025-12-02 12:53:55 | 192.168.120.1 | gg:11:22:33:44:51 | EXAM0001.yoruchin.local |
| 2025-12-02 14:38:57 | 192.168.120.1 | gg:11:22:33:44:55 | EXAM0005.yoruchin.local |
| 2025-12-02 16:10:16 | 192.168.120.1 | gg:11:22:33:44:51 | EXAM0001.yoruchin.local |
| 2025-12-02 17:42:10 | 192.168.120.1 | gg:11:22:33:44:55 | EXAM0005.yoruchin.local |
| 2025-12-02 13:07:46 | 192.168.120.2 | gg:11:22:33:44:52 | EXAM0002.yoruchin.local |
| 2025-12-02 14:51:48 | 192.168.120.2 | gg:11:22:33:44:56 | EXAM0006.yoruchin.local |
| 2025-12-02 16:28:47 | 192.168.120.2 | gg:11:22:33:44:56 | EXAM0006.yoruchin.local |
| 2025-12-02 17:59:21 | 192.168.120.2 | gg:11:22:33:44:52 | EXAM0002.yoruchin.local |
| 2025-12-02 14:25:19 | 192.168.144.1 | gg:11:22:33:44:53 | EXAM0003.yoruchin.local |
| 2025-12-02 14:35:31 | 192.168.144.5 | gg:11:22:33:44:57 | EXAM0007.yoruchin.local |
| 2025-12-02 16:35:32 | 192.168.144.5 | gg:11:22:33:44:54 | EXAM0004.yoruchin.local |
| … | … | … | … |
| 2025-12-02 17:56:59 | 192.168.144.9 | gg:11:22:33:44:59 | EXAM0009.yoruchin.local |
(ログは、日時列の昇順で出力されており、見やすくするためにそれをExcelでIPアドレス列で昇順に並び替えてあるもの、MACアドレス列の値によってホスト名の値が一意に定まる、という前提で話を進めます。多分、Excelであればフィルターなどを駆使すればIPアドレスの昇順じゃなくても大丈夫なはず)
まずはじめに、COUNIFS()とは
COUNTIFS 関数は、複数の範囲のセルに条件を適用して、すべての条件が満たされた回数をカウントします。らしいです。
参照
簡単な例で言うと、下のような値が入っていたとして…
| A | B | C | |
|---|---|---|---|
| 1 | 滋賀県 | 大津市 | 343916 |
| 2 | 滋賀県 | 草津市 | 139939 |
| 3 | 滋賀県 | 長浜市 | 113940 |
| 4 | 奈良県 | 奈良市 | 349385 |
| 5 | 奈良県 | 橿原市 | 119250 |
| 6 | 奈良県 | 生駒市 | 117332 |
=COUNTIFS(B:B, "*市", C:C, "<=300000", C:C, ">=100000")と入力すると4を返すような関数です。
やりたいこと
全期間において、各IPアドレスについてそれらが払い出された端末のホスト名と、直近に払い出された日時を調べたい。
つまり、
| 日時 | IPアドレス | MACアドレス | ホスト名 |
|---|---|---|---|
| 2025-12-02 16:10:16 | 192.168.120.1 | gg:11:22:33:44:51 | EXAM0001.yoruchin.local |
| 2025-12-02 17:42:10 | 192.168.120.1 | gg:11:22:33:44:55 | EXAM0005.yoruchin.local |
| 2025-12-02 16:28:47 | 192.168.120.2 | gg:11:22:33:44:56 | EXAM0006.yoruchin.local |
| 2025-12-02 17:59:21 | 192.168.120.2 | gg:11:22:33:44:52 | EXAM0002.yoruchin.local |
| 2025-12-02 14:25:19 | 192.168.144.1 | gg:11:22:33:44:53 | EXAM0003.yoruchin.local |
| 2025-12-02 14:35:31 | 192.168.144.5 | gg:11:22:33:44:57 | EXAM0007.yoruchin.local |
| 2025-12-02 16:35:32 | 192.168.144.5 | gg:11:22:33:44:54 | EXAM0004.yoruchin.local |
| … | … | … | … |
| 2025-12-02 17:56:59 | 192.168.144.9 | gg:11:22:33:44:59 | EXAM0009.yoruchin.local |
こんな感じにしたいわけです。
というわけで、次のように取得してみました。
日時～ホスト名列をExcelのA～D列に見立てて、各行のE列にこんな式を入れました。
E2セルであれば=COUNTIFS($B$2:$B$10000,B2,$C$2:$C$10000,C2)、
E3セルであれば=COUNTIFS($B$2:$B$10000,B3,$C$2:$C$10000,C3)といった感じです。
(セルの右下掴んで下にグワーってするやつで10000行目まで入力します。)
この10000という値は、一番下の行の番号です。皆さんが使うときはこの値を変えて試してみてください。
その中で、フィルターを使ってE列の値がの1のやつだけ抽出すると、欲しい行が取れました。(その行より下の行に、IPアドレスとMACアドレスが同じという条件を満たす行が自分の行を含めて1個しかないですよね。)
おわりに
他にこんな取得方法がありますよ、というアイディアがあれば教えてね。