Calculated Field 计算入门★★★
考点 2.3.5 Create a calcualted field (e.g. string, date,simple artihmetic)
ABS 绝对值函数
绝对值函数可以说是任何数据处理最基本的函数, 不管是负数还是正数进去, 都是正数出来.
ABS(-1) = 1
CEILING & FLOOR 函数
ROUND( )函数
冷门函数:
SIGN( ) 函数
ZN( )函数
CEILING & FLOOR 函数
Ceiling和Floor在英语中是天花板和地板的意思, 这两个函数的名字非常有代入感.
CEILING是如果你输入一个小数, 那么Ceiling返回的比这个小数大的最小整数, 换句话说, 我们常常讲四舍五入, 而Ceiling函数就是不管多少位小数我都要进一位. 也就是Round Up.
比如CEILING(4.045) = 5
FLOOR则恰恰相反, FLOOR是不管什么数, 返回的是小于这个小数的最大整数, 也就是CEILING函数返回的整数-1.
对于任何一个以4开头的小数来说, 比如4.045, 那它的天花板就是5, 而它的地板就是4. 这些小数就在这两个天花板之间.
我什么时候会用到CEILING或者是FLOOR函数呢?
CEILING和FLOOR函数适用于所有那种小数和整数之间错配的问题. 如果我定义两个部分, 一部分叫做Task, 任务, 而另一部分叫做Execution执行者.
往往任务都是小数, 而执行都是正数. 什么意思, 工作量, 运载量, 重量这些都是我们需要完成的任务. 而执行的话一般都是员工的数量, 运输工具的数量等等.
任务往往都是小数, 而执行往往都是整数, 执行的部分是不能拆分的.
使用CEILING函数计算Headcount也就是用人
假设我们现在计算出来每周有100个小时的工作量, 然后我现在要计算把这些工作分配给员工需要多少人.
假设每个员工的合同是每周工作40个小时, 那么为了完成这些工作, 100/40 = 2.5
但是你不能跟老板说, 我们需要2.5个员工, 因为没有半个员工一说, 你必须把 2.5 Round Up to 3 也就是把2.5向上取整到3, 至少需要3个员工来完整这100个小时的工作.
使用CEILING函数计算销售额
这是另一个典型的案例, 虽然定价是14.99, 但是一般都是收取15快钱, 那么你就会发现把14.99取整到15 往往更容易
使用Floor函数计算满减
假设我们进行促销活动, 消费者每满10元,可以减一元. 假设消费者消费了25元, 那么就可以获得两块钱的折扣.
FLOOR(25/10) = Floor(2.5) = 2
这样我们可以得到具体的由于这个满减活动, 我们的消费者获得实际折扣金额
ROUND( )函数
⚠️警告: 在解释Round函数之前, 你有一个很重要的问题要搞清楚. 你为什么需要四舍五入? 换句话说, 你是为了显示需要,还是为了计算需要?
通常来讲, 如果我们需要显示金额, 或者显示很大的数字, 我们需要把这个数字四舍五入到更高的整数位. 比如$100.02 我们只想显示$100.
如果是为了显示需要, 那么你不需要先把$100.02, 通过Round函数计算成100 然后再显示成100, 你可以通过调整$100.02的格式, 直接把100.02 显示成100
如果只是为了显示需要而把数据进行四舍五入是不需要使用Round( )函数的, 我再说一遍, 如果你只是为了显示需要而使用Round( )函数是完全错误的, 因为Round会影响计算的结果.
Round( )是如何更改计算结果的?
Round函数只有在你真正需要在底层计算中修改原始数据的时候才需要, 单纯是为了显示, 你需要更改的是计算结果的数字显示格式而非是给计算添加Round. 这是一个初学者非常容易踏入的误区. 下面我们就来解释一下Round是更改我们计算的结果的.
假设我们现在有10笔交易, 每笔交易的价值10.49, 加起来是104.9, 我们想要四舍五入成105.
如果我们把每一笔交易都四舍五入, ROUND(10.49) = 10, 而10笔10块钱的交易加起来是100块钱. 105块和100块之间差距甚远. 在绝大多数情况下, 你都是不需要使用Round函数来修改的, 这个单纯为了显示效果所做的四舍五入不是通过Round( )函数实现的.
Round( )函数的使用
ROUND(number, [decimals]) . [decimals]是你需要输入的一个参数, 如果你想保留一位小数, 那就输入1, 保留四位小数你就输入4. 如果你在[decimals]这里不输入任何数字, 默认是0代表显示整数.
ROUND(10.1234567, 4) = 10.1234
ROUND(10.1234567) = 10
ROUND(10.5) = 11
[decimals] 这个参数可以输入负数.
ROUND(number, -2) 代表整数的最后两位是0. ROUND(1056,-2) = 1100
因为2代表小数点两位, 是从小数点从左向右数, -2就是以小数点为中心, 向左走, 最靠近小数点的整数位也就是个数位为-1, 然后取整到百位为-2,取整到千为-3.
通过动图来对比通过改变格式来四舍五入和使用ROUND函数来四舍五入的区别
首先我没有创建Round函数, 而是直接把[Sales] 拖拽到了右侧, 然后我右键点击SUM(Sales), 选择Format, 选择Number Format, 通过调整Decimal Places选项, 我们可以显示更多的位数. 但是在底层, 我们并没有对数据进行任何的更改.
紧接着我又重新创建了一个叫做[Rounded Sales]的计算, 然后再次把它加入右侧的工作表当中, 你会发现Rounded Sales的值和右侧的Sales有一点点不一样, 而且, Rounded Sales的格式也是可以修改的, 我们用同样的方式展开Rounded Sales的格式, 你会发现在添加了额外的两位小数之后, Rounded Sales的后两位小数全都是0
冷门数值函数: SIGN函数和ZN函数
SIGN函数还好, ZN函数是Tableau早期最容易被忽视的函数之一. 它实在是太重要了.
SIGN( )函数
Sign因为里是符号的意思, 在Tableau里SIGN函数专门是用来计算一个数值变量是正数, 负数,还是0的. 如果是正数, 那么它的返回值是1, 如果是负数,返回值是-1, 如果是0 返回的是0
SIGN( -100) = -1, SIGN(100) = , SIGN(0) = 0
SIGN( )函数的使用场景: 判断趋势和分配颜色, 用Excel的功能来解释的话叫做Condition Formatting. 根据条件来分配格式.
理解起来非常简单, 如果上升, 不管是用Bar, 还是用文字, 我想用绿色来显示增长, 我用红色来显示下降. 那么颜色就是基于计算结果的另一层计算. 这个计算就是SIGN([Sales Growth]).
如果结果是-1, 那我就给-1分配红色, 如果是1我就分配绿色.
ZN( )函数★★★
先不用说别的, 就ZN这俩字母上来就给你干懵了. 因为Tableau所有其他的函数名字基本上都有对应的单词, 你通过那个单词的含义就能推断出来这个函数大概是干嘛的. 比如Round, 英文就是四舍五入的意思, 所以这个函数就是四舍五入, 但是ZN并不是一个单词, 而是几个单词的缩写
ZN的意思是Zero If Null: 如果是空值, 转换成0
为了方便记忆, 你可以把ZN简化成Zero Null, 0空值函数.
对于计算来讲我们是绝对不能允许空值的, 达咩🫸 Absolutely Not.
而ZN( )是在Tableau当中处理这个问题的唯一手段. 这个函数有它的唯一性
理解空值对计算的影响
我举一个最简单的例子你就明白了. 在Tableau当中, 100 + null = null. 在任何计算中, 一旦Null包含其中, 那返回的结果大概率就是Null. 这就带来一个很严重的问题, 很多时候我们并不会一行一行的去检查你的数据. 当数据更新时, 你是不可能去检查每一行数据到底是不是Null的. 尤其是当你的数据是来自于Excel这种可以手工编辑的文件的时候.
那么如果你不在设计计算的时候, 加上ZN( )函数这一层保护罩, 那么当Null出现在数据当中的时候, 你的图表和报告将会返回一片空白.
其实在数据基础那章, 空值和特殊值那一课, 我已经提到过这个问题. 你用下边的链接可以再复习一下
如何使用ZN( ) 函数
ZN( )函数的使用非常简单, 你就是在你的Calculated Field外层再加上一个ZN([Calculated Field])即可. 比如ZN([Sales])
ZN同样可以加在Aggregated Function的外层 比如ZN(SUM(Sales)))
我最推荐的时候在计算的最内层就添加ZN()函数, 这样你所有嵌套函数的外层就都不要考虑这个问题了.