Skip to main content

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

Nuair a bhíonn raon sonraí agat ina bhfuil roinnt uimhreacha troma i mbileog oibre, agus anois nach dteastaíonn uait ach na cealla troma a shuimiú nó a chomhaireamh, ar ndóigh is féidir leat iad a chur suas ceann ar cheann de láimh, ach glacfaidh sé go leor ama. Conas a d’fhéadfá ach na cealla troma in Excel a achoimriú nó a chomhaireamh ar bhealach éasca gasta?

Líon uimhreacha troma i raon le Feidhm Shainithe ag Úsáideoir (VBA agus foirmle)
Suimigh uimhreacha troma i raon le Feidhm Sainithe ag an Úsáideoir (VBA agus foirmle)
Cliceáil ar roinnt chun cealla troma a chomhaireamh agus a shuimiú i raon le Kutools for Excel


Líon uimhreacha troma i raon le Feidhm Sainithe ag an Úsáideoir

Seo a leanas Feidhm Sainithe ag an Úsáideoir in ann cabhrú leat líon na gcealla troma a fháil go tapa. Déan mar seo le do thoil:

1. Coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Fuinneog Microsoft Visual Basic for Applications.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Fuinneog an Mhodúil.

Cód VBA: Líon uimhreacha troma i raon cealla

Function CountBold(WorkRng As Range)
'Update 20220516
Dim Rng As Range
Dim xCount As Double
For Each Rng In WorkRng
    If Rng.Font.Bold Then
        xCount = xCount + 1
    End If
Next
CountBold = xCount
End Function

3. Ansin sábháil an cód seo, agus clóscríobh an fhoirmle seo = CountBold (A1: C9) isteach i gcill bhán, féach an scáileán:

doc-count-bold-cealla1

4. Ansin brúigh Iontráil eochair, agus déanfaidh tú na cealla troma sa raon A1: C9 a chomhaireamh.

doc-count-bold-cealla2

Nóta:A1: C9 san fhoirmle léirítear an raon a theastaíonn uait an fheidhm a úsáid chun na cealla troma a chomhaireamh, is féidir leat é a athrú mar do riachtanas.


Suimigh uimhreacha troma i raon le Feidhm Sainithe ag an Úsáideoir

Agus mura dteastaíonn uait ach na huimhreacha troma i raon sonraí a shuimiú, is féidir liom feidhm atá sainithe ag an úsáideoir a chruthú chun tú a réiteach.

1. Coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Fuinneog Microsoft Visual Basic for Applications.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Fuinneog an Mhodúil.

Cód VBA: Suim uimhreacha troma i raon cealla

Function SumBold(WorkRng As Range)
'Update 20220516
Dim Rng As Range
Dim xSum As Double
For Each Rng In WorkRng
    If Rng.Font.Bold Then
        xSum = xSum + Rng.Value
    End If
Next
SumBold = xSum
End Function

3. Ansin sábháil an cód seo, agus clóscríobh an fhoirmle seo = suimithe (A1: C9) isteach i gcill bhán, féach an scáileán:

doc-count-bold-cealla3

4. Agus ansin brúigh Iontráil eochair, tá na huimhreacha troma go léir sa raon A1: C9 curtha suas. Féach an pictiúr:

doc-count-bold-cealla4

Nóta:A1: C9 san fhoirmle léirítear an raon a theastaíonn uait an fheidhm a úsáid chun na cealla troma a shuimiú, is féidir leat é a athrú de réir mar is gá duit.


Cliceáil ar roinnt chun cealla troma a chomhaireamh agus a shuimiú i raon le Kutools for Excel

YIs féidir le ou gach cealla trom a chomhaireamh nó a shuimiú go héasca i raon le roinnt cad a tharlaíonn nuair gan cód casta VBA a láimhseáil agus foirmlí a mheabhrú.

Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.

1. Chun cealla troma a chomhaireamh, roghnaigh cill bán chun an toradh a aimsiú, agus ansin cliceáil Kutools > Feidhmeanna Kutools > Staidrimh & Mata > COUNTBYFONTBOLD.

Agus chun cealla troma a achoimriú, cliceáil Kutools > Feidhmeanna Kutools > Staidrimh & Mata > SUMBYFONTBOLD. Féach an pictiúr:

2. Sa F.Argóintí unction bosca dialóige, sonraigh an raon le cealla troma a chaithfidh tú a chomhaireamh nó a shuimiú sa bhosca Tagartha, agus ansin cliceáil ar an OK cnaipe.

Anois déantar comhaireamh nó achoimre na gcealla troma go léir i raon sonraithe a dhaonra i gcill roghnaithe láithreach. Féach an pictiúr:

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.


Coinnigh tagairt cille foirmle seasmhach le Kutools le haghaidh Excel


Earraí gaolmhara:

Conas gach cealla trom nó téacs in Excel a aithint agus a roghnú?

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 (22)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello Anthony,
I was struggling with the same problem. For me, the solution ended up being very simple. I noticed I had 4 Modules listed. I removed all of them and then started over with the above instructions. Now it works perfectly.
This comment was minimized by the moderator on the site
I use the function and keep getting the #NAME? error. I am using Excel 365 and Windows11.
This comment was minimized by the moderator on the site
Hello, Anthony

Have you copied and pasted the code into the Module of Microsoft Visual Basic for Applications window in your worksheet? Afetr pasting the code, then, you should enter the formula as you need.

Or you can upload your file here, we can check where the problem is.

Thank you!
This comment was minimized by the moderator on the site
Ma se metto in grassetto nuove celle, la somma non si aggiorna !!!
This comment was minimized by the moderator on the site
Hello Carlo,

Gald to help. After you bold new cells, the sum won't update automatically. But when you double-click the formula cell, then press Enter, the sum will update. Please have a try.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hi Mandy,
thanks for your reply,
Yes I know that after double click the sum will uograde.
Is there any way to upgrade automatically the sum ?

Regards
Carlo
This comment was minimized by the moderator on the site
Hello Carlo,

Sorry that I don't have the solution for automatically upgrading the sum. The easiest way I can think of is to press Ctrl + Alt + F9 to recalculate all worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
А если в ячейках числа и текст? надо просуммировать только ячейки с числами жирным шрифтом.
This comment was minimized by the moderator on the site
RE: CountBold formula This formula does not appear to be dynamic. While it works at first pass, if i change the bolded cells the counters do not update unless i re-enter the formula again. Any tips?
This comment was minimized by the moderator on the site
Yea this is the same for me, otherwise it works great. But if this could be solved so it refreshes the count after each cell deselect like other basic counts it would be perfect!
This comment was minimized by the moderator on the site
Dear guys,
The formula won't update if you just change the format of the number cells.
It updates only when you change the cell content.
This comment was minimized by the moderator on the site
I'm using excel for mac 2011. How can I run the code on my system? thanks!
This comment was minimized by the moderator on the site
I copied and pasted code into excel but get #NAME? error. I made sure to enable macros and using excel 2010 version (don't know if that changes anything). Can anyone please help?!
This comment was minimized by the moderator on the site
[quote]I copied and pasted code into excel but get #NAME? error. I made sure to enable macros and using excel 2010 version (don't know if that changes anything). Can anyone please help?!By Maey[/quote] You have to follow the instruction. It says: "Click Insert > Module, and paste the following code in the Module Window", and you have to do exactly so. Make sure you paste the code in the Module Window, not in the Sheet code Window.
This comment was minimized by the moderator on the site
Thank you.. it's working...
This comment was minimized by the moderator on the site
This works a treat. I only have one issue...it doesn't seem to be adding anything after a decimal point. Any help would be greatly appreciated.
This comment was minimized by the moderator on the site
This works perfectly except it doesn't seem to add my decimal places, only adds the whole dollar. If you have a fix for this I would be very grateful.
This comment was minimized by the moderator on the site
Hi Katie, Use the below code, as the sum shows decimal places. Function SumBold(rng As Range) Dim rCell As Range Application.Volatile SumBold = 0 For Each rCell In rng If rCell.Font.Bold Then _ SumBold = SumBold + rCell Next End Function
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