Excel. Формулы без страха: учимся ВПР и ПРОСМОТРX на летнем пикнике.

Допустим, у вас две таблицы: в одной — коды товаров, в другой — их цены. Нужно быстро «подтянуть» цену к каждому коду, иначе придётся вручную копировать строчку за строчкой. Тут и выручают функции ВПР (VLOOKUP) — старый добрый помощник и ПРОСМОТРX (XLOOKUP) — более гибкая современная версия. Они, как поисковая служба, находят нужную строку и приносят ответ туда, куда скажете. В статье я разберу, как написать формулу без ошибок и что делать, если данных станет больше или столбцы поменяются местами.

📌Навигация по материалам в Telegram

Я возьму тестовое задание среднего уровня: 12 вопросов, где нужно составить формулы для разных случаев — поиск слева‑направо, поиск с ошибкой «не найдено», поиск по нескольким условиям. Каждый вопрос разберу пошагово, объясняя, почему именно такая конструкция работает и что изменится, если таблица вырастет.

Вопрос 1

Что отобразится в ячейке после применения в ней этой функции?
=ПРОПНАЧ("договор НА согласовании") (или =PROPER("договор НА согласовании") в англоязычной версии)

Объяснение:

Функция ПРОПНАЧ / PROPER в Excel автоматически делает:

Первые буквы всех слов — заглавными
Остальные буквы — строчными

Фраза:
"договор НА согласовании"

После применения =ПРОПНАЧ(...) станет:
"Договор На Согласовании"

"договор" → "Договор"
"НА" → "На"
"согласовании" → "Согласовании"

Анализ вариантов:

1. Договор НА согласовании — вторая часть не преобразована

2. Договор на согласовании — "на" и "согласовании" не с большой буквы

3. Договор На Согласовании — всё правильно!

4. договор на согласовании — без заглавных

5. ДОГОВОР НА СОГЛАСОВАНИИ — это СТРОЧБОЛЬШ() / UPPER()

Выбранный ответ:

3. Договор На Согласовании

Вопрос 2

Как правильно указать диапазон, включающий все ячейки столбца C от строки 3 до строки 8?

Объяснение:

В Excel диапазоны указываются через двоеточие:, чтобы обозначить откуда и докуда.
Например:

C3:C8 — это все ячейки в столбце C от строки 3 до строки 8 включительно.
Остальные варианты используют неверный синтаксис, Excel их просто не поймёт.

Разбор остальных вариантов:

C(3:8) — неправильный синтаксис (так пишут в формулах массивов, но не в диапазонах).
C3, C8 — это две отдельные ячейки, а не диапазон.
C:3-8 — такого формата вообще не существует.
C3 to C8 — Excel не понимает "to", он использует: .

Выбранный ответ:

1. C3:C8

Вопрос 3

Какой код числового формата вы используете, чтобы отобразить число 140000 как 140 000,00? Обязательно должен отображаться пробел в качестве разделителя групп разрядов и два знака после запятой.

Варианты ответа:

1. # ##0,0E+0

2. # ##0

3. # ##0,00

4. #" "0,00

5. 0,00

Объяснение:

# — показывает цифру, если она есть (не ставит лишних нулей).
0 — показывает цифру, а если её нет, ставит 0.
Пробел между группами # ## — ровно тот символ‑разделитель, который нам нужен для тысяч.
,00 после запятой заставляет Excel выводить ровно два знака после запятой, даже если это нули.

Разбираем варианты:

Выбранный ответ:

3. # ##0,00

Вопрос 4

Вы хотите создать ссылку на ячейку B2, которая находится на другом листе с названием «Все сотрудники». Как будет выглядеть эта ссылка?

Варианты ответа:

1. =ССЫЛКА(Все сотрудники;B2) или =LINK(Все сотрудники;B2)

2. =Все сотрудники;B2

3. ='Все сотрудники'!B2

4. =Все сотрудники!B2

5. ="Все сотрудники!B2"

Объяснение:

В Excel, чтобы сослаться на ячейку с другого листа, пишем:
'Имя_листа'!Адрес_ячейки
Если имя листа содержит пробелы или русские буквы, его берём в одинарные кавычки '...'.
Значит для листа «Все сотрудники» и ячейки B2 правильная запись:
'Все сотрудники'!B2
Знак = ставится в начале, как у любой формулы.

Разбор вариантов:

Выбранный ответ:

3. ='Все сотрудники'!B2

Вопрос 5

В таблице — учёт поставок в мебельном магазине. После какого действия товар «Шкаф‑купе» окажется на первой строке таблицы?

Фильтрация столбца C (Цена) > 6 000

Останутся товары дороже 6 000 ₽, но порядок строк сохранится, «Шкаф‑купе» будет внизу списка.

Объяснение:

Сортировка меняет порядок строк; фильтрация лишь скрывает лишние.
У «Шкаф‑купе» самая высокая цена (12 000 ₽).
Поэтому при сортировке столбца C по убыванию товар с максимальной ценой окажется сверху.

Выбранный ответ:

4. После сортировки значений ячеек столбца C по убыванию

Вопрос 6

Что вернёт функция ЕТЕКСТ(A1) / ISTEXT(A1), если ячейка A1 содержит число 2024?

Объяснение:

ЕТЕКСТ (рус.) или ISTEXT (англ.) проверяет: «является ли содержимое ячейки текстом?»
Если да → возвращает ИСТИНА (TRUE).
Если нет → возвращает ЛОЖЬ (FALSE).
В A1 записано число 2024, т.е. это не текст.

Проверяем варианты:

1. ЛОЖЬ (FALSE) — соответствует описанному поведению.

2. "2024" — было бы, если функция вернула сам текст.

3. 2024 — просто число, функция так не отвечает.

4. ИСТИНА (TRUE) — только для текста, не подходит.

5. #ЗНАЧ! — ошибка тут не возникает.

Выбранный ответ:

1. ЛОЖЬ (FALSE)

Вопрос 7

В таблице нужно вычислить долю каждой категории расходов от общих (ячейка B7 = 7500). Какую формулу записать в C2, чтобы после копирования вниз в C3:C6 расчёт остался корректным? Требуется использовать абсолютные ссылки.

Варианты ответа:

1. =B2/$B7

2. =B2/B$7

3. =B2/B7

4. =B2/$B$7

5. =$B$2/B$7

Объяснение:

Числитель ‑ это расход из той же строки, поэтому ссылка B2 должна смещаться вниз (относительная).
Знаменатель ‑ общая сумма в строке 7, столбце B; при копировании эта ссылка не должна изменяться.
→ ставим два доллара $B$7, что «приклеивает» и столбец, и строку.

Разбор вариантов:

Выбранный ответ:

4. =B2/$B$7

Вопрос 8

Вы хотите автоматически выделять ячейки, значения которых выше среднего по столбцу. Какой способ настройки условного форматирования для этой задачи будет правильным?

Варианты ответа:

1. Выделить столбец, выбрать «Условное форматирование» → «Правила для выделения ячеек» → «Значения выше среднего»

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

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

4. Выделить столбец, затем использовать функцию СРЕДНЕЕ() / AVERAGE() в каждой ячейке для сравнения

5. Применить к столбцу стандартную заливку, если значение больше среднего по формуле в строке условия

Объяснение:

Условное форматирование — это встроенный инструмент Excel, который автоматически меняет оформление ячеек в зависимости от их содержимого.
Среди готовых правил есть пункт «Значения выше среднего»: Excel сам вычисляет среднее по выделенному диапазону и подсвечивает то, что выше.
Всё происходит без ручных формул и сохраняется динамически, если данные меняются.

Разбор вариантов:

Выбранный ответ:

1. Выделить столбец, выбрать «Условное форматирование» → «Правила для выделения ячеек» → «Значения выше среднего»

Вопрос 9

Текст вопроса:

=ЕСЛИ(И(A1>5; НЕ(B1<3)); "Условие выполнено"; "Условие не выполнено")

или

=IF(AND(A1>5, NOT(B1<3)), "Условие выполнено", "Условие не выполнено")

Что вернёт формула, если A1 = 7, а B1 = 2?

Сделаем пошаговую проверку:

1. Проверяем A1>5

- 7 > 5 → ИСТИНА.

2. Проверяем B1<3

- 2 < 3 → ИСТИНА.

3. Применяем НЕ / NOT к результату второго условия

- NOT(ИСТИНА) → ЛОЖЬ.

4. Функция И / AND возвращает TRUE, только если все её аргументы истинны.

- AND(ИСТИНА; ЛОЖЬ) → ЛОЖЬ.

5. ЕСЛИ / IF получает в качестве логического теста ЛОЖЬ, значит выбирается ветка "Условие не выполнено".

Выбранный ответ:

4. Условие не выполнено

Вопрос 10

Текст вопроса:

=СЧЁТЕСЛИ(B1:B7; ">5") // или =COUNTIF(B1:B7, ">5")

Какой результат вернёт формула для диапазона на изображении?

Объяснение: СЧЁТЕСЛИ / COUNTIF считает, сколько ячеек удовлетворяют условию.
Условие ">5" — число должно быть больше пяти.

Проверяем каждое значение:

12 > 5 → учитываем (1)
4 ≤ 5 → нет
2 ≤ 5 → нет
6 > 5 → учитываем (2)
2 ≤ 5 → нет
4 ≤ 5 → нет
7 > 5 → учитываем (3)

Всего подходящих значений — 3.

Результат формулы: 3

Вопрос 11

Вы хотите выделить ячейки в столбце «Количество», которые содержат значения между 10 и 20 включительно. Какой способ настройки условного форматирования для этой задачи будет правильным?

Объяснение:

В Excel есть готовое правило условного форматирования «Между…»:

1. Выделяем столбец со значениями.

2. Переходим во вкладку «Главная → Условное форматирование».

3. Выбираем «Правила для выделения ячеек → Между…».

4. Вводим 10 и 20.

5. Задаём желаемый цвет/стиль заливки.

Excel сам проверит каждую ячейку: если число ≥ 10 и ≤ 20, применит выбранное форматирование. Это быстро, не требует формул и обновляется при изменении данных.

Разбор других вариантов

Выбранный ответ:

1. Выделить столбец, выбрать «Условное форматирование» → «Правила для выделения ячеек» → «Между» и ввести значения 10 и 20

Вопрос 12

В столбце «Количество» (именованный диапазон Количество) указано, сколько единиц каждого товара продано, а в столбце «Цена за ед.» (именованный диапазон Цена за ед.) — цена одной штуки. Требуется единой формулой массива получить общую стоимость всех товаров.

Варианты ответа (формула вводится с Ctrl + Shift + Enter, поэтому показана в фигурных скобках {}):

1. {=СУММ(A2*B2; A3*B3; A4*B4; A5*B5; A6*B6)}

2. {=A2*B2+A3*B3+A4*B4+A5*B5+A6*B6}

3. {=Количество*Цена за ед.}

4. {=СУММ(Количество*Цена за ед.)}

5. {=СУММ(A2:B6)}

Объяснение:

Что нужно сделать? — Перемножить каждую строку (штук × цена) и сложить результаты.
Как это сделать одной формулой?

1. Умножаем сразу два именованных диапазона: Количество*Цена за ед..
Excel создаёт массив парных произведений: {13*750; 23*820; …}.

2. Оборачиваем в СУММ / SUM, чтобы сложить элементы массива.

3. Такую формулу вводим как формулу массива (Ctrl + Shift + Enter в старом Excel; в 365/2021 достаточно Enter).

Записать без функции СУММ (вариант 3) вернёт только первое произведение, а не сумму.
Варианты 1 и 2 громоздкие и теряют преимущество именованных диапазонов.
Вариант 5 суммирует прямоугольник A2:B6, что вовсе не то.

Выбранный ответ:

4. {=СУММ(Количество*Цена за ед.)}

Вопрос 13

Какая из следующих функций НЕ поддерживается сводными таблицами в Excel?

Краткий разбор возможностей сводной таблицы

Единственный пункт, который действительно невозможен без изменения исходных данных — ручное редактирование значений прямо в ячейках сводной таблицы.

Выбранный ответ:

2. Прямое редактирование значений в ячейках сводной таблицы

Вопрос 14

Что произойдёт, если вы выберете диапазон строк и дважды щёлкнете по границе между заголовками строк в этом диапазоне?

Объяснение:

В Excel двойной щелчок по границе между номерами строк выполняет команду «Автоподбор высоты строки» (Row AutoFit).
Если выделено несколько строк, высота каждой из них подбирается так, чтобы в строке полностью уместился самый высокий объект (текст, обёрнутый текст, картинка и т.д.).
Происходит именно с каждой выбранной строкой, а не только с первой; никаких новых строк, переносов текста или выделения содержимого не создаётся.

Выбранный ответ:

1. Высота всех выбранных строк изменится, чтобы соответствовать содержимому их самых высоких ячеек

Заключение

Освоив ВПР и ПРОСМОТРX, вы сможете:

автоматически подтягивать цены, фамилии сотрудников, остатки на складе — что угодно;
избавить себя и коллег от ручного копирования и связанных с этим опечаток;
строить связки «больших» таблиц, даже если они хранятся на разных листах.
Главное правило — всегда проверяйте, чтобы код (или другой «ключ») совпадал в обеих таблицах. Тогда формула сработает безупречно, а вы сэкономите часы монотонной работы.