Excel公式开发需求：基于指定单元格天数自动复制金额至对应工作日日历单元格

Hey there! Let’s build a formula-only solution (no macros/VBA) to map your payment amounts to the correct calendar cell based on the payment term workdays. We’ll use built-in Excel functions and optional helper sheets to keep things clean and maintainable.

Step 1: Calculate the Due Workday First #

First, let’s compute the exact due date (收款日期后第N个工作日) for each payment record. Let’s assume your main payment data lives in a sheet named 收款记录 with these columns:

• Column A: 收款日期 (e.g., 1/5/2024)
• Column B: 美元金额 (e.g., 5000)
• Column C: 付款期限 (工作日天数, e.g., 5)

Add a helper column (Column D, named 到期工作日) with this formula:

=WORKDAY(A2, C2)

Pro Tip: If your company has custom holidays, add a third argument to exclude them. For example, if holidays are listed in a sheet named 节假日 Column A:

=WORKDAY(A2, C2, 节假日!A:A)

For your example, this formula will return 1/12/2024 for a 1/5/2024 date and 5-day term—exactly what you need!

Step 2: Pull Amounts into Your Calendar #

Now, in your 日历 sheet, each cell corresponds to a specific date (make sure these are actual date values, not plain text!). Let’s say cell G3 in the calendar is the cell for 1/12/2024. Use one of these formulas to pull the matching amount:

Option 1: Single Amount per Due Date #

If only one payment will fall on each due date, use XLOOKUP (for Excel 365/2021+):

=XLOOKUP(G3, 收款记录!D:D, 收款记录!B:B, "")

For older Excel versions, use INDEX + MATCH (more flexible than VLOOKUP):

=IFERROR(INDEX(收款记录!B:B, MATCH(G3, 收款记录!D:D, 0)), "")

Option 2: Sum Multiple Amounts per Due Date #

If multiple payments might share the same due date, use SUMIF to total them:

=SUMIF(收款记录!D:D, G3, 收款记录!B:B)

This will automatically add up all amounts where the due date matches the calendar cell’s date.

Optional: Use a Helper Sheet for Complex Setups #

If you have a large dataset or want to separate calculation logic from your main records, add a 到期计算 helper sheet:

1. Copy your 收款记录 data to this sheet
2. Add the WORKDAY formula here to compute due dates
3. Reference this helper sheet in your calendar formulas instead of the main records sheet. This keeps your main data clean and makes troubleshooting easier.

Quick Check for Common Issues #

• Date Format Mismatch: Ensure both your payment dates and calendar dates are formatted as dates (not text). Use =ISDATE(G3) to verify—if it returns TRUE, you’re good to go.
• Workday Calculation Errors: Double-check that your payment term is entered as a number (not text) in Column C.
• Holiday Exclusions: If using custom holidays, make sure the holiday list only includes valid date values (no text entries).

Let me know if you need to adjust this to fit your exact worksheet layout—just share the column/row references, and I can tweak the formulas to match!

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