Возможно, вы уже знакомы с таким понятием Excel, как сводные таблицы, которые создаются из списка. Но что если вы хотите выполнить обратную операцию? В этом приеме вы узнаете, как создать список из простой сводной таблицы с двумя переменными.
Лист, показанный на рис. 167.1, отображает тот тип преобразования, о котором я говорю. Диапазон А1:Е4 содержит исходную сводную таблицу: 48 точек данных. Столбцы G:I показывают часть 48-строковой таблицы, полученную из сводной таблицы. Другими словами, каждое значение в исходной сводной таблице преобразуется в строку, которая также содержит соответствующие значению название продукта и месяц. Этот тип списка полезен, поскольку его можно отсортировать и манипулировать им другими способами.
Хитрость создания такого списка заключается в использовании сводной таблицы. Но прежде чем вы сможете применить этот метод, вы должны добавить команду Мастер сводных таблиц на панель быстрого доступа. Excel 2007, Excel 2010 и Excel 2013 все еще поддерживают Мастера сводной таблицы , но он недоступен на ленте. Чтобы получить доступ к мастеру, выполните следующие действия.
После выполнения этих шагов ваша панель быстрого доступа будет содержать новый значок.
Теперь пришло время преобразовать сводную таблицу в список. Имейте в виду, что, хотя следующие шаги специфичны для образцов данных, приведенных здесь, вы можете легко изменять шаги для работы с вашими данными.
Так, сводная таблица остается только с полем данных Сумма по полю Значение . На данный момент небольшая сводная таблица показывает только сумму всех значений (рис. 167.2).
Чтобы завершить, щелкните два раза на ячейке, содержащей итоговое значение (2851 в этом примере). Excel создаст новый лист, который будет содержать исходные данные в виде таблицы (рис. 167.3). Заголовки столбцов представляют общее описание (Строка , Столбец и Значение ), так что вы, вероятно, захотите изменить их и сделать более информативными.
Источником данных для сводной таблицы служит список данных, где, как правило, каждый столбец выступает в роли поля сводной таблицы. Но что если к вам пришла, только с виду похожая на сводную, таблица (она отформатирована и внешне напоминает ее, но использовать инструменты работы со сводными таблицами невозможно). И вам необходимо превратить ее в список данных, т.е. выполнить обратную операцию. В этом посте вы узнаете, как преобразовать сводную таблицу с двумя переменными в список данных.
На рисунке показан принцип, который я описал. Т.е. в диапазоне A2:E5 находится исходная сводная таблица, которая преобразуется в список данных (диапазон H2:J14). Вторая таблица представляет тот же набор данных, только в другом ракурсе. Каждое значение исходной сводной таблицы выглядит в виде строки, состоящее из пункта поля строки, поля столбца и соответствующего им значения. Такое отображение данных бывает полезно, когда необходимо отсортировать и манипулировать данными другими способами.
Для того чтобы реализовать возможность создания такого списка, мы воспользуемся инструментами сводной таблицы. Добавим кнопку Мастер сводных таблиц в панель быстрогодоступа, которая недоступна нам на ленте, но осталась как рудимент от более ранних версий Excel.
Перейдите по вкладке Файл -> Параметры . В появившемся диалоговом окне Параметры Excel, во вкладке Панель быстрого доступа в левом поле найдите пункт Мастер сводных таблиц и диаграмм и добавьте его в правый. Нажмите ОК.
Теперь на панели быстрого доступа у вас появился новый значок.
Щелкните по этой вкладке, чтобы запустить Мастер сводных таблиц.
На первом шаге мастера необходимо выбрать тип источника данных сводной таблицы. Устанавливаем переключатель В нескольких диапазонах консолидации и жмем Далее.
На шаге 2а укажите, как следует создавать поля страницы. Поместите переключатель Создать поля страницы -> Далее.
На шаге 2б, в поле Диапазон выберите диапазон, содержащий данные, и щелкните Добавить. В нашем случае это будет местоположение исходной сводной таблицы A1:E4.
На третьем шаге необходимо определиться, куда необходимо поместить сводную таблицу, и нажмите кнопку Готово.
Excel создаст сводную таблицу с данными. В левой части экрана вы увидите область Список полей сводной таблицы. Уберите все пункты с полей строк и столбцов. Более подробно о я писал в предыдущей статье.
У вас получится небольшая сводная таблица, состоящая из одной ячейки, которая содержит сумму всех значений исходной таблицы.
Дважды щелкните по этой ячейке. Excel создаст новый лист, который будет содержать таблицу со списком значений.
Заголовки этой таблицы представляют общую информацию, вероятно, вы захотите сделать их более информативными.
Итак, таблица в Excel - это прямоугольная область листа, в которой каждая строка представляет собой набор данных, а в ячейке на пересечении данной строки и каждого столбца находится единица данных. Каждому столбцу присваивается уникальное имя. Столбцы таблицы называются полями , а строки - записями . В таблице не может быть записей, в которых нет данных ни в одном поле.
Если на листе Excel
выбран прямоугольный диапазон ячеек, его легко преобразовать в таблицу, и, наоборот, с записями таблицы можно работать как с обычным диапазоном ячеек.
В таблицах можно использовать функции и формулы Excel
, в частности, можно вставить строку (запись), в которой подсчитываются общие или промежуточные итоги.
Если этот диапазон нужно изменить, достаточно выделить нужный диапазон данных курсором.
По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя.
Требования к именам таблиц аналогичны требованиям к именованным диапазонам.
В созданной таблице можно изменить цвет, шрифт, вид границ и прочее. С помощью форматирования можно добиться того, что информация будет легче восприниматься. Эти изменения можно выполнить самостоятельно или выбрать готовый стиль оформления.
К таблице можно добавить дополнительную, итоговую строку, в которой будут размещены результаты выполнения различных функций, примененных к данным некоторых или всех полей.
Порядок действия следующий:
Для ввода в конец таблицы новых записей следует выделить строку итогов и воспользоваться правой кнопкой мыши. Появится контекстное меню, в нем нужно выбрать пункт Вставить . Если в появившуюся новую строку ввести данные, они будут автоматически участвовать в пересчете итогов.
Для того чтобы сократить время на добавление строк в таблицу, можно отключить строку итогов и довалять новые данные с новой строки. В этом случае таблица будет автоматически расширять свой диапазон.
Для проведения расчетов и помещения результатов в новое поле, достаточно ввести формулу в одну ячейку этого поля. Excel автоматически размножит ее по всем ячейкам данного поля. Если правильно установлены параметры Excel , при вводе формулы в нее записываются не адреса ячейки, а имена полей.
Если вместо имени поля на экране в формулах указаны адреса ячеек, необходимо изменить настройку:
При работе с таблицами, наряду с преимуществами, действует ряд ограничений: нельзя объединять ячейки, нельзя добавлять промежуточные итоги и т.д. Если расчеты в таблице завершены и нужны только данные из нее и, возможно, оформление, таблицу можно быстро преобразовать в обычный диапазон данных.
Для этого необходимо выполнить следующие шаги:
Здравствуйте, друзья. В прошлой статье мы , настроили поля, обсудили вопросы фильтрации данных, сортировки, оформления. Пришло время чуть глубже вникнуть в работу инструмента для получения еще более современных и совершенных отчетов, а так же удобства использования сводных таблиц в повседневной работе. Приступаем!
Совсем недавно один из читателей обратился ко мне с вопросом: «А как скопировать сводную таблицу с одного листа на другой»? Решил осветить этот вопрос здесь, потому что слышу его не впервые. Сводная таблица – это не обычный диапазон данных, это область ячеек с результатами работы инструмента. Поэтому, просто скопировать сводную таблицу в другое место нельзя.
Но Вы всегда можете создать еще одну такую же таблицу в новом месте. Если же Вас интересует лишь результат работы инструмента и никаких манипуляций, связанных с работой сводных таблиц Вы делать не будете – можно скопировать данные в сводной таблице, и вставить их на новое место, как значения, а не как дубликат сводной таблицы. Для этого выполните такие действия:
В результате, программа вставит значения, содержащиеся в выделенном диапазоне. Дальше можете работать с этой таблицей, как с обычными данными на листе.
Иногда нужно получить исходные данные, на основании которых вычислена та или иная строка сводной таблицы. То есть, сделать выборку данных, удовлетворяющих какому-то критерию из сводной таблицы.
Вернемся к примеру из предыдущего поста. Там у нас была подневная таблица продаж по группам товара, регионам и менеджерам. Мы построили сводную таблицу, в которой посчитали сумму продаж с группировкой по менеджерам и группам товаров.
Представим, что по какой-то причине нас заинтересовали подневные продажи метизов у Романа. Выделим ячейку на пересечении строки «Роман» и столбца «Метизы». Жмем на нем правой кнопкой мыши и выбираем «Показать детали». Программа создаст новый лист и отобразит на нем выборку из исходной таблицы, т.е. все продажи Романа метизов.
А можно просто сделать двойной клик по нужной величине. Это альтернативный, и более простой способ.
Обратите внимание, что результат будет представлен в виде , а не обычного диапазона. Можете еще раз оценить удобство такой структуры данных и воспользоваться всеми ее преимуществами.
Если стандартных функций вычисления в сводной таблице (сумма, количество, отклонение и т.п.) Вам оказалось мало, инструмент имеет дополнительные вычислительные возможности. Прежде всего, нажмите правой кнопкой мыши в любой ячейке столбца с вычислениями и выберите «Дополнительные вычисления». В списке вычислений будет много интересных вариантов. К примеру. Мы хотим знать какую долю продаж от всего количества выполняет каждый менеджер из примера. Выполним дополнительные вычисления в столбце, где суммируются продажи. Параметром выберем «% от суммы по столбцу». Посмотрите, в 2 клика мы перешли от натуральных величин к процентам, теперь легче сравнивать показатели, или скрыть их, когда не рекомендуется отражать в отчете конкретные цифры в денежных единицах.
Просмотрите все возможные опции расчетов, и Вы наверняка найдете то, что можно использовать уже сейчас.
Еще один интересный способ добавить таблице информативности – вычисляемые поля и объекты. Они применяются, когда нужно ввести в таблицу дополнительные строки или столбцы. К сожалению, в них нельзя записать привычные нам и , но все же это лучше, чем ничего.
Эти объекты нужны, чтобы вставить в таблицу новые столбцы без вставки их в исходный массив данных. К примеру, у нас есть сумма продаж менеджеров и количество чеков. Рассчитаем в отдельном столбце средний чек.
Выполняем такую последовательность действий:
Теперь у нас появился еще один столбец, в котором посчитана средняя сумма в чеке для каждого менеджера. Его название появилось в области «Значения» панели настройки сводной таблицы.
Похожий функционал предоставляют вычисляемые объекты. Но они вставляют в таблицу не столбцы, а строки. К примеру, у нас есть сумма продаж менеджеров, а нас интересует сколько составит НДС (18%) от этих продаж и общая сумма с НДС. Создаем вычисляемый объект:
Когда Ваша таблица готова, можно сделать дополнительную группировку данных. Это улучшит его читаемость и гибкость. Выделю несколько удобных инструментов группировки.
Если Вам нужно объединить числовые данные в несколько интервалов для минимизации отчета – используйте этот инструмент. Например, в нашей таблице есть данные о продажах по дням. Нам нужно сгруппировать эти данные по месяцам. В сводной таблице это можно сделать очень просто. Делаем по шагам:
Таким же образом можно группировать обычные числовые данные. Например, мы хотим сгруппировать ежедневные продажи с шагом 1000 и узнать, в каком из интервалов было больше всего чеков. Делаем так:
Конечно, эта задача упрощенная, он отличается от тех, которые будете решать Вы. Но главное – понять механизм работы, тогда сможете применить его в своих расчетах.
Так же, можно группировать записи вручную. Для этого выделите нужные данные и нажмите Работа со сводными таблицами – Анализ – Группировать – Группировка по выделенному .
Кстати, чтобы отменить группировку – кликните правой кнопкой мыши по группированному столбцу и выберите «Разгруппировать».
Если Вы мало знаете о срезах, или вообще с ними незнакомы – вот вам моя . Вкратце, срезы – это инструменты, позволяющие фильтровать данные благодаря выносным кнопкам. Это прекрасный способ создать пользовательский интерфейс, ведь в реальности не каждый пользователь Эксель продвинут, как Вы и не все умеют пользоваться автофильтром, поиском или фильтрами сводных таблиц.
А выглядит это вот так:
Вы видите несколько окон на рабочем листе с перечисленными в них данными, а так же, сводную таблицу, содержащую полный набор данных. Но что если кому-то нужно посмотреть продажи метизов у Романа 1 апреля 2016 года? Кликаем в окнах на кнопки:
Вот так, за три клика любой пользователь может из огромного массива сведенной информации выбрать лишь нужный ему отрывок данных. Посмотрите, что получилось:
По-моему, отлично! Так вот, чтобы включить срезы в сводных таблицах – выделите любую ячейку этой таблицы и выполните на ленте Работа со сводными таблицами – Анализ – Фильтр – Вставить срез . На экране появится окошко, где нужно галочками отметить те поля сводной таблицы, по которым можно будет делать срезы. Каждому полю будет соответствовать свое окошко со списком. Выбирайте, жмите Ок и все, заработало!
Аналогично работает временная шкала. Этот инструмент очень похож на срезы, но управляет полями, в которых содержатся даты. Чтобы добавить временную шкалу – нажмите Работа со сводными таблицами – Анализ – Фильтр – Вставить временную шкалу . После простых настроек появится окно фильтрации дат, которое позволяет эффективно и быстро ограничивать периоды дат, выводимые в отчет.
Пожалуй, это все основные функции сводных таблиц, хотя их возможности можно изучать и далее самостоятельно. Готов ответить на Ваши вопросы по изложенному материалу, или тем моментам, которые не попали в обзор. В следующей статье мы рассмотрим , которые могут сделать еще более наглядной Вашу отчетность. До скорых встреч!
Есть несколько макетов, которые предоставляют предопределенных структуру к отчету сводной таблицы, но невозможно настроить макетов. Если вам нужна большая гибкость при создании макета отчета сводной таблицы, можно преобразовать ячейки в формулы листа, а затем измените макет эти ячейки с преимуществами функций, доступных на листе. Можно преобразовать ячейки в формулах, использующих функции куба или с помощью функции получить.данные.сводной.таблицы. Преобразование ячейки в формулах значительно упрощает процесс создания, обновление, и обслуживание эти настройки Сводные таблицы.
При преобразовании ячейки в формулы, эти формулы доступ к тому же данных сводной таблицы и могут быть обновлены, чтобы просмотреть актуальные результаты. Тем не менее за исключением возможных фильтров отчета, больше не имеется доступ к интерактивные возможности сводной таблицы, такие как фильтрация, сортировка, или развертывание и свертывание уровней.
Примечание: При преобразовании сводную таблицу интерактивной аналитической обработки (OLAP), вы можете продолжать обновление данных для получения значений актуальные меры, но не удается обновить фактические элементы, которые отображаются в отчете.
Ниже приведены типичные примеры того, что можно делать после преобразования ячеек сводной таблицы в формулы листа для настройки макета преобразованных ячеек.
Изменение расположения и удаление ячеек
Предположим, имеется периодический отчет, который нужно создавать для персонала каждый месяц. Для него необходимо только подмножество отчетных данных и специальный макет. Можно просто переместить и расположить нужным образом ячейки в макете, удалить ячейки, включение которых в ежемесячный отчет для персонала не требуется, а затем отформатировать ячейки и лист нужным образом.
Вставка строк или столбцов
Допустим, что нужно отобразить данные о продажах за предыдущие два года разбивкой по региона и товарной группы, а вы хотите вставить расширенный комментарий в дополнительные строки. Просто вставьте строку и введите текст. Кроме того нужно добавить столбец, показывающий продажи по региона и товарной группы, которого нет в исходной сводной таблицы. Просто вставить столбец, добавить формулу, чтобы получить нужные результаты и нажмите Заполнение столбца вниз, чтобы получить результаты для каждой строки.
Использование нескольких источников данных
Предположим, необходимо сравнить результаты в реальной и тестовой базах данных, чтобы убедиться в том, что тестовая база данных выдает ожидаемые результаты. Можно легко скопировать формулы ячеек, а затем изменить аргумент для подключения, чтобы указать на тестовую базу данных для сравнения этих двух результатов.
Использование ссылок на ячейки для изменения введенных пользователем данных
Предположим, необходимо изменить весь отчет в зависимости от введенных пользователем данных. Можно изменить аргументы в формулах куба на ссылки на ячейки листа, а затем ввести в ячейки другие значения и получить соответствующие результаты.
Создание неоднородного макета строк или столбцов (асимметричное создание отчета)
Допустим, вам нужно создать отчет, содержащий 2008 столбец с именем фактические продажи 2009 столбец с именем прогнозируемого продаж, но вы не хотите другие столбцы. Можно создать отчет, содержащий только те столбцы, в отличие от сводной таблицы, которое потребует симметричную отчетов.
Создание собственных формул куба и многомерных выражений
Предположим, необходимо создать отчет, отображающий продажи определенного продукта в июле тремя отдельными продавцами. Если вы умеете использовать многомерные выражения и запросы OLAP, то можете вводить формулы куба сами. Хотя эти формулы могут быть довольно сложными, можно упростить создание и улучшить их точность, используя функцию автозавершения формулы. Дополнительные сведения см. в разделе Использование функции автозавершения .
Преобразование ячейки в формулы, использующие функцию куба
Примечание: Только с помощью этой процедуры можно преобразовать сводную таблицу интерактивной аналитической обработки (OLAP).
Преобразование ячейки в формулы, использующие функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Функция получить.данные.сводной.таблицы можно использовать в формуле преобразовать ячейки сводной таблицы в формулы листа при необходимости работы с источниками данных не OLAP, если вы предпочитаете не обновить сразу в новый формат версии 2007 сводную таблицу или при необходимости во избежание ошибок сложность с помощью функции куба.
Убедитесь, что доступна команда Создать GetPivotData в группе Сводная таблица на вкладке Параметры .
Примечание: Команда Создать GetPivotData управляет параметром Использовать функции GetPivotData для ссылок в сводной таблице в категории Формулы раздела Работа с формулами в диалоговом окне Параметры Excel .
В сводной таблице убедитесь в том, что ячейки, которое вы хотите использовать в каждой формуле отображается.
В ячейке листа за пределами сводной таблицы введите формулу, которая требуется до точки, которую вы хотите включить данные из отчета.
Щелкните ячейку в сводную таблицу, которую требуется использовать в формуле в сводной таблице. Функция получить.данные.сводной.таблицы добавляется в формулу, получающего данные из сводной таблицы. Эта функция по-прежнему получать правильные данные, если изменения макета отчета или обновить данные.
Завершите ввод формулы и нажмите клавишу ВВОД.
Примечание: В случае удаления из отчета любой из ячеек, на которые ссылается формула ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ формула возвращает ошибку #ССЫЛКА!.
