見積書作成に使えるエクセル関数について

質問

縦横それぞれ50mm刻みで単価の変わる商品の価格表があります。
これをもとにエクセルで見積書を作成したいのですが、どのような関数を用いたらよいでしょうか。

例えば、横260mmで縦620mmならC7の\14,200となるような見積書です。
見積書のセルに実寸を入力すると、単価が自動的に計算されるような関数の方法を教えてください。

回答No.8

MarcoRossiItaly

INDEX、MATCH 関数を組み合わせる、例題みたいなご質問ですね。ただ、ちょっとだけ工夫が必要ですね。250 mm 未満または 400 mm 未満の場合に MATCH が発生させるエラーを回避するため。

=if(i2*j2,index(sheet2!$B$2:$G$14,match(max(j2,400),sheet2!$A$2:$A$14,1)+(400<j2)*(j2<1000)*(mod(j2,50)>0),match(max(i2,250),sheet2!$B$1:$G$1,1)+(250<i2)*(i2<500)*(mod(i2,50)>0)),"")

上式では、「0 mm」の行や列を用意しない代わりに、「+(400<j2)*(j2<1000)*(mod(j2,50)>0)」という項によって、検索される行列の位置を補正しています。ですから 0 mm 用の行や列は、価格表の中に設けません。

補正項ではなく、No.1 さんのように CEILING を使っても構いません。

No.6 さんの表では 0 mm 用の行列を設けていますし、No.2 さんの場合は見出しの行列が 0 mm 用として働いているようです。上式や No.1 さんの数式では設けないので、ご注意。

250 mm 未満または 400 mm 未満の寸法を入力しても、MAX の働きでエラーとはなりません。0 mm 用の行列を設けたご回答でも、もちろんそのエラーは発生しません。

また、MAX のせいで、I2 と J2 のどちらかが未入力の場合に価格表から誤った値を拾ってしまうという問題は、「i2*j2」という部分で回避しています。

なお MATCH の第 3 引数に「1」ではなく「-1」（検索値以上の最小値を検索）を指定する方法だと、数列が降順に並んでいないときはエラーになってしまいます。そのため今回の表では、できません。

回答No.7

Nouble

ご回答します

添付図のように
Ｂ１６に横の値を、
Ｃ１６に縦の値を入れるとＤ１６に答えが返る物とします。

Ｄ１６=OFFSET($A$1,INDEX(FREQUENCY($A$2:$A$14,$C$16-POWER(10,-15+TRUNC(LOG($C$16,10)))),1,0)+1,INDEX(FREQUENCY($B$1:$G$1,$B$16-POWER(10,-15+TRUNC(LOG($B$16,10)))),1,0)+1,1,1)

寸評
こういった場合検索系の関数を頭に思い浮かべることが多いと思うのですが、
その多くが「～未満の値」を呼び出してきます。
すると「横２４９縦３９９」以下の寸法でエラーになってしまいます。
良くはありませんね。

なので今回は区間関数を使いました、
ＦＲＥＱＵＥＮＣＹ構文です。

しかも
本来のヘルプ記載とは違う、
裏技の使い方をしています。

此はいにしえの失われた技術、
まるでラピュタのような物です。　（汗）

書き方としては
ＦＲＥＱＵＥＮＣＹ（区間、検索値）
です。

例として、例えば
=FREQUENCY({250,300,350,400,450,500},260)
と書くと
｛１，５｝などと返されるのですが、
{250以下,250超過,300超過,350超過,400超過,450超過,500超過}
の何処に当たるかを、最初の引数「１」が
１つ少ない値で教えてくれます。

この関数に＃ＮＡはありません。

ただこのままでは
この関数でさえ「～未満の値」を探しますので
有効桁数ギリギリのあり得ないほど小さい値を計算で引いて
調整しています。

如何でしょうか？
お役に立てていたならば幸いです。

なお、
クラウドにサンプルファイルを置いていきますので、
お役立てください。

参考URL：

http://sdrv.ms/19wxXwQ

回答No.6

tom04

こんばんは！
すでに回答は出ていますが、参考程度で・・・

↓の画像で説明します。
右側がSheet2でSheet2に表を作成し、Sheet1に表示するとします。
↓の画像のように表に少し手を加えます。

画像では昇順に並んでいますので、○以上～次の行（または次の列）未満の区切りになります。
おそらくアップされている画像を拝見すると「～以下」というコトだと思います。
となるとぴったりの場合どちらに入れるか？によって大きく結果が変わりますので、

表の列・行の区切り寸法に0.1を加えて、小数点以下の表示を減らしています。
（Sheet1に入力される数値に小数点以下はない！という判断で・・・）

以上の準備ができた上でSheet1のC2セルに
=IF(COUNTBLANK(A2:B2),"",INDEX(Sheet2!B$2:G$14,MATCH(B2,Sheet2!A$2:A$14,1),MATCH(A2,Sheet2!B$1:G$1,1)))
という数式を入れオートフィルで下へコピーすると
画像のような感じになります。

※　すでに表の区切り数値を入れていてひとつひとつ0.1ずつ加えていくのが手間であれば
どこか使っていないセルに
0.1と入力　→　右クリック　→　コピー　→　0.1を加えたいセルを範囲指定　→　右クリック　→　「形式を選択して貼り付け」
→　「加算」を選択しOK
これですべてに0.1が加えられますので、小数点以下を表示しないようにします。

尚、Sheet1の入力値がmm単位である場合は加算を0.01のように調整してみてください。m(_ _)m

回答No.5

gyouda1114

No.2とNo.3です。
No.3の回答にNo.1ですと書いてしまいました。
すみませんNo.2の間違いでした。

No.1さん申し訳ありませんでした。

回答No.4

keithin

＞試したところ、#NAME?とセルに表示される

「試した」とは、回答の数式をコピーして、何も考えないでそのままエクセルに貼り付けてＥｎｔｅｒしたという事ですね。

では改めて、次の通りに操作しましょう。

１．Ｊ１セルにあなたの横の寸法、２６０を記入する
２．Ｊ２セルにあなたの縦の寸法、６２０を記入する
３．Ｊ３セルに数式として
　=IF(OR(J2="",J1=""),"",VLOOKUP(MAX(400,CEILING(J2,50)),$A$2:$G$14,MATCH(MAX(250,CEILING(J1,50)),$A$1:$G$1,0)))
と記入する。

回答No.3

gyouda1114

No.1です
参考のサイトを忘れました。

関数の“合体ワザ”で距離と重量から料金を算出せよ
http://pc.nikkeibp.co.jp/article/column/20120927/1064922/?P=1

回答No.2

gyouda1114

一例です
画像参照
D17及びD18を作業列とし、列、行の位置を求めます
D17に　=MATCH(C17,B1:G1,1)
D18に　=MATCH(C18,A2:A14,1)
C19に　=INDEX(B2:G14,D18,D17)

回答No.1

keithin

＞横260mmで縦620mmならC7の\14,200となる

そのご説明が正しいなら、あなたの表は「横２５０から３００までがＢ列」じゃなくて、「横２５０以下Ｂ列、２５０を超えて３００までＣ列」という区分で書かれているということですね。

計算例：
=IF(OR(縦のセル="",横のセル=""),"",VLOOKUP(MAX(400,CEILING(縦の寸法,50)),$A$2:$G$14,MATCH(MAX(250,CEILING(横の寸法,50)),$A$1:$G$1,0)))
といった具合で。

お礼

早速のご回答ありがとうございます。

試したところ、#NAME?とセルに表示されるのですが、なぜでしょうか？