Excel에서 금액을 결정하는 방법. 엑셀이 계산기보다 낫다

EXCEL 스프레드시트를 사용하여 작업하는 많은 사람들은 "EXCEL의 특정 조건에 따라 셀에 숫자를 추가하는 방법"이라는 질문에 직면하게 됩니다. 어느 화창한 날, 저도 이 질문을 접했는데 인터넷에서 이 질문에 대한 답을 찾을 수 없었으나 끈질기게 아이디어를 얻은 프로그램의 도움을 연구하기로 결심했고, 시행착오를 거쳐 공식을 작성했는데, 알고 보니 그 공식은 초보적인 수준이었습니다.

모든 것은 내가 월별 비용을 고려하기로 결정했다는 사실에서 시작되었으며 이를 위해 이 기사에 첨부된 표가 여러분에게도 유용할 수 있기 때문에 이 기사에 첨부했습니다.

이제 공식을 만드는 원리를 자세히 설명하겠습니다. 보고서에는 자세한 통계와 요약 보고서가 있습니다. 세부 통계에는 나의 일일 지출액을 입력하고, 요약 보고서에는 특정 항목에 대한 지출 금액과 지출 총액이 계산됩니다.

예를 들어 '매장 내 쇼핑'이라는 비용 카테고리를 생각해 보겠습니다. 세부 통계에서 특정 카테고리에 대한 모든 비용을 찾고, 이 카테고리에 대한 비용을 합산하고 결과 금액을 셀에 쓰려면 EXCEL이 필요합니다. D10.

먼저 준비된 수식을 적어서 셀에 붙여 넣습니다. D10, 자세한 내용을 살펴보겠습니다. 완성된 공식은 다음과 같습니다(이 기사에만 해당).

=SUMIF ($G$5:$G$300 ;(" 매장에서 쇼핑하기");$H$5:$H$300 )

다양한 조건을 색상으로 강조하여 더욱 명확하게 보여줍니다. 순서대로 살펴 보겠습니다. 설명 과정에서 위의 그림을 보면 더 명확해집니다. 사진을 찍을 때 열의 글자와 행의 번호를 특별히 캡쳐했습니다. 그럼 시작해 보겠습니다.

• SUMIF– 이 조건을 사용하면 특정 조건을 충족하는 경우 특정 셀의 값 합계가 셀에 기록되어야 한다고 말합니다.
• $G$5:$G$300– 여기서는 선택 조건을 찾아야 하는 열을 EXCEL에 표시합니다. 우리의 경우 검색은 열에서 발생합니다. G줄부터 시작해서 5 그리고 줄로 끝나는 300 ;
• (« 매장에서 쇼핑하기») – 여기에는 필요한 조건이 표시되며 이 조건에 따라 추가로 표시하는 셀의 값이 요약됩니다.
• $H$5:$H$300– 여기서는 합계를 위한 숫자를 가져올 열을 나타냅니다. 우리의 경우 값은 열에서 사용됩니다. 시간줄부터 시작해서 5 그리고 줄로 끝나는 300.

요약하면 EXCEL은 범위의 값만 합산한다고 말할 수 있습니다. H5:H300, 범위의 해당 값 G5:G300"매장에서 구매"와 동일하며 결과를 셀에 기록합니다. D10.

따라서 다음을 수행할 수 있습니다. EXCEL에서는 어떤 조건에 따라 셀에 숫자를 추가합니다..

징후 $ 수식에서는 D10 셀에서 다른 셀로 수식을 복사할 때 오프셋이 없도록 사용됩니다. 부호 없는 수식의 예를 살펴보겠습니다. $ . 예를 들어, 셀에서 D10우리는 공식을 가지고 있습니다:

=SUMIF(G5:G300,("매장에서 구매"),H5:H300)

