Hoe vervang je bereiknamen door celverwijzingen in Excel?
Bij het werken met benoemde bereiken in Excel, zul je merken dat ze handig zijn voor leesbaarheid en formulebeheer. Er zijn echter situaties waarin je bereiknamen wilt verwijderen en daadwerkelijke celverwijzingen wilt herstellen, vooral bij het delen van bestanden, probleemoplossing of het standaardiseren van sjablonen. In dit artikel behandelen we verschillende manieren om benoemde bereiken te vervangen door hun overeenkomstige celverwijzingen in Excel.
Vervang bereiknamen door celverwijzingen met VBA-code
Snel en gemakkelijk bereiknamen vervangen door celverwijzingen met Kutools voor Excel
Bijvoorbeeld, in dit werkblad zal ik het bereik A2:A6 de naam 'saleprice' geven, en B2 de naam 'discount', en vervolgens beide bereiknamen gebruiken in een formule om de nieuwe prijs te berekenen. Zie screenshots:
Na het volgen van deze snelle trucs, wordt de bereiknaam vervangen door een celverwijzing zoals hieronder te zien is:
Vervang bereiknamen door celverwijzingen met VBA-code
Door VBA te gebruiken om formules bereiknamen te vervangen door celverwijzingen, kun je dit stap voor stap doen zoals hieronder beschreven:
1. Selecteer het bereik met de formules waarvan je de namen wilt vervangen door celverwijzingen.
2. Druk op Alt + F11 om een nieuw Microsoft Visual Basic for Applications venster te openen, klik dan op Invoegen > Module, en kopieer en plak de volgende code in de module:
VBA-code: Vervang bereiknamen met absolute verwijzing
Sub AbsoleteNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
For Each xName In ThisWorkbook.Names
If InStr(Rng.Formula, xName.Name) > 0 Then
Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(VBA.Replace(xName.RefersTo, "=", ""), "$", ""))
End If
Next
Next
End SubVBA-code: Vervang bereiknamen met relatieve verwijzing
Sub ReplaceNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
For Each xName In ThisWorkbook.Names
If InStr(Rng.Formula, xName.Name) > 0 Then
Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(xName.RefersTo, "=", ""))
End If
Next
Next
End Sub3. Druk vervolgens op F5 om de code uit te voeren. Een dialoogvenster verschijnt om een bereik te selecteren om te vervangen. Zie screenshot:
4. Klik vervolgens op OK. Alle geselecteerde formulebereiknamen zijn vervangen.
Snel en gemakkelijk bereiknamen vervangen door celverwijzingen met Kutools voor Excel
Hoewel dit handmatig doen of via VBA tijdrovend en foutgevoelig kan zijn, biedt Kutools voor Excel een een-klik-oplossing om het hele proces te vereenvoudigen. Als je Kutools voor Excel hebt, helpt de utility Namen converteren naar bereik je snel en gemakkelijk bereiknamen te vervangen door celverwijzingen.
1. Pas deze utility toe door te klikken op Kutools > Naamgereedschappen > Namen converteren naar bereik.
2. Er verschijnt een dialoogvenster, klik op het tabblad Bereik en selecteer vervolgens het bereik dat je wilt vervangen door celverwijzingen uit Gebaseerd op bereik. Klik vervolgens op de knop Vervangen. Zie screenshot:
3. Er verschijnt een andere promptbox om je te vertellen hoeveel formules gewijzigd zijn en hoeveel ongewijzigd zijn. Klik op OK, en de bereiknamen in het geselecteerde bereik zijn vervangen door celverwijzingen.
🔚 Conclusie
Of je nu te maken hebt met een paar formules of een heel werkboek, het vervangen van benoemde bereiken door daadwerkelijke celverwijzingen is een geweldige manier om transparantie, compatibiliteit en eenvoudige samenwerking te verbeteren.
- Voor gevorderde gebruikers biedt VBA een flexibele en programmeerbare oplossing om het vervangingsproces te automatiseren.
- Voor iedereen biedt Kutools voor Excel de snelste en gebruiksvriendelijkste manier om de taak in slechts een paar klikken te voltooien - geen codering of formulebewerking nodig.
Door de methode te kiezen die het beste past bij je workflow, kun je je formules efficiënt opruimen en ervoor zorgen dat je spreadsheets gemakkelijk te begrijpen en onderhouden blijven. Als je geïnteresseerd bent in meer Excel tips en trucs, onze website biedt duizenden tutorials om je te helpen Excel onder de knie te krijgen.
Gerelateerd artikel:
Verander celverwijzingen in formules naar bereiknamen in Excel
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen