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

Вход

Регистрация

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

 

= Мир MS Excel/Изменение месяца в формуле автоматически - Мир MS Excel

  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Изменение месяца в формуле автоматически
roman1403 Дата: Четверг, 27.03.2025, 16:38 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 46
Репутация: 0 ±
Замечаний: 0% ±

Добрый день!
Возник такой вопрос: можно ли автоматически изменить месяц в формуле, чтобы не использовать стандартную функцию "заменить", которая вызывает ошибки из-за "недопустимых ссылок"?

Код
=ИНДЕКС('[ТО январь 2025.xlsx]Р2'!$M$10:$M$200;ПОИСКПОЗ(A9;'[ТО январь 2025.xlsx]Р2'!$A$10:$A$200;0))


Данная формула используется во всём столбце, и при попытке заменить месяц с помощью функции "заменить" Excel выдаёт ошибку о "недопустимых ссылках". Например, при создании нового документа нужно изменить месяц на предыдущий, чтобы данные перешли из файла "ТО январь 2025" в новый документ "ТО февраль 2025".
 
Ответить
СообщениеДобрый день!
Возник такой вопрос: можно ли автоматически изменить месяц в формуле, чтобы не использовать стандартную функцию "заменить", которая вызывает ошибки из-за "недопустимых ссылок"?

Код
=ИНДЕКС('[ТО январь 2025.xlsx]Р2'!$M$10:$M$200;ПОИСКПОЗ(A9;'[ТО январь 2025.xlsx]Р2'!$A$10:$A$200;0))


Данная формула используется во всём столбце, и при попытке заменить месяц с помощью функции "заменить" Excel выдаёт ошибку о "недопустимых ссылках". Например, при создании нового документа нужно изменить месяц на предыдущий, чтобы данные перешли из файла "ТО январь 2025" в новый документ "ТО февраль 2025".

Автор - roman1403
Дата добавления - 27.03.2025 в 16:38
cmivadwot Дата: Четверг, 27.03.2025, 20:43 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 599
Репутация: 115 ±
Замечаний: 0% ±

365
roman1403, Использование ячейки с указанием месяца
1. Создайте ячейку (например, B1) с названием месяца: "февраль 2025"
2. Измените формулу:
Код
=ИНДЕКС(ДВССЫЛ("'[ТО "&B1&".xlsx]Р2'!$M$10:$M$200"); ПОИСКПОЗ(A9;ДВССЫЛ("'[ТО "&B1&".xlsx]Р2'!$A$10:$A$200");0))

Файл с которого берутся данные должен быть открыт.
Как вариант все листы - ТО месяцы залить в один файл и примерно по той же схеме.


Сообщение отредактировал cmivadwot - Пятница, 28.03.2025, 09:34
 
Ответить
Сообщениеroman1403, Использование ячейки с указанием месяца
1. Создайте ячейку (например, B1) с названием месяца: "февраль 2025"
2. Измените формулу:
Код
=ИНДЕКС(ДВССЫЛ("'[ТО "&B1&".xlsx]Р2'!$M$10:$M$200"); ПОИСКПОЗ(A9;ДВССЫЛ("'[ТО "&B1&".xlsx]Р2'!$A$10:$A$200");0))

Файл с которого берутся данные должен быть открыт.
Как вариант все листы - ТО месяцы залить в один файл и примерно по той же схеме.

Автор - cmivadwot
Дата добавления - 27.03.2025 в 20:43
roman1403 Дата: Пятница, 28.03.2025, 08:20 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 46
Репутация: 0 ±
Замечаний: 0% ±

cmivadwot, в одном файле у меня 8 разделов. Никак не получится все файлы собрать в одном документе. Каждый файл идет с названием "ТО январь 2025", "ТО февраль 2025" и т.д. Когда файл предыдущего месяца открыт - ссылка короткая, а при закрытии этого файла ссылка удлиняется, появляется длинный адрес '\\srv-file\ЭЖ_цеха_21\Техотчеты\2025\[ТО январь 2025.xlsx]. Предложенная формула не помогает.
 
Ответить
Сообщениеcmivadwot, в одном файле у меня 8 разделов. Никак не получится все файлы собрать в одном документе. Каждый файл идет с названием "ТО январь 2025", "ТО февраль 2025" и т.д. Когда файл предыдущего месяца открыт - ссылка короткая, а при закрытии этого файла ссылка удлиняется, появляется длинный адрес '\\srv-file\ЭЖ_цеха_21\Техотчеты\2025\[ТО январь 2025.xlsx]. Предложенная формула не помогает.

Автор - roman1403
Дата добавления - 28.03.2025 в 08:20
cmivadwot Дата: Пятница, 28.03.2025, 08:51 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 599
Репутация: 115 ±
Замечаний: 0% ±

365
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

monthYear = ThisWorkbook.Sheets("Лист1").Range("B1").Value ' Ячейка с "февраль 2025"
sourcePath = "C:\Путь\К\Файлам\"
sourceFile = sourcePath & "ТО " & monthYear & ".xlsx"

Set targetSheet = ThisWorkbook.Sheets("Лист1")

' Данные из закрытого файла (через ADO или формулу массива)
targetSheet.Range("C2:C100").FormulaArray = _
"=ИНДЕКС('" & sourcePath & "[ТО " & monthYear & ".xlsx]Р2'!$M$10:$M$200; " & _
"ПОИСКПОЗ(A2:A100; '" & sourcePath & "[ТО " & monthYear & ".xlsx]Р2'!$A$10:$A$200; 0))"

' Конвертируем формулы в значения (чтобы не зависеть от источника)
targetSheet.Range("C2:C100").Value = targetSheet.Range("C2:C100").Value
End Sub
[/vba]
```
> **Минус:** формулы превращаются в значения (если нужно динамическое обновление, лучше Power Query).


Сообщение отредактировал cmivadwot - Пятница, 28.03.2025, 09:32
 
Ответить
Сообщение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

monthYear = ThisWorkbook.Sheets("Лист1").Range("B1").Value ' Ячейка с "февраль 2025"
sourcePath = "C:\Путь\К\Файлам\"
sourceFile = sourcePath & "ТО " & monthYear & ".xlsx"

Set targetSheet = ThisWorkbook.Sheets("Лист1")

' Данные из закрытого файла (через ADO или формулу массива)
targetSheet.Range("C2:C100").FormulaArray = _
"=ИНДЕКС('" & sourcePath & "[ТО " & monthYear & ".xlsx]Р2'!$M$10:$M$200; " & _
"ПОИСКПОЗ(A2:A100; '" & sourcePath & "[ТО " & monthYear & ".xlsx]Р2'!$A$10:$A$200; 0))"

' Конвертируем формулы в значения (чтобы не зависеть от источника)
targetSheet.Range("C2:C100").Value = targetSheet.Range("C2:C100").Value
End Sub
[/vba]
```
> **Минус:** формулы превращаются в значения (если нужно динамическое обновление, лучше Power Query).

Автор - cmivadwot
Дата добавления - 28.03.2025 в 08:51
китин Дата: Пятница, 28.03.2025, 09:21 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 7034
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
cmivadwot, ну ведь не первый день на форуме. оформите ваши посты по правилам


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеcmivadwot, ну ведь не первый день на форуме. оформите ваши посты по правилам

Автор - китин
Дата добавления - 28.03.2025 в 09:21
cmivadwot Дата: Пятница, 28.03.2025, 09:30 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 599
Репутация: 115 ±
Замечаний: 0% ±

365
китин, да капец...с телефона..не могу чёт завернуть код..изловчился до всплытия окна после выделения))


Сообщение отредактировал cmivadwot - Пятница, 28.03.2025, 09:33
 
Ответить
Сообщениекитин, да капец...с телефона..не могу чёт завернуть код..изловчился до всплытия окна после выделения))

Автор - cmivadwot
Дата добавления - 28.03.2025 в 09:30
_Boroda_ Дата: Пятница, 28.03.2025, 09:33 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16880
Репутация: 6586 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
cmivadwot, почти 600 постов уже, а макрос оформлять не научились? Исправьте свой пост - зайдите в редактирование, выделите код макроса, нажмите кнопку # на панели, сохраните
Кстати, формулу в посте 2 тоже оформите нормально. Все также, только кнопка fx
* Добавлено - пока писал, код макроса уже поправили. Супер, осталось еще с формулой разобраться ))))))

По теме вопроса - ДВССЫЛ с закрытыми книгами не работает, поэтому да, макрос, Квери, сводные таблицы, ...
Я иногда, если нужно именно просто заменить ссылки, ничего при этом больше не меняя, делаю так: Данные - Изменить связи, вылезает окошко выбора файла, который нужно изменить, выбираем, жмем Изменить, вылезает окошко выбора файла, НА который нужно изменить - К - Закрыть
Но файлы должны быть с одинаковым расположение данных
Все это, конечно, можно сделать и макросом


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщениеcmivadwot, почти 600 постов уже, а макрос оформлять не научились? Исправьте свой пост - зайдите в редактирование, выделите код макроса, нажмите кнопку # на панели, сохраните
Кстати, формулу в посте 2 тоже оформите нормально. Все также, только кнопка fx
* Добавлено - пока писал, код макроса уже поправили. Супер, осталось еще с формулой разобраться ))))))

По теме вопроса - ДВССЫЛ с закрытыми книгами не работает, поэтому да, макрос, Квери, сводные таблицы, ...
Я иногда, если нужно именно просто заменить ссылки, ничего при этом больше не меняя, делаю так: Данные - Изменить связи, вылезает окошко выбора файла, который нужно изменить, выбираем, жмем Изменить, вылезает окошко выбора файла, НА который нужно изменить - К - Закрыть
Но файлы должны быть с одинаковым расположение данных
Все это, конечно, можно сделать и макросом

Автор - _Boroda_
Дата добавления - 28.03.2025 в 09:33
cmivadwot Дата: Пятница, 28.03.2025, 09:35 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 599
Репутация: 115 ±
Замечаний: 0% ±

365
_Boroda_, читайте выше...
 
Ответить
Сообщение_Boroda_, читайте выше...

Автор - cmivadwot
Дата добавления - 28.03.2025 в 09:35
_Boroda_ Дата: Пятница, 28.03.2025, 09:46 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16880
Репутация: 6586 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
cmivadwot, читайте выше ))))))
:D :D

* Добавлено - пока писал, ...


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщениеcmivadwot, читайте выше ))))))
:D :D

* Добавлено - пока писал, ...

Автор - _Boroda_
Дата добавления - 28.03.2025 в 09:46
cmivadwot Дата: Пятница, 28.03.2025, 10:01 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 599
Репутация: 115 ±
Замечаний: 0% ±

365
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
[/vba]


Сообщение отредактировал cmivadwot - Пятница, 28.03.2025, 10:13
 
Ответить
Сообщение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
[/vba]

Автор - cmivadwot
Дата добавления - 28.03.2025 в 10:01
  • Страница 1 из 1
  • 1
Поиск:

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