Быстрые ссылки
Ищете способ упорядочить вашу таблицу и уменьшить её размер без использования динамических формул, таких как OFFSET, INDEX или TOCOL? Функция TRIMRANGE определяет ячейки, которые занимает ваши данные, и автоматически подстраивается под них.
Оператор Trim Ref является более простой и краткой версией функции TRIMRANGE, о которой я расскажу позже в статье.
Синтаксис TRIMRANGE
Функция TRIMRANGE имеет три аргумента:
где
Если вы пропустите аргументы b и/или c, Excel будет по умолчанию обрезать как начальные, так и конечные строки и/или столбцы.
На момент написания (январь 2025 года) TRIMRANGE не позволяет обрезать пустые строки или столбцы между существующими данными. Вы можете использовать её только для обрезки данных до или после ваших данных. Вместо этого вы можете использовать другие способы для удаления пустых строк данных между заполненными строками.
Пример 1: TRIMRANGE с суммой
В этом примере я хочу вычесть значения из столбца B из значений в столбце C, чтобы получить прибыль.
Я знаю, что в будущем добавлю дополнительные строки, поэтому хочу, чтобы Excel автоматически учитывал эти строки в формуле вычитания. Для этого я могу ввести:
в ячейку D2, так как это будет принимать ссылки на первые 200 строк. Однако в этом случае таблица будет выглядеть неаккуратно из-за множества пустых расчётов. Более того, если я использую ссылки на целый столбец, расчёт будет распространяться до самого низа таблицы. Это приведет к более чем миллиону расчётов, тем самым значительно увеличив размер таблицы и замедлив её работу.
Вместо этого я введу:
в ячейку D2, что эффективно обрежет лишние ячейки внизу моих данных, чтобы предотвратить перегрузку Excel и сохранить аккуратность таблицы.
Я пропустил аргументы b и c в приведённых выше ссылках TRIMRANGE, так как по умолчанию Excel обрезает начальные и конечные строки и столбцы, что подходит в данном примере, так как нет пустых строк или столбцов в начале и нет данных справа от столбца D.
Теперь, когда я добавлю новые строки данных внизу, соответствующие ячейки в колонке 'Прибыль' будут вычисляться автоматически.
Пример 2: TRIMRANGE с XLOOKUP
В этом втором примере у меня есть список футболистов, и я собираюсь использовать функцию XLOOKUP, чтобы определить, какой цвет формы мне нужно предоставить им в зависимости от их команды.
Я также знаю, что планирую набрать ещё пятерых игроков, поэтому, когда я добавлю свою формулу XLOOKUP, мне нужно расширить её до 22 строки. Итак, в ячейку E2 я могу ввести:
где B2:B22 — это диапазон, $G$2:$G$7 — массив поиска, а $H$2:$H$7 — массив возврата. Я использовал символы $ для указания Excel, что это абсолютные (фиксированные) ссылки на ячейки. Однако, как и в предыдущем примере, это оставит пять строк пустыми, что будет выглядеть неаккуратно из-за ошибки #N/A. Кроме того, Excel работает сильнее, чем нужно, что может повлиять на производительность, если в формуле содержится много пустых строк.
Я могу также использовать OFFSET, INDEX или TOCOL для создания динамических формул диапазона, но это гораздо более сложные способы достижения того же результата, что и более простой метод TRIMRANGE.
<pИтак, в="" ячейку="" e2="" я="" введу:<="" p="">
что является точно такой же формулой, как выше, за исключением того, что я указал диапазон (B2:B22) внутри функции TRIMRANGE. Обратите внимание, что на этот раз я решил включить второй аргумент ("2"), который указывает Excel, что я хочу обрезать конечные пустые строки. Однако я не включал третий аргумент, так как мой диапазон охватывает только один столбец.
Использование операторов Trim Ref
Признаю, некоторые формулы в приведённых выше примерах довольно сложны. По этой причине Microsoft также ввела операторы Trim Ref, краткую версию функции TRIMRANGE. Другими словами, операторы Trim Ref избавляют вас от необходимости встраивать TRIMRANGE в другие функции. Более того, операторы Trim Ref не требуют от вас указания, обрезаете ли вы строки или столбцы, так как они автоматически обрезают и те, и другие.
Использование оператора Trim Ref подразумевает добавление точки (.) с одной или обеих сторон двоеточия в вашей формуле:
Где добавить точку
Какие пробелы обрезаются
После двоеточия
Конечные пробелы
Перед двоеточием
Начальные пробелы
С обеих сторон двоеточия
Начальные и конечные пробелы
Поскольку операторы Trim Ref представляют собой единственную "точку" в вашей формуле, их может быть сложно заметить при проверке вашей работы или работы кто-то другого. Это важно учитывать, если что-то не работает так, как вы ожидаете!
Используя те же данные о футболистах, что и в Примере 2, в ячейке E2 я введу:
Обратите внимание, что я добавил точку после двоеточия в B2:.B22. Это нужно, чтобы Excel обрезал конечные пробелы. На скриншоте ниже представлен тот же результат, хотя я использовал гораздо более простую формулу без внедрения функции TRIMRANGE в свою формулу.
Как и при использовании функции TRIMRANGE, я знаю, что могу добавить больше строк данных внизу, и функция XLOOKUP будет применяться к этим данным.
Почему использовать TRIMRANGE и Trim Refs вместо структурированных таблиц?
Вы можете оправданно утверждать, что следует представлять ваши данные в форматированных таблицах Excel и использовать структурированные ссылки, которые автоматически расширяются, чтобы включать новые данные и все соответствующие расчёты, которые вы создаёте. В большинстве случаев я согласен с этим.
Тем не менее, распавшиеся массивы (и некоторые LAMBDAS) не могут находиться в структурированных таблицах, поэтому в таких ситуациях использование TRIMRANGE является удобной альтернативой. Также в некоторых случаях вы можете предпочесть размещать диапазон вне структурированной таблицы (например, если хотите уникальным образом отформатировать ваши данные), и функция TRIMRANGE поможет вам сохранить неструктурированные данные в порядке.
Помимо функции TRIMRANGE и операторов Trim Refs в Excel есть и другие способы упорядочить ваши данные, такие как использование Power Query или применение возможностей ИИ через Copilot.
</pНа></pИтак,>
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Любите активный отдых на природе? Подписывайтесь на канал Поход лайфхак в Яндекс Дзен — кладезь полезных советов для любителей активного отдыха!
Вы также можете читать наши материалы в:
- Telegram: https://t.me/gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru