엑셀 SUM 함수로 상대적, 절대적, 혼합적 참조를 사용하는 방법

엑셀 SUM 함수로 상대적, 절대적, 혼합적 참조를 사용하는 방법

 Excel 셀 참조는 공식에서 특정 결과를 얻기 위해 상대적이거나 절대적이거나 혼합될 수 있습니다.

 상대 참조는 다른 셀로 복사될 때 변경되지만 절대 참조는 그대로 유지됩니다.

 혼합된 참조는 공식의 열 또는 행을 잠급니다.

 

2023.07.17 - Ctrl+C, Ctrl+V, Ctrl+X, Ctrl+Z의 시작과 기원에 대해서 알아보자

Ctrl+C, Ctrl+V, Ctrl+X, Ctrl+Z의 시작과 기원에 대해서 알아보자

Excel에서 많은 데이터를 처리할 때 상대 참조, 

절대 참조 또는 혼합 참조를 사용하여 수동 작업을 반복적으로 

저장하고 공식이 올바르게 작동하는지 확인하고 

몇 번의 빠른 작업만으로 많은 양의 데이터를 캡처할 수 있습니다.

 

이러한 엑셀 참조 유형의 사용법에 대해 알아보겠습니다.

 

Excel 셀 참조란?

다른 셀 또는 셀 범위 내에 포함된 정보를 캡처하려는 경우 셀 참조를 사용합니다. 

 

예를 들어, 아래의 공식은 A1과 A2 세포를 가리키며, 

이들의 내용물을 함께 더해 결과물을 만들어낼 것입니다.

 

=SUM(A1+A2)

 

다음 유형의 셀 참조를 사용하여 특정 결과를 얻을 수 있습니다.

기본적으로 Excel의 참조는 상대적이며, 셀의 상대적 위치를 나타냅니다.

 

셀 A1에 셀 A2를 포함하는 공식을 입력하는 경우, 입력하는 셀 아래에 있는 셀을 참조하는 것입니다.

 

공식을 다른 셀에 복사하는 경우 기준은 이전 위치와 동일한 상대 작업을 자동으로 수행합니다.

공식을 다른 셀에 복사해도 변경되지 않는 절대 참조를 사용할 수도 있습니다. 

절대적인 참조를 사용하여 셀을 지칭하는 공식을 생성하면 공식이 사용되는 모든 곳에서 동일한 셀이 참조됩니다.

마지막으로 두 가지를 조합한 혼합 참조를 사용할 수 있습니다.

이것들을 좀 더 자세히 알아보겠습니다.

 

Excel에서 상대 참조를 사용하는 방법

여기에 표시된 예제에서 각 항목에 대해 매달 얼마나 많은 비용을 지출했는지 알 수 있습니다. 

그들이 지금 하고 싶은 것은 각 달의 총 지출을 계산하는 것입니다.

 

먼저 첫 번째 계산을 수행할 셀(이 경우 H2)을 클릭하고 공식을 입력하여 이 계산을 만듭니다. 

이 예제에서는 다음 공식을 사용합니다.

 

=SUM(E2+E12+E22)

 

이제 계산 내에서 셀 E2, E12 및 E22를 참조하여 이들을 결합했습니다. 

이것은 상대적인 참조입니다. 

 

셀 H2의 공식은 왼쪽으로 3열, 왼쪽으로 3열, 아래로 10열, 

왼쪽으로 3열, 아래로 20열인 셀을 각각 참조했습니다.


이제 같은 공식을 복사하여 다른 곳에 붙여넣는다면, 

붙여넣는 위치와 상대적으로 같은 위치에 있는 값들을 집어낼 수 있을 것입니다. 

엑셀의 AutoFill 기능을 사용하여 나머지 데이터를 동일한 상대 참조로 완성할 수도 있습니다.

 

여기 보시는 바와 같이 H11 셀의 공식은 다음과 같습니다.

 

=SUM(E11+E21+E31)

 

상대 참조가 원래 위치에서 9행 아래로 배치되기 때문입니다. 

이제 매달 공식을 입력하기보다는 한 번만 입력한 다음 

엑셀 워크시트에서 원하는 곳에 동일한 상대 참조를 적용하면 됩니다.

 

Excel에서 절대 참조를 사용하는 방법

이 예제에서 회사 회계사는 직원이 일한 시간 수와 시간당 고정 비율을

기반으로 직원에게 얼마를 지불해야 하는지 계산하려고 합니다.

 

첫 번째 합계를 계산할 셀(이 경우 E2)을 클릭하여 적절한 공식을 입력하기 시작합니다.

 

이 예제에서 회계사는 셀 A2의 시간당 비율에 D2의 Jacinda가 작업한 총 시간을 곱하려고 합니다.

=SUM(A2)

 

그러나 여기서 상대적인 참조를 사용하면 공식을 다른 직원의 합계에 적용할 때 

공식은 Raul의 경우 셀 A3, Lucy의 경우 A4 등을 참조하므로 원하는 결과를 얻을 수 없습니다.

 

따라서 엑셀이 계속해서 올바른 셀을 참조하고 있는지 확인해야 합니다. 

이 경우 A2입니다. 이렇게 하려면 참조의 각 부분 앞에 달러 기호를 삽입해야 합니다.

=SUM($A$2)

 

이제 다른 곳에서도 동일한 공식을 사용하면 A2가 항상 참조된다는 것을 엑셀에 알려줍니다.

