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

         Здравствуй уважаемый, читатель!

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

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

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

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

       Возникает закономерный вопрос, где же применение сводной таблицы даст наибольший эффект:

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

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

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

1.  Каждый без исключения столбец обязан иметь собственный заголовок шапки;
2.  Все строки и столбики вы обязаны заполнить, пробелы должны отсутствовать.
3.  Для всех столбцов данных, должены быть определенные форматы ячеек, для тех данных, которые должны в них хранятся (пример, для поля “Дата” нужен формат календарной даты, а для поля “Контрагент” — формат текста и т.п.)
4. Значения в этих ячейках должны быть “единоличным”, это значит такими которые не делятся (к примеру, “Договор №23 от 03.09.2016 года” должен быть записан в 3 разных столбцах “Документ”, “Номер” и “Дата”, это позволит создавать гибкую и удобную систему). Также это возможно при помощи функции СЦЕПИТЬ.
5. Если вы ведете расходно-доходную табличку в которой кроме суммирования еще есть надобность отнимания, то и в базу первоначальных данных вводите данные которые уже изначально со знаком “-” и тогда в свёрнутом виде вы получите нужный вам результат;
6. Сама конструкция вашей сводной таблицы обязана иметь оптимальный вид.

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

         Как создается сводная таблица в Excel

      Для начала мы проверяем правильно сформирована ли наша таблица: все столбцы имеют правильное название, имеют соответствующий формат ячеек, кто текстовое, кто числовое ну т.д… Проверьте обязательно если хотите получить отличный результат. И если всё отлично приступаем:

                

     На панели управления выбираем вкладку «Вставка» и получаем на выбор 2 варианта создания вашей сводной таблицы:

1. Рекомендуемые сводные таблицы (этот пункт рекомендуется использовать начинающим, но не бойтесь, это ненадолго, уловите суть создания, попрактикуетесь и всё, будете работать по второму пункту).
2. Сводная таблица (используется при ручной настройке таблицы в основном используется опытными пользователями)

1.  Рекомендуемые сводные таблицы.

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

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

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

2. Сводная таблица (мастер сводных таблиц)

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

        В открывшимся окне мы выбираем несколько условий создания сводной таблицы – это диапазон нужных исходных данных и куда же следует запихнуть сводную табличку, толи рядышком на одном листе, ну или создать новый лист и на нём разместить. А поскольку курсор уже стоял на таблице,Excel быстро и автоматически определил структуру таблицы и подставил в графу диапазона. Нажимаем «ОК» и получаем:

                

    Как видите, был создан новый «Лист3» и вызван «Мастер создания сводных таблиц» ну или для друзей просто «Конструктор сводных таблиц», его вы собственно и видите в следующем скриншоте.

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

         Вот мы получили и наш первый результат, но он нас не устраивает так как у нас не суммируется количество фруктов которые были проданы, а значит, нам нужно с области «СТРОКИ» перетянуть заголовок столбца «Вес, кг» и у нас создаётся та конструкция сводной таблицы, которую мы хотим.

       Ну вот форма то та, конечно, но вот результат не тот, а именно поле «Вес, кг» собирает по критерию — количество значений, а нам надо суммировать, а значит подводим курсор мыши к области значений «ЗНАЧЕНИЕ» и на указаном поле «Количество по полю Вес, кг», нажимаем левую кнопку мыши вызывая контекстное меню. Нам нужно выбрать последний пункт «Параметр полей значений».

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

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

Но всё же стоит еще немножко потрудится, например,убрать ненужные поля «(пусто)», так как нам они ни к чему и портят интерьер созданной сводной таблицы Excel. Так что продолжим работу, учимся делать табличку красивей.

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

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

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

Указываем первым вычисляемым значением «Страна», вот и всё с 1 исходной таблицы данных мы получили 2 сводные таблицы нужных нам данных.

•        Еще стоить поговорить о том, что при манипуляциях со сводными таблицами, Excel дополнительно формирует новое меню в панеле управления для работы с данными таблиц:

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

1.          Но не стоит забывать что данные в нашей таблице самостоятельно не умеют обновляться, а это значит то что при изменении в исходных данных (для примера я просто убрал данные 2 строк) у нас изменился итог, а значится надо сообщить об этом нашей сводной таблице:
2.        Для этого, в меню «Работа со сводными таблицами» переходим в закладку «Анализ» и нажимаем кнопочку «Обновить» и все данные пересчитались, ввиду изменений, которые мы внесли в нашу исходную таблицу:

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

       Пример можно взять здесь.

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

Не забудьте поблагодарить автора!

Золото убило больше душ, чем железо – тел. В. Скотт

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/kak-sozdat-svodnuyu-tablicu-v-excel/

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

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

Начало работы

Первым делом нужно создать какую-нибудь таблицу. Желательно, чтобы там было несколько столбцов. При этом какая-то информация должна повторяться, поскольку только в этом случае можно будет сделать какой-нибудь анализ введенной информации.

