Excel 2016에서 자동 채우기를 사용하여 수식을 복사하는 방법

Excel 2016에서 단일 수식만 복사해야 하는 경우 자동 채우기 기능 또는 복사 및 붙여넣기 명령을 사용합니다. 이러한 유형의 수식 복사는 매우 일반적이지만 끌어서 놓기로 수행할 수 없습니다.

빠른 분석 도구의 합계 옵션을 잊지 마십시오. 이를 사용하여 플래시에서 데이터 테이블의 하단 또는 오른쪽 가장자리에 합계 행 또는 열을 생성할 수 있습니다. 표를 셀 범위로 선택하고 팔레트에서 요약이 표시되는 빠른 분석 버튼을 클릭하기만 하면 됩니다. 그런 다음 팔레트 시작 부분의 합계 버튼을 클릭하여 테이블 하단의 새 행에 있는 열의 합계를 계산하는 수식을 생성하거나 팔레트 끝에 있는 합계 버튼을 클릭하여 새 행의 합계를 생성하는 공식을 생성합니다. 오른쪽 끝에 있는 열.

자동 완성을 사용하여 한 수식을 셀 범위에 복사하는 방법은 다음과 같습니다. 이 그림에서 모든 회사가 포함된 Mother Goose Enterprises – 2016 Sales 워크시트를 볼 수 있지만 이번에는 E12 셀을 통해 복사되는 과정에 있는 12행에 월별 총계가 하나만 있습니다.

자동 채우기를 사용하여 수식을 셀 범위에 복사합니다.

다음 그림은 B12 셀의 채우기 핸들을 끌어 C12:E12 셀 범위(이 수식을 복사해야 함)를 선택한 후의 워크시트를 보여줍니다.

월별(및 분기별) 매출을 합산하는 공식을 복사한 후의 워크시트.

상대적으로 말하자면

그림은 셀의 수식이 셀 범위 C12:E12에 복사되고 B12 셀이 활성화된 후의 워크시트를 보여줍니다. Excel에서 수식 복사를 처리하는 방법을 확인하십시오. B12 셀의 원래 수식은 다음과 같습니다.

=합(B3:B11)

원본 수식이 셀 C12에 복사되면 Excel은 수식을 다음과 같이 약간 변경합니다.

=SUM(C3:C11)

행을 가로질러 왼쪽에서 오른쪽으로 복사했기 때문에 Excel은 열 참조를 조정하여 B에서 C로 변경합니다.

행 아래로 확장되는 셀 범위에 수식을 복사하면 Excel에서 각 복사본의 위치에 맞게 열 문자가 아닌 복사된 수식의 행 번호를 조정합니다. 예를 들어 Mother Goose Enterprises – 2016 Sales 워크시트의 E3 셀에는 다음 수식이 포함됩니다.

=합(B3:D3)

이 수식을 셀 E4에 복사하면 Excel에서 수식의 복사본을 다음과 같이 변경합니다.

=합(B4:D4)

Excel은 새 행 4 위치를 최신 상태로 유지하도록 행 참조를 조정합니다. Excel은 복사 방향을 기준으로 수식 복사본의 셀 참조를 조정하기 때문에 셀 참조를 상대 셀 참조 라고 합니다.

어떤 것들은 절대적입니다!

별도로 지정하지 않는 한 생성하는 모든 새 수식은 자연스럽게 상대 셀 참조를 포함합니다. 수식을 복사하는 대부분의 경우 셀 참조를 조정해야 하므로 이 배열을 다시 생각할 필요가 거의 없습니다. 그런 다음 때때로 복사본에서 셀 참조가 조정되는 시기와 방법을 제한해야 하는 예외를 접하게 됩니다.

이러한 예외 중 가장 일반적인 것 중 하나는 여러 값의 범위를 단일 값과 비교하려는 경우입니다. 이것은 각 부분이 전체에 대한 백분율을 계산하려고 할 때 가장 자주 발생합니다. 예를 들어 Mother Goose Enterprises – 2016 Sales 워크시트에서 각 월별 합계(셀 범위 B14:D14)가 셀 E12의 분기별 합계에 대한 백분율을 계산하는 수식을 만들고 복사할 때 이러한 상황이 발생합니다.

