000818.ru

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

Как обновить сводную таблицу в файле, открытом в Excel?

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

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

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

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

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

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

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

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

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

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

Щелкните OK, и появится следующий конструктор:

Макет

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

Ниже перечислены пункты в правой части (поля и области):

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

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

Теперь все приведено в порядок.

Проблемы совместимости между Excel 2007 и Excel 2010

Хорошей новостью является то, что Microsoft продолжает вкладывать значительные средства в дальнейшее развитие электронных таблиц. С одной стороны, это хорошо, но с другой — создает проблемы несовместимости с предыдущей версией Excel 2007. Некоторые из этих проблем обсуждаются в данной публикации.

Исчезновение срезов в Excel 2007

До появления Excel 2010 слайсы были недоступны пользователям Excel 2007. В результате при открытии рабочей книги Excel 2010, содержащей слайс, в версии Excel 2007 вместо слайса отображается окно, любезно информирующее пользователя о том, что слайс был создан в более новой версии Excel и не может быть отображен в текущей версии.

Однако самая большая проблема заключается не в том, что Excel 2007 не может отображать срезы, а в том, что, хотя сводная таблица была создана в Excel 2010 и открыта в Excel 2007, она включает информацию о Восточном регионе, объем продаж которого составляет 2,4 миллиона долларов.

Читайте так же:
Как отключить кнопку после однократного нажатия в Excel?

Рис. 8.1. С помощью среза в сводной таблице, созданной в Excel 2010, отображаются сведения только о регионе Восток

Рисунок 8.1. Используя срез, электронная таблица, созданная в Excel 2010, показывает информацию только для Восточного региона

Если рабочий лист, созданный в Excel 2010, открыть в окне Excel 2007, вместо среза появится объект Excel Shape. Этот объект содержит предупреждающий текст о том, что срезы не могут быть использованы в текущей версии Excel. Однако, поскольку вместо фильтра отчета используется срез, который невидим, пользователи не знают, почему продажи составляют всего $2,4 млн. (рис. 8.2).

Рис. 8.2. При открытии книги в Excel 2007 срез исчезает. В результате можно ошибочно предположить, что сумма дохода по всем регионам составляет всего лишь 2,4 млн долл.

Рисунок 8.2. Срез исчезает, как только вы открываете электронную таблицу в Excel 2007. Это означает, что мы можем ошибочно предположить, что доход для всех регионов составляет всего 2,4 миллиона долларов.

Итоговое значение в сводной таблице Excel 2007, созданной с помощью Excel 2010, автоматически изменится (рис. 8.3). Правда, для этого изменения есть причина, поскольку фильтр, реализованный в форме среза, больше не хранится в сводной таблице.

Рисунок 8.3: После обновления сумма в поле Доход изменилась без видимой причины.

Рассмотрим пример создания электронной таблицы в Excel 2010. Добавьте раздел поля Регион в эту таблицу, затем переместите поле Регион в область фильтра отчета. В этой области установите фильтр по Западному региону. Сохраните файл электронной таблицы в Excel 2010 и откройте его в Excel 2007. Используя фильтр отчета, выберите регион Центр вместо региона Запад. Если в этот момент сохранить файл электронной таблицы в Excel 2007, а затем открыть его в Excel 2010, вырезка снова появится в таблице, но отображаемые данные не будут точными. Хотя вырезка была создана для Западного региона, фильтр отчета установлен на регион Центр, и цифры в таблице соответствуют всем регионам (Рисунок 8.4).

Рис. 8.4. В результате сохранения сводной таблицы в Excel 2010, пересохранения Excel 2007 и возврата к Excel 2010 срез остается, хотя отображенные в сводной таблице значения не будут соответствовать действительности

Рисунок 8.4. Сохранение рабочего листа в Excel 2010, повторное сохранение в Excel 2007 и возвращение в Excel 2010 оставит разрыв, хотя значения в рабочем листе не верны.

Результат вычисления процента от родительского элемента в Excel 2007 не обновляется

Некоторые вычисления, доступ к которым можно получить через выпадающее меню кнопки «Показать значения как», недоступны в предыдущих версиях Excel. Например, если выбрать % Current Total в поле % Parent Item или % Cumulative Total, откроется электронная таблица Excel 2007 и даже отобразит некоторые результаты. В Excel 2007 вместо дополнительных вычислений в опциях поля отображается Нет вычислений (Обычный). Это означает, что ничего не произойдет, если вы обновите поле сводной таблицы в Excel 2007, используя функцию % parent или % cumulative total в этом поле.

В Excel 2007 отсутствует возможность повторения всех подписей элементов

Настройка макета отчета «Повторять все подписи элементов» (Repeat All Item Labels) отсутствует в Excel 2007. Если созданную в Excel 2010 сводную таблицу, для которой выбрана эта настройка, открыть в Excel 2007, то подписи всех элементов сначала будут повторяться. Но после внесения изменений в сводную таблицу повторяющиеся подписи исчезнут, и после открытия этой таблицы в Excel 2010 не появятся.

Читайте так же:
Как определить последовательность недостающих чисел в Excel?

Созданные в Excel 2007 сводные таблицы полностью совместимы с Excel 2010

При создании электронной таблицы в Excel 2007 и открытии ее в Excel 2010 все ее функции остаются неизменными. Кроме того, в электронную таблицу можно добавить функции Excel 2010, чтобы расширить ее возможности.

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

  • Возможно, вы добавляете новые данные в строки за пределами диапазона сводной таблицы.
  • Ваша таблица pivot может использовать фильтры
  • Ваш набор данных может содержать формулы, которые необходимо пересчитывать, например, RANDOM, TODAY и т.д. Д.

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

Проблема 1: сводная таблица не обновляется при добавлении новой строки

Перекрестные таблицы работают с данными в заданном диапазоне ячеек исходного листа. Добавление новой строки за пределами диапазона перекрестной таблицы не повлияет на нее.

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

Решение

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

Например, если ваши данные в настоящее время заполняют более 20 строк, вы можете создать электронную таблицу с 1000 строк. Еще 980 строк можно оставить пустыми и заполнить их данными позже по мере необходимости.

Пока ваши данные находятся в пределах 1000 строк, сводная таблица будет обновляться автоматически, если ваши данные не превышают 1000 строк.

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

Проблема: ваша сводная таблица использует фильтры

Данные не будут обновляться при изменении значений исходных данных, если вы используете фильтры в сводной таблице. Это ограничение Google Spreadsheets.

Решение

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

  • Нажмите на крестик рядом со всеми полями категории Фильтры в редакторе сводной таблицы.
  • Внесите необходимые изменения в исходный набор данных.
  • Теперь изменения должны быть отражены в сводной таблице.
  • После завершения изменений добавьте фильтры снова, используя кнопку Добавить в категории Фильтры (в редакторе перекрестных таблиц).

Проблема: сводная таблица не обновляется, если исходный набор данных содержит такие функции, как RANDOM, TODAY и т. Д.

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

Читайте так же:
Как объединить одноименные листы в один лист?

Решение

Единственный способ решить эту проблему — просто избегать использования таких функций.

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

Функция принудительного обновления изначально была доступна в Google Таблицах, но в новых версиях она была удалена, поскольку функция неявного обновления имеет больше смысла.

Однако проблемы, когда сводные таблицы не обновляются по причинам, описанным в этой главе, встречаются довольно часто, и Google следует решить эту проблему как можно скорее.

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

Как использовать

Сводные таблицы в Excel для чайников представляются чем-то очень сложным и непонятным. На самом же деле не все так страшно. Перед тем как сделать сводную таблицу в Excel, необходимо «раздобыть» для нее исходные данные. Получают их как автоматически, выгрузив необходимую информацию из 1С или другой программы, например, системы ЭДО, так и в ручном режиме, создав документ со всеми необходимыми данными. Идеальный вариант, если сам учет деятельности ведется в Эксель, тогда никаких дополнительных действий совершать не придется. Главное — проверить, что исходный массив соответствует следующим требованиям:

  • Нет связанных клеток;
  • Нет пустых строк и столбцов;
  • Во всех столбцах есть заголовки.

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

Создаем базу Excel с помощью функции «Вставка» — «Таблица» — «Сводная таблица».

В результате получается следующее

Мне еще предстоит показать вам, как работать с перекрестными таблицами в Excel. Изменение вида отчета заключается в снятии и установке флажков в списке полей. Давайте отобразим общую сумму транзакций каждого менеджера.

Теперь добавим виды продаж.

Как сделать вычисления

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

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

Если данные в исходном массиве изменились, базу необходимо обновить. Добавим менеджера Самуйлову в исходные данные, поставим курсор в любую ячейку базы и обновим результат сведений с помощью вкладки «Анализ» — «Обновить данные».

Чтобы настроить автоматическое обновление данных при открытии файла, необходимо установить галочку в соответствующем месте (вкладка «Анализ» — «Параметры» — «Данные»).

Удалите основание, выделив его и нажав на клавишу Delete.

Сводная таблица в excel как сделать

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

  1. Запустите Excel 2007. В верхней панели выберите вкладку «Вставка».
  2. Выберите «Создать рекомендуемые сводные таблицы».
  3. Появится окно с параметрами таблицы. Вы можете предварительно просмотреть, как будет выглядеть ваш отчет, щелкнув на макете. Затем вы можете выбрать основную. Нажмите OK, и отчет появится на листе. Кроме того, Excel сам определит порядок следования строк.
  4. Вы можете фильтровать и сортировать значения в программе. Для этого на панели есть специальный значок.
Читайте так же:
Как объединить несколько диапазонов в одну сводную таблицу в Excel?

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

Сводные таблицы в excel 2010 и 2013 года – пример работы в excel

Создание отчета в Excel 2010 и 2013 практически идентично. Процесс в этих версиях немного отличается от версии 2007 года. Однако некоторые моменты схожи: прежде чем начать, следует создать простую таблицу, содержащую все необходимые вам данные. Верхняя строка должна содержать названия столбцов.

Теперь вы можете создать отчет в Excel:

Сводная таблица Excel – учимся создавать

  • Запустите программу Excel. На главной панели есть вкладка «Вставка», перейдите туда.
  • Справа появится значок «Сводная таблица». Нажмите на него.
  • Откроется окно для создания отчета. Вы увидите перед собой пустой ряд, который необходимо заполнить. Для этого выберите таблицу, содержащую данные, которые вы создали ранее.
  • Теперь укажите, где разместить отчет. Лучше всего делать это на новом листе.
  • Появится кнопка «OK», нажмите на нее.
  • После этого перед вами откроется лист с вашим отчетом.

Что вы можете сделать с листом?

  • У вас будет доступ к панели инструментов для управления значениями. Все поля и их заголовки появятся на вкладке «Выберите поля для добавления в отчет». Чтобы добавить нужные элементы, необходимо выбрать нужные поля, и список будет создан.
  • Есть возможность управлять расположением полей. Вы можете перетаскивать колонки в нужные окна и располагать их в нужном порядке. Вы также можете фильтровать элементы. Для этого перетащите элемент в окно фильтра. Если вам нужно выполнить расчет для отдельного поля, перетащите его в окно «Значение».
  • Вы можете использовать дополнительные опции. Найдите их следующим образом: «Опции» ► «Работа с таблицами pivot». Существует множество инструментов, которые предлагают большое количество дополнительных настроек и функций.

Как создать сводную таблицу при помощи мастера сводных таблиц

На главной панели отсутствует инструмент Crosstab Wizard. Поэтому сначала необходимо его активировать. Перейдите в меню Файл, затем Параметры, затем Панель инструментов быстрого доступа. Перед вами откроется список «Выбрать команды из», и вы отметите «Команды ленты». Теперь выберите мастер перекрестных таблиц и добавьте его. После выполнения всех шагов на главной панели появится значок выбранной функции.

Чтобы создать сводный отчет, нажмите кнопку Мастер:

Сводные таблицы в MS Excel – пошаговая инструкция

  1. Запустите мастер из панели инструментов быстрого доступа. Откроется окно, в котором необходимо установить переключатель на нужный источник данных. Их несколько: первый — источник, который находится либо в списке, либо в базе данных Excel. База данных электронной таблицы будет использоваться в качестве источника, если она будет создана, а вторая — это внешний источник данных. Если необходимо подключить другую базу данных, ее можно использовать. Третий — источник, который встречается в различных консолидациях. Этот пункт выбирается, если вы хотите сделать консолидацию различных источников, четвертым из которых является источник, находящийся в другой таблице или диаграмме. В качестве источника выбирается рабочий лист, уже созданный в Excel.
  2. Первый — это источник, который находится в списке или в базе данных Excel. База данных электронной таблицы, если она была создана, будет использоваться в качестве источника.
  3. Второй — внешний источник данных. Если необходимо подключить другую базу данных, ее можно использовать.
  4. Третий — это источник, который находится в различных консолидациях. Этот пункт выбирается, если вы хотите выполнить консолидацию различных источников.
  5. Четвертый — это источник, который находится в другой таблице или диаграмме. В качестве источника выбирается таблица pivot, которая уже была создана в Excel.
  6. После того как вы определились с источником отчета, необходимо выбрать его тип. Это может быть простая таблица или график с таблицей.
Читайте так же:
Как отключить редактирование в текстовом поле, чтобы пользователь не вводил данные в Excel?

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

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

Сортировка данных в таблицах Эксель

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

Теперь вам нужно нажать кнопку «Далее». Вы выбираете местоположение отчета. Нажмите «Готово».

Сводные таблицы excel 2010 из нескольких листов

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

Cводная таблица Эксель – что это такое

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

Теперь следует создать сводный отчет:

  • Вставка.
  • Краткий отчет.
  • Введите диапазон и местоположение.
  • OK.

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

Поэтому мы узнали, как создать перекрестные таблицы в Excel. Результат не займет много времени, если вы будете усидчивы и внимательны.

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