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

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

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

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

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

Расширенный пример Vlookup 1

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

Расширенный пример Vlookup 1-1

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

Расширенный пример Vlookup 1-2

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

Расширенный пример Vlookup 1-3

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

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

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

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

Расширенный индекс, пример 2

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

Расширенный индекс INDEX, пример 2-1

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

Расширенный индекс, пример 2-2

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

Расширенный индекс, пример 2-3

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

Расширенный индекс, пример 2-4

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

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

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

Пример расширенного соответствия 3

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

Пример расширенного соответствия 3-1

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

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

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

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

Расширенный IF AND Пример 4

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

Расширенный IF AND Пример 4-1

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

Расширенный IF AND Пример 4-2

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

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

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

Расширенный IF OR Пример 5

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

Расширенный вариант IF OR, пример 5-1

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

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

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

Расширенный пример СУММЕСЛИ 6

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

Расширенный пример СУММЕСЛИ 6-1

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

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

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

Пример 7 расширенного конкатенации

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

Пример расширенного конкатената 7-1

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

Пример расширенного конкатената 7-2

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

Пример расширенного конкатената 7-3

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

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

Расширенный левый пример 8

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

Расширенный левый пример 8-1

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

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

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

Расширенный пример смещения 9

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

Расширенный пример смещения 9-1

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

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

Расширенный пример TRIM 1

Результатом будет «Чандан Кале» без пробела перед Чанданом.

Расширенный пример TRIM 10-1

УЗНАТЬ БОЛЬШЕ >>

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.