Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Динамика в сводной таблице
 
#1
10.03.2016 16:00:55
Добрый день!

Помогите, пожалуйста, решить такую задачу. Есть таблица с заявками, в которой содержится следующая информация: Номер заявки, Дата, Неделя, Месяц, Город и компания, исполнившая заявку. На основе данной таблицы строится сводная таблица, которая выводит количество заявок по городу и неделям. В этой сводной таблице требуется отследить динамику обращений по городам, так что бы после последней недели выводилась динамика за весь период, желательно с момента первой заявки. То есть, выводить сообщения, типа: Изменений нет, Количество заявок растет/уменьшается.
 
 
 
#2
10.03.2016 22:07:07
Доброе время суток
По существу вам нужно анализировать коэффициент линии тренда, если положительный - то рост, отрицательный - падение, около нуля - без изменений. Такое, теоретически можно сделать, используя Power Pivot (обычными сводными вряд ли). Но, нужно учитывать один момент, что сводные как результат отображают только числа - не текст. Так что даже если делать в Power Pivot, то придётся использовать числа и коэффициентов и условное форматирование для демонстрации требуемого.
Либо писать макрос, который будет составлять подобную сводную как результат вычислений.
Для анализа можете построить точечную сводную диаграмму, а по данным отображать линии тренда. Правда, так и не нашёл как добавить линии тренда сразу для всех серий данных.
Изменено: Андрей VG - 10.03.2016 22:12:23
 
 
 
#3
11.03.2016 15:43:12
Андрей VG, Можно использовать и условное форматирование, как это сделать? Я правильно понимаю, что в Excel 2013 Power Pivot установлен по умолчанию?
 
 
 
#4
11.03.2016 19:35:11
Доброе время суток
Пришлось с условным форматированием то и повозиться. Пришлось использовать помимо Power Pivot ещё и Power Query для создания календаря. А так вроде что-то получилось. Пример выкладываю здесь cor_coef.rar.  Увы, использование Power Pivot приводит к таким размерам файла, что он уже не удовлетворяет условиям размещения файлов по размеру.
Успехов.
 
 
 
#5
14.03.2016 10:26:53
Андрей VG, Спасибо большое, меня данный вариант устраивает. Но при обновлении данных, вылетает ошибка: "Нам не удалось обновить подключение "Запрос-Календарь", Получено сообщение об ошибке: Поставщик "Microsoft.Mashup.OleDb.1" не зарегистрирован на локальном компьютере".
 
 
 
#6
14.03.2016 18:39:18
Доброе время суток
Извините, упустил момент, что у вас 2013. Вам нужно установить надстройку Power Query - она рассчитывает календарь и сквозную нумерацию недель сквозь годы. Переделал и расчёт мер, чтобы работало в 2013. Выложил Расчёт_коэффициента_регрессии.xlsx.
Единственное замечание, так как условное форматирование приходится применять ко всей мере, то пришлось "хитрить". Так что на реальных данных нужно будет подбирать значение множителя в мере Result - сейчас 0,5, иначе значение коэффициента регрессии может выйти из диапазона +/-1.
Успехов.
 
 
 
#7
15.03.2016 17:43:56
Андрей VG, сообщение так же выдается, но данные пересчитывает. Спасибо!!!
 
 
 
#8
15.03.2016 18:40:31
Цитата
SBardashov написал: сообщение так же выдается
Так вы надстройку Power Query для Excel 2013 установили? А то закончится 2016 и дальше считаться то не будет :)
 
 
 
#9
18.03.2016 23:11:03
Допилил таки вариант со значками в сводной. Может кому будет интересно Расчёт_коэффициента_регрессии.xlsx. Да, и не могу понять - где то ошибка в вычислении коэффициента линейной регрессии, так что внимательнее.
 
 
 
#10
19.03.2016 20:55:14
Нашёл таки ошибку в вычислении коэффициента линейной регрессии, не правильную таблицу указал для подсчёта указал. Правильно будет использовать в мерах такую таблицу группировки
Код
SUMMARIZE('Заявки','Календарь'[xField],"yField",COUNTROWS('Заявки'))
 
 
 
Страницы: 1
Наверх