Excel-하나의 셀에서 값을 피벗합니다 (쉼표로 구분 된 값으로)
두 개의 데이터 열이 있습니다.
공급 업체 1 | 제품 1 공급 업체 1 | 제품 2 공급 업체 1 | 제품 4 공급 업체 1 | 제품 7 공급 업체 2 | 제품 3 공급 업체 2 | 제품 5
공급 업체를 중심으로 피봇 팅하고 쉼표로 구분 된 하나의 단일 셀에 제품 목록을 제공하려고합니다. 예 :
공급 업체 1 | 제품 1, 제품 2, 제품 4, 제품 7 공급자 2 | 제품 3, 제품 5
약 1000 개의 공급 업체가 있으며 0 <제품 <= 10입니다.
현재 해결 방법은 피벗 테이블을 사용하고 CSV 등으로 저장하는 것과 관련이 있으며 매우 지저분합니다. 비 VBA 솔루션은 놀랍습니다.
답변
다음은 몇 가지 수식 만 사용하는 VBA가 아닌 피벗 테이블 솔루션입니다.
-
먼저, “텍스트-컬럼”을 사용하여 “파이프”구분 기호 (수직선)의 데이터를 2 개의 열로 분할했습니다. “공급자”열 및 “제품”열. 그것들은 각각 열 A와 B에 들어갑니다. (귀하의 게시물에 하나의 열로 결합 된 것으로 보이므로 먼저 분리했습니다.이 작업을 수행 할 필요는 없습니다.)
-
“연결”열로 명명 된 C 열에서 C2 셀에서 시작하여 아래로 완전히 복사하는이 수식을 사용했습니다. = IF (A2 = A1, C1 & “,”& B2, A2 & “|”& B2)
-
D 열에서 “SupplierChangesAtNextLine?”으로 명명했습니다. 이 공식을 사용했습니다 (D2에서 시작하여 끝까지 복사). = IF (A2 = A3, “”, “Changed”)
-
이제 “변경된”값만 D 열에서 필터링 할 수 있습니다.
좋은 사냥!
답변
C1 C2 C3 C4
a 1 1
a 2 2,1
a 3 3,2,1
a 5 5,3,2,1
a 3 3,5,3,2,1 New
b 11 11
b 13 13,11
b 11 11,13,11
b 14 14,11,13,11 New
c 22 22
c 24 24,22 New
f 25 25
f 11 11,25
f 10 10,11,25 New
- col3 (평균 C3)에서 C2 셀을 시작하는 공식 = IF (A2 = A1, B2 & “,”& C1, B2)를 사용하여 아래로 끕니다.
- C4에서 (컬럼 4를 의미) 셀 D2를 시작하는 공식 = IF (A2 = A3, “”, “changed”)를 사용하고 아래로 끕니다.
- C4의 새로운 기능을 필터링하면 (col4 의미) 원하는 결과를 얻을 수 있습니다.
답변
C 열을 카운터로 추가 * : = COUNTIF (A $ 2 : A $ 528, A2)
D를 증분으로 추가 * = IF (A2 = A1, D1 + 1, 1)
연결할 E를 추가 * : = IF (A1 = A2, E1 & “,”& B2, B2)
마지막 연결 만 유지하려면 F를 추가하십시오 . = IF (AND (C2 = D2, E4 <> “”,, E4, “”)
참고 셀 2를 추가하고 아래로 당기거나 두 번 클릭하십시오. 열의 모든 셀에 수식을 적용하려면 오른쪽 모서리)
다른 시트에 값으로 복사하여 붙여 넣기, 내림차순으로 정렬, 나머지 삭제