[Excel] Het grote Excelvragentopic #42

quote:

Op woensdag 7 januari 2015 17:10 schreef hugecooll het volgende:
Aight nu heeft ie op de goede plaatsen een 1 geplaatst, maar hoe fix ik het dan dat Excel de 1 ook als 1 herkent? Dus dat ik in een cel al die eentjes kan optellen? =SOM(C4:C488) komt op 0 uit, terwijl er een hoop eentjes staan

quote:

Op woensdag 7 januari 2015 17:22 schreef qu63 het volgende:

[..]

Dan moet je de eentjes zonder de aanhalingstekens plaatsen

quote:

Op woensdag 7 januari 2015 17:23 schreef hugecooll het volgende:

[..]

Dus dan wordt bijv deze formule:
=ALS(AANTAL.ALS($A$600:$A$2000;B11)=1;"1";"")
=ALS(AANTAL.ALS($A$600:$A$2000;B11)=1;1;"")

Toch?

Edit: ja dus, thx

quote:

Op woensdag 7 januari 2015 09:56 schreef Stickers het volgende:
Hoi allemaal,

Ik heb een matrix formule welke werkt, maar ik niet helemaal begrijp. Kan iemand hier het een en ander toelichten?

Ik weet overigens wat het het doet, maar niet hoe het werkt

quote:

Op zondag 4 januari 2015 23:24 schreef qu63 het volgende:

[..]

Waar gaat die data heen dan?

[..]

Met creatief gebruik van de zoekfuncties en index() kan je ver komen, en zelfs tot t einde

quote:

Op donderdag 8 januari 2015 08:38 schreef Meike26 het volgende:

[..]

De data gaat naar onze website + Fb pagina, waar de leden de overzichten na kunnen trekken voor hun gereden tijd, positie en eind uitslag in de punten + kampioenschap.

quote:

- Of bedoelde je de data van het invoer veld naar de tabel? Ik wil dus naam + auto + tijd opgeven in het invoerveld en deze moet direct automatisch gesorteerd worden door Excel op tijd (uren) ; (min.) ; (seconden) , (tienden tot duizendsten). Dus die 3 moeten wel aan elkaar gekoppeld blijven, daarom wilde ik voor de zekerheid en duidelijkheid 1 formule hebben van het invoerveld de tabel in. Ik heb nu in iedergeval 8 werkbladen klaar staan voor de eerste 4 maanden. 1 werkblad komt er bij voor het punten overzicht.

quote:

Ik weet niet wat makkelijker/sneller werkt, iedereen heeft wel een acrobat reader op zijn pc of tablet staan. Excel is een ander verhaal en niet iedereen heeft daar tijd/zin in om uit te pluizen hoe je kan zoeken. Aanbieden in zo mogelijk de meest hapklare brokken.

quote:

Op donderdag 8 januari 2015 13:55 schreef Meike26 het volgende:
Maar als het resultaat er uiteindelijk staat, kan ik toch de eind-uitslag van de tabel exporteren als een read-only file / pdf ?

quote:

Op donderdag 8 januari 2015 14:10 schreef qu63 het volgende:

[..]

Ja, dat kan wel ja Ik dacht dat je alles handmatig in een PDF-je wilde doen

quote:

Op donderdag 8 januari 2015 14:19 schreef Meike26 het volgende:

[..]

Ik heb je een pm gestuurd.

quote:

Op donderdag 8 januari 2015 16:39 schreef Bismarck het volgende:
Ik wil weten hoelang een proefpersoon erover doet om een scherm in een computerprogramma te selecteren. In het logbestand van het programma heb ik een kolom met de absolute tijd (L) per stap en een kolom met de handelingen.

Twee voorbeelden van handelingen:

A1 Verkiezing 1 gestart. Sessienummer: C_018_1
A2 Gekozen: Lijst 1
A3 Gekozen: Kandidaat 3
A4 Keus bevestigd. Stembiljet wordt geprint.

A10 Verkiezing 2 gestart. Sessienummer: C_018_2
A11 Gekozen: Lijst 13
A12 Geselecteerd: Tab2
A13 Gekozen: Kandidaat 25
Keus bevestigd. Stembiljet wordt geprint.

De lijstnummers en kandidaatnummers zijn telkens afwijkend, dus daar zal moeten worden gezocht op een deel van de tekst.

Als kolom A2 bevat: "gekozen lijst" en kolom A3 bevat: ''gekozen kandidaat" dan L3-L2 = de tijd om van lijstenscherm naar kandidaatscherm te komen. Hoe vat ik dit in een formule?

quote:

Op vrijdag 9 januari 2015 09:50 schreef Stickers het volgende:
Om te zoeken of een cel een tekst bevat kun je de volgende formule gebruiken:
[ code verwijderd ]

Een WAAR geeft aan dat er iets gevonden is en #WAARDE dat er niks gevonden is.

Volledige formule adhv jouw tekst:

=ALS(ALS.FOUT(EN(VIND.SPEC("gekozen lijst";A2)>0;VIND.SPEC("gekozen kandidaat";A3)>0);ONWAAR);L3-L2;"")

quote:

Op vrijdag 9 januari 2015 10:01 schreef vitessearnhem1.026 het volgende:
Is het mogelijk om horizontaal zoeken te combineren met min en max?

Ik wil binnen 1 formule eerst de maximale waarde van een reeks laten zoeken op basis van een kenteken, daarna de minimale waarde van dezelfde reeks hier van af trekken. Iemand een idee hoe?

quote:

Op vrijdag 9 januari 2015 10:35 schreef Bismarck het volgende:

[..]

Kan je 'm niet gewoon opknippen in drie stappen?

=MAX(A1:Z1)
=MIN(A1:Z1)
=Max-Min

quote:

Op vrijdag 9 januari 2015 12:26 schreef SgtPorkbeans het volgende:
Hoi hoi

Ik zit met het volgende, ik heb een rij getallen, en nu wil ik dat Excel hiervan de hoogste kiest, en aan de hoogste vervolgens een bonus toe kent. Aan de rest van de getallen wordt dan geen bonus toegekend. Hoe moet ik dit in Excel verwerken?

Let wel: het is een Engelse versie van Excel

quote:

Op vrijdag 9 januari 2015 23:30 schreef Kriebelhoest het volgende:
Beste lezers,

Ik zit met een probleempje, namelijk dat ik niet weet welke formules of wat de beste methode is om het volgende uit te vogelen:

Ik heb een klantbestand in excel (zie: [Klantnaam][omzet]) van de afgelopen 3 jaar, opgedeeld in A, B en C klanten in termen van omzet. Nu moet ik het volgende zien te bepalen:

1. Wat is het klantbehoud?, dus hoeveel van de unieke klanten in 2013 hebben ook in 2012 voor omzet gezorgd. Hetzelfde bij 2014 t.o.v. 2013 en 2014 t.o.v. 2012
2. Hoeveel nieuwe unieke klanten zijn er jaarlijks bijgekomen?
3. Hoeveel unieke klanten zijn er jaarlijks weggegaan?

Het probleem is dat ik geen formule weet die dit gemakkelijk kan laten zien in termen van aantallen of percentage. Als ik dit weet dan kan ik dit vervolgens ook nog eens toepassen per klantsegment.

Dus als iemand mij hierbij van een tip kan voorzien, ben ik erg dankbaar

quote:

Op vrijdag 9 januari 2015 23:35 schreef Janneke141 het volgende:

[..]

Maak voor ieder gegeven dat je wil berekenen een aparte hulpkolom aan die je gaat voorzien van enen en nullen: een 1 als die klant voldoet aan de eis, en een 0 als hij er niet aan voldoet.

Voorbeeld: je wil weten of een klant klant was in 2012 én 2013 (even in de veronderstelling dat de jaren 2012-2013-2014 in de kolommen A, B, C staan), dan wordt de bijbehorende formule
=ALS(EN(A2>0;B2>0);1;0)

Sommeer die rij, en je weet het aantal behouden klanten voor 2013. Voor de andere wensen kun je vast wel zelf verzinnen wat de bijbehorende formules moeten zijn.

De bovenstaande formule kun je trouwens ook vervangen door het kortere
=A2*B2>0
Maar daarvan zal misschien niet iedereen meteen zien dat die werkt.

quote:

Op zaterdag 10 januari 2015 00:05 schreef Kriebelhoest het volgende:

[..]

Excuus, maar ik weet niet precies wat je bedoeld met het maken van de hulpkolom en het geven van de 1 of 0 aan de klant wanneer hij aan de eis voldoet of niet. Aan welke eis moet de klant voldoen? Dit is onduidelijk voor mij in jouw uitleg.

Edit: Ik heb inmiddels voor het gemak de kolommen zoals in jouw uitleg gemaakt, dus a=2012, b = 2013 en c = 2014. Ik heb de volgende formule gebruikt, alleen ik weet nu niet zeker of dit juist is:= VLOOKUP(B2;A:A;1;FALSE). Ik krijg volgens mijn redenering nu de klant te zien wanneer hij zowel in 2013 als in 2012 klant is, en een N/A wanneer dit niet zo is. Klopt dit?

Edit2: Ik krijg overigens hetzelfde als ik de formule vergelijk gebruik, =MATCH(B2;A:A;0). Dus ik kan er denk ik van uit gaan dat dit klopt?

quote:

Op zaterdag 10 januari 2015 00:05 schreef Kriebelhoest het volgende:

[..]

Excuus, maar ik weet niet precies wat je bedoeld met het maken van de hulpkolom en het geven van de 1 of 0 aan de klant wanneer hij aan de eis voldoet of niet. Aan welke eis moet de klant voldoen? Dit is onduidelijk voor mij in jouw uitleg.

Edit: Ik heb inmiddels voor het gemak de kolommen zoals in jouw uitleg gemaakt, dus a=2012, b = 2013 en c = 2014. Ik heb de volgende formule gebruikt, alleen ik weet nu niet zeker of dit juist is:= VLOOKUP(B2;A:A;1;FALSE). Ik krijg volgens mijn redenering nu de klant te zien wanneer hij zowel in 2013 als in 2012 klant is, en een N/A wanneer dit niet zo is. Klopt dit?

Edit2: Ik krijg overigens hetzelfde als ik de formule vergelijk gebruik, =MATCH(B2;A:A;0). Dus ik kan er denk ik van uit gaan dat dit klopt?

quote:

Op vrijdag 9 januari 2015 10:01 schreef vitessearnhem1.026 het volgende:
Is het mogelijk om horizontaal zoeken te combineren met min en max?

Ik wil binnen 1 formule eerst de maximale waarde van een reeks laten zoeken op basis van een kenteken, daarna de minimale waarde van dezelfde reeks hier van af trekken. Iemand een idee hoe?

quote:

Op donderdag 8 januari 2015 08:28 schreef Stickers het volgende:

[..]

Niemand?
[ code verwijderd ]

quote:

Op zaterdag 10 januari 2015 08:18 schreef Janneke141 het volgende:

[..]

Oh op die manier, je lijst met gegevens is wat anders opgebouwd als dat ik dacht. Dan kun je inderdaad gebruiken wat je hierboven doet. Wel moet je goed opletten wat je nu precies telt: in het voorbeeld hierboven kijk je of de 2013-klant in cel B2 ook al klant was in 2013. Het aantal nieuwe klanten in 2013 is dus het aantal #N/A'tje, die je dan zou moeten tellen.

Als ik er vanuit ga dat per jaar geen dubbele klanten in de kolommen voorkomen, dan is het volgende misschien mooier: gebruik COUNTIF's.
=COUNTIF(A:A;B2) geeft een 1 als de klant in B2 in 2012 ook al klant was, en een 0 als hij dat niet was. Als je deze formule naar beneden kopieert en vervolgens sommeert, dan is de som van die rij het totaal aantal gebleven klanten. Het aantal nullen in de rij is het aantal nieuwe klanten.

Misschien kun je hiermee weer even vooruit.

quote:

Op zaterdag 10 januari 2015 13:06 schreef Intentiion het volgende:
Beste mensen,

Is het mogelijk om automatisch opeenvolgende pagina nummers onder elke worksheet te krijgen.

Dus:

Worksheet 1: pagina 1
Worksheet 2: pagina 2
Etc.

quote:

Op zaterdag 10 januari 2015 09:52 schreef snabbi het volgende:

[..]

Horizontaal zoeken pakt altijd de eerste match die het vindt. Dat kan je dus niet gebruiken.
Je kan het bijvoorbeeld doen via een array/matrix formule. Wat je zal moeten doen is een lijst van kentekens maken en vervolgens je formule toepassen. Ik vind de volgende site voor het onderdeel MIN IF en MAX IF wel een goede uitleg geven: http://www.contextures.com/excelminmaxfunction.html

De plaatjes erbij voorkomen dat ik een heel verhaal hier neer moet zetten
Overigens is het nadeel van een array formule dat het veel tijd kost in de berekening. Als je bestand dus groot is (groot wagenpark) dan gaat het wel ten koste van je performance. Daarnaast is het gebruik van die CTRL SHIFT ENTER invoer van de formule gevoelig voor het maken van fouten. Pak er dus even rustig je tijd voor.

quote:

Op zaterdag 10 januari 2015 10:29 schreef snabbi het volgende:

[..]

Een matrix formule maakt een vector (=reeks aan getallen) of matrix binnen 1 cel. Je bouwt als het ware een excel sheet binnen je excel sheet. Het resultaat van die sheet schrijf je weg in 1 cel.

In jouw formule heeft die maxtrix een lengte van 150. Ik splits het even op voor je:
ALS($C$1:$C$150=C10
In dit deel van de formule wordt 150 keer de vergelijking gemaakt of C1,C2,C3,C4 etc gelijk zijn aan C10. Klik maar op de fx knop voor de formule opbouw wizzard en je zal ook zien dat in het eerste deel van de formule staat {ONWAAR\ONWAAR\ONWAAR...} om aan te geven dat C1,C2,C3 niet gelijk is aan C10.

ALS($C$1:$C$150=C10;$H$1:$H$150)
Wanneer de waarde gelijk is aan je C10, dan pak je de corresponderende waarde in H1:H150. Is het niet waar dan is het gewoon leeg oftewel 0.

Met andere woorden in dit stukje formule heb je een lijst gebouwd van alle waarden in H waarbij je C kolom gelijk is aan C10.

Volgende deel:
ALS($C$1:$C$150=C10;RIJ($H$1:$H$150))
Hier doe je hetzelfde als wat hierboven staat, alleen pak je niet de waarde maar het rijnummer van die betreffende waarde in de H kolom.

Volgende deel:
INDEX(<formule1>;MAX(<formule2>))
Nogmaals je hebt in formule1 een vector aan getallen staan dat correspondeert met een 0 wanneer de C niet gelijk was aan C10 of de H waarde wanneer dat wel zo is.
In formule2 heb je de rijnummers staan en daar pak je het maximum van. Met andere woorden je wilt de rij hebben wanneer je waarde voor het laatst voor is gekomen.

Eindresultaat:
Je krijgt de waarde uit de H kolom voor de laatste keer in de serie van C1:C150 waarbij de C gelijk is aan de waarde in C10.

quote:

Op donderdag 15 januari 2015 10:55 schreef Bismarck het volgende:
In kolom V op tabblad 1 is een lijst met sessienummers opgenomen. Op een ander tabblad is ook een lijst met sessienummers opgenomen, in willekeurige volgorde. Ik wil checken of de sessienummers in kolom V overeenkomen met de sessienummers op het andere tabblad. Ik probeer de volgende formule, maar die werkt niet. Heeft iemand een suggestie?

=ALS.FOUT(VERT.ZOEKEN(V2;Groep A!$A$1:$A$999;1;ONWAAR);"")

quote:

Op donderdag 15 januari 2015 12:28 schreef Janneke141 het volgende:

[..]

Wat gaat er mis, want zo op het oog lijkt het wel te kloppen.

quote:

Op donderdag 15 januari 2015 13:37 schreef Bismarck het volgende:
=ALS.FOUT(VERT.ZOEKEN(V2;'Groep A'!$A$1:$A$999;1;ONWAAR);"")

quote:

Op maandag 19 januari 2015 16:11 schreef Mark_ng het volgende:
Goedemidddag,

Ik ben bezig met het maken van een excel werkblad maar ik kom er niet helemaal uit.
Nu wil ik verticaal zoeken gebruiken om een prijs op te zoeken uit 2 lijsten. ik wil zelf kunnen selecteren in welke lijst hij moet gaan zoeken doormiddel van een dropdown.

=C9*VERT.ZOEKEN(D3;=if(F9="01","01 CSV'!1:1048576","02 Csv'!1:1048576";10;0)

is mijn code maar die doet het niet.

Kan iemand mij een beetje opweg helpen?

Ik werk in Excel 2010 NL versie.

Alvast bedankt!

Gr. mark

quote:

Op woensdag 21 januari 2015 18:20 schreef SgtPorkbeans het volgende:
Oke de situatie is als volgt:
Ik wil een formule die korting geeft aan een bepaalde waarde.
In mijn geval <25 10% korting en >65 25% korting.
Dit lukt, alleen moet ik de formule zo maken dat als er een waarde komt tussen de 25 en 65 er ''Geen korting'' wordt weergegeven.

Ik heb dit geprobeerd, maar dan krijg ik een foutmelding.
Iemand een idee hoe het wel moet

=ALS(C6<25;"10% korting";ALS(C6>65;"25% korting";ALS(C6=26…64;''Geen korting'';))

quote:

Op woensdag 21 januari 2015 18:32 schreef Stickers het volgende:

[..]

De derde als is overbodig. Je controleert met de eerste en tweede als immers al of de waardes voldoen aan jouw criteria. Doen zij dit niet, dan is het per definitie onwaar.

=ALS(C6<25;"10% korting";ALS(C6>65;"25% korting";"geen korting"))

Op m'n mobiel, dus kan er naast zitten wat betreft haakjes en puntkomma's haha

SPOILER: Om een idee te geven van een dergelijke macro

Om spoilers te kunnen lezen moet je zijn ingelogd. Je moet je daarvoor eerst gratis Registreren. Ook kun je spoilers niet lezen als je een ban hebt.

quote:

Op vrijdag 23 januari 2015 20:55 schreef Stickers het volgende:
Even wat gegoogled en er is (volgens mij) geen standaard oplossing. Een van de vele opties die er worden gegeven zijn macro's die de beveiliging van het blad eraf gooien, de rij toevoegen en de beveiliging er vervolgens weer terug op gooien.

Een alternatief is met gegevensvalidatie een onrealistische voorwaarde instellen, waardoor de cellen niet gewijzigd kunnen worden. Afhankelijk van je gebruikers kun je dan een waarschuwing instellen. Nadeel hiervan is echter dat een gebruiker simpelweg een cel kan kopiëren en plakken in de cellen met gegevensvalidatie.

Al met al denk ik dat je toch bent aangewezen op een macro.

SPOILER: Om een idee te geven van een dergelijke macro

Om spoilers te kunnen lezen moet je zijn ingelogd. Je moet je daarvoor eerst gratis Registreren. Ook kun je spoilers niet lezen als je een ban hebt.

Dit is overigens slechts een voorbeeld, ik zou het zelf niet gebruiken, tenzij je al bekend bent met macro's.