Vispārēja formula
(=MATCH(2,1/(range"")))
Kopsavilkums
Lai iegūtu jauktu datu, kas var saturēt tukšas šūnas, pēdējo relatīvo pozīciju (ti, pēdējo rindu, pēdējo kolonnu), varat izmantot funkciju MATCH, kā aprakstīts tālāk.
Piezīme: šī ir masīva formula, un tā jāievada ar Control + Shift + Enter.
Parādītajā piemērā formula E5 ir:
(=MATCH(2,1/(B4:B10"")))
Darba lapas pēdējā * relatīvā * pozīcija, nevis rinda
Veidojot sarežģītākas formulas, bieži vien ir jānoskaidro datu pēdējā atrašanās vieta sarakstā. Atkarībā no datiem tā var būt pēdējā rinda ar datiem, pēdējā kolonna ar datiem vai abu krustpunkts. Mēs vēlamies, lai pēdējā relatīvā pozīcija * noteiktā diapazonā būtu nevis darblapas rindas numurs:
Paskaidrojums
Šajā formulā tiek izmantota funkcija MATCH, kas konfigurēta, lai atrastu pēdējās ne tukšās šūnas pozīciju diapazonā.
Strādājot no iekšpuses uz āru, meklēšanas masīvs MATCH iekšpusē tiek veidots šādi:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Piezīme: visas masīva vērtības ir vai nu 1, vai # DIV / 0! kļūda.
Pēc tam MATCH tiek iestatīts tā, lai tas atbilstu 2. vērtībai "aptuvenajā spēles režīmā", izlaižot 3. argumentu, tiek izlaists.
Tā kā uzmeklēšanas vērtība 2 nekad netiks atrasta, MATCH vienmēr atradīs pēdējo 1 meklēšanas masīvā, kas atbilst pēdējai tukšajai šūnai.
Šī pieeja darbosies ar jebkura veida datiem, ieskaitot skaitļus, tekstu, datumus utt. Tā darbojas arī ar nulles teksta virknēm, kuras tiek atgrieztas ar šādām formulām:
=IF(A1<100,"")
Dinamiskais diapazons
Jūs varat izmantot šo formulu, lai izveidotu dinamisko diapazonu ar citām funkcijām, piemēram, INDEX un OFFSET. Piemērus un paskaidrojumus skatiet zemāk esošajās saitēs:
- Dinamiskais diapazons ar INDEX un COUNTA
- Dinamiskais diapazons ar OFFSET un COUNTA
Iedvesma šim rakstam radās no Maika Girvina lieliskās grāmatas Control + Shift + Enter, kur Maiks lieliski strādā, izskaidrojot jēdzienu "pēdējā radinieka pozīcija".
