БИБЛИОТЕКА ФОРМУЛ
Готовые формулы под частые задачи
Разбор самых частых вопросов по Excel и Google Таблицам: готовая формула, объяснение и вариант под вашу версию. Не нашли своё — опишите задачу словами.
- ВПР по двум условиям
=ИНДЕКС(C:C;ПОИСКПОЗ(1;(A:A=F1)*(B:B=G1);0)) - Ошибка Н/Д в ВПР — как убрать
=ЕСЛИОШИБКА(ВПР(A2;D:E;2;ЛОЖЬ);"не найдено") - СУММЕСЛИМН по датам за период
=СУММЕСЛИМН(C:C;A:A;">="&D1;A:A;"<="&D2) - Как посчитать рабочие дни между датами
=ЧИСТРАБДНИ(A2;B2) - Как убрать дубликаты формулой
=УНИК(A2:A100) - Как склеить ячейки через запятую
=ОБЪЕДИНИТЬ(", ";ИСТИНА;A2:A10) - Как посчитать процент от суммы
=B2/СУММ(B:B) - ЕСЛИ с несколькими условиями
=ЕСЛИМН(A2>=90;"отлично";A2>=70;"хорошо";A2>=50;"средне";ИСТИНА;"плохо") - СУММЕСЛИ по части текста
=СУММЕСЛИ(A:A;"*молоко*";B:B) - Как округлить число в Excel
=ОКРУГЛ(A2;2) - Последний день месяца по дате
=КОНМЕСЯЦА(A2;0) - Как посчитать непустые ячейки
=СЧЁТЗ(A:A) - ВПР с другого листа
=ВПР(A2;Прайс!A:B;2;ЛОЖЬ) - Как извлечь число из текста
=ПРОСМОТР(9E+307;--ПСТР(A2;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(--ПСТР(A2;СТРОКА($1:$99);1));0);99)) - Сумма по месяцу из даты
=СУММПРОИЗВ((МЕСЯЦ(A2:A100)=F1)*(ГОД(A2:A100)=F2)*B2:B100) - Как сравнить два столбца в Excel на совпадения
=ЕСЛИ(ЕНД(ПОИСКПОЗ(A2;B:B;0));"нет в B";"есть в B") - Как посчитать количество ячеек по условию
=СЧЁТЕСЛИ(A:A;">100") - Среднее значение по условию в Excel
=СРЗНАЧЕСЛИ(A:A;"Москва";B:B) - Как объединить текст из нескольких ячеек
=A2&" "&B2&" "&C2 - Как разделить текст в ячейке на столбцы формулой
=ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1) - Формула ЕСЛИ ячейка содержит текст
=ЕСЛИ(ЕЧИСЛО(ПОИСК("кот";A2));"да";"нет") - Как убрать лишние пробелы в Excel
=СЖПРОБЕЛЫ(A2) - Как узнать номер недели по дате
=НОМНЕДЕЛИ(A2;2) - Как получить день недели из даты словом
=ТЕКСТ(A2;"ДДДД") - Как заменить часть текста в ячейке формулой
=ПОДСТАВИТЬ(A2;"старый";"новый") - Как посчитать возраст по дате рождения в Excel
=РАЗНДАТ(A2;СЕГОДНЯ();"y") - Как округлить число до сотен или тысяч
=ОКРУГЛ(A2;-2) - ВПР по части текста (частичное совпадение)
=ВПР("*"&A2&"*";D:E;2;ЛОЖЬ) - Как посчитать сумму только видимых ячеек
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B:B) - Как найти значение последней непустой ячейки
=ПРОСМОТР(2;1/(A:A<>"");A:A) - Как посчитать процент выполнения плана
=B2/C2 - Как убрать ошибку ДЕЛ/0 в Excel
=ЕСЛИОШИБКА(A2/B2;0) - Как найти номер строки по значению
=ПОИСКПОЗ(F1;A:A;0) - Как сгенерировать случайное число в диапазоне
=СЛУЧМЕЖДУ(1;100) - Как перевести число в текст прописью
=ТЕКСТ(A2;"# ##0,00") - СУММЕСЛИ по одному условию
=СУММЕСЛИ(A:A;"Москва";B:B) - СУММЕСЛИМН по нескольким условиям
=СУММЕСЛИМН(C:C;A:A;"Москва";B:B;"Ноутбук") - СЧЁТЕСЛИ количество ячеек по условию
=СЧЁТЕСЛИ(A:A;"Москва") - СЦЕПИТЬ текст из нескольких ячеек
=СЦЕПИТЬ(A2;" ";B2) - Как убрать лишние пробелы в тексте
=СЖПРОБЕЛЫ(A2) - ЕСЛИ с несколькими условиями
=ЕСЛИ(A2>=90;"Отлично";ЕСЛИ(A2>=75;"Хорошо";ЕСЛИ(A2>=60;"Удовлетворительно";"Плохо"))) - Округлить до 2 знаков после запятой
=ОКРУГЛ(A2;2) - Извлечь часть текста из ячейки
=ПСТР(A2;3;5) - Найти позицию символа в тексте
=НАЙТИ("@";A2) - Преобразовать текст в число
=ЗНАЧЕН(A2) - Как посчитать процент от числа
=A2*B2 - ГПР горизонтальный поиск
=ГПР(A1;A1:F5;3;ЛОЖЬ) - Как выделить уникальные значения из списка
=УНИК(A2:A100) - Как посчитать разницу между датами
=РАЗНДАТ(A2;B2;"d") - СЧЁТЕСЛИМН по нескольким условиям
=СЧЁТЕСЛИМН(A:A;"Москва";B:B;">="&D1;B:B;"<="&D2) - Как скрыть ошибку в формуле ЕСЛИОШИБКА
=ЕСЛИОШИБКА(A2/B2;"") - Максимум и минимум по условию в Excel
=МАКСЕСЛИ(B:B;A:A;"Москва") - Среднее значение по нескольким условиям
=СРЗНАЧЕСЛИМН(C:C;A:A;"Москва";B:B;"Ноутбук") - Как сделать все буквы заглавными или строчными
=ПРОПИСН(A2) - Как сделать первую букву заглавной в Excel
=ПРОПНАЧ(A2) - Как объединить дату и текст в одной ячейке
="Отчёт за "&ТЕКСТ(A2;"ДД.ММ.ГГГГ") - Как получить номер месяца из даты
=МЕСЯЦ(A2) - Как получить название месяца из даты
=ТЕКСТ(A2;"ММММ") - Как удалить определённые символы из текста
=ПОДСТАВИТЬ(A2;" ";"") - ВПР с приблизительным совпадением (интервал)
=ВПР(A2;$D$2:$E$10;2;ИСТИНА) - Как посчитать количество уникальных значений
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A100;A2:A100)) - Как проверить, пустая ли ячейка
=ЕСЛИ(ЕПУСТО(A2);"пусто";"есть данные") - Как найти максимальное и минимальное значение
=МАКС(A2:A100) - Как посчитать сумму по цвету ячейки
=СУММЕСЛИ(B:B;"да";A:A) - Как перевернуть таблицу (строки в столбцы)
=ТРАНСП(A1:C5) - Как автоматически пронумеровать строки
=СТРОКА()-1 - Как убрать первые несколько символов из текста
=ПРАВСИМВ(A2;ДЛСТР(A2)-3) - Как оставить только цифры из текста
=ОБЪЕДИНИТЬ("";ИСТИНА;ЕСЛИ(ЕЧИСЛО(--ПСТР(A2;СТРОКА($1:$99);1));ПСТР(A2;СТРОКА($1:$99);1);"")) - Как посчитать количество слов в ячейке
=ЕСЛИ(СЖПРОБЕЛЫ(A2)="";0;ДЛСТР(СЖПРОБЕЛЫ(A2))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";""))+1) - ВПР возвращает 0 вместо пустой ячейки
=ЕСЛИ(ВПР(A2;D:E;2;ЛОЖЬ)=0;"";ВПР(A2;D:E;2;ЛОЖЬ)) - Как сложить каждую вторую (N-ю) строку
=СУММПРОИЗВ((ОСТАТ(СТРОКА(A2:A100)-СТРОКА(A2);2)=0)*A2:A100) - Как сравнить две даты в Excel
=ЕСЛИ(A2>B2;"A позже";ЕСЛИ(A2<B2;"B позже";"даты равны")) - Как прибавить месяцы к дате
=ДАТАМЕС(A2;3) - Как извлечь домен из адреса почты
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ("@";A2)) - Как извлечь имя файла из полного пути
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ("*";ПОДСТАВИТЬ(A2;"\";"*";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"\";""))))) - Как определить ранг (место) значения в списке
=РАНГ(A2;$A$2:$A$100;0) - Как сделать накопительный (нарастающий) итог
=СУММ($B$2:B2) - Как настроить условное форматирование по формуле
=$C2>100 - Как округлить до кратного (до 50, 100, 0.5)
=ОКРВВЕРХ(A2;50) - Как проверить, число в ячейке или текст
=ЕСЛИ(ЕЧИСЛО(A2);"число";"не число") - ВПР с несколькими результатами (все совпадения)
=ФИЛЬТР(B:B;A:A=F1) - Как найти первый рабочий день месяца
=РАБДЕНЬ(КОНМЕСЯЦА(A2;-1);1) - Как посчитать количество месяцев между датами
=РАЗНДАТ(A2;B2;"m") - Как разбить ФИО на фамилию, имя и отчество
=ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1) - Как сделать из ФИО «Фамилия И.О.»
=ЛЕВСИМВ(A2;НАЙТИ(" ";A2))&ПСТР(A2;НАЙТИ(" ";A2)+1;1)&"."&ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2)+1)+1;1)&"." - Как посчитать НДС в Excel (выделить и начислить)
=A2*0,2 - Как перевести число в проценты и обратно
=A2*100 - Excel показывает формулу вместо результата
=A2+B2 - Как убрать переносы строк в ячейке
=ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ") - Как найти повторяющиеся значения (дубли)
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$100;A2)>1;"дубль";"") - Как посчитать возраст в годах и месяцах
=РАЗНДАТ(A2;СЕГОДНЯ();"y")&" г. "&РАЗНДАТ(A2;СЕГОДНЯ();"ym")&" мес." - Как написать сумму прописью с копейками
=ТЕКСТ(ЦЕЛОЕ(A2);"# ##0")&" руб. "&ТЕКСТ(ОКРУГЛ(ОСТАТ(A2;1)*100;0);"00")&" коп." - Как проверить, входит ли дата в диапазон
=ЕСЛИ(И(A2>=$D$1;A2<=$D$2);"в периоде";"вне периода") - Как сделать ВПР по нескольким листам
=ЕСЛИОШИБКА(ВПР(A2;Лист1!A:B;2;ЛОЖЬ);ЕСЛИОШИБКА(ВПР(A2;Лист2!A:B;2;ЛОЖЬ);ВПР(A2;Лист3!A:B;2;ЛОЖЬ))) - Как извлечь год из даты
=ГОД(A2) - Как посчитать количество определённого символа в тексте
=ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;",";"")) - Как сложить N самых больших значений
=СУММ(НАИБОЛЬШИЙ(A2:A100;{1;2;3})) - Как автоматически ставить дату при вводе в строку
=ЕСЛИ(B2<>"";СЕГОДНЯ();"") - Как сцепить диапазон ячеек с разделителем
=ОБЪЕДИНИТЬ("; ";ИСТИНА;A2:A20) - Как сравнить текст с учётом регистра
=ЕСЛИ(СОВПАД(A2;B2);"совпадают";"различаются") - Как преобразовать дату в текст нужного формата
=ТЕКСТ(A2;"ДД.ММ.ГГГГ") - Как посчитать процент от числа
=A2*B2 - ЕСЛИ с несколькими условиями
=ЕСЛИ(A2>=90;"отлично";ЕСЛИ(A2>=70;"хорошо";ЕСЛИ(A2>=50;"удовл";"плохо"))) - Как округлить число в Excel
=ОКРУГЛ(A2;0) - Как объединить текст и число в одной ячейке
=A2&" — "&ТЕКСТ(B2;"# ##0")&" ₽" - Как посчитать количество по условию
=СЧЁТЕСЛИ(A:A;"Оплачено") - ВПР справа налево (искать в правом столбце)
=ИНДЕКС(A:A;ПОИСКПОЗ(D2;B:B;0)) - Как вывести месяц прописью из даты
=ТЕКСТ(A2;"ММММ") - Как убрать лишние пробелы в ячейке
=СЖПРОБЕЛЫ(A2) - Как определить номер недели по дате
=НОМНЕДЕЛИ(A2;2) - ПРОСМОТРX (XLOOKUP) — замена ВПР
=ПРОСМОТРX(A2;D:D;E:E;"не найдено") - Среднее по условию (СРЗНАЧЕСЛИ)
=СРЗНАЧЕСЛИ(A:A;"Москва";B:B) - Как сделать сводную таблицу в Excel
Вставка → Сводная таблица - Как разбить текст по разделителю на столбцы
Данные → Текст по столбцам - Как определить место (ранг) значения в списке
=РАНГ(B2;B:B) - Как посчитать сумму по цвету ячейки
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B:B) - ДВССЫЛ — ссылка на ячейку из текста
=ДВССЫЛ("A"&B2) - Как найти последнее заполненное значение в столбце
=ПРОСМОТР(2;1/(A:A<>"");A:A) - Как перевести сумму в рубли с копейками текстом
=ТЕКСТ(A2;"# ##0,00")&" ₽"