엑셀 셀 참조(절대 참조, 상대 참조) 알아보기

엑셀 셀 참조(절대 참조, 상대 참조) 알아보기

 

지난 포스팅까지 IF와 AND, OR 함수에 대해 배워보았습니다.

 

개념을 먼저 이해하기 위해 간단한 사용법 위주로 설명하였으며, 추가 활용법을 익힐 필요가 있다고 생각되면 시간이 지나더라도 해당 함수와 관련된 내용을 시리즈로 계속 올리도록 하겠습니다.

 

이번 시간에는 앞으로 배우게 될 다양한 함수(SUM, SUMIF, SUMIFS, COUNTIF, COUNTIFS, VLOOKUP, INDEX, MATCH... 등)의 활용에 필요한 개념인 '참조'에 대해 알아보려고 합니다.

 

누군가에게 설명하기 위해 '절대 참조', '상대 참조'와 같은 용어를 사용하면 바로 설명하려는 대상과 그 개념을 직관적으로 연결시킬 수 있기에 해당 용어를 사용하긴 하지만, 배우는 분들은 굳이 '절대 참조', '상대 참조'라는 용어보다는 수식을 작성하여 사용할 때 이렇게 사용해야 되는구나 하는 식으로 활용법을 익히는 데 중점을 두는 것이 좋을 것 같습니다.

실제로 수 백, 수 천 번 수식을 만들고 사용하였지만 만드는 과정에서 '이럴 땐 절대 참조를 하고 이럴 땐 상대 참조를 해야지'하면서 해당 용어를 머리나 입으로 생각하고 내뱉어 본 적은 손에 꼽을 정도이고 그냥 자연적으로 구하고자 하는 결과에 맞는 참조를 적용하며 수식을 만들었기에 여러분들도 용어 자체를 외우기보다는 '참조의 방법에는 이러한 것들이 있고 이렇게 사용하면 되는구나'만 익히셔도 될 것 같습니다.

 

엑셀에서 수식을 활용할 때 가장 편한 것 중 하나가 바로 수식을 한번 입력한 후 상, 하, 좌, 우로 드래그하여 다른 셀에 해당 수식이 적용된 결과가 나오도록 하는 것입니다.

하지만 간혹 드래그를 통해 수식을 적용할 때 지정한 셀이나 범위가 드래그하는 방향만큼 같이 이동하여 원하는 결과가 나오지 않거나 오류가 나기도 합니다.

이러한 상황을 이해하기 위해 참조의 개념을 익힐 필요가 있습니다.

 

 

1. 상대 참조

 

'고정하지 않는다' → '이동 시 변한다'

 

앞으로 참조를 설명할 때 '고정'이란 단어를 통해 편하게 설명하도록 하겠습니다.

 

우선 간단한 예제를 풀어 보도록 하겠습니다.

예제

위와 같이 각각 A3, A4, A5셀에 가로 값이 나와있고 B2셀에 세로 값이 나와있습니다.

문제는 결과 셀인 B3, B4, B5셀에 각각 대응되는 가로와 세로의 곱을 구하는 문제입니다.

 

이에 따라 B3셀에는 1*10, B4셀에는 2*10, B5셀에는 3*10이란 식이 들어가게 되지만 일일이 셀을 지정하여 각각의 수식을 넣는 방법은 효율적이지 않으므로, 아래와 같이 B3셀에 A3*10을 입력한 후 B5셀까지 드래그할 수 있습니다.

풀이

여기서 수식에 적용한 A3부터 A5셀을 고정되지 않은 상대 참조 상태라고 이해하면 됩니다. 간단히 말해 수식에 적용하기 위해 셀을 클릭하거나 범위를 드래그하여 잡으면 최초에는 보이는 바와 같이 열을 나타내는 문자 A와 행을 나타내는 숫자 3 사이에 아무런 기호 없이 'A3'와 같이 표시됩니다.

이 A3는 고정되어 있지 않기에 상, 하, 좌, 우로 드래그하여 이동 시 문자와 숫자가 이동하는 방향에 따라 변하게 됩니다.

이렇게 변하는 이유는 행 또는 열을 '고정'하지 않았기 때문이며, 그렇기에 B3셀에 입력된 수식을 아래로 드래그하여 제대로 계산된 값이 나올 수 있도록 한 것입니다.

 

얼핏 보면 수식을 한번 입력한 후 아래로 드래그만 하면 되기에 간단히 풀이가 완료된 것처럼 보이지만 만약 B2셀에 입력된 세로의 값이 변한다면 어떻게 될까요? B3~B5셀에 입력된 세로의 값은 B2셀과 연결되어 있지 않기에 수식을 다시 새로운 세로 값으로 바꾼 후 드래그를 해야 되는 번거로운 상황이 발생합니다.

그러면 B3셀에 입력하는 수식을 'A3*B2'로 입력하면 되지 않을까요? 만약 A3*B2로 입력한 후 드래그하면 결과는 아래와 같이 나오게 됩니다.

잘못 된 풀이

세로의 값도 수식에 포함하기 위해 'A3*B2'와 같이 입력하였으나, B5셀의 결과는 정답과 다르게 나옵니다.(B4셀도 결과적으로 맞는 값이 나오긴 했지만 사실상 수식의 적용은 잘못된 상태입니다.)

 

결과가 제대로 나오지 않는 이유는 B3셀의 수식을 아래로 드래그하여 적용할 때마다 세로 값으로 지정한 B2셀이 아래로 같이 이동하여 B3, B4로 변경되기 때문입니다.

드래그함에 따라 변경되는 이유는 앞서 설명드린 바와 같이 '고정되지 않았기 때문'이며, 고정이 필요한 셀(또는 범위)은 반드시 고정을 해야 제대로 된 결과가 나오게 됩니다.

 

상대 참조는 '고정하지 않는다' → '이동 시 변한다'라는 이해가 되었다면, 바로 '고정한다'의 개념을 배워보도록 하겠습니다.

 

 

2. 절대 참조

 

'고정한다' → '이동해도 변하지 않는다'

 

셀이나 범위를 고정하여 수식을 드래그하여도 지정한 셀이나 범위가 변경되지 않도록 하는 방법을 알아보도록 하겠습니다.

참조 시 열(A, B, C, D...)만 고정할 수도 있으며, 열은 이동하도록 하고 행(1, 2, 3, 4...)만 고정할 수도 있고, 경우에 따라 열과 행 모두 고정할 수도 있습니다.

 

고정의 방법은 고정하고자 하는 열 또는 행 앞에 '$'기호를 붙여주면 됩니다.

이렇게 고정이 된 열 또는 행은 상, 하, 좌, 우로 드래그하여 이동해도 변경되지 않고 고정된 상태로 수식에 적용됩니다.

 

이 방식을 적용하여 위의 잘못된 풀이를 제대로 풀어보면 아래와 같습니다.

풀이 정정

수식이 아래로 이동할 때에도 세로의 값이 표시된 B2셀은 이동하면 안 됩니다. 따라서 이동하지 않도록 F4키를 한번 누르거나 'B'와'2'앞에 각각 '$'기호를 붙여줍니다.

이후 아래로 드래그하게 되면 위와 같이 B2셀은 고정된 상태에서 A열만 이동하여 정확한 수식을 얻을 수 있습니다.

 

※F4키는 수식 입력 후 한번 누르면 위와 같이 열과 행을 모두 고정시켜주며, 한번 더 누르면 행만 고정, 한번 더 누르면 열만 고정, 마지막으로 한번 더 누르면 다시 모두 고정하지 않은 원래의 상태로 돌아옵니다. 

 

여기서 추가로 확인할 사항은 위 수식에서 B2셀은 변하지 않고 고정된 값이어야 되므로 열과 행을 모두 고정하였지만, 만약 열 또는 행만 고정한다면 어떤 결과가 나올까요?

열 또는 행만 고정했을 경우

위와 같이 '$B2'처럼 문자 앞에 $를 붙여 열만 고정한 경우 아래로 드래그할 때 행을 나타내는 숫자는 고정되지 않아 3, 4로 변하면서 결과가 잘못 나오는 것을 확인할 수 있습니다.

반대로 행만 고정한 경우 모두 고정했을 때와 마찬가지로 정확한 값이 나오는 것을 확인할 수 있습니다.

수식이 좌, 우로 이동하지 않기 때문에 B열은 고정했을 때와 하지 않았을 때 차이가 없지만 아래로는 이동하므로 행을 고정하느냐, 고정하지 않느냐의 차이는 발생하게 됩니다.

 

더 복잡한 경우를 가정한 예제를 통해 확인해보도록 하겠습니다.

예제

위 예제를 각 셀에 하나씩 직접 수식을 입력하여 푼다면 아래와 같이 입력해야 됩니다.

각 셀별 수식 입력

하지만 일일이 입력하는 방법보다 배운 방법을 활용하면 보다 쉽게 하나의 셀에 수식을 입력한 후 오른쪽과 아래로 드래그하여 해결할 수 있습니다.

풀이

가장 먼저 생각해야 되는 부분은 드래그할 때 고정되어 변하면 안 되는 부분을 생각하는 것입니다.

 

수식이 오른쪽으로 이동하게 되면 '열'이 변경되고 아래로 이동하게 되면 '행'이 변경됩니다.

 

B3셀에 입력된 수식이 오른쪽과 아래로 이동할 때 변경되면 안 되는 부분은 바로 가로 값이 표시된 'A'열과 세로 값이 표시된 '2'행입니다.

 

따라서 이를 조합하여 가로 값을 적용할 때 A열을 고정하고 세로 값을 적용할 때 2행을 고정하여 B3셀에 수식을 입력해보면 '=$A3*B$2'로 정리할 수 있습니다.

해당 수식을 B3셀에 입력한 후 오른쪽과 아래로 드래그하여 수식을 적용하면 풀이와 같이 고정된 열과 행은 그대로 적용되고 고정되지 않은 부분은 이동하는 방향에 따라 변경되는 것을 확인할 수 있습니다.

 

익숙하지 않으면 헷갈릴 수 있는 내용이나 지속적으로 반복하다 보면 어느새 무리 없이 활용할 수 있는 수준이 되리라 생각합니다.