Excel как сводную таблицу сделать обычной

Доброго времени суток, дорогие форумчане.

Столкнулся недавно с проблемой:

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

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

_сводные_

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

После создания сводной таблицы и настройки ее структуры выделите всю таблицу и скопируйте ее в буфер обмена. Далее перейдите на вкладку Главная (Ноте) и щелкните на кнопке Вставка (Insert), а затем выберите в раскрывающемся меню команду Вставить значения (Insert Values), как показано на рис. 6.19.

Тем самым вы удалите сводную таблицу и замените ее статическими значениями, полученными на основе последнего состояния сводной таблицы. Полученные значения становятся основой создаваемой впоследствии сводной таблицы.

Рис 6.19. Команда Вставить значения применяется для получения на основе сводной таблицы обычной таблицы со статическими значениями

Эта методика эффективно применяется для удаления интерактивных возможностей сводной таблицы. Таким образом вы преобразуете сводную таблицу в стандартную таблицу, а потому создаете не сводную диаграмму, а обычную диаграмму, не поддающуюся фильтрации и переорганизации. Это же замечание касается также методов 2 и 3.

excel как сводную таблицу сделать обычной

Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.

Создание сводной Эксель таблицы требует соблюдения определенных условий:

  1. Данные вписываются в таблицу, где есть столбцы и списки с названиями.
  2. Отсутствие незаполненных форм.
  3. Отсутствие скрытых объектов.

Как сделать сводную таблицу в excel: пошаговая инструкция

excel как сводную таблицу сделать обычной

Для создания сводной таблицы необходимо:

Создался пустой лист, где видно списки областей и полей. Заголовки стали полями в нашей новой таблице. Сводная таблица будет формироваться путем перетаскивания полей.

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

Числовой формат мы выбираем для следующего окна и отмечаем «Разделитель групп разрядов». Подтверждаем кнопкой «ОК».

Оформление сводной таблицы

Если мы поставим галочку, которая подтверждает выделение сразу нескольких объектов, то сможем обрабатывать данные сразу по нескольким продавцам. Применение фильтра возможно для столбцов и строк. Поставив галочку на одной из разновидностей товара, можно узнать, сколько его реализовано одним или несколькими продавцами.

  • Выделение ячеек в сводной таблице приведет к появлению такой вкладки как «Работа со сводными таблицами», а в ней будут еще две вкладки «Параметры» и «Конструктор».

Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений». Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор. Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук. Теперь меняем таблицу и делаем так, чтобы фильтр срабатывал по месяцам. Поле «Дата» мы переносим в «Фильтр отчета», а там где «Названия столбцов», будет «Продавец». Таблица  отображает весь период продаж или за конкретный месяц.

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

Надеюсь, вы усвоили весь материал, и теперь знаете, как сделать сводную таблицу в excel.

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

Можно сформировать новые итоги по исходным параметрам, поменяв строки и столбцы местами. Можно произвести фильтрацию данных, показав разные элементы. А также наглядно детализировать область.

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

Для примера используем таблицу реализации товара в разных торговых филиалах.

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

Самое рациональное решение – это создание сводной таблицы в Excel:

  1. Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
  2. В меню «Вставка» выбираем «Сводная таблица».
  3. Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
  4. Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

  • Первая строка заданного для сведения данных диапазона должна быть заполнена.
  • В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
  • В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.

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

Часто требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Нужно объединить их в одну общую. Для науки придумаем остатки на складах в двух магазинах.

Порядок создания сводной таблицы из нескольких листов такой же.

Создадим отчет с помощью мастера сводных таблиц:

  1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
  2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
  3. Следующий этап – «создать поля». «Далее».
  4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
  5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
  6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

Как работать со сводными таблицами в Excel

Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).

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

Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

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

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

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

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

Читайте также:  Как сделать огибание текста в powerpoint?

Проверка правильности выставленных коммунальных счетов

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

  1. Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:
  2. = тариф * количество человек / показания счетчика / площадь
  3. Для удобства рекомендуем сделать промежуточный столбец, в который будут заноситься показания по счетчикам (переменная составляющая).
  4. Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.
  5. Скачать все примеры сводной таблицы

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

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

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

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

На рисунке показан принцип, который я описал. Т.е. в диапазоне A2:E5 находится исходная сводная таблица, которая преобразуется в список данных (диапазон H2:J14). Вторая таблица представляет тот же набор данных, только в другом ракурсе.

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

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

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

Перейдите по вкладке Файл -> Параметры. В появившемся диалоговом окне Параметры Excel, во вкладке Панель быстрого доступа в левом поле найдите пункт Мастер сводных таблиц и диаграмм и добавьте его в правый. Нажмите ОК.

Теперь на панели быстрого доступа у вас появился новый значок.

Щелкните по этой вкладке, чтобы запустить Мастер сводных таблиц.

На первом шаге мастера необходимо выбрать тип источника данных сводной таблицы. Устанавливаем переключатель В нескольких диапазонах консолидации и жмем Далее.

На шаге 2а укажите, как следует создавать поля страницы. Поместите переключатель Создать поля страницы -> Далее.

На шаге 2б, в поле Диапазон выберите диапазон, содержащий данные, и щелкните Добавить. В нашем случае это будет местоположение исходной сводной таблицы A1:E4.

На третьем шаге необходимо определиться, куда необходимо поместить сводную таблицу, и нажмите кнопку Готово.

Excel создаст сводную таблицу с данными. В левой части экрана вы увидите область Список полей сводной таблицы. Уберите все пункты с полей строк и столбцов. Более подробно о редактировании полей строк и столбцов в сводной таблице я писал в предыдущей статье.

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

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

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

Источник: http://word-office.ru/excel-kak-svodnuyu-tablicu-sdelat-obychnoy.html

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

Приветствую вас, посетители блога!

При аналитике различных рекламных инструментов необходима простота и наглядность отчетов. Смотреть на сухие цифры не очень-то и хочется, да и глаза разбегаются от их большого количества. Сегодня вы узнаете, как создать сводную таблицу в Excel. Это поможет вам составить наглядные отчеты по эффективности рекламных кампаний в Яндекс.Директ или Google Adwords.

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

Еще кое-что: статья написана не только для тех, кто занимается интернет-маркетингом, но и для людей, занимающимся другими сферами деятельности. Поэтому этот урок я разделю на две части:

  1. Выгрузка отчета из Метрики;
  2. Создание сводной таблицы;

Тем, кто не интересуется интернет-маркетингом первую часть можно не читать!

Выгрузка отчета из Метрики

Итак. Давайте выгрузим отчет о расходах рекламных кампаний Директ из Яндекс.Метрики в XLSX-файл. Для этого перейдите в отчет “Директ-расходы”: excel как сводную таблицу сделать обычной

Затем нажмите на небольшую кнопку “Экспорт” в правом верхнем углу отчета и выберите “XLSX” из графы “Данные из таблицы”: excel как сводную таблицу сделать обычной

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

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

Прежде, чем мы начнем создавать сводную таблицу, необходимо подготовить исходную таблицу. Что под этим подразумевается:

  • Убираем все объединенные ячейки;
  • Убираем пустые ячейки;

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

Еще раз перепроверьте табличку. Наличие пустых и объединенных ячеек неприемлемо для сводной таблицы.

Ну-с начнем создавать:

  1. Перейдите во вкладку “Вставка”: excel как сводную таблицу сделать обычной
  2. Во вкладке “Вставка” найдите кнопку “Сводная таблица” (крайняя с лева). Нажмите на нее: excel как сводную таблицу сделать обычной
  3. В появившемся окошке укажите куда поместить отчет: на новый лист или на существующий. Нажмите “ОК”: excel как сводную таблицу сделать обычной

И вот перед вами конструктор сводной таблицы. Наша задача – перетащите все необходимые данные таблицы в определенные поля сводной таблицы. Делается это в области с права: excel как сводную таблицу сделать обычной

Как видите здесь четыре области:

  • Фильтр отчета. Предназначается для фильтрации данных;
  • Названия строк. Сюда переносятся анализируемые поля. Например, рекламная кампания, объявление и ключевая фраза;
  • Названия столбцов. Здесь указываются значения. Подставлять ничего не нужно, данные из поля значения перенесутся автоматически;
  • Значения. Сюда переносятся данные, с помощью которых анализируются поля, перенесенные в область “Названия строк”;
  • Кажется доходчиво объяснил.
  • Итак, вот пример моей сводной таблицы, где я просматриваю расходы, переходы по каждой кампании, по каждому объявлению, по каждой фразе: excel как сводную таблицу сделать обычной
  • В области “Названия строк” сохранена изначальная иерархия, то есть сперва кампании, потом объявления, затем ключевые фразы. Благодаря этому сводная таблица принимает следующий вид: excel как сводную таблицу сделать обычной
  • Как видите, все данные вложены по порядку. А вся таблица выглядит так: excel как сводную таблицу сделать обычной
  • Как создать сводную таблицу в Excel вы теперь знаете, осталось только сделать ее восприятие проще.

Упрощаем восприятие данных таблицы

Отчет имеет не очень-то и красивое оформление и приходиться много крутить глазами, чтобы четко разобрать цифры. Для более лучшего восприятия оформим таблицу так: 

Сделаем это следующим образом:

  1. Во вкладке “Конструктор” выберите любой, понравившийся вам, стиль оформления таблицы: 
  2. Затем переименуйте столбцы. Дважды щелкните по столбцу и дайте название;
  3. Присвойте денежный формат столбцу с расходами. Нажмите правой кнопкой мыши на ячейке с данными о расходах и выберите “Числовой формат”, затем в списке с лева найдите пункт “Денежный формат”: 
  4. Указываем гистограмму в каждой ячейке столбца “Расходы”. Выделите ячейки столбца “Расходы” и на вкладке “Главная” найдите кнопку “Условную форматирование”, нажмите на нее. В выпадающем списке выберите пункт “Гистограмма”: 

Результат: 

Теперь вы наглядно видите на какую кампанию больше всего тратите средств. В данном примере на кампанию “Котлы – Поиск” больше всего тратится денежных средств. Это может означать только одно – необходимо оптимизировать цену клика посредством увеличения CTR. Этот процесс называется ведение рекламных кампаний.

Ну а на этом все! Думаю, что на вопрос “Как создать сводную таблицу в Excel?” я ответил.

До свидания!

Не забывайте подписываться и оставлять комментарии к уроку.

Источник: https://context-up.ru/kak-sozdat-svodnuy-tablicu-v-excel/

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

Как скопировать сводную таблицу

Совсем недавно один из читателей обратился ко мне с вопросом: «А как скопировать сводную таблицу с одного листа на другой»? Решил осветить этот вопрос здесь, потому что слышу его не впервые. Сводная таблица – это не обычный диапазон данных, это область ячеек с результатами работы инструмента. Поэтому, просто скопировать сводную таблицу в другое место нельзя.

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

  1. Выделите нужный диапазон данных в сводной таблице
  2. Скопируйте его одним из известных способов. Например, нажмите Ctrl+C на клавиатуре
  3. Установите курсор в ячейку, где должен располагаться верхний левый угол вставляемого диапазона
  4. Выполните на ленте Главная – Буфер обмена – Вставить – Значения.
Читайте также:  Как сделать красивый фон в word?

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

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

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

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

Представим, что по какой-то причине нас заинтересовали подневные продажи метизов у Романа. Выделим ячейку на пересечении строки «Роман» и столбца «Метизы». Жмем на нем правой кнопкой мыши и выбираем «Показать детали». Программа создаст новый лист и отобразит на нем выборку из исходной таблицы, т.е. все продажи Романа метизов.

А можно просто сделать двойной клик по нужной величине. Это альтернативный, и более простой способ.

excel как сводную таблицу сделать обычной

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

excel как сводную таблицу сделать обычной

Дополнительные вычисления в сводной таблице

Если стандартных функций вычисления в сводной таблице (сумма, количество, отклонение и т.п.) Вам оказалось мало, инструмент имеет дополнительные вычислительные возможности. Прежде всего, нажмите правой кнопкой мыши в любой ячейке столбца с вычислениями и выберите «Дополнительные вычисления». В списке вычислений будет много интересных вариантов. К примеру.

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

Посмотрите, в 2 клика мы перешли от натуральных величин к процентам, теперь легче сравнивать показатели, или скрыть их, когда не рекомендуется отражать в отчете конкретные цифры в денежных единицах.

excel как сводную таблицу сделать обычной

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

Вычисляемые поля и объекты

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

Вычисляемые поля

Эти объекты нужны, чтобы вставить в таблицу новые столбцы без вставки их в исходный массив данных. К примеру, у нас есть сумма продаж менеджеров и количество чеков. Рассчитаем в отдельном столбце средний чек.

Выполняем такую последовательность действий:

  1. Установим курсор в одну из ячеек, содержащих значения
  2. На ленте нажимаем: Работа со сводными таблицами – Анализ – Поля, элементы, наборы – Вычисляемое поле
  3. В открывшемся окне в поле «Имя» запишем «Средний чек»
  4. Теперь вводим формулу, нам нужно поделить сумму продаж на количество чеков. Всписке полей дважды кликнем на «Сумма продаж», пишем на клавиатуре знак деления «/» и дважды щелкаем на «Количество чеков. Должна получиться такая формула:

excel как сводную таблицу сделать обычной

  1. Жмем Ок и смотрим, что получилось.

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

excel как сводную таблицу сделать обычной

Вычисляемые объекты

Похожий функционал предоставляют вычисляемые объекты. Но они вставляют в таблицу не столбцы, а строки. К примеру, у нас есть сумма продаж менеджеров, а нас интересует сколько составит НДС (18%) от этих продаж и общая сумма с НДС. Создаем вычисляемый объект:

  1. Ставим курсор в любую строчку первого столбца или любой столбец первой строки
  2. Жмем на ленте: Работа со сводными таблицами – Анализ – Поля, элементы, наборы – Вычисляемый объект. Откроется окно вставки:

excel как сводную таблицу сделать обычной

  1. В поле «Имя» запишем «НДС», в списке «Поля» выбираем «Менеджер»
  2. Кликая дважды на имя каждого менеджера, запишем формулу: =(Алексей+Анна +Виктор +Виктория +Виталий +Денис +Егор +Роман +Светлана)*0,18
  3. Удалим ненужные поля, жмем Ок. Получаем еще одно поле, в котором будет посчитана НДС. Значение в этом поле будет добавлено к общей сумме.

excel как сводную таблицу сделать обычной

Группировка данных в сводной таблице

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

Группировка с шагом

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

  1. Создаем сводную таблицу, где в строках будут дни, а в значениях – продажи. Если Вы не знаете, как создать сводную таблицу – сначала прочтите предыдущий пост;
  2. Кликните правой кнопкой мыши по любой из дат в сводной таблице и в контекстном меню выберите «Группировать». Откроется окно настройки группировки;

excel как сводную таблицу сделать обычной

  1. В полях «Начиная с» и «по» автоматически установятся минимальная и максимальная даты в списке. Можете, при необходимости, указать здесь более узкий период для группировки
  2. В списке «С шагом» выберите эталонный интервал времени. У нас это «Месяцы». Можно выбрать сразу несколько пунктов в этом списке. Давайте попробуем построить по кварталам и месяцам, отмечаем их;
  3. Жмем «Ок» и сразу же получаем результат. Взгляните, что получилось:

excel как сводную таблицу сделать обычной

Таким же образом можно группировать обычные числовые данные. Например, мы хотим сгруппировать ежедневные продажи с шагом 1000 и узнать, в каком из интервалов было больше всего чеков. Делаем так:

  1. Строим сводную таблицу, в строках – суммы продаж, в значениях – количество чеков. Сначала у нас получится длинная и бесполезная таблица.
  1. Кликнем правой кнопкой в любо строке первого столбца (суммы продаж) и выберем «Группировать». В открывшемся окне задаем минимальное и максимальное числа для группировки, а так же, шаг. У нас это 1000. Вместо огромной таблицы, получаем компактную, из десяти строк. В каждой строке – интервал сумм и количество чеков в этом интервале.

excel как сводную таблицу сделать обычной

  1. Кликнем правой кнопкой по любой ячейке в столбце «Количество чеков» и выберем Сортировка – по убыванию;
  2. Можно для наглядности выразить результат в процентах. Кликнем по той же ячейке и выберем Дополнительные вычисления — % от суммы по столбцу. Вот и все, задача решена, нам прекрасно видно в каких интервалах сумм было больше всего чеков.

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

Так же, можно группировать записи вручную. Для этого выделите нужные данные и нажмите Работа со сводными таблицами – Анализ – Группировать – Группировка по выделенному.

Кстати, чтобы отменить группировку – кликните правой кнопкой мыши по группированному столбцу и выберите «Разгруппировать».

Фильтрация сводных таблиц с помощью срезов

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

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

А выглядит это вот так:

Вы видите несколько окон на рабочем листе с перечисленными в них данными, а так же, сводную таблицу, содержащую полный набор данных. Но что если кому-то нужно посмотреть продажи метизов у Романа 1 апреля 2016 года? Кликаем в окнах на кнопки:

  1. В окне «Дата» ищем и выбираем 01.04.2016;
  2. В окне «Менеджер» выбираем «Роман»;
  3. В окне «Группа товара» кликаем «Метизы»

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

По-моему, отлично! Так вот, чтобы включить срезы в сводных таблицах – выделите любую ячейку этой таблицы и выполните на ленте Работа со сводными таблицами – Анализ – Фильтр – Вставить срез.

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

Выбирайте, жмите Ок и все, заработало!

Аналогично работает временная шкала. Этот инструмент очень похож на срезы, но управляет полями, в которых содержатся даты. Чтобы добавить временную шкалу – нажмите Работа со сводными таблицами – Анализ – Фильтр – Вставить временную шкалу. После простых настроек появится окно фильтрации дат, которое позволяет эффективно и быстро ограничивать периоды дат, выводимые в отчет.

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

Читайте также:  Формулы в excel как сделать

Источник: https://officelegko.com/2017/03/16/svodnaya-tablitsa-excel-ch-2-rasshiryaem-navyiki/

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

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

Файлы для скачивания:

ФайлОписаниеРазмер файла:Скачивания
Пример 69 Кб 1448

Итак, имеем такую таблицу, хотя рука не поднимается назвать это таблицей:

excel как сводную таблицу сделать обычной

Для преобразования данной таблицы нам потребуется надстройка ЁXCEL.

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

excel как сводную таблицу сделать обычной

Далее нам необходимо заполнить пустые строки в столбцах «Месяц» и «Менеджер». Для этого возвращаемся в начало таблицы, выделяем первые ячейки с данными в этих столбцах, в нашем случае это ячейки «А3:В3«. В главном меню заходим во вкладку ЁXCEL и нажимаем кнопку «Таблицы», в выпавшем списке выбираем команду «Заполнить пустоты»:

excel как сводную таблицу сделать обычной

В открывшемся диалоговом окне нажимаем «ОК»:

excel как сводную таблицу сделать обычной

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

excel как сводную таблицу сделать обычной

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

excel как сводную таблицу сделать обычной

Сбрасываем фильтр по столбцу «А«. Повторяем операцию для столбца «В«:

excel как сводную таблицу сделать обычной

Сбрасываем фильтр по столбцу «В» и выделяем всю таблицу. В главном меню во вкладке ЁXCEL нажимаем кнопку «Таблицы» в выпавшем меню выбираем команду «Трансформировать таблицу в массив»:

excel как сводную таблицу сделать обычной

В открывшемся окне мастера нажимаем кнопку «Далее»:

excel как сводную таблицу сделать обычной

  • В следующей вкладке мастера, так же нажимаем кнопку «Далее»:
  • В следующей вкладке мастера, отвечаем на вопрос «Транспонировать данные ряда?«, в нашем случае выбираем ответ «Нет» и нажимаем кнопку «Далее»:
  • В следующей вкладке мастера, отвечаем на вопрос «Исключить пустые строки?«, в нашем случае выбираем ответ «Да» и нажимаем кнопку «Трансформировать»:
  • Ждем. Получаем вот такой массив:
  • Встаем в ячейку «F1» и пишем название столбца «Наименование»:
  • Встаем в ячейку «А1» и строим сводную таблицу (Как построить сводную таблицу?):

Источник: https://e-xcel.ru/index.php/svodnye-tablitsy/kak-bystro-preobrazovat-tablitsu-v-massiv-dlya-svodnoj-tablitsy

Введение в сводные таблицы

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

  • Кто-то вам сказал, что это нечто, сравнимое по сложности понимания с Общей теорией относительности А.Эйнштейна, и, что лучше, наверное, держаться от этого подальше, чтобы не подрывать свою «Великую веру в себя».
  • Вам наплевать на Excel вообще и на сводные таблицы в частности, но завтра у вас собеседование на хорошую позицию в хорошем месте и надо произвести неизгладимое впечатление своей осведомленностью по поводу этой сакральной возможности, которая почему-то так люба работодателям.
  • Вы лишь начинаете осваивать Excel и только-только созрели для этого нового чудного мира, который откроет перед вами знание сводных таблиц. Я так и знал, что это именно ваш пункт!

▲ вверх

Наша задача

Моя задача, как можно более кратко, просто, доходчиво и не скучно рассказать про сводные таблицы (pivot tables, пивотные таблицы).

Я надеюсь, что по прочтении этого, вы, как минимум, перестанете их бояться и приступите к смелым экспериментам над своими данными, потому что сводные таблицы, это как раз то, что можно осваивать методом тыка. То, что функционал сводных таблиц сложен для понимания — это не более чем миф.

Сводные таблицы — настолько же интуитивно-понятный инструмент, насколько и мощный. Всё, что необходимо — это знать чуть-чуть теории и разбираться в 2-3 окнах настройки. Вперёд!

▲ вверх

Учебная ситуация

Представьте, что вы владелец четырёх торговых точек по продаже цветов. У вас по точке на севере, юге, западе и востоке вашего городка. На вас работают 8 продавщиц.

Вы продаёте 5 видов цветов: розы, гвоздики, тюльпаны, лилии и хризантемы.

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

▲ вверх

Журнал продаж цветов

Скачать

▲ вверх

5 шагов

Внимательно вглядитесь в журнал учёта продаж цветов.

excel как сводную таблицу сделать обычной

  • Имеем 5 столбцов
  • 3 из них содержат текстовую информацию: точка, имя продавца, тип цветов
  • 1 столбец содержит даты продаж
  • 1 столбец содержит числа — стоимость проданных цветов

Обратите внимание, что один из столбцов содержит числовую информацию, которую можно суммировать. Теперь следите за руками:

  1. Шаг 1. Скроем столбцы A:D, оставим только столбец E — Стоимость. В ячейку E1002 введём формулу суммы, а строки 2:1001 скроем. Получится вот это: excel как сводную таблицу сделать обычной Таким образом мы узнали? что за весь год, всеми точками, всеми продавцами, всех видов цветов продано на 1063100 рублей. Мы пока не говорим ни о каких сводных таблицах, а просто пытаемся анализировать обычную таблицу.

  2. Шаг 2. Будучи владельцем бизнеса, вы конечно же не остановитесь на шаге 1, узнав лишь общую сумму. Наверняка вам захочется увидать СЛАГАЕМЫЕ этой суммы, то есть её СТРУКТУРУ.

    OK! Давайте посмотрим, как продавали точки в сравнении друг с другом! Для этого мы опять скроем всё лишнее, оставив только столбцы Точка и Стоимость, и при помощи формул СУММЕСЛИ относительно легко получим такую статистику: excel как сводную таблицу сделать обычной Посмотрите те ка, на южной стороне города мужчины чаще дарят женщинам цветы!

  3. Шаг 3. А кто у нас передовик капиталистического труда? А вот кто: excel как сводную таблицу сделать обычной

  4. Шаг 4. А какие цветы приносят больше денег? Посмотрим: excel как сводную таблицу сделать обычной

  5. Шаг 5. А в каком месяце максимальные продажи? Странная картина, ну да ладно… excel как сводную таблицу сделать обычной

Утомительно, не правда ли? И заметьте, что действия ТИПОВЫЕ! Так вот, для того, чтобы НИКОГДА БОЛЬШЕ не заниматься этими пятью позорными шагами, существуют сводные таблицы.

▲ вверх

Измерения

Я надеюсь, что вы заметили, что в 4-х из 5-ти шагов мы работали со столбцами, по которым ГРУППИРОВАЛИ текстовые данные (или даты) и суммировали стоимость цветов.

Такие столбцы, по которым можно группировать строки давайте называть ИЗМЕРЕНИЯМИ. Наш журнал продаж имеет 4 измерения: торговая точка, имя продавца, вид цветов и дата продажи.

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

▲ вверх

Создаём сводную таблицу

Давайте создадим сводную таблицу, благо дело это крайне простое:

  • Встаньте на любую ячейку нашей таблицы на листе Журнал_продаж
  • На ленте ВСТАВКА нажмите крайнюю левую кнопку Сводная таблица
  • Нажмите в возникшем диалоговом окне OK. В этом диалоге можно изменить диапазон нашей таблицы, который Excel отлично определяет самостоятельно, а также можно указать, куда вставлять сводную таблицу — рекомендую выбирать всегда отдельный новый лист, что и соответствует настройке по умолчанию.

▲ вверх

Момент истины

▲ вверх

Теперь по порядку..

excel как сводную таблицу сделать обычной Итак, что мы видим на главном экране управления сводной таблицой:

  • Секция Поля сводной таблицы — содержит не что иное, как заголовки столбцов нашей исходной таблицы. Поле или измерение — суть синонимы. Термин «поле» пришёл к нам из баз данных. В сводных таблицах операции с полями означают действия со всеми строками данного столбца, из чего, собственно, и проистекает вся мощь этого инструмента. excel как сводную таблицу сделать обычной
  • Раздел СТРОКИ — сюда перетаскивают поля, сгруппированные значения которых должны располагаться по вертикали. Пример: excel как сводную таблицу сделать обычной
  • Раздел КОЛОННЫ — сюда перетаскивают поля, значения которых должны располагаться горизонтально. Пример:
  • Раздел ЗНАЧЕНИЯ — сюда перетаскивают поля, которые можно суммировать. Суммировать можно только числовые поля, а вот подсчитать количество строк можно по любому полю, поэтому, если вы в данный раздел перетащите какое-либо поле измерения (например, Продавец), то Excel автоматически поймёт, что его надо подсчитывать (count), а не суммировать (sum).
  • Раздел ФИЛЬТРЫ — сюда перетаскиваем поля, по которым не надо группировать значения столбцов и суммировать, но надо отфильтровать строки, чтобы в дальнейшей группировке и суммировании участвовали только строки, удовлетворяющие условию на значение фильтра. Посмотрите на примере:

▲ вверх

Раз пошла такая пьянка..

А что это мы используем только одно измерение? Как насчёт двух?

или так…

А чего это у нас таблица такая блёклая? Добавим красок и ещё усложним:

  • Двойная группировка по дате в колоннах(квартал + месяц)
  • Тройная группировка по точке, продавцу и цветам в строках

Не хотите попробовать сделать такое формулами? А сводными таблицами это делается за 5 секунд!

А тут мы находим среднюю стоимость букета по продавщицам. Схема объясняет некоторые нюансы настройки.

▲ вверх

Выводы

  1. Сводные таблицы — самый мощный и востребованный инструмент в Excel.

  2. Анализировать данные со сводными таблицами в десятки раз быстрее и проще, чем без оных.

  3. Миф о сложности освоения этого инструмента — всего лишь миф.

  4. Для первого раза и дальнейших самостоятельных экспериментов вы узнали достаточно! Не поленитесь — закрепите полученные знания на практике.

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

▲ вверх

Источник: http://perfect-excel.ru/publ/excel/svodnye_tablicy/vvedenie_v_svodnye_tablicy/5-1-0-40

Ссылка на основную публикацию
Adblock
detector