Bạn đang xem bài viết Hướng Dẫn Cách Đếm Các Ký Tự Trong Một Ô (Hoặc Phạm Vi Ô) Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung 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.
Trong Excel mặc định có hàm LEN có thể sử dụng để đếm số ký tự trong một ô, nhưng bạn cũng có thể kết hợp với các hàm khác để tính tổng số ký tự trong một ô, phạm vi hoặc số lần một ký tự cụ thể xuất hiện trong một ô.
Đếm tất cả các ký tự trong một ôNếu bạn chỉ muốn biết tổng tất cả các ký tự trong một ô của Excel thì có thể sử dụng hàm LEN. Hàm LEN nhận một đối số, có thể là văn bản trong dấu ngoặc kép hoặc tham chiếu ô đến ô có văn bản.
= LEN (A2)
Một mình hàm LEN tách riêng trong Excel có thể không thực hiện được nhiều tính năng, nhưng khi kết hợp với các hàm khác thì có thể thực hiện được những tính năng ấn tượng như: Đếm số từ trong một ô hoặc tách họ và tên.
Lưu ý: Hàm LEN sẽ đếm tất cả các ký tự trong một ô, có thể là ký tự đặc biệt, số, dấu chấm câu và ký tự khoảng trắng (đầu, cuối và dấu cách kép giữa các từ).
Vì hàm LEN đếm mọi ký tự trong một ô nên đôi khi bạn có thể nhận được kết quả sai trong trường hợp có thêm khoảng trắng trong ô.
Để tránh việc sử dụng hàm LEN đếm các khoảng trắng thừa, trước tiên bạn có thể sử dụng hàm TRIM để loại bỏ bất kỳ khoảng trắng đầu, cuối và kép nào trong ô, sau đó sử dụng hàm LEN để tính số từ thực tế.
= LEN (TRIM (A2))
Đếm tất cả các ký tự trong một dải ôÝ nghĩa của công thức trên sẽ là
Bạn cũng có thể sử dụng hàm LEN để đếm tổng số ký tự trong toàn bộ phạm vi.
Ví dụ bạn có một bảng dữ liệu, thay vì đếm số ký tự trong mỗi ô bạn muốn biết có bao nhiêu ký tự trong toàn bộ phạm vi.
= SUMPRODUCT (LEN (A2: A7)))
– Đầu tiên là hàm LEN trong công thức sẽ lấy toàn bộ phạm vi ô và đếm các ký tự trong mỗi ô. Lúc này kết quả của hàm LEN sẽ là {22; 21; 23; 23; 23; 31} và mỗi số này đại diện cho số ký tự trong ô.
Do đó khi bạn sử dụng hàm SUMPRODUCT kết hợp thì hàm LEN sẽ chỉ cần thêm tất cả các số này.
Có thể có bạn thắc mắc tại sao không dùng hàm SUM thay vì hàm SUMPRODUCT là dó đây là một mảng và hàm SUMPRODUCT có thể xử lý mảng nhưng hàm SUM thì không.
= SUM(LEN(A2: A7))
Đếm các ký tự cụ thể trong một ôNhư đã nói ở trên, tiện ích thực sự của hàm LEN là khi được sử dụng kết hợp với các hàm khác trong Excel. Nếu bạn muốn đếm các ký tự cụ thể trong một ô (đó có thể là một chữ cái, số, ký tự đặc biệt hoặc ký tự khoảng trắng), bạn có thể thực hiện điều đó bằng tổ hợp công thức.
Mặc dù không có công thức tích hợp nào để tính số từ nhưng bạn có thể đếm các ký tự khoảng trắng và sau đó sử dụng công thức đó để biết tổng số từ trong ô.
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))+1
Công thức trên sẽ đếm tổng số ký tự khoảng trắng và sau đó thêm 1 vào số đó để có số từ và đây là cách công thức này hoạt động:
– Hàm SUBSTITUTE được sử dụng để thay thế tất cả các ký tự khoảng trắng bằng một khoảng trống. Sau đó, hàm LEN được sử dụng để đếm tổng số ký tự khi không có ký tự khoảng trắng.
– Kết quả của hàm LEN(SUBSTITUTE(A2,” “,””)) sau đó được trừ cho LEN(A2). Điều này sẽ cho tổng số ký tự khoảng trắng có trong ô.
– Số 1 được thêm vào công thức và tổng số từ sẽ nhiều hơn tổng số ký tự khoảng trắng (vì hai từ được phân tách bằng một ký tự).
Lưu ý: Trong trường hợp có bất kỳ dấu cách nào ở đầu, ở cuối hoặc kép, bạn sẽ nhận được kết quả sai. Trong trường hợp này, tốt nhất nên sử dụng hàm TRIM cùng với hàm LEN.
Bạn cũng có thể sử dụng logic tương tự để tìm một ký tự, từ hoặc cụm từ cụ thể trong một ô.
= LEN (B2) -LEN (SUBSTITUTE (B2, “A”, “”))
Công thức trên một lần nữa sử dụng cùng một logic – tìm độ dài của văn bản trong một ô có và không có ký tự mà bạn muốn đếm, sau đó lấy hiệu của hai ký tự này.
Đếm các ký tự cụ thể bằng công thức phân biệt chữ hoa chữ thườngCó một vấn đề với công thức được sử dụng để đếm các ký tự cụ thể trong một ô đó là hàm SUBSTITUTE không phân biệt được chữ hoa chữ thường. Điều này có nghĩa là chữ “A” không bằng “a”. Đây là lý do tại sao bạn nhận được kết quả sai trong ô C5 (kết quả đáng lẽ phải là 3).
=LEN(B2)-LEN(SUBSTITUTE(B2,”A”,””))+LEN(B2)-LEN(SUBSTITUTE(B2,”a”,””))
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Thực hiện tương tự và thay thế ký tự A, a bằng các ký tự khác khi bạn muốn kiểm tra.
Đếm ký tự hoặc chữ số trước và sau số thập phânĐếm ký tự hoặc chữ số trước số thập phân:
= LEN(INT(A2))
Đếm ký tự hoặc chữ số sau số thập phân:
=LEN(A2)-FIND(“.”,A2)
Lưu ý: Các công thức này chỉ dành cho các chữ số có nghĩa trong ô. Trong trường hợp bạn có các số 0 ở đầu hoặc ở cuối hoặc đã sử dụng định dạng số tùy chỉnh để hiển thị nhiều hơn hoặc ít hơn các số, các công thức trên sẽ vẫn cung cấp cho bạn các chữ số có nghĩa trước và sau số thập phân.
Hướng dẫn cách sắp xếp dữ liệu trong Excel theo màu chi tiết 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 xử lý lỗi dữ liệu dạng số trong Excel nhanh chóng
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
Hướng Dẫn Cách Tạo Ghi Chú Trong Một Ô Hoặc Một Vùng Ô Trên Excel
Đôi khi chúng ta phải làm file Excel và để người khác sử dụng, nhập dữ liệu. Vậy có cách nào để giúp họ biết được cần lưu ý những gì khi nhập dữ liệu trong 1 ô hoặc cả 1 vùng ô? Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn 2 cách để có thể tạo ghi chú trong một ô hoặc 1 vùng ô trên Excel.
Sử dụng chức năng tạo Ghi chú trong ôĐây là cách làm khá phổ biến, thường áp dụng khi bạn muốn tạo 1 ghi chú trong 1 ô. Cách làm như sau:
Chọn ô bạn muốn tạo ghi chú
Bấm chuột phải trong ô đó, chọn New Note (trong một số phiên bản có thể là Insert Comment)
Nhập nội dung bạn muốn ghi chú
Ví dụ như sau:
Trong hình trên ta có:
ô C4 là ô muốn tạo ghi chú
Khi chọn New Note trong ô C4, xuất hiện vùng màu vàng nhạt để ghi nội dung ghi chú. Trong vùng gồm 2 phần: Người ghi chú (được tô đậm, kèm dấu hai chấm) và nội dung ghi chú (nội dung phía sau người ghi chú)
Ô được ghi chú sẽ xuất hiện ký hiệu tam giác đỏ ở góc trên bên phải của ô
Khi đưa chuột qua ô đó (có thể chọn hoặc không chọn ô được ghi chú) thì nội dung ghi chú trong ô vẫn hiện lên
1. Việc ghi chú không ảnh hưởng tới việc giám sát nội dung dữ liệu được phép nhập vào trong ô
2. Muốn xóa ghi chú, bạn bấm chuột phải trong ô đã thiết lập ghi chú và chọn Delete Comment (Delete Note)
3. Bạn không thể thiết lập ghi chú bằng Note/Comment cho cả 1 vùng ô. Khi áp dụng cho 1 vùng ô thì chỉ có ô đầu tiên trong vùng đó nhận được ghi chú.
Sử dụng chức năng Data Validation để tạo ghi chú trong ô, vùng ôĐể thiết lập ghi chú cho cả 1 vùng ô, mà ô nào trong vùng cũng có ghi chú thì chúng ta có thể sử dụng phương pháp này.
Trong chức năng Data Validation – Thiết lập điều kiện nhập dữ liệu chúng ta có 3 nội dung:
2-Input Message: Thiết lập thông báo về điều kiện nhập dữ liệu
3-Error Alert: Thiết lập thông báo lỗi nếu dữ liệu được nhập không đúng theo điều kiện đã quy định trong mục 1-Settings
Với thiết lập 2-Input Message, chúng ta có thể tạo ghi chú trong ô mỗi khi chọn vào ô đó như sau:
2. Chỉ khi chọn vào ô áp dụng chức năng Data Validation thì thông báo mới xuất hiện (khi không chọn ô đó thì không có thông báo)
3. Có thể áp dụng cùng lúc cho nhiều ô, vùng ô với cùng 1 thiết lập giống nhau.
4. Ô được thiết lập Data Validation không có ký hiệu tam giác màu đỏ ở góc ô, nhưng vẫn hiện thông báo.
5. Có thể tắt chức năng hiện thông báo này bằng cách bỏ đánh dấu tích vào mục ” Show input message when cell is selected “
Hướng dẫn cách tạo danh sách động trong Excel với hàm OFFSET Hướng dẫn cách giới hạn số lượng kí tự trong 1 ô Excel Cách thiết lập chỉ nhập dữ liệu dạng ngày tháng trong ô trên Excel
Hướng Dẫn Các Cách Đếm Ô Màu Trong Excel
SỬ DỤNG HÀM SUBTOTAL VÀ CÔNG CỤ LỌC THEO MÀU
Cách này được thực hiện dựa trên 2 nguyên tắc sau:
Hàm SUBTOTAL là một hàm được đánh giá rất linh hoạt trong Excel. Bạn có thể dùng hàm này trong việc tính tổng, đếm… đặc biệt hơn là cho phép bỏ qua các giá trị bị ẩn đi khi lọc bằng Filter hay sử dụng chức năng ẩn dòng (Hide).
Công cụ lọc (Filter) trong Excel thì có khả năng hỗ trợ bạn lọc theo các ô được tô màu.
Khi kết hợp 2 tính năng này, bạn sẽ thấy khi lọc theo màu, kết quả là sẽ còn lại các ô được tô màu theo đúng màu được lọc. Trong khi đó, những ô có màu khác sẽ được bỏ qua. Khi dùng hàm SUBTOTAL để đếm các ô còn lại sẽ cho bạn kết quả như ý muốn.
Bước 1: Dùng hàm SUBTOTAL để đếm.
Dùng hàm SUBTOTAL tại vị trí ô C11:
=SUBTOTAL(102, C2:C10)
Trong đó:
Tham số 102 là dùng cho hàm COUNT để đếm
Vùng ô cần đếm là vùng C2:C10
Kết quả thu được = 9 là 9 ô.
Bước 2: Lọc dữ liệu theo màu và xem kết quả
Tiếp theo đó, chúng ta sử dụng công cụ Filter (Auto Filter) để lọc dữ liệu theo màu sắc.
Chọn thẻ Data chọn Filter để áp dụng bộ lọc cho bảng
Tiếp tục chọn thực hiện lọc theo màu với chức năng Filter by Color:
Kết quả sau khi lọc theo màu như sau:
Các bạn có thể áp dụng tương tự bộ lọc này cho các màu khác để xem kết quả tự thay đổi cho màu đó.
SỬ DỤNG HÀM COUNTIF KẾT HỢP VỚI HÀM chúng tôiHàm chúng tôi là 1 hàm ẩn trong Excel, bình thường chúng ta sẽ không gọi hàm này lên được. Tuy nhiên có 1 cách để sử dụng đó là dùng hàm thông qua việc đặt tên vùng (Name Range).
Hàm chúng tôi để xác định màu trong ô:
Bước 1: Trong thẻ Formulas, chọn Define Name, thao tác đặt tên vùng như sau:
Trong thẻ Refers to: viết hàm =GET.CELL(38, Sheet2!$C2)
+ 38 là tham số xác định mã màu nền trong ô
+ Sheet2!$C2 là ô C2 tại Sheet2 (ô bắt đầu), với cố định cột C, không cố định dòng 2 (để hàm có thể sử dụng cho các dòng khác trong cột C)
Bước 2: Sau khi đặt tên, chúng ta sẽ đặt công thức tại cột D (cột phụ làm căn cứ xác định kết quả của hàm Get.Cell)
=MaMauNen
Đây là tên đã đặt ở bước 1. Filldown công thức từ ô D2 tới ô D10, ta sẽ có được kết quả mã màu nền được sử dụng trong các ô.
Lưu ý:
Hàm MaMauNen không nên dùng trực tiếp được mà phải dùng thông qua name range
Tham chiếu khi đặt tên là ô sẽ trả về kết quả mã màu
Hàm chỉ xác định màu nền được tô trực tiếp với Fill Color, không xác định được màu do chức năng Conditional formatting tạo ra.
Cuối cùng bạn muốn đếm ô theo màu nào chỉ cần dựa trên kết quả mã màu đã có để đếm (đếm theo kết quả số trong cột D với hàm COUNTIF, điều kiện là mã màu đã có)
Cách Đếm Số Ô Tính Chứa Văn Bản Hoặc Ký Tự Trong Excel 2010,2013,2016
Trong bài viết này, blog học Excel Online sẽ hướng dẫn bạn cách đếm số ô tính chứa nội dung dạng text và ký tự trong Excel 2010-2013 bằng một số công thức đếm ký tự trong một hay nhiều ô tính, giới hạn số ký tự trong ô và cách tìm những ô có chứa nội dung cụ thể.
Đếm ô tính chứa nội dung là dạng textNếu bạn cần đếm số ô tính chứa nội dung là dạng text trong Excel, bạn có thể sử dụng hàm COUNTIF:
Trong đó, A2:D10 là địa chỉ vùng dữ liệu cần đếm và “*” là dấu hoa thị đại diện cho bất kỳ số lượng ký tự nào.
Chú ý. Giá trị Logic TRUE/FALSE sẽ không được đếm như dạng text. Thêm vào đó, những con số cũng không được đếm bởi “*” nếu chúng không được định dạng text bắt đầu với dấu nháy đơn (‘). Những ô rỗng bắt đầu với dấu nháy đơn (‘) sẽ được đếm.
Một công thức nữa bạn có thể sử dụng để đếm số ô tính chứa nội dung định dạng text là:
Một công thức khác bạn có thể sử dụng nữa là công thức mảng (cần nhấn Ctrl + Shift + Enter để hiển thị kết quả). Bạn có thể sử dụng hàm SUMPRODUCT để đếm những giá trị dạng text cùng với hàm ISTEXT như sau:
Hai dấu gạch nối ràng buộc kết quả của hàm ISTEXT là giá trị logic TRUE hoặc FALSE, là giá trị 1 hoặc 0. SUMPRODUCT sẽ cộng các giá trị này lại với nhau và cho kết quả.
Công thức Excel để đếm số ký tự trong một ô tính
= LEN(A1)
Trong công thức này, A1 là một ô tính chứa số ký tự cần tính.
Trọng điểm là Excel giới hạn số ký tự. Ví dụ, tiêu đề Excel không qua 254 kí tự. Nếu bạn vượt quá giới hạn này, tiêu đề sẽ bị cắt. Công thức sẽ rất hữu ích khi bạn có một chuỗi ký tự dài trong ô tính và cần đảm bảo rằng ô tính của bạn không vượt quá 254 kí tự để tránh vấn đề nhập hoặc hiển thị bảng từ nguồn khác.
Đếm ký tự trong nhiều ô tínhBạn cũng cũng cần đếm số ký tự nhiều ô tính. Trong trường hợp này, hãy nhập công thức:
=SUM(LEN(range))
Chú ý. Công thức phía trên cần nhập dưới dạng công thức mảng. Để hiển thị kết quả, bạn cần nhấn Ctrl + Shift + Enter.
Đếm những ký tự xác định trong một ô tính bằng công thức ExcelNếu bạn cần biết số lần xuất hiện của một ký tự nhất định trong ô tính, hoặc nếu bạn muốn kiểm tra ô tính có chứa ký tự không phù hợp hay không, sử dụng công thức sau:
=LEN(A1)-LEN(SUBSTITUTE(A1,”a”,””))
Ký tự “a” là ký tự bạn cần đếm trong Excel.
Nếu bạn muốn đếm số lần xuất hiện một ký tự trong dãy trong nhiều ô tính hoặc một cột, bạn cần tạo cột phụ và dán vào đó công thức tôi vừa giới thiệu ở phần trước =LEN(A1)-LEN(SUBSTITUTE(A1,”a”,””)). Sau đó bạn có thể sao chép công thức này sang các cột, cộng tổng cột này và bạn sẽ được kết quả mong muốn. Dường như khá mất thời gian phải không?
May mắn là, bạn có thể đếm số ký tự nhất định trong một dãy bằng cách sử dụng công thức mảng sau:
=SUM(LEN(range)-LEN(SUBSTITUTE(range,”a”,””)))
Chú ý. Công thức trên là công thức dạng mảng. Hãy chắc chắn rằng bạn nhấn Ctrl + Shift + Enter để hiển thị kết quả.
Công thức mảng sau sẽ giúp bạn đếm số lần xuất hiện của dự liệu dạng text trong một dãy:
=SUM((LEN(C2:D66)-LEN(SUBSTITUE(C2:D66,”Excel”,””)))/LEN(“Excel”))
Ví dụ, bạn có thể đếm số lần xuất hiện của “Excel” trong bảng. Đừng quên dấu cách hoặc hàm cũng sẽ đếm những từ bắt đầu với dữ liệu dạng text đó, không phải những từ riêng lẻ.
Giới hạn ký tự Excel cho ô tính
Excel giới hạn số lượng ký tự được nhập vào một ô.
Tổng số ký tự một ô tính có thể chứa là 32.767.
Một ô tính chỉ có thể hiển thị 1.024 ký tự. Bên cạnh đó, thanh công thức chỉ có thể hiển thị 32.767 ký tự.
Độ dài lớn nhất của dãy công thức là 1.014 đối với Excel 2003. Excel 2007-2013 có thể chứa 8.192 ký tự.
Cập nhật thông tin chi tiết về Hướng Dẫn Cách Đếm Các Ký Tự Trong Một Ô (Hoặc Phạm Vi Ô) Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!