가변 형식이 있는 웹 데이터베이스에서 다운로드한 데이터 세트가 있습니다. 목표는 모든 데이터를 행으로 가져와 정렬하고 잠재적으로 CSV-KMZ(KML) 파일로 변환하는 것입니다. 셀 병합을 취소하면 데이터 형식이 다음과 같이 지정됩니다.이것.
내가 찾은 무차별 대입 솔루션은 다음을 사용하는 것입니다.='cell number'오른쪽 셀(이와 같이.) 그런 다음 빈 셀을 모두 삭제하여 모든 데이터를 동일한 행에 통합합니다(여기 예). 여기서는 셀 배열을 선택하고 아래로 드래그하여 하위 셀에 복사할 수 있습니다. 문제는 첫 번째 그림과 같이 여러 사이트가 있는 책임 당사자 항목을 발견할 때 발생합니다. 문제를 해결하기 위해 많은 복사 및 붙여넣기가 필요한 공백을 버리기 때문입니다.
이 데이터 세트는 10,000개 이상의 행으로 내려가고 이와 같은 세트가 더 많이 있을 가능성이 높으므로 여기 있는 훌륭한 인재 중 일부가 다른 솔루션을 생각할 수 있기를 바랍니다. 이 문제에 대해 생각해 주신 모든 분들께 감사드립니다!
답변1
그림에 나온 데이터 레이아웃 예시가 성공의 열쇠입니다. 그것은잘 특성화 된데이터. 이는 모든 곳에 존재하는 것이 아니라 정해진 패턴을 따른다는 것을 의미합니다. 이는 간단하고 간단한 공식을 작성하여 각 항목을 수집할 수 있음을 의미합니다.
클릭하기 전 도입부를 읽었을 때, 여기저기서 데이터가 보이는 줄 알았습니다. 예를 들어, {City}는 셀 D3, E3 또는 F3에 있을 수도 있고 다른 곳도 유사하게 있을 수도 있고 {State}와 결합될 수도 있으며 각 레코드는 다를 수도 있습니다. 일부에서는 결합되지 않고 6개가 연속으로 E3에서는 {City}, F3에서는 커플, 그런 거죠.
당신은 그런 종류의 결함이 전혀 없습니다!
유일한 어려움은 기록 기간당 하나의 사이트가 아닌 여러 사이트로 구성된 사이트 정보에 있습니다. 그러나 이는 다음 두 가지 방법으로 해결되는 사소한 합병증입니다.
1) 레코드 당 가능한 사이트 수에 대한 판단. RP당 단일 행에 결과를 배치할 계획이므로 이미 이에 대해 생각하고 있어야 하므로 이를 사용하십시오. 2) IF() 함수를 사용하여 사이트 정보를 추출할지 아니면 새 기록을 시작할지 알려주는 특정 데이터 조각을 테스트합니다.
두 번째는 한 곳이 아니라 두 곳에서 결정하게 되므로 약간 이상해 보일 것입니다.
일단 결정되면 출력 행의 셀은 결정 셀의 내용을 테스트하고 행을 따라 물결치는 더 간단한 수식을 가질 수 있습니다.
너 뭐하니? 먼저, 명확성을 위해 굵은 글씨로 표시된 항목을 추가했으며 추출할 필요가 없다고 가정합니다. (그렇다면 같은 방식으로 수행하십시오.) 또한 데이터 행, 일련의 "빈" 행, 또 다른 데이터 행 등을 포함하는 간단한 출력을 계획하고 있습니다. 마지막 아이디어는 Copy and Paste|Special|Values를 수행한 다음 끝에 있는 거대한 "빈" 행 더미를 정렬하고 삭제하는 것입니다. 더 좋아질 수도 있지만 오후 11시 30분에는 그럴 수 없습니다...
주요 정보인 "RP" 데이터를 추출하려면(T2에서 시작한다는 아이디어를 사용하고 "파티 ID는 "RP#"라고 가정합니다.) 수정하고 관련시킬 약간의 데이터가 필요합니다. 다른 부분을 살펴보는 위치는 "RP#"에 이상적입니다. 따라서 셀 T2는 다음과 같습니다.
= C1
이제 OFFSET()을 사용하여 나머지 RP 데이터를 모두 찾습니다. 하지만 출력의 각 줄은 데이터를 수집해야 하는지 확인해야 한다는 점을 기억하세요. 따라서 행이 데이터를 가져오는지 여부를 확인하려면 이를 IF()로 래핑해야 합니다.
= IF( C1 = "", "", C1)
이렇게 하면 T2가 일부 RP#로 채워집니다. T3~T7에서는 ""가 표시됩니다. U2로 이동합니다. 다음 수식을 입력합니다.
= IF( C1="", "", OFFSET( C1, 0, 2 ) )
C1에 RP#이 있는 경우 아래로 0행, 오른쪽으로 2열 셀의 값을 찾습니다. 그렇지 않은 경우 U2에 ""가 표시됩니다(사이트가 아닌 데이터를 찾는 모든 열에 대해 계속해서 표시됩니다).
계속하세요. 필요에 따라 두 값(행이 첫 번째, 열이 두 번째)을 변경하여 C1을 기준으로 각 부분을 찾습니다. Thant는 검색된 모든 비사이트 데이터를 처리합니다. (기억하고 다음에 사용하는 흥미로운 점은 오프셋이 음수 값이 될 수 있으므로 OFFSET()을 사용하여 왼쪽과 위쪽, 오른쪽과 아래쪽을 볼 수 있다는 것입니다.)
AB2 셀의 경우 +4행의 오프셋을 입력합니다.아래에) 및 -1 열(따라서왼쪽). 따라서 간단한 IF() 테스트를 수행한 다음 오프셋을 수행합니다. 각 RP에 대해 최소 하나의 사이트가 없다는 데이터를 고려하면 불가능해 보이지만 사이트가 없을 경우 IF() 테스트에 추가하세요.
= IF( OR( C1="", OFFSET( C1, 4, -1 ) = "" ), "", OFFSET( C1, 4, -1 ) )
사이트 데이터 수집 오른쪽으로 이동하여 간단한 IF() 테스트로 돌아가되 C1 대신 AB2를 사용합니다. (C1이 RP#이 아닌 경우 "빈" AB2가 있으므로 "빈" AB2는 C1도 "비어 있으므로 매번 테스트할 필요가 없음을 의미합니다.) RP에서 했던 것처럼 해당 사이트의 모든 데이터를 수집합니다. 데이터.
이제 문제의 핵심은 다음과 같습니다. 두 번째 사이트가 있습니까, 아니면 새로운 기록의 시작이 있습니까? 동일한 열의 셀이지만 이 레코드의 RP#에서 7행 아래에 있는 셀은 새 RP#이거나 비어 있습니다. "공백"은 이전처럼 테스트할 수 있습니다. AK2가 두 번째 사이트의 데이터가 시작되어야 하는 곳이라고 가정해 보겠습니다. 해당 셀이 비어 있는지 또는 비어 있지 않은지 테스트하십시오. 비어 있으면 두 번째 사이트가 있으며 위와 동일하게 해당 데이터를 찾을 수 있습니다. 사용:
= IF( OR( C1="", OFFSET( C1, 7, 0 ) = "" ), "", OFFSET( C1, 7, -1 ) )
사이트 번호(있는 경우) 또는 ""를 가져옵니다. 첫 번째 사이트와 같은 종류의 수식으로 행 오프셋만 변경합니다(열 오프셋은 동일함). 사이트 정보가 전혀 없을 수 있는 경우 ""에 대해 AB2도 테스트하도록 이를 수정하여 "비어 있는" 경우 "" 결과가 여기에서 오른쪽으로 파급되도록 합니다.
가능하다고 결정한 만큼 많은 사이트 데이터 세트에 대해 이 작업을 수행하십시오. 게다가 한두 개 정도 더 있을 수도 있죠, 그렇죠?
이제 사이트 데이터 존재의 "나머지 절반"인 행 3과 셀 T3으로 이동합니다. 2행의 모든 셀을 15행까지 복사합니다. C8에 도달할 때까지 RP#이 없는 C2, c3 등의 빈 행이 연속으로 나타납니다. 그러면 데이터가 다시 나타납니다.
그리고 T8은 ""가 아니므로 오른쪽 행에 데이터가 채워집니다. 이야!
실제 사이트 모음 오른쪽에 있는 사이트 셀이 해당 데이터에 대해 다음 레코드(2개 또는 4개)를 읽어 내려가기 때문에 이상하거나 혼란스러운 항목을 얻을 수 있다고 생각할 수도 있습니다. 그러나 첫 번째는 그 이유가 있는지 확인하고 (가짜가 아닐까 걱정되는) 사이트 번호에 대해 "" 결과를 가져온 다음 다른 레코드의 데이터를 읽는 대신 오른쪽으로 잔물결이 발생하여 "" 항목을 생성합니다. . 걱정 마.
가지고 있는 모든 데이터 행 또는 작업할 수 있는 만큼의 데이터 행을 복사하여 붙여넣습니다(컴퓨터가 10,000개 행의 모든 수식을 추적할 수 있을 뿐만 아니라 해당 값을 복사하여 붙여넣는다는 점을 기억하세요). 동시에). 여기서는 문제가 없다고 가정해 보겠습니다. 하지만 문제가 있는 경우 1,000행 세트로 관리해야 하는 문제이거나 자신에게 잘 맞는 것이면 무엇이든 가능합니다.
모든 계산이 완료되면 출력 셀을 복사하고 Paste|Special|Values를 두 번째 시트와 같은 다른 곳에 복사합니다. (이 시점에서 기계 성능이 우려되는 경우 첫 번째 쌍을 제외한 모든 수식 행을 삭제합니다.)
완전히 다른 시트에 있으면 수식 추출 셀이나 원본 데이터에 영향을 주지 않고 출력에 대해 모든 작업을 수행할 수 있습니다.
두 번째 시트에서는 한 가지 더 걱정할 사항이 있습니다. 다음에는 출력을 정렬하는 것입니다. 때로는 원래 데이터 순서가 필요한데 Excel에서 생성하는 정렬 순서가 아닙니다. 그러한 경우 왼쪽에 열을 삽입하고 원하는 대로 일련번호 목록을 채우십시오. 수식이 아니라 "상수"여야 하므로 정렬 후 수식 자체를 다시 계산하므로 아무 것도 변경되지 않습니다.
자, 이제 "빈" 행을 모두 제거하고 빡빡한 데이터 행 집합만 만들 차례입니다. 데이터를 정렬합니다(필요하거나 원하는 경우 번호 매기기 열도 포함). 평소처럼 낮은 수준에서 높은 수준으로 작업하여 "빈" 행이 맨 아래에 표시되도록 합니다. "빈" 행 중 첫 번째 행을 찾으세요. 이를 수행하는 방법에는 여러 가지가 있습니다... 스크롤, 예를 들어... 또는 먼저 빈 행으로 이동하여 첫 번째(가장 왼쪽) 셀에 "zzzzzzzzzzzzz"를 입력하면 데이터 끝으로 정렬됩니다. 모든 "빈" 행 앞의 첫 번째 행입니다. "비어 있는 항목"의 첫 번째 행의 첫 번째 셀에 도달하면 Ctrl-Shift-End를 눌러 꺼내야 하는 모든 쓰레기를 강조 표시합니다. 삭제 키로 내용을 지웁니다.
이제 데이터 행이 모두 함께 있고, 데이터 사용을 방해하는 가짜 행(실제로 비어 있지 않음)이 없으므로 작업 준비가 완료되었습니다. 작업하거나 다른 스프레드시트의 최종 휴게소에 복사하여 붙여넣고 마을로 가세요!
그건 그렇고, 건설은 매우 쉽고 시간이 전혀 걸리지 않습니다. 한 번 완료되면 데이터가 이동하지 않는 한 영원히 완료됩니다. 웹사이트가 변경되고, 소프트웨어가 업그레이드되고, 완전히 새로운 열이 상황을 변화시킨다는 사실을 아시겠지만, 초기 작업을 천천히 업그레이드하면서 이러한 문제를 해결할 수 있는 쉬운 방법이 많이 있습니다.
일단 구성되면 데이터는 로드되는 즉시 읽혀지고 한 번 복사하여 붙여넣기, 정렬, 삭제(모두 키 누르기, 문자 그대로 몇 시간이 아닌 1분)만 수행하면 작업이 완료됩니다. 나는 당신이 Quesiton에서 제기한 것처럼 당신의 입장에서 무차별적인 노력으로 문제를 해결했기 때문에 이것을 언급합니다. 나는 당신이 믿기 어려울 것이라고 생각합니다. 톤, 마우스 슬립 등이 이제 말 그대로 1분이 됩니다. 더 이상 당신을 훔치지 않는 삶을 즐겨보세요!