Что обозначает появление в ячейке символов в excel
Приложение Microsoft Excel может отображать в ячейках символы #####, если ширины столбца недостаточно для отображения всего содержимого ячейки. В формулах, возвращающих дату и время как отрицательные значения, также может отображаться строка #####.
Чтобы расширить столбец и отобразить все содержимое ячейки, дважды щелкните правый край заголовка столбца или перетащите его на нужное расстояние.
Также попробуйте сделать следующее.
Если число содержит слишком много знаков после запятой, щелкните Главная > Уменьшить разрядность.
Если даты слишком длинные, щелкните Главная, затем — стрелку рядом с элементом Формат чисел и выберите Краткий формат даты.
Исправление отрицательных значений даты или времени
Если Excel отображает символы #####, потому что ячейка содержит отрицательное значение даты или времени, сделайте следующее.
При использовании системы дат 1900 убедитесь, что дата и время заданы положительными значениями.
Используйте формулу для вычитания дат или правильного сложить или вычесть время, чтобы избежать отрицательных значений даты или времени.
Для ячеек с отрицательными результатами в формате даты или времени выберите другой числовой формат.
Функция ПРАВСИМВ в Excel — примеры и советы.
В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части текстовой строки. Как и другие функции для работы с текстом, ПРАВСИМВ очень проста и понятна. Тем не менее, у нее есть несколько неочевидных применений, которые могут оказаться полезными в вашей работе.
Синтаксис.
ПРАВСИМВ возвращает указанное количество символов от конца текста.
ПРАВСИМВ(текст; [число_знаков])
Например, чтобы извлечь последние 6 символов из ячейки A2, запишите:
Результат может выглядеть примерно так:
В реальных таблицах ПРАВСИМВ редко используется в одиночку. В большинстве случаев вы будете использовать ее вместе с другими функциями Excel в составе более сложных формул. Об этом и поговорим далее.
Как получить подстроку после определенного символа.
Скажем, ячейка A2 содержит имя и фамилию, разделенные пробелом, и вы хотите перенести фамилию в другую ячейку. Просто используйте приведенную выше общую формулу:
Формула даст следующий результат:
Аналогичным образом вы можете получить подстроку, которая следует за любым другим символом, например за запятой, точкой с запятой, дефисом и т. д. Например, чтобы извлечь то, что записано после дефиса, используйте выражение:
Результат будет выглядеть примерно так:
Как извлечь подстроку после последнего вхождения разделителя
При работе со сложными текстовыми выражениями, которые содержат несколько вхождений одного и того же разделителя, вам часто может потребоваться получить текст справа от последнего появления разделителя. Чтобы упростить понимание, взгляните на следующие исходные данные и желаемый результат:
рис4
Ключом к поиску решения является определение позиции последнего разделителя (последнее вхождение двоеточия в этом примере). Для этого вам нужно будет выполнить несколько несложных операций:
Чтобы убедиться, что формула работает правильно, вы можете ввести ее в отдельную ячейку, и результатом будет 2, то есть количество двоеточий в ячейке A2.
Если вы знакомы с синтаксисом функции ПОДСТАВИТЬ, вы можете помнить, что у нее есть 4-й необязательный аргумент (номер вхождения), который позволяет заменять только конкретное появление указанного символа. И поскольку мы уже вычислили количество разделителей, просто впишите рассмотренное выше выражение в четвертый аргумент функции ПОДСТАВИТЬ:
Если вы работаете с большим набором данных, где разные ячейки могут содержать различные разделители, вы можете заключить приведенную выше формулу в функцию ЕСЛИОШИБКА, чтобы предотвратить возможные сообщения об ошибках:
Как показано на скриншоте ниже, формула работает отлично:
Замечание. В случае, если ячейка не содержит ни одного вхождения указанного разделителя, будет возвращена исходная строка целиком.
Как удалить первые N символов из ячейки
Помимо извлечения отрезка из конца строки, ПРАВСИМВ пригодится в ситуациях, когда вы хотите удалить определенное количество символов из её начала.
В наборе данных, использованном в предыдущем примере, вы можете удалить слово «ERROR», которое появляется в начале, и оставить только номер ошибки и описание. Для этого вычтите количество удаляемых символов из общей длины текста и передайте это число в аргумент количество_знаков:
В этом примере мы удаляем первые 6 символов (5 букв и двоеточие) из содержимого A2, поэтому наша формула выглядит следующим образом:
Может ли функция Excel ПРАВСИМВ вернуть число?
Например, чтобы извлечь последние 6 символов (почтовый индекс) из значения A2 и преобразовать их в число, используйте эту формулу:
На рисунке ниже показан результат. Обратите внимание на числа с выравниванием по правому краю в столбце B, в отличие от текстовых значений, которые выровнены по левому краю в столбце A:
Почему ПРАВСИМВ не работает с датами?
Предположим, у вас есть дата 9 августа 2020 года в ячейке A1. Если вы попытаетесь извлечь год с помощью формулы ПРАВСИМВ(A1,4), результатом будет 4052, что является последними четырьмя цифрами числа 44052, представляющего 9 августа 2020года в системе Excel.
На скриншоте показаны результаты:
Если ваши даты записаны в виде текста, что часто бывает при экспорте данных из других программ, то ничто не мешает вам использовать ПРАВСИМВ для извлечения последних нескольких символов, которые представляют определенную часть даты:
Теперь наша попытка извлечь год из даты вполне удачна.
Почему не работает? Причины и решения.
Это может быть связано с одной из следующих причин:
Вот как вы можете использовать ПРАВСИМВ в Excel.
Все описанные выше манипуляции с текстовыми значениями вы можете выполнить без формул при помощи специального инструмента работы с текстом, который является частью надстройки Ultimate Suite. Вы сможете без проблем избавиться от лишних пробелов и ненужных символов, поменять регистр букв, подсчитать символы и слова в ячейке или в диапазоне, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить его по отдельным ячейкам, извлечь отдельные слова, найти и удалить дубли слов.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге.
Описание формул для подсчета случаев появления текста, символов и слов в Excel
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
Эта статья содержит и описывает формулы, которые вычисляют следующее:
Дополнительная информация
Формула для подсчета количества вхождений текстовой строки в диапазоне
=SUM(LEN(range)-LEN(SUBSTITUTE(range,»text»,»»)))/LEN(«text»)
Где диапазон — диапазон ячеек, а «text» заменяется конкретной текстовой строкой, которую вы хотите посчитать.
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массива в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в качестве массива в Excel для Macintosh, нажмите COMMAND+RETURN.
Формула должна быть разделена на длину строки текста, поскольку сумма длины символа диапазона уменьшается кратно каждому вхождению текстовой строки. Эта формула может заменить все более поздние формулы в этой статье, кроме формулы для подсчета количества слов в ячейке.
Пример 1. Подсчет количества вхождений текстовой строки в диапазоне
Запустите Excel, а затем откройте новую книгу
Введите следующие сведения на Листе 1:
Значение ячейки A8 составляет 4, потому что текст «яблоко» появляется четыре раза в диапазоне.
Формула для подсчета количества происшествий одного символа в одной ячейке
=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,»a»,»»))
Где cell_ref ссылка на ячейку, и «а» заменяется символом, который вы хотите посчитать.
Эта формула не должна быть введена в качестве массивной формулы.
Пример 2. Подсчет количества появлений символа в одной ячейке.
Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество появлений символа «p» в A7. Введите следующую формулу в ячейку A9:
Значение ячейки A9 составляет 3, потому что символ «p» появляется три раза в A7.
Формула для подсчета количества появлений одного символа в диапазоне
=SUM(LEN(range)-LEN(SUBSTITUTE(range,»a»,»»)))
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.
Пример 3. Подсчет количества появлений символа в диапазоне
Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество символа «p» в A2:A7. В ячейку А10 введите следующую формулу:
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.
Значение ячейки A10 составляет 11, потому что символ «p» появляется 11 раз в A2:A7.
Формула для подсчета количества слов, разделенных символом в ячейке
=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,»»))+1)
Где cell_ref – это ссылка на ячейку, а char – это символ, разделяющий слова.
В приведенной выше формуле нет пробелов; несколько строк используются только для того, чтобы вписать формулу в этот документ. Не добавляйте пробелы при вводе формулы в ячейку. Эта формула не должна быть введена в качестве массивной формулы.
Пример 4. Подсчет количества слов, разделенных пространством в ячейке
Чтобы подсчитать количество слов в ячейке, где слова разделены пробелом, выполните следующие действия:
Запустите Excel, а затем откройте новую книгу
Введите следующие сведения на Листе 1:
Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенных пробелами. Если слова разделены несколькими пробелами или если слова начинаются или заканчиваются пробелами, это не имеет значения. Функция TRIM удаляет дополнительные пробелы и пробелы в начале и в конце в тексте ячейки.
В Excel можно также использовать макрос для подсчета количества случаев появления определенного символа в ячейке или диапазоне ячеек.
Ссылки
Дополнительные сведения о подсчете количества символов см. в следующей статье базы знаний Майкрософт:
89794 Как использовать Visual Basic для приложений для подсчета случаев появления символа в выделении в Excel
ЛЕВСИМВ, ЛЕВБ (функции ЛЕВСИМВ, ЛЕВБ)
В этой статье описаны синтаксис и использование функций ЛЕВСИМВ и ЛЕВБ в Microsoft Excel.
Описание
Функция ЛЕВСИМВ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа символов.
Функция ЛЕВБ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа байтов.
Эти функции могут быть доступны не на всех языках.
Функция ЛЕВБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой двухбайтовой кодировки. В противном случае функция ЛЕВБ работает так же, как функция ЛЕВСИМВ, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы этих функций описаны ниже.
Текст Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.
Число_знаков Необязательный. Количество символов, извлекаемых функцией ЛЕВСИМВ.
«Число_знаков» должно быть больше нуля или равно ему.
Если «число_знаков» превышает длину текста, функция ЛЕВСИМВ возвращает весь текст.
Если значение «число_знаков» опущено, оно считается равным 1.
Число_байт Необязательный. Количество символов, извлекаемых функцией ЛЕВБ.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Подстановочные знаки в Excel: что это и как использовать
Подстановочные знаки в Excel представляют собой выражения, позволяющие найти или заменить часть символов, не имея представления о них.
Подстановочные символы в Excel
Система позволяет использовать следующие комбинации:
| Символ | Комментарий | Пример |
| * | Обозначает любое количество символов | Марке* В этом случае система найдет «Маркет», «Маркетинг», «Маркету» и тд |
| ? | Заменяет одну любую букву | ?ксель Система найдет «Эксель» и «Ексель» |
| [] | Соответствие заключенным символам | Сто[оу]л покажет: «стол» и «стул», но исключит «стал». |
| ! | Исключает символы или комбинации, заключенные в скобки | Сто[!оу]л покажет: «стал». но исключит «стол» и «стул». |
| — | Обозначает диапазон символов, например, букв или цифр (указывается только при использовании алфавитного порядка) | Комбинация 6 покажет все ячейки, где имеются числа от 1 до 6 |
| # | Заменяет любую цифру | Поиск по 3#3 покажет все числа, начиная от 303, 313 до 393. |
Примеры использования подстановочных символов в Эксель
Звездочка (*)
Поможет в создании конструкции «содержит».
Вопросительный знак (?)
Поможет найти слово или словосочетание, даже если есть неизвестные символы.
В случае, когда возникает необходимость найти соответствующие символы без использования их функций:
появление в ячейке excel последовательности символов означает что
Приложение Microsoft Excel может отображать в ячейках символы #####, если ширины столбца недостаточно для отображения всего содержимого ячейки. В формулах, возвращающих дату и время как отрицательные значения, также может отображаться строка #####.
Чтобы расширить столбец и отобразить все содержимое ячейки, дважды щелкните правый край заголовка столбца или перетащите его на нужное расстояние.
Также попробуйте сделать следующее.
Если число содержит слишком много знаков после запятой, щелкните Главная > Уменьшить разрядность.
Если даты слишком длинные, щелкните Главная, затем — стрелку рядом с элементом Формат чисел и выберите Краткий формат даты.
Исправление отрицательных значений даты или времени
Если Excel отображает символы #####, потому что ячейка содержит отрицательное значение даты или времени, сделайте следующее.
При использовании системы дат 1900 убедитесь, что дата и время заданы положительными значениями.
Используйте формулу для вычитания дат или правильного сложить или вычесть время, чтобы избежать отрицательных значений даты или времени.
Для ячеек с отрицательными результатами в формате даты или времени выберите другой числовой формат.
ЛЕВСИМВ, ЛЕВБ (функции ЛЕВСИМВ, ЛЕВБ)
В этой статье описаны синтаксис и использование функций ЛЕВСИМВ и ЛЕВБ в Microsoft Excel.
Описание
Функция ЛЕВСИМВ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа символов.
Функция ЛЕВБ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа байтов.
Эти функции могут быть доступны не на всех языках.
Функция ЛЕВБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой двухбайтовой кодировки. В противном случае функция ЛЕВБ работает так же, как функция ЛЕВСИМВ, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы этих функций описаны ниже.
Текст Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.
Число_знаков Необязательный. Количество символов, извлекаемых функцией ЛЕВСИМВ.
«Число_знаков» должно быть больше нуля или равно ему.
Если «число_знаков» превышает длину текста, функция ЛЕВСИМВ возвращает весь текст.
Если значение «число_знаков» опущено, оно считается равным 1.
Число_байт Необязательный. Количество символов, извлекаемых функцией ЛЕВБ.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Описание формул для подсчета случаев появления текста, символов и слов в Excel
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
Эта статья содержит и описывает формулы, которые вычисляют следующее:
Дополнительная информация
Формула для подсчета количества вхождений текстовой строки в диапазоне
=SUM(LEN(range)-LEN(SUBSTITUTE(range,»text»,»»)))/LEN(«text»)
Где диапазон — диапазон ячеек, а «text» заменяется конкретной текстовой строкой, которую вы хотите посчитать.
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массива в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в качестве массива в Excel для Macintosh, нажмите COMMAND+RETURN.
Формула должна быть разделена на длину строки текста, поскольку сумма длины символа диапазона уменьшается кратно каждому вхождению текстовой строки. Эта формула может заменить все более поздние формулы в этой статье, кроме формулы для подсчета количества слов в ячейке.
Пример 1. Подсчет количества вхождений текстовой строки в диапазоне
Запустите Excel, а затем откройте новую книгу
Введите следующие сведения на Листе 1:
Значение ячейки A8 составляет 4, потому что текст «яблоко» появляется четыре раза в диапазоне.
Формула для подсчета количества происшествий одного символа в одной ячейке
=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,»a»,»»))
Где cell_ref ссылка на ячейку, и «а» заменяется символом, который вы хотите посчитать.
Эта формула не должна быть введена в качестве массивной формулы.
Пример 2. Подсчет количества появлений символа в одной ячейке.
Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество появлений символа «p» в A7. Введите следующую формулу в ячейку A9:
Значение ячейки A9 составляет 3, потому что символ «p» появляется три раза в A7.
Формула для подсчета количества появлений одного символа в диапазоне
=SUM(LEN(range)-LEN(SUBSTITUTE(range,»a»,»»)))
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.
Пример 3. Подсчет количества появлений символа в диапазоне
Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество символа «p» в A2:A7. В ячейку А10 введите следующую формулу:
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.
Значение ячейки A10 составляет 11, потому что символ «p» появляется 11 раз в A2:A7.
Формула для подсчета количества слов, разделенных символом в ячейке
=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,»»))+1)
Где cell_ref – это ссылка на ячейку, а char – это символ, разделяющий слова.
В приведенной выше формуле нет пробелов; несколько строк используются только для того, чтобы вписать формулу в этот документ. Не добавляйте пробелы при вводе формулы в ячейку. Эта формула не должна быть введена в качестве массивной формулы.
Пример 4. Подсчет количества слов, разделенных пространством в ячейке
Чтобы подсчитать количество слов в ячейке, где слова разделены пробелом, выполните следующие действия:
Запустите Excel, а затем откройте новую книгу
Введите следующие сведения на Листе 1:
Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенных пробелами. Если слова разделены несколькими пробелами или если слова начинаются или заканчиваются пробелами, это не имеет значения. Функция TRIM удаляет дополнительные пробелы и пробелы в начале и в конце в тексте ячейки.
В Excel можно также использовать макрос для подсчета количества случаев появления определенного символа в ячейке или диапазоне ячеек.
Ссылки
Дополнительные сведения о подсчете количества символов см. в следующей статье базы знаний Майкрософт:
89794 Как использовать Visual Basic для приложений для подсчета случаев появления символа в выделении в Excel
ПРАВСИМВ, ПРАВБ (функции ПРАВСИМВ, ПРАВБ)
В этой статье описаны синтаксис формулы и использование функций ПРАВСИМВ и ПРАВБ в Microsoft Excel.
Описание
Функция ПРАВСИМВ возвращает последний символ или несколько последних символов текстовой строки на основе заданного числа символов.
Функция ПРАВБ возвращает последний символ или несколько последних символов текстовой строки на основе заданного числа байтов.
Эти функции могут быть доступны не на всех языках.
Функция ПРАВСИМВ предназначена для языков с однобайтовой кодировкой, а ПРАВБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.
Функция ПРАВСИМВ всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.
Функция ПРАВБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ПРАВБ считает каждый символ за один.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ПРАВСИМВ и ПРАВБ описаны ниже.
Текст Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.
Число_знаков Необязательный. Количество символов, извлекаемых функцией ПРАВСИМВ.
Значение «число_знаков» должно быть больше нуля или равно ему.
Если значение «число_знаков» превышает длину текста, функция ПРАВСИМВ возвращает весь текст.
Если значение «число_знаков» опущено, оно считается равным 1.
Число_байтов Необязательный. Количество символов, извлекаемых функцией ПРАВБ.
Num_bytes должен быть больше нуля или равен нулю.
Если num_bytes больше, чем длина текста, то right возвращает весь текст.
Если num_bytes опущен, предполагается, что это 1.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.