Täytä tyhjät solut seuraavaan arvoon Excelissä (3 helppoa tapaa)

Katso video - täytä Excelissä

Usein saatat kohdata tietojoukon, jossa vain yksi solu on täynnä tietoja ja sen alapuolella olevat solut ovat tyhjiä seuraavaan arvoon asti.

Jotain alla olevan kuvan mukaisesti:

Vaikka tämä muoto toimii joillekin ihmisille, tällaisten tietojen ongelma on se, että et voi käyttää niitä pivot -taulukoiden luomiseen tai laskemiseen.

Ja tähän on helppo korjata!

Tässä opetusohjelmassa näytän sinulle, kuinka nopeasti täytä Excelin solut seuraavaan täytettyyn arvoon asti.

Voit tehdä tämän helposti yksinkertaisella Go-To-erityisellä valintaikkunatekniikalla, VBA: lla tai Power Queryllä.

Aloitetaan siis!

Menetelmä 1 - Täytä käyttämällä Siirry erikoiseen + kaava

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat täyttää tiedot sarakkeissa A ja B.

Sarakkeessa B pyritään täyttämään 'Tulostin' sen alla olevaan viimeiseen tyhjään soluun asti, ja kun 'Skanneri' alkaa, täytä sitten 'Skanneri' alla olevissa soluissa, kunnes solut ovat tyhjiä.

Käytettävissä olevien vaiheiden alta siirry erikoissivulle ja valitse kaikki tyhjät solut ja täytä ne yksinkertaisella kaavalla:

1. Valitse tiedot, jotka haluat täyttää (esimerkissämme A1: D21)
2. Siirry "Etusivu" -välilehdelle

3. Napsauta Muokkaus-ryhmässä Etsi ja valitse -kuvaketta (tämä näyttää lisää vaihtoehtoja avattavasta valikosta)

4. Napsauta "Siirry" -vaihtoehtoa

5. Napsauta avautuvassa Siirry-valintaikkunassa Erikoispainiketta. Tämä avaa "Siirry erityiseen" -valintaikkunan

6. Valitse Siirry erikoisvalintaikkunaan Tyhjät

7. Napsauta OK

Yllä olevat vaiheet valitsevat kaikki tietojoukon tyhjät solut (kuten alla on esitetty).

Valituissa tyhjissä soluissa huomaat, että yksi solu on vaaleampi kuin muu valinta. Tämä solu on aktiivinen solu, johon aiomme syöttää kaavan.

Älä huolehdi siitä, missä solu on valinnassa, sillä menetelmämme toimisi joka tapauksessa.

Alla on vaiheet, joilla voit täyttää valitut tyhjät solut:

1. Paina näppäimistön (=) -näppäintä. Tämä lisää yhtäsuuruusmerkin aktiiviseen soluun
2. Paina ylänuolinäppäintä. Tämä lisää solun viittauksen aktiivisen solun yläpuolelle. Esimerkiksi meidän tapauksessamme B3 on aktiivinen solu ja kun teemme nämä kaksi vaihetta, se tulee soluun = B2
3. Pidä Control -näppäintä painettuna ja paina Enter -näppäintä

Yllä olevat vaiheet lisäisivät automaattisesti kaikki tyhjät solut, joiden arvo on niiden yläpuolella.

Vaikka tämä saattaa tuntua liian monelta vaiheelta, kun olet perehtynyt tähän menetelmään, voit täyttää tiedot Excelissä nopeasti muutamassa sekunnissa.

Nyt on vielä kaksi asiaa, jotka sinun on huolehdittava tätä menetelmää käytettäessä.

Muunna kaavat arvoiksi

Ensimmäinen on varmistaa, että muunnat kaavat arvoiksi (jotta sinulla on staattisia arvoja ja asiat eivät sotkeudu, jos muutat tietoja tulevaisuudessa).

Alla on video, joka näyttää kuinka voit nopeasti muuntaa kaavat arvoiksi Excelissä.

Muuta päivämäärän muotoa

Jos käytät datassa päivämääriä (kuten minä esimerkkitiedoissa), huomaat, että päivämääräsarakkeen täytetyt arvot ovat numeroita eivätkä päivämääriä.

