エクセルの2000で下記のような式を作りたいのですが、#VALUEにな

質問

エクセルの2000で下記のような式を作りたいのですが、#VALUEになってしまいます。

D1～I1が空白であれば、J1にI2を表示、
E1～I1が空白であれば、J1にD2を表示、
F1～I1が空白であれば、J1にE2を表示、
G1～I1が空白であれば、J1にF2を表示、
H1～I1が空白であれば、J1にG2を表示、
I1～I1が空白であれば、J1にH2を表示、
D1～I1がどれも空白でない場合も、J1にI2を表示します。

=IF(D1:I1="",I2,IF(E1:I1="",D2,IF(F1:I1="",E2,IF(G1:I1="",F2,IF(1:I1="",G2,IF(I1:I1="",H2,I2))))))

ネスト回数には引っかかっていないと思うのですが、IF関数の論理式は複数のセルを選択出来ないんですかね？

D～H2のセルには=SUM(D15:I15)/SUM(D1:I1)といった数式が入っており、パーセンテージで小数点以下第二位まで表示しています。

うまく表示できる方法をどなたか教えていただけないでしょうか？

逆に
D1だけが空白でなければ、D2を表示、
D1～E1だけが空白でなければ、J1にE2を表示、
D1～F1だけが空白でなければ、J1にF2を表示、
D1～G1だけが空白でなければ、J1にG2を表示、
D1～H1だけが空白でなければ、J1にH2を表示、
D1～I1だけが空白でなければ、J1にI2を表示、
D1～I1の全てが空白の場合は、J1にI2を表示、
でもかまいません。

どうかよろしくお願いします。

回答No.4

KURUMITO · ベストアンサー

簡単には次のようにすればよいでしょう。
Ｄ１セルからＩ1セルには数値が入力されるのでしたらＪ1セルには次の式を入力します。

=IF(COUNTBLANK(D1:I1)=6,I2,INDEX(D2:I2,MATCH(10^10,D1:I1)))

Ｄ1セルからＩ1セルに文字列が入力されるのでしたらＪ1セルには次の式を入力します。

=IF(COUNTBLANK(D1:I1)=6,I2,INDEX(D2:I2,MATCH("ー",D1:I1)))

ＭＡＴＣＨ関数の引数でーはひらがな入力モードで入力します。半角モードではありません。

お礼

ご回答ありがとうございました。
できました！
本当にありがとうございます。

回答No.3

mt2008

要するに、全部空白のときはI2、それ以外は何か入っている一番右のセルの下の行を表示で良いんですよね？

=OFFSET(A1,1,SUMPRODUCT(MAX((D1:I1<>"")*COLUMN(D1:I1)))+9*(COUNTA(D1:I1)=0)-1)

お礼

度々すみません。
他のやり方でうまくできそうです。
本当にありがとうございました。

補足

ご回答ありがとうございます。
OFFSET、いろいろ調べてみたのですがよく分からなかったので教えてください。
教えていただいた数式で、A1というのはどこを参照していることになるのでしょうか？
何回もすみませんがよろしくお願いします。

回答No.2

imogasi

珍しい問題だな。
IF文のネストは常々泥臭いと思っているので
ユーザー定義関数を考えた。
標準モジュールに
Function aa()
Application.Volatile True
'MsgBox Application.WorksheetFunction.CountBlank(Range("D1:I1"))
If Application.WorksheetFunction.CountBlank(Range("D1:I1")) = 6 Then
aa = Range("j2")
Else
c = Range("j1").End(xlToLeft).Column
aa = Range("A1").Offset(1, c - 1)
End If
End Function
をコピペ。
(注）Range("j1").End(xlToLeft).Column
はENDキーの次に←（左向き矢印キー）を押す操作に当たるコード（マクロの記録でも取れる）
ーー
例データ
C1:J2に
-は、この画面で、左に詰められないように空白の代わりに入れたもの。実際は空白セルです
- - - - - - - a7
- a1 a2 a3 a4 a5 a6 a7
ーーー
J1セルに=aa()と入れる。
D1からI1までにデータを入れて、テストしてみてください。左から入れていくと判りやすいだろう。
ーー
これをエクセル関数でやろうとするとEnd(xlToLeft)に当たる関数がなくて苦労するだろう。
右から最初の非空白行の列を捉える、なども関数では難しい。右から迫るのはもっと難しい。

お礼

ご回答ありがとうございました。
お礼が遅くなりまして申し訳ございません。
現在の私の頭では理解できそうもないので、今後勉強していくことにします。
本当にありがとうございました。

回答No.1

mshr1962

D1～I1が空白であれば、J1にI2を表示、
E1～I1が空白であれば、J1にD2を表示、
F1～I1が空白であれば、J1にE2を表示、
G1～I1が空白であれば、J1にF2を表示、
H1～I1が空白であれば、J1にG2を表示、
I1～I1が空白であれば、J1にH2を表示、
D1～I1がどれも空白でない場合も、J1にI2を表示します。

=IF(COUNTBLANK(D1:I1)=6,I2,IF(COUNTBLANK(E1:I1)=5,D2,IF(COUNTBLANK(F1:I1)=4,E2,IF(COUNTBLANK(G1:I1)=3,F2,IF(COUNTBLANK(H1:I1)=2,G2,IF(COUNTBLANK(I1)=1,H2,I2))))))

お礼

ご回答ありがとうございました。
お礼が遅くなりまして申し訳ございません。
ご丁寧な回答、本当に感謝いたします。