Функция Excel TAKE для извлечения строк или столбцов из массива
Познакомьтесь с новой функцией Excel TAKE, которая может мгновенно получить указанное количество строк или столбцов из диапазона или массива.
При работе с большими массивами данных иногда может потребоваться извлечь меньшую часть для более тщательного изучения. С новой функцией динамического массива, представленной в Excel 365, это будет для вас прогулкой по парку. Просто укажите, сколько строк и столбцов вы хотите взять, и нажмите клавишу Enter 🙂
ВЗЯТЬ функция в Excel
Функция Excel TAKE извлекает указанное количество смежных строк и/или столбцов из массива или диапазона.
Синтаксис следующий:
ВЗЯТЬ(массив, строки, [columns])
Где:
Массив (обязательно) — исходный массив или диапазон.
Rows (необязательно) — количество возвращаемых строк. Положительное значение берет строки с начала массива, а отрицательное значение — с конца массива. Если он опущен, столбцы должны быть установлены.
Столбцы (необязательно) — количество возвращаемых столбцов. Положительное целое число берет столбцы с начала массива, а отрицательное целое число — с конца массива. Если он опущен, строки должны быть определены.
Вот как выглядит функция TAKE:
Советы:
- Чтобы вернуть несмежные строки из диапазона, используйте функцию CHOOSEROWS.
- Чтобы получить несмежные столбцы, используйте функцию CHOOSECOLS.
- Чтобы получить часть массива, удалив заданное количество строк или столбцов, используйте функцию DROP.
Наличие функции TAKE
Функция ВЗЯТЬ поддерживается только в Excel для Microsoft 365 (Windows и Mac) и Excel для Интернета.
В более ранних версиях Excel вы можете использовать формулу OFFSET в качестве альтернативное решение.
Как использовать функцию ВЗЯТЬ в Excel
Чтобы привести ожидания в соответствие с реальностью при использовании функции ВЗЯТЬ в ваших рабочих листах, обратите внимание на следующие вещи:
- Аргумент массива может быть диапазоном ячеек или массивом значений, возвращаемых другой формулой.
- Аргументы строк и столбцов могут быть положительными или отрицательными целыми числами. Положительные числа берут подмножество данных с начала массива; отрицательные числа – с конца.
- Аргументы строк и столбцов необязательны, но хотя бы один из них должен быть задан в формуле. Пропущенный по умолчанию равен общему количеству строк или столбцов в массиве.
- Если значение строк или столбцов больше, чем строк или столбцов в исходном массиве, возвращаются все строки/столбцы.
- TAKE — это функция динамического массива. Вы вводите формулу только в одну ячейку, и она автоматически распространяется на столько соседних ячеек, сколько необходимо.
Примеры формул Excel ПРИНИМАТЬ
Теперь, когда у вас есть общее представление о том, как работает функция TAKE, давайте рассмотрим несколько практических примеров, иллюстрирующих ее реальную ценность.
Извлечение строк из диапазона или массива
Чтобы вернуть заданное количество непрерывных строк от начала двумерного массива или диапазона, укажите положительное число для аргумента строк.
Например, чтобы взять первые 4 строки из диапазона A3:C14, используйте следующую формулу:
=ВЗЯТЬ(A3:C14, 4)
Формула помещается в ячейку E3 и распределяется по четырем строкам и столько столбцов, сколько имеется в исходном диапазоне.
Взять столбцы из массива или диапазона
Чтобы получить определенное количество смежных столбцов с начала двумерного массива или диапазона, укажите положительное число в качестве аргумента столбцов.
Например, чтобы извлечь первые 2 столбца из диапазона A3:C14, используйте следующую формулу:
=ВЗЯТЬ(A3:C14, ,2)
Формула помещается в ячейку E3 и разбивается на два столбца и столько строк, сколько имеется в указанном диапазоне.
Извлечь определенное количество строк и столбцов
Чтобы получить заданное количество строк и столбцов из начала массива, вы предоставляете положительные числа как для аргументов строк, так и для столбцов.
Например, чтобы взять первые 4 строки и 2 столбца из нашего набора данных, используйте следующую формулу:
=ВЗЯТЬ(A3:C14, 4, 2)
Введенная в E3 формула заполняет четыре строки (как указано во втором аргументе) и два столбца (как указано в третьем аргументе).
Получить последние N строк
Чтобы извлечь определенное количество строк из конца массива, укажите отрицательное число в качестве аргумента rows. Например:
Чтобы взять последнюю строку, используйте -1:
=ВЗЯТЬ(A3:C14, -1)
Чтобы получить последние 3 строки, введите -3:
=ВЗЯТЬ(A3:C14, -3)
На скриншоте ниже вы можете наблюдать результаты.
Вернуть последние N столбцов
Чтобы извлечь некоторые столбцы из конца массива или диапазона, используйте отрицательное число в качестве аргумента столбцов. Например:
Чтобы получить последний столбец, установите для третьего аргумента значение -1:
=ВЗЯТЬ(A3:C14, , -1)
Чтобы получить последние 2 столбца, установите для третьего аргумента значение -2:
=ВЗЯТЬ(A3:C14, , -2)
И вот результаты:
Кончик. Чтобы взять строки и столбцы с конца массива, укажите отрицательные числа для аргументов строк и столбцов.
Как взять строки/столбцы из нескольких диапазонов
В ситуации, когда вы хотите извлечь некоторые столбцы или строки из нескольких несмежных диапазонов, для выполнения задачи требуется два шага:
- Объедините несколько диапазонов в один по вертикали или горизонтали, используя функцию VSTACK или HSTACK.
- Возвращает желаемое количество столбцов или строк из объединенного массива.
В зависимости от структуры рабочего листа примените одно из следующих решений.
Складывать диапазоны по вертикали и брать строки или столбцы
Допустим, у вас есть 3 отдельных диапазона, как показано на изображении ниже. Чтобы добавить каждый последующий диапазон к нижней части предыдущего, используйте формулу:
=ВСТЕК(A4:C6, A10:C14, A18:C21)
Вложите его в аргумент массива TAKE, укажите, сколько строк нужно вернуть, и вы получите результат, который ищете:
=ВЗЯТЬ(ВСТЭК(A4:C6, A10:C14, A18:C21), 4)
Чтобы вернуть столбцы, введите соответствующее число в 3-м аргументе:
=ВЗЯТЬ(ВСТЭК(A4:C6, A10:C14, A18:C21), ,2)
Вывод будет выглядеть примерно так:
Складывать диапазоны по горизонтали и брать строки или столбцы
В случае, если данные в исходных диапазонах расположены горизонтально в строках, используйте функцию HSTACK, чтобы объединить их в один массив. Например:
=HSTACK(B3:D5, G3:H5, K3:L5),
Затем вы помещаете приведенную выше формулу внутрь функции TAKE и устанавливаете аргумент строк или столбцов или и то, и другое в соответствии с вашими потребностями.
Например, чтобы получить первые 2 строки из массива с накоплением, используйте формулу:
=ВЗЯТЬ(HSTACK(B3:D5, G3:H5, K3:L5), 2)
И эта формула выведет последние 5 столбцов:
=ВЗЯТЬ(HSTACK(B3:D5, G3:H5, K3:L5), ,5)
Альтернатива функции ВЗЯТЬ для Excel 2010–365
В Excel 2019 и более ранних версиях, где функция ВЗЯТЬ не поддерживается, в качестве альтернативы можно использовать СМЕЩЕНИЕ. Хотя формула OFFSET не так интуитивно понятна и проста, она обеспечивает рабочее решение. И вот как вы это настроили:
- В качестве первого аргумента укажите исходный диапазон значений.
- 2-й и 3-й аргументы или оба равны нулю или опущены, если вы извлекаете подмножество из начала массива. При желании вы можете указать, как строки и столбцы могут смещаться от верхней левой ячейки массива.
- В четвертом аргументе укажите количество возвращаемых строк.
- В 5-м аргументе укажите количество возвращаемых столбцов.
Подводя итог, общая формула принимает следующий вид:
СМЕЩ(массив, , , строки, столбцы)
Например, чтобы извлечь 6 строк и 2 столбца из начала диапазона A3:C14, формула выглядит следующим образом:
=СМЕЩЕНИЕ(A3:C14, , , 6, 2)
Во всех версиях, кроме Excel 365 и 2021, которые изначально обрабатывают массивы, это работает только как традиционная формула массива СПП. В него можно войти двумя способами:
- Выберите диапазон ячеек того же размера, что и ожидаемый результат (в нашем случае 6 строк и 2 столбца), и нажмите F2, чтобы войти в режим редактирования. Введите формулу и нажмите Ctrl + Shift + Enter, чтобы ввести ее сразу во все выделенные ячейки.
- Введите формулу в любую пустую ячейку (E3 в этом примере) и нажмите Ctrl + Shift + Enter, чтобы завершить ее. После этого перетащите формулу вниз и вправо по необходимому количеству строк и столбцов.
Результат будет выглядеть примерно так:
Примечание. Имейте в виду, что OFFSET — это изменчивая функция, и она может замедлить работу вашего рабочего листа, если используется во многих ячейках.
Функция Excel TAKE не работает
Если формула ВЗЯТЬ не работает в вашем Excel или приводит к ошибке, это, скорее всего, одна из следующих причин.
TAKE не поддерживается в вашей версии Excel.
TAKE — это новая функция, и она имеет ограниченные возможности. доступность. Если ваша версия отличается от Excel 365, попробуйте альтернативная формула OFFSET.
Пустой массив
Если для аргумента rows или columns установлено значение 0, #CALC! возвращается ошибка, указывающая на пустой массив.
Недостаточное количество пустых ячеек для заполнения результатов
Если для формулы не хватает пустых ячеек для переноса результатов, возникает ошибка #SPILL. Чтобы это исправить, просто очистите соседние ячейки снизу и/или справа. Дополнительные сведения см. в разделе Как решить проблему #SPILL! ошибка в экселе.
Вот как можно использовать функцию ВЗЯТЬ в Excel для извлечения строк или столбцов из диапазона ячеек. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Формула ВЗЯТЬ в Excel – примеры (файл .xlsx)
