[Excel] Het grote Excelvragentopic #45

quote:

Op zondag 10 april 2016 16:54 schreef qu63 het volgende:

[..]

Als de code openbaar gemaakt kan worden zou dat fijn zijn

Ik zal het hem even vragen

quote:

Op zondag 10 april 2016 16:54 schreef qu63 het volgende:

[..]

Als de code openbaar gemaakt kan worden zou dat fijn zijn

https://www.dropbox.com/s(...)ZwHX_KEA3eyewMa?dl=0

IBU Points Template is het bestand dat de lijst uiteindelijk creeërt, ik heb er even de resultaten van twee willekeurige wedstrijden van afgelopen seizoen bij gezet. Die kunnen geïmporteerd worden, juiste jaar invullen, juiste trimester invullen (1 in het geval van de twee bijgevoegde resultaten) en op "Standings" klikken

Degene die het gemaakt heeft zei er bij dat het niet perfect of professioneel is of iets dergelijks, maar voor mij doet het exact wat het moet doen dus dat boeit me niet zo

Sorry dat ik jullie terug stoor.

Ik zou graag hebben, dat als ik een postcode typ in Excel, hij automatisch de gemeente gaat geven. Is daar een goeie tutorial voor?
Het zijn zowel alle Nederlandse als Belgische postcodes (aangezien onze klanten uit beide landen komen).
Uiteraard is het ook zo dat er soms dubbele postcodes zijn waarbij meerdere gemeenten zijn. Het zou leuk zijn mocht er dan een keuzelijstje zijn.

quote:

Op woensdag 13 april 2016 16:00 schreef einzeinz het volgende:
Sorry dat ik jullie terug stoor.

Ik zou graag hebben, dat als ik een postcode typ in Excel, hij automatisch de gemeente gaat geven. Is daar een goeie tutorial voor?
Het zijn zowel alle Nederlandse als Belgische postcodes (aangezien onze klanten uit beide landen komen).
Uiteraard is het ook zo dat er soms dubbele postcodes zijn waarbij meerdere gemeenten zijn. Het zou leuk zijn mocht er dan een keuzelijstje zijn.

Met verticaal zoeken in een tabel waar de postcodes en plaatsnamen in staan?

Beste iedereen,

Ik wil graag een formule om een prognose te maken van het aantal uren dat ik in een jaar met iets bezig ben.Oftewel:

Kolom 1 Kolom 2 Kolom 3
Activiteit 1 1 januari 3 uur
Activiteit 2 7 januari 1 uur
Activiteit 3 10 januari 2 uur

Totaal: 6 uur

Prognose jaartotaal: 6 uur/10 dagen*365 *

Maar dan een formule die ook werkt als ik een 4e activiteit invul.

Alvast bedankt voor het meedenken!

quote:

Op vrijdag 15 april 2016 10:56 schreef superZjozz het volgende:
Beste iedereen,

Ik wil graag een formule om een prognose te maken van het aantal uren dat ik in een jaar met iets bezig ben.Oftewel:

Kolom 1 Kolom 2 Kolom 3
Activiteit 1 1 januari 3 uur
Activiteit 2 7 januari 1 uur
Activiteit 3 10 januari 2 uur

Totaal: 6 uur

Prognose jaartotaal: 6 uur/10 dagen*365 *

Maar dan een formule die ook werkt als ik een 4e activiteit invul.

Alvast bedankt voor het meedenken!

=som(Kolom3)/(max(Kolom2)-min(Kolom2))*365.25
Als de precieze dag nog belangrijk is voor je voorspelling, moet je richting lineaire regressie kijken, maar dan komen er een hoop assupties bij kijken waar je over na moet denken.

-laaamaaaa, al opgelost -

Ik ben bezig met een csv genereren uit een Excel file. Dat lukt helemaal zoals gewenst, op 1 ding na.

De applicatie waarin de csv vervolgens ingelezen wordt, geeft een foutmelding op de laatste regel van de csv en na enig onderzoek blijkt het volgende te gebeuren.

Het opslaan als csv gaat helemaal goed, maar als ik vervolgens de csv in Notepad bekijk, is er altijd een Enter zichtbaar na het laatste item uit de laatste rij, en de applicatie ziet dat blijkbaar als nieuwe regel. Er staat verder geen data in die regel, dus ook geen scheidingstekens o.i.d.

Als ik in Notepad die Enter backspace naar de laatste regel en het bestand weer opsla, pakt de applicatie de csv wel.

