![如何将Excel数据从一行拆分为多行](https://linux22.com/image/1410610/%E5%A6%82%E4%BD%95%E5%B0%86Excel%E6%95%B0%E6%8D%AE%E4%BB%8E%E4%B8%80%E8%A1%8C%E6%8B%86%E5%88%86%E4%B8%BA%E5%A4%9A%E8%A1%8C.png)
下午好,
有没有办法将一行中的数据拆分并存储到单独的行中?我有一个包含日程安排信息的大型文件,我正在尝试开发一个列表,该列表包含每行课程、日期、学期和时间段的每个组合。例如,我有一个类似于此的文件:
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