如何在Excel中创建按客户购买金额分配多抽奖次数的抽奖系统并随机选出中奖者

Hey there! Let's tackle this problem step by step—first getting the raffle ticket counts sorted in Excel, then figuring out how to pick winners fairly based on those weighted ticket numbers.

Step 1: Calculate Raffle Ticket Counts per Person #

First, let's add that new column to assign tickets based on spending. Assuming your purchase amounts are in column B (starting at B2), add a new column C (labeled Raffle Tickets) with one of these formulas:

For Excel 2019/365 (using IFS for cleaner code): #

=IFS(B2>=601,15,B2>=201,10,B2>=60,5,B2>=1,1,TRUE,0)

• This checks from the highest threshold down to avoid logical errors. The TRUE,0 catches any entries with $0 or negative amounts (you can adjust this if needed, like excluding those rows entirely).

For older Excel versions (nested IF): #

=IF(B2>=601,15,IF(B2>=201,10,IF(B2>=60,5,IF(B2>=1,1,0))))

Just drag the formula down the column to apply it to all participants.

Step 2: Pick Weighted Winners (2 Reliable Methods) #

Now for the fun part—randomly selecting winners where more tickets mean higher odds. Here are two methods depending on your list size:

Method 1: Expand the List (Great for Smaller Groups) #

This method creates a list where each participant is repeated exactly as many times as their ticket count, then picks a random entry from that expanded list.

1. Add a new column D (labeled Expanded Entries). For Excel 365, use the LET function to repeat names automatically:

   =LET(Participant,A2,Tickets,C2,SEQUENCE(Tickets),Participant)
   

   Drag this down—Excel will automatically generate the repeated entries for each person.
2. To pick a winner, use this formula in a blank cell:

   =INDEX(D:D,RANDBETWEEN(1,COUNTA(D:D)))
   

   Press F9 to refresh and pick a new winner. If you need multiple unique winners, just copy the formula and delete duplicates, or use UNIQUE with RANDARRAY for a cleaner approach.

Method 2: Weighted Random Selection (Better for Large Groups) #

If you don't want to clutter your sheet with an expanded list, use cumulative weights to pick winners mathematically:

1. Add a column D (labeled Cumulative Tickets). In D2, enter =C2. In D3, enter =D2+C3, then drag down to the end of your list. The last cell in column D will be your total number of tickets.
2. Generate a random ticket number in a blank cell (say, F2):

   =RANDBETWEEN(1,D$[last_row])
   

   Replace [last_row] with the row number of your final cumulative total (e.g., D100).
3. Match the random number to a participant with INDEX and MATCH:

   =INDEX(A:A,MATCH(F2,D:D,1))
   

   The MATCH function with 1 finds the largest cumulative value that's less than or equal to your random number—this ensures the winner is picked proportionally to their ticket count.

Bonus: Excel 365 Shortcut for Weighted Picks #

If you're on Excel 365, you can combine everything into one formula without extra columns:

=INDEX(A:A,XLOOKUP(RANDBETWEEN(1,SUM(C:C)),SCAN(0,C:C,LAMBDA(a,b,a+b)),SEQUENCE(ROWS(C:C)),,1))

• SCAN calculates the cumulative ticket counts on the fly, XLOOKUP matches the random number to the right participant, and INDEX pulls their name.

Quick Notes #

• For multiple unique winners, generate multiple random numbers and use UNIQUE to remove duplicates before matching.
• Always double-check your formulas with a test row to make sure ticket counts are assigned correctly (e.g., $59 gets 1, $60 gets 5, $200 gets 5, $201 gets 10, etc.).

内容的提问来源于stack exchange，提问作者Ragirate