小项目-根据不同判别关系求平均值，和中位值

1. excel 数据中有空值
2. 判别关系 1
   1级的经销店，所有指标求平均值和中位值
   2级的经销店，以战区最判别依据，求所有指标的平均值和中位值
   3级的经销店，以战区作为判别依据，求所有指标的平均值和中位值
   其他级的经销店，以省份作为判别依据，求所有指标的平均值和中位值

import os
from openpyxl import load_workbook
from openpyxl import Workbook


wb = Workbook()
ws = wb.active 
wb_second_index = load_workbook("statistics_all_index.xlsx")
#print(wb_second_index.sheetnames)
#ws_data = wb_second_index.get_sheet_by_name('原始数据')
#ws_key = wb_second_index.get_sheet_by_name('关键字')
ws_data = wb_second_index['原始数据']
ws_key = wb_second_index['关键字']
#print(ws_data["A3"].value)
#print(ws_key["D2"].value)
#print(ws_key.cell(row=2,column=4).value)
list_key1 = []   #级别
list_key2 = []   #城市
list_key3 = []   #省份
list_key4 = []   #战区
for x in range(2,8):
    list_key1.append(ws_key.cell(row=x,column=4).value)
#print(list_key1)
for x in range(2,247):
    list_key2.append(ws_key.cell(row=x,column=3).value)
#print(list_key2)
for x in range(2,31):
    list_key3.append(ws_key.cell(row=x,column=2).value)
#print(list_key3)
for x in range(2,7):
    list_key4.append(ws_key.cell(row=x,column=1).value)
#print(list_key4)
for x in range(2,559):
    ws.cell(row=x,column=1).value=ws_data.cell(row=x,column=1).value
    ws.cell(row=x,column=2).value=ws_data.cell(row=x,column=2).value
    ws.cell(row=x,column=3).value=ws_data.cell(row=x,column=3).value
    ws.cell(row=x,column=5).value=ws_data.cell(row=x,column=5).value
    ws.cell(row=x,column=4).value=ws_data.cell(row=x,column=4).value
    for y in range(6,113,2):
        ws.cell(row=1,column=y).value=str(ws_data.cell(row=1,column=((y-6)/2+6)).value) + "平均值"
        ws.cell(row=1,column=y+1).value=str(ws_data.cell(row=1,column=((y-6)/2+6)).value)+ "中位值"
        #for a in range(0,4):
        if ws_data.cell(row=x,column=5).value==list_key1[0]:   #判断级别，1级
            average_numbers = cal_average(ws_data,list_key1,y,0,5)
            max_numbers = cal_max(ws_data,list_key1,y,0,5)
            if (average_numbers=="值有问题") or (max_numbers=="值有问题"):
                ws.cell(row=x,column=y).value = "值有问题"
                ws.cell(row=x,column=y+1).value = "值有问题"
            else:
                ws.cell(row=x,column=y).value = average_numbers
                ws.cell(row=x,column=y+1).value = ((average_numbers)+(max_numbers))/2
            #ws.cell(row=x,column=y).value = cal_average(ws_data,list_key1,y,0,5)
            #ws.cell(row=x,column=y+1).value = cal_max(ws_data,list_key1,y,0,5)
        if ws_data.cell(row=x,column=5).value==list_key1[1]:   #判断级别，2级
            for a in range(0,5):
                if ws_data.cell(row=x,column=2).value==list_key4[a]:
                    average_numbers = cal_average(ws_data,list_key4,y,a,2)
                    max_numbers = cal_max(ws_data,list_key4,y,a,2)
                    if (average_numbers=="值有问题") or (max_numbers=="值有问题"):
                        ws.cell(row=x,column=y).value = "值有问题"
                        ws.cell(row=x,column=y+1).value = "值有问题"
                    else:
                        ws.cell(row=x,column=y).value = average_numbers
                        ws.cell(row=x,column=y+1).value = ((average_numbers)+(max_numbers))/2
                    #ws.cell(row=x,column=y).value = cal_average(ws_data,list_key4,y,a,2)
                    #ws.cell(row=x,column=y+1).value = cal_max(ws_data,list_key4,y,a,2)
           
        if ws_data.cell(row=x,column=5).value==list_key1[2]:   #判断级别，3级，
            for a in range(0,5):
                if ws_data.cell(row=x,column=2).value==list_key4[a]:
                    average_numbers = cal_average(ws_data,list_key4,y,a,2)
                    max_numbers = cal_max(ws_data,list_key4,y,a,2)
                    if (average_numbers=="值有问题") or (max_numbers=="值有问题"):
                        ws.cell(row=x,column=y).value = "值有问题"
                        ws.cell(row=x,column=y+1).value = "值有问题"
                    else:
                        ws.cell(row=x,column=y).value = average_numbers
                        ws.cell(row=x,column=y+1).value = ((average_numbers)+(max_numbers))/2
                    #ws.cell(row=x,column=y).value = cal_average(ws_data,list_key4,y,a,2)
                    #ws.cell(row=x,column=y+1).value = cal_max(ws_data,list_key4,y,a,2)
        #判断级别，4级
            
        if (ws_data.cell(row=x,column=5).value==list_key1[3]) or (ws_data.cell(row=x,column=5).value==list_key1[4]) or (ws_data.cell(row=x,column=5).value==list_key1[5]):
            for a in range(0,29):
                if ws_data.cell(row=x,column=3).value==list_key3[a]:
                    average_numbers = cal_average(ws_data,list_key3,y,a,3)
                    max_numbers = cal_max(ws_data,list_key3,y,a,3)
                    if (average_numbers=="值有问题") or (max_numbers=="值有问题"):
                        ws.cell(row=x,column=y).value = "值有问题"
                        ws.cell(row=x,column=y+1).value = "值有问题"
                    else:
                        ws.cell(row=x,column=y).value = average_numbers
                        ws.cell(row=x,column=y+1).value = ((average_numbers)+(max_numbers))/2
                    #ws.cell(row=x,column=y).value = cal_average(ws_data,list_key3,y,a,3)
                    #ws.cell(row=x,column=y+1).value = cal_max(ws_data,list_key3,y,a,3)
        #Aprint(count)
        #print(total)
    #print(count)
    #print(total)
            #if (total==0) or (count==0):
                #ws.cell(row=b,column=6).value = "有问题"
            #else:
                #ws.cell(row=b,column=6).value = total / count 
    print("已处理第"+str(x)+"行数据")
                    
wb.save('test.xlsx')
print("完成")

def cal_average(ws_data,list_key,y,a,index):
#求平均值
    count = 0
    total = 0
    for b in range(2,559):
                
        if ws_data.cell(row=b,column=index).value== list_key[a]:
                
            if ws_data.cell(row=b,column=((y-6)/2+6)).value !=None:
                count = count + 1
                value = ws_data.cell(row=b,column=((y-6)/2+6)).value
                total = total + value
    #Aprint(count)
    #print(total)
    if (count==0) or (total==0):
        result="值有问题"
    else:
        result=total/count
    return result
print(count)
print(total)

def cal_max(ws_data,list_keys,y,a,index):
    temp_list=[]
    for b in range(2,559):
        if ws_data.cell(row=b,column=index).value== list_keys[a]:      
            if ws_data.cell(row=b,column=((y-6)/2+6)).value !=None:
                temp_list.append(ws_data.cell(row=b,column=((y-6)/2+6)).value)
    if temp_list:
        number_max=max(temp_list)
    else:
        number_max="值有问题"
    return number_max