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

Содержание:

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel FIND функция

Описание

Как в excel посчитать ячейки с определенным текстом

Была основана FIND Функция используется для поиска строки внутри другой строки и возвращает начальную позицию строки внутри другой строки. Предположим, =FIND(«ea»,»pear») Возвращает 2 что означает найти начальную позицию «ea» in «pear».

Синтаксис и аргументы

Синтаксис формулы

FIND(find_text,with_text,)

аргументы

Find_text: Обязательно, строка или символ, который вы хотите найти.
Within_text: Требуется, строка содержит find_text вы хотите найти.
Start_num: Необязательно, исходное положение в within_text искать find_text. Если опустить этот аргумент, предполагается, что он равен 1.

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

Была основана FIND функция возвращает начальную позицию строки внутри другой.

Замечания

1. Функция НАЙТИ возвращает начальную позицию первого экземпляра find_text в файле within_text.

2. Функция НАЙТИ всегда возвращает количество символов от начала внутри_текста.

3. Если find_text пуст, функция FIND возвращает символ с номером start_num, если start_num опущен, возвращает 1.

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

ошибки

1. Если функция НАЙТИ не может найти искомый_текст в пределах_текста, она возвращает ошибку # ЗНАЧ.

2. Если start_num не больше нуля, функция FIND возвращает ошибку #VALUE.

3. Если start_num длиннее, чем within_text, функция НАЙТИ возвращает ошибку #VALUE.

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

Пример 1 Найти начало с первого символа в другой строке

=FIND(B3,C3,1)

Find_text	Within_text	Start_num	Результат	Формула	Описание
a	банан	1	2	=FIND(B3,C3,1)	Найдите позицию «а» от первого символа «банан».

=FIND(B4,C4,1)

Find_text	Within_text	Start_num	Результат	Формула	Описание
na	банан	1	3	=FIND(B4,C4,1)	Найдите позицию «na» от первого символа слова «банан».

Пример 2 Найти начало с n-го символа в другой строке

=FIND(B5,C5,3)

Find_text	Within_text	Start_num	Результат	Формула	Описание
a	банан	3	4	=FIND(B5,C5,3)	Найдите букву «а» в третьем знаке слова «банан».

Пример 3 Найти строку с учетом регистра

=FIND(B6,C6,1)

Find_text	Within_text	Start_num	Результат	Формула	Описание
a	банан	1	5	=FIND(B6,C6,1)	Найдите позицию «t» от первого символа «Десять тонн» с учетом регистра.

Скачать образец

Относительные функции:

Excel DOLLAR функцияБыла основана CHAR Функция используется для преобразования числа в текст в формате валюты с округлением десятичных знаков до заданного количества десятичных знаков.

Excel CLEAN функцияБыла основана CLEAN Функция используется для удаления всех непечатаемых символов из данного текста.

Excel CODE функцияБыла основана CODE функция возвращает числовой код символа или первого символа в заданной ячейке текстовой строки.

Excel CONCAT функцияБыла основана CONCAT функция объединяет тексты из нескольких столбцов, строк или диапазонов вместе.

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

В эксель не равно

Основные функции для работы с текстом в VBA Excel:

Функция	Описание
Asc(строка)	Возвращает числовой код символа, соответствующий первому символу строки. Например: MsgBox Asc(“/Stop”). Ответ: 47, что соответствует символу «/».
Chr(код символа)	Возвращает строковый символ по указанному коду. Например: MsgBox Chr(47). Ответ: «/».
Format(Expression, , , )	Преобразует число, дату, время в строку (тип данных Variant (String)), отформатированную в соответствии с инструкциями, включенными в выражение формата. Подробнее…
InStr(, строка1, строка2, )	Возвращает порядковый номер символа, соответствующий первому вхождению одной строки (строка2) в другую (строка1) с начала строки. Подробнее…
InstrRev(строка1, строка2, ])	Возвращает порядковый номер символа, соответствующий первому вхождению одной строки (строка2) в другую (строка1) с конца строки.
Join(SourceArray,)	Возвращает строку, созданную путем объединения нескольких подстрок из массива. Подробнее…
LCase(строка)	Преобразует буквенные символы строки в нижний регистр.
Left(строка, длина)	Возвращает левую часть строки с заданным количеством символов. Подробнее…
Len(строка)	Возвращает число символов, содержащихся в строке.
LTrim(строка)	Возвращает строку без начальных пробелов (слева). Подробнее…
Mid(строка, начало, )	Возвращает часть строки с заданным количеством символов, начиная с указанного символа (по номеру). Подробнее…
Replace(expression, find, replace, , , )	Возвращает строку, полученную в результате замены одной подстроки в исходном строковом выражении другой подстрокой указанное количество раз. Подробнее…
Right(строка, длина)	Возвращает правую часть строки с заданным количеством символов. Подробнее…
RTrim(строка)	Возвращает строку без конечных пробелов (справа). Подробнее…
Space(число)	Возвращает строку, состоящую из указанного числа пробелов. Подробнее…
Split(Expression,,,)	Возвращает одномерный массив подстрок, извлеченных из указанной строки с разделителями. Подробнее…
StrComp(строка1, строка2, )	Возвращает числовое значение Variant (Integer), показывающее результат сравнения двух строк. Подробнее…
StrConv(string, conversion)	Изменяет регистр символов исходной строки в соответствии с заданным параметром «conversion». Подробнее…
String(число, символ)	Возвращает строку, состоящую из указанного числа символов. В выражении «символ» может быть указан кодом символа или строкой, первый символ которой будет использован в качестве параметра «символ». Подробнее…
StrReverse(строка)	Возвращает строку с обратным порядком следования знаков по сравнению с исходной строкой. Подробнее…
Trim(строка)	Возвращает строку без начальных (слева) и конечных (справа) пробелов. Подробнее…
UCase(строка)	Преобразует буквенные символы строки в верхний регистр.
WorksheetFunction.Trim(строка)	Функция рабочего листа, которая удаляет все лишние пробелы (начальные, конечные и внутренние), оставляя внутри строки одиночные пробелы.

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

Как извлечь подстроку перед определенным символом.

Знак в формуле в эксель

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

Если имя и фамилия разделены пробелом, проблема сводится к определению положения этого разделителя. Это можно легко сделать с помощью функции ПОИСК или НАЙТИ .

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

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

Таким же образом вы можете извлечь коды стран из столбца телефонных номеров. Единственное отличие состоит в том, что вам теперь нужно узнать позицию первого дефиса («-«), а не пробела:

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

Как добавить текст к значению ячейки в Excel?

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

Добавление текста к значению ячейки с формулами

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

Если вы хотите добавить текст в начало и конец значения ячейки, функция конкатенации может вам помочь: = СЦЕПИТЬ («текст»; A1; «текст»).

Например, я хочу отдельно добавить текст «Локальный» в начале и добавить текст «Оценка 3» в конце ячейки A2.

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

=CONCATENATE(«Local «,A2,» Grade 3″)

Заметки:

1. Для добавления текста только в начале значения ячейки вы можете использовать формулу: = СЦЕПИТЬ («текст»; A2).
2. Для добавления текста в конце значения ячейки вы можете использовать формулу: = СЦЕПИТЬ (A2; «текст»)

3. Формула = «текст» & A1, = A1 & «текст» or = «текст» & A1 & «текст» тоже может вам помочь. А если вы хотите разделить тексты разделителем (говорит запятая), вы можете добавить «,» в формулу, например = A1 & «,» & «текст».

Если с формулой вам нелегко справиться, вы можете попробовать утилиту Добавить текст в Kutools for Excel.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выделите ячейки, в которые нужно добавить текст, затем щелкните Kutools > Текст > Добавить текст.

2. в Добавить текст диалоговом окне, если вам просто нужно добавить текст перед значением ячейки, введите определенный текст в поле Текст поле выберите Перед первым персонажем и нажмите OK кнопка. Смотрите скриншот:

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

Внимание: Если вы хотите добавить текст отдельно в начало и конец значения ячейки, сначала выберите Перед первым персонажем вариант, а затем щелкните Применить кнопка. Измените текст в Текст поле и выберите После последнего символа вариант и, наконец, нажмите OK для завершения

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

Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

4. Формула РАS (Problem— Attention — Solution)

Problem (проблема).
Attention (внимание, зацепить внимание клиента, допустимо немного преувеличить последствия игнорирования проблемы).

Solution (решение)

Пример продающего текста по формуле PAS:

