Descărcați exemplul de registru de lucru
Acest tutorial vă va învăța cum să căutați ultima valoare din coloană sau rând în Excel.
Ultima valoare din coloană
Puteți utiliza funcția CĂUTARE pentru a găsi ultima celulă ne-goală dintr-o coloană.
| 1 | = LOOKUP (2,1 / (B: B ""), B: B) |
Să parcurgem această formulă.
Partea formulei B: B ”” returnează o matrice care conține valori True și False: {FALSE, TRUE, TRUE, …}, testarea fiecărei celule din coloana B este goală (FALSE).
| 1 | = LOOKUP (2,1 / ({FALSE; ADEVĂRATE; ADEVĂRATE; ADEVATE; ADEVATE; ADEVATE; FALSE; …), B: B) |
Aceste valori booleene se convertesc la 0 sau 1 și sunt folosite pentru a împărți 1.
| 1 | = LOOKUP (2, {# DIV / 0!; 1; 1; 1; 1; 1; # DIV / 0!;, B: B) |
Acesta este vectorul de căutare pentru funcția LOOKUP. În cazul nostru, lookup_value este 2, dar cea mai mare valoare din lookup_vector este 1, deci Funcția LOOKUP se va potrivi cu ultima 1 din matrice și va returna valoarea corespunzătoare în result_vector.
Dacă sunteți sigur că aveți doar valori numerice în coloană, datele dvs. încep de la rândul 1 și intervalul de date continuu, puteți utiliza o formulă puțin mai simplă cu funcțiile INDEX și COUNT.
| 1 | = INDEX (B: B, COUNT (B: B)) |
Funcția COUNT returnează numărul de celule umplute cu date în intervalul continuu (4), iar funcția INDEX oferă astfel valoarea celulei din acest rând corespunzător (al 4-lea).
Pentru a evita posibilele erori atunci când gama dvs. de date conține un amestec de valori numerice și nenumerice, sau chiar unele celule goale, puteți utiliza funcția CĂUTARE împreună cu funcțiile ISBLANK și NOT.
| 1 | = LOOKUP (2,1 / (NOT (ISBLANK (B: B)))), B: B) |
Funcția ISBLANK returnează o matrice care conține valori True și False, corespunzătoare 1’s și 0’s. Funcția NOT schimbă True (adică 1) în False și False (adică 0) în True. Dacă inversăm această matrice rezultată (când împărțim 1 la această matrice), vom obține o matrice de rezultate care conține din nou # DIV / 0! erori și 1, care pot fi utilizate ca matrice de căutare (lookup_vector) în funcția noastră de căutare. Funcționalitatea funcției LOOKUP este atunci aceeași cu cea din primul nostru exemplu: returnează valoarea vectorului rezultat la poziția ultimului 1 din matricea de căutare.
Când aveți nevoie de numărul rândului cu ultima intrare care trebuie returnată, puteți modifica formula folosită în primul nostru exemplu împreună cu funcția ROW din rezultatul_vector.
| 1 | = LOOKUP (2,1 / (B: B ""), ROW (B: B)) |
Ultima valoare în rând
Pentru a obține valoarea ultimei celule ne-goale într-un rând umplut cu date numerice, poate doriți să utilizați o abordare similară, dar cu funcții diferite: funcția OFFSET împreună cu funcțiile MATCH și MAX.
| 1 | = OFFSET (Referință, rânduri, coloane) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2) + 1, B2: XFD2,1) -1) |
Să vedem cum funcționează această formulă.
Funcția MATCH
Folosim funcția MATCH pentru a „număra” câte valori de celule sunt sub 1 + maximul tuturor valorilor din rândul 2 începând de la B2.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2) + 1, B2: XFD2,1) |
Valoarea de căutare a funcției MATCH este maximul tuturor valorilor din rândul 2 + 1. Întrucât această valoare evident nu există în rândul 2 și tipul de potrivire este setat la 1 (mai mic sau egal cu valoarea de căutare), funcția MATCH va returna ultima poziție a celulei „verificate” în matrice, adică numărul de celule umplute cu date în intervalul B2: XFD2 (XFD este ultima coloană din versiunile mai noi de Excel).
Funcția OFFSET
Apoi folosim funcția OFFSET pentru a obține valoarea acestei celule, a cărei poziție a fost returnată de funcția MATCH.
| 1 | = OFFSET (B2,0, C4-1) |
