Все формулы РФ

Excel и Google Workspace / Базовые формулы Excel

CHOOSECOLS в Excel: выбрать столбцы массива по номерам

CHOOSECOLS возвращает только названные столбцы исходного массива, допускает перестановку, повтор и отрицательные номера с конца.

Опубликовано: Обновлено:

ExcelОфисные расчетыСтраницы с задачами и решениямиЕсть историческая справка

Формула

$$\texttt{=CHOOSECOLS(array,col\_num1,[col\_num2],…)}$$
Схема Выбор столбцов 4 и 2
Выбор столбцов 4 и 2Выбор столбцов 4 и 2Исходная строкаСтолбец 4Столбец 2114122242233432=CHOOSECOLS(A2:D4,4,2)

Результат имеет три строки и два столбца.

Обозначения

$array$
исходный двумерный массив, строки и столбцы
$col_num1$
номер первого возвращаемого столбца, целое число
$col_num2$
дополнительные номера столбцов, целые числа

Условия применения

array должен содержать хотя бы один столбец. Каждый col_num — целое число; допустимы положительные номера от 1 до ширины массива и отрицательные номера от -1 до минус ширины. Ноль и выход за ширину дают #VALUE!. Перед выбором столбцов проверьте, что исходная таблица действительно содержит ожидаемое число полей, особенно если она получена динамической формулой. Доступность проверена для Microsoft 365 и Excel 2024; формулы показаны в английской записи с запятыми.

Ограничения

CHOOSECOLS не выполняет фильтрацию по значениям, сортировку, агрегацию или удаление дублей. Повтор col_num создаёт повторный столбец. Ошибки источника сохраняются в соответствующих ячейках. Номер 0 и номер за пределами ширины вызывают #VALUE!, а занятая область вывода — #SPILL!. Для отбора строк нужна отдельная функция.

Подробное объяснение

CHOOSECOLS возвращает из array только столбцы, перечисленные аргументами col_num. Порядок аргументов становится порядком столбцов результата, поэтому формула одновременно выбирает и переставляет поля. Для A2:D4=[[11,12,13,14],[21,22,23,24],[31,32,33,34]] формула CHOOSECOLS(A2:D4,4,2) возвращает [[14,12],[24,22],[34,32]]. Первый выбранный столбец — исходный D, второй — исходный B; строки сохраняются. Номер -1 означает последний столбец, -2 — предпоследний. Так CHOOSECOLS(A2:D4,-1,1) возвращает [[14,11],[24,21],[34,31]]. Отрицательная нумерация удобна для меняющихся таблиц, когда нужное поле определяют от правого края. Один и тот же номер можно указать несколько раз. CHOOSECOLS(A2:D4,2,2) намеренно создаёт повтор столбца: [[12,12],[22,22],[32,32]]. Это не ошибка и может быть полезно для шаблона, где одно поле показывается в двух местах. Ноль недопустим, как и номер, абсолютное значение которого больше ширины array; Microsoft указывает #VALUE!. CHOOSECOLS не фильтрует строки по условию и не сортирует данные: сначала получите нужные строки отдельной операцией, затем выберите столбцы. Разлив результата требует свободных ячеек.

Как пользоваться формулой

  1. Посчитайте столбцы исходного диапазона и запишите нужные позиции слева направо внутри этого диапазона.
  2. Расположите номера аргументов в порядке будущего вывода; отрицательные номера отсчитывайте справа.
  3. Введите =CHOOSECOLS с выбранными номерами в свободной области, достаточной для всех исходных строк.
  4. Сверьте значения и заголовки результата; при #VALUE! исключите ноль и номера за пределами ширины.

Историческая справка

CHOOSECOLS возникла в поколении динамических массивов Excel, где одна формула может вернуть новый разлившийся диапазон. Раньше перестановку выбранных полей часто строили через INDEX с отдельными номерами столбцов, ручное копирование или вспомогательную строку. CHOOSECOLS выразила эту операцию непосредственно: array остаётся источником, а список col_num описывает контракт вывода. Отрицательная нумерация и повтор аргумента делают описание компактнее для шаблонов с переменным расположением полей. Microsoft относит функцию к новым возможностям Microsoft 365 и Excel 2024; это атрибуция доступности и синтаксиса, а не утверждение о происхождении всех прежних методов выбора столбцов.

Историческая линия формулы

Microsoft Support описывает CHOOSECOLS как функцию, возвращающую заданные столбцы массива, приводит синтаксис, отрицательные номера и повтор столбцов в примерах, а также фиксирует #VALUE! для нуля и выхода за ширину. Сведения о Microsoft 365 и Excel 2024 взяты из официальной страницы функции; объяснение применения к шаблонам является редакционным выводом.

Пример

В A2:D4 записаны строки [11,12,13,14], [21,22,23,24] и [31,32,33,34]. Формула =CHOOSECOLS(A2:D4,4,2) возвращает [14,12], [24,22], [34,32]. Первым идёт исходный четвёртый столбец D, вторым — второй столбец B. Проверьте размер: все три исходные строки сохранены, а ширина сократилась с четырёх полей до двух. В верхней строке результата сумма 14+12=26; в двух следующих 46 и 66. Это контроль правильности выбора, а не действие самой функции: она не суммирует значения. Если источник перенести на B:E и сохранить те же относительные номера, будут выбраны E и C. Поэтому ориентироваться нужно на границы array.

Частая ошибка

Не считайте номер столбца абсолютным номером листа: для B:D первый столбец — B. Не ждите отбора строк по условию: CHOOSECOLS только выбирает поля. Не теряйте повторяющиеся номера при проверке результата, поскольку они намеренно дублируют поле. Не предполагайте, что -1 всегда выбирает конкретный заголовок: это только последнее поле текущего массива.

Практика

Задачи с решением

Последний и первый

Условие. A2:D4 задан как [[11,12,13,14],[21,22,23,24],[31,32,33,34]]. Используйте исходный массив из разобранного примера.

Решение. = CHOOSECOLS(A2:D4,-1,1). Выберите указанные столбцы в этом порядке для каждой из трёх строк.

Ответ. [[14,11],[24,21],[34,31]].

Повтор поля

Условие. Нужно дважды вывести столбец 2. Используйте исходный массив из разобранного примера.

Решение. = CHOOSECOLS(A2:D4,2,2). Выберите указанные столбцы в этом порядке для каждой из трёх строк.

Ответ. [[12,12],[22,22],[32,32]].

Дополнительные источники

  • https://support.microsoft.com/en-us/excel/functions/choosecols-function
  • https://support.microsoft.com/en-us/excel/functions/chooserows-function

Связанные формулы

Excel и Google Workspace

TAKE в Excel: выбрать строки и столбцы массива

$\texttt{=TAKE(A1:C3,2,-2)}$

TAKE возвращает заданное число соседних строк или столбцов с начала или конца массива. Знак rows и columns задаёт сторону выбора, а исходная матрица и порядок её элементов сохраняются.

Excel и Google Workspace

DROP в Excel: исключить строки и столбцы массива

$\texttt{=DROP(A1:C3,1,1)}$

DROP удаляет заданное число соседних строк или столбцов с начала или конца массива и возвращает оставшуюся прямоугольную часть. Исходный диапазон при этом не изменяется.