Access VBA код на 65 тыс. строк формулы вешают Excel

Добрый день!

Подскажите, пожалуйста, может ли кто-то помочь с кодом на VBA для Access? Ситуация следующая.

• Есть база данных, она выгружается в Access 2010.
• На основе этой базы делаются определенные вычисления, которые в итоговом варианте выдаются в виде сводной таблицы.
• Исходные данные (база) и свод (результат) - в приложении Excel.
• Выборка базы - урезанная, в полной выгрузке 65 тыс. строк.
• В серых полях - дополнительные вычисления посредством формул.

Проблема в том, что на 65 тыс. строк формулы вешают Excel.
Вопрос - можно ли написать сразу в Access код VBA, чтобы все нужные формулы считались кодом?

Заранее спасибо!

Вложения

Тест.zip (95.6 Кб, 17 просмотров)

0

Разумеется можно, в Access тот же VBA, что и в Excel.

0

Corbis, спасибо за комментарий. С кодом можете помочь?

0

Выкладывайте БД, посмотрим..

0

что на 65 тыс. строк формулы вешают Excel

может макрос надо написать в екселе?

0

shanemac51, добрый день! Думаю, что макрос можно написать и в Excel, и в Access. Главное, чтобы он работал без зависаний программы. Наверное, в Access будет работать быстрее, но, может быть, я ошибаюсь.
Проблема в циклах, которых, видимо, будет немало, поскольку используются формулы суммеслимн и суммпроизв.

0

Strecosa, вы собираетесь наконец выложить вашу базу в Access и описать человеческим языком, что именно и каким способом должно быть посчитано?

0

Corbis, добрый день!
База (выборка из 87 строк) приложена.

Вложения

База.rar (23.7 Кб, 8 просмотров)

0

Corbis, описание того, что должно быть "на выходе", а также все формулы для вычислений и свод "на выходе", есть в первом письме. Исходная база приложена. Что именно нужно пояснить подробнее?
Спасибо.

0

вы конечно много чего используете(не примере строк 2 и 6)

NN	реквизит	формула_2	значение_2	формула_6
A	Дата_		41318		41639
B	Дата_Квартал		1		4
C	Дата_год		2013		2013
D	Дата_месяц		2		12
E	Дата_Месяц_Имя		02 Февраль		12 Декабрь
F	Контрагент		ООО "Ромашка"		ООО "Ромашка"
G	Контрагент_от_мемо		ГК "Белый медведь"		ООО "Ромашка"
H	Контрагент_к_мемо		ООО "Ромашка"		ГК "Белый медведь"
I	Документ_основание		Счет, 1, 05.02.2013		Договор, 07/10-2013, 31.12.2013
J	Содержание операции		Предоплата за работы		Оплата за работы по договору
K	Документ_подтверждение		П/П, 459, 13.02.2013		СЧФК, 27, 31.12.2013
L	Документ_сопр_
M	_Номер_операции		212-61282		213-81895
N	Сумма_документа_руб	105000* (1/1000)	105	48507978.08* (1/1000)	48507,97808
O	Дебет_счет_номер		60/1		19/4
P	Кредит_счет_номер		51		60/1
Q	Сумма_операции_руб	105000* (1/1000)	105	7399522.08* (1/1000)	7399,52208
R	Сумма_операции_вал		105000		7399522,08
S	Expr1018		Бухгал	Бухгал
T	_Вид_учета		Бухгал		Бухгал
U	Корреспонденция		60/1 - 51		19/4 - 60/1
V	Source		2014-2013		2014-2013
W	Наименование операции		Оплата		Начисление
X	Сумма_руб_знак	-105000* (1/1000)	-105	7399522.08* (1/1000)	7399,52208
Y	Дебет_Ан2_имя
Z	ОригинСцепка	IF(Y2<>"", CONCATENATE(Y2, I2, F2) , 0)	0	IF(Y6<>"", CONCATENATE(Y6, I6, F6) , 0)	0
AA	Кол-воНачисл	COUNTIFS(Z:Z, Z2, Z:Z, "<>0")	0	COUNTIFS(Z:Z, Z6, Z:Z, "<>0")	0
AB	Доля для одного начисления	IF(AA2<>0, 1/AA2, 0)	0	IF(AA6<>0, 1/AA6, 0)	0
AC	Проект/документ	SUMIFS(AB:AB, I:I, I2)	0	SUMIFS(AB:AB, I:I, I6)	1
AD	Доля проекта в документе	IF(Z2=0, 0, X2/SUMIFS(X:X, I:I, I2, W:W, "Начисление") )	0	IF(Z6=0, 0, X6/SUMIFS(X:X, I:I, I6, W:W, "Начисление") )	0
AE	Повтор_ПрисвоенПроект	COUNTIFS($Z$2:$Z2, Z2, $Z$2:$Z2, "<>0")	0	COUNTIFS($Z$2:$Z6, Z6, $Z$2:$Z6, "<>0")	0
AF	Документ для проекта	IF(AE2=1, I2, 0)	0	IF(AE6=1, I6, 0)	0
AG	Присвоение проекта	IF(AND(Y2="", AC2=0) , "Аванс", IF(AND(Y2="", AC2>=1) , INDIRECT(AH2) , IF(Y2<>"", Y2, ) ) )	Аванс	IF(AND(Y6="", AC6=0) , "Аванс", IF(AND(Y6="", AC6>=1) , INDIRECT(AH6) , IF(Y6<>"", Y6, ) ) )	Вираж -Проект 1
AH	технич. Адрес	IF(AND(Y2="", AC2=1) , ADDRESS(SUMPRODUCT ((($AF$2:$AF$88=I2) * ROW($AF$2:$AF$88) ) ) , 25) , IF(AND(Y2="", AC2>1) , IF(AND(LEFT(O2, 2) ="19", Y2="") , ADDRESS(SUMPRODUCT ((($M$2:$M$88=M2) * ($X$2:$X$88=X2/0.18) * ROW($X$2:$X$88) ) ) , 25) , "$AM$1") , 0) )	0	IF(AND(Y6="", AC6=1) , ADDRESS(SUMPRODUCT ((($AF$2:$AF$88=I6) * ROW($AF$2:$AF$88) ) ) , 25) , IF(AND(Y6="", AC6>1) , IF(AND(LEFT(O6, 2) ="19", Y6="") , ADDRESS(SUMPRODUCT ((($M$2:$M$88=M6) * ($X$2:$X$88=X6/0.18) * ROW($X$2:$X$88) ) ) , 25) , "$AM$1") , 0) )	$Y$5
AI	Сумма_руб_распределено	IFERROR(IF(AC2>1, SUMIFS(X:X, I:I, I2, W:W, "Оплата") * AD2, 0) , 0)	0	IFERROR(IF(AC6>1, SUMIFS(X:X, I:I, I6, W:W, "Оплата") * AD6, 0) , 0)	0
AJ	СуммаНачисления, руб	IF(W2="Начисление", X2, 0)	0	IF(W6="Начисление", X6, 0)	7399,52208
AK	СуммаОплаты, руб	IF(AND(W2="Начисление", AI2<>0) , AI2, IF(W2="Оплата", X2, 0) )	-105	IF(AND(W6="Начисление", AI6<>0) , AI6, IF(W6="Оплата", X6, 0) )	0
AL
AM	Распределено
AN

