Как использовать функции CHOOSECOLS и CHOOSEROWS в Google Таблицах для извлечения данных

Составьте краткое изложение этой истории. Вот краткое изложение содержания истории, основанное на фактах: Вот упрощенная версия содержания истории: Вот беззаботный взгляд на содержание истории: Исследуйте другую точку зрения: Покажите мне факты Объясните это так, будто мне 5 лет Дайте мне беззаботный пересказ

Функции CHOOSECOLS и CHOOSEROWS в Google Таблицах идеально подходят для быстрого извлечения определённых столбцов или строк из данных без создания запутанных вложенных формул. Более того, поскольку это динамические функции, они адаптируются к изменениям исходных данных.

Чтобы следовать инструкциям, создайте собственную копию шаблона Google Таблиц. После перехода по ссылке войдите в свою учётную запись Google и нажмите «Создать копию», чтобы добавить копию файла на Google Диск. Затем следуйте вкладкам в нижней части окна Google Таблиц, выполняя примеры ниже.

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

Как только вы поймете, как использовать одну из этих двух функций в Google Таблицах, вы будете хорошо понимать, как работает другая.

Вот синтаксис CHOOSECOLS:

=ВЫБЕРИТЕ КОЛС(a,b,c)

А вот синтаксис для CHOOSEROWS:

=ВЫБИРАЕМЫЕСТРОКИ(a,b,c)

Вот что представляет собой каждый аргумент:

Если какой-либо из индексных номеров равен нулю или превышает количество столбцов или строк в массиве, Google Таблицы вернут ошибку #ЗНАЧ!.

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

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

С тех пор, как я узнал, как работают функции CHOOSECOLS и CHOOSEROWS, я постоянно использую их для извлечения первого и последнего столбца или строки из своих данных. Это особенно удобно, если первый столбец или строка — заголовок, а последний столбец или строка содержит итоги.

Извлечение первого и последнего столбцов

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

Для этого в пустой ячейке введите:

=CHOOSECOLS(Продажи[#ALL],1,-1)

в котором

• ВЫБЕРИТЕ это функция, которую нужно использовать для извлечения данных из столбцов «Магазин» и «Итого»,
• Продажи — имя таблицы, содержащей массив, и [#ВСЕ] сообщает Google Таблицам, что вы хотите включить строку заголовка в результат,
• 1 указывает, что вы хотите извлечь первый столбец (в данном случае столбец «Магазин»), и
• -1 указывает, что вы хотите извлечь последний столбец (в данном случае столбец Tot).

Вместо того, чтобы вручную вводить массив в аргументе a, с помощью мыши выберите ячейки, на которые вы хотите ссылаться.

Вот результат после нажатия Enter:

Обратите внимание, что, хотя вы ввели формулу только в ячейку J1, результат переместился из этой ячейки в ячейку K12, чтобы вместить количество столбцов и строк в результате.

Кроме того, поскольку исходные данные находятся в структурированной таблице, при изменении данных изменяется и результат функции CHOOSECOLS. Это также справедливо при добавлении дополнительных столбцов или строк в набор данных.

Если вы используете прямые ссылки на ячейки в аргументе a Вместо табличной ссылки формула не будет учитывать новые данные, пока вы не измените ссылки на ячейки вручную. Независимо от того, используете ли вы Google Таблицы или Microsoft Excel, я всегда рекомендую использовать таблицы вместо неструктурированных ячеек, поскольку они предлагают лучшие инструменты и адаптивность.

Теперь вы можете скопировать сводные данные в другую таблицу Google Таблиц (например, на вкладку панели инструментов) или в документ Google Docs.

Извлечение первой и последней строк

Подобным же образом, извлечение первой и последней строк удобно для создания горизонтальных сводок ваших данных.

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

В пустой ячейке введите:

=chooserows(Продажи[#ALL],1,-1)

и нажмите Enter. Как и в предыдущем примере, добавление [#ВСЕ] после имени таблицы сообщает Google Таблицам, что вы хотите, чтобы строка заголовка учитывалась как строка 1 в поиске индекса.

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

В примере 1 мы извлекли определённые столбцы и строки из отдельных диапазонов. Однако вы можете комбинировать CHOOSECOLS с функцией VSTACK, чтобы объединить извлечения из нескольких диапазонов одновременно.

Здесь вы планируете создать список общих очков каждой команды в Лиге 1 (красный) и Лиге 2 (зеленый).

Первое, что следует отметить, — не нужно извлекать заголовки столбцов, поскольку данные из каждой таблицы нужно расположить друг над другом. Если включить заголовки столбцов, они появятся в верхней части обоих наборов данных, что устранит ощущение, что вы смотрите на объединённый набор данных.

В результате в ячейках J1 и K1 введите заголовки столбцов вручную.

Затем в ячейке J2 введите:

=CHOOSECOLS(VSTACK(Лига1;Лига2);1;-1)

в котором

• ВЫБЕРИТЕ сообщает Google Таблицам, что вы хотите извлечь определенные столбцы,
• ВСТАК объединяет результат вертикально (отсюда «v» в названии функции),
• Лига1,Лига2 — это массивы, содержащие столбцы, а отсутствие [#ALL] в формуле указывает Google Таблицам не включать строки заголовков,
• 1 извлекает первый столбец (Команда) из каждого массива и
• -1 извлекает последний столбец (Tot) из каждого массива.

Нажав Enter, вы получите следующий результат:

Вы можете пойти еще дальше и вложить всю формулу в функцию SORT, тогда в сводной таблице команды будут отображаться в порядке их общих очков.

Для этого дважды щелкните ячейку, в которой вы ранее ввели комбинацию CHOOSECOLS-VSTACK, и добавьте следующие аргументы SORT:

=SORT(CHOOSECOLS(VSTACK(Лига1;Лига2);1;-1),2,ЛОЖЬ)

в котором

• SORT это функция, которая сортирует диапазон на основе указанного столбца,
• 2 сообщает Google Таблицам, что нужно сортировать диапазон по второму столбцу, и
• НЕПРАВДА указывает, что данные следует сортировать в порядке убывания.

Впечатляет, что при изменении каких-либо значений в исходном наборе данных сводная таблица корректируется соответствующим образом. В данном случае изменение счёта команды H в 6-й игре с 51 до 89 поднимает её с девятого места на четвёртое.

Пример 3: Создание динамических функций CHOOSECOLS и CHOOSEROWS

До сих пор я показывал вам, как использовать функции CHOOSECOLS и CHOOSEROWS для извлечения первого и последнего столбца и строки из массива в Google Таблицах. Однако в этом последнем примере я покажу, как динамически извлекать другие столбцы и строки, ссылаясь на ячейки, а не используя жёстко заданные индексы, в аргументах.

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

В этой таблице Google Таблиц представлены результаты пяти команд за шесть игр, а также общий счёт каждой игры. Ваша цель — составить сводку по номеру игры, указанному в ячейке B9. Сначала введите номер игры в эту ячейку, чтобы у вас была информация для работы при создании формулы CHOOSECOLS.

Затем в ячейке A11 введите:

=ВЫБРАТЬКОЛС(РезультатыКоманды;1;B9+1)

в котором

• ВЫБЕРИТЕ сообщает Google Таблицам, что вы собираетесь извлечь определенные столбцы,
• TeamScores — это имя таблицы, содержащей данные, которые вы хотите извлечь,
• 1 — это индексный номер для столбца «Команда», а
• В9+1 Google Таблицам сообщает, что второй аргумент индекса рассчитывается путем добавления 1 к значению в ячейке B9. Причина, по которой вам нужно включить +1 Это потому, что номера игр начинаются со второго столбца первой строки, а не с первого. В результате, вводя 4 в ячейку B9 извлекаются данные из пятого столбца таблицы, который представляет собой игру 4.

Вот что вы получите, нажав Enter:

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

Совет: добавьте правило проверки данных

Хотя ввод номера игры в ячейку B9 работает точно так же, как и ожидалось, делая функцию CHOOSECOLS динамической, при вводе недопустимого числа вы внезапно получите ошибку #VALUE!. Чтобы предотвратить это, вы можете использовать проверку данных для управления входными параметрами ячейки.

Для этого выберите ячейку B9 и на вкладке Данные нажмите «Проверка данных».

Затем на панели «Правила проверки данных» справа нажмите «Добавить правило».

Затем в поле «Критерии» выберите «Раскрывающийся список» и введите в текстовое поле первый возможный вариант для этой ячейки. Затем нажмите «Добавить ещё один элемент» и повторяйте этот процесс, пока не будут указаны все варианты. Когда все варианты будут указаны, нажмите «Готово».

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

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

Функции CHOOSECOLS и CHOOSEROWS в Google Таблицах создают динамические массивы, то есть результат выводится из ячейки, в которую введена формула. Поэтому перед вводом формулы необходимо убедиться, что в таблице достаточно места. В противном случае возникнет ошибка #REF!.