【EXCEL】目次の番号を作成する方法

2019年9月13日
2021年8月4日
その他業務・IT
ヒント, 豆知識
445view
0件

本記事では、私が業務で使用しているEXCELの式の中で、役立ちそうな式を記載しようと思います。

以下の目次の番号の設定するために使用している式を説明していこうと思います。

1 目次の番号（パターン①）
2 目次の番号（パターン②）
3 まとめ

目次の番号（パターン①）

使用している式

=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,1,1,1)=””,””,COUNTA($C$3:OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,1,1,1)))

解説

上記の式は、OFFSET関数(INDIRECT関数(ADDRESS関数(ROW関数,COLUMN関数)))の構成を「参照位置」と定義すると、以下のように表せる。

IF関数(「参照位置」が空欄か？,”処理なし”,COUNTA関数)

ROW()により、式が入力されているセル（カーソルが当たっているセル）の行を指しています。同様にCOLUMN()では現在の列を指しています。

ADDRESS関数で「取得した行番号、列番号」を基にセルの位置を文字列として返し、INDIRECT関数でADDRESS関数が示す文字列を参照できる形で返します。つまり、この式が入力されているセルの番号を返します。

どうして、INDIRECT関数でADDRESS関数を囲む必要があるかというと、OFFSET関数は参照するセルの位置を文字列としては受け取れないため、受け取れる参照する形に変換する必要があるからです。

OFFSET関数では、論理式として、値を確認するセルを設定しています。

IF関数では、OFFSET関数により得られたセル（式が入力されているセルの右隣）が空欄か？を確認しています。

文字がなければ空欄を、文字があればCOUNTA関数で求めた数（前述の画像でいうオレンジセルの数）を返します。

以上により、パターン①の章番号を表示しています。

各関数の説明を以下に示します。

使用している関数の説明

IF関数

書式

IF(論理式, 真の場合, 偽の場合)

説明

論理式で示した条件を満たせば「真の場合」で記載した処理や文字列（ダブルコーテーションで囲む必要あり）、満たさなければ「偽の場合」で記載した処理や文字列を返す。

実施例

IF(A1=0, “〇”,”×”）⇒　結果：A1が「0」「空白」であれば「〇」、「0」「空白」でなければ「×」を返す。

OFFSET関数

書式

OFFSET(参照、行数、列数、高さ、幅)

説明

詳細は、こちらを参照ください。

実施例

同上

INDIRECT関数

書式

INDIRECT(参照文字列, [参照形式])

説明

参照文字列は、セル参照の値を指定します。

参照形式は、参照文字列の形式を指定し、以下のパターンに分かれます。

省略：A1形式

０：R1C1形式（Rは行：ROWを示し、Cは列：COLUMNを示します。）

１：A1形式

実施例

INDIRECT(A1）⇒　結果：A1セルに入力されている値（参照するためのセル番号）を返す。

ADDRESS関数

書式

ADDRESS(行番号,列番号,[参照の種類],[参照形式],[シート名])

説明

行番号、列番号には、それぞれセル参照に使用する行番号、列番号を指定します。

参照の種類は、以下のパターンに分かれます。

１：行・列⇒絶対参照（例：$A$1）

２：行⇒絶対参照、列⇒相対参照（例：A$1）

３：行⇒相対参照、列⇒絶対参照（例：$A1）

４：行・列⇒相対参照（例：A1）

省略：行・列⇒絶対参照（例：$A$1）

参照形式は、以下のパターンに分かれます。

省略：A1形式

０：R1C1形式（Rは行：ROW関数を示し、Cは列：COLUMN関数を示します。）

１：A1形式

シート名は、” Sheet1 ” のように指定します（ダブルコーテーションは、必須）。省略時は、本関数を入力しているセルが存在するシートになります。

実施例

ADDRESS(1,1）⇒　結果：$A$1（文字列として返す）

ROW関数

書式

ROW([範囲])

説明

指定された「範囲」の一番左上のセルの行番号を返す。省略された場合は、式が入力されているセルの行番号を返す。

列番号の場合は、後述のCOLUMN関数を使用する。

実施例

ROW(C5:D10）⇒　結果：5

COLUMN関数

書式

COLUMN([範囲])

説明

指定された「範囲」の一番左上のセルの列番号を返す。省略された場合は、式が入力されているセルの列番号を返す。

行番号の場合は、前述のROW関数を使用する。

実施例

COLUMN(C5:D10）⇒　結果：3

COUNTA関数

書式

COUNTA(値1,[値2],[値3],・・・)

説明

指定された値（範囲）内のデータの数をカウントする。

実施例

※A5からA8にデータが入力され、他は空欄の場合

COUNTA(A1:A10）⇒　結果：4

目次の番号（パターン②）

使用している式

=IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,1,1,1)=””,””,IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2,1,1)=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,-2,1,1),LEFT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0,1,1),1)&”-“&RIGHT(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0,1,1),1)+1,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,-2,1,1)&”-“&1))

解説

上記の式は、OFFSET関数(INDIRECT関数(ADDRESS関数(ROW関数,COLUMN関数)))の構成を「参照位置」と定義すると、以下のように表せる。

IF関数(「参照位置」が空欄か？,”処理なし”,IF関数(「参照位置」を使った論理式,LEFT関数とRIGHT関数で構成した文字列表示,「参照位置」で構成した文字列表示))

一見パターン①と同じ関数を使ってはいますが、IF関数のネスト（IF関数の中にIF関数がある構造）であったり、かなり複雑になっていると思います。

IF関数では、現在のセルが、オレンジセルか白セルか？を判断しています。オレンジセルの場合は空欄を返し、白セルの場合は以下で説明するIF関数の処理を行います。

IF関数の条件式では、「x-1」か「x-2以降」かを確認しています。OFFSET関数により1つ上の行と同じ章か？（現在のセルの「2つ左」と「2つ左、1つ上」が同じか？）を判断しています。

同じであれば「”章番号”-“前行+1″」を表示させ、異なれば「”章番号”-“1″」を表示させるようにしています。

以上により、パターン②の章番号を表示しています。

各関数の説明を以下に示します。

使用している関数の説明

※本記事で前述の関数は省略します。

LEFT関数

書式

LEFT(文字列,[文字数])

説明

文字列で示した文字列に対し、左から指定した文字数だけを取り出して返します。

文字列は、直打ち、セルの参照のどちらでも対応しています。

文字数を省略した場合は、「１」となります。

実施例

LEFT(“テスト用の文字列”,3)　⇒　結果：テスト

RIGHT関数

書式

RIGHT(文字列,[文字数])

説明

文字列で示した文字列に対し、右から指定した文字数だけを取り出して返します。

文字列は、直打ち、セルの参照のどちらでも対応しています。

文字数を省略した場合は、「１」となります。

実施例

RIGHT(“テスト用の文字列”,3)　⇒　結果：文字列

まとめ

今回は、EXCELで目次を作成する際に私が使う２つの式を説明しました。

どちらも構造が複雑になっていますが、何かの参考にしていただければ幸いです。