Есть нетривиальная, на мой взгляд, задача: В ячейках есть находятся формулы - функция СУММЕСЛИМН, которая возвращает из большой базы данных по нескольким условиям, но есть дебильное требование заказчика, чтобы в ячейке была не функция, а сумма чисел, которые эта функция суммирует, т.е. формула должна быть не "=суммеслимн(......)", а типа "=число 1 + число 2 + число 3 +...+ число n". Есть идеи, как можно такое реализовать с помощью макроса или пользовательской функции? Может, кто сталкивался...
Дмитрий Болдырев, здравствуйте! Реализовать можно без проблем, но нет примера, так что алгоритм: 1. запоминаем все столбцы для просмотра критериев в массивы (или в один массив, если они в одной таблице) 2. пробегаем по всем столбцам и, при совпадении всех критериев, собираем словарь, только, если в случае суммы, это был бы словарь dic(key)=dic(key) + arr(row,column), то тут будет сцепка dic(key)=dic(key) & "+" & arr(row,column)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Думаю, что здесь СцепитьЕсли не совсем в кассу. Скорее имелось ввиду это: Отобразить в формулах вместо ссылок на ячейки значения ячеек Но Дмитрий Болдырев, имейте ввиду: СУММЕСЛИМН может и полностью столбцы складывать, а у формул есть ограничение на кол-во символов, что неизбежно повлечет ошибки. С малым кол-вом входных ячеек этот вариант еще имеет право на жизнь, а если в каждом аргументе строк эдак по 100-200 хотя бы, то это провал. Можете так заказчику и передать.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Дмитрий(The_Prist) Щербаков, спасибо, пригодится, но не то. Для суммеслимн не подойдет. Надо показывать не составляющие формулы, а скорее результат работы функции (массив значений, удовлетворяющих условиям, записанных через "+"), как если бы их вводили вручную: "=123+333+44,5+79" То есть в ячейке будет результат - значение 579,5.
вангую, что для "проверки" — я на таких "контроллёров" насмотрелся
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
в этом случае надо писать собственную UDF, которая будет принимать в качестве аргументов массивы и условия, искать подходящие под условия ячейки и записывать их как результат. Что-то вроде этого еще в сообщении #2 показал Jack Famous
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Jack Famous, Да, "проверяющие" требуют. Этим "специалистам": "так удобно" и никаких аргументов слышать не хотят.
_Igor_61, Ну я ж написал, на листе должен быть результат сложения. В ячейке - формула сложения через "+".
Дмитрий(The_Prist) Щербаков, моих знаний недостаточно, чтобы такую функцию написать. Со словарями не умею работать. ((( Мне бы хотя бы пример, может я его бы и переработал....
нам бы тоже...Хоть какой-то от Вас с тем, что имеется и что хотите в итоге. Потому что для каждой функции по сути надо будет писать отдельную UDF со своими условиями... И по сути, раз думаете, что сможете переработать - в том же сообщении #2 Вам дали ссылку на статью с функцией, которая отбирает данные по условию. Там достаточно только разделитель на + поменять и может условий добавить.
В прилагаемом файле формула: =СУММЕСЛИМН(D2:D100;A2:A100;">=01.01.2015";B2:B100;">5";C2:C100;"ручка") давала результат: 217 642
Я написал формулу: =ОБЪЕДИНИТЬ("+";ИСТИНА;ЕСЛИ((A2:A100>=ДАТАЗНАЧ("01.01.2015"))*(B2:B100>5)*(C2:C100="ручка");D2:D100;"")) её результат: "10569+13035+14216+13228+15764+14530+10572+10660+15081+19191+19527+12939+12702+19682+15946"
Я скопировал её, вставил значение и в самом начале поставил "="
Option Explicit
'===========================================================================================
Function MergeNumIf(crit, rngCrit As Range, rngMerge As Range) As String
Dim x, arrCrit, arrMerge, arrRes() As String, txt$, s#, r&, n&
On Error GoTo er
If rngMerge.Columns.Count <> 1 Or rngMerge.Columns.Count <> 1 Then GoTo er
If rngMerge.Areas.Count <> 1 Or rngMerge.Areas.Count <> 1 Then GoTo er
If rngMerge.Cells.Count <> rngMerge.Cells.Count Then GoTo er
arrCrit = rngCrit.Value2
arrMerge = rngMerge.Value2
If Not IsArray(arrCrit) Then
ReDim arrCrit(1 To 1, 1 To 1): arrCrit = rngCrit.Value2
ReDim arrMerge(1 To 1, 1 To 1): arrMerge = rngMerge.Value2
End If
ReDim arrRes(UBound(arrCrit, 1) - 1): n = -1
For r = 1 To UBound(arrCrit, 1)
If arrCrit(r, 1) = crit Then
n = n + 1
arrRes(n) = arrMerge(r, 1)
s = s + arrMerge(r, 1)
End If
Next r
If n = -1 Then
MergeNumIf = "0=0"
Else
ReDim Preserve arrRes(n)
txt = Join(arrRes, "+"): If Len(txt) > 32700 Then txt = Left$(txt, 32700)
MergeNumIf = txt & "=" & s
End If
Exit Function
er: MergeNumIf = CVErr(xlErrNA)
End Function
'===========================================================================================
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Здравствуйте, коллеги! Бахтиёр - на мой взляд, интересная идея! Единственное уточнение - даты в текстовом виде в формулах лучше записывать как гггг-мм-дд (в Вашем случае это "2015-01-01"), тогда это будет работать при любых региональных настройках.
Для несчастливых обладалей MS Office версий <=2016 можно воспользоваться этой же идеей. В примере Бахтиёра оператор
Код
res = ActiveSheet.Evaluate("=IF((A2:A100>=--""2015-01-01"")*(B2:B100>5)*(C2:C100=""ручка""),D2:D100,"""")")
вернет массив, из которого нужно будет взять ненулевые числа и сцепить с помощью знака "+". Это открывает дорогу к написанию соответствующих макросов.