excel使用技巧-数据引用.docxVIP

现实中excel的功能已经能够帮助我们解决很多问题。今天一个同事问我了一个 问题，说能否建立一个excel模板文件自动检测其他的excel文件，检测其中的 数据，如果受检测的excel文件做任何数据的改动，这个模板文件会自动更新数 据。想完成这一目的，使用excel的引用即可。 excel数据引用分为四种，我们给大家简单介绍下： 1、excel数据相对引用。这种最为简单，通常大家都知道，就是普通的公式计 算。简单介绍几个公式，相信大家都很清楚。 、在d2单元格中需要计算：数据一和数据二的平均数与数据三的差。就可以 输入 =(A2+B2)/2-C2 或者输入 =sum(A2:B2)/2-C2 其中，括号必须是英文状态或者是半角中文状态， 其中的列号ABC输入大小写没 有关系，都会自动变成大写。如果求和的数据过多则不要用加号了， 用sum求和 函数较为方便。(求括号内英文冒号两侧的单元格之间的数据之和) sum函数不 但可以求横向的行之和、竖向的列之和，还可以求对角线所辖区域的矩形区域之 和。 ：^J文件? 骗辑⑥ 视團? 插入① 格式工具(I) 111 B C D E 1 缴据一娄据二_ 数据三 结厶 祢1=1 2 : . 1201 50)-(A2+B2)/2-C2 SUM z X V =(A2+B2)/2-C2 、计算：数据一、数据二、数据三的最大值与最小值的差，可以输入： =max(a2:d2)-mi n( a2:d2) 其中，max是求最大值函数，min是求最小值函数。和sum一样，这两个函数都 支持对角线所辖区域矩形的数据运算。 、计算：如果数据一大于1500,则综合数据是数据一、数据二、数据三求和， 若数据一介于1000至1500之间则计算数据一和数据二之和，若数据一低于 1000,则只记录数据一的结果。(这个例子在公司计算工资的时候很常用，但是 要非常小心边界的判断，需要的时候可以用 =表示大于等于，用=来表示小于等 于)关于这个if判断语句的写法要非常小心括号的运用，我们可以在有图早期 的一篇个人所得数计算公式中看到多次嵌套的运用。 =IF(A21500,SUM(A2:C2),IF((A21000),A2,SUM(A2:B2))) 文件心編辑⑥视喲观讎入② 格式也】 ? . ... - ? . - °-°匚 - ■ . .. ? - . : 工具①数皓? 窗口②帮勖⑩ -J 亠』」J jj-X 1 /和氏逗▼沢辺妙 卸 D5 * /■ ；=IF(A51500, SUM(A5:C5)fIF((A51000)JA5, SUM(盒5:B5))) A C D E F G 1 H 1 數据一 数据二 数据三 综台 特别留心边界值，如 2 500 120 50 500 3 1000 120 50 1120 图中的1500和1000 4 1200 120 50 1320 「都是边界值。这是写一 5 1500 120 50 1020] 6 2000 120 50 2170 f判断语句特别小心的 7 8 这三个例子主要是介绍大家常见的三种模式：直接运算，用excel的函数进行运 算，用程序语句进行运算。(这种提法是我自己随便想的，不一定有真正的三中 模式之说。) 2、excel数据的绝对引用。参加了办公自动化培训班的朋友和自学 excel的朋 友应该对他不陌生。举例说明一下，还以上面的那些数据为例： 计算数据一中每个数据与最大值的差。 (呵呵，也很常用的，就是计算平级的人 或者部门之间的差距) 我们一般会在d2单元格输入 =MAX(A2:A6)-A2 可是我们无法通过拖动，生成但 d3、d4、d5等下面单元格的数据，当我们拖动 的时候，在d3单元格变成了 =MAX(A3:A7)-A3 这显然不是我们要的，我们在 d3单元格想要的是 =MAX(A2:A6)-A3 所以，我们可以使用$来固定那些需要不变的数据，如上面a2单元格的公式就可 以写成 =MAX(A$2:A$6)-A2 随你怎样拖动，max计算的区域就不会变化了。如果你想固定的更牢靠一点，可 以在列标号前加$。 3、同文件内excel表间引用。同文件内不同表之间的数据引用和计算通常是使 用英文状态的引号表名结合而来的，例如本例中： 引用表1当中的a列数据，可以在a2单元格输入 =1!a2 其中英文单引号中为表的名字，表和单元格名称间用英文的 ！分割开来 A2 ▼ / ?=J r ■ A2 A c D 1 2 5001 ! 3 1000 4 1200 5 1500 6 2000 7 M ~^ |\ 1 \ Z/Sheets / 4、不同文件间的excel引用。使用英文的中括号[]来引用文件，如我们将正 在操作的文件保存到d盘，默认名字为book1.xls新建一个excel文件