一张数据表格，每日导入一列新的数据，如何能准确的对每一格异常数据进行突出显示？

Question

如上图所示，我使用代码每日导入最新的一列数据，怎么才能对异常的数据自动进行突出显示？

比方说M76 数值异常，像我现在编写的代码，只有和前一天的数据进行大于小于多少比例的比较，M76和N76都会凸显。

import openpyxl as op
import pandas as pd
import time
import datetime
td = str(time.strftime('%Y-%m',time.localtime()))       #为获得导出数据所存的未见地址
td2 = str(time.strftime('%m-%d',time.localtime()))      #为获得导出数据所存的未见地址
def getYesterday():                                 #获取昨日日期
    today = datetime.date.today()
    oneday = datetime.timedelta(days=1)
    yesterday = today - oneday
    return yesterday
yd =  str(getYesterday())  
dir = r"E:\SynologyDrive\☆☆☆☆☆☆☆☆☆☆☆☆☆☆诚道数据导出☆☆☆☆☆☆☆☆☆☆☆☆☆☆\2021年诚道数据"   
filename = dir+"\\"+td+"\\"+"QBSB"+yd+".xls"  
zrlm = '%s月%s日'%(yd[5:7],yd[8:10]) 
df = pd.read_excel(filename,header = 3)              
df.set_index(["设备编号"],drop=True,inplace=True)   

from openpyxl.styles import colors, Font, Fill, NamedStyle
from openpyxl.styles import PatternFill, Border, Side, Alignment


# 字体
font1 = Font(name='宋体', size=9, b=True,color='ffff00')  #字体一，黄色，加粗，为异常数据填充
font2 = Font(name='宋体', size=9)

# 边框
line_t = Side(style='thin', color='000000')  # 细边框
line_m = Side(style='medium', color='000000')  # 粗边框
border1 = Border(top=line_m, bottom=line_m, left=line_m, right=line_m)
# 与标题相邻的边设置与标题一样
border2 = Border(top=line_t, bottom=line_t, left=line_t, right=line_t)


# 填充
fill1 = PatternFill('solid', fgColor= 'ff0000')
fill2 = PatternFill('solid', fgColor= '696969')
# 对齐
alignment = Alignment(horizontal='center', vertical='center')

# 将样式打包命名
sty1 = NamedStyle(name='sty1'+yd,font=font1, fill=fill1,border=border1,alignment=alignment)
sty2 = NamedStyle(name='sty2'+yd,font=font2, border=border2,alignment=alignment)
sty3 = NamedStyle(name='sty3'+yd,font=font2, border=border2,fill=fill2,alignment=alignment)


wb = op.load_workbook(r'E:\SynologyDrive\运维 联通一期\数据告警\1期数据告警202111.xlsx',data_only= False)
ws1 = wb['过车数据']
ws2 = wb['违法数据']

#下面把过车数据导入过车告警
df_gcgj = pd.read_excel(r'E:\SynologyDrive\运维 联通一期\数据告警\1期数据告警202111.xlsx',sheet_name= '过车数据',header=0)  
names = df_gcgj["设备编号"].values.tolist()


df_gcgj.set_index(["设备编号"],drop=True,inplace=True)
for i in names:                                     
    try:
        df_gcgj.loc[i,zrlm] = df.loc[i,"过车数据"]     
    except:pass
    
for col in ws1.iter_cols():                       
    for cell in col:
        if cell.value == zrlm:
            m = int(cell.column)      
            
zrsj = df_gcgj[zrlm].values.tolist()    
for j in range(len(zrsj)):
    ws1.cell(j+2,m).value = zrsj[j]      

for x in range(2,ws1.max_row+1):
    vtd = ws1.cell(row = x,column = m).value
    vyd = ws1.cell(row = x,column = m-1).value
    if  ws1.cell(row = x,column = 6).value in list(('路泽太与永长路移机至此，做全景相机使用','纯视频球机无过车和违法抓拍','仅作为视频相机')):
        ws1.cell(row = x,column = m).style = 'sty3'    
        ws1.cell(row = x,column = m).value = '/'
    else:
        if vyd==0:
            ws1.cell(row = x,column = m).style = sty1
        else:
            try:
                if vyd > 10000:
                    if vtd-vyd*1.2>0:
                        ws1.cell(row = x,column = m).style = sty1
                    elif vyd*0.8-vtd>0:
                        ws1.cell(row = x,column = m).style = sty1
                    else:
                        ws1.cell(row = x,column = m).style = sty2
                elif 2000<vyd<=10000:
                    if vtd-vyd*1.3>0:
                        ws1.cell(row = x,column = m).style = sty1
                    elif vyd*0.7-vtd>0:
                        ws1.cell(row = x,column = m).style = sty1
                    else:
                        ws1.cell(row = x,column = m).style = sty2
                else:                          
                    if vyd<2000:    
                        if vtd-vyd*1.5>0:
                            ws1.cell(row = x,column = m).style = sty1
                        elif vyd*0.5-vtd>0:
                            ws1.cell(row = x,column = m).style = sty1
                        else:
                            ws1.cell(row = x,column = m).style = sty2
            except:pass

            
wb.save(r'E:\SynologyDrive\运维 联通一期\数据告警\1期数据告警202111.xlsx' )
print('一期数据告警表制作完毕')

求思路

Answer 1

你好，我是有问必答小助手，非常抱歉，本次您提出的有问必答问题，技术专家团超时未为您做出解答


本次提问扣除的有问必答次数，将会以问答VIP体验卡（1次有问必答机会、商城购买实体图书享受95折优惠）的形式为您补发到账户。


因为有问必答VIP体验卡有效期仅有1天，您在需要使用的时候【私信】联系我，我会为您补发。