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

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

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

Пройдитесь по списку и расскажите нам о своем фаворите!

Учащиеся получают в среднем повышение заработной платы на 58%, а максимальное повышение составляет до 400%.

1. СОВПАДЕНИЕ ПО ИНДЕКСУ

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

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

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

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

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

Источник

Разберем пошагово:

Как объединить ИНДЕКС и ПОИСКПОЗ

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

Интересные идеи проекта Data Science

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

2. СРЕДНЯЯ

Чтобы выполнить формулу среднего, чтобы найти среднее значение любого диапазона чисел, вам необходимо выполнить следующие шаги:

Введите значения, ячейки или шкалу ячеек, которые вы вычисляете в формате.

Формула должна начинаться с =СРЗНАЧ(число1, число 2 и т.д.)

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

• Введите значения, ячейки или шкалу ячеек, которые вы вычисляете, в формате, как вы делали выше.
• Формула должна иметь вид =СРЗНАЧ(Начальное значение: Конечное значение).

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

3. СУММЕСЛИ

Эта формула в Excel обозначается как СУММЕСЛИ(диапазон, критерии, [диапазон суммы]). Это приведет к сумме значений в пределах желаемого диапазона ячеек, которые будут соответствовать установленным вами требованиям. Например, функция =СУММЕСЛИ(C3: C12, ">70 000) вернет сумму значений между ячейками C3 и C12 только из тех ячеек, значение которых превышает 70 000.

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

Формула для приведенного выше условия может быть =СУММЕСЛИ(диапазон, критерии, [сумма_диапазон]), где диапазон может быть определен как диапазон листа, из которого вам нужно выбрать значения.

[sum_range] определяется как дополнительный или необязательный диапазон, который вы собираетесь добавить в дополнение к первому введенному диапазону.

Вот пример:

Источник

4. СМЕЩЕНИЕ В СОЧЕТАНИИ С СУММОЙ

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

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

=СУММ(B4:СМЕЩЕНИЕ(B4,0,E2-1))

Источник

Здесь конечная ссылка функции SUM заменяется функцией OFFSET. Формула SUM, начинающаяся с B4, заканчивается переменной и представляет собой формулу OFFSET, начинающуюся с B4 и продолжающуюся значением в E2 («3») минус один. Это помогает методу перемещаться по двум ячейкам и суммировать данные за три года. В приведенной выше ссылке вы можете видеть, что ячейка F7 содержит сумму ячеек B4: D4 = 15.

5. ЕСЛИ И

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

Например, если вы хотите пометить сотрудников с зарплатой выше 50 000 и идентификатором сотрудника больше 3, формула будет выглядеть так:

ЕСЛИ(И(E4>3;F4>50000)1,0)

Источник

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

6. СЦЕПИТЬ

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

= СЦЕПИТЬ (B3, C3)

Источник

7. ПМТ

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

• Срок кредита
• Начальная сумма (деньги) кредита
• Будущая стоимость
• Тип кредита

Теперь, если вы хотите найти ежемесячный платеж по основной сумме, формула для него будет следующей:

= PMT (ставка, за, PV, [fv], [тип])

Давайте разберемся в этом на примере:

Источник

=ФМТ(C2/12.B2.A2)

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

Популярные типы вакансий в области науки о данных

8. ОТДЕЛКА

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

=ОТРЕЗАТЬ(C6)

Источник

Это вернет вам значение Chandan Kale без каких-либо дополнительных пробелов, прикрепленных к началу или концу.

9. ЛЕН

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

=ДЛСТР(B2)

Источник

А если вы хотите выделить все ячейки в графе Donation, то вам необходимо:

• Выберите вкладку Главная в меню
• Нажмите «Условное форматирование» (на панели инструментов).
• Выберите «Использовать формулу», чтобы определить, какие ячейки следует форматировать.

Источник

Здесь, если вы укажете условие «> 3», то все, что превышает 1000 долларов США, получит уникальное форматирование, которое вы можете выбрать, щелкнув параметр «Формат».

10. ВЫБЕРИТЕ

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

Формула:

=ВЫБРАТЬ(C7,D3,D4,D5)

Источник

11. КЛЕТКА, ЛЕВАЯ, СРЕДНЯЯ И ПРАВАЯ

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

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

Источник

12. XNPV и XIRR

Для всех аналитиков, которые сталкиваются с инвестиционно-банковскими услугами, исследованиями акций или любой другой областью корпоративных финансов, требующей дисконтирования денежных потоков, эти формулы наверняка сэкономят вам много времени и ворчания!

Интервью по науке о данных Вопросы и ответы

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

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

Источник

С другой стороны, функция ЧИСТВНР сообщает внутреннюю норму доходности (где отток = приток) для ряда денежных потоков, происходящих через нерегулярные промежутки времени, например:

13. СЧЕТ ( )

Аналитики часто находятся в напряжении, пытаясь найти количество ячеек со значениями (числа, ошибки, текст, логические значения) и тех, которые пусты. Функция COUNTA() может помочь вам найти имена непустых ячеек в выбранном диапазоне. Например, формула для подсчета значений для листа данных, имеющего столбцы A1-A10, выглядит следующим образом:

=СЧЕТЧАСТЬ(A1: A10)

Источник

14. ФВ

Эта формула пригодится, если вы хотите вложить деньги во что-то и знать, чего они стоят. Требования формулы FV:

• Процентная ставка кредита
• Количество платежей
• Оплата за каждый период
• Текущий начальный баланс
• Тип кредита

Например, если вы хотите сравнить несколько чистых компакт-дисков и у вас есть наследство в размере 20 000 долларов, которое вы можете инвестировать в компакт-диск. Процентные ставки представлены в десятичном формате; платежи нулевые. Формула сценария будет такой:

=БС(А2/12,В2,С2,D2)

Источник

Результаты будут:

Источник

15. СЛУЧАЙНО МЕЖДУ

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

=СЛУЧМЕЖДУ(начальная точка, конечная точка)

Источник

16. МАЛЕНЬКИЙ

Функция НАИМЕНЬШИЙ в Excel возвращает числовые значения в зависимости от положения значения в списке, ранжированном по важности. Эта функция помогает получить «n-е наименьшее значение» из массива или диапазона ячеек, например наименьшее значение, 2-е наименьшее значение, 3-е наименьшее значение и т. д.

Синтаксис формулы

=МАЛЕНЬКИЙ (раз,диапазон)

Например,

Источник

Поскольку функция МАЛЕНЬКИЙ является автоматической, вам необходимо указать диапазон и целое число для «n-го», чтобы указать ранжированное значение. Официальные названия этих аргументов — «массив» и «k».

17. КВАРТИЛЬ

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

Синтаксис: =QUARTILE (массив, кварта)

Источник

ЗАКЛЮЧЕНИЕ:

Microsoft Excel совершенно неизбежен, когда дело доходит до рабочего места 21-го века, но хитрость в том, что он не должен быть таким пугающим. Сделайте его своим другом и наблюдайте, как растет ваша продуктивность!