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

Вход

Регистрация

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

 

= Мир MS Excel/Переход к найденному значению в листе - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Переход к найденному значению в листе (Формулы/Formulas)
Переход к найденному значению в листе
малыш Дата: Суббота, 22.04.2017, 11:13 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день! Создал список, для организации оглавления, в ОООЧЕНЬ большой книге с множеством страниц, на которых большое количество строк . Задача в том, чтобы при нажатии на ячейку, выполнялся поиск по значению и курсор перебрасывало на нужное место в книге. Все вроде бы получилось вот только дальше 99 строки не переходит или не видит.. В чем подвох не пойму?! вот формула которую использую для гиперссылок
Код
=ГИПЕРССЫЛКА(ПСТР(ЯЧЕЙКА("имяфайла");НАЙТИ("[";ЯЧЕЙКА("имяфайла"));НАЙТИ("]";ЯЧЕЙКА("имяфайла"))-НАЙТИ("[";ЯЧЕЙКА("имяфайла"))+1)&АДРЕС(A1;4;1;1;"Лист1");O1)


а вот формула по которой определяется строка
Код
=ПРАВСИМВ(ЯЧЕЙКА("адрес";ИНДЕКС($D$1:$D$1500;ПОИСКПОЗ($O$1;$D$2:$D$1500;0);1));2)


Сообщение отредактировал малыш - Суббота, 22.04.2017, 12:02
 
Ответить
СообщениеДобрый день! Создал список, для организации оглавления, в ОООЧЕНЬ большой книге с множеством страниц, на которых большое количество строк . Задача в том, чтобы при нажатии на ячейку, выполнялся поиск по значению и курсор перебрасывало на нужное место в книге. Все вроде бы получилось вот только дальше 99 строки не переходит или не видит.. В чем подвох не пойму?! вот формула которую использую для гиперссылок
Код
=ГИПЕРССЫЛКА(ПСТР(ЯЧЕЙКА("имяфайла");НАЙТИ("[";ЯЧЕЙКА("имяфайла"));НАЙТИ("]";ЯЧЕЙКА("имяфайла"))-НАЙТИ("[";ЯЧЕЙКА("имяфайла"))+1)&АДРЕС(A1;4;1;1;"Лист1");O1)


а вот формула по которой определяется строка
Код
=ПРАВСИМВ(ЯЧЕЙКА("адрес";ИНДЕКС($D$1:$D$1500;ПОИСКПОЗ($O$1;$D$2:$D$1500;0);1));2)

Автор - малыш
Дата добавления - 22.04.2017 в 11:13
Nic70y Дата: Суббота, 22.04.2017, 19:24 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3914
Репутация: 828 ±
Замечаний: 0% ±

Excel 2013
ну конечно
=ПРАВСИМВ(.....;2)
более 2 знаков и не выдаст (макс 99)
Код
=ГИПЕРССЫЛКА("#"&"D"&ПОИСКПОЗ($O$1;$D$2:$D$1500;0);O1)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Суббота, 22.04.2017, 19:32
 
Ответить
Сообщениену конечно
=ПРАВСИМВ(.....;2)
более 2 знаков и не выдаст (макс 99)
Код
=ГИПЕРССЫЛКА("#"&"D"&ПОИСКПОЗ($O$1;$D$2:$D$1500;0);O1)

Автор - Nic70y
Дата добавления - 22.04.2017 в 19:24
малыш Дата: Вторник, 25.04.2017, 09:09 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Nic70y, Да , то что нужно! спасибо.. вот только можно ли реализовать так , чтобы при переходу к найденной строке, она отображалась по центру экрана? а так получается, что после переброса к нужному месту , еще надо делать прокрутку ниже строк на 10-15


Сообщение отредактировал малыш - Вторник, 25.04.2017, 09:16
 
Ответить
СообщениеNic70y, Да , то что нужно! спасибо.. вот только можно ли реализовать так , чтобы при переходу к найденной строке, она отображалась по центру экрана? а так получается, что после переброса к нужному месту , еще надо делать прокрутку ниже строк на 10-15

Автор - малыш
Дата добавления - 25.04.2017 в 09:09
AlexM Дата: Вторник, 25.04.2017, 09:59 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

малыш, файл свой приложите.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениемалыш, файл свой приложите.

Автор - AlexM
Дата добавления - 25.04.2017 в 09:59
малыш Дата: Вторник, 25.04.2017, 10:44 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
AlexM, вот пожалуйста
К сообщению приложен файл: ____.zip(96Kb)
 
Ответить
СообщениеAlexM, вот пожалуйста

Автор - малыш
Дата добавления - 25.04.2017 в 10:44
AlexM Дата: Вторник, 25.04.2017, 11:15 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

Макросы должны быть включены. см. файл
К сообщению приложен файл: _____01.rar(97Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеМакросы должны быть включены. см. файл

Автор - AlexM
Дата добавления - 25.04.2017 в 11:15
малыш Дата: Вторник, 25.04.2017, 11:31 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
AlexM, а что вы сделали? просто хочу так же сделать в другом файле
 
Ответить
СообщениеAlexM, а что вы сделали? просто хочу так же сделать в другом файле

Автор - малыш
Дата добавления - 25.04.2017 в 11:31
Sancho Дата: Вторник, 25.04.2017, 11:53 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 129
Репутация: 3 ±
Замечаний: 0% ±

2007, 2010, 2013
малыш, вот так

Код
=ГИПЕРССЫЛКА("#"&"D"&ПОИСКПОЗ($B$1;$D$1:$D$1743;0);B1)


только я бы еще добавил +20
Код
=ГИПЕРССЫЛКА("#"&"D"&ПОИСКПОЗ($B$1;$D$1:$D$1743;0)+20;B1)


тогда полностью раздел будет отображен, но надо что бы количество строк в разделах было одинаковым, а вас разное


Сообщение отредактировал Sancho - Вторник, 25.04.2017, 11:55
 
Ответить
Сообщениемалыш, вот так

Код
=ГИПЕРССЫЛКА("#"&"D"&ПОИСКПОЗ($B$1;$D$1:$D$1743;0);B1)


только я бы еще добавил +20
Код
=ГИПЕРССЫЛКА("#"&"D"&ПОИСКПОЗ($B$1;$D$1:$D$1743;0)+20;B1)


тогда полностью раздел будет отображен, но надо что бы количество строк в разделах было одинаковым, а вас разное

Автор - Sancho
Дата добавления - 25.04.2017 в 11:53
малыш Дата: Вторник, 25.04.2017, 12:08 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Sancho, да, то что нужно).. есть один момент ..когда выбираешь из списка ссылку ниже от предыдущей все нормально (название в центре), а если из списка ссылку выше от предыдущей то уходит вверх (как раз на те +20строк)! можно ли как то это подрегулировать? может через условие надо?


Сообщение отредактировал малыш - Вторник, 25.04.2017, 12:15
 
Ответить
СообщениеSancho, да, то что нужно).. есть один момент ..когда выбираешь из списка ссылку ниже от предыдущей все нормально (название в центре), а если из списка ссылку выше от предыдущей то уходит вверх (как раз на те +20строк)! можно ли как то это подрегулировать? может через условие надо?

Автор - малыш
Дата добавления - 25.04.2017 в 12:08
Nic70y Дата: Вторник, 25.04.2017, 12:24 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3914
Репутация: 828 ±
Замечаний: 0% ±

Excel 2013
малыш. ваш вопрос решается тока макросом,
AlexM его написал,
файл не смотрел - думаю он в модуле листа.
ПКМ по ярлыку листа - исходный текст


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениемалыш. ваш вопрос решается тока макросом,
AlexM его написал,
файл не смотрел - думаю он в модуле листа.
ПКМ по ярлыку листа - исходный текст

Автор - Nic70y
Дата добавления - 25.04.2017 в 12:24
Sancho Дата: Вторник, 25.04.2017, 12:34 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 129
Репутация: 3 ±
Замечаний: 0% ±

2007, 2010, 2013
когда выбираешь из списка ссылку ниже от предыдущей все нормально (название в центре), а если из списка ссылку выше от предыдущей то уходит вверх (как раз на те +20строк)! можно ли как то это подрегулировать? может через условие надо?

малыш, я же писал, что у вас в разделах разное количество строк, особенно пустых строк. вот надо и привести к одинаковому количеству строк , а потом уже определяться будет это +20 или какое другое значение

Цитата
малыш. ваш вопрос решается тока макросом,

Nic70y, почему только макросом? формула вполне рабочая, только исправить чуток надо было


Сообщение отредактировал Sancho - Вторник, 25.04.2017, 12:34
 
Ответить
Сообщение
когда выбираешь из списка ссылку ниже от предыдущей все нормально (название в центре), а если из списка ссылку выше от предыдущей то уходит вверх (как раз на те +20строк)! можно ли как то это подрегулировать? может через условие надо?

малыш, я же писал, что у вас в разделах разное количество строк, особенно пустых строк. вот надо и привести к одинаковому количеству строк , а потом уже определяться будет это +20 или какое другое значение

Цитата
малыш. ваш вопрос решается тока макросом,

Nic70y, почему только макросом? формула вполне рабочая, только исправить чуток надо было

Автор - Sancho
Дата добавления - 25.04.2017 в 12:34
Nic70y Дата: Вторник, 25.04.2017, 12:39 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3914
Репутация: 828 ±
Замечаний: 0% ±

Excel 2013
только исправить чуток надо
не, не выйдет, т.к. прямая связь со скролом в данный момент времени.


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
только исправить чуток надо
не, не выйдет, т.к. прямая связь со скролом в данный момент времени.

Автор - Nic70y
Дата добавления - 25.04.2017 в 12:39
малыш Дата: Вторник, 25.04.2017, 12:43 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Nic70y, да все супер! спасибо разобрался!! макрос работает красиво! Всем огромное спасибо!


Сообщение отредактировал малыш - Вторник, 25.04.2017, 12:44
 
Ответить
СообщениеNic70y, да все супер! спасибо разобрался!! макрос работает красиво! Всем огромное спасибо!

Автор - малыш
Дата добавления - 25.04.2017 в 12:43
AlexM Дата: Вторник, 25.04.2017, 12:53 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

Хорошо что разобрались.
Кода два. Первый в модуле листа, второй в модуле1
Первый код запускается по событию Worksheet_SelectionChange. Он предает адрес ячейки функции CenterOnCell в модуле1, которая центрирует окно относительно активной ячейки.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеХорошо что разобрались.
Кода два. Первый в модуле листа, второй в модуле1
Первый код запускается по событию Worksheet_SelectionChange. Он предает адрес ячейки функции CenterOnCell в модуле1, которая центрирует окно относительно активной ячейки.

Автор - AlexM
Дата добавления - 25.04.2017 в 12:53
малыш Дата: Вторник, 25.04.2017, 14:33 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
AlexM, А подскажите , а как быть если поставить защиту на лист ? это возможно, чтоб сохранялась работа всех функций макроса?
 
Ответить
СообщениеAlexM, А подскажите , а как быть если поставить защиту на лист ? это возможно, чтоб сохранялась работа всех функций макроса?

Автор - малыш
Дата добавления - 25.04.2017 в 14:33
AlexM Дата: Вторник, 25.04.2017, 15:10 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

Делают так.
Макрос снимает защиту, делает необходимые действия и в конце ставит защиту.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеДелают так.
Макрос снимает защиту, делает необходимые действия и в конце ставит защиту.

Автор - AlexM
Дата добавления - 25.04.2017 в 15:10
малыш Дата: Вторник, 25.04.2017, 15:46 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

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


Сообщение отредактировал малыш - Вторник, 25.04.2017, 15:58
 
Ответить
СообщениеAlexM, как оказалось защита срабатывает не на макрос а на выпадающий список данных и при выборе срабатывает защита. тут оказалось все намного проще всего лишь надо снять защиту с одной ячейки)

Автор - малыш
Дата добавления - 25.04.2017 в 15:46
AlexM Дата: Вторник, 25.04.2017, 16:06 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

на всякий случай доработанный код модуля листа[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="111"
CenterOnCell Range(Target.Address)
ActiveSheet.Protect Password:="111", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
End Sub
[/vba]


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениена всякий случай доработанный код модуля листа[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="111"
CenterOnCell Range(Target.Address)
ActiveSheet.Protect Password:="111", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
End Sub
[/vba]

Автор - AlexM
Дата добавления - 25.04.2017 в 16:06
малыш Дата: Вторник, 25.04.2017, 16:36 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
AlexM, Круто !! спасибо... а можно к этому еще добавить скрытие пустых столбцов ?
 
Ответить
СообщениеAlexM, Круто !! спасибо... а можно к этому еще добавить скрытие пустых столбцов ?

Автор - малыш
Дата добавления - 25.04.2017 в 16:36
AlexM Дата: Вторник, 25.04.2017, 16:39 | Сообщение № 20
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3446
Репутация: 873 ±
Замечаний: 0% ±

А зачем скрытие столбцов в макрос добавлять. Скройте ненужные вручную.
Да и вопрос этот не в тему.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеА зачем скрытие столбцов в макрос добавлять. Скройте ненужные вручную.
Да и вопрос этот не в тему.

Автор - AlexM
Дата добавления - 25.04.2017 в 16:39
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Переход к найденному значению в листе (Формулы/Formulas)
Страница 1 из 11
Поиск:

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