Hướng dẫn count sheet excel python - đếm bảng excel python

22

New! Save questions or answers and organize your favorite content. Learn more.
Learn more.

I am reading a excel file using python.

import pandas as pd
import os

xls = pd.ExcelFile['D:\DirectoryProject\Mapping.xlsx']

It has several number of data sheets which I don't know. How can I count the total number of sheets in Mapping.xlsx file using Python?

jpp

152k32 gold badges259 silver badges319 bronze badges32 gold badges259 silver badges319 bronze badges

asked Jun 20, 2018 at 14:15Jun 20, 2018 at 14:15

openpyxl

import openpyxl

wb = openpyxl.load_workbook['file.xlsx'] 
res = len[wb.sheetnames]

import openpyxl

wb = openpyxl.load_workbook['file.xlsx'] 
res = len[wb.sheetnames]

0

import pandas as pd

xl = pd.ExcelFile['file.xlsx']
res = len[xl.sheet_names]

import openpyxl

wb = openpyxl.load_workbook['file.xlsx'] 
res = len[wb.sheetnames]

1

import xlrd

# use on_demand=True to avoid loading worksheet data into memory
wb = xlrd.open_workbook['file.xlsx', on_demand=True]
res = len[wb.sheet_names[]]  # or wb.nsheets

answered Jun 20, 2018 at 14:20Jun 20, 2018 at 14:20

jppjppjpp

152k32 gold badges259 silver badges319 bronze badges32 gold badges259 silver badges319 bronze badges

1

asked Jun 20, 2018 at 14:15

len[pd.read_excel[r"D:\DirectoryProject\Mapping.xlsx", sheet_name="None"]]

answered Jun 20, 2018 at 14:20

jppjpp

Just to add to the previous answer -5 gold badges37 silver badges78 bronze badges

This way you can get the number of sheets as well.Jan 28, 2020 at 11:33

neanderslobSankar

2,5055 gold badges37 silver badges78 bronze badges3 silver badges13 bronze badges

2

str.count[sub, start= 0, end=len[string]]

str1 = "vi du ham count trong Python, hoc lap trinh Python"
sub = "Py"
print ["str1.count[sub, 4, 40] : ", str1.count[sub, 10, 60]]
sub = "ham";
print ["str1.count[sub] : ", str1.count[sub]]

str1.count[sub, 4, 40] :  2
str1.count[sub] :  1

Hàm COUNTIF với ký tự đại diện [khớp một phần]Học Excel Online cùng các bạn sẽ tập trung vào  hàm COUNTIF cơ bản, được sử dụng để đếm ô với điều kiện tự thiết lập thông qua các ví dụ dưới đây.

• Cấu trúc hàm COUNTIF cơ bản
• Ứng dụng hàm Countif trong các chuỗi ô
  • Hàm COUNTIF cho văn bản và số [chính xác tuyệt đối]
  • Hàm COUNTIF với ký tự đại diện [khớp một phần]
  • Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định
• Ứng dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớp
• Sử dụng COUNTIF kết hợp những kí tự đại diện [wildcard]
• Sử dụng hàm COUNTIF đếm với các phép so sánh lớn nhỏ bằng
  • Ứng dụng hàm COUNTIF lọc giá trị trùng lặp
  • Sử dụng COUNTIF kết hợp AND hoặc OR
• Lời kết

Cấu trúc hàm COUNTIF cơ bản

Ứng dụng hàm Countif trong các chuỗi ôCOUNTIF có cấu trúc như sau:

Hàm COUNTIF cho văn bản và số [chính xác tuyệt đối]

Hàm COUNTIF với ký tự đại diện [khớp một phần]

Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định: Vùng chứa các ô muốn đếm

Ứng dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớpđiều kiện bạn muốn thiết lập dưới dạng một số, kí tự hoặc một logic sẽ chỉ định những giá trị nào sẽ được đếm

Ứng dụng hàm Countif trong các chuỗi ô

Hàm COUNTIF cho văn bản và số [chính xác tuyệt đối]

Hàm COUNTIF cho văn bản và số [chính xác tuyệt đối]

Hàm COUNTIF với ký tự đại diện [khớp một phần]= COUNTIF [C2: C15, “Roger Federer”]. Vì vậy, bạn nhập:

• Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định
• Ứng dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớp
• Sử dụng COUNTIF kết hợp những kí tự đại diện [wildcard]

Sử dụng hàm COUNTIF đếm với các phép so sánh lớn nhỏ bằngsử dụng tham chiếu đến bất kỳ ô nào chứa từ hoặc các từ đó và nhận được kết quả tương tự, ví dụ: = COUNTIF [C1: C9, C7].

Ứng dụng hàm COUNTIF lọc giá trị trùng lặp= COUNTIF [D2: D9,5] đếm các ô có giá trị 5 ở Cột D.

Hàm COUNTIF với ký tự đại diện [khớp một phần]

Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định

Ứng dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớp= COUNTIF [D2: D10, “*Brown *”].

Sử dụng COUNTIF kết hợp những kí tự đại diện [wildcard][*] được sử dụng thay thế các giá trị đầu và cuối, như được minh họa trong ví dụ trên. Nếu bạn cần phải khớp từng ký tự đơn, hãy nhập một dấu hỏi [?], như được trình bày dưới đây.

Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định

Ứng dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớp

Nếu bạn muốn biết số ô bắt đầu hoặc kết thúc bằng văn bản nhất định bất kể có bao nhiêu ký tự khác trong ô, hãy sử dụng các công thức sau:

= COUNTIF [C2: C10, “Mr*”] – tính các ô bắt đầu bằng “Mr”.Mr*”] – tính các ô bắt đầu bằng “Mr”.

= COUNTIF [C2: C10, “* ed”] – tính các ô kết thúc bằng chữ “ed”. – tính các ô kết thúc bằng chữ “ed”.

Hình ảnh dưới đây minh họa cho công thức thứ hai:

Nếu bạn đang tìm kiếm một số ô bắt đầu hoặc kết thúc bằng các ký tự nhất định và chứa một số ký tự nhất định, bạn sử dụng hàm COUNTIF với dấu hỏi [?] tại điều kiện:

= COUNTIF [D2: D9, “??own”] – đếm số ô kết thúc bằng chữ “own” và có chính xác 5 ký tự trong các ô từ D2 đến D9, bao gồm cả khoảng cách. – đếm số ô kết thúc bằng chữ “own” và có chính xác 5 ký tự trong các ô từ D2 đến D9, bao gồm cả khoảng cách.

= COUNTIF [D2: D9, “Mr ??????”] – đếm số ô bắt đầu bằng chữ “Mr” và có chính xác 8 ký tự trong các ô từ D2 đến D9, bao gồm khoảng cách. – đếm số ô bắt đầu bằng chữ “Mr” và có chính xác 8 ký tự trong các ô từ D2 đến D9, bao gồm khoảng cách.

Mẹo. Để tìm số ô chứa dấu hỏi “?” hoặc dấu hoa thị “*”, hãy gõ dấu ngã [~] trước dấu ? hoặc * trong công thức. Ví dụ, = COUNTIF [D2: D9, “* ~? *”] để đếm tất cả các ô có chứa dấu chấm hỏi trong dải D2: D9.. Để tìm số ô chứa dấu hỏi “?” hoặc dấu hoa thị “*”, hãy gõ dấu ngã [~] trước dấu ? hoặc * trong công thức. Ví dụ, = COUNTIF [D2: D9, “* ~? *”] để đếm tất cả các ô có chứa dấu chấm hỏi trong dải D2: D9.

Ứng dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớp

Hãy tham khảo ví dụ dưới đây:

[iframe width=”700″ height=”689″ frameborder=”0″ scrolling=”no” src=”//onedrive.live.com/embed?resid=B21F9331F704AFCF%21115&authkey=%21AGBxOyCOXzaOtds&em=2&wdAllowInteractivity=False&AllowTyping=True&Item=’%C4%90%E1%BA%BFm%20ch%C3%ADnh%20x%C3%A1c’!A1%3AN32&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True”]

Tại đây, chúng ta sẽ sử dụng hàm COUNTIF để đếm những ô chứa giá trị thỏa mãn trùng khớp với điều kiện. Cú pháp tương ứng trong ví dụ:

=COUNTIF[$C$2:$C$31,$I$8] hoặc =COUNTIF[$C$2:$C$31,”Nam”] – đếm số lượng ô chứa giá trị “Nam” trong vùng C2 đến C31 hoặc =COUNTIF[$C$2:$C$31,”Nam”] – đếm số lượng ô chứa giá trị “Nam” trong vùng C2 đến C31

=COUNTIF[$D$2:$D$31,I11] hoặc =COUNTIF[$D$2:$D$31,18] – đếm số lượng ô chứa giá trị “18” trong vùng C2 đến C31.hoặc =COUNTIF[$D$2:$D$31,18] – đếm số lượng ô chứa giá trị “18” trong vùng C2 đến C31.

Lưu ý: Criteria không phân biệt chữ hoa và chữ thường. Bởi vậy, Nam hay NAM, nam thì cũng như nhau thôi. Trong nhiều trường hợp, nó rất tiện vì bạn không phải xử lí chính xác dữ liệu, nhưng trong nhiều trường hợp, nó lại ảnh hưởng rất nhiều tới việc tìm kiếm. Vậy xử lí thế nào? Có rất nhiều cách. Ở đây, mình sẽ gợi ý một hướng đi, đó là thay vì đếm “Nam”, “NAM”, “nam”, hãy đếm TRUE và FALSE kết hợp cùng hàm EXACT. Cụ thể ra sao, các bạn hãy thử suy nghĩ và tự thực hành lời giải nhé.TRUE và FALSE kết hợp cùng hàm EXACT. Cụ thể ra sao, các bạn hãy thử suy nghĩ và tự thực hành lời giải nhé.

Không hề khó nhớ phải không? Giờ ta sẽ đến với những “biến thể” ở những ví dụ tiếp theo.

Sử dụng COUNTIF kết hợp những kí tự đại diện [wildcard]

Kí tự đại diện [Wildcard] được sử dụng để thay thế các kí tự khác trong ô. Có 3 kí tự đại diện là dấu sao [*], dấu hỏi [?], dấu ngã [~].được sử dụng để thay thế các kí tự khác trong ô. Có 3 kí tự đại diện là dấu sao [*], dấu hỏi [?], dấu ngã [~].

*Chú ý: Wildcard có thể thay thế mọi giá trị trong chuỗi kí tự, nhưng không được dùng để thay thế đối với định dạng số [Number]. Nếu gặp trường hợp như vậy, bạn hãy chuyển toàn bộ về dạng TEXT bằng hàm TEXT, sau đó kiểm tra lại bằng hàm ISTEXT và cuối cùng COUNTIF kết hợp wildcard như bình thường.Chú ý: Wildcard có thể thay thế mọi giá trị trong chuỗi kí tự, nhưng không được dùng để thay thế đối với định dạng số [Number]. Nếu gặp trường hợp như vậy, bạn hãy chuyển toàn bộ về dạng TEXT bằng hàm TEXT, sau đó kiểm tra lại bằng hàm ISTEXT và cuối cùng COUNTIF kết hợp wildcard như bình thường.

Hãy cùng xem ví dụ:

[iframe width=”700″ height=”689″ frameborder=”0″ scrolling=”no” src=”//onedrive.live.com/embed?resid=B21F9331F704AFCF%21115&authkey=%21AGBxOyCOXzaOtds&em=2&wdAllowInteractivity=False&AllowTyping=True&Item=’%C4%90%E1%BA%BFm%20v%E1%BB%9Bi%20k%C3%AD%20t%E1%BB%B1%20%C4%91%E1%BA%A1i%20di%E1%BB%87n’!A1%3AN32&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True”]

 Trong ví dụ này, ta sẽ đếm cho 2 trường hợp: những người có họ Lê; những người có tên Anh. Lê; những người có tên Anh. 

Đầu tiên, ta sử dụng dấu [*] thay thế cho cụm kí tự. Đối với những người họ Lê, ta sử dụng cú pháp “Lê*” tại vùng điều kiện; hoặc $I$8 & “*” nếu tham chiếu tới ô I8. Nếu các bạn còn khúc mắc về các kí tự đại diện, xem thêm bài viết về Kí tự đại diện Wildcard ngayLê, ta sử dụng cú pháp “Lê*” tại vùng điều kiện; hoặc $I$8 & “*” nếu tham chiếu tới ô I8. Nếu các bạn còn khúc mắc về các kí tự đại diện, xem thêm bài viết về Kí tự đại diện Wildcard ngay

Kết hợp hàm COUNTIF cơ bản, ta có thể  đếm được số lượng ô thỏa mãn điều kiện.hàm COUNTIF cơ bản, ta có thể  đếm được số lượng ô thỏa mãn điều kiện.

Bây giờ, hãy đến với ví dụ thứ 3

Sử dụng hàm COUNTIF đếm với các phép so sánh lớn nhỏ bằng

[iframe width=”700″ height=”689″ frameborder=”0″ scrolling=”no” src=”//onedrive.live.com/embed?resid=B21F9331F704AFCF%21115&authkey=%21AGBxOyCOXzaOtds&em=2&wdAllowInteractivity=False&AllowTyping=True&Item=’%C4%90%E1%BA%BFm%20v%E1%BB%9Bi%20to%C3%A1n%20t%E1%BB%AD%20l%E1%BB%9Bn%20nh%E1%BB%8F%20b%E1%BA%B1ng’!A1%3AN32&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True”]

Đối với việc đếm sử dụng điều kiện là các phép so sánh lớn hơn [>], nhỏ hơn [điều kiện trực tiếp và điều kiện tham chiếu.

Với điều kiện trực tiếp ta đặt toàn bộ trong dấu nháy kép như sau: “>=a” với >= là phép so sánh, a là giá trị số được so sánh.điều kiện trực tiếp ta đặt toàn bộ trong dấu nháy kép như sau: “>=a” với >= là phép so sánh, a là giá trị số được so sánh.

Với điều kiện tham chiếu ta đặt phép so sánh trong dấu nháy kép, kết hợp cùng kí tự &: “>=”& I8 với >= là phép so sánh, I8 là ô được tham chiếu tới.điều kiện tham chiếu ta đặt phép so sánh trong dấu nháy kép, kết hợp cùng kí tự &: “>=”& I8 với >= là phép so sánh, I8 là ô được tham chiếu tới.

Qua 3 ví dụ trên, chắc hẳn các bạn đã bắt đầu hiểu rõ hơn về hàm COUNTIF rồi phải không? Giờ hãy tìm hiểu ứng dụng của hàm này một chút với ví dụ cuối cùng nhé.

Ứng dụng hàm COUNTIF lọc giá trị trùng lặp

[iframe width=”700″ height=”689″ frameborder=”0″ scrolling=”no” src=”//onedrive.live.com/embed?resid=B21F9331F704AFCF%21115&authkey=%21AGBxOyCOXzaOtds&em=2&wdAllowInteractivity=False&Item=’%C4%90%E1%BA%BFm%20tr%C3%B9ng’!A1%3AN32&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True”]

Ví dụ trên là một ứng dụng đơn giản của hàm COUNTIF trong các hoạt động như lọc email khách hàng, lọc tên… bị trùng lặp. Có lẽ tới đây, các bạn đều đã nhận ra cách làm rồi phải không? Hãy tính số lần xuất hiện của giá trị muốn tìm trong cột tổng và chuyển bài toán về lọc các giá trị trả về theo số tự nhiên.

Sử dụng COUNTIF kết hợp AND hoặc OR

Với cú pháp của hà Countif

=COUNTIF[range, criteria]

Rõ ràng là criteria ở đây là hàm AND, và điều kiện ta đã liệt kê ra đầy đủ rồi, phải không?AND, và điều kiện ta đã liệt kê ra đầy đủ rồi, phải không?

Ta hãy xem qua hàm AND một chút:AND một chút:

Vấn đề ở đây là:

1. Hàm AND [và cả OR] sẽ trả về giá trị là TRUE hoặc FALSE.AND [và cả OR] sẽ trả về giá trị là TRUE hoặc FALSE.
2. Logic so sánh trong hàm AND. Cái gì sẽ lớn hơn, hoặc nhỏ hơn?AND. Cái gì sẽ lớn hơn, hoặc nhỏ hơn?

Khi đưa AND hoặc OR vào COUNTIF, đối số range của bạn sẽ được so với criteria là TRUE hoặc FALSE [hoặc #VALUE!]. Rõ ràng kết quả sẽ trả về 0 rồi.AND hoặc OR vào COUNTIF, đối số range của bạn sẽ được so với criteria là TRUE hoặc FALSE [hoặc #VALUE!]. Rõ ràng kết quả sẽ trả về 0 rồi.

Vậy ta khắc phục thế nào?

Cách 1: Sử dụng hàm COUNTIFS thay thế

Hàm COUNTIFS cho phép đếm với nhiều điều kiện. Tại sao phải cố nhồi nhét vào COUNTIF trong khi có thể dùng hàm COUNTIFS.hàm COUNTIFS.

Cách 2: Lập cột phụ

Hướng làm mà Học Excel Online gợi ý như sau: Lập một cột phụ dùng hàm And để trả về giá trị True hoặc False, sau đó sử dụng COUNTIF với cột phụ.True hoặc False, sau đó sử dụng COUNTIF với cột phụ.

Cách 3: Sử dụng logic toán học

Xem thêm: Ứng dụng hàm COUNTIF đếm giá trị đúng saiỨng dụng hàm COUNTIF đếm giá trị đúng sai

Video Hướng dẫn Cách sử dụng hàm countif trong excel

Lời kết

Đừng quên trên đây chỉ là những ví dụ cơ bản nhất của COUNTIF. Còn rất nhiều ứng dụng đang chờ các bạn khám phá, bởi vậy nên hãy luyện tập thường xuyên nhé!

Chúc các bạn học tốt.