Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

src="images/i_283.jpg"/>

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

Как превратить текст в число? Функция ЛЕВСИМВ / LEFT здесь для примера, на ее месте может быть любая функция, которая возвращает текстовую строку (из которой вам нужно сделать «настоящее» число, пригодное для вычислений).

1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения:

=-ЛЕВСИМВ(…)

2. С помощью функции ЗНАЧЕН / VALUE:

=ЗНАЧЕН(ЛЕВСИМВ(…))

3. С помощью еще какой-нибудь математической операции, не меняющей значение, например умножения на единицу:

=ЛЕВСИМВ(…)*1

Регулярные выражения

Google Таблица с примерами: Регулярные выражения

https://mif.to/ywyPVn

Регулярные выражения (Regular Expressions или RegExp) позволяют решать довольно сложные задачи по поиску, извлечению и замене текстовых строк. Они нужны для решения задач с динамическими текстовыми строками.

Для тривиальных задач с фиксированным текстом (например, когда нужно просто найти/заменить определенное слово) подходят и обычные текстовые функции Excel или Google Таблиц (как НАЙТИ / FIND или ПОДСТАВИТЬ / SUBSTITUTE) и окно «Найти и заменить».

Если нужно вырезать первые шесть символов (допустим, если в полном почтовом адресе всегда указан индекс, всегда в начале и всегда из шести символов), подойдет ЛЕВСИМВ / LEFT, которая извлекает заданное количество символов из начала текстовой строки в ячейке.

Кот Лемур, автор многих примеров в этой книге, изучает книгу «Регулярные выражения» Джеффри Фридла. Помимо нее он рекомендует книгу Бена Форта «Изучаем регулярные выражения»

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

— извлечь дату из текстового описания транзакции;

— извлечь индекс из полного почтового адреса;

— проверить, есть ли в ячейке артикул товара;

— или извлечь этот самый артикул;

— найти товары с двумя или тремя портами HDMI в описании (2xHDMI или 3xHDMI), но не одним или более чем тремя (не 1xHDMI или что-то еще).

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

Регулярные выражения поддерживаются разными языками программирования (например, JavaScript, Java, Perl, PHP) и разными инструментальными средствами (например, Word или Google Таблицы). К сожалению, в Excel нет встроенных функций для работы с «регулярками», но можно создать пользовательскую функцию на VBA. А вот в Google Таблицах такие функции есть «из коробки». Их три (названия у этих функций только на английском):

• REGEXMATCH для проверки текста на соответствие регулярному выражению (возвращает ИСТИНА / TRUE или ЛОЖЬ / FALSE);

• REGEXREPLACE для замены части текста, соответствующей регулярному выражению, на другой текст;

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

Кроме того, в Google Таблицах можно фильтровать данные с условием, заданным в виде регулярного выражения, с помощью функции QUERY.

ЯЗЫК РЕГУЛЯРНЫХ ВЫРАЖЕНИЙ

Давайте рассмотрим основные элементы, из которых собираются шаблоны — регулярные выражения.

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

Просто текст без всяких знаков — это вполне себе регулярное выражение, хоть и очень простое.

Лемур

Это совпадение с конкретным словом «Лемур» в тексте — именно в таком регистре (регулярные выражения чувствительны к регистру).

Привет, Лемур!

_____________________

. (точка) — любой символ. Например, «Ро. линг» — две конкретные буквы, далее любой символ, далее еще четыре конкретных буквы. Такое регулярное выражение будет соответствовать и фамилии «Роулинг», и другим вариантам написания с ошибкой (например, «Ровлинг»), и вообще любому варианту написания, где на третьей позиции будет любой символ.

Ро. линг

В некоторых ячейках была опечатка в фамилии Роулинг — она была указана как Ровлинг, а то и как Роуллинг.

«Роуллинг» нашему регулярному выражению не соответствует — на месте точки может быть только один символ.

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

_____________________

[символы] — символы в квадратных скобках — это набор из нескольких символов, которые могут быть на соответствующей позиции в текстовой строке.

Например:

Ле[мд]ур — это один из вариантов «Лемур» или «Ледур», но не что-то иное.

Если кот вытворял что-то эдакое, мы могли в шутку пожурить его: "Лемур-Ледур", но быстро прощали любимого Ле Мура.

Можно использовать дефис, чтобы обозначать диапазоны символов. Например, [0–6] и [0123456] идентичны.

[^символы] — если перед набором символов добавить «крышечку» (^), то это будет отрицанием, то есть любым символом, кроме перечисленных в квадратных скобках.

_____________________

d — любая цифра. Идентично выражению [0–9].

Регистр здесь имеет значение. D — это уже любой символ, кроме цифры.

_____________________

w — любая латинская буква в любом регистре, любая цифра или нижнее подчеркивание.

W — любой символ, кроме перечисленных (то есть «не w», не латинская буква, не цифра и не подчеркивание).

_____________________

s — любой пробельный символ (непечатаемые символы: пробел, перевод строки, табуляция и другие).

S — соответственно, любой не пробельный символ.

_____________________

| (вертикальная черта) — один из вариантов, «или — или». Например:

USD|EUR

Функция REGEXMATCH в Google Таблицах возвращает ИСТИНА / TRUE, если в ячейке (ссылка на нее — первый аргумент функции) есть текст, соответствующий регулярному выражению (указанному во втором аргументе этой функции)

_____________________

? (знак вопроса) — ничего или один указанный символ.

Например:

Леs?Мур

Такому выражению будут соответствовать и варианты с одним переносом строки, и с одним пробелом «Ле Мур», и слитное написание «ЛеМур». Варианты с несколькими пробелами не подойдут.

Слитное написание «Лемур» не будет соответствовать такому выражению — из-за регистра. Чтобы учесть варианты и «ЛеМур», и «Лемур», можно указать обе буквы в квадратных скобках, напомним: это означает один символ из набора.

_____________________

+ (плюс) — одно или несколько повторений символа.

Например, s + — один или более пробелов.

Леs + Мур — будет соответствовать вариантам «Ле Мур», «Ле     Мур» (много пробелов между словами), «Ле Мур» с переносом строки между словами, но не вариантам «ЛеМур» или «Лемур».

_____________________

* (звездочка) — ноль, одно или несколько повторений символа.

Если заменить в предыдущем примере плюс на звездочку, то такому регулярному выражению

Леs*Мур

будет соответствовать и вариант «ЛеМур» (но не «Лемур» — из-за регистра).

_____________________

{количество повторений} — в фигурных скобках можно задать точное число повторений