TRIMRANGE и точечная нотация для создания динамических диапазонов в Excel
В Excel для Microsoft 365 функция УРЕЗДИАПАЗОН (TRIMRANGE) и сокращённые ссылки Trim Refs позволяют отсекать пустые внешние строки и столбцы диапазона. Для использования нужны сборка и канал обновления с поддержкой этих возможностей.
Эта статья особенно полезна тем, кто ищет формулы в Excel, хочет быстрее разобраться в функциях и применять рабочие примеры без путаницы между русской и английской версиями Excel.
Мы разберем, как использовать новые возможности на практике.
Знакомство с функцией TRIMRANGE
Новая функция TRIMRANGE убирает полностью пустые строки и столбцы только с внешних краёв диапазона. Пустоты внутри него сохраняются. Это идеально подходит для создания динамических таблиц подстановки или списков.
=УРЕЗДИАПАЗОН(диапазон; [режим_обрезки_строк]; [режим_обрезки_столбцов])=TRIMRANGE(диапазон, [режим_обрезки_строк], [режим_обрезки_столбцов])Аргументы режима обрезки могут принимать следующие значения:
0— не обрезать соответствующее измерение.1— обрезать пустые ячейки в начале (сверху или слева).2— обрезать пустые ячейки в конце (снизу или справа).3— обрезать пустые ячейки с обеих сторон (значение по умолчанию).
Пример использования с ВПР / VLOOKUP для таблицы в столбцах F:G:
=ВПР(B4; УРЕЗДИАПАЗОН(F:G); 2; ЛОЖЬ)=VLOOKUP(B4, TRIMRANGE(F:G), 2, FALSE)Используйте точечную нотацию для краткости
Для тех, кто предпочитает более лаконичный синтаксис, Excel теперь поддерживает точечную нотацию:
B:.B— обрезает пустые ячейки в конце диапазона.B.:B— обрезает пустые ячейки в начале диапазона.B.:.B— обрезает пустые ячейки с обеих сторон (аналогTRIMRANGEпо умолчанию).
Тот же пример с ВПР / VLOOKUP, но с точечной нотацией:
=ВПР(B4; (F.:.G); 2; ЛОЖЬ)=VLOOKUP(B4, (F.:.G), 2, FALSE)Этот синтаксис особенно удобен в проверке данных для создания растущего списка. Например, для выпадающего списка, который автоматически расширяется, можно использовать формулу Русская версия: =A2:.A30English version: =A2:.A30.
Важные ограничения и советы
Для источников сводных таблиц и диаграмм проверяйте поддержку динамических ссылок отдельно. Наличие функции на листе само по себе не подтверждает поддержку такого синтаксиса в каждом поле интерфейса.
- Сводные таблицы: для растущего источника можно использовать таблицу Excel; после добавления данных обновляйте сводную таблицу.
- Диаграммы: после настройки источника проверьте, попадают ли в него новые данные; не переносите синтаксис формулы в поле источника без проверки.
Используйте TRIMRANGE и точечную нотацию для оптимизации ваших формул, заменяя ссылки на целые столбцы на интеллектуальные динамические диапазоны. Это может сократить объём дальнейших вычислений и сделает ваши таблицы более управляемыми.