Функция 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:

Функция ВЫБОР рядов Excel

Наличие функции CHOOSEROWS

Функция CHOOSEROWS доступна только в Excel для Microsoft 365 (Windows и Mac) и Excel для Интернета.

Кончик. Чтобы получить определенные столбцы из диапазона или массива, используйте функцию CHOOSECOLS.

Как использовать функцию ВЫБОР в Excel

Чтобы получить определенные строки из заданного массива, создайте формулу CHOOSEROWS следующим образом:

  1. Для массива вы можете указать диапазон ячеек или массив значений, управляемый другой формулой.
  2. Для 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})
Использование формулы CHOOSEROWS в Excel для извлечения определенных строк.

Другой способ указать номера строк — ввести их в отдельные ячейки, а затем использовать либо ссылки на отдельные ячейки для нескольких аргументов 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)
Извлеките последние 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)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *