Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel
При работе с книгами Excel и построении сводных таблиц может возникнуть ошибка, которая не позволяет завершить операцию. Основных причин может быть две и обе они зависят стороннего файла, с которыми работает пользователь.
Первая — если в процессе создания сводной таблицы используются данные из другого файла Excel, в наименовании которого содержатся квадратные скобки. Для того чтобы устранить эту причину, достаточно из наименования файла убрать скобки.
Вторая — используемый для работы файл, взят пользователем из Internet Explorer, что также порождает возникновение ошибки «Недопустимая ссылка на источник данных». Решением проблемы, как и первом случае, будет небольшая корректировка используемого файла, а точнее места его нахождения. Достаточно сохранить используемый файл и работу с ним можно продолжить.
Предыдущая запись ТОП-5 бесплатных онлайн-игр
Следующая запись Как полностью удалить DirectX с компьютера
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Как изменить внешний вид (формат) сводной таблицы
Про настройку форматирования сводной таблицы есть видеоролик:
В статье кратко остановимся на возможностях.
Изменить внешний вид сводной таблицы можно на вкладке Конструктор.
Выпадающий список Промежуточные итоги позволяет управлять выводом промежуточных итогов по строкам. Промежуточные итоги можно отключить совсем, или выводить в верхней или нижней части группы.
Общий итоги — позволяет управлять общими итогами для строк или столбцов, выводить их или отключать.
Макет отчета — позволяет изменять структуру таблицы. По умолчанию сводная таблица создается в сжатой форме. Но можно перестроить ее в форме структуры или в табличной форме.
Пустые строки — этот выпадающий список редко используется, позволяет добавлять пустую строку между группами, чтобы визуально отделить их.
Сводные таблицы в Excel: Анализ данных с помощью сводных таблиц
Сводные таблицы носят универсальный характер благодаря тому, что позволяют подытоживать данные с помощью множества функций (хотя итоги, создаваемые с помощью функции СУММ, на практике остаются самыми востребованными). При создании исходной сводной таблицы приходится принимать несколько решений: какую итоговую функцию использовать, к каким столбцам (полям) ее применить и по каким столбцам (полям) вычисленные значения должны формировать таблицу.
Сводные таблицы идеальны для перекрестного сравнения двух наборов данных. Например, можно создать сводную таблицу на основе базы данных сотрудников, суммирующую зарплату сотрудников разных подразделений под каждой категории должностей.
Урок 46. Как создать сводную таблицу в Excel?
Приветствую вас, посетители блога!
При аналитике различных рекламных инструментов необходима простота и наглядность отчетов. Смотреть на сухие цифры не очень-то и хочется, да и глаза разбегаются от их большого количества. Сегодня вы узнаете, как создать сводную таблицу в Excel. Это поможет вам составить наглядные отчеты по эффективности рекламных кампаний в Яндекс.Директ или Google Adwords.
Ну так как блог о интернет-маркетинге, то и составлять мы будем сводную таблицу из отчета о расходах рекламных кампаний, объявлений и ключевых фраз.
Еще кое-что: статья написана не только для тех, кто занимается интернет-маркетингом, но и для людей, занимающимся другими сферами деятельности. Поэтому этот урок я разделю на две части:
- Выгрузка отчета из Метрики;
- Создание сводной таблицы;
Тем, кто не интересуется интернет-маркетингом первую часть можно не читать!
Выгрузка отчета из Метрики
Итак. Давайте выгрузим отчет о расходах рекламных кампаний Директ из Яндекс.Метрики в XLSX-файл. Для этого перейдите в отчет “Директ-расходы”:
Затем нажмите на небольшую кнопку “Экспорт” в правом верхнем углу отчета и выберите “XLSX” из графы “Данные из таблицы”:
Отлично! Отчет выгружен, теперь можно переходить к созданию сводной таблицы.
Создание сводной таблицы
Прежде, чем мы начнем создавать сводную таблицу, необходимо подготовить исходную таблицу. Что под этим подразумевается:
- Убираем все объединенные ячейки;
- Убираем пустые ячейки;
После всех подготовок исходная таблица примет вид:
Еще раз перепроверьте табличку. Наличие пустых и объединенных ячеек неприемлемо для сводной таблицы.
Ну-с начнем создавать:
- Перейдите во вкладку “Вставка”:
- Во вкладке “Вставка” найдите кнопку “Сводная таблица” (крайняя с лева). Нажмите на нее:
- В появившемся окошке укажите куда поместить отчет: на новый лист или на существующий. Нажмите “ОК”:
И вот перед вами конструктор сводной таблицы. Наша задача – перетащите все необходимые данные таблицы в определенные поля сводной таблицы. Делается это в области с права:
Как видите здесь четыре области:
- Фильтр отчета. Предназначается для фильтрации данных;
- Названия строк. Сюда переносятся анализируемые поля. Например, рекламная кампания, объявление и ключевая фраза;
- Названия столбцов. Здесь указываются значения. Подставлять ничего не нужно, данные из поля значения перенесутся автоматически;
- Значения. Сюда переносятся данные, с помощью которых анализируются поля, перенесенные в область “Названия строк”;
- Кажется доходчиво объяснил.
- Итак, вот пример моей сводной таблицы, где я просматриваю расходы, переходы по каждой кампании, по каждому объявлению, по каждой фразе:
- В области “Названия строк” сохранена изначальная иерархия, то есть сперва кампании, потом объявления, затем ключевые фразы. Благодаря этому сводная таблица принимает следующий вид:
- Как видите, все данные вложены по порядку. А вся таблица выглядит так:
- Как создать сводную таблицу в Excel вы теперь знаете, осталось только сделать ее восприятие проще.
Упрощаем восприятие данных таблицы
Отчет имеет не очень-то и красивое оформление и приходиться много крутить глазами, чтобы четко разобрать цифры. Для более лучшего восприятия оформим таблицу так:
Сделаем это следующим образом:
- Во вкладке “Конструктор” выберите любой, понравившийся вам, стиль оформления таблицы:
- Затем переименуйте столбцы. Дважды щелкните по столбцу и дайте название;
- Присвойте денежный формат столбцу с расходами. Нажмите правой кнопкой мыши на ячейке с данными о расходах и выберите “Числовой формат”, затем в списке с лева найдите пункт “Денежный формат”:
- Указываем гистограмму в каждой ячейке столбца “Расходы”. Выделите ячейки столбца “Расходы” и на вкладке “Главная” найдите кнопку “Условную форматирование”, нажмите на нее. В выпадающем списке выберите пункт “Гистограмма”:
Результат:
Теперь вы наглядно видите на какую кампанию больше всего тратите средств. В данном примере на кампанию “Котлы – Поиск” больше всего тратится денежных средств. Это может означать только одно – необходимо оптимизировать цену клика посредством увеличения CTR. Этот процесс называется ведение рекламных кампаний.
Ну а на этом все! Думаю, что на вопрос “Как создать сводную таблицу в Excel?” я ответил.
До свидания!
Не забывайте подписываться и оставлять комментарии к уроку.
Макет сводной таблицы
Ну и выбор макета также влияет на внешний вид сводной таблицы. Есть три варианта.
Первый — сжатая форма. Этот вариант по умолчанию и мы его видим сразу после создания сводной таблицы.
При выборе второго варианта — форма структуры, в сводной таблице под каждое поле будет выделен отдельный столбец. То есть в первом столбце теперь выводится только категория товара, а сами товары отображаются во втором столбце.
Табличная форма аналогична форме структуры, но промежуточные итоги из строки с названием категории перемещаются вниз.
В этом же меню есть еще одна настройка, позволяющая повторять или не повторять подписи элементов.
Сейчас категория отображается только в одной строке и это вариант с не повторяющимися подписями. Если выбрать второй вариант, то название категории будет дублироваться в каждой строке.
Ну а теперь со знанием дела приведем отчет к нужному виду — вернем сводной таблице сжатую форму, а затем перенесем промежуточные итоги вниз каждой категории.
С помощью соответствующего инструмента вставим пустые строки после каждой категории, чтобы визуально их отделить друг от друга.
Ну а чтобы быстро свернуть или развернуть все категории можно воспользоваться контекстным меню, вызванным щелчком правой кнопки мыши на соответствующей ячейке. Здесь есть раздел, в котором выбираем нужный вариант.
Ну а если кнопки свертывания не нужны, то можно их скрыть. Для этого на контекстной вкладке Анализ отключим их отображение.
Подкорректируем заголовки, выберем подходящий стиль и наш отчет готов.
Метод 5: формула для удаления повторяющихся строк
Последний метод достаточно сложен, и им мало, кто пользуется, так как здесь предполагается использование сложной формулы, объединяющей в себе несколько простых функций. И чтобы настроить формулу для собственной таблицы с данными, нужен определенный опыт и навыки работы в Эксель.
Формула, позволяющая искать пересечения в пределах конкретного столбца в общем виде выглядит так:
Давайте посмотрим, как с ней работать на примере нашей таблицы:
Как уже было сказано выше, этот метод сложен и функционально ограничен, так как не предполагает удаления найденных столбцов. Поэтому, при прочих равных условиях, рекомендуется использовать один из ранее описанных методов, более логически понятных и, зачастую, более эффективных.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Как удалить сводную таблицу в Excel — Офис Ассист
- Выбрать в таблице нужную область.
- Открыть вкладку «Данные» — «Сортировка» — «Дополнительно».
- В окне расширенного фильтра установить переключатель таким способом, чтобы результат его работы был скопирован в другое место.
- Выбрать в строке исходного диапазона всю таблицу или ее часть, а в строке диапазона для перемещения установить место верхнего левого угла.
- Поставить галочку напротив выбора только уникальных значений и нажать «ОК».
Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.
Как убрать группировку в сводной таблице?
Чтобы убрать структуру таблицы Excel , выделяем ее и нажимаем стрелку у кнопки «Разгруппировать» Выбираем функцию «Удалить структуру». Удалятся все группировки. Чтобы удалить конкретную группировку, например, одного раздела по строкам, выделяем эти строки, нажимаем на кнопку «Разгруппировать».
Формирование сводной таблицы из нескольких файлов (таблиц) Excel
Это более сложная, но вполне выполнимая задача. Используем мастер СТ, который нужно добавить на панель быстрого доступа.
Итак, слева, возле пункта «Файл», жмём на стрелочку, направленную вниз, выбираем пункт «Другие команды». Откроется новое окно, в средней панели щёлкаем на опции «Все команды». Находим в открывшемся перечне «Мастер сводных таблиц», выбираем его и кликаем на кнопе «Добавить». Соответствующий значок появится в верхней панели.
Запускаем мастер. Допустим, нам нужно объединить данные из двух листов, майского и за июнь. Главное условие – они должны совпадать по структуре. Если это так, то в первом окне мастера выбираем опции «Сводная таблица» и следующий параметр – «Несколько диапазонов консолидации».
На втором шаге указываем Мастеру, что нам нужно несколько полей. На третьем – выбираем диапазон ячеек для суммирования по первому листу, жмём «Добавить», и проделываем то же самое для второго листа Excel. Теперь нужно дать имена двум полям. Делаем это сначала для первого поля (жмём селектор «1», в графу «1 поле» вписываем «Май»), затем, по аналогии, для второго. При этом нужно будет указать в списке диапазонов соответствующую строку, первую или вторую.
После нажатия кнопки «Готово» откроется новое окно, в котором указываем «Новый лист» и снова жмём кнопку «Готово».
ВНИМАНИЕ. Получение многомерных таблиц неудобно тем, что в ней сложно ориентироваться при большом количестве полей, да и нужные расчёты приходится проводить, каждый раз указывая нужные значения из верхней области таблицы.
Как удалить из фильтров сводной таблицы “старенькые” данные?
Кулешова Ольга Владимировна: Как удалить из фильтров сводной таблицы “старенькые” данные?
Ситуация довольна обычная. По источнику данных происходит построение отчета сводной таблицы. Потом в источнике происходит изменение данных, но при обновлении отчета в фильтрах видны как прежние, так и новейшие значения. Как избавиться от “старенькых” (прежних) данных?
К примеру, строим отчет по источнику, в котором есть данные 4-х городов:
Сейчас произведем подмену в начальных данных, к примеру, город Казань заменим на Ульяновск:
Но, выполнив команду Обновить в сводной таблице (клавиши Alt+F5), данные показываются в ячейках корректно в согласовании с данными источника:
но в фильтрах осталось и прежнее значение — Казань:
Чтоб убрать из фильтра уже не имеющиеся данные, следуют в контекстном меню отчета избрать команду Характеристики сводной таблицы и на вкладке Данные в перечне Число частей, сохраняемых для всякого поля избрать Нет .
выполнить обновление (Alt+F5), опосля что элементы в фильтре содержатся животрепещущие:
Заказ добавлен в Корзину.
Для окончания дизайна, пожалуйста, перейдите в Корзину!
Как убрать сетку в Excel 2010 полностью и частично
Строки таблицы ниже диапазона. Если вставляемые столбца, вставьте скопированное на листе можно от ее содержимого. таблице снизу (справа).Главная
Скрытие сетки листа
Excel – это закладке «Границы», а или для оформления- ссылка наПри создании нового столбцаВ результате после такого не упоминается, наверное, обязательно целиком).
изменение и ОтменаСтроки таблицы. данные содержат больше
- значение в ячейку легко добавлять и Вероятно, Вам потребуется В этом случае
- в группе команд уже одна огромная для его вызова специальных шаблонов и всю таблицу, включая с формулой - преобразования диапазона в самый простой способНа Ленте меню под условного форматирования.илиВ приведенном ниже примере столбцов, чем таблица, или добавьте новые удалять строки и
немного поэкспериментировать с строка или столбецСтили таблица, предназначенная для
жмите CTRL+SHIFT+F (или
интерфейсов, сетка мешает заголовки столбцов, данные она будет автоматически «умную» удалить таблицу (или заголовкомУбедитесь, что активная ячейкаСтолбцы таблицы новая строка будет лишние столбцы не строки или столбцы
будут автоматически включенынажмите команду хранения самых различных CTRL+1). и ее необходимо и строку итогов скопирована на весь
Фильтрация отдельных полей строк и столбцов
Кнопки фильтра в полях строк и столбцов, прикрепленные к названиям, позволяют фильтровать записи в конкретных группах значений источника данных. Для фильтрации данных в столбцах или строках сводной таблицы щелкните на такой кнопке и установите флажок равным значению (Выделить все) в верхней части раскрывающегося списка. Затем установите флажки всех групп или отдельных записей, итоговые значения которых вам нужны в сводной таблице, и щелкните на кнопке ОК.
Как и в случае фильтрации по полю фильтра, Excel заменяет стандартный значок конусообразной пиктограммой фильтра, свидетельствующей о том, что поле в настоящий момент отфильтровано по одному или нескольким своим значениям, которые и отображаются в сводной таблице. Чтобы снова отобразить все значения поля столбца или строки, щелкните на кнопке фильтра, а затем — на пункте (Выделить все) в самом верху раскрывающегося списка.
На скриншоте ниже показан пример сводной таблицы после фильтрации по дате (выбрана дата 1 января) и категории (выбраны Одежда, Питание и Хоз. Расходы).
Категория Хоз. Расходы не отображается в списке по причине отсутствия этой категории расходов за 1 января.
Помимо отдельных записей, в сводной таблице можно фильтровать группы записей, которые соответствуют определенным критериям (например, названия городов начинаются с заданных букв или величина зарплаты находится в определенных рамках). Для выполнения подобной фильтрации используются фильтры по подписи или фильтры по значению, доступные в дополнительных меню.
Базовые особенности работы с таблицами
Один из самых главных элементов таблицы – ее название. Его можно увидеть во вкладке «Конструктор». Она отображается сразу после того, как будет нажата левая кнопка мыши на любую ячейку, входящую в нее. Название есть, даже если пользователь его не задает. Просто в таком случае по умолчанию дается имя «Таблица 1», «Таблица 2» и другие.
Если вы собираетесь использовать сразу несколько таблиц в вашем документе, то рекомендуем дать более понятные имена. В будущем тогда будет значительно проще понять, какая из них за что отвечает
Особенно это важно при работе с Power Query и Power Pivot. Давайте присвоим таблице имя «Отчет»
В Excel есть отдельная функция, предназначенная для того, чтобы просмотреть, какие таблицы и именованные диапазоны есть и быстро управлять их названиями. Для того, чтобы ею воспользоваться, необходимо открыть вкладку «Формулы», после чего найти пункт «Диспетчер имен».
Увидеть название таблицы можно и при ручном вводе формулы.
Но больше всего любопытно то, что Excel может работать не только с таблицей в целом, но и отдельными ее частями – колонками, заголовками, итогами и так далее. Чтобы сослаться на какой-то конкретный компонент, необходимо записывать формулы в таком виде.
Начинающий пользователь сразу скажет: «Боже, как можно все это выучить»? Но на самом деле, этого не нужно делать, поскольку в ходе набора формулы появляются подсказки. Главное – не забыть открыть квадратную скобку (ее можно найти в английской раскладке там, где у нас находится кнопка «х»).
8
Переключение между компонентами таблицы осуществляется с помощью клавиши Tab. После того, как формула введена, не стоит забывать закрыть все скобки, включая квадратную.
Если в любой ячейке записать формулу, возвращающую сумму всего столбца «Продажи», то она автоматически обретет такой вид.
=Отчет
Простыми словами, ссылка указывает не на какой-то определенный диапазон, а на всю колонку таблицы.
Это говорит о том, что если использовать умную таблицу в диаграмме или сводной таблице, новая информация туда будет добавляться автоматически.
Фильтрация полей отчета
Пожалуй, наиболее важной кнопкой сводной таблицы является кнопка фильтрации полей отчета. Выбрав конкретные пункты из раскрывающегося списка, прикрепленного к кнопке фильтрации, вы увидите в таблице только сводные данные для этого подмножества
Например, в нашей сводной таблице, в качестве поля фильтрации используется Дата. В результате можно отображать данные о расходах и доходах за конкретный период. Для этого сделайте следующее.
Щелкните на кнопке фильтрации по Дате, в раскрывающемся списке выберите «1 января», а затем щелкните на кнопке ОК. В результате отобразятся расходы и доходы только за 1 января.
Аналогичным образом можно выбирать несколько дат. Для этого установите галочку напротив пункта Выделить несколько элементов.
Если позже вы решите отображать расходы и доходы за весь период, снова установите флажок в поле (Все) раскрывающегося списка фильтрации и щелкните на кнопке ОК.
Как создать сводную таблицу?
Для начала убедитесь, что у Вас есть какие-то исходные данные на листе Excel. Перечень финансовых операций – самое типичное, что встречается. На самом деле, это может быть перечень чего угодно: контактные данные сотрудников, коллекция компакт-дисков или данные о расходе топлива Вашей компании.
Итак, запускаем Excel… и загружаем такой список…
После того, как мы открыли этот список в Excel, можем приступить к созданию сводной таблицы.
Выделите любую ячейку из этого списка:
Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):
Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:
- Какие данные использовать для создания новой сводной таблицы?
- Куда поместить сводную таблицу?
Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):
Excel создаст новый лист и разместит на нем пустую сводную таблицу:
Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).
Список полей в верхней части диалогового окна – это перечень всех заголовков из исходного списка. Четыре пустые области в нижней части экрана позволяют указать сводной таблице, как требуется обобщить данные. Пока эти области пусты, в таблице тоже ничего нет. Всё, что от нас требуется, это перетащить заголовки из верхней области в пустые области внизу. При этом автоматически формируется сводная таблица, в соответствии с нашими инструкциями. Если мы допустили ошибку, то можно удалить заголовки из нижней области либо перетащить другие им на замену.
Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):
Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован. Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы
Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы.
Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем есть сейчас.
Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Т.е. в нашу сводную таблицу добавятся строки с именем каждого продавца компании и его итоговой суммой продаж. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson (Торговый представитель) в область Row Labels (Строки):
Становится интересней! Наша сводная таблица начинает обретать форму…
Видите преимущества? За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго.
Что ещё мы можем сделать? Ну, в определённом смысле, наша сводная таблица уже готова. Мы создали полезную сводку по исходным данным. Уже получена важная информация! В оставшейся части статьи мы разберём некоторые способы создания более сложных сводных таблиц, а также узнаем, как их настраивать.
Что такое сводная таблица
Сводная таблица в Excel (или pivit table, или “пивотная таблица”) — это особым образом организованная и структурированная таблица, которая выводит данные в нужном пользователю разрезе и производит необходимые вычисления без использования формул.
Например, у нас есть база данных с продажами магазина для садоводов. В ней собрана информация обо всех продажах, детализированная до каждого кассового чека. Это огромная таблица с большим количеством строк, из которой невозможно взять какую-то ценную аналитическую информацию.
Предположим, нужна информация о сумме выручки в разрезе категории товара. Или по месяцам. Конечно, можно взять все категории товара и функцией СУММЕСЛИМН вытянуть нужные данные, но это займет намного больше времени. К тому же, если у вас завтра появится новая категория, ее нужно будет вручную добавить в таблицу (не забыть об этом, как минимум). Если же построить сводную таблицу в excel, они это сделает это за вас.