DROP в Excel: как удалять строки и столбцы из массива

Переходы по статье

• Синтаксис DROP

• Примеры: как работает DROP

• Использование DROP с другими функциями

Функция DROP — одна из наименее используемых, но очень полезных функций в категории «Справка и поиск» Microsoft Excel. Она позволяет удалить указанное число строк или столбцов с начала или конца массива, не меняя исходный набор данных. Это особенно удобно, когда вы хотите быстро получить подмножество данных или подготовить анонимизированный представленный массив.

Функция DROP доступна в Excel для Microsoft 365, в Excel в вебе и в мобильных/планшетных приложениях Excel (версии, поддерживающие динамические массивы).

Microsoft 365 Personal

ОС: Windows, macOS, iPhone, iPad, Android

Бесплатная пробная версия: 1 месяц

Подписка Microsoft 365 включает доступ к приложениям Office (Word, Excel, PowerPoint) на нескольких устройствах, 1 ТБ в OneDrive и дополнительные сервисы.

Цена: $100 на сайте Microsoft

Синтаксис DROP

Формула DROP в Excel имеет три аргумента:

=DROP(a,b,c)

где

• a — массив, из которого нужно удалить строки или столбцы;
• b — количество строк, которые нужно удалить (положительное число удаляет строки сверху, отрицательное — снизу);
• c — количество столбцов, которые нужно удалить (положительное число удаляет столбцы слева, отрицательное — справа).

Ключевые замечания:

• Аргумент a обязателен. По крайней мере один из аргументов b или c должен быть указан, иначе будет ошибка.
• Для b и c положительное значение удаляет с начала соответствующего измерения (верх/лево). Отрицательное — с конца (низ/право).
• Если число, переданное в b или c, превышает число строк или столбцов массива, возвращается ошибка CALC!.
• DROP — динамическая функция. Результат «выливается» (spills) в соседние ячейки. Формула должна быть введена в обычную ячейку, а не в столбец таблицы Excel.

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

Примеры: как работает DROP

Во всех примерах далее используется одна и та же тестовая таблица: в ней столбцы содержат ID студента (A), пол (B), класс (C) и баллы теста (D).

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

Упражнение 1: удалить строки сверху

Представим, что нужно получить все записи, кроме первых 10 студентов по убыванию баллов. Сначала отсортируйте таблицу по столбцу «Score» в порядке убывания (кнопка фильтра → Sort Largest To Smallest).

Один из простых вариантов — жестко прописать число строк для удаления:

=DROP(T_Stud,10)

где T_Stud — таблица с исходными данными, а 10 — число удаляемых строк сверху.

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

=DROP(T_Stud,H1)

где в H1 вводится число строк для удаления. Это делает формулу гибкой и удобной для интерактивных отчётов.

Советы:

• Проверяйте, что формула введена вне таблицы; иначе результат не сможет вылиться.
• При обновлении исходной таблицы (добавлении строк) значение, хранящееся в H1, автоматически применится к новому объёму данных, если вы используете структурированные ссылки.

Упражнение 2: удалить строки снизу

Чтобы удалить строки с конца массива, используйте отрицательное число для аргумента b. Например, чтобы исключить 10 последних записей:

=DROP(T_Stud2,-H1)

Здесь H1 содержит 10, а знак минус в формуле указывает на удаление снизу.

Примечание: минус можно поставить в ячейке (в H1), в отдельной ячейке или непосредственно в формуле.

Упражнение 3: удалить столбцы слева

Если нужно удалить первый (левый) столбец, пропустите аргумент b и укажите c:

=DROP(T_Stud3,,1)

Это удалит первый столбец и вернёт все остальные.

Упражнение 4: удалить столбцы справа

Чтобы удалить правые столбцы, укажите отрицательное значение:

=DROP(T_Stud4,,-1)

Минус означает «с правой стороны». Если потом добавить ещё столбцы справа от исходной таблицы, DROP будет продолжать удалять правую границу, пока число удалённых столбцов соответствует указанному значению.

Упражнение 5: удалить строки и столбцы одновременно

Можно указывать оба аргумента одновременно. Например, получить ID и пол студентов, исключив 10 последних строк:

=DROP(T_Stud5,-H1,-2)

Здесь -H1 удаляет 10 строк снизу, а -2 удаляет два правых столбца.

Использование DROP вместе с другими функциями

DROP особенно ценна в сочетании с динамическими функциями, такими как SORT, VSTACK, HSTACK, CHOOSECOLS и CHOOSEROWS. Ниже — примеры комбинирования функций для более надежной и воспроизводимой логики.

Упражнение 6: сначала сортировать, затем удалять

Если порядок результата DROP важен независимо от порядка исходных данных, отсортируйте массив внутри DROP:

=DROP(SORT(T_Stud6,4,-1),-H1,1)

Пояснение аргументов:

• 4 — сортировка по 4-му столбцу (Score);
• -1 — сортировка по убыванию;
• -H1 — удалить последние 10 строк (значение 10 в H1 с минусом);
• 1 — удалить первый столбец.

Это гарантирует, что результат отсортирован по баллам вне зависимости от текущего порядка источника.

Упражнение 7: VSTACK + SORT + DROP для объединения классов

Если данные распределены по таблицам классов, их можно объединить и затем обработать одним выражением:

=DROP(SORT(VSTACK(T_Blue,T_Green,T_Yellow),3,-1),G1)

где VSTACK объединяет таблицы, SORT упорядочивает по 3-му столбцу (Score), а DROP удаляет первые G1 строк.

Аналогично можно использовать HSTACK для склеивания данных по горизонтали.

Упражнение 8: комбинировать CHOOSECOLS и DROP

Чтобы выбрать только один столбец, а затем удалить строки, используйте CHOOSECOLS внутри DROP:

=DROP(CHOOSECOLS(SORT(T_Stud8,4,-1),3),-I1)

Пошагово:

• SORT(T_Stud8,4,-1) — сортировка по 4-му столбцу;
• CHOOSECOLS(…,3) — выбрать третий столбец (например, «Class»);
• DROP(…,-I1) — удалить последние I1 строк.

Ограничение: при вложении CHOOSECOLS в DROP можно указать только количество строк для удаления (b). Если вложить CHOOSEROWS, то внутри DROP можно указывать только столбцы (c). Это сделано, чтобы избежать противоречивых указаний Excel о форме массива.

Когда DROP не подходит (контрпример)

• Если нужно удалить отдельные внутренние строки/столбцы (не с краёв) — DROP не годится. Для этого используйте FILTER, INDEX/SEQUENCE комбинации или Power Query.
• Если требуется условная фильтрация (например, убрать студентов с оценкой <50), лучше использовать FILTER.
• Если вы работаете в классической версии Excel без динамических массивов, DROP недоступна.
• Если массив слишком мал и вы передаёте больше элементов для удаления, получите ошибку CALC!.

Альтернативные подходы

• TAKE — противоположность DROP: выбирает первые/первые/правые элементы. Синтаксис аналогичен.
• FILTER — позволяет отфильтровать строки по условию.
• INDEX + SEQUENCE — гибкая комбинация для создания срезов по индексам.
• Power Query — для сложной подготовки и трансформации больших наборов данных.

Практическая методика использования (мини-методология)

1. Решите, нужно ли удалить края массива или фильтровать по условию.
2. Если по условию — используйте FILTER или Power Query.
3. Если по краям — используйте DROP; сначала определите число строк/столбцов и храните это число в отдельной ячейке.
4. Для стабильного порядка всегда сортируйте внутри формулы (SORT) перед DROP.
5. При объединении таблиц используйте VSTACK/HSTACK, затем применяйте SORT и DROP.
6. Тестируйте на небольших подвыборках и проверяйте граничные сценарии (пустые массивы, слишком маленькие массивы).

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

• DROP — «отрезать края»: представьте массив как кусок ткани и ножницы, которые укорачивают края сверху/снизу/слева/справа.
• SORT → DROP — «сначала упорядочить, потом отсечь»; порядок действий важен для предсказуемого результата.
• VSTACK → SORT → DROP — «собрать, упорядочить, отрезать» при объединении нескольких наборов.

Уровни зрелости использования DROP

• Начальный: простое удаление фиксированного числа строк сверху/слева.
• Средний: использование ссылок на ячейки (H1, G1) и отрицательных значений для удаления снизу/справа.
• Продвинутый: вложение SORT, VSTACK, CHOOSECOLS; использование в динамических отчётах и шаблонах.

Факт-бокс: ключевые моменты

• DROP доступна в Microsoft 365, Excel в web и мобильных приложениях.
• DROP — динамическая функция, поддерживает «spilling».
• Ошибка при удалении больше элементов, чем есть в массиве — CALC!.

Контрольные сценарии и тест-кейсы

1. Ввести массив из 20 строк, H1=10: ожидается 10 строк.
2. H1=0: ожидается идентичный исходный массив.
3. H1>числа строк: ожидается CALC!.
4. Отрицательное значение для удаления снизу: проверьте корректность при удалении последних N строк.
5. Комбинация SORT внутри DROP: при изменении порядка источника результат остаётся упорядоченным по SORT.

Шаблон / заготовка формул (cheat sheet)

• Удалить первые n строк: =DROP(range,n)
• Удалить последние n строк: =DROP(range,-n)
• Удалить первый столбец: =DROP(range,,1)
• Удалить последний столбец: =DROP(range,,-1)
• Удалить n снизу и m справа: =DROP(range,-n,-m)
• Сортировать и удалить: =DROP(SORT(range,колонка,порядок),n)

Руководство для ролей (checklists)

Data Analyst:

• Проверить размеры массива.
• Хранить число строк/столбцов в контролируемой ячейке.
• Покрыть тестами: нулевые, граничные и большие значения.

Учитель / преподаватель:

• Использовать DROP для анонимизации: удалить ID и персональные столбцы.
• Стараться не встраивать формулу в таблицу учебного листа.

Администратор отчётов:

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

SOP: пошаговый план для безопасного применения DROP

1. Создайте резервную копию исходной таблицы или используйте копию листа.
2. Введите параметр (число строк/столбцов для удаления) в отдельную ячейку с заметкой.
3. Впишите формулу DROP вне таблицы.
4. Проверьте результат на нескольких примерах и граничных значениях.
5. При необходимости преобразуйте результат в значения и вставьте в целевую таблицу.
6. Документируйте изменения в отчёте.

Диаграмма принятия решения (decision tree)

flowchart TD
  A[Нужно убрать части массива?] --> B{Удалять края массива?}
  B -- Да --> C{Нужно сортировать перед удалением?}
  B -- Нет --> D[Использовать FILTER или Power Query]
  C -- Да --> E[Использовать =DROP'SORT'...',...']
  C -- Нет --> F[Использовать =DROP'...']
  E --> G[Проверить граничные значения]
  F --> G
  D --> G
  G --> H[Документировать и сохранить]

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

• Формула DROP корректно возвращает массив ожидаемого размера для заданных параметров.
• Нет ошибок CALC! при рабочих сценариях; при ошибке — ожидаемое поведение.
• Результат аккуратно «выплыл» в свободную область листа, не перекрывая данные.
• Параметры удаления вынесены в отдельные ячейки и задокументированы.

Совместимость и миграция

• Работает в Microsoft 365 и в Excel Online.
• Не поддерживается в старых локальных версиях Excel без динамических массивов (Excel 2016, 2019 без соответствующих обновлений).
• При необходимости распространить логику на старые версии используйте Power Query или组合 INDEX/SEQUENCE подходы.

Конфиденциальность и обработка персональных данных

Если вы используете DROP для анонимизации (удаление столбцов с ID, именами), это первый шаг, но не гарантирует полную анонимность. Удаление столбца с идентификатором снижает риск, но сочетайте с агрегированием и удалением редких комбинаций для соответствия требованиям конфиденциальности.

Короткое сравнение: DROP vs TAKE

• DROP удаляет указанные края массива.
• TAKE сохраняает указанные края массива.
• Синтаксис аналогичен, логика противоположна. Если вы знаете одну из функций, легко понять другую.

Глоссарий (1 строка определения)

• Массив: упорядоченная совокупность ячеек в прямоугольной области.
• Динамический массив: результат формулы, который автоматически «выплескивается» в соседние ячейки.
• Spilling: механизм распространения результата динамической формулы в смежные ячейки.
• VSTACK/HSTACK: функции для вертикального/горизонтального соединения массивов.
• CHOOSECOLS/CHOOSEROWS: функции для выбора подмножества столбцов или строк.

Краткое резюме

DROP — простой и мощный инструмент для удаления краевых строк и столбцов в динамических массивах Excel. Его сила раскрывается при комбинировании с SORT, VSTACK и CHOOSECOLS. Для надёжности сохраняйте параметры в ячейках, тестируйте граничные случаи и документируйте изменения.

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