尘风
红尘往事,一切随风

因存在位置未知的空值,需要两两空值判断再进行比较,所以5列两两相比有10种可能,需要全面考虑,缺一都可能导致数据不准确(空值位置影响)

一、初始版本:案例中当前单元格的函数:=AND(IF(OR(B3="",C3=""),TRUE,B3=C3),IF(OR(C3="",D3=""),TRUE,C3=D3),IF(OR(D3="",E3=""),TRUE,D3=E3),IF(OR(E3="",F3=""),TRUE,E3=F3),IF(OR(B3="",F3=""),TRUE,B3=F3),IF(OR(B3="",D3=""),TRUE,B3=D3),IF(OR(B3="",E3=""),TRUE,B3=E3),IF(OR(C3="",F3=""),TRUE,C3=F3),IF(OR(C3="",E3=""),TRUE,C3=E3),IF(OR(D3="",F3=""),TRUE,D3=F3))

 

注意:使用此方法前请根据实际需要同时比较的列数,进行排列组合,增加或减少比较种类的数量(即if的数量)

 

一、升级版本:

=IF(AND(IF(OR(B3="",C3=""),TRUE,B3=C3),IF(OR(B3="",F3=""),TRUE,B3=F3),IF(OR(B3="",D3=""),TRUE,B3=D3),IF(OR(B3="",E3=""),TRUE,B3=E3)),"",$B$2&":"&B3)
&" "&IF(AND(IF(OR(B3="",C3=""),TRUE,B3=C3),IF(OR(C3="",D3=""),TRUE,C3=D3),IF(OR(C3="",F3=""),TRUE,C3=F3),IF(OR(C3="",E3=""),TRUE,C3=E3)),"",$C$2&":"&C3)
&" "&IF(AND(IF(OR(C3="",D3=""),TRUE,C3=D3),IF(OR(D3="",E3=""),TRUE,D3=E3),IF(OR(B3="",D3=""),TRUE,B3=D3),IF(OR(D3="",F3=""),TRUE,D3=F3)),"",$D$2&":"&D3)
&" "&IF(AND(IF(OR(D3="",E3=""),TRUE,D3=E3),IF(OR(E3="",F3=""),TRUE,E3=F3),IF(OR(B3="",E3=""),TRUE,B3=E3),IF(OR(C3="",E3=""),TRUE,C3=E3)),"",$E$2&":"&E3)
&" "&IF(AND(IF(OR(E3="",F3=""),TRUE,E3=F3),IF(OR(B3="",F3=""),TRUE,B3=F3),IF(OR(C3="",F3=""),TRUE,C3=F3),IF(OR(D3="",F3=""),TRUE,D3=F3)),"",$F$2&":"&F3)

 

 思路:跟当前彩票站有关的对比放一起,若其中一个有差异即返还当前彩票站比例,逐一彩票站返回即可(空值为无差异的项目)

 

posted on 2023-03-31 14:48  一个行者  阅读(437)  评论(0编辑  收藏  举报