in datum zoeken naar tekst

[Rheino]

Ik heb een Excel-blad met 40 tabbladen aan gegevens (de klant wil het nu eenmaal zo). Het voorste tabblad geeft een overzicht van de gegevens op de andere tabbladen. Bijvoorbeeld:
Tablad 1 bevat gegevens over bedrijf x. Daarin staat een kolom datum en een kolom winst
Tabblad 2 bevat gegevens over bedrijf y. Daarin staat een kolom datum en een kolom winst.

Nu wil ik op het overzichtstabblad een trendlijn toevoegen die de winst van alle bedrijven meeneemt. De oplossing die ik daar zelf voor bedacht heb is om een ALS-formule toe te voegen op ieder tabblad, die de winst sorteert per datum. Op het voorblad kan ik dan alle totaten per maand optellen en daar een grafiek van maken. De gebruikte ALS-formule zou dan zijn: =ALS(VIND.SPEC("apr";A1);B1;0). A1 bevat de datum, B1 bevat de winst. Probleem hiermee is dat de datum is opgeslagen als een getallenreekt (je zie wel 1-apr, maar er zit een uniek getal "achter"). Hij zoekt dus niet in de tekst die ik zie. Hoe kan ik dit omzeilen? En is er misschien nog een beter idee dan deze formule op elk tabblad toevoegen?

Voor de duidelijkheid heb ik een bijlage gepost. Bij ingewikkelde formules graag een beetje uitleg met wat het betekent , zodat ik de formule eventueel zelf om kan bouwen.

Bedankt alvast!

 

[bandito bob]

In D2 kan je b.v. deze formule plaatsen:

=ALS(MAAND($A2)=KOLOM();$B2;0)

deze kan je vervolgens doortrekken naar rechts.

Uitleg:
Maand($A2) geeft het cijfer van de maand, b.v. 4 voor April. Als dit gelijk is aan het kolom nummer waar de formule staat (kolom D is dus 4, wat staat voor April) dan is de uitkomst gelijk aan cel $B2, anders wordt de waarde 0.

Op het voorblad kan je in b1 zetten:

=SOM(bedrijf1:bedrijf2!D2)

 

[Rheino]

Super! Dit is precies wat ik zoek en ik begrijp het nog ook .

op deze manier werk ik vaker trouwens, maar zou er niet een makkelijkere manier zijn om op het totaalblad de gegevens te genereren?

 

[bandito bob]

Voor meerdere werkbladen zal dat niet zomaar gaan. Volgens mij is alleen de SOM functie te gebruiken over meerdere werkbladen. Je zal dus eerst de gegevens moeten "filteren" per werkblad, om hierna de uitkomsten van al die gegevens op te tellen.

Of je moet in VBA gaan programeren, hierin zal het ook wel kunnen. Maar of het dan makkelijker wordt?

 

[Rheino]

Met de SOM-funcite kun je maximaal 30 getallen optellen. Aangezien het Excel-bestand 40 tabbladen heeft, lukt dat niet. Ik ga hem nu maar eens helemaal omgooien, want werd al helemaal gek van die 40 bladen .

Thanx voor je hulp!

 

[bandito bob]

Met de SOM-funcite kun je maximaal 30 getallen optellen. Aangezien het Excel-bestand 40 tabbladen heeft, lukt dat niet. Ik ga hem nu maar eens helemaal omgooien, want werd al helemaal gek van die 40 bladen .

Thanx voor je hulp!

?

Als je de som wilt hebben van alle cellen D2, van blad bedrijf1 t/m bedrijf 40 dan verander je de formule: =SOM(bedrijf1:bedrijf2!D2) ....in: =SOM(bedrijf1:bedrijf40!D2)

Alle werkbladen die "fysiek" tussen die 2 werkbladen zitten worden meegeteld.

 

[Rheino]

Sorry, toch nog een vraag. Zie bijlage. Ik heb nu de bovenste tabel op het tabblad totaaloverzicht kunnen maken zonder tussenstap. Nu wil ik ook de onderste tabel kunnen maken zonder tussenstap (dus zonder de gegevens helemaal rechts op het tabblad "verwerking").

 

[bandito bob]

Zoiets als in de bijlage?

 

[Rheino]

Ja, precies zo. Kun je de formule uitleggen, ik snap hem namelijk niet helemaal (sorry...)

Bedankt!

 

[bandito bob]

OK, even proberen uit te leggen.

=SOMPRODUCT([COLOR="Red"](Verwerking!B$7:B$18>=Totaaloverzicht!B18)[/COLOR]*[COLOR="DarkGreen"](Verwerking!B$7:B$18<Totaaloverzicht!B19)[/COLOR]*Verwerking!D$7:D$18)

(Verwerking!B$7:B$18>=Totaaloverzicht!B18)
Als een cel in het gebied B7 t/m B18 groter dan of gelijk is aan de waarde in cel B18 --> 1, anders 0.

(Verwerking!B$7:B$18<Totaaloverzicht!B19)
Als een cel in het gebied B7 t/m B18 kleiner is dan de waarde in cel B19 --> 1, anders 0.

Vervolgens vermedigvuldig je de cellen(uitkomsten) die bij elkaar horen (zelfde rij nummer). Dus de uitkomsten van de eerste vergelijking(en) * de uitkomsten van de tweede vergelijking(en) * de waardes in de bereik D7 t/m D18.

Hierna neem je van deze uitkomsten de som.

Eigenlijk tel je dus de waardes van D7 t/m D18 op als ze aan beide voowaarden voldoen.

Ik hoop dat het zo duidelijk is.

 

[snb]

Ik krijg de indruk dat de werkbladen qua struktuur nogal overeenkomen.

Heb je al eens naar de methode consolidate gekeken ?

 

[Rheino]

Thanx. Het werkt perfect zo!