Vvmebel.com

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

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

Создание сводной таблицы в 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 – Сводная таблица «Итоговые суммы заказов»

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

Иллюстрированный самоучитель по Microsoft Office 2003

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

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

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

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

Читать еще:  Расчет процента от числа в excel

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

В качестве примера рассмотрим создание сводной таблицы, позволяющей на основе таблиц с исходными данными выполнить анализ продажи определенных товаров в различных городах России. В книге, приведенной на рис. 18.16, показана продажа нескольких моделей автомобилей: Волга, Жигули, Ока в разных городах России: в Москве, Саратове и Туле. Каждый город показан на отдельном листе. Предполагается, что сводные таблицы составляются по четырем месяцам: январь, февраль, март и апрель. Таблицы отформатированы с использованием команды Автоформат (AutoFormat) в меню Формат (Format). Выбран образец с подписью Простой (Simple).


Рис. 18.16. Исходный список для составления сводной таблицы

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

Положением переключателя в группе Создать таблицу на основе данных, находящихся: (Create Pivot table from data in:) установите переключатель в положение: в нескольких диапазонах консолидации (Multiple consolidation ranges), так как источники данных для создания сводной таблицы, расположены на разных листе Excel.

Назначение других положений переключателя:

  • в списке или базе данных Microsoft Office Excel (Microsoft Office Excel list or Database) – позволяет создать отчет сводной таблицы или сводной диаграммы по данным строк или столбцов с подписями, расположенных на одном листе Excel;
  • во внешнем источнике данных (External data source) – позволяет создать отчет сводной таблицы или сводной диаграммы по данным внешнего файла или базы данных, например, Microsoft Access, SQL Server, Paradox;.
  • в нескольких диапазонах консолидации – позволяет создать отчет сводной таблицы или сводной диаграммы по данным строк или столбцов с подписями, расположенных в нескольких диапазонах консолидации;
  • в другой сводной таблице или сводной диаграмме (Another PivotTable report or Pivot Chart report) – используется для создания отчета сводной таблицы или сводной диаграммы по данным другого отчета сводной таблицы в активной книге.

Канал в Telegram

Вы здесь

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

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

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

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

Открываем книгу на листе «Вариант 1»

Открываем «Данные – Сводная таблица…»

Откроется окно мастера создания сводных таблиц. Ставим все как на снимке:

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

В видео-примере я указал диапазон ‘Вариант 1’!$A:$G

Это необходимо в том случае, если таблица постоянно дополняется данными, а постоянно строить таблицу может быть очень затруднительно или просто лень 🙂 Таким образом, я указал диапазон столбцов, но диапазон строк ограничен только возможностями Excel (в 2003 это 65536 строк, в 2007-2010 более 1млн. строк). Но у такого способа есть небольшой недостаток, в таблицах появляется критерий «пусто» (увидите далее). Хотя мне он особо не мешает.

Жмем «Далее>»

На этом шаге указываем, где создать таблицу. Оставляем «новый лист». Так же можно сразу построить макет (на мой взгляд это удобнее делать описанным далее способом, он более наглядный) или задать некоторые параметры таблице. Но все это можно в дальнейшем поправить.

Жмем «Готово».

Мы увидим следующую картину

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

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

Тип операции – тащим в левую зону

Поставщик – так же в левую, но немного правее Типа операции;

Наименование товара тащим в верхнюю зону;

Кол-во и Сумму тащим в самую большую зону поочередно;

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

В результате получим такую таблицу:

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

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

Читать еще:  Как рассчитать процентное соотношение в excel

Щелкнув один раз левой кнопкой мыши все строки группы «Количество по полю Кол-во» должны выделиться как на снимке выше.

Теперь жмем правой кнопкой мыши и в контексте выбираем пункт «Параметры поля»

В открывшемся окне параметров вычесления выберем «Сумма»

То же самое проделайте и для строк группы «Количество по полю Сумма»/

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

И в контекстном меню выбираем пункт «Скрыть»

В результате должны получить таблицу следующего вида:

Все. Теперь с помощью критериев отбора можно просмотреть различную информацию. Например, посмотрим, кто нам привозит молоко сгущенное, в каком кол-ве и на какую сумму. Для этого щелкнем в поле наименование товара по изображению стрелки и в списке выберем:

Получиться таблица вида:

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

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

Прикрепленный файл: svodnaya_excel.zip

Видео: Строим сводную таблицу в Excel

Как создать простейшую сводную таблицу в Excel?

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

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

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

  1. Выбираем любую ячейку из диапазона данных или весь диапазон, который будет использоваться в сводной таблице.ВНИМАНИЕ: Если выбрать одну ячейку из диапазона данных, Excel автоматически определит и выберет весь диапазон данных для сводной таблицы. Для того, чтобы Excel выбрал диапазон правильно, должны быть выполнены следующие условия:
    • Каждый столбец в диапазоне данных должен иметь своё уникальное название;
    • Данные не должны содержать пустых строк.
  2. Кликаем кнопку Сводная таблица (Pivot Table) в разделе Таблицы (Tables) на вкладке Вставка (Insert) Ленты меню Excel.
  3. На экране появится диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.Убедитесь, что выбранный диапазон соответствует диапазону ячеек, который должен быть использован для создания сводной таблицы. Здесь же можно указать, куда должна быть вставлена создаваемая сводная таблица. Можно выбрать существующий лист, чтобы вставить на него сводную таблицу, либо вариант – На новый лист (New worksheet). Кликаем ОК.
  4. Появится пустая сводная таблица, а также панель Поля сводной таблицы (Pivot Table Field List) с несколькими полями данных. Обратите внимание, что это заголовки из исходной таблицы данных.
  5. В панели Поля сводной таблицы (Pivot Table Field List):
    • Перетаскиваем Sales Rep. в область Строки (Row Labels);
    • Перетаскиваем Amount в Значения (Values);
    • Проверяем: в области Значения (Values) должно быть значение Сумма по полю Amount (Sum of Amount), а не Количество по полю Amount (Count of Amount).

В данном примере в столбце Amount содержатся числовые значения, поэтому в области Σ Значения (Σ Values) будет по умолчанию выбрано Сумма по полю Amount (Sum of Amount). Если же в столбце Amount будут содержаться нечисловые или пустые значения, то в сводной таблице по умолчанию может быть выбрано Количество по полю Amount (Count of Amount). Если так случилось, то Вы можете изменить количество на сумму следующим образом:

  • В области Σ Значения (Σ Values) кликаем на Количество по полю Amount (Count of Amount) и выбираем опцию Параметры полей значений (Value Field Settings);
  • На вкладке Операция (Summarise Values By) выбираем операцию Сумма (Sum);
  • Кликаем ОК.

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

Если необходимо отобразить объемы продаж в денежных единицах, следует настроить формат ячеек, которые содержат эти значения. Самый простой способ сделать это – выделить ячейки, формат которых нужно настроить, и выбрать формат Денежный (Currency) в разделе Число (Number) на вкладке Главная (Home) Ленты меню Excel (как показано ниже).

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

  • сводная таблица до настройки числового формата
  • сводная таблица после установки денежного формата

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

Рекомендуемые сводные таблицы в последних версиях Excel

В последних версиях Excel (Excel 2013 или более поздних) на вкладке Вставка (Insert) присутствует кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables). Этот инструмент на основе выбранных исходных данных предлагает возможные форматы сводных таблиц. Примеры можно посмотреть на сайте Microsoft Office.

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

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

Читать еще:  Excel вставить символ переноса строки

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

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

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

  1. Диапазон (может находиться в другой книге);
  2. Таблица данных (указывается ее имя);
  3. Данные из внешнего источника, полученные по SQL-запросу из базы данных и т.п.

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

Управление списком полей таблицы

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

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

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

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

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

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

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

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

Если предоставленных операций и вычислений недостаточно, то эксель позволяет создать свое вычисляемое поле в сводной таблице. Для этого выделите ячейку из области таблицы, перейдите на вкладку «Параметры» («Анализ» для Excel 2013) появившейся ленты. Далее в разделе «Сервис» кликните по пиктограмме «Формулы», из раскрывающегося меню (в версии 2010 и выше путь отличается: Раздел «Вычисления» -> Раскрывающийся список «Поля, элементы и наборы») выберите пункт «Вычисляемое поле…». Должно появиться окно:

Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:

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

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

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

Из примера видно, что сводная таблица представляет древовидную структуру, если используется более 1 поля. Корнем являются значения столбца, который в списке области «Названия строк» идет первым. Все последующие поля вкладываются в него и в друг друга, согласно своей очередности в списке, изменить которую можно простым перетаскиванием мыши. Каждую отдельную ветвь подобного дерева можно сворачивать и раскрывать. Данное свойство так же применимо к области названий столбцов.
По умолчанию эксель задает сводным таблицам макет в сжатом виде. Его можно изменить через параметры (клик правой кнопкой мыши по области таблицы -> параметры сводной таблицы -> Вывод -> Классический макет) либо через конструктор:

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

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

Теперь законченная сводная таблица выглядит так на листе Excel:

Помимо рассмотренных свойств через параметры таблицы можно установить:

  1. Имя сводной таблицы;
  2. Объединение и выравнивание подписей;
  3. Вывод значений для пустых ячеек;
  4. Автоматическое изменение ширины столбцов;
  5. Отображение общих итогов по строкам и столбцам;
  6. Сортировку;
  7. Печать;
  8. Обновление и др.

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

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