【原】循环跨列引用的Excel公式翻译为Pandas处理代码

小小明代码实体 2022-10-31 发布于广东

展开全文

📢作者： 小小明-代码实体

📢博客主页：https://blog.csdn.net/as604049322

📢欢迎点赞 👍 收藏 ⭐留言 📝 欢迎讨论！

之前在群里看到了下面这个问题：

数据集下载地址：逻辑demo.xlsx

我们看下F-J列每一列的公式。

F2的公式为：

=IF($A2<>$A1,0,IF(F1>E2,F1,F1+G1))

G2的公式为：

=IF(C2<0,-G1,MIN(D2,H2))

H2的公式为：

=IF($A2<>$A1,E2,MAX(0,E2-F2))

I2的公式为：

=IF(D2<0,IF(E1<=F1+G1,-D1+G1,0),MAX(D2-H2,0))

J2的公式为：

=IF($A2=$A1,I2+J1,I2)

初步分析可以看到 F列需要G列的数据，G列需要H列的数据，H列需要F列的数据，I列需要FGH三列的数据，J列则是对I列的分组累加。

这FGH三列的公式咋还互相形成循环引用了？但是Excel能计算出结果就说明，并没有在单元格层面形成循环引用。仔细观察，F需要G的上一行，G需要当前H行，H需要当前F行。F列开头有初始值0，那么第一行可以先根据F为0计算出H，再根据H计算出G。后续行计算顺序依然为F->H->G，只不过F需要依赖G列的上一行。

未知数据的计算顺序：

这种形式的数据，使用常规方式已经无法计算。我们可以借助变量，逐行遍历。

首先读取数据：

import pandas as pd

df = pd.read_excel("逻辑demo.xlsx", usecols="A:E", nrows=14)
df

直接翻译的逐行处理代码：

result = []
a1, d1, e1, f1, g1, h1, i1, j1 = [0]*8
for a2, b2, c2, d2, e2 in df.itertuples(False):
    # F2=IF($A2<>$A1,0,IF(F1>E2,F1,F1+G1))
    # H2=IF($A2<>$A1,E2,MAX(0,E2-F2))
    if a2 != a1:
        f2 = 0
        h2 = e2
    else:
        f2 = f1 if f1 > e2 else f1+g1
        h2 = max(0, e2-f2)
    # G2=IF(C2<0,-G1,MIN(D2,H2))
    g2 = -g1 if c2 < 0 else min(d2, h2)
    # I2=IF(D2<0,IF(E1<=F1+G1,-D1+G1,0),MAX(D2-H2,0))
    if d2 < 0:
        i2 = -d1+g1 if e1 <= f1+g1 else 0
    else:
        i2 = max(0, d2-h2)
    # J2=IF($A2=$A1,I2+J1,I2)
    j2 = i2+j1 if a2 == a1 else i2
    result.append((f2, g2, h2, i2, j2))
    a1, d1, e1, f1, g1, h1, i1, j1 = a2, d2, e2, f2, g2, h2, i2, j2
result = pd.DataFrame(
    result, columns=["累计抵扣", "本次抵扣", "剩余额度", "本次可报", "累计可报"])
result = pd.concat([df, result], axis=1)
result

经过对比可以看到与Excel公式的计算结果一致。

公式中A2<>A1的这种判断明显是为了判断是否为同一员工，我们可以利用pandas的分组功能稍微简化一下代码：

result = []
for name in df.员工.unique():
    d1, e1, f1, g1, h1, i1, j1 = [0]*7
    for a2, b2, c2, d2, e2 in df.query(f"员工=='{name}'").itertuples(False):
        # F2=IF($A2<>$A1,0,IF(F1>E2,F1,F1+G1))
        f2 = f1 if f1 > e2 else f1+g1
        # H2=IF($A2<>$A1,E2,MAX(0,E2-F2))
        h2 = max(0, e2-f2)
        # G2=IF(C2<0,-G1,MIN(D2,H2))
        g2 = -g1 if c2 < 0 else min(d2, h2)
        # I2=IF(D2<0,IF(E1<=F1+G1,-D1+G1,0),MAX(D2-H2,0))
        if d2 < 0:
            i2 = -d1+g1 if e1 <= f1+g1 else 0
        else:
            i2 = max(0, d2-h2)
        result.append((f2, g2, h2, i2))
        d1, e1, f1, g1, h1, i1, j1 = d2, e2, f2, g2, h2, i2, j2
result = pd.DataFrame(result, columns=["累计抵扣", "本次抵扣", "剩余额度", "本次可报"])
result = pd.concat([df, result], axis=1)
result["累计可报"] = result.groupby("员工")["本次可报"].transform("cumsum")
result