엑셀 테이블 데이터 재구성

Question

첨부된 이미지(현재 테이블)와 같은 형식의 테이블이 있습니다.

제가 가진 질문은 동일한 열에 다른 수준(이 경우 Continent-Nation-City)의 데이터가 포함되어 있다는 것입니다. 일반적으로 수동으로 열을 추가하고 필터와 간단한 수식을 사용하여 셀의 내용을 복사하면 두 번째 이미지(원하는 테이블)의 테이블을 얻을 수 있습니다.

이는 적은 양의 데이터에는 작동하지만 수천 개의 행이 있는 테이블의 경우 시간이 많이 걸릴 수 있습니다.

"현재 테이블"에서 "원하는 테이블"로 테이블을 재구성하는 보다 효율적인 또 다른 방법이 있습니까?

현재 테이블:

원하는 테이블:

Answer 1

이것은 Excel에서 제가 가장 좋아하는 트릭이며 이를 수행하는 방법은 다음과 같습니다.

여러 단계가 있는 것처럼 보이지만 매우 간단하며 알고 보면 모두 수행하는 데 보통 10~20초 정도 걸립니다. 그리고 매우 자주 필요하므로 미래를 위해 반드시 배워야 합니다.

1. 주황색만 기준으로 "위치" 열을 필터링합니다.

2. Continent에 대한 새 열의 모든 셀을 선택합니다(새 열의 마지막 셀을 더 빠르게 선택하고 Shift+ Control+를 누릅니다 Up).

3. 홈 -> 찾기 및 선택 -> 특수 항목으로 이동 -> 보이는 셀만 -> 확인

4. 이제 활성 선택을 유지하면서 간단한 수식을 입력해 보세요.

   =[@[Location]]
   

   아니면 그냥:

   =B2
   

5. Control+를 누르면 Enter이전에 선택한 모든 셀에 수식이 복사됩니다. 자, 보시다시피 사용하고 있는 표 범위이므로 수식이 다른 셀에 자동으로 채워지지 않도록 주의하세요. 그렇다면 Ctrl+Z를 한 번만 누르십시오.

6. 필터를 종료합니다.

7. 이제 새로운 "대륙" 열의 모든 셀을 선택하십시오.

8. 홈 -> 찾기 및 선택 -> 특수 항목으로 이동 -> 공백 -> 확인

9. 을 누른 =다음 + 를 누릅니다 . 이것이 수행하는 작업은 새 "대륙" 열의 빈 셀이 있는 모든 항목을 해당 모계 계층 대륙에 참조하는 것입니다.UpControlEnter

이제 새 열 "Nation"의 두 번째 수준 계층 구조에 대해 동일한 작업을 반복하여 이번에는 녹색을 필터링할 수 있습니다. 그리고 가지고 있는 계층 구조 수준만큼 반복하세요.

결국에는 더 이상 필요하지 않은 수식이 없도록 새 열의 모든 데이터를 값으로 복사해야 합니다.

마지막으로 이제 주황색과 녹색 행으로 다시 필터링하고 삭제할 수 있습니다. 그런 다음 "위치" 열의 이름을 "도시"로 바꿉니다. 그리고 "플랫 테이블"이라는 것이 있어야 하며 PivotTalbes 등을 사용하여 데이터를 사용한 추가 분석 작업에 가장 적합합니다.