数值/执行策划分享:excel常用函数及技巧分享

发表于2016-05-01
评论6 4.3k浏览

 




前言:做游戏策划的时间也不算短了,从执行策划开始一步步累积经验,前段时间和某个前辈聊天,他告诉我不要有经历无总结,每个人都应该有自己的方法论和技巧的总结。受到他的启发,我决定由基础开始写一些对于以前工作技巧方面的总结,或是心得方面的分析,今天就先由最最基础的excel常用函数和技巧开始吧,如果有幸有人阅读到,且觉得对他有用,那我会非常开心~也希望各位同伴们能多多指教分享。

P.S.光看是很无聊且很难有收获的,建议有兴趣的伙伴可以打开excel一边尝试一边看哦。


目录:

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

        二、一些好用的小函数

        三、一些方便的小技巧



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

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

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

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

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

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


索引表格的一般用法

1、数值计算

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

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

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

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

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

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

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

2、  配置方面

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

      b) 此处以批量更名为例

 

                 i. 首先图中用vlookup根据配置表中左侧原本的名称,在更名表中索引出更改后的名称。

                 ii. 然后用iferror处理更名表中没有的内容,即不更名的那部分英雄,名称=原本配置的名称。

                 iii. 最后将B列更名后的名称直接复制,以”选择性粘贴”中的数值粘贴到A列,再把B列的辅助列删除即可。当然为了影响最小,你可以选择把辅助列放到表格最后面那列。


常用的索引函数

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

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

      a)  用途:

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

      b)用法:            

            1. Vlookup(索引标志/条件,索引的数据库范围,返回列数,精确(false)/模糊(true)查找))

            2. Hlookup(索引标志/条件,索引的数据库范围,返回行数,精确(false)/模糊(true)查找))

      c) 特殊用法:双重索引

            1. 在单元格中输入=vlookup(条件1&条件2, if({1,0}, 条件1所在列&条件2所在列, 返回内容所在列), 2, false)  

            2. 输入完后记得要按ctrl+shift+enter组合键才会出来结果。

      d) 优缺点

            1. 优点在于单一条件索引时简单直观,且可索引各种类型的内容。

            2. 缺点在于不方便做多条件索引,且限制了原始表格的表头只能都在列上都在行上。

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

      a) 用途:

            1. 将符合索引条件的单元格输出,若满足条件的对象大于等于2个,则会输出所有对象的加和结果,这是个双刃剑。

            2. 多重条件索引的利器,但无奈限制于只能输出数值类型的结果。

      b)用法:

           

            1. sumproduct((条件1=查找范围1行/列)*(条件2=查找范围2行/列)*(条件3=查找范围3行/列),查找范围(表、行、列都行))

            2. 如果想快速记忆的话,可以考虑用矩阵相乘的方法理解,以D16为例。

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

 

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

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

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

      c) 优缺点:

            1. 优点在于可N重条件索引,凡是数值类型的索引都强烈推荐此函数。

            2. 缺点在于索引结果只能为数值。

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

      a)用途:

            1. 可同时对行、列进行索引,即对一般同时带有行、列的表头的表格进行双重索引。

            2. 可对最常见类型的表格进行行列的双重索引,弥补了前两个函数的一些不方便的地方。

      b)用法:

            1. Index(查找区域,match(目标值1,对应行), match(目标值2,对应列), 0)

            2. match(匹配值,匹配区域,查找模式):寻找特定值在某个表头中的相对位置,查找模式一般用 0(精确查找),另外1=小于、-1=大于。

            3. index(行数,列数):根据特定行数、列数查找到制定位置的信息。

            4. 这个就直接上图好了,不明白的可以看看附件

      c) 优缺点

            1. 优点在于可索引所有类型的数值。

            2. 缺点在于只能用于指定格式的数据源,但是这种格式也是最常见的了

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

            1. 要注意使用绝对/相对引用

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

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

            2. 不要随便使用剪切功能

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

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

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

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

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

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



二、一些好用的小函数

1、 Substitute(目标单元格,"要替代的字符",“替代成什么字符”)

      a) 将目标单元格的部分字符串以新的字符串替代,和len函数组合也可以算出目标单元格中某字符的个数

      b) 如len(A1)-len(substitute(A1, “a”, “”)),可计算出A1单元格中包含”a”字符的数量。

      c) 挺好用的一个函数,值得研究一下,附件中有示例。

2、  LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

      a) 作用:提取指定单元格中的数字。

      b) 注意:若该单元格字符串中有两段数字,也只能返回第1段数字,若需多次提取可与Substitute()结合使用先将第一个数字设为空值。

      c) P.S.建议用到的时候直接复制并将其中的“A1”改为目标单元格即可!

3、offset(初始单元格,列偏移量,行偏移量,输出行数,输出列数)

      a) 可用于相对应索引,或重复输出某个位置的数值,在附件中有示例。

4、 iferror(目标单元格,错误后的输出值)

5、 sumif(Range,Criteria,Sum_Range)  

      a) Range代表条件判断的单元格区域;Criteria为指定条件表达式;Sum_Range代表需要计算的数值所在的单元格区域。

      b) 如=SUMIF(C2:C63,“女”,D2:D63),即可求出“女”生的语文成绩和。

 

6、 countif(区域,条件)

      a) 作用:统计符合特定条件的单元格个数。

            1.大于50=COUNTIF(数据区,">50")。

            2.大于或等于E5单元格的值=COUNTIF(数据区,">="&$E$5)

            3.>=10并且<15 =SUM(COUNTIF(数据区,">="&{10,15})*{1,-1})

7、 left(目标单元格,截取字符数)、right(目标单元格,截取字符数)、mid(目标单元格,从第几个字符开始截取,截取长度)

      a)  针对某个单元格中的内容,从左边、右边或中间截取某段信息。

8、 EVALUATE:可引用某个单元格中填写的内容作为公式,计算出结果,但注意这不是可直接写在单元格中的公式!

      a)  具体例子可见附件,要用到一点点VBA,当然定义名称也可用,但是那样非常的不灵活,所以我还是选择分析VBA的方式,放心这是基础分享不会涉及太多的。

      b) 具体步骤:

            1. 打开一个excel

            2. 打开开发工具→查看代码

                  P.S.若没有查看代码的页签,也可按Alt+F8→创建宏,名称任意。

            3. 在对应表格插入模块

            4.  在新的模块中填写如下代码(先删除原来的所有内容)

            Function fun(func As Variant)

            a = func

            fun = Evaluate(a)

            End Function

 

           5. 然后你就可以在单元格中用=fun(公式所在单元格),进行计算了,甚至还可配合其他的函数。

      c) 顺便再介绍一下,把这个函数变成所有表格都可使用的函数

            1. 将刚才写了模块的excel另存为”加载宏”类型的文件。

            2. 然后关闭该文件。

            3. 打开其他任意一个普通excel文件。

            4. 点击左上角【文件】(或office图标)→选项→加载项→转到→浏览→选择刚才另存为加载宏的文件→勾选该文件→确定。

            5. 以后再使用其他任意excel时,都可用到这次写的fun()函数啦。



三、一些方便的小技巧

1、 快捷键类型:

      a) F4:重复上一操作、绝对/相对引用

            1. 对单元格点击可重复上一个操作,比如删除单元格、改变填充颜色等。

            2. 在函数中的单元格名称旁点击可对其绝对/相对引用,多次点击为切换引用模式(何必一个个的打$呢)。如A4:A6旁点击可变为$A$4:$A$6。

 

     b) F9:公式部分运算

 

            1. 主要用于查错。

            2. 选择对单元格公式中的某段公式使用,可立即得出该段公式计算结果。

      c) ctrl+shift+方向键:朝着指定方向选定所有已填充的单元格。

            1. 很好用的,尝试一下就知道,特别是在上百行数据时更能体现便捷性。

 

      d) &:连接几个单元格中的内容

            1. 如想将A1、A2、A3的内容连接起来,只要在其他任意单元个中写=A1&A2&A3。

      e) PHONETIC(A1:B10):连接多个单元格内容,虽然属于函数但是觉得和&挨在一起讲比较容易理解。

            1. 不可用于数值类型,若要用于数值类型需将对应单元格格式转化为文本。

      f) ctrl+g

            1. 选定区域后按,可定位选中空置、可见、或某些特殊条件的单元格。

2、 简单操作类

      a) 双击单元格右下角:自动填充。

            1. 计算后公式不用手动拉,双击一下单元格右下角即可。

            2.在上百行数据需要填充时可体现其便捷性。

      b) 批量修改:选择一堆单元格--打字填充某个单元格--按快捷键ctrl+enter可全部填充。

      c) 选择性粘贴:

            1. 批量计算。在某个单元格打一个数字→复制→选定目标区域→选择性粘贴选加减乘除,可一次性改变所有选中区域的结果。

            2. 转置。

            3. 粘贴链接,建立表格之间的联动性。

            4. 转换格式,转换成数值。

      d) 查找替换

            1. 批量修改或去除包含指定内容的单元格。

            2. 批量选中已填充颜色或有统一特殊格式的的单元格,配合ctrl+enter。


P.S.附件的excel中有一些例子可供大家看,另外不知道为何上传上来的大图都变小了,所以附件中有打包的示意图。

结语:以上就是小A想分享的excel基础内容,第一次写这样的文章,似乎写得有些繁杂,以后会边学习边改进的!也希望能得到大家的意见和建议。


如社区发表内容存在侵权行为,您可以点击这里查看侵权投诉指引

0个评论