Mike Dole
Probleem met absolute / relatieve koppelingen naar externe bestanden in Office 2010 Deel 2
De koppelingen in PowerPoint bevatten het complete / absolute pad naar het Excel bestand. Kopiëren en plakken geeft problemen omdat er naar 'oude' bestanden wordt verwezen...
In deze video toon ik een oplossing door het pad met bijvoorbeeld kladblok weg te halen zodat alleen de bestandsnaam overblijft.
Stap 1: Hernoem het .pptx bestand naar een .zip bestand
Stap 2: Open dit bestand en blader naar: PPT - Slides - _rels
Stap 3: Sleep het .xml.rels bestand naar een plek 'buiten de zip'. Klik met de rechtermuisknop en kies bewerken.
Stap 4: We verwijderen het absolute pad maar laten file:/// en de bestandsnaam staan.
Eventueel gebruiken we ALT-H, dit werkt gemakkelijker als er meerdere koppelingen zijn! We vervangen dan het pad door 'niets'
Stap 5: We slepen de aangepaste .xml.rels weer terug naar de zip en kiezen daar OK
Stap 6: Hernoem het .zip bestand terug naar een .pptx bestand
Als we nu een kopie maken van de bestanden uit de map januari naar een nieuwe map februari...
En we wijzigen de gegevens in de Excel, dan zien we als we 'koppelingen bijwerken' kiezen dat de presentatie de nieuwe Excel data bevat. Ook de koppeling zal verwijzen naar de nieuwe locatie!
Probleem met absolute / relatieve koppelingen naar externe bestanden in Office 2010 Deel 1
'Stel' We hebben een Excel bestand met gegevens over de maand januari En een powerpoint die verwijst naar deze 'gegevens.xlsx' Via 'Bestand - Koppelingen naar bestanden bewerken' zien we de 'harde' verwijzing naar het Excel bestand in de map januari...
We kopiëren nu de bestanden naar de map februari Passen de cijfers aan. Als we nu het net gekopieerde bestand openen dan zien we -hoewel we kiezen voor 'koppelingen bijwerken'- dat de cijfers niet bijgewerkt worden! Bij de eigenschappen van de koppeling zien we nog steeds een verwijzing naar het bestand in de map januari...
In dit filmpje wordt bovenstaand probleem getoond, oplossingen vindt u in deel 2 en deel 3 van deze serie
Hoe zet ik een kolom met tekst in Excel om naar getallen?
Formule Som Als - SOM.ALS
De formule som.als gaat al vele Excel versies terug.
Als voorbeeld hebben we een kolom met kleuren (A2:A12) en een kolom met cijfers (B2:B12). In de cel D2 staat de kleur waarvan we de cijfers willen optellen.
De formule som.als is als volgt opgebouwd 'BEREIK;CRITERIUM;OPTELBEREIK'
- Het bereik is het gehele gebied met kleuren en cijfers.
- Het criterium is de voorwaarde waar (in ons geval) de kleur aan moet voldoen.
- Het optelbereik is alleen de range met de op te tellen waarden.
Let er op dat je in het bereik altijd de kolom met voorwaarden (bij ons de kleuren) voor de op te tellen waarden (de cijfers) zet!
Onze formule wordt =SOM.ALS(A2:B12;D2;B2:B12)
Veranderen we de kleur in cel D2 naar paars dan wordt de uitkomst 22 (Paars komt 2x voor in de kolom cijfers, 1 x 21 en 1 x 1).
Download het voorbeeldbestand om te oefenen HIER
De formule som.als kan regelmatig van pas komen
Sparklines gebruiken in Excel
Sparklines in Excel zijn eigenlijk een soort van 'mini grafiekjes' die je gemakkelijk naast een tabel kan plaatsen om bijvoorbeeld een ontwikkeling / tendens in omzet te benadrukken:
Opdracht:
We hebben een tabel waarin de omzet over 2012 getoond wordt met damesfietsen, herenfietsen en kinderfietsen.
Na de laatste kolom willen we een sparkline tonen die de ontwikkeling in omzet accentueert.
Stappen:
Tabblad Invoegen - Sectie Sparklines - Kies bijvoobeeld Lijn:
Geef bij Gegevensbereik B4:M4 in (de hele regel voor damesfietsen) en bij Locatiebereik / waar zet ik de sparklinegroep neer N4
Herhaal dit voor de herenfietsen en de kinderfietsen, LET OP: Veel gemakkelijker is nu om met CTRL-C / CTRL-V (Kopiëren / Plakken) de herenfietsen en kinderfietsen te doen!
De sparklines worden nu achter de tabel getoond:
Als we op de sparklines gaan staan kunnen we nu kiezen voor het tabblad Ontwerpen.
In onderstaande afbeelding hebben we bij de sparkline voor de damesfietsen gekozen voor Kolom en hebben we Hoge punt aangevinkt waardoor we zien dat de hoogste maand augustus een andere kleur krijgt:
Met sparklines geef je je Excel werkblad meer het idee van een echt dashboard!
Download het voorbeeldbestand HIER
Hoe werkt verticaal zoeken in Excel nu eigenlijk?
Verticaal zoeken is 1 van de meest gebruikte formules in Excel!
Waarom? Omdat je met deze formule heel eenvoudig waarden in gegevenslijsten terug kan vinden. We laten nu aan de hand van een simpel voorbeeld zien hoe verticaal zoeken in Excel nu precies werkt zodat u het ook onder de knie krijgt.
Opdracht zoek in onderstaande tabel het percentage stemmen dat de PVDA gehaald heeft in de groep 75 jaar en ouder
In de cel onder ‘Partij’ staat de volgende formule:
=VERT.ZOEKEN(A14;A5:H11;8;ONWAAR)
Stap voor stap is dat:
- Zoek A14 = PVDA
- Zoek in het gebied A5:H11 dat is de hele tabel inclusief kolomlabels (groenlinks, D66, etc)
- Laat als de zoekwaarde gevonden is de 8E kolom terug van de regel waarin de zoekwaarde gevonden is zien. Had hier 1 gestaan dat kregen we als uitkomst de kolomlabels: groenlinks, D66, etc. Nu staat er 8 en krijgen we dus de 8e kolom terug als uitkomst.
- Zet benaderen op ‘ONWAAR’ zodat alleen iets wordt getoond als de zoekwaarde (pvda) exact is gevonden.
Om de laatste parameter ‘benaderen’ te verduidelijken wordt in het voorbeeld hieronder dezelfde formule gebruikt, echter met het veld benaderen op ‘WAAR’
=VERT.ZOEKEN(A29;A21:B25;2;WAAR)
Hierdoor wordt eigenlijk tegen de formule gezegd: ‘toon mij de uitkomst die het dichts tegen de zoekwaarde aanzit'. Er wordt dus gezocht op de waarde 64, die wordt niet gevonden maar de dichtstbijzijnde waarde wordt getoond = C.
Download het voorbeeldbestand HIER
Omzetten tekst naar getallen
Wanneer we een bestand openen waarin een 'mix' van getallen en tekst staat kunnen we natuurlijk op het gele bordje met het uitroepteken klikken en kiezen voor 'converteren naar getal'.
Als je een lange lijst moet converteren wil het nog wel eens voor komen dat je het gele bordje met uitroepteken niet 1-2-3 meer kan vinden.
Een alternatieve manier:
- Vul 'ergens' in een vrije cel een 1 in
- Selecteer deze cel en kies Kopiëren
- Selecteer de 'range' met velden waarvan u de tekst om wilt zetten naar getallen (bijvoorbeeld door de SHIFT toets ingedrukt te houden)
- Klik met de rechter muisknop op de range en kies 'Plakken Speciaal'
- Selecteer 'Vermenigvuldigen'
- Klik OK
Kijk ook naar
In dit filmpje ziet u een gemakkelijke / alternatieve manier om tekst om te zetten naar getallen
Excel Boeken en Software Shop
In samenwerking met BOL.COM
Zelf een formule maken 4 - toon de naam van de dag
We gaan weer een stapje verder en laten nu het gebruik van 'Select Case' in een zelfgemaakte formule zien.
Opdracht: Excel heeft alleen een formule om het nummer van een (week)dag aan te geven dus voor maandag 1, dinsdag 2, etc
ik wil graag een formule hebben die mij van een datum toont welke weekdag dit geweest is.
Maak een formule om op basis van een datum de naam van deze dag te tonen.
- Toets ALT-F11
- Kies Invoegen Module
- Zet het volgende in de formule:
Function DagNaam(WelkeDatum As Date)
Dim DagNummer As Integer
DagNummer = Weekday(WelkeDatum, vbSunday)
Select Case DagNummer
Case 1
DagNaam = "Zondag"
Case 2
DagNaam = "Maandag"
Case 3
DagNaam = "Dinsdag"
Case 4
DagNaam = "Woensdag"
Case 5
DagNaam = "Donderdag"
Case 6
DagNaam = "Vrijdag"
Case 7
DagNaam = "Zaterdag"
End Select
End Function
In een select case kijken we steeds of de waarde die we 'afvragen' overeenkomt met een opgegeven waarde. In ons voorbeeld bepalen we dus wat de dagnaam is van 10 februari 2013
* Overigens is er gewoon een bestaande functie binnen Excel om hetzelfde te bereiken: =TEKST(B50;"DDDD")
Doe mee met formules in Excel!
Zelf een formule maken 3 - Gebruik parameters in een formule
Door parameters aan onze formule toe te voegen kunnen we vanuit het Excel werkblad waarden naar de formule sturen waarmee deze moet gaan rekenen.
Klinkt complex? is het niet!
Opdracht: maak een formule om op basis van de kilometerstanden bij het tanken en het getankte aantal liters te berekenen hoeveel
kilometer er gemiddeld per liter is gereden:
Stappen
- Toets ALT-F11
- Kies Invoegen Module
- Zet het volgende in de formule:
Public Function KilometersPerLiter(StartKM As Integer, EindKM As Integer, Liter As Single)
KPL = (EindKM - StartKM) / Liter
End Function
Wat we doen is, na de formule naam (in onderstaand voorbeeld KilometersPerLiter) zetten we de parameters door een komma gescheiden tussen de haakjes.
De bovenstaande formule bevat het volgende =KilometersPerLiter(100;500;45)
100 is de kilometerstand bij de vorige tankbeurt, 500 bij de huidige tankbeurt, Liter is het aantal liters getankt bij de vorige tankbeurt.
Let er hier op dat je bij de formule in excel ; gebruikt waar je bij het maken van de functie , gebruikt tussen de parameters
De uitkomst van de formule is: 8,888889313
As Integer/ As Single?
Bij de parameters geven we aan wat voor soort waarde we verwachten, voor StartKM willen we een heel getal (Integer), hetzelfde geldt voor EindKM maar voor liters willen we een getal achter de komma aangeven (Single)
Excel zal een foutwaarde geven als je toch probeert om een tekst met quotes (") als paramter mee te sturen:
=KPL("honderd";500;45) --> #WAARDE
Meer hierover vindt u op deze pagina
Excel formules maken kan iedereen!