Почему сбиваются ссылки в excel
Макрос может быть полезен для замены абсолютных гиперссылок на относительные, а также помогает вернуть работоспособность ссылок после случайного сохранения файла Excel в другой папке (на другом диске).
Если нужно заменить несколько вариантов неверных ссылок, код будет таким:
Расширенная версия этого макроса учитывает, что слеш в ссылках может быть как прямым, так и обратным, а также выводит информацию о количестве произведённых замен, и список ссылок из файла, которые не были обработаны (к которым замены не были применены)
Комментарии
Елена, это уже совсем другой макрос нужен.
Можем сделать под заказ. От 1500 руб.
Скажите, пожалуйста, как сделать, чтобы макрос по введенной уже гиперссылке проверял наличие объекта сначала по указанному адресу (1) и если оттуда объект уже перенесен, адрес менялся на другой (2). Гиперссылка создается на папки объектов на удаленном сервере, а со временем они переносятся в другую папку- архив. Надеюсь понятно написала.
Не знаю что даже сказать. Магия сработала. Спасибо разработчик большое! Здоровья тебе и твоим близким.
Спасибо Дружище. Желаю тебе всего хорошего, чего сам себе желаешь.
Отличный макрос! Спасибо!
Автор, спасибо огромное!
Спасибо ОГРОМНОЕ . Помогло, заменил 267 гиперссылок.
Спасибо автору! Макрос помог!
Благодарю тебя за то что ты есть, и написал такой прекрасный макрос.
Ты спас годовую работу трех человек.
Сработал расширенный макрос,
более 5000 адресов исправлены на коректные
Огромная благодарность
Дай Бог тебе здоровья автор
Добрый день! Прогнал расширенную версию макроса, он отработал, но не все. после нажатия на ошибочную гиперссылку и дальнейшим запуском макроса он ее отрабатывает, а остальные просто выводит в протокол что они есть.
Я спасён!
Огромное спасибо за помощь! Помогли сэкономить огромный вал рабочего времени!
Огромно спасибо. Автоматически исправил более 5 тысяч ссылок в архиве. Все работает. Ну просто нет слов, как я Вам признателен.
СПАСИБО ОГРОМНОЕ ЗА ВАШ РЕСУРС И ДОБРОТУ.
Правда, Ваш макрос у меня не работает почему-то, цель сделать из абсолютных ссылок относительные.но выяснилось, что вполне работает обычное "найти и заменить"
в строку "найти" пишем кусок пути ДО нужной папки, а в "заменить" оставляем пустоту, ставим галку "во всей книге" и жмем "заменить все"
Слетели все ссылки после вставки автосохраненного файла. Почему то данный макрос не работает
Sub ZamenaIsporchennihGiperssilok()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
' part of hyperlink, which you want to change
oldString = "C:\Users\DianaGA\AppData\Roaming\Microsoft\Excel"
' to what to change
newString = "C:\Users\DianaGA\Dekstop\ÀÁÌ - 2015"
For Each sh In ActiveWorkbook.Worksheets ' ïåðåáèðàåì âñå ëèñòû â àêòèâíîé êíèãå
For Each hl In sh.Hyperlinks ' ïåðåáèðàåì âñå ãèïåðññûëêè íà ëèñòå
If hl.Address Like "*" & oldString & "*" Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub
Никаких действий не происходит
Тут ничего особенного делать не надо, - Excel сам все гиперссылки в книге подправит, как только вы лист переименуете.
Добрый вечер всем! Выручайте! Как изменить имя листа книги в Excel при этом не потеряв гиперссылки? Заранее благодарю!
Спасибо тебе, добрый человек!
Благодарность не знает границ! Спасибо очень выручили!)))
Автор, огромное вам СПАСИБО. Очень выручили
Доброго дня! Подскажите, пожалуйста, код макроса, который бы в выделенном диапазоне ячеек (каждая из которых ссылается в относительном режиме на какую-то ячейку этого же листа) заменил все относительные ссылки на абсолютные.
vitamax555, а какое отношение ваш вопрос имеет к теме статьи, - замене гиперссылок?
На вопросы «не в тему» не отвечаю
Доброго дня! Подскажите, пожалуйста, у меня есть экселевский файл и в нем прописан код VBA. Вроде бы работал правильно, но мне нужно было изменить данные, так как не правильно тянул часть данных из исходника. Как только изменил данные и исходник, данные стали грузиться правильно, однако очень долго. Так раньше на это требовалось около 1 минуты, теперь это занимает 10. Что мне делатать. Подскажите, пожалуйста! Спасибо!
Почему то макрос меняет гиперссылку только в одной ячейке, следующую не трогает. Приходится запускать столько раз, сколько ячеек. Как будто цикл не срабатывает.
Здравствуйте, Юрий
Достаточно убрать цикл по листам,
применяя изменения только к активному листу:
Спасибо! Очень выручила данная статья!
Но как всегда возник вопрос - как изменить данный макрос, что бы изменения коснулись только ОДНОГО листа в книге? (Файлов больших несколько, листов в каждом масса, соответственно разбирать файл по листам а потом собирать его обратно - очень долгий и не совсем желаемый процесс. )
Заранее СПАСИБО.
целую твои умелые ручки, автор!
Вот в этом и проблема, когда я папку с (эксель и файлами) перемещаю на другой комп у меня остается адрес гиперссылки моего компа (где находилась папка изначально)
Здравствуйте, Владимир
Так не получится, - Excel (тем более, если он не запущен) не отслеживает перемещение файлов и папок.
Да и как Excel узнает, где искать перемещенные файлы. мало ли куда вы их перенесете, вдруг вообще на другой комп.
Надо делать гиперссылки относительными (чтобы путь прописывался относительно файла Excel)?
а папку с файлами держать в той же папке. где сохранён файл Excel, и не переименовывать.
Тогда,если переносите на другой комп файл Excel вместе с папкой, гиперссылки вроде должны сохранять работоспособность.
подскажите, проблема такая, в экселе перестали работать гиперссылка, я взял макрос поменял часть гиперссылки и он начал работать, но когда папку (с файлами и экселем) отправляю, или перемещаю на другой жесткий диск они перестают работать, приходится опять переделывать через макрос гиперссылку.
вопрос можно ли сделать так что бы при переносе папки на другой диск, все менялась автоматически? (когда делаешь в новом экселе гиперссылку и переносишь всю папку куда либо все меняется автоматом)
Здравствуйте, Татьяна.
Так вы перенесите файлы на другой комп в ту же папку (чтобы путь к папке не изменился), - и тогда ничего переделывать не придётся.
А если путь к папке изменится, - тогда макрос этот поможет.
Как запустить макрос - объяснять не буду (об этом в интернете много написано, в т.ч. есть видеоинструкции)
В макросе надо будет только задать, что на что менять (в коде указать старый путь к папке, и новый)
Попробуйте, - всё очень просто.
PS: Чтобы вставить макрос в файл, надо нажать сначала Alt + F11, потом Ctrl + R, потом Enter, - и в появившееся большое текстовое поле скопировать код из статьи.
Здравствуйте!
Я в вопросе макросов вообще новичок, вернее никогда с ними не работала, поэтому прошу помощи в следующем вопросе. Я создала файл в EXELе со множеством гиперссылок на разные файлы, эти файлы во многих папках, но эти папки собрала в одну. Теперь мне нужно перенести рабочий файл на другой компьютер. Подскажите, пожалуйста как я могу это сделать не потеряв все гиперссылки, их более 3000. Могу ли я воспользоваться макросом который вы предлагаете? Буду очень признательна за помощь.
Да повыситься многократно твоя карма, уважаемый автор! Спасло!:)
Большое человеческое СПА-СИ-БО!
Ваш макрос здорово меня выручил.
и снова не работает, вот так у меня прописан макрос6
Sub ЗаменаИспорченныхГиперссылок()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
' part of hyperlink, which you want to change
oldString = "AppData\Local\LocalSettings\AppData\Local\LocalSettings\Temp\"
' to what to change
newString = "Ok!\"
For Each sh In ActiveWorkbook.Worksheets ' перебираем все листы в активной книге
For Each hl In sh.Hyperlinks ' перебираем все гиперссылки на листе
If hl.Address Like oldString & "*" Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub
более того при попытке заменить в адресах хотя бы один символ (для проверки работоспособности)например:
' part of hyperlink, which you want to change
oldString = "Temp\"
' to what to change
newString = "Ok!\"
Попробуйте менять ЧАСТЬ пути в гиперссылках
(пути в гиперссылках могут быть сохранены не абсолютные, а относительные, - потому замена и не выполняется)
постараюсь более детально описать процесс создания файла:
вставлены именно макросы, вставлены непосредственно т.е. -> правая кнопка -> гиперссылка -> выбор файла из архива где все добро и хранится. Первые 100 ссылок проверил на работоспособность в т.ч. с переносом папки архива на домашний компьютер, убедившись в работоспособности базы данных продолжил, а после обнаружил проблему. Гиперссылки забиты не формулами а вставкой. Найти и заменить не помогло, "не обнаружено совпадений" правильность написания проверил трижды.
Возможно что в ячейках адрес как таковой не прописан, его можно увидеть только подведя курсор к ячейке с гиперссылкой и он отображается в всплывающей подсказке, именно в ней я и увидел несоответствие фактического и прописанного адресов и вот его и нужно изменить, но в строке формулы ячейки адреса не прописаны наверное поэтому "найти и заменить" не сработало.
Макрос так же не сработал. Могу предоставить скриншоту по каждым моим действиям, может ошибка именно в последовательности
А вы уверены, что у вас на листе именно гиперссылки (синие, подчеркнутые), а не формулы, например?
Нажмите Ctrl + H (диалоговое окно поиска и замены),
в поле «Что заменять» введите AppData\Local\LocalSettings\AppData\Local\LocalSettings\Temp
в поле «На что заменять» введите Ok!
и нажмите «Заменить все»
Если это поможет, - то вам нужен другой макрос (попроще, который выполняет замены в ячейках, а не в гиперссылках):
вместо
Доброго дня! Помогите советом, до сего дня макросами не пользовался и в этой области "чайник".
Проблема следующая на рабочем компьютере создаю базу данных типовых серий (вставляю гиперссылки), скидываю по почте на домашний дорабатываю (на этом этапе гиперссылки вовсе не трогаю, добавляю примечания и прочую доп.информацию. ) скидываю обратно ч/з почту, а на работе сюрприз: большинство гиперссылок не работают. При объеме базы в 3500-5000 файлов попробуйте представить тяжесть разочарования и стресса. Выяснил что адрес сменился с
D:\Нормативно техническая база\Типовые материалы для проектирования\Типовые серии\Ok!\
например было D:\Нормативно техническая база\Типовые материалы для проектирования\Типовые серии\Ok!\0-312 в.0.djvu
стало D:\Нормативно техническая база\Типовые материалы для проектирования\Типовые серии\AppData\Local\LocalSettings\AppData\Local\LocalSettings\Temp\0-312 в.0.djvu
макрос бы помог да не выходит ничего. Пожалуйста помогите разобраться, вот как я его записал
Sub ZamenaIsporchennihGiperssilok()
On Error Resume Next
Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet
' part of hyperlink, which you want to change
oldString = "D:\Нормативно техническая база\Типовые материалы для проектирования\Типовые серии\AppData\Local\LocalSettings\AppData\Local\LocalSettings\Temp\"
' to what to change
newString = "D:\Нормативно техническая база\Типовые материалы для проектирования\Типовые серии\Ok!\"
For Each sh In ActiveWorkbook.Worksheets ' перебираем все листы в активной книге
For Each hl In sh.Hyperlinks ' перебираем все гиперссылки на листе
If hl.Address Like oldString & "*" Then
hl.Address = Replace(hl.Address, oldString, newString)
End If
Next
Next sh
End Sub
Однако положительных признаков работы не подал.
Ну можно "выделить" так строки - вот только нужно ли?
Если вы не знаете, зачем это «выделение» - зачем его делаете?
Это точки останова макроса. Оно вам надо, чтобы макрос останавливался на каждой строке?
Код один раз сработал - значит, проблема точно не в нём.
Ищите проблему в соответствии пути гиперссылок, и текст для замены в макросе.
И убирайте все красные точки (и не ставьте их впредь)
Скажите пожалуйста - скрипт сработал лишь раз. Затем почему то перестал работать. Я зашел в "Войти" в Макросах и там можно выделить все строчки красным (красная точка и красная линия), кроме Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet. На нее так же нельзя перетащить стрелку дебага. То есть я так понял скрипт стопорится на ней. Хотя 1 раз скрипт точно сработал. Я сохранил документ, затем его немного переместили, я изменил скрипт снова как надо (то есть места папок назначения) и он перестал работать.
Огромное спасибо! Очень помогло! Можно сказать, спасло от кары босса )))!
а по-моему гиперссылки и ссылки на файлы итак считаются одним и тем же. для них создана только одна кнопочка там в меню "изменение гиперссылки" слева
то есть
. создать ссылки на сайты.
>> В меню "Изменение Гиперссылки" слева есть возможность выбора:
это для того, чтобы можно было мышкой создать ссылку. а так, эти кнопки особой роли не играют.
можно при помощи этих кнопок создать ссылки на файлы, затем запустить скрипт по изменению ссылок, поменять например "http://ya.ru" на "\helloworld.txt" и при следующем открытии меню "Изменение Гиперссылки" слева будет нажата другая кнопочка
В меню "Изменение Гиперссылки" слева есть возможность выбора:
Связать с:
- файлом, веб-страницей;
- местом в документе;
- новым документом;
- электронной почтой.
Ты не ошибся.
а по-моему все ссылки одинаковые. она становится гипер или обычной просто из-за содержимого ссылки. например если начинается на "http://. " значит гипер, а если "C:\. " или "\\192.168.1.1\. " значит локальная или сетевая там.
как сделать чтобы менял обычные ссылки а не гипер ?
Вроде бы формат файла не изменился (xls) 97-2003, как был так и остался (не у всех, просматривающих, таблицу есть новый (xlsx))
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше
В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).
Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку "Отменить" на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).
Пример функции ВПР с неправильными ссылками на диапазоны
Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).
Пример функции ИНДЕКС с неправильной ссылкой на строку или столбец
Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.
Пример ссылки на закрытую книгу с использованием функции ДВССЫЛ
Откройте книгу, на которые ссылается ссылка. Эта же ошибка будет возникнуть при ссылке на закрытую книгу с динамической функцией массива.
Проблемы с OLE
Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.
Проблемы dDE
Примечание. Динамические Exchange (DDE)— это протокол, который позволяет обмениваться данными между Windows программами Майкрософт.
Проблемы с макросами
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Добрый день. Недавно устроился в компанию, здесь оказалась такая проблема.
Имеется общая сетевая папка, в ней 2 файла Exel (.xlsx,.xlsa,.xlsb - тестировалось на разных). В одном из них ссылка на содержимое ячейки из другого. Содержимое ячейки:
Операционная система Windows 7professional. Office 2010 profeccional.
Добрый день. Недавно устроился в компанию, здесь оказалась такая проблема.
Имеется общая сетевая папка, в ней 2 файла Exel (.xlsx,.xlsa,.xlsb - тестировалось на разных). В одном из них ссылка на содержимое ячейки из другого. Содержимое ячейки:
Операционная система Windows 7professional. Office 2010 profeccional. MaestroSVK
Операционная система Windows 7professional. Office 2010 profeccional. Автор - MaestroSVK
Дата добавления - 17.04.2014 в 08:36
Кто-нибудь нашел решение? Куда копать?
P.S. Проблему, т.е. результат наблюдал в 2010, но не исключаю, что ссылки "бьются", когда файл открывают клиенты с 2007 или 2013.. Установить точно не пока удалось.
Кто-нибудь нашел решение? Куда копать?
P.S. Проблему, т.е. результат наблюдал в 2010, но не исключаю, что ссылки "бьются", когда файл открывают клиенты с 2007 или 2013.. Установить точно не пока удалось. ast
Кто-нибудь нашел решение? Куда копать?
P.S. Проблему, т.е. результат наблюдал в 2010, но не исключаю, что ссылки "бьются", когда файл открывают клиенты с 2007 или 2013.. Установить точно не пока удалось. Автор - ast
Дата добавления - 22.08.2015 в 00:44
Если таких ссылок немного - сделайте их не напрямую через связи, а используя формирование ссылки через ДВССЫЛ(), скажем. Т.е. путь к файлу задается текстовой строкой, формируется как 'путь[книга]лист'!диапазон, например
Понятно,что любую часть этой строки мы можем задать и как вычисляемое значение или ссылку.
Это избавляет от процесса "обновления ссылок", но при перемещении книги-источника ссылки в другое место - нужно исправлять данные для частей, формирующих тесктовое представление ссылки.
Не забываем также о том, что при таком методе, в момент работы с книгой, содержащей функции ДВССЫЛ() - книга-источник ссылок должна быть тоже открыта.
Если таких ссылок немного - сделайте их не напрямую через связи, а используя формирование ссылки через ДВССЫЛ(), скажем. Т.е. путь к файлу задается текстовой строкой, формируется как 'путь[книга]лист'!диапазон, например
Понятно,что любую часть этой строки мы можем задать и как вычисляемое значение или ссылку.
Это избавляет от процесса "обновления ссылок", но при перемещении книги-источника ссылки в другое место - нужно исправлять данные для частей, формирующих тесктовое представление ссылки.
Не забываем также о том, что при таком методе, в момент работы с книгой, содержащей функции ДВССЫЛ() - книга-источник ссылок должна быть тоже открыта. AndreTM
Понятно,что любую часть этой строки мы можем задать и как вычисляемое значение или ссылку.
Это избавляет от процесса "обновления ссылок", но при перемещении книги-источника ссылки в другое место - нужно исправлять данные для частей, формирующих тесктовое представление ссылки.
Не забываем также о том, что при таком методе, в момент работы с книгой, содержащей функции ДВССЫЛ() - книга-источник ссылок должна быть тоже открыта. Автор - AndreTM
Дата добавления - 22.08.2015 в 01:54
ast, всегда такая проблема или только в каких-то случаях? Например, может быть с другими файлами такой проблемы нет?
ast, всегда такая проблема или только в каких-то случаях? Например, может быть с другими файлами такой проблемы нет? Karataev
AndreTM, Спасибо за совет! Правда если для работоспособности такого способа обязательно должны быть открыты книги, на данные в которых указывают ссылки, то это не мой случай, т.к. данные подтягиваются из пары десятков книг..
Karataev, Проблема не постоянна, пока систематику не выявил. Как будут доп.данные- дам знать.
AndreTM, Спасибо за совет! Правда если для работоспособности такого способа обязательно должны быть открыты книги, на данные в которых указывают ссылки, то это не мой случай, т.к. данные подтягиваются из пары десятков книг..
Karataev, Проблема не постоянна, пока систематику не выявил. Как будут доп.данные- дам знать. ast
Karataev, Проблема не постоянна, пока систематику не выявил. Как будут доп.данные- дам знать. Автор - ast
Дата добавления - 23.08.2015 в 01:11
Про два файла топик-стартер говорил, а не я.
Я писал, что у меня проблема усечения пути в формуле 1 в 1.
Из пути "='\\IP_сервера\КорневаяПапка\Подпапка1\[Файл.xlsx]Лист1'!A1", при пока что невыясненных обстоятельствах, Excel удаляет "КорневаяПапка".
У всех файлов, из которых берутся данные, единая часть пути: \\IP_сервера\КорневаяПапка\ , дальше файлы лежат в разных подпапках с разным уровнем вложенности.
На каком (каких) именно из компьютеров происходит сбой и как част- пока не выявлено.
Про разные серверы- опять не я. У меня в сети все это происходит в рамках одной шары на одном сервере (win). поэтому и непонятно, почему в одних ячейках от пути отъедается "КорневаяПапка", а в других - нет. И это не всегда.
Про два файла топик-стартер говорил, а не я.
Я писал, что у меня проблема усечения пути в формуле 1 в 1.
Из пути "='\\IP_сервера\КорневаяПапка\Подпапка1\[Файл.xlsx]Лист1'!A1", при пока что невыясненных обстоятельствах, Excel удаляет "КорневаяПапка".
У всех файлов, из которых берутся данные, единая часть пути: \\IP_сервера\КорневаяПапка\ , дальше файлы лежат в разных подпапках с разным уровнем вложенности.
На каком (каких) именно из компьютеров происходит сбой и как част- пока не выявлено.
Про разные серверы- опять не я. У меня в сети все это происходит в рамках одной шары на одном сервере (win). поэтому и непонятно, почему в одних ячейках от пути отъедается "КорневаяПапка", а в других - нет. И это не всегда. ast
Суть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами.
Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда.
Прилагаю пример, сортируете столбец B от "А до Я" и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально.
Суть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами.
Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда.
Прилагаю пример, сортируете столбец B от "А до Я" и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально. lema
lema, а как вы хотите сортировать столбец в котором не однородные данные, а данные перемешаны с вычислениями этих же самих данных ?
lema, а как вы хотите сортировать столбец в котором не однородные данные, а данные перемешаны с вычислениями этих же самих данных ? koyaanisqatsi
прикрепил файл с Вашим примером и данной формулой - при любой сортировке этого примера работает корректно
прикрепил файл с Вашим примером и данной формулой - при любой сортировке этого примера работает корректно Szekerfehesvar
прикрепил файл с Вашим примером и данной формулой - при любой сортировке этого примера работает корректно Автор - Szekerfehesvar
Дата добавления - 07.09.2016 в 16:27
прикрепил файл с Вашим примером и данной формулой - при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно.
прикрепил файл с Вашим примером и данной формулой - при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно. lema
прикрепил файл с Вашим примером и данной формулой - при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно. Автор - lema
Дата добавления - 07.09.2016 в 17:42
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие buchlotnik
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие Автор - buchlotnik
Дата добавления - 07.09.2016 в 19:24
по формуле - ИНДЕКС() выискивает ячейку по ее адресу в диапазоне, что как раз подходит под Вашу задачу
ПОИСКПОЗ() - ищет в диапазоне ячейку по ее значению
в итоге получается
=ИНДЕКС( вводим диапазон, где находятся искомые данные - то есть столбец В в вашем случае; далее ищем строку, с которой нужно взять значение - ПОИСКПОЗ(значение, которое ищем - в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых;указываем диапазон, где искать наш порядковый номер - столбец А; указываем 0 - это даст нам поиск точных совпадений);указываем 1, так как в нашем диапазоне только 1 столбец)
по формуле - ИНДЕКС() выискивает ячейку по ее адресу в диапазоне, что как раз подходит под Вашу задачу
ПОИСКПОЗ() - ищет в диапазоне ячейку по ее значению
в итоге получается
=ИНДЕКС( вводим диапазон, где находятся искомые данные - то есть столбец В в вашем случае; далее ищем строку, с которой нужно взять значение - ПОИСКПОЗ(значение, которое ищем - в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых;указываем диапазон, где искать наш порядковый номер - столбец А; указываем 0 - это даст нам поиск точных совпадений);указываем 1, так как в нашем диапазоне только 1 столбец) Szekerfehesvar
При открытии файла Excel с такими формулами на другом компьютере, Excel начинает отображает формулы с указанием пути к файлу надстройки, в таком виде:
= 'D:\ПРОЕКТЫ\Папка\заполнение документов\FillDocuments.xla'! Пропись_Число(D12)
Кроме того, Excel может отобразить предупреждение о том, что автоматическое обновление ссылок отключено:
Разумеется, формулы после этого не работают, так как ссылаются на отсутствующий по данному пути файл.
Способ 1 применяется при использовании файла с формулами с разных компов.
Расположите файл надстройки FillDocuments.xla на всех компьютерах по одному пути (и убедитесь, что имя файла надстройки везде одинаково)
Например, можно расположить файл в корне диска С, чтобы путь к файлу был C:\FillDocuments.xla
Или можно разместить в общей сетевой папке, по пути типа \\server\надстройки\FillDocuments.xla
- отключить автозапуск программы в О ПРОГРАММЕ - ОПЦИИ
- закрыть Excel (или закрыть надстройку FillDocuments)
- переместить файл в новое расположение
- запустить надстройку из нового места
- включить автозапуск программы в О ПРОГРАММЕ - ОПЦИИ (если это нужно)
После этого, выполните описанное в одном из других способов, для восстановления формул.
Способ 2: вручную исправляем ссылки, через поиск и замену
Копируем из ячейки «лишний» путь к файлу, начиная с кавычки, и заканчивая восклицательным знаком:
'D:\ПРОЕКТЫ\Папка\заполнение документов\FillDocuments.xla'!
Нажимаем Ctrl + H для вывода диалогового окна «Найти и заменить», в поле «Найти» вставляем скопированный путь,
а поле «Заменить на» оставляем пустым.
Осталось нажать кнопку Заменить все, и формулы приведутся к исходному варианту написания: =Пропись_Число(D12)
Способ 3: исправляем ссылки через меню надстройки FillDocuments
Описанное в способе 2 может сделать и сама надстройка.
Для этого есть специальный пункт в подменю «Дополнительно», но сначала это подменю надо включить.
Нажимаем кнопку Настройки, идём на вкладку «Дополнительно», и включаем там опцию «Отображать подменю ДОПОЛНИТЕЛЬНО на панели инструментов программы»:
После этого, на панели инструментов появится новое подменю Дополнительно, в котором нам нужно выбрать пункт Восстановить формулы:
Способ 4: исправляем ссылки на файл штатными средствами Excel
Нажмём кнопку Включить содержимое на желтом предупреждении системы безопасности Excel, появляющемся при открытии файла.
При это появится диалоговое окно, где нужно нажать кнопку Изменить связи.
Нажимаем кнопку Изменить, и в диалоговом окне указываем, где теперь расположен файл надстройки FillDocuments.xla
После того, как файл выбран, в графе Состояние будет написано ОК:
Теперь отстаётся запустить надстройку FillDocuments (если она до этого не была запущена), и Excel автоматически внесёт изменения в формулы во всём файле.
Читайте также: