Как вставить определенное количество строк через фиксированные интервалы в Excel?

Добавление и удаление строк и столбцов в Numbers на Mac

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

Основные строки и столбцы содержат табличные данные.

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

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

Итоговые строки (если они есть) отображаются в нижней части таблицы. В таблице может быть максимум пять итоговых строк.

Динамический диапазон с автоподстройкой размеров

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

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

Все это, вместе взятое, развлечет вас 😉

Гораздо более удобным и точным решением был бы динамический диапазон «резиновой ленты», который автоматически подстраивается по размеру с учетом количества строк и столбцов. Существует несколько способов его реализации.

Способ 1. Умная таблица

Выделите ряд ячеек и на вкладке Главная выберите Главная — Форматы как таблица:

Если вам не нужен полосатый дизайн, который добавляется к таблице в качестве побочного эффекта, вы можете отключить его на появившейся вкладке Design. Этот метод создает таблицу с именем, которое можно изменить на вкладке Design в поле Table Name.

Использование динамических ссылок на нашу смарт-таблицу теперь возможно:

• Table1 — ссылка на всю таблицу, кроме строки заголовка (A2:D5)
• Table1[#All] — ссылка на всю таблицу (A1:D5)
• Table1[Stone] — ссылка на диапазон столбцов без первой ячейки заголовка (C2:C5)
• Table1[#Headers] — ссылка на «шапку» с названиями столбцов (A1:D1) .

Такие ссылки очень хорошо работают в формулах, например:

=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»

=FPR(F5;Table1;3;0) — поиск в таблице месяца в ячейке F5 и печать для него суммы петра (что такое FPR? ).

Эти ссылки можно успешно использовать при создании разворотных таблиц, выбрав Вставка — Разворотная таблица и введя в качестве источника данных имя разворотной таблицы:

В этом случае при выделении фрагмента таблицы (например, первых двух столбцов) и создании графика любого типа новые строки будут автоматически добавлены к графику.

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

То есть ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а ее выпадающий список хорошо воспринимается.

Способ 2. Динамический именованный диапазон

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

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

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

Ищем последнюю ячейку с помощью ПОИСКПОЗ

SEARCH_value;range;match_type) — это функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, в которой оно было найдено. Например, формула FIND(«Март»;A1:A5;0) возвращает число 4, потому что «Март» находится в четвертой ячейке столбца A1:A5. Последний аргумент типа совпадения = 0 означает, что мы ищем точное совпадение. Если этот аргумент не указан, функция продолжает поиск наименьшего ближайшего значения — это можно успешно использовать для поиска последней занятой ячейки в нашей таблице.

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

Если наш массив содержит только числа, то в качестве искомого значения можно указать число, которое заведомо больше любого другого в таблице:

В качестве альтернативы можно использовать 9E+307 (9, деленное на 10 в 307-й степени), а не 29E+307 (это 29E, умноженное на 9). 9 с 307 нулями) — максимальное число, с которым в принципе может работать Excel.

Используя конструкцию REPEAT(«me»;255), мы можем создать текстовую строку, состоящую из 255 символов «me», как эквивалент наибольшего числа. Точная длина строки «IYaYaYa.ya» может показаться вам чрезмерной, но на самом деле Excel сравнивает коды символов при поиске:

Формируем ссылку с помощью ИНДЕКС

Теперь, когда мы знаем расположение последнего непустого элемента в массиве, остается только сгенерировать ссылку на весь наш домен. Это можно сделать с помощью функции:

ИНДЕКС(диапазон; номер строки; номер столбца)

При использовании функции = INDEX(A1:D5;3;4) мы получим 1240, что соответствует содержимому 3-й строки и 4-го столбца (ячейка D3) в нашей таблице с городами и месяцами из предыдущего метода. Если столбец только один, то его номер указывать нельзя. формула INDEX(A2:A6;3) выведет «Самара» на последнем скриншоте.

И еще один не очень очевидный нюанс: если INDEX вводится в ячейку не после знака =, как обычно, а используется как последняя часть ссылки на поле после двоеточия, то он выдает не содержимое ячейки, а ее адрес! Формула вида $A$2:INDEX($A$2:$A$100;3), таким образом, показывает ссылку на диапазон A2:A4.

Чтобы определить конец списка динамически, мы вставляем функцию ПОИСК в ИНДЕКС:

=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР("я";255) ;A2:A100))

Создаем именованный диапазон

Теперь вам остается только собрать чемоданы. Откройте вкладку Формулы и нажмите на кнопку Менеджер имен. В открывшемся окне нажмите кнопку New и введите имя нашего диапазона и формулу в поле Reference:

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