[Excel 2007]Automatisch filteren met bereik uit cellen

[Verwijderd]

Ik heb een vraagstuk waar ik met behulp van de Help en Google niet uitkom.

De situatie:
Ik heb een gigantische lijst met geplaatste bestellingen door bedrijven vanuit heel Nederland, hiervan wil ik per postcodegebied weten hoeveel unieke bestellers er waren (dubbele waarden zijn al verwijderd).
De kolom postcode is al gesplitst in twee kolommen> één voor de vier cijfers en één voor de letters. Hierdoor kan met Autofilter een Getalfilter>Tussen gemaakt worden.

Het probleem:
Er zijn veel teveel postcodegebieden om allemaal handmatig het filter voor aan te gaan passen en de velden te tellen (selecteren, Aantal overnemen). Ik heb een tweede werkblad in het document waarin twee kolommen staan, Kolom A is het postcodebereik vanaf en Kolom B is het bereik t/m

Ik zou een macro of formule willen creëren welke voor de waarden in Kolom A en B kijkt hoeveel bestellingen aan die voorwaarden voldoen, en dat in Kolom C weergeeft.

Ik denk dat een formule het makkelijkste toe te passen is, maar ik zie de oplossing over het hoofd...


Alvast enorm bedankt voor de inzet !

[ Voor 1% gewijzigd door Verwijderd op 19-03-2010 12:57 . Reden: Tabblad =/ werkblad ]

[pedorus]

Gewoon een draaitabel maken?

[Verwijderd]

Bedankt, ik zal er eens mee stoeien. Maar met met een vluchtige blik zie ik dat je daarmee alsnog zelf de selectiecriteria moet invoeren.
Ik heb honderden verschillende selectiecriteria:
Postcodebereik voor rayon 1 is bijvoorbeeld
1200 - 1239
1280 - 1299
1370 - 1399
etc.

Voor Rayon twee weer andere bereiken, en deze lopen door elkaar. Rayon 2 kan dus onder andere het bereik 1239 - 1280 hebben.

[pedorus]

Het handigst lijkt me dan dat je ergens een lijstje maakt als:
1200 Rayon 1
1240 Rayon 2
1280 Rayon 1
...
En dan eerst met de lookup-functies de juiste waarde bij iedere rij zoekt, voordat je draaitabellen gaat gebruiken.

[Verwijderd]

Je bedoelt dat ik een kolom toevoeg aan de originele gegevens met daarin het kenmerk bij welk rayon de bestelling hoort?

Dat zou kunnen, maar als er een andere indeling op postcodegebied gemaakt moet worden hang ik.

[pedorus]

Dan zul je sowieso iets moeten veranderen. Het lijkt me niet zo moeilijk om in dat geval dat tabelletje aan te passen, en de draaitabel te refreshen om het resultaat te zien.

[Verwijderd]

Ik snap je niet. Hoe zorg ik er nou voor dat in onderstaande tabel een kolom ernaast wordt aangevuld met de aantallen rijen waarvan de postcode binnen dat bereik valt?
En als ik in de tabel hieronder 1000 verander in 1150, dat in het veld ernaast de nieuwe waarde wordt ingevuld.

Rayon 1
Postcodebereik
1000 1199
1300 1439
1500 1599
2100 2199
1440 1499
1600 2099

[pedorus]

Als je de data voor de rayons zo hebt, dan kun je er niet makkelijk mee werken, en moet je het eerst omzetten in iets dat wel bruikbaar is (of een macro'tje gebruiken bij het opzoeken). Lijkt mij een niet-handige manier van aanleveren.. Heb je niet een handigere bron?

Stel dat je
[posctodevan] [postcodetm] [rayon]
dus
1000 1199 Rayon 1
1300 1439 Rayon 1
1500 1599 Rayon 1
...
zou hebben, dan zou je met alleen sorteren een handig lijstje voor lookup kunnen maken (als je de 2e kolom gewoon negeert).

[Verwijderd]

Een macro zou inderdaad wel kunnen, maar daar ben ik al helemaal niet zo in thuis.

De volgende stappen moeten dan genomen worden:
Begin macro
sla de waarde 0 op in variabele "c"
neem waarde over uit veld twee plaatsen naar links en sla deze op in variabele "a"
ga één veld naar rechts en neem deze waarde over in variabele "b"
ga naar werkblad 1
selecteer veld E3
begin lus
als cel is "null", beëindig lus
als a=<cel=<b, dan c=c+1
ga 1 cel naar beneden
einde lus
vul waarde van "c" in in de oorspronkelijk geselecteerde cel.
Einde macro

[pedorus]

Geen idee wat die macro precies moet doen (bijvoorbeeld: twee plaatsen naar links van wat?). Ik zou zo'n soort macro verwachten, die je in een module kan zetten, en kan gebruiken als functie:

Visual Basic:

Public Function LookupName(value As Long, table As Range) As String
    Dim i As Long, currentName As String, nameJustSet As Boolean
    For i = 1 To table.Rows.Count
        If Not IsNumeric(table(i, 1)) Then
            If Not nameJustSet Then
                currentName = table(i, 1)
                nameJustSet = True
            End If
        Else
            If table(i, 1) <= value And table(i, 2) >= value Then
                LookupName = currentName
                Exit Function
            End If
            nameJustSet = False
        End If
    Next i
End Function

Stel dat dan de op te zoeken postcode in A1 staat, en het tabelletje met Rayons en hun bijbehorende postcodes in E1:F100, dan gebruik je dus =LookupName(A1, $E$1:$F$100) om de bijbehorende rayons op te zoeken.

NB: voor een 'gigatische lijst' met opzoekingen zal het berekenen wel een aantal seconden duren..

---

Andere mogelijkheid:

Verwijderd schreef op vrijdag 19 maart 2010 @ 12:56:
Ik zou een macro of formule willen creëren welke voor de waarden in Kolom A en B kijkt hoeveel bestellingen aan die voorwaarden voldoen, en dat in Kolom C weergeeft.

code:

=COUNTIF(postcodes,">="&A3)-COUNTIF(postcodes,">"&B3)

(in NL is dat AANTAL.ALS en ; ipv ,)
En dat doortrekken, en daarna het eventueel weer samenvatten per rayon.

Overigens is dit probleem met bijvoorbeeld access/sql veel makkelijker op te lossen.

[Verwijderd]

Ontzettend bedankt voor alle hulp tot nu toe Pedorus
Het werkt nu als een tierelier.

code:

=AANTALLEN.ALS('Alle bestellingen tm wk09'!$E$3:$E$4142;">="&'per Rayon'!C4)-AANTALLEN.ALS('Alle bestellingen tm wk09'!$E$3:$E$4142;">"&'per Rayon'!D4)

Deze formule kan ik gewoon doortrekken langs de tabel met postcodebereiken. Deze geeft als resultaat nu dus het aantal bestellingen per postcodebereik.
Ik heb zitten puzzelen met INTERVAL om alleen het aantal unieke waarden uit het zoekbereik 'Alle bestellingen tm wk09'!$E$3:$E$4142 te tellen, maar dat krijg ik nog niet voor mekaar.

Eventueel is ook de volgende formule te gebruiken als alternatief voor bovenstaande (bijvoorbeeld voor Excel 2003 en ouder). Ook hier moet dan nog in opgenomen worden dat alleen de unieke waarden worden geteld.

code:

=SOM(ALS('Alle bestellingen tm wk09'!$E$3:$E$4182>='per Rayon'!C4;ALS('Alle bestellingen tm wk09'!$E$3:$E$4182<='per Rayon'!D4;1;0);0))

Edit:

Het is opgelost door de gegevens te kopiëren, en daarna de dubbele waarden verwijderd.
Er moet nu wel wekelijks handmatig dubbele waarden gefilterd worden, maar alles wordt verder automatisch overgenomen en berekend.

Tenzij iemand nog een truukje weet om alleen unieke waarden te tellen binnen een dynamisch bereik, mag dit draadje op Solved.

[ Voor 18% gewijzigd door Verwijderd op 23-03-2010 17:06 ]