Hoe controleer ik of het nummer een priemgetal is in Excel?

Zoals we allemaal weten, is een priemgetal een natuurlijk getal dat slechts twee verschillende natuurlijke getalsdelers bevat: één en zichzelf. Als u een lijst met getallen in een werkblad heeft, hoe kunt u dan controleren of de getallen priemgetallen zijn?

Controleer of een getal een priemgetal is met de matrixformule

Controleer of een nummer een priemgetal is met de door de gebruiker gedefinieerde functie

Controleer of een getal een priemgetal is met de matrixformule

De volgende formule kan u helpen het nummer te identificeren, of het nu een priemgetal is of niet, doe dit als volgt:

1. Voer de volgende formule in een lege cel in - C2 bijvoorbeeld naast uw gegevens:

=IF(A2=2,"Prime",IF(AND(MOD(A2,ROW(INDIRECT("2:"&ROUNDUP(SQRT(A2),0))))<>0),"Prime","Not Prime")) (A2 is de cel met het nummer dat u wilt controleren), en druk vervolgens op Ctrl + Shift + Enter toetsen samen, en u krijgt het resultaat, als het nummer een priemgetal is, wordt "Prime" weergegeven in de cel, zo niet, dan wordt "Not Prime" weergegeven, zie screenshot:

2. Selecteer vervolgens de cel C2 en sleep de vulgreep naar de cellen waarop u deze formule wilt toepassen, en alle getallen worden geïdentificeerd of het een priemgetal is of niet. Zie screenshot:

Controleer of een nummer een priemgetal is met de door de gebruiker gedefinieerde functie

De volgende Door de gebruiker gedefinieerde functie kan u ook helpen om te controleren of de cijfers een priemgetal zijn of niet, doe dit als volgt:

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: controleer of een nummer een priemgetal is of niet:

Function CheckPrime(Numb As Single) As Boolean
'Updateby Extendoffice
    Dim X As Long
    If Numb < 2 Or (Numb <> 2 And Numb Mod 2 = 0) _
     Or Numb <> Int(Numb) Then Exit Function
    For X = 3 To Sqr(Numb) Step 2
        If Numb Mod X = 0 Then Exit Function
    Next
    CheckPrime = True
End Function

3. Sla vervolgens deze code op en sluit deze, ga terug naar het werkblad en voer deze formule in: = checkprime (A2) in een lege cel naast uw nummerlijst en sleep vervolgens de vulgreep naar de cellen waarin u deze formule wilt hebben.Als het getal een priemgetal is, wordt WAAR weergegeven, zo niet, dan wordt ONWAAR weergegeven, zie screenshot:

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.

Lees verder... Gratis download... Aankoop...

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!

Lees verder... Gratis download... Aankoop...

21 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
craig · 1 years ago
DO NOT ENTER THE FORMULAE IN THE CELL. Select the cell then paste the formulae in the formulae bar and press ctrl+shft+Enter. it should put { around the formulae} otherwise its wrong.
- Reply
- To post as a guest, your comment is unpublished.
  ChrisA · 1 years ago
  
  Craig, Thanks - that works! Sorry, I didn't read the instructions sufficiently carefully.
  
  Reply
To post as a guest, your comment is unpublished.
ChrisA · 1 years ago
There is definitely a problem with this equation. I did a direct copy/paste and checked it was a faithful copy but it still has 9, 15, 21, 25, 33, 35 and 39 as primes when they obviously are not. On quick inspection it seems as if factors of 3 and 5 are giving rise to some sort of problem. It is a very complicated formula for a single cell - so much so, I can't work out what it is trying to do. I usually break such things down into multiple cells to make debugging easier (which, I believe is Excel best practice). The function seems to work, though.
- Reply
To post as a guest, your comment is unpublished.
Chris · 1 years ago
There is definitely a problem with this equation. I did a direct copy/paste and checked it was a faithful copy but it still has 9, 15, 21, 25, 33, 35 and 39 as primes when they obviously are not. On quick inspection it seems as if factors of 3 and 5 are giving rise to some sort of problem. It is a very complicated formula for a single cell - so much so, I can't work out what it is trying to do. I usually break such things down into multiple cells to make debugging easier (which, I believe is Excel best practice).
- Reply
To post as a guest, your comment is unpublished.
gian_ve · 1 years ago
Hi.

I am using an italian version of Excel, so the formula does not work and i have an error. Can you help me?

Thanks in advance
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 1 years ago
  
  Hi, Gian,
  If the formula does not work correctly for you, you can apply the second method-User Defined Function. Please try, hope it can help you!
  Thank you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  gian_ve · 1 years ago
  
  Hi skyyang.
  If it can be useful for others researchers, I inform you that I have found the solution to my problem here => http://www.riolab.org/index.php?option=com_content&view=article&id=172&Itemid=68
  
  Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
Kiran · 2 years ago
Yes it worked wen I did Ctrl+Shift+Enter... !! Thank you..:D and Do u mind explaining the algorithm please.
- Reply
To post as a guest, your comment is unpublished.
Nathan · 2 years ago
There's a reference error when you type in numbers that have more than 12 digits.
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello,Nathan,
  As you said, when the numbers are longer than 12 digits, it will become scientific notation. The formula is not applied for this formatting.
  
  Reply
To post as a guest, your comment is unpublished.
vaibhav · 2 years ago
well 4095 is not a prime. still it gives result as prime no though. its incorrect.
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello, vaibhav,
  When you pasting above formula, you should press Ctrl + Shift + Enter keys together, not just Enter key, please try it again.
  
  Reply
