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

Вход

Регистрация

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

 

= Мир MS Excel/Как превратить Ссылку на диапазон в Текст - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как превратить Ссылку на диапазон в Текст (Формулы/Formulas)
Как превратить Ссылку на диапазон в Текст
ZetMenChavo Дата: Воскресенье, 20.12.2020, 00:30 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 159
Репутация: 7 ±
Замечаний: 0% ±

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

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

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

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

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


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

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

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

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

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

Автор - ZetMenChavo
Дата добавления - 20.12.2020 в 00:30
Gustav Дата: Воскресенье, 20.12.2020, 00:49 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Может, функция Ф.ТЕКСТ как-то подойдёт? Типа в ячейке B1 диапазон:
Код
=A1:A10

а в C1 - разбор:
Код
=ПСТР(Ф.ТЕКСТ(B1);2;20)

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


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеМожет, функция Ф.ТЕКСТ как-то подойдёт? Типа в ячейке B1 диапазон:
Код
=A1:A10

а в C1 - разбор:
Код
=ПСТР(Ф.ТЕКСТ(B1);2;20)

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

Автор - Gustav
Дата добавления - 20.12.2020 в 00:49
ZetMenChavo Дата: Воскресенье, 20.12.2020, 07:58 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 159
Репутация: 7 ±
Замечаний: 0% ±

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

Автор - ZetMenChavo
Дата добавления - 20.12.2020 в 07:58
bmv98rus Дата: Воскресенье, 20.12.2020, 08:43 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

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

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


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеZetMenChavo, Как OFFSET так и CELL летучие и их лучше не применять, не смотря на компактность записи, если можно обойтись без них.

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

Автор - bmv98rus
Дата добавления - 20.12.2020 в 08:43
ZetMenChavo Дата: Воскресенье, 20.12.2020, 09:17 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 159
Репутация: 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
 
Ответить
Сообщение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;);"►"))


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

Автор - ZetMenChavo
Дата добавления - 20.12.2020 в 09:17
Pelena Дата: Воскресенье, 20.12.2020, 09:31 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

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


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеZetMenChavo, оформите формулы тегами с помощью кнопки fx в режиме правки поста

Автор - Pelena
Дата добавления - 20.12.2020 в 09:31
ZetMenChavo Дата: Воскресенье, 20.12.2020, 09:46 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 159
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
Pelena,
Код
=ЕСЛИ(A10="";"";ГИПЕРССЫЛКА(ПСТР(ЯЧЕЙКА("имяфайла");ПОИСК("[";ЯЧЕЙКА("имяфайла"));30)&"!"&ПОДСТАВИТЬ(АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0);4)&":"&АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0)+1;4);1;);"►"))
 
Ответить
СообщениеPelena,
Код
=ЕСЛИ(A10="";"";ГИПЕРССЫЛКА(ПСТР(ЯЧЕЙКА("имяфайла");ПОИСК("[";ЯЧЕЙКА("имяфайла"));30)&"!"&ПОДСТАВИТЬ(АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0);4)&":"&АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0)+1;4);1;);"►"))

Автор - ZetMenChavo
Дата добавления - 20.12.2020 в 09:46
Pelena Дата: Воскресенье, 20.12.2020, 09:47 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

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


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеОтлично! А теперь, плз, то же самое в предыдущих постах :)

Автор - Pelena
Дата добавления - 20.12.2020 в 09:47
ZetMenChavo Дата: Воскресенье, 20.12.2020, 09:49 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 159
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
Pelena, Разобрался с этим чатом :D
 
Ответить
СообщениеPelena, Разобрался с этим чатом :D

Автор - ZetMenChavo
Дата добавления - 20.12.2020 в 09:49
Pelena Дата: Воскресенье, 20.12.2020, 10:48 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Получится ли упростить эту часть

на мой взгляд, не надо тут упрощать. Короче не значит быстрее.
В первой части, если работаете на одном листе, можно заменить
Код
ПСТР(ЯЧЕЙКА("имяфайла");ПОИСК("[";ЯЧЕЙКА("имяфайла"));30)&"!"
на "#"
Код
ГИПЕРССЫЛКА("#"&ПОДСТАВИТЬ(АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0);4)&":"&АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0)+1;4);1;);"►")


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Получится ли упростить эту часть

на мой взгляд, не надо тут упрощать. Короче не значит быстрее.
В первой части, если работаете на одном листе, можно заменить
Код
ПСТР(ЯЧЕЙКА("имяфайла");ПОИСК("[";ЯЧЕЙКА("имяфайла"));30)&"!"
на "#"
Код
ГИПЕРССЫЛКА("#"&ПОДСТАВИТЬ(АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0);4)&":"&АДРЕС(1;СТОЛБЕЦ(B1)+ПОИСКПОЗ(A10;C10:P10;0)+1;4);1;);"►")

Автор - Pelena
Дата добавления - 20.12.2020 в 10:48
bmv98rus Дата: Воскресенье, 20.12.2020, 12:08 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
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;"►"))

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


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

Сообщение отредактировал bmv98rus - Воскресенье, 20.12.2020, 13:59
 
Ответить
Сообщение
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;"►"))

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

Автор - bmv98rus
Дата добавления - 20.12.2020 в 12:08
ZetMenChavo Дата: Воскресенье, 20.12.2020, 12:52 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 159
Репутация: 7 ±
Замечаний: 0% ±

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

Автор - ZetMenChavo
Дата добавления - 20.12.2020 в 12:52
Pelena Дата: Воскресенье, 20.12.2020, 13:15 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
она летучая

я в курсе. Только не пойму, о чём речь. В моём варианте ЯЧЕЙКА() отсутствует


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
она летучая

я в курсе. Только не пойму, о чём речь. В моём варианте ЯЧЕЙКА() отсутствует

Автор - Pelena
Дата добавления - 20.12.2020 в 13:15
bmv98rus Дата: Воскресенье, 20.12.2020, 13:59 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Лена, сорян, прочел не так.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеЛена, сорян, прочел не так.

Автор - bmv98rus
Дата добавления - 20.12.2020 в 13:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как превратить Ссылку на диапазон в Текст (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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