Esența unei interogări selectate este de a selecta rânduri din tabelul sursă care îndeplinesc anumite criterii (similar cu utilizarea unuia standard). Să selectăm valorile din tabelul sursă folosind. Spre deosebire de utilizarea ( CTRL + SHIFT + L sau Date / Sortare și Filter / Filter) rândurile selectate vor fi plasate într-un tabel separat.
În acest articol, vom lua în considerare cele mai frecvente interogări, de exemplu: selectarea rândurilor de tabel a căror valoare dintr-o coloană numerică se încadrează într-un interval specificat (interval); selectarea rândurilor pentru care este data o anumită perioadă; sarcini cu 2 criterii de text și altele. Să începem cu interogări simple.
1. Un criteriu numeric (Selectați acele produse pentru care prețul este mai mare decât minimul)
fișier exemplu, foaie Un criteriu este numărul ).
Este necesar să afișați într-un tabel separat numai acele înregistrări (rânduri) din tabelul Sursă, care au un preț mai mare de 25.
Puteți rezolva cu ușurință acest lucru și sarcinile ulterioare cu ajutorul. Pentru aceasta, selectați anteturile tabelului sursă și apăsați CTRL + SHIFT + L... Folosind lista derulantă la rubrica Prețuri selectați Filtre numerice ..., apoi setați condițiile de filtrare necesare și faceți clic pe OK.
Vor fi afișate înregistrările care corespund criteriilor de selecție.
O altă abordare este utilizarea. Spre deosebire de rândurile selectate, acestea vor fi plasate într-un tabel separat - un fel, care, de exemplu, poate fi formatat într-un stil diferit de tabelul Sursă sau să facă alte modificări ale acestuia.
Plasați criteriul (prețul minim) în celulă E6 , tabel pentru date filtrate - în interval D10: E19 .
Acum să selectăm gama D11: D19 (coloana Produs) și introduceți:
INDEX (A11: A19;
MIC (DACĂ ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $ 10))
In loc de INTRODUCE apăsați comanda rapidă de la tastatură CTRL + SHIFT + ENTER(formula matrice va fi).
E11: E19 (coloana Preț) unde vom introduce una similară:
INDEX (B11: B19;
MIC (DACĂ ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $ 10))
Drept urmare, vom obține un nou tabel care va conține numai produse pentru care prețurile nu sunt mai mici decât cel specificat în celulă. E6 .
Pentru a arăta dinamismul cererii de probe primite, vă prezentăm în E6 valoare 55. Doar 2 înregistrări vor intra în noul tabel.
Dacă adăugați un nou produs cu un preț de 80 la tabelul Sursă, o nouă înregistrare va fi adăugată automat la noul tabel.
Notă... De asemenea, puteți utiliza și pentru a afișa date filtrate. Alegerea unui instrument specific depinde de sarcina cu care se confruntă utilizatorul.
Dacă nu vă simțiți confortabil formula matrice, care returnează mai multe valori, puteți utiliza o abordare diferită, care este discutată în secțiunile de mai jos: 5.a, 7, 10 și 11. În aceste cazuri, sunt utilizate.
2. Două criterii numerice (selectați acele produse pentru care prețul se încadrează în interval)
Să existe un tabel inițial cu o listă de produse și prețuri (vezi. fișier exemplu, foaieGama de numere).
Plasați criteriile (limite de preț inferioare și superioare) în interval E5: E6 .
Acestea. dacă prețul mărfurilor se încadrează în intervalul specificat, atunci o astfel de înregistrare va apărea în noul tabel de date filtrate.
Spre deosebire de sarcina anterioară, vom crea două: Produse și Prețuri (puteți face fără ele, dar sunt convenabile atunci când scrieți formule). Formulele corespunzătoare ar trebui să apară în Managerul de nume ( Formule / Nume definite / Manager de nume) după cum urmează (vezi figura de mai jos).
Acum să selectăm gama D11: D19 și în introducem:
INDEX (Bunuri;
CEL MAI PUŢIN(
IF (($ E $ 5<=Цены)*($E$6>= Prețuri); LINE (Prețuri); "");
In loc de INTRODUCE apăsați comanda rapidă de la tastatură CTRL + SHIFT + ENTER.
Vom efectua aceleași manipulări cu gama E11: E19 unde vom introduce unul similar:
INDEX (Prețuri;
CEL MAI PUŢIN(
IF (($ E $ 5<=Цены)*($E$6>= Prețuri); LINE (Prețuri); "");
LINE (Prețuri) -LINE ($ B $ 10)) - LINE ($ B $ 10))
Drept urmare, vom obține un nou tabel care va conține numai produse pentru care prețurile se încadrează în intervalul specificat în celule E5 și E6 .
Pentru a arăta dinamismul raportului primit (Cerere de selecție), intrăm în E6 valoarea 65. Încă o înregistrare din tabelul Sursă va fi adăugată la noul tabel, satisfăcând noul criteriu.
Dacă adăugați un nou produs cu un preț cuprins între 25 și 65 în tabelul Sursă, atunci o nouă înregistrare va fi adăugată la noul tabel.
Fișierul eșantion conține, de asemenea, formule matrice care gestionează erorile atunci când coloana Preț conține o valoare de eroare, cum ar fi # DIV / 0! (vezi foaia Eroare la procesare).
Următoarele sarcini sunt rezolvate într-un mod similar, deci nu le vom lua în considerare în detaliu.
3. Un criteriu Data (Selectați acele produse pentru care data livrării se potrivește cu cea specificată)
fișier exemplu, foaieUn criteriu - Data).
Pentru a selecta rânduri, se utilizează formule matrice, similare cu Task1 (în locul criteriului<= используется =):
=INDEX (A12: A20; MIC (IF ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B 11 USD))
INDEX (B12: B20; MIC (IF ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B 11 USD))
4. Două criterii Data (Selectați acele produse pentru care Data livrării se încadrează în interval)
Să presupunem că există un tabel inițial cu o listă de mărfuri și date de livrare (a se vedea. fișier exemplu, foaieInterval de date).
Rețineți că coloana Data NU este SORTATĂ.
Soluție 1: Puteți utiliza pentru a filtra rândurile.
Intrați în celulă D12 formula matrice:
INDEX (A $ 12: A $ 20;
MARE (($ E $ 6<=$B$12:$B$20)*($E$7>= $ B $ 12: $ B $ 20) * (LINE ($ B $ 12: $ B $ 20) -LINE ($ B $ 11));
$ J $ 12-LINE (A12) + LINE ($ B $ 11) +1))
Notă: După introducerea formulei, în loc de tasta ENTER, apăsați combinația de taste CTRL + SHIFT + ENTER. Această comandă rapidă de la tastatură este utilizată pentru a introduce formule matrice.
Copiați formula matricei până la numărul dorit de celule. Formula va returna numai acele valori ale Produselor care au fost livrate în intervalul de date specificat. Restul celulelor vor conține #NUM! Erori. Erori în fișier exemplu (foaia 4 interval de date) .
Ar trebui introdusă o formulă similară pentru datele din coloana E.
Într-o celulă J12 a calculat numărul de rânduri ale tabelului sursă care îndeplinesc criteriile:
COUNTIFS (B12: B20; "> =" & $ E $ 6; B12: B20; "<="&$E$7)
Rândurile de tabel sursă care îndeplinesc criteriile sunt.
Soluția 2: Pentru a selecta rânduri, puteți utiliza formule matrice similare cu Task2 (adică):
=INDEX (A12: A20; SMALL (IF (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
INDICE (B12: B20; MIC (IF (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
Pentru a introduce prima formulă, selectați un interval de celule G12: G20 ... După introducerea formulei, în loc de tasta ENTER, apăsați combinația de taste CTRL + SHIFT + ENTER.
Soluția 3: Dacă coloana Data este SORTED, atunci nu este nevoie să utilizați formule matrice.
În primul rând, trebuie să calculați prima și ultima poziție a rândurilor care îndeplinesc criteriile. Apoi tipăriți liniile.
Acest exemplu demonstrează încă o dată cât de ușor este să scrieți formule.
5. Un criteriu Data (Selectați acele mărfuri pentru care Data livrării nu este mai devreme / nu mai târziu de cea specificată)
Să presupunem că există un tabel inițial cu o listă de mărfuri și date de livrare (a se vedea. fișier exemplu, foaie Un criteriu - Data (nu mai târziu) ).
Pentru a selecta rândurile a căror dată nu este anterioară (inclusiv data în sine), utilizați formula matricei:
=INDEX (A12: A20; MIC (IF ($ E $ 7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
De asemenea, fișierul de exemplu conține formule pentru condiții: Nu mai devreme (fără a include); Nu mai târziu (inclusiv); Nu mai târziu (fără a include).
7. Un criteriu de text (Selectați produse de un anumit tip)
Să existe un tabel inițial cu o listă de produse și prețuri (vezi. fișier exemplu, foaieUn criteriu - Textul).
8. Două criterii de text (mărfuri selectate de un anumit tip livrate într-o lună dată)
Să existe un tabel inițial cu o listă de produse și prețuri (vezi. fișier exemplu, foaie 2 criterii - text (I) ).
INDEX ($ A $ 11: $ A $ 19;
MICĂ (DACĂ (($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19); LINE ($ A $ 11: $ A $ 19) -LINE ($ A $ 10); 30); LINE (INDIRECT („A1: A” & ROWS ($ A $ 11: $ A $ 19)))))
Expresie ($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19) stabilește ambele condiții (articol și lună).
Expresie LINE (INDIRECT („A1: A” & ROWS ($ A $ 11: $ A $ 19))) forme (1: 2: 3: 4: 5: 6: 7: 8: 9), adică numerele rândurilor din tabel.
9. Două criterii de text (produse selectate de anumite tipuri)
Să existe un tabel inițial cu o listă de produse și prețuri (vezi. fișier exemplu, foaie2 criterii - text (SAU)).
Spre deosebire de problema 7, selectați rândurile cu mărfuri de 2 tipuri ().
Formula matricei este utilizată pentru a selecta rândurile:
INDEX (A $ 11: A $ 19;
LARGE ((($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19)) * (ROW ($ A $ 11: $ A $ 19 ) -LINE ($ A $ 10)); COUNTIF ($ A $ 11: $ A $ 19; $ E $ 6) + COUNTIF ($ A $ 11: $ A $ 19; $ E $ 7) -ROWS ($ A $ 11: A11) +1))
Condiție ($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19) garantează că numai tipurile de bunuri specificate vor fi selectate din celulele galbene (Produs2 și Produs3). Semnul + (adăugare) este utilizat pentru sarcină (trebuie îndeplinit cel puțin un criteriu).
Expresia de mai sus va returna o matrice (0: 0: 0: 0: 1: 1: 1: 0: 0). Înmulțind-o cu expresia LINE ($ A $ 11: $ A $ 19) -LINE ($ A $ 10), adică pe (1: 2: 3: 4: 5: 6: 7: 8: 9), obținem o serie de poziții (numere de rând de tabel) care îndeplinesc criteriile. În cazul nostru, va fi o matrice (0: 0: 0: 0: 5: 6: 7: 0: 0).
De exemplu, prezentăm soluții la următoarea problemă: Selectați produse, al căror preț se află într-un anumit interval și se repetă de un număr specificat de ori sau mai multe.
Să luăm tabelul loturilor de bunuri ca cel inițial.
Să presupunem că suntem interesați de câte și ce loturi de bunuri au fost furnizate la un preț de 1000 de ruble. până la 2000r. (criteriul 1). Mai mult, trebuie să existe cel puțin 3 părți cu același preț (criteriul 2).
Soluția este o formulă matrice:
MIC (ROW ($ A $ 14: $ A $ 27)) * ($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10); F14 + ($ G $ 8- $ G $ 9))
Această formulă returnează numerele de linie care îndeplinesc ambele criterii.
Formulă = SUMPRODUCT (($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10)) numără numărul de rânduri care îndeplinesc criteriile.
11. Folosim valoarea criteriului (Orice) sau (Toate)
ÎN exemplu de fișier pe foaia „11. Criteriu Orice sau (Toate)” această variantă a criteriului a fost implementată.
Formula în acest caz trebuie să conțină funcția IF (). Dacă este selectată valoarea (Toate), atunci formula este utilizată pentru a afișa valori fără a lua în considerare acest criteriu. Dacă este selectată orice altă valoare, atunci criteriul funcționează în mod obișnuit.
IF ($ C $ 8 = "(Toate)";
SMALL ((LINE ($ B $ 13: $ B $ 26)) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8); F13 + ($ G $ 6 - $ G $ 7));
SMALL ((LINE ($ B $ 13: $ B $ 26)) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8) * ($ C $ 13: $ C $ 26 = $ C $ 8); F13 + ($ G $ 6- $ G $ 7)))
Restul formulei este similar cu cele discutate mai sus.
Colectare de date
Creați un raport privind eșantionul din Foaia 5 în coloana „Performanță calitativă, procent”. (din foaia 8, tabelul 7)
Pentru a eșantiona date, trebuie să efectuați următorii pași:
Determinați numărul de elemente ale unei noi matrice în funcție de o condiție dată, introducând o variabilă utilizând operatorul InputBox
Declarați și redeclarați o nouă matrice
Formați o nouă matrice. Pentru a face acest lucru, trebuie să setați numărul primului element al noii matrice u = 1. Apoi se execută un ciclu, în care condiția de selecție este scrisă în conformitate cu coloana „Performanță calitativă, procent”. Dacă rezultatul verificării este adevărat, atunci elementul matricei analizate devine un element al matricei noi.
Afișați elementul nou pe foaia 8
Sub Raport Exemplu ()
Foi („Sheet8”). Selectați
Dim A () Ca variantă
Redimensionare A (1 la n1, 1 la m)
VVOD „Sheet5”, A, n1, m, 4
C = InputBox („Introduceți o condiție”)
Foi („Sheet8”). Celule (5.11) = C
Pentru i = 1 la n1
Dacă A (i,
8) >
d = d + 1
Foi („Sheet8”). Celule (5,10) = d
Dim B () Ca variantă
Redimensionare B (1 la d, 1 la m)
Pentru i = 1 la n1
Dacă A (i,
8)> Foi ("Sheet8"). Celulele (5.11) Apoi
Pentru j = 1 până la m
B (u, j) = A (i, j)
u = u + 1
Pentru i = 1 la d
Pentru j = 1 până la m
Foi („Sheet8”). Celule (i + 4, j) = B (i, j)
Fig. 6. Date de tabel după preluare
Crearea unei macro automate pe o selecție
Pornim înregistrarea macro-ului. Instrumente> Macro> Începeți înregistrarea> OK. Va apărea un pătrat unde se află butonul de oprire a înregistrării. În Sheet5 (raport), selectați tabelul fără anteturi și totaluri, copiați-l în Sheet10 (selecție automată). Selectați tabelul fără anteturi și în elementul de meniu, selectați Date> Filtru> Filtru automat> selectați starea> OK. Marcăm coloana după care vom sorta. Terminăm munca macro.
Sub Macro2Sample ()
"Macro2Setup Macro
Foi („Sheet5”). Selectați
Selecţie. Copie
Foi („Sheet9”). Selectați
Foaie activă. Pastă
Interval ("H5: H17"). Selectați
Cerere. CutCopyMode = False
Selecţie. AutoFilter
Foaie activă. Interval („$ H $ 5: $ H $ 17”). Câmpul AutoFilter: = 1, Criteria1: = "> 80", _
Operator: = xlAnd
Gama ("G22"). Selectați
Fig. 7. Date de tabel după selecție automată
Determinarea valorii maxime și minime
Determinați valorile maxime și minime pentru coloanele „Total”, „Performanță absolută, procent.”, „Performanță calitativă” (tabelul 9, foaia 10)
Pentru a determina valorile maxime și minime, trebuie să faceți următoarele:
Setați variabila de referință, care va fi minimul curent (maxim)
Fiecare element al populației este comparat unul câte unul cu minimul actual (maxim), iar dacă acest element nu îndeplinește condițiile de căutare (în cazul unui minim, este mai mare, iar în cazul unui maxim, mai puțin) , atunci valoarea de referință este înlocuită cu valoarea elementului comparat
După o scanare completă a tuturor elementelor din variabila minimului curent (maxim), se găsește minimul real (maxim)
Valoarea minimului (maxim) este afișată în celulele corespunzătoare
Sub minmax ()
Dim A () Ca variantă
n1 = Foi ("Sheet4"). Celule (5.12)
m = Foi ("Sheet2"). Celule (5.12)
Redimensionare A (1 la n1, 1 la m)
VVOD „Sheet5”, A, n1, m, 4
VIVOD „Sheet10”, A, n1, m, 4
VVOD „Sheet10”, A, n1, m, 4
Pentru j = 3 până la m
maxA = 0,00001
minA = 1.000.000
Pentru i = 1 la n1
Dacă A (i, j)> maxA Atunci
maxA = A (i, j)
Dacă A (i, j)< minA Then
minA = A (i, j)
Foi („Sheet10”). Celule (i + 4 + 2, j) = maxA
Foi („Sheet10”). Celulele (i + 4 + 3, j) = minA
O macro este o secvență de acțiuni care a fost înregistrată și salvată pentru referințe viitoare. Macro-ul salvat poate fi redat printr-o comandă specială. Cu alte cuvinte, puteți să vă înregistrați acțiunile într-o macro, să o salvați și apoi să permiteți altor utilizatori să redea acțiunile salvate în macro cu o simplă apăsare de tastă. Acest lucru este util mai ales atunci când se distribuie rapoartele PivotTable.
Să presupunem că doriți să le oferiți clienților posibilitatea de a grupa rapoartele PivotTable în funcție de lună, trimestru și an. Din punct de vedere tehnic, procesul de grupare poate fi realizat de orice utilizator, dar este posibil ca unii dintre clienții dvs. să nu considere necesar să înțeleagă acest lucru. Într-un astfel de caz, puteți înregistra o macrocomandă pentru grupare pe lună, alta pe trimestru și a treia după an. Apoi creați trei butoane - unul pentru fiecare macro. Apoi, clienții dvs. care nu fac parte din tabelul pivot trebuie să facă clic doar pe un buton pentru a grupa corect raportul tabelului pivot.
Principalul avantaj al utilizării macro-urilor în rapoartele PivotTable este de a permite clienților să execute rapid în tabele rezumative operații pe care nu le pot efectua în mod normal. Acest lucru crește semnificativ eficiența analizei datelor furnizate.
Descărcați nota în format sau, descărcați cu exemple (în interior fisier Excel cu macro-uri; politica furnizorului nu vă permite să încărcați direct un fișier de acest format pe site).
Înregistrare macro
Aruncați o privire la tabelul pivot prezentat în Fig. 1. Puteți actualiza acest tabel pivot făcând clic dreapta în interiorul acestuia și alegând Reîmprospăta... Dacă ați înregistrat acțiunile ca macro în timp ce actualizați tabelul pivot, atunci dvs. sau orice alt utilizator puteți relua aceste acțiuni și puteți actualiza tabelul pivot ca urmare a executării macro-ului.
Orez. 1. Înregistrarea acțiunilor în timpul actualizării acestui tabel pivot vă va permite să actualizați datele în viitor ca urmare a rularii macro-ului
Primul pas în înregistrarea unei macro este să invocați o casetă de dialog Înregistrare macro... Accesați fila Dezvoltatorul panglică și faceți clic pe buton Înregistrare macro... (Dacă nu puteți găsi fila de pe panglică Dezvoltatorul, selectați fila Fişierși faceți clic pe buton Parametrii... În caseta de dialog care apare Opțiuni Excel Selectați o categorie Personalizarea panglicii iar în lista din dreapta, bifați caseta Dezvoltatorul... Ca rezultat, pe panglică va apărea o filă Dezvoltatorul.) Mod alternativîncepeți înregistrarea unui macro - faceți clic pe buton (Fig. 2).
În caseta de dialog Înregistrare macro introduceți următoarele informații despre macro (Figura 3):
Numemacro... Numele ar trebui să descrie acțiunile efectuate de macro. Numele trebuie să înceapă cu o literă sau o subliniere; nu trebuie să conțină spații sau alte caractere ilegale; nu trebuie să fie același cu numele Excel încorporat sau numele unui alt obiect din registrul de lucru.
Combinaţiechei... Puteți introduce orice literă în acest câmp. Va deveni parte a comenzii rapide de la tastatură care va fi folosită pentru redarea macro-ului. Combinația de taste este opțională. În mod implicit, numai Ctrl este oferit ca început al unei combinații. Dacă doriți ca combinația să includă și Shift, tastați litera în fereastră în timp ce țineți apăsată tasta Shift
salvaîn... Aici este stocată macrocomanda. Dacă urmează să distribuiți raportul PivotTable către alți utilizatori, selectați opțiunea Acestcarte... Excel vă permite, de asemenea, să salvați macro-ul în Carte noua sau în Macro carte personală.
Descriere... Acest câmp este utilizat pentru a introduce o descriere a macro-ului creat.
Orez. 3. Setarea ferestrei Înregistrare macro
Deoarece macro-ul actualizează tabelul pivot, selectați numele Actualizare date... De asemenea, puteți atribui o macro unei combinații Taste Ctrl+ Shift + Q. Amintiți-vă că după crearea unei macro, veți utiliza această comandă rapidă de la tastatură pentru a o rula. Selectați opțiunea ca locație pentru stocarea macro-ului Această carteși faceți clic pe Bine.
După ce faceți clic în caseta de dialog Înregistrare macro pe buton Bineîncepe înregistrarea macro. În acest moment, toate acțiunile pe care le efectuați în Excel vor fi înregistrate.
Faceți clic dreapta în zona PivotTable și alegeți Reîmprospăta(ca în Fig. 1, dar în modul de înregistrare macro). După actualizarea tabelului pivot, puteți opri procesul de înregistrare macro utilizând butonul Opriți înregistrarea filele Dezvoltatorul... Sau faceți clic din nou pe butonul prezentat în fig. 2.
Așa că tocmai ați înregistrat prima dvs. macro. Acum puteți executa macro utilizând comanda rapidă de la tastatură Ctrl + Shift + Q.
Avertisment de siguranță macro. Trebuie remarcat faptul că dacă macro-urile sunt înregistrate de utilizator, acestea vor fi executate fără restricții din partea subsistemului de securitate. Cu toate acestea, la distribuire registru de lucru conținând macrocomenzi, trebuie să oferiți altor utilizatori posibilitatea de a vă asigura că nu există niciun risc în deschiderea fișierelor de lucru, iar executarea macrocomenzilor nu va duce la o infecție cu virus a sistemului. În special, veți observa imediat că fișierul eșantion utilizat în acest capitol nu va funcționa complet decât dacă permiteți în mod special Excel să ruleze macrocomenzi pe acesta.
Cel mai simplu mod de a vă păstra macrocomenzile în siguranță este de a crea o locație de încredere - un dosar în care vor fi plasate doar cărțile de lucru „de încredere” fără virus. Locația de încredere permite dvs. și clienților dvs. să execute macrocomenzi în registrele de lucru fără restricții de securitate (acest comportament persistă atât timp cât registrele de lucru se află în dosarul de încredere).
Pentru a configura o locație de încredere, urmați acești pași.
Selectați fila panglică Dezvoltatorulși faceți clic pe buton Securitate macro... O fereastră de dialog va apărea pe ecran. Centrul de încredere.
Faceți clic pe buton Adăugați o locație nouă.
Faceți clic pe buton Prezentare generală pentru a specifica un folder pentru fișierele de lucru în care aveți încredere.
După ce specificați o locație de încredere, macro-urile arbitrare vor rula în mod implicit pentru toate registrele de lucru din ea.
Modelul de securitate a fost îmbunătățit în Excel 2013. Fișierele din registrele de lucru care anterior erau „de încredere” sunt acum amintite; după deschidere Cărți de lucru Excelși faceți clic pe buton Includeți conținut Excel își amintește această stare. Ca urmare, această carte se încadrează în categoria celor de încredere, iar întrebările inutile nu sunt puse în timpul deschiderii sale ulterioare.
Crearea unei interfețe utilizator utilizând controale de formular
Rularea unei macro utilizând combinația de taste Ctrl + Shift + Q vă va ajuta atunci când există o singură macro în raportul PivotTable. (În plus, utilizatorii trebuie să cunoască această combinație.) Dar să presupunem că doriți să oferiți clienților dvs. mai multe macro-uri care efectuează acțiuni diferite. În acest caz, trebuie să le oferiți clienților un sistem de înțelegere și într-un mod simplu rulați fiecare macro fără a fi nevoie să memorați combinațiile de taste. Soluția perfectă este simplu interfața cu utilizatorul ca o colecție de controale, cum ar fi butoane, bare de derulare și alte instrumente care vă permit să executați macrocomenzi cu clicuri de mouse.
Excel vă oferă un set de instrumente concepute pentru a crea o interfață cu utilizatorul direct într-o foaie de calcul. Aceste instrumente se numesc controale de formă. Ideea de bază este că este posibil să introduceți un control de formular foaie de calculși atribuiți-i macro-ul înregistrat anterior. Odată alocat unui control, macro-ul va rula făcând clic pe acel control.
Controalele formularului pot fi găsite în grup Controale de formular file de panglică Dezvoltatorul... Pentru a deschide paleta de controale, faceți clic în acest grup pe buton Introduce(fig. 4).
Orez. 4. Controlul formularului Buton
Vă rugăm să rețineți: pe lângă controalele de formular, paleta conține și Comenzi ActiveX... Deși sunt similare, programatic sunt obiecte complet diferite. Controale de formular cu dizabilitățile lor și setări simple special concepute pentru plasarea pe foi de lucru. În același timp Comenzi ActiveX utilizat în principal în forme personalizate. Faceți o regulă să plasați numai controale de formular pe foile de lucru.
Ar trebui să selectați comenzile care se potrivesc cel mai bine sarcinii la îndemână. În acest exemplu, clienții trebuie să poată actualiza tabelul pivot făcând clic pe un buton. Faceți clic pe control Buton, mutați indicatorul mouse-ului în poziția din foaia de lucru în care doriți să fie butonul și faceți clic pe.
După ce plasați butonul în tabel, se va deschide o casetă de dialog Atribuiți o macro obiect(fig. 5). Selectați macrocomanda necesară (în cazul nostru - Actualizare dateînregistrat mai devreme) și faceți clic pe Bine.
Orez. 5. Selectați macrocomanda care va fi atribuită butonului și faceți clic pe buton Bine... ÎN acest caz ar trebui aplicată o macro Actualizare date
După plasarea tuturor controalelor necesare în raportul PivotTable, puteți formata tabelul pentru a crea o interfață de bază. În fig. 6 arată raportul PivotTable după formatare.
Modificarea unei macro înregistrate
Ca urmare a înregistrării unei macro Program Excel creează un modul care stochează acțiunile pe care le-ați efectuat. Toate acțiunile înregistrate sunt reprezentate de liniile codului VBA care alcătuiesc macro-ul. Puteți adăuga diferite tipuri de tabele pivot în rapoartele PivotTable. funcționalitate personalizând codul VBA pentru a obține rezultatele dorite. Pentru a înțelege mai ușor cum funcționează toate acestea, să creăm o nouă macrocomandă care afișează primele cinci înregistrări ale clienților. Accesați fila Dezvoltatorulși faceți clic pe buton Înregistrare macro... Caseta de dialog prezentată în Fig. 7. Denumiți macro-ul creat PrimulCliențiși specificați locația de salvare Această carte... Clic Bine pentru a începe înregistrarea macro-ului.
După ce începeți înregistrarea, faceți clic pe săgeata de lângă casetă Numele clientului, Selectați Filtrează după valoareși opțiune Primul 10(Fig.8a). În caseta de dialog care apare, configurați setările așa cum se arată în Fig. 8b. Aceste setări vă spun să afișați datele primilor cinci clienți în ceea ce privește vânzările. Clic Bine.
Orez. 8. Selectați filtrul (a) și reglați parametrii (b) pentru a afișa primii cinci clienți după vânzări
După ce ați înregistrat cu succes toți pașii necesari pentru a recupera primii 5 clienți potențiali, accesați fila Dezvoltatorulși faceți clic pe buton Opriți înregistrarea.
Acum aveți o macrocomandă care va filtra tabelul pivot pentru a recupera primii 5 clienți de vânzări. Este necesar să faceți macro-ul să reacționeze la starea barei de derulare, adică folosind bara de derulare, trebuie să puteți indica macro-ului numărul de clienți ale căror date vor fi afișate în raportul tabelului pivot. Astfel, utilizând bara de derulare, utilizatorul va putea să obțină primii cinci, opt opt sau treizeci și doi de clienți de top după cum consideră potrivit.
Pentru a adăuga o bară de derulare în foaia de calcul, accesați fila Dezvoltatorul, faceți clic pe buton Introduce, selectați un control pe paletă Bara de derulareși așezați-l pe foaia de lucru. Faceți clic dreapta pe control Bara de derulare Format obiect... Se va deschide o casetă de dialog Formatul de control(fig. 9). În acesta, efectuați următoarele modificări la setări: Valoare minimă atribuiți valoarea 1 parametrului Valoare maximă - valoare 200, și în teren Comunicarea celulară introduceți valoarea $ M $ 2 pentru a afișa valoarea barei de derulare în celula M2. Faceți clic pe buton Bine pentru a aplica setările specificate anterior.
Acum trebuie să se potrivească cu macro-ul înregistrat recent PrimulClienți cu control Bara de derulare pe foaia de lucru. Faceți clic dreapta pe control Bara de derulare si in meniul contextual alege echipa Atribuiți o macro pentru a deschide caseta de dialog Macro Assignment. Atribuiți macrocomanda înregistrată la bara de derulare Clienții FirstN... Macrocomanda va rula de fiecare dată când se face clic pe bara de derulare. Testați bara de defilare creată. După ce faceți clic pe bandă, macro-ul va rula Clienții FirstN iar numărul din celula M2 se va schimba pentru a indica starea barei de derulare. Numărul din celula M2 este important deoarece este utilizat pentru a lega macro-ul de bara de derulare.
Singurul lucru care rămâne de făcut este să faceți ca procesul macro să fie numărul din celula M2, asociindu-l cu bara de derulare. Pentru a face acest lucru, trebuie să accesați codul VBA al macro-ului. Pentru a face acest lucru, accesați fila Dezvoltatorulși faceți clic pe buton Macrocomenzi... Se va deschide o casetă de dialog Macro(fig. 10). În acesta, puteți rula, șterge și edita macrocomanda selectată. Pentru a afișa codul VBA al unei macro pe ecran, selectați macro-ul și faceți clic pe buton Schimbare.
Orez. 10. Pentru a accesa codul macro VBA PrimulClienți, selectați macrocomanda și faceți clic pe buton Schimbare
Pe ecran va apărea o fereastră de editor Visual Basic cu codul VBA al macro-ului (fig. 11). Obiectivul dvs. este să înlocuiți numărul 5 codificat, care este setat atunci când înregistrați macrocomanda, cu valoarea din celula M2 care este legată de bara de derulare. Inițial, a fost înregistrată o macro pentru a afișa primii cinci clienți cu cel mai mare venit.
Eliminați numărul 5 din cod și introduceți următoarea expresie:
ActiveSheet.Range ("M2"). Valoare
Adăugați două linii la începutul macro-ului pentru a șterge filtrele:
Gama ("A4"). Selectați
ActiveSheet.PivotTables ("PivotTable1") .PivotFields ("Customer Name") .ClearAllFilters
Codul macro ar trebui să arate acum ca cel prezentat în Fig. 12.
Închideți Editorul Visual Basic și reveniți la raportul PivotTable. Testați bara de derulare trăgând glisorul la 11. Macro-ul ar trebui să ruleze și să filtreze 11 înregistrări despre cei mai buni clienți prin vânzări.
Sincronizați două tabele pivot cu un singur meniu derulant
Raportul prezentat în Fig. 13 conține două tabele pivot. Fiecare dintre ele are un câmp de pagină care vă permite să selectați o zonă de vânzare. Problema este că de fiecare dată când selectați o piață în câmpul de pagină al unui tabel pivot, trebuie să selectați aceeași piață în câmpul de pagină al unui alt tabel pivot. Sincronizarea filtrelor între două tabele în timpul fazei de analiză a datelor nu este o mare problemă, dar există șansa ca dvs. sau clienții dvs. să uitați în continuare să o faceți.
Orez. 13. Cele două tabele pivot conțin câmpuri de pagină care filtrează datele în funcție de piață. Pentru a analiza datele unei piețe unice, trebuie să sincronizați ambele tabele pivot
O modalitate de a menține sincronizate aceste tabele pivot este de a utiliza o listă derulantă. Ideea este de a înregistra un macro care selectează piața dorită din domeniu Piața vânzărilorîn ambele tabele. Apoi, trebuie să creați o listă derulantă și să o completați cu numele piețelor de vânzări din două tabele pivot. În cele din urmă, macrocomanda înregistrată trebuie modificată pentru a filtra ambele tabele pivot folosind valorile din lista derulantă. Pentru a rezolva această problemă, trebuie să efectuați pașii următori.
1. Creați o nouă macrocomandă și dați-i un nume SynchMarket-uri... Când începe înregistrarea, selectați în câmp Piața de vânzări pentru ambele tabele pivot de vânzări Californiași opriți înregistrarea macro-ului.
2. Afișați o paletă de controale de formular și adăugați o listă derulantă în foaia de lucru.
3. Creați o listă codificată cu toate piețele PivotTable. Rețineți că primul element din listă este (Toate). Ar trebui să activați acest element dacă doriți să puteți selecta toate piețele din lista derulantă.
4. În acest moment, raportul PivotTable ar trebui să arate ca cel prezentat în Fig. paisprezece.
Orez. 14. Aveți la dispoziție toate instrumentele de care aveți nevoie: o macro care schimbă un câmp Piața vânzărilor ambele tabele pivot, o listă derulantă și o listă cu toate piețele de vânzări conținute în tabelul pivot
5. Faceți clic dreapta pe lista derulantă și selectați comanda Format obiect pentru a personaliza controlul.
6. Mai întâi, setați intervalul inițial de valori utilizate pentru completarea listei derulante, așa cum se arată în Figura 6-7. 15. În acest caz, vorbim despre lista piețelor de vânzări pe care le-ați creat la pasul 3. Apoi specificați celula care afișează numărul de serie al elementului selectat (în acest exemplu, aceasta este celula H1). Parametru Numărul de linii de listă determină câte linii vor fi afișate în lista derulantă în același timp. Faceți clic pe buton Bine.
Orez. 15. Setările listei derulante trebuie să indice lista piețelor de vânzări ca interval inițial de valori și să definească celula H1 ca punct de ancorare
7. Acum aveți posibilitatea de a selecta o piață de vânzări din lista derulantă, precum și de a defini numărul de serie asociat în celula H1 (Fig. 16). Se pune întrebarea: de ce se utilizează valoarea indicelui în locul numelui real al pieței? Deoarece meniul derulant nu returnează un nume, ci un număr. De exemplu, dacă selectați California din lista derulantă, valoarea 5 apare în celula H1, ceea ce înseamnă că California este al cincilea element din listă.
Orez. 16. Lista derulantă este acum completată cu numele piețelor, iar numărul de serie al pieței selectate este afișat în celula H1
8. Pentru a utiliza numărul de ordine în locul numelui pieței, trebuie să-l treceți folosind funcția INDEX.
9. Introduceți funcția INDEX, care convertește numărul de serie din celula H1 într-o valoare semnificativă.
10. Funcția INDEX ia două argumente. Primul argument reprezintă gama de valori din listă. În majoritatea cazurilor, veți utiliza același interval care populează meniul derulant. Al doilea argument este un număr secvențial. Dacă un număr de serie este introdus într-o celulă (de exemplu, în celula H1, ca în Fig. 17), atunci puteți face referire pur și simplu la această celulă.
Orez. 17. Funcția INDEX din celula I1 convertește numărul de secvență stocat în celula H1 într-o valoare. Veți utiliza valoarea din celula I1 pentru a schimba macro-ul
11. Editați macro-ul SynchMarket-uri folosind valoarea din celula I1 în locul valorii codificate tare. Accesați fila Dezvoltatorulși faceți clic pe buton Macrocomenzi... O fereastră de dialog va apărea pe ecran, așa cum se arată în Fig. 18. Selectați o macro în ea SynchMarket-uriși faceți clic pe butonul Modificare.
Orez. 18. Pentru a accesa codul VBA al unei macrocomenzi, selectați macrocomanda SynchMarket-uriși faceți clic pe Schimbare
12. La înregistrarea macro-ului, ați selectat zona de vânzări din California din câmpul din ambele tabele pivot. Piața vânzărilor... După cum se poate vedea din Fig. 19, piața din California este acum codificată în macro VBA.
13. Înlocuiți „California” cu Activesheet.Range („I1”). Valoare, care se referă la valoarea din celula I1. În acest moment, codul macro ar trebui să arate ca cel prezentat în Fig. 20. După modificarea macro-ului, închideți Editorul Visual Basic și reveniți la foaia de calcul.
Orez. 20. Înlocuiți „California” cu ActiveSheet.Range („I1”). Valorați și închideți Editorul Visual Basic
14. Rămâne doar să asigurați execuția macro-ului atunci când selectați o zonă de vânzare din lista derulantă. Faceți clic dreapta pe meniul derulant și selectați o opțiune Atribuiți o macro... Selectați o macrocomandă SynchMarketși faceți clic pe buton Bine.
15. Ascundeți rândurile și coloanele cu câmpurile de pagină din tabelele pivot și lista piețelor și a formulelor de index pe care le-ați creat.
În fig. 21 arată rezultatul final. Acum aveți o interfață cu utilizatorul care permite clienților să își selecteze zona de vânzare în ambele tabele pivot utilizând o singură listă derulantă.
Când selectați un element nou din lista derulantă, coloanele sunt redimensionate automat pentru a găzdui toate datele afișate în ele. Acest comportament al programului este destul de plictisitor la formatarea unui șablon de foaie de lucru. O puteți preveni făcând clic dreapta pe tabelul pivot și alegând Opțiuni pentru tabelul pivot... Pe ecran va apărea o casetă de dialog cu același nume, în care trebuie să debifați caseta de selectare Redimensionați automat coloanele la actualizare.
Notă bazată pe cartea lui Jelen, Alexander. ... Capitolul 12.
Folosind instrumentele Excel, puteți selecta date specifice dintr-un interval în ordine aleatorie, o condiție sau mai multe. Pentru a rezolva astfel de probleme, de regulă, se utilizează formule matrice sau macrocomenzi. Să aruncăm o privire la câteva exemple.
Cum se face o selecție în Excel în funcție de condiție
Când utilizați formule matrice, datele selectate sunt afișate într-un tabel separat. Care este avantajul aceasta metoda comparativ cu un filtru convențional.
Tabelul sursă:
În primul rând, să învățăm cum să facem o selecție pe baza unui singur criteriu numeric. Sarcina este de a selecta din masă produse cu un preț mai mare de 200 de ruble. O soluție este aplicarea filtrării. Ca urmare, numai acele produse care satisfac cererea vor rămâne în tabelul original.
O altă soluție este utilizarea unei formule matrice. Rândurile corespunzătoare cererii se vor încadra într-un raport de tabel separat.
Mai întâi, creăm un tabel gol lângă cel original: anteturi duplicate, numărul de rânduri și coloane. Noul tabel ocupă intervalul E1: G10. Acum selectați E2: E10 (coloana „Data”) și introduceți următoarea formulă: ( }.
Pentru a obține o formulă matrice, apăsați combinația de taste Ctrl + Shift + Enter. În coloana următoare - „Produs” - introducem o formulă de matrice similară: ( ). Numai primul argument al funcției INDEX s-a schimbat.
În coloana „Preț”, introduceți aceeași formulă matrice, schimbând primul argument al funcției INDEX.
Drept urmare, primim un raport privind mărfurile cu un preț mai mare de 200 de ruble.
O astfel de selecție este dinamică: atunci când interogarea se modifică sau apar produse noi în tabelul sursă, raportul se va modifica automat.
Activitatea numărul 2 - selectați din tabelul original bunurile care au fost puse în vânzare pe 20 septembrie 2015. Adică criteriul de selecție este data. Pentru comoditate, vom introduce data dorită într-o celulă separată, I2.
O formulă matrice similară este utilizată pentru a rezolva problema. Numai în locul unui criteriu).
Formule similare sunt introduse în alte coloane (a se vedea principiul de mai sus).
Acum folosim criteriul textului. În locul datei din celula I2, introduceți textul „Produsul 1”. Să schimbăm puțin formula matricei: ( }.
O astfel de funcție excelentă de selecție în Excel.
Selectarea în condiții multiple în Excel
În primul rând, să luăm două criterii numerice:
Sarcina este de a selecta bunuri care costă mai puțin de 400 și mai mult de 200 de ruble. Să combinăm condițiile cu semnul „*”. Formula matrice arată astfel: ( }.!}
Aceasta este pentru prima coloană a tabelului de raport. Pentru al doilea și al treilea - schimbăm primul argument al funcției INDEX. Rezultat:
Pentru a face o selecție după mai multe date sau criterii numerice, folosim formule matrice similare.
Eșantionare aleatorie în Excel
Când un utilizator lucrează cu o cantitate mare datele pot necesita eșantionare aleatorie pentru analiza ulterioară. Puteți atribui un număr aleatoriu fiecărui rând și apoi aplicați sortarea selecției.
Set de date original:
Mai întâi, să inserăm două coloane goale în stânga. În celula A2, scrieți formula RAND (). Să-l înmulțim cu întreaga coloană:
Acum copiem coloana cu numere aleatorii și o lipim în coloana B. Acest lucru este necesar pentru ca aceste numere să nu se schimbe atunci când datele noi sunt introduse în document.
Pentru a insera valori, nu o formulă, faceți clic dreapta pe coloana B și selectați instrumentul Lipire specială. În fereastra care se deschide, puneți o bifă în fața elementului „Valori”:
Acum puteți sorta datele din coloana B în ordine crescătoare sau descendentă. De asemenea, se va modifica ordinea în care sunt prezentate valorile originale. Selectăm orice număr de linii de sus sau de jos - vom obține un eșantion aleatoriu.
Diverse (39)
Bug-uri și erori Excel (3)
Cum obțin o listă de valori unice (non-duplicate)?
Imaginați-vă o listă mare de nume, nume, numere de personal etc. Și este necesar să lăsați o listă cu toate aceleași nume din această listă, dar pentru a nu se repeta - adică eliminați toate intrările duplicate din această listă. Așa cum se numește altfel: creați o listă de elemente unice, o listă de non-repetare, fără duplicate. Există mai multe modalități de a face acest lucru: instrumente Excel încorporate, formule încorporate și, în cele din urmă, utilizarea codului Visual Basic pentru aplicații (VBA) și tabele pivot. Acest articol va analiza fiecare dintre opțiuni.
Utilizarea funcțiilor încorporate în Excel 2007 și versiuni ulterioare
În Excel 2007 și 2010 este la fel de ușor ca decojirea perelor să o faci - există echipă specială, care se numește așa -. Se află pe filă Date subsecțiune Instrumente de date
Cum se folosește această comandă. Evidențiați coloana (sau mai multe) cu datele în care înregistrările duplicate trebuie șterse. Accesați fila Date -Eliminați duplicatele.
Dacă selectați o coloană, dar lângă aceasta vor exista mai multe coloane cu date (sau cel puțin o coloană), atunci Excel vă va oferi să alegeți: extindeți gama de selecție cu această coloană sau lăsați selecția așa cum este și ștergeți doar datele în intervalul selectat. Este important să ne amintim că, dacă nu extindeți intervalul, atunci datele vor fi modificate într-o singură coloană, iar datele din coloana alăturată vor rămâne neschimbate.
Va apărea o fereastră cu opțiuni pentru eliminarea duplicatelor 
Bifați casetele din fața acelor coloane, duplicatele în care doriți să le eliminați și faceți clic pe OK. Dacă anteturile de date se află și în intervalul selectat, atunci este mai bine să setați semnalizatorul Datele mele conțin anteturi pentru a evita ștergerea accidentală a datelor din tabel (dacă coincid brusc complet cu valoarea din titlu).
Metoda 1: Filtru avansat
În cazul Excel 2003, lucrurile sunt mai complicate. Nu există un astfel de instrument ca Eliminați duplicatele... Dar, pe de altă parte, există un instrument atât de minunat ca Filtru avansat... În 2003 acest instrument poate fi găsit în Date -Filtru -Filtru avansat... Frumusețea acestei metode este că o puteți folosi pentru a crea o listă într-o gamă diferită, mai degrabă decât să stricați datele originale. În Excel 2007-2010, este și el acolo, dar puțin ascuns. Situat pe filă Date, Grup Sortați și filtrați - Avansat
Cum se folosește: rulați instrumentul specificat - apare o casetă de dialog: 
- Tratament: Noi alegem Copiați rezultatul într-o altă locație.
- Gama de liste: Selectarea unui interval cu date (în cazul nostru este A1: A51).
- Gama de criterii:în acest caz, lăsați-l necompletat.
- Copiați în interval: indicăm prima celulă pentru afișarea datelor - orice gol (în imagine - E2).
- Bifeaza casuta Numai înregistrări unice.
- Apăsăm Bine.
Notă: dacă doriți să plasați rezultatul pe o altă foaie, atunci pur și simplu nu puteți specifica o altă foaie. Veți putea specifica o celulă pe o altă foaie, dar ... Vai și ah ... Excel va da un mesaj că nu poate copia date pe alte foi. Dar acest lucru poate fi eludat și simplu. Trebuie doar să alergi Filtru avansat din foaia pe care vrem să plasăm rezultatul. Și ca date inițiale, selectăm date din orice foaie - acest lucru este permis.
De asemenea, nu puteți transfera rezultatul către alte celule, ci filtrați datele la locul lor. Datele nu vor fi afectate în nici un fel - vor fi filtrarea obișnuită a datelor.
Pentru a face acest lucru, trebuie doar să selectați în secțiunea Procesare Filtrează lista, în loc.
Metoda 2: Formule
Această metodă este mai dificil de înțeles pentru utilizatorii fără experiență, dar creează o listă de valori unice fără a modifica datele originale. Ei bine, este și mai dinamic: dacă modificați datele din tabelul sursă, rezultatul se va schimba, de asemenea. Acest lucru este uneori util. Voi încerca să explic pe degetele mele ce și ce: de exemplu, aveți o listă cu date în coloana A (A1: A51, unde A1 este antetul). Vom afișa lista în coloana C, începând de la celula C2. Formula din C2 va fi după cum urmează:
(= INDEX ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A $ 50)); 1)))
(= INDEX ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A $ 50)); 1)))
O analiză detaliată a funcționării acestei formule este dată în articol:
Trebuie remarcat faptul că această formulă este o formulă matrice. Acest lucru se poate spune bretele, în care este inclusă această formulă. Și o astfel de formulă este introdusă într-o celulă cu o comandă rapidă de la tastatură - Ctrl+Schimb+introduce... După ce am introdus această formulă în C2, trebuie să o copiem și să o lipim în mai multe rânduri, astfel încât să afișăm cu exactitate toate elementele unice. De îndată ce formula din celulele inferioare revine #NUMĂR!- aceasta înseamnă că toate elementele sunt afișate și nu are rost să întindeți formula de mai jos. Pentru a evita eroarea și a face formula mai universală (fără a vă întinde de fiecare dată până când apare eroarea), puteți utiliza o verificare simplă:
pentru Excel 2007 și mai sus:
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; STRING ($ A $ 1: $ 50 USD)); 1)); ""))
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ 50 USD)); 1)); ""))
pentru Excel 2003:
(= IF (ISH (SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A 50 USD)); 1))))
(= IF (ISERR (SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A 50 USD)); 1))))
Apoi, în loc de o eroare #NUMĂR! (#NUM!) vei avea celule goale(nu complet gol, desigur - cu formule :-)).
Un pic mai multe detalii despre diferențele și nuanțele formulelor ESLIOSHIBKA și IF (EOSH poate fi citit în acest articol: Cum se arată 0 într-o celulă cu o formulă în loc de o eroare
Metoda 3: cod VBA
Această abordare va necesita rezolvarea macro-urilor și cunoștințe de bază despre lucrul cu acestea. Dacă nu sunteți sigur de cunoștințele dvs., vă recomand mai întâi să citiți aceste articole:
- Ce este un macro și unde îl pot găsi? un articol video este atașat la articol
- Ce este un modul? Ce module există? va fi obligat să afle unde se introduc codurile de mai jos
Ambele coduri de mai jos trebuie plasate în modul standard... Macrocomenzile trebuie permise.
Să lăsăm datele inițiale în aceeași ordine - lista cu datele se află în coloana „A” (A1: A51, unde A1 este un titlu)... Doar vom afișa lista nu în coloana C, ci în coloana E, începând de la celula E2:
Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 to 1) Cu colecție nouă la eroare Reîncepeți pentru fiecare vItem din interval ("A2", celule (Rows.Count, 1) .End (xlUp)). Valoare "(! LANG: Celule (Rows.Count, 1). End (xlUp) - definește ultima celulă completată în coloana A. Adăugați vItem, CStr (vItem) Dacă Err = 0 Atunci li = li + 1: avArr (li, 1) = vItem Else: Err.Clear End If Next End With If li Then .Resize (li) .Value = avArr End Sub
Folosind acest cod, puteți extrage valori unice nu numai dintr-o singură coloană, ci din orice gamă de coloane și rânduri. Dacă în loc de linie
Gamă ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
specificați Selecție.Valoare, atunci rezultatul codului va fi o listă de elemente unice din intervalul selectat pe foaia activă. Abia atunci ar fi bine să schimbi celula de ieșire a valorilor - în loc de
puneți-l pe cel în care nu există date.
De asemenea, puteți specifica un anumit interval:
Interval („C2”, celule (Rows.Count, 3). End (xlUp)). Valoare
Cod universal pentru selectarea valorilor unice
Codul de mai jos poate fi aplicat oricăror intervale. Este suficient să îl rulați, să specificați un interval cu valori pentru selectarea numai a celor care nu se repetă (sunt permise mai multe coloane) și o celulă pentru afișarea rezultatului. Celulele specificate vor fi scanate, dintre care numai valori unice(celulele goale sunt omise) și lista rezultată va fi scrisă începând cu celula specificată.
| Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "cere adresa celulelor pentru a selecta valori unice Setați rVals = Application.InputBox ( „Specificați un interval de celule pentru a testa probe de valori unice”, „Solicitați date”, „A2: A51”, Tastați: = 8) Dacă rVals nu este nimic atunci "dacă se face clic pe butonul Anulare Ieșiți Sub End Dacă "dacă este specificată o singură celulă, nu are rost să alegem Dacă rVals.Count = 1 Apoi MsgBox „Pentru a filtra valori unice, trebuie să specificați mai multe celule”, vbInformation, „www.site” Exit Sub End If "tăiați rândurile și coloanele goale din afara intervalului de lucru Set rVals = Intersect (rVals, rVals.Parent.UsedRange) "dacă sunt specificate numai celule goale în afara domeniului de lucru Dacă rVals nu este nimic, atunci MsgBox „Date insuficiente pentru a selecta valorile”, vbInformation, „www.site” Exit Sub End If avVals = rVals.Value "cere o celulă pentru a afișa rezultatul Setați rResultCell = Application.InputBox ( "Specificați o celulă pentru a insera valorile unice selectate", „Solicitați date”, „E2”, tastați: = 8) Dacă rResultCell nu este nimic atunci "dacă se face clic pe butonul Anulare Ieșiți Sub End Dacă "definiți dimensiunea maximă posibilă a matricei pentru rezultat ReDim avArr (1 la rânduri. Număr, 1 la 1) "folosind un obiect Colecție „selectați numai înregistrări unice, "deoarece colecțiile nu pot conține valori duplicat Cu o nouă colecție la eroare Reîncepeți pentru fiecare x În evaluări Dacă Len (CStr (x)) Apoi "sări peste celulele goale.Adăugați x, CStr (x) "dacă elementul adăugat există deja în colecție, va apărea o eroare "dacă nu există nicio eroare, această valoare nu a fost încă introdusă, "adăugați la matricea rezultată Dacă Err = 0 atunci li = li + 1 avArr (li, 1) = x Altfel "asigurați - vă că ați șters obiectul Error Err. Ștergeți sfârșitul dacă se termină dacă următorul se termină cu "scrie rezultatul pe foaie, începând de la celula specificată Dacă li, atunci rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub |
Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "solicitați adresa celulelor pentru a selecta valorile unice Set rVals = Application.InputBox (" Specify the range de celule pentru a selecta valori unice "," Cerere de date "," A2: A51 ", Tastați: = 8) Dacă rVals nu este nimic atunci" dacă se face clic pe butonul Anulare Exit Sub End Dacă "dacă este specificată o singură celulă , nu are rost să alegeți If rVals.Count = 1 Apoi MsgBox "Pentru a selecta valori unice, trebuie să specificați mai multe celule", vbInformation, "www.site" Exit Sub End If "am tăiat rândurile și coloanele goale în afara domeniului de lucru Set rVals = Intersect (rVals, rVals.Parent.UsedRange) "dacă sunt specificate numai celule goale în afara domeniului de lucru Dacă rVals nu este nimic, atunci MsgBox" Nu sunt suficiente date pentru a selecta valorile ", vbInformation," www..Value "(! LANG: solicitați o celulă pentru a afișa rezultatul Set rResultCell = Application.InputBox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "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!}
Metoda 4: tabele pivot
niste mod non-standard recuperarea valorilor unice.
- Selectați una sau mai multe coloane din tabel, accesați fila Introduce-Grup Masa -Masă rotativă
- În caseta de dialog Creați tabelul pivot verificăm corectitudinea selecției intervalului de date (sau set sursă nouă date)
- indicați locația PivotTable:
- Foaie de lucru nouă
- Foaie de lucru existentă
- confirmați crearea apăsând un buton Bine
pentru că tabelele pivot, atunci când prelucrați datele care sunt plasate în zona rândurilor sau coloanelor, selectează numai valori unice din acestea pentru analiza ulterioară, atunci nu este absolut necesar de la noi decât pentru a crea un tabel pivot și a plasa datele coloana dorită în zona rândurilor sau coloanelor.
Folosind fișierul atașat articolului ca exemplu, eu:
Care este inconvenientul de a lucra cu tabelele pivot în acest caz: atunci când datele sursă se modifică, tabelul pivot va trebui să fie actualizat manual: Selectați orice celulă din tabelul pivot -Butonul din dreapta al mouse-ului - Reîmprospăta sau filă Date -Reîmprospătați-le pe toate -Reîmprospăta... Și dacă datele inițiale sunt completate dinamic și chiar mai rău, va fi necesar să specificați din nou intervalul datelor originale. Și încă un dezavantaj - datele din tabelul pivot nu pot fi modificate. Prin urmare, dacă va fi necesar să lucrați cu lista rezultată în viitor, atunci după creare lista dorită folosind rezumatul, acesta trebuie copiat și lipit pe foaia dorită.
Pentru a înțelege mai bine toți pașii și a învăța cum să utilizați tabelele pivot, vă recomandăm să citiți articolul Informații generale despre tabelele pivot - este atașat un tutorial video, în care demonstrez clar simplitatea și comoditatea lucrului cu elementele de bază. caracteristicile tabelelor pivot.
În exemplul atașat, pe lângă tehnicile descrise, se scrie o variație puțin mai complexă a extragerii elementelor unice printr-o formulă și cod, și anume: extragerea elementelor unice după criteriu... Despre ce vorbim: dacă într-o coloană a numelui de familie și în a doua (B) există unele date (în fișier sunt luni) și doriți să extrageți valorile unice ale coloanei B numai pentru numele de familie selectat. Exemple de astfel de extracții unice sunt situate pe foaie Extras după criteriu.
Exemplu de descărcare:
(108,0 KiB, 14.152 descărcări)
A ajutat articolul? Distribuie linkul prietenilor tăi! Lectii video
