Excel koppelen aan ERP: mogelijkheden en beperkingen

Gegevens uit het ERP (het bedrijfssysteem voor orders, voorraad en financiën) in Excel halen is eenvoudig: met een export of met Power Query, als uw ERP daar een gedocumenteerde ingang voor heeft. De andere kant op, van Excel naar het ERP, vraagt meer zorg. Gebruik dan een importfunctie van het ERP, een Excel-invoegtoepassing van de leverancier, een koppeling of een software-robot (RPA, software die schermhandelingen van een medewerker overneemt), en controleer de gegevens vóór ze het ERP in gaan. Gaan er elke dag veel gegevens tussen Excel en het ERP heen en weer, dan is Excel vaak niet meer de juiste plek voor die gegevens.

Twee richtingen, twee soorten risico

"Excel koppelen aan ERP" betekent in de praktijk één van twee dingen. Of u haalt gegevens uit het ERP naar Excel, bijvoorbeeld voor een rapport of een analyse. Of u zet gegevens vanuit Excel in het ERP, bijvoorbeeld orders, prijzen, artikelen of een stuklijst (de onderdelen en aantallen van een product). Engineering exporteert zo'n stuklijst bijvoorbeeld als Excel-bestand uit het CAD-pakket (de tekensoftware) of het PDM-systeem (het tekeningenbeheer).

De eerste richting is weinig riskant. Gaat er iets mis, dan klopt uw rapport niet, maar het ERP blijft goed. De tweede richting is riskanter. Een fout in een Excel-bestand komt dan in het ERP terecht, en van daaruit in orderbevestigingen, facturen en de planning. Daarom gaat dit artikel vooral over controles bij die tweede richting.

Het voorbeeld: orders als Excel-bestand

Rekenvoorbeeld Excel-orders (aannames: 20 orders van 4 minuten, 230 werkdagen, 15% afwijkingen, 15 minuten controle per dag)
Onderdeel	Per dag	Per jaar
Handwerk nu: 20 × 4 minuten	80 minuten	ca. 307 uur
Afwijkingen blijven handwerk: 3 × 4 minuten	−12 minuten	ca. −46 uur
Dagelijkse controle van de automatisering	−15 minuten	ca. −58 uur
Werkelijk vrijgemaakte tijd	53 minuten	ca. 203 uur

De 80 minuten tellen niet volledig als besparing, want de afwijkingen en de controle blijven mensenwerk. Bij een kleine stroom weegt die vaste controletijd zwaar: hier is het bijna een vijfde van het handwerk. Bouw, licenties en onderhoud zijn kosten die u apart rekent. Voor alle 80 orders samen, pdf en Excel, komt hetzelfde rekenvoorbeeld uit op ca. 985 uur per jaar. De rest van dit artikel laat zien welke route zo'n orderbestand in het ERP krijgt, en waar Excel daarbij voor verrassingen zorgt.

Zes manieren om Excel en ERP te verbinden

1. Exporteren en importeren via CSV

Bijna elk ERP kan een lijst exporteren naar Excel of CSV. CSV is een tekstbestand met kolommen, gescheiden door een komma of puntkomma. Veel ERP-pakketten kunnen ook een bestand importeren, als het precies de opbouw heeft die het pakket verwacht. Dit is de eenvoudigste route: weinig te bouwen, en het ERP voert bij de import zijn eigen controles uit.

Let op de details. Welk scheidingsteken Excel bij opslaan als CSV gebruikt, hangt af van de landinstellingen van Windows. Microsoft waarschuwt dat het wijzigen van die instelling alle programma's op de computer raakt. Opent u een CSV-bestand met dubbelklikken, dan past Excel zijn standaardopmaak toe en verdwijnen bijvoorbeeld voorloopnullen. Spreek de opbouw daarom vast af en test met een echt bestand, inclusief datum en decimaalteken.

2. De standaard importfuncties van het ERP

Veel pakketten hebben een importfunctie voor vaste soorten gegevens, zoals artikelen, prijzen, relaties of boekingsregels. Vaak hoort daar een sjabloon bij, en na de import krijgt u een verslag met de regels die zijn geweigerd. Zoek in de documentatie van uw pakket naar de importfuncties, en vraag de leverancier welke velden en controles erin zitten. Ga er niet van uit dat er ook een import voor orders is: controleer per pakket welke soorten gegevens de import aankan.

Het risico zit in wat er na de import gebeurt. Een verslag met een fout bij regel 312 van 400 valt makkelijk weg. Spreek af wie het verslag leest en wat er met geweigerde regels gebeurt.

3. Power Query: gegevens uit het ERP lezen

Power Query is het onderdeel van Excel waarmee u gegevens ophaalt en bewerkt, op het tabblad Gegevens. Het kan onder meer een OData-feed lezen: een gestandaardiseerde manier waarop een systeem tabellen beschikbaar stelt. Business Central, het ERP van Microsoft, biedt zo'n ingang, en Microsoft noemt Power Query in Excel als een van de programma's die OData kunnen lezen. De aparte Business Central-connector van Power Query noemt Microsoft alleen voor Power BI en Power Query Online. In Excel gebruikt u dus de OData-feed.

Gebruik deze route alleen als uw ERP de ingang zelf documenteert. Power Query meldt zich bij het ERP aan met een account, bijvoorbeeld uw eigen werkaccount. Het vernieuwt de gegevens in Excel: met de knop Alles vernieuwen, bij het openen van het bestand of elke zoveel minuten. Belangrijk: Power Query schrijft niets terug naar het ERP. Het is een route voor rapporten en controles, niet voor invoer.

4. Een Excel-invoegtoepassing van de ERP-leverancier

Sommige leveranciers bieden een invoegtoepassing voor Excel. Microsoft Dynamics 365 Business Central heeft er een. Met Openen in Excel kunt u een lijst alleen bekijken en bewerken in het bestand; wijzigingen gaan niet terug. Met Bewerken in Excel kunt u records in Excel aanpassen en daarna publiceren naar Business Central. Wijzigingen gaan pas terug als u publiceert.

De beperkingen volgens Microsoft: Bewerken in Excel staat op de meeste lijsten, niet op alle. De invoegtoepassing werkt in Excel voor het web en in Excel voor Windows, niet op de Mac. En als OneDrive voor systeemfuncties is ingesteld, beperkt Business Central het bestand tot 100 kolommen. Gebruikt u een ander pakket, vraag dan of de leverancier een vergelijkbare invoegtoepassing heeft, en wat die controleert.

5. Een koppeling

Bij een koppeling leest een automatisering het Excel-bestand en zet de gegevens via de API van het ERP erin. Een API (application programming interface) is een vaste ingang voor software. Het ERP antwoordt per order met een ordernummer of een foutmelding, dus u ziet direct wat mislukt is.

Dit kan ook met Power Automate (de Microsoft-software voor het automatiseren van taken) en de Excel-connector (de kant-en-klare verbinding met Excel), als het bestand in OneDrive voor Bedrijven of SharePoint staat. Ken de grenzen: de gegevens moeten in een Excel-tabel staan, bestanden mogen hoogstens 25 MB zijn, en rijen ophalen geeft standaard 256 rijen, tenzij u paginering aanzet. Microsoft ondersteunt ook niet dat een stroom en een medewerker tegelijk in hetzelfde bestand schrijven; dat kan tot inconsistente gegevens leiden.

6. Een software-robot

Een software-robot werkt via het scherm, zoals een medewerker. Hij leest het Excel-bestand en typt de gegevens in het ERP. Dat is de route als het ERP geen bruikbare import en geen API heeft. Een robot is wel trager dan een koppeling en gevoelig voor schermwijzigingen na een update. De afweging leest u in RPA of API-koppeling: wat is de beste keuze?

De zes routes vergeleken
Route	Richting	Sterk in	Beperkingen
Export en CSV-import	Beide	Eenvoudig, het ERP controleert bij import	Scheidingsteken, gegevenstypen, verslagen die niemand leest
Importfunctie van het ERP	Excel naar ERP	Stamgegevens en prijzen in batches	Vaste soorten gegevens, niet altijd voor orders
Power Query (OData)	ERP naar Excel	Rapporten en controles die zichzelf bijwerken	Alleen lezen, alleen met een gedocumenteerde ingang
Invoegtoepassing van de leverancier	Beide	Lijsten in Excel bewerken en terugzetten	Niet bij elk pakket, niet op elke lijst
Koppeling (API)	Excel naar ERP	Dagelijkse stromen, directe foutmelding	Vraagt een bruikbare API en beheer
Software-robot	Excel naar ERP	ERP zonder import of API	Trager, onderhoud na schermwijzigingen

De beperkingen van Excel in deze keten

De route is zelden het grootste probleem. Excel zelf is dat vaker, omdat het gemaakt is om vrij in te werken. Zes punten verdienen aandacht.

Excel als leidende bron

Staan de afgesproken prijzen alleen in een Excel-bestand en niet in het ERP, dan zijn er twee waarheden. Het ERP rekent met de ene prijs, de verkoper belooft de andere. Bepaal per soort gegeven welk systeem leidend is. Meestal hoort dat het ERP te zijn, en leest Excel alleen mee. Hetzelfde geldt voor een stuklijst die de werkvoorbereiding naast het ERP in Excel bijhoudt: dan weet niemand zeker welke revisie de werkvloer krijgt.

Versies en kopieën

Een bestand dat per e-mail rondgaat, krijgt kopieën: "prijslijst definitief", "prijslijst definitief 2". Niemand weet zeker welke versie is geïmporteerd. Zet het bestand op één vaste plek, deel een link in plaats van een kopie, en leg vast welke versie wanneer in het ERP is gezet.

Validatie

Met gegevensvalidatie in Excel kunt u de invoer sturen, bijvoorbeeld met een keuzelijst of een minimum en maximum. Dat helpt, maar het controleert niet of een artikel in het ERP bestaat, of een prijs past bij de afspraak, of een order al eerder is verwerkt. Die controles horen vlak vóór de import, tegen de actuele gegevens in het ERP.

Twee mensen tegelijk

Tegelijk in één werkmap werken (cocreatie) kan met Microsoft 365, als het bestand in OneDrive of SharePoint Online staat en automatisch opslaan aan staat. Volgens Microsoft wint bij een conflict de wijziging die het laatst is opgeslagen. SharePoint op een eigen server ondersteunt cocreatie niet, en één collega met een oude Excel-versie kan het bestand voor iedereen vergrendelen. Voor een bestand waaruit een automatisering leest, is dat een risico.

Gegevenstypen en voorloopnullen

Excel past ingevoerde of geopende gegevens automatisch aan. Microsoft noemt de belangrijkste gevallen: Excel haalt voorloopnullen weg, zet bij getallen van meer dan 15 cijfers de laatste cijfers op nul, en maakt van letter- en cijfercombinaties zoals JAN1 een datum. Een klantartikelnummer 004512 wordt dan 4512. In het gunstigste geval vindt de automatisering het artikel niet. In het slechtste geval vindt ze een ander artikel.

In Excel voor Microsoft 365 en Excel 2024 kunt u die automatische conversies uitzetten, via Bestand, Opties, Gegevens. Die instelling geldt ook bij het openen van een CSV-bestand, maar volgens Microsoft niet voor Power Query. Zet in Power Query het gegevenstype van zulke kolommen daarom zelf op Tekst.

Macrobeveiliging

Een macro in VBA (Visual Basic for Applications) lijkt een snelle manier om een bestand naar het ERP te sturen. Maar Office op Windows blokkeert standaard macro's in bestanden van internet, zoals e-mailbijlagen. Een macrobestand dat u rondmailt, werkt bij de ontvanger dus niet zonder extra stappen. Microsoft noemt als oplossingen onder meer een vertrouwde locatie en macro's met een digitale handtekening van een vertrouwde uitgever. Bouwt één collega de macro, dan weet niemand meer precies wat het bestand doet als die collega vertrekt.

Het voorbeeld uitgewerkt

Voorbeeld (illustratief)

Het productiebedrijf kiest per stap de eenvoudigste route die werkt. Het ERP heeft in dit voorbeeld een gedocumenteerde API voor verkooporders.

Ophalen. Orderbestanden komen in een vaste map, ook als een klant ze per e-mail stuurt.
Omzetten. Per klant ligt vast welke kolom wat betekent. De automatisering leest het bestand en zet de kolommen om naar één vaste orderopbouw. Artikelnummers leest ze als tekst, zodat voorloopnullen blijven staan.
Vertalen. Klantartikelnummers worden vertaald met een kruistabel: een lijst met het klantnummer en het eigen artikelnummer. Die tabel staat in het ERP, niet in een los Excel-bestand.
Controleren. Bestaan de klant en de artikelen? Ligt de prijs binnen de marge van de afspraak? Is het klantordernummer nog niet verwerkt? Is de leverdatum haalbaar?
Aanmaken. Klopt alles, dan maakt een koppeling de order via de API aan. Het ERP geeft een ordernummer of een foutmelding terug.
Afwijkingen. De drie afwijkende orders per dag gaan naar een werkbak, de lijst met gevallen die een medewerker beoordeelt, met het originele bestand en de reden erbij. De binnendienst beslist.

Had het ERP geen API maar wel een orderimport, dan maakt stap 5 een importbestand in de opbouw van het ERP. Had het ERP geen van beide, dan voert een software-robot de goedgekeurde orders in via het scherm. De stappen 1 tot en met 4 blijven in alle gevallen gelijk. Hoe het hele orderproces werkt, ook met pdf-orders, leest u in handmatige orderverwerking automatiseren.

Wanneer Excel niet meer de juiste plek is

Excel is sterk in rekenen, analyseren en snel iets proberen. Als centrale plek voor gegevens waar een bedrijfsproces op draait, is het zwakker. Dit zijn signalen dat het werk naar het ERP of een ander systeem hoort:

U typt elke dag gegevens uit Excel over in het ERP, of andersom.
Het Excel-bestand is de enige plek waar een belangrijk gegeven staat, zoals de afgesproken prijzen, de actuele stuklijst of de echte voorraad.
Meerdere mensen werken tegelijk in hetzelfde bestand en overschrijven elkaars werk.
U moet kunnen laten zien wie wat wanneer heeft veranderd.
Het bestand wordt traag. Een werkblad heeft een grens van 1.048.576 rijen, maar problemen beginnen meestal veel eerder.
Een fout in het bestand kost direct geld, bijvoorbeeld in een prijs of een bankrekeningnummer.

Zet de gegevens dan in het systeem waar ze thuishoren, en laat Excel alleen lezen voor analyse. Kijk eerst wat het ERP zelf kan, zoals prijsafspraken per klant of een kruistabel voor klantartikelnummers. Meer voorbeelden van werk dat u zo kunt verplaatsen, staan in de gids bedrijfsprocessen automatiseren.

Besliskader

Vragen om intern te stellen

Welke richting gaat het op: van het ERP naar Excel, of van Excel naar het ERP?
Hoe vaak gebeurt het, en hoeveel regels per keer?
Wie maakt het Excel-bestand: een collega (bijvoorbeeld van engineering), een klant of een leverancier? En verandert de opbouw weleens?
Welke route biedt ons ERP: een importfunctie, een invoegtoepassing, een API? Vraag het de leverancier.
Wat moet er gebeuren met een regel die niet klopt, en wie beslist daarover?

Welke route past

Situatie en logische route
Situatie	Logische route
Rapport of controle op ERP-gegevens	Export, of Power Query als het ERP een gedocumenteerde ingang heeft
Af en toe een prijslijst van een leverancier of een reeks artikelen	Importfunctie van het ERP, met controles vooraf
Een stuklijst uit CAD of PDM, na elke vrijgave	Importfunctie van het ERP als die stuklijsten aankan; anders een koppeling met het PDM
Veel records in het ERP bijwerken door één medewerker	Invoegtoepassing van de leverancier, als die er is
Elke dag orders of andere bestanden van buiten	Koppeling, of een orderimport als er geen API is
ERP zonder import en zonder API	Software-robot, met monitoring en een test na elke update

De factoren die het zwaarst wegen, zijn de richting (lezen is veel minder riskant dan schrijven), hoe vaak het gebeurt, en wat uw ERP officieel ondersteunt. Externe hulp is zinvol als er dagelijks gegevens het ERP in gaan, als bestanden van veel verschillende klanten komen, of als een API nodig is. Bij de controles en de afwijkingen zit het verschil tussen een handig bestand en een betrouwbaar proces. Meer over koppelingen met een ERP leest u op de oplossingenpagina, bij ERP-koppelingen.

Conclusie

Excel en het ERP kunnen goed samenwerken, zolang het ERP de leidende bron blijft. Lezen uit het ERP is eenvoudig en weinig riskant. Schrijven naar het ERP vraagt een route die het ERP officieel ondersteunt, controles vóór de import en een vaste plek voor afwijkingen. Veel fouten komen niet uit de route, maar uit Excel zelf: weggevallen voorloopnullen, verkeerde kopieën en twee mensen in hetzelfde bestand. Ziet u die signalen elke week, dan hoort het werk waarschijnlijk niet meer in Excel.