Как проредить колонки и столбцы диапазонов в Google Sheets

Статья Михаила Смирнова, написанная для канала t.me/google_sheets

Чат канала: t.me/google_spreadsheets_chat

Другие статьи Миши, написанные для нашего канала: t.me/google_sheets/1203

Здравствуйте, товарищи!

Сегодня мы возьмём диапазон и будем с помощью функции QUERY() ловко отрубать от него различные строки и столбцы, а другие будем оставлять.

Диапазоном может быть любой реальный диапазон в таблице (какой-нибудь Лист1!A:D или полученный из формул (вот, например, формула возвращает диапазон =SEQUENCE(10; 5)).

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

=ARRAYFORMULA(
    "строка " & SEQUENCE(10) & CHAR(10)
  & "столбец " & SEQUENCE(1; 10)
)

Тут сразу будет видно, какие строки и столбцы остались, а какие мы выкинули

Для наглядности галочками буду обозначать строки и столбцы, которые должны остаться (зелёные), остальные — выкинем (серые).

Чем будем пользоваться? — Сейчас QUERY

Мы будем использовать QUERY() причём именно условия OFFSET и SKIPPING и немного функцией TRANSPOSE(). Просто чтобы показать, что так можно. То есть это не панацея, не "рекомендованный для любого случая" способ, подходит ли он лучше остальных — надо думать.

Остальные способы — это явное указание нужных колонок в SELECT, использование с соответствующими условиями FILTER() (только для строк или колонок) или два FILTER() (один для строк, другой (поверх первого) — для колонок).

Что делает OFFSET N?

QUERY(...; "OFFSET N";) убирает верхние N строк из результата.

Собственно, как убрать первые N строк из диапазона?

=QUERY(B3:K12; "OFFSET 4";)

Что делает SKIPPING N?

QUERY(...; "SKIPPING N";) оставляет от вывода каждую N-ную, начиная с первой.

SKIPPING — недокументированная функциональность, но когда нас это останавливало?

Как оставить только нечётные строки?

=QUERY(B3:K12; "SKIPPING 2";)

Убираем только чётный строки, оставляем нечётные.

Что делает TRANSPOSE()?

TRANSPOSE(...) повернёт диапазон относительно диагонали, то есть заменит строки на столбцы, а столбцы на строки.

Например, у нас есть столбец A:A. Формула =TRANSOSE(A:A) выведет его в виде строки.

Если два раза применить TRANSPOSE(), диапазон вернётся в исходное положение. Обычно между этими двумя применениями TRANSPOSE() с диапазоном надо что-то сделать, иначе зачем крутили?

Как убрать первые N столбцов из диапазона?

=TRANSPOSE(
  QUERY(
    TRANSPOSE(B3:K12);
    "OFFSET 4";
  )
)

Остальные примеры

Сейчас всяко совмещать будем.

Как убрать нечётные строки диапазона?

=QUERY(
  QUERY(B3:K12; "OFFSET 1";);
  "SKIPPING 2";
)

Как убрать чётные столбцы диапазона?

=TRANSPOSE(
  QUERY(
    TRANSPOSE(B3:K12);
    "SKIPPING 2";
  )
)

Как убрать нечётные столбцы диапазона?

=TRANSPOSE(
  QUERY(
    QUERY(
      TRANSPOSE(B3:K12);
      "OFFSET 1";
    );
    "SKIPPING 2";
  )
)

Ну, вы поняли? Для столбцов всё то же, что и для строк, только в начале и в конце делаем TRANSPOSE().

Как убрать чётные столбцы и строки:

=TRANSPOSE(
  QUERY(
    TRANSPOSE(
      QUERY(B3:K12; "SKIPPING 2";)
    );
    "SKIPPING 2";
  )
)

Остались только ячейки из нечётных столбцов и строк

Как оставить каждую N-ю строку, начиная с M-ой? (вместо M-1 надо вставить конкретное число):

=QUERY(
  QUERY(B3:K12; "OFFSET 4";);
  "SKIPPING 3";
)

Оставляем каждую третью строку, начиная с 5-й

На этом всё. Спасибо за внимание.

Ссылки

Функция QUERY()

QUERY() — документация на функцию
Описание языка запросов, которые можно писать в QUERY()
Крутейшая таблица справочник по QUERY() от Паши Мрыкина (там куча примеров)
Не путайте порядок условий (команд, кляуз) в запросе — памятка

Функция TRANSPOSE()

TRANSPOSE() — документация на функцию