달러 기호를 수동으로 입력하는 대신 셀 참조를 입력한 후 

F4(키보드에 따라 기능 키가 수행하는 작업을 배워야 할 수도 있음)를 

누르면 달러 기호가 자동으로 나타납니다.

 

이제 공식을 완성할 수 있습니다. 이 경우 다음과 같습니다.

=SUM($A$2*D2)

 

공식의 두 번째 부분(D2)이 절대적인 기준을 사용하지 않는다는 것을 주목하십시오. 

셀 A2에 각 직원의 시간을 곱하려고 하므로 열 D의 각 사람의 시간과 상대적이어야 합니다.

이제 A2 셀이 절대적으로 참조된다는 것을 알고 복사하여 붙여넣거나 

엑셀의 AutoFill 기능을 사용하여 다른 셀에 공식을 적용할 수 있습니다.

Excel에서 혼합 참조를 사용하는 방법

공식 내에서 행을 잠그되 열을 잠그지 않으면 안 되는 경우가 있거나

그 반대의 경우가 있을 수 있습니다.

 

예를 들어, 이 경우 회계사는 각 직원이 개별 세율을 기준으로

세금을 얼마나 내야 하는지 계산하려고 합니다.

 

따라서 E열(세율)의 값은 직원마다 다르므로 행이 아니라 연도에 대한 참조가 아닌 적응력이 있어야 합니다.

첫 번째 계산을 수행할 셀(이 경우 셀 G2)을 클릭하고 공식을 입력합니다. 

위의 예에서 회계사는 Arjun의 2021년 납부세액을 계산하는 것으로 시작하려고 하므로 다음 공식을 입력합니다.

=SUM(B2*E2)

 

이제 E열을 잠가야 합니다.

 

 공식이 수정되지 않은 상태에서 오른쪽으로 AutoFill하면 Arjun의 

2022년 세금 납부액을 정확하게 계산하지 못할 것입니다.

 

 (B2 셀을 잠그지 않기 때문에) 공식은 정확한 연간 소득 합계를 정확하게 

계산하지만 공식은 F2 셀도 참조할 것입니다. 

 

이렇게 하려면 공식의 열 E를 참조하기 전에 달러 기호를 추가합니다.

 

=SUM(B2*$E2)

 

우리는 여기서 절대적인 참조를 사용하고 싶지 않습니다. 

왜냐하면 그것은 우리가 다른 직원들에게 지불해야 

할 세금을 계산할 때 각 직원의 개별 요금 대신 Arjun의 세율을 사용한다는 것을 의미하기 때문입니다.

 

 본질적으로 열 E만 잠근다는 것은 세율은 그대로 유지하되 

사람마다 다른 세율에 상대적으로 조정할 수 있음을 의미합니다.

 

이제 AutoFill 기능을 사용하여 Arjun의 세금 납부 총액을 완성할 수 있습니다. 

열 E는 계산에 잠겨 있으며 각 연도의 총액은 공식이 배치된 위치에 따라 상대적으로 이동합니다.

 

열 E를 잠그고 상대적인 행을 공식 내에 남겨두었기 때문에 

개별 세율을 기준으로 나머지 직원의 합계를 자신 있게 작성할 수 있습니다. 

 

결과 내의 임의의 셀을 클릭하고 공식 막대의 공식을 참조하여 

올바른 셀이 참조되었는지 확인할 수 있습니다.

 

Excel에서 서로 다른 유형의 셀 참조를 전환하는 방법

엑셀 시트에서 상대 참조, 절대 참조 및 혼합 참조를 쉽게 전환하려면

매번 달러 기호를 입력할 필요가 없습니다.

 

대신 수정할 셀을 클릭한 다음 공식 막대에서 전환할 공식 부분을 클릭합니다

(기준 바로 전, 기준 중간 또는 기준이 작동한 후 바로 클릭).

이 경우, 우리는 셀 G11에 대한 셀 참조 유형 간에 전환하기를 원하므로, 

우리는 반드시 그 셀 참조 위 또는 옆에 있는 어딘가를 클릭해야 합니다.

그런 다음 F4를 누르면 셀 참조 유형이 변경됩니다.

먼저 절대 기준으로 전환됩니다.

 

$G$11

그런 다음 행이 잠긴 혼합 참조:

G$11

그런 다음 열이 잠긴 혼합 참조:

$G11

그리고 마지막으로 기본값인 상대 참조로 돌아갑니다.

G11

동일한 공식의 여러 참조를 동시에 전환하려면 공식의 관련 참조를 강조 표시하고 동일한 단계를 따릅니다.

 

달러 기호가 있는 것만으로도 엑셀의 셀 참조를 빠르고 정확하게 정리하는 데 도움이 될 수 있습니다.

 

 

2022.10.17 - 엑셀에서 텍스트를 대문자 혹은 소문자로 표시하는 함수

엑셀에서 텍스트를 대문자 혹은 소문자로 표시하는 함수

2023.05.21 - 엑셀 팁, IS함수로 True 또는 False 값을 쉽게 얻어보자

2023.07.06 - 엑셀에서 색상별로 정렬하는 방법에 대해 알아보자

엑셀에서 색상별로 정렬하는 방법에 대해 알아보자

2023.07.19 - 워드에서 표의 셀 간격을 조정하는 방법에 대해 알아보자

 

-