学习笔记【和秋叶一起学EXCEL】入群福利包 入群福利链接：https://pan.baidu.com/s/1hvDe2enfanFItyEjRNAnZQ提取码：cg6u（Excel精美模板+Excel常用函数大全+Excel微信公众号精选文章+微软Office快捷键宝典）
素材帮你全找好，Excel使用少烦恼~《和秋叶一起学Excel》课程配套练习 链接地址:联想Filez 提取码:zkcg★温馨提示：不用注册联想网盘账号，复制链接到电脑网址上打开可以直接下载~excel 自带丰富的模板
第一节：使用模板，模板不单单只是用，还可以从模板中借鉴一些可以使用的元素，以及模板中的实现有哪些是不会的 第三方模板库：OfficePLUS，微软Office官方在线模板网站！ ； ppt美化大师练习：做一个自己的时间线（模板）；找到喜欢的一类模板；找到合适的，实际应用到工作中去。
1.在excel中使用ctrl+c 复制，复制完成后，会有浮动的小按钮，叫智能标记 可以只复制值，以及其他的一些格式2.excel在模板列表中，在2013版本以上有一个“欢迎使用excel”的入门手册3.套用表格样式后（套用表格格式在‘开始’中），表格区域会自动动态延伸。
可以用来做动态图表so3 选择数据的四种方法1.使用鼠标拖动选择（当行和列太多时，该方式不可行）2.快捷键：使用ctrl+上下左右来移动， excel会直接移动到有数据的行或列，如果后续的行或列没有数据，使用快捷键则不会继续往下。
使用ctrl+shift+上下左右，直接选中数据 使用ctrl+home/end 到开始或结尾3.选择以后，手动调整选中的数据：选择以后，按照shift不放，使用鼠标进行调整4.使用ctrl+a 选中全部表格数据 ，按住ctrl不放，手动调整。
5.ctrl +点击 手动选择单个单元格6.shift+点击 选中两次点击之间的所有单元格7.使用ctrl+shift + 向左箭头 选中 左侧的单元格8.在单元格中输入值，然后直接按ctrl+enter ,在选中的单元格中，批量写入输入的值。
名称定位：在左上角的 名称框 输入框中 ，输入单元格的坐标，选择区域单元格 选择区域单元格后，在名称框中，输入字符，为区域单元格取名通过别名定位 使用ctrl+g 弹出定位对话框条件选择：ctrl+g ，定位条件 选中区域。
通过excel自带的区域定位来选中区域so3-1 高效查看数据的3个妙招冻结单元格：以光标所在位置，冻结光标以上，以左的所有单元格在‘视图’--窗口--冻结窗格 查找某一些数据，条件筛选，比如名字中姓王的人：在ctrl+f中筛选了数据后，可以使用ctrl+a全选，筛选后的数据。
然后就可以对这些数据进行操作然后使用筛选也可以做查找，选中需要筛选的列，通过“开始”--编辑--排序和筛选（ctrl+shift+L），选中的列头，出现下拉框，选择可以文本筛选和数字筛选S04-1 纸张，图片上的表格怎么转入excel。
通过ocr文字识别软件泰比ABBYY，生成电子化的文档，提供直观的文件扫描工具，识别换转换几乎所有的打印文档类型，包括书籍，杂志上的文章和表格，电子表格，生成的电子文档可以编辑和搜索S04-2 pdf中的表格如何导入excel。
分列：数据--分列--选择分隔符号（分隔符号可以多选，如果我没有出现则自动忽略；默认将多个连续的分隔符当作一个处理）--列，选择日期，格式化成日期格式（有了日期的分隔符号），使用分列--固定长度--提取字符串（使用分列--日期--YMD格式，可以转换为日期格式）通过再次分然后通过右击选择单元格格式，设置其他日期格式
长数字文本变成科学计算解决：通过再次分列解决，在分列中选择“文本”格式S04-3 网页和外部文件的数据批量导入excel从网页采集数据，从网页动态更新数据：数据---从网站（还有其他更多的数据源设置）csv：类似于通行证一般的文件格式，绝好的数据中转站
在分列中-数据预览--可以通过选中列，然后在“列数据格式”中选择“不导入此列”来忽略该列数据过滤空行：通过筛选--选择筛选的列--选择筛选值--排序方式 对某一列的值进行排序在查找中，可以使用通配符*来查找多个 ，使用? 匹配一个字符 使用~转义通配符
秋*&叶* xx开头并且xx结尾大数据导航网址：大数据导航-大数据工具导航-199IT大数据导航-199IT大数据工具导航-Hao.199it.com 有各种各样的数据集S04-4 手工录入数据，巧用填充。
在开始-编辑-填充-序列 中可以设置各种填充的条件1.自动填充连续序号，日期：使用填充句柄填充连续的值（可以双击和拖动，可以在下拉框中设置是复制单元格还是填充序列），可以自定义序列；当选中多个单元格时，excel会自动找出多个单元格的规律，并生成后续单元格的值。
2.自动填充同等间隔的数值，日期3.自动生成指定区间的工作日4.自动求和和填充公式5.不用分列，不用函数自动提取字符，合成文本开始--编辑--填充--序列 可以自定义填充方式可以设置行、列；等差序列、等比序列、日期、自动填充；日，工作日，月，年等；可以设置步长和终止值。
快速填充--在相关值周围输入需要提取的值后，回车，然后在开始--编辑--填充--快速填充 自动识别填充相关模型的值 快速填充，excel会自动查找整个sheet中的数据，然后进行匹配使用ctrl+z 可以取消excel中的超链接 包括邮箱，网址等。
S04-5 批量输入数据：快捷键和公式批量输入多个重复的值：输入完值后，直接按Ctrl+Enter使用相同的值填充空白：使用ctrl+a全选；使用ctrl+g，定位到所有的空值；在一个单元格中输入king，按ctrl+enter
如果键盘没有自带的小键盘，可以购入一个外接的小键盘提升效率vlookup(找啥，去哪找，在第几列，找完全一样的还是找差不多的（0,1）)在列前加入$符号，表示锁定单元格，不带该符号的则会跟着输入公式的单元格走。
使用vlookup应该注意的点：“找啥”单元格的内容，必须在“去哪找”区域的第一列；“去哪找”区域必须锁定使用excel 80%都是在观察，分析和找出规律使用ctrl+g 找出相关查找的值，然后输入需要替换的值，在按ctrl+enter 在相同的值全部替换掉。
快捷键：使用f1 可以得到excel 的快捷键大全S04-6 复制粘贴（选择性粘贴）转置：复制单元格，然后选中单元格，右击-转置 完成行列的转换批量加减乘除：在单元格中输入并复制需要操作的值，并在开始-粘贴-选择性粘贴-选择减 ，完成批量减（其中有相应的加减乘除的方式）。
格式与验证：比如自动补充为固定的格式；日期使用不同的分隔符号，金额自动加入货币符号；手机号自动分段；输入数字显示文本；数字自动转换成大写；S05-1 乱码类似身份证长数字乱码问题（）数据格式：长串的数字，excel自动按照科学记数法来显示，只要超过11位，excel就会按照科学记数法显示（依据excel版本的不同，该位数会有差异）
解决：因此在输入长数字之前，需要将在输入的单元格将单元格格式改成文本格式开始--数字--下拉框选择或者：在单元格最开始，输入一个单引号，excel会把带单引号的数字转换成字符，同时单引号不会显示出来简化输入操作（1,0：男，女）：
将输入的数字转换成相应的文本：if可以操作选中需要转换的列-开始-数字--右下角的箭头--自定义设定单元格格式--输入：[=1]"男";[=0]"女"手机号码分段显示自动分隔手机号码：选中需要转换的列-开始-数字--右下角的箭头--自定义设定单元格格式--输入000-0000-0000
默认状态下，所有单元格的数字格式都是常规：该格式会自动判断输入的值是数字还是文本，默认数字会自动靠右对其，文本自动靠左对齐单元格内容为数字时，任何单元格格式的设定，该单元的显示会跟着其变化，而文本内容则不会。
excel中，日期是中1900-1-1开始，当输入数字，则会变成：2020-1-24. 可以对日期进行加减通过f1，打开帮助窗口，输入自定义格式-搜索相关内容S05-2下拉框（数据验证）下拉列表，限定范围。
选择需要设置下拉框的单元格--数据--数据工具---数据验证--序列（可以设置多个值，中间使用英文的逗号隔开）对于下拉框项比较多，并且可能后续需要更新的：通过设置下拉项的值来源，来解决通过开始--套用表格格式--在后续行中输入值时，会自动进行表扩展，利用这一特性，完成单元格下拉框的自动更新
对于可能需要设置下拉框的单元格类型，在不确定的情况下，可以问自己两个问题：1. 设置后，能提高录入效率和减少出错率吗？如果能，则设置2. 需不需要频繁切换鼠标和键盘进行操作，影响操作体验？如果需要，则不设置
同时，还可以设置联动的下拉框对输入的值进行校验S05-3批量导入照片（排列工具）使用：格式-分布--一次性将照片导入选中行：通过选择一行--使用快捷键（ctrl+shift+下箭头）设置单元格行高（照片得到充分的显示）--查找图片按照什么排序（需要让文件夹中的图片和excel中的行一一对应），能够一一对应后--选中第一个对应的单元格--插入图片--全选，插入全部图片-- 设置图片的大小，以能够放入单元格为合适--将最后一张图片放入到最后一个单元格（为方便后续的操作，让整个图片均匀分所有的单元格中，能够一一对应）--再次全选图片--在照片工具中（只有在光标选中图片时，该图标会显示在正上方，文件名的左侧）-- 在工具栏的【偏右，设置图片的对其方式：分布设置：左对齐；纵向分布，完工。
S05-4 删除、统计 重复项（数据工具和透视表）用颜色标记重复值：选中数据--右下角-重复值删除多余的重复项：仅保留一个不重复的值操作：数据--数据工具--删除重复值S05-5 筛选和统计重复项将不重复的值放置到其他地方：数据--排序和筛选--高级--选择数据区域--放置数据的区域--勾选选择不重复的记录 完工。
（或者也可以在原位置显示，该方式是把重复的隐藏了）列出所有不重复的项，并统计重复的次数：通过数据透视表完成统计按条件筛选出不重复项统计重复次数高级筛选：可以将筛选的结果复制到其他地方，并且可以设置条件区域，在设置条件区域时，需要设置列头，和数据集的列头一致，该高级筛选通过列头来匹配；条件区域的值，可以为空，可以模糊匹配，可以数值的大小于匹配。
S05-6 按颜色筛选和统计选中单元格--右键-筛选-按所选单元格的颜色筛选或者：ctrl+shift+L 打开下拉框筛选，然后可以按照单元格颜色；然后可以按照单元格值：可以等于，不等于，小于，模糊查询等。
S05-7 批量一列拆成多列多列合并成一列：使用快速填充操作动态更新文本合并：使用连字符&,连接多个单元格，或者是使用concat函数；phonetic((a1,b1,c1,d1));phonetic(a1:d1)
多行合并成一行：使用开始--填充--内容重排 ，把多行转换成一行，不能对数字进行操作会重新排序要单元格的宽度，超过则换到第二行一行拆成多行：同样，使用内容重排，单元格的宽度这一特性，来一行拆分成多行内容重排 ，会根据空白单元格进行换行
同一条件合成一行：S05-8 批量整理数据的技巧把以固定符号分隔的一行分隔为多行：把数据表格从excel中复制到work中，然后把固定符号替换为^p，换行，然后复制回excelS06 Excel 知识体系。
熟悉使用快捷键和excel菜单：在excel中，按f1打开帮助界面，可以在该页面搜索相关页面；观察每个菜单的功能，进行实际的操作，并通过思维导图梳理一遍； 思维导图工具：百度脑图，xmind，mindjet。
通过实际操作来解决问题针对性的专项强化训练函数公式S07-1 比赛计分和排名alt+= 插入求和公式rank(单元格,单元格区域) (j3,$j$3:Jj$17)sum 求和max 最大值min最小值average 平均值
vlookup&subtotalcountif(countifs,count)sumif(sumifs,sum)phonetic=RANDBETWEEN(30,90) 生成指定范围内的随机数=RANDBETWEEN(1956/3/3,2020/10/25) 生成指定日期范围内的日期 。
+-*/%^ 10^2=10的平方=100&字符连接符 ：,单元格连接符=IF(e3>40,"中年",IF(e3>28,"青年",IF(e3>16,"少年",IF(e3>10,"童年",IF(AND(e30),"幼年",0)))))
S07-2 函数公式S07-3 vlookup函数补全函数查看函数文档修改参数，不用死记硬背vlookup函数：查找数据的引用函数vlookup(查找的数值（找谁），查找的数据区域（上哪找），索引列（要找第几列的值：根据上哪找的列中定义，第一列为1,），查找方式、可选（怎么找）)。
=VLOOKUP(A3,sheet_name!$B$3:$C$110,2,FALSE)a3 ：找谁sheet_name!$B$3:$C$110 在哪个sheet的哪个区域内查找，通过使用钱符号来绝对定位，如果不使用该符号，则在应用到其他位置时，该值会变动。
通过按住选择参数，按照f4, 快速锁定，变成绝对引用2：显示在公式单元格中的值，显示的值为“区域”中的第二列False：精确查询两个函数的结合使用，iferror（函数，函数错处时，显示的值）=IFERROR(VLOOKUP(A8,武侠情侣!$B$3:$E$110,2,FALSE),0)
搜索函数：在公式--插入函数--搜索相关功能关键字--搜索出相关函数networkdays：用来计算工作日以及在单元格编辑栏中，选择函数，然后可以跳转到相应的web界面有详细的参数说明S07-4 Rand函数。
应用场景：有一个抽奖名单，然后随机抽取一部分中奖名额涉及到vlookup，rand，rank函数的使用index 函数使用公式求值，可以查看函数公式的执行步骤S07-5 if函数if(条件，如果为真，如果为假)，后续两个参数都是可选参数，需要使用逗号替代。
条件：等于，大于，小于，大于等于，小于等于，不等于<> 两个条件：=if(and(条件1，条件2),真，假)=if(or(条件1，条件2),真，假)多个条件：=if(and(条件1，条件2,)，真，再次跟if...)
S07-6 countif 函数 和数据验证countif(计数区域，计数条件) / countifs通过把函数放置到数据验证-自定义 中，来设置单元格的数据验证S08-1 图形化呈现数据条件格式：条件格式中有这种图标的形式，可以灵活使用该图表来让数据一目了然
打开条件格式的三种方式：1.开始--条件格式练习：对于把男女使用图标显示：先把男女分别替换为1,0，然后使用条件格式，判断大于等于1，大于等于0，设置不同的图标，并且设置只显示图标练习：对同一个数字设置两种图标：设置数据条和条件格式（大于或者小于xx的单元格）。
通过设置条件格式的其他格式，可以方便的设置条件范围，当值满足条件时，可以不显示图标S08-2 突出重点和异常数据S08-3 条件格式和函数公式的结合使用在条件格式中，通过相对引用来对整行引用规则=$D3="北京"。
通过条件规则使用隔行变色：=Mod(Row(),2)=0 row返回当前行号，mod是求余函数：如果当前行数能够被2整除，则加上颜色三行变色：=MOD(ROW(),6)>=3自动甘特图：填写开始和结束日期，自动生成右边的“进度条”。
=AND(E$2>=$C3,E$2<=$D3)S09-1 制作图表 六种基础图表：柱状图--直观展示少量数据的对比，最常用折线图--呈现多项数据基于时间推移的趋势饼图--展现不同数据的百分比占比条形图--放倒的柱形图，用于更多数据的对比。
散点图--表示来个因素之间的相关性雷达图：数据类别以放射状进行对比1.创建图表与突出显示选中数据，在插入--插入数据图表；在设计中，设计图表相关样式或者右击设置相关样式2.双系列图表，层叠样式在设计中找到重叠的样式。
3.经典子弹图表，双轴插入一张普通柱状图--然后选中其中一个柱状--然后设置数据格式--设置重叠100%---选中其中一个--设置宽度以及最后设置柱状图的颜色S09-2 可视化S09-3 组合图表右击-选择数据--添加数据源--添加一个平均值的辅助列--设置格式如下：
选中该线条--线条设置为无--标记设置为：内置，类型为短横线，增大尺寸，填充颜色，标记边框白色动态变化控制线：添加一个平均值的辅助列（如上设置）折线面积图：为最大的柱状图着色：=IF(B3=MAX($B$3:$B$10),B3,0)。
使用辅助列，找出最大值，然后选择该列数据，然后选中最大值的图，设置成次数据，设置颜色S09-4 创意图表通过设置 图表“选中数据源”的列数据顺序，可以设置列在画布上的层次S09-5 mini图表S10-1 数据透视表。
S10-2 使用数据透视表，分组统计透视表的分组--可以设置起始值，步长，以及如果超出的部分，头尾各一组S10-3 使用透视表拆分表格S10-4 切片器-- 交互式图表S11-1 对于合并单元格，取消合并后（再次点击合并后居中），填充值，然后把格式刷完成合并的效果。
使用数据透视表完成合并单元格的效果： 要求：数据表需要是一维的对于元数据需要维护，一般通过如下三张表：参数表（用于记录基础信息，按行存储，常作为数据有效性来源）；元数据表（用于存储需要从频繁引用的固定信息，每行存储一笔数据）；汇总表：用于分类汇总和统计分析，一般由数据透视表完成。
S11-3 在excel中，空行会导致各种问题，比如排序以空格为界限；使用ctrl+向下方向键检查是否有空行空行会导致数据遗落，由此需要特别注意S11-4使用{数据--分列}完成单元格类型的转换把文本存储的单元格转换成数值型的：下拉箭头转换；辅助列计算转换比如单元格+0；选择性粘贴等。
S12-1 表格打印S13-1excel常用插件：慧办公，方方格子（推荐），易用宝，以及excel应用商店（插入--应用商店-我的加载项--查看已经添加的应用）S14-1 vloopup 查找匹配函数快速核对数据：=VLOOKUP(B5,$F$3:$F$96,1,FALSE) 查找两列中，一列多出的值
多条件查找（同时满足多个条件的结果）：在源数据中，把多个列中的值合并到一列中，在查找的表中，通过&合并多个单元格来达到多条件的目的=VLOOKUP(G3&H3,$D$3:$E$50,2,FALSE) d列为合并单元格。
分段归类/归类：日期转换星座，数值划分等级 text函数：可以将数值转换成指定格式的文本，比如1月1日，按mmdd转换后，会变成0101的数字 =TEXT(A3,"mmdd")-0 ==101 根据日期输出星座：找出星座的日期范围，然后对日期进行编码，编码规则为：mdd的形式，然后对实际的生日也提取成mdd 的形式，采取text函数，再使用vlookup函数：=vlookup(生日编码，星座所在的表范围，模糊查询)模糊查询的方式为：从上往下逐个比较，取小于ta的最大值。
参数表必须由从小到大排序，写数据分段区间中最小的值多列查询，查找一个对象，返回多个结果：S14-2 查找匹配，index+match 逆向查找和匹配通过右边的列返回左边的值：index(b:b,match(f2,c:c,0)) f2为查找表
match:找出对象所在位置的信息，返回值为行号，0为精确匹配，1为模糊匹配（查找数据在区域中的所在位置） index: 在查找范围内查找，第二个参数的行数（根据位置参数返回结果）S14-3 年龄和工龄的计算。
数字中0表示日期：1900/1/0，任何日期的数字格式，代表距离1900/1/0的天数，数字中，整数表示日期，小数表示时间把时间转换为小时数，比如3.12小时，把3：23 乘以24.计算几个小时以后的时间：时间+小时/24。
合并在不同单元格中的年月日：date(y,m,d)拆分日期：year(date),month(date),day(date)合并时分秒：time(h,m,s)拆分时间：hour(time),minute(time),second(time)
weeknum:周别计算 weeknum(date,1) 1:周日作为每周的第一天，2：周一做为每周的第一天int：日期时间分离，舍弃小数部分，时间-int(时间) 得到小数部分datedif：计算日期差 （date1,date2,Y/M/D/YM/MD/YD） YM:计算同年月数，同一年相差几个月，MD：同月天数。
S14-4书籍：谁说菜鸟不会数据分析；偷懒的技术，打造财务excel达人；你早该这么玩excel，excel这么用就对了；数据透视表应用大全；excel图表之道，excel函数与公式速查手册《秋叶excel》
亲爱的读者们，感谢您花时间阅读本文。如果您对本文有任何疑问或建议，请随时联系我。我非常乐意与您交流。
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。