Функция СУММПРОИЗВ в Гугл Таблицах

Старая форма входа

Страница 1 из 1 1
Модератор форума: _Boroda_, китин

Функция СУММПРОИЗВ в Гугл Таблицах (Формулы/Formulas)

book

Дата: Суббота, 07.07.2018, 14:09 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 151

Репутация: 8 ±

Замечаний: 0% ±

Excel 2016

Уважаемые коллеги!

Делал таблицу. Все настраивал в Excel, а потом, когда по требованию шефа перенес на Гугл Таблицы, обнаружил, что некоторые формулы считают неправильно. В частности, речь идет о формуле

Код

=СУММПРОИЗВ(СУММЕСЛИМН(B3:B212;A3:A212;A218;E3:E212;Список))

Список – это именованный диапазон. Пример с файлом выложил.

Это моя ошибка или это не работает в Гугл Таблицах?

Собственно, вопрос я бы поставил шире: какие плюсы и минусы у Гугл Таблиц по сравнению с Excel?
Также буду благодарен за ссылки на материалы по Гугл Таблицам.

Спасибо.

К сообщению приложен файл: __2.xlsx (23.0 Kb)

Сообщение отредактировал book - Воскресенье, 08.07.2018, 00:17

Ответить

Pelena

Дата: Суббота, 07.07.2018, 15:49 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19403

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

book, оформите формулу тегами с помощью кнопки fx в режиме правки поста

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Gustav

Дата: Суббота, 07.07.2018, 18:02 | Сообщение № 3

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата book, 07.07.2018 в 14:09, в сообщении № 1 ()

Пример с файлом выложил.

Excel - да, спасибо, а таблицу Google? Надо ссылочку и права хотя бы на просмотр.

МОИ: Ник, Tip box: 41001663842605

Ответить

book

Дата: Суббота, 07.07.2018, 19:15 | Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 151

Репутация: 8 ±

Замечаний: 0% ±

Excel 2016

Цитата Gustav, 07.07.2018 в 18:02, в сообщении № 3 ()

Надо ссылочку

Вот ссылка. Не знаю, работает ли? Не очень хорошо пока знаю, как дать доступ...
https://docs.google.com/spreads....0830519

--
С уважением,
Андрей.

Ответить

book

Дата: Суббота, 07.07.2018, 19:38 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 151

Репутация: 8 ±

Замечаний: 0% ±

Excel 2016

Цитата Pelena, 07.07.2018 в 15:49, в сообщении № 2 ()

оформите формулу тегами

Уважаемый модератор, извините, не совсем понял, как это сделать... Не удаляйте, пожалуйста, мое сообщение. Спасибо.

--
С уважением,
Андрей.

Ответить

Pelena

Дата: Суббота, 07.07.2018, 20:55 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 19403

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Снизу от своего первого поста нажмите кнопку Правка, выделите формулу и нажмите кнопку fx на панели инструментов, сохраните изменения

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Gustav

Дата: Суббота, 07.07.2018, 23:24 | Сообщение № 7

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Что ж, имеем формулу, работающую в Excel и НЕработающую в Таблицах Google:
[vba]

Код

=SUMPRODUCT(SUMIFS(B3:B212;A3:A212;A218;E3:E212;Список))

[/vba]
В Таблицах Google не работает ни в обертке ArrayFormula(...), ни без нее.

В Таблицах Google в контексте функции SUMIFS можно предложить следующую работающую формулу - с заменой заключительной пары параметров "E3:E212;Список" на довольно сложный фрагмент "MMULT(...E3:E212...);">0"":
[vba]

Код

=ArrayFormula(SUMIFS(B3:B212;A3:A212;A218;MMULT(--(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"))

[/vba]
Эта формула, в свою очередь, не работает в Excel - в Excel в этом месте (на месте MMULT) допустимы только ссылки на диапазоны.

Как говорится, Вы хотели - вот Вам и первое наглядное различие между этими табличными процессорами

P.S. Ну, а через SUMPRODUCT Вам там уже написали (или Вы сами) очень полезную формулу:
[vba]

Код

=SUMPRODUCT((B3:B212*(A3:A212=A218)*(E3:E212=TRANSPOSE(Список))))

[/vba]

P.P.S. Хотя! Если вдруг дальше захочется и условие по дате задать не одним значением A218, а, допустим, диапазоном A218:A220 - в этом случае формула с SUMPRODUCT c внесенным (и кажущимся логичным!) изменением:
[vba]

Код

=ArrayFormula(SUMPRODUCT((B3:B212*(A3:A212=TRANSPOSE(A218:A220))*(E3:E212=TRANSPOSE(Список)))))

[/vba]
работать, увы, перестанет.

А после соответствующего изменения в формуле с SUMIFS получим правильный ответ - при двух условиях со списками значений:
[vba]

Код

=ArrayFormula(SUMIFS(
B3:B212;
MMULT(--(A3:A212=TRANSPOSE(A218:A220));ROW(A218:A220)^0);">0";
MMULT(--(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"
))

[/vba]
Вместо двойного минуса привести логические значения к числовым (а именно числа нужны функции MMULT) можно с помощью функции N:
[vba]

Код

=ArrayFormula(SUMIFS(
B3:B212;
MMULT(N(A3:A212=TRANSPOSE(A218:A220));ROW(A218:A220)^0);">0";
MMULT(N(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"
))

[/vba]что сделает формулу короче на пару символов.

Что ж, имеем формулу, работающую в Excel и НЕработающую в Таблицах Google:
[vba]

Код

=SUMPRODUCT(SUMIFS(B3:B212;A3:A212;A218;E3:E212;Список))

Код

=ArrayFormula(SUMIFS(B3:B212;A3:A212;A218;MMULT(--(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"))

P.S. Ну, а через SUMPRODUCT Вам там уже написали (или Вы сами) очень полезную формулу:
[vba]

Код

=SUMPRODUCT((B3:B212*(A3:A212=A218)*(E3:E212=TRANSPOSE(Список))))

Код

=ArrayFormula(SUMPRODUCT((B3:B212*(A3:A212=TRANSPOSE(A218:A220))*(E3:E212=TRANSPOSE(Список)))))

Код

=ArrayFormula(SUMIFS(
B3:B212;
MMULT(--(A3:A212=TRANSPOSE(A218:A220));ROW(A218:A220)^0);">0";
MMULT(--(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"
))

Код

=ArrayFormula(SUMIFS(
B3:B212;
MMULT(N(A3:A212=TRANSPOSE(A218:A220));ROW(A218:A220)^0);">0";
MMULT(N(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"
))

[/vba]что сделает формулу короче на пару символов.Gustav

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 08.07.2018, 00:26

Ответить

СообщениеЧто ж, имеем формулу, работающую в Excel и НЕработающую в Таблицах Google:
[vba]

Код

=SUMPRODUCT(SUMIFS(B3:B212;A3:A212;A218;E3:E212;Список))

Код

=ArrayFormula(SUMIFS(B3:B212;A3:A212;A218;MMULT(--(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"))

P.S. Ну, а через SUMPRODUCT Вам там уже написали (или Вы сами) очень полезную формулу:
[vba]

Код

=SUMPRODUCT((B3:B212*(A3:A212=A218)*(E3:E212=TRANSPOSE(Список))))

Код

=ArrayFormula(SUMPRODUCT((B3:B212*(A3:A212=TRANSPOSE(A218:A220))*(E3:E212=TRANSPOSE(Список)))))

Код

=ArrayFormula(SUMIFS(
B3:B212;
MMULT(--(A3:A212=TRANSPOSE(A218:A220));ROW(A218:A220)^0);">0";
MMULT(--(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"
))

Код

=ArrayFormula(SUMIFS(
B3:B212;
MMULT(N(A3:A212=TRANSPOSE(A218:A220));ROW(A218:A220)^0);">0";
MMULT(N(E3:E212=TRANSPOSE(Список));ROW(Список)^0);">0"
))

[/vba]что сделает формулу короче на пару символов.

Автор - Gustav
Дата добавления - 07.07.2018 в 23:24

_Boroda_

Дата: Воскресенье, 08.07.2018, 02:17 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 16714

Репутация: 6503 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Цитата Gustav, 07.07.2018 в 23:24, в сообщении № 7 ()

через SUMPRODUCT Вам там уже написали (или Вы сами)

Это я побаловался. Но здесь пока не писал - к тому времени еще не было исправлено замечание модератора. А потом отвлекся

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

book

Дата: Воскресенье, 08.07.2018, 11:39 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 151

Репутация: 8 ±

Замечаний: 0% ±

Excel 2016

Цитата Pelena, 07.07.2018 в 20:55, в сообщении № 6 ()

выделите формулу и нажмите кнопку fx

Сделано. Спасибо за совет.

--
С уважением,
Андрей.

Ответить

book

Дата: Воскресенье, 08.07.2018, 11:41 | Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 151

Репутация: 8 ±

Замечаний: 0% ±

Excel 2016

Цитата _Boroda_, 08.07.2018 в 02:17, в сообщении № 8 ()

через SUMPRODUCT Вам там уже написали (или Вы сами)

Это я побаловался

Спасибо!

--
С уважением,
Андрей.

Ответить

book

Дата: Воскресенье, 08.07.2018, 12:21 | Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 151

Репутация: 8 ±

Замечаний: 0% ±

Excel 2016

Спасибо всем, кто откликнулся!
Хочу поделиться своими результатами исследования на тему "Функция СУММПРОИЗВ в Excel и GS.
Если кратко, есть формулы, которые работают в обеих системах, есть, которые только в одной. Примеры - в приложенном файле. Надеюсь, кому-то это поможет.
С уважением,

К сообщению приложен файл: 1931174.xlsx (37.1 Kb)

--
С уважением,
Андрей.

Ответить

Функция СУММПРОИЗВ в Гугл Таблицах (Формулы/Formulas)

Страница 1 из 1
1