În acest tutorial, veți afla cum să găsiți ultima apariție a unui articol dintr-o listă utilizând formule Excel.
Recent, lucram la stabilirea agendei unei întâlniri.
Am avut o listă în Excel, unde am avut o listă de persoane și datele la care au acționat ca „președinte de întâlnire”.
Întrucât în listă se repetau (ceea ce înseamnă că o persoană a fost președinte de întâlnire de mai multe ori), de asemenea, trebuia să știu când a fost ultima dată când o persoană a acționat ca „președinte de întâlnire”.
Acest lucru se datorează faptului că a trebuit să mă asigur că cineva care a prezidat recent nu va mai fi repartizat.
Așa că am decis să folosesc magia Funcției Excel pentru a face acest lucru.
Mai jos este rezultatul final în care pot selecta un nume din meniul derulant și îmi dă data ultimei apariții a acelui nume în listă.
Dacă aveți o bună înțelegere a funcțiilor Excel, știți că nu există nicio funcție Excel care să poată face acest lucru.
Dar vă aflați în secțiunea Formula Hack și aici facem ca magia să se întâmple.
În acest tutorial, vă voi arăta trei moduri de a face acest lucru.
Găsiți ultima apariție - Folosind funcția MAX
Meritarea acestei tehnici este acordată unui articol al Excel MVP Charley Kyd.
Iată formula Excel care va returna ultima valoare din listă:
= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14)) * ($ D $ 3 = $ A $ 2: $ A $ 14)) - 1))
Iată cum funcționează această formulă:
- Funcția MAX este utilizată pentru a găsi numărul rândului ultimului nume de potrivire. De exemplu, dacă numele este Glen, acesta va întoarce 11, deoarece se află pe rândul 11. Întrucât lista noastră începe de la al doilea rând, 1 a fost scăzut. Deci poziția ultimei apariții a lui Glen este 10 pe lista noastră.
- SUMPRODUCT este utilizat pentru a vă asigura că nu trebuie să utilizați Control + Shift + Enter, deoarece SUMPRODUCT poate gestiona formule de matrice.
- Funcția INDEX este acum utilizată pentru a găsi data ultimului nume de potrivire.
Găsiți ultima apariție - Folosind funcția de căutare
Iată o altă formulă pentru a face aceeași treabă:
= CĂUTARE (2,1 / ($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Iată cum funcționează această formulă:
- Valoarea căutării este 2 (veți vedea de ce … continuați să citiți)
- Intervalul de căutare este 1 / ($ A $ 2: $ A $ 14 = $ D $ 3) - Aceasta returnează 1 atunci când găsește numele potrivit și o eroare când nu. Deci ajungi să obții o matrice. De exemplu, din valoarea de căutare este Glen, matricea ar fi {# DIV / 0!; # DIV / 0!; 1; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; 1; # DIV / 0!; # DIV / 0!; # DIV / 0!}.
- Al treilea argument ([result_vector]) este intervalul din care dă rezultatul, care sunt date în acest caz.
Motivul pentru care această formulă funcționează este că funcția LOOKUP folosește tehnica de potrivire aproximativă. Aceasta înseamnă că, dacă poate găsi valoarea potrivită exactă, ar returna acest lucru, dar dacă nu poate, va scana întreaga matrice până la sfârșit și va returna următoarea cea mai mare valoare, care este mai mică decât valoarea de căutare.
În acest caz, valoarea de căutare este 2, iar în matricea noastră vom primi doar 1 sau erori. Deci, scanează întreaga matrice și returnează poziția ultimului 1 - care este ultima valoare de potrivire a numelui.
Găsiți ultima apariție - Utilizarea funcției personalizate (VBA)
Permiteți-mi să vă arăt și un alt mod de a face acest lucru.
Putem crea o funcție personalizată (numită și funcție definită de utilizator) folosind VBA.
Avantajul creării unei funcții personalizate este că este ușor de utilizat. Nu trebuie să vă faceți griji cu privire la crearea unei formule complexe de fiecare dată, deoarece cea mai mare parte a lucrării se întâmplă în backend-ul VBA.
Am creat o formulă simplă (care seamănă mult cu formula VLOOKUP).
Pentru a crea o funcție personalizată, trebuie să aveți codul VBA în Editorul VB. Vă voi da codul și pașii pentru a-l plasa în Editorul VB peste o vreme, dar permiteți-mi să vă arăt mai întâi cum funcționează:
Aceasta este formula care vă va oferi rezultatul:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Formula ia trei argumente:
- Valoare de căutare (acesta ar fi numele din celula D3)
- Interval de căutare (acesta ar fi intervalul care are numele și datele - A2: B14)
- Număr coloană (aceasta este coloana din care dorim rezultatul)
Odată ce ați creat formula și ați introdus codul în VB Editor, îl puteți folosi la fel ca orice alte funcții obișnuite ale foii de lucru Excel.
Iată codul pentru formula:
„Acesta este un cod pentru o funcție care găsește ultima apariție a unei valori de căutare și returnează valoarea corespunzătoare din coloana specificată” Cod creat de Sumit Bansal (https://trumpexcel.com) Funcție LastItemLookup (Lookupvalue As String, LookupRange As Range, ColumnNumber as Integer) Dim i Long for i = LookupRange.Columns (1) .Cells.Count to 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Then LastItemLookup = LookupRange.Cells (i, ColumnNumber) Ieșiți din funcția End If Next i End Function
Iată pașii pentru plasarea acestui cod în Editorul VB:
- Accesați fila Dezvoltator.
- Faceți clic pe opțiunea Visual Basic. Aceasta va deschide editorul VB în backend.
- În panoul Explorator de proiecte din Editorul VB, faceți clic dreapta pe orice obiect pentru registrul de lucru în care doriți să inserați codul. Dacă nu vedeți Project Explorer, accesați fila View și faceți clic pe Project Explorer.
- Accesați Insert și faceți clic pe Module. Aceasta va insera un obiect modul pentru registrul dvs. de lucru.
- Copiați și lipiți codul în fereastra modulului.
Acum formula va fi disponibilă în toată foaia de lucru a registrului de lucru.
Rețineți că trebuie să salvați registrul de lucru în format .XLSM, deoarece are o macro în el. De asemenea, dacă doriți ca această formulă să fie disponibilă în toate registrele de lucru pe care le utilizați, o puteți salva în Registrul personal de lucru pentru macro sau puteți crea un supliment din acesta.
