현재 39,000개의 행과 27개의 열로 구성된 데이터 세트가 있습니다. 첫 번째 열은 ID 번호입니다. 후속 열은 선거에 해당하며 행 1(셀 B1- AA1) 에는 선거 날짜가 있습니다 . 나머지 셀은 투표 방법에 해당하는 문자로 채워집니다(또는 투표하지 않은 경우 null). ID, 날짜, 투표 방법이라는 총 3개의 열이 있도록 이 테이블을 다시 정렬해야 합니다. 예를 들어:
현재 테이블:
ID 05/2005 11/2005 03/2006 (etc., for 27 total columns)
2345 P V
3789 A V
4321 V A V
7890 I
그리고 다음과 같이 표시되어야 합니다.
ID Date Voting Method
2345 05/2005 P
2345 11/2005 V
3789 11/2005 A
3789 03/2006 V
4321 05/2005 V
4321 11/2005 A
4321 03/2006 V
7890 11/2005 I
여기에는 VBA 스크립트가 필요할 것이라고 생각하고 온라인에서 찾은 스크립트를 함께 연결해 보았지만(VBA를 배운 적이 없기 때문에) 제대로 작동하지 않는 것 같습니다. 아마도 이 기능이 Excel에 이미 존재할까요?
지금까지 작업 중인 스크립트는 다음과 같습니다.
Sub NewLayout()
For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For j = 0 To 26
If Cells(i, 3 + j) <> vbNullString Then
intCount = intCount + 1
Cells(i, 1).Copy Destination:=Cells(intCount, 10)
Cells(i, 2).Copy Destination:=Cells(intCount, 11)
[I think this one is wrong. It needs to copy the column name,
not the cell value, if there is a cell value.]
Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
End If
Next j
Next i
End Sub
누구든지 제안 사항이 있으면 감사하겠습니다!
답변1
VBA를 사용하면 데이터가 A1부터 시작하는 Sheet1에 있고 Sheet2가 존재한다고 가정합니다.
Sub normalize()
Dim wks1 As Worksheet, wks2 As Worksheet
Dim iColCount As Integer, iRowCount As Integer
Dim i As Integer, j As Integer, k As Integer
Set wks1 = ActiveWorkbook.Sheets("Sheet1")
Set wks2 = ActiveWorkbook.Sheets("Sheet2")
iColCount = Application.WorksheetFunction.CountA(wks1.Range("1:1"))
iRowCount = Application.WorksheetFunction.CountA(wks1.Range("A:A"))
k = 1
For i = 2 To iRowCount
For j = 2 To iColCount
If wks1.Cells(i, j) <> vbNullString Then
wks1.Cells(i, 1).Copy Destination:=wks2.Cells(k, 1)
wks1.Cells(1, j).Copy Destination:=wks2.Cells(k, 2)
wks1.Cells(i, j).Copy Destination:=wks2.Cells(k, 3)
k = k + 1
End If
Next j
Next i
End Sub
Sheet2의 결과:
2345 5/2005 P
2345 11/2005 V
3789 11/2005 A
3789 3/2006 V
4321 5/2005 V
4321 11/2005 A
4321 3/2006 V
7890 11/2005 I
답변2
완전성을 기하기 위해 이제 이 작업을 수행하는 방법을 보여 드리겠습니다.없이VBA를 사용합니다. 다음 코드는 복잡하고 효과적으로 확장하기 어렵다는 점을 경고해야 합니다.
다음과 같은 초기 조건을 가정해 보겠습니다.
시트1
| A | B | C | D | …
---+----+---------+---------+---------+---
1 | ID | 05/2005 | 11/2005 | 03/2006 |
2 |2345| P | V | |
3 |3789| | A | V | …
4 |4321| V | A | V |
5 |7890| | I | |
… | …
시트2
| A | B | C |
---+----+------+---------------+
1 | ID | Date | Voting method |
2 | #1 | #2 | #3 |
이름이 지정된 셀#1다음과 같은 공식이 있습니다.
=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),1,,,"Sheet1"))
이 수식의 역할은 현재 셀을 Sheet1의 적절한 셀에 매핑하는 것입니다. 이는 함수의 도움으로 수행됩니다 FLOOR. 이 함수는 27개의 행이 전달될 때마다 1씩 증가하여 Sheet2의 행을 Sheet1의 콘텐츠와 올바르게 매핑합니다.
이 ADDRESS함수는 숫자 입력 및 시트 이름에서 셀에 대한 사용 가능한 참조를 구성하는 반면, 함수는 INDIRECT참조가 가리키는 콘텐츠를 검색합니다.
나머지 다른 함수도 동일한 추론을 따릅니다. 보조 함수를 사용하여 현재 셀의 좌표를 Sheet1의 올바른 셀에 매핑합니다.
이름이 지정된 셀의 경우#2:
=INDIRECT(ADDRESS(1,MOD(ROW(A2)-2,27)+2,,,"Sheet1"))
이 경우 MOD함수는 0에서 26 사이를 순차적으로 번갈아 가며 2에서 28 사이의 시퀀스(즉, 날짜가 있는 셀이 있는 위치)로 변환됩니다.
마지막으로, 이름이 지정된 셀에 대해#삼:
=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),MOD(ROW(A2)-2,27)+2,,,"Sheet1"))
이는 이전에 사용된 두 시퀀스를 혼합한 것입니다. 그 이유는 ID에 따라 내용이 다르기 때문입니다.#1호출됨) 및 날짜(셀의 일부가#2들어간다).
해당 함수를 올바른 셀에 입력한 후 아래로 드래그하면 결과가 분명하게 드러납니다. 한 가지 작은 문제는 바로 null 투표도 표시된다는 것입니다.
하지만 해당 결과를 필터링할 수 있습니다. 헤더(이 경우 ASheet2의 행)를 선택하고 데이터 > 필터 > 자동 필터(또는 사용 중인 Excel 버전의 이에 상응하는 항목)로 이동합니다. 투표 방법 열의 드롭다운을 클릭하고 0으로 구성된 결과를 제외하도록 정렬을 맞춤 설정하세요.
답변3
이를 위해 파워 쿼리 추가 기능을 사용하겠습니다. 코드나 복잡한 기능이 필요하지 않습니다. 처음부터 시작하면 이 작업을 완료하는 데 5분도 채 걸리지 않을 것입니다.
기존 Excel 테이블에서 쿼리를 시작할 수 있습니다. 그런 다음 Unpivot 명령을 사용하여 필요에 따라 데이터를 변환합니다.
http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx
Unpivot 구현의 장점은 추가된 모든 열(새 날짜)을 허용하고 쿼리 정의를 변경하지 않고 이를 처리한다는 것입니다.
필요에 따라 열 이름을 바꾸고 결과를 Excel 테이블로 전달합니다.