如图，A列B列不动，C列和D列行值不变，以A列姓名为主让C列姓名和A列相同姓名的对齐（行），D行跟着C行不变。
在E1输入公式=MATCH(C1,A:A,0)然后下拉,接著选中C,D,E列,以E列为标准升序排列即可
转自：https://wenku.baidu.com/view/b7f6198058fb770bf68a5559.html
pandas实现方法：
#! /usr/bin/env python
#-*- coding:utf8 -*-
import pandas as pd
from pandas import DataFrame
import numpy as np
pd.set_option('display.height',10000)
pd.set_option('display.max_rows',5000)
pd.set_option('display.max_columns',5000)
pd.set_option('display.width',10000)
df = pd.read_excel(r"F:\test.xlsx")
col_n = ['C','D']
col_A = ['A','B']
CD = pd.DataFrame(df,columns = col_n)
AB = pd.DataFrame(df,columns = col_A)
# print(CD)
# print(AB)
fi =pd.merge(AB,CD,left_on='A',right_on='C',how='left')
print(fi) zn=fi[fi.isnull().values==True]
print(zn.fillna(0))
改进版
#! /usr/bin/env python
#-*- coding:utf8 -*-
import pandas as pd
from locale import *
from pandas import DataFrame
import numpy as np
writer = pd.ExcelWriter('output.xlsx')
pd.set_option('display.height',10000)
pd.set_option('display.max_rows',5000)
pd.set_option('display.max_columns',5000)
pd.set_option('display.width',10000)
df = pd.read_excel(r"F:\test.xlsx", thousands=',')
print(df.info())
# df = pd.read_excel(r"F:\test.xlsx")
col_A = ['A','B']
col_n = ['C','D']
print(df)
AB = pd.DataFrame(df,columns = col_A)
CD = pd.DataFrame(df,columns = col_n) fi =pd.merge(AB,CD,left_on='A',right_on='C',how='left') # fi['E']=fi.apply(lambda x: (x['D'] - x['B'])/x['D']*100, axis=1).round(2) # fi['E']=fi.apply(lambda x: format((x['D'] - x['B'])/x['D'],'.2%'), axis=1) # fi['E']=(fi.D-fi.B)
# fi['F']=((fi.D-fi.B)/fi.D*100)
fi=fi.assign(E=fi.B-fi.D,F=((fi.B-fi.D)/fi.B)*100).round(2)
# fi=fi.assign(E=fi.B-fi.D,F=((fi.B-fi.D)/fi.B)) def number_to_flag(number):
if number > 0:
return '↑'
elif number == 0:
return '='
else:
return '↓' fi =fi.sort_values(by=['F'],ascending=False) #升序 fi['G'] =fi['F'].map(number_to_flag) fi['E'] = fi['E'].astype('str').str.replace("-","")
fi['F'] = fi['F'].astype('str').str.replace("-","")
fi['F'] = fi.F + '%'
fi=fi.dropna(axis=0)
fi=fi[ ~ fi['F'].str.contains('0.0') ]
fi['E'] = fi['E'].astype('float64')
print(fi)
print(fi.dtypes)
fi.to_excel(writer)
writer.save()
最终版
#! /usr/bin/env python
#-*- coding:utf8 -*-
import sys
reload(sys)
sys.setdefaultencoding('gbk')
from locale import *
from pandas import DataFrame
import pandas as pd
import numpy as np
writer = pd.ExcelWriter('output.xlsx')
pd.set_option('display.height',10000)
pd.set_option('display.max_rows',5000)
pd.set_option('display.max_columns',5000)
pd.set_option('display.width',10000)
df = pd.read_excel(r"F:\test.xlsx")
pd.options.display.float_format = '{:,}'.format print(df.info())
# df = pd.read_excel(r"F:\test.xlsx")
col_A = ['A','B']
col_n = ['C','D'] AB = pd.DataFrame(df,columns = col_A)
CD = pd.DataFrame(df,columns = col_n) fi =pd.merge(AB,CD,left_on='A',right_on='C',how='left') # fi['E']=fi.apply(lambda x: (x['D'] - x['B'])/x['D']*100, axis=1).round(2) # fi['E']=fi.apply(lambda x: format((x['D'] - x['B'])/x['D'],'.2%'), axis=1) # fi['E']=(fi.D-fi.B)
# fi['F']=((fi.D-fi.B)/fi.D*100)
fi=fi.assign(E=fi.B-fi.D,F=((fi.B-fi.D)/fi.B)*100).round(2)
# fi=fi.assign(E=fi.B-fi.D,F=((fi.B-fi.D)/fi.B)) def number_to_flag(number):
if number > 0:
return '↓'
elif number == 0:
return '='
else:
return '↑' fi =fi.sort_values(by=['F'],ascending=False) #升序 fi['G'] =fi['F'].map(number_to_flag) fi['E'] = fi['E'].astype('str').str.replace("-","")
fi['F'] = fi['F'].astype('str').str.replace("-","")
fi['F'] = fi.F + '%'
fi=fi.dropna(axis=0) fi=fi[ ~ fi['F'].str.contains('0.0') ]
fi['E'] = fi['E'].astype('float64')
fi['B'] = fi['B'].astype('float64') print(fi)
# print(fi.dtypes)
fi.to_excel(writer)
writer.save()
# fi.to_html('files.html',escape=False,index=False,sparsify=True,border=1,index_names=False,header=True)