Atlases pieprasījuma būtība ir izvēlēties virkni no avota tabulas, kas atbilst noteiktiem kritērijiem (līdzīgi standarta izmantošanai). Mēs izvēlēsim vērtības no avota tabulas, izmantojot. Atšķirībā no lietošanas ( Ctrl + Shift + L vai Datu / Kārtot un filtrs / filtrs) Izvēlētās līnijas tiks ievietotas atsevišķā tabulā.
Šajā rakstā apsveriet visbiežāk pieprasījumus, piemēram: tabulu rindu izvēli, kurā vērtība no skaitliskās kolonnas ietilpst noteiktajā diapazonā (intervāls); Līniju izvēle, kurā datums ievadīs noteiktu laiku; Uzdevumi ar 2 teksta kritērijiem un citiem. Sāksim ar vienkāršiem vaicājumiem.
1. Viens skaitlis kritērijs (izvēlieties preces, kurās ir cena virs minimuma)
piemērs fails, lapa Viens kritērijs - numurs ).
Jums ir jāatskaņo atsevišķā tabulā tikai tie ieraksti (līnijas) no avota tabulas, kurā cena ir augstāka par 25.
Atrisināt šo un turpmākos uzdevumus var viegli ar. Lai to izdarītu, izvēlieties avota tabulas virsrakstus un noklikšķiniet uz Ctrl + Shift + L. Ar nosaukuma nolaižamo sarakstu izvēlieties Ciparu filtri ...pēc tam iestatiet nepieciešamos filtrēšanas apstākļus un noklikšķiniet uz Labi.
Ieraksti tiks parādīti atbilstoši atlases nosacījumiem.
Vēl viena pieeja ir jāizmanto. Atšķirībā no izvēlētajām līnijām tiks ievietotas atsevišķā tabulā - savdabīga, kas, piemēram, var formatēt stilā, kas nav avota tabula vai sagatavot citus grozījumus.
Kritērijs (minimālā cena) tiks ievietota šūnā E6. , tabula filtrētiem datiem - diapazonā D10: E19 .
Tagad izvēlieties diapazonu D11: D19. (kolonnu produkts) un ievadā:
Indekss (A11: A19;
Mazākais (ja ($ 6 $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
Insults ($ 10))
Vietā Ievadīt Nospiediet taustiņu kombināciju Ctrl + Shift + Enter(Masīva formula būs).
E11: E19 (kolonnas cena), kur un ievadiet to pašu:
Indekss (B11: B19;
Mazākais (ja ($ 6 $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
Insults ($ 10))
Tā rezultātā mēs saņemam jaunu tabulu, kurā būs tikai preces, kuras cenas nav mazākas šūnā E6. .
Lai parādītu saņemto atlases pieprasījuma dinamiku, mēs ieviešam E6. vērtība 55. Tikai 2 ieraksti nonāks jaunajā tabulā.
Ja avota tabula pievieno jaunu produktu ar cenu 80, tad jaunais ieraksts tiks automātiski pievienots jaunajai tabulai.
Piezīme. To var izmantot arī, lai izvadītu filtrētos datus. Konkrēta instrumenta izvēle ir atkarīga no lietotāja saskaras ar uzdevumu.
Ja neesat lietderīgi izmantot massiva formulaKas atgriež vairākas vērtības, varat izmantot citu pieeju, kas tiek uzskatīta tālāk sadaļās: 5.a, 7, 10 un 11. Šādos gadījumos tiek izmantoti.
2. Divi ciparu kritēriji (izvēlieties preces, ko cena samazinās diapazonā)
Ļaujiet būt avota tabula ar preču un cenu sarakstu (skatīt piemērs fails, lapaSkaitļu diapazons).
Kritēriji (zemākas un augstākās cenu robežas) tiks ievietotas diapazonā E5: E6. .
Tiem. Ja preču cena ietilpst noteiktajā intervālā, tad šāds ieraksts parādīsies jaunajā tabulā filtrēto datu.
Atšķirībā no iepriekšējā uzdevuma mēs izveidosim divas: preces un cenas (bez tiem, jūs varat darīt, bet tie ir ērti rakstot formulas). Attiecīgajām formulām vajadzētu izskatīties kā nosaukuma pārvaldnieks ( Formulas / konkrēti nosaukumi / nosaukuma pārvaldnieks) Šādi (skatīt zemāk redzamo attēlu).
Tagad izvēlieties diapazonu D11: D19. Un ieviešanā:
Indekss (preces;
Vismaz (
Ja (($ 5 $ 5<=Цены)*($E$6>\u003d Cenas); rinda (cenas); "");
Vietā Ievadīt Nospiediet taustiņu kombināciju Ctrl + Shift + Enter.
Tās pašas manipulācijas rada diapazonu E11: E19 Kur un ieviest līdzīgus:
Indekss (cenas;
Vismaz (
Ja (($ 5 $ 5<=Цены)*($E$6>\u003d Cenas); rinda (cenas); "");
Rinda (cenas) -struktūra ($ B $ 10)) - līnija ($ $ 10))
Tā rezultātā mēs iegūstam jaunu tabulu, kurā būs tikai preces, kuras cenas nonāk šūnās norādītās intervālos E5 un E6. .
Lai parādītu dinamiku saņemto ziņojumu (atlases pieprasījumu) ievietot E6. vērtība 65. Vēl viens ieraksts no avota tabulas tiks pievienota jaunajai tabulai, kas atbilst jaunajam kritērijam.
Ja jūs pievienot jaunu produktu ar cenu avota tabulā no 25 līdz 65, jaunais ieraksts tiks pievienots jaunajai tabulai.
Piemērs Fails satur arī kļūdu apstrādes formulas, kad cenu kolonnā ir kļūdas vērtība, piemēram, # gadījumi / 0! (skatiet lapu Kļūdu apstrāde).
Šādi uzdevumi tiek atrisināti līdzīgi, tāpēc mēs tos neuzskatīsim detalizēti.
3. Viens no kritērija datums (izvēlieties tos produktus, kas ir norādīts piegādes datums)
piemērs fails, lapaViens kritērijs - datums).
Rindu izvēlei, masīva formulas, kas ir līdzīgas uzdevumam1 (kritērija vietā<= используется =):
=Indekss (A12: A20; mazākais (ja ($ 6 \u003d B12: B20; rinda (B12: B20); "); virkne (B12: B20) - virkne ($ B $ 11)) - līnija ($ $ 11)) - līnija ($ $ 11)) - līnija ($ 11)) B $ 11))
Indekss (B12: B20; mazākais (ja ($ 6 \u003d B12: B20; rinda (B12: B20); "); virkne (B12: B20) - virkne ($ B $ 11)) - līnija ($ $ 11)) - līnija ($ $ 11)) B $ 11))
4. Divi kritēriji (izvēlieties preces, ko piegādes datums ir diapazonā)
Ļaujiet būt avota tabula ar preču un piegādes datumu sarakstu (skatīt piemērs fails, lapaDatuma diapazons).
Lūdzu, ņemiet vērā, ka kolonna nav sakārtota.
Risinājums1: Rindu izvēlei var izmantot.
Ieiet šūnā D12. masīva formula:
Indekss ($ 12: $ 20;
Lielākais (($ 6 $ 6<=$B$12:$B$20)*($E$7>\u003d $ B $ 12: $ B $ 20) * (Line ($ B $ 12: $ B $ 20) -Strokok ($ B $ 11));
$ J $ 12 line (A12) + līnija ($ 11) +1))
Piezīme: Pēc ievadīšanas formulu, nevis ievadīt taustiņu, jums ir nepieciešams, lai nospiestu taustiņu Ctrl + Shift + ievadiet taustiņu kombināciju. Šo taustiņu kombināciju izmanto, lai ievadītu masīva formulu.
Kopējiet masīvu formulu līdz vēlamajam šūnu skaitam. Formula atgriezīsies tikai tās preču vērtības, kas tika piegādātas šo datumu diapazonā. Pārējās šūnas satur kļūdas # numurs! Kļūdas B. failu piemērs (datumu saraksts) .
Līdzīga formula jāievieš datumiem E. kolonnā
Šūnā J12 aprēķina to avota tabulas rindu skaits, kas atbilst kritērijiem:
Kolekcionalitāte (B12: B20; "\u003e \u003d" & $ E $ 6; B12: B20; "<="&$E$7)
Avota tabulas rindas, kas atbilst kritērijiem.
Risinājums2: Lai izvēlētos rindas, varat izmantot masīva formulas, līdzīgas uzdevumam2 (t.i.):
=Indekss (A12: A20; mazākais (ja ($ 6 $ 6<=B12:B20)*($E$7>\u003d B12: B20); līnija (B12: B20); "); virkne (B12: B20) -strkoKoka ($ B $ 11)) - līnija ($ B $ 11)))
Indekss (B12: B20; mazākais (ja ($ 6 $ 6<=B12:B20)*($E$7>\u003d B12: B20); līnija (B12: B20); "); virkne (B12: B20) -strkoKoka ($ B $ 11)) - līnija ($ B $ 11)))
Lai ievadītu pirmo formulu, iezīmējiet šūnu klāstu G12: G20. . Pēc ievadīšanas formulu, nevis ievadīt taustiņu, jums ir nepieciešams nospiest Ctrl + Shift + ievadiet taustiņu kombināciju.
Risinājums3.: Ja datumi ir sakārtoti, jūs nevarat izmantot masīva formulas.
Pirmkārt, ir nepieciešams aprēķināt pirmās un pēdējās pozīcijas līnijas, kas atbilst kritērijiem. Tad izejas stīgas.
Šis piemērs atkal skaidri parāda, cik vieglāk rakstot formulas.
5. Viens kritērijs datums (izvēlieties tos produktus, ko piegādes datums nav agrāk / ne vēlāk kā noteikts)
Ļaujiet būt avota tabula ar preču un piegādes datumu sarakstu (skatīt piemērs fails, lapa Viens kritērijs - datums (ne vēlāk) ).
Rindu izvēlei, kuras datums nav agrāk (ieskaitot pašu datumu), tiek izmantota masīva formula:
=Indekss (A12: A20; mazākais (ja ($ 7 $ 7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Arī piemēru failā formulas ir dota: ne agrāk (neietverot); Ne vēlāk (ieskaitot); Ne vēlāk (neietverot).
7. Viens teksta kritērijs (izvēlieties noteiktu preču veidu)
Ļaujiet būt avota tabula ar preču un cenu sarakstu (skatīt piemērs fails, lapaViens kritērijs - teksts).
Ļaujiet būt avota tabula ar preču un cenu sarakstu (skatīt piemērs fails, lapa {!LANG-78bfa423500bef3ca398b809db54c56a!} ).
{!LANG-f76987eeea6204542dc95ac9670d39c0!}
{!LANG-d50ce11745aff02711b7065747f2f42f!}
{!LANG-37c24a93149d3e920b44e25bfa1db334!} {!LANG-207354d04f1b59678db3eb80b248c3eb!} Norāda abus nosacījumus (preces un mēnesis).
{!LANG-37c24a93149d3e920b44e25bfa1db334!} ROW (DVSSL ("A1: A" & Crekok ($ 11: $ 19)))) Veidlapas (1: 2: 3: 4: 5: 6: 7: 8: 9), i.e. Rindu numuri tabulā.
9. Divi teksta kritēriji (izvēlieties dažu sugu produktus)
Ļaujiet būt avota tabula ar preču un cenu sarakstu (skatīt piemērs fails, lapa2 Kritēriji - teksts (vai)).
Atšķirībā no uzdevuma 7 uzņemt virknes ar 2 sugām ().
Rindu izvēlei tiek izmantota masīva formula:
Indekss ($ 11: $ 19;
Lielākais ((($ 6 $ 6 \u003d $ 11: $ 19) + ($ e $ 7 \u003d $ 11: $ 19)) * (Line ($ 11: $ $ $ 19) -Trukle ($ 10)); skaitīts ($ 11: $ 19 $; $ 6) + Countdown ($ 11: $ 19 $; $ 7 $ 7) - bons $ 11: A11) +1))
Stāvoklis ($ 6 \u003d $ 11 $: $ 19) + ($ e $ 7 \u003d $ 11 $: $ 19) Tas nodrošina, ka tiks atlasītas tikai noteiktās sugas no dzeltenajām šūnām (preces2 un preces3). A + (papildinājums) tiek izmantots uzdevumam (vismaz 1 kritērijs ir jāveic).
Iepriekš minētais izteiksme atgriezīsies ar masīvu (0: 0: 0: 0: 1: 1: 1: 0: 0). Reizinot viņu Rinda ($ 11: $ 19) -struktūra ($ 10). Ieslēgts (1: 2: 3: 4: 5: 6: 7: 8: 9), mēs iegūstam virkni pozīciju (tabulas līniju skaitu), kas atbilst kritērijiem. Mūsu gadījumā tas būs masīvs (0: 0: 0: 0: 5: 6: 0: 0).
Piemēram, veiciet risinājumu šādam uzdevumam: Izvēlieties preces, kuru cena atrodas noteiktā diapazonā un atkārto norādīto skaitu vai vairāk.
Kā sākotnējo, ņemiet preču partiju tabulu.
Pieņemsim, ka mēs esam ieinteresēti, cik daudz un kuras puses preces tika piegādātas par 1000R cenu. Līdz 2000. gadam (1. kritērijs). Turklāt pusēm ar tādu pašu cenu jābūt vismaz 3 (2. kritērijs).
Šķīdums ir masīva formula:
Mazākais (līnija ($ 14 $: $ 27) * ($ C $ 14: $ C $ 27\u003e \u003d $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>\u003d $ B $ 10); F14 + ($ 8 - $ 8 $ 9))
Šī formula atgriež rindas numurus, kas atbilst abiem kritērijiem.
Formula \u003d SAAKSTS (($ C $ 14: $ C $ 27\u003e \u003d $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>\u003d $ B $ 10)) Aprēķina rindu skaitu, kas atbilst kritērijiem.
11. Mēs izmantojam kritērija vērtību (jebkuru) vai (visu)
Iebildums piemērs fails uz lapas "11. Kritērijs jebkura vai (viss)" Šī opcija tiek īstenota.
Šādā gadījumā formula jāietver funkcija, ja (). Ja vērtība ir izvēlēta (viss), formula tiek izmantota, lai izvadītu vērtības, neņemot vērā šo kritēriju. Ja ir izvēlēta kāda cita vērtība, kritērijs darbojas parastajā veidā.
Ja ($ C $ 8 \u003d "(viss)";
Mazākais ((līnija ($ B $ 13: $ B $ 26) -Strok ($ B $ 12)) * ($ D $ 13: $ 2 $ 26\u003e \u003d $ D $ 8); F13 + ($ g $) 6- $ g $ 7));
Mazākais ((Line ($ B $ 13: $ B $ 26) -Trook ($ B $ 12)) * ($ D $ 13: $ D $ 26\u003e \u003d $ D $ 8) * ($ C $ 13: $ C $ 26 \u003d $ C $ 8); F13 + ($ g $ 6- $ g $ 7)))))
Pārējā formula ir līdzīga iepriekš minētajām.
Datu paraugu ņemšana
Izveidojiet parauga ziņojumu no lapas5 uz kolonnas "Kvalitatīvais sniegums, procenti". (no 8. lapas, 7. tabula)
Lai veiktu datu izvēli, jāveic šādi pasākumi:
Nosakiet jaunā masīva elementu skaitu noteiktā stāvoklī, ievadot mainīgo, izmantojot ievades kastes operatoru
Paziņot un atsvaidzināt jauno masīvu
Veidot jaunu masīvu. Lai to izdarītu, norādiet jaunā masīva U \u003d 1 pirmā elementa skaitu. Tad tiek veikts cikls, kas ieraksta parauga stāvokli uz kolonnas "Kvalitātes veiktspēju, procentiem.". Ja patiesības pārbaudes rezultāts analizētā masīva elements kļūst par jaunu masīvu elementu.
Izņemt jaunu elementu uz lapas 8
Sub aizsardzības bērns ()
Loksnes ("saraksts8"). Atlasīt
Blāvs a () kā variants
Redim a (1 līdz n1, 1 līdz m)
VVOD "Sheet5", A, N1, M, 4
C \u003d ievades kaste ("Ievadiet stāvokli")
Loksnes ("saraksts8"). Šūnas (5.11) \u003d c
Par i \u003d 1 līdz n1
Ja A (i,
8) >
d \u003d d + 1
Loksnes ("saraksts8"). Šūnas (5.10) \u003d d
Blāvs b () kā variants
Redim b (1 līdz D, 1 līdz m)
Par i \u003d 1 līdz n1
Ja A (i,
8)\u003e Loksnes ("saraksts8"). Šūnas (5.11)
Par j \u003d 1 līdz m
B (u, j) \u003d a (i, j)
u \u003d u + 1
Par i \u003d 1 līdz d
Par j \u003d 1 līdz m
Loksnes ("saraksts8"). Šūnas (i + 4, j) \u003d b (i, j)
6. att. Tabulas dati pēc paraugu ņemšanas
Automātiska parauga makro izveide
Ieslēdziet makro ierakstu. Serviss\u003e Makro\u003e Start Record\u003e Labi. Kvadrāts parādīsies, ja pieturas poga. Lapā5 (ziņojums), mēs izcelt tabulu bez galvenēm un rezultātiem, kopēt uz List 10 (Auto Collector). Mēs izcelt tabulu bez galvenēm un izvēlnes vienībā, izvēlieties Dati\u003e Filtrēt\u003e Automātiskais filtrs\u003e Izvēlieties Stāvoklis\u003e OK. Mēs atzīmējam kolonnu, kurā mēs šķiram. Pabeigt makro darbu.
Sub makro2 vērtība ()
"Makro 22 makro makro
Loksnes ("saraksts5"). Atlasīt
Izvēle. Kopija
Loksnes ("saraksts9"). Atlasīt
ActivePeet. Ielīmēt
Diapazons ("H5: H17"). Atlasīt
Pieteikumu. Cutcopymode \u003d False
Izvēle. Autofiltrs.
ActivePeet. Diapazons ("$ H $ 5: $ H $ 17"). Autofiltra lauks: \u003d 1, kritēriji1: \u003d "\u003e 80", _
Operators: \u003d Xland
Diapazons ("G22"). Atlasīt
7. attēls. Tabulas dati pēc paraugu ņemšanas
Maksimālās un minimālās vērtības noteikšana
Nosakiet maksimālo un min vērtības kolonnās "Kopā", "absolūtā veiktspēja, procentos", "augstas kvalitātes sniegums" (9. tabula, 10. lapa)
Lai noteiktu max un min vērtības, jums ir veikt šādas darbības:
Iestatiet standarta mainīgo, kas būs pašreizējais minimums (maksimālais)
Agri salīdzina katru komplekta elementu ar pašreizējo minimālo (maksimumu), un, ja šis elements neatbilst meklēšanas nosacījumiem (minimālā gadījumā, tas ir lielāks, un maksimāli - mazāk) gadījumā, tad standarta gadījumā vērtību aizstāj ar vērtību salīdzināt elementa
Pēc visu elementu skatīšanās pašreizējā minimālā minimālā (maksimālā) ir derīga minimālā (maksimālā)
Minimālā (maksimālā) vērtība tiek parādīta attiecīgajās šūnās.
Sub minmax ()
Blāvs a () kā variants
n1 \u003d loksnes ("Sheet4"). Šūnas (5,12)
m \u003d loksnes ("saraksts2"). Šūnas (5,12)
Redim a (1 līdz n1, 1 līdz m)
VVOD "Sheet5", A, N1, M, 4
Vivod "List10", A, N1, M, 4
VVOD "LIST10", A, N1, M, 4
Par j \u003d 3 līdz m
maxa \u003d 0.00001
mINA \u003d 1000000.
Par i \u003d 1 līdz n1
Ja a (i, j)\u003e maxa tasn
maxa \u003d a (i, j)
Ja a (i, j)< minA Then
mINA \u003d A (I, J)
Loksnes ("saraksts10"). Šūnas (i + 4 + 2, j) \u003d maxa
Loksnes ("saraksts10"). Šūnas (i + 4 + 3, j) \u003d min
Makro ir ierakstīto un saglabāto darbību secība turpmākai izmantošanai. Saglabāto makro var reproducēt ar īpašu komandu. Citiem vārdiem sakot, jūs varat ierakstīt savas darbības makro, saglabājot to, un pēc tam ļaut citiem lietotājiem atskaņot saglabāti darbības makro, vienkārši nospiežot taustiņu. Tas ir īpaši ērti, izplatot kopsavilkuma tabulu ziņojumus.
Pieņemsim, ka vēlaties sniegt saviem klientiem iespēju grupēt konsolidēto tabulu ziņojumus pēc mēnešiem, ceturkšņiem un gadiem. Tehniski, grupēšanas process var veikt jebkuru lietotāju, bet daži no jūsu klientiem netiks uzskatīts par nepieciešamu to saprast. Līdzīgā gadījumā varat pierakstīt vienu makro grupu mēnešiem, otrs - ceturkšņos un trešajā - pēc gada. Tad izveidojiet trīs pogas - vienu uz katra makro. Tad jūsu klienti, kuriem nav pieredzes ar konsolidētām tabulām, būs nepieciešama tikai noklikšķiniet uz pogas, lai pareizi grupētu kopsavilkuma tabulu.
Galvenā priekšrocība izmantot makro ziņojumos par konsolidētajām tabulām ir nodrošināt klientiem iespēju ātri veikt šādas darbības konsolidētajās tabulās, ka viņi nevar izpildīt parastajā situācijā. Ņemot vērā to, ka sniegto datu analīzes efektivitāte ir ievērojami palielināta.
Lejupielādējiet piezīmi formātā vai lejupielādējiet ar piemēriem (Excel faila iekšpusē ar makro; Pakalpojumu sniedzēja politika neļauj tieši augšupielādēt šī formāta failu uz vietni).
Makroekretārs
Paskatieties uz konsolidēto tabulu, kas parādīta 1. attēlā. 1. Jūs varat atjaunināt šo konsolidēto tabulu, ar peles labo pogu noklikšķinot uz tā un izvēlieties komandu. atjaunot. Ja jūs ierakstījāt darbības makro formā Pivot tabulas atjaunināšanas laikā, tad jūs vai kāds cits lietotājs var reproducēt šīs darbības un atjaunināt konsolidēto tabulu, kā rezultātā makro sākuma.
Fig. 1. Darbības reģistrēšana, atjauninot šo kopsavilkuma tabulu, turpmāk atjauninās datus makro uzsākšanas rezultātā
Pirmais posms makro ierakstā ir saruna dialoglodziņu Makroekretārs. Noklikšķiniet uz cilnes Attīstītājs Lentes un noklikšķiniet uz pogas Makroekretārs. (Ja nevarat atrast lentes cilni Attīstītājs, Izvēlieties cilni Failsun noklikšķiniet uz pogas Parametri. Dialoglodziņā, kas parādās dialoglodziņā Excel iestatījumi Izvēlies kategoriju Lentes iestatīšana Un pareizajā sarakstā izvēlieties izvēles rūtiņu Attīstītājs. Tā rezultātā lente parādīsies lentē Attīstītājs.) Alternatīvs veids, kā sākt ierakstīt makro - noklikšķiniet uz pogas (2. att.).
Dialoglodziņā Makroekretārs Ievadiet šādu makroinformāciju (3. att.):
Nosauktmakro. Nosaukumā ir jāapraksta makro veiktās darbības. Nosaukumam jāsākas ar burtu vai pasvītrojumu; nedrīkst saturēt telpu un citas nepieņemamas zīmes; Tas nedrīkst sakrist ar iegulto nosaukumu Excel vai nosaukumu cita objekta grāmatā.
Kombinācijataustiņi. Šajā laukā varat ievadīt jebkuru burtu. Tas būs daļa no galvenās kombinācijas, kas tiks izmantota, lai atskaņotu makro. Atslēgas kombinācija nav obligāta. Pēc noklusējuma, tikai Ctrl tiek piedāvāts kā sākums kombinācijas. Ja vēlaties, lai kombinācija ieslēgt arī maiņu, izsauciet logā burtu, turot nospiestu taustiņu Shift taustiņu
Ietaupītiebildums. Tas norāda uz vietas uzglabāšanas makro. Ja jūs plānojat izplatīt kopsavilkuma tabulas ziņojumu starp citiem lietotājiem, izvēlieties parametru Tasgrāmata. Excel arī ļauj jums saglabāt makro in Jauna grāmata vai Personīgo grāmatu makro.
Apraksts. Šajā laukā ir aprakstīts apraksts par makro izveidoto.
Fig. 3. Loga iestatīšana Makroekretārs
Tā kā makro atjaunina konsolidēto tabulu, izvēlieties Vārds Uzlabojumi. Varat piešķirt Ctrl + Shift + Q taustiņu kombināciju. Atcerieties, ka pēc makro izveidošanas jūs izmantosiet šo taustiņu kombināciju, lai to sāktu. Izvēlieties parametru kā makro uzglabāšanas vietu Šī grāmata un noklikšķiniet uz labi.
Pēc noklikšķināšanas uz dialoglodziņā Makroekretārs Uz pogas labi Makro sākas. Šajā posmā tiks reģistrētas visas darbības, ko veicat Excel.
Ar peles labo pogu noklikšķiniet uz konsolidētā tabulas apgabala un izvēlieties komandu. atjaunot (Kā 1. attēlā, bet jau makro ierakstīšanas režīmā). Pēc Pivot tabulas atjaunināšanas jūs varat apturēt makro ierakstīšanas procesu, izmantojot pogu. Pārtraukt ierakstīšanu cilnes Attīstītājs. Vai atkārtoti noklikšķiniet uz pogas, kas parādīta 1. attēlā. 2.
Tātad, jūs vienkārši ierakstījāt savu pirmo makro. Tagad jūs varat izpildīt makro, izmantojot Ctrl + Shift + Q taustiņu kombināciju.
Makro drošības brīdinājums. Jāatzīmē, ka gadījumā, ja lietotājs ieraksta makro, tie tiks veikti bez drošības apakšsistēmas ierobežojumiem. Tomēr, izplatot darba grāmatu, kas satur makro, ir nepieciešams sniegt citiem lietotājiem iespēju pārliecināties, ka nav risks, atverot darba failus, un makro izpilde neradīs vīrusu sistēmas infekciju. Jo īpaši, jūs uzreiz pamanīsiet, ka šajā nodaļā izmantotais piemērs nebūs pilnībā funkcionējis, ja tas nav īpaši atļauts Excel veikt makro tajā.
Vieglākais veids, kā nodrošināt makro drošību, ir izveidot uzticamu atrašanās vietu - mapi, uz kuru tiks ievietotas tikai "uzticamas" darbgrāmatas, kas nesatur vīrusus. Uzticama atrašanās vieta ļauj jums un jūsu klientiem veikt makro darbavietu bez jebkādiem drošības apakšsistēmas ierobežojumiem (šāda rīcība tiek saglabāta, līdz darba grāmatas ir trasta mapē).
Lai konfigurētu uzticamu atrašanās vietu, rīkojieties šādi.
Izvēlieties cilni Ribbon Attīstītājs un noklikšķiniet uz pogas Drošības makro. Ekrānā parādās dialoglodziņš. Drošības vadības centrs.
Noklikšķiniet uz pogas Pievienot jaunu atrašanās vietu.
Noklikšķiniet uz pogas PārskatsLai norādītu mapi, lai strādātu failus, kurus uzticaties.
Pēc uzticamas atrašanās vietas norādīšanas, patvaļīgi makro tiks veikti pēc noklusējuma visām darba grāmatām tajā.
Excel 2013 tika uzlabots drošības modelis. Tagad tika atcerēti darbgrāmatas faili, kas iepriekš bija "uzticība", t.i. Pēc Excel grāmatas atvēršanas un noklikšķiniet uz pogas Iespējot saturu Excel atceras šo valsti. Rezultātā šī grāmata ietilpst uzticamiem, un nevajadzīgie jautājumi nav norādīti tās turpmākās atvēršanas laikā.
Lietotāja saskarnes izveide, izmantojot veidlapu kontroli
Makro uzsākšana, izmantojot Ctrl + Shift + Q galveno kombināciju gadījumā, ja konsolidētajam tabulas ziņojumam ir tikai viens makro. (Turklāt lietotājiem ir jāzina šī kombinācija.) Bet pieņemsim, ka vēlaties nodrošināt saviem klientiem vairākas makro, veicot dažādas darbības. Šādā gadījumā jums ir jāsniedz klienti ar skaidru un vienkāršu veidu, kā uzsākt katru makro, neizmantojot galveno kombināciju iegaumēšanu. Ideāls risinājums ir vienkāršs lietotāja interfeiss, kas veido kontroli, piemēram, pogas, ritināšanas sloksnes un citi līdzekļi, kas ļauj veikt makro ar peles klikšķiem.
Excel piedāvājumi Jūsu rīcībā ir rīku komplekts, kas paredzēts, lai izveidotu lietotāja interfeisu tieši izklājlapā. Šie rīki tiek saukti par kontroles elementiem. Pamatideja ir tā, ka jūs varat ievietot veidlapas vadību izklājlapā un piešķiriet viņam makro, kas tika reģistrēts agrāk. Pēc iecelšanas makro darbosies šajā elementā.
Veidlapu kontroli var atrast grupā Veidlapu vadības elementi Lentes lentes Attīstītājs. Lai atvērtu vadības paleti, noklikšķiniet uz šīs grupas uz pogas Ievietot (4. att.).
Fig. 4. Veidlapas vadības elements Poga
PIEZĪME: Papildus veidlapas kontrolei ir arī palete ActiveX elementi. Lai gan tie ir līdzīgi, tie ir programmiski dažādi objekti. Veidlapu vadības elementiar ierobežotajām funkcijām un vienkāršiem iestatījumiem tas bija īpaši izstrādāts izmitināšanai darba lapās. Tajā pašā laikā ActiveX elementi Piesakies galvenokārt pasūtījuma veidlapās. Veikt noteikumu, lai nosūtītu darba lapām tikai veido kontroli.
Jums vajadzētu izvēlēties kontroles, kas ir vislabāk piemērotas, lai atrisinātu uzdevumu. Šajā piemērā klientiem ir jāsniedz iespēja atjaunināt konsolidēto tabulu ar klikšķi uz pogas. Noklikšķiniet uz vadības elementa Poga, Pārvietojiet peles rādītāju uz darba lapas vietu, kur ir jāatrodas pogai, un noklikšķiniet uz peles.
Pēc tam, kad esat ievietojis pogu tabulā, atveras dialoglodziņš. Piešķirt makro objekts (5. att.). Izvēlieties vēlamo makro (mūsu gadījumā - Uzlabojumi, reģistrēts agrāk) un noklikšķiniet uz pogas labi.
Fig. 5. Izvēlieties makro, kas jāpiešķir pogai, un noklikšķiniet uz pogas. labi. Šādā gadījumā jāpiemēro makro Uzlabojumi
Pēc visu nepieciešamo kontroli purnavas tabulā varat formatēt tabulu, lai izveidotu pamata interfeisu. Att. 6 Pēc formatēšanas parāda kopsavilkuma tabulas ziņojumu.
Mainīt ierakstīto makro
Makro ierakstīšanas rezultātā Excel programma izveido moduli, kas veikali esat pabeidzis. Visas reģistrētās darbības pārstāv VBA kodu virknes, no kurām veido makro. Konsolidētā tabulas ziņojumos varat pievienot dažādas funkcijas, konfigurējot VBA kodu, lai iegūtu nepieciešamos rezultātus. Lai padarītu to vieglāk saprast, kā tas viss darbojas, izveidojiet jaunu makro, izņemot piecus pirmos ierakstus par klientiem. Noklikšķiniet uz cilnes Attīstītājs un noklikšķiniet uz pogas Makroekretārs. Dialoglodziņš parādīts attēlā. 7. Nosaukiet izveidoto makro PirmkārtNsakazchikov un norādiet taupīšanas vietu Šī grāmata. Klikšķis labiLai sāktu ierakstīt makro.
Pēc ierakstīšanas sākuma noklikšķiniet uz bultiņas blakus laukam Klienta vārds, Izvēlieties Filtrēt pēc vērtības un opcija Pirmais 10 (8.a att.). Parādītajā dialoglodziņā iestatiet iestatījumus, kā parādīts 1. attēlā. 8b. Šie iestatījumi norāda, lai parādītu piecu klientu datus, vislabāk pārdošanas ziņā. Klikšķis labi.
Fig. 8. Izvēlieties Filtrs (A) un konfigurējiet parametrus (b), lai parādītu piecus klientus pārdošanai.
Pēc veiksmīgas uzskaites par visām darbībām, kas nepieciešamas, lai iegūtu piecus piedāvājumus par pārdošanu, dodieties uz cilni Attīstītājs un noklikšķiniet uz pogas Pārtraukt ierakstīšanu.
Tagad jums ir makro, kas filtrēs konsolidēto tabulu, lai iegūtu piecus piedāvājumus par pārdošanu. Tas jādara tā, lai makro reaģētu uz ritināšanas joslas stāvokli, t.i. Izmantojot ritjoslu, jums ir jāspēj norādīt Makro skaitu klientiem, kuru dati tiks parādīti konsolidētā tabulas ziņojumā. Tādējādi, izmantojot ritjoslu, lietotājs varēs iegūt piecus labākos, astoņus labākos vai trīsdesmit divus labākos klientus pēc saviem ieskatiem.
Lai pievienotu ritiniet uz izklājlapu izklājlapai, dodieties uz cilni. AttīstītājsNoklikšķiniet uz pogas IevietotIzvēlieties vadību uz paletes Ritināšanas josla Un novietojiet to darba lapā. Ar peles labo pogu noklikšķiniet uz vadības elementa Ritināšanas josla Formāta objekts. Tiek atvērts dialoglodziņš Kontroles formāts (9. att.). Tajā, veikt šādas izmaiņas iestatījumos: parametrs Minimālā vērtība 1. piezīme, parametrs Maksimālā vērtība - vērtība 200, un šajā jomā Komunikācija ar šūnu Ievadiet vērtību $ $ 2, lai M2 šūnas attēlotu ritināšanas joslu. Noklikšķiniet uz pogas labiLai izmantotu iepriekš norādītos iestatījumus.
Tagad jums ir nepieciešams salīdzināt jaunizveidoto makro PirmkārtNsakazchikov Ar vadības elementu Ritināšanas joslaAtrodas uz darba lapas. Ar peles labo pogu noklikšķiniet uz vadības elementa Ritināšanas josla Un konteksta izvēlnē izvēlieties komandu Piešķirt makroLai atvērtu dialoglodziņu Macro iecelšanu. Piešķirt ritināšanas joslas ieraksta makro Firstnenzakazchikov. Makro tiks veikta katru reizi pēc noklikšķināšanas uz ritjoslas. Pārbaudiet izveidoto ritjoslu. Pēc noklikšķināšanas uz joslas, makro sāksies Firstnenzakazchikov M2 šūnas numurs mainīsies, lai parādītu ritināšanas joslas statusu. Numurs M2 šūnā ir svarīga loma, jo to izmanto, lai saistītu makro uz ritjoslu.
Vienīgais, kas palicis jādara, ir piespiest makro apstrādāt numuru M2 šūnā, sasaistot to ar ritjoslu. Lai to izdarītu, dodieties uz makro VBA kodu. Lai to izdarītu, dodieties uz cilni Attīstītājs un noklikšķiniet uz pogas Makro. Tiek atvērts dialoglodziņš Makro (10. att.). Varat palaist, dzēst un rediģēt izvēlēto makro. Lai ekrānā parādītu VBA Macro kodu, izvēlieties Makro un noklikšķiniet uz pogas. Mainīt.
Fig. 10. Lai piekļūtu makro VBA kodam PirmkārtNsakazchikov, izvēlieties makro un noklikšķiniet uz pogas Mainīt
Ekrānā parādās vizuālais pamata redaktors ar Makro VBA kodu (11. att.). Jūsu mērķis ir nomainīt 5. skaitu, kas stingri norādīts kodā, uzstādīts makro ierakstīšanas laikā, M2 šūnas vērtību, kas ir piesaistīta ritjoslā. Sākotnēji tika reģistrēts makro, kas paredzēts, lai parādītu pirmos piecus klientus, kuriem ir lielākie ienākumi.
Noņemiet numuru 5 no koda un ievadiet šādu izteiksmi:
ActiveSheet.Range ("m2") .Value
Pievienojiet divus virknes makro, lai attīrītu filtrus:
Diapazons ("A4") .Select
ActiveSheet.Pivottables ("PivotTable1") .Pivotfields ("Klienta nosaukums") .clearallFilters
Tagad makro kodam vajadzētu izskatīties, piemēram, attēlā. 12.
Aizveriet vizuālo pamata redaktoru un atgriezieties konsolidētajā tabulā. Pārbaudiet ritināšanas joslu, velkot slīdni līdz vērtībai 11. Makro jāsāk un filtrē 11 ieraksti par labākajiem pārdošanas klientiem.
Divu konsolidēto tabulu sinhronizācija, izmantojot vienu nolaižamo sarakstu
Ziņojumā parādīts 1. attēlā. 13, ir divas kopsavilkuma tabulas. Katram no tiem ir lapas lauks, kas ļauj izvēlēties pārdošanas tirgu. Problēma ir tā, ka katru reizi, kad izvēlaties tirgu lapas laukā viena pilona galda, jums ir jāizvēlas tāds pats tirgus lapas laukā citas šarnavas galda. Sinhronizējot abu tabulu filtrus datu analīzes stadijā, nerada lielu problēmu, bet ir iespēja, ka jūs vai jūsu klienti to neaizmirsīs.
Fig. 13. Divi kopsavilkuma tabulas satur lapu laukus, kas veic datu filtrēšanu tirgos. Lai analizētu atsevišķa tirgus datus, jums ir nepieciešams sinhronizēt abus kopsavilkuma tabulas.
Viena no šīm kopsavilkuma tabulu sinhronizēšanas metode ir izmantot nolaižamo sarakstu. Ideja ir ierakstīt makro, kas izvēlas vēlamo tirgu no lauka Pārdošanas tirgus Abās tabulās. Tad jums ir nepieciešams izveidot nolaižamo sarakstu un aizpildīt to ar pārdošanas tirgu nosaukumiem no diviem kopsavilkuma tabulām. Visbeidzot, ierakstītā makro ir jāmaina, lai filtrētu abus kopsavilkuma tabulas, izmantojot vērtības no nolaižamā saraksta. Lai atrisinātu šo uzdevumu, jums ir nepieciešams veikt šādas darbības.
1. Izveidojiet jaunu makro un piešķiriet to nosaukumu. Sinhorkets.. Kad sākas ieraksts, izvēlieties laukā Tirgus pārdošana abiem Kopsavilkuma tabulu pārdošana Kalifornija Un apturiet makro ierakstu.
2. Parādiet ekrāna veidlapas vadības ierīces un ekrānā pievienojiet nolaižamo sarakstu.
3. Izveidojiet stingri noteiktu sarakstu ar visiem konsolidētajiem galda tirgiem. Ņemiet vērā, ka pirmais elements sarakstā norāda vērtību (visu). Jums vajadzētu ļaut šo elementu, ja jūs vēlaties, lai varētu izvēlēties visus tirgos nolaižamajā sarakstā.
4. Šajā posmā konsolidētā tabulas ziņojumam vajadzētu izskatīties attēlā. četrpadsmit.
Fig. 14. Jūsu rīcībā ir visi nepieciešamie līdzekļi: makro maiņa lauka Pārdošanas tirgus Gan pagrieziena tabulas, nolaižamo sarakstu un visu konsolidētajā tabulā iekļauto pārdošanas tirgu sarakstu
5. Ar peles labo pogu noklikšķiniet uz nolaižamā saraksta un izvēlieties komandu konteksta izvēlnē. Formāta objektsKonfigurēt kontroli.
6. Pirmkārt, iestatiet to vērtību avota klāstu, ko izmanto, lai aizpildītu nolaižamo sarakstu, kā parādīts 1. attēlā. 15. Šajā gadījumā mēs runājam par to, ko jūs izveidojāt 3. punktā esošos pārdošanas tirgus, norādiet šūnu, kas parāda izvēlētā vienuma secības numuru (šajā piemērā ir šūna H1). Parametrs Lūpu līniju skaits Nosaka, cik rindu vienlaicīgi tiks atspoguļotas nolaižamajā sarakstā. Noklikšķiniet uz pogas labi.
Fig. 15. Nolaižamajā saraksta iestatījumos jānorāda pārdošanas tirgu saraksts kā sākotnējā vērtību diapazons, kā arī kā saistošs punkts - lai noteiktu šūnu H1
7. Tagad jums ir iespēja izvēlēties pārdošanas tirgu nolaižamajā sarakstā, kā arī noteikt saistīto secības numuru šūnā H1 (16. att.). Rodas jautājums: kāpēc tā indeksa vērtība ir reālā tirgus nosaukuma vietā? Tā nolaižamajā sarakstā atgriež nav vārdu, bet numuru. Piemēram, ja izvēlaties California Nolaižamajā sarakstā H1 šūnā, parādās vērtība 5. Tas nozīmē, ka Kalifornija ir saraksta piektais elements.
Fig. 16. Nolaišanas saraksts tagad ir piepildīts ar tirgu nosaukumiem, un izvēlētais izvēlētā tirgus numurs tiek parādīts H1 šūnā.
8. Lai izmantotu secības numuru, nevis tirgus nosaukumu, jums tas jānosūta, izmantojot indeksa funkciju.
9. Ievadiet indeksa funkciju, kas pārvērš secības numuru no H1 šūnas uz skaidru vērtību.
10. Indeksa funkcijai ir divi argumenti. Pirmais arguments ir saraksta vērtību diapazons. Vairumā gadījumu jūs izmantosiet to pašu diapazonu, ka nolaižamā izvēlne ir piepildīta. Otrais arguments ir secības numurs. Ja secības numurs tiek ievadīts šūnā (piemēram, H1 šūnā, kā 17. att.), Jūs varat vienkārši atsaukties uz šo šūnu.
Fig. 17. Funkcijas indekss šūnā i1 pārvērš secības numuru, kas uzglabāts šūnā H1, uz vērtību. Jūs izmantosiet vērtību šūnā I1, lai mainītu makro
11. Rediģēt makro Sinhorkets.Izmantojot vērtību šūnā I1, nevis stingri noteikta vērtība. Noklikšķiniet uz cilnes Attīstītājs un noklikšķiniet uz pogas Makro. Dialoglodziņš parādās ekrānā redzams attēlā. 18. Izvēlieties makro tajā Sinhorkets. un noklikšķiniet uz pogas Rediģēt.
Fig. 18. Lai piekļūtu makro VBA kodam, izvēlieties Makro Sinhorkets. un noklikšķiniet uz Mainīt
12. ierakstot makro esat izvēlējies Kalifornijas pārdošanas tirgu no lauka abās kopsavilkuma tabulās Pārdošanas tirgus. Kā redzams no attēla. 19, Kalifornijas tirgus tagad ir stingri noteikts makro VBA kodā.
13. Nomainiet "Kalifornijas" vērtību ActivePeet.Range izteiksme ("I1") .Value, kas attiecas uz vērtību I1 šūnā. Šajā posmā makro kodam vajadzētu izskatīties attēlā. 20. Pēc makro, aizveriet vizuālo pamata redaktoru un atgriezieties izklājlapā.
Fig. 20. Nomainiet vērtību "California" ar ActivePeet.Range izteiksme ("I1") .Value un aizveriet Visual Basic redaktoru
14. Tā joprojām ir tikai, lai nodrošinātu makro izpildi, izvēloties pārdošanas tirgu nolaižamajā sarakstā. Ar peles labo pogu noklikšķiniet uz nolaižamā saraksta un izvēlieties parametru Piešķirt makro. Izvēlieties makro Sinhorkets. un noklikšķiniet uz pogas labi.
15. Paslēpt rindas un kolonnas ar lapu laukiem konsolidētajās tabulās, kā arī tirgu sarakstu un indeksu formulas esat izveidojis.
Att. 21 parāda galīgo rezultātu. Jūs esat saņēmis lietotāja interfeisu, kas ļauj klientiem izvēlēties pārdošanas tirgu abās kopsavilkuma tabulās, izmantojot vienu nolaižamo sarakstu.
Izvēloties jauno elementu nolaižamajā sarakstā, kolonnu izmēri automātiski mainās, lai pielāgotos visus tos redzamos datus. Līdzīga programmas uzvedība ir garlaicīga, formatējot darba lapas veidni. Jūs varat novērst to, ja noklikšķināsiet uz peles labās pogas kopsavilkuma un izvēlieties komandu Ievietoja tabulas iestatījumus. Ekrānā parādās dialoglodziņš, kurā vēlaties atiestatīt izvēles rūtiņu. Automātiski maina kolonnu platumu, atjauninot.
Piezīme ir rakstīta, pamatojoties uz Jelenas grāmatu Aleksandru. . 12. nodaļa.
Izmantojot Excel rīkus, varat izvēlēties noteiktus datus no izlases diapazona, viens nosacījums vai vairāk. Lai atrisinātu šādas problēmas, tiek izmantoti masīva vai makro formulas vai makro. Apsvērt piemērus.
Kā padarīt paraugu Excel ar nosacījumu
Izmantojot masīva formulu, izvēlētie dati tiek parādīti atsevišķā tabulā. Kāda ir šīs metodes priekšrocība salīdzinājumā ar parasto filtru.
Avota tabula:
Vispirms iemācīties veikt paraugu uz viena skaitliskā kritērija. Uzdevums ir izvēlēties no preču galda ar cenu virs 200 rubļu. Viens veids, kā atrisināt filtrēšanas izmantošanu. Tā rezultātā tikai tie produkti, kas atbilst vaicājumam paliks avota tabulā.
Vēl viens risinājums ir izmantot masīva formulu. Saistītās vaicājumu rindas tiks ievietotas atsevišķā ziņojuma tabulā.
Vispirms mēs izveidojam tukšu galdu blakus oriģinālam: Duplicate galvenes, rindu un kolonnu skaits. Jaunā tabula aizņem E1: G10.Tube diapazonu E2: E10 ("Date" kolonnu) un ieviest šādu formulu: ( }.
Lai iegūtu masīva formulu, nospiediet taustiņu kombināciju Ctrl + Shift +. Nākamajā slejā - "produkts" - mēs ieviešam līdzīgu cieto formulu: ( ). Ir mainījies tikai pirmais funkcijas arguments.
Ar "Cena" kolonnā mēs ieviest to pašu formulu masīva, mainot pirmo argumentu par indeksa funkciju.
Tā rezultātā mēs saņemam ziņojumu par precēm ar cenu vairāk nekā 200 rubļu.
Šis paraugs ir dinamisks: kad jūs maināt pieprasījumu vai izskatu jaunu produktu avota tabulā, ziņojums automātiski mainīsies.
Uzdevuma numurs 2 - izvēlēties no avota tabulas preces, kas devās pārdošanā 09/20/2015. Tas nozīmē, ka atlases kritērijs ir datums. Ērtības gadījumā mēs iepazīstinām vēlamo datumu atsevišķai šūnai, I2.
Lai atrisinātu problēmu, tiek izmantota līdzīga masīva formula. Tikai kritērija vietā).
Šādas formulas tiek ieviestas citās kolonnās (cm iepriekš).
Tagad mēs izmantojam teksta kritēriju. Tā vietā, dienā I2 šūnā, mēs iepazīstinām ar tekstu "produkts 1". Nedaudz mainiet masīva formulu: ( }.
Šāda liela paraugu ņemšanas funkcija programmā Excel.
Paraugu ņemšana vairākiem nosacījumiem Excel
Pirmkārt, ņemiet divus ciparu kritērijus:
Uzdevums ir izvēlēties preces, kas maksā mazāk nekā 400 un vairāk nekā 200 rubļu. Mēs apvienojam terminu zīmi "*". Masnas formula ir šāda: ( }.!}
Tas ir pirmajā slejā ziņojuma tabulā. Par otro un trešo, mainiet pirmo argumentu funkciju indeksu. Rezultāts:
Lai ņemtu paraugu vairākos datumos vai skaitliskos kritērijos, mēs izmantojam līdzīgas cietas formulas.
Izlases paraugs Excel
Ja lietotājs strādā ar lielu datu apjomu, vēlākai analīzei var būt nepieciešams izlases paraugs. Katrai rindai var piešķirt nejaušu skaitu un pēc tam piemērot šķirošanu paraugu ņemšanai.
Avota datu kopa:
Vispirms ievietojiet divas tukšas kolonnas kreisajā pusē. A2 šūnā mēs nozīmējam līmes formulu (). Spin to visa kolonna:
Tagad kopējiet kolonnu ar izlases numuriem un ievietojiet to V slejā, tas ir nepieciešams, lai šie skaitļi nemainītos, veicot jaunus datus dokumentā.
Lai ievietotu vērtības, nevis formulu, noklikšķiniet uz peles labās pogas uz kolonnas un izvēlieties rīku "Īpašais ievietot". Logā, kas atveras, mēs ielieciet atzīmi pretī "vērtības" vienumam:
Tagad jūs varat kārtot datus kolonnā augošā vai dilstošā secībā. Sākotnējo vērtību pārstāvēšanas procedūra mainīsies. Mēs izvēlamies jebkuru rindu skaitu no augšas vai zemāk - mēs saņemam izlases paraugu.
Dažādi (39)
Excel bugs un glitches (3)
Kā iegūt sarakstu ar unikālām (ne atkārtotām) vērtībām?
Iedomājieties lielu dažādu vārdu sarakstu, pilnu vārdu, galda numurus utt. Un ir nepieciešams atstāt sarakstu ar visiem pašiem nosaukumiem no šī saraksta, bet tie neatkārtojas - i.e. Dzēst visus dublēt ierakstus no šī saraksta. Kā citādi tiek saukts par: izveidojiet sarakstu ar unikāliem elementiem, sarakstu, kas neatkārtojas, bez dublikātiem. Šim nolūkam ir vairāki veidi: iebūvēti Excel rīki, iebūvēti formulas un, visbeidzot, ar kodu Visual Basic pieteikumu (VBA) un kopsavilkuma tabulas. Šajā rakstā apsveriet katru no iespējām.
Izmantojot iebūvētās iezīmes Excel 2007 un vairāk
Excel 2007 un 2010 ir vieglāk izdarīt vienkāršu - ir īpaša komanda, kas tiek saukta -. Tā atrodas uz cilnes Dati (dati) apakšiedaļa Datu rīki (datu rīki)
Kā izmantot šo komandu. Iezīmējiet kolonnu (vai vairāk) Ar šiem datiem, kuros vēlaties dzēst dublētus ierakstus. Dodieties uz cilni Dati (dati) -Noņemiet dublikātus (noņemt dublikātus).
Ja izvēlaties vienu kolonnu, bet būs vairāk kolonnu ar datiem (vai vismaz vienu kolonnu), tad Excel piedāvās izvēlēties: paplašināt parauga diapazonu ar šo kolonnu vai atstāt izvēli, kā tas ir un dzēst datus tikai tajā Īpašs diapazons. Ir svarīgi atcerēties, ka, ja jums nav paplašināt diapazonu, dati tiks mainīti tikai vienā kolonnā, un dati blakus esošajā kolonnā paliks bez mazākās izmaiņas.
Logs parādīsies ar dublētiem noņemšanas parametriem 
Ievietojiet ērces pretī šīm kolonnām, dublikātus, kuros ir nepieciešams noņemt un noklikšķiniet uz Labi. Ja datu galvenes atrodas arī īpašajā diapazonā, tad labāk ielieciet karogu Maniem datiem ir virsrakstinejauši neizdzēsiet datus tabulā (Ja viņi pēkšņi pilnībā sakrīt ar nosaukuma vērtību).
1. metode: Advanced Filter
Attiecībā uz 2003. gada Excel viss ir sarežģītāks. Šāds līdzeklis nav līdzīgs Noņemt dublikātu. Bet ir tik brīnišķīgs rīks kā Advanced Filter. 2003. gadā šo rīku var atrast Dati -Filtrēt -Advanced Filter. Šīs metodes skaistums ir ar to, jūs nevarat sabojāt avota datus, bet, lai izveidotu sarakstu citā diapazonā. 2007-2010 Excel, viņš arī ir, bet mazliet rūdīta. Atrodas uz cilnes Dati (dati)Grupa Kārtot un filtrēt (kārtot un filtru) - Advanced)
Kā to izmantot: palaidiet norādīto rīku - parādās dialoglodziņš: 
- Ārstēšana: Izvēlēties Kopējiet rezultātu uz citu vietu (kopējiet uz citu vietu).
- Avota diapazons: Izvēlieties diapazonu ar datiem (Mūsu gadījumā tas ir A1: A51).
- Kritēriju diapazons (kritēriju diapazons): Šādā gadījumā mēs atstājam tukšu.
- Novietojiet rezultātu diapazonā (kopēt uz): Norādiet pirmo šūnu, lai izejas datus - jebkuru tukšu (Attēlā - E2).
- Ielieciet ērču Tikai unikāli ieraksti (tikai unikāli ieraksti).
- Zhmem. labi.
Piezīme: Ja vēlaties ievietot rezultātu citā lapā, tad vienkārši norādiet citu lapu nedarbosies. Jūs varat norādīt šūnu citā lapā, bet ... ALAS un AH ... Excel sniegs ziņu, ka tā nevar kopēt datus uz citām lapām. Bet to var apiet un diezgan vienkārši. Mums vienkārši ir jādarbojas Advanced Filter No šīs lapas, uz kuru mēs vēlamies novietot rezultātu. Un kā avota dati, izvēlieties datus no jebkuras lapas - tas ir atļauts.
Jūs varat arī nedarīt rezultātu citās šūnās, bet, lai filtrētu datus uz vietas. Dati no tā netiks ietekmēta - tas būs parastais datu filtrēšana.
Lai to izdarītu, jums vienkārši ir jāizvēlas apstrādes objekts Filtru saraksts uz vietas (filtrējiet sarakstu, vietā).
2. metode: formulas
Šī metode ir grūtāk saprast nepieredzējušiem lietotājiem, bet tas rada sarakstu ar unikālām vērtībām, nemainot sākotnējos datus. Nu, tas ir dinamiskāks: ja mainīsiet datus avota tabulā, rezultāts mainīsies. Dažreiz tas ir noderīgi. Es centīšos izskaidrot pirkstus, kas un ko: Piemēram, saraksts ar datiem atrodas A slejā (A1: A51, kur A1 ir galvene). Parādīt sarakstu, mēs būsim kolonnā ar, sākot ar C2 šūnu. C2 formula būs šāda:
(\u003d Indekss ($ 2 $: $ 51; mazākais (ja (skaitīts ($ C $ 1: C1; $ 2: $ $ 51) \u003d 0; rinda ($ 1: $ a $ 50)); 1)))
(\u003d Index ($ 2 $: $ 51; mazs (ja (Countif ($ C $ 1: C1; $ 2: $ $ 51) \u003d 0; rinda ($ 1: $ $ $ $ 50)); 1)))
Detalizēta analīze par šīs formulas darbu ir dota rakstā:
Jāatzīmē, ka šī formula ir masīva formula. To var teikt cirtainiem kronšteiniem, kuros tika noslēgta šī formula. Un šī formula tiek ieviesta šūnu kombinācijā - Ctrl+Maiņa.+Ievadīt. Pēc tam, kad mēs ievadījām šo formulu C2, mums ir kopēt to un ielīmēt vairākās rindās, lai precīzi parādītu visus unikālos priekšmetus. Tiklīdz formula zemākajās šūnās atgriezīsies #Number! - Tas nozīmē, ka visi vienumi tiek parādīti un zem formulas nav jēgas. Lai iegūtu kļūdu, lai izvairītos un padarītu formulu vairāk universālāku (bez pagarinot katru reizi, kad rodas kļūda), jūs varat izmantot ne-balss pārbaudi:
Par Excel 2007 un augstāk:
(\u003d Ja indekss (indekss ($ 2 $: $ 51; mazākais (ja (skaitīts ($ C $ 1: C1; $ 2: $ $ 51) \u003d 0; virkne ($ $ $ 1: $ 50)); 1)); ""))
(\u003d Iferror (indekss ($ 2: $ 51; mazs (ja (Countif ($ C $ 1: C1; $ 2 $: $ 51) \u003d 0; rinda ($ 1: $ $ 50)); 1)); ""))
Excel 2003:
(\u003d Ja (Eosh (mazākais (ja (cena ($ C $ 1: C1, $ 2 $: $ 51) \u003d 0; virkne ($ 1: $ 50)); 1)) ; ""; indekss ($ 2: $ $ 51; mazākais (ja (coartecles ($ C $ 1: C1; $ 2: $ $ 51) \u003d 0; rinda ($ 1 $ 1: $ 50)); 1)))))
(\u003d Ja (ISERR (ja (Countif ($ C $ 1: C1; $ 2: $ 51) \u003d 0; rinda ($ 1: $ 50)); 1)); "" indekss ($ 2 $: $ 51; mazs (ja (Countif ($ C $ 1: C1; $ 2: $ $ 51) \u003d 0; rinda ($ 1: $ 50 $ 50 )); 1)))
Tad kļūdas vietā #Number! (#Num!) Jums būs tukšas šūnas (nav gluži tukša, protams - ar formulām :-)).
Nedaudz vairāk par formulas formulas atšķirībām un niansēm un ja (Eosh var lasīt šajā rakstā: kā šūnā ar formulu, nevis kļūdu parādīt 0
3. metode: VBA kods
Šai pieejai būs nepieciešama makro atļauja un pamatzināšanas par darbu ar viņiem. Ja neesat pārliecināts par jūsu zināšanām, es iesaku jums izlasīt šos rakstus:
- Kas ir makro un kur meklēt to? Raksts ir pievienots video pamācību
- Kas ir modulis? Kādi ir moduļi? Būs nepieciešams saprast, kur ievietot tālāk norādītos kodus.
Abi kodi ir jānovieto standarta modulis. Makro ir jāatļauj.
Sākotnējie dati tiks atstāti tādā pašā secībā - saraksts ar datiem atrodas kolonnā "A" (A1: A51, kur A1 - nosaukums). Tikai izņemiet sarakstu, mēs nebūsim C slejā, bet E2 kolonnā, sākot ar E2 šūnu:
Sub ekstrakts_unique () Dim Vitem, Avarr, LI, LIG, REDIM AVARR (1 līdz rinda .End (XLUP)). Vērtība "(! Lang: šūnas (rindas.Count, 1) + 1: Avarr (Li, 1) \u003d Vitem cits: Err.Clear End, ja nākamais gals ar, ja LI tad. VRESIZE (LI)
Ar šo kodu jūs varat iegūt unikālu ne tikai no vienas kolonnas, bet arī no jebkura kolonnu un līniju klāsta. Ja string vietā
Diapazons ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
Norādiet izvēli.Value, koda koda rezultāts būs saraksts ar unikāliem elementiem no aktīvās lapas diapazonā. Tikai tad būtu jauki mainīt izejas šūnu, lai mainītu - vietā
Ievietojiet to, kurā nav datu.
Varat arī norādīt konkrētu diapazonu:
Diapazons ("C2", šūnas (rindas.Count, 3) .end (xlup)). Vērtība
Universālais kods unikālo vērtību izvēlei
Turpmāk minēto kodu var izmantot jebkuriem diapazoniem. Tas ir pietiekami, lai sāktu to, norādiet diapazonu ar vērtībām, lai izvēlētos tikai neatkārtojamo (vairāk nekā viena kolonna tiek piešķirta) un šūnu izvadīt rezultātu. Tiks apskatītas norādītās šūnas, no tām tiks izvēlētas tikai unikālas vērtības (tiek izlaisti tukšas šūnas), un iegūtais saraksts tiks ierakstīts, sākot ar norādīto šūnu.
| Sub ekstrakts_unique () Dim X, Avarr, LI kā Long Dim Avvals Dim Rvals kā diapazons, RResultCell, kā diapazons uz kļūdu atsāk nākamo "Pieprasiet šūnu adresi, lai izvēlētos unikālās vērtības Iestatiet rvals \u003d lietojumprogrammu. "Norādiet šūnu klāstu unikālo vērtību paraugu ņemšanai", "Datu pieprasījums", "A2: A51", tips: \u003d 8), ja RVals nav nekas tad "Ja tiek nospiesta atcelšanas poga Iziet no apakšas beigām, ja "Ja ir norādīta tikai viena šūna - nav jēgas izvēlēties Ja rvals.count \u003d 1, tad msgbox "Lai izvēlētos unikālas vērtības, jums ir jānorāda vairāk nekā viena šūna", Vbinformācija, "www.syt" izejas apakšā, ja "Izgriezt tukšas virknes un kolonnas ārpus darba diapazona Iestatiet rvals \u003d krustojas (RVals, RVals.Parent.useRange) "Ja ir norādītas tikai tukšas šūnas ārpus darba diapazona Ja RVals nav nekas, tad msgbox "Nav pietiekami daudz datu, lai izvēlētos vērtības", vbinformācija, "www.syt" izejas apakšslēgs, ja AVVALS \u003d RVALS.VALUE "Pieprasiet šūnu izvadīt rezultātu Iestatiet rresultcell \u003d lietojumprogrammu.Inputbox ( "Norādiet šūnu, lai ievietotu izvēlētās unikālās vērtības", "Datu pieprasījums", "E2", tips: \u003d 8), ja rresultcell nav nekas tad "Ja tiek nospiesta atcelšanas poga Iziet no apakšas beigām, ja "Nosakiet maksimālo iespējamo masīva dimensiju par rezultātu Redim Avarr (1 līdz rows.Count, no 1 līdz 1) "Izmantojot savākšanas objektu (kolekcija) "Izvēlieties tikai unikālus ierakstus, "Tā kā kolekcijas nevar saturēt atkārtotas vērtības Ar jaunu kolekciju par kļūdu atsākiet nākamo par katru X avvvā, ja len (cstr (x)), tad "Mēs izlaist tukšas šūnas .Add x, cstr (x) "Ja pievienotā elements jau ir kolekcijā - radīsies kļūda "Ja nav kļūdu - šī vērtība vēl nav veikta, "Pievienot iegūto masīvu Ja Err \u003d 0 Tad Li \u003d LI + 1 AVARR (LI, 1) \u003d X Else "Noteikti tīrīt kļūdas objektu Err.clear gals, ja beidzas, ja nākamais gals ar "Ierakstiet rezultātu uz lapas, sākot ar norādīto šūnu Ja li tad rresultcell.cells (1, 1) .Vale \u003d Avarr End Sub |
Sub ekstrakts_unique () Dim X, Avarr, LI, Long Dim Avvals Dim Rvals kā diapazons, RResultCell kā diapazons Resume Next "Pieprasīt adresi šūnas, lai izvēlētos unikālo komplektu rvals \u003d lietojumprogramma.Inputbox (" Norādiet šūnu klāstu Par paraugu ņemšanai unikālām vērtībām "," datu pieprasījums "," A2: A51 ", tips: \u003d 8), ja RVals nav nekas, tad" Ja izejas apakšslēgs, ja tiek nospiesta poga, "ja ir norādīta tikai viena šūna - tā nav jēgas izvēlēties, vai rvals.count \u003d 1, tad msgbox ", lai izvēlētos unikālas vērtības, jums ir jānorāda vairāk nekā viena šūna", vbinformācija, "www.sight" izejas apakšslēgs, ja "dziedāt tukšas virknes un kolonnas ārpus darbības diapazona Iestatiet rvals \u003d krustojas (RVals, RVals.Parent.useRange) "Ja tikai tukšas šūnas nav norādītas ārpus IF, ja RVals nav nekas, tad msgbox" nav pietiekami daudz datu, lai izvēlētos vērtības ", vbinformācija, www..value (! Lang: pieprasīt a šūnu izejas komplekts rresultcell \u003d lietojumprogrammu.Intputbox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}
4. metode: Kopsavilkuma tabulas
Vairākas nestandarta metode unikālo vērtību iegūšanai.
- Atlasiet vienu vai vairākas tabulas kolonnas, dodieties uz cilni Ievietojiet (INSERT) -Group Tabula (tabula) -Kopsavilkuma tabula (pivottable)
- Dialoglodziņā Izveidojiet PivotTable Create tabulu Pārbaudiet datu diapazona pareizību (vai instalēt jaunu datu avotu)
- norādiet konsolidētās tabulas izvietošanu:
- Uz jaunu lapu (jauna darblapa)
- Uz esošās lapas (esošā darblapa)
- apstipriniet pogas izveidi, nospiežot pogu LABI.
Jo Kopsavilkuma tabulas, apstrādājot datus, kas ievietoti rindās vai kolonnās, tikai unikālas vērtības tiek veiktas no tām vēlākai analīzei, tas nav nepieciešams no ASV pat konta, izņemot, lai izveidotu kopsavilkuma tabulu un ievietotu Dati par vēlamo kolonnu stīgu zonā vai kolonnās.
Attiecībā uz rakstu pievienoto failu:
Kas ir neērtības strādāt ar konsolidētu šajā gadījumā: mainot avota datus, konsolidētā tabula būs jāatjaunina manuāli: jāpiešķir jebkura šūna konsolidētā galda - kamēr peles poga - Atjaunot) vai cilne Dati (dati) -Atjauniniet visu (atsvaidzināt visu) -Atjaunot). Un, ja avota dati tiek regulēti dinamiski un sliktāk - tas būs nepieciešams atkārtoti norādīt avota datu diapazonu. Un vēl viens mīnus - nevar mainīt datus konsolidētajā tabulā. Tāpēc, ja jums ir nepieciešams strādāt ar iegūto sarakstu nākotnē, tad pēc tam, kad izveidotu vēlamo sarakstu, jums ir nepieciešams, lai kopētu to ar palīdzību un ievietot to uz vēlamo lapu.
Lai labāk izprastu visas darbības un uzzinātu, kā rīkoties ar konsolidētajām tabulām, es stingri iesaku lasīt rakstu Vispārīgu informāciju par kopsavilkuma tabulām - video pamācība ir pievienota tā, kurā es vizuāli pierādīt vienkāršību un ērtības darbu ar galvenajām iespējām Konsolidētās tabulas.
Piemērotajā piemērā papildus aprakstītajām metodēm ir reģistrēta nedaudz sarežģītāka unikālu elementu formulas un koda iegūšanas variācija, proti: unikālu elementu noņemšana pēc kritērijiem. Kāds ir jautājums: ja vienā uzvārda ailē un otrajā (c) dažos datos (failā ir mēneši), un tas ir nepieciešams, lai iegūtu kolonnas unikālās vērtības tikai izvēlētajam uzvārdam . Līdzīgas ekstrakcijas piemēri ir unikāli atrodas uz lapas. Ekstrakts pēc kritērija.
Lejupielādes piemērs:
(108,0 kid, 14 152 lejupielādes)
Šis pants palīdzēja? Kopīgojiet saiti ar draugiem! Video nodarbības
