Есть ли аналог Экселевской СУМЕСЛИМН в ООо

Автор Vova60, 1 мая 2011, 18:07

0 Пользователи и 1 гость просматривают эту тему.

Vova60

Существует ли аналог экселевской команды СУММЕСЛИМН (Exell 2007) в таблице ООо либо возможность создать нечто подобное? Суммирование по нескольким параметрам. В Экселе это выглядит так: =СУММЕСЛИМН(O$5:O$2000;B$5:B$2000;B2055;C$5:C$2000;C2055;D$5:D$2000;D2055)

Vladjmir

#1
Самый простой способ проверить, есть ли такая функция: сохранить файл в Excel'е в старом формате .xls, открыть его Calc'ом и посмотреть, какая функция будет записана в этой ячейке.

Vova60

Цитата: Vladjmir от  1 мая 2011, 18:03Самый простой способ проверить, есть ли такая функция: сохранить файл в Excel'е в старом формате .xls, открыть его Calc'ом и посмотреть, какая функция будет записана в этой ячейке.

Calc выдаёт Экселевскую функцию только на английском SUMIFS, Calc её не поддерживает, функция Calc SUMIF обрабатывает только одно условие, а требуется суммировать по нескольким условиям

JohnSUN

Есть простой способ затолкать в одну формулу кучу условий, объединенных по "И" (описан в справке по функции SUM).
Поскольку TRUE (ИСТИНА) в Calc'е всегда равна единице, а FALSE - нулю, то достаточно суммируемые величины просто домножить на соответствующие условия. Есть несколько способов записи...
К сожалению, не смог расшифровать приведенную в первом сообщении формулу
=СУММЕСЛИМН(O$5:O$2000;B$5:B$2000;B2055;C$5:C$2000;C2055;D$5:D$2000;D2055)
Просто не знаю, какие параметры этой функции имелись в виду. Можно, конечно, предположить, что первый параметр это собственно суммируемые данные, второй-четвертый-шестой (и, наверное, так далее?) - диапазоны проверяемых ячеек, третий-пятый-седьмой - ячейки условий...
Если бы речь шла о сравнении на точное совпадение со значением, то формула в Calc'е выглядела бы так:
=SUMPRODUCT(O5:O2000*(B5:B2000=B2055)*(C5:C2000=C2055)*(D5:D2000=D2055))
Само собой, если в ячейках 2055 строки записаны не значения, а условия (например, ">14" или "<0"), то их нужно перенести в формулу.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

RFJ


JohnSUN

Ну да, я так и подумал... Но Vova60 не сказал, что у него в B2055:D2055

PS. Забыл указать другой вариант написания SUMPRODUCT:
=SUMPRODUCT(O5:O2000;(B5:B2000=2);(C5:C2000=0);(D5:D2000=10))
То есть записывается не произведение, а перечень массивов - функция сама перемножит нужное
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Vova60

Цитата: RFJ от  3 мая 2011, 19:54Просто не знаю, какие параметры этой функции имелись в виду. Можно, конечно, предположить, что первый параметр это собственно суммируемые данные, второй-четвертый-шестой (и, наверное, так далее?) - диапазоны проверяемых ячеек, третий-пятый-седьмой - ячейки условий...

Да совершенно верно и при совпадении, в данном случае всех трёх условий, в одной строке, суммируются данные соответствующие этой строке. На условия можно ссылаться как в данной формуле, либо прописать их в формулу. Эксел 2007 (в 2003 ещё нет) позволяет прописать несколько условий (до 30 по моему, не проверял), а Calk в SUMIF только одно условие

Vova60

Упустил. Условие это не число,  это текст - Вася, Федя, красный, автомашина.....

Vova60

Цитата: RFJ от  3 мая 2011, 19:54Если бы речь шла о сравнении на точное совпадение со значением, то формула в Calc'е выглядела бы так:Код:=SUMPRODUCT(O5:O2000*(B5:B2000=B2055)*(C5:C2000=C2055)*(D5:D2000=D2055))
Да именно это и требовалось. Вставил работает, правда не совсем понял как, буду рыть. У нас неделю назад, в целях экономии, контору перевели на бесплатное ПО, вот переходим...
Спасибо.

Vova60

Довольно странно, вот в таком виде =SUMPRODUCT(O5:O2000;B5:B2000=B2055;C5:C2000=C2055;D5:D2000=D2055) результат такой же как и =SUMPRODUCT(O5:O2000*(B5:B2000=B2055)*(C5:C2000=C2055)*(D5:D2000=D2055)), даже не знаю что сказать, настолько разнообразен синтаксис?

JohnSUN

Да нет, не странно... Это я виноват, оборвал изложение на полуслове, отвлекся. Потому так и получилось.
Еще раз:
В справке по функции SUM() приведен пример использования логических выражений для включения-исключения отдельных значений в диапазоне суммирования. Идея заключается в домножении числа на результат логического выражения TRUE или FALSE (1 или 0). Чтобы SUM() с такими параметрами выдала правильный результат, она должна быть формулой массива, вводиться с помощью Ctrl+Shift+Enter.
SUMPRODUCT() возвращает одно значение по параметрам массивам. То есть для нее Ctrl+Shift+Enter не нужен. Как промежуточный вариант рассуждения я и привел запись с умножениями - "напрямую перетащил" из идеи массивной SUM.
Задача SUMPRODUCT - перемножить соответствующие элементы массивов-параметров и просуммировать эти произведения. Поэтому знаки умножения в записи этой формулы не нужны, достаточно перечислить массивы через точку с запятой. Это и есть правильный, окончательный вариант формулы
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Vova60


Chumakov_JA

=СУММЕСЛИМН(Платежи!$E:$E;Платежи!$B:$B;A9;Платежи!$C:$C;КБК1;Платежи!$D:$D;">=01.01.2012";Платежи!$D:$D;"<=31.01.2012")

ну помогите заменить эту формулу
на листе "Платежи" (номер,кбк,дата,сумма)
выбираю платежи номера который в ячейке А9
за январь 2012 по виду КБК1

JohnSUN

Ну, попробуем помочь...
=SUM(Платежи.$E1:$E65535
  *(Платежи.$B1:$B65535=A9)
  *(Платежи.$C1:$C65535=КБК1)
  *(Платежи.$D1:$D65535>=Date(2012;1;1))
  *(Платежи.$D1:$D65535<=DATE(2012;1;31)) Ctrl+Shift+Enter

К сожалению, задать весь столбец в виде $B:$B не выйдет - номера строк нужно указывать обязательно.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне