セルの列をアルファベットで取得 - エクセル(Excel)関数
セルの列をアルファベットで取得するADDRESS関数とアルファベットをセル番号として認識させるINDIRECT関数の使い方
HLOOKUP関数やMATCH関数で列の横の位置は取得できますが
取得数字をVLOOKUP関数やSUM関数で使う場合
列をアルファベットで取得する必要があります
別シートの場合はこちら→
関数内のセル参照を別シートから数式で出す方法
エクセル表サンプル
|
A |
B |
C |
D |
E |
F |
1 |
|
|
|
|
|
|
2 |
|
日時 |
佐藤 |
小林 |
井上 |
|
3 |
|
10月4日 |
18,001 |
19,001 |
15,001 |
|
4 |
|
10月6日 |
18,002 |
19,002 |
15,002 |
|
5 |
|
10月7日 |
18,003 |
19,003 |
15,003 |
|
6 |
|
10月8日 |
18,004 |
19,004 |
15,004 |
|
7 |
|
10月9日 |
18,005 |
19,005 |
15,005 |
|
8 |
|
10月10日 |
18,006 |
19,006 |
15,006 |
|
9 |
|
10月11日 |
18,007 |
19,007 |
15,007 |
|
10 |
|
10月12日 |
18,008 |
19,008 |
15,008 |
|
11 |
|
10月13日 |
18,009 |
19,009 |
15,009 |
|
12 |
|
|
|
|
|
|
上記の表の2行目の名前を検査する場合
MATCH関数を使います
・関数 | MATCH(読み方 - マッチ) |
・書式 | =SUMIF(検査値,検査範囲,照合の型) |
・内容 | 検索条件に一致するセルの位置を数値で表します。照合の型は省略すると既定値は1です。 |
例)小林のセルの場所を検索
=MATCH(
"小林",
2:2)
結果:4
検索を行に対して何番目か「
2:2」と2行目を検索しています
4列目のセルです
この4列目のセルを合計しようとすると
=SUM(D3:D11)←この「D」が「4」
このままでは合計できません
セルをアルファベットで取得します
アルファベットで列を取得するにはADDRESS関数を使います
・関数 | ADDRESS(読み方 - アドレス) |
・書式 | =ADDRESS(検査値,検査範囲,照合の型) |
・内容 | セルの位置をセル番号で表します。 |
=ADDRESS(
2,
4,
4)
結果:D2
上記はADDRESS(
2行目,
4列目,
4型)という意味です
※「
4型」を省略すると「1型」となり結果が「$D$2」になります
3行目から11行目まで足す場合
=SUM(D3:D11)なので
D3=ADDRESS(
3,
4,
4)
D11=ADDRESS(
11,
4,
4)
これを=SUM(D3:D11)に入れれば、、と思いますが
=SUM(ADDRESS(3,4,4):ADDRESS(11,4,4))
エラーにもならす「入力した数式は正しくありません」と警告されます
これは上記のADDRESS関数で取得した値が文字列で、セル番号として認識されていないからです
文字列で「1234」としたのを数値にするにはVALUE関数を使いますが
同じようにセル番号として認識させるにはINDIRECT関数を使います
ADDRESSで取得した数式を全部INDIRECT()の中に入れます
D3=INDIRECT(ADDRESS(
3,
4,
4))
D11=INDIRECT(ADDRESS(
11,
4,
4))
これを=SUM(
D3:
D11)の中に入れます
=SUM(
INDIRECT(ADDRESS(3,4,4)):
INDIRECT(ADDRESS(11,4,4)))
結果:171045
列のアルファベット取得が ADDRESS関数
アルファベット+数字をセル番号と認識させるのが INDIRECT関数です
したがって小林を行から横に検索して合計を出す場合
上記の
MATCH("小林",2:2)を
=SUM(INDIRECT(ADDRESS(3,
4,4)):INDIRECT(ADDRESS(11,
4,4)))
に組み込み
=SUM(INDIRECT(ADDRESS(3,
MATCH("小林",2:2),4)):INDIRECT(ADDRESS(11,
MATCH("小林",2:2),4)))
となります
結果:171045
別シートの場合はこちら→
関数内のセル参照を別シートから数式で出す方法
・
1,2,3,4の4つの数字で12桁のランダム数を作成
・
複数列の条件が一致している数を出す方法
・
セルの列をアルファベットで取得
・
関数内のセル参照を別シートから数式で出す方法
・
50個のセルが次のセルと同じになる確率
・
指定の文字列を含むセルを抽出とセルに警告
・
指定範囲内に何か文字列が入力された場合その文字を抽出
・
消費税8%の価格を10%にする方法とCEILING関数