pb数据窗口如何设置一列中连续相同数据的背景颜色_Excel学习 （从秒杀二级到数据分析，不断更）...

基础入门版（随便找了一本书+b站王佩丰老师网课）、

一、Excel单元格的各种操作（只记录比较繁琐的），很多都可以在:右键设计单元格格式里面找到

1.高效复制单元格：录入一个数据后，按住鼠标的填充柄往下拖。

2.合并单元格：选中单元格后，要么在开始选项里面找；要么右键找工具栏；最全的是在设置单元格格式。

3.数据格式设计：选项卡功能中的数字功能栏；右键设计单元格格式。

4.设置边框：边框的选择是可以叠加的，注意有一个功能（绘制边框），考试画斜三角常用。

5.填充格式：右键设计单元格格式——填充栏

6.最低级的函数：数据之间的关系运算公式，先打一个 = .

7.关于Excel自动检验输入数据的有效性：数据选项卡——（数据有效性）数据验证窗口——输入数据序列（也可以是整数，文本的有效性），在来源中自己打值，不同的值之间用英文逗号隔开（一般是这样用的，允许多个值，如“现金”，“转账”，“指标”）——输入提示信息——输出出错：1.禁止，一点都输不进去。2.警告：询问你一下，再来改。 这样在设计好的单元格里面，就会自动检验了。 可以用“圈释无效数据”一次将无效数据圈出，便于修改。（会出现单元格有下拉框的情况，很不错很方便的！）

在数据有效性中，自动设置输入法：数据有效性——输入法格式——可以选择xx模式

8.区域的快速复制 不可选中 Ctrl+拖拉 是无效的，应该放到表格边缘——按住鼠标右键——选择复制到此位置。

9.条件格式：条件格式——突出显示单元格规则（这个有很多种过滤选择方式，具体情况具体分析）

二、Excel工作表的各种操作

1.关于表格标题：公式——定义的名称——根据所选内容创建名称窗口——一般选择首行（表格格式就是这样写的哈）这样索引会快很多（其实也还行）

2.通过选项卡可以插入行，删除行，隐藏行，隐藏列。但是有一个问题哈，我隐藏完了之后，好像就没法恢复了。

3.数据（寻找都是在数据功能栏）的排序与筛选，分类汇总和显示，都是对Excel表的操作。

整个sheet算是区域，而带有有效数据的单元格集合才是Excel表。（转为Excel表后，）

4.先排序，再分类汇总。不然系统会爆掉。

排序与筛选专栏

多个分类关键字下如何进行分类汇总：

1.点击排序按钮，有主要关键字，继续点击添加条件(产生次要关键字）2.再进行两次分类汇 总

2.如何在分类汇总后单单复制需要的表格：定位到可见单元格再进行复制。

3.排顺序组合的时候，要注意单元格内容的文本格式是相同的

5.拆分和冻结窗口功能：在“视图栏”，用于检查，对比数据。

6.套用表格样式！！这个非常好用，一出来全都给你设置打包好了。就是对考试不太友好，一般要自己设计。（插入——表格——可以同样得到结果）

7.数据导入工具

三、数据透视表专栏

数据透视表可以说是排名前几名的Excel中最有价值的功能。

1.想产生数据透视表（插入——创建数据透视表）

2.一般来说，数据透视表的行信息和列信息来源都是原始表格的第一行的“栏目”信息。

为什么说数据透视表好用呢？因为可以根据鼠标将一般的“栏目”信息，将具体的信息名称按照一定勾选的次序，然后按照自己的需求将不同的栏目信息名称放到“行”和“列”中，这洋会很方便地产生排序和分类汇总。

3.关于数据透视表中的值字段功能设置——“分析标签”——字段设置——值字段设置，这样就可以设计计算方法为汇总啊，求平均值啊这些（或者用另一个方法：在数据透视表的左上方有一个值，说了这是xxx项，点击修改即可）。

4.在数据透视表中也可用数据切片器：分析——插入切片器

5.数据透视表可以进行随时的刷新和删除。

6.如果行字段是有两个分类项目的话，可以变成经典模式连续两次拖拽（如何为经典模式看下文ps） 日期分类变成别的分类（随意点日期，右键，分组，自己选）

7.行的值为数据的话，可以在数据内进行一定范围的分类（右键创建分类组）

8. 汇总多列数据：在拖拽过程中，把数据这个单元格拖拽到汇总单元格即可。

9.在数据透视表中插入公式：数据透视表的工具选项——项目和集（工具）——插入计算字段

PS:发现还可以通过预览数据透视表（右键，数据透视表——显示选项，选择经典的数据表布局，后面可直接拖拽），将想要进行对应分析的值拖拽进对应的“行”“列”“值”之中，这样会更加的直观。

四、图标分析工具

1.这里强调一下图标形式中的选择数据：选择数据功能——选择数据源——编辑——系列名称（可以用来改名系列）

2.添加图表元素——先单击一下图表，跳出图表操作快捷工具栏——添加图表元素——在其中选择需要添加的图表元素，自己改名即可。

3.ps:在选中图表后，图表右上角有三个快捷工具——增加图表元素，快捷样式，等

五、Excel公式和函数

1.运算符 连字符& <>不等于， 在一个公式中，比较运算符的优先级大于算术运算符，true可以当做1来运算，false可以当做0来运算。

2.加减乘除，可以用文本状态的数字进行加减（文本强制加减乘除后，会自动转为数值状态，可进行函数运算），而使用函数的时候用文本状态的数字无法进行运算。

3.公式的意义在于可以直接和单元格联结。在Excel的公式中，文本是需要用双引号勾出来的

作为入门级别的内容，只介绍了其中几个重要函数。

1.相对引用和绝对引用（锁住的概念）：“$列标$行标” 列标和行标之前有无符号决定了它是不是绝对引用，如果是绝对引用（有$）则无法拖拉得出一列数字。反之，则可。一般都是用混合引用=相对引用+绝对引用（一般用于可以计算区域，又要往下拉，又要往右拉的那种）

2.任何函数之前都要打个 = 符号。

3.判断型函数 IF函数

=IF（逻辑判断，“真返回值”，“假返回值”）都是英文符号！

IF的逻辑嵌套 =IF(逻辑判断，“真返回值”，IF（逻辑判断，“真返回值”，“家返回值”））

if函数给数分层要讲究逻辑顺序，层层迭进（剥洋葱）。

如何避开嵌套呢（太麻烦了！）

（针对数字）=if（逻辑条件1，“真返回1”，0）+if（逻辑条件2，“真返回2”，0）+........

(针对文字） =if（逻辑条件1，“真返回值”，“”）+if(逻辑条件2，“真返回值”，“”）+...................

（实际上也很烦）————最好的方法用 vlookup！！

if（iserror（运算），0，运算）（可以用作在工作表计算中回避错误）

if（and（条件1，条件2.........），“真返回值”，“假返回值”）

if（or（条件1，条件2，..........），“真返回值”，“假返回值”）

4.保持数据唯一性函数 COUNTIF（单元格数据范围，单元格值）=1，查找个数，然后判断是否为1，等于则返回true，否则，返回false.（可以结合数据有效性，这样可以防止输入重复数据）

COUNTIF(单元格数据范围，“条件”）——返回满足条件的个数

对于数字，countif只判断前10位数字是否满足相同要求。

特殊的条件格式：条件格式栏——新建条件格式——根据公式设定条件格式——选择公式（这个和countif函数的使用密切相关）

5.查找并引用函数 VLOOKUP函数

= VLOOKUP(查找的值，查找区域，预定返回值的列号（在之前的查找区域），精确匹配（0）或者近似匹配（1）

其他函数我们将会在进阶Excel学习中开始第二层次的学习。

6.最基础函数：求和函数 =sum（）求平均函数=average（）最大值函数=MAX() 最小值函数 =MIN（） 排名函数 rank（排名对象，排名范围（需要锁定，绝对引用 按F4），升降序） 在不方便不断写求和公式地时候（跳跃式求和，只要是跳跃式都可以这样用下文方法），可以先定位空格，然后在开始栏找到公式按钮，可以自动查找求和。count（）计数函数

7.批量填充！！！ Ctrl+回车！！！！！

其他：

1.查找和替换（在开始工具栏）快捷键Ctrl+F

如果选定一个词a直接替换为b，那么不管是不是单独的词a还是包含了a的词c，都会变化，这样会出问题。解决：在替换栏中找到选项——选择单元格匹配，可以解决这个问题。

替换不仅仅有内容替换，也有颜色格式等替换。都在替换栏的选项——格式 中。

两个通配符：（Excel中只认英文符号）

1. * 表示 之后的任意字符（不限个数）2.？表示任意的一个字符

2.定位工具

通过名称框定位单元格及区域位置；自己选择一个范围的单元格，先自己起名字，后面可以直接输入名字调用。

定位——定位条件(很重要）

1.批注条件 需要显示所有批注——在审阅栏中找

批注的样式修改，批注在Excel中是被当做图形来修改。（要想修改，先得弄出绘图工具栏，里面有更改形状）或者在批注上选中，右键设计批注格式。

2.公式条件定位

3.合并条件 如果要查找的条件已经是合并的单元格，这样不太好查找。解决方法：先将已经合并的单元格撤回原来的样子，选中空的单元格（查找与运算——定位条件——空值），按住 = ctrl+

（上方有选中的，即将填进下面空格的内容），然后回车，这样会把空格中填入需要的内容。

这个在工作中常常使用！！！然后就可以查找咯！

4.定位条件之对象定位

常常用于清理图片，因为图片太多，一次选不太好选。操作类似！

---

好久没更新了，之前已经忙着自我的输入，也没有顾得上时间去输出一下。通过计算机二级的考试，我发现我的Excel技术脚系统学习之前有了质一般的飞跃！偷偷给王老师鞠个躬。

函数专栏的补充

Sumif函数

Sumif（寻求范围，寻求的具体条件，求和目标的范围（列））

和countif一样，对于数值，只能识别前15位 怎么识别全部呢: 条件&”*”

Sumif有强大的容错能力，第三参数可以简写（就当无事发生，了解即可）,如果非要使用第三参数的简写，那就得目标和目标值的选值是平行的，不然会进行错位计算

小提升：为什么跨列用sumif需要对查询值用绝对引用：sumif（xxx，xx，$A$1）:

如果有多个条件进行sumif，有一个小窍门：创造一个辅助列：在列内的单元格：创造一个公式：用连字符把多个条件在一起，然后对这个列进行sumif计算

更好的方法：sumifs（求和目标区域，条件区域1，条件1,条件区域2，条件2....，）

有一个类似于vlookup的替代方法（因为目标范围只有一个数自己本身，给自己求和，那就是查找）sumif（单元格范围，目标条件，求和自身目标的范围） 只能找数字的“vlookup”

Vlookup函数专题——常用于跨表查询

1. vlookup函数语法

使用vlookup函数，要这样理解 一行是关联的信息，一列是查找的个体范围

=Vlookup（查找的目标，查找范围（范围的圈画条件是，查找的目标和需要返回的目标都要圈到且最左列必须是要查找的目标列，最好弄成绝对区域（F4）引用，除非是列全选），输入需要返回的值所在的相对列数（所谓相对就是之前已经圈画的范围内开始从左往右数），0（精确匹配）/1（模糊匹配））

2. Vlookup中使用通配符

有时候查找的目标会使用简写，比如网易公司写成了网易，这个时候我们要加通配符进行查找——&”*” 任意符通配符

3. Vlookup模糊查找

找近似值，只会找小于等于目标数中的最大值，一般对于那种生活中自然而然有分层的，我们常用模糊匹配

4. 使用处理数字格式引起的错误

如何解决：在公式中，将数值变成文本（F4&“”）连了，又啥也不连。

将文本变成数值 （F12*1）乘了，又没乘

5. Hlookup函数

如果查的表和数据源的排列不同，需要用hlookup 就是vlookup的方向转换，列变行来看

6.match函数 查找返回函数

=match（寻求目标数，搜索范围，0/1） 会返回目标数在哪里

7.Index函数（引用取回函数）

=index（查询目标列，match（寻求目标数，搜索范围，0） 效果和vlookup一样哦

index（目标范围，返回行数，返回列数）

index和match的多重组合可以搞一个二维数组的查询：index（目标范围，match（条件1，搜索范围，0），match（条件2，搜索范围，0）），这个是非常经典的

Vlookup有一个弊端，就是只能从左来查询返回右，如果需要右查左，就得用match和index来结合 高级函数方便，底层语言自由。

返回多列结果函数：

1.Column（）返回列号函数，可以自动返回所在列数，很好用！

！！Vlookup（目标数（列要绝对引用），查找数据范围，column（）（这个表的相对位置要自己选择），0）然后可以横向拉右键

Excel常用日期与时间运算

可以通过单元格格式将数字和时间进行转换

1.（常规格式） 默认为一天，一个整数÷24 可以转化为小时，再除以60可以转化为分钟，再除以60就会变成秒——这个很有效！然后可以进行时间的加减运算

如果是同一个单位，那就无所谓，可以直接加减，不用乘除 当时间单位不统一时，先变成天（整数），然后数值乘24，得到小时，再乘60得到分钟。

推算日期（考虑到了月份和年份）

Day（）Year（）Month（）

Date（）日期函数——date（年，月，日）都是数字，然后可以自动计算出日期

如果想要计算推迟月份 date（year（单元格），month（单元格）+推迟的月份数，day（单元格））

计算给定月份的最后一天——思路就是算出下个月的第一天然后－1

求得本月一共有多少天，算出最后一天是几号，最后用day函数显示，这个月的最后一天是几号就有几天

计算日期间隔： dateif函数，这是一个隐藏函数，dateif（开始日期，结束日期，“y”）得到间隔年份（“d”）得到日，“m”得到月份，“ym”得到年月，如果都想出现，可以用&连接起来几个函数。

求日期是当年的第几周——weeknum（日期值，确定首日的习惯（周几是第一天））

Weekday（日期值，一周中的第几天）

条件格式——管理条件格式，里面有可以用公式的：可以自己写公式、可以改变公式。

在整体表格选中，注意不要横向平移切换，也就是不要全部相对单元格引用。

简单文本函数

1.截取字符串

Left（）函数 从左边开始截取 left（目标单元格，取几位字符数），然后就会返回你想要的了

right（）函数，和left函数差不多。

Mid（）函数 mid（目标单元格，从第几位开始取，取几位）

=right（left（目标单元格，取几位），再取几位）常规用法（用数据很方便）

一个很好的函数技巧 ，针对18位身份证的查询最后一位的方法 =right（ left（目标单元格，17），1）

Left（目标，find（“寻求的某个文本”，目标单元格-1）） 总之，先找到共同点的思路在哪里，然后构思创造函数满足所有情况

要想取位数取满，可以说取100位这样（夸张的手法）。

2.如何计算文本的长度：

Len（）——求有几个字符，有几个字就会等于几、lenb（）——求有几个字节，一个汉字2个字节

前面有数字+后面有中文字 40摄氏度、288人 这样的，把中文字分出来的公式

Right（目标单元格，lenb（目标单元格）-len（目标单元格）） 抓住在汉字中字节和字符的差异

PS:(一个例子）关于身份证的问题：

在使用left函数的时候注意，left函数是文本函数，不能直接用vlookup来查询，要用之前学过的文本数字相转换的方法来做

数学函数与引用

1. Round函数、Roundup函数、Rounddown函数、Int函数

Round（目标数字，四舍五入保留几位）、Roundup（目标数字，往上进位保留几位，不考虑四舍五入）——Rounddown（）int（）取整函数

2. Mod函数

大多数时候不直接使用mod（被除数，输出）直接得到余数 求小数：mod（被除数，1）

基本上还是和if函数啊这些一起用 mod（目标，2）判断奇偶

3. Row函数与column函数

基于位置引用的基本原理：

Row（）啥都不写，自动返回所在单元格的行号

Column（）啥都不写，自动返回所在单元格的列号

下面都得先找到目标数字自己的行列规律，才能用column和row函数，结合index函数

关于转置：

1. 选中一列需要转置的目标，随便找一个单元格，然后右键——选择性粘贴，有一个转置按钮

关于跳跃：找到等差数列的规律

关于分列：相当于转置和跳跃的集合，找到那一小块中行列单元格之间的关系（这个需要多练习）

Rank函数：可以用来用公式进行排序，rank（目标排序数，排序范围）

Lookup浅谈数组

1.数组生成原理

一列=一个数 这个就会返回一串数组，里面不是true就是false，true可以代表为1，false可以代表为0，进行之后的运算。 数组不能选择整列！数组要把能选的数据一个个都选了。

数组可以理解为很快地一组数的筛选？（高级函数的原理）写法很简单，思路很重要

sum（（列目标1=目标1）*（列目标2=目标2）*金额列目标） 金额列目标也不能都全列选

1. 对于列目标的区域，我们得用$进行绝对引用，这样得到的结果就可与拉结果

2. 对于包含数组的公式，不能直接回车得到结果，而是Ctrl+shift+回车

3. sumproduct函数

一般认为是不需要三件连按的函数，其他和sum一样

Sumproduct（（区域1=条件1）*（区域2=条件2）*求和区域）

4. lookup函数

lookup（寻求目标，寻求范围，返回的列数）lookup没有第四参数，精确匹配

5.indirect函数：

5.1 了解indirect函数的意义及语法

5.2indirect函数与Index函数引用方式的对比

5.3处理跨表

5.4跨表引用时的单引号问题

可以辅助很多函数做事情

indirect（单元格）自动把写的单元格给翻译了 如a1为佩奇 自动返回佩奇

之前处理过一个问题是用index函数的，先通过row或者column函数来确定目标的列或者行号，然后用index来返回。这里我们使用indirect (“e”&row())，这样就可以先得到单元格，再用indirect函数翻译为原本单元格内的值

跨表：vlookup（“张三”，indirect（A4&“！A：G”），7,0）通过文本来表示地址，就得用indirect函数

indirect函数对于跨表很好用。（我得思考一下）就是跨表那个表名字会变！

跨表！！！回头再看看！

indirect函数对于某些地域起名称

选中区域——公式选项卡——定义名称——新建名称——比如张三

sum（indirect（张三 选中单元格就行））

indirect（f1） 将选出来的省，来选择省内的城市，(不过首先要先把省内的城市定义新建名称)

find函数（指定字符，字符串所在的位置）

经典动态图表实现原理

1. 图表的系列必须是一列数据或者一行数据，否则无法生成图像；有几个系列就有几个图例。在图上右击某个系列——选择数据，然后就可以编辑图表。要学会把数据图表分开来做

具体做动态图标的过程：。

方法1：开发工具——插入——表单控件，勾选格——右键勾选格，选择一个单元格，会出现这样的结果，选中，单元格为true；没有选中，单元格为false.，假设之前选中的单元格为g2，g2目前为true

If（g2，列1，空列）注意（g2，列1，空列都需要是$引用 ）

在表中用文本引用数据 ——表名！数据

我们可以给一个函数命名：公式——定义名称

Ps：隐藏了某一列后，如何恢复：比如你隐藏了B列，那就选中AC两列，再右键取消隐藏，就恢复了。

方法二：用offset函数+数据透视表（比较难理解）

Offset（某个点为基准，下一n行，右移n列，取n行，n列），offset可以用来取自定义的区域，公式表明一个动态的数据区域。

Counta（）计算范围单元格中不为0的单元格个数

插入控键——滚动条——右键设置控件格式

用法都差不多：做一个简单设置，然后再和Excel中某个单元格关联起来 本次例子为D1 、D2单元格 接下来用offset的函数来取——offset（某个基准单元格，下移D1，右移D2，1）这样就能得到动态的列表数据了！！

甘特图和动态甘特图

定义：中间轴为0，有2组数据各自往左往右的一种特殊的图

先要把两组数据选一组，变成次要坐标轴

想要坐标轴0在数据百分比的中央，就要右键——设置坐标轴格式，最大值为1最小值为-1，这样坐标轴0就在中央了。

如果想选择坐标刻度值，右键——设置坐标轴格式——刻度单位——然后自己调整即可

想要在图表里面添加东西，先选中图表，然后看到“布局”工具栏，再进行调整。

调整图片的格式：点中图片——格式栏——艺术效果，一般背景图都用模糊虚化效果，这样不会影响关键的输出

甘特图 一种上面的更进阶的图：

难度并不大，有两组数据，一组为紫色的，另一组为白色的，白色的数据条覆盖了之前的紫色数据条 选择图表中的堆积条形图 然后改条的格式就好，算是一种障眼法hhh

数据系列格式——分类间距——可以设置条形的宽度，动态的目前用到的地方不是很多，跳！

饼图美化和PPT图表

一、双坐标轴的补充知识

1. 主次坐标轴设置

2. 主次坐标轴的柱形避让——对来两组数据，分别将已经存在且重叠的数据表，一个向挤，另一个向右挤（两组数据并排那种）

二、饼图美化

讲的是三维饼图——点击饼图——三维旋转——可以自己调节高度和深度 ——数据格式——三维格式——选择各种效果

双层饼图！高级！

当完成其中一个饼图的时候，改变一下系列值，选中需要的值的数据，Excel会自动变为（双层饼图）——但是目前这个程度是看不到的，因为两层饼图完全的重叠在了一起——设置数据格式——设置为次坐标，这样就能分开了。（先整体拉大图（小饼会一起跟着拉大），然后再把小饼一块一块地拉回去）

三、PPT中的图表

1. 图表更新（不要把Excel的图表粘贴为图片到PPT中）

如果从Excel中复制粘贴一个图标给PPT然后变化数据之后：点击PPT的图表——图表工具——刷新数据，这样的方式只能一个个图表的进行数据刷新，不够便利。

那么，如何进行可以自动更新的图表呢：复制图表，在PPT中——开始——粘贴——选择性粘贴——粘贴链接——Excel图表对象，这样就好了！！后面可以自动更新了。

2. 另一个则是图表的系列对象和PPT动画相结合的操作（暂缺）

王培峰的数据透视表精选（b站有记得要去学）

对于Excel的话，很多文件都是保存为xlsx

保护Excel表中的文件： 选中数据、审阅——保护工作表

其他：

套用表格格式的时候，不能把合并的单元格一起套用了。

排序——自定义排序——可以选择优先级别的关键字

Excel里面有查找和选择——一般选择空值和错误值

数据工具——删除重复项——（小窍门）先全部取消全选，然后再按题目选择需要的

数据——合并计算——引用多个表里面的数据进行跨表计算（按照题目来）

不能在表格状态下进行数据的分类汇总，要选中表格右键将其转化为区域。

Rank（目标，范围（这个范围是需要绝对区域的，不能混用区域））

在公式中如果是自己打上去的，基本上都要加引号！如果是引用的单元格（直接点上去），那就不要！日期函数date360（起始日期，最终日期）

数据透视表专栏

（下面进行数据透视表专题学习）