Cập nhật nội dung chi tiết về Hướng Dẫn Tính Tổng Doanh Số Theo Từng Tháng Trong Excel mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Công thức tổng quát tính tổng doanh số theo từng tháng trong Excel
[Các vùng và điều kiện khác nếu có thêm]
)
Giải thích:
Hàm SUMIFS sẽ tính tổng của vùng giá trị dựa trên các điều kiện của vùng thời gian. Cụ thể ở đây là 2 điều kiện: Lớn hơn ngày đầu tháng và nhỏ hơn ngày cuối tháng.
Để xác định ngày đầu tháng, bạn có thể dùng hàm DATE, trong đó đã biết rõ số năm, số tháng và số ngày (ngày đầu tháng luôn là ngày 1)
Hàm EOMONTH sẽ trả về giá trị ngày cuối cùng trong tháng dựa theo ngày đầu tháng.
Áp dụng các nội dung trên vào ô I6 ta nhập:
Kết quả thu được như sau:
Sử dụng hàm SUMIFS tính tổng theo ngày, tháng, năm
Lập báo cáo tổng hợp theo từng tháng với SUMIFS
Đánh giá bài viết này
Tài liệu kèm theo bài viết
Tính tổng theo điều kiện tháng khi dữ liệu chỉ có cột ngày.xlsx
Tải xuống
Hướng Dẫn Cách Tính Tổng Các Số Dương Hoặc Âm Trong Excel
Sử dụng Excel đôi khi bạn muốn tính tổng các số dương hoặc số âm nhưng chưa biết cách. Vậy thì Gitiho sẽ hướng dẫn các bạn cách tính tổng các số dương hoặc âm trong Excel.
Cách tính tổng các số dương
– Đối số đầu tiên là phạm vi tiêu chí. Đây là nơi kiểm tra các tiêu chí và chỉ thêm các giá trị đáp ứng tiêu chí. Trong ví dụ này chính là B2:B8
– Đối số thứ ba có phạm vi thực tế mà bạn muốn tính tổng.
Lưu ý: Với công thức SUMIF, phạm vi tiêu chí và phạm vi ô bạn muốn tính tổng có thể khác nhau. Trong ví dụ này, cả phạm vi tiêu chí và phạm vi tổng đều giống nhau (B2:B8).
Vì trong trường hợp này chỉ có một tiêu chí nên sử dụng công thức SUMIF. Trong trường hợp bạn có nhiều tiêu chí để kiểm tra, bạn có thể sử dụng công thức SUMIFS này trong Excel.
Cách tính tổng các số dương lớn hơn 100
Trong ví dụ trên chúng ta đã tính tất cả các ô có giá trị dương. Nhưng bạn cũng có thể sử dụng công thức SUMIF cho những ô cụ thể đáp ứng các tiêu chí khác.
Tất cả những gì bạn cần làm là điều chỉnh tiêu chí để thay vì tính tổng tất cả các số dương thì sẽ thay bằng tính tổng của tất cả các số lớn hơn 100.
Như bạn có thể thấy, tất cả những gì cần làm ở đây chỉ là điều chỉnh đối số thứ hai, đó là đối số tiêu chí.
Cách tính tổng các số dương của một ô dựa trên giá trị dương của ô khác
Trong các ví dụ trên, chúng ta đã sử dụng công thức SUMIF trong đó ‘phạm vi tiêu chí’ và ‘phạm vi tổng’ giống nhau.
Nhưng như đã nói ở trên, bạn cũng có thể áp dụng cho những phạm vi khác nhau. Vì vậy, bạn có thể có một phạm vi ô mà bạn đánh giá một tiêu chí, sau đó xem xét dựa trên một ô có đáp ứng tiêu chí hay không rồi thêm giá trị vào một phạm vi tương ứng.
Lưu ý: Đây là một công thức tương tự như chúng ta đã sử dụng ở trên (với một thay đổi). Thay vì có cùng phạm vi ‘tiêu chí’ và ‘tổng’ thì ở công thức này cả hai đều khác nhau.
Cách tính tổng các số âm
Cũng giống như cách chúng ta đã tính tổng tất cả các số dương, bạn có thể sử dụng một khái niệm tương tự để chỉ tính tổng các giá trị âm.
=SUMIF(B2:B8,”<0″,B2:B8)
Công thức trên sử dụng “<0” làm tiêu chí (đối số thứ hai), chỉ thêm các số âm. Bạn cũng có thể sử dụng các khái niệm tương tự được hiển thị trước đó để thêm số âm bên dưới một số cụ thể và thậm chí có thể có các tiêu chí và phạm vi tổng riêng biệt.
Hướng dẫn cách tối ưu file Excel nhanh hơn hiệu quả hơn Hướng dẫn cách thao tác trình bày định dạng bảng dữ liệu trong Excel chuẩn nhất Hướng dẫn cách sắp xếp dữ liệu trong Excel theo màu chi tiết
Cách Tính Tổng, Sum Theo Hàng Ngang Trong Excel
Hàm Sum trong excel được sử dụng để tính tổng, việc áp dụng hàm Sum vào tính toán là rất cần thiết và đây cũng là một trong những hàm cơ bản trong Excel. Hàm Sum thông thường chúng ta chỉ tính toán theo từng cột dữ liệu, việc sử dụng hàm Sum tính tổng theo hàng ngang tron Excel cũng không có gì lạ nhưng bạn có biết được có bao nhiều cách để giúp bạn thực hiện những thao tác này không.
Ngoài ra hàm Sum cũng là một trong những hàm tính toán thông dụng trong Excel, nên việc bạn hiểu càng nhiều về hàm này trong Excel thì càng giúp bạn sử dụng Excel thành thạo hơn. Ngay bây giờ chúng tôi sẽ đi vào chi tiết bài viết tính tổng trong Excel sử dụng hàm Sum, là hàm tính toán trong Excel bạn cần phải biết.
Cách tính tổng hàm Sum theo hàng ngang trong Excel.
Cách 1: Bạn có thể áp dụng tính tổng sum hàng ngang trong Excel bằng cách giữ Alt vào ô cần nhập công thức sau đó nhấn phím “=”, ngay lập tức công thức sẽ hiện ra là khoanh toàn bộ các giá trị theo hàng ngang.
Kết quả sẽ hiện ra bằng 27, bạn có thể tính nhẩm để tra kết quả xem có khớp không.
Ngay lập tức tháo tác trên sẽ giúp bạn tính tổng toàn bộ hàng ngang trên Excel và bạn chỉ cần nhấn Enter để kết quả hiện ra mà thôi.
Có một cách để bạn kiểm tra xem tổng của một hàng chính xác là bao nhiêu bằng cách bôi đen vào tất cả các hàng, ở dưới bạn sẽ nhìn thấy có chữ Sum và kết quả hiện ra, đây là hệ thống trong Excel giúp bạn tính tổng sum hàng ngang trong Excel.
Với những cách mà chúng tôi vừa giới thiệu trên giúp bạn hiểu biết thêm về hàm Sum, cách tính tổng hàng sum theo hàng ngang trong Excel mà bạn nên biết cũng như nên tìm hiểu để sử dụng thành thạo hơn.
Ngoài ra có hàm Vlookup giúp bạn dò tìm giá trị theo cột, hàng cũng rất hữu hiệu và hàm Vlookup cũng là một trong những hàm rất quan trọng trong Excel, được sử dụng nhiều trong Excel nên bạn cần phải biết. Cũng với hàm Sum thì hàm Vlookup là một trong những hàm cơ bản trong Excel cần phải biết, cần phải thành thục nó đầu tiên.
Hướng Dẫn Cách Đếm Và Tính Tổng Dựa Theo Màu Sắc Của Ô Tính Bằng Vba
Trong bài viết này, Học Excel Online sẽ đếm các ô tính bằng màu sắc và tính tổng các ô được tô màu. Những giải pháp này hiệu quả đối với cả ô tính tô màu thủ công và ô tính được định dạng theo điều kiện. Đồng thời, bạn cũng biết cách lọc các ô tính bằng nhiều màu trong Excel 2010, 2013, 2016.
Cách đếm và tính tổng bằng màu trong trang tính Excel
Giả sử bạn có bảng liệt kê danh sách đơn đặc hàng của công ty, trong đó cột Delivery được tô màu dựa trên giá trị của chúng: “Due in X Days” được tô màu cam, “Delivered” được tô màu xanh lá và “Past Due” được tô màu đỏ.
Mở bảng tính Excel và nhấn để mở Visual Basic Editor (VBE).
Thêm đoạn mã sau vào bảng tính.
Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColor = arResults
Else
GetCellColor = xlRange.Interior.Color
End If
End Function
Function GetCellFontColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
Next
Next
GetCellFontColor = arResults
Else
GetCellFontColor = xlRange.Font.Color
End If
End Function
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
End Function
Function SumCellsByColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByColor = sumRes
End Function
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByFontColor = cntRes
End Function
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByFontColor = sumRes
End Function
Lưu bảng tính thành “Excel Macro-Enabled Workbook (.xlsm)”.
Như vậy, tất cả những thao tác lặt vặt đã thực hiện xong, chỉ cần thêm hàm được chỉ định, chọn ô tính muốn hiển thị công thức và gõ công thức CountCellsByColor.
=CountCellsByColor(range, color code)
Trong ví dụ này, chúng ta sử dụng công thức = CountCellsByColor (F2:F14, A17) trong đó F2: F14 là dãy chứa các ô có màu bạn muốn đếm và A17 là ô có màu nền nhất định, trong ví dụ đó là các ô màu đỏ.
Tương tự, bạn viết công thức để đếm các ô màu khác, như màu vàng và màu xanh lá cây trong bảng của chúng tôi.
Chú ý. Sau khi áp dụng mã VBA, bạn sẽ cần thêm màu cho một số ô khác theo cách thủ công, tổng và số ô được tô màu sẽ không được tính lại tự động để phản ánh những thay đổi.
Trên thực tế, đó là đặc điểm bình thường của tất cả các , VBA scripts và hàm User-Defined. Vấn đề là tất cả các chức năng đó sẽ đi kèm với sự thay đổi dữ liệu của một bảng tính và Excel không nhận thấy sự thay đổi màu chữ hoặc màu sắc của ô này. Vì vậy, sau khi tô màu các ô một cách thủ công, chỉ cần đặt con trỏ chuột vào bất kỳ ô nào, nhấn và Enter, số ô và tổng của chúng sẽ được cập nhật.
Tính tổng và đếm số ô bằng màu sắc trên toàn bộ bảng tính:
Đoạn VB bên dưới được viết theo câu trả lời của Connor (một bậc thầy Excel) và thực hiện chính xác những gì Connor yêu cầu, cụ thể là đếm và tính tổng các ô cùng màu trong tất cả trang tính của bảng tính. Vì vậy, ở đây có mã số:
Function WbkCountCellsByColor(cellRefColor As Range)
Dim vWbkRes
Dim wshCurrent As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
vWbkRes = 0
For Each wshCurrent In Worksheets
wshCurrent.Activate
vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
WbkCountCellsByColor = vWbkRes
End Function
Function WbkSumCellsByColor(cellRefColor As Range)
Dim vWbkRes
Dim wshCurrent As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
vWbkRes = 0
For Each wshCurrent In Worksheets
wshCurrent.Activate
vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
WbkSumCellsByColor = vWbkRes
End Function
Tùy chỉnh các chức năng để tô màu nền ô, tô màu chữ và mã màu sắc
Bạn sử dụng tương tự như mã trước đó và sử dụng kết quả của các công thức =WbkCountCellsColor() và =WbkSumCellsByColor(). Chỉ cần nhập công thức vào bất kỳ ô trống nào trên trang tính nằm ngoài vùng dữ liệu được chọn, xác định địa chỉ ô tính chứa màu bạn muốn vào ngoặc đơn, ví dụ = WbkSumCellsByColor (A1), và công thức sẽ hiển thị tổng của tất cả các ô được tô cùng trong bảng tính của bạn.
Ở đây, bạn sẽ tìm thấy một bản tóm tắt tất cả các chức năng chúng ta đã sử dụng trong ví dụ này cũng như một vài cái chức năng mới để lấy mã màu.
Chú ý. Hãy nhớ rằng tất cả các công thức chỉ hoạt động nếu bạn đã thêm chức năng do người dùng định nghĩa vào bảng tính Excel như đã trình bày ở trên.
Chức năng đếm theo màu sắc:
CountCellsByColor (phạm vi, mã màu) – đếm các ô có màu nền được chỉ định.
Công thức tính tổng bằng màu sắc:
CountCellsByFontColor (phạm vi, mã màu) – đếm các ô với màu chữ được chỉ định.
SumCellsByColor (range, color code) – tính tổng của các ô có cùng màu nền.
Công thức để lấy mã màu:
SumCellsByFontColor (range, color code) – tính tổng của các ô có cùng màu chữ.
GetCellFontColor (cell) – trả về mã màu chữ của một ô xác định.
Cách đếm và tính tổng các ô màu định dạng có điều kiện:
Sub SumCountByConditionalFormat()
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Dim sumRes
Dim cntCells As Long
Dim indCurCell As Long
cntRes = 0
sumRes = 0
cntCells = Selection.CountLarge
indRefColor = ActiveCell.DisplayFormat.Interior.Color
For indCurCell = 1 To (cntCells – 1)
If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
cntRes = cntRes + 1
sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
End If
Next
MsgBox “Count=” & cntRes & vbCrLf & “Sum= ” & sumRes & vbCrLf & vbCrLf & _
“Color=” & Left(“000000”, 6 – Len(Hex(indRefColor))) & _
Hex(indRefColor) & vbCrLf, , “Count & Sum by Conditional Format color”
End Sub
Cách sử dụng mã để đếm số ô màu và tính tổng giá trị của chúng
Thêm đoạn mã trên vào trang tính như đã giải thích ở ví dụ.
Chọn một dãy hoặc nhiều vùng dữ liệu bạn cần nếu bạn có dữ liệu dạng số.
Nhấn giữ phím , chọn một ô tính với màu cần thao tác, thả phím .
Nhấn để mở danh sách trong bảng tính.
Chọn SumCountByConditionalFormat và nhấp vào Run.
Trong ví dụ này, chúng ta chọn cột Qty. và có các con số sau:
Count là số ô tính trong một màu cụ thể, như số ô màu đỏ trong ví dụ được đánh dấu là “Past Due”.
Sum là tổng giá trị các ô màu đỏ trong cột Qty, cụ thể là tổng số ô “Past Due”.
Color là mã màu của ô được chọn, cụ thể là ô D2.
Qua bài viết này chúng ta thấy việc sử dụng VBA trong Excel đem lại hiệu quả rất nhiều trong công việc đúng không nào. Không chỉ có vậy, VBA còn làm được nhiều điều thú vị hơn nữa như: giúp bạn tăng tốc trong quá trình xử lý trên excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như word, power point, outlook… giúp biến file excel của bạn trở thành 1 phần mềm chuyên nghiệp…
Bạn đang đọc nội dung bài viết Hướng Dẫn Tính Tổng Doanh Số Theo Từng Tháng Trong Excel trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!