同工作簿跨工作表统计各员工唯一工作日数技术问询

Alright, let's tackle this problem of counting unique workdays for employees when data is spread across multiple sheets in the same workbook. You already know the single-sheet formula (=SUM(IF(FREQUENCY(B:B,B:B)>0,1)), entered as an array formula with Ctrl+Shift+Enter), so let's expand that to cross-sheet scenarios.

1. For a Single Specific Employee #

Let's say your data lives in sheets named Sheet1, Sheet2, Sheet3 (adjust these names to match your workbook), with employee names in column A and dates in column B. Here's how to count unique dates for an employee like "John Doe":

Option 1: Excel 365/2021 (Dynamic Array Functions, Recommended) #

This uses modern functions to keep things clean—no array key combo needed:

=ROWS(UNIQUE(TOCOL(FILTER({Sheet1!B:B,Sheet2!B:B,Sheet3!B:B},{Sheet1!A:A="John Doe",Sheet2!A:A="John Doe",Sheet3!A:A="John Doe"}),3)))

• Breakdown:
  • FILTER grabs only the dates tied to "John Doe" from each sheet
  • TOCOL stacks all those filtered dates into a single column
  • UNIQUE strips out duplicate dates
  • ROWS counts how many unique dates are left

Option 2: Older Excel Versions (No Dynamic Arrays) #

If you're stuck on an older version, use this array formula (must press Ctrl+Shift+Enter to activate it):

=SUMPRODUCT(1/COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!B:B"),INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!B:B")*(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!A:A")="John Doe")))

• Note: If your sheet names have spaces, wrap them in single quotes like 'Q3 Sales'
• Breakdown:
  • INDIRECT references the date and name columns across your sheets
  • The *(INDIRECT(...="John Doe")) part filters only dates linked to the employee
  • COUNTIF counts how many times each date appears, and 1/COUNTIF converts unique dates to 1 (duplicates become fractions that sum to 1)
  • SUMPRODUCT adds those up to get the total unique dates

2. Bulk Count for Multiple Employees #

If you need to calculate unique attendance for every employee at once, use Power Query + Pivot Tables—it's way more efficient for large datasets:

1. Go to the Data tab → Get Data → From File → From Workbook, select your current file
2. In the Power Query Editor, pick all the sheets you need, then click Combine → Append Queries to merge all data into one table
3. Filter out empty dates, then select the employee name and date columns, and click Remove Duplicates
4. Close and load the cleaned data to a new sheet, then insert a Pivot Table: drag employee names to the Rows area, dates to the Values area, and set the value summary to Count

This method is great if you need to refresh the data regularly—just hit "Refresh All" to update counts when new data is added.

Quick Tip for Lots of Sheets #

If you have a ton of sheets and don't want to type each name manually, list all sheet names in a range (like C1:C10), then use TEXTJOIN to reference them all:

=ROWS(UNIQUE(TOCOL(FILTER(INDIRECT("'"&TEXTJOIN("','",TRUE,C1:C10)&"'!B:B"),INDIRECT("'"&TEXTJOIN("','",TRUE,C1:C10)&"'!A:A")="John Doe"),3)))

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