Функции CHOOSECOLS и CHOOSEROWS Microsoft Excel — это кардинальные перемены, если вы хотите быстро извлечь определенные столбцы или строки из ваших данных без вложения нескольких функций в формулу. Более того, это динамические функции, то есть они адаптируются к изменениям в вашем наборе данных.
На момент написания статьи (май 2025 г.) этими функциями могут пользоваться только пользователи Excel для Microsoft 365 на Windows или Mac или Excel для веб-сайта.
Синтаксисы CHOOSECOLS и CHOOSEROWS
Функции Excel CHOOSECOLS и CHOOSEROWS похожи на близнецов: их ДНК очень похожи, но их разделяют тонкие различия. То же самое можно сказать и об их синтаксисе.
Вот синтаксис CHOOSECOLS:
=ВЫБЕРИТЕ КОЛС(a,b,c, …)
А вот синтаксис для CHOOSEROWS:
=ВЫБИРАЕМЫЕСТРОКИ(a,b,c, …)
В обоих случаях,
- a (обязательно) — исходный массив, содержащий столбцы (CHOOSECOLS) или строки (CHOOSEROWS), которые вы хотите извлечь,
- b (обязательно) — индексный номер первого столбца (CHOOSECOLS) или строки (CHOOSEROWS), которые необходимо извлечь,
- c (необязательно) представляет собой индексные номера любых дополнительных столбцов (CHOOSECOLS) или строк (CHOOSEROWS), которые необходимо извлечь, каждый из которых должен быть разделен запятыми.
Если индексный номер представляет столбец или строку в центре массива (другими словами, не первый или последний столбец или строку), добавление дополнительных столбцов или строк в центр массива изменит индексированный столбец или строку.
Вы можете подумать, что эти функции очень похожи на функцию TAKE. Однако, если TAKE позволяет извлечь первый или последний x столбцы или строки, или отдельный столбец или строка из другого места в именованной таблице, CHOOSECOLS и CHOOSEROWS позволяют извлекать любое количество столбцов в любом порядке из любого места ваших данных.
Пример 1: Извлечение первого и последнего столбца или строки из таблицы
Я уже сбился со счета, сколько раз я использовал CHOOSECOLS и CHOOSEROWS для извлечения первого и последнего столбца или строки из таблицы. Это особенно удобно, если первый столбец или строка являются заголовком, а последний столбец или строка содержат итоги.
Чтобы следовать по ходу чтения этого руководства, загрузите бесплатную копию книги Excel, используемой в примерах. После того, как вы нажмете на ссылку, вы найдете кнопку загрузки в правом верхнем углу экрана.
Представьте, что вы администратор местной спортивной лиги и составляете отчет, в котором суммируются очки, набранные пятью командами в пяти играх.
Первый набор данных, который вы хотите сгенерировать, покажет вам общее количество очков, набранных каждой командой. Для этого в пустой ячейке введите:
=CHOOSECOLS(T_Games[#All],1,-1)
в котором
- ВЫБЕРИТЕ — это функция, которую вы хотите использовать, поскольку вы извлекаете данные из столбцов «Команда» и «Всего»,
- T_Games — имя таблицы, в которой хранится массив, и [#Все] сообщает Excel, что вы хотите включить в результат строки заголовка и итоговые строки,
- 1 это первый столбец (в данном случае столбец с названием «Команда»), и
- -1 последний столбец (в данном случае столбец с названием «Итого»)
и нажмите Enter.
По умолчанию CHOOSECOLS считает слева направо, а CHOOSEROWS считает сверху вниз. Чтобы изменить порядок, поставьте знак минус (-) перед соответствующими индексными числами.
Вот результат, который вы получите, нажав Enter. Эти данные можно продублировать на другом листе той же книги, например, на вкладке панели мониторинга, или скопировать и вставить как текст в электронное письмо или документ Word.
Теперь, даже если добавляются дополнительные данные, результат, включая строку «Итого», обновляется соответствующим образом.
Вы также можете использовать тот же метод с неформатированными таблицами, используя прямые ссылки на ячейки вместо структурированных ссылок. Однако если вы добавите больше строк справа или столбцов в конец массива, формула не подхватит их, если вы не измените ссылки на ячейки вручную. Кроме того, в целом таблицы предлагают лучшие инструменты и адаптивность, чем неструктурированные ячейки.
Следующий отчет, который вам нужно сгенерировать, покажет количество очков, набранных в каждой игре (строки 1 и 8).
Итак, в пустой ячейке введите:
=ВЫБОР_СТРУКТУРЫ(T_Games[#All],1,-1)
и нажмите Enter. Помните, что добавление [#ВСЕ] после имени таблицы в формуле заставляет Excel подсчитывать заголовки и итоговые строки при обращении к индексным номерам.
Пример 2: Извлечение столбцов из более чем одного диапазона
Теперь предположим, что у вас есть эта электронная таблица и ваша цель — составить список, содержащий общие баллы каждой команды в лигах 1 (зеленая), 2 (синяя) и 3 (серая).
На этот раз вы не хотите, чтобы Excel извлекал заголовки столбцов и итоги строк, поскольку вы собираетесь вложить функцию VSTACK в формулу CHOOSECOLS, чтобы разместить три таблицы непосредственно друг над другом. Поэтому в ячейках I1 и J1 создайте заголовки столбцов вручную.
Затем в ячейке I2 введите:
=CHOOSECOLS(VSTACK(League_1,League_2,League_3),1,-1)
в котором
- ВЫБЕРИТЕ это функция, которую вы будете использовать для извлечения столбцов,
- ВСТАК позволяет объединять результаты вертикально,
- Лига_1,Лига_2,Лига3 — это имена таблиц, представляющих массивы, а отсутствие [#ALL] после имен таблиц сообщает Excel, что не следует включать в результат строки заголовка и итоговые строки,
- 1 сообщает Excel о необходимости извлечь первый столбец («Команда») из каждого массива и
- -1 сообщает Excel о необходимости извлечь последний столбец из каждого массива («Итого»)
и нажмите Enter.
На этом этапе вы можете пойти еще дальше и отсортировать результат в порядке убывания, введя:
=СОРТПО(I2#,J2:J16,-1)
в ячейку L2 и нажмите Enter.
Пример 3: использование CHOOSECOLS с проверкой данных и условным форматированием
До сих пор я показывал вам преимущества использования CHOOSECOLS и CHOOSEROWS для извлечения первых и последних столбцов и строк из массива. Однако на этот раз я покажу вам, как можно использовать CHOOSECOLS для извлечения других столбцов и объединить это с дополнительными инструментами Excel, чтобы сделать вашу электронную таблицу выдающейся.
В этой таблице показаны результаты пяти команд за пять игр, включая общее количество очков, набранных в каждой игре в строке «Общая сумма». Ваша цель здесь — получить результат, который показывает счет каждой команды и общую сумму в ячейках A11–B15 для номера игры, который вы введете в ячейку B9.
Сначала введите номер игры в ячейку B9, чтобы у вас было с чем работать при создании формулы CHOOSECOLS. Затем в ячейке A11 введите:
=CHOOSECOLS(T_Scores[[#Данные],[#Итого]],1,B9+1)
в котором
- ВЫБЕРИТЕ это формула, используемая для извлечения столбцов,
- T_Scores это имя таблицы, и [[#Данные],[#Итого]] сообщает Excel о необходимости включить в результат данные и итоги, но не строку заголовка,
- 1 представляет собой первый столбец («Итого»), и
- В9+1 сообщает Excel, что второй аргумент индекса представлен значением в ячейке B9 плюс один. Причина, по которой вам нужно включить +1 здесь потому, что номера игр начинаются со второго столбца таблицы. В результате, набрав 3 в ячейку B9 извлекает данные из четвертого столбца таблицы, который представляет собой игру 3.
Хотя ввод номера игры в ячейку B9 работает нормально, если вы или кто-то другой случайно введете недопустимое число, функция CHOOSECOLS вернет ошибку #VALUE.
Excel возвращает ошибку #ЗНАЧЕНИЕ, если какой-либо из индексных номеров равен нулю или превышает количество столбцов или строк в массиве.
Это хороший случай для использования проверки данных в ячейке B9 для создания раскрывающегося списка доступных номеров игр.
Мой любимый способ сделать это для строки заголовка в таблице Excel — сначала выбрать все соответствующие ячейки, плюс несколько дополнительных для обеспечения роста, и назвать диапазон в поле имени в верхнем левом углу окна Excel. Обратите внимание, что я не выбрал заголовок столбца «Команда», потому что я не хочу, чтобы он отображался в раскрывающемся списке игр в ячейке B9.
Далее выберите ячейку B9 и нажмите верхнюю половину кнопки «Проверка данных» на вкладке «Данные» на ленте.
Теперь в диалоговом окне «Проверка данных» выберите «Список» в поле «Разрешить», убедитесь, что установлен флажок «Игнорировать пустые поля», введите символ равенства (=) и затем имя, которое вы только что дали диапазону заголовков столбцов, и нажмите «ОК».
Теперь вы можете нажать на стрелку раскрывающегося списка в ячейке B9, чтобы выбрать допустимый номер игры, будучи уверенными в том, что даже если вы добавите дополнительные столбцы в таблицу, CHOOSECOLS и именованный диапазон при проверке данных будут работать вместе, чтобы соответствующим образом обновить доступные параметры.
Наконец, чтобы еще лучше визуализировать данные, выберите ячейки B11–B15 и на вкладке «Главная» на ленте нажмите «Условное форматирование», наведите указатель мыши на «Гистограммы» и выберите сплошной цвет заливки.
В результате вы не только извлекли определенные столбцы из своих данных с помощью CHOOSECOLS, но и сделали результат динамичным, добавив раскрывающийся список, а также отформатировали данные для повышения визуальной ясности.
CHOOSECOLS и CHOOSEROWS создают динамические массивы, то есть результат выливается из ячейки, в которую вы ввели формулу. В результате, прежде чем вводить формулу, убедитесь, что в вашей таблице достаточно места, иначе вы получите ужасную ошибку #SPILL!. Кроме того, поскольку динамические массивы и отформатированные таблицы Excel несовместимы, если вы хотите отформатировать результат, вам придется сделать это вручную или с помощью условного форматирования.
