Разбор вопросов ЕЦЭ-2024. Блок «Работа в Excel»

Вопрос №1

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

• B2+B3+B4
• +B2+B3+B4
• СУММА(B2, B3, B4)
• =B2+B3+B4

Комментарий. Правильный ответ — 4. Любая формула в Excel всегда начинается со знака «=». Если его не поставить, то это будет уже не формула, а простой текст. Освоить универсальный навык работы с формулами разной сложности можно бесплатно за 3 дня на уроках «Как производить расчеты с данными: пишем простые формулы» курса «Аналитик данных».

Вопрос №2

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

• СРЕД
• СРЕДНЕЕ
• СРЗНАЧ
• ПОДСЧИТАТЬ_СРЕДНЕЕ
• AVVALUE
• Все функции заменит калькулятор

Комментарий. Верный ответ — 3. Для расчета среднего значения из диапазона, можно использовать функцию СРЗНАЧ. Ещё есть функция СРЗНАЧЕСЛИ, которая возвращает среднее арифметическое всех ячеек в диапазоне, которые соответствуют условию.

Вопрос №3

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

• Сделать фильтр по нужному отделу — в сократившемся списке будет быстрее найти коллегу
• Нажать комбинацию клавиш ctrl+S (search) и ввести ФИО и должность коллеги
• Нажать комбинацию клавиш ctrl+F (find) и ввести фамилию коллеги
• Написать специальный макрос для поиска, так как в таблице более 1000 строк для поиска значений
• Найти по таблице коллег или руководителя Пряточкина, и выяснить отчество у них
• Позвонить секретарю компании — пусть она узнает отчество Пряточкина

Комментарий. Правильный ответ — 3. Ctrl+F — это классическая комбинация клавиш, чтобы открыть окно поиска в приложении. Она работает и в браузере, и в Word, и в Excel.

Вопрос №4

Вы работаете с Excel таблицей, в которой хранят данные о поставщиках. Последнее время некоторые поставщики начали регулярно задерживать поставки. Вам поручили разобраться с этим и «выкатить» им неустойку по просрочке. В качестве теста в одну из ячеек вы написали формулу: ="20.11.2024″ — 10

Что получится в результате выполнения такой формулы?

• Из даты 20.11.2024 вычитается 10 лет, поэтому получится 20.11.2014
• Из даты 20.11.2024 вычитается 10 недель, поэтому получится 11.09.2024
• Из даты 20.11.2024 вычитается 10 дней, поэтому получится 10.11.2024
• Excel выдаст ошибку: из даты нельзя вычитать число
• Excel выдаст ошибку: в формуле лишние кавычки

Комментарий. Верный ответ — 3. Excel имеет огромные возможности для работы с данными. В том числе и с датами — значение даты можно увеличить или уменьшить обычным прибавлением или вычитанием числа. Для Excel число 1 — это сутки, то есть 24 часа. Поэтому если из 20.11.2024 вычесть 10, то получится 10.11.2024, то есть минус 10 дней.

Вопрос №5

Работая с Excel-таблицей вы решили пройтись по ячейкам и выделить отдельные желтым цветом для перепроверки. В какой-то момент вы устали тыкать на заливку для каждой ячейки, ведь это действие вы делали буквально только что. Какое сочетание клавиш позволит повторить предыдущую команду или действие?

• Ctrl+U
• Ctrl+B
• Ctrl+Y
• Ctrl+P
• Вручную удобнее

Комментарий. Правильный ответ — 3. Комбинация Ctrl+B выделяет текст полужирным, а Ctrl+U подчеркивает текст. Ctrl+P открывает окно печати документа. Наконец, Ctrl+Y повторяет предыдущую команду или действие. Кстати, эти горячие клавиши работают и в Word. Таким образом вы можете быстро применять последнее форматирование в разным частям документа.

Вопрос №6

Вы работаете с Excel таблицей, где ведете семейный бюджет и планируете отпуск. Когда вы планировали расходы на отдых, в одной из ячеек высветилась ошибка «#ЗНАЧ». Что это значит?

• Значение не умещается в ячейке
• Неверное преобразование типа данных
• Используется неподходящий тип данных для ячейки
• Формула содержит неверно заданный аргумент
• Временный сбой Excel

Комментарий. Правильный ответ — 4. В Excel есть разные типы ошибок. Ошибка «#ЗНАЧ» означает, что формула содержит неверно заданный аргумент, например, суммируются числовая и текстовая ячейки. Если видите в ячейке «####», значит значение просто не влезает в ячейку и нужно ее растянуть. А ошибка «#ИМЯ?» говорит о том, что есть проблема с работой функции внутри ячейки. Узнать больше о возможностях Excel и развить уверенные навыки работы в таблицах для анализа данных поможет курс «Аналитик данных».

Вопрос №7

Вы ведете учет канцелярских товаров в Excel: их количество и стоимость. Общую стоимость одного вида товара вместе с доставкой считаете по формуле: Общая стоимость = Кол-во товара * стоимость одной штуки + доставка
Стоимость доставки фиксированная для каждого товара (хранится в ячейке F3).
Выберите формулу для расчета стоимости в ячейке D3, чтобы при копировании формулы верно рассчиталась общая стоимость и для остальных деталей:

• =B3*C3+F3
• =$B3*$C3+F3
• =B$3*C$3+$F$3
• =B3*C3+$F$3
• =$B$3*$C$3+$F$3
• =B3*C3+$F3
• На калькуляторе быстрее

Комментарий. Правильный ответ — 4. Допустим, в ячейке D3 у нас написана формула «=B3*C3+F3». Если скопировать эту формулу в D4, то она превратится в «=B4*C4+F4», то есть номер строки везде в формуле увеличится на 1. В то же время, если скопировать ее в E3, то получится «=C3*D3+G3». Значит, нужна такая формула, в которой при копировании F3 меняться не будет. Для этого используют абсолютную адресацию — знак $. Кроме ответа «=B3*C3+$F$3», конкретно для этой задачи также подойдет формула «=$B3*$C3+$F$3».

Вопрос №8

Выберите все верные утверждения про Excel:

• К датам в Excel нельзя прибавлять целые числа
• В Excel можно создать выпадающий список в ячейке
• При работе с логическими выражениями в Excel любые числовые значения кроме 0 оценивают как «ИСТИНА»
• В Excel нельзя менять размер текста, только формат: жирный, курсив и прочее
• Excel не имеет ограничений по объему данных для расчетов. Можно добавлять любое количество строк и столбцов до бесконечности — на оперативность расчетов это никак не повлияет
• Сочетание «Win+точка в английской раскладке» в Excel поможет вставить смайл в ячейку

Комментарий. Верные ответы — 2, 3 и 6. Дата в Excel хранится в виде числа, поэтому к дате спокойно можно прибавлять целые числа и получать новую дату. Размер текста в Excel можно менять в том же меню, где настраиваем формат. А вот с объемом данных в Excel есть нюанс — стандартное максимальное количество строк в Excel 2019 составляет 1 048 576. Если этот предел превышен, появится всплывающее сообщение «Файл не полностью загружен». Остальные утверждения про Excel верны. Сочетание Win+точка позволяет вставить смайл в ячейку. Все, кроме нуля, Excel воспринимает как значение «ИСТИНА». А чтобы создать выпадающий список в ячейке, перейдите во вкладку «Данные», выберите «Проверка данных» и укажите тип данных «Список».

Вопрос №9

Каждый месяц аналитический отдел просит сотрудников отправлять пакет данных о новых пользователях для анализа. В середине месяца в офис влетает разъяренный аналитик и кричит: «Сколько можно повторять?! В отчете используйте нижний регистр!». Что он имел ввиду? Отметьте подходящий вариант ответа.

• Нижний регистр — регистрация пользователя ниже, верхний регистр — регистрация пользователя выше
• Нижний регистр — строчные буквы, верхний регистр — прописные буквы
• Нижний и верхний регистры — это специальные возможности просмотра листа, которые находятся во вкладке Вид
• Нижний регистр — это нижняя половина листа Excel, верхний регистр — верхняя половина листа

Комментарий. Верный ответ — 2. В Excel есть даже специальные функции для того, чтобы сделать все буквы в верхнем или нижнем регистре: СТРОЧН и ПРОПИСН. Узнать больше о возможностях Excel и развить уверенные навыки работы в таблицах для анализа данных поможет программа «Аналитик данных».

Вопрос №10

Вы работаете с таблицей Excel, где хранят данные о выручке магазинов за каждый день. Вы хотите суммировать всю выручку с помощью функции СУММ. Вводите в строку формул «=СУ», и Excel предлагает функции на выбор, первая из которых как раз «СУММ». Какую клавишу или комбинацию клавиш нажать, чтобы автоматически дописать функцию?

• Alt
• Shift
• Shift+пробел
• Ctrl+Shift
• Tab

Комментарий. Верный ответ — 5. Автоматически дописать функцию поможет клавиша Tab. С помощью горячих клавиш можно не только быстро писать формулы, но и удобно и быстро перемещаться по таблице или документу. Например, клавиша Home переводит курсор в начало строки, End — в конец строки. Если знать горячие клавиши в системе и программе, то при желании за весь можно даже ни разу не воспользоваться мышкой, и сэкономить за месяц несколько часов работы.

Вопрос №11

Вы работаете в торговой компании. У вас есть таблица из следующих полей:

• Номер заказа: уникальный номер каждого заказа
• Клиент: имя клиента, сделавшего заказ
• Товар: наименование приобретенного товара
• Количество: количество приобретенных единиц товара
• Цена за единицу (руб.): стоимость каждой единицы товара в рублях

Скачайте файл с рабочими таблицами по этой ссылке. Откройте вкладку «Товары». Рассчитайте общую выручку компании. Введите ответ цифрами без пробелов, без запятых, без точек и без копеек. Пример: 200000

Комментарий. Ответ: 517500. Сначала посчитаем выручку по каждому товару. Составим формулу для ячейки F2, а затем растянем её на весь столбец, чтобы не заполнять вручную. Чтобы посчитать выручку по товару, нужно умножить количество на цену. Получится такая формула: =D2*E2
Растягиваем формулу на весь столбец F. Осталось суммировать все выручки. Для этого в соседней ячейке воспользуемся функцией СУММ и укажем диапазон суммирования: =СУММ(F2:F31)
Вот и всё! Итоговая сумма равна 517500. Кстати, так как мы суммируем весь столбец F, то вместо диапазона можно просто щелкнуть на имя столбца. В результате получится такая формула: =СУММ(F:F)
Так мы можем посчитать сумму по всему столбцу, причем Excel сам будет игнорировать названия столбцов, если такие есть. Только убедитесь, что формула не написана в столбце F, иначе будет ошибка.

Вопрос №12

У вас есть таблица, в которой указан заработок за два месяца для каждого сотрудника. Нужно начислить премию, если сотрудник за два месяца заработал более чем на 90 000 руб. Для решения задачи вы в ячейку E2 записали формулу: =ЕСЛИ(D2>90000;Да;Нет)
Какой результат получите по такой формуле?

• Да
• Нет
• Ошибка #ИМЯ?
• Ошибка #ЗНАЧ
• Excel не даст выполнить формулу: выбрана неверная функция

Комментарий. Верный ответ — 3. В указанных условиях выйдет ошибка «#ИМЯ?». Это говорит о том, что есть проблема с работой функции внутри ячейки. В нашем случае — внутри функции ЕСЛИ забыли взять в кавычки «Да» и «Нет».

Вопрос №13

Пока вы рассчитывали давать ли премию сотрудникам, к вам в личные сообщения постучался руководитель и сообщил, что условия расчета изменились. Теперь сотруднику выдают премию, если в январе выручка была больше 40 тыс., а в феврале — больше 30 тыс. Какую формулу написать в ячейке Е2, чтобы определить положена сотруднику премия или нет?

• =ЕСЛИ(B2>40000;C2>30000;"Да";"Нет")
• =ЕСЛИ(B2>40000 И C2>30000;"Да";"Нет")
• =ЕСЛИ(И(B2>40000;C2>30000);"Да";"Нет")
• =ЕСЛИ(ИЛИ(B2>40000;C2>30000);"Да";"Нет")
• =ЕСЛИ(B2>40000 ИЛИ C2>30000;"Да";"Нет")

Комментарий. Верный ответ — 3. С помощью функций И/ИЛИ можно проверять сразу несколько условий. Для этого в скобках функции нужно указать через точку с запятой условия, которые проверяем. Например так: И(B2>40000;C2>30000)
Если оба условия выполняются, то выведем «Да», иначе — «Нет». Получается такая формула: =ЕСЛИ(И(B2>40000;C2>30000);"Да";"Нет«)
Функция ИЛИ работает похожим образом, но там если выполняется хотя бы одно условие, то в нашем примере выведем «Да».

Вопрос №14

Вам прислали Excel-таблицу с данными о клиентах. В ней много разных полей: ФИО, номер телефона, адрес, номер накладной и так далее. Только данные по клиентам расположены не по строкам, а по столбцам. Это неудобно и вы решили перевернуть таблицу: поменять строки и столбцы местами. Как это сделать не перепечатывая все данные вручную? Отметьте подходящий вариант.

• Выделить таблицу и потянуть вправо за правый нижний угол
• Вырезать таблицу -> перейти во вкладку Данные -> нажать Консолидация
• Выделить таблицу и перетащить ее в ячейку с зажатой клавишей Shift
• Скопировать таблицу -> вставить в ячейку -> выбрать в настройках вставки Транспонировать
• Это невозможно сделать в Excel. Нужно попросить аналитиков пересобрать отчет из сырых данных

Комментарий. Правильный ответ — 4. Перевернуть таблицу поможет опция Транспонирование. Если не лезть в математику и говорить простым языком, то транспонирование — это когда столбцы превращаются в строки, а строки — в столбцы. Можете проверить прямо сейчас принцип работы транспонирования:

• Введите несколько полей подряд в одну строку или столбец.
• Скопируйте поля.
• Вставьте в другую ячейку и выберите в настройках вставки Транспонировать.

Вопрос №15

В рабочей таблице Excel приведен каталог с характеристиками ноутбуков и их стоимостью. А ещё есть таблица с данными клиентов, которые хотят купить эти ноутбуки. Необходимо сообщить покупателям, сколько стоят их ноутбуки. Перед тем как обзванивать клиентов, нужно объединить данные: добавить во вторую таблицу колонку с ценами из первой. Какая функция поможет решить эту задачу?

• ИНДЕКС
• СВЕРКА
• ПОДТ
• ГПР
• ВПР
• ЛПР

Комментарий. 5. ВПР (Vlookup, или вертикальный просмотр) — поисковая функция в Excel. Она находит значения в одной таблице и переносит их в другую. Функция ВПР полезна, когда работаем с большими объемами данных — не нужно самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически. Узнать больше о возможностях Excel и развить уверенные навыки работы в таблицах для анализа данных поможет курс «Аналитик данных».

Вопрос №16

Скачайте файл с рабочими таблицами. Откройте вкладку «Сотрудники». Посчитайте, сколько сотрудников имеют зарплату выше среднего значения. Ответ введите цифрами без пробелов, без запятых, без точек и без копеек. Например: 20

Правильный ответ: 13. Возможные вариации: с пробелами до и после, тринадцать

Комментарий. Для начала нужно посчитать среднюю зарплату. Это можно сделать с помощью функции СРЗНАЧ. Сделаем это, например, в ячейке G2: =СРЗНАЧ(D:D)
Теперь посчитаем сколько сотрудников имеют зарплату выше среднего значения. Для этого возьмем специальную функцию СЧЕТЕСЛИ — она подсчитывает кол-во непустых ячеек, которые удовлетворяют условию. Указываем диапазон зарплат, а в качестве условия — среднее значение, т.е. ячейку G2: =СЧЕТЕСЛИ(D2:D29;G2)
Однако, такая формула выведет «0». Это потому, что мы сейчас проверили на «равенство», а надо на «выше». То есть «если зп больше среднего, то берем в подсчет». Подкорректируем формулу:
=СЧЕТЕСЛИ(D2:D29;">"&G2)

Обратите внимание:

• знак «больше» взят в кавычки,
• перед ячейкой стоит знак «&» — он нужен, чтобы сравнить именно со значением ячейки.

В итоге получаем ответ 13 — именно столько сотрудников имеют зарплату выше среднего.