如何使用以0开头的数字countif ?
大家早上好。我对公式有困难。在我工作的公司的人力资源部门，我们使用一个表格来登记巴西各地提供的空缺职位，在这个表格中，我们要求提供CPF(个人纳税人登记号码——我们在巴西用来识别个人的号码)。这个数字由11位数字组成，您可以从任何数字开始，包括0。我想做一个简单的分析:确定有问题的候选人是否已经在我们的候选人群体中。我尝试使用COUNTIF来计算整个列中该特定值的数量。但是，它只适用于以数字1到9开头的cpf。以0开头的cpf没有被识别。我需要一些帮助。谢谢。
Olá pessoal, bom dia。
Estou com uma困难em uma fórmula。
Na área de RH da empresa que trabalho不胜枚举formulário para cadastro em vagas ofertadas por todo o Brasil 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 é composto de 11 dígitos, podendo INICIAR com qualquer dígito，含0。
Gostaria de fazer uma análise simple: identiar se o candidato em questão já consta em nossa base de candidatos。
我的名字，我的名字，我的名字，我的名字específico我的名字。Porém, só está funcionando para os CPF que começam com dígitos de 1 a 9。Os CPF que começam com 0 não estão sendo identificados。
Gostaria de uma ajuda，谢谢。Obrigado。
标签:
0
答案
要查看的所有值都需要具有相同的数据类型。以1 - 9开头的数字被存储为数值，但以0开头的数字被存储为文本值(因此前导的0不会消失)。
为了适应这一点，创建另一个文本/数字列(可以在设置后隐藏，以保持表格看起来干净)并使用
=[ID号列名]@row + ""
这将把所有内容转换为文本值。现在您可以从这个帮助器列运行COUNTIFS，您应该会得到正确的结果。
thinkspi.com
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":"2022-05-03T19:26:05+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
你能提供更多关于辅助列和添加1的细节吗?你到底是怎么做到的?你能举个例子吗?
thinkspi.com
嗨@Arthur Magalhaes！
大家好!
你能帮我这个忙吗?我的床单也面临同样的问题。
这些是我用于计数(辅助)和“YES”和“NO”列的公式。
任何想法吗?
先谢谢你
嗨@Sadsoft
当主列的前导是0时，我见过这种情况。
试着用@cell =标准前:
=COUNTIF([列名]:[列名];@cell =[列名]@行）
欢呼,
吉纳维芙
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":"","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/NDZ9JFCTQ8E3\/n8CCYP0Y12F8V.png","dateLastActive":"2022-05-04T17:07:05+00:00","banned":0,"punished":0,"private":false,"label":"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
太棒了!谢谢你告诉我