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

Хотя, мы будем рассматривать процесс создания сводной таблицы на примере программы Microsoft Excel 2010, но данный алгоритм применим и для других современных версий этого приложения.

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

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

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

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

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

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

После этого, на новом листе открывается форма создания сводной таблицы.

Как видим, в правой части окна расположен список полей таблицы, а ниже четыре области:

1. Названия строк;
2. Названия столбцов;
3. Значения;
4. Фильтр отчёта.

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

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

Получилась вот такая сводная таблица. Над таблицей отображаются фильтры по полу и дате.

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

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

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

После этого, сводная таблица приобрела такой вид.

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

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

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

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

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

Теперь, наша сводная таблица приобрела презентабельный вид.

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

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

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

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

После этого, открывается мастер сводных таблиц. Как видим, мы имеем четыре варианта источника данных, откуда будет формироваться сводная таблица:

• в списке или в базе данных Microsoft Excel;
• во внешнем источнике данных (другой файл);
• в нескольких диапазонах консолидации;
• в другой сводной таблице или в сводной диаграмме.

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

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

Затем, Мастер сводных таблиц предлагает выбрать место, где будет размещаться новая таблица на этом же листе или на новом. Делаем выбор, и жмем на кнопку «Готово».

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

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

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

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

2.2.6. Анализ деловых данных в Excel 2007

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

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

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

Рис. 1.

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

Рис. 2.

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

Рис. 3.

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

Рис. 4.

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

Например, если выбрать поля: № склада, Наименование, Цена (грн.) и перетащить их в соответствующие области: «Название столбцов», «Название строк» и «Значения», то в правой части они будут отображаться в этих областях. При этом в левой части рабочего листа будет построена сводная таблица или отчет (рис. 5).

Рис. 5.

Следует отметить, что в области «Значения» выполняются какие-либо математические вычисления, например, суммирование (Сумма по полю Цена). Чтобы изменить тип вычислений, надо в области «Значения» щелкнуть левой кнопкой мыши по полю «Сумма по полю Цена» и в открывшемся меню выбрать команду «Параметры полей значений», затем в окне диалога «Параметры поля значений» выбрать требуемую функцию и щелкнуть на кнопке ОК.

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

Средства Excel для анализа данных и решения задач оптимизации

Мощными средствами анализа данных Excel 2007 являются:

• анализ «что – если», к которым относятся: подбор параметров и диспетчер сценариев;
• надстройка «Поиск решения» (надстройка Solver).

Средства анализ «что – если» помещены на вкладке «Данные» в группе «Работа с данными», а «Поиск решений» на вкладке «Данные» в группе «Analysis».

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

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

Средства анализа данных и решения задач оптимизации Excel 2007 аналогичны средствам Excel 2003. Работа со средствами Excel 2003 для анализа данных и решения задач оптимизации: подбор параметров, диспетчер сценариев и надстройка Solver подробно изложены на страничке: Решение задач оптимизации в Excel.

Далее …>>> Тема: 2.2.7. Проверочные средства и настройка Microsoft Excel 2007

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Анализ

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

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

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

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

• изменить имя;

• вызвать окно настроек.

В окне параметров вы увидите много чего интересного.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обновить

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

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

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

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

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

Действия

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

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

Вычисления

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

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

• вычисляемое поле;

• вычисляемый объект;

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

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

Сервис

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

Показать

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

Благодаря этому вы сможете:

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

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

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

Конструктор

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

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

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

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

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

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

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

Покажем, к примеру, количество проданного товара.

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



Изменение структуры отчета

Добавим в сводную таблицу новые поля:

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

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

После изменения диапазона в сводке появилось поле «Продажи».

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

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

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

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

1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.
2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.
3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.
4. Жмем ОК. Появились Остатки.

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

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

Как создавать таблицы в Excel 2003, 2007, 2010, 2013? Пошаговые инструкции и видео

1. Интерфейс и управление Microsoft Excel
2. Как вводить и редактировать данные?
3. Как создать таблицу Excel 2003?
4. Как создавать таблицы в Excel 2007?
5. Как создавать таблицы в Excel 2010?
6. Как создать таблицу в Excel 2013?

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

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

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

