Добрый день! Возник такой вопрос: можно ли автоматически изменить месяц в формуле, чтобы не использовать стандартную функцию "заменить", которая вызывает ошибки из-за "недопустимых ссылок"?
Данная формула используется во всём столбце, и при попытке заменить месяц с помощью функции "заменить" Excel выдаёт ошибку о "недопустимых ссылках". Например, при создании нового документа нужно изменить месяц на предыдущий, чтобы данные перешли из файла "ТО январь 2025" в новый документ "ТО февраль 2025".
Добрый день! Возник такой вопрос: можно ли автоматически изменить месяц в формуле, чтобы не использовать стандартную функцию "заменить", которая вызывает ошибки из-за "недопустимых ссылок"?
Данная формула используется во всём столбце, и при попытке заменить месяц с помощью функции "заменить" Excel выдаёт ошибку о "недопустимых ссылках". Например, при создании нового документа нужно изменить месяц на предыдущий, чтобы данные перешли из файла "ТО январь 2025" в новый документ "ТО февраль 2025".roman1403
cmivadwot, в одном файле у меня 8 разделов. Никак не получится все файлы собрать в одном документе. Каждый файл идет с названием "ТО январь 2025", "ТО февраль 2025" и т.д. Когда файл предыдущего месяца открыт - ссылка короткая, а при закрытии этого файла ссылка удлиняется, появляется длинный адрес '\\srv-file\ЭЖ_цеха_21\Техотчеты\2025\[ТО январь 2025.xlsx]. Предложенная формула не помогает.
cmivadwot, в одном файле у меня 8 разделов. Никак не получится все файлы собрать в одном документе. Каждый файл идет с названием "ТО январь 2025", "ТО февраль 2025" и т.д. Когда файл предыдущего месяца открыт - ссылка короткая, а при закрытии этого файла ссылка удлиняется, появляется длинный адрес '\\srv-file\ЭЖ_цеха_21\Техотчеты\2025\[ТО январь 2025.xlsx]. Предложенная формула не помогает.roman1403
roman1403, #### **Вариант 1. Power Query (Надстройка "Получить и преобразовать")** ✅ **Работает с закрытыми файлами** (достаточно, чтобы путь был доступен). ✅ **Автоматическое обновление** при изменении источника.
**Как настроить:** 1. **Данные** → **Получить данные** → **Из файла** → **Из книги Excel**. 2. Выбрать файл `ТО февраль 2025.xlsx`, указать лист `Р2` и диапазон. 3. В Power Query можно задать параметр (например, ячейку с месяцем) для динамического пути. 4. Загрузить в Excel → данные будут обновляться по кнопке **"Обновить все"**.
---
#### **Вариант 2. Макрос VBA (если Power Query не подходит)** ✅ Можно написать код, который будет: - Брать значение месяца из ячейки. - Открывать файл в фоне → копировать данные → закрывать. - Работает даже с закрытыми файлами, но требует **настройки путей**.
**Пример кода:** ```vba [vba]
Код
Sub LoadDataFromClosedFile() Dim monthYear As String Dim sourcePath As String Dim sourceFile As String Dim targetSheet As Worksheet Dim sourceRange As Range
' Конвертируем формулы в значения (чтобы не зависеть от источника) targetSheet.Range("C2:C100").Value = targetSheet.Range("C2:C100").Value End Sub
[/vba] ``` > **Минус:** формулы превращаются в значения (если нужно динамическое обновление, лучше Power Query).
roman1403, #### **Вариант 1. Power Query (Надстройка "Получить и преобразовать")** ✅ **Работает с закрытыми файлами** (достаточно, чтобы путь был доступен). ✅ **Автоматическое обновление** при изменении источника.
**Как настроить:** 1. **Данные** → **Получить данные** → **Из файла** → **Из книги Excel**. 2. Выбрать файл `ТО февраль 2025.xlsx`, указать лист `Р2` и диапазон. 3. В Power Query можно задать параметр (например, ячейку с месяцем) для динамического пути. 4. Загрузить в Excel → данные будут обновляться по кнопке **"Обновить все"**.
---
#### **Вариант 2. Макрос VBA (если Power Query не подходит)** ✅ Можно написать код, который будет: - Брать значение месяца из ячейки. - Открывать файл в фоне → копировать данные → закрывать. - Работает даже с закрытыми файлами, но требует **настройки путей**.
**Пример кода:** ```vba [vba]
Код
Sub LoadDataFromClosedFile() Dim monthYear As String Dim sourcePath As String Dim sourceFile As String Dim targetSheet As Worksheet Dim sourceRange As Range
cmivadwot, почти 600 постов уже, а макрос оформлять не научились? Исправьте свой пост - зайдите в редактирование, выделите код макроса, нажмите кнопку # на панели, сохраните Кстати, формулу в посте 2 тоже оформите нормально. Все также, только кнопка fx * Добавлено - пока писал, код макроса уже поправили. Супер, осталось еще с формулой разобраться ))))))
По теме вопроса - ДВССЫЛ с закрытыми книгами не работает, поэтому да, макрос, Квери, сводные таблицы, ... Я иногда, если нужно именно просто заменить ссылки, ничего при этом больше не меняя, делаю так: Данные - Изменить связи, вылезает окошко выбора файла, который нужно изменить, выбираем, жмем Изменить, вылезает окошко выбора файла, НА который нужно изменить - К - Закрыть Но файлы должны быть с одинаковым расположение данных Все это, конечно, можно сделать и макросом
cmivadwot, почти 600 постов уже, а макрос оформлять не научились? Исправьте свой пост - зайдите в редактирование, выделите код макроса, нажмите кнопку # на панели, сохраните Кстати, формулу в посте 2 тоже оформите нормально. Все также, только кнопка fx * Добавлено - пока писал, код макроса уже поправили. Супер, осталось еще с формулой разобраться ))))))
По теме вопроса - ДВССЫЛ с закрытыми книгами не работает, поэтому да, макрос, Квери, сводные таблицы, ... Я иногда, если нужно именно просто заменить ссылки, ничего при этом больше не меняя, делаю так: Данные - Изменить связи, вылезает окошко выбора файла, который нужно изменить, выбираем, жмем Изменить, вылезает окошко выбора файла, НА который нужно изменить - К - Закрыть Но файлы должны быть с одинаковым расположение данных Все это, конечно, можно сделать и макросом_Boroda_
Sub LoadDataFromClosedFile() Dim monthYear As String Dim sourcePath As String Dim sourceFile As String Dim targetSheet As Worksheet Dim lastRow As Long
' Получаем значение месяца из ячейки B1 monthYear = ThisWorkbook.Sheets("Отчет").Range("B1").Value
' Проверяем, что значение не пустое If monthYear = "" Then MsgBox "Укажите месяц и год в ячейке B1 (например, 'январь 2025')", vbExclamation Exit Sub End If
' Формируем путь к файлу (двойные слеши для VBA) sourcePath = "\\srv-file\ЭЖ_цеха_21\Техотчеты\2025\" sourceFile = sourcePath & "ТО " & monthYear & ".xlsx"
' Проверяем существование файла If Dir(sourceFile) = "" Then MsgBox "Файл не найден: " & sourceFile, vbCritical Exit Sub End If
' Определяем последнюю строку с данными в столбце A On Error Resume Next lastRow = ThisWorkbook.Sheets("Отчет").Cells(ThisWorkbook.Sheets("Отчет").Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then MsgBox "Нет данных для обработки в столбце A", vbExclamation Exit Sub End If On Error GoTo 0
' Указываем лист для вывода данных Set targetSheet = ThisWorkbook.Sheets("Отчет")
' Записываем формулу массива с полным путем With targetSheet.Range("C2:C" & lastRow) .FormulaArray = "=IFERROR(INDEX('" & sourcePath & "[ТО " & monthYear & ".xlsx]Р2'!$M$10:$M$200, " & _ "MATCH(A2:A" & lastRow & ", '" & sourcePath & "[ТО " & monthYear & ".xlsx]Р2'!$A$10:$A$200, 0)), """")" .Value = .Value ' Конвертируем в значения End With
MsgBox "Данные успешно загружены!", vbInformation End Sub
[/vba]
roman1403, попробуйте так [vba]
Код
Sub LoadDataFromClosedFile() Dim monthYear As String Dim sourcePath As String Dim sourceFile As String Dim targetSheet As Worksheet Dim lastRow As Long
' Получаем значение месяца из ячейки B1 monthYear = ThisWorkbook.Sheets("Отчет").Range("B1").Value
' Проверяем, что значение не пустое If monthYear = "" Then MsgBox "Укажите месяц и год в ячейке B1 (например, 'январь 2025')", vbExclamation Exit Sub End If
' Формируем путь к файлу (двойные слеши для VBA) sourcePath = "\\srv-file\ЭЖ_цеха_21\Техотчеты\2025\" sourceFile = sourcePath & "ТО " & monthYear & ".xlsx"
' Проверяем существование файла If Dir(sourceFile) = "" Then MsgBox "Файл не найден: " & sourceFile, vbCritical Exit Sub End If
' Определяем последнюю строку с данными в столбце A On Error Resume Next lastRow = ThisWorkbook.Sheets("Отчет").Cells(ThisWorkbook.Sheets("Отчет").Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then MsgBox "Нет данных для обработки в столбце A", vbExclamation Exit Sub End If On Error GoTo 0
' Указываем лист для вывода данных Set targetSheet = ThisWorkbook.Sheets("Отчет")
' Записываем формулу массива с полным путем With targetSheet.Range("C2:C" & lastRow) .FormulaArray = "=IFERROR(INDEX('" & sourcePath & "[ТО " & monthYear & ".xlsx]Р2'!$M$10:$M$200, " & _ "MATCH(A2:A" & lastRow & ", '" & sourcePath & "[ТО " & monthYear & ".xlsx]Р2'!$A$10:$A$200, 0)), """")" .Value = .Value ' Конвертируем в значения End With
MsgBox "Данные успешно загружены!", vbInformation End Sub