Een lijst uitbreiden of wijzigen met een gegevensformulier

Database Een lijst uitbreiden of wijzigen met een gegevensformulier Een gegevensformulier is een handige manier om één volledige rij met informatie, oftewel een record, uit een lijst tegelijk in te voeren of weer te geven. Voordat u met een gegevensformulier een record aan een nieuwe lijst kunt toevoegen, moeten boven aan elke kolom in de lijst labels staan. Aan de hand van deze labels worden de velden op het formulier gemaakt. Klik op voor meer informatie over het maken van een lijst op een werkblad. Opmerking Bij gegevensformulieren kunnen maximaal 32 velden tegelijk worden weergegeven.

Records aan een lijst toevoegen met een gegevensformulier 1 2

Klik op een cel in de lijst waaraan u de record wilt toevoegen. Kies Formulier in het menu Data. Demonstratie

3 4

Klik op Nieuw. Typ de informatie voor de nieuwe record.

Druk op TAB als u naar het volgende veld wilt gaan en druk op SHIFT+TAB als u naar het vorige veld wilt gaan. 5 Voeg wanneer u alle gegevens hebt getypt de record toe door op ENTER te drukken. Wanneer u alle records hebt toegevoegd, klikt u op Sluiten om de nieuwe record toe te voegen en het gegevensformulier te sluiten.. Opmerkingen · In velden die formules bevatten worden de resultaten van de formule als een label weergegeven. U kunt de labels niet in het gegevensformulier veranderen. · Als u een record toevoegt die een formule bevat, wordt de formule pas berekend wanneer u de record toevoegt door ENTER te drukken of op Sluiten te klikken. Database-mogelijkheden in Excel © Dehaemers Guido

-1-

· Terwijl u een record toevoegt, kunt u wijzigingen ongedaan maken door op Herstellen te klikken voordat de record toevoegt door op ENTER te drukken of op Sluiten te klikken. · De record wordt toegevoegd wanneer u naar een andere record gaat of het gegevensformulier sluit.

Records in een lijst wijzigen met een gegevensformulier 1 2

Klik op een cel in de lijst die u wilt wijzigen. Kies Formulier in het menu Data.

3

Zoek de record die u wilt wijzigen. Hoe?

4

Verander de informatie in de record.

Druk op TAB als u naar het volgende veld wilt gaan en op SHIFT+TAB als u naar het vorige veld wilt gaan. 5 Wanneer u de gegevens hebt gewijzigd, drukt u op ENTER om de record bij te werken en naar de volgende record te gaan. Wanneer u alle records hebt gewijzigd, klikt u op Sluiten om de weergegeven records bij te werken en het gegevensformulier te sluiten. Opmerkingen · ·

·

·

In velden die formules bevatten, worden de resultaten van die formules als een label weergegeven. U kunt de niet in het gegevensformulier veranderen. Als u een record verandert die een formule bevat, wordt de formule pas berekend wanneer de record wijzigt door op ENTER te drukken of op Sluiten te klikken. Terwijl u een record verandert, kunt u wijzigingen ongedaan maken door op Herstellen te klikken zolang de record de actieve record in het gegevensformulier is. De record wordt gewijzigd wanneer u naar een andere record gaat of het gegevensformulier sluit.

Database-mogelijkheden in Excel © Dehaemers Guido

-2-

Lijsten sorteren U kunt de rijen of kolommen in een lijst rangschikken op basis van de waarden in de lijst door deze te sorteren. Bij het sorteren worden rijen, kolommen of afzonderlijke cellen gerangschikt volgens de door u opgegeven sorteervolgorde. U kunt lijsten sorteren in oplopende volgorde (1 tot en met 9, A tot en met Z) of in aflopende volgorde (9 tot en met 1, Z tot en met A). U kunt de sorteerbewerking uitvoeren op basis van de inhoud van een of van meer kolommen. Standaard worden lijsten in Microsoft Excel alfabetisch gerangschikt. Als u maanden en weekdagen wilt sorteren volgens de kalendervolgorde in plaats van de alfabetische volgorde, gebruikt u een aangepaste sorteervolgorde. U kunt ook lijsten in een bepaalde volgorde plaatsen aan de hand van een aangepaste sorteervolgorde. Als u bijvoorbeeld een lijst hebt waarin een kolom met de waarden ‘Laag’, ‘Midden’ of ‘Hoog’ voorkomt, kunt u een sorteervolgorde opgeven die zorgt dat de rijen met de waarde ‘Laag’ eerst worden weergegeven, vervolgens de rijen met ‘Midden’ en ten slotte de rijen met Hoog’.

Rijen oplopend sorteren op basis van de inhoud van één kolom Als u al eerder een sorteerbewerking in het werkblad hebt uitgevoerd, worden dezelfde sorteeropties gehanteerd, tenzij u deze wijzigt. 1 2

Klik op een cel in de kolom waarop u wilt sorteren. Klik op Oplopend .

Opmerking In een draaitabel worden de items op basis van het geselecteerde veld in oplopende alfabetische volgorde gesorteerd. Getallen worden van laag naar hoog gesorteerd.

Rijen aflopend sorteren op basis van de inhoud van één kolom Als u al eerder een sorteerbewerking in het werkblad hebt uitgevoerd, worden dezelfde sorteeropties gehanteerd, tenzij u deze wijzigt. 1 2

Klik op een cel in de kolom waarop u wilt sorteren. Klik op Aflopend .

Opmerking In een draaitabel worden de items op basis van het geselecteerde veld in aflopende alfabetische volgorde gesorteerd. Getallen worden van hoog naar laag gesorteerd.


-3-

Rijen sorteren op basis van de inhoud van twee of meer kolommen U bereikt het beste resultaat als u kolomlabels in uw lijst opneemt. 1 Klik op een cel in de lijst die u wilt sorteren. 2 Klik op Sorteren in het menu Data. 3 Klik in de vakken Sorteren op en Vervolgens op op de kolommen die u wilt sorteren. Als u sorteert op meer dan drie kolommen, moet u eerst op de minst belangrijke kolommen sorteren. Als uw lijst bijvoorbeeld gegevens van werknemers bevat en u wilt deze rangschikken op Afdeling, Functie, Achternaam en Voornaam, moet u de lijst twee maal sorteren. Eerst klikt u op Voornaam in het vak Sorteren op en sorteert u de lijst. Daarna klikt u op Afdeling in het vak Sorteren op, vervolgens op Functie in het eerste vak Vervolgens op en ten slotte op Achternaam in het tweede vak Vervolgens op, waarna u de lijst opnieuw sorteert. 4

Selecteer desgewenst nog meer sorteeropties en klik vervolgens op OK.

Herhaal indien nodig stap 2 tot en met 4 en gebruik daarbij de kolommen die dan het belangrijkst zijn. Opmerkingen · Als de kolom die u in het vak Sorteren op hebt opgegeven dubbele items bevat, kunt u de waarden verder sorteren door nog een kolom op te geven in het eerste vak Vervolgens op. Als de tweede kolom dubbele items bevat, kunt u een derde kolom opgeven om op te sorteren in het tweede vak Vervolgens op. · Als u rijen sorteert die deel uitmaken van een werkbladoverzicht, worden de groepen van het hoogste niveau (niveau 1) zodanig gesorteerd dat de rijen kolomdetails bij elkaar blijven, zelfs als deze verborgen zijn.

Kolommen sorteren op basis van de inhoud van rijen 1 2 3 4 OK. 5

Klik op een cel in de lijst die u wilt sorteren. Klik op Sorteren in het menu Data. Klik op Opties. Klik onder Richting op Van links naar rechts sorteren en klik vervolgens op Klik in de vakken Sorteren op en Vervolgens op op de rijen die u wilt sorteren.


-4-

Sorteren op maanden, dagen van de week of aangepaste lijsten 1 Selecteer een cel of cellenbereik in de lijst die u wilt sorteren. 2 Klik op Sorteren in het menu Data. 3 Klik op Opties. 4 Klik onder Sorteervolgorde voor 1e sleutel op de aangepaste sorteervolgorde die u wilt gebruiken en klik vervolgens op OK. 5 Klik desgewenst op andere sorteeropties. Opmerkingen ·

De aangepaste sorteervolgorde is alleen van toepassing op de kolom die in het vak Sorteren op is opgegeven. Als u meerdere kolommen op basis van een aangepaste sorteervolgorde wilt sorteren, moet u iedere kolom afzonderlijk sorteren. Als u bijvoorbeeld op kolom A en B wilt sorteren, in die volgorde, sorteert u eerst op kolom B en geeft u vervolgens de aangepaste sorteervolgorde op in het dialoogvenster Sorteeropties. Daarna sorteert u de lijst op kolom A.

·

Als u een lijst in een bepaalde volgorde wilt sorteren (bijvoorbeeld bedrijfsgegevens), kunt u een aangepaste lijst voor de aangepaste sorteervolgorde gebruiken.

Gegevens in een lijst zoeken Microsoft Excel kent verschillende manieren waarop u gegevens in een lijst kunt analyseren. Met de opdracht AutoFilter kunt u een lijst filteren als u de rijen wilt zien die aan bepaalde criteria voldoen. Als u een waarde in een lijst wilt zoeken met behulp van een andere waarde in de lijst, bijvoorbeeld de prijs van een product in een lijst met producten en prijzen, gebruikt u de wizard Opzoeken. Wat wilt u doen? Subsets van rijen in een lijst weergeven door middel van filters Met de wizard Opzoeken waarden zoeken in een lijst

Subsets van rijen in een lijst weergeven door middel van filters U kunt op niet meer dan een lijst in een werkblad tegelijk filters toepassen. 1 2 3

Klik op een cel in de lijst die u wilt filteren. Wijs Filter in het menu Data aan en klik vervolgens op AutoFilter. Klik op de pijl in de kolom met de gegevens die u wilt weergeven als u alleen de rijen met een bepaalde waarde wilt weergeven. Database-mogelijkheden in Excel

© Dehaemers Guido

-5-

4 5

Klik op de gewenste waarde. Als u een extra voorwaarde wilt toepassen op basis van een waarde in een andere kolom, herhaalt u stap 3 en 4 in de andere kolom.

Als u de lijst wilt filteren op twee waarden in dezelfde kolom of als u andere vergelijkingsoperatoren wilt toevoegen dan Is gelijk aan, klikt u op de pijl in de kolom en klikt u vervolgens op Aangepast. Klik op voor meer informatie over het weergeven van rijen door vergelijking van waarden. Opmerkingen ·

Als u een kolom filtert, zijn de enige filters die voor andere kolommen beschikbaar zijn de waarden die in de gefilterde lijst worden weergegeven. Met AutoFilter kunt u maximaal twee voorwaarden op een kolom toepassen. Als u drie of meer voorwaarden wilt toepassen, berekende waarden als criteria wilt gebruiken of records naar een andere locatie wilt kopiëren, kunt u Uitgebreid filter gebruiken. Klik op voor meer informatie over uitgebreide filters.

·

Met de wizard Opzoeken waarden zoeken in een lijst U kunt een formule maken die een waarde bepaalt aan de hand van de rijen kolomlabels in een lijst. Als u bijvoorbeeld een voorraadlijst hebt met artikelnummers, beschrijvingen en prijzen, kunt u een formule maken die de beschrijving of de prijs van een artikel bepaalt door een bepaald artikelnummer te zoeken. De wizard Opzoeken helpt u bij het schrijven van de juiste formule. De wizard Opzoeken is een invoegtoepassing. Als de opdracht Opzoeken niet in het vervolgmenu Wizard van het menu Extra staat, moet u de invoegtoepassing installeren en laden. 1 2 3

Klik op een cel in de lijst. Wijs in het menu Extra naar Wizard en klik op Opzoeken. Volg de instructies in de wizard.

LINKS Geeft als resultaat de eerste, ofwel de meest linkse tekens in een tekenreeks. Syntaxis LINKS(tekst;aantal-tekens)


-6-

tekst is de tekenreeks met de tekens die u wilt ophalen. aantal-tekens is het aantal tekens dat u uit tekst wilt ophalen. · aantal-tekens moet groter dan of gelijk aan 0 zijn. · Als aantal-tekens groter is dan het aantal tekens in de opgegeven tekenreeks, geeft LINKS de gehele tekenreeks weer. · Als u aantal-tekens weglaat, wordt uitgegaan van de waarde 1.

Voorbeelden LINKS("Verkoopprijs"; 4) resulteert in "Verk" Als de cel A1 de tekst "Zweden" bevat, geldt het volgende: LINKS(A1) resulteert in "Z" Sommige boekhoudprogramma’s geven negatieve waarden weer met een minteken (-) rechts van de waarde. Als u een bestand importeert waarin negatieve waarden op deze manier zijnopgeslagen, is het mogelijk dat Microsoft Excel deze waarden als tekst importeert. U kunt deze tekenreeksen converteren naar waarden. De conversie moet als resultaat alle tekens van de tekenreeks opleveren, met uitzondering van het meest rechtse teken ( het minteken). Vervolgens vermenigvuldigt u het resultaat met -1. Gebruik voor het argument aantal tekens de werkbladfunctie LENGTE om het aantal tekens in de tekenreeks te berekenen,.en trek er vervolgens 1 af. Als de waarde in cel A2 bijvoorbeeld 156- is, converteert de volgende formule de tekst naar de waarde -156. LINKS(A2,LENGTE(A2)–1)*–1

TEKST.SAMENVOEGEN Voegt verschillende tekenreeksen samen tot één tekenreeks. Syntaxis TEKST.SAMENVOEGEN (tekst1;tekst2; ...)

tekst1; tekst2;... zijn maximaal 30 tekstfragmenten die u tot één tekstfragment samenvoegt. De tekstfragmenten kunnen tekenreeksen, getallen of verwijzingen naar één bepaalde cel zijn. Opmerking Database-mogelijkheden in Excel © Dehaemers Guido

-7-

In plaats van de functie TEKST.SAMENVOEGEN kunt u ook het bewerkingsteken "&" gebruiken om tekstfragmenten samen te voegen. Voorbeelden TEKST.SAMENVOEGEN("totaal";"waarde") resulteert in "totaalwaarde". U krijgt hetzelfde resultaat als u het volgende typt: "totaal"&" "&"waarde" . Stel dat cel C2 van een werkblad met een visbestand "soort" bevat, C5 "bronforel" en C8 het totaal 32. In dat geval geldt het volgende: TEKST.SAMENVOEGEN("Vispopulatie voor de";C2;" ";C5;" is ";C8;"/kilometer") resulteert in "Vispopulatie voor de soort bronforel is 32/kilometer"

Standaardsorteervolgorden In Microsoft Excel worden specifieke sorteervolgorden gebruikt voor het rangschikken van gegevens op waarde. De gegevens worden niet gerangschikt op opmaak. Als u tekst sorteert, wordt in Microsoft Excel van links naar rechts gesorteerd, teken voor teken. Als een cel bijvoorbeeld de tekst ‘A100’ bevat, komt deze cel na de sorteerbewerking na een cel met de inhoud ‘A1’ en vóór een cel met de inhoud ‘A11’. Bij een oplopende sorteervolgorde wordt de volgende volgorde gehanteerd (bij een aflopende sorteervolgorde wordt deze volgorde omgedraaid, met uitzondering van lege cellen die altijd als laatste worden gesorteerd): · Getallen worden gerangschikt van het kleinste negatieve getal tot het grootste positieve getal. · Tekst, en tekst met getallen, wordt in deze volgorde gerangschikt: 0 1 2 3 4 5 6 7 8 9 ‘ - (spatie) ! “ # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C DEFGHIJKLMNOPQRSTUVWXYZ. · · ·

De logische waarde ONWAAR komt voor WAAR Alle foutwaarden zijn gelijk. Lege waarden worden altijd achteraan geplaatst.

SUBTOTAAL

Geeft als resultaat een subtotaal in een lijst of een database. Over het algemeen is het makkelijker een lijst met subtotalen te maken met de opdracht Subtotalen (menu Database-mogelijkheden in Excel © Dehaemers Guido

-8-

Data) Als u een subtotaallijst hebt gemaakt, kunt u deze wijzigen door de functie SUBTOTAAL te bewerken. Syntaxis SUBTOTAAL(functie_getal;verw1,...)

functie_getal is een getal van 1 tot 11 dat aangeeft welke functie moet worden gebruikt voor de subtotaalberekening in een lijst.

Functie_getal 1 2 3 4 5 6 7 8 9 10 11

Functie

