Эксель основные функции: 13 функций Excel, которые должен знать каждый специалист

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

Microsoft Excel — одно из самых известных компьютерных приложений. Это изменило то, как люди и компании работают с данными.

Таким образом, изучение Excel может помочь как в карьере, так и в личных потребностях.

Excel работает с использованием функций, а их около 500! Они варьируются от базовой арифметики до сложной статистики.

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

Итак, мы здесь, чтобы помочь вам! 🤝

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

Начнем!

Содержание

Что такое функции Excel?

Excel используется для вычисления и обработки чисел и текста. Для этого используется формулы !

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

Что такое операторы?

Это символы, которые определяют тип вычисления, которое вы хотите выполнить для элементов формулы.

Например, чтобы сложить два числа, введите в ячейку «=1+1». Как только вы нажмете , введите , Excel выполнит формулу и вернет результат, равный 2.

Вот несколько примеров распространенных операторов:

Excel автоматически обрабатывает содержимое ячеек, начинающихся с (=), как формулы. Это также применимо, когда вы начинаете ячейку с символов плюс (+) или минус (-) .

Вы можете обойти это, добавив начальный апостроф (‘) . Вот как вы можете отображать формулы в виде текста, как в таблице выше.

Порядок действий и использование скобок в формулах Excel

Как правило, Excel использует PEMDAS при расчете формул. PEMDAS означает сначала круглых скобок, затем показатели степени, затем умножение и деление, затем сложение и вычитание .

Что такое функции?

Это предварительно определенные процессы в Excel. Каждая функция в Excel имеет уникальное имя и конкретные входные данные . Функция принимает эти входные данные и выполняет соответствующие вычисления.

Входы или аргументы функции Excel всегда заключаются в круглые скобки.

Например, это синтаксис для функции MAX :

=MAX(число1, [число2], …)

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

Использование ячейки или диапазона в качестве входных данных

По мере изучения Excel вы обнаружите, что формулы Excel редко состоят из отдельных чисел, как в формуле «=1+1».

Таким образом, ссылки на ячейки важны в Excel, и вы можете узнать больше, нажав здесь.

Хорошо! Вы только что узнали, как работает функция в Excel.

Давайте погрузимся прямо в список! 🤿

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

Основные математические функции (начальный уровень ★☆☆)

1. СУММ

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

Синтаксис: =СУММ(число1, [число2], …)

Попробуйте это в рабочей тетради.

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

1. Введите эту формулу в ячейку F2 :

=СУММ(B2:E2)

Вы также можете ввести «=СУММ(B2,C2,D2,E2)», но «=СУММ(B2: E2)» намного проще.

2. Нажмите Введите . Затем Excel оценивает формулу, и ячейка возвращает общее число, равное 360.

3. Скопируйте это для остальных учащихся или перетащите вниз маркер заполнения .

Обратите внимание, что функция СУММ игнорирует ячейки, содержащие текст . («X» означает, что учащийся не смог пройти тест)

Большинство основных математических функций в Excel игнорируют нечисловые значения , такие как текст, дата и время.

2. СЧЕТ

Далее следует функция СЧЁТ . Он возвращает количество ячеек, содержащих числовые значения в пределах входного диапазона.

Синтаксис : =СЧЕТ(значение1, [значение2], …)

1. Чтобы получить количество тестов, пройденных каждым учащимся, используйте эту формулу в ячейке G2 :

=СЧЕТ(B2:E2 )

2. Нажмите Введите и заполните строки ниже.

Если вы хотите включить в подсчет нечисловые значения, вы можете использовать СЧЕТЧИК функция. Чтобы подсчитать количество пустых ячеек, вы можете использовать функцию СЧИТАТЬПУСТОТЫ .

Узнайте больше о функции COUNT и ее вариантах здесь.

3. СРЕДНЕЕ

Среднее значение списка чисел равно сумме, деленной на количество чисел в этом списке.

Это достаточно просто, чтобы подсчитать баллы викторины. У вас уже есть СУММА и СЧЕТЧИК тестов для каждого учащегося.

Но с 9 становится еще проще0003 Функция СРЗНАЧ в Excel.

Синтаксис : =СРЗНАЧ (значение1, [значение2], …)

1. Введите это в ячейку h3 :

=СРЗНАЧ(B2:E2)

Функция СРЗНАЧ также имеет варианты для более сложных вычислений. Кликните сюда, чтобы узнать больше.

Логические функции (средний уровень — ★★☆)

Немного повысим уровень сложности.

А 9Логическая функция 0003 в Excel позволяет проводить сравнения и использовать результаты для изменения способа вычисления формулы.

4. ЕСЛИ

Функция ЕСЛИ — очень популярная функция в Excel, и ее довольно легко освоить.

Синтаксис : =IF(логическая_проверка, значение_если_истина, [значение_если_ложь])

Эта функция проверяет, является ли логическая проверка ИСТИННОЙ или ЛОЖНОЙ. Затем он возвращает указанное значение на основе результата.

Используя средний балл каждого учащегося, попробуйте поставить оценки «ПРОШЕЛ» или «НЕ ПРОШЕЛ». Предположим, что проходной балл для этого класса равен 60.

1. Начните формулу в ячейке C2 с «=IF(«

оценка 60.

2. Итак, формула принимает вид:

=ЕСЛИ(B2>=60,

Если сравнение возвращает ИСТИНА , то формула должна вернуть текст «ПРОШЕЛ». Таким образом, value_if_true аргумент должен быть «PASS».0005

И если он возвращает FALSE , то аргумент value_if_false должен быть «FAIL».

3. Таким образом, формула принимает вид:

=ЕСЛИ(B2>=60,”ПРОШЕЛ”,”НЕ ПРОШЕЛ”)

4. Нажмите Enter и заполните строки ниже.

Что, если бы вам нужно было выставлять оценки по шкале, а не просто «ПРОШЕЛ» и «НЕ ПРОШЕЛ»?

Для этого необходимо использовать несколько критериев или логических тестов. Хотя это возможно с использованием вложенных функций ЕСЛИ , он может очень быстро запутаться. Вместо этого вы можете использовать функцию IFS .

5. Функция IFS

Функция IFS была введена в Excel 2016 для замены вложенных функций ЕСЛИ .

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

📖 Другими словами, функция IFS выводит значение, соответствующее первому заданному критерию, который является истинным .

Синтаксис : =IFS(логическая_проверка1, значение_если_истина1, [логическая_проверка2], [значение_если_истина2],. .)

1. Сначала формула должна проверить, является ли средний балл (столбец B) больше или равен 90 , Если да, он должен вернуть «А» .

=IFS(B2>=90,”A”,

2. Если нет, то следует проверить, больше ли средний балл или равен 80. Если да, он должен вернуть «Б» . Если вы сделаете это до уровня D, формула станет такой:

=IFS(B2>=90,A”,B2>=80,B”,B2>=70,C”,B2>=60 «D»,

3. Для последней оценки «F» поставьте «ИСТИНА» для логического теста

Функция IFS будет оценивать только последних указанных критериев , если все предыдущие логические значения были FALSE. Таким образом, вы можете установить последних критерия, чтобы они всегда были TRUE , что делает его оператором « catch all ».0005

Окончательная формула будет следующей:

=IFS(B2>=90,A,B2>=80,B,B2>=70,C,B2>=60,D, TRUE, «F»)

PRO-TIP:

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

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

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

=ЕСЛИ(B2>=$H$2,$F$2,B2>=$H$3,$F$3,B2>
=$H$4,$F$4,B2>=$H$5,$ F$5,TRUE,$F$6)

Текстовые функции (средний уровень — ★★☆)

В следующем разделе вы увидите, как можно использовать Excel для работы с текстом.

В рабочем листе «Список классов» рабочей тетради полное имя каждого учащегося указано в столбце A . Ваша цель состоит в том, чтобы переставить их с «имя фамилия» на «фамилия_имя» в столбце F .

Для этого сначала необходимо извлечь имя и фамилию из столбца A.

6. НАЙТИ

Имена разделены пробелом » « . Таким образом, вы должны определить положение пробела в каждой текстовой строке в столбце A.

Функция НАЙТИ в Excel возвращает номер или позицию указанного символа или подстроки в другой текстовой строке.

Синтаксис : =НАЙТИ(найти_текст, внутри_текста, [начальный_номер])

Чтобы получить позицию пробела ” ” , введите эту формулу:

=НАЙТИ(” “,A2)

Далее, взгляните на функцию LEN .

7. LEN

Эта функция возвращает количество символов в текстовой строке.

Синтаксис : =LEN(текст)

Чтобы получить количество символов в имени каждого учащегося:

=LEN(A2)

Теперь можно перейти к извлечению имени и фамилии с помощью функции MID в Excel.

8. MID

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

Синтаксис : = MID(text, start_num, num_chars)

Это одна из трех текстовых функций, которые используются для извлечения текста. Два других — LEFT и RIGHT — извлекают текст из начала и конца текстовой строки соответственно.

Имя начинается с самого первого символа текстовой строки. Итак, вы извлекаете, начиная с позиции 1 . Тогда длина имени задается позицией пробела минус 1 .

Итак, формула для извлечения имени или первого слова из текстовой строки:

=MID(A2,1,B2-1)

Или вы можете выразить ее напрямую, используя формулу НАЙТИ ранее.

=СРЕД(A2,1,НАЙТИ(” “,A2)-1)

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

=СРЕД(A2,B2+1,C2-B2)

Или, используя формулы НАЙТИ и ДЛСТР ранее: А2)+1 ,ДЛСТР(A2)-НАЙТИ(” “,A2))

Теперь вы можете комбинировать фамилию и имя в нужном порядке, используя Функция CONCAT .

9. СЦЕПИТЬ

Подобно IFS , СЦЕПИТЬ — еще одна новая функция в Excel 2016. Она заменила старую функцию СЦЕПИТЬ .

Синтаксис : =CONCAT(текст1, [текст2],…)

Объедините фамилию и имя с запятой и символом пробела «,» между ними.

=CONCAT(E2″, “,D2)

ПРОФЕССИОНАЛЬНЫЙ СОВЕТ:

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

В реальных приложениях вы можете использовать одну длинную формулу для получения таких результатов: “,A2)),”, “,MID(A2,1,FIND(” “,A2)-1))

Функции поиска и поиска (расширенный уровень – ★★★)

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

Взгляните на рабочий лист «Расписание» .

10. СТОЛБЦ

Функция СТОЛБЦ в Excel возвращает номер столбца данной ячейки.

Синтаксис : =СТОЛБЦ([ссылка])

Давайте попробуем назначить конкретные даты для каждого теста. Например, вы можете захотеть, чтобы викторины проводились каждый понедельник. Это означает, что первая дата викторины должна смещаться на 1 неделю или 7 дней для каждой последующей даты викторины.

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

=$B$2+(COLUMN()-2)*7

Два (2) вычитается из номера столбца, так что последовательность начинается с 1.

Вы также можете получить этот результат, используя гораздо более простой «=B2+7» , так как вы добавляете фиксированное количество дней к каждой дате. 🤔

Но, используя функцию СТОЛБЦ , можно создавать сложные узоры.

Возьмем, к примеру, такой шаблон:

Викторины по-прежнему проводятся каждый понедельник. Но каждую третью неделю они проводятся в среду.

Вот формула для этого шаблона:

=$B$2+(COLUMN()-2)*7+IF(MOD(COLUMN()-1,3)=0,2,0)

Функция MOD в Excel возвращает остаток после деления числа на заданный делитель. Это часть Math & Trig группы функций.

Эта группа включает в себя другие забавные функции, такие как ABS , которая возвращает абсолютное значение числа, и ROUND , которая округляет число до указанного количества цифр.

Узнайте больше о функциональных группах в конце этой статьи!

11. ROW

Далее рассмотрим функцию ROW . Он работает точно так же, как COLUMN , но вместо этого возвращает номер строки.

Синтаксис : =СТРОКА([ссылка])

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

Предположим, что R1C1 — это место, ближайшее к столу учителя.

1. Вы можете рассадить студентов одно за другим и в две колонки:

=CONCAT(“R”,MOD(ROW()-6,3)*2+1″,C”,INT( (СТРОКА()-6)/3)*2+1)

2. Или они могут располагаться в строках по 3 и в столбцах по 2

=CONCAT(“R”,MOD(ROW()-6, 2)*2+1″,C”,INT((ROW()-6)/2)*2+1)

3. Вы также можете посадить их в самые дальние ряды:

=CONCAT(” R”, MOD(СТРОКА()-6,2)*4+1″,C”,INT((СТРОКА()-6)/2)*2+1)

4. Или в самых дальних столбцах:

=CONCAT(“R”,MOD(ROW()-6,3)*2+1″,C”,INT((ROW()-6)/ 3)*4+1)

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

Функции COLUMN и ROW редко используются сами по себе. Как и IF и IFS , вы используете их с другими функциями, чтобы изменить способ вычисления формулы.

12. ПОИСКПОЗ

Теперь откройте рабочий лист «Поиск» .

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

Для начала вы будете использовать функцию ПОИСКПОЗ . Он ищет указанный элемент в заданном диапазоне ячеек. Затем он возвращает относительную позицию первого совпадения.

Синтаксис : =ПОИСКПОЗ(искомое_значение, искомое_массив, [тип_совпадения])

1. искомое_значение — это элемент, который вы хотите найти. Итак, установите это на ячейку B2 .
2. lookup_array — это диапазон или массив таблиц, в которых вы хотите выполнить поиск. Используйте F2:F7 из рабочего листа «Список классов» .
3. Для match_type установите это значение равным нулю, чтобы функция искала точное совпадение . (Узнайте больше о MATCH и различных типах в этой статье)

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

Чтобы исправить это, вы можете использовать подстановочный знак звездочки «*» , чтобы работал поиск по имени или фамилии.

Формулу также можно заключить в функцию IFNA . Таким образом, если формула не может найти данное имя в таблице, она вернет фразу типа «Результат не найден».

13. ИНДЕКС

Функция ИНДЕКС извлекает значение из заданного массива таблиц на основе предоставленных номеров строк и столбцов.

Синтаксис : =ИНДЕКС (массив, номер_строки, [номер_столбца])

Подобно примеру ПОИСКПОЗ , вам необходимо указать, где находится поиск диапазона или массива.

Для row_num можно использовать более ранний результат MATCH в Cell B5 . Затем для col_num используйте 1 для Имя :

=INDEX(‘Список классов’!D2:E7,B5,1)

И установите col_num на 2 для 9 0003 Фамилия .

=ИНДЕКС(‘Список классов’!D2:E7,B5,2)

Вот так у вас есть рабочая формула поиска 🔍!

Это всего лишь небольшой пример бесчисленных возможностей использования ИНДЕКС и СОВПАДЕНИЕ комбинация . Нажмите здесь, чтобы увидеть больше примеров!

14. ВПР

Функция ВПР в Excel работает аналогично комбинации ИНДЕКС и ПОИСКПОЗ . Он быстрее настраивается, но менее универсален. VLOOKUP также работает, только если ваш массив поиска находится в самом левом углу справочной таблицы.

Синтаксис : = ВПР (искомое_значение, табличный_массив, индексный_номер столбца, [диапазон_просмотра])

На этот раз вы будете использовать результат «Имя» (ячейка B6) в качестве lookup_value . Используйте это и VLOOKUP , чтобы получить баллы данного учащегося из рабочего листа «Оценки викторины» .

=ВПР($B$6,’Оценки викторины’!$A$2:$E$7,СТОЛБЦ(),ЛОЖЬ)

Для назначения места используйте результат Фамилия, за которым следует подстановочный символ звездочки.

=ВПР($B$7&”*”,Расписание!$A$6:$E$11,СТОЛБЦ(),ЛОЖЬ)

15. ДВССЫЛ

Последняя функция, о которой вы узнаете сегодня, также является одной из самых мощных в Excel.

ДВССЫЛ позволяет указывать ссылки на ячейки с помощью текстовых строк.

СИНТАКСИС : =ДВССЫЛ(ref_text, [a1])

Например, вместо ввода «=A1» вы можете ввести «=ДВССЫЛ(«A»&1) . Это означает, что вы можете динамически изменять ссылки.

Возьмем ИНДЕКС И СООТВЕТСТВУЙТЕ 9Формула 0004, которую вы использовали для получения фамилии. Вы можете получить тот же результат, используя эту формулу:

=ДВССЫЛ(“‘Список классов’!”&”E”&(B5+1))

Функция ДВССЫЛ открывает так много возможностей с динамическими ссылками в Экселе. Я высоко ценю эту статью как подробный учебник по INDIRECT.

Вот и все – Что теперь?

Как вы только что узнали, Excel предлагает множество различных функций на выбор. К счастью, Excel объединил их всех в Вкладка “Формулы” .

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

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

Вы также можете нажать Дополнительные функции что открывает еще больше возможностей для расширенных формул Excel.

Например, группа Statistical полезна, если вам нужно вычислить статистическое значение. Сюда входят функции для максимального значения, минимального значения, значения прогноза, значения гамма-функции и т. д. Вы можете вставить кумулятивную функцию распределения и другие полезных инструмента для анализа данных .

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

Мы поможем вам максимально эффективно использовать Excel! 📈

Другие соответствующие ресурсы

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

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

Спасибо за внимание! 😄