Cara menentukan diskon maksimum yang diterima persentase ambang batas suatu populasi

Saya mencoba mencari tahu rumus apa yang akan membantu saya menemukan diskon maksimum yang diterima persentase ambang batas suatu populasi.

Misalnya pada tabel di bawah ini, saya ingin mengetahui diskon maksimum yang diterima oleh minimal 30% populasi.

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%

Hal ini cukup mudah jika data diurutkan seperti di atas berdasarkan jumlah diskon dalam urutan menurun. Caranya cukup sederhana, turunkan % kumulatif populasi dari atas hingga setidaknya 30% tercapai, lalu periksa % diskonnya.

Pada tabel di atas, minimal 30% populasi tercapai pada baris ke 8 (30,93% dari total populasi), sehingga maksimal diskon yang diterima minimal 30% populasi adalah 15%.

Namun, bagaimana jika datanya tidak berurutan? Bagaimana saya bisa mendapatkan jawaban yang sama dari tabel di bawah tanpa bisa mengandalkan pengurutan berdasarkan diskon tertinggi terlebih dahulu?

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 sumber
comment
Saya sangat menyarankan untuk menyortir data, itu membuat hidup jadi lebih mudah. Jika penting bagi Anda untuk mempertahankan urutan saat ini, tambahkan kolom pembantu bernomor, sehingga pengurutan pada kolom tersebut akan mengembalikan data Anda ke format aslinya.   -  person Luuklag    schedule 21.12.2017
comment
Terima kasih, Luuklag. Masalah yang saya hadapi adalah tabel sumber sebenarnya lebih kompleks daripada contoh di atas dan diurutkan berdasarkan kolom lain yang membuat data lebih mudah digunakan oleh pengguna akhir. Sayangnya ada beberapa tabel seperti itu dan setiap kali diskon dan jumlah populasi berubah, saya harus mengulangi tugas ini dengan mengubah urutan berdasarkan diskon tertinggi terlebih dahulu kemudian menemukan diskon maksimal untuk X% populasi. Saya berharap menemukan cara untuk menghilangkan tugas yang monoton itu.   -  person DLR    schedule 21.12.2017
comment
Itu sangat mungkin terjadi jika Anda ingin menulis kode VBA untuk itu.   -  person Luuklag    schedule 21.12.2017
comment
Power Query mungkin merupakan pilihan lain untuk menyortir.   -  person Alexis Olson    schedule 21.12.2017

Jawaban (1)


arrow_upward
0
arrow_downward

Saya dapat melakukan ini dengan bantuan kolom pembantu.

Tentukan kolom pembantu yang memberikan berapa banyak orang yang mendapat diskon tersebut atau lebih baik:

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

(dengan asumsi data Anda ada di A1:B15). Ini memberi Anda

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

Sekarang yang Anda inginkan adalah mencari diskon maksimal pada kolom B sehingga nilai pada kolom C minimal 30% dari jumlah orang.

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

Anda dapat, jika mau, menjadikan 30% hardcode tersebut menjadi referensi sel yang berisi parameter tersebut.

person Alexis Olson    schedule 21.12.2017
comment
MAXIFS tersedia di Excel 2016 dan seterusnya. Pendekatan berbeda mungkin diperlukan untuk versi Excel sebelumnya. - person shrivallabha.redij; 21.12.2017