新增单价变动列需求：按描述计算当期与上期单价差值

Alright, let's figure out how to add that price difference column you need. You want to calculate the gap between the unit price on 2018-02-01 (current period) and 2018-01-01 (previous period), grouped by each description. Here are straightforward solutions for the two most common tools people use for this kind of work:

Solution 1: Using Excel #

First, let's assume your data is structured like this:

• Column A: Description
• Column B: Date (formatted as dates like 2018-01-01, 2018-02-01)
• Column C: Unit Price

Using XLOOKUP (Best for Excel 365/2021+) #

This is the cleanest method if you have a newer Excel version. In the first cell of your new column (say, D2), paste this formula:

=XLOOKUP($A2&"2018-02-01", $A:$A&$B:$B, $C:$C, 0) - XLOOKUP($A2&"2018-01-01", $A:$A&$B:$B, $C:$C, 0)

Then drag the fill handle down to apply the formula to all rows. This will pull the matching unit prices for each description in both periods and subtract the previous from the current.

Using VLOOKUP (For Older Excel Versions) #

If you don't have access to XLOOKUP, you can use VLOOKUP with a helper column or concatenate directly in the formula:

1. With a helper column: Add Column E, and in E2 enter =A2&B2 (this combines description and date for unique matching). Then in D2:

=VLOOKUP(A2&"2018-02-01", $E:$C, 2, FALSE) - VLOOKUP(A2&"2018-01-01", $E:$C, 2, FALSE)

2. Without a helper column: Use CHOOSE to create a combined lookup range on the fly:

=VLOOKUP($A2&"2018-02-01", CHOOSE({1,2}, $A:$A&$B:$B, $C:$C), 2, FALSE) - VLOOKUP($A2&"2018-01-01", CHOOSE({1,2}, $A:$A&$B:$B, $C:$C), 2, FALSE)

Solution 2: Using SQL #

Suppose your data is stored in a table named sales_data with columns: description, date, unit_price.

Basic Inner Join (Only Include Descriptions Present in Both Periods) #

Use a self-join to match each description's current and previous period prices, then calculate the difference:

SELECT
    curr.description,
    curr.date AS current_period_date,
    curr.unit_price AS current_unit_price,
    prev.unit_price AS previous_unit_price,
    (curr.unit_price - prev.unit_price) AS price_change
FROM
    sales_data curr
INNER JOIN
    sales_data prev ON curr.description = prev.description
WHERE
    curr.date = '2018-02-01'
    AND prev.date = '2018-01-01';

Full Outer Join (Include All Descriptions, Even if Missing One Period) #

If you need to keep descriptions that only exist in one of the periods, use a FULL OUTER JOIN and handle missing values with COALESCE:

SELECT
    COALESCE(curr.description, prev.description) AS description,
    COALESCE(curr.unit_price, 0) AS current_unit_price,
    COALESCE(prev.unit_price, 0) AS previous_unit_price,
    (COALESCE(curr.unit_price, 0) - COALESCE(prev.unit_price, 0)) AS price_change
FROM
    sales_data curr
FULL OUTER JOIN
    sales_data prev ON curr.description = prev.description
WHERE
    (curr.date = '2018-02-01' OR curr.date IS NULL)
    AND (prev.date = '2018-01-01' OR prev.date IS NULL);

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