下午好，
有没有办法将一行中的数据拆分并存储到单独的行中？我有一个包含日程安排信息的大型文件，我正在尝试开发一个列表，该列表包含每行课程、日期、学期和时间段的每个组合。例如，我有一个类似于此的文件：
Crs:Sn Title Tchr TchrName Room Days Terms Periods
7014:01 English I 678 JUNG 300 M,T,W,R,F 3,4 2,3
1034:02 English II 123 MOORE 352 M,T,W,R,F 3 4
7144:02 Algebra 238 VYSOTSKY 352 M,T,W,R,F 3,4 3,4
0180:06 Pub Speaking 23 ROSEN 228 M,T,W,R,F 3,4 5
7200:03 PE I 244 HARILAOU GYM 4 M,T,W,R,F 1,2,3 3
2101:01 Physics/Lab 441 JONES 348 M,T,W,R,F 1,2,3,4 2,3
Should extract to this in an excel file:
Crs:Sn Title Tchr# Tchr Room Days Terms Period
7014:01 English I 678 JUNG 300 M 3 2
7014:01 English I 678 JUNG 300 T 3 2
7014:01 English I 678 JUNG 300 W 3 2
7014:01 English I 678 JUNG 300 R 3 2
7014:01 English I 678 JUNG 300 F 3 2
7014:01 English I 678 JUNG 300 M 4 2
7014:01 English I 678 JUNG 300 T 4 2
7014:01 English I 678 JUNG 300 W 4 2
7014:01 English I 678 JUNG 300 R 4 2
7014:01 English I 678 JUNG 300 F 4 2
7014:01 English I 678 JUNG 300 M 3 3
7014:01 English I 678 JUNG 300 T 3 3
7014:01 English I 678 JUNG 300 W 3 3
7014:01 English I 678 JUNG 300 R 3 3
7014:01 English I 678 JUNG 300 F 3 3
7014:01 English I 678 JUNG 300 M 4 3
7014:01 English I 678 JUNG 300 T 4 3
7014:01 English I 678 JUNG 300 W 4 3
7014:01 English I 678 JUNG 300 R 4 3
7014:01 English I 678 JUNG 300 F 4 3
1034:02 English II 123 MOORE 352 M 3 4
1034:02 English II 123 MOORE 352 T 3 4
1034:02 English II 123 MOORE 352 W 3 4
1034:02 English II 123 MOORE 352 R 3 4
1034:02 English II 123 MOORE 352 F 3 4
7144:02 Algebra 238 VYSOTSKY 352 M 3 3
7144:02 Algebra 238 VYSOTSKY 352 T 3 3
7144:02 Algebra 238 VYSOTSKY 352 W 3 3
7144:02 Algebra 238 VYSOTSKY 352 R 3 3
7144:02 Algebra 238 VYSOTSKY 352 F 3 3
7144:02 Algebra 238 VYSOTSKY 352 M 4 3
7144:02 Algebra 238 VYSOTSKY 352 T 4 3
7144:02 Algebra 238 VYSOTSKY 352 W 4 3
7144:02 Algebra 238 VYSOTSKY 352 R 4 3
7144:02 Algebra 238 VYSOTSKY 352 F 4 3
7144:02 Algebra 238 VYSOTSKY 352 M 3 4
7144:02 Algebra 238 VYSOTSKY 352 T 3 4
7144:02 Algebra 238 VYSOTSKY 352 W 3 4
7144:02 Algebra 238 VYSOTSKY 352 R 3 4
7144:02 Algebra 238 VYSOTSKY 352 F 3 4
7144:02 Algebra 238 VYSOTSKY 352 M 4 4
7144:02 Algebra 238 VYSOTSKY 352 T 4 4
7144:02 Algebra 238 VYSOTSKY 352 W 4 4
7144:02 Algebra 238 VYSOTSKY 352 R 4 4
7144:02 Algebra 238 VYSOTSKY 352 F 4 4
0180:06 Pub Speaking 23 ROSEN 228 M 3 5
0180:06 Pub Speaking 23 ROSEN 228 T 3 5
0180:06 Pub Speaking 23 ROSEN 228 W 3 5
0180:06 Pub Speaking 23 ROSEN 228 R 3 5
0180:06 Pub Speaking 23 ROSEN 228 F 3 5
0180:06 Pub Speaking 23 ROSEN 228 M 4 5
0180:06 Pub Speaking 23 ROSEN 228 T 4 5
0180:06 Pub Speaking 23 ROSEN 228 W 4 5
0180:06 Pub Speaking 23 ROSEN 228 R 4 5
0180:06 Pub Speaking 23 ROSEN 228 F 4 5
7200:03 PE I 244 HARILAOU GYM 4 M 1 3
7200:03 PE I 244 HARILAOU GYM 4 M 2 3
7200:03 PE I 244 HARILAOU GYM 4 M 3 3
7200:03 PE I 244 HARILAOU GYM 4 T 1 3
7200:03 PE I 244 HARILAOU GYM 4 T 2 3
7200:03 PE I 244 HARILAOU GYM 4 T 3 3
7200:03 PE I 244 HARILAOU GYM 4 W 1 3
7200:03 PE I 244 HARILAOU GYM 4 W 2 3
7200:03 PE I 244 HARILAOU GYM 4 W 3 3
7200:03 PE I 244 HARILAOU GYM 4 R 1 3
7200:03 PE I 244 HARILAOU GYM 4 R 2 3
7200:03 PE I 244 HARILAOU GYM 4 R 3 3
7200:03 PE I 244 HARILAOU GYM 4 F 1 3
7200:03 PE I 244 HARILAOU GYM 4 F 2 3
7200:03 PE I 244 HARILAOU GYM 4 F 3 3
2101:01 Physics/Lab 441 JONES 348 M 1 2
2101:01 Physics/Lab 441 JONES 348 M 2 2
2101:01 Physics/Lab 441 JONES 348 M 3 2
2101:01 Physics/Lab 441 JONES 348 M 4 2
2101:01 Physics/Lab 441 JONES 348 T 1 2
2101:01 Physics/Lab 441 JONES 348 T 2 2
2101:01 Physics/Lab 441 JONES 348 T 3 2
2101:01 Physics/Lab 441 JONES 348 T 4 2
2101:01 Physics/Lab 441 JONES 348 W 1 2
2101:01 Physics/Lab 441 JONES 348 W 2 2
2101:01 Physics/Lab 441 JONES 348 W 3 2
2101:01 Physics/Lab 441 JONES 348 W 4 2
2101:01 Physics/Lab 441 JONES 348 R 1 2
2101:01 Physics/Lab 441 JONES 348 R 2 2
2101:01 Physics/Lab 441 JONES 348 R 3 2
2101:01 Physics/Lab 441 JONES 348 R 4 2
2101:01 Physics/Lab 441 JONES 348 F 1 2
2101:01 Physics/Lab 441 JONES 348 F 2 2
2101:01 Physics/Lab 441 JONES 348 F 3 2
2101:01 Physics/Lab 441 JONES 348 F 4 2
2101:01 Physics/Lab 441 JONES 348 M 1 3
2101:01 Physics/Lab 441 JONES 348 M 2 3
2101:01 Physics/Lab 441 JONES 348 M 3 3
2101:01 Physics/Lab 441 JONES 348 M 4 3
2101:01 Physics/Lab 441 JONES 348 T 1 3
2101:01 Physics/Lab 441 JONES 348 T 2 3
2101:01 Physics/Lab 441 JONES 348 T 3 3
2101:01 Physics/Lab 441 JONES 348 T 4 3
2101:01 Physics/Lab 441 JONES 348 W 1 3
2101:01 Physics/Lab 441 JONES 348 W 2 3
2101:01 Physics/Lab 441 JONES 348 W 3 3
2101:01 Physics/Lab 441 JONES 348 W 4 3
2101:01 Physics/Lab 441 JONES 348 R 1 3
2101:01 Physics/Lab 441 JONES 348 R 2 3
2101:01 Physics/Lab 441 JONES 348 R 3 3
2101:01 Physics/Lab 441 JONES 348 R 4 3
2101:01 Physics/Lab 441 JONES 348 F 1 3
2101:01 Physics/Lab 441 JONES 348 F 2 3
2101:01 Physics/Lab 441 JONES 348 F 3 3
2101:01 Physics/Lab 441 JONES 348 F 4 3
我试图避免逐行分离数据。我不太熟悉 Excel 的 VBA 功能，但想开始使用它。
任何帮助将不胜感激。
答案1
我将使用 Power Query 插件 - 它具有 Split 和 Unpivot 命令，您可以逐层执行这些命令来转换您的表格。
从您的示例中读起来有点困难，但我认为我看到 Days 列中有多个“单元格”，以逗号分隔？所以我将使用 Split 命令将其拆分为多个列，然后使用 Unpivot 命令将这些列转换为多行。
然后，我会对条款和期限重复上述内容（如果我正确理解了您的要求的话）。
您可以在此处获取 Power Query：
http://www.microsoft.com/en-au/download/details.aspx?id=39379