Ref-sgain.ru

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

Как создать динамическую сводную таблицу для автоматического обновления расширяемых данных в Excel?

Как создать динамическую сводную таблицу для автоматического обновления расширяемых данных в Excel?

Чтобы узнать больше о кросстабах, прочитайте эту статью.

Проблема

Иногда данные, подлежащие анализу, поступают к нам в виде отдельных таблиц, которые, тем не менее, должны быть связаны между собой. MS Access может сделать это легко, тогда как в Excel для этого всегда приходилось использовать формулы VLOOKUP. Однако, начиная с Excel 2013, появилась возможность использовать несколько таблиц, связанных ключевыми полями, в качестве источника при создании электронной таблицы.

Пример

В нашем примере у нас есть 4 таблицы: Заказы, строки заказов, товары, клиенты.

Таблица сроков выполнения заказов :

Исходные таблицы разработаны как интеллектуальные таблицы: Заказы, строки заказов, продукты и клиенты.

Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа , таблицы Orders и Clients — по полю ID_клиента , таблицы OrderLines и Goods — по полю ID_товара .

Скачать пример

Создание модели данных

Создайте сводную таблицу на основе любой из имеющихся таблиц.

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

На панели Crosstab Fields также есть возможность выбрать OTHER TABLES (ДРУГИЕ ТАБЛИЦЫ).

Давайте нажмем на нее. Появится вопрос:

Ответив Да, мы видим, что все наши таблицы добавлены в список полей:

Если вы начнете выбирать поля, то через некоторое время в списке полей появится кнопка Создать.

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

Таким же образом создавайте другие ссылки.


Диалоговое окно Управление отношениями доступно через команду ленты АНАЛИЗ ОТНОШЕНИЙ.

Чтобы отобразить больше полей в панели «Поля» сводной таблицы, можно выбрать этот вид с помощью кнопки «Инструменты» (в виде значка шестеренки):

Результат будет следующим:

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

Читайте так же:
Как скрыть кнопку выбора при скрытии строки в Excel?

Просто и удобно!

Читайте также:

Введение в сводные таблицы
Автоматизация форматирования сводных таблиц

0

Проблема, поднятая Николаем очень правильная. Тут действительно не всё так просто. Поэтому подумал, что мой ответ будет интересен и другим читателям этой статьи:
————————————-
Николай,здравствуйте.

Я понимаю ваши затруднения. Например, чтобы посчитать стоимость какого-либо
товара в заказе, надо [OrderLines].[количество]
умножить на [Goods].[Цена]. Это делается при помощи
вычисляемого поля, которое вы создать в меню Анализ сводной таблицы не можете,
так как эта таблица построена на основе Модели данных, а это уже часть PowerPivot функционала. Добавлять
вычисляемый столбец надо через модуль PowerPivot,
который у вас в Excel будет
только в версии Prof Plus. Речь идёт про MS Office 2013.

0

Получил такое письмо:
——————————-
Денис, здравствуйте,
спасибо за вашу статью про сводные таблицы по нескольким диапазонам.
http://perfect-excel.ru/publ. -1-0-67

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

Например, составляйте отчеты, подобные этому.

— вид продукта — общая стоимость согласно заказам
— клиент — общая сумма заказов
— заказ № — стоимость заказа
и т.п.

именно через сводные таблицы, а не модификацией текущих четырех таблиц.
Заранее большое спасибо.

Как создать таблицы в Excel

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

Работа с таблицами в Excel может показаться сложной на первый взгляд начинающим пользователям. В Word таблицы строятся по другим принципам. Мы начнем с малого: с таблицы. Когда вы закончите читать статью, вы уже поймете, что Excel — лучший инструмент для создания диаграмм.

КАК СОЗДАТЬ ТАБЛИЦУ В EXCEL ДЛЯ ЧАЙНИКОВ

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

Внимательно изучите таблицу табличной обработки:

Электронная таблица.

Он представляет собой набор ячеек в столбцах и строках. По сути, это стол. Колонки написаны латинскими буквами. Ряды в цифрах. Если вы распечатаете этот лист, вы получите чистую страницу. Нет границ.

Читайте так же:
Как скопировать и вставить строки или столбцы в обратном порядке в Excel?

Сначала научимся работать с ячейками, строками и колонками.

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

Вы также можете выбрать весь столбец, щелкнув левой кнопкой мыши его название (на латинице).

Выделить столбец.

Чтобы выбрать линию — по названию линии (по номеру).

Выделить строку.

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

Чтобы выделить столбец с помощью горячих клавиш, установите курсор в любую ячейку нужного столбца — нажмите Ctrl + пробел. Для выделения строки — Shift + пробел.

Как изменить границы ячеек

Для того чтобы таблица поместилась, границы ячеек необходимо изменить:

  1. Перемещайте вручную, зажав границу ячейки левой кнопкой мыши.
  2. Когда вы набираете длинное слово в ячейке, дважды щелкните по границе столбца/строки. Программа автоматически расширит границы.
  3. Если вы хотите сохранить ширину колонки, но увеличить высоту строки, используйте кнопку «Переместить текст» на панели инструментов.

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

Ширина столбцов.

Примечание. Для возврата к предыдущему размеру можно использовать CTRL+Z или кнопку «Undo». Однако это сработает, если вы сделаете это сразу. Позже это не сработает.

Линии вернутся к своим первоначальным границам, если нажать «Главная»-«Формат» и выбрать «Автоматическая регулировка высоты линий».

Автоподбор высоты строки.

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

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

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

Место для вставки столбца.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Добавить ячейки.

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

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

Читайте так же:
Как скрыть строки по цвету ячеек в Excel?

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

  1. Вручную заполните заголовок — названия столбцов. Мы вводим данные — заполняем строки. Немедленно примените на практике полученные знания — расширьте края столбцов, «восстановите» высоту рядов.
  2. Чтобы заполнить столбец «Стоимость», установите курсор в первую ячейку. Введите «=». Это сообщает Excel: формула будет находиться здесь. Выделите ячейку B2 (с первой ценой). Введите знак умножения (*). Выделите ячейку C2 (с количеством). Нажмите клавишу Enter.
  3. Когда вы наводите курсор на ячейку, содержащую формулу, в правом нижнем углу появляется крестик. Укажите на маркер автозаполнения. Щелкните по нему левой кнопкой мыши и перетащите его в конец колонки. Формула будет скопирована во все ячейки.
  4. Обозначьте границы нашего стола. Выберите диапазон с данными. Нажмите «Главная»-«Границы» (на главной странице в меню «Источник»). И выберите «Все границы».

При печати границы столбцов и строк теперь будут видны.

Границы таблицы.

Вы можете использовать меню «Шрифт», чтобы отформатировать данные в рабочем листе Excel как в Word.

Меню шрифт.

Измените, например, размер шрифта, сделайте заголовок «жирным». Можно установить текст по центру, назначить дефисы и т.д.

КАК СОЗДАТЬ ТАБЛИЦУ В EXCEL: ПОШАГОВАЯ ИНСТРУКЦИЯ

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

Для создания «умной» (динамической) таблицы выполните следующие шаги:

  1. Переходим на вкладку «Вставка» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
  2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.

Примечание. Сначала выберите диапазон ячеек, а затем нажмите «Таблица», чтобы создать таблицу.

Умная таблица.

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

Плюс склад.

Увеличить количество строк очень просто: возьмите маркер автозаполнения в правом нижнем углу и перетащите его вниз.

КАК РАБОТАТЬ С ТАБЛИЦЕЙ В EXCEL

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» — «Конструктор».

Конструктор таблиц.

Здесь можно дать имя таблице, изменить ее размер.

Читайте так же:
Как скопировать строки из нескольких листов на основе критериев на новый лист?

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

Возможности динамических электронных таблиц MS Excel огромны. Сначала рассмотрим основные навыки ввода данных:

  1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
  3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
  4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+»=»).

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

Числовые фильтры.

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

Как создать список из сводной таблицы Excel

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

Лист на рисунке 167.1 иллюстрирует тип преобразования, о котором я говорю. Диапазон A1:E4 содержит исходную сводную таблицу: 48 точек данных. Столбцы G:I показывают часть 48-рядной таблицы, полученной из сводной таблицы. Другими словами, каждое значение в исходной сводной таблице становится строкой, которая также содержит название продукта и месяц, соответствующий этому значению. Этот тип списка полезен тем, что его можно сортировать и манипулировать им другими способами.

Рис. 167.1. Преобразование сводной таблицы в список

Рис. 167.1: Преобразование сводной таблицы в список

Хитрость создания такого списка заключается в использовании поворотной таблицы. Но прежде чем использовать этот метод, необходимо добавить команду Мастер сводных таблиц на панель инструментов быстрого доступа. Excel 2007, Excel 2010 и Excel 2013 по-прежнему поддерживают мастер перекрестных таблиц, но он недоступен на ленте. Чтобы получить доступ к мастеру, выполните следующие действия.

  1. Щелкните правой кнопкой мыши панель инструментов быстрого доступа и выберите в контекстном меню пункт Настроить панель инструментов быстрого доступа.
  2. В разделе Панель инструментов быстрого доступа диалогового окна Параметры Excel выберите Команды ленты из выпадающего списка слева.
  3. Прокрутите список и выберите Мастер сводных таблиц и диаграмм.
  4. Нажмите кнопку Добавить.
  5. Нажмите клавишу 0K, чтобы закрыть диалоговое окно Параметры Excel.
Читайте так же:
Как создать выпадающий список из другой книги в Excel?

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

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

  1. Активируйте любую ячейку в поворотной таблице.
  2. Щелкните значок Мастера построения сводных таблиц и диаграмм, который вы добавили на панель инструментов быстрого доступа.
  3. В диалоговом окне Мастер построения сводных таблиц и диаграмм установите первую радиокнопку в положение Несколько диапазонов консолидации и нажмите кнопку Далее.
  4. На шаге 2a установите радиокнопку Создать поля страницы и нажмите Далее.
  5. На шаге 2b в поле Диапазон укажите диапазон таблицы консолидации (A1:E4 для образца в примере) и нажмите Добавить; затем нажмите Далее, чтобы перейти к шагу 3.
  6. На шаге 3 выберите местоположение для поворотной таблицы и нажмите Готово. Excel создает разворотную таблицу с данными и отображает область Список полей разворотной таблицы.
  7. В области Список полей разворотной таблицы снимите флажки Строка и Столбец.

В сводной таблице есть только два поля, Сумма и Значение. Небольшая сводная таблица показывает на данном этапе только сумму всех значений (Рисунок 167.2).

Рис. 167.2. Эта небольшая сводная таблица может быть расширена

Рисунок 167.2. Эта небольшая сводная таблица может быть увеличена

Для каждой ячейки, содержащей итоговое значение, дважды щелкните по ней (в данном примере 2851). Исходные данные будут вставлены в таблицу в новом рабочем листе, созданном Excel (Рисунок 167.3). Заголовки столбцов представляют собой общее описание (Строка, Столбец и Значение), поэтому вы можете изменить их и сделать более информативными.

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