- 博客(95)
- 资源 (13)
- 收藏
- 关注
RSS订阅原创 Excel-VBA基础（1）：如何在菜单栏显示“开发工具”
进入VBA代码编辑窗口，除了快捷键Alt+F11，还可以从菜单栏（工具栏）中点击“开发工具”项下的“宏”、“录制宏”、“属性”等按钮。Excel安装后默认不显示顶部的“开发工具”，按如下步骤可将“开发工具”显示出来。第1步：单击菜单“文件”---“选项”第2步：在弹出的窗口中勾选“开发工具”，单击“确认”即可。之后的界面：...
2020-12-10 11:18:27
1176
原创 Excel综合应用（1）： 批量打印格式化后的表格
工作中，有如下格式的表格，需要打印出n个员工的。员工详细数据在另外一张工作表中，那么本案例将结合函数与VBA展示综合应用的优势。表格样式：员工详细数据：解决思路：第1步在"表格样式"工作表中插入ActiveX控件：下拉列表框，数据源定位于"员工详细数据"工作表中的B列序列号，当人工选择下拉列表项时，选中的"序列号"值存在A2单元格，为防止误删除，将A列隐藏；因为A2单元格得到的"序列号"值是文本型...
2020-12-09 19:38:20
591
原创 Excel引用函数（1）：FORMULATEXT，取得单元格公式内容
当需要读取单元格公式并修改公式内容时，通常要用到FORMULATEXT函数，以字符串的形式返回公式内容。下述示例是将C列年终奖的计算公式读出来，每人再加100元，因为希望保留C列的公式内容，所以用如下步骤操作：第1步：在单元格D2读取C2的公式内容并加上100 “=FORMULATEXT(C2) & "+100"”，复制D2内容到D列其他单元格。第2步：如果直接将D列的内容复制粘贴到E列...
2020-12-08 19:29:06
2529
原创 Excel数值函数（8）：取余数，从而判断奇偶数，MOD
工作中经常需要从身份证号码中判断性别，准确而迅速，其中最重要的函数就是MOD。例如：=IF(MOD(MID(A2,17,1),2)=0,"女","男")以上公式是假设身份证号全部是18位，取倒数第2位数字除以2，如果得到的余数等于0，说明第17位是偶数，女性，否则是男性；如果有旧身份证号码（15位长度）的情况，可以再嵌套一层判断，如果是15位的，用MID函数取最后一位数字除以2，取余数判断奇偶。多...
2020-12-05 20:16:38
2349
原创 Excel逻辑函数（5）：IFNA，判断公式是否返回错误值 #N/A
此函数通常结合 VLOOKUP函数使用，判断是否查找到结果，如果没有找到，IFNA可以捕获错误值 #N/A，根据实际需要，转为指定的值。例如下述应用：公式说明结果=IFNA(VLOOKUP("西安",$A$5:$B$10,0),"待查")IFNA 检验 VLOOKUP 函数的结果。 因为在查找区域中找不到“西安”，VLOOKUP 将返回错误值 #N/A。 IFNA 在单元格中返回字符串“待查”，而...
2020-12-04 22:48:19
4823
原创 Excel逻辑函数（4）：IFERROR，判断公式计算是否正确
当单元格中输入计算公式时，可以用IFERROR做个容错的判断，例如FIND函数查找字符串，当查不到结果就会报错，此时就可以结全IFERROR函数使用。举例如下：=IFERROR(FIND("和平街道",E2),0)上述公式解析：当FIND("和平街道",E2) 查找到时，返回正常结果；查找不到时，返回0（此值可以根据实际需要而指定）语法IFERROR(value, value_if_error...
2020-12-04 21:52:26
4187
原创 Excel数值函数（7）：对指定多个条件的单元格求和，SUMIFS
之前讲到了“Excel数值函数（4）：对指定条件的单元格求和”，这是只有1个条件的情况下，对指定单元格区域的数值求和。这次说说如果有多个条件，怎样计算指定单元格区域的数值之和？那就是当之无愧的SUMIFS函数了。语法SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)参数Sum_range ...
2020-12-03 17:28:48
3527
原创 Excel字符函数（3）：字符查找函数Find、Search
工作中，有时候编号与时间、短信回复内容、评论内容等常有混在一起需要拆分的情况，还有一些从数据库Oracle、Mysql等导出的数据字段内容需要拆分，那么除了MID、LEN、SUBSTITUTE等字符函数外，用的最多的就是Find、Search函数了。（菜单“分列”功能虽然也能用，但是不如函数的灵活性强）二者的差异如下：01 SEARCH 语法SEARCH(find_text,within_tex...
2020-12-03 17:01:28
3038
原创 Excel基础知识（11）：快速访问工具栏利用好，效率大增
快速访问工具栏是容易忽略的一个菜单功能区，可以把工作中常用的菜单按钮单独提出来，放在“快速访问工具栏”，可极大的提高工作效率。话不多说，上图：“快速访问工具栏”最右侧的下拉箭头点开，即可勾选系统预置的一些功能，用不上的不用选。点击上图的“在功能区上方显示”即可调快速访问工具栏调整在菜单栏的上方或下方。点击上图的“其他命令”即可添加更多菜单里的按钮项"： ...
2020-12-02 17:51:29
648
原创 Excel基础知识（10）：查找替换不只是单元格内容
还可以是单元格格式，包括背景色、字体、框线等。如下图所示，当不知道背景色值时，还可以选择“从单元格选取格式”，将格式整体读出来：
2020-12-02 17:34:29
108
原创 Excel基础知识（9）：常用的、极有效的快捷键
（1）Ctrl+S 保存工作簿，养成好习惯，操作完成一个阶段的重要任务后，就按一下这个快捷键，以防突然停电或宕机。（2）Ctrl+上下左右箭头可迅速回到表头、表尾，无论表格有多长，多高。如果表格中间有空单元格，会停留在空单元格处。（3）Ctrl+Home、End快速定位到A1单元格和最后一个单元格。（4）Ctrl+Shift+上下左右箭头快速选中目标区域，代替拖动鼠标去选择，尤其当行或列很多时；（...
2020-12-01 15:46:04
577
原创 Excel字符函数（2）：CLEAN及TRIM的区别与应用场景
二者的语法都很简单，在应用VLOOKUP函数之前，最好将查询与被查询的单元格都同时应用这两种函数，避免匹配失败。尤其是从Oracel\Mysql\SQL Server等数据库中导出或网页上抓取的内容。=Trim(text)=Clean(text)Trim去除文本最前面和最后面的空格去除，如文本间存在多个连续空格的，则保留第一个空格。如下图，单元格A44,A45就是应用TRIM函数后的结果：Clea...
2020-11-27 16:58:24
2396
原创 Excel查找引用类型函数（1）：常用的VLOOKUP及HLOOKUP、LOOKUP
查找引用类型的函数中，因为在EXCEL中使用纵向表格较多，所以VLOOKUP使用的频率最高。例如下图中，E26单元格中的公式“=VLOOKUP(D26,$A$26:$C$30,2,FALSE)”参数间用英文半角的“，”逗号分隔。第1个参数是根据查询区域的单元格D26的值（姓名，李1），在被查找区域A26:C30（第2个参数）中查找精确匹配（第4个参数）后的第2列（第2个参数）的值“身份证号码”。要...
2020-11-27 16:14:30
1314
原创 Excel数值函数（6）：Average,Max,Min,Count等数据统计函数
01 Average ，求平均值如下图所示，“C15:H15”单元格区域的平均值是12.5：02 Max，求最大值如下图所示，单元格区域“C16:F16”的最大值是20：03 Min，求最小值如下图，“C17:F17”的最小值是10：04 COUNT，统计指定单元格区域包含数字的单元格个数如下图所示，"C18:F18"单元格区域中，包含数字的单元格个数是4...
2020-11-23 19:22:40
1805
原创 Excel数值函数（5）：COUNTIF,统计符合指定条件的单元格数量
COUNTIF 函数会统计指定区域内，符合指定条件的单元格数量。例如统计工资大于指定值的员工数量；统计指定职称的员工数量等。如下图所示，统计“C19:F19”区域中，">10"的单元格数量：01 语法COUNTIF(range, criteria)range 必需。 要计数的一个或多个单元格，包括数字或包含数字的名称、数组或引用。 空值和文本值将被忽略。criteria 必需。 定义要进行...
2020-11-23 18:53:15
73955
2
原创 Excel基础知识（9）：单元格区域命名，精简公式中的单元格引用
当公式中引用的单元格或单元格区域较多时，公式可读性较差，工作表名称、单元格地址混合在一起，很难维护，不小心还容易修改错误。EXCEL提供了“名称管理器"，可以将单元格区域中，内容较固定的部分，命名一个特殊的名称，这样在函数或公式中即可调用该名称，代替复杂冗长的单元格地址。例如，下图中单元格C2中VLOOKUP公式的“'1月'!$A$2:$B$9”，就可以命名一个单独的名称进行管理：请点击引用示例0...
2020-11-23 16:55:51
1392
原创 Excel逻辑函数（3）：逻辑“或”函数OR的用法
当需要判断两个以上的条件中，是否有任一条件的判断结果成立(True)，此种情况，需要用逻辑函数OR。OR(logical1,logical2, ...)01 语法OR(logical1,logical2, ...)判断多个条件，如果有任一条件判断结果成立(True)，则整体判断的结果成立（True)。02 示例再比如，OR(D3<E3,D3<100)，是判断D3<E3和E3<...
2020-11-22 21:48:51
7372
原创 Excel逻辑函数（2）：逻辑“与”函数AND的用法
当需要判断两个以上的条件是否同时满足时，需要用逻辑函数AND.01 语法AND(logical1,logical2, ...).判断多个条件是否同时成立(True)，如果有任一条件不成立(False)，则整体判断的结果是不成立。02 示例再比如，AND(D3<E3,E3<=300)，是判断D3<E3和E3<=300是否同时满足，同时满足，结果是R1，有任一条件不满足，结果是...
2020-11-22 21:38:19
6549
原创 Excel逻辑函数（1）：多层IF判断函数的嵌套，完成递进逻辑判断
IF函数可能有两个结果。 第一个结果是比较结果为 True，第二个结果是比较结果为 False。例如，=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。再如下图所示：当判断的条件“D3>E3”成立时，结果是第2个参数值"完成"，否则，结果是第3个参数"未完成"。当有第2个IF判断时，例如当销售额小于任务额时，小于60万元以内的、小于超过60万元...
2020-11-22 12:18:55
6291
原创 Excel功能挖掘（3）：突出显示包含有公式的单元格
对于一张工作表中大片单元格区域，怎么能知道哪个单元格是用公式计算出来的呢？下面可以利用名称管理和隐藏函数get来实现。名称可以随便命名。
2020-11-21 19:57:58
498
原创 Excel功能挖掘（2）：限制单元格区域内输入重复内容，即时提示
对于录入订单编号、产品编号的内容，为避免手误录入重复，可以用如下方法：主要使用到COUNTIF函数。COUNTIF 用于统计满足某个条件的单元格的数量。COUNTIF 的最简形式为：=COUNTIF(要检查哪些区域? 要查找哪些内容?)例如：=COUNTIF(A2:A5,"London")示例要在 Excel 中使用这些示例，请复制下表中的数据，然后将其粘贴进新工作表的 A1 单元格中。...
2020-11-21 19:44:21
235
原创 Excel功能挖掘（1）：快速识别两个单元格区域中内容不同的单元格
如上图所示，如何将两个红框中同一行的单元格的性别、年龄不相等的标记出来？步骤如下：01 以第一个红框内的性别、年龄为基准，选择要比较的单元格E1:F2902 点击菜单"开始"--"条件格式"--"管理规则"--"编辑格式规则" 设置单元格的格式规则如下图所示:在“格式”中可以设置单元格内容的字体、颜色、边框、填充色等，本例是设置单元格背景色为粉色。 ...
2020-11-21 19:19:01
641
原创 Excel基础知识（8）：快速打开最近使用的工作簿
希望能快速打开最近使用过的工作簿，那么就要将Excel记忆的“最近使用的工作簿”数量放大，虽然占用一点磁盘空盘和内存，但是硬件越来越便宜，优化这项功能是划算的。话不多说，上图（下图“最近使用的工作簿”数量是25个，根据需要可以再加）：设置方法：菜单“文件”-“选项”，在对话框中的“高级”标签页中，设置上述内容：EXCEL 2013版本，此处最大数量是50...
2020-11-19 13:58:58
294
原创 Excel基础知识（7）：如何降低文件保存失败风险（断电、宕机等）
要利用好Excel的自动保存功能，修改自动保存的时间间隔，避免因突然袭击断电、宕机等原因而导致的文件保存失败，减少内容丢失量。话不多说，上图：点击菜单“文件"-"选项”，在对话框中的“保存”标签页中，设置上述内容：可以修改为1分钟（极端值），不过频率过高会影响电脑性能（频繁读写磁盘），文件比较大时，偶尔会有卡顿。...
2020-11-19 13:45:35
240
原创 Excel基础知识（6）：改变默认的字体、字号、新建工作簿时的工作表数量
年龄段不同，对习惯的字体大小是有个体差异的，为Excel设置适合自己的字体、字号，工作时更舒服，效率也更高。新建工作簿时一般会同时新建3张工作表，但实际工作中通常只使用一张，如果把这个默认值修改为1，更舒适。话不多说，上图：点击Excel的菜单“文件”-“选项”，在对话框中的“常规”标签页中，设置上述内容：...
2020-11-19 13:36:10
446
原创 Excel基础知识（5）：改变默认的小位数位
金融、保险行业工作中，小数位数保留4-6位的情况较为常见，尤其是利率的测算。改变默认的小数位数，将会提高工作效率。步骤如下：01 点击进入菜单“文件”-“选项”02 进入“高级”标签页根据实际需要，修改下图红框中的小数位数即可。...
2020-11-19 13:26:58
304
原创 Excel基础知识（4）：改变按回车键后，光标默认的转移方向
在当前单元格的内容输入完成后，我们习惯性地按回车键确认，光标会自动移到下一个单元格，默认是同一列的下一个单元格。但有时我们需要在行方向输入完一行后，再移到下一行输入，此时回车确认后，光标自动移到同行的右侧单元格最高，大大提高工作效率。如何改变回车确认后，默认下一个单元格是下面的还是右侧的？操作步骤如下：01 点击菜单“文件”---“选项02 进入”高级“标签页调整“按Enter键后移动所选内容...
2020-11-19 13:19:59
1092
原创 Excel基础知识（3）：工作表两种隐藏方法，第2种只能进入VBA解决
工作中，某些保存有部门列表、工资基数、系数等基础的、轻易不能被修改的内容，但又需要在函数或公式中调用，通常会单独存放在一张sheet工作表中，然后将这个工作表隐藏，避免误修改。01 方法1最常用的方法是在工作表标签上右击，在弹出的菜单中选择“隐藏”：取消上述隐藏的工作表，同样还是在工作表标签上右击，在弹出的菜单中选择“取消隐藏”。02 方法2按ALT+F11快捷键，进入VBA的界面，左侧工作表的...
2020-11-19 11:34:17
2717
7
原创 Excel数值函数（4）：对指定条件的单元格求和
SUMIF，对单元格区域中符合指定条件的值求和。 例如，假设在含有数字的某一列中，需要对大于 5 的数值求和。 请使用以下公式：=SUMIF(B2:B25,">5")下图中是对B2:B9单元格区域中 ，数值>=500的单元格求和。01 语法SUMIF(range, criteria, [sum_range])02 参数range 必需。 用于条件计算的单元格区域。 每个区域中的单...
2020-11-18 14:16:27
8178
原创 Excel日期函数（1）：计算两个日期间隔的天数、月数、年数
使用Excel的隐藏函数DateDif，可以快捷实现。如下图所示：1、计算间隔的年份数量，注意看A6,B6单元格的日期，差一天，就会减少一年，是严格安排对头的日期来计算。2、下图是计算A2,B2两个单元格中日期相差的月份数。3、下图是计算A1,B1两个单元格中日期相差的月份数。函数DateDif在帮助文档中找不到，下面是相关的的语法说明。01 语法DATEDIF(start_date,end_d...
2020-11-18 13:07:39
13250
原创 Excel字符函数（1）：利用3个函数提取邮箱中的域名
从上图中可以看出，要用到三个函数：mid，find，len。用find函数找到域名前的标志“@”的位置，再往后移1位，该位置作为截取域名的起始位；利用mid函数截取“@”标志之后的所有的字符（len 函数度量了邮箱的整体长度，为了提高效率，不再计算“@”标志之后的字符长度，而是使用邮箱的整体长度，亦不会出错）。下面分别来学习这3个函数的用法。一、mid 函数从一个字符串中截取出指定数量的字符01 ...
2020-11-18 13:06:09
1168
原创 Excel数值函数（3）：对“自动筛选”的结果求和、平均值、极值等
你没看错，只需要用一个函数：SUBTOTAL ，即可实现。如下图所示，对单元格B2:B6进行筛选后，再对筛选的结果求和。01 SUBTOTAL 函数语法对筛选结果、“分类汇总”结果再进行运算SUBTOTAL (function_num,ref1,[ref2] ])02 参数说明Function_num 必需。 1 到 11之间的数字，用于指定使用何种函数在列表中进行分类汇总计算。黑体的参...
2020-11-18 13:04:10
3929
原创 Excel数值函数（2）：三个四舍五入函数的区别
除了最常用的ROUND 函数，另外还有向下、向上舍入的ROUNDDOWN、ROUNDUP函数，下面示例清晰地展示出区别，可以应用于不同的需求。一、ROUND 函数是最常用的四舍五入函数01 语法ROUND(number, num_digits)02 示例复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果，请选中它们，按 F2，然后按 Ente...
2020-11-18 13:01:51
449
原创 Excel数值函数（1）：将十位数上的数字归零的
例如5783.01元，希望将十位数字之后的数字全部去除，理想的结果是5780元，可以使用如下公式及函数：=TRUNC(5783.01/10)*10即将数据除以10之后，用TRUNC函数取整，之后再乘以10。TRUNC函数的说明如下：功能：将数字的小数部分截去，返回整数。01 语法TRUNC(number, [num_digits])TRUNC 函数语法具有下列参数 （参数：为操作、事件、方法、属...
2020-11-18 12:59:43
994
原创 Excel基础知识（1）：公式中相对引用、绝对引用的区别
先来看绝对引用。如下图所示，公式“=C15/$C$23”中的分母，在行23、列C前面加了$，这就表明对单元格C23做了固定，无论怎么拖拉、复制这个公式所在单元格，分母C23都不会改变。分子是C15是相对引用，所以当将单元格D15复制到D16-D23时，分子会相应变化为C16-C23，但是分母固定不变(第二张图显示有全部公式)。当用于计算各项值占总体的比例时，需要固定分母（总体值），分母用绝对引用。...
2020-11-18 12:52:15
7950
原创 Excel基础知识（2）：如何让单元格出现下拉箭头，以供选择？
这就要用到菜单“数据”---“数据验证”这个功能。对于做工资表中的部门名称、职位、职称，仓储表中的产品名称、编号等固定的内容，最适合用此功能。如下图所示的效果：下面介绍实现的步骤：第一步：选择要加下拉箭头单元格区域，本例中是M列的多个单元格。第二步：点击菜单“数据”---“数据验证”，弹出数据验证的小窗口第三步：小窗口中，在“允许”下拉框中选择“序列”项目第四步：小窗口中，在“来源”输入框中输入“...
2020-11-18 12:49:58
5375
原创 清华同方锋锐K560散热处理，再也不用担心我的笔记本发烫了
我的清华同方锋锐K560笔记本购于4年前，当时拿来做大数据分析练习用的，这配置在目前看来也还是够用的，至少比传统的纸张写字来的快：）传统的记事方法，还是电脑更方便呐这个配置还是可以的，除了固态硬盘，还有1TB硬盘但是从一年前开始，老骥伏枥的它发热越来越严重，安装鲁大师后看到CPU温度经常穿越90度，甚至高达100度：（，让人怀疑人生... ...风扇的声音象哮喘严重的老人，唉，下功夫治治它吧，历尽...
2019-07-23 17:16:27
946
原创 Python：Anaconda Jupyter notebook 更改默认启动路径方法
安装好Anaconda以后运行Anaconda Prompt输入jupyter notebook --generate-config 会生成jupyter_notebook_config.py找到文件，并打开C:\Users\XXXXx\jupyter_notebook_config.py将#c.NotebookApp.notebook_dir = '' 改为c.Notebo...
2018-12-06 16:00:26
227
原创 SAS：Proc Freq ，输出结果控制小数位数
Proc Freq ，输出结果控制小数位数ODS PATH RESET; ODS PATH (PREPEND) WORK.Templat(UPDATE) ; proc template;edit Base.Freq.CrossTabFreqs;edit TotalPercent; header = "Tot Pct"...
2018-12-05 17:26:09
5663
Excel-VBA应用：循环将多个工作表另存为PDF的宏代码
2024-06-25
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人