☰

누락된 반복 ID 번호를 텍스트 또는 Excel의 시리즈에 추가해야 합니다. 코딩 없이 이 작업을 수행하는 방법은 무엇입니까?

tag-icon • tag-icon microsoft-excel regex
누락된 반복 ID 번호를 텍스트 또는 Excel의 시리즈에 추가해야 합니다. 코딩 없이 이 작업을 수행하는 방법은 무엇입니까?

다음과 같은 텍스트, csv 또는 Excel 파일이 있습니다.

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

실제 세트에는 총 1,000,000개 행 중 280,000개가 포함되어 있습니다. 누락된 ID 번호(모두 순차적 +1 반복)를 모두 추가하고 이름 및 dob에 대한 빈 필드만 추가해야 합니다. 그래서 나는 다음과 같은 것을 얻습니다.

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

32까지 계속해서 모든 정수가 거기에 있을 때까지 다시 반복합니다. 이 작업을 수행하는 쉬운 방법이 있나요? 루프에 코드 블록을 작성하지 않고? Excel, 일부 앱 또는 Windows 텍스트 편집기 트릭의 모든 것이 감사하겠습니다.

편집: 쉼표, 따옴표 등은 무시하십시오. 누락된 ID 번호가 유일하게 중요한 것입니다.

답변1

한 가지 접근 방식은 다음과 같습니다.

  1. CSV를 시트 1로 Excel로 가져옵니다.
  2. 시트 2에서 a2: =a1+1과 같은 수식을 사용하여 전체 ID 번호 목록을 작성한 다음 아래로 복사합니다.
  3. =VLOOKUP(A2,Sheet1!A2:C13,2,false)b2: 및 c2: 와 같은 시트 1의 테이블을 다시 참조하여 다른 2개 열에 vlookup 수식을 사용하거나 =VLOOKUP(A2,Sheet1!A2:C13,3,false)요청과 정확히 일치하도록 IFNA 문으로 래핑하여 값이 없는 경우 ""를 반환합니다. b2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"")및 c2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"")(이제 이 수식을 열 아래로 복사할 수 있습니다.)
  4. 일단 저장 시트 2를 CSV로 채웁니다.

참고: 이 작업이 올바르게 작동하려면 시트 1의 테이블을 열 1에서 정렬해야 합니다. 제공된 정보에서는 그렇게 보이지만 그렇지 않은 경우 첫 번째 열의 정보를 정렬해야 합니다.

백만 개의 행을 사용하면 많은 컴퓨터에서 실제로 속도가 매우 느려질 것입니다. 수식을 작성하기 전에 이를 놓쳤습니다. 모든 수식을 제자리에 가져오기 위해 자동 relcalc를 꺼서 대규모 데이터 세트로 비슷한 작업을 수행한 다음 수동 재계산을 수행했습니다. 몇 시간이 걸렸지만 올바르게 완료되었습니다.

답변2

파워 쿼리 추가 기능을 사용하여 이 문제를 해결하겠습니다.

내 One Drive에서 보거나 다운로드할 수 있는 "파워 쿼리 데모 - 누락된 ID 번호를 series.xlsx에 추가"라는 프로토타입을 만들었습니다.

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

List.Numbers 함수를 호출하려면 몇 가지 단계를 거쳐 파워 쿼리 언어(M)로 약간의 코딩을 수행해야 합니다(파워 쿼리 UI에는 표시되지 않음). 하지만 이는 단지 한 줄의 간단한 코드일 뿐입니다. 나머지는 파워 쿼리에서 클릭하여 작성할 수 있습니다.

기본적으로 내 기술은 List.Numbers를 사용하여 ID 번호 테이블을 생성한 다음 Merge를 추가하여 입력 데이터(ID 번호가 존재하는 위치)에서 열을 가져오는 것이었습니다.

List.Numbers에 대한 문서는 여기에 있습니다:

http://office.microsoft.com/en-au/excel-help/list-numbers-HA104111648.aspx?CTT=5&origin=HA104122363

"설정" 기술에 대해 Matt Masson에게 큰 "모자 팁"을 주었습니다.

http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/

참고 파워 쿼리는 "입력 데이터" 쿼리의 원본으로 사용하는 CSV 파일에서 직접 읽을 수 있습니다. 해당 쿼리를 삭제한 다음 CSV 파일에서 새 쿼리를 작성하고 이름을 "입력 데이터"로 지정하는 것이 가장 쉬운 방법일 것입니다. 리소스를 저장하려면 워크시트로 로드 옵션을 선택 취소하세요.

관련 정보