Сводная таблица в excel как сделать 2003

Сводная таблица в excel как сделать 2003

Выводит данные, используя следующую формулу: ((Значение в ячейке)*(06щий итог))/((0бщий итог строки)*(06щий итог столбца)).

Кроме этого Excel позволяет создавать вычисляемые поля и вычисляемые элементы поля.

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

    В сводной таблице выделите ячейку, перед которой будет вставлено новое вы­числяемое поле/поле данных сводной таблицы, в которое будет вставлен вычисляе­мый элемент.

  • Выберите на панели инструментов Сводные таблицы команду Сводная таблицаФормулыВычисляемое поле/ Вычисляемый объект. На экране появится диалоговое окно Вставка вычисляемого поля.
  • Введите в поле Имя имя нового вычисляемого поля.
  • Введите формулу для расчетов в поле Формула.
  • Чтобы использовать в создаваемой формуле значение поля данных сводной таблицы, выделите нужное поле данных в списке Поля щелкните на кнопке Добавить поле.
  • Щелкните на кнопке Добавить, чтобы вновь созданное поле оказалось в списке Поля.
  • Щелкните на кнопке ОК, чтобы вернуться к сводной таблице с новыми вычисляемыми полями.
  • Форматирование сводной таблицы

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

    1. Щелкните на любой ячейке сводной таблицы.
    2. Выберите команду ФорматАвтоформат или щелкните на кнопке Автоформат панели инструментов Сводные таблицы. На экране появится диалоговое окно Автоформат с возможными вариантами автоформата.
    3. Выделите понравившийся вариант и щелкните на кнопке ОК.

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

    1. Выведите на экран панель инструментов Сводные таблицы.
    2. Щелкните на любой ячейке сводной таблицы и выберите команду Сводная таблицаВыделитьРазрешить выделение.
    3. Подведите указатель мыши к полю данных, элементу поля или строкам итогов — он должен превратиться в стрелку. Укажите на нужную часть таблицы и щелк­ните левой кнопкой мыши.
    4. Укажите, какие именно группы сведений должны быть выделены: заголовки, данные или то и другое. Для этого выберите команду Сводная таблицаВыделитьТолько Заголовки/Только Данные/Заголовкии данные.
    5. Чтобы выделить всю таблицу, выберите команду Сводная таблицаВыделитьТаблица целиком.
    6. Укажите, какие части сводной таблицы выделить: заголовки, данные или то и другое. Процедура аналогична шагу 4.
    7. Выберите необходимое форматирование с помощью меню Формат или одноименной панели инструментов.

    Для задания числовых форматов необходимо выполнить следующие действия:

    1. Выделите ячейку или диапазон ячеек, для которых нужно задать числовой формат.
    2. Вызовите контекстное меню щелчком правой кнопки мыши и выберите команду Параметры поля или щелкните на одноименной кнопке на панели инструментов Сводные таблицы. На экране появится диалоговое окно Вычисление поля сводной таблицы (см. рис. 9.9).
    3. Щелкните на кнопке Формат. На экране появится диалоговое окно Формат ячеек с единственной вкладкой Число.
    4. Укажите нужный формат.
    5. Закройте диалоговые окна, щелкая на кнопках ОК.

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

    Читать еще:  Как сделать разные таблицы в excel на одном листе?

    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: пошаговая инструкция

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

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

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

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

    Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

    — Можно ли отчет сделать не по выручке, а по прибыли?

    — Можно ли товары показать по строкам, а регионы по столбцам?

    — Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

    Рассмотрим, как создать сводную таблицу в Excel.

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

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

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

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

    Читать еще:  Как сделать текст столбиком в excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Работа со сводными таблицами в Excel

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

    Заменим выручку на прибыль.

    Товары и области меняются местами также перетягиванием мыши.

    Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

    Источник данных сводной таблицы Excel

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

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

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

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

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

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

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

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

    или
    через команду во вкладке Данные – Обновить все.

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

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

    Читать еще:  Диаграмма ганта в excel как сделать условное форматирование

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

    ШКОЛА ПРОГРАММИРОВАНИЯ

    Сообщение об ошибке

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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