функция ВПР

[oleg11]

KL

спасибо.
сейчас попробую.

[oleg11]

KL

что-то не идет-(
Сообщает о синтаксической ошибке.
а можете ли дать пример более полного скрипта для даного случая?

[KL]

KL

что-то не идет-(
Сообщает о синтаксической ошибке.
а можете ли дать пример более полного скрипта для даного случая?

Все-таки наверное будет лучше если ТЫ дашь пример скрипта, который сообщает о синтаксической ошибке. Тебе не кажется, что мы тогда быстрее узнаем в чем твоя проблема?

[oleg11]

KL

я только начал изучене VBA, поэтому в моем скрипте может быть полная чушь. быть может легче, написать мне пример поиска-вставки даных для одной позиции а я продолжу по аналогии. если конечно это возможно.

[KL]

KL

я только начал изучене VBA, поэтому в моем скрипте может быть полная чушь. быть может легче, написать мне пример поиска-вставки даных для одной позиции а я продолжу по аналогии. если конечно это возможно.

Конечно возможно! Вешай файл с детальным описанием задачи, примером данных и с указанием правильного ответа, а я тебе - скрипт. Или пример выдумывать и вбивать тоже мне?

P.S. А вот если бы ты вместо препираний выполнил то, что я тебе рекомендовал в предыдущем сообщении, вероятно ответ уже сейчас был бы у тебя в кармане

[oleg11]

KL

[oleg11]

KL

[oleg11]

KL

имеем файл А - куда должны попасть даные из файлов В и С. Причем строки не всегда точно совпадают по названию и по количеству (как в файле С есть лишняя строка "литература" которую нужно сложить с другой (верхней) строкой и сумму поместить в строку в А, отмеченную желтым цветом для понимания что там сумма двух строк. дать определение строкам - что чему соответствует, лучше в файле А где-то в спрятанном столбце. Важно чтобы были вставлены только значения без формул а итоговые (последняя строка снизу и последний столбец справа) остались с формулами. В реальности таблица больше и шире раза в два чем прведеный пример. Желательнов А где-то снизу иметь строчку для самопроверки. Где будут итоговые суммы по столбцам из В и С. Все расчеты в английской верси Excel. спасибо.

[KL]

Вот мой субъективный анализ:

1) Из всех наименований строк, только следующие в точности присутствуют во всех 3-х файлах:

Automobile
Buyers Expense
Cash over/short
Depreciation and amortisation
Marketing Allowance
Meals and Entertainment
Repaire and Maintenance
Royalties
Shipping and delivery
Shipping salaries
Travelling

2) При этом, наименования строк в файлах B и C совпадают, но есть одна лишня строка Literatura в С, которая на словах "мапится" в строку Repaire and Maintenance

3) Соответственно, неясно в какие строки в файле А "мапится" следующие строки файлов B и C

Advertising and promotion
Commissions
General
Overhead allocation
Salesmen Salaries
Selling Salaries
Showroom general
Showroom rents
Travelling-Overhead

4) Из вышесказанного следует, что файлы сами по себе неготовы к автоматизации, т.к. не просматриваются объективные критерии поиска соответствий (например общие коды для строк - ID)

5) Использование функций (в т.ч. VLookup и CountIf) из-под VBA для извлечения данных из закрытых файлов [практически] невозможно.

6) Возможные направления поиска решения

a. После того, как структура данных будет модифицирована:

- переход на Access
- Excel: Сводные Таблицы
http://peltiertech.com/Excel/Pivots/pivotstart.htm
- Excel VBA: ADO
http://erlandsendata.no/english/index.php?t=envbadac
http://www.rondebruin.nl/ado.htm
- Excel VBA: OLE Automation
http://www.rondebruin.nl/tips.htm
http://peltiertech.com/Excel/XL_PPT.html
http://erlandsendata.no/english/index.php?t=envbaole
- Excel VBA: MSQuery
- Excel: Формулы прямо в ячейках файла А

b. Если структура данных НЕ будет модифицирована, то остается:

- Excel: Сводные Таблицы
- Excel: Ручной "копи-пейст" данных из файлов B и С в А

[oleg11]

KL

Я специально привел такой пример, т.к. он близок к реальности. В данном случае я полагал, что можно дать определение строкам - что чему соответствует, в файле А где-то в спрятанном столбце (я привожу пример в новой редакци А. Где нзванию Marketing Allowance соответствует Salesmen Salaries которое имеется в В и С).

В случае, если это все проблематично реализовать, давайте упростмм пример - просто скопируйте названия из В в А. т.о. мы будем иметь 100% совпадение по строкам, за исключением в файла С, где есть лишняя строка "литература". но и это можно убрать. Также можно назвать одинаково столбцы. Если даже я получу просто поиск-копироваие-вставка с помощью VBA, то несоответсвие наименований строк и столбцов не проблема - найду другое решение. спасибо.

[Слобожанин]

Уважаемый oleg11,
Вообще-то в приведенном вами примере можно воспользоваться функцией ИНДЕКС(массив;номер_строки;номер столбца)
В свою очередь "номер_строки" и "номер_столбца" можно найти с помощью функции ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)
Высылаю вам ваш пример в одном файле (то что было в разных файлах, я разместил на отдельных листах)
Если есть желание, конечно, можно всё это уместить в более короткую функцию в VBA, но...
... зачем? Разве что - в качестве разминки.
Опять же, можно написать макрос, когда будут переноситься только значения, без формул, но тогда будет иметь смысл делать это через отсортированный массив.

С уважением,
Слобожанин.
P.S. Приведенный вами итог (файл "A"), некорректен, т.к. данные не соответствуют заглавной колонке исходных файлов

[ice_sar]

Приветствую
нужна помощь по данной теме
Требуется создать макрос выполняющий поиск значения в одном файле и перенос этого значения в другой
1й файл имеет формат

стол 100
стул 200
шкаф 300

т.е. в первой колонке наименование, во второй цена. в первой колонке наименования не повторяются. количество строк неизвестно и может меняться.

2й файл имеет формат
стол
стул
шкаф
стол
стол
шкаф

т.е есть только колонка с наименованиями, которые могут повторяться по несколько раз.количество строк неизвестно и может меняться

Задача проставить цены во втором файле получив следующий результат
стол 100
стул 200
шкаф 300
стол 100
стол 100
шкаф 300

Спасибо за помощь

[Viper]

Где вопрос? Да еще и некрофилия.

[ice_sar]

Вопрос в помощи - я не программист. Может кто-то помочь в написании подобного макроса?

сама формула ВПР выглядит так
=ЕСЛИ(ЕНД(ВПР(A:A;'E:\test\[test2.xls]Лист1'!$A:$C;3;ЛОЖЬ));"";ВПР(A:A;'E:\test\[test2.xls]Лист1'!$A:$C;3;ЛОЖЬ))
пытался засунуть ее в VBA - не работает

делал так:

Код: Выделить всё

Sub test()

Dim A
A = "=ЕСЛИ(ЕНД(ВПР(A:A;'E:\test\[test2.xls]Лист1'!$A:$C;3;ЛОЖЬ));"";ВПР(A:A;'E:\test\[test2.xls]Лист1'!$A:$C;3;ЛОЖЬ))"
[G] = A   
End Sub

[ice_sar]

Ну вроде получилось что-то, правда медленно работает - 1000 строк около 1минуты
Буду благодарен, если возможно ускорить

Код: Выделить всё

Sub test2()

Dim A As Long, B, sCr As String

    Application.ScreenUpdating = False
 
For i = 2 To 1000
    sCr = Cells(i, 1)
   
    Workbooks.Open "E:\test\test2.xls"
       
    B = Application.VLookup(sCr, Range("A2:C10"), 2, False)
   
    ActiveWorkbook.Close False
 
  Cells(i, 8 ) = B
 
  Next i
   
Application.ScreenUpdating = True
End Sub

[Viper] :: Пользуйся тэгом CODE!

[alibek]

Вынеси открытие и закрытие книги за пределы цикла.

[Michael_A]

Добрый день, как реализовать это, есть 2 документа в первом там структура такая:
Справочник.xls

Наименование 1 | | | | | | | |
Наименование 2 | | | | | | | |
Наименование 3 | | | | | | | |
Наименование 4 | | | | | | | |
Наименование 5 | | | | | | | |
один столбик с наименованием и последующее для данных.
второй документ:
Данные.xls

Наименование 1 | 123456789
Наименование 5 | 987654123
Наименование 2 | 147852369
Наименование 4 | 741258963
Наименование 3 | 321456987
Наименование 5 | 258741963
Наименование 5 | 654789123
тут наименования расположены в разброс и могут повторятся и тогда нада сохранить если несколько значений их все сохранить в первой таблице в строку напротив соответствующего наименования.
как бы так должно получится:
Справочник.xls

Наименование 1 |123456789| | |
Наименование 2 |147852369| | |
Наименование 3 |321456987| | |
Наименование 4 |741258963| | |
Наименование 5 |987654123|258741963|654789123| |
Буду очень благодарен если поможете!

[Michael_A]

пробовал при помощью опычной команды:

Код: Выделить всё

=ВПР(G18;[2.xls]Лист2!$A:$B;2;0)

но тут выдает тока одно значение тобиш не подойдет