GEMIDDELDE AANTAL AANTALARG MAX MIN PRODUCT STDEV STDEVP SOM VAR VARP

verw1 is een bereik of verwijzing waarvan u het subtotaal wilt bepalen. Opmerkingen ·

· ·

Als in verw1, verw2 … andere subtotalen, of geneste subtotalen, voorkomen, worden deze genegeerd, zodat ze niet twee keer worden geteld. SUBTOTAAL negeert verborgen rijen. Dit is van belang als u alleen een subtotaal wilt maken van de zichtbare gegevens in een gefilterde lijst. Als één van de verwijzingen een 3D-verwijzing is, geeft SUBTOTAAL de foutwaarde #WAARDE! als resultaat.

Voorbeeld SUBTOTAAL(9;C3:C5) resulteert in een subtotaal van de cellen C3:C5 uitgaande van de functie SOM.


-9-

Databasefuncties In dit gedeelte vindt u beschrijvingen van de twaalf werkbladfuncties die worden gebruikt voor berekeningen in databases of lijsten van Microsoft Excel. Elk van deze zogenoemde Db-functies gebruikt drie argumenten: database, veld en criteria. Deze argumenten verwijzen naar de werkbladbereiken die in de databasefunctie worden gebruikt. Syntaxis Db-functie(database;veld;criteria)

Database is het cellenbereik dat de database omvat. · In Microsoft Excel is een database een lijst met gerelateerde gegevens waarbij rijen met gerelateerde informatie de records vormen en de kolommen met gegevens de velden zijn. De eerste rij in de lijst bevat de labels voor elke kolom. De verwijzing kan worden ingevoerd als een cellenbereik of als een naam die het bereik vertegenwoordigt dat de lijst bevat. Klik op voor richtlijnen bij het maken van lijsten op werkbladen. · Voor alle databasefuncties geldt dat wanneer de databaseverwijzing betrekking heeft op een cel in een draaitabel, de berekening uitsluitend op de draaitabelgegevens wordt uitgevoerd. · Als u subtotalen in de lijst wilt berekenen, kiest u Subtotalen in het menu Data om subtotalen in te voegen. Klik op voor informatie over automatische subtotalen.

Veld geeft aan welke kolom in de functie wordt gebruikt. Gegevenskolommen in de lijst hebben een label in de eerste rij. Het veld kan worden ingevoerd als tekst waarbij de kolomlabel tussen dubbele aanhalingstekens staat, zoals "Leeftijd" of "Oogst" in de onderstaande databasetabel, of als een nummer dat de positie in de kolom in de lijst aangeeft: 1 voor de eerste kolom (Boom in het voorbeeld), 2 voor de de tweede kolom (Hoogte), enzovoort. Criteria is het cellenbereik met de voorwaarden voor de functie. De functie haalt informatie uit de lijst die overeenkomt met de voorwaarden die in het criteriabereik zijn opgegeven. Het criteriabereik bevat een kopie van de kolomlabel in de lijst voor de kolom die u met de functie wilt samenvatten. De criteriaverwijzing kan worden ingevoerd als een cellenbereik, zoals A1:F2 in de onderstaande databasetabel, of als een naam die aan het bereik is toegekend, zoals "Criteria". Klik op voor meer voorbeelden van voorwaarden die u als criteria kunt opgeven. Tips Database-mogelijkheden in Excel © Dehaemers Guido

- 10 -

·

U kunt elk bereik gebruiken als criteria, mits dit bereik ten minste één kolomlabel bevat en ten minste één cel onder de kolomlabel waarin de voorwaarde kan worden opgegeven.

Als het bereik G1:G2 bijvoorbeeld de veldkop Salaris in G1 bevat en het bedrag 10.000 in G2, kunt u het bereik definiëren als VergelijkSalaris en deze naam vervolgens gebruiken als het criteria-argument in uw databasefuncties. ·

· ·

Het criteriabereik kan zich overal op het werkblad bevinden. Plaats het echter nooit onder de lijst. Als u met de opdracht Formulier in het menu Data meer informatie aan de lijst toevoegt, wordt de nieuwe informatie aan de eerste rij onder de lijst toegevoegd. Als de rij onder de lijst niet leeg is, kan Microsoft Excel de nieuwe informatie niet toevoegen. Zorg ervoor dat het criteriabereik de lijst niet overlapt. Als u een bewerking op een gehele kolom in een database wilt uitvoeren, voegt u een witregel onder de kolomlabels in het criteriabereik in.

Voorbeelden In de volgende figuur ziet u een voorbeeld van een database van een kleine boomgaard. Elke rij bevat informatie over één boom. Het bereik A5:E11 is Database genoemd en het bereik A1:F3 is Criteria genoemd.

DBAANTAL(Database;"Leeftijd";A1:F2) resulteert in 1. Deze functie onderzoekt de records van appelbomen met een hoogte van 10 tot 16 meter en telt hoeveel velden in de kolom Leeftijd getallen bevatten. DBAANTALA(Database;"Winst";A1:F2) resulteert in 1. Deze functie onderzoekt de records van appelbomen met een hoogte van 10 tot 16 meter en telt het aantal nietlege velden in de kolom Winst. Database-mogelijkheden in Excel © Dehaemers Guido

- 11 -

DMAX(Database;"Winst";A1:A3) resulteert in fl 105,00, de maximale opbrengst van de appel- en perenbomen. DMIN(Database;"Winst";A1:B2) resulteert in fl 75,00, de minimale winst uit de appelbomen hoger dan 10 meter. DBSOM(Database;"Winst";A1:A2) resulteert in fl 225,00, de totale winst uit de appelbomen. DBSOM(Database;"Winst";A1:F2) resulteert in fl 75,00, de totale winst uit de appelbomen met een hoogte tussen 10 en 16 meter. DBPRODUCT(Database;"Oogst";A1:F2) resulteert in 140, het product van de oogsten van appelbomen met een hoogte tussen 10 en 16 meter. DBGEMIDDELDE(Database;"Oogst";A1:B2) resulteert in 12, de gemiddelde oogst van appelbomen met een hoogte van meer dan 10 meter. DBGEMIDDELDE(Database;3;Database) resulteert in 13, de gemiddelde leeftijd van alle bomen in de database. DBSTDEV(Database;"Oogst";A1:A3) resulteert in 2,97, de geschatte standaarddeviatie in de oogst van de appel- en perenbomen als de gegevens in de database slechts een steekproef zijn van de totale boomgaardpopulatie. DBSTDEVP(Database;"Oogst";A1:A3) resulteert in 2,65, de werkelijke standaarddeviatie in de oogst van de appel- en perenbomen als de gegevens in de database de gehele populatie omvatten. DBVAR(Database;"Oogst",A1:A3) resulteert in 8,8, de geschatte variantie in de oogst van de appel- en perenbomen als de gegevens in de database slechts een steekproef zijn van de totale boomgaardpopulatie. DBVARP(Database;"Oogst";A1:A3) resulteert in 7,04, de werkelijke variantie in de oogst van appel- en perenbomen als de gegevens in de database de gehele boomgaardpopulatie omvatten. DBLEZEN(Database;"Oogst";Criteria) resulteert in de foutwaarde #GETAL! omdat meer dan één record aan de criteria voldoet.

DBSOM Telt in een database de getallen op uit de kolom veld van records die aan de opgegeven criteria voldoen.


- 12 -

Syntaxis DBSOM(database;veld;criteria)

Database is het cellenbereik dat de database omvat. Een database is een lijst met gerelateerde informatie waarin rijen met gerelateerde informatie de records vormen en kolommen met gegevens de velden zijn. De eerste rij in de lijst bevat labels voor elke kolom. Veld geeft aan welke kolom in de functie wordt gebruikt. U kunt het argument veld als tekst opgeven waarbij de kolomlabel tussen dubbele aanhalingstekens staat, zoals "Leeftijd" of "Oogst", of als een getal dat de positie van de kolom in de lijst aangeeft: 1 voor de eerste kolom, 2 voor de tweede kolom, enzovoort. Criteria is het cellenbereik dat de opgegeven criteria bevat. U kunt voor het argument criteria elk bereik opgeven, mits dit bereik ten minste één kolomlabel bevat en ten minste één cel onder de kolomlabel waarin de voorwaarde kan worden opgegeven.

DBAANTAL Telt in een lijst of database het aantal cellen dat getallen bevat die aan de criteria voldoen. Het argument veld is optioneel. Als u veld weglaat, telt DBAANTAL alle records in de database die aan de criteria voldoen. Syntaxis DBAANTAL(database;veld;criteria)

Database is het cellenbereik dat de database omvat. Een database is een lijst met gerelateerde informatie waarin rijen met gerelateerde informatie de records vormen en kolommen met gegevens de velden zijn. De eerste rij in de lijst bevat labels voor elke kolom. Veld geeft aan welke kolom in de functie wordt gebruikt. U kunt het argument veld als tekst opgeven waarbij de kolomlabel tussen dubbele aanhalingstekens staat, zoals "Leeftijd" of "Oogst", of als een getal dat de positie van de kolom in de lijst aangeeft: 1 voor de eerste kolom, 2 voor de tweede kolom, enzovoort. Criteria is het cellenbereik dat de opgegeven criteria bevat. U kunt voor het argument criteria elk bereik opgeven, mits dit bereik ten minste één kolomlabel bevat en ten minste één cel onder de kolomlabel waarin de voorwaarde kan worden opgegeven. Database-mogelijkheden in Excel © Dehaemers Guido

- 13 -

JAAR

Converteert serieel-getal naar een jaar. Het jaar wordt weergegeven als een geheel getal tussen 1900 en 9999. Syntaxis JAAR(serieel-getal)

serieel-getal is een getal waarmee een dag of tijd wordt aangegeven binnen het datumsysteem dat Microsoft Excel gebruikt voor datum- en tijdberekeningen. U kunt serieel-getal ook als tekst opgeven, zoals "15-apr-1993" of "15-04-93", in plaats van als getal. De tekst wordt automatisch geconverteerd naar een serieel getal. Zie NU voor meer informatie over serieel-getal. Opmerking Microsoft Excel voor Windows en Microsoft Excel voor de Macintosh gebruiken verschillende standaarddatumsystemen. Zie NU voor meer informatie. Voorbeelden JAAR("5-7-90") resulteert in 1990 Als u het datumsysteem 1900 gebruikt (standaard in Microsoft Excel voor Windows), geldt het volgende: JAAR(0,007) resulteert in 1900 JAAR(29747,007) resulteert in 1981 Als u het datumsysteem 1904 gebruikt (standaard in Microsoft Excel voor de Macintosh), geldt het volgende: JAAR(0,007) resulteert in 1904 JAAR(29747,007) resulteert in 1985


- 14 -

MAAND Converteert serieel-getal naar een maand. De maand wordt weergegeven als een geheel getal tussen 1 (januari) en 12 (december). Syntaxis MAAND(serieel-getal)

serieel-getal is een getal waarmee een dag of tijd wordt aangegeven binnen het datumsysteem dat Microsoft Excel gebruikt voor datum- en tijdberekeningen. U kunt serieel-getal ook als tekst opgeven, zoals "15-apr-1993" of "15-4-93", in plaats van als getal. De tekst wordt automatisch geconverteerd naar een serieel getal. Zie NU voor meer informatie over serieel-getal. Opmerking Microsoft Excel voor Windows en Microsoft Excel voor de Macintosh gebruiken verschillende standaarddatumsystemen. Zie NU voor meer informatie. Voorbeelden MAAND("6-mei") resulteert in 5 MAAND(366) resulteert in 12 MAAND(367) resulteert in 1

DATUM Converteert een bepaalde datum naar een serieel getal. Syntaxis DATUM(jaar;maand;dag)

jaar is een getal tussen 1900 en 9999 in Microsoft Excel voor Windows of tussen 1904 en 9999 in Microsoft Excel voor de Macintosh. maand is een getal dat de maand van het jaar aangeeft. Als maand groter is dan 12, telt DATUM dat aantal maanden op bij de eerste maand in het opgegeven jaar. Database-mogelijkheden in Excel © Dehaemers Guido

- 15 -

DATUM(90;14;2) bijvoorbeeld resulteert in het seriële getal dat staat voor 2 februari 1991. dag is een getal dat de dag van de maand aangeeft. Als dag groter is dan het aantal dagen in de opgegeven maand, telt DATUM dat aantal dagen op bij de eerste dag in de opgegeven maand. DATUM(91;1;35) bijvoorbeeld resulteert in het seriële getal dat staat voor 4 februari 1991. Opmerkingen · Microsoft Excel voor Windows en Microsoft Excel voor de Macintosh gebruiken verschillende standaarddatumsystemen. · De functie DATUM is bijzonder handig in formules waarbij jaar, maand en dag formules zijn en geen constanten.

Voorbeelden Als u het datumsysteem 1900 gebruikt (standaard in Microsoft Excel voor Windows), resulteert DATUM(91; 1; 1) in 33239, het seriële getal voor 1 januari 1991. Als u het datumsysteem 1904 gebruikt (standaard in Microsoft Excel voor de Macintosh), resulteert DATUM(91; 1; 1) in 31777, het seriële getal voor 1 januari 1991.

HW Berekent de huidige waarde van een investering. De huidige waarde is het totale bedrag dat een reeks toekomstige betalingen op dit moment waard is. Als u bijvoorbeeld geld leent, is het geleende bedrag de huidige waarde voor de verstrekker van de lening. Syntaxis HW(rente;aantal-termijnen;bet;tw;type_getal)

rente is het rentepercentage per termijn. Als u bijvoorbeeld een lening afsluit voor de aanschaf van een auto tegen 10 procent jaarrente en die lening maandelijks aflost, bedraagt het maandelijkse rentepercentage 10%/12, ofwel 0,83%. Als rente voert u dan 10%/12, 0,83% of 0,0083 in de formule in. aantal-termijnen is het totale aantal termijnen in een annuïteit. Als u bijvoorbeeld een lening met een looptijd van vier jaar afsluit voor de aanschaf van een auto en Database-mogelijkheden in Excel © Dehaemers Guido

- 16 -

maandelijkse aflossingen verricht, beslaat uw lening 4*12 (ofwel 48) termijnen. Voor aantal-termijnen voert u dan 48 in de formule in. bet is de betaling die elke termijn wordt verricht. Dit bedrag kan gedurende de looptijd van de annuïteit niet worden gewijzigd. Normaal gesproken omvat bet de hoofdsom en de rente, maar geen andere verschuldigde bedragen. De maandelijkse aflossingen op een vierjarige lening van BEF 10.000 voor de aanschaf van een auto tegen 12 procent rente bedragen bijvoorbeeld BEF 263,33. Voor bet voert u dan 263,33- in de formule in. tw is de toekomstige waarde, ofwel het saldo dat u wilt bereiken als de laatste betaling is verricht. Als u tw weglaat, wordt van de waarde 0 uitgegaan (de toekomstige waarde van bijvoorbeeld een lening is 0). Als u bijvoorbeeld over achttien jaar over fl 50.000 wilt beschikken, is de toekomstige waarde BEF 50.000. U kunt dan een voorzichtige schatting van het rentepercentage maken en vaststellen hoeveel u elke maand opzij moet leggen. type_getal is het getal 0 of 1 en geeft aan wanneer betalingen voldaan moeten worden. Geef voor type_getal op Als betalingen voldaan moeten worden Als betalingen voldaan moeten worden

0 of niets

1

Aan het einde van de termijn

Aan het begin van de termijn

Opmerkingen · Wees consequent in het gebruik van de eenheden waarmee u rente en aantaltermijnen opgeeft. Als u maandelijkse aflossingen verricht op een lening met een looptijd van vier jaar tegen een jaarrente van 12 procent, gebruikt u 12%/12 voor rente en 4*12 voor aantal-termijnen. Als u jaarlijkse aflossingen verricht op dezelfde lening, gebruikt u 12% voor rente en 4 voor aantal-termijnen. · De volgende functies hebben betrekking op annuïteiten: CUM.RENTE PBET CUM,HOOFDSOM HW TW RENTE TOEK.WAARDE2 IR.SCHEMA IBET NHW2 BET Een annuïteit is een reeks vaste betalingen die worden verricht over een aaneensluitende periode. Een lening voor een auto en een hypotheek zijn bijvoorbeeld annuïteiten. Zie de beschrijving bij iedere annuïteitenfunctie voor meer informatie.


- 17 -

· In annuïteitenfuncties worden bedragen die u betaalt, zoals stortingen op een spaarrekening, weergegeven als negatieve getallen. Betalingen die aan u worden verricht, zoals een dividenduitkering, worden weergegeven als positieve getallen. Een storting van BEF 1.000 op de bank wordt bijvoorbeeld aangeduid met het argument -1000 als u de storting verricht, en met het argument 1000 als u de bank bent. · Bij financiële berekeningen drukt Microsoft Excel het ene argument uit in de andere. Als rente niet gelijk is aan 0, geldt het volgende:

Als rente gelijk is aan 0, geldt het volgende: (bet * aantal-termijnen) + hw + tw = 0

Voorbeeld Stel dat u de aankoop van een verzekeringsannuïteit overweegt die de komende twintig jaar BEF 500 aan het eind van iedere maand moet uitkeren. De kosten van de annuïteit zijn BEF 60.000 en het geld dat wordt uitgekeerd levert 8 procent rente op. U wilt bekijken of dit een verstandige investering is. Met de functie HW berekent u de huidige waarde van de annuïteit als volgt: HW(0,08/12; 12*20; 500; ; 0) resulteert in BEF 59.777,15Het resultaat is negatief omdat dit een bedrag is dat u moet betalen: een uitgaande cashflow. De huidige waarde van de annuïteit (BEF 59.777,15) is minder dan wat u nu zou moeten betalen (BEF 60.000). Op grond van deze uitkomst komt u tot de conclusie dat deze investering niet lucratief is.

AFRONDEN Rondt een getal af op het opgegeven aantal decimalen. Syntaxis AFRONDEN(getal;aantal-decimalen)

getal is het getal dat u wilt afronden. aantal-decimalen geeft het aantal decimalen aan waarop u getal wilt afronden.


- 18 -

· Als aantal-decimalen groter dan 0 (nul) is, wordt getal afgerond op het opgegeven aantal decimalen. · Als aantal-decimalen gelijk aan 0 is, wordt getal afgerond op het dichtstbijzijnde gehele getal. · Als aantal-decimalen kleiner dan 0 is, wordt getal afgerond op tientallen (-1), honderdtallen (-2), enz.

Voorbeelden AFRONDEN(2,15; 1) resulteert in 2,2 AFRONDEN(2,149; 1) resulteert in 2,1 AFRONDEN(-1,475; 2) resulteert in -1,48 AFRONDEN(21,5; -1) resulteert in 20


- 19 -

Oefeningen 1. Open de werkmap ……………………………… op de kleine schijf. Bekijk de tabel met de klantengegevens in het werkblad klanten. Er zijn ……… velden. De database bevat ………. Records. KLantno. 101 102 103 104 105 106 107 108 109 110

Naam Argus B.V. LEX Software Nationale Bank Kinsman & Nelis Noord Investeringen Nica BV MDC BV Willems & Rood CC Baggerwerken Van Looji Assurantiën

Code 1 2 1 2 1 1 1 2 1 2

Categorie Produktie Diensten Financiën Diensten Financiën Produktie Produktie Diensten Productie Financieel

Regio Noord Zuid Zuid Oost Noord West Oost West West Zuid

Laatste contact Omzet Geplande omzet Omzet vorig jaar 1-11-93 191.380 BF 275.500 BF 294.460 BF 10-1-94 70.540 BF 210.680 BF 257.740 BF 1-12-93 29.440 BF 162.460 BF 200.440 BF 1-6-94 74.340 BF 247.480 BF 184.420 BF 25-2-94 66.300 BF 373.120 BF 220.880 BF 11-8-94 130.840 BF 1.164.580 BF 1.051.180 BF 1-3-94 163.340 BF 472.260 BF 514.660 BF 23-4-94 80.520 BF 235.720 BF 184.500 BF 15-5-94 8.470 BF 6.872 BF 6.613 BF 18-3-93 3.316 BF 8.465 BF 6.157 BF

2. Voeg de laatste 2 records toe (zoals op bijgevoegde afdruk ) met behulp van het data-formulier.

3. Sorteer de records alfabetisch op klantnaam.

4. Sorteer de records op categorie en binnen de categorie volgens dalende omzet.

5. Sorteer de records chronologisch volgens laatste contactdatum.

6. Filter de gegevens waarvoor de categorie gelijk is aan Financiën uit de database.

7. Idem 6 maar bijkomende voorwaarde : Regio = Zuid.


- 20 -

8. Toon terug alle records.

9. Filter de records waarvoor de regio noord of oost is.

10. Idem 9 maar bijkomende voorwaarde : Geplande omzet > 300.000BF

11. Schakel alle filters in één bewerking uit.

12. Maak een kolomtotaal voor de velden Omzet; Geplande Omzet en Omzet vorig jaar enkel voor de klanten van regio noord.

13. Wat is het onderscheid tussen de funktie SOM() en SUBTOTAAL() ?

14. Sorteer de records op basis van 4 velden : Code;Regio;Categorie en Naam.

15. Sorteer de gegevens in het werkblad REGIO in dalende volgorde op basis van de totale verkopen.

16. Sorteer de gegevens in het werkblad TELEFOON beginnend bij de maand JUNI.


- 21 -

17. Filter de records uit de KLANTEN-database met behulp van een uitgebreid filter. 17.a.

De records waarvoor de code =1

KLantno. Naam

17.b.

Code Categorie Regio



Omzet vorig jaar

Laatste contact

Omzet

Geplande omzet

Omzet vorig jaar

Laatste contact

Omzet

Geplande omzet

Omzet vorig jaar

De records waarvoor de regio = west en de categorie verschillend van diensten

KLantno. Naam

17.e.

Geplande omzet

De klanten waarvan de naam met de letter N begint

KLantno. Naam

17.d.

Omzet

De records waarvan de categorie =diensten en de geplande omzet groter dan 300.000BF

KLantno. Naam

17.c.

Laatste contact


Laatste contact

Omzet

Geplande omzet

Omzet vorig jaar

De records waarvoor de omzet verschillend is van 0 en de geplande omzet groter dan 6.000.000BF

KLantno. Naam


Laatste contact

Omzet

Geplande omzet

Omzet vorig jaar

18. Filter de records uit de Klanten-database 18.a.

De records waarvoor de regio gelijk is aan Noord of Oost

KLantno. Naam

18.b.


Laatste contact

Omzet

Geplande omzet

Omzet vorig jaar

De records waarvoor de regio verschillend van Noord en verschillend van Zuid

KLantno. Naam


Laatste contact

Omzet

Geplande omzet

Omzet vorig jaar


- 22 -

18.c.

De records waarvoor de categoerie begint met FIN en het laatste contact voor 1/1/94

KLantno. Naam

18.d.


Omzet

Geplande omzet

Omzet vorig jaar

De records waarvoor de regio = Oost of categorie gelijk aan financiën

KLantno. Naam

18.e.

Laatste contact


Laatste contact

Omzet

Geplande omzet

Omzet vorig jaar

De records waarvoor de regio gelijk is aan Oost en de omzet groter dan 5000 of regio gelijk aan Oost en de geplande omzet groter dan 200.000

KLantno. Naam


Laatste contact

Omzet

Geplande omzet

Omzet vorig jaar

19. We kunnen ook filteren op het resultaat van een berekening : een berekend criterium. Maak in je criteriumbereik een nieuwe kolom met om het even welke naam. Daaronder maak je de berekening door te verwijzen naar het eerste record in de database. Op basis hiervan wordt het criterium bepaald. Het resultaat van de berekening zal False of True zijn, afhankelijk van de gegevens van het eerste record. Op zich heeft dit echter geen belang, Kies Data ? Filter ? Uitgebreid filter om de selectie uit te voeren.

19.a.

Filter in de database Kapitaal (Kapitaal.xls) alle records waarvoor het intrestbedrag (kapitaal*intrest) groter is dan 10000BF

19.b.

Filter in de database Kapitaal alle records waarvoor de maand van aanvangsdatum oktober is.

19.c.

Filter alle records waarvoor de naam met de letter d begint.

19.d.

Filter alle records waarvoor de naam eindigt op de letter d


- 23 -

19.e.

Filter in de database Helpdesk alle gegevens waarvoor het totaal aantal telefoongesprekken over 1994 en 1995 de waarde 1000 overschrijdt

20. In plaats van de rijen te verbergen bij het filteren, kan je ook de geselecteerde records ergens anders naar toe kopiëren.

20.a.

Haal alle records uit de database klanten waarvoor de regio Noord is. We halen enkel de velden Naam, Regio en Geplande Omzet uit de database voor deze records.

20.b.

Haal de verschillende voorkomende categorieën uit de KLANTENdatabase

21. In de wizard-functies vind je onder de categorie Databsae-functies de functies die beginnen met de letter DB. Deze functies laten ons toe berekeningen te doen in databanken. Voorbeeld : Maak in de onderstaande tabel de som van alle getallen die overeenkomen met de naam A

Maak het gemiddelde van de getallen waarvoor de naam A is.

Tel het aantal getallen waarvoor de naam A is.

Tel het aantal namen waarvoor het getal groter is dan 10.


- 24 -

Oefening Maak in de database klanten de som van de geplande omzet waarvoor de Regio Noord is.

22. Je kan in een lijst of een databank ook op een eenvoudige manier subtotalen inlassen. Deze functie werkt met overzichtsymbolen, waardoor je snel de lijst kan comprimeren.

22.a.

Maak met behulp van Data?Subtotalen een som van de omzet en de geplande omzet per regio (klanten)

22.b.

Verwijder de ingevoegde subtotalen

22.c.

Maak met behulp van Data? Subtotalen een gemiddelde van de omzet en de geplande omzet per categorie (Klanten)

22.d.

Maak een overzicht van het aantal klanten per woonplaats in de database KAPITAAL

23. Open de werkmap ADRES.xls op de kleine schijf.

23.a. Sorteer de database alfabetisch op GESLACHT + NAAM + VOORNAAM

23.b.

Sorteer de gegevens volgens leeftijd.

23.c.

Bepaal het aantal mannen en vrouwen in de lijst.

23.d.

Bepaal het aantal personen per woonplaats.

23.e.

Bepaal het aantal mannen en vrouwen per woonplaats.


- 25 -

23.f.

Kopiëer de verschillende woonplaatsen uit de lijst.

23.g.

Filter alle personen jarig in de maand oktober uit de database

23.h.

Filter de drie oudste uit de lijst.

23.i.

Filter alle records uit de lijst van de personen met woonplaats Brabant (de postcode beginnend met 1)

24.

Open de werkmap Rekening

24.a. Schrijf een formule om het nieuwe saldo te berekenen en het vorige saldo over te brengen naar de volgende bewerking. 24.b.

Sorteer de database alfabetisch op het veld bewerking.

24.c. Filter alle benzinekosten in de lijst. Maak de som van de benzinekosten. 24.d.

Maak de som van de bezinekosten in de maand september.

24.e.

Bepaal het aantal stortingen

24.f.

Bepaal het aantal afnamen

24.g.

Bepaal de grootste storting

24.h.

Bepaal de kleinste afname


- 26 -

Een lijst uitbreiden of wijzigen met een gegevensformulier

Recommend Documents