☰

Excel: 두 개의 열 집합을 하나로 집계

tag-icon • tag-icon microsoft-excel
Excel: 두 개의 열 집합을 하나로 집계

하나의 Excel 시트에는 매우 유사한 데이터를 나타내는 각각 3개의 열로 구성된 두 개의 서로 다른 데이터 세트가 있습니다.

이것을 세 개의 열로 구성된 하나의 세트로 결합하고 싶습니다.

각 데이터 세트에는 그룹화하려는 키로 가장 왼쪽 열이 있습니다.

키는 각 데이터 세트에 한 번만 나타나거나 전혀 나타나지 않습니다. 다른 열은 숫자이며 비어 있을 수 있습니다(0으로 해석됨). 두 계열의 길이는 다를 수 있습니다.

다음과 같습니다:

[Name_A],[Score_A],[Value_A],[Name_B],[Score_B],[Value_B]
Adam,14,20,Johnny,8,
Johnny,11,,Bernice,5,5
,,,Adam,2,8

다음으로 집계되어야 합니다.

[Name],[Score],[Value]
Adam,16,28
Johnny,19,
Bernice,5,5

최종 결과의 행 순서는 중요하지 않습니다.

답변1

텍스트를 열에 사용하고 쉼표를 구분 기호로 사용하여 데이터를 구문 분석합니다. 그런 다음 고유한 이름 목록을 컴파일하고(Excel의 데이터 탭에서 중복 항목 제거를 사용하여 수행할 수 있음) =sumif(Name_Column,Name1,Value1_Column).

이름과 값으로 모든 것을 요약해야 합니다.

예

답변2

데이터세트 병합의 핵심은 고유한 이름 목록을 추출하는 것입니다. 이것은많이이름이 모두 한 열에 있으면 더 쉽습니다. 표시된 대로 데이터를 정렬해야 하는 경우 VBA 솔루션이 있으므로 필요한 경우 아래에 설명을 입력하세요.

아래 첫 번째 표에서는 "B" 데이터를 "A" 데이터 아래로 이동하고 각 섹션에 몇 개의 행을 더 추가했습니다.

여기에 이미지 설명을 입력하세요

E2에서 채워진 이 수식은 이름을 나열하고 중복 항목을 제거합니다.

=IFERROR(INDEX((A$2:A$15),MATCH(0,COUNTIF($E$1:E1,A$2:A$15),0)),"")

배열 수식이므로 로 입력해야 합니다 CTRLShiftEnter.

F2와 G2로 채워진 이 두 수식은 목록에 있는 각 이름의 점수와 값을 합산합니다.

=IF(LEN(E2)<1,"",SUMIF(A$2:A$15,E2,B$2:B$15))

=IF(LEN(E2)<1,"",SUMIF(A$2:A$15,E2,C$2:C$15)) ____________________________________________________________________________________

작동 방식:내부는 COUNTIF()A 열의 각 이름이 E 열의 증가 목록에 나타나는 횟수의 배열을 생성합니다. MATCH()해당 배열에서 처음 0(아직 나열되지 않은 이름에 해당)의 위치를 ​​찾습니다. , 이는 열 A에서 이름을 선택하는 의 row_num으로 사용됩니다. INDEX()이미 나열된 이름은 다시 나타나지 않습니다.

IFERROR()#NUM!이 있는 행에 공백을 삽입합니다 . 수식에 고유한 값이 부족하면 오류가 발생합니다.

두 SUMIF()수식은 각 이름의 점수와 값 숫자를 합산합니다. E 열의 길이가 < 1인 행에 대해 공백을 삽입 합니다 IF()(즉, 셀에 이름이 없고 공백으로 표시됨).

관련 정보