Функция Суммпроизв () - выборка по условию список-массив - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Функция Суммпроизв () - выборка по условию список-массив - Мир MS Excel

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

Функция Суммпроизв () - выборка по условию список-массив (Формулы/Formulas)

Функция Суммпроизв () - выборка по условию список-массив

Дата: Вторник, 03.02.2015, 14:35 | Сообщение № 1

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

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

Сообщений: 101

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

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

Excel 2007

Добрый день.

Есть ли механизм работы функции

Код

=суммпроизв ()

, позволяющий осуществлять отбор значений при выполнении условия соответствия значений группы из списка - значениям в массиве.

Пример. Список товаров из группы А (А1, А2, А3) = присвоено имя списку Товар_А
Массив со значениями А1, А2, А3, Б1, Б2, Б3 и.т.д = присвоено имя массиву Товары
Массив с продажами 1,2,3 и.д.т = присвоено имя массиву Продажи

Вариант формулы не работает

Код

=суммпроизв(Продажи; 1*(Товар_А=Товары))

[moder]Где файл?

Ответить

Дата: Вторник, 03.02.2015, 14:49 | Сообщение № 2

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

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

Сообщений: 101

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

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

Excel 2007

файл

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

Ответить

Дата: Вторник, 03.02.2015, 14:55 | Сообщение № 3

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

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

Сообщений: 19511

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

Замечаний: ±

Excel 365 & Mac Excel

Здравствуйте.
Формула массива вводится сочетанием клавиш Ctrl+Shift+Enter

Код

=СУММПРОИЗВ(Продажи*(Товары=ТРАНСП(Товар_А)))

немного короче

Код

=СУММ(Продажи*(Товары=ТРАНСП(Товар_А)))

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

Ответить

Дата: Вторник, 03.02.2015, 15:00 | Сообщение № 4

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

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

Сообщений: 101

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

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

Excel 2007

Спасибо.

Помогло.

Транспонировать обязательно?

Ответить

Дата: Вторник, 03.02.2015, 15:03 | Сообщение № 5

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

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

Сообщений: 19511

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

Замечаний: ±

Excel 365 & Mac Excel

Ну, можно группу записать в строку, тогда не надо будет транспонировать

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

Ответить

Дата: Вторник, 03.02.2015, 15:07 | Сообщение № 6

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

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

Сообщений: 101

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

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

Excel 2007

Еще раз спасибо.

Ответить

Дата: Вторник, 03.02.2015, 15:17 | Сообщение № 7

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

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

Сообщений: 16885

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

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Цитата zzbear, 03.02.2015 в 15:00, в сообщении № 4

Транспонировать обязательно?

Не, не обязательно. Обычная формула

Код

=СУММПРОИЗВ(Продажи*ЕЧИСЛО(ПОИСКПОЗ(Товары;Товар_А;)))

К сообщению приложен файл: -456875_1.xlsx (12.0 Kb)

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

Ответить

Дата: Вторник, 03.02.2015, 15:20 | Сообщение № 8

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

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

Сообщений: 101

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

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

Excel 2007

_Boroda_, Спасибо

Ответить

Дата: Вторник, 03.02.2015, 15:35 | Сообщение № 9

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

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

Сообщений: 101

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

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

Excel 2007

Сейчас обратил внимание на то что
1 вариант решения перестает работать в случае если массивы данных неограниченны

К примеру массив Продажи='Выборка продаж'!$B:$B

2 вариант работает с небольшим изменением

Код

=СУММПРОИЗВ(Продажи; 1*ЕЧИСЛО(ПОИСКПОЗ(Товары;Товар_А;))*($A8=Магазины))

Ответить

Дата: Вторник, 03.02.2015, 15:36 | Сообщение № 10

Группа: Друзья

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

Сообщений: 2941

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

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

Код

=СУММПРОИЗВ(СУММЕСЛИ(Товары;Товар_А;Продажи))

Ответить

Дата: Вторник, 03.02.2015, 15:36 | Сообщение № 11

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

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

Сообщений: 101

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

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

Excel 2007

файл

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

Ответить

Дата: Вторник, 03.02.2015, 15:43 | Сообщение № 12

Группа: Друзья

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

Сообщений: 2941

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

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

Код

=СУММПРОИЗВ(СУММЕСЛИМН(Продажи;Товары;Товар_А;Магазины;A8))

Ответить

Дата: Вторник, 03.02.2015, 15:43 | Сообщение № 13

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

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

Сообщений: 101

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

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

Excel 2007

vikttur, Спасибо.

Такой вариант решения был реализован ранее, только с использованием функции

Код

=суммпроизв(суммеслимн(Продажи;Товары;Товар_А;Магазины;$A1))

но использование функции

Код

=суммесли() или =суммеслимн()

вносят некоторые ограничения

Ответить

Дата: Вторник, 03.02.2015, 15:44 | Сообщение № 14

Группа: Друзья

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

Сообщений: 2941

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

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

Вы не успели увидеть последнее сообщение. Какие ограничения?

Сообщение отредактировал vikttur - Вторник, 03.02.2015, 15:46

Ответить

Дата: Вторник, 03.02.2015, 15:52 | Сообщение № 15

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

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

Сообщений: 101

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

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

Excel 2007

vikttur,

Такие которые позволяет решать функция

Код

=суммпроизв()

Типа такого

Код

=сумпроизв(Продажи; 1*((Магазин=А1)+(Магазин=А2)))

То есть механизм функции

Код

=ИЛИ()

Ответить

Дата: Вторник, 03.02.2015, 15:55 | Сообщение № 16

Группа: Друзья

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

Сообщений: 2941

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

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

Не понял. Мысль понятна, но не понятно, почему предложенные варианты не подошли. Покажите в файле на конкретном примере, где применение ИЛИ.

Код

=сумпроизв(Продажи; 1*(СУММЕСЛИ(...)+СУММЕСЛИ(...))

Возможно, многого хотите от функций.
Если данных много, таблицы большие, лучше разгружать формулы с помощью доп. вычислений.

Сообщение отредактировал vikttur - Вторник, 03.02.2015, 16:00

Ответить

Дата: Вторник, 03.02.2015, 16:31 | Сообщение № 17

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

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

Сообщений: 101

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

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

Excel 2007

vikttur,
Возможно много требую.
Но наоборот стараюсь проводить меньше доп.вычислений для унификации отчетов.

Пример в файле.
Добавлено доп условие выбираемое из выпадающих списков по массиву Сети.

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

Ответить

Дата: Вторник, 03.02.2015, 16:53 | Сообщение № 18

Группа: Друзья

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

Сообщений: 2941

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

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

В последнем случае нужно применять две СУММЕСЛИМН, три (если больше условий)... Но это не решение.
Как и не решениие - пытаться втиснуть все в одну формулу.
Можно все в одной формуле считать, но об оптимизации речи и быть не может. А если еще условия добавятся? нужные условия будут обсчитываться в КАЖДОЙ формуле (С14 и ниже). А если таких магазинов много?

Цитата

Но наоборот стараюсь проводить меньше доп.вычислений для унификации отчетов

Заблуждение.
выводим в отдельные вычисления проверку условий - и задача решается легкой на подъем СУММЕСЛИМН.
В выборке продаж, столбец Е:

Код

=--(ЕСЛИ(СЧЁТЕСЛИ(Товар_А;A2);СЧЁТЕСЛИ(Расчет!$A$9:$A$10;D2)))

Уже в этой формуле часть вычислений отрезается с помощью функции ЕСЛИ.
В таблице с магазинами:

Код

=СУММЕСЛИМН(Продажи;Магазины;A14;'Выборка продаж'!E:E;1)

Выигрыш налицо: СУММПРОИЗВ мало того, что не отключает лишние вычисления. Плюс к этому (плюс к объему вычислений и минус к скорости) - в каждой формуле с этой функцией повторяются одинаковые вычисления и при увеличении условий увеличивается количество вычислений в каждой формуле. Условий при предложенном варианте (с нехорошими доп. вычислениями) можете добавлять хоть сотню.
Так что унификация должна быть оптимальной

В последнем случае нужно применять две СУММЕСЛИМН, три (если больше условий)... Но это не решение.
Как и не решениие - пытаться втиснуть все в одну формулу.
Можно все в одной формуле считать, но об оптимизации речи и быть не может. А если еще условия добавятся? нужные условия будут обсчитываться в КАЖДОЙ формуле (С14 и ниже). А если таких магазинов много?

Цитата

Но наоборот стараюсь проводить меньше доп.вычислений для унификации отчетов

Заблуждение.
выводим в отдельные вычисления проверку условий - и задача решается легкой на подъем СУММЕСЛИМН.
В выборке продаж, столбец Е:

Код

=--(ЕСЛИ(СЧЁТЕСЛИ(Товар_А;A2);СЧЁТЕСЛИ(Расчет!$A$9:$A$10;D2)))

Уже в этой формуле часть вычислений отрезается с помощью функции ЕСЛИ.
В таблице с магазинами:

Код

=СУММЕСЛИМН(Продажи;Магазины;A14;'Выборка продаж'!E:E;1)

Выигрыш налицо: СУММПРОИЗВ мало того, что не отключает лишние вычисления. Плюс к этому (плюс к объему вычислений и минус к скорости) - в каждой формуле с этой функцией повторяются одинаковые вычисления и при увеличении условий увеличивается количество вычислений в каждой формуле. Условий при предложенном варианте (с нехорошими доп. вычислениями) можете добавлять хоть сотню.
Так что унификация должна быть оптимальной

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

Сообщение отредактировал vikttur - Вторник, 03.02.2015, 17:01

Ответить

СообщениеВ последнем случае нужно применять две СУММЕСЛИМН, три (если больше условий)... Но это не решение.
Как и не решениие - пытаться втиснуть все в одну формулу.
Можно все в одной формуле считать, но об оптимизации речи и быть не может. А если еще условия добавятся? нужные условия будут обсчитываться в КАЖДОЙ формуле (С14 и ниже). А если таких магазинов много?

Цитата

Но наоборот стараюсь проводить меньше доп.вычислений для унификации отчетов

Заблуждение.
выводим в отдельные вычисления проверку условий - и задача решается легкой на подъем СУММЕСЛИМН.
В выборке продаж, столбец Е:

Код

=--(ЕСЛИ(СЧЁТЕСЛИ(Товар_А;A2);СЧЁТЕСЛИ(Расчет!$A$9:$A$10;D2)))

Уже в этой формуле часть вычислений отрезается с помощью функции ЕСЛИ.
В таблице с магазинами:

Код

=СУММЕСЛИМН(Продажи;Магазины;A14;'Выборка продаж'!E:E;1)

Выигрыш налицо: СУММПРОИЗВ мало того, что не отключает лишние вычисления. Плюс к этому (плюс к объему вычислений и минус к скорости) - в каждой формуле с этой функцией повторяются одинаковые вычисления и при увеличении условий увеличивается количество вычислений в каждой формуле. Условий при предложенном варианте (с нехорошими доп. вычислениями) можете добавлять хоть сотню.
Так что унификация должна быть оптимальной

Автор - vikttur
Дата добавления - 03.02.2015 в 16:53

Дата: Вторник, 03.02.2015, 18:41 | Сообщение № 19

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

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

Сообщений: 101

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

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

Excel 2007

vikttur, На счет скорости вычислений вы правы

Поэтому при расчетах в основном пользуюсь

Код

суммеслимн()

Ответить

Дата: Пятница, 12.05.2017, 10:18 | Сообщение № 20

Группа: Пользователи

Ранг: Участник

Сообщений: 93

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

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

Excel 2007

Здравствуйте.
Есть список городов и выручка помесячно.
По одному месяцу по списку городов с помощью СУММПРОИЗВ нужна выручка.
По месяцу функция работает, а по списку городов нет.
[moder]Нарушение п. 5q Правил форума
Тема закрыта[/moder]

К сообщению приложен файл: 9574693.xls (27.0 Kb)

Ответить

Функция Суммпроизв () - выборка по условию список-массив (Формулы/Formulas)

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

© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!