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

Вход

Регистрация

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

 

= Мир MS Excel/Создание свода значений сумм по нескольким параметрам - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Создание свода значений сумм по нескольким параметрам (Формулы/Formulas)
Создание свода значений сумм по нескольким параметрам
Nidus Дата: Четверг, 09.06.2016, 12:18 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Есть выборка продаж. Разные товарные группы, гендеры. Некоторые модели встречаются несколько раз в таблице с разными занчениями продаж. Задача - составить в отдельной таблицу список лучших по продажам моделей.
Два параметра - "Брюки" и "Жен". В столбце продажи должна быть сумма продаж по модели из разных строк (при повторе модели в списке). Изначальная таблица на 150000 строк, моделей порядка 15000, повторяются часто с разынми и одинаковыми значениями продаж. Значения продаж могут отличасться у разных моделей.
Сводная таблица - не вариант, к сожалению - с итоговой таблицей также далее надо будет работать, как с базой данных.
Прошу прощения за сумбурное обьяснение - мозг уже взорвался - не понимаю как сделать.
К сообщению приложен файл: 2445798.xls(28Kb)
 
Ответить
СообщениеЕсть выборка продаж. Разные товарные группы, гендеры. Некоторые модели встречаются несколько раз в таблице с разными занчениями продаж. Задача - составить в отдельной таблицу список лучших по продажам моделей.
Два параметра - "Брюки" и "Жен". В столбце продажи должна быть сумма продаж по модели из разных строк (при повторе модели в списке). Изначальная таблица на 150000 строк, моделей порядка 15000, повторяются часто с разынми и одинаковыми значениями продаж. Значения продаж могут отличасться у разных моделей.
Сводная таблица - не вариант, к сожалению - с итоговой таблицей также далее надо будет работать, как с базой данных.
Прошу прощения за сумбурное обьяснение - мозг уже взорвался - не понимаю как сделать.

Автор - Nidus
Дата добавления - 09.06.2016 в 12:18
Manyasha Дата: Четверг, 09.06.2016, 12:48 | Сообщение № 2
Группа: Модераторы
Ранг: Старожил
Сообщений: 1721
Репутация: 722 ±
Замечаний: 0% ±

Excel 2007, 2010
Nidus, посмотрите вариант с доп. столбцом:
формула для модели (массивная, вводить через ctrl+shift+enter)
Код
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2:$E$16<>"";СТРОКА($E$2:$E$16)-1;"");СТРОКА(E1)))

для продаж:
Код
=СУММЕСЛИ($B$2:$B$16;I4;$D$2:$D$16)
К сообщению приложен файл: 2445798-1.xls(36Kb)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеNidus, посмотрите вариант с доп. столбцом:
формула для модели (массивная, вводить через ctrl+shift+enter)
Код
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2:$E$16<>"";СТРОКА($E$2:$E$16)-1;"");СТРОКА(E1)))

для продаж:
Код
=СУММЕСЛИ($B$2:$B$16;I4;$D$2:$D$16)

Автор - Manyasha
Дата добавления - 09.06.2016 в 12:48
Nidus Дата: Четверг, 09.06.2016, 13:37 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Manyasha, спасибо, пробую!
Вариант с доп столбцом - блин получается жутко громоздкий - в исходной базе 50 товарных групп, 5 блоков гендеров, т.е. придется рисовать 250 доп столбцов и от них выплясывать таблицы ТОП под каждую группу условий. Но это не беда, нарисуем.

С индексом получается не совсем то. Индекс заводит в таблицу модели в последовательности в которой они идут в исходном массиве данных и находятся в доп столбце, а не в последовательности убывания суммы продаж (от топа к аутсайдеру). Для примера поменял последовательность в исходной таблице - свод получается 1200-400-900. (файл вложил).
Наверное можно привязать суммесли к допстолбцу, и в сводной таблицу проиндексировать суммы. Но как к ним опять притянуть соответствующие модели?
%) %) %)
К сообщению приложен файл: _2445798-2.xls(34Kb)
 
Ответить
СообщениеManyasha, спасибо, пробую!
Вариант с доп столбцом - блин получается жутко громоздкий - в исходной базе 50 товарных групп, 5 блоков гендеров, т.е. придется рисовать 250 доп столбцов и от них выплясывать таблицы ТОП под каждую группу условий. Но это не беда, нарисуем.

С индексом получается не совсем то. Индекс заводит в таблицу модели в последовательности в которой они идут в исходном массиве данных и находятся в доп столбце, а не в последовательности убывания суммы продаж (от топа к аутсайдеру). Для примера поменял последовательность в исходной таблице - свод получается 1200-400-900. (файл вложил).
Наверное можно привязать суммесли к допстолбцу, и в сводной таблицу проиндексировать суммы. Но как к ним опять притянуть соответствующие модели?
%) %) %)

Автор - Nidus
Дата добавления - 09.06.2016 в 13:37
Nidus Дата: Четверг, 09.06.2016, 13:50 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Manyasha, в целом через ранг, индекс и поискпоз получилось в 3ий итоговый свод вывести все как надо. Файл вложил.
Но все равно получается как-то очень по проктологически, через 100500 промежуточных таблиц.
М.б. есть идеи как это оптимизировать?
К сообщению приложен файл: _2445798-3.xls(33Kb)
 
Ответить
СообщениеManyasha, в целом через ранг, индекс и поискпоз получилось в 3ий итоговый свод вывести все как надо. Файл вложил.
Но все равно получается как-то очень по проктологически, через 100500 промежуточных таблиц.
М.б. есть идеи как это оптимизировать?

Автор - Nidus
Дата добавления - 09.06.2016 в 13:50
Nidus Дата: Четверг, 09.06.2016, 15:50 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Вопрос оптимизации стоит очень остро, т.к. после ввода формул в основной файл - только доп столбец для первого критерия в 50000 строк обсчитывается минут 10. По данной методе полный пересчет для всех критериев боюсь займет несколько часов.
 
Ответить
СообщениеВопрос оптимизации стоит очень остро, т.к. после ввода формул в основной файл - только доп столбец для первого критерия в 50000 строк обсчитывается минут 10. По данной методе полный пересчет для всех критериев боюсь займет несколько часов.

Автор - Nidus
Дата добавления - 09.06.2016 в 15:50
Manyasha Дата: Четверг, 09.06.2016, 16:12 | Сообщение № 6
Группа: Модераторы
Ранг: Старожил
Сообщений: 1721
Репутация: 722 ±
Замечаний: 0% ±

Excel 2007, 2010
Nidus, самое оптимально - использовать сводную, но Вы не хотите.
Обновлять и сортировать ее можно макросом, при изменении листа.
с итоговой таблицей также далее надо будет работать

почему Вы думаете, что со сводной таблицей нельзя дальше работать? Что Вы хотите дальше сделать с полученными данными?
К сообщению приложен файл: 2445798-1-1.xls(37Kb)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеNidus, самое оптимально - использовать сводную, но Вы не хотите.
Обновлять и сортировать ее можно макросом, при изменении листа.
с итоговой таблицей также далее надо будет работать

почему Вы думаете, что со сводной таблицей нельзя дальше работать? Что Вы хотите дальше сделать с полученными данными?

Автор - Manyasha
Дата добавления - 09.06.2016 в 16:12
Матраскин Дата: Четверг, 09.06.2016, 16:32 | Сообщение № 7
Группа: Друзья
Ранг: Обитатель
Сообщений: 351
Репутация: 74 ±
Замечаний: 0% ±

20xx
Nidus, Самое оптимальное - использовать базу данных, как наипростейщий пример - Access. Там такого рода вычисления выполняются в течение пары секунд


в интернете опять кто-то не прав

Сообщение отредактировал Матраскин - Четверг, 09.06.2016, 16:32
 
Ответить
СообщениеNidus, Самое оптимальное - использовать базу данных, как наипростейщий пример - Access. Там такого рода вычисления выполняются в течение пары секунд

Автор - Матраскин
Дата добавления - 09.06.2016 в 16:32
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Создание свода значений сумм по нескольким параметрам (Формулы/Formulas)
Страница 1 из 11
Поиск:

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