Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Цикл по заполнению большого массива значениями, большой массив данных необходимо заполнить результатами вычисления формулы (значениями)
 
#1
11.11.2020 15:00:12
Приветствую Вас, о жители планеты Excel !
Много лет уже пользуюсь файлами, сконструированными из решений данного ресурса, за что премного благодарен!
Но приходится расти. У меня появилась задача, с которой мои знания уже не справляются. Полагаю нужен цикл (с которыми я вообще никогда не работал).
"Почитать" не помогло (наверное старею).
Отсюда просьба о помощи:
Есть файл с базой, в который на один лист собирается порядка 30-ти файлов в одну стройную таблицу данных.

В первом столбце "Бренд" содержатся значения брендов производителей проданного товара.

Во втором столбце  сумма выручки.

В третьем столбце Сумма премии, которая выражена как формула.

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

Если в заполняемом поле значение "Бренд" (по полному соответствию) отсутствует в справочнике брендов с повышенными процентами, формула (ВПР) возвращает значение ячейки с именем "Процент_базовый".

Если значение "Бренд" присутствует в справочнике, то формула возвращает расценку, предусмотренную справочником.

В небольшом массиве это работает прекрасно с формулами.

Но проблема в том, что массив формул очень большой (может доходить до 100 тысяч строк) а справочник брендов насчитывает более 400 строк, да и сам рабочий файл состоит иб большого числа листов с различными формулами (там где без них нельзя обойтись).

Обработка формулами будет сильно затруднена или вообще невозможна.

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

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

Критерием для поиска последней строки может быть первый столбец.

Буду признателен за помощь!
Файлик примера прилагаю.
 
 
 
#2
11.11.2020 15:02:00
Зачем для простого примера 160 кБ?
Предложите название темы, отражающее конкретную задачу. Заменят модераторы (В большом массиве строк вычисление проуцента приемии в зависимости от...)
 
 
 
#3
11.11.2020 15:13:14
Konstanta, если нужно ускорить именно ВПР, то поможет бинарный ВПР, и может этого и достаточно.
 
 
 
#4
11.11.2020 15:15:02
Слушаю и повинуюсь :)
Спасибо за конструктивную критику
 
 
 
#5
11.11.2020 15:20:02
Простите мне моё невежество, давно не был здесь...
раньше при предварительном просмотре можно было изменять название темы.
Сейчас это через как?
 
 
 
#6
11.11.2020 15:22:37
Выложил новый файл (меньшего размера)  
 
 
 
#7
11.11.2020 15:28:10
Цитата
Hugo написал:
если нужно ускорить именно ВПР, то поможет бинарный ВПР, и может этого и достаточно.
Спасибо за мысль, но файл сильно нагружен и я стараюсь минимизировать вообще формулы. Обычно (поскольку иначе не умею) после вычисления формул я их макросом копирую и вставляю как значения.
Но при таком большом массиве их и вычислить не получится.
 
 
 
#8
11.11.2020 15:28:37
Получил справедливое замечание по названию темы.
Предлагаемое название "В большом массиве строк вычисление процента премии в зависимости от значения бренда"
Изменено: Konstanta - 11.11.2020 15:38:03 (Попытка сменить название темы, после замечания модератора)
 
 
 
#9
11.11.2020 15:35:16
Вообще пытаюсь сейчас внедрить этот бинарный поиск в примере - ACV и AGA всё портят :(
Изменено: Hugo - 11.11.2020 15:37:46
 
 
 
#10
11.11.2020 15:40:35
Цитата
Hugo написал:
ACV и AGA всё портят
В реальном массиве есть вообще "чудесные" названия, причём завтра могут появиться новые и я даже предположить не могу что там прилетит!
Единственно, что можно использовать? это полное соответствие (просто равно или не равно)  
 
 
 
#11
11.11.2020 15:48:19
С AGA справился - если имя диапазона задать именно по данным, без заголовка - то ОК, бинарно работает.
ACV выпадает в ошибку, а для её обработки - это ещё одну формулу нужно...
Макрос на словаре быстро всё сделает.
P.S. Кстати первый файл в 3 раза меньше второго, но в нём в 10 раз больше данных :)
Изменено: Hugo - 11.11.2020 15:54:03
 
 
 
#12
11.11.2020 15:53:53
Цитата
Hugo написал:
С AGA справился - если имя диапазона задать
А можно увидеть что именно получилось у Вас в файле?  
 
 
 
#13
11.11.2020 15:56:02
можно: http://prntscr.com/vhe3te
Я вот думаю как бы сделать удобным применение макроса...
 
 
 
#14
11.11.2020 16:08:30
Цитата
Hugo написал:
Я вот думаю как бы сделать удобным применение макроса...
Но ведь это опять формула в итоге.
Конечно, можно всё это скопировать и вставить как значения.
Но этот вариант я уже пробовал.
У меня 8 ядерный комп лопатит этот массив по пять минут *в рабочем файле с другими листами) без признаков жизни...
Поэтому и хочу заполнять построчно значениями а не формулами
 
 
 
#15
11.11.2020 16:25:36
Придумал.
Встать на A1 или A2, выполнить
Код
Option Explicit

Sub Premija()
    Dim sdvig&, a, i&, t$, x, iL&

    On Error Resume Next
    sdvig = InputBox("Введите сдвиг от выделенной ячейки")
    On Error GoTo 0
    If sdvig <= 0 Then Exit Sub

    x = Range("Процент_базовый").Value

    With CreateObject("Scripting.Dictionary")
        a = Range("Справочник_бренд").Value
        For i = 1 To UBound(a)
            t = Trim(a(i, 1))
            .Item(t) = a(i, 2)
        Next
        iL = Cells(Rows.Count, Selection.Column).End(xlUp).Row
        a = Selection.Cells(1).Resize((iL - Selection.Cells(1).Row) + 1).Value
        For i = 1 To UBound(a)
            t = Trim(a(i, 1))
            If .exists(t) Then a(i, 1) = .Item(t) Else a(i, 1) = x
        Next
    End With

    Selection.Cells(1).Offset(, sdvig).Resize(UBound(a), 1) = a
End Sub
Изменено: Hugo - 11.11.2020 16:29:08
 
 
 
#16
11.11.2020 17:30:32
Hugo , Спасибо огромное!
Макрос работает.
Я правда не вполне понял функциональное назначение "Сдвига" с окном, но просто заменил процедуру вызова окна на нужное число "сдвига" и всё работает.
Проверил на массиве в 630 000 строк, при справочнике = 639 строк.
Работает прекрасно!!!
Беру во внедрение, буду пробовать в реальном рабочем файле!
При рабочей доработке, могу обратиться в личку за помощью (конечно за вознаграждение) ???
 
 
 
#17
11.11.2020 17:37:37
Ну это чтоб можно было универсально применять, а так то конечно можно жёстко прописать и стартовую ячейку, и вывод куда делать, тогда только нужно в коде указать с каким листом работаете, а то будет с активным в момент запуска работать.
В личку с деньгами - можно! :)
Изменено: Hugo - 11.11.2020 17:38:17
 
 
 
#18
11.11.2020 17:47:32
Извиняюсь за вопрос не по теме: раньше закрыть тему можно было изменив название "решено"
подскажите пожалуйста, как сейчас это тут делается?
 
 
 
#19
11.11.2020 22:34:08
Цитата
Konstanta написал:  раньше закрыть тему можно было изменив название "решено"
Никогда здесь такого не было. По назанию ориентируются. А что будет, если вся траница будет с названиями тем "Решено"?

Поблагодарили, сообщили, подошло или нет - вот и все закрытие
 
 
 
Страницы: 1
Наверх