[MS엑셀] 실무용 엑셀 2편

자 오늘도 지식인의 엑셀 질문을 실습하고자 합니다.

[질문사항]

질문에 대한 데이터이다.

데이터가 이렇게 있는 경우, 데이터1이 '가' 이면서 데이터2의 겹치지 않는 데이터의 개수를 세고 싶다.
sumproduct로 사용하고 싶습니다.
또한 데이터 2는 지속적으로 추가될 거니까, sumproduct를 쓸 때, 열 참조를 제한하지 않고 'B'열 전체로 하고 싶습니다.
아니면 다른방법이 있을까요?

 

[가정]

오늘도 저의 난독증은 시작되었습니다. 하지만 열심히 궁리한 결과 아래와 같은 가정을 하겠습니다
'나'의 데이터의 경우 'b' 1개와 'a' 2개로 나뉘어 있는데, 내가 구해야 할 숫자는 '나-a', '나-b' 즉 2개다.
이 가정을 위해 문제를 풀어보겠습니다
(사실 전 sumproduct를 잘 사용할 줄 모르고 왜 써야 하는지도 잘은 모르겠습니다. 그래서 그냥 업무다 생각하고 풀어보겠습니다)

 

[답변 : 엑셀 실습]

저는 unique함수와 left함수, countif함수로 진행을 해보겠습니다. 그러면 자동적으로 행이 채워지더라도 숫자를 계속 자동적으로 셀 수 있습니다.

1단계 : 각 유형별 자동으로 데이터에 대한 구분자를 만듭니다. 만약 구분자 수식을 계속 채우기 싫다면, 표기능을 활용하여 표로 해놓거나, 미리 열을 긁어놓는 방법도 있습니다.

요게 표기능을 만드는 방법이고, 데이터1, 2가 채워질 때마다 구분자가 자동으로 채워집니다. 가장 편해요

표의 색깔이 마음에 안들면 표 -> 오른쪽에 스타일을 선택하면 됩니다.

여기까지가 구분자를 자동으로 채워넣는 1단계입니다.

반응형

 

 

그다음 구분자에 대해 unique함수를 밑에까지 걸어서 독립값을 배열로 반환합니다 (중복값 없애기 기능을 2019 이상인가? 여기서 수식으로 구현한 내용입니다)

그리고 구분자 독립값에 대해 데이터1을 다시 구해줍니다.

그리고 데이터1에 대해서도 별도의 열에 unique를 걸어줍니다.

그다음에 countif를 걸어서, 범위는 구분자정리에서 발췌한 데이터1, 그리고 찾을값은 데이터1정리에 있는 열로 정리합니다.

요렇게 2번째 단계를 진행하면 데이터 1, 데이터 2가 진행될 때마다 자동으로 최종값이 채워지는 수식을 구할 수 있습니다
단, 배열함수가 있는 (unique) 구분자정리, 데이터 1 정리에는 표의 기능을 삽입하면 에러가 걸릴 수 있으니, 최종값의 수식을 밑으로 쭈욱 걸어주세요. 만약에 숫자가 0이거나 보기 싫은 것 있으면 표현서식에서 #,##;-#,##; 요렇게 해버리면 0에 대해서는 아예 그려지지 않으니 보기가 좋을 겁니다!

오늘 엑셀 실습은 이렇게 마칩니다. 부디 도움이 되시길.

답변.xlsx

0.01MB