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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Читать еще:  Как сделать графики в excel 2016?

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

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

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

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

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

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

    В этом курсе:

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

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

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

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

    На вкладке Вставка нажмите кнопку Сводная таблица.

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

    В поле Таблица или диапазон проверьте диапазон ячеек.

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

    Нажмите кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

    Щелкните ячейку в диапазоне исходных данных и таблицы.

    На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.

    «Рекомендуемые сводные таблицы» для автоматического создания сводной таблицы» />

    Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:

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

    Щелкните ячейку в диапазоне исходных данных и таблицы.

    На вкладке Вставка нажмите кнопку Сводная таблица.

    Если вы используете Excel для Mac 2011 или более ранней версии, кнопка «Сводная таблица» находится на вкладке Данные в группе Анализ.

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

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

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

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

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

    Соответствующие поля в сводной таблице

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

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

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

    Читать еще:  Как сделать таблицу в excel график на месяц?

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

    Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.

    Отображение значения как результата вычисления и как процента

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

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

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

    Теперь вы можете вставить сводную таблицу в электронную таблицу в Excel в Интернете.

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

    Выделите таблицу или диапазон в электронной таблице.

    На вкладке Вставка нажмите кнопку Сводная таблица.

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

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

    Примечание: Указанная ячейка должна находиться за пределами таблицы или диапазона.

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

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

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

    Соответствующие поля в сводной таблице

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

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

    Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

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

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

    диалоговое окно «Дополнительные вычисления»» />

    Отображение значения как результата вычисления и как процента

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

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

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

    Дополнительные сведения

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

    Get expert help now

    Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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