Pārbaudiet šo skaitli:
Pieņemsim, ka vēlaties izveidot pārskatu no tā, it kā jūs filtrētu reģionā. Tas ir, ja jūs filtrētu uz ziemeļiem, jūs redzētu:
Bet ko tad, ja jūs vēlaties, lai viena un tā pati versija būtu balstīta uz formulu?
Lūk, kādu rezultātu jūs meklējat I slejā: K:
Skaidrs, ka tas ir tas pats pārskats, taču šeit nav filtrētu vienumu. Ja vēlaties jaunu ziņojumu par Austrumiem, būtu jauki vienkārši mainīt vērtību G1 uz Austrumiem:
Lūk, kā tas tiek darīts. Pirmkārt, tas nav izdarīts, izmantojot VLOOKUP. Tāpēc es meloju par šīs tehnikas nosaukumu!
F kolonna iepriekš netika parādīta, un to var paslēpt (vai pārvietot kaut kur citur, lai tas netraucētu pārskatam).
F kolonnā tiek parādīti to rindu numuri, kur A kolonnā ir G1; tas ir, kādās rindās ir vērtība “ziemeļi”? Šī metode ietver, izmantojot šūnu iepriekš, tāpēc tai jābūt sāksies 2. Tas sakrīt ar vērtību "Ziemeļi" pret A ailē, bet tā vietā visa kolonnas, izmantojiet OFFSET funkciju vismaz rindas: OFFSET($A$1,F1,0,1000,1).
Tā kā F1 ir 0, tas ir tas, OFFSET(A1,0,0,1000,1)kas ir A1: A1000. (1000 ir patvaļīgs, bet pietiekami liels, lai veiktu šo darbu - jūs varat to padarīt par jebkuru citu skaitli).
F2 vērtība 2 ir vieta, kur atrodas pirmais “ziemeļu” punkts. Jūs arī vēlaties vēlreiz pievienot F1 vērtību, taču līdz šim tā ir nulle.
“Maģija” atdzīvojas F3 šūnā. Jūs jau zināt, ka pirmie ziemeļi ir atrodami 2. rindā. Tātad, jūs vēlaties sākt meklēt divas rindas zem A1. To var izdarīt, norādot 2 kā funkciju OFFSET otro argumentu.
Ar F3 formulu automātiski norāda uz 2, kas tika aprēķināta šūnā F2: Kad jūs kopēt formulu uz leju, jūs redzēsiet =OFFSET($A$1,F2,0,1000,1), kas ir OFFSET($A$1,2,0,1000,1), kas ir A3: A1000. Tātad jūs salīdzināt ziemeļus pret šo jauno diapazonu, un tas atrod ziemeļus šī jaunā diapazona trešajā šūnā, tāpēc MATCH dod 3.
Pievienojot atpakaļ vērtību no iepriekš norādītās šūnas F2, jūs redzēsiet 3 plus 2 vai 5, kas ir rinda, kurā ir otra ziemeļu daļa.
Šī formula ir aizpildīta pietiekami tālu, lai iegūtu visas vērtības.
Tādējādi iegūsit rindu numurus, kur atrodami visi ziemeļu ieraksti.
Kā jūs pārtulkojat šos rindu numurus no I līdz K slejas rezultātiem? Tas viss tiek darīts ar vienu formulu. Ievadīt šo formulu I2: =IFERROR(INDEX(A:A,$F2),””). Kopējiet pa labi un pēc tam uz leju.
Kāpēc izmantot IFERROR? Kur ir kļūda? Ievērojiet šūnu F6 - tajā ir # N / A (tieši tāpēc jūs vēlaties paslēpt F kolonnu), jo pēc 15. rindas vairs nav ziemeļu. Tātad, ja kolonna F ir kļūda, atgrieziet tukšu. Pretējā gadījumā atlasiet vērtību no A slejas (un, aizpildot pa labi, B & C).
$ F2 ir absolūta atsauce uz F kolonnu, tāpēc aizpildīšanas tiesības joprojām attiecas uz F kolonnu.
Šis viesu raksts ir no Excel MVP Bob Umlas. Tas ir viens no viņa iecienītākajiem paņēmieniem no viņa grāmatas Excel ārpus kastes.
Excel ārpus kastes »
