如何在MS Excel中基于多条件对酒店收入进行排名

Hey there! Let's break down how to calculate that daily hotel revenue ranking per EU country—super common use case, and totally doable with a couple of tools depending on what you're working with.

First up, if you're using spreadsheets. Let's assume your data is in columns A to D:

• A = Day
• B = EUCountry
• C = Hotels
• D = Revenue

You'll want a ranking that resets every time the country or day changes. Here's how to do it:

For Excel 365 or Google Sheets (dynamic array support) #

Pop this formula into cell E2 (your new ranking column) and it'll auto-fill or spill down automatically:

=RANK.EQ(D2, FILTER(D:D, A:A=A2, B:B=B2), 0)

Let's unpack this:

• FILTER(D:D, A:A=A2, B:B=B2) grabs all revenue values that match the same day and same EU country as the current row.
• RANK.EQ then takes the current row's revenue and ranks it against that filtered list. The final 0 means we're ranking in descending order (highest revenue = rank 1)—swap it to 1 if you want ascending instead.

For older Excel versions (no dynamic arrays) #

Use this array formula (make sure to press Ctrl + Shift + Enter after typing it, instead of just Enter):

=RANK.EQ(D2, IF((A:A=A2)*(B:B=B2), D:D), 0)

The IF((A:A=A2)*(B:B=B2), D:D) part creates an array that only includes revenues from the same day and country, ignoring all others. RANK.EQ uses this array to calculate the rank.

If you're working with larger datasets or prefer code, Pandas makes this a breeze (after a quick cleanup step):

First, make sure your Revenue column is a numeric type (since your example has commas like 34,120):

import pandas as pd

# Load your data (adjust the read method to match your file type)
df = pd.read_csv('your_data.csv')

# Clean the Revenue column: remove commas and convert to float
df['Revenue'] = df['Revenue'].str.replace(',', '').astype(float)

Then add your ranking column in one line:

# Group by EUCountry and Day, then rank Revenue in descending order
df['Revenue_Rank'] = df.groupby(['EUCountry', 'Day'])['Revenue'].rank(ascending=False, method='min')

A quick note on the method parameter to fit your needs:

• 'min': Hotels with the same revenue get the same lowest rank (e.g., two hotels tied for rank 2 both stay at rank 2, next is rank 4)
• 'max': Same revenue gets the same highest rank
• 'first': Same revenue is ranked based on their position in the dataset
• 'average': Default, takes the average of the ranks for matching values

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