다음과 같은 데이터가 있습니다.
Id | Loc1 | Loc2 | Loc3 | Loc4
---+------+------+------+-----
1 | NY | CA | TX | IL
2 | WA | OR | NH | RI
그리고 이것을 이것을 다음과 같이 변환하고 싶습니다 :
Id | LocNum | Loc
---+--------+----
1 | 1 | NY
1 | 2 | CA
1 | 3 | TX
1 | 4 | IL
2 | 1 | WA
2 | 2 | OR
2 | 3 | NH
2 | 4 | RI
Excel 2007에서 가장 쉬운 방법은 무엇입니까?
답변
피벗 테이블을 사용하여이 작업을 수행 할 수 있습니다.
- “여러 통합 범위 피벗 테이블”을 만듭니다. (만과 피벗 테이블 마법사. 콜업에 ALT+ D, P엑셀 2007에서)
- “나만의 페이지 필드를 작성합니다”를 선택하십시오.
- 데이터를 선택하십시오.
- 피벗 테이블의 오른쪽 하단 에서 행 그랜드 와 열 그랜드 의 교차점에있는 총계 값을 두 번 클릭하십시오 .
피벗 테이블의 모든 데이터가 포함 된 새 시트가 원하는 방식으로 바뀝니다.
Datapig 기술 은 실제로 필요한 것보다 더 복잡한 단계별 지침 을 제공합니다. 그의 예제는 데이터 세트의 일부만 바꾸고 TextToColumns 와 결합 된 피벗 기술을 사용합니다 . 그러나 많은 그림이 있습니다.
피벗 테이블은 데이터를 그룹화합니다. 그룹화하지 않으려면 피벗 테이블을 복사하고 값으로 “특별 붙여 넣기”를 수행하는 것이 유일한 방법입니다. 그런 다음 http://www.contextures.com/xlDataEntry02.html 과 같은 기술로 공백을 채울 수 있습니다 .
답변
데이터가 Excel 피벗 테이블이 아니라 데이터 인 경우 간단한 VBA 코드를 사용하여 데이터를 “피벗 해제”할 수 있습니다. 코드는 두 가지 명명 된 범위 (소스 및 대상)에 따라 다릅니다. 소스는 피벗 해제하려는 데이터 (예 : 샘플의 NY-RI와 같은 열 / 행 헤더 제외)이고 대상은 결과를 배치 할 첫 번째 셀입니다.
Sub unPivot()
Dim oTarget As Range
Dim oSource As Range
Dim oCell As Range
Set oSource = Names("Source").RefersToRange
Set oTarget = Names("Target").RefersToRange
For Each oCell In oSource
If oCell.Value <> "" Then
oTarget.Activate
' get the column header
oTarget.Value = oCell.Offset(-(oCell.Row - oSource.Row + 1), 0).Text
' get the row header
oTarget.Offset(0, 1).Value = oCell.Offset(0, _
-(oCell.Column - oSource.Column + 1)).Text
' get the value
oTarget.Offset(0, 2).Value = oCell.Text
' move the target pointer to the next row
Set oTarget = oTarget.Offset(1, 0)
End If
Next
Beep
End Sub
답변
추가 기능을 구축하여 다양한 상황에 쉽게 적응할 수 있습니다. 여기에서 확인하십시오 : http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/
답변
Excel 2010에는 훌륭한 솔루션이 있습니다. 피벗 테이블로 조금만 연주하면됩니다.
다음 설정으로 데이터에서 피벗 테이블을 만듭니다.
- 소계 없음, 총합계 없음
- 보고서 레이아웃 : 표 형식, 모든 항목 레이블 반복
- 필요한 모든 열을 추가하고 변환하려는 열을 오른쪽에 유지
- 변환하려는 각 열에 대해 : 필드 설정 열기 – 레이아웃 및 인쇄 탭에서 : “개요 형식으로 항목 레이블 표시”를 선택하고 그 아래의 확인란을 모두 선택하십시오.
- 테이블을 별도의 위치에 복사하십시오 (값만).
- 원본 데이터에 빈 셀이 있으면 가장 오른쪽 열에서 빈 값을 필터링하고 해당 행을 삭제하십시오 (피벗 테이블에서 필터링하지 마십시오. 필요한대로 작동하지 않습니다!)
답변
내가 지금까지 생각해 낸 최고의 것은 이것입니다 :
Id LocNum Loc
---------------------------------
1 1 =INDEX(Data,A6,B6)
1 2 =INDEX(Data,A7,B7)
1 3 =INDEX(Data,A8,B8)
1 4 =INDEX(Data,A9,B9)
2 1 =INDEX(Data,A10,B10)
2 2 =INDEX(Data,A11,B11)
2 3 =INDEX(Data,A12,B12)
2 4 =INDEX(Data,A13,B13)
이것은 작동하지만 Id와 LocNum을 수동으로 생성해야합니다. 매크로 작성 외에 더 자동화 된 솔루션이있는 경우 별도의 답변으로 알려주십시오.
답변
데이터의 차원이 질문에 제공된 샘플과 같으면 OFFSET을 사용하는 다음 수식 세트가 필요한 결과를 제공해야합니다.
가정
1 | 뉴욕 | CA | TX | 일리노이
2 | WA | 또는 | NH | RI
A2 : E3 범위에있는 경우 다음을 입력하십시오.
= OFFSET ($ A $ 2, FLOOR ((ROW (A2) -ROW ($ A $ 2)) / 4,1), 0)
F2에서
= MOD (ROW (A2) -ROW ($ A $ 2), 4) +1
G2에서
= OFFSET ($ B $ 2, FLOOR ((ROW (B2) -ROW ($ B $ 2)) / 4,1), MOD (ROW (A2) -ROW ($ A $ 2), 4))
H2에서 말합니다.
그런 다음이 공식을 필요한만큼 복사하십시오.
이것은 내가 생각할 수있는 가장 쉽고 순수한 내장 수식 솔루션입니다.
답변
“loc”열을 얻은 것 같습니다 (첫 번째 답변에서 입증 됨). 이제 다른 두 열을 얻는 데 도움이 필요합니다.
첫 번째 옵션은 열에 첫 번째 (예 : 12) 행을 입력하고 아래로 드래그하는 것입니다.이 경우 Excel이 올바른 작업을 수행한다고 생각합니다 (이 컴퓨터에서는 확실하게 테스트 할 수는 없습니다).
그래도 작동하지 않거나 더 많은 프로그래머가 필요하면 row () 함수를 사용해보십시오. ID 열의 경우 “= Floor (row () / 4)”및 LocNum 열의 경우 “= mod (row (), 4) +1″과 같은 것입니다.