Excel (一般機能)

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

 
(指定なし : 指定なし)
【関数】複数条件に応じてポイントをつけたい
投稿日時: 19/03/26 10:04:34
投稿者: かのん12345

はじめまして。
関数について知識があまりない為、何卒お知恵をお借りできましたらと思い投稿させて頂きます。


◆添付図@◆

作業列				
1	計画	実績	達成率	達成/未達額  ポイント
40	182,000	326,980	180%	144,980	      60.00 
1	133,000	216,000	162%	83,000      59.00 
17	238,000	300,000	126%	62,000	      58.00 
19	135,500	193,400	143%	57,900        57.00 
4	20,000	60,000	300%	40,000	      28.00 
46	214,000	214,000	100%	0	      55.00 
25	412,000	422,971	103%	10,971	      54.00 
57	13,600	20,000	147%	6,400	      27.00 
5	0	0	0%	0	       0.00 
56	0	0	0%	0	       0.00 
39	0	0	0%	0	       0.00 
46	0	0	0%	0	       0.00 
13	850,000	846,588	100%	-3,412	      12.00 
10	300,000	294,358	98%	-5,642	      12.00 
20	20,000	14,140	71%	-5,860	      12.00 

 
@
作業列には「=IFERROR(VLOOKUP(B5&C5&D5,ID!$A$3:$E$61,5,0),"")」という数式が入っています。
別リストと元データで合致した番号を検索するようにしています。
※重複番号は「46」とします。
 
A計画 ※実績も同様の数字が入っています。
計画に「=IFERROR(IF(COUNTIF(J$5:J5,J5)=1,SUMIF(J:J,J5,E:E),"0"),"0")」という数式が入っています。
作業列中に重複した番号が存在します。
重複している番号は元データからの抽出時、データを合算して表示したいです。
かつ、元データがN列「達成/未達成額」降順に並んでいるので、合算したデータも作業列では降順に並べたいです。
また、重複番号1つ目にデータを合算した数字を表示、2つ目以降には数字を表示させないようにしたいです。
 
現状合算した数字で表示するようにはできているのですが「達成/未達成額」が0にも関わらず
上位に表示されてしまっております。(参照:一番最初の「46」)
これを回避するにはどう数式を修正すればよろしいでしょうか?
 
◆添付図A◆
★基本ポイント定義                            
優先順位	   計画  実績  達成率 未達成/達成額			
ランキングに含めない	0 	0 	0%	 0 	→	0P	
ランキングに含めない	0     -100 	0%	-100 	→	0P	
優先度@	    100     120 	120%	 20 	→	60Pから減点ポイント付与	
優先度A	       100 	20 	20%	-80 	→	優先度@の次に60Pから減点ポイント付与
    
                            
*上記+追加条件                            
条件@    担当者ID+主担当部門コードが紐づけばMAXポイント付与                    
条件A    担当者ID+主担当部門コード以外だったら該当ポイントの1/2付与                
条件B    条件@or条件A+D列「達成額/未達額」が0orマイナスなら1/4(条件@or条件Aの半分付与    
                            
★条件定義表                            
主担当部門コード	1	2	3				
所属名	              @課	A課	B課				
ポイント	      60	60	100	★			
条件@	              60	60	100	★			
条件A	              30	30	50	★			
条件B	              15	15	12.5	★
            
 
B
添付図Aにあるような条件定義に添ったポイント付与をしたいです。
Aにあるように作業列に重複した番号があります。
重複した番号があれば、1つ目にはポイント付与し、2つ目以降にはポイント付与せずとしたいです。
「=IF($L6>0,MAX(60+1-(RANK.AVG($N6,IF($M$5:$M$1000>=0,$N$5:$N$1000,"")),0)*IF(VLOOKUP($J6,ID!$E$3:$F$61,2,FALSE)='2018.10月'!J$4,1,IF('2018.10月'!$N6>0,0.5,0.25)),"")
ただし、「達成/未達成額」が0になっている人も順位付けされてしまっております。0になっている人は除き、それ以降の人に減点でポイントを付与するようにしたいです。
 
C
Bの数式を添付図Aにあるように、別シートの「条件定義」の数字を可変したら反映できるような数式にしたいです。
※★印をつけた部分です。
 
恐れ入りますが、何卒ご教授頂きます様お願いいたします。
[/code]

回答
投稿日時: 19/03/26 13:18:02
投稿者: Suzu

提示頂いた「表」「数式」「条件」回答者では、関連性が見出せません。
 
=IFERROR(VLOOKUP(B5&C5&D5,ID!$A$3:$E$61,5,0),"")
 
B〜E列って表の中でどこ?
シートIDって何?提示頂いている表の中にあるの?
 

引用:
計画 ※実績も同様の数字が入っています。

数字?数式?
 
 
引用:
条件@ 担当者ID+主担当部門コードが紐づけばMAXポイント付与

 
どこの値 を 担当者ID と言うのですか?
主担当部門コードが、 条件定義表 1 2 3 の事ですか?
どこの値と紐づけるのですか? MAXポイントとは何ですか?
 
 etc・・
 
客観的に質問文を読んでみてください。理解できますか?
 
理解できない物に回答はできません。
 
 
回答する立場に立った時、
「質問が理解できないし、質問がいくつもあり 時間が掛かりそう」
積極的に回答をしようとは思えなくなってしまいます。
 
焦る気も判りますが、ひとつづつ潰していっては如何ですか?

投稿日時: 19/03/26 13:24:21
投稿者: かのん12345

はじめまして。
 
ご指摘、ごもっともですね。
大変失礼いたしました。
質問し直します。
 
ありがとうございました。

回答
投稿日時: 19/03/26 16:16:57
投稿者: んなっと

    E   F    G      H     I  J
 4 計画 実績 達成率 達成/未達額 ポイント ID
 5  182  327  180%     145    60 40
 6  238  300  126%      62    58 17
 7  136  193  143%      58    57 19
 8  110  126  115%      16    55 46
 9  412  423  103%      11    54 25
10  14  20  147%      6    27 57
11  104  88   85%     -16     0 46
12  850  780   92%     -70    12 13
 
上の範囲を選択して
 
 挿入→ピボットテーブル→OK
→IDを「行」にドラッグ
 計画、実績をチェックして「Σ値」に追加
→ピボットテーブルツール 分析 フィールド/アイテム/セット
→集計フィールド
 名前:
 率
 数式:
 =実績/計画
→OK
→率の数値(小数になっている)のどれかを選択して、
 フィールドの設定→表示形式 パーセンテージ
→達成/未達成をチェックして「Σ値」に追加
 達成/未達成の数値のどれかを選択して、
 降順に並べ替え
 
   A      B      C   D          E        F
 3 ID 合計 / 計画 合計 / 実績 比率 合計 / 達成/未達額 合計 / ポイント
 4 40     182     327 180%         145        60
 5 17     238     300 126%         62        58
 6 19     136     193 142%         58        57
 7 25     412     423 103%         11        54
 8 57      14      20 143%          6        27
 9 46     214     214 100%          0        55
10 13     850     780  92%         -70        12

投稿日時: 19/03/27 07:30:32
投稿者: かのん12345

んなっと様
ご教授頂きましてありがとうございます。
ご教授頂いた方法で対応させていただきます。
ありがとうございました。