Mike Dole

Mike Dole

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!

 

 

 

'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

 

Als we een bestand openen met een kolom waarin getallen en tekst door elkaar staan kunnen we natuurlijk op het gele bordje klikken met het uitroepteken en kiezen voor 'converteren naar getal' maar 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. In dit Excel instructie filmpje ziet u een gemakkelijke / alternatieve manier om tekst om te zetten naar getallen.
woensdag, 20 februari 2013 19:33

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)

Voorbeeld som.als functie

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

zondag, 17 februari 2013 19:46

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.
Tabel omzet fietsen
Na de laatste kolom willen we een sparkline tonen die de ontwikkeling in omzet accentueert.

Stappen:
Tabblad Invoegen - Sectie Sparklines - Kies bijvoobeeld Lijn:

Tabblad Invoegen - Sparklines

 Geef bij Gegevensbereik B4:M4 in (de hele regel voor damesfietsen) en bij Locatiebereik / waar zet ik de sparklinegroep neer N4

Bereik sparklines

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:

 Tabblad Invoegen - Sparklines

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:

Sparklines bewerken

 

Met sparklines geef je je Excel werkblad meer het idee van een echt dashboard!

Download het voorbeeldbestand HIER

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

Verticaal zoeken

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.
Verticaal zoeken in Excel

Download het voorbeeldbestand HIER

zondag, 17 februari 2013 17:20

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

Omzetten tekst naar getallen in Excel

 Kijk ook naar

In dit filmpje ziet u een gemakkelijke / alternatieve manier om tekst om te zetten naar getallen

dinsdag, 12 februari 2013 19:10

Excel Boeken en Software Shop

In samenwerking met BOL.COM

 

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   
   

formule dagnaam  

 

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!

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!

Pagina 4 van 7

Excel Software Shop

Web Analytics