Например, рассмотрим одни и те же финансовые расходы в разных месяцах.

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

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

1. Для начала ее необходимо полностью выделить.

1. Затем перейдите на вкладку «Вставка». Нажмите на иконку «Таблица». В появившемся меню выберите пункт «Сводная таблица».

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

1. Затем вас попросят указать, где именно будет происходить построение. Лучше выбрать пункт «На существующий лист», поскольку будет неудобно проводить анализ информации, когда всё разбросано на несколько листов. Затем необходимо указать диапазон. Для этого нужно кликнуть на иконку около поля для ввода.

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

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

1. Для завершения настроек нужно нажать на кнопку «OK».

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

1. На этом этапе необходимо указать, какое поле будет:
   1. столбцом;
   2. строкой;
   3. значением для анализа.

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

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

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

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

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

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

1. Если таблица вам не понравилась, можно попробовать построить ее немного по-другому. Для этого нужно поменять поля в областях построения.

1. Снова закрываем помощник для построения.

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

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

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

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

1. Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.

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

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

1. Можно выбрать то, что нравится больше всего.

1. Для вставки выбранного варианта достаточно нажать на кнопку «OK».

1. В итоге вы получите следующий результат.

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

Анализ

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

Рассмотрим каждую из них более детально.

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

• Нажав на кнопку, отмеченную на скриншоте, вы сможете сделать следующие действия:
• В окне параметров вы увидите много чего интересного.

Активное поле

При помощи этого инструмента можно сделать следующее:

1. Для начала нужно выделить какую-нибудь ячейку. Затем нажмите на кнопку «Активное поле». В появившемся меню кликните на пункт «Параметры поля».

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

1. Помимо этого, можно настроить числовой формат. Для этого нужно нажать на соответствующую кнопку.

1. В результате появится окно «Формат ячеек».

Здесь вы сможете указать, в каком именно виде нужно выводить результат анализа информации.

Группировать

Благодаря этому инструменту вы можете настроить группировку по выделенным значениям.

Вставить срез

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

1. Выделите какой-нибудь столбец. Затем нажмите на кнопку «Вставить срез».
2. В появившемся окне, в качестве примера, выберите одно из предложенных полей (в будущем вы можете выделять их в неограниченном количестве). После того как что-нибудь будет выбрано, сразу же активируется кнопка «OK». Нажмите на неё.

1. В результате появится небольшое окошко, которое можно перемещать куда угодно. В нем будут предложены все возможные уникальные значения, которые есть в данном поле. Благодаря этому инструменту вы сможете выводить сумму лишь за определенные месяцы (в данном случае). По умолчанию выводится информация за всё время.

1. Можно кликнуть на любой из пунктов. Сразу после этого в поле сумма изменятся все значения.

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

  Как отключить режим совместимости в Эксель

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

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

Вставить временную шкалу

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

1. В качестве примера создадим небольшую таблицу с различными датами.
2. Затем нужно будет построить сводную таблицу.

Снова переходим на вкладку «Вставка». Кликаем на иконку «Таблица». В появившемся подменю выбираем нужный нам вариант.

1. Затем нас попросят выбрать диапазон значений.

1. Для этого достаточно выделить всю таблицу целиком.

1. Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».

1. Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).

1. Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).

1. На этот раз никаких ошибок не будет. Вам предложат выбрать поле для сортировки. Поставьте галочку и нажмите на кнопку «OK».

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

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

Обновить

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

Источник данных

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

Для этого нужно нажать на иконку «Источник данных». Затем выбрать одноименный пункт меню.

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

Действия

При помощи этого инструмента вы сможете:

• очистить таблицу;
• выделить;
• переместить её.

Вычисления

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

1. К ним относятся:

• порядок вычислений (в списке отображаются добавленные формулы);

• вывести формулы (информации нет, так как нет добавленных формул).

Сервис

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

Показать

• При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.
• Благодаря этому вы сможете:

• настроить отображение боковой панели со списком полей;

• включить или выключить кнопки «плюс/мину»с;

• настроить отображение заголовков полей.

Конструктор

При работе со сводными таблицами помимо вкладки «Анализ» также появится еще одна – «Конструктор». Здесь вы сможете изменить внешний вид вашего объекта вплоть до неузнаваемости по сравнению с вариантом по умолчанию.

Можно настроить:

• промежуточные итоги:
  • не показывать;
  • показывать все итоги в нижней части;
  • показывать все итоги в заголовке.

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

• общие итоги:
  • отключить для строк и столбцов;
  • включить для строк и столбцов;
  • включить только для строк;
  • включить только для столбцов.

• макет отчета:
  • показать в сжатой форме;
  • показать в форме структуры;
  • показать в табличной форме;
  • повторять все подписи элементов;
  • не повторять подписи элементов.

• пустые строки:
  • вставить пустую строку после каждого элемента;
  • удалить пустую строку после каждого элемента.

• параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
  • заголовки строк;
  • заголовки столбцов;
  • чередующиеся строки;
  • чередующиеся столбцы.

• настроить стиль оформления элементов.

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

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

• Помимо этого, при желании, вы можете создать свой собственный стиль оформления.

Сортировка значений

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

Для этого нужно сделать следующее.

1. Кликните на треугольник около нужного поля.
2. В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

1. Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».

В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».

Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.

Сводные таблицы в Excel 2003

Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.

Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.

1. Перейти в раздел меню «Данные» и выбрать соответствующий пункт.

1. В результате этого появится мастер для созданий подобных объектов.

1. После нажатия на кнопку «Далее» откроется окно, в котором нужно указать диапазон ячеек. Затем снова нажимаем на «Далее».

1. Для завершения настроек жмем на «Готово».

1. В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.

1. К примеру, может получиться вот такой результат.

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

Заключение

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

Если данного самоучителя вам недостаточно, дополнительную информацию можно найти в онлайн справке компании Microsoft.

Видеоинструкция

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

Источник: https://os-helper.ru/excel/svodnye-tablicy.html

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

109593 18.11.2012 Скачать пример

Видео

Лирическое вступление или мотивация

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

В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):

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

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

• Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?  
• Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается? 
• Кто входит в пятерку наших самых крупных заказчиков? 

… и т.д.

Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить легче, чем Вы думаете. Нам потребуется один из самых ошеломляющих инструментов Microsof Excel — сводные таблицы.

Поехали…

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные — Сводная таблица (Data — PivotTable and PivotChartReport).

Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard).

Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего — «в списке или базе данных Microsoft Excel». Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel «понимает» практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант «в другой сводной таблице…» нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета — на Ваш вкус — только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

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

Шаг 3. Куда поместить сводную таблицу?

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист — тогда нет риска что сводная таблица «перехлестнется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному — этапу конструирования нашего отчета.

Работа с макетом

То, что Вы увидите далее, называется макетом (layout) сводной таблицы.

Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета.

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

Останется его только достойно отформатировать:

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

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

• Названия строк (Row labels)
• Названия столбцов (Column labels)
• Значения (Values) — раньше это была область элементов данных — тут происходят вычисления.
• Фильтр отчета (Report Filter) — раньше она называлась Страницы (Pages), смысл тот же.

Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) — минимален. 

P.S

Единственный относительный недостаток сводных таблиц — отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/8/130/

Как создать сводную таблицу в Excel: подробно об эффективном инструменте для вычислений

Вопросы, рассмотренные в материале:

• Что такое сводная таблица Excel?
• Как создать сводную таблицу Excel?
• Как пользоваться сводной таблицей Excel?
• Какие плюсы и минусы сводной таблицы Excel?
• Как сделать сводную таблицу Excel из нескольких таблиц?

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

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

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

Для чего нужна сводная таблица

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

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

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

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

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

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

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

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

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

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

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

Структура сводной таблицы

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

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

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

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

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

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

Задумываясь над тем, как создать сводную таблицу в Excel, ознакомьтесь с пошаговой инструкцией:

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

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

2. В разделе «Вставка» выберете значение «Сводная таблица».
3. Далее в категории «Выберите данные для анализа» поставьте галочку около «Выбрать таблицу или диапазон».
4. В поле Таблица или диапазон установите диапазон ячеек.
5. В пункте «Укажите, куда следует поместить отчет сводной таблицы» выберете «На новый лист». В этом случае таблица откроется в новом документе. Вариант «На существующий лист» предполагает, что месторасположение таблицы будет в ранее созданном документе.
6. «ОК».

Настройка сводной таблицы:

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

Примечание. Поля добавляются в области следующим образом: буквенные значения – в область строк, значения даты и времени – в область столбцов, числовые данные – в область значений.

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

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

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

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

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

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

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

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

1. Использование фильтров в сводной таблице.

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

1. Обновление сводной таблицы.

Со временем возникает необходимость вносить изменения в исходные данные. Нажмите правую кнопку мышки и выберете «Обновить».

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

Нажмите левую кнопку мыши и пройдите в раздел «Работа со сводными таблицами», далее «Анализ», затем «Источник данных».

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

Кеш сводной таблицы Excel 2016: достоинства и недостатки

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

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

В версиях Excel, вышедших до 2016 года, при создании сводной таблицы программа предлагала использовать данные, сохраненные в кеш-памяти. В Excel 2016 эта функция уже отсутствует, поэтому, когда создается новая сводная таблица, программа делает для нее отдельную область кеша. Из-за этого происходит перегрузка оперативной памяти компьютера и существенно вырастает объем файла.

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

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

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

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

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

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

Лучший способ отработать навык создания таблиц – открыть Excel и начать действовать.

Источник: https://rdv-it.ru/news/sozdat-svodnuyu-tablitsu-excel/