COLUMN関数の使い方|基本~目的・応用まで分かりやすく
ここでは、エクセルの「COLUMN関数の基本~応用的な使い方」を解説します。
読めばCOLUMN関数がどのような動きをするのか、どう使えばいいのかが分かるようになります。
なお、似た関数「COLUMNS関数」の説明は、以下のページにあります。
⇒ COLUMNS関数の説明はこちら
1.COLUMN関数とは?意味や読み方を確認
まずは、COLUMN関数の意味や動き、読み方を見てみます。
1-1.COLUMN関数
とは
COLUMN関数は、「セルの列(=横)番号を返す」関数です。
列番号とは、エクセルの上の方にある「A、B、C、…」を数値に直したもののことです。
例えば、「Aは1」、「Bは2」、「Cは3」です。この番号を求めることができるのが「COLUMN関数」です。
COLUMN関数のポイントは、次の通りです。
- 列番号を求めることができる
- 返ってくる値は「数値」
- 行番号は一切関係ない
このように、「このセルはAから数えて何列目かな?」と知りたい時に使うのがCOLUMN関数なのです。
1-2.COLUMN関数の読み方 → 「カラム」
一応、COLUMN関数の読み方も紹介しておきます。
「COLUMN」の読み方は「カラム(またはコラム)」です。縦の列のことを英語で「COLUMN(カラム)」と言います。
ちなみに、雑誌などの囲み記事のことを「コラム」と言いますよね。
「コルムン」などと、無理やり読まないようにしましょう。
2.COLUMN関数の基本的な使い方
次に、COLUMN関数の構文と使い方を見てみましょう。
2-1.COLUMN関数の構文と引数
COLUMN関数の構文は、次の通りです。
名称だけ見ると、「配列ってなに?」、「なんかめんどくさそう」となりそうですが、基本的な使い方はたった2通りです。
2-2.使い方その1「セル番号を入れる」
一つ目は、配列に「セル番号を入れる」使い方です。セル番号を入れると、「入れたセルの列番号」が返ってきます。
例えば上のように、
=COLUMN(B4)
と入れると「セルB4の列番号は?」という意味になり、結果「2」が返ってきます。
この場合は、「数式を入れたセルの番号」は関係ありません。
2-3.使い方その2「何も入れない」
二つ目は、配列に「何も入れない」使い方です。何も入れないと、「数式を入れたセルの列番号」が返ってきます。
この場合、どんな時でも数式は、
=COLUMN()
です。
例えば、セルA1にこの数式を入れると、「セルA1の列番号は?」という意味になり、結果「1」が返ってきます。
以上2つが、COLUMN関数の基本的な使い方です。
2-4.「COLUMN()-1」の意味は
COLUMN関数でよく使われる形が、
=COLUMN()-1
ですが、この数式の意味は、「数式を入れたセルの列番号から1を引く」です。
数式を入れたセルが「A1」なら「0」、「B10」なら「1」、「C100」なら「2」です。
「COLUMN()-1」の部分を数値に置き換えてみると、数式の全体の意味が分かってくるでしょう。
3.COLUMN関数を使う主な目的
COLUMN関数は、基本的な使い方だけ知っても「なんのために使うのか?」が分かりません。
そこで、「COLUMN関数を使う主な目的」を見てみます。
3-1.数式を右にコピーした時の手直しをなくするため
COLUMN関数を使う目的で一番多いのが、「数式を右にコピーした時の手直しをなくするため」です。
言葉だけだと分かりずらいので、一つ例を見てみましょう。
上の例は、左の表から「№」を検索値に都道府県名と県庁所在地を抜き出そうとしたものです。
セルE2の値を検索値とし、セルF2にVLOOKUP関数を使った数式を入れています。
この時、VLOOKUP関数の引数「列番号」にCOLUMN関数を使うと、県庁所在地の抽出も簡単になります。
まず、セルF2に入れた数式は次の通りです。
=VLOOKUP($E$2,$A$2:$C$7,COLUMN(B1),FALSE)
これを右隣のセルG2にコピーすると、数式は次のように変化します。
=VLOOKUP($E$2,$A$2:$C$7,COLUMN(C1),FALSE)
COLUMN関数のカッコ内が「相対参照」の機能で「C1」に変わってくれるため、結果列番号は「3」となり、県庁所在地が抽出されるのです。
もし、列番号に数値を入れていると、数式コピー時に手直しが必要になります。数式一つであればまだいいのですが、たくさんコピーする場合には面倒です。
なので、COLUMN関数を使って「数式の手直しの必要」をなくしているのです。
3-2.列番号を計算に使うため
次にCOLUMN関数を使うのは、「列番号を計算に使うため」です。
列番号は、当たり前ですが、「右に1ずつ増えて」いきます。この特性を利用して、数式で行われる計算に使います。
上の図は、「セルA2に『=COLUMN()』と入れてセルC2までコピーした」ものです。
例えばこの「セルA2からC2の範囲」の、「奇数行だけ抽出」したい場合には、「列番号の余り」をIF関数で判定します。
上の例で使っているMOD関数は、「数値を特定の数で割った余り」を求める関数です。
そして、IF関数で「2で割って余りが1の時はセルの値を、それ以外は空白を」とすれば、「奇数行の値」だけが取り出せます。
さらに、COLUMN関数を上のようにIF関数の中に組み込めば、「列番号を表示させるセル」がなくても「奇数行のみを抽出」できます。
この時IF関数の論理式は、「上のセルの列番号を2で割った余りが1か?」です。
=IF(MOD(COLUMN(セル番号),2)=1,~)
COLUMN
C関数はこのように「列番号を計算に使うため」に使われます。
3.COLUMN関数の応用的な使い方
これだけだと、どんな時にCOLUMN関数を使うのかが分かりづらいですよね。
そこで最後に、「COLUMN関数の応用的な使い方」を紹介します。
4-1.VLOOKUP関数やINDEX+MATCH関数の「列番号」に使う
相対参照の機能も踏まえてCOLUMN関数がよく使われるのが、「VLOOKUP関数やINDEX+MATCH関数の引数『列番号』」です。
COLUMN関数を使うと「数式コピー時に列番号を入れ直さなくて済む」ので、手間がとても省けるからです。
上の例は、セルF2にVLOOKUP関数で左の表から「検索№」を検索値としてデータを抜き出したものです。
セルF2には、次の数式が入っています。
=VLOOKUP($E$2,$A$2:$C$7,COLUMN(B1),FALSE)
ポイントは「COLUMN(B1)」です。COLUMN関数で「セルB1の列番号」を求めているので、ここは「2」が返ってきて、結果「都道府県名」を返します。
そして、この数式を右隣のセルG2にコピーすると…。
数式は次のように変わり、結果「県庁所在地」を返します。
=VLOOKUP($E$2,$A$2:$C$7,COLUMN(C1),FALSE)
これは、COLUMN関数のカッコ内が「セルB1」から「セルC1」に変わり、結果「3」が返ってきたためです。
COLUMN関数をVLOOKUP関数やINDEX+MATCH関数に使う時のポイントは、次の通りです。
- COLUMN関数のカッコ内に「参照したい値が返ってくるセル番号」を入れる
- 列番号以外の引数は「$」で固定化(絶対参照化)する
- 列番号は固定化しない(相対参照)
このように、COLUMN関数を使うと「列番号が自動化」されて「数式をコピーするだけで終わる」ため、とても便利です。
4-2.「自動で列方向に番号を振る」ことができる
もう一つ、COLUMN列関数を使えば「自動で列方向に連番を振る」ことができます。
詳しくは次のページで紹介していますので、クリックして参照してください。
⇒「関数を使って自動で列方向に連番の番号を振る2つの簡単な方法」へはこちら
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】