Niet leidt tot aanpassing Referenties in formules

Iedereen weet kunt u een formule in Excel. (Wat zou een spreadsheet zijn zonder formules, na al?) Als je adres verwijzingen gebruiken in een formule, die verwijzingen worden automatisch bijgewerkt als u plaatst of cellen, rijen of kolommen te verwijderen en deze wijzigingen van invloed op het adres verwijzing op een bepaalde manier. Beschouw bijvoorbeeld de volgende eenvoudige formule:

= ALS (A7 = B7, "YES", "NO")

Als u een cel boven B7 plaatst, dan is de formule wordt automatisch aangepast door Excel zodat het lijkt zo uit:

= ALS (A7 = B8, "YES", "NO")

Wat als je niet wilt dat Excel de formule aan te passen, echter? Je zou kunnen proberen het toevoegen van enkele dollartekens naar het adres, maar dit alleen van invloed op adressen in formules die zijn later gekopieerd; het doesn? t invloed op de formule zelf als je cellen die de formule van invloed te voegen of te verwijderen.

De beste manier om de formule referenties? Niet-aanpassing te maken? is om de formule zelf wijzigen om ander werkblad functies gebruiken. Bijvoorbeeld, kon u deze formule in cel C7 te gebruiken:

= ALS (INDIRECT ("A" & RIJ (C7)) = INDIRECT ("B" & RIJ (C7)), "JA", "NO")

Wat deze formule doet is een adres construeren op basis van welke cel de formule weergegeven. De functie RIJ retourneert het rijnummer van de cel (C7 in dit geval dus de waarde 7 wordt teruggegeven) en dan de indirecte functie wordt gebruikt om te verwijzen de geconstrueerde adres, zoals de A7 en B7. Als u cellen boven A7 of B7 voegen (of verwijderen), wordt de verwijzing in cel C7 niet verstoord, omdat het gewoon vrolijk construeert een gloednieuw adres.

Een andere benadering is om de functie VERSCHUIVING gebruiken om een ​​vergelijkbaar type referentie te construeren:

= ALS (VERSCHUIVING ($ A $ 1, RIJ () - 1,0) = VERSCHUIVING ($ B $ 1, RIJ () - 1,0), "JA", "NO")

Deze formule ziet er gewoon naar waar het (in kolom C) en vergelijkt de waarden in de cellen die links daarvan. Deze formule is eveneens ongestoord als je toevallig te voegen of cellen te verwijderen in een van beide kolom A of B.

Een laatste benadering (en misschien wel de handigste één) is vernoemd formules gebruiken. Dit is een functie van Excel? S naamgeving mogelijkheden die zelden wordt gebruikt door de meeste mensen. Volg deze stappen:

  1. Selecteer cel C2.
  2. Kies Naam in het menu Invoegen en kies vervolgens Definieer in het submenu. Excel toont het definiëren Naam dialoogvenster. (Zie figuur 1.)

    Niet leidt tot aanpassing Referenties in formules

    Figuur 1. De Naam bepalen dialoogvenster.

  3. In het vak Namen in werkmap, voer de naam CompareMe. (U kunt een andere naam te gebruiken, als u dat wilt.)
  4. Wist u alles wat in het vak Verwijst naar te vervangen door de volgende formule:

    = ALS (A2 = B2, "YES", "NO")
  5. Klik op OK.

Op dit punt heb je? Ve gemaakt uw naam formule. U kunt nu gebruiken in een willekeurige cel in kolom C op deze manier:

= CompareMe

Het vergelijkt wat er in de twee cellen aan zijn linkerkant, net als uw originele formule is ontworpen om te doen. Beter nog, is de formule niet automatisch aangepast als u invoegt of cellen te verwijderen.

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (2876) is van toepassing op Microsoft Excel 97, 2000, 2002 en 2003. U kunt een versie van deze tip voor de ribbon-interface van Excel (Excel 2007 en later) vindt u hier: Non-aanpassen van verwijzingen in formules.