ExcelにあるColumn関数とColumns関数の使い方とその7つの実例

ExcelにあるColumn関数は参照の列番号を返します。引数を省略または指定することができます。引数を省略すると、Column関数が入力されているセルの列番号が返されます。配列として参照の列番号を返す場合は、Ctrl + Shift + Enterを押す必要があります。 Columns関数は、配列または参照の列数を返すため、引数を省略できません。

Columns関数はよく、Index、Indirect、Char、SumProduct、Mod、If、Sum、Rowなどの関数と組み合わせて使用されます。たとえば、Index + Columnの組み合わせは、参照の行番号または列番号を返します。Indirect + Char + Column + Rowの組み合わせは、参照の行番号及びこの後の内容を返します。SumProduct + Mod + Columnの組み合わせは、偶数列または奇数列の合計を返します。

ExcelにあるColumn関数とColumns関数の構文

Column関数の書式：

=COLUMN([参照])
=COLUMNS(配列)

説明：

A、Column関数は、参照の列番号を返します。引数(参照)は省略できます。省略した場合、Column関数が入力されているセルの列番号が返されます。参照が1つのセルを入力される場合、このセルの列番号を返します。参照が複数のセルを入力し、同じ行(例えば、A1:C1)の場合、Ctrl + Shift + Enterを押してA1からC1までの列番号を配列形式で返し、Enterだけを押してA1の列番号のみ返します。参照は複数の範囲を参照できません。

B、Columns関数は、配列または参照の列数を返します。引数(配列)は省略できません。

ExcelにあるColumn関数の使い方と実例

数式のセルまたは参照の列番号を返す実例

1、セルB1を選択し、数式

=COLUMN()

を入力し、Enterキーを押して、2を返します。セルA1を選択し、数式

=COLUMN(C1)

を入力し、Enterキーを押して、3を返します。手順は次となります。

2、説明：

数式

=COLUMN()

は引数を省略し、デフォルトで数式を入力されたセルの列番号を返し、数式はB1にあるため、B1の列番号が2を返します。数式

=COLUMN(C1)

の引数はC1です。数式がセルA1にあっても、C1の 列番号が3を返します。

返された列番号を配列で返す実例

1、A1からD1までの列番号を配列形式で返したい場合、 E2セルをダブルクリックし、数式

=COLUMN(A1:D1)

をE2にコピーし、Ctrl + Shift + Enterを押して、1を返します。E2を選択し、Altキーを押しながら、MとVを順番に押して、[数式の検証]ウィンドウを開きます。Enterキーを押して、結果が1を返します。E3をダブルクリックし、数式

=INDEX(COLUMN(A1:D1),,2)

をE３にコピーし、Ctrl + Shift + Enterキーを押して、2を返します。E3を選択して、同じ方法で [数式の検証]ウィンドウを開きます。Enterキーを押して、1〜4の配列を返します。手順は次となります。

2、数式の説明：

A、例から見ると、Ctrl + Shift + Enterを押しても、数式

=COLUMN(A1:D1) 

は配列を返されないことが分かります。数式

=COLUMN(A1:D1)

を参照された関数に入力して、配列を返します。COLUMN(A1:D1) をIndex関数に入れると、配列{1;2;3;4}を返します。

B、数式

=INDEX(COLUMN(A1:D1),,2)

は、配列または参照の行番号と列番号を返し、またその値を参照します。COLUMN(A1:D1) は{1;2;3;4}を返し、この値はINDEX関数の1番目の引数にします。2番目の引数は行番号ですが、ここでは省略されました。3番目の引数は列番号(ここの値は2です)です。数式の意味：配列{1;2;3;4}にある2番目の列番号を返します。つまり、2を返します。

Column(1:1)1行目の列番号またはColumn(A:A)A列の列番号を返す実例

1、セルA2をダブルクリックし、数式

=INDEX(COLUMN(1:1),3)

をコピーし、Ctrl + Shift + Enterを押すと、3を返します。B2をダブルクリックし、

=INDEX(COLUMN(A:A),1)

をコピーし、Ctrl + Shift + Enterを押すと、1を返します。手順は次となります。

2、数式の説明：

A、数式

=INDEX(COLUMN(1:1),3)

では、COLUMN(1:1)を使用して、最初の行のすべての列番号を配列形式で返します。数式COLUMN($1:$1)で表すこともでき、$は絶対参照を意味します。

B、数式

=INDEX(COLUMN(A:A),1)

では、COLUMN(A:A)を使用して、A列の列番号を配列形式で返します。A:Aは、A列を参照することを意味します。COLUMN($A:$A)で表示することもできます。

ExcelにあるColumns関数の使い方と実例

配列の列番号を返す実例

1、セルA1をダブルクリックし、数式

=COLUMNS({3,8,4,7,9,2})

をコピーし、Enterキーを押して、6を返します。またセルA1をダブルクリックして、数式の2番目と4番目の[,]を[;]に変更し、Enterキーを押して、2を返します。手順は次となります。

2、数式の説明：

数式

=COLUMNS({3,8,4,7,9,2})

は、配列の列番号を返します。配列には1行で6つの数値があるため、6を返します。数式

=COLUMNS({3,8;4,7;9,2}) 

配列には3行で、各行は2つの数値があるため、2を返します。配列では、[;]記号は行のデリミタで、[,]記号は値のデリミタです。

領域を参照する場合、列番号を返す実例

セルC3をダブルクリックし、数式=COLUMNS(A1:D1)をC3にコピーし、Enterキーを押して、4を返します。手順は次となります。