To post as a guest, your comment is unpublished.
xsoft.cz@gmail.com · 4 years ago
Your formula is not working. Eg. 1681 is NOT a prime but by formula it is.
=IF(A2=2,"Prime",IF(AND(MOD(A2,ROW(INDIRECT("2:"&ROUNDUP(SQRT(A2),0))))<>0),"Prime","Not Prime"))
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 4 years ago
  
  Hello, Jan,
  The above formula is correct, after pastingt the formula into a cell, you should press Ctrl + Shift + Enter keys together, not just Enter key.
  Please try it again, thank you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  xsoft.cz@gmail.com · 4 years ago
  
  Yes, it was that case with CSE. Thanks (it's fixed now).
  https://superuser.com/questions/674566/when-to-use-ctrlshiftenter-and-when-to-use-enter-in-excel
  
  Btw Czech variant to:
  =IF(A2=2,"Prime",IF(AND(MOD(A2,ROW(INDIRECT("2:"&ROUNDUP(SQRT(A2),0))))<>0),"Prime","Not Prime"))
  is:
  =KDYŽ(A2=2;"Prime";KDYŽ(A(MOD(A2;ŘÁDEK(NEPŘÍMÝ.ODKAZ("2:"&ROUNDUP(ODMOCNINA(A2);0))))<>0);"Prime";"Not Prime"))
  
  Compare: https://imgur.com/a/4MgeV
  
  Reply
To post as a guest, your comment is unpublished.
John Kennedy Aquino · 4 years ago
[b]this is my fastest version[/b]

Sub generateprimenumbersbetween3()
starting_number = 1 'input value here
last_number = 30000 'input value here
primenumbers = ""
For a = starting_number To last_number
c = a
For b = 2 To c
If a Mod b = 0 And c b Then
Exit For
Else
If b = c Then
primenumbers = primenumbers & " " & a
Exit For
Else
If Round(a / (b + 1)) + 1 > b Then
c = Round(a / (b + 1)) + 1
End If
End If
End If
Next b
Next a
MsgBox primenumbers
End Sub

Sub ISPRIME3()
number_to_be_checked = 2000000000 'input value here
c = number_to_be_checked
For b = 2 To c
If number_to_be_checked Mod b = 0 And c b Then
MsgBox "Not Prime. Divisible by " & b
Exit Sub
Else
If b = c Then
MsgBox "Prime"
Exit Sub
Else
If Round(number_to_be_checked / (b + 1)) + 1 > b Then
c = Round(number_to_be_checked / (b + 1)) + 1
End If
End If
End If
Next b
End Sub
- Reply
To post as a guest, your comment is unpublished.
Rick · 4 years ago
The user defined function does not seem to work for numbers above ~16777213
- Reply
- To post as a guest, your comment is unpublished.
  John Kennedy Aquino · 4 years ago
  
  Sub generateprimenumbersbetween()
  starting_number = 99990 'input value here
  last_number = 99999 'input value here
  primenumbers = ""
  For a = starting_number To last_number
  For b = 2 To a
  If a - b * Int(a / b) = 0 And a b Then
  Exit For
  Else
  If a = b Then
  primenumbers = primenumbers & " " & a
  End If
  End If
  Next b
  Next a
  MsgBox primenumbers
  End Sub
  
  Function primenumbersbetween(starting_number, last_number)
  primenumbers = ""
  For a = starting_number To last_number
  For b = 2 To a
  If a - b * Int(a / b) = 0 And a b Then
  Exit For
  Else
  If a = b Then
  primenumbers = primenumbers & " " & a
  End If
  End If
  Next b
  Next a
  primenumbersbetween = primenumbers
  End Function
  
  'to check if a number is prime
  Sub ISPRIME()
  number_to_be_checked = 102 'input value here
  For b = 2 To number_to_be_checked
  If number_to_be_checked - b * Int(number_to_be_checked / b) = 0 And _
  number_to_be_checked b Then
  MsgBox "Not Prime. Divisible by " & b
  Exit Sub
  Else
  If number_to_be_checked = b Then
  MsgBox "Prime"
  End If
  End If
  Next b
  End Sub
  
  'to check if a number is prime
  Function ISPRIME2(number_to_be_checked)
  For b = 2 To number_to_be_checked
  If number_to_be_checked - b * Int(number_to_be_checked / b) = 0 And _
  number_to_be_checked b Then
  ISPRIME2 = "Not Prime. Divisible by " & b
  Exit Function
  Else
  If number_to_be_checked = b Then
  ISPRIME2 = "Prime"
  End If
  End If
  Next b
  End Function
  
  Reply
To post as a guest, your comment is unpublished.
Craig B · 4 years ago
The array formula doesn't work but the User defined function does and was very helpful. Thanks!
- Reply
To post as a guest, your comment is unpublished.
Craig B · 4 years ago
The array formula above doesn't work but the User Defined function does and was very helpful.Thanks!
- Reply
To post as a guest, your comment is unpublished.
Yoshi Enomoto · 4 years ago
Hi there,
I like your article. Thanks a lot.
My simple tests showed that 99 was a prime number while 99 is not as 99 is divided by 3 and 33 as well as 1 and 99.
Can you let me know what is wrong with my operation?
- Reply

Log in op uw account

Wachtwoord Resetten