Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » AA・AB・ACとオートフィルしたい−ADDRESS関数

AA・AB・ACとオートフィルしたい−ADDRESS関数

対象:Excel2002, Excel2003, Excel2007, Excel2010

実務でExcelを使っていると、オートフィルで
 X
 Y
 Z
 AA
 AB
 AC
といった入力をしたくなることがあります。

X・Y・Z
という並びは通常のアルファベットですから、CHAR関数とROW関数などを使うことで入力できます

しかし「Z」につづく
AA・AB・AC
という並びはダメです。

ちなみに、AA・AB・ACというのはExcelの列番号です。

R1C1参照形式を使用する設定をしていない通常のExcelでは、
 25列目 = Y列
 26列目 = Z列
 27列目 = AA列
 28列目 = AB列
  ・
 52列目 = AZ列
 53列目 = BA列
 54列目 = BB列
  ・
とアルファベットを使った26進法で列番号は表示されます。

2003までのExcelは256列ありますからIV列まで、2007以降のExcelでは16,384列ありますからXFD列まで存在しています。

Excelで業務アプリを作っているようなときなどに、この列番号を入力する必要が出てくることがありますが、
 X・Y・Z・AA・AB・AC・・
といった並びを手入力するとなるとかなり面倒です。

[スポンサードリンク]

で、オートフィルでX・Y・Z・AA・AB・ACと入力するにはどうしたらいいのだろう、といった疑問になるわけです。

▼操作手順:・・X・Y・Z・AA・AB・AC・・といった入力をオートフィルで行う
※A2列から縦方向にA・B・C・・X・Y・Z・AA・AB・AC・・と入力する例

A2セルに
「=SUBSTITUTE(ADDRESS(1,ROW()-1,4),1,"")」
という数式を入力し縦方向にオートフィルする

▼サンプルファイル(003721.xls 68KByte)ダウンロード

サンプルファイルのA列に上記の例を作成してあります。

「=SUBSTITUTE(ADDRESS(1,ROW()-1,4),1,"")」
の意味を理解するには、サンプルファイルのB列とC列に注目していただくといいでしょう。

B列にはA列に入力された数式のSUBSTITUTEの第1引数に入力されている
「=ADDRESS(1,ROW()-1,4)」
という数式が入力してあります。

C列にはその
「=ADDRESS(1,ROW()-1,4)」
の第2引数「ROW()-1」を整数にした
「=ADDRESS(1,1,4)」
という数式が入力してあります。

ADDRESS関数は、
 第1引数に行番号
 第2引数に列番号
 第3引数にセル参照の方式
を指定すると、指定されたセルを文字列として表示してくれる関数です。

第3引数は、
「1」は絶対参照
「2」は行が絶対参照・列が相対参照の複合参照
「3」は行が相対参照・列が絶対参照の複合参照
「4」は相対参照
を意味しています。

上記の数式
「=ADDRESS(1,ROW()-1,4)」
「=ADDRESS(1,1,4)」
は第3引数が「4」なので相対参照を指定しています。

で、C列を見ていただくとおわかりいただけるとおり、
「=ADDRESS(1,1,4)」という数式では、行番号「1」列番号「1」のセルなので「A1」
「=ADDRESS(1,2,4)」という数式では、行番号「1」列番号「2」のセルなので「B1」
「=ADDRESS(1,3,4)」という数式では、行番号「1」列番号「3」のセルなので「C1」
 ・
 ・
「=ADDRESS(1,27,4)」という数式では、行番号「1」列番号「27」のセルなので「AA1」
「=ADDRESS(1,28,4)」という数式では、行番号「1」列番号「28」のセルなので「AB1」
という値がそれぞれ返されます。

しかしこのような数式をオートフィルだけで入力することはできません。

そこで、列番号を指定する第2引数が「1」「2」「3」となるように、ADDRESS関数の第2引数にROW関数を使った「ROW()-1」を指定して
「=ADDRESS(1,ROW()-1,4)」
としたのがB列の数式の意味です。

参考までにサンプルファイルのD列には
「=ROW()-1」
という数式を入力してあります。

上記の例ではA2セルから開始しているので「ROW()-1」としていますが、A1セルから開始するのなら「ROW()」、A3セルから開始するのなら「ROW()-2」と指定してください。

B列に表示されている
「A1」「B1」「C1」・・「AA1」「AB1」
で、目的にかなり近いのですが、「1」が邪魔です。

「A1」「B1」「C1」・・「AA1」「AB1」
という値から「1」を削除するために、SUBSTITUTE関数の
第1引数に「ADDRESS(1,ROW()-1,4)」
第2引数に「1」
第3引数に「""」
を指定したのが
「=SUBSTITUTE(ADDRESS(1,ROW()-1,4),1,"")」
というA列に入力した数式です。

[スポンサードリンク]

Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » AA・AB・ACとオートフィルしたい−ADDRESS関数

「検索・行列」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.