Excel IF関数を短くする式を教えて下さい。

質問

ベストアンサーにさせて頂いた方のやり方で別な問題点が出てきたので再び同じ質問です。
画像のB2セルに入る数式を短くまとめたいのです。。
今のところは下記のような式で上手くいっていますが、長すぎて編集する際困ってます。

=IF(D2="",0,IF(D2<4.5,D2,4.5))+IF(F2="",0,IF(F2<4.5,F2,4.5))…
+IF(D3="",0,IF(D3<3.5,D3,3.5))+IF(F3="",0,IF(F3<3.5,F3,3.5))…
+IF(D4="",0,IF(D4<4.5,D4,4.5))+IF(F4="",0,IF(F4<4.5,F4,4.5))…
+IF(D5="",0,IF(D5<3.5,D5,3.5))+IF(F5="",0,IF(F5<3.5,F5,3.5))…
+IF(D6="",0,IF(D6<4.5,D6,4.5))+IF(F6="",0,IF(F6<4.5,F6,4.5))…
+IF(D7="",0,IF(D7<3.5,D7,3.5))+IF(F7="",0,IF(F7<3.5,F7,3.5))…

D2～7にはC2～7を使った式が入っています。

つまり、D,F,H,J,L,N,P,R,T,V,X列をA列の数値と比較したときに
小さい方を合計として出したいのです。

どなたか分かる方いらっしゃいましたらよろしくお願いいたします。

回答No.8

cj_mover · ベストアンサー

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

扱い易さ優先で、B2セルだけで完結する配列数式（※後述の◆手順◆を参考にしてください）です。
=SUM(IF(ISNUMBER(C2:X7)*ISEVEN(COLUMN(C2:X7)),CHOOSE(1+(C2:X7>A2:A7),C2:X7,A2:A7)))

C2:X7、A2:A7、は、絶対参照　↓　にした方がいいかも知れませんが、そちらの都合で↑↓選んでください。
=SUM(IF(ISNUMBER($C$2:$X$7)*ISEVEN(COLUMN($C$2:$X$7)),CHOOSE(1+($C$2:$X$7>$A$2:$A$7),$C$2:$X$7,$A$2:$A$7)))

4.5、2.5、3.5、といった直値は避け、
"D,F,H,J,L,N,P,R,T,V,X列をA列の数値と比較"して、"小さい方"の合計を求めます。

セル参照を　C2:X7　A2:A7　の２種類に纏めて、編集し易さも考慮しました。
参照先の変更は置換機能で２種類を書き換えるようにします。
指定したC2:X7の内、偶数列だけを比較・合計の対象にするように、ISEVEN()関数を使っています。
参照先の変更の際、もし、奇数列を比較・合計の対象に代える必要がある場合は、
ISEVEN()関数をISODD()関数に換えることも必要になります。

数値の場合だけ合計を求めるので、万が一文字列が混じっていても、
（例えば、D,F,H,J,L,N,P,R,T,V,X列のセルが　=IF(条件,数値,"")　のような数式だった場合などでも）
計算から除外し（※誤ってＡ列の値を加算することもなく）、エラーにもなりません。

もし、D,F,H,J,L,N,P,R,T,V,X列の計算対象が、確実に数値であるならば、
数式の中の　ISNUMBER(C2:X7)*　の部分は省略可能です。
=SUM(IF(ISEVEN(COLUMN(C2:X7)),CHOOSE(1+(C2:X7>A2:A7),C2:X7,A2:A7)))

参照先のセルでのエラー値は計算から除外した合計を返します。

◆手順◆としては、
０）一旦、Ｂ２セルの結合を解除。
１）Ｂ２セルに上記何れかの数式を入力。
２）CtrlキーとShiftキーを同時に押しながらEnterキーを押して数式を確定。
３）Ｂ２セル選択時の数式バーを確認して数式が中括弧で挟まれて {=......} いたら、
　　正しく配列数式として確定できています。
必要なら　セルの結合を適用し直してください。

＃気を付けたつもりですが、もし検証漏れがあったりしたら、ごめんなさい。
　ご指摘あれば、対応するようにします。

試してみて、何かあったら補足欄にでも書いてみてください。
以上です。

お礼

すごい、完璧です！

表のレイアウトを変えることなく、数式だけ短くまとめて下さり
編集のし易さと正確さからcj_moverさんをベストアンサーにさせて頂きます。

ありがとうございました。大変、助かりました。

回答No.7

bunjii

>つまり、D,F,H,J,L,N,P,R,T,V,X列をA列の数値と比較したときに小さい方を合計として出したいのです。
集計範囲が飛び飛びになっているので1つの配列として扱えないのが難点です。
C、E、G、I、K、M、O、Q、S、U、Wの各列に数字以外の文字列が無ければ1つの配列として集計できます。

=SUM((C2:X7>A2:A7)*C9:X9*A2:A7,(C2:X7<A2:A7)*C2:X7*C9:X9)
この式は配列値を扱いますので数式バーへ入力後、Ctrl+Shift+Enterで確定してください。
尚、結合セルには配列式を入力できませんのでセルの結合を解除してから入力してください。
式を入力した後でセルの結合は可能です。
式中のC9:X9には 0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1 のように列選択用のパラメーターをセットしています。
C9:X9のセルは別のセルでも良いのですが値の並びとセルの数が同じにしてください。

代替案としてSUMPRODUCT関数を使った次の式も使えます。

=SUMPRODUCT((C2:X7>A2:A7)*C9:X9*A2:A7+(C2:X7<A2:A7)*C2:X7*C9:X9)
この式は通常のEnterキーで確定しても問題ありません。

お礼

分かりやすく画像まで載せて頂きありがとうございました。

回答No.6

tom04

こんばんは！
手っ取り早くVBAでやってみました。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面（カーソルが点滅しているところ）に
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, k As Long, lastRow As Long, myVal
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
If lastRow > 1 Then
Range(Cells(2, "B"), Cells(lastRow, "B")).ClearContents
End If
For i = 2 To lastRow Step 6
For k = i To i + 5
For j = 4 To 24 Step 2
If Cells(k, "A") > Cells(k, j) Then
myVal = myVal + Cells(k, j)
Else
myVal = myVal + Cells(k, "A")
End If
Next j
Next k
Cells(i, "B") = myVal
myVal = 0
Next i
End Sub 'この行まで

※　関数でないので、データ変更があるたびにマクロを実行する必要があります。

お望みの方法でなかったら無視してください。m(_ _)m

お礼

ありがとうございました。

回答No.5

mshr1962

ちょっと長くなるけど
=SUMPRODUCT(ISEVEN(COLUMN($C$2:$X$7))*(($C$2:$X$7>0)*($C$2:$X$7<=$A2:$A7)*$A2:$A7+($C$2:$X$7>$A2:$A7)*$C$2:$X$7))

お礼

ちょっと上手くいきませんでした。。
でもありがとうございました。

回答No.4

noname#203218

各行でA列数値以上の場合の集計とA列数値以下の集計をY列以降にします。

ここではY列、Z列に数式を挿入するものとします。

Y2式=SUMPRODUCT((MOD(COLUMN(D2:W2),2)=0)*(D2:W2>=A2))*A2
Z2式=SUMPRODUCT((MOD(COLUMN(D2:W2),2)=0)*(D2:W2<A2)*(D2:W2>0)*(D2:W2))

Y2、Z2式をY7,Z7までコピペ

B2式=sum(Y2:Z7)

Y2式はD2～W2の偶数列かつ、数値がA2以上のセル数をカウントしA2の値を掛ける
Z2式はD2～W2の偶数列かつ、数値が０以上A2未満のセル数の数値を集計

Y2とZ2は似ていますが、条件に当てはまるセルの個数を計算しているのか、数値を合計しているのかの違いがありますのでお待ちがないように。

エクセルの関数はバージョンによって利用できる関数が違いますので質問するときには必ずバージョンを記載するようにして下さい。

このような集計がある場合は１列置きに集計するような作表は不利です。
列数を増やすのではなく行数を２倍にする方が計算はしやすくなりますので、作表自体を工夫されたほうが良いかと思います又、全ての数式を纏める必要も無いでしょう。

今回のような比較し、数値を変動されるのであれば別表でifでA2の値を使用するのか記載しているセル値を使用するのか目視で確認出来る方法をとったほうが間違いは減るし、修正も楽だと思います。

お礼

ありがとうございました。

回答No.3

jcctaira

pekoouao さん

配列数式を使用すればできるかと思います。

=SUM(IF(D2:F7>=4.5-MOD(ROW(D2:F7),2),4.5-MOD(ROW(D2:F7),2),D2:F7))
を入力後、『Enter』 ではなく　、『Ctrl』+『Shift』+『Enter』と３つのキーを同時に押して下さい。
そうすれば、式の両方に大カッコ { } が表示されると思います。
{=SUM(IF(D2:F7>=4.5-MOD(ROW(D2:F7),2),4.5-MOD(ROW(D2:F7),2),D2:F7))}

簡単に説明しますと
　・D2やF6等偶数行は4.5 D3やF5等奇数行は3.5を最大としています。
　　4.5-MOD(ROW(D2:F7),2)　の式で4.5or3.5になるようにしています。
　・後は全ての配列（D2:F7）を合計をしています。

以上、お試し下さい。

お礼

ちょっと希望通りにはいきませんでした。。
でもありがとうございました。

回答No.2

satoron666

やりたいことがぼやけていて良く分かりません…
結局、合計を出したいのですか？それとも比較したいのですか？

上記に示している式では、合計を出しているようにしか見えません。

最小値を出したい、とのことでしたら
＝MINで良いのではないでしょうか？

お礼

比較した上で最小値の合計を出したかったのです。

回答No.1

Picosoft

どれくらい短くまとめたいのでしょうか？

IF(D2="",0,IF(D2<4.5,D2,4.5))
　　↓
MIN(N(D2),4.5)
と置き換えていくと半分くらいの長さになりますが……
※A列の数値と比較したいならMIN(N(D2),N(A2))

さらに短く、となるとユーザ定義のワークシート関数を作るとか、隠し行を作るとかになるかと。

お礼

ありがとうございました。