|
VLOOKUP函数对我们奥迪特来说，是再熟悉不过了，但它有个不足之处，就是我们搜索的条件值必须是选定区域的第一列，而INDEX+MATCH组合使用可以克服该不足。今天先简单总结一下VLOOKUP函数，然后介绍一下INDEX+MATCH组合使用。 1、VLOOKUP VLOOKUP函数的主要功能是搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。其形式是：VLOOKUP（参数1，参数2，参数3，参数4）。 以下图为例：利用VLOOKUP函数找出税费的金额，在E1单元格中输入公式 参数1：指的是需要在单元格区域搜索到的值，即为上图中的D1单元格，我们需要在单元格区域（A1：B5）搜索到“税费”（D1）； 参数2：指的是包含参数1的单元格区域，且参数1必须在该区域的第一列，即为上图中的A1：B5，（实际操作时，别忘了使用F4快捷键对该区域进行绝对引用，目的是避免在向下填充时改变条件区域） 参数3：指的是我们想要返回的数值在参数2区域的第几列，因为我们想要知道税费的金额，所以需要返回参数2（A1：B5）中的第2列。 参数4：指的是是一个逻辑值，指定 VLOOKUP 查找精确匹配值还是近似匹配值。在审计过程中，一般都需要查找精确匹配值。即为“False”或者“0”。 综上所述：E1中的公式就应该是：=VLOOKUP(D1,$A$1:$B$5,2,0) 2、INDEX+MATCH函数 如下图所示：需要找出水费的金额，这次条件列在我们需要的返回值的右侧，则可以采用INDEX和MATCH函数。 （1）MATCH函数 如下图所示，MATCH函数的作用是：提取指定单元格所在的行数。E2单元格公式=MATCH(D2,B1:B6,0)的意思为：D2单元格内容在B1：B6区域内位于第几行。其中0指的是精确匹配。 （2）INDEX函数 如下图所示，INDEX函数的作用是：提取对应行数的内容。E4单元格公式=INDEX(A1:A6,4)的意思为：A1：A6区域的第4行是什么内容。 （3）MATCH+INDEX组合使用 在上张图中，我们很容易就知道水费在所选区域的第4行，所以可以直接写=INDEX(A1:A6,4)，从而返回水费所对应的金额，但如果表格很大且我们需要匹配很多项目时，就需要先利用MATCH函数提取出行数，再利用INDEX函数，提取对应行数的内容。如下图所示，E6单元格公式=INDEX(A1:A6,MATCH(D6,B1:B6,0))的思路是：先确定D6单元格内容在B1：B6中的行数，再确定该行所对应的B列的内容。 Excel快捷键一、界面切换篇 Alt + Tab 在最近打开的两个界面中转换 Windows + Tab 在所有打开的界面中转换 Ctrl + PgUp /PgDn 在Excel工作簿中切换不同的Excel表格 二、查找替换篇 Ctrl + F 查找 Ctrl + H 替换 其实这个查找、替换功能几乎所有人都知道，但我今天想强调的是一个“单元格匹配”功能，以Ctrl + H为例，如下图所示：我们需要把下面这张表中的数值为“0”的单元格清空，但不能删除其他数值中的0，如D2单元格中的数值102900中的0。 操作方法：如果我们直接Ctrl + H，然后在“替换内容”框中填入“0”，在“替换为”框啥也不填，就会把表格中所有的“0”替换掉，如下图所示，把其他数值中的“0”也删除了。 正确操作方法：Ctrl + H → “选项” → “单元格匹配”→ “查找内容”框填入“0” → “替换为”框啥也不填→ 点击“全部替换”，这样子，删除的仅仅是数值为0的单元格，而不会影响其他的数字。 三、定位篇 Ctrl + g 定位 四、编辑篇 Alt + Enter：在某个单元格中强制换行 F2：使单元格出于编辑状态 我们一般都是双击单元格使单元格处于编辑状态，其实F2键也可以，同时也提一下，F2也是重命名文件名称的快捷键。 Ctrl + F2：快速建立批注 Tab快捷键 横向移动单元格 一般的，我们电脑的设置是：按Enter键，将会向下移动单元格，而不是横向移动，而Tab快捷键就可以做到。提一句，如果因为特殊需要，我们希望按enter键时，移动方向为向右，可以进行的操作为：点击“文件” → “选项” → “高级” → 在“按Enter键后移动所选内容”下方的“方向”框中选择即可，如下图所示：
五、公式篇 F4：这个快捷键有两个比较重要的作用：（1）、在输入公式时，在绝对引用、相对引用之间进行切换；（2）、重复上一步操作，比如重复插入行，重复填充颜色等，多试试就可以； F9：选择公式的一部分，然后点击F9，就可以得出这部分公式的计算结果。如下面两图所示
Esc: 将公式还原到原来状态。 当我们在编辑公式时，出现错误时，可以按Esc键。 Ctrl + [ 和 Ctrl +] -下文中详细介绍 六、刚刚发现的一个技巧，还蛮有趣，哈哈 Alt + P：预览快捷键。 这个快捷键就是在excel或者word文件没打开时，单击该文件，使用该快捷键，便能预览该文件内容，不可以编辑的，但我试了试，是可以进行“复制”的。所以，当我们需要取数且文件已打开很多时，避免电脑打开新文件很慢，可以采用这个快捷键。如下图所示：我在D盘中单击工作簿1，然后按Alt + P，右侧就会出现预览界面。
Ctrl+[ 这个快捷键你知道吗？在excel表中，很多单元格都会用到公式，引用不少单元格，数据可能来源于不同的工作表甚至是工作簿。这些公式有的是含有计算功能，比如求和等；也有的可能就是单纯的链接到别的单元格，不同excel表格之间可能含有各种内在勾稽关系。有时候，我们发现前后excel表格的数字出现矛盾了，就需要充分考虑各个表格之间的内在勾稽关系，并一步步找出错误出现在哪一步，这个时候“追踪引用单元格”以及“Ctrl+[”就会发挥功能了。 当某个单元格数据仅仅来源于另一个单元格时，使用“Ctrl+[”时很快捷的，除此之外，还是要使用“追踪引用单元格”按钮，显示所有的引用单元格。下面举两个例子来说明其用法。 例子1：在Sheet1中的A1单元格等于Sheet2中的G5单元格加上sheet3中的H8单元格。我们想快速的定位到这两个单元格，并分析该公式是否正确，进而一步步往前推，看看是在哪一步的数据处理出现了差错。 操作步骤：把鼠标放在Sheet1中的A1单元格——点击“公式”下的“追踪引用单元格”按钮（如图1的右上角所示“追踪引用单元格”按钮）
就会出现图2：
双击箭头的任意位置，就会出现图3：
接下来，我们只要单击“定位”窗格下的单元格，就能跳转到相应的单元格，而不需要我们自己手动转换工作表去寻找。 例子2：在Sheet1中的A1单元格等于Sheet2中的G5单元格。像这种数据仅仅来自其他excel表格的某一单元格，我们就可以使用“ctrl+[”快捷键，快速定位到引用单元格，并进一步分析哪一步的计算或者链接出现了问题。 相应的，与追踪引用单元格类似，当我们需要知道这个单元格被哪些单元格利用了，即找其从属单元格时，就可以用到“追踪从属单元格”以及“ctrl+]”两种方法，大家可以自己试一试。 注：上面这种做法主要是因为涉及的单元格在不同的工作表。如果数据都在同一个excel工作表，只需要双击单元格，就能显示所有的引用单元格。有所欠缺，希望指出，一起进步。 |
|
|
