excel表格数据比对

Question

我有这个药品材料目录数据库，对应着各种药品，材料，诊疗项目是否自费，是否限价!

我现在有纸质的收费清单，我做到了找AI转换成excel表格，光用vlook不够，举个例子，A药物是自费的，那么就全扣除，B药物是限100元，如果医院单价收费超过100元则扣除超限价部分，并列出超限价金额和应扣除金额。

Answer 1

下午好🌅🌅🌅
本答案参考通义千问

根据你的描述，你有一个药品材料目录数据库（包含药品、材料、诊疗项目是否自费、是否限价等信息），并且你已经将纸质收费清单通过AI转换成Excel表格。现在的问题是：

• A药物是自费的，那么就全扣除；
• B药物是限价100元的，如果医院单价超过100元，则扣除超限价部分，并列出超限价金额和应扣除金额。

这种情况下，VLOOKUP虽然可以用来匹配药品名称和价格，但无法直接处理“是否自费”或“限价”的逻辑判断，因此需要结合IF函数、VLOOKUP、SUMIF、SUMIFS等组合使用。

---

✅ 解决方案步骤如下：

1. 整理数据结构

确保你的Excel表格中有以下列：

| 药品/材料名称 | 医院单价 | 是否自费 | 是否限价 | 限价金额 | 应扣除金额 | 超限价金额 | |---------------|----------|----------|----------|----------|------------|-------------| | A药物 | 200 | 是 | 否 | - | 200 | - | | B药物 | 150 | 否 | 是 | 100 | 50 | 50 |

重点：

• 是否自费 和 是否限价 列需要为文本类型（如“是”或“否”）。
• 限价金额 列填写具体数值。

---

2. 使用公式进行计算

(1) 判断是否自费

在“应扣除金额”列中输入以下公式：

=IF(C2="是", B2, 0)

解释：
如果“是否自费”为“是”，则全额扣除；否则不扣除。

(2) 判断是否限价并计算超限价金额

在“超限价金额”列中输入以下公式：

=IF(D2="是", IF(B2 > E2, B2 - E2, 0), 0)

解释：
如果“是否限价”为“是”，且医院单价高于限价金额，则计算超限部分；否则为0。

(3) 计算应扣除金额（综合自费+限价）

在“应扣除金额”列中使用更复杂的公式：

=IF(C2="是", B2, IF(D2="是", IF(B2 > E2, B2 - E2, 0), 0))

解释：
首先判断是否自费，如果是，直接扣除全部；否则判断是否限价，如果是，扣除超限部分；否则不扣除。

---

3. 自动汇总所有应扣除金额

如果你需要统计整个清单的总扣除金额，可以在一个单元格中使用：

=SUM(F2:F100)

说明：
假设“应扣除金额”在F列，从第2行到第100行。

---

4. 示例数据展示

| 药品/材料名称 | 医院单价 | 是否自费 | 是否限价 | 限价金额 | 应扣除金额 | 超限价金额 | |---------------|----------|----------|----------|----------|------------|-------------| | A药物 | 200 | 是 | 否 | - | 200 | - | | B药物 | 150 | 否 | 是 | 100 | 50 | 50 | | C药物 | 80 | 否 | 是 | 100 | 0 | 0 | | D药物 | 50 | 否 | 否 | - | 0 | - |

---

5. 注意事项

• 确保“药品/材料名称”列与数据库中的名称一致，否则VLOOKUP会找不到对应数据。
• 如果你有多个数据库表，可以使用VLOOKUP来查找对应的“是否自费”、“是否限价”和“限价金额”。

---

✅ 修改后的完整公式（适用于应扣除金额）

=IF(VLOOKUP(A2, 数据库!A:E, 3, FALSE)="是", B2, 
    IF(VLOOKUP(A2, 数据库!A:E, 4, FALSE)="是", 
        IF(B2 > VLOOKUP(A2, 数据库!A:E, 5, FALSE), B2 - VLOOKUP(A2, 数据库!A:E, 5, FALSE), 0), 
        0))

说明：

• A2 是当前行的药品/材料名称；
• 数据库!A:E 是你的药品材料目录数据库所在的工作表和列范围；
• 第3列是“是否自费”，第4列是“是否限价”，第5列是“限价金额”。

---

✅ 总结

| 步骤 | 内容 | |------|------| | 1 | 整理药品材料目录数据库，确保字段完整 | | 2 | 使用IF和VLOOKUP判断是否自费或限价 | | 3 | 计算超限价金额和应扣除金额 | | 4 | 汇总所有扣除金额 | | 5 | 使用公式实现自动化比对和计算 |

---

如果你能提供具体的Excel文件或数据样例，我可以进一步帮助你定制公式和格式！