วิธีกำหนดส่วนลดสูงสุดที่เปอร์เซ็นต์เกณฑ์ของประชากรที่ได้รับ

ฉันกำลังพยายามหาว่าสูตรใดจะช่วยให้ฉันค้นหาส่วนลดสูงสุดที่เปอร์เซ็นต์เกณฑ์ของประชากรได้รับ

ตัวอย่างเช่น ในตารางด้านล่าง ฉันต้องการทราบส่วนลดสูงสุดที่ประชากรอย่างน้อย 30% ได้รับ

Population   Discount
400          25%
3000         24%
2000         23%
1000         22%
850          20%
400          19%
350          18%
350          15%
1500         13%
450          12%
700          11%
3000         5%
7000         3%
6000         2%

วิธีนี้จะง่ายเพียงพอหากข้อมูลถูกจัดเรียงตามด้านบนด้วยจำนวนส่วนลดจากมากไปน้อย ง่ายพอที่จะลด % สะสมของประชากรจากด้านบนลงจนกระทั่งถึงอย่างน้อย 30% จากนั้นตรวจสอบ % ส่วนลด

ในตารางด้านบน มีประชากรถึงอย่างน้อย 30% ที่แถวที่ 8 (30.93% ของประชากรทั้งหมด) ดังนั้นส่วนลดสูงสุดที่ได้รับสำหรับอย่างน้อย 30% ของประชากรคือ 15%

อย่างไรก็ตาม จะเกิดอะไรขึ้นหากข้อมูลไม่อยู่ในลำดับนั้น ฉันจะได้คำตอบเดียวกันจากตารางด้านล่างโดยไม่ต้องพึ่งการเรียงลำดับตามส่วนลดสูงสุดก่อนได้อย่างไร

People   Discount
700      11%
3000     24%
3000     5%
1000     22%
6000     2%
400      25%
350      18%
350      15%
1500     13%
450      12%
850      20%
400      19%
7000     3%
2000     23%

excel excel-formula
person DLR    schedule 21.12.2017    source แหล่งที่มา
comment
ฉันขอแนะนำอย่างยิ่งให้จัดเรียงข้อมูล ซึ่งจะทำให้ชีวิตง่ายขึ้นมาก หากเป็นสิ่งสำคัญสำหรับคุณที่จะต้องรักษาลำดับปัจจุบัน ให้เพิ่มคอลัมน์ตัวช่วยที่มีตัวเลข ดังนั้นการเรียงลำดับคอลัมน์นั้นจะทำให้ข้อมูลของคุณกลับไปอยู่ในรูปแบบดั้งเดิม   -  person Luuklag    schedule 21.12.2017
comment
ขอบคุณลูกลาก. ปัญหาที่ฉันมีคือตารางต้นฉบับจริงมีความซับซ้อนมากกว่าตัวอย่างด้านบนและจัดเรียงตามคอลัมน์อื่นที่ทำให้ผู้ใช้ปลายทางบริโภคข้อมูลได้ง่ายขึ้น น่าเสียดายที่มีหลายตารางดังกล่าว และทุกครั้งที่ส่วนลดและจำนวนประชากรเปลี่ยนแปลง ฉันจะต้องทำซ้ำโดยเปลี่ยนลำดับการจัดเรียงด้วยส่วนลดสูงสุดก่อน จากนั้นจึงค้นหาส่วนลดสูงสุดสำหรับ X% ของประชากร ฉันหวังว่าจะพบวิธีขจัดความซ้ำซากจำเจของงานนั้น   -  person DLR    schedule 21.12.2017
comment
นั่นจะเป็นไปได้มากหากคุณเต็มใจที่จะเขียนโค้ด VBA สำหรับสิ่งนั้น   -  person Luuklag    schedule 21.12.2017
comment
Power Query อาจเป็นอีกตัวเลือกหนึ่งสำหรับการเรียงลำดับ   -  person Alexis Olson    schedule 21.12.2017

คำตอบ (1)


arrow_upward
0
arrow_downward

ฉันสามารถทำได้ด้วยความช่วยเหลือของคอลัมน์ช่วยเหลือ

กำหนดคอลัมน์ตัวช่วยที่ระบุจำนวนผู้ที่ได้รับส่วนลดหรือดีกว่า:

=SUMIFS($A$2:$A$15,$B$2:$B$15,">="&B2)

(สมมติว่าข้อมูลของคุณอยู่ใน A1:B15) สิ่งนี้จะช่วยให้คุณ

People   Discount  Helper
700      11%       11000
3000     24%       3400
3000     5%        14000
1000     22%       6400
6000     2%        27000
400      25%       400
350      18%       8000
350      15%       8350
1500     13%       9850
450      12%       10300
850      20%       7250
400      19%       7650
7000     3%        21000
2000     23%       5400

ตอนนี้สิ่งที่คุณต้องการคือค้นหาส่วนลดสูงสุดในคอลัมน์ B โดยที่ค่าในคอลัมน์ C มีค่าอย่างน้อย 30% ของจำนวนคนทั้งหมด

=MAXIFS(B2:B15,C2:C15,">="&30%*SUM(A2:A15))

คุณสามารถทำให้ฮาร์ดโค้ดนั้น 30% เป็นการอ้างอิงเซลล์ที่มีพารามิเตอร์นั้นได้หากต้องการ

person Alexis Olson    schedule 21.12.2017
comment
MAXIFS มีอยู่ใน Excel 2016 เป็นต้นไป อาจต้องใช้แนวทางที่แตกต่างออกไปสำหรับ Excel เวอร์ชันก่อนหน้า - person shrivallabha.redij; 21.12.2017