Excel Automation With Python

PYEXDA – Bài 0801: Phân Bổ Số Tiền Theo Tháng Với Tỷ Lệ Phần Trăm Tương Ứng

Khóa học ứng dụng Python Programming giải các thể loại task Excel thực tế. Để đăng ký khóa học, bạn có thể inbox TẠI ĐÂY. Sau khi đăng ký xong, bạn sẽ được cấp tài khoản học viên để truy cập vào tài liệu Premium của khóa học.

SOLUTION PYTHON:

import xlwings as xw #Làm việc với Excel
from dateutil.relativedelta import relativedelta #Hàm relativedelta để tính toán thời gian linh hoạt
from datetime import datetime #Làm việc với các đối tượng ngày tháng
wb = xw.Book('data.xlsx') #Mở tệp Excel có tên 'data.xlsx'
sheet = wb.sheets['data'] #Chọn sheet có tên là 'data' trong tệp Excel
amounts = sheet.range('A5').expand('down').value #Đọc dữ liệu từ A5:A8 (Dynamic) và lưu vào biến amounts
start_dates = sheet.range('B5').expand('down').value #Đọc dữ liệu từ B5:B8 (Dynamic) và lưu vào biến start_dates
percentages = {0: 10, 2: 20, 4: 30, 8: 40} #Tạo dict "tháng - tỷ lệ %" được sử dụng để tính toán
header_dates_str = sheet.range('C4').expand('right').value #Đọc dữ liệu ngày tháng từ C4:K4 (Dynamic) và lưu vào biến header_dates_str
#Chuyển đổi các giá trị ngày tháng dưới dạng chuỗi (string) trong header_dates_str thành các đối tượng datetime và lưu vào biến header_dates
header_dates = [datetime.strptime(date_str, "%m/%d/%Y") for date_str in header_dates_str]
#Lặp qua từng cặp giá trị amount và start_date trong danh sách amounts và start_dates. enumerate giúp chúng ta có thêm chỉ số i để biết được đang lặp qua phần tử thứ mấy
for i, (amount, start_date) in enumerate(zip(amounts, start_dates)):
    #Lặp qua từng cặp (tháng - tỷ lệ phần trăm) trong dict percentages
    for months_offset, percentage in percentages.items():
        #Tính toán ngày mục tiêu target_date bằng cách cộng thêm months_offset tháng vào ngày bắt đầu start_date, và đặt ngày là ngày cuối cùng của tháng đó (ngày 31)
        target_date = start_date + relativedelta(months=months_offset, day=31)
        value = amount * percentage / 100 #Tính giá trị sẽ điền vào Excel
        #Tìm chỉ số cột tương ứng với ngày mục tiêu target_date trong danh sách header_dates. Nếu tìm thấy cột phù hợp (năm và tháng khớp), vị trí đó được lưu vào col_index. Nếu không tìm thấy, col_index sẽ là None
        col_index = next((idx for idx, date in enumerate(header_dates) if date.year == target_date.year and date.month == target_date.month), None)
        if col_index is not None: #Nếu tìm thấy cột phù hợp (tức là col_index không phải None)
            #Điền giá trị value vừa tính được vào ô của hàng thứ i + 5 (vì dữ liệu bắt đầu từ hàng thứ 5) và cột thứ col_index + 3 (vì dữ liệu ngày tháng bắt đầu từ cột thứ 3)
            sheet.range((i + 5, col_index + 3)).value = value
SOLUTION EXCEL:

=LET(s,B5:B8,d,C4:K4,A5:A8*XLOOKUP(YEAR(d)*12+MONTH(d)-YEAR(s)*12-MONTH(s),--TEXT(LEFT(C1:F1,2),"0;;;\0"),Table1,0))