Excel各种tips汇总
这里对一些容易忽略但有可能会用到的Excel的小技巧进行记录,不定期进行补充
- VBA中的atpvbaen.xls和Solver的密码:Wildebeest!!
- 工作表超级隐藏: 打开VBA,对Sheet1的属性Visible进行设置即可
- 正态分布随机数生成:=INT(NORM.INV(RAND(),20,20/3))
- 处理错误值函数:iferror()
- vlookup函数数字模糊查找:
- 引用的数字区域一定要是从小到大排序
- 查找出和它最接近,但比它小的那个数
- 修改前后的Excel表差异对比:【开始】-【Microsoft Office 2013】-【Office 2013 工具】-【Spreadsheet Compare 2013】,使用该工具进行Excel表对比(记住用2013版本及以后的版本Office软件)
VBA常用代码
- 清除单元格内容:range("AA:AP").ClearContents
- 取消/开启工作表保护:ActiveSheet.Unprotect/ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Excel常用函数
- 查看某单元格引用了哪些单元格:【Ctrl+[】组合键
- 查看某单元格被哪些单元格引用:【Ctrl+]】组合键
- 隐藏公式(知道就行,反正以后有百度)
- 数组公式输入前必须先选择存放结果的单元格区域,然后按【Ctrl+Shift+Enter】键锁定数组公式
- 日期函数:today()、date()、month()、day()、year()、weekday()、now()、hour()、minute()、second()、workday()、networkday()
- 文本函数:text()
- 返回两数值相除后的整数部分:quotient()【注意与int()函数的区别】【与trunc()函数类似】
- 返回两数值相除后的余数部分:mod()
- 计算数值的正负号:sign()
- 最大公约数:gcd()
- 最小公倍数:lcm()
- 文本合并函数:concatenate()
- 字符位置查找:find()【区分大小写】、search()【忽略大小写】【通配符的使用:?、*】
- 字符串长度:len()
- 按照给定次数重复显示文本:rept()
- 替换函数:replace()
- 重复函数:rept()
- 去掉文本中的所有空格:substitute()【同样为替换函数】
- 大小写:upper()、lower() 首字母大写:proper()
- 字符串比较:exact();数值比较是否相等:delta();数值比大小:gestep()
- 货币函数:rmb()、dollar()
- 数值转换为汉字文本:numberstring()
- 指定小数位置四舍五入取整:fixed()
- 修剪平均值(去掉最低最高):trimmean()
- 返回空白单元格以外的数值平均值:averagea()
- 算数平均值:average()
- 几何平均值:geomean()
- 众数(频率最高的数):mode()
- 频数:frequency()
- 最大、最小:large()、small()
- 排名函数:rank()
- 返回数值区域的k百分比数值点:percentile()
- 中位数:median()
- 四分位数:quartile()
- 回归分析函数:forecast()、growth()、trend()、linest()、logest()、intercept()、slope()、steyx()
- 选择函数:choose()
- 返回指定引用的行列标:row()、column()
- 偏移函数:offset()
- 行列转置:transpose()
- 数据库函数:dsum()、daverage()、dcount()、dcountA()、dget()
- 错误:iserror()
- 角度转换为弧度:radians()
- 弧度转换为角度:degrees()
- 圆周率π近似值:pi()
- 底数的幂运算:pow(number,power)
- e的幂运算:exp(number)
- 对数运算:log(number,base)、ln()、log10()
- 返回≥0,<1的均匀分布随机实数:rand()
- 随机取整数:randbetween(bottom,top)
- 返回number!结果,阶乘:fact(number)
- 组合数结果:combin(number,chosen)
- 排列数结果:permut(number,chosen)
- 返回参数列表中非空值的单元格个数:counta()
- 返回包含数字的单元格个数:count()
- 返回指定单元格区域中空白单元格的个数:countblank()
- 返回区域中满足给定条件的单元格的个数:countif()【通配符的使用:?、*】
- 检查数值类型:type()
- 判断是否为空白单元格:isblank()
- 十进制转二进制:dec2bin();转八进制:dec2oct();转十六进制:dec2hex()【相互转,函数名调换位置即可】
Excel工作步骤:①数据录入(导入);②数据处理;③数据分析。
对应的操作:①输入(导入)数据;②整理数据(函数等技巧);③对数据进行分类汇总。
对应的工作表:①源数据表;②源数据表或其他新建工作表;③分类汇总表。
正确的源数据表应该满足以下条件:
- 一维数据
- 一个标题行
- 字段分类清晰
- 数据属性完整
- 数据连续
- 无合并单元格
- 无合计行
- 无分隔行/列
- 数据区域中无空白单元格
- 单元格内容禁用短语或句子
Excel2010表格右边出现有很多空白列,导致表格过长,如下图滚动条特别长,怎么删除掉那些空白列
解决方案:
1、选择第一个空白列,按住CTRL+SHIFT+向右的箭头,然后点击鼠标右键,“删除”。
2、把鼠标定位到A1单元格,保存一下,滚动条就恢复正常了。