Обработка данных другого файла *.xlsx

13.08.2010, 18:18. **Показов** 5746. **Ответов** 5

Студворк — интернет-сервис помощи студентам

Добрый день!

Прошу помочь разобраться со следующей задачей. У нас в банке каждую неделю обновляется большой массив (таблица в эксель) с данными по кредитному портфелю (остатки задолженности по счетам в разрезе бизнес-линий, продуктов, валют, вместе с прочей информацией: филиалы, признак того, была ли реструктуризация, корзины просрочки и т.д.). Из этой таблицы часто вручную приходится тянуть данные с многоразовым использованием фильтров, это очень утомительно.

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

Этапы:

1. Открытие файла *.xlsx объемом порядка 26 мб. Файл я открывал, вот только не получалось сделать там простейшие операции с числами в ячейках и вывести результат на экран… Прошу пример. Еще касательно этого пункта вопрос следующий: а можно ли получить доступ к данным файла без его фактического открытия на экране ? (это вроде как тормозит процесс, т.к. файл открывается порядка 30-40 секунд, что тоже немало).

2. После открытия файла нужно реализовать в программном коде либо цикл для подсчета суммы значений в ячейках в столбце при условии, что в соответствующих ячейках других столбцов находятся определенные значения, либо реализацию функции SUMIF, когда диапазон для поиска будет задан программно как уникальный ключ в виде связки значений в других столбцах. Что быстрее на первый взгляд? Здесь, кстати, еще простой вопрос об обращении к данным в открытом файле, нет ли подводных камней в этом вопросе?

3. Все необходимые значения я бы поместил в массивы, а потом уже определил в нужные ячейки главного файла. Возможно если передавать значения сразу, т.е. через ряд текущих переменных, которые обнуляются после передачи значений в главный файл, это будет быстрее… это вопрос?

4. Вопрос касательно графиков в основном файле. Хотелось бы добавлять новые данные так, чтобы они автоматически появлялись на графике. А то мы пока делаем это вручную, что есть очень неудобно(( Как это реализовать?

5. Еще раз к вопросу об использовании функций эксель в коде VBA: буду использовать SUMIF, VLOOKUP, SUM, MIN, MAX, MMULT (вот тут думаю есть ньюансы). Буду благодарен за примеры. Может есть подводные камни в использовании.
6. Еще хочу узнать, где можно взять нормальные, красивые кнопки для интерфейса, а то стандартные какие-то примитивные.

СПАСИБО!!!

@ironegg · 13.08.2010, 21:09

рекомендую эту задачу решать при помощи Access. при тех же трудозатратах получишь более качественное, масштабируемое решение

@XoFfiCEr · 15.08.2010, 03:13

Сообщение от ironegg

рекомендую эту задачу решать при помощи Access. при тех же трудозатратах получишь более качественное, масштабируемое решение

Графики в Acess не сделаешь как в excel

@ironegg · 15.08.2010, 10:57

деза. здесь идет речь о противоположном http://office.microsoft.com/ru... 34600.aspx
я уже не говорю о страницах доступа к данным(для которых имеются различные Web-компоненты, в том числе и диаграммы) и о том, что Access благодаря VBA можно использовать как сервер автоматизации и выводить данные не то что в word и excel, но и, например, в Visio, AutoCAD итд

@Busine2009 · 15.08.2010, 11:16

Сообщение от Antony25x

а можно ли получить доступ к данным файла без его фактического открытия на экране ?

У закрытого файла нельзя получить данные. Можно открывать файл, но не отображать его на экране монитора, только я думаю, что это по времени будет столько же - ведь время определяется загрузкой в оперативную память, а не прорисовкой на мониторе.

16.08.2010, 11:36

В принципе ведь можно прямо из кода в книге эксель обращаться к базе данных Access за необходимыми данными, у меня вот есть такой пример:

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Private Sub UFN_REPORT_CORPORATE_GRP(ColIdx As Integer, RepDate As String, KolVoD As String, strConnectSQL As String) 
On Error GoTo ERR_UFN_REPORT_OTP_CORPORATE_GRP 
 Dim StrSQL As String 
 Dim SheetIdx As Integer 
 Dim cnn As ADODB.Connection 
 Dim rst As ADODB.Recordset 
 StrSQL = "SELECT * FROM UFN_REPORT_CORPORATE_GRP_CLN2_PER('" + KolVoD + "','" + RepDate + "') ORDER BY LOAN" 
 Set cnn = New ADODB.Connection 
 cnn.CommandTimeout = 0 
 cnn.ConnectionTimeout = 0 
 cnn.Mode = adModeRead 
 cnn.CursorLocation = adUseClient 
 cnn.Open strConnectSQL 
 cnn.BeginTrans 
 Set rst = New ADODB.Recordset 
 rst.Open StrSQL, cnn, adOpenStatic, adLockOptimistic 
 If Not rst.EOF Then 
  rst.MoveFirst 
 Else 
  MsgBox "There are no data for the specified date", vbInformation, "MS SQL Server" 
  GoTo EXIT_UFN_REPORT_OTP_CORPORATE_GRP 
 End If 
 While Not rst.EOF 
    For SheetIdx = 4 To Sheets.Count 'Step 3 
        If UCase(Sheets(SheetIdx).Name) = UCase(CStr(rst!LOAN)) Then 
            Sheets(SheetIdx).Select 
            If SheetIdx = 9 Then
              Cells(6, ColIdx) = 0 
              Cells(8, ColIdx) = 0 
              Cells(10, ColIdx) = 0 
...........................
        End If 
    Next SheetIdx

Хочу узнать Ваше мнение, возможно это и будет оптимальным решением - просто кодом из эксель файла тянуть все данные обращаясь к базе данных. Конечно, в самой базе возможно потребуются дополнительные запросы, но это не проблема, доступ к базе есть.

Еще один вопрос по графикам, автоматическое обновление графика после вставки столбца с новыми данными, возможно ли это? Если да, то попрошу пример кода. Спасибо.

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Работа со звуком через SDL3_mixer 8Observer8 08.02.2026 Содержание блога Пошагово создадим проект для загрузки звукового файла и воспроизведения звука с помощью библиотеки SDL3_mixer. Звук будет воспроизводиться по клику мышки по холсту на Desktop и по. . .	SDL3 для Web (WebAssembly): Основы отладки веб-приложений на SDL3 по USB и Wi-Fi, запущенных в браузере мобильных устройств 8Observer8 07.02.2026 Содержание блога Браузер Chrome имеет средства для отладки мобильных веб-приложений по USB. В этой пошаговой инструкции ограничимся работой с консолью. Вывод в консоль - это часть процесса. . .	SDL3 для Web (WebAssembly): Обработчик клика мыши в браузере ПК и касания экрана в браузере на мобильном устройстве 8Observer8 02.02.2026 Содержание блога Для начала пошагово создадим рабочий пример для подготовки к экспериментам в браузере ПК и в браузере мобильного устройства. Потом напишем обработчик клика мыши и обработчик. . .	Философия технологии iceja 01.02.2026 На мой взгляд у человека в технических проектах остается роль генерального директора. Все остальное нейронки делают уже лучше человека. Они не могут нести предпринимательские риски, не могут. . .
SDL3 для Web (WebAssembly): Вывод текста со шрифтом TTF с помощью SDL3_ttf 8Observer8 01.02.2026 Содержание блога В этой пошаговой инструкции создадим с нуля веб-приложение, которое выводит текст в окне браузера. Запустим на Android на локальном сервере. Загрузим Release на бесплатный. . .	SDL3 для Web (WebAssembly): Сборка C/C++ проекта из консоли 8Observer8 30.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .	SDL3 для Web (WebAssembly): Установка Emscripten SDK (emsdk) и CMake для сборки C и C++ приложений в Wasm 8Observer8 30.01.2026 Содержание блога Для того чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. . . .	SDL3 для Android: Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .

Antony25x

	Обработка данных другого файла .xlsx 13.08.2010, 18:18. Показов* 5746. Ответов 5 Метки нет (Все метки) Добрый день! Прошу помочь разобраться со следующей задачей. У нас в банке каждую неделю обновляется большой массив (таблица в эксель) с данными по кредитному портфелю (остатки задолженности по счетам в разрезе бизнес-линий, продуктов, валют, вместе с прочей информацией: филиалы, признак того, была ли реструктуризация, корзины просрочки и т.д.). Из этой таблицы часто вручную приходится тянуть данные с многоразовым использованием фильтров, это очень утомительно. Возникла идея создать некую общую таблицу с необходимой структурой и с помощью программного кода VBA раз в неделю выгружать в нее необходимые данные. Буду признателен за похожий код, который сможет проиллюстрировать описанные ниже этапы. Опыт программирования, в принцепе, есть, так что я сумею все правильно преобразовать и скомпоновать. Этапы: 1. Открытие файла *.xlsx объемом порядка 26 мб. Файл я открывал, вот только не получалось сделать там простейшие операции с числами в ячейках и вывести результат на экран… Прошу пример. Еще касательно этого пункта вопрос следующий: а можно ли получить доступ к данным файла без его фактического открытия на экране ? (это вроде как тормозит процесс, т.к. файл открывается порядка 30-40 секунд, что тоже немало). 2. После открытия файла нужно реализовать в программном коде либо цикл для подсчета суммы значений в ячейках в столбце при условии, что в соответствующих ячейках других столбцов находятся определенные значения, либо реализацию функции SUMIF, когда диапазон для поиска будет задан программно как уникальный ключ в виде связки значений в других столбцах. Что быстрее на первый взгляд? Здесь, кстати, еще простой вопрос об обращении к данным в открытом файле, нет ли подводных камней в этом вопросе? 3. Все необходимые значения я бы поместил в массивы, а потом уже определил в нужные ячейки главного файла. Возможно если передавать значения сразу, т.е. через ряд текущих переменных, которые обнуляются после передачи значений в главный файл, это будет быстрее… это вопрос? 4. Вопрос касательно графиков в основном файле. Хотелось бы добавлять новые данные так, чтобы они автоматически появлялись на графике. А то мы пока делаем это вручную, что есть очень неудобно(( Как это реализовать? 5. Еще раз к вопросу об использовании функций эксель в коде VBA: буду использовать SUMIF, VLOOKUP, SUM, MIN, MAX, MMULT (вот тут думаю есть ньюансы). Буду благодарен за примеры. Может есть подводные камни в использовании. 6. Еще хочу узнать, где можно взять нормальные, красивые кнопки для интерфейса, а то стандартные какие-то примитивные. СПАСИБО!!!

@ironegg 1905 / 782 / 31 Регистрация: 11.02.2010 Сообщений: 1,567
	13.08.2010, 21:09
	рекомендую эту задачу решать при помощи Access. при тех же трудозатратах получишь более качественное, масштабируемое решение 0

@ironegg 1905 / 782 / 31 Регистрация: 11.02.2010 Сообщений: 1,567
	15.08.2010, 10:57
	деза. здесь идет речь о противоположном http://office.microsoft.com/ru... 34600.aspx я уже не говорю о страницах доступа к данным(для которых имеются различные Web-компоненты, в том числе и диаграммы) и о том, что Access благодаря VBA можно использовать как сервер автоматизации и выводить данные не то что в word и excel, но и, например, в Visio, AutoCAD итд 1