Joskus haluamme kopioida vuorottelevia soluja alueelta tai joka kolmas arvo tai joka neljäs arvo jne. Kaikissa näissä tapauksissa manuaalinen kopiointi on tyhjentävä tehtävä. Joten mikä on ratkaisu? Kuinka kopioit arvon jokaisesta n: stä rivistä? Onko tähän olemassa kaava?
Kyllä on. Voimme käyttää excelin OFFSET -toimintoa arvojen saamiseksi n. Riviltä. Miten? Katsotaanpa.
= SIIRTÖ (First_Cell_in_List, (RIVI (A1)*N)-1,0)
First_Cell_in_Range: Se on luetteloalueen ensimmäisen solun ehdoton viite, josta haluat kopioida. Jos luettelosi on esimerkiksi A5: A100, niin First_Cell_in_Range On $ 5 $. Älä lisää sarakkeen otsikkoa.
N: se on numero, jonka arvon saat moninkertaisena. Jos esimerkiksi haluat saada jokaisen 2. arvon luettelosta, N on 2. Joka 3. arvo on 3 ja niin edelleen.
RIVI (A1): se moninkertaisen N: n saamiseksi.
Katsotaan nyt esimerkkiä, ja sitten selitän, miten se toimii.
Esimerkki Kopioi joka toinen arvo Excelin luettelosta
Tässä meillä on luettelo kohteista. Meidän on kopioitava joka toinen arvo luettelosta uudella alueella.
Joten miten teemme sen?
Koska luettelossa on 11 kohdetta, saamme viisi vaihtoehtoista kohdetta (2, 4, 5, 6, 10).
Kirjoita alla oleva kaava kohtaan E4 ja kopioi solut alas.
= SIIRTO ($ B $ 4, (RIVI (A1)*2)-1,0)
Tässä $ B $ 4 on luetteloalueen ensimmäinen solu, ja koska haluamme kopioida joka toinen (vaihtoehtoinen) arvo luettelosta, meillä on N kuin 2.
Jos haluat kopioida joka kolmas kohde, käytä tätä kaavaa.
= SIIRTO ($ B $ 4, (RIVI (A1)*3)-1,0)
Joka neljäs:
= SIIRTO ($ B $ 4, (RIVI (A1)*4)-1,0)
Ja niin edelleen. Voit kopioida minkä tahansa n: nnen arvon a: sta käyttämällä tätä Excel -kaavaa.
Kuinka se toimii
Kaava suorittaa tämän tehtävän kahdella Excel -toiminnolla, OFFSET ja ROW.
OFFSET -funktio palauttaa tietyn solun rivin ja sarakkeen siirtymän arvon.
RIVI -funktio palauttaa annetun viitteen rivinumeron.
Sisältä alkaen RIVI (A1) palauttaa 1. Koska A1 kuuluu ensimmäiselle riville.
(RIVI (A1)*2 siitä tulee 2.
(RIVI (A1)*2)-1, siitä tulee (1*2) -1 = 1.
Kun kopioimme kaavan alas, rivin viittaus muuttuu muotoon A2, A3, A4 ja niin edelleen. A2: lle saamme lopullisen arvon (2*2) -1 = 3. A3: lle saamme (3*3) -1 = 5 ja niin edelleen.
Nyt E4: n kaava ratkaistiin = OFFSET ($ B $ 4,1, 0). Tämä palauttaa arvon 1 rivi alla B4: stä, joka on toinen arvo B4: stä.
E4: ssä kaava on = OFFSET ($ B $ 4,3,0), joka on B4: n neljäs arvo. Samoin saamme kaikki vaihtuvat kustannukset.
Nyt tiedät kuinka kopioida jokaisen n: nnen arvon Excel -luettelosta. Tämä on tekniikka, jonka tiedän. Jos tiedät muita temppuja n: nnen solun kopioimiseksi alueelta, voit jakaa sen alla olevassa kommenttiosiossa. Jos sinulla on kysyttävää tästä tai muista Excel 2019: n, 2016, 2013 ja 2010: n toiminnoista, käytä kommentteja.
Kuinka hakea uusin hinta Excelissä
Ensimmäisen arvon hakeminen luettelosta, joka on suurempi / pienempi kuin määritetty arvo
Kuinka hakea täsmäytetyn arvon koko rivi
Suosittuja artikkeleita:
VLOOKUP -toiminto Excelissä
COUNTIF Excel 2016: ssa
SUMIF -toiminnon käyttäminen Excelissä