Excel
文章平均质量分 65
weixin_43416960
金融风控,大数据,大数据金融风控,数据挖掘
展开
-
【excel】设置二级可变联动菜单
之前做完了,开始做二级联动菜单。原创 2024-05-31 10:47:24 · 2630 阅读 · 0 评论 -
【excel】设置可变下拉菜单(一级联动下拉菜单)
1、用UNIQUE()函数制作辅助列,其中FILTER()函数用来消除UNIQUE()函数结果中的”0“。然后在用”数据验证“生成下拉菜单时,使用OFFSET()函数,引用辅助列。2、非常重要的是,所谓”辅助列“,其实是”辅助单元格“。因为在写UNIQUE()函数时,是用到了数组,但是又不用数组显示出来,因为不能用快捷键Ctrl+Shift+Enter,而是将结果直接回车放在了单元格M1中。原创 2024-05-30 18:41:49 · 1643 阅读 · 0 评论 -
【excel】统计单元格内数字/字符串的数量
2、用以上辅助列+公式的方法的弊端在于,对不同长度的数值/字符串,需要拉动单元格公式做穷举,对于特别长的手工操作比较麻烦。在单元格B1中输入公式=MID($A$1,ROW(A1),1),然后下拉将所有在A1单元格中出现的数字都分离出来。1、excel主要是对单元格间的操作,对单元格内部的数值型或字符串操作可使用脚本语言,如python或VBA等。复制B、C列,粘贴为数值,然后对新的两列进行去重,就可以得到所有数字及他们对应的个数。(因为操作比较简单,略)原创 2024-05-08 17:03:28 · 901 阅读 · 0 评论 -
【Excel】求一列中非重复值的个数-sumproduct函数的使用
问题:找出一列中,不重复的值的个数方法一:可以在【数据】-【删除重复项】中直接删除重复的,剩下的就是不同值,看下个数就可以了。方法二:利用sumproduct函数解释:①COUNTIF(A2:A17,A2:A17)找出每个单元格里的值在整个范围里面有几个重复值②对所以的重复值取倒数③sumproduct(1/COUNTIF(A2:A17,A2:A17))让倒数相加,同一个值的结果都是1,返回值就是区域中不同值的个数了。举例:【银行理财】在区域中有3个重复值,取倒数以后再使用sumprodu原创 2020-11-26 09:10:46 · 3793 阅读 · 0 评论 -
【Excel】解决:数据透视表日期与创建组
问题:插入数据透视表后,源数据的具体日期格式变成了【月】原因:在数据表中默认对日期一栏创建组操作解决:方法①选中透视表中该列任意单元格右键-【取消组合】即可。方法②选中透视表中该列任意单元格,【数据】-【取消组合】。两种方法是一样的,只是按钮和快捷方式的不同。拓展:【创建组】可以对日期、文本、数字进行分组,非常实用有趣。分享一个链接学习参考:http://www.360doc.com/content/17/1104/17/30583536_700869947.shtml...原创 2020-11-25 11:09:19 · 3236 阅读 · 0 评论 -
【Excel】vlookup+if实现逆向查找
vlookup+if实现逆向查找一般VLOOKUP是在一个区域内,在首列查找自己的目标内容,并从左到右返回指定列数所对应的内容,这里有个关键点,是从左到右,查找的目标内容只能是选择区域的第一列,如下图,根据姓名查找基础工资。但有时我们的数据并不会如我们所愿,比如我们需要根据姓名查找编号,而编号是在左侧,违背了VLLOKUP函数正常的逻辑,此时我们就需要将A列和B列进行调换。手动调换当然没问题,但我们的数据是不能随便更换位置的,这就需要使用函数将区域进行对调,if函数,就是我们的解决办法。VLOOKU转载 2020-06-08 14:57:57 · 1864 阅读 · 0 评论 -
【Excel-公式】将符合条件的数值由列显示改为行显示
目的:将某条件下的数值,由列显示改为排列在一行之内。案例:【方法一:】将左侧的数据一项一项逐一复制,选择性粘贴-转置即可。如果数据量比较大,这种手工方法还是比较麻烦的。【方法二:】步骤一:1.1在【C3】单元格中输入公式:=B3&IFERROR(","&VLOOKUP(A3,A4:C100,3,0),"")其中,查找范围A4:C100不能使用绝对引用。原理:...原创 2020-03-03 19:43:59 · 2496 阅读 · 0 评论 -
【Excel】将一列数据按内容不同分为多列-index函数的应用
目标:将一列数据按照内容不同,分为多列。【案例】将不同【分支机构】的【时长】单独统计出来。数据源如下:期望得到如下表格:【步骤一】1.1在【F2】单元格中输入公式=INDEX($ B:$ B,SMALL(IF(A:A=$ D$ 2,ROW(A:A),4^8),ROW(A1)))&""1.2语法解释:INDEX(单元格区域,行号,列号)①目标单元格区域:B列;②行号:...原创 2020-02-28 19:10:25 · 3379 阅读 · 0 评论 -
【excel】找出一个数列中某个百分位所对应的数据
问题:在一列数据中,找到80%的数据所对应的区域范围。比如下表所示的是,所有人完成某项任务的处理时长,我们想找到80%的人处理这项工作所需的时间范围。方法一:使用函数 PERCENTILE(array,k),返回区域中数值的第k个百分点的值。(0<k<1)前提:把数值按从小到大排序。在本例中,先将数值按从大到小排列了,但结果是一样的。从结果可以看出,80%的人完成这项任...原创 2020-02-24 17:51:13 · 14791 阅读 · 0 评论 -
【Excel】多列变为一列
目标:将Excel表格中,多列数据合并到一列之中。如,将下表的8行4列变为1列(8*4=32行):1.方法一:利用Word1.1选中数据区域,右键【复制】,粘贴到word文档中:1.2点击Word中的数字区域部分,使得工具栏中出现【表格工具】选项卡。【表格工具】-【布局】-【转换为文本】,在弹出框中选择【段落标记】,点击确定。Word中的表格数字则会排成一排:选中后复制回E...原创 2020-01-13 15:14:07 · 6560 阅读 · 0 评论 -
Excel易用宝-安装与激活
Excel易用宝1.安装Excel易用宝是由Excel Home开发的一款Excel功能扩展工具软件,可用于Windows平台中的Excel 2003、2007、2010和Excel2013、Excel2016。易用宝下载地址:http://yyb.excelhome.net/download/使用指南:http://yyb.excelhome.net/features说明:易用宝20...原创 2019-11-11 17:30:05 · 8188 阅读 · 0 评论 -
Excel-多级联动
Excel多级联动–数据有效性与indirect函数的结合使用目标:设置多级联动菜单数据源:制作如下,通过颜色和内容,可以看出联动关系:联动菜单:步骤一:设置一级菜单选中F10单元格,【数据】-【数据验证】-【数据验证】,【设置】选项卡-【验证条件】-【允许】选择【序列】,【来源】选择F2:F3区域,默认为绝对引用:步骤二:设置二级联动①定义名称选中G2:G4,即“百...原创 2019-11-08 16:54:45 · 4922 阅读 · 0 评论 -
Excel-条形图个性化填充
Excel条形图的个性化填充步骤一:点击已做好的条形图,选中其中的一个系列,也就是一组条形柱图,【设置数据系列格式】-【填充】,选择【图片或纹理填充】,在【插入图片来自】下选择【文件】,找到提前准备好的图片,插入进来。步骤二:插入的图片默认为【伸展】,改为【层叠】即可完成。...原创 2019-11-05 14:43:35 · 580 阅读 · 0 评论 -
Excel-动态环形图
利用Excel制作动态环形图数据源步骤一:插入控件,制作动态表1.可以使用两种控件:1.1组合框(窗体控件)①点击【开发工具】-【插入】-【组合框】②找到空白区域,点击鼠标左键,设置好组合框③右键组合框控件-【设置控件格式】-【控制】,【数据源区域】选择源数据表中的【销售时间】表头下的内容填充,【单元格链接】选择任一空白单元格(如B7),默认为绝对引用,【下拉显示项数】可以根据...原创 2019-11-04 14:58:14 · 1161 阅读 · 0 评论 -
【待续】【Excel-函数】常用技巧总结
Excel-函数-常用技巧总结1.vlookup函数的多条件查找该函数的语法规则如下:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)当把下图中的左侧表格格式改成右边表格的格式时,数据的填充则需要多条件进行判断。方法:①使用vlookup函数和数组相结合的方式②在G3单元格中输入公式以后,ctrl+shift+e...原创 2019-10-31 17:18:19 · 234 阅读 · 0 评论 -
【Excel】offset函数
offset函数关于参数的总结语法OFFSET(reference,rows,cols,[height],[width])1.【height】【width】缺省,reference为单元格时OFFSET(reference,rows,cols)2.【height】【width】缺省,reference为区域时OFFSET(reference,rows,cols)说明:①当返回结...原创 2019-10-31 11:31:49 · 712 阅读 · 0 评论 -
Excel-函数-分组动态图表
利用offset和match组合函数,制作分组动态图表源数据:制作动态图表,变动组别,使后面的月份数据联动:步骤一制作组别的下拉框选中A29,点击【数据】-【数据验证】-【数据验证】,【设置】-【验证条件】-允许【序列】,来源【$ A$ 21:$ A$ 26】(默认为绝对引用),即源数据中组别的内容数据。确定。步骤二:设置公式思路:①因为表头都是一样的,我们只需要利用offs...原创 2019-10-30 15:13:26 · 387 阅读 · 0 评论 -
Excel-图表与文本框/图片的组合
Excel图表与文本框/图片组合在一起目标:把下图中的树叶放在图表里面,使之成为一体。方法:①把树叶放在图表中合适的位置:②单击选中整张图表,按ctrl键同时选中树叶的图片。在图片或者图表内的区域内邮件-【组合】,点击【组合】,这就完成了。用鼠标单击一下图表外的区域,再移动图表,树叶图片就会随着图表一起移动了。③单击图表或者叶子图片后,可以对图片在图表中的位置进行移动修改。修改...原创 2019-10-29 16:53:11 · 6774 阅读 · 0 评论 -
Excel-制作各国历年GDP动态图
目标:利用Excel制作历年各国GDP动态图步骤一:准备数据将数据整理成以下格式:(不考虑数据的真实性)步骤二:插入控件①Excel【文件】-【选项】-【自定义功能区】,在右侧自定义功能区框中,勾选【开发工具】,确定。②【开发工具】-【插入】-【表单控件】,点击【窗体控件(数值调节钮)】,鼠标左键在空白区域拉动,得到数值调节钮的窗体控件。③右键窗体控件-【设置控件格式】,在【控制...原创 2019-10-29 14:48:10 · 9600 阅读 · 0 评论 -
Excel-制作漂亮可视化图表
制作柱状旋风图原始数据如下表,制作类似于下图的图表步骤一:改造数据源,增加辅助列。①在B列【人均消费茶叶量(克)】前插入【辅助列-消费量】,公式为B2=max(C:C)-C2,即,赋值给B列,使B列的值为,对应的C列的值与人均消费茶叶量最大值之差,这样做是为了给消费量的柱形图做出补充柱形图。另一种方法是做个辅助列,使消费量为负数。②在D列增加【站位数据】列,观察数据(被比较的产量...原创 2019-10-28 17:52:35 · 7153 阅读 · 0 评论 -
Excel-甘特图制作
制作甘特图步骤一:整理数据源数据源须包含任务名称、开始时间以及持续时间(时间段,因为最后呈现的结果是一段时间)。如果是结束时间,需要做个辅助列(结束时间-开始时间)。注意:开始时间和工期这两列的数据,须先设置成常规格式。步骤二:制作堆积条形图纵轴的顺序与源数据表不一致,我们选中纵轴数据以后,在【设置坐标轴格式】-【坐标轴选项】下,勾选【逆序类别】,这时排序就与源数据表保持一致了。...原创 2019-10-25 11:10:48 · 1365 阅读 · 0 评论 -
excel-柱状图不同柱子不同颜色设置
柱状图不同柱子不同颜色怎么设置目标:给柱状图中,不同的柱子设置不同的颜色。步骤一:制作柱形图,如下步骤二:选中数据系列,即柱子,在【设置数据系列格式】中勾选【依数据点着色】。得到想要的柱形图,如下对图表其他部分的设置及美化略。完成。...原创 2019-10-24 15:57:59 · 17068 阅读 · 0 评论 -
Excel-旋风图制作
旋风图制作-条形图进阶原始数据目标图形(优化略)步骤一:原始数据改造,增加辅助列,用来展示被比较的指标名称。参考:百度经验,原文写的非常详细https://jingyan.baidu.com/article/4f34706e7c09b7e387b56db9.html...原创 2019-10-24 14:12:46 · 1125 阅读 · 0 评论 -
Excel-图例文本如何修改
修改图例文本的内容目标:将下图中的图例文本(“1"和"2”)改成数据源表格的表头(“数量"和"辅助列”)步骤一:制图步骤略。方法参见https://mp.csdn.net/mdeditor/102696990#可以看到图例被默认为"1"和"2"。步骤二:在生成的图表中任意位置邮件-选择数据, 点击水平(分类)轴标签的编辑按钮,在轴标签区域下,选中数据源表的表头标题,确定即可。...原创 2019-10-23 14:36:35 · 2973 阅读 · 0 评论 -
Excel-制作简单的环形柱状图
利用圆环图,制作环形柱状图。–环形图的变形原始数据如下,目标图形:步骤一:添加辅助列①添加C列辅助列,目的是将B列的数值,转化为最大值为270(270度,即为3/4圆环)的相对应的值(即该部分的数值占圆环的度数范围)。②添加D列辅助列,目的是将转化后的数值,用360度完整圆环填满。步骤二:制作环形图①选中C2:D2数据区域,然后插入圆环图。②选中数据源C3:D3单元格区...原创 2019-10-23 12:23:28 · 4889 阅读 · 0 评论 -
Excel-数据透视表/图,动态扩展数据源
目标:当数据源发生变化时,数据透视表/图也相应发生改变,即自动刷新数据。初始数据源如下:步骤一:制作数据透视表/图,如下所示,当我们在此数据源中删减数据时,在数据透视表/图中手动刷新便可以得到更新后的结果;但当我们在此数据源的基础上,添加数据行或列时,在数据透视表/图中手动刷新数据源却不会显示新的结果。这时,需要我们对数据源进行格式化,方法很简单,如步骤三:选中数据源中的任意单元格...原创 2019-10-21 16:17:44 · 1250 阅读 · 0 评论 -
Excel-将图表数据标签是0的值不显示
目的:将Excel图表中,数据标签显示为0的值去掉。如下图:上图中的柱形图显示了两组数据标签,为将数据标签的值为0的不显示,需要将两组数据标签分别设置数字格式。步骤一:选中柱形图,右键,选择“设置数据标签格式”,将“数字”下的“类别”项选择为“自定义”,在下面的“格式代码”中,输入“0;;;”,点击“添加”按钮。如果需要坐标轴的数字保留小数,则可以将“格式代码”添加为“0.0;;;...原创 2019-10-11 10:10:00 · 28114 阅读 · 0 评论 -
【Excel】给文本框赋值/写入公式,使文本框中的内容随单元格同步变化
目的:在Excel图表中插入文本框,在文本框内写入公式,使文本框中的数值可以变动。如下图中,Excel图表的中的文本框,其数值从2000到2009,可变。步骤一:在生成的图表中,插入文本框,然后点击输入框,在里面输入“=",然后点击目标单元格A13,默认为锁定单元格引用。回车。步骤二:根据需要设置文本框和框内字体的大小,颜色,透明度等。完成。...原创 2019-10-10 17:12:03 · 29357 阅读 · 5 评论 -
Excel-散点图横坐标改为日期格式
Excel 散点图创建以后,日期格式变为数值,Excel表格如下:生成的散点图如下:我们看到横坐标是数值,并不是我们需要的日期类型。原因是我们数据源中的日期类型为常规:如果要改为我们需要的日期格式,方法很简单,只需要手动将数据源中的日期改为标准日期型yyyy/mm/dd后,图表横坐标自动变为日期。如下图完成。...原创 2019-09-24 11:27:22 · 22843 阅读 · 0 评论