Как работает эта формула?

Question

Начиная с ДВССЫЛ перестал понимать. Особенно сложно с фигурными скобками и знаком &.

=ТРАНСП(ДВССЫЛ("'ЛИСТ_1'!"&ArrayFormula({АДРЕС(17;3)&":"&АДРЕС(21;СЧЁТЗ('ЛИСТ_1'!17:17)+2)})))

Answer 1

Формула выводит данные из диапазона на листе ЛИСТ_1. Где адрес диапазона строится по определенным условиям.

Никакой нужды в скобках и ARRAYFORMULA нет. Кто-то перестарался, или неумело пытался запутать.

=TRANSPOSE(INDIRECT(
  "'ЛИСТ_1'!"&
  ADDRESS(17,3)&
  ":"&
  ADDRESS(21,COUNTA('ЛИСТ_1'!17:17)+2)
))

Обычно, когда запутались, формулу решают "луковым" или "капустным" методом. Это когда начинают разбирать формулу от большего к меньшему, как будто снимают листья капусты.

Получает адрес диапазона в виде строки для INDIRECT

="'ЛИСТ_1'!"&
  ARRAYFORMULA(
    {ADDRESS(17,3)&":"&ADDRESS(21,COUNTA('ЛИСТ_1'!17:17)+2)}
  )

Конкатенирует значения в строку-диапазон

=ARRAYFORMULA({ADDRESS(17,3)&":"&ADDRESS(21,COUNTA('ЛИСТ_1'!17:17)+2)})

Получает адрес по координатам: 21 строка и колонка, номер которой равен количеству значений в строке 17 плюс 2.

=ADDRESS(21,COUNTA('ЛИСТ_1'!17:17)+2)

Answer 2

Фигурные скобки не нужны. Они определяют массив констант и не могут содержать внутри себя ссылки.

АДРЕС - текстовая функция, медленная. ДВССЫЛ - медленная и, мало того, что работает с текстами, - она еще и пересчитывается когда надо и не надо (летучая). СЧЕТЗ не стоит применять для всей строки (считает весь диапазон, а не только ограниченный данными). Вместо нее - СЧЁТЕСЛИ(Лист_1!17:17;"?*") (если там числа - "<>")

Диапазон можно создать функциями полегче:

ИНДЕКС(Лист_1!C:C;17):ИНДЕКС(Лист_1!$1:$1000;21;СЧЁТЕСЛИ(Лист_1!17:17;"<>")+2)

Для транспонирования диапазона формулу следует вводить, как формулу массива (В Excel функция ТРАНСП - массивная, возможно, в Google - таблицах не так...)

БЛОГ НА HUSL

Как работает эта формула?

Ответы (2 шт):