Выбрать дату в календареВыбрать дату в календаре

Страницы: 1
ФУНКЦИЯ ФИЛЬТР для версий до 365, Замена функции ФИЛЬТР из 365 формулами массива
 
Цитата
написал:
ну тут есть путь сразу определить количество нужных элементов для создания последовательности черезCOUNTIFS(Index[Дата];">="&H3;Index[Дата];" 0")последовательноть от 1 до ...ROW(A1:INDEX(A:A;COUNTIFS(Index[Дата];">="&H3;Index[Дата];" 0")) и сразу отбросить то, что не подходит. То есть фильтровать не только по дате, но и по значению (H3 =Index[Дата])*(Index[показатели]<>0)
Ну то есть "слона есть по частям..."
1. определиться с размерностью нового массива
2. исключить неподходящие значения
3. заполнить новый массив подходящими значеними

принцип понятен. Примерно так я и действовал, к сожалению, где-то что-то идёт не так.
В любом случае за помощь 100 ++ в карму.

Буду ковырять дальше
СЧЁТЕСЛИМН для уникальных значений, Подсчет уникальных значений в таблице с несколькими условиями
 
Эта тема пересекается с ЭТОЙ ТЕМОЙ

в случае задвоенных (дублированных) строк , неправильного ввода и т.д. будет считать неверно.

жаль что в екселе до сих пор нет функций СУММПРОИЗВЕДЕСЛИ() СУММПРОИЗВЕДЕСЛИМН()  
Изменено: Андрей - 26.06.2025 14:29:23
ФУНКЦИЯ ФИЛЬТР для версий до 365, Замена функции ФИЛЬТР из 365 формулами массива
 
Цитата
написал:
Интересно: это просто короче записывается, чем ЛОЖЬ() - или так работает быстрее?
Это работает правильно! Если вы напишите ЛОЖЬ()  - то в англоязычной версии у вас будет ошибка
ФУНКЦИЯ ФИЛЬТР для версий до 365, Замена функции ФИЛЬТР из 365 формулами массива
 
Цитата
написал:
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. они тоже меняются)
ФУНКЦИЯ ФИЛЬТР для версий до 365, Замена функции ФИЛЬТР из 365 формулами массива
 
Доброго времени суток, уважаемые форумчане.

Имеется насущная необходимость замены функции ФИЛЬТР формулами массива, для сохранения функционала файла в более ранних версиях EXCEL.

по сути надо использовать конструкцию
Код
{ИНДЕКС(Index[показатели];НАИМЕНЬШИЙ(ЕСЛИ((H3<=Index[Дата])*(H4>=Index[Дата]);СТРОКА(Index[Дата])-1;0);СТРОКА()-Index[[#Заголовки];[Дата]]))}
для дальнейших вычислений например =СРГЕОМ(), которые при нулевых значениях выдают ошибку

Т.Е. требуется сократить массив значений убрав нулевые (или например дублирующиеся) и получить меньший массив В ЯЧЕЙКЕ для дальнейших вычислений.

должно получаться как-то так





ОЧЕНЬ прошу помочь или хотя бы посоветовать в каком направлении искать

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[Валюта])
Изменено: Андрей - 19.05.2023 16:19:40
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
Цитата
написал:
Да ладно?
Спасибо тебе, мил ЧЕЛОВЕК.
Вот уж никогда не подумал бы что такая конструкция будет РАБОТАТЬ особенно если учесть что вот это
Цитата
СУММЕСЛИ(C$21:C$25;table[Валюта];D$21:D$25)
/
СУММЕСЛИ(C$21:C$25;table[Валюта];B$21:B$25))
обе функции выдают ошибку #ЗНАЧ!
но тем не менее вся формула считает. Проверю насколько верно считает на БОЛЬШИХ массивах и насколько загружает машину при обработке ошибок в двух данных функциях.
Изменено: Андрей - 20.04.2023 10:15:37
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
За год так и не нашлось ответа. Жаль....
Ещё раз обращаюсь к тем, кто может помочь.
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
В целом на данный момент есть только единственное решение - пользовательской  функцией, немного модернизировал предыдущий вариант для использования множественных условий:
Код
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
в результате получаем простейший вариант исчисления с множественными условиями, понимающими символы подстановки * ? ! и т.д.:
Цитата
=СУММПРОИЗВ(IFF(table[Касса];A14;table[Банк];B14;table[Валюта];C14)*(table[Сумма]);СУММЕСЛИ(C18:C22;C3:C10;D18:D22);1/СУММЕСЛИ(C18:C22;C3:C10;B18:B22))
единственный и ОГРОМНЫЙ минус - работает медленно на больших диапазонах, в коде три цикла, причём есть цикл в цикле и из-за этого тормозит.

ОЧЕНЬ прошу помочь оптимизировать данный алгоритм, простейшие рекомендации - надстройка, отключить обновление не предлагайте (уже сделано), для ускорения работы на БОЛЬШИХ массивах данных. Возможно помогла бы проверка по словарю
Цитата
Set dic = CreateObject("scripting.dictionary")
...
dic.comparemode = 1:
mIFF = .UsedRange.Value
dic.Item(CStr(mIFF(i, 1))) = 0: dic.Item(CStr(mIFF(i, 2))) = 0
или использование Switch, понимаю что быстрее сравнивать в массиве а не на листе, но я не совсем ясно представляю каким образом это использовать в моём случае, по сути это функция =СУММПРОИЗВЕСЛИМН, которая понимает символы подстановки * ? ! и которая недоступна в эксель см. ссылку, но крайне необходима для построения блоков для ERP-систем
очень прошу откликнутся многоуважаемых ГУРУ форума Nordheim, alex1210, Jack Famous, Sanja и всех других.  
Изменено: Андрей - 05.04.2022 16:53:12 (добавлен файл)
Расчет заказа, Расчет в Excel числа паллет для сбора заказа
 
Цитата
tolikt написал:
Чтоб не мудрить, можно просто посчитать сумму объёмов коробок и разделить на указанный эмпирический параметр 1.14114 куба, а потом округлить вверх.P.S. По опыту: работники склада, если не совсем альтернативно одарённые, сборные паллеты соберут по-своему...


Ігор Гончаренко правильно написал - по данным Вами пояснениям работники склада могут сложить "тетрис" на поддоне совершенно различными способами.

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

1) Определяем пользовательскую функцию макросом:  
Код
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
далее уже в листе в ячейке D17
Цитата
{=СУММПРОИЗВ(ТРАНСП(IFF(A6:A13;A17)*IFF(B6:B13;B17)*IFF(C6:C13;C17))*(D6:D13);СУММЕСЛИ(C21:C25;C6:C13;D21:D25);1/СУММЕСЛИ(C21:C25;C6:C13;B21:B25))}

2)  Решение формулой ячейке E17
Цитата
=СУММПРОИЗВ((ЕСЛИОШИБКА(ПОИСК(A17;table[Касса]);0)*ЕСЛИОШИБКА(ПОИСК(B17;table[Банк]);0)*ЕСЛИОШИБКА(ПОИСК(C17;table[Валюта]);0))*(D6:D13);СУММЕСЛИ(C21:C25;C6:C13;D21:D25);1/СУММЕСЛИ(C21:C25;C6:C13;B21:B25))
Оба решения не устраивают по ряду причин:

Первое считает верно, но в макросе определён цикл. Для малых диапазонов работает без проблем, но если диапазон => 500х365 и ячеек с вычислением такой формулы требуется минимум 15х365, то имеем существенную просадку по времени вычисления, - машина жутко тормозит при пересчёте. (>150 сек.) в моём конкретном случае (i7gen8)

Второе считает не совсем верно. По крайней мере в моём Excel 2019 функция Поиск неверно сравнивает диапазон по условию с символами подстановки.
Конструкция
Цитата
ЕСЛИОШИБКА(МИН(1;ПОИСК(A17;table[Касса]));0)
вообще не работает

В общем нужен ЛЮБОЙ совет или помощь по данному вопросу.
СУММПРОИЗВ с условием
 
Всем доброго времени суток.

Обращаюсь прежде всего к , vikttur  , ну и к другим гуру, способным оказать практическую помощь.
Указанные формулы хороши для "строгого" условия. Т.е. если
Цитата
написал:
=СУММПРОИЗВ($B$2:$B$17;C$2:C$17;--($A$2:$A$17=$I15))
условие в $I15 есть в диапазоне $A$2:$A$17.
при этом НЕ РАБОТАЮТ символы подстановки *?! и т.д.  

из примера:

если в диапазоне значения а1, а2, а-первый, а-второй, то формула не воспринимает счёт по условию "а*"

т.е. возможен ли для данного случая аналог формулы СУММЕСЛИ или СУММЕСЛИМН своего рода
СУММПРОИЗВЕСЛИ или СУММПРОИЗВЕСЛИМН
которые "понимают" символы подстановки?

файл примера в этой теме
Очень буду признателен за помощь, нужна именно формула, без макроса.  
Изменено: Андрей - 21.03.2022 09:49:28
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
Хотя бы (ткните носом) подскажите, где уточнить перечень функций Экселя, которые поддерживают/воспринимают символы подстановки * и ?
по опыту СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН  
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
Цитата
написал:
формула массива:
Задача была не в этом. Нужна формула по условию (ячейка D19), т.е. сумма произведений элементов списка, выбираемых по НЕСКОЛЬКИМ условиям, в т.ч. ОБЯЗАТЕЛЬНО с символами подстановки * и ?  

Касательно вашей формулы для использования деноминатора - она работает, но эта формула массива, а значит вычисляется медленнее, - там решение гораздо проще причём обычной формулой.

Повторю задачу:
Формула вычисления значений списка выбираемых по НЕСКОЛЬКИМ условиям, в т.ч. ОБЯЗАТЕЛЬНО с символами подстановки * и ?  
в примере убрал ненужное и конкретизировал задачу

оранжевым выделены влияющие ячейки, красным - ячейка, где необходима формула с условиями, зелёным - контрольные значения вычисления формулы
Изменено: Андрей - 09.03.2022 10:10:17
Сумма значений совпадающих по двум критериям
 
Цитата
написал:
(диапазон = значение) формирует вектор (массив) из нулей и единиц.например: {0,1,0,0,1}
...
Если условий несколько, то и векторов с нулями и единицами нужно делать несколько.
Это работает если условие "строго" определено. В случае использования символов подстановки * и/или ? данная формула НЕ РАБОТАЕТ.
Может быть есть решение этой проблемы?


файл примера в этой теме
Очень буду признателен за помощь, нужна именно формула, без макроса.  
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
Цитата
написал: ЕЧИСЛО(НАЙТИ(B22;B6:B13))
Не вариант - Формула работает в "идеальных" условиях
Если у вас реальный перечень банков, к примеру Сбербанк, Альфабанк, RBG, EBRD  и т.д. такой вариант не подходит.

Может есть ещё варианты...
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
Покурил форум - разобрался как делать, в принципе ничего сложного:
Код
=СУММПРОИЗВ((B6:B13=B21)*(C6:C13=C21)*(D6:D13);СУММЕСЛИ(C25:C29;C6:C13;D25:D29);1/СУММЕСЛИ(C25:C29;C6:C13;B25:B29)) 
остался единственный момент: как прописать конструкцию
Код
(B6:B13=B21)*(C6:C13=C21)*(D6:D13)

таким образом, чтобы она понимала знаки подстановки, например * ? аналогично тому как их воспринимает СУММЕСЛИ, т.е. Банк 1 и *1 валентные значения
Кто-нибудь, посоветуйте как сделать.
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
Доброго времени суток.
Незначительно модифицировал предыдущую задачу

По сути: необходимо сумма произведений с условием (условиями), кроме того существует необходимость деления на деноминатор
(просьба плекс, макросы не предлагать)

Очень прошу откликнутся ГУРУ и помочь решить это.
Искал по форуму, но или не там искал или не нашёл требуемое
Заранее признателен за любой совет по данному примеру или ссылку на материал по теме суммы произведений с несколькими условиями

п.с. Всех с наступающим Новым 2022 годом!!!
Страницы: 1
Наверх