Как Excel поможет Вам заработать на инвестициях и не только (с примерами формул)

Я – фундаментальный инвестор и это означает, что я анализирую финансовые и иные показатели работы компаний, публикуемые в их отчетности с целью найти недооцененные на текущий момент компании с высоким потенциалом роста. Огромное количество различной финансовой информации я перекладываю в таблицу и работаю с ней с помощью Excel или GoogleSheets (если Вам не интересны инвестиции, возможно в своей работе вы сталкиваетесь с бюджетами, отчетами о продажах, списками, перечнями и др. табличной информацией – статья точно будет Вам полезна).

Мы будем рассматривать работу с двумя видами данных – текстом и числами. Это два наиболее часто встречающихся формата. Тема будет разбита на несколько частей – подписывайтесь на VC или телеграмм-канал, чтоб не пропустить выход новых частей.

Например, нам нужно оставить только почтовый индекс в адресе или только фамилию в ФИО, только город из адреса и тому подобные задачи.

Допустим у нас есть текст в одной ячейке вида «Иванов Иван Иванович» или «123456, г.Москва, Красная площадь, д.1» (см. скриншот).
Очевидно, что в ячейках есть лишние пробелы. Функцией =СЖПРОБЕЛЫ мы уберем лишние пробелы.:

Затем скопируйте результат в колонку А как значение (иначе будет ошибка #ССЫЛКА !).

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

Здесь мы изучим следующие формулы: =НАЙТИ (ПОИСК), =ДЛСТР, =ЛЕВСИМВ, =ПРАВСИМВ, =СЦЕПИТЬ.

Обратите внимание, что если нужно просто ячейку разбить по столбцам, для этого есть встроенная возможность во вкладке «Данные» - Текст по столбцам. Останавливаться на ней не буду – это очень простая и интуитивно понятная возможность в Excel

Сначала я покажу как все работает, а затем постараюсь объяснить логику:

Функция =НАЙТИ ищет первый знак в ячейке, который Вы хотите найти слева направо - у Иванова С.П. мы ищем букву «о» (важно указывать в кавычках и помнить о регистре: «О» и «о» - разные знаки). Ей нужно минимум два аргумента – первый «Что ищем?» и второй – «Где ищем?». Есть еще третий – «С какого знака ищем?», но, если его не задать, будет искать с начала. Как видим, буква «о» в ячейке А2, которая содержит текст «Иванов Иван Иванович» впервые слева направо встречается на пятой позиции, т. е. 5 по порядку, о чем нам сообщила формула. Если Вам не важен регистр и нужно искать любую букву «о», используйте функцию =ПОИСК.

Функция =ЛЕВСИМВ возвращает, левые символы. У нее два аргумента «Где ищем?» и «Сколько левых символов забрать?». А функция =ПРАВСИМВ делает тоже самое, только начинает справа.

Функция =ДЛСТР считает количество знаков в ячейке. Знаком в этом случае является что угодно – цифра, буква, знак препинания, пробел. Аргумент один – «Где считаем?».

Функция =СЦЕПИТЬ - сцепляет содержимое любых ячеек и преобразует их в текст. В ячейке В16, мы сцепили Иванова Сергея Петровича из ячейки А14 и адрес из ячейки А15, а между ними мы добавили текст « живет по адресу » (результат в ячейке С16, см. скриншот). Обратите внимание, что текст, даже если это просто пробел, в функциях всегда обособляется кавычками.

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

Чтобы найти второй пробел, нужно найти первый пробел (=НАЙТИ(« »;ЯЧЕЙКА ГДЕ ИСКАТЬ)), а затем найти второй пробел, то есть к указанной функции добавить третий аргумент - «С какого знака ищем?» (см. чуть выше) и добавить единицу (потому что если не добавить единицу, то формула вернет нам первое же вхождение пробела – тот с которого и надо считать, а значит вернет «Иванов» Если же добавить единицу, то формула начнет поиск с букву «С» в слове Сергей).

С запятыми в адресе почти тоже самое, с оговоркой лишь, что формула выше (ячейка B10 на скриншоте) всегда возвращает нам значение с последним знаком на конце («Иванов Сергей » - то есть с пробелом на конце). Если последний знак мешает – уберите его путем дописки -1 к аргументу, отвечающему за «Сколько левых символов забрать» для функции ЛЕВСИМВ.

Чтобы забрать текст из середины, логично, что можно это делать через функцию =ЛЕВСИМВ и =ПРАВСИМВ. На скриншоте в ячейке В12 показан пример через =ПРАВСИМВ, где первым аргументом «Где ищем?» задается формула для того, чтобы забрать фамилию и имя, а вторым – что правые символы забираются до пробела между фамилией и именем. А значит, нам нужны только правые символы до пробела между фамилией и именем.

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

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

P.S. Указанные формулы одинаково работают в Excel и GoogleSheets, однако между аргументами в русских версиях указанных программ используется точка с запятой (;), а в зарубежных – запятая (,).

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

Тут же оставлю ссылку на пост про таблицы, которыми пользуюсь сам.

#excel #googlesheets #лайфхак #таблицы #формулы