Skip to main content

Conas uimhreacha dearfacha nó diúltacha amháin in Excel a chomhaireamh / shuimiú?

De ghnáth, is furasta dúinn raon sonraí a shuimiú nó a chomhaireamh i mbileog oibre, ach anseo, níl uaim ach uimhreacha dearfacha nó diúltacha a chomhaireamh nó a shuimiú. An bhfuil aon bhealaí éifeachtacha ann chun an fhadhb seo a réiteach?

Ná comhaireamh ach uimhreacha dearfacha nó diúltacha i raon le foirmlí

Ná tabhair ach uimhreacha dearfacha nó diúltacha i raon le foirmlí


Ná comhaireamh ach uimhreacha dearfacha nó diúltacha i raon le foirmlí

Anseo, is féidir le feidhm COUNTIF cabhrú leat go tapa líon na n-uimhreacha dearfacha nó na huimhreacha diúltacha i raon a chomhaireamh.

1. Chun na luachanna dearfacha amháin a chomhaireamh, i gcill bhán, iontráil an fhoirmle seo le do thoil = COUNTIF ($ A $ 1: $ D $ 7, "> 0") isteach ann, féach an pictiúr:

doc-count-dearfach1

2. Ansin brúigh Iontráil eochair, agus rinneadh na huimhreacha dearfacha go léir a chomhaireamh agus taispeántar an toradh sa chill. Féach an pictiúr:

doc-count-dearfach1

Nótaí:

1. Más mian leat líon na luachanna diúltacha amháin a chomhaireamh, cuir an fhoirmle seo i bhfeidhm le do thoil = COUNTIF ($ A $ 1: $ D $ 7, "<0").

2. Agus san fhoirmle thuas, A1: D7 an raon sonraí a theastaíonn uait a úsáid.


Ná tabhair ach uimhreacha dearfacha nó diúltacha i raon le foirmlí

In Excel, ní féidir le feidhm SUMIF cabhrú leat ach na luachanna dearfacha nó na luachanna diúltacha a chur suas. Is féidir leat an fhoirmle seo a chur i bhfeidhm mar seo:

1. Clóscríobh an fhoirmle seo = SUMIF ($ A $ 1: $ D $ 7, "> 0") isteach i gcill bhán, féach an scáileán:

doc-count-dearfach1

2. Ansin brúigh Iontráil eochair, agus níl ach na huimhreacha dearfacha go léir curtha suas.

doc-count-dearfach1

Nótaí:

1. Chun gach uimhir dhiúltach a achoimriú i raon amháin, bain úsáid as an bhfoirmle seo le do thoil = SUMIF ($ A $ 1: $ D $ 7, "<0").

2. San fhoirmle thuas, A1: D7 an raon sonraí a theastaíonn uait a úsáid.


Earraí gaolmhara:

Conas uimhreacha troma a shuimiú / a chomhaireamh i raon cealla in Excel?

Conas líon na gcealla earráide / na gcealla neamh-earráide in Excel a chomhaireamh?

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (15)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
=SUMIF($H$9:$H$39;">0") el carácter es ; no ,
This comment was minimized by the moderator on the site
Hello Sir, i need a solution/Formula to count negative numbers from different cells e.g i have three column of deposit (Current Account, Saving Account and Total Deposit) in 12 months and count in how many many months one branch miss the Current, saving and total budget. but when i use =countifs(C1,E1,F1.....,",0") but it only accept range not selected cell what will be the solution. thanks

e.g
This comment was minimized by the moderator on the site
Hello, i need a solution/Formula to count negative numbers from different cells e.g i have three column of deposit (Current Account, Saving Account and Total Deposit) in 12 months and count in how many many months one branch miss the Current, saving and total budget. but when i use =countifs(C1,E1,F1.....,",0") but it only accept range not selected cell what will be the solution. thanks
This comment was minimized by the moderator on the site
Gracias!! por fortuna escontre está página donde encontré Soluciones. 👍
Rated 5 out of 5
This comment was minimized by the moderator on the site
Bonjour à tous,

je suis novice en excel. Existe il une fonction qui calcule automatiquement la différence entre deux nombres en incluant soit - où + devant le résultat.:
exemple, 1735-1685 = +50. (je l'ai fait avec la fonction somme automatique, mais je n'ai pas le + devant le résultat.
Pouvez vous m'envoyer un exemple de la formule avec ces nombres
Cdlt
This comment was minimized by the moderator on the site
Hello, LYS
Normally, there isn't a function to sove your problem, but, you can apply the SUM function to get the results first, and then use the Format Cells feature to set +0;-0;0 for the numbers, please see the below screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-format-cells.png

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Thank you!!! This worked like a charm for my bank's annoying single "amount" column.
This comment was minimized by the moderator on the site
Thanks a lot!
This comment was minimized by the moderator on the site
How to use the Countif formula to count Positive AND Negative values but exclude ZEROS from the counting?

For example (data below):
1
3
0
-5
0
-1
7

The Total counting that I am expecting is equal to 5.

I thought the formula should be like =COUNTIF(N18:N320,"<>0")
But it is not working in the way that I imagined.
Can anyone help please?

Thanks,
Fernando
This comment was minimized by the moderator on the site
@Ferras,


=(COUNTIF(N18:N320,">0")+COUNTIF(N18:N320,"<0"))

Try it and let me know.
This comment was minimized by the moderator on the site
=COUNTIF(N18:N320,"<>0").....Works for me
This comment was minimized by the moderator on the site
how to change color of the cell automatically, according to profit and loss
This comment was minimized by the moderator on the site
How can I add only the numbers that will give me 0? Thank you
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations