ExcelでのINDIRECT関数の使い方について

質問

Excelの関数についてお伺いしたいと思います。現在、図のような表があり、A2, C2, E2を合計したいと思っております。条件として；

1) 列を頻繁に挿入するものの、常にA2, C2, E2を合計したい（セルがずれてしまっては困る）
2) B列やD列には他の数値が入るため（列が連続しない）、A2:E2ということができない

これを解決するためにA8に"A2,C2,E2"と入力し、A9に=SUM(INDIRECT(A8)と記入したのですが、#REF!というエラーになってしまいます。この場合、どのようにIndirect関数を使えばよいのでしょうか。またそもそもIndirect以外の有効な関数を使うべきなのでしょうか。教えていただきたく、よろしくお願い致します。

回答No.3

Chiquilin · ベストアンサー

> またそもそもIndirect以外の有効な関数を使うべきなのでしょうか。
そう思います。 INDIRECTは便利使いするような関数じゃありません。

> A8に"A2,C2,E2"と入力し
そもそも列がかわる度に こんなものを入力するのが無駄です。

=SUMIF(A1:E1,"Data*",A2:E2)

フラグになるものを有効利用した方が 間違いがないです。

お礼

ご回答ありがとうございました。SUMIFのような、自分が普段使っている関数で対応できるということに気付くことができたのは大きな収穫でした。自分にとってIndirectについて理解を深めるいい機会となりましたが、既知の関数を応用することも忘れないようにしたいと思います。

回答No.6

cj_mover

＃５、ｃｊです。＃５にてミスがありましたので訂正します。

誤）
> 以上の理由から、INDEX関数はそれ以外で表現する方法の無い場合の
> 緊急避難的関数という位置付け、と言っても過言ではないです。

正）
以上の理由から、INDIRECT関数はそれ以外で表現する方法の無い場合の
緊急避難的関数という位置付け、と言っても過言ではないです。

以上、訂正をお願いします。
失礼しました。

お礼

訂正いただきありがとうございました。ミスについては文脈から推察できましたので大丈夫です。Indirect以外の便利な関数についても勉強してみます。

回答No.5

cj_mover

こんにちは。お邪魔します。

> この場合、どのようにIndirect関数を使えばよいのでしょうか。
　　=INDIRECT("A2")+INDIRECT("C2")+INDIRECT("E2")
SUM関数はとびぬけて優秀な関数ですが、四則演算で済むものを
わざわざ余分に関数を使う必要はありません。
一般論として関数の数だけ計算が遅くなります。
他方、数式の文字長によっても再計算の速さは変りますから、
SUM関数を用いた方が少ない文字数で数式を仕上げられる場合には、
積極的に関数を使います。
今回の課題では、関数を少なくまとめた方が有利です。

> またそもそもIndirect以外の有効な関数を使うべきなのでしょうか。
現行のExcelにはマルチスレッド計算というものがあって、
CPUのプロセッサの数だけ数式の演算を並行処理することで
数式の再計算の処理速度を高めることができるようになっています。
マルチスレッド計算に対応できない関数が幾つかありますが、
INDIRECT関数もその内のひとつに該当します。
また、INDIRECT関数はブックを開いただけで再計算を必要とする
揮発関数でもあります。
さらに言えば、単体での処理速度でいうと、
INDIRECT関数は、
同様の参照を返すINDEX関数やOFFSET関数に比べて
極端に遅いです。
以上の理由から、INDEX関数はそれ以外で表現する方法の無い場合の
緊急避難的関数という位置付け、と言っても過言ではないです。
私がお奨めするのは、
　　=INDEX(2:2,,1)+INDEX(2:2,,3)+INDEX(2:2,,5)
という数式になります。
数式が重くなる要素を極力排除しています。
おまけとして、行方向にフィルして各行の集計を求めることもできますから、
この点でも、INDIRECT関数より有利になります。

> これを解決するためにA8に"A2,C2,E2"と入力し
と書いてありますから、Ａ８セルのような参照文字列ありき、のお話
ではないと受け止めていますが、
もし、どこかのセルに、複数領域のセル参照文字列を書き込み、
この文字列を元に計算させたい、ということでしたら、
ＶＢＡでユーザー定義関数を作成してシート上で活用する方法が、
結果的に一番簡単になるとは思いますが、
INDEX関数で目的を果たせるなら、その方がベターです。
何か不足があって、複数領域のセル参照文字列を元に計算したい場合は、
事情を詳らかに補足して貰えれば、ユーザー定義関数を考えてみます。

以上です。

お礼

丁寧なご解説、本当にありがとうございました。回答してくれる方々のレベルの高さに恐縮してしまいます。今回の件ではINDEXやOFFSET関数についても調べたのですが、まだまだ理解が追いついておりません。これを機会に勉強してみます。

回答No.4

tom04

こんにちは！

INDIRECT関数の方法はすでに出ていますので・・・

=SUMPRODUCT((2:2)*(MOD(COLUMN(2:2),2)=1)*(COLUMN(2:2)<=5))
といった方法もあります。

※　今回の場合は奇数列というコトで可能ですが、
規則性がない場合は
他の方の方法になると思います。m(_ _)m

お礼

今回教えていただいた式を完全に理解するにはもう少し時間が掛かりそうです。ただsumproductはよく見かけるので、この機会に勉強してみたいと思います。ご回答いただきありがとうございました。

回答No.2

bunjii

>この場合、どのようにIndirect関数を使えばよいのでしょうか。
INDIRECT関数の返り値を確認して応用しないと正しい結果を得られません。
SUM関数の引数は単独セルを幾つも指定できますが、INDIRECT関数に置き換えるときは要素毎にINDIRECT関数で指定しなければなりません。
=SUM(INDIRECT("A2"),INDIRECT("C2"),INDIRECT("E2")) のようにします。
1つのセル（A8)へ"A2,C2,E2"と入力して、それをINDIRECT関数の引数に与える場合は"A2"と"C2"と"E2"に分けてINDIRECT関数の引数にしなければなりません。

>またそもそもIndirect以外の有効な関数を使うべきなのでしょうか。
Excelの関数で引数をセルの値にするときはその数式を他のセルにコピーするか否かで絶対アドレスか相対アドレスにするかを考える必要があります。
また、引数の対象セルは行および列の挿入や削除によって当初の指定から変化することも想定しなければなりません。
例題のSUM(A2,C2,E2)はA列とC列の間に1列挿入されたときSUM(A2,D2,F2)に変化することを確認してください。
従って、INDIRECT関数を使うと逆効果になると思われます。

お礼

INDIRECT関数の引数では各要素を分けるということを知りませんでした。ご丁寧な解説、本当にありがとうございました。

回答No.1

aokii

A8
=A2

C8
=C2

E8
=E2

A9
=SUM(INDIRECT(A8),INDIRECT(C8),INDIRECT(E8))

お礼

当サイトを始めて利用させていただきましたが、こんなにも早くご回答いただきありがとうございました。