按指定逻辑为Unit ID分配销售周期的技术实现问询

Alright, let's figure out how to implement this Unit ID to Period allocation logic. I'll walk you through two practical methods—one for spreadsheet users (Excel) and another for folks who need code-based automation (Python).

Core Logic Recap #

First, let's restate the key rules to make sure we're aligned:

• Ignore any Period with a Units value of 0 (like Periods 3,7,8,9 in your example)
• Assign consecutive Unit IDs to each valid Period, following the original Period sequence
• The number of Unit IDs per Period matches its Units count

1. Excel Formula Approach #

This is perfect if you're working directly in spreadsheets. Here's how to set it up:

Step 1: Create a Filtered & Cumulative Helper Table #

First, make a helper table that only includes Periods with non-zero Units, plus a cumulative units column to define the Unit ID ranges:

Calculate the cumulative units column (e.g., cell D2) with =C2, then D3 would be =D2+C3, and drag the formula down to fill the rest of the column.

Step 2: Assign Periods to Unit IDs #

Assume your Unit IDs are in column A (starting at A2 with value 1). In cell B2, use this formula to get the corresponding Period:

=LOOKUP(A2, $D$2:$D$7, $B$2:$B$7)

Drag this formula down for all Unit IDs in column A.

How it works:

The LOOKUP function finds the largest value in the cumulative units column that's less than or equal to the current Unit ID, then returns the matching Period from the helper table. This automatically maps each Unit ID to the correct Period range.

2. Python Code Approach #

Use this if you need to automate the process, handle large datasets, or integrate with other systems.

Code Implementation #

# Define the valid Period -> Units mapping (exclude periods with 0 units)
period_units = {1: 5, 2: 1, 4: 3, 5: 2, 6: 4, 10: 1}

# Initialize variables to track current Unit ID and build the allocation
allocation = []
current_unit = 1

# Iterate through each Period and assign corresponding Unit IDs
for period, unit_count in period_units.items():
    # Generate pairs of (Unit ID, Period) for this period's unit count
    for i in range(unit_count):
        allocation.append((current_unit + i, period))
    # Move to the next starting Unit ID
    current_unit += unit_count

# Print the formatted result
print("UnitID | Period")
print("-------|-------")
for unit_id, period in allocation:
    print(f"{unit_id:6} | {period:6}")

Output: #

UnitID | Period
-------|-------
     1 |      1
     2 |      1
     3 |      1
     4 |      1
     5 |      1
     6 |      2
     7 |      4
     8 |      4
     9 |      4
    10 |      5
    11 |      5
    12 |      6
    13 |      6
    14 |      6
    15 |      6
    16 |     10

How it works:

• We start with the first Unit ID (1) and loop through each valid Period
• For each Period, we generate the exact number of Unit ID-Period pairs needed
• We update the starting Unit ID after each Period to ensure consecutive assignment

This code is easily scalable—you can load the period_units mapping from a CSV/JSON file, or output the allocation to a spreadsheet using libraries like pandas.

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