Автоматическое добавление строк в таблицу Excel
Обновлено: 21.11.2024
Дизайн лучше, но когда вы застряли с существующей структурой данных, полезно знать, как обойти проблемы. Сьюзан Харкинс объясняет.
Таблицы Excel растут — иногда за счет добавления новых функций, но чаще всего за счет добавления новых данных. Хороший дизайн вмещает в себя большую часть роста и изменений, но часто вы работаете с набором данных, который не соответствует тому, что вам нужно делать. К счастью, с небольшим ноу-хау вы можете получить то, что хотите. Если вы не можете, вам, возможно, придется подумать об ограничении данных. В этом примере мы рассмотрим три способа сделать одно и то же — заставить зависимые функции автоматически обновляться при расширении диапазона данных. В частности, мы будем подсчитывать вхождения строк в нескольких столбцах, а не в одном. Вы можете работать с собственным простым набором данных или скачать демонстрационный файл .xlsx.
Считать легко
На рисунке A показан простой набор данных из нескольких столбцов и строк. Поскольку имена встречаются в столбцах и строках, мы будем ссылаться на диапазон. С этой целью я использовал простую функцию COUNTIF(), которая вычисляет весь диапазон, а не одну строку или столбец, как вы, возможно, привыкли видеть.
Рисунок А
COUNTIF() возвращает количество раз, когда каждое имя встречается в диапазоне.
Эта функция использует следующий синтаксис:
После ввода первой функции =СЧЁТЕСЛИ($B$2:$E$9,$H2) в ячейку I2 я скопировал её в оставшийся список уникальных имен. Обратите внимание, что ссылка на диапазон является абсолютной, но только столбец является абсолютным в ссылке на критерии. Абсолютная ссылка необходима для получения точного результата при оценке диапазона, а не строки или столбца.
Добавление данных усложняет подсчет
Если вы работаете со стабильным диапазоном, в котором размеры не увеличиваются, это решение отлично подойдет. Однако, как только вы добавите новую строку или столбец, вы столкнетесь с проблемой, поскольку функции COUNTIF() не будут автоматически обновляться для включения новых данных. Как видно на рис. Б, счетчики остаются прежними даже после добавления новой строки и столбца.
Рисунок Б
Функции COUNTIF() не обновляются при добавлении новых данных.
В этом случае необходима абсолютная ссылка в ссылке диапазона. Поскольку ссылка является абсолютной, функция не будет автоматически обновляться, чтобы включать новые данные, добавляемые каждый день.
Простое решение
Вероятно, вы знаете, что вы можете вставить строку или столбец в диапазон, чтобы воспользоваться преимуществами автоматической ссылки. На рис. C показан результат добавления новой строки. Как видите, функции обновляются, но с новой строкой появляются новые проблемы:
- Набор данных больше не отсортирован по дате. Обычно это не имеет значения, потому что вы можете сортировать данные.
- Новая вставленная строка создает пробел в списке уникальных имен. Если вы можете переместить уникальный список и столбец функций COUNTIF() на другой лист, эта проблема исчезнет.
Рисунок C
Вставка строки или столбца — простое решение, если это не создает новых проблем.
Несмотря на то, что я не показывал это на примере набора данных, при вставке столбца возникает тот же неприятный потенциал. Однако эти небольшие проблемы легко решаются. Не храните больше ничего на листе с вашим набором данных, и вставка вдруг станет простым решением. Вы даже можете создать макрос, который вставляет новые столбцы и строки для ваших пользователей.
Спасение таблицы
В зависимости от уровня знаний ваших пользователей вы можете рассмотреть возможность преобразования набора данных в объект таблицы (поддерживается в Excel 2007 и более поздних версиях). При вставке столбцов и строк ссылки будут обновляться автоматически.
Чтобы преобразовать набор данных в таблицу, выполните следующие действия:
- Нажмите внутри набора данных.
- На вкладке "Вставка" нажмите "Таблица" в группе "Таблицы".
- Укажите, есть ли у таблицы заголовки. В этом случае набор данных имеет заголовки (рис. D).
Рисунок D - Нажмите "ОК".
- В левом конце ленты перейдите на вкладку "Файл".
- Затем нажмите "Параметры".
- В окне параметров Excel слева нажмите Проверка правописания.
- В разделе "Параметры автозамены" нажмите "Параметры автозамены".
- Перейдите на вкладку "Автоформат при вводе". Вы наконец-то добрались до настроек!
- Добавьте флажки к пунктам "Включить новые строки и столбцы в таблицу" и "Заполнить формулы в таблицах для создания вычисляемых столбцов".
- Дважды нажмите "ОК", чтобы вернуться в Excel.
- Нажмите на пустую ячейку рядом с таблицей.
- Введите значение ячейки.
- Нажмите в любом месте за пределами ячейки или нажмите клавишу Enter, чтобы добавить значение.
- Выберите ячейку в строке или столбце таблицы рядом с тем местом, куда вы хотите добавить строку или столбец.
- Вставить строки таблицы выше: вставляет новую строку над выбранной ячейкой.
- Вставить столбцы таблицы слева: вставляет новый столбец слева от выбранной ячейки.
- Выберите ячейку в строке или столбце, которую хотите удалить.
- Нажмите стрелку Удалить список.
- Выберите «Удалить строки таблицы» или «Удалить столбцы таблицы».
С новым объектом "Таблица" вы можете быстро и легко вставить новую строку. Просто выберите первую пустую ячейку под таблицей и начните вводить данные. Excel автоматически расширит объект Table, чтобы включить новую строку и обновить ссылку в ваших функциях COUNTIF(), как показано на рисунке E.
Рисунок E
Объект таблицы Excel достаточно гибок, чтобы включать новые данные и функции, зависящие от обновления.
Чтобы получить максимальную отдачу от расположения таблицы, используйте структурированную ссылку в функциях COUNTIF(). В частности, замените ссылку на абсолютный диапазон именем новой таблицы, как показано на рисунке F. Затем, когда вы вставите новый столбец справа, функции COUNTIF() будут обновлены соответствующим образом.
Рисунок F
После преобразования набора данных в таблицу обновите зависимые ссылки.
Когда вы создаете объект таблицы, Excel дает ему имя — используйте это имя или присвойте ему более осмысленное имя. Щелкните в любом месте таблицы, а затем щелкните контекстную вкладку «Дизайн». Имя таблицы появится в элементе управления «Имя таблицы» в крайнем левом углу, как показано на рисунке G. Чтобы переименовать таблицу, просто щелкните внутри и введите новое имя.
Рисунок G
Используйте имя Excel по умолчанию или переименуйте его.
Реструктуризация
Мне больше всего нравится решение Table, но оно может оказаться непрактичным для вас. Объект таблицы Excel, хотя и мощный, имеет несколько ограничений. По этой причине я хотел бы представить еще одну возможность, которую сложнее реализовать постфактум.
Сейчас структура диапазона данных немного неоднозначна. Более стабильная структура показана на рис. H. Добавление имен в длинный столбец, а не распределение их по нескольким столбцам, немного упрощает реализацию других функций Excel (поскольку вы можете легко сортировать, добавление новых записей для прошлых дат не проблематично). проблема).
Рисунок Н
Вместо того, чтобы идти поперек, идите вниз.
Ссылка на столбец, показанная на рис. 1, обрабатывает новые строки как есть. На листе не должно быть других данных ниже диапазона данных, поскольку ссылка на столбец оценивает весь столбец (включая ячейку заголовка).
Рисунок I
Ссылка на структурированный столбец оценивает новые строки.
Реструктуризация неудобна, особенно если у вас много данных. Если вы сможете реализовать эту структуру изначально, вы сможете избежать проблем по мере роста вашей книги.
Отправьте мне свой вопрос об Office
Опубликовано: 8 сентября 2014 г., 00:05 по тихоокеанскому времени. Изменено: 7 сентября 2014 г., 17:45 по тихоокеанскому времени. Подробнее Microsoft
На этой неделе, работая над клиентским проектом Excel, я столкнулся с некоторыми проблемами с таблицами Excel при добавлении новых данных. Вместо расширения для включения строк таблица их просто игнорировала.
К счастью, эту проблему легко решить, если вы знаете, как это сделать, и если у вас достаточно терпения, чтобы делать много кликов.
Новые данные игнорируются
Таблицы — отличная функция новых версий Excel. Они упрощают добавление, сортировку и фильтрацию данных. Обычно таблицы ведут себя довольно хорошо, если, конечно, вы не пытаетесь добавить в книгу Custom View или сделать что-то с группой листов, содержащих таблицы.
Файл содержит пользовательскую форму для ввода данных, похожую на показанную ниже, которую вы можете загрузить с моего сайта Contextures. Когда вы нажимаете кнопку «Добавить эту часть», новая запись добавляется в конец существующих строк.
На моем компьютере все работало хорошо, поэтому я отправил файл своему клиенту для тестирования. К сожалению, на его компьютере дела пошли не так.
Данные переполняли таблицу, а не расширялись автоматически. В результате новые данные не сортировались с другими элементами и не попадали в выпадающий список по одному из столбцов.
Узнав о проблеме, я попробовал код на нескольких других компьютерах и в разных версиях Excel. Я не смог воспроизвести проблему, поэтому на компьютере моего клиента должна была быть настройка, которая вызывала проблему.
Решить проблему
Изучив несколько вариантов, я наконец понял, что проблема была вызвана настройкой автозамены. Кто вообще помнит, что в Excel есть настройки автозамены?
Я добавил код в пользовательскую форму, чтобы изменить эти настройки, но вы можете внести те же изменения вручную. Однако настройки спрятаны на 5 кликов в глубину, так что возьмите с собой обед в дорогу!
ПРИМЕЧАНИЕ. На вкладке «Дизайн инструментов для работы с таблицами» на ленте много места — возможно, эти настройки можно будет добавить туда в будущей версии Excel. А пока вы можете добавить команду «Параметры автозамены» на одну из вкладок, чтобы упростить доступ к ней.
Например, поместите его на вкладку "Рецензирование" вместе с другими инструментами проверки правописания.
Изменить настройки автозамены
ПРИМЕЧАНИЕ. Изменение этих параметров повлияет на все книги, которые вы открываете в Excel на своем компьютере.
Чтобы исправить таблицы, чтобы они автоматически расширялись и включали новые строки или столбцы, выполните следующие действия:
Видео: таблица Excel не расширяется автоматически
Чтобы увидеть, как настроить таблицу Excel и проверить параметры автозамены, чтобы она автоматически расширялась, посмотрите это короткое видео.
Загрузить образец файла
Чтобы поэкспериментировать с таблицами Excel и получить код для программного изменения настроек автозамены, перейдите на страницу таблиц Excel на моем веб-сайте Contextures.
27 мыслей о «Таблица Excel не расширяется для новых данных»
Спасибо за участие. Вот новый сценарий, для которого я ищу вашу помощь. Я работаю над таблицей (в Excel), где я защитил некоторые строки (с формулами) от модификации, и листы будут доступны пользователю. кто может добавлять/удалять строки из таблицы. я ищу вашу поддержку, чтобы понять, как я могу добавить новую строку с формулами из строки выше без использования VBA.
Очень хорошее и полезное объяснение!
Поздравление
Спасибо, но нет, не решает, так как в моем Excel были обе эти галочки. Я добавил столбец слева от всех столбцов, чтобы использовать его в качестве категории для сортировки. Он обрабатывает это как отдельную таблицу.
У меня была такая же проблема.
Оказалось, что под моей таблицей скрывались невидимые данные.
(Ячейки с одним символом пробела в них, которые были вставлены во время какого-то специального тестирования давным-давно.)
Я предполагаю, что Excel видит заполненные ячейки под таблицей и не добавляет их в новую listRow в таблице.
Удалил эти значения, и все снова заработало как положено.
После создания таблицы Excel на листе вы можете легко добавлять или удалять строки и столбцы таблицы.
Вы можете использовать команду "Изменить размер" в Excel, чтобы добавить строки и столбцы в таблицу:
Щелкните в любом месте таблицы, и появится параметр Работа с таблицами.
Нажмите «Дизайн» > «Изменить размер таблицы».
Выделите весь диапазон ячеек, которые вы хотите включить в таблицу, начиная с самой верхней левой ячейки.
В приведенном ниже примере исходная таблица охватывает диапазон A1:C5. После изменения размера и добавления двух столбцов и трех строк таблица будет охватывать диапазон A1:E8.
Совет. Можно также нажать "Свернуть диалоговое окно", чтобы временно скрыть диалоговое окно "Изменить размер таблицы", выбрать диапазон на листе и нажать "Развернуть диалоговое окно" .
Выбрав нужный диапазон для таблицы, нажмите OK.
Добавьте строку или столбец в таблицу, введя текст в ячейке чуть ниже последней строки или справа от последнего столбца, вставив данные в ячейку или вставив строки или столбцы между существующими строками или столбцами.< /p>
Начните вводить
Чтобы добавить строку внизу таблицы, начните вводить текст в ячейке под последней строкой таблицы. Таблица расширится, чтобы включить новую строку. Чтобы добавить столбец справа от таблицы, начните вводить текст в ячейке рядом с последним столбцом таблицы.
В приведенном ниже примере для строки при вводе значения в ячейку A4 таблица расширяется, чтобы включить эту ячейку в таблицу вместе с соседней ячейкой в столбце B.
В примере, показанном ниже для столбца, ввод значения в ячейку C2 расширяет таблицу, чтобы включить столбец C, называя столбец таблицы Qtr 3, поскольку Excel обнаружил шаблон именования от Qtr 1 и Qtr 2.
Чтобы добавить строку путем вставки, вставьте данные в крайнюю левую ячейку под последней строкой таблицы. Чтобы добавить столбец путем вставки, вставьте свои данные справа от самого правого столбца таблицы.
Если данные, которые вы вставляете в новую строку, содержат столько же или меньше столбцов, чем таблица, таблица расширяется и включает все ячейки вставленного вами диапазона. Если вставляемые данные содержат больше столбцов, чем таблица, дополнительные столбцы не становятся частью таблицы — вам нужно использовать команду «Изменить размер», чтобы расширить таблицу и включить их.
В приведенном ниже примере для строк вставка значений из A10:B12 в первую строку под таблицей (строка 5) расширяет таблицу, чтобы включить вставленные данные.
В приведенном ниже примере для столбцов вставка значений из C7:C9 в первый столбец справа от таблицы (столбец C) расширяет таблицу, чтобы включить вставленные данные, добавляя заголовок Qtr 3.
Используйте вставку, чтобы добавить строку
Чтобы вставить строку, выберите ячейку или строку, не являющуюся строкой заголовка, и щелкните правой кнопкой мыши. Чтобы вставить столбец, выберите любую ячейку в таблице и щелкните правой кнопкой мыши.
Наведите указатель мыши на пункт «Вставить» и выберите «Строки таблицы выше», чтобы вставить новую строку, или «Столбцы таблицы слева», чтобы вставить новый столбец.
Если вы находитесь в последней строке, вы можете выбрать "Строки таблицы выше" или "Строки таблицы ниже".
В приведенном ниже примере для строк строка будет вставлена над строкой 3.
Для столбцов: если у вас выделена ячейка в крайнем правом столбце таблицы, вы можете выбрать между вставкой столбцов таблицы слева или столбцов таблицы справа.
В приведенном ниже примере для столбцов столбец будет вставлен слева от столбца 1.
Выберите одну или несколько строк или столбцов таблицы, которые вы хотите удалить.
Вы также можете просто выбрать одну или несколько ячеек в строках или столбцах таблицы, которые хотите удалить.
На вкладке "Главная" в группе "Ячейки" нажмите стрелку рядом с кнопкой "Удалить", а затем нажмите "Удалить строки таблицы" или "Удалить столбцы таблицы".
Вы также можете щелкнуть правой кнопкой мыши одну или несколько строк или столбцов, выбрать пункт Удалить в контекстном меню, а затем нажать Столбцы таблицы или Строки таблицы. Или вы можете щелкнуть правой кнопкой мыши одну или несколько ячеек в строке или столбце таблицы, выбрать "Удалить", а затем щелкнуть "Строки таблицы" или "Столбцы таблицы".
Точно так же, как вы можете удалить дубликаты из любых выбранных данных в Excel, вы можете легко удалить дубликаты из таблицы.
Нажмите в любом месте таблицы.
При этом отобразятся инструменты для работы с таблицами и добавлена вкладка "Дизайн".
На вкладке "Дизайн" в группе "Инструменты" нажмите "Удалить дубликаты".
В диалоговом окне "Удалить дубликаты" в разделе "Столбцы" выберите столбцы, содержащие дубликаты, которые вы хотите удалить.
Вы также можете нажать «Отменить выбор всех», а затем выбрать нужные столбцы или нажать «Выбрать все», чтобы выбрать все столбцы.
Примечание: дубликаты, которые вы удаляете, удаляются из рабочего листа. Если вы случайно удалили данные, которые хотели сохранить, вы можете использовать Ctrl+Z или нажать «Отменить» на панели быстрого доступа, чтобы восстановить удаленные данные. Вы также можете использовать условные форматы для выделения повторяющихся значений перед их удалением. Дополнительные сведения см. в разделе Добавление, изменение или удаление условных форматов.
Убедитесь, что активная ячейка находится в столбце таблицы.
Нажмите стрелку в заголовке столбца.
Чтобы отфильтровать пробелы, в меню "Автофильтр" в верхней части списка значений снимите флажок (Выбрать все), а затем в нижней части списка значений выберите (Пробелы).
Примечание. Флажок (Пробелы) доступен только в том случае, если диапазон ячеек или столбец таблицы содержит хотя бы одну пустую ячейку.
Выберите пустые строки в таблице и нажмите CTRL+- (дефис).
Вы можете использовать аналогичную процедуру для фильтрации и удаления пустых строк рабочего листа. Дополнительные сведения о том, как фильтровать пустые строки на листе, см. в разделе Фильтрация данных в диапазоне или таблице.
Выберите таблицу, затем выберите Дизайн таблицы > Изменить размер таблицы.
Настройте диапазон ячеек, содержащихся в таблице, и нажмите кнопку ОК.
Важно! Заголовки таблиц нельзя переместить в другую строку, а новый диапазон должен перекрывать исходный диапазон.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Даже после создания таблицы вы можете добавить дополнительные строки и столбцы. Независимо от того, добавляете ли вы новые ячейки в пределах текущего диапазона или рядом с таблицей, они будут автоматически отформатированы в соответствии с текущим стилем таблицы.
Вставить строку или столбец рядом с таблицей
Новая строка или столбец добавляются в таблицу и применяется форматирование таблицы.
Когда формула вводится в пустой столбец таблицы, формула автоматически заполняет остальную часть столбца без использования функции автозаполнения. Если в столбец добавляются строки, формула также появляется в этих строках.
Вставить строку или столбец в таблицу
Параметры вставки недоступны, если выбран заголовок столбца.
Щелкните правой кнопкой мыши строку или столбец рядом с тем местом, куда вы хотите добавить данные, выберите в меню пункт "Вставить" и выберите вариант вставки.
Удалить строки и столбцы
Вы также можете удалить ненужные строки и столбцы таблицы, удалив их.
Щелкните правой кнопкой мыши строку или столбец, которые хотите удалить, выберите в меню пункт "Удалить" и выберите "Столбцы таблицы" или "Строки таблицы".
Выбранные строки или столбцы и все содержащиеся в них данные будут удалены.
БЕСПЛАТНЫЙ краткий справочник
Бесплатно для распространения с нашей благодарностью; мы надеемся, что вы рассмотрите наше платное обучение.
Читайте также: