Функция Excel CHOOSEROWS для извлечения определенных строк из массива
В этом руководстве мы подробно рассмотрим функцию Excel 365 под названием CHOOSEROWS и ее практическое применение.
Предположим, у вас есть рабочий лист Excel с сотнями строк, из которых вы хотите извлечь некоторые конкретные, скажем, все нечетные или четные строки, первые 5 или последние 10 строк и т. д. Вы уже чувствуете раздражение при мысли о копировании и вставлять данные вручную или писать код VBA для автоматизации задачи? Не волнуйся! Все гораздо проще, чем кажется. Просто используйте новую функцию CHOOSEROWS динамического массива.
Функция ВЫБОР рядов Excel
Функция CHOOSEROWS в Excel используется для извлечения указанных строк из массива или диапазона.
Синтаксис следующий:
CHOOSEROWS(массив, row_num1, [row_num2]…)
Где:
Массив (обязательно) — исходный массив.
Row_num1 (обязательно) — целое число, представляющее числовой индекс первой возвращаемой строки.
Row_num2, … (необязательный) — порядковые номера дополнительных строк для возврата.
Вот как работает функция CHOOSEROWS в Excel 365:
Наличие функции CHOOSEROWS
Функция CHOOSEROWS доступна только в Excel для Microsoft 365 (Windows и Mac) и Excel для Интернета.
Кончик. Чтобы получить определенные столбцы из диапазона или массива, используйте функцию CHOOSECOLS.
Как использовать функцию ВЫБОР в Excel
Чтобы получить определенные строки из заданного массива, создайте формулу CHOOSEROWS следующим образом:
- Для массива вы можете указать диапазон ячеек или массив значений, управляемый другой формулой.
- Для row_num укажите положительное или отрицательное целое число, указывающее, какую строку следует вернуть. Положительное число извлекает соответствующую строку из начала массива, отрицательное число — из конца массива. Несколько номеров строк могут быть предоставлены индивидуально в отдельных аргументах или в одном аргументе в виде константы массива.
Как функция динамического массива Excel, CHOOSEROWS изначально обрабатывает массивы. Вы вводите формулу в верхнюю левую ячейку целевого диапазона, и она автоматически распределяется по необходимому количеству столбцов и строк. Результатом является единый динамический массив, также известный как диапазон разлива.
Например, чтобы получить строки 2, 4, 6, 8 и 10 из диапазона A4:D13, используйте следующую формулу:
=ВЫБОР(A4:D13, 2, 4, 6, 8, 10)
Кроме того, вы можете использовать константу массива, такую как {2,4,6,8,10} или {2;4;6;8;10}, чтобы указать нужные строки:
=ВЫБОР(A4:D13, {2,4,6,8,10})
Или
=ВЫБОР(A4:D13, {2;4;6;8;10})
Другой способ указать номера строк — ввести их в отдельные ячейки, а затем использовать либо ссылки на отдельные ячейки для нескольких аргументов row_num, либо ссылку на диапазон для одного аргумента row_num.
Например:
=ВЫБОР(A4:D13, F4, G4, H4)
=ВЫБРАТЬСТРОКИ(A4:D13, F4:H4)
Преимущество этого подхода заключается в том, что он позволяет извлекать любые другие строки, просто изменяя числа в предопределенных ячейках без редактирования самой формулы.
Ниже мы обсудим еще несколько примеров формул CHOOSEROWS для обработки более конкретных случаев использования.
Возврат строк с конца массива
Чтобы быстро получить последние N строк из диапазона, укажите отрицательные числа для аргументов row_num. Это заставит функцию считать строки с конца массива.
Например, чтобы получить последние 3 строки из диапазона A4:D13, используйте следующую формулу:
=ВЫБРАТЬСТРОКИ(A4:D13, -3, -2, -1)
Результатом будет массив из 3 строк, в котором строки отображаются в том же порядке, что и в указанном диапазоне.
Чтобы вернуть последние 3 строки в обратном порядке, снизу вверх, измените порядок аргументов row_num следующим образом:
=ВЫБРАТЬСТРОКИ(A4:D13, -1, -2, -3)
Извлечь каждую вторую строку из массива в Excel
Чтобы получить каждую вторую строку из заданного диапазона, используйте CHOOSEROWS в сочетании с несколькими другими функциями. Формула будет немного отличаться в зависимости от того, извлекаете ли вы нечетные или четные строки.
Чтобы вернуть нечетные строки, такие как 1, 3, 5, …, формула принимает следующий вид:
=ВЫБРАТЬСТРОКИ(A4:D13, ПОСЛЕДОВАТЕЛЬНОСТЬ(ОКРУГЛВВЕРХ(СТРОКИ(A4:D13)/2, 0), 1, 1, 2))
Чтобы вернуть четные строки, такие как 2, 4, 6, …, формула выглядит следующим образом:
= ВЫБОР РЯДОВ (A4: D13, ПОСЛЕДОВАТЕЛЬНОСТЬ (ОКРУГЛЕНИЕ ВНИЗ (СТРОКИ (A4: D13)/2, 0), 1, 2, 2))
Как работает эта формула:
По сути, функция CHOOSEROWS возвращает строки на основе массива последовательных нечетных или четных чисел, сгенерированных функцией SEQUENCE. Подробная разбивка формулы приведена ниже.
Во-первых, вы определяете, сколько строк нужно вернуть. Для этого вы используете функцию СТРОКИ, чтобы получить общее количество строк в указанном массиве, которое вы делите на 2, а затем округляете частное вверх или вниз до целого числа с помощью ОКРУГЛВВЕРХ или ОКРУГЛВНИЗ. Поскольку это число позже будет передано в аргумент строк SEQUENCE, округление необходимо для получения целого числа в случае, если исходный диапазон содержит нечетное количество строк.
Поскольку наш исходный диапазон имеет четное количество строк (10), которое точно делится на 2, и ОКРУГЛВВЕРХ(10/2, 0), и ОКРУГЛВНИЗ(10/2, 0) возвращают один и тот же результат, равный 5.
Возвращенный номер передается функции ПОСЛЕДОВАТЕЛЬНОСТЬ.
Для нечетных рядов:
ПОСЛЕДОВАТЕЛЬНОСТЬ(5, 1, 1, 2)
Для четных рядов:
ПОСЛЕДОВАТЕЛЬНОСТЬ(5, 1, 2, 2)
Приведенная выше формула ПОСЛЕДОВАТЕЛЬНОСТЬ создает массив чисел, состоящий из 5 строк и 1 столбца, начиная с 1 для нечетных строк (с 2 для четных строк) и увеличивая на 2.
Для нечетных строк мы получаем этот массив:
{1;3;5;7;9}
Для четных строк мы получаем это:
{2;4;6;8;10}
Сгенерированный массив переходит к аргументу row_num1 CHOOSEROWS, и вы получаете желаемый результат:
=ВЫБОР(A4:D13, {1;3;5;7;9})
Обратный порядок строк в массиве
Чтобы отразить массив по вертикали сверху вниз, вы также можете использовать функции CHOOSEROWS и SEQUENCE вместе. Например:
=ВЫБРАТЬСТРОКИ(A4:D13, ПОСЛЕДОВАТЕЛЬНОСТЬ(СТРОКИ(A4:D13))*-1)
В этой формуле мы устанавливаем только первый аргумент (строки) SEQUENCE, который равен общему количеству строк в исходном массиве ROWS(A4:D13). Пропущенные аргументы (столбцы, начало, шаг) по умолчанию равны 1. В результате ПОСЛЕДОВАТЕЛЬНОСТЬ создает массив последовательных чисел, таких как 1, 2, 3, …, n, где n — последняя строка в исходном массиве. Чтобы CHOOSEROWS считал строки в направлении вниз-вверх, сгенерированная последовательность умножается на -1, поэтому аргумент row_num получает массив отрицательных чисел, таких как {-1;-2;-3;-4;-5;- 6;-7;-8;-9;-10}.
В результате порядок элементов в каждом столбце меняется сверху вниз:
Извлечение строк из нескольких массивов
Чтобы получить определенные строки из двух или более несмежных диапазонов, вы сначала объединяете их с помощью функции VSTACK, а затем передаете объединенный диапазон в CHOOSEROWS.
Например, чтобы извлечь первые две строки из диапазона A4:D8 и последние две строки из диапазона A12:D16, используйте следующую формулу:
=ВЫБРАТЬСТРОК(ВСТЕК(A4:D8, A12:D16), 1, 2, -2, -1)
Получить строки на основе строки, содержащей номера строк
В этом примере показано, как вернуть определенные строки, извлекая числа из буквенно-цифровой строки.
Предположим, у вас есть числа, разделенные запятыми, в ячейке G3, в которой перечислены интересующие вас строки. Чтобы извлечь номера строк из строки, используйте функцию TEXTSPLIT, которая может разделить текстовую строку по заданному разделителю (в нашем случае — запятая):
=ТЕКСТРАЗДЕЛИТЬ(G3, “,”)
Результатом является массив текстовых значений, таких как {“3″,”5″,”7″,”10”}. Чтобы преобразовать его в массив чисел, выполните любую математическую операцию, которая не изменяет значения, например, +0 или *1.
=ТЕКСПЛИТ(G3, “,”) *1
Это создает числовую константу массива {3,5,7,10}, необходимую для функции CHOOSEROWS, поэтому вы вставляете формулу TEXTSPLIT во 2-й аргумент:
=ВЫБОР(A4:D13, РАЗДЕЛЕНИЕ ТЕКСТА(G3, “,”) *1)
В результате все указанные строки возвращаются в виде единого массива:
Функция CHOOSEROWS не работает
Если формула CHOOSEROWS приводит к ошибке, скорее всего, это одна из следующих причин.
#ЦЕНИТЬ! ошибка
Происходит, если абсолютное значение любого аргумента row_num равно нулю или превышает общее количество строк в массиве.
#НАЗВАНИЕ? ошибка
Происходит, если имя функции написано с ошибкой или функция не поддерживается в вашем Excel. В настоящее время CHOOSEROWS доступен только в Excel 365 и Excel в Интернете.
#ИГРА! ошибка
Происходит, когда недостаточно пустых ячеек для заполнения результатами. Чтобы это исправить, просто очистите мешающие ячейки. Дополнительные сведения см. в разделе Excel #ПРОЛИВ! ошибка.
Вот как можно использовать функцию CHOOSEROWS в Excel для возврата определенных строк из диапазона или массива. Спасибо за чтение, и я надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Формула Excel CHOOSEROWS – примеры (файл .xlsx)
