Как вставить таблицу в excel между таблицами

Обновлено: 21.11.2024

С помощью модели данных автоматизируйте задачи анализа данных и бизнес-моделирования в Microsoft Excel. Посмотрите, как это делается!

Excel – это мощный инструмент для анализа данных и последующей автоматизации при работе с большими наборами данных. Вы можете потратить много времени на анализ огромного количества данных с помощью функций ВПР, ИНДЕКС-ПОИСКПОЗ, СУММЕСЛИ и т. д.

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

Основные требования

Вам понадобятся Power Pivot и Power Query (получение и преобразование) для выполнения нескольких задач при создании модели данных Excel. Вот как вы можете использовать эти функции в своей книге Excel:

Как получить Power Pivot

<р>1. Excel 2010: вам необходимо загрузить надстройку Power Pivot с веб-сайта Microsoft, а затем установить ее для своей программы Excel на свой компьютер.

<р>2. Excel 2013: выпуск Office профессиональный плюс Excel 2013 включает Power Pivot. Но вам нужно активировать его перед первым использованием. Вот как:

Нажмите «Файл» на ленте книги Excel.
Затем нажмите «Параметры», чтобы открыть параметры Excel.
Теперь нажмите "Надстройки".
Выберите надстройки COM, щелкнув раскрывающееся меню окна «Управление».
Нажмите «Перейти» и установите флажок Microsoft Power Pivot для Excel.

<р>3. Excel 2016 и более поздние версии: меню Power Pivot находится на ленте.

Как получить Power Query (получить и преобразовать)

<р>1. Excel 2010: вы можете загрузить надстройку Power Query с веб-сайта Microsoft. После установки Power Query появится на ленте.

<р>2. Excel 2013: вам необходимо активировать Power Query, выполнив те же действия, которые вы только что сделали, чтобы включить Power Pivot в Excel 2013.

<р>3. Excel 2016 и более поздние версии: Power Query (получение и преобразование) можно найти на вкладке "Данные" на ленте Excel.

Создание модели данных путем импорта данных в книгу Excel

Для этого руководства вы можете получить предварительно отформатированные образцы данных от Microsoft:

Загрузить: образец данных об учащихся (только данные) | Пример данных учащихся (полная модель)

Вы можете импортировать базу данных с несколькими связанными таблицами из многих источников, таких как книги Excel, Microsoft Access, веб-сайты, SQL Server и т. д. Затем вам нужно отформатировать набор данных, чтобы Excel мог его использовать. Вот шаги, которые вы можете попробовать:

<р>1. В Excel 2016 и более поздних версиях перейдите на вкладку "Данные" и выберите "Новый запрос".

<р>2. Вы найдете несколько способов импорта данных из внешних или внутренних источников. Выберите тот, который подходит вам.

<р>3. При использовании версии Excel 2013 нажмите Power Query на ленте, а затем выберите Получить внешние данные, чтобы выбрать данные для импорта.

<р>4. Вы увидите окно Navigator, где вам нужно выбрать, какие таблицы вам нужно импортировать. Установите флажок Выбрать несколько элементов, чтобы выбрать несколько таблиц для импорта.

<р>5. Нажмите «Загрузить», чтобы завершить процесс импорта.

<р>6. Excel создаст для вас модель данных, используя эти таблицы. Вы можете увидеть заголовки столбцов таблицы в списках полей сводной таблицы.

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

<р>1. Отформатируйте данные в табличной модели, выбрав все ячейки, содержащие данные, и нажмите Ctrl+T.

<р>2. Теперь выберите всю таблицу, а затем щелкните вкладку Power Pivot на ленте.

<р>3. В разделе "Таблицы" нажмите "Добавить в модель данных".

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

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

Создание отношений между таблицами в модели данных

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

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

<р>1. На ленте книги Excel щелкните меню Power Pivot.

<р>2. Теперь нажмите «Управление» в разделе «Модель данных». Вы увидите редактор Power Pivot, как показано ниже:

<р>3. Нажмите кнопку «Просмотр схемы», расположенную в разделе «Вид» на вкладке «Главная» Power Pivot.Вы увидите заголовки столбцов таблицы, сгруппированные в соответствии с названием таблицы.

<р>4. Теперь вы сможете перетаскивать уникальный идентификатор поля из одной таблицы в другую. Ниже приведена схема отношений между четырьмя таблицами модели данных Excel:

Связь между таблицами описана ниже:

Таблица студентов | Студенческий билет в таблицу оценок | Удостоверение личности
Таблица семестров | Идентификатор семестра для таблицы оценок | Семестр
Классы таблиц | Номер класса в таблице оценок | Идентификатор класса

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

<р>6. Вы заметите звездочку (*) с одной стороны и единицу (1) с другой в представлении диаграммы отношений. Он определяет, что между таблицами существует отношение "один ко многим".

<р>7. В редакторе Power Pivot перейдите на вкладку "Дизайн" и выберите "Управление взаимосвязями", чтобы узнать, какие поля связаны между собой.

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

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

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

<р>1. В редакторе Power Pivot перейдите на вкладку "Главная".

<р>2. На ленте нажмите Сводная таблица.

<р>3. Выберите любой из вариантов «Новый рабочий лист» или «Существующий рабочий лист».

<р>4. Выберите ОК. Excel добавит сводную таблицу, которая покажет панель списка полей справа.

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

Преобразование сложных наборов данных в простые отчеты с помощью модели данных Excel

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

Вы можете постоянно обновлять существующую книгу и публиковать отчеты об обновленных данных. Вам не нужно редактировать формулы или тратить время на прокрутку тысяч столбцов и строк при каждом обновлении исходных данных.

Вы когда-нибудь использовали функцию ВПР для переноса столбца из одной таблицы в другую? Теперь, когда в Excel есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами данных на основе сопоставления данных в каждой таблице. Затем вы можете создавать листы Power View и создавать сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы взяты из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете импортировать и связать данные аналитики времени, чтобы анализировать модели продаж по годам и месяцам.

Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.

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

Убедитесь, что книга содержит как минимум две таблицы и что в каждой таблице есть столбец, который можно сопоставить со столбцом в другой таблице.

Выполните одно из следующих действий: отформатируйте данные в виде таблицы или импортируйте внешние данные в виде таблицы на новый лист.

Дайте каждой таблице значимое имя. В разделе «Инструменты для таблиц» нажмите «Дизайн» > «Имя таблицы» > введите имя.

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

Например, чтобы связать продажи клиентов с аналитикой времени, обе таблицы должны содержать даты в одном и том же формате (например, 01.01.2012), и по крайней мере в одной таблице (аналитика времени) каждая дата указана только один раз в пределах столбец.

Нажмите Данные > Отношения.

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

В поле "Управление отношениями" нажмите "Создать".

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

В поле "Столбец (внешний)" выберите столбец, содержащий данные, связанные со связанным столбцом (основным). Например, если бы у вас был столбец даты в обеих таблицах, вы бы выбрали этот столбец сейчас.

В поле "Связанная таблица" выберите таблицу, в которой есть хотя бы один столбец данных, связанный с таблицей, которую вы только что выбрали для таблицы.

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

Подробнее о связях между таблицами в Excel

Примечания о взаимоотношениях

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

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

В модели данных отношения между таблицами могут быть "один к одному" (каждый пассажир имеет один посадочный талон) или "один ко многим" (на каждом рейсе много пассажиров), но не "многие ко многим". Отношения «многие ко многим» приводят к ошибкам циклической зависимости, например «Обнаружена циклическая зависимость». Эта ошибка возникает, если вы устанавливаете прямое соединение между двумя таблицами типа «многие ко многим» или косвенные соединения (цепочка отношений таблиц, которые являются «один ко многим» внутри каждого отношения, но «многие ко многим» при просмотре). до конца. Узнайте больше о связях между таблицами в модели данных.

Типы данных в двух столбцах должны быть совместимы. Дополнительные сведения см. в разделе Типы данных в моделях данных Excel.

Другие способы создания взаимосвязей могут быть более интуитивно понятными, особенно если вы не уверены, какие столбцы использовать. См. раздел Создание связи в представлении схемы в Power Pivot.

Пример: сопоставление данных аналитики времени с данными о рейсах авиакомпаний

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

Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace. Домашняя страница Microsoft Azure Marketplace откроется в мастере импорта таблиц.

В разделе "Цена" нажмите "Бесплатно".

В разделе "Категория" нажмите "Наука и статистика".

Найдите DateStream и нажмите «Подписаться».

Войдите в свою учетную запись Microsoft и нажмите «Войти». В окне должен появиться предварительный просмотр данных.

Прокрутите вниз и нажмите "Выбрать запрос".

Нажмите "Далее".

Выберите BasicCalendarUS, а затем нажмите Готово, чтобы импортировать данные. При быстром интернет-соединении импорт займет около минуты. Когда закончите, вы должны увидеть отчет о состоянии 73 414 переданных строк. Нажмите Закрыть.

Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace, чтобы импортировать второй набор данных.

В разделе "Тип" нажмите "Данные".

В разделе "Цена" нажмите "Бесплатно".

Найдите задержки рейсов авиаперевозчика США и нажмите «Выбрать».

Прокрутите вниз и нажмите "Выбрать запрос".

Нажмите "Далее".

Нажмите «Готово», чтобы импортировать данные. При быстром подключении к Интернету импорт может занять 15 минут. По завершении вы должны увидеть отчет о состоянии 2 427 284 переданных строк. Щелкните Закрыть. Теперь у вас должно быть две таблицы в модели данных. Чтобы связать их, нам потребуются совместимые столбцы в каждой таблице.

Обратите внимание, что DateKey в BasicCalendarUS имеет формат 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени, FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одного типа, и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих нескольких шагах вы будете использовать эти столбцы для связи таблиц.

В окне Power Pivot нажмите "Сводная таблица", чтобы создать сводную таблицу на новом или существующем листе.

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

Разверните BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.

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

В списке полей в разделе «Могут потребоваться связи между таблицами» нажмите «Создать».

В связанной таблице выберите On_Time_Performance, а в связанном столбце (основной) выберите FlightDate.

В таблице выберите BasicCalendarUS, а в столбце (иностранный) выберите DateKey.Нажмите OK, чтобы создать связь.

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

В BasicCalendarUS перетащите YearKey в область строк над MonthInCalendar.

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

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

Убедитесь, что таблица BasicCalendarUS открыта в окне Power Pivot.

В таблице "Главная" нажмите "Сортировать по столбцу".

В разделе «Сортировка» выберите «Месяц в календаре».

В поле "По" выберите "МесяцГода".

Сводная таблица теперь сортирует каждую комбинацию месяц-год (октябрь 2011 г., ноябрь 2011 г.) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что фид DateStream предоставляет все необходимые столбцы, чтобы этот сценарий работал. Если вы используете другую таблицу операций со временем, ваш шаг будет другим.

"Могут потребоваться связи между таблицами"

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

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

Шаг 1. Определите, какие таблицы указать в связи

Если ваша модель содержит всего несколько таблиц, может быть сразу понятно, какие из них вам нужно использовать. Но для более крупных моделей вам, вероятно, может понадобиться помощь. Один из подходов — использовать представление схемы в надстройке Power Pivot. Представление диаграммы обеспечивает визуальное представление всех таблиц в модели данных. Используя представление диаграммы, вы можете быстро определить, какие таблицы отделены от остальной части модели.

Примечание. Можно создать неоднозначные отношения, которые недопустимы при использовании в сводной таблице или отчете Power View. Предположим, что все ваши таблицы каким-то образом связаны с другими таблицами в модели, но при попытке объединить поля из разных таблиц вы получаете сообщение «Могут потребоваться связи между таблицами». Наиболее вероятная причина заключается в том, что вы столкнулись с отношениями «многие ко многим». Если вы проследите цепочку взаимосвязей таблиц, которые соединяются с таблицами, которые вы хотите использовать, вы, вероятно, обнаружите, что у вас есть две или более связи таблиц «один ко многим». Не существует простого обходного пути, подходящего для любой ситуации, но вы можете попробовать создать вычисляемые столбцы, чтобы объединить нужные столбцы в одну таблицу.

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

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

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

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

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

Типы данных исходного столбца и столбца подстановки должны быть совместимы. Дополнительные сведения о типах данных см. в разделе Типы данных в моделях данных.

Чтобы узнать больше о связях между таблицами, см. раздел Отношения между таблицами в модели данных.

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

Люди делали это с ранних версий Excel и создавали отношения между различными таблицами с помощью функций ВПР или ИНДЕКС и ПОИСКПОЗ.

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

В этом примере есть таблица "Заказы", которая содержит данные, относящиеся к заказам клиентов, такие как идентификатор клиента, проданный продукт и общая сумма.

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

Одним из решений может быть добавление еще двух столбцов к данным о заказах и использование функции ВПР для ввода имени и страны клиента на основе идентификатора клиента.

Другим более элегантным решением может быть использование модели данных сводной таблицы для создания отношения между таблицами "Заказы" и "Клиенты" с использованием поля "Идентификатор клиента" в обеих таблицах.

Создайте таблицу на основе таблицы "Заказы".

Перейдите на вкладку "Вставка" на ленте и выберите "Сводная таблица" в разделе "Таблицы".

В меню "Создать сводную таблицу".

Выберите таблицу заказов.
Установите флажок "Добавить эти данные в модель данных".
Нажмите кнопку "ОК".

Повторите эти шаги для таблицы "Клиенты".

Создав любую из ваших сводных таблиц, вы должны увидеть обе таблицы в окне "Поля сводной таблицы" в представлении "Все".

Вы можете удалить одну из созданных сводных таблиц, так как вам нужна только одна, данные останутся в модели данных.

Создайте сводную таблицу с именем из таблицы "Клиенты" в области "Строки" и "Итого" из таблицы "Заказы" в области "Значения".

Перетащите поле «Имя» из таблицы «Клиенты» в область «Строки». Разверните таблицу, чтобы увидеть поля, нажав стрелку рядом с названием таблицы.
Перетащите поле "Итого" из таблицы "Заказы" в область "Значения".
Над списком полей появится уведомление о том, что могут потребоваться отношения между таблицами. Отсюда вы можете выбрать «Автоопределение» и позволить Excel угадать взаимосвязи между вашими таблицами или создать их самостоятельно.

Если вы выберете Автоопределение, Excel будет работать над созданием взаимосвязей, и вы сможете проверить результаты, нажав кнопку "Управление взаимосвязями" или просто закрыв окно.

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

Если вы решите создать отношения самостоятельно, откроется меню «Создать отношение».

Теперь ваша результирующая сводная таблица содержит имя клиента из таблицы "Клиенты" вместе с правильной соответствующей суммой из таблицы "Заказы".

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

Откроется диалоговое окно "Управление отношениями".

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

Это отличный способ избежать ВПР или ИНДЕКС и ПОИСКПОЗ и свести к минимуму количество столбцов в таблицах.

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

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

Таблица Excel

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

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

На приведенном ниже снимке экрана показан контраст между обычным диапазоном и форматом таблицы:

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

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

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

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

Выберите любую ячейку в наборе данных.
На вкладке Вставка в группе Таблицы нажмите кнопку "Таблица" или нажмите сочетание клавиш Ctrl + T.
Появится диалоговое окно Создать таблицу со всеми данными, выбранными для вас автоматически; вы можете настроить диапазон, если это необходимо. Если вы хотите, чтобы первая строка данных стала заголовками таблицы, убедитесь, что установлен флажок Моя таблица имеет заголовки.
Нажмите ОК.

В результате Excel преобразует ваш диапазон данных в настоящую таблицу со стилем по умолчанию:

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

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

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

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

Выберите любую ячейку в наборе данных.
На вкладке Главная в группе Стили нажмите Форматировать как таблицу.
В галерее выберите стиль, который хотите использовать.
В диалоговом окне Создать таблицу при необходимости измените диапазон, установите флажок В моей таблице есть заголовки и нажмите ОК.< /li>

Совет. Чтобы применить выбранный стиль и удалить все существующее форматирование, щелкните стиль правой кнопкой мыши и выберите Применить и очистить форматирование в контекстном меню.

Как назвать таблицу в Excel

Каждый раз, когда вы создаете таблицу в Excel, она автоматически получает имя по умолчанию, например Таблица1, Таблица2 и т. д.Если вы имеете дело с несколькими таблицами, изменение имен по умолчанию на более осмысленные и описательные может значительно облегчить вашу работу.

Чтобы переименовать таблицу, выполните следующие действия:

Выберите любую ячейку в таблице.
На вкладке Конструктор таблиц в группе Свойства выберите существующее имя в поле Имя таблицы и замените его новым.

Совет. Чтобы просмотреть имена всех таблиц в текущей книге, нажмите Ctrl + F3, чтобы открыть Диспетчер имен.

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

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

Как отфильтровать таблицу в Excel

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

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

Если вам не нужна функция автофильтра, вы можете убрать стрелки, сняв флажок Кнопка фильтра на вкладке Дизайн в разделе Параметры стиля таблицы. Или вы можете включать и выключать кнопки фильтра с помощью сочетания клавиш Ctrl + Shift + L.

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

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

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

Формулы таблиц Excel

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

Простота создания. Просто выберите данные таблицы при создании формулы, и Excel автоматически создаст для вас структурированную справку.
Легко читается. Структурированные ссылки относятся к частям таблицы по имени, что упрощает понимание формул.
Заполняется автоматически. Чтобы выполнить одно и то же вычисление в каждой строке, введите формулу в любой отдельной ячейке, и она будет немедленно скопирована по всему столбцу.
Изменено автоматически. Если вы измените формулу в любом месте столбца, другие формулы в том же столбце изменятся соответствующим образом.
Обновлено автоматически. При каждом изменении размера таблицы или переименовании столбцов структурированные ссылки динамически обновляются.

На приведенном ниже снимке экрана показан пример структурированной ссылки, которая суммирует данные в каждой строке:

Суммирование столбцов таблицы

Еще одна замечательная функция таблицы Excel — возможность суммировать данные без формул. Этот параметр называется «Строка итогов».

Чтобы суммировать данные таблицы, вам нужно сделать следующее:

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

Строка Итого вставляется внизу таблицы и показывает итог в последнем столбце:

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

Какую бы операцию вы ни выбрали, Excel будет использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая вычисляет данные только в видимых строках:

Как расширить таблицу в Excel

Когда вы вводите что-либо в соседнюю ячейку, таблица Excel автоматически расширяется и включает новые данные. В сочетании со структурированными ссылками это создает динамический диапазон для ваших формул без каких-либо усилий с вашей стороны. Если вы не хотите, чтобы новые данные были частью таблицы, нажмите Ctrl + Z . Это отменит расширение таблицы, но сохранит введенные вами данные.

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

Вы также можете добавлять и удалять столбцы и строки с помощью команды "Изменить размер таблицы". Вот как:

Нажмите в любом месте таблицы.
На вкладке Дизайн в группе Свойства нажмите Изменить размер таблицы.
Когда появится диалоговое окно, выберите диапазон, который нужно включить в таблицу.
Нажмите ОК.

Стили таблиц Excel

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

Как изменить стиль таблицы

Когда вы вставляете таблицу в Excel, к ней автоматически применяется стиль по умолчанию. Чтобы изменить стиль таблицы, сделайте следующее:

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

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

Применить стиль таблицы и удалить существующее форматирование

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

Управление полосами строк и столбцов

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

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

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

Выберите любую ячейку в таблице.
На вкладке Дизайн в группе Стили таблиц нажмите кнопку Дополнительно в правом нижнем углу, а затем выберите Очистить под шаблонами стиля таблицы. Или выберите первый стиль в разделе Светлый, который называется "Нет".

Примечание. Этот метод удаляет только встроенное форматирование таблицы, ваше пользовательское форматирование сохраняется. Чтобы удалить абсолютно все форматирование в таблице, перейдите на вкладку Главная > группа Форматы и нажмите Очистить > Очистить форматы.

Как удалить таблицу в Excel

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

Щелкните правой кнопкой мыши любую ячейку в таблице и выберите Таблица>Преобразовать в диапазон. Или нажмите кнопку Преобразовать в диапазон на вкладке Дизайн в группе Инструменты.
В появившемся диалоговом окне нажмите Да.

Это удалит таблицу, но сохранит все данные и форматирование. Чтобы сохранить только данные, удалите форматирование таблицы перед преобразованием таблицы в диапазон.

Вот как вы создаете, редактируете и удаляете таблицу в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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