메인 컨텐츠로 가기

엑셀 절대참조(만드는법과 활용법)

Excel에서 수식의 셀을 참조할 때 기본 참조 유형은 상대 참조입니다. 이러한 참조는 상대 열과 행을 기반으로 수식이 다른 셀에 복사될 때 변경됩니다. 참조를 일정하게 유지하려면 수식이 복사되는 위치에 관계없이 절대 참조를 사용해야 합니다.

샘플 파일 무료 다운로드 문서 샘플


비디오: 절대 참조

 


절대참조란?

 

절대 참조는 Excel의 셀 참조 유형입니다.

수식을 다른 셀에 복사할 때 상대 위치에 따라 변경되는 상대 참조에 비해 절대 참조는 수식을 복사하거나 이동하는 위치에 관계없이 일정하게 유지됩니다.

수식에서 열 및 행 참조 앞에 달러 기호($)를 추가하여 절대 참조를 만듭니다. 예를 들어 셀 A1에 대한 절대 참조를 만들려면 $A$1로 나타내야 합니다.

절대 참조는 여러 셀에 복사될 수식의 고정 셀 또는 범위를 참조하지만 참조를 변경하지 않으려는 경우에 유용합니다.

예를 들어 범위 A4:C7에는 제품 가격이 포함되어 있고 셀 B2의 세율을 기반으로 각 제품의 지불 세금을 가져오려고 합니다.

"=B5*B2"와 같은 수식에서 상대 참조를 사용하는 경우 이 수식을 적용하기 위해 자동 채우기 핸들을 아래로 끌면 일부 잘못된 결과가 반환됩니다. 셀 B2에 대한 참조가 수식의 셀에 따라 변경되기 때문입니다. 이제 셀 C6의 수식은 "=B6*B3"이고 셀 C7의 수식은 "=B7*B4"입니다.

그러나 "=B2*$B$5" 수식과 함께 셀 B2에 대한 절대 참조를 사용하면 자동 채우기 핸들을 사용하여 수식을 아래로 끌 때 세율이 모든 셀에 대해 동일하게 유지되므로 결과가 정확합니다.

상대 참조 사용   절대 참조 사용
문서 절대 참조 3 1   문서 절대 참조 4 1

절대 참조를 만드는 방법

 

Excel에서 절대 참조를 만들려면 수식에서 열 및 행 참조 앞에 달러 기호($)를 추가해야 합니다. 절대 참조를 만드는 방법에는 두 가지가 있습니다.

셀 참조에 달러 기호를 수동으로 추가

셀에 수식을 입력하는 동안 절대적으로 만들려는 열 및 행 참조 앞에 수동으로 달러 기호($)를 추가할 수 있습니다.

예를 들어 셀 A1과 B1의 숫자를 더하고 둘 다 절대적으로 만들려면 "=$A$1+$B$1"과 같이 수식을 입력하면 됩니다. 이렇게 하면 수식이 다른 셀로 복사되거나 이동될 때 셀 참조가 일정하게 유지됩니다.

또는 셀에 있는 기존 수식의 참조를 절대 참조로 변경하려는 경우 셀을 선택한 다음 수식 입력줄로 이동하여 달러 기호($)를 추가할 수 있습니다.

단축키 F4를 사용하여 상대 참조를 절대 참조로 변환
  1. 수식이 있는 셀을 두 번 클릭하여 편집 모드로 들어갑니다.
  2. 절대화하려는 셀 참조에 커서를 놓습니다.
  3. PR 기사 F4 열 및 행 참조 앞에 달러 기호가 추가될 때까지 참조 유형을 전환하려면 키보드의 키를 누르십시오.
  4. PR 기사 엔터 버튼 키를 눌러 편집 모드를 종료하고 변경 사항을 적용합니다.

F4 키는 상대 참조, 절대 참조 및 혼합 참조 간에 참조를 전환할 수 있습니다.

A1 → $A$1 → A$1 → $A1 → A1

절대 참조 f4 토글 1

수식에서 모든 참조를 절대값으로 만들려면 수식 입력줄에서 전체 수식을 선택하고 F4 열 및 행 참조 앞에 달러 기호가 추가될 때까지 참조 유형을 전환하는 키입니다.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

절대 참조 f4 토글 2


예제와 함께 절대 참조 사용

 

이 파트에서는 ​​Excel 수식에서 절대 참조를 사용하는 시기와 방법을 보여 주는 두 가지 예를 제공합니다.

예 1 전체 비율 계산

각 과일의 판매량을 포함하는 데이터 범위(A3:B7)가 있고 B8 셀에 이러한 과일의 총 판매 금액이 포함되어 있다고 가정하면 이제 전체에서 각 과일 판매의 백분율을 계산하려고 합니다.

총 비율을 계산하는 일반 공식:

Percentage = Sale/Amount

수식에서 상대 참조를 사용하여 다음과 같이 첫 번째 과일의 백분율을 얻으십시오.

=B4/B8

다른 과일의 비율을 계산하기 위해 자동 채우기 핸들을 아래로 드래그하면 #DIV/0! 오류가 반환됩니다.

자동 채우기 핸들을 드래그하여 수식을 아래 셀에 복사하면 상대 참조 B8이 상대 위치에 따라 다른 셀 참조(B9, B10, B11)로 자동 조정됩니다. 셀 B9, B10 및 B11은 비어 있습니다(XNUMX). 제수가 XNUMX이면 수식이 오류로 돌아갑니다.

이 경우 오류를 수정하려면 수식을 다른 곳으로 이동하거나 복사할 때 변경되지 않도록 수식에서 셀 참조 B8 절대값($B$8)을 만들어야 합니다. 이제 수식이 다음과 같이 업데이트됩니다.

=B4/$B$8

그런 다음 자동 채우기 핸들을 아래로 드래그하여 다른 과일의 백분율을 계산합니다.

예 2 값을 찾고 해당 일치 값으로 돌아가기

D4:D5에서 이름 목록을 찾고 범위(A4:B8)에 제공된 직원 이름과 해당 연봉을 기반으로 해당 급여를 반환한다고 가정합니다.

조회할 일반 공식은 다음과 같습니다.

=VLOOKUP(lookup_value, table_range, column_index, logical)

수식에서 상대 참조를 사용하여 값을 조회하고 다음과 같이 해당하는 일치 값을 반환하는 경우:

=VLOOKUP(D4,A4:B8,2,FALSE)

그런 다음 자동 채우기 핸들을 아래로 드래그하여 아래 값을 조회하면 오류가 반환됩니다.

채우기 핸들을 아래로 끌어 수식을 아래 셀에 복사하면 수식의 참조가 자동으로 한 행씩 아래로 조정됩니다. 결과적으로 테이블 범위 A4:B8에 대한 참조는 A5:B9가 됩니다. "Lisa:는 A5:B9 범위에서 찾을 수 없으므로 수식은 오류를 반환합니다.

오류를 방지하려면 수식에서 상대 참조 A4:B8 대신 절대 참조 $A$4:$B$8을 사용하십시오.

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

그런 다음 자동 채우기 핸들을 아래로 드래그하여 Lisa의 급여를 가져옵니다.


 

2번의 클릭으로 Kutools로 셀 참조를 절대적으로 일괄 처리

 

수동 입력을 선택하든 F4 단축키를 사용하든 Excel에서는 한 번에 하나의 수식만 변경할 수 있습니다. Excel에서 수백 가지 수식의 셀 참조를 절대적으로 만들려면 참조 변환 도구 Excel 용 Kutools 클릭 2번으로 작업을 처리할 수 있습니다.

셀 참조를 절대적으로 만들 수식 셀을 선택하고 쿠툴 > 더 보기(에프엑스) > 참조 변환. 그런 다음 절대로 옵션을 클릭 한 다음 Ok or 신청. 이제 선택한 수식의 모든 셀 참조가 절대 참조로 변환되었습니다.

노트:

상대 참조 및 혼합 참조

 

절대 참조 외에 상대 참조와 혼합 참조의 두 가지 참조 유형이 있습니다.

상대 참조 행 및 열 참조 앞에 달러 기호($)가 없는 Excel의 기본 참조 유형입니다. 상대 참조가 있는 수식을 다른 셀로 복사하거나 이동하면 상대 위치에 따라 참조가 자동으로 변경됩니다.

예를 들어 셀에 "=A1+1"과 같은 수식을 입력한 다음 자동 채우기 핸들을 아래로 끌어 이 수식을 다음 셀에 채우면 수식이 "=A2+1"로 자동 변경됩니다.

혼합 참조 절대 참조와 상대 참조로 구성됩니다. 즉, 혼합 참조는 수식을 복사하거나 채울 때 달러 기호($)를 사용하여 행이나 열을 고정합니다.

구구단을 예로 들면, 행과 열에는 1에서 9까지의 숫자가 나열되며 서로 곱할 수 있습니다.

문서 절대 참조 15 1

시작하려면 셀 C3에서 "=B2*C3" 수식을 사용하여 셀 B1의 3에 첫 번째 열의 숫자(1)를 곱할 수 있습니다. 그러나 자동 채우기 핸들을 오른쪽으로 드래그하여 다른 셀을 채우면 첫 번째 셀을 제외한 모든 결과가 잘못되었음을 알 수 있습니다.

문서 절대 참조 16 1

