Как использовать функции TAKE и DROP в Excel
Введение
Когда вы анализируете таблицу в Microsoft Excel, часто нужно работать не со всем набором данных, а с его частью. Для таких задач в Excel есть функции TAKE и DROP. Они работают с массивами и возвращают массивы, поэтому не требуют специальной комбинации клавиш для ввода формул.
В этом руководстве вы найдёте понятные определения, практические примеры, рекомендации по сочетанию с другими функциями и подсказки на случай ошибок и ограничений.
Что делает функция TAKE
Функция TAKE извлекает указанное количество строк и/или столбцов из массива.
Синтаксис:
=TAKE(array, rows, [columns])Параметры:
- array: исходный массив или диапазон.
- rows: число строк для извлечения. Отрицательное число берёт строки с конца.
- columns: (необязательный) число столбцов для извлечения. Отрицательное значение берёт столбцы с конца.
Кратко: TAKE берёт фрагмент массива, не изменяя исходные данные.
Что делает функция DROP
Функция DROP исключает указанное число строк и/или столбцов из массива.
Синтаксис:
=DROP(array, rows, [columns])Параметры аналогичны TAKE:
- array: исходный массив или диапазон.
- rows: число строк, которые нужно удалить. Отрицательное число удаляет строки с конца.
- columns: число столбцов для удаления. Отрицательное значение удаляет столбцы с конца.
Коротко: DROP возвращает массив за вычетом указанных строк/столбцов.
Важно: TAKE и DROP доступны в Excel для Microsoft 365, Excel для Mac в составе Microsoft 365 и Excel для веб. В старых настольных версиях их нет.
Основные примеры использования
Ниже приведены практические кейсы с пошаговыми инструкциями.
Пример 1. Извлечь первые пять строк
Предположим, что у вас есть таблица продуктов и их цен. Вместо копирования вы можете получить первые 5 строк так:
=TAKE(B2:F21, 5)Эта формула возвращает первые 5 строк из диапазона B2:F21.
Пример 2. Получить первый столбец
Если нужно взять все строки только первого столбца диапазона, укажите пустой параметр rows и передайте columns:
=TAKE(B2:F21,,1)Пустой параметр rows означает «все строки», возвращая весь первый столбец.
Пример 3. Исключить последние 10 строк
Чтобы показать всё, кроме последних 10 строк, используйте DROP с отрицательным значением:
=DROP(B2:F21, -10)Отрицательное rows указывает, что удаляются строки с конца диапазона.
Пример 4. Удалить последний столбец
Чтобы отсечь последний столбец диапазона:
=DROP(B2:F21,, -1)Это вернёт массив без последнего столбца.
Сочетание с другими функциями
Гибкость растёт, когда вы комбинируете TAKE и DROP с другими динамическими функциями.
Пример: получить первую ячейку среди товаров с ценой выше N100:
=TAKE(FILTER(B3:E8, D3:D8>100), 1, 1)Здесь FILTER возвращает все строки с ценой больше N100, а TAKE берёт первый элемент результата.
Полезные сочетания:
- FILTER + TAKE — выбрать первые N результатов после фильтрации.
- SORT + TAKE — взять топ по метрике.
- TAKE + DROP — выделить середину диапазона: сначала DROP первой части, затем TAKE нужного объёма.
- LET + TAKE — дать имена промежуточным массивам для читаемости формул.
Пример использования LET для читаемости:
=LET(
data, B2:F21,
filtered, FILTER(data, D2:D21>100),
TAKE(filtered, 3)
)Когда TAKE и DROP не работают или дают ошибки
Важно понимать ограничения и частые причины ошибок.
- Ошибка #SPILL: результирующий массив не помещается в соседние ячейки. Освободите пространство или используйте одну ячейку для массива.
- Старые версии Excel: функции отсутствуют и формула выдаст ошибку или не распознается. Решение — заменять на INDEX/SEQUENCE/OFFSET или использовать Power Query.
- Несовместимые размеры: при математических операциях с массивами проверьте согласованность размеров.
- Мerged cells: объединённые ячейки часто приводят к ошибкам разброса массива.
Примеры обходов для старых версий:
- Взять первые N строк: INDEX + SEQUENCE + TRANSPOSE/IFERROR комбинация.
- Удалить последние строки: использовать INDEX с вычислением последней строки через COUNTA.
Практические приёмы и подсказки
- Используйте отрицательные аргументы для удобного обращения к концам массива.
- Для динамических таблиц (Excel Tables) передавайте именованные диапазоны или структурированные ссылки.
- Когда результат потенциально большой, проверяйте свободные ячейки и препятствия, вызывающие #SPILL.
- В сложных сценариях используйте LET для разбиения формулы на логические части.
Мини‑методология: как выбрать TAKE или DROP
- Определите цель: нужно взять часть массива или убрать часть массива.
- Если нужна «передняя» часть — используйте TAKE. Если нужно «отсечь» часть — DROP.
- Подумайте о порядке операций: сначала фильтрация, сортировка, затем TAKE/DROP.
- Тестируйте формулу на небольшом подмножестве, прежде чем применять на всей таблице.
Альтернативные подходы
- INDEX + SEQUENCE: рабочая альтернатива для извлечения диапазонов в старых версиях.
- OFFSET: делает смещения динамическими, но является волатильной функцией и может замедлять большой файл.
- Power Query: лучше для подготовки и трансформации больших наборов данных вне листа.
Контрпримеры и ограничения
- TAKE(B2:F21, 1000) на маленьком диапазоне вернёт #REF или #SPILL, если размер выходит за границы.
- DROP с положительным значением rows удаляет строки с начала, а с отрицательным — с конца. Неправильный знак даёт неожиданный результат.
- Если массив содержит ошибки, результат TAKE/DROP может наследовать эти ошибки.
Чеклист для разных ролей
Аналитик:
- Проверил, что результат помещается на листе
- Указал правильный знак для отрицательных аргументов
- Протестировал на граничных случаях (пустые данные, все значения ниже порога)
BI‑разработчик:
- Использует LET для читаемости
- Комбинирует с SORT и FILTER для детерминированного вывода
- Обрабатывает потенциальные ошибки через IFERROR или ISERROR
Бухгалтер:
- Убедился, что ссылки на таблицы обновляются при вставке строк
- Проверил совместимость с корпоративной версией Excel
Тестовые кейсы и критерии приёмки
Кейс 1: входной диапазон 10 строк, формула =TAKE(range, 5) — ожидается массив из 5 строк.
Кейс 2: входной диапазон пуст, формула =DROP(range, -3) — ожидается пустой массив без ошибок.
Критерии приёмки:
- Формула возвращает корректный размер массива
- Нет #SPILL в тестовой области
- При передачи результата в другую функцию (например, SUM) результат совпадает с ожидаемым ручным вычислением
Безопасность и конфиденциальность
Функции сами по себе не передают данные извне, но при комбинировании с внешними источниками (Power Query, подключениями) помните про ограничения доступа и конфиденциальность.
Примеры быстрого шпаргалки
- TAKE(range, n) — взять первые n строк
- TAKE(range, -n) — взять последние n строк
- TAKE(range,, m) — взять m первых столбцов
- DROP(range, n) — удалить первые n строк
- DROP(range, -n) — удалить последние n строк
Советы по совместимости и миграции
- Для пользователей старых версий подготовьте альтернативные формулы с INDEX/SEQUENCE/COUNTA.
- При миграции шаблонов проверьте макросы и сторонние плагины на совместимость с динамическими массивами.
Частые ошибки и как их исправить
- Проблема: #SPILL. Решение: освободите соседние ячейки или укажите подходящий диапазон вывода.
- Проблема: функция не распознаётся. Решение: обновите Excel до Microsoft 365 или используйте альтернативы.
- Проблема: неверный знак для отрицательных аргументов. Решение: проверьте документацию и тестовые примеры.
Итог
Функции TAKE и DROP упрощают работу с частями массивов и повышают читабельность формул при работе с динамическими данными. Они хорошо сочетаются с FILTER, SORT и LET. Для совместимости со старыми версиями подготовьте альтернативные формулы на базе INDEX/SEQUENCE или используйте Power Query.
Важно: перед развёртыванием в рабочем процессе протестируйте формулы на граничных случаях и убедитесь, что файл остаётся быстрым и без лишних #SPILL.
Краткие шаги для начала:
- Попрактикуйтесь на копии файла.
- Используйте LET для сложных вычислений.
- Обрабатывайте ошибки через IFERROR или надежные проверки данных.
Спасибо за чтение. Если нужно, пришлите пример вашего диапазона — помогу составить формулу под задачу.
