Расширенные формулы Excel - 10 формул, которые вы должны знать!

Каждый финансовый аналитик The Analyst Trifecta® Guide Полное руководство о том, как стать финансовым аналитиком мирового уровня. Вы хотите быть финансовым аналитиком мирового уровня? Вы хотите следовать передовым отраслевым практикам и выделяться из толпы? Наш процесс, называемый аналитиком Trifecta®, состоит из аналитики, презентаций и межличностных навыков. Он проводит в Excel больше времени, чем они могут себе представить. Основываясь на многолетнем опыте, мы составили самые важные и продвинутые формулы Excel, которые должен знать каждый финансовый аналитик мирового уровня.

1. ИНДЕКС МАТЧ

Формула: = ИНДЕКС (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))

Это продвинутая альтернатива формулам ВПР или ГПР (которые имеют несколько недостатков и ограничений). ИНДЕКС СООТВЕТСТВИЕ Формула сопоставления индекса Объединение функций ИНДЕКС и ПОИСКПОЗ является более мощной формулой поиска, чем ВПР. Узнайте, как использовать ИНДЕКС ПОИСКПОЗ в этом руководстве по Excel. Index возвращает значение ячейки в таблице на основе номера столбца и строки, а Match возвращает позицию ячейки в строке или столбце. Узнайте, как это сделать, в этом руководстве - это мощное сочетание формул Excel, которое выведет ваш финансовый анализ и финансовое моделирование на новый уровень.

ИНДЕКС возвращает значение ячейки в таблице на основе номера столбца и строки.

ПОИСКПОЗ возвращает позицию ячейки в строке или столбце.

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

Расширенные формулы Excel - соответствие индексаФормула сопоставления индекса Объединение функций ИНДЕКС и ПОИСКПОЗ является более мощной формулой поиска, чем ВПР. Узнайте, как использовать ИНДЕКС ПОИСКПОЗ в этом руководстве по Excel. Индекс возвращает значение ячейки в таблице на основе номера столбца и строки, а Match возвращает положение ячейки в строке или столбце. Узнайте, как это сделать, в этом руководстве

Для получения пошагового объяснения или того, как использовать эту формулу, обратитесь к нашему бесплатному руководству о том, как использовать ИНДЕКС ПОИСКПОЗ в формуле сопоставления индекса в Excel. Объединение функций ИНДЕКС и ПОИСКПОЗ - более эффективная формула поиска, чем ВПР. Узнайте, как использовать ИНДЕКС ПОИСКПОЗ в этом руководстве по Excel. Индекс возвращает значение ячейки в таблице на основе номера столбца и строки, а Match возвращает положение ячейки в строке или столбце. Узнайте, как это сделать, в этом руководстве.

2. ЕСЛИ в сочетании с И / ИЛИ

Формула: = ЕСЛИ (И (C2> = C4, C2 <= C5), C6, C7)

Любой, кто потратил много времени на создание различных типов финансовых моделей. Типы финансовых моделей. К наиболее распространенным типам финансовых моделей относятся: модель с тремя отчетами, модель DCF, модель M&A, модель LBO, модель бюджета. Откройте для себя 10 основных типов и знает, что вложенные формулы ЕСЛИ могут быть кошмаром. Сочетание IF с функцией AND или OR может быть отличным способом упростить аудит формул и облегчить понимание другими пользователями. В приведенном ниже примере вы увидите, как мы использовали комбинацию отдельных функций для создания более сложной формулы.

Подробное описание выполнения этой функции в Excel см. В нашем бесплатном руководстве по использованию оператора ЕСЛИ с оператором И / ИЛИ, если между двумя числами. Загрузите этот бесплатный шаблон для оператора ЕСЛИ между двумя числами в Excel. В этом руководстве мы шаг за шагом покажем вам, как вычислить IF с помощью оператора AND. Узнайте, как создать оператор IF, который проверяет, содержит ли ячейка значение между двумя числами, а затем выводит желаемый результат, если это условие выполнено. Расширенные формулы Excel - ЕСЛИ ИОператор ЕСЛИ между двумя числами Загрузите этот бесплатный шаблон для оператора ЕСЛИ между двумя числами в Excel. В этом руководстве мы шаг за шагом покажем вам, как вычислить IF с помощью оператора AND. Узнайте, как создать оператор IF, который проверяет, содержит ли ячейка значение между двумя числами, а затем выводит желаемый результат, если это условие выполнено.

3. СМЕЩЕНИЕ в сочетании с СУММОМ или СРЕДНИМ

Формула: = СУММ (B4: СМЕЩЕНИЕ (B4,0; E2-1))

Функция СМЕЩЕНИЕ Функция СМЕЩЕНИЕ Функция СМЕЩЕНИЕ относится к функциям поиска и справочника Excel. OFFSET вернет диапазон ячеек. То есть он вернет указанное количество строк и столбцов из указанного начального диапазона. сам по себе не особенно продвинут, но когда мы объединяем его с другими функциями, такими как SUM или AVERAGE, мы можем создать довольно сложную формулу. Предположим, вы хотите создать динамическую функцию, которая может суммировать переменное количество ячеек. С помощью обычной формулы СУММ вы ограничены статическим вычислением, но добавив СМЕЩЕНИЕ, вы можете перемещать ссылку на ячейку.

Как это работает: чтобы эта формула работала, мы заменяем конечную ссылочную ячейку функции СУММ на функцию СМЕЩЕНИЕ. Это делает формулу динамической, и в ячейке, обозначенной как E2, вы можете указать Excel, сколько последовательных ячеек вы хотите добавить. Теперь у нас есть несколько сложных формул Excel!

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

формула смещения суммы

Как видите, формула СУММ начинается с ячейки B4, но заканчивается переменной, которая представляет собой формулу СМЕЩЕНИЯ, начинающуюся с B4 и продолжающуюся значением в E2 («3») минус один. Это перемещает конец формулы суммы на 2 ячейки, суммируя данные за 3 года (включая начальную точку). Как вы можете видеть в ячейке F7, сумма ячеек B4: D4 равна 15, что дает нам формула смещения и суммы.

Узнайте, как построить эту формулу, шаг за шагом, в нашем расширенном курсе Excel.

4. ВЫБЕРИТЕ

Формула: = ВЫБРАТЬ (выбор, вариант1, вариант2, вариант3)

Функция ВЫБРАТЬ Функция ВЫБРАТЬ Функция ВЫБРАТЬ относится к функциям поиска и справочника Excel. Он вернет значение из массива, соответствующее указанному номеру индекса. Функция вернет n-ю запись в данном списке. Как финансовый аналитик, функция ВЫБОР полезна при выборе из заданного набора данных. Например, мы отлично подходят для сценарного анализа финансового моделирования. Он позволяет вам выбирать между определенным количеством вариантов и возвращать «выбор», который вы выбрали. Например, представьте, что у вас есть три разных предположения относительно роста доходов в следующем году: 5%, 12% и 18%. Используя формулу ВЫБРАТЬ, вы можете вернуть 12%, если скажете Excel, что хотите вариант №2.

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

ВЫБРАТЬ функцию

Чтобы посмотреть видеодемонстрацию, посетите наш расширенный курс формул Excel.

5. XNPV и XIRR

Формула: = XNPV (ставка дисконтирования, денежные потоки, даты)

Если вы аналитик, работающий в сфере инвестиционного банкинга, инвестиционный банкинг. Карьерный путь. Руководство по карьере в инвестиционном банке - спланируйте свою карьеру в IB. Узнайте о зарплатах в инвестиционном банке, о том, как устроиться на работу и что делать после карьеры в IB. Подразделение инвестиционного банкинга (IBD) помогает правительствам, корпорациям и учреждениям привлекать капитал и осуществлять слияния и поглощения (M&A). , исследование капитала, финансовое планирование и анализ (FP&A Аналитик FP&A Станьте аналитиком FP&A в корпорации. Мы опишем зарплату, навыки, личность и обучение, необходимые для работы FP&A и успешной финансовой карьеры. Аналитики FP&A, менеджеры и директора отвечает за предоставление руководителям необходимого анализа и информации) или любой другой области корпоративных финансов, требующей дисконтирования денежных потоков,тогда эти формулы - палочка-выручалочка!

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

Формула расширенного финансирования XNPV в Excel

Для более подробной разбивки см. Наше бесплатное руководство по формулам IRR и XIRR. Сравнение XIRR и IRR. Зачем использовать XIRR и IRR. XIRR назначает конкретные даты каждому отдельному денежному потоку, что делает его более точным, чем IRR, при построении финансовой модели в Excel. а также наше руководство по XNPV.

6. СУММЕСЛИ и СЧЁТЕСЛИ

Формула: = СЧЁТЕСЛИ (D5: D12, ”> = 21 ″)

Эти две сложные формулы отлично подходят для условных функций. СУММЕСЛИ добавляет все ячейки, соответствующие определенным критериям, а СЧЁТЕСЛИ подсчитывает все ячейки, соответствующие определенным критериям. Например, представьте, что вы хотите подсчитать все ячейки, которые больше или равны 21 (законный возраст употребления алкоголя в США), чтобы узнать, сколько бутылок шампанского вам нужно для клиентского мероприятия. Вы можете использовать СЧЁТЕСЛИ в качестве расширенного решения, как показано на скриншоте ниже.

СЧЁТЕСЛИ формула

В нашем продвинутом курсе Excel мы разбиваем эти формулы еще более подробно.

7. PMT и IPMT

Формула: = PMT (процентная ставка, количество периодов, приведенная стоимость)

Если вы работаете в коммерческом банке Профиль карьеры в коммерческом банке Карьерный путь в коммерческом банке предполагает предоставление клиентам кредитных продуктов, таких как срочные ссуды, возобновляемые кредитные линии, синдицированные услуги, услуги по управлению денежными средствами и другие продукты с фиксированным доходом. Как кредитный аналитик или менеджер по работе с клиентами, вы предоставляете финансовые консультации, недвижимость, FP&A FP&A Финансовое планирование и анализ (FP&A) - важная функция в корпорации. Специалисты FP&A поддерживают принятие управленческих решений для генерального директора, финансового директора и совета директоров с помощью планирования, анализа и моделирования. Узнайте, что влечет за собой работа аналитика, менеджера или директора FP&A - зарплата, требования, образование, навыки или любую должность финансового аналитика, которая занимается графиками долга, вы захотите понять эти две подробные формулы.

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

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

8. LEN и TRIM

Формулы: = LEN (текст) и = TRIM (текст)

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

В приведенном ниже примере вы можете увидеть, как формула TRIM очищает данные Excel.

Advanced Excel - TRIM

9. СЦЕПИТЬ

Формула: = A1 & "дополнительный текст"

На самом деле Concatenate не является отдельной функцией - это просто инновационный способ объединения информации из разных ячеек и создания более динамичных рабочих листов. Это очень мощный инструмент для финансовых аналитиков, выполняющих финансовое моделирование (см. Наше бесплатное руководство по финансовому моделированию. Бесплатное руководство по финансовому моделированию. Это руководство по финансовому моделированию содержит советы и передовые методы работы с Excel в отношении предположений, драйверов, прогнозирования, связывания трех отчетов, анализа DCF и т. Д. учить больше).

В приведенном ниже примере вы можете увидеть, как текст «Нью-Йорк» плюс «,» соединяется с «Нью-Йорк» для создания «Нью-Йорк, Нью-Йорк». Это позволяет создавать динамические заголовки и метки на листах. Теперь вместо прямого обновления ячейки B8 вы можете обновлять ячейки B2 и D2 независимо. Имея большой набор данных, это ценный навык, которым вы должны располагать.

Формула конкатенации Пример Excel

10. Функции CELL, LEFT, MID и RIGHT.

Эти расширенные функции Excel можно комбинировать для создания очень сложных и сложных формул. Функция ЯЧЕЙКА может возвращать различную информацию о содержимом ячейки (например, ее имя, расположение, строку, столбец и т. Д.). Функция LEFT может возвращать текст из начала ячейки (слева направо), MID возвращает текст из любой начальной точки ячейки (слева направо), а RIGHT возвращает текст из конца ячейки (справа налево).

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

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

Формулы CELL, LEFT, MID и RIGHT

Больше обучения формулам Excel

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

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

  • Шпаргалка по формулам Excel Шпаргалка по формулам Excel Шпаргалка по формулам Excel по финансам предоставит вам все наиболее важные формулы для выполнения финансового анализа и моделирования в таблицах Excel. Если вы хотите стать мастером финансового анализа Excel и экспертом по построению финансовых моделей, вы попали в нужное место.
  • Сочетания клавиш Excel Сочетания клавиш Excel ПК Mac Сочетания клавиш Excel - список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, финансов и бухгалтеров. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, манипулирование данными, редактирование формул и ячеек и другие краткие сведения.
  • Бесплатный ускоренный курс по Excel
  • Продвинутый курс Excel
  • Excel для финансового моделирования