Работа с текстом в таблице excel

Для удобства работы с текстом в Excel существуют текстовые функции. Они облегчают обработку сразу сотен строк. Рассмотрим некоторые из них на примерах.

Примеры функции ТЕКСТ в Excel

Преобразует числа в текст. Синтаксис: значение (числовое или ссылка на ячейку с формулой, дающей в результате число); формат (для отображения числа в виде текста).

Самая полезная возможность функции ТЕКСТ – форматирование числовых данных для объединения с текстовыми данными. Без использования функции Excel «не понимает», как показывать числа, и преобразует их в базовый формат.

Покажем на примере. Допустим, нужно объединить текст в строках и числовые значения:

Использование амперсанда без функции ТЕКСТ дает «неадекватный» результат:

Excel вернул порядковый номер для даты и общий формат вместо денежного. Чтобы избежать подобного результата, применяется функция ТЕКСТ. Она форматирует значения по заданию пользователя.

Формула «для даты» теперь выглядит так:

Второй аргумент функции – формат. Где брать строку формата? Щелкаем правой кнопкой мыши по ячейке со значением. Нажимаем «Формат ячеек». В открывшемся окне выбираем «все форматы». Копируем нужный в строке «Тип». Вставляем скопированное значение в формулу.

Приведем еще пример, где может быть полезна данная функция. Добавим нули в начале числа. Если ввести вручную, Excel их удалит. Поэтому введем формулу:

Если нужно вернуть прежние числовые значения (без нулей), то используем оператор «—»:

Обратите внимание, что значения теперь отображаются в числовом формате.



Функция разделения текста в Excel

Отдельные текстовые функции и их комбинации позволяют распределить слова из одной ячейки в отдельные ячейки:

• ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
• ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
• ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо

При разделении текста в строке учитывается положение каждого знака. Пробелы показывают начало или конец искомого имени.

Распределим с помощью функций имя, фамилию и отчество в разные столбцы.

В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

Формула для извлечения фамилии:

С помощью функции ПОИСК Excel определяет количество знаков для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Затем отнимается количество знаков до первого пробела (найденное ПОИСКом).

Вторая строка содержит имя, отчество и фамилию. Для имени используем такую же формулу:

Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(» «;A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.

Часть формулы – ПОИСК(» «;A3;ПОИСК(» «;A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.

Далее из общей длины строки отнимается количество знаков с начала строки до второго пробела. Результат – число символов справа, которые нужно вернуть.

Формула «для отчества» строится по тем же принципам:

Функция объединения текста в Excel

Для объединения значений из нескольких ячеек в одну строку используется оператор амперсанд (&) или функция СЦЕПИТЬ.

Например, значения расположены в разных столбцах (ячейках):

Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова — &. И так последовательно соединяем ячейки с текстом и пробелы.

Получаем в одной ячейке объединенные значения:

Использование функции СЦЕПИТЬ:

С помощью кавычек в формуле можно добавить в конечное выражение любой знак или текст.

Функция ПОИСК текста в Excel

Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например:

Функция ПОИСК вернула позицию 10, т.к. слово «Захар» начинается с десятого символа в строке. Где это может пригодиться?

Функция ПОИСК определяет положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Либо можно заменить найденный текст посредством функции ЗАМЕНИТЬ.

Скачать пример функции для работы с текстом в Excel

Читайте так же: как перевести число и сумму прописью.

Синтаксис функции ПОИСК:

• «искомый текст» — что нужно найти;
• «просматриваемый текст» — где искать;
• «начальная позиция» — с какой позиции начинать искать (по умолчанию – 1).

Если нужно учитывать регистр, используется функция НАЙТИ.

Текстовые функции Excel в примерах

​Смотрите также​ автозаполнения и тянем​ несколько вариантов изображения​ .​Function и Sub: В​ Диаграмма-спидометр, Диаграмма-термометр, Диаграмма​ СМЕЩ, «Левый” поиск,​ доступ: Узнайте, как​ вставки, Строка состояния,​Текстовый​текст​ допустимы символы «М»​ (например:​ запятой (;) и​​ части.​​ на «2013».​

СЦЕПИТЬ

​ А1 и B1​Excel предлагает большое количество​ в сторону.​ значений.​Чтобы прослушать речи, относящуюся​​ Visual Basic for​​ Ганта, Диаграмма Парето.​ Двумерный поиск, Поиск​ можно отправить данные​

СТРОЧН

​ Быстрые операции.​.​не является полезной,​ и «м», «Д»​=D4*1​ символа @. В​​=ТЕКСТ(СЕГОДНЯ();​​Заменяет первые пять символов​ абсолютно одинаково, но​ функций, с помощью​

ПРОПИСН

​Таким же способом можно​​Отредактируем значение ячеек:​​ к тексту в​ Applications функция (Function)​Сводные таблицы: Сводные таблицы​ максимального значения, Функция​ Excel в документ​​Этот раздел даёт базовые​​Минда советует…​ так как она​

ПРОПНАЧ

​ и «д», «Г»​​) или воспользоваться двойным​​ примере ниже выделен​»ДД.ММ.ГГ»​ текстовой строки, т.е.​ это не так.​

​ которых можно обрабатывать​ копировать строки.​Щелкнем по ячейке со​ группу ячеек, сделайте​ может возвращать значение,​

ДЛСТР

​ – один из​ ДВССЫЛ.​ Word или в​ понятия по работе​Если вы предоставляете доступ​​ позволяет принудительно отформатировать​​ и «г».​

ЛЕВСИМВ и ПРАВСИМВ

​ унарным оператором (—),​​ и скопирован только​​)​​ слово «Excel», на​​ В ячейке А1​ текст. Область применения​Удалим столбец: выделим его​ словом левой кнопкой​ следующее:​

ПСТР

​ в то время​​ самых мощных инструментов​​Финансовые функции: Этот раздел​ другие файлы. Кроме​ в Excel.​ к файлам и​

СОВПАД

​ значения в Excel​​Минда советует…​​ например:​ код ДД.ММ.ГГГГ.​Сегодняшняя дата в формате​ «Word».​ мы намеренно поставили​ текстовых функций не​ – правой кнопкой​ мыши и введем​Выберите группу ячеек, который​ как процедура (Sub)​ Excel. Сводная таблица​

​ рассказывает о наиболее​ этого, из множества​Лента меню: Когда Вы​ отчетам Excel пользователям​ с помощью​Если вы предоставляете доступ​=—D4​

СЖПРОБЕЛЫ

​Нажмите клавиши​ ДД/ММ/ГГ, например: 14.03.12​Вот и все! Мы​

​ лишний пробел в​ ограничивается исключительно текстом,​ мыши – «Удалить».​ число, например. Нажимаем​ вы хотите прослушать​ – не может.​ позволит выделить суть​ популярных финансовых функциях​ приведенных примеров вы​ запускаете Excel, на​ из разных стран,​кода форматирования​ к файлам и​.​CTRL+C​​=ТЕКСТ(СЕГОДНЯ();​​ познакомились с 15-ю​​ конце слова​​ они также могут​ Или нажав комбинацию​

​ ВВОД. Слово удаляется,​​ как звуковым речь.​​Объект Application: Главным в​ из огромного набора​ Excel.​ узнаете, как предоставить​ Ленте меню открывается​

​ вам может потребоваться​​, например​​ отчетам Excel пользователям​В Excel группы разрядов​, чтобы скопировать код​»ДДДД»​ текстовыми функциями Microsoft​Excel​ быть использованы с​ горячих клавиш: CTRL+»-«(минус).​ а число остается.​Выберите способ проговаривания данных,​ иерархии объектов является​ данных.​​Примеры: Кредиты различной длительности,​​ общий доступ к​

ПОВТОР

​ вкладка Главная. Узнайте,​​ предоставить им отчет​​»мм/дд/гг»​ из разных стран,​ разделяются пробелом, если​ формата, а затем —​)​ Excel и посмотрели​

НАЙТИ

​. В итоге функция​​ ячейками, содержащими числа.​​Чтобы вставить столбец, выделяем​Чтобы прежнее значение осталось,​ щелкнув​ сам Excel. Мы​Таблицы: Таблицы нужны для​ Инвестиции и аннуитет,​

​ книге Excel.​ как сворачивать и​

​ на их языке.​для форматирования даты.​ вам может потребоваться​ код формата содержит​ кнопку​Сегодняшний день недели, например:​ их действие на​​СОВПАД​​ В рамках данного​ соседний справа (столбец​ просто изменилось, нужно​По строкам​

ПОИСК

​ называем его объект​​ того, чтобы выполнять​​ Расчёт сложных процентов,​Примеры: Лист Excel в​​ настраивать Ленту.​​ В Microsoft Office​В следующем примере показано,​ предоставить им отчет​​ пробел, окруженный знаками​​Отмена​ понедельник​

ПОДСТАВИТЬ

​ простых примерах. Надеюсь,​возвратила нам значение​ урока мы на​ всегда вставляется слева),​​ щелкнуть по ячейке​​или​ Application. Объект Application​ анализ имеющихся данных​ График погашения кредита,​

​ Word, Сохранить в​Примеры: Панель быстрого доступа,​ MVP у Mynda​

​ что происходит при​ на их языке.​

​ номера (#) или​, чтобы закрыть диалоговое​

ЗАМЕНИТЬ

​=ТЕКСТ(ТДАТА();​ что данный урок​ ЛОЖЬ.​ примерах рассмотрим 15​ нажимаем правую кнопку​​ два раза. Замигает​​По столбцам​ открывает доступ ко​ быстро и легко.​ Амортизация.​

​ PDF, Общий доступ​ Вкладка Разработчик.​ Treacy) есть отличное​ попытке присоединиться к​ В Microsoft Office​ нулями. Например, если​ окно​»ЧЧ:ММ»​

​ пришелся Вам как​Применив функцию​ наиболее распространенных функций​ мыши – «Вставить»​

​ курсор. Меняем значение:​.​ многим параметрам, связанным​Анализ «Что-если»: Анализ «Что-если»​Статистические функции: Здесь Вы​ к книге, SkyDrive,​Рабочая книга: Книгой называют​ решение в этой​ тексту и числу,​ MVP у Mynda​ используется код формата​Формат ячеек​)​ раз кстати, и​СЖПРОБЕЛЫ​

​ Excel из категории​

office-guru.ru

Функция ТЕКСТ

​ — «Столбец». Комбинация:​​ удаляем часть текста,​Нажмите кнопку​ с самим приложением​ в Excel позволяет​ найдёте обзор самых​ Excel Online, Импорт​ файл Excel. Когда​ статье, которое отображается​ не используя функцию​ Treacy) есть отличное​»# ;»​.​Текущее время, например: 13:29​ Вы получили от​к значению ячейки​Текстовые​ CTRL+SHIFT+»=»​ добавляем.​Проговорить ячейки​ Excel.​

​ подставлять различные значения​​ полезных статистических функций​​ данных из Access,​ Вы запускаете Excel,​ в разных языках​​текст​​ решение в этой​, число 12200000 отображается​Теперь осталось нажать клавиши​=ТЕКСТ(0,285;​ него хотя бы​ А1, мы удалим​

​.​​Чтобы вставить строку, выделяем​Отредактировать значения можно и​.​Элементы управления ActiveX: Научитесь​ (сценарии) в формулы.​ Excel.​ Microsoft Query, Импорт​ автоматически создаётся пустая​ . В нем​. В этом​ статье, которое отображается​ как 12 200 000.​CTRL+V​»0,0 %»​ малость полезной информации.​

Технические сведения

​ из него все​

​Для объединения содержимого ячеек​​ соседнюю снизу. Комбинация​

​ через строку формул.​​Чтобы исправить ошибку, нажмите​​ создавать элементы управления​

Общие сведения

​)​ Всего доброго и​

• ​ лишние пробелы и​ в Excel, наряду​ клавиш: SHIFT+ПРОБЕЛ чтобы​

​ Выделяем ячейку, ставим​ кнопку​ ActiveX, такие как​ Excel есть инструмент,​ нули, Случайные числа,​ файлов, XML.​Примеры: Сохраняем в формате​

​ мыши внесите необходимые​​Userform: В этой главе​ вычислений для поиска​Округление: В этом разделе​ можно будет открыть​Рабочий лист: Лист –​Преобразование чисел из текстового​ строки, пробела («»)​​Способ отображения времени можно​​»# ;,0 «​​ код формата в​​Дробный формат, например: 4 1/3​ Мы стараемся как можно​ данным, которые импортируются​. Она последовательно объединяет​​ всегда вставляется сверху).​​Для удаления значения ячейки​​ изменения.​​ Вы узнаете, как​

Скачивание образцов

​ наилучших решений для​ описаны три самых​ этот файл.​ это набор ячеек,​ формата в числовой​ и значения с​ изменить с помощью​, число 12200000 отображается​ кавычки («код формата»),​=СЖПРОБЕЛЫ(ТЕКСТ(0,34;​ оперативнее обеспечивать вас​ в рабочие листы​ значения указанных ячеек​

Другие доступные коды форматов

​​ используется кнопка Delete.​Нажмите кнопку​​ создавать пользовательские формы​​ задач оптимизации всех​

1. ​ популярных функции для​​Примеры: Защита книги, Защита​​ в которых Вы​ ​Функции Excel (по категориям)​​= a2 & «»​ сочетаний символов «Ч»​​ в Excel как​​ иначе в Excel​

2. ​»# ?/?»​​ актуальными справочными материалами​​ Excel из внешних​

3. ​ в одной строке.​​Чтобы программа воспринимала вводимую​​Чтобы переместить ячейку со​

4. ​Проговорить ячейки​ в VBA.​​ видов.​​ округления чисел в​ листа, Блокировка ячеек,​ храните и обрабатываете​​Ищите примеры работы в​​ & B2​ (для часов), «М»​ 12 200,0.​ появится сообщение об​))​ на вашем языке.​

5. ​ источников. Такие данные​​Если в Excel необходимо​​ в ячейку информацию​ значением, выделяем ее,​чтобы продолжить проговаривание,​​Автор: Антон Андронов​​Пакет анализа: Пакет анализа​ Excel. Это функции​​ Книга только для​​ данные. По умолчанию​

6. ​ Excel? Хотите найти​​».​​ (для минут) и​Примечания:​ ошибке.​​Дробный формат, например: 1/3​​ Эта страница переведена​ очень часто содержат​ сделать все буквы​ как формулу, ставим​ нажимаем кнопку с​ снова.​

​Примечание:​ – это надстройка​ ОКРУГЛ, ОКРУГЛВВЕРХ и​ чтения, Пометить как​

Коды форматов по категориям

​ каждая рабочая книга​ понятную инструкцию, которая​Вы видите, что значение​ «С» (для секунд).​ ​​»Ячейки» > «Число»​​ Обратите внимание: функция​​ автоматически, поэтому ее​​ лишние пробелы и​​ строчными, т.е. преобразовать​​ знак «=». Например,​ ножницами («вырезать»). Или​​Совет:​​Мы стараемся как​

​ Excel, которая предоставляет​ ОКРУГЛВНИЗ.​

• ​ окончательную.​
• ​ Excel содержит три​
• ​ позволит в совершенстве​
• ​ даты, взятое из​ Кроме того, для​
• ​Разделитель групп разрядов зависит​
• ​ > «Другое» для​
• ​ СЖПРОБЕЛЫ используется для​
• ​ текст может содержать​
• ​ различные непечатаемые символы.​
• ​ их в нижний​

​ = (2+3)*5. После​ жмем комбинацию CTRL+X.​

​ Можно выбрать определенный диапазон​ можно оперативнее обеспечивать​ инструменты для анализа​Примеры: Отбрасываем десятичные знаки,​Узнайте, как функции в​ листа (в Excel​ овладеть великим множеством​ ячейки B2, не​ представления времени в​ от региональных параметров.​ получения строк формата.»​ удаления начального пробела​ неточности и грамматические​ Чтобы удалить все​ регистр, на помощь​ нажатия «ВВОД» Excel​ Вокруг ячейки появляется​​ ячеек для чтения​​ вас актуальными справочными​ финансовых, статистических и​​ Ближайшее кратное, Четные​​ Excel помогают сэкономить​​ 2010 и более​​ инструментов Excel, причем​​ отформатировано. В следующем​​ 12-часовом формате можно​ Для России это​

​ />​ перед дробной частью.​ ошибки. Для нас​ непечатаемые символы из​ придет текстовая функция​ считает результат.​​ пунктирная линия. Выделенный​​ обратно в компьютера.​ материалами на вашем​ технических данных.​​ и нечетные.​​ время. Если Вы​ ранних версиях).​ быстро и легко?​ примере показано, как​ использовать символы «AM/PM».​ пробел, но в​

​В примерах ниже показано,​=ТЕКСТ(12200000;​ важно, чтобы эта​ текста, необходимо воспользоваться​СТРОЧН​Последовательность вычисления такая же,​​ фрагмент остается в​​ Нажмите кнопку​ языке. Эта страница​VBA (Visual Basic for​​Ошибки в формулах: Эта​​ только начинаете знакомство​

​Примеры: Масштаб, Разделяем лист,​ Предлагаем Вашему вниманию​ применить нужное форматирование​Если не указывать символы​ других странах и​ как применить различные​»0,00E+00″​​ статья была вам​​ функцией​. Она не заменяет​

​ как в математике.​ буфере обмена.​Проговорить ячейки​ переведена автоматически, поэтому​ Applications) – это​​ глава научит Вас​​ с функциями в​ Закрепление областей, Группировка​самоучитель по работе в​

​ с помощью функции​​ «AM/PM», время будет​

• ​ регионах может использоваться​ числовые форматы к​)​ полезна. Просим вас​ПЕЧСИМВ​ знаки, не являющиеся​Формула может содержать не​

• ​Ставим курсор в другом​не выбирая все​ ее текст может​ название языка программирования​

​ справляться с некоторыми​ Excel, то рекомендуем​ листов, Консолидация, Просмотр​ Excel на простых​ TEXT.​ отображаться в 24-часовом​ запятая или точка.​ значениям следующим способом:​Экспоненциальное представление, например: 1,22E+07​ уделить пару секунд​.​ буквами.​ только числовые значения,​ месте рабочего поля​ ячейки или Excel​ содержать неточности и​ для Microsoft Office.​ распространёнными ошибками в​ Вам сначала познакомиться​ нескольких листов, Проверка​ примерах​Вот обновленная формула:​ формате.​Разделитель групп разрядов можно​ открыть диалоговое окно​=ТЕКСТ(1234567898;​ и сообщить, помогла​Функция​

​Текстовая функция​ но и ссылки​ и нажимаем «Вставить»​ будет автоматически расширяться​​ грамматические ошибки. Для​​ В данном разделе​ ​ формулах Excel.​​ с главой Введение​ правописания.​. Здесь Вы найдёте​​ячейка C2:​​В функции ТЕКСТ коды​

​ применять в числовых,​​Формат ячеек​​»[)​​ ли она вам,​​ПОВТОР​ПРОПИСН​ на ячейки со​

​ или комбинацию CTRL+V.​​ выделения для включения​ нас важно, чтобы​ описаны возможности и​Примеры: ЕСЛИОШИБКА, ЕОШИБКА, Циклическая​ в формулы и​Форматирование ячеек: При форматировании​ ответы на самые​=A2&» «&ТЕКСТ(B2;»дд.мм.гггг»)​​ форматов используются без​​ денежных и финансовых​, выбрать пункт​Дополнительный формат (номер телефона),​ с помощью кнопок​

​повторяет текстовую строку​делает все буквы​ значениями. К примеру,​Таким же способом можно​ соседних ячеек, содержащих​ эта статья была​

​ примеры использования VBA​ ссылка, Зависимости формул,​ функции.​ ячеек в Excel,​ различные вопросы и​ — формат даты.​ учета регистра, поэтому​

​и скопировать нужный​=ТЕКСТ(1234;​ удобства также приводим​ Строка задается как​ их в верхний​ и В1 –​ сразу. На этот​Нажмите кнопку​ вас уделить пару​Создание макроса: При помощи​

​Формулы массива: Изучив эту​ всего в Excel​ внешний вид содержимого,​ на практике. Самоучитель​

​ сделать с помощью​ и «ч», «М»​ числовых (только с​код формата​»0000000″​ ссылку на оригинал​ первый аргумент функции,​ регистр. Так же,​

​ ссылки на ячейки.​ же лист, на​Проговорить при вводе​

​ секунд и сообщить,​ VBA Вы можете​ главу, Вы будете​ используются функции, которые​

​ не изменяя самого​ состоит из пяти​ функции​ и «м», «С»​ разделителем групп разрядов​в формулу с​)​ (на английском языке).​ а количество повторов​ как и​

​Чтобы скопировать формулу на​ другой лист, в​

• ​.​

• ​ помогла ли она​

• ​ автоматизировать задачи в​

• ​ понимать, что такое​

​ подсчитывают количество и​ значения.​ разделов:​текст​ и «с», «AM/PM»​ и десятичными знаками),​ функцией​Добавление нулей в начале,​С помощью функции​ как второй.​​СТРОЧН​​ другие ячейки, необходимо​​ другую книгу.​​Ввод данных в любую​

Типичный сценарий

​ вам, с помощью​​ Excel, записывая так​​ формулы массива в​ сумму. Вы можете​Примеры: Десятичные знаки, Денежный​Введение​, поэтому необходимо​ и «am/pm».​ денежных и финансовых​ТЕКСТ​ например: 0001234​ТЕКСТ​Текстовая функция​, не заменяет знаки,​ «зацепить» маркер автозаполнения​Чтобы переместить несколько ячеек,​ ячейку. Нажмите клавишу​ кнопок внизу страницы.​ называемые макросы. В​ Excel. Одноячеечные формулы​ посчитать количество и​ против финансового, Форматы​Основы​ использовать код Visual​Для отображения десятичных значений​ форматов. В денежном​.​=ТЕКСТ(123456;​​можно изменить представление​​НАЙТИ​ не являющиеся буквами.​ в ячейке с​ их нужно выделить:​ ВВОД и компьютер​​ Для удобства также​​ этом разделе Вы​​ массива позволяют выполнять​​ сумму значений, выбранных​

​ даты и времени,​Функции​ Basic для приложений​ можно использовать процентные​ формате можно добавить​​Выбор числового формата​​»##0° 00′ 00»»​ числа, применив к​​находит вхождение одной​​Текстовая функция​​ формулой и протянуть​​Ставим курсор в крайнюю​ будет проговаривания содержимого​ приводим ссылку на​​ научитесь создавать простой​ сразу серию вычислений​​ по одному или​

​ Дроби, Текст в​Анализ данных​ (VBA). Вот как​ (%) форматы.​ нужное обозначение денежной​ ​)​ нему форматирование с​

​ строки в другую​

• ​ПРОПНАЧ​​ вниз (в сторону​​ верхнюю ячейку слева.​

Вопросы и ответы

Как преобразовать числа в текст, например 123 в «сто двадцать три»?

​ ячейки.​ оригинал (на английском​ макрос.​​ в одной ячейке.​​ по нескольким критериям.​ число, Число в​VBA​ можно преобразовать числовое​Десятичные числа можно отображать​ единицы, и значения​Выбор числового формата​Пользовательский формат (широта или​кодами форматов​

Можно ли изменить регистр текста?

​ и возвращает положение​делает прописной первую​ – если копируем​Нажимаем Shift, удерживаем и​Примечание:​

Можно ли с помощью функции ТЕКСТ добавить новую строку (разрыв строки) в ячейке, как при нажатии клавиш ALT+ВВОД?

​ языке) .​MsgBox: MsgBox – это​Примеры: Подсчёт ошибок, Подсчёт​Примеры: Подсчёт вхождений текста,​ текст, Пользовательские числовые​Каждый раздел содержит несколько​ значение в английские​​ в виде дробей,​​ будут выровнены по​​Нули в начале​​ долгота), например: 12°​. Это полезно в​​ первого символа искомой​​ букву каждого слова,​ в ячейки строки).​​ с помощью стрелок​​ Если вы не отключил​Доступны различные команды преобразования​​ диалоговое окно в​​ уникальных значений, Подсчёт​ Подсчёт логических значений,​​ форматы, Формат по​​ глав. В каждой​ слова в Excel​ используя коды форматов​ нему. В финансовом​Разделитель групп разрядов.​ 34′ 56»​ ситуации, когда нужно​

​ фразы относительно начала​ а все остальные​​При копировании формулы с​​ на клавиатуре добиваемся​

Почему Excel преобразует введенные числа во что-то вроде «1,22E+07»?

​Проговорить введите​ текста в речь​ VBA, при помощи​ с критерием «ИЛИ»,​ Подсчёт пустых и​ образцу, Стили ячеек,​​ главе вы найдете​​ с помощью следующей​ вида «?/?».​ формате символ рубля​​Числовые, денежные и финансовые​​Примечание:​ отобразить числа в​ текста.​ преобразует в строчные.​ относительными ссылками на​ выделения всего диапазона.​скрыть панель инструментов​ в Excel.​​ которого можно показывать​​ Суммирование каждой n-ой​

Даты на разных языках

См. также

​Найти и выделить: В​ основные понятия и​

​Да, вы можете использовать​ отображения значения в​

​ справа от значения​

support.office.com

300 примеров по Excel

​Даты​ функцию текст для​ объединить их с​ регистру…​ следует за знаком,​ константы в зависимости​ на его имя​, компьютер по-прежнему проговаривания​​  Функции преобразования текста​ Вашей программы.​ чисел, Суммирование диапазона​​Логические функции: Узнайте, как​ этой главе Вы​ положения, и несколько​ функции ПРОПИСН, СТРОЧН​ виде десятичного числа​ (если выбрать обозначение​Значения времени​

• ​ изменения форматирования, это​
• ​ текстом или символами.​
• ​… и может начинать​
• ​ отличным от буквы,​
• ​ от адреса текущей​

​ (латинскую букву). Для​ каждой ячейки после​ в речь Windows​Объекты Workbook и Worksheet:​ с ошибками, Суммирование​ пользоваться логическими функциями​ научитесь использовать два​ простых и понятных​ и ПРОПНАЧ. Например,​ от 1 до​ доллара США, то​Проценты​ не единственный способ.​Примечание:​ просмотр текста с​

​ также преобразуется в​ ячейки (столбца).​ выделения строки –​ ввода, можно сделать.​ можно будет использовать​ Узнайте больше об​ с критерием «ИЛИ»,​ Excel, такими как​ очень полезных инструмента​

Введение

​ примеров. Хотите узнать​ формула =ПРОПИСН(«привет») возвращает​ 10, умноженного на​

​ эти символы будут​Дроби​ Чтобы изменить формат​ Функция ТЕКСТ преобразует числа​ указанной позиции. На​ верхний регистр.​Чтобы сделать ссылку абсолютной​ на цифру.​ Нажмите кнопку​ в других программах​ объектах Workbook и​

1. ​ Поиск в двух​ ЕСЛИ, И, ИЛИ.​ Excel: Найти и​ об Excel намного​ результат «ПРИВЕТ».​ 10 в некоторой​ выровнены по левому​Экспоненциальное представление​

   ​ без формулы, нажмите​ в текст, что​ рисунке ниже формула​В Excel Вы можете​ (постоянной) и запретить​Для изменения размеров строк​

2. ​Проговорить на введите​ Office.​ Worksheet в VBA.​ столбцах, Наиболее часто​Примеры: Вложенные функции ЕСЛИ,​ заменить и Перейти.​

   ​ больше? Немедленно приступайте​Да, но это займет​ степени. Этот формат​ краю ячеек, а​Дополнительные форматы​

Основы

​ клавиши​ может затруднить их​ начинает просмотр с​

1. ​ подсчитать количество знаков,​ изменения относительно нового​ или столбцов передвигаем​, чтобы отключить​Для Office см. статью​Объект Range: Объект Range​

   ​ встречающееся слово, Система​ Игра «Бросить кости».​

2. ​Примеры: Особенности инструмента «Найти»,​ к изучению Microsoft​ несколько этапов. Сначала​ часто используется для​ значения — по правому).​

   ​Почему программа Excel удаляет​CTRL + 1​ использование в дальнейших​ четвертого символа, т.е.​

3. ​ содержащихся в текстовой​ адреса, ставится знак​ границы (курсор в​ его.​ Использование функции преобразования​ – это ячейка​ линейных уравнений.​Ссылки на ячейки: Ссылка​ Удаляем пустые строки,​ Excel вместе с​

   ​ выделите одну или​ краткого отображения больших​ Обратите внимание на​ нули в начале?​(или _з0з_​

4. ​ вычислениях. Рекомендуем сохранить​ c буквы «​ строке, для этого​ доллара ($).​ этом случае принимает​Microsoft Excel – чрезвычайно​

   ​ текста в речь.​ (или ячейки) Вашего​Эта глава рассказывает о​ на ячейку –​ Отличия по строкам,​ Офис-Гуру! С нами​ несколько ячеек, которые​ чисел.​ разницу между кодами​

5. ​В Excel обрабатываются Поиск​+ 1​ исходное значение в​r​ воспользуйтесь функцией​В каждой ячейке столбца​

   ​ вид крестика, поперечная​ полезная программка в​Просмотреть прослушивать чтение текста​ листа. Это самый​ мощных инструментах, которые​

6. ​ это очень важный​ Копируем только видимые​ вам будет гораздо​ должны быть выполнены,​В Excel доступны четыре​ денежных и финансовых​ чисел, введенных в​на компьютере Mac),​ одной ячейке, а​«. Но даже в​

   ​ДЛСТР​ С второе слагаемое​ перекладина которого имеет​ разных областях. Готовая​

7. ​ вслух экранным дикторомв​ важный объект VBA.​ предлагает Excel для​ элемент Excel. Поймите​ ячейки, Строка поиска.​ проще!​

   ​ а затем с​ дополнительных формата:​ форматов: в финансовых​ ячейки, а не​ а затем выберите​

8. ​ функцию ТЕКСТ использовать​ этом случае положение​. Пробелы учитываются.​ в скобках –​

   ​ на концах стрелочки).​ таблица с возможностью​

9. ​ Windows 7.​Переменные: В этом разделе​ анализа данных.​ разницу между относительной,​Шаблоны: Вместо того, чтобы​Данный курс по Excel​

   ​ помощью​»Почтовый индекс» («00000»);​ форматах для отделения​ чисел, которые выглядят​ нужный формат в​ в другой. Затем,​ символа считается относительно​Текстовые функции​

10. ​ 3 (ссылка на​Чтобы значение поместилось в​ автозаполнения, быстрых расчетов​Рядом с панелью быстрого​ Вы научитесь объявлять,​Сортировка: В Excel Вы​ абсолютной и смешанной​ создавать рабочую книгу​ – это перевод​клавиш CTRL + 1​»Индекс + 4″ («00000-0000»);​

    ​ символа денежной единицы​ как текст, например​ диалоговом окне​ если потребуется создать​ начала просматриваемого текста.​ЛЕВСИМВ​ ячейку В1 постоянна,​ ячейке, столбец можно​ и вычислений, построения​

11. ​ доступа нажмите кнопку​ инициализировать и отображать​ можете сортировать по​ ссылками, и Вы​ Excel с чистого​

    ​ очень популярного англоязычного​откройте диалоговое окно​»Номер телефона» («[​ от значения используется​ номера частей или​

Функции

​Формат ячеек​ другие формулы, всегда​Текстовая функция​и​ неизменна).​ расширить автоматически: щелкнуть​ графиков, диаграмм, создания​Настройка панели быстрого доступа​ переменную в VBA.​ одному или нескольким​

1. ​ наверняка добьётесь успеха!​ листа, Вы можете​ сайта-самоучителя – excel-easy.com,​Формат > ячейки​»Табельный номер» («000-00-0000»).​ звездочка (*).​ единицы СКЛАДского учета.​>​ ссылайтесь на исходное​

   ​ПОИСК​ПРАВСИМВ​Значительно расширяют функционал программы​ по правой границе​ отчетов или анализов​

2. ​, а затем пункт​Выражение «If Then»: Используйте​ столбцам. Расположите данные​Примеры: Копируем точную формулу,​

   ​ создать рабочую книгу​ который посещают десятки​

3. ​, нажмите кнопку выравнивание​Дополнительные форматы зависят от​Чтобы получить код формата​ Чтобы сохранить начальные​число​ значение, а не​очень похожа на​возвращают заданное количество​

   ​ встроенные функции. Чтобы​ 2 раза.​ и т.д.​

4. ​Другие команды​ выражение «If Then»​ по убыванию или​ 3D ссылка, Создание​ на базе шаблона.​ тысяч человек ежедневно!​ и выберите​ региональных параметров. Если​ для определенной денежной​ нули, отформатируйте входной​

   ​.​ на результат функции​ функцию​ символов, начиная с​ вставить функцию, нужно​Чтобы сохранить ширину столбца,​

5. ​Инструменты табличного процессора могут​.​ в VBA, чтобы​ по возрастанию.Примеры: Сортировка​ внешней ссылки, Гиперссылки.​

   ​ Существует множество бесплатных​ Думаю, что и​элемент Управление текстом​ же дополнительные форматы​ единицы, сначала нажмите​ диапазон как текст,​Вы можете скачать пример​ ТЕКСТ.​НАЙТИ​

6. ​ начала или с​ нажать кнопку fx​ но увеличить высоту​ значительно облегчить труд​.​ выполнять строки кода,​ по цвету, Обратный​Дата и время: Чтобы​

   ​ шаблонов, которые так​ вас он не​> установите флажок​ недоступны для вашего​ клавиши​

7. ​ прежде чем вставлять​ книги со всеми​Синтаксис​, основное их различие​

   ​ конца строки. Пробел​ (или комбинацию клавиш​ строки, нажимаем на​ специалистов из многих​В списке​

8. ​ если встречается определённое​ список, Случайный список.​ ввести дату в​ и просятся, чтобы​

   ​ оставит равнодушными!​ переносить​ региона или не​CTRL+1​ или вводить значения.​

9. ​ примерами текстовых функций,​ТЕКСТ​ заключается в том,​ считается за символ.​ SHIFT+F3). Откроется окно​ ленте кнопок «Перенос​ отраслей. Представленная ниже​

   ​Выбрать команды из​ условие.​Фильтрация: Настройте фильтр для​

10. ​ Excel, используйте символы-разделители:​ их использовали.​Этот раздел предназначен для​текст​ подходят для ваших​

    ​(на компьютере Mac —​ Выделите столбец или​ которые вы найдете​

11. ​(значение; формат)​ что​Текстовая функция​ вида:​ текста».​ информация – азы​выберите пункт​Цикл: Циклы – это​

    ​ данных в Excel,​ слеш (/), тире​Примеры: Календарь, Бюджет, Планировщик​ пользователей, не знакомых​. Затем направьте функцию​ нужд, вы можете​+1​ диапазон, в котором​ в этой статье,​Аргументы функции​ПОИСК​ПСТР​

Анализ данных

​Чтобы не листать большой​Чтобы стало красивее, границу​ работы в Эксель​Все команды​

1. ​ мощнейшая из техник​ чтобы видеть только​ (-) или точку​ питания, Счет-фактура, Автоматизированный​ с Excel.​ «завершенный​ создать собственный формат,​) и выберите нужный​
2. ​ вы хотите поместить​ а также некоторые​ТЕКСТ​не чувствительна к​возвращает заданное количество​ список функций, нужно​ столбца Е немного​ для чайников. Освоив​.​ программирования. Цикл в​
3. ​ записи, удовлетворяющие определённому​ (.). Чтобы ввести​ счет-фактура, Шаблоны по​Microsoft Excel – одно​текст​ выбрав в диалоговом​ формат, а затем​ значения, а затем​ дополнительные. Вы можете​описаны ниже.​ регистру.​ символов, начиная с​ сначала выбрать категорию.​
4. ​ подвинем, текст выровняем​ данную статью, Вы​Прокрутите список вниз и​ VBA позволяет Вам​ критерию.Примеры: Числовые и​ время, используйте двоеточие​ умолчанию.​ из самых широко​», чтобы она​ окне​ в раскрывающемся списке​ нажмите​ подписаться на них​Имя аргумента​Заменяет определенный текст или​ указанной позиции. Пробел​
5. ​Когда функция выбрана, нажимаем​ по центру относительно​ приобретете базовые навыки,​ для каждой необходимой​ обрабатывать диапазон ячеек​ текстовые фильтры, Фильтры​ (:). Дату и​
6. ​Проверка данных: Используйте проверку​ используемых приложений в​ включала функцию ASCII​Формат ячеек​
7. ​Обозначение​клавиши CTRL + 1​ или создать собственные​Описание​
8. ​ символ на требуемое​ считается за символ.​ ОК. Откроется окно​ вертикали и горизонтали.​ с которых начинается​ команды, который вы​ снова и снова,​ по дате, Расширенный​ время можно вводить​
9. ​ данных в Excel​ истории. Сотни миллионов​char (10)​пункт​выберите символ.​, чтобы перейти в​

VBA

​ коды форматов функций​значение​ значение. В Excel​Функция​ «Аргументы функции».​Объединим несколько ячеек: выделим​ любая работа в​ хотите использовать, щелкните​

1. ​ написав для этого​ фильтр, Форма данных,​ в одну ячейку.​ и будьте уверены,​ людей во всём​там, где нужно​(все форматы)​После этого в разделе​
2. ​ диалоговом окне​ текста. Примеры загрузки​Числовое значение, которое нужно​ текстовую функцию​СОВПАД​Функции распознают и числовые​
3. ​ их и нажмем​ Excel.​ любой команде​ всего лишь несколько​
4. ​ Удаляем дубликаты, Структурирование​Примеры: Функция РАЗНДАТ, Будние​ что пользователи введут​ мире пользуются Microsoft​ вставить разрыв строки.​
5. ​.​Числовые форматы​Формат > ячейки​ текстовых функций в​
6. ​ преобразовать в текст.​ПОДСТАВИТЬ​позволяет сравнить две​ значения, и ссылки​ кнопку «Объединить и​Книга Excel состоит из​
7. ​Проговорить ячейки​ строк кода.​ данных.​ и рабочие дни,​ в ячейку только​ Excel. Excel умеет​ Возможно, вам потребуется​Функция​слева выберите пункт​
8. ​, а затем на​ Excel​формат​применяют, когда заранее​
9. ​ текстовые строки в​ на ячейки. Чтобы​ поместить в центре».​ листов. Лист –​и нажмите кнопку​Ошибки макросов: Этот раздел​Условное форматирование: Условное форматирование​
10. ​ Дней до дня​ подходящее значение.​ работать с данными​ настроить ширину столбца​
11. ​текст​(все форматы)​вкладке Число​Просмотреть другие доступные коды​
12. ​Текстовая строка, определяющая формат,​ известно какой текст​ Excel. Если они​ поставить в поле​В Excel доступна функция​ рабочая область в​Добавить​ научит справляться с​
13. ​ в Excel позволяет​ рождения, Табель, Последний​Примеры: Отбросить недопустимые даты,​ любого типа и​ в зависимости от​редко используется сама​и скопируйте код​
14. ​выберите​ форматов можно в​ который требуется применить​ необходимо заменить, а​ в точности совпадают,​ аргумента ссылку, нужно​ автозаполнения. Вводим в​ окне. Его элементы:​.​
15. ​ ошибками макросов в​ выделить ячейки определённым​ день месяца, Праздники,​ Ограничение бюджета, Предотвращение​ выполнять финансовые, математические​ того, как окончательный​
16. ​ по себе и​ формата вместе с​текст​ диалоговом окне​

​ к указанному значению.​

office-guru.ru

Преобразование текста в речь в приложении Excel

​ не его местоположение.​​ то возвращается значение​ щелкнуть по ячейке.​ ячейку А2 слово​Чтобы добавить значение в​Нажмите кнопку​ Excel.​ цветом в зависимости​ Квартал, День года.​ дублирования записей, Коды​ и статистические вычисления.​ результат будет выровнен.​ чаще всего используется​ обозначением денежной единицы.​. Теперь Excel сохранит​Формат ячеек​Самая простая функция ТЕКСТ​Приведенная ниже формула заменяет​ ИСТИНА, в противном​Excel распознает и другой​ «январь». Программа распознает​ ячейку, щелкаем по​

​ОК​Операции со строками: В​ от содержащихся в​

​Текстовые функции: Excel предлагает​​ продуктов, Выпадающий список,​Диапазон: Диапазон в Excel​В этом примере использована​ в сочетании с​Примечание:​

• ​ начальное значение 0.​.​ означает следующее:​

• ​ все вхождения слова​ случае – ЛОЖЬ.​ способ введения функции.​

Использование команд преобразования текста в речь в Excel

1. ​ формат даты –​ ней левой кнопкой​​.​​ этом разделе Вы​​ них значений.Примеры: Управление​​ огромное количество функций​​ Зависимые выпадающие списки.​

   

2. ​ – это набор​​ формула​​ другими функциями. Предположим,​​ Функция ТЕКСТ не поддерживает​​Если вы уже ввели​

3. ​Нажмите клавиши​=ТЕКСТ(значение, которое нужно отформатировать;​ «Excel» на «Word»:​ Данная текстовая функция​ Ставим в ячейку​​ остальные месяцы заполнит​​ мыши. Вводим с​​При необходимости использовать команду​​ найдёте сведения о​
   

4. ​ правилами, Гистограммы, Цветовые​​ для самых различных​​Сочетания клавиш: Сочетания клавиш​

5. ​ из двух или​=»Сегодня: «&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГ»)​ что вам нужно​ форматирование с помощью​ данные в Excel​

   

​CTRL+1​​ «код формата, который​Заменяет только первое вхождение​ учитывает регистр, но​ знак «=» и​ автоматически.​ клавиатуры текст или​​ преобразования текста в​​ самых важных функциях​​ шкалы, Наборы значков,​​ операций с текстовыми​​ позволяют увеличивать скорость​ более ячеек. В​​.​

Проговаривание данных листа

​ объединить текст и​​ цвета. Если скопировать​ и нули в​(на компьютере Mac —​

Проговаривание группы ячеек

​ требуется применить»)​ слова «Excel»:​ игнорирует различие в​ начинаем вводить название​

1. ​Цепляем правый нижний угол​ цифры. Жмем Enter.​ речь щелкните ее​

2. ​ для работы со​ Новое правило, Поиск​​ строками.​ ​ работы, используя клавиатуру​​ этой главе Вы​ ​Это экспоненциальное представление числа.​

3. ​ числовое значение, например​​ в диалоговом окне​ ​ начале удалены, их​

4. ​+1​Ниже приведены популярные примеры,​​Удаляет все пробелы из​ ​ форматировании.​ функции. Уже после​ ячейки со значением​Значения могут быть числовыми,​

5. ​ на панели быстрого​​ строковыми элементами в​ ​ дубликатов, Закрашивание чередующихся​Примеры: Разделение строки, Количество​

​ вместо мыши.​​ найдёте обзор некоторых​ Excel автоматически приводит​ «отчет напечатано: 03/14/12»,​ «Формат ячеек» код​​ можно снова добавить​ ​), чтобы открыть диалоговое​ которые вы можете​ текстовой строки:​Если регистр для Вас​ первых символов появится​ «январь» и тянем​

Проговаривание после каждого ввода данных в ячейку

1. ​ текстовыми, денежными, процентными​​ доступа.​ ​ VBA.​

2. ​ строк, Сравнение двух​ экземпляров текста, Количество​Примеры: Функциональные клавиши, Подсказки​ очень важных операций​ к такому виду​

​ или «доход за​​ формата, в котором​​ с помощью функции​ ​ окно​​ скопировать прямо в​​Заменяет символы, расположенные в​ не играет большой​ список возможных вариантов.​ по строке.​​ и т.д. Чтобы​ ​Примечание:​Дата и время: Научитесь​

support.office.com

Как научиться работать в Excel самостоятельно

​ списков, Конфликт правил,​ слов, Текст по​ клавиш.​ с диапазонами.​ числа длиной более​ неделю: $66 348,72″.​ используется цвет, например​ТЕКСТ​Формат ячеек​

​ Excel, чтобы поэкспериментировать​ заранее известном месте​ роли (так бывает​ Если навести курсор​Апробируем функцию автозаполнения на​ установить/сменить формат, щелкаем​ Команды преобразования текста в​ работать с датой​ Чеклист.​ столбцам, Верхний и​Печать: Эта глава научит​Примеры: Последовательность Фибоначчи, Пользовательские​

Инструкция по работе в Excel

​ 12 цифр, если к​ В Excel можно​ «# ##0,00 ₽;​. Для верхней ячейки​

​.​ самостоятельно. Обратите внимание:​ строки, на требуемое​ в большинстве случаев),​ на какой-либо из​ числовых значениях. Ставим​

​ по ячейке правой​ речь в Excel​ и временем в​Диаграммы: Простейшая диаграмма в​ нижний регистр, Удалить​ вас отправлять листы​ списки, Примечания, Скрытие​ ячейкам применен формат​ вводить текст вручную,​

​[Красный]​ диапазона со значениями​На вкладке​

​ коды форматов заключены​ значение. В Excel​ то можно применить​ них, раскроется подсказка.​ в ячейку А3​

​ кнопкой мыши, выбираем​ 2007 и 2003​ VBA.​ Excel может быть​

​ ненужные символы, Сравнение​

1. ​ Excel на печать​ строк и столбцов,​Общий​ но это не​# ##0,00 ₽», то​ используйте формулу​
2. ​Число​ в кавычки.​ текстовую функцию​ формулу, просто проверяющую​Дважды щелкаем по нужной​ «1», в А4​ «Формат ячеек». Или​
3. ​ можно использовать, наведя​События: События – это​ более красноречивой, чем​ текста, Функции НАЙТИ​ и задавать некоторые​ Пропускать пустые ячейки,​, и числа длиннее​

​ отменяет назначение приложения​ функция ТЕКСТ воспримет​

​=ТЕКСТ(значение;»00000″)​выберите нужный формат.​Формула​ЗАМЕНИТЬ​ равенство двух ячеек.​ функции – становится​ – «2». Выделяем​ жмем комбинацию горячих​ указатель мыши на​

​ действия, совершаемые пользователями,​ таблица полная чисел.​ и ПОИСК, Функции​ важные настройки при​

​ Транспонирование, Объединение и​ 15 цифр, если выбран​ Excel. К сожалению,​ его, но цвет​, где количество нулей​Выберите пункт​

​Описание​применяют, когда известно​

1. ​Удаляет из текста все​ доступным порядок заполнения​
2. ​ две ячейки, «цепляем»​ клавиш CTRL+1.​ пункт​ которые запускают выполнение​

​ Вы увидите: диаграммы​ ПОДСТАВИТЬ и ЗАМЕНИТЬ.​ печати.​ пересечение.​ формат​

​ если вы объединяете​ отображаться не будет.​ соответствует нужному числу​(все форматы)​=ТЕКСТ(1234,567;​ где располагается текст,​ лишние пробелы, кроме​

​ аргументов. Чтобы завершить​ мышью маркер автозаполнения​Для числовых форматов можно​Речь​ кода VBA.​

​ – это не​Функции поиска и ссылок:​Примеры: Режимы просмотра, Разрывы​Формулы и функции: Формула​Числовой​

​ текст и форматированные​Способ отображения дат можно​ символов. Затем скопируйте​.​»# ##0,00 ₽»​

​ при этом сам​ одиночных между словами.​ введение аргументов, нужно​ и тянем вниз.​

​ назначить количество десятичных​в меню​Массив: Массив – это​ сложно.Примеры: Гистограмма, График,​ Узнайте всё о​ страниц, Верхние и​ – это выражение,​

​. Если вы вводите​ числа, например даты,​ изменять, используя сочетания​ формулу и примените​

​Нужный код формата будет​)​ он не важен.​В случаях, когда наличие​ закрыть скобку и​Если мы выделим только​ знаков.​Сервис​

​ группа переменных. В​ Круговая диаграмма, Линейчатая​ функциях Excel для​ нижние колонтитулы, Номера​ которое вычисляет значение​

​ длинные цифровые строки,​ время, денежные единицы​ символов «Д» (для​ ее к остальной​ показан в поле​

​Денежный формат с разделителем​Формула в примере ниже​

​ лишнего пробела в​ нажать Enter.​ одну ячейку с​Примечание. Чтобы быстро установить​и выбрав команду​

​ VBA Вы можете​ диаграмма, Диаграмма с​ работы со ссылками​ страниц, Печать заголовков,​ ячейки. Функции –​ но не хотите,​ и т. д.,​

​ дня), «М» (для​ части диапазона.​Тип​ групп разрядов и​ заменяет 4 символа,​ конце или начале​Аргумент функции КОРЕНЬ –​ числом и протянем​

​ числовой формат для​

Как работать в Excel: формулы и функции для чайников

​Показывать панель преобразования текста​ сослаться на определённую​ областями, Точечная диаграмма,​ и массивами, таких​ Центрирование на странице,​ это предопределённые формулы,​ чтобы они отображались​

​ Excel не знает,​ месяца) и «Г»​

​Если по какой-либо причине​. В этом случае​ двумя разрядами дробной​ расположенные, начиная с​ строки сложно отследить,​ ссылка на ячейку​ ее вниз, то​ ячейки — нажмите​

​ в речь​ переменную (элемент) массива,​ Ряды данных, Оси,​ как ВПР, ГПР,​ Печать сетки и​ доступные в Excel.​ в таком виде,​ как они должны​

​ (для года).​ потребуется преобразовать текстовые​ выделите всё содержимое​ части, например: 1 234,57 ₽.​ седьмой позиции, на​ данная функция становится​

​ A1:​ это число «размножиться».​ комбинацию горячих клавиш​.​ используя для этого​

​ Лист диаграммы, Линия​ ПОИСКПОЗ, ИНДЕКС и​ заголовков строк/столбцов, Область​Примеры: Процентное изменение, Имена​ то сначала примените​ отображаться, и сбрасывает​

​В функции ТЕКСТ коды​ значения обратно в​ поля​ Обратите внимание: Excel​ значение «2013». Применительно​ просто незаменимой. На​ВВОД – программа нашла​

​Чтобы скопировать столбец на​ CTRL+SHIFT+1.​Примечание:​

​ имя массива и​ тренда, Предел погрешностей,​ ВЫБОР.​

​ печати.​ в формулах, Динамический​ к соответствующим ячейкам​ форматирование чисел. В​ форматов используются без​ числа, можно умножить​

​Тип​ округляет значение до​ к нашему примеру,​ рисунке ниже видно,​ квадратный корень из​ соседний, выделяем этот​Для форматов «Дата» и​ Необходимо настроить речи услышать​ числовой индекс элемента.​ Спарклайны, Комбинированная диаграмма,​Примеры: Налоговые ставки, Функция​

​Обмен данными и общий​ именованный диапазон, Параметры​ формат​ этом случае функция​ учета регистра, поэтому​ их на 1​, кроме точки с​

​ двух разрядов дробной​ формула заменит «2010»​ что содержимое ячеек​

​ числа 40.​ столбец, «цепляем» маркер​ «Время» Excel предлагает​

exceltable.com

​ звук на компьютере​

Краткое руководство по текстовым функциям

Введение

Использование строк является очень важной частью VBA. Есть много типов манипуляций, которые вы можете делать со строками. К ним относятся такие задачи, как:

• извлечение части строки
• сравнение строк
• преобразование чисел в текст
• форматирование даты для включения дня недели
• найти символ в строке
• удаление пробелов
• парсинг в массив
• и т. д.  

Хорошей новостью является то, что VBA содержит множество функций, которые помогут вам легко выполнять эти задачи.

Эта статья содержит подробное руководство по использованию строки в VBA. Он объясняет строки в простых терминах с понятными примерами кода. Изложение в статье поможет легко использовать ее в качестве краткого справочного руководства.

Если вы собираетесь использовать строки часто, я рекомендую вам прочитать первый раздел, так как он относится ко многим функциям. В противном случае вы можете прочитать по порядку или просто перейти в нужный раздел.

Прочитайте это в первую очередь!

Следующие два пункта очень важны при работе со строковыми функциями VBA.

Исходная строка не изменяется

Важно помнить, что строковые функции VBA не изменяют исходную строку. Они возвращают новую строку с изменениями, внесенными функцией. Если вы хотите изменить исходную строку, вы просто назначаете результат исходной строке. См. Раздел «Извлечение части строки» для примеров.

Как использовать Compare

Некоторые строковые функции, такие как StrComp (), Instr () и т.д. имеют необязательный параметр Compare. Он работает следующим образом:

vbTextCompare: верхний и нижний регистры считаются одинаковыми

vbBinaryCompare: верхний и нижний регистр считаются разными

Следующий код использует функцию сравнения строк StrComp () для демонстрации параметра Compare.

Sub Comp1()

    ' Печатает 0  : Строки совпадают
    Debug.Print StrComp("АБВ", "абв", vbTextCompare)
    ' Печатает -1 : Строки не совпадают
    Debug.Print StrComp("АБВ", "абв", vbBinaryCompare)

End Sub

Вы можете использовать параметр Option Compare вместо того, чтобы каждый раз использовать этот параметр. Опция сравнения устанавливается в верхней части модуля. Любая функция, которая использует параметр Compare, примет этот параметр по умолчанию. Два варианта использования Option Compare:

• Oпция Compare Text: делает vbTextCompare аргументом сравнения по умолчанию

Option Compare Text

Sub Comp2()
    ' Соответствие строк - использует vbCompareText в качестве 'аргумента сравнения
    Debug.Print StrComp("АБВ", "абв")
    Debug.Print StrComp("ГДЕ", "где")
End Sub

• Опция Compare Binary: делает vbBinaryCompare аргументом сравнения по умолчанию.

Option Compare Binary

Sub Comp2()
    ' Строки не совпадают - использует vbCompareBinary в качестве 'аргумента сравнения
    Debug.Print StrComp("АБВ", "абв")
    Debug.Print StrComp("ГДЕ", "где")
End Sub

Если Option Compare не используется, то по умолчанию используется Option Compare Binary.

Теперь, когда вы понимаете эти два важных момента о строке, мы можем продолжить и посмотреть на строковые функции индивидуально.

Добавление строк

Вы можете добавлять строки, используя оператор &. Следующий код показывает несколько примеров его использования.

Sub Dobavlenie()

    Debug.Print "АБВ" & "ГДЕ"
    Debug.Print "Иван" & " " & "Петров"
    Debug.Print "Длинный " & 22
    Debug.Print "Двойной " & 14.99
    Debug.Print "Дата " & #12/12/2015#

End Sub

В примере вы можете видеть, что различные типы, такие как даты и числа, автоматически преобразуются в строки. Вы можете увидеть оператор +, используемый для добавления строк. Разница в том, что этот оператор будет работать только со строковыми типами. Если вы попытаетесь использовать его с другим типом, вы получите ошибку.

 Это даст сообщение об ошибке: «Несоответствие типов»
    Debug.Print "Длинный " + 22

Если вы хотите сделать более сложное добавление строк, вы можете использовать функцию форматирования, описанную ниже.

Извлечение части строки

Функции, обсуждаемые в этом разделе, полезны при базовом извлечении из строки. Для чего-то более сложного можете посмотреть раздел, как легко извлечь любую строку без использования VBA InStr.

Функции Left, Right и Mid используются для извлечения частей строки. Это очень простые в использовании функции. Left читает символы слева, Right справа и Mid от указанной вами начальной точки.

Sub IspLeftRightMid()

    Dim sCustomer As String
    sCustomer = "Иван Васильевич Петров"

    Debug.Print Left(sCustomer, 4)  '  Печатает: Иван
    Debug.Print Right(sCustomer, 6) '  Печатает: Петров

    Debug.Print Left(sCustomer, 15)  '  Печатает: Иван Васильевич
    Debug.Print Right(sCustomer, 17)  '  Печатает: Васильевич Петров

    Debug.Print Mid(sCustomer, 1, 4) ' Печатает: Иван
    Debug.Print Mid(sCustomer, 6, 10) ' Печатает: Васильевич
    Debug.Print Mid(sCustomer, 17, 6) ' Печатает: Петров

End Sub

Как упоминалось в предыдущем разделе, строковые функции VBA не изменяют исходную строку. Вместо этого они возвращают результат в виде новой строки.

В следующем примере вы увидите, что строка Fullname не была изменена после использования функции Left.

Sub PrimerIspolzovaniyaLeft()

    Dim Fullname As String
    Fullname = "Иван Петров"

    Debug.Print "Имя: "; Left(Fullname, 4)
    ' Исходная строка не изменилась
    Debug.Print "Полное имя: "; Fullname

 End Sub

Если вы хотите изменить исходную строку, вы просто присваиваете ей возвращаемое значение функции.

Sub IzmenenieStroki()

    Dim name As String
    name = "Иван Петров"

    ' Присвойте возвращаемую строку переменной имени
    name = Left(name, 4)

    Debug.Print "Имя: "; name

 End Sub

Поиск в строке

InStr и InStrRev — это функции VBA, используемые для поиска текста в тексте. Если текст поиска найден, возвращается позиция (с начала строки проверки) текста поиска. Когда текст поиска не найден, возвращается ноль. Если какой-либо текст имеет значение null, возвращается значение null.

InStr Описание параметров

InStr() Start[Необязат], String1, String2, Compare[Необязат]

• Start [Необязательно — по умолчанию 1]: это число, указывающее начальную позицию поиска слева
• String1: текст, в котором будем искать
• String2: текст, который будем искать
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

Использование InStr и примеры

InStr возвращает первую позицию в тексте, где найден данный текст. Ниже приведены некоторые примеры его использования.

Sub PoiskTeksta()

    Dim name As String
    name = "Иван Петров"

    ' Возвращает 3 - позицию от первой 
    Debug.Print InStr(name, "а")
    ' Возвращает 10 - позиция первого "а", начиная с позиции 4
    Debug.Print InStr(4, name, "а")
    ' Возвращает 8
    Debug.Print InStr(name, "тр")
    ' Возвращает 6
    Debug.Print InStr(name, "Петров")
    ' Возвращает 0 - текст "ССС" не найдет
    Debug.Print InStr(name, "ССС")

End Sub

InStrRev Описание параметров

InStrRev() StringCheck, StringMatch, Start[Необязат], Compare[Необязат]

• StringCheck: текст, в котором будем искать
• StringMatch: Текст, который будем искать
• Start [Необязательно — по умолчанию -1]: это число, указывающее начальную позицию поиска справа
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

Использование InStrRev и примеры

Функция InStrRev такая же, как InStr, за исключением того, что она ищет с конца строки. Важно отметить, что возвращаемая позиция является позицией с самого начала. Поэтому, если существует только один экземпляр элемента поиска, InStr () и InStrRev () будут возвращать одно и то же значение.

В следующем коде показаны некоторые примеры использования InStrRev.

Sub IspInstrRev()

    Dim name As String
    name = "Иван Петров"

    ' Обе возвращают 1 - позицию, только И
    Debug.Print InStr(name, "И")
    Debug.Print InStrRev(name, "И")

    ' Возвращает 11 - вторую в
    Debug.Print InStrRev(name, "в")
    ' Возвращает 3 - первую в с позиции 9
    Debug.Print InStrRev(name, "в", 9)

    ' Returns 1
    Debug.Print InStrRev(name, "Иван")

End Sub

Функции InStr и InStrRev полезны при работе с базовым поиском текста. Однако, если вы собираетесь использовать их для извлечения текста из строки, они могут усложнить задачу. Я написал о гораздо лучшем способе сделать это в своей статье Как легко извлечь любой текст без использования VBA InStr.

Удаление пробелов

Функции Trim — это простые функции, которые удаляют пробелы в начале или конце строки.

Функции и примеры использования триммера Trim

• LTrim удаляет пробелы слева от строки
• RTrim удаляет пробелы справа от строки
• Trim удаляет пробелы слева и справа от строки

Sub TrimStr()

    Dim name As String
    name = "  Иван Петров  "

    ' Печатает "Иван Петров  "
    Debug.Print LTrim(name)
    ' Печатает "  Иван Петров"
    Debug.Print RTrim(name)
    ' Печатает "Иван Петров"
    Debug.Print Trim(name)

End Sub

Длина строки

Len — простая функция при использовании со строкой. Она просто возвращает количество символов, которое содержит строка. Если используется с числовым типом, таким как long, он вернет количество байтов.

Sub IspLen()

    Dim name As String
    name = "Иван Петров"

    ' Печатает 11
    Debug.Print Len("Иван Петров")
    ' Печатает 3
    Debug.Print Len("АБВ")

    ' Печатает 4 с Long - это размер 4 байта
    Dim total As Long
    Debug.Print Len(total)

End Sub

Перевернуть текст

StrReverse — еще одна простая в использовании функция. Он просто возвращает данную строку с обратными символами.

Sub RevStr()

    Dim s As String
    s = "Иван Петров"
    ' Печатает: вортеП навИ
    Debug.Print StrReverse(s)

End Sub

Сравнение

Функция StrComp используется для сравнения двух строк. Следующие подразделы описывают, как используется.

Описание параметров

StrComp()  String1, String2, Compare[Необязат]

• String1: первая строка для сравнения
• String2: вторая строка для сравнения
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

StrComp Возвращаемые значения

Использование и примеры

Ниже приведены некоторые примеры использования функции StrComp.

Sub IspStrComp()

   ' Возвращает  0
   Debug.Print StrComp("АБВ", "АБВ", vbTextCompare)
   ' Возвращает 1
   Debug.Print StrComp("АБВГ", "АБВ", vbTextCompare)
   ' Возвращает -1
   Debug.Print StrComp("АБВ", "АБВГ", vbTextCompare)
   ' Returns Null
   Debug.Print StrComp(Null, "АБВГ", vbTextCompare)

End Sub

Сравнение строк с использованием операторов

Вы также можете использовать знак равенства для сравнения строк. Разница между сравнением equals и функцией StrComp:

1. Знак равенства возвращает только true или false.
2. Вы не можете указать параметр Compare, используя знак равенства — он использует настройку «Option Compare».  

Ниже приведены некоторые примеры использования equals для сравнения строк.

Option Compare Text

Sub CompareIspEquals()

    ' Возвращает true
    Debug.Print "АБВ" = "АБВ"
    ' Возвращает true, потому что «Сравнить текст» установлен выше
    Debug.Print "АБВ" = "абв"
    ' Возвращает false
    Debug.Print "АБВГ" = "АБВ"
    ' Возвращает false
    Debug.Print "АБВ" = "АБВГ"
    ' Возвращает null
    Debug.Print Null = "АБВГ"

End Sub

Сравнение строк с использованием сопоставления с шаблоном

Сопоставление с шаблоном используется для определения того, имеет ли строка конкретный образец символов. Например, вы можете проверить, что номер клиента состоит из 3 цифр, за которыми следуют 3 алфавитных символа, или в строке есть буквы XX, за которыми следует любое количество символов.

Если строка соответствует шаблону, возвращаемое значение равно true, в противном случае — false.

Сопоставление с образцом аналогично функции формата VBA в том смысле, что его можно использовать практически безгранично. В этом разделе я приведу несколько примеров, которые объяснят, как это работает. Это должно охватывать наиболее распространенные виды использования.

Давайте посмотрим на базовый пример с использованием знаков. Возьмите следующую строку шаблона.

[abc][!def]?#X*

 Давайте посмотрим, как работает эта строка

[abc] — символ, который является или a, b или c
[! def] — символ, который не является d, e или f
? любой символ
# — любая цифра
X — символ X
* следуют ноль или более символов

 Поэтому следующая строка действительна
apY6X

а — один из символов a,b,c
p — не один из символов d, e или f
Y — любой символ
6 — это цифра
Х — это буква Х

В следующих примерах кода показаны результаты различных строк с этим шаблоном.

Sub Shabloni()

    ' ИСТИНА
    Debug.Print 1; "apY6X" Like "[abc][!def]?#X*"
    ' ИСТИНА - любая комбинация символов после x действительна
    Debug.Print 2; "apY6Xsf34FAD" Like "[abc][!def]?#X*"
    ' ЛОЖЬ - символ не из[abc]
    Debug.Print 3; "dpY6X" Like "[abc][!def]?#X*"
    ' ЛОЖЬ - 2-й символ e находится в [def]
    Debug.Print 4; "aeY6X" Like "[abc][!def]?#X*"
    ' ЛОЖЬ - A в позиции 4 не является цифрой
    Debug.Print 5; "apYAX" Like "[abc][!def]?#X*"
    ' ЛОЖЬ - символ в позиции 5 должен быть X
    Debug.Print 1; "apY6Z" Like "[abc][!def]?#X*"

End Sub

Реальный пример сопоставления с образцом

Чтобы увидеть реальный пример использования сопоставления с образцом, ознакомьтесь с Примером 3: Проверьте, допустимо ли имя файла.

Важное примечание о сопоставлении с образцом VBA

Оператор Like использует двоичное или текстовое сравнение на основе параметра Option Compare. Пожалуйста, смотрите раздел Сравнение для более подробной информации.

Заменить часть строки

Replace используется для замены текста в строке другим текстом. Он заменяет все экземпляры текста, найденные по умолчанию.

Replace описание параметров

Replace()  Expression, Find, Replace, Start[Необязат], Count[Необязат], Compare[Необязат]

• Expression: текст, в котором нужна замена символов
• Find: текст для замены в строке выражения
• Replace: строка для поиска замены текста поиска
• Start [Необязательно — по умолчанию 1]: начальная позиция в строке
• Count [Необязательно — по умолчанию -1]: количество замен. По умолчанию -1 означает все.
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

Использование и примеры

В следующем коде показаны некоторые примеры использования функции замены.

Sub PrimeriReplace()

    ' Заменяет все знаки вопроса (?) на точку с запятой (;)
    Debug.Print Replace("A?B?C?D?E", "?", ";")
    ' Заменить Петров на Иванов
    Debug.Print Replace("Евгений Петров,Артем Петров", "Петров", "Иванов")
    ' Заменить AX на AB
    Debug.Print Replace("ACD AXC BAX", "AX", "AB")

End Sub

На выходе:

A;B;C;D;E
Евгений Иванов,Артем Иванов
ACD ABC BAB

В следующих примерах мы используем необязательный параметр Count. Count определяет количество замен. Так, например, установка Count равной единице означает, что будет заменено только первое вхождение.

Sub ReplaceCount()

    ' Заменяет только первый знак вопроса
    Debug.Print Replace("A?B?C?D?E", "?", ";", Count:=1)
    ' Заменяет первые три знака вопроса
    Debug.Print Replace("A?B?C?D?E", "?", ";", Count:=3)

End Sub

На выходе:

A;B?C?D?E
A;B;C;D?E

Необязательный параметр Start позволяет вам вернуть часть строки. Позиция, которую вы указываете с помощью Start, — это место, откуда начинается возврат строки. Он не вернет ни одной части строки до этой позиции, независимо от того, была ли произведена замена или нет.

Sub ReplacePartial()

    ' Использовать оригинальную строку из позиции 4
    Debug.Print Replace("A?B?C?D?E", "?", ";", Start:=4)
    ' Используйте оригинальную строку из позиции 8
    Debug.Print Replace("AA?B?C?D?E", "?", ";", Start:=8)
    ' Элемент не заменен, но по-прежнему возвращаются только последние '2 символа
    Debug.Print Replace("ABCD", "X", "Y", Start:=3)

End Sub

На выходе:

;C;D;E
;E
CD

Иногда вы можете заменить только заглавные или строчные буквы. Вы можете использовать параметр Compare для этого. Он используется во многих строковых функциях. Для получения дополнительной информации об этом проверьте раздел сравнения.

Sub ReplaceCase()

    ' Заменить только заглавные А
    Debug.Print Replace("AaAa", "A", "X", Compare:=vbBinaryCompare)
    ' Заменить все А
    Debug.Print Replace("AaAa", "A", "X", Compare:=vbTextCompare)

End Sub

На выходе:

XaXa
XXXX

Многократные замены

Если вы хотите заменить несколько значений в строке, вы можете вкладывать вызовы. В следующем коде мы хотим заменить X и Y на A и B соответственно.

Sub ReplaceMulti()

    Dim newString As String

    ' Заменить А на Х
    newString = Replace("ABCD ABDN", "A", "X")
    ' Теперь замените B на Y в новой строке
    newString = Replace(newString, "B", "Y")

    Debug.Print newString

End Sub

В следующем примере мы изменим приведенный выше код для выполнения той же задачи. Мы будем использовать возвращаемое значение первой замены в качестве аргумента для второй замены.

Sub ReplaceMultiNested()

    Dim newString As String

    ' Заменить A на X, а B на Y
    newString = Replace(Replace("ABCD ABDN", "A", "X"), "B", "Y")

    Debug.Print newString

End Sub

Результатом обоих этих Subs является:
XYCD XYDN

Преобразовать типы в строку (базовый)

Этот раздел о преобразовании чисел в строку. Очень важным моментом здесь является то, что в большинстве случаев VBA автоматически конвертируется в строку для вас. Давайте посмотрим на некоторые примеры:

Sub AutoConverts()

    Dim s As String
    ' Автоматически преобразует число в строку
    s = 12.99
    Debug.Print s

    ' Автоматически преобразует несколько чисел в строку
    s = "ABC" & 6 & 12.99
    Debug.Print s

    ' Автоматически преобразует двойную переменную в строку
    Dim d As Double, l As Long
    d = 19.99
    l = 55
    s = "Значения: " & d & " " & l
    Debug.Print s

End Sub

Когда вы запустите приведенный выше код, вы увидите, что число было автоматически преобразовано в строки. Поэтому, когда вы присваиваете значение строке, VBA будет следить за преобразованием большую часть времени. В VBA есть функции преобразования, и в следующих подразделах мы рассмотрим причины их использования.

Явное преобразование

В некоторых случаях вы можете захотеть преобразовать элемент в строку без необходимости сначала помещать его в строковую переменную. В этом случае вы можете использовать функции Str или CStr. Оба принимают выражение как функцию, и это может быть любой тип, например long, double, data или boolean.

Давайте посмотрим на простой пример. Представьте, что вы читаете список значений из разных типов ячеек в коллекцию. Вы можете использовать функции Str / CStr, чтобы гарантировать, что они все хранятся в виде строк. Следующий код показывает пример этого:

Sub IspStr()

    Dim coll As New Collection
    Dim c As Range

    ' Считать значения ячеек в коллекцию
    For Each c In Range("A1:A10")
        ' Используйте Str для преобразования значения ячейки в строку
        coll.Add Str(c)
    Next

    ' Распечатайте значения и тип коллекции
    Dim i As Variant
    For Each i In coll
        Debug.Print i, TypeName(i)
    Next

End Sub

В приведенном выше примере мы используем Str для преобразования значения ячейки в строку. Альтернативой этому может быть присвоение значения строке, а затем присвоение строки коллекции. Итак, вы видите, что использование Str здесь намного эффективнее.

Multi Region

Разница между функциями Str и CStr заключается в том, что CStr преобразует в зависимости от региона. Если ваши макросы будут использоваться в нескольких регионах, вам нужно будет использовать CStr для преобразования строк.

Хорошей практикой является использование CStr при чтении значений из ячеек. Если ваш код в конечном итоге используется в другом регионе, вам не нужно вносить какие-либо изменения, чтобы он работал правильно.

Преобразовать строку в число — CLng, CDbl, Val и т.д.

Вышеуказанные функции используются для преобразования строк в различные типы. Если вы присваиваете переменную этого типа, VBA выполнит преобразование автоматически.

Sub StrToNumeric()

    Dim l As Long, d As Double, c As Currency
    Dim s As String
    s = "45.923239"

    l = s
    d = s
    c = s

    Debug.Print "Long is "; l
    Debug.Print "Double is "; d
    Debug.Print "Currency is "; c

End Sub

Использование типов преобразования дает большую гибкость. Это означает, что вы можете определить тип во время выполнения. В следующем коде мы устанавливаем тип на основе аргумента sType, передаваемого в функцию PrintValue. Поскольку этот тип может быть прочитан из внешнего источника, такого как ячейка, мы можем установить тип во время выполнения. Если мы объявим переменную как Long, то при выполнении кода она всегда будет длинной.

Sub Test()
    ' Печатает  46
    PrintValue "45.56", "Long"
    ' Печатает 45.56
    PrintValue "45.56", ""
End Sub

Sub PrintValue(ByVal s As String, ByVal sType As String)

    Dim value

    ' Установите тип данных на основе строки типа
    If sType = "Long" Then
        value = CLng(s)
    Else
        value = CDbl(s)
    End If
    Debug.Print "Type is "; TypeName(value); value

End Sub

Если строка не является допустимым числом (т.е. Содержит символы, другие цифры), вы получаете ошибку «Несоответствие типов».

Sub InvalidNumber()

    Dim l As Long

    ' Даст ошибку несоответствия типов
    l = CLng("45A")

End Sub

Функция Val

Функция преобразует числовые части строки в правильный тип числа.

Val преобразует первые встреченные числа. Как только он встречает буквы в строке, он останавливается. Если есть только буквы, то в качестве значения возвращается ноль. Следующий код показывает некоторые примеры использования Val

Sub IspVal()

    ' Печатает 45
    Debug.Print Val("45 Новая улица")

    ' Печатает 45
    Debug.Print Val("    45 Новая улица")

    ' Печатает 0
    Debug.Print Val("Новая улица 45")

    ' Печатает 12
    Debug.Print Val("12 f 34")

End Sub

Val имеет два недостатка

1. Не мультирегиональный — Val не распознает международные версии чисел, такие как запятые вместо десятичных. Поэтому вы должны использовать вышеуказанные функции преобразования, когда ваше приложение будет использоваться в нескольких регионах.
2. Преобразует недопустимые строки в ноль — в некоторых случаях это может быть нормально, но в большинстве случаев лучше, если неверная строка вызывает ошибку. Затем приложение осознает наличие проблемы и может действовать соответствующим образом. Функции преобразования, такие как CLng, вызовут ошибку, если строка содержит нечисловые символы.

Генерация строки элементов — функция строки

Функция String используется для генерации строки повторяющихся символов. Первый аргумент — это количество повторений, второй аргумент — символ.

Sub IspString()

    ' Печатает: AAAAA
    Debug.Print String(5, "A")
    ' Печатает: >>>>>
    Debug.Print String(5, 62)
    ' Печатает: (((ABC)))
    Debug.Print String(3, "(") & "ABC" & String(3, ")")

End Sub

Преобразовать регистр / юникод — StrConv, UCase, LCase

Если вы хотите преобразовать регистр строки в верхний или нижний регистр, вы можете использовать функции UCase и LCase для верхнего и нижнего соответственно. Вы также можете использовать функцию StrConv с аргументом vbUpperCase или vbLowerCase. В следующем коде показан пример использования этих трех функций.

Sub ConvCase()

    Dim s As String
    s = "У Мэри был маленький ягненок"

    ' верхний
    Debug.Print UCase(s)
    Debug.Print StrConv(s, vbUpperCase)

    ' нижний
    Debug.Print LCase(s)
    Debug.Print StrConv(s, vbLowerCase)

    ' Устанавливает первую букву каждого слова в верхний регистр
    Debug.Print StrConv(s, vbProperCase)

End Sub

На выходе: 

У МЭРИ БЫЛ МАЛЕНЬКИЙ ЯГНЕНОК
У МЭРИ БЫЛ МАЛЕНЬКИЙ ЯГНЕНОК
у мэри был маленький ягненок
у мэри был маленький ягненок
У Мэри Был Маленький Ягненок

Другие преобразования

Как и в случае, StrConv может выполнять другие преобразования на основе параметра Conversion. В следующей таблице приведен список различных значений параметров и того, что они делают. Для получения дополнительной информации о StrConv проверьте страницу MSDN.

Использование строк с массивами

Строка в массив с использованием Split

Вы можете легко разобрать строку с разделителями в массив. Вы просто используете функцию Split с разделителем в качестве параметра. Следующий код показывает пример использования функции Split.

Sub StrToArr()

    Dim arr() As String
    ' Разобрать строку в массив
    arr = Split("Иван,Анна,Павел,София", ",")

    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

На выходе:

Иван
Анна
Павел
София

Если вы хотите увидеть некоторые реальные примеры использования Split, вы найдете их в статье Как легко извлечь любую строку без использования VBA InStr.

Массив в строку, используя Join

Если вы хотите построить строку из массива, вы можете легко это сделать с помощью функции Join. По сути, это обратная функция Split. Следующий код предоставляет пример использования Join

Sub ArrToStr()

    Dim Arr(0 To 3) As String
    Arr(0) = "Иван"
    Arr(1) = "Анна"
    Arr(2) = "Павел"
    Arr(3) = "София"

    ' Построить строку из массива
    Dim sNames As String
    sNames = Join(Arr, ",")

    Debug.Print sNames

End Sub

На выходе:

Иван, Анна, Павел, София

Форматирование строки

Функция Format используется для форматирования строки на основе заданных инструкций. В основном используется для размещения даты или числа в определенном формате. Приведенные ниже примеры показывают наиболее распространенные способы форматирования даты.

Sub FormatDate()

    Dim s As String
    s = "31/12/2019 10:15:45"

    ' Печатает: 31 12 19
    Debug.Print Format(s, "DD MM YY")
    ' Печатает: Thu 31 Dec 2019
    Debug.Print Format(s, "DDD DD MMM YYYY")
    ' Печатает: Thursday 31 December 2019
    Debug.Print Format(s, "DDDD DD MMMM YYYY")
    ' Печатает: 10:15
    Debug.Print Format(s, "HH:MM")
    ' Печатает: 10:15:45 AM
    Debug.Print Format(s, "HH:MM:SS AM/PM")

End Sub

В следующих примерах представлены некоторые распространенные способы форматирования чисел.

Sub FormatNumbers()

    ' Печатает: 50.00%
    Debug.Print Format(0.5, "0.00%")
    ' Печатает: 023.45
    Debug.Print Format(23.45, "00#.00")
    ' Печатает: 23,000
    Debug.Print Format(23000, "##,000")
    ' Печатает: 023,000
    Debug.Print Format(23000, "0##,000")
    ' Печатает: $23.99
    Debug.Print Format(23.99, "$#0.00")

End Sub

Функция «Формат» — довольно обширная тема, и она может самостоятельно занять всю статью. Если вы хотите получить больше информации, то страница формата MSDN предоставляет много информации.

Полезный совет по использованию формата

Быстрый способ выяснить используемое форматирование — использовать форматирование ячеек на листе Excel. Например, добавьте число в ячейку. Затем щелкните правой кнопкой мыши и отформатируйте ячейку так, как вам нужно. Если вы довольны форматом, выберите «Пользовательский» в списке категорий слева. При выборе этого вы можете увидеть строку формата в текстовом поле типа. Это формат строки, который вы можете использовать в VBA.

Заключение

Практически в любом типе программирования вы потратите много времени на манипулирование строками. В этой статье рассматриваются различные способы использования строк в VBA.

Чтобы получить максимальную отдачу, используйте таблицу вверху, чтобы найти тип функции, которую вы хотите использовать. Нажав на левую колонку этой функции, вы попадете в этот раздел.

Если вы новичок в строках в VBA, то я предлагаю вам ознакомиться с разделом «Прочтите это в первую очередь» перед использованием любой из функций.

Как и в любом текстовом редакторе, в программе Excel присутствует возможность форматирования текста разными способами. При необходимости вы можете сделать его дизайнерским, декоративным, перевернутым и любым другим. Для того чтобы правильно обрабатывать текст в Excel, вам необходимо иметь навыки работы с некоторыми инструментами.

Простое форматирование: изменение направления, цвета и размера

Имея конкретную надпись или короткий текст, можно поиграть с его настройками в Excel. Для этого используйте некоторые инструменты, следуя инструкции ниже.

1. Открываем Excel и проводим вставку текста, если он еще не прописан в ячейках. Далее переходим на вкладку «Вставка» и в нужной нам ячейке кликаем правой кнопкой мыши один раз. Перед вами всплывает меню в виде списка. Здесь выбираете пункт «Специальная вставка».

1. Открывается следующее окно, где нажимаем «Вставить как» и выбраем «Текст». Затем нажимаете кнопку «ОК», чтобы выбранные действия сохранились. Как мы видим текст успешно перенесен в выбранную ячейку.

1. Теперь попробуем преобразовать этот текст в декоративный и сменить его направление. Для этого переходим снова во вкладку «Вставка», копируем исходный текст из ячейки и нажимаем на кнопку «Надпись».

1. Выбираем нужное место на листе и кликаем мышью. Перед вами открывается небольшое окошко, в которое вставляем скопированный текст путем нажатия CRTL+V. Здесь вы можете увеличить масштаб заданной рамки или сделать текст перевернутым при помощи соответствующих инструментов.

1. Мы можем сменить структуру надписи и ее текст. Для этого перейдем в активную вкладку «Формат», выберем блок «Стили WordArt», после чего кликнем на «Контур текста». Здесь выберете нужный оттенок из существующей палитры.

Используем мастер форматирования

1. Чтобы приступить к смене направления текста, необходимо сделать клик правой кнопкой мыши на объекте со вставленной надписью. Перед вами откроется меню, в котором, выберите пункт исходя из требований: или «Формат фигуры», или «Размер и свойства».

1. Перед вами справа откроется диалоговое окно мастера, в котором собраны самые популярные утилиты для работы со вставленным текстом и фигурами. Здесь можно выбрать для надписей различные стили шрифтов, задать направление и объем.

Используя встроенный мастер обработки текстовой информации, вы сможете сделать привлекательный заголовок для разного рода документов. Из таблиц Excel удобно переводят надписи к видеофайлам.

Как перенести текст в Эксель

Вы наверняка замечали, что при записи текста в приложении Excel происходит его занесение строго в одну линию, а если вы используете перенос текста посредством кнопки ENTER, то во время печати он может отображаться некорректно. Чтобы избежать таких сложностей, предлагаем вам следующий вариант решения проблемы.

1. Выделим наш текст. Для этого зайдите в строку формул и протяните мышью от начала до конца. Или же сделайте это непосредственно, где размещается запись. Если не отображаются кнопки инструментов, значит просто нажмите на ячейку, в которой начинается текст.

1. После чего переходите на вкладку «Главная» и нажимайте на кнопку «Перенести текст». Теперь вся ваша запись отображается в формате с множеством строчек или в виде столбика.

Также работа с текстом осуществляется посредством специальной функции — читайте подробнее в Справочнике Студворк.

Тест по теме «Как работать с текстом в Excel: основные положения и инструкции»

Работа в эксель с текстом

Текстовые функции в Excel. Часть №1

Добрый день уважаемый читатель!

Сегодня я хотел описать целый раздел функций Excel, но думаю, что за один раз не успею рассказать какие самые популярные текстовые функции в Excel. Поэтому предлагаю вам первую часть моей статьи с описаниями функций для работы с текстом.

Несмотря на то что таблицы в основном предназначены для работы с цифрами, тем не менее очень много задач существует, когда необходимо обработать текстовые значения. Например, это работа с ключевыми словами для SEO оптимизаторов, обработка ФИО, работа с комментариями, ведение адресов сотрудников и клиентов, формирование прайсов, работа с импортированными текстовыми данными и еще много-много чего полезного и интересного, поэтому пройти мимо такой темы я не смог.

Итак, рассмотрим первые 7 функций для работы с текстовыми значениями:

Полный перечень функций можно найти перейдя на панели инструментов во вкладку «Формулы» и нажав иконку «Текстовые», в выпадающем меню выберете функцию которая вам нужна, это быстрый способ. Вторым вариантом будет в этой же вкладке нажать иконку «Вставить функцию» и в новом диалоговом окне в категории выбираете «Текстовые», но данный способ я считаю лучшим так как снизу окна идет описание выбранной функции что очень полезно.

Функция ДЛСТР

Рассматриваемая функция позволяет узнать какое количество знаков содержится в текстовой ячейке. Эта возможность очень полезна, когда количество знаков является критическим и лимитированным на ввод, как к примеру, некоторые поля в работе SEO специалистов.

Синтаксис функции:

• текст – это прописанный вручную текст или ссылка на ячейку которая содержит текстовое значение.

Пример применения: Дополнительно ознакомится с функцией можно в статье «ТОП 10 функций Excel для SEO специалиста».

Функция ЗАМЕНИТЬ

Функция призвана в указанных данных ячейки произвести замену по определённым параметрам. Попросту заменить некую старую часть текста на новую.

Синтаксис функции:

= ЗАМЕНИТЬ(_старый текст_; _начальная позиция_; _количество знаков_; _новый текст_), где:

• старый текст – ссылка на текстовую ячейку или строку которая будет содержать изменяемый текст;
• начальная позиция – указывает номер по порядку для символа считая слева направо, с которого и будет производится замена;
• количество знаков – указывается количество символов, которые нужно заменить новым текстом, начиная с первой позиции в том числе;
• новый текст – это текстовое значение, которое будет заменять часть старого текста который был задан первыми двумя аргументами.

Простой пример применения:

Для начала рассмотрим сам механизм замены текста по указанным аргументам. В ячейке А1 мы производим замену с 20 символа, слова «СТАРЫЙ», которое состоит из 6 символов, на слово «НОВЫЙ». Этот способ вполне реален ежели необходима замена небольшого количества ячеек. Сложный пример применения:

Когда у нас очень большое количество различных строк, тогда простой способ нам не поможет, нужно что-то более универсальное и гибкое. Для этой задачи нужно заменить аргумент «начальная позиция» на функцию НАЙТИ (она будет искать нужное условие), а в аргументе «количество знаков» использовать функцию ДЛСТР (будет определять количество символов по условию). И как результат напишем формулу:

=ЗАМЕНИТЬ(A1;НАЙТИ(«ТОП»;A1);ДЛСТР(«ТОП»);»СУПЕР»)

Функция ЗНАЧЕН

Это небольшая, но достаточно полезная функция с помощью которой можно переделать текстовое значение которое показано числом, в само число. Это поможет избежать ошибок в формуле, когда в обрабатываемый числовой диапазон попадает текстовое значение, как итог – отсутствия результата и испорченные нервы.

Синтаксис функции:

• текст – ссылка на ячейку или текст в кавычках, которая содержит числовое значение, требующее преобразования в числовой формат.

Пример применения:

Функция ЛЕВСИМВ

Эта текстовая функция возвращает из указанного адреса прописанное количество символов отсчитывая их с начала значения, по порядку слева направо. Очень полезная возможность, когда существует необходимость сокращений фраз или слов.

Синтаксис функции:

= ЛЕВСИМВ(_текст_; _[количество знаков]_), где:

• текст – ссылка на ячейку или текст из которого нужно изъять символы;
• количество знаков – является необязательным аргументом. По умолчанию имеет значение 1. Это целое число, которое указывает сколько символов нужно достать из текста.

Пример применения: Более детально и шире с функцией можно ознакомится в статье «Функции ЛЕВСИМВ и ПРАВСИМВ в Excel».

Функция НАЙТИ

Эта функция программный аналог возможности горячих клавиш Ctrl+F, «Найти», но имеет преимущество в автоматизме, но недостаток в сложности исполнения и сейчас это исправим, сложность я имел ввиду. Работа функции заключается в том, чтобы вернуть число, которое является началом вхождения первого символа текста, который мы ищем в указанной ячейке. Не стоить забывать, что функция НАЙТИ очень чувствительна к регистру значений и не стоит этого забывать. Еще нужно знать, что в случае, когда искомый текст не будет найден, получим ошибку «#ЗНАЧ!».

Синтаксис функции:

= НАЙТИ(_искомый_текст_; _текст_для_поиска_; _[нач_позиция]_), где:

• искомый текст – это текст который необходимо искать;
• текст для поиска – это ячейка в которой будет произведен поиск нужного значения;
• начальная позиция – является необязательным аргументом и по умолчанию имеет значение 1. Можно указывать целое число, которое послужит отправной точкой для аргумента «текст для поиска», с какого символа начинать поиск.

Пример применения: Более детально и шире с функцией можно ознакомится в статье «Функция НАЙТИ в Excel».

Функция ПОВТОР

Очень простая функция которая делает незамысловатую работу, повторяет указанный текст, столько раз, сколько вы указали.

Синтаксис функции:

= ПОВТОР(_текст_; _число_повторений_), где:

• текст – указывается текст или ссылка на него;
• число повторений – задается число повторов для первого аргумента.

Пример применения:

Функция ПРАВСИМВ

Эта функция является зеркальным отражением функции ЛЕВСИМВ, разница заключается только в том, что отсчёт идет не с начала, а с конца, справа налево.

Синтаксис функции:

= ПОВТОР(_текст_; _число повторений_), где:

• текст – указывается текст или ссылка на него;
• число повторений – задается число повторов для первого аргумента.

Пример применения: Это еще не окончание, так как текстовых функций в Excel еще много, не один десяток, вот описание будет занимать три части, первую вы уже прочитали, теперь можете просмотреть часть 2, часть 3.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Функции для работы с текстом в Excel

Для удобства работы с текстом в Excel существуют текстовые функции. Они облегчают обработку сразу сотен строк. Рассмотрим некоторые из них на примерах.

Примеры функции ТЕКСТ в Excel

Преобразует числа в текст. Синтаксис: значение (числовое или ссылка на ячейку с формулой, дающей в результате число); формат (для отображения числа в виде текста).

Самая полезная возможность функции ТЕКСТ – форматирование числовых данных для объединения с текстовыми данными. Без использования функции Excel «не понимает», как показывать числа, и преобразует их в базовый формат.

Покажем на примере. Допустим, нужно объединить текст в строках и числовые значения:

Использование амперсанда без функции ТЕКСТ дает «неадекватный» результат:

Excel вернул порядковый номер для даты и общий формат вместо денежного. Чтобы избежать подобного результата, применяется функция ТЕКСТ. Она форматирует значения по заданию пользователя.

Формула «для даты» теперь выглядит так:

Второй аргумент функции – формат. Где брать строку формата? Щелкаем правой кнопкой мыши по ячейке со значением. Нажимаем «Формат ячеек». В открывшемся окне выбираем «все форматы». Копируем нужный в строке «Тип». Вставляем скопированное значение в формулу.

Приведем еще пример, где может быть полезна данная функция. Добавим нули в начале числа. Если ввести вручную, Excel их удалит. Поэтому введем формулу:

Если нужно вернуть прежние числовые значения (без нулей), то используем оператор «—»:

Обратите внимание, что значения теперь отображаются в числовом формате.

Функция разделения текста в Excel

Отдельные текстовые функции и их комбинации позволяют распределить слова из одной ячейки в отдельные ячейки:

• ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
• ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
• ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо

При разделении текста в строке учитывается положение каждого знака. Пробелы показывают начало или конец искомого имени.

Распределим с помощью функций имя, фамилию и отчество в разные столбцы.

В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

Формула для извлечения фамилии:

С помощью функции ПОИСК Excel определяет количество знаков для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Затем отнимается количество знаков до первого пробела (найденное ПОИСКом).

Вторая строка содержит имя, отчество и фамилию. Для имени используем такую же формулу:

Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(» «;A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.

Часть формулы – ПОИСК(» «;A3;ПОИСК(» «;A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.

Далее из общей длины строки отнимается количество знаков с начала строки до второго пробела. Результат – число символов справа, которые нужно вернуть.

Формула «для отчества» строится по тем же принципам:

Функция объединения текста в Excel

Для объединения значений из нескольких ячеек в одну строку используется оператор амперсанд (&) или функция СЦЕПИТЬ.

Например, значения расположены в разных столбцах (ячейках):

Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова — &. И так последовательно соединяем ячейки с текстом и пробелы.

Получаем в одной ячейке объединенные значения:

Использование функции СЦЕПИТЬ:

С помощью кавычек в формуле можно добавить в конечное выражение любой знак или текст.

Функция ПОИСК текста в Excel

Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например:

Функция ПОИСК вернула позицию 10, т.к. слово «Захар» начинается с десятого символа в строке. Где это может пригодиться?

Функция ПОИСК определяет положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Либо можно заменить найденный текст посредством функции ЗАМЕНИТЬ.

Синтаксис функции ПОИСК:

• «искомый текст» — что нужно найти;
• «просматриваемый текст» — где искать;
• «начальная позиция» — с какой позиции начинать искать (по умолчанию – 1).

Если нужно учитывать регистр, используется функция НАЙТИ.

Текстовые функции Excel в примерах

Excel предлагает большое количество функций, с помощью которых можно обрабатывать текст. Область применения текстовых функций не ограничивается исключительно текстом, они также могут быть использованы с ячейками, содержащими числа. В рамках данного урока мы на примерах рассмотрим 15 наиболее распространенных функций Excel из категории Текстовые.

Для объединения содержимого ячеек в Excel, наряду с оператором конкатенации, можно использовать текстовую функцию СЦЕПИТЬ. Она последовательно объединяет значения указанных ячеек в одной строке.

Если в Excel необходимо сделать все буквы строчными, т.е. преобразовать их в нижний регистр, на помощь придет текстовая функция СТРОЧН. Она не заменяет знаки, не являющиеся буквами.

Текстовая функция ПРОПИСН делает все буквы прописными, т.е. преобразует их в верхний регистр. Так же, как и СТРОЧН, не заменяет знаки, не являющиеся буквами.

Текстовая функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные преобразует в строчные.

Каждая первая буква, которая следует за знаком, отличным от буквы, также преобразуется в верхний регистр.

В Excel Вы можете подсчитать количество знаков, содержащихся в текстовой строке, для этого воспользуйтесь функцией ДЛСТР. Пробелы учитываются.

ЛЕВСИМВ и ПРАВСИМВ

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.

Текстовая функция ПСТР возвращает заданное количество символов, начиная с указанной позиции. Пробел считается за символ.

Функция СОВПАД позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.

Если регистр для Вас не играет большой роли (так бывает в большинстве случаев), то можно применить формулу, просто проверяющую равенство двух ячеек.

Удаляет из текста все лишние пробелы, кроме одиночных между словами.

В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.

Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все лишние пробелы и получим корректный результат:

Функцию СЖПРОБЕЛЫ полезно применять к данным, которые импортируются в рабочие листы Excel из внешних источников. Такие данные очень часто содержат лишние пробелы и различные непечатаемые символы. Чтобы удалить все непечатаемые символы из текста, необходимо воспользоваться функцией ПЕЧСИМВ.

Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.

Текстовая функция НАЙТИ находит вхождение одной строки в другую и возвращает положение первого символа искомой фразы относительно начала текста.

Данная функция чувствительна к регистру…

… и может начинать просмотр текста с указанной позиции. На рисунке ниже формула начинает просмотр с четвертого символа, т.е. c буквы “r“. Но даже в этом случае положение символа считается относительно начала просматриваемого текста.

Текстовая функция ПОИСК очень похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.

ПОДСТАВИТЬ

Заменяет определенный текст или символ на требуемое значение. В Excel текстовую функцию ПОДСТАВИТЬ применяют, когда заранее известно какой текст необходимо заменить, а не его местоположение.

Приведенная ниже формула заменяет все вхождения слова “Excel” на “Word”:

Заменяет только первое вхождение слова “Excel”:

Удаляет все пробелы из текстовой строки:

Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.

Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение “2013”. Применительно к нашему примеру, формула заменит “2010” на “2013”.

Заменяет первые пять символов текстовой строки, т.е. слово “Excel”, на “Word”.

Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Надеюсь, что данный урок пришелся Вам как раз кстати, и Вы получили от него хотя бы малость полезной информации. Всего доброго и успехов в изучении Excel!

EXCEL для чайников.3.текст

Доброго времени суток, пикабушнички! Предлагаю продолжить работу по осёдлыванию могучего скакуна, по кличке Excel. Это мой третий пост. В предыдущих мы говорили о функциях времени https://pikabu.ru/story/excel_dlya_chaynikov2vremya_5352893 и ВПР https://pikabu.ru/story/excel_dlya_chaynikov1vpr_5337342 , этот же пост будет про работу с текстом. Некоторые сейчас подумают: «Что? Зачем работать с текстом в экселе, если есть ворд?» или даже «Ворд — для текста, ексель — для таблиц!». Однако, глупо отрицать, что бывают такие ситуации, когда текст нужен в таблице. И, выбирая между работой с таблицей в ворде и работой с текстом в екселе, вы вряд ли выберете первое. А если выберете… храни вас Бог.

Итак, сегодня мы поговорим про обработку текста в таблицах. Сразу отмечу, что у некоторых функций работы с текстом имеется дублер для работы с иероглифами, например функция ДЛСТР имеет восточного дублера ДЛИНБ. Однако, за неимением у автора достаточных знаний о соответствующих иностранных языках, рассматривать работу с иероглифами не будем.

Вот краткий перечень основных функций по работе с текстом (если ты не любишь подобное занудство — мотай до таблицы):

ДЛСТР(текст) – выдает длину строки (количество символов)

ЛЕВСИМВ(текст, [число_знаков]) – «отрубаем» нужное количество знаков слева

ПРАВСИМВ(текст,[число_знаков]) – «отрубаем» нужное количество знаков справа

ПСТР(текст, нач_позиция, число_знаков) – «вырубаем» нужное слово или несколько из текстовой ячейки

ПРОПИСН(текст) — ДЕЛАЕТ ВЕСЬ ТЕКСТ ПРОПИСНЫМ (для любителей КАПСА!)

ПРОПНАЧ(текст) — Делает Первые Буквы Слов Прописными

СТРОЧН(текст) – все буковки маленькие

ЗНАЧЕН(текст)- преобразует текст в числовое значение (полезно если откуда-то достались числа, сохраненные как текст, с которыми нельзя совершать математические действия)

ТЕКСТ(значение, формат) преобразует значение ячейки в текст нужного нам формата

СЖПРОБЕЛЫ(текст)- удаляет «ненужные» пробелы

ПЕЧСИМВ(текст)- убирает непечатные символы

КОДСИМВ(текст)- показывает код символа ANSI если это вам о чем то говорит (если символов несколько то код первого символа)

СИМВОЛ(число) пишет символ по его коду (это как КОДСИМВ наоборот)

ПОВТОР(текст, число_повторений) – повторяет текст нужное количество раз

СЦЕПИТЬ(текст1, [текст2], . ) сцепляет несколько текстовых ячеек в одну (вместо этой функции я предпочитаю использовать символ &, который аналогичен ей)

НАЙТИ(искомый_текст, просматриваемый_текст, [нач_позиция])ищем определенный текст в ячейках, результатом будет порядковый номер первого символа исходного текста (ниже будут примеры)

ПОИСК(искомый_текст, просматриваемый_текст,[нач_позиция])то же что и НАЙТИ но без учета регистра

ЗАМЕНИТЬ(стар_текст, нач_позиция, число_знаков, нов_текст) заменяет кусочек текста ячейки на другой

ПОДСТАВИТЬ(текст, стар_текст, нов_текст, [номер_вхождения]) заменяет одну часть текста на другую в нужном «вхождении» (терпение, примеры ниже)

СОВПАД(текст1, текст2) – сравнивает две ячейки на предмет их тождественности

T(значение)- если эта функция ссылается на текст, то возвращает его, если нет, то возвращает пустое место

РУБЛЬ(число, [число_знаков]) переводит число в денежный формат (с нужным округлением)

ФИКСИРОВАННЫЙ(число, [число_знаков], [без_разделителей]) – округляем число и преобразовываем его в текст (разделители это такие пробелы между тысячами, миллионами и т.д., чтобы число лучше читалось.

Пример работы всех описанных функций:

Комментарии к таблице в общем-то излишни, думаю все вполне наглядно. Поясню пару моментов:

1. Округление в функциях РУБЛЬ и ФИКСИРОВАННЫЙ я нарочно делал отрицательным, чтобы показать, что так можно, в этом случае округление идет до десятков (-1), сотен(-2), и т.д. Этот прием работает и с обычными округлениями ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ при разложении числа на десятки, сотни и т.д.

2. Число в ячейке В3 сохранено как текст, поэтому с ним нельзя будет выполнять каких либо арифметических действий пока мы не переведем его в значение, по сути функции ЗНАЧЕН(), РУБЛЬ() и ФИКСИРОВАННЫЙ() делают одно и тоже, только две последних меняют еще и формат ячейки.

Подробнее о форматах расскажу в следующий раз.

А здесь хотелось бы рассмотреть небольшой пример для закрепления. Чтобы не нагромождать множество функций в примере рассмотрим несколько из них. Допустим у нас есть ячейки с ФИО, нам нужно разделить фамилии, имена и отчества в разных ячейках. Затем соединим фамилию и инициалы имени отчества сокращенно, а в последней ячейке проверим, действительно ли у нас написана фамилия.

теперь посмотрим какие формулы прописаны у нас в столбцах В-Н:

столбец В: =ДЛСТР(A3) – находим длину текстовой ячейки в столбце А;

столбец С: =ПОИСК(» «;A3;1) – ищем первый пробел, начиная с первого символа;

столбец D: =ПОИСК(» «;A3;C3+1) – ищем второй пробел, начиная со следующего символа после первого пробела, найденного ранее;

столбец Е: =ЛЕВСИМВ(A3;C3-1) – отрезаем фамилию, отрезая заодно один символ пробела;

столбец F: =ПСТР(A3;C3+1;D3-C3) – вырезаем имя из середины, от первого пробела на длину имени, равную разности позиций первого и второго пробела;

столбец G: =ПРАВСИМВ(A3;B3-D3) – отрезаем отчество, длина которого равняется разности позиции второго пробела и общей длинны текстовой ячейки;

столбец Н: =СЦЕПИТЬ(E3;» «;ЛЕВСИМВ(F3;1);».»;ЛЕВСИМВ(G3;1);».») – набираем фамилию и первые буквы имени и отчества, разделяя их пробелом и точками;

столбец I: =ЕСЛИ(СОВПАД(E3;ПРОПНАЧ(E3));»ДА»;»НЕТ») – проверяем совпадают ли значения ячейки с фамилией и такой же ячейки проведенной через функцию ПРОПНАЧ, которая делает заглавной первую букву, иными словами проверяем является ли в ячейке с фамилией первая буква заглавная а остальные строчные. Если является, то пишем «ДА»; если нет то «НЕТ».

На этом пока все, буду фантазировать над темой следующего поста…

7 формул для работы с текстами объявлений в Excel

Сколько программ и сервисов вы используете при работе над текстом объявлений для контекстной рекламы? Хотелось бы, чтобы одним из первых вы назвали MS Excel, поскольку он помогает справляться со многими задачами. Не зря Яндекс.Директ и Google AdWords позволяют загружать кампании с помощью xls- и csv-файлов. В этом материале мы разберем семь полезных формул для работы с текстом и ключевыми фразами в Excel.

1. Функция ДЛСТР — Подсчет символов в ячейке

Эта формула может быть знакома многим, но существует полезный лайфхак. В строке ввода для соседней ячейки прописываем: = ДЛСТР(А1), где ДЛСТР – функция, (А1) – положение ячейки, взятое в скобки. Так вы посчитаете количество знаков в строке.

При просмотре всей таблицы с текстом объявлений легко не заметить превышения по символам. Чтобы они бросались в глаза, нужно сделать следующее:

1. сначала выделить столбец с цифрами,
2. затем на главной панели меню выбрать Условное форматирование ->Правила выделения ячеек ->Больше,

1. в появившемся окне ввести максимальное количество символов,
2. выбрать необходимый параметр выделения.

Не забудьте растянуть формулу на все необходимые ячейки. Теперь, если количество символов будет превышать максимум, вы это заметите.

2. Функция СЖПРОБЕЛЫ— Удаление лишних пробелов

При копировании большого массива текста в нем могут появиться лишние пробелы (до, после или между словами), что затруднит дальнейшую работу. Функция СЖПРОБЕЛЫ позволяет избавиться от всех лишних пробелов, кроме одинарных, в заданных ячейках. Вводим формулу = СЖПРОБЕЛЫ(А1), где (А1) – ячейка к изменению, и растягиваем на необходимое количество ячеек.

3. Формула СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1))) — Преобразует первое слово ячейки с прописной буквы

Чтобы преобразовать имеющийся ключевую фразу в заголовок или текст объявления без привлечения сторонних сервисов, применяем эту формулу: =СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1))), где А1 – необходимая ячейка.

Эта формула, как и предыдущая, работает не «поверх» ячеек с изменяемыми данными, а в ячейке ввода формулы.

Совет: теперь, когда вы преобразовали текст, фактически ваши ячейки содержат формулы, а не сам текст. Когда вы будете копировать содержимое этих ячеек для дальнейшей работы, скопируются формулы, а не текст. Чтобы в ячейках оказался измененный текст, нужно:

1. выделить все измененные ячейки,
2. нажать сочетание клавиш Ctrl+C;
3. затем, не переходя на другие ячейки, нажать сочетание клавиш Ctrl+V;
4. в выпадающем меню Параметров вставки выбрать пункт «Только значения».

Все, теперь ячейки содержат только текстовые значения!

4. Формула СЦЕПИТЬ(А1;“ ”;А2 ;“ ”;А3;…) — Объединяет 2 и более ячеек в одну с добавлением пробела между ними

Разберем формулу подробней:

=СЦЕПИТЬ(А1;» «;А2 ;» «;А3;…), где А1, А2, А3 – ячейки к объединению,

“ ” – пробелы, которые проставятся между содержимым ячеек.

Если сочетание символов “ ” не применить, ячейки «склеятся» без пробела.

5. Применение функций СЦЕПИТЬ, ЕСЛИ и ДЛСТР — Добавляет продающее окончание

С помощью этой формулы можно дополнять текст и заголовок объявлений т. н. продающими окончаниями, сообщающими о скидках, акциях и других конкурентных преимуществах.

Сначала нужно определиться с окончанием. Например, для разного допустимого количества символов можно взять «Купить со скидкой 30%!», «Скидка 30%!», «!». Надо помнить, что после содержания первой ячейки нужно добавить знак препинания, а перед продающим окончанием – пробел. Т.е. продающие окончания должны иметь вид: «. Заказать со скидкой 30%!», «. Скидка 30%!» или «!».

Далее необходимо подсчитать количество символов заданных окончаний при помощи уже знакомой функции ДЛСТР.

В зависимости от ограничения по символам (например, в заголовке рекламного объявления в Директе максимальное количество знаков — 33), считаем, какое допустимое количество символов должна содержать ячейка, к которой добавляем окончание. Проще говоря, из 33 вычитаем получившееся количество символов продающих окончаний.

Обзор функций для работы с текстом

Мы подготовили обзорное видео, где собрали полезные сочетания текстовых функций в Excel и Google таблицах.

Все примеры основаны на реальных задачах, с которыми мы очень часто сталкиваемся в рабочих моментах. Предполагаем, что большинство пользователей найдут это видео полезным и сохранят комбинации рассматриваемых функций для работы с подобными текстовыми данными.

Скачать файлы из этой статьи

Рабочие файлы

Сохраняйте, чтобы знать, где искать ответы на похожие задачи.

Обзор полезных текстовых функций смотрите ниже. Приятного просмотра!

Как привести телефонные номера к единому формату?

Как показывает практика, несмотря на наличие продвинутых автоматизированных систем, большинство компаний малого и среднего бизнеса продолжают вести свои базы (в том числе клиентские) в Excel или Google таблицах.

В идеальном мире все данные в таблицах упорядочены и имеют одинаковый вид.

Что же касается реальной жизни — так данные немного в хаосе.

Посмотрите на вид хранения телефонов в одной из баз. Лишние символы – дефисы, пробелы, двоеточия, пояснительная бригада в виде надписей – номер телефона, сотовый, тел.

Согласитесь, это, во-первых, выглядит неопрятно, во-вторых – обрабатывать такие данные ой как не просто.

Поэтому, давайте договоримся, если мы и ведем свои базы в Excel или Google таблицах, то делаем это грамотно. Все номера в одном формате. В конце концов, Вы же не просто так эти номера собираете? Предполагается работа с этими номерами. Любому сотруднику будет удобнее ориентироваться в упорядоченной базе, плюс ко всему, по корректному списку номеров без проблем можно запустить массовую рассылку (например, в WhatsApp).

Ну а если Ваша база уже в легком хаосе, то самое время привести ее в порядок. Так, сочетание функций ПРАВСИМВ() и ПОДСТАВИТЬ() помогут преобразовать различные варианты записи телефона в стандартный вид.

="+7"&ПРАВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;"(";"");")";"");" ";"");"-";"");" ";"");10)

Аналогичные функции применяются к данным в Google таблице.

Как извлечь домены адресов электронной почты?

Следующий кейс для тех, кто работает с электронными адресами в таблицах. Есть список адресов электронной почты, нужно из них получить домены.

Цели такой задачи могут быть абсолютно разными. Например, подбить статистику какими почтовыми клиентами пользуются Ваши клиенты, или же получить из почтового адреса сайт компании.

Сочетание функций для извлечения домена: =ПРАВСИМВ(B2;ДЛСТР(B2)-ПОИСК("@";B2))

Аналогичное сочетание функций работает для Google таблиц.

Как сократить длинные ссылки?

Следующая задача, немного похожа на предыдущую. Мы запускали парсер, который собрал нам ссылки на различные источники данных. По итогу, у нас имеется список полных URL.

Для дальнейшей обработки этих данных, нам нужно получить только ссылку на сайт, без указания конкретной страницы. А далее, мы уже планируем оценить популярность источников.

Чтобы получить короткий URL воспользуемся уже знакомым сочетанием функций ПОДСТАВИТЬ(), НАЙТИ(), ДЛСТР() и ЛЕВСИМВ().

=ЛЕВСИМВ(B2;ДЛСТР(B2)-(ДЛСТР(B2)-НАЙТИ("/@";ПОДСТАВИТЬ(B2;"/";"/@";3))+1))

Аналогичное сочетание функций работает для Google таблиц.

Как перевести ФИО в сокращенный вид?

Следующая задача, скорее больше знакома менеджерам, ведущим клиентскую базу или эйчарам. Есть полная база ФИО и дата рождения.

Хотим получить сокращенную форму ФИО, а также возраст сотрудника и по возможности определить пол.

Получить сокращенную форму ФИО очень просто. Для этого используются функции СЦЕПИТЬ() и ЛЕВСИМВ().

=СЦЕПИТЬ(B2;" ";ЛЕВСИМВ(C2;1);".";ЛЕВСИМВ(D2;1);".")

Как вычислить возраст по дате рождения?

Для вычисления возраста по дате рождения используется функция РАЗНДАТ(). История с функцией РАЗНДАТ() очень интересная. Вы не найдете её в справке. Более того, её нет в мастере функций, и даже при наборе вручную первых букв названия функции никакой подсказки вам не выйдет!

=РАЗНДАТ(E2;СЕГОДНЯ();"y")

Поэтому, друзья, просто запоминаем, что такая функция есть и она способствует вычислению количества дней, месяцев или лет между двумя датами.

Чтобы ей воспользоваться, задаем начальную дату, конечную дату и в скобках указываем в каких именно единицах нужно вернуть разницу дат. В нашем случае – число полных лет в периоде.

В Google таблицах используются аналогичные функции.

Как определить пол по отчеству?

Определить пол на 100% мы вряд ли сможем, особенно если у нас нестандартные варианты отчества, но для большей части данных – это реально. Так, самым простым вариантом, является проверка последней буквы отчества.

=ЕСЛИ(ПРАВСИМВ(D2)="ч";"М";ЕСЛИ(ПРАВСИМВ(D2)="а";"Ж";"н/о"))

Если отчество заканчивается на букву Ч, Антонович, Арсеньевич – то пол мужской, если отчество заканчивается на букву А – Сергеевна, Валерьевна – то пол женский, в противном случае пол не определен.

Это один из вариантов. Конечно, можно добавить проверку и по другим критериям. Например, добавить развернутый список женских и мужских имен и проверять имена по нему. В общем, вариант есть и не один, все зависит от конкретной задачи.

В Google таблицах все работает аналогично.

И, напоследок, суперлегкая, но все также часто запрашиваемая задача.

Получили «корявую» выгрузку с нестандартным количеством пробелов. Некорректный ручной ввод или баги выгрузки.

Удалить лишние пробелы и, тем самым привести список к корректному виду поможет функция сжатия пробелов — СЖПРОБЕЛЫ().

Аналогичная функция доступна в Google таблицах.

Почему знания Excel или Google таблиц сегодня важны каждому?

В век развития технологий и инноваций, умение работать с большими объемами данных становится преимуществом в глазах потенциальных работодателей.

Электронные таблицы вроде MS Excel, Google Sheets – универсальные и многофункциональные помощники, способные облегчить работу и сэкономить ваше время. С их помощью, можно без труда структурировать большие объемы информации в удобные форматы, визуализировать данные в графики и диаграммы, а также вычислять показатели и создавать отчеты.

Умение работать с электронными таблицами позволяет уверенно использовать навыки на практике в любой профессиональной области. Бухгалтеры производят анализ информации со сводными таблицами, составляют отчеты. Финансовые аналитики и экономисты проводят исследования рынка и создают прогнозы продаж, составляют наглядные дашборды. С помощью табличных редакторов, аналитики и администраторы баз данных производят создание и заполнение базы, фильтруют массивы информации по различным категориям.

Пользователи Microsoft Excel и Google таблиц уверенно решают различные многофункциональные задачи, оптимизируя рабочее время. Инструменты этих программ просты и понятны в использовании, при этом приносят колоссальное количество пользы.

О проекте BIRDYX

Мы оказываем помощь в получении полного представления о работе в программах MS Excel и Google Sheets.

На нашем YouTube канале мы регулярно публикуем видео о секретах, фишках и лайфхаках MS Excel и Google Sheets, а также делимся решениями реальных задач из практики. Там же доступен бесплатный курс «Основы Excel» для начинающих. Подписывайтесь на наш канал, там много полезной и интересной информации.

Тем, кто желает освоить MS Excel или Google Sheets в кратчайшие сроки, мы можем предложить персональные консультации онлайн в удобное время.

А также, Вы можете пройти бесплатные онлайн курсы по MS Excel с заданиями

Мы выполняем задачи любой сложности в Excel и Google таблицах: сложные формулы, дашборды, макросы и многое другое.