KLEINE.LETTERS

Veldhuis Installatie BV heeft je de ouderdomsanalyse debiteuren gegeven. De factuurnummers zijn door verschillende mensen ingetypt: VF-25-0412, vf-25-0413, Vf-25-0414.

Je data-analysetool vergelijkt factuurnummers teken voor teken, en in de export van het grootboek staan ze in kleine letters. Je zet er een kolom naast met elk factuurnummer in kleine letters voordat je de twee bronnen aan elkaar koppelt.

Formule in D2

=KLEINE.LETTERS(B2)

=KLEINE.LETTERS(B2)

Resultaat: vf-25-0412

Let op: Excel zelf ziet VF-25-0412 en vf-25-0412 al als hetzelfde, dus een opzoeking in Excel had ook zonder KLEINE.LETTERS gewerkt. De kolom is alleen nodig omdat de tool waarnaar je exporteert hoofdlettergevoelig is.

=KLEINE.LETTERS(B2:B6)

=KLEINE.LETTERS(B2:B6)

Je koppelt de verkoopfacturen in de debiteurenadministratie aan de omzetboekingen in dagboek VRK van het grootboek. De debiteurenadministratie bevat factuurnummers als vf-25-0414.

In de XAF-auditfile staat het nummer in de omschrijving, met een spatie in plaats van een koppelteken en met hoofdletters door elkaar: Vf 25-0414. Je maakt van de omschrijving de schrijfwijze van de debiteurenadministratie: spaties worden koppeltekens en letters worden klein.

Formule in E4

=KLEINE.LETTERS(SUBSTITUEREN(B4, " ", "-"))

=KLEINE.LETTERS(SUBSTITUEREN(B4; " "; "-"))

Resultaat: vf-25-0414

Let op: Staat er een spatie aan het begin of eind van de omschrijving, dan krijgt de sleutel daar een koppelteken. Zet SPATIES.WISSEN binnen SUBSTITUEREN als de export niet schoon is.

=KLEINE.LETTERS(SUBSTITUEREN(SPATIES.WISSEN(B4), " ", "-"))

=KLEINE.LETTERS(SUBSTITUEREN(SPATIES.WISSEN(B4); " "; "-"))

Bij het testen van journaalposten kijk je naar de handmatige MEM-boekingen van Veldhuis Installatie BV. Je wilt weten hoe vaak het woord storno in de omschrijvingen voorkomt, omdat stornoboekingen een toelichting nodig hebben. De opstellers schreven Storno, storno en STORNO, en in één omschrijving staat het woord twee keer.

AANTAL.ALS kan niet binnen een tekst tellen, en SUBSTITUEREN, de gebruikelijke teltruc, is hoofdlettergevoelig. Daarom zet je de omschrijvingen eerst in kleine letters, zodat één SUBSTITUEREN elke schrijfwijze vangt.

Formule in E2

=SOMPRODUCT((LENGTE(B2:B6) - LENGTE(SUBSTITUEREN(KLEINE.LETTERS(B2:B6), D2, ""))) / LENGTE(D2))

=SOMPRODUCT((LENGTE(B2:B6) - LENGTE(SUBSTITUEREN(KLEINE.LETTERS(B2:B6); D2; ""))) / LENGTE(D2))

Resultaat: 44

Let op: Typ je in D2 Storno met een hoofdletter, dan levert de formule zonder waarschuwing 0 op: de omschrijvingen staan in kleine letters, het zoekwoord niet, en niets komt overeen. Typen anderen het zoekwoord, zet D2 dan ook in KLEINE.LETTERS.

=SOMPRODUCT(--ISGETAL(VIND.SPEC(D2, B2:B6)))

=SOMPRODUCT(--ISGETAL(VIND.SPEC(D2; B2:B6)))

=AANTAL.ALS(B2:B6, "*" & D2 & "*")

=AANTAL.ALS(B2:B6; "*" & D2 & "*")

Je test de inkopen op dubbele facturen. De export van dagboek INK bevat de leveranciersrelatie en een referentieveld als Inv: FA 25-0388, ingetypt met verschillende hoofdletters, extra spaties en het nummer achter een voorvoegsel. Je data-analysetool vergelijkt sleutels teken voor teken.

Die sleutel bouw je in Excel uit drie onderdelen, alles in kleine letters, zodat dezelfde factuur die twee keer is geboekt dezelfde sleutel krijgt:

• leverancierscode
• factuurnummer zonder voorvoegsel en spaties
• bedrag

De formule gaat 20,00020.000 rijen omlaag, dus hij moet één keer goed te controleren zijn en daarna overal te vertrouwen.

Formule in E3

=LET(
  relatie, SPATIES.WISSEN(A3),
  factuurnr, SUBSTITUEREN(SPATIES.WISSEN(DEEL(B3, VIND.ALLES(":", B3) + 1, LENGTE(B3))), " ", ""),
  bedrag, C3,
  KLEINE.LETTERS(relatie & "|" & factuurnr & "|" & bedrag)
)

=LET(
  relatie; SPATIES.WISSEN(A3);
  factuurnr; SUBSTITUEREN(SPATIES.WISSEN(DEEL(B3; VIND.ALLES(":"; B3) + 1; LENGTE(B3))); " "; "");
  bedrag; C3;
  KLEINE.LETTERS(relatie & "|" & factuurnr & "|" & bedrag)
)

Resultaat: c2002|fa25-0391|12100

Let op: De omgekeerde opbouw, KLEINE.LETTERS in elk onderdeel en het samenvoegen aan de buitenkant, geeft hier dezelfde uitkomst, maar gaat mis zodra iemand een onderdeel toevoegt en daar de KLEINE.LETTERS vergeet. Eén KLEINE.LETTERS aan de buitenkant kun je niet vergeten.

=KLEINE.LETTERS(SPATIES.WISSEN(A3) & "|" & SUBSTITUEREN(SPATIES.WISSEN(TEKST.NA(B3, ":")), " ", "") & "|" & C3)

=KLEINE.LETTERS(SPATIES.WISSEN(A3) & "|" & SUBSTITUEREN(SPATIES.WISSEN(TEKST.NA(B3; ":")); " "; "") & "|" & C3)