Перейти к основному содержанию

Как найти и получить значение первой видимой ячейки после фильтрации в Excel?

В некоторых случаях может потребоваться получить значение первой видимой ячейки в отфильтрованном списке. Как этого добиться? Эта статья покажет вам способы ее решения.

Найти и получить значение первой видимой ячейки после фильтрации с помощью формулы массива
Найти и получить значение первой видимой ячейки после фильтрации с помощью VBA

Найти и получить значение первой видимой ячейки после фильтрации с помощью формулы массива

Вы можете применить следующую формулу массива, чтобы получить значение первой видимой ячейки в отфильтрованном списке. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку для размещения первого видимого значения, введите в нее формулу ниже, а затем нажмите Ctrl + Shift + Enter ключи одновременно.

=INDEX(C2:C19,MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C19)-ROW(C2),0)),ROW(C2:C19)-ROW(C2)+1)))

Внимание: В формуле C2: C19 - это отфильтрованный список, который вы хотите вернуть для первого видимого значения ячейки, C2 - это первая ячейка списка. Вы можете изменить их по своему усмотрению.

Затем вы можете увидеть значение первой видимой ячейки в отфильтрованном списке, заполненном в выбранной ячейке, как показано выше.

Найти и получить значение первой видимой ячейки после фильтрации с помощью VBA

Помимо использования приведенной выше формулы массива для получения значения первой видимой ячейки в отфильтрованном списке, вы можете запустить приведенный ниже сценарий VBA, чтобы быстро вернуть первую видимую ячейку отфильтрованного списка. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку, например E8, для размещения первого видимого значения ячейки отфильтрованного списка.

2. Нажмите другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.

3. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модули. Затем скопируйте и вставьте приведенный ниже код VBA в окно модуля.

Код VBA: найти и получить значение первой видимой ячейки после фильтрации в Excel

Sub FirstVisibleCell()
   With Worksheets("Sheet1").AutoFilter.Range
       ActiveCell.Value2 = Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
End Sub

Внимание: В коде Sheet1 - это имя листа, который содержит отфильтрованный список. «C» - это имя столбца, из которого вы хотите получить первое видимое значение. Вы можете изменить их по своему усмотрению.

3. нажмите F5 нажмите клавишу для запуска кода, то первая видимая ячейка отфильтрованного списка, которая находится в столбце C, будет немедленно заполнена в ячейку E8.

Лучшие инструменты для офисной работы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup  |   VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар   |  Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки   |  Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов:  12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...

Описание

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (18)
No ratings yet. Be the first to rate!
Bir Dost
Bir Dost
This comment was minimized by the moderator on the site
Thanks a lot, that formula INDEX(C2:C19,MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C19)-ROW(C2),0)),ROW(C2:C19)-ROW(C2)+1))) literally save my soul.
Even though I still do not get how the formula works, it does provide the return that I would like to have.

Regards,
Vedat
Reply
0
0
Someonewhoneededthisinfo
Someonewhoneededthisinfo
This comment was minimized by the moderator on the site
Thank you!
Reply
0
0
nh
nh
This comment was minimized by the moderator on the site
In the following VBA code, can you please explain what does SpecialCells(xlCellTypeVisible)(1) mean? What is (1) used for after SpecialCells(xlCellTypeVisible)?
ActiveCell.Value2 = Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
Reply
Report
0
0
andrew
andrew
This comment was minimized by the moderator on the site
Can you help me write this formula in google sheet with arrayformula ()

{=INDEX(C2:C19,MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C19)-ROW(C2),0)),ROW(C2:C19)-ROW(C2)+1)))}
Reply
0
0
Chricke
Chricke
This comment was minimized by the moderator on the site
So for me the answer above simply does't work that well. I have a workbook with over 23k rows and is something I've been working to optimize over the years. My solution came to me this morning in yet another spark of inspiration.
Assuming that you are using a TABLE to get the data you want do this:One row (I named it "Rownmbr") is dedicated to: =CELL([@[YOURFIRSTROW]])
Outside the table you make a formula somewhere:=INDIRECT(CONCATENATE("A";SUBTOTAL(105;Table1[Rownmbr])))
You simply replace the "A" with wherever you want to get the data from. For me this works great, and it's not a heavy formula to use that will bog down the document further.
Reply
0
0
prestonh
prestonh     Chricke
This comment was minimized by the moderator on the site
This solution worked perfectly for me, since I was using a table. The instructions were hard to follow, so hopefully this explanation will be a bit clearer.
Say you have an excel table named "Table_Name."  First, create a new column in your table and name it "RowNumber". 
In that new column, enter the formula "=ROW([@RowNumber])" This will then populate the new column with
In the cell where you want to display the first visible value, enter the formula "=INDIRECT("A"&SUBTOTAL(105,Table_Name[RowNumber]))"  The "A" is the letter of the column that holds the value you want to return.
The SUBTOTAL(105,...) function returns the minimum value of all the visible rows in a given range, which in the case above will return the row number of the first visible row.
The INDIRECT function returns the value of the cell for a given cell address. This is why you need the first part of the formula to contain the column letter of the column you want to display. The second part of the formula will return the row number.


Reply
Report
0
0
Kristine
Kristine
This comment was minimized by the moderator on the site
This formula works, but be aware that if you have blank cells, the formula can malfunction and select the first value in the entire table, not just in the filtered results. The workaround I found was to replace blank cells with NULL or a space.
Reply
0
0
Spock1956
Spock1956     Kristine
This comment was minimized by the moderator on the site
First I had blanks.... then all cells were filled but still having the #VALUE response. Hopefully this formula works on text as well...! Thanks for the advise anyway ;-)
Reply
Report
0
0
Hcs
Hcs
This comment was minimized by the moderator on the site
It works. Thanks so much...
Reply
0
0
Javane
Javane
This comment was minimized by the moderator on the site
Thanks a million🙏🌺
Reply
Report
0
0
Javane
Javane
This comment was minimized by the moderator on the site
‌wonderful🌺 Thanks a million🙏
Reply
0
0
Bub
Bub
This comment was minimized by the moderator on the site
I keep getting a Run-time error '91' Object variable or With block variable not set. My worksheet is Sheet1 titled AllBrands. This is my formula :

Sub FirstVisibleCell()
With Worksheets("ALLBRANDS").AutoFilter.Range
ActiveCell.Value2 = Range("H" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
End With

End Sub

Any help will be appreciated!
Reply
0
0
Spock1956
Spock1956     Bub
This comment was minimized by the moderator on the site
Unfortunately - neither the formula - that gives me a #VALUE error nor the VBA works for me giving me a run-time error '1004'
Too bad as I have Office 2019 and I was expecting one of these features to work when filtering specific cells... :-(
Reply
Report
0
0
crystal
crystal     Bub
This comment was minimized by the moderator on the site
Hi Bub,
The code works well in my case. Which Excel version are you using?
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations