Excel: 열/행/행렬 간의 데이터 변환 공식

Excel: 열/행/행렬 간의 데이터 변환 공식

열에 있는 데이터를 행렬이나 행으로 변환하는 공식이 있습니까?그리고 다른 조합으로 변환하시겠습니까?

더 복잡한 경우는 어떨까요? 너비 W의 행렬을 너비 N*W로 재구성하는 것입니다.

몇 가지 유사하거나 관련된 질문이 있습니다.*.로 표시된 답변 중 일부에 답했습니다. 새로운 유사한(또는 동등한) 질문이 추가됨에 따라 이 목록을 계속 업데이트합니다.

데이터 형식 지정: 열에서 행으로 *

내용을 1열에서 3열로 이동 *

EXCEL의 조건에 따라 한 열을 두 열로 분할하는 방법 *

매크로를 작성하여 3개의 열을 하나의 행으로 바꾸기

Excel VBA를 문자로 대체

엑셀의 수학적 전치

Excel의 각 항목에 대해 "매트릭스" 테이블을 한 줄로 변환하는 방법

데이터 행이 여러 개인 열을 Excel의 열이 여러 개인 행으로 변환합니다.

VBA를 사용하여 Excel의 데이터 모양을 변경하는 방법 *

Excel을 사용하여 성별로 수평 정렬된 세 개의 열을 여섯 개로 정렬 *

한 열에 있는 데이터를 엑셀의 더 많은 열로 나눕니다.

여러 열의 데이터를 단일 행으로 이동 *

일부 답변은 좀 더 포괄적인 것으로 "업그레이드 가능"한 것으로 보입니다.그게 가능한가요?

변환할 샘플 형식은 다음과 같습니다.

기둥.

1
2
3
4
5
6
7
...

배를 젓다

1   2   3   4   5   6   7   ...

행렬(여기에 4개의 열 범위 포함)

1   2   3   4
5   6   7   8
...

이 아이디어는 위에 나열된 질문에 대한 사소한 적응에도 사용될 수 있는 무언가를 여기에 제공하는 것이며, 이는 향후 관련 질문에 대한 참조로도 사용될 수 있습니다.

해야 할 은 사해야하필기같다습다니음입니다.INDEX또는OFFSET각각의 장단점은 그림을 참조하여 명시적인 예 후에 제시될 것입니다.정의된 이름을 가진 여러 범위가 표시됩니다(다음의 기울임꼴로 표시됨).정의된 모든 이름은 해당 셀에 대한 직접 절대 참조로 대체될 수 있습니다.

열에서 행렬로

범위(C1)는 열 수를 나타냅니다.그러면 행렬_data_top_left(여기서 D1)는 다음을 포함합니다.

=INDEX(col_data,(ROW()-ROW(matrix_data_top_left))*span+(COLUMN()-COLUMN(matrix_data_top_left)+1),1)

그러면 나머지 matrix_data에 복사됩니다.결과 공식이 col_data 외부의 셀(A1:A16)을 참조하므로 D5로 복사하면 오류가 발생합니다.행렬_data2_top_left(I1)에서도 다음과 같은 결과가 얻어집니다.

=OFFSET(col_data_top,(ROW()-ROW(matrix_data2_top_left))*span+(COLUMN()-COLUMN(matrix_data2_top_left)),0)

matrix_data2에 유사한 내용을 복사합니다.I5에도 복사하면 다음이 반환됩니다.0실수가 아닙니다.

OFFSET기본 참조(col_data_top)로 하나의 셀만 사용해야 한다는 장점이 있으므로 추가 데이터로 소스 데이터 범위를 확장하면 수식에서 소스 데이터 범위를 재정의할 필요가 없으며 확장된 대상 범위로 복사하면 됩니다.반면에, 소스 데이터 범위를 확장하려면INDEX에서는 먼저 공식으로 업데이트한 다음(명시적으로 사용되는 경우 범위 변경) 확장된 대상 범위로 복사해야 합니다.정의된 이름을 사용하는 것이 col_data를 재정의하는 것으로 충분하기 때문에 이 목적에 더 유용합니다(그리고 대상 범위를 확장한 후에 수행할 수 있습니다).이 같은 특성 때문에INDEX소스 범위에 대한 일종의 자동 경계 검사를 제공합니다.OFFSET하지 않다.

행렬을 열로

col_data2_top 내용

=INDEX(matrix_data2,INT((ROW()-ROW(col_data2_top))/span)+1,MOD(ROW()-ROW(col_data2_top),span)+1)

및 col_data3_top

=OFFSET(matrix_data2_top_left,INT((ROW()-ROW(col_data3_top))/span),MOD(ROW()-ROW(col_data3_top),span))

두 수식이 모두 아래쪽으로 복사됩니다.사이의 동일한 차이INDEX그리고.OFFSET존재하다.

행으로 행렬

부터OFFSET는 오류를 제공하지 않으며, 나머지 수식이 이를 사용합니다.적응:INDEX위에 표시된 선을 따라 이동하는 것은 쉽습니다.row_data_left에 포함된 내용

=OFFSET(matrix_data_top_left,INT((COLUMN()-COLUMN(row_data_left))/span),MOD(COLUMN()-COLUMN(row_data_left),span))

오른쪽으로 복사합니다.

행으로 열 이동

row_data2_left는 다음을 포함합니다.

=OFFSET(col_data_top,COLUMN()-COLUMN(row_data2_left),0)

다시 오른쪽으로 복사했습니다.

PS: 공식=TRANSPOSE(...는 이 경우에 사용할 수 있으며 배열 공식(++shiftenter 포함)으로 입력해야 합니다.그러나 배열 수식은 피하는 것이 바람직할 수 있습니다.

5/6. 행을 열/매트릭스로 이동

이 선을 따라 매우 쉽게 얻을 수 있습니다.예: col_data_top에 다음이 포함됩니다.

=OFFSET(row_data_left,0,ROW()-ROW(col_data_top))

그리고 아래로 복사합니다.

행렬 전치

matrix_data3(그림에 표시되지 않음)에서 matrix_data2의 전치를 얻으려면 matrix_data3_top_left만 사용하면 됩니다.

=OFFSET(matrix_data2_top_left,COLUMN()-COLUMN(matrix_data3_top_left),ROW()-ROW(matrix_data3_top_left))

적절한 목표 범위로 복사됩니다.

행렬 재구성

행렬을 N4 행과 M4 열(폭 4)이 있는 매트릭스_data4를 N5=N4/R 행과 M5=M4xR 열(폭 5)이 있는 매트릭스_data5로 재구성하려고 합니다(그림에 표시되지 않음).사용할 경우

=OFFSET(matrix_data4_top_left,(ROW()-ROW(matrix_data5_top_left))*rep5+INT((COLUMN()-COLUMN(matrix_data5_top_left))/width4),MOD((COLUMN()-COLUMN(matrix_data5_top_left)),width4))

이제 행렬을 N4 행과 M4 열(폭 4)이 있는 행렬_data4를 N6=N4xS 행과 M6=M4/S 열(폭 6)이 있는 행렬_data6로 더 좁은 행렬로 재구성하려고 합니다(그림에는 표시되지 않음).사용할 경우

=OFFSET(matrix_data4_top_left,INT((ROW()-ROW(matrix_data6_top_left))/split6),MOD((ROW()-ROW(matrix_data6_top_left)),split6)*width4+(COLUMN()-COLUMN(matrix_data6_top_left)))

언급URL : https://stackoverflow.com/questions/20800820/excel-formulas-for-converting-data-among-column-row-matrix