Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как создать интерактивные гиперссылки в сводной таблице?

Как использовать сводные таблицы Excel в КДП

Прежде чем разбираться в нюансах применения, выясним, что такое сводная таблица в Excel. В общем случае под этим термином понимают особый инструмент стандартной версии пакета MS Office — Excel, позволяющий выбрать из массива данных необходимые сведения и совершить с ними простые арифметические действия. Допустим, мы имеем массив следующего вида, иллюстрирующий работу фирмы по продаже оргтехники:

Количество проданных товаров можно просмотреть с помощью Excel, построив сводную таблицу.

Выясните, каков общий объем продаж в каждой категории.

Проверьте остатки по каждой категории товаров и т.д.

Как использовать

Сводные таблицы в Excel для чайников представляются чем-то очень сложным и непонятным. На самом же деле не все так страшно. Перед тем как сделать сводную таблицу в Excel, необходимо «раздобыть» для нее исходные данные. Получают их как автоматически, выгрузив необходимую информацию из 1С или другой программы, например, системы ЭДО, так и в ручном режиме, создав документ со всеми необходимыми данными. Идеальный вариант, если сам учет деятельности ведется в Эксель, тогда никаких дополнительных действий совершать не придется. Главное — проверить, что исходный массив соответствует следующим требованиям:

  • Нет объединенных ячеек,
  • Нет пустых строк и столбцов,
  • Все столбцы имеют заголовки.

Если какое-либо из этих условий не выполняется, данные должны быть отформатированы, иначе база данных не может быть создана. Чтобы проиллюстрировать, как создать электронную таблицу в Excel, используйте в качестве примера следующий документ:

Создаем базу Excel с помощью функции «Вставка» — «Таблица» — «Сводная таблица».

Получаем следующий результат:

Осталось только показать вам, как работать с электронными таблицами Excel. Снимая и устанавливая флажки в списке полей, мы изменяем вид отчета. Давайте посмотрим количество транзакций для каждого менеджера.

Теперь давайте добавим виды продаж.

Как сделать вычисления

В отчет можно добавить вычисляемые поля. Для этого необходимо поставить курсор в любую ячейку Еxcel, выбрать вкладку «Анализ» — «Вычисления» — «Поля, элементы и наборы» — «Вычисляемое поле». В появившемся окне зададим имя поля и формулу для вычислений. В нашем случае зарплата составляет 5% от выручки, и формула выглядит следующим образом:

Колонки можно добавлять или удалять, перетаскивая их в соответствующую область.

Если данные в исходном массиве изменились, базу необходимо обновить. Добавим менеджера Самуйлову в исходные данные, поставим курсор в любую ячейку базы и обновим результат сведений с помощью вкладки «Анализ» — «Обновить данные».

Чтобы настроить автоматическое обновление данных при открытии файла, необходимо установить галочку в соответствующем месте (вкладка «Анализ» — «Параметры» — «Данные»).

Удалите основание, выделив его и нажав клавишу Delete.

Где применять

Мы надеемся, что вы поняли, как создать таблицу Excel и как с ней работать. Теперь немного о том, для чего используются электронные таблицы Excel. Прежде всего, они очень полезны для расчета ключевых показателей эффективности (KPI). Во-вторых, они также полезны, если вы хотите создавать отчеты по сотрудникам, например, по полу, возрасту, образованию и т.д.

Читайте так же:
Как скопировать и вставить размеры ячеек (ширину столбцов и высоту строк) в Excel?

Расчет KPI

Современные CRM-системы позволяют мгновенно загружать все необходимые отчеты. Но как быть тем, кто не использует специализированное программное обеспечение? По-прежнему можно как создать сводную таблицу в Excel, так и рассчитать необходимые показатели вручную. Второй способ кажется проще, но не всегда практичен. Если исходные данные представлены в виде такого списка, очень полезно использовать сводные записи, так как это значительно облегчает последующую работу.

Используя сводную базу данных, мы уже рассчитали объем продаж для каждого менеджера.

Для дальнейшего расчета мы будем использовать эти данные. Сравним плановый показатель с фактическим и рассчитаем отклонение.

Менеджеры, которые превышают целевой показатель (отклонение положительное), имеют право на премию, рассчитанную как процент от дохода, превышающего целевой показатель. Давайте рассчитаем размер бонуса.

Отчет по персоналу

Практически все данные о персонале получаем из 1С. Но если такой софт в организации не используется или необходим отчет в другой форме, не остается ничего, кроме как делать сводные таблицы в Еxcel. Даже если массив данных составляется в ручном режиме, базы помогут представить их в более «красивом» виде. Имея сведения об образовании, стаже, окладе сотрудников в виде подобного списка, есть возможность, допустим, выяснить, сколько сотрудников каждого из отделов имеют образование определенного уровня.

Решение более сложных задач возможно с помощью подобной базы данных. Выведем минимальные зарплаты сотрудников различных отделов для каждого уровня образования.

Полезно использовать эти отчеты для построения диаграмм, которые представляют информацию в графическом виде.

Как создать сводную таблицу в excel 2010

Как создать сводную таблицу в excel 2010

Вы можете создавать сводные таблицы двумя способами. Давайте рассмотрим каждый из них.

Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»

Прежде чем создавать отчет в электронной таблице, решите, каким будет источник данных. Рассмотрим источник в том же документе.

1. Начните с создания простой таблицы с элементами, которые вы хотите использовать в отчете. Убедитесь, что верхняя строка содержит заголовки столбцов.

2. Откройте вкладку «Вставка» и выберите инструмент «Сводная таблица» в разделе «Таблицы».

Если вы хотите создать сводную диаграмму одновременно с поворотной таблицей, нажмите на стрелку в правом нижнем углу значка поворотной таблицы и выберите «Pivot Table».

3. В открывшемся диалоговом окне Create Crosstab выберите таблицу с данными, которые вы только что создали, или ее диапазон. Затем выберите нужный диапазон.

Создание сводной таблицы

Вы можете указать внешний источник в качестве данных для анализа: установите флажок в соответствующем поле и выберите нужное подключение из списка доступных подключений.

Читайте так же:
Как создать плавающую гистограмму в Excel?

4. Затем укажите, куда следует поместить отчет электронной таблицы. Лучше всего делать это на новом листе.

5 После подтверждения действия кнопкой OK макет отчета будет создан и открыт. Давайте посмотрим на это.

В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.

Список полей сводной таблицы

Вы можете перетащить имена полей в соответствующие окна, чтобы сделать их именами строк или столбцов, а затем отсортировать их в удобном порядке. Вы можете фильтровать отдельные элементы, перетащив соответствующее поле в окно «Фильтр». Поле, в котором суммируются расчеты и результаты, помещается в окно «Значение».

управление расположением полей

Существуют также дополнительные возможности редактирования отчетов из меню Crosstabs на вкладках Options и Build. Эти инструменты предоставляют широкий спектр опций и дополнительных возможностей.

Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»

Чтобы применить этот способ, придется сделать доступным инструмент, который по умолчанию на ленте не отображается. Откройте вкладку «Файл» — «Параметры» — «Панель быстрого доступа». В списке «Выбрать команды из» отметьте пункт «Команды на ленте». А ниже, из перечня команд, выберите «Мастер сводных таблиц и диаграмм». Нажмите кнопку «Добавить». Иконка мастера появится вверху, на панели быстрого доступа.

Мастер сводных таблиц и диаграмм

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

Щелкните значок мастера на панели быстрого доступа. Выберите нужный элемент из списка источников данных в диалоговом окне:

  • «в списке или базе данных Microsoft Excel» — источником будет база данных рабочего листа, если таковая имеется;
  • «во внешнем источнике данных» — если существует подключение к внешней базе, которое нужно будет выбрать из доступных;
  • «в нескольких диапазонах консолидации» — если требуется объединение данных из разных источников;
  • «данные в другой сводной таблице или сводной диаграмме» — в качестве источника берется уже существующая сводная таблица или диаграмма.

Мастер сводных таблиц в Excel 2010

2. Выберите тип отчета, который вы хотите создать: сводки, диаграммы или таблицы (с таблицей).

  • Если в качестве источника выбран текущий документ, где уже есть простая таблица с элементами будущего отчета, задайте диапазон охвата — выделите курсором нужную область. Далее выберите место размещения таблицы — на новом или на текущем листе, и нажмите «Готово». Сводная таблица будет создана.
  • Если же необходимо консолидировать данные из нескольких источников, поставьте переключатель в соответствующую область и выберите тип отчета. А после нужно будет указать, каким образом создавать поля страницы будущей сводной таблицы: одно поле или несколько полей.
Читайте так же:
Как скопировать формулу ячейки только без форматирования в Excel?

Вы должны сначала указать диапазоны источников данных, если вы выбрали «Создать поля страницы»: выберите первый диапазон, нажмите «Добавить», затем следующий и так далее.

Для удобства диапазонам можно присваивать имена. Для этого выберите одно из них из списка и укажите количество полей страницы, которые будут созданы для него, затем дайте каждому полю имя (метку). Затем выберите следующий диапазон и так далее.

Мастер сводных таблиц в Excel 2010

После завершения нажмите кнопку Далее, выберите, где будет отображаться будущая сводная таблица — на этом листе или на другом, нажмите кнопку Готово, и отчет с несколькими источниками будет создан.

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

Как в Excel создать гиперссылку и какие типы ссылок существуют

Чаще всего гиперссылки в excel используются при создании прайс-листов

Наиболее распространенное использование гиперссылок в Excel — это создание прайс-листов.

Microsoft Excel — это табличный процессор, который умеет работать с таблицами, может выполнять математические расчеты любой сложности, а на основе полученных данных создавать диаграммы, графики и пр. Эксель входит в офисный пакет Microsoft Office, который, наверное, установлен на каждом компьютере. Одно из основных понятий, которое нужно знать каждому пользователю при работе с этой программой, это ссылки в Excel.

Ссылки — это указатели на некоторый объект. Создать такой указатель очень просто: нужно лишь ввести в любое поле знак «=» (равно), а затем выбрать, например, ячейку, на которую необходимо ссылаться. Ссылки всегда будут копировать то значение, которое находится в исходной ячейке. Если исходные данные изменятся, то и результат мгновенно будет обновлен. Например, в поле A1 указано число 5. Необходимо создать ссылку на это число в другом поле — B1. Для этого нужно щелкнуть левой кнопкой мыши на ячейке B1, написать знак равно, выбрать поле A1 и нажать «Enter». В поле B1 тут же появится результат — число 5. Если теперь исходное число в поле A1 изменить, например, написать 12, то результат в ячейке B1 тут же обновится — там тоже будет стоять число 12. То есть ссылки копируют не конкретное число (или слово), которое было написано, а данные, указанные в ячейке. Это очень помогает пользователю при работе с табличным процессором Excel.

Видео по работе с гиперссылками в Excel

Абсолютные и относительные ссылки в Excel

Итак, ссылки — это формулы, которые копируют данные с исходной ячейки (группы ячеек, строки, столбца и т.д.). Они могут быть 2 видов: относительные и абсолютные.

Чаще всего при работе в Excel используются относительные ссылки. Это обычная формула, которая выглядит примерно таким образом: «=B1» или «=Лист1!A1». В первом случае дублируется значение поля B1, а во втором — поля A1 с первого листа рабочей книги Excel. Если такую формулу скопировать, к примеру, потянуть вниз, то она тоже распространится вниз по ячейкам. Если в первом примере скопировать ссылку еще на 2 строки вниз, то результат будет следующим: «=B2» и «=B3».

Читайте так же:
Как создать динамическую сводную таблицу для автоматического обновления расширяемых данных в Excel?

При создании гиперссылки можно указать, куда она будет ссылаться - на веб-расположение, место с текущем документе, ином документе либо электронную почту

При создании гиперссылки можно указать, куда она будет ссылаться — на веб-расположение, место с текущем документе, ином документе либо электронную почту

Абсолютные ссылки содержат в себе формулу, которая копирует только одно и то же поле. Абсолютные ссылки содержат в себе фиксированное значение, и если пользователь скопирует эту формулу куда-то еще — значение останется неизменным (оно не распространится вниз или в сторону). Но зачем нужны такие ссылки?

Например, в Excel создана таблица для расчета зарплаты сотрудников. И пользователю необходимо рассчитать каждому сотруднику зарплату, опираясь на исходные данные: количество отработанных часов, почасовая оплата и пр. Формула простая: количество отработанных часов умножается на почасовую оплату. Затем пользователю необходимо скопировать эту формулу для всех остальных сотрудников, к примеру, просто потянув ее вниз. Но в данном случае «почасовая оплата» — это фиксированное значение, которое находится всего в одной ячейке. И если потянуть формулу вниз, то это значение просто сместится вниз на пустое поле (по умолчанию там стоит 0). А если любое число умножить на 0, то получится 0. Посчитать зарплату таким способом не получится. Для этого надо знать, как сделать абсолютную ссылку и зафиксировать значение в поле «Почасовая оплата», чтобы при копировании оно никуда не смещалось.

Делается это несложно: нужно лишь написать обычную ссылку (например, «=A1»), а затем нажать кнопку F4. Теперь формула будет выглядеть так: «=$A$1». Знак доллара означает, что значение зафиксировано, и если пользователь потянет формулу вниз или в сторону — это число останется неизменным.

Можно также указать, чтобы значение поля «Почасовая оплата» сохранялось только при копировании по столбцам, а при копировании по строкам — не сохранялось (или наоборот). Для этого нужно просто еще раз нажать кнопку F4. Если нужно оставить фиксированное значение при копировании формулы по столбцам, то формула будет выглядеть так: «=A$1», а если по строкам — тогда «=$A1». Такие ссылки называются смешанными.

Как сделать в Экселе гиперссылку

В Excel много ссылок, но есть и гиперссылка. Когда пользователь нажимает на нее, источник открывается так же, как и на сайте. Это может быть изображение, документ или интернет-сайт. С ее помощью можно автоматизировать рабочий процесс Excel так, чтобы нужный сайт или документ открывался одним щелчком мыши.

Существует три способа создания гиперссылки в Excel:

  • Непосредственно в ячейке;
  • С помощью объектов;
  • С помощью функций.

Достаточно щелкнуть правой кнопкой мыши по любой ячейке и в появившемся списке выбрать пункт «Гиперссылка». Откроется новое окно, в котором необходимо указать адрес сайта или файл на компьютере. Также можно создать ее и через панель меню: «Вставка — Гиперссылка».

Читайте так же:
Как сделать прозрачный цвет фона флажка в Excel?

Теперь, когда вы знаете, как сделать простую гиперссылку, можно легко создать такую же ссылку с помощью объектов рабочего листа. К ним относятся любые изображения, надписи, фигуры, объекты WordArt, диаграммы и пр. Для этого нужно вызвать контекстное меню на одном из таких объектов и выбрать пункт «Гиперссылка». Кроме того, можно это сделать и с помощью комбинации клавиш — Ctrl+K.

Для создания гиперссылки можно использовать встроенную функцию. Эта функция имеет следующий синтаксис: =Гиперссылка(адрес; [имя]). Она может быть вызвана двумя способами:

  • через панель меню «Вставка — Функция»;
  • вручную.

Если воспользоваться первым способом, то сначала в появившемся окне необходимо будет найти функцию «Гиперссылка» (проще всего написать название функции в поле «Поиск функции»). После этого откроется еще одно окно, в котором надо будет заполнить 2 параметра — адрес и имя. Адрес — это местоположение ячейки, диапазона ячеек или объекта в документе. Здесь можно указать и страницу в интернете или путь к файлу на компьютере. А в поле «Имя» указывается наименование, которым будет подписано выбранное поле. Хотя заполнять его необязательно.

Все это можно сделать вручную, выделив нужное поле и набрав: =hyperlink(. После открытой скобки нужно указать адрес, поставить точку с запятой, дать имя гиперссылки, закрыть скобку и нажать «Enter». Например, в поле E1 можно ввести следующую формулу: =Гиперссылка(«yandex.ru»; «Открыть Яндекс»). В поле E1 введите фразу «Открыть Яндекс», нажатие на которую приведет к открытию сайта в браузере.

Другой способ создания гиперссылок — использование макросов. Однако этот вариант слишком сложен. Существуют более простые варианты.

Виды гиперссылок

При добавлении гиперссылки первым способом (непосредственно в ячейку) пользователь будет работать с диалоговым окном «Добавить гиперссылку», которое предлагает 4 способа соединения:

  1. Файл, веб-страница (указывается путь к файлу или адрес веб-сайта).
  2. Поместите в документ (лист или ячейку).
  3. Новый документ (путь к новому документу).
  4. E-mail (здесь вы указываете адрес получателя, который будет отображаться при открытии Microsoft Outlook).

Существующие типы гиперссылок

Существующие типы гипертекстовых связей

Если вам нужно изменить или удалить существующую гиперссылку, просто щелкните ее правой кнопкой мыши и выберите соответствующую опцию («изменить» или «удалить гиперссылку»).

Такими довольно простыми способами можно создать гиперссылку в Excel. Наиболее простым и практичным способом является первый — через ячейку. Дело в том, что именно здесь собраны все варианты: можно создать ссылку на любое поле в документе, можно указать путь к файлу, адрес сайта и т.д. Хотя использовать можно любой из этих способов — как удобнее.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector