Excel数据处理：按列排序及部分列转行整理患者就诊记录

Hey Ricardo, let's work through your Excel problem step by step. I get that pivot tables didn't cut it for organizing patient visit records, so here are practical, actionable solutions for both your sorting and column-to-row needs:

First, let's get your dataset sorted to group patient records together neatly:

• Select your entire dataset (including headers) — click the top-left corner cell (between row 1 and column A) to select everything quickly.
• Navigate to the Data tab in Excel's ribbon.
• Click the Sort button. In the pop-up dialog:
  • Pick your target column (e.g., "Patient ID") from the "Column" dropdown.
  • Set "Sort On" to "Cell Values".
  • Choose your preferred order (Ascending/Descending).
  • Make sure the "My data has headers" box is checked (so Excel doesn't treat your first row as data).
• Hit OK, and your data will be grouped perfectly by the column you selected.

Since pivot tables rely on aggregation (like counts or sums) which aren't helpful for raw visit details, we'll use two better methods depending on your dataset size and preference:

Method 1: Power Query (Best for Large Datasets) #

This is ideal for your hundreds of patients — it's automated, repeatable, and handles messy data well. Let's assume your raw data has one row per visit (e.g., Patient ID, Name, Visit Date, Department). We'll turn this into one row per patient with all their visits listed horizontally:

1. Select your dataset, go to the Data tab, and click From Table/Range (Excel will prompt you to convert your data to a table if it isn't already — just check "My table has headers" and confirm).
2. In the Power Query Editor:
   • Add visit numbers per patient:
     • Select the "Patient ID" column, then go to Transform > Group By.
     • Click "Add grouping" to include "Name" as another grouping column.
     • Name the new column VisitDetails, set the operation to All Rows, then click OK.
     • Go to Add Column > Custom Column, paste this formula, and click OK:

       Table.AddIndexColumn([VisitDetails], "VisitNumber", 1, 1)
       

     • Click the expand icon on your new custom column, select "Visit Date", "Department", and "VisitNumber", then uncheck "Use original column name as prefix" and hit OK.
   • Pivot visits into horizontal columns:
     • Select the "Patient ID" and "Name" columns (hold Ctrl to multi-select).
     • Go to Transform > Pivot Column.
     • Set "Pivot column" to VisitNumber and "Values column" to Visit Date. Under "Advanced options", pick "Don't aggregate" (since each visit number is unique per patient).
     • Click OK, then repeat this pivot step for the Department column to get visit departments alongside dates.
3. When you're happy with the structure, go to Home > Close & Load to export the cleaned data back to a new Excel sheet.

If your data is structured the opposite way (one row per patient with multiple visit columns like "Visit1Date", "Visit2Dept"), use Unpivot Other Columns instead: select your fixed columns (Patient ID, Name), go to Transform > Unpivot Columns > Unpivot Other Columns, then split and clean the unpivoted attributes to get one row per visit.

Method 2: Formula-Based Approach (If You Prefer Avoiding Power Query) #

This works if you want to stick to Excel formulas, though it's less automated for large updates:

Assume your sorted raw data is in Sheet1 (columns A: Patient ID, B: Name, C: Visit Date, D: Department):

1. Create a list of unique patient IDs in a new sheet (Sheet2):
   • In Sheet2!A2, enter =UNIQUE(Sheet1!A:A) (for Excel 365/2021; for older versions, use the Advanced Filter to extract unique values).
2. Pull in patient names with XLOOKUP:
   • In Sheet2!B2, enter =XLOOKUP(A2, Sheet1!A:A, Sheet1!B:B).
3. Fetch visit dates for each patient:
   • In Sheet2!C2, enter this array formula (press Ctrl+Shift+Enter for pre-365 Excel; 365 handles it automatically):

     =INDEX(Sheet1!C:C, SMALL(IF(Sheet1!$A$2:$A$1000=Sheet2!A2, ROW(Sheet1!$A$2:$A$1000)), COLUMN()-2))
     

   • Drag this formula across columns to get all visit dates, and down rows for all patients.
4. Repeat the formula for departments, replacing Sheet1!C:C with Sheet1!D:D.

Let me know if you need to adjust these steps to fit your exact data structure — happy to tweak things!

内容的提问来源于stack exchange，提问作者Ricardo C.B.