ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Замена формул с СУММЕСЛИ на ВПР
8 сообщений из 8, страница 1 из 1
Замена формул с СУММЕСЛИ на ВПР
    #35151686
dmitser
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmitser
Гость
Помогите чайнику:

Нужен макрос, который бы заменял функцию СУММЕСЛИ на ВПР (суммесли использовалась только для поиска значений).
Пример:
функция с СУММЕСЛИ сейчас
=СУММЕСЛИ('\\Server\Финплан_2008\[Калькуляция_01.2008.xls]Кальк.'!$D$6:$D$195;D27;'\\Server\Финплан_2008\[Калькуляция_01.2008.xls]Кальк.'!$FS$6:$FS$195)
Функция с ВПР, которая нужна
=ВПР(D27;'\\Server\Финплан_2008\[Калькуляция_01.2008.xls]Кальк.'!$D$6:$FS$195;172;Ложь)

Знаю, что это вполне реально, но в VB я не в зуб ногой.

Столкнулся с проблемой СУММЕСЛИ, когда начал переходить с 2000-го на 2003 MS Office. Оказалось, что фин.отдел лет 5-6 в бюджетах и финпланах пользовался функцией СУММЕСЛИ для поиска значений в других книгах. СУММЕСЛИ требует открытия всех связанных фалов. Но раньше финотдел это не напрягало, так как они при открытии файлов никогда не обновляли связи. При переходе на 2003 Еxcel видать решил пересчитать формулы и без открытия связанных файлов не хочет считать. Открыть все файлы не реально. Поменять вручную все СУММЕСЛИ на ВПР тоже... :(
...
Рейтинг: 0 / 0
23.02.2008, 13:11:27
| Ответить | Цитировать | Написать  
Замена формул с СУММЕСЛИ на ВПР
    #35152268
Сергей06
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей06
Участник
Возможно лучше вместо ВПР() использовать СУММПРОИЗВ().
Как она работает можно посмотреть здесь:
http://www.sql.ru/forum/actualthread.aspx?tid=527003
http://www.sql.ru/forum/actualthread.aspx?tid=515466&hl=%f1%f3%ec%ec%ef%f0%ee%e8%e7%e2
...
Рейтинг: 0 / 0
24.02.2008, 10:28:51
| Ответить | Цитировать | Написать  
Замена формул с СУММЕСЛИ на ВПР
    #35152275
Сергей06
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей06
Участник
Думаю, что можно использовать следующий алгоритм:

Поочередно открыть файлы, и вручную (макросом не знаю как, но может люди подскажут) изменить ОДНУ формулу Суммесли() на ВПР() или суммпроизв().
После этого, копировать измененную ячейку во все ячейки в которых есть суммесли().

Примеры и коды обработки нескольких книг:
http://sql.ru/forum/actualthread.aspx?tid=456013&hl=%ea%e0%f2%e0%eb%ee%e3#4442011
Найти и выделить ячеки по условию:
http://www.sql.ru/forum/actualthread.aspx?tid=513219
...
Рейтинг: 0 / 0
24.02.2008, 10:39:20
| Ответить | Цитировать | Написать  
Замена формул с СУММЕСЛИ на ВПР
    #35152344
dmitser
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmitser
Гость
Макрос по замене функции с СуммЕсли на ВПР, может коряво, но сделал
На данный момент макрос делает следущее:
из:
=СУММЕСЛИ('\\Serv\Финплан_2007\12.2007. (01)\С.с.12.2007\[Калькуляция с.с.12.2007 (3).xls]Кальк.'!$D$6:$D$195;D55;'\\Serv\Финплан_2007\12.2007. (01)\С.с.12.2007\[Калькуляция с.с.12.2007 (3).xls]Кальк.'!$FS$6:$FS$195)

преобразует в:
=ВПР(D55;'\\Serv\Финплан_2007\12.2007. (01)\С.с.12.2007\[Калькуляция с.с.12.2007 (3).xls]Кальк.'!$D$6:$FS$195;172;ЛОЖЬ)

Работает тока на активной ячейке. Тоесть нуна самому отыскивать функции с СуммЕсли, вставать на эту ячейку и запускать макрос...

Сейчас хочу сделать автоматический поиск по листу функции СуммЕсли и автоматической замены, но пока не знаю как :(

авторПосле этого, копировать измененную ячейку во все ячейки в которых есть суммесли().

Довольно нудное и долгое занятие, учитывая что порядка в книгах нет... В одной таблице идентичные значения могут быть получены либо функцией СуммЕсли, либо простой ссылкой на ячейку, либо вообще забиты ручками.
...
Рейтинг: 0 / 0
24.02.2008, 12:08:59
| Ответить | Цитировать | Написать  
Замена формул с СУММЕСЛИ на ВПР
    #35152393
Pavel55
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pavel55
Гость
Смотрите метод Find в справке VBA.
Вот пример, который ищет СУММЕСЛИ и заменяет на ВПР

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Sub ReplaceSumIF()
Dim iText As String
Dim iFoundRng As Range
    iText = "СУММЕСЛИ"
    With ActiveSheet.UsedRange
        Set iFoundRng = .Find(What:=iText, LookIn:=xlFormulas, LookAt:=xlPart)
        If iFoundRng Is Nothing Then
            MsgBox "Значение " & iText & " на листе не найдено!", vbExclamation, "Ошибка"
            Exit Sub
        End If
        firstAddress = iFoundRng.Address
        Do
            'тут вставляете свой макрос по изменению формулы,
            'как пример
            iFoundRng.FormulaLocal = "ВПР"
            
            Set iFoundRng = .FindNext(iFoundRng)
        Loop While Not iFoundRng Is Nothing
    End With
    MsgBox "Замена завершена!", vbInformation, ""
End Sub
...
Рейтинг: 0 / 0
24.02.2008, 13:24:15
| Ответить | Цитировать | Написать  
Замена формул с СУММЕСЛИ на ВПР
    #35152395
Pavel55
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Pavel55
Гость
кхе, удалите в моём макросе строку

firstAddress = iFoundRng.Address
...
Рейтинг: 0 / 0
24.02.2008, 13:26:01
| Ответить | Цитировать | Написать  
Замена формул с СУММЕСЛИ на ВПР
    #35152575
dmitser
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmitser
Гость
Пасиба за помощь...
Только данный макрос ищет СуммЕсли среди значений...
Как сделать, чтобы искал в формулах?
...
Рейтинг: 0 / 0
24.02.2008, 17:24:43
| Ответить | Цитировать | Написать  
Замена формул с СУММЕСЛИ на ВПР
    #35152827
dmitser
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmitser
Гость
Разобрался.... Опять на теже грабли... VB нуна задавать поиск не "СуммЕсли", а "SumIF" ...
...
Рейтинг: 0 / 0
24.02.2008, 22:45:53
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Замена формул с СУММЕСЛИ на ВПР
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&fpage=243&tid=2181371]:
 0ms
get settings:
 7ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 30ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 209ms
total:  315ms
созд. / загр.: 315ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]