일반적인 질문에는 더 작은 하위 집합 목록과 일치하는 항목을 찾기 위해 대규모 Excel 2007 목록을 정렬하는 작업이 포함됩니다.
문제에 접근하는 방법에 대한 몇 가지 아이디어가 있지만 이러한 아이디어를 구현하기에는 기술적인 정교함이 부족합니다. 질문을 더 명확하게 하기 위해 구체적인 사용 사례 요구 사항을 간략하게 설명하겠습니다.
구체적인 예:
나는 내 판매 지역(약 1000개의 고객 계정)에 대해 관리하는 회사 이름의 마스터 목록을 가지고 있습니다. 매주 우리 회사는 미국의 모든 판매 지역(광산 및 기타 수백 개의 지역)에서 거래된 모든 비즈니스 목록을 게시합니다. 거래 로그는 10,000줄 이상이므로 내 계정과 관련된 거래를 찾기 위해 눈으로 스캔하는 것은 거의 불가능합니다.
현재 부적절한 해결책은 내 계정 목록을 노란색으로 강조 표시하고 강조 표시된 목록을 복사한 다음 강조 표시된 목록을 주간 거래 로그 하단에 붙여넣고 AZ를 정렬한 다음 수동으로 스크롤하여 강조 표시된 항목을 찾는 것입니다. 거래 로그에 내 계정 중 하나가 포함된 경우 거래 로그 항목은 내가 삽입한 강조 표시된 항목 바로 위 또는 아래에 표시됩니다. 이 방법은 효과적이지만 시간이 많이 걸립니다.
Excel에서 중복을 제거하는 방법을 알고 있습니다. 중복을 제외한 모든 것을 제거하는 방법이 있습니까? 이렇게 하면 목록을 시각적으로 더 쉽게 검색할 수 있습니다.
데이터 불일치로 인해 간단한 매크로, 필터 또는 "중복 항목 찾기" 버튼의 사용이 제한되었기 때문에 또 다른 문제가 남아 있습니다. 트랜잭션 로그 이름의 철자는 마스터 목록과 약간 다른 경우가 많습니다.
예: Acme Widget Company, Inc.; Acme 위젯 Inc; 애크미 위젯; 예: 미국 핸드볼 협회; 미국 핸드볼 조직; 미국 핸드볼; USHO
정확하지 않은 항목을 일치시키기 위해 퍼지 논리를 사용할 수 있는 일부 타사 앱이 있다는 것을 알고 있습니다. 하지만 내 기업 컴퓨터에서는 플러그인을 실행할 수 없습니다. (아주 특별한 경우가 아니라면...)
공백과 구두점을 제거하여 트랜잭션 로그를 '정규화'할 수 있는 매크로가 있습니까? 처음 X개의 문자 수와 일치할 수 있는 매크로가 있습니까(문자가 많을수록 정확도가 높아지지만 거의 중복된 항목이 누락될 가능성이 커짐...)? 결과 '일치' 목록을 출력하거나 필터링할 수 있는 매크로가 있습니까?
그 작업이 너무 복잡하다면 훨씬 간단한 아이디어가 있습니다. 강조 표시된 계정 목록을 트랜잭션 로그에 병합한 후 강조 표시된 항목 위 또는 아래에 5줄 미만의 다른 모든 트랜잭션 로그 줄을 숨길 수 있으면 좋을 것입니다. 이는 비표준 철자법에 대해 어느 정도 유연성을 허용하지만 목록을 통한 육안 검사 작업을 크게 단순화합니다.
이러한 아이디어 또는 완전히 다른 접근 방식을 구현하는 방법에 대한 의견을 주시면 감사하겠습니다. 나는 이 질문에 대한 일반적인 대답이 내가 설명한 좁은 사용 사례를 넘어 다른 사람들에게도 가치가 있을 것이라고 생각합니다.
감사해요!
답변1
여기에 답변해야 할 질문이 너무 많습니다(하이퍼슬러그 댓글처럼). 나는 매우 유사한 상황에 처해 있으며, 인코딩할 수 있는 종류가 너무 많기 때문에 속이는 항목을 찾으려면 수동으로 수행해야 한다는 것을 알았습니다.
귀하가 제안한 모든 매크로를 작성할 수 있습니다. 어떤 매크로가 가장 효과적인지 결정한 후 별도의 질문으로 요청하면 우리가 할 수 있는 모든 조치를 취하겠습니다. 마지막 방법은 구현이 간단하고 스크롤 시간을 절약해 줍니다. 해당 매크로를 만든 다음 속이는 항목을 숨긴 후 '표준' 항목을 클릭하여 다른 항목 위로 드래그하면 됩니다.
답변2
복사 및 정렬 대신 Excel의 MATCH 기능을 사용하여 필요한 데이터를 얻습니다.
마스터 목록이명명된 범위Master라고 불리며 트랜잭션 로그의 회사 이름은 D열에 있습니다. 트랜잭션 행의 어딘가에 다음 수식을 입력하고 =IF(ISNA(MATCH(D1,Master,0)),0,1)이를 트랜잭션 테이블의 모든 행에 복사합니다. 이 공식은 회사 이름이 일치하면 1이 되고 그렇지 않으면 0이 됩니다.
이는 정확한 이름과만 일치합니다. 가능한 모든 버전을 얻으려면 마스터 범위에 대체 이름을 추가해야 합니다(이름을 추가한 후 정렬해야 합니다).
답변3
나는 마스터 목록에 대체 철자를 추가하는 접근 방식에 동의합니다(메일링 등을 위해 선호하는 형식과 회사 데이터와 일치하는 형식을 알려주는 두 번째 열이 있을 수 있습니다). 이름의 대체 버전을 생성하기 위해 연속적인 SUBSTITUTE 함수를 사용하면 어느 정도 성공할 수 있습니다. 예를 들어
=SUBSTITUTE(대체(대체(LOWER(A1)," inc",""),".","")," ","")...
따라서 각 대체 항목은 선택한 텍스트의 모든 인스턴스를 대체 항목으로 대체합니다. 여기서는 아무것도 없습니다. 서로 다른 시스템의 이름 간에 유사한 유사 퍼지 일치를 경험한 경험에 따르면 일치 항목을 얻으려면 inc, corp, plc 등과 같은 항목을 삭제해야 할 수도 있습니다. 이를 위해 SUBSTITUTE를 사용할 수 있지만 "Income Corporation"이 "omeorporation"이 되는 이상한 결과를 얻을 수 있으므로 다음과 같은 종류를 사용하는 것이 더 안전할 수 있습니다.
IF(RIGHT(lower(A1),4)="회사",left(lower(A1),len(A1)-4)),lower(A1)).
공백을 마지막으로 대체하십시오.
유사한 결과로 MATCH 또는 COUNTIF를 사용하여 목록과 일치하는 거래를 보여주는 열을 제공할 수 있습니다.
대안은 고급 필터의 기반이 되는 기준으로 마스터 목록을 사용하는 것입니다. 이렇게 하면 고객 이름과 일치하는 거래 목록 항목의 복사본을 매우 쉽게 가져와 필터링된 복사본을 다른 곳에 배치할 수 있습니다(예: 한쪽 면 또는 다른 시트). 위와 마찬가지로 원래 이름과 너무 다른 변형을 추가해야 합니다.
답변4
피벗 테이블을 사용해 본 적이 있는지 궁금합니다. 저는 PT를 사용하여 많은 데이터를 처리하는데 PT를 사용하면 다양한 방식으로 문제를 매우 신속하고 완전한 데이터 무결성으로 볼 수 있습니다.
모든 데이터를 강조 표시하고 피벗 테이블 삽입을 선택합니다. 이제 성가신 이중 항목, 철자 오류 등의 범위를 좁힐 수 있는 다양한 대화형 방식으로 데이터를 검토할 수 있습니다. 그런 다음 AZ뿐만 아니라 사용자 정의 정렬 등을 사용하여 정렬할 수 있습니다.