СЖПРОБЕЛЫ (функция СЖПРОБЕЛЫ)
SharePoint Server 2019 SharePoint Server 2016 SharePoint Server 2013 SharePoint Server 2013 Enterprise SharePoint в Microsoft 365 SharePoint Foundation 2010 SharePoint Server 2010 SharePoint Server 2007 SharePoint в Microsoft 365 для малого бизнеса Windows SharePoint Services 3.0 Еще. Меньше
Удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать лишние пробелы.
Синтаксис
СЖПРОБЕЛЫ(текст)
Текст — текст, из которого удаляются пробелы.
Пример
Описание (результат)
=СЖПРОБЕЛЫ(» Доход за первый квартал «)
Удаляет начальные и конечные пробелы из текста в формуле (Доход за первый квартал)
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Функция СЖПРОБЕЛЫ в Excel с примерами использования
Опубликовано 03.05.2020 Автор Ренат Лотфуллин
Многие пользователи табличного редактора ошибочно вводят дополнительные пробелы в текстовые строки, содержащиеся в ячейках. Например, текстовые строки «Macbook Air 128 Gb» и «Macbook Air 128 Gb» будут распознаны как содержащие различные значения, поскольку между словами присутствует лишний пробел. Убрать его можно вручную, если записей немного. Но что делать, если таблица содержит огромное количество строк, которые нужно проверить? Упростить задачу поможет встроенная функция СЖПРОБЕЛЫ.
Функция СЖПРОБЕЛЫ в Excel предназначена для удаления всех пробелов из текстовой строки, принимаемой в качестве аргумента, и возвращает строку, которая содержит только одиночные пробелы.
Синтаксис функции
Функция СЖПРОБЕЛЫ имеет следующую синтаксическую запись:
Единственным аргументом функции является текст, который принимает ссылку на ячейку с текстовыми данными или текстовую строку, заключенную в кавычки (например, в результате выполнения =СЖПРОБЕЛЫ(«некоторый текст») будет возвращена строка «некоторый текст»).
Примечание
В наборе знаков ASCII символу пробела соответствует значение 32. Функция СЖПРОБЕЛЫ используется для удаления данных символов (кроме одиночных) в текстовых строках, закодированных с использованием таблицы ASCII. В таблице Unicode содержится дополнительный знак пробела, называемый неразрывным пробелом. Ему присвоено числовое значение 160 в таблице Юникод. Функция СЖПРОБЕЛЫ не удаляет лишние неразрывные пробелы, которые наиболее распространены в текстах формата HTML и обозначаются как « ». Эту особенность следует учитывать при обработке данных, полученных с веб-страниц.
Примеры использования функции СЖПРОБЕЛЫ
Пример 1. В таблице имеется столбец из нескольких строк, в которых могут быть допущены ошибки – содержаться дополнительные пробелы. Создать рядом столбец с текстовыми строками на основе имеющихся, в которых отсутствуют лишние пробелы.
Исходная таблица имеет следующий вид:
Выделим ячейки B2:B7, в строку для ввода функций введем следующую запись:
A2:A7 – диапазон ячеек, содержащих исходные строки с лишними пробелами. Для выполнения функции необходимо использовать комбинацию Ctrl+Shift+Enter, поскольку используем формулу массива.
В результате выполнения формулы получим следующее:
Пример 2. В таблице содержатся строки с записями о продажах, совершенными двумя менеджерами магазина на протяжении 1 рабочего дня. Некоторые записи содержат ошибочно проставленные дополнительные пробелы. Подсчитать число продаж, сделанных каждым менеджером по-отдельности.
Исходная таблица данных имеет следующий вид:
Для подсчета числа продаж (количества записей в таблице) для первого менеджера используем следующую формулу:
Функция СУММ используется для подсчета количества значений параметров, переданных в качестве аргумента. Оператор «—» (двойное отрицание) используется в Excel для явного преобразования типов данных. В скобках содержится логическое выражение, которое возвращает один из двух возможных вариантов значений: ИСТИНА или ЛОЖЬ.
С помощью двойного отрицания происходит преобразование данных логического типа к соответствующим числовым значениям: 1 и 0. Функция ЛЕВСИМВ отбирает первые 10 символов в каждой строке из столбца A:A (длина строки «Менеджер 1» составляет 10 символов). Для корректировки ошибок (удаления дополнительных пробелов) использована рассматриваемая функция СЖПРОБЕЛЫ. Результат выполнения функции ЛЕВСИМВ напрямую сравнивается с исходной текстовой строкой.
Для получения корректных результатов необходимо использовать комбинацию Ctrl+Shift+Enter (формула массива). Выполнив функцию, получим следующее значение:
Аналогичным способом определим число продаж у второго менеджера, используя следующую формулу:
Если упустить функцию СЖПРОБЕЛЫ, получим некорректные результаты:
Также можно легко подсчитать число строк, в которых содержатся лишние пробелы, используя следующую формулу (вводить с использованием комбинации клавиш Ctrl+Shift+Enter):
Функция СУММ определяет сумму значений, содержащихся в массиве, переданном в качестве аргумента. Благодаря использованию двойного отрицания, логические данные ИСТИНА и ЛОЖЬ преобразуются в массив соответствующих числовых значений (1 или 0).
Выражением ДЛСТР(A2:A8)-ДЛСТР(СЖПРОБЕЛЫ(A2:A8))<>0 выполняется проверка длин строк до и после применения рассматриваемой функции СЖПРОБЕЛЫ. Если длина строки изменилась (разница длин не равна нулю), возвращается логическое ИСТИНА, преобразуемое в число 1.
То есть, лишние пробелы были найдены в 4 строках столбца.
Вам также могут быть интересны следующие статьи
- Четыре способа использования ВПР с несколькими условиями
- Несколько условий ЕСЛИ в Excel
- Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
- Как расчитать дисперсию в Excel с помощью функции ДИСП.В
- Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel
- Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Excel
- Функция СУММЕСЛИМН в Excel
- Изучаем функцию СМЕЩ
- Многоразовое копирование формулы ВПР
- Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали
Функция СЖПРОБЕЛЫ в Excel и примеры ее использования
Лишние пробелы в тексте замечают не все. И не для всех это, в общем-то, важно. Но в Excel это может обернуться серьезной проблемой, потому что при выполнении команд программа просто не будет учитывать ячейки с дополнительными пробелами.
Как удалить лишние пробелы в Excel в автоматическом режиме? Чтобы не испортить зрение, вглядываясь в каждую ячейку и убирая лишние доступы клавишей Backspace, можно воспользоваться специальной функцией – СЖПРОБЕЛЫ.
Синтаксис функции СЖПРОБЕЛЫ в Excel
Да, проговорить название команды может оказаться непросто. Но вот понять ее синтаксис и принцип работы очень легко. Если начать вводить команду, то подсветится следующее: =СЖПРОБЕЛЫ(текст). Т.е. в скобках нужно всего лишь задать ячейку (ячейки), в которых необходимо удалить пробелы.
Примечание: функция убирает все лишние пробелы, за исключением того, который ставится по умолчанию между словами.
Те, кто хорошо владеет техникой печатания, наверняка недоумевают, как вообще могут появиться лишние пробелы. Но опытные пользователи знают, что информация для Excel часто копируется из разных источников, которые могли заполняться дилетантами. Поэтому в тексте и встречаются опечатки и ненужные пробелы.
Пример использования функции СЖПРОБЕЛЫ
На небольшом примере рассмотрим, как пользоваться функцией СЖПРОБЕЛЫ. Имеем таблицу, в которой указаны наименования детских игрушек и их количество. Не очень грамотный оператор вел примитивный учет: при появлении дополнительных единиц игрушек он просто впечатывал новые позиции, несмотря на то, что аналогичные наименования уже есть. Наша задача: подсчитать общую сумму каждого вида.
Невооруженным глазом видно, что некоторые позиции написаны с явными лишними пробелами. Рассмотрим, к чему это может привести.
Подсчитывать общее количество позиций по каждому виду игрушек будем через функцию СУММЕСЛИ. Вводим ее, протягиваем на остальные ячейки и смотрим результат. Программа выдала ответ по плюшевым зайцам: 3. Хотя мы видим, что их должно быть 3+2=5. В чем проблема? В лишних пробелах.
Запишем функцию в ячейке D3. В качестве аргумента введем ячейку B3, в которой значится наименование игрушки.
Теперь протянем функцию до 14 строки и увидим, что тексты действительно выровнялись, потому что пробелы удалились. Проверим это наверняка, изменив диапазон в команде СУММЕСЛИ. Вместо B3:B14 пропишем D3:D14 и посмотрим результат. Теперь плюшевых зайцев действительно 5, медведей – 6 и т.п. Получается, символы табуляции играют важную роль, и их нужно подчищать.
СЖПРОБЕЛЫ с другими функциями
Использование функции СЖПРОБЕЛЫ вместе с другими функциями расширяет возможности пользователя. Логично, что она будет использоваться вместе с теми функциями, которые исследуют массивы и возвращают данные. В частности, это функции НАЙТИ, ЛЕВСИМВ, ПРАВСИМВ и др. На практике оказывается, что чаще всего СЖПРОБЕЛЫ используется вместе с ВПР.
Рассмотрим на примере той же таблицы. Задача: проставить цену напротив каждой позиции. Справа дана вспомогательная таблица, в которой прописана цена каждой игрушки. Теоретически мы можем вручную перепечатать нужные нам цифры, но раз мы в Excel, будем использовать ВПР.
Если мы протянем формулу до конца таблицы, увидим следующее:
Розовым цветом мы специально подсветили те позиции, которые написаны с лишними пробелами. Поэтому команда ВПР не выдала по ним результат. Чтобы исправить это, добавим к ВПР функцию СЖПРОБЕЛЫ и посмотрим, что получится.
Т.к. пробелы нам нужно удалить в искомом значении, команду СЖПРОБЕЛЫ поставим на первое место в синтаксисе ВПР. Формула немного видоизменилась, и если теперь мы протянем ее до низа таблицы, функция проставит нам все значения. Теперь все правильно.
Как еще можно удалить лишние пробелы в Excel?
Избавиться от лишних пробелов можно и без использования функции СЖПРОБЕЛЫ. Воспользуемся старым проверенным способом, который нам знаком еще из WORD – команда НАЙТИ-ЗАМЕНИТЬ.
Пример останется тот же самый. Выделяем столбец, в котором прописаны наименования игрушек и нажимаем CTRL+H. В появившемся окне напротив НАЙТИ проставляем пробел, а напротив ЗАМЕНИТЬ НА не пишем ничего.
Проблема такого способа в том, что он убирает все, в том числе между словами. Поэтому этот путь используют, преимущественно, для удаления лишних пробелов в составе чисел.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Функция СЖПРОБЕЛЫ() в EXCEL
Текст — текстовая строка, из которого удаляются пробелы.
Пример
Формула =СЖПРОБЕЛЫ(» Доход за первый квартал «) вернет » Доход за первый квартал «, т.е. удалены все пробелы за исключением одиночных пробелов между словами. Также удалены пробелы перед первым и после последнего слова.
Символ неразрывного пробела
Функция СЖПРОБЕЛЫ() предназначена для удаления из текста только знаков пробела (код символа 32). Страницы в интернете обычно используют и другой знак пробела, который называется знаком неразрывного пробела и имеет код 160 ( ) . Сама по себе функция СЖПРОБЕЛЫ() не удалит этот знак неразрывного пробела, это может формула =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A1;СИМВОЛ(160);СИМВОЛ(32))) Формула заменит все знаки неразрывного пробела (код символа 160) знаком пробела (код символа 32) с помощью функции ПОДСТАВИТЬ() , а затем удаление начального пробела и повторяющихся промежуточных пробелов из строки в ячейке А1 .
Кроме того, при вводе в программе MS WORD чисел с разделителями разряда, MS WORD автоматически заменяет пробел, который отделяет разряды, на символ неразрывного пробела. После копирования таких чисел в EXCEL через Буфер обмена они вставляются как текст. Как бороться с неразрывным пробелом в этом случае — читайте в этой статье .