엑셀(excel) 함수 TIP 4- (셀 참조 이해하기)

,,., 2018. 9. 10. 16:45

엑셀(excel) 함수 TIP 4- (셀 참조 이해하기)

직장생활과 진급, 진급은 직장생활에 있어서 최고의 기쁨이요, 의미입니다. 사원, 대리, 과장, 차장, 부장,...그리고 본부장, 이사....하지만 위로 올라갈수록 그 숫자가 줄어들기 때문에 평사원에서 잘 나가면 부장으로 마치는 경우가 대부분입니다. 관리를 잘 하지 못하거나 인정을 받지 못하면 차장에서 퇴직하는 경우도 많습니다.

저도 어느덧 중간관리자이며, 상사보다는 부하 직원들이 훨씬 많습니다. 만약 내가 여기에서 진급하지 못한다면....예전의 부하가 상사가 된다면....이대로 퇴직까지 갈것인가...이러한 고민을 직장인이라면 안해볼 수가 없습니다. 선택은 그냥 만족하면서 생활하는 것과 타 직장으로의 이직 또는 명예퇴직을 하고 사업을 하는 방안입니다.

준비가 되어있지 않다면 타직장이직, 퇴직 후 사업도 어렵습니다. 지금 신입사원이신가요? 중간관리자이신가요? 내가 목표하는 그 직급까지 충분히 진급할 수 있다면 목숨걸고 현 직장에 충성하십시요, 그러나 그렇지 않다면 반드시 40대 이후의 나를 위해(재태크, 이직 등)반드시 시간투자 하십시요. 준비하지 않으면 기회가 결코 찾아오지 않습니다.

1. 수식의 구조

함수는 아래와 같이 이루어집니다. 모든 함수의 첫번째는 =abc( 의 형태로 시작합니다. 함수에서 이 괄호() 가 빠지면 안됩니다. 그 뒤에 따라오는 모든 것은 인수이며 인수는 상수, 수식, 셀참조, 다른 함수 등이 사용될 수 있습니다. 이 인수에 들어가는 것이 바로 (셀 참조)입니다. 하단에서는 (A5)입니다. 이 셀참조는 상대참조, 절대참조, 혼합참조 세가지로 이루어져 있으며 이 참조를 바르게 적용해야 올바른 함수값이 추출이 됩니다.

=ABC(A5) * 6

등호(=) : 수식의 시작

함수(ABC) : ABC(값)함수는 괄호안에 지정한 절대값을 구한다

셀참조(A5) : A5의 값을 반환한다. 워크시트에 있는 셀의 값으로 계산

연산자(*) : 연산을 한다.(A5)와 6을 곱한다

상수(6) : 수식에 직접입력하는 값

▶ 실제 함수 사용 예 =INDEX(B2:G16,MATCH(F18,F2:F15,0),2)

2. 연산자의 종류

연산자는 수식에서 어떠한 계산을 수행해야 하는 지를 결정하는 것으로서 아래와 같이 산술, 비교, 참조, 연결(&)연산자로 4가지로 구분이 됩니다. 산술연산자는 더하기, 빼기 등 수학연산을 수행하고 비교연산자는 두값은 비교해서 FALSE, TRUE값을 반환합니다. 연결연산자는 두개의 값을 연결할 때 사용합니다.

산술연산자 : (+ : 더하기, - : 빼기, * : 곱하기, / : 나누기, % : 백분율 ^ : 거듭제공)

비교연산자 : (= , < >, <,>, >=, <=)

참조연산자 : (:[콜론] : 두 참조사이의 모든셀, [,[쉼표] : 여러개의 참조를 하나의 참조로,

연결연산자 : (엑셀의 & 함수) = (엑셀의 함수)

3. 상대참조

데이터시트(datasheet)의 셀은 행과 열로 이루어져 있습니다. 하단처럼 수식에 들어있는 셀의 위치가 바뀔 시에는 하단과 같이 자동으로 수식에 들어있는 참조의 영역들도 바뀌게 됩니다. 즉, 하단과 같이 각 과목의 합을 구하여(E2) 하단부위로 복사하면(E3),(E4)셀의 내용에 있는 참조값들이 한 영역씩 증가하여 바뀌게 됩니다. 이렇게 복사를 하던가 하단으로 채우기핸들을 이용시에는 셀의 참조영역이 바뀌게 됩니다.

4. 절대참조

경우에 따라서는 참조영역이 바뀌지 않아야 할 경우가 있습니다. 하단과 같이 판매이익(= 판매금액 * 2.15%) 을 구할 때 상대참조로 한다면 각각의 판매금액에 이익율(E1:2.15%)값을 곱해주여야 하지만 절대참조를 이용하면 쉽게 구할 수 있습니다. 즉 각각의 셀항에 &(절대참조)를 앞부분에 첨가하면 그 셀의 값들이 변하지 않습니다. 아래의 예를 들면 각각의 판매이익에 고정된 판매량(2.15%)을 곱해줄때 (E1)을 ($E$1)으로 해서 하단부위로 드래그(복사)하면 절대값(이익율값)은 변하지 않습니다.

▶ 절대참조 및 RANK함수를 이용한 순위결정 예)

하단부위는 학생 평균성적에 따라 석차를 구하는 함수사용의 예입니다. 함수는 RANK함수가 사용되었으며 함수안의 인수로는 절대참조가 되어 있습니다.

5. 혼합참조

혼합참조는 절대참조와 상대참조가 혼용되어있는 형태입니다. 즉 $B2 또는 B$2와 같이 $가 붙어있는 부분은 변하지 않고 $기호가 없는 부분만 셀의 위치가 바뀔때 상대적인 위치에 따라 변하게 됩니다. 하단처럼 (B2)를 복사하여 아래방향으로 드래그를 하면 B값은 변하지 않고 숫자만 증가 합니다. 즉 $ 표시가 절대참조이기 때문에 B값을 변하지 않게 고정했기 때문입니다.

6. 셀 참조형식 바꾸기 단축키 : F4

수식에서 셀범위를 입력 후 F4 누를 마다 하단과 같이 참조의 영역들이 바뀌게 된다. 그 순서는 절대참조 - 행번호에 $가 있는 혼합참조 - 열 문자에 $가 있는 혼합참조 순서로 바뀌게 됩니다. 바꾸고자 할 때는 원하는 부분을 마우스로 클릭 한 후 F4를 누르면 됩니다.

7. 다른 워크시트 참조

엑셀 수식을 계산하다 보면 해당 워크시트(worksheet)의 값뿐만이 아니고 다른 워크시트(worksheet)의 값을 참조하는 경우가 많습니다. 이렇때 다른 워크시트를 참조하라는 의미인 ！를 사용하여서 표현합니다. 하단과 같이 Sheet1에는 [1학기 점수]와 [2학기 점수]가 있습니다. 이를 다른 시트인 Sheet2에 평균을 구할 경우 우측과 같이 수식이 적용이 됩니다. =AVERAGE(Sheet1!B3,Sheet1!F3) 이때 바로 Sheet 뒤에 ！를 사용했습니다. 이 표시는 다른 시트(sheet 1)를 참조하라는 의미입니다.

☞ 엑셀 수식복사 Tip

▶ 복사명령으로 복사 : 하단에서 G9 셀에 최고점(박희망)의 값을 복사(G3)여넣기를 하면 오류가 나타납니다. [복사 - 붙여넣기]를 하면 셀의 이동과 함께 참조범위가 바뀌기 때문입니다. 이렇게 복사해서 붙여넣기를 하면 원하는 결과값이 결코 나올수가 없습니다.

▶ 셀참조 변화없이 복사

해당 결과값(G11)이 나오게 하기 위해서 [해당셀(G3)을 복사 (Ctrl+ C)후 - 컴퓨터 자판의 Esc를 누르고 - 복사하고자 하는 셀에 (Ctrl+ V)] 를 하면 됩니다. 핵심 키는 ESC 키입니다. 즉 셀참조의 영역들이 절대값으로 변하지 않도록 합니다.