|
|
Регистрация Восстановить пароль |
Повторная активизация e-mail |
Регистрация | Задать вопрос |
Заплачу за решение |
Новые сообщения |
Сообщения за день |
Расширенный поиск |
Правила |
Всё прочитано |
|
|
Опции темы | Поиск в этой теме |
05.08.2009, 22:53 | #1 |
Регистрация: 05.08.2009
Сообщений: 5
|
Импорт значений из внешних книг Excel по списку файлов
Люди, помогите.
Есть задачка. В таблице, назову её сводной, существует список с именами файлов, которые все лежат на компе в одном месте и имеют одинаковую структуру. Так что можно сказать есть список ссылок на ячейки во внешних файлах. Задача подтягивать в сводную таблицу данные из определенных полей каждого из файлов. Понятно что можно каждый открывать и делать из сводной таблицы внешнюю ссылку на заданную ячейку. И так с каждым из файлов, вот только файлов много и полей в них много. Задача осложняется что список файлов динамически меняется, так что процесс это бесконечный. Хочется как-то автоматизировать процесс. Пробовал формулами, например "ДВССЫЛ", но работает только с открытыми книгами. Что посоветуете, учить VBA, или как-то можно заставить её работать по внешним ссылкам штатными средствами ? |
06.08.2009, 05:35 | #2 |
Старожил
Регистрация: 05.12.2007
Сообщений: 4,180
|
Получить значения из файла, не открывая его, можно только в том случае, если известны не только путь и имя файла, но также в явном виде имя листа и диапазон ячеек.
Если все это известно, приведите пример, из каких ячеек листа брать имена файлов, из каких листов (не номера, а имена), и из какого диапазона получать значения, делать с ними что-нибудь или нет, и куда вставлять полученные данные. Также, поясните, каким образом нужно указывать путь к папке с файлами-источниками.
Чем шире угол зрения, тем он тупее.
|
06.08.2009, 20:07 | #3 |
Регистрация: 05.08.2009
Сообщений: 5
|
SAS888,
все из написанного известно, и пусть, пусть это будет "с:\1\" и имя листа во всех файлах одно, пусть это будет "Лист1", и значение надо брать из одной и той-же ячейки, пусть это будет A1. Пусть в сводной таблице в столбце А подряд идут имена файлов, например A1=1, A2=2 и т.д. При этом файлы соответственно 1.xlsx, 2.xlsx и т.д. все находятся в "с:\1\". При этом данные из ячейки А1 Листа "Лист1" файла-источника должны вставляться в ячейку Bn (где n собственно строка где находится название соответствующего файла) сводного файла. На первый взгляд показалось логичным сформировать ссылку на внешнюю книгу функцией "сцепить". То есть в ячейку B1 я ввел такую формулу =СЦЕПИТЬ("='c:\1\[";A1;".xlsx]Лист1'!$A$1") , но она вполне логично мне возвращает значение ='c:\1\[1.xlsx]Лист1'!$A$1. Но мне то нужно не значение выводить а по сути формулу, которую после этого еще выполнить надо. То есть значение что мне дала функция СЦЕПИТЬ надо после этого понять как формулу и её выполнить. Последний раз редактировалось Varen1k; 06.08.2009 в 20:17. |
07.08.2009, 06:34 | #4 |
Старожил
Регистрация: 05.12.2007
Сообщений: 4,180
|
Могу предложить решение с помощью VBA. Вставьте в модуль нужного листа код:
Код:
ПРИМЕЧАНИЕ: Если к моменту вставки кода в программный модуль листа в столбце "A" уже имелись значения, то для этих ячеек ссылки созданы не будут. Для того, чтобы их получить, скопируйте и снова вставьте требуемые ячейки столбца "A" (т.е. требуется обновление значений).
Чем шире угол зрения, тем он тупее.
|
07.08.2009, 08:49 | #5 |
Пользователь
Регистрация: 03.08.2009
Сообщений: 10
|
А если использовать такую функцию:
Private Function gv(p, f, s, r1, r2) Dim arg As String If Right(p, 1) <> "\" Then p = p & "\" If Dir(p & f) = "" Then gv = "файл не найден" Exit Function End If arg = "'" & p & "[" & f & "]" & s & "'!" & Cells(r1, r2).Range("a1").Address(, , xlR1C1) gv = ExecuteExcel4Macro(arg) End Function а ссылка к этой функции выглядит так: Аctivesheet.Cells(x, y) = gv(path, file, list, x1, y1) где path - путь к папке, в таком виде "c:\папка\" file - имя файла, в таком виде "имя файла.xls" list - имя листа - "имя листа" x, y - строка, столбец куда разместить x1,y1 - строка, столбец запрашиваемой информации список файлов задаётся предварительно, потом полный путь разбивается на папку и имя файла. и вперёд =) при желании могу выложить файлик с примером работы такого файла (делал подобное пару лет назад) Последний раз редактировалось smith45; 07.08.2009 в 08:51. |
07.08.2009, 08:59 | #6 | |
Старожил
Регистрация: 05.12.2007
Сообщений: 4,180
|
Цитата:
Чем шире угол зрения, тем он тупее.
|
|
07.08.2009, 10:03 | #7 | |
Пользователь
Регистрация: 03.08.2009
Сообщений: 10
|
Цитата:
автору... полюбому лучше учить vba. "пригодится" (с) |
|
07.08.2009, 10:42 | #8 | ||
Старожил
Регистрация: 05.12.2007
Сообщений: 4,180
|
Цитата:
Код:
Цитата:
Чем шире угол зрения, тем он тупее.
|
||
07.08.2009, 13:05 | #9 |
Регистрация: 05.08.2009
Сообщений: 5
|
Smyth, SAS, спасибо вам.
Вариант SAS действительно подходит больше, т.к. происходит он-лайн изменение данных в сводной сразу после изменения данных в файле-источнике, что очень удобно, т.к. корректировкой файлов-источников и работой со сводной таблицей занимаются разные люди и им в таком случае не требуется сообщать друг другу что в файле-источнике произошли изменения. Только остается вопрос: насколько это будет грузить комп? Т.к. файлов-источников может быть несколько сотен и из каждого берется с десяток данны. Значит получается штатными средствами или формулами Excel нельзя сделать так, чтобы он значение, вычисляемое одной формулой, понимал как формулу а не как значение ячейки ? |
25.04.2012, 11:59 | #10 | |
Пользователь
Регистрация: 01.12.2011
Сообщений: 42
|
Цитата:
Данные лежат в папках по годам, в каждой папке "года" еще три папки с пронумерованными файлами. Вычисления реализованы через использование функции "ДВССЫЛ", естественно данные обновляются только после открытия файлов. Хотелось бы, чтобы данные обновлялись при закрытых файлах. Пример во вложении.СводФЭ_разр.rar |
|
|
Опции темы | Поиск в этой теме |
Похожие темы | ||||
Тема | Автор | Раздел | Ответов | Последнее сообщение |
Обработка в Excel внешних событий | pan | Microsoft Office Excel | 4 | 22.02.2009 17:26 |
Импорт внешних данных, если данные в архиве Zip | Vic65 | Microsoft Office Excel | 5 | 29.07.2008 14:10 |
Импорт внешних данных, синхронизация по времени | slba | Microsoft Office Excel | 3 | 15.07.2008 13:05 |
Компиляция внешних файлов | NoRty | Паскаль, Turbo Pascal, PascalABC.NET | 2 | 25.02.2008 15:34 |
Импорт внешних данных | asale | Microsoft Office Excel | 1 | 15.04.2007 00:41 |