動画と記事で学ぶ / 業務ツール
ネットワークのExcel実務を完成例で解説。ポート表、access設定を生成する式、COUNTIFSの重複検査、通信要件と試験IDの対応、CSV化と差分レビューを紹介します。
ここからは、記事でゆっくり確かめましょう。図や操作の例を見ながら、気になるところから読み進めてください。
ネットワークの設計構築では、設計書、パラメーターシート、構成図、試験仕様書、手順書など、たくさんのドキュメントを作ります。その多くがExcelです。ネットワークエンジニアが「Excelエンジニア」と呼ばれることがあるくらいで、Excelをどれだけ使えるかで、作業の速さと正確さが大きく変わります。
この記事の前半では、設計構築で作るドキュメントの種類と、それぞれ何をどのソフトで書くのかを一覧にします。後半では、ポート表からExcel関数でconfigを作る方法、COUNTIFSでの重複チェック、通信要件と試験表のつなげ方を、具体的な例で説明します。

設計構築で作るドキュメント9種類と、使うソフト
案件によって細かい違いはありますが、設計構築で作るドキュメントは、だいたい次のとおりです。
| ドキュメント | 主なソフト | 読む人 | 書いてあること |
|---|---|---|---|
| 基本設計書 | Word | お客さん | 要件の概要と設計の基本方針。ルーティングの方式、障害時の経路、保守・監視の方法など |
| 詳細設計書 | Word、Excel | エンジニア | プロトコル、アドレス体系、正常時と障害時の経路、IPアドレス管理表など |
| パラメーターシート | Excel | エンジニア | ルーター、スイッチ、ファイアウォール、ロードバランサーの設定値すべて |
| 物理構成図 | Visio、Excel、PowerPoint | エンジニア、配線の作業者 | 機器、型番、ホスト名、ポート番号、どこにどうつながっているか |
| 論理構成図 | Visio、PowerPoint | エンジニア | IPアドレス、VLAN、セグメント、セグメント間の通信 |
| 試験仕様書(単体・結合・障害) | Excel | エンジニア | 試験の手順と、期待する結果 |
| 現地導入手順書 | Excel | 現地で作業するエンジニア | 作業の手順、想定時間、取得するコマンド、ログのファイル名 |
| 切り戻し手順書 | Excel | 現地で作業するエンジニア | トラブル時に元へ戻す手順 |
| 運用手順書 | Word | 運用保守のエンジニア | 日常の運用手順、障害時の対応フロー、緊急連絡先 |
基本設計書と詳細設計書は、読む人が違う
基本設計書は、お客さん向けの資料です。要件定義や設計の担当者が作ります。構成図も、細かい物理構成図ではなく、全体のイメージを描いた絵(ポンチ絵)レベルのものが載ります。ルーティングはスタティックかダイナミックか、障害が起きたらどの経路で通信するか、保守・監視の拠点はどこか、といった方針を書きます。エンジニアほどネットワークに詳しくない人が読んでも分かるように書く必要があります。
詳細設計書は、エンジニア向けの資料です。構築の担当者は、これをもとにconfigを作ります。中に入っているIPアドレス管理表には、VLANごとのセグメント、そのセグメントの用途、ゲートウェイのアドレス、サーバーごとのアドレスなどを書きます。途中から案件に入った人が、詳細設計書だけを読んでネットワークの全体像から細かいところまで理解できる。そういう目線で作ります。違いは基本設計と詳細設計の違いでも説明しています。
パラメーターシートからは、Excel関数でconfigを作る
パラメーターシートには、configのほぼすべての設定値が書かれています。ここから手でconfigを書き写すのではなく、Excel関数で組み立てます。設定値があとから変わっても、パラメーターシートを1か所直せばconfigも変わるので、直し忘れや、直したつもりで別の場所を壊す事故(デグレ)を防げます。具体的な関数の例は、この記事の後半で紹介します。
構成図は、物理と論理を分けて描く
物理構成図は、どの機器が、どのポートで、どこにつながっているかを描いた図です。検証でも本番導入でも、これを見ながらLANケーブルをつなぎます。データセンターでラックへの設置や配線を専門の作業者にお願いするときも、物理構成図と、Excelで作るポート収容表を渡します。IPアドレスやVLANは、管理用のIPくらいしか書かないことが多いです。
論理構成図は、ネットワークが論理的にどう分かれていて、セグメント間でどう通信するかを描いた図です。ポート番号や配線は書きません。管理用IPを持たないL2スイッチやハブは、描かないこともあります。物理構成図と論理構成図の両方を読めて、初めてネットワークの全体像がつかめます。
試験仕様書は、障害時だけでなく復旧時も見る
試験仕様書には、単体試験(機器が正常に動くか)、結合試験(設計どおりに構築できているか、configに間違いがないか)、障害試験(正常時と障害時の通信)の手順と、期待する結果を書きます。想定どおりの経路を通っているかは、tracerouteの結果をあらかじめ書いておき、実際の結果と比べて確かめます。
見落としやすいのが、復旧するときです。たとえば、ケーブルを抜いたらVRRPで15秒以内に切り替わる、という試験があったとします。5秒で切り替わったのでOK、で終わりにしてはいけません。抜いたケーブルを挿し直したときにも、通信が途切れることがあります。何秒途切れたのかまで、障害時と復旧時の両方で確認します。切替時間の測り方は冗長化の障害試験で見るべき切替時間にまとめています。
未経験で設計構築の会社に入ると、この試験仕様書に沿って試験を行い、ログを取るところから始めることが多いです。想定と違う結果を見落として「OK」にすると、本番導入のときに大きな障害になることがあります。細かいところまで正確に確認する力が求められます。
現地導入手順書には、ログのファイル名まで書く
現地導入手順書には、どの機器の何番ポートにつなぎ、IPアドレスを何に設定し、どのコマンドでログを取るか、といった手順をすべて書きます。ファームウェアの更新なら、アップロードと再起動に何分かかるかを事前に検証で測り、想定時間として書いておきます。ログを保存するファイル名も決めておきます。現地では、手順書に書いていない作業は原則として行いません。トラブルが起きたときのための切り戻し手順書も、あわせて作ります。
運用手順書は、運用保守のチームへの引き継ぎ資料
構築が終わると、運用保守のチームにネットワークを引き継ぎます。運用手順書は、その人たちが日々の業務で使うマニュアルです。障害時の対応フローや緊急連絡先まで書くので、何十ページにもなることがあります。これも設計構築のエンジニアが作ることが多いです。
ここからは、Excelで表を作るときの具体的な考え方と、関数の使い方を説明します。
表を作る前に、1行が何を表すかを決める
ポート表なら一行は一つの機器の一つのIF、通信要件表なら一行は一つの許可・拒否の要件、と決めます。異なる種類の情報を同じ行へ詰めると、並べ替えや検査が難しくなります。
機器名、IF、VLAN、対向を列へ分けます。色だけで変更や確認済みを表すより、変更区分や状態を文字でも持つと、印刷やCSVでも意味が残ります。
| シート | 一行の単位 | 主な列 |
|---|---|---|
| VLAN台帳 | VLANと参照範囲 | ID、VRF、VLAN、用途、サブネット、GW |
| ポート表 | 1つの機器の1つのIF | 設計ID、機器、IF、モード、VLAN、対向 |
| 通信要件 | 1つの通信条件 | 要件ID、送信元、宛先、プロトコル、ポート、許可/拒否 |
| 設定生成 | 1つの設計IDの生成結果 | 入力ID、config、生成版、レビュー結果 |
| 試験表 | 1つの確認操作 | 試験ID、要件ID、前提、操作、期待値、実結果、証跡 |
集計するデータ領域では結合セルを増やしすぎず、見出しの装飾とデータを分けます。見やすさと検査しやすさの両方を考えます。
重複チェックは「機器名+インターフェース名」で行う
SW1のGi1/0/10とSW2のGi1/0/10は別ポートです。重複を調べるなら、IF名だけでなく機器名も合わせたキーを使います。
VLAN番号やIPも、独立した環境では同じ値が存在することがあります。どの環境、機器、VRFの範囲で一意にするかを決めてから検査します。
| 設計ID | 機器 | IF | モード | VLAN | 対向 |
|---|---|---|---|---|---|
| P-ACCESS-10 | SW1 | GigabitEthernet1/0/10 | access | 81 | PC-A |
| P-ACCESS-11 | SW1 | GigabitEthernet1/0/11 | access | 81 | PC-B |
| P-UPLINK-48 | SW1 | GigabitEthernet1/0/48 | trunk | 81,92 | SW2 Gi1/0/48 |
図のP-ACCESS-10は設計IDです。同じIDをポート表、生成config、確認結果へ添えると、どの入力からどの設定を作ったかを追えます。
表の一行と機器の一行が同じ対象を指すことが、レビューの土台になります。行数が合うだけでなく、対象が合うことを確認してください。
Excel関数でconfigを作る:accessポートの例
式はA列からF列の値を使い、interface、description、VLANなどの行をつなぎます。CHAR(10)は改行です。「折り返して全体を表示」で、セル内の複数行を読めます。
D列がaccess以外ならレビューの表示にする例です。trunkへaccess用の設定を作らないため、最初は対象の形を限定します。
=IF(D2<>"access","要レビュー: access以外",
"interface "&C2&CHAR(10)&
" description "&F2&CHAR(10)&
" switchport mode access"&CHAR(10)&
" switchport access vlan "&E2)
この入力から生成するconfigの例
interface GigabitEthernet1/0/10
description PC-A
switchport mode access
switchport access vlan 81
この式は、空欄、対応機種、VLANの存在、現在の設定まで全部検証するものではありません。文字列が出たことと、投入できる設定になったことは別です。
コピーした文字列は、引用符や改行も確認します。変更前のconfigと比べ、対象外の行がないかをレビューします。ACLやPort-channelのように順序や関連がある設定は、単純な行連結だけで済ませないようにします。
入力規則とCOUNTIFSで、入力ミスと重複を見つける
access/trunkをリストで選ばせると、入力の揺れを減らせます。COUNTIFSで機器とIFが同じ行を数えれば、二重登録の候補を見つけられます。
ただし、ルールを通った値が設計上も正しいとは限りません。存在しないVLAN番号でも範囲内なら入力できる、貼り付けで想定外の値が入る、といった限界があります。
同じ機器・IFの重複を検査する例:H2
=IF(OR(B2="",C2=""),"未入力",
IF(COUNTIFS($B$2:$B$100,B2,$C$2:$C$100,C2)>1,"重複","OK"))
| 検査 | 設定例 | 限界 |
|---|---|---|
| モード | access/trunkのリストから選択 | 貼り付け等で想定外の値が入る場合も確認 |
| access VLAN | 整数の入力規則、環境の採番範囲 | 予約・未作成VLANを自動で判断しない |
| 機器・IFの重複 | 上のCOUNTIFS | 空白、略記、全角文字などの表記差は別に整理 |
| IPの重複 | VRFや環境を含むキーを決めて比較 | 文字列の重複だけではサブネット重複を判定できない |
IP、MAC、型番、IDは文字列として扱う列を決めます。CSVの取り込みでは型と文字コードも確認します。Excelが別の型へ変換すると、元の表記が変わることがあります。
検査の結果には「何を調べたか」を添えます。重複がないことを、配線や通信まで正常だった意味へ広げないでください。
通信要件のIDを、試験表のIDと対応させる
NW-APP-01でHTTPSを許可するなら、その要件へ対応するT-APP-01で実際の応答を確認します。許可しないSSHの試験も別に作り、どの装置のルールで止まったかを記録します。
期待値は試験前、実結果は実行後に書きます。期待した文章を実結果へコピーすると、観測した内容が分からなくなります。
| 要件ID | 送信元 | 宛先 | 条件 | 期待値 |
|---|---|---|---|---|
| NW-APP-01 | 172.27.81.15 | 172.27.92.25 | TCP443 | HTTPSを許可 |
| NW-SSH-01 | 172.27.81.15 | 172.27.92.25 | TCP22 | 新規SSHを拒否 |
| 試験ID | 要件ID | 操作 | 実結果・証跡 |
|---|---|---|---|
| T-APP-01 | NW-APP-01 | 指定URLへHTTPS要求、TLSと応答を確認 | 実施後に記入 |
| T-SSH-01 | NW-SSH-01 | 待受確認後、端末から新規接続を試す | FW拒否ログと時刻を保存 |
失敗後に直して再試験した場合は、最初の結果と修正後を追えるようにします。試験IDと設定版、出力の保存先をそろえると、変更の経緯を確認できます。
図のように設計から試験までIDがつながれば、「この要件を、この設定で実現し、この試験で確かめた」と説明できます。
レビューでは、入力値・数式・生成結果・実機を分けて確認する
正本の版と作業対象を確認し、入力値、式、生成結果をそれぞれ見ます。シートに数式エラーがないことだけでなく、参照先とコピー範囲が合うかも必要です。
印刷やCSVへ出す場合は、見切れ、隠れた列、フィルター、文字化けを確認します。画面で見える行だけを、すべての対象と判断しないようにします。
- 正本ファイル、版、作業対象、更新者をそろえる。
- 数式・入力・生成結果を分け、入力漏れやエラーを検査する。
- フィルターで非表示になった行と、印刷・出力範囲を確認する。
- CSV化する場合は、対象シート、文字コード、先頭ゼロ、改行を確認する。
- 元ファイルを残し、出力テキストをWinMergeで変更前と比較する。
- configのレビュー後、実機状態と実通信で試験する。
規模が大きくなればIPAMやGitなどと役割を分ける方法もあります。どの道具でも、何が正本で、どの入力から設定を作ったかを説明できることが大切です。
まず一ポートと一通信を、台帳から試験まで追ってみてください。Excelの作業が、転記から確認のための資料作りへ変わります。
よくある質問
ネットワークエンジニアはExcelをどのくらい使いますか?
設計構築では、パラメーターシート、IPアドレス管理表、試験仕様書、現地導入手順書など、多くのドキュメントをExcelで作ります。configもExcel関数で作ることが多いです。
入社前に覚えておくExcelの機能は?
SUMなどの基本的な関数、文字列をつなぐ「&」、ピボットテーブルが使えれば十分です。使う関数は会社やシートを作った人によって違うので、配属されてから業務の中で覚えていきます。
基本設計書と詳細設計書の違いは何ですか?
基本設計書はお客さん向けで、設計の方針を分かりやすく書きます。詳細設計書はエンジニア向けで、configを作れるところまで具体的に書きます。
物理構成図と論理構成図の違いは何ですか?
物理構成図は機器とポートのつながり(配線)を、論理構成図はIPアドレスやVLANなど論理的な分かれ方と通信を描きます。
未経験で入ったら、最初はどのドキュメントに関わりますか?
試験仕様書に沿って試験を行い、ログを取る作業から始めることが多いです。
関連する動画・筆者の投稿:Xで読む
