Как да намерите последното появяване на елемент в списък с помощта на формула на Excel

0 Акции

Резюме: в този урок ще научите как да използвате формула на Excel, за да намерите последното появяване на елемент в списък.

Наскоро работех по дневен ред на срещата.

Имам списък в Excel, който има списък с хора и датите, на които са били „председатели на срещи“.

Също така трябва да знам кога човек е бил последно „председател на срещата“ поради дублиране в списъка (което означава, че човек е бил председател на срещата няколко пъти).

Това е така, защото трябва да се уверя, че последният председател не е преназначен.

Затова реших да използвам магия на функциите на Excel, за да свърша работата.

По-долу е крайният резултат, мога да избера име от падащото меню и ми дава датата на последното появяване на това име в списъка.

Ако разбирате добре функциите на Excel, знаете, че няма функция на Excel, която може да направи това.Тук правим чудеса да се случват.

В този урок ще ви покажа три начина да направите това.

Съдържание

1 Намерете последното появяване - с помощта на функцията MAX
2 Намерете последното събитие - с помощта на функцията LOOKUP
3 Намерете последното появяване - с помощта на персонализирана функция (VBA)
4 О, здравей, радвам се да се запознаем.

Намерете последното появяване - с помощта на функцията MAX

Ето формулата на Excel, която ще върне последната стойност в списъка:

=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))

Ето как работи тази формула:

Функцията MAX се използва за намиране на номера на реда на последното съвпадащо име.Например, ако името е Glen, то ще върне 11, защото е на ред 11.Тъй като нашият списък започва от втория ред, 1 се изважда.Следователно, последното появяване на Glen е 10 в нашия списък.
SUMPRODUCT се използва, за да се гарантира, че не е нужно да използвате Control + Shift + Enter, защото SUMPRODUCT може да обработва формули за масиви.
Функцията INDEX вече се използва за намиране на датата на последното съвпадащо име.

Свързани въпроси Поправете - това не работи.Уверете се, че вашето bluetooth устройство все още е откриваемо и опитайте отново

Намерете последното събитие - с помощта на функцията LOOKUP

Ето още една формула, която върши същата работа:

=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 редактора.Ще ви дам кода и стъпките, за да го вкарате в редактора на VB по-късно, но нека първо ви покажа как работи:

Свързани въпроси Пълно архивиране на Outlook: Експортиране на поща като PST файл

Ето формулата, която ще ви даде резултата:

=LastItemLookup($D$3,$A$2:$B$14,2)

Формулата приема три параметъра:

Намерете стойността (това ще бъде името в клетка D3)
Намерете диапазона (това ще бъде диапазонът с име и дата – A2:B14)
номер на колона (това е колоната, за която искаме резултата)

След като създадете формулата и поставите кода в редактора на VB, можете да го използвате като всяка друга обикновена функция на работен лист на Excel.

Ето кода за формулата:

Функция LastItemLookup(Lookupvalue като низ, LookupRange като диапазон, ColumnNumber като цяло число) Dim i As Long For i = LookupRange.Columns(1).Cells.Count до 1 стъпка -1 Ако Lookupvalue = LookupRange.Cells(i, 1) Тогава LastItemLoo = LookupRange.Cells(i, ColumnNumber) Изход от функция Край, ако Следващ i Край на функция

Ето стъпките за поставяне на този код във VB редактора:

Отидете в раздела за програмисти.
Щракнете върху Опции на Visual Basic.Това ще отвори VB редактора в бекенда.
В панела Project Explorer на редактора на VB щракнете с десния бутон върху всеки обект в работната книга, където искате да вмъкнете код.Ако не виждате Project Explorer, отидете в раздела Изглед и щракнете върху Project Explorer.
Отидете на Вмъкване и щракнете върху Модули.Това ще вмъкне модулен обект за вашата работна книга.
Копирайте и поставете кода в прозореца на модула.

Свързани въпроси Как да спра синхронизирането на Google Photos?

Сега формулата ще бъде налична във всички листове на работната книга.

Имайте предвид, че ще трябва да запишете работната книга във формат .XLSM, тъй като съдържа макроси.Освен това, ако искате тази формула да е налична във всички работни книги, които използвате, можете да я запишете в личната си работна книга с макроси или да създадете добавка от нея.