일반적으로 사용되는 Excel 함수 공식은 무엇입니까? 직장에서 Excel은 데이터의 통계 분석에 가장 일반적으로 사용됩니다. 일정량의 기술을 익히지 않으면 오늘 사무실 프로세스 중에 다른 사람에게 도움을 요청할 것입니다. , 편집자는 직장에서 도움을 요청하는 것을 중단하는 데 도움이 되는 43개의 Excel 함수 수식을 제공합니다.
엑셀 내에 미리 정의되어 특정 순서와 구조에 따라 계산, 분석, 기타 데이터 처리 작업을 수행하는 기능 모듈입니다. 따라서 함수를 "특수 수식"이라고 합니다. 수식과 마찬가지로 Excel 함수의 최종 반환 결과는 값입니다. 함수는 대소문자를 구분하지 않고 함수와 목적을 결정하는 고유한 이름을 갖습니다.
간단한 예를 들자면, 테이블을 처리할 때 이름의 첫 글자를 모두 대문자로 변경하는 방법은 무엇입니까?
기능이 이해가 안되면 수동으로 하나씩 수정하시나요? 적절한 기능을 알고 있다면 하나씩 수정하지 않을 것입니다. 함수식을 입력하고 3초만에 끝내세요!
수식은 사용자가 설계한 계산이며 데이터 처리 및 계산을 위해 상수 데이터, 셀 참조, 연산자 및 기타 요소와 결합됩니다. 사용자는 수식을 사용하여 목적에 따라 결과를 계산하므로 Excel의 수식은 값을 반환해야 하며 반환할 수만 있습니다.
수식의 구조: =(C2+D2)*5 수식 구조의 관점에서 수식을 구성하는 요소에는 일반적으로 등호, 상수, 참조 및 연산자와 같은 요소가 포함됩니다. 그 중 = 기호는 필수입니다. 그러나 실제 응용 프로그램에서는 배열, Excel 함수 또는 이름(명명된 수식)을 사용하여 수식을 연산할 수도 있습니다.
일반적으로 Excel에서는 수식 연산을 왼쪽에서 오른쪽으로 수행합니다. 여러 연산자가 수식에 사용되는 경우 Excel은 각 연산자의 우선 순위에 따라 연산을 수행합니다. 그리고 오른쪽은 순차 작업입니다. 구체적인 우선순위는 다음과 같습니다.
Excel 수식을 사용하여 계산할 때 어떤 이유로 인해 올바른 결과를 얻지 못하고 오류 값이 반환될 수 있습니다. 일반적인 오류 값과 그 의미는 아래 표에 나와 있습니다.
수식의 결과가 오류 값을 반환하는 경우 오류의 원인을 즉시 찾아내고 수식을 수정하여 문제를 해결해야 합니다.
Excel 함수는 일반적으로 함수 이름, 왼쪽 괄호, 매개변수, 반각 쉼표, 오른쪽 괄호로 구성됩니다.
함수 수식 구조: =IF(A1>0,"양수", IF(A1<0,음수,"")) 함수의 매개변수는 숫자 값, 날짜 및 기타 요소로 구성될 수 있습니다. 텍스트 또는 상수, 배열, 셀 참조 또는 기타 기능을 사용할 수 있습니다.
함수의 매개 변수도 함수인 경우 Excel에서는 이를 함수 중첩이라고 합니다. 데이터베이스 함수, 날짜 및 시간 함수, 공학 함수, 금융 함수, 정보 함수, 논리 함수, 쿼리 및 참조 함수, 수학 및 삼각 함수, 통계 함수, 텍스트 함수, 사용자 정의 함수 등 총 11가지 유형의 함수가 있습니다. .
이 기사의 내용은 카탈로그 형식으로 각 기능이 수행하는 작업, 특정 문제를 해결하는 데 어떤 기능을 사용할 수 있는지 등을 소개합니다. 구체적인 사용 방법은 Baidu에서 알아볼 수 있습니다.
기능은 암기할 필요 없이 어떤 기능을 선택해야 하는지, 어떤 파라미터가 필요한지, 어떻게 사용하는지만 알면 됩니다! 예를 들어 필드 선택, LEFT/RIGHT/MID 기능 사용 등 기타 세부 사항은 전능하신 Baidu에 맡겨주세요!
다음은 다양한 응용 시나리오에 따라 일반적으로 사용되는 필수 기능을 분류하고 소개합니다.
1. 협회 매칭 수업
필요한 데이터가 동일한 엑셀 시트에 없거나, 동일한 엑셀 시트에 서로 다른 시트가 있습니다. 데이터가 너무 많으면 복사가 번거롭고 오류가 발생하기 쉽습니다. 다중 테이블 연관 또는 행-행 비교에는 다음 함수가 사용됩니다. 테이블이 복잡할수록 사용하는 것이 더 재미있습니다!
01.VLOOKUP
기능: 조건에 맞는 첫 번째 열의 요소를 찾는 데 사용됩니다.
구문: =VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])
*비고: [ ]는 선택 매개변수이고, 나머지는 필수 매개변수이며, 아래에도 동일하게 적용됩니다. =VLOOKUP(찾을 항목, 찾을 위치, 반환할 값이 포함된 범위의 열 번호, 대략적인 일치 또는 정확한 일치 반환 - 1/TRUE 또는 0/FALSE로 표시). 예: 셀 F5에 이름이 있는 직원의 직위를 쿼리합니다.
02.HLOOKUP
기능: 테이블이나 숫자 배열의 첫 번째 행에서 값을 검색한 다음 테이블이나 배열에서 지정된 행의 열에 있는 값을 반환합니다. HLOOKUP의 H는 "라인"을 의미합니다.
구문: =HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup])
예: =HLOOKUP("Axle",A1:C4, 2, TRUE)는 첫 번째 행에서 축을 찾고 동일한 열(열 A)의 2행에 있는 값을 반환합니다.
LOOKUP과 HLOOKUP의 차이점: 비교값이 데이터 테이블의 첫 번째 행에 있을 때, 아래쪽으로 지정된 개수만큼 조회하고 싶다면 HLOOKUP을 사용하면 됩니다. VLOOKUP은 찾고자 하는 데이터의 왼쪽 열에 비교값이 있을 때 사용할 수 있습니다.
03.인덱스
함수: 테이블이나 범위의 값 또는 값에 대한 참조를 반환합니다.
구문: =INDEX(배열,행_번호, [열_번호])
예: =INDEX(B2:D11,3,3)은 A2:B3 범위에서 세 번째 행과 세 번째 열의 교차점에 있는 값입니다.
04.매치
기능: 지정된 영역(특정 행 또는 열)에서 지정된 내용의 위치를 반환하는 데 사용됩니다.
구문: =MATCH(조회_값,조회_배열, [일치_유형])
예: =MATCH(41,B2:B5,0) 셀 범위 B2:B5에서 값 41의 위치입니다.
match_type：
1 또는 생략됨: MATCH는 lookup_value보다 작거나 같은 최대값을 찾습니다.
0: MATCH는 lookup_value와 정확히 동일한 첫 번째 값을 찾습니다.
-1: MATCH는 lookup_value보다 크거나 같은 가장 작은 값을 찾습니다.
05.랭킹
기능: 특정 지역의 값 그룹 중에서 특정 값의 순위를 찾습니다.
구문: =RANK(번호,참조,[순서])
예: =RANK(A3,A2:A6,1) 위 표의 A2:A6에서 A3의 순위 방법: 0은 내림차순, 1은 오름차순, 기본값은 0입니다.
06.행
기능: 참조된 줄 번호를 반환합니다.
구문: = ROW([참조])
예: = ROW() 수식이 위치한 행의 행 번호
07.칼럼
기능: 셀이 위치한 열을 반환합니다.
구문=COLUMN(참조)
예: =COLUMN (D10)은 열 D가 네 번째 열이므로 4를 반환합니다.
08.오프셋
함수: 지정된 수의 행과 열이 있는 셀 또는 셀 범위에 대한 참조를 반환합니다. 반환된 참조는 단일 셀이거나 셀 범위일 수 있습니다. 반환할 행과 열의 수를 지정할 수 있습니다.
구문: =OFFSET(참조, 행, 열,[높이], [너비])
예: =OFFSET(D3,3,-2,1,1)은 셀 B6의 값을 표시합니다. 여기서 3은 아래 세 행, -2는 왼쪽 두 행, 1은 행 높이와 열 너비입니다. .
데이터를 처리하기 전에 문자열 공백 지우기, 셀 병합, 바꾸기, 문자열 가로채기, 문자열이 나타나는 위치 찾기 등 추출된 데이터를 초기에 정리해야 합니다.
가로채기 문자열: MID /LEFT/ RIGHT 사용
셀 내용 바꾸기: SUBSTITUTE /REPLACE
셀 병합: CONCATENATE 사용
문자열 공백 지우기: TRIM/LTRIM/RTRIM 사용
셀에서 텍스트 위치 찾기: FIND/SEARCH
09.MID
기능: 문자열을 중간에서 가로채기
구문: =MID(텍스트,start_num, num_chars)
예: =MID(A2,1,5) A2 문자열의 첫 번째 문자부터 시작하여 5자가 반환됩니다.
ID번호를 기준으로 연도와 월을 추출합니다.
10.왼쪽
기능: 왼쪽에서 문자열을 가로챕니다.
구문: =LEFT(텍스트,[숫자_문자])
예: =LEFT(A2,4) 첫 번째 문자열의 처음 4개 문자입니다.
11.오른쪽
기능: 오른쪽에서 문자열을 가로챕니다.
구문: =RIGHT(텍스트,[숫자_문자])
예: =RIGHT(A2,5) 첫 번째 문자열의 마지막 5자
12.교체
기능: 텍스트 문자열에서 old_text를 new_text로 대체합니다.
구문: =SUBSTITUTE(text,old_text, new_text, [instance_num])
예: =SUBSTITUTE(A2, "Sales", "Cost")는 "Sales"를 "Cost"(비용 데이터)로 바꾸고 전화번호의 일부를 바꿉니다.
13.교체
기능: 셀의 문자열을 바꿉니다.
구문: =REPLACE(old_text,start_num, num_chars, new_text)
예: =REPLACE(A2,6,5,"*") A2에서 6번째 문자(f)부터 5개 문자를 단일 문자 *로 바꿉니다.
REPLACE와 SUBSTITUTE의 차이점: 두 함수는 매우 유사합니다. 차이점은 REPLACE가 위치에 따라 교체를 구현하고 교체 후 어떤 위치에서 시작하는지, 교체 횟수 및 새 텍스트를 제공해야 한다는 것입니다. SUBSTITUTE는 텍스트 내용에 따라 대체되며 이전 텍스트와 새 텍스트, 대체되는 이전 텍스트 등을 제공해야 합니다. 따라서 REPLACE는 고정 위치 텍스트 바꾸기를 구현하고 SUBSTITUTE는 고정 위치 텍스트 바꾸기를 구현합니다.
14. 연결
기능: 두 개 이상의 텍스트 문자열을 하나의 문자열로 연결합니다.
구문: =CONCATENATE(텍스트1,[텍스트2], ...)
셀의 내용을 병합하는 또 다른 방법은 &입니다. 병합할 내용이 너무 많으면 CONCATENATE가 더 효율적입니다.
예: =CONCATENATE(B2, " ", C2)는 세 부분, 즉 셀 B2의 문자열, 공백 문자 및 셀 C2의 값을 결합합니다.
15.트림
기능: 단어 사이의 단일 공백을 제외하고 텍스트에서 모든 공백을 제거합니다.
구문: =TRIM(텍스트)
텍스트는 공백을 제거하는 텍스트입니다.
예: =TRIM("First Quarter Earnings ")는 수식 텍스트에서 앞뒤 공백을 제거합니다.
16. L트림
기능: 문자열 왼쪽에서 공백이나 기타 미리 정의된 문자를 제거합니다.
구문: =LTRIM(문자열, [문자 목록])
17.R트림
기능: 문자열 오른쪽에서 공백이나 기타 미리 정의된 문자를 제거합니다.
구문: = LTRIM(문자열, [문자 목록])
18.찾기
기능: 텍스트 위치 찾기
구문: =FIND(find_text,within_text, [start_num])
예: =FIND("M",A2) 셀 A2에서 첫 번째 "M"의 위치
19.검색
기능: 왼쪽에서 오른쪽으로 검색하여 지정된 문자 또는 텍스트 문자열이 문자열에서 처음 나타나는 위치를 반환합니다.
구문: =SEARCH(find_text,within_text,[start_num])
예: =SEARCH("e",A2,6) 셀 A2의 문자열에서 6번째 위치부터 시작하여 첫 번째 "e" 위치입니다.
FIND와 SEARCH의 차이점: 이 두 기능의 기능은 거의 동일하며 문자의 위치를 찾을 수 있습니다. 차이점은 FIND 기능은 정확하게 검색하고 SEARCH 기능은 대소문자를 구분하지 않고 검색한다는 것입니다. 대소문자를 구분합니다.
20.렌
기능: 텍스트 문자열의 문자 수를 반환합니다.
구문: =LEN(텍스트)
예: =LEN(A1) 셀 A1의 문자열 길이
21.LENB
기능: 텍스트 문자열의 문자를 나타내는 데 사용되는 바이트 수를 반환합니다.
구문: =LENB(텍스트)
예: =LEN(A1)셀 문자열 A1의 바이트 수입니다.
이름에서 알 수 있듯이 논리는 세부 사항을 다루지 않고 함수로 이동합니다.
22. 만약에
함수: 논리 함수 IF 함수를 사용할 때 조건이 true이면 함수는 값을 반환하고, 조건이 false이면 함수는 다른 값을 반환합니다.
구문: =IF(논리적,Value_if_true,Value_if_false)
IF 함수는 지정된 조건이 true로 평가되면 값을 반환하고 조건이 false로 평가되면 다른 값을 반환합니다.
23.카운티프
기능: 특정 조건을 충족하는 셀 수를 계산하는 데 사용됩니다. 예를 들어 특정 도시가 고객 목록에 나타나는 횟수를 계산합니다.
구문: =COUNTIF(셀 1: 셀 2, 조건)
특정 매장이 목록에 나타나는 횟수를 셉니다.
24. 그리고
기능: 논리적 판단, "결합"과 동일함.
구문: 모든 매개변수가 True이면 True를 반환합니다. 이는 다중 조건 판단에 자주 사용됩니다.
예: =AND(A2>1,A2<100) A2가 1보다 크고 100보다 작으면 TRUE가 표시되고, 그렇지 않으면 FALSE가 표시됩니다.
25.또는
기능: 논리적 판단, "또는"과 동일.
구문: 매개변수에 True가 있으면 Ture가 반환되며, 다중 조건 판단에 자주 사용됩니다.
예: =OR(A2>1,A2<100) A2가 1보다 크거나 100보다 작으면 TRUE가 표시되고, 그렇지 않으면 FALSE가 표시됩니다.
엑셀 표 통계를 사용하다 보면 엑셀에서 제공하는 다양한 수식을 사용해야 하는 경우가 많은데, 이는 가장 일반적으로 사용되는 유형이기도 하다. (이를 위해 Excel에는 바로가기 기능이 제공됩니다.)
26.분
기능: 특정 영역에서 최소값을 찾습니다.
구문: =MIN(숫자1, [숫자2], ...)
예: =MIN(D2:D11) D2:D11 범위의 최소 숫자입니다.
27.MAX
기능: 특정 영역에서 최대값을 찾습니다.
구문: =MAX(숫자1, [숫자2], ...)
예: =MAX(A2:A6) A2:A6 영역의 최대값입니다.
28.평균
기능: 특정 지역의 평균값을 계산합니다.
구문: =AVERAGE(숫자1, [숫자2], ...)
예: =AVERAGE(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")은 셀 A2~A7에 1과 6(1과 6 제외) 사이의 숫자가 몇 개 포함되어 있는지 계산합니다.
31.합
기능: 셀 범위에 있는 모든 값의 합을 계산합니다.
구문: =SUM(셀 1:셀 2)
예: =SUM(A2:A10)은 A2:10 셀의 값을 더합니다.
32.스미프
기능: 조건에 맞는 셀의 합을 구합니다.
구문: =SUMIF(범위,기준, [합계_범위])
예: =SUMIF(A2:A7,"과일",C2:C7) "과일" 범주에 속하는 모든 식품의 판매량 합계입니다.
32.스미프스
기능: 지정된 조건을 충족하는 셀 그룹을 합산합니다.
구문: =SUMIFS(합계 범위,기준_범위1, 기준1, [기준_범위2, 기준2], ...)
예: =SUMIFS(A2:A9, B2:B9, "=향*", C2:C9, "LUNING") "향"으로 시작하고 "LUNING"에서 판매되는 제품의 총 수량을 계산합니다.
33.SUMPRODUCT
기능: 해당 배열 또는 면적 곱의 합계를 반환합니다.
구문: =SUMPRODUCT(배열1, [배열2], [배열3], ...)
예: =SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) Table 1의 A1~A100과 Table 2의 B1~B100의 곱의 합, 즉 A1*B1+A2를 계산한다. *B2+A3* B3+...
34.STDEV
기능: 표본을 기반으로 표준 편차를 추정합니다.
구문: STDEV(번호1,[번호2],...)
예: =STDEV(D2:D17) 열의 표준 편차
35.소계
기능: 목록이나 데이터베이스의 소계를 반환합니다.
구문: =SUBTOTAL(function_num,ref1,[ref2],...)
예: =SUBTOTAL(9,A2:A5)는 9를 첫 번째 매개 변수로 사용하여 A2:A5 셀의 소계 값 합계를 계산합니다.
http://36.INT/ROUND
기능: ROUND 함수는 숫자를 지정된 자릿수로 반올림합니다.
구문: =ROUND(A1, 2)
예: =ROUND(2.15, 1)은 2.15를 소수점 이하 한 자리로 반올림합니다.
기능: INT는 숫자를 가장 가까운 정수로 내림합니다.
구문:=INT(8.9) 8.9를 가장 가까운 정수로 내림합니다.
시간 형식 및 변환을 처리하도록 특별히 설계되었습니다.
37. 오늘
기능: 현재 날짜의 일련번호를 반환합니다.
구문: =TODAY()
li'z=TODAY()+5는 현재 날짜에 5일을 더한 값을 반환합니다. 예를 들어, 현재 날짜가 2012년 1월 1일인 경우 이 수식은 2012년 1월 6일을 반환합니다.
38.지금
기능: 현재 날짜와 시간의 일련번호를 반환합니다.
구문: =지금()
=NOW()+7은 7일 후의 날짜와 시간을 반환합니다.
39.년
기능: 특정 날짜에 해당하는 연도를 반환합니다.
구문: =YEAR(일련번호)
=YEAR(A3) 셀 A3에 있는 날짜의 연도입니다.
40.월
기능: 날짜의 월을 반환합니다.
구문: =MONTH(일련번호)
=MONTH(A2) 셀 A2에 있는 날짜의 월입니다.
41.DAY
기능: 일련번호로 표현된 날짜의 일수를 반환합니다.
구문: =DAY(일련번호)
=DAY(A2) 셀 A2에 있는 날짜의 일 수입니다.
42.평일
기능: 특정 날짜에 해당하는 요일을 반환합니다. 기본적으로 일수는 1(일요일)부터 7(토요일)까지의 정수입니다.
구문: =WEEKDAY(일련번호,[반품_유형])
=WEEKDAY(A2) 1(일요일)부터 7(토요일)까지의 요일
=WEEKDAY(A2, 2) 1(월요일)부터 7(일요일)까지의 요일입니다.
43.날짜IF
기능: 두 날짜 사이의 일, 월 또는 연 수를 계산합니다.
구문: =DATEDIF(시작일, 종료일, 단위)
=DATEDIF(Start_date,End_date,"Y")기간의 연수
=DATEDIF(Start_date,End_date,"D")기간의 일수
=DATEDIF(Start_date,End_date,"YD")는 날짜의 연도와 기간의 일수를 무시합니다.
첨부된:
