Как превратить Ссылку на диапазон в Текст

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Как превратить Ссылку на диапазон в Текст (Формулы/Formulas)

ZetMenChavo

Дата: Воскресенье, 20.12.2020, 00:30 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 162

Репутация: 7 ±

Замечаний: 0% ±

Excel 2010

Здравствуйте. нередко использую функцию "ГИПЕРССЫЛКА", для правильной работы которой необходимо что бы адрес ячейки (диапазона) был задан текстом. Обычно использую для синтеза ссылки функции ЯЧЕЙКА("адрес";AH370), если адрес только на одну ячейку, либо АДРЕС()&":"&АДРЕС(), если надо сослаться на диапазон. Так как не редко использую с функцией АДРЕС функции ПОИСКПОЗ, ИНДЕКС, что бы ссылка могла смещаться, конечная функция получается громоздкой.

И вот недавно открыл для себя ещё одну полезную функцию СМЕЩ, которую куда проще писать и использовать в функциях СУММ и СЧЁТЕСЛИ, но при попытке использовать её в ГИППЕРСЫЛКЕ возникла проблема. Так как она сама по себе создает ссылку, то для ГИП-КИ её нужно превратить в текст. Для этого использовал функцию ЯЧЕЙКА("адрес";СМЕЩ(a1;;;;7)), но получал адрес лишь первой ячейки диапазона.

Есть ли альтернатива функции ЯЧЕЙКА которая будет целиком преобразовывать адрес диапазона в текст?

Код

=ЯЧЕЙКА("адрес";СМЕЩ(A1;;;;7))

ЯЧЕЙКА("адрес";$A$1:$G$1)
"$A$1"

Сообщение отредактировал ZetMenChavo - Воскресенье, 20.12.2020, 10:07

Ответить

Gustav

Дата: Воскресенье, 20.12.2020, 00:49 | Сообщение № 2

Группа: Админы

Ранг: Участник клуба

Сообщений: 2792

Репутация: 1155 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Может, функция Ф.ТЕКСТ как-то подойдёт? Типа в ячейке B1 диапазон:

Код

=A1:A10

а в C1 - разбор:

Код

=ПСТР(Ф.ТЕКСТ(B1);2;20)

и "A1:A10" в качестве результата в C1.

МОИ: Ник, Tip box: 41001663842605

Ответить

ZetMenChavo

Дата: Воскресенье, 20.12.2020, 07:58 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 162

Репутация: 7 ±

Замечаний: 0% ±

Excel 2010

Gustav, Попытался сделать ваш вариант, но возникло непреодолимое препятствие в моём Excel 2010. Там такой функции нет(

Ответить

bmv98rus

Дата: Воскресенье, 20.12.2020, 08:43 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4113

Репутация: 769 ±

Замечаний: 0% ±

Excel 2013/2016

ZetMenChavo, Как OFFSET так и CELL летучие и их лучше не применять, не смотря на компактность записи, если можно обойтись без них.

Но также вы можете использовать стиль R1C1. Комбинация R1C1:R1C1 даст вам диапазон. вставить между номера строк и столбцов не проблема. а будет короче чем с адрес, но у Address есть оно полезное свойство использовать имя листа и она сама добавляет апострофа по надобности, если в имени есть пробелы.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

ZetMenChavo

Дата: Воскресенье, 20.12.2020, 09:17 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 162

Репутация: 7 ±

Замечаний: 0% ±

Excel 2010

bmv98rus, Никогда раньше не доводилось использовать стиль ссылок R1C1.
Получится ли упростить эту часть

Код

=ПОДСТАВИТЬ(АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0);4)&":"&АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0)+1;4);1;)

в формуле

Код

=ЕСЛИ(A10="";"";ГИПЕРССЫЛКА(ПСТР(ЯЧЕЙКА("имяфайла");ПОИСК("[";ЯЧЕЙКА("имяфайла"));30)&"!"&ПОДСТАВИТЬ(АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0);4)&":"&АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0)+1;4);1;);"►"))

Прикрепил файл с примером использования.

К сообщению приложен файл: 2922359.xlsx (9.1 Kb)

Сообщение отредактировал ZetMenChavo - Воскресенье, 20.12.2020, 09:48

Ответить

Pelena

Дата: Воскресенье, 20.12.2020, 09:31 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 19401

Репутация: 4549 ±

Замечаний: ±

Excel 365 & Mac Excel

ZetMenChavo, оформите формулы тегами с помощью кнопки fx в режиме правки поста

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

ZetMenChavo

Дата: Воскресенье, 20.12.2020, 09:46 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 162

Репутация: 7 ±

Замечаний: 0% ±

Excel 2010

Pelena,

Код

Ответить

Pelena

Дата: Воскресенье, 20.12.2020, 09:47 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 19401

Репутация: 4549 ±

Замечаний: ±

Excel 365 & Mac Excel

Отлично! А теперь, плз, то же самое в предыдущих постах

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

ZetMenChavo

Дата: Воскресенье, 20.12.2020, 09:49 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 162

Репутация: 7 ±

Замечаний: 0% ±

Excel 2010

Pelena, Разобрался с этим чатом

Ответить

Pelena

Дата: Воскресенье, 20.12.2020, 10:48 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 19401

Репутация: 4549 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата ZetMenChavo, 20.12.2020 в 09:17, в сообщении № 5 ()

Получится ли упростить эту часть

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

Код

ПСТР(ЯЧЕЙКА("имяфайла");ПОИСК("[";ЯЧЕЙКА("имяфайла"));30)&"!"

на "#"

Код

ГИПЕРССЫЛКА("#"&ПОДСТАВИТЬ(АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0);4)&":"&АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0)+1;4);1;);"►")

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

bmv98rus

Дата: Воскресенье, 20.12.2020, 12:08 | Сообщение № 11

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4113

Репутация: 769 ±

Замечаний: 0% ±

Excel 2013/2016

Цитата Pelena, 20.12.2020 в 10:48, в сообщении № 10 ()
CELL("имяфайла")

ой Елена плохому учите. она летучая и будет имя листа активного в активной книге возвращать. Лишние пересчет, хотя на результат не повлияют.

ZetMenChavo, вам гиперссылку на два столбца целиком нужно получить?

Код

=IF(A10="";"";HYPERLINK(MID(CELL("filename");SEARCH("[";CELL("filename"));30)&"!R1C"&COLUMN(B1)+MATCH(A10;C10:P10;0)&":R"&ROWS(A:A)&"C"&COLUMN(B1)+MATCH(A10;C10:P10;0)+1;"►"))

Или с учетом того что на лист тот же

Код

=IF(A10="";"";HYPERLINK("#R1C"&COLUMN(B1)+MATCH(A10;C10:P10;0)&":R"&ROWS(A:A)&"C"&COLUMN(B1)+MATCH(A10;C10:P10;0)+1;"►"))

и короче и быстрее

К сообщению приложен файл: example2011.xlsx (9.4 Kb)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Воскресенье, 20.12.2020, 13:59

Ответить

ZetMenChavo

Дата: Воскресенье, 20.12.2020, 12:52 | Сообщение № 12

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 162

Репутация: 7 ±

Замечаний: 0% ±

Excel 2010

Чтож, жаль что с функцией СМЕЩ не получилось так как хотел, но всёж увидел интересные альтернативы.
Pelena, bmv98rus, спасибо за ваши варианты), попользуюсь ими на практике, а там уже будет видно который для меня удобнее.