Продвинутые формулы: упражнения
Эти упражнения для тех, кто уже пишет обычные формулы и хочет искать данные и менять их структуру. Вы поработаете с ВПР, ИНДЕКС и ПОИСКПОЗ, ПРОСМОТРХ и функциями динамических массивов ФИЛЬТР, СОРТ и УНИК, а затем с LET и LAMBDA, чтобы длинные формулы оставались читаемыми. Задания построены на реалистичных таблицах поиска, поэтому типичные ошибки, такие как приблизительное совпадение или сдвинутый диапазон, сразу видны в автоматической проверке.
69 упражнений · уровень: Уверенный пользователь
Начать с первого задания Изучить курс «Продвинутые формулы» →
Показано 69 из 69
Поиск данных
В F2:F4 подставьте название товара по коду из столбца E.
В G2:G4 подставьте цену товара.
В F2:F6 найдите скидку клиента по шкале слева.
В G2:G6 посчитайте сумму к оплате с учётом скидки.
В G2:G4 найдите табельный номер по ФИО (номер стоит левее ФИО, ВПР тут не поможет).
В H2:H4 найдите оклад сотрудника.
В I2:I4 найдите стоимость доставки по городу и весу из таблицы слева.
В F2:F5 подставьте название товара по артикулу. Если артикула нет, напишите «Нет в каталоге».
В G2:G5 подставьте цену, а для неизвестного артикула — 0.
В D2:D5 подтяните цену из листа «Прайс».
В E2:E5 посчитайте сумму заказа.
В D10:D13 подставьте название курса по коду (точный поиск ВПР).
В E10:E13 найдите цену в месяц связкой ИНДЕКС + ПОИСКПОЗ.
В F10:F13 найдите скидку за предоплату по шкале F2:G5 (интервальный поиск по числу месяцев).
В G10:G13 посчитайте сумму к оплате: цена × месяцев × (1 − скидка).
В J2:J4 найдите код курса по преподавателю (код стоит левее!). Если такого преподавателя нет, напишите «Нет курса».
Динамические массивы
В D2 одной формулой выведите список городов без повторов, по алфавиту.
В E2 одной формулой посчитайте визиты для каждого города из списка D2#.
В G2 посчитайте, сколько разных клиентов приходило.
В E2 выведите все товары категории из K1 (все три столбца).
Поменяйте категорию в K1 на «Бытовая техника» — список в E2 обновится сам.
В E7 выведите товары дороже 100 000 ₸, отсортированные по цене от большей к меньшей.
В A2 одной формулой пронумеруйте строки от 1 до 10.
В F4 разрежьте ответ из F2 на отдельные города (по одному в ячейке).
В F7 поверните таблицу C1:D4 так, чтобы месяцы шли по строке.
В E2 выведите три лучших строки по тесту 2 (все три столбца).
В H2 выведите только столбцы «Ученик» и «Тест 1» для всех строк.
В K2 соберите в один столбец: сначала ученики A2:A4, а под ними — A5:A7, но без первого из них (DROP).
В E2 одной формулой выведите список курсов без повторов, по алфавиту.
В F2 одной формулой посчитайте заявки на каждый курс из списка E2#.
В G2 одной формулой посчитайте средний балл по каждому курсу из E2#.
В I2 одной формулой выведите всех студентов, отсортированных по баллу от большего к меньшему.
В H2 пронумеруйте места рейтинга: столько номеров, сколько строк в I2#.
В K3 выведите студентов курса из L1, у которых балл не ниже 80.
Формулы-конструкторы
В A2:A10 сделайте нумерацию функцией СТРОКА.
В F1 посчитайте сумму выручки за последние 3 заполненных месяца через СМЕЩ.
В F5 достаньте значение из столбца C той строки, номер которой указан в F4.
В B2:B5 выведите логин (до «@»).
В C2:C5 выведите домен (после «@»).
В F2:F4 посчитайте итоговую цену: скидка 10%, затем наценка 12%, округлить до тенге. Используйте LET.
В C2 одной формулой посчитайте цены со скидкой 7%, округлённые до 10 ₸ вниз.
В E2 одной формулой постройте нарастающий итог цен.
В G2 посчитайте сумму квадратов цен через REDUCE.
Дайте ячейке I1 с тарифом имя «Тариф».
В A2:A9 сделайте нумерацию функцией СТРОКА.
В C2:C9 выведите код отправления — всё, что до « | ».
В D2:D9 выведите пункт назначения — всё, что после « | ».
В F2:F9 посчитайте стоимость: вес × Тариф, но не меньше минимума из I2. Используйте LET и имя «Тариф».
В I5 и I6 через СМЕЩ достаньте пункт и стоимость отправления, номер которого указан в I4.
Современные функции
В F1 одной формулой GROUPBY посчитайте выручку по регионам — с заголовками и строкой итога. Возьмите диапазоны вместе с заголовками (A1:A13 и D1:D13).
В F8 выведите средний чек каждого менеджера: без заголовков (берите B2:B13 и D2:D13), без итога, от большего среднего к меньшему.
В I2 выведите выручку по товарам только для региона из J1 (используйте аргумент «фильтр»), без строки итога.
В I8 посчитайте, сколько сделок было по каждому товару (функция СЧЁТ), без строки итога.
В F13 постройте сводную PIVOTBY: регионы по строкам, товары по столбцам, сумма продаж. Диапазоны берите с заголовками.
В C2:C7 проверьте, есть ли в заявке слово на «срочн» в любом регистре («срочно», «СРОЧНО», «Срочная»).
В D2:D7 достаньте из заявки email. Если адреса нет, пусть будет «нет».
В E2:E7 приведите телефон к виду из одних цифр, начинающемуся с 7: уберите всё, кроме цифр, а ведущую 8 замените на 7.
В F2 одной формулой достаньте из кода заказа «ЗК-2026-0457» год и номер — они должны разлиться в F2 и G2. Повторите для строк 3–7.
В H2:H7 оформите телефон из E по шаблону «+7 (701) 123-45-67».
В D2:D13 формула ищет цену дважды. Перепишите её так, чтобы поиск выполнялся один раз, а для неизвестного товара (Соль) по-прежнему получался 0.
В K2:K6 каждая формула просматривает целые столбцы B:B и D:D. Замените их одной формулой в K2 с точными диапазонами — она должна разлиться на K2:K6.
В H9 сумма первых N продаж считается через летучую СМЕЩ. Перепишите формулу без СМЕЩ и ДВССЫЛ — так, чтобы она по-прежнему зависела от H8.
Сделайте ячейку-параметр: введите в H11 дату отчёта 31.10.2026.
В E2:E13 СЕГОДНЯ() стоит в каждой строке. Перепишите формулы так, чтобы они считали дни от даты отчёта в H11.
В D2:D11 определите город: достаньте буквы в начале кода (REGEXEXTRACT) и найдите их в справочнике I2:J4 (ПРОСМОТРX).
В E2:E11 отметьте юрлиц: ИСТИНА, если название клиента начинается с «ТОО » или «АО ».
В F1 одной формулой GROUPBY посчитайте выручку по городам из столбца D — с заголовками и итогом.
В F8 выведите клиентов-юрлиц и суммы их заказов (столбцы B и C) по признаку из E.
В J8 через ПУСТЬ посчитайте долю юрлиц в общей выручке: заведите переменные «всего» и «юр».
По этим фильтрам упражнений нет.