Course
Introduction to Excel
4 ч
237.2K
Вопросы по Excel среднего уровня
Если вы более опытный пользователь Excel, от вас ожидается лучшее понимание функций и умение анализировать данные. Освежите ключевые моменты в наших курсах Analysis in Excel и Intermediate Spreadsheets.
В чём разница между SUM(), SUMIF() и SUMIFS()?
В Excel функции SUM(), SUMIF() и SUMIFS() используются для суммирования диапазона ячеек, но различаются подходами к вычислению.
Функция SUM() просто складывает все числа в указанном диапазоне. Пример: =SUM(A1:A10) сложит все числа из ячеек A1–A10.
SUMIF() суммирует ячейки, соответствующие одному заданному критерию. Пример: =SUMIF(A1:A10, ">5") просуммирует все числа больше 5 в диапазоне A1–A10.
SUMIFS() расширяет SUMIF() и позволяет использовать несколько критериев. Пример: =SUMIFS(A1:A10, B1:B10, "X", C1:C10, ">5") просуммирует числа в диапазоне A1–A10, где соответствующие ячейки в диапазоне B1–B10 равны "X", а в C1–C10 больше 5.
В чём разница между COUNT(), COUNTA(), COUNTBLANK() и COUNTIF()?
В Excel функции COUNT(), COUNTA(), COUNTBLANK() и COUNTIF() предназначены для подсчёта ячеек в диапазоне, но каждая решает свою задачу.
Функция COUNT() подсчитывает количество ячеек диапазона, содержащих числа. Она игнорирует пустые ячейки, текст и другие нечисловые значения. Пример: =COUNT(A1:A10) подсчитает только ячейки с числами в диапазоне A1–A10.
COUNTA() считает количество непустых ячеек в диапазоне, независимо от содержимого (числа, текст и др.). Пример: =COUNTA(A1:A10) подсчитает все непустые ячейки в A1–A10.
COUNTBLANK() считает количество пустых ячеек в заданном диапазоне. Пример: =COUNTBLANK(A1:A10) вернёт число пустых ячеек в A1–A10.
COUNTIF() подсчитывает число ячеек в диапазоне, удовлетворяющих заданному критерию. Пример: =COUNTIF(A1:A10, ">5") подсчитает ячейки в A1–A10 с числами больше 5.
В чём разница между SUBSTITUTE() и REPLACE()?
В Excel и SUBSTITUTE(), и REPLACE() — текстовые функции для изменения содержимого строк, но методы замены различаются.
Функция SUBSTITUTE() заменяет указанные вхождения одной текстовой строки другой строкой.
Она особенно полезна, когда нужно заменить конкретный текст внутри строки, причём можно заменить все вхождения или только определённое. Пример: =SUBSTITUTE("Hello World", "World", "Excel") изменит "Hello World" на "Hello Excel". Если "World" встречается несколько раз, можно указать, какое именно вхождение заменить.
Функция REPLACE(), напротив, заменяет часть текстовой строки по позиции и количеству символов. Пример: =REPLACE("Hello World", 7, 5, "Excel") начинает с 7‑го символа (W), заменяет 5 символов ("World") на "Excel", в итоге получается "Hello Excel".
Что такое VLOOKUP()? Когда он полезен?
VLOOKUP() (вертикальный поиск) — функция Excel для поиска конкретного значения в одном столбце и извлечения соответствующего значения из другого столбца в той же строке. Особенно полезна, когда нужно найти и получить данные из больших таблиц или наборов данных.
Базовый синтаксис: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), где lookup_value — искомое значение, table_array — диапазон, содержащий это значение, col_index_num — номер столбца в диапазоне с возвращаемым значением, а [range_lookup] — необязательный аргумент: TRUE или 1 ищет приблизительное совпадение (по умолчанию), FALSE или 0 — точное совпадение.
Пример: нужно вернуть имя сотрудника (см. таблицу соответствий E1:F11, где в столбце E — ID сотрудника, а в столбце F — имя) по ID сотрудника из столбца A. =VLOOKUP(A2,$E$2:$F$11,2,0) вернёт "Sue" в примере ниже.
Примечание для собеседований: хотя VLOOKUP() всё ещё широко используется и его стоит знать, на продвинутых уровнях от вас ожидают знание XLOOKUP() — современной замены. XLOOKUP() более гибок (ищет влево и вправо), лучше обрабатывает ошибки и не ломается при вставке столбцов. Эквивалентная формула с XLOOKUP(): =XLOOKUP(A2, $E$2:$E$11, $F$2:$F$11). Готовясь к собеседованию на аналитика данных, убедитесь, что можете объяснить обе функции.
Как создать сводную таблицу в Excel?
Чтобы создать сводную таблицу в Excel, сначала выделите диапазон данных для анализа. Затем перейдите на вкладку Вставка на ленте и нажмите Сводная таблица. В появившемся диалоговом окне выберите место размещения сводной таблицы (новый или существующий лист). После нажатия ОК откроется область Поля сводной таблицы, где можно перетаскивать поля в области Строки, Столбцы, Значения и Фильтры для организации данных.
Как создать именованный диапазон?
Чтобы создать именованный диапазон в Excel, выделите ячейку или диапазон. Нажмите вкладку Формулы на ленте. Затем в группе Определённые имена нажмите Присвоить имя. В диалоговом окне Новое имя введите имя диапазона в поле Имя и при необходимости укажите область видимости и ссылку на ячейки. Нажмите ОК для создания именованного диапазона.
Как защитить книгу?
Чтобы защитить всю книгу Excel, перейдите на вкладку Рецензирование на ленте и нажмите Защитить книгу в группе Защита. Введите пароль в появившемся окне и подтвердите его. Книга будет зашифрована, и для её открытия в будущем потребуется пароль.
Продвинутые вопросы по Excel
Этот последний набор вопросов — для продвинутых пользователей Excel, имеющих большой опыт использования инструмента для разных задач.
Что такое XLOOKUP() и чем он лучше VLOOKUP()?
XLOOKUP() — современная функция поиска, представленная в Microsoft 365, которая заменяет VLOOKUP(), HLOOKUP() и во многих случаях INDEX-MATCH. Базовый синтаксис:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Ключевые преимущества по сравнению с VLOOKUP():
-
Ищет в любом направлении — влево, вправо, вверх или вниз.
VLOOKUP()ищет только слева направо. -
Без номера индекса столбца — вы указываете диапазон возврата напрямую, поэтому формулы не ломаются при вставке или удалении столбцов.
-
Встроенная обработка ошибок — параметр
[if_not_found]позволяет вернуть пользовательское сообщение вместо#N/A. -
Точное совпадение по умолчанию —
VLOOKUP()по умолчанию ищет приблизительное совпадение, что часто сбивает пользователей.
Используя тот же пример с сотрудниками из вопроса 16: =XLOOKUP(A2, $E$2:$E$11, $F$2:$F$11, "Not found") возвращает имя сотрудника или "Not found", если ID не существует — это чище и надёжнее эквивалента на VLOOKUP().
В чём преимущество INDEX-MATCH по сравнению с VLOOKUP()?
Использование INDEX-MATCH вместо VLOOKUP() в Excel даёт ряд преимуществ: больше гибкости — можно вернуть значение в столбце слева от столбца поиска, в то время как VLOOKUP() работает только слева направо. INDEX-MATCH также эффективнее при больших наборах данных, так как обращается только к нужным столбцам, а не ко всей строке. Он менее подвержен ошибкам при добавлении или удалении столбцов, поскольку использует ссылку на столбцы, не зависящую от их модификации.
Стоит отметить, что XLOOKUP(), представленный в Microsoft 365, устраняет большинство ограничений VLOOKUP() и в большинстве случаев проще, чем INDEX-MATCH. На собеседовании умение объяснить все три — VLOOKUP(), INDEX-MATCH и XLOOKUP() — и когда что использовать, демонстрирует всесторонние знания поисковых функций Excel.
Как создать раскрывающийся список с помощью проверки данных?
Чтобы создать раскрывающийся список в Excel с помощью проверки данных, выделите ячейку или диапазон, где нужен список. Затем перейдите на вкладку Данные на ленте и нажмите Проверка. В окне Проверка данных на вкладке Параметры выберите Список в поле Тип данных:.
В поле Источник: введите элементы списка через запятую или нажмите стрелку вверх, чтобы выбрать диапазон ячеек с элементами списка. Убедитесь, что параметр Список в ячейке отмечен. Нажмите ОК, чтобы применить проверку данных и создать раскрывающийся список.
Можно ли извлечь доменное имя из адреса электронной почты?
Предположим, адрес электронной почты example@email.com находится в ячейке A1. Введите в другую ячейку (например, B1) формулу =RIGHT(A1, LEN(A1) - FIND("@", A1)), которая вернёт email.com.
-
FIND("@", A1)находит позицию символа@в адресе. -
LEN(A1)вычисляет общую длину адреса. -
Вычитая позицию
@из общей длины, получаем длину доменной части. -
Наконец,
RIGHT(A1, LEN(A1) - FIND("@", A1))извлекает доменную часть справа от адреса.
Что такое подстановочные знаки в Excel? Как их применять?
В Excel подстановочные знаки — это специальные символы, используемые в текстовом поиске и функциях для обозначения одного или нескольких символов, что делает поиск и сопоставление более гибкими и мощными.
Три основных подстановочных знака: звёздочка (*) — представляет любое количество символов, вопросительный знак (?) — один символ, и тильда (~) — для экранирования подстановочных символов.
Подстановочные знаки можно использовать в различных функциях Excel, например SEARCH, FIND, REPLACE, SUBSTITUTE, а также в фильтрах и условном форматировании. Например, формула =COUNTIF(A1:A10, "*test*") подсчитает все ячейки в диапазоне A1:A10, где слово "test" встречается в любом месте текста.
Можно ли применить срез для фильтрации данных?
Чтобы применить срез для фильтрации данных в Excel, сначала убедитесь, что данные оформлены как таблица или являются частью сводной таблицы. Щёлкните в пределах таблицы или сводной таблицы, затем перейдите на вкладку Вставка на Ленте и нажмите Срез в группе Фильтры. В появившемся окне отметьте столбцы для срезов и нажмите ОК. На листе появится срез, с помощью которого можно фильтровать данные таблицы или сводной таблицы, просто нажимая на элементы среза.
Что такое подбор параметра (Goal Seek) в Excel?
Goal Seek в Excel — инструмент, который позволяет найти входное значение, необходимое для достижения заданной цели или целевого показателя в формуле. Он изменяет одно входное значение так, чтобы результат формулы совпал с желаемым значением. Доступ к Подбору параметра осуществляется на вкладке Данные через кнопку Анализ "что‑если", где вы указываете ячейку с формулой, целевое значение и изменяемую ячейку для достижения цели.
В примере ниже мы хотим узнать, под какой процент мы будем платить, если ежемесячно вносить $900 в течение 15 лет по кредиту $100 000. Подбор параметра поможет это выяснить!
Вопросы по Excel для аналитиков данных
В завершение добавим раздел для тех, кто проходит собеседование на позицию аналитика данных. Ещё один отличный способ подготовиться — пройти наш курс Data Analysis in Excel.
Как вы обрабатываете пропуски в данных в Excel?
Чтобы управлять пропущенными данными в Excel, начните с использования условного форматирования для подсветки пустых ячеек. Например, выделите диапазон данных, перейдите Главная > Условное форматирование > Правила выделения ячеек > Пустые и выберите цвет. Вы можете заменить пропуски средним значением через функцию AVERAGE(). Например, в новом столбце используйте =IF(ISBLANK(A2), AVERAGE($A$2:$A$100), A2), чтобы заполнить пустые ячейки средним по столбцу.
Можете объяснить, как использовать функцию проверки данных (Data Validation) в Excel?
Используйте Проверку данных, чтобы ограничивать ввод и поддерживать качество данных. Выделите ячейки с ограничениями, затем перейдите в Данные > Проверка данных. Например, чтобы создать выпадающий список допустимых вариантов, выберите «Список» в поле «Разрешить» и введите варианты через запятую (например, «Yes, No»). Это ограничит ввод только этими значениями. Также можно применять пользовательские формулы, например =ISNUMBER(A1), чтобы разрешить ввод только чисел.
Как вы используете Power Query в Excel для объединения наборов данных?
Power Query упрощает слияние наборов данных. Перейдите в Данные > Получить данные > Объединить запросы > Объединить. В диалоговом окне выберите объединяемые наборы и общий столбец. После загрузки данных используйте инструменты преобразования Power Query для очистки и приведения данных. Например, можно фильтровать строки, разделять столбцы или менять типы данных, обеспечивая согласованность объединённого набора.
Как вычислить перцентили или квартели в Excel?
Для вычисления перцентилей используйте функции PERCENTILE.INC() или PERCENTILE.EXC(). Например, =PERCENTILE.INC(A1:A100, 0.75) вычисляет 75‑й перцентиль набора в ячейках A1:A100. Аналогично, для квартилей используйте =QUARTILE(A1:A100, 1) для первого квартиля или =QUARTILE(A1:A100, 3) для третьего.
Можете описать, как использовать надстройку Data Analysis Toolpak?
Сначала включите Data Analysis Toolpak: Файл > Параметры > Надстройки > Analysis Toolpak > Управление > Перейти > ОК. Для регрессионного анализа перейдите Данные > Анализ данных > Регрессия, укажите зависимую и независимые переменные и нажмите ОК. Excel создаст подробный отчёт с коэффициентами и R‑квадратом. Используйте это для прогнозирования или проверки гипотез.
Вопросы по Excel для пользователей ИИ и Copilot
Что такое Copilot в Excel и что он умеет?
Copilot — это ИИ‑ассистент Microsoft, встроенный прямо в Excel для Microsoft 365. Он располагается в боковой панели и позволяет взаимодействовать с книгой на естественном языке. Возможности включают пошаговое рассуждение, прямое редактирование книги, а также использование Python прямо из Copilot для продвинутого анализа данных, построения визуализаций и выполнения сложных многошаговых задач, не покидая таблицы.
На собеседовании важно подчеркнуть, что Copilot — это ускоритель продуктивности, а не замена знаниям Excel. Вам по‑прежнему нужно понимать полученные результаты и проверять их.
Как работает Python в Excel и когда его использовать?
Python в Excel — общедоступная функция Microsoft 365, позволяющая запускать код Python прямо в таблице с помощью функции =PY(). Python выполняется в защищённой облачной среде — ничего не нужно устанавливать локально.
Используйте его, когда стандартных функций Excel недостаточно: для статистического моделирования, машинного обучения по табличным данным, создания продвинутых визуализаций с библиотеками Matplotlib или seaborn, либо для обработки данных в объёмах, которые замедлят нативный Excel. В повседневных задачах родные функции Excel пишутся быстрее и понятнее для коллег — поэтому Python в Excel лучше применять для действительно сложных задач.
Что такое функция COPILOT() в Excel?
Функция COPILOT() — это новая возможность, позволяющая вводить запрос на естественном языке прямо в ячейку, при необходимости ссылаться на другие ячейки и получать результаты от ИИ, которые автоматически обновляются при изменении данных — тем самым интегрируя Copilot в вычислительный движок Excel, а не в отдельную панель.
Например, =COPILOT("Summarize the trend in ", A1:A12) может сгенерировать текстовое описание тренда ряда данных. Наиболее полезна для динамических текстов — сводок, подписей или интерпретаций — которые обновляются по мере изменения исходных данных.
Как вы бы использовали Copilot, чтобы составить формулу, которую не знаете, как написать?
Сильный ответ показывает процесс, а не только знание инструмента. Опишите цель простым языком в панели Copilot — например: «вернуть самую позднюю дату продажи для каждого клиента из столбца A, используя даты в столбце C» — и Copilot предложит формулу, обычно с функциями MAXIFS() или XLOOKUP(). Затем вы проверяете формулу, чтобы убедиться в корректности диапазонов и логики, прежде чем принять её.
Главная мысль для собеседования: Copilot отлично помогает открывать незнакомые функции или получать отправную точку для сложных вложенных формул — но вам всё равно нужно достаточное знание Excel, чтобы валидировать результат.
Заключение
Наш последний совет для вашего собеседования по Excel — продолжайте учиться! Инструменты вроде Excel постоянно адаптируются к запросам рынка (см. появление Python in Excel от Anaconda и интеграцию управляемого LLM Copilot в Excel от Microsoft). Хотя вряд ли вас будут напрямую проверять на новейшие технологии, знание о них может дать вам преимущество на собеседовании (при условии, что вы сначала продемонстрируете ключевые компетенции, требуемые для роли). Удачи!
Если перед собеседованием вы нервничаете, загляните в наш шпаргалку по формулам Excel, чтобы освежить ключевые концепции и формулы. И помните: интервью — это всего лишь разговор. Воспринимайте его как встречу со старым другом после долгой разлуки.