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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка непустых ячеек (Формулы/Formulas)
Выборка непустых ячеек
kvlar Дата: Четверг, 22.10.2026, 16:56 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 75
Репутация: 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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 552
Репутация: 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
Группа: Админы
Ранг: Местный житель
Сообщений: 19649
Репутация: 4721 ±
Замечаний: ±

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
Группа: Админы
Ранг: Участник клуба
Сообщений: 2891
Репутация: 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
Группа: Пользователи
Ранг: Участник
Сообщений: 75
Репутация: 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
Группа: Админы
Ранг: Участник клуба
Сообщений: 2891
Репутация: 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
kvlar Дата: Пятница, 23.10.2026, 08:18 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 75
Репутация: 0 ±
Замечаний: 0% ±

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

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

Дома на свежем офисе работает, а на работе на 16 - нет.

Цитата Gustav, 22.10.2026 в 21:46, в сообщении № 8 ()
Мы с ИИ немножко посидели

На 16 офисе тоже не работает. На свежем посмотрю после обеда.
 
Ответить
Сообщение
Цитата kvlar, 22.10.2026 в 20:08, в сообщении № 6 ()
=ВЫБОР({1;2};"A"&ФИЛЬТР(СТРОКА(A1:A10);A1:A10<>"");ФИЛЬТР(A1:A10;A1:A10<>""))

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

Дома на свежем офисе работает, а на работе на 16 - нет.

Цитата Gustav, 22.10.2026 в 21:46, в сообщении № 8 ()
Мы с ИИ немножко посидели

На 16 офисе тоже не работает. На свежем посмотрю после обеда.
Автор - kvlar
Дата добавления - 23.10.2026 в 08:18
Gustav Дата: Пятница, 23.10.2026, 12:59 | Сообщение № 10
Группа: Админы
Ранг: Участник клуба
Сообщений: 2891
Репутация: 1224 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Цитата kvlar, 23.10.2026 в 08:18, в сообщении № 9 ()
На 16 офисе тоже не работает. На свежем посмотрю после обеда

Я на бесплатном 365 отлаживался, который в OneDrive доступен. Или в Гугл таблицы переходите - там у всех всегда один и тот же "офис" и всегда самый последний и бесплатный. Тем более, они совсем недавно общее кол-во ячеек в файле увеличили с 10 до 20 миллионов - красота! Рекомендую.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата kvlar, 23.10.2026 в 08:18, в сообщении № 9 ()
На 16 офисе тоже не работает. На свежем посмотрю после обеда

Я на бесплатном 365 отлаживался, который в OneDrive доступен. Или в Гугл таблицы переходите - там у всех всегда один и тот же "офис" и всегда самый последний и бесплатный. Тем более, они совсем недавно общее кол-во ячеек в файле увеличили с 10 до 20 миллионов - красота! Рекомендую.
Автор - Gustav
Дата добавления - 23.10.2026 в 12:59
kvlar Дата: Пятница, 23.10.2026, 14:53 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 75
Репутация: 0 ±
Замечаний: 0% ±

Это организация и там стоит 16й офис. Другого думаю не будет...
 
Ответить
СообщениеЭто организация и там стоит 16й офис. Другого думаю не будет...
Автор - kvlar
Дата добавления - 23.10.2026 в 14:53
Egyptian Дата: Пятница, 23.10.2026, 18:49 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 552
Репутация: 207 ±
Замечаний: 0% ±

Excel 2013/2016
Для старых офисов смотрите сообщение №2.
Формула для гиперссылки:
Код
=HYPERLINK("#"&ADDRESS(AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1));1;4);ADDRESS(AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1));1;4))
 
Ответить
СообщениеДля старых офисов смотрите сообщение №2.
Формула для гиперссылки:
Код
=HYPERLINK("#"&ADDRESS(AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1));1;4);ADDRESS(AGGREGATE(15;6;ROW($A$1:$A$10)/($A$1:$A$10<>"");ROW(A1));1;4))
Автор - Egyptian
Дата добавления - 23.10.2026 в 18:49
kvlar Дата: Суббота, 24.10.2026, 11:04 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 75
Репутация: 0 ±
Замечаний: 0% ±

Цитата Egyptian, 23.10.2026 в 18:49, в сообщении № 12 ()
Для старых офисов смотрите сообщение №2.

Результат такой получается #ЧИСЛО

Хорошо и наглядно работает эта формула:
=ВЫБОР({1;2};"H"&ФИЛЬТР(СТРОКА(H5:H999);H5:H999<>"");ФИЛЬТР(H5:H999;H5:H999<>""))
но как бы ее объединить с формулой гиперссылки
=ГИПЕРССЫЛКА("#"&АДРЕС(АГРЕГАТ(15;6;СТРОКА($A$1:$A$10)/($A$1:$A$10<>"");СТРОКА(A1));1;4); АДРЕС(АГРЕГАТ(15;6;СТРОКА($A$1:$A$10)/($A$1:$A$10<>"");СТРОКА(A1));1;4))
в столбце Н?
К сообщению приложен файл: 121.xlsx (9.5 Kb)
 
Ответить
Сообщение
Цитата Egyptian, 23.10.2026 в 18:49, в сообщении № 12 ()
Для старых офисов смотрите сообщение №2.

Результат такой получается #ЧИСЛО

Хорошо и наглядно работает эта формула:
=ВЫБОР({1;2};"H"&ФИЛЬТР(СТРОКА(H5:H999);H5:H999<>"");ФИЛЬТР(H5:H999;H5:H999<>""))
но как бы ее объединить с формулой гиперссылки
=ГИПЕРССЫЛКА("#"&АДРЕС(АГРЕГАТ(15;6;СТРОКА($A$1:$A$10)/($A$1:$A$10<>"");СТРОКА(A1));1;4); АДРЕС(АГРЕГАТ(15;6;СТРОКА($A$1:$A$10)/($A$1:$A$10<>"");СТРОКА(A1));1;4))
в столбце Н?
Автор - kvlar
Дата добавления - 24.10.2026 в 11:04
Egyptian Дата: Суббота, 24.10.2026, 12:29 | Сообщение № 14
Группа: Проверенные
Ранг: Ветеран
Сообщений: 552
Репутация: 207 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата kvlar, 24.10.2026 в 11:04, в сообщении № 13 ()
Результат такой получается #ЧИСЛО
Такая ошибка возникает, если в формуле или функции использовано недопустимое числовое значение а также неверные даты или форматы.
Покажите в файле, как вы ее вводили.
И еще. функция ФИЛЬТР() не может работать в 2016-ом офисе, я не понял вам совместимые формулы нужны или исключительно для новых офисов?
 
Ответить
Сообщение
Цитата kvlar, 24.10.2026 в 11:04, в сообщении № 13 ()
Результат такой получается #ЧИСЛО
Такая ошибка возникает, если в формуле или функции использовано недопустимое числовое значение а также неверные даты или форматы.
Покажите в файле, как вы ее вводили.
И еще. функция ФИЛЬТР() не может работать в 2016-ом офисе, я не понял вам совместимые формулы нужны или исключительно для новых офисов?
Автор - Egyptian
Дата добавления - 24.10.2026 в 12:29
kvlar Дата: Суббота, 24.10.2026, 14:04 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 75
Репутация: 0 ±
Замечаний: 0% ±

Цитата Egyptian, 24.10.2026 в 12:29, в сообщении № 14 ()
Покажите в файле, как вы ее вводили.

В файле желтые поля.
Зеленые рабочие. Вот с зеленых бы гиперссылки...

Цитата Egyptian, 24.10.2026 в 12:29, в сообщении № 14 ()
вам совместимые формулы нужны или исключительно для новых офисов?

Дома у меня 2024 офис.
На работе 2016 офис. Если я сделаю на 24-ом, то на 16 -ом будет работать? Если нет, то в приоритете команды для 16-го.
Мне нужно, чтобы то, что сделано, работало не 16-ом.
К сообщению приложен файл: 5712151.xlsx (9.7 Kb)


