Обзор функций для работы с текстом
Мы подготовили обзорное видео, где собрали полезные сочетания текстовых функций в 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 таблицах все работает аналогично.
Как удалить лишние пробелы в Excel и 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 таблицах: сложные формулы, дашборды, макросы и многое другое.