WeTab-AI新标签页 - Microsoft Edge Addons
Sider: ChatGPT侧边栏+ Vision 眼睛, GPT-4 Turbo, 联网, 绘图 - Microsoft Edge Addons
Kimi.ai - 帮你看更大的世界
MindMaster 多平台思维导图软件，让您的创意破茧而出
PPT模板 - PPT模板免费下载_免费PPT模板下载 - PPT世界
iSlidePPT模板下载,iSlide演示设计作品,iSlidePPT教程 - 51PPT模板网
目录
绝对引用：选中想要绝对引用的单元格，fn +f4 一起按住即可。
移动列：选择列，按shift键移动到需要的位置。
写入公式后 双击‘+’下拉变成自动复制单元格了？办法：在‘公式’栏的计算选项改‘自动’。
Vlookup函数(区域要绝对引用，关于数字的模糊匹配只会小于或等于的数值)：
=IF(ISNA(VLOOKUP(F20,$A$2:$C$18,3)),VLOOKUP(F20&"",$A$2:$C$18,3),VLOOKUP(F20,$A$2:$C$18,3))
1.1：找到定位条件，定位空值后输入公式：=j2/i2 。然后按批量填充：按ctrl后按enter。
函数的基本用法；如果logical_test逻辑判断值为true，返回value_if_true，否则返回value_if_false
2.1 用IF函数处理运算错误 Iserror函数：如果是错误的返回true。
2.2与and、or嵌套使用：=IF(AND(A3="男",B3>=60),1000,0)
2.3 中国式排名 =RANK(H5,$H$5:$H$11)
3. Countif函数语法：Countif(range,criteria)，在range内，满足criteria这个条件就技计数。
应该采取绝对引用："=COUNTIF($A$8:$A$20,A9&"*")"
=COUNTIFS(D:D,I5,E:E,J5) （且的关系，计算左边范围出现且右边范围出现的次 数）
5.sum =sumif(range,criteria,[sum_range])
7. 日期weekday、weeknum、text、date
将'20201128173036 转换成时间（2020-11-28 17:30:36）可以用：
9.Left函数——Right函数——Mid取中间函数—Find函数——len():文本长度——lenb()文本长度:中文为2——mod()余数
10. if——mod——row、mid——index混合使用
11.SUMIFS函数—数组生成原理—SUMPRODUCT函数—LOOKUP函数多条件精确匹配
滚动条变换-日期变换-已完成变换-未完成变换-插入方框公式=日期变换
14.1 =SUBSTITUTE(A9,",","+") —— evaluate
移动列名：选中列，按shift,再移动
写入公式后 双击‘+’下拉变成自动复制单元格了？办法：在‘公式’栏的计算选项改‘自动’。
解决：你这是表格手动了 要改成自动 公式自动计算。
找表格最后一行：点击一列第一个单元格下面出现+后双击，返回首行刚好相反；
找表格最右边：点击第一列单元格右边出现+后双击。
冻结窗格：冻结窗格永远冻结。
Offset函数全方位解析Excel中的OFFSET函数 (zhihu.com)
https://www.zhihu.com/tardis/sogou/art/369713764
Vlookup函数(区域要绝对引用，关于数字的模糊匹配只会小于或等于的数值)：
Excel：史上最全的VLOOKUP应用教程 - 知乎 (zhihu.com)https://zhuanlan.zhihu.com/p/148938771
'=VLOOKUP($A3,数据源!$A:$K,MATCH(C$2,数据源!$1:$1,0),0)
MATCH(C$2,数据源!$1:$1,0)：在某列或某行以“0”精确匹配形式查找C&2值所在的位置
match_type=-1时，表示会查找大于或等于lookup_value的最小值，此时lookup_array必须按降序排序，如果没有按降序排序，将会返回#N/A。
match_type=1时，表示会查找小于或等于lookup_value的最大值，此时lookup_array必须按升序排序，如果没有按升序排序，将会返回#N/A。
match_type=0时，表示会查找等于lookup_value的第一个值，可以按任何顺序排序，如果没有查找到匹配项，将会返回#N/A。
=VLOOKUP(G10,$C$8:$D$13,2,1)
=IF(ISNA(VLOOKUP(F20,$A$2:$C$18,3)),VLOOKUP(F20&"",$A$2:$C$18,3),VLOOKUP(F20,$A$2:$C$18,3))
如果VLOOKUP(F20,$A$2:$C$18,3)是空值，返回VLOOKUP(F20&"",$A$2:$C$18,3),否则返回VLOOKUP(F20,$A$2:$C$18,3)
Hlookup函数 与vlookup方向相反
1.批量填充：按ctrl后按enter（1.1示例）：
（填充下方表格单价）
1.1：找到定位条件，定位空值后输入公式：=j2/i2 。然后按批量填充：按ctrl后按enter。
1.2 数据透视表
要想数据透视表的两行并排显示，可以尝试使用 1.经典模式，或者 以表格形式显示。
2.IF
函数的基本用法；如果logical_test逻辑判断值为true，返回value_if_true，否则返回value_if_false
| 2、IF函数的基本用法 | |||||
| 函数语法：IF(logical_test,[value_if_true],[value_if_false]) | |||||
多层嵌套：=IF(B2="理工","LG",IF(B2="文科","WK","CK"))
=IF(G2="本地",H2+30,IF(G2="本省",H2+20,H2+10))
=IF(I2>=600,"第一批",IF(I2>=400,"第二批","落榜"))
2.1 用IF函数处理运算错误 Iserror函数：如果是错误的返回true。
2.2与and、or嵌套使用：=IF(AND(A3="男",B3>=60),1000,0)
=IF(OR(B12>60,B12<40),1000,0)
=IF(OR(AND(A20="男",B20>60),AND(A20="女",B20<40)),1000,0)
2.3 中国式排名 =RANK(H5,$H$5:$H$11)
H5在列范围$H$5:$H$11数据从小到大的排序号。
2.4 int mod sqrt round text mid left replace rept substitute taday edate emonth 求和快捷键-选中区域-alt+'+'; alt+'='快速调出公式
int（number)向下取整——示例：3=int(3.6)
求余数：mod(number1，number2)
四舍五入：=round(number,保留小数位数）
取绝对值=abs(number)
平方=sqrt(number)
日期=text(a1,'##-##-##')/text(a1,'00-00-00')
时间=text(a1,'00!:00!:00!')
快速填充（合并也行)：crrl+E
生成x1,x2之间的数=randbetween(x1,x2)
文本长度= length(a1)
提取=mid(文本，开始位置，提取长度）
左提取=left()
右提取=right()
替换=replace（原来文本，开始位置，替换数量，新的文本）
替换= substitute(a1整个文本,要替换的一段文本，替换的新文本)
重复=rept(文本，重复次数）
求和快捷键-选中区域-alt+'+'; alt+'='快速调出公式
taday返回现在日期
now()返回现在时间
edate(a1,月份数)返回与a1差距的多少月份的日期
emonth(a1,月份数)返回与a1差距的多少月份的最后一日的日期
示例：
3. Countif函数语法：Countif(range,criteria)，在range内，满足criteria这个条件就技计数。
Count（f:F）只会对数字计数。
=COUNTIF($E$1:$F$53,H8)条件需要引号
3.1countif区域单元格会发变化，
应该采取绝对引用："=COUNTIF($A$8:$A$20,A9&"*")"
3.2与if嵌套使用：=IF(COUNTIF(F:F,A2),"是","否")
3.3数据有效性
4. countifs :
=COUNTIFS($D$2:$D$53,I5,$E$2:$E$53,J5)
=COUNTIFS(D:D,I5,E:E,J5) （且的关系，计算左边范围出现且右边范围出现的次数）
5.sumif
=sumif(range,criteria,[sum_range])
在range范围内，如果满足criteria条件，对[sum_range]对应范围的求和。
Sumif函数超过15位字符时的错误：=SUMIF(A:A,F5&"*",B:B)
=SUMIF(D:D,H4,F1) D要与F1平齐。
=avregeif()
=SUMIF(A:I,L3,B1) 选到a到i，求和b1一样的和：
5.1Sumifs函数,多条件求和，第一参数为求和区域。
=SUMIF(A:A,I3&J3,G:G) <==> =SUMIFS(G:G,E:E,I3,F:F,J3)
I3在E:E范围内，且J3在F:F范围内，求G列的和。
5.2 限制数据超过指定值。数据有效性。
6.MATCH —— INDEX
MATCH(lookup_value,lookup_array,[match_type])
查找值，查找的数组，精确类型
INDEX(array,row_num,[column_num])
查找的数组，返回数组那个位置的值
6.1 INDEX索引多列（混合引用）
=INDEX(数据源!B:B,MATCH($B3,数据源!$A:$A,0)) <==> =VLOOKUP($B13,数据源!$A:$G,返回多列结果2!C$11,0) <==> =VLOOKUP($B22,数据源!$A:$K,COLUMN()-1,0)
COLUMN() 返回单元格所在列的位置。
COLUMN()返回所在的列数：
7. 日期weekday、weeknum、text、date
将'20201128173036 转换成时间（2020-11-28 17:30:36）可以用：
=MID(A2,1,4)&"-"&MID(A2,5,2)&"-"&MID(A2,7,2)&" "&MID(A2,9,2)&":"&MID(A2,11,2)&":"&MID(A2,13,2)
8.条件格式：开始——条件格式
单元格颜色标记。使用公式标记注意引用问题!
——————
9.Left函数——Right函数——Mid取中间函数—Find函数——len():文本长度——lenb()文本长度:中文为2——mod()余数
find()可以查找重第几个数开始找同样的值
10. if——mod——row、mid——index混合使用
=IF(MOD(RIGHT(LEFT(B3,17),1),2)=1,"男","女")
=IF(MOD(C3,1)<0.5,INT(C3),INT(C3)+0.5)
=INDEX(E:E,MATCH(G4,A:A,0))
=INDEX($A:$A,COLUMN(A1))
=INDEX(E:E,ROW()*5-17)
=INDEX($A:$A,ROW()*3+COLUMN()-30)
11.SUMIFS函数—数组生成原理—SUMPRODUCT函数—LOOKUP函数多条件精确匹配
=SUMIFS(E:E,B:B,H12,C:C,I12)
'=SUMPRODUCT(($A$2:$A$22=I16)*($B$2:$B$22=J16)*$E$2:$E$22)
=LOOKUP(1,0/($A$2:$A$92=G15),B13:B103)
'=LOOKUP(1,0/(($A$2:$A$13=I6)*($B$2:$B$13=J6)),$D$2:$D$13)
12. indirect单元格引用
indirect可以理解为简接返回最原始单元格的值
=INDIRECT("e"&(ROW()*5-25))
12.1 跨表引用
=INDIRECT(A4&"!g2")
12.2如果员工姓名位置不固定该如何处理
=VLOOKUP("张三",INDIRECT(A13&"!A:G"),7,0)
12.3相对引用使用VLOOKUP加indirect
=VLOOKUP(C$2,INDIRECT($A9&"!A:G"),7,0)
13. 图表 动态图表
13.1勾选显示
| 开发工具-插入-表单控件- | |||||||
| 复选框-右键改名字-右键设置控件格式- | |||||||
| 单元格链接(选择合适的位置)-确定再勾选会出现True | |||||||
| 找到公式-定义名称设置为彩盒3(这个值指的是作图最终选择的数据值) | |||||||
| 引用位置设置if条件公式(目的:当勾选彩盒(True)时, | |||||||
| 作出彩盒列的数据折线图，不勾选时显示F列空白值. | |||||||
| 插入空白折线图右键-选择数据-添加图例-名称为彩盒-图例值为Sheet!彩盒3 | |||||||
| 确定可以显示折线图引用值 | |||||||
13.2 数据更新永远返回最后10行数据
| 在公式-定义名称-名称填:成交量2-值:填=OFFSET($B$1,COUNTA($B:$B)-10,0,10,1) | ||||
| 再插入折线图右键选择数据添加填:Sheet1!成交量2,确定 | ||||
| 横坐标一样,定义名称填:=OFFSET($a$1,COUNTA($a:$a)-10,0,10,1)确定后, | ||||
| 在图中选中坐标右键编辑填:Sheet1!日期2,确定 |
折线图会根据数据更新而更新
13.3 根据滚动条-动态选择数据-动态更新图
| 开发工具-插入滚动条-右键间隔设置10,单元格控件放在合适位置,复制多一个 | |||
| 在公式-定义名称-名称填:成交量3-值:填=OFFSET(Sheet3!$B$1,Sheet3!$D$2,0,Sheet3!$D$5,1) | |||
| 横坐标一样,定义名称填:=OFFSET(Sheet3!$A$1,Sheet3!$D$2,0,Sheet3!$D$5,1)确定后, | |||
| 在图中选中坐标右键编辑填:Sheet1!日期2,确定 | |||
| 右键图表至于底层,滚动条拖到合适位置 |
13.4制作甘特图
| 堆叠柱状图设置次坐标轴,反转,两个坐标轴都设置-1至1,间隔0.25,删除次轴 | |||
| 坐标轴数字单元格格式添加0%;0%,添加数据标签居中,在开始栏调整数字 | |||
| 复制图片,图片纹理填充 | |||
13.5
滚动条变换-日期变换-已完成变换-未完成变换-插入方框公式=日期变换 |
| 滚动条变换-日期变换-已完成变换-未完成变换-插入方框公式=日期变换 | |||||
| 滚动条变换-单元格链接变换-设定公式合适日期+单元格-已完成列公式： | |||||
| =IF($B$11<B2,0,IF($B$11>B2+C2,C2,$B$11-B2)),未完成公式:=C2-D2 | |||||
| 插入图表,图标样式由右键和菜单栏字体设定,图表置于底层,拖动滚动条到合适位置, | |||||
| 插入方框里面公式=日期变换 | |||||
14. （宏函数）创建工作表链接
宏表函数需要再公式里定义公式
首先定义公式名称用get.workbook创建公式，最后用hyperlink公式，错误值可以用iserror公式
| GET.WORKBOOK(type_num, name_text) | |||||||||
| Type_num 指明要得到的工作簿信息类型的数。 | |||||||||
| Name_text 是打开的工作簿的名字。如果name_text被省略,默认为活动工作簿。 | |||||||||
| Type_num | 返回 | ||||||||
| 1 | 正文值的水平数组,返回工作簿中所有表的名字。 | ||||||||
| 2 | 通常返回错误值#N/A。 | ||||||||
| 3 | 正文值的水平数组,返回工作簿中当前选择的表的名称。 | ||||||||
| 4 | 工作簿中表的数。 | ||||||||
| 5 | 如果工作簿含有发送名单，返回TRUE；否则返回FALSE。 | ||||||||
| 6 | 正文值的水平数值，返回所有未接收到文件的接受者的名字。 | ||||||||
| 7 | 以文本形式返回当前发送名单的主题线。 | ||||||||
| 8 | 以文本形式返回发送名单的信息正文。 | ||||||||
| 9 | 如果文件被发送,一个接一个被接收，返回1；如果全部一次被发送，返回2。 | ||||||||
| 10 | 如果［发送名单］对话框中选择［返回送毕信息］选择框,返回TRUE；否则返回FALSE。 | ||||||||
| 11 | 如果当前接收发送当前文件,返回TRUE；否则返回FALSE。 | ||||||||
| 12 | 如果［发送名单］对话框中选择［跟踪状态］选择框,返回TRUE；否则返回FALSE。 | ||||||||
| 13 | 工作簿［发送名单］： | ||||||||
| 0 =不被发送 | |||||||||
| 1 =进展中的路径选择,或对于用户工作簿已被发送。 | |||||||||
| 2 =发送已完成。 | |||||||||
| 14 | 如果工作簿结构被保护,返回TRUE；否则返回FALSE。 | ||||||||
| 15 | 如果工作簿窗口被保护,返回TRUE；否则返回FALSE。 | ||||||||
| 16【说明有误？点击链接】 | 以文字形式返回工作簿的名称，不包括驱动器，目录或文件， 或窗口编号，等价于GET.DOCUMENT(1) | ||||||||
| 17 | 如果文件为只读，返回TRUE，否则返回FALSE。等价于GET,DOCUMENT(34) | ||||||||
| 18 | 如果表被写保护，返回TRUE，否则返回FALSE。等价于GET.DOCUMENT(35) | ||||||||
| 19 | 当前文件允许写的用户的名字,等价于GET.DOCUMENT(36)。 | ||||||||
| 20 | 对应于显示在［另存为］对话框中的文件的文件类型的数字。等价于GET.DOCUMENT(37)。 | ||||||||
| 21 | 如在［另存为］对话框中选择了［建立备文件］选择框，返回TRUE；否则，返回FALSE。等价于GET.DOCUMENT(40) | ||||||||
| 22 | 如在[选择]对话框的[重新计算设置]标签中选择了[保存外部链接值]选择框，返回TRUE。等价于GET.DOCUMENT(43) | ||||||||
| 23 | 如果工作簿有一个苹果开放合作环境(OCE)返回TRUE；否则返回FALSE。如未安装OCE(mailer),返回#N/A。 | ||||||||
| 24 | 如果工作簿在最后一次保存后被改变,返回TRUE。若未改变(当被关闭,不提示保存)，返回FALSE。 | ||||||||
| 25 | 以文字形式水平数组，返回Power Talk(mailer)的To线上的接收。 | ||||||||
| 26 | 以文字形式水平数组，返回Power Talk(mailer)的Cr线上接收。 | ||||||||
| 27 | 以文字形式水平数组,返回Power Talk(mailer)的Bxx线上的接收。 | ||||||||
| 28 | 以文字形式返回Power Talk(mailer)的主题。 | ||||||||
| 29 | 以文字形式水平数组,返回Power Talk(mailer)的外壳。 | ||||||||
| 30 | 如果Power Talk(mailer)从另一用户［*(相对于刚增加但不是发送)。 | ||||||||
| 31 | 作为一系列数返回Power Talk mailer)被发送的日期和时间，如(mailer)未被发送，返回错误值#N/A。 | ||||||||
| 32 | 以文本形式返回Power Talk(mailer)的发送者的名字,如(mailer)未被发送,返回错误值#N/A。 | ||||||||
| 33 | 以文字形式返回显示在［摘要信息］对话框中的文件的标题。 | ||||||||
| 34 | 以文字形式返回显示在［摘要信息］对话框中的文件的主题。 | ||||||||
| 35 | 以文字形式返回显示在［摘要信息］对话框中的文件的作者。 | ||||||||
| 36 | 以文字形式返回显示在［摘要信息］对话框中的文件的关键字。 | ||||||||
| 37 | 以文字形式返回显示在［摘要信息］对话框中的文件的注释。 | ||||||||
| 38 | 活动工作表的名字。 | ||||||||
14.1 =SUBSTITUTE(A9,",","+") —— evaluate
SUBSTITUTE(A9,",","+")
要替换的字符串，字符串里的旧符号，替换成新的符号
——
——
2723
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
