• ベストアンサー
  • 困ってます

入力した数値を元にその他の数値を効率よく求める方法

お世話になります。 Excelの関数について質問させていただきます。 図を見て頂ければ早いかとは思いますが、元の量(A2~A5)を基準に、 現在ある量を入力する事でその他の量が自動で計算できる式を作りたいと思っています。 現時点で試しているのは IF関数を使い、数値の入っているセルを見つける →数値が入っていれば割合を求め数量を出す →数値がなければ次のセルに移動、以下繰り返し 具体的にD2のセルには以下のような計算式が入っています。 IF(ISNUMBER($C$2),$C$2/$A$2*A2,IF(ISNUMBER($C$3),$C$3/$A$3*A2, IF(ISNUMBER($C$4),$C$4/$A$4*A2,IF(ISNUMBER($C$5),$C$5/$A$5*A2, IF(ISNUMBER($C$6),$C$6*B2," "))))) 今は5行しか入っていないので動きますが、実際はもっとある為ネストの制限に引っかかりそうです。 なにより計算式が長くて修正するにも一苦労してしまいます。 ここまで来て行き詰ってしまいました。 既出でしたら申し訳ありません。知恵を貸して下さい。よろしくお願いします。

共感・応援の気持ちを伝えよう!

  • 回答数3
  • 閲覧数78
  • ありがとう数2

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.3
  • keithin
  • ベストアンサー率66% (5278/7939)

ちょっと誤記ました。倍率は,現在量が,該当する元の量の何倍あるかです。 参考に,もし現在量の用意が2種以上ある(しかも現在量の比率は元の量の必要比率と合っていない)として,その全てを使い切る前提で追加の必要量を算定してみると 添付図: C7 =MAX(C2:C5/A2:A5) と記入し,コントロールキーとシフトキーを押しながらEnterで入力する D2: =A2*$C$7-C2 以下コピー。 #もちろんこの方式でも,現在量が1種しか入力されて無くても問題なく計算できます。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

>現在量の用意が2種以上ある(しかも現在量の比率は元の量の必要比率と合っていない) 記載はしておりませんでしたがこれが最終目標だったため、ベストアンサーとさせていただきました。 丁寧な解説のおかげでどのような計算が行われているのかという筋道を把握する事ができ、計算式もとても簡単なものになりました。SUMPRODUCTは今まで使った事がない関数名だったので、関数の意味なども自分でもしっかりと理解して使いたいと思います。 本当に助かりました!ありがとうございます!

関連するQ&A

  • #260の補足です。四捨五入の方法教えて下さい。

    #5362 NO.#2にて、 IF(ISNUMBER(H2),IF(H2>1000,3000+6*700+6*(H2-1000),IF(H2>300,3000+7*(H2-300),"3000"))*65%,"")   の式を適用すれば、というご回答を頂きましたが、今度、この数式の結果の小数点以下を四捨五入させる必要が発生したため、 以下のように数式を変更しました。 =IF(ISNUMBER(H2),ROUND(IF(H2>1000,3000+6*700+6*(H2-1000),IF(H2>300,3000+7*(H2-300),"3000"))*65%,""),0) あるいは、 =ROUND(IF(ISNUMBER(H2),IF(H2>1000,3000+6*700+6*(H2-1000),IF(H2>300,3000+7*(H2-300),"3000"))*65%,""),0) すると四捨五入は出来るのですが、H2セルに数値を入れないと「#VALUE!」が再び出るようになりました。 どうすればいいでしょうか?

  • Excelの関数がうまく使えません。

    Excelで関数がうまく使えません。 「=IF(E2>=2000,200,IF(E2>=1000,100,IF(E2>=500,50," ")))」というのをH列のセルに入力しています。 計算はうまく?いくのですが、偽の場合に空白にしているのに、E列が空白でも200と反映されてしまいます。 ちなみにE列には「=IF(ISNUMBER(D2),(C2*D2)," ")」このような関数を使っております。 H列が空白のときに正しく空白にするにはどうしたらよいのでしょうか? 教えてください。

  • エクセルで"=AND(ISNUMBER(B2),ISNUMBER(C3),ISNUMBER(D4))"の簡略化?

    エクセル2000です。ワークシート関数での質問です。 特定の複数のセルのすべてに数値が入力されているかどうかの判定は、たとえば以下のような関数で調べられますね? =AND(ISNUMBER(B2),ISNUMBER(C3),ISNUMBER(D4)) このように少ないセルならこれでもいいのですが、多くなってくるといちいち、セルの数だけISNUMBER()を書いていくのが大変になります。 '=ISNUMBER(B2,C3,D4)←これでは当然エラーになりますが、こんな感じで簡単に判定するワークシート関数はないのでしょうか?

その他の回答 (2)

  • 回答No.2
  • keithin
  • ベストアンサー率66% (5278/7939)

簡単のため「現在の量」にはどれか一行しか入力しないのだとすると 添付図: C7に「現在ある量」が該当する必要量の何倍あるのか調べ, =SUMPRODUCT(C2:C5,1/A2:A5) D2以下にその倍率分算定します。 =A2*$C$7 以下コピー。

共感・感謝の気持ちを伝えよう!

  • 回答No.1
  • CoalTar
  • ベストアンサー率40% (705/1742)

「現在ある量」の一番下の数値を参照します。 =IF(COUNT($C$2:$C$5)=0,"", LOOKUP(8^3^8,$C$2:$C$5)/INDEX($A$2:$A$5,MATCH(8^3^8,$C$2:$C$5))*A2)

共感・感謝の気持ちを伝えよう!

質問者からのお礼

IF関数はLOOKUP・INDEX・VLOOKUPなどで応用できるとあったので出来る限り調べてみたところ、どうも知識が追いつかず形にできなくて質問してしまいましたが、このような方法もあるのですね。勉強になりました。 ありがとうございます!

関連するQ&A

  • セル内の文章から文字を抜出したいです

    セル内の文章から文字を抜出したいです。 文字の書き方は、本当にランダムな並び方をしています。 [例] A1セル あいうえお12345 ↓ B1セル ○○ A2セル かきくけこ456789 ↓ B2セル ■■ いま、下の関数式を入れていますが、5つしか文字を抜き出せません。 せめて倍の10個は抜出したいのですが、どうすればいいでしょうか? =IF(ISNUMBER(FIND("あいうえお",A2)),"OO",IF(ISNUMBER(FIND("さしすせそ",A2)),"■■",IF(ISNUMBER(FIND("かきくけこ",A2)),"□□",IF(ISNUMBER(FIND("たちつてと",A2)),"◇◇",IF(ISNUMBER(FIND("なにぬねの",A2)),"△△","その他"))))) よろしくお願いいたします。

  • EXCEL関数使用で結果が理解できません

    OS:WindowsXP EXCEL2000 以下の表が既に作成されています。 行 :O(英文字オーです) ------------------ 列 6 | 96 7 |216 8 |216 9 |185 10 |(空欄) 11 |(空欄) ここで、セルR6に以下の式が入力されています。 =INDIRECT("R"&MAX(IF(ISNUMBER(O6:O11),ROW(O6:O11)))&"C"&COLUMN(O6:O11),FALSE) セルR6には185が表示されているため、空欄でない最後の行の数値を 出力させる式のようなのですが、よく理解できません。 ISNUMBER(O6:O11)は全部数値だった場合にTrueになるのですか? ROW(O6:O11)は6でした。 IF(ISNUMBER(O6:O11),ROW(O6:O11))はFALSEです。 MAX(IF(ISNUMBER(O6:O11),ROW(O6:O11)))は0です。 COLUMN(O6:O11)は15です。 どなたか知恵をお貸しください。

  • エクセル IF式について、

    先日からこちらで=IFとisnumberの使い方を教えていただき、次のような式を作りました。 =IF(isnumber(C2),B2+C2+F2+G2+J2+K2,"") これで(C2)に数値がある時のみ計算結果を打ち返し、文字その他は空欄で、が出来ました。 次にやりたいことは、(C2)と(G2)と(K2)のいずれかに数値がある時に計算させる式はどのようにすればよろしいでしょうか? 以下の4つを試しましたがNGでした。 =IF(isnumber(C2,G2,K2),B2+C2+F2+G2+J2+K2,"") =IF(isnumber((C2)*(G2)*(K2)),B2+C2+F2+G2+J2+K2,"") =IF(isnumber((C2)OR(G2)OR(K2)),B2+C2+F2+G2+J2+K2,"") =IF(ISNUMBER((C2)*OR(G2)*OR(K2)),B2+C2+F2+G2+J2+K2,"") よろしくお願いします。

  • sum関数で合計が返らない。

    sum関数で、数値の入ったセルの合計を求めようとしましたところ、なぜか合計が1600とならなければならないところ、0になってしまいます。 数値の入ったセルが数値であること(文字列ではない)は、isnumberで確認をしたのですが、原因がわかりません。 どなたか解決方法をご存じの方がいましたらおしえてください。 excel2010です。

  • エクセルで文字列検索の関数

    エクセルのワークシート関数で質問です。 「A1セルに、文字列、A、B、Cの何れかを含み、かつCDを含まない」ことを調べる関数です。 素直に、 =AND(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1)),ISNUMBER(FIND("C",A1)),NOT(ISNUMBER(FIND("CD",A1)))) と長ったらしく書けば取得できることはわかるのですが、もっと簡潔なやり方がありそうな気がします。 どうか教えてください。

  • ISERROR関数を使用してもエラーが出る

    Excel2016でセルにUSDまたは、JPYの文字がある場合はエラーが出ないのですが、それ以外の文字の場合ISERROR関数を使用しても#VALUE!というエラーが出ます。 関数は以下の通りです。 =IF(OR(NOT(ISNUMBER($G5)),NOT(ISNUMBER($K5))),"",IF($B$5<ROUNDDOWN($I5*IF(NOT(ISERROR(SEARCH("JPY",$D5,1))),100,VLOOKUP(CONCATENATE(MID($D5,IF(OR(SEARCH("USD",$D5,1)=4,ISERROR(SEARCH("USD",$D5,1))),1,4),3),"JPY"),$A$12:$B$18,2,FALSE))*$G5*$B$8/IF($B$8=100000,10000,IF($B$8=10000,1000,IF($B$8=1000,100,))),0),"超過",ROUNDDOWN(IF(ISNUMBER($P5),-$P5,-$I5)*IF(NOT(ISERROR(SEARCH("JPY",$D5,1))),100,VLOOKUP(CONCATENATE(MID($D5,IF(OR(SEARCH("USD",$D5,1)=4,ISERROR(SEARCH("USD",$D5,1))),1,4),3),"JPY"),$A$12:$B$18,2,FALSE))*$G5*$B$8/IF($B$8=100000,10000,IF($B$8=10000,1000,IF($B$8=1000,100,))),0))) エラーが出ているのは以下の「【】」の部分のようです。 =IF(OR(NOT(ISNUMBER($G5)),NOT(ISNUMBER($K5))),"",IF($B$5<ROUNDDOWN($I5*IF(【NOT(ISERROR(SEARCH("JPY",$D5,1)))】&#65374; 数値を表示したいのですが解決方法を教えてください。 回答よろしくお願いします。

  • IsNumberについて

    Sub macro01() If WorksheetFunction.IsNumber(123) = ture Then MsgBox "数値です" End If End Sub WorksheetFunction.IsNumber(123)で、数値なら、MsgBox "数値です"って表示されると思ったのですが、 MsgBox "数値です"を通りません。 123を数値と認識してないのでしょうか?

  • max関数で#N/A

    k1,o1,r1にそれぞれ数式が入っています。 u1で =max(k1,o1,r1) と最大値を求めたいです。 すべてのセルが数値になっているときはもちろんu1も数値になりますが、 #N/Aがあると、u1は#N/A になります。 連続しているセルではないので、{=MAX(IF(ISNUMBER( … は使えません。 やり方をご教授ください。 エクセル2010を使ってます。 よろしくお願いします。

  • EXCEL関数について

    質問があります。 エクセルのA行に 定期点検 (月度検査) ラジオ修理 ボリューム つまみ 校正 視力検査 ボールペン 発注 替芯 購入 コピー用紙 経費 ポケットティッシュ配布用    ・    ・       ・  とあるのを、B行に下記の用に変換したいのですが、 検査 修理 検査 検査 購入 購入 購入 購入 その他 その他 その他    ・    ・       ・  下記のように関数をつくったのですが、うまく動きません =IF(A1="","",IF(ISNUMBER(FIND("点検",A1)),"検査",IF(ISNUMBER(FIND("修理",A1)),"修理",IF(ISNUMBER(FIND("校正",A1)),"検査",IF(ISNUMBER(FIND("検査",A1)),"検査",IF(ISNUMBER(FIND("発注",A1)),"購入",IF(ISNUMBER(FIND("購入",A1)),"購入",IF(ISNUMBER(FIND("経費",A1)),"購入,"IF(ISNUMBER(FIND("配布用",A1)),"購入","その他"))))) わかる方がいらっしゃいましたら、どうぞ教えて下さい。

  • VBAのCodeModule.Linesの記入方法

    Set Target = ThisWorkbook With Target.VBProject.VBComponents("Module2").CodeModule For i = 1 To .CountOfLines If .Lines(i, 2) = Range("AE77").FormulaArray = _ "=SUMPRODUCT((ISNUMBER(FIND(""1号施設"",R11C19:R72C19)))*1,((IF(ISNUMBER(範囲1),範囲1,0)+IF(ISNUMBER(範囲2),範囲2,0)+IF(ISNUMBER(範囲3),範囲3,0)+IF(ISNUMBER(範囲4),範囲4,0)+IF(ISNUMBER(範囲5),範囲5,0)+IF(ISNUMBER(範囲6),範囲6,0))>=8)*1)" Then .ReplaceLine i, Range("AE77").FormulaR1C1 = _ "=ROUNDDOWN((SUMIF(R11C19:R72C19,""*1号施設*"",範囲1)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲2)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲3)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲4)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲5)+SUMIF(R11C19:R72C19,""*1号施設*"",範囲6))/8,0)" End If Next i End With の If .Lines(i, 2) =     以下の Range("AE77").FormulaArray = _ "=SUMPRODUCT((ISNUMBER(FIND(""1号施設"",R11C19:R72C19)))*1,((IF(ISNUMBER(範囲1),範囲1,0)+IF(ISNUMBER(範囲2),範囲2,0)+IF(ISNUMBER(範囲3),範囲3,0)+IF(ISNUMBER(範囲4),範囲4,0)+IF(ISNUMBER(範囲5),範囲5,0)+IF(ISNUMBER(範囲6),範囲6,0))>=8)*1)"  Then 記入方法が解りません。 605行目からは2行にわたるコードを記載してるのですが、Range("AE77").FormulaArray = 以下>=8)*1)"まで(605行以外にもあるのでForで廻してます) 文字列として””で括るるんだろうなと思ってトライしましたが、””で括る場所が多すぎてどこに付ければいいのやらです。最初と最後を””付けるだけでなく元々("AE77")のようになっている場所には更に””を付けたりするのでしょうか?それも試しましたが、途中の:にも何かしなくてはならないような‥ どなたかご教示願います。