# WJX计算公式 **Repository Path**: gennyturtle/wjx-calculation-formula ## Basic Information - **Project Name**: WJX计算公式 - **Description**: 问卷星计算公式 - Excel函数功能：探索、技巧与实践 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2023-12-26 - **Last Updated**: 2025-12-18 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # WJX计算公式 ### 介绍 问卷星计算公式 - Excel函数功能：探索、技巧与实践 问卷星中的Excel函数功能来源于Excel，但为了在问卷中使用对函数公式的写法做了一定的修改。 对比如下： | Excel | SUM(A1,A2,B1) | COUNTIF(A1:B1,C1) | |-------|---------------------|-------------------------------| | 问卷星 | SUM([q1],[q2],[q3]) | COUNTIF([['北京'],['天津']],[q1]) | 可以看到Excel中的单元格，在问卷星中变为了具体的某个题目序号；如果是某个单元格数组，则需要修改为特定的列表形式。 #### 题目引用 在Excel中我们采用A1，B2这些单元格标号来引用某个单元格的内容。 而在问卷星的计算公式中，可以使用 [q1] 、[q2] 的方式，引用第一题、第二题作答的内容。 | Excel | SUM(A1,A2,B1) | |-------|---------------------| | 问卷星 | SUM([q1],[q2],[q3]) | 对比以上表格，Excel函数中的参数被转换为问卷中的具体某个题目，[q1]表示第一题，[q2]表示第二题，依此类推。 备注： 如果题目是 **矩阵题** ，[q1_1] 代表第1题第1行这个小题，[q1_2] 代表第1题第2行这个小题。 如果题目是 **表格题** ，[q1_1_1] 代表「第1题第1行第1列」这一空，[q1_1_2] 代表「第1题第1行第2列」这一空，[q1_2_1] 代表「第1题第2行第1列」这一空。 如果使用了 **评分题** ，只想获取这个作答题目的分值，而不是选择的选项内容，可以使用[q1_value]、[q2_value]的输入方式，分别代表第一题的分值和第二题的分值。 如果题目是 **排序题** ，[q1] 代表这个题目所选中的全部选项。若使用 [q1_1_sort] ，表示该题选中的第一个选项，即排序为 1 的选项，依次类推 [q1_2_sort]... 如果要获取 **选择题或矩阵题**中**允许填空**的内容 ，则使用选择题使用 [q1_1_text]，表示该选择题第一个选项的允许填空的内容，依次类推 [q1_2_text]... ;矩阵题使用 [q1_1_1_text]，表示该矩阵题第一行第一列的允许填空的内容，依次类推[q1_2_1_text]，[q1_1_2_text]... 问卷中各题型题目序号的参数写法参考：[点击链接](https://doc.weixin.qq.com/doc/w3_AAwAkQaRAIQpmNsLTa5T42beLoP5i?scode=AMsAaQfnAA4pcRFOT1AAwAkQaRAIQ) 问卷中的函数与Excel函数的参数个数和位置是一致的。 #### 题目属性 Excel中有单元格格式概念，比如数值、字符串、日期等。 在问卷星计算公式中，获取到的默认为字符串，如果想当做数值来进行运算，可以将参与计算题目的属性验证为「整数」或「小数」。 ![输入图片说明](picture/049fc059-be3e-4fb6-ab42-8fd709b78ecd.jpg) 若需要对数值进行「加、减、乘、除」的运算，通常不需要设置属性验证可直接进行计算。 对于设置了分值的题目，例如评分单选题，矩阵量表题，需要使用[q1_value]、[q1_1_value]获取分值进行计算。 除此之外，进行数值运算时都需要注意数据格式的问题。 提示： 推荐采用函数的方式来进行计算，比如：用SUM([q1],[q2]) 而不是 [q1]+[q2]。 #### 返回值 返回值是当我们完整的输入函数和相应参数后的经过函数计算或判断最后得到的值。 例如： | | 返回值 | |-------|---------------------| | SUM(A1,A2,B1) | 第1,2,3题分数的和 | | AND([q1]>0,[q1]<10) | True / False | #### 数组 在Excel中，我们采用 A1:A10 的写法，可以选中一个单元格区域。 Excel中的单元格区域在问卷星中需要改写成数组，首先最外层必须有“[]”包裹起来，每一个元素也需要使用“[]”进行包裹。 例如： 对表格数值题的第一行的每个值求和：SUM([[q1_1_1],[q1_1_2],[q1_1_2]]) 如果使用vlookup函数涉及到二维数组，则写法为：VLOOKUP([q1],[['张三','客服部'],['李四','销售部'],['王五','技术部']],2,0) 提示：如果是字符串均需使用单引号''包裹起来。 #### 赋值与判断 填空题计算公式的执行逻辑，分为「赋值」和「判断」： 1、赋值，是将计算公式的计算结果，填充到填空题的输入框中。不需要填写者手动填写。 2、判断，判断计算公式的结果是True，还是False。如果是True的话，就认为用户输入的内容符合要求，如果是False的话，就认为用户输入的内容不符合要求。并且可以抛出自定义的错误提示。 ![输入图片说明](picture/640.jpg) 从上面的逻辑可以看出，执行「赋值」逻辑时，当前题目是 _不能参与计算_ 的，而执行「判断」逻辑时，当前题目 _必须参与计算_ 的。 注意： 在输入「判断」逻辑的计算公式时，系统已经在外层增加了if函数，所以直接写判断表达式就可以了。 #### 题目表达式和选项表达式 选择题的计算公式，分为「题目表达式」和「选项表达式」： 1、题目表达式，用户只需要输入一个计算公式，计算公式的结果和哪一个选项内容完全相同，就会自动选中对应那个选项。 2、选项表达式：用户需要针对每个选项都输入一个计算公式，执行时会按选项顺序依次执行，哪一个计算公式结果为True，就选中这个选项，并停止继续向下执行。 3、选项表达式的禁止继续作答：选项表达式还支持选中该选项后，不允许继续作答下一页或提交答卷，并且还可以在指定题目，抛出自定义的错误提示。 ![输入图片说明](picture/0.webp) ![输入图片说明](picture/1.webp) #### 隐藏题目和是否允许修改 1、执行「赋值」逻辑时，针对计算公式自动作答的题目，可以选择是否允许填写者进行修改。默认为不允许修改。 ![输入图片说明](picture/Snipaste_2023-12-27_10-48-00.png) 2、为了实现复杂的逻辑，我们可能会增加一些题目作为计算过程。这些题目的数据，由计算公式计算赋值 ，不需要用户手动输入，所以可以将这些类型的题目，设置为隐藏状态。 ![输入图片说明](picture/52632098-6cf3-41a6-9c1f-2e5c76a4bc0e.jpg) ### 函数 #### 日期，时间相关 ##### YEARFRAC 函数 可计算两个日期（start_date 和 end_date）之间的天数（取整天数）占一年的比例。 YEARFRAC(start_date, end_date, [basis]) YEARFRAC 函数语法具有下列参数： Start_date 必需。 一个代表开始日期的日期。 End_date 必需。 一个代表终止日期的日期。 Basis 可选。 要使用的日计数基准类型。 | Basis | 日计数基准 | |---|---| | 0 或省略 | US (NASD) 30/360 | | 1 | 实际/实际 | | 2 | 实际/360 | | 3 | 实际/365 | | 4 | 欧洲 30/360 | ##### DAYS函数 用于计算两个日期之间相隔的天数。 DAYS([结束日期],[开始日期]) ##### DATEVALUE函数 用于将文本日期转换为日期值 DATEVALUE([q1]) ##### HOUR函数 返回小时数值 HOUR([q1]) ##### MINUTE函数 返回分钟数值 MINUTE([q1]) ##### SECOND函数 返回秒数值 SECOND([q1]) #### 计算，取值相关 ##### ROUND函数 用于四舍五入数字的函数 ROUND([q1],2) 2表示四舍五入到两位小数 ##### ROUNDUP函数 用于将数字向上舍入 ##### ROUNDDOWN函数 将数字向下舍入的函数 ##### INT函数 对数值向下取整 INT([q1]) ##### MOD函数 返回两数相除的余数 MOD([q1],36) #### 文本字符串合并 ##### CONCATENATE函数 将多个文本字符串合并为一个 CONCATENATE([q1],'小时',[q2],'分钟') #### 条件判断 ##### IF函数 IF函数是用于根据条件或逻辑测试返回结果的函数。 IF函数的基本格式是: IF(条件,满足条件的结果,不满足条件的结果) 让我们用一个具体的例子来说明。假设问卷的第一题是填空题，输入了个数字，您想知道这个数字是否大于10。您可以在第二题填空题中使用IF函数： IF([q1]>10, "大于10", "小于或等于10") 在这个例子中，如果[q1]的值大于10，那么整个公式的结果就是“大于10”，即第二个填空题会出现"大于10"的文字；如果[q1]的值不大于10，那么结果就是“小于或等于10”。 ##### 比较运算符 比较运算符是指可以使用下列运算符比较两个值。 | 大于 | 大于等于 | 小于 | 小于等于 | 等于 |不等于 | |-------|---------------------|-------------|--|--|--| | > | >= | < | <= |==|!=| 注意：输入以上符号时打开大写键（Caps）或将输入法切换至英文状态。 ##### 逻辑函数 ###### AND函数: 语法：AND(逻辑1,逻辑2,逻辑3,...) 功能：如果所有逻辑表达式都为TRUE,则返回TRUE,否则返回FALSE。 例如：AND([q1]>0,[q1]<10) 如果[q1]题输入的数值大于0且小于10都为TRUE,则返回TRUE ###### OR函数: 语法：OR(逻辑1,逻辑2,逻辑3,...) 功能：如果其中一个逻辑表达式为TRUE,则返回TRUE,只有全部FALSE时才返回FALSE 例如：OR([q1]=='5',[q1]=='10') 如果[q1]题输入的数值等于5或等于10任意一个成立,则返回TRUE ##### 查找，统计相关函数 ###### FIND函数: 语法：FIND(find_text, within_text, [start_num]) find_text是要查找的文本。 within_text是要在其中查找find_text的文本。 start_num是可选参数，指定了在within_text中开始查找的字符位置，默认为 1 。 功能：FIND函数用于在 within_text 中定位 find_text 这个字符串，并返回 find_text 这个字符串的起始位置的值，该值从 within_text 所填写的内容的第一个字符算起。 例如：FIND('北京', [q1])，如果第一题选择了北京，则返回1，否则返回0 ###### COUNTIF函数: 语法：COUNTIF([['北京'],['上海'],['深圳']],[q1]) 功能：可以根据题目的选中项或输入内容，在给定的数组中进行匹配，匹配成功则返回1，不成功则为0。 例如：COUNTIF([['北京'],['上海'],['深圳']],[q1])，如果第一题选择了北京，上海，深圳中任意一个，则返回 1 ，否则返回一个 0 。 ### 案例 #### 四则运算 不同题目中的数值相加，可使用推荐使用SUM()函数，例如：SUM([q1],[q2]) 。 不同题目中的数值相加，可使用题号1 - 题号2的形式，例如：[q2]-[q1] 。 不同题目中的数值相乘，可使用题号1 * 题号2的形式，例如：[q2]*[q1] , [q1]*0.2。 不同题目中的数值相除，可使用题号1 / 题号2的形式，例如：[q2]/[q1] , [q2]/5 。 如需计算平方，立方可以推荐使用POWER()函数，例如：POWER([q1],2) 。 以上运算均可组合使用，但需要注意运算的先后顺序，必要时推荐使用"()"将需要先运算的公式包裹起来。例如：(SUM([q1],[q2])+100)/100 。 #### 日期比较 如下图所示，DAYS([q2],[q1]),可以通过对天数的大小来判断填写者输入的日期是否符合规范。 ![输入图片说明](picture/%E5%9B%BE%E5%83%8F_2024-01-04_102144906.png) [点击查看问卷示例](https://www.wjx.cn/vm/wRzdSt2.aspx) 当填空题选择了日期和时间属性时，可以使用DATEVALUE函数转换为数值，并使用该数值进行比较。 如下图所示，DATEVALUE([q2])-DATEVALUE([q1]) ,可以通过对日期与时间转换后的数值的大小来判断填写者输入的内容是否符合业务情况。 ![输入图片说明](picture/%E5%9B%BE%E5%83%8F_2024-01-04_103315213.png) ![输入图片说明](picture/%E5%9B%BE%E5%83%8F_2024-01-04_103445517.png) #### 工时计算 ##### 天数 通过上述聊到的DAY函数，分别获取到开始和结束时间的2个日期的天数并相减，会出现的第一种结果等于0，即当天开始，当天结束；第二种结果大于0，即存在跨天的情况；第三种结果是小于0，说明日期填错了，开始时间晚于结束时间。 因此可以使用IF函数来判断，首先第一种情况，结果等于0，即当天。然后是第二种情况，此时要考虑，是否满足24个小时即一天。然后再用开始时间到结束时间之间经过的秒数来计算是否满足24个小时，即86400秒。 即：IF((DAY([q2])-DAY([q1]))==0,0,SUM((DAY([q2])-DAY([q1]))-1,QUOTIENT(SUM(86400-[q3],[q4]),86400))) ##### 小时，分钟，秒数 为了获取以上3个具体的数值，需要先将开始时间转换为秒数，即使用HOUR函数获取小时数并乘以3600。 然后使用MINUTE函数获取分钟数，再乘以60。 最后使用SECOND函数获取秒数。将以上3个数值相加(SUM函数)得到总计的秒数。 对于结束时间也是按以上步骤操作。 ![输入图片说明](picture/%E5%9B%BE%E5%83%8F_2024-01-15_095516866.png) 使用第四题的秒数 减去 第三题的秒数，得到差值。此时对差值进行换算成具体的小时，分钟数。 小时数：IF(([q4]-[q3])<0,QUOTIENT(SUM(86400-[q3],[q4]),3600),QUOTIENT([q4]-[q3],3600)) 分钟数：IF(([q4]-[q3])<0,QUOTIENT(MOD(SUM(86400-[q3],[q4]),3600),60),QUOTIENT(MOD([q4]-[q3],3600),60)) 秒钟数：IF(([q4]-[q3])<0,MOD(MOD(SUM(86400-[q3],[q4]),3600),60),MOD(MOD([q4]-[q3],3600),60)) 最后再使用CONCATENATE函数把以上数值都合并起来，并加入小时，分钟，秒等单位。 CONCATENATE([q5],' 天 ',[q6],' 小时 ',[q7],' 分钟 ',[q8],' 秒 ') ![输入图片说明](picture/PixPin_2024-09-10_15-51-22.png) ![输入图片说明](picture/PixPin_2024-09-10_15-51-59.png) 提示：[点击查看示例](https://www.wjx.cn/vm/YfNO8Wh.aspx) “将开始/结束时间转换为秒数”这2个题目是可以隐藏的，在示例中为方便展示故未做隐藏。 #### 年龄分层 根据年龄填空题[q1]输入的数字，自动圈选年龄段单选题[q2]的选项。其中涉及判断年龄大小需在年龄段单选题中设置判断公式。 [点击查看问卷示例](https://www.wjx.cn/vm/eJWnZd1.aspx) 例如： | 年龄 |公式| | --- |--- | | 18岁以下 |[q1]<19| | 18-25岁 |AND([q1]>18,[q1]<26)| | 26-35岁 |AND([q1]>25,[q1]<36)| | 35岁以上 |[q1]>35| #### 人数控制 公司、单位做人员环评时，往往都会有意识的控制获得优秀或得分高的人数，您可以参考以下多个示例进行设置。 ##### 多人不得同时处于同一档位 评价者在填写问卷时往往为了省时，省力会给所有的被评者同一分数或非常接近的分数，导致最后的统计结果丧失了公平性、合理性。以下示例通过对分值分档位，来判断所有的被评者是否处于同一档位并给出提示。 函数示例：IF(SUM(COUNTIF([['9'],['10']],[q1_1]),COUNTIF([['9'],['10']],[q1_2]),COUNTIF([['9'],['10']],[q1_3]),COUNTIF([['9'],['10']],[q1_4]),COUNTIF([['9'],['10']],[q1_5]),COUNTIF([['9'],['10']],[q1_6]),COUNTIF([['9'],['10']],[q1_7]),COUNTIF([['9'],['10']],[q1_8]),COUNTIF([['9'],['10']],[q1_9]))>8,1,0) 函数注释： 首先使用COUNTIF函数统计第一个行标题是否选中了9或10，若选中其中一个，则返回1，未选中则返回0。对另外8个行标题设置同样的函数。 然后使用SUM函数对以上九个COUNTIF函数的返回值求和。 最后使用IF函数对SUM返回的和与人数做比较，即>8人或等于9人，就说明所有被评人均处于同一档位。 提示： 其他几个档位也是按照此思路进行设置，但要注意替换分值即['9'],['10']。例如：1-4档，应修改为['1'],['2'],['3'],['4']。 如有要修改控制某个档位的人数，则修改 SUM()> 后的数值。 [点击查看问卷示例](https://www.wjx.cn/vm/tFUwh9w.aspx) [点击查看问卷示例](https://v.wjx.cn/vm/wEjp4hM.aspx) [点击查看问卷示例](https://v.wjx.cn/vm/w3Mjpsx.aspx) #### 日期，年龄相关计算 主要使用 YEARFRAC 函数计算2个日期间的年龄 函数示例：ROUNDDOWN(YEARFRAC([q7],'12/31/2024',1),0) 函数注释： 首先使用 YEARFRAC 函数计算 [q7] 题选择的日期与截止日期 '12/31/2024'之间的天数并换成对应的年数，再使用 ROUNDDOWN 函数对换算过程中产生的小数结果取整。 [q7] 题支持 年 - 月 -日 格式，也支持 年 - 月 格式 截止日期 '12/31/2024' ，是可以自由设置任意日期 [点击查看问卷示例](https://www.wjx.cn/vm/PDYHayO.aspx) [点击查看问卷示例](https://www.wjx.cn/vm/O0gmQmy.aspx) [点击查看问卷示例](https://www.wjx.cn/vm/Qsq1d2Z.aspx) #### 城市地区划分 [点击查看问卷示例](https://www.wjx.cn/vm/tOHTSaJ.aspx) #### 用公式计算维度分值比较 [点击查看问卷示例](https://www.wjx.cn/vm/tGCx24L.aspx) #### 提取手机号生成随机编码 [点击查看问卷示例](https://www.wjx.cn/vm/exJf5kB.aspx)