Расширенные формулы Excel

Список 10 лучших расширенных формул и функций Excel

Вы можете скачать этот расширенный шаблон формул Excel здесь — Расширенный шаблон формул Excel

# 1 — Формула ВПР в Excel

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

Вы можете использовать ВПР, как показано ниже.

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

Перетащите формулу в остальные ячейки.

Существует три основных типа ВПР:

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

# 2 — Формула ИНДЕКС в Excel

Эта расширенная формула Excel используется для получения значения ячейки в данной таблице путем указания количества строк, столбцов или того и другого. Ex. Чтобы узнать имя сотрудника на 5^th наблюдение, Ниже приведены данные.

Мы можем использовать расширенную формулу Excel, как показано ниже:

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

Приведенная выше формула вернет «Раджеш Вед».

Заметка: Если вставить еще одну строку в 5^th строка, формула вернет «Чандан Кале». Следовательно, вывод будет зависеть от любых изменений, происходящих в таблице данных с течением времени.

# 3 — Формула MATCH в Excel

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

Формула будет такой, как указано ниже:

Функция ПОИСКПОЗ вернет 5 в качестве значения.

3^rd аргумент используется для точного совпадения. Вы также можете использовать +1 и -1 в зависимости от ваших требований.

Заметка: Можно комбинировать ИНДЕКС и ПОИСКПОЗ, чтобы преодолеть ограничение ВПР.

# 4 — ЕСЛИ И формула в Excel

Есть много случаев, когда нужно создавать флаги на основе некоторых ограничений. Все мы знакомы с основным синтаксисом IF. Мы используем эту расширенную функцию Excel IF для создания нового поля на основе некоторого ограничения существующего поля. Но что, если нам нужно учитывать несколько столбцов при создании флага. Ex. В приведенном ниже случае мы хотим отметить всех сотрудников, чья зарплата больше 50K, но идентификатор сотрудника больше 3.

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

Результатом будет 0.

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

# 5 — Формула ЕСЛИ ИЛИ в Excel

Точно так же мы также можем использовать функцию OR в excel вместо AND, если нам нужно выполнить одно из многих условий.

В приведенных выше случаях, если выполняется какое-либо из условий, ячейка будет заполнена как 1 else 0. Мы также можем заменить 1 или 0 некоторыми подстроками в двойных кавычках («»).

# 6 — Формула СУММЕСЛИ в Excel

В некоторых анализах вам может потребоваться отфильтровать некоторые наблюдения при применении функции суммы или подсчета. В таких случаях нам на помощь приходит расширенная функция СУММЕСЛИ в excel. Он фильтрует все наблюдения на основе определенных условий, указанных в этой расширенной формуле Excel, и суммирует их. Ex. Что, если мы хотим узнать сумму зарплат только тех сотрудников, у которых ID сотрудника больше 3.

Применяя формулу СУММЕСЛИМН:

Формула возвращает результат как 322000.

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

# 7 — СЦЕПИТЬ формулу в Excel

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

Для этого мы можем использовать формулу CONCATENATE.

Приведенная выше формула приведет к «1Аман Гупта».

У нас может быть еще один вариант, если поставить один дефис между ID и NAME. Ex. СЦЕПИТЬ (B3, «-«, C3) приведет к «1-Аман Гупта». Мы также можем использовать это в ВПР, когда ПРОСМОТР в значении Excel представляет собой смесь более чем одной переменной.

# 8 — Формула LEFT, MID и RIGHT в Excel

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

Результат представлен ниже:

Применение формулы ПРАВИЛЬНО в Excel то же самое. Просто мы будем смотреть на символ справа от строки. Однако в случае функции MID в Excel мы должны указать начальную позицию требуемой текстовой строки и длину строки.

# 9 — Формула смещения в Excel

Эта расширенная функция Excel с комбинацией других функций, таких как СУММ или СРЕДНЕЕ, может придать расчетам динамичный характер. Его лучше всего использовать в случаях, когда мы вставляем непрерывные строки в существующую базу данных. СМЕЩЕНИЕ Excel предоставляет нам диапазон, в котором нам нужно указать ссылочную ячейку, количество строк и столбцов. Ex. Если мы хотим вычислить среднее значение для первых 5 сотрудников в компании, где у нас есть зарплата сотрудников, отсортированных по идентификатору сотрудника, мы можем сделать следующее. Приведенный ниже расчет всегда дает нам зарплату.

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

# 10 — Формула TRIM в Excel

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