Excel uitdaging

[Raffy]

Beste,

Ik ben bezig om een simpel klein huishoud kasboekje in excel te maken. Ik weet dat je deze ook zo na wat zoeken van internet haalt maar daarmee spijker ik mijn Excel kennis niet mee bij .

Wat ik wil is je voert een aankoop in waarbij de volgende zaken gebruikt worden in de berekening:

• Datum
• Bedrag
• catagory (boodschappen, huur, benzine, ....)

Nu wil ik alles van de maand januari met categorie boodschappen bij elkaar optellen en de uitkomst in kolom F zetten. De boodschappen van februari moeten dan in kolom G enz...

Het volgende heb ik nu in de formule staan:

SOMMEN.ALS('Inkomsten - Uitgaven'!$C$2:$C$999;'Inkomsten - Uitgaven'!$E$2:$E$999;'Maandelijks overzicht'!$A13)

Inkomsten - Uitgaven'!$A$2:$A$999 = Het bereik waar de datum in genoteerd is.
Inkomsten - Uitgaven'!$C$2:$C$999 = Het bereik waar alle bedragen worden ingevuld.
Inkomsten - Uitgaven'!$E$2:$E$999 = Het bereik waar de categorieën ingevuld zijn.

Wie-o-wie kan mij de juiste kant op duwen? Want er zal vast een standaard formule zijn waar dit mee kan, alleen kan ik die even niet vinden

[feelthepower]

Heb je overwogen om een pivottable / draaitabel te gebruiken?

[Mantus]

Dit zal je moeten doen met SUMPRODUCT. Hiermee kan je je voorwaarden per range opgeven en geeft ie het juiste resultaat. Hoe die in het nederlands heeft moet je even googlen.

En anders heb je inderdaad met Pivot's hetzelfde resultaat alleen kan je hier minder mee rommelen.

[Raffy]

feelthepower schreef op dinsdag 08 maart 2016 @ 11:11:
Heb je overwogen om een pivottable / draaitabel te gebruiken?

Een draaitabel heb ik over gedacht maar is niet handig voor het eind resultaat wat ik wil bereiken.

Ik wil straks een overzicht hebben waarin je in de kolommen A, B, C,... de maanden terug ziet komen. In de rijen staan dan de categoriën onderverdeeld zodat je een overzicht krijgt per maand wat je hebt uitgegeven aan bijvoorbeeld boodschappen.

Pivot tabel ken ik nog niet. Ga mijzelf daar even in inlezen. Bedankt voor de tip in ieder geval.

[Yukkie]

Kun je misschien ergens een sheetje plaatsen met je opzet en fake getallen? Desnoods screenshots?

edit: Pivot table == draaitabel, maar dan in het engels

[ Voor 25% gewijzigd door Yukkie op 08-03-2016 11:18 ]

[Amafi]

Draaitabel = Pivottable
Maar als ik het lees is dat inderdaad een van de makkelijkste/beste oplossingen.
Je kan makkelijk spelen met de volgorde, en wat je als row en wat je als column wil gebruiken

[Tazzios]

Raffy schreef op dinsdag 08 maart 2016 @ 11:16:
[...]
Een draaitabel heb ik over gedacht maar is niet handig voor het eind resultaat wat ik wil bereiken.

Ik wil straks een overzicht hebben waarin je in de kolommen A, B, C,... de maanden terug ziet komen. In de rijen staan dan de categoriën onderverdeeld zodat je een overzicht krijgt per maand wat je hebt uitgegeven aan bijvoorbeeld boodschappen.

Pivot tabel ken ik nog niet. Ga mijzelf daar even in inlezen. Bedankt voor de tip in ieder geval.

Dan kan ook prima (en het best) met een draaitabel.

Somproduct kan ook maar waarom allerlei formules knutselen met kans op fouten indien je het met een draaitabel bij elkaar kan slepen? Als je eht een beetje door krijgt kun je ook allerlei andere overzichten maken.

[Raffy]

Yukkie schreef op dinsdag 08 maart 2016 @ 11:17:
Kun je misschien ergens een sheetje plaatsen met je opzet en fake getallen? Desnoods screenshots?

edit: Pivot table == draaitabel, maar dan in het engels

Hierbij het excel bestand zover ik deze nu opgezet heb. De data welke er in vermeld staat is gewoon random vulling.

Excel document.

[Raffy]

Een draaitabel ziet er mooi en fancy uit. Dat is alleen niet wat ik zoek. ik wil de waardes in een overzichtelijke tabel geordend op maand en categorie :-).

[Tazzios]

Dat kan ook.

edit:
In 10 seconden om precies te zijn.

[ Voor 65% gewijzigd door Tazzios op 08-03-2016 11:24 ]

[Highland]

Het belangrijkste is een goede ordening aanbrengen aan je platte data, bij voorkeur aansluitend bij het overzicht zoals je dat van je bank kan downloaden.
Dus bijvoorbeeld alle transacties onder elkaar, en dan de volgende kolomtitels (eenvoudig voorbeeld):
Nr. - transactiedatum- € - bij/af- omschrijving.

Je kan zelf nog wat kolommen toevoegen met eigen vertalingen (maandnamen, categorieën, ...)

Zo maak je er een 'vierkant' van gegevens van. Dit vierkant geef je een dynamische naam, bv Transacties met formule =verschuiving(celnaam;0;0;aantalarg(werkbladnaam!$A:$A);aantalarg(werkbladnaam!$1:$1)).
Dan maak je een draaitabel met als vulling Transacties.
En dan spelen maar met de indeling en draaigrafieken.
Wanneer je nieuwe transacties aanvult, hoef je alleen de draaitabel te vernieuwen om ook de nieuwe transacties weer te geven.

[Mystikal]

In B6 (maandelijks overzicht) zet je deze formule:

=SUMIFS('Inkomsten - Uitgaven'!$C:$C,'Inkomsten - Uitgaven'!$B:$B,'Maandelijkst overzicht'!B$5,'Inkomsten - Uitgaven'!$E:$E,'Maandelijkst overzicht'!$A6)

Vervolgens naar beneden en rechts kopiëren.

Wel even je kosten (of salaris) negatief maken. Anders kloppen de totalen niet.

[ Voor 14% gewijzigd door Mystikal op 08-03-2016 11:34 ]

[Raffy]

Ik heb me even snel ingelezen in een draaitabel en ben tot de conclusie gekomen dat ik tabel soorten door elkaar gehaald heb, Een draaitabel is wel hetgeen ik zoek

Ik ga dit eens proberen te verwerken in hetgeen ik heb en zal daarbij ook de aanbevelingen van Highland meenemen.

Bedankt allemaal voor de snelle reacties en het meedenken!

[Mystikal]

Raffy schreef op dinsdag 08 maart 2016 @ 11:35:
Ik heb me even snel ingelezen in een draaitabel en ben tot de conclusie gekomen dat ik tabel soorten door elkaar gehaald heb, Een draaitabel is wel hetgeen ik zoek :-$

Ik ga dit eens proberen te verwerken in hetgeen ik heb en zal daarbij ook de aanbevelingen van Highland meenemen.

Bedankt allemaal voor de snelle reacties en het meedenken!

Persoonlijk vind ik de oplossing zonder draaitabel beter. Aangezien je wat flexibeler bent in opmaak. Draaitabel vind ik met name interessant bij een grotere hoeveelheid data.

[Raffy]

Mystikal schreef op dinsdag 08 maart 2016 @ 11:37:
[...]

Persoonlijk vind ik de oplossing zonder draaitabel beter. Aangezien je wat flexibeler bent in opmaak. Draaitabel vind ik met name interessant bij een grotere hoeveelheid data.

Ik ga 2 versies maken, 1 met draaitabel en 1 met SUMIFS. Dan kan ik goed testen wat voor mij het prettigst is.

[Tazzios]

Als je in de war was met excel tabellen, die zijn ook handig om:
- Als Draaitabel bron te gebruiken.
- automatisch opmaak en formules bij nieuwe regels.
- overzichtelijker per regel, handige filters.

Maand kolom is trouwens overbodig als je al een datum hebt. (draaitabel functie 'groeperen')

@highland
Dat is een goede! Was me nog niet opgevallen in zijn voorbeeld dat er geen onderscheid werd gemaakt in bij en afboekingen.

[Highland]

@Tazzios: is voor normale mensen ook met +€ of -€ op te lossen, maar in de administratieve wereld zijn -€ vaak inkomsten en +€ kosten. Daarom zetten ze dat vaak apart.

[Yukkie]

Ik denk dat dit is wat je nodig hebt:

code:

=SUMIFS(Table1[Bedrag];Table1[Maand];C$5;Table1[Categorie];$A13)

Zoals je ziet heb ik van je inkomsten/uitgaven tabel ook echt een tabel gemaakt, dat vind ik persoonlijk netter. maar je kunt ook gewoon verwijzen naar kolommen natuurlijk

Ah crap, zie nu pas dat Mystikal mij voor was

[ Voor 6% gewijzigd door Yukkie op 08-03-2016 13:51 ]

[Raffy]

Yukkie schreef op dinsdag 08 maart 2016 @ 13:28:
Ik denk dat dit is wat je nodig hebt:

[afbeelding]

code:

=SUMIFS(Table1[Bedrag];Table1[Maand];C$5;Table1[Categorie];$A13)

Zoals je ziet heb ik van je inkomsten/uitgaven tabel ook echt een tabel gemaakt, dat vind ik persoonlijk netter. maar je kunt ook gewoon verwijzen naar kolomrijen natuurlijk

Ah crap, zie nu pas dat Mystikal mij voor was

Bedankt voor je voorbeeld! Is het niet dat als je gebruik maakt van tabellen over een wat groter bereik dat dan , je bestand in Bytes, veel groter wordt en ook trager?

Heb je dit voorbeeld met tabellen online staan?

[Yukkie]

Nu wel : https://dl.dropboxusercon...4653214/Kasboek_v2-0.xlsx

Ik heb zelf geen ervaring met het trager worden van Excelsheets door het gebruik van structured referals om eerlijk te zijn. Ik denk dat het wel mee zal vallen met je sheetje als je ieder jaar een nieuwe maakt. Het grote voordeel is vind ik dat je echt alleen de cellen pakt waar de data in staat, zodat je eventueel onder je inkomsten tabel een andere tabel kunt zetten als je dat wilt. Is misschien nu niet helemaal relevant, maar ik heb dat bijvoorbeeld wel in mijn workbook waarin ik mijn meterstanden bijhoud.

[Raffy]

Yukkie schreef op dinsdag 08 maart 2016 @ 13:49:
Nu wel : https://dl.dropboxusercon...4653214/Kasboek_v2-0.xlsx

Ik heb zelf geen ervaring met het trager worden van Excelsheets door het gebruik van structured referals om eerlijk te zijn. Ik denk dat het wel mee zal vallen met je sheetje als je ieder jaar een nieuwe maakt. Het grote voordeel is vind ik dat je echt alleen de cellen pakt waar de data in staat, zodat je eventueel onder je inkomsten tabel een andere tabel kunt zetten als je dat wilt. Is misschien nu niet helemaal relevant, maar ik heb dat bijvoorbeeld wel in mijn workbook waarin ik mijn meterstanden bijhoud.

Bedankt!
Ga jou voorbeeld eens uitpluizen

[Yukkie]

Geen probleem. Je kunt het nog toffer maken trouwens door de categorisering ook automatisch te doen. Je kunt dan bijvoorbeeld een csv bestand inlezen van je bank, en op basis van bepaalde velden/omschrijvingen in dat csv bestand een categorie aan een regeltje hangen.

Ik gebruik daarvoor deze formule (Dit is overigens maar een klein deel daarvan):

code:

=IF(OR(ISNUMBER(SEARCH("Albert Heijn";I2));ISNUMBER(SEARCH("Albert Heijn";B2));ISNUMBER(SEARCH("Aldi";B2));ISNUMBER(SEARCH("JUMBO";B2));ISNUMBER(SEARCH("EDEKA";B2));ISNUMBER(SEARCH("hit-markt";B2));ISNUMBER(SEARCH("PLUS";B2));ISNUMBER(SEARCH("DIRK VDBROEK";I2)));"Boodschappen";
IF(OR(ISNUMBER(SEARCH("NL96BNGH0285145355";D2));ISNUMBER(SEARCH("NL57BNGH0285071076";D2)));"Belasting";
IF(OR(ISNUMBER(SEARCH("NL18INGB0005309282";D2));ISNUMBER(SEARCH("NL54INGB0000000503";D2)));"Energie";
"Overig")))

Ik heb ooit uit dit topic Programma om geld mee te beheren een Excelfile geplukt en ben dat verder gaan "ontwikkelen" naar mijn wensen. Mede ook om wat meer kennis op te doen van Excel. (kan het origineel niet meer vinden in dat topic trouwens).

Succes er mee in ieder geval!