엑셀 셀 참조방식 완벽정리 | 상대참조 절대참조 혼합참조 이론 및 실전예제

많은 함수를 다룰 시 작업시간 단축을 위한 필수 지식인 '셀 참조방식'의 원리와 실전예제를 단계별로 살펴봅니다.

홈페이지 » 엑셀 셀 참조방식 완벽정리 | 상대참조 절대참조 혼합참조 기초 및 실전예제

엑셀 셀 참조방식의 모든것 | 상대참조 vs 절대참조 vs 혼합참조 총정리

엑셀 셀 참조방식 목차 바록가기
강의 요약

실무에서 넓은 범위에 많은 수식을 빠르게 입력하는 것은 작업시간 단축에 매우 중요한 부분 중 하나입니다. 특히 동일한 형태의 수식을 넓은 범위에 빠르게 입력하려면, 셀의 참조방식을 잘 이해하고 사용하는 것이 아주 중요한데요. 이번 강의에서는 '셀 참조방식'의 기초부터 응용, 그리고 실전 예제까지 셀 참조방식의 모든것을 단계별로 살펴봅니다.

영상강의

예제파일 다운로드

셀 참조방식 알아보기

엑셀 셀 참조방식은 '$(달러기호)'로 결정됩니다. 그리고 달러($) 기호가 어디에 붙는지에 따라 '상대참조', '절대참조', '혼합참조'로 구분되며 각 참조방식은 아래와 같습니다. 상대참조 및 절대참조, 혼합참조에 대한 더욱 자세한 설명 및 기초 이론은 아래 상대참조 vs 절대참조 완벽비교 포스트를 참고하세요.

  • 상대참조 : 셀이 이동할 때 참조되는 셀 주소도 같이 이하는 셀 참조방식입니다.

    상대참조
    상대참조는 셀이 이동할 때 참조되는 셀 주소도 같이 이동하는 참조방식입니다.
  • 절대참조 : 셀이 이동할 때 참조되는 셀 주소는 항상 고정되는 셀 참조방식입니다.

    절대참조
    절대참조는 참조되는 셀 주소가 항상 고정되는 참조방식입니다.
  • 혼합참조 : $(달러기호)가 붙은 위치에 따라 행 또는 열 중에 하나만 고정되는 셀 참조방식입니다.

    혼합참조
    혼합참조는 행 또는 열 중 하나만 고정하는 참조방식입니다.

상대참조 vs 절대참조, 언제 사용하나요?

상대참조와 절대참조를 언제 사용하는지 알아보기 위해 각 제품별 판매가격와 판매비율을 구하는 예제를 살펴보겠습니다.

1. 제품별 판매 가격 구하기 (상대참조 예제)
  1. 예제파일 '2' 시트로 이동한 뒤, 가격의 첫번째 셀인 E4 셀을 선택합니다. 이후 셀에 [수량 * 단가]를 입력하여 값을 계산합니다.

    상대참조 가격 계산
    수량 * 단가로 제품별 판매가격을 계산합니다.
  2. 마우스 커서를 셀의 오른쪽 아래로 이동하여 '검은색 십자가'로 만든 뒤, 아래로 드래그하여 수식을 자동채우기 합니다.

    자동채우기
    마우스 커서를 셀의 오른쪽 아래로 이동한 뒤, 값을 아래로 자동채우기합니다.
  3. 각 제품별 판매 가격이 계산되었습니다.

    가격 자동채우기 완료
    제품별 판매가격이 계산 되었습니다.
  4. '수식' - '수식 표시'를 클릭하거나, 단축키 Ctrl+ ~ 를 눌러 사용된 수식을 표시합니다.

    수식표시 활성화
    '수식' - '수식표시'를 클릭하여 각 셀에 사용된 수식을 표시합니다.
  5. 셀 자동채우기를 하면서 참조된 셀이 같이 이동 된 것을 볼 수 있습니다. 이러한 참조방식을 '상대참조' 라고 합니다.

    셀 참조방식 상대참조 확인
    셀이 이동하면서 참조된 셀 주소도 같이 이동된 것을 확인할 수 있습니다.
  6. 다시 '수식표시'를 해제한 뒤, '합계' 셀을 선택합니다. '홈' - '자동합계'를 클릭하거나, 단축키 Alt + =를 입력하면 제품의 가격 합계가 계산됩니다.

    가격 합계 계산
    제품별 판매가격의 합계를 계산합니다.
