[Excel] Het grote Excelvragentopic #42

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?

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 donderdag 8 januari 2015 08:28 schreef Stickers het volgende:

[..]

Niemand?
[ code verwijderd ]

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 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.

Die formule werkt inderdaad ook en het is voor mij handiger als ik een 1 of een 0 zie. Bedankt!
En klopt dit principe ook voor het klantverlies? Dus vergelijk ik de specifieke klant in A2 (2012) met de kolom B;B (2013).

[ Bericht 4% gewijzigd door Kriebelhoest op 10-01-2015 12:59:57 ]

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 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.

Ga naar Ribbon "Pagina-indeling" -> knopje Titels afdrukken -> Tabblad Kop/voettekst -> voettekst pull down menu openen en hij staat er tussen als standaard selectie.

Hij pakt de naam van je tabblad zelf, maar je kunt het eventueel ook wijzigen naar hoe je het wil.

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.

Bedankt voor de hulp! Ik ga er maandag op kantoor mee aan de gang

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.

Duidelijker kon je het niet uitleggen. Bedankt!

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 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);"")

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

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.

Als ik de formule doortrek over de hele kolom blijven de cellen leeg en aan het eind opent Excel een venster ''waarden bijwerken A'' waarbij ik schijnbaar een ander bestand moet openen. Terwijl de andere tab in hetzelfde bestand zit.

Edit:

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

Heb Groep A nu tussen twee haakjes geplaatst en dat werkt wel.

[ Bericht 10% gewijzigd door Bismarck op 15-01-2015 14:05:36 ]

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);"")

kanweg

[ Bericht 4% gewijzigd door Twentsche_Ros op 15-01-2015 14:17:38 ]

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

Je moet de range (bereik?) opgeven doormiddel van indirect (ik weet de Nederlandse versie er niet van). Overigens denk ik dat je de Nederlandse variant van IF moet hebben...

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

Hier gaat volgens mij best veel fout

Voorbeeld gemacht
http://s000.tinyupload.com/index.php?file_id=81719641226553972307

En idd met indirect. Al zou ik dat liever met een offset doen. Ofzo < niet dus want 2 verschillende sheets
Sowieso zou ik die = weghalen voor de if.

En waarom die 1:1048576 als range? En niet A:J oid?

Waarom ik het hele tabblad heb geselecteerd weet ik eigenlijk niet. Doe het altijd zo (zonde de nietwerkende IF functie) en dan werkt het prima.

Bedankt voor het voorbeeld Holy_Goat Ik ga het verder uitwerken.

Bedankt voor de reacties!

Kolom J geeft aan of er een nieuwe sessie is gestart (j/n). Kolom K bevat de actuele tijd per seconden voor iedere stap. Kolom L bevat de starttijd van iedere sessie. Iedere keer als in kolom J wordt aangegeven dat een nieuwe sessie is gestart geeft kolom L de starttijd van die sessie weer. Dat is een simpele formule: =ALS(J2="ja";K2;"")

De opvolgende cellen blijven leeg totdat er weer een ''ja'' is. Nu wil ik dat in kolom L in iedere cel de starttijd wordt neergezet totdat een nieuwe starttijd wordt gevonden.

Dus ipv:
ja 1
Nee -
Nee -
Nee -
Ja 2

Dit:
Ja 1
Nee 1
Nee 1
Nee 1
Ja 2

Je kan ipv "" ook gewoon verwijzen naar de cel erboven: K1

Dus:=ALS(J2="ja";K2;k1)

Ik snap je niet precies, maar lost dit je probleem niet gewoon op?

Wat Sylvesterrr zegt, zij het dat =ALS(J2="ja";K2;k1) moet zijn =ALS(J2="ja";K2;L1).

Dank.

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: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'';))

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

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

Hij doet het
Reuze bedankt

Ik heb in een werkblad een tabel met daarin kolommen waar een gebruiker iets moet invoeren (voornaam, tussenvoegsel, achternaam, activiteit, etc) en kolommen die op basis hiervan gevuld worden (volledige naam, activiteitomschrijving, activiteitlocatie, etc).

Wanneer ik dit onbeveiligd laat, zal ik met druk op de tab in de laatste kolom van de laatste regel een nieuwe regel toevoegen aan de tabel. Wanneer ik echter alleen de invulkolommen deblokkeer en het werkblad beveilig blijf ik met de tabtoets door de laatste regel tabben en wordt er geen nieuwe regel toegevoegd.

Wat vergeet ik te deblokkeren of welke rechten moet ik toestaan om dit wel te kunnen? Mooiste zou namelijk zijn als de gebruiker alleen kan tabben in de invoercellen en de geblokkeerde cellen overslaat (het zijn er namelijk een stuk of 8 per regel)
Ik gebruik overigens engelstalige Excel 2013

[ Bericht 0% gewijzigd door Zeus1981 op 23-01-2015 17:13:13 ]

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.

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.

Bedankt! Had gehoopt dat ik gewoon een instelling vergeten was... :-(
Ik laat de gebruiker dan gewoon het onbeveiligde werkblad gebruiken. Hij bespaart met mijn oplossing toch al zo'n 10 uur op de vorige keer dat hij deze inschrijvingen verwerkte. Die 8 extra tabs per regel neemt hij maar voor lief