Noen ganger ønsker vi å kopiere vekslende celler fra et område eller hver tredje verdi eller hver fjerde verdi, og så videre. I alle disse tilfellene vil kopiering manuelt være en tømmende oppgave. Så hva er løsningen? Hvordan kopierer du verdien fra hver niende rad? Finnes det en formel for dette?
Ja det er. Vi kan bruke excels OFFSET -funksjon for å hente verdier fra niende rad. Hvordan? La oss se det.
= FORSKYTT (First_Cell_in_List, (RAD (A1)*N)-1,0)
First_Cell_in_Range: Det er den absolutte referansen til den første cellen i listeområdet du vil kopiere fra. For eksempel, hvis listen din er i A5: A100, da First_Cell_in_Range er $ A $ 5. Ikke ta med kolonneoverskriften.
N: det er av tall som du vil få multiplaverdi. For eksempel, hvis du vil få hver andre verdi fra listen, er N 2. For hver tredje verdi er den 3 og så videre.
RAD (A1): det for å få multipler av N.
La oss nå se et eksempel, og så vil jeg forklare hvordan det fungerer.
Eksempel Kopier annenhver verdi fra en liste i Excel
Her har vi en liste over varer. Vi må kopiere hver andre verdi fra listen i et nytt område.
Så, hvordan gjør vi det?
Siden vi har 11 elementer på listen, får vi fem alternative varer (2, 4, 5, 6, 10).
Skriv formelen nedenfor i E4 og kopier ned cellene.
= OFFSET ($ B $ 4, (RAD (A1)*2)-1,0)
Her er $ B $ 4 den første cellen i listeområdet, og siden vi ønsker å kopiere hver andre (alternative) verdi fra listen, har vi vår N som 2.
Hvis du vil kopiere hvert tredje element, bruker du denne formelen.
= OFFSET ($ B $ 4, (RAD (A1)*3)-1,0)
For hver fjerde:
= OFFSET ($ B $ 4, (RAD (A1)*4)-1,0)
Og så videre. Du kan kopiere hvilken som helst Nth -verdi fra en ved hjelp av denne excelformelen.
Hvordan det fungerer
Formelen utfører denne oppgaven ved å bruke to excel -funksjoner, OFFSET og ROW.
OFFSET -funksjonen returnerer verdien fra rad- og kolonneforskyvning fra en gitt celle.
ROW -funksjonen returnerer radnummeret til gitt referanse.
Fra innsiden vil RAD (A1) returnere 1. Siden A1 tilhører 1. rad.
(RAD (A1)*2 det blir 2.
(RAD (A1)*2)-1, det blir (1*2) -1 = 1.
Når vi kopierer ned formelen, vil referansen i RAD endres som A2, A3, A4, og så videre. For A2 får vi den endelige verdien (2*2) -1 = 3. For A3 får vi (3*3) -1 = 5 og så videre.
Nå løste formelen vår i E4 oss til = OFFSET ($ B $ 4,1, 0). Dette vil returnere verdien 1 rad nedenfor fra B4, som er den andre verdien fra B4.
I E4 vil formelen være = OFFSET ($ B $ 4,3,0), som er den fjerde verdien fra B4. På samme måte får vi alle vekslende kostnader.
Nå vet du hvordan du kopierer hver N -verdi fra listen i excel. Dette er teknikken jeg kjenner. Hvis du kjenner et annet triks for å kopiere nth celle fra et område, kan du gjerne dele den i kommentarfeltet nedenfor. Hvis du har spørsmål angående denne eller andre funksjoner i Excel 2019, 2016, 2013 og 2010, kan du bruke kommentarfeltet.
Hvordan hente siste pris i Excel
Henter den første verdien i en liste som er større / mindre enn en spesifisert verdi
Slik henter du hele raden med en matchet verdi
Populære artikler:
VLOOKUP -funksjonen i Excel
COUNTIF i Excel 2016
Slik bruker du SUMIF -funksjonen i Excel