Динамические таблицы в Excel
Динамические таблицы в excel — это таблицы, в которых при вставке нового значения таблица сама регулирует свой размер, для создания динамической таблицы в excel у нас есть два разных метода, один из которых создает таблицу данных из table, а другой — с помощью функции смещения, в динамических таблицах отчеты и сводные таблицы также изменяются по мере изменения данных в динамической таблице.
Динамические таблицы в Excel
Под динамикой понимается процессорная система, характеризующаяся постоянным изменением или изменением активности. Точно так же в Excel, когда мы создаем списки или данные в книге и составляем из них отчет, но если мы добавляем какие-либо данные или удаляем их, перемещаем или изменяем данные, то весь отчет может быть неточным. В Excel есть решение для этого в виде динамических таблиц.
Теперь возникает вопрос, зачем нам динамический диапазон или динамические таблицы. Ответ на этот вопрос заключается в том, что всякий раз, когда список или диапазон данных обновляется или изменяется, это не гарантирует, что отчет будет изменен в соответствии с изменением данных.
По сути, есть два основных преимущества динамических таблиц:
- Динамический диапазон будет автоматически расширяться или сужаться в соответствии с изменением данных.
- Сводные таблицы на основе динамической таблицы в Excel могут автоматически обновляться при обновлении сводной таблицы.
Как создать динамические таблицы в Excel?
Существует два основных способа использования динамических таблиц в Excel: 1) Использование ТАБЛИЦ и 2) Использование функции OFFSET.
# 1 — Использование таблиц для создания динамических таблиц в Excel
Используя таблицы, мы можем создать динамическую таблицу в Excel и построить сводную таблицу на основе динамической таблицы.
пример
У нас есть следующие данные,
Если мы создадим сводную таблицу с этим нормальным диапазоном данных от A1 до E6, то, если мы вставим данные в строку 7, они не отразятся в сводной таблице.
Итак, сначала мы сделаем динамический диапазон.
# 1 — Выберите данные, например A1: E6.
# 2 — На вкладке «Вставка» щелкните «Таблицы» в разделе таблиц.
# 3 — Появится диалоговое окно.
Так как у наших данных есть заголовки, не забудьте установить флажок «Моя таблица имеет заголовки» и нажать ОК.
# 4 — Наш динамический диапазон создан.
# 5 — Выберите данные и на вкладке «Вставка» в разделе «Таблицы Excel» щелкните сводные таблицы.
# 6 — Поскольку мы создали таблицу, она принимает диапазон, как Таблица 2. Нажмите OK и в сводных таблицах перетащите продукт в строки и продажи в значения.
# 7 — Теперь в Sheet, где у нас есть наша таблица, вставьте Another Data в 7th
В сводной таблице обновите сводную таблицу.
В нашей динамической сводной таблице автоматически обновляются данные о продукте 6 в сводной таблице.
# 2 — Использование функции OFFSET для создания динамической таблицы в Excel
Мы также можем использовать функцию OFFSET для создания динамических таблиц в Excel. Давайте посмотрим на один такой пример.
пример
У меня есть прайс-лист на мои продукты, который я использую для своих расчетов,
Выберите данные и дайте им имя
Теперь, когда я обращаюсь к прайс-листу набора данных, он переводит меня к данным в диапазоне B2: C7, в котором есть мой прайс-лист. Но если я обновлю другую строку данных, она все равно приведет меня к диапазону B2: C7, потому что наш список статичен.
Мы будем использовать функцию смещения, чтобы сделать диапазон данных динамическим.
# 1 — На вкладке «Формулы» в «Определенном диапазоне» нажмите «Определенное имя», и появится диалоговое окно.
# 2 — В поле Имя введите любое имя, я буду использовать PriceA. Область действия — это текущая книга, и в настоящее время она ссылается на текущую выбранную ячейку, то есть B2.
В Ссылке написать следующую формулу:
= смещение (Sheet2! $ B $ 2,1,0, counta (Sheet2! $ B: $ B) -1,2)
= смещение (
# 3 — Теперь выберите начальную ячейку, то есть B2.
# 4 — Теперь нам нужно ввести 1,0, так как он посчитает, сколько строк или столбцов осталось.
# 5 — Теперь нам нужно, чтобы он подсчитал все данные в столбце B и использовал это как количество строк, поэтому используйте функцию COUNTA и выберите столбец B.
# 6 — Поскольку мы не хотим, чтобы подсчитывалась первая строка, которая является заголовком продукта, поэтому (-) 1 от нее.
# 7 — Теперь количество столбцов всегда будет равно двум, поэтому введите 2 и нажмите OK.
# 8 — Этот диапазон данных не будет отображаться по умолчанию, поэтому, чтобы увидеть это, нам нужно нажать на Диспетчер имен на вкладке Формула и выбрать Продукт,
# 9 — Если мы нажмем на ссылку, он покажет диапазон данных,
# 10 — Теперь добавьте еще один продукт в таблицу Product 6.
# 11 — Теперь щелкните Таблицу продуктов в Диспетчере имен; это также относится к добавленным новым данным,
Вот как мы можем использовать функцию смещения для создания динамических таблиц.
То, что нужно запомнить
- Сводные таблицы на основе динамического диапазона автоматически обновляются при обновлении.
- Использование функции смещения в Определенных именах можно увидеть в Диспетчере имен на вкладке формул.
УЗНАТЬ БОЛЬШЕ >>
