Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Выборка непустых ячеек - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка непустых ячеек (Формулы/Formulas)
Выборка непустых ячеек
kvlar Дата: Четверг, 22.10.2026, 16:56 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 68
Репутация: 0 ±
Замечаний: 0% ±

Здравствуйте. Нужно получить выборку ячеек с названиями ячеек.
В столбце А есть пустые и непустые ячейки.
Формула: =ФИЛЬТР(А1:А999; А1:А999<>"")
выводит только значения непустых ячеек, а мне нужно еще и видеть в каких ячейках находятся эти значения.
Как и где выводится результат не важно, главное, чтобы было видно в какой ячейке какое значение.
К сообщению приложен файл: 131.xlsx (9.7 Kb)
 
Ответить
СообщениеЗдравствуйте. Нужно получить выборку ячеек с названиями ячеек.
В столбце А есть пустые и непустые ячейки.
Формула: =ФИЛЬТР(А1:А999; А1:А999<>"")
выводит только значения непустых ячеек, а мне нужно еще и видеть в каких ячейках находятся эти значения.
Как и где выводится результат не важно, главное, чтобы было видно в какой ячейке какое значение.
Автор - kvlar
Дата добавления - 22.10.2026 в 16:56
Egyptian Дата: Четверг, 22.10.2026, 18:39 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 548
Репутация: 207 ±
Замечаний: 0% ±

Excel 2013/2016
Для пустых (Можно и без индекса):
Код
=INDEX($A$1:$A$10;AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1)))


Для адресов:
Код
=INDEX(ADDRESS(ROW($A$1:$A$10);1;4);AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1)))

а также:
Код
=ADDRESS(AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1));1;4)

или проще:
Код
=CHAR(COLUMN(A$1)+64)&AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1))


Сообщение отредактировал Egyptian - Четверг, 22.10.2026, 18:42
 
Ответить
СообщениеДля пустых (Можно и без индекса):
Код
=INDEX($A$1:$A$10;AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1)))


Для адресов:
Код
=INDEX(ADDRESS(ROW($A$1:$A$10);1;4);AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1)))

а также:
Код
=ADDRESS(AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1));1;4)

или проще:
Код
=CHAR(COLUMN(A$1)+64)&AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1))
Автор - Egyptian
Дата добавления - 22.10.2026 в 18:39
Pelena Дата: Четверг, 22.10.2026, 18:46 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 19648
Репутация: 4720 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата kvlar, 22.10.2026 в 16:56, в сообщении № 1 ()
Формула: =ФИЛЬТР(А1:А999; А1:А999<>"")

для новых версий
Код
=ФИЛЬТР(ГСТОЛБИК(MAP(A2:A11;LAMBDA(x;ЯЧЕЙКА("адрес";x)));A2:A11);A2:A11>0)

или с доп. столбцом и Вашей формулой (в файле)
К сообщению приложен файл: 2565319.xlsx (10.1 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата kvlar, 22.10.2026 в 16:56, в сообщении № 1 ()
Формула: =ФИЛЬТР(А1:А999; А1:А999<>"")

для новых версий
Код
=ФИЛЬТР(ГСТОЛБИК(MAP(A2:A11;LAMBDA(x;ЯЧЕЙКА("адрес";x)));A2:A11);A2:A11>0)

или с доп. столбцом и Вашей формулой (в файле)
Автор - Pelena
Дата добавления - 22.10.2026 в 18:46
Gustav Дата: Четверг, 22.10.2026, 18:48 | Сообщение № 4
Группа: Админы
Ранг: Участник клуба
Сообщений: 2887
Репутация: 1224 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
В свежих версиях можно так. Вместо фильтрации только по одной колонке A:
[vba]
Код
=FILTER(A1:A999; A1:A999<>"")
[/vba]
фильтруйте по динамическому виртуальному массиву, состоящему из столбца номеров строк и собственно столбца A:
[vba]
Код
=FILTER(HSTACK(ROW(A1:A999); A1:A999); A1:A999<>"")
[/vba]
Формула отлажена в Гугл-таблице. Для Excel адаптируйте самостоятельно. Ну, и букву "A" в адреса ячеек можете добавить конкатенацией, если одного номера строки недостаточно.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВ свежих версиях можно так. Вместо фильтрации только по одной колонке A:
[vba]
Код
=FILTER(A1:A999; A1:A999<>"")
[/vba]
фильтруйте по динамическому виртуальному массиву, состоящему из столбца номеров строк и собственно столбца A:
[vba]
Код
=FILTER(HSTACK(ROW(A1:A999); A1:A999); A1:A999<>"")
[/vba]
Формула отлажена в Гугл-таблице. Для Excel адаптируйте самостоятельно. Ну, и букву "A" в адреса ячеек можете добавить конкатенацией, если одного номера строки недостаточно.
Автор - Gustav
Дата добавления - 22.10.2026 в 18:48
i691198 Дата: Четверг, 22.10.2026, 19:46 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 492
Репутация: 152 ±
Замечаний: 0% ±

2016
Для версий с динамическими массивами (2021+)
Код
=ВЫБОР({1;2};"A"&ФИЛЬТР(СТРОКА(A1:A10);A1:A10<>"");ФИЛЬТР(A1:A10;A1:A10<>""))

или более универсальная, если исходный диапазон не обязательно в первом столбце, но не правее 26 столбца
Код
=LET(Z;A1:A10;ВЫБОР({1;2};(СИМВОЛ(СТОЛБЕЦ(Z)+64))&ФИЛЬТР(СТРОКА(Z);Z<>"");ФИЛЬТР(Z;Z<>"")))


Сообщение отредактировал i691198 - Четверг, 22.10.2026, 19:49
 
Ответить
СообщениеДля версий с динамическими массивами (2021+)
Код
=ВЫБОР({1;2};"A"&ФИЛЬТР(СТРОКА(A1:A10);A1:A10<>"");ФИЛЬТР(A1:A10;A1:A10<>""))

или более универсальная, если исходный диапазон не обязательно в первом столбце, но не правее 26 столбца
Код
=LET(Z;A1:A10;ВЫБОР({1;2};(СИМВОЛ(СТОЛБЕЦ(Z)+64))&ФИЛЬТР(СТРОКА(Z);Z<>"");ФИЛЬТР(Z;Z<>"")))
Автор - i691198
Дата добавления - 22.10.2026 в 19:46
kvlar Дата: Четверг, 22.10.2026, 20:08 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 68
Репутация: 0 ±
Замечаний: 0% ±

Цитата i691198, 22.10.2026 в 19:46, в сообщении № 5 ()
=ВЫБОР({1;2};"A"&ФИЛЬТР(СТРОКА(A1:A10);A1:A10<>"");ФИЛЬТР(A1:A10;A1:A10<>""))

Спасибо. Более чем подходит.

А можно усложнить немного? Можно добавить быстрый переход на эту ячейку? В ответе появилась ячейка А10, я нажимаю на нее и оказываюсь в А10?


Сообщение отредактировал kvlar - Четверг, 22.10.2026, 20:18
 
Ответить
Сообщение
Цитата i691198, 22.10.2026 в 19:46, в сообщении № 5 ()
=ВЫБОР({1;2};"A"&ФИЛЬТР(СТРОКА(A1:A10);A1:A10<>"");ФИЛЬТР(A1:A10;A1:A10<>""))

Спасибо. Более чем подходит.

А можно усложнить немного? Можно добавить быстрый переход на эту ячейку? В ответе появилась ячейка А10, я нажимаю на нее и оказываюсь в А10?
Автор - kvlar
Дата добавления - 22.10.2026 в 20:08
i691198 Дата: Четверг, 22.10.2026, 20:39 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 492
Репутация: 152 ±
Замечаний: 0% ±

2016
Цитата kvlar, 22.10.2026 в 20:08, в сообщении № 6 ()
Можно добавить быстрый переход на эту ячейку?

Можно макросом, например так
[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("C1:C" & Cells(1, 1).End(xlDown).Row)) Is Nothing Then Range(Target.Value).Select
End Sub
[/vba]
Макрос нужно вставить в модуль листа.
Если в исходном диапазоне много строк и они выходят за границу экрана, то можно добавить прокрутку экрана. В этом случае вторую строку можно записать так
[vba]
Код
If Not Intersect(Target, Range("C1:C" & Cells(1, 1).End(xlDown).Row)) Is Nothing Then Application.Goto Range(Target.Value), True
[/vba]


Сообщение отредактировал i691198 - Четверг, 22.10.2026, 20:48
 
Ответить
Сообщение
Цитата kvlar, 22.10.2026 в 20:08, в сообщении № 6 ()
Можно добавить быстрый переход на эту ячейку?

Можно макросом, например так
[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("C1:C" & Cells(1, 1).End(xlDown).Row)) Is Nothing Then Range(Target.Value).Select
End Sub
[/vba]
Макрос нужно вставить в модуль листа.
Если в исходном диапазоне много строк и они выходят за границу экрана, то можно добавить прокрутку экрана. В этом случае вторую строку можно записать так
[vba]
Код
If Not Intersect(Target, Range("C1:C" & Cells(1, 1).End(xlDown).Row)) Is Nothing Then Application.Goto Range(Target.Value), True
[/vba]
Автор - i691198
Дата добавления - 22.10.2026 в 20:39
Gustav Дата: Четверг, 22.10.2026, 21:46 | Сообщение № 8
Группа: Админы
Ранг: Участник клуба
Сообщений: 2887
Репутация: 1224 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Мы с ИИ немножко посидели и набросали формулу с кликабельными гиперссылками в первом столбце (без макросов):
[vba]
Код
=LET(
  исходный; A1:A999;
  значения; ФИЛЬТР(исходный; исходный<>"");
  строки;   ФИЛЬТР(СТРОКА(исходный); исходный<>"");
  столбцы;  ФИЛЬТР(ЕСЛИ(исходный<>""; СТОЛБЕЦ(исходный)); исходный<>"");
  адреса;   АДРЕС(строки; столбцы; 4);
  ссылки;   MAP(адреса; LAMBDA(адр; ГИПЕРССЫЛКА("#" & адр; "Перейти к " & адр)));

  ГСТОЛБИК(ссылки; значения)
)
[/vba]

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеМы с ИИ немножко посидели и набросали формулу с кликабельными гиперссылками в первом столбце (без макросов):
[vba]
Код
=LET(
  исходный; A1:A999;
  значения; ФИЛЬТР(исходный; исходный<>"");
  строки;   ФИЛЬТР(СТРОКА(исходный); исходный<>"");
  столбцы;  ФИЛЬТР(ЕСЛИ(исходный<>""; СТОЛБЕЦ(исходный)); исходный<>"");
  адреса;   АДРЕС(строки; столбцы; 4);
  ссылки;   MAP(адреса; LAMBDA(адр; ГИПЕРССЫЛКА("#" & адр; "Перейти к " & адр)));

  ГСТОЛБИК(ссылки; значения)
)
[/vba]
Автор - Gustav
Дата добавления - 22.10.2026 в 21:46
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка непустых ячеек (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2026 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!