Excel合并和组合像单元格

I have a simple excel spreadsheet that is a daily updated list of counters. The format of the sheet is as follows:

我有一个简单的Excel电子表格,它是每日更新的计数器列表。表格的格式如下:

A         B          C             D          E
date      name       computer      info       counter

Example:

A             B          C         D              E
04/04/2012    John       186       Workstation    4813
04/04/2012    Mary       181       Workstation    2273
04/05/2012    John       186       Workstation    4822
04/05/2012    Mary       181       Workstation    2274
04/06/2012    John       186       Workstation    4854
04/06/2012    Mary       181       Workstation    2275
04/06/2012    Bill       183       Workstation    3698

and son on....

和儿子......

What I need to be able to do is combine all like cells. So for instance combine all of the rows for each month and subtract the last E column from the first one so it it would look like the following in a new sheet.

我需要做的就是组合所有类似的细胞。因此,例如,将每个月的所有行组合起来,并从第一个列中减去最后一个E列,这样它在新工作表中看起来就像下面一样。

A             B          C         D              E
April 2012    John       186       Workstation    41
April 2012    Mary       181       Workstation    2
April 2012    Bill       183       Workstation    0

and so on for each one...keeping in mind this is updated daily and have many days, months and years.

等等每一个...请记住,这是每天更新,有很多天,几个月和几年。

I have no idea how to even think about doing this but I assume I would need a macro. If anyone has any assistance that would be great!

我不知道如何考虑这样做,但我想我需要一个宏。如果有人有任何帮助,那将是伟大的!

2 个解决方案

#1

I have spent some time on this, and it feels like I have finally got what you need. I would have uploaded a sample file, but file sharing sites are blocked here :).

我花了一些时间在这上面,感觉就像我终于得到了你需要的东西。我会上传一个示例文件,但文件共享站点在这里被阻止:)。

Now I know, you want your output in a different tab, but bear with me on this for now.

现在我知道了,你想要在不同的标签中输出你的输出,但现在请耐心等待。

The following solution is lengthy (expected because of the nature of your question). Follow what is done here to the T, and don't care about intermediate errors that might popup in some cells, it will all be sorted out in some time.

以下解决方案很长(由于您的问题的性质,预期)。按照这里对T做的,不关心可能在某些单元格中弹出的中间错误,它将在一段时间内整理出来。

I am assuming your sheet looks like yellow part of the image. (Never mind the dates in the first column, they are all April 2012 dates, that's what matters.)

我假设您的工作表看起来像图像的黄色部分。 (不要介意第一列中的日期,它们都是2012年4月的日期,这才是最重要的。)

In cell F2, paste the following formula

在单元格F2中,粘贴以下公式

=IF(A2="","",MONTH(A2))

In cell G2, paste the following formula,

在单元格G2中,粘贴以下公式,

=IF(A2="","",YEAR(A2))

In cell H2, paste the following formula,

在单元格H2中,粘贴以下公式,

=IF(B2="","",B2&"|"&F2&"@"&G2)

I am using the characters "|" and "@" here, because I am assuming they do not occur in any if the names!! Please make sure if this is so!!

我正在使用字符“|”和“@”在这里,因为我假设他们不会出现在任何名称!!请确保是这样的!!

Now copy cells F2 to H2, and paste them all the way down till wherever you have data.

现在将单元格F2复制到H2,然后将它们一直粘贴到有数据的位置。

Now, I am also assuming that you have 8000 rows of data. If you have more than 8000 rows, replace all '8000's in the following formulae with the number you desire.

现在,我还假设你有8000行数据。如果您有超过8000行,请将以下公式中的所有'8000'替换为您想要的数字。

In cell Q2, paste the following formula,

在单元格Q2中,粘贴以下公式,

=IF(ISERROR(INDEX($H$2:$H$8000,MATCH(0,COUNTIF($Q$1:Q1,$H$2:$H$8000),0))),"",INDEX($H$2:$H$8000,MATCH(0,COUNTIF($Q$1:Q1,$H$2:$H$8000),0)))

Please note that this is an array formula, and it needs to be entered with Ctrl+Shift+Enter. So in Q2, you paste this formula in the formula bar, and instead of pressing Enter, you press Ctrl + Shift + Enter. I have borrowed this formula from here.You may check out the site to see how this formula works.

请注意,这是一个数组公式,需要使用Ctrl + Shift + Enter输入。因此,在Q2中,您将此公式粘贴到公式栏中,而不是按Enter键,按Ctrl + Shift + Enter。我从这里借用了这个公式。你可以查看网站,看看这个公式是如何工作的。

Now, in the cell K2, paste the following formula

现在,在单元格K2中,粘贴以下公式

=IF(OR(Q2=0,Q2=""),"",LEFT(Q2,FIND("|",Q2)-1))

In the cell P2, paste the following formula

在单元格P2中,粘贴以下公式

=IF(OR(Q2=0,Q2=""),"",VALUE(RIGHT(Q2,LEN(Q2)-(FIND("@",Q2)))))

In the cell O2, paste the following formula

在单元格O2中,粘贴以下公式

=IF(OR(Q2=0,Q2=""),"",VALUE(MID(Q2,FIND("|",Q2)+1,LEN(Q2)-LEN(K2)-LEN(P2)-2)))

In the cell J2, paste the following formula

在单元格J2中,粘贴以下公式

=IF(OR(Q2=0,Q2=""),"",TEXT(DATE(P2,O2,1),"mmm yyyy"))

In the cell L2, paste the following formula

在单元格L2中,粘贴以下公式

=IF(OR(Q2=0,Q2=""),"",INDEX($C$2:$C$8000,MATCH(K2,$B$2:$B$8000,0)))

In the cell M2, paste the following formula

在单元格M2中,粘贴以下公式

=IF(OR(Q2=0,Q2=""),"",INDEX($D$2:$D$8000,MATCH(K2,$B$2:$B$8000,0)))

In the cell N2, paste the following formula,

在单元格N2中,粘贴以下公式,

=IF(OR(Q2=0,Q2=""),"",MAX(IF((($B$2:$B$8000=K2)*($F$2:$F$8000=O2)*($G$2:$G$8000=P2)),($E$2:$E$8000)))-MIN(IF((($B$2:$B$8000=K2)*($F$2:$F$8000=O2)*($G$2:$G$8000=P2)),($E$2:$E$8000))))

Again, please note that this is an array formula, and it needs to be entered with Ctrl+Shift+Enter. Make sure you have done all the steps above

请注意,这是一个数组公式,需要使用Ctrl + Shift + Enter输入。确保您已完成上述所有步骤

Now select cells from J2 to Q2, copy them and paste them to as far as required. In the example screenshot, this would be upto row number 5 (to cover all possibilities).

现在选择从J2到Q2的单元格,复制它们并将它们粘贴到所需的位置。在示例屏幕截图中,这将是第5行(以涵盖所有可能性)。

You finally have the table you require. You may now link this table in other places using a simple 'equals to' relation. Also, you can hide all these extra columns from your data sheet if you so wish.

你终于得到了你需要的桌子。您现在可以使用简单的“等于”关系将此表链接到其他位置。此外,如果您愿意,可以从数据表中隐藏所有这些额外的列。

Whew!! I really hope that solved your problem!! I think I deserve a pat on the back for all this effort!! Hope it helps! :)

呼!我真的希望能解决你的问题!!我认为我应该为所有这些努力拍拍!希望能帮助到你! :)

#2

Add 4 new columns (F to I) to your sheet and enter the following formulas: (assuming 100 rows of data: adjust as required!)

在工作表中添加4个新列(F到I)并输入以下公式:(假设有100行数据:根据需要调整!)

F --> =DATE(YEAR(A1), MONTH(A1), 1)

G --> =MIN(IF(($B$1:$B$100=$B1)*($C$1:$C$100=$C1)*($D$1:$D$100=$D1)*($F$1:$F$100=$F1),$E$1:$E$100)) [press CTRL+SHIFT+ENTER (this is an array formula)]

H --> =MAX(IF(($B$1:$B$100=$B1)*($C$1:$C$100=$C1)*($D$1:$D$100=$D1)*($F$1:$F$100=$F1),$E$1:$E$100)) [press CTRL+SHIFT+ENTER (this is an array formula)]

I --> =H1-G1

(drag down to expand the formulas to all rows)

(向下拖动以将公式展开到所有行)

It should look like this:

它应该如下所示:

A             B          C         D              E       F                              G                              H                              I
04/04/2012    John       186       Workstation    4813    =DATE(YEAR(A1), MONTH(A1), 1)  {=MIN(IF(($B$1:$B$100=$B1)...  {=MAX(IF(($B$1:$B$100=$B1)...  =H1-G1
04/04/2012    Mary       181       Workstation    2273    =DATE(YEAR(A2), MONTH(A2), 1)  {=MIN(IF(($B$1:$B$100=$B2)...  {=MAX(IF(($B$1:$B$100=$B2)...  =H2-G2
04/05/2012    John       186       Workstation    4822    =DATE(YEAR(A3), MONTH(A3), 1)  {=MIN(IF(($B$1:$B$100=$B3)...  {=MAX(IF(($B$1:$B$100=$B3)...  =H3-G3
04/05/2012    Mary       181       Workstation    2274    =DATE(YEAR(A4), MONTH(A4), 1)  {=MIN(IF(($B$1:$B$100=$B4)...  {=MAX(IF(($B$1:$B$100=$B4)...  =H4-G4
04/06/2012    John       186       Workstation    4854    =DATE(YEAR(A5), MONTH(A5), 1)  {=MIN(IF(($B$1:$B$100=$B5)...  {=MAX(IF(($B$1:$B$100=$B5)...  =H5-G5
04/06/2012    Mary       181       Workstation    2275    =DATE(YEAR(A6), MONTH(A6), 1)  {=MIN(IF(($B$1:$B$100=$B6)...  {=MAX(IF(($B$1:$B$100=$B6)...  =H6-G6
04/06/2012    Bill       183       Workstation    3698    =DATE(YEAR(A7), MONTH(A7), 1)  {=MIN(IF(($B$1:$B$100=$B7)...  {=MAX(IF(($B$1:$B$100=$B7)...  =H7-G7

The result being:

结果是:

A             B          C         D              E       F              G        H        I
04/04/2012    John       186       Workstation    4813    04/01/2012     4813     4854     41
04/04/2012    Mary       181       Workstation    2273    04/01/2012     2273     2275     2
04/05/2012    John       186       Workstation    4822    04/01/2012     4813     4854     41
04/05/2012    Mary       181       Workstation    2274    04/01/2012     2273     2275     2
04/06/2012    John       186       Workstation    4854    04/01/2012     4813     4854     41
04/06/2012    Mary       181       Workstation    2275    04/01/2012     2273     2275     2
04/06/2012    Bill       183       Workstation    3698    04/01/2012     3698     3698     0

For clarity's sake, let's get references for the relevant data in another sheet. Assuming the data above is stored in Sheet1, enter the following on Sheet2:

为清楚起见,让我们在另一张表中获取相关数据的参考。假设上面的数据存储在Sheet1中,请在Sheet2上输入以下内容:

A           B            C           D           E
=Sheet1!F1  =Sheet1!B1   =Sheet1!C1  =Sheet1!D1  =Sheet1!I1
=Sheet1!F2  =Sheet1!B2   =Sheet1!C2  =Sheet1!D2  =Sheet1!I2
...

The result being:

结果是:

A             B          C         D              E
04/01/2012    John       186       Workstation    41
04/01/2012    Mary       181       Workstation    2
04/01/2012    John       186       Workstation    41
04/01/2012    Mary       181       Workstation    2
04/01/2012    John       186       Workstation    41
04/01/2012    Mary       181       Workstation    2
04/01/2012    Bill       183       Workstation    0

Finally, on Sheet2:

最后,在Sheet2上:

• change the format of column A to "custom" / mmmm yyyy, so that the date is displayed as you like (i.e. April 2012 instead of 04/01/2012)

  将A列的格式更改为“custom”/ mmmm yyyy,以便根据您的喜好显示日期(即2012年4月而不是04/01/2012)

• last but not least: use excel's advanced filter to get rid of the duplicates.

  最后但并非最不重要:使用excel的高级过滤器来摆脱重复。