Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

= Мир MS Excel/Создание выборки максимальных сумм из массива по условиям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Создание выборки максимальных сумм из массива по условиям (Формулы/Formulas)
Создание выборки максимальных сумм из массива по условиям
zzbear Дата: Вторник, 07.02.2017, 12:34 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 101
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Существует массив данных. (лист Исходные)

Из данного массива необходимо отобрать максимальные суммы значений "Колличество" по нескольким условиям "Клиент" и "Территория" (лист Форма отбора).

Кроме того к каждому максимальному значению из образованного списка необходимо подтянуть значение кому принадлежит данная сумма "Клиент".
К сообщению приложен файл: ____.xlsx (15.0 Kb)
 
Ответить
СообщениеСуществует массив данных. (лист Исходные)

Из данного массива необходимо отобрать максимальные суммы значений "Колличество" по нескольким условиям "Клиент" и "Территория" (лист Форма отбора).

Кроме того к каждому максимальному значению из образованного списка необходимо подтянуть значение кому принадлежит данная сумма "Клиент".
Автор - zzbear
Дата добавления - 07.02.2017 в 12:34
китин Дата: Вторник, 07.02.2017, 12:49 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7034
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
формула массива
Код
=НАИБОЛЬШИЙ(ЕСЛИ(Исходные!$B$2:$B$32=$G$2;Исходные!$D$2:$D$32);СТРОКА(A1))
К сообщению приложен файл: 222333.xlsx (15.1 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Вторник, 07.02.2017, 12:50
 
Ответить
Сообщениеформула массива
Код
=НАИБОЛЬШИЙ(ЕСЛИ(Исходные!$B$2:$B$32=$G$2;Исходные!$D$2:$D$32);СТРОКА(A1))
Автор - китин
Дата добавления - 07.02.2017 в 12:49
zzbear Дата: Вторник, 07.02.2017, 13:14 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 101
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
китин, Нет это не то. Необходим список сумм "Колличеств" по каждому клиенту в порядке убывания.

В моем примере формула работает, но некорректно из-за условия "Список_уникальных_клиентов"
Код
=НАИБОЛЬШИЙ(СУММЕСЛИМН(Колличество;Территория;'Форма отбора'!$G$2;Клиент;Список_уникальных_клиентов);$B2)


Сообщение отредактировал zzbear - Вторник, 07.02.2017, 13:15
 
Ответить
Сообщениекитин, Нет это не то. Необходим список сумм "Колличеств" по каждому клиенту в порядке убывания.

В моем примере формула работает, но некорректно из-за условия "Список_уникальных_клиентов"
Код
=НАИБОЛЬШИЙ(СУММЕСЛИМН(Колличество;Территория;'Форма отбора'!$G$2;Клиент;Список_уникальных_клиентов);$B2)
Автор - zzbear
Дата добавления - 07.02.2017 в 13:14
Perfect2You Дата: Вторник, 07.02.2017, 13:18 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 237
Репутация: 59 ±
Замечаний: 0% ±

Excel 2010
zzbear, Попробуйте
Код
=НАИБОЛЬШИЙ(ИНДЕКС(($H$2=Территория)*ЕСЛИ(ЕТЕКСТ(Колличество);0;Колличество);0;1);$B2)

Тоже формула массива.

С клиентом есть вопрос: возможны ли совпадения значений количеств для разных клиентов на одной территории? Если нет, Ваша формула подходит. Если да, усложнять надо.


Сообщение отредактировал Perfect2You - Вторник, 07.02.2017, 13:24
 
Ответить
Сообщениеzzbear, Попробуйте
Код
=НАИБОЛЬШИЙ(ИНДЕКС(($H$2=Территория)*ЕСЛИ(ЕТЕКСТ(Колличество);0;Колличество);0;1);$B2)

Тоже формула массива.

С клиентом есть вопрос: возможны ли совпадения значений количеств для разных клиентов на одной территории? Если нет, Ваша формула подходит. Если да, усложнять надо.
Автор - Perfect2You
Дата добавления - 07.02.2017 в 13:18
sboy Дата: Вторник, 07.02.2017, 13:31 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
вариант не массивной
Код
=НАИБОЛЬШИЙ(ИНДЕКС((Исходные!$B$2:$B$32=$G$2)*Исходные!$D$2:$D$32;0);B2)
К сообщению приложен файл: 2946982.xlsx (14.9 Kb)

Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
вариант не массивной
Код
=НАИБОЛЬШИЙ(ИНДЕКС((Исходные!$B$2:$B$32=$G$2)*Исходные!$D$2:$D$32;0);B2)
Автор - sboy
Дата добавления - 07.02.2017 в 13:31
zzbear Дата: Вторник, 07.02.2017, 13:38 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 101
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Perfect2You, sboy, Спасибо, сейчас буду пробовать
 
Ответить
СообщениеPerfect2You, sboy, Спасибо, сейчас буду пробовать
Автор - zzbear
Дата добавления - 07.02.2017 в 13:38
zzbear Дата: Вторник, 07.02.2017, 13:51 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 101
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Perfect2You, Ваша не работает.

Совпадающие варианты значений количеств возможны.

То есть Клиент 1 купил за месяц 3+4+5= 12 ед товара
Клиент 2 купил за месяц 4+6+1+2= 13 ед товара.
И.т.д

В результате мы должны получить список

1. Клиент 2 = 13 ед.
2. Клиент 1 = 12 ед.
И.т.д
 
Ответить
СообщениеPerfect2You, Ваша не работает.

Совпадающие варианты значений количеств возможны.

То есть Клиент 1 купил за месяц 3+4+5= 12 ед товара
Клиент 2 купил за месяц 4+6+1+2= 13 ед товара.
И.т.д

В результате мы должны получить список

1. Клиент 2 = 13 ед.
2. Клиент 1 = 12 ед.
И.т.д
Автор - zzbear
Дата добавления - 07.02.2017 в 13:51
sboy Дата: Вторник, 07.02.2017, 13:59 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Тогда самый простой вариант сводной
К сообщению приложен файл: 6851299.xlsx (18.7 Kb)

Яндекс: 410016850021169
 
Ответить
СообщениеТогда самый простой вариант сводной
Автор - sboy
Дата добавления - 07.02.2017 в 13:59
_Boroda_ Дата: Вторник, 07.02.2017, 13:59 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16886
Репутация: 6599 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Переделал Ваши диапазоны со всего столбца на динамически изменяемые типа
Код
=Исходные!$C$2:ИНДЕКС(Исходные!$C:$C;СЧЁТЗ(Исходные!$B:$B))

Тогда формула массива для клиентов (одинаковые итоговые суммы распознает)
Код
=ИНДЕКС(Кл;ПОИСКПОЗ(НАИБОЛЬШИЙ(СУММЕСЛИМН(Кол;Тер;G$2;Кл;Кл)+ПОИСКПОЗ(Кл;Кл;)%%;1+СУММПРОИЗВ(СЧЁТЕСЛИМН(Тер;G$2;Кл;C$1:C1)));СУММЕСЛИМН(Кол;Тер;G$2;Кл;Кл)+ПОИСКПОЗ(Кл;Кл;)%%;))

И обычная формула для общего количества
Код
=СУММЕСЛИМН(Кол;Тер;G$2;Кл;C2)

А вообще все проще сводной таблицей можно сделать. Сортировка и фильтр 10 наибольших. Сделал ее на первом листе

Я ведь правильно понял, что Вам нужно ранжирование по суммарному количеству по клиенту на указанной территории?
К сообщению приложен файл: -2-1.xlsx (19.4 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПеределал Ваши диапазоны со всего столбца на динамически изменяемые типа
Код
=Исходные!$C$2:ИНДЕКС(Исходные!$C:$C;СЧЁТЗ(Исходные!$B:$B))

Тогда формула массива для клиентов (одинаковые итоговые суммы распознает)
Код
=ИНДЕКС(Кл;ПОИСКПОЗ(НАИБОЛЬШИЙ(СУММЕСЛИМН(Кол;Тер;G$2;Кл;Кл)+ПОИСКПОЗ(Кл;Кл;)%%;1+СУММПРОИЗВ(СЧЁТЕСЛИМН(Тер;G$2;Кл;C$1:C1)));СУММЕСЛИМН(Кол;Тер;G$2;Кл;Кл)+ПОИСКПОЗ(Кл;Кл;)%%;))

И обычная формула для общего количества
Код
=СУММЕСЛИМН(Кол;Тер;G$2;Кл;C2)

А вообще все проще сводной таблицей можно сделать. Сортировка и фильтр 10 наибольших. Сделал ее на первом листе

Я ведь правильно понял, что Вам нужно ранжирование по суммарному количеству по клиенту на указанной территории?
Автор - _Boroda_
Дата добавления - 07.02.2017 в 13:59
Perfect2You Дата: Вторник, 07.02.2017, 14:08 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 237
Репутация: 59 ±
Замечаний: 0% ±

Excel 2010
zzbear,
Странно, прикладываю файл - вроде работает.
Удалось победить и построить формулу (даже не массива) для клиентов. Она не боится повторений количеств. Единственное, в случае повторений, клиенты перечисляются "снизу вверх". Если это не должно быть так, незначительным усложнением можно сделать наоборот.

А с динамически изменяемыми диапазонами формула для количеств перестает быть массивной:
Код
=НАИБОЛЬШИЙ(ИНДЕКС(($H$2=Территория)*Колличество;0;1);$B2)
К сообщению приложен файл: 2528327.xlsx (15.3 Kb)


Сообщение отредактировал Perfect2You - Вторник, 07.02.2017, 14:13
 
Ответить
Сообщениеzzbear,
Странно, прикладываю файл - вроде работает.
Удалось победить и построить формулу (даже не массива) для клиентов. Она не боится повторений количеств. Единственное, в случае повторений, клиенты перечисляются "снизу вверх". Если это не должно быть так, незначительным усложнением можно сделать наоборот.

А с динамически изменяемыми диапазонами формула для количеств перестает быть массивной:
Код
=НАИБОЛЬШИЙ(ИНДЕКС(($H$2=Территория)*Колличество;0;1);$B2)
Автор - Perfect2You
Дата добавления - 07.02.2017 в 14:08
zzbear Дата: Вторник, 07.02.2017, 14:18 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 101
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, sboy, Спасибо.

вариант со сводной не подходит, т.к. необходимо в последующем проводить дополнительные вычисления (сопоставление с планами, прогнозами
 
Ответить
Сообщение_Boroda_, sboy, Спасибо.

вариант со сводной не подходит, т.к. необходимо в последующем проводить дополнительные вычисления (сопоставление с планами, прогнозами
Автор - zzbear
Дата добавления - 07.02.2017 в 14:18
zzbear Дата: Вторник, 07.02.2017, 14:55 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 101
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, Спасибо большое, кажется то что надо. Буду разбирать.
 
Ответить
Сообщение_Boroda_, Спасибо большое, кажется то что надо. Буду разбирать.
Автор - zzbear
Дата добавления - 07.02.2017 в 14:55
zzbear Дата: Вторник, 07.02.2017, 15:01 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 101
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_,

Нескромный вопрос как работает конструкция

Код
=ПОИСКПОЗ(Кл;Кл;)%%
 
Ответить
Сообщение_Boroda_,

Нескромный вопрос как работает конструкция

Код
=ПОИСКПОЗ(Кл;Кл;)%%
Автор - zzbear
Дата добавления - 07.02.2017 в 15:01
_Boroda_ Дата: Вторник, 07.02.2017, 15:53 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 16886
Репутация: 6599 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
ПОИСКПОЗ массива по самому себе даст номера строк первых вхождений каждого элемента массива.
Если массив 1-3-3-2-1, то ПОИСКПОЗ(масс;масс;) даст 1-2-2-4-1
%% у конце - умножаем на 1% и на 1% - аналогично делению на 10000. Нам нужно очень маленькое число добавлять, но свое для каждого клиента

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПОИСКПОЗ массива по самому себе даст номера строк первых вхождений каждого элемента массива.
Если массив 1-3-3-2-1, то ПОИСКПОЗ(масс;масс;) даст 1-2-2-4-1
%% у конце - умножаем на 1% и на 1% - аналогично делению на 10000. Нам нужно очень маленькое число добавлять, но свое для каждого клиента
Автор - _Boroda_
Дата добавления - 07.02.2017 в 15:53
zzbear Дата: Вторник, 07.02.2017, 21:44 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 101
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, Спасибо еще раз.
 
Ответить
Сообщение_Boroda_, Спасибо еще раз.
Автор - zzbear
Дата добавления - 07.02.2017 в 21:44
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Создание выборки максимальных сумм из массива по условиям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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