快捷導航

教你使用Pandas直接核算Excel中的快遞費用

更新時間：2021年05月12日 15:27:35 作者：繁華落盡，尋一世真情

文中仔細說明了怎么根據(jù)賬單核算運費.首先要確定運費規(guī)則,然后根據(jù)運費規(guī)則編寫代碼,生成核算列（快遞費 = 省份*重量）,最后輸入賬單,進行核算.將腳本件生成EXE文件,就可以使用啦,需要的朋友可以參考下

一、確定核算規(guī)則

在這里插入圖片描述

二、根據(jù)核算規(guī)則編寫代碼，生成核算列

# -*- coding:utf-8 -*- 
import pandas as pd 
from math import ceil
import os

def account(adress,weight):
    
    if adress == "湖南":
        if weight <= 3:
            totel = 2.5
        elif  (weight >= 3) and (weight<=5):
            totel = 3.5 + ceil((weight-3))*1
        else:
            totel = ceil(weight)*1
        return totel
            
    elif adress in ["河北","天津","山西","浙江","江蘇","安徽","福建","山東","江西","廣東","廣西","河南","湖北","陜西","四川","重慶","云南","貴州"]:
        if weight <= 3:
            totel = 2.5
        elif  (weight >= 3) and (weight<=5):
            totel = 3.5 + ceil((weight-3))*1
        else:
            totel = ceil(weight)*2 
        return totel
    
    elif adress in ["深圳","北京","上海"]:
        if weight <= 3:
            totel = 3.3
        elif  (weight >= 3) and (weight<=5):
            totel = 3.5 + ceil((weight-3))*1.5
        else:
            totel = ceil(weight)*2
        return totel
            
    elif adress in ["海南","遼寧","黑龍江","吉林"]:
        if weight <= 3:
            totel = 2.5
        elif  (weight >= 3) and (weight<=5):
            totel = 3.5 + ceil((weight-3))*2.5
        else:
            totel = ceil(weight)*3
        return totel
    elif adress in ["內(nèi)蒙古","甘肅","寧夏","青海"]:
        if  weight <= 1:
            totel = 9
        else:
            totel = 9 + ceil(weight-1)*6 
        return totel
    elif adress == "新疆":
        if  weight <= 1:
            totel = 15
        else:
            totel = 15 + ceil(weight-1)*12 
        return totel
    elif adress == "西藏":
        if  weight <= 1:
            totel = 16
        else:
            totel = 15 + ceil(weight-1)*18 
        return totel    
    else:
        print("你輸入的省份不合法?。?！")
    
file_path = input("請輸入文件路徑：")   
sheet_name = input("請輸入工作簿名稱：")
pf = pd.read_excel(file_path,sheet_name=sheet_name)
#獲取省份一列
pro = pf["省份"].values.tolist()
#獲取重量一列
wt = pf["重量"].values.tolist()
#核算列
totel = []
for p,w in zip(pro,wt):
    print(p,w)
    totel.append(account(p,w))

pf["最新核算結(jié)果"] = totel
file_name = os.path.basename(file_path)
pf.to_excel(os.path.join(os.path.dirname(file_path),os.path.basename(file_path).split(".")[0]+sheet_name+"最新核算結(jié)果"+".xlsx"))

三、輸入賬單，進行核算。

在這里插入圖片描述

在腳本文件目錄中執(zhí)行pyinstaller -F hesuan.py 進行打包exe文件，如果為安裝pyinstaller,使用pip install pyinstaller 安裝。點擊運行打包后的exe文件，輸入文件的路徑名和sheet名，就可以進行自動核算，腳本運行完成后會自動保存一個新的Excel文件。

在這里插入圖片描述