[Excel.Application] #2 엑셀에서 특정 조건에 일치하는 데이터 추출

제가 오토핫키를 사용하는 궁극적인 이유는 지금 설명드릴 내용 때문입니다.

이 기능을 통해 매일 하던 반복적인 행위를 10초만에 완료할 수 있죠.

---

한 엑셀 파일을 보겠습니다.

복잡해 보이지만 평범한 계획 파일입니다.

Item 이 순서대로 며칠날 어떤 Shift 에 몇개를 태울건지에 대한 정보를 담고 있어요.

 

하지만 이 정보를 사용하기 위해서는 가공이 필요합니다. Row Data 요.

최종적으로는 이렇게 날짜와 Shift 구분 모델명과 수량만을 추출한 가공된 데이터가 나오게 되는거죠.

아무래도 돼지 한마리 잡는 것보다 돼지 내에서 나에게 필요한 삼겹살, 앞다리살만 가져오는게 낫죠.

 

그럼 먼저 아이디어를 구상해야겠죠

각 데이터는 어디서 가져올까

어떻게 가져올까

 

일단 계획표 Range 를 가져오자

Row 의 끝

Column 의 끝을 가져오고

계획이 Column 내에 자유분방하게 분포되어있고 하나의 Row 안에 2개의 계획이 있을수도 있으니(Shift 때문에)

각 Column 에 값이 빈값이 아니라면 값을 넣는 것으로 하자. Column 의 숫자가 무한으로 증가하는 것을 막아야하니까 처음 추추했던 Column 의 끝에 도달하면 Row 를 +1 해주고 Column 을 다시 검사하는 것으로 만들자.

 

그렇다면 Row 를 증가시킬 Loop 와 그 안에 Column 을 증가시키며 검사를 할 Loop 2개의 Loop 가 필요하겠구나.

 

xl := ComObjActive("Excel.Application")
RC := xl.Sheets("RC SEQUENCE")

;표 Column 끝 값 column 변수에 담기
Loop {
	if(RC.Cells(8, 10 + A_Index).MergeArea.Cells(1).Value = "") {
		column := RC.Cells(8, 10 + A_Index).MergeArea.Cells(1).Column
		break
	}
}

날짜가 담겨있는 병합된 셀의 값이 빈값인지 확인한다. 만약 비어있다면 column 변수에 해당 column 번호를 넣고 반복문에서 탈출한다. 이걸로 계획표의 끝을 알게 되었다.

 

;표 Row 끝 값 row 변수에 담기
Loop {
	if(RC.Cells(8 + A_Index, 6).Value = "") {
		row := RC.Cells(8 + A_Index, 6).Row
		break
	}
}

Row 도 마찬가지이다. 다만 Row 는 병합되어있지 않기 때문에 조금 더 간단해 보인다.

 

;Loop 로 Row 를 하나씩 늘려가며 Column 10 ~ 37 까지 값을 찾아서 빈값이 아닐 경우 누적 변수에 Item 명과 수량 담기
r := 10
Loop {
	Loop {
		if(9 + A_Index = column)
			break
		if(RC.Cells(r, 9 + A_Index).Value != "") {
			Shift := RC.Cells(9, 9 + A_Index).Value = "1 st" ? "Dia" : "Noche"
			Day := RC.Cells(8, 9 + A_Index).MergeArea.Cells(1).Value
			Data .= Day "`t" Shift "`t"RC.Cells(r, 6).Value "`t" RC.Cells(r, 9 + A_Index).Value "`n"
		}
	}
	r ++
	if(r = row)
		break
}

r 이라는 변수에 10을 담아준다. 그 이유는 계획 수량이 담겨있는 위치가 Column 10번부터 시작이기 때문이다.

그렇게 해서 9 + A_Index 가 만약 Column 과 같다면 (표의 범위 밖으로 벗어났다면) 반복문을 멈춘다.

그게 아니라면 이제부터 값을 검사해볼건데 Column 을 하나씩 늘려보면서 검사를 하고 빈값이 아니라면

각종 정보를 추출해서 Data 변수에 누적으로 담는다.

해당 반복문이 끝나면 Row 를 늘려줘야 하므로 r 변수 ++ 해준 후 다시 반복한다.

 

r 이 아까 검사했던 row 변수의 값과 같다면 (표의 끝에 도달했다면) 반복문을 종료한다.

 

SetBatchLines, -1

xl := ComObjActive("Excel.Application")
RC := xl.Sheets("RC SEQUENCE")

Data := "Fecha`tTurno`tItem`tCantidad`n"

Start := A_TickCount

;표 Column 끝 값 column 변수에 담기
Loop {
	if(RC.Cells(8, 10 + A_Index).MergeArea.Cells(1).Value = "") {
		column := RC.Cells(8, 10 + A_Index).MergeArea.Cells(1).Column
		break
	}
}

;표 Row 끝 값 row 변수에 담기
Loop {
	if(RC.Cells(8 + A_Index, 6).Value = "") {
		row := RC.Cells(8 + A_Index, 6).Row
		break
	}
}

;Loop 로 Row 를 하나씩 늘려가며 Column 10 ~ 37 까지 값을 찾아서 빈값이 아닐 경우 누적 변수에 Item 명과 수량 담기
r := 10
Loop {
	Loop {
		if(9 + A_Index = column)
			break
		if(RC.Cells(r, 9 + A_Index).Value != "") {
			Shift := RC.Cells(9, 9 + A_Index).Value = "1 st" ? "Dia" : "Noche"
			Day := RC.Cells(8, 9 + A_Index).MergeArea.Cells(1).Value
			Data .= Day "`t" Shift "`t"RC.Cells(r, 6).Value "`t" RC.Cells(r, 9 + A_Index).Value "`n"
		}
	}
	r ++
	if(r = row)
		break
}
TrayTip, % Round((A_TickCount - Start)/1000, 1) " 초 소요", 계획 취합이 완료되었습니다.
clipboard := Data

실제로 사용하는 스크립트 전문

 

Production Plan.xlsx

0.11MB

실제로 사용한 엑셀 파일 중 일부