如何使用以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
类别
Hi @Louis.Smith<\/a> <\/p> Maybe try this one:<\/p> =COUNTIF(Client:Client, \"Delta\")<\/p> I hope that helps.<\/p> Matt<\/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":106091,"type":"question","name":"How to reference column name pulled from formula","excerpt":"I'm trying to pull a date from columns based on the number in the helper column. I know I can do this with nested IFs, but is there a way to change which column I'm pulling the value from? My column Start Date should be whatever the column is based on the Index. So for the first column below, since Index is 3, it should…","categoryID":322,"dateInserted":"2023-06-06T19:01:04+00:00","dateUpdated":null,"dateLastComment":"2023-06-06T19:24:57+00:00","insertUserID":162114,"insertUser":{"userID":162114,"name":"mwat4482","url":"https:\/\/community.smartsheet.com\/profile\/mwat4482","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-06T19:24:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162114,"lastUser":{"userID":162114,"name":"mwat4482","url":"https:\/\/community.smartsheet.com\/profile\/mwat4482","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-06T19:24:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3372158761,"url":"https:\/\/community.smartsheet.com\/discussion\/106091\/how-to-reference-column-name-pulled-from-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106091\/how-to-reference-column-name-pulled-from-formula","format":"Rich","lastPost":{"discussionID":106091,"commentID":379133,"name":"Re: How to reference column name pulled from formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379133#Comment_379133","dateInserted":"2023-06-06T19:24:57+00:00","insertUserID":162114,"insertUser":{"userID":162114,"name":"mwat4482","url":"https:\/\/community.smartsheet.com\/profile\/mwat4482","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-06T19:24:48+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/EITIHXA4Y0I7\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-06T19:24:45+00:00","dateAnswered":"2023-06-06T19:18:07+00:00","acceptedAnswers":[{"commentID":379128,"body":" Are you trying to use that as a dynamic cell reference? If so, that is not possible. What it looks to me like you need is something more along the lines of<\/p> =INDEX([1st Date Column]@row:[Last Date Column]@row, 1, Index@row)<\/p> You are going to need to update the column names in the formula to match what you are using in your sheet.<\/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":[]},{"discussionID":106087,"type":"question","name":"Conditional Formatting Based on 2 Date Columns, with or without Helper Column","excerpt":"Hi! I've scoured the Community posts, and can't find exactly what I need. I am managing a membership list and need to flag when memberships expire before or on a specific date (06\/29\/2023) so I can contact those members. I have one column with their membership expiration dates, a second column with the 06\/29\/2023 deadline,…","categoryID":322,"dateInserted":"2023-06-06T18:29:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-06T19:02:02+00:00","insertUserID":162111,"insertUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162111,"lastUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":58,"score":null,"hot":3372157304,"url":"https:\/\/community.smartsheet.com\/discussion\/106087\/conditional-formatting-based-on-2-date-columns-with-or-without-helper-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106087\/conditional-formatting-based-on-2-date-columns-with-or-without-helper-column","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":106087,"commentID":379120,"name":"Re: Conditional Formatting Based on 2 Date Columns, with or without Helper Column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379120#Comment_379120","dateInserted":"2023-06-06T19:02:02+00:00","insertUserID":162111,"insertUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JWT4M72YQK8N\/screenshot-2023-06-06-142808.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-06 142808.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-06T19:00:34+00:00","dateAnswered":"2023-06-06T18:52:14+00:00","acceptedAnswers":[{"commentID":379113,"body":"
<\/p>