메뉴
실무 위키›응용 공식›엑셀 실시간 행열 전환 공식

엑셀 실시간 행열 전환 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

OFFSET 함수 또는 TRANSPOSE 함수로 원본 범위의 행과 열을 바꿔 출력하는 공식입니다. 원본 값을 수정하면 전환된 결과에도 실시간 반영됩니다.

예제파일 다운로드
엑셀 실시간 행열 전환 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01OFFSET 함수 채우기 공식

=OFFSET ( 시작셀, COLUMNS ( $A$1:A1 ) - 1, ROWS ( $A$1:A1 ) - 1 )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
시작셀필수행과 열을 바꿀 원본 범위의 왼쪽 위 셀입니다. 수식을 가로와 세로로 채울 때 위치가 바뀌지 않도록 절대참조로 고정합니다. 값만 참조하며 셀 서식은 전환하지 않습니다.
G2=OFFSET($A$2,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)
H2=OFFSET($A$2,COLUMNS($A$1:B1)-1,ROWS($A$1:B1)-1)
A
B
C
D
E
F
G
H
I
1
원본 A
원본 B
원본 C
전환 1
전환 2
2
상품A
상품B
상품C
상품A
120
3
120
85
140
상품B
85
4
상품C
140
5
G2:H4에서 오른쪽 이동 횟수를 원본의 아래쪽 이동으로, 아래쪽 이동 횟수를 원본의 오른쪽 이동으로 바꿔 A2:C3의 2행×3열 값을 3행×2열로 반환합니다.

02TRANSPOSE 함수 스필 공식 (엑셀 2021 이후)

=TRANSPOSE ( 범위 )
인수 설명 자세히 보기
인수구분설명
범위필수행과 열 방향을 바꿀 원본 전체 범위입니다. 엑셀 2021 이후 버전은 시작 셀 한 곳에만 입력하면 결과가 자동으로 펼쳐집니다. 이전 버전은 출력 범위를 먼저 선택한 뒤 Ctrl + Shift + Enter로 입력합니다. 값만 반환하며 셀 서식은 바뀌지 않습니다.
E2=TRANSPOSE(A2:C3)
A
B
C
D
E
F
G
H
I
1
원본 A
원본 B
원본 C
스필 시작
스필 확장
전환 1
전환 2
2
상품A
상품B
상품C
상품A
상품A
120
3
120
85
140
상품B
85
4
상품C
140
5

수식을 입력한 셀자동으로 채워진 범위

E2 셀의 TRANSPOSE 함수가 A2:C3의 2행×3열 값을 E2:F4에 스필합니다. G2:H4는 웹 시트에서 전체 결과를 보여주는 정적 미리보기입니다.
이 공식이 사용하는 함수
OFFSET 함수 COLUMNS 함수 ROWS 함수 TRANSPOSE 함수

동작 원리

01OFFSET 함수 채우기 공식

COLUMNS 함수와 ROWS 함수가 결과 셀의 가로·세로 이동 횟수를 계산하고 OFFSET 함수는 두 횟수를 서로 바꿔 원본 값을 가져옵니다.

OFFSET 함수 채우기 공식의 예제 시트에서 G3 셀이 상품B를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

COLUMNS 함수가 아래쪽 이동 횟수를 계산합니다

G3 셀은 결과 범위의 첫 열에 있으므로 COLUMNS 함수가 1을 세고 1을 뺀 0을 반환합니다. 이 값은 OFFSET 함수의 아래쪽 이동 횟수로 사용됩니다.

=COLUMNS ( $A$1:A2 ) - 1 결과 범위의 첫 열이므로 아래쪽 이동 횟수는 0입니다.
= 0 OFFSET 함수의 행 이동 인수입니다.
2

ROWS 함수가 오른쪽 이동 횟수를 계산합니다

G3 셀은 결과 범위의 두 번째 행에 있으므로 ROWS 함수가 2를 세고 1을 뺀 1을 반환합니다. 이 값은 OFFSET 함수의 오른쪽 이동 횟수로 사용됩니다.

=ROWS ( $A$1:A2 ) - 1 결과 범위의 두 번째 행이므로 오른쪽 이동 횟수는 1입니다.
= 1 OFFSET 함수의 열 이동 인수입니다.
3

OFFSET 함수가 교차 위치의 값을 가져옵니다

$A$2에서 아래로 0칸, 오른쪽으로 1칸 이동해 B2 셀의 상품B를 반환합니다. 가로 채우기 횟수는 원본의 행 이동으로, 세로 채우기 횟수는 원본의 열 이동으로 바뀌어 행과 열이 전환됩니다. OFFSET 함수는 휘발성 함수이므로 넓은 범위에 많이 채우면 재계산 부담이 커질 수 있습니다.

=OFFSET ( $A$2, 0, 1 ) A2 셀에서 B2 셀로 이동합니다.
= "상품B" G3 셀의 결과입니다.
댓글 2
5 (2개 평가)
공수래
공수래 2021.11.27 10:17
감사합니다
강민준🤗
강민준🤗 2024.08.11 20:18
좋은 강의 감사합니다🙇‍♂️
스크랩 완료