Jos päivämääräsarakkeen tulos on halutussa päivämäärämuodossa, olet hyvä eikä sinun tarvitse tehdä mitään muuta.

Mutta jos nämä eivät ole oikeassa päivämäärämuodossa, sinun on tehtävä pieni säätö. Vaikka sinulla on oikeat arvot, sinun tarvitsee vain muuttaa muotoa niin, että se näkyy päivämääränä solussa.

Alla on vaiheet tämän tekemiseen:

1. Valitse sarake, jossa on päivämäärät
2. Napsauta Etusivu -välilehteä
3. Napsauta Numero-ryhmässä avattavaa Muoto-valikkoa ja valitse sitten päivämäärän muoto.

Jos sinun on tehtävä tämä täyttö aina silloin tällöin, suosittelen käyttämään tätä Go-To-erikois- ja kaavatekniikkaa.

Vaikka siinä on muutama vaihe, se on yksinkertainen ja antaa sinulle tuloksen suoraan tietojoukossasi.

Mutta jos sinun on tehtävä tämä melko usein, ehdotan, että tarkastelet seuraavaksi käsiteltyjä VBA- ja Power Query -menetelmiä

Menetelmä 2 - Täytä yksinkertaisella VBA -koodilla

Voit käyttää todella yksinkertaista VBA -makrokoodia täyttämään Excelin solut nopeasti viimeiseen tyhjään arvoon asti.

Sinun tarvitsee vain lisätä VBA -koodi tiedostoon kerran ja sitten voit helposti käyttää koodia uudelleen useita kertoja samassa työkirjassa tai jopa useissa järjestelmän työkirjoissa.

Alla on VBA -koodi, joka kulkee valinnan jokaisen solun läpi ja täyttää kaikki tyhjät solut:

'Tämän VBA -koodin on luonut Sumit Bansal TrumpExcel.com -sivustosta Sub FillDown () Jokaiselle valitulle solulle If cell = "" then cell.FillDown End If Next End Sub

Yllä oleva koodi käyttää For -silmukkaa kulkemaan valinnan jokaisen solun läpi.

For-silmukassa olen käyttänyt If-then-ehtoa, joka tarkistaa, onko solu tyhjä vai ei.

Jos solu on tyhjä, se täytetään yllä olevan solun arvolla ja jos se ei ole tyhjä, for -silmukka yksinkertaisesti jättää solun huomiotta ja siirtyy seuraavaan soluun.

Nyt kun sinulla on VBA -koodi, näytän sinulle, mihin tämä koodi sijoitetaan Excelissä:

1. Valitse tiedot, jotka haluat täyttää
2. Napsauta Kehittäjä -välilehteä

3. Napsauta Visual Basic -kuvaketta (tai voit käyttää pikanäppäintä ALT + F11). Tämä avaa Visual Basic Editorin Excelissä

4. Visual Basic Editorin vasemmalla puolella olisi Project Explorer. Jos et näe sitä, napsauta valikon Näytä -vaihtoehtoa ja valitse sitten Project Explorer

5. Jos sinulla on useita Excel -työkirjoja auki, Project Explorer näyttää kaikki työkirjan nimet. Etsi työkirjan nimi, josta sinulla on tiedot.
6. Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia, siirry Lisää-kohtaan ja valitse sitten Moduuli. Tämä lisää uuden moduulin kyseiselle työkirjalle

7. Kaksoisnapsauta yllä olevassa vaiheessa juuri lisäämääsi moduulia. Se avaa kyseisen moduulin koodi -ikkunan

8. Kopioi ja liitä VBA-koodi koodi-ikkunaan

9. Aseta kohdistin mihin tahansa koodiin ja suorita sitten makrokoodi napsauttamalla työkalurivin vihreää painiketta tai käyttämällä pikanäppäintä F5

Yllä olevat vaiheet suorittavat VBA -koodin ja tietosi täytetään.

Jos haluat käyttää tätä VBA-koodia uudelleen tulevaisuudessa, sinun on tallennettava tämä tiedosto makroyhteensopivana Excel-työkirjana (.XLSM-laajennuksella)

Toinen asia, jonka voit tehdä, on lisätä tämä makro pikakäyttötyökalupalkkiisi, joka on aina näkyvissä ja sinulla on pääsy tähän makroon yhdellä napsautuksella (työkirjassa, jossa koodi on taustaohjelmassa).

Joten seuraavan kerran, kun sinulla on tietoja, jotka sinun on täytettävä, sinun on vain tehtävä valinta ja napsautettava sitten pikavalintatyökalurivin makropainiketta.

Voit myös lisätä tämän makron henkilökohtaiseen makro -työkirjaan ja käyttää sitä sitten missä tahansa järjestelmän työkirjassa.

Napsauta tätä saadaksesi lisätietoja henkilökohtaisista makrotyökirjoista ja koodin lisäämisestä niihin.

Tapa 3 - Täytä Power Queryn avulla

Power Queryssä on sisäänrakennettu toiminto, jonka avulla voit täyttää tiedot yhdellä napsautuksella.

Power Queryn käyttöä suositellaan, kun käytät sitä joka tapauksessa tietojen muuttamiseen tai useiden laskentataulukoiden tai useiden työkirjojen tietojen yhdistämiseen.

Osana nykyistä työnkulkua voit täyttää tyhjät solut nopeasti Power Queryn täyttövaihtoehdolla.

Power Queryn käyttämiseksi on suositeltavaa, että tietosi ovat Excel -taulukkomuodossa. Jos et voi muuntaa tietojasi Excel -taulukkoksi, sinun on luotava tiedoille nimetty alue ja käytettävä sitten nimettyä aluetta Power Queryssä.

Alla on tietojoukko, jonka olen jo muuttanut Excel -taulukkoksi. Voit tehdä sen valitsemalla tietojoukon, siirtymällä Lisää -välilehteen ja napsauttamalla sitten Taulukko -kuvaketta.

Alla olevat vaiheet Power Queryn avulla tietojen täyttämiseen seuraavaan arvoon:

1. Valitse mikä tahansa tietojoukon solu
2. Napsauta Data -välilehteä

3. Napsauta Hae ja muunna tiedot -ryhmässä Arkilta. Tämä avaa Power Query -editorin. Huomaa, että tyhjät solut näyttävät arvon "null" Power Queryssä

4. Valitse Power Query -editorissa sarakkeet, jotka haluat täyttää. Voit tehdä tämän pitämällä Control -näppäintä painettuna ja napsauttamalla sitten valittavien sarakkeiden otsikkoa. Esimerkissämme se olisi Päivämäärä ja Tuote -sarakkeet.
5. Napsauta hiiren kakkospainikkeella mitä tahansa valittua otsikkoa

6. Siirry "Täytä" -vaihtoehtoon ja napsauta sitten "Alas". Tämä täyttäisi kaikkien tyhjien solujen tiedot

7. Napsauta Tiedosto -välilehteä ja napsauta sitten Sulje ja lataa. Tämä lisää uuden laskentataulukon työkirjaan tuloksena olevan taulukon kanssa

Vaikka tämä menetelmä saattaa kuulostaa hieman yliarvostetulta, yksi suuri etu Power Queryn käytöstä on, että sen avulla voit nopeasti päivittää tuloksena olevat tiedot, jos alkuperäiset tiedot muuttuvat.

Jos esimerkiksi lisäät lisää tietueita alkuperäisiin tietoihin, sinun ei tarvitse tehdä kaikkea yllä olevaa uudelleen. Voit yksinkertaisesti napsauttaa tuloksena olevaa taulukkoa hiiren kakkospainikkeella ja napsauttaa sitten Päivitä.

Vaikka Power Query -menetelmä toimii hyvin, on parasta käyttää sitä, kun käytät jo Power Queryä työnkulussa.

Jos sinulla on vain tietojoukko, johon haluat täyttää tyhjät solut, olisi helpompaa käyttää Siirry -erikoismenetelmää tai VBA -menetelmää (yllä).

Joten nämä ovat kolme yksinkertaista tapaa, joita voit käyttää täytä tyhjät solut alas seuraavaan arvoon Excelissä.

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.