Легко запомнить это по аббревиатуре PEMDAS , которая является первым алфавитом каждого оператора.

В чем разница между функцией и формулой в Excel?

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

Например, = A1 + A2 — это формула, а = SUM (A1: A10) — функция.

Какие, по вашему мнению, 5 основных функций в Excel?

Этот вопрос часто задают, чтобы понять, насколько кандидат удобен для работы с функциями Excel. Хотя в Excel более 450 функций и нет установленных критериев для выбора первой пятерки, вот те, которые я считаю достойными:

• VLOOKUP
• COUNTIF
• SUMIF
• IFERROR
• INDEX / MATCH

Я выбрал перечисленные выше функции, поскольку они не очень простые и весьма полезны для тех, кто занимается анализом в Excel.Вы также можете рассмотреть следующие функции — СУММПРОИЗВ, ТЕКСТ, СУММ, СРЕДНЕЕ, LEN / LEFT / RIGHT / MID.

Опять же, на этот вопрос нет правильного или неправильного ответа. Просто убедитесь, что вы знаете упомянутые функции.

Здесь вы можете найти подробные объяснения наиболее популярных функций Excel.

В чем разница между абсолютными и относительными ссылками на ячейки?

Ссылка в Excel означает ссылку на ячейку, например A1, или ссылку на диапазон, например A1: A10.

Относительные ссылки: это ссылки на ячейки, которые изменяются при копировании и вставке формулы, содержащей ссылки. Чтобы дать вам простой пример, если вы поместите = A10 в ячейку A1, а затем скопируете ячейку A1 и вставите ее в ячейку A2, ссылка изменится на A11. Это происходит потому, что это относительная ссылка на ячейку, и она изменяется относительно ячейки, из которой скопирована.

Абсолютные ссылки: это ссылки, которые остаются неизменными и не меняют, скопируйте и вставьте формулу, в которой есть ссылки.Например, если вы поместите = $ A $ 10 в ячейку A1, а затем скопируете ячейку A1 и вставите ее в ячейку A2, ссылка все равно останется $ A $ 10. Знак $ перед алфавитом столбца и номером строки делает его абсолютным.

Подробнее об абсолютных и относительных ссылках можно прочитать здесь.

Какие типы ошибок могут возникать в Excel?

При работе с Excel вы можете столкнуться со следующими шестью типами ошибок:

1. # N / A Ошибка : Это называется ошибкой «Значение недоступно».Вы увидите это, если воспользуетесь формулой поиска и не сможете найти значение (следовательно, недоступно).
2. # DIV / 0! Ошибка: . Вы, вероятно, увидите эту ошибку, когда число делится на 0. Это называется ошибкой деления.
3. #VALUE! Erro r: ошибка значения возникает при использовании неправильного типа данных в формуле.
4. # ССЫЛКА! Ошибка : это называется ошибкой ссылки, и вы увидите это, когда ссылка в формуле больше не действительна.643 вернет числовую ошибку.

Как исправить ошибки при работе с формулами Excel?

Есть несколько способов исправить ошибки в Excel:

1. Вы можете выделить ошибки, используя условное форматирование. Это требует использования функции ISERROR в условном форматировании.
2. Вы можете использовать функцию ЕСЛИОШИБКА в Excel, чтобы получить конкретное значение, если формула возвращает ошибку.
3. Вы можете использовать ISERROR, чтобы получить TRUE, если есть ошибка, и FALSE, если ее нет.
4. Функцию IFNA можно использовать для устранения ошибки # N / A.

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

Можно использовать следующие функции:

• СЕГОДНЯ () — эта функция не принимает аргументов и возвращает текущее значение даты.
• NOW () — эта функция не принимает аргументов и возвращает текущее значение даты и времени.

Помните, что дата и время хранятся в Excel в виде чисел.Таким образом, вы можете выполнять такие операции, как сложение / вычитание с этими датами.

Как можно объединить текст из нескольких ячеек с помощью формулы?

Чтобы объединить текст из разных ячеек, вы можете использовать любой из следующих трех методов:

1. Функция TEXTJOIN. Если вы используете подписку на Office 365, в вашей версии будет доступна функция TEXTJOIN. Щелкните здесь, чтобы узнать, как это работает.
2. Функция СЦЕПИТЬ. Если вы хотите объединить значения в ячейках A1 и A2, вы можете использовать формулу = СЦЕПИТЬ (A1, A2)
3. Оператор амперсанда (&): это работает так же, как функция СЦЕПИТЬ.Чтобы объединить текстовые строки в ячейках A1 и A2, используйте формулу = A1 & A2

Подробнее о соединении строк в Excel можно узнать здесь.

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

Вы можете найти длину строки в ячейке с помощью функции ДЛИН.

Например, если вы хотите узнать длину строки в ячейке A1, вы можете использовать формулу = LEN (A1)

Каков синтаксис функции ВПР?

ВПР — определенно одна из самых популярных функций Excel.И это также один из самых часто задаваемых вопросов об Excel, который я видел в интервью.

Вот синтаксис ВПР:

= ВПР (lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value — это значение для поиска, которое вы больше всего пытаетесь найти в левом столбце стола. Это может быть значение, ссылка на ячейку или текстовая строка. В примере с оценочным листом это будет ваше имя.
• table_array — это массив таблиц, в котором вы ищите значение.Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с оценочным листом это будет вся таблица, содержащая оценки для всех по каждому предмету.
• col_index — это номер индекса столбца, из которого вы хотите получить соответствующее значение. В примере с оценочным листом, если вам нужны оценки по математике (первый столбец в таблице, содержащий оценки), вы должны посмотреть в столбец 1. Если вы хотите получить оценки по физике, вы должны посмотреть в столбец 2.
• [range_lookup] — здесь вы указываете, хотите ли вы точное или приблизительное совпадение. Если опущено, по умолчанию используется значение ИСТИНА — приблизительное совпадение .

Если у вас есть время, я рекомендую просмотреть это руководство по функциям ВПР, которое я создал с 10 практическими примерами.

Как бы вы избавились от начальных / конечных / двойных пробелов в тексте в Excel?

Чтобы избавиться от начальных, конечных и двойных пробелов, вам необходимо использовать функцию ОБРЕЗАТЬ.

Например, если у вас есть текстовая строка в ячейке A1, и вы хотите удалить пробелы, вы можете использовать следующую формулу:

= TRIM (A1)

Обратите внимание, что при этом не удаляются отдельные пробелы между словами.

Функция Excel TRIM хорошо справляется с удалением пробелов в Excel, однако она не работает, если в наборе данных есть непечатаемые символы (например, разрывы строк). Чтобы удалить непечатаемые символы, вы можете использовать комбинацию функций ОБРЕЗАТЬ и ОЧИСТИТЬ.

Если у вас есть текст в ячейке A1, из которого вы хотите удалить пробелы, используйте следующую формулу:

= TRIM (CLEAN (A1))

Подробнее об этом можно прочитать здесь.

Каковы известные ограничения функции ВПР?

Функция ВПР очень полезна, но у нее также есть несколько ограничений:

• Ее нельзя использовать, если значение поиска указано справа. Чтобы функция ВПР работала, значение поиска всегда должно быть в крайнем левом столбце. Теперь это ограничение можно преодолеть, используя его с другими формулами, это делает формулы сложными.
• ВПР даст неверный результат, если вы добавите / удалите новый столбец в своих данных (поскольку значение номера столбца теперь относится к неправильному столбцу). Вы можете сделать номер столбца динамическим, но если вы планируете объединить две или более функций, почему бы в первую очередь не использовать ИНДЕКС / ПОИСКПОЗ.
• При использовании с большими наборами данных это может замедлить вашу книгу.

Вы можете прочитать мое сравнение VLOOKUP Vs. ИНДЕКС / МАТЧ здесь.

Вот несколько примеров использования комбинации ИНДЕКС ПОИСКПОЗ в Excel.

Когда бы вы использовали функцию ПРОМЕЖУТОЧНЫЙ ИТОГ?

Когда вы работаете с табличными данными, вы можете использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ для получения различных промежуточных итогов, таких как СРЕДНИЙ, СЧЁТ, МАКС, МИН, СТАНДОТКЛОН.

Одним из преимуществ функции ПРОМЕЖУТОЧНЫЙ ИТОГ является то, что она позволяет игнорировать скрытые / отфильтрованные ячейки. Поэтому, если у вас есть огромный набор данных и вы фильтруете его на основе критериев или скрываете некоторые строки, функция ПРОМЕЖУТОЧНЫЙ ИТОГ будет автоматически обновляться, чтобы дать вам результат только для видимых ячеек.

Конечно, если вы не хотите, чтобы данные отфильтрованных / скрытых ячеек игнорировались, вы тоже можете это сделать.

Что такое энергозависимые функции? Вы можете назвать несколько?

Неустойчивая функция повторно вычисляет формулу снова и снова (всякий раз, когда есть какие-либо изменения в рабочем листе).Это может значительно замедлить работу книги.

Очень простым примером изменчивой функции является функция NOW () (для получения текущей даты и времени в ячейке). Всякий раз, когда вы редактируете любую ячейку на листе, она пересчитывается. Это нормально, если у вас небольшой набор данных и меньшее количество формул, но если у вас большие электронные таблицы, это может значительно замедлить обработку.

Вот список изменчивых формул:

• Высоко изменчивый: RAND (), NOW (), TODAY ()
• Почти изменчивый: OFFSET (), CELL (), INDIRECT (), INFO ()

БОНУСНЫЙ СОВЕТ : Условное форматирование также непостоянно.Вы должны использовать его только там, где это необходимо.

Сочетания клавиш в Excel

Ниже приведены некоторые общие вопросы собеседования с Excel о сочетаниях клавиш, которые вам могут задать во время собеседования (щелкните вопрос, чтобы просмотреть ответ).

Какие сочетания клавиш вы используете наиболее часто?

Существуют сотни сочетаний клавиш Excel. Я перечисляю свои пять основных горячих клавиш, но если у вас есть собственный, не стесняйтесь его использовать.

1. CONTROL D , чтобы заполнить содержимое ячейки выше.
2. SHIFT пробел для выбора всей строки (или CONTROL + пробел для выбора всего столбца).
3. УПРАВЛЕНИЕ — для удаления ячеек / строки / столбца. Откроется диалоговое окно удаления, в котором вы можете выбрать, что удалить.
4. КОНТРОЛЬ; , чтобы вставить текущую дату (CONTROL SHIFT; вставляет текущее время).
5. CONTROL PAGEUP / PAGEDOWN для циклического просмотра рабочих листов.

Вы можете прочитать следующие руководства, если вам нужно больше сочетаний клавиш:

Какой ярлык для открытия диалогового окна поиска и замены

• CONTROL F — откроется диалоговое окно «Найти и заменить» с выбранной вкладкой «Найти».
• CONTROL H — откроется диалоговое окно «Найти и заменить» с выбранной вкладкой «Заменить».

Что такое ярлык для проверки орфографии?

F7 — открывает диалоговое окно проверки орфографии.

Какой ярлык для быстрого автоматического суммирования строк / столбцов?

ALT =

Если у вас есть числа в столбце / строке, вы можете быстро получить сумму с помощью этого сочетания клавиш Excel.

Какой ярлык позволяет открыть новую книгу Excel?

КОНТРОЛЬНЫЙ N

Работает только тогда, когда у вас открыто приложение Excel.

Как выбрать все ячейки на листе

Вы можете использовать CONTROL A A — удерживая клавишу Control, дважды нажмите клавишу A.

Если у вас нет данных вокруг активной ячейки, однократное нажатие клавиши A выберет весь рабочий лист. Но в случае, если есть данные, нажатие клавиши A один раз выделит все данные и повторное нажатие выберет все ячейки на листе.

Как бы вы вставили новую строку в ту же ячейку?

Чтобы вставить новую строку в ту же ячейку, используйте ярлык ALT Введите — удерживайте клавишу ALT и нажмите клавишу ВВОД.

Какой ярлык для вставки комментария в Excel?

SHIFT F2

Выберите ячейку, в которую вы хотите добавить комментарий, удерживая клавишу ALT, нажмите клавишу F2.

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

Вопросы о сводной таблице Excel

Ниже приведены некоторые распространенные вопросы на собеседовании в Excel о сводной таблице, которые вам могут задать во время собеседования (щелкните вопрос, чтобы просмотреть ответ).

Что такое сводная таблица и когда вы бы ее использовали?

Сводная таблица — это функция Microsoft Excel, которая позволяет быстро суммировать огромные наборы данных (с помощью нескольких щелчков мышью).

Даже если вы новичок в мире Excel, вы можете легко использовать сводную таблицу. Для создания отчетов достаточно просто перетащить заголовки строк / столбцов.

В качестве примера: если у вас есть данные о продажах в 10 000 строк из четырех разных регионов, вы можете использовать сводную таблицу, чтобы мгновенно узнать, каковы общие продажи в каждом регионе. Если вы хотите более детально изучить продажи каждого продукта в каждом регионе, вы можете легко это сделать (это буквально так же просто, как перетащить точку данных в поле).

Подробнее о сводных таблицах можно прочитать здесь.

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

Сводная таблица состоит из четырех разных разделов:

• Область значений : это область, в которой отображаются значения.

• Область строк : заголовки слева от области значений составляют область строк.

• Область столбцов : заголовки в верхней части области значений составляют область столбцов.

• Область фильтра : это дополнительный фильтр, который можно использовать для дальнейшей детализации в наборе данных.

Что такое слайсеры?

Срезы были представлены в сводной таблице в версии Excel 2010 года.

Срез сводной таблицы позволяет фильтровать данные, когда вы выбираете один или несколько параметров в поле среза (как показано ниже).

Что такое сводная диаграмма

При создании сводной таблицы вы получаете сводку своих данных.Вы также можете нанести эту сводку на диаграмму, связанную с данными.

Эта диаграмма называется сводной диаграммой.

Одним из больших преимуществ использования сводной диаграммы является то, что она обновляется при изменении макета сводной таблицы. Например, если у вас есть общие продажи по регионам, и вы обновляете сводную таблицу, чтобы отображать данные о продажах для каждого продукта в регионах, сводная диаграмма будет соответствующим образом обновлена.

В чем разница между сводными диаграммами и обычными диаграммами?

Хотя сводные диаграммы великолепны и имеют возможность обновляться при обновлении сводной таблицы, они не так гибки, как обычные диаграммы.

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

Несмотря на ограничения, сводные диаграммы полезны и могут помочь создавать быстрые представления из сводной таблицы.

Как обновить сводную таблицу?

Чтобы обновить сводную таблицу, щелкните любую ячейку в сводной таблице, щелкните правой кнопкой мыши и выберите «Обновить».

Другой способ обновления сводной таблицы — выбрать любую ячейку в сводной таблице. Это активирует вкладку Инструменты сводной таблицы. На вкладке «Анализ» нажмите «Обновить».

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

Можно ли сгруппировать даты в сводных таблицах?

Если у вас есть записи по дате, вы можете легко сгруппировать их в следующие сегменты:

• Годы
• Кварталы
• Месяцы
• Недели
• Дней
• Часы / Минуты / Секунды

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

Подробнее о группировке дат в сводной таблице можно прочитать здесь.

Что такое сводный кеш?

Сводный кэш — это то, что автоматически создается при создании сводной таблицы. Это объект, который содержит реплику источника данных. Хотя вы его не видите, он является частью книги и связан со сводной таблицей. Когда вы вносите какие-либо изменения в сводную таблицу, она не использует источник данных, а использует сводный кеш. Причина создания сводного кеша заключается в оптимизации работы сводной таблицы.Даже когда у вас есть тысячи строк данных, сводная таблица очень быстро суммирует их. Вы можете перетаскивать элементы в поля строк / столбцов / значений / фильтров, и это мгновенно обновит результаты. Pivot Cache обеспечивает быстрое функционирование сводной таблицы.

Можно ли составить сводную таблицу из нескольких таблиц?

Да, вы можете создать одну сводную таблицу из нескольких разных таблиц. Однако в этих таблицах должна быть связь.

Например, если у вас есть две таблицы, одна с датой, идентификатором продукта и стоимостью продажи, а другая с идентификатором продукта и названием продукта, вы можете объединить их как общий столбец в идентификаторе продукта.

После того, как вы соединили эти таблицы, вы можете создать из них сводную таблицу.

Важнейшей частью этого является установка связей между таблицами (где вы указываете связь между двумя таблицами).

Подробнее об этом можно прочитать здесь.

Что такое вычисляемые поля в сводной таблице?

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

Приведу простой пример.

Предположим, у вас есть набор данных о розничных продавцах и вы создали сводную таблицу, как показано ниже:

В приведенной выше сводной таблице суммируются значения продаж и прибыли для розничных продавцов.

Теперь, что, если вы также хотите знать, какова была норма прибыли этих розничных продавцов (где норма прибыли равна «Прибыль», деленная на «Продажи»).

У вас есть несколько вариантов:

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

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

Использование вычисляемого поля сводной таблицы дает множество преимуществ:

• Оно не требует от вас обработки формул или обновления исходных данных.
• Он масштабируемый, поскольку он автоматически учитывает любые новые данные, которые вы можете добавить в свою сводную таблицу. После добавления поля вычисления вы можете использовать его как любое другое поле в сводной таблице.
• Легко обновлять и управлять. Например, если показатели меняются или вам нужно изменить расчет, вы можете легко сделать это из самой сводной таблицы.

Подробнее о вычисляемом поле сводной таблицы можно прочитать здесь.

Вопросы по построению диаграмм в Excel

Ниже приведены некоторые распространенные вопросы о построении диаграмм в Excel, которые могут возникнуть на собеседовании (щелкните вопрос, чтобы просмотреть ответ).

Что такое столбчатая диаграмма?

Столбчатая диаграмма состоит из вертикальных полос, которые используются для сравнения значений с течением времени или для сравнения двух значений в разных категориях.

Например, вы можете использовать его, чтобы увидеть, как выросли продажи за эти годы. Или вы можете использовать его, чтобы сравнить, какая категория продуктов принесла больше продаж. Поскольку вы можете видеть все вертикальные полосы за один раз, их легче визуально увидеть и сравнить.

Вы также можете создавать кластерные столбчатые диаграммы, где у вас может быть несколько столбцов для одной и той же категории или года (как показано ниже).

Что такое гистограмма?

Гистограмма состоит из горизонтальных полос, которые используются для сравнения значений в разных категориях.

Например, вы можете использовать его, чтобы сравнить, какая категория продуктов принесла лучшие продажи. Или каков был ответ на опрос.

Вы также можете создавать сгруппированные гистограммы, где у вас может быть несколько столбцов для одной и той же категории (как показано ниже).

Что такое линейный график?

Линейный график полезен, когда вы хотите показать тенденцию за годы (или другие периоды времени, такие как недели, месяцы или кварталы).

На линейном графике может быть несколько линий. Это позволит вам сравнивать разные категории за один и тот же период времени (как показано ниже).

Что такое точечная диаграмма?

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

Это позволяет увидеть, какие продукты хорошо работают по обоим KPI (верхний правый квадрант) или плохо работают по обоим KPI (нижний левый квадрант).

Здесь вы можете увидеть пример точечной диаграммы в действии на панели KPI Dashboard.

Насколько хороши круговые диаграммы? Следует ли использовать его в отчетах / на панели инструментов?

Есть две школы мысли.

Есть некоторые, кто полностью ненавидит круговую диаграмму и рекомендует никогда не использовать их (например, эта статья). А есть некоторые (включая меня), которые иногда используют круговые диаграммы на информационных панелях в отчетах.

Многие менеджеры знакомы с круговыми диаграммами и считают их легко читаемыми.Поэтому, если вы хотите показать разбивку доходов по разделам (где у вас всего несколько подразделений), вы можете использовать круговую диаграмму.

Позвольте мне пояснить. Круговую диаграмму можно полностью заменить гистограммой. Дополнительных преимуществ от его использования нет. Но в некоторых случаях круговые диаграммы — это хорошая история (например, показывающая, что одно подразделение приносит ~ 75% дохода, как показано ниже).

Вам следует избегать использования круговых диаграмм:

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

Что такое диаграмма водопада? Когда бы вы его использовали?

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

Это поможет вам наглядно увидеть, как получается преобразование выручки в чистую прибыль после вычета всех затрат.

Что такое комбинированные диаграммы?

Комбинированные диаграммы — это диаграммы, в которых вы комбинируете более одного типа диаграмм. Популярным примером этого является отображение гистограммы с линейной диаграммой.

Комбинированные диаграммы позволяют представить и сравнить два различных набора данных, связанных друг с другом. Например, вам может быть интересно построить график показателей выручки компании, и в то же время вы сможете показать, как изменилась маржа прибыли. Комбинированная диаграмма (как показано ниже) — подходящий способ сделать это.

Что такое второстепенные оси на диаграмме?

На диаграмме есть ось Y, на которой вы показываете масштаб, в котором вы можете измерить диаграмму (гистограмма, линейная диаграмма или другие).

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

В качестве примера, если вы хотите отобразить выручку и маржу чистой прибыли на одной диаграмме, вам нужно показать две разные оси.Это связано с тем, что цифры выручки могут быть в тысячах или миллионах, но маржа чистой прибыли будет в процентах и ​​всегда меньше 100%. В этом случае у вас должно быть две оси: одна показывает масштаб для дохода, а вторая — для маржи чистой прибыли.

Итак, когда вы добавляете другие оси, они называются вторичными осями. На рисунке ниже оси справа — это второстепенные оси.

Что такое маркированная диаграмма? Когда мы должны его использовать?

Маркированные диаграммы были разработаны экспертом по приборным панелям Стивеном Фью, и с тех пор они получили широкое признание как одно из лучших графических представлений, где вам нужно показать эффективность по отношению к цели.

Одно из преимуществ маркированных диаграмм — это то, что они содержат много информации и занимают мало места в отчете или на информационных панелях.

Ниже приведен пример маркированной диаграммы:

Обратите внимание, что маркированные диаграммы не являются типом диаграммы по умолчанию в Excel, и для их создания необходимо выполнить несколько шагов.

Подробнее о маркированных диаграммах можно прочитать здесь.

Вопросы по анализу данных в Excel

Ниже приведены некоторые распространенные вопросы об анализе данных в Excel, которые могут возникнуть на собеседовании (щелкните вопрос, чтобы просмотреть ответ).

Как заменить одно значение другим в Excel?

Вы можете заменить одно значение другим с помощью функции НАЙТИ И ЗАМЕНИТЬ в Excel.

Для этого выберите набор данных и воспользуйтесь сочетанием клавиш — КОНТРОЛЬ H (удерживая контрольную клавишу, затем нажмите H). Откроется диалоговое окно «Найти и заменить».

В этом диалоговом окне вы можете указать значение, которое вы хотите изменить, и значение замены.

Если вы хотите узнать больше о поиске и замене, нажмите здесь.

Какие фильтры данных доступны в Excel?

В Excel вы можете фильтровать набор данных по типу данных.

В Excel доступны следующие типы фильтров данных:

• Текстовый фильтр
• Числовой фильтр
• Фильтр даты

Вы можете применить фильтр к набору данных, выбрав данные, затем щелкнув вкладку Главная и нажав на значке фильтра.

Если у вас есть табличные данные и вы применяете фильтры на основе данных в столбце, Excel покажет вам соответствующий фильтр.Например, если у вас есть текстовые данные, вам будут показаны фильтры, относящиеся к тексту (например, текст содержит, начинается с, заканчивается на и т. Д.).

You c

Как можно сортировать данные в Excel?

В Excel есть функция сортировки, которая позволяет сортировать данные по тексту, числам или цветам.

Вот несколько способов сортировки данных в Excel:

• Выберите данные и щелкните один из двух значков сортировки на вкладке «Данные».
• Выберите данные и щелкните значок «Сортировка».Откроется диалоговое окно сортировки, и вы сможете указать столбец для сортировки и критерии (по возрастанию / убыванию).
• Примените фильтр данных и щелкните фильтр. Наряду с параметрами фильтра он также показывает параметры сортировки данных.

Подробнее о сортировке данных можно прочитать здесь.

Что такое проверка данных?

Проверка данных позволяет вводить в ячейку только те данные, которые удовлетворяют критериям.

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

Параметры проверки данных доступны на вкладке данных.

Подробнее о проверке данных можно прочитать здесь.

Проверка данных может быть очень полезной при создании форм в Excel. Например, если вы хотите, чтобы пользователь вводил только свой возраст, вы можете использовать проверку данных, чтобы убедиться, что ячейка принимает только числовое значение.

Как можно транспонировать набор данных в Excel?

Существует два популярных способа транспонирования данных в Excel:

• Использование диалогового окна «Специальная вставка».
• Использование функции транспонирования.

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

Подробнее о транспонировании данных в Excel можно прочитать здесь.

Как выбрать все пустые ячейки в Excel?

Если вы работаете с набором данных, в котором есть пустые ячейки, вы можете легко выбрать эти ячейки в Excel.После выбора вы можете выделить их, удалить или добавить к ним какое-либо значение (например, 0 или NA).

Для этого вам необходимо использовать диалоговое окно «Перейти к специальному» в Excel.

Вот шаги для выбора всех пустых ячеек в Excel:

• Выберите весь набор данных (включая пустые ячейки)
• Нажмите F5 (откроется диалоговое окно «Перейти»).
• Нажмите кнопку «Специальный» (это открывает специальное диалоговое окно «Перейти к»)
• Выберите пустые ячейки и нажмите «ОК» (при этом будут выбраны все пустые ячейки в наборе данных)

Подробнее о выборе пустых ячеек в Excel можно узнать здесь.

Как удалить дубликаты из набора данных?

Excel имеет встроенную функцию, позволяющую удалять повторяющиеся ячейки / строки в Excel.

Вы можете найти опцию удаления дубликатов на вкладке «Данные».

Вот шаги для удаления дубликатов в Excel:

• Выберите данные.
• Щелкните вкладку «Данные», а затем выберите параметр «Удалить дубликаты».
• В диалоговом окне «Удаление дубликатов», если у ваших данных есть заголовки, убедитесь, что установлен флажок «Мои данные имеют заголовки».Выберите столбец, из которого вы хотите удалить дубликаты.
• Нажмите OK

Подробнее об удалении дубликатов в Excel можно узнать здесь.

Что такое расширенный фильтр в Excel?

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

Вот некоторые различия между обычным фильтром и расширенным фильтром:

• Хотя обычный фильтр данных будет фильтровать существующий набор данных, вы также можете использовать расширенный фильтр Excel для извлечения набора данных в другое место.
• Расширенный фильтр Excel позволяет использовать сложные критерии. Например, если у вас есть данные о продажах, вы можете отфильтровать данные по критерию, где торговый представитель — Боб, а регион — либо Север, либо Юг.

Подробнее о расширенном фильтре Excel можно узнать здесь.

Можно ли одновременно сортировать несколько столбцов?

Да, в Excel можно сортировать несколько столбцов.

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

Ниже приведен пример многоуровневой сортировки в Excel.

Обратите внимание, что в этом случае сначала сортируется столбец A, а затем сортируется столбец B. В конечном результате значения столбца A отсортированы, а столбец B отсортирован для каждого элемента в столбце A.

Для выполнения многоуровневой сортировки необходимо использовать диалоговое окно «Сортировка». Для этого выберите данные, которые вы хотите отсортировать, щелкните вкладку «Данные», а затем щелкните значок «Сортировка».

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

Подробнее о сортировке данных по нескольким столбцам можно прочитать здесь.

Что такое таблица данных с одной переменной?

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

Например, если вы хотите узнать, насколько изменится ежемесячный платеж при увеличении / уменьшении количества месяцев, вы можете настроить для него таблицу данных с одной переменной.Это может быть полезно, если вы хотите, чтобы ежемесячный платеж составлял менее 500 долларов США, и вы знаете, какие у вас есть все варианты (6 месяцев, 9 месяцев, 12 месяцев и т. Д.).

Возможность установки таблицы данных с одной переменной находится в Data в раскрывающемся списке «Что, если».

Подробнее о таблице данных с одной переменной можно прочитать здесь.

Что такое таблица данных с двумя переменными?

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

Например, если вы хотите узнать, насколько изменится ежемесячный платеж, если вы увеличите / уменьшите количество месяцев и процентную ставку.

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

Параметр для установки таблицы данных с двумя переменными находится на вкладке «Данные» в раскрывающемся списке «Анализ возможных вариантов».

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

Что такое диспетчер сценариев?

Диспетчер сценариев в Excel может быть предпочтительным инструментом, когда у вас есть несколько переменных, и вы хотите увидеть влияние на конечный результат изменения этих переменных. Если у вас изменяются только одна или две переменные, вы можете создать одну переменную. или таблица данных с двумя переменными. Но если у вас есть 3 или более 3 переменных, которые могут изменяться, тогда вам подойдет диспетчер сценариев.

Например, если вы региональный менеджер по продажам и у вас есть четыре подразделения, вы можете использовать диспетчер сценариев для создания различных сценариев (например):

• Ни в одной из областей не наблюдается роста продаж.
• Область A растет на 10%, но остальные 3 не растут.
• Области A и B увеличиваются на 10%, но две другие области не растут.

Вы поняли .. верно?

С помощью диспетчера сценариев в Excel вы можете легко создавать сценарии и анализировать их один за другим или как сводку по всем сценариям.

Подробнее о менеджере сценариев можно прочитать здесь.

Что такое поиск цели?

Goal Seek in Excel, как следует из названия, помогает вам в достижении значения (цели) путем изменения зависимого значения.

Например, если вы покупаете автомобиль и хотите узнать, какой ежемесячный платеж следует выбрать, чтобы ваш ежемесячный платеж не превышал 500 долларов, вы можете сделать это с помощью поиска цели.

Подробнее о Goal Seek можно прочитать здесь.

Что такое решатель?

Solver in Excel — это надстройка, которая позволяет получить оптимальное решение при наличии большого количества переменных и ограничений.Вы можете считать это продвинутой версией Goal Seek.

С помощью Solver вы можете указать, каковы ограничения и цель, которую необходимо достичь. Он выполняет вычисления в серверной части, чтобы дать вам возможное решение.

Подробнее о Solver можно прочитать здесь.

Вопросы по Excel VBA

Ниже приведены некоторые общие вопросы собеседования с Excel о VBA, которые вам могут задать во время собеседования (щелкните вопрос, чтобы просмотреть ответ).

Что такое VBA?

VBA означает Visual Basic для приложений.Это язык программирования, который можно использовать для автоматизации задач в Excel.

Каковы преимущества использования VBA в Excel?

Хотя Excel имеет множество удивительных функций и возможностей, в нем может не быть всего, что вам нужно.

VBA позволяет расширить возможности Excel, создавая коды, которые могут автоматизировать задачи в Excel. Вот некоторые из вещей, которые вы можете сделать с этим VBA:

• Автоматизируйте набор задач и сэкономьте время.
• Создавайте собственные функции Excel (на случай, если существующих функций недостаточно).
• Создавайте свои коды и делитесь ими с другими людьми, чтобы они также могли автоматизировать задачи и экономить время.
• Создавайте собственные приложения.

Что такое макрос в Excel?

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

Во многих случаях люди склонны использовать код VBA и макрос как взаимозаменяемые.

Как записать макрос в Excel?

Даже если вы ничего не знаете о VBA, вы все равно можете создавать макросы и автоматизировать свою работу.

Это можно сделать, записав макрос.

Когда вы записываете макрос, Excel внимательно следит за выполняемыми вами шагами и записывает их на понятном языке — VBA.

Теперь, когда вы останавливаете запись, сохраняете макрос и запускаете его, Excel просто возвращается к сгенерированному коду VBA и выполняет те же действия.

Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто позволив Excel записать ваши шаги один раз, а затем повторно использовать их позже.

Вы можете записать макрос с помощью параметра «Запись макроса», который доступен на вкладке «Разработчик» на ленте.

После того, как вы запишете макрос, Excel сохранит его с указанным вами именем, а затем вы можете легко использовать его столько раз, сколько захотите.

Подробнее о записи макроса можно прочитать здесь.

Какие ограничения записи макроса в Excel?

Хотя запись макроса — отличный способ быстро сгенерировать код, она имеет следующие ограничения:

• Вы не можете выполнить код, не выбрав объект. Если вы хотите, чтобы средство записи макросов перешло на следующий рабочий лист и выделило все заполненные ячейки в столбце A, не выходя из текущего рабочего листа, то оно не сможет этого сделать. Потому что, если я попрошу вас сделать это, даже вы не сможете этого сделать (не покидая текущий лист). И если вы не можете сделать это самостоятельно, как регистратор макросов фиксирует ваши действия? В таких случаях вам нужно вручную перейти и создать / отредактировать код.
• Вы не можете создать пользовательскую функцию с помощью средства записи макросов. С помощью VBA вы можете создавать собственные функции, которые можно использовать на листе как обычные функции. Вы можете создать это, написав код вручную.
• Вы не можете запускать коды на основе событий. В VBA вы можете использовать множество событий, таких как открытие книги, добавление рабочего листа, двойной щелчок по ячейке и т. Д., Чтобы запустить код, связанный с этим событием. Для этого нельзя использовать средство записи макросов.
• Вы не можете создавать петли с помощью устройства записи макросов . Когда вы вводите код вручную, вы можете использовать возможности циклов в VBA (например, For Next, For Each Next, Do While, Do until).Но вы не можете этого сделать, когда записываете макрос.
• Вы не можете анализировать условия: Вы можете проверить условия в коде с помощью средства записи макросов. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если оно истинно (или другого кода, если ложно).

Что такое UDF в Excel VBA?

UDF — это функция, определяемая пользователем в VBA. Их также называют пользовательскими функциями.

С помощью VBA вы можете создать пользовательскую функцию (UDF), которую можно использовать в рабочих листах, как обычные функции.

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

Подробнее о пользовательских функциях можно прочитать здесь.

Что такое события в VBA?

В Excel VBA событие — это действие, которое может инициировать выполнение указанного макроса.

Например, когда вы открываете новую книгу, это событие. Когда вы вставляете новый лист, это событие.Если дважды щелкнуть ячейку, это событие.

В VBA много таких событий, и вы можете создавать коды для этих событий. Это означает, что как только происходит событие, и если вы указали код для этого события, этот код будет немедленно выполнен.

Excel автоматически делает это, как только замечает, что произошло событие. Таким образом, вам нужно только написать код и поместить его в правильную подпрограмму обработки событий.

Подробнее о событиях Excel VBA можно прочитать здесь.

Какие полезные циклы в VBA?

В Excel VBA есть следующие циклы:

• Для следующего цикла
• Цикл «Пока»
• Цикл «Пока»
• Для каждого следующего цикла

Подробнее о циклах Excel VBA можно узнать здесь.

Какие существуют способы запуска макроса в Excel?

Вы можете использовать следующие способы запуска макроса в Excel:

• Назначьте макрос фигуре
• Назначьте макрос кнопке
• Запустите макрос с ленты (вкладка разработчика)
• Запустите макрос из редактор VB
• Запуск макроса с помощью сочетания клавиш
• Вызов макроса из другого макроса

Подробнее о запуске макроса можно прочитать здесь.

Что такое пользовательские формы?

UserForm — это диалоговое окно, которое можно спроектировать и построить в Excel VBA.

После создания они могут использоваться многими способами в области рабочего листа Excel:

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

Что такое надстройки?

Надстройка — это файл, который можно загрузить с помощью Excel при запуске.

Когда вы создаете надстройку и устанавливаете ее в Excel, она открывается всякий раз, когда открывается приложение Excel. У вас может быть много разных макросов в надстройке, и всякий раз, когда вы открываете Excel, эти коды доступны для использования.

Это полезно, поскольку вы можете создать надстройку, и она станет доступной для всех книг. Поэтому, если есть какие-то задачи, которые вам нужно выполнять часто, вы можете автоматизировать их, написав макрос, а затем сохранив их как надстройки.Теперь независимо от того, какой Excel вы открываете, вы можете использовать макрос.

Еще одним преимуществом надстройки является то, что вы также можете поделиться файлом надстройки с другими. Все, что им нужно сделать, это установить его один раз, и им также будут доступны те же самые макросы.

Подробнее о создании надстройки можно прочитать здесь.

Вопросы о панелях мониторинга Excel

Ниже приведены некоторые распространенные вопросы собеседований по Excel о панелях мониторинга, которые вам могут задать во время собеседования (щелкните вопрос, чтобы просмотреть ответ).

В чем разница между панелью мониторинга и отчетом?

Отчет предназначен для предоставления соответствующих данных. Это может быть отчет, содержащий все данные или несколько диаграмм / визуализаций. Примерами отчетов могут быть данные о продажах или данные опросов сотрудников.

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

Подробнее о панелях мониторинга Excel можно прочитать здесь.

Какие вопросы вы должны задать клиенту / заинтересованной стороне перед созданием информационной панели?

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

• Какова цель панели мониторинга?
• Какие источники данных?
• Кто будет использовать эту панель Excel?
• Как часто нужно обновлять панель Excel Dashboard?
• Какую версию Office использует клиент / заинтересованное лицо?

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

Какие интерактивные элементы управления можно использовать на информационных панелях?

Excel имеет ряд интерактивных инструментов, которые можно использовать на панели мониторинга:

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

Какие полезные типы диаграмм можно использовать на информационной панели?

При создании панели мониторинга необходимо отображать соответствующие данные с помощью визуальных элементов.Диаграммы, которые могут рассказать хорошую историю и показать релевантную информацию, больше подходят для панели инструментов.

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

Следующие диаграммы могут быть полезны при создании информационной панели:

• Комбинированные диаграммы
• Точечные диаграммы
• Маркированная диаграмма
• Водопадная диаграмма
• Тепловые карты

Какие лучшие практики при создании информационной панели в Excel ?

Вот несколько рекомендаций по созданию панели мониторинга в Excel:

• Преобразование табличных данных в таблицы Excel: Создание панелей мониторинга с использованием таблицы Excel в качестве источника данных намного проще.Если у вас нет веских причин против, всегда конвертируйте внутренние данные в таблицу Excel.
• Нумерация ваших диаграмм / разделов: Это помогает, когда вы представляете информационную панель и ссылаетесь на разные диаграммы / таблицы. Легче попросить людей сосредоточиться на диаграмме под номером 2, вместо того, чтобы говорить о линейной диаграмме или столбчатой ​​диаграмме в верхнем левом углу.
• Ограничить перемещение в области панели мониторинга : Хотя рабочий лист Excel огромен, лучше удалить все строки / столбцы, кроме тех, которые имеют панель мониторинга.
• Закрепить Важные строки / столбец : Если вы хотите, чтобы некоторые строки / столбцы всегда были видны, вы можете закрепить их.
• Make Shapes / Charts Stick : Пользователь может изменить ширину строки / столбца. Вы не хотите, чтобы фигуры и диаграммы смещались с исходного места. Так что лучше заставить их придерживаться своей позиции.
• Предоставьте руководство пользователя : Было бы неплохо добавить отдельный лист с подробными сведениями о том, как использовать панель управления.Это становится более полезным, когда у вас есть интерактивные элементы управления на панели управления.
• Экономия места с помощью комбинированных диаграмм : Поскольку на панели инструментов мало места (так как вам нужно разместить его на одном экране), использование комбинированных диаграмм может сэкономить ваше место.
• Использование символов и условного форматирования : Вы можете сделать информационную панель более наглядной и удобной для чтения с помощью символов и условного форматирования. Например, легче увидеть наименьшее значение в таблице, когда оно выделено красным, вместо того, чтобы просматривать все значения одно за другим.
• Разумно используйте цвета, чтобы показать контраст: Если вы хотите выделить точку данных в таблице или полосу на диаграмме, лучше выделить ее, выделив привлекательным цветом.

Другие вопросы по Excel

Это вопросы, которые я не мог поместить ни в одну из вышеперечисленных категорий. Итак, я собираю все это вместе (нажмите на вопрос, чтобы увидеть ответ).

Что такое таблица Excel?

Таблица Excel — это функция Excel.Это не то же самое, что табличные данные.

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

Согласно сайту справки Microsoft — «Таблица обычно содержит связанные данные в серии строк и столбцов рабочего листа, которые были отформатированы как таблица. Используя функции таблицы, вы можете управлять данными в строках и столбцах таблицы независимо от данных в других строках и столбцах на листе.”

Подробнее о таблицах Excel можно узнать здесь.

Каковы преимущества использования таблицы Excel?

При преобразовании табличных данных в таблицу Excel к данным автоматически добавляются следующие функции:

• Вы можете использовать значок фильтра в заголовке каждого столбца для сортировки и фильтрации данных.
• Вы можете легко применить стили к таблице и отформатировать ее. Доступно множество встроенных стилей, которые можно использовать одним щелчком мыши.
• Вы можете использовать имя таблицы и имена столбцов вместо ссылок на ячейки в формулах.
• Когда вы добавляете новую строку / столбец к табличным данным, таблица Excel автоматически расширяется, чтобы покрыть новую строку / столбец как ее часть. А поскольку вы можете использовать имена таблиц / столбцов в формулах, вам не нужно обновлять формулы при добавлении новых строк / столбцов. Таблица Excel автоматически учитывает это.

Что такое структурированные ссылки?

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

Какие обычно используемые форматы файлов, в которых можно сохранить файл Excel?

Существует множество форматов файлов, в которых можно сохранить книгу Excel. Вот некоторые из наиболее часто используемых:

• .XLS
• .XLSX
• .XLSM (если в файле есть макросы)
• .CSV
• .XLB (двоичный формат)
• .XLA (для надстроек)
• .PDF

Как уменьшить размер файла Excel?

Есть много способов уменьшить размер файла книги Excel:

• Удалить неиспользуемые данные
• Удалить неиспользуемые рабочие листы
• Удалить изображения или сжать эти изображения
• Удалить ненужное форматирование
• Удалить ненужные сводные таблицы нужно. Это также поможет очистить сводный кеш.
• Сохраните файл в формате .XLSB

Подробнее : 8 способов уменьшить размер файла в Excel

Какие шаги вы можете предпринять, чтобы справиться с медленными книгами Excel?

Для работы с медленными книгами Excel можно использовать следующие методы:

• Избегайте изменчивых функций, таких как КОСВЕННО, СМЕЩЕНИЕ и т. Д.
• Используйте вспомогательные столбцы вместо формулы массива
• Используйте условное форматирование только в случае крайней необходимости (поскольку оно также является изменчивым).
• Используйте таблицы и именованные диапазоны Excel.
• Преобразование неиспользуемых формул в значения.
• Храните все данные на одном листе.
• Избегайте использования всей строки / столбца в ссылках.
• Использовать ручной режим расчета.

Подробнее о том, как работать с медленной книгой Excel, можно узнать здесь.

Сколько строк и столбцов на листе Excel?

Лист Excel (2007 и более поздние версии) содержит:

• 16 384 столбца
• 1048 576 строк

Как добавлять / удалять строки в Excel?

Чтобы добавить новые строки, щелкните правой кнопкой мыши любую ячейку, в которую вы хотите вставить строку, и выберите параметр «Вставить».Это откроет диалоговое окно, которое вы можете использовать для вставки новых строк.

Аналогичным образом, чтобы удалить ячейки / строки / столбцы, выберите их, щелкните правой кнопкой мыши и выберите «Удалить».

Как увеличить или уменьшить масштаб в Excel?

В строке состояния Excel есть кнопки «Увеличить» и «Уменьшить». Вы можете щелкнуть знак плюса, чтобы увеличить масштаб, и знак минуса, чтобы уменьшить.

Вы также можете удерживать клавишу Control, а затем использовать колесо прокрутки мыши для увеличения и уменьшения масштаба.

Кроме того, на вкладке «Просмотр» есть возможность увеличивать и уменьшать масштаб в Excel.

Как защитить лист в Excel?

Вы можете защитить лист, щелкнув вкладку «Рецензирование» и выбрав параметр «Защитить лист».

Откроется диалоговое окно, в котором вы можете установить пароль. Если вы не хотите устанавливать пароль, оставьте поле пустым.

Что такое именованные диапазоны? В чем его преимущества?

Именованный диапазон — это функция в Excel, которая позволяет дать ячейке или диапазону ячеек имя.Теперь вы можете использовать это имя вместо ссылок на ячейки.

Использование именованного диапазона упрощает работу с формулами. Это становится особенно полезным, когда вам нужно создавать формулы, использующие данные с нескольких листов. В таких случаях вы можете создать именованные диапазоны, а затем использовать их вместо ссылок на ячейки.

Этим именованным диапазонам можно давать описательные имена, что также упрощает чтение и понимание формулы. Например, вы можете использовать = СУММ (ПРОДАЖИ) вместо = СУММ (C2: C11), что мгновенно сообщит вам, о чем формула.

Подробнее об именованных диапазонах можно прочитать здесь.

Можно ли сделать так, чтобы строки / столбцы заголовка оставались видимыми при прокрутке?

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

Используя параметр «Закрепить области» в Excel, вы можете сделать строки / столбцы заголовков видимыми при прокрутке к удаленным точкам данных.

Параметры закрепления панелей доступны на вкладке «Вид» на ленте Excel.

Подробнее о закрепленных областях Excel можно узнать здесь.

Как бы вы идентифицировали ячейки, в которых есть комментарии?

Ячейки, к которым добавлены комментарии, помечаются маленьким красным треугольником в правом верхнем углу ячейки. Когда вы наводите курсор на ячейку, комментарии становятся видимыми.

Как бы вы сохранили рабочий лист Excel в формате PDF?

Чтобы сохранить рабочий лист как документ PDF, вы можете указать тип файла как PDF при сохранении файла.

Для этого перейдите на вкладку «Файл» и затем нажмите «Сохранить как».

В диалоговом окне «Сохранить как» выберите место, где вы хотите сохранить файл, и используйте раскрывающийся список «Тип файла», чтобы выбрать PDF. Это сохранит весь рабочий лист как документ PDF.

Как создать гиперссылку в Excel?

Чтобы создать гиперссылку, выберите ячейку, в которой вы хотите разместить гиперссылку, и используйте сочетание клавиш Control K (удерживая клавишу Ctrl, нажмите клавишу K).

Откроется диалоговое окно «Вставить гиперссылку», в котором можно указать URL-адрес.

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

Когда вы могли бы подумать о переходе с автоматического на ручной расчет в Excel?

Хотя в большинстве случаев лучше всего подходит автоматический режим расчета, в случае, если у вас есть тяжелый файл формул, в котором пересчет занимает много времени каждый раз, когда вы меняете что-либо на листе, вы можете переключиться на ручной расчет.

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

Что такое Flash Fill?

Flash Fill — замечательный инструмент, который был добавлен в Excel 2013 и доступен во всех последующих версиях.

Вы можете использовать Flash Fill в Excel, чтобы упростить ввод данных. Это умный инструмент, который пытается определить закономерности на основе введенных вами данных и делает это за вас.

Простыми примерами использования Flash Fill могут быть получение имени из полного имени, получение инициалов имени, форматирование телефонных номеров и т. Д.

Подробнее о Flash Fill можно узнать здесь.

Что такое ручка заполнения?

Маркер заполнения — это инструмент, который можно использовать для автозаполнения списков в Excel. Например, если вам нужно ввести числа от 1 до 20 в ячейку A1: A20, вместо того, чтобы вводить каждое число вручную, вы можете просто ввести первые два числа и использовать дескриптор заполнения для остального.

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

Когда вы наводите курсор на маркер заливки, курсор меняется на значок плюса.Теперь вы можете удерживать левую клавишу мыши и перетаскивать ее, чтобы добавить больше чисел в ряд.

Что такое подстановочные знаки в Excel?

В Excel есть три подстановочных символа:

• * (звездочка) — представляет любое количество символов. Например, Ex * может означать Excel, Excel, Пример, Эксперт и т. Д.
• ? (вопросительный знак) — представляет собой один единственный символ. Например, Gl? Ss может быть Glass или Gloss.
• ~ (тильда) — используется для обозначения подстановочного знака (~, *,?) В тексте.Например, допустим, вы хотите найти точную фразу Excel * в списке. Если вы используете Excel * в качестве строки поиска, это даст вам любое слово, в начале которого есть Excel, за которым следует любое количество символов (например, Excel, Excels, Excellent). Чтобы специально искать excel *, нам нужно использовать ~. Таким образом, наша строка поиска будет выглядеть следующим образом: excel ~ *. Здесь наличие ~ гарантирует, что excel прочитает следующий символ как есть, а не как подстановочный знак.

Подстановочные знаки полезны, когда вы хотите использовать их в формулах или при фильтрации данных.

Подробнее о подстановочных знаках можно узнать здесь.

Что такое область печати и как ее настроить в Excel?

Область печати — это диапазон ячеек (смежных или несмежных), которые вы назначаете для печати при печати этого рабочего листа. Например, вместо печати всего рабочего листа, если я хочу напечатать только первые 10 строк, я могу установить первые 10 строк в качестве области печати.

Чтобы установить область печати в Excel:

• Выберите ячейки, для которых вы хотите установить область печати.
• Щелкните вкладку «Макет страницы».
• Щелкните Область печати.
• Щелкните Установить область печати.

Подробнее о Print Area можно узнать здесь.

Как вставить номера страниц в Excel?

Вы можете вставить номера страниц с помощью диалогового окна Параметры страницы.

Вот шаги, чтобы сделать это:

• Щелкните вкладку «Макет страницы»,
• В категории «Параметры страницы» щелкните значок средства запуска диалогового окна (который представляет собой небольшую наклонную стрелку в правом нижнем углу группы) .
• В диалоговом окне «Параметры страницы» щелкните вкладку «Верхний / нижний колонтитул».
• Выберите формат номера страницы в раскрывающемся списке Нижний колонтитул.

Подробнее о номерах страниц в Excel можно узнать здесь.

Хотя я старался, чтобы это руководство «Вопросы и ответы на собеседовании в Excel» не содержало ошибок, в случае, если вы обнаружите какие-либо ошибки в любом из вопросов, сообщите мне об этом в области комментариев.

Связанные : Бесплатное онлайн-обучение по Excel (видеокурс из 7 частей)

Вам также могут понравиться следующие учебные пособия по Excel:

Комбинация функций ИНДЕКС и ПОИСКПОЗ в Excel (10 простых примеров)

В Excel много функций — около 450+ из них.

И многие из них просто классные. Объем работы, которую вы можете выполнить с помощью нескольких формул, все еще удивляет меня (даже после того, как вы использовали Excel более 10 лет).

Среди всех этих удивительных функций выделяется комбинация функций INDEX MATCH.

Я большой поклонник комбинации ИНДЕКС МАТЧ и много раз разъяснял ее.

Я даже написал статью об индексном сопоставлении и ВПР, которая вызвала небольшую дискуссию (вы можете проверить раздел комментариев, чтобы увидеть фейерверк).

И сегодня я пишу эту статью, посвященную исключительно Index Match, чтобы показать вам несколько простых и сложных сценариев, в которых вы можете использовать эту мощную комбинацию формул и выполнить свою работу.

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

ИНДЕКС Функция: поиск значения на основе координат

Самый простой способ понять, как работает функция индекса, — это представить ее как спутник GPS.

Как только вы сообщите спутнику координаты широты и долготы, он будет точно знать, куда идти и найти это место.

Итак, несмотря на ошеломляющее количество комбинаций широты и долготы, спутник точно знает, где искать.

Я быстро нашел место работы, и вот что я нашел.

Все равно географии хватит.

Точно так же, как спутнику нужны координаты широты и долготы, функции ИНДЕКС в Excel потребуется номер строки и столбца, чтобы знать, о какой ячейке вы говорите.

И это функция ИНДЕКС Excel в кратком изложении.

Итак, позвольте мне дать вам определение простыми словами.

Функция ИНДЕКС будет использовать номер строки и номер столбца, чтобы найти ячейку в заданном диапазоне и вернуть значение в ней.

Сам по себе ИНДЕКС — очень простая функция, не имеющая никакой полезности. В конце концов, в большинстве случаев вы вряд ли знаете номера строк и столбцов.

Но…

Тот факт, что вы можете использовать его с другими функциями (подсказка: ПОИСКПОЗ), которые могут найти номер строки и номер столбца, делает ИНДЕКС чрезвычайно мощной функцией Excel.

Ниже приведен синтаксис функции ИНДЕКС:

 = ИНДЕКС (массив, номер_строки, [номер_столбца])
= ИНДЕКС (массив; номер_строки; [номер_столбца]; [номер_площади]) 

• массив — диапазон ячеек или константа массива.
• row_num — номер строки, из которой должно быть извлечено значение.
• [col_num] — номер столбца, из которого должно быть извлечено значение. Хотя это необязательный аргумент, но если row_num не указан, его необходимо указать.
• [area_num] — (Необязательно) Если аргумент массива состоит из нескольких диапазонов, это число будет использоваться для выбора ссылки из всех диапазонов.

Функция ИНДЕКС имеет 2 синтаксиса (только FYI).

В большинстве случаев используется первый. Второй используется только в сложных случаях (например, при трехстороннем поиске), который мы рассмотрим в одном из примеров далее в этом руководстве.

Но если вы новичок в этой функции, просто запомните первый синтаксис.

Ниже приведено видео, в котором объясняется, как использовать функцию ИНДЕКС.

Функция ПОИСКПОЗ: Находит положение, основанное на значении поиска

Возвращаясь к моему предыдущему примеру долготы и широты, ПОИСКПОЗ — это функция, которая может найти эти позиции (в мире электронных таблиц Excel).

Простым языком функция ПОИСКПОЗ в Excel может найти положение ячейки в диапазоне.

А по какому принципу он определит положение ячейки?

На основе поискового значения.

Например, если у вас есть список, показанный ниже, и вы хотите найти в нем положение имени «Отметить», то вы можете использовать функцию ПОИСКПОЗ.

Функция возвращает 3, так как это позиция ячейки с именем Mark в ней.

Функция ПОИСКПОЗ начинает поиск сверху вниз для поискового значения (которым является «Отметка») в указанном диапазоне (в данном примере это A1: A9). Как только он находит имя, он возвращает позицию в этом конкретном диапазоне.

Ниже приведен синтаксис функции ПОИСКПОЗ в Excel.

 = MATCH (lookup_value, lookup_array, [match_type]) 

• lookup_value — Значение, для которого вы ищете совпадение в lookup_array.
• lookup_array — Диапазон ячеек, в котором выполняется поиск lookup_value.
• [match_type] — (Необязательно) Указывает, как Excel должен искать совпадающее значение. Может принимать три значения: -1, 0 или 1.

Объяснение аргумента типа соответствия в функции MATCH

Есть еще одна вещь, которую вам нужно знать о функции MATCH, и это о том, как она просматривает данные и находит позицию ячейки.

Третий аргумент функции ПОИСКПОЗ может быть 0, 1 или -1.

Ниже приводится объяснение того, как работают эти аргументы:

• 0 — это будет искать точное совпадение значения. Если найдено точное совпадение, функция ПОИСКПОЗ вернет позицию ячейки.В противном случае он вернет ошибку.
• 1 — находит наибольшее значение, которое меньше или равно искомому значению. Чтобы это работало, диапазон данных необходимо отсортировать в порядке возрастания.
• -1 — находит наименьшее значение, которое больше или равно искомому значению. Чтобы это работало, диапазон данных необходимо отсортировать в порядке убывания.

Ниже приведено видео, в котором объясняется, как использовать функцию ПОИСКПОЗ (вместе с аргументом типа соответствия)

Чтобы подвести итог и выразить это простыми словами:

• INDEX требует положения ячейки (строка и столбец число) и дает значение ячейки.
• MATCH находит позицию, используя значение поиска.

Давайте объединим их, чтобы создать мощный центр (ИНДЕКС + ПОИСКПОЗ)

Теперь, когда у вас есть базовое понимание того, как функции ИНДЕКС и ПОИСКПОЗ работают по отдельности, давайте объединим эти две функции и узнаем обо всех чудесных вещах, которые они могут делать.

Чтобы лучше понять это, у меня есть несколько примеров, в которых используется комбинация INDEX MATCH.

Я начну с простого примера, а затем покажу вам несколько сложных вариантов использования.

Щелкните здесь, чтобы загрузить файл примера

Пример 1: простой поиск с использованием комбинации INDEX MATCH

Давайте сделаем простой поиск с помощью INDEX / MATCH.

Ниже приведена таблица, в которой у меня есть оценки для десяти студентов.

Из этой таблицы я хочу найти оценки для Джима.

Ниже приведена формула, которая может легко сделать это:

 = ИНДЕКС ($ A $ 2: $ B $ 11, MATCH ("Джим", $ A $ 2: $ A $ 11,0), 2) 

Теперь, Если вы думаете, что это легко сделать с помощью функции ВПР, вы правы! Это не лучшее использование потрясающих возможностей INDEX MATCH.Несмотря на то, что я фанат INDEX MATCH, это немного сложнее, чем VLOOKUP. Если все, что вам нужно сделать, это получить данные из столбца справа, я рекомендую вам использовать ВПР.

Причина, по которой я показал этот пример, который также легко можно сделать с помощью ВПР, — показать вам, как работает INDEX MATCH в простой настройке.

Теперь позвольте мне показать преимущество INDEX MATCH.

Предположим, у вас есть те же данные, но они хранятся не в столбцах, а в строках (как показано ниже).

Знаете что, вы все равно можете использовать комбинацию INDEX MATCH, чтобы получать оценки Джима.

Ниже приведена формула, которая даст вам результат:

= ИНДЕКС ($ B $ 1: $ 2,2 K $, MATCH («Джим», $ B $ 1: $ 1,0 K $))

Примечание. что вам нужно изменить диапазон и переключить части строки / столбца, чтобы эта формула работала и для горизонтальных данных.

Это невозможно сделать с помощью ВПР, но вы все равно можете легко сделать это с помощью ГПР.

Комбинация INDEX MATCH может легко обрабатывать как горизонтальные, так и вертикальные данные.

Щелкните здесь, чтобы загрузить файл примера

Пример 2: Поиск слева

Это встречается чаще, чем вы думаете.

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

Пример, показанный ниже:

Чтобы узнать о продажах Майкла, вам нужно будет выполнить поиск слева.

Если вы думаете о ВПР, позвольте мне остановиться прямо здесь.

ВПР не предназначена для поиска и получения значений слева.

Можно еще сделать это с помощью ВПР?

Да, можно!

Но это может превратиться в длинную и уродливую формулу.

Итак, если вы хотите выполнить поиск и получить данные из столбцов слева, вам лучше использовать комбинацию INDEX MATCH.

Ниже приведена формула, по которой будет получен номер продажи Майкла:

 = ИНДЕКС (2 доллара США: 11 долларов США, ПОИСКПОЗ («Майкл», C2: C11,0), 2) 

Еще один пункт для ИНДЕКС МАТЧ.Функция VLOOKUP может извлекать данные только из столбцов, которые находятся справа от столбца, имеющего значение подстановки.

Пример 3: двусторонний поиск

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

Но в реальной жизни данные часто занимают несколько столбцов.

INDEX MATCH может легко обрабатывать двусторонний поиск.

Ниже представлен набор данных об оценках учащихся по трем различным предметам.

Если вы хотите быстро получить оценки учащегося по всем трем предметам, вы можете сделать это с помощью INDEX MATCH.

Приведенная ниже формула даст вам оценки Джиму для всех трех предметов (скопируйте и вставьте в одну ячейку и перетащите, чтобы заполнить другие ячейки, или скопируйте и вставьте в другие ячейки).

 = ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0)) 

Позвольте мне также быстро пояснить эту формулу.

В формуле ИНДЕКС в качестве диапазона используется B2: D11.

Первое ПОИСКПОЗ использует имя (Джим в ячейке F3) и выбирает его позицию в столбце имен (A2: A11). Он становится номером строки, из которой необходимо извлечь данные.

Вторая формула ПОИСКПОЗ использует имя субъекта (в ячейке G2), чтобы получить позицию этого конкретного имени субъекта в B1: D1. Например, математика — 1, физика — 2, а химия — 3.

Поскольку эти позиции ПОИСКПОЗ передаются в функцию ИНДЕКС, она возвращает оценку на основе имени ученика и имени предмета.

Эта формула является динамической, что означает, что если вы измените имя учащегося или имена субъектов, она все равно будет работать и получать правильные данные.

В использовании ИНДЕКС / ПОИСКПОЗ замечательно то, что даже если вы поменяете имена субъектов местами, это будет по-прежнему давать правильный результат.

Пример 4: Значение подстановки из нескольких столбцов / критериев

Предположим, у вас есть набор данных, как показано ниже, и вы хотите получить метки для «Mark Long».

Поскольку данные находятся в двух столбцах, я не могу найти Марка и получить данные.

Если я сделаю это таким образом, я собираюсь получить данные оценок для Марка Фроста, а не для Марка Лонга (потому что функция ПОИСКПОЗ даст мне результат для МЕТКИ, которой она соответствует).

Один из способов сделать это — создать вспомогательный столбец и объединить имена. Когда у вас есть вспомогательный столбец, вы можете использовать ВПР и получать данные о отметках.

Если вам интересно узнать, как это сделать, прочтите это руководство по использованию ВПР с несколькими критериями.

Но с комбинацией ИНДЕКС / ПОИСКПОЗ вам не нужен вспомогательный столбец.Вы можете создать формулу, которая обрабатывает несколько критериев в самой формуле.

Следующая формула даст результат.

 = ИНДЕКС ($ C $ 2: $ C $ 11, ПОИСКПОЗ ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0)) 

Позвольте мне быстро объяснить, что делает эта формула.

Часть формулы MATCH объединяет поисковое значение (Mark и Long), а также весь поисковый массив. Когда $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 используется в качестве массива поиска, он фактически проверяет значение поиска по объединенной строке имени и фамилии (разделенной вертикальной чертой).

Это гарантирует, что вы получите правильный результат без использования каких-либо вспомогательных столбцов.

Этот вид поиска (при наличии нескольких столбцов / критериев) можно также выполнять с помощью ВПР, но вам необходимо использовать вспомогательный столбец. Комбинация INDEX MATCH позволяет сделать это немного проще без каких-либо вспомогательных столбцов.

Пример 5: Получить значения из всей строки / столбца

В приведенных выше примерах мы использовали функцию ИНДЕКС для получения значения из определенной ячейки. Вы указываете номер строки и столбца, и он возвращает значение в этой конкретной ячейке.

Но вы можете сделать больше.

Вы также можете использовать функцию ИНДЕКС, чтобы получить значения из всей строки или столбца.

А чем это может пригодиться спросите вы!

Предположим, вы хотите узнать общий балл Джима по всем трем предметам.

Вы можете использовать функцию ИНДЕКС, чтобы сначала получить все оценки Джима, а затем использовать функцию СУММ, чтобы получить итог.

Давайте посмотрим, как это сделать.

Ниже приведены оценки всех студентов по трем предметам.

Приведенная ниже формула даст мне общий балл Джима по всем трем предметам.

 = СУММ (ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)) 

Позвольте мне объяснить, как работает эта формула.

Уловка здесь заключается в том, чтобы использовать 0 в качестве номера столбца.

Когда вы используете 0 в качестве номера столбца в функции ИНДЕКС, он вернет все значения строки. Точно так же, если вы используете 0 в качестве номера строки, он вернет все значения в столбце.

Итак, приведенная ниже часть формулы возвращает массив значений — {97, 70, 73}

 ИНДЕКС ($ B $ 2: $ D $ 11, ПОИСКПОЗ ($ F $ 4, $ A $ 2: $ A $ 11,0) , 0) 

Если вы просто введете эту формулу выше в ячейку Excel и нажмете клавишу ВВОД, вы увидите # ЗНАЧ! ошибка. Это потому, что он возвращает не одно значение, а массив значений.

Но не волнуйтесь, массив значений все еще там. Вы можете проверить это, выбрав формулу и нажав клавишу F9. Он покажет вам результат формулы, которая в данном случае является массивом из трех значений — {97, 70, 73}

Теперь, если вы заключите эту формулу ИНДЕКС в функцию СУММ, она даст вам сумму всех оценки, поставленные Джимом.

Вы также можете использовать то же самое, чтобы получить высшую, низшую и среднюю оценки Джима.

Так же, как мы сделали это для студента, вы также можете сделать это для предмета. Например, если вы хотите получить средний балл по предмету, вы можете оставить номер строки равным 0 в формуле ИНДЕКС, и он предоставит вам все значения столбцов этого предмета.

Щелкните здесь, чтобы загрузить файл примера.

Пример 6. Определение оценки учащегося (метод приблизительного сопоставления)

До сих пор мы использовали формулу MATCH для получения точного соответствия искомому значению.

Но вы также можете использовать его для приблизительного совпадения.

Что, черт возьми, такое приблизительное совпадение?

Позвольте мне объяснить.

Когда вы ищете такие вещи, как имена или идентификаторы, вы ищете точное совпадение. Но иногда вам нужно знать диапазон, в котором лежат ваши значения поиска. Обычно так бывает с числами.

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

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

Таким образом, если ученик получает меньше 33 баллов, он получает оценку F, а если он / она получает меньше 50, но больше 33, это E и так далее.

Ниже приведена формула, которая сделает это.

 = ИНДЕКС ($ F $ 3: $ F $ 8, ПОИСКПОЗ (B2, $ E $ 3: $ E $ 8,1), 1) 

Позвольте мне объяснить, как работает эта формула.

В функции ПОИСКПОЗ мы использовали 1 в качестве аргумента [match_type].Этот аргумент вернет наибольшее значение, которое меньше или равно искомому значению.

Это означает, что формула MATCH проходит по диапазону меток, и как только она находит диапазон меток, который равен или меньше значения меток поиска, она останавливается на этом и возвращает свою позицию.

Итак, если значение поисковой метки равно 20, функция ПОИСКПОЗ вернет 1, а если оно равно 85, она вернет 5.

И функция ИНДЕКС использует это значение позиции для получения оценки.

ВАЖНО: Чтобы это работало, ваши данные должны быть отсортированы в порядке возрастания. В противном случае вы можете получить неверные результаты.

Обратите внимание, что указанное выше также можно сделать с помощью приведенной ниже формулы ВПР:

 = ВПР (B2, $ E $ 3: $ F $ 8,2, ИСТИНА) 

Но функция ПОИСКПОЗ может пойти дальше, когда дело доходит до приблизительного совпадения. .

Вы также можете иметь данные в порядке убывания и можете использовать комбинацию ИНДЕКС ПОИСКПОЗ, чтобы найти результат. Например, если я изменю порядок в таблице оценок (как показано ниже), я все равно смогу найти оценки учащихся.

Для этого все, что мне нужно сделать, это изменить аргумент [match_type] на -1.

Ниже приведена формула, которую я использовал:

 = ИНДЕКС ($ F $ 3: $ F $ 8, ПОИСКПОЗ (B2, $ E $ 3: $ E $ 8, -1), 1) 

ВПР также может выполнять приблизительную совпадают, но только когда данные отсортированы в порядке возрастания (но не работает, если данные отсортированы в порядке убывания).

Пример 7: Поиск с учетом регистра

До сих пор все поиски, которые мы выполняли, были без учета регистра.

Это означает, что было ли искомое значение Jim, JIM или jim, не имело значения. Вы получите тот же результат.

Но что, если вы хотите, чтобы поиск был чувствительным к регистру.

Обычно это имеет место, когда у вас есть большие наборы данных и возможность повторения или различных имен / идентификаторов (с той лишь разницей, что это так)

Например, предположим, что у меня есть следующий набор данных студентов, где есть два студенты с именем Джим (с той лишь разницей, что один указан как Джим, а другой — как Джим).

Обратите внимание, что есть два студента с одинаковым именем — Джим (ячейка A2 и A5).

Поскольку обычный поиск не работает, вам нужно выполнять поиск с учетом регистра.

Ниже приведена формула, которая даст вам правильный результат. Поскольку это формула массива, вам нужно использовать Control + Shift + Enter.

= ИНДЕКС ($ B $ 2: $ B $ 11, ПОИСКПОЗ (ИСТИНА, ТОЧНО (D3, A2: A11), 0), 1)

Позвольте мне объяснить, как работает эта формула.

Функция EXACT проверяет точное совпадение искомого значения (в данном случае это «jim»).Он перебирает все имена и возвращает ЛОЖЬ, если совпадение не совпадает, и ИСТИНА, если совпадение.

Таким образом, на выходе функции EXACT в этом примере будет — {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Обратите внимание, что есть только одно TRUE, то есть когда EXACT функция нашла идеальное соответствие.

Функция ПОИСКПОЗ затем находит положение ИСТИНА в массиве, возвращаемом функцией ТОЧНО, которое в этом примере равно 4.

Когда у нас есть позиция, функция ИНДЕКС использует ее для поиска меток.

Пример 8: Найдите наиболее близкое совпадение

Давайте немного продвинемся вперед.

Предположим, у вас есть набор данных, в котором вы хотите найти человека, у которого опыт работы наиболее близок к требуемому (указан в ячейке D2).

Хотя формулы поиска не предназначены для этого, вы можете комбинировать их с другими функциями (такими как MIN и ABS), чтобы сделать это.

Ниже приведена формула, которая найдет человека с опытом, наиболее близким к требуемому, и вернет имя человека.Обратите внимание, что опыт должен быть самым близким (который может быть как меньше, так и больше).

 = ИНДЕКС ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0)) 

Так как это массив формулу, вам нужно использовать Control + Shift + Enter.

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

Прежде чем я объясню формулу, давайте разберемся, как это сделать вручную.

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

Именно это мы и делаем с этой формулой.

Позвольте мне объяснить.

Подстановочное значение в формуле MATCH — MIN (ABS (D2-B2: B15)).

Эта часть дает вам минимальную разницу между заданным опытом (то есть 2.5 лет) и все остальные опыты. В этом примере он возвращает 0,3

Обратите внимание, что я использовал ABS, чтобы убедиться, что ищу ближайший (который может быть больше или меньше, чем данный опыт).

Теперь это минимальное значение становится нашим значением поиска.

Подстановочный массив в функции ПОИСКПОЗ — это ABS (D2- $ B $ 2: $ B $ 15).

Это дает нам массив чисел, из которого вычтено 2,5 (необходимый опыт).

Итак, теперь у нас есть искомое значение (0.3) и поисковый массив ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

Функция ПОИСКПОЗ находит позицию 0,3 в этом массиве, что также является положением имени человека, имеющего самый близкий опыт.

Этот номер позиции затем используется функцией ИНДЕКС для возврата имени человека.

  Связанное чтение : Найдите наиболее близкое соответствие в Excel (примеры с использованием формул поиска) 

Щелкните здесь, чтобы загрузить файл примера

Пример 9: Использовать ИНДЕКСНОЕ СОГЛАСОВАНИЕ с подстановочными знаками

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

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

Поскольку это не точные совпадения, в этом случае нельзя выполнять обычный поиск.

Но вы все равно можете получить правильные данные, используя звездочку (*), которая является подстановочным знаком.

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

= ИНДЕКС ($ B $ 2: $ B $ 10, ПОИСКПОЗ (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Позвольте мне объяснить, как работает эта формула.

Поскольку нет точного совпадения значений поиска, я использовал D2 и ”*” в качестве значения поиска в функции ПОИСКПОЗ.

Звездочка — это подстановочный знак, который представляет любое количество символов. Это означает, что Apple * в формуле будет означать любую текстовую строку, которая начинается со слова Apple и может иметь любое количество символов после него.

Таким образом, когда Apple * используется в качестве значения поиска и формула MATCH ищет его в столбце A, она возвращает позицию «Apple Inc.», поскольку она начинается со слова Apple.

Вы также можете использовать подстановочные знаки для поиска текстовых строк, между которыми находится значение поиска. Например, если вы используете * Apple * в качестве значения поиска, он найдет любую строку, в которой есть слово «яблоко».

Примечание. Этот метод хорошо работает, когда у вас есть только один экземпляр соответствия.Но если у вас есть несколько экземпляров сопоставления (например, Apple Inc и Apple Corporation, то функция ПОИСКПОЗ вернет только позицию первого совпадающего экземпляра.

Пример 10: Трехсторонний поиск

Это расширенное использование ИНДЕКС-ПОИСКПОЗ , но я все же расскажу об этом, чтобы показать вам силу этой комбинации.

Помните, я сказал, что функция ИНДЕКС имеет два синтаксиса:

 = ИНДЕКС (array, row_num, [col_num])
= ИНДЕКС (array, row_num, [col_num], [area_num]) 

Пока что во всех наших примерах мы использовали только первый.

Но для трехстороннего поиска необходимо использовать второй синтаксис.

Позвольте мне сначала объяснить, что означает трехсторонний вид.

При двустороннем поиске мы используем формулу ИНДЕКС ПОИСКПОЗ, чтобы получить оценки, когда у нас есть имя учащегося и имя предмета. Например, получение оценок Джима в математике — это двусторонний поиск.

Трехсторонний вид добавит ему еще одного измерения. Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите узнать оценку Джима по математике на промежуточном экзамене, тогда это будет трехсторонний поиск.

Ниже приведена формула, по которой будет получен результат.

 = ИНДЕКС (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH ( G $ 4, $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3)))) 

Вышеуказанное формула проверяет три вещи — имя студента, предмет и экзамен. Найдя нужное значение, он возвращает его в ячейку.

Позвольте мне объяснить, как работает эта формула, разбив ее на части.

• массив — ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23) : вместо использования одного массива в этом случае я использовал три массива внутри скобка.
• row_num — ПОИСКПОЗ ($ F $ 5, $ A $ 3: $ A $ 7,0) : функция ПОИСКПОЗ используется для поиска позиции имени учащегося в ячейке $ F $ 5 в списке имен учащихся.
• col_num — ПОИСКПОЗ (G $ 4, $ B $ 2: $ D $ 2,0) : Функция ПОИСКПОЗ используется для поиска позиции имени субъекта в ячейке $ B $ 2 в списке имен субъектов.
• [area_num] — IF (G $ 3 = «Unit Test», 1, IF (G $ 3 = «Mid Term», 2,3)) : значение номера области сообщает функции ИНДЕКС, какой из трех массивов следует используйте для получения значения. Если экзамен представляет собой единичный термин, функция ЕСЛИ вернет 1, а функция ИНДЕКС будет использовать первый массив для получения значения. Если экзамен среднесрочный, формула ЕСЛИ вернет 2, иначе она вернет 3.

