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