Тонкости Excel. Полезные формулы

Содержит ли ячейка нужные символы

В таблице из 13 тысяч строк нужно было посчитать количество ячеек определенных. Если ячейка содержит нужные нам символы, к примеру «4×4», создаём где нам нужно ячейку, в которой пишем формулу:

=—НЕ(ЕОШ(ПОИСК(«4×4»;D4)))

В итоге в новой ячейке возвращается 1, если содержит ячейка «4×4» и если не содержит, то 0. Затем суммируя 1 и 0 увидим сколько чего.

Проверка ячейки A2 на наличие буквы «ю» (Верно):

=ЕСЛИ(ЕЧИСЛО(ПОИСК(«ю»;A2));»Верно»; «Неверно»)

Убрать некоторые символы до определенного места

Убираем все символы в строке до слова «Цвет»:

=ПСТР(RC[-1];НАЙТИ(«Цвет»;RC[-1];1)+1;ДЛСТР(RC[-1])-НАЙТИ(«Цвет»;RC[-1];1))

Удаляем всё после символа «,»

=ПСТР(ячейка_где_текст;1;ПОИСК(«,»;ячейка_где_текст)-1) 

Поиск по ячейке с условием (если содержит определенный текст)

=ЕСЛИ(ЕЧИСЛО(ПОИСК(«искомые символы»;искомая ячейка;с какой позиции начать));»что вывести если истина»;»что вывести если ложь»)

Подсчет слов в ячейке после указанного символа

Считаем слова (между ними пробел) в ячейке В14 после символа №:

=ДЛСТР(СЖПРОБЕЛЫ(ПСТР(B14;НАЙТИ(«№»;B14;1);1000)))-ДЛСТР(ПОДСТАВИТЬ(ПСТР(B14;НАЙТИ(«№»;B14;1);1000);» «;»»))

Расчет времени между датами

Следующая формула возвращает возраст в годах, месяцах, днях относительно даты в ячейке B2:

=РАЗНДАТ(B2;СЕГОДНЯ();»y»)&» лет «&РАЗНДАТ(B2;СЕГОДНЯ();»ym»)&» мес. «&РАЗНДАТ(B2;СЕГОДНЯ();»md»)&» дн.»

Нумерация строк, которая сохраняется при удалении строк из середины списка

Формулу ставить в В2 и растянуть вниз:

=МАКС($B$1:B1)+1

Проссумировать время

Возникла задача проссумировать время разговоров по отчету из Wintarrif для АТС. Самое интересное, что имея продолжительность каждого разговора я не мог посчитать их сумму. Но решение нашлось. Сумма не считалась, т.к. формат был текстовый, хотя формат я задавал принудительно — «время», проблема в том что так поменять нельзя, если изначально отчет генерировался по такому формату, такое бывает.

Можно сразу понять, по умолчанию, если значение в ячейке (к примеру «0:00:44») выравнено к левому краю, значит да, проссумировать не получится.

Для всех ячеек с данными вида «0:01:04» (продолжительность разговора или любое значение времени формата «37:30:55») выбираем формат [ч]:мм:cc.

Выделяем диапазон с данными и нажимаем Данные — Текст по столбцам. Пропускаем первых два шага нажимая «Далее», затем идём в «Дополнительно».  Разделитель целой и дробной части ставим точку, а разделитель разрядов — верхнюю одну кавычку.

После нажатия на «Готово» Excel преобразует наш текст в нормальные числа.

Также видим, что выравнивание стало по правому краю, теперь сумма считается.

Поиск символов в ячейках и вывод исходя из условий поиска

Среди столбца А будем искать слово «шипы», формулу ставим в «В», протягиваем вниз.

=ЕСЛИ(ЕЧИСЛО(ПОИСК(«шипы»;A1;1));»Да»;»Нет»)

Усложним, ищем «4×4» и если находим, то выводим «Легкогрузовые (С)», не находим — тогда выводим «Легковые»

=ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК(«4×4»;A11452));ЕЧИСЛО(ПОИСК(«C»;E11452)));»Легкогрузовые (С)»;»Легковые»)

Поиском по ячейке (d1 и затем протягивая вниз) смотрим, чтобы число было больше 0, но меньше 60, если да, то выводим «Низкопрофильные»

=ЕСЛИ(И(D1>0;D1<=60);»Низкопрофильные»;»»)

Помогла статья http://gigabaza.ru/doc/45096.html и http://www.planetaexcel.ru/techniques/7/1091/