logo标签列表

Excel合并和组合相似单元格

excelmerge
3

我有一个简单的Excel电子表格，它是一个每日更新的柜台清单。该表格的格式如下：

A         B          C             D          E
date      name       computer      info       counter

例子：

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

我需要做的是将所有相似的单元格合并。例如，将每个月的所有行合并，并从第一个E列中减去最后一个E列，使其在新表中如下所示。
A             B          C         D              E
April 2012    John       186       Workstation    41
April 2012    Mary       181       Workstation    2
April 2012    Bill       183       Workstation    0

对于每个项目都是如此……记住这是每天更新的，有很多天、月和年。

我甚至不知道如何考虑做这件事，但我想我需要一个宏。如果有人能提供帮助，那就太好了！

你是否被 MS Excel 绑住了手脚？如果这个数据在数据库中，比如 MS Access 中，那么这个问题就会变得更加容易处理。 - Stewbob
不用担心，你可以使用公式来完成它。看看我的答案 :) - playercharlie
2个回答
2

假设这是您想要做的事情

我花了一些时间，感觉我终于明白你需要什么了。我本来想上传一个样本文件，但是文件分享网站在这里被封锁了 :)

现在我知道了，你希望输出在另一个标签页中，但是暂时先忍耐一下。

以下解决方案比较冗长（因为你的问题的性质），请严格按照此处所做的操作，不必担心某些单元格中可能出现的中间错误，这些都会在一段时间内得到解决。

我假设你的工作表看起来像图片中的黄色部分。（不用管第一列中的日期，它们都是2012年4月份的日期，这才是重点）

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

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

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

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

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

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

我在这里使用字符“|”和“@”，因为我假设它们不会出现在任何一个名称中！！请确保是这样的！！

现在将F2到H2单元格复制并粘贴到您拥有数据的任何地方。

现在，我还假设你有8000行数据。如果您的数据行数超过8000行，请用所需的数字替换以下公式中的所有“8000”。

在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)))

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

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

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

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

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

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

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

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

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

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

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

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

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

在单元格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))))

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

现在选择从J2到Q2的单元格，将它们复制并粘贴到所需的范围。 在示例屏幕截图中，这将覆盖所有可能性的行数，例如到第5行。

您最终获得了所需的表格。 您现在可以使用简单的“等于”关系在其他地方链接此表格。 如果您愿意，还可以从数据表中隐藏所有这些额外的列。

哇！！我真的希望这解决了您的问题！！ 我认为我应该因所有这些努力而受到赞扬！！ 希望有所帮助！ ：）

0

在您的工作表中添加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

（向下拖动以将公式扩展到所有行）

它应该看起来像这样：

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

结果是：
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

为了清晰起见，在另一个表格中获取相关数据的引用。假设上面的数据存储在 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
...

结果为：

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

最后，在Sheet2上：

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接