Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

205 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

28 maart 2024, 19:47:11

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: Excel: Deel van gegevens naar ander tabblad  (gelezen 22015 keer)

0 leden en 1 gast bekijken dit topic.

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Excel: Deel van gegevens naar ander tabblad
« Gepost op: 23 oktober 2016, 13:55:01 »
Halllo allemaal!

Ik hoop dat alles goed gaat met iedereen?
Ik kom, voor het eerst sindsd lange tijd, weer eens op bezoek. Dit keer niet om SQL vragen te beantwoorden (die komen hier helaas niet zo veel voor), maar om zelf een Excel vraag te stellen.

Ik heb een werkblad dat er (vereenvoudigd - het werkelijke probleem is wat ingewikkelder maar ik heb het versimpeld om op de kern te focusen) zo uit ziet:

DatumKleurScore
12-10-2016Rood17
14-10-2016Blauw20
15-10-2016Geel15
18-10-2016Blauw15
22-10-2016Rood32
29-10-2016Rood5

Ik wil een tweede tabblad toevoegen met alleen de Datum en Score kolommen van alleen de rijen met kleur Rood. In dit tabblad moet dus dit komen:

DatumScore
12-10-201617
22-10-201632
29-10-20165

Nu is dit prima te doen met bv geavanceerd filter of met een draaitabel. Alleen er is één extra detail dat dit lastig maakt: als ik in het eerste tabblad gegevens wijzig of extra rijen toevoeg, dan moet het nieuwe tabblad automatisch worden bijgewerkt.

Het liefst zou ik dit doen op basis van enkel formules, zodat ik geen VBA code aan mijn spreadsheet hoef toe te voegen. Als dat niet kan is VBA code of een macro ook goed, zolang de macro maar automatisch wordt uitgevoerd als ik iets heb gewijzigd in het eerste tabblad.

(Opties waarbij het tabblad wordt weggegooid en weer toegevoegd in een macro kan ik niet gebruiken, ik wil namelijk dit nieuwe tabblad gebruiken als bron voor formules zoals gemiddelde, lopend gemiddelde etc van alleen de resultaten voor Rood)

Alvast bedankt voor het meedenken!

Groetjes,
Hugo
--
Hugo Kornelis, SQL Server MVP

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: Excel: Deel van gegevens naar ander tabblad
« Reactie #1 Gepost op: 23 oktober 2016, 14:54:05 »
Na een tijdje zoeken ben ik er toch zelf uitgekomen. Voor het geval iemand anders ooit dezelfde vraag heeft geef ik hier een uitleg van de methode.

Ik heb eerst in het originele tabblad een vierde kolom toegevoegd met een volgnummer per kleur: formule =AANTAL.ALS($B$2:$B2;B2)

In het tabblad voor Rood heb ik een metrix-formule gemaakt met behulp van INDEX en VERGELIJKEN. Bv voor de datum heb ik: {=INDEX(Data!$A:$A;VERGELIJKEN(1;(Data!$B:$B="Rood")*(Data!$D:$D=RIJ(A4)-3);0);1)}

De VERGELIJKEN is vrij complex. Het tweede argument bevat twee condities: de B-kolom in het data tabblad moet "Rood" zijn; de D-kolom (het volgnummer) moet gelijk zijn aan het rijnummer minus 3 - omdat ik drie kopregels heb staat deze formule op rijen 4 en verder, waar dit dus 1 en verder oplevert. De vermenigvulding converteert de waar/onwaar resultaten naar 0 en 1 en het resultaat is alleen 1 voor de rij die ik zoek. Dat is de waarde die VERGELIJKEN zoekt in de matrix (eerste argument).
De INDEX formule zorgt dan dat de waarde uit de corresponderende cel gekopiëerd wordt.
Dit is een matrix-formule, dus hij moet worden ingevoerd met Ctrl-Shfift-Enter. (Dan plaatst Excel er uitzichzelf {} tekens omheen)

Als laatste heb ik de formule gekopieerd naar een paar honderd rijen, en met voorwaardelijke opmaak zorg ik dat rijen waar nog geen waarden voor zijn (bv rij 7 in het voorbeeld zoekt naar de vierde Rood, maar die is er niet) niet zichtbaar zijn.
--
Hugo Kornelis, SQL Server MVP

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: Excel: Deel van gegevens naar ander tabblad
« Reactie #2 Gepost op: 23 oktober 2016, 15:21:55 »
Ik heb helaas toch te vroeg gejuichd....

Ja, de methode werkt. Maar het maakt Excel giga-traag. Ik heb meerdere van dit soort tabbladen nodig (om in het voorbeeld te blijven, aparte tabbladen voor Rood, Groen, Blauw, etc). Ik heb er nu negen gemaakt; als ik nu in het Data blad iets invoer dan heeft Excel bijna een minuut nodig om alles te herberekenen, met 100% cpu gebruik op al mijn acht processoren. En een geheugengebruik van plm 1GB. (Terwijl de spreadsheet helemaal niet groot is, slechts 500KB op schijf).

Op die manier is het spreadsheet onwerkbaar. Terug naar de tekentafel, vrees ik.
--
Hugo Kornelis, SQL Server MVP

Offline TdJ

  • Sysop
  • *****
  • Berichten: 218
  • Geslacht: Man
    • Mijn bedrijf
Re: Excel: Deel van gegevens naar ander tabblad
« Reactie #3 Gepost op: 23 oktober 2016, 19:10:40 »
Hoi Hugo,

Prettig dat je zelf al (een deel van) de antwoorden geeft <g>

Helaas zijn matrix-formules over een groot bereik in Excel inderdaad erg traag en daardoor zoals je zelf al hebt ervaren, praktisch gezien helaas vaak onwerkbaar. Er zijn wel ander oplossingen maar die hebben allemaal wel wat vba/macro in zich. Eentje is gewoon de data dynamisch in het ander werkblad opnemen, daar een filter leggen (en die kleur kolom hidden maken) en vervolgens in het oorspronkelijke werkblad een refresh van het filter initiëren (want dat gaat helaas niet automatisch). Of dat over grote bereiken traag wordt durf ik zo niet te zeggen, maar ik verwacht dat het wel acceptabel is. Andere optie is een vba/macro die alles ophaalt en gefilterd neerzet op het nieuwe werkblad. 

Zonder vba/macro zie ik zo snel geen andere optie.

TdJ