Excel单元格多日期拆分：按固定日期列添加对应X标记

Got it, let's tackle this Excel transformation problem. Here are two straightforward methods to get your desired output—one using formulas (perfect for quick, one-off tasks) and another with Power Query (ideal if you need to refresh the data regularly):

This is the fastest way for a static dataset:

1. First, set up your output table with the fixed headers: 姓名（Name）, 工作日（Work Days）, 1, 2, 3, 4, 5, 6, 7
2. For the first date column (e.g., cell C2 under header 1), paste this formula:

   =IF(ISNUMBER(SEARCH(C$1,$B2)),"X","")
   

   • Quick breakdown: SEARCH(C$1,$B2) checks if the header value (like "1") exists in the comma-separated list in column B. ISNUMBER converts the search result to a yes/no boolean, and IF outputs "X" if found, otherwise an empty string.
3. Drag the formula across all 7 date columns, then drag it down to apply to every row. Your table will populate with "X" marks exactly where needed.

Use this if your source data might change and you want to avoid redoing formulas:

1. Select your original data table, go to the Data tab > From Table/Range (make sure your data has headers).
2. In the Power Query Editor:
   • Go to Add Column > Custom Column, create a column that splits the workday values into a list:

     = Text.Split([工作日（Work Days）], ",")
     

     Rename this column to Work Day List.
   • Add custom columns for each date 1-7. For example, for date 1:

     = if List.Contains([Work Day List], "1") then "X" else ""
     

     Repeat this for dates 2 through 7, updating the string in the formula each time.
   • Optional: Delete the Work Day List column if you don't need it anymore.
3. Click Close & Load to bring the transformed table back to Excel. Right-click the table later and select Refresh if your source data changes.

Final Output Example #

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