如何使用以0开头的数字countif ?
大家早上好。我在计算公式方面有困难。在我工作的公司的人力资源领域，我们使用表格来注册巴西各地提供的职位空缺，并在此表格中要求提供CPF(个人纳税人注册号-我们用来识别巴西人的号码)。该号码由11位数字组成，可以从任意数字开始，包括0。我想做一个简单的分析:确定有问题的候选人是否已经在我们的候选人基础中。我尝试使用COUNTIF来计算该列中特定值的数量。但是，它只适用于以数字1到9开头的cpf。以0开头的cpf没有被识别。我需要帮助。谢谢。
橄榄
Estou com, uma，困难，uma, fórmula。
Na área de RH da empresa que trabalho utilizos um formulário para cadastro em vagas ofofofofbrazil . e neste formulário solicitamos o número CPF (cadastro de Pessoa Física - número que utilizamos para identiar uma Pessoa no Brasil)。Este número
Gostaria de fazer uma análise simpleses:相同的候选对象名称与问题名称
对COUNTIF的临时用途(para a quantidade daquele valor específico)，今天是一列。por
Gostaria de uma adjuda，请帮忙。Obrigado。
答案
-
保罗新来的 ✭✭✭✭✭✭所查看的所有值都需要具有相同的数据类型。以1 - 9开头的数字被存储为数值，但以0开头的数字被存储为文本值(因此前导零不会丢失)。
为了适应这种情况，创建另一个文本/数字列(可以在设置后隐藏以保持工作表看起来干净)并使用
=[ID号列名]@row + ""
这将把所有内容转换为文本值。现在，您可以在这个助手列上运行COUNTIFS，并且应该得到正确的结果。
thinkspi.com
-
Paul你好，很抱歉这么晚才回复你。我无法用建议的解决方案解决这个问题。但是，我添加了一个带有数字1的辅助列，并将其与CPF列一起添加。这样就可以进行必要的计数和识别。非常感谢您的回复和帮助。大大的拥抱。
-
保罗新来的 ✭✭✭✭✭✭
-
Sadsoft ✭ -
吉纳维芙P。 员工管理 -
Sadsoft ✭
-
吉纳维芙P。 员工管理
太棒了!谢谢你告诉我
-
Sadsoft ✭
-
吉纳维芙P。 员工管理
你能发布你目前使用的公式吗?
如果您只使用COUNT，它将自动排除空白单元格。如果您使用COUNTIF，它将只计算您告诉它的值(例如，等于“This”的单元格，这将排除空白单元格)。
@cell告诉公式进行检查每一个细胞查看该特定单元格是否=该值。看到的:使用@cell和@row创建高效的公式
干杯!
吉纳维芙
-
Sadsoft ✭
-
吉纳维芙P。 员工管理
谢谢你的澄清!
由于您要特别查找空白单元格，因此公式将计算有多少个空白单元格。这是因为您正在搜索[Referência do documentento]@row，它是空白的。
如果单元格是空的，可以在开头添加IF语句返回Blank:
=我F(Referência do documentento) @row = "";“”;IF(COUNTIF([Referência do documentento]:[Referência do documentento];@cell = [Referência do documentento]@row) > 1;“模拟”;“NAO”)）
=我F(Referência do documentento) @row = "";“”;COUNTIF([Referência do documentento]:[Referência do documentento];@cell = [Referência do documentento]@row)）
欢呼,
吉纳维芙
帮助文章参考资料欧宝体育app官方888
类别
I got it! It's long but it works:<\/p>
<\/p>
=IF(AND(CONTAINS(\"N\/A\", CHILDREN(STATUS@row)), CONTAINS(\"Not Started\", CHILDREN(STATUS@row)) = false, CONTAINS(\"In Progress\", CHILDREN(STATUS@row)) = false, CONTAINS(\"Complete\", CHILDREN(STATUS@row)) = false), \"N\/A\", IF([% Complete]@row = 1, \"Complete\", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), \"In Progress\", IF([% Complete]@row = 0, \"Not Started\"))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106136,"type":"question","name":"I am trying to manipulate my SUMIFs Formula","excerpt":"When Using SUMIFS(Range, Criterion Range 1,Criterion...) I have multiple columns for example Estimate Spend, 25% spend, 50% spend, Rather than having multiple %'s of spend I just want to be able to use 1 reference and multiply it by 0.25 or 0.5 in the first \"Range\" category so =SUMIFS({Spend} * 0.25, {Phase}, \"phase 1\",…","categoryID":322,"dateInserted":"2023-06-07T14:39:19+00:00","dateUpdated":null,"dateLastComment":"2023-06-07T14:56:44+00:00","insertUserID":155241,"insertUser":{"userID":155241,"name":"Mathew Palter","url":"https:\/\/community.smartsheet.com\/profile\/Mathew%20Palter","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxYml_p2w4ZGEQsYcizl1E0LV2zWrFZLuG5E2D3h=s96-c","dateLastActive":"2023-06-07T16:05:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":155241,"lastUser":{"userID":155241,"name":"Mathew Palter","url":"https:\/\/community.smartsheet.com\/profile\/Mathew%20Palter","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxYml_p2w4ZGEQsYcizl1E0LV2zWrFZLuG5E2D3h=s96-c","dateLastActive":"2023-06-07T16:05:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3372299763,"url":"https:\/\/community.smartsheet.com\/discussion\/106136\/i-am-trying-to-manipulate-my-sumifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106136\/i-am-trying-to-manipulate-my-sumifs-formula","format":"Rich","tagIDs":[219,254,319,334,445],"lastPost":{"discussionID":106136,"commentID":379290,"name":"Re: I am trying to manipulate my SUMIFs Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379290#Comment_379290","dateInserted":"2023-06-07T14:56:44+00:00","insertUserID":155241,"insertUser":{"userID":155241,"name":"Mathew Palter","url":"https:\/\/community.smartsheet.com\/profile\/Mathew%20Palter","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxYml_p2w4ZGEQsYcizl1E0LV2zWrFZLuG5E2D3h=s96-c","dateLastActive":"2023-06-07T16:05:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-07T14:56:07+00:00","dateAnswered":"2023-06-07T14:43:39+00:00","acceptedAnswers":[{"commentID":379282,"body":"
