如何使用以0开头的数字countif ?

亚瑟Magalhaes · 01/14/21

大家早上好。我在计算公式方面有困难。在我工作的公司的人力资源领域，我们使用表格来注册巴西各地提供的职位空缺，并在此表格中要求提供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 comcomto de 11 dígitos, podendo INICIAR com qualquer dígito，包括0。

Gostaria de fazer uma análise simpleses:相同的候选对象名称与问题名称相同。

对COUNTIF的临时用途(para a quantidade daquele valor específico)，今天是一列。por， só estest funccionando para os CPF que com com dígitos de 1 a 9。o CPF que comalam com 0 n / o est / o发送身份证件。

Gostaria de uma adjuda，请帮忙。Obrigado。

保罗新来的 · 01/14/21

所查看的所有值都需要具有相同的数据类型。以1 - 9开头的数字被存储为数值，但以0开头的数字被存储为文本值(因此前导零不会丢失)。

为了适应这种情况，创建另一个文本/数字列(可以在设置后隐藏以保持工作表看起来干净)并使用

=[ID号列名]@row + ""

这将把所有内容转换为文本值。现在，您可以在这个助手列上运行COUNTIFS，并且应该得到正确的结果。

亚瑟Magalhaes · 02/04/21

All values being looked at need to be of the same data type. Numbers that start with 1 - 9 are being stored as numerical values, but those that start with 0 are being stored as text values (so the leading zero won't drop off).<\/p>

To accommodate this, create another text\/number column (that can be hidden after setting up to keep the sheet looking clean) and use<\/p>

=[ID Number Column Name]@row + ""<\/p>

This will convert everything to text values. Now you can run your COUNTIFS off of this helper column, and you should be getting the correct results.<\/p>","bodyRaw":"[{\"insert\":\"All values being looked at need to be of the same data type. Numbers that start with 1 - 9 are being stored as numerical values, but those that start with 0 are being stored as text values (so the leading zero won't drop off).\\n\\nTo accommodate this, create another text\\\/number column (that can be hidden after setting up to keep the sheet looking clean) and use\\n=[ID Number Column Name]@row + \\\"\\\"\\n\\nThis will convert everything to text values. Now you can run your COUNTIFS off of this helper column, and you should be getting the correct results.\\n\"}]","format":"rich","dateInserted":"2021-01-14T13:53:59+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-06T19:28:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/275225#Comment_275225","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/275225#Comment_275225

Paul你好，很抱歉这么晚才回复你。我无法用建议的解决方案解决这个问题。但是，我添加了一个带有数字1的辅助列，并将其与CPF列一起添加。这样就可以进行必要的计数和识别。非常感谢您的回复和帮助。大大的拥抱。

保罗新来的 · 02/05/21

你能提供更多关于辅助列和加1的细节吗?你到底是怎么做到的?你能举个例子吗?

Sadsoft · 04/28/22

嗨@Arthur Magalhaes！

大家好!

你能帮我一下吗?我的床单也遇到了同样的问题。

这些是我用于计数(helper)和“YES”和“NO”列的公式。

任何想法吗?

先谢谢你

吉纳维芙P。 · 04/28/22

嗨@Sadsoft

我以前见过这个当主列中有前导0时。

试着用@cell =之前的标准:

=COUNTIF([列名]:[列名];@cell =[列名]@row）

欢呼,

吉纳维芙

Sadsoft · 04/28/22

Hi @Sadsoft<\/a> <\/p>

I've seen this before when there are leading 0's in a Primary Column. <\/p>

Try using @cell =<\/strong> before the criteria:<\/p>

=COUNTIF([Column Name]:[Column Name]; @cell = [Column Name]@row<\/strong>)<\/p>

Cheers,<\/p>

Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Sadsoft\",\"userID\":147468}}},{\"insert\":\" \\nI've seen this before when there are leading 0's in a Primary Column. \\nTry using\"},{\"attributes\":{\"bold\":true},\"insert\":\" @cell =\"},{\"insert\":\" before the criteria:\\n=COUNTIF([Column Name]:[Column Name]; \"},{\"attributes\":{\"bold\":true},\"insert\":\"@cell = [Column Name]@row\"},{\"insert\":\")\\nCheers,\\nGenevieve\\n\"}]","format":"rich","dateInserted":"2022-04-28T14:37:59+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-06T21:50:17+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327705#Comment_327705","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327705#Comment_327705

你好,@Genevieve P。！

成功了! !

非常感谢!

欢呼,

Sadsoft

吉纳维芙P。 · 04/28/22

太棒了!谢谢你告诉我

Sadsoft · 05/16/22

嗨,大家好!

嗨@Genevieve P。！

我想了解公式中使用“@cell”的含义是什么。

还想了解如何避免空单元格被计数。

提前谢谢你。

吉纳维芙P。 · 05/17/22

Hi, everyone!<\/p>

Hi @Genevieve P.<\/a> !<\/p>

I would like to understand what is the implication\/meaning of the use of the "@cell" in the formula.<\/p>

Also would like to understand how to avoid empty cells to be counted.<\/p>

Thank you in advance.<\/p>","bodyRaw":"[{\"insert\":\"Hi, everyone!\\nHi \"},{\"insert\":{\"mention\":{\"name\":\"Genevieve P.\",\"userID\":91566}}},{\"insert\":\" !\\nI would like to understand what is the implication\\\/meaning of the use of the \\\"@cell\\\" in the formula.\\nAlso would like to understand how to avoid empty cells to be counted.\\nThank you in advance.\\n\\n\"}]","format":"rich","dateInserted":"2022-05-16T18:31:27+00:00","insertUser":{"userID":147468,"name":"Sadsoft","url":"https:\/\/community.smartsheet.com\/profile\/Sadsoft","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-05-23T07:39:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/329775#Comment_329775","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/329775#Comment_329775

嗨@Sadsoft

你能发布你目前使用的公式吗?

如果您只使用COUNT，它将自动排除空白单元格。如果您使用COUNTIF，它将只计算您告诉它的值(例如，等于“This”的单元格，这将排除空白单元格)。

@cell告诉公式进行检查每一个细胞查看该特定单元格是否=该值。看到的:使用@cell和@row创建高效的公式

干杯!

吉纳维芙

Sadsoft · 05/17/22

吉纳维芙P。 · 05/17/22

嗨@Sadsoft

谢谢你的澄清!

由于您要特别查找空白单元格，因此公式将计算有多少个空白单元格。这是因为您正在搜索[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)）

欢呼,

吉纳维芙

如何使用以0开头的数字countif ?

答案

帮助文章参考资料欧宝体育app官方888

类别