如何在Excel中将每日重置的5分钟降雨累计值转为原始数据值

Hey there, let's break down how to solve these two Excel data conversion challenges—they're really typical for time-series datasets like rainfall measurements, so I’ll walk you through each solution clearly.

This is the straightforward case where your cumulative values keep increasing without daily resets. Let's assume your cumulative data lives in Column A (starting at cell A2, with A1 as the header), and you want to calculate the raw 5-minute increments in Column B.

Formula Options: #

• For the first data row (B2): Since there's no prior value, just use the cumulative value directly:
  =A2
• For all subsequent rows (B3 onwards): Subtract the previous row's cumulative value from the current one to get the incremental value:
  =A3-A2
• To use a single universal formula that works for every row (including the first):
  =IF(ROW()=2, A2, A2-A1)

How it works: #

The universal formula checks if you're on the second row (the first data entry) and returns the cumulative value. For every other row, it calculates the difference between the current and prior cumulative value to get the raw 5-minute reading.

This trickier scenario happens when the cumulative rainfall resets to 0 every 24 hours (e.g., at midnight). Directly subtracting the previous row will fail here, since the reset row's value will be smaller than the prior day's last value.

Let's assume your resetting cumulative data is in Column C (starting at C2), and your target raw values go into Column D.

Universal Formula (works for all rows): #

=IF(OR(ROW()=2, C2<C1), C2, C2-C1)

Breakdown of the formula: #

• ROW()=2: Handles the first data row, just like the previous case—returns the cumulative value as the first increment.
• C2<C1: Detects when the cumulative value has reset (since the current value is smaller than the prior row's value). In this case, we take the current cumulative value directly (it's the first increment of the new 24-hour period).
• The final C2-C1: For normal non-reset rows, calculates the 5-minute incremental rainfall as usual.

Bonus: More accurate reset detection (using timestamps) #

If you have a timestamp column (e.g., Column E with datetime values), you can make the reset detection more reliable by checking if the current row is the first entry of the day, instead of just relying on cumulative value size:
=IF(OR(E2=MINIFS(E:E, INT(E:E), INT(E2)), C2<C1), C2, C2-C1)
This checks if the current timestamp is the earliest one for its date, ensuring you correctly identify the start of a new 24-hour period even if cumulative values don't drop exactly to 0.

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