Поиск доли - Мир MS Excel

Дата: Среда, 22.01.2020, 11:26 | Сообщение № 1

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

Ранг: Прохожий

Сообщений: 4

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

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

Excel 2013

Всем привет!

Эксперты, помогите пожалуйста) Надо по каждому магазину найти товары с наибольшим отставанием по доле продаж от доли продаж этого товара по всем магазинам.

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

Дата: Среда, 22.01.2020, 12:13 | Сообщение № 2

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

Ранг: Ветеран

Сообщений: 589

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

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

Excel 2010

Andreius, Немного замудренно. С кучей допстолбцов

Код

=ИНДЕКС($A$1:$A$15;ПОИСКПОЗ(НАИБОЛЬШИЙ(($B$1:$B$15=$J2)*$G$1:$G$15;1);$G$1:$G$15;0);1)

Формула массива вводится одновременным нажатием Ctrl+Shift+Enter

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

Сообщение отредактировал YouGreed - Среда, 22.01.2020, 12:14

Дата: Среда, 22.01.2020, 12:32 | Сообщение № 3

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

Ранг: Прохожий

Сообщений: 4

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

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

Excel 2013

Спасибо) а без допстолбцов не получается?)

Дата: Среда, 22.01.2020, 12:52 | Сообщение № 4

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

Ранг: Ветеран

Сообщений: 589

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

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

Excel 2010

Andreius, Пока так... Один доп столбец

Код

=ИНДЕКС($A$1:$A$15;ПОИСКПОЗ(НАИБОЛЬШИЙ(($B$1:$B$15=$J2)*($E$1:$E$15-((($B$1:$B$15=$J2)*$C$1:$C$15)/СУММ(($B$1:$B$15=$J2)*$C$1:$C$15)));1);($E$1:$E$15-((($B$1:$B$15=$J2)*$C$1:$C$15)/СУММ(($B$1:$B$15=$J2)*$C$1:$C$15)));0);1)

Формула также массивна

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

китин

Дата: Среда, 22.01.2020, 14:00 | Сообщение № 5

Группа: Модераторы

Ранг: Экселист

Сообщений: 7015

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

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

Excel 2007;2010;2016

Цитата Andreius, 22.01.2020 в 12:32, в сообщении № 3 ()

а без допстолбцов не получается?)

дык и без них мона

Код

=ИНДЕКС($A$1:$A$15;ПОИСКПОЗ(МИН(ЕСЛИ((ЕСЛИ($B$1:$B$15=$J2;$C$1:$C$15)/СУММЕСЛИ($B$1:$B$15;$J2;$C$1:$C$15))<>0;(ЕСЛИ($B$1:$B$15=$J2;$C$1:$C$15)/СУММЕСЛИ($B$1:$B$15;$J2;$C$1:$C$15))));(ЕСЛИ($B$1:$B$15=$J2;$C$1:$C$15)/СУММЕСЛИ($B$1:$B$15;$J2;$C$1:$C$15));0))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

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

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

Дата: Среда, 22.01.2020, 14:12 | Сообщение № 6

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

Ранг: Прохожий

Сообщений: 4

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

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

Excel 2013

YouGreed, китин,
Пробовал так, но, если вручную посчитать неправильный результат получается.
У магазин 1 наибольшая доля отставания у моркови.

Дата: Среда, 22.01.2020, 14:17 | Сообщение № 7

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

Ранг: Ветеран

Сообщений: 589

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

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

Excel 2010

Andreius, У меня разве не правильная? С одним доп столбцом)

Цитата

дык и без них мона

Я думаю, время использовать Power Query.
Логика у меня верная, но как запихнуть в одну формулу, я не могу додумать.

Сообщение отредактировал YouGreed - Среда, 22.01.2020, 14:22

Дата: Среда, 22.01.2020, 14:19 | Сообщение № 8

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

Ранг: Прохожий

Сообщений: 4

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

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

Excel 2013

YouGreed,
Если с одним, то все окей)

китин

Дата: Среда, 22.01.2020, 14:20 | Сообщение № 9

Группа: Модераторы

Ранг: Экселист

Сообщений: 7015

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

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

Excel 2007;2010;2016

Цитата Andreius, 22.01.2020 в 14:12, в сообщении № 6 ()

У магазин 1 наибольшая доля отставания у моркови.

да ладно...... проверьте исчё!!!!

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

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

Светлый

Дата: Среда, 22.01.2020, 14:22 | Сообщение № 10

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

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Мой вариант массивной формулы:

Код

=ИНДЕКС(A:A;ОСТАТ(МИН(ЕСЛИ(B$1:B$20=J2;ОКРУГЛ(СУММЕСЛИМН(C$1:C$20;A$1:A$20;A$1:A$20;B$1:B$20;J2)/СУММЕСЛИ(A$1:A$20;A$1:A$20;C$1:C$20);6)+СТРОКА($1:$20)%%%%))/1%%%%;100))

*Что-то я замудрил:

Код

=ИНДЕКС(A:A;ОСТАТ(МИН(ЕСЛИ(B$1:B$20=J2;ОКРУГЛ(C$1:C$20/СУММЕСЛИ(A$1:A$20;A$1:A$20;C$1:C$20);6)+СТРОКА($1:$20)%%%%))/1%%%%;100))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 22.01.2020, 14:36

Дата: Среда, 22.01.2020, 14:44 | Сообщение № 11

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

Ранг: Ветеран

Сообщений: 589

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

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

Excel 2010

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

Смотрите вложение.

Если обновить вложение, то все данные поплывут...

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

Сообщение отредактировал YouGreed - Среда, 22.01.2020, 14:51

Nic70y

Дата: Среда, 22.01.2020, 14:58 | Сообщение № 12

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

Ранг: Экселист

Сообщений: 8759

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

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

Excel 2010

потомушто все неправильно

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

ЮMoney 41001841029809

Дата: Среда, 22.01.2020, 15:39 | Сообщение № 13

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

Ранг: Ветеран

Сообщений: 589

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

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

Excel 2010

Nic70y, Я домучал...

Код

=ИНДЕКС($A$1:$A$15;ПОИСКПОЗ(НАИБОЛЬШИЙ(($B$1:$B$15=$J2)*((СУММЕСЛИ($A$1:$A$15;$A$1:$A$15;$C$1:$C$15)/СУММ($C$1:$C$15))-((($B$1:$B$15=$J2)*$C$1:$C$15)/СУММ(($B$1:$B$15=$J2)*$C$1:$C$15)));1);((СУММЕСЛИ($A$1:$A$15;$A$1:$A$15;$C$1:$C$15)/СУММ($C$1:$C$15))-((($B$1:$B$15=$J2)*$C$1:$C$15)/СУММ(($B$1:$B$15=$J2)*$C$1:$C$15)));0);1)

Напоминаю. Формула массива. Вводится нажатием Ctrl+Shift+Enter одновременно.
Правочка

Код

=ИНДЕКС($A$1:$A$15;ПОИСКПОЗ(МАКС(($B$1:$B$15=$J2)*(СУММЕСЛИ($A$1:$A$15;$A$1:$A$15;$C$1:$C$15)/СУММ($C$1:$C$15))-((($B$1:$B$15=$J2)*$C$1:$C$15)/СУММ(($B$1:$B$15=$J2)*$C$1:$C$15)));(СУММЕСЛИ($A$1:$A$15;$A$1:$A$15;$C$1:$C$15)/СУММ($C$1:$C$15))-((($B$1:$B$15=$J2)*$C$1:$C$15)/СУММ(($B$1:$B$15=$J2)*$C$1:$C$15));0);1)

Убрал лишние скобки, заменил наибольший на Макс

Отдельная благодарность Светлому, за подсказку формирования массива долей по наименованиям без привязки к Магазинам.
[offtop]В плане эргономики, не самый лучший вариант, но решение работает. Все действия последовательны, логичны и обоснованы

Видимо, вкусный обед, действует благотворно.

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

Сообщение отредактировал YouGreed - Среда, 22.01.2020, 15:58

Светлый

Дата: Среда, 22.01.2020, 20:43 | Сообщение № 14

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

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Цитата YouGreed, 22.01.2020 в 15:39, в сообщении № 13 ()

В плане эргономики, не самый лучший вариант, но решение работает.

Оно же, но немного короче:

Код

=ИНДЕКС(A:A;ОСТАТ(МАКС(ЕСЛИ($B$1:$B$15=$J2;ОКРУГЛ(СУММЕСЛИ($A$1:$A$15;$A$1:$A$15;$C$1:$C$15)/СУММ($C$1:$C$15)-$C$1:$C$15/СУММ(($B$1:$B$15=$J2)*$C$1:$C$15);6)+СТРОКА($1:$15)%%%%))/1%%%%;100))

*Или так:

Код

=ИНДЕКС(A:A;ОСТАТ(МАКС((B$1:B$25=J2)*(ОКРУГЛ(СУММЕСЛИ(A$1:A$25;A$1:A$25;C$1:C$25)/СУММ(C$1:C$25)-C$1:C$25/СУММ((B$1:B$25=J2)*C$1:C$25);6)+СТРОКА($1:$25)%%%%))/1%%%%;100))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 22.01.2020, 20:52