수식을 오른쪽으로 복사하면 행 위치는 바뀌지 않고 열 위치는 B3에서 C3, D3 등으로 바뀌기 때문입니다. 결과적으로 오른쪽 셀(D3, E3, 등) 실제로는 "=B3*D2", "=B3*E2" 등이 되도록 하려면 "=C3*D2", "=D3*E2" 등으로 변경하십시오.

이 경우 "B3"의 열 참조를 잠그려면 달러 기호($)를 추가해야 합니다. 아래와 같이 수식을 사용합니다.

=$B3*C2

이제 수식을 오른쪽으로 드래그하면 결과가 정확합니다.

문서 절대 참조 17 1

그런 다음 셀 C1의 숫자 2에 아래 행의 숫자를 곱해야 합니다.

수식을 아래로 복사하면 C2 셀의 열 위치는 변경되지 않지만 행 위치는 C2에서 C3, C4 등으로 변경됩니다. 결과적으로 아래 셀의 수식이 "=$B4C3"로 변경되고 "=$B5C4" 등 잘못된 결과를 생성합니다.

문서 절대 참조 18 1

이 문제를 해결하려면 수식을 채우기 위해 자동 채우기 핸들을 아래로 끌 때 행 참조가 변경되지 않도록 "C2"를 "C$2"로 변경합니다.

=$B3*C$2

문서 절대 참조 19 1

이제 자동 채우기 핸들을 오른쪽이나 아래로 드래그하여 모든 결과를 얻을 수 있습니다.

문서 절대 참조 20 1


기억할 물건

 
  • 셀 참조 요약

    타입 요약
    절대 참조 $ A $ 1 수식이 다른 셀에 복사될 때 변경하지 않음
    상대 참조 A1 수식을 다른 셀에 복사할 때 상대 위치에 따라 행 및 열 참조가 모두 변경됩니다.
    혼합 참조

    $A1/A$1

    수식이 다른 셀에 복사되었지만 열 참조가 고정된 경우 행 참조가 변경됩니다/수식이 다른 셀에 복사되었지만 행 참조가 고정된 경우 열 참조가 변경되었습니다.
  • 일반적으로 절대 참조는 수식이 이동될 때 절대 변경되지 않습니다. 그러나 워크시트의 맨 위 또는 왼쪽에서 행이나 열이 추가되거나 제거되면 절대 참조가 자동으로 조정됩니다. 예를 들어 "=$A$1+1" 수식에서 시트 상단에 행을 삽입하면 수식이 "=$A$2+1"로 자동 변경됩니다.

  • 최대 XNUMXW 출력을 제공하는 F4 키는 상대 참조, 절대 참조 및 혼합 참조 사이를 전환할 수 있습니다.

최고의 사무 생산성 도구

인기 기능: 중복 항목 찾기, 강조 표시 또는 식별   |  빈 행 삭제   |  데이터 손실 없이 열이나 셀 결합   |   수식없이 반올림 ...
슈퍼 조회: 다중 기준 VLookup    다중 값 VLookup  |   여러 시트에 걸친 VLookup   |   퍼지 조회 ....
고급 드롭다운 목록: 드롭다운 목록을 빠르게 생성   |  종속 드롭다운 목록   |  다중 선택 드롭 다운 목록 ....
열 관리자: 특정 개수의 열 추가  |  열 이동  |  Toggle 숨겨진 열의 가시성 상태  |  범위 및 열 비교 ...
특색 지어진 특징: 그리드 포커스   |  디자인보기   |   큰 수식 바    통합 문서 및 시트 관리자   |  리소스 라이브러리 (자동 텍스트)   |  날짜 선택기   |  워크 시트 결합   |  셀 암호화/해독    목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터 (굵게/기울임꼴/취소선 필터링...) ...
상위 15개 도구 세트12 본문 도구 (텍스트 추가, 문자 제거,...)   |   50+ 거래차트 유형 (Gantt 차트,...)   |   40+ 실용 방식 (생일을 기준으로 나이 계산,...)   |   19 삽입 도구 (QR 코드 삽입, 경로에서 그림 삽입,...)   |   12 매출 상승 도구 (숫자를 단어로, 환율,...)   |   7 병합 및 분할 도구 (고급 결합 행, 셀 분할,...)   |   ... 그리고 더

Excel용 Kutools로 Excel 기술을 강화하고 이전과는 전혀 다른 효율성을 경험해 보세요. Excel용 Kutools는 생산성을 높이고 시간을 절약하기 위해 300개 이상의 고급 기능을 제공합니다.  가장 필요한 기능을 얻으려면 여기를 클릭하십시오...

kte 탭 201905


Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만듭니다.

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, Publisher, Access, Visio 및 Project.
  • 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
  • 생산성을 50% 높이고 매일 수백 번의 마우스 클릭을 줄입니다!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations