Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Подстановка данных используя другую книгу как источник, с массой листов и отличиями в таблицах
 
#1
22.04.2020 17:01:58
Всем доброго дня.
Прошу помочь со следующим вопросом:
Есть 2 книги, в одной надо вывести результаты, используя данные из другой книги, где порядка 100 листов, причем данные ищем одинаковые, но на листах их расположение может отличаться, привести в единый порядок нельзя.

Я пробовал https://www.planetaexcel.ru/techniques/3/7453/, почитал про ВПР, массивы, Индекс, понял что это все работает только тогда, когда все данные на листах, имеют единую архитектуру, не отличаются. В результате собирается массив, где данные с листов просто скопированы друг под друга, и если есть разница в столбцах, то использовать этот массив корректно уже не выйдет.

В моем же случае, надо вводить код в книге "результат", и автоматически на этой строке в столбце Россия, должна появляться цена из книги "тест", причем проходясь по всем листам этой книги. Проблема в том, что хоть столбец код, везде в одном месте, столбец Россия может смещаться, то на странице есть 'лишний' столбец индекс, то нет столбца Россия. *Отмечу, в реальной таблице и Россия не всегда простая, Россия-МСК, Россия-МСК МО, Россия, но данные надо из них всех брать.

Я думал, что есть некий вариант поиска ячейки с искомым словом (Россия) и использованию данных, которые идут ниже, но ничего подобного не нашел.
Есть какой вариант решения проблемы? Или только через перевод всех листов в единый вид для ВПР или PowerQuery?
 
 
 
#2
22.04.2020 17:19:17
GreyAlex, так?
 
 
 
#3
22.04.2020 17:43:43
CainV, вроде того.
Но, подразумевается, что я вручную ввожу код, а поле напротив в столбе Россия, анализирует и подставляет значение. Значит, наверное в столбе Россия действует функция, которая смотрит, есть ли данные в поле код, и выдает ответ, используя базу.
Хотя вариант с выводом всех данных, тож интересен, не подумал об этом.

В принципе, пока пишу, домысливаю.
Я так понимаю, что вы взяли каждый лист, транспонировали данные, чтоб можно было отфильтровать просто одну страну.
И потом все таблицы свели в одну, которая выдает единый результат по всем кодам для Россия, либо можно использовать этот результат, для той самой подстановки, сделать формулу, которая будет обращаться к этой таблице и выводить результат.

Спасибо за пример. Но я уточнял один момент, у меня в реальности 100 с лишним листов, они не все поголовно разные, но отличия там есть, выходит что это все 100 листов, надо вот так руками настроить. Это довольно длительная работа, иных вариантов нет?
 
 
 
#4
22.04.2020 18:03:51
GreyAlex, вот такой вариант
Перед этим Вам необходимо будет превратить все свои листы в таблицы следующим макросом:
Код
Sub Tables()

Sheets(1).Select

For i = 1 To Sheets.Count

   Sheets(i).Activate

   Range("A1").Select

   Range(Selection, Selection.End(xlDown)).Select

   Range(Selection, Selection.End(xlToRight)).Select

   If ActiveSheet.ListObjects.Count < 1 Then

       ActiveSheet.ListObjects.Add.Name = ActiveSheet.Name

   End If

Next i

End Sub




Дальше просто подгружаете через файл и все, фильтровать можно как вам угодно: "Россия", "Рос" и т.д.
Изменено: CainV - 22.04.2020 18:05:24
 
 
 
#5
22.04.2020 19:50:00
CainV, большое спасибо!
Буду думать, начал пробовать на актуальной таблице, столкнулся с тем, что мусора там много. Макрос как я понимаю, преобразует все листы в умные таблицы, он у меня взбрыкнулся, что есть автофильтры, которые надо убрать. Начал смотреть в чем дело, оказалось что тут листы где по паре таблиц нарисовано, или вообще не пойми что есть) В общем, похоже, что вариант 1 который вы показали, придется применять, ручную обработку, убрать мусор, лишние листы и пр.

Тестовые книги то чистые, а то что в реальности есть, помойка...и да, вы могли верно понять, это не мои файлы, помогаю знакомым)

Точно так же встретил такой бред, что в таблицах есть на один код 2 цены, и в результате ВПР берет цену, та что выше)) в общем, буду пинать "заказчика".

Еще раз большое спасибо, интересный опыт. Радует что я вроде даже понимаю, что где происходит.
 
 
 
#6
22.04.2020 19:55:30
Цитата
GreyAlex написал: что есть автофильтры, которые надо убрать
Код
 Sub Auto_Open()   
    Dim xWs As Worksheet
    
    For Each Wks In ThisWorkbook.Worksheets
         On Error Resume Next

         If Wks.AutoFilterMode Then
               Wks.AutoFilterMode = False
         End If
    Next Wks
 End Sub
Изменено: CainV - 23.04.2020 00:13:18
 
 
 
Страницы: 1
Наверх