Как сделать ссылку на сводную таблицу в excel
Как создать интерактивные гиперссылки в сводной таблице?
Вы можете легко щелкнуть гиперссылку, чтобы перейти на ее веб-страницу на листе, но если вы создадите сводную таблицу, содержащую гиперссылки, гиперссылки не будут активными. У вас есть какие-нибудь хорошие идеи, как решить эту проблему в Excel?
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Создание интерактивных гиперссылок в сводной таблице с кодом VBA
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Гиперссылки не работают в сводной таблице по умолчанию, но, к счастью, вы можете создать код VBA, чтобы гиперссылки были интерактивными в сводной таблице.
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Затем выберите использованный рабочий лист слева Обозреватель проекта панели, дважды щелкните ее, чтобы открыть Модули, а затем скопируйте и вставьте следующий код VBA в пустой Модули:
Код VBA: создание интерактивных гиперссылок в сводной таблице
3. Затем сохраните и закройте этот код, вернитесь к своему рабочему листу, и теперь, когда вы щелкнете гиперссылку в сводной таблице, гиперссылка будет активирована и приведет к соответствующей веб-странице.
Рис. 167.1. Преобразование сводной таблицы в список
Хитрость создания такого списка заключается в использовании сводной таблицы. Но прежде чем вы сможете применить этот метод, вы должны добавить команду Мастер сводных таблиц на панель быстрого доступа. Excel 2007, Excel 2010 и Excel 2013 все еще поддерживают Мастера сводной таблицы, но он недоступен на ленте. Чтобы получить доступ к мастеру, выполните следующие действия.
После выполнения этих шагов ваша панель быстрого доступа будет содержать новый значок.
Теперь пришло время преобразовать сводную таблицу в список. Имейте в виду, что, хотя следующие шаги специфичны для образцов данных, приведенных здесь, вы можете легко изменять шаги для работы с вашими данными.
- Активизируйте любую ячейку в сводной таблице.
- Щелкните на значке Мастер сводных таблиц и диаграмм, который вы добавили на панель быстрого доступа.
- В диалоговом окне Мастер сводных таблиц и диаграмм установите первый переключатель в положение в нескольких диапазонах консолидации и нажмите кнопку Далее.
- В шаге 2а установите переключатель в положите Создать поля страницы и нажмите кнопку Далее.
- В шаге 2b в поле Диапазон укажите диапазон сводной таблицы ( А1:Е4 для выборки из примера) и нажмите кнопку Добавить; затем нажмите кнопку Далее, чтобы перейти к шагу 3.
- В шаге 3 выберите место для сводной таблицы и нажмите кнопку Готово. Excel создаст сводную таблицу с данными и покажет область Список полей сводной таблицы.
- В области Список полей сводной таблицы снимите флажки Строка и Столбец.
Так, сводная таблица остается только с полем данных Сумма по полю Значение. На данный момент небольшая сводная таблица показывает только сумму всех значений (рис. 167.2).
Рис. 167.2. Эта небольшая сводная таблица может быть расширена
Чтобы завершить, щелкните два раза на ячейке, содержащей итоговое значение (2851 в этом примере). Excel создаст новый лист, который будет содержать исходные данные в виде таблицы (рис. 167.3). Заголовки столбцов представляют общее описание (Строка, Столбец и Значение), так что вы, вероятно, захотите изменить их и сделать более информативными.
Практическая ситуация: из разных филиалов ежедневно поступают отчеты и их надо объединить в один общий отчет. Отчеты филиалов имеют общий формат.
Например, что-то в это роде
Для получения быстрой аналитики можно воспользоваться сводными таблицами Excel. Чтобы сформировать сводную таблицу в которой будут все три филиала нужно их собрать на одном листе. Для этого создаем новый лист "Свод" и копируем туда данные из листов с отчетами.
Важно. Так как эта сводная таблица будет обновляться каждый день, то нужно ее сделать "умной", тогда не нужно будет каждый раз переписывать ссылки в формулах.
Делаем умную таблицу.
Практические советы по работе с экономическими данными в Excel, читайте в моей книге " Excel для экономистов. 10 статей про отчеты, сверки, сопоставления"
Копируем на лист "Свод" таблицу с первым филиалом, выделяем ее, заходим во вкладку "Вставка" и нажимаем кнопку "Таблица"
Ваша таблица примет следующий вид (если такой вид не нравится можно его поменять)
Теперь просто копируем данные из отчетов двух оставшихся филиалов, они тоже примут такой же формат. Кроме того, если ввести в эту таблицу формулу (например, средняя цена, то она будет автоматически копироваться при добавлении новых данных)
Если какой-то диапазон не попал при копировании в умную таблицу, ее границы можно легко расширить, достаточно потянуть за правый крайний угол (там есть небольшой синий значок)
В итоге получается такая умная таблица
На основании этой таблицы можно сделать сводные таблицы, которые позволят получать быструю аналитику. Например, такую
Или такую (все зависит от целей анализа)
Сводные таблицы, конечно, удобны для быстрой аналитики, но работать с ними не всегда удобно: сложно добавлять формулы, строки могут меняться в зависимости от состава данных, поэтому я больше люблю работать с таблицами, в которые данные подтягиваются при помощи формул
При помощи формул мы можем сделать, например, такую таблицу (такой же формат можно сделать и в сводной, кому что нравится)
Вот эта же таблица с формулами
Используется формула СУММЕСЛИМН(Таблица2[Сумма];Таблица2[Менеджер];$B$2;Таблица2[Номенклатура];A3)
Таблица2 - это автоматические название нашей умной таблицы (при необходимости его можно поменять, но я на практике никогда этого не делаю за ненадобностью).
В квадратных скобках [ ] ссылки на столбцы из умной таблицы
B2 и А3 ссылки на критерий поиска: конкретного менеджера (в данном случае Менеджера 1) и конкретную номенклатуру (в данном случае Чайник).
Если ссылку на менеджера закрепить знаками доллара $B$2, то формула легко и правильно скопируется вниз по столбцу.
По строке закрепление долларами не помогает (проверьте сами). В данном примере для копирования по строке я во все столбцы по строке 3 скопировала формулу из ячейки В3 без знака равно, потом в каждой формуле поставила знак равно, изменила ссылку на нужного Менеджера, закрепила ее знаком доллара и скопировала вниз по столбцу. (В сети есть рекомендации по доработке формулы, чтобы ссылка на умную таблицу тянулась и по строке, я не использовала, так как формула и без того длинная)
Подсветка красным - это условное форматирование (максимальные продажи по строке номенклатура)
В этой таблице по полученным данным можно добавлять любые формулы (например, мотивацию, штрафы, доли и прочее).
Если появится новая номенклатура или новый менеджер, то нужно будет заводить новую строку или столбец, автоматически он не добавиться.
Проверим, как работают умные таблицы при вводе новых данных. В умную таблицу добавим продажи пульта по "Менеджеру 1" за 3.02.19 на сумму 2000 руб. (Если вы буде вводить прямо под умной таблицей, ее диапазон автоматически расширится, если не расширится расширьте его сами, как я писала выше)
В этой части самоучителя подробно описано, как создать сводную таблицу в Excel. Данная статья написана для версии Excel 2007 (а также для более поздних версий). Инструкции для более ранних версий Excel можно найти в отдельной статье: Как создать сводную таблицу в Excel 2003?
В качестве примера рассмотрим следующую таблицу, в которой содержатся данные по продажам компании за первый квартал 2016 года:
Для начала создадим очень простую сводную таблицу, которая покажет общий объем продаж каждого из продавцов по данным таблицы, приведённой выше. Для этого необходимо сделать следующее:
- Выбираем любую ячейку из диапазона данных или весь диапазон, который будет использоваться в сводной таблице.ВНИМАНИЕ: Если выбрать одну ячейку из диапазона данных, Excel автоматически определит и выберет весь диапазон данных для сводной таблицы. Для того, чтобы Excel выбрал диапазон правильно, должны быть выполнены следующие условия:
- Каждый столбец в диапазоне данных должен иметь своё уникальное название;
- Данные не должны содержать пустых строк.
- Кликаем кнопку Сводная таблица (Pivot Table) в разделе Таблицы (Tables) на вкладке Вставка (Insert) Ленты меню Excel.
- На экране появится диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.Убедитесь, что выбранный диапазон соответствует диапазону ячеек, который должен быть использован для создания сводной таблицы. Здесь же можно указать, куда должна быть вставлена создаваемая сводная таблица. Можно выбрать существующий лист, чтобы вставить на него сводную таблицу, либо вариант – На новый лист (New worksheet). Кликаем ОК.
- Появится пустая сводная таблица, а также панель Поля сводной таблицы (Pivot Table Field List) с несколькими полями данных. Обратите внимание, что это заголовки из исходной таблицы данных.
- В панели Поля сводной таблицы (Pivot Table Field List):
- Перетаскиваем Sales Rep. в область Строки (Row Labels);
- Перетаскиваем Amount в Значения (Values);
- Проверяем: в области Значения (Values) должно быть значение Сумма по полю Amount (Sum of Amount), а не Количество по полю Amount (Count of Amount).
В данном примере в столбце Amount содержатся числовые значения, поэтому в области Σ Значения (Σ Values) будет по умолчанию выбрано Сумма по полю Amount (Sum of Amount). Если же в столбце Amount будут содержаться нечисловые или пустые значения, то в сводной таблице по умолчанию может быть выбрано Количество по полю Amount (Count of Amount). Если так случилось, то Вы можете изменить количество на сумму следующим образом:
- В области Σ Значения (Σ Values) кликаем на Количество по полю Amount (Count of Amount) и выбираем опцию Параметры полей значений (Value Field Settings);
- На вкладке Операция (Summarise Values By) выбираем операцию Сумма (Sum);
- Кликаем ОК.
Сводная таблица будет заполнена итогами продаж по каждому продавцу, как показано на рисунке выше.
Если необходимо отобразить объемы продаж в денежных единицах, следует настроить формат ячеек, которые содержат эти значения. Самый простой способ сделать это – выделить ячейки, формат которых нужно настроить, и выбрать формат Денежный (Currency) в разделе Число (Number) на вкладке Главная (Home) Ленты меню Excel (как показано ниже).
В результате сводная таблица примет вот такой вид:
Обратите внимание, что формат валюты, используемый по умолчанию, зависит от настроек системы.
Рекомендуемые сводные таблицы в последних версиях Excel
В последних версиях Excel (Excel 2013 или более поздних) на вкладке Вставка (Insert) присутствует кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables). Этот инструмент на основе выбранных исходных данных предлагает возможные форматы сводных таблиц. Примеры можно посмотреть на сайте Microsoft Office.
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Аналогом функции ВПР для сводных таблиц является функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которая возвращает данные, хранящиеся в отчете сводной таблицы.
Чтобы получить быстрый доступ к функции, необходимо ввести знак равенства в ячейку (=) и выделить необходимую ячейку в сводной таблице. Excel сгенерирует функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ автоматически.
Отключение создания GetPivotData
Чтобы отключить автоматическую генерацию функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, выберите любую ячейку в сводной таблице, перейдите по вкладке Работа со сводными таблицами -> Параметры в группу Сводная таблица. Щелкните по стрелке вниз, находящейся рядом с вкладкой Параметры. В выпавшем меню, уберите галку с пункта Создать GetPivotData.
Теперь, при ссылке на ячейку, находящуюся в сводной таблице, Excel будет генерировать адрес ячейки.
Использование ссылок на ячейки в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Вместо указания названия пунктов или полей в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, вы можете ссылаться на ячейки, находящиеся на листе. В примере ниже ячейка E3 содержит название продукта, а формула в ячейке E4 ссылается на нее. В результате будет возвращен суммарный объем по тортам.
Использование ссылок на поле сводной таблицы
Вопросов по работе ссылок на пункты сводной таблицы нет, проблемы возникают, если мы захотим сослаться на поле данных.
В примере ячейка E3 содержит название поля данных «Количество», и было бы неплохо ссылаться на эту ячейку в функции, вместо того, чтобы иметь название поля в формуле ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
Проблему решит простое добавление пустой строки (“”) в начало или конец ссылки на ячейку.
Простая коррекция формулы приведет к возврату правильного значения.
Использование дат в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Если вы используете даты в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, у вас могут возникнуть проблемы, даже если дата отображается в сводной таблице. К примеру, аргументом формулы ниже является дата “21/04/2013”, и сводная таблица содержит поле с датами продаж. Однако формула в ячейке E4 возвращает ошибку.
Для предотвращения ошибок, связанных с датами, вы можете воспользоваться одним из следующих способов:
- Сравнять форматы дат в формуле и сводной таблице
- Использовать функцию ДАТАЗНАЧ
- Использовать функцию ДАТА
- Сослаться на ячейку с корректной датой
Сравнять форматы дат в формуле и сводной таблице.
Для получения корректного результата, во время использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, убедитесь, что форматы дат аргумента формулы и сводной таблицы одинаковые.
В ячейке E4, в формуле использована дата формата “ДД.ММ.ГГГГ”, и в результате возвращена правильтая информация.
Использование функции ДАТАЗНАЧ
Вместо ручного ввода даты в формуле, можно добавить функцию ДАТАЗНАЧ для возврата даты.
В ячейке E4, дата введена с помощью функции ДАТАЗНАЧ, и Excel возвращает необходимую информацию.
Использование функции ДАТА
Вместо ручного ввода даты в формуле, можно воспользоваться функцией ДАТА, которая позволит корректно вернуть необходимую информацию.
Ссылка на ячейку с датой
Вместо ручного ввода даты в формуле, можно сослаться на ячейку, содержащую дату (в любом формате, в котором Excel воспринимает данные, как даты). В примере в ячейке E4, формула ссылается на ячейку E3 и Excel возвращает корректные данные.
Читайте также: