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

Обучение и трудоустройство проектных менеджеров

Добавление сводной таблицы в MS Excel

Для добавления сводной таблицы необходимо:

  1. В меню выберите закладку "Вставка".
  2. Выберите пункт "Сводная таблица".
  3. В открывшемся окне "Создание сводной таблицы" Вы можете выбрать источник данных и место размещения сводной таблицы.
  4. В блоке "Выберите данные для анализа:" нажмите на кнопку "Выбрать подключение".
  5. В блоке "Укажите, куда следует поместить отчет сводной таблицы:" укажите ячейку с которой будет начинаться выгрузка данных. Данные будут размещаться от указанной ячейки вниз и вправо. При размещении данных избегайте наложение сводных таблиц.

Создание графика в MS MS Excel

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

Создание графика в MS MS Excel

7. После выбора запроса нажмите кнопку "Открыть"

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

Для начала встанем в любую ячейку нашей таблицы с данными. Далее в панели вкладок перейдем Вставка -> Сводная таблица:

Задание диапазона и места вставки таблицы

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

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

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

Нажимаем OK и перед нами появляется следующий конструктор:

Макет

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

Читайте так же:
Как узнать температуру видеокарты в Windows 10

В правой части мы видим следующие элементы (список полей и области):

  • Список полей;
    Список всех заголовков столбцов исходной таблицы с данными.
  • Фильтры;
    Добавление дополнительного среза для детализации данных.
  • Строки;
    Поля таблицы вынесенные в строки.
  • Столбцы;
    Поля таблицы вынесенные в столбцы;
  • Значения.
    Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).

В итоге мы имеем список полей и 4 области (фильтры, строки, столбцы, значения) из которых и составляется сводная таблица.

Теперь обо всем по порядку.

Группировка и фильтрация временных рядов в сводной таблице

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

Для этого переходим в лист «Данные», и после даты вставляем 3 пустых столбца. Выделяем столбец «Товар» и нажимаем «Вставить».

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

Вставленные столбцы называем «Год», «Месяц», «Год-Месяц».

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

  • В столбец «Год» добавляем формулу =ГОД(со ссылкой на дату);
  • В столбец «Месяц» добавляем формулу =МЕСЯЦ(со ссылкой на дату);
  • В столбец «Год — Месяц» добавляем формулу =СЦЕПИТЬ(ссылка на год;» «;ссылка на месяц).

Получаем 3 столбца с годом, месяцем и годом и месяцем:

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

Теперь переходим в лист «Сводная», устанавливаем курсор на сводную таблицу, вызываем правой кнопкой мыши меню и нажимаем кнопку «Обновить». После обновления в списке полей у нас появляются новые поля сводной таблицы «Год», «Месяц», «Год — месяц», которые мы добавили в простую таблицу с данными:

Читайте так же:
Скачать драйвера для ATI Radeon 3000 Graphics

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

Теперь давайте проанализируем продажи по годам.

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

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

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

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

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

сводные таблицы +в microsoft excel

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

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

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

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

Нажимаем на появившейся над сводной фильтр и ставим галочку «Выделить несколько элементов». Затем в списке с годами и номерами месяцев снимаем галочку с 2012 10 и нажимаем ОК.

эксель сводные таблицы

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

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

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

Читайте так же:
Что лучше HDMI или DisplayPort для игр

Макет сжатой формы

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

Макет табличной формы

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

Удаление промежуточных итогов

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

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

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

Макет табличной формы с повторением подписей

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

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

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

Читайте так же:
Определение необходимых для звуковой карты драйверов

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

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

Как быстро выделить таблицу:

  • Выбрать ее любую ячейку и нажать Crtl + * или Ctrl + A, или
  • Выбрать самую первую ячейку в таблице, зажать кнопки Ctrl и Shift, а затем нажать на кнопки вправо, затем вниз (→↓).

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

Вам также могут быть интересны следующие статьи

Объединение таблиц Excel без использования функций и макросов.
Здравствуйте! Меня зовут Сергей, я представляю проект All-Link. Он создан для анализа данных и здорово повышает возможности Excel, Open office и их аналогов. Например, он умеет буквально в несколько кликов объединять таблицы, даже если их много и структура переноса данных в них сложная. Мы хотим выяснить пределы его возможностей и приглашаем вас протестировать функции объединения таблиц на ваших данных и задачах. Подробности здесь — https://a3link.com/merge-tables-ru/
Доступ, разумеется, бесплатный.

Добрый день.. Меня интересует повторяющиеся числа по горизонтали .. число 1 сколько раз повторяется, число 2, число 3, число 4 и вместе от 5 до 8 сколько раз, если не трудно Может Вы мне поможете Спасибо большое, до свидания…

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

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

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

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

Обновить все

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

Читайте так же:
13 лучших плагинов для After Effects 2020 года

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector