나는 학생들과 그들이 수강한 수업에 대한 대규모 데이터 세트를 가지고 있습니다. 각 학생은 약 80개의 수업 중 12~18개를 수강했습니다. Excel(2013)을 사용하여 특정 수업 쌍에 대해 두 수업을 모두 수강한 학생 수를 확인하고 싶습니다. 나는 80개의 수업이 행과 열로 구성된 테이블을 상상하고 각 교차점에 대해 해당 조합을 수강한 학생 수를 볼 수 있습니다.
데이터는 수업당 학생당 하나의 행이 포함된 Excel 파일로 도착합니다.
Student Class
Smith E101
Jones E101
Parker E101
Brown E102
Green E102
Smith E201
Jones E202
Parker E201
Brown E202
Green E203
...
예상되는 출력:
E101 E102 E201 E202 E203 ...
E101 0 2 1 0
E102 0 0 1 1
E201 2 0 0 0
E202 1 1 0 0
E203 0 1 0 0
...
(분명히 위의 대각선 절반만 필요합니다. 나머지 절반은 이를 반영하기 때문입니다.)
저는 피벗 테이블을 사용하여 학생을 행으로, 가능한 모든 수업을 열로 테이블에 데이터를 가져와 학생이 특정 수업을 수강한 위치를 1로 표시했습니다.
E101 E102 E201 E202 E203 ...
Smith 1 1
Jones 1 1
Parker 1 1
Brown 1 1
Green 1 1
...
그러나 가능한 한 수동 개입을 최소화하면서 원하는 결과를 얻기 위해 진행하는 방법에 대해 고민하고 있습니다.
누구든지 Excel에서 필요한 결과를 얻을 수 있는 방법을 제안할 수 있습니까? 상당히 광범위한 검색을 수행했지만 아무것도 찾지 못했습니다.
아니면 다른 소프트웨어를 찾아야 합니까?
답변1
피벗 테이블에서 작동하는 수식을 사용하면 Excel에서 매우 간단하게 수행할 수 있습니다.
이렇게 테이블 2개가 세팅되어 있어서
다음 수식을 입력 J2하고 표의 나머지 셀에 ctrl-enter/copy-paste/fill-down&right/auto-fill을 입력합니다.
=
IF(
J$1=$I2,
"",
COUNTIFS(
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)),
1,
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)),
1
)
)
설명:
함수 의 첫 번째 인수는 COUNTIFS()출력 테이블의 열 헤더에 해당하는 피벗 테이블의 동적으로 생성된 열입니다. 중간 평가 단계(cell의 경우 L2) 를 살펴보면 이해하기가 조금 더 쉽습니다 .
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,MATCH("E201",$A$1:$F$1,0)):INDEX($A$1:$F$6,6,MATCH("E201",$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,4):INDEX($A$1:$F$6,6,4)
→$D$1:$D$6
(각각의 두 번째 인수는 INDEX()각각 피벗 테이블의 완전히 동적인 시작 및 끝 행입니다.)
함수 의 세 번째 인수와 유사 COUNTIFS()하지만 이번에는 피벗 테이블의 동적으로 생성된 열이열출력 테이블의 헤더. 셀의 경우 L2로 평가됩니다 $B$1:$B$6.
따라서 COUNTIFS()함수는 다음과 L2같습니다.
COUNTIFS($D$1:$D$6,1,$B$1:$B$6,1)
이는 행(학생) 수를 계산하는 표준 방법입니다.둘 다열에는 1(즉, 학생이 두 수업 모두에 등록되어 있음)이 포함되어 있습니다.
캡슐화 IF()기능은 대각선 셀이 비어 있는지 확인하기 위한 것입니다.