Страницы: 1
RSS
Сбор данных через функции ПОИСКПОЗ и ИНДЕКС из закрытых книг, Задание изменяемого пути для функций ПОИСКПОЗ и ИНДЕКС
 
Добрый день! Господа, пожалуйста, помогите решить такую простую на первый взгляд задачку. Прошлая задача не получила решения, потому была упрощена и быть может сейчас найду отклик

Итак. Имеем около 400 магазинов (в примере "Магазин А" и "Магазин Б")
Нужно получить один файл "Отчет общий" в котором будут отображены данные из файлов магазинов по следующему принципу:
формула берет из списка наименование (например, Яблоки) и ВНИМАНИЕ (!) не открывая файл Магазин А проверяет наличие этого товара в перечне магазина и если таковой находится возвращает значение цены в соответствующую строку столбца магазина А. Если же такого товара нет, как например "Картофель" и "Свекла", то просто пишется "Не найден", ну или даже сойдет ошибка формулы (потом ее условным форматированием выделю)

Все это реально и работает через Индекс и ПоискПоз, но дело в том, что таких товаров около 500, а магазинов 400 и вбивать формулу минимум 400 раз глупо.

Файлы магазинов расположены на диске в папке Торговля, которая далее делится на города, а в папке соответствующего города уже файлы "магазинов" с уникальными неповторяющимися именами. Например, Торговля\Москва\Магазин "Океан", Торговля\Киров\Магазин "Весна", Торговля\Киров\Магазин "Березка" и т.д.
Так вот, хотелось бы чтобы формула сама брала и формировала путь по принципу Торговля+Город+Название магазина, но сделать это у меня не получилось через функции ИНДЕКС и ПОИСКПОЗ.
Быть может нужно создать пользовательскую функцию или как-то еще?

Обращаю внимание, что не все магазины имеют весь ассортимент, а потому нужно чтобы формула сама искала. Она это в принципе и делает через ПОИСКПОЗ, но вот с заданием ПУТИ, как переменной у меня не сложилось. Спасибо всем откликнувшимся!
Если будут доступны варианты ЧЕРЕЗ ДРУГИЕ функции - это тоже приветствуется, ибо может я просто не знаю более простого и надежного способа. )
Изменено: SergeyPeshkov - 08.01.2020 20:42:23
 
Добрый день.
Для начала делаете так - ставите вручную свою формулу, закрываете файл магазина, включаете рекордер и типа правите формулу - получаете примерно такую запись (я ленивый, поэтому использовал ВПР()):
Код
    [B4].FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'C:\Users\Igor\Downloads\[магазин А.xlsx]Лист1'!C1:C2,2,0)"

Далее можно в этот код/строку подставлять нужный путь и название магазина. Обращаю внимание что нужно точно знать название листа, ну и расположение столбцов!
P.S. Даже так:
Код
    With [B4]
        .FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'C:\Users\Igor\Downloads\[магазин А.xlsx]Лист1'!C1:C2,2,0)"
        .AutoFill Destination:=Range("B4:B12"), Type:=xlFillDefault
    End With

Ещё в формулу можно сразу прописать что выводить если искомое не найдено, ну и в конце заменить формулу на значение.

Вот код на два магазина:
Код
Sub Макрос1()

    Dim arr, i&
    arr = Split("Магазин А|Магазин Б", "|")
    For i = 0 To UBound(arr)
        With Cells(4, i + 2)
            .FormulaR1C1 = _
            "=VLOOKUP(RC[-" & 1 + i & "],'C:\Users\Igor\Downloads\[" & arr(i) & ".xlsx]Лист1'!C1:C2,2,0)"
            .AutoFill Destination:=Range(Cells(4, i + 2), Cells(12, i + 2)), Type:=xlFillDefault
        End With
    Next
End Sub


Вот с учётом каталогов (см. http://prntscr.com/qkw5xx ), на два города и три магазина:
Код
Sub Макрос2()
    Dim i&
    For i = 2 To 4
        With Cells(4, i)
            .FormulaR1C1 = _
            "=VLOOKUP(RC[-" & i - 1 & "],'C:\Users\Igor\Downloads\Торговля\" & Cells(2, i).MergeArea(1) & "\[" & Cells(3, i) & ".xlsx]Лист1'!C1:C2,2,0)"
            .AutoFill Destination:=Range(Cells(4, i), Cells(12, i)), Type:=xlFillDefault
        End With
    Next
End Sub
Изменено: Hugo - 08.01.2020 18:39:58
 
Цитата
SergeyPeshkov написал: Нужно получить один файл "Отчет общий" в котором будут отображены данные из файлов магазинов по следующему принципу:
И это ОБЯЗАТЕЛЬНО с использованием ИНДЕКС/ПОИСКПОЗ? В таком случае ответ выше нужно удалить? Не по теме ведь!
 
Hugo,спасибо! Сейчас приеду домой и протестирую, думаю должно получиться ))
 
vikttur, желательно с этими функциями, т.к логика была следующая:
выбираем значение товара из отчета
при помощи ПОИСКПОЗ ищем в столбце товаров магазина такой же и если находим, то вернув его порядковый номер
через ИНДЕКС ищем значение соответственно цены для этого товара.

Думаю через ВПР будет тоже работать, но не помню уже почему, но последовал совету на форуме использовать связку ИНДЕКС/ПОИСКПОЗ.
 
Цитата
SergeyPeshkov написал: Думаю через ВПР будет тоже работат
Так почему в названии темы ТОЛЬКО две функции? Вы сами отметаете все другие решения!!!
Предложите новое название.
Страницы: 1
Наверх