Как вводить и редактировать данные?

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

После того, как вы запустили приложение, происходит автоматическое создание новой книги, которая будет называться «Книга1». В каждой рабочей книге по умолчанию есть три листа: «Лист1», «Лист2», «Лист3».

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

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

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

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

1. Теперь следует из вкладки «Данные» выбрать «Список».

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

1. На последнем этапе следует указать, есть ли в таблице заголовки столбцов. Если есть, то нужно поставить галочку напротив «Таблица с заголовками». Если же нет, то программа по умолчанию присвоит им имена: «Столбец 1», «Столбец 2», и т. д. В этом случае, при желании, название столбца можно изменить, нажав на него и задав название в поле формулы.

Как создавать таблицы в Excel 2007?

Панель инструментов версии 2007 имеет следующий вид:

Видео, как создавать таблицы в Excel 2007

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

1. Активируйте ячейку и нажмите «Вставка — сводная таблица». Отображается диапазон данных. Разместите сводную таблицу на новом листе и кликните «ОК»
2. В открывшемся новом листе Excel можно увидеть в меню новые пункты: «Конструктор» и «Параметры». Справа появится окно с названиями столбцов таблицы, а снизу поля: «Названия столбцов», «Названия строк», «Фильтр отчета», «Значения».
3. Проставьте галочки в нужных вам столбцах. К примеру, вас интересуют данные по продажам каждого менеджера, поэтому ставьте галочки на строках «Продано» и «Менеджер». Эти названия отобразятся следующим образом: «Менеджер» окажется в области «Названия строк», а «Продано» — в «Область значений». Рядом можно будет увидеть готовый отчет.

1. Заголовку сводной таблицы можно присваивать другие имена. К примеру, выделите «Названия строк», а в строке формул напишите «Менеджер», в результате чего заголовок переименуется.
2. Данные сводной таблицы можно фильтровать. Для этого поставьте галочку на строке «Склад». Перетаскиваете строку из «Названия строк» в «Фильтр отчета», в результате чего вверху сводной таблицы отобразится строка «Склад», где можно будет выбрать необходимые параметры.

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

Как создавать таблицы в Excel 2010?

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

Пошаговая инструкция, как создать таблицу в Excel:

1. Введите данные в таблицу и получаем такой её вид:

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

1. Чтобы исправить ситуацию, подведите курсор к границе столбцов «A» и «B» в той строке, где отображены не поместившиеся в ячейку названия. Удерживая левую кнопку мыши, перетащите появившуюся пунктирную линию вправо до тех пор, пока столбец расширится до нужного размера и все названия поместятся в рамках ячеек.
2. Это же самое действие проделывайте, в случае необходимости, и со строкой. Также регулировать высоту и ширину ячейки можно, выбрав «Формат» в группе «Ячейки» на вкладке «Главная». Используя команды «Высота строки» и «Ширина столбца» задайте необходимые параметры вручную.
3. Теперь данные таблицы необходимо выровнять по центру. Обратите внимание, что на вкладке «Главная» есть группа «Выравнивание». Выделите сразу несколько ячеек, в которых содержатся названия месяцев и щелкните по клавише «Выровнять по центру»:

1. Размер и тип шрифта, его цвет и начертание изменяется с помощью группы «Шрифт» на «Главной» вкладке. Здесь также возможно изменить цвет заливки ячейки. Для примера попробуйте увеличить шрифт в столбце «А» и выделить его жирным:

1. Далее выделите верхнюю строку таблицы и установите на ней черный цвет заливки, а левый столбец с ячейками «А2:А6» залейте темно-синим цветом. Используйте для этого клавишу «Цвет заливки».

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

1. Теперь обозначьте границы таблицы. Для этого её необходимо выделить и щелкнуть на стрелочке в группе «Шрифт».

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

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

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

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

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

1. Теперь приступайте к созданию таблицы. Лучше всего для этого воспользоваться функцией «Границы»:

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

1. Ширина строк регулируется таким же образом:

Видео, как создать таблицу в Excel 2013

Создание сводной таблицы в MS Excel ХР(2003)

10. Создать новый лист (пункт меню Вставка). Переименовать Лист4 в лист с названием «Форма заказов». В пункте меню Данные выбрать команду Сводная таблица. Откроется Мастер сводных таблиц.

10.1. На шаге 1 Мастераустановить переключатель Вид создаваемого отчета в положение Сводная таблица.

10.2. На шаге 2 Мастера указать диапазон, содержащий исходные данные. Для этого следует нажать кнопку Обзор, открыть лист Список заказов и выделить таблицу «Список фирм-заказчиков» (рис.1.11). В диапазоне не допускаются имена столбцов в объединенных ячейках.

Рисунок 1.11 – Шаг 2 Мастера сводных таблиц и диаграмм

10.3. На шаге 3 Мастера, выбрать место размещения таблицы на листе Форма заказа и нажать кнопку Макет (рис.1.12). Откроется диалоговое окно Мастер сводных таблиц и диаграмм-макет(рис.1.13).

Рисунок 1.12 – Шаг 3 Мастера сводных таблиц и диаграмм

Рисунок 1.13 – Макет сводной таблицы

10.4. В правой части диалогового окна макета имеется список названий полей (столбцов выбранной таблицы). Следует выполнить следующие операции (рис.1.13):

– в область с надписью «Строка» последовательно перетащить поля «Код фирмы», «Наименование фирмы», «Код товара», «Наименование товара», «Количество»;

– в область с надписью «Страница» перетащить поля «Код заказа» и «Дата заказа»;

– в область с надписью «Данные» перетащить поле «Сумма, руб», данные из этого поля будут автоматически суммироваться;

– область с надписью «Столбец» в данном примере не использовались.

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

10.5. Создав нужный макет, нажать кнопку OK, а затем кнопку Готово (рис.1.14).

Рисунок 1.14 – Сводная таблица с итогами

10.6. Для фильтрации записей в сводной таблице следует раскрыть поле со списком и выбрать нужное значение (рис.1.16).

Рисунок 1.16 – Фильтрация сводной таблицы по Коду заказа

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

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

Чтобы убрать строки с промежуточными итогами надо последовательно два раза щелкнуть по имени каждого поля(кроме поля Итого). В диалоговом окне Вычисление поля сводной таблицыкаждого поля следует отметить переключатель Итоги®Нет®ОК (рис. 1.15).

Рисунок 1.15 – Диалоговое окно Вычисление поля сводной таблицы

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

10.8. Для завершения создания сводной таблицы по образцу (рис.1.2) выбрать на панели инструментов Сводная таблица кнопку Формат отчета. В открывшимся диалоговом окне Автоформат среди образцов оформления выбрать образец с подписью Нет. Выше таблицы добавить надпись ООО «Строймастер», а ниже таблицы надписи «Принял» и «Дата». В итоге получим сводную таблицу в виде, показанном на рисунке 1.17.

11. Для создания сводной таблицы «Итоговые суммы заказов» вставить в рабочую книгу новый лист. Переименовать Лист 5 в лист с названием Форма заказов. Выбрать команду Сводная таблица в пункте меню Данные и повторить операции 10.1-10.8. Для сводной таблице в диалоговом окне Мастер сводных таблиц и диаграмм – макет для области «Строка» выбрать поля «Код товара» и «Наименование фирмы», а для области «Данные» поле «Сумма, руб.». Убрать для выбранных полей промежуточные итоги. Для фильтрации выбрать из списка в поле «Наименование фирмы» запись ОАО «Привет». Общий итог рассчитывается автоматически. Результат выполнения показан на рисунке 1.18.

Рисунок 1.17 – Сводная таблица «Форма заказа» заказа №22 в виде бланка

Рисунок 1.18 – Сводная таблица «Итоговые суммы заказов»

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

Зачем нужны сводные таблицы?

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

Рис. 1.1. Как и ожидалось, начальник изменил требования к отчету после получения первого его варианта

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

Рис. 1.2. Устаревшая система возвращает данные за день, а не за месяц

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

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

Рис. 1.3. Ваша цель состоит в получении итоговых сведений за месяц и транспонировании их в горизонтальный вид

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

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

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

Рис. 1.4. Добавив сведения о кредитах в отчет, можно подсчитать чистую прибыль

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

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