1

Сообщение от Strecosa Что именно нужно пояснить подробнее?

ВСЕ. Физический смысл данных, их взаимосвзяь, методику обработки и вычисления. У баз данных другая, чем у электронных таблиц, логика. Если вы хотите получить от базы данных эффективность и скорость, вам нужно забыть о том, что у вас уже сделано в электронной таблице (вы ж сами видите, что оно вас е устраивает), и сделать не "как есть", а "как надо". Поэтому в вашу табличку нас не отправляйте.

0

Деление на 1000 можно пропустить. Это неважно. Все нужные вычисления начинаются с поля Z. Поля А-Y считаем входящими данными.

0

Уважаемый texnik-san, в приложении добавлены комментарии к методике вычисления добавленных столбцов. На входе есть таблица в базе. К ней необходимо добавить несколько колонок с вычислениями (методика описана в приложении) и вывести новую таблицу с исходными и добавленными колонками.

Вложения

Тест_шаблон.rar (61.7 Кб, 10 просмотров)

0

Strecosa, вы только не сочтите, что я над вами издеваюсь, но я ждала немного другого объяснения.

Понимаете, аксес, в отличие от эксель, не имеет таких понятий, как "ячейка" и адрес ячейки". В аксес данные имеют вид таблиц, у которых "адрес" имеют только столбцы (Они назваются "полями" и обращаются к ним по именам, а не номерам).

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

Если одновременно текущее значение ячейки в столбце "Дебет_Ан2_имя" пустое и значение ячейки в столбце "Проект/документ" равно нулю, то возвращаем фиксированное значение "Аванс".

Тут все нормально, эту фразу в контекст аксес перевести можно.

В противном случае, если текущее значение ячейки в столбце "Дебет_Ан2_имя" пустое и значение ячейки в столбце "Проект/документ" больше или равно 1, то возвращаем значение ячейки по адресу, указанному в соответствующей строке столбца "технич. Адрес".

А вот эта фраза лишена смысла, потому что нет "ячеек" и нет "адресов".

Поэтому лучше всего будет рассказать принцип таки нормальным человеческим языком.

Примерно так (чисто образец, все с потолка):

Имеются сведения о поставках (дата, контрагент, тип документа, домер документа, номер договора, сумма) и оплатах (дата, контрагент, номер договора, сумма), и требуется сопоставить, какая оплата относится к какой поставке по таким правилам:
1) должны совпадать названия контрагентов и номера договоров. Поставки без номера договора сопоставляются только с оплатами без номера договора
2) сопоставление поставок и оплат происходит в порядке возрастания даты. Если в одну дату было несколько поставок или оплат, то порядок определяется номером документа
3) если дата первой оплаты раньше даты первой поставки, такой оплате должна быть присвоена метка "аванс"
и т.д.

1

Сообщение от Strecosa Уважаемый texnik-san

правильнее будет - уважаемая

0

Сообщение от texnik-san добавить в таблицу столбец "номер строки" и заполнить его данными.

Желаю ТС побольше подобных советов.

0

Сообщение от alvk Желаю ТС побольше подобных советов.

Если это ирония/сарказм, то хочется возразить. Не редкость когда в документе требуется определенный порядок. Как его сделать, если в исходной таблице порядок не указан? Никак. Ну не делать же длинючий код для правильной сортировки, когда можно элементарно просто задавать ее в таблице. Так что Номер строки иногда очень полезный атрибут. Более того, был случай, когда для одного справочника задавалось несколько различных сортировок и пришлось создать специальную таблицу сортировок для разных выходных документов.

1

Сообщение от mobile Не редкость когда в документе требуется определенный порядок.

Редкость.

Сообщение от mobile Так что Номер строки иногда очень полезный атрибут.

В справочнике одном я тоже использовал поле для порядкового номера отображения в списке. Но у ТС таблица фактов. А это совсем другое.

0

Сообщение от Strecosa Проблема в том, что на 65 тыс. строк формулы вешают Excel

Ну и пусть вешают, чем это плохо?

0

Сообщение от commun Ну и пусть вешают, чем это плохо?

Скорости хочется?

0