Какие формулы функций Excel обычно используются? На рабочем месте Excel чаще всего используется для статистического анализа данных. Если вы не овладеете определенными навыками, вы обязательно обратитесь к другим за помощью в офисном процессе. , редактор предоставит вам 43 формулы функций Excel, которые помогут вам перестать просить о помощи на рабочем месте.
Это функциональный модуль, предопределенный в Excel и выполняющий вычисления, анализ и другие задачи по обработке данных в определенном порядке и структуре. Поэтому функции называются «специальными формулами». Как и в случае с формулами, конечный результат функции Excel — это значение. Функция имеет уникальное имя, не чувствительное к регистру и определяющее ее функцию и назначение.
Приведу простой пример - как при обработке таблицы изменить первую букву имени на всю прописную?
Если вы не понимаете функции, вы вручную изменяете их одну за другой? Если вы знаете правильную функцию, вы не будете изменять ее одну за другой. Введите формулу функции и сделайте это за 3 секунды!
Формулы — это расчеты, разработанные пользователями и объединенные с постоянными данными, ссылками на ячейки, операторами и другими элементами для обработки и расчета данных. Пользователи используют формулы для расчета результатов с определенной целью, поэтому формулы Excel должны (и могут только) возвращать значения.
Структура формулы: =(C2+D2)*5 С точки зрения структуры формулы элементы, составляющие формулу, обычно включают такие элементы, как знаки равенства, константы, ссылки и операторы. Среди них незаменим знак =. Но в реальных приложениях с формулами также можно работать, используя массивы, функции Excel или имена (именованные формулы).
Обычно Excel выполняет операции с формулами в порядке слева направо. Если в формуле используется несколько операторов, Excel выполняет операции в соответствии с приоритетом каждого оператора. Для операторов одного уровня операции выполняются слева направо. И последовательная операция справа. Конкретный порядок приоритетов следующий:
При использовании формул Excel для расчетов по каким-либо причинам может быть не получен правильный результат и возвращается значение ошибки. Распространенные значения ошибок и их значения приведены в таблице ниже.
Если результат формулы возвращает значение ошибки, необходимо оперативно найти причину ошибки и изменить формулу для решения проблемы.
Функции Excel обычно состоят из имени функции, левой скобки, параметров, полуширинной запятой и правой скобки.
Структура формулы функции: =ЕСЛИ(A1>0,"положительное число", ЕСЛИ(A1<0,отрицательное число,"")) Параметры функции могут состоять из таких элементов, как числовые значения, даты и Можно использовать текст или константы, массив, ссылку на ячейку или другую функцию.
Если параметры функции также являются функциями, Excel называет это вложенностью функций. Всего существует 11 типов функций, включая функции базы данных, функции даты и времени, инженерные функции, финансовые функции, информационные функции, логические функции, функции запроса и ссылки, математические и тригонометрические функции, статистические функции, текстовые функции и определяемые пользователем функции. .
Содержание этой статьи представлено в формате каталога, в котором рассказывается, что делает каждая функция, какую функцию можно использовать для решения определенной проблемы и т. д. Конкретный метод использования можно узнать на Baidu.
Для функций не нужно запоминать их наизусть. Достаточно лишь знать, какой тип функции следует выбрать, какие параметры необходимы и как их использовать! Например, выбирайте поля, используйте функции ВЛЕВО/ВПРАВО/СРЕДНЕ... Остальные детали оставьте всемогущему Baidu!
Ниже приводится классификация и описание этих часто используемых необходимых функций в соответствии с различными сценариями применения.
1. Класс соответствия ассоциации
Требуемые данные находятся не в одном листе Excel, либо в одном листе Excel на разных листах. Слишком много данных сложно копировать, и они подвержены ошибкам. Как их интегрировать? Следующие функции используются для ассоциации нескольких таблиц или сравнения строк. Чем сложнее таблица, тем интереснее ее использовать!
01.ВПР
Функция: используется для поиска элементов в первом столбце, соответствующих условиям.
Синтаксис: =VLOOKUP (искомое_значение,табличный_массив, номер_индекса_столбца, [диапазон_просмотра])
*Примечания: [ ] — необязательные параметры, остальные — обязательные параметры, то же самое применимо и ниже. =ВПР (элемент для поиска, местоположение для поиска, номер столбца в диапазоне, содержащем возвращаемое значение, возвращает приблизительное или точное совпадение — указывается как 1/ИСТИНА или 0/ЛОЖЬ). Пример. Запросите должность сотрудника, имя которого указано в ячейке F5.
02.Просмотр
Функция: поиск значения в первой строке таблицы или числового массива, а затем возврат значения в столбце указанной строки таблицы или массива. H в HLOOKUP означает «линия».
Синтаксис: =HLOOKUP(искомое_значение,табличный_массив, номер_индекса_строки, [диапазон_просмотра])
Пример: =HLOOKUP("Axle",A1:C4, 2, TRUE) находит ось в первой строке и возвращает значение во второй строке в том же столбце (столбец A).
Разница между ПРОСМОТРОМ и HLOOKUP: Когда значение сравнения находится в первой строке таблицы данных, если вы хотите просмотреть указанное количество строк вниз, вы можете использовать HLOOKUP. ВПР можно использовать, когда значение сравнения находится в левом столбце искомых данных.
03.ИНДЕКС
Функция: возвращает значение или ссылку на значение в таблице или диапазоне.
Синтаксис: =ИНДЕКС(массив,номер_строки, [номер_столбца])
Пример: =INDEX(B2:D11,3,3) — значение, расположенное на пересечении третьей строки и третьего столбца в диапазоне A2:B3.
04.МАТЧ
Функция: используется для возврата позиции указанного содержимого в указанной области (определенной строке или столбце).
Синтаксис: =MATCH(искомое_значение,искомый_массив, [тип_соответствия])
Пример: =MATCH(41,B2:B5,0) Положение значения 41 в диапазоне ячеек B2:B5.
тип_соответствия:
1 или опущено: ПОИСКПОЗ находит максимальное значение, меньшее или равное искомому_значению.
0: ПОИСКПОЗ, чтобы найти первое значение, которое точно равно искомому_значению.
-1: ПОИСКПОЗ, чтобы найти наименьшее значение, большее или равное искомому_значению.
05.РАНГ
Функция: найти рейтинг определенной ценности среди группы ценностей в определенной области.
Синтаксис: =RANK(число,ссылка,[порядок])
Пример: =RANK(A3,A2:A6,1) Метод ранжирования A3 в A2:A6 в приведенной выше таблице: 0 — по убыванию, 1 — по возрастанию, значение по умолчанию — 0.
06.СТРОКА
Функция: Возвращает указанный номер строки.
Синтаксис: = СТРОКА([ссылка])
Пример: = ROW() Номер строки, в которой находится формула.
07. КОЛОНКА
Функция: Возвращает столбец, в котором находится ячейка.
Синтаксис=СТОЛБЕЦ(ссылка)
Пример: =COLUMN (D10) возвращает 4, поскольку столбец D является четвертым столбцом.
08.СМЕЩЕНИЕ
Функция: возвращает ссылку на ячейку или диапазон ячеек с указанным количеством строк и столбцов. Возвращенная ссылка может быть одной ячейкой или диапазоном ячеек. Вы можете указать количество возвращаемых строк и столбцов.
Синтаксис: =OFFSET(ссылка, строки, столбцы,[высота], [ширина])
Пример: =OFFSET(D3,3,-2,1,1) отображает значение в ячейке B6, где 3 — это три строки ниже, -2 — две строки слева, а 1 — высота строки и ширина столбца. .
Перед обработкой данных необходимо первоначально очистить извлеченные данные, например очистить строковые пространства, объединить ячейки, заменить, перехватить строки и найти место, где появляются строки.
Строка перехвата: используйте MID/LEFT/RIGHT
Замените содержимое ячейки: ПОДСТАВИТЬ/ЗАМЕНИТЬ.
Объединить ячейки: используйте CONCATENATE
Очистите строковые пространства: используйте TRIM/LTRIM/RTRIM.
Найдите расположение текста в ячейке: НАЙТИ/ПОИСК.
09.СРЕДНИЙ
Функция: перехватить строку из середины
Синтаксис: =MID(текст,начальный_номер, число_символов)
Пример: =MID(A2,1,5) Начиная с первого символа строки в A2, возвращается 5 символов.
Извлеките год и месяц на основе идентификационного номера.
10.ЛЕВО
Функция: перехватить строку слева.
Синтаксис: =LEFT(текст,[число_символов])
Пример: =LEFT(A2,4) Первые четыре символа первой строки.
11.ПРАВО
Функция: перехватить строку справа.
Синтаксис: =ПРАВО(текст,[число_символов])
Пример: =RIGHT(A2,5) последние 5 символов первой строки.
12.ЗАМЕНА
Функция: заменить старый_текст на новый_текст в текстовой строке.
Синтаксис: =SUBSTITUTE(текст,старый_текст, новый_текст, [номер_экземпляра])
Пример: =SUBSTITUTE(A2, «Продажи», «Стоимость») заменяет «Продажи» на «Стоимость» (данные о затратах) и заменяет часть номера телефона.
13.ЗАМЕНА
Функция: Заменить строку в ячейке.
Синтаксис: =REPLACE(old_text,start_num, num_chars, new_text)
Пример: =REPLACE(A2,6,5,"*") В A2, начиная с шестого символа (f), замените пять символов одним символом *.
Разница между REPLACE и SUBSTITUTE: эти две функции очень близки. Разница в том, что REPLACE реализует замену в зависимости от позиции и должна обеспечивать замену, начиная с какой позиции, количества замен и нового текста после замены. ЗАМЕНИТЬ заменяет в соответствии с текстовым содержимым и требует замены старого текста и нового текста, а также того, какой старый текст заменяется и т. д. Таким образом, REPLACE реализует замену текста в фиксированной позиции, а SUBSTITUTE реализует замену текста в фиксированной позиции.
14. ОБЪЕДИНИТЬ
Функция: объединить две или более текстовые строки в одну строку.
Синтаксис: =СЦЕПИТЬ(текст1,[текст2], ...)
Другой способ объединить содержимое ячеек — &. Когда необходимо объединить слишком много содержимого, более эффективным является CONCATENATE.
Пример: =CONCATENATE(B2, " ", C2) объединяет три части: строку в ячейке B2, символ пробела и значение в ячейке C2.
15. ОБРЕЗКА
Функция: Удаляет все пробелы в тексте, кроме одиночных пробелов между словами.
Синтаксис: =TRIM(текст)
Text — текст для удаления пробелов.
Пример: =TRIM("Прибыль за первый квартал") удаляет начальные и конечные пробелы из текста формулы.
16.ЛТРИМ
Функция: удаление пробелов или других предопределенных символов из левой части строки.
Синтаксис: =LTRIM (строка, [список символов])
17.РТРИМ
Функция: удаление пробелов или других предопределенных символов с правой стороны строки.
Синтаксис: = LTRIM(строка, [список символов])
18.НАЙТИ
Функция: найти положение текста
Синтаксис: =НАЙТИ(найти_текст,внутри_текста, [начальный_номер])
Пример: =FIND("M",A2) Позиция первой буквы "M" в ячейке A2.
19.ПОИСК
Функция: Возвращает позицию, где указанный символ или текстовая строка впервые появляется в строке, поиск слева направо.
Синтаксис: =ПОИСК(найти_текст,внутри_текста,[начальный_номер])
Пример: =SEARCH("e",A2,6) В строке в ячейке A2, начиная с 6-й позиции, позиция первой "e".
Разница между НАЙТИ и ПОИСК: Функции этих двух функций почти одинаковы, и они могут находить расположение символов. Разница в том, что функция НАЙТИ выполняет поиск точно и учитывает регистр; функция ПОИСК ищет нечетко и не является. с учетом регистра.
20.ЛЕН
Функция: Возвращает количество символов в текстовой строке.
Синтаксис: =LEN(текст)
Пример: =LEN(A1) Длина строки в ячейке A1.
21.ЛЕНБ
Функция: Возвращает количество байтов, используемых для представления символов в текстовой строке.
Синтаксис: =LENB(текст)
Пример: =LEN(A1)Количество байтов в строке ячеек A1.
Логика, как следует из названия, не вдается в детали, просто переходит к функции.
22. ЕСЛИ
Функция: при использовании функции логической функции ЕСЛИ, если условие истинно, функция вернет значение; если условие ложно, функция вернет другое значение;
Синтаксис: =ЕСЛИ(Логическое,Значение_если_истина,Значение_если_ложь)
Функция ЕСЛИ возвращает значение, если указанное условие оценивается как истинное, и другое значение, если условие оценивается как ложное.
23.СЧЁТЕСЛИ
Функция: используется для подсчета количества ячеек, соответствующих определенному условию, например, для подсчета количества раз, когда определенный город появляется в списке клиентов;
Синтаксис: =СЧЁТЕСЛИ(ячейка 1: ячейка 2, условие)
Подсчитайте, сколько раз конкретный магазин появляется в списке.
24.И
Функция: логическое суждение, эквивалентное «союзу».
Синтаксис: Если все параметры имеют значение True, будет возвращено значение True. Это часто используется для оценки нескольких условий.
Пример: =И(A2>1,A2<100) Если A2 больше 1 и меньше 100, отображается ИСТИНА, в противном случае отображается ЛОЖЬ;
25.ИЛИ
Функция: логическое суждение, эквивалентное «или».
Синтаксис: Пока параметр имеет значение True, будет возвращено значение Ture, которое часто используется для оценки нескольких условий.
Пример: =ИЛИ(A2>1,A2<100). Если A2 больше 1 или меньше 100, отображается ИСТИНА, в противном случае отображается ЛОЖЬ.
При использовании статистики таблиц Excel часто необходимо использовать различные формулы, поставляемые с Excel, которые также являются наиболее часто используемым типом. (Для этого в Excel есть ярлыки)
26.МИН
Функция: Найдите минимальное значение в определенной области.
Синтаксис: =MIN(число1, [число2], ...)
Пример: =MIN(D2:D11) Минимальное число в диапазоне D2:D11.
27.МАКС.
Функция: Найдите максимальное значение в определенной области.
Синтаксис: =MAX(число1, [число2], ...)
Пример: =MAX(A2:A6) Максимальное значение в области A2:A6.
28. СРЕДНИЙ
Функция: рассчитать среднее значение в определенной области.
Синтаксис: =СРЗНАЧ(число1, [число2], ...)
Пример: =СРЗНАЧ(D2:D11) Среднее число чисел в диапазоне ячеек от D2 до D11.
29.СЧЕТ
Функция: Подсчитайте количество ячеек, содержащих числа.
Синтаксис: =COUNT(значение1, [значение2], ...)
Пример: =COUNT(A2:A7) Подсчитывает количество ячеек, содержащих числа в диапазоне ячеек от A2 до A7.
30.СЧЁТ
Функция: подсчитать количество ячеек, заданных заданным набором условий.
Синтаксис: COUNTIFS(диапазон_критерия1,критерий1, [диапазон_критерия2, критерий2],…)
Пример: =COUNTIFS(A2:A7,"<6",A2:A7,">1") вычисляет, сколько чисел от 1 до 6 (исключая 1 и 6) содержится в ячейках от A2 до A7.
31.СУММ
Функция: вычислить сумму всех значений в диапазоне ячеек.
Синтаксис: =СУММ(ячейка 1:ячейка 2)
Пример: =SUM(A2:A10) складывает значения в ячейках A2:10.
32.СУММЕСЛИ
Функция: Найдите сумму ячеек, удовлетворяющих условиям.
Синтаксис: =СУММЕСЛИ(диапазон,критерий, [диапазон_суммы])
Пример: =СУММЕСЛИ(A2:A7,"Фрукты",C2:C7) Сумма продаж всех продуктов питания в категории "Фрукты".
32.СУММИФС
Функция: просуммировать группу ячеек, соответствующих указанным условиям.
Синтаксис: =СУММИФС(диапазон_суммы,диапазон_критерия1, критерий1, [диапазон_критерия2, критерий2], ...)
Пример: =СУММИФС(A2:A9, B2:B9, «=香*», C2:C9, «LUNING») Подсчитайте общее количество продуктов, начинающихся с «香», проданных компанией «LUNING».
33.СУММПРОИЗВЕД
Функция: Возвращает сумму соответствующего произведения массива или площади.
Синтаксис: =СУММПРОИЗВ (массив1, [массив2], [массив3], ...)
Пример: =СУММПРОИЗВ(Таблица1!A1:Таблица1!A100,Таблица2!B1:Таблица2!B100) Вычислить сумму произведений от A1 до A100 таблицы 1 и от B1 до B100 таблицы 2, то есть A1*B1+A2. *Б2+А3* Б3+…
34.СТАНДОТКЛОН
Функция: Оцените стандартное отклонение на основе выборки.
Синтаксис: STDEV(число1,[число2],...)
Пример: =STDEV(D2:D17) стандартное отклонение столбца.
35. ПРОМЕЖУТОЧНЫЙ ИТОГО
Функция: Возвращает промежуточный итог в списке или базе данных.
Синтаксис: =СУБТИТОГ(номер_функции,ссылка1,[ссылка2],...)
Пример: =SUBTOTAL(9,A2:A5) использует 9 в качестве первого параметра для вычисления суммы значений промежуточных итогов в ячейках A2:A5.
http://36.INT／РАУНД
Функция: Функция ОКРУГЛ округляет число до указанного количества цифр.
Синтаксис: = ОКРУГЛ (A1, 2)
Пример: =ROUND(2.15, 1) округляет 2,15 до одного десятичного знака.
Функция: INT округляет число до ближайшего целого числа.
Синтаксис:=INT(8.9) Округляет 8,9 до ближайшего целого числа.
Специально разработан для обработки форматов времени и преобразований.
37.СЕГОДНЯ
Функция: Возвращает серийный номер текущей даты.
Синтаксис: =СЕГОДНЯ()
li'z=TODAY()+5 возвращает текущую дату плюс 5 дней. Например, если текущая дата — 01.01.2012, эта формула возвращает 06.01.2012.
38.СЕЙЧАС
Функция: Возвращает серийный номер текущей даты и времени.
Синтаксис: =Сейчас()
=NOW()+7 возвращает дату и время через 7 дней.
39.ГОД
Функция: Возвращает год, соответствующий определенной дате.
Синтаксис: =ГОД(серийный_номер)
=ГОД(A3) Год даты в ячейке A3.
40.МЕСЯЦ
Функция: Возвращает месяц в дате.
Синтаксис: =МЕСЯЦ(серийный_номер)
=МЕСЯЦ(A2) Месяц даты в ячейке A2.
41.ДЕНЬ
Функция: Возвращает количество дней в дате, выраженное в виде серийного номера.
Синтаксис: =ДЕНЬ(серийный_номер)
=ДЕНЬ(A2) Количество дней в дате в ячейке A2.
42.БЕДНЫЙ ДЕНЬ
Функция: Возвращает день недели, соответствующий определенной дате. По умолчанию количество дней представляет собой целое число в диапазоне от 1 (воскресенье) до 7 (суббота).
Синтаксис: =ДЕНЬНЕД(серийный_номер,[тип_возврата])
=ДЕНЬНЕД(A2) День недели от 1 (воскресенье) до 7 (суббота).
=ДЕНЬНЕД(A2, 2) День недели от 1 (понедельник) до 7 (воскресенье).
43.ДАТАДИФ
Функция: рассчитать количество дней, месяцев или лет между двумя датами.
Синтаксис: =DATEDIF(начальная_дата,конечная_дата,единица измерения)
=DATEDIF(Start_date,End_date,"Y")Количество лет в периоде.
=DATEDIF(Start_date,End_date,"D")Количество дней в периоде.
=DATEDIF(Start_date,End_date,"YD") игнорирует год в дате и количество дней в периоде.
Прикрепил:
