Lejupielādējiet darbgrāmatas piemēru
Šī apmācība iemācīs jums meklēt pēdējo vērtību kolonnā vai rindā programmā Excel.
Pēdējā vērtība kolonnā
Varat izmantot funkciju MEKLĒT, lai atrastu pēdējo šūnu, kas nav tukša.
| 1 | = MEKLĒT (2,1/(B: B ""), B: B) |
Apskatīsim šo formulu.
Formulas B daļa: B ”” atgriež masīvu, kas satur patiesas un nepatiesas vērtības: {FALSE, TRUE, TRUE,…}, pārbaudot katru B slejas šūnu ir tukša (FALSE).
| 1 | = MEKLĒT (2,1/({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE;…), B: B) |
Šīs Būla vērtības pārvēršas par 0 vai 1 un tiek izmantotas, lai dalītu 1.
| 1 | = MEKLĒT (2, {#DIV/0!; 1; 1; 1; 1; 1; 1;#DIV/0!; B: B) |
Šis ir funkcijas LOOKUP lookup_vector. Mūsu gadījumā lookup_value ir 2, bet lookup_vector lielākā vērtība ir 1, tāpēc funkcija LOOKUP sakrīt ar masīva pēdējo 1 un atgriež atbilstošo vērtību rezultāta vektorā.
Ja esat pārliecināts, ka kolonnā ir tikai skaitliskas vērtības, jūsu dati sākas no 1. rindas un datu diapazons nepārtraukti, varat izmantot nedaudz vienkāršāku formulu ar funkcijām INDEX un COUNT.
| 1 | = INDEKSS (B: B, COUNT (B: B)) |
Funkcija COUNT atgriež šūnu skaitu, kas piepildīts ar datiem nepārtrauktajā diapazonā (4), un funkcija INDEX tādējādi norāda šūnas vērtību šajā atbilstošajā rindā (4.).
Lai izvairītos no iespējamām kļūdām, ja jūsu datu diapazonā ir skaitlisku un neskaitlisku vērtību maisījums vai pat dažas tukšas šūnas, varat izmantot funkciju LOOKUP kopā ar funkcijām ISBLANK un NOT.
| 1 | = MEKLĒT (2,1/(NAV (ISBLANK (B: B))), B: B) |
Funkcija ISBLANK atgriež masīvu, kurā ir patiesas un nepatiesas vērtības, kas atbilst 1 un 0. Funkcija NOT maina True (t.i. 1) uz False un False (t.i. 0) uz True. Ja mēs apvērsim šo iegūto masīvu (dalot 1 ar šo masīvu), mēs iegūstam rezultātu masīvu, kas atkal satur #DIV/0! kļūdas un 1, ko var izmantot kā uzmeklēšanas masīvu (lookup_vector) mūsu LOOKUP funkcijā. Funkcijas MEKLĒŠANA funkcionalitāte ir tāda pati kā mūsu pirmajā piemērā: tā atgriež rezultātu vektora vērtību uzmeklēšanas masīva pēdējā 1 vietā.
Ja jums ir jāatgriež rindas numurs ar pēdējo ierakstu, varat mainīt mūsu pirmajā piemērā izmantoto formulu kopā ar funkciju ROW savā rezultāta vektorā.
| 1 | = LOOKUP (2,1/(B: B ""), ROW (B: B)) |
Pēdējā vērtība rindā
Lai iegūtu rindas, kas piepildīta ar skaitliskiem datiem, pēdējās tukšās šūnas vērtību, iespējams, vēlēsities izmantot līdzīgu pieeju, bet ar dažādām funkcijām: funkciju OFFSET kopā ar funkcijām MATCH un MAX.
| 1 | = OFFSET (atsauce, rindas, kolonnas) |
| 1 | = Nobīde (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) |
Apskatīsim, kā šī formula darbojas.
MATCH funkcija
Mēs izmantojam funkciju MATCH, lai “saskaitītu”, cik šūnu vērtību ir zem 1 + maksimālās vērtības, kas ir visas 2. rindas vērtības, sākot no B2.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) |
Funkcijas MATCH_vērtība ir maksimālā vērtība no visām 2. un 1. rindas vērtībām. Tā kā šī vērtība acīmredzami nepastāv 2. rindā un atbilstības veids ir iestatīts uz 1 (mazāks vai vienāds ar lookup_value), funkcija MATCH atgriež pēdējā “pārbaudītā” šūnas pozīcija masīvā, tas ir, šūnu skaits, kas piepildīts ar datiem diapazonā B2: XFD2 (XFD ir pēdējā sleja jaunākajās Excel versijās).
OFFSET funkcija
Tad mēs izmantojam funkciju OFFSET, lai iegūtu šīs šūnas vērtību, kuras pozīciju atdeva funkcija MATCH.
| 1 | = Nobīde (B2,0, C4-1) |
