В този урок ще научите как да намерите последното появяване на елемент в списък с помощта на формули на Excel.
Наскоро работих по определяне на дневния ред за среща.
Имах списък в Excel, където имах списък с хора и датите, на които те действаха като „председател на срещата“.
Тъй като в списъка имаше повторение (което означава, че човек е бил на заседание председател няколко пъти), аз също трябваше да знам кога за последен път човек е действал като „председател на срещата“.
Това беше така, защото трябваше да гарантирам, че някой, който наскоро е председателствал, няма да бъде назначен отново.
Затова реших да използвам някаква магия на функцията на Excel, за да направя това.
По-долу е крайният резултат, при който мога да избера име от падащото меню и той ми дава датата на последното появяване на това име в списъка.
Ако разбирате добре функциите на Excel, ще знаете, че няма една функция на Excel, която да може да направи това.
Но вие сте в секцията Formula Hack и тук ние правим магията да се случи.
В този урок ще ви покажа три начина да направите това.
Намерете последното събитие - Използване на функцията MAX
Заслуга на тази техника има статия на MVP на Excel Чарли Кайд.
Ето формулата на Excel, която ще върне последната стойност от списъка:
= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14))*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Ето как работи тази формула:
- Функцията MAX се използва за намиране на номера на ред на последното съвпадащо име. Например, ако името е Glen, то ще върне 11, тъй като е в 11 -ия ред. Тъй като нашият списък започва от втория ред нататък, 1 е изваден. Така че позицията на последното появяване на Глен е 10 в нашия списък.
- SUMPRODUCT се използва, за да се гарантира, че не е нужно да използвате Control + Shift + Enter, тъй като SUMPRODUCT може да обработва формули на масиви.
- Функцията INDEX вече се използва за намиране на датата за последното съвпадащо име.
Намерете последното събитие - Използване на функцията LOOKUP
Ето още една формула за извършване на същата работа:
= ПОГЛЕД (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Ето как работи тази формула:
- Стойността за търсене е 2 (ще видите защо … продължете да четете)
- Диапазонът за търсене е 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Това връща 1, когато намери съвпадащото име и грешка, когато не го направи. Така че в крайна сметка получавате масив. Например, от стойността за търсене е Glen, масивът ще бъде {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
- Третият аргумент ([result_vector]) е диапазонът, от който той дава резултата, които в този случай са дати.
Причината тази формула да работи е, че функцията LOOKUP използва техниката на приблизително съвпадение. Това означава, че ако може да намери точната съвпадаща стойност, ще върне това, но ако не може, ще сканира целия масив до края и ще върне следващата най -голяма стойност, която е по -ниска от стойността за търсене.
В този случай стойността за търсене е 2 и в нашия масив ще получим само 1 или грешки. Така той сканира целия масив и връща позицията на последното 1 - което е последната съвпадаща стойност на името.
Намерете последното събитие - Използване на персонализирана функция (VBA)
Нека ви покажа и друг начин да направите това.
Можем да създадем персонализирана функция (наричана още потребителска функция), използвайки VBA.
Ползата от създаването на персонализирана функция е, че е лесна за използване. Не е нужно да се притеснявате за създаването на сложна формула всеки път, тъй като по -голямата част от работата се извършва във VBA бекенда.
Създадох проста формула (която много прилича на формулата VLOOKUP).
За да създадете персонализирана функция, трябва да имате VBA кода във VB Editor. Ще ви дам кода и стъпките, за да го поставите във VB Editor след известно време, но първо ще ви покажа как работи:
Това е формулата, която ще ви даде резултат:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Формулата приема три аргумента:
- Стойност за търсене (това би било името в клетка D3)
- Обхват на търсене (това би бил диапазонът с имената и датите - A2: B14)
- Номер на колона (това е колоната, от която искаме резултата)
След като сте създали формулата и сте поставили кода във VB Editor, можете да я използвате, както всички други обикновени функции на работен лист на Excel.
Ето кода за формулата:
„Това е код за функция, която намира последното появяване на стойност за търсене и връща съответната стойност от посочената колона“ Код, създаден от Sumit Bansal (https://trumpexcel.com) Функция LastItemLookup (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns (1) .Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Тогава LastItemLookup = LookupRange.Cells (i, ColumnNumber) Изход от функцията Край Ако Следващ i Прекратяване на функцията
Ето стъпките за поставяне на този код във VB Editor:
- Отидете в раздела Разработчик.
- Щракнете върху опцията Visual Basic. Това ще отвори VB редактора в задната част.
- В прозореца Project Explorer в редактора на VB щракнете с десния бутон върху всеки обект за работната книга, в който искате да вмъкнете кода. Ако не виждате Project Explorer, отидете в раздела View и щракнете върху Project Explorer.
- Отидете на Вмъкване и щракнете върху Модул. Това ще вмъкне обект на модул за вашата работна книга.
- Копирайте и поставете кода в прозореца на модула.
Сега формулата ще бъде достъпна във всички работни листове на работната книга.
Имайте предвид, че трябва да запишете работната книга във формат .XLSM, тъй като в нея има макрос. Също така, ако искате тази формула да е налична във всички работни книги, които използвате, можете или да я запишете в личната работна книга за макроси или да създадете добавка от нея.
