Как добавить данные в сводную таблицу в excel из другой таблицы
Связанная таблица — это Excel, которая содержит ссылку на таблицу в модели данных. Преимущество создания и обслуживания данных в таблице Excel, а не импорта данных из файла заключается в том, что вы можете продолжать изменять значения на Excel, используя связанную таблицу в модели данных в качестве основы для отчета в сводная диаграмма или сводная диаграмма.
Чтобы добавить связанную таблицу, достаточно выбрать диапазон и нажать кнопку Добавить в модель данных. Кроме того, следует отформатирование диапазона как таблицы и придать ему правильное имя. Гораздо проще выполнять вычисления и управлять отношениями с помощью именуемой связанной таблицы.
Чтобы связать данные с таблицей, выполните указанные ниже действия.
Вы можете выбрать диапазон строк и столбцов, которые вы хотите использовать в связанной таблице.
Отформатирование строк и столбцов в виде таблицы
На ленте > в формате таблицы, а затем выберите стиль таблицы. Вы можете выбрать любой стиль, но не забудьте выбрать таблицу с заглавами. Если в таблице нет заглавных таблиц, можно создать их прямо сейчас. В противном Excel будут использовать произвольные имена (столбец1, столбец2 и так далее), которые не передают осмысленные сведения о содержимом столбца.
Присвойте таблице имя. В окне Excel нажмите кнопку Конструктор средств работы с таблицами. В группе Свойства введите имя таблицы.
Поместите курсор на любую ячейку таблицы.
Щелкните Power Pivot > добавить в модель данных, чтобы создать связанную таблицу. В окне Power Pivot вы увидите таблицу со значком ссылки, указывающим на то, что таблица связана с таблицей-источником в Excel:
Если модель уже содержит таблицы, то нужно сделать еще один шаг. Необходимо создать связь между новой таблицей, которую вы только что добавили, и другими таблицами в модели. Инструкции см. в таблицах Создание связи между двумя таблицами и Создание связей в представлении диаграммы.
Если книга ранее не содержала ее, теперь она содержит модель данных. Модель создается автоматически при создании связи между двумя таблицами или нажатии кнопки Добавить в модель данных в Power Pivot. Дополнительные сведения см. в этойExcel.
Теперь, когда у вас есть связанная таблица, вы можете изменить любую ячейку на этом столе, включая добавление и удаление строк и столбцов. Модель данных в Power Pivot сразу же синхронизируется.
Имена таблиц являются исключением. Если переименовать таблицу в Excel, потребуется обновить ее вручную в Power Pivot.
Совет: Дополнительные сведения о связанных таблицах см. в учебнике Импорт данных в Excel и Создание модели данных.
Синхронизация изменений между таблицей и моделью
По умолчанию связанная таблица является активным подключением, которое сохраняется между диапазоном или именоваемой таблицей, содержаной значения данных, и моделью данных, которая является диском отчета. При добавлении или удалите данные либо переименуйте столбцы и таблицы, модель данных будет обновляться автоматически.
В некоторых случаях может потребоваться контролировать поведение обновления. Переключение в режим обновления вручную с помощью надстройки Power Pivot.
Чтобы переключиться в режим обновления вручную, выполните указанные здесь действия.
Убедитесь, что книга со связанной таблицей открыта в Excel.
Откройте окно Power Pivot.
На вкладке внизу щелкните связанную таблицу. Любая связанная таблица обозначена маленьким значком ссылки рядом с ее именем.
В верхней части ленты щелкните Связанная таблица.
В режиме обновления выберите Вручную или Автоматически. По умолчанию этот режим является автоматическим. Если переключиться на ручное, обновления будут происходить только при использовании команд Обновить все или Обновить выбранные на ленте Связанной таблицы в окне Power Pivot или команды Обновить все на ленте Power Pivot в Excel.
wikiHow работает по принципу вики, а это значит, что многие наши статьи написаны несколькими авторами. При создании этой статьи над ее редактированием и улучшением работали авторы-волонтеры.
Сводные таблицы используются для группировки и организации исходных данных из электронной таблицы. Главной ценностью сводных таблиц является то, что они допускают манипулирование процессом организации данных различными способами, в зависимости от выводов, которые разрабатываются на основе информации и потребностей пользователей электронной таблицы. Добавление строк в сводную таблицу является еще одним способом, с помощью которого данные могут быть организованы и отображены. Вот как можно добавлять строки в сводную таблицу, чтобы обеспечить большую глубину и смысл ваших выводов.
Запустите Microsoft Excel и откройте файл рабочей книги, содержащий сводную таблицу и исходные данные.
- Заголовки столбцов в исходных данных, как правило, используются в качестве меток полей для сводной таблицы.
Сравните исходные данные с существующей сводной таблицей, и определите, какая колонка будет добавлена в сводную таблицу, как дополнительно отображаемые строки.
Заставьте "Pivot Table Field List" (Список полей сводной таблицы) или "Pivot Table Wizard" (Мастер сводных таблиц) запуститься, нажав на одну из ячеек внутри области сводной таблицы.
Нажмите заголовок выбранного столбца, перетащите его в раздел "Row Labels" (Названия строк) списка полей сводной таблицы Pivot Table Field List..
Перегруппируйте заголовки полей в разделе "Row Labels" (Названия строк) и обратите внимание на изменения, внесенные в сводную таблицу.
Соавтор(ы): Jack Lloyd. Джек Ллойд — автор и редактор, пишущий о технологиях для wikiHow. Имеет более двух лет опыта в написании и редактировании статей на технические темы. Энтузиаст всего связанного с технологиями и учитель английского языка.
В этой статье мы расскажем вам, как добавить данные в существующую сводную таблицу Excel. Это можно сделать на компьютерах под управлением Windows и Mac OS X.
Откройте сводную таблицу Excel. Дважды щелкните по файлу Excel со сводной таблицей, чтобы открыть ее.
- Например, если данные находятся в ячейках с A1 по E10, введите дополнительные данные в столбце «F» или в строке «11».
- Если вы просто хотите изменить существующие данные, сделайте это.
- На компьютере Mac перейдите на вкладку «Анализ сводной таблицы».
Нажмите Изменить источник данных . Эта опция находится в разделе «Данные» панели инструментов «Анализ». Раскроется меню.
Выберите данные. Удерживайте кнопку мыши и перетащите указатель из верхней левой ячейки в нижнюю левую ячейку с данными, включая добавленные вами данные в столбце (столбцах) или в строке (строках).
- Если вы добавили новый столбец в сводную таблицу, отметьте его в правой части окна Excel, чтобы отобразить его.
- Сохраните сводную таблицу, прежде чем вносить в нее изменения, чтобы вернуться к сохраненной версии, если что-то пойдет не так.
Дополнительные статьи
Об этой статье
wikiHow работает по принципу вики, а это значит, что многие наши статьи написаны несколькими авторами. При создании этой статьи над ее редактированием и улучшением работали авторы-волонтеры.
Бывают случаи, когда вам нужно добавить чуть больше информации в сводную таблицу по сравнению с тем, на что она рассчитывалась, но при этом нет смысла изменять исходные данные, включая туда эту самую информацию. В таких редких случаях может быть полезным добавить в сводную таблицу пользовательские вычисляемые поля. Такие пользовательские поля можно добавить, например, для отображения средних значений, процентов, расхождения или даже минимального или максимального значения поля. Читайте инструкции по добавлению пользовательских полей в сводную таблицу, чтобы вы могли получить нужную вам информацию, не прилагая особых усилий.
Определите, какое пользовательское поле вам нужно, и на какие другие поля оно будет ссылаться для получения желаемого результата.
Отобразите меню "PivotTable Tools" ("Работа со сводными таблицами"), просто кликнув в любом месте сводной таблицы.
Выберите вкладку "Options" ("Параметры") и затем выберите пункт "Calculated Field" ("Вычисляемое поле") в меню "Formulas" ("Формулы").
- Вычисляемые поля в формулах сводных таблиц не поддерживают диапазоны. Поэтому в формулах вместо диапазона вам следует использовать имя соответствующей колонки. Щелкните внутри поля для ввода текста "Formula" ("Формула") и из предлагаемого списка выберите поле, используемое в расчете.
- Щелкните "Insert Field" ("Добавить поле"), чтобы вставить правильное имя колонки в свою формулу. Допишите формулу, добавив нужные вычисления.
- Например, вы хотите вычислить 6-процентный налог на продажи в сводной таблице, в которой показываются продажи продукта в различных регионах. Имя колонки "Регион" находится в разделе "Columns"("Колонки"), название "Продажи" находится в разделе "Values"("Значения"), а название "Продукт" находится в разделе "Rows"("Строки").
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:
Чем отличается эта сводная таблица? Обратите внимание, что в списке полей справа отображается не одна таблица, а целый набор таблиц. Каждая из этих таблиц содержит поля, которые можно объединить в одну сводную таблицу для получения различных срезов данных. Не требуются ручное форматирование и подготовка данных. Сразу после импорта данных можно создать сводную таблицу на основе связанных таблиц.
Чтобы объединить несколько таблиц в списке полей сводной таблицы:
Можно импортировать их из реляционной базы данных, например, Microsoft SQL Server, Oracle или Microsoft Access. Вы можете импортировать несколько таблиц одновременно.
Можно импортировать несколько таблиц из других источников данных, в том числе из текстовых файлов, веб-каналов данных, данных листа Excel и т. д. Вы можете добавить эти таблицы в модель данных в Excel, создать связи между ними, а затем создать сводную таблицу с помощью модели данных.
Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.
Убедитесь, что вам известны имя сервера, имя базы данных и учетные данные, необходимые для подключения к SQL Server. Все необходимые сведения можно получить у администратора базы данных.
Щелкните Данные > Получение внешних данных > Из других источников > С сервера SQL Server.
В поле Имя сервера введите сетевое имя компьютера с запущенным сервером SQL Server.
В разделе Учетные данные входа в систему выберите команду Использовать проверку подлинности Windows, если вы подключаетесь с помощью своих учетных данных. В противном случае введите имя пользователя и пароль, предоставленные администратором базы данных.
Нажмите клавишу ВВОД и в разделе Выбор базы данных и таблицы выберите нужную базу данных, а затем щелкните Разрешить выбор нескольких таблиц.
Выберите необходимые для работы таблицы вручную, если вы знаете, какие именно нужны вам. Или же выберите одну или две, а затем щелкните Выбор связанных таблиц для автовыбора таблиц, связанных с уже указанными.
Если установлен флажок Импорт связи между выбранными таблицами, оставьте его, чтобы разрешить Excel воссоздать аналогичные связи таблиц в книге.
Нажмите Готово.
В диалоговом окне Импорт данных выберите элемент Отчет сводной таблицы.
Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ.
Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле "ОбъемПродаж" из таблицы "ФактПродажиЧерезИнтернет".
Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.
Работа с другими типами баз данных
Для использования других реляционных баз данных, например Oracle, может понадобиться установить дополнительное клиентское программное обеспечение. Обратитесь к администратору базы данных, чтобы уточнить, есть ли такая необходимость.
Вы можете импортировать несколько таблиц из приложения Access. Подробнее об этом можно узнать в учебнике по импорту данных в Excel и созданию модели данных.
Импорт таблиц из других источников
Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.
Реляционные базы данных — это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей.
Использование модели данных для создания новой сводной таблицы
Возможно, вы создали связи между таблицами в модели данных и теперь готовы использовать эти данные для анализа. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
Щелкните любую ячейку на листе.
Выберите Вставка > Сводная таблица.
В диалоговом окне Создание сводной таблицы щелкните Из внешнего источника данных.
Выберите вариант Выбрать подключение.
На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.
Читайте также: