On Sheet1 the names of sellers are in Column C and in Column D are amounts. In Column C, the name of the same employee can appear multiple times.
On Sheet2, we want to show the seller with the highest total amount and then the second seller, etc. A kind of Top 10 so to speak. In addition, there is a criterion, we only count the amounts for which column E says “Sold”.
Sheet1
Sheet2
A1 Since you want to see a Top-N, enter the number here, for example 8. Then you get to see the Top-8.
You have to enter the formulas with Ctrl+Shift+Enter (not just Enter)
We hebben een tabel zoals in de afbeelding hieronder. Het doel is om dezelfde producten op te tellen en te vermenigvuldigen. Bijvoorbeeld: het aantal fusten “Rhönbräu Klosterbier” maal het aantal items (in dit geval liters, omdat de eenheid voor fusten liters is). In totaal hebben we 48 fusten “Rhönbräu Klosterbier”. Een deel van de fusten bevat 15 liter en een ander deel 12 liter. Dit komt samen uit op een totaal van 648 liter “Rhönbräu Klosterbier”.
Formule in G2 is: =SUMPRODUCT(--(A$2:A$11=F2);B$2:B$11;D$2:D$11) Doorvoeren naar beneden.
Gegevens in één cel splitsen en weergeven per cel. In Kolom A en B staan gegevens. Er staan telkens twee namen in één cel bijvoorbeeld in A2 . In B2 staan twee steden. Zie afbeelding.
1. Kopieer de onderstaande code middels Ctrl + C 2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 4. Druk op M om een standaard module in te voegen 5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 6. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel 7. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbelklik op de macro naam om te starten.
Sub Splitsen()
Dim a As Variant, b As Variant, c As Variant
Dim i As Long
a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a) * 2, 1 To 2)
For i = 1 To UBound(a)
c = Split(a(i, 1), Chr(10))
b(i * 2 - 1, 1) = c(0)
b(i * 2, 1) = c(1)
c = Split(a(i, 2), Chr(10))
b(i * 2 - 1, 2) = c(0)
b(i * 2, 2) = c(1)
Next
Range("D2:E2").Resize(UBound(b)).Value = b
End Sub
Met behulp van VBA code 8 tekens vanaf de linkerkant van een tekenreeks verwijderen. Tekenreeksen staan in kolom A.
Je moet wel opletten want een cijfer als 02 kapt Excel af en toont dat als 2. Hierdoor wordt de tekenreeks “AFT5985602” weergegeven als 2. Er zijn in dat geval 9 cijfers weggelaten. Daarom moet je de 2 aan het einde van de code niet veranderen
Option Explicit
Sub Verwijder_8_Tekens()
Range("A2", Range("A" & Rows.Count).End(xlUp)).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 1))
End Sub
In Kolom A namen van Employees waarbij dezelfde naam meerdere keren kan voorkomen. In Kolom B namen van Customers. Dezelfde Employee kan meerdere Customers hebben. Dat wil je weergeven vanaf cel A26. Zie afbeelding.
1. Kopieer de onderstaande code middels Ctrl + C 2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 4. Druk op M om een standaard module in te voegen 5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 6. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel.
Plaats de volgende Formules: A26=IFERROR(INDEX($A$2:$A$23;MATCH(0;INDEX(COUNTIF($A$25:A25;$A$2:$A$23)+(A$2:A$23="");0);0));"") Invoeren met gewoon Enter en doorvoeren naar beneden.
B26=IF(A26="";"";MID(aconcat(IF($A$2:$A$23=A26;", "&$B$2:$B$23;""));3;999)) Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+Enter, NIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }.
Let op: Plaats die accolades { } niet handmatig. Doorvoeren naar beneden.
Je hebt een Excel tabel en wil die omzetten naar een HTML tabel. Selecteer een cel in de tabel. In de afbeelding is dat cel C6. Vervolgens onderstaande code in een module zetten. En dan de Sub: Test_Tabel_Naar_HTML uitvoeren. Als de Excel tabel is overgezet wordt het resultaat naar het bestand C:\temp\textfile.html weg geschreven. Dubbelklikken op dat bestand en je ziet het resultaat in de browser. Zoiets.
Als eerste:
Kopieer de onderstaande code middels Ctrl + C
Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
Druk op M om een standaard module in te voegen
Daar waar de cursor knippert voeg je de code in middels Ctrl + V
Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
En dan de Sub: Test_Tabel_Naar_HTML uitvoeren.
Option Explicit
'VBA - Excel tabel naar HTML
'Eenvoudige code om een Excel tabel naar een HTML tabel te converteren.
'Om tabelrijen te markeren ga je er met de muis overheen.
Sub Test_Tabel_Naar_HTML()
'Selecteer één cel in je tabel zodat Excel weet
'Welke data naar de HTML tabel moet worden omgezet
'Tevens wordt het eindresultaat geprint naar:
'Direct; Venster / Immediate; Window
Debug.Print Tabel_Naar_HTML(ActiveCell.CurrentRegion)
End Sub
Function Tabel_Naar_HTML(ByVal rng As Excel.Range)
Dim strHTML_Code As String
'Begin van de HTML code die altijd hetzelfde is
strHTML_Code = "<!DOCTYPE html><html><head><style>table{border-collapse:collapse;width:100%}td,th{padding:8px;text-align:left;border-bottom:1px solid #DDD}tr:hover{background-color:#D6EEEE}</style></head><body><h2>Hoverable Table</h2><p>Move the mouse over the table rows to see the effect.</p><table>"
'Door de rijen navigeren.
Dim rngRowLoop As Excel.Range
For Each rngRowLoop In rng.Rows
strHTML_Code = strHTML_Code & "<tr>"
Dim rngCellLoop As Excel.Range
For Each rngCellLoop In rngRowLoop.Cells
strHTML_Code = strHTML_Code & "<td style='border: 1px solid lightgrey;'>" & rngCellLoop.Value2 & "</td>"
Next rngCellLoop
strHTML_Code = strHTML_Code & "</tr>"
Next
'HTML afsluiten met "</table></body></html>"
strHTML_Code = strHTML_Code & "</table></body></html>"
Tabel_Naar_HTML = strHTML_Code
'Naar bestand schrijven.
Call Schrijf_Naar_Bestand(strHTML_Code)
End Function
Function Schrijf_Naar_Bestand(strTableData As String) As Boolean
'Bestand textfile.html wordt automatisch aangemaakt
Const LogFileName As String = "C:\temp\textfile.html"
Dim FileNum As Integer
'Volgende bestandsnummer
FileNum = FreeFile
'Maakt bestand aan indien niet aanwezig
Open LogFileName For Append As #FileNum
'Schrijft informatie weg aan het einde van het bestand
Print #FileNum, strTableData
'Sluit het bestand
Close #FileNum
'Gelukt
Schrijf_Naar_Bestand = True
End Function
De methode is als volgt: Voer in A5 kolom het volgende in:
={“USA”;”FRANCE”} [ Dat wil zeggen, Selecteer A5 én sleep naar A6 zodat A5 de actieve cel blijft, voer de formule in met de toetscombinatie Ctrl+Shift+Enter Selecteer deze twee cellen opnieuw, ga naar het naamvak in de formulebalk en typ COUNTRIES, gevolgd door Enter.
Voer in een B5 het volgende in:
={“New York”;”Pittsburgh”;”Los Angeles”;”Boston”} [ Dat wil zeggen, Selecteer B5 én sleep naar B8 zodat B5 de actieve cel blijft, voer de formule in met de toetscombinatie Ctrl+Shift+Enter Selecteer deze vier cellen opnieuw, ga naar het naamvak in de formulebalk en typ USA, gevolgd door Enter.
={“Parijs”;”Nice”;”Toulon”} [ Dat wil zeggen, Selecteer C5 én sleep naar C7 zodat C5 de actieve cel blijft, voer de formule in met de toetscombinatie Ctrl+Shift+Enter Selecteer deze drie cellen opnieuw, ga naar het naamvak in de formulebalk en typ FRANCE, gevolgd door Enter.
Om te zien hoe dit werkt, activeer je A1 in het werkblad (in hetzelfde werkboek). Je gaat naar/kiest voor: Data | Data Validation | Data Validation. Kies bij ‘Allow’ voor ‘List’ en voer bij ‘Source’ de formule in: =COUNTRIES klik op OK. Ga naar cell C1. Je gaat naar/kiest voor: Data | Data Validation | Data Validation. Kies bij ‘Allow’ voor ‘List’ en voer bij ‘Source’ de formule in: =INDIRECT(A1) klik op OK.
Het kan zijn dat je een foutmelding krijgt:
Klik gewoon op Yes, Die foutmelding ontstaat omdat er in cell A1 nog geen keuze is gemaakt. Klik op het pijltje rechts van de cell en kies voor USA. Nu komen de namen van de steden in C1. Klik maar op de pijl naast C1 en dan zie je dat.
Er zijn twee vergelijkingen op te lossen. We noemen dat een verzameling van twee of meer vergelijkingen die dezelfde variabelen gebruiken (x en y). De oplossing van die twee vergelijkingen is de combinatie van x en y die aan de 2 vergelijkingen tegelijk moeten voldoen.
De 2 vergelijkingen:
2x + 3y = 21 5x + 2y = 3
Vul de waarden van de coëfficienten in: In [B8] 2 In [C8] 3 In [B9] 5 In [C9] 2
Selecteer het bereik B11:C12
En vul formule in: =MINVERSE(B8:C9). Doe dat met Ctrl+Shift+Enter
Selecteer het bereik F11:F12
En vul de formule in: =MMULT(B11:C12;E8:E9). Doe dat met Ctrl+Shift+Enter
Het Vernam Cipher/cijfer (ook wel het “One-Time-Pad”) genoemd is het éénmalig blokcipher of het perfecte cipher genoemd, is een crypto algoritme waarbij men de tekst of data combineert met een willekeurige Sleutel/Key van dezelfde lengte. Het is de enige gekende vercijfering die onbreekbaar is.
Gebruikt door Special Operations teams en het verzet in Wereldoorlog II, populair bij inlichtingendiensten en hun spionnen sinds de Koude Oorlog en decennialang ten dienste van de diplomatie en het leger heeft het Vernam Cipher een sterke reputatie opgebouwd als eenvoudige maar onbreekbare encryptie met een absolute veiligheid die ongeëvenaard is door moderne crypto algoritmes.
Om van Vernam Cipher encryptie te kunnen spreken en de onbreekbaarheid effectief te verwezenlijken dient aan verschillende voorwaarden voldaan te zijn. Indien één van deze voorwaarden ontbreekt kan men niet meer spreken van het Vernam Cipher/One-Time-Pad en is het niet meer onbreekbaar. Bij correct gebruik is Vernam Cipher/One-Time-Pad echter de enige bestaande bewezen perfect veilige vercijfering, bestand tegen elke mogelijke cryptoanalytische aanval. Dit werd bewezen in Claude Shannon’s verhandeling ‘Communication theory of secrecy systems’.
De criteria voor een echte Vernam Cipher: – De sleutel moet op een volstrekt willekeurige manier zijn gegenereerd. – De sleutel moet dezelfde lengte als de platte tekst hebben. – De sleutel mag slechts één keer gebruikt worden. – Na één keer gebruik moet de sleutel vernietigd worden. – Van een sleutel mogen slechts twee kopieën bestaan, een voor de verzender en een voor de ontvanger van de gegevens.
Door aan deze criteria te voldoen, wordt de resulterende versleutelde tekst statistisch, willekeurig en theoretisch onkraakbaar. Hierdoor is frequentieanalyse onmogelijk en is ontcijfering onmogelijk zonder de juiste sleutel.
De Code is ONKRAAKBAAR omdat het berust op een willekeurig gegenereerd getal. Zelfs met de allergrootste huidige supercomputers is de code niet te kraken MITS alle voorwaarden correct worden nageleefd.
In [B1] zet je de plaintext. Als voorbeeld hebben we de volgende tekst gebruikt: THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG In [B2] =GenerateKey(LEN(B1)) In [B3] =EncryptTextUDF(B1; B2) In [B4] =DecryptTextUDF(B3;B2)
En dan de VBA code:
Option Explicit
' =========================================================
' Vernam Cipher (XOR-based) with Hex Encoding for Excel
' =========================================================
' Generate a random key of the same length as the plaintext (A–Z)
Function GenerateKey(plaintextLength As Long) As String
Dim i As Long
Dim key As String
Randomize
For i = 1 To plaintextLength
key = key & Chr(Int(Rnd * 26) + 65)
Next i
GenerateKey = key
End Function
' Encrypt plaintext using XOR, return readable hex string
Function EncryptText(plaintext As String, key As String) As String
Dim i As Long
Dim pChar As Integer, kChar As Integer, cChar As Integer
Dim cipherHex As String
If Len(key) < Len(plaintext) Then
EncryptText = "#KEY TOO SHORT#"
Exit Function
End If
For i = 1 To Len(plaintext)
pChar = Asc(Mid(plaintext, i, 1))
kChar = Asc(Mid(key, i, 1))
cChar = pChar Xor kChar
cipherHex = cipherHex & Right("0" & Hex(cChar), 2)
Next i
EncryptText = cipherHex
End Function
' Convert hex string to raw binary string
Private Function HexToString(hexStr As String) As String
Dim i As Long
Dim result As String
Dim val As Long
hexStr = Replace(hexStr, " ", "")
If Len(hexStr) Mod 2 <> 0 Then
HexToString = ""
Exit Function
End If
For i = 1 To Len(hexStr) Step 2
val = CLng("&H" & Mid(hexStr, i, 2))
result = result & Chr(val)
Next i
HexToString = result
End Function
' Decrypt hex ciphertext using XOR with key
Function DecryptText(cipherHex As String, key As String) As String
Dim i As Long
Dim plaintext As String
Dim cChar As Integer, kChar As Integer, pChar As Integer
Dim cipherRaw As String
cipherRaw = HexToString(cipherHex)
If Len(key) < Len(cipherRaw) Then
DecryptText = "#KEY TOO SHORT#"
Exit Function
End If
For i = 1 To Len(cipherRaw)
cChar = Asc(Mid(cipherRaw, i, 1))
kChar = Asc(Mid(key, i, 1))
pChar = cChar Xor kChar
plaintext = plaintext & Chr(pChar)
Next i
DecryptText = plaintext
End Function
' =========================================================
' Worksheet-Friendly UDFs
' =========================================================
' Manual key versions (for static use)
Function EncryptTextUDF(plaintext As String, key As String) As String
EncryptTextUDF = EncryptText(plaintext, key)
End Function
Function DecryptTextUDF(cipherHex As String, key As String) As String
DecryptTextUDF = DecryptText(cipherHex, key)
End Function
' =========================================================
' Auto-Key Paired Functions (for use in adjacent cells)
' =========================================================
' Generates and stores a random key (updates each recalc)
Function EncryptAutoKey_Key(plaintext As String) As String
Dim gAutoKey As String
gAutoKey = GenerateKey(Len(plaintext))
EncryptAutoKey_Key = gAutoKey
End Function
' Uses the most recently generated key for the same recalc cycle
Function EncryptAutoKey_Cipher(plaintext As String) As String
Dim gAutoKey As String
If gAutoKey = "" Then
' fallback: if used standalone, generate a key automatically
gAutoKey = GenerateKey(Len(plaintext))
End If
EncryptAutoKey_Cipher = EncryptText(plaintext, gAutoKey)
End Function
' =========================================================
' Quick Test (Immediate Window)
' =========================================================
Sub TestVernamCipher()
Dim plaintext As String, key As String, cipherHex As String, decrypted As String
plaintext = "HELLO"
key = GenerateKey(Len(plaintext))
Debug.Print "Plaintext: " & plaintext
Debug.Print "Key: " & key
cipherHex = EncryptText(plaintext, key)
Debug.Print "Ciphertext (Hex): " & cipherHex
decrypted = DecryptText(cipherHex, key)
Debug.Print "Decrypted Text: " & decrypted
End Sub
Je kunt de code vanuit de VBA IDE bereiken via sneltoets Alt-F11 en dan Sub TestVernamCipher starten middels F5.
Belangrijk: Je kunt het beste geen computer gebruiken om de Key/Sleutel voor een Vernam Cipher te genereren, omdat een door de computer gegenereerde Key niet echt willekeurige cijfers of letters kan genereren. waardoor deze kwetsbaar is voor cryptoanalyse Om de perfecte geheimhouding van het Vernam Cipher te bereiken, moet de sleutel echt willekeurig zijn, dezelfde lengte hebben als het bericht, slechts één keer worden gebruikt en veilig worden gedeeld tussen de verzender en de ontvanger.
Een computer maakt doorgaans gebruik van PRNG, een deterministisch algoritme dat een reeks getallen produceert die willekeurig lijkt, maar in werkelijkheid voorspelbaar is als de beginwaarde en het algoritme bekend zijn.
