Недавно мой друг интересовался кнопками Drill-Down и Drill-Up на вкладке Инструменты сводной таблицы на ленте. Почему они постоянно неактивны? Они занимают много места на ленте. Как можно их использовать?
После некоторого исследования есть способ их использовать, но вы должны использовать модель данных и использовать представление диаграммы Power Pivot для создания иерархии. Если у вас нет вкладки Power Pivot на ленте, вам нужно будет найти сотрудника, у которого есть кнопка, чтобы создать иерархию. (Или, если вы просто хотите попробовать эту функцию, загрузите созданный мной файл Excel: Hierarchy.xlsx)
Первый шаг - преобразуйте исходный набор данных сводной таблицы в таблицу, используя либо Home - Format as Table, либо Ctrl + T. Убедитесь, что выбран параметр My Table Has Headers.
Используйте Insert - Pivot Table. В диалоговом окне «Создание сводной таблицы» установите флажок «Добавить эти данные в модель данных».
Вот поля сводной таблицы перед созданием иерархии.
Щелкните значок «Управление» на вкладке Power Pivot на ленте. (Многие экземпляры Excel 2013 и 2016 не имеют этой вкладки. Она не отображается на Mac.)
В окне Power Pivot для Excel щелкните значок представления схемы. Он находится в правой части вкладки "Главная".
Используйте ручку изменения размера в правом нижнем углу Table1, чтобы увеличить Table1, чтобы вы могли видеть все свои поля. Щелкните первый элемент в вашей иерархии (в моем примере - континент). Удерживая нажатой клавишу «Shift» щелкните последний элемент в иерархии (в моем примере - «Город»). Вы также можете щелкнуть один элемент и, удерживая Ctrl, щелкнуть по другим, если поля иерархии не смежны. После того, как вы выбрали поля, щелкните правой кнопкой мыши любое поле и выберите «Создать иерархию».
Hierarchy1 создана и ждет, когда вы введете новое имя. Назову свою иерархию Географией. Если щелкнуть вне Power Pivot, Hierarchy1 больше не находится в режиме переименования. Щелкните правой кнопкой мыши Hierachy1 и выберите «Переименовать».
Закройте Power Pivot и вернитесь в Excel. В полях сводной таблицы теперь отображается иерархия географии и дополнительные поля. Ваше поле продаж скрыто под дополнительными полями. Я немного понимаю, почему они скрывают континент, страну, регион, территорию, город под дополнительными полями. Но я не понимаю, почему они скрывают продажи в дополнительных полях.
Чтобы построить сводную таблицу, установите флажок для иерархии «География». Откройте дополнительные поля, щелкнув треугольник рядом с ним. Выберите Продажи.
На изображении выше есть на что обратить внимание. При первоначальном создании сводной таблицы активная ячейка находится на A3, а значок «Развернуть» отображается серым цветом. Однако, если вы переместите указатель ячейки на Северную Америку в A4, вы увидите, что Drill Down включен.
Наведя указатель ячейки на Северную Америку, щелкните Развернуть, и континент заменяется на страну.
Наведя указатель ячейки на Канаду, нажмите «Развернуть», и вы увидите Восточную Канаду и Западную Канаду. Обратите внимание, что на этом этапе активированы кнопки Drill Down и Drill Up.
Я щелкнул Drill Up, чтобы вернуться в страну. Выберите США. Трижды пройдите вниз, и я окажусь в городах региона Каролина. На этом этапе кнопка Drill Down неактивна.
Обратите внимание, что на уровне «Континент» можно щелкнуть «Развернуть поле», чтобы отобразить континенты и страны. Затем в первой стране выберите «Развернуть поле», чтобы отобразить регионы. В первом регионе используйте «Развернуть поле», чтобы отобразить территории. На первой территории нажмите «Развернуть поле», чтобы открыть город.
На всех приведенных выше снимках экрана показана сводная таблица в моем представлении по умолчанию «Показать в табличной форме». Если ваши сводные таблицы созданы в компактной форме, вы увидите представление ниже. (Чтобы узнать, как сделать так, чтобы все ваши будущие сводные таблицы начинались в табличной форме, см. Это видео).
В чем преимущество Иерархии? Я пробовал создать обычную сводную таблицу без иерархии. У меня все еще есть возможность разворачивать и сворачивать поля. Но если я хочу показать только регионы Канады, мне придется добавить срез или фильтр отчетов.
Смотреть видео
Стенограмма видео
Изучите Excel из подкаста, эпизод 2196: детализация и детализация сводных таблиц.
Привет, добро пожаловать обратно в сеть, я Билл Джелен. В сводных таблицах есть загадка. Если я вставлю сюда сводную таблицу, вы увидите, что у нас есть поля Drill Up и Drill Down, но они никогда не загораются. Что с этим? Почему они у нас есть? Как заставить их работать? Хорошо, это отличный, отличный вопрос, и, к сожалению, мне это не нравится. Я пытаюсь всю свою жизнь работать в Excel, никогда не используя вкладку Power Pivot. Я не хочу, чтобы вам приходилось платить дополнительные 2 доллара в месяц за версию Office 365 Pro Plus, но это один - это один - где мы должны потратить дополнительные 2 доллара в месяц или найти кого-то, у кого есть дополнительные 2 доллара в месяц, чтобы установить это.
Я возьму этот формат данных в виде таблицы. Неважно, какой формат я выберу, формат не важен; просто достать нам столик - важная часть. Power Pivot, мы собираемся добавить эту таблицу в нашу модель данных, а затем нажать «Управление». Хорошо, вот наша таблица в модели данных. Нам нужно перейти в представление диаграммы, теперь мы сделаем его немного шире, чтобы мы могли видеть все поля. Я выберу Континент; Я собираюсь Shift + щелкнуть по городу. Теперь это составляет мою иерархию Drill Down, Drill Up. А затем щелкнем правой кнопкой мыши и скажем «Создать иерархию». И они дают нам имя … Я наберу "География" для моей Иерархии, вот так. Отлично, теперь, с этим одним изменением, мы вставляем сводную таблицу - и это будет сводная таблица модели данных - и вы видите, что мы можем добавить географию в качестве собственной иерархии.
Что мне в этом не особенно нравится, так это то, что все остальное перемещается в More Fields. Хорошо? Итак, мы выбираем Географию, и она вылетает в левую часть. И хотя это здорово, мне также нужно выбрать «Доход», и они взяли поля, которые не были частью Иерархии, и переместили их в Дополнительные поля. Так что, как я понимаю, они пытаются скрыть поля, которые я не должен выбирать, но в процессе этого они также скрывают здесь больше полей - доход или продажи. Хорошо. Итак, немного расстраивает, что нам нужно перейти к большему количеству полей, чтобы получить поля, которые не являются частью Географии, но так оно и есть.
Хорошо. Итак, теперь, когда у нас есть это, давайте посмотрим, что здесь работает. Сижу на Континенте, захожу во вкладку Analyze и ничего не загорается, не заработало. Стреляй! Нет, это сработало, вам просто нужно приехать в Северную Америку, и тогда я могу Drill Down, и он заменяет Continent на Country. А потом из Канады я могу Drill Down и получить Восточную Канаду и Западную Канаду. Из Восточной Канады я получаю Онтарио и Квебек. Онтарио, я получаю эти города, я могу развернуть Drill Up, Drill Up и выбрать Соединенные Штаты; Развернуть, развернуть, развернуть. Хорошо, вот как это работает.
Попробуйте, у вас должна быть вкладка Power Pivot или найти кого-нибудь с вкладкой Power Pivot. Если вы просто хотите попробовать, посмотрите описание YouTube, там будет ссылка на веб-страницу, и на веб-странице есть место, где вы можете загрузить этот файл, и вы сможете использовать Иерархию, даже если у вас нет вкладки Power Pivot. Если вы используете Excel 2016 или Office 365, он должен работать.
Вы знаете, понимаете, я думаю, что я не уверен, что я поклонник, так это то, что они избавляются от другой информации, а не используют значок "Развернуть", который затем расширяется до следующая группа, и следующая группа, и следующая группа. У нас всегда был значок «Развернуть», но даже тогда он работает немного по-другому. Здесь, если бы я мог, я действительно могу сидеть там, в Северной Америке, и расширять один уровень за раз, не выбирая каждый дополнительный из модели данных. Похоже, нам нужно перемещать указатель ячейки по одному биту за раз.
Хорошо, теперь этот совет действительно был обнаружен. MVP по Excel обсудили эти кнопки с командой Excel, поэтому в этой книге они не рассматриваются. Но много других замечательных советов содержится в LIVe, 54 величайших советах всех времен.
Подведение итогов на сегодня: почему Drill Up и Drill Down постоянно неактивны? Что ж, вам нужно создать Иерархию. Чтобы создать иерархию, вам нужно войти в Power Pivot; в представление диаграммы; выберите поля для иерархии; а затем щелкните правой кнопкой мыши; и создать иерархию.
Хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другой сетевой трансляции от.
