合計額が正しいか関数式を使って確認するには

質問

大変お世話になっております
チェックするデータは値のみの記載で各セルに数式は入っておりません。下記に示しました例題で『R列の合計』が正しいか確認する数式を作成しチェックをしたいのですが、関数式の最適な式が分かりません。お分かりでしたら、どうぞご教示をお願いいたします。

各列の内訳は、以下の通りとなりなります。
※値が入っていないセルも存在しています。
A列＝単価A
B列＝使用時間A(時間を数値変換したもの)
C列＝Aの合計額(=IFERROR(ROUND(A2*B2,0),0))
D列＝単価B
E列＝使用時間B(時間を数値変換したもの
F列＝Bの合計額(=IFERROR(ROUND(D2*E2,0),0))
G列＝単価C
H列＝使用時間C(時間を数値変換したもの
I列＝Cの合計額(=IFERROR(ROUND(G2*H2,0),0))
J列＝単価D
K列＝使用時間D(時間を数値変換したもの
L列＝Dの合計額(=IFERROR(ROUND(J2*K2,0),0))
M列＝A,B,D,Dの合計額(=C2+F2+I2+L2)
N列＝利用区分
O列＝間接経費(=IF(N2="FF利用",ROUNDDOWN(M2*0.3,0),0))
P列＝税抜き合計(=M2+O2)
Q列＝消費税相当額(=ROUNDDOWN(P2*0.1,0))
R列＝合計(税抜け合計額＋消費税)(=P2+Q2)
【例題】
単価A,使用時間A,Aの合計額,単価B,使用時間B,Bの合計額,単価C,使用時間C,Cの合計額,単価D,使用時間D,Dの合計額,"A,B,D,Dの合計額",利用区分,間接経費,税抜き合計,消費税相当額,合計(税抜け合計額＋消費税)
200,2.44998,490,10000,4.4356789,44357,500,3.234543,1617,,,0,46464,FF利用,13939,60403,6040,66443
,,0,20000,0.89876579,17975,,,0,4500,9,40500,58475,,0,58475,5847,64322
350,0.1243389,44,20000,3.5645779,71292,300,0.456743,137,800,1,800,72273,FF利用,21681,93954,9395,103349

回答No.4

hiro_1116 · ベストアンサー

シンプルな例を示しておきますので御参考になさってください。

お礼

ご教示ありがとうございました。
『NG』の文字を表示する事が出来ました。
最初に質問をしました、R列＝合計(税抜け合計額＋消費税)(=P2+Q2)が正しいか確認する為の関数式についても、
1つのセルで完結する事が今の自分にはハードルが高い事がわかりまして、間接経費、税抜き合計、消費税の3つをそれぞれに求めてから、合計額を求める事にしました。
今回は、TEXT関数の使い方をご紹介頂きまして感謝申し上げます。

回答No.3

hiro_1116

＞この値の前に下記のように表示出来たら良いのですが、ご存知でしたらご教示頂きたいです。【NG　64475】

判定結果と数値を別のセル（別の列）にすれば、難易度が下がると思います。
どうしても１つのセルにしたければ、数値を文字に変換して判定結果と結合して表示する手はあると思いますが。

補足

大変お世話になります。
ご教示ありがとうございました。
『どうしても１つのセルにしたければ、数値を文字に変換して判定結果と結合して表示する手はあると思いますが。』
1つのセルに判定結果と文字を入れたいので、下記のような関数式にしましたが、【NG　64475】の『NG』を
手前に入れるには、何処を修正すればよろしいでしょうか。お分かりでしたら、具体的な関数式のご教示を頂けると大変助かります。
=IF(IFERROR(SUM(ROUND(A2*B2,0)+ROUND(D2*E2,0)+ROUND(G2*H2,0)+ROUND(J2*K2,0)),0)+IF(N2="FF利用",ROUNDDOWN(M2*0.3,0),0)=P2,"OK",(IFERROR(TEXT(SUM(ROUND(A2*B2,0)+ROUND(D2*E2,0)+ROUND(G2*H2,0)+ROUND(J2*K2,0)),0)+IF(N2="FF利用",ROUNDDOWN(M2*0.3,0)),0)))

回答No.2

m5048172715

こんなことしてどうするのかと私は思いましたが、

S列に合計を計算表ソフトウエアで出して、R列とS列が同値かの判定結果をT列に出す。

回答No.1

hiro_1116

ご質問の趣旨がよく分かりませんが、貴方がご質問の中で書かれている式を用いたのではダメなのでしょうか？
単に目的が達せられるだけではなく、「最適な式」にこだわっていらっしゃるのでしょうか？

補足

早速のご教示ありがとうございました。
口足らずな質問内容となりまして申し訳ありません。
チェックするデータの各セルは、手入力で値が入力されております。
チェックポイントは、以下通りです。
①手書きのA,B,C,Dの合計と間接経費の合計額の値が関数式の通り一致しているか。
②手書きのA,B,C,Dの合計と間接経費と消費税の合計額が関数式の通り一致しているか。
合っているか自信がありませんが、①、②を確認する関数式を以下のように作成して見ました。
【税抜き合計額の関数式チェック関数式】
=IF(IFERROR(SUM(ROUND(A2*B2,0)+ROUND(D2*E2,0)+ROUND(G2*H2,0)+ROUND(J2*K2,0)),0)+IF(N2="FF利用",ROUNDDOWN(M2*0.3,0),0)=P2,"OK",(IFERROR(SUM(ROUND(A2*B2,0)+ROUND(D2*E2,0)+ROUND(G2*H2,0)+ROUND(J2*K2,0)),0)+IF(N2="FF利用",ROUNDDOWN(M2*0.3,0),0)))

【合計(税抜け合計額＋消費税)チェック関数式】
=IF(IFERROR(SUM(ROUND(A2*B2,0)+ROUND(D2*E2,0)+ROUND(G2*H2,0)+ROUND(J2*K2,0)),0)+IF(N2="FF利用",ROUNDDOWN(M2*0.3,0),0)+ROUNDDOWN(P2*0.1,0)=U2,"OK",(IFERROR(SUM(ROUND(A2*B2,0)+ROUND(D2*E2,0)+ROUND(G2*H2,0)+ROUND(J2*K2,0)),0)+IF(N2="FF利用",ROUNDDOWN(M2*0.3,0),0)+ROUNDDOWN(U2*0.1,0)))

手書きの値が関数式の値と一致しない時は、関数式で求めた値を表示するようにしましたが、この値の前に下記のように表示出来たら良いのですが、ご存知でしたらご教示頂きたいです。【NG　64475】