excel常用函数大全

excel 职场办公, 数据分析常用函数, 分类解析，用户可以更快速、更准确地完成数据处理和计算工作，提高工作效率！

函数

• 日期时间
  • 快捷键
    • ctrl + shift + ~ 常规
    • ctrl + shift + # 日期
    • ctrl + shift + @ 时间
    • ctrl + ; 当前日期
    • ctrl + shift + ; 当前时间
  • 文本转换
    • timevalue
      • 文本--->时间
    • datevalue
      • 文本--->日期
  • 日期
    • 提取拼接
      • year 年
      • month 月
      • day 日
      • date 日期
    • 月
      • datedif 日期差
        • 第三参数：y,m,d,ym,md,yd
      • eomonth 月底日期
        • 参数：日期，偏移月份
      • edate 日期偏移
        • 偏移月，日不变
    • 周
      • weekday 本周第几天
        • 注意第二参数
      • weeknum 今年第几周
    • 工作日
      • workday
        • 例：3个工作日后日期
      • workday.intl
        • 自定义 ，1 表示休息，0 表示上班。例双休："0000011"
      • networkdays
        • 两个日期间工作日
      • networkdays.intl
        • 两个日期间工作日，自定义上班日
    • 日历制作
  • 时间
    • 提取拼接
      • hour 时
      • minute 分
      • second 秒
      • time 时间
• 数字函数
  • 计数
    • count 计算数字单元格
    • counta 计算非空
    • countblank 计算空白
    • counta(区域&"") 计算全部单元格
    • countif
      • 单列不重复计算
      • 条件可为引用
    • countifs
      • 多列不重复计算
  • 平均
    • average
      • 数字区域平均
    • averagea
      • 非空区域平均
    • averageif
      • 条件平均，可计算后几位平均值
    • trimmean
      • 去掉极值后平均，注意第二参数
  • 求和
    • sum
      • 合并单元格求和
        • sum从下往上求和
    • sumproduct
      • 参数数组维度相同
      • 非数字元素当0处理
    • sumif
      • 条件数组
      • 计算区域if选择
      • 相对区域条件
    • sumifs
      • 通配符
      • 数组
      • 引用区域条件
  • 乘积类
    • product 乘
      • 注：乘积区域文本视为1
    • sumproduct
      • 数组先相乘后相加
    • mmult
      • 行列式乘积
    • power 幂
      • 简写 ^
    • sqrt 平方根
  • subtotal
    • 功能
      • 以上各模块都支持
    • 优点
      • 可以计算所有值
      • 可以只计算可见值
        • 隐藏单元格
        • 筛选单元格
  • 数字操作
    • 截取
      • int
        • 向下取整
      • trunc
        • 截尾取数
    • 四舍五入
      • round
        • <5舍去，>=5向着远离0方向进位
      • roundup
        • 全部向着远离0的方向进位
      • rounddown
        • 同trunc
    • 倍数取数
      • ceiling
        • 按参数倍数向上取数
      • floor
        • 按参数倍数向下取数
    • 取余
      • mod
        • 符号相同，求余的部分
        • 符号不同，求差的部分
        • 结果值与除数的正负相同
    • 绝对值
      • abs
    • 进制转换
      • base
    • 错误值
      • NA生成错误值
    • rank 排名
      • 多列排名加括号
    • 阿拉伯和罗马转换
      • arabic 罗马-->阿拉伯
      • roman 阿拉伯-->罗马
    • 正负标识 sign
    • 判断奇偶
      • iseven 偶
      • isodd 奇
    • N函数 文本转0
• 文本
  • 字符串操作类
    • 字符串连接
      • concat
      • concatenate
      • phonetic 合并文本，忽略数字
    • 字符串截取
      • left 从左截取
      • right 从右截取
      • mid 从中间截取
    • 字符串查询
      • len 字符长度
        • lenb 字节长度
      • find
        • 返回字符位置，区分大小写 findb 字节
      • search
        • 同find，不区分大小写，可使用通配符 searchb 字节
    • 字符替换
      • replace
        • 替换一段值，参数为0可插入字符
      • substitute
        • 查找字符并替换
    • 数字字符转换
      • char
        • 数字转换字符
      • code
        • 字符转换数字
    • 字符比较
      • =
        • 不区分大小写
      • exact
        • 区分大小写
    • rept
      • 重复
    • T
      • 非文本转换文本
    • 通配符
      • ? 单个字符
      • * 多个字符
      • ~ 代表通配符
  • 字符串格式类
    • 英文大小写
      • proper
        • 每个词首字母大写，其余小写
      • upper
        • 全部大写
      • lower
        • 全部小写
    • 去除空格
      • trim
        • 去除字符串两端空格
    • 人民币美元
      • RMB
        • 数字转换人民币格式
      • DOLLAR
        • 数字转换美元格式
    • 文本转换
      • numbervalue
        • 文本转数字
      • value
        • 文本、日期转数字
      • clean
        • 清除非打印字符，无法计算时使用
      • numberstring
        • 数字转汉字
• 单元格格式
  • 内容格式
    • [>0]格式;[<0]格式;0格式;文本格式
      • 数字范围可变
  • 字体颜色
    • [颜色10][>50]"优秀";[洋红][<20]"差";"合格";
      • [黑色]、[白色]、[红色]、[蓝色]、[绿色]、[洋红]
      • 颜色1.....颜色56
  • 占位符
    • G/通用格式
      • 常规
    • 0
      • 数字占位符，当数字比代码符少时用0占位
    • #
      • 数字占位符，只显示有效数字，不显示无意义的0值
    • ？
      • 数字占位符，当数字比代码少时用空格占位，可用于分数显示
    • @
      • 文本占位符，表示输入文本本身
  • 特殊代码
    • _
      • 留出与后面字符一样宽的空位
    • *
      • 重复下一个字符，直到充满列宽
    • ,
      • 千位分隔符，一般与“#”配合使用，简化数字
    • \
      • 格式化，强制显示。
    • !
      • 同\，显示不能显示出的字符
    • %
      • 将数字缩小100倍
  • 汉字格式
    • [dbnum1]G/通用格式
      • 1、2、3
  • 日期时间
    • 日期
      • 示例：yyyy-m-d
    • 时间
      • 示例： AM/PM hh:mm:ss.0
  • TEXT函数
    • text函数可以直接以上格式做参数
• 查找匹配
  • vlookup
    • 精确匹配
      • 参数为0
    • 模糊匹配
      • 参数为1
    • 反向匹配
      • 2列
        • if({1,0},列1,列2)
      • 多列
        • choose({1,2,3...},列1,列2,列3...)
    • 数组
      • 第一、第三参数可用数组
  • hlookup
    • 同vlookup，横向
  • lookup
    • 二参数
      • 要求：条件第一列，结果最后一列
    • 三参数
      • 条件不用在第一列
    • 升序排列
      • 就算不是升序lookup也当做升序处理
    • 二分法查找
  • match
    • 精确匹配
      • 参数0
    • 模糊匹配
      • 升序
        • 参数1
      • 降序
        • 参数-1
    • 二分法·
      • 模糊匹配使用二分法，效率高
      • 模糊匹配中查找值重复，则找到最后一个位置
  • index
    • 三参数
      • 二维区域
      • 一维区域
    • 四参数
      • 增加多区域选择
  • choose
    • 相当于java的switch
    • 可制作自定义区域
  • frequency
    • 分布函数
  • hyperlink
    • 网址链接
    • 文件、文件夹
      • 绝对地址
      • 相对地址
    • 局域网
      • 其他同事电脑共享
  • small
    • 最小的数提取
  • large
    • 最大的数提取
  • mode.mult
    • 众数
      • 数字
      • 文本
        • 用match转换数字
  • median
    • 中数
• 区域函数
  • offset
    • 区域平移
    • 数组
      • 表示多维区域，分别计算
  • address
    • 数字-->地址
  • indirect
    • 地址-->区域
  • rows
    • 区域所占行数
  • areas
    • 引用区域包含不连续区域数
  • cell
    • 第一参数
      • address
        • 文本地址
      • row
        • 行
      • col
        • 列
      • filename
        • 文件名
      • contents
        • 内容
    • 第二参数
      • 选择区域，缺省表示鼠标区域左上单元格
  • 数组
    • transpose转置
    • munit
      • 生成斜角矩阵
  • 区域交叉
    • 两个区域中间空格
    • 配合定义名称计算交叉值
  • 定义名称
    • 根据所选内容创建
  • 逻辑
    • xor异或
    • 判断函数
      • isformula是否公式
      • isref是否引用
      • isblank是否空白
      • islogical是否逻辑值
• 数据透视表函数
  • getpivotdata
    • 格式
      • 第一参数：求什么字段
      • 第二参数：在哪个透视表求
      • 第三参数：初步判断哪个类别
      • 第四参数：具体哪个类别哪个条件
      • 其他条件...
    • 方法
      • 一、保证分析菜单栏中pivotdata打开
      • 二、引用透视表单元格
      • 三、将文本条件修改为引用
    • 优点
      • 公式简单效率高
        • 直接引用透视表数据而非数据源
      • 随着透视表刷新而刷新数据
• 循环引用
  • 流程
    • 一、设置迭代器
    • 二、设置开关、计数器
    • 三、写公式
• 数据库函数
  • 举例
    • DSUM(数据区域,列,条件...)
  • 特点
    • 可以应用单元格的条件
  • 数据区域
    • 引用
    • 数据从首行开始只引用列号
  • 列
    • 支持数组
  • 条件
    • 支持通配符
    • 无“=”表示包含
    • 有“=”表示等于
    • 空白单元格表示所有数据