B14 셀에서 시작하는 Mother Goose Enterprises – 2016 Sales 워크시트의 14행에 이러한 수식을 입력한다고 가정합니다. 1월 매출 대 1분기 합계의 백분율을 계산하기 위한 셀 B14의 공식은 매우 간단합니다.

=B12/E12

이 수식은 B12 셀의 1월 판매 총액을 E12의 분기별 총액으로 나눕니다(어느 것이 더 쉬울까요?). 그러나 채우기 핸들을 한 셀 오른쪽으로 끌어 이 수식을 셀 C14에 복사하면 어떻게 되는지 보십시오.

=C12/F12

B12에서 C12로의 첫 번째 셀 참조 조정은 의사가 지시한 대로입니다. 그러나 두 번째 셀 참조를 E12에서 F12로 조정하는 것은 재앙입니다. C12 셀의 2월 매출이 E12의 1분기 매출 중 몇 퍼센트인지 계산하지 않을 뿐만 아니라 끔찍한 #DIV/0 중 하나로 끝납니다! C14 셀에 오류가 있습니다.

Excel에서 만드는 모든 복사본에서 수식의 셀 참조를 조정하지 못하게 하려면 셀 참조를 상대 참조에서 절대 참조로 변환합니다. Excel을 편집 모드(F2)로 전환한 후 기능 키 F4를 눌러 이 작업을 수행합니다. Excel에서는 열 문자와 행 번호 앞에 달러 기호를 배치하여 셀 참조를 절대 참조로 지정함을 나타냅니다. 예를 들어, 이 그림에서 B14 셀에는 C14:D14 셀 범위에 복사할 올바른 수식이 포함되어 있습니다.

절대 셀 참조를 사용하여 분기별 매출 대비 월별 매출 비율을 계산하는 공식을 복사합니다.

=B12/$E$12

채우기 핸들을 사용하여 이 수식을 범위 C14:D14에 복사하고 C14 셀을 선택한 후 워크시트를 확인합니다(다음 그림 참조). 수식 입력줄에 이 셀에 다음 수식이 포함되어 있음을 알 수 있습니다.

절대 셀 참조가 있는 수식을 복사한 후 워크시트.

=C12/$E$12

원래 수식에서 E12가 $E$12로 변경되었기 때문에 모든 사본에는 동일한 절대(변경되지 않음) 참조가 있습니다.

하나 이상의 셀 참조가 절대적이어야 하지만 모두 상대적인 위치에 있는 수식을 복사하여 복사하는 경우 원래 수식을 다음과 같이 편집합니다.

수식이 있는 셀을 두 번 클릭하거나 F2 키를 눌러 편집합니다.

절대값으로 변환할 참조의 어딘가에 삽입점을 놓습니다.

F4 키를 누릅니다.

편집이 끝나면 수식 입력줄에서 Enter 버튼을 클릭한 다음 채우기 핸들이 있는 엉망인 셀 범위에 수식을 복사합니다.

앞에서 설명한 대로 셀 참조를 절대 절대값으로 변경하려면 F4 키를 한 번만 누르십시오. F4 기능 키를 두 번째로 누르면 행 부분만 절대적이고 열 부분은 상대적인 소위 혼합 참조로 끝납니다 (E$12에서와 같이). 그런 다음 F4 키를 다시 누르면 Excel은 열 부분이 절대이고 행 부분이 상대적인 다른 유형의 혼합 참조를 제공합니다($E12에서와 같이). 계속해서 F4 키를 다시 누르면 Excel에서 셀 참조를 완전히 상대적으로 다시 변경합니다(E12에서와 같이).

시작한 곳으로 돌아간 후 계속 F4 키를 사용하여 동일한 셀 참조 변경 세트를 순환할 수 있습니다.

기능 키가 있는 물리적 키보드(예: 터치스크린 태블릿)에 액세스할 수 없는 장치에서 Excel 2016을 사용하는 경우 수식의 셀 주소를 상대 주소에서 절대 주소로 변환하거나 일부 형식의 혼합 주소 형식으로 변환하는 유일한 방법은 터치 키보드를 사용하여 수식 입력줄의 해당 셀 주소에서 열 문자 및/또는 행 번호 앞에 달러 기호를 추가합니다.