CHOOSECOLS и CHOOSEROWS в Excel: извлечение столбцов и строк быстро и гибко

Быстрые ссылки

• Синтаксис CHOOSECOLS и CHOOSEROWS
• Пример 1: извлечение первого и последнего столбца или строки из таблицы
• Пример 2: извлечение столбцов из нескольких диапазонов (VSTACK)
• Пример 3: CHOOSECOLS вместе с проверкой данных и условным форматированием

Что делают CHOOSECOLS и CHOOSEROWS

CHOOSECOLS и CHOOSEROWS — это «близнецы»: синтаксис и поведение очень похожи, но одна функция оперирует столбцами, другая — строками. Обе возвращают динамический массив: когда вы вводите формулу, результат автоматически «вытекает» за пределы ячейки формулы.

Простая формула позволяет выбрать любые позиции внутри массива, задав индексы (положительные и отрицательные). Положительные индексы считаются слева направо (для столбцов) или сверху вниз (для строк). Отрицательный индекс начинает отсчёт с конца массива — например, -1 означает последний столбец/строку.

На момент написания (май 2025 г.) функции доступны в Excel для Microsoft 365 на Windows, macOS и в Excel для веба.

Синтаксис CHOOSECOLS и CHOOSEROWS

=CHOOSECOLS(a,b,c,…)

=CHOOSEROWS(a,b,c,…)

• a (обязательный) — исходный массив или диапазон (например, имя таблицы).
• b (обязательный) — номер столбца (для CHOOSECOLS) или строки (для CHOOSEROWS) для извлечения.
• c, … (необязательные) — дополнительные индексы столбцов/строк через запятую.

Важно: если индекс указывает на внутреннюю позицию массива, и вы вставите новые столбцы/строки в середину массива, нумерация сместится, и формула начнёт ссылаться на другие данные. Поэтому при необходимости стабильных ссылок используйте структурированные имена столбцов или вспомогательные индексы.

Отличие от TAKE: TAKE извлекает первые/последние X столбцов или строк или единичную позицию. CHOOSECOLS/CHOOSEROWS дают полный контроль над порядком и набором извлекаемых позиций.

Пример 1: извлечение первого и последнего столбца или строки из таблицы

Сценарий: вы администратор любительской лиги и хотите отчёт с общим количеством очков для каждой команды. Таблица T_Games содержит заголовок, данные и строку итогов.

Чтобы получить только столбцы Team и Total (первый и последний столбцы), в пустой ячейке напишите:

=CHOOSECOLS(T_Games[#All],1,-1)

Где:

• T_Games[#All] — структурированная ссылка, включающая заголовки и строку итогов;
• 1 — первый столбец (Team);
• -1 — последний столбец (Total).

CHOOSECOLS по умолчанию считает столбцы слева направо; CHOOSEROWS — сверху вниз. Чтобы получить обратный порядок, используйте отрицательные индексы.

Если вы добавите строки или столбцы в исходную таблицу, результирующий массив обновится автоматически, включая строку итогов:

Если исходные данные не оформлены как таблица (обычные диапазоны), формула при расширении диапазона вправо/вниз не обновится автоматически — придётся править ссылки вручную. Поэтому рекомендуется использовать таблицы Excel для динамики и удобства.

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

=CHOOSEROWS(T_Games[#All],1,-1)

Пример 2: извлечение столбцов из нескольких диапазонов (VSTACK + CHOOSECOLS)

Задача: у вас есть три таблицы League_1, League_2 и League_3. Нужно создать объединённый список команд с итоговыми очками по каждой лиге.

Подход: вертикально объединить таблицы через VSTACK, затем вынуть нужные столбцы через CHOOSECOLS. В ячейке I2 напишите:

=CHOOSECOLS(VSTACK(League_1,League_2,League_3),1,-1)

Пояснения:

• VSTACK объединяет массивы вертикально;
• отсутствие [#All] указывает не включать заголовки/итоги из исходных таблиц;
• 1 — первый столбец (Team);
• -1 — последний столбец (Total).

При желании можно отсортировать получившийся динамический массив по убыванию очков:

=SORTBY(I2#,J2:J16,-1)

Где I2# — оператор «спилл» для динамического массива, возвращённого CHOOSECOLS.

Пример 3: CHOOSECOLS вместе с проверкой данных и условным форматированием

Задача: дать пользователю возможность выбрать номер игры в выпадающем списке, а затем показать очки команд и общий итог для выбранной игры.

Процесс:

1. Введите номер игры в ячейку B9.
2. В ячейке A11 напишите формулу:

=CHOOSECOLS(T_Scores[[#Data],[#Totals]],1,B9+1)

Пояснения:

• T_Scores[[#Data],[#Totals]] — структурированная ссылка, включающая только данные и строку итогов, без заголовка;
• 1 — первый столбец (Team/Итоги);
• B9+1 — индекс столбца для выбранной игры; +1 нужен, потому что нумерация игр начинается со второго столбца таблицы.

Если пользователь введёт неверный номер (например, 0 или слишком большое число), Excel вернёт #VALUE. Это стандартное поведение: индекс должен существовать в диапазоне.

Рекомендуется защитить ввод через проверку данных. Пример для создания списка номеров игр:

1. Выделите диапазон заголовков игр (без колонки Team) и немного справа, чтобы оставить место для роста. Дайте этому диапазону имя Games в поле имени.

2. Выделите ячейку B9, вкладка Данные → Проверка данных (Data Validation в англ. версии). В диалоге выберите Тип — Список, отметьте Игнорировать пустые, в поле Источник введите =Games и нажмите OK.

После этого выпадающий список в B9 будет содержать корректные номера игр, и пользователю станет труднее ввести недопустимое значение.

Для визуализации результатов можно применить условное форматирование: выделите B11:B15, откройте вкладку Главная → Условное форматирование → Диаграммы данных (Data Bars) и выберите заливку.

Важные замечания и частые ошибки

• Динамические массивы «вытекают»: перед вводом формулы убедитесь, что справа и вниз достаточно пустого пространства — иначе появится ошибка #SPILL!.
• Форматированные таблицы и вытекающие массивы не всегда совместимы: если вы хотите применить форматирование к результату, делайте это вручную или используйте условное форматирование.
• CHOOSECOLS/CHOOSEROWS возвращают #VALUE!, если индекс равен нулю или превышает длину массива.
• Вставка столбцов/строк в середину массива влияет на позиционные индексы; используйте структурированные ссылки или дополнительные вычисляемые индексы для стабильности.

Практические рекомендации и эвристики

• Используйте структурированные таблицы Excel (Insert → Table) для надёжной работы формул при добавлении данных.
• Если вы планируете менять порядок столбцов исходной таблицы, лучше ссылаться на имена столбцов через XLOOKUP/INDEX+MATCH, а не на жёсткие индексы.
• Для объединения наборов данных используйте VSTACK/HSTACK, затем CHOOSECOLS/CHOOSEROWS для отбора столбцов/строк.
• Для сортировки результат комбинируйте с SORT или SORTBY. Для фильтрации — с FILTER.

Когда CHOOSECOLS и CHOOSEROWS не подходят (контрпример)

• Если нужно извлечь столбцы по имени, но структура таблицы часто меняется местами и вы не хотите полагаться на позиции — лучше INDEX с MATCH по заголовку.
• Для выборки на основе критериев (например, все строки, где значение > 100) используйте FILTER.
• Если нужно выбрать диапазон «первые N» или «последние N», проще применить TAKE.

Альтернативные подходы

• INDEX + SEQUENCE: для программного формирования списка индексов (например, выбор каждых N‑го столбца).
• FILTER: когда нужно выбирать строки/столбцы по критериям, а не по позиции.
• POWER QUERY: для ETL‑задач и подготовки наборов данных перед выводом в расчётные листы.

Чек‑лист ролей: кто и как использует CHOOSECOLS/CHOOSEROWS

• Администратор данных:
  • Создаёт таблицы Excel и даёт понятные имена диапазонам;
  • Настраивает проверку данных для пользовательских вводов.
• Аналитик/Отчётник:
  • Использует CHOOSECOLS с VSTACK и SORTBY для агрегированных отчётов;
  • Добавляет условное форматирование для визуализации.
• Разработчик отчётов/BI:
  • Преобразует исходные таблицы в стабильные структуры (таблицы/Power Query);
  • Пишет инструкции по поддержке и тесты регресса формул.

Шпаргалка (Cheat sheet): быстрые примеры

• Извлечь 1‑й и последний столбцы:

=CHOOSECOLS(Table[#All],1,-1)

• Извлечь столбцы 1, 4 и 2 в указанном порядке:

=CHOOSECOLS(Table,1,4,2)

• Извлечь строки 1 и последнюю:

=CHOOSEROWS(Table,1,-1)

• Комбинация с VSTACK:

=CHOOSECOLS(VSTACK(TableA,TableB),1,-1)

• Использование индекса из ячейки:

=CHOOSECOLS(T_Scores[[#Data],[#Totals]],1,B9+1)

Методология быстрой проверки формул (мини‑метод)

1. Убедитесь, что исходные диапазоны оформлены как таблицы и имеют понятные имена.
2. Проверьте, что у формулы достаточно пространства для «вылива» результата (спилл).
3. Подставьте простые тестовые индексы (1,-1) и убедитесь, что результат корректен.
4. Если используете индексы из ячеек — добавьте проверку данных, чтобы избежать #VALUE.
5. Добавьте условное форматирование для наглядности итогов.

Критерии приёмки

• Формула возвращает ожидаемые столбцы/строки при стандартных данных.
• Результат корректно обновляется при добавлении записей в таблицу.
• При попытке ввести недопустимый индекс пользователь видит контролируемое поведение (проверка данных) или понятную ошибку.
• Вокруг формулы достаточно пустых ячеек, чтобы избежать #SPILL!.

Типичные тестовые сценарии / Acceptance тесты

1. Базовый тест: CHOOSECOLS(Table[#All],1,-1) возвращает первый и последний столбцы.
2. Расширение таблицы: добавить строку данных — результат должен обновиться автоматически.
3. Неверный индекс: ввести 0 или число > числа столбцов — функция возвращает #VALUE!.
4. Индекс из ячейки + проверка данных: выбор из выпадающего списка всегда даёт корректный результат.
5. Комбинация VSTACK: объединить три таблицы и проверить правильность итогов.

Руководство по устранению ошибок (Incident runbook)

Симптом: ошибка #SPILL! при вводе CHOOSECOLS/CHOOSEROWS.

Шаги устранения:

1. Выберите ячейку с формулой и посмотрите причину всплывающей подсказки для #SPILL!. Обычно справа или под формулой есть заполненные ячейки.
2. Очистите или переместите мешающие ячейки; при необходимости вставьте новый лист или область.
3. Если надёжного места для «вылива» нет, примените формулу в отдельном листе и затем ссылку на неё используйте на основном листе.

Симптом: возвращается #VALUE! после ввода индекс‑значения из ячейки.

Шаги устранения:

1. Проверьте значение индекса: оно должно быть целым числом, не равным 0 и не превышать число столбцов/строк в массиве.
2. Добавьте проверку данных для предотвращения некорректного ввода.
3. При необходимости обрабатывайте ошибку через IFERROR или конструкцию проверки типа ISNUMBER и границ диапазона.

Совместимость и советы по переносу

• Функции доступны только в Microsoft 365 и веб‑версии по состоянию на май 2025 г.; файлы, содержащие эти формулы, при открытии в более старых версиях Excel покажут ошибки или нераспознанные функции.
• При совместной работе с пользователями старых версий: предоставьте копию отчёта с фиксированными значениями (копировать → Вставить значения) или реализуйте резервную логику через INDEX/SEQUENCE для суммарных отчётов.

Короткая памятка по производительности

• CHOOSECOLS и CHOOSEROWS сами по себе лёгкие. Производительность зависит от размера исходных массивов и от дополнительных операций (VSTACK, FILTER, SORTBY).
• Для очень больших таблиц рассмотрите предварительную агрегацию в Power Query.

Сводка

CHOOSECOLS и CHOOSEROWS — простые и мощные инструменты для выборки столбцов и строк по позициям. Они хорошо сочетаются с VSTACK/HSTACK, SORTBY и проверкой данных. Используйте структурированные таблицы для стабильности и предотвращения ошибок при росте данных.

Главные выводы:

• Функции возвращают динамические массивы и автоматически обновляются при изменениях источника;
• Индексы могут быть положительными или отрицательными; отрицательные отсчитываются с конца;
• Проверка данных и условное форматирование делают решения удобнее для пользователей.

Примечание: при работе с этими функциями всегда проверяйте совместимость с версиями Excel у конечных пользователей.