Skip i'r prif gynnwys

Sut i gyfrif nifer yr achosion mewn colofn yn nhaflen Google?

Gan dybio, mae gennych chi restr o enwau yng ngholofn A o ddalen Google, ac nawr, rydych chi am gyfrif sawl gwaith mae pob enw unigryw yn ymddangos fel y llun isod a ddangosir. Y tiwtorial hwn, byddaf yn siarad am rai fformiwlâu ar gyfer datrys y swydd hon ar ddalen Google.

Cyfrif nifer yr achosion mewn colofn yn nhaflen Google gyda fformiwla cynorthwyydd

Cyfrif nifer y digwyddiadau mewn colofn yn nhaflen Google gyda fformiwla

Cyfrif nifer yr achosion mewn colofn yn nhaflen Google gyda fformiwla cynorthwyydd

Yn y dull hwn, gallwch echdynnu'r holl enwau unigryw o'r golofn yn gyntaf, ac yna cyfrif y digwyddiad yn seiliedig ar y gwerth unigryw.

1. Rhowch y fformiwla hon: = UNIGRYW (A2: A16) i mewn i gell wag lle rydych chi am echdynnu'r enwau unigryw, ac yna pwyso Rhowch yn allweddol, mae'r holl werthoedd unigryw wedi'u rhestru fel y screenshot canlynol a ddangosir:

Nodyn: Yn y fformiwla uchod, A2: A16 yw'r data colofn rydych chi am ei gyfrif.

2. Ac yna ewch ymlaen i nodi'r fformiwla hon: = COUNTIF (A2: A16, C2) wrth ymyl y gell fformiwla gyntaf, gwasgwch Rhowch allwedd i gael y canlyniad cyntaf, ac yna llusgwch y handlen llenwi i lawr i'r celloedd rydych chi am gyfrif digwyddiad y gwerthoedd unigryw, gweler y screenshot:

Nodyn: Yn y fformiwla uchod, A2: A16 yw'r data colofn rydych chi am gyfrif enwau unigryw ohono, a C2 yw'r gwerth unigryw cyntaf i chi ei dynnu.

Cyfrif nifer y digwyddiadau mewn colofn yn nhaflen Google gyda fformiwla

Gallwch hefyd gymhwyso'r fformiwla ganlynol i gael y canlyniad. Gwnewch fel hyn:

Rhowch y fformiwla hon: = ArrayFormula (QUERY (A1: A16 & {"", ""}, "dewiswch Col1, cyfrif (Col2) lle mae Col1! = '' Grŵp yn ôl cyfrif label Col1 (Col2) 'Cyfrif'", 1)) i mewn i gell wag lle rydych chi am roi'r canlyniad, yna pwyswch Rhowch allwedd, ac mae'r canlyniad wedi'i gyfrifo wedi'i arddangos ar unwaith, gweler y screenshot:

Nodyn: Yn y fformiwla uchod, A1: A16 yw'r ystod ddata sy'n cynnwys pennawd y golofn rydych chi am ei chyfrif.

Nifer y digwyddiadau mewn colofn yn Microsoft Excel:

Kutools for Excel'S Rhesi Cyfuno Uwch gall cyfleustodau eich helpu i gyfrif nifer yr achosion mewn colofn, a gall hefyd eich helpu i gyfuno neu grynhoi gwerthoedd celloedd cyfatebol yn seiliedig ar yr un celloedd mewn colofn arall.

Kutools for Excel: gyda mwy na 300 o ychwanegiadau Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod. Dadlwythwch a threial am ddim Nawr!

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools for Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed o'r blaen. Kutools for Excel Yn cynnig dros 300 o nodweddion uwch i hybu cynhyrchiant ac arbed amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905

Office Tab Yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (14)
No ratings yet. Be the first to rate!
xxx
xxx
This comment was minimized by the moderator on the site
可以限制规定内容出现的频次吗
Reply
0
0
Hedy
Hedy
This comment was minimized by the moderator on the site
Thanks!!! how do I count multiple columns?Ie column A, B,C,D all have names that needs to be counted
Reply
0
0
Michael
Michael
This comment was minimized by the moderator on the site
Thank you for this! Been trying to figure it out for hours!
Reply
0
0
michaelbrown@live.ca
This comment was minimized by the moderator on the site
Hi, I am trying to filter out some data from a sheet that is regularly updated. To simplify keeping track of the data I have a reporting section on the google sheet.The problem is that the data isn't organized in the most ideal way.

There are 2 columns that I want to use specific data from to create a filter, then with the leftover names I want to know how many unique names there are


Column 1 - Filter with A for one report field, B for the other report field (I will manually change this in the formula)

Column 2 - There are 9 Text entries possible. I want to only include 3 of them as a Filter

Column 3 - There are a couple of thousand entries(names), many are the same in multiple rows. With the data filtered using Column 1, and Column 2, I want to get a number for how many unique names are left after the data has been filtered.


I am not used to having this many things I am trying to filter the data from so any help would be hugely appreciated.


Have an excellent day
Reply
Report
0
0
M B
M B
This comment was minimized by the moderator on the site
I have a google sheet with a lot of information but I am looking to filter it down into a report.

Column1 - Options are A, B (The is the first data to sort)
Column 2 - has 3 of the text string that I want my sum to include, but I want to leave out the others 5.
Column 3 - Names that I want to know how many unique names there are

What I am looking to figure out is the following

Option A, the sum to only include 3 text string that appears multiple times in a 2nd column, The total being how many unique names there are in Column 3

Hoping someone can help as this is a bit over my experience level
Reply
0
0
Leila
Leila
This comment was minimized by the moderator on the site
My count isn't grouping the names together.
i.e.
Column B says
Independence
Walnut St.
Parkway
Walnut St.
and instead of my " count " column saying Walnut St. 2 it says
Walnut St. 1
Walnut St. 1

How can I fix this ?
Reply
0
0
Q
Q
This comment was minimized by the moderator on the site
This is partly what I'm looking for.
Now my next issue is, how can I sort these so that I get them in desending order?
So it should be looking like this:
James 4
Nicol 3
Ruby 3
Lucy 2
Tom 2
John 1

Hope you can help with that as well
Reply
0
0
skyyang
skyyang     Q
This comment was minimized by the moderator on the site
Hi,
If you want to sort the results in descending order, you just need to sort the data by the formula result column after getting the result.
Hope it can help you!
Reply
Report
0
0
MCR
MCR     skyyang
This comment was minimized by the moderator on the site
Whenever I try to sort, I get an error, is there a way I can fix that? I have the $A$1:$A$7, B1 as my formula - is there something in my formula I should edit?
Reply
0
0
noguchiii
noguchiii
This comment was minimized by the moderator on the site
VERY useful - thank you so much!
Reply
0
0
Mel
Mel
This comment was minimized by the moderator on the site
Thanks! Super useful!
Reply
0
0
Guest
Guest
This comment was minimized by the moderator on the site
When you drag the handle down to apply it to the rest of the values, it increments the search radius by 1. So if youre searching =COUNTIF(A1:A10,C1), when you drag the handle down, the second value will be =COUNTIF(A2:A11,C2).
Reply
0
0
Heather
Heather     Guest
This comment was minimized by the moderator on the site
This is SOOO helpful! This skips an entire step
Reply
0
0
Mikkel
Mikkel     Guest
This comment was minimized by the moderator on the site
You fix it this way
=COUNTIF($A$2:$A$11,C2)
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations