Gå til hovedindhold

Hvordan beregnes procentdelen af ​​ja og nej fra en liste i Excel?

Hvordan kunne du beregne procentdelen af ​​ja og ingen tekst fra en liste over række celler i Excel-regneark? Måske hjælper denne artikel dig med at håndtere opgaven.

Beregn procentdelen af ​​ja og nej fra en liste over celler med formlen

Beregn procentdelen af ​​ja og nej fra en liste over celler med formlen

For at få procentdelen af ​​en bestemt tekst fra en liste over celler kan følgende formel hjælpe dig, gør som dette:

1. Indtast denne formel: =COUNTIF(B2:B15,"Yes")/COUNTA(B2:B15) ind i en tom celle, hvor du vil få resultatet, og tryk derefter på Indtast til et decimaltal, se skærmbillede:

doc per af ja 1

2. Derefter skal du ændre dette celleformat til procent, og du får det ønskede resultat, se skærmbillede:

doc per af ja 2

Noter:

1. I ovenstående formel ，B2: B15 er listen over celler, der indeholder den specifikke tekst, du vil beregne procentdelen;

2. For at beregne procentdelen af ​​ingen tekst skal du bare anvende denne formel: =COUNTIF(B2:B15,"No")/COUNTA(B2:B15).

doc per af ja 3

Bedste kontorproduktivitetsværktøjer

🤖 Kutools AI Aide: Revolutionér dataanalyse baseret på: Intelligent udførelse   |  Generer kode  |  Opret brugerdefinerede formler  |  Analyser data og generer diagrammer  |  Aktiver Kutools funktioner...
Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier  |   Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar   |  Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler   |  Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt:  12 tekst Værktøjer (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøjer (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøjer (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøjer (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

Beskrivelse

Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
Comments (19)
Rated 5 out of 5 · 1 ratings
jeremie
jeremie
This comment was minimized by the moderator on the site
i'm trying to use this formula, but some how it keeps gettings stuck on the dividing part.
=AANTAL.ALS(K3:K85;"yes")/AANTALARG(K3:K85,"yes")

AANTAL.ALS= COUNTIF
AANTALARG= COUNTA
what is wrong with the formula?
Reply
0
0
May
May
This comment was minimized by the moderator on the site
I am want to use a function that calculate a rate of 1 to 5 using a percentage of each cell if the answer is yes. Rate = (country x 25%)+(role x 25%) + (age x 25%) + ( risk x 25%)
Reply
0
0
Daniel
Daniel
This comment was minimized by the moderator on the site
Hello,

I am looking to get a percentage of cells populated in a column. I have built a tracking sheet for a project where associates will enter their initials into the cells to show they have completed that task. I would like to show a percentage of tasks completed if that makes sense?

Thanks,
Reply
0
0
Jas
Jas
This comment was minimized by the moderator on the site
Hi Mandy,

How do you use this formula =COUNTIF(B2:B15,"Yes")/COUNTA(B2:B15)

But to fetch data across multiple Sheets? For example I am looking for the word 'Yes' in a second sheet and third sheet but would like to populate the result on the first sheet.

Thank
Jas
Reply
0
0
skyyang
skyyang     Jas
This comment was minimized by the moderator on the site
Hello, Jas
If you want to get the percentage of Yes from multiple sheets, may be the below formula can help you:

=(COUNTIF(Sheet2!B2:B15,"Yes")+COUNTIF(Sheet3!B2:B15,"Yes"))/(COUNTA(Sheet2!B2:B15)+COUNTA(Sheet3!B2:B15))

But, if you just need to put the result in another sheet, please apply the below formula:
=COUNTIF(Sheet2!B2:B15,"Yes")/COUNTA(Sheet2!B2:B15)

Please have a try, hope it can help you! If you have any other question, please comment here.
Reply
Report
0
0
Dan
Dan
This comment was minimized by the moderator on the site
Team Outcome
1 Won
2 lost
4 lost
5 lost
6 Won


=COUNTIF(Table1[[#Headers],[Outcome]],"Lost")/COUNTA(Table1[[#Headers],[Outcome]])

Why does this not work using the Tables parameters ?
Reply
0
0
Dan
Dan     Dan
This comment was minimized by the moderator on the site
Forgot to mention, this returns 0 no matter the data
Reply
0
0
skyyang
skyyang     Dan
This comment was minimized by the moderator on the site
Hello, Dan,
As you said, the formula does not work correctlly in a table format, so, you need to use it in a normal range. Please don't put the formula next to the table, locate it beyond the table, as below screenshot shown:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/lost-percentage.png

Please try, hope it can help you!
Reply
Report
0
0
joel
joel
This comment was minimized by the moderator on the site
gracias, me sirvió la formula para calcular el % de SI y NO
Reply
0
0
Rated 5 out of 5
Brandy Smith
Brandy Smith
This comment was minimized by the moderator on the site
How do you use the countif when you are trying 3 different criteria to equal a percentage.
Yes/No/NA
NA- should not impact the total combined percentage of Yes/No Answers. Using as an excel audit tool.

=IF(A23="","",COUNTIF(E23:I23,"Yes")/(COUNTIF(E23:I23,"Yes")+COUNTIF(E23:I23,"NO")))

This formula is still decreasing total score when N/A is selected in cell.
Reply
0
0
MandyZhou
MandyZhou     Brandy Smith
This comment was minimized by the moderator on the site
Hello Brandy,
Thanks for your message. In B1:B10, there are 3 different data: Yes/No/NA. To calculate the percentage of the number of Yes of the total number of Yes and No, please input the formula: =IF(B1="","",COUNTIF(B1:B10,"Yes")/(COUNTIF(B1:B10,"Yes")+COUNTIF(B1:B10,"NO"))). You will get the correct result.
Sincerely,
Mandy
View Attachments (1 / 5)
Reply
Report
0
0
Natalie
Natalie
This comment was minimized by the moderator on the site
this worked HOWEVER, when I do a sort the % does not change with the sorted data. How can I get the % to change when I sort?
Reply
0
0
MandyZhou
MandyZhou     Natalie
This comment was minimized by the moderator on the site
Hello Nataile,
Glad to help. When you sort the data, you need to change the range in the Countif formula to absolute. Otherwise, the results will be wrong. For example, the first formula in the artical should be changed to: =COUNTIF($B$2:$B$15,"Yes")/COUNTA(B2:B15) . Please have a try.

Sincerely,
Mandy
Reply
Report
0
0
Joshua
Joshua
This comment was minimized by the moderator on the site
Trying to find a way to use the function

=COUNTIF(F3:F17,F21:F35,P3:P17,P21:P35,F39:F53,P39:P53,Z3:Z17,Z21:Z35,Z39:Z53,AJ3:AJ17,AJ21:AJ35,AJ39:AJ53,"No")/COUNTA(F3:F17,F21:F35,P3:P17,P21:P35,F39:F53,P39:P53,Z3:Z17,Z21:Z35,Z39:Z53,AJ3:AJ17,AJ21:AJ35,AJ39:AJ53)
However, it says I can only have 2 arguments. Is there another function I have to use for this many ranges?

Any help is much appreciated!
Reply
0
0
CHAKOURI
CHAKOURI
This comment was minimized by the moderator on the site
Bonjour,

En utilisant votre formule j'arrive a une erreur et rien n'apparait est-ce normal ? d'autant plus que j'ai bien entré la formule ..
Pouvez vous m'aider svp ?
Merci
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations