小白如何在短时间内精通 EXCEL ？

如何才算精通Excel？

数据分析非常熟练， 熟悉工程、财务、统计等大部分不常用的函数，动态图表的熟练使用，数组公式的普通应用，各种图表、控件、窗体、有效性等综合应用

分析工具熟练使用如单变量、规划求解 ，模拟运算表 ，XML

初步了解vba和简单程序编制 ，制作自定义函数，自己设计解决方案，可以处理90％以上的Excel问题，在所在公司已属于一流水平，常常作培训，很容易找工作。

这种其实已经是数据分析师级别的Excel使用能力，而我们普通的上班族，尤其是会接触很多数据的互联网运营，财务，行政人员，需要做到：

掌握30个以上的常用函数，公式使用较熟如多重IF判断，熟悉常用图表，会使用名称、自定义和条件格式、有效性、组及分级显示、高级筛选、自定义排序、合并、分列，共享，快照

普通数据透视表，设计普通自动计算功能的表格，安全性的概念与简单应用，会录制宏，简单控件应用，可以解决常见Excel问题，其Excel水平在所在部门小有名气，常有小女孩或小帅哥找他帮忙。

我觉得大部分人只要做到第二个水平，就很不错了。那么按照他的这个标准，我建议你掌握下面这些能力

建议收藏保存，当然了，如果你记不住，你可以给自己安一个外挂，比如下面这个

这样是不是很棒？

SUM 函数

最基本函数之一，此函数用于对单元格中的值求和。

IF 函数

它可以对值和期待值进行逻辑比较。 因此IF 语句可能有两个结果。 第一个结果是比较结果为True，第二个结果是比较结果为False。

例如，=IF(E2>60,"合格","不合格")， 表示如果E2>60,那么就输出“超出预算合格”，否则就输出“不合格”

VLOOKUP 函数

很多同学都接触过VLOOKUP，经常使用 Excel 的人应该都很熟悉这个函数。通过该函数，你可以查找垂直列中的数据。例如，如果你要处理一个按月份排列的利润列表，VLOOKUP 可以显示你所选择月份的数据

该函数的语法规则如下：

VLOOKUP(要查找的值,要查找的区域,返回数据在查找区域的第几列数,[精确匹配/近似匹配])

比如我们要查找小王成绩，已知查询区域为A2:E9，分数在第五列，精确输出分数，则单元格写

=VLOOKUP(A13,A2:E9,5,0)

XLOOKUP 函数

本人已经弃用vlookup，改用xlookup了，他的优点就是不用再框选那么大的数据区域，不用再数第几列，只需要选择好条件列和结果列，即可。

语法：=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

第一参数（必须参数）：lookup_value，想要查找值

第二参数（必须参数）：lookup_array，想要在哪个数据区域中查找

第三参数（必须参数）：return_array，要返回的数据区域，就是结果所在的区域

如下图所示，在这里我们同样想要查找小王的成绩，只需要将公式设置为：

=XLOOKUP(A13,A1:A9,E1:E9)

就能找到正确的结果，在这里我们需要注意的是前三个参数是必须参数，后三个参数是可选参数，也就是说前三个参数必须填写，后三个参数可以选择性填写，在普通查找中，一般将其省略即可

MATCH 函数

使用MATCH 函数在范围单元格中搜索特定的项，然后返回该项在此区域中的相对位置。

例如，如果A2:A5 区域中包含值推广费、注册费 和办公费，那么公式=MATCH(“办公费",A2:A5,0) 返回数字3，因为办公费 是该区域中的第3项。

CHOOSE 函数

该功能用于根据索引号从最多254个值中选择一个。例如，如果value1到value7表示一周中的7天，当1到7之间的数字用作index_num时，CHOOSE将返回其中的一天。

比如下面这个案例

我们要回传第一个同事的名字

CHOOSE 中的第一项，则为小刘，输出小刘

SUMIF 函数

SUMIF 函数是数据分析领域的一个重要函数。通过该函数，你可以将符合特定要求的数字单元格中的值相加。

在下面的例子中，我们通过此函数将单元格中成绩大于 70 的数字相加。

DATEDIF函数

用于计算两个日期之间的天数、月数或年数。

假如A1单元格写的也是一个日期，那么下面的三个公式可以计算出A1单元格的日期和编辑当天的时间差，分别是年数差，月数差，天数差。注意下面公式中的引号和逗号括号都是在英文状态下输入的。

=DATEDIF(A1,TODAY(),"Y")计算年数差

=DATEDIF(A1,TODAY(),"M")计算月数差

=DATEDIF(A1,TODAY(),"D")计算天数差

DAYS 函数

此函数用于返回两个日期之间的天数。

FIND、FINDB 函数

FIND和FINDB函数用于在第二个文本字符串中定位第一个文本字符串。这两个函数返回第一个文本字符串的起始位置值，该值是根据第二个文本字符串的第一个字符计算的。

假如要查找 Kg 在“26865kg”中位置。双击单元格，输入=FIND("kg",B10)，就可以知道kg在第五个位置

FINDB函数是把汉字算作2个字符，FIND是把汉字算作1个字符，这个函数常常和right，left，mid函数一起，截取一定字符。

INDEX 函数

该函数用于返回一个数值或对表格或区域中数值的引用。

INDEX(数组或区域, 行号, 列号)

比如说下面这个案例，要引用Eze，可以在表格中输入公式=INDEX(A2:A4,3)

A2:A4的第3个单元格，就是“Eze”

INDEX 的数据区域可以不是一行或者一列，可以是一个区域.

要在数据区域中调取注册费3月份的费用

可以在单元格中输入以下公式。

介绍几个常用的Excel函数组合，一定要收藏！

MATCH(lookup_value,lookup_array,[match_type])：返回符合特定值特定顺序的项在数组中的相对位置。

INDEX(array,row_num,[coulumn_num])：在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。

Index和Match组合可以根据条件查找字段，并且可以在条件改变后自动更新结果。除了可以实现VLOOKUP函数的查找功能外，还可以实现逆向查找，从右向左查找数据也轻松搞定。

我们看下面这个案例:根据月份和费用项目求金额。

解析下哈

INDE函数为=INDEX(区域,行数,列数）

意思是在数据区域，按照行数和列数找到数值，并且返回

3月在第四列，工资在第五行

那么INDEX应该为 =INDEX(A1:G6,5,4)

但是月份和项目是灵活的，这时候我们需要用MATCH函数找到具体的月份和项目的行数和列数

用match找到项目的具体行数

列数=MATCH(B10,$A$2:$A$6,0)

然后使用MATCH函数找到月份的具体列数。

=MATCH(A10,$B$1:$G$1,0)

最后，使用INDEX根据行数和列数提取值。

=INDEX(B2:G6,MATCH(B10,A2:A6),MATCH(A10,B1:G1))

结果就是

vlookup和Match的组合类似于第一种Index和Match的组合，这个函数主要是用于列数不确定的数据查询。

我们来看下面这个案例：

根据B13单元格的姓名，来查询对应的项目（部门，职务，性别，分数）

如果用 vlookup 函数的话，我们需要用match函数来判定项目在等几列

也就是 =MATCH(C12,B1:E1)

然后用vlookup查找，那么公式变为

=VLOOKUP(B13,A1:E9,MATCH(C12,A1:E1),0)

函数:可以计算满足条件下的最小值

案例如下，比如我们要就算女性中最小分数的话。

c13单元格公式:

C13==MIN(IF(D2:D9=B13,E2:E9,1000))

注意:首先使用IF函数确定B列中的性别是否等于D2指定的性别。如果满足条件，则返回C列对应的分数，否则，逻辑值为FALSE；。

接下来，MIN函数用于计算最小值。MIN函数有一个特点就是可以自动忽略逻辑值，所以只会计算数值部分，最后得到指定部门的最低分。

解析：IF(D2:D9=B13,E2:E9,1000)

这个函数会依次将D2:D9的数据和B13进行比较，如果是一致的，也就是true，会返回相应的C列值，false会返回1000，这样的话IF函数就形成了一个数组

{1000,11,73,4,49,66,1000,1000,1000,66}

然后 MIN 函数会在其中挑选最小值。

MIN(1000,11,73,4,49,66,1000,1000,1000,66)=4

就是这么个结果了。

功能:计算一个单元格中有多少项。

举例: 比如要计算每个人参与的项目数量

LEN 函数用来计算字符数量

SUBSTITUTE 函数是替代函数，可以实现某一文本字符串中替换指定的文本

SUBSTITUTE(text,old_text,new_text)

我们的思路是

SUBSTITUTE(B2,”、”,)去掉 B2 中的“、”

然后这样的话就得了去除“、”之后字符，然后计算这个字符长度。

然后用没有去处“、”的字符长度LEN(B2)，减去去掉“、”的字符长度，就是“、”的数量，之后再+1，得到

=(LEN(B2)-LEN(SUBSTITUTE(B2,”、”,))+1)

然后我们再做个判断B2，如果B2是空值，就返回0，不是空值，就返回1

最终得出

=LEN(B2)-LEN(SUBSTITUTE(B2,”、”,))+1)*(B2””)

也就“、”+1的数量，也就是项目数

功能:从单元格中分隔汉字、数字和字母。

示例:比如从 A1 中获取汉字

那么我们的B2单元格可以写成如下:

=LEFT(A2，LENB(A2)-LEN(A2))

解析：

LEN 函数无论是汉字还是字母都会记为1个字符

而LENB 函数会把汉字汉字计为2个字符，LENB(A2)-LEN(A2)就会得出汉字的数量

之后用 left 函数左侧截取单元格汉字数量的字符串，就能剥离出汉字

出道题：如果我要是截取英文该怎么办呢？

功能:这个函数用来多条件的逻辑判断

例:假如我们判断，金额不到500，且没有到期，则提醒“补款”

用AND函数构建条件 AND(A47.TEXT+MID 组合

这个函数可以用来提取字符串中间部分，然后转化为对应的形式。

最常用的案例，就是提取身份证号中的出生年月

首先我们需要用MID函数提取出生日期，也就是MID(B2，7，8)

意思是b2单元格第7个字符开始，向右截取8个字符，那么刚好是生日信息19881109。

然后用TEXT函数把这个字符串改为，我们想要的格式，格式设置为0-00-00”的样式，结果是“1988-11-09”。

这个格式信息应该写啥，我们可以在单元格设置，自定义中学习，不用死记硬背。

我们在用VLOOKUP时候，经常会遇到找不到的情况，只会会返回错误值#N/A,非常难看

加入我们需要，在没有找到的时候返回“无”的时候，我们就可以用IFERROR

可以在目标单元格中写

=IFERROR(VLOOKUP(A8,A1:D5,3,0)，"无")

这个函数可以用来计算去重后，单元格出现的次数

举个例子，我们要计算一共有门店

COUNTIF函数是用来计算每个客户出现的次数

然后我们再取倒数，相同门店相加，必然是1

1/COUNTIF(A3:A18,A3:A18)

之后再用SUMPRODUCT函数将这些1相加，就等得出门店数

=SUMPRODUCT(1/COUNTIF(A3:A18,A3:A18))

这个组合可以对近几天的数量进行求和

示例:在D3单元格中，返回B列最近7天的销售量。

OFFSET函数可以是一个引用函数，类似于鼠标拖拉选择了一块区域

比如offset(C2,1,2,3,1)

他的意思是从C2为起点，像下一个单元格，向右两个单元格，一共3行，一列的区域

那么知道这个函数的话，那就好办了

我们以B1为原点，计算要移动到的位置，即移动数，也就是COUNT(B1:B14)-7，

用OFFSET(B1，COUNT(B1:B14)-7，0，7，1)，找到B1往下7格，往右0格位置，形成7行，1列的数据区域

然后求和

=SUM(OFFSET(B1，COUNT(B1:B14)-7，0，7，1))

这个函数用来一找多，比如有多个记录符合条件

比如说下面这个案例，我们需要找到陈强在4月份的所有出勤日期，但是很明显他有多条记录

=INDEX(B:B,SMALL(IF(A$2:A$10=E$1,ROW($2:$20)),ROW(A1))

解析:

IF(A$2:A$10=E$1,ROW($2:$20):如果列B的名称与F1的名称相同，则返回其行号。不同返回FALSE。

Row(A1):返回A1的是行号1。如果公式被复制下来，它将成为行(a2)并返回2。实际上，它是用来生成序列号的:1，2，3...当公式复制下来后，再取满足条件的第一行号和第二行号。...

SMALL:从最小的合格行号中逐一选择合格的行。

INDEX:根据获取的行号从C行中选择一个值。

在 Excel 中，数据透视表可让你以简单的方式分析和可视化你的数据。

使用数据透视表，你可以更快地进行比较和创建计算。你甚至可以创建图表来可视化你的数据。

如果你是第一次创建数据透视表，可能会感到困惑。但在本文中，我将解释开始创建数据透视表所需的一切。

不止于此——我还将向你展示如何添加图表，以便你可以可视化数据。

此外，你使用的 Excel 版本无关紧要。你甚至可以在 Excel 2013 中创建数据透视表。我在本文中以 Excel 13 为例。

为了向你展示如何创建数据透视表，我创建了一个包含一些虚构的足球运动员的表格，其中显示：

我将创建额外的进球贡献 Goal Contributions 和进球率 Goal Ratio 行，也就是每场比赛的进球。

在足球（Soccer）中，进球贡献是进球和助攻的总数。进球率是用进球数除以比赛场数得出的。

要创建数据透视表，请执行以下步骤：

第 1 步：在菜单栏中，单击“插入”并选择“数据透视表”：

第 2 步：保持一切不变，然后选择“确定”：

你应该使用新的工作表，以便为数据透视表提供专用工作表。

你将看到的下一个界面如下所示：

你将处理“数据透视表字段（PivotTable Fields）”，你会在那里看到表格的列。

这是你可以创建行、列和进行计算的部分。

要为你的数据透视表创建行，请将现有表中的一行拖到 “ROWS” 的部分。

例如，我想用原始表的“名称”行为数据透视表创建一行，那么我需要将这一行拖到 ROWS 区域：

你可以看到我用原始表的“名称”行创建了一行。

为了方便计算，你可以使用 “VALUES” 区域。

我想看看每个球员的进球数。因此，我将“进球得分”行拖到 “VALUES” 区域：

我可以直观地看到每个球员的进球数。

你还可以在 “VALUES” 区域中进行其他计算。只需单击右侧列前面的下拉菜单，然后选择“值字段设置（Value Field Settings）”：

我想看到最高进球数，而不是所有球员的总进球数。所以我会选择 “MAX” 并点击 “OK”：

现在我可以看到最大进球数，而不是所有进球数的总和：

还记得我说过我会创建额外的进球贡献和进球比率行？现在来创建吧。

我需要创建助攻和进球得分行来计算进球贡献。因此，我将确保它们都在 Values 区域中：

现在，我将确保选中“分析（Analyze）”选项卡，单击“字段、项目和集合（Fields, Items, & Sets）”，然后选择“计算字段（Calculated Field）”：

你将看到的下一个界面如下所示：

在这里，我会做三件事：

现在，我已经成功创建了进球贡献行：

要创建进球比率行，我必须确保已完成的比赛行位于 VALUES 区域中：

我将使用的公式是进球数/比赛场数。所以，我将再次实现计算字段：

我现在可以看到每个球员的进球率：

创建数据透视表并轻松实现计算固然很好，但在图表中查看数据透视表的图形呈现会更好。

在图表中呈现数据透视表：

第 1 步：确保选中“分析（Analyze）”选项卡，然后选择“数据透视图（PivotChart）”：

第 2 步：在右侧选择你想要的图表类型，它可以是柱形图、饼图或条形图。另外，选择上部的格式，它可以是 2D 或 3D。

满意后单击“确定”。

以上就是呈现数据的图表。

当应用到用于设置格式或纠正问题的重复性解决方案时，VBA 非常高效。 举例来说，你是否曾经在 Word 中更改过每一页顶部的段落样式？ 你是否曾经不得不对从 Excel 粘贴到 Word 文档或 Outlook 电子邮件中的多个表格重新设置格式？ 你是否曾经必须对多个 Outlook 联系人进行相同的更改？

如果你有必须进行十多二十次的更改，则可能值得使用 VBA 来自动进行该更改。 如果是必须进行数百次的更改，那么当然值得考虑。 你可手动完成的几乎任何格式设置或编辑更改都可用 VBA 完成。

例如，在 Word 中，你可以通过使用 Application 对象的 ActiveDocument 属性更改属性，并调用当前 Word 文档的方法。 此 ActiveDocument 属性将返回对当前在 Word 应用程序中处于活动状态的 Document 对象的引用。 “返回引用”意思是“可以访问。”

下面的代码完全按照它所表达的含义运行；即，保存应用程序中的活动文档。

VB复制

从左到右阅读代码，“在此应用程序中，使用 ActiveDocument 引用的文档调用 Save 方法。请注意， Save 是最简单的方法形式;它不需要你提供任何详细说明。 你指示 Document 对象执行 Save 指令，它不需要你输入更多的信息。

如果某个方法需要更多信息，则这些详细信息被称为参数。 下面的代码运行 SaveAs 方法，该方法需要文件的新名称。

VB复制

方法名称后的括号中列出的值是参数。 此处，文件的新名称是 SaveAs 方法的参数。

看完这些代码之后，很多人马上抓耳挠腮

我擦！！我对编程一窍不通，也不想写代码，怎么办！！？？

这个不用担心，其实根本不用你写代码。

我们可以用【宏】来代替手动写代码。

简单来说，Excel 宏指的是，使用 Excel 内置的编程语言 VBA (Visual Basic for Applications）写的，能在 Excel 环境里运行的一系列操作指令。我们在 Excel 里手动进行的几乎所有操作，宏（VBA）也都可以操作。

打个比方，如果需要你写一篇内容，一个是需要你用笔亲自写，一个是你口述，另一个人帮你代写。

宏就是可以找人帮你代写代码，他会把你的行动记录下来，整理成VBA,之后你再自己运行VBA，就能实现重复操作。

只要会用 Excel，就可以学会录制并使用宏，提高工作效率。接下来，一步一步教你如何录制一个简单的宏，并运行它。

录制和管理宏，需要使用「开发工具」选项卡。默认情况下，Excel 不显示此选项卡，需要自定义开启。

开启步骤如下：

第一步：在功能区任意一区域点击右键，弹出的菜单中选择「自定义功能区」。

第二步：在右侧选项卡列表中，找到「开发工具」并勾选。

第三步：点击确定，这时在功能区可以看到「开发工具」了。

接下来是设置宏安全性。默认情况下，为防止来源不明的工作簿自带宏自动运行，Excel 会禁用宏的运行。

为了运行自己录制的宏，按如下步骤设置宏安全性。先点击开发工具选项卡里，「宏安全性」命令。在弹出的设置菜单中，按如下方式设置。

Excel 2007 及以后的版本中，工作簿中想要保存和运行宏，必须将其另存为「启用宏的工作簿」文件类型。

按一下步骤另存为启用宏的工作簿：点击「文件」命令，菜单中选择「另存为」，在弹出的另存为菜单中，保存类型列表中选择「Excel 启用宏的工作簿」，点击保存。

前期工作已经完成，接下来可以录制你的第一个宏了。这里说明一下，同一个电脑，开启开发工具和设置宏安全性只需一次设置，以后无需再重新设置了。

接下来一步一步，我们录制一个规范日期格式的宏，即，将「2018.1.1」格式日期，使用 Excel 替换功能，转换成「2018-1-1」标准格式日期。

第一步：在开发工具选项卡→代码命令组中，点击「使用相对引用」。

第二步：接上一步，先选中数据区域，再点击「录制宏」按钮。

第三步：在弹出的对话框中，输入宏名，指定快捷键，选择保存在「当前工作簿」，填写说明。点击「确定」开始录制。快捷键我们设置为「Ctrl + Shift + Q」。

第四步：这一步我们按正常操作，使用替换功能，将「.」替换为「-」。注意，这个步骤，不要使用鼠标重新选择数据区域。

第五步：点击功能区「停止录制」按钮，停止录制。到这一步，就完成了录制。

上一步中，我们给宏「规范日期」设置了「Ctrl + Shift + Q」快捷键。现在，选中你要转换的区域，按对应快捷键即可。对，就是这么简单。

对已经录制的宏，使用宏管理菜单，对其进行编辑、删除、执行等操作。点击开发工具选项卡→宏命令，即可打开宏管理菜单。

宏具有两个特性，一个是自动批量执行重复操作；一个是，操作简单，容易学会。如果你不想学习背后的 VBA 知识，也可以正常使用宏，提高你的工作效率。

这就是Excel学习的过程，以及关键功能的介绍，希望对你有用。