Страницы: 1
RSS
Результат формулы в виде =число1+число2+...
 
Доброго дня!

Есть нетривиальная, на мой взгляд, задача:
В ячейках есть находятся формулы - функция СУММЕСЛИМН, которая возвращает из большой базы данных по нескольким условиям, но есть дебильное требование заказчика, чтобы в ячейке была не функция, а сумма чисел, которые эта функция суммирует, т.е. формула должна быть не "=суммеслимн(......)", а типа "=число 1 + число 2 + число 3 +...+ число n".
Есть идеи, как можно такое реализовать с помощью макроса или пользовательской функции? Может, кто сталкивался...
Изменено: Дмитрий Болдырев - 30.01.2020 11:27:43
 
Дмитрий Болдырев, здравствуйте!
Реализовать можно без проблем, но нет примера, так что алгоритм:
  1. запоминаем все столбцы для просмотра критериев в массивы (или в один массив, если они в одной таблице)
  2. пробегаем по всем столбцам и, при совпадении всех критериев, собираем словарь, только, если в случае суммы, это был бы словарь dic(key)=dic(key) + arr(row,column), то тут будет сцепка dic(key)=dic(key) & "+" & arr(row,column)

Готовое решение: Как сцепить несколько значений в одну ячейку по критерию?
Название темы: СцепитьЕсли. Сцепить аргументы по нескольким условиям
Изменено: Jack Famous - 30.01.2020 11:15:49
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Думаю, что здесь СцепитьЕсли не совсем в кассу. Скорее имелось ввиду это: Отобразить в формулах вместо ссылок на ячейки значения ячеек
Но Дмитрий Болдырев, имейте ввиду: СУММЕСЛИМН может и полностью столбцы складывать, а у формул есть ограничение на кол-во символов, что неизбежно повлечет ошибки. С малым кол-вом входных ячеек этот вариант еще имеет право на жизнь, а если в каждом аргументе строк эдак по 100-200 хотя бы, то это провал. Можете так заказчику и передать.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков, спасибо, пригодится, но не то.
Для суммеслимн не подойдет. Надо показывать не составляющие формулы, а скорее результат работы функции (массив значений, удовлетворяющих условиям, записанных через "+"), как если бы их вводили вручную: "=123+333+44,5+79" То есть в ячейке будет результат - значение 579,5.
 
Дмитрий Болдырев, если это нужно разово, чтобы показать заказчику, как оно там внутри работает, то "Формулы"/"Вычислить формулу" не подойдёт?
Я не волшебник, я только учусь.
 
Цитата
Дмитрий Болдырев написал:
=123+333+44,5+79
Цитата
Дмитрий Болдырев написал:
значение 579,5
Что же все-таки должно быть в ячейке? :)
 
Цитата
Wiss написал:
"Формулы"/"Вычислить формулу" не подойдёт?
Ни ячейки ни слогаемые не покажет.
И вообще, как автор правильно сказал, требование заказчика дебильное.
Уточните зачем ему это надо.
Изменено: Максим В. - 30.01.2020 13:35:16
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Цитата
Максим В.: зачем ему это надо
вангую, что для "проверки" — я на таких "контроллёров" насмотрелся  :D
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, а верный результат не есть проверка? Да и тот кто может такую фишку реальзовать, сможет и результат исказить так, что заказчик не поймет.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Цитата
Дмитрий Болдырев написал:
ригодится, но не то
в этом случае надо писать собственную UDF, которая будет принимать в качестве аргументов массивы и условия, искать подходящие под условия ячейки и записывать их как результат. Что-то вроде этого еще в сообщении #2 показал Jack Famous
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Jack Famous, Да, "проверяющие" требуют.
Этим "специалистам": "так удобно" и никаких аргументов слышать не хотят.

_Igor_61, Ну я ж написал, на листе должен быть результат сложения. В ячейке - формула сложения через "+".

Дмитрий(The_Prist) Щербаков, моих знаний недостаточно, чтобы такую функцию написать. Со словарями не умею работать. ((( Мне бы хотя бы пример, может я его бы и переработал....
Изменено: Дмитрий Болдырев - 30.01.2020 14:32:38
 
Цитата
Дмитрий Болдырев написал:
Мне бы хотя бы пример
нам бы тоже...Хоть какой-то от Вас с тем, что имеется и что хотите в итоге. Потому что для каждой функции по сути надо будет писать отдельную UDF со своими условиями...
И по сути, раз думаете, что сможете переработать - в том же сообщении #2 Вам дали ссылку на статью с функцией, которая отбирает данные по условию. Там достаточно только разделитель на + поменять и может условий добавить.
Изменено: Дмитрий(The_Prist) Щербаков - 30.01.2020 15:56:51
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
В прилагаемом файле формула:
=СУММЕСЛИМН(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"

Я скопировал её, вставил значение и в самом начале поставил "="

Результат: 217 642
Изменено: Бахтиёр - 30.01.2020 17:22:20
 
Цитата
Дмитрий Болдырев: Этим "специалистам": "так удобно" и никаких аргументов слышать не хотят
прекрасно вас понимаю — проверяйте пользовательскую макрофункцию
Function MergeNumIf()
Изменено: Jack Famous - 30.01.2020 18:06:59
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Здравствуйте, коллеги! Бахтиёр - на мой взляд, интересная идея! Единственное уточнение - даты в текстовом виде в формулах лучше записывать как гггг-мм-дд (в Вашем случае это "2015-01-01"), тогда это будет работать при любых региональных настройках.

Для несчастливых обладалей MS Office версий <=2016 можно воспользоваться этой же идеей. В примере Бахтиёра оператор
Код
 res = ActiveSheet.Evaluate("=IF((A2:A100>=--""2015-01-01"")*(B2:B100>5)*(C2:C100=""ручка""),D2:D100,"""")")

вернет массив, из которого нужно будет взять ненулевые числа и сцепить с помощью знака "+".
Это открывает дорогу к написанию соответствующих макросов.
Владимир
 
Jack Famous, Спасибо за функцию! С помощью доп столбца в базе все заработало!
Тему можно закрывать. Всем огромное спасибо за участие!

В итоге еще я вспомнил про функцию уважаемого ZVI вот отсюда: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=42
Которая позволит решить эту задачу.
Страницы: 1
Наверх