엑셀 절대참조와 행·열 반전 기술: INDIRECT와 TRANSPOSE 활용 방법

안녕하세요, 여러분. 엑셀에서 셀을 드래그할 때 원하는 대로 행과 열이 바뀌지 않아 고민이신가요? 특정 상황에서는 열이 바뀌고 행이 고정되길 바라는데, 엑셀의 기본 드래그 규칙은 아래 방향으로 드래그하면 행 번호가 증가하고, 오른쪽(또는 왼쪽) 방향으로 드래그하면 열 문자가 바뀝니다. 이러한 특성 때문에 “열이 변하고 행이 고정되도록” 드래그하려 해도, 단순히 절대참조($A$1) 등을 적용하는 것만으로는 해결되지 않습니다. 대신 INDIRECT, ADDRESS, ROW, COLUMN 같은 함수를 조합하거나, 행과 열을 통째로 뒤집고 싶다면 TRANSPOSE 함수를 활용해보세요. 지금부터 이 문제를 자세히 살펴보고, 해결책을 알아보겠습니다.

엑셀의 기본 드래그 동작과 절대참조의 원리

엑셀을 사용하다 보면, 셀에 간단한 수식을 입력한 뒤 셀 핸들을 드래그하여 여러 셀에 동일 패턴의 수식을 적용하는 경우가 많습니다. 이때 엑셀은 다음과 같이 동작합니다.

아래로 드래그하면 행 번호가 증가합니다. 예: =A1을 아래로 드래그하면 =A2, =A3 …
오른쪽(또는 왼쪽)으로 드래그하면 열 문자가 바뀝니다. 예: =A1을 오른쪽으로 드래그하면 =B1, =C1 …

그렇다면 절대참조($)는 어떤 역할을 할까요?

$A1과 같은 형태: 열은 고정하지만 행은 상대적으로 변화
A$1과 같은 형태: 행은 고정하지만 열은 상대적으로 변화
$A$1과 같은 형태: 열, 행 모두 고정 (완전 절대참조)
A1과 같은 형태: 열, 행 모두 상대 (완전 상대참조)

이 규칙에 따라, “아래로 드래그했을 때 열이 바뀌고 행이 고정”되게 만들려면(즉, ‘행 대신 열이 증가’하도록) 단순히 $ 기호만으로는 구현할 수 없습니다. 엑셀은 기본적으로 아래로 드래그 시 행 증가, 오른쪽으로 드래그 시 열 증가라는 틀을 벗어나지 않기 때문입니다. 따라서 $A$1 등만으로는 “드래그 방향에 따라 기본 동작이 뒤집히는” 효과를 낼 수 없습니다.

실제 예시와 문제 상황

예를 들어, 다음과 같은 상황을 가정해 봅시다.

원본 데이터가 Sheet1!B9 ~ D9 범위에 가로로 놓여 있고,
이를 세로로 늘어놓으며, 아래로 드래그하는 과정에서 열 인덱스가 증가하길 바람.

그런데 단순히 =$B$9, =$C$9, =$D$9와 같이 절대참조를 걸어놓고 아래로 드래그하면 =$B$9, =$B$9, … 로 그대로 복사되어 버립니다. 만약 $B$9에서 B만 상대, 9만 절대로 바꾼 =B$9 형태로 해보아도, 아래로 드래그하면 =B$9, =B$10 … 이렇게 행 번호만 바뀝니다. 이처럼 원하는 “아래로 드래그 시 열 증가” 패턴을 단순 $ 기호만으로 만들기는 어렵습니다.

절대참조가 무용지물일까?

“그렇다면 절대참조($)는 쓸모가 없는 걸까?”라고 의문이 생길 수 있습니다. 하지만 결코 그렇지 않습니다. $A$1 등은 “특정 셀이나 범위를 고정”해야 할 때 정말 유용합니다. 예를 들어, VLOOKUP의 범위가 고정되거나, 고정된 상수를 가진 셀을 여러 수식에서 불러올 때, 아니면 SUM 등의 함수에서 범위가 변하지 않도록 해야 할 때는 매우 편리합니다.
문제는 “드래그 방향을 아예 뒤집고 싶다”라는 특수한 요구사항에 대한 해결책일 뿐, 절대참조 자체가 가치가 없는 것은 아닙니다.

INDIRECT, ADDRESS, ROW, COLUMN 함수의 조합으로 해결하기

엑셀에서 기본적으로 제공하는 행·열 자동 증가 방식을 바꿀 수 없으므로, 대안을 찾기 위해서는 수식을 통해 필요한 셀 주소를 ‘간접’으로 생성해내야 합니다. 여기서 INDIRECT 함수가 유용합니다.

INDIRECT("텍스트 형태의 셀 주소")
→ 텍스트로 쓰여 있는 주소를 실제 셀 참조처럼 해석하여 반환합니다.

이때 셀 주소를 동적으로 만들어내기 위해 ADDRESS, ROW, COLUMN 등의 함수를 함께 사용할 수 있습니다. 간단히 함수들의 역할을 살펴봅시다.

함수	설명	예시
INDIRECT	텍스트를 실제 셀 참조로 해석	`INDIRECT("A1")` → A1 셀의 값
ADDRESS	지정한 행 번호와 열 번호를 엑셀 주소로 변환	`ADDRESS(1,2)` → \$B\$1
ROW	해당 셀의 행 번호 반환	`ROW(A1)` → 1
COLUMN	해당 셀의 열 번호 반환	`COLUMN(A1)` → 1

ADDRESS 함수는 세 번째, 네 번째 파라미터를 조정하여 $ 기호의 형태를 바꿀 수도 있습니다.

ADDRESS(row_num, column_num, [abs_num], [A1], [sheet_text])
abs_num 옵션값:
1 → 절대참조(\$A\$1 형식)
2 → 행은 절대, 열은 상대 (A\$1)
3 → 행은 상대, 열은 절대 (\$A1)
4 → 상대참조(A1)

또한 sheet_text 인수를 사용하면 시트 이름을 함께 포함한 참조 주소도 만들 수 있습니다.

구체적인 공식 예시

예를 들어, “아래로 드래그할 때 열이 증가하고, 행은 고정”되도록 수식을 설계해 봅시다. 셀 A1에 아래 수식을 입력하고, 아래로 드래그한다고 가정해 보겠습니다.

=INDIRECT(
  "Sheet1!" &
  TEXTBEFORE(ADDRESS(1, COLUMN($A1)+ROW($A1), 4), "1") &
  (COLUMN(A1)+8)
)

이게 어떻게 동작하는지 단계별로 살펴봅시다.

COLUMN($A1) → $A1은 열이 절대로 고정되어 있으므로, 어떤 셀로 옮겨가더라도 열은 A로 인식되고 결과적으로 1을 반환합니다.
ROW($A1) → $A1은 행이 절대로 고정되어 있으므로, 결과가 1로 고정됩니다.
COLUMN(A1) → 여기서는 A1을 상대참조합니다. 아래로 드래그하더라도 열 번호는 그대로 A(1)이지만, 혹시 다른 위치에 있다면 바뀔 수 있습니다.
ADDRESS(1, COLUMN($A1)+ROW($A1), 4) → ADDRESS(1, 1+1, 4) = ADDRESS(1, 2, 4) → "B1" (상대참조 모드 4 → B1 형태)

여기서 COLUMN($A1) + ROW($A1) = 1 + 1 = 2가 되어, 열 번호가 2 (즉 B)가 됩니다.

TEXTBEFORE(ADDRESS(...),"1") → "B1"에서 "1" 이전 문자인 "B"만 추출합니다.
(COLUMN(A1) + 8) → 이제 실제 열 번호를 계산하기 위해 8을 더할 수도, 다른 숫자를 더할 수도 있습니다. 예시로 8을 더했다면 COLUMN(A1)이 1이므로 1 + 8 = 9가 됩니다. 이는 행 번호로 활용될 수 있습니다.
"Sheet1!" & "B" & "9" → "Sheet1!B9"가 최종 문자열로 완성됩니다.
INDIRECT("Sheet1!B9") → 최종적으로 Sheet1의 B9 셀 값을 반환합니다.

이런 식으로 복잡해 보이지만, 드래그하는 방향에 따라 필요한 열과 행 번호를 조정해 원하는 방식으로 셀 주소를 동적으로 만들 수 있습니다. 수식을 여러 단계로 나누어서 작성하거나, 추가로 IF 문 등을 삽입해 조건별로 주소를 달리 생성할 수도 있습니다.

동적 행·열 제어를 위한 팁

행·열 고유 번호: ROW(A1)은 1, COLUMN(A1)도 1입니다. 아래로 드래그하면 A2, A3 …가 되면서 ROW(A2) = 2, ROW(A3) = 3 등으로 증가합니다.
상대참조와 절대참조 조합: ROW($A1), ROW(A$1) 등의 형태로 특정 부분만 고정할 수 있습니다. 드래그 패턴에 따라 적절히 골라 쓰시면 됩니다.
ADDRESS 함수의 abs_num 파라미터: 행·열에서 $ 기호를 어떻게 붙일지 유연하게 조정할 수 있으니, 필요한 형태(절대·상대 혼합)에 맞춰 활용하세요.

TRANSPOSE 함수로 한 번에 행·열 뒤집기

만약 현재 작업이 “가로로 놓인 데이터를 세로로, 혹은 그 반대로 일괄 변환”하는 목적이라면, TRANSPOSE 함수를 고려해보세요. TRANSPOSE는 선택한 범위를 한 번에 행·열을 뒤집어 배열 형태로 반환합니다.
예시로, Sheet1의 B9:D9 범위(가로 3칸)를 세로 3칸으로 뒤집고 싶다면, 원하는 빈 셀에 다음 수식을 입력합니다.

=TRANSPOSE(Sheet1!B9:D9)

그러면 동적 배열(Spill)로, 세로 방향으로 3칸이 자동 채워집니다. 엑셀의 최신 버전에서는 스필 범위를 편리하게 다룰 수 있습니다.

하지만 TRANSPOSE는 “드래그”라는 행위를 통해 특정 패턴을 자동 생성하기보다는 범위를 한꺼번에 뒤집어 배치하는 목적에 가깝습니다.

대규모 데이터의 행·열을 한 번에 전환할 때 유용
수식을 하나만 입력해도 여러 셀이 동시에 채워짐
다만, 사용자가 매 행마다 개별적으로 다른 조건을 적용해야 하는 경우라면, INDIRECT와 ADDRESS를 이용한 방식이 더 적합

TRANSPOSE 함수 vs. 수식 설계

항목	TRANSPOSE	INDIRECT + ADDRESS 조합
주된 목적	범위를 통으로 행↔열 전환	개별 셀을 원하는 규칙(드래그 패턴)에 따라 동적으로 조정
드래그 활용	따로 필요 없음(스필 기능 이용)	드래그 시 행·열 번호를 직접 계산
설정 난이도	간단	상대적으로 복잡
장점	빠르고 직관적, 대규모 데이터 변환 용이	원하는 대로 세부 제어 가능, 조건부 변환 구현 가능
단점	개별 셀의 맞춤 로직 구현이 어려움	수식이 길고 복잡해질 수 있음

절대참조($$ $ $$)의 가치와 활용

이처럼 단순히 “드래그 방향을 뒤집는다”는 요구에는 부합하지 않지만, 절대참조($)가 엑셀에서 매우 중요한 기능임은 부정할 수 없습니다. 다음과 같은 상황에서 절대참조는 필수적입니다.

VLOOKUP, HLOOKUP, INDEX/MATCH, XLOOKUP 등의 범위 고정

예: =VLOOKUP(A2, $A$1:$D$10, 2, FALSE)
표 범위가 바뀌지 않도록 $A$1:$D$10 형식으로 고정

상수 값을 저장한 셀 고정

예: =$B$1 * C2
B1에 중요한 상수가 있고, 모든 행에서 동일하게 참조해야 할 때

계산의 기준이 되는 셀이나 범위를 확실히 묶어야 할 때

다양한 부분합, 요약 수식에서 특정 셀만 고정해야 할 때

절대참조를 현명하게 활용하는 팁

빠른 범위 지정: 마우스로 범위를 드래그하면 자동으로 절대참조로 지정되는데, 필요에 따라 F4 키를 눌러 $ 형태를 바꿔보세요.
열 기준 고정 vs. 행 기준 고정: 어떤 값을 세로로만 복사해야 한다면 열을 고정($A1), 가로로만 복사해야 한다면 행을 고정(A$1)하는 식으로 활용하세요.
복잡한 계산식에서 참조 오류 방지: 혼합 참조($A1 또는 A$1)를 적절히 사용하면, 대규모 데이터에서 반복적인 작업을 줄일 수 있습니다.

실무 적용 시나리오

마지막으로, 실제 업무 상황에서 자주 마주치는 시나리오와 해결책을 요약해보겠습니다.

시나리오 1: 복잡한 행·열 전환 (매 행마다 다른 열을 가리키도록)

문제: 각 행에서 달라지는 열 인덱스를 가져와야 하는데, 단순 드래그만으로는 해결이 안 됨.
해결: ROW(), COLUMN(), ADDRESS(), INDIRECT() 조합으로 동적 참조.

=INDIRECT(
  ADDRESS(
    고정할_행번호 + ROW(A1) - 1,
    고정할_열번호 + (ROW(A1)*2), 
    4, 1, "Sheet1"
  )
)

위의 예시에서 ROW(A1)*2는 열 번호를 두 칸씩 증가시킨다는 가정. 마구잡이로 로직을 설정하는 것이 아니라, 얼마만큼 증가해야 하는지를 계산 로직으로 짜두면, 드래그만 하면 자동으로 필요한 위치의 셀을 참조하게 됩니다.

시나리오 2: 행·열 통째로 뒤집기(간단히 Transpose)

문제: 표 형태의 데이터를 가로로 쌓인 상태에서 세로 형태로 바꿔야 함.
해결: TRANSPOSE 함수로 한 번에 해결. 스필을 이용하면 별도의 드래그 작업 없이 자동으로 여러 셀에 값이 입력됨.

=TRANSPOSE(Sheet2!A1:Z1)

실무에서 대규모 데이터를 빠르게 전환해야 할 때 유용합니다.

시나리오 3: VLOOKUP 등에서 테이블 범위 고정

문제: VLOOKUP을 아래로 복사해가면서 테이블 범위가 계속 바뀌어 버림.
해결: $A$1:$D$10 식으로 절대참조. 이 경우, 드래그해도 테이블 범위는 고정이므로 참조 오류가 발생하지 않음.

=VLOOKUP(A2, $A$1:$D$10, 2, FALSE)

결론 및 요약

엑셀의 기본 드래그 동작(아래로 드래그하면 행 증가, 오른쪽으로 드래그하면 열 증가)은 수정할 수 없습니다. $ 기호를 붙인다고 해서 이 규칙을 바꾸는 것이 아닙니다.
“아래로 드래그 시 열이 증가하고 행은 고정” 같은 패턴을 만들려면, INDIRECT, ADDRESS, ROW, COLUMN 함수를 조합해 셀 주소를 간접적으로 생성하는 방식을 활용해야 합니다.
범위를 통째로 행·열을 뒤집고 싶다면 TRANSPOSE 함수를 사용하세요. 동적 배열(스필)로 간단하게 가로 데이터를 세로로, 또는 그 반대로 전환할 수 있습니다.
절대참조($)는 여전히 중요한 기능입니다. VLOOKUP 등 범위 고정, 고정 상수 참조, 상대·절대 혼합 등 다양한 상황에서 반드시 필요한 도구이므로 적절히 잘 활용하시길 바랍니다.

마무리하며

드래그 작업의 본질을 이해하고, 엑셀 함수들을 창의적으로 결합하면 데이터 참조 패턴을 얼마든지 자유롭게 구성할 수 있습니다. 처음에는 INDIRECT나 ADDRESS의 개념이 어려울 수 있지만, 한 번 익혀 두면 복잡한 데이터 작업도 순식간에 해결할 수 있습니다. 무엇보다 드래그 패턴을 바꿀 수 없어도, 우리가 원하는 셀 주소를 “직접” 만들어서 간접 참조할 수 있다는 점이 Excel 작업의 유연성과 확장성을 보여줍니다.
각자의 업무 환경에 맞게 이 노하우들을 적용해 보세요. 궁극적으로는 정확하고 빠른 작업 자동화와 실수 방지에 크게 도움이 될 것입니다.