Excel嵌套IF(AND)相邻行比对公式转SQL或Python实现咨询

核心判定逻辑拆解 #

原Excel公式是逐行向下拖拽计算的，严格按照表格物理行顺序判定，不会跨非紧邻行对比，具体规则如下：

• 前置判定：当前行的A列值与物理上紧邻的上一行A列值是否相等（跨主体的行不做数值对比）
• 前置条件满足时，对比当前行B列与紧邻上一行B列的数值大小：
  • 当前B列值 > 上一行B列值：返回Improve
  • 当前B列值 < 上一行B列值：返回Not Improved
  • 当前B列值 = 上一行B列值：返回No Change
• 不满足前置判定的行（含第一行，因为没有上一行可对比），统一返回-

注意：不要将该逻辑误写为“按A列分组后组内逐行对比”，两种逻辑在存在多分组交错排列的场景下，计算结果会完全不同。

等价SQL实现 #

因为公式需要逐行取紧邻上一行的列值做对比，可直接通过窗口函数LAG实现（适配MySQL8.0+、PostgreSQL、SQL Server、BigQuery等所有支持标准窗口函数的数据库引擎），示例写法如下：

-- 假设源表为source_data，字段对应关系：
-- group_key = 原ExcelA列，metric_value = 原ExcelB列，row_order = 原Excel从上到下的行顺序标识
SELECT
  group_key,
  metric_value,
  CASE
    -- 前置判定：当前行分组键与紧邻上一行分组键一致
    WHEN group_key = LAG(group_key) OVER (ORDER BY row_order) THEN
      CASE
        WHEN metric_value > LAG(metric_value) OVER (ORDER BY row_order) THEN 'Improve'
        WHEN metric_value < LAG(metric_value) OVER (ORDER BY row_order) THEN 'Not Improved'
        WHEN metric_value = LAG(metric_value) OVER (ORDER BY row_order) THEN 'No Change'
      END
    ELSE '-'
  END AS compare_result
FROM source_data
ORDER BY row_order;

注意：SQL写法需严格按照原Excel的行顺序对数据排序，不能随意打乱行序，否则取到的上一行值与Excel计算结果会出现偏差。

等价Python（Pandas）实现 #

Python侧可通过Pandas的行偏移函数shift(1)实现与Excel完全一致的逐行对比逻辑，两种常见写法分别适配小数据量、大数据量场景：

逐行判定写法（逻辑直观，适配小数据集） #

import pandas as pd

# 加载源数据，df的行顺序需与原Excel从上到下的顺序完全一致
df = df.reset_index(drop=True)

# 提前提取上一行的A、B列值
df['prev_A'] = df['A'].shift(1)
df['prev_B'] = df['B'].shift(1)

# 按公式规则逐行生成结果
def calc_result(row):
    # 第一行无前置行，直接返回占位符
    if pd.isna(row['prev_A']):
        return '-'
    # 前置条件判定
    if row['A'] == row['prev_A']:
        if row['B'] > row['prev_B']:
            return 'Improve'
        if row['B'] < row['prev_B']:
            return 'Not Improved'
        return 'No Change'
    return '-'

df['compare_result'] = df.apply(calc_result, axis=1)

# 清理中间辅助列
df = df.drop(columns=['prev_A', 'prev_B'])

向量化优化写法（性能更高，适配百万级以上大数据集） #

import numpy as np

# 行偏移后构造判定条件
same_group = df['A'] == df['A'].shift(1)
current_b = df['B']
prev_b = df['B'].shift(1)

df['compare_result'] = np.select(
    [
        same_group & (current_b > prev_b),
        same_group & (current_b < prev_b),
        same_group & (current_b == prev_b),
    ],
    ['Improve', 'Not Improved', 'No Change'],
    default='-'
)

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