De vraag is natuurlijk, hoe kan ik voorkomen dat deze loze Enter toegevoegd wordt. Ik heb al uitgebreid lopen googelen maar kan niks vinden, line breaks e.d. blijken vaker een probleem bij andersom proces (vanuit een csv een excel file genereren).

Verder ook al vanalles geprobeerd in Excel zelf:
-bv alle lege rijen onder mijn data eerst deleten en dan pas opslaan
-idem maar dan ook nog de range selecteren en dan opslaan
-idem maar dan de gegenereerde data eerst naar een nieuw werkblad kopiëren en dan opslaan

helpt allemaal niet. Iemand nog een idee daarover?

En wat als je het niet als csv opslaat maar als txt (delimited)?

Ik vind dat geen loze enter. De applicatie die de csv inleest zou daar maar mee om moeten kunnen gaan.

Zeg nou zelf, wat is logischer:
- elke regel in het bestand wordt afgesloten door een enter, of
- elke regel behalve de laatste wordt afgesloten door een enter?

^^Nu ook een paar varianten mee geprobeerd. Tab delimited txt, txt MSDOS etc. Creëert allemaal min. 1 extra lege regel onderaan.

Ik denk dat het ergens best werkbaar zal zijn voor de gebruiker om deze eenmalige handeling nog in Notepad te doen, omdat we met deze csv sowieso al heel veel tijd gaan winnen, maar ik heb ergens het idee dat ook dit probleem gewoon opgelost zou moeten kunnen worden.

Helaas heb ik geen invloed op hoe de applicatie werkt, die is van een externe partij.

Ik heb een vraagje:

Ik heb 2 kolommen met data (zeg A en B) die niet op alle plekken gevuld zijn. Nu heb ik een IF functie toegepast die me een 1 geeft wanneer A1 en B1 tegelijkertijd een willekeurige waarde in hun cel hebben staan en de functie geeft me een 0 wanneer er in beide of in 1 van beide cellen geen waarde staat, dus: =IF (AND(A1>0;B1>0);1;0).

Deze functie heb ik naar beneden gesleept (zeg in kolom D) en beslaat nu zo'n 2000 rijen met 1-en en 0-len overeenkomstig met de data. Op het einde tel ik alle 1-en op, dus ik neem de som van de hele kolom om zo te zien hoe vaak er een waarde in kolom A en tegelijkertijd in kolom B staat en dat werkt eigenlijk prima. Maar nu vroeg ik me af of het mogelijk is om dit alles in 1 formule te zetten. Zodat ik geen 2000 rijen hoef te beslaan om vervolgens alle cellen op te tellen.

Ik hoop dat de vraag een beetje duidelijk is, als het nodig is kan ik misschien wat meer informatie geven, misschien met screenshots.

Alvast Bedankt!

quote:

Op dinsdag 26 april 2016 13:34 schreef Modus het volgende:
Ik denk dat het ergens best werkbaar zal zijn voor de gebruiker om deze eenmalige handeling nog in Notepad te doen, omdat we met deze csv sowieso al heel veel tijd gaan winnen, maar ik heb ergens het idee dat ook dit probleem gewoon opgelost zou moeten kunnen worden.

Als je een beetje ervaring hebt met vba, dan je hele csv export doen via macro. En dan met stream read/write voorbeeld de text inlezen en dan iets van StrStream = left(StrStream, len(StrStream )-2)
en dan StrStream weer wegschrijven.

(Wel eerst in VBA in -> References -> "Microsoft Scripting RunTime" aanvinken.)

[ Bericht 12% gewijzigd door The_vice op 27-04-2016 12:19:32 (nazorg) ]

quote:

Op woensdag 27 april 2016 11:44 schreef qu63 het volgende:

[..]

SUMIF()

Dankje! Het wil echter nog niet zo vlotten aangezien ik steeds 0 uit krijg op welke manier ik de de functie ook verander.

Is het mogelijk om als "criteria" in de SUMIF functie de functie IF(AND(A1>0;B1>0);1;0) te nemen? Alhoewel, dan kijkt die ook maar naar één cel, dus misschien iets als: IF(AND(A1:A2000>0;B1:B2000>0);1;0). Ben de draad kwijt

quote:

Op woensdag 27 april 2016 12:13 schreef The_vice het volgende:

[..]

Als je een beetje ervaring hebt met vba, dan je hele csv export doen via macro. En dan met stream read/write voorbeeld de text inlezen en dan iets van StrStream = left(StrStream, len(StrStream )-2)
en dan StrStream weer wegschrijven.
[ code verwijderd ]

(Wel eerst in VBA in -> References -> "Microsoft Scripting RunTime" aanvinken.)

Ha interessant. Wat ik tot nu toe heb gaat ook via VBA, en stream gedoe ken ik wel maar gebruik het niet vaak dus ook niet meteen aan gedacht.

Dus wat ik eik moet doen is:
-basis xlsm bestand, daarin de data formatten, data kopiëren naar nieuw leeg bestand en dat opslaan als csv
-vanuit de macro met stream object die csv uitlezen
-vanuit de macro met stream object nieuw bestand aanmaken en de content (min die 2 kar.) daar in wegschrijven en opslaan als nieuw csv.

Moet ik in die laatste stap echt een nieuwe filenaam geven (zodat ik daarna dus 2 csv's heb) of kan ik daarmee die bestaande csv overschrijven? Als dat eerste van toepassing is, dan kan ik denk ik ook met dit streamobject verhaal dat eerste csv bestand (want niet meer nodig) deleten, toch?

quote:

Op woensdag 27 april 2016 14:37 schreef qu63 het volgende:

[..]

Eigenlijk bedoelde ik SUMPRODUCT()

=SOMPRODUCT(--(A1:A2000<>"");--(B1:B2000<>""))

Gelukt! Bedankt!

quote:

Op woensdag 27 april 2016 15:03 schreef Modus het volgende:
[..]

Door in de laatste stap het opnieuw te open (met zelfde filenaam), maar dan middels "ForWrite" wordt alles overschreven met de inhoud van je stream.
(andere mogelijkheden zijn Writeline of Append, maar dat is voor toevoegen)

Verder nog. De netste methode is om direct vanuit je sheet data naar file stream te schrijven.
zelf zou ik dan beginnen om alles in een Array te duwen middels:

Thx, ga er vandeweek mee verder.

Bij "Insert Object from a file" -> Create from file krijgt het icon als standaardcaption het volledige bestandspad. Kan ik die default ergens zetten op puur de bestandsnaam zelf, zodat ik dat niet elke keer handmatig hoef aan te passen?

Excel 2013 Engels.

Is er een manier om ervoor te zorgen dat de data die ik omzet naar een tabel altijd tot de laatste cel in die tabel gaat? Ik heb hier een macro voor maar de tabellen verschillen dus elke keer in het aantal regels.
De complete macro verzamelt verschillende tekst bestanden, laadt deze allemaal onder elkaar in 1 sheet, zet de data om naar kolommen en vervolgens zet ik die kolommen om naar een tabel.
Nu is het zo dat die lijsten elke keer verschillend zijn qua aantal regels. Uit voorzorg selecteer ik tot regel 10.000 voordat ik het omzet naar tabel. Maar daarna maak ik nog weer een overzicht in een pivot, die pivot telt natuurlijk al die lege regels ook mee. Dat kan ik uitzetten in het filter maar ik heb liever dat tijdens het omzetten naar tabel gewoon alle gevulde regels worden omgezet.

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$10000"), , xlYes).Name _
= "Table1"

De range zou ik dus graag aangepast hebben zodat ie elke keer de laatste cel met data van de tabel vindt.

quote:

Op vrijdag 29 april 2016 11:25 schreef Puckzzz het volgende:
Excel 2013 Engels.

Is er een manier om ervoor te zorgen dat de data die ik omzet naar een tabel altijd tot de laatste cel in die tabel gaat? Ik heb hier een macro voor maar de tabellen verschillen dus elke keer in het aantal regels.
De complete macro verzamelt verschillende tekst bestanden, laadt deze allemaal onder elkaar in 1 sheet, zet de data om naar kolommen en vervolgens zet ik die kolommen om naar een tabel.
Nu is het zo dat die lijsten elke keer verschillend zijn qua aantal regels. Uit voorzorg selecteer ik tot regel 10.000 voordat ik het omzet naar tabel. Maar daarna maak ik nog weer een overzicht in een pivot, die pivot telt natuurlijk al die lege regels ook mee. Dat kan ik uitzetten in het filter maar ik heb liever dat tijdens het omzetten naar tabel gewoon alle gevulde regels worden omgezet.

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$10000"), , xlYes).Name _
= "Table1"

De range zou ik dus graag aangepast hebben zodat ie elke keer de laatste cel met data van de tabel vindt.

Na enig zoeken en testen heb ik een code gevonden die werkt:

quote:

ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "Table1"