将 Excel 中的表格并排堆叠并添加标题列

[#1]

我已经努力了两天，想把数据以正确的格式从 Excel 拉入 JMP。

我有数百张 Excel 表，但数据杂乱无章。Excel 表中有很多相邻的表格（数百张）。

我想将数据堆叠到不同的表中，同时还要创建新的列，其中包含相关的标题数据。我只能在一个表上实现这一点，但无法将其复制到所有表或不同 excel 文档的工作表中。工作表也往往有不同的选项卡名称，但如果我能让它在一个 excel 文档上运行，那就太好了。

[#2]

您能提供一个示例 excel 文件吗？如果您有几种不同类型的文件，提供更多示例将使提供答案变得更容易。

[#3]

你好，Jarmo，

当然，我附加了一些虚拟数据。最多可以有 200 个并排的表格，并且我在同一个 excel 文件中添加了不同的工作表只是为了显示选项卡名称的变化，但实际上这些工作表通常位于文件夹中单独的 excel 工作表中。我认为黄色突出显示是需要新标题的地方，而橙色突出显示只是因为在实际数据集中，在它下面的数据开始之前有几百个空行。

理想情况下，我希望将所有参数（参数 1-19，在 Excel 中向下滚动时会有更多参数）放在参数标题下，将值放在值标题下，并添加列以显示这些数据属于标题 1/2/3（和新的 4）值，方法是像在原始帖子中添加列一样。

最终，我还将寻求使用来自 header1/2/3（和 4）的一些标识符以类似方式保存两组数据（VCL 和 VL 标题列）。

如果您有任何想法请告诉我，我将不胜感激！

谢谢

刘易斯

[#4]

这并不容易，因为说实话，这些数据格式太糟糕了。找到源头并修复数据是最好的选择，但这很少是一个选择。

我会先将尽可能原始的数据导入 JMP，如下所示

从此开始，我将开始编写一个脚本，因为如果不使用 JSL，这些数据有太多问题需要处理（没有 JSL 你可能可以走得很远，但使用 JSL 会容易得多）。修复第一行标题很可能是你应该做的第一件事，因为这样你就可以更轻松地开始分离“表格”及其数据

[#5 · Accepted solution]

我修改了您提供的 excel 文件，使其更简单一些，但如果更复杂，想法仍然是一样的。我只处理一个工作表，但循环遍历工作表很容易。

起始数据

导入到 JMP（由于某种原因，JMP 想要删除第一个空列，尽管我没有告诉它这么做，但这样没问题）

我们的起始数据

此时，我会立即使用 JSL，但您可以使用 JMP 的交互式功能轻松进行一些清理。请注意，此解决方案不处理数据中的不同类型的问题（缺少标题、重复参数），因为我不知道应该如何处理这些问题

查看更多...

Names Default To Here(1);

dt = Open(
	"$DOWNLOADS/Book1.xlsx",
	Worksheets("Sheet1"),
	Use for all sheets(0),
	Concatenate Worksheets(0),
	Create Concatenation Column(0),
	Worksheet Settings(
		1,
		Has Column Headers(0),
		Number of Rows in Headers(1),
		Headers Start on Row(1),
		Data Starts on Row(1),
		Data Starts on Column(1),
		Data Ends on Row(0),
		Data Ends on Column(0),
		Replicated Spanned Rows(0),
		Replicated Spanned Headers(0),
		Suppress Hidden Rows(0),
		Suppress Hidden Columns(0),
		Suppress Empty Columns(0),
		Treat as Hierarchy(0),
		Multiple Series Stack(0),
		Import Cell Colors(0),
		Limit Column Detect(1),
		Column Separator String("-"),
		Column Numeric Format(Index(0), Column Name(Column 1), Datatype("Character"))
	)
);


// turn everything after this into functions/expressions which you can easily run on different sheets

// Take only useful data: header which belongs to parameters (first two rows)
// Parameter rows
header_rows = [1, 2];
param_rows = dt << Get Rows Where(Starts With(:Column 1, "Parameter"));
dt_headers = dt << subset(Rows(header_rows), Selected Columns(0), Output table("Data Header"));
dt_params = dt << subset(Rows(param_rows), Selected Columns(0), Output table("Data Parametere"));
Close(dt, no save);

// Drop empty columns
For Each({colname}, dt_params << Get Column Names("String"),
	If(Col Number(Column(dt_params, colname)) == 0,
		dt_params << Delete Column(colname)
	);
);

dt_result = dt_params << Stack(
	columns(dt_params << Get Column Names("String")),
	Stack By Row(0),
	Drop All Other Columns(1),
	Number of Series(2),
	Output Table("Result")
);
// Close(dt_params, no save);

// Cleanup some columns
dt_result << Delete Columns(1, 3);
Column(dt_result, 1) << Set Name("Name");
Column(dt_result, 2) << Set Name("Value");

// Add grouping for header joining
new_col = dt_result << New Column("Group", Numeric, Ordinal, Formula(
	Col Cumulative Sum(1, :Name)
));
dt_result << run formulas;
new_col << Delete Formula;


headers = dt_headers[1, 0];
header_count = N Items(Associative Array(headers));
dt_headerstack = Eval(EvalExpr(dt_headers << Stack(
	columns(dt_headers << Get Column Names("String")),
	Stack By Row(0),
	Number of Series(Expr(header_count)),
	Output Table("header stack")
)));
// Close(dt_headers, no save);
// drop unnecessary columns
dt_headerstack << Delete Columns(1::N Cols(dt_headerstack)::2);

// Move header columns and remove extra header rows
dt_headerstack << Move Up;
rows_to_delete = dt_headerstack << Get Rows Where(Starts With(Column(dt_headerstack, 1)[], "Header"));
dt_headerstack << Delete Rows(rows_to_delete);

// Add grouping for update
new_col = dt_headerstack << New Column("Group", Numeric, Ordinal, Formula(
	Row()
));
dt_headerstack << run formulas;
new_col << Delete Formula;

dt_result << Update(
	With(dt_headerstack),
	Match Columns(:Group = :Group)
);
Close(dt_headerstack, no save);
Close(dt_headers, no save); // close earlier
Close(dt_params, no save); // close earlier
Close(dt, no save); // close earlier



/*
sheetList = Get Excel Worksheets("$DOWNLOADS/Book1.xlsx"); // for sheet list to loop over
*/

附有 Excel 文件和 JMP 结果文件

[#6]

太棒了，谢谢！这对于示例文件来说非常有效，我将开始进行一些清理，看看这会给我带来多大的帮助，但这看起来是一个很好的方法，谢谢！