написал: ну тут есть путь сразу определить количество нужных элементов для создания последовательности черезCOUNTIFS(Index[Дата];">="&H3;Index[Дата];" 0")последовательноть от 1 до ...ROW(A1:INDEX(A:A;COUNTIFS(Index[Дата];">="&H3;Index[Дата];" 0")) и сразу отбросить то, что не подходит. То есть фильтровать не только по дате, но и по значению (H3 =Index[Дата])*(Index[показатели]<>0)
Ну то есть "слона есть по частям..." 1. определиться с размерностью нового массива 2. исключить неподходящие значения 3. заполнить новый массив подходящими значеними
принцип понятен. Примерно так я и действовал, к сожалению, где-то что-то идёт не так. В любом случае за помощь 100 ++ в карму.
написал: 0 замените на FALSE типа такIFERROR(1/(1/J29:J33);1=0)и эти значения учитываться не будут.
Спасибо. Я совсем забыл об этой возможности. Совет дельный, задачу решает. получаем конструкцию типа
Код
{FALSE:0.275:0.555:FALSE:FALSE.....}
с игнорируемыми значениями, не учитываемыми вычислениях
Но академический интерес по прежнему остался
возможно ли сокращение массива по условию (-ям)
Код
{0:0:0:0:0:0:0:1:0:0:1:1}
до
{1:1:1}
в ТЕМЕ АВТОРА рассматривается подобный вопрос, но в данном случае имеется нюанс: отфильтрованный массив нужен В ЯЧЕЙКЕ без привязки к определённым строкам (p.s. они тоже меняются)
написал: Да ладно?=СУММПРОИЗВ(СУММЕСЛИМН(table[Сумма];table[Касса];A17;table[Банк];B17;table[Валюта];C17;table[Касса];table[Касса];table[Банк];table[Банк];table[Валюта];table[Валюта])*СУММЕСЛИ(C$21:C$25;table[Валюта];D$21:D$25)/СУММЕСЛИ(C$21:C$25;table[Валюта];B$21:B$25))
Формула РАБОТАЕТ в "идеальных" условиях: если все строки эксклюзивны и не одна из них не дублируется
если же присутствует хотя бы одна "задвоенная по условиям" строка - то результат неверный - по дублированной строке сумма умножается на количество дублированных строк см. пример дублированная строка выделена жёлтым, результат вычисления формулы - оранжевым. Верное значение - зелёным.
Уважаемый автор, как доработать Вашу замечательную формулу, если учитывать что допускается несколько строк с суммами? Очень буду признателен за ответ.
p.s. интересно используется конструкция СУММЕСЛИМН(...;table[Касса];table[Касса];table[Банк];table[Банк];table[Валюта];table[Валюта])
обе функции выдают ошибку #ЗНАЧ! но тем не менее вся формула считает. Проверю насколько верно считает на БОЛЬШИХ массивах и насколько загружает машину при обработке ошибок в двух данных функциях.
В целом на данный момент есть только единственное решение - пользовательской функцией, немного модернизировал предыдущий вариант для использования множественных условий:
Код
Public Function IFF(Диапазон As Range, Условие As Range, ParamArray Диапазон_Условие())
On Error Resume Next
IFF = Error(3) 'проверка ошибок ввода - выход "#ЗНАЧ!"
If Диапазон Is Nothing Or Условие Is Nothing Or (Диапазон.Columns.Count > 1 And Диапазон.Rows.Count > 1) Or (Условие.Cells.Count > 1) Then GoTo exf
For I = LBound(Диапазон_Условие) To UBound(Диапазон_Условие) Step 2
If (Диапазон_Условие(I).Columns.Count <> Диапазон.Columns.Count) Or (Диапазон_Условие(I).Rows.Count <> Диапазон.Rows.Count) Or Диапазон_Условие(I + 1).Cells.Count > 1 Then GoTo exf
Next
ReDim mIFF(1 To Диапазон.Cells.Count) 'формирование диапазона 0 и 1 в размерности равной обязательному условию Диапазон
For I = 1 To UBound(mIFF)
mIFF(I) = Abs(UCase(Диапазон.Cells(I).Value) Like UCase(Условие.Value)) 'проверка соответствия по обязательному условию Диапазон / Условие
For ii = LBound(Диапазон_Условие) To UBound(Диапазон_Условие) Step 2
mIFF(I) = mIFF(I) * Abs(UCase(Диапазон_Условие(ii).Cells(I).Value) Like UCase(Диапазон_Условие(ii + 1).Value)) 'проверка соответствия по необязательным условиям ДиапазонN... / УсловиеN...
Next ii
Next I
IFF = Application.Transpose(mIFF) 'возврат результата в значение функции
exf:
err.Clear
On Error GoTo 0
End Function
в результате получаем простейший вариант исчисления с множественными условиями, понимающими символы подстановки * ? ! и т.д.:
единственный и ОГРОМНЫЙ минус - работает медленно на больших диапазонах, в коде три цикла, причём есть цикл в цикле и из-за этого тормозит.
ОЧЕНЬ прошу помочьоптимизировать данный алгоритм, простейшие рекомендации - надстройка, отключить обновление не предлагайте (уже сделано), для ускорения работы на БОЛЬШИХ массивах данных. Возможно помогла бы проверка по словарю
или использование Switch, понимаю что быстрее сравнивать в массиве а не на листе, но я не совсем ясно представляю каким образом это использовать в моём случае, по сути это функция =СУММПРОИЗВЕСЛИМН, которая понимает символы подстановки * ? ! и которая недоступна в эксель см. ссылку, но крайне необходима для построения блоков для ERP-систем очень прошу откликнутся многоуважаемых ГУРУ форума Nordheim, alex1210, Jack Famous, Sanja и всех других.
Изменено: Андрей - 05.04.2022 16:53:12(добавлен файл)
tolikt написал: Чтоб не мудрить, можно просто посчитать сумму объёмов коробок и разделить на указанный эмпирический параметр 1.14114 куба, а потом округлить вверх.P.S. По опыту: работники склада, если не совсем альтернативно одарённые, сборные паллеты соберут по-своему...
Ігор Гончаренко правильно написал - по данным Вами пояснениям работники склада могут сложить "тетрис" на поддоне совершенно различными способами.
По моему пониманию эта задача - линейная матрица, с входящими данными в виде габаритов ВДШ (высота,длинна,ширина) каждого товара, партии товара под загрузку, а решение - способ оптимальной укладки.
Public Function IFF(Диапазон As Range, Условие As Range)
IFF = Error(3)
If Диапазон.Areas.Count > 1 Or Условие.Areas.Count > 1 Or (Диапазон.Columns.Count > 1 And Диапазон.Rows.Count > 1) Or (Условие.Cells.Count > 1) Then Exit Function 'проверка ошибок ввода
ReDim mIFF(1 To Диапазон.Cells.Count)
For i = 1 To UBound(mIFF)
mIFF(i) = Abs(UCase(Диапазон.Cells(i).Value) Like UCase(Условие.Value))
Next
IFF = mIFF
End Function
Первое считает верно, но в макросе определён цикл. Для малых диапазонов работает без проблем, но если диапазон => 500х365 и ячеек с вычислением такой формулы требуется минимум 15х365, то имеем существенную просадку по времени вычисления, - машина жутко тормозит при пересчёте. (>150 сек.) в моём конкретном случае (i7gen8)
Второе считает не совсем верно. По крайней мере в моём Excel 2019 функция Поиск неверно сравнивает диапазон по условию с символами подстановки. Конструкция
Цитата
ЕСЛИОШИБКА(МИН(1;ПОИСК(A17;table[Касса]));0)
вообще не работает
В общем нужен ЛЮБОЙ совет или помощь по данному вопросу.
Обращаюсь прежде всего к ,vikttur , ну и к другим гуру, способным оказать практическую помощь. Указанные формулы хороши для "строгого" условия. Т.е. если
условие в $I15 есть в диапазоне $A$2:$A$17. при этом НЕ РАБОТАЮТ символы подстановки *?! и т.д.
из примера:
если в диапазоне значения а1, а2, а-первый, а-второй, то формула не воспринимает счёт по условию "а*"
т.е. возможен ли для данного случая аналог формулы СУММЕСЛИ или СУММЕСЛИМН своего рода СУММПРОИЗВЕСЛИ или СУММПРОИЗВЕСЛИМН которые "понимают" символы подстановки?
файл примера в этой теме Очень буду признателен за помощь, нужна именно формула, без макроса.
Хотя бы (ткните носом) подскажите, где уточнить перечень функций Экселя, которые поддерживают/воспринимают символы подстановки * и ? по опыту СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН
Задача была не в этом. Нужна формула по условию (ячейка D19), т.е. сумма произведений элементов списка, выбираемых по НЕСКОЛЬКИМ условиям, в т.ч. ОБЯЗАТЕЛЬНО с символами подстановки * и ?
Касательно вашей формулы для использования деноминатора - она работает, но эта формула массива, а значит вычисляется медленнее, - там решение гораздо проще причём обычной формулой.
Повторю задачу: Формула вычисления значений списка выбираемых по НЕСКОЛЬКИМ условиям, в т.ч. ОБЯЗАТЕЛЬНО с символами подстановки * и ? в примере убрал ненужное и конкретизировал задачу
оранжевым выделены влияющие ячейки, красным - ячейка, где необходима формула с условиями, зелёным - контрольные значения вычисления формулы
написал: (диапазон = значение) формирует вектор (массив) из нулей и единиц.например: {0,1,0,0,1} ... Если условий несколько, то и векторов с нулями и единицами нужно делать несколько.
Это работает если условие "строго" определено. В случае использования символов подстановки * и/или ? данная формула НЕ РАБОТАЕТ. Может быть есть решение этой проблемы?
файл примера в этой теме Очень буду признателен за помощь, нужна именно формула, без макроса.
Не вариант - Формула работает в "идеальных" условиях Если у вас реальный перечень банков, к примеру Сбербанк, Альфабанк, RBG, EBRD и т.д. такой вариант не подходит.
остался единственный момент: как прописать конструкцию
Код
(B6:B13=B21)*(C6:C13=C21)*(D6:D13)
таким образом, чтобы она понимала знаки подстановки, например * ? аналогично тому как их воспринимает СУММЕСЛИ, т.е. Банк 1 и *1 валентные значения Кто-нибудь, посоветуйте как сделать.
Доброго времени суток. Незначительно модифицировал предыдущую задачу
По сути: необходимо сумма произведений с условием (условиями), кроме того существует необходимость деления на деноминатор (просьба плекс, макросы не предлагать)
Очень прошу откликнутся ГУРУ и помочь решить это. Искал по форуму, но или не там искал или не нашёл требуемое Заранее признателен за любой совет по данному примеру или ссылку на материал по теме суммы произведений с несколькими условиями