【関数】複数条件に応じてポイントをつけたい

お世話になっております。

条件によってポイント付与する数式を組みたいのですが
条件が複雑で、どのような関数を組めば良いか、関数の知識も乏しく頭を悩ませております。
詳しい方教えて頂けないでしょうか？

K列　担当者ID
L列　計画
M列　実績
N列　達成率
O列　達成額/未達額
P列　ポイント付与欄

上記のようなデータがあります。

下記条件で、O列「達成額/未達額」の降順にポイントを付与したいです。
その際、下記の条件でポイントを付与したいのです。

ポイント集計先
①黄色セル　K5~K18：担当者ID
➁赤色セル　K4：部門コード
※画像添付いたします。

ポイント付与条件別表
黄色セル　E列：担当者ID　※上記①と紐づく
ピンクセル　F列：担当者に紐づく主担当部門コード　※上記➁と紐づく
※補足へ画像添付いたします。

★条件★
・基本
O列
①「達成額/未達成額」が0以上なら降順に10Pからポイント付与
➁「達成額/未達成額」が0orマイナスならポイント付与せず
※ただし、マイナスでも実績があればポイント付与

上記、基本条件に加えて、下記条件も組み込みたいです。
①担当者ID+主担当部門コードが紐づけはO列に10PからポイントMAX付与
➁担当者ID+主担当部門コード以外だったら該当ポイントの1/2付与
かつ、「達成額/未達成額」が0orマイナスなら1/2のポイントの半分付与

何卒、よろしくお願いいたします。

質問者からの補足コメント

• 

  ※紐づけコードの画像を添付いたします。

  
    補足日時：2019/03/07 15:58

• 

  ふじのくま様
  
  お世話になっております。
  先日はご教授頂きましてありがとうございました。
  
  無事、求めたい数字を算出できました。
  感謝いたします。
  
  ただ、もう1つ条件を加えたく。。
  再度ご教授頂けませんでしょうか？
  
  K5~K18の中で重複した番号があります。
  L5~O5に数字が入っている番号にポイントを付与して
  数字が入っていない数字にはポイントを付与しない、という式を追加したいのです。
  
  何度も申し訳ございませんが、何卒、ご教授頂けますと幸いです。
  
  よろしくお願いいたします。

  No.3の回答に寄せられた補足コメントです。 補足日時：2019/03/13 17:06

• ふじのくま様
  
  お世話になっております。
  
  ご教授頂いた数式を利用して作成した表の画像添付いたします。
  計画列の左側の数字が重複し、重複した数字の計画、実績は0なのですが
  ポイント列にはポイントが計算されるようになってしまっております。
  
  知識不足で申し訳ありません。
  他に何かいい案はありませんでしょうか？
  
  何卒、ご教授願います。

  
  No.5の回答に寄せられた補足コメントです。 補足日時：2019/03/18 10:51

No.2 ベストアンサー 回答者： ふじのくま 回答日時： 2019/03/08 23:06

まず基本条件の方からいきます。

１．セルP5に　「　=IF($M5>0,MAX(10+1-RANK.AVG($O5,IF($M$5:$M$1000>0,$O$5:$O$1000,"")),0),"")　」を入力します。
２．必要なだけ下方向にコピーします。（完了）

＊RANK.AVG関数で”達成額／未達成額”の順にランキング（１～）をつけ、11から差し引く、という方法をとります
＊”実績”がマイナスのものはランキング付けそのものから除外するので、IF関数で検索対象の配列を絞り込みます
（IF関数に配列を入れたら答えも配列で返してくれるので、その絞り込んだ配列に対してRANK.AVR関数を使います。）
＊マイナスのポイントは排除したいので、MAX関数でゼロと比較して正の値のみを採択します

追加条件込みの方は、以下です。
１．セルP5に　「　=IF($M5>0,MAX(10+1-RANK.AVG($O5,IF($M$5:$M$1000>0,$O$5:$O$1000,"")),0)*IF(VLOOKUP($K5,$E$3:$F$1000,2,FALSE)=$K$4,1,IF($O5>0,0.5,0.25)),"")　」を入力します。
２．必要なだけ下方向にコピーします。（完了）

