通过使用公式或vlookup进行Excel串联 [英] Excel concatenation by using formula or vlookup

问题描述

我在Excel工作簿中有两张纸. 我需要匹配SITEID的公式，并将3个值连接到由/分隔的1个值. 我尝试将 Vlookup 与范围一起使用，但未获得实际结果.

I have two sheets in excel workbook. I need the formula which matches SITEID and concatenate 3 values to 1 separated by / . I have tried to use Vlookup with ranges but not get the actual results.

第一张纸包含(4列):

First sheet contains (4 columns):

SiteId  Cell    Sector  Bore
MDLH1238    12381   1   10
MDLH1238    12382   2   20
MDLH1238    12383   3   40
MDLH1239    12391   1   60
MDLH1240    12401   1   50
MDLH1240    12402   2   20

第二张纸包含(2列):

Second sheet contains (2 columns):

SiteId  Bore
MDLH1238
MDLH1239
MDLH1240    

预期结果应该是这样的:

The expected result should be like this :

SiteId  Bore
MDLH1238 10/20/30
MDLH1239 60 
MDLH1240 50/20

推荐答案

假定每个站点只有3个扇区，并使用工作表名称，并假定单元格以A1开头，则在Sheet2中输入这些ID，并与SiteID相邻:

Assuming there's only ever 3 sectors per site, and using your sheet names, and assuming your cells start in A1, enter these in Sheet2, adjacent to the SiteIDs:

单元格B2:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,1)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,2)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,3)

单元格B3:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,1)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,2)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,3)

单元格B4:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,1)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,2)&"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,3)

应该产生如下内容:

SiteId    Bore
MDLH1238  10/20/40
MDLH1239  60/0/0
MDLH1240  50/20/0

在扇区2/3不存在的情况下，还有额外的"/0"条目，但是公式变得更长且更难阅读.

That's got extra "/0" entries where the sector 2/3 don't exist, but the formulas get longer and harder to read, to do that.

添加用于处理"/0"的公式

Adds the formulas for dealing with "/0"

单元格B2:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,1)&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,2)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,2),"")&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,3)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A2,Sheet1!$C$2:$C$7,3),"")

单元格B3:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,1)&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,2)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,2),"")&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,3)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A3,Sheet1!$C$2:$C$7,3),"")

单元格B4:

=SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,1)&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,2)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,2),"")&IF(SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,3)>0,"/"&SUMIFS(Sheet1!$D$2:$D$7,Sheet1!$A$2:$A$7,A4,Sheet1!$C$2:$C$7,3),"")

输出:

SiteId    Bore
MDLH1238  10/20/40
MDLH1239  60
MDLH1240  50/20

这篇关于通过使用公式或vlookup进行Excel串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！