Funcțiile de index și căutare din excel sunt cea mai bună alternativă pentru vpr. Funcții de indexare și searchpos în excel - cea mai bună alternativă pentru vpr Cum să cauți din partea stângă folosind searchpos și index

Să ne uităm la una dintre opțiunile utile oferite de Microsoft Excel. Apropo, puteți cumpăra o versiune licențiată a acestui program din magazinul nostru online cu reducere. Preturile si versiunile pot fi vizualizate.

Astăzi vom vorbi despre formatarea condiționată. Este destinat să evidențieze celulele tabelului care au caracteristici comune. Acesta ar putea fi același font, valori, fundal etc. Această operațiune prevede diverse configurații: severitatea verificării, conținutul potrivirilor, identitatea și variabilitatea acestora.

1. Să începem prin a lansa programul Microsoft Excel care conține tabelul de care avem nevoie. Apoi, selectăm gama de celule care necesită procesare. Vorbim despre comunitatea coloanelor și celulelor care fac parte dintr-un tabel sau mai multe zone neînrudite ale tabelului.
2. În continuare trebuie să parcurgem următoarea cale:

1. Programul are o gamă largă de capabilități, în special: puteți selecta evidențierea celulelor incluse în selecție, există o opțiune de selectare a fundalului de umplere (programul oferă 6 soluții de culoare), variații de fonturi și cadre de tabel. Puteți selecta „FORMAT PERSONALIZAT”, care vă permite să vă creați propria versiune a celulelor. Pentru a deselecta celulele care se potrivesc, faceți clic pe OK.

Folosind funcția EQUALS

Dacă celulele pe care trebuie să le selectați au o semnificație foarte specifică, utilizați elementul „EGAL” din lista „FORMATARE CONDIȚIONALĂ”, aflată în secțiunea „REGULI PENTRU SELECTAREA CELULLOR”. În caseta de dialog care se deschide, marcați celulele care vă interesează care necesită detectarea dublelor, iar adresa lor va apărea în caseta de dialog alăturată. După stăpânirea acestor abilități simple, puteți reduce semnificativ timpul petrecut cu procesarea datelor tabelare și gruparea valorilor comune.

Video: Găsirea potrivirilor în Excel

Acest tutorial explică principalele beneficii ale funcțiilor INDEXŞi CĂUTAREîn Excel, ceea ce le face mai atractive în comparație cu CĂUTARE V. Veți vedea câteva exemple de formule care vă vor ajuta să faceți față cu ușurință multor sarcini complexe cu care se confruntă funcția. CĂUTARE V neputincios.

În mai multe articole recente, am depus toate eforturile pentru a explica elementele de bază ale funcției utilizatorilor începători. CĂUTARE Vși arată exemple de formule mai complexe pentru utilizatorii avansați. Vom încerca acum, dacă nu vă descurajăm să utilizați CĂUTARE V, apoi cel puțin afișați modalități alternative de implementare a căutării verticale în Excel.

De ce avem nevoie de asta? – întrebi. Da, pentru că CĂUTARE V Căutarea nu este singura funcție de căutare din Excel și numeroasele sale limitări vă pot împiedica să obțineți rezultatele dorite în multe situații. Pe de altă parte, funcțiile INDEXŞi CĂUTARE– mai flexibile și au o serie de caracteristici care le fac mai atractive în comparație cu CĂUTARE V.

Informații de bază despre INDEX și MATCH

Deoarece scopul acestui tutorial este de a arăta capacitățile funcțiilor INDEXŞi CĂUTARE pentru a implementa căutarea verticală în Excel, nu ne vom opri asupra sintaxei și aplicației lor.

Să prezentăm aici minimul necesar pentru a înțelege esența, iar apoi vom examina în detaliu exemple de formule care arată avantajele utilizării INDEXŞi CĂUTAREîn loc de CĂUTARE V.

INDEX – sintaxa și utilizarea funcției

Funcţie INDEX(INDEX) în Excel returnează o valoare dintr-o matrice la numerele de rând și de coloană date. Funcția are această sintaxă:

Fiecare argument are o explicație foarte simplă:

• matrice(matrice) este intervalul de celule din care doriți să extrageți valoarea.
• row_num(număr_linie) este numărul liniei din matrice din care doriți să extrageți valoarea. Dacă nu este specificat, atunci este necesar un argument coloană_num(număr_coloană).
• coloană_num(număr_coloană) este numărul coloanei din tabloul din care doriți să extrageți valoarea. Dacă nu este specificat, atunci este necesar un argument row_num(număr_linie)

Dacă sunt specificate ambele argumente, atunci funcția INDEX returnează valoarea din celula de la intersecția rândului și coloanei specificate.

Aici cel mai simplu exemplu funcții INDEX(INDEX):

INDEX(A1:C10,2,3)
=INDEX(A1:C10,2,3)

Formula caută un interval A1:C10și returnează valoarea celulei în al 2-lea linie și al 3-lea coloană, adică dintr-o celulă C2.

Foarte simplu, nu? Cu toate acestea, în practică, nu știți întotdeauna de ce rând și coloană aveți nevoie și, prin urmare, aveți nevoie de ajutorul funcției CĂUTARE.

MATCH - sintaxa și utilizarea funcției

Funcţie MECI(MATCH) în Excel caută o valoare specificată într-un interval de celule și returnează poziția relativă a acelei valori în interval.

De exemplu, dacă se află în interval B1:B3 conține valorile New-York, Paris, Londra, apoi următoarea formulă va returna numărul 3 , deoarece „Londra” este al treilea element din listă.

MATCH(„Londra”, B1:B3,0)
=POTIRE(„Londra”;B1:B3;0)

Funcţie MECI(MATCH) are următoarea sintaxă:

MATCH(valoare_căutare,matrice_căutare,)
MATCH(valoare_căutare, matrice_căutare, [tip_potrivire])

• valoare_căutare(search_value) este numărul sau textul pe care îl căutați. Argumentul poate fi o valoare, inclusiv un boolean, sau o referință de celulă.
• matrice_căutare(search_array) – intervalul de celule în care are loc căutarea.
• tip_potrivire(map_type) – Acest argument spune funcția CĂUTARE, indiferent dacă doriți să găsiți o potrivire exactă sau aproximativă:
  • 1 sau nu este specificat– găsește valoarea maximă mai mică sau egală cu valoarea dorită. Matricea care este vizualizată trebuie ordonată în ordine crescătoare, adică de la cel mai mic la cel mai mare.
  • 0 – găsește prima valoare egală cu cea dorită. Pentru combinație INDEX/CĂUTARE aveți întotdeauna nevoie de o potrivire exactă, deci al treilea argument al funcției CĂUTARE trebuie să fie egal 0 .
  • -1 – găsește cea mai mică valoare mai mare sau egală cu valoarea de căutare. Matricea care este vizualizată trebuie sortată în ordine descrescătoare, adică de la cel mai mare la cel mai mic.

La prima vedere, beneficiul funcției CĂUTARE este îndoielnic. Cine trebuie să cunoască poziția unui element într-un interval? Vrem să știm semnificația acestui element!

Să vă reamintim că poziția relativă a valorii pe care o căutăm (adică numărul rândului și/sau al coloanei) este exact ceea ce trebuie să specificăm pentru argumente row_num(număr_linie) și/sau coloană_num(număr_coloană) funcții INDEX(INDEX). După cum vă amintiți, funcția INDEX poate returna valoarea la intersecția rândului și coloanei date, dar nu poate determina de ce rând și coloană ne interesează.

Cum se utilizează INDEX și MATCH în Excel

Acum că știi informatii de baza despre aceste două funcții, cred că deja devine clar cum funcționează CĂUTAREŞi INDEX pot lucra împreună. CĂUTARE determină poziția relativă a valorii de căutare într-un interval dat de celule și INDEX folosește acel număr (sau numere) și returnează rezultatul din celula corespunzătoare.

Încă nu este complet clar? Prezentați funcțiile INDEXŞi CĂUTAREîn această formă:

INDEX(,(POTIRE ( valoarea de căutare,coloana în care căutăm,0))
=INDEX( coloana din care extragem;(MECI( valoarea de căutare;coloana în care căutăm;0))

Cred că va fi și mai ușor de înțeles cu un exemplu. Să presupunem că aveți următoarea listă de capitale de stat:

Să găsim populația uneia dintre capitale, de exemplu, Japonia, folosind următoarea formulă:

INDEX($D$2:$D$10,MATCH(„Japonia”,$B$2:$B$10,0))
=INDEX($D$2:$D$10,MATCH(„Japonia”,$B$2:$B$10,0))

Acum să ne uităm la ce face fiecare element al acestei formule:

• Funcţie MECI(MATCH) caută valoarea „Japan” în coloană Bși în mod specific – în celule B2:B10, și returnează un număr 3 , deoarece „Japonia” este pe locul trei pe listă.
• Funcţie INDEX(INDEX) utilizări 3 pentru argumentare row_num(row_number), care specifică din ce rând trebuie returnată valoarea. Aceste. obținem o formulă simplă:

  INDEX($D$2:$D$10,3)
  =INDEX($D$2:$D$10,3)

  Formula spune cam așa: uitați-vă în celule de la D2 la D10și extrageți valoarea din al treilea rând, adică din celulă D4, deoarece numărarea începe de la a doua linie.

Acesta este rezultatul pe care îl obțineți în Excel:

Important! Numărul de rânduri și coloane din matrice pe care funcția le folosește INDEX(INDEX), trebuie să se potrivească cu valorile argumentului row_num(număr_linie) și coloană_num(număr_coloană) funcții MECI(MECI). În caz contrar, rezultatul formulei va fi eronat.

Stai, stai... de ce nu putem folosi o funcție CĂUTARE V(VPR)? Are rost să pierzi timpul încercând să descoperi labirinturi? CĂUTAREŞi INDEX?

CĂUTARE V ("Japonia", $B$2:$D$2,3)
=CĂUTAREV(„Japonia”, $B$2:$D$2,3)

În acest caz, nu are rost! Scopul acestui exemplu este doar pentru scopuri demonstrative, astfel încât să puteți înțelege cum funcționează CĂUTAREŞi INDEX lucra in perechi. Următoarele exemple vă vor arăta adevărata putere a legăturii. INDEXŞi CĂUTARE, care face față cu ușurință multor situații dificile când CĂUTARE V se găsește într-o fundătură.

De ce este INDEX/MATCH mai bun decât VLOOKUP?

Atunci când decid ce formulă să folosească pentru o căutare verticală, cei mai mulți guru din Excel cred asta INDEX/CĂUTARE mult mai bine decât CĂUTARE V. Cu toate acestea, mulți utilizatori Excel recurg în continuare la utilizare CĂUTARE V, pentru că această funcție este mult mai simplă. Acest lucru se întâmplă deoarece foarte puțini oameni înțeleg pe deplin toate beneficiile trecerii de la CĂUTARE V pe ciorchine INDEXŞi CĂUTARE, și nimeni nu vrea să piardă timpul studiind o formulă mai complexă.

4 Beneficii principale ale utilizării MATCH/INDEX în Excel:

1. Căutați de la dreapta la stânga. După cum știe orice utilizator Excel competent, CĂUTARE V nu poate privi spre stânga, ceea ce înseamnă că valoarea căutată trebuie să fie neapărat în coloana din stânga a intervalului examinat. În cazul în care CĂUTARE/INDEX, coloana de căutare poate fi fie în partea stângă, fie în partea dreaptă a intervalului de căutare. Exemplu: va afișa această caracteristică în acțiune.

2. Adăugați sau eliminați coloane în siguranță. Formule cu funcție CĂUTARE V nu mai funcționează sau returnați valori eronate dacă eliminați sau adăugați o coloană la un tabel de căutare. Pentru funcție CĂUTARE V orice coloană inserată sau eliminată va schimba rezultatul formulei deoarece sintaxa CĂUTARE V vă solicită să specificați întregul interval și numărul de coloană specific din care doriți să extrageți datele.

De exemplu, dacă ai o masă A1:C10, și doriți să preluați date dintr-o coloană B, atunci trebuie să setați valoarea 2 pentru argumentare col_index_num(număr_coloană) funcții CĂUTARE V, ca aceasta:

CĂUTARE V ("valoare de căutare", A1:C10,2)
=CĂUTAREV(„valoare de căutare”;A1:C10;2)

Dacă mai târziu introduceți o nouă coloană între coloane OŞi B, atunci valoarea argumentului va trebui modificată de la 2 pe 3 , altfel formula va returna rezultatul din coloana nou introdusă.

Folosind CĂUTARE/INDEX Puteți elimina sau adăuga coloane în intervalul studiat fără a distorsiona rezultatul, deoarece coloana care conține valoarea dorită este definită direct. Într-adevăr, acesta este un mare avantaj, mai ales atunci când trebuie să lucrați cu cantități mari de date. Puteți adăuga și elimina coloane fără să vă faceți griji că trebuie să remediați fiecare funcție pe care o utilizați CĂUTARE V.

3. Nu există limită pentru dimensiunea valorii căutate. Folosind CĂUTARE V, rețineți că lungimea valorii căutate este limitată la 255 de caractere, altfel riscați să obțineți o eroare #VALOARE!(#VALOARE!). Deci, dacă tabelul conține rânduri lungi, singura soluție viabilă este utilizarea INDEX/CĂUTARE.

Să presupunem că folosești această formulă cu CĂUTARE V, care caută în celule din B5 la D10 valoarea specificată în celulă A2:

CĂUTARE V(A2;B5:D10;3;FALSE)
=CĂUTAREV(A2;B5:D10;3;FALSE)

Formula nu va funcționa dacă valoarea din celulă este A2 mai mult de 255 de caractere. În schimb, trebuie să utilizați o formulă similară INDEX/CĂUTARE:

INDEX(D5:D10,POTRIVIRE(ADEVĂRAT,INDEX(B5:B10=A2,0),0))
=INDEX(D5:D10,POTRIVIRE(ADEVĂRAT,INDEX(B5:B10=A2,0),0))

4. Viteză de operare mai mare. Dacă lucrați cu tabele mici, atunci diferența de performanță Excel cel mai probabil nu va fi vizibilă, mai ales în cele mai recente versiuni. Dacă lucrați cu tabele mari care conțin mii de rânduri și sute de formule de căutare, Excel va funcționa mult mai rapid dacă utilizați CĂUTAREŞi INDEXîn loc de CĂUTARE V. În general, această înlocuire crește viteza Excel cu 13% .

Influenţa CĂUTARE V Performanța Excel este vizibilă mai ales dacă registrul de lucru conține sute de formule matrice complexe, cum ar fi CĂUTARE V+SUMA. Faptul este că verificarea fiecărei valori din matrice necesită un apel separat de funcție CĂUTARE V. Prin urmare, cu cât o matrice conține mai multe valori și cu cât mai multe formule matrice conține tabelul, cu atât Excel funcționează mai lent.

Pe de altă parte, o formulă cu funcții CĂUTAREŞi INDEX pur și simplu efectuează o căutare și returnează rezultatul, efectuând o muncă similară considerabil mai rapid.

INDEX și MATCH - exemple de formule

Acum că înțelegi motivele pentru care ar trebui să înveți funcțiile CĂUTAREŞi INDEX, să trecem la partea distractivă și să vedem cum poți aplica cunoștințele teoretice în practică.

Cum să căutați din partea stângă folosind MATCH și INDEX

Orice manual pe CĂUTARE V Se spune că această funcție nu poate privi spre stânga. Aceste. dacă coloana la care te uiți nu este cea din stânga din intervalul de căutare, atunci nu există nicio șansă de a ajunge de la CĂUTARE V rezultatul dorit.

Funcții CĂUTAREŞi INDEX Excel este mult mai flexibil și nu-i pasă unde se află coloana cu valoarea pe care doriți să o preluați. De exemplu, să revenim la tabelul cu capitalele de stat și populația. De data aceasta vom scrie formula CĂUTARE/INDEX, care va arăta ce loc ocupă capitala Rusiei (Moscova) ca populație.

După cum puteți vedea în figura de mai jos, formula face această treabă perfect:

INDEX($A$2:$A$10,MATCH(„Rusia”,$B$2:$B$10,0))

Acum nu ar trebui să aveți nicio problemă în a înțelege cum funcționează această formulă:

• Mai întâi, să folosim funcția MECI(MECI), care găsește poziția „Rusia” în listă:

  MATCH(„Rusia”, $B$2:$B$10,0))
  =POTIRE(„Rusia”, $B$2:$B$10,0))

• Apoi, setați intervalul pentru funcție INDEX(INDEX) din care se extrage valoarea. În cazul nostru este A2:A10.
• Apoi combinăm ambele părți și obținem formula:

  INDEX($A$2:$A$10;POITIRE(„Rusia”;$B$2:$B$10,0))
  =INDEX($A$2:$A$10,MATCH(„Rusia”,$B$2:$B$10,0))

Cheie: Decizia corectă va folosi întotdeauna referințe absolute pentru INDEXŞi CĂUTARE, astfel încât intervalele de căutare să nu se piardă atunci când copiați formula în alte celule.

Calcule folosind INDEX și MATCH în Excel (AVERAGE, MAX, MIN)

Puteți imbrica alte funcții Excel în interior INDEXŞi CĂUTARE, de exemplu, pentru a găsi valoarea minimă, maximă sau cea mai apropiată de valoarea medie. Iată câteva opțiuni pentru formule în raport cu tabelul de la:

1. MAX(MAX). Formula găsește maximul într-o coloană D C aceeasi linie:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,POITIRE(MAX($D$2:I$10),$D$2:D$10,0))

Rezultat: Beijing

2. MIN(MIN). Formula găsește minimul într-o coloană Dși returnează valoarea din coloană C aceeasi linie:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,POITIRE(MIN($D$2:I$10),$D$2:D$10,0))

Rezultat: Lima

3. MEDIE(MEDIE). Formula calculează media unui interval D2:D10, apoi îl găsește pe cel mai apropiat de acesta și returnează valoarea din coloană C aceeasi linie:

INDEX($C$2:$C$10,PORITARE(MEDIE($D$2:D$10),$D$2:D$10,1))
=INDEX($C$2:$C$10,PORITARE(MEDIE($D$2:D$10),$D$2:D$10,1))

Rezultat: Moscova

Lucruri de reținut când utilizați funcția MEDIE cu INDEX și MATCH

Folosind funcția MEDIE in combinatie cu INDEXŞi CĂUTARE, ca al treilea argument al funcției CĂUTARE cel mai adesea va trebui să indicați 1 sau -1 în cazul în care nu sunteți sigur că intervalul pe care îl vizualizați conține o valoare egală cu media. Dacă ești sigur că există o astfel de valoare, pune 0 pentru a găsi o potrivire exactă.

• Daca indicati 1 , valorile din coloana de căutare ar trebui să fie ordonate în ordine crescătoare, iar formula va returna valoarea maximă mai mică sau egală cu media.
• Daca indicati -1 , valorile din coloana de căutare ar trebui ordonate în ordine descrescătoare și va fi returnată valoarea minimă mai mare sau egală cu media.

În exemplul nostru, valorile din coloană D sunt ordonate în ordine crescătoare, deci folosim tipul de colare 1 . Formula INDEX/SEARCHPOZ returnează „Moscova”, deoarece populația orașului Moscova este cea mai apropiată de valoarea medie (12.269.006).

Cum să utilizați INDEX și MATCH pentru a căuta un rând și o coloană cunoscute

Această formulă este echivalentă cu o căutare bidimensională CĂUTARE Vși vă permite să găsiți valoarea la intersecția unui anumit rând și coloană.

În acest exemplu formula INDEX/CĂUTARE va fi foarte asemănătoare cu formulele pe care le-am discutat deja în această lecție, cu o singură diferență. Ghici care?

După cum vă amintiți, sintaxa funcției INDEX(INDEX) permite trei argumente:

INDEX(matrice,num_rând,)
INDEX(matrice, număr_rând, [număr_coloană])

Și îi felicit pe cei care ați ghicit!

Să începem prin a scrie șablonul de formulă. Pentru a face acest lucru, să luăm formula care ne este deja familiară INDEX/CĂUTAREși adăugați o altă funcție CĂUTARE, care va returna numărul coloanei.

INDEX(Tabelul dvs. ,(POTIRE(, coloană pentru a căuta,0)),(POTIRE(, linie pentru a căuta,0))
=INDEX(Tabelul dvs. ,(POTIT( valoare pentru căutare verticală,coloană pentru a căuta,0)),(POTRIVIT( valoare pentru căutare orizontală,linie pentru a căuta,0))

Rețineți că pentru căutarea bidimensională trebuie să specificați întregul tabel în argument matrice(matrice) funcții INDEX(INDEX).

Acum să încercăm acest model în practică. Mai jos vedeți o listă cu cele mai populate țări din lume. Să presupunem că sarcina noastră este să aflăm populația Statelor Unite în 2015.

Bine, hai să scriem formula. Când trebuie să creez o formulă complexă în Excel cu funcții imbricate, mai întâi notez fiecare funcție imbricată separat.

Deci, să începem cu două funcții CĂUTARE, care va returna numerele rândurilor și coloanelor pentru funcție INDEX:

• MATCH pentru coloană– căutăm în coloană B, sau mai degrabă în gamă B2:B11, valoarea care este specificată în celulă H2(STATELE UNITE ALE AMERICII). Funcția va arăta astfel:

  MECI ($H$2,$B$1:$B$11,0)
  =POTRIV ($H$2,$B$1:$B$11,0)

  4 , deoarece „SUA” este al 4-lea element din listă din coloană B(inclusiv titlul).

• MATCH pentru șir– căutăm valoarea celulei H3(2015) în linie 1 , adică în celule A1:E1:

  MATCH($H$3,$A$1:$E$1,0)
  =PORITARE($H$3,$A$1:$E$1,0)

  Rezultatul acestei formule va fi 5 , deoarece „2015” este în coloana a 5-a.

Acum inserăm aceste formule în funcție INDEX si voila:

INDEX($A$1:$E$11,POTRIBUIRE($H$2,$B$1:$B$11,0),POTIRE($H$3,$A$1:$E$1,0))
=INDEX($A$1:$E$11,POTRIBUIRE($H$2,$B$1:$B$11,0),POTIRE($H$3,$A$1:$E$1,0))

Dacă înlocuiți funcțiile CĂUTARE pe baza valorilor pe care le returnează, formula va deveni ușoară și de înțeles:

INDEX($A$1:$E$11,4,5))
=INDEX($A$1:$E$11,4,5))

Această formulă returnează valoarea la intersecție al 4-lea linii şi al 5-lea coloană în interval A1:E11, adică valoarea celulei E4. Doar? Da!

Căutare cu mai multe criterii cu INDEX și MATCH

În tutorialul de pe CĂUTARE V am arătat un exemplu de formulă cu o funcție CĂUTARE V pentru a căuta folosind mai multe criterii. Cu toate acestea, o limitare semnificativă a acestei soluții a fost necesitatea de a adăuga o coloană auxiliară. Vești bune: formula INDEX/CĂUTARE poate căuta în valori în două coloane, fără a fi nevoie să creați o coloană de ajutor!

Să presupunem că avem o listă de comenzi și dorim să găsim suma pe baza a două criterii − numele cumpărătorului(Client) și produs(Produs). Problema este complicată de faptul că un cumpărător poate cumpăra mai multe produse diferite simultan și numele cumpărătorilor din tabelul de pe foaie Tabel de căutare aranjate în ordine aleatorie.

Iată formula INDEX/CĂUTARE rezolva problema:

(=INDEX ("Tabel de căutare"!$A$2:$C$13,MATCH(1,(A2="Tabel de căutare"!$A$2:$A$13))*
(B2="Tabel de căutare"!$B$2:$B$13),0),3))
(=INDEX(„Tabel de căutare”!$A$2:$C$13;POTIRE(1,(A2="Tabel de căutare"!$A$2:$A$13))*
(B2="Tabel de căutare"!$B$2:$B$13);0);3))

Această formulă este mai complexă decât altele despre care am discutat mai devreme, dar înarmată cu cunoștințe despre funcții INDEXŞi CĂUTARE O vei învinge. Cea mai grea parte este funcția CĂUTARE, cred că trebuie explicat mai întâi.

MATCH(1,(A2="Tabel de căutare"!$A$2:$A$13),0)*(B2="Tabel de căutare"!$B$2:$B$13)
MATCH(1;(A2="Tabel de căutare"!$A$2:$A$13);0)*(B2="Tabel de căutare"!$B$2:$B$13)

În formula prezentată mai sus, valoarea pe care o căutăm este 1 , iar tabloul de căutare este rezultatul înmulțirii. Bine, ce ar trebui să înmulțim și de ce? Să privim totul în ordine:

• Luați prima valoare din coloană O(Client) pe foaie Masa principalăși comparați-l cu toate numele clienților din tabelul de pe foaie Tabel de căutare(A2:A13).
• Dacă se găsește o potrivire, ecuația revine 1 (ADEVĂRAT), iar dacă nu - 0 (MINCIUNĂ).
• Apoi, facem același lucru pentru valorile coloanei B(Produs).
• Apoi înmulțim rezultatele obținute (1 și 0). Numai dacă se găsesc potriviri în ambele coloane (adică ambele criterii sunt adevărate), veți primi 1 . Dacă ambele criterii sunt false, sau numai unul dintre ele este îndeplinit, vei primi 0 .

Acum înțelegeți de ce am întrebat 1 , care este valoarea dorită? Este corect ca functia CĂUTARE a returnat postul numai atunci când ambele criterii au fost îndeplinite.

Vă rugăm să rețineți:În acest caz, trebuie să utilizați al treilea argument opțional al funcției INDEX. Este necesar pentru că în primul argument specificăm întregul tabel și trebuie să spunem funcției din ce coloană să preia valoarea. În cazul nostru, aceasta este coloana C(Suma), și așa am intrat 3 .

Și în sfârșit, pentru că trebuie să verificăm fiecare celulă din matrice, această formulă trebuie să fie o formulă matrice. Puteți vedea acest lucru după bretele în care este închis. Deci, când ați terminat de introdus formula, nu uitați să faceți clic Ctrl+Shift+Enter.

Dacă totul este făcut corect, veți obține rezultatul ca în figura de mai jos:

INDEX și MATCH combinate cu IFERROR în Excel

După cum probabil ați observat deja (de mai multe ori), dacă introduceți o valoare incorectă, de exemplu, una care nu se află în matricea care este vizualizată, formula INDEX/CĂUTARE raportează o eroare #N / A(#N/A) sau #VALOARE!(#VALOARE!). Dacă doriți să înlocuiți un astfel de mesaj cu ceva mai ușor de înțeles, puteți introduce o formulă cu INDEXŞi CĂUTAREîntr-o funcție DACA EROARE.

Sintaxa funcției DACA EROARE foarte simplu:

IFEROARE(valoare,valoare_dacă_eroare)
IFEROARE(valoare,valoare_dacă_eroare)

Unde este argumentul valoare(valoare) este valoarea care este verificată pentru o eroare (în cazul nostru, rezultatul formulei INDEX/CĂUTARE); si argumentul valoare_dacă_eroare(value_if_error) este valoarea care trebuie returnată dacă formula aruncă o eroare.

De exemplu, puteți introduce într-o funcție DACA EROARE ca aceasta:

DACĂ EROARE(INDEX($A$1:$E$11,POTRIVIRE($G$2,$B$1:$B$11,0),POTIRE($G$3,$A$1:$E$1,0))),
„Nu s-au găsit potriviri. Încercați din nou!”) =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1 ): $E$1;0));
„Nu s-au găsit potriviri. Încercați din nou!”)

Și acum, dacă cineva introduce o valoare incorectă, formula va produce acest rezultat:

Dacă preferați să lăsați celula goală în cazul unei erori, puteți utiliza ghilimele (“”) ca valoare a celui de-al doilea argument al funcției DACA EROARE. Ca aceasta:

IFERROR(INDEX(matrice, MATCH(valoare_căutare,matrice_căutare,0),"")
IFERROR(INDEX(matrice, MATCH(valoare_căutare,matrice_caută,0),"")

Sper că ați găsit utilă cel puțin o formulă descrisă în acest manual. Dacă ați întâmpinat alte probleme de căutare pentru care nu ați putut găsi o soluție potrivită printre informațiile din această lecție, nu ezitați să vă descrieți problema în comentarii și vom încerca cu toții să o rezolvăm împreună.

Pentru a combate o explozie combinatorie, este necesară o „rangă combinatorie”. Există două instrumente care vă permit să rezolvați practic probleme combinatorii complexe. Prima este paralelizarea masivă a calculelor. Și aici este important nu numai să ai număr mare procesoare paralele, dar și să selectați un algoritm care vă permite să paralelizați sarcina și să încărcați toată puterea de calcul disponibilă.

Al doilea instrument este principiul limitării. Metoda principală care utilizează principiul mărginirii este metoda „subspațiilor aleatoare”. Uneori, problemele combinatorii permit restricții puternice asupra condițiilor inițiale și, în același timp, păstrează speranța că și după aceste restricții, în date vor rămâne suficiente informații pentru a putea fi găsită soluția necesară. Pot exista multe opțiuni pentru limitarea condițiilor inițiale. Nu toate pot avea succes. Dar dacă, totuși, există posibilitatea că există opțiuni de succes pentru restricții, atunci o problemă complexă poate fi împărțită într-un număr mare de probleme limitate, fiecare dintre acestea putând fi rezolvată mult mai simplu decât cea originală.

Combinând aceste două principii, putem construi o soluție la problema noastră.

Spațiu combinatoriu

Să luăm vectorul de biți de intrare și să îi numărăm biții. Să creăm „puncte” combinatorii. Să combinăm mai mulți biți aleatori ai vectorului de intrare în fiecare punct (figura de mai jos). Observând intrarea, fiecare dintre aceste puncte nu va vedea întreaga imagine, ci doar o mică parte a acesteia, determinată de ce biți au convergit în punctul selectat. Deci, în figura de mai jos, punctul din stânga cu indicele 0 monitorizează doar biții 1, 6, 10 și 21 ai semnalului de intrare original. Să creăm destul de multe astfel de puncte și să numim setul lor spațiu combinatoriu.

Spațiu combinatoriu

Care este sensul acestui spațiu? Presupunem că semnalul de intrare nu este aleatoriu, ci conține anumite modele. Modelele pot fi de două tipuri principale. Ceva din descrierea de intrare poate apărea puțin mai des decât altele. De exemplu, în cazul nostru, literele individuale apar mai des decât combinațiile lor. În codificarea biților, aceasta înseamnă că anumite combinații de biți apar mai des decât altele.

Un alt tip de model este atunci când, pe lângă semnalul de intrare, există un semnal de învățare însoțitor și ceva conținut în semnalul de intrare se dovedește a fi asociat cu ceva conținut în semnalul de învățare. În cazul nostru, biții de ieșire activi sunt un răspuns la o combinație de anumiți biți de intrare.

Dacă căutați modele „direct”, adică uitați-vă la întregul vector de intrare și de ieșire, atunci nu este foarte clar ce să faceți și unde să vă mutați. Dacă începeți să construiți ipoteze despre ce poate depinde de ce, atunci are loc imediat o explozie combinatorie. Numărul de ipoteze posibile se dovedește a fi monstruos.

O metodă clasică utilizată pe scară largă în rețelele neuronale este coborârea în gradient. Este important pentru el să înțeleagă în ce direcție să se miște. Acest lucru nu este de obicei dificil atunci când există un singur obiectiv de ieșire. De exemplu, dacă dorim să antrenăm o rețea neuronală să scrie numere, îi arătăm imagini cu numere și indicăm ce fel de număr vede. Rețeaua înțelege „cum și unde să coboare”. Dacă arătăm imagini cu mai multe numere deodată și numim toate aceste numere în același timp, fără a indica unde se află ceva, atunci situația devine mult mai complicată.

Atunci când sunt create puncte ale unui spațiu combinatoriu cu o „vedere” foarte limitată (subspații aleatoare), se dovedește că unele puncte pot fi norocoase și pot vedea modelul, dacă nu complet pur, atunci cel puțin într-o formă semnificativ purificată. O astfel de vedere limitată va permite, de exemplu, efectuarea unei coborâri în gradient și obținerea unui model pur. Probabilitatea ca un punct individual să se împiedice de un model poate să nu fie foarte mare, dar puteți selecta oricând un astfel de număr de puncte pentru a garanta că orice model va „apari undeva”.

Desigur, dacă dimensiunea punctelor este prea îngustă, adică numărul de biți din puncte este ales aproximativ egal cu câți biți sunt așteptați în model, atunci dimensiunea spațiului combinatoriu va începe să tinde să numărul de opțiuni pentru o căutare completă a posibilelor ipoteze, care ne întoarce la explozia combinatorie. Dar, din fericire, poți crește vizibilitatea punctelor, reducându-le numărul total. Această reducere nu este dată gratuit, combinatoria este „transferată în puncte”, dar până la un anumit punct nu este fatală.

Să creăm un vector de ieșire. Pur și simplu reducem câteva puncte ale spațiului combinatoriu la fiecare bit de ieșire. Vom alege care puncte vor fi acestea la întâmplare. Numărul de puncte care se încadrează într-un bit va corespunde de câte ori dorim să reducem spațiul combinatoriu. Un astfel de vector de ieșire va fi o funcție hash pentru vectorul de stare al spațiului combinatoriu. Vom vorbi despre modul în care această afecțiune este luată în considerare puțin mai târziu.

În general, de exemplu, așa cum se arată în figura de mai sus, dimensiunea intrării și ieșirii poate fi diferită. În exemplul nostru cu codificarea șirurilor, aceste dimensiuni sunt aceleași.

Grupuri de receptori

Cum să cauți modele în spațiul combinatoriu? Fiecare punct își vede propriul fragment al vectorului de intrare. Dacă ceea ce vede ea se dovedește a avea destul de multe părți active, atunci putem presupune că ceea ce vede ea este un fel de model. Adică, un set de biți activi care atinge punctul poate fi numit o ipoteză despre prezența unui model. Să ne amintim această ipoteză, adică fixăm setul de biți activi vizibili într-un punct. În situația prezentată în figura de mai jos, este clar că la punctul 0 trebuie fixați biții 1, 6 și 21.

Fixarea biților într-un cluster

Vom numi înregistrarea numărului unui bit un receptor pentru acest bit. Aceasta implică faptul că receptorul monitorizează starea bitului corespunzător al vectorului de intrare și reacționează atunci când unul apare acolo.

Vom numi un set de receptori un cluster receptor sau un cluster receptiv. Când este prezentat un vector de intrare, receptorii clusterului răspund dacă pozițiile corespunzătoare ale vectorului le conțin. Pentru un cluster, puteți număra numărul de receptori declanșați.

Deoarece informațiile noastre sunt codificate nu de biți individuali, ci de un cod, acuratețea cu care formulăm o ipoteză depinde de câți biți luăm în cluster. La articol este atașat textul unui program care rezolvă problema conversiei șirurilor. În mod implicit, programul are următoarele setări:

• lungime vector de intrare - 256 biți;
• lungime vector de ieșire – 256 biți;
• o singură literă este codificată cu 8 biți;
• lungimea liniei - 5 caractere;
• număr de contexte offset - 10;
• dimensiunea spațiului combinatoriu – 60000;
• numărul de biți care se intersectează într-un punct – 32;
• pragul de creare a clusterului – 6;
• pragul pentru activarea parțială a clusterului este 4.

Cu astfel de setări, aproape fiecare bit care se află în codul unei litere este repetat în codul altei litere, sau chiar în codurile mai multor litere. Prin urmare, un singur receptor nu poate indica în mod fiabil un model. Doi receptori indică o literă mult mai bine, dar pot indica și o combinație de litere complet diferite. Putem introduce un anumit prag de lungime, pornind de la care putem judeca în mod fiabil dacă fragmentul de cod de care avem nevoie se află în cluster.

Să introducem un prag minim pentru numărul de receptori necesar formării unei ipoteze (în exemplu este 6). Să începem să învățăm. Vom furniza codul sursă și codul pe care dorim să-l obținem ca rezultat. Pentru codul sursă, este ușor de calculat câți biți activi cad în fiecare dintre punctele din spațiul combinatoriu. Vom selecta doar acele puncte care sunt conectate la biții activi ai codului de ieșire și pentru care numărul de biți activi ai codului de intrare inclus în acesta nu va fi mai mic decât pragul pentru crearea unui cluster. În astfel de puncte vom crea grupuri de receptori cu seturi corespunzătoare de biți. Să salvăm aceste clustere exact în punctele în care au fost create. Pentru a nu crea duplicate, verificăm mai întâi dacă aceste clustere sunt unice pentru aceste puncte și că punctele nu conțin încă exact aceleași clustere.

Să spunem același lucru cu alte cuvinte. Din vectorul de ieșire știm ce biți ar trebui să fie activi. În consecință, putem selecta puncte în spațiul combinatoriu asociat acestora. Pentru fiecare astfel de punct, putem formula o ipoteză că ceea ce vede acum pe vectorul de intrare este modelul care este responsabil pentru activitatea bitului la care este conectat acest punct. Nu putem spune dintr-un singur exemplu dacă această ipoteză este adevărată sau nu, dar nimeni nu ne împiedică să facem o presupunere.

Educaţie. Consolidarea memoriei

În timpul procesului de învățare, fiecare exemplu nou creează un număr mare de ipoteze, dintre care majoritatea sunt incorecte. Ni se cere să testăm toate aceste ipoteze și să le eliminăm pe cele false. Putem face acest lucru observând dacă aceste ipoteze sunt confirmate în exemplele ulterioare. În plus, atunci când creăm un cluster nou, ne amintim toți biții pe care îi vede punctul, iar acesta, chiar dacă conține un model, și biți aleatori care au ajuns acolo din alte concepte care nu ne afectează rezultatul și care în cazul nostru sunt zgomot. În consecință, este necesar nu numai să se confirme sau să infirme că combinația memorată de biți conține modelul dorit, ci și să se ștergă această combinație de zgomot, lăsând doar o regulă „pură”.

Există abordări diferite pentru rezolvarea problemei. Voi descrie una dintre ele fără a pretinde că este cea mai bună. Am trecut prin multe variante, aceasta m-a captivat prin calitatea muncii și simplitatea ei, dar asta nu înseamnă că nu poate fi îmbunătățită.

Este convenabil să ne gândim la clustere ca la computere autonome. Dacă fiecare cluster își poate testa propria ipoteză și poate lua decizii independent de celelalte, atunci acest lucru este foarte bun pentru potențiala paralelizare a calculelor. Fiecare grup de receptori, după creare, începe o viață independentă. El monitorizează semnalele primite, acumulează experiență, se schimbă și, dacă este necesar, ia o decizie privind autodistrugerea.

Un cluster este un set de biți despre care am presupus că există un model în interiorul său asociat cu funcționarea bitului de ieșire la care este conectat punctul care conține acest cluster. Dacă există un model, atunci cel mai probabil acesta afectează doar o parte din biți și nu știm dinainte care dintre ele. Prin urmare, vom înregistra toate momentele în care un număr semnificativ de receptori sunt activați în cluster (în exemplu, cel puțin 4). Este posibil ca în aceste momente tiparul, dacă există unul, să se manifeste. Când anumite statistici se acumulează, putem încerca să stabilim dacă există ceva natural în astfel de activări parțiale ale clusterelor sau nu.

Un exemplu de statistici este prezentat în figura de mai jos. Plusul de la începutul liniei indică faptul că în momentul în care cluster-ul a fost declanșat parțial, bitul de ieșire era și el activ. Biții cluster sunt formați din biții corespunzători ai vectorului de intrare.

Cronica activării parțiale a unui grup de receptori

Ce ar trebui să ne intereseze la aceste statistici? Ne pasă de ce biți lucrează împreună mai des decât altele. Nu confundați acest lucru cu cele mai comune biți. Dacă calculăm frecvența apariției sale pentru fiecare bit și luăm cei mai obișnuiți biți, atunci aceasta va fi o medie, care nu este deloc ceea ce avem nevoie. Dacă mai multe modele stabile converg într-un punct, atunci când se face media, se va obține „neregularitatea” medie dintre ele. În exemplul nostru, este clar că liniile 1, 2 și 4 sunt similare între ele, iar liniile 3, 4 și 6 sunt, de asemenea, similare. Trebuie să selectăm unul dintre aceste modele, de preferință cel mai puternic, și să-l ștergem de biți inutile.

Cea mai comună combinație care apare ca anumiți biți care se declanșează împreună este pentru această statistică. Pentru a calcula componenta principală, puteți utiliza filtrul Hebb. Pentru a face acest lucru, puteți specifica un vector cu greutăți inițiale unitare. Apoi obțineți activitatea clusterului prin înmulțirea vectorului de greutăți cu starea curentă a clusterului. Și apoi mutați greutățile în lateral starea actuală cu cât este mai puternică cu atât este mai mare această activitate. Pentru a preveni creșterea necontrolată a greutăților, după modificarea greutăților acestea trebuie normalizate, de exemplu, la valoarea maximă din vectorul greutăților.

Această procedură se repetă pentru toate exemplele disponibile. Ca urmare, vectorul greutăților se apropie din ce în ce mai mult de componenta principală. Dacă exemplele existente nu sunt suficiente pentru a converge, atunci puteți repeta procesul de mai multe ori folosind aceleași exemple, reducând treptat viteza de învățare.

Ideea principală este că, pe măsură ce se apropie de componenta principală, clusterul începe să reacționeze din ce în ce mai puternic la eșantioane similare cu ea și din ce în ce mai puțin la altele, datorită acestui fapt, învățarea în direcția corectă merge mai repede decât încearcă exemplele „rele”. să-l strice. Rezultatul unui astfel de algoritm după mai multe iterații este prezentat mai jos.

Rezultatul obţinut după mai multe iteraţii de izolare a primei componente principale

Dacă acum tăiem clusterul, adică lăsăm doar acei receptori cu greutăți mari (de exemplu, peste 0,75), atunci vom obține un model șters de biții de zgomot inutile. Această procedură poate fi repetată de mai multe ori pe măsură ce statisticile se acumulează. Ca rezultat, putem înțelege dacă există vreun model în cluster sau dacă am colectat un set aleatoriu de biți împreună. Dacă nu există niciun model, atunci tăierea clusterului va lăsa un fragment prea scurt. În acest caz, un astfel de cluster poate fi eliminat ca o ipoteză eșuată.

Pe lângă tăierea grupului, trebuie să vă asigurați că este prins exact modelul dorit. Șirul sursă conține coduri din mai multe litere, fiecare dintre ele fiind un model. Oricare dintre aceste coduri poate fi „prins” de cluster. Dar ne interesează doar codul literei care afectează formarea bitului de ieșire. Din acest motiv, majoritatea ipotezelor vor fi false și trebuie respinse. Acest lucru se poate face pe baza criteriilor că activarea parțială sau chiar completă a clusterului nu va coincide prea des cu activitatea bitului de ieșire dorit. Astfel de grupuri trebuie șterse. Procesul unui astfel de control și eliminare a clusterelor inutile împreună cu „decuparea” acestora poate fi numit consolidare a memoriei.

Procesul de acumulare de noi clustere este destul de rapid, fiecare nouă experiență generează câteva mii de noi clustere de ipoteze. Este recomandabil să efectuați antrenamentul în etape cu o pauză pentru „somn”. Când se creează un număr critic de clustere, este necesar să treceți la modul de funcționare „inactiv”. În acest mod, experiențele amintite anterior sunt derulate. Dar, în același timp, nu sunt create ipoteze noi, ci sunt testate doar cele vechi. Ca urmare a „somnului”, este posibil să eliminați un procent uriaș de ipoteze false și să lăsați doar ipotezele care au trecut testul. După „somn”, spațiul combinatoriu nu este doar curățat și gata să primească informații noi, dar este și mult mai încrezător în ceea ce a fost învățat „ieri”.

Ieșire spațială combinatorie

Pe măsură ce clusterele acumulează statistici și sunt supuse consolidării, vor apărea clustere care sunt suficient de asemănătoare încât ipoteza lor este fie adevărată, fie aproape de adevărată. Vom lua astfel de clustere și vom monitoriza când sunt pe deplin activate, adică când toți receptorii clusterului sunt activi.

În continuare, vom forma rezultatul din această activitate ca un hash al spațiului combinatoriu. În același timp, vom ține cont de faptul că, cu cât clusterul este mai lung, cu atât este mai mare șansa să fi prins tiparul. Pentru clusterele scurte, există posibilitatea ca combinația de biți să apară întâmplător ca o combinație a altor concepte. Pentru a crește imunitatea la zgomot, vom folosi ideea de amplificare, adică vom cere ca pentru clusterele scurte activarea bitului de ieșire să aibă loc numai atunci când există mai multe astfel de operațiuni. În cazul clusterelor lungi, vom presupune că o singură operație este suficientă. Acest lucru poate fi reprezentat prin potențialul care apare atunci când clusterele sunt declanșate. Acest potențial este mai mare cu cât clusterul este mai lung. Se adaugă potențialele punctelor conectate la un bit de ieșire. Dacă potențialul rezultat depășește un anumit prag, bitul este activat.

După ceva antrenament, rezultatul începe să reproducă o parte care se potrivește cu ceea ce dorim să obținem (imaginea de mai jos).

Un exemplu despre cum funcționează un spațiu combinatoriu în timpul procesului de învățare (aproximativ 200 de pași). Mai sus este codul sursă, în mijloc este codul necesar, mai jos este codul prezis de spațiul combinatoriu.

Treptat, ieșirea spațiului combinatoriu începe să reproducă mai bine codul de ieșire necesar. După câteva mii de pași de antrenament, rezultatul este reprodus cu o precizie destul de mare (figura de mai jos).

Un exemplu despre cum funcționează un spațiu combinatoriu antrenat. Deasupra este codul sursă, în mijloc este codul necesar, mai jos este codul prezis de spațiul combinatoriu.

Pentru a vizualiza cum funcționează totul, am înregistrat un videoclip cu procesul de învățare. În plus, poate explicațiile mele vă vor ajuta să înțelegeți mai bine toată această bucătărie.

Întărirea regulilor

Receptorii inhibitori pot fi utilizați pentru a identifica modele mai complexe. Adică, introduceți modele care blochează funcționarea anumitor reguli afirmative atunci când apare o anumită combinație de biți de intrare. Aceasta pare crearea, în anumite condiții, a unui grup de receptori cu proprietăți inhibitoare. Când un astfel de cluster este declanșat, acesta nu va crește, ci va scădea potențialul punctului.

Nu este dificil să se vină cu reguli pentru testarea ipotezelor inhibitoare și să declanșeze consolidarea clusterelor receptive inhibitorii.

Deoarece grupurile inhibitoare sunt create în anumite puncte, ele nu influențează blocarea bitului de ieșire în general, ci blochează funcționarea acestuia din regulile detectate în acest punct particular. Este posibil să se complice arhitectura conexiunii și să se introducă reguli inhibitorii care sunt comune unui grup de puncte sau tuturor punctelor conectate la bitul de ieșire. Se pare că puteți veni cu lucruri mult mai interesante, dar deocamdată să ne concentrăm pe modelul simplu descris.

pădure întâmplătoare

Mecanismul descris vă permite să găsiți modele care în Data Mining sunt de obicei numite reguli de tip „dacă-atunci”. În consecință, se poate găsi ceva în comun între modelul nostru și toate acele metode care sunt folosite în mod tradițional pentru a rezolva astfel de probleme. Poate că cea mai apropiată de noi este „pădurea aleatorie”.

Această metodă începe cu ideea de „subspații aleatorii”. Dacă există prea multe variabile în datele sursă și aceste variabile sunt slab, dar corelate, atunci devine dificil să izolați modelele individuale folosind întreaga cantitate de date. În acest caz, este posibil să se creeze subspații în care atât variabilele utilizate, cât și exemplele de antrenament vor fi limitate. Adică, fiecare subspațiu va conține doar o parte din datele de intrare, iar aceste date nu vor fi reprezentate de toate variabilele, ci de un set limitat aleatoriu al acestora. Pentru unele dintre aceste subspații, șansele de a detecta un model greu de văzut în datele complete sunt mult crescute.

Apoi, în fiecare subspațiu, un arbore de decizie este antrenat pe un set limitat de variabile și exemple de antrenament. Un arbore de decizie este o structură de tip arbore (figura de mai jos), la nodurile cărora sunt verificate variabilele de intrare (atributele). Pe baza rezultatelor verificării condițiilor la noduri, se determină calea de la vârf la nodul terminal, care se numește de obicei o frunză a arborelui. Frunza arborelui conține rezultatul, care poate fi valoarea unei cantități sau numărul clasei.

Exemplu de arbore de decizie

Pentru arborii de decizie, există diverși algoritmi de învățare care vă permit să construiți un arbore cu atribute mai mult sau mai puțin optime în nodurile sale.

În etapa finală, se aplică ideea de boosting. Arborele de decizie formează un comitet de vot. Pe baza opiniei colective se creează cel mai plauzibil răspuns. Principalul avantaj al stimulării este capacitatea de a combina mulți algoritmi „răi” (al căror rezultat este doar puțin mai bun decât aleatoriu) pentru a obține un rezultat final arbitrar „bun”.

Algoritmul nostru, care exploatează spațiul combinatoriu și clusterele de receptori, folosește aceleași idei fundamentale ca metoda pădurii aleatorii. Prin urmare, nu este surprinzător că algoritmul nostru funcționează și produce rezultate bune.

Biologia învăţării

De fapt, acest articol descrie implementarea software a mecanismelor care au fost descrise în părțile anterioare ale seriei. Prin urmare, nu vom repeta totul de la bun început, vom nota doar punctele principale. Dacă ați uitat cum funcționează un neuron, îl puteți reciti.

Există mulți receptori diferiți localizați pe membrana neuronului. Majoritatea acestor receptori sunt „plutitori liberi”. Membrana creează un mediu pentru receptori în care aceștia se pot mișca liber, schimbându-și cu ușurință poziția pe suprafața neuronului (Sheng, M., Nakagawa, T., 2002) (Tovar K. R., Westbrook G. L., 2002).

Membrană și receptori

În abordarea clasică, motivele pentru o astfel de „libertate” a receptorilor nu sunt de obicei subliniate. Când o sinapsă își crește sensibilitatea, aceasta este însoțită de mișcarea receptorilor din spațiul extrasinaptic în fanta sinaptică (Malenka R.C., Nicoll R.A., 1999). Acest fapt este perceput tacit ca o justificare pentru mobilitatea receptorilor.

În modelul nostru, putem presupune că principalul motiv pentru mobilitatea receptorilor este nevoia de a forma grupuri din ei „din mers”. Adică imaginea arată așa. O varietate de receptori, sensibili la diverși neurotransmițători, se deplasează liber de-a lungul membranei. Semnalul informațional generat în minicoloană determină eliberarea de neurotransmițători de către terminațiile axonale ale neuronilor și astrocitelor. Fiecare sinapsă în care sunt emiși neurotransmițătorii, în plus față de neurotransmițătorul principal, are propriul său aditiv unic care identifică această sinapsă particulară. Neurotransmițătorii ies din crăpăturile sinaptice în spațiul înconjurător, datorită căruia apare un cocktail specific de neurotransmițători în fiecare loc al dendritei (puncte ale spațiului combinatoriu) (ingredientele cocktailului indică biții care au lovit punctul). Acei receptori care rătăcesc liber și care în acest moment își găsesc neurotransmițătorul în acest cocktail (receptorii anumitor biți ai semnalului de intrare) trec într-o nouă stare - starea de căutare. În această stare, ei au un timp scurt (până când apare următoarea bătaie) timp în care pot întâlni alți receptori „activi” și pot crea un grup comun (un grup de receptori sensibili la o anumită combinație de biți).

Receptorii metabotropi și despre care vorbim despre ele, au o formă destul de complexă (figura de mai jos). Ele constau din șapte domenii transmembranare care sunt conectate prin bucle. În plus, au două capete libere. Datorită sarcinilor electrostatice de diferite semne, capetele libere se pot „lipi” unele de altele prin membrană. Datorită unor astfel de conexiuni, receptorii sunt combinați în grupuri.

Receptor metabotrop unic

După începerea fuziunii viata impreuna receptori într-un cluster. Se poate presupune că poziția receptorilor unul față de celălalt poate varia foarte mult și grupul poate lua forme bizare. Dacă presupunem că receptorii care lucrează împreună vor avea tendința de a avea loc prieten mai apropiat unul față de celălalt, de exemplu, din cauza forțelor electrostatice, atunci va rezulta o consecință interesantă. Cu cât astfel de receptori „articulați” sunt mai apropiați, cu atât atracția lor comună va fi mai puternică. Pe măsură ce se apropie, vor începe să-și consolideze influența reciproc. Acest comportament reproduce comportamentul filtrului Hebb, care selectează prima componentă principală. Cu cât filtrul este reglat mai precis la componenta principală, cu atât răspunsul său este mai puternic atunci când apare în exemplu. Astfel, dacă după un număr de iterații receptorii declanșați în comun ajung împreună în „centrul” condiționat al clusterului, iar receptorii „extra” sunt la distanță, la marginile sale, atunci, în principiu, astfel de receptori „extra” se poate autodistruge la un moment dat, atunci este pur și simplu să se desprindă de cluster. Și apoi vom obține un comportament de cluster similar cu cel descris mai sus în modelul nostru computațional.

Clusterele care au suferit consolidare se pot muta undeva „într-un refugiu sigur”, de exemplu, într-o despicatură sinaptică. Există un sigiliu postsinaptic, la care se pot ancora grupuri de receptori, pierzând mobilitatea de care nu mai au nevoie. În apropiere vor exista canale ionice pe care le pot controla prin proteinele G. Acum acești receptori vor începe să influențeze formarea potențialului postsinaptic local (potenţial punctual).

Potențialul local constă în influența comună a receptorilor activatori și inhibitori din apropiere. În abordarea noastră, activatorii sunt responsabili pentru recunoașterea modelelor care solicită activarea bitului de ieșire, în timp ce inhibitorii sunt responsabili pentru identificarea modelelor care blochează acțiunea regulilor locale.

Sinapsele (punctele) sunt situate pe arborele dendritic. Dacă undeva pe acest arbore există un loc în care mai mulți receptori de activare se declanșează simultan într-o zonă mică și acesta nu este blocat de receptorii inhibitori, atunci apare un vârf dendritic, care se extinde în corpul neuronului și, la atingerea dealului axonului , provoacă un vârf în neuronul însuși. Un arbore dendritic conectează multe sinapse la un singur neuron, la fel ca la generarea bitului de ieșire al unui spațiu combinatoriu.

Combinarea semnalelor de la diferite sinapse ale aceluiași arbore dendritic poate să nu fie o simplă adăugare logică, dar poate fi mai complexă și poate implementa un fel de algoritm de stimulare complicat.

Permiteți-mi să vă reamintesc că elementul de bază al cortexului este minicoloana corticală. Într-o mini-coloană, aproximativ o sută de neuroni sunt situați unul sub celălalt. În același timp, acestea sunt strâns învăluite în conexiuni, care sunt mult mai abundente în interiorul minicoloanei decât conexiunile care merg către minicoloanele învecinate. Întregul cortex cerebral este un spațiu de astfel de mini-coloane. Un neuron minicoloană poate corespunde unui bit de ieșire, toți neuronii unei minicoloane corticale pot fi un analog al vectorului binar de ieșire.

Grupurile de receptori descrise în acest capitol creează memoria responsabilă pentru căutarea modelelor. Anterior, am descris cum să creați o memorie de eveniment holografică folosind grupuri de receptori. Acestea sunt două tipuri diferite de memorie care îndeplinesc funcții diferite, deși se bazează pe mecanisme comune.

Vis

La o persoană sănătoasă, somnul începe cu prima etapă a somnului cu unde lente, care durează 5-10 minute. Urmează a doua etapă, care durează aproximativ 20 de minute. Alte 30-45 de minute apar în timpul etapelor a treia și a patra. După aceasta, dormitorul revine la a doua etapă a somnului cu unde lente, după care apare primul episod de somn REM, care are o durată scurtă de aproximativ 5 minute. În timpul somnului REM, globii oculari fac foarte des și periodic mișcări rapide sub pleoapele închise. Dacă trezești o persoană adormită în acest moment, atunci în 90% din cazuri poți auzi o poveste despre un vis viu. Toată această secvență se numește ciclu. Primul ciclu durează 90-100 de minute. Apoi ciclurile se repetă, proporția somnului cu unde lente scăzând și proporția somnului REM crescând treptat, ultimul episod al căruia în unele cazuri poate ajunge la 1 oră. În medie, cu un somn complet sănătos, există cinci cicluri complete.

Se poate presupune că activitatea principală de curățare a grupurilor de receptori care s-au acumulat în timpul zilei are loc în somn. În modelul de calcul, am descris procedura de antrenament „inactiv”. Vechea experiență este prezentată creierului fără a provoca formarea de noi clustere. Scopul este de a testa ipotezele existente. Această verificare constă în două etape. Prima este calcularea componentei principale a modelului și verificarea faptului că numărul de biți responsabili pentru acesta este suficient pentru o identificare clară. Al doilea este verificarea adevărului ipotezei, adică că modelul s-a dovedit a fi în punctul potrivit asociat cu bitul de ieșire dorit. Se poate presupune că unele dintre etapele somnului nocturn sunt asociate cu astfel de proceduri.

Toate procesele asociate cu modificări ale celulelor sunt însoțite de expresia anumitor proteine ​​și factori de transcripție. Există proteine ​​și factori care s-au dovedit a fi implicați în formarea de noi experiențe. Deci, se dovedește că numărul lor crește foarte mult în timpul stării de veghe și scade brusc în timpul somnului.

Concentrația de proteine ​​poate fi văzută și evaluată prin colorarea unei secțiuni de țesut cerebral cu un colorant care reacționează selectiv la proteina necesară. Observații similare au arătat că cele mai răspândite modificări ale proteinelor asociate cu memoria au loc în timpul somnului (Chiara Cirelli, Giulio Tononi, 1998) (Cirelli, 2002) (figurele de mai jos).

Distribuția proteinei arcului în cortexul parietal de șobolan după trei ore de somn (S) și după trei ore de veghe spontană (W) (Cirelli, 2002)

Distribuția factorului de transcripție P-CREB în regiunile coronale ale cortexului parietal de șobolan după trei ore de somn (S) și în cazul a trei ore de privare de somn (SD) (Cirelli, 2002)

Un astfel de raționament despre rolul somnului se potrivește bine cu caracteristica binecunoscută - „dimineața este mai înțeleaptă decât seara”. Dimineața avem o înțelegere mult mai bună a ceea ce nu a fost deosebit de clar ieri. Totul devine mai clar și mai evident. Este posibil să datorăm acest lucru tocmai curățării la scară largă a grupurilor de receptori care a avut loc în timpul somnului. Ipotezele false și dubioase sunt înlăturate, cele de încredere sunt supuse consolidării și încep să participe mai activ la procesele informaționale.

În timpul simulării, a fost clar că numărul de ipoteze false a fost de multe mii de ori mai mare decât numărul celor adevărate. Întrucât unul poate fi distins de altul doar prin timp și experiență, creierul nu are de ales decât să acumuleze toate aceste informații, în speranța de a găsi în el grame de radiu în timp. Pe măsură ce se dobândește o nouă experiență, numărul de clustere cu ipoteze care necesită testare este în continuă creștere. Numărul de clustere formate pe zi și care conțin minereu care nu a fost încă procesat poate depăși numărul de clustere responsabile pentru codificarea experienței dovedite acumulate de-a lungul întregii vieți anterioare. Resursa creierului pentru stocarea ipotezelor brute care necesită testare ar trebui să fie limitată. Se pare că în timpul celor 16 ore de veghe în timpul zilei, grupurile de receptori umplu aproape complet tot spațiul disponibil. Când vine acest moment, creierul începe să ne oblige să intrăm în modul de somn pentru a-i permite să efectueze consolidarea și să elibereze spațiul liber. Aparent, procesul complet de curățare durează aproximativ 8 ore. Dacă ne treziți mai devreme, unele dintre clustere vor rămâne neprocesate. Aici se acumulează fenomenul de oboseală. Dacă nu dormi suficient timp de câteva zile, atunci va trebui să compensați somnul pierdut. În caz contrar, creierul începe să ștergă „de urgență” clusterele, ceea ce nu duce la nimic bun, deoarece ne privează de oportunitatea de a obține cunoștințe din experiența dobândită. Este posibil ca memoria evenimentelor să fie păstrată, dar modelele vor rămâne nedetectate.
Apropo, sfatul meu personal: nu neglijați somnul de calitate, mai ales dacă studiați. Nu încercați să economisiți bani pe somn, astfel încât să puteți face mai multe. Somnul nu este mai puțin important în învățare decât participarea la prelegeri și revizuirea materialelor din orele practice. Nu degeaba copiii, în acele perioade de dezvoltare în care acumularea și generalizarea informațiilor este cea mai activă, își petrec cea mai mare parte a timpului dormind.

Performanța creierului

Ipoteza despre rolul clusterelor receptive ne permite să aruncăm o nouă privire asupra problemei vitezei creierului. Mai devreme am spus că fiecare mini-coloană a cortexului, constând din sute de neuroni, este un modul de calcul independent care ia în considerare interpretarea informațiilor primite într-un context separat. Acest lucru permite unei zone corticale să ia în considerare până la un milion de opțiuni de interpretare posibile simultan.

Acum putem presupune că fiecare grup de receptori poate funcționa ca un element de calcul autonom, efectuând întregul ciclu de calcule pentru a-și testa ipoteza. Pot exista sute de milioane de astfel de grupuri numai în coloana corticală. Aceasta înseamnă că, deși frecvențele la care funcționează creierul sunt departe de frecvențele la care funcționează computerele moderne, nu este nevoie să vă faceți griji cu privire la viteza creierului. Sute de milioane de grupuri de receptori care lucrează în paralel în fiecare mini-coloană a cortexului fac posibilă rezolvarea cu succes a problemelor complexe care se află la granița cu o explozie combinatorie. Nu vor fi miracole. Dar poți învăța să mergi pe margine.

sens

rețele neuronale

neuron

constiinta

Adăugați etichete

Ghidul utilizatorului.

NatClass

Numele operațiunii

Construirea clasificării și analizei secvențelor genomice.

Condițiile în care operația poate fi efectuată

Ordin de operare

Acțiuni pregătitoare

Acțiuni de bază în ordinea necesară.

1.

Datele de intrare în program sunt două mostre de secvențe în format FASTA: Secvențe pozitive (eșantioane de secvențe genomice, Secvențe negative (eșantioane de secvențe aleatorii sau secvențe genomice contrastante).

Pentru a încărca datele de antrenament, utilizați comanda de meniu Sursa ->AdăugaPozitivSecvențe(Fig. 1) sau butonul din bara de instrumente. Pe ecran apare un expert și vă solicită să specificați numele fișierului cu un eșantion pozitiv/negativ de secvențe.

Fișierele de proiect salvate anterior folosind acest program pot servi ca date de intrare. Astfel de fișiere de proiect pot stoca toate datele care au fost încărcate sau primite în momentul salvării.

2.Setarea parametrilor programului. Pornirea procesului de generare a modelelor.

În prima filă, „ Reguli ”, există elemente de căutare a tiparelor (Fig. 2). Trebuie să setați parametrii pentru căutarea modelelor și să faceți clic pe butonul „Start”.

Parametrii de căutare sunt:

Interval de încredere : nivelul minim de probabilitate condiționată;

Min. Nivelul CP : prag pentru valoarea testului Fisher;

Dimensiunea tamponului de finisare : numărul de modele detectate;

Dimensiunea bufferelor secundare : Dimensiunea tamponului auxiliar al modelului.

Se selectează și modul de funcționare: poziții fixe ( Poziții fixe ) sau modul fereastră glisantă ( Schimbați pozițiile ). Acesta din urmă este utilizat pentru recunoașterea de-a lungul unei secvențe genomice lungi și necesită specificarea dimensiunii ferestrei ( Lățimea cadrului de scanare)

Programul vă permite să întrerupeți procesul de generare a modelului (făcând clic pe butonul „Pauză”) sau să opriți procesul (făcând clic pe butonul „Oprire”).

Orez. 2. Elemente de marcaj pentru căutarea modelelor.

La sfârșitul procesului de căutare a modelelor, programul afișează mesajul „Procesul de căutare a modelelor a fost finalizat cu succes”. Ca urmare, modelele găsite sunt prezentate utilizatorului în ordinea în care au fost descoperite (Fig. 3).

Orez. 3 Modele descoperite.

3. Construirea obiectelor de clasă ideală.

În plus față de modelele detectate, ieșirea programuluiNatClassla fel sunt reprezentanţii ideali ai claselor. Pentru construcția lor se folosește Fila „Obiecte”. ” programe (Fig. 4). Obiectele ideale pot fi construite fie din obiecte inițiale dintr-un eșantion de antrenament pozitiv (opțiunea „ obiecte originale "), sau după modele (" regularități "). De asemenea, puteți alege una dintre cele trei opțiuni pentru algoritmul de construcție ( tipul de idealizare ), stabilirea priorităților între eliminarea și adăugarea de funcții. După construirea obiectelor ideale, programul atribuie în consecință obiectele de învățare uneia dintre clasele detectate sau le recunoaște ca aparținând unei clase noi. Nou " Prin analogie cu procesul de generare a tiparelor, procesul de idealizare poate fi suspendat(prin apăsarea butonului „Pauză”) sau oprire (prin apăsarea butonului „Stop”).

Orez. 4. Dispunerea elementelor pentru construirea obiectelor ideale.

La sfârșitul procesului de căutare a modelelor, programul afișează mesajul „Procesul de idealizare a fost finalizat cu succes”.

4. Aplicarea tiparelor obținute. Calculul erorilor de recunoaștere.

Fila Clasuri conţine funcţii pentru procesarea datelor de ieşire primite (Fig. 5).

Următoarele funcții sunt disponibile aici pentru analiza rezultatelor numărării: clasificarea probelor de control (“ Clasificare "), recunoaștere în raport cu clasele disponibile (" Date de control al recunoașterii "), erori de numărare de recunoaștere (" Recognition Errors Count”), procedura Bootstrap.

Pentru a încărca secvențele de control utilizați comanda de meniu Control ->AdăugaControlaPozitiv.

La numărarea erorilor de recunoaștere, programul va produce rezultatul optim (construiește o histogramă), dar utilizatorul o poate corecta independent, setând fie pragul de recunoaștere („Recunoașteți Nivel "), sau valoarea primei erori de tip ("1 Sf eroare de nivel").

Orez. 5. Marcaj „ Clasele”.

Când faceți clic dreapta pe un obiect ideal, apare opțiunea de ștergere a obiectului („Şterge ", orez. 6), arată obiectele clasei („ Afișați obiecte ", orez. 7), modele („ Afișați regularități "), matrice de predicție (" Matricea de predicție "), matrice de recunoaștere (" Matricea de recunoaștere”, Fig. 8)

Orez. 6 Operații efectuate de program cu un obiect ideal.

Orez. 7 Operațiunea de afișare a obiectelor din clasa „ Spectacolobiecte”.

Orez. 8 Operațiunea de afișare a matricei de recunoaștere a obiectelor din clasa „ RecunoaştereMatrice”.

Rezultatele recunoașterii și erorile sunt salvate de program în formular tabele html.

Caz de testare: Construcția clasificării și analizei site-urilor de legare a factorului de transcripție (TFBS) EGR 1.

1.

Intrarea în program este dată ca un eșantion pozitiv: SSTF EGR 1:

>S1916;

gtccgtgggt

>S4809;

ttggggggcga

>S6067;

gagggggcgg

fişier EGR1_poz.seq.

Ca negative - secvențe aleatorii generate cu aceleași frecvențe de nucleotide ca și secvențe pozitive:

>S1916 ;_ N1_H1_W1;

gggtcttggc

>S1916 ;_ N1_H2_W1;

gggcgtttcg

>S1916 ;_ N1_H3_W1;

ggtggggctct

fişier neg _2200. secv

Pentru a descărca datele de intrare, consultați Manualul utilizatorului, punctul 1

2.Setarea parametrilor programului. Pornirea procesului de generare a modelelor.

Setați parametri de căutare:

Interval de încredere: 0,05 ;

Min. Nivelul CP: 0 ,8 ;

Dimensiunea tamponului de finisare: 2000;

Dimensiunea bufferelor secundare: 100.

Programul a descoperit 2000 de modele (Fig. 9).

Orez. 9 Modele care satisfac parametrii de căutare.

3. Construirea obiectelor de clasă ideală.

Ca rezultat al programului, a fost descoperită o clasă. Obiectul de clasă ideală și matricea de predicție sunt prezentate în Fig. 10.

Orez. 10. Obiect de clasă ideală și matrice de predicție pentru SSTF EGR1.

4. Aplicarea tiparelor obținute. Calculul erorilor de recunoaștere.

Secvențele generate la aceeași frecvență de nucleotide ca și secvențele pozitive au fost luate drept martori negativi. Fişier controla_ neg _1000. secv. Programul a efectuat clasificarea, calculul greutății fiecărui obiect și recunoașterea (Fig. 11).

Orez. 11. Clasificarea și recunoașterea obiectelor de control pentru STTF EGR1.