＊担当IDから担当部門コードの検索にはVLOOK関数を使います。VLOOK関数が正常に動作するには、担当IDに漏れがない事と番号順に並んでいる事が必要ですので、これ前提で考えて下さい。
＊追加条件に準じ、ポイントを1倍したり、0.5倍したり、0.25倍したり、しました。

この回答へのお礼

ふじのくま様

はじめまして。
この度は、ご教授頂きましてありがとうございます。
また、わかりやすいご説明感謝いたします。
とても勉強になります。

月曜日に実際の管理表で数式を加え、確認させていただきます。また、不明点等あればお問い合わせさせて頂いてもよろしいでしょうか？

よろしくお願いいたします。

お礼日時：2019/03/09 08:51

No.5 回答者： ふじのくま 回答日時： 2019/03/15 18:28

分かりました。

では、回答No.4の式は撤回しますので、お願いします。

さて、回答No.2の式でその通りに動作するはずでした。（「　=IF($M5>0,～　」の部分により、M列がゼロならランク付けから除外する）
今回、質問者さんのところで、M列がゼロにも関わらずポイント付与があるなら、もしかしたらM列に0.1などの小数が入力されゼロに見えているだけの可能性は無いでしょうか。（単なる推測です、すいませんが）
一度確認いただけますか。

この回答へのお礼

お世話になっております。

何度も申し訳ありません。
月曜日以降、確認させていただきますね。

よろしくお願いいたします。

お礼日時：2019/03/16 15:56

No.4 回答者： ふじのくま 回答日時： 2019/03/14 11:24

無事に進展されたようで良かったですね。

追加の件は、M列の入力内容に注目し、以下で期待通りになるか、確認してもらえますか。

・式中の「　～　IF($M$5:$M$1000>0,　～　」の部分を、「　～　IF($M$5:$M$1000>=0,　～　」に変更する（イコール追加）

もし質問者さんの想定と違っていたら、お手数ですが、また教えて下さい。

この回答へのお礼

ふじのくま様

お世話になっております。

再度ご教授頂きましてありがとうございます。
ただ、ご教授頂いた内容で解決できませんでした(´；ω；`)

ちなみに、L~O列には”0”と入力されております。

恐れ入りますが、再度ご教授頂けますと幸いです。

よろしくお願いいたします。

お礼日時：2019/03/14 16:19

No.3 回答者： ふじのくま 回答日時： 2019/03/09 13:22

はい、分かりました。

頑張って( ‘ ᴗ ‘)／

No.1 回答者： ぐーるぐる 回答日時： 2019/03/07 17:23

お、だいぶん進化したね(^_-)-☆

F列の意味がよくわかんないけど、もし複数の担当部門とかエリアとかあるんだったとしたら、
やり方は、こんなこと。以下サンプルね。まずは、リストをしっかり作る。それを組み合わせる。

list_担当者
担当者ID 担当者名
T0001 A田 A子
T0002 B川 B美
T0003 C上 C代
T0004 D田 D子
T0005 E川 E美

list_エリア
エリアID エリア名
A0001 A
A0002 B
A0003 C
A0004 D
A0005 E

tbl_担当者エリア
担当者ID 担当者名 エリアID エリア名 サブエリアID サブエリア名
T0001 A田 A子 A0001 A A0002 B
T0002 B川 B美 A0001 A A0002 B
T0003 C上 C代 A0001 A A0002 B
T0004 D田 D子 A0001 A A0002 B
T0005 E川 E美 A0001 A A0002 B
T0006 F上 F代 A0002 B A0004 D
T0007 G田 G子 A0002 B A0004 D
T0008 H川 H美 A0002 B A0004 D
T0009 I上 I代 A0002 B A0004 D
T0010 J田 J子 A0002 B A0004 D

まずは、データなのか？表なのか？
データとはシンプルで整理されたリストのこと。
表とは、データリストを組み合わせたもの。