[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『配列関数を使用せず複数の条件から求められる数値を集計したい』（頭の固い上司）
みなさんはじめまして、助けてください（長文・愚痴ありすみません）
頭の固い（エクセル使えない）上司から面倒な事を言われています。
はるか下のほうにあるデータで、作付必要数を求める自動計算式を入れてくれとのこと。
どうもこの上司様は、1行目から13行目までのデータをわざわざ電子卓上計算機という
優れもので手計算（ついでに四捨五入）し、その集計結果を手入力しておられます。
おまけに総合計もすべて電卓で手計算し手入力されているという、もはやエクセル
開発者が聞いたら寝込んでしまうような使い方をしていらっしゃるのです。
ところが最近はその計算がだんだん面倒になったらしく、というかエクセルで
自動計算できるということをタバコ部屋で誰かに入れ知恵されたようで、
ようやくエクセルが何者であるかを理解したようです。そして意気揚々と、りんごの
集計をしようとセルに入れた内容が「1000÷95%÷86%+2000÷…=」という文章を
入力して計算できないとほざく始末。そりゃあ計算できんだろ。
とうとう、これまで面白かったので遠目で生暖かく見守っていた私に、なんとかして
くれというヘルプがきました。ではひとまずF列に各々の割り算式を入れて作付必要数
を求めSUMIF関数で集計するというデータを作って渡してみました。すると「どうして
Ｆ列に余計なデーターを入れるんだ、他部門の人が見たらフォーマットが変わって困
るだろう」と、作り直せの指示がありました。
こちらとしても、「絶対に段階を踏まえた計算結果のプロセスがわかるようにした
ほうが、今後別の担当がデーターを使用する場合に理解しやすいはず」と説得するも
「計算方法が開示されることは業務内容の守秘任務にに反するから却下である」
といわれ、アゴが外れそうになりました。
じゃあ配列関数（Shift+Ctrl+Enterのやつ）で作って渡すと、表示式の{ }の意味が
わからないらしく、いじり倒して式を戻せないことに。それじゃあ触れないように
セルの保護をかけると、マニアックなことはするな普通にしておけと。
（やれやれお前自身が、SUMIF=なにそれサムがどうした？っていうレベルのくせに
いっちょまえな事を言うなよな）
というわけで、作付け必要数を求められる適当な関数ってないでしょうか。
SUMPRODUCTでなんかできそうな気もするのですが、割り算なので手こずってます。
ちなみに作付率と収穫率を掛け算方式、つまり係数方式にするとSUMPRODUCTで簡単に
計算できますと提案したのですが、率を係数に変更するには役員決裁が必要、その決裁書
をお前が書いて、上司からの依頼によるものでご裁可くださいとワシがハンコをもらい
にいくような恥をかかすのか？とややこしい事をいうのであきらめました。
A B C D E 1 品種 作付率 収穫率 収穫見込 地域 2 りんご 95.00% 86.00% 1,000 北部 3 みかん 90.00% 81.00% 1,500 北部 4 いちご 80.00% 72.00% 2,000 北部 5 ぶどう 60.00% 54.00% 3,000 東部 6 りんご 85.00% 77.00% 2,000 東部 7 みかん 65.00% 59.00% 5,000 東部 8 いちご 70.00% 63.00% 3,000 西部 9 ぶどう 75.00% 68.00% 2,000 西部 10 りんご 90.00% 81.00% 1,000 西部 11 みかん 60.00% 54.00% 1,500 南部 12 いちご 50.00% 45.00% 2,000 南部 13 ぶどう 55.00% 50.00% 5,000 南部 14 15 作付必要数 16 りんご 5,652 17 みかん 19,726 18 いちご 19,164 19 ぶどう 31,363 20 総合計 75,905
長文お付き合いありがとうございます。これらは実在する人物で、約もう一名
「エクセルは計算ミスするから電卓の結果が正しい」と宣言する上司も
いらっしゃいます。計算ミスではなく式のミスだと思うのですがねえ・・・
やめてえ
＜ 使用 Excel：Excel2013、使用 OS：Windows8 ＞
要するに、作業列は使わず、Ctrl＋Shift＋Enter で確定しないでも済む式なら その上司さんはホクホク顔の上機嫌ということ？
2行目から13行目は全部データで埋まってます？ 空白セルはないのか？ってことだけど。
ないんだったら
D16 =SUMPRODUCT(ROUND(($A$2:$A$13=C16)*$D$2:$D$13/$B$2:$B$13/$C$2:$C$13,0))
下へコピー
こういうこと？ （笑） 2016/02/07(日) 09:25
なんとなく空白セルありそうな気がしてきたので、、 一応範囲内に空白があることを考慮すると
空白は数式の「""」ではない、未入力セルだとして
D16 =SUMPRODUCT(ROUND(($A$2:$A$13=C16)*$D$2:$D$13/TEXT($B$2:$B$13,"標準;;1")/TEXT($C$2:$C$13,"標準;;1"),0))
こんな感じかな？ （笑） 2016/02/07(日) 09:56
ありがとうございます。
空白セルの件が悩みの種で、ご回答いただきありがとうございました。
案の定、上司から「ゼロの場合はどうなんだ」ということで、ゼロ値を
入力するとエラー値も出ず「ほほー」と満足げな様子で助かりました。
ただ一点、作付率や収穫率がゼロの場合は作付必要数がゼロにならないのは
どうしてだ？と、ほざいていたので「そもそも作付率も収穫率もあきらかにゼロ
の場所で収穫見込みを立てることじたいがおかしいでしょ」と答えたところ
「いやいや、そのようなシミュレーションをする連中がいるかもしれないから
今度考えておいてくれ」と。
（それって、あんたくらいなもん）と喉元まで出かけたのですが、
そのような数式は可能なものでしょうか？
（頭の固い上司） 2016/02/07(日) 12:11
波線部を追加しただけの安直な式ですが、、
D16 =SUMPRODUCT(ROUND(($A$2:$A$13=C16)*($B$2:$B$13>0)*($C$2:$C$13>0)*$D$2:$D$13/TEXT($B$2:$B$13,"標準;;1")/TEXT($C$2:$C$13,"標準;;1"),0))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
こんな式見せて「マニアック」とか「冗長」とかほざきませんかね？ （笑） 2016/02/07(日) 13:25
大変ありがとうございました。
なるほど、そのような方法があったとは目からウロコです。
あれから上司は、というとそれぞれの条件を入力すると作付必要数が
ピョコピョコと変化するのが楽しいらしく、電卓も使って検算しながら
遊んでいるようです。
ただ、たまに作付率や収穫率を0％にすると思った結果にならないので
チラチラとこちらを伺っているようですが、知らんぷりを決め込んでいます。
まあ一生やっててくださいと、心の中でつぶやいた次第です。
明日になれば、例の式はどうだ？とか聞いてくるかもしれないので
活用させていただきます。
大変貴重なお知恵を教えていただきありがとうございました。
（頭の固い上司） 2016/02/07(日) 17:23
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.