Сообщение отредактировал kvlar - Суббота, 24.10.2026, 14:08
 
Ответить
Сообщение
Цитата Egyptian, 24.10.2026 в 12:29, в сообщении № 14 ()
Покажите в файле, как вы ее вводили.

В файле желтые поля.
Зеленые рабочие. Вот с зеленых бы гиперссылки...

Цитата Egyptian, 24.10.2026 в 12:29, в сообщении № 14 ()
вам совместимые формулы нужны или исключительно для новых офисов?

Дома у меня 2024 офис.
На работе 2016 офис. Если я сделаю на 24-ом, то на 16 -ом будет работать? Если нет, то в приоритете команды для 16-го.
Мне нужно, чтобы то, что сделано, работало не 16-ом.
Автор - kvlar
Дата добавления - 24.10.2026 в 14:04
Egyptian Дата: Суббота, 24.10.2026, 14:18 | Сообщение № 16
Группа: Проверенные
Ранг: Ветеран
Сообщений: 552
Репутация: 207 ±
Замечаний: 0% ±

Excel 2013/2016
Проверил. Естественно не работало бы. Вы отсчет диапазона с пятой строки делали и последний аргумент тоже пятой строкой указали.
Варианты в файлах. Должно работать во всех офисах.
К сообщению приложен файл: 2465159.xlsx (9.8 Kb)
 
Ответить
СообщениеПроверил. Естественно не работало бы. Вы отсчет диапазона с пятой строки делали и последний аргумент тоже пятой строкой указали.
Варианты в файлах. Должно работать во всех офисах.
Автор - Egyptian
Дата добавления - 24.10.2026 в 14:18
kvlar Дата: Суббота, 24.10.2026, 14:29 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 75
Репутация: 0 ±
Замечаний: 0% ±

Цитата Egyptian, 24.10.2026 в 14:18, в сообщении № 16 ()
отсчет диапазона с пятой строки делали

Мне и нужно, чтобы первые четыре ячейки столбца Н не учитывались
 
Ответить
Сообщение
Цитата Egyptian, 24.10.2026 в 14:18, в сообщении № 16 ()
отсчет диапазона с пятой строки делали

Мне и нужно, чтобы первые четыре ячейки столбца Н не учитывались
Автор - kvlar
Дата добавления - 24.10.2026 в 14:29
kvlar Дата: Суббота, 24.10.2026, 14:42 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 75
Репутация: 0 ±
Замечаний: 0% ±

Цитата kvlar, 24.10.2026 в 11:04, в сообщении № 13 ()
=ВЫБОР({1;2};"H"&ФИЛЬТР(СТРОКА(H5:H999);H5:H999<>"");ФИЛЬТР(H5:H999;H5:H999<>""))

В этом варианте сразу выдается и ячейка и ее содержимое. Нельзя к этой формуле к ячейкам прикрутить гиперссылку?
 
Ответить
Сообщение
Цитата kvlar, 24.10.2026 в 11:04, в сообщении № 13 ()
=ВЫБОР({1;2};"H"&ФИЛЬТР(СТРОКА(H5:H999);H5:H999<>"");ФИЛЬТР(H5:H999;H5:H999<>""))

В этом варианте сразу выдается и ячейка и ее содержимое. Нельзя к этой формуле к ячейкам прикрутить гиперссылку?
Автор - kvlar
Дата добавления - 24.10.2026 в 14:42
Egyptian Дата: Суббота, 24.10.2026, 16:23 | Сообщение № 19
Группа: Проверенные
Ранг: Ветеран
Сообщений: 552
Репутация: 207 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата kvlar, 24.10.2026 в 14:29, в сообщении № 17 ()
Мне и нужно, чтобы первые четыре ячейки столбца Н не учитывались

В таком случае последний аргумент (отсчет если попроще) =ROW(H1) надо начинать с единицы.
Код
=ADDRESS(AGGREGATE(15;6;ROW($H$5:$H$999)/($H$5:$H$999<>"");ROW(H1));8;4)


Цитата kvlar, 24.10.2026 в 14:42, в сообщении № 18 ()
Нельзя к этой формуле к ячейкам прикрутить гиперссылку?

Именно так, как вам хочется, — нет, потому что, судя по всему, функция
Код
HYPERLINK()
вторым аргументом не принимает массивы, только одно значение.
 
Ответить
Сообщение
Цитата kvlar, 24.10.2026 в 14:29, в сообщении № 17 ()
Мне и нужно, чтобы первые четыре ячейки столбца Н не учитывались

В таком случае последний аргумент (отсчет если попроще) =ROW(H1) надо начинать с единицы.
Код
=ADDRESS(AGGREGATE(15;6;ROW($H$5:$H$999)/($H$5:$H$999<>"");ROW(H1));8;4)


Цитата kvlar, 24.10.2026 в 14:42, в сообщении № 18 ()
Нельзя к этой формуле к ячейкам прикрутить гиперссылку?

Именно так, как вам хочется, — нет, потому что, судя по всему, функция
Код
HYPERLINK()
вторым аргументом не принимает массивы, только одно значение.
Автор - Egyptian
Дата добавления - 24.10.2026 в 16:23
Gustav Дата: Суббота, 24.10.2026, 17:04 | Сообщение № 20
Группа: Админы
Ранг: Участник клуба
Сообщений: 2891
Репутация: 1224 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Цитата kvlar, 24.10.2026 в 14:42, в сообщении № 18 ()
=ВЫБОР({1;2};"H"&ФИЛЬТР(СТРОКА(H5:H999);H5:H999<>"");ФИЛЬТР(H5:H999;H5:H999<>""))

Именно в эту формулу гиперссылку можно прикрутить вот так:
Код
=ВЫБОР({1;2};ГИПЕРССЫЛКА("#H"&ФИЛЬТР(СТРОКА(H5:H999);H5:H999<>""));ФИЛЬТР(H5:H999;H5:H999<>""))

Но адреса в первом столбце будут при этом предворены символом #, типа такого:
[vba]
Код
#H6
#H9
#H12
[/vba]
И убрать этот начальный диез # никак не получится, потому что:
Цитата Egyptian, 24.10.2026 в 16:23, в сообщении № 19 ()
функция

HYPERLINK()
вторым аргументом не принимает массивы, только одно значение.

Вот-вот, именно поэтому! При этом символ # убрать, конечно, можно, но картина станет такой - во всех строках будет повторяться адрес первой ячейки, типа такого:
[vba]
Код
H6
H6
H6
[/vba], что по ходу еще хуже, чем с диезом...

Но если диез вас не будет сильно раздражать, то вариант с ним вполне рабочий.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата kvlar, 24.10.2026 в 14:42, в сообщении № 18 ()
=ВЫБОР({1;2};"H"&ФИЛЬТР(СТРОКА(H5:H999);H5:H999<>"");ФИЛЬТР(H5:H999;H5:H999<>""))

Именно в эту формулу гиперссылку можно прикрутить вот так:
Код
=ВЫБОР({1;2};ГИПЕРССЫЛКА("#H"&ФИЛЬТР(СТРОКА(H5:H999);H5:H999<>""));ФИЛЬТР(H5:H999;H5:H999<>""))

Но адреса в первом столбце будут при этом предворены символом #, типа такого:
[vba]
Код
#H6
#H9
#H12
[/vba]
И убрать этот начальный диез # никак не получится, потому что:
Цитата Egyptian, 24.10.2026 в 16:23, в сообщении № 19 ()
функция

HYPERLINK()
вторым аргументом не принимает массивы, только одно значение.

Вот-вот, именно поэтому! При этом символ # убрать, конечно, можно, но картина станет такой - во всех строках будет повторяться адрес первой ячейки, типа такого:
[vba]
Код
H6
H6
H6
[/vba], что по ходу еще хуже, чем с диезом...

Но если диез вас не будет сильно раздражать, то вариант с ним вполне рабочий.
Автор - Gustav
Дата добавления - 24.10.2026 в 17:04
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка непустых ячеек (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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