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

와이프의 요청으로 엑셀 블로그를 시작합니다!!
기업의 데이터가 있다보니, 지식인에서 선정해서 진행하겠습니다!

[특정 주소에 텍스트 값을 입력하면, 연관된 데이터를 불러옵니다. 이 때 특정 주소를 2개를 설정하여 2개에 대한 연관된 데이터를 불러오려면 어떻게 해야할까요?]

지식인 캡쳐사항을 남겨봅니다. 사실 질문자가 무슨말을 정확하게 하는 법도 중요한데 조금 의아합니다.

1. C2에 텍스트를 입력하면 C2에 입력된 테스트와 일치하는 값을 불러오게 하는 수식이라고 합니다
: 조회할 기록테이블에 만약 텍스트가 동일하게 입력되어 있는 행이 여러개라면, 여러개를 다 불러와야 할지? 아니면 합계를 구할지?

2. F2를 입력하면 C2 혹은 F2에 텍스트가 입력되면 불러오게 하는 것으로 수정을 하려면 어떻게 바꿔야 하나요?
: F2나 C2에 입력되면 불러오게 하면 될지? 만약 두개 다입력되면 불러와야 할지?

여러가지로 의문사항이 많이 발생되는 질문입니다만, 나름대로 해석하여 준비해봤습니다!

[답안]

가정을 몇개 하겠습니다. 찾아올 데이터가 있는 참조데이터는 독립성이 있어서, 텍스트 하나는 한개라고 가정했습니다.
그리고, 여기에 있고 저기에 없다면 여기에 있는것을, 저기에만 있다면 저기에만 있는 것을, 둘다 있다면 각각 하는 것을 표현했습니다.
사실 문의사항에 대해 답은 vlookup정도만 사용하고, iferror함수만 활용하면 충분히 금방 해결가능합니다.

vlookup함수로 노란색의 텍스트 일치항목을 초록색에 불러올 수 있습니다.

만약 노란색을 두개를 봐야 하는데, 둘중에 하나가 있다면 빈칸일 경우에 vlookup이 #NA 뜨는 것을 이용, iferror로 참과 거짓일때 vlookup을 겁니다.

사실 논리적으로 생각해보면 어렵지 않습니다. iferror는 첫번째값이 참이면 참을 불러오고, 에러면 두번째값을 불러오라는 수식입니다.
vlookup은 저보다 다른분들이 더욱 설명을 잘하시겠죠 (요즘은 xlookup도 있습니다)

이렇게 정리하도록 하겠습니다. 그런데 만약 참조데이터에 바나나가 두개이상 있다면 어떻게 할까요??

[케이스2 : 만약 참조데이터에 중복된 이름 데이터가 있다면?]

여기서부터는 데이터 전처리 과정이 필요합니다. 데이터 전처리란 내가 원하는 값을 얻기 위해, 참조데이터를 가공하는 과정을 말합니다. 사실 전처리과정만 잘되어 있다면, 모든 데이터는 수식이 명쾌하게 쉽게 나올 수 있습니다.
2가지 방법을 보여드릴 텐데, 1번째는 unique수식과 sumif수식을 이용해서 전처리를 별도로 하고, 2번째는 피벗테이블을 활용한 방식입니다.

Case1) 유니크함수로 각각의 중복값을 자동으로 찾아주는 배열함수를 겁니다. 간단합니다.

Case1-2) 중복된 값을 없애고, sumif함수를 사용합니다. vlookup과 다른점은, vlookup은 위에서부터든 아래서부터든 단 하나의 데이터만 반환합니다.

Case2) 데이터 전처리의 무적인 피벗테이블을 사용합니다. 데이터를 안에 커서를 넣고, 좌측 상단에 피벗테이블을 선택합니다

피벗테이블 — Case2-2) 아래행과 시그마값에 저렇게 이름과 수치를 넣으면 데이터가 변합니다.

요렇게 전처리 과정을 진행하고, 맨 위의 케이스처럼 값을 찾아오면 누가봐도 이해할 수 있고, 커뮤니케이션이 쉬울 수 있습니다.
지금까지 다양한 케이스에 대해 실무에서 사용하는 방법을 해봤고, 물론 좀더 쉽고 좋은 방법이 있을 수 있습니다만,
엑셀도 보고서인만큼 서로 이해할 수 있는 언어로 된 수식을 짜는 것이 일하기가 쉬울 수 있습니다. 이점 꼭 참조하시고 내용 확인하시면 좋겠습니다.

다음에도 지식인에서 문의하는 건에 대해 진행해보도록 하겠습니다!

답변.xlsx

0.01MB