다음으로 셀에 점심 식사의 합계를 표시하려고 합니다. D11. 수식을 다시 작성하지 않으려면 셀을 복사하면 됩니다. D10그리고 셀에 붙여넣기 D11. 덕분에 수식이 삽입됩니다. D11, 그러나 여기서는 5를 6으로, 300을 301로 대체하여 수식이 값을 변경했음을 알 수 있습니다.

=SUMIF(G6:G301,("매장에서 구매"),H6:H301)

변화가 발생했습니다. 수식을 복사하면 D12, 그러면 2교대 등이 표시됩니다. 이를 방지하기 위해 기호를 사용하여 공식을 작성합니다. $ . EXCEL의 이러한 기능.

표 "월별 비용 회계"-

이 기사에서는 방법을 살펴볼 것입니다. 엑셀로 금액 계산하기자동 합계 기능을 사용하고 열의 금액을 계산하는 방법, 한 줄의 금액을 계산하다또는 SUM 함수를 사용하여 선택한 범위에서. 또한 보이는 셀만 합산하는 방법, 총 개수를 계산하는 방법, Excel에서 다른 시트의 합계를 계산하는 방법그리고 훨씬 더.

가장 빠른 방법으로 Excel에서 금액을 계산하세요.

먼저 가장 빠른 방법을 살펴보겠습니다. Excel에서 셀의 합을 계산하는 방법. Excel에서 특정 셀의 합계를 빠르게 확인해야 하는 경우 해당 셀을 선택하고 Excel 창 오른쪽 하단에 있는 상태 표시줄을 보면 됩니다.

Excel에서 합계 계산 - 선택한 셀의 합계를 찾는 빠른 방법

자, 가장 간단한 방법부터 시작해 보겠습니다. 따라서 Excel을 처음 사용하는 경우에도 다음 예를 이해하는 것이 어렵지 않을 것입니다.

간단한 산술 계산을 사용하여 Excel에서 합계를 계산합니다.

당신이 요구하는 경우 셀의 합을 계산, Excel을 미니 계산기로 사용할 수 있습니다. 일반 산술 연산 덧셈과 마찬가지로 더하기 기호 연산자(+)를 사용하면 됩니다. 예를 들어:
=1+2+3
또는
=A1+C1+D1

Excel에서 합계 계산 - 간단한 산술 계산을 사용하여 셀의 합계를 계산합니다.

물론 수십, 수백 행의 합을 계산해야 하는 경우 수식의 각 셀을 참조하는 것은 최선의 방법이 아닙니다. 이 경우 지정된 숫자 집합을 추가하도록 특별히 설계된 SUM 함수를 사용해야 합니다.

SUM 함수를 사용하여 Excel에서 합계 계산

SUM 함수는 값을 더하는 수학 및 삼각 함수 중 하나입니다. 이 함수의 구문은 다음과 같습니다.

SUM(숫자1;[숫자2];...)

첫 번째 인수(number1)는 필수이고 다른 숫자는 선택 사항이며 단일 수식에 최대 255개의 숫자를 지정할 수 있습니다.

SUM 수식에서 모든 인수는 양수 또는 음수 숫자 값, 범위 또는 셀 참조가 될 수 있습니다. 예를 들어:

합계(A1:A100)

합계(A1; A2; A5)

합계(1;5;-2)

SUM 함수는 다양한 범위의 값 합계를 계산하거나 숫자 값, 셀 참조 및 범위를 결합해야 할 때 유용합니다. 예를 들어:

합계(A2:A4; A8:A9)

합계(A2:A6; A9; 10)

아래 이미지는 SUM 수식의 이러한 예와 기타 여러 예를 보여줍니다.

Excel에서 합계 계산 - SUM 수식을 사용하여 합계 계산 예

Excel에서 금액을 자동으로 계산하는 방법

필요한 경우 자동으로 금액을 계산, 하나의 숫자 범위를 합산하여 열, 행 또는 인접한 여러 열이나 행의 합계를 계산하려는 경우 Microsoft Excel에서 이러한 계산을 수행하도록 할 수 있습니다.

"편집" 그룹의 "홈" 탭에서 합계를 계산하려는 숫자 옆에 있는 셀을 선택하고 "자동 합계"와 "Enter" 키를 누르면 공식 " 합집합»:

Excel에서 합계 계산 - 자동 합계를 사용하여 열의 합계 계산

보시다시피 "자동 합계" Excel은 SUM 수식을 입력할 뿐만 아니라 합계를 계산하기 위해 가장 가능성 있는 셀 범위를 선택합니다. 이 경우 Excel에서 해당 열의 금액을 자동으로 계산합니다.. 제안된 범위를 조정해야 하는 경우 계산하려는 셀 위로 커서를 끌고 Enter 키를 눌러 수동으로 수정할 수 있습니다.

합계를 계산하는 것 외에도 AutoSum을 사용하여 AVERAGE, COUNTER, MAX 또는 MIN 함수를 자동으로 입력할 수 있습니다. 자세한 내용은 ""기사를 확인하세요.

Excel에서 열의 금액을 계산하는 방법

합(B2:B8)

Excel에서 합계 계산 - 열의 합계 계산

Excel에서 행의 금액을 계산하는 방법

합(B2:D2)

Excel에서 금액 계산 - Excel에서 행의 금액을 계산합니다.

Excel에서 합계 계산 - 합계 행을 사용하여 Excel 테이블에서 합계를 계산합니다.

Excel에서 다른 시트의 금액을 계산하는 방법

레이아웃과 데이터 유형이 동일한 시트가 여러 개 있는 경우 다음을 수행할 수 있습니다. 금액을 계산하다 동일한 SUM 수식을 사용하는 다른 시트의 동일한 셀 또는 동일한 셀 범위에 있습니다.

SUM(1월:3월!B2)

SUM(1월:3월!B2:B8)

첫 번째 수식은 B2 셀의 값 합계를 계산하고, 두 번째 수식은 지정된 두 테두리 시트(이 예에서는 1월과 3월) 사이에 있는 모든 시트에서 B2:B8 범위의 합계를 계산합니다.

Excel에서 합계 계산 - 3D 참조를 사용하여 지정된 시트 사이의 동일한 셀 또는 범위의 합계를 계산합니다.

따라서 =SUM(1월:3월!B2) 수식을 사용하여 3개월 동안의 사과 판매에 대한 모든 값의 합계를 계산했습니다. 그리고 두 번째 수식 =SUM(1월:3월!B2:B8)을 사용하여 3개월 동안의 모든 제품 판매량 합계를 알아냈는데, 그 데이터는 서로 다른 시트에 있었습니다. 그래서 당신은 할 수 있습니다 Excel에서 다른 시트의 금액 계산, 3D 참조가 포함된 공식을 사용합니다.

그게 다야. 기사를 읽어주셔서 감사합니다. 이 기사가 어떻게 해야 하는지에 대한 질문을 충분히 포괄적으로 밝혀냈기를 바랍니다. 엑셀로 금액 계산하기.

이번 글에서는 행 수가 무한정인 컬럼에서 금액을 계산하는 방법과, 헤더나 처음 몇 행을 제외한 컬럼에서 금액을 계산하는 방법을 살펴보겠습니다.

SUM 함수를 사용하여 열의 합계를 계산하는 방법

합(B2:B8)

Excel에서 열의 금액을 계산하는 방법 - 열의 금액을 계산합니다.

Excel과 같은 두 번째 방법이 있습니다 자동으로 열 합계 계산 . 이렇게 하려면 금액을 계산하려는 열의 마지막 빈 셀을 선택합니다. "HOME" 탭의 "편집" 그룹에서 "자동 합계"를 클릭하고 "Enter" 키를 누릅니다. 그런 다음 "SUM" 수식이 셀에 자동으로 입력됩니다.

Excel에서 열의 합계를 계산하는 방법 - 테이블에서 자동 합계를 사용하여 열의 합계를 계산합니다.

행 수가 무한정인 열의 합계를 계산하는 방법

필요한 경우 열의 금액 계산가변 개수의 행이 있는 경우(예: 언제든지 새 셀을 추가할 수 있고 기존 셀을 삭제할 수 있음) 다음을 수행할 수 있습니다. 전체 열의 합계를 계산합니다.열 참조를 지정하지만 하한 또는 상한은 지정하지 않습니다. 예:

주의하세요!어떠한 경우에도 "라는 공식을 사용해서는 안 됩니다. 열 합계 "를 원하는 열에 금액을 계산하다이렇게 하면 순환 셀 참조(즉, 무한 재귀 합계)가 생성되고 SUM 수식이 0을 반환하기 때문입니다.

Excel에서 열의 금액을 계산하는 방법 - 열 참조를 사용하여 열의 금액을 계산합니다.

헤더를 제외하거나 처음 몇 행을 제외한 열의 금액을 계산하는 방법

일반적으로 열 참조는 SUM 수식에서 다음을 수행하는 데 사용됩니다. 열의 금액 계산 , 위 이미지에 표시된 것처럼 헤더를 무시합니다. 그러나 어떤 경우에는 열 머리글에 일종의 숫자 값이 포함될 수 있습니다. 또는 예를 들어 데이터와 관련이 없는 숫자가 포함된 처음 몇 개의 행을 제외하려고 합니다.

불행하게도 Microsoft Excel에서는 =SUM(B2:B)와 같이 명시적인 하한은 있지만 상한은 없는 혼합 SUM 수식을 허용하지 않습니다. 필요한 경우 열의 금액 계산 처음 몇 줄을 제거한 후 다음 몇 가지 솔루션 중 하나를 사용할 수 있습니다.

1. 수학을 해보세요 열 합계그런 다음 합계에 포함하지 않으려는 셀(이 예에서는 B1-B3 셀)을 뺍니다.

합계(B:B) - 합계(B1:B3)

Excel에서 열의 합계를 계산하는 방법 - 처음 몇 행을 제외한 열의 합계를 계산합니다.

1. 워크시트 크기 제한을 염두에 두고 Excel 버전의 최대 행 수를 기준으로 SUM 수식의 상한을 지정할 수 있습니다.

Excel 2007, Excel 2010, Excel 2013 및 Excel 2016의 경우:

합(B2:B1048576)

합계(B2:B655366)

이 기사에서는 다양한 방법을 살펴보았습니다. Excel에서 열의 금액을 계산하는 방법, 이제 상황에 따라 둘 중 하나를 선택할 수 있습니다.

이전에는 사용자 정의 함수를 사용하여 를 찾는 방법을 설명했습니다. 안타깝게도 조건부 서식을 사용하여 셀에 색상을 지정한 경우에는 이 기능이 작동하지 않습니다. 나는 그 기능을 “완성”하겠다고 약속했습니다. 하지만 그 노트가 출판된 후 2년이 지나도록 나는 스스로 또는 인터넷 정보의 도움을 받아 소화 가능한 코드를 작성할 수 없었습니다... ( 2017년 3월 29일 업데이트 5년이 더 지난 후에도 나는 여전히 코드를 작성할 수 있었습니다. 메모의 마지막 부분을 참조하세요.) 그리고 최근에 저는 D. Hawley, R. Hawley가 쓴 "Excel 2007. Tricks"라는 책에서 코드 없이도 작업을 수행할 수 있는 아이디어를 발견했습니다.

A1:A100 범위에 1부터 100까지의 숫자 목록이 있다고 가정합니다(그림 1, Excel 파일의 "SUMIF" 시트 참조). 범위에는 10보다 크고 20보다 작거나 같은 숫자가 포함된 셀을 표시하는 조건부 서식이 있습니다.

쌀. 1. 숫자의 범위 조건부 서식은 10에서 20 사이의 값을 포함하는 셀을 선택합니다.

형식의 노트, 형식의 예시 다운로드

이제 방금 설정한 기준에 맞는 셀의 값을 더해야 합니다. 이러한 셀에 어떤 서식이 적용되는지는 중요하지 않지만 셀이 강조 표시되는 기준을 알아야 합니다.

다음에 해당하는 셀 범위를 추가하려면 홀로기준에 따라 SUMIF 함수를 사용할 수 있습니다(그림 2).

쌀. 2. 동일한 조건을 만족하는 셀들의 합산

만약 당신이 일부조건에서는 SUMIFS 함수를 사용할 수 있습니다(그림 3).

쌀. 3. 여러 조건을 만족하는 셀의 합산

COUNTIF 함수를 사용하여 하나의 기준을 충족하는 셀 수를 계산할 수 있습니다.

COUNTIFS 함수를 사용하여 여러 기준을 충족하는 셀 수를 계산할 수 있습니다.

Excel에는 여러 조건을 지정할 수 있는 또 다른 기능이 있습니다. 이 함수는 Excel 데이터베이스 함수 세트의 일부이며 BDSUMM이라고 합니다. 이를 확인하려면 A2:A100 범위에서 동일한 숫자 세트를 사용하십시오(그림 4, Excel 파일의 "BDSUMM" 시트 참조).

쌀. 4. 데이터베이스 기능 사용

C1:D2 셀을 선택하고 수식 입력줄 왼쪽에 있는 이름 상자에 입력하여 범위 이름을 Criteria로 지정합니다. 이제 C1 셀을 선택하고 =$A$1, 즉 데이터베이스 이름이 포함된 시트의 첫 번째 셀에 대한 링크를 입력합니다. D1 셀에 =$A$1을 입력하면 A열 머리글의 복사본 두 개가 생성됩니다. 이 복사본은 Criteria라는 이름의 BDSUM(C1:D2) 조건에 대한 머리글로 사용됩니다. C2 셀에 >10을 입력합니다. D2 셀에 다음을 입력합니다.<=20. В ячейке, где должен быть результат, введите следующую формулу:

BDSUMM($A$1:$A$101,1,기준)

COUNT 함수를 사용하여 여러 기준을 충족하는 셀 수를 계산할 수 있습니다.

John Walkenbach의 책을 읽으면서 Excel 2010부터 새로운 DisplayFormat 속성이 VBA에 나타났다는 사실을 알게 되었습니다(예: Range.DisplayFormat 속성 참조). 즉, VBA는 화면에 표시된 형식을 읽을 수 있습니다. 직접 사용자 설정 또는 조건부 서식을 사용하여 얻은 방법은 중요하지 않습니다. 안타깝게도 MS 개발자는 DisplayFormat 속성이 VBA에서 호출된 프로시저에서만 작동하고 이 속성을 기반으로 하는 사용자 정의 함수에서 #VALUE! 그러나 프로시저(매크로이지만 함수는 아님)를 사용하여 특정 색상의 셀 범위에 있는 값의 합계를 얻을 수 있습니다. 개방형(VBA 코드 포함). 메뉴를 살펴보세요 보다 -> 매크로 -> 매크로; 창문에 매크로, 라인을 선택하세요 합계색상Usl을 누르고 실행하다. 매크로를 실행하고 합계 범위와 기준을 선택합니다. 답변이 창에 나타납니다.

절차 코드

Sub SumColorConv() Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " 범위 요청 세트 UserRange = Application.InputBox(_ Prompt:="합계 범위 선택", _ Title : ="범위 선택", _ 기본값:=ActiveCell.Address, _ 유형:=8) " 기준 요청 Set CriterionRange = Application.InputBox(_ Prompt:="합계 기준 선택", _ Title:="기준 선택", _ 기본값:=ActiveCell.Address, _ 유형:=8) " i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat.Interior.Color Then SumColor = SumColor + i End인 UserRange의 각 i에 대해 "올바른" 셀을 합산합니다. If Next MsgBox SumColor End Sub

예를 들어 특정 지역의 제품 판매량과 같이 둘 이상의 조건에서 값을 합산해야 한다고 가정해 보겠습니다. 이는 수식에 SUMIFS 함수를 사용하는 좋은 방법입니다.

두 가지 조건이 있는 이 예를 살펴보십시오. 판매 금액을 알아야 합니다. 고기다섯 남부 지방 사투리영역 (열 A에서) (열 C에서).

이를 달성하는 데 사용할 수 있는 공식은 다음과 같습니다.

결과는 14.719입니다.

공식의 모든 부분을 연구해 보세요.

SUMIFS는 산술 공식입니다. 숫자를 계산합니다. 이 경우 D열에 있습니다. 가장 먼저 해야 할 일은 숫자의 위치를 ​​지정하는 것입니다.

=SUMIFS(D2:D11;

즉, 조건을 만족하는 경우 열의 숫자를 합산하는 수식을 입력해야 합니다. 셀 범위가 먼저입니다. 논쟁이 수식에는 함수가 입력으로 요구하는 첫 번째 데이터 조각이 있습니다.

그런 다음 두 가지 조건과 일치하는 데이터를 찾아야 하므로 첫 번째 조건을 입력하고 함수에 대해 데이터 위치(a2: a11)와 "South"가 가장 적합한 조건을 지정합니다. 개별 인수 사이에 쉼표를 참고하세요.

= SUMIFS(D2:D11,A2:A11,"남쪽",

"Southern"이라는 단어 주위의 따옴표는 이것이 텍스트 데이터임을 나타냅니다.

마지막으로 두 번째 조건에 대한 인수, 즉 "Meat"이라는 단어가 포함된 셀 범위(C2:C11)와 Excel에서 일치시킬 수 있도록 단어 자체(따옴표로 묶음)를 입력합니다. 닫는 괄호로 수식을 완성하세요. ) 그리고 ENTER를 누르세요. 결과, 다시 누르십시오 - 14.719.

=SUMIFS(D2:D11,a2:a11,"남쪽",C2:C11,"고기")

Microsoft Excel에서 SUMIFS 함수를 입력할 때 인수가 기억나지 않으면 도움말이 준비되어 있습니다. 입장 후 = SUMIFS(, 수식 자동 완성은 인수 목록의 수식 아래에 올바른 순서로 표시됩니다.

수식 완성 및 인수 목록을 사용하는 이미지를 보면 이 예에서 sum_range는 합산하려는 숫자가 포함된 열인 D2:D11 범위입니다. Condition_range1은 다음을 포함하는 데이터 열인 A2.A11 범위입니다. 조건 1- "남부".

수식을 입력하면 나머지 인수(여기에는 표시되지 않음)가 수식 자동 완성에 표시됩니다. 범위_조건2는 다음을 포함하는 데이터 열인 C2:C11 범위입니다. 조건 2- "고기".

클릭하면 SUMIFS수식 자동 완성에서 도움말이 포함된 기사가 열립니다.

연습해 보세요

SUMIFS 함수를 실험해 보려면 해당 함수를 사용하는 몇 가지 샘플 데이터와 수식을 참조하세요.

이 Excel Online 통합 문서에서 직접 데모 데이터로 작업할 수 있습니다. 값과 수식을 변경하거나 직접 추가하여 즉각적인 결과 변경을 확인하세요.

아래 표의 모든 셀을 복사하여 새 Excel 시트의 A1 셀에 붙여넣습니다. 열 너비를 조정하여 수식이 더 잘 보이도록 할 수 있습니다.