Eurotehnik.ru

Бытовая Техника "Евротехник"
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Сводная таблица Excel

Сводная таблица Excel

Во-первых, давайте рассмотрим определение сводной таблицы в Excel. В очень упрощенном виде лучший ответ — «Сводная таблица — это программный инструмент Excel, который обобщает большой набор данных». У нас есть огромные порции данных в Excel, и чтобы упростить работу, мы используем сводные таблицы, чтобы обобщить наши данные, чтобы получить желаемый отчет о данных. Сводная таблица Excel извлекает значимость из данных с набором различных записей и полей.

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

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

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

Это очень просто и удобно в использовании. Давайте посмотрим, как создать сводную таблицу с помощью нескольких примеров.

Вы можете скачать этот шаблон Excel для сводной таблицы здесь — Шаблон Excel для сводной таблицы

Пример № 1

В компании, где работа отдела заключается в том, чтобы пометить определенные идентификаторы, правильные они или нет. Они обрабатывают данные, которые имеют своего рода идентификаторы и помечают их как правильные или нет.

Ниже приведен скриншот необработанных данных,

Предположим, что менеджер компании в отделе хочет узнать, сколько Prop_ID было правильным и неправильным. Он может вручную подсчитать эти значения, но для большого набора данных это будет медленной задачей. Но есть способ сделать это легко.

В своей рабочей книге Excel он нажмет кнопку «Вставить» и нажмет на сводную таблицу на самой левой кнопке своего экрана. Теперь он может выбрать тот же или новый лист, чтобы открыть эту сводную таблицу. Чтобы узнать количество правильных и неправильных значений в этом prop_id, поле «Статус», которое является правильным или неправильным, будет перетащено вниз в секцию строк, а значения, относящиеся к идентификатору свойства в Prop_ID, будут перетащены вниз в секцию значений.

  • Выберите данные и перейдите на вкладку Вставка, щелкните по сводным таблицам в разделе таблиц.

  • Откроется диалоговое окно. На изображении выше есть несколько флажков, во-первых, чтобы выбрать диапазон таблицы, который мы сделали, выбрав данные. Теперь, где вставить сводную таблицу для вставки в тот же лист или другой лист? Если данные очень велики, тогда удобно вставить сводную таблицу в новую рабочую таблицу и нажать «ОК».

  • Мы получаем приведенный ниже результат: с правой стороны у нас есть поля сводных таблиц, которые будут перемещены в строки и столбцы в виде желаемого отчета, а с левой стороны будет создана сводная таблица.

  • Наша задача — проверить, сколько идентификаторов свойств были помечены как правильные, а сколько были помечены как неверные аудитором. Перетащите поля Auditor в секцию Rows, Property_id в секцию value, а поле status в секцию filters.
Читайте так же:
Как включить юсб порты в биосе

  • Мы создали нашу сводную таблицу, которая в настоящее время показывает общее количество идентификаторов свойств, отмеченных аудиторами.

  • Теперь, чтобы проверить статус, сколько идентификаторов свойств были помечены как правильные и сколько были помечены как неправильные. В сводной таблице под статусом раздела нажмите на нее.

  • Теперь выберите несколько объектов, затем выберите «Исправить» и нажмите «ОК».

  • Теперь у нас есть количество идентификаторов свойств, помеченных аудитором как правильные.

  • Точно так же мы могли рассчитывать на неправильные.

Пример № 2

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

Ниже приведен скриншот необработанных данных,

Эти данные содержат не более 20 строк, но в этом примере это 66 строк, которые могут пойти еще дальше, и было бы утомительно проверять продажи определенного продукта в любом квартале.

  • Выберите данные для вставки сводной таблицы. На этот раз мы будем использовать горячую клавишу для вставки сводных таблиц, нажмите Alt, затем «D», а затем «P». Появится другое диалоговое окно. У нас есть данные в Excel, и мы хотим создать сводную таблицу, затем мы должны нажать кнопку «Далее».

  • На следующем шаге он запрашивает диапазон данных. Но так как мы уже выбрали данные, они уже заполнены. Нажмите на Далее.

  • Теперь последнее диалоговое окно спрашивает нас, где мы хотим, чтобы наши сводные таблицы находились в том же самом листе или другом. Мы должны выбрать Новый лист и затем нажать «Готово».

  • С правой стороны у нас есть поля сводной таблицы, а с левой стороны у нас есть отчет по сводной таблице.

  • Перетащите продукт в раздел строк, продажи в соответствии со значениями и квартал в разделе столбцов, а год в фильтре. И у нас есть наш отчет.

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

  • Если мы хотим проверить в 2017 году, каковы были продажи продуктов, просто отметим 2018 год во вкладке года.

  • Теперь у нас есть продажи для продуктов в 2017 году.

Объяснение сводной таблицы в Excel

По сути, сводные таблицы — это мощный инструмент Excel, который помогает нам суммировать большие объемы данных и экономит много времени.

Сводные таблицы — это инструмент отчетности, в котором есть раздел полей, содержащий четыре поля:

  • Строки: данные, которые принимаются в качестве спецификатора.
  • Значения: количество данных.
  • Фильтры: фильтры, чтобы скрыть определенные данные.
  • Столбцы: значения в разных условиях.

Что нужно помнить о сводной таблице Excel

  • Сводные таблицы не изменяют значения в базе данных.
  • Сводные таблицы могут быть вставлены в один лист с данными или в другой лист.
  • Для удобства мы добавим сводные таблицы в новый рабочий лист.

Рекомендуемые статьи

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

Читайте так же:
Можно ли варить инвертором проволокой от полуавтомата

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

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

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

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

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

Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).

Диалоговое окно создания сводной таблицы

Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.

Пустая сводная таблица

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

Панель управления полями сводной таблицы

В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.

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

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

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

Читайте так же:
Как в excel столбцы перевести в буквы

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

Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

В результате мы получаем настоящую сводную таблицу.

Сводная таблица

На ее построение потребовалось буквально 5-10 секунд.

Вам также могут быть интересны следующие статьи

Объединение таблиц Excel без использования функций и макросов.
Здравствуйте! Меня зовут Сергей, я представляю проект All-Link. Он создан для анализа данных и здорово повышает возможности Excel, Open office и их аналогов. Например, он умеет буквально в несколько кликов объединять таблицы, даже если их много и структура переноса данных в них сложная. Мы хотим выяснить пределы его возможностей и приглашаем вас протестировать функции объединения таблиц на ваших данных и задачах. Подробности здесь — https://a3link.com/merge-tables-ru/
Доступ, разумеется, бесплатный.

Добрый день.. Меня интересует повторяющиеся числа по горизонтали .. число 1 сколько раз повторяется, число 2, число 3, число 4 и вместе от 5 до 8 сколько раз, если не трудно Может Вы мне поможете Спасибо большое, до свидания…

Как известно, макеты сводных таблиц отличаются в Excel 2003 и Excel 2007 или более поздних версиях. И если вы предпочитаете классический макет сводной таблицы (в Excel 2003), как можно вернуть классический макет сводной таблицы в Excel 2007 или более поздних версиях?

  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Читайте так же:
Где вводить ключ в стиме в браузере

стрелка синий правый пузырь Установите классический макет сводной таблицы с опцией проверки

Популярные

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Фактически, у вас есть возможность изменить макет сводной таблицы на классический макет Excel 2003 в Excel.

1. Щелкните правой кнопкой мыши любую ячейку сводной таблицы, чтобы отобразить контекстное меню, и выберите Параметры сводной таблицы. Смотрите скриншот:

док-классик-сводный макет-1

2. в Параметры сводной таблицы диалоговое окно, нажмите Показать вкладка и проверка Классический макет сводной таблицы (позволяет перетаскивать поля в сетке) вариант. Смотрите скриншот:

док-классик-сводный макет-2

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

док-классик-сводный макет-3док-стрелкадок-классик-сводный макет-4

стрелка синий правый пузырь Установите классический макет сводной таблицы с сохранением в виде книги 97-03

Если вы хотите установить классический макет сводной таблицы книги, вы можете сначала сохранить книгу как книгу 97-03, а затем вставить сводные таблицы.

1. Нажать на Офисы или Файл вкладку и щелкните Сохранить как. Смотрите скриншот:

док-классик-сводный макет-5

2. в Сохранить как В диалоговом окне выберите папку, в которой вы хотите разместить книгу, затем выберите Книга Excel 97-2003 от Сохранить как список. Смотрите скриншот:

док-классик-сводный макет-6

3. Нажмите Сохраните чтобы закрыть диалог. Тогда все сводные таблицы, которые вы будете вставлять в книгу в будущем, будут автоматически отображаться как классический макет сводной таблицы.

КОЛОННЫ

Вы можете перетащить поля в область COLUMNS.

Поля, помещенные в область COLUMNS, отображаются в сводной таблице как столбцы, а метки столбцов являются значениями выбранных полей.

Перетащите поле Region в область COLUMNS. Сводная таблица отображается с первым столбцом, содержащим метки строк — продавец и месяц, следующими четырьмя столбцами, содержащими метки столбцов — регион и последний общий столбец, как указано ниже.

Перетащите поле Месяц из ряда в столбцы.

Перетащите поле Регион из столбцов в ряды. Ваш макет сводной таблицы изменится, как указано ниже.

Перетащите поле Месяц из ряда в столбцы.

Перетащите поле Регион из столбцов в ряды. Ваш макет сводной таблицы изменится, как указано ниже.

Столбцы Ряд

Вы можете видеть, что теперь есть только пять столбцов — первый столбец с метками строк, три столбца с метками столбцов и последний столбец с итоговой суммой.

Количество строк и столбцов зависит от количества значений в этих полях.

Применение умных таблиц

Давайте вернемся к исходной таблице с данными, встанем в любую ячейки таблицы и в панели вкладок перейдем на Главная -> Стили -> Форматировать как таблицу:

Форматирование умной таблицы

Среди стилей оформления выбираем любой понравившийся (внешний вид на функциональности не влияет), далее выбираем диапазон для таблицы (по умолчанию Excel автоматически его определяет) и нажимаем OK:

Преобразование исходной таблицы в умную

Полученная умная таблица обладает большим спектром удобных свойств, но в данном случае нам интересны 2:

  • Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
  • Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.
Читайте так же:
Иерархический список в excel

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

Изменение диапазона источника данных

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

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

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

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

Три макета для отчета сводной таблицы.

Несмотря на то, что макет не отличается от остальных, табулярная форма выглядит проще всего для чтения, и это макет, к которому привыкли большинство людей, которые видели сводные таблицы.

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

Изменение макета сводной таблицы легко. Выполните следующие шаги:

Щелкните в любом месте сводной таблицы, чтобы активировать вкладку контекста инструмента сводной таблицы на ленте.

Выберите вкладку «Дизайн» на ленте.

Нажмите на значок макета отчета и выберите нужный макет из появившегося меню.

Изменение макета сводной таблицы.

Когда новая сводная таблица добавляется на лист она имеете макет и определенное форматирование. Форматирование сводной таблицы можно легко перенастроить под себя и сохранить, как стиль сводной таблицы. С макетом дела обстоят немного сложнее, если не использовать стандартный макет, то нужно постоянно производить рутинные действия по его настройке. На форуме пользователей Excel: «Excel UserVoice forum» возможность изменять макет по умолчанию была достаточно востребованной функцией.

Microsoft прислушалась к комьюнити и добавила возможность изменять макет по умолчанию для сводной таблицы. Нововведение доступно начиная с версии 1704 (сборка 8067.2102), которая доступна для участников программы Office Insider (медленное кольцо).

Настроить макет сводной таблицы можно через вкладку «Файл/ Параметры/ Данные/ Изменить макет по умолчанию»

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

Напомним, что ранее пользователь мог изменить стиль сводной таблиц по умолчанию.

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