【数值入门】数值工具之excel篇

发表于2015-07-23
评论8 3k浏览

想免费获取内部独家PPT资料库?观看行业大牛直播?点击加入腾讯游戏学院游戏策划行业精英群

361498939


   数值工具之excel(1)

    本文原创首发Gad-腾讯游戏开发者平台(http://gad.qq.com),如需转载,请取得授权并标明出处。

最近看到圈子内有很多的精彩文章,但很少人提到工具的使用,可能各位数值同学都把这个当成种族天赋,默认应当掌握,才木有提及吧,但是考虑到也可能会有新同学加入,所以写了此文,希望能有帮助。

              如果数值策划只允许掌握一个工具软件,那么它一定是excel,因为它是图灵完备的,且使用简单,和其他各种工具也都可以很好的组合使用。

1.1.      函数和公式

              函数与公式,excel有十一类,400个左右的函数。这么多的函数,我们每个都掌握的话,耗费实在太大,需要时现即可。

1.1.1.                      如何查看函数说明?

l         在功能区选择公式插入函数,选择函数的类别,就可以在下方查看相应的函数及其说明。点击窗口下侧的有关改函数的帮助超链接可以打开相应函数文档,会有函数说明,语法,备注和函数示例。

l         任意单元格输入等号,并输入希望查看的函数的首写字母,在智能填充的下拉列表,选中函数,查看函数说明。(双击该函数可完成函数的输入,点击函数名的超链接,也可打开相应的函数文档)

1.1.2.                      如何理解公式?

实际应用中,单独使用函数情况很少更多的是函数的组合使用。

这里我们对公式函数先下个定义:

公式,一个等号开始,到结束的一个多个函数的表达式。

函数接收一个或者多个的输入值(个别函数没有输入值,rand,row,column,通常这类函数是易函数(易函数,是指触发自动重算之后,容易改变结果的函数。当你打开一份表格,什么操作也没做的情况下关闭,仍提示保存时是用了易失函数的原因。返回一个输出表达式

基于以上概念,公式就是接受多个输入值,并将返回值进行一次加工,得到最终返回值的一个用户自定义函数。我们设计一个公式以及理解他人的长串的公式,都基于这一点出发

今天一个同事的需求举例有这样一数据,是奖励项各个等级的奖励内容格式xxx:xxx,同事希望取出冒号右侧的字符串(冒号侧的字符串长短不确定)根据需求出发,我们输入值是一个字符串,返回值亦是。

拆解一下需求实现的步骤:

Step1,计算出需要取出的字符串(冒号右侧部分)长度这样我们可以使用合适的函数取出想要的内容。

Step2实现Step1必须先计算出整个字符串的长度length,以及冒号所处的位置i

Step3,使用right函数截取输入值,截取的长短为length-i

最终A奖励配置时候,B1公式写为:=RIGHT(A1,LEN(A1)-FIND(":",A1))

注:

              上述例子,还有多种方法实现

ü         文本的处理,若熟悉正则表达式,可优先考虑使用正则本文这条可略过

ü         本着最简洁的办法最美丽的原则,示例中的问题简单方法是使用快速填充(开始填充快速填充,excel2013新增功能)

ü         使用mid函数替代right但相比之下,right函数针对性更强,函数目的更明确整个公式更简短

ü         可使用substitute冒号及其左侧的字符串替换为空

解读一段公式,就是通过观察公式,逆推断设计者思路的过程有时我们知道作者写公式的目的,比如文本处理的,比对一下引用参数和返回值,可以很容易观察到差异多时候,公式的目的需要揣测的,比如引用参数较多,或参数和返回值都数值,或公式掺加了逻辑判断

以上文的公式=RIGHT(A1,LEN(A1)-FIND(":",A1))作为例子说明,选中公式所在的单元格B2,鼠标点击上编辑栏,鼠标会在点击处变为光标,编辑栏下方附近处会出现光标所在位置调用的函数的说明并且编辑栏中公式里的各级括号会以颜色区别开,最外层的函数的括号黑色。

光标移动right函数处,在公式说明中我们看到RIGHT(text,[num_chars])提示信息,这表明right函数最多2参数,方括号表示该参数是个可选参数查看=RIGHT(A1,LEN(A1)-FIND(":",A1))显然a1就是text后面这部分逗号右侧到黑括号结束的LEN(A1)-FIND(":",A1)就是num_chars值了。阅读函数说明,我们得知这是指定返回字母的数量参数拖动鼠标选中LEN(A1)-FIND(":",A1),按下键盘F9键,选中部分变为1这样我们就初步了解了公式的设计目的——取传入的字符串右侧指定字符串作为返回值。

我们继续使用F9可以进一步了解到LEN(A1)FIND(":",A1)分别如何运作的进而完整的解读了公式。

查看帮助文件,经常使用F9已经可以阅读和理解绝大部分的公式了。

工具栏的公式公式求值,也可以达到相同的效果。

1.1.3.        相对引用和绝对引用

相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时,当将公式向旁边复制时,地址是如何变化的。

具体情况举例说明:

1、相对引用,复制公式时地址跟着发生变化,如C1单元格有公式:=A1+B1

当将公式复制到C2单元格时变为:=A2+B2

当将公式复制到D1单元格时变为:=B1+C1

2、绝对引用,复制公式时地址不会跟着发生变化,如C1单元格有公式:=$A$1+$B$1

当将公式复制到C2单元格时仍为:=$A$1+$B$1

当将公式复制到D1单元格时仍为:=$A$1+$B$1

3、混合引用,复制公式时地址的部分内容跟着发生变化,如C1单元格有公式:=$A1+B$1

当将公式复制到C2单元格时变为:=$A2+B$1

当将公式复制到D1单元格时变为:=$A1+C$1

规律:加上了绝对地址符$的列标和行号为绝对地址,在公式向旁边复制时不会发生变化,没有加上绝对地址符号的列标和行号为相对地址,在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。

注意:工作薄和工作表都是绝对引用,没有相对引用。

技巧:在输入单元格地址后可以按F4键切换绝对引用混合引用相对引用状态。

1.2.      自定义名称

当一个公式,可以达成目的我们说可用

当一个公式,即可以达到目的,又有较好的扩展性,又足够的健壮时,我们称它好用

一个公式,即好用,又直观,便于维护时那就是易用了我们提倡书写健壮易用的公式

使用自定义名称,能让你的公式看起来通俗易懂

还是上述的例子,我们a1单元格的内容,称之为字符串,LEN(A1)-FIND(":",A1)这部分称之为截取长度,那么上文的公式就可以写成=RIGHT(字符串, 截取长度)

1.2.1.        创建名称

Ctrl+F3打开名称管理器

ü         新建名称原字符串,引用位置,输入=$A1”

ü         新建名称截取长度,引用位置,输入 LEN($A1)-FIND(":",$A1)

1.2.2.        使用名称

a1单元格输入,2046:5b2单元格输入,=RIGHT(字符串, 截取长度)

发现结果与直接b2输入 =RIGHT(A1,LEN(A1)-FIND(":",A1))一致的。

我们继续新建名称myFunc引用位置,输入=RIGHT(原字符串,截取长度)b2输入=myFunc结果仍然正确。

使用名称,可以将一个复杂的公式分解,可以使用自定义的中间函数名,极大的提高了可读性,维护起来也方便。制作动态图表时,自定义名称也可以很好的发挥作用后续内容将会详细提到。

名称中,可以看到,名称中的引用位置,可以是公式,公式中的相对引用和绝对引用仍然生效,注意使用正确的引用方式。

1.2.3.        创建名称的其他方法

可以一个区域指定名称,这时候简化操作是,选中某个待命名的区域,如a1:a3在公式编辑栏左侧的名称框,输入任意非数字开头的字符串作为这个区域的命名。个在利用数据有效性制作下拉框,可以直接域名称作为来源。

工具栏上执行:公式定义名称在弹出的对话框中输入参数,然后点击确定按钮,也可以定义名称。

工具栏执行:公式根据所选内容创建,可批量的设置名称。

1.3.      宏表函数

宏表函数,顾名思义,是介于宏和公式之间的一个函数,可以完成一部分VBA的功能比如获取工作薄中工作的数量,指定单元格色值等等,但这些在有了VBA之后实用价值已经很小了(宏表函数VBA出现前的替代物)

还是有一个宏表函数值得我们记住它,EVALUATE

启用宏表函数,需要excel文件启用宏的工作簿,(07之后的版本需要另存为.xlsm格式,没错,和使用VBA一样)

我们在一个新的sheet输入这些值,黄色填充的单元格是公式结果,不需要输入公式的目的是按照提供的攻击力和防御力,以及我们提供的战斗力公式,计算攻击力和防御属性提供的战斗力总和。这个公式的内容是可变的。

选中A1:B3区域工具栏公式名称管理器根据所选内容创建批量的创建了分别名为攻击力防御一个引用

选中B4ctrl+F3新建名称战斗力输入=EVALUATE(B3),就得到指定的公式的计算结果。

上文为了简明的介绍这个函数,提供的是比较简单的例子,实际应用时,根据需要可以胜任一些特定任务。比如,运营需要几个宝箱了,这个宝箱价值50~100QB,我们有若干道具,价值QB2~10不等,宝箱可以有2~4道具。

了各宝箱内容不至于雷同,可能经常需要调整内容,然后核算宝箱价值。这个时候,就可以道具和价值,批量的创建名称的键对值,做若干个宝箱,4分别的输入宝箱道具

当然,同样的需求很多其他的方法实现,我们无需过多讨论挑选最适合自己的就好

1.4.      单元格格式和条件格式

1.4.1.        格式设置

excel,可以认为一切皆公式,单元格的格式设置也是如此

              选中试图设置格式的单元格,ctrl+1调出设置面板,在分类中我们选择货币,右侧选择负数的第一项。

切换到分类的自定义,此时,类型中的设置¥#,##0.00_);[红色](¥#,##0.00)

这里可以当作一段公式,这样来解读。分号前的内容,表示大于0值应用的格式分号后的内容,表示小0时应用的格式

¥内置的人民币前缀字符。

#:数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于#的数量,则按#的位数四舍五入。

0:数字占位符。如果单元格的内容大于占位符,则显示实际数字,如果小于点位符的数量,则用0补足。

_)表示对齐时空出一位。

1.4.2.        格式语法

                                                                                                                                                                                     

a)         单元格自定义格式:                                                                                                                                                                                     

完整的格式代码由四个部分组成,这四部分顺序定义了格式中的正数、负数、零和文本,格式代码各部分以分号分隔。                                                                                                                                                                                     

它们如果只指定两个部分,则第一部分用于表示正数和零,第二部分用于表示负数。如果只指定一个部分,则该部分可用于所有数字。                                                                                                                                                                                     

如果要跳过某一部分,则使用分号代替该部分即可。                                                                                                                                                                                     

                                                                                                                                                                                     

b)        G/通用格式:以常规的数字显示,相当于分类列表中的常规选项。                                                                                                                                                                                     

例:代码:G/通用格式10显示为1010.1显示为10.1                                                                                                                                                                                     

                                                                                                                                                                                     

c)         #:数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于#的数量,则按#的位数四舍五入。                                                                                                                                                                                     

例:代码:###.##,12.1显示为12.10;12.1263显示为:12.13                                                                                                                                                                                     

                                                                                                                                                                                     

d)        0:数字占位符。如果单元格的内容大于占位符,则显示实际数字,如果小于点位符的数量,则用0补足。                                                                                                                                                                                     

例:代码:000001234567显示为1234567123显示为00123                                                                                                                                                                                     

代码:00.000100.14显示为100.1401.1显示为01.100                                                                                                                                                                                     

                                                                                                                                                                                     

e)         @:文本占位符,如果只使用单个@,作用是引用原始文本,                                                                                                                                                                                     

要在输入数字数据之后自动添加文本,使用自定义格式为:文本内容@;要在输入数字数据之前自动添加文本,使用自定义格式                                                                                                                                                                                     

为:@文本内容@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。如果使用多个@,则可以重复文本。                                                                                                                                                                                     

例:代码;;;"集团"@"",财务显示为:集团财务部                                                                                                                                                                                     

代码@@@,财务显示为:财务财务财务                                                                                                                                                                                     

                                                                                                                                                                                     

f)          *:重复下一次字符,直到充满列宽。                                                                                                                                                                                     

例:代码:@*-ABC显示为ABC-------------------                                                                                                                                                                                     

可就用于仿真密码保护:代码**;**;**;**123显示为:************                                                                                                                                                                                     

                                                                                                                                                                                     

g)         :千位分隔符,将值扩大1000倍。两个分号逗号,,"表示将值扩大1000*1000=1000000倍。                                                                                                                                                                                     

例:代码 #,###12000显示为:12,000                                                                                                                                                                                     

                                                                                                                                                                                     

h)        .:小数点。如果外加双引号则为字符。                                                                                                                                                                                     

                                                                                                                                                                                     

i)           %:百分比。                                                                                                                                                                                      

                                                                                                                                                                                     

j)          下划线_ :若要在数字格式中创建N字符宽的空格,请在字符前加上N条下划线 _                                                                                                                                                                                      

                                                                                                                                                                                     

k)         "":显示引号里面的文本。                                                                                                                                                                                     

                                                                                                                                                                                     

l)           ¥:用这种格式显示下一个字符。                                                                                                                                                                                     

¥:显示下一个字符。和""用途相同都是显示输入的文本,且输入后会自动转变为双引号表达。                                                                                                                                                                                     

例:代码"人民币 "#,##0,,"百万",与¥人民币 #,##0,,¥百万,                                                                                                                                                                                     

输入1234567890显示为:人民币 1,235百万                                                                                                                                                                                     

                                                                                                                                                                                     

m)      :显示下一个字符,""用途相同都是显示输入的文本,由于引号是代码常用的符号。在单元格中是无法用"""来显示出来"                                                                                                                                                                                     

要想显示出来,须在前加入,用法与¥一样。例:代码:#!"10显示10"                                                                                                                                                                                     

代码:#!"!"10显示10""                                                                                                                                                                                     

                                                                                                                                                                                     

n)        :数字占位符。在小数点两边为无意义的零添加空格,以便当按固定宽度时,小数点可对齐,另外还用于对不等到长数字的分数                                                                                                                                                                                     

例:分别设置单元格格式为??.?????.???,对齐结果如下:                                                                                                                                                                                     

输入12.1212 显示12.12 12.121                                                                                                                                                                                     

                                                                                                                                                                                     

o)        []:中括号在自定义格式中有两个用途:使用颜色代码、使用条件。                                                                                                                                                                                     

如自定义格式[红色][<=100];[蓝色][>100]表示以红色字体显示小于和等于 100 的数字,而以蓝色字体显示大于 100 的数字。                                                                                                                                                                                     

                                                                                                                                                                                     

p)        运算符:包括:= 等于、> 大于、< 小于、>= 大于等于、<= 小于等于和 <> 不等于                                                                                                                                                                                     

                                                                                                                                                                                     

q)        [颜色N]:是调用调色板中颜色,N0~56之间的整数。                                                                                                                                                                                      

[颜色]:用指定的颜色显示字符。可有八种颜色可选:红色、黑色、黄色,绿色、白色、兰色、青色和洋红                                                                                                                                                                                     

植物大战僵尸(Plants vs. Zombies)

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

游戏学院公众号二维码
腾讯游戏学院
微信公众号

提供更专业的游戏知识学习平台