Python Menjumlahkan Baris Tertentu pada Semua Sheet Excel

Ternyata memilih judul tulisan sulit juga. Masalah yang kita hadapi adalah seperti ini, kita memiliki sebuah file dengan banyak sheet, di tiap sheet terdapat data pada kolom H yang ingin kita jumlahkan, hasil penjumlahan semua sheet kita simpan di sheet bernama Total. Ilustrasi masalah tersebut seperti di bawah ini.

File Excel di atas dapat diunduh di sini.

Untuk mencapai tujuan menjumlahkan kolom H dari baris 2 sampai baris 18 (atau baris n ke m) kita punya beberapa alternatif solusi formula di sheet Total.

1. =SUM(JAN!H1:H14)+SUM(FEB!H1:H14) namun dengan konsekuensi kita menuliskan sebanyak sheet yang ada
2. =SUM(JAN!H1:H14,FEB!H1:H14) formula ini hanya berlaku di Excel (Google Sheets tidak dapat memproses).
3. =SUM(JAN:APR!H1:H14) lagi, hanya berlaku di Excel, formula ini akan memproses semua sheet antara JAN sampai APR. Untuk formula ini digunakan istilah 3-D References.
   

Lebih lanjut mengenai dua formula terakhir dapat dibaca di sini.

Formula di atas tentu dapat disederhanakan jika baris 23, terdapat tulisan Jumlah Periode Ini, telah terisi jumlah baris di atasnya. Cukup menggunakan formula =SUM(JAN:APR!H23) pada sheet Total. Bahkan lebih sederhana lagi bila baris 25, terdapat tulisan Jumlah Semua sampai Periode Ini, telah terisi, nilai sel H25 pada sheet APR tentu sudah berisi total seluruh nilai kolom H pada sheet sebelumnya. Jika kondisi terakhir tersebut yang kita temui, tentu kita tidak perlu solusi lain dan tulisan ini menjadi tidak bermakna. Hahaha.

Namun bagaimana bila jumlah baris tiap sheet berbeda, misal di JAN nilai yang akan dijumlahkan ada di H1:H14 tapi di FEB barisnya lebih banyak, menjadi H1:H25. Tentu formula #1 dan #2 saja yang dapat kita gunakan. Dapat pula menggunakan formula yang berisi referensi sel pada baris Jumlah Periode Ini seperti =JAN!H23+FEB!H34.

Alternatif formula di atas sangat bergantung pada data yang kita miliki, kita harus memilih solusi dengan pola yang paling sering ditemukan pada masing-masing sheet. Jika tidak terdapat baris berisi penjumlahan baris sebelumnya, seperti baris Jumlah Periode Ini, kita dapat menggunakan formula =SUM(JAN:APR!H:H).

Bila jumlah baris pada tiap sheet berbeda dan terdapat baris berisi penjumlahan baris tentu kita tidak dapat menggunakan rumus =SUM(JAN:APR!H:H) karena akan terdapat perhitungan ganda. Kondisi seperti ini ditangani dengan formula =JAN!H23+FEB!H34 atau =SUM(JAN!H23,FEB!H34).

Menggunakan formula terakhir ditambah kemalasan untuk melakukan klik di tiap sheet maka terbitlah kode Python seperti ini.

from openpyxl import load_workbook

wb = load_workbook('/home/aan/basangdata_excel/python_menjumlahkan_baris/contoh_output_formula.xlsx', data_only = True)

row_numbers = {}
for ws in wb.worksheets:
    if 'Total' not in ws.title:
        max_row_b = ws.max_row + 1
    
        for row_b in range(2, max_row_b):
            if ws['B' + str(row_b)].value == 'Jumlah Periode Ini':
                row_numbers[ws.title] = row_b

ws_total = wb['Total']
formula = '='

for rn in row_numbers:
    formula = formula + rn + '!H' + str(row_numbers[rn]) + '+'

ws_total['B2'].value = formula[:-1]

wb.save('/home/aan/basangdata_excel/python_menjumlahkan_baris/contoh_output_formula_hasil.xlsx')

Hasil dari kode di atas adalah sebuah file Excel bernama contoh_output_formula_hasil.xlsx yang pada sheet Total sel B2 berisi =JAN!H23+FEB!H23+MAR!H23+APR!H23. File Jupyter Notebook kode di atas, yang dilengkapi dengan dokumentasi langkah kerja, dapat ditinjau di sini. Pada file Excel tersebut, sheet Total akan berisi seperti ini.

Berbeda dengan tulisan–tulisan sebelumnya, kali ini hasil kode berupa formula, keuntungan dari output berupa formula adalah kita tidak perlu menjalankan kode Python lagi saat data berubah karena telah dikalkukasi ulang oleh Excel. Tentu saja formula tersebut harus dapat dieksekusi oleh Excel jika tidak dikenali maka tak akan diproses oleh Excel.

Semoga menambah wawasan mengenai bantuan yang dapat dilakukan Python untuk mempermudah Anda menangani file Excel.

Salam.