基于今日日期，在Excel中统计近1个月工单更新的技术人员次数

Alright, let's tackle this problem step by step. You've got ticket update logs jammed into Excel cells, and you need to flag which technicians updated each ticket in the last month—even with messy free text, inconsistent name order, and typos. Here are two reliable methods depending on your data size and Excel version:

Method 1: Excel 365/2021 Formula (Small to Medium Datasets) #

This uses modern Excel functions to split, extract, and match data directly in your worksheet. Assume:

• Your ticket update text is in column B (starting at B2)
• Technician names are in row 1, starting at C1 (e.g., Jane Doe, John Smith)
• You want to flag 1 if the technician updated the ticket in the last 30 days, 0 otherwise

Formula for Cell C2 (Jane Doe's flag for ticket IM1234567): #

=IF(SUMPRODUCT(--(
    DATEVALUE(TEXTBEFORE(TEXTSPLIT(B2, "(?=\d{1,2}/\d{1,2}/\d{2})", , TRUE), " (")) >= TODAY()-30,
    ISNUMBER(SEARCH(UPPER(INDEX(TEXTSPLIT(C$1, " "),1)), UPPER(TEXTAFTER(TEXTBEFORE(TEXTSPLIT(B2, "(?=\d{1,2}/\d{1,2}/\d{2})", , TRUE), "):"), "(")))),
    ISNUMBER(SEARCH(UPPER(INDEX(TEXTSPLIT(C$1, " "),2)), UPPER(TEXTAFTER(TEXTBEFORE(TEXTSPLIT(B2, "(?=\d{1,2}/\d{1,2}/\d{2})", , TRUE), "):"), "("))))
))>0, 1, 0)

Breakdown of the formula: #

1. Split updates into individual entries: TEXTSPLIT(B2, "(?=\d{1,2}/\d{1,2}/\d{2})", , TRUE) uses a regex to split the cell every time a new date (like 4/20/16) starts—this avoids splitting on periods in the update text.
2. Extract and validate dates: DATEVALUE(TEXTBEFORE(...)) pulls the timestamp from each entry, converts it to a date, and checks if it's within the last 30 days (TODAY()-30).
3. Match technician names (case-insensitive, order-agnostic):
   • TEXTSPLIT(C$1, " ") splits the column header (e.g., Jane Doe) into first/last name
   • UPPER(...) normalizes text to uppercase to handle inconsistent capitalization
   • ISNUMBER(SEARCH(...)) checks if both first and last name appear in the ticket's update author string (even if the order is reversed, like Doe Jane in the log)
4. Flag if any match exists: SUMPRODUCT counts valid matches; if ≥1, return 1, else 0.

Method 2: Power Query (Large Datasets or Automation) #

If you have hundreds/thousands of tickets, Power Query is faster and more flexible for cleaning messy text. Here's the workflow:

Step 1: Import your data into Power Query #

• Select your ticket table → Go to the Data tab → Click From Table/Range (check "My table has headers")

Step 2: Split update logs into individual entries #

• Select the column with your ticket update text (e.g., Updates) → Go to Transform tab → Split Column → By Delimiter
• Choose "Custom" and enter the regex (?=\d{1,2}/\d{1,2}/\d{2}) → Click OK. This splits each update into its own column.

Step 3: Turn columns into rows (unpivot) #

• Select all the new split columns → Transform tab → Unpivot Columns → Unpivot Other Columns. Now each update is a separate row linked to its ticket ID.

Step 4: Extract dates and technician names #

• Add a date column: Go to Add Column → Custom Column → Use this formula to pull the timestamp:

  = Text.Before([Value], " (")
  

  Name it TimeString, then set its data type to Date/Time (Power Query will auto-ignore timezones like US/Eastern).
• Add a technician name column: Add another custom column with this formula to pull the author name:

  = Text.Between([Value], "(", "):")
  

  Name it FullName.

Step 5: Filter for the last month #

• Add a custom column to calculate the cutoff date:

  = Date.AddDays(DateTime.LocalNow(), -30)
  

  Name it OneMonthAgo.
• Filter the TimeString column to only show dates ≥ OneMonthAgo, then delete the OneMonthAgo column.

Step 6: Match technicians and pivot the table #

• If your technician list is in another Excel table, import it into Power Query too (Data → From Table/Range).
• For each row in your ticket updates, check if the FullName contains both parts of a technician's name (case-insensitive):
  Add a custom column with this formula (replace Techs[TechName] with your technician table's name/column):

  = List.First(List.Select(Techs[TechName], (tech) => 
      let
          techParts = Text.Split(tech, " "),
          firstName = Text.Upper(techParts{0}),
          lastName = Text.Upper(techParts{1}),
          fullNameUpper = Text.Upper([FullName])
      in
          Text.Contains(fullNameUpper, firstName) and Text.Contains(fullNameUpper, lastName)
  ), "No Match")
  

• Now, pivot the table: Go to Transform → Pivot Column
  • Value column: Use a custom column with 1 (create this first if needed)
  • Columns to pivot: The technician name column you just created
  • Aggregate value function: Max (this will return 1 if there's a match, null otherwise)
• Replace null values with 0 (Transform → Replace Values).

Step 7: Load back to Excel #

• Click Close & Load to import the cleaned, pivoted table back into your worksheet.

Bonus Tips for Handling Messy Data #

• Typos: For minor spelling errors, use Text.LevenshteinDistance in Power Query to calculate string similarity (e.g., flag matches where the distance is ≤2).
• Unusual date formats: If DATEVALUE fails, use Text.ToDate(Text.Replace([TimeString], " US/Eastern", "")) to strip timezones first.
• Long update texts: Power Query handles large text fields better than formulas, so it's the go-to for heavy datasets.

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