Это продвинутый пример использования ПОИСКПОЗ ПО ИНДЕКСУ, и вы вряд ли найдете ситуацию, когда вам придется его использовать.Но все же полезно знать, на что способны формулы Excel.

Щелкните здесь, чтобы загрузить файл примера

Почему ИНДЕКС / ПОИСКПОЗ лучше, чем ВПР?

ИЛИ Это?

Да, в большинстве случаев.

Я изложу свой случай через некоторое время.

Но прежде чем я это сделаю, позвольте мне сказать вот что: ВПР — чрезвычайно полезная функция, и мне она нравится. Он может многое делать в Excel, и я сам время от времени им пользуюсь. Сказав это, это не означает, что не может быть ничего лучше, а INDEX / MATCH (с большей гибкостью и функциональностью) лучше.

Итак, если вы хотите выполнить базовый поиск, вам лучше использовать ВПР.

ИНДЕКС / МАТЧ — это ВПР на стероидах. И как только вы изучите INDEX / MATCH, вы всегда можете предпочесть его использование (особенно из-за его гибкости).

Не вдаваясь в подробности, позвольте мне быстро объяснить, почему INDEX / MATCH лучше, чем VLOOKUP.

INDEX / MATCH может смотреть влево (а также вправо) от поискового значения

Я рассмотрел его в одном из приведенных выше примеров.

Если у вас есть значение, которое находится слева от значения поиска, вы не можете сделать это с помощью ВПР.

По крайней мере, не с помощью только ВПР.

Да, вы можете комбинировать ВПР с другими формулами и делать это, но это становится сложным и беспорядочным.

INDEX / MATCH, с другой стороны, предназначен для поиска везде (будь то влево, вправо, вверх или вниз)

INDEX / MATCH может работать с вертикальными и горизонтальными диапазонами

Опять же, с полным уважением к VLOOKUP, это не предназначено для этого.

В конце концов, V в VLOOKUP означает вертикальное.

VLOOKUP может просматривать данные только по вертикали, в то время как INDEX / MATCH может просматривать данные как по вертикали, так и по горизонтали.

Конечно, есть функция ГПР для горизонтального просмотра, но тогда это не ВПР .. верно?

Мне нравится, что комбо INDEX MATCH достаточно гибкое, чтобы работать как с вертикальными, так и с горизонтальными данными.

ВПР не может работать с данными по убыванию

Когда дело доходит до приблизительного совпадения, ВПР и ИНДЕКС / ПОИСКПОЗ находятся на одном уровне.

Но INDEX MATCH имеет значение, поскольку он также может обрабатывать данные в порядке убывания.

Я показываю это в одном из примеров в этом руководстве, где нам нужно найти оценку учащихся на основе таблицы оценок. Если таблица отсортирована в убывающем порядке, функция ВПР не будет работать (но ПОИСКПОЗ будет).

INDEX / MATCH может быть немного быстрее

Буду честен. Я сам не проводил этот тест.

Я полагаюсь на мудрость мастера Excel — Чарли Кида.

Разница в скорости в режимах VLOOKUP и INDEX / MATCH едва заметна при небольших наборах данных. Но если у вас тысячи строк и много столбцов, это может стать решающим фактором.

В своей статье Чарли Кид утверждает:

«В худшем случае метод INDEX-MATCH работает примерно так же быстро, как VLOOKUP; в лучшем случае это намного быстрее «.

INDEX / MATCH не зависит от фактического положения столбца

Если у вас есть набор данных, показанный ниже, при получении оценки Джима по физике, вы можете сделать это с помощью ВПР.

И для этого вы можете указать номер столбца как 3 в ВПР.

Все нормально.

Но что, если я удалю столбец Math.

В этом случае формула ВПР нарушится.

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

В этом случае лучше использовать INDEX / MATCH, так как вы можете сделать номер столбца динамическим с помощью MATCH.Поэтому вместо номера столбца он проверяет имя субъекта и использует его для возврата номера столбца.

Конечно, вы можете сделать это, комбинируя VLOOKUP с MATCH, но если вы все равно комбинируете, почему бы не сделать это с INDEX, который намного более гибок.

При использовании INDEX / MATCH вы можете безопасно вставлять / удалять столбцы в наборе данных.

Несмотря на все эти факторы, есть причина, по которой функция ВПР так популярна.

И это серьезная причина.

ВПР проще в использовании

ВПР принимает не более четырех аргументов.Если вы можете осмыслить эти четыре, то все готово.

И поскольку большинство базовых вариантов поиска также обрабатываются с помощью ВПР, она быстро стала самой популярной функцией Excel.

Я называю это королем функций Excel.

INDEX / MATCH, с другой стороны, немного сложнее в использовании. У вас может возникнуть зависание, если вы начнете его использовать, но для новичка VLOOKUP гораздо проще объяснить и изучить.

И это не игра с нулевой суммой.

Итак, если вы новичок в мире поиска и не знаете, как использовать ВПР, лучше научитесь этому.

У меня есть подробное руководство по использованию ВПР в Excel (с множеством примеров)

В этой статье я не собираюсь противопоставлять две потрясающие функции друг другу. Я хотел показать вам мощь комбинации INDEX MATCH и все замечательные вещи, на которые она способна.

Надеюсь, эта статья оказалась для вас полезной.

Сообщите мне свои мысли в разделе комментариев, и если вы обнаружите какую-либо ошибку в этом руководстве, сообщите мне.

Вам также могут понравиться следующие учебные пособия по Excel:

Изучите Excel с помощью онлайн-курсов и занятий

Что такое Excel?

Microsoft Excel — это стандартная программа для работы с электронными таблицами, используемая для вычисления, анализа, визуализации данных и многого другого. Его можно запрограммировать для доступа к данным из внешних баз данных, и он является отличным инструментом для моделирования данных, статистики и финансовых расчетов со встроенными функциями, позволяющими быстро и легко выполнять стандартные задачи.Знание Excel обычно требуется для бухгалтерского учета, бизнес-аналитики, анализа данных и статистики.

Learn Excel

Microsoft предлагает бесплатные онлайн-классы Excel на edX, чтобы получить как фундаментальные, так и продвинутые навыки работы с Excel. Введение в анализ данных с использованием Excel — это 4-недельный онлайн-курс для самостоятельного изучения, который идеально подходит для учащихся, не имеющих или практически не имеющих опыта использования программного обеспечения. Узнайте, как настроить лист Excel, выполнять вычисления, использовать базовые и расширенные формулы Excel и создавать визуальные представления данных с помощью диаграмм и графиков.Узнайте, как быстро создавать различные представления данных с помощью сводных таблиц и многое другое, непосредственно от команды Excel в Microsoft.

Дополнительные учебные курсы по Excel изучают расширенные функции программного обеспечения для статистики и визуализации данных. Узнайте об описательной статистике, базовой вероятности, случайных величинах, Excel VBA, выборке и проверке гипотез. Выполняйте гибридные приложения и визуализации данных, используя данные, полученные из нескольких источников. Некоторые другие элементы, которые вы изучите в Microsoft Excel, включают условное форматирование, сводные таблицы, сочетания клавиш и многое другое.Курсы с расширенными функциями являются частью сертификата Microsoft Professional Program в области науки о данных — специализации, разработанной для того, чтобы учащиеся могли сделать карьеру в области науки о данных или смежных областях. Если вы хотели изучить Excel, вы попали в нужное место.

Учебные классы по Excel

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

Добавьте Excel в свое резюме

Чтобы дать вам представление об огромной популярности Excel среди работодателей, недавно поиск по ключевым словам «Excel» на Indeed.com дал почти 600 000 результатов. Навыки Excel востребованы почти во всех отраслях и постоянно занимают первые места в списке наиболее важных навыков, которые можно добавить в свое резюме. Работаете ли вы в сфере здравоохранения, информационных технологий, финансов, бизнес-аналитики или информатики, использование учебного пособия по Excel или короткого онлайн-курса по Excel для изучения новейших функций может повысить вашу производительность. Изучение Microsoft Excel может быть навыком, применимым к большинству бизнес-операций. edX также предлагает продвинутые учебные курсы по Excel, которые помогут продвинуть ваш набор навыков на новый уровень.

Learn Excel Online — Лучшее бесплатное руководство и ресурсы по Excel

Несмотря на то, что Excel известен обработкой чисел, его возможности выходят далеко за рамки этого. В Excel вы можете:

• Упорядочивайте данные с помощью удобной навигации
• Вычисление основных и сложных математических функций, не выполняя их вручную
• Превратите груды данных в полезные графики и диаграммы
• Анализировать данные и делать прогнозы
• Создавайте, создавайте и редактируйте рисунки в Excel (да, творческие люди тоже это используют!)

Excel также может помочь вам в личной жизни с помощью таблиц для отслеживания расходов, шаблонов планирования вечеринок и многого другого.

Заинтересованы, но не знаете, с чего начать?

Мы вас прикрыли.

Этот обзор познакомит вас с основами Excel и научит, как повысить производительность труда с помощью ключевых формул и функций, фильтрации, макросов и многого другого. Мы также добавили 50+ бесплатных ресурсов в конце, которые помогут вам изучить Excel онлайн!

Что такое Microsoft Excel?

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

Кто пользуется Excel?

Практически все, по-разному. Excel используется практически во всех отраслях, включая финансы, розничную торговлю, здравоохранение, связь, информационные технологии и многие другие. Excel также пригодится для личного использования, чтобы управлять своими финансами, организовывать поиск работы или даже планировать роман! Если вы новичок в Microsoft Excel, вот несколько ресурсов, которые могут вам пригодиться:

Изучение основ Excel

Прежде чем приступить к изучению таких интересных вещей, как макросы и Power Pivot, важно сначала сориентироваться.

Книга Microsoft Office Excel — это файл формата xlsx (если вы используете Excel 2007+), который содержит один или несколько листов , которые вы можете использовать для организации различных типов связанной информации, согласно Microsoft.

Рабочие листы состоят из почти бесконечного числа строк и столбцов , которые вместе создают ячеек .

Ячейки служат для хранения вашей информации, такой как текст, числа, изображения, даты, время и формулы.

Вы заметите, что строки пронумерованы, а столбцы помечены буквами. Пример метки ячейки (или ссылки): A2, H5 и т. Д.

Куда обращаться в Excel

Чтобы работать в Excel, вам нужно разбираться в ленте.

Что такое панель быстрого доступа?

Excel (см. Снимок экрана ниже) позволяет одним щелчком мыши получить доступ к инструментам, которые вы чаще всего используете в Excel.

Вы можете настроить то, что здесь отображается, щелкнув значок стрелки, крайний правее в левом углу.

Что такое лента?

Лента — это набор значков в верхней части книги, которые помогают выполнять определенные действия. Думайте о ленте как о иерархии, где каждая вкладка (например, «Главная», «Вставка», «Макет страницы») содержит группу команд (например, «Домашняя страница», «Вставка», «Макет страницы»).грамм. Копировать, Вставить, Сортировка, Фильтр).

Давайте углубимся в каждый из этих пунктов.

Что такое вкладки?

На ленте (см. Снимок экрана) вы найдете команды. Он разбит на интерактивные вкладки, где вы найдете группы связанных команд.

Важно отметить, что ваша лента Excel может отличаться от моей ленты Excel.Это могло быть по нескольким причинам.

1. Надстройки:
Надстройки расширяют возможности Microsoft Excel и, следовательно, требуют новой вкладки.

2. Контекстные вкладки:
Есть также контекстные вкладки — или вкладка, которая появляется автоматически, когда вы делаете что-то конкретное в Excel.

3. Настройка:
Чтобы Excel работал на вас, вы можете настроить ленту, добавляя и / или удаляя различные вкладки.

Что такое группы команд?

В зависимости от того, на какой вкладке вы щелкнете — главная, вставка, макет страницы и т. Д.- ниже появятся разные группы команд.

Что можно делать с Excel?

Excel — это мощная и надежная программа, которая позволяет выполнять множество вещей, таких как организация, анализ, представление и даже автоматизация ваших данных.

Использование электронных таблиц обычно состоит из трех этапов:

Сделайте что-нибудь с
данными

Иногда бывает четвертый этап:

Сделайте что-нибудь с данными

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

Как применить условное форматирование

Хотя форматирование важно для удобства чтения, это не единственное преимущество этой функции.

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

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

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

Если в Excel еще нет нужной вам опции, вы можете создать свой собственный стиль.

Важно отметить, что выбранный вами стиль будет применен к каждой выбранной ячейке, поэтому обратите внимание на ячейки, которые вы выбираете, потому что это может повлиять на внешний вид стиля.

Как использовать морозильные стекла

Мы действительно затронули это в статье.Вот выдержка:

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

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

Вот как это сделать:

• Щелкните строку под заголовками столбцов.
• Щелкните вкладку «Просмотр».
• Щелкните кнопку «Закрепить области».

Прокрутите таблицу вниз и вы увидите, что нужная информация всегда под рукой!

Что такое формулы и функции?

Одна из причин, по которой Excel так широко используется в деловом мире, заключается в том, что он позволяет выполнять расширенные вычисления и создавать на его основе решения для бизнес-аналитики.

Для этого вам необходимо понимать формулы и функции. Формулы — это самый простой способ делать математику в Excel.

Каждая формула начинается со знака «=».
Как вы начинаете каждую формулу

Если вы хотите сложить два числа, используйте знак «+».
Добавить числа

Если вы хотите вычесть два числа, используйте знак «-».
Вычесть числа

Если вы хотите умножить два числа, используйте знак «*».
Умножать числа

И если вы хотите разделить два числа, вы должны использовать знак «/».
Разделить числа

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

Вот что я должен был сделать вместо этого:

Обратите внимание, как я ввожу ссылки на ячейки вместо фактических чисел.

А что, если вы хотите заниматься более сложной математикой? Как бы ты это сделал?

Ну, вы бы использовали функции. Функции, которые классифицируются по функциональности, представляют собой просто предопределенные формулы.Вот некоторые из самых распространенных. Вы можете найти полный список здесь и здесь.

• Функция СУММ
  Добавляет значения. Вы можете добавить отдельные значения, ссылки на ячейки или диапазоны, либо их сочетание.
• Функция СРЕДНИЙ
  Возвращает среднее (среднее арифметическое) аргументов. Например, если диапазон A1: A20 содержит числа, формула = СРЕДНЕЕ (A1: A20) возвращает среднее значение этих чисел.
• Функция MAX
  Возвращает наибольшее значение в наборе значений.
• Функция MIN
  Возвращает наименьшее число в наборе значений.
• Функция СЧЕТ
  Подсчитывает количество ячеек, содержащих числа.

Интерпретируйте ваши данные

Теперь, когда у вас есть данные, вы должны их интерпретировать.

Дело в том, что трудно разобраться в беспорядочных данных. Так что сначала вам нужно визуализировать это.

Вот несколько способов улучшить внешний вид данных.

Как вы сортируете данные по столбцам?

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

Что ж, вы можете это сделать. Вот как.

Подготовьте данные для сортировки

Прежде чем вы сможете отсортировать данные, вы должны убедиться, что они готовы к сортировке.Ваши данные готовы к сортировке, если верно следующее:

• Ваши данные в табличном формате.
• Нет пустых строк или столбцов, прерывающих данные, которые вы хотите отсортировать.
• У вас есть отдельная строка заголовка для ваших данных. (Необязательно, но настоятельно рекомендуется.)

Как выполнять одноуровневую сортировку

• Выберите любую ячейку в таблице данных (или весь диапазон, который вы хотите отсортировать).
• Выберите «Данные»> «Сортировка».
• Настройте способ сортировки, затем нажмите «ОК».

Как выполнять многоуровневую сортировку

Многоуровневая сортировка хороша для больших наборов данных, потому что вы, вероятно, захотите отсортировать данные по одному столбцу, а затем по другому.

• Выберите любую ячейку в таблице данных (или весь диапазон, который вы хотите отсортировать),
• Выберите «Данные»> «Сортировка».
• Настройте первичную сортировку так, как вы хотите.
• Щелкните Добавить уровень.
• Настройте вторичную сортировку так, как вы хотите сортировать связи.
• Вы можете добавлять столько уровней сортировки, сколько вам нужно.

Как использовать фильтрацию

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

Чтобы отфильтровать дубликаты, опять же, вы должны подготовить свои данные для фильтрации — убедитесь, что:

• Ваши данные в табличном формате.
• Нет пустых строк или столбцов, прерывающих данные, которые вы хотите отсортировать.
• У вас есть отдельная строка заголовка для ваших данных. (Необязательно, но настоятельно рекомендуется.)
• Выберите верхнюю строку ваших данных. Перейдите на вкладку «Данные» и выберите «Фильтр» (это добавит ряд раскрывающихся стрелок к вашим заголовкам).

Фильтр по конкретным словам

• Щелкните стрелку раскрывающегося списка столбца, который нужно отфильтровать.
• В поле поиска введите искомый термин и нажмите кнопку ОК.

Фильтр по датам

• Щелкните стрелку раскрывающегося списка столбца, который нужно отфильтровать.
• Снимите флажок «Выбрать все», чтобы удалить ненужные даты, затем используйте флажки для перехода к нужным записям.

Фильтр нескольких столбцов

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

Очистить фильтры одним из трех способов

• Щелкните значки фильтра в заголовках столбцов, затем нажмите кнопку «Очистить фильтр из» в меню.
• Перейдите на вкладку «Данные» и щелкните значок «Очистить» в группе «Сортировка и фильтр».
• Перейдите на вкладку «Данные» и отключите фильтрацию, нажав кнопку «Фильтр», затем снова добавьте фильтр.

Что такое сводная таблица и как ее использовать?

Сводная таблица похожа на кубик Рубика, потому что позволяет формировать данные и рассматривать их по-разному.

Как создать сводную таблицу

• Выберите любую ячейку в диапазоне данных.
• Перейдите на вкладку «Вставка», выберите «Сводная таблица» и поместите ее на новый рабочий лист.
• Перетащите числовое поле в область ЗНАЧЕНИЯ.
• Перетащите поля, содержащие текст, в область СТРОК по желанию.

Как удалить элементы из сводной таблицы

• Перетащите любое поле из области макета обратно в список полей.

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

• Перетащите поле из СТРОКИ в КОЛОННЫ.
• Перетащите поле из КОЛОННЫ в СТРОКИ.
• Щелкните стрелку рядом с любым полем в области ЗНАЧЕНИЯ, выберите «Параметры поля значений» и выберите «Среднее».
• Вернитесь к исходным данным и обновите любую ячейку до нового значения.
• Вернитесь в сводную таблицу, щелкните ее правой кнопкой мыши и выберите «Обновить».

Дополнительные руководства по сводным таблицам см. В разделе ресурсов ниже.

Как автоматизировать данные в Excel?

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

Что такое макрос?

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

Как записывать макросы

Добавьте вкладку «Разработчик» на ленту (если ее нет на ленте)

• Щелкните правой кнопкой мыши любую команду на ленте и выберите «Настроить ленту».
• Установите флажок рядом с вкладкой «Разработчик» в списке справа.
• Нажмите «ОК», чтобы вернуться в Excel и активировать вкладку «Разработчик».

Подготовка к записи

• Практикуйтесь в шагах, которые вы будете выполнять, поскольку записывающее устройство макросов запишет ваши ошибки!
• Имейте в виду, что макрос будет делать ТОЧНО одно и то же при каждом запуске (это можно изменить, но это требует обучения программированию).

Записать макрос

• На вкладке разработчика щелкните «Записать макрос».”
• Дайте макросу имя (без пробелов) и при необходимости добавьте заглавную букву в поле «Ярлык» (например, R).
• Выберите, чтобы поместить макрос в ThisWorkbook.
• Выполните задачи, которые должен выполнять макрос.
• На вкладке «Разработчик» нажмите «Остановить запись».

Запустить макрос

Вы можете запустить макрос одним из трех способов:

• Нажмите CTRL + SHIFT + R (или любую другую выбранную букву), чтобы запустить сочетание клавиш.
• Нажмите Alt + F8 , чтобы запустить макрос из диалогового окна макроса.
• Перейдите на вкладку «Разработчик» и выберите «Вставить кнопку управления формой» на лист (вам будет предложено подключить к ней макрос). Затем вы можете нажать кнопку, чтобы запустить макрос.

Дополнительные руководства по макросам и VBA можно найти в разделе ресурсов ниже.

Более 50 бесплатных ресурсов для изучения Excel онлайн

Это был общий обзор, но возможности Excel безграничны, поэтому не останавливайтесь на этом.

Мы собрали более 50 бесплатных ресурсов Excel, которые помогут вам двигаться вперед, включая пошаговые руководства, сочетания клавиш и шаблоны для экономии времени.

Если вы ищете что-то более комплексное, мы также включили онлайн-курсы, которые помогут вам быстро изучить и применить свои новые навыки.

Шаблоны, ярлыки и надстройки

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

Уроки для начинающих

Воспользуйтесь этими руководствами по Excel для начинающих или тех, кто хочет освежить в памяти основы.

Видеоуроки

ресурса

Вебинар

Курсы

Учебные пособия для среднего и продвинутого уровней

Освоив основы, попробуйте свои силы в освоении этих промежуточных и продвинутых навыков.

Видеоуроки

ресурса

Курсы

Дополнительные ресурсы среднего и продвинутого уровней см. В разделах ниже, сгруппированных по конкретным категориям.

Учебные пособия по сводным таблицам

Узнайте, как использовать один из самых незаменимых инструментов Excel, сводную таблицу, для анализа и представления данных в понятной форме.

Видеоуроки

ресурса

Курсы

Учебные пособия по макросам и VBA

Узнайте, как автоматизировать самые повторяющиеся задачи, создавая эффективные макросы с помощью VBA.

Видеоуроки

ресурса

Курсы

Учебные пособия по Dashboards

Изучите навыки визуализации данных, чтобы разрабатывать и создавать динамичные и красивые панели мониторинга Excel.

Видеоуроки

ресурса

Курсы

Руководства по Power Query

Узнайте, как использовать Power Query, инструмент бизнес-аналитики в Excel, который позволяет извлекать, очищать и изменять данные для подготовки их к анализу.

Видеоуроки

ресурса

Курсы

Руководства по Power Pivot

Узнайте, как использовать Power Pivot, инструмент бизнес-аналитики в Excel, который позволяет преобразовывать большие наборы данных в расширенные таблицы для анализа и моделирования данных.

Видеоуроки

ресурса

Курсы

Руководства по Power BI

Более опытные пользователи Excel, которые работают с такими инструментами, как Power Query и Power Pivot для очистки и анализа своих данных, также могут быть заинтересованы в Power BI.Вы можете импортировать данные из файлов Excel в Power BI для создания красивых и интерактивных отчетов и панелей мониторинга.

Видеоуроки

ресурса

Курсы

Мы надеемся, что приведенные выше ресурсы помогут вам в вашем стремлении изучить Excel в Интернете. Загляните в наш раздел ресурсов и подпишитесь на нашу рассылку, чтобы получать уведомления о новых статьях, учебных пособиях и вебинарах.

Готовы узнать больше об Excel и добавить сертификат в свое резюме? Просмотрите наши онлайн-курсы по Excel сегодня.

Посмотреть курсы Excel

Онлайн-курсы по Excel — 11 бесплатных учебных курсов по Excel

Программное обеспечение для работы с электронными таблицами, такое как Microsoft Excel, используется всеми, от офисных работников до специалистов по обработке данных. Excel дает много возможностей людям, работающим с данными, но также может пугать.

Мы выпустили полный курс на YouTube-канале freeCodeCamp.org, который научит вас с самого начала использовать Microsoft Excel. Вы узнаете, создав 6 реальных проектов.Большая часть контента также применима к Google Таблицам.

Курс создан Шадом Слютером. Шад — профессор информатики в Университете Гранд-Каньона. За последние 20 лет он обучал учеников от 6 класса до пожилых людей многим аспектам технологий.

Excel — один из наиболее часто используемых инструментов в бизнесе, машиностроении, бухгалтерском учете и многих других отраслях. Это помогает вам организовать и управлять большим объемом данных, которые в противном случае могут быть трудоемкими, рутинными и сложными для организации.

Если вы работаете в сфере бизнеса, изучение Excel необходимо. Это помогает вам управлять запасами, человеческими ресурсами и финансами.

В этом курсе Excel вы узнаете, как:

• вводить данные,
• перемещаться по электронной таблице,
• создавать формулы для решения проблем,
• создавать диаграммы и графики,
• понимать относительные и абсолютные ссылки,
• импортирует и экспортирует данные,
• реализует ВПР,
• использует сводные таблицы,
• разбивает и объединяет текст,
• и другие.

При изучении Excel вы создадите следующие шесть проектов:

• Заработная плата
• Журнал успеваемости
• Факторы принятия решения
• База данных продаж
• Инвентаризация автомобилей
• Шаблоны решения проблем

Продолжительность курса Microsoft Excel:

📺 Изучите Microsoft Excel — полный 3-часовой видеокурс на YouTube.

Бесплатные курсы и руководства по Excel

А вот и множество других бесплатных курсов и руководств по Excel, которые вы можете использовать для освоения Excel.Они отвечают на некоторые из наиболее часто задаваемых вопросов об Excel.

А вот несколько руководств по программированию в Excel, если вы хотите объединить Python и другие языки программирования с электронными таблицами, чтобы автоматизировать еще больше вашей работы.

10 лучших онлайн-курсов, классов и учебных пособий по Excel [2021]

Microsoft Excel — наиболее широко используемое программное обеспечение в бизнес-сообществе. Будь то банкиры, бухгалтеры, бизнес-аналитики, специалисты по маркетингу, ученые или предприниматели, почти все профессионалы постоянно используют Excel.Так что, учитывая, что Excel настолько широко распространен в рабочей силе, это помогает им хорошо владеть. Наличие навыков Excel в вашем резюме может значительно повысить ваши шансы на трудоустройство. Самое приятное то, что вы можете развивать свои навыки работы с Excel, используя онлайн-ресурсы, не выходя из дома, без необходимости тратить деньги на курсы в колледже. Пройдя правильный курс обучения Excel и овладев основами Excel, вы сможете улучшить свои перспективы трудоустройства и карьерного роста и стать ценным активом для вашей организации.Microsoft Excel также может быть очень полезным инструментом для анализа данных в повседневной жизни, например для управления личными финансами. Таким образом, навыки Excel имеют решающее значение для личного развития.

Наши глобальные эксперты составили список из 10 лучших онлайн-тренингов, курсов, классов и учебных пособий по Excel на 2021 год. Эти ресурсы отлично подходят для того, чтобы освежить свои навыки работы с Excel или даже получить сертификат Excel, чтобы укрепить свое резюме. Они предназначены для всех учащихся начального, среднего и продвинутого уровней.

Этот курс поможет вам освоить Microsoft Excel от начального до продвинутого уровня. Его преподает Кайл Пью, сертифицированный эксперт Microsoft Office, а также сертифицированный тренер Microsoft с более чем 18-летним опытом обучения в реальных классах и онлайн-обучении. Он использует свой опыт обучения в Excel, чтобы шаг за шагом направлять вас, чтобы вы могли заложить прочный фундамент по мере прохождения промежуточных, продвинутых уровней и выше.

Этот курс очень глубокий и объединяет 4 различных курса Excel:

• Microsoft Excel 101 (Введение) — охватывает основы Excel, например, интерфейс Excel, создание электронных таблиц, ввод / редактирование данных, работу с формулами, форматирование данных, параметры печати Excel, общие ярлыки Excel и визуализацию данных с помощью диаграмм
• Microsoft Excel 102 (средний уровень) — охватывает такие темы, как работа со списками / таблицами Excel, сортировка и фильтрация данных, работа со списками, проверка данных, импорт и экспорт данных, сводные таблицы и управление большими наборами данных
• Microsoft Excel 103 (продвинутый уровень) — охватывает такие темы, как создание расширенных формул Excel, функции поиска, текстовые функции, аудит формул Excel, защита таблиц Excel, анализ «что, если» и введение в макросы
• Освойте макросы Microsoft Excel и VBA в 6 простых проектах — подробно описывает макросы и VBA, чтобы вы узнали, как автоматизировать задачи Excel, как создавать процедуры VBA и т. Д.

Этот курс был создан с помощью Excel 2013. Но все функции работают в Excel 2010, 2013 и 2016. Вы получаете пожизненный доступ к 16 часам видеоконтента, 4 статьям и 37 загружаемым ресурсам.

Ключевые особенности

• Научитесь создавать эффективные таблицы
• Самые популярные функции Master Excel, такие как СУММ, ВПР, ЕСЛИ, СРЕДНЕЕ, ИНДЕКС / ПОИСКПОЗ и другие
• Изучите надстройку Microsoft Excel, инструменты PowerPivot
• Научитесь создавать динамические отчеты с помощью сводных таблиц Excel
• Научитесь автоматизировать повседневные задачи Excel с помощью макросов и VBA

Продолжительность: 16 часов видео по запросу
Рейтинг: 4.6
Зарегистрируйтесь здесь

Этот сертификат Excel предназначен для тех, кто хочет развить расширенные навыки работы с Excel для бизнеса. Он постепенно проведет вас через 4 уровня тем Excel (Essentials, Intermediate I, Intermediate II и Advanced), опираясь на знания, полученные на предыдущем уровне. По завершении этих курсов специализации Excel вы сможете создавать сложные электронные таблицы, включая профессиональные информационные панели, и выполнять сложные вычисления с использованием передовых функций и методов Excel.

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

Ключевые особенности

• Лучший онлайн-курс по сертификации Microsoft Excel для самообучающихся и бизнес-пользователей
• Научитесь ориентироваться в пользовательском интерфейсе Excel, вводить, обрабатывать и форматировать данные
• Научитесь создавать электронные таблицы, которые помогают прогнозировать и моделировать данные
• Изучите эффективные методы и инструменты, которые позволяют автоматизировать повседневные задачи в Excel
• Узнайте, как очищать и готовить данные для анализа
• Научитесь создавать диаграммы и таблицы для эффективного обобщения необработанных данных в удобной для пользователя форме
• Узнайте, как работать с датами и финансовыми функциями
• Создание профессиональных панелей мониторинга в Excel
• Научитесь создавать средства навигации для больших книг
• Включает в себя широкий спектр практических викторин и задач, которые предоставляют отличные возможности для развития ваших навыков.
• Отличная команда преподавателей, которая поможет вам на каждом этапе пути

Продолжительность: прибл.3 месяца, 11 часов в неделю
Рейтинг: 4.9
Зарегистрируйтесь здесь

Этот курс был создан экспертом по Excel Деннисом Тейлором, директором Taylor Associates. Он направлен на привитие необходимых навыков для использования Microsoft Excel и овладения им. Курс начинается с ознакомления с интерфейсом Excel, системой меню, с тем, как вводить и организовывать данные, как редактировать и форматировать данные, как создавать формулы с использованием простых функций. Затем он переходит к более сложным темам, таким как построение диаграмм и сводных таблиц, работа с несколькими листами, использование таких мощных функций, как ЕСЛИ, ВПР, СЧЁТЕСЛИ и т. Д.Вы также научитесь автоматизировать задачи с помощью макросов и использовать инструменты анализа данных, такие как Goal Seek и Solver.

Ключевые особенности

• Изучите систему меню Excel и легко выполняйте простые и сложные вычисления
• Получите советы по экономии времени и уловки для повышения эффективности, такие как ввод данных с помощью автозаполнения
• Работа с датой и временем
• Узнайте, как использовать возможности печати и построения диаграмм в Excel
• Научитесь работать с несколькими листами и рабочими тетрадями
• Общие сведения о безопасности и совместном использовании книг
• Изучите функции управления данными Excel, такие как сортировка, использование фильтров, инструментов проверки и т. Д.
• Узнайте, как создавать сводные таблицы и использовать сводные диаграммы

Продолжительность: 8 часов 53 минуты
Рейтинг: 4.6
Зарегистрируйтесь здесь

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

В этом курсе обучения Microsoft Excel используется Excel 2013 для Windows и предоставляется пожизненный доступ, который позволяет просматривать материалы и продолжать изучать новые материалы.За каждой видеолекцией следует викторина, которая поможет вам повторить и закрепить полученные знания. Вы также получите сертификат об окончании в конце занятия.

Ключевые особенности

• Идеально подходит для всех уровней квалификации от новичка до продвинутого
• Представлены качественными видеолекциями, наглядно демонстрирующими, как легко все делать в Excel
• Файлы проекта Excel и шаблоны Excel включены, что позволяет улучшить обучение с помощью практических занятий
• Узнайте, как найти и загрузить бесплатные шаблоны Excel, которые можно использовать как шаблоны бюджета, шаблон календаря или шаблон расписания.
• Ознакомьтесь с советами и рекомендациями по Excel, а также с секретами и ярлыками Excel
• Узнайте, как использовать пароли в Excel для защиты вашей работы в нескольких сценариях
• Узнайте, как интегрировать Microsoft Excel с Microsoft Word

Продолжительность: 6.5 часов видео по запросу
Рейтинг: 4.4
Зарегистрируйтесь здесь

Этот курс является лучшим расширенным курсом обучения Excel, доступным в Интернете, со всеми расширенными формулами и функциями Excel, необходимыми для того, чтобы стать пользователем Excel Power. В этом курсе вы познакомитесь с 75+ наиболее мощными функциями Excel с использованием практических демонстраций и кристально ясных объяснений. Инструктор Крис Даттон (который является сертифицированным инструктором Microsoft Excel Expert с огромным опытом преподавания) заставляет вас думать как Excel, а не просто запоминать синтаксис формул.Он сосредотачивается на контекстных примерах того, как применять эти формулы разными способами.

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

С этим популярным курсом вы получите 7 часов видеоконтента, загружаемые файлы проектов, викторины и домашние задания, а также индивидуальную поддержку инструктора.

Ключевые особенности

• Научитесь писать мощные и динамичные формулы с нуля
• Получите уникальные советы, инструменты и примеры из практики, которых вы не найдете ни в одном другом курсе
• Научитесь создавать динамические инструменты и панели мониторинга Excel для фильтрации, отображения и анализа данных
• Научитесь извлекать данные в реальном времени из API прямо в Excel (погода, котировки акций, маршруты и т. Д.)
• Узнайте, как объединять наборы данных из нескольких источников с помощью функций ПРОСМОТР, ИНДЕКС и ПОИСКПОЗ в Excel.
• Научитесь автоматизировать утомительные и трудоемкие задачи с помощью формул и функций ячеек в Excel

Продолжительность: 7 часов видео по запросу
Оценка: 4.6
Зарегистрируйтесь здесь

Это вводный курс по использованию Excel, который является частью специализации «Бизнес-статистика и анализ» Университета Райса. Он был разработан для двух типов учащихся — тех, кто имеет базовые функциональные знания Excel, и тех, кто регулярно использует Excel и желает усовершенствовать свои знания. Это обучение Excel состоит из 4 модулей, которые постепенно выводят вас от изучения основных операций (таких как чтение данных в Excel с использованием различных форматов данных, редактирование и организация данных и т. Д.) для более продвинутых функций (таких как сводные таблицы, диаграммы и графики и т. д.).

Инструктор Шарад Борле очень хорошо структурировал курс и использует простые для понимания примеры, которые помогают учащимся быстро все понять и уметь применять их в реальных сценариях. В курсе есть множество викторин и заданий, для выполнения которых потребуется доступ к Windows Microsoft Excel 2010 или более поздней версии.

Подробная информация о модулях курса:
1.Модуль 1: Введение в электронные таблицы — Введение в электронные таблицы Excel, основные функции и формулы данных
2. Модуль 2: Функции электронных таблиц для организации данных — Введение в функции Excel для организации и запроса данных, таких как IF, вложенные IF, VLOOKUP, HLOOKUP и RANDBETWEEN
3. Модуль 3: Введение в фильтрацию, сводные таблицы и диаграммы — Введение в возможности фильтрации данных Excel и сводных таблиц
4. Модуль 4: Расширенные графики и диаграммы — Введение в расширенные методы построения графиков и диаграмм, доступные в Excel

Ключевые особенности

• Научитесь использовать формулы в Excel, используя абсолютные и относительные ссылки
• Научитесь использовать сводные таблицы как с категориальными, так и с числовыми данными
• Изучите различные линейные, гистограммы и круговые диаграммы и научитесь строить их самостоятельно
• Общие сведения о сводных диаграммах, диаграммах рассеяния и гистограммах
• Готовит учащихся к более продвинутым темам в бизнес-статистике

Продолжительность: 4 недели, 1-2 часа в неделю
Оценка: 4.7
Зарегистрируйтесь здесь

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

Этот курс Excel был разработан Крисом Даттоном, который является сертифицированным преподавателем Microsoft Excel и является самым продаваемым инструктором по Excel и ведет курс в увлекательной, интерактивной и эффективной форме. Он совместим с Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 или Office 365. Он включает файлы проектов Excel, 6 загружаемых ресурсов, 22 статьи, домашние задания и тесты, а также пожизненный доступ к материалам курса.

Ключевые особенности

• Получите 100% полное представление о сводных таблицах Excel и сводных диаграммах Excel для оптимизации рабочего процесса в Excel
• Освойте уникальные советы, инструменты и примеры использования сводной таблицы
• Узнайте о вычисляемых полях, элементах и ​​значениях Excel
• Научитесь создавать интерактивные информационные панели Excel
• Проверьте свои навыки с помощью 10 практических примеров из сводной таблицы (погода, нападения акул, дегустации вин, рейтинги буррито, высшая лига бейсбола, фондовый рынок и т. Д.)
• Интерактивные практические демонстрации и упражнения в Excel на протяжении всего курса
• Требуется, чтобы учащиеся имели базовый опыт работы с Excel

Продолжительность: 6.5 часов видео по запросу
Рейтинг: 4.6
Зарегистрируйтесь здесь

Этот курс является частью курса «Навыки анализа и презентации данных: специализация подхода PwC на Coursera». Это лучший курс для изучения Advanced Excel. Он состоит из 4 модулей, в которых вы получите практические инструкции по расширенным функциям Excel 2013 и навыки использования PowerPivot для создания баз данных и моделей. Вы познакомитесь со встроенными инструментами Excel, такими как Решатель, Таблицы данных, Менеджер сценариев и Поиск цели.Наконец, вы узнаете о визуализации данных, графиках и отчетах Power View, а также о том, как объединить их в динамические информационные панели.

Модули курса:
1. Подготовка профессионального Excel — охватывает модели данных и базы данных, инструмент PowerPivot
2. Расширенный анализ сценариев — исследует различные аналитические методы и способы их использования
3. Визуализация данных — охватывает такие элементы визуализации данных, как диаграммы и графики в деталях
4. Dashboarding — Подробная информация о принципах проектирования и требованиях для создания информационной панели

Курс ведет Алекс Маннелла и содержит несколько оцениваемых викторин и заданий с отзывами коллег.По окончании курса вы получите электронный сертификат.

Ключевые особенности

• Мастер расширенных функций Excel
• Изучите 3 различных аналитических метода — анализ сценариев, анализ чувствительности и моделирование
• Узнайте, как создавать подробные графики и диаграммы, чтобы эффективно рассказывать историю ваших данных
• Выполнение различных типов анализа сценариев и моделирования с помощью набора встроенных инструментов Excel
• Создайте свою собственную функциональную панель мониторинга в Excel

Продолжительность: 4 недели, 3-4 часа в неделю
Оценка: 4.8
Зарегистрируйтесь здесь

Это целая серия Excel Mastery Series, объединенная в один курс, который превращает ученика из полного новичка в опытного программиста VBA. Он дает полное и всестороннее понимание Microsoft Excel на 3 уровнях, где вы постепенно овладеваете всеми основами, осваиваете расширенные функции Excel и осваиваете программирование Excel с помощью VBA. Он охватывает Excel 2010, Excel 2013 и Excel 2016.

Вы пройдете следующие три уровня:
1.Уровень 1 — Изучите основы Excel, ввод и редактирование данных, формул, функции
2. Уровень 2 — Научитесь использовать Excel как профессионалы, используя расширенные функции, условное форматирование, настраиваемое форматирование, вычисления со временем и датами, макросы, создание баз данных, диаграммы Ганта и т. д.
3. Уровень 3 — Научитесь программировать на VBA и автоматизировать рутинные задачи, такие как создание отчетов, даже автоматизировать Outlook, Word и PowerPoint

Курс был разработан и ведет Алан Джарвис, который преподает Microsoft Excel более 20 лет.Он создал этот универсальный мастер-курс по Excel для всех, кто является полным новичком, или тех, кто хочет поднять свои навыки Excel на ступень выше, или тех, кто хочет создавать эффективные бизнес-решения с помощью Excel. После регистрации на курс вы получаете доступ ко всем видеолекциям, статьям, викторинам, проектам и 116 загружаемым ресурсам на всю жизнь.

Ключевые особенности

• Научитесь создавать полнофункциональную реляционную базу данных с помощью Excel
• Создание диаграмм Ганта в Excel для планирования проектов
• Узнайте, как импортировать данные из нескольких источников
• Создание простого экрана ввода данных для автоматического заполнения сложных шаблонов
• Создание эффективных сложных проектов Excel и сложных приложений с нуля
• Узнайте, как полностью автоматизировать создание отчетов
• Научитесь программировать простые «вспомогательные» решения
• Учитесь в нескольких подробных пошаговых проектах
• Управляйте другими приложениями Microsoft, такими как Outlook, Word, PowerPoint и даже самой Windows

Продолжительность: 26.5 часов видео по запросу
Рейтинг: 4.6
Зарегистрируйтесь здесь

Это проектный курс по Excel VBA и макросам Excel. Его создал Кайл Пью, сертифицированный инструктор Microsoft с многолетним опытом. Он создал несколько различных проектов, в которых он знакомит вас с тем, как вы можете учиться и преуспевать в макросах и VBA. Курс охватывает ключевые фундаментальные концепции программирования в Excel (например, процедуры, переменные, циклы, условные операторы и т. Д.), Поэтому предварительный опыт программирования не требуется.Более глубокое обучение гарантируется сценариями, основанными на проектах.

Курс содержит подробные инструкции в виде видеолекций и PDF-документов, на которые вы можете ссылаться всю жизнь. Он разработан для тех, кто использует Excel для бизнеса и хочет автоматизировать свои повседневные задачи или создавать небольшие приложения с помощью Excel. Материалы курса работают в Microsoft Excel 2007, 2010, 2013 и 2016 годах.

Ключевые особенности

• Освойте процесс автоматизации рутинных задач с помощью макросов Excel и Excel VBA
• Определите, когда лучше использовать Macro Recorder по сравнению с VBA
• Научитесь применять концепции VBA в реальных сценариях
• Узнайте, как писать собственные макросы VBA для создания более надежной автоматизации
• Научитесь импортировать данные из внешних текстовых файлов
• Научитесь работать с пользовательскими формами Excel VBA
• Завершение реальных проектов макросов / VBA от начала до конца

Продолжительность: 5 часов видео по запросу
Оценка: 4.