【数值入门】数值工具之excel篇
数值工具之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列为奖励配置时候，B列1行的公式写为：=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:5“，b2单元格输入，=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区域，工具栏→公式→名称管理器→根据所选内容创建，批量的创建了分别名为”攻击力”和”防御”的一个引用。
选中B4，ctrl+F3，新建名称“战斗力”，输入”=EVALUATE(B3)”，就得到指定的公式的计算结果。
上文为了更简明的介绍这个函数，提供的是比较简单的例子，实际应用时，根据需要可以胜任一些特定任务。比如，运营需要几个宝箱了，这个宝箱价值50~100个QB，我们有若干的道具，价值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显示为10；10.1显示为10.1。
c) “#”：数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于”#”的数量，则按”#”的位数四舍五入。
例：代码：”###.##”,12.1显示为12.10;12.1263显示为：12.13
d) ”0”：数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用0补足。
例：代码：”00000”。1234567显示为1234567；123显示为00123
代码：”00.000”。100.14显示为100.140；1.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]：是调用调色板中颜色，N是0~56之间的整数。
[颜色]：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色，绿色、白色、兰色、青色和洋红
植物大战僵尸(Plants vs. Zombies)