ExcelTip.Net留存知识帖 ---【注:附件之前被网盘供应商清空后,现已修复-现已修复-现已修复为本地下载!】
现在位置:首页 > E问E答 > 函数与公式 > 如何将一组数的个位数对应到标有0—9的列?

如何将一组数的个位数对应到标有0—9的列?

作者:绿色风 分类: 时间:2022-08-17 浏览:92
楼主
悟空师弟
Q:如何将一组数的个位数对应到标有0—9的列?
  如下图:只要I列至R列中第2行的数字在B列至H列对应行的个位出现过就显示该数字,否则显示为空。

 

A:数组公式:
  1. =IF(OR(MOD($B3:$H3,10)=I$2),I$2,"")
上面公式在B列至H没有输入数据的情况下,I列会显示0,如果让这种情况下I列的0值不显示再加个IF即可,如下数组公式:
  1. =IF(COUNT($B3:$H3),IF(OR(MOD($B3:$H3,10)=I$2),I$2,""),"")
详见附件:

如何将一组数的个位数对应到标有0—9的列?.rar
2楼
悟空师弟
解题思路:
    1、通常看到这种情况会想到用IF逐个判断,比如判断I2中的0是否在B3:H3这个区域的个位是否出现过,
一、常规思路:
    1、先判断B3,再判断C3,再判断D3……直到H3,所有单元格逐个判断。
    2、判断前先要逐个取出每个数值个位上的数字,可用RIGHT或MOD函数,
       比如
       =RIGHT(B3) (取B3右边第1个字符)(这样取出来的是文本型数值,还需要转换成数值型数字再进行比较,如=--RIGHT(B3))
       或
       =MOD(B3,10) (取B3与10相除的余数)
    3、判断是否出现过可用“=”号进行比较,
       比如=MOD(B3,10)=I2,如果相等,则为出现过,如果不等则为没出现过。
    4、逐个判断完后只要有一个出现过即可,可用OR函数达到,
       公式: =OR(MOD(B3,10)=I2,MOD(C3,10)=I2,MOD(D3,10)=I2,MOD(E3,10)=I2,MOD(F3,10)=I2,MOD(G3,10)=I2,MOD(H3,10)=I2)
       只要有一个出现过就返回TRUE(真),
       如果全部都没出现过则返回FALSE(假)。
    5、再用IF函数返回结果,“真”则返回I2的值,“假”则返回""(空值)
       公式: =IF(OR(MOD(B3,10)=I2,MOD(C3,10)=I2,MOD(D3,10)=I2,MOD(E3,10)=I2,MOD(F3,10)=I2,MOD(G3,10)=I2,MOD(H3,10)=I2),I2,"")
二、利用数组公式简化公式:
    1、取个位上的数字可一次性取出B3:H3这7个单元格中所有个位上数字,公式如下:
      =MOD(B3:H3,10) (输入此公式按F9可以看到一组数: {0,6,3,9,2,7,5})
    2、再与I2单元格进行比较,公式为:
      =MOD(B3:H3,10)=I2 (结果为一组逻辑值: {TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE})  
    3、外套一个OR函数,公式为:
      =OR(MOD(B3:H3,10)=I2)  (结果为 TRUE)
    4、用IF函数返回结果,公式为:
      =IF(OR(MOD(B3:H3,10)=I2),I2,"")  (结果为:0)
    5、上面公式在B列至H没有输入数据的情况下,I列会显示0,如果让这种情况下I列的0值不显示再加个IF判断B3:H3是否全部为空即可,判断是否全部为空方法用COUNT函数,如下数组公式:
      =IF(COUNT(B3:H3),IF(OR(MOD(B3:H3,10)=I2),I$2,""),"")
    6、因公式要右拖再下拖,故要加上绝对引用符号 $
      公式拖动时B3:H3的列不能变但行要变则写为$B3:$H3,I2的列要变行不变则写为I$2,得到最终的公式为:
      =IF(COUNT($B3:$H3),IF(OR(MOD($B3:$H3,10)=I$2),I$2,""),"")
注:数组公式的特点:录入完公式后按 Ctrl + Shift + Enter 三键结束,否则出错。

详见附件:
如何将一组数的个位数对应到标有0—9的列?-公式解析.rar
3楼
wjc2090742
第2个公式,如果B:H中不是全无数据或全部有数据,0的判断会有出入。

另外,既然用or,不如一or到底。
  1. =IF(OR(($B3:$H3<>"")*(MOD($B3:$H3,10)=I$2)),I$2,"")
4楼
悟空师弟


的确,这是当时没考虑到的,翁版高见!

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素, 经与ExcelTip.Net站长Apolloh商议并征得其同意, 现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示, 供有需要的人士查询使用,也慰缅曾经的论坛时代。 所示各个帖子的原作者如对版权有异议, 可与本人沟通提出,或于本站点留言,我们会尽快处理。 在此,感谢ExcelTip.Net站长Apolloh的支持,感谢本站点所有人**绿色风(QQ:79664738)**的支持与奉献,特此鸣谢!
------本人网名**KevinChengCW(QQ:1210618015)**原ExcelTip.Net总版主之一

评论列表
sitemap