☰

다른 테이블의 행을 조건부로 복사하여 테이블 생성 - MS-Excel에서 가능합니까?

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
다른 테이블의 행을 조건부로 복사하여 테이블 생성 - MS-Excel에서 가능합니까?

매주 새로운 Excel 파일이 CRM 시스템에 의해 생성됩니다. 이 파일 이름에는 날짜 스탬프가 포함되어 있지만 고정된 위치(공유 포인트 URL)의 폴더에 저장됩니다.

이 파일은 상당히 크며 일반적으로 25,000~30,000개의 행을 포함합니다. 이 중 내 목적에 맞는 새 테이블을 만들어야 합니다.

내 목적과 관련된 행은 30-40개의 키워드/키 문구 목록에서 하나 이상의 키워드/키 문구와 일치해야 합니다. 그러나 이 키워드/핵심 문구 목록은 증가하기는 하지만 천천히 몇 달에 한 번씩 새 키워드를 추가합니다.

지루하고 오류가 발생하기 쉬운 이 수동 작업을 자동화할 수 있는 방법이 있습니까?

답변1

B세 가지 다른 열(아래에서 , D및 이라고 가정 F)을 확인해야 하므로 OR함수가 적절해 보입니다.

=OR(ISNUMBER(MATCH(B1,keyarray,0)),
    ISNUMBER(MATCH(D1,keyarray,0)),
    ISNUMBER(MATCH(F1,keyarray,0)))

세 열 중 하나와 일치하는 항목이 반환됩니다 TRUE. 이를 데이터가 차지하는 첫 번째 행의 '도우미' 열에 배치합니다(예: 행 1 또는 adjust 및 B1그에 따라 위).D1F1

MATCHB1예를 들어 의 값이 에 존재하는지 확인합니다 keyarray. 여기서 이는 키워드/키 문구 목록(~30-40개)이 포함된 범위에 대해 지정된 이름입니다. 동일한 시트나 통합 문서에 있을 필요는 없습니다. 그러나 그렇지 않은 경우 전체 경로를 지정해야 하며 새 데이터 배치에 수식을 적용할 때 '기타' 통합 문서를 열어 두는 것이 좋습니다.

0정확한 일치만 강제합니다(또는 -1보다 크거나 같은 가장 작은 값 B1또는 1가장 큰 값에 대해).

MATCH배열에서 발견된 값의 위치를 ​​반환합니다(그렇지 않으면 매개변수 0, #N/A). 이는 숫자이므로 결과를 ISNUMBER제외하기 위해 숫자(모든 숫자)를 테스트합니다 #N/A.

따라서 결과에 B1, D1또는 F1가 있으면 – 그렇지 않으면 keyarray입니다 .TRUEFALSE

병합된 셀이 포함된 25~30,000개 이상의 행에 수식을 편리하게 복사하려면 마지막으로 사용된 행과 '도우미' 열의 교차점에 무언가(예: '끝')를 배치하세요(스프레드시트의 크기가 불필요하게 확장되는 것을 방지하기 위해). ). 수식이 포함된 셀을 복사하고 바로 아래 셀을 선택한 다음 Ctrl+Shift+Down/Paste를 사용하여 마지막으로 점유된 행을 지나 계속하지 않고 '끝'을 덮어쓰지 않고 점유된 모든 행에 대해 도우미 열을 아래쪽으로 채웁니다.

에 대한 '도우미' 열을 필터링하고 TRUE, 사용 중인 모든 열을 선택한 다음, 새 시트/통합 문서에 복사하여 붙여넣습니다. 새 시트/통합 문서에서 빈 행을 삭제하고 저장합니다. (소스에서 '도우미' 열을 삭제하도록 선택할 수도 있습니다.)

가끔 keyarray명명된 범위에 항목을 추가할 때 추가 항목이 포함되는지 확인하세요.

답변2

이는 다양한 방법으로 해결될 수 있는 더 큰 작업입니다. 하지만 간단히 말해서, 그렇습니다. 이를 자동화할 수 있습니다.

시작하자면:

귀하의 CRM 시스템이 귀하가 직접 추출하려는 정보를 제공할 수 없다고 확신하십니까?

일반적으로 이는 SQL과 같은 데이터베이스 시스템을 기반으로 하며 이미 데이터를 추출하고 있으므로 필요에 맞게 이 출력을 수정할 수 있습니다.

이제 Excel의 가능성을 살펴보겠습니다.

  1. CRM에서 기본 데이터 가져오기

CRM에 대한 데이터 연결을 설정할 수 있습니다.

VBA 코드를 생성하여 폴더의 최신 파일을 가져오거나 설정된 데이터 연결을 이 새 파일로 업데이트할 수 있습니다.

VBA를 통해 최신 파일을 열고 원하는 데이터를 대상 워크시트에 복사할 수 있습니다.

...

  1. 여전히 필요한 경우 데이터 필터링

필터와 고급 필터, vlookup, countifs 및 vba 코드 예제를 살펴보는 것이 좋습니다. 여기와 다중 조건 필터링을 처리하는 stackoverflow에는 꽤 많은 질문이 있지만 이 문제를 올바르게 해결하려면 더 구체적인 내용을 제공해야 합니다.

제안 사항: 이 작업을 기본 데이터를 가져오고 필터링이 필요한 경우에만 나누겠습니다. 내가 보기에 필터링된 데이터를 프로세스의 훨씬 더 일찍 얻는 것이 가능해야 합니다. 아니면 다르게 말해주세요 :)

관련 정보