Một số ví dụ về các công thức sử dụng hàm COUNTIF

Một số ví dụ về các công thức sử dụng hàm COUNTIF

– Số ô chứa giá trị 12 : =COUNTIF(Data,12)


-Số ô chứa giá trị lớn hơn 12 : =COUNTIF(Data,”>12″)


-Số ô chứa giá trị khác 0 : =COUNTIF(Data,”<>0″)


-Số ô chứa giá trị âm : =COUNTIF(Data,”<0″)


-Số ô chưa giá trị bằng nội dung của một ô nào đó, (TD B1)
=COUNTIF(Data,B1)


-Số ô chưa giá trị lớn hơn ô B1 : =COUNTIF(Data,”>”&B1)


-Số ô chứa Text : =COUNTIF(Data,”*”)


-Số ô Text chứa chính xác 3 ký tự : =COUNTIF(Data,”???”)


-Số ô chứa Text bắt đầu bằng chữ A : =COUNTIF(Data,”A*”)


-Số ô chứa từ đơn nào đó Td : “Dung” =COUNTIF(Data,”Dung”)


-Số ô chứa Text Dung ở bất cứ nơi nào trong Text :
=COUNTIF(Data,”*Dung*”)


-Số ô chứa ngày tháng hiện hành :
=COUNTIF(Data,TODAY())


-Số ô có giá trị lớn hơn giá trị trung bình :
=COUNTIF(Data,”>”&AVERAGE(Data))


-Số ô chứa giá trị lớn hơn 3 hoặc nhỏ hơn 2 :
=COUNTIF(Data,”>3″)+COUNTIF(Data,”<2″)


-Số ô chứa giá trị Logic (TRUE hoặc FALSE) :
=COUNTIF(Data,TRUE)+COUNTIF(Data,FALSE)


-Số ô chứa giá trị lỗi #N/A : =COUNTIF(Data,”#N/A”)




Ở phần trên, chúng ta đã tìm hiểu sơ qua một số thí dụ dùng hàm COUNTIF để đếm một số trường hợp. Hôm nay, chúng ta khai thác kỹ hơn qua các hàm kết hợp cùng với công thức mảng.

1/Đếm các ô chứa nội dung Text :

Để thuận tiện, tôi gọi mảng dữ liệu cần đếm là Data, và tôi muốn đếm các ô chứa chuỗi “DD”. Đơn giản nhất, ai cũng biết là hàm COUNTIF : =COUNTIF(Data,”DD”)
Công thức mảng sau cho kết quả giống hàm COUNTIF là :

{=SUM((Data=”DD”)*1)} hay {=SUM(IF(Data=”DD”,1))}

Nhưng công thức này không nhạy kiểu chữ, có nghĩa là “DD” hay “Dd” hay “dd” gì, nó cũng đếm hết. Nếu ta muốn đếm chính xác, chỉ những chuỗi “DD” mới tính, ta phải dùng hàm EXACT.

{=SUM(IF(EXACT(Data,”DD”),1))}

Hàm EXACT khi dùng trong công thức mảng, nó sẽ cho một mảng mới sau khi duyệt xong mảng Data gồm các phần tử TRUE và False, tương ứng với các ô trong mảng Data có chứa “DD” hay không, và khi kết hợp với hàm IF, nó tiếp tục tạo mảng gồm các phần tử 1, 0 tương ứng với TRUE và False. Nếu là TRUE (ứng với ô có chứa chuỗi “DD”) sẽ cho kết quả là 1, và hàm SUM sẽ cho tổng số các ô chứa chuỗi “DD” có trong mảng

2/Đếm các ô có chứa “DD” trong chuỗi :


Một ô chứa Text trong đó có chứa chuỗi “DD” sẽ gồm cả có thêm những từ khác đứng phía trước và phía sau. Khi đó, để đếm các ô này, ở trên, ta dùng công thức :

=COUNTIF(Data,”*DD*”) Nếu không muốn đếm các ô có chứa những từ có phía sau “DD” thì ta bỏ dấu * đằng sau đi.

Hay : =COUNTIF(Data,”*”&”DD”&”*”)

Cũng giống như trên, việc đếm này không nhạy kiểu chữ. Nếu muốn đếm chính xác, ta phải dùng công thức mảng sau :

{=SUM(IF(LEN(Data)-LEN(SUBSTITUTE(Data,”DD”,””))>0,1))}

Hàm SUBSTITUTE trong công thức mảng trên sẽ thay thế chuỗi “DD” trong mảng thành chuỗi rỗng. Ta có thể hiểu trong công thức mảng này, hàm LEN(Data) sẽ tính chiều dài của các chuỗi có trong từng ô của mảng. Hàm LEN(SUBSTITUTE(Data,”DD”,””)) sẽ cho kết quả là chiều dài các chuỗi không bao gồm chuỗi “DD” của từng ô trong mảng. Khi kết hợp cùng hàm IF trong công thức mảng , nó sẽ tạo một mảng mới gồm các phần tử 1, 0 tương ứng với hiệu số của hai hàm LEN ở trên (nếu >0, bao gồm ô đó có chứa chuỗi “DD”).
Chgúng ta sẽ tiếp tục vấn đề này vào dịp khác nhé. Chúc các bạn rút được nhiều ý tưởng hơn trong công việc hàng ngày từ những bài trao đổi trên





3/ Đếm tổng số lần xuất hiện của một chuỗi trong một dãy :

Để đếm tổng số lần xuất hiện của một chuỗi (td : DD) trong một dãy ô, ta sử dụng công thức mảng sau đây :
{=(SUM(LEN(Data))-SUM(LEN(SUBSTITUTE(Data,”DD”,””))))/LEN(“DD”)}
Những hàm trên đã được giới thiệu ở phần trước. Trong công thức mảng,ta có thể tóm tắt như sau :
SUM(LEN(Data)) : Cho biết chiều dài của tất cả các chuỗi ký tự có trong mảng
SUM(LEN(SUBSTITUTE(Data,”DD”,””) : Cho biết chiều dài của các ký tự còn lại trong mảng không phải là chuỗi “DD”
Hiệu số này dĩ nhiên cho ta kết quả là tổng số chiều dài của tất cả các chuỗi “DD” có trong mảng.
Và như vậy, khi ta chia tổng số chiều dài của tất cả các chuỗi “DD” có trong mảng cho chính độ dài của chuỗi “DD”, ta sẽ có số lần chuỗi “DD” xuất hiện trong mảng
Công thức này nhạy kiểu chữ. Nếu ta muốn công thức trên không nhạy kiểu chữ hoa, thường, ta có thể chỉnh sửa như sau : Ta dùng kết hợp thêm hàm UPPER và hàm LOWER để đổi tất cả các chữ có trong mảng thành một kiểu đồng nhất với nhau
{=(SUM(LEN(Data))-SUM(LEN(SUBSTITUTE(UPPER(Data),UPPER(“DD”),””))))/LEN(“DD”)}
Hay :
{=(SUM(LEN(Data))-SUM(LEN(SUBSTITUTE(LOWER(Data),LOWER(“DD”),””))))/LEN(“DD”)}

4/ Đếm mục thường xuất hiện nhiều nhất :

– Nếu là các ô chứa số hạng : ta có thể dùng hàm MODE, kết quả của hàm này sẽ cho ta biết số hạng nào là số hạng thường xuyên xuất hiện trong dãy nhiều nhất.
Td : Dãy Data từ A1:A10 có lần lượt các số hạng sau : {1,4,4,10,10,10,10,12,14,14}
Nhập công thức : =MODE(Data) sẽ cho kết quả là 10, vì số 10 xuất hiện 4 lần trong dãy.
Kết hợp : =COUNTIF(Data,MODE(Data)) sẽ đếm trong mảng Data, số lần xuất hiện của số 10 (do kết quả trả về của hàm MODE)

-Nếu các ô chứa Text lẫn các giá trị :

Khi đó, bạn nên sử dụng công thức mảng, đây là công thức mảng cho cùng kết quả với hàm COUNTIF trên :

{=MAX(COUNTIF(Data,Data))}





5/Đếm các ô bằng cách sử dụng nhiều tiêu chuẩn (Criteria) :

– Sử dụng tiêu chuẩn AND :

Một tiêu chuẩn AND đếm các ô nếu tất cả các điều kiện xác định được thỏa. Td : Đếm các ô chứa các giá trị >4 và nhỏ hơn 12 trong dãy có tên là Data. Ta sử dụng hàm COUNTIF
=COUNTIF(Data,”>4″)-COUNTIF(Data,”>12″).
Công thức trên cho ta cảm giác khó chịu vì ta muốn đếm các ô có chứa giá trị trong khỏang 4 đến 12, ta lại phải làm phép tính hiệu số giữa các ô có giá trị lớn hơn 12 và các ô có giá trị lớn hơn 4.
Nếu sử dụng công thức mảng, ta sẽ thấy rõ hơn sự tiện lợi của nó :
{=SUM((Data>4)*(Data<=12))}
Đôi khi, tiêu chuẩn đếm được dựa vào nhiều điều kiện ở các ô khác nhau, ngọai trừ các ô đang được đếm.
Td : Mảng có tên là MatHang, điều kiện MatHang = “M92”
Mảng có tên là TenNCC, điều kiện là “AA”
Mảng có tên là SoLuong, điều kiện là SoLuong >1000
Ta sẽ đếm số lần mua của NCC là AA,mặt hàng Xăng M92 có số lượng lớn hơn 1000 lít
{=SUM((Mathang=”M92″)*(TenNCC=”AA”)*(SoLuong>1000) )}
Hay :
{=SUM(IF(Mathang=”M92″,IF(TenNCC=”AA”,IF(SoLuong>1 000,1))))}
Bạn cũng có thể tránh dùng công thức mảng trên bằng cách dùng hàm SUMPRODUCT
=SUMPRODUCT((Mathang=”M92″)*(TenNCC=”AA”)*(SoLuong >1000))

– Sử dụng tiêu chuẩn OR :

Để đếm các ô bằng cách sử dụng một tiêu chuẩn OR, đôi khi bạn có thể sử dụng nhiều hàm COUNTIF. TD : Công thức sau đây đếm các số 1,3,5 trong dãy có tên là Data :
=COUNTIF(Data,1)+COUNTIF(Data,3)+COUNTIF(Data,5)
Bạn cũng có thể dùng hàm COUNTIF trong công thức mảng :
{=SUM(COUNTIF(Data,{1,3,5}))}
Nhưng nếu bạn muốn đếm các ô dựa vào tiêu chuẩn của các ô khác ngọai trừ các ô đang được đếm, hàm COUNTIF sẽ không thực hiện được. Như ví dụ trên, giả sử ta muốn đếm số lần mua hoặc mặt hàng là M92, hoặc của nhà CC là AA, hoặc những mặt hàng có số lượng lớn hơn 1000 lít. Ta phải sử dụng công thức mảng như sau :
{=SUM((MatHang=”M92″)+(TenNCC=”AA”)+(SoLuong>1000) )}
Hay :
{=SUM(IF((MatHang=”M92″)+(TenNCC=”AA”)+(SoLuong>10 00),1))}
Hay :
=SUMPRODUCT((MatHang=”M92″)+(TenNCC=”AA”)+(SoLuong >1000))

-Kết hợp tiêu chuẩn AND và OR :

Trong một số trường hợp, bạn cần kết hợp một số tiêu chuẩn AND và OR. Td : Cũng trường hợp trên, giả sử bạn muốn đếm số lần mua hoặc mặt hàng M92, hoặc mặt hàng M90, của nhà CC AA hoặc BB, và số ưuợng lớn hơn 1000 lít. Ta sẽ sử dụng công thức mảng như sau :

{=SUM(((MatHang=”M92)+(MatHang=”M90″))*((TenNCC=”A A”)+(TenNCC=”BB”))*(SoLuong>1000))}

Trên đây là các công thức dùng để đếm các ô kết hợp nhiều tiêu chuẩn với nhau. Nếu bạn đã nắm vững vấn đề này, thì việc tính tổng các số hạng thỏa mãn nhiều điều kiện sẽ trở nên dễ dàng và nhẹ nhàng rất nhiều. Chúc các bạn thành công

Read more

Sách VBA Excel 2016 power programming with vba (pdf)

Sách VBA Excel 2016 power programming with vba (pdf)

Sách VBA Excel 2016 power programming with vba (Mr. Spreadsheet’s Bookshelf) tác giả Richard Kusleika, Michael A. Alexander


vba excel ebook excel 2016 power programming with vba back

vba excel ebook excel 2016 power programming with vba back


Let Mr. Spreadsheet show you how to:


  • Go beyond recording simple macros
  • Dynamically manipulate workbooks and worksheets
  • Automate pivot table and chart features
  • Manage external data and wrangle external files
  • Send emails directly from Excel
  • Create your own Excel Ribbon interface
  • Develop and distribute your own Excel Add-ins
 

Maximize your Excel experience with VBA

 


Excel 2016 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2016. Encompassing an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA), this comprehensive book presents all of the techniques you need to develop both large and small Excel applications. Over 800 pages of tips, tricks, and best practices shed light on key topics, such as the Excel interface, file formats, enhanced interactivity with other Office applications, and improved collaboration features. In addition to the procedures, tips, and ideas that will expand your capabilities, this resource provides you with access to over 100 online example Excel workbooks and the Power Utility Pak, found on the Mr. Spreadsheet website.


Understanding how to leverage VBA to improve your Excel programming skills can enhance the quality of deliverables that you produce—and can help you take your career to the next level.


  • Explore fully updated content that offers comprehensive coverage through over 900 pages of tips, tricks, and techniques
  • Leverage templates and worksheets that put your new knowledge in action, and reinforce the skills introduced in the text
  • Access online resources, including the Power Utility Pak, that supplement the content
  • Improve your capabilities regarding Excel programming with VBA, unlocking more of your potential in the office

Excel 2016 Power Programming with VBA is a fundamental resource for intermediate to advanced users who want to polish their skills regarding spreadsheet applications using VBA.


Phiên bản sách Excel 2013 Power Programming with VBA link download bên dưới:


Sách VBA Excel 2013 power programming with vba (pdf)





Size 22.6 MiB





Language : English
File Type : PDF
Pdf Pages : 767










Download: Excel 2016 Power Programming with VBA (PDF)

Read more

Sách Excel Pivot Tables and Charts pdf

Sách Excel Pivot Tables and Charts pdf

Sách Programming Excel with VBA Flavio Morgado pdf


These are powerful, flexible, and complex tools, and most Excel users never tap their full potential. But you can, with Peter Aitken’s step-by-step tutorials, understandable instructions, and detailed explanations. You’ll learn to create and use PivotTables and charts from the simplest to the most complex.


Sách Excel 2007 VBA Programmer’s Reference pdf


This book will help you get more from Excel than ever before.


  • Chart Topper # 1 Creating a PivotTable Report
  • Chart Topper # 3 Using Special PivotTable(r) Tools
  • Chart Topper # 6 Understanding and Using PivotCharts
  • Chart Topper # 7 Working with Multidimensional Data
  • Chart Topper # 10 Programming PivotTables with VBA

Sách có liên quan:


Sách Excel 2013 Power Programming with VBA pdf


Microsoft Excel 2019 All in one for Dummies ( 8 books in one ) pdf


Sách VBA Excel 2016 power programming with vba (pdf)


 




Download Excel Pivot Tables and Charts pdf

Read more