Šajā apmācībā jūs uzzināsit, kā atrast vienuma pēdējo notikumu sarakstā, izmantojot Excel formulas.
Nesen strādāju pie sanāksmes darba kārtības noteikšanas.
Man bija Excel saraksts, kurā man bija cilvēku saraksts un datumi, kad viņi darbojās kā “sapulces priekšsēdētājs”.
Tā kā sarakstā bija atkārtošanās (tas nozīmē, ka persona ir tikusies ar priekšsēdētāju vairākas reizes), man arī bija jāzina, kad persona pēdējo reizi darbojās kā “sapulces priekšsēdētāja”.
Tas bija tāpēc, ka man bija jānodrošina, lai kāds, kurš nesen vadīja, netiktu atkal iecelts.
Tāpēc es nolēmu izmantot kādu Excel funkciju burvju, lai to paveiktu.
Zemāk ir galīgais rezultāts, kurā es varu izvēlēties vārdu nolaižamajā izvēlnē, un tas norāda datumu, kad pēdējā vārda parādīšanās sarakstā.
Ja jums ir laba izpratne par Excel funkcijām, jūs zināt, ka nav nevienas Excel funkcijas, kas to varētu izdarīt.
Bet jūs atrodaties sadaļā Formula Hack, un šeit mēs liekam burvībai notikt.
Šajā apmācībā es jums parādīšu trīs veidus, kā to izdarīt.
Atrodiet pēdējo notikumu - izmantojot funkciju MAX
Pateicoties šai tehnikai, var minēt Excel MVP Charley Kyd rakstu.
Šeit ir Excel formula, kas atgriež pēdējo vērtību no saraksta:
= INDEKSS ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Šī formula darbojas šādi.
- Funkcija MAX tiek izmantota, lai atrastu pēdējā atbilstošā nosaukuma rindas numuru. Piemēram, ja vārds ir Glen, tas atgriezīs 11, kā tas ir 11 rindā. Tā kā mūsu saraksts sākas no otrās rindas, 1 ir atņemts. Tātad pēdējās Glenas parādīšanās pozīcija mūsu sarakstā ir 10.
- SUMPRODUCT tiek izmantots, lai nodrošinātu, ka jums nav jāizmanto Control + Shift + Enter, jo SUMPRODUCT var apstrādāt masīva formulas.
- Funkcija INDEX tagad tiek izmantota, lai atrastu pēdējā atbilstošā vārda datumu.
Atrodiet pēdējo notikumu - izmantojot funkciju LOOKUP
Šeit ir vēl viena formula, lai veiktu to pašu darbu:
= MEKLĒT (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Šī formula darbojas šādi.
- Uzmeklēšanas vērtība ir 2 (jūs redzēsit, kāpēc… turpiniet lasīt)
- Uzmeklēšanas diapazons ir 1/($ A $ 2: $ A $ 14 = $ D $ 3) - tas atgriež 1, kad tiek atrasts atbilstošais nosaukums, un kļūda, ja tā nav. Tātad jūs galu galā iegūstat masīvu. Piemēram, uzmeklēšanas vērtība ir Glen, masīvs būtu {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
- Trešais arguments ([result_vector]) ir diapazons, no kura tas dod rezultātu, kas šajā gadījumā ir datumi.
Šī formula darbojas tāpēc, ka LOOKUP funkcija izmanto aptuvenās atbilstības tehniku. Tas nozīmē, ka, ja tā var atrast precīzu atbilstošo vērtību, tā to atgriezīs, bet, ja tā nevarēs, tā skenēs visu masīvu līdz galam un atdos nākamo lielāko vērtību, kas ir zemāka par uzmeklēšanas vērtību.
Šajā gadījumā uzmeklēšanas vērtība ir 2, un mūsu masīvā mēs iegūsim tikai 1 vai kļūdas. Tātad tas skenē visu masīvu un atgriež pēdējā 1 pozīciju - kas ir pēdējā vārda atbilstošā vērtība.
Atrodiet pēdējo notikumu - izmantojot pielāgotu funkciju (VBA)
Ļaujiet man parādīt arī citu veidu, kā to izdarīt.
Mēs varam izveidot pielāgotu funkciju (sauktu arī par lietotāja definētu funkciju), izmantojot VBA.
Pielāgotas funkcijas izveides priekšrocība ir tā, ka to ir viegli lietot. Jums nav jāuztraucas par sarežģītas formulas izveidi katru reizi, jo lielākā daļa darba notiek VBA aizmugurē.
Esmu izveidojis vienkāršu formulu (kas līdzinās VLOOKUP formulai).
Lai izveidotu pielāgotu funkciju, VB redaktorā jābūt VBA kodam. Es jums došu kodu un soļus, kā pēc kāda laika to ievietot VB redaktorā, bet vispirms ļaujiet man parādīt, kā tas darbojas:
Šī ir formula, kas sniegs jums rezultātu:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Formulai ir trīs argumenti:
- Uzmeklēšanas vērtība (tas būtu nosaukums šūnā D3)
- Uzmeklēšanas diapazons (tas būtu diapazons ar nosaukumiem un datumiem - A2: B14)
- Kolonnas numurs (šī ir sleja, no kuras mēs vēlamies rezultātu)
Kad esat izveidojis formulu un ievietojis kodu VB redaktorā, varat to izmantot tāpat kā citas parastās Excel darblapas funkcijas.
Šeit ir formulas kods:
"Šis ir funkcijas kods, kas atrod pēdējo uzmeklēšanas vērtības gadījumu un atgriež atbilstošo vērtību no norādītās slejas" Kods, ko izveidojis Sumit Bansal (https://trumpexcel.com) Funkcija LastItemLookup (Lookupvalue As String, LookupRange As Diapazons, kolonnas numurs kā vesels skaitlis) Dim i tik ilgi, cik i = uzmeklēšanas diapazons. Kolonnas (1). Šūnas. Saskaitīt līdz 1. solim -1. Iziet no funkcijas Beigt, ja Nākamais i Beigu funkcija
Lai ievietotu šo kodu VB redaktorā, veiciet tālāk norādītās darbības.
- Dodieties uz cilni Izstrādātājs.
- Noklikšķiniet uz opcijas Visual Basic. Tas atvērs VB redaktoru aizmugurē.
- VB redaktora Project Explorer rūtī ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta, kurā vēlaties ievietot kodu. Ja neredzat Project Explorer, dodieties uz cilni Skats un noklikšķiniet uz Project Explorer.
- Dodieties uz Ievietot un noklikšķiniet uz moduļa. Tādējādi jūsu darbgrāmatai tiks ievietots moduļa objekts.
- Kopējiet un ielīmējiet kodu moduļa logā.
Tagad formula būtu pieejama visās darbgrāmatas darblapās.
Ņemiet vērā, ka jums ir jāsaglabā darbgrāmata .XLSM formātā, jo tajā ir makro. Turklāt, ja vēlaties, lai šī formula būtu pieejama visās jūsu izmantotajās darbgrāmatās, varat to saglabāt personīgajā makro darbgrāmatā vai izveidot no tās pievienojumprogrammu.
