Excel uitdagingkje

[verhoog]

Ik heb momenteel een heel zwaar bestand wat ik wil vereenvoudigen. Zo zwaar zelfs dat Excel na input van te veel gegevens stopt met rekenen. Hij slaat niet vast, maar uitkomsten worden simpelweg niet meer weergegeven. Alleen als je de cel actief maakt met F2 en op enter drukt, rekent ie weer door. Mijn eerste vraag is dan ook, hoe kan dit?? (ik heb toch een Centrino 1,5 Ghz met 512 RAM)

Verder wil ik het bestand dus gaan vereenvoudigen. Het betreft een invoerbestand van een enquête die wij bij klanten van ons afnemen. Elke persoon die deelneemt, heeft zijn eigen tabblad waar de uitkomsten ingevoerd kunnen worden. Er zijn nu 212 invoer tabbladen opgenomen, plus een paar bladen voor samenkomst van alle uitkomsten en grafieken. Ja, inderdaad, niet erg slim, maar het bestand is ten tijde van de Pentium I ontwikkeld.

Op ieder invoerblad vul je 82 antwoorden (scores) in van 1 - 5. Op basis van een rekentabel worden dan voor 8 categorieën de scores per categorie per persoon berekend. Deze scores worden daarna dus overgenomen in het totaalscore blad.

Zelf zat ik te denken om gebruik te gaan maken van slechts één invoerblad. Op moment dat er een persoon is ingevoerd, ga je naar het scoreblad en klikt daar op een button die de 8 categoriescores uit het invoerblad kopieert naar het scoreblad middels een simpele macro. Vervolgens maak je het invoerblad leeg (ook met een button) en voer je een nieuwe persoon in. Daarna ga je weer naar het scoreblad en klikt op een andere button 1 regel lager om weer de scores te kopiëren. Allemaal prima, maar dit betekent dat ik 212 buttons moet aanmaken die allemaal min of meer dezelfde macro hebben, op lokatie van de doelcellen na dan (iedere keer een regel lager). Beetje veel werk dus.

Heeft iemand hier een simpelere oplossing voor, dan hoor ik het graag. Wellicht zijn er manieren om de buttons snel door te kopiëren, of kennen mensen nog veel intelligentere oplossingen. Mocht het niet geheel duidelijk zijn, dan kan ik evt wel een deel van het huidige bestand posten. Vast bedankt.

 

[jzeedijk]

Hoi Verhoog,

Is het niet eenvoudiger om slechts gebruik te maken van 3 werkbladen:
- 1 invulformulier voor 82 vragen
- 1 database met circa 212 * 100 regels
- 1 blad met de resultaten

Het werken met 1 database heeft als grote voordeel dat met formules als aantal, somproduct gewerkt kan worden voor de resultaten. Het aantal geenquetteerden kan hiermee uitgebreid worden tot maximaal 650.

Op c1 zet je een teller, het nummer van de geenquetteerde.

In de kolommen H5-L100 komt de invoer.
VBA zet je een code als

Private Sub Worksheet_Change(ByVal Target As Range)
dim i,j as integer
i = Target.Row
Bepaald het rijnummer van de cursor
If not intersect(Target, range("H5:L100")) is nothing
Then
Bepaald of de cursor zich in het bereik H5:L100 bevind.
j=(range("C1").value-1)*100+i-4
Bepaald het rijnummer waar de antwoorden van de geenquetteerde naar toe moeten. Antwoord 1 van geenquetteerde 1 komt hiermee in Rij1
Worksheets(2).cells(j,8) =target.value
Zet het gegeven antwoord op de juiste plaats, in dit geval kolom 8 (H) maar met Target.Column wordt dat dezelfde kolom als waar het antwoord staat op het invulblad. Met Target.Column-5 komt het antwoord in Kolom H in Kolom C van blad 2 terecht.
End If
End Sub

Voor kandidaat 1 wordt het (laatst ingegevoerde) antwoord op vraag 1 op blad2 in cel H1, voor kandidaat 2 zal dit H101 zijn en kandidaat 100 H991
Natuurlijk is het ook mogelijk om de antwoorden exact over te nemen of anderszins.

Ook is het mogelijk om als c1 verandert de resultaten van de kandidaat terug te halen. Ook is het mogelijk om bijvoorbeeld een knop te maken "nieuwe kandidaat".

Jeroen

 

[verhoog]

Ok, ik had er al een beetje op gehoopt dat je met een dergelijke oplossing zou komen Jeroen. Alleen zit er tussen jouw oplossing en mijn begrip nog een flink gat. Ik ben aardig thuis in de formules, maar macro's zijn nog redelijk nieuw en het gebruik van databases in Excel heb ik nog nooit gedaan.

Wat ik (denk ik) mis in jouw oplossing is dat aan de hand van de 82 antwoorden er eerst via een rekentabel 8 scores worden berekend voor categorieën. Dus bijv de vragen 1, 16, 56, 73, en 80 zijn gekoppeld aan categorie 1, en dus worden hun scores bij elkaar opgeteld en getoetst in de tabel onder categorie 1. De tabel geeft dan een totaalscore voor categorie 1 bij het invoerblad. De scores van alle 8 de categorieën moeten in het resultatenblad terechtkomen, per persoon. Niet alle 82 antwoorden dus.

Verder snap ik erg weinig van je macro, wat doet ie eigenlijk? (sorry hoor ). Ik neem aan dat die ervoor zorgt dat de scores van het invoerblad in de dbase terecht komen? Is het dan de bedoeling dat je na iedere invoer de macro runt om de scores over te zetten naar de dbase?

Even uit nieuwsgierigheid: enig idee waarom mijn bestand niet meer doorrekent? Heb jij er ook wel eens last van gehad? En ik vroeg me toch af of het ook mogelijk is in mijn oplossing de macrobuttons snel door te kopiëren?

Alweer bedankt.

 

[jzeedijk]

Hoi Verhoog,

Waarom het werkblad niet meer automatisch doorrekend, ik weet het niet. Ik heb het zelf ook al eens aan de hand gehad en volgens mij geen oplossing gevonden.

Ik denk dat het altijd handig is alle antwoorden op te slaan in de database op blad2 en vervolgens van hieruit de resultaten verwerkt. dat kan overigens eenvoudig, als je achter een vraag zet tot welke categorie de vraag gerekend moet worden. Met somproduct kun je dan per kandidaat of per categorie de score presenteren met de meest vreemde combinaties.

Ikzelf ben helemaal van de macrobuttons af. Met enige regelmaat liep ik tegen tekort systeembronnen aan (weet ik ook geen oplossing voor) en meer narigheid. Zonder knoppen kun je in excel ook alles voor elkaar krijgen.

Wat het macro doet?
Test 'm zelf , in een nieuw werkblad. Zet in C1 een getal Bij voorkeur 1) en daarna in H5:H100 een getal. Kijk vervolgens op blad2 wat er gebeurt. Zet daarn in de kolom ervoor of erna (in ieder geval tussen H en L) een nieuw getal en kijk weer op blad2.

Jeroen

 

[verhoog]

Ok, ik ga m testen. Maar waar plaats ik m, in een module van This Workbook? En misschien een rare vraag, maar moet ik m nog koppelen aan een toescombinatie?

Thx zover.

 

[jzeedijk]

Hoi Verhoog,

In de programmacode van het werkblad (rechtermuisknop klikken op het tabblad en programmacode weergeven kiezen)

Jeroen

 

[verhoog]

Hi Jeroen,

Het gaat de goede kant op, ik zie idd wat ie doet en het zal erg bruikbaar zijn! Nog een paar dingetjes: ik heb zowel in kolom H als in kolom J op blad 1 op dezelfde regel getallen ingevoerd, maar op het tweede blad komt steeds alleen het laatste getal in kolom H terecht, waarom is dat?? Ik bedoel, waarom zou ik zowel in kolom H als J getallen in willen voeren als ze niet allebei zichtbaar worden?

Wanneer ik de macro wil gaan gebruiken, zal ik er nog wel een aantal aanpassingen aan moeten doen. C1 is bijvoorbeeld dan niet de cel waar de teller in komt. Nou is dat nog wel terug te vinden in de code voor een leek als ik, maar als ik grotere aanpassingen wil gaan doen wordt het lastig. Is de code die jij beschrijft te 'ontleden', zodat ik weet wat ieder deel doet? Kan ik dat ergens opzoeken of zo, of kun je het makkelijk beschrijven?

Thx,
Fred

 

[jzeedijk]

Hoi Verhoog,

Uitleg staat bij de programmacode.

Jeroen

 

[verhoog]

Dank je zover Jeroen, als ik meer tijd heb ga ik ermee aan de slag. Eens kijken of ik de code zo kan ombouwen dat alles op de juiste plek terecht gaat komen. Voor nu sluit ik de vraag, maar wellicht kom ik er nog op terug.

:thumb:

Fred

 

[verhoog]

Hi Jeroen,

Ik ben er mee aan het stoeien gegaan, en ik heb inmiddels aardig door hoe de macro werkt. Dacht ik tenminste. Ik heb m zelf aangepast naar eigen wensen, dus bepaalde ranges vervangen en het 'j =' gedeelte aangepast om de target te verplaatsen. Helaas deed ie het niet , dus ik de orginele macro van je weer terug gezet. Probleem is dat die het nu ook niet meer doet. Heel raar, ik heb m weer opnieuw in een leeg blad geplakt, zoals de eerste keer, maar wat ik ook probeer, de data worden niet meer overgenomen. Kan het zijn dat ik iets heb aangepast in Excel waardoor de Macro niet meer zou werken?? Ik kan me nl niet voorstellen dat ik het nu anders heb gedaan dan de vorige keer.

Maar goed, eigenlijk wil ik dat de macro nog net iets anders doet, en wel de ingevoerde waarden transponeren. Dit leest makkelijker. Ik heb een voorbeeldbestandje bijgevoegd, waarin het invoerblad een goede simulatie is van hoe het straks moet worden. Het blad 'resultaat' is uiteraard waar de macro moet komen, nu staan er nog even formules om je te laten zien wat ik bedoel. Vul maar eens een 1 in in A1 van het invoerblad, dan worden daar de uitkomsten berekend en overgenomen op het blad resultaat. Met jouw originele macro is dat vlg mij niet mogelijk, dus is er iets te verzinnen waar dat mee lukt??

Alweer erg bedankt!

Fred

 

[fra]

Geplaatst door verhoog
eigenlijk wil ik dat de macro nog net iets anders doet, en wel de ingevoerde waarden transponeren.

Verhoog, de tekst van zo'n macro is je op 19 maart al letterlijk gegeven. Het moet toch vrij eenvoudig zijn om die aan allerlei soort situaties aan te passen ?
Overigens, als je gegevens naar andere werkbladen wil verplaatsen, dan kun je m.i. er toch beter voor kiezen om daar zelf opdracht voor te geven i.p.v dat via een worksheetevent te laten gebeuren. Dat laatste kan nl. snel aanleiding geven tot het ongewenst kopiëren van gegevens. Maar dat is uiteraard je eigen keuze.

 

[jzeedijk]

Hoi Fred,

Mogelijk ben ik nu de weg kwijt,

Je hebt een invulformulier met 82 vragen met 5 mogelijkheden en circa 200 geenquetteerden.

Horizontaal moet nu komen te staan de vragen met daarbij een code (a-e of 1-5) en verticaal de geenquetteerden?

De onderstaande code moet het doen:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Integer
i = Target.Row
If Not Intersect(Target, Range("H5:H100")) Is Nothing Then
j = Range("C1").Value
Worksheets(2).Cells(j + 7, i - 5 + 2) = Target.Value
End If
End Sub

Waarbij de invulkolom H is, hier moet a-e of 1-5 ingevoerd worden.

Jeroen

 

[verhoog]

Geplaatst door fra
Verhoog, de tekst van zo'n macro is je op 19 maart al letterlijk gegeven. Het moet toch vrij eenvoudig zijn om die aan allerlei soort situaties aan te passen ?

Ten eerste, je kan in de vorige vraag lezen dat het dus bij nu niet werkt.

Ten tweede, de macro zoals Jeroen die gaf zorgde er niet voor dat de waarden getransponeerd werden op de manier zoals ik heb uitgelegd in het voorbeeldbestand.

Ten derde, als het "zo eenvoudig" zou zijn, dan zou ik hier natuurlijk geen vraag stellen. En wat voor sommige mensen eenvoudig is, is voor andere chinees, getuige een heleboel andere vragen hier op het forum! En ik neem aan dat jij het ook eens hebt moeten leren.



Jeroen, ik ga ermee aan de slag. Bedankt weer.

Fred

 

[fra]

Verhoog, door alleen maar op te merken dat "het bij mij niet werkt", daar heeft een lezer niets aan. De vraag is dan natuurlijk wát er niet werkt en wat voor verschillende mogelijkheden je al geprobeerd hebt.
En een bestand als voorbeeld plaatsen waarin (in tegenstelling met wat je schrijft) niets wordt uitgelegd, maar dat alleen maar een horizontaal en vertikaal rijtje getallen bevat zonder een enkele formule en zonder code, daar heeft een lezer ook niks aan en doet toch vermoeden dat je zelf bitter weinig geprobeerd hebt.
En terzijde (n.a.v. je laatste opmerking): mijn kennis van Excel is zeer beperkt, maar door de helpfile goed te bestuderen kun je vaak een heel eind komen.

 

[verhoog]

Afgezien van het feit dat ik niets zie in het oeverloos discussiëren op dit forum over wat is wel of wat zou niet zijn, toch even een laatste reactie:

Het lijkt mij duidelijk wat er bij mij niet werkt en wat ik heb gedaan. Ik heb de macro ingeplakt zoals Jeroen zei en de waarden worden niet overgenomen in blad 2. Meer valt er niet over te zeggen.

Het voorbeeld wat ik heb geplaatst is precies wat het is: een voorbeeld van hoe het blad zou moeten functioneren, alleen moeten de huidige formules worden vervangen door een macro. Dus ik begrijp er niets van als je zegt dat er geen formules in het blad staan, want die staan er wel degelijk. Tevens vraag ik me af waar jouw aanname vandaan komt dat ik bitter weinig zelf heb geprobeerd, daar duidelijk uit mijn antwoord naar voren komt (en getuige de tijd die er tussen twee antwoorden zit) dat ik de macro zelf heb geprobeerd aan te passen naar mijn eigen wensen.

En afgezien van dit alles zie ik geen enkele waarde in het enkel bekritiseren van de vraagsteller op dit forum, anders dan mee te denken over een mogelijke oplossing, of het vragen om uitleg aan de vraagsteller indien er iets niet duidelijk is omtrent zijn of haar probleem of de mogelijkheden die hij of zij reeds heeft geprobeerd.

 

[jzeedijk]

Hoi Fred,

Ten aanzien van je reactie op het commentaar van fra:
Helemaal mee eens.

Fra:


Met betrekking tot mijn tweede macro:
Als je in H5 een waarde of tekst invoert komt ie bij mij keurig uit in werkblad 2. (Invulrange is H5:H100)
Staat de programmacode trouwens in een module (fout) of in de editor van het werkblad (goed)?

Ik heb de code trouwens even uitgebreid zodat je ook oude gegevens weer kunt inlezen:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Integer
i = Target.Row
j = Range("C1").Value

If Not Intersect(Target, Range("H5:H100")) Is Nothing Then
j = Range("C1").Value
Worksheets(2).Cells(j + 7, i - 5 + 2) = Target.Value
End If

If Not Intersect(Target, Range("C1")) Is Nothing Then
If Not Range("K1") = j Then
For i = 5 To 100
Cells(i, 8) = Worksheets(2).Cells(j + 7, i - 5 + 2)
Next i
Range("K1") = j
End If
End If

End Sub

Jeroen