ВАЖНЫЕ ОБЪЯВЛЕНИЯ:

1. Открыт СБОР СРЕДСТВ на продление работы нашего сайта - см. в разделе "Работа форума и сайта"

2. О невозможности скачивания некоторых файлов, выложенных с 1 по 10 ноября 2026 (ОШИБКА 404) - см. здесь
Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

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

  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин, _Boroda_, DrMini  
Выборка непустых ячеек
kvlar Дата: Суббота, 24.10.2026, 17:48 | Сообщение № 21
Группа: Пользователи
Ранг: Участник
Сообщений: 77
Репутация: 0 ±
Замечаний: 0% ±

Но если диез вас не будет сильно раздражать

Не раздражает абсолютно, но все ссылки все равно ведут в одно место :( , на адрес первой ячейки.

А без # пишет, не удается открыть указанный файл.
К сообщению приложен файл: 0704737.xlsx (9.7 Kb)


Сообщение отредактировал kvlar - Суббота, 24.10.2026, 17:53
 
Ответить
Сообщение
Но если диез вас не будет сильно раздражать

Не раздражает абсолютно, но все ссылки все равно ведут в одно место :( , на адрес первой ячейки.

А без # пишет, не удается открыть указанный файл.

Автор - kvlar
Дата добавления - 24.10.2026 в 17:48
Gustav Дата: Суббота, 24.10.2026, 21:02 | Сообщение № 22
Группа: Админы
Ранг: Участник клуба
Сообщений: 2912
Репутация: 1226 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Ну, не знаю... Я загрузил ваш файл в свой OneDrive, открыл там, навел мышку на значение #H12 в ячейке O7, чуть подождал, под ячейкой всплыла "подсказка" со ссылкой "Лист1!H12" - щелкнул по ней и рамка табличного курсора прыгнула на ячейку H12



Мышку надо наводить на само значение (сам текст в начале ячейки, т.е. на левом краю). Если навести на пустое место в правой части ячейки, то "подсказка" с гиперссылкой, к сожалению, не появляется. И, кстати, есть же еще клавиатура! Можно установить рамку табличного курсора в ячейку O7 и нажать комбинацию Ctrl+Enter - произойдёт немедленный прыжок в H12.

Мышкой можно также переходить, не дожидаясь "подсказки": предварительно зажимаете Ctrl и подводите мышку к тексту #H12 - до тех пор, пока "крестик" мыши не превратится в "ладошку". И как только стал "ладошкой" - левый щелчок (не отпуская Ctrl).
К сообщению приложен файл: 1524956.png (9.9 Kb)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеНу, не знаю... Я загрузил ваш файл в свой OneDrive, открыл там, навел мышку на значение #H12 в ячейке O7, чуть подождал, под ячейкой всплыла "подсказка" со ссылкой "Лист1!H12" - щелкнул по ней и рамка табличного курсора прыгнула на ячейку H12



Мышку надо наводить на само значение (сам текст в начале ячейки, т.е. на левом краю). Если навести на пустое место в правой части ячейки, то "подсказка" с гиперссылкой, к сожалению, не появляется. И, кстати, есть же еще клавиатура! Можно установить рамку табличного курсора в ячейку O7 и нажать комбинацию Ctrl+Enter - произойдёт немедленный прыжок в H12.

Мышкой можно также переходить, не дожидаясь "подсказки": предварительно зажимаете Ctrl и подводите мышку к тексту #H12 - до тех пор, пока "крестик" мыши не превратится в "ладошку". И как только стал "ладошкой" - левый щелчок (не отпуская Ctrl).

Автор - Gustav
Дата добавления - 24.10.2026 в 21:02
cmivadwot Дата: Суббота, 24.10.2026, 21:25 | Сообщение № 23
Группа: Проверенные
Ранг: Ветеран
Сообщений: 652
Репутация: 150 ±
Замечаний: 0% ±

365
kvlar, Добрый вечер. вроде работает)))
К сообщению приложен файл: vaniant.xlsx (10.3 Kb)
 
