新浪博客

EXCEL2013如何自动填写复杂表格？

2016-03-15 08:05阅读：

http://blog.sina.cn/dpool/blog/u/2687384272

工作中经常会遇到大量填写表格这种重复枯燥的工作，有没有什么好办法呢？有人说使用ACCESS,但对我等数据库菜鸟，并非易事。而且在我的工作中公司要求上交生成的表格，与ACCESS统一生成的表格差别非常大，这就不得不另外想办法，现在把我的经验分享如下，希望能够帮到遇到和我同样情况的朋友。

本文中使用的软件：EXCEL2003/2007/2010/2013（WPS2016可使用）
  • 制作待填写空白表格
注意每个项目名称单独占用一个单元格，填写内容另占一个空白单元格
EXCEL2013如何自动填写复杂表格？
  • 准备好相关填写内容的数据表格
数据表格与空白表格放在同一个工作簿内 这样在后面引用时会更方便
数据表格内的数据要求填写完整 但对其数据类型并不象ACCESS有严格的要求，总之就你方便填好就行
每个项目名称对应一个数据表格
这有点类似于ACCESS数据库中的数据库表，这叫“有据可查”
EXCEL2013如何自动填写复杂表格？
EXCEL2013如何自动填写复杂表格？
  • 填写空白表格
作好以上准备工作就可以快速批量填表了
  • 建立索引
通常我是以姓名为索引的，但也遇到重名的情况，这不要紧，只要将数据表格中重名项稍作修改就可以了，比如在重名后加个1和2，否则估计电脑会发生神经错乱哈哈！
建立索引的下拉菜单是使用了EXCEL中数据>数据验证>设置：验证条件>允许 选“序列”，勾选“忽略空值”和“提供下拉菜单”，来源 请点击右角的小图标，跳转到相应的工作表，把姓名项下的全部人名框选进来。点击确定后就生成了一个索引名单。EXCEL2003中没有这么方便，需要调出VBA编写一段小的程序，再试运行通过就可以了，但我觉得在这个与时俱进的年代，2003版真的可以考虑升个级，哪怕到2007也行啊，毕竟版本高用起来更方便。
EXCEL2013如何自动填写复杂表格？
  • 逐一建立填写空格内的函数
  • IF(ISERROR(VLOOKUP(D4,员工数据表!$C$1:$AJ$128,1,FALSE)),'',VLOOKUP(D4,员工数据表!$C$1:$AJ$125,2,0))
  • EXCEL2013如何自动填写复杂表格？
  • 上面这个函数看着很复杂，现在拆解给大家看：
  • 逻辑函数 IF 函数 如果条件为真，该函数将返回一个值；如果条件为假，函数将返回另一个值。

    语法

    IF(logical_test, value_if_true, [value_if_false])
    例如：
    • =IF(A2>B2,'超出预算','正常')
    • =IF(A4=500,B4-A4,''）
    参数名称
    		 说明
    logical_test （必需）
    		 要测试的条件。
    value_if_true （必需）
    		 logical_test 的结果为 TRUE 时，您希望返回的值。
    value_if_false （可选）
    		 logical_test 的结果为 FALSE 时，您希望返回的值。

  • ISERROR 函数 (DAX)

    检查某个值是否为错误，并且返回 TRUE 或 FALSE。
    数据分析表达式 (DAX) 中的一个函数，DAX 是用于 Excel 中的 PowerPivot 中定义计算的公式表达式语言。许多 DAX 函数具有与 Excel 的函数相同的名称，行为也相似；但 DAX 公式只能用于 DAX 函数中。

    语法

    ISERROR (< 值 >)

    参数 value 要测试的值


  • 返回值
    为 TRUE 的布尔值（如果值为错误）；否则为 FALSE。

    示例

    下面的示例计算 Internet 总销售额与分销商总销售额之间的比率。ISERROR 函数用于检查是否有被零除之类的错误。如果存在错误则返回空白，否则返回该比率。
    = 如果 (ISERROR (SUM('ResellerSales_USD'[SalesAmount_USD])
    /SUM('InternetSales_USD'[SalesAmount_USD]))
    ，BLANK()
    ，SUM('ResellerSales_USD'[SalesAmount_USD])
    /SUM('InternetSales_USD'[SalesAmount_USD]))

  • VLOOKUP 函数

    这是一个查找和引用函数。语法
  • VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

  • 参数名称
    		 说明
    lookup_value （必需参数）
    		 要查找的值。要查找的值必须位于 table-array 中指定的单元格区域的第一列中。
    Table_array （必需参数）
    		 VLOOKUP 在其中搜索 lookup_value 和返回值的单元格区域。
    该单元格区域中的第一列必须包含 lookup_value（例如，“姓氏”）。此单元格区域中还需要包含您要查找的返回值
    col_index_num （必需参数）
    		 其中包含返回值的单元格的编号（table-array 最左侧单元格为 1 开始编号）。
    range_lookup （可选参数）
    		 一个逻辑值，指定希望 VLOOKUP 查找精确匹配值还是近似匹配值：
    • TRUE 假定表中的第一列按数字或字母排序，然后搜索最接近的值。这是未指定值时的默认方法。
    • FALSE 在第一列中搜索精确值。
    例如：
    • =VLOOKUP(105,A2:C7,2,TRUE)
    • =VLOOKUP('袁',B2:E7,2,FALSE)
    • 说明：在 table_array B2:E7 的第一列（B 列）中查找值“袁”，返回在 table_array 的第二列（C 列）中找到的值“洛”。 range_lookup FALSE 返回精确匹配。
    • http://www.chuanke.com/v5231155-150891-548438.html这里附一个视频讲解VLOOKUP函数，讲得非常清楚而且目前还是免费的。

  • 绝对引用 在列标号与行号的前面加“$'

上面写了这么多，全都看不懂也没有关系，简单把它翻译过来是这样：

在 '员工数据表！$C$1:$AJ$128”区域中的第1列查找D4单元格中的内容，如果查找到，那么就在 '员工数据表！$C$1:$AJ$128”区域中的向右第2列中找到这一行对应的单元格的值，就是这么个先竖着找到这个人名字，然后再顺着这个人的名字找到他对应的值，让空白单元格D4等于这个找到的值.

我的更多文章

下载客户端阅读体验更佳

APP专享