비교하고 차이점을 빠르게 찾아야 하는 두 개의 테이블(예: 가격 목록의 이전 버전과 새 버전)이 있습니다.
새로운 가격 목록(날짜, 마늘 …)에 무언가가 추가되었고, 무언가가 사라졌으며(블랙베리, 라즈베리 …), 일부 상품(무화과, 멜론 …)의 가격이 변경되었음을 즉시 알 수 있습니다. 이러한 모든 변경 사항을 신속하게 찾아서 표시해야 합니다.
Excel의 모든 작업에는 거의 항상 하나 이상의 솔루션(보통 4-5개)이 있습니다. 우리 문제의 경우 다양한 접근 방식을 사용할 수 있습니다.
- 기능 VPR (조회) — 이전 가격 목록의 새 가격 목록에서 제품 이름을 찾고 새 가격 옆에 이전 가격을 표시한 다음 차이점을 파악합니다.
- 두 목록을 하나로 병합한 다음 이를 기반으로 피벗 테이블을 작성하면 차이점이 명확하게 표시됩니다.
- Excel용 파워 쿼리 추가 기능 사용
모두 순서대로 가져갑시다.
방법 1. VLOOKUP 함수로 테이블 비교
이 멋진 기능에 완전히 익숙하지 않은 경우 먼저 여기를 살펴보고 이에 대한 비디오 자습서를 읽거나 시청하십시오. 몇 년의 생명을 구하십시오.
일반적으로 이 함수는 일부 공통 매개변수를 일치시켜 한 테이블에서 다른 테이블로 데이터를 가져오는 데 사용됩니다. 이 경우 이전 가격을 새 가격으로 푸시하는 데 사용합니다.
#N/A 오류가 발생한 제품은 이전 목록에 없습니다. 즉, 추가되었습니다. 가격 변화도 명확하게 보입니다.
장점 이 방법은 간단하고 명확하며 "장르의 고전"입니다. 모든 버전의 Excel에서 작동합니다.
단점 도 있습니다. 새 가격 목록에 추가된 제품을 검색하려면 반대 방향으로 동일한 절차를 수행해야 합니다. 즉, VLOOKUP을 사용하여 새 가격을 이전 가격으로 끌어올립니다. 내일 테이블의 크기가 변경되면 공식을 조정해야 합니다. 글쎄, 그리고 정말 큰 테이블(> 100 행)에서는 이 모든 행복이 상당히 느려질 것입니다.
방법 2: 피벗을 사용하여 테이블 비교
나중에 어떤 목록에서 어떤 행을 이해할 수 있도록 가격 목록 이름이 있는 열을 추가하여 테이블을 다른 테이블 아래에 복사해 보겠습니다.
이제 생성된 테이블을 기반으로 다음을 통해 요약을 생성합니다. 삽입 – 피벗 테이블 (삽입 - 피벗 테이블). 필드를 던지자 프로덕트 선의 영역으로, 필드 가격 열 영역 및 필드로 ЦENA 범위로:
보시다시피 피벗 테이블은 이전 및 새 가격 목록(반복 없음!)에서 모든 제품의 일반 목록을 자동으로 생성하고 제품을 알파벳순으로 정렬합니다. 추가된 제품(이전 가격이 없음), 제거된 제품(새로운 가격이 없음) 및 가격 변동(있는 경우)을 명확하게 볼 수 있습니다.
이러한 테이블의 총계는 의미가 없으며 탭에서 비활성화할 수 있습니다. 생성자 – 총합계 – 행과 열에 대해 비활성화 (디자인 — 총계).
가격이 변경되면(상품 수량은 변경되지 않음!) 생성된 요약을 마우스 오른쪽 버튼으로 클릭하여 간단히 업데이트하면 됩니다. 새로 고침.
장점: 이 접근 방식은 VLOOKUP보다 큰 테이블에서 훨씬 더 빠릅니다.
단점: 서로 수동으로 데이터를 복사하고 가격 목록의 이름이 있는 열을 추가해야 합니다. 테이블의 크기가 변경되면 모든 작업을 처음부터 다시 해야 합니다.
방법 3: 파워 쿼리로 테이블 비교
파워 쿼리는 거의 모든 소스에서 Excel로 데이터를 로드한 다음 원하는 방식으로 이 데이터를 변환할 수 있는 Microsoft Excel용 무료 추가 기능입니다. Excel 2016에서 이 추가 기능은 기본적으로 탭에 이미 내장되어 있습니다. Data (데이터), Excel 2010-2013의 경우 Microsoft 웹 사이트에서 별도로 다운로드하여 설치해야 합니다. 새 탭 가져오기 파워 쿼리.
가격 목록을 파워 쿼리에 로드하기 전에 먼저 스마트 테이블로 변환해야 합니다. 이렇게하려면 데이터가있는 범위를 선택하고 키보드의 조합을 누르십시오 Ctrl 키+T 또는 리본에서 탭을 선택합니다. 홈 – 테이블 형식 (홈 — 표 형식). 생성된 테이블의 이름은 탭에서 수정할 수 있습니다. 건설자 (기준은 생략하겠습니다 표 1 и 표 2, 기본적으로 가져옴).
버튼을 사용하여 파워 쿼리에서 이전 가격 로드 테이블/레인지에서 (표/범위에서) 탭에서 Data (데이트) 또는 탭에서 파워 쿼리 (엑셀 버전에 따라 다름). 로드 후 명령을 사용하여 파워 쿼리에서 Excel로 다시 돌아갑니다. 닫기 및 로드 – 닫기 및 로드… (닫기 및 로드 — 닫기 및 로드...):
... 표시되는 창에서 다음을 선택합니다. 연결만 만드세요 (연결 전용).
새 가격 목록에 대해 동일한 작업을 반복합니다.
이제 이전 두 데이터의 데이터를 결합하고 비교할 세 번째 쿼리를 만들어 보겠습니다. 이렇게 하려면 탭의 Excel에서 선택하십시오. 데이터 – 데이터 가져오기 – 요청 결합 – 결합 (데이터 — 데이터 가져오기 — 쿼리 병합 — 병합) 버튼을 누르거나 결합 (병합) 탭 파워 쿼리.
조인 창의 드롭다운 목록에서 테이블을 선택하고 상품 이름이 포함된 열을 선택한 다음 하단에서 조인 방법을 설정합니다. 완전한 외부 (풀 아우터):
클릭 후 OK 세 개의 열로 구성된 테이블이 나타나야 합니다. 여기서 세 번째 열에서는 헤더의 이중 화살표를 사용하여 중첩 테이블의 내용을 확장해야 합니다.
결과적으로 두 테이블의 데이터 병합을 얻습니다.
물론 더 이해하기 쉬운 항목을 두 번 클릭하여 헤더의 열 이름을 바꾸는 것이 좋습니다.
그리고 지금 가장 흥미로운. 탭으로 이동 열 추가 (열 추가) 버튼을 클릭하십시오. 조건부 열 (조건부 열). 그런 다음 열리는 창에서 해당 출력 값과 함께 여러 테스트 조건을 입력합니다.
클릭하는 것이 남아 있습니다. OK 동일한 버튼을 사용하여 결과 보고서를 Excel에 업로드 닫고 다운로드 (닫기 및 로드) 탭 홈 (집):
아름다움.
또한 향후 가격 목록에 변경 사항이 발생하면(라인 추가 또는 삭제, 가격 변경 등) 키보드 단축키로 요청을 업데이트하는 것으로 충분합니다. Ctrl 키+다른+F5 또는 버튼으로 모두 새로고침 (모두 새로 고침) 탭 Data (데이트).
장점: 아마도 가장 아름답고 편리한 방법일 것입니다. 큰 테이블과 함께 스마트하게 작동합니다. 테이블 크기를 조정할 때 수동 편집이 필요하지 않습니다.
단점: 파워 쿼리 추가 기능(Excel 2010-2013) 또는 Excel 2016이 설치되어 있어야 합니다. 원본 데이터의 열 이름을 변경해서는 안 됩니다. 그렇지 않으면 "Column such as not found!" 오류가 발생합니다. 쿼리를 업데이트하려고 할 때.
- 파워 쿼리를 사용하여 지정된 폴더의 모든 Excel 파일에서 데이터를 수집하는 방법
- Excel에서 두 목록 간의 일치 항목을 찾는 방법
- 중복되지 않은 두 목록 병합