Функции TAKE и DROP в Excel: как извлекать и исключать части массива

TL;DR

TAKE извлекает указанное количество строк и/или столбцов из массива. DROP исключает строки и/или столбцы из массива. Обе функции возвращают динамический массив, доступны в Microsoft 365 и Excel в браузере. Ниже — синтаксис, примеры, распространённые ошибки, альтернативы и практические чек-листы.

Что такое функция TAKE в Excel

Функция TAKE извлекает указанное количество строк и/или столбцов из исходного массива. Она полезна, когда нужно получить «часть» табличного диапазона без копирования и вставки.

Синтаксис:

=TAKE(array, rows, [columns])

Краткое значение параметров:

• array — исходный массив или диапазон.
• rows — число строк для извлечения. Отрицательное значение берёт строки с конца массива.
• columns (необязательный) — число столбцов для извлечения. Отрицательное значение берёт столбцы с конца.

Важно: TAKE всегда возвращает массив; это динамическая формула, поэтому результат «выплескивает» (SPILL) в соседние ячейки при наличии места.

Что такое функция DROP в Excel

Функция DROP исключает указанное количество строк и/или столбцов из исходного массива. Используется для получения «оставшейся» части данных.

Синтаксис:

=DROP(array, rows, [columns])

Параметры:

• array — исходный массив или диапазон.
• rows — число строк для удаления. Отрицательное значение удаляет строки с конца.
• columns (необязательный) — число столбцов для удаления. Отрицательное значение удаляет столбцы с конца.

Пример: если array — таблица из 100 строк, то =DROP(array,10) вернёт первые 90 строк, а =DROP(array,-10) вернёт первые 90 строк (удаляя последние 10).

Important: TAKE и DROP работают только с динамическими массивами. В старых версиях Excel эти функции отсутствуют.

Как использовать TAKE: практические примеры

Ниже — несколько типичных сценариев и формул.

Извлечь первые 5 строк

Если таблица находится в диапазоне B2:F21, то формула для первых пяти строк:

=TAKE(B2:F21, 5)

Результат — массив из первых 5 строк и всех столбцов исходного диапазона.

Извлечь первый столбец во всём диапазоне

Если нужно получить только первый столбец диапазона B2:F21, можно оставить параметр rows пустым и указать столбец:

=TAKE(B2:F21, , 1)

Пустой параметр rows означает «все строки», затем параметр columns = 1 ограничивает только первым столбцом.

Взять последние N строк с помощью отрицательного значения

Чтобы получить последние 3 строки из диапазона B2:F21:

=TAKE(B2:F21, -3)

Отрицательное rows указывает на счёт с конца.

Как использовать DROP: практические примеры

Исключить последние 10 строк

Если нужно показывать всё, кроме последних 10 строк, и исходный диапазон B2:F21 имеет 21 строку, формула:

=DROP(B2:F21, -10)

Это удалит 10 строк с конца и вернёт остальные.

Исключить последний столбец

Чтобы убрать последний столбец диапазона B2:F21:

=DROP(B2:F21, , -1)

columns = -1 удаляет последний столбец исходного диапазона.

Комбинация TAKE/DROP с другими функциями

Часто TAKE и DROP применяются совместно с FILTER, SORT, UNIQUE и INDEX для построения гибких формул.

Пример: вернуть первое значение из таблицы B3:E8, где цены в D3:D8 больше 100:

=TAKE(FILTER(B3:E8, D3:D8>100), 1, 1)

Разбор:

1. FILTER(B3:E8, D3:D8>100) — отбрасывает строки с ценой ≤100.
2. TAKE(…, 1, 1) — берёт первую строку и первый столбец из отфильтрованного результата.

Этот приём упрощает поиск «первого подходящего» результата без использования дополнительных вспомогательных столбцов.

Типичные ошибки и как их исправить

• #SPILL! — результат не вмещается в соседние ячейки. Решение: очистите диапазон, куда формула должна «выплеснуться», или поместите формулу в более свободную область.
• Неправильные диапазоны (например, B2:F1) — проверьте, что диапазон действительно содержит ожидаемые строки и столбцы.
• Отсутствие функций в старой версии Excel — используйте альтернативы (см. раздел “Альтернативы”).

Альтернативы TAKE и DROP в старых версиях Excel

Если у вас нет доступа к TAKE/DROP (например, Excel 2016), используйте комбинации INDEX, SEQUENCE, OFFSET и INDEX+MATCH:

• Взять первые N строк: INDEX вместе с SEQUENCE или конструкция OFFSET.
• Взять конкретный столбец: INDEX(range, 0, column_number) возвращает весь столбец как массив (в новых Excel это удобно; в старых — нужно подтверждать как CSE-формулу).

Пример через INDEX+SEQUENCE для первых 5 строк из B2:F21:

=INDEX(B2:F21, SEQUENCE(5), SEQUENCE(1, COLUMNS(B2:F21)))

Этот приём имитирует поведение TAKE, но сложнее в поддержке.

Ментальные модели и правила-эвристики

• TAKE = взять кусок (think: head/tail) — положительное rows берёт сверху, отрицательное — снизу.
• DROP = отбросить кусок — положительное rows отбрасывает сверху, отрицательное — снизу.
• Оба работают на исходном массиве и возвращают новый динамический массив.
• Всегда проверяйте направление счёта при отрицательных значениях: они читаются как «с конца».

Когда эти функции не подходят (контрпримеры)

• Если вы хотите изменить исходный диапазон физически (удалить строки/столбцы), эти функции лишь создают представление, а не модифицируют лист.
• Для больших промежуточных таблиц, где требуется потоковая обработка данных в макросах, удобнее VBA/Power Query.
• Если нужна обратная совместимость с Excel без динамических массивов, TAKE и DROP не подойдут.

Роль‑базовый чек‑лист перед использованием

• Аналитик:

  • Убедиться, что рабочий файл открыт в Microsoft 365.
  • Проверить, куда «выплеснется» массив (свободные ячейки).
  • Протестировать на выборке данных.

• Разработчик отчётов:

  • Согласовать поведение при пустых значениях и ошибках.
  • Добавить обработку ошибок (IFERROR) и тестовые данные.

• Менеджер/владелец данных:

  • Подтвердить требования к выборке: какие строки/столбцы нужны.
  • Уточнить, нужна ли персистентная модификация данных или только представление.

Критерии приёмки

• Формулы возвращают ожидаемое количество строк/столбцов для всех тестовых диапазонов.
• Финальные ячейки не содержат #SPILL при корректной подготовке рабочего пространства.
• Решение совместимо с требованиями совместимости (Microsoft 365 или указана альтернатива).

Тестовые случаи (минимум для проверки)

1. TAKE(B2:F21,5) — ожидание: 5 строк, все столбцы.
2. TAKE(B2:F21,,-1) — ожидание: весь диапазон, только первый столбец? (проверьте логику параметров).
3. DROP(B2:F21,-10) — ожидание: исходные строки без последних 10.
4. Комбинация FILTER + TAKE с пустым фильтром — должно возвращать пустой массив без ошибок.
5. Формулы в области с занятыми ячейками — должны вернуть #SPILL указывающий на проблему места.

Краткий словарь

• TAKE — извлечь часть массива.
• DROP — исключить часть массива.
• SPILL — поведение динамического массива, когда результат «выплескивается» в соседние ячейки.
• FILTER — функция, возвращающая подмножество строк по условию.

Безопасность и конфиденциальность

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

Заключение

TAKE и DROP упрощают манипуляции с массивами в Excel: извлекать верхние/нижние части, убирать ненужные строки или столбцы и передавать результат в другие функции. Если у вас Microsoft 365 — используйте их для компактных и понятных формул. Если нет — заменяйте на комбинации INDEX/SEQUENCE/OFFSET или Power Query.

Краткие рекомендации:

• Проверяйте область «выплеска» (SPILL) перед размещением формул.
• Для совместимости документируйте альтернативные формулы для старых версий Excel.
• Комбинируйте TAKE/DROP с FILTER и SORT для гибких выборок.

Требуется пример формулы под вашу задачу или помощь с превращением существующей формулы в TAKE/DROP? Опишите диапазон и ожидаемый результат — предложу точную формулу.