Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Построение сводной таблицы по несвязанным диапазонам, Excel 2013
 
#1
27.06.2014 12:21:18
Имеется большая расчетная таблица (см. приложенный файл), в которой на основе исходных данных производится вычисление рейтинга выполнения набора показателей по структурным подразделениям предприятия. Для каждого структурного подразделения рейтинг вычисляется по каждому показателю (поле "Нормир. значение ИИ), а потом на их основе производится расчет сводного рейтинга подразделения. Значение рейтинга от 0 до 1. Для отдельного подразделения может не рассчитываться рейтинг по некоторым показателям, в этом случае значение поля "Нормир. значение ИИ" этих показателей пустое.
Нужно построить краткую итоговую таблицу на основе исходной, в которую были бы включены значения полей "Нормир. значение ИИ" каждого показателя и значение поля "Сводный рейтинг"для каждого подразделения, при этом эта таблица должна быть всегда отсортирована по убыванию сводного рейтинга, а пустые значения рейтингов отдельных показателей не отражались (не обнулялись).
У меня частично получилось решить проблему путем копирования нужных полей в промежуточную таблицу (один непрерывный диапазон), а потом создания на ее основе сводной таблицы, которую можно было отсортировать. Однако, в этой сводной таблице пустые ячейки рейтингов отдельных показателей всегда обнулялись, что визуально приравнивало их к рейтингам со значением 0 и затрудняло анализ. В параметрах сводной таблицы поле "Для пустых ячеек отражать" очищено.
Может быть есть более элегантный способ полного решения моей проблемы?
 
 
 
#2
27.06.2014 12:55:41
Вы-бы для примера и наглядности хоть какие-нибудь данные накидали....
Да и вот это
Цитата
У меня частично получилось
не лишним будет для помогающих
Изменено: Sanja - 27.06.2014 12:56:35
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
27.06.2014 13:22:54
Выкладываю мой незаконченный вариант решения.
 
 
 
#4
28.06.2014 17:14:44
Доброе время суток
Дело в том, что в источнике для сводной нет пустых ячеек. Есть ячейки формулы в которых возвращают пустую строку, поэтому Excel их интерпретирует как нулевое значение (а не пустое - можете сами убедится удалив формулу в ячейке столбца p1 и обновив сводную - будет пустое значение в ней).
Можно пойти путём макросов - создания подключения к диапазону данных, а уже по нему делать сводную.
Недостаток. Подключение жёстко привязано к пути, так что придётся при переносе файла редактировать свойство подключения, либо пересоздавать и указывать его в качестве источника данных сводной.
Успехов.
P. S. Если исходные данные расположены на разных листах, то их подготовить для сводной таблицы можно таким приёмом.
Изменено: anvg - 28.06.2014 17:37:11
 
 
 
#5
28.06.2014 20:24:07
Спасибо за вариант! Но мне кажется, что наверное будет проще и надежнее скопировать с помощью макроса нужные данные в промежуточную таблицу, которая является источником данных в моем варианте. Надо только для удобства найти команду VBA для обновления сводной таблицы  :?:
А мне казалось, что можно обойтись без макросов...
 
 
 
#6
29.06.2014 08:21:17
kliffgomel, вы не находите, что в вашем последнем посте есть противоречия?
С одной стороны
Цитата
А мне казалось, что можно обойтись без макросов...
С другой
Цитата
надежнее скопировать с помощью макроса нужные данные в промежуточную таблицу
Если вы скопируете в эту промежуточную таблицу только значения, то сводная будет показывать пустые значения, если же ячейка будет содержать формулу, возвращающую пустое значение, то это пустое значение будет интерпретироваться как 0. Моё предложение и состояло в том, как этого избежать.
Можно ведь обойтись и без макросов. Подключение уже есть. Экспортируйте его. Файл подключения - текстовый формата xml, в нём необходимо на будущее поменять путь к файлу в строке подключения (параметр <odc:ConnectionString>) и определение SQL запроса к данным (параметр <odc:CommandText>). Далее изменённый файл подключения добавляете и строите по нему сводную.
А в общем, у вас в заголовке первого сообщения один вопрос, в теле другой. Представленная сводная в файле составлена по таблице, которая сама выглядит как сводная. Вы бы всё же определись бы с вопросом и примером исходных данных, а то это будет долгая песня в попытке понять, что же вы хотите на самом деле.
 
 
 
#7
29.06.2014 10:17:40
Цитата
kliffgomel пишет: Надо только для удобства найти команду VBA для обновления сводной таблицы
если бы дело было только в команде - то вроде имелись на форуме идеи Refresh, и надстройка ZVI_PivotRefresh  - если помогут для 2013 - не знаю... но дело в том, что у меня НЕ 2013-й - поэтому более подсказать ничем не смогу по вашему вопросу ...  :(   просто у него (13-го) какой-то вроде загадочный способ брать кэш для сводной (не как в 2007)... имхо
Изменено: JeyCi - 29.06.2014 10:17:51
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#8
29.06.2014 12:09:39
anvg,в моем репосте я лишь имел ввиду, что если использовать макросы, то проще (для меня) заполнить промежуточную таблицу исходными данными с учетом вашего замечания по пустым ячейкам. Промежуточная таблица мне была нужна потому, что я не мог построить стандартными средствами сводную по несвязанным диапазонам исходной таблицы, что и указано в качестве моего основного вопроса (для краткости).
Ваш вариант рабочий, но для моего уровня он слишком сложный.
В остальном, вопрос мой, как мне кажется, сформулирован предельно ясно, исходные данные имеются.
JeyCi, спасибо за совет, идея refresh подошла.
 
 
 
#9
29.06.2014 13:16:45
Цитата
что я не мог построить стандартными средствами сводную по несвязанным диапазонам исходной таблицы
Думаю, вам поможет Редизайнер таблиц, чтобы свернуть данные в плоскую таблицу. Успехов.
 
 
 
Страницы: 1
Наверх