엑셀 수식편(참조란?)

안녕하세요,봉자씨입니다.

   

저번 강의에 이어서 이번 강의에서는 수식에 사용 되는 참조 즉, 주소라고 말하는 부분에 대해서 함께 하겠습니다.

그 전에 다시 한번 수식의 구성을 한번 볼까요?

 

수식의 각 부분

1.함수: PI() 함수는 pi 값(3.142...)을 반환합니다. 3.142...

2.참조: A2는 A2 셀 값을 반환합니다.

3.상수: 수식에 직접 입력되는 숫자 또는 텍스트 값입니다(예:

4.연산자: 예를 들어 캐럿 연산자(^)는 숫자의 거듭 제곱을 구하고, 별표 연산자(*)는 숫자를 곱합니다.

 

오늘 이야기 할 부분은 바로 참조!!(주소)입니다. 조금 생소한 단어이기도 합니다. 참조라고 하는 것은 데이터가 있는 곳을 말할 경우 사용합니다. 다시 말해서 데이터가 어디에 존재하고 있는지를 알려줄 때 바로 이 참조를 통해서 가능하게 되는 것입니다. 참조라는 단어가 잘 와 닿지 않을 수 있습니다. 해당 데이터가 있는 주소라고 생각하시면 됩니다.

 

참조 = 주소

 

이 참조를 통해서 여러 데이터를 지정, 한 데이터만 지정, 현재 시트의 데이터를 지정, 다른 외부 시트 또는 외부 통합문서의 데이터를 지정이 가능합니다.

 

A1 참조 스타일(가장 기본적으로 사용하는 참조 스타일)

기본 참조 스타일이라고 하고 엑셀의 사용하는 대부분의 사용자가 해당 참조 스타일을 사용하고 있습니다. 문자와 숫자를 행 머리글과 열 머리글이라고 하며, 셀을 참조하려면 열 문자 다음에 행 문자를 입력하시면 됩니다. 이전에 엑셀의 시작에서도 다뤘던 내용입니다.

예를 들어 B2는 B열과 2행이 만나는 위치의 셀을 가르키게 됩니다. 아래의 표를 한번 보시고 생각해보겠습니다.

바로 아래의 표가 엑셀의 기본적인 참조를 말하는 방법이랍니다.

참조 대상	참조
A열 11행에 있는 셀	A11
A열 10행부터 20행까지의 셀 범위	A11:A21
15행 B열부터 E열까지의 셀 범위	B15:E15
7행에 있는 모든 셀	7:7
5행부터 10행까지의 모든 셀	5:10
H열의 모든 셀	H:H
H열부터 J열까지의 모든 셀	H:J
A열부터 E열까지, 10행부터 20행까지의 셀 범위	A10:E20

 

보면 이해가 되시나요? 생각보다 쉽지 않습니다. 하지만 하나만 알면 쉽게 해결이 됩니다. " : " 이 표시에 대한 이해가 되면 됩니다. 해당 표시는 간단합니다. "에서 까지.." 라고 생각하시면 됩니다. 

그렇다면, A12:E20 이렇게 되어 있다면?

맞아요 A12셀부터 E20셀까지의 모든 범위를 말하게 되는 겁니다. 간단합니다. 하지만 앞으로 엑셀을 사용하는데 있어서 엄청나게 많이 나오는 부분이니 꼭 알았으면 합니다.

 

하지만 엑셀의 참조를 이해하기 위해서는 더 알아야 할 것이 있습니다. 위에 표는 모두 동일한 시트에 있을 때입니다. 만약에 다른 시트에 있는 데이터를 참조하고 싶을 때는 어떻게 해야 할 가요?

(엑셀에서는 다른 시트에 다른 통합 문서에 있는 데이터를 참조 할 수 있습니다.)

의외로 간단합니다. 아래의 예를 통해서 한번 보겠습니다. 다른 워크시트(마케팅이라고 이름이 붙여져 있는)에 대한 B1:B10 범위에 대한 평균 값을 계산하는 예제입니다.

 

같은 통합 문서에 있는 다른 워크시트의 셀 범위에 대한 참조

1. 마케팅이라는 워크시트를 참조합니다.

2. B1에서 B10까지의 셀 범위를 참조합니다.

3. 워크시트 참조와 셀 범위 참조를 구분합니다.

위의 내용을 간단하게 정리하도록 하겠습니다.

동일 한 워크 시트는 b1:b10 만약에 다른 워크 시트인데 워크시트 이름이 마케팅이라면 마케팅!b1:b10

 

위의 내용은 꼭 기억하도록 하겠습니다. 위의 내용만 기억을 해도 복잡 한 수식이 한결 가벼워지게 됩니다. 대부분이 긴 참조에 의해서 수식이 복잡해 보이기 때문입니다.

 

이제부터 조금 헷갈릴 수가 있는 내용에 대해서 이야기 해보겠습니다.

 

절대 참조, 상대 참조 및 혼합 참조의 차이점

주소를 이야기 하는 것이 참조했습니다. 그런데 절대, 상대, 혼합 이란 말이 붙었습니다. 분명이 참조는 주소를 뜻하고 주소는 데이터가 있는 곳을 뜻한다고 했습니다만 저 단어들은 어떤 것을 의미 할 가요?

엑셀에서는 수식을 자주 사용합니다. 그 수식을 복사하는 일도 수식을 사용하는 만큼 빈번하게 발생합니다. 그럼 그 수식 안에 참조(주소)가 들어 있는 경우가 당연히 발생 할 것입니다. 그때 여러분은 그 수식의 주소가 수식을 복사한 거리만큼 같이 변경 되길 원할 수도 있고, 그것 과는 상관 없이 그냥 고정이 되었으면 하는 경우도 있을 겁니다.

 

여기서 만약에 그냥 무조건 주소가 고정이 되어 있다면, 여러분은 무조건 수식 안에 주소를 다시 작성해야 하는 번거러움이 생겨 버립니다.

그렇다고 그냥 무조건 주소가 변경이 되어 버린다면, 여러분은 계속적으로 그 수식 안에 주소를 또 원래의 주소로 다시 작성해 줘야합니다.

 

그래서 이를 고정하고 변경하고 결정 할 수 있게 해주는 것이 바로 우리가 지금 이야기 하려는 상대, 절대, 혼합 참조입니다.

 

상대 참조! => 주소 변경!!

절대 참조! => 주소 고정!!

혼합 참조! => 주소 일부 고정 일부 변경!!

 

이것이 있어서 저희가 해야 할 고생을 줄여 준다는 것을 꼭 알고 계셨으면 합니다.

 

상대 참조

수식의 상대 셀 참조(예:A1)는 수식과 해당 참조가 가리키는 셀이 들어 있는 셀의 상대 위치를 기반으로 합니다. 수식이 들어 있는 셀의 위치가 바뀌면 참조도 바뀝니다. 수식을 행이나 열 간에 복사하거나 입력하면 참조도 자동으로 조정됩니다. 새로 만드는 수식에는 기본적으로 상대 참조가 사용됩니다.

예를 들어 B2 셀의 상대 참조를 B3 셀에 복사하거나 입력하면 참조가 자동으로 =A1에서 =A2로 조정됩니다.

 

 

절대 참조

수식의 절대 셀 참조(예:$A$1)는 항상 특정 위치의 셀을 가리킵니다. 수식이 들어 있는 셀의 위치가 바뀌더라도 절대 참조는 바뀌지 않고 그대로 유지됩니다. 수식을 셀이나 열 간에 복사하거나 입력해도 절대 참조는 조정되지 않습니다. 새로 만드는 수식에는 기본적으로 상대 참조가 사용되므로 필요한 경우 절대 참조로 전환해야 합니다.

예를 들어 셀 B2의 절대 참조를 셀 B3에 복사하거나 입력하면 두 셀이 모두 =$A$1이 됩니다.

 

혼합 참조

혼합 참조에는 절대 열과 상대 행 또는 절대 행과 상대 열이 있습니다. 절대 열 참조는 $A1, $B1 등의 형태이며 절대 행 참조는 A$1, B$1 등의 형태로 사용됩니다. 수식이 들어 있는 셀의 위치가 바뀔 때 상대 참조는 바뀌지만 절대 참조는 바뀌지 않습니다. 행이나 셀 간에 수식을 복사하거나 입력하면 상대 참조는 자동으로 조정되지만 절대 참조는 조정되지 않고 그대로 유지됩니다.

예를 들어 A2 셀의 혼합 참조를 B3 셀에 복사하거나 입력하면 =A$1에서 =B$1로 조정됩니다.

 

이해도나 상황에 따라 힘들 수 있습니다. 하지만 위의 내용은 엑셀에서 필수적인 지식입니다. 꼭 숙지 하셔야 합니다.

글이 조금이나마 도움이 되셨다면 공감 부탁드립니다.

오늘도 좋은 하루 되세요!

감사합니다.

클릭 클릭 공감 클릭~~