«Бизнес захватил полностью Вашу жизнь: Вы и директор, и бухгалтер, и HR. Пытались делегировать, но кандидаты не отвечали вашим требованиям? (проблема)

А что дальше? Предприятие будет расти, а Вы так и откладывать свою жизнь на потом? (последствия)

Наше кадровое агентство подберет специалиста в любом направлении. Звоните или пишите по номерам, указанным в шапке профиля. (решение)

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

=ТЕКСТ(числовое_значение_или_формула_в_результате_вычисления_которой_получается_число;формат_ который_требуется_применить_к_указанному_значению)

Для определения формата следует предварительно клацнуть по значению правой кнопкой мышки – и в выпадающем меню выбрать одноименную опцию, либо нажать сочетание клавиш Ctrl+1. Перейти в раздел «Все…». Скопировать нужный формат из списка «Тип».

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

Кликнуть по любому свободному месту, например, G Ввести знак «=» и ссылку на адрес ячейки – B2. Активировать Мастер функций, нажав на кнопку fx (слева) во вкладке «Формулы», или с помощью комбинации клавиш Shift+F3.
На экране отобразится окно Мастера. В строке поиска ввести название функции и нажать «Найти».
В списке нужное название будет выделено синим цветом. Нажать «Ок».
Указать аргументы: ссылку на число и скопированное значение формата. В строке формулы после B2 вписать знак «&».
В результате появится сумма в денежном формате вместе с наименованием товара. Протянуть формулу вниз.

Необходимо объединить текстовые и числовые значения с помощью формулы =A14&» «&»составляет»&» «&B14&»,»&» «&A15&» «&ТЕКСТ(B15;»ДД.ММ.ГГ;@»).

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

Как преобразовать формулу в текстовую строку в Excel?

Обычно Microsoft Excel показывает рассчитанные результаты, когда вы вводите формулы в ячейки. Однако иногда может потребоваться показать только формулу в ячейке, например = СЦЕПИТЬ («000»; «- 2»), как ты с этим справишься? Есть несколько способов решить эту проблему:

Преобразование формулы в текстовую строку с функцией поиска и замены

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

Для решения этой задачи Найти и заменить функция может вам помочь, пожалуйста, сделайте следующее:

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

2. Затем нажмите Ctrl + H , чтобы открыть Найти и заменить диалоговое окно в диалоговом окне под Заменять вкладка, введите равно = войдите в Найти то, что текстовое поле и введите ‘= в Заменить текстовое поле, см. снимок экрана:

3. Затем нажмите Заменить все , вы можете увидеть, что все рассчитанные результаты заменены исходными текстовыми строками формулы, см. снимок экрана:

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

Следующий код VBA также поможет вам легко справиться с этим.

1. Удерживайте другой + F11 ключи в Excel, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модуль, и вставьте следующий макрос в Окно модуля.

Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function

3. В пустой ячейке, например в ячейке D2, введите формулу. = ShowF (C2).

4. Затем щелкните ячейку D2 и перетащите маркер заливки. к тому ассортименту, который вам нужен.

Преобразование формулы в текстовую строку или наоборот одним щелчком мыши

Если у вас есть Kutools for Excel, С его Преобразовать формулу в текст функция, вы можете преобразовать несколько формул в текстовые строки одним щелчком мыши.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите формулы, которые хотите преобразовать.

2. Нажмите Kutools > Content > Преобразовать формулу в текст, и выбранные вами формулы были сразу преобразованы в текстовые строки, см. снимок экрана:

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

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

Демо: преобразование формулы в текстовую строку или наоборот с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Инструмент «текст по столбцам».

Это еще один способ использовать встроенные возможности Excel. При использовании для других целей, например для разделения ячеек, мастер «Текст по столбцам» представляет собой многоэтапный процесс. А вот чтобы просто выполнить нашу метаморфозу, нажимаете кнопку Готово на самом первом шаге 🙂

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

Это все, что нужно сделать!

Примеры

Вывод прямых парных кавычек

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

1
2
3
4
5
6
7
8

SubPrimer1()

‘Вывод одной прямой парной кавычки

MsgBox Chr(34)

‘Отображение текста в прямых кавычках

MsgBox Chr(34)&»Волга»&Chr(34)

‘Вывод 10 прямых парных кавычек подряд

MsgBox String(10,Chr(34))

EndSub

Смотрите интересное решение по выводу прямых кавычек с помощью прямых кавычек в первом комментарии.

Отображение слов наоборот

Преобразование слова «налим» в «Милан»:

1
2
3
4
5
6
7

SubPrimer2()

Dimstroka

stroka=»налим»

stroka=StrReverse(stroka)’милан

stroka=StrConv(stroka,3)’Милан

MsgBox stroka

EndSub

или одной строкой:

1
2
3

SubPrimer3()

MsgBox StrConv(StrReverse(«налим»),3)

EndSub

Преобразование слова «лето» в «отель»:

1
2
3
4
5
6
7

SubPrimer4()

Dimstroka

stroka=»лето»

stroka=StrReverse(stroka)’отел

stroka=stroka&»ь»‘отель

MsgBox stroka

EndSub

или одной строкой:

1
2
3

SubPrimer5()

MsgBox StrReverse(«лето»)&»ь»

EndSub

Печатная машинка

Следующий код VBA Excel в замедленном режиме посимвольно печатает указанную строку на пользовательской форме, имитируя печатную машинку.

Для реализации этого примера понадобится пользовательская форма (UserForm1) с надписью (Label1) и кнопкой (CommandButton1):

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SubStopSub(Pause AsSingle)

DimStart AsSingle

Start=Timer

DoWhileTimer<Start+Pause

DoEvents

Loop

EndSub

PrivateSubCommandButton1_Click()

Dimstroka AsString,iAsByte

stroka=»Печатная машинка!»

Label1.Caption=»»

Fori=1ToLen(stroka)

CallStopSub(0.25)’пауза в секундах

‘следующая строка кода добавляет очередную букву

Label1.Caption=Label1.Caption&Mid(stroka,i,1)

EndSub

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

Как посчитать, содержит ли ячейка текст или часть текста в Excel?

Предположим, у вас есть данные, указанные ниже, и вы хотите подсчитать количество ячеек, содержащих текст «Apple», количество ячеек, содержащих текст»Оранжевая»и ячейки содержат»персик»отдельно, как это сделать? В этом руководстве мы объясняем COUNTIF Подробная функция, которая поможет вам быстро подсчитать количество ячеек, если они содержат текст или часть определенного текста в Excel. Кроме того, мы представляем замечательную функцию, позволяющую легко достичь ее с помощью только щелчков мышью.

Подсчитайте, если ячейка содержит текст или часть текста, с помощью функции СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ может помочь подсчитать ячейки, содержащие часть текста в диапазоне ячеек в Excel. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку (например, E5), скопируйте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер заполнения вниз, чтобы получить все результаты.

=COUNTIF(B5:B10,»*»&D5&»*»)

Синтаксис

=COUNTIF (range, criteria)

аргументы

Диапазон (обязательно): диапазон ячеек, которые вы хотите подсчитать.
Критерии (обязательно): число, выражение, ссылка на ячейку или текстовая строка, определяющая, какие ячейки будут учитываться.

Заметки:

В формуле B5: B10 — это диапазон ячеек, который нужно подсчитать. D5 — это ссылка на ячейку, содержащую то, что вы хотите найти. Вы можете изменить ссылочную ячейку и критерии в формуле по своему усмотрению.
Если вы хотите напрямую вводить текст в формуле для подсчета, примените следующую формулу:=COUNTIF(B5:B10,»*Apple*»)
В этой формуле регистр не учитывается.

Была основана Выбрать определенные ячейки полезности Kutools for Excel может помочь вам быстро подсчитать количество ячеек в диапазоне, если они содержат определенный текст или часть текста. После получения результата во всплывающем диалоговом окне все совпавшие ячейки будут выбраны автоматически. .Загрузите Kutools for Excel прямо сейчас! (30-дневная бесплатная трасса)

Счетные ячейки содержат текст с функцией СЧЁТЕСЛИ

Как показано на скриншоте ниже, если вы хотите подсчитать количество ячеек в определенном диапазоне, которые содержат только текст, метод в этом разделе может вам помочь.

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

=COUNTIF(B5:B10,»*»)

Подсчитайте, если ячейка содержит текст или часть текста с помощью Kutools for Excel

Чаевые: Помимо приведенной выше формулы, здесь представлена замечательная функция, позволяющая легко решить эту проблему. С Выбрать определенные ячейки полезности Kutools for Excel, вы можете быстро подсчитать, содержит ли ячейка текст или часть текста, щелкнув мышью. С помощью этой функции вы даже можете подсчитать с помощью OR или AND, если вам нужно. Пожалуйста, сделайте следующее.

Перед использованием Kutools for Excel, вам нужно потратить несколько минут, чтобы в первую очередь.

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

2. Нажмите Kutools > Выберите > Выбрать определенные ячейки.

3. в Выбрать определенные ячейки диалоговое окно, вам необходимо:

Выберите Ячейка вариант в Тип выбора раздел;
В Конкретный тип раздел, выберите Комплект в раскрывающемся списке введите Apple в текстовом поле;
Нажмите OK кнопку.
Затем появляется окно подсказки, в котором указано, сколько ячеек соответствует условию. Щелкните значок OK кнопка и все соответствующие ячейки выбираются одновременно.

Наконечник. Если вы хотите получить бесплатную (60-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Используйте countif с несколькими критериями в Excel В Excel функция СЧЁТЕСЛИ может помочь нам вычислить количество определенного значения в списке. Но иногда нам нужно использовать несколько критериев для подсчета, это будет сложнее. Из этого туториала Вы узнаете, как этого добиться.Нажмите, чтобы узнать больше …

Подсчитайте, начинаются ли ячейки или заканчиваются определенным текстом в Excel Предположим, у вас есть диапазон данных, и вы хотите подсчитать количество ячеек, которые начинаются с «kte» или заканчиваются «kte» на листе. Эта статья знакомит вас с некоторыми хитростями вместо ручного подсчета.Нажмите, чтобы узнать больше …

Подсчитайте конкретное значение на нескольких листах Как можно подсчитать конкретные значения на нескольких листах? Например, получить количество вхождений определенного значения «Excel» из нескольких листов. В этом руководстве представлены методы, позволяющие это сделать.Нажмите, чтобы узнать больше …

Работа с формулами

Ввод

Программа понимает стандартные математические операторы:

сложение «+»;
вычитание «-»;
умножение «*»;
деление «/»;
степень «^»;
меньше «<»;
больше «>»;
меньше или равно «<=»;
больше или равно «>=»;
не равно «»;
процент «%».

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

«%» и «^»;
«*» и «/»;
«+» и «-».

Постоянные и абсолютные ссылки

Относительные ссылки помогают «растянуть» одну формулу на любое количество столбцов и строк. Как это работает на практике:

Создать таблицу с нужными данными.

Чтобы найти общую стоимость за каждый товар, нужно количество единиц умножить на цену. Для этого в первую ячейку вписать формулу через знак «=» и нажать Enter.

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

Абсолютный адрес обозначается знаком «$». Форматы отличаются:

Неизменна строка – A$1.
Неизменен столбец – $A
Неизменны строка и столбец – $A$1.

Сначала посчитать общую стоимость. Это делается несколькими способами: обычным сложением всех значений или автосуммой (выделить столбец с одной пустой ячейкой и на вкладке «Главная» справа выбрать опцию «Сумма», либо вкладка «Формулы» – «Автосумма»).

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

Для получения результата в процентах можно произвести умножение на 100. Однако проще выделить ячейку и выбрать в разделе «Главная» формат в виде значка «%».

С помощью маркера заполнения опустить формулу вниз. В итоге должно получиться 100%.

Функция ПСТР

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

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

Пример использования:

Из текста, находящегося в ячейке A1 необходимо вернуть последние 2 слова, которые имеют общую длину 12 символов. Первый символ возвращаемой фразы имеет порядковый номер 12.

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

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

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

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

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

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

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

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

Как заставить ЛЕВСИМВ возвращать число.

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

Итак, как заставить ЛЕВСИМВ выводить числовое значение, а не текстовую строку, состоящую из цифр? Просто заключив его в функцию ЗНАЧЕН (VALUE), которая предназначена для преобразования текста, состоящего из цифр, в число.

Например, чтобы извлечь символы перед разделителем “-” из A2 и преобразовать результат в число, можно сделать так:

Результат будет выглядеть примерно так:

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

Это лишь некоторые из множества возможных вариантов использования ЛЕВСИМВ в Excel.

Дополнительные примеры формул ЛЕВСИМВ можно найти на следующих ресурсах: