Сообщение успешно добавлено.

Страницы: 1
RSS
Сумма произведений из разных таблиц., Расчёт суммы произведений в разных таблицах одной формулой
 
Здравствуйте!
Прошу помочь с решением небольшой задачи более изящным способом, чем она решена сейчас.
Имеются суммы в разных валютах в одной таблице  и курсы валют в другой таблице. Нужно найти сумму в рублях. Сейчас реализовано через сумму сумм с условием, но это не оптимально, потому что при добавлении новых валют надо будет менять формулу — добавлять ещё раз суммеслимн
Прошу подсказать как решить это одной формулой, например, суммпроизв. При этом диапазоны могут быть именными, чтобы не менять формулу.
Файл прикидываю.
 
medvedevda,
Код
=СУММПРОИЗВ(D6:D10;СУММЕСЛИ(C14:C17;C6:C10;D14:D17))
В таблицу "курс валют" каждая валюта должна встречаться строго один раз.
Изменено: Казанский - 21.01.2019 18:43:02
 
Казанский, большое спасибо!
 
Доброго времени суток.
Незначительно модифицировал предыдущую задачу

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

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

п.с. Всех с наступающим Новым 2022 годом!!!
 
Покурил форум - разобрался как делать, в принципе ничего сложного:
Код
=СУММПРОИЗВ((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 валентные значения
Кто-нибудь, посоветуйте как сделать.
 
Код
(B6:B13=Т(B22))
попробуйте заменить на
Код
ЕЧИСЛО(НАЙТИ(B22;B6:B13))
в ячейке B22 подставлять уже нужное значение без *. Например, "1"
 
Цитата
написал: ЕЧИСЛО(НАЙТИ(B22;B6:B13))
Не вариант - Формула работает в "идеальных" условиях
Если у вас реальный перечень банков, к примеру Сбербанк, Альфабанк, RBG, EBRD  и т.д. такой вариант не подходит.

Может есть ещё варианты...
 
формула массива:
Код
=СУММ(D2:D9*(C2:C9=ТРАНСП(C18:C22))*ТРАНСП(D18:D22/B18:B22))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
написал:
формула массива:
Задача была не в этом. Нужна формула по условию (ячейка D19), т.е. сумма произведений элементов списка, выбираемых по НЕСКОЛЬКИМ условиям, в т.ч. ОБЯЗАТЕЛЬНО с символами подстановки * и ?  

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

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

оранжевым выделены влияющие ячейки, красным - ячейка, где необходима формула с условиями, зелёным - контрольные значения вычисления формулы
Изменено: Андрей - 09.03.2022 10:10:17
 
Хотя бы (ткните носом) подскажите, где уточнить перечень функций Экселя, которые поддерживают/воспринимают символы подстановки * и ?
по опыту СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН  
 
Есть два промежуточных решения:

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)
вообще не работает

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

Сообщение успешно добавлено.

Наверх