С числами Excel справляется классно, а вот текст может заставить вас изрядно понервничать. Добавите вручную единицы измерения или пропустите лишний пробел — и формулы тут же начнут чудить. Делимся простыми приёмами, чтобы в Excel всё работало идеально, даже с самым сложным текстом!
- Как превратить «текстовые» числа в настоящие цифры
- Волшебство индивидуального числового формата в Excel
- Как извлекать куски текста по шаблону: новые функции TEXT
- Автоматическая очистка данных: избавляемся от невидимых символов и «мусора»
- Как правильно объединять текст: TEXTJOIN — лучше старого «&»
- Подпишитесь на Excel-фишки: полезные советы раз в неделю
- Excel угадывает всё сам: Мгновенное заполнение и Формула по образцу
Как превратить «текстовые» числа в настоящие цифры
Числа по умолчанию стоят в Excel по правому краю, а текст — по левому. Если ваши «числа» вдруг выровнялись влево или в углу появился зелёный треугольник — значит, Excel считает их текстом. Такие «числа» никак не участвуют в расчетах и ломают формулы.
Чтобы исправить вручную, просто выделите нужные ячейки, нажмите на значок с восклицательным знаком и выберите «Преобразовать в число».
Если данные часто обновляются (например, их импортируют автоматически), используйте формулу с двойным минусом (—): в новом столбце введите =—, кликните по ячейке с «текстовым» числом — например, из столбца «Вес».
В таблице Excel формула сразу применится ко всем строкам — новые значения тоже автоматически станут числами.
Если число до сих пор выглядит «как текст», просто измените формат ячеек: выделите их и на вкладке «Главная» в группе «Число» выберите «Общий» или «Числовой».
Волшебство индивидуального числового формата в Excel
Если вы вписываете единицы измерения прямо в ячейку (например, «69 фунтов»), Excel считает это текстом, и никакие вычисления не проходят.
Правильно: удалите из ячеек текст, выделите столбец и нажмите Ctrl+1. Во вкладке «Число» выберите «Числовой» — «Особый», и в поле «Тип» напишите 0 «кг» (или любую нужную единицу). Кавычки нужны обязательно — Excel сам покажет нужную подпись, а для формул будет использовать только число.
После нажатия «ОК» вы увидите, например, «50 кг», а в строке формул останется просто число. Значит, дальше можно свободно считать и строить любые формулы.
5 нестандартных приёмов для оформления чисел в Excel
Уходите от скучных форматов и делайте свою таблицу заметной!
Перед тем как создавать свои форматы, проверьте стандартные в разделе «Число» — для финансовых данных или процентов они подходят идеально. Эти форматы сразу правильно оформляют все значения, и таблица становится аккуратнее.
Хочется максимально просто — вынесите единицу измерения в заголовок столбца, а данные оставьте только числами.
Как извлекать куски текста по шаблону: новые функции TEXT
«Текст по столбцам» — это из прошлого. Если данные меняются, каждый раз придётся заново возиться с мастером. Новые функции Excel (Microsoft 365, 2024+, Web) справятся в разы быстрее:
TEXTBEFORE — вытягивает всё, что до указанного символа.
TEXTAFTER — всё, что идёт после.
TEXTSPLIT — сразу разбивает строку на несколько ячеек или столбцов.
К примеру, чтобы получить имена из e-mail и сразу сделать их с заглавной буквы, используйте формулу:
Формула ищет символ «@» и берёт всё слева от него. И работает сразу: стоит изменить e-mail — и имя в соседней ячейке меняется тут же автоматически.
Если нужен только домен, берём TEXTAFTER. А если столбец надо сразу разбить на две части с помощью TEXTSPLIT, помните — результат появится только вне структуры таблицы Excel.
А с огромными объёмами и сложными текстами идеально справится Power Query. Это отдельный инструмент, который в считанные секунды очистит даже миллионы строк, не замедлив работу Excel.
Автоматическая очистка данных: избавляемся от невидимых символов и «мусора»
Часто при импорте из других источников в таблицу вместе с нужными данными попадает и куча лишнего: невидимые пробелы, странные символы и разный «мусор». Например, проверяем длину строки с помощью функции ДЛСТР (LEN) для «Product 452» — ожидаем 11 знаков: семь букв, пробел и три цифры. А в реальности выходит 15: где-то вкрались невидимые символы.
Базовый способ — функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN):
Но и после этого нередко остаётся один лишний символ (12 вместо 11): где-то притаился хитрый знак, который СЖПРОБЕЛЫ пропускает.
При копировании из интернета это почти всегда неразрывный пробел (CHAR(160)), которого СЖПРОБЕЛЫ не трогает. Решение — используйте ПОДСТАВИТЬ (SUBSTITUTE): замените такой пробел на обычный и снова примените СЖПРОБЕЛЫ.
Готово! После проверки увидите точно то количество символов, которое и ожидали.
Как правильно объединять текст: TEXTJOIN — лучше старого «&»
Раньше для склейки слов в Excel пользовались знаком «&»:
Абсолютный справочник по всем символам Excel
Погрузитесь в Excel полностью — разберите все его знаки и символы!
Но если хоть одна из ячеек пустая — получите двойные пробелы или запятые в неудобных местах.
Функция TEXTJOIN (Excel 365, Web, 2019+) гораздо умнее: один раз задаёте разделитель и указываете — пропускать ли пустые ячейки.
Подпишитесь на Excel-фишки: полезные советы раз в неделю
Если вторым параметром поставить TRUE, Excel будет аккуратно пропускать пустые ячейки и у вас никогда не будет двойных пробелов или запятых.
Если описания получаются длинными, нажмите «Перенос текста» на вкладке «Главная» — тогда весь текст будет вмещаться в ячейку, а не убегать за границу таблицы. Для перехода на новую строчку внутри ячейки используйте Alt+Enter.
Excel угадывает всё сам: Мгновенное заполнение и Формула по образцу
Запутанный текстовый шаблон? Просто покажите Excel пару примеров. Начните переписывать нужные значения (например, индексы из адреса) — и Excel предложит сам заполнить столбец (функция «Мгновенное заполнение», Flash Fill). Нажмите Tab или Enter — и столбец готов!
Если подсказка не появилась — вручную вызовите Flash Fill сочетанием Ctrl+E.
Ещё круче — «Формула по образцу» (Formula by Example): Excel сам пишет формулу, подходящую для вашего примера. Введите пару строк — и программа сама предложит динамическую формулу: она будет обновляться при любых изменениях данных и работать на новых строчках без ваших правок.
Сейчас функция доступна всем в веб-версии Excel (внутри таблиц) и пользователям с лицензией Copilot для Excel в Windows.
Как только вы приведёте текст и числа в полный порядок, уделите внимание внешнему виду. Если значения нужно показать на всю ширину нескольких столбцов, не объединяйте ячейки! Лучше используйте «Выравнивание по центру по выделенному» — так таблица останется рабочей: фильтры и сортировки не исчезнут, а внешний вид будет на уровне.
Microsoft 365 Персональный
Microsoft 365 открывает доступ к Word, Excel, PowerPoint сразу на 5 устройствах, 1 ТБ в облаке OneDrive и даёт другие крутые фишки.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка — это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!
Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru
