Excel学习汇总

1. 2003 Excel 筛选单元格背景颜色以及文字颜色
   通过辅助列来筛选相关事宜

获取文字颜色
Excel2003工具栏    
        插入->名称->定义
            输入findTextColor
                输入公式 =get.cell(24,$A2) //$后面跟的是引用位置，这样得到字体颜色的序列号
                    在引用位置输入 =findTextColor //批量获取通过向下复制当前公式
                        通过序列号进行筛选

获取单元格背景颜色
Excel2003工具栏    
        插入->名称->定义
            输入findBackgroundColor
                输入公式 =get.cell(63.$A2)//$后面跟的是引用位置，这样得到单元格背景颜色序列号
                    在引用位置输入 =findBackgroundColor //批量获取通过向下复制当前公式
                        通过序列号进行筛选

COUNTIF函数

        =COUNTIF(I:I,"3")//计数I所有单元格中数值为3的数量

工作应用TEXT and COUNTIF函数

    工作需求概括：
        单元格值是否存在于一个区域中，存在则通过条件格式填充背景色为红色，不存在则通过条件格式填充背景色为绿色

        Example：Excel2007
            ![最终样式](http://img.blog.csdn.net/20160726160143133)
            Excel面板——>公式——>定义名称——>UserExistDemo
                    输入公式=TEXT(COUNTIF(Sheet2!$A$2:$A$86,Sheet1!B1),"存在;;不存在;")
                        单独取一列键入公式 =UserExitDemo
                            鼠标拖住下拉,因为公式使用绝对引用，所以查询区域不会跟着改变
            Excel面板——>开始——>条件格式——>新建规则
                    只用包含以下内容的单元格设置格式
                        单元格值 等于 存在 
                            设置格式样式：背景色为红色  
                    重复操作新建规则 
                        单元格值 等于 不存在 
                            设置格式样式：背景色为绿色

Excel引用位置解释

A1 在下拉拖动引用时,会变成引用A2,A3,A4...,右拉拖动时引用变成B1,C1,D1....

A$1
当你下拉复制时想保证引用的只是A1单元格时,A1就要加$符号,成A$1,这样在下拉时能保证对A列第一行的相对引用(即保持行号在引用时不产生变动)

$A1
当你右拉复制时想保证引用的只是A1单元格时,A1就要加$符号,成$A1,这样在右拉时能保证对A列第一行的相对引用(即保持列标在引用时不产生变动)

$A$1
当你在下拉和右拉复制时想保证引用的只是A1单元格时,A1就要加$符号,成$A$1,这样在下拉和右拉时能保证对A列第一行的绝对引用(即保持行号列标在引用时不产生变动)

单元格内公式与字符串共存

    ="背景颜色代码:"&（GET.CELL(63,Sheet!C3)）
    ="文字颜色代码:"&（GET.CELL(24,Sheet!C3)）

通过身份证获取信息

身份证规律
 (1）前1、2位数字表示：所在省份的代码； 
（2）第3、4位数字表示：所在城市的代码； 
（3）第5、6位数字表示：所在区县的代码； 
（4）第7~14位数字表示：出生年、月、日； 
（5）第15、16位数字表示：户口所在地派出所的代表号码； 
（6）第17位数字表示性别：奇数表示男性，偶数表示女性； 
（7）第18位数字是校检码：代表个人信息，是根据前十七位数字计算出来的

    通过身份证第17位计算性别
        公式: =IF(MOD(MID(A2,17,1),2)=1,"男","女")
        Explain : MID（A2，17，1）取单元格A2中内容的第17位
                  MOD(number,divisor)求余数函数，余数为1则是奇数，余数为2则为偶数
                  奇数：男
                  偶数：女

    通过身份证取出出生年/月/日
        公式: =MID(A1,7,4)&"/"&MID(A1,11,2)&"/"&MID(A1,13,2)
        Explain : MID函数 取单元格内容中部分内容
                  7-10位：出生年份
                  11-12位：出生月份
                  13-14位：出生日期
    计算年龄
        设置单元格格式为 数值 小数位数 设置为 0
        公式 =(TODAY()-(MID(A1,7,4)&"/"&MID(A1,11,2)&"/"&MID(A1,13,2)))/365
        Explain : TODAY()函数 查询当前年月日

更新时间：2016年8月1日 By:EngineerZhong