Как сделать динамическую таблицу в Excel

Обновлено: 21.11.2024

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Обзор

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

Метод, описанный в этой статье, предполагает наличие не более 200 строк данных. Вы можете пересмотреть определенные имена, чтобы они использовали соответствующий номер и отражали максимальное количество строк.

Как использовать формулу OFFSET с определенным именем

Для этого выполните следующие действия в зависимости от используемой версии Excel.

Microsoft Office Excel 2007, Microsoft Excel 2010 и Microsoft Excel 2013

В новом листе введите следующие данные.

Перейдите на вкладку "Формулы".

В группе "Определенные имена" нажмите "Диспетчер имен".

Нажмите "Создать".

В поле Имя введите Дата.

В поле Относится к введите следующий текст и нажмите кнопку ОК:

=СМЕЩЕНИЕ($A$2,0,0,СЧЁТ($A$2:$A$200),1)

Нажмите "Создать".

В поле Имя введите Продажи.

В поле Относится к введите следующий текст и нажмите кнопку ОК:

=СМЕЩЕНИЕ($B$2,0,0,COUNT($B$2:$B$200),1)

Нажмите "Закрыть".

Очистите ячейку B2 и введите следующую формулу:

В этой формуле COUNT используется для столбца чисел. COUNTA используется для столбца текстовых значений.

В этой формуле используется переменная функция RAND. Эта формула автоматически обновляет формулу СМЕЩЕНИЯ, которая используется в определенном имени "Продажи", когда вы вводите новые данные в столбец B. В этой формуле используется значение 10, поскольку 10 — исходное значение ячейки B2.

Microsoft Office Excel 2003

В новом листе введите следующие данные:

В меню "Вставка" выберите "Имя" и нажмите "Определить".

В поле Имена в книге введите Дата.

В поле Относится к введите следующий текст и нажмите кнопку ОК:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1).

Нажмите "Добавить".

В поле "Имена в книге" введите "Продажи".

В поле "Относится к" введите следующий текст и нажмите "Добавить":

=СМЕЩЕНИЕ($B$2,0,0,COUNT($B$2:$B$200),1)

Нажмите "ОК".

Очистите ячейку B2 и введите следующую формулу:

В этой формуле COUNT используется для столбца чисел. COUNTA используется для столбца текстовых значений.

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

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

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

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

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

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

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

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

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

Пример

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

Поэтому сначала мы создадим динамический диапазон.

Поскольку у наших данных есть заголовки, не забудьте установить флажок u201cMy Table has headersu201d и нажать "ОК".

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

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

Пример

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

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

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

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

В поле Относится к следующей формуле:

=смещение(Лист2!$B$2,1,0,счетчик(Лист2!$B:$B)-1,2)

=смещение(

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

Что нужно помнить

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

Рекомендуемые статьи

Это руководство по динамическим таблицам в Excel. Здесь мы обсуждаем, как создать динамическую таблицу в Excel, используя функцию ТАБЛИЦА и СМЕЩЕНИЕ, а также практические примеры и загружаемые шаблоны. Вы можете узнать больше об Excel из следующих статей –

В этом видео мы рассмотрим, как создать динамический именованный диапазон с таблицей. Это самый простой способ создать динамический именованный диапазон в Excel.

Эта таблица содержит данные для десяти свойств. Я могу легко создать именованный диапазон для этих данных. Например, я могу создать диапазон под названием «данные». Затем, используя функцию ROWS, я могу получить общее количество свойств.

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

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

Самый простой способ создать именованный диапазон в Excel — использовать функцию «Таблица». Просто поместите курсор в любом месте данных и используйте сочетание клавиш Control + T. Excel попросит вас подтвердить, содержит ли таблица строку заголовка. В большинстве случаев ответ будет "да".

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

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

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

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

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

Например, когда я выбираю столбец "Цена" в качестве входных данных для СРЗНАЧ, Excel будет использовать структурированную ссылку для ссылки на столбец "Цены". То же самое верно, если я использую COUNTIF для подсчета проданных свойств.

Как и сама таблица, эти ссылки являются динамическими. Если я вставлю новые данные внизу таблицы, вы увидите, что формулы автоматически учитывают эти данные.

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

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

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

Сводные результаты

Для тех, кому нужно только решение без каких-либо объяснений, есть два варианта.

Предположим, что существует диапазон, такой как C2:C5, который содержит данные. Мы хотим создать ссылку на этот диапазон таким образом, чтобы при добавлении новых данных в C6, C7 и т. д. все ссылки на скорректируйте диапазон, чтобы включить новые данные.

Первый вариант — использовать таблицу в Excel 2007 или Excel 2010 (называемую списком в Excel 2002 и Excel 2003).

Теперь любая существующая ссылка на ячейки в таблице, например =SUM(C2:C5), будет расширяться, чтобы включать любые новые данные, добавленные в таблицу. Чтобы узнать, как создать таблицу см. в разделе Создание таблицы (называемой списком до Excel 2003).

Если мы создадим ссылку на данные после создания таблицы, Excel будет обращаться к ячейкам как к таблице[], например, Таблица1[Оценка]

Второй вариант — использовать именованную формулу, которая возвращает ссылку на диапазон, например,

После создания этой именованной формулы, которую часто называют динамическим диапазоном, используйте ее для ссылки на текущий диапазон. Таким образом, вместо =СУММ(C2:C5) используйте =СУММ(aRng). Эта именованная формула опирается на данные являются непрерывными. Дополнительную информацию по этой теме и альтернативу, которая работает с отсутствующими данными, см. в разделе «Использование именованной формулы».

В оставшейся части этого совета более подробно описывается проблема и решения.

Описание проблемы

Три типичных примера, демонстрирующих формулу, условное форматирование и функции Excel (в данном случае проверка данных), приведены ниже

Функция СУММ — довольно простое приложение:

Условный формат, примененный к ячейке G2, делает фон синим, если сумма ячеек в указанном диапазоне нечетна:

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

Проблема с каждым из вышеперечисленных способов заключается в том, что при добавлении новых данных в C6, C7 и т. д. нам придется отслеживать все ячейки и функциональные возможности, которые нам придется обновлять. Итак, если бы мы чтобы сложить все целые числа до 7 (до ячейки C8), мы должны были бы знать, какие ячейки и диалоговые окна мы должны обновить, чтобы отразить новые данные.

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

Создать таблицу (до Excel 2003 называлась списком)

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

Чтобы создать таблицу в Excel 2007 или Excel 2010, используйте команду Вставка | Таблица и в появившемся диалоговом окне укажите текущий диапазон таблицы.

В версии 2003 используйте Данные | «Список» > «Создать список» и появится то же диалоговое окно, что и выше.

Результат в 2007 году (в 2003 году он выглядит примерно так же):

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

Обратите внимание, что формула в D2 теперь составляет 28, в раскрывающемся списке "Проверка данных" отображается расширенный список, а c.f больше не показывает синий шаблон.

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

Хотя Excel 2007 правильно обновил формулу условного форматирования, это не относится к Excel 2003, в котором c.f. формула обновляться не будет — в этом можно легко убедиться.

Еще один случай, когда Excel не обновляет формулу – и это не должно вызывать удивления – это когда диапазон указывается в качестве аргумента функции ДВССЫЛ, как в =СУММ(ДВССЫЛ("C2:C5")).

«Еще одно место, где это не работает, — это ссылка из другой книги». На самом деле это работает, когда обе книги изначально открыты. Однако после их закрытия и повторного открытия ссылка, по-видимому, разорвана.

Последнее и важное ограничение заключается в том, что ссылка всегда будет указывать на весь столбец в таблице (или списке).� Мы не можем иметь ссылку только на часть таблицы, например, на последние N строк. � Эта возможность может быть важна для тех случаев, когда требуется скользящая сумма или скользящее среднее значение, или требуется построить только последние несколько строк. значения набора данных.

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

Использование именованной формулы

Подход, который работал, по крайней мере, с версии Excel 97, заключается в использовании именованной формулы, которая правильно определяет интересующий диапазон. Использование этой формулы приводит к динамическому диапазону, который настраивается по мере добавления или вычитания данных из диапазона. � В дополнение к работе с гораздо большим количеством версий Excel, чем предыдущий метод, этот метод также поддерживает возможность ссылаться только на подмножество данных. Это позволяет создавать формулы для расчета скользящего среднего значения. можно определить это подмножество несколькими различными способами. Можно даже создать результирующий набор, который повторяет данные — метод, который может дать «замкнутый» график!

Чтобы идентифицировать все ячейки в непрерывном диапазоне, используйте функцию OFFSET с функцией COUNTA, как в

Функция COUNTA возвращает количество ячеек в столбце C, содержащих данные. Мы вычитаем 1, чтобы исключить заголовок столбца. Затем функция OFFSET вычисляет диапазон, начиная с C2 (смещение нулевыми строками и нулевыми столбцами) с высота определяется количеством ячеек, содержащих данные, и шириной 1 столбца.

Теперь используйте имя aRng вместо ссылки на диапазон. �Таким образом, =SUM(aRng) или, для источника проверки данных, используйте =aRng. Точно так же использование с формулой серии диаграммы или в формуле условного форматирования будет допустимым использованием aRng.

Например, в условном форматировании для G9 мы будем использовать

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

Во всех этих случаях, когда мы добавляем данные в C8, C9 и т. д., Excel корректно обновляет различные результаты и функции.

Другое ограничение этого подхода с использованием именованной формулы с использованием функций СМЕЩ и СЧЁТЗ заключается в том, что он не работает, если диапазон включает отсутствующие данные. Можно адаптировать подход с именованной формулой к таким случаям, используя давно существующую ошибку в Excel. Функция ПОИСКПОЗ [1]. Хотя мне не нравится этот подход, потому что он использует ошибку, я подозреваю, что Microsoft никогда не исправит эту ошибку, потому что так много людей использовали ее так долго, что поведение стало нормой, а не исключением.

Когда последний аргумент функции ПОИСКПОЗ равен 1, ожидается, что данные будут расположены в порядке возрастания. Но даже если это не так, Excel предполагает, что это так. Таким образом, поиск максимально возможного числа 9,9999999999999999E307 будет всегда находить последнее число — хотя, честно говоря, я не знаю, что произойдет, если число действительно присутствует в таблице. У этого метода есть ограничения: если данные содержат текст, нужно найти соответствие REPT(CHAR( 255),255), а не 9,9999999999999999E307.

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