数式の説明：

数式

=COLUMNS(A1:D1)

は、参照された範囲A1:D1の列番号を返します。A1からD1までは4つの列があるため、4を返します。

ExcelにあるColumn関数の活用実例

Indirect + Char + Column + Rowの組み合わせは、指定された行やその以降の内容を返す

1、5行目から以降の内容を返したい場合。 セルA9をダブルクリックし、数式

=INDIRECT(CHAR(COLUMN()+64)&ROW(A5))

をセルA9にコピーし、Enterキーを押して「ジュース」を返し、またセルA9を選択して、マウスをA9の右下隅に移動し、黒い十字になった後マウスを押しながら、右にドラッグして、5行目の内容を返します。セルD9を選択し、同じ方法で下にドラッグして6行目と7行目を返します。手順は次となります。

2、数式

=INDIRECT(CHAR(COLUMN()+64)&ROW(A5))

説明：

A、COLUMN() は、参照の列番号を返します。数式がA9にある場合、1を返し、CHAR(COLUMN()+64)は CHAR(1+64)に変更し、64はASCIIコードテーブルに「@」のコードを意味します。65は大文字のAのコード、Char関数はASCIIコードに対応する文字を返すために使用され、CHAR(65)はAを返します。COLUMN()+64を使用する理由は、右にドラッグする時に、COLUMN()がB、C、D列の列番号2、3、4を返すためです。64 + 2はBのASCIIコードであり、64 + 3はCのASCIIコードであり、…、右にドラッグしたときに上記の論理で対応する列番号を返し、またChar関数で対応する列の英文字を返します。

B、ROW(A5) は、A5の行番号5を返します。下にドラッグすると、A5はA6に変わり、次のROW(A6)はA6の行番号6を返します。他も同じロジックになります。

C、数式は

=INDIRECT("A"&5)

になり、最後にIndirect関数を使用してA5の参照を返します。つまり、A5の “ジュース”を返します。

SumProduct + Mod + Columnの組み合わせにより、奇数列または偶数列の合計を実現する

1、偶数列の合計を求めます。セルE8をダブルクリックし、数式

=SUMPRODUCT((MOD(COLUMN($B:$E),2)=COLUMN(A1))*B$2:E$6)

をE8にコピーし、Enterキーを押して、合計結果が4717を返します。奇数列の合計を求めます。セルE9をダブルクリックし、数式

=SUMPRODUCT((MOD(COLUMN($B:$E),2)=0)*B$2:E$6)

をE9にコピーし、Enterキーを押して5457を返します。手順は次となります。

2、偶数列を合計する数式

=SUMPRODUCT((MOD(COLUMN($B:$E),2)=COLUMN(A1))*B$2:E$6) 

説明：

A、$B は、列Bへの絶対参照を表し、右に移動したときに列Bが列C、列Dなどに変更されないように確保します。$B:$Eは、列Bから列Eの範囲を固定して合計することを意味します。COLUMN($B:$E)は配列形式で列B から列Eの列番号を返します。つまり、{2;3;4;5}を返します。

B、MOD(COLUMN($B:$E),2)はMOD({2;3;4;5},2)になり、配列内の各要素を2と除算します。配列の最初から2を取得し、また2と除算して、結果は0(除数は0)を返します。2番目の3を取得し、2と除算して、結果は1(3÷2=1[除数1])を返します。この論理で演算して、最後は配列{0;1;0;1}を返します。除算は偶数列を検索して取り出します。

C、COLUMN(A1)を使用してA1の列番号1を返します。MOD(COLUMN($B:$E),2)=COLUMN(A1)は {0;1;0;1}={1}になります。さらに計算すると、配列から各要素を取り出して1と比較します。1である場合はTrueを返し、それ以外の場合はFalseを返し、最後に{FALSE;TRUE;FALSE;TRUE}を返します。

D、数式は

=SUMPRODUCT({FALSE;TRUE;FALSE;TRUE}*B$2:E$6)

になります。さらに計算すると、配列から最初の要素FALSEを取得し、B2:E6でFALSEに対応するセルB2を返します。FALSEのため、0を返します。また配列の2番目の要素のTRUEを取り出し、B2:E6で対応するセルC2を返します。TRUEのため、セルC2の239を返します。また配列から3番目の要素のFALSEを取得し、B2:E6で対応するセルD2を返します。FALSEのため、0を返します。最後は配列から4番目の要素TRUEを取得し、B2:E6で対応するセルE2を返します。 TRUEのため、セルE2の457を返します。この論理で演算続けて、最後は{0,239,0,457;0,215,0,562;0,439,0,651;0,549,0,690;0,586,0,329}を返します。

E、数式は

=SUMPRODUCT({0,239,0,457;0,215,0,562;0,439,0,651;0,549,0,690;0,586,0,329})

になり、最後に配列内の要素を合計すると、結果は4717を返します。

ヒント：上記の数式は$B:$EのBを最初の列とします。列Aが最初の列の場合、奇数列の合計になります。

3、奇数列の合計を求める数式

=SUMPRODUCT((MOD(COLUMN($B:$E),2)=0)*B$2:E$6) 

説明：

奇数列と偶数列の合計を求める数式の違いは、奇数列は0で除算して奇数列を求め、偶数列は1で除算して偶数列を求めます。つまり奇数列はMOD(COLUMN($B:$E),2)=0 を使用します。偶数列はMOD(COLUMN($B:$E),2)=COLUMN(A1)を使用します。(注意点：ここでは、列Bが$B:$Eの最初です。)