从一个电子表格中提取数据并将其插入到模板中?

I'm looking for a way to take a spreadsheet full of data and move it into another spreadsheet that's set up as a faux rack diagram.

我正在寻找一种方法来获取一个满是数据的电子表格，并将它移动到另一个电子表格中，该电子表格被设置为一个模拟的机架图。

For example, I would like to take data formatted as:

例如，我想将数据格式化为:

+------+---+-------------+-------+---------+-------+
| Rack | U | Service Tag | Model | Project | Owner |
+------+---+-------------+-------+---------+-------+

And insert into a new spreadsheet where "Rack" is a preexisting column header, "U" is a value in the first column, and the other four cells are merged together into one cell. So:

然后插入到一个新的电子表格中，其中“Rack”是一个已有的列标头，“U”是第一列中的一个值，其他四个单元格合并到一个单元格中。所以:

+-----+----+---------+--------+---------+----------+
| 566 | 36 | 1234567 | Server | Project | John Doe |
+-----+----+---------+--------+---------+----------+

would be converted to:

将转换为:

+-----+-----+-----+-----+-----+----------+-----+-----+-----+
|  U  | 001 | 002 | ... | 555 |    566   | 567 | ... |  n  |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| 042 |     |     |     |     |          |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| 041 |     |     |     |     |          |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| ... |     |     |     |     |          |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| 037 |     |     |     |     |          |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| 036 |     |     |     |     | John Doe |     |     |     |
|     |     |     |     |     | Server   |     |     |     |
|     |     |     |     |     | Project  |     |     |     |
|     |     |     |     |     | 1234567  |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| 035 |     |     |     |     |          |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| ... |     |     |     |     |          |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| 002 |     |     |     |     |          |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+
| 001 |     |     |     |     |          |     |     |     |
+-----+-----+-----+-----+-----+----------+-----+-----+-----+

where the first row and column are already defined.

第一行和列已经定义。

Is there any way to automate this, or am I just stuck doing it manually?

有什么方法可以实现自动化吗?还是我只能手工操作?

I would have no problem doing it manually if there weren't over 900 entries that needed to be ported over.

如果没有超过900个条目需要移植，我可以手工操作。

1 个解决方案

#1

"simple" in simple vlookup() formula seems a little understated to me. Assuming your matrix is in a different sheet with U in A1, then in B2 copied across and down to suit:

在简单的vlookup()公式中，“简单”似乎有点低估了我的意思。假设你的矩阵在一个不同的表格中，U在A1中，然后在B2中来回复制以适应:

=IFERROR(VLOOKUP(B$1&"|"&$A2,Sheet1!$H:$I,2,0),"")

with wrapping should work, provided your data sheet (say Sheet1 with ColumnA for Rack) has in H1:

使用包装应该可以工作，提供您的数据表(例如，Sheet1带有ColumnA作为齿条)在H1中:

=A1&"|"&TEXT(B1,"000")

and in I1:

而在I1:

=F1&CHAR(10)&D1&CHAR(10)&E1&CHAR(10)&C1

with both copied down to suit.

都复制下来了。

#1