Чистка текста
Выгрузка из CRM редко готова к работе сразу. Вот заказы за день: на листе четыре заказа из Перми, а СЧЁТЕСЛИ в C8 насчитала два. СУММ в C9 показывает 0, хотя суммы на месте.
Чтобы по выгрузке можно было считать, её готовят к работе:
- находят и убирают то, что мешает СЧЁТЕСЛИ узнать Пермь;
- превращают суммы в числа, которые сложит СУММ;
- склеивают фамилию и имя в одну ячейку для рассылки.
Пробел тоже символ
В C3 и C6 по шесть символов. Шестой — пробел: в C3 он стоит после слова, в C6 перед ним. Excel сравнивает текст символ за символом, поэтому «Пермь» с пробелом и просто «Пермь» для него разные слова, хотя на листе их не отличить.
Такие пробелы оставляет программа, из которой взяли данные, или человек, вводивший город вручную. Убирает их функция СЖПРОБЕЛЫ: она стирает пробелы в начале и в конце текста, а несколько пробелов подряд между словами заменяет одним. Аргумент у неё один, ячейка с текстом: =СЖПРОБЕЛЫ(A2).
Формулу пишут в соседний пустой столбец и протягивают вниз. Но СЧЁТЕСЛИ смотрит в исходный столбец, поэтому чистый текст возвращают туда. Ячейки с формулами копируют, а в исходный столбец вставляют через меню правой кнопки: «Специальная вставка» → «Значения». Обычная вставка перенесла бы формулы, а эта кладёт только готовый текст.
Суммы с припиской
СУММ выдала 0, потому что к каждой сумме приписано « руб.», а в «2490.50» дробная часть отделена точкой вместо запятой. Excel видит в таких ячейках текст и пропускает их.
Лишние символы убирает окно «Найти и заменить». Оно открывается клавишами Ctrl + H (на Mac ⌘ + Shift + H) или на вкладке «Главная»: «Найти и выделить» → «Заменить…».
- Выделите ячейки, которые нужно почистить. Если выделена одна ячейка, Excel ищет по всему листу.
- В поле «Найти» впишите то, что мешает, например « руб.» с пробелом в начале. Поле «Заменить на» оставьте пустым.
- Нажмите «Заменить все». Excel сообщит, сколько замен сделал.
Склейка через &
Для рассылки нужно полное имя клиента в одной ячейке, а CRM выгружает фамилию и имя в разные столбцы. Склеить их можно знаком &. Он соединяет текст из ячеек по порядку, а пробел между словами вписывают отдельным кусочком в кавычках:
=A2&" "&B2
В кавычках может стоять любой текст, не только пробел. Так собирают, например, приветствие для письма: ="Здравствуйте, "&B2&"!".
Результат склейки всегда текст, даже если склеивали числа. Приклеить к сумме « ₽» значит снова получить текст вроде «1500 ₽», который СУММ пропустит. Чтобы рядом с числом стоял знак рубля, ячейке ставят денежный формат.
& в условиях: порог из ячейки
Крупным считается заказ дороже 5 000 ₽, но порог иногда меняют. Чтобы не переписывать формулы, его держат в отдельной ячейке, например в F1. Условие из одной ячейки F1 нашло бы только суммы, равные порогу. А в ">F1" всё стоит в кавычках, и F1 для функции просто две буквы, а не ячейка.
Выручает то, что условие для СЧЁТЕСЛИ и так текст, а текст собирают знаком &. Знак сравнения пишут в кавычках, а число приклеивают из ячейки:
=СЧЁТЕСЛИ(B2:B9;">"&F1)
Если в F1 стоит 5000, склейка ">"&F1 даёт текст «>5000», то есть то самое условие, которое раньше писали в кавычках целиком. Поменяли порог, и условие собралось заново.
Так же работают СУММЕСЛИ и СУММЕСЛИМН: условие с порогом из ячейки в них собирают той же склейкой.