跨多工作表分散数据的指定引用AVERAGE计算方法咨询

Hey Ahmad, let's break down how to calculate the average of those target cells scattered across different sheets (each on row 2, but varying columns). There are a couple of straightforward methods depending on how many sheets you're working with:

Method 1: Direct Cell Reference (Best for a Small Number of Sheets) #

If you only have a handful of sheets to include, you can directly reference each target cell inside the AVERAGE function. This is simple and requires no extra setup.

For your example (Sheet1!D2, Sheet5!J2, Sheet10!M2), the formula would be:

=AVERAGE(Sheet1!D2, Sheet5!J2, Sheet10!M2)

Just add more cell references separated by commas if you have additional sheets. The AVERAGE function automatically ignores empty cells, so you don’t have to worry about those skewing your result.

Method 2: INDIRECT Function for Bulk Sheets (Best for Many Sheets) #

When dealing with a lot of sheets, manually typing each reference gets tedious. Instead, use the INDIRECT function to convert text-based sheet/cell references into actual cell values, then calculate the average.

Step 1: Create a Reference List #

First, set up a summary table in a blank sheet (name it something like "Summary" for clarity):

• Column A: List the names of all your target worksheets (e.g., Sheet1, Sheet5, Sheet10)
• Column B: List the corresponding cell addresses (e.g., D2, J2, M2)

Step 2: Use the Average Formula #

Choose the formula that matches your Excel version:

For Excel 365/2021 (Dynamic Arrays):

=AVERAGE(INDIRECT("'"&A1:A3&"'!"&B1:B3))

This will automatically spill the average result. The '"&A1:A3&"'!" part handles sheet names with spaces or special characters (adding single quotes around the sheet name prevents errors).

For Older Excel Versions:

Use an array formula with SUMPRODUCT and COUNTIF (press Ctrl+Shift+Enter after typing, instead of just Enter):

=SUMPRODUCT(IFERROR(INDIRECT("'"&A1:A3&"'!"&B1:B3),0))/COUNTIF(IFERROR(INDIRECT("'"&A1:A3&"'!"&B1:B3),""),"<>")

The IFERROR ensures missing sheets or invalid references don’t break the formula—they’re excluded from both the sum and count.

Key Notes to Avoid Errors #

• Sheet Names with Spaces/Special Characters: Always wrap sheet names in single quotes inside INDIRECT (like 'Sales Q1'!D2), which the formula above handles automatically.
• Empty or Invalid Cells: AVERAGE ignores empty cells by default. If using SUMPRODUCT, the IFERROR and COUNTIF combo ensures only valid, non-empty cells are included.
• Hidden Sheets: Both methods work with hidden sheets, so you don’t need to unhide data sheets to calculate the average.

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