Функции СБРОСИТЬ (DROP), ВЗЯТЬ (TAKE), CHOOSEROWS и CHOOSECOLS в Excel: как извлекать и удалять части массивов
Работа с динамическими массивами в Excel стала значительно проще благодаря новым функциям, таким как СБРОСИТЬ (DROP), ВЗЯТЬ (TAKE), CHOOSEROWS и CHOOSECOLS. Эти инструменты позволяют гибко управлять данными, извлекая или удаляя нужные строки и столбцы из массивов. В этой статье мы разберем, как использовать эти функции на практике и в каких случаях каждая из них наиболее эффективна.
A1# — ссылка на существующий разлив из A1. Для выбора столбцов 1, 3, 5, 7, 9 нужна ширина не меньше 9; это все нечётные столбцы только при ширине 9 или 10. Пример со строками до 11 требует не меньше 11 строк. Номера выбора могут повторяться, отрицательные отсчитываются от конца; ноль и выход за размер вызывают #ЗНАЧ! / #VALUE!. ВЗЯТЬ и СБРОСИТЬ не создают пустой массив: нулевой размер возвращает #ВЫЧИСЛ! / #CALC!.
Используемые здесь функции преобразования массивов доступны в Microsoft 365, Excel 2024 для Windows/Mac и Excel для Интернета; в Excel 2016/2019/2021 полного набора нет. Формулу разлива размещайте вне таблицы Excel, оставляя место для результата. Русские и английские примеры ниже используют стандартные разделители аргументов ; и , соответственно.
Если вы разбираете формулы в Excel с нуля или хотите ускорить расчеты, здесь показано, как использовать функции в экселе на понятных и рабочих примерах.
Удаление строк и столбцов с помощью СБРОСИТЬ (DROP)
Функция СБРОСИТЬ (DROP) предназначена для удаления указанного количества строк или столбцов с начала или конца массива. Её синтаксис: .СБРОСИТЬ(массив;строки;[столбцы])
- Чтобы удалить верхнюю строку массива в
A1#, используйте формулу: Русская версия:=СБРОСИТЬ(A1#;1)English version:=DROP(A1#,1). - Для удаления двух верхних строк: Русская версия:
=СБРОСИТЬ(A1#;2)English version:=DROP(A1#,2). - Если указать отрицательное число для строк или столбцов, Excel удалит элементы с конца массива. Например, Русская версия:
=СБРОСИТЬ(A1#;-1)English version:=DROP(A1#,-1)удалит последнюю строку. - Чтобы удалить последний столбец: Русская версия:
=СБРОСИТЬ(A1#;;-1)English version:=DROP(A1#,,-1). - Для одновременного удаления последней строки и последнего столбца: Русская версия:
=СБРОСИТЬ(A1#;-1;-1)English version:=DROP(A1#,-1,-1).
Обратите внимание: функция СБРОСИТЬ (DROP) не позволяет одновременно удалить строки с начала и конца массива в одной формуле. Для этого потребуется вложить две функции: Русская версия: =СБРОСИТЬ(СБРОСИТЬ(A1#;1);-1)English version: =DROP(DROP(A1#,1),-1).
Извлечение данных с помощью ВЗЯТЬ (TAKE)
Функция ВЗЯТЬ (TAKE) работает противоположно СБРОСИТЬ (DROP): она оставляет только указанное количество строк или столбцов с начала или конца массива. Синтаксис: .ВЗЯТЬ(массив;строки;[столбцы])
- Чтобы получить первую строку массива: Русская версия:
=ВЗЯТЬ(A1#;1)English version:=TAKE(A1#,1). - Для извлечения последнего столбца используйте отрицательное число: Русская версия:
=ВЗЯТЬ(A1#;;-1)English version:=TAKE(A1#,,-1).
Гибкий выбор с CHOOSEROWS и CHOOSECOLS
В отличие от СБРОСИТЬ (DROP) и ВЗЯТЬ (TAKE), функции CHOOSEROWS и CHOOSECOLS позволяют выбирать любые строки или столбцы массива, а не только с его краёв.
- Чтобы получить первую и последнюю строки массива: Русская версия:
=ВЫБОРСТРОК(A1#;1;-1)English version:=CHOOSEROWS(A1#,1,-1). - Для выбора столбцов 1, 3, 5, 7 и 9 можно комбинировать
CHOOSECOLSсПОСЛЕДОВ / SEQUENCE: Русская версия:=ВЫБОРСТОЛБЦ(A1#; ПОСЛЕДОВ(5;1;1;2))English version:=CHOOSECOLS(A1#, SEQUENCE(5,1,1,2)).
Когда использовать СБРОСИТЬ (DROP)/ВЗЯТЬ (TAKE), а когда CHOOSEROWS/CHOOSECOLS?
Выбор между этими функциями зависит от задачи:
- СБРОСИТЬ (DROP) и ВЗЯТЬ (TAKE) идеально подходят для удаления или извлечения строк/столбцов с краёв массива. Их ключевое преимущество — возможность одновременно работать с итоговыми строками и столбцами.
- CHOOSEROWS и CHOOSECOLS более гибкие: они позволяют выбирать любые строки или столбцы, но работают только с одним измерением за раз (либо строки, либо столбцы).
Например, если нужно выбрать строки 1, 2, 5, 6, 9, 10 и 11, используйте Русская версия: =ВЫБОРСТРОК(A1#;1;2;5;6;9;10;11)English version: =CHOOSEROWS(A1#,1,2,5,6,9,10,11).
Практические советы
- Комбинируйте функции для сложных задач. Например, сначала удалите ненужные строки с помощью
СБРОСИТЬ (DROP), а затем извлеките нужные столбцы с помощьюCHOOSECOLS. - Используйте отрицательные числа в
ВЗЯТЬ (TAKE)иСБРОСИТЬ (DROP)для работы с концом массива. - Для создания последовательностей номеров строк или столбцов применяйте функцию
ПОСЛЕДОВ / SEQUENCE.
Освоив функции СБРОСИТЬ (DROP), ВЗЯТЬ (TAKE), CHOOSEROWS и CHOOSECOLS, вы сможете эффективно управлять динамическими массивами в Excel, делая ваши формулы более гибкими и мощными.