Kopiëren Formules met behulp van een patroon

Stel dat je een werkblad dat een reeks formules in de cellen A1 bevat hebben: A3. Cel A1 de formule = Blad1! B4, cel A2 bevat = Blad1! B18 en cel A3 bevat = Blad1! B32. Mogelijk moet u dit patroon voort te zetten door de kolom, zodat A4 bevat = Blad1! B46, enz.

Het probleem is, als je gewoon kopiëren van de cellen, het patroon wordt niet voortgezet. In plaats daarvan worden de formules aangepast op basis opzichte van de doelcel om de broncel. Dus, als je plakt A1: A3 naar A4: A6, dan A4 zal = Blad1 B7 bevatten, dat is niet wat je wilt. (Dit gebeurt of je specifiek te kopiëren en plakken of vul de cellen door het slepen van de vulgreep.)

Er is geen manier om een ​​patroon blijven tijdens het kopiëren van een formule. In plaats daarvan moet je opnieuw hoe je samen de formule in de eerste plaats. Kijk bijvoorbeeld naar deze formule:

= INDIRECT ("Blad1 B!" & ((RIJ () - 1) * 14) 4)

Deze formule construeert een referentie gebaseerd op de positie van de cel waarin de formule wordt geplaatst. Als deze formule wordt geplaatst in cel A1, dan is de functie RIJ retourneert 1, de rij waarin de formule wordt geplaatst. Zo is de formule wordt dit:

= INDIRECT ("Blad1! B" & ((1-1) * 14) 4)
= INDIRECT ("Blad1! B" & (0 * 14) 4)
= INDIRECT ("Blad1! B" & 0 + 4)
= INDIRECT ("Blad1! B" & 4)
= INDIRECT ("Blad1! B4")

Wat teruggezonden de waarde Sheet1! B4, zoals aanvankelijk gezocht. Wanneer u deze formule kopiëren door de kolom echter de ROW functie geeft iets anders in elke rij. In feite, de functie RIJ wordt een manier om verhoging van de offset van elke formule met 14 rijen van de ene voordat het - net wat je wilde.

U kunt ook gebruik maken van een iets andere aanpak, dit keer met behulp van de functie VERSCHUIVING:

= VERSCHUIVING (! Blad1 $ B $ 4, (RIJ () - 1) * 14,0)

Deze formule pakt een getal gebaseerd op de rij waarin de formule is geplaatst (opnieuw met ROW functie) en offset van cel Sheet1! B4. Geplaatst in de eerste rij van een kolom en vervolgens gekopieerd naar beneden die kolom, geeft de formule waarden volgens het patroon gewenst.

Een andere benadering is om de gewenste formules direct creëren. U kunt dit het beste doen door de volgende stappen:

  1. Begin met een nieuw, leeg werkblad in een werkmap met een werkblad met de naam "Blad1."
  2. In cel A1 voer de formule = Blad1! B4.
  3. Omdat de formule patroon is om de 14 rijen, markeert het bereik A1: A14. Alleen de eerste cel moet de formule hebben; de andere 13 zijn leeg.
  4. Sleep de vulgreep (het kleine plein aan de rechterbenedenhoek van de selectie naar beneden voor een goede aantal rijen - misschien 1000 of zo het bereik A1: A1000 moet nog steeds worden gemarkeerd..
  5. Kies Bewerken | Ga naar | Special. Excel toont de Go To Special dialoogvenster. (Zie figuur 1.)

    Kopiëren Formules met behulp van een patroon

    Figuur 1. De Go To Special dialoogvenster.

  6. Klik op de Blanks optie-knop en klik vervolgens op OK. Alle blank (lege) cellen in de selectie geselecteerd.
  7. Kies Bewerken | Verwijderen | Shift Cellen Up /

Het resultaat is dat je eindigt met alleen de formules de gewenste patroon.

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (3067) is van toepassing op Microsoft Excel 97, 2000, 2002 en 2003.