Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
【関数】別表の条件を可変できる数式を組みたい
投稿日時: 19/03/27 13:42:13
投稿者: かのん12345

いつもご教授頂きましてありがとうございます。
関数について知識が乏しい為、詳しい方何卒ご教授願います。
 
関数で条件に合致すれば、ポイントを付与したいということで下記数式を組みました。
 
=IFERROR(IF(COUNT(INDEX(1/(MATCH($J5,$J$5:$J$63,0)=ROW(A1)),0)),IF(N5=0,"",@60-(COUNT(1/(IF($L$5:$L$1000<>0,$N$5:$N$1000,-10^13)>N5))*2+COUNTIF($N$5:$N$1000,N5)-1)/2)*IF(VLOOKUP($J5,ID!$E$3:$F$61,2,FALSE)=➁$J$4,B1,IF($N5>0,C0.5,D0.25)),""),"")
 
当初、部署統一条件だった為、上記数式で希望する数字を算出できましたが
算出条件を部署によって別表で変更できるようにしたいと言われました。
なかなかいい案が思いつかず頭を悩ませております。
 
 
●別表●条件定義表の★ポイント〜★条件Bまでを可変して数式に反映できるようにしたいです。
※記載しました数式内@〜Dの部分。
 
●別表●条件定義表             
主担当部門コード(数式内➁)   1      2 3        
所属名   営業一課 営業二課 営業三課        
★ポイント(数式内@)     60     60     100    
★条件@(数式内B)         100%     50% 100%        
★条件A(数式内C)         50%       25% 50%        
★条件B(数式内D)         25%       15% 25%
 
●基本ポイント定義●                
優先順位       計画 実績 達成率 未達成/達成額        
ランキングに含めない    0 0 0%        0     →0P
ランキングに含めない    0 -100 0%     -100     →0P
優先度@        100 120 120%     20     →60Pから減点付与
優先度A     100 20 20%     -80     →優先度@→優先度Aの順に60Pから減点付与
                            
*上記+追加条件                            
条件@    担当者ID+主担当部門コードが紐づけば順位に応じて60Pから減点付与     
条件A    担当者ID+主担当部門コード以外だったら該当ポイントの1/2付与                
条件B    条件@or条件A+D列「達成額/未達額」が0orマイナスなら1/4(条件@or条件Aの半分付与)
 
何卒、ご教授願います。
 
 

回答
投稿日時: 19/03/27 14:48:36
投稿者: WinArrow
投稿者のウェブサイトに移動

掲示された数式が殆ど理解できないため、
お望みの提案ができませんが、
 
数式そのものが異なる場合はやむを得ないが
計算要素が異なるというのであれば、
計算要素だけを表にするという方法があります。
それも含めて
数式を「名前定義」にする方法を検討してみればと思います。
 
とりあえず、セル範囲が固定なのは気になります。
可変にするには、セル範囲に名前を付けて名前定義します。
(但し、ブックレベルではなく、シートレベルで)
 
 

投稿日時: 19/03/27 15:46:51
投稿者: かのん12345

WinArrow様
 
はじめまして。
アドバイス頂きましてありがとうございます。
 
名前定義ですか。
使用したことがない為、どのようにしたら良いかご教授頂けませんでしょうか?

回答
投稿日時: 19/03/27 16:35:45
投稿者: んなっと

表形式の具体例」が全く不十分です。質問し直し。
 
 
以下、適当に回答。
 
●IDシート
 
   E   F
2  ID 分類
3 001  ○
4 002  ○
5 003  ○
6 004   
7 005  ○
 
●Sheet1
 
    I  J   K   L    M    N     O P     Q   R   S   T
4 コード  ○ 計画 実績 達成率 達成額 ポイント    コード   1   2   3
5    1 001   0   0   0%    0    58    所属名 一課 二課 三課
6    1 001   0 -100   0%  -100        ポイント  60  60  100
7    1 002  100  120  120%   20    59    条件@ 100%  50% 100%
8    3 003  100  20   20%   -80    98    条件A  50%  25%  50%
9    2 005  200  290  145%   90    60    条件B  25%  15%  25%
 
とりあえず
HLOOKUP(I5,R$4:T$9,3,FALSE)で60,60,100の切り替え
HLOOKUP(I5,R$4:T$9,4,FALSE)で100%,50%,100%の切り替え
.......
だけ考慮すると、
 
O5
=IF(MATCH(J5,J:J,0)=ROW(),HLOOKUP(I5,R$4:T$9,3,FALSE)-(SUMPRODUCT((L$5:L$1000<>0)*(N$5:N$1000>N5))*2+COUNTIF($N$5:$N$1000,N5)-1)/2*IF(VLOOKUP(J5,ID!E:F,2,FALSE)=J$4,HLOOKUP(I5,R$4:T$9,4,FALSE),IF(N5>0,HLOOKUP(I5,R$4:T$9,5,FALSE),HLOOKUP(I5,R$4:T$9,6,FALSE))),"")
下方向・↓

回答
投稿日時: 19/03/27 16:37:40
投稿者: んなっと

あとExcelのバージョンも選択してください。
COUNTIFSが使えるかもしれないので。

回答
投稿日時: 19/03/27 16:57:10
投稿者: WinArrow
投稿者のウェブサイトに移動

余り面白くない例ですが・・・・
 
 
三角形の面積を求める例
 
例えば、B列セルに高さ、C列セルに底辺を入れて、A列セルの三角形の面積を計算させるとします。
勿論、
A1に「=(B1*C1)/2」と入力し、下へフィルドラッグすれば、対応することができますが、
数式が長〜いとか・で、数式を修正する必要が出た場合、全ての数式を変更しなくていけません。
そんな場合、数式を名前定義することで、この数式を修正するだけで、対応は簡単で、早くなります。
 
名前の定義を開き
名前:三角形の面積
参照範囲に「=(B1*C1)/2」を入力します
 
 
セルA1に
=三角形の面積
入力します。B1,C1の値で計算結果が表示されます。
下へフィルドラッグして数式複写できます。
 

回答
投稿日時: 19/03/27 18:56:32
投稿者: んなっと

IFERRORが使えているので、COUNTIFSも使えますね。修正。
 
O5
=IFERROR(IF(MATCH(J5,J:J,0)=ROW(),HLOOKUP(I5,R$4:T$9,3,FALSE)-(COUNTIFS(L$5:L$1000,"<>0",N$5:N$1000,">"&N5)*2+COUNTIF($N$5:$N$1000,N5)-1)/2*IF(VLOOKUP(J5,ID!E:F,2,FALSE)=J$4,HLOOKUP(I5,R$4:T$9,4,FALSE),IF(N5>0,HLOOKUP(I5,R$4:T$9,5,FALSE),HLOOKUP(I5,R$4:T$9,6,FALSE))),""),"")

回答
投稿日時: 19/03/28 10:27:35
投稿者: んなっと

なお、数式の中の
HLOOKUP(I5,R$4:T$9,3,FALSE)
HLOOKUP(I5,R$4:T$9,4,FALSE)
HLOOKUP(I5,R$4:T$9,5,FALSE)
HLOOKUP(I5,R$4:T$9,6,FALSE)
COUNTIFS(L$5:L$1000,"<>0",N$5:N$1000,">"&N5)
などの部分は、別のセルに入れて参照するようにすれば式を短くできます。
簡単な作業なので、自分で変更できるはずです。

投稿日時: 19/03/28 10:54:08
投稿者: かのん12345

んなっと様
 
お世話になっております。
丁寧でわかりやすくご教授頂きましてありがとうございます。
また、私の質問の仕方がわかりづらく申し訳ありません。
ご指摘頂きましたように具体例を記載いたしました。
 
また、数式ご教授頂きましたのに大変申し訳ありません。
達成額が0だった場合、ランキングに含めないようにしたいです。
今回ご教授頂きました数式を反映したところ0が含まれてしまいます。。。
また、下記数式のI5の部分が実際はリストには項目がなく、実際の表では●集計先●のJ4を参照するようにしたいです。
HLOOKUP(I5,R$4:T$9,3,FALSE)
 
何度も申し訳ありませんが、何卒ご教授頂けますと幸いです。
 
 
●IDシート
A           B   C   D    E       F
作業列        支店 拠点 担当者 担当ID 担当者主担当部門コード
支店&拠点&担当ID             1        1
支店&拠点&担当ID             2        3
支店&拠点&担当ID             3        2
支店&拠点&担当ID             4        1
 
●条件定義シート
A     B     C     D 
コード   1     2     3
所属名  一課   二課   三課
ポイント  60    60    100
条件@  100%  50%  100%
条件A  50%  25%  50%
条件B  25%  15%  25%
[/code]※コードは集計先のJ4の数字
※ポイントはMAX付与。そこから順位に応じて減点付与
※条件@〜Bは下記の通り。
 
優先順位       計画  実績  達成率  未達成/達成額            
ランキングに含めない    0     0     0%      0        →0P    
ランキングに含めない    0  -100     0%     -100        →0P    
優先度@       100   120   120%      20      →60Pから減点付与    
優先度A    100    20   20%     -80      →優先度@→優先度Aの順に60Pから減点付与                        
*上記+追加条件                                
条件@担当者ID+主担当部門コードが紐づけば順位に応じて60Pから減点付与 ※IDシート参照        
条件A担当者ID+主担当部門コード以外だったら該当ポイントの1/2付与                
条件B条件@or条件A+D列「達成額/未達額」が0orマイナスなら1/4(条件@or条件Aの半分付与)    
 
●集計先●  
J4に担当者主担当部門コード1or2or3が入っています。
J5~下へ担当IDが入っています。(達成額降順)
 
例)J4が「1」だった場合
J    K   L   M    N    O           
   計画 実績  達成率 達成額 ポイント        
1    80  100   125%   20   60       
3   100 110   110%   10   29.5       
2    0  5   0%   5   29          
4    57 -29  -36%  -28   28.5

投稿日時: 19/03/28 10:56:38
投稿者: かのん12345

 WinArrow様
 
お世話になっております。
 
名前定義についてわかりやすいご説明、ありがとうございます。
勉強になります。
 
また、理解不足で申し訳ありませんが追加で質問させて頂きます。
参照範囲が別になった場合は都度、名前定義を設定する必要があるということでしょうか?
 
何度も申し訳ありませんが、何卒ご教授頂けますと幸いです。

回答
投稿日時: 19/03/28 11:28:02
投稿者: んなっと

引用:
今回ご教授頂きました数式を反映したところ0が含まれてしまいます。。。

あの...最初の質問文に対して、まともな回答が得られるとでも思っているのですか?
 
修正後の質問文も、少しましになった程度です。もっと頑張りましょう。
 
●ID シート
 
   E    F
2  ID 担当者
3 001    1
4 002    1
5 003    1
6 004    2
7 005    3
 
●条件定義 シート
 
     A   B   C   D
1  コード   1   2   3
2  所属名 一課 二課 三課
3 ポイント  60  60  100
4  条件@ 100%  50% 100%
5  条件A  50%  25%  50%
6  条件B  25%  15%  25%
 
●集計先 シート
 
R4
=HLOOKUP(J$4,条件定義!B$1:D$6,ROW(R3),FALSE)
下方向・↓
 
   J   K   L    M    N     O P     Q   R
4  1 計画 実績 達成率 達成額 ポイント   ポイント  60
5 001   0   0   0%    0         条件@ 100%
6 001   0 -100   0%  -100         条件A  50%
7 002  100  120  120%   20    59    条件B  25%
8 003  100  20   20%   -80    58          
9 005  200  290  145%   90    60          
 
O5
=IFERROR(IF(MATCH(J5,J:J,0)=ROW(),IF(N5=0,"",R$4-(COUNTIFS(L$5:L$1000,"<>0",N$5:N$1000,">"&N5)*2+COUNTIF($N$5:$N$1000,N5)-1)/2*IF(VLOOKUP(J5,ID!E:F,2,FALSE)=J$4,R$5,IF(N5>0,R$6,R$7))),""),"")
下方向・↓

投稿日時: 19/03/28 11:42:05
投稿者: かのん12345

んなっと様
 
お世話になっております。
 
仰る通りですね。
大変失礼いたしました。
申し訳ございませんでした。
ご教授頂きましてありがとうございました。

投稿日時: 19/03/28 17:00:06
投稿者: かのん12345

んなっと様
 
お世話になっております
 
理解不足、説明不足で何度もお手数をおかけしております。
申し訳ありません。
 
再度、HLOOKUPの数式についてご教授願います。
ご教授頂きました数式で算出するとJ4が「1」以外の時の数字が希望した数字になりません。
私の質問の仕方がわかりづらかったのかと思います。
申し訳ありません。
 
●集計先●  
J4に担当者主担当部門コード1or2or3が入っています。
J5~下へ担当IDが入っています。(達成額降順)
 
例)J4が「1」の場合
※J列主担当コードが1、3の人はJ4が「1」なので、60Pから減点付与
※J列主担当コードが2の人はJ4が「1」以外の「2」の為、追加条件の通り58Pの1/2なので29Pとなるようにしたいです。
 

J    K    L    M     N     O          O    
   計画  実績   達成率  達成額  希望する数字  HLOOKUPで算出した数字       
1    350   590   169%     240      60              60
3   614   843   137%    229      59              59
2    20    97   488%    77        29                        58  
4   1000    0    0%     -1000          0                           56
5       42      40      95%        2           11                          56    

 
 
●条件●
優先順位       計画  実績  達成率  未達成/達成額             
ランキングに含めない    0     0     0%        0        →0P     
ランキングに含めない    0  -100     0%      -100        →0P     
優先度@       100   120   120%       20      →60Pから減点付与     
優先度A           100    20   20%        -80    →優先度@→優先度Aの順に60Pから減点付与      

                    
*上記+追加条件
条件@担当者ID+主担当部門コードが紐づけば順位に応じて60Pから減点付与 ※IDシート参照
条件A担当者ID+主担当部門コード以外だったら該当ポイントの1/2付与
条件B条件@or条件A+D列「達成額/未達額」が0orマイナスなら1/4(条件@or条件Aの半分付与)  
 
 
何度も申し訳ありませんが、何卒、よろしくお願いいたします。 

回答
投稿日時: 19/03/28 17:51:51
投稿者: んなっと

この流れだったら、普通
「申し訳ありませんでした。すべてのシートの表形式の具体例を最初から提示します。」
となりませんか?
面倒なのは嫌いですか?
回答者側でも条件をそろえられるので、意思の疎通がしやすくなり、無駄な時間が省けると思いませんか?
 
O5
=IFERROR(IF(MATCH(J5,J:J,0)=ROW(),IF(OR(K5*L5=0,N5=0),0,(R$4-(COUNTIFS(L$5:L$1000,"<>0",N$5:N$1000,">"&N5)*2+COUNTIF($N$5:$N$1000,N5)-1)/2)*IF(VLOOKUP(J5,ID!E:F,2,FALSE)=J$4,R$5,IF(N5>0,R$6,R$7))),""),"")

投稿日時: 19/03/28 17:52:37
投稿者: かのん12345

んなっと様
 
マナー、ルールを知らずに申し訳ありません。
大変失礼いたしました。
 
今更かとは思いますが、質問し直します。
 
質問の仕方のマナーがなっていなかったにも関わらず
ご教授頂きましてありがとうございました。

回答
投稿日時: 19/03/28 18:03:39
投稿者: んなっと

最低2時間はかけて作成してください。
うまく質問できない場合は、具体的な説明ができる別の方にかわってください。

投稿日時: 19/03/28 18:05:03
投稿者: かのん12345

んなっと さんの引用:
最低2時間はかけて作成してください。
うまく質問できない場合は、具体的な説明ができる別の方にかわってください。

 
 
ご迷惑をおかけいたしました。