angelstonehi的博客

转载 excelhome 知识树

http://club.excelhome.net/tree/index-224.html

转载 RANK的三维引用和COUNTIF的四维引用

http://club.excelhome.net/forum.php?mod=viewthread&tid=146260&page=11#pid7124339

转载 一起来认识数组公式(最基础)

http://club.excelhome.net/thread-511876-1-1.html

翻译 Markdown转公众号格式

https://lab.lyric.im/wxformat/https://mp.weixin.qq.com/s/pn0LzyfgUj6rGUfVHUksjg 这里有个介绍

翻译 Offset函数，偏移变量注意是数值而不是数组

错误：#VALUE!=SUMPRODUCT(($L$3:$L$5)*OFFSET(A$9,,ROW(A$1)-1,3,1))正确：=SUMPRODUCT(($L$3:$L$5)*OFFSET(A$9,,SUM(ROW(A$1))-1,3,1))或者：=SUMPRODUCT(($L$3:$L$5)*OFFSET(A$9,,ROWS(A$1:A1)-1,3,1))区别是：Row(A$...

原创 数组公式显示数组的每个元素，而不是一个数组。

不能更改数组的某一部分如果变成数组的形式：先写好第一行公式（F9可查看数组的结果）全选结果单元格区域（有几个结果就包含几行/几列）在编辑栏单击，然后Ctrl+Shift+Enter，这样就能生成数组的每一个元素...

转载 Frequency函数 分段统计个数 去除重复值的唯一值数组（常用于提取不重复的数字）

Frequency(data_arrya,bins_array)该函数的两个参数，均可使用数组或者单元格引用。如果参数为数组，除了包含错误值以外，可以使用任何其他类型的数据。如果参数是单元格引用，还可以包含多区域联合引用。无论用户指定的参数是一维的数组还是二维的单元格引用，该函数都将返回一个垂直数组，即一维数组。=Frequency(B2:B10,{5,10,15})Countif...

翻译 降维处理 N函数和sum降维，rows/cols降维

数组公式为什么不显示数组结果http://club.excelhome.net/thread-1306675-1-1.html二位数组，变成一位数组，需要降维处理

翻译 Rank函数+中国式排名+PercentRank函数+lookup（根据占比划分等级）

rank(number,ref,[order])rank.avg(number,ref,[order])rank.eq(number,ref,[order])rank函数的第二个参数ref(排名区域)只能是单元格引用，而不能使用数组（常量数组或运算后的内存数组），否则结果返回错误提示。第二个和第三个公式的基本原理类似。唯一的区别是在处理相同数值的排位名次存在差异，前者是按平均值排名，后者是...

翻译 Subtotal统计函数

如果subtotal 函数引用的区域中，有部分单元格使用了subtotal公式，则这些单元格会被自动忽略，不会被统计。另外，subtotal函数只能忽略隐藏行下的数据统计，不能忽略隐藏列下的数据，即所有隐藏列的数据将被统计在内。Function_num（）函数101average102count103counta104max105min...

翻译 Large函数和Small函数 Max Min

Large和Small，相对于Max Min更灵活。因为可以返回用户指定的特定大小顺序的数值。用法：Small(array,k)Large(array,k)其中array可以使用数值或单元格引用，甚至可以是单元格的联合引用。k为指定的第几个位置，而且按照取值原理，可以对指定数据升序或降序排列。当k为小数，small将K值向下舍去取整，large将K值向上舍入取整=small({10...

转载 去掉最高分和最低分的平均分trimmean，计算频率最高的那个数即众数mode.sngl，判断数据是否重复

在8个裁判的评分结果中分别首尾各去除2个极值（共去除4个数据），因此表示为4/8,Trimmean函数会自动去除两端的极值计算内部平均值=Trimmean((B3:B3:B3:I3,4/8)或者：=Average（Large(B3:B3:B3:I3),{3,4,5,6}）...

转载 极值函数max min median 上限，下限值

应用：指定数据的上限值和下限值，如果超过上限值，返回上限值如果低于下限值，返回下限值。如果介于两者之间，返回原值通用公式：=MIN(上限值，max(下限值，测试值或测试公式表达式))或：=Median(下限值，上限值，测试值或者测试公式表达式)...

转载 Mround Round Floor Ceiling

Mround 可以以指定的基数四舍五入，比如0.5的步长Round 四舍五入Floor 只能向下取，可以以指定的基数Ceiling 只能向上取，可以以指定的基数MROUND(, )如果 number 除以指定 multiple 的余数大于或等于 multiple 值的一半，MROUND 将向上舍入数值。例如：...

转载 INDEX函数，隔行提取数据

1.INDEX函数隔行提取

转载 多条件查询+反向查询+分段区间数据查询+最后几行的数据+

VLOOKUP和MATCH函数INDEX和MATCH函数

转载 Offset函数

转载 Lookup函数

LOOKUP：好处：速度快，假设Vlookup遍历100次，LOOKUP只要7次。原理：1.数据升序排列（默认）2.在由N个数据组成区间（1——N）查找某值X假设查找范围100个数据，LOOKUP首先比较中间值（第INT(1+100)/2=50）与X的大小。如果该值<X,则收敛搜索区域为“右区间”即第51-100个。反之则在左区间查找，进入次级区间后，将再度使用刚才的方法对比查找。...

转载 Match函数判断单个条件重复个数,Countifs函数多条件判断元素重复个数

1.辅助列，求出当前单元格在所选择区域* 的行数，比如C2在C2:C11之间，是第1行

转载 Columns函数：返回数据表区域的总列数。

COLUMNS( $ C: $ H) 返回结果为6返回区域的总列数，利用该技巧可以避免人为对区域列数的手工计算，将直接返回指定区域中最后一列的序号

转载 行列函数构建序列

转载 方方格子---聚光灯，删除空格，四舍五入，合并拆分工作表

http://www.ffcell.com

转载 16---Indirect函数

1.Indirect引用名称2.

原创 检查excel sumif结果少算的方法：1.公式出错 2.条件值有空格或者文本等

检查excel sumif结果少算的方法：1.公式出错 2.条件值有空格或者文本等

原创 English words summary

幻灯片 PPT第一张 first slide汉语英文低级替换词汇显示showindicate按照…according tobased on占…比例make up/account for值得注意的是It’s worth noticing that比上一年增长increaseup 10 percent下降decrease...

转载 Excel每一行数据都加上表头

https://jingyan.baidu.com/album/3ea51489d372c952e61bba80.html?picindex=1插入空行-定位空值-选择性粘贴

原创 Find函数-目标：1.查找某个区域是否含有某值；2.查找某个单元格是否含有某个字符

目标1：查找某个区域是否含有某值（count次数）举例：目标1：查找某个区域是否含有某值（所在位置）

转载 或关系，用+加法或者or；与关系，用*乘法或者And

或关系，用+加法或者or；与关系，用*乘法或者And

原创 Markdown语法

1、标题,注意#与文字之间加一个空格# 一级标题## 二级标题### 三级标题#### 四级标题##### 五级标题###### 六级标题展示效果：一级标题二级标题三级标题四级标题五级标题六级标题2、列表无序列表（用* 或者 -）* 简介* 第一章* 结束语展示效果：简介第一章结束语有序列表（用1. 2. 3.）1. 第一条2. 第二条...

转载 带数字和单位的数字汇总 sumproduct substitute

B12=sumproduct(- -substitute(B3:B14,“人”)) 按Ctrl+Shift+Enter（因为是数组公式）

转载 英文全角转换为半角ASC() 清理非打印字符Clean() 清理空格Trim()

=ASC(A1)

转载 计算N天以后的日期

1.计算2019/2/8号30天以后的日期2019/2/17=A1+30得出结果2019/3/19注意：A1格式设置为日期，且与输入法日期格式一致（中英文日期表示不同）

转载 一次性奖金是否选择并入综合所得测算表

http://www.shui5.cn/article/7f/125237.html政策：　　《财政部关于个人所得税法修改后有关优惠政策衔接问题的通知》（财税[2018]164号）规定，居民个人取得全年一次性奖金，符合《国家税务总局关于调整个人取得全年一次性奖金等计算征收个人所得税方法问题的通知》（国税发[2005]9号）规定的，在2021年12月31日前，不并入当年综合所得，以全年一次性奖金...

翻译 分离中文和数字/英文字母(赵四18430849383)；取日期时用日期函数

赵四18430849383刘能13844039570取“赵四”=LEFT(A1,LENB(A1)-LEN(A1))取“18430849383”=RIGHT(A1,2*LEN(A1)-LENB(A1))

原创 定义名称offset引用最后一个单元格的上个单元格(适用于插入行时不用改求和公式)

上一个单元格=OFFSET(Sheet1!G$46,-1,0)

转载 查找Excel最后一个非空单元格的值，你会吗？（适用于数据加行时，一直引用最后一个非空单元格的值）

https://baijiahao.baidu.com/s?id=1610823465051256884&wfr=spider&for=pc

转载 统计单元格内容重复出现的次数（适用于对重复文本/数字的操作）

单元格内容是文本格式：=countif(A:A,A2&’*’)单元格内容是数字格式：=countif(A:A,A2)

原创 适用于汇总不同工作表相同单元格或者单元格区域

假如有5个sheet,名字分别为1，2，3，4，5。汇总工作表1到5的E1到E10单元格区域的数据。有三种写法：=sum(‘1:5’!Q1:Q10)=sum(‘1:2’!Q1:Q10,‘3:5’!Q1:Q10)=sum(’*’!Q1:Q10)...

原创 在同一个页面组合打印不同工作表的内容

Excel 选项-功能区和工具栏-不在常用功能区的命令-照相机

翻译 水平对齐（缩进）+跨越合并+删除线+上下标

第一行：水平左对齐垂直居中第二至四行：设置单元格格式-对齐水平对齐方式：靠左缩进-2垂直对齐方式：居中