首页 > Excel专区 > Excel教程 >

最基础的excel常用函数和excel技巧分享

Excel教程 2021-11-13 11:53:38

一、索引&索引函数——配置和计算的基础扩展

为什么觉得索引那么重要?

因为在配置和计算中,常常遇到如下问题:

1、在做某几个表格的计算或配置时,常常会用到另一个表格的内容,手动查找太费时了。

2、当需要调整某个数据时,往往牵一发而动全身,需要修改与其相关的很多配置或计算表格,如果是手动修改效率很低,且没办法保证准确性。

而若在数值计算或配置中用到索引函数,可以保证数值来源的一致性和可靠性,也可以提高配置的准确性,当然也带来了很大的便利(修改某个数值可使相关单元格随之而更新)。

索引表格的一般用法

1、数值计算

第一步:在excel里用一个单独的页签或区域作为“数据库”存放原始数据。即一些最原始、需要手填的数据表,如物品ID和物品名称的对应表、英雄偏向性、英雄ID和其他资料的对应表等等。

1)、格式注意:每个表格的首列/行一般是用唯一标识如ID或者名称,这些都是用于索引的关键词。

2)、当然某些策划因为需要常常看到“数据库”中的内容,选择把数据库放在计算表格旁边,这种做法也是可以的,最好加个颜色标注。

第二步:在其他页签中进行计算,在计算过程中凡是要用到原始数据的部分,都必须用索引函数从“数据库”中取出来,这也之后凡是有要改动的地方,只要改最原始的数据库就OK啦。

1)、如英雄属性的计算,如果要改动一个英雄的偏向性,或者游戏中总的属性上限,只需要改数据库中对应英雄的偏向性系数,其他的升级、进化属性的计算表和配置表会随之而变,可直接观察结果并复制配置。

最基础的excel常用函数和excel技巧分享

2)、 如图中就是一个最简单的例子,计算区域的数值全部是由两个基础的表格来的,只需要填入关键词(即需要计算的是什么类型、几星、什么品质的英雄),即可索引出对应数据并计算。而且我只要修改上方数据源某个数据,下方计算区域也会随之而改变。

3)、该表格为粗糙的示例表格,正式使用时可变化方式。

2、 配置方面

1)、配置方面运用到索引函数就更多了,而且比较繁杂,一般是从计算表中索引最终数值到对应配置位置,或批量更名等小配置的修改,不是很有统一的步骤,但是用熟了索引函数之后会发现那些都不是事儿。

2)、此处以批量更名为例:

最基础的excel常用函数和excel技巧分享

  • 首先图中用vlookup根据配置表中左侧原本的名称,在更名表中索引出更改后的名称。
  • 然后用iferror处理更名表中没有的内容,即不更名的那部分英雄,名称=原本配置的名称。
  • 最后将B列更名后的名称直接复制,以”选择性粘贴”中的数值粘贴到A列,再把B列的辅助列删除即可。当然为了影响最小,你可以选择把辅助列放到表格最后面那列。

常用的索引函数

叨叨了那么多,终于进入正题了,其实索引的方式有很多种,但是为了避免混乱,以下只介绍3种。

1、 vlookup/hlookup——最常见的索引函数

1)、用途:

根据首列(或行)的信息查找后面特定列(或行)中指定的内容。

2)、用法:

最基础的excel常用函数和excel技巧分享

  • Vlookup(索引标志/条件,索引的数据库范围,返回列数,精确(fault)/模糊(ture)查找))
  • Hlookup(索引标志/条件,索引的数据库范围,返回行数,精确(fault)/模糊(ture)查找))

3)、 特殊用法:双重索引

  • 在单元格中输入=vlookup(条件1&条件2, if({1,0}, 条件1所在列&条件2所在列, 返回内容所在列), 2, false)
  • 输入完后记得要按ctrl shift enter组合键才会出来结果。

4)、优缺点

  • 优点在于单一条件索引时简单直观,且可索引各种类型的内容。
  • 缺点在于不方便做多条件索引,且限制了原始表格的表头只能都在列上或都在行上。

2、 Sumproduct——多少条件都不怕的数值索引函数

1) 用途:

  • 将符合索引条件的单元格输出,若满足条件的对象大于等于2个,则会输出所有对象的加和结果,这是个双刃剑。
  • 多重条件索引的利器,但无奈限制于只能输出数值类型的结果。

2) 用法:

最基础的excel常用函数和excel技巧分享

  • sumproduct((条件1=查找范围1行/列)*(条件2=查找范围2行/列)*(条件3=查找范围3行/列),查找范围(表、行、列都行))
  • 如果想快速记忆的话,可以考虑用矩阵相乘的方法理解,以D16为例。

(1)$A16=$A$1:A$10这句是指在A1:A10范围内匹配出与”1星”相同的位置,匹配成功记为“1”,否则记为“0”,最终得出一个列矩阵。

最基础的excel常用函数和excel技巧分享

(2)$A$1:D$10这句是指在A1:10范围内匹配出与”血量”相同的位置,匹配成功记为“1”,否则记为“0”,最终得出一个行矩阵。

最基础的excel常用函数和excel技巧分享

(3) 用之前所得的列、行矩阵相乘,得出一个最终的矩阵图。

(4)用该矩阵图去匹配所选的区域范围A1:10,选出矩阵中为“1”的点的数值,若有多个,则将其加和后输出。

最基础的excel常用函数和excel技巧分享

3) 优缺点:

  • 优点在于可N重条件索引,凡是数值类型的索引都强烈推荐此函数。
  • 缺点在于索引结果只能为数值。

3、Match、index函数共用——鸡胸肉一般的索引函数

1)、用途:

  • 可同时对行、列进行索引,即对一般同时带有行、列的表头的表格进行双重索引。
  • 可对最常见类型的表格进行行列的双重索引,弥补了前两个函数的一些不方便的地方。

2)、用法:

  • Index(查找区域,match(目标值1,对应行), match(目标值2,对应列), 0)
  • match(匹配值,匹配区域,查找模式):寻找特定值在某个表头中的相对位置,查找模式一般用 0(精确查找),另外1=小于、-1=大于。
  • index(行数,列数):根据特定行数、列数查找到制定位置的信息。
  • 这个就直接上图好了,不明白的可以看看附件。

最基础的excel常用函数和excel技巧分享

3)、优缺点

  • 优点在于可索引所有类型的数值。
  • 缺点在于只能用于指定格式的数据源,但是这种格式也是最常见的了

4)、使以上用索引函数时的一些注意点:

  • 要注意使用绝对/相对引用

(1)写索引函数选择“数据源”区域后,记得用绝对引用。

(2)在写索引函数用于索引的条件时,也需要根据当前表格结构选择相对或绝对引用。

  • 不要随便使用剪切功能

(1)宁愿使用复制和删除,剪切和移动会导致引用的公式出现错误。

(2)这其实是基本常识,即使不是索引函数,使用其他excel函数时也需要注意。

  • 如何扩展“数据源”表格?——插入单元格

(1)经常会遇到数据源需要增加内容的情况,如物品价值表中又加入了几种道具啥的,此时需要做的是:将需要添加的新内容插入到倒数第一行/列的前面,这样原来引用该区域的单元格会自动扩展其引用区域。

P.S.可在扩展完数据库后再调整表格内容结构,同样注意使用复制和删除,而不是剪切!

  • 每个表格的第一列/行,一般是用来做索引的关键词如ID、名称或属性标志,尽量避免根据第2行去索引第1行数据的情况。

标签: Excel常用函数excel技巧excel

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.