Soms willen we afwisselende cellen kopiëren uit een bereik of elke derde waarde of elke 4e waarde, enzovoort. In al deze gevallen zal het handmatig kopiëren een tijdrovende klus zijn. Dus wat is de oplossing? Hoe kopieer je de waarde van elke n-de rij? Is hier een formule voor?
Ja dat is er. We kunnen de OFFSET-functie van Excel gebruiken om waarden uit de n-de rijen te halen. Hoe? Laten we eens kijken.
=VERSCHUIVING(Eerste_cel_in_lijst,(RIJ(A1)*N)-1,0)
First_Cell_in_Range: het is de absolute referentie van de eerste cel in het lijstbereik waaruit u wilt kopiëren. Als uw lijst bijvoorbeeld in A5:A100 staat, dan Eerste_cel_in_bereik is $A$5. Neem geen kolomkop op.
N: het is van het nummer dat u een veelvoudswaarde krijgt. Als u bijvoorbeeld elke 2e waarde uit de lijst wilt krijgen, dan is N 2. Voor elke 3e waarde is dit 3 enzovoort.
RIJ (A1): het voor het verkrijgen van veelvouden van N.
Laten we nu een voorbeeld bekijken, en dan zal ik uitleggen hoe het werkt.
Voorbeeld Kopieer elke tweede waarde uit een lijst in Excel
Hier hebben we een lijst met items. We moeten elke tweede waarde uit de lijst in een nieuw bereik kopiëren.
Dus, hoe doen we dat?
Omdat we 11 items op de lijst hebben, krijgen we vijf alternatieve items (2, 4, 5, 6, 10).
Schrijf de onderstaande formule in E4 en kopieer de cellen naar beneden.
=OFFSET($B$4,(RIJ(A1)*2)-1,0)
Hier is $ B $ 4 de eerste cel in het lijstbereik en omdat we elke tweede (alternatieve) waarde uit de lijst willen kopiëren, hebben we onze N als 2.
Gebruik deze formule als u elk derde item wilt kopiëren.
=OFFSET($B$4,(RIJ(A1)*3)-1,0)
Voor elke 4e:
=OFFSET($B$4,(RIJ(A1)*4)-1,0)
Enzovoort. U kunt elke N-waarde van a kopiëren met behulp van deze Excel-formule.
Hoe het werkt
De formule volbrengt deze taak met behulp van twee Excel-functies, OFFSET en ROW.
De functie OFFSET retourneert de waarde van de rij- en kolomoffset van een bepaalde cel.
De functie RIJ retourneert het rijnummer van de opgegeven referentie.
Van binnenuit begint RIJ(A1) als resultaat 1. Aangezien A1 bij de 1e rij hoort.
(RIJ(A1)*2 het wordt 2.
(RIJ(A1)*2)-1, het wordt (1*2)-1=1.
Wanneer we de formule kopiëren, verandert de verwijzing in RIJ in A2, A3, A4, enzovoort. Voor A2 krijgen we de uiteindelijke waarde (2*2)-1=3. Voor A3 krijgen we (3*3)-1=5 enzovoort.
Nu is onze formule in E4 opgelost tot =OFFSET($B$4,1,0). Dit retourneert de waarde 1 rij hieronder van B4, wat de tweede waarde van B4 is.
In E4 is de formule =OFFSET($B$4,3,0), wat de vierde waarde van B4 is. Op dezelfde manier krijgen we alle afwisselende kosten.
Nu weet u hoe u elke N-waarde uit de lijst in Excel kunt kopiëren. Dit is de techniek die ik ken. Als je een andere truc kent om de n-de cel uit een bereik te kopiëren, deel deze dan in de comments hieronder. Als u vragen heeft over deze of een andere functie van Excel 2019, 2016, 2013 en 2010, gebruik dan de opmerkingensectie.
Hoe de laatste prijs in Excel op te halen
De eerste waarde ophalen in een lijst die groter/kleiner is dan een opgegeven waarde
De hele rij van een overeenkomende waarde ophalen
Populaire artikels:
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?