14/5/2010
CVO HEUSDENZOLDER
WORKSHOP ZOEKEN EN VALIDEREN
Geschreven door | Inge Put
Valideren van gegevens. 1 Gegevensinvoer controleren. In het Menu Data kan je valideren terugvinden.
Met gegevensvalidatie kan je voorkomen dat er ongeldige gegevens in een cel worden ingevoerd. Je kan zelf bepalen wat je wel of niet toestaat in die cel of cellen. Je kan bv enkel getallen aanvaarden die groter zijn dan 100 en kleiner dan 1000. Selecteer de cel of cellen waarvoor u de validatie wenst in te stellen. En klik op valideren.
Pagina 2
Vul volgende gegevens in op het tabblad Instellingen. Kies onder Toestaan de gegevens die je wenst toe te laten in deze cel of cellen.
Kies onder gegevens aan welke voorwaarden de gegevens moeten voldoen.
Pagina 3
Geef vervolgens de grenswaarden in.
Vul op het tabblad Invoerbericht de tekst in die je wenst te laten verschijnen wanneer je de cel selecteert.
Pagina 4
Kies op het tabblad Foutmelding onder Stijl wat er moet gebeuren indien er foutieve gegevens worden ingevoerd.
Vul op het tabblad foutmelding bij Titel en Foutbericht de tekst in die je wil laten verschijnen wanneer er foutieve gegevens worden ingevoerd.
Pagina 5
Klik op OK om te bevestigen. Bekijk het resultaat. Het Invoerbericht verschijnt bij het selecteren van de cel.
Type een foutief getal in(>1000 of <100). De Foutmelding verschijnt.
Pagina 6
Zo kan je ĂŠĂŠn of meerdere cellen beveiligen tegen verkeerde invoer van gegevens.
2 Vervolgkeuzelijst maken Je kan ook afdwingen dat de invoer wordt gekozen uit een vervolgkeuzelijst. Voer volgende gegevens in op een leeg werkblad. Links in de cel B3 voeren we een vervolgkeuzelijst in. Rechts onder BTW% staat de keuzelijst die we willen laten verschijnen in de cel B3.
Selecteer de cel B3. Klik op Data Valideren en vul volgende gegevens in op het tabblad Instellingen. Kies bij toestaan voor Lijst.
Selecteer bij bron de BTW% in de cellen J2-J5. Deze getallen zullen verschijnen in de vervolgkeuzelijst.
Pagina 7
Klik op OK en bekijk het resultaat. Rechts aan de cel B3 verschijnt er een keuzepijltje.
Klik op het pijltje en kijk wat er gebeurt.
Pagina 8
Maak je keuze en voer in de cellen C3 en C4 de juiste formules in.
Let wel op indien de lijst met gegevens die je wenst te laten verschijnen in de vervolgkeuzelijst niet op hetzelfde blad staat dan de cel waarvoor u de keuzelijst wil maken moet u de lijst benoemen(een naam geven) via naam definiĂŤren.
Pagina 9
3 Celbereiken benoemen. Selecteer de te benoemen cellen en klik in het Menu op Invoegen bij Naam en DefiniĂŤren.
Selecteer de cellen J2-J5 en pas toe.
Geef het celbereik een naam en klik op OK om te bevestigen. Links in formulebalk verschijnt de naam van de benoemde cellen. Die naam kan je nu in formules of bij validatie in de bron gebruiken wanneer je deze cellen wenst te gebruiken.
Pagina 10
Klik bij Bron in gegevensvalidatie, ga naar het Menu Invoegen en kies voor Naam en Plakken.
Klik op BTW(de naam van de benoemde cellen). Deze verschijnt nu in het kader van Bron voorafgegaan door een = teken.
Pagina 11
De naam van de benoemde cellen verschijnt in de kader onder Bron.
Klik op OK om te bevestigen.
Pagina 12
Zoekfuncties 1 Via het menu Invoegen Functie een functie invoegen
Pagina 13
2 Via Som en meerfuncties een functie invoegen
Pagina 14
3 Via fx in de formulebalk een functie invoegen
4 De groep Zoeken en Verwijzen: Verticaal Zoeken
Pagina 15
Met deze functie zoeken we in een tabel naar een bepaald veld adv een ingegeven veld.
Pagina 16
5 Voorbeeld Hotel uitwerken Open de werkmap â&#x20AC;&#x153;Hotelâ&#x20AC;?.
Dit is de bijhorende Tabel met kamergegevens.
Pagina 17
Het is de bedoeling dat als je in kolom A een kamernummer intypt het type, de prijs en het BTW% van de kamer automatisch verschijnen. En moet ook het totaal berekend worden. Je kan best eerst de tabel benoemen. Anders moet je telken denken aan “absolute celverwijzingen” die er bijhoren in de formules. Selecteer de volledige tabel. Je mag ook de volledige kolommen selecteren indien je er wenst voor te zorgen dat er nog kamer bij kunnen ingevoerd worden.
Klik links in formulebalk en voer de tabelnaam “Hotelkamers” in. Klik op Enter om te bevestigen.
Pagina 18
Nu kan je in alle formules waar je wil verwijzen naar deze tabel de naam “Hotelkamers” gebruiken in plaats van de volledige tabel te selecteren. Bij het kopiëren van de formule moet je dan ook geen zorgen maken over de $-tekens. Je kan dit ook doen via het Menu Invoegen in het van Naam Definiëren.
Klik in de cel D7. Hier kan je de formule opbouwen. Ga naar invoegen functie, kies voor de groep Zoeken en Verwijzen en klik op Verticaal Zoeken.
Pagina 19
Zoekwaarde: is de cel met de inhoud die je in de eerste kolom van de tabel gaat zoeken. Tabelmatrix: is de volledige tabel waarin je wenst te zoeken (Hotelkamers).
Pagina 20
Kolomindex_getal: is de kolom van de tabel waaruit je de corresponderende gegevens went op te halen. Deze tabel bevat 4 kolommen. Je wil het kamertype vinden uit kolom 2. Vandaar die 2. Benaderen: hier zet je best 0 indien je een volledige overeenkomst wil met de ingevoerde zoekwaarde. Het kamertype verschijnt in de Cel. Je kan de formule nu doorvoeren naar beneden met de vulgreep.
Zo kan je voor Eenheidsprijs en BTW op dezelfde manier werken. Enkel het Kolomindex_getal wijzigt. Dit wordt respectievelijk 3 en 4. Selecteer de cel E6 en voer volgende formule in.
Pagina 21
Selecteer de cel F6 en voer volgende formule in.
Pagina 22
De oefening ziet er nu als volgt uit. Nu moet je enkel nog het totaal berekenen.
Selecteer de cel G6 en voer volgende formule in: =(C6-B6)*E6*(1+F6)
Voer de formule door naar beneden. Resultaat.
Pagina 23
Bereken de som van de Totalen.
Endresultaat.
Pagina 24
6 Voorbeeld Vandeyk
Open het werkblad “Van Dyck”.
Selecteer de tabel en geef ze de naam “boek”.
Het is de bedoeling dat de juiste prijs verschijnt naast elke bestelbon. De prijs wordt bepaald door het aantal bestelde boeken. De prijzentabel vind je links in kolom A B een C. plaats de cursor in de Cel I2. En voer de Verticale Zoekfunctie in.
Pagina 25
Zoekwaarde: is de cel met de inhoud die je in de eerste kolom van de tabel gaat zoeken. Het aantal bestelde boeken per bestelbon. Tabelmatrix: is de volledige tabel waarin je wenst te zoeken (boek). Kolomindex_getal: is de kolom van de tabel waaruit je de corresponderende gegevens went op te halen. Deze tabel “boek” bevat 2 kolommen. Je wil de prijs vinden uit kolom 2 die overeen stemt met de bestelde hoeveelheid boeken. Vandaar die 2. Benaderen: hier zet je 1 indien je wenst te zoeken in groepen. Het aantal bestelde boeken vanaf 1000 tot en met 1999 komt overeen met de prijs € 50,00. Het aantal bestelde boeken vanaf 2000 tot en met 2999 komt overeen met de prijs € 45,00. En zo verder. Bevestig met OK en controleer de Prijs.
Pagina 26
Sleep de formule door naar beneden.
Bereken de totale prijs per bestelbon.
Druk op Enter en sleep de formule naar beneden.
Pagina 27
Bekijk het resultaat en bewaar het voorbeeld.
7 De groep Zoeken en Verwijzen: Horizontaal Zoeken Wanneer je tabel horizontaal is opgebouwd ipv vertikaal dan gebruik je Horizontaal Zoeken.
Pagina 28
Pagina 29
Oefening Bakker Open de werkmap Bakker opgave. Je krijgt dan volgend overzicht op blad 1.
Pagina 30
En volgende tabellen op blad 2.
Pagina 31
Werk naar onderstaand afgewerkt overzicht. Voer de nodige formules in en pas gegevens validatie toe om tot dit afgewerkt resultaat te bekomen.
Pagina 32
Zorg voor het verschijnen van Drop down menuâ&#x20AC;&#x2122;s in kolom van de klant. En kies de juiste klanten.
Pagina 33
Zorg voor het verschijnen van Drop down menuâ&#x20AC;&#x2122;s in kolom van het product. En kies de juiste producten.
Pagina 34
Bereken de korting door gebruik te maken van de gegevens in de rijen 23:26. Maak hiervoor gebruik van de Zoekfunctie.
Bereken het totaal bedrag door de prijs van het product te zoeken in de tabel op blad 2 en deze te vermenigvuldigen met de hoeveelheid verkochte ĂŠĂŠnheden en er de juiste korting op toe te passen. Bereken bovenaan de totalen en het gemiddelde.
Pagina 35
Maak een draaitabel met het totaal van het aantal verkochte broden per soort.
Maak van deze tabel een grafiek.
Pagina 36
Maak een draaitabel met de totale opbrengst per soort brood.
Maak van deze tabel een grafiek.
Bewaar onder de naam BAKKER en Upload je oplossing. Veel succes!
Pagina 37