In $A$3:$A$93 namen waaronder veel dubbele namen, We willen in C3 een lijst met unieke namen en die lijst sorteren van A naar Z. Niet al te moeilijk. We hebben slechts één formule nodig.
In [C3] =IFERROR(INDEX($A$3:$A$93; MATCH(SMALL(IF(COUNTIF($C$2:C2; $A$3:$A$93)=0; COUNTIF($A$3:$A$93; "<"&$A$3:$A$93); ""); 1); COUNTIF($A$3:$A$93; "<"&$A$3:$A$93); 0));"")
Invoeren met Ctrl+Shift+Enter (NIET alleen Enter)
En doorvoeren naar beneden.
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)
A2 =LARGE(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold");MIN(A1;SUM(IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);1))))
A3 =IFERROR(SUM(IF(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23))>=A2;1));0)
A5 =IF($B5="";"";INDEX(Sheet1!$C$2:$C$23;SMALL(IF(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold")=$B5;ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);COUNTIFS($B$5:B5;B5))))
B5 =IF(ROWS($B$5:B5)>$A$3;"";LARGE(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold");ROWS($B$5:B5)))
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.
Indien je de relatieve positie van de laatste waarde in een bereik wil bepalen gebruik je de volgende formule:
=MATCH(2;1/($A$5:$A$22<>""))
Invoeren met Ctrl+Shift+Enter (Niet alleen maar Enter)
Het maakt niet uit of er getallen of lege cellen in het bereik staan. De formule bepaalt NIET het laatste rijnummer met een waarde
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 SubMet 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.
Function aconcat(a As Variant, Optional sep As String = "") As String
' © Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End FunctionPlaats 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:
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
