Excel에서 아래/오른쪽으로 자동 채우기 시 참조를 전치하는 방법은 무엇입니까?

Excel에서 수식 및 자동 채우기를 사용할 때 기본 동작은 열 참조가 오른쪽으로 드래그될 때 수평으로 증가하고, 행 참조는 아래로 드래그될 때 수직으로 증가하는 것입니다. 그러나 때때로 참조를 전치해야 할 수도 있습니다 — 예를 들어, 세로 범위를 참조하지만 가로로 채우거나 그 반대의 경우입니다. 이 작업은 데이터 레이아웃을 재구성하거나 전치 방식으로 데이터를 상호 참조해야 하는 동적 보고서를 작성할 때 특히 유용합니다. 이 문서에서는 수식, VBA 매크로 및 Kutools와 같은 편리한 도구를 포함하여 이러한 전치된 자동 채우기를 달성하기 위한 다양한 실용적인 방법을 살펴보겠습니다. 일반적인 전치 자동 채우기 시나리오는 아래 삽화를 참조하십시오.

아래 또는 오른쪽으로 채울 때 참조 전치

Excel 수식 – 전치된 참조를 수동으로 구성하기 위해 INDEX 또는 OFFSET 사용

아래 또는 오른쪽으로 채울 때 참조 전치

행 또는 열을 채우면서 참조를 전치하려면 Excel의 TRANSPOSE 함수를 활용할 수 있습니다. 이 방법은 열에 배열된 데이터를 행으로 변환하거나 그 반대로 변환하려는 경우 적합하며, 자동 채우기 중에 수식이 참조 방향을 자동으로 조정됩니다.

전치된 참조를 적용하려는 범위를 선택하고 수식 표시줄에 다음 수식을 입력하세요: =TRANSPOSE(Sheet2!$B$1:B12) 그런 다음 Ctrl + Shift + Enter 를 동시에 누르세요 (Microsoft 365 / Excel 2021 이전 버전의 경우). 동적 배열을 지원하는 새 버전에서는 Enter 키를 누르기만 하면 충분합니다. 아래 스크린샷을 참조하세요:

이 수식에서 B1:B12 는 세로 범위에서 가로 배열로 전치하려는 범위를 나타냅니다. 실제 데이터 소스에 맞게 시트 이름과 특정 범위를 조정할 수 있습니다.
팁: 수식 오류를 방지하려면 전치된 데이터의 대상 범위 크기가 적절하게 조정되었는지 확인하세요 (예: 원본이 12개의 행을 가진 하나의 열인 경우, 대상 범위는 12개의 열을 가진 하나의 행이어야 함).

팁. 범위를 단일 열 또는 행 (또는 그 반대)로 여러 번 변환하거나 범위가 크고 수식이 복잡해질 경우, Kutools for Excel의 Transform Range 유틸리티를 사용하여 효율적으로 이를 수행할 수 있습니다. 이 도구는 간단한 인터페이스와 범위 변환을 위한 유연성을 제공하며, 수식 오류나 불일치 위험을 최소화합니다. 이 기능은 60일간 완전히 사용 가능합니다. 무료로 다운로드하여 사용해 보세요.

Excel 수식 – 전치된 참조를 수동으로 구성하기 위해 INDEX 또는 OFFSET 사용

때때로 TRANSPOSE와 같은 배열 수식을 사용하는 것이 선호되지 않거나, 특히 비연속 데이터로 작업하거나 이전 Excel 버전에서 작업할 때 참조를 전치하는 데 더 많은 제어가 필요할 수 있습니다. INDEX 및 OFFSET 함수를 사용하면 Ctrl + Shift + Enter 없이도 표준 채우기 작업을 통해 전치된 데이터를 직접 참조할 수 있습니다.

적용 시나리오: 예를 들어, A1 셀을 세로로 참조하여 B1 셀을 가로로 참조하거나 그 반대의 경우처럼 셀당 하나의 전치된 참조가 필요한 경우에 적합합니다. 배열 항목을 피하거나 복잡한 워크시트에서 동적 참조를 위해 유용합니다.

장점: 배열 입력이 필요 없으며, 수식을 드래그 앤 채우기하기 쉽습니다.
잠재적인 단점: 초기 수식 설정이 조금 더 복잡할 수 있으며, 행과 열 인덱스를 조정하는 데 주의 깊게 신경 써야 합니다.

전치된 참조에 INDEX를 사용하는 방법:

1. A1:A5 (세로)에 데이터가 있고 이를 B1:F1 (가로)에 전치하여 채우려고 한다고 가정합니다. B1 셀을 선택하고 다음 수식을 입력하세요:

=INDEX($A$1:$A$5,COLUMN(A1))

2. Enter 키를 누른 다음 수식을 F1까지 오른쪽으로 드래그합니다. 수식은 COLUMN(A1)을 사용하여 오른쪽으로 이동하면서 A1, A2, ..., A5를 B1:F1에 참조합니다.

전치된 참조에 OFFSET을 사용하는 방법:

1. 데이터가 B1:F1 (가로) 행에 있고 G1:G5 열에 세로로 채우려고 한다고 가정합니다. G1 셀에 다음을 입력하세요:

=OFFSET($B$1,0,ROW(A1)-1)

2. Enter 키를 누른 다음 수식을 G5까지 아래로 드래그합니다. 아래로 채우면서 ROW(A1)-1은 0부터 증가하여 OFFSET 함수의 열 인덱스를 변경하여 B1, C1, D1 등을 가져옵니다.

데이터 레이아웃에 맞게 수식 범위 또는 시작 위치를 조정하세요. 특히 수식을 복사하거나 이동할 때 올바른 참조를 확인하여 참조 불일치를 방지하세요.

문제 해결: #REF! 오류가 발생하면 참조 범위를 다시 확인하고 수식이 대상 데이터 집합 외부를 참조하려고 하지 않는지 확인하세요. 또한 큰 데이터 세트의 경우 이러한 수식으로 인해 워크북 계산 부하가 증가할 수 있습니다.

요약하자면, 수식, VBA 또는 Excel 추가 기능 중 어느 것을 사용할지 결정하는 것은 데이터 구조, 이 작업을 수행해야 하는 빈도 및 고급 Excel 기능에 대한 숙련도에 따라 달라집니다. 한 가지 방법이 요구 사항에 맞지 않는다면 위에서 설명한 다른 방법을 시도해 보세요. 지속적으로 수식이나 참조 문제가 발생하는 경우 수식에서 잠긴 셀 참조 ($ 기호 사용 여부)를 다시 확인하고 전치 시 올바른 방향성을 위해 필요에 따라 조정하세요.

Kutools AI로 엑셀의 마법을 풀다

• 스마트 실행: 셀 작업 수행, 데이터 분석 및 차트 생성 - 간단한 명령어로 모든 것을 처리합니다.
• 사용자 정의 수식: 작업을 간소화하기 위한 맞춤형 수식을 생성합니다.
• VBA 코딩: 손쉽게 VBA 코드를 작성하고 실행합니다.
• 수식 해석: 복잡한 수식도 쉽게 이해할 수 있습니다.
• 텍스트 번역: 스프레드시트 내 언어 장벽을 허물어 보세요.

AI 기반 도구로 엑셀 활용 능력을 강화하세요. 지금 다운로드하여 그 어느 때보다 높은 효율성을 경험해 보세요!