Функция Excel DROP для удаления определенных строк или столбцов из массива
В этом руководстве вы познакомитесь с совершенно новой функцией Excel 365, которая может удалять ненужные строки и столбцы, чтобы вернуть соответствующее подмножество данных.
При анализе огромных наборов данных иногда может потребоваться извлечь только данные без общей строки/столбца, верхних или нижних колонтитулов. В более ранних версиях Excel вам приходилось полагаться на старый добрый метод копирования/вставки. В Excel 365 у нас есть специальная функция для динамического извлечения нужной части массива путем «отбрасывания» ненужных данных.
Excel функция DROP
Функция DROP в Excel удаляет указанное количество строк и/или столбцов из начала или конца массива.
Синтаксис включает следующие аргументы:
DROP(массив, строки, [columns])
Где:
Массив (обязательно) — исходный массив или диапазон.
Ряды (необязательно) — количество строк, которые необходимо удалить. Положительное значение удаляет строки из начала массива, а отрицательное значение — из конца массива. Если он опущен, столбцы должны быть установлены.
Столбцы (необязательно) — количество столбцов, которые необходимо удалить. Положительное целое число исключает столбцы из начала массива, а отрицательное целое число — из конца массива. Если он опущен, строки должны быть определены.
Вот функция DROP в действии:
Наличие функции DROP
Функция DROP доступна только в Excel для Microsoft 365 (для Windows и Mac) и Excel для Интернета.
Как работает функция DROP
Следующие 5 простых фактов помогут вам лучше понять внутреннюю механику функции DROP:
- DROP — это функция динамического массива. Вы вводите формулу в верхнюю левую ячейку целевого диапазона, и она автоматически распределяет результаты по необходимому количеству столбцов и строк.
- Аргумент массива может быть диапазоном ячеек, константой массива или массивом значений, возвращаемых другой функцией.
- Аргументы строк и столбцов могут быть положительными или отрицательными целыми числами. Положительные числа удаляют строки/столбцы с начала массива; отрицательные числа – с конца.
- Аргументы строк и столбцов необязательны, но хотя бы один из них должен быть задан в формуле. Пропущенный аргумент по умолчанию равен нулю.
- Если абсолютное значение строк или столбцов больше или равно общему количеству строк и столбцов в массиве, #CALC! возвращается ошибка.
Как использовать функцию DROP в Excel – примеры формул
Вооружившись теорией, давайте перейдем к практическим делам и посмотрим, как можно вернуть часть массива, отбросив ненужные строки или столбцы.
Удалить строки из начала массива
Чтобы удалить определенное количество строк из начала двумерного массива или диапазона, укажите положительное число для аргумента строк.
Например, чтобы удалить первые 5 строк из диапазона A3:C16, используйте следующую формулу:
=УДАЛИТЬ(A3:C16, 5)
Формула вводится в ячейку E3 и автоматически заполняет результаты в столько строк и столбцов, сколько необходимо.
Удалить столбцы из начала массива
Чтобы удалить заданное количество столбцов из начала двумерного массива или диапазона, укажите положительное число в качестве аргумента столбцов.
Например, чтобы удалить первый столбец из диапазона A3:C16, используйте следующую формулу:
=УДАЛИТЬ(A3:C16, ,1)
Формула помещается в ячейку E3 и распределяется по двум столбцам и столько строк, сколько имеется в исходном диапазоне.
Удаление строк и столбцов одновременно
Чтобы удалить и строки, и столбцы из начала массива, укажите положительные числа для аргументов строк и столбцов.
Например, чтобы убрать первые 5 строк и 1 столбец из нашего набора данных, используйте следующую формулу:
=УДАЛИТЬ(A3:C16, 5, 1)
Удалить последние N строк
Чтобы удалить строки из конца массива, используйте отрицательное число для аргумента строк. Например:
Чтобы удалить последнюю строку, поставьте -1:
=УДАЛИТЬ(A3:C16, -1)
Чтобы исключить последние 3 строки, укажите -3:
=УДАЛИТЬ(A3:C16, -3)
Чтобы удалить последние 5 строк, введите -5:
=УДАЛИТЬ(A3:C16, -5)
На скриншоте ниже показан результат последней формулы.
Удалить последние N столбцов
Чтобы исключить несколько столбцов из конца массива, укажите отрицательное число в качестве аргумента столбцов. Например:
Чтобы удалить последний столбец, используйте -1:
=УДАЛИТЬ(A3:D16, , -1)
Чтобы удалить последние 2 столбца, используйте -2:
=УДАЛИТЬ(A3:D16, , -2)
Чтобы удалить последние 3 столбца, используйте -3:
=УДАЛИТЬ(A3:D16, , -3)
И вот результат с удаленными двумя последними столбцами:
Совет. Чтобы удалить как строки, так и столбцы из конца массива, укажите отрицательные числа для аргументов строк и столбцов.
Сортировать и бросать
В ситуации, когда вы хотите переупорядочить значения в исходном массиве перед удалением некоторых столбцов или строк, используйте функцию SORT, которая может сортировать массив по любому столбцу, который вы хотите, в порядке возрастания или убывания.
Например, вы можете отсортировать приведенный ниже диапазон по 4-му столбцу (с именем «Среднее») от самого высокого к самому низкому, используя эту формулу:
=ЧЕРНЫЙ(A3:D16, 4, -1),
Затем передайте отсортированный массив функции DROP, указав ей пропустить 5 самых низких результатов:
= УДАЛИТЬ (СОРТИРОВАТЬ (A3: D16, 4, -1), -5)
Как удалить строки/столбцы из нескольких диапазонов
При работе с несколькими несмежными диапазонами можно сразу “объединить и удалить”, объединив пару функций в одной формуле:
- Сложите несколько диапазонов в один, используя функцию VSTACK или HSTACK.
- Удалите определенное количество строк или столбцов из массива с накоплением, используя функцию DROP.
В зависимости от того, как структурированы исходные данные, вам может подойти один из следующих подходов.
Объединение диапазонов по вертикали и удаление строк или столбцов
Предположим, у вас есть 3 отдельных диапазона, как показано на скриншоте ниже. Для объединения диапазонов по вертикали сверху вниз используется формула VSTACK:
=VSTACK(A4:D7, A11:D15, A19:D23)
Вложите его в аргумент массива DROP, укажите, сколько строк или столбцов нужно удалить, и вы получите результат, который ищете.
Например, чтобы удалить последний столбец из сложенного массива, формула принимает следующий вид:
=DROP(VSTACK(A4:D7, A11:D15, A19:D23), , -1)
И результат выглядит следующим образом:
Объединяйте диапазоны по горизонтали и удаляйте строки или столбцы
В случае, если исходные диапазоны расположены горизонтально в ряды, вы можете объединить их в единый массив с помощью функции HSTACK. В нашем случае это формула для использования:
=HSTACK(B3:E6, H3:K6)
Затем вы вставляете приведенную выше формулу в аргумент массива функции DROP и настраиваете аргументы строк и столбцов по мере необходимости.
В этом примере мы используем -1 для аргумента строки, чтобы удалить последнюю строку:
= УДАЛИТЬ (СТЕК (B3: E6, H3: K6), -1)
DROP против TAKE в Excel
В Excel 365 есть еще одна полезная функция для возврата части массива или диапазона — функция TAKE. По сути, эти две функции выполняют одну и ту же задачу, но по-разному. В то время как DROP исключает определенные строки и/или столбцы из массива, TAKE получает определенные строки и/или столбцы из массива.
Например:
Чтобы удалить первые 3 строки, вы используете:
=УДАЛИТЬ(массив, 3)
Чтобы извлечь первые 3 строки, вы используете:
=ВЗЯТЬ(массив, 3)
Функция Excel DROP не работает
Если функция DROP не работает в вашем Excel или приводит к ошибке, это, скорее всего, одна из этих причин.
DROP недоступен в вашей версии Excel
DROP — это новая функция с ограниченным доступность. Если ваша версия отличается от Excel 365, формула вернет ошибку #ИМЯ, как если бы вы неправильно написали имя функции.
Пустой массив
Если ваша формула DROP настроена на удаление всех или большего количества строк или столбцов, чем есть в массиве, #CALC! ошибка будет указывать на то, что возвращать нечего.
Недостаточно пустых ячеек для вывода результатов
Если ниже и/или справа от формулы недостаточно пустых ячеек, возникает ошибка #SPILL. Чтобы исправить это, просто очистите диапазон разлива. Дополнительные сведения см. в разделе Как исправить ошибку #SPILL! ошибка в экселе.
Вот как можно использовать функцию DROP в Excel для удаления строк или столбцов из массива. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Формула Excel DROP – примеры (файл .xlsx)
