Cookies op Tweakers

Tweakers is onderdeel van DPG Media en maakt gebruik van cookies, JavaScript en vergelijkbare technologie om je onder andere een optimale gebruikerservaring te bieden. Ook kan Tweakers hierdoor het gedrag van bezoekers vastleggen en analyseren. Door gebruik te maken van deze website, of door op 'Cookies accepteren' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt? Bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

Excel lijst splitsen

Pagina: 1
Acties:

Onderwerpen

Vraag


Acties:
  • 0Henk 'm!

  • Crxtreem
  • Registratie: augustus 2007
  • Laatst online: 20:48
Ik heb in Excel een lijst met positieve en negatieve getallen.
Deze wil ik op een ander tabblad splitsen in 2 lijsten 1 met de negatieve en de andere met de positieve.

Ik heb dit Ik heb dit al gedeeltelijk werkend gekregen met de ALS functie en anders volgende rij.
Maar heeft de ALS functie ook een next functie?

De rest van de rij vul ik aan met vert.zoeken. (Of kan ik met de Als functie ook de complete rij plakken?)

Nu wil ik dit het liefst zonder macro's doen omdat ik thuis Libreoffice gebruik maar het ook op Excel wil gebruiken.

Alle reacties


Acties:
  • 0Henk 'm!

  • F_J_K
  • Registratie: juni 2001
  • Niet online

F_J_K

Moderator CSA/PB

Front verplichte underscores

Zou het kunnen voldoen om te filteren? bij de een filteren op <0 en de ander op >= 0. Dat zal niet voldoen als je er ook nog mee wilt rekenen trouwens.

Makkelijkste daarvoor: voeg op het positieve tab een hulpkolom toe: als A1 >= 0 dan A1, anders "".
En nog steeds de lege regels filteren zodat het mooi lijkt aan te sluiten.

'Multiple exclamation marks,' he went on, shaking his head, 'are a sure sign of a diseased mind' (Terry Pratchett, Eric)


Acties:
  • +1Henk 'm!

  • KabouterSuper
  • Registratie: september 2005
  • Niet online
Je kunt een tellertje maken die de positieve en negatieve getallen nummert. En in je andere tabbladen daarnaar verwijzen met een vlookup of index/match.
Dus
code:
1
2
3
4
5
1    => 1, 0
5    => 2, 0
-2    => 2, 1
5    => 3, 1
-4    => 3, 2

Acties:
  • 0Henk 'm!

  • heuveltje
  • Registratie: februari 2000
  • Laatst online: 17:11

heuveltje

KoelkastFilosoof

Crxtreem schreef op maandag 6 juli 2020 @ 08:52:
Ik heb in Excel een lijst met positieve en negatieve getallen.
Deze wil ik op een ander tabblad splitsen in 2 lijsten 1 met de negatieve en de andere met de positieve.

Ik heb dit Ik heb dit al gedeeltelijk werkend gekregen met de ALS functie en anders volgende rij.
Maar heeft de ALS functie ook een next functie?

De rest van de rij vul ik aan met vert.zoeken. (Of kan ik met de Als functie ook de complete rij plakken?)

Nu wil ik dit het liefst zonder macro's doen omdat ik thuis Libreoffice gebruik maar het ook op Excel wil gebruiken.
Sorteer op 2 dat getal. kies alles tot de 0 en knip en plak dat naar een 2e blad ?

You cant build a better world for people.
Only people can build a better world for people.
Otherwise its just a fancy cage. T. Pratchett


Acties:
  • 0Henk 'm!

  • Crxtreem
  • Registratie: augustus 2007
  • Laatst online: 20:48
heuveltje schreef op maandag 6 juli 2020 @ 09:15:
[...]


Sorteer op 2 dat getal. kies alles tot de 0 en knip en plak dat naar een 2e blad ?
Maar dat is niet te automatiseren zonder macro?
Mijn wens is dat ik dit met een formule automatisch kan doen zodat de nieuwe lijsten altijd bijgewerkt zijn.

Acties:
  • 0Henk 'm!

  • heuveltje
  • Registratie: februari 2000
  • Laatst online: 17:11

heuveltje

KoelkastFilosoof

Crxtreem schreef op maandag 6 juli 2020 @ 09:17:
[...]


Maar dat is niet te automatiseren zonder macro?
Mijn wens is dat ik dit met een formule automatisch kan doen zodat de nieuwe lijsten altijd bijgewerkt zijn.
Je kunt iets met verticaal zoeken zoals @KabouterSuper zegt.
Maar dan moet je filters instellen die automatisch opnieuw toegepast worden zodra er iets gewijzigd is.
Maar mij niet bekend dat dat mogelijk is zonder een (zeer simpel) macro.
(wat niet wil zeggen dat het niet bestaat :P)

You cant build a better world for people.
Only people can build a better world for people.
Otherwise its just a fancy cage. T. Pratchett


Acties:
  • +3Henk 'm!

  • KabouterSuper
  • Registratie: september 2005
  • Niet online
heuveltje schreef op maandag 6 juli 2020 @ 11:36:
[...]
Je kunt iets met verticaal zoeken zoals @KabouterSuper zegt.
Maar dan moet je filters instellen die automatisch opnieuw toegepast worden zodra er iets gewijzigd is.
Maar mij niet bekend dat dat mogelijk is zonder een (zeer simpel) macro.
(wat niet wil zeggen dat het niet bestaat :P)
De oplossing die ik voorstel, heeft juist geen filters nodig.
Even wat pseudo code.
Als in kolom A de reeks met positieve en negatieve getallen staan met een header, dan:
kolom B (formule is voor B2) om aantal positieve getallen te tellen: =COUNTIFS($A$2:$A2,">0")
kolom C (formule is voor C2) om aantal negatieve getallen te tellen: =COUNTIFS($A$2:$A2,"<0")
kolom D (formule is voor D2) omdat ik lui ben en geen zin had om een index/match te programmeren: =A2

kolom H (formule is voor H2, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A2)-1,B:D,3,FALSE),"")
kolom I (formule is voor I2, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A2)-1,C:D,2,FALSE),"")

Je krijgt dan in kolom H en I netjes een rijtje met de positieve en negatieve getallen zonder witte velden er tussen.

[Voor 9% gewijzigd door KabouterSuper op 06-07-2020 12:13. Reden: Geklooi met headers, soms begon ik in rij 2, soms in rij 1]


Acties:
  • 0Henk 'm!

  • heuveltje
  • Registratie: februari 2000
  • Laatst online: 17:11

heuveltje

KoelkastFilosoof

KabouterSuper schreef op maandag 6 juli 2020 @ 12:03:
[...]

De oplossing die ik voorstel, heeft juist geen filters nodig.
Even wat pseudo code.
Als in kolom A de reeks met positieve en negatieve getallen staan, dan:
kolom B (formule is voor B2) om aantal positieve getallen te tellen: =COUNTIFS($A$2:$A2,">0")
kolom C (formule is voor C2) om aantal negatieve getallen te tellen: =COUNTIFS($A$2:$A2,"<0")
kolom D (formule is voor D2) omdat ik lui ben en geen zin had om een index/match te programmeren: =A1

kolom H (formule is voor H1, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A1),B:D,3,FALSE),"")
kolom I (formule is voor I1, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A1),C:D,2,FALSE),"")

Je krijgt dan in kolom H en I netjes een rijtje met de positieve en negatieve getallen zonder witte velden er tussen.
Ik heb hem nu 3 keer gelezer, maar ik snap het nog steends niet.
als regel 3 een negatief getal is, dan is H toch gewoon leeg.
Dus wat je wil werkt wel, (mits aantal regels gelijk blijft). maar vervolgens zijn alle regels met negatieve waarde op je positive blad toch gewoon blank ?

You cant build a better world for people.
Only people can build a better world for people.
Otherwise its just a fancy cage. T. Pratchett


Acties:
  • +2Henk 'm!

  • KabouterSuper
  • Registratie: september 2005
  • Niet online
heuveltje schreef op maandag 6 juli 2020 @ 12:14:
[...]


Ik heb hem nu 3 keer gelezer, maar ik snap het nog steends niet.
als regel 3 een negatief getal is, dan is H toch gewoon leeg.
Dus wat je wil werkt wel, (mits aantal regels gelijk blijft). maar vervolgens zijn alle regels met negatieve waarde op je positive blad toch gewoon blank ?
No problem, het is niet een voor de hand liggende truc. Zie hieronder de sheet die ik net gemaakt heb. Er zijn geen lege velden in kolom H en I.

De vlookup condenseert de lijst zodat er geen lege velden zijn. Dat doe ik door het rijnummer (of eigenlijk rijnummer -1) op te zoeken in kolom B en C. Bij een vlookup wordt altijd de eerste teruggegeven, wat in dit geval precies degene is die we nodig hebben.

[Voor 20% gewijzigd door KabouterSuper op 06-07-2020 12:24]


Acties:
  • 0Henk 'm!

  • Crxtreem
  • Registratie: augustus 2007
  • Laatst online: 20:48
Ik heb hem even naar Nederlands omgezet en dit stukje werkt.
Vanavond eens kijken of ik dit in mijn eigen bestand ook zo werkend krijg.

Acties:
  • 0Henk 'm!

  • KabouterSuper
  • Registratie: september 2005
  • Niet online
Crxtreem schreef op maandag 6 juli 2020 @ 12:34:
Ik heb hem even naar Nederlands omgezet en dit stukje werkt.
Vanavond eens kijken of ik dit in mijn eigen bestand ook zo werkend krijg.
Mooi. Je kunt waarschijnlijk wel kolom D vermijden met een index/match als je dat zou willen.

Acties:
  • 0Henk 'm!

  • heuveltje
  • Registratie: februari 2000
  • Laatst online: 17:11

heuveltje

KoelkastFilosoof

KabouterSuper schreef op maandag 6 juli 2020 @ 12:03:
[...]

kolom B (formule is voor B2) om aantal positieve getallen te tellen: =COUNTIFS($A$2:$A2,">0")
kolom C (formule is voor C2) om aantal negatieve getallen te tellen: =COUNTIFS($A$2:$A2,"<0")
kolom D (formule is voor D2) omdat ik lui ben en geen zin had om een index/match te programmeren: =A2
Ah ik miste *het gebrek aan) 2e $ in de countif.
Dan werkt het inderdaad wel zoals je zegt.

Interessante oplossing !

You cant build a better world for people.
Only people can build a better world for people.
Otherwise its just a fancy cage. T. Pratchett


Acties:
  • 0Henk 'm!

  • g0tanks
  • Registratie: oktober 2008
  • Nu online

g0tanks

Moderator CSA
KabouterSuper schreef op maandag 6 juli 2020 @ 12:03:
[...]

De oplossing die ik voorstel, heeft juist geen filters nodig.
Even wat pseudo code.
Als in kolom A de reeks met positieve en negatieve getallen staan met een header, dan:
kolom B (formule is voor B2) om aantal positieve getallen te tellen: =COUNTIFS($A$2:$A2,">0")
kolom C (formule is voor C2) om aantal negatieve getallen te tellen: =COUNTIFS($A$2:$A2,"<0")
kolom D (formule is voor D2) omdat ik lui ben en geen zin had om een index/match te programmeren: =A2

kolom H (formule is voor H2, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A2)-1,B:D,3,FALSE),"")
kolom I (formule is voor I2, hoort in eigen tabblad te staan) =IFERROR(VLOOKUP(ROW(A2)-1,C:D,2,FALSE),"")

Je krijgt dan in kolom H en I netjes een rijtje met de positieve en negatieve getallen zonder witte velden er tussen.
Mooie oplossing. Volgende uitdaging: automatisch sorteren. :P

Ultrawide gaming setup: AMD Ryzen 7 2700X | NVIDIA GeForce RTX 2080 | Dell Alienware AW3418DW


Acties:
  • +1Henk 'm!

  • KabouterSuper
  • Registratie: september 2005
  • Niet online
g0tanks schreef op maandag 6 juli 2020 @ 13:14:
[...]


Mooie oplossing. Volgende uitdaging: automatisch sorteren. :P
Challenge accepted >:)

Eerst de rank bepalen. Voor de positieve getallen bijv in kolom G: <G2>=IFERROR(RANK.EQ(H2,H:H,TRUE),-1)
Dan in kolom L de gesorteerde lijst: <L2>=IF(G2=-1,"",IFERROR(VLOOKUP(ROW(A2)-1,G:H,2,FALSE),M1))

Het lastigste is om de dubbele waarden ook terug te laten komen, aangezien een rank alleen de hoogste rank teruggeeft (of het average, maar daar heb je nog minder aan).

Update: binnen een kwartier opgelost. Anyone else :9 ?

[Voor 4% gewijzigd door KabouterSuper op 06-07-2020 13:30]


Acties:
  • +1Henk 'm!

  • Lustucru
  • Registratie: januari 2004
  • Niet online

Lustucru

26 03 2016

g0tanks schreef op maandag 6 juli 2020 @ 13:14:
[...]


Mooie oplossing. Volgende uitdaging: automatisch sorteren. :P
Die is alleen maar makkelijker (als je de route neemt via matrixformules):



De matrixformules voor ongesorteerd / gesorteerd:
code:
1
2
3
4
=ALS.FOUT(INDEX($A$1:$A$10;KLEINSTE(ALS($A$1:$A$10<0;RIJ($A$1:$A$10);"-");RIJ()));"")
=ALS.FOUT(INDEX($A$1:$A$10;KLEINSTE(ALS($A$1:$A$10>0;RIJ($A$1:$A$10);"-");RIJ()));"")
=ALS.FOUT(GROOTSTE(ALS($A$1:$A$10<0;$A$1:$A$10;"-");RIJ());"")
=ALS.FOUT(KLEINSTE(ALS($A$1:$A$10>0;$A$1:$A$10;"-");RIJ());"")


Ongesorteerd moet hij eerst een matrix opbouwen van rijnummers van getallen die kleiner resp groter zijn dan 0, of "-", en dan met index het getal ophalen.
gesorteerd bouwt hij een matrix op van getallen of "-" en pakt daar de k-grootste resp de k-kleinste van. :)

De oever waar we niet zijn noemen wij de overkant / Die wordt dan deze kant zodra we daar zijn aangeland


Acties:
  • 0Henk 'm!

  • Lustucru
  • Registratie: januari 2004
  • Niet online

Lustucru

26 03 2016

KabouterSuper schreef op maandag 6 juli 2020 @ 13:29:
[...]
Update: binnen een kwartier opgelost. Anyone else :9 ?
Yep, en zonder hupkolommen. :P

De oever waar we niet zijn noemen wij de overkant / Die wordt dan deze kant zodra we daar zijn aangeland


Acties:
  • +1Henk 'm!

  • Crxtreem
  • Registratie: augustus 2007
  • Laatst online: 20:48
Gisteravond werkend gekregen op de manier van @KabouterSuper in de lijst die ik zelf had.

Het is toch nog best een geworstel om de formule's in Libreoffice werkend te krijgen.
Maar ik ben weer een stap verder.

Volgende keer de matrix eens uit proberen.
Pagina: 1


Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Microsoft Xbox Series X LG CX Google Pixel 5a 5G Sony XH90 / XH92 Samsung Galaxy S21 5G Sony PlayStation 5 Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True