2. 제품 판매 비율 구하기 (절대참조 예제)
  1. 비율 첫번째 셀인 F4셀을 선택한 후, [가격 / 합계] 로 값을 계산합니다.

    비율 계산
    각 제품별 판매 비율을 계산합니다.
  2. 이전과 동일하게 수식을 아래로 자동채우기 합니다. 가격을 계산할 때와는 다르게 #DIV/0! (0으로 나눠짐 오류)가 출력됩니다.

    비율 셀 참조방식 오류
    수식을 아래로 자동채우기 하면 #DIV/0! 오류가 출력됩니다.
  3. '수식' - '수식표시'를 클릭하거나, 단축키 Ctrl + ~ 를 눌러 사용된 수식을 표시합니다. 이후 비율의 첫번째 셀을 클릭하면 '합계'가 올바르게 입력된 것을 볼 수 있습니다.

    비율 첫번째 셀
    수식표시를 한 뒤, 첫번째 셀을 클릭하면 합계가 올바르게 참조 된 것을 볼 수 있습니다.
  4. 하지만 두번째, 세번째 셀을 클릭하면 '합계'셀이 아래로 이동되면서 올바르지 않은 값이 분모로 참조된 것을 볼 수 있습니다.

    비율 두번째 셀 셀 참조방식 문제
    두번째 셀 이후부터는 합계셀이 아래로 이동하면서 옳지 않은 값이 참조된 것을 볼 수 있습니다.
  5. 수식표시를 해제한 뒤, 비율의 첫번째 셀로 이동합니다. 합계셀(E12)를 선택 한 후, 키보드 F4키를 눌러 절대참조로 변경합니다.

    절대참조 변경
    합계셀을 선택한 뒤, 키보드 F4키를 눌러 절대참조로 변경합니다.
  6. 수식을 다시 자동채우기 하면 비율이 올바르게 계산됩니다.

    비율 계산 완료
    수식을 아래로 자동채우기 하면 각 제품별 판매 비율이 올바르게 계산됩니다.

혼합참조는 언제 사용하나요?

혼합참조는 상대참조나 절대참조에 비해 다소 어렵다고 생각 될 수 있는 셀 참조방식입니다. 하지만 원리만 이해하면 여러개의 수식을 빠르게 입력하는데에 아주 큰 도움이 됩니다. 혼합참조를 알아보기 위해 각 판매량 대비 커미션 테이블을 예제로 살펴보겠습니다. 예제파일의 4번 시트로 이동합니다.

판매량 대비 커미션 표
판매량 대비 커미션 표를 예제로 혼합참조 사용법을 알아봅니다.

판매량 대비 커미션을 구할 경우, 첫번째 셀에 수식을 입력한 뒤, 오른쪽 및 아래 방향으로 수식을 자동채우기 하게 됩니다.

판매량 대비 커미션 자동채우기
입력한 수식은 오른쪽 및 아래 방향으로 자동채우기 됩니다.

수식을 자동채우기 할 때, 표 상단에 위치한'커미션' 값은 오른쪽으로는 이동하되 아래방향으로는 이동하면 안되는 값 입니다. (숫자(=행)를 고정합니다.)

커미션 셀 참조방식
커미션 범위는 '아래방향'으로는 이동하면 안 됩니다. (숫자(=행)을 고정합니다)

또한 표 좌측에 위치한 '판매량' 값은 아래로는 이동하되 오른쪽으로는 이동하는 안되는 값이 됩니다.(알파벳(=열)을 고정합니다.)

판매량 셀 참조방식
판매량은 오른쪽으로 이동하면 안됩니다. (알파벳(=열)을 고정합니다)

따라서, 판매량 대비 커미션을 구하기 위해 표의 첫번째 셀에 아래와 같이 수식을 입력합니다.

판매량 대비 커미션 혼합참조
위와 같이 셀 참조방식 (혼합참조) 을 변경하여 수식을 입력합니다.

이후 수식을 자동채우기 하면 판매량 대비 커미션이 쉽게 계산됩니다.

판매량 대비 커미션 완료
수식을 자동채우기 하면 판매량 대비 커미션이 한번에 계산됩니다.

셀 참조방식 실전예제 (차수별 시험 평균성적 구하기)

셀 참조방식 실전예제로 학생들의 차수별 시험성적 평균을 계산해보겠습니다. 에제파일의 5 페이지로 이동합니다.

학생들 시험 평균성적 예제
셀 참조방식 실전 예제로 학생들의 차수별 시험 성적 평균을 계산합니다.

차수별 평균 성적 계산을 위해 '조건을 만족하는 평균 계산'에 사용되는 AVERAGEIF 함수를 사용합니다. AVERAGEIF 함수 구문은 아래와 같습니다.

= AVERAGEIF ( 조건범위, 조건, [평균범위] )
  1. 첫번째 인수인 '조건범위'로 '차수범위'를 입력합니다. 이 차수범위는 수식이 자동채우기 될 때 이동되지 않고 항상 고정되어야 하는 범위입니다. 따라서 절대참조로 입력합니다.

    차수 셀 참조방식 절대참조
    차수범위는 자동채우기와 상관없이 항상 고정되어야 하는 범위입니다.
  2. 두번째 인수인 '조건'으로 각 '차수'를 입력합니다. 이 차수는 오른쪽으로는 이동하면 안되고, 아래로만 이동되어야 합니다. 따라서 알파벳(=열) 앞에만 $(달러표시)가 붙도록 셀 참조방식을 변경합니다.

    차수 조건 혼합참조
    조건으로 입력되는 차수는 '아래방향'으로만 이동되어야 합니다. (알파벳(=열)을 고정합니다)
  3. 마지막 인수인 '평균범위'는 각 '필기/실기점수' 범위가 입력됩니다. 이 범위는 오른쪽으로만 이동하고, 아래로는 이동하면 안되는 범위입니다. 따라서 숫자(=행) 앞에만 $(달러표시)가 붙도록 셀 참조방식을 변경합니다.

    시험 점수 셀 참조방식 혼합참조
    평균을 구할 범위는 오른쪽으로만 이동해야합니다. (숫자(=행)을 고정합니다)
  4. 따라서 차수별 시험 평균성적을 구하기 위한 AVERAGEIF 함수 수식은 아래와 같이 입력됩니다. 이후 수식을 자동채우기 하면 각 차수별 시험 평균성적 계산이 완료됩니다.
    =AVERAGEIF($C$4:$C$14,$C15,D$4:D$14)

    수식 입력 후 완료
    위와같이 수식을 입력한 뒤 자동채우기 하면 각 차수별 평균점수가 한번에 계산됩니다.

관령링크 : MS 홈페이지 셀 참조 변경 (상대참조 - 절대참조 - 혼합참조 간 변환)

5 21 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
29 Comments
Inline Feedbacks
View all comments
올챙이
올챙이
2020년 5월 7일 11:11 오전
게시글평점 :
     

8강 ... 수강 완료

하늘보리
하늘보리
2020년 5월 11일 6:26 오전
게시글평점 :
     

기초부터 탄탄히 할 수 있어서 너무 좋아요 ^^

꾸루꾸루
2020년 5월 11일 6:31 오전
게시글평점 :
     

정말 유익합니다.

f9****
2020년 5월 12일 3:58 오후
게시글평점 :
     

며칠 전 유튜브를 통해서 알게 되었는데, 그동안 이렇게 좋은 사이트가 있는 줄 몰랐네요.
자주 들러서 많이 배우려합니다.

풍이맘
2020년 5월 12일 11:17 오후
게시글평점 :
     

감사히 잘 보고 있습니다^^

추기경
2020년 5월 21일 12:49 오전
게시글평점 :
     

ㅎㅎ 8강까지 완료~ 좋아요!

초코우유
초코우유
2020년 6월 7일 9:02 오후
게시글평점 :
     

강의 너무너무 좋아요 열심히 완강할게요!

Peter
Peter
2020년 6월 11일 11:20 오후
게시글평점 :
     

수강 잘 들었습니다.

엑셀초보
엑셀초보
2020년 6월 16일 12:58 오전
게시글평점 :
     

오늘도 좋은 강의 너무나 감사합니다!
Alt + 등호(=) 로 자동합계 구하는 단축키는 한국어 입력기 상태에서는 안 되는건가요? 매번 키보드 입력기 설정 바꿔줘야 되는게 조금 번거롭네요ㅠ

처소
처소
2020년 6월 23일 10:54 오전
게시글평점 :
     

친절한 강의 감사합니다~ 잘 들었습니다~~

뿌까뿌가
뿌까뿌가
2020년 6월 30일 3:52 오후
게시글평점 :
     

감사합니다.

안녕하세요
안녕하세요
2020년 7월 2일 3:59 오전
게시글평점 :
     

언제나 강의 감사하게 잘 보고 있습니다. 질문이 두 가지 있는데요. 엑셀2010 버전에서는 Alt N R 즉, 추천차트가 존재하지 않나요? Alt N으로 삽입에 들어가도 추천차트는 보이지 않네요. 그리고 alt+= 명령어가 먹히질… 더보기 »

엠니아
엠니아
2020년 7월 10일 10:04 오전
게시글평점 :
     

감사합니다

엑셀배우미
엑셀배우미
2020년 7월 15일 3:23 오후
게시글평점 :
     

판매량 커미션 비교표에서 판매량 A2 셀 부분 대각선으로 도형그리고나서 저기 2개를 어떻게그리죠? 한셀에 저렇게 글씨쓰는 법.. 알고싶습니다.

파파깡
파파깡
2020년 7월 15일 4:38 오후
게시글평점 :
     

판매량 커미션 비교표에 보면은 표 왼쪽상단 제목 밑에 슬래시 하고 판매량 / 커미션 나눠서 써놓으셨는데, 저거 쉽게하는 방법 알려주세요.

파파깡
파파깡
2020년 7월 17일 8:37 오전

아이고 감사합니다~ 보겠습니다~

홍비맘
홍비맘
2020년 7월 22일 11:52 오전
게시글평점 :
     

저는 왜 'alt+=' 가 안되죠? ㅜㅠ

홍비맘
홍비맘
2020년 7월 22일 12:31 오후
Reply to  홍비맘

아래 '안녕하세요'님 글 참조하구 이젠 돼요
근데 입력변환을 해야하는게 쫌 불편하네요
걍 자동합계를 눌러서 쓰는게 더 빠를 듯......

어때요
어때요
2020년 7월 31일 12:43 오후
게시글평점 :
     

감사합니다.

ease****
ease****
2020년 8월 5일 6:34 오후
게시글평점 :
     

열심히 강의 듣고 있는 엑셀초보 입니다. 아래 댓글상 버전이 틀려서 안되는것 외에 함수를 =averageif 이렇게 쓰여 지지않고 = a v e r a g e i f 이렇게 띄어쓰여집니다. 어떻게… 더보기 »

유나
유나
2020년 8월 29일 12:44 오후
게시글평점 :
     

쉽게 설명해주셔서 매일 재미있게 공부하고 있습니다 ^^

명품깽여사
명품깽여사
2020년 9월 29일 12:49 오후
게시글평점 :
     

너무 좋은 엑셀 강의. 오늘도 1강 수강완료합니다.~~~~ 감사합ㄴ디ㅏ.

미성
미성
2020년 10월 14일 1:02 오후
게시글평점 :
     

잘듣고 갑니다
감사합니다

Sonia
Sonia
2020년 10월 14일 1:58 오후
게시글평점 :
     

진짜 최고예요. 처음부터 하나씩 차근차근 완강할거예요. 이런 퀄리티 강의 정말 감사합니다.

알콩달콩이
알콩달콩이
2020년 10월 23일 2:35 오후
게시글평점 :
     

감사합니다.

29
0
여러분의 생각을 댓글로 남겨주세요.x