이원 데이터 표

이번 영상에서는 엑셀의 데이터 분석 기능 중 많이 사용하는 이원 데이터 표 기능을 함께 알아보도록 하겠습니다. 여러분께서 가지고 계시는 실습 자료의 16번 데이터 표 엑셀 파일을 이용해서 함께 하실 수 있습니다. 실습 파일을 열어보시면 두 개의 워크시트를 확인하실 수 있습니다. 두 개의 워크시트 모두 다 구구단표가 들어있는데요. 01 구구단 상대참조, 그리고 두 번째 시트에도 구구단표가 들어있는 걸 보실 수 있습니다. 구구단표는 우리가 어떤 결과가 도출되어야 되는지 매우 잘 알고 있는 간단한 표이기 때문에 이걸 이용해서 우리가 데이터 표 기능을 좀 익혀보도록 하겠습니다. 그럼 일단 01. 구구단 상대참조라고 하는 워크시트를 한번 같이 보실까요? 우리가 일반적으로 구구단표를 만들겠다라고 한다면 통상적으로 결국 구구단이라고 하는 것은 1 곱하기 2는 2이다. 예를 들어서 3 곱하기 4는 12다. 5 곱하기 2는 10이다. 라는 방식으로 각각의 열 머리와 그리고 행의 어떤 값 이것을 곱해서 이 표를 채우는 방식으로 구구단표를 만들게 됩니다. 즉 열과 행의 곱이 바로 구구단표이죠. 그런데 우리가 이것을 만드십시오 라고 하면 특별히 데이터 표를 지금까지 쓰지 않으셨다면 대체로 상대 참조를 이용해서 만드셨을 겁니다. 한번 해볼까요? 예를 들어서 1 곱하기 1 이라고 하는 것은 C4번 셀 곱하기 B5번 셀이다 라는 거죠. 엔터를 치면 1이라고 하는 값을 얻을 수 있습니다. 그런데 이것을 그냥 전체 수식을 그대로 그냥 다 모든 곳에 밀어넣어 버리면, 컨트롤 엔터로 한번에 입력하면 우리가 기대한 것과 전혀 다른, 우리는 여기 어떤 값이 들어가야 되는지 이미 잘 알고 있습니다. 예를 들어서 9 곱하기 9는 81이죠. 그쵸? 그런데 그런 값이 들어가지 않고 엉뚱한 값이 들어가는 걸 볼 수 있습니다. 왜냐하면 아무데나 클릭해 보면 우리가 기대한 것과 달리 열과 행을 곱해 주는 것이 아니고 매우 엉뚱하게 근접해 있는 셀 두 개를 곱함으로써 엉뚱한 값이 나온다 라고 하는 것을 알고 있죠. 이 문제 해결하기 위해서 우리가 보통 수식을 입력할 때 이렇게 하지 않고 처음에 입력할 때 C4번 셀과 곱하기 B5번 셀을 곱하되 열에 해당하는 C4번 셀은 D4번 셀, E4번 셀, F4번 셀 즉 알파벳 4번 셀이 유지되어야 되기 때문에 F4키를 눌러서 여기에 달러를 B5, B6, B7으로 쭉 행에 해당하는 것들은 이렇게 B열은 유지하면서 5행, 6행, 7행 이렇게 쭉 숫자가 늘어나야 되기 때문에 B는 유지하면서 숫자가 바뀌라, 그러므로 B는 고정이라고 이렇게 F4키를 눌러서 보통 통상적으로 고정을 해줍니다. 행과 열을 이용해서 어떠한 수식을 만드는데 이렇게 한다 라는 거죠. 실제로 간단하게 해볼까요? F4키를 눌러서 4번 셀만 고정을 해주고 B5번 셀도 B만 고정을 이렇게 해준 다음 이 수식을 전체를 선택해서 컨트롤 엔터 해서 한 번에 입력하면 우리가 원하는 이런 구구단표를 매우 손쉽게 만들어 낼 수 있습니다. 물론 그렇습니다. 이것이 지금은 우리가 매우 단순하게 열과 행을 곱하면 이라고 표현했지만 곱하는 것이 아니라 다른 어떠한 함수건 어떠한 수식이건 나누기든 더하기든 뭐 루트를 씌우는 거든 뭘 하는 거든 열에 있는 데이터와 행에 있는 데이터를 이용해서 무언가 수식을 계산할 때 이러한 방식을 쓸 수 있다라는 것입니다. 그런데 이러한 수식을 사용하는 것이 이렇게 수식 자체가 단순하다면야 F4키를 눌러 갖고 이렇게 표현해 줄 수가 있습니다. 그런데 만약에 여기 들어가야 되는 수식이 엄청나게 복잡하다면 우리가 그것을 일일이 C4에 F4키를 누르고 여기 고정 여기 고정 여기 고정 일일이 이렇게 하는 것이 너무나 어렵고 힘든 일이 될 겁니다. 게다가 그나마 이것이 비록 복잡하더라도 수식 하나로 계산한 값이 여기에 바로 들어갈 수 있다 라고 하면 뭐 번거롭더라도 그렇게 처리할 수도 있는데요. 그런데 어떠한 계산들은 앞서서 계산을 한 결과를 다시 가져다가 또 계산해서 또 다시 계산하는 여러 단계를 거쳐서 만들어지는 경우들도 있습니다. 이럴 경우에는 우리가 최종적으로 사용하게 되는 이 결과 수식에 해당하는 인자 자체가 존재하지 않는 경우가 있습니다. 앞 단계 어딘가에 존재하고 있는 거죠. 이럴 경우에는 우리가 원하는 것처럼 F4키를 눌러서 뭔가 처리를 해내는 것도 굉장히 요원하고 어려운 일이 됩니다. 이런 경우에 이러한 상황에서 우리가 유용하게 사용할 수 있는 것이 바로 데이터 표 라고 하는 것입니다. 그러면 동일한 구구단표를 우리 어떤 결과가 나와야 하는지는 너무 잘 알고 있습니다. 이 구구단표를 데이터 표를 이용해서 한번 만들어 보도록 하겠습니다. 02번 구구단 데이터 표 워크시트를 열어 보시겠습니다. 두 번째 워크시트를 보시면 동일하게 구구단표를 만듭니다. 여기도 물론 구구단표를 만들 겁니다. 만드는데 앞서서 본 것과 약간 다른 점이 있습니다. 여기가 좀 다르죠. 그렇죠. 앞서서 우리가 구구단표를 만들 때는 보지 못했던 두 개의 칸이 있습니다. 아 이게 뭘까. 이 두 개의 칸은 보시면은 이겁니다. 우리는 가로 행, 가로 행과 사실은 열인거죠. 하나하나의 개별적인 필드인거죠. 이 가로 행과 메뉴명을 일치하기 위해서 이렇게 입력을 해놓은 겁니다. 가로 행과 세로 열, 이 두 개를 곱해서 구구단을 만들게 됩니다. 그런데 이때 직접적으로 계산을 하는 게 아니라 어떤 방식을 쓰냐면 데이터 표에서는 1부터 9까지 순차적으로 이 각각의 값이 이 칸에 한번씩 모두 들어갔다가 나오는 겁니다. 모두 들어갔다 나오는 이러한 과정을 계속 반복해서 계산을 해내는 것이 바로 데이터 표다 라는 겁니다. 그러면 실제로 설명보다 일단 실제로 한번 해서 이해를 해보도록 하겠습니다. 구구단표를 만들 건데요. 구구단표는 결국 이 짙게 표시되어 있는 가로 행 그리고 회색으로 표시되어 있는 세로 열의 곱입니다. 그런데 우리는 아까는 이렇게 C7번 셀과 B8번 셀의 곱 이렇게 수식을 적어서 직접적으로 여기에 수식을 적었지만 데이터 표에서는 여기에 수식을 적지 않습니다. 수식은 이 위치에 적어줍니다. 한 칸인데 어떻게 적어줄까? 이러한 값들을 모두 어떻게 여기 한 칸에 넣을 수 있는가? 라고 하면 실제로 이 값들을 넣는 게 아니고 바깥쪽에 우리가 한번 들어갔다 나올 겁니다 라고 하는 요 칸들을 이용해서 수식을 만들어서 여기에 입력해 주시면 되겠습니다. 결국 구구단이라고 하는 것은 가로 행과 세로 열의 곱인거죠. 여기에 들어가야 하는 수식은 가로 행에 해당하는 값 즉 C3번 셀 곱하기 세로 열에 해당하는 값 C4번 셀을 곱한 이 결과가 바로 이 구구단표 전체에 적용되어야 한다 라고 하는 수식을 적어 주시면 됩니다. 현재는 아무런 값이 없기 때문에 0 이라고 하는 값을 우리가 볼 수 있습니다. 필요하시면 기본값을 넣어주셔도 되고요. 이건 마치 해 찾기와 마찬가지로 기본값이 뭐가 들어있던 상관없이 결국은 해당하는 값들이 모두 들어갔다 나오게 됩니다. 그러면 데이터 표를 만들기 위해서 실행하기 위해서 일단 이 범위, 데이터 표가 만들어져야 되는 범위를 모두 이렇게 마우스로 쭉 범위를 잡아 주겠습니다. 수식이 들어가 있는 제일 위쪽 첫 번째 셀 뿐만 아니라 데이터 표가 들어가야 되는 전체 범위를 다 잡아줍니다. 그런 다음 이제 데이터 표 실행해 보도록 하겠습니다. 데이터 표 기능은 데이터 탭의 가상 분석에 들어 있습니다. 데이터 탭의 가상 분석 선택하시면 제일 마지막에 데이터 표 라고 하는 메뉴를 확인하실 수 있습니다 데이터 표를 실행해 보면 이렇게 대화 상자가 나타나죠 데이터 표의 이 대화 상자는 매우 심플합니다 뭐 별다른 게 없죠 이 데이터 표의 내용을 보시면 행 입력 셀은 어디입니까 라고 물어보고 있고 열 입력 셀은 어디입니까 라고 물어봅니다 이것은 이 얘기입니다 행 입력 셀이라고 하는 것은 현재 행으로 구성되어 있는 이 값이 어디를 갔다 와야 합니까 어디에 순차적으로 들어가야 합니까 라는 것이죠 어디입니까 여기입니다 C3번 셀에 들어가야 한다 라는 것이죠 그러면 열 입력 셀이라고 하는 것은 현재 이 표에 열로 배치되어 있는 이 항목들 이 값들이 어디에 들어가야 합니까 요 위치 C4번 셀에 순차적으로 모두 들어가야 한다 라고 우리가 지정해 줄 수 있습니다 행 입력 셀과 열 입력 셀이란 그런 뜻입니다 실제로 입력해 보겠습니다 행 입력 셀은 여기다 행에 들어가 있는 123456789 라고 하는 것은 C3번 셀을 갔다 와야 한다 마찬가지로 열에 있는 123456789라고 하는 이 값들은 여기 C4번 셀을 갔다 와야 한다 선택하시고 확인을 누르시면 이와 같이 구구단표가 완성되는 것을 보실 수 있습니다 결과는 같습니다 상대 참조로 만들든 데이터 표로 만들든 우리가 원하는 어떠한 결과는 동일하게 적용이 되었습니다 물론 각각의 셀에 실제로 들어가 있는 값의 형태는 다릅니다 우리가 상대 참조로 만들었을 때는 개별 셀에 들어가 있는 것이 하나하나 계산하는 수식이 직접적으로 직접적으로 들어가 있는 것을 볼 수 있습니다 하지만 데이터 표로 값을 만드시게 되면 각각의 개별적인 값에 들어가는 값들이 모두 수식 입력줄에 동일하게 나타나는 게 보이시죠 지금 보시는 바와 같이 동일하게 데이터 표가 입력되어 있고 이 데이터 표는 C3번 셀과 C4번 셀의 해당하는 데이터들이 모두 한 번씩 들어가서 만들어진 값이다 해당하는 수식은 이 위치에 들어 있다 라는 뜻으로 이렇게 표시가 됩니다 이것이 바로 데이터 표입니다 이런 데이터 표를 사용함으로써 우리는 굉장히 복잡한 어떠한 수식을 굳이 상대 참조를 하느라고 매번 어떤 위치를 F4키를 눌러서 맞춰준다거나 또는 여러 단계를 거쳐야 되는 것을 모두 하나의 수식에 다 넣어서 엄청나게 긴 수식을 만들어내는 이런 번거로움은 할 필요 없이 유용하게 데이터를 분석할 때 활용할 수 있습니다.