Microsoft Office, будучи одним из самых важных программ, которые нужно знать, независимо от того, какую роль вы можете выполнять, стал одной из областей, в которой люди переоценивают свои возможности. Для многих людей высокий уровень владения Microsoft Excel означает использование сводных таблиц и сводных диаграмм. Для некоторых других людей опыт означает знание VLookUp и HLookUp. Я не могу сказать, что все это неправильно, поскольку это зависит от роли и отрасли, на которую вы претендуете.
Однако важно не то, какие формулы и функции вы знаете. Как применить их в полной мере, чтобы максимизировать функцию, является ключом к полному использованию Excel.
При этом вы должны сначала узнать, что такое формулы, прежде чем научиться их применять. В этой статье мы рассмотрим, какие основные формулы нужно знать, чтобы претендовать на «знание» Excel. Чтобы помочь вам понять тип формул, вы можете обратиться к карте ума ниже.
Качественные функции
Сначала я начну с более простых функций форматирования — Upper(), Lower(), Proper().
Название формулы: Верхний([Текст])
Функция: Преобразование всех символов в [Текст] в верхний регистр
Пример: =Верхний("ExCeL") => EXCEL
Название формулы: Lower([Text])
Функция: Преобразование всех символов в [Text] в нижний регистр
Пример =Lower("ExCeL") =› excel
Название формулы: Правильный([Текст])
Функция: Преобразование символов в [Текст] в прописные только для первых символов
Пример: = Правильный("ExCeL aPplied") => Применяется Excel
Хотя в обычной ситуации эти функции могут показаться излишними, бывают случаи, когда эти формулы могут вам понадобиться.
Далее перейдем к опорным формулам. Две вспомогательные формулы, которые редко используются сами по себе, это Len() и Find(). Мы проиллюстрируем вам их полезность в конце качественного раздела.
Название формулы: Len([Text])
Функция: Сообщает, сколько символов содержится в самом [Text]
Пример = Len("ExceL") = 5
Название формулы: Найти([Поисковое слово],[Текст],[Начальная позиция])
Функция: Начать с [Начальная позиция] и найти [Поисковое слово] в [Текст] и вернуть его позицию в [Текст ]
Пример =Find("c","ExceL",1) = 3
Примечание. Find() будет последовательно переходить к концу строки для поиска искомого термина. Если в [Текст] существует несколько совпадений [Поисковый термин], он вернет местоположение первого совпадения.
И последнее, но не менее важное: мы рассмотрим функции извлечения. Функции извлечения в основном удаляют часть [Текста] в зависимости от количества нужных вам символов. В основном они используются для текстовой аналитики или для расшифровки выдержек из систем. В этой статье мы рассмотрим только функции Left() и Right(), которых должно быть достаточно для большинства случаев.
Имя формулы: Left([Text],[Number])
Функция: Берет самые левые [Number] символы из [Text]
Пример: = left(“Excel”,2) = Ex
Название формулы: Right([Text],[Number])
Функция: Взять крайние правые [Number] символы из [Text]
Пример: = Right(“Excel”,2) = el
Знание приведенных выше текстовых формул является крайне базовым и не может считаться знанием Microsoft Excel. Эти формулы обычно не несут особой цели и недостаточно гибки. Давайте возьмем приведенную ниже ситуацию в качестве примера, предположим, что вы должны извлечь имя из списка, где оно отформатировано как [Имя]/[Фамилия]. Естественным инстинктом является использование left() для получения первого имени. Однако сколько символов вы поместите, чтобы оно было применимо ко всем именам в этом формате?
Left позволяет вам легко получить первые несколько символов любой заданной строки, но если вы посмотрите на каждую отдельную строку и наберете количество символов, нет никакой разницы по сравнению с выполнением этого вручную. Поэтому, объединив ее с формулой find(), поскольку имя всегда стоит перед «/», вы получите формулу, которая подходит для всех сценариев, следующих правилу.
Количественные функции
В зависимости от профессии большую часть времени мы использовали Excel в основном для вопросов, связанных с числами. Таким образом, количественные формулы чрезвычайно важны для большинства пользователей.
Как и в случае с текстовыми функциями, давайте начнем с простых функций «форматирования». Формула в разделе «форматирование» будет — round()
Название формулы: Округлить([Число],Количество десятичных разрядов)
Функция: Округлить число до ближайшего определенного десятичного разряда
Пример: =Округлить(0,112,1) = 0,1
Примечание. Вы также можете округлить число до ближайшей сотни, используя отрицательные десятичные разряды
Пример: =Округление(100 054,-2) = 100 100
Подобно Round(), Roundup() и Rounddown() используют тот же синтаксис формулы с той лишь разницей, что указывается направление округления.
Пример: Округление(1.02,0) = 2
Пример: Округление(1.98,0) = 1
Две другие полезные формулы округления — «Потолок» и «Пол». Помимо их округления в указанном направлении, он также указывает уровень значимости для округления.
Название формулы: =Потолок([Число],[Уровень значимости])
Функция: Округлить число до более высокого уровня значимости
Пример 1: = Потолок(1,42,0,05) = 1,45
Пример 2: = Потолок (1,42,0,08) = 1,44
Название формулы: =Минимум([Число],[Уровень значимости])
Функция: Округление числа до более низкого уровня значимости
Пример 1:= Минимальный уровень (1,49,0,05) = 1,45
Пример 2: =Этаж(1,49,0,08)= 1,44
После того, как мы познакомились с «форматированием» чисел, мы можем перейти к сравнению. Формулы сравнения, как следует из названия, будут считывать контрольный диапазон чисел и определять его значение среди контрольных чисел.
Название формулы: =Max([Reference Range])
Функция: Найти максимальное число в [Reference Range]
Пример 1: =Max(1,2,3,4,5,6,5 ,4,3,2,1) = 6
Название формулы: =Min([Reference Range])
Функция: Найти минимальное число в [Reference Range]
Пример 1: = Min(6,5,4,3,2,1,2 ,3,4,5,6) = 1
Название формулы: =Large([Reference Range],[N])
Функция: Возвращает N-е наибольшее число в [Reference Range]
Пример 1: =Large([1,5,44,23 ,99,2,22],3) = 23
Пример 2: = Большой ([1,5,44,23,99,2,22],2) = 44
Название формулы: =Small([Reference Range],[N])
Функция: Возвращает N-е наименьшее число в [Reference Range]
Пример 1: =Small([1,5,44,23 ,99,2,22],3) = 5
Пример 2: =Small([1,5,44,23,99,2,22],2) = 2
Как вы заметили, Large с rank = 1 даст тот же результат, что и Max(), а Small с rank = 1 даст тот же результат, что и Min(). Наиболее распространенное приложение, используемое для малых и больших, — это получение 10 лучших или 10 последних категорий. Поскольку Max() дает вам только самое высокое значение, вам нужно будет зависеть от Large() для выполнения других ранжирований.
Наконец, мы перейдем к аналитическим формулам. Если ваша роль требует от вас работы с числами или аналитикой, вам обязательно понадобятся следующие 3 формулы (или их варианты).
Название формулы:= Sum([Reference Range])
Функция: суммировать все числа в [Reference Range] и вернуть результат
Пример 1: =sum(1,5,6,8,10 ) = 30
Название формулы:=Count([Reference Range])
Функция: Подсчет всех чисел в [Reference Range])
Пример 1: =Count(1,2,5,A,22) = 4
Название формулы:=Average([Reference Range])
Функция: найти среднее значение всех чисел в [Reference Range]
Пример 1: =Average(2,3,4,5,6) = (2+3+4+5+6)/5 = 4
Хотя эти формулы сами по себе не очень полезны, их варианты (Sumif, Countif) являются чрезвычайно полезными аналитическими функциями, особенно при работе с огромными наборами данных или при создании информационных панелей.
Функции поиска
VLookup был подробно описан в моих предыдущих статьях. Вы можете просмотреть следующую ссылку, чтобы узнать больше о VLookUps (и HLookUps, поскольку они похожи) и узнать больше о распространенных ошибках, возникающих при их использовании.
на этом я закончу свою статью. Следите за моими сообщениями, чтобы узнать больше о Ifs() и о том, как выполнять аналитику со всеми формулами, описанными мной на моей странице.
