「知っていると得するかもしれないExcelの癖」シリーズ第三回、テーマは「行番号について」になります。
Excel 2016バージョン1902を使用して確認しています。
エクセルの資料に行番号を付ける時どのように附番しているでしょうか?
直打ちをしていると、附番されている行の間に新しい行を追加したくなった時に番号の付け直しする作業が発生します。
これを回避するためにはいくつかの数式が考えられます。
最初の行をどのようにして求めるか?
やり方はいろいろあると思うのですが、思いつく方法を5つ上げます。
- 最初の行に「1」を入力し、次の行からは「前行+1」で設定
- 〃「ROW()-最初の行まで行数を数値」を入力し、以下すべて同じ
- 〃「ROW()-ROW(最初の行の直前のセルを絶対参照)」〃
- 〃「ROW()-(直前までの行数を数える式)」〃
- 〃「ROW()-ROW(最初の行のセルを絶対参照)」〃
最初の行を4行目とした時のそれぞれの数式下記になります。
| セル | 数式 | |
| 1. | A4 | 1 |
| A5 | =A4+1 | |
| 2. | B4 | =ROW()-3 |
| 3. | C4 | =ROW()-ROW($C$3) |
| 4. | D4 | =ROW()-(COUNTBLANK($D$1:$D$3)+COUNTA($D$1:$D$3)) |
| 5. | E4 | =ROW()-(ROW($E$4)-1) |
どれだけの対応力があるのか?確認する
実際に行の追加・削除をしてみます。
左図で2行目を行削除し、5行目と6行目の間に行を挿入します。結果は右図になります。
※この例では表領域をテーブルとして書式設定しているのですが、右図を見ていただくと分かるのですが、行挿入した時に上下に同じ数式が入っているためなのか絶対参照だけの数式になっているためなのか詳細な仕様は不明ですが、数式が自動設定されるようです。
メンテナンスフリーを目標とするのであれば、残念ながら1と2は落選になります。
つぎに右図での３行目、表で見出しの次の行を削除してみます。
この段階で生き残っているのは3と4になります。
最後に1行目と2行目を削除して見ます。
がしかし、表領域をテーブルとして書式設定しているために、テーブルに必要な見出し行である2行目を削除することはできません。
このExcelの有難い機能を取り除くために、表領域を選択し、メニューからデザインを選び、リボンのツールにある「範囲に変換」をクリックすると「テーブルを標準の範囲に変換しますか?」と聞いてくるので「はい」にします。
範囲に変換すると、1行目と2行目を行削除することができます。
削除した結果は下記のようになります。
結果としては残念ながら全滅となりました。
ただ「できませんでした」という終わり方はできないので、何とか方法を考えなくてはなりません。
要は3の数式を使用して基準とする最初の行の直前のセルが削除されてもセル参照のエラーが起きても大丈夫なような条件式を入れれば良いはずです。
全滅になったので別案を考える
そこで捻りだしたのが次の数式です。
=ROW()-IF(ISERROR($F$3),0,ROW($F$3))
おなじように確認してみます。
①この状態で、2行目を削除し、5行目と6行目の間に1行挿入します。→右上図に遷移
③ここまで問題はありません。
ここで、1行目と2行目を削除します。
→右下図に遷移
②つぎに、3行目を削除します。
→左下図に遷移
見た目としては問題ありません。
これで大丈夫なのでは?
確認のためF1セルの数式を見てみると下記のようになっています。
=ROW()-IF(ISERROR(#REF!),0,ROW(#REF!))
想定通りではありますが「#REF!」のエラーが発症しないように手当しただけなので、この状態で1行目に行挿入をすると下図のようになります。
一度、「#REF!」のエラーとなったセルが、自動的に復活するわけもなく残念な結果となります。
という事で、いろいろ試してみましたが、いずれも完ぺきにメンテナンスフリーにすることはできないことが解りました。
まとめ
完全にメンテナンスフリーとなる数式がないのであれば、次善の策を採用することになると思います。
その場合は、「見出し行は削除されない」ということを前提にして、3の案「ROW()-ROW(最初の行の直前のセルを絶対参照)」を採用するのが良い認識です。
と言うのは表をテーブルとして書式設定すれば、Excelの有難い機能により見出し行を削除することができないようにすることができるからです。
そうは言いながらもあまり長い数式にしてしまうのもメンテナンス性を考えるとよろしくは無いと思いますので、想定される使用状況のなかで最善なものを採用されるのがベターであると考えます。
以上、最後までご一読いただきありがとうございました。
