Вставка сводной таблицы в excel. Сводные расчеты в Microsoft Excel - Формулы. Проверка правильности выставленных коммунальных счетов.
Сейчас на простом примере мы научимся создавать сводные таблицы и познакомимся с некоторыми их возможностями.
Для примера в качестве исходных данных возьмем ведомость основных средств компании и определим стоимость позиций по каждому филиалу с помощью сводной таблицы.
Перед созданием сводной таблицы убедитесь, что в исходной таблице отсутствуют пустые заголовки. Это требуется потому что каждый столбец таблицы становиться полем сводной таблицы, по которому можно собрать данные.
Затем сортировка автоматически создает таблицу, которую невозможно создать вручную. Ответ на четвертый вопрос будет возможен после сравнения количества женщин и мужчин, допущенных к работе каждый год. Для этого нам нужно создать новую сводную таблицу.
В области рядов мы помещаем дату работы и в область столбцов пола. Наша первая задача - сократить их количество. С его помощью мы можем создавать собственные категории группировки данных. Просто укажите начальное значение, конечное значение и ширину диапазонов. Лист также может обнаружить, что он имеет дело с часами или датами - тогда он предлагает дополнительные варианты разделения.
Советую также преобразовывать исходный диапазон данных в таблицу (Главная - Форматировать как таблицу ). Тогда при добавлении или удалении строк и столбцов не придется менять ссылку на этот диапазон в сводном отчете.
Пользователи Excel 2013 могут выбрать сводную таблицу из макета на основе рекомендаций, которые предлагает Excel. Вот как это делается:
Поскольку мы хотим покрыть все годы деятельности компании, мы автоматически начинаем и заканчиваем отсек. Если мы добавим другую позицию, мы создадим многоуровневую группировку. Теперь, когда таблица намного читаема, ее вторая проблема видна - она показывает абсолютные числа, сравнение которых может привести к неправильным выводам.
Чтобы найти ловушки этого типа, номера в таблице следует заменить на относительные меры. Этот эффект достигается без использования даже одной формулы, если мы углубимся в настройки положения в области данных. Поэтому мы открываем окно макета таблицы редактирования. Дважды щелкните по единственной кнопке в области данных, а в новом окне нажмите «Дополнительно».
Самостоятельное создание сводной таблицы с помощью конструктора
Для тех, у кого нет Excel 2013, или же если нужной сводной таблицы нет в предложенных, можно создать ее с нуля с помощью конструктора. Для этого:
Появятся три раскрывающихся списка. Хотя они выглядят неприметными, вместе они образуют один из самых сложных элементов дизайна сводной таблицы. Все варианты - лучше или хуже - описаны в справке программы. Мы хотим сопоставить количество людей каждого пола с общим количеством новых сотрудников за определенный год. Другие списки неактивны, поэтому мы одобряем только изменения в обоих окнах. Стол почти закончен. Тем не менее, у нас все еще есть некоторые возражения против этого, поэтому нам нелегко найти ответ на дальнейшую часть поставленного вопроса.
Важно! При выделении диапазона убедитесь, что выбрана таблица с заголовками, а так же что выбранный диапазон не включает строку итогов.
Так же укажите, где необходимо разместить отчет сводной таблицы: на новом листе или на уже существующий.
Мы можем изменить порядок строк в порядке возрастания относительно значений во втором столбце. Кажется естественным изменить настройки сортировки в настройках области строки так же, как мы это делали при подготовке графика прибыли в отделе маркетинга.
Поэтому мы должны использовать общий механизм сортировки. Сначала выберите область, в которой вы хотите изменить порядок. В новом окне мы выбираем направление и направление сортировки; Также убедитесь, что вкладка «Параметры» не выбрана. Поле «Область» содержит метки столбцов.
Как только изменения будут одобрены, наша таблица будет именно такой, какой мы хотели. Последняя задача требует от нас выполнения большинства мероприятий, которые мы обсуждали до сих пор; Кстати, мы увидим, как выглядит таблица, когда мы помещаем более одной кнопки в область данных.
Советую размещать каждый отчет сводной таблицы на отдельном листе. Это поможет избежать ошибок в случае если будут добавлять данные.
После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 . Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.
Прежде всего, мы должны создать новую таблицу. Мы соберем секцию с позицией. Давайте остановимся на мгновение с этими настройками. Используя первый, мы выбираем столбец из базы данных. Хотя все они доступны, это будет иметь смысл, если мы выберем одну из строк или столбцов сводной таблицы.
Второй позволяет указать определенную группу внутри столбца. Они вызывают опорную точку в ячейке в непосредственной близости от «тока», согласно следующей таблице. Мы можем закрыть окно настроек окна оплаты, но мы еще не закрываем окно макета таблицы.
Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ , КОЛОННЫ , СТРОКИ и ЗНАЧЕНИЯ .
В зависимости от того в какую область мы перенесем то или иное поле будет зависеть вид будущей сводной таблицы.
Таким образом, результирующая таблица будет легче читать. Мы не могли этого сделать раньше, потому что область данных была пуста. Мы одобряем изменения во всех открытых окнах. Полученная таблица показывает разницу между средними доходами в определенной позиции и средним заработком в нижней позиции в иерархии. Первый столбец пуст, поскольку содержит самые низкие позиции. Во второй колонке показано, сколько зарабатывают специалисты из младших специалистов. В третьей колонке содержится информация о том, сколько пожилых специалистов зарабатывает у специалистов.
В качестве примера давайте создадим сводный отчет, в котором посчитаем рыночную стоимость для каждой группы из исходной таблицы. Т.е. мы хотим, чтобы напротив группы стали строками сводного отчета. Значит нужно перетащить поле Группа в область строк.
Данную операцию можно сделать еще 2 способами:
- отметить флажок напротив поля Группа ;
- щелкнуть по полу Группа правой кнопкой мыши и выбрать Добавить в названия строк .
После добавления поля вы увидите список всех групп, которые есть в исходной таблице:
Наконец, четвертый показывает различия между координаторами и старшими специалистами. Такая компиляция дает нам некоторую информацию о прибыльности этой компании. Однако эти различия выражаются в абсолютных числах, и они, как мы помним из ответа на четвертый вопрос, иногда дают ложную картину реальности. Поэтому стоит взглянуть на наши данные с несколько иной точки зрения.
Мы снова открываем окно редактирования макета таблицы и перетаскиваем платежную ведомость в область данных. Только сейчас мы видим, что по мере того, как лестница переходит на более высокие уровни, относительное преимущество продвижения снижается; Принимая во внимание только текущую зарплату, более «выгодно» продвигаться от младшего специалиста к специалисту, чем от старшего специалиста до координатора.
Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.
Чтобы лучше понимать как создавать сводные таблицы в программе, нам нужно научиться делать их в Excel.
«Дайвинг» в данных и извлечение строк, соответствующих критериям
Раньше мы использовали сводные таблицы только для поиска ответов на вопросы. Однако они также могут использоваться для предварительного анализа данных и подготовки специальных заявлений. Вернемся к нашей первой таблице, которая показывает количество сотрудников в каждом отделе. Предположим, мы хотели бы проверить гендерную структуру доски.
Оказывается, мы можем сделать это, не меняя макет таблицы. Просто нажмите на метку «Совет» в первой колонке. Появится новое окно - вместе с ним вы укажете, какие подгруппы разделяют нашу категорию сотрудников. Конечно, мы нажимаем на секс, и мы одобряем изменения. Теперь мы можем дважды щелкнуть имя другого отдела, чтобы узнать распределение по полу своих сотрудников.
Сводные таблицы в Excel
Форма сводной таблицы содержит три области для перетаскивания в них полей: для столбцов, для строк и для данных.
Из диалога Список полей сводной таблицы перетащите поле Фамилия в область Перетащите сюда поля строк . Форма сводной таблицы немного изменилась.
Дважды щелкнув по ячейке данных, мы создадим новый лист, содержащий все строки, которые использовались для вычисления значения. Это особенно полезно, если вы хотите выполнить некоторую нестандартную математическую операцию в выбранной группе. Ищете ответы на четвертый вопрос, мы сократили количество строк в сводной таблице путем автоматической группировки. Оказывается, что группы произвольно фиксированного состава также могут быть созданы для текстовых данных.
Сводная таблица и набор переменных данных
Для этого выберите нужные элементы и выберите в меню пункт «Данные → Контур → Группа». Все выбранные строки будут сгруппированы. Мы повторяем операцию для других интересующих нас групп. Все наши задачи предполагали, что исходный набор данных был инвариантным. Однако сила сводных таблиц заключается в том, что их можно использовать для анализа динамического набора данных на регулярной основе. Благодаря этому однажды разработанная таблица может служить нам в течение длительного времени.
Excel выбрал все уникальные значения столбца Фамилия и расположил их в строках нашего будущего отчета.
Теперь перетащите поле Сумма в область данных .
Excel посчитал сумму продаж для каждого продавца. Главное быстро и без ошибок. Отчет можно считать готовым.
Мы должны обратить внимание на два вопроса. Во-первых, таблица не обновляется автоматически. Поэтому изменения, внесенные в исходные данные, немедленно не отражаются. Нам нужно щелкнуть по одной из ячеек в таблице правой кнопкой мыши и выбрать из меню «Обновить».
Во-вторых, таблица автоматически не расширяет диапазон данных, на которых он работает. Поэтому, если мы добавляем новые строки только в нашу базу данных, нам нужно открыть окно изменения макета таблицы и в разделе «Настроить содержимое поля выбора поля».
Когда мы обнаружим, что сводная таблица больше не нужна, мы, вероятно, захотим ее удалить. Мы можем сделать это несколькими способами. Важно разместить курсор в любом месте и выбрать «Данные» → «Сводка» → «Удалить» в меню. Мы также можем щелкнуть правой кнопкой мыши по любой из его ячеек и выбрать «Удалить» из контекстного меню.
Но для большего понимания возможностей сводной таблицы укажем еще область столбцов. Эта область кажется теперь скрытой, но на самом деле ее видно. Перенесите поле Дата на правый верхний угол отчета , вот сюда:
Как только вы отпустите кнопку мыши отчет сразу изменится, показав динамику продаж по дням для каждого продавца.
Другой подход использует тот факт, что сводная таблица представляет собой специально подготовленный набор ячеек. Мы можем выбрать их все и выбрать «Правка» → «Удалить содержимое» в меню. В новом окне мы должны выбрать «Удалить все». Поскольку таблица создается по умолчанию в новой электронной таблице, мы можем просто ее удалить. Таким образом мы удалим всю информацию об этом из файла.
Сводные таблицы являются чрезвычайно всеобъемлющим предметом, и ни одна статья, даже до тех пор, пока эта, не способна решать все связанные с ними проблемы. Однако для читателей, которые просто изучают возможности сводных таблиц, важно знать более тонкие детали того, как они работают, и более активно работать с ними. Следовательно, статья заканчивается списком задач для самостоятельного исполнения. Ответы на все вопросы требуют только одной таблицы.
Отчет получился слишком подробным. Сгруппируем его по месяцам.
- Щелкните правой кнопкой по полю отчета Дата , в появившемся меню выберите Группа и структура , затем Группировать...
- В следующем диалоге Excel просит нас указать как именно группировать и сразу предлагает по месяцам (выделено). Ничего не меняем и нажимаем ОК .
Теперь дата продажи в отчете сгруппирована по месяцам.
В каком году была первая премия, присуждаемая в области, где количество наград наименьшее?
- Сколько премий было присуждено в каждой из областей?
- Сколько лет занимали физики в год Нобелевской премии?
Изменение сводного отчета Excel
Чтобы изменить отчет, например, заменить поле строк, просто перетяните это поле из отчета за его пределы. А затем перенесите туда другое поле, например, поле Продукт .
Создайте отчет сводной таблицы из списка данных. Выберите любую ячейку в списке, из которой вы создаете сводную таблицу. 
На экране отображаются диалоги сводной таблицы и сводной таблицы. 
На экране появится второе окно мастера. 
Если область действия неверна, нажмите «Свернуть» и выберите соответствующую область непосредственно на листе. На экране появится третий шаг мастера.
Получили отчет по продажам продуктов по месяцам.
А вот что получится, если мы заменим поле Дата на Фамилия .
Возможности сводных таблиц кажутся безграничными. А как вам это?
Резюме
В качестве исходных данных для построения отчета мы взяли коротенькую таблицу продаж. Но она могла состоять из тысяч строк и десятков столбцов. Способ построения отчета останется таким же: указали столбцы, строки и данные.
Чтобы поместить отчет в новую таблицу, установите флажок «Новый лист». Вы также можете использовать опцию «Существующий листок» и выбрать соответствующую ячейку, если вы хотите, чтобы отчет отображался на текущем листе. На экране появится Мастер таблиц и сводных графиков.
Построение сводного стола немного напоминает складывание стола с частями. Это отдельные столбцы в списке. Разная компоновка таблицы и, следовательно, различные аспекты анализа данных возможны путем размещения столбцов в так называемом. Строка строк - поле, которому назначена ориентация линии в отчете сводной таблицы. Столбцы столбцов - поле, которому была назначена ориентация столбца в отчете сводной таблицы. Поле страницы - поле, которое назначается ориентации страницы или фильтра. Когда вы нажимаете на другой элемент в поле страницы, весь отчет сводной таблицы будет изменять свой внешний вид, чтобы отображались только агрегированные данные, связанные с этим элементом.
Вы получили базовые знания о сводных таблицах Excel. Этого вам достаточно, чтобы понять принцып их настройки и пользу от них. Теперь посмотрим
