Чистая база клиентов
Выгрузки клиентов из разных форм содержат лишние пробелы, разный регистр и повторные адреса. Подготовьте столбцы для загрузки в CRM, сохранив исходные записи.
Задание
Все адреса ниже относятся к листу «Работа».
Нормализуйте имена и почту
E5:F22В E5:E22 замените неразрывные пробелы обычными, уберите лишние пробелы и запишите каждое слово с заглавной буквы. В F5:F22 уберите крайние пробелы и переведите почту в нижний регистр.
Подготовьте телефоны и домены
G5:H22В G5:G22 удалите из телефона символы +, пробел, (, ) и дефис. Верните текст из 11 цифр, начинающийся с 7. В H5:H22 извлеките часть очищенного email справа от @.
Отметьте повторы
I5:I22, B28:B29В I5:I22 поставьте «Дубль», если очищенный email встречается больше одного раза, иначе «Уникальный». Отметьте все строки повтора, включая первую. В B28:B29 посчитайте строки обоих типов.
Требования к решению
- Заполните жёлтые ячейки формулами. Можно использовать другие формулы с тем же результатом.
- Имена в выгрузке записаны латиницей; переводить их на русский не нужно.
- Все адреса в исходных данных содержат один знак @. Проверка существования почтового ящика не требуется.
- Телефоны и ID должны оставаться текстом. В именах встречается неразрывный пробел с кодом 160.
Подсказки к заданию
PremiumДоступ открывается после покупки.
Выбрать PremiumНапомнить, как работает ПРОПНАЧ
Делает первую букву каждого слова прописной.
Синтаксис=ПРОПНАЧ(text)=PROPER(text)
text- Исходный текст или ссылка на ячейку с текстом.
Делает прописной первую букву после любого небуквенного символа, остальные буквы переводит в нижний регистр.
Аббревиатуры и собственные имена после такого преобразования могут потребовать правки.
Напомнить, как работает СЖПРОБЕЛЫ
Удаляет лишние пробелы из текста, оставляя одиночные пробелы между словами.
Синтаксис=СЖПРОБЕЛЫ(text)=TRIM(text)
text- Исходный текст или ссылка на ячейку с текстом.
Удаляет обычные пробелы с кодом 32 по краям и оставляет по одному внутри строки.
Неразрывный пробел с кодом 160 нужно предварительно заменить, например через ПОДСТАВИТЬ.
Напомнить, как работает ПОДСТАВИТЬ
Заменяет один текстовый фрагмент другим по содержанию.
Синтаксис=ПОДСТАВИТЬ(text; old_text; new_text; [instance_num])=SUBSTITUTE(text, old_text, new_text, [instance_num])
text- Исходный текст или ссылка на ячейку с текстом.
old_text- текст, который нужно заменить
new_text- новый текст
instance_num— необязательный- Номер заменяемого вхождения. Если пропущен, заменяются все вхождения.
Без instance_num заменяет все вхождения; с этим аргументом только указанное.
Замена учитывает регистр. =ПОДСТАВИТЬ("А-Б-А";"А";"В";2) возвращает "А-Б-В".
Напомнить, как работает ЮНИСИМВ
Возвращает символ Unicode по его числовому коду.
Синтаксис=ЮНИСИМВ(number)=UNICHAR(number)
number- Число для вычисления.
Возвращает символ по кодовой точке Unicode. Ноль и недопустимые кодовые точки вызывают ошибку.
Напомнить, как работает СТРОЧН
Преобразует текст в нижний регистр.
Синтаксис=СТРОЧН(text)=LOWER(text)
text- Исходный текст или ссылка на ячейку с текстом.
Меняет регистр букв. Цифры, пробелы и знаки остаются без изменения.
Напомнить, как работает ПРАВСИМВ
Возвращает заданное число символов справа.
Синтаксис=ПРАВСИМВ(text; [num_chars])=RIGHT(text, [num_chars])
text- Исходный текст или ссылка на ячейку с текстом.
num_chars— необязательный- Количество извлекаемых символов. По умолчанию 1, должно быть неотрицательным.
=ПРАВСИМВ("Excel";2) возвращает "el". Без num_chars возвращается один символ.
Напомнить, как работает ДЛСТР
Возвращает количество символов в тексте.
Синтаксис=ДЛСТР(text)=LEN(text)
text- Исходный текст или ссылка на ячейку с текстом.
Считает также пробелы и другие символы строки.
В книгах с версией совместимости 2 суррогатная пара Unicode считается одним символом; селекторы вариантов считаются отдельно.
Напомнить, как работает НАЙТИ
Находит позицию текста внутри строки с учётом регистра.
Синтаксис=НАЙТИ(find_text; within_text; [start_num])=FIND(find_text, within_text, [start_num])
find_text- искомый текст
within_text- текст, в котором выполняется поиск
start_num— необязательный- Номер символа, с которого начинается поиск. По умолчанию 1.
Учитывает регистр и не поддерживает подстановочные знаки. Нумерация символов начинается с 1.
Если фрагмент не найден, возвращается #ЗНАЧ!.
Напомнить, как работает СЧЁТЕСЛИ
Подсчитывает количество ячеек, удовлетворяющих условию
Синтаксис=СЧЁТЕСЛИ(range; criteria)=COUNTIF(range, criteria)
range- диапазон ячеек
criteria- условие отбора
Первый аргумент должен быть диапазоном ячеек. Условие с оператором записывается в кавычках, например ">0".
Текстовое сравнение не учитывает регистр; * и ? являются подстановочными знаками, тильда их экранирует.
Напомнить, как работает ЕСЛИ
Проверяет условие и позволяет вернуть разные результаты для ИСТИНА и ЛОЖЬ.
Синтаксис=ЕСЛИ(logical_test; value_if_true; [value_if_false])=IF(logical_test, value_if_true, [value_if_false])
logical_test- условие, которое нужно проверить
value_if_true- результат, если условие истинно
value_if_false— необязательный- результат, если условие ложно
=ЕСЛИ(A1>=60;"Зачёт";"Незачёт") выбирает результат по условию.
Результат "" выглядит пустым, но ячейка содержит формулу. ЕПУСТО вернёт ЛОЖЬ, а СЧЁТЗ учтёт такую ячейку.