Skatieties video - aizpildiet programmā Excel
Daudzas reizes jūs varat saskarties ar datu kopu, kurā tikai viena šūna ir piepildīta ar datiem, un šūnas zem tās ir tukšas līdz nākamajai vērtībai.
Kaut kas, kā parādīts zemāk:
Lai gan šis formāts darbojas dažiem cilvēkiem, šāda veida datu problēma ir tāda, ka jūs nevarat to izmantot, lai izveidotu rakurstabulas vai izmantotu to aprēķinos.
Un tam ir vienkāršs risinājums!
Šajā apmācībā es jums parādīšu, kā ātri aizpildiet šūnas programmā Excel līdz nākamajai aizpildītajai vērtībai.
To var viegli izdarīt, izmantojot vienkāršu pāreju uz īpašu dialoglodziņa paņēmienu, VBA vai Power Query.
Tātad, sāksim!
1. metode - Aizpildiet, izmantojot Pāriet uz īpašo + formulu
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties aizpildīt datus A slejā un B slejā.
B slejā mērķis ir aizpildīt “Printeris” līdz pēdējai tukšajai šūnai zem tā, un tad, kad sākas “Skeneris”, pēc tam aizpildiet “Skeneris” zemāk esošajās šūnās, līdz šūnas ir tukšas.
Zemāk norādītās darbības dodieties uz īpašo, lai atlasītu visas tukšās šūnas un aizpildītu, izmantojot vienkāršu formulu:
- Atlasiet datus, kurus vēlaties aizpildīt (mūsu piemērā A1: D21)
- Dodieties uz cilni Sākums
- Rediģēšanas grupā noklikšķiniet uz ikonas “Atrast un atlasīt” (nolaižamajā izvēlnē tiks parādītas vēl dažas iespējas)
- Noklikšķiniet uz opcijas “Iet uz”
- Atvērtajā dialoglodziņā Go-To noklikšķiniet uz pogas Special. Tiks atvērts dialoglodziņš “Doties uz īpašo”
- Dialoglodziņā Pāriet uz īpašo noklikšķiniet uz Tukšas vietas
- Noklikšķiniet uz Labi
Iepriekš minētās darbības atlasītu visas tukšās šūnas datu kopā (kā parādīts zemāk).
Atlasītajās tukšajās šūnās jūs pamanīsit, ka viena šūna ir gaišāka nekā pārējā atlase. Šī šūna ir aktīvā šūna, kurā mēs ievadīsim formulu.
Neuztraucieties par to, kur šūna atrodas atlasē, jo mūsu metode jebkurā gadījumā darbotos.
Tālāk ir norādītas darbības, lai aizpildītu datus atlasītajās tukšajās šūnās:
- Nospiediet tastatūras taustiņu vienāds ar (=). Tādējādi aktīvajā šūnā tiks ievietota vienādības zīme
- Nospiediet augšupvērsto bultiņu. Tas ievietos šūnas atsauci virs aktīvās šūnas. Piemēram, mūsu gadījumā B3 ir aktīvā šūna, un, veicot šīs divas darbības, tā šūnā ieraksta = B2
- Turiet nospiestu vadības taustiņu un nospiediet taustiņu Enter
Iepriekš minētās darbības automātiski ievietos visas tukšās šūnas ar vērtību virs tām.
Lai gan tas var šķist pārāk daudz darbību, tiklīdz esat apguvis šo metodi, dažu sekunžu laikā varēsit ātri aizpildīt datus programmā Excel.
Tagad, lietojot šo metodi, ir jārūpējas par vēl divām lietām.
Pārvērst formulas par vērtībām
Pirmais ir pārliecināties, ka formulas tiek pārvērstas par vērtībām (lai jums būtu statiskas vērtības un lietas nesajauktos, ja turpmāk mainītu datus).
Zemāk ir video, kas parādīs, kā ātri konvertēt formulas uz Excel vērtībām.
Mainiet datuma formātu
Ja savos datos izmantojat datumus (kā es esmu datu piemēros), pamanīsit, ka datuma slejā aizpildītās vērtības ir skaitļi, nevis datumi.
Ja datuma slejas izvade ir vēlamajā datuma formātā, jums viss ir kārtībā un nekas cits nav jādara.
Bet, ja tie nav pareizajā datuma formātā, jums ir jāveic neliela korekcija. Kamēr jums ir pareizās vērtības, jums vienkārši jāmaina formāts, lai tas šūnā tiktu parādīts kā datums.
Tālāk ir norādītas darbības, kā to izdarīt:
- Atlasiet kolonnu, kurā ir datumi
- Noklikšķiniet uz cilnes Sākums
- Grupā Cipars noklikšķiniet uz nolaižamās izvēlnes Formāts un pēc tam atlasiet datuma formātu.
Ja jums ik pa laikam jāveic šī aizpildīšana, iesaku izmantot šo Go-To īpašo un formulas tehniku.
Lai gan tam ir dažas darbības, tas ir vienkārši un sniedz rezultātu tieši jūsu datu kopā.
Bet, ja jums tas jādara diezgan bieži, es iesaku jums apskatīt VBA un Power Query metodes, kas apskatītas tālāk
2. metode - aizpildiet, izmantojot vienkāršu VBA kodu
Jūs varat izmantot patiešām vienkāršu VBA makro kodu, lai ātri aizpildītu šūnas programmā Excel līdz pēdējai tukšajai vērtībai.
VBA kods ir jāpievieno failam tikai vienu reizi, un tad jūs varat viegli atkārtoti izmantot kodu vairākas reizes vienā darbgrāmatā vai pat vairākās darbgrāmatās savā sistēmā.
Zemāk ir VBA kods, kas iet cauri katrai atlases šūnai un aizpilda visas tukšās šūnas:
'Šo VBA kodu izveidoja Sumits Bansals no TrumpExcel.com Sub FillDown () Katrai atlasītajai šūnai If cell = "" then cell.FillDown End If Next End Sub
Iepriekš minētais kods izmanto cilpu For, lai izietu cauri katrai atlases šūnai.
Cilnē For es izmantoju nosacījumu If-then, kas pārbauda, vai šūna ir tukša.
Ja šūna ir tukša, tā tiek aizpildīta ar vērtību no iepriekš minētās šūnas, un, ja tā nav tukša, for cilpa vienkārši ignorē šūnu un pāriet uz nākamo.
Tagad, kad jums ir VBA kods, ļaujiet man parādīt, kur ievietot šo kodu programmā Excel:
- Atlasiet datus, kurus vēlaties aizpildīt
- Noklikšķiniet uz cilnes Izstrādātājs
- Noklikšķiniet uz ikonas Visual Basic (vai arī varat izmantot īsinājumtaustiņu ALT + F11). Tādējādi programmā Excel tiks atvērts Visual Basic redaktors
- Visual Basic redaktora kreisajā pusē jums būs Project Explorer. Ja jūs to neredzat, izvēlnē noklikšķiniet uz opcijas Skatīt un pēc tam noklikšķiniet uz Project Explorer
- Ja jums ir atvērtas vairākas Excel darbgrāmatas, Project Explorer parādīs visus darbgrāmatu nosaukumus. Atrodiet darbgrāmatas nosaukumu, kurā ir dati.
- Ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta, dodieties uz Ievietot un pēc tam noklikšķiniet uz Modulis. Tādējādi šai darbgrāmatai tiks ievietots jauns modulis
- Veiciet dubultklikšķi uz “moduļa”, kuru tikko ievietojāt iepriekš minētajā darbībā. Tas atvērs šī moduļa koda logu
- Kopējiet un ielīmējiet VBA kodu koda logā
- Novietojiet kursoru jebkurā koda vietā un pēc tam palaidiet makro kodu, rīkjoslā noklikšķinot uz zaļās pogas vai izmantojot īsinājumtaustiņu F5
Iepriekš minētās darbības izpildīs VBA kodu un jūsu dati tiks aizpildīti.
Ja nākotnē šo VBA kodu vēlaties izmantot vēlreiz, šis fails ir jāsaglabā kā Excel darbgrāmata ar makro-iespējotu saturu (ar paplašinājumu .XLSM)
Vēl viena lieta, ko varat darīt, ir pievienot šo makro ātras piekļuves rīkjoslai, kas vienmēr ir redzama, un jums būs piekļuve šim makro ar vienu klikšķi (darbgrāmatā, kuras kods atrodas aizmugurē).
Tātad, nākamreiz, kad jums būs jāaizpilda dati, viss, kas jums jādara, ir jāizvēlas un pēc tam noklikšķiniet uz makro pogas ātrās piekļuves rīkjoslā.
Šo makro varat pievienot arī personīgajai makro darbgrāmatai un pēc tam izmantot jebkurā darbgrāmatā savā sistēmā.
Noklikšķiniet šeit, lai uzzinātu vairāk par personīgajām makro darbgrāmatām un to, kā tām pievienot kodu.
3. metode - aizpildiet, izmantojot Power Query
Power Query ir iebūvēta funkcionalitāte, kas ļauj aizpildīt informāciju ar vienu klikšķi.
Power Query lietošana ir ieteicama, ja vienalga to izmantojat, lai pārveidotu savus datus vai apvienotu datus no vairākām darblapām vai vairākām darbgrāmatām.
Esošās darbplūsmas ietvaros varat izmantot Power Query aizpildīšanas opciju, lai ātri aizpildītu tukšās šūnas.
Lai izmantotu Power Query, ieteicams, lai jūsu dati būtu Excel tabulas formātā. Ja nevarat pārvērst datus Excel tabulā, jums būs jāizveido nosaukts datu diapazons un pēc tam jāizmanto šis nosauktais diapazons programmā Power Query.
Zemāk man ir datu kopa, kuru jau esmu pārveidojusi par Excel tabulu. To var izdarīt, atlasot datu kopu, dodoties uz cilni Ievietot un pēc tam noklikšķinot uz tabulas ikonas.
Tālāk ir norādītas darbības, lai izmantotu Power Query, lai aizpildītu datus līdz nākamajai vērtībai.
- Datu kopā atlasiet jebkuru šūnu
- Noklikšķiniet uz cilnes Dati
- Grupā Iegūt un pārveidot datus noklikšķiniet uz “No lapas”. Tiks atvērts Power Query redaktors. Ņemiet vērā, ka tukšajās šūnās Power Query tiktu parādīta vērtība “null”
- Power Query redaktorā atlasiet slejas, kuras vēlaties aizpildīt. Lai to izdarītu, turiet nospiestu vadības taustiņu un pēc tam noklikšķiniet uz atlasāmo kolonnu galvenes. Mūsu piemērā tās būtu kolonnas Datums un Produkts.
- Ar peles labo pogu noklikšķiniet uz jebkuras atlasītās galvenes
- Dodieties uz opciju “Aizpildīt” un pēc tam noklikšķiniet uz “Uz leju”. Tas aizpildītu datus visās tukšajās šūnās
- Noklikšķiniet uz cilnes Fails un pēc tam noklikšķiniet uz “Aizvērt un ielādēt”. Tādējādi darbgrāmatā tiks ievietota jauna darblapa ar iegūto tabulu
Lai gan šī metode var šķist nedaudz pārspīlēta, viena liela Power Query izmantošanas priekšrocība ir tā, ka tā ļauj ātri atjaunināt iegūtos datus gadījumā, ja tiek mainīti sākotnējie dati.
Piemēram, ja sākotnējiem datiem pievienojat vairāk ierakstu, jums nav jādara viss iepriekš minētais vēlreiz. Jūs varat vienkārši ar peles labo pogu noklikšķināt uz iegūtās tabulas un pēc tam noklikšķiniet uz Atsvaidzināt.
Lai gan Power Query metode darbojas labi, vislabāk to izmantot, kad darbplūsmā jau izmantojat Power Query.
Ja jums vienkārši ir datu kopa, kurā vēlaties aizpildīt tukšās šūnas, tad ērtāk būtu izmantot Go To īpašu metodi vai VBA metodi (aplūkota iepriekš).
Tātad šie ir trīs vienkārši veidi, kurus varat izmantot aizpildiet tukšās šūnas līdz nākamajai Excel vērtībai.
Es ceru, ka šī apmācība jums šķita noderīga.
