Динамические таблицы в Excel

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

Динамические таблицы в Excel

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

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

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

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

Как создать динамические таблицы в Excel?

Существует два основных способа использования динамических таблиц в Excel: 1) Использование ТАБЛИЦ и 2) Использование функции OFFSET.

Вы можете скачать этот шаблон динамической таблицы Excel здесь — шаблон динамической таблицы Excel

# 1 — Использование таблиц для создания динамических таблиц в Excel

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

пример

У нас есть следующие данные,

Динамический диапазон с таблицами данных, пример 1

Если мы создадим сводную таблицу с этим нормальным диапазоном данных от A1 до E6, то, если мы вставим данные в строку 7, они не отразятся в сводной таблице.

Итак, сначала мы сделаем динамический диапазон.

# 1 — Выберите данные, например A1: E6.

Динамический диапазон с таблицами данных, пример 1-1

# 2 — На вкладке «Вставка» щелкните «Таблицы» в разделе таблиц.

Динамический диапазон с таблицами данных, пример 1-2

# 3 — Появится диалоговое окно.

Динамический диапазон с таблицами данных, пример 1-3

Так как у наших данных есть заголовки, не забудьте установить флажок «Моя таблица имеет заголовки» и нажать ОК.

# 4 — Наш динамический диапазон создан.

Динамический диапазон с таблицами данных, пример 1-4

# 5 — Выберите данные и на вкладке «Вставка» в разделе «Таблицы Excel» щелкните сводные таблицы.

Динамический диапазон с таблицами данных, пример 1-5

# 6 — Поскольку мы создали таблицу, она принимает диапазон, как Таблица 2. Нажмите OK и в сводных таблицах перетащите продукт в строки и продажи в значения.

Динамический диапазон с таблицами данных, пример 1-6

# 7 — Теперь в Sheet, где у нас есть наша таблица, вставьте Another Data в 7th

Динамический диапазон с таблицами данных, пример 1-7

В сводной таблице обновите сводную таблицу.

Динамический диапазон с таблицами данных, пример 1-8

В нашей динамической сводной таблице автоматически обновляются данные о продукте 6 в сводной таблице.

# 2 — Использование функции OFFSET для создания динамической таблицы в Excel

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

пример

У меня есть прайс-лист на мои продукты, который я использую для своих расчетов,

Использование функции смещения, пример 1

Выберите данные и дайте им имя

Использование функции смещения, пример 1-1

Теперь, когда я обращаюсь к прайс-листу набора данных, он переводит меня к данным в диапазоне B2: C7, в котором есть мой прайс-лист. Но если я обновлю другую строку данных, она все равно приведет меня к диапазону B2: C7, потому что наш список статичен.

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

# 1 — На вкладке «Формулы» в «Определенном диапазоне» нажмите «Определенное имя», и появится диалоговое окно.

Пример использования функции OFFSET 1-2

# 2 — В поле Имя введите любое имя, я буду использовать PriceA. Область действия — это текущая книга, и в настоящее время она ссылается на текущую выбранную ячейку, то есть B2.

В Ссылке написать следующую формулу:

= смещение (Sheet2! $ B $ 2,1,0, counta (Sheet2! $ B: $ B) -1,2)

= смещение (

Пример использования функции СМЕЩЕНИЕ 1-3

# 3 — Теперь выберите начальную ячейку, то есть B2.

Использование примера функции СМЕЩЕНИЕ 1-4

# 4 — Теперь нам нужно ввести 1,0, так как он посчитает, сколько строк или столбцов осталось.

Использование функции смещения, пример 1-5

# 5 — Теперь нам нужно, чтобы он подсчитал все данные в столбце B и использовал это как количество строк, поэтому используйте функцию COUNTA и выберите столбец B.

Использование функции смещения, пример 1-6

# 6 — Поскольку мы не хотим, чтобы подсчитывалась первая строка, которая является заголовком продукта, поэтому (-) 1 от нее.

Пример использования функции OFFSET 1-7

# 7 — Теперь количество столбцов всегда будет равно двум, поэтому введите 2 и нажмите OK.

Пример использования функции OFFSET 1-8

# 8 — Этот диапазон данных не будет отображаться по умолчанию, поэтому, чтобы увидеть это, нам нужно нажать на Диспетчер имен на вкладке Формула и выбрать Продукт,

Использование функции смещения, пример 1-9

# 9 — Если мы нажмем на ссылку, он покажет диапазон данных,

Использование функции OFFSET, пример 10

# 10 — Теперь добавьте еще один продукт в таблицу Product 6.

Использование функции OFFSET, пример 11

# 11 — Теперь щелкните Таблицу продуктов в Диспетчере имен; это также относится к добавленным новым данным,

Использование функции OFFSET, пример 12

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

То, что нужно запомнить

  1. Сводные таблицы на основе динамического диапазона автоматически обновляются при обновлении.
  2. Использование функции смещения в Определенных именах можно увидеть в Диспетчере имен на вкладке формул.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.