엑셀 파일들 내맘대로 병합하는 법(영상)[엑셀에 엑셀달기]

들어가며:

업무 수행을 하시다 보면 raw 데이터들이 엑셀 확장자(.xlsx)로 주어지는 경우가 많습니다.

여러 엑셀 파일을 취합하는 방법들이 있지만(시트 병합, 인터넷에서 다운받은 프로그램, ...)

 

시트 병합은 한 번에 3개 이상의 병합이 불가능하며 시트로 합쳐지는 단점이 있고

인터넷에서 다운받은 프로그램은 내 데이터의 상황에 맞춤이 아닌 것이 불편합니다.

 

내 데이터에 맞춤으로 직접 병합하는 코드를 짤 수 있다면 작업 속도가 크게 상승할 것입니다.

파이썬으로는 쉽게, 직접 하실 수 있습니다.

여러 엑셀 파일을 한 엑셀 파일로 취합 (w/ python)

import openpyxl

data = []

for i in range(1, 6+1):
    filename = "2021/20210{}.xlsx".format(i)
    b = openpyxl.load_workbook(filename)
    bs = b["Sheet"]
    
    j=3
    while True:
        data.append(
            [
                bs["B{}".format(j)].value, 
                bs["C{}".format(j)].value, 
                bs["D{}".format(j)].value, 
                bs["E{}".format(j)].value
            ]
        )
        j = j+1
        if bs["B{}".format(j)].value == None:
            break
print(data)

wb = openpyxl.Workbook()
wb.create_sheet("01-06")
wb["01-06"]["B2"] = "판매코드"
wb["01-06"]["C2"] = "지점"
wb["01-06"]["D2"] = "종류"
wb["01-06"]["E2"] = "가격"
for i in range(len(data)):
    wb["01-06"]["B{}".format(i+3)] = data[i][0]
    wb["01-06"]["C{}".format(i+3)] = data[i][1]
    wb["01-06"]["D{}".format(i+3)] = data[i][2]
    wb["01-06"]["E{}".format(i+3)] = data[i][3]

wb.save("상반기.xlsx")

(취합하는 코드와 데이터 파일은  https://github.com/CodingVillainKor/AccelExcel 에서 확인하실 수 있습니다.)

 

각 줄의 코드를 설명드리면,

[line 1] import openpyxl : 엑셀 데이터를 다루기 위해, openpyxl이라는 python library를 import합니다.

 

[line 3] data = [] : 여러 엑셀 파일의 데이터를 한 곳에 모으기 위한 빈 리스트를 선언합니다.

 

[line 5] for i in range(1, 6+1): for 반복문을 사용합니다. 반복하는 동안, i는 1에서 6까지 1씩 증가합니다(1,2,3,4,5,6).

 

[line 6] filename = "2021/20210{}.xlsx".format(i): i는 1~6까지 증가하는 동안 {}자리에 i값을 대입합니다.

결과적으로, filename은 2021폴더의 202101.xlsx, 202102.xlsx, 202103.xlsx, ... , 202106.xlsx가 됩니다.

 

[line 7] b=load_workbook(filename) : [line 6]의 filename의 파일명을 엑셀로 엽니다.

실제로 엑셀창이 켜지는 것은 아니고, 파이썬이 이 파일을 들여다보는 것입니다.

for문으로 반복되면서 변화하는 파일명에 대해 각각 load하게 됩니다.

 

[line 8] bs=b["Sheet"] : bs는 방금 연 엑셀 파일(b)의 Sheet 시트를 가리킵니다.

 

[line 10] j=3 : 데이터의 3번째 줄부터 데이터가 등장하는 것을 알고 있기에 행 정보로 사용할 j를 3으로 초기화합니다.

 

[line 11] while True: while은 뒤에 있는 구문이 참인 동안 계속 반복합니다.

여기서는 while True는 항상 참으로 걸겠다는 뜻으로 이론상 무한히 반복합니다.

다만 while문 안에서 break를 만나면 while 뒤의 조건에 관계없이 반복을 멈추고 빠져나옵니다. 

 

[line 12~19] data.append(...) : 필요한 데이터를 모으는 코드입니다.

list에 append(O)는 list에 O를 추가합니다.

bs["B{}".format(j)].value는 [line 8]에서의 Sheet 시트를 보고 있는 bs에서 B열 j행의 값이라는 의미입니다.

B, C, D, E열의 j번째 행 값을 모읍니다.

13, 18번째 줄의 [, ]도 list를 나타내는 것으로 data라는 list 안에 4개의 데이터를 담은 list를 넣는다는 의미입니다.

 

[line 20] j=j+1 : 매 반복마다 j값에 1을 더합니다.

처음엔 j가 3에서 4가 되며, 4에서 5, 5에서 6, ...의 형태로 증가합니다.

 

[line 21]if bs["B{}".format(j)] == None: : B열 j행 데이터가 None일때 라는 말은 비어있을 때를 의미합니다.

앞의 if는 조건문을 사용할 때 쓰이며, 뒤가 참일 경우 아래 들여쓰기 부분을 실행합니다.

여기서는 B열 j번째 행 데이터가 비어있을 때, 아래의 break를 수행하겠다는 의미입니다.

 

[line 22] break : break는 제일 가까운 반복문 하나를 탈출합니다.현재 반복은 for문과 while문 두개가 있습니다.22번째 코드 시점에서 더 가까운 반복문은 while문이기 때문에 while문을 탈출합니다.

 

[line 23] print(data) : data의 내용을 확인할 수 있도록 출력합니다.알고리즘 상 변화는 없습니다. 데이터가 잘 담겼는지 확인을 위한 용도입니다.

 

[line 25] wb = openpyxl.Workbook() : 빈 엑셀 창을 엽니다.

[line 7]과 마찬가지로 실제로 엑셀창이 켜지는 것은 아니고 파이썬이 빈 엑셀창을 가상으로 여는 것입니다.

[line 7]은 존재하는 엑셀 파일을 여는 것이고, 이번 코드는 빈 엑셀 창을 연 것과 같다고 생각하시면 됩니다.

 

[line 26] wb.create_sheet("01-06") : 연 엑셀 창에 "01-06"라는 이름의 시트를 추가합니다.

 

[line 27~30] wb["01-06"]["B2"] = "판매 코드" : [line 25]에서 연 빈 엑셀 창의 01-06 시트의 B2 셀에 (판매 코드)를 입력합니다.

C2, D2, E2에도 각각 데이터 이름을 기입합니다.

 

[line 31] for i in range(len(data)): [line 5]와 같이 반복 수행을 하는 코드입니다.

여기서는 [line 5]와 달리 range() 괄호 안에 len(data) 값 하나만 있습니다.

range에 인자가 하나 뿐인 경우, 시작은 0에서 시작합니다.

여기서는 i가 0에서 len(data)-1까지 반복합니다.

len(data)는 data list의 아이템 개수입니다.

data는 전체 파일의, 전체 데이터 줄을 담았으므로 모든 파일의 데이터 수만큼 담겨있습니다.

 

[line 32~35] wb["01-06"]["B{}".format(i+3)] = data[i][0] : 모은 데이터를 하나하나 입력합니다.

wb["01-06"]은 연 엑셀 창의 01-06셀을 본다는 의미입니다.

"B{}".format(i+3)은 B열의 i+3번째 줄, i는 0부터 시작했으므로 즉, B3번째 줄부터 보겠다는 의미입니다.

data[i][0]에서 data[i]는 data의 i번째, 즉 맨 첫번째 아이템부터 보겠다는 의미입니다.

data[i]에 [0]을 붙인 것은 각 데이터의 첫 번째 아이템([line 14]에서 담은 것)입니다.

data[i][1]은 각 아이템의 두 번째 아이템([line 15]에서 담은 것)입니다.

data[i][2]은 각 아이템의 세 번째 아이템([line 16]에서 담은 것)입니다.

data[i][3]은 각 아이템의 네 번째 아이템([line 17]에서 담은 것)입니다.

python에서 list 아이템은 [0]이 첫 번째 아이템입니다.

 

[line 37] wb.save("상반기.xlsx") : 데이터를 쓴 wb를 저장하겠다는 의미입니다.

이 코드를 실행하지 않는 것은 엑셀 데이터를 다 써놓고 저장하지 않고 종료하는 것과 같은 의미입니다.

 

 

요약하면, 코드가 작동하는 알고리즘은 아래와 같습니다.

1. 데이터를 담을 바구니(python list)를 만들어둔다. [line 3]
2. 취합할 파일 리스트를 얻어둔다. [line 6]
3. 취합할 파일 리스트에서 하나씩 연다. → 데이터 범위를 설정해서 바구니에 담는다. [line 7~19]
4. 취합할 엑셀 창을 파이썬에서 만든다(openpyxl.Workbook()). [line 25]
5. 만든 엑셀 창에 바구니의 데이터를 담는다. [line 26~35]
6. 바구니의 데이터를 담은 엑셀 창을 [다른 이름으로 저장](wb.save)한다. [line 37]

 

영상 설명은 여기를 참고해주세요,

https://www.youtube.com/watch?v=33qLfmfi0XA