Excel kennis verspreiden - deel 02: Special cells
- 30/12/2013
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Dag allen. Na deel 1 vorige week is hier, nog net voor 2013 feestelijk afgesloten wordt, deel 2.
Vandaag bespreek ik een dialoogvenster dat niet zoveel mensen kennen of goed weten te gebruiken, maar wat wel uiterst handig is: Go to > Special cells. Inderdaad, ik bespreek (slechts) 1 dialoogvenster en toch zal het een lijvig artikel zijn!
Dit dialoogvenster laat toe om heel snel bepaalde cellen of objecten te vinden en te selecteren in een bereik. Je kan dit venster oproepen via functietoets F5 (of Ctrl-G), en daarna klik je op Special… en je komt in het scherm rechts terecht. Met behulp van dat scherm kan je zeer gemakkelijk op zoek gaan naar bepaalde types cellen, objecten, enz. In wat volgt bespreek ik de belangrijkste mogelijkheden daar.
Om het één en ander te kaderen, kijk nog eens naar bovenstaand voorbeeld. Je ziet daar de top 5 van het Belgische voetbal :-) Hoe kan je nu gemakkelijk alle cellen selecteren die al een uitslag hebben. Ctrl-click van de verschillende cellen mag je doen, maar dat is geen aanrader. We selecteren cellen B2:F6 - in echte werkbladen zal het over veel grotere bereiken gaan.
Als we daarin alle cellen met tekst willen selecteren, drukken we F5, dan Special…, en dan "Constants" en tot slot "Text". Het resultaat is een selectie van niet-aaneengesloten cellen met de reeds gespeelde matchen. De overige (lege) cellen cellen selecteren kan via F5, dan Special…, en dan "Blanks".
Zijn de tekstcellen met de uitslagen echter het resultaat van een formule, dan zullen die niet mee geselecteerd worden als je "Constants" kiest. Dan kies je uiteraard voor "Formulas" en daaronder "Text".
Kan het zoekcommando geen enkele cel vinden, dan krijg je dit als resultaat:
-
We zien al direct een aantal voordelen van zo'n Excel tabel (ListObject eigenlijk):
- Automatisch heb je de autofilter pijltjes, waar je gemakkelijk kan filteren (autofilter werd drastisch uitgebreid in versie Excel 2007)
- Kleuren van de rijen, om en om
- Als je naar beneden scrollt in de tabel naar het volgende blad, blijven de kolomhoofdingen (Consultant, Klant, …) bovenaan vast in beeld staan
- Merk het driehoekje op rechtsonderaan de tabel: je kan de tabel eenvoudig resizen.
Andere zoekmogelijkheden in het dialoogvenster, telkens met praktisch voorbeeld/nut
Bekijk hierbij ook de bijlage voor deel 02 bovenaan deze pagina, daar staan praktische voorbeelden.
"Comments" slaat op de opmerkingen die je aan cellen kan toevoegen. Alle cellen met een opmerking selecteren, kan dus via het selecteren van het grote bereik, dan F5, dan Special…, dan Comments, OK. Zo delete je bijvoorbeeld alle opmerkingen uit een heel tabblad.
-
Vervolgens kan je ook automatisch zoeken op:
- Getallen / tekst / logische waarden / fouten
- Constante waarden of formules
Met 1 beweging kan je zo uit een bereik de cellen selecteren/kleuren/aanduiden die een formule hebben EN de formule geeft een foutmelding (links is vóór, rechts is ná):
DAN:
De celtypes zijn additief: je kan meerdere vinkjes zetten, om zo de formulecellen te krijgen die ofwel een getal, ofwel een foutwaarde hebben (maar geen tekst en geen logische waarde). Een toepassing is als volgt. Stel dat je een kolom hebt met datums, maar een aantal van die datums zijn eingelijk tekst: dat krijg je als je bvb. van een internetpagina kopieert en de datums niet goed geïnterpreteerd worden als geldige datums (Nederlands <> Engels datumformaat). Dan haal je zo met 1 beweging de tekstcellen eruit, aangezien datums in Excel eigenlijk getallen zijn. Die tekstcellen kan je dat deleten / verbeteren / in een kleurtje zetten, …
Blanks, oftewel, de lege cellen. Kan zeer nuttig en tijdsbesparend zijn. Vaak moeten we lege cellen opvullen met de waarde van de cel erboven (van links naar rechts weer de opeenvolgende stappen):
DAN:
DAN:
DAN:
-
Te volgen stappen (doe dit nu even 2 é 3 keer en het zit in je vingers voor 2014 :-) ):
- Selecteer cellen A1:D10
- Druk F5, Special… > Blanks (dat kennen we nu ondertussen)
- De cursor staat nu in cel A2, een lege cel. Typ een "isgelijkaan teken" en dan druk je pijltje naar boven
- Tot slot, nog altijd sta je in cel A2, druk je Ctrl-Enter en NIET gewoon Enter!
- Daarna kan je nog het bereik kopiëren en plakken als Waarden, om van de tijdelijke formules af te geraken
Ctrl-Enter is de manier om een invoer te doen in alle geselecteerde cellen tegelijk! Dit is zeer handig en snel.
Volgende handige optie is het selecteren van de Objecten. Bijvoorbeeld, je hebt een mooie sheet vol met plaatjes, maar je wil die verwijderen:
DAN:
Selecteer het hele bereik rondom de figuren, druk F5, Special…, Objects, OK. Druk Delete en ze zijn weg.
Nog een nuttige, is Row differences en Column differences, vooral boekhouders bijvoorbeeld. Stel dat je 2 (hele lange) kolommen met getallen hebt en je wil weten waar de verschillen zitten:
DAN:
Selecteer de cellen in de 2 kolommen, druk F5, Special…, Row Differences, OK. Excel selecteert onmiddellijk 2 cellen (30,85 en 91,60) aangezien die niet gelijk zijn aan het getal op dezelfde rij, in de andere kolom. De eerste kolom bevat de waarden waarmee vergeleken wordt. Deze techniek kan je ook op bereiken toepassen met meer dan 2 kolommen! Column differences is analoog maar aan Row differences, maar dan in getransponeerde vorm. Row en Column differences werken ook met tekstcellen!
Precedents en dependents heeft te maken met Excel formules en de celverwijzingen die je daarin maakt: welke cel(len) worden genomen in de berekening van een andere cel. Die (basis)cellen kan je aanduiden via F5, Special…, Precedents, OK. Dependents zijn de cellen die via hun formule afhangen van de geselecteerde cel. Het omgekeerde principe dus. Dit zal ik waarschijnlijk eens bespreken in een bijdrage over hoe je formules kan auditen in Excel.
De "Last cell" is handig in die zin dat het aangeeft waar Excel denkt dat de laatste cel van dat werkblad zich bevindt. Zegt Excel dat die laatste cel duizenden en duizenden rijen lager ligt dan waar jij denkt dat de data in het werkblad zou stoppen, dan moet je de tussenliggende lege rijen en/of kolommen eens volledig verwijderen en het bestand opslaan. Tussenliggende rijen en kolommen nemen plaats in en zorgen voor een groter, zwaarder en trager Excel bestand. Soms is een bestand Megabytes groot terwijl er maar een honderdtal cellen ingevuld zijn. Wel, dat is dit fenomeen dat je dan moet nakijken, werkblad per werkblad.
De "Visible cells" zijn ook wel al eens handig. Als je autofilter toepast, of je verbergt zelf rijen/kolommen, dan kan je de resterende zichtbare cellen ineens "pakken". Dit gebruik je als je uit een bereik (na manueel verbergen van rijen) enkel de overblijvende zichtbare cellen wil kopiëren. Doe je deze actie niet, dan kopieer je ook de verborgen cellen mee.
De laatste 2 opties zijn Data validatie en Voorwaardelijke opmaak (Conditional formatting). Cellen die data validatie hebben kan je onmiddellijk opzoeken en selecteren via de eerste optie. Zo pas je in 1 beweging de keuzelijstjes aan in een groot aantal cellen. Voorwaardelijke opmaak verwijderen in een bereik kan je doen door het opzoeken van de cellen die hieraan voldoen (F5, Special…, Conditional formats)
Nadeel
Super handig dus, maar wat je niet zomaar kan via dit scherm, is bijvoorbeeld selecteren op cellen die een bepaalde achtergrondkleur hebben. Of cellen die in het vet / schuingedrukt staan, dat kan jammer genoeg niet. Tevens niet het selecteren van de cellen die groter zijn dan 0, kleiner dan 1000, enz. Het dichtste kan je in de buurt komen met een Autofilter sinds Excel 2007, omdat die sinds toen kan zoeken op kleur, o.a. Of wend je tot VBA :-)
VBA-code
In VBA-code gaan we deze technieken ook toepassen, vooral uit snelheidsoverwegingen. Als je een lus maakt doorheen de cellen in een kolom, waarvan er een een heel aantal leeg zijn, dan kan je even goed de lus maken doorheen de cellen die je krijgt via .SpecialCells(xlCellTypeConstants, xlNumbers) Zo vraag je uit een bereik de cellen op met een constante, numerieke, waarde. Je slaat m.a.w. cellen over die berekend zijn, of die een foutwaarde geven, of die tekst bevatten, … Deze techniek is sneller dan te loopen over alle cellen en cel per cel uit te vragen of er een getal staat of niet.
Nadere lectuur
Deze rechtstreekse pagina op mijn eigen website: hier zo.
Zo, deel 2 is hiermee ten einde!
