Страницы: 1
RSS
Привязка формул разных книг к одной
 
Всем привет!
Имею много одинаковых таблиц в книгах, выполненных по одному шаблону, со временем приходиться редактировать формулы в данных книгах. А книг очень много, при том в каждой книге 21 формула и каждый раз при изменении части формулы приходиться менять их во всех файлах. Для удобства создал "книгу формул" в которой меняю формулу, а потом заменяю в книгах. Возможно автоматизировать этот процесс? Думал про макроредактор, возможно есть вариант попроще?

Данная тема открыта: http://www.excelworld.ru/forum/10-42365-1
Изменено: adamm - 12.07.2019 17:28:41
 
возможно можно, только это как то не по фен-шую, что ли.
А чем обусловлена необходимость иметь очень много одинаковых книг?
 
Каждая книга заполняется инженером по своему шифру объекта, а форма для всех одна, таких журналов у меня уже 15, а будет ещё больше. А формулы я частенько меняю, по причине того, что приходиться добавлять новые условия особенно в ячейках столбцов с BJ по BU, в примере я выложил только формулы, второй лист куда они ссылаются я очистил, что бы не загружать вас лишней информацией. Все ячейки с формулами подкрасил зелёным цветом
 
В продолжение разговора, точнее монолога).
Решение почти нашёл. Через PQ я выгружаю формулы с шаблона, но без знака "=". Получаю так сказать текстовую часть формулы, но вот как преобразовать её в полноценную формулу не получается, пробовал через сцепить не выходит, приложил простой пример.
 
Выгружайте формулы после замены "равно" на другой символ, которого нет в формулах. После выгрузки - НАЙТИ/ЗАМЕНИТЬ символ на  "равно"
 
adamm, может здесь что полезное найдете. для массивных формул, если не ошибаюсь, Aray как то используют.
 
Спасибо всем!
vikttur, сделал проще, формулу сцепил в PQ с "=", но при выгрузке получаю текстовое значение, и приходиться каждую ячейку перещёлкивать мышкой, или менять "=" на "=", тогда превращается в нормальную рабочую формулу
 
Цитата
adamm написал:
приходиться каждую ячейку перещёлкивать мышкой
здесь макрос есть. но не работает с массивными формулами
 
artyrH, да я так и сделал, но опять маленькая проблема, формулы в моём случае не протягиваются, но тут думаю много "гемороя", проще записать макроредактором автопротягивание
да и с формулой массива не получается ввести данные...
По первому, возможно склеенную формулу запихать в диспетчер имен?
По второму я думаю получится ввести формулу кодом через "FormulaArray"

Изменено: adamm - 15.07.2019 10:35:59
 
adamm, и еще один минус есть. макрос надо применить только один раз. если применить второй раз, то в ячейках останутся только значения
 
Цитата
artyrH написал:
останутся только значения
думаю это не страшно, возможно да же лучше, так как формулы тяжёлые, файл немного подвисает. Или обновить запрос в PQ
Изменено: adamm - 15.07.2019 10:38:52
 
adamm,
правильно ли я понял
1. есть таблица с соответствием поля  и формулы ,с примечанием, массивная или нет
2. требуется автоматизировать замену формул в другой книге в советующих полях таблиц

Ну то что замену делать макросом - это понятно, и не так сложно. Получить таблицу соответсвий, пробежаться по полям и заменить формулы, не великая печаль. Но
1. вы используете RUS и в случае другой локализации офиса ваш вариант не сработает совсем
2. предположим локализация идентична, тогда надо  
вставить вставить текст формулу с префиксом = в Range.FormulaLocal
и потом сразу   Range.FormulaArray=RangeFormula, если формула массивная.
заполнить на нужное количество.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
правильно ли я понял
Да, вы меня правильно поняли. Касаемо локализации, она не поменяется, если я правильно вас понял, локализация (язык рус или англ).
 
Цитата
adamm написал:
локализация (язык рус или англ).
да, про нее. Просто я работаю всегда в Eng и текст формулы ни под каким соусом не будет принят.
По вопросам из тем форума, личку не читаю.
 
adamm, попробуйте этот запрос
Код
let
   a=Table.FromList({1..1200}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(a,{{"Column1", "Column"}}),
    Источник = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Столбец1", type text}}),
    #"Transposed Table" = Table.Transpose(#"Измененный тип")&#"Renamed Columns",
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4"}),
    #"Inserted Addition" = Table.AddColumn(#"Filled Down", "Сложение", each [Column] + 10, type number ),
    #"Added to Column" = Table.TransformColumns(#"Inserted Addition", {{"Column", each _ + 3, type  number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Added to Column",{{"Column", type text}, {"Сложение", type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Changed Type", {{"Column", each _ & ":", type text}}),
    #"Added Suffix1" = Table.TransformColumns(#"Added Suffix", {{"Сложение", each _ & ")", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Added Suffix1","3:", each _ [Column],Replacer.ReplaceText,{"Column1" , "Column2" , "Column3" , "Column4"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","10)",each _ [Сложение],Replacer.ReplaceText,{"Column1", "Column2", "Column3", "Column4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Column", "Сложение"})
in
    #"Removed Columns"
 
artyrH, спасибо, попробую адаптировать под свою формулу, но боюсь будет не просто) у меня трехэтажные формулы, в примере я упростил формулу.
 
В очередной раз всем привет!
Воспользовался советом artyrH, первым способом, при помощи кода, но вот не хочет он работать с моим файлом...
 
подходит ли вариант копировать данные в файл-шаблон с формулами?
 
На данный момент организовал выгрузку данных следующим образом:
1. С шаблона выгружаю формулы через QueryTables
Код
Sub Запрос_формул_основные()
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D\formula.txt", Destination:=Sheets("Справочно").Range("AR2"))
        .Name = "formula"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .RefreshStyle = 0
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1251
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
     
    End With
End Sub
2. Далее для каждой формулы, прописал код для копирования в имена, да немного топорно, но лучше чем ручками каждый раз вставлять
Код
Sub Всего_замечаний()
Sheets("Основные линии").Range("BH3").Formula = Sheets("Справочно").Range("AR2").Formula
On Error Resume Next
ThisWorkbook.Worksheets("Основные линии").Names("Всего_замечаний").Delete
On Error GoTo 0
ThisWorkbook.Worksheets("Основные линии").Names.Add Name:="Всего_замечаний", RefersToR1C1:=Sheets("Основные линии").Range("BH3").FormulaR1C1
Sheets("Основные линии").Range("BH3").Formula = "=Всего_замечаний"
End Sub
 
Цитата
adamm написал:
Далее для каждой формулы, прописал код для копирования в имена
Правильнее конечно было сделать простую функцию, куда передавать что  и куда. И её уже использовать. Но и это уже на Ваше усмотрение.
По вопросам из тем форума, личку не читаю.
 
БМВ,
Ну по сути, так и есть, 2 код это только одна формула, на каждую формулу свой код, для примера я выложил только одну формулу. Далее я наверное его объединю убрав End Sub и Sub в промежутках.
 
Цитата
adamm написал:
Далее я наверное его объединю убрав End Sub и Sub в промежутках.
Вот этого то и опасаюсь :-)
Если не передавать объекты, то вот так имел в виду
Код
Sub XXX(ByRef strSourceSheetName As String, ByRef strSourceRange As String, ByRef strDestSheetname As String, ByRef strDestRange As String, ByRef strName As String)
With ThisWorkbook.Worksheets(strDestSheetname)
    .Range(strDestRange).Formula = Sheets(strSourceSheetName).Range(strSourceRange).Formula
    On Error Resume Next
    .Names(strName).Delete
    On Error GoTo 0
    .Names.Add Name:=strName, RefersToR1C1:=.Range(strDestRange).FormulaR1C1
    .Range(strDestRange).Formula = "=" & strName
End With
End Sub
Sub Всего_замечаний()
    Call XXX("Справочно", "AR2", "Основные линии", "BH3", "Всего_замечаний")
End Sub
Изменено: БМВ - 24.07.2019 10:21:31
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Вот этого то и опасаюсь :-)
Уже пробовал, все работает!
По сути код в дальнейшем можно упростить, до
Код
Sub Всего_замечаний()
ThisWorkbook.Worksheets("Основные линии").Names.Add Name:="Всего_замечаний", RefersToR1C1:=Sheets("Справочно").Range("AR2").FormulaR1C1
End Sub
Так как имена уже будут созданы, их можно просто менять, при необходимости
Изменено: adamm - 24.07.2019 10:19:25
 
Цитата
adamm написал:
Уже пробовал, все работает!
да =работать то работает, только это неправильно. Я просто накидал выше пример.
Цитата
adamm написал:
сути код в дальнейшем можно упростить
и получить нерабочий. Если переменные уже есть, то уже не ADD а просто замена
Код
ThisWorkbook.Worksheets("Основные линии").Names("Всего_замечаний").RefersToR1C1:=Sheets("Справочно").Range("AR2").FormulaR1C1
По вопросам из тем форума, личку не читаю.
 
БМВ, А уточните пожалуйста, чем код в #22 посте отличается?
Но опять же если я уже ввел в диспетчер имен имя, в дальнейшем мне его нужно только заменять (саму формулу), ведь на листе оно уже будет существовать, как имя, или я что то ппутаю? ДА в идеале бы конечно, код упростить до возможности только создать в диспетчер имя на основании данных с листа "справочно"
Изменено: adamm - 24.07.2019 10:54:35
 
только тем что подпрограмму потом нужно только вызвать , с параметрами, и если нужно что-то в ней изменить, как в случае с оптимизации, то меняется в одном месте, а не в куче. Использование With не принципиально, но рекомендуемо.
По вопросам из тем форума, личку не читаю.
 
Спасибо! теперь вроде как понятно.
На просторах нарыл вот такое решение https://excelvba.ru/code/NamesList, вот только жалко, что нет обратного решения)
 
adamm,У Игоря хорошие решения, просто это для другой цели, ну а обратное не проблема, только основной момент - это подготовка того что нужно загрузить в имена, чтоб все ссылки на ячейки не поехали. По идее R1C1 стиль это решает.
Более того, в одной из тем, о том что пробежаться по списку и создать я писал, и главное совершенно не нужно делить на массивные и нет, просто в имена запихнули, в ячейке результат.
По вопросам из тем форума, личку не читаю.
 
Оставил пока все как есть, вроде все работает, в результате имею 78 имен в диспетчере), три общих кода для трех листов, и один запрос, с подключением к шаблону
Изменено: adamm - 24.07.2019 11:23:08
Страницы: 1
Наверх