Ответить
Сообщениеkvlar, Добрый вечер. вроде работает)))

Автор - cmivadwot
Дата добавления - 24.10.2026 в 21:25
cmivadwot Дата: Суббота, 24.10.2026, 21:33 | Сообщение № 24
Группа: Проверенные
Ранг: Ветеран
Сообщений: 652
Репутация: 150 ±
Замечаний: 0% ±

365
Gustav, Все ссылки на H6, в этом файле 0704737.xlsx (9.7 Kb) отправляют
К сообщению приложен файл: 1689810.png (97.5 Kb)


Сообщение отредактировал cmivadwot - Суббота, 24.10.2026, 21:40
 
Ответить
СообщениеGustav, Все ссылки на H6, в этом файле 0704737.xlsx (9.7 Kb) отправляют

Автор - cmivadwot
Дата добавления - 24.10.2026 в 21:33
Gustav Дата: Суббота, 24.10.2026, 22:12 | Сообщение № 25
Группа: Админы
Ранг: Участник клуба
Сообщений: 2912
Репутация: 1226 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Все ссылки на H6, в этом файле 0704737.xlsx (9.7 Kb) отправляют

Да, действительно. Получается результат напрямую опять зависит от версии Excel (ох, уж этот Мелкософт). Я в своей 2019 на компе синтезировал пример - формулу массива, дающую такой же результат "все H6":
Код
=ГИПЕРССЫЛКА({"#H6":"#H8":"#H12":"#H17"})

Ну и попутно задал вопрос ИИ, чтобы подробно разобраться.

Я: почему формула массива =ГИПЕРССЫЛКА({"#H6":"#H8":"#H12":"#H17"}) дает четыре гиперссылки на одну и ту же ячейку H6 ?

ИИ: Вы абсолютно правы в своем наблюдении — на бумаге эта формула должна создавать массив из четырех разных ссылок, но на деле все они ведут на ячейку H6.
Причина этого кроется во внутреннем устройстве Excel и является одним из старейших его ограничений: функция ГИПЕРССЫЛКА (HYPERLINK) технически не поддерживает работу с массивами в качестве первого аргумента (адреса ссылки).

Вот почему это происходит на уровне логики Excel:
1. Игнорирование матрицы: Когда вы передаете массив констант {"#H6":"#H8":"#H12":"#H17"} (или через точку с запятой ;) внутрь функции ГИПЕРССЫЛКА, Excel по своему внутреннему алгоритму не умеет циклически перебирать эти элементы.
2. Правило первого элемента: Столкнувшись с массивом там, где она ожидает увидеть одну строго определенную текстовую строку, функция ГИПЕРССЫЛКА просто берет самый первый элемент из этого массива (в вашем случае "#H6").
3. Размножение результата: Из-за того, что формула введена как формула массива (или находится в версии с динамическими массивами), Excel понимает, что нужно заполнить четыре ячейки. Он берет результат работы функции (которая обработала только "#H6") и копирует этот единственный результат во все четыре ячейки. В итоге визуально вы видите четыре ячейки, но внутри каждой сидит ссылка, смотрящая на H6.

Как заставить это работать?

Поскольку ГИПЕРССЫЛКА категорически отказывается принимать массивы «напрямую», её нужно заставить обрабатывать элементы поочередно.

Способ 1. Для современных Excel (365 / 2024)
Используйте функцию-переборщик MAP. Она берет массив и скармливает функции ГИПЕРССЫЛКА каждый адрес строго по одному:

Код
=MAP({"H6";"H8";"H12";"H17"}; LAMBDA(cell; HYPERLINK("#"&cell; cell)))


Способ 2. Для классических версий Excel (2013 / 2016 / 2019)
Если динамических массивов нет, то передавать фигурные скобки внутрь одной ячейки бесполезно. Вместо этого адреса пишутся в обычный вертикальный диапазон ячеек (например, в ячейки A1:A4 вписать H6, H8, H12, H17), а в соседнем столбце пишется простая протягиваемая формула:

Код
=ГИПЕРССЫЛКА("#"&A1; A1)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Все ссылки на H6, в этом файле 0704737.xlsx (9.7 Kb) отправляют

Да, действительно. Получается результат напрямую опять зависит от версии Excel (ох, уж этот Мелкософт). Я в своей 2019 на компе синтезировал пример - формулу массива, дающую такой же результат "все H6":
Код
=ГИПЕРССЫЛКА({"#H6":"#H8":"#H12":"#H17"})

Ну и попутно задал вопрос ИИ, чтобы подробно разобраться.

Я: почему формула массива =ГИПЕРССЫЛКА({"#H6":"#H8":"#H12":"#H17"}) дает четыре гиперссылки на одну и ту же ячейку H6 ?

ИИ: Вы абсолютно правы в своем наблюдении — на бумаге эта формула должна создавать массив из четырех разных ссылок, но на деле все они ведут на ячейку H6.
Причина этого кроется во внутреннем устройстве Excel и является одним из старейших его ограничений: функция ГИПЕРССЫЛКА (HYPERLINK) технически не поддерживает работу с массивами в качестве первого аргумента (адреса ссылки).

Вот почему это происходит на уровне логики Excel:
1. Игнорирование матрицы: Когда вы передаете массив констант {"#H6":"#H8":"#H12":"#H17"} (или через точку с запятой ;) внутрь функции ГИПЕРССЫЛКА, Excel по своему внутреннему алгоритму не умеет циклически перебирать эти элементы.
2. Правило первого элемента: Столкнувшись с массивом там, где она ожидает увидеть одну строго определенную текстовую строку, функция ГИПЕРССЫЛКА просто берет самый первый элемент из этого массива (в вашем случае "#H6").
3. Размножение результата: Из-за того, что формула введена как формула массива (или находится в версии с динамическими массивами), Excel понимает, что нужно заполнить четыре ячейки. Он берет результат работы функции (которая обработала только "#H6") и копирует этот единственный результат во все четыре ячейки. В итоге визуально вы видите четыре ячейки, но внутри каждой сидит ссылка, смотрящая на H6.

Как заставить это работать?

Поскольку ГИПЕРССЫЛКА категорически отказывается принимать массивы «напрямую», её нужно заставить обрабатывать элементы поочередно.

Способ 1. Для современных Excel (365 / 2024)
Используйте функцию-переборщик MAP. Она берет массив и скармливает функции ГИПЕРССЫЛКА каждый адрес строго по одному:

Код
=MAP({"H6";"H8";"H12";"H17"}; LAMBDA(cell; HYPERLINK("#"&cell; cell)))


Способ 2. Для классических версий Excel (2013 / 2016 / 2019)
Если динамических массивов нет, то передавать фигурные скобки внутрь одной ячейки бесполезно. Вместо этого адреса пишутся в обычный вертикальный диапазон ячеек (например, в ячейки A1:A4 вписать H6, H8, H12, H17), а в соседнем столбце пишется простая протягиваемая формула:

Код
=ГИПЕРССЫЛКА("#"&A1; A1)

Автор - Gustav
Дата добавления - 24.10.2026 в 22:12
kvlar Дата: Воскресенье, 25.10.2026, 08:19 | Сообщение № 26
Группа: Пользователи
Ранг: Участник
Сообщений: 77
Репутация: 0 ±
Замечаний: 0% ±

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

не заработал.
Работает вариант от cmivadwot из двух формул протягиванием.
Код
=ЕСЛИОШИБКА(ГИПЕРССЫЛКА("#H"&АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)/($H$5:$H$999<>"");СТРОКА(H1));"H"&АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)/($H$5:$H$999<>"");СТРОКА(H1)));"")

Код
=ЕСЛИОШИБКА(ИНДЕКС($H$5:$H$999;АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)-4/($H$5:$H$999<>"");СТРОКА(H1)));"")

как вариант отлично. Две хуже чем одна, но лучше, чем ничего. :)))))


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

не заработал.
Работает вариант от cmivadwot из двух формул протягиванием.
Код
=ЕСЛИОШИБКА(ГИПЕРССЫЛКА("#H"&АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)/($H$5:$H$999<>"");СТРОКА(H1));"H"&АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)/($H$5:$H$999<>"");СТРОКА(H1)));"")

Код
=ЕСЛИОШИБКА(ИНДЕКС($H$5:$H$999;АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)-4/($H$5:$H$999<>"");СТРОКА(H1)));"")

как вариант отлично. Две хуже чем одна, но лучше, чем ничего. :)))))

Автор - kvlar
Дата добавления - 25.10.2026 в 08:19
i691198 Дата: Воскресенье, 25.10.2026, 09:46 | Сообщение № 27
Группа: Проверенные
Ранг: Ветеран
Сообщений: 503
Репутация: 152 ±
Замечаний: 0% ±

2016
Две хуже чем одна

Можно сцепить две формулы от cmivadwot, в одну функцией ВЫБОР
Код
=ЕСЛИОШИБКА(ВЫБОР({1;2};ГИПЕРССЫЛКА("#H"&АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)/($H$5:$H$999<>"");СТРОКА(H1));"H"&АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)/($H$5:$H$999<>"");СТРОКА(H1)));ИНДЕКС($H$5:$H$999;АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)-4/($H$5:$H$999<>"");СТРОКА(H1))));"")

Работать будет в версиях поддерживающих динамические массивы. И нужно будет протягивать вниз.
 
Ответить
Сообщение
Две хуже чем одна

Можно сцепить две формулы от cmivadwot, в одну функцией ВЫБОР
Код
=ЕСЛИОШИБКА(ВЫБОР({1;2};ГИПЕРССЫЛКА("#H"&АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)/($H$5:$H$999<>"");СТРОКА(H1));"H"&АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)/($H$5:$H$999<>"");СТРОКА(H1)));ИНДЕКС($H$5:$H$999;АГРЕГАТ(15;6;СТРОКА($H$5:$H$999)-4/($H$5:$H$999<>"");СТРОКА(H1))));"")

Работать будет в версиях поддерживающих динамические массивы. И нужно будет протягивать вниз.

Автор - i691198
Дата добавления - 25.10.2026 в 09:46
Pelena Дата: Воскресенье, 25.10.2026, 10:30 | Сообщение № 28
Группа: Админы
Ранг: Местный житель
Сообщений: 19653
Репутация: 4721 ±
Замечаний: ±

Excel 365 & Mac Excel
kvlar, оформите Ваши формулы тегами в соответствии с Правилами форума.
Для этого в режиме редактирования поста выделите формулу и нажмите кнопку fx на панели инструментов


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеkvlar, оформите Ваши формулы тегами в соответствии с Правилами форума.
Для этого в режиме редактирования поста выделите формулу и нажмите кнопку fx на панели инструментов

Автор - Pelena
Дата добавления - 25.10.2026 в 10:30
i691198 Дата: Воскресенье, 25.10.2026, 11:10 | Сообщение № 29
Группа: Проверенные
Ранг: Ветеран
Сообщений: 503
Репутация: 152 ±
Замечаний: 0% ±

2016
Для версий имеющих функцию LAMBDA можно так -
Код
=ГСТОЛБИК(BYROW("H"&ФИЛЬТР(СТРОКА(H6:H999);H6:H999<>"");LAMBDA(Z;ГИПЕРССЫЛКА("#"&Z;Z)));ФИЛЬТР(H6:H999;H6:H999<>""))
 
Ответить
СообщениеДля версий имеющих функцию LAMBDA можно так -
Код
=ГСТОЛБИК(BYROW("H"&ФИЛЬТР(СТРОКА(H6:H999);H6:H999<>"");LAMBDA(Z;ГИПЕРССЫЛКА("#"&Z;Z)));ФИЛЬТР(H6:H999;H6:H999<>""))

Автор - i691198
Дата добавления - 25.10.2026 в 11:10
kvlar Дата: Воскресенье, 25.10.2026, 11:17 | Сообщение № 30
Группа: Пользователи
Ранг: Участник
Сообщений: 77
Репутация: 0 ±
Замечаний: 0% ±

оформите Ваши формулы

исправил

Для версий имеющих функцию LAMBDA можно так

все на первую заполненную ячейку

Можно сцепить две формулы от cmivadwot, в одну функцией ВЫБОР

Отлично.
 
Ответить
Сообщение
оформите Ваши формулы

исправил

Для версий имеющих функцию LAMBDA можно так

все на первую заполненную ячейку

Можно сцепить две формулы от cmivadwot, в одну функцией ВЫБОР

Отлично.

Автор - kvlar
Дата добавления - 25.10.2026 в 11:17
Gustav Дата: Воскресенье, 25.10.2026, 15:00 | Сообщение № 31
Группа: Админы
Ранг: Участник клуба
Сообщений: 2912
Репутация: 1226 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Можно сцепить две формулы от cmivadwot, в одну функцией ВЫБОР

Зачем? Чтобы обречь себя на ввод формулы массива в две соседние ячейки, предварительно обе их выделив? И потом протягивать вниз именно выделение из двух ячеек?

Нет, если уж делать формулы для классических версий Excel (2013, 2016, 2019), то я бы максимально их упростил, не гнушаясь и доп. столбца, который при желании всегда можно скрыть. Вот "мои" формулы для ячеек N1, O1 и P1 соответственно (обычные, не массивные), полученные из формул от cmivadwot:
Код
=ЕСЛИОШИБКА(АГРЕГАТ(15; 6; СТРОКА($H$5:$H$999)/($H$5:$H$999<>""); СТРОКА(H1)); "")

Код
=ГИПЕРССЫЛКА("#H"&N1; ЕСЛИ(N1=""; ""; "H"&N1))

Код
=ЕСЛИ(N1=""; ""; ИНДЕКС($H$5:$H$999; N1-4))


И заметьте, что во второй формуле (для ячейки O1) ГИПЕРССЫЛКА теперь самая внешняя функция и поэтому результат в этой ячейки отображается как полноценная гиперссылка - синенькая и подчеркнутая!

Остается протянуть формулы вниз на количество строк, достаточное для отображения всех непустых значений (с запасом), а также при необходимости скрыть весь столбец N установкой его нулевой ширины ИЛИ скрыть значения в этом столбце, установив белый цвет шрифта (невидимый на стандартном белом фоне ячейки).


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Можно сцепить две формулы от cmivadwot, в одну функцией ВЫБОР

Зачем? Чтобы обречь себя на ввод формулы массива в две соседние ячейки, предварительно обе их выделив? И потом протягивать вниз именно выделение из двух ячеек?

Нет, если уж делать формулы для классических версий Excel (2013, 2016, 2019), то я бы максимально их упростил, не гнушаясь и доп. столбца, который при желании всегда можно скрыть. Вот "мои" формулы для ячеек N1, O1 и P1 соответственно (обычные, не массивные), полученные из формул от cmivadwot:
Код
=ЕСЛИОШИБКА(АГРЕГАТ(15; 6; СТРОКА($H$5:$H$999)/($H$5:$H$999<>""); СТРОКА(H1)); "")

Код
=ГИПЕРССЫЛКА("#H"&N1; ЕСЛИ(N1=""; ""; "H"&N1))

Код
=ЕСЛИ(N1=""; ""; ИНДЕКС($H$5:$H$999; N1-4))


И заметьте, что во второй формуле (для ячейки O1) ГИПЕРССЫЛКА теперь самая внешняя функция и поэтому результат в этой ячейки отображается как полноценная гиперссылка - синенькая и подчеркнутая!

Остается протянуть формулы вниз на количество строк, достаточное для отображения всех непустых значений (с запасом), а также при необходимости скрыть весь столбец N установкой его нулевой ширины ИЛИ скрыть значения в этом столбце, установив белый цвет шрифта (невидимый на стандартном белом фоне ячейки).

Автор - Gustav
Дата добавления - 25.10.2026 в 15:00
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

Яндекс.Метрика Яндекс цитирования