VinaOnline.net

Lọc dữ liệu trong Excel phù hợp với yêu cầu

Loc-du-lieu trong Excel

Lọc dữ liệu trong Excel là hiển thị một số mẫu tin nào đó theo yêu cầu đặt ra. Nếu thực hiện công việc thủ công sẽ mất rất nhiều thời gian và dễ dẫn tới sai sót. Hôm nay, mình cùng bạn tìm hiểu lọc dữ liệu theo Excel để phục vụ công việc tốt nhất có thể.

Loc-du-lieu-trong-Excel-Bang-tinh-minh-hoa

BÀI VIẾT THAM KHẢO

I. Tìm hiểu từ ngữ lọc dữ liệu trong Excel

1. Text Filters

Thực hiện trên các giá trị chuỗi. Sử dụng cột “Chức vụ” để minh họa cho mục này.

Loc-du-lieu-trong-Excel-Text-Filters

a. Chọn trực tiếp

Nếu bạn bỏ chọn (không tick) vào mục nào (trong cột Chức vụ) thì dữ liệu bảng tính sẽ không hiển thị những mẫu tin tương ứng.

Ví dụ: Bạn chọn hai mục PGĐ

Loc-du-lieu-trong-Excel-Vi-du-1

Kết quả như sau:

Loc-du-lieu-trong-Excel-Ket-qua-Vi-du-1

Nếu chọn hết, đồng nghĩa bạn chưa lọc cho cột này (cột Chức vụ).

b. Chọn theo điều kiện

Nếu chọn Text Filters sẽ xuất hiện các mục như sau:

Loc-du-lieu-trong-Excel-Cac-muc-trong-Text-Filters
  • Equals: Bằng.

Mục này sẽ hiển thị kết quả đúng với chuỗi được chọn.

– Chọn Chức vụ là GĐ

Loc-du-lieu-trong-Excel-Custom-Text-Filters 1

Tương đương

Loc-du-lieu-trong-Excel-Custom-Text-Filters-1b

– Chọn Chức vụ là GĐ hoặc PGĐ

Loc-du-lieu-trong-Excel-Custom-Text-Filters-2
  • Does Not Equal: Không bằng, điều kiện này sẽ ngược với Equals.
  • Begins With: Bắt đầu với.
  • Ends With: Kết thúc (cuối) với
  • Contains: Chứa
  • Does Not Contain: Không chứa.
  • Custom Filter: Thiết lập điều kiện lọc theo yêu cầu
Loc-du-lieu-trong-Excel-Custom-Text-Filters-3

Chứa các tùy chọn lọc cho chuỗi (Text Filters) và cho số (Number Filters).

Chú ý:

Muốn hiển thị đầy đủ dữ liệu của bảng tính thì chọn mục “Clear Filter From …” đóng khung màu xanh của hình mục I.1.

2. Number Filters

Thực hiện trên các giá trị số, tiền tệ,…

Thực hiện cột Lương cơ bản để minh họa.

Loc-du-lieu-trong-Excel-Cac-muc-trong-Number-Filters

Một số điều kiện chưa có trong Text Filters.

  • Greater Than: Lớn hơn
  • Greater Than Or Equal To: Lớn hơn hoặc bằng.
  • Less Than: Nhỏ hơn.
  • Less Than Or Equal To: Nhỏ hơn hoặc bằng.
  • Between: Trong khoảng.
  • Top 10: Lấy bao nhiêu mẫu tin (hoặc phần trăm) cao nhất (Top) hoặc thấp nhất (Bottom) của cột đang xét (Lương cơ bản).
Loc-du-lieu-trong-Excel-Cac-muc-trong-Number-Filters--Top-10a
Loc-du-lieu-trong-Excel-Cac-muc-trong-Number-Filters--Top-10b
  • Above Average: Trên mức trung bình.
  • Below Average: Dưới mức trung bình.

II. Thực nghiệm lọc dữ liệu trong Excel

Phần này xem như minh họa chi tiết lý thuyết.

Bước 1: Chọn bảng tính (từ A2:I18 – cả dòng tiêu đề).

Bước 2: Vào menu Data, chọn mục Filter

Loc-du-lieu-trong-Excel-menu-loc-du-lieu

Bước 3: Thực hiện lọc dữ liệu.

– Yêu cầu 1: Chức vụ: GĐ, PGĐ, TP, PP có Lương cơ bản lớn hơn hoặc bằng 450,000.

Để giải quyết yêu cầu này, bạn tiến hành trên hai cột (Chức vụ, Lương cơ bản). Cột nào thực hiện trước đều được.

+ Thực hiện trên cột Chức vụ. Chọn cách thông thường:

Loc-du-lieu-trong-Excel-Yeu-cau-1a

Hoặc chọn cách Text Filters

Loc-du-lieu-trong-Excel-Yeu-cau-1b

Kết quả thực hiện lọc trên cột Chức vụ:

Loc-du-lieu-trong-Excel-Ket-qua-yeu-cau-1a

+ Thực hiện trên cột Lương cơ bản.

Loc-du-lieu-trong-Excel-Yeu-cau-1c

Kết quả thực hiện lọc trên cột Chức vụ, tiếp theo lọc trên cột Lương cơ bản

Loc-du-lieu-trong-Excel-Ket-qua-yeu-cau-1b

– Yêu cầu 2: Chọn những nhân viên có tên bắt đầu bằng “C” hoặc “H”.

+ Thực hiện tại cột Họ tên Nhân viên

Loc-du-lieu-trong-Excel-Yeu-cau-2

Kết quả thực hiện lọc trên cột Họ tên nhân viên:

Loc-du-lieu-trong-Excel-Ket-qua-yeu-cau-2

– Yêu cầu 3: Chọn những nhân viên có Mã nhân viên là “5” hoặc “6”.

Loc-du-lieu-trong-Excel-Yeu-cau-3a

Hoặc

Loc-du-lieu-trong-Excel-Yeu-cau-3b

Kết quả thực hiện lọc trên cột Họ tên nhân viên:

Loc-du-lieu-trong-Excel-Ket-qua-yeu-cau-3

Kết luận

Lọc dữ liệu trong Excel ít sử dụng trong thường xuyên, nếu có yêu cầu phát sinh bạn không thực hiện được thì … rất khó. Qua bài viết này, mình mong bạn từng bước lọc dữ liệu trong Excel một cách hiệu quả. Chúc bạn thành công.

Tải dữ liệu mẫu

Sắp xếp dữ liệu trong Excel

Sap-xep-du-lieu

Sắp xếp dữ liệu là một phần không thể thiếu trong phân tích dữ liệu. Bạn có thể sắp xếp danh sách các tên theo thứ tự bảng chữ cái, sắp xếp danh sách các mức tồn kho sản phẩm từ cao nhất đến thấp nhất. Sắp xếp dữ liệu giúp bạn kiểm soát dữ liệu trực quan, và cuối cùng đưa ra quyết định hiệu quả hơn.

I. Các tiêu chí sắp xếp dữ liệu

* A→Z (hoặc A to Z): Sắp xếp tăng dần

* Z→A (hoặc Z to A): Sắp xếp giảm dần

Có nhiều cột trong một bảng tính Excel, bạn muốn sắp xếp cột nào thì sắp xếp cột đó theo tiêu chí tăng dần hoặc giảm dần.

Nếu muốn sắp xếp kết hợp nhiều cột:

* Cột nào được chọn sắp xếp trước sẽ có hiệu lực nhất.

* Cột được chọn tiếp theo sẽ có hiệu lực kế tiếp.

* Cột tiếp theo nữa sẽ có hiệu lực thấp nhất.

Chú ý:

– Không nhất thiết cột đứng trước trong bảng tính sẽ được chọn trước. Tùy theo YÊU CẦU sắp xếp của bạn.

– Không nhất thiết tiêu chí sắp xếp các cột phải giống nhau. Cột sắp xếp đầu tiên chọn tăng dần chẳng hạn, cột sắp xếp thứ hai, thứ ba có thể tăng dần hoặc giảm dần.

– Không nhất thiết phải sắp xếp trên 3 cột. Bạn có thể sắp xếp trên 1 cột, 2 cột, … n cột.

– KHÔNG CHÍNH XÁC khi sắp xếp chuỗi có chữ cái đầu là Đ, Ô, Ê,… vì các chữ này ngoài bảng chữ cái tiếng Anh.

II. Trình tự sắp xếp dữ liệu

Bước 1: Chọn bảng tính cần sắp xếp

Bạn không cần chọn hết vì nó sẽ thay đổi trật tự khi tiến hành sắp xếp. Cột Số thứ tự chẳng hạn.

Hình minh họa, bạn cần chọn từ ô B2 (Mã nhân viên) đến ô H18 (ô cuối cùng của cột Thực lãnh).

Sap-xep-du-lieu-chon-du-lieu

Cột Số thứ tự và cột Ghi chú không cần chọn.

Bước 2: Tiến hành sắp xếp

Đảm bảo Bước 1 phải được chọn.

Nhấp vào menu Data \ Chọn biểu tượng ngay mũi tên số 2.

Sap-xep-du-lieu-tien-hanh-sap-xep

Bước 3: Chọn tiêu chí sắp xếp

Khi thực hiện Bước 2, ngay lập tức hiện ra hộp thoại gợi ý theo dữ liệu bạn chọn.

Sap-xep-du-lieu-sap-xep-theo-Ma-Nhan-Vien-tang-dan

Từ hộp thoại này, bạn có thể sắp xếp theo các tiêu chí như sau:

1. Sắp xếp theo Mã nhân viên tăng dần

Sort by: Mã Nhân Viên            Order: A to Z

Bạn giữ nguyên các thông số trong hộp thoại, nhấp OK.

Kết quả sắp xếp theo Mã nhân viên tăng dần.

Ket-qua-sap-xep-du-lieu-sap-xep-theo-Ma-Nhan-Vien-tang-dan

2. Sắp xếp theo Phòng Ban tăng dần, nếu cùng Phòng Ban thì sắp theo Phụ cấp Chức vụ giảm dần

Sort by: Phòng Ban                                    Order: A to Z

Then by: Phụ cấp Chức vụ                       Order: Largest to smallest

Đến đây phát sinh thêm:

– Nhấp chuột vào nút Add Level (thêm mục Then by), nếu phát sinh thêm cứ nhấp vào nút này. Trong trường hợp dư tiêu chí sắp xếp, bạn nhấp vào nút Delete Level để xóa.

– Đối với dữ liệu kiểu số, tiền tệ, ngày tháng, giờ phút,…

* Smallest to largest: Tương đương A→Z.

* Largest to Smallest: Tương đương Z→A.

Sap-xep-du-lieu-sap-xep-theo-Phong-Ban-tang-dan-PCCV-giam-dan

Kết quả sắp xếp theo Phòng Ban tăng dần, nếu cùng Phòng Ban thì sắp theo Phụ cấp Chức vụ giảm dần.

Ket-qua-sap-xep-du-lieu-sap-xep-theo-Phong-Ban-tang-dan-PCCV-giam-dan

Kết luận

Sắp xếp dữ liệu trong Excel khá đơn giản, nếu không thực hiện được thì ảnh hưởng đến thao tác thực hiện bảng tính của bạn. Tốt nhất bạn nên thực hành sắp xếp vài lần để chuyển qua phần khác nhanh hơn. Chúc bạn sắp xếp bảng tính đúng theo yêu cầu đặt ra.

Tải dữ liệu mẫu

Định dạng ngày tháng năm trong Excel

Dinh-dang-ngay-thang-nam

Định dạng ngày tháng năm trong Excel ít xuất hiện hơn định dạng số. Tuy nhiên, nếu bạn có tính toán liên quan đến ngày tháng năm thì định dạng này rất hữu ích cho người sử dụng Excel. Mình hướng dẫn bạn cách định dạng ngày tháng năm cho phù hợp với yêu cầu.

Bước 1: Nhận dạng yêu cầu định dạng ngày tháng năm

Kiểu dữ liệu ngày tháng năm của Việt Nam như sau: ngày/tháng/năm hoặc ngày-tháng-năm.

Ví dụ:

1. 03/04/2019 hoặc 3/4/19         ngày 3 tháng 4 năm 2019.

2. 25/12/2019                          ngày 25 tháng 12 năm 2019.

Qua hai ví dụ này, bạn lưu ý như sau:

– Dạng đầy đủ: Ngày hoặc tháng từ 1 đến 9 (03/04/2019).

+ Ngày có hai chữ số;

+ Tháng có hai chữ số;

+ Năm có bốn chữ số;

– Dạng ngắn gọn: Ngày hoặc tháng từ 1 đến 9 (3/4/19).

+ Ngày có một chữ số;

+ Tháng có một chữ số;

+ Năm có hai chữ số;

– Trước khi định dạng, bạn nên nhớ một số ký tự liên quan đến định dạng ngày tháng năm như sau:

+ d hoặc D: day (ngày)

+ m hoặc M (tháng)

+ y hoặc Y (năm)

– Định dạng cả hai môi trường: Windows và Excel

Bước 2: Định dạng trong Windows

Mở Control Panel \ Mở mục Region (Đối với Windows 10, hoặc Regional and Language / Windows 7 hoặc XP) sẽ xuất hiện hộp thoại Region

Tiếp theo nhấp nút Additional settings…, xuất hiện hộp thoại Customize Format.

Chọn thẻ Date (nếu chưa được chọn).

+ Gõ d/m/yy (dạng ngắn gọn) hoặc dd/mm/yyyy (dạng đầy đủ) vào ô Short date

+ Gõ dd/mm/yyyy vào ô Long date.

+ Nhấp OK để đóng hộp thoại, định dạng ngày tháng năm xong.

Bước 3: Định dạng trong Excel

1. Chọn khối ô cần định dạng.

2. Nhấp chuột vào ô vuông nhỏ kế mục Numbers trên thanh công cụ.

hoặc Nhấp phải vào khối ô vừa chọn. Chọn Format Cells… xuất hiện hộp thoại Format Cells

Dinh-dang-ngay-thang-nam-trong-Excel-Hop-thoai-Format-Cells

Chọn thẻ Number (nếu chưa được chọn)

Tại hộp thoại này, bạn thực hiện:

– Chọn mục Date (Số 1), tiếp theo

– Chọn mục Custom, và

– Gõ mẫu Short date hoặc Long date vào ô Type

Bạn đã định dạng ngày tháng năm trong Excel.

Kết luận

Định dạng ngày tháng năm trong Excel là định dạng phức tạp nhất trong các loại định dạng. Rất nhiều người bỏ cuộc khi mới tiếp cận với Excel tại mục này. Nếu bạn thực hiện trình tự, mình tin rằng bạn sẽ Định dạng ngày tháng năm trong Excel. Chúc bạn thành công.

Định dạng số trong Excel – Định dạng thường gặp

Dinh-dang-so-trong-Excel

Định dạng số trong Excel là dạng thường gặp nhất trong bảng tính Excel. Nếu bạn sử dụng liên quan đến số lẻ (thập phân) hoặc phân cách hàng nghìn (ngàn),… thì điều đó càng trở nên quan trọng. Chúng ta hãy tìm hiểu định dạng số trong Excel theo các bước để bảng tính của bạng đáp ứng được yêu cầu.

Bước 1: Nhận dạng yêu cầu định dạng của bảng tính

Nếu có bảng tính đã tồn tại trên giấy, bạn cần xem xét dấu phân cách thập phân (số lẻ, nếu có) là dấu chấm (.) hay dấu phẩy (,).

Bước 2: Nhận dạng số trong khối ô

Bạn gõ thử một số lẻ vào Excel, xem giống yêu cầu chưa. Nếu chưa giống thì tiến hành định dạng tại Bước 3.

Bước 3: Tiến hành định dạng số (quan trọng)

Thực hiện trong MS Windows.

Mở Control Panel \ Mở mục Region (Đối với Windows 10, hoặc Regional and Language / Windows 7 hoặc XP) sẽ xuất hiện hộp thoại Region

Dinh-dang-so-trong-Excel-Hop-thoai-Region

Tiếp theo nhấp nút Additional settings…, xuất hiện hộp thoại Customize Format

Dinh-dang-so-trong-Excel-Hop-thoai-Customize-Format

Chọn thẻ Numbers, sau đó quan tâm các mục sau:

* Decimal symbol (dấu phân cách thập phân): Chấm (.) hoặc Phẩy (,)

* Digit grouping symbol (dấu phân cách hàng nghìn (ngàn)): Phẩy (,) hoặc Chấm (.)

* List separator (dấu phân cách trong công thức): Phẩy (,) hoặc Chấm phẩy (;)

Ví dụ:

Cách 1 (màu đỏ), dữ liệu kiểu số và công thức trong bảng tính như sau:

– 2.5 (hai lẻ năm)

– 1,000 (một nghìn (ngàn))

– SUM(2,3)

Cách 2 (màu xanh), kết quả như sau:

– 2,5 (hai lẻ năm)

– 1.000 (một nghìn (ngàn))

– SUM(2;3)

Mặc định windows (và Excel nói riêng) chọn theo cách 1 (màu đỏ).

Chú ý:

– Bạn PHẢI CHỌN theo cách 1 hoặc cách 2.

– Khi chọn cách 2 thì dấu phân cách trong công thức bị thay đổi theo.

– Những mục khác trong hộp thoại này đừng sửa đổi nếu bạn chưa hiểu về nó.

Bước 4: Các biểu tượng Excel liên quan đến định dạng kiểu số

Sau khi thực hiện các bước định dạng kiểu số đối với Excel, chưa chắc bạn nhập dữ liệu hiển thị đúng như mong muốn. Do đó, bạn cần kết hợp với các biểu tượng trên thanh công cụ của Excel.

1. Chọn khối ô cần hiển thị

+ Cách 1: Bạn có thể sử dụng chuột, kết hợp với các phím mũi tên để chọn khối ô.

+ Cách 2: Nhấp chuột vào tiêu đề cột (A, B, C,…). Nếu muốn chọn nhiều cột thì kết hợp với phím Shift (chọn nhiều cột liên tục) hoặc phím Ctrl (chọn nhiều cột không liên tục).

2. Hiển thị đúng

Dinh-dang-so-trong-Excel-Cac-bieu-tuong-dinh-dang

Comma Style: Thể hiện dấu phân cách hàng nghìn. Nếu chưa chọn (1000 chẳng hạn), chọn (1,000.00 chẳng hạn).

Increase Decimal: Giảm số chữ số thập phân (2.5→ 2.50 chăng hạn).

Decrease Decimal: Tăng số chữ số thập phân (2.500→ 2.50 chăng hạn).

Kết luận

Định dạng số trong Excel là bước rất quan trọng cho những người mới học Excel. Nếu định dạng số trong Excel không đúng, những số đó sẽ không có giá trị (không thể cộng, trừ, nhân, chia,…). Chúc bạn thành công.

Sử dụng hàm MATCH và hàm INDEX : Minh họa chi tiết

Sử dụng hàm MATCH và hàm INDEX thực chất là thay thế hàm VLOOKP/HLOOKUP và hàm IF. Hai hàm này kết hợp sẽ rất tiện lợi trong một số trường hợp tính toán. Vinaonline.net cùng bạn khảo sát chi tiết cách sử dụng hàm MATCH và hàm INDEX trong Excel.

I. Sử dụng hàm MATCH và hàm INDEX : MATCH

– Công dụng: Trả về vị trí cột hoặc vị trí hàng.

1. Cú pháp

MATCH(lookup_value, lookup_array, [match_type])

* lookup_value (bắt buộc) : Giá trị cần tìm kiếm kiếm (số, chuỗi, địa chỉ ô,…).

* lookup_array (bắt buộc) : Gồm một cột hoặc một hàng.

* [match_type] (không bắt buộc) : Có 3 tùy chọn.

1 hoặc bỏ đối số này : Hàm MATCH tìm giá trị lớn nhấtnhỏ hơn hoặc bằng giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm (lookup_array) phải đặt theo thứ tự TĂNG dần.

0 : Hàm MATCH tìm giá trị chính xác giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm không nhất thiết theo tứ tự.

-1 : Hàm MATCH tìm giá trị nhỏ nhấtlớn hoặc bằng giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm phải đặt theo thứ tự GIẢM dần.

Sử dụng hàm MATCH và hàm INDEX - Minh họa

Sử dụng hàm MATCH và hàm INDEX – Minh họa

2. Ví dụ

MATCH(“A”,B1:E1,0) = 1

MATCH(“A”,B1:E1,1) = 1

Tìm thấy “A” tại vị trí (cột) 1 trong bảng tìm kiếm.

MATCH(“E“,B1:E1,0) = #N/A

MATCH(“E“,B1:E1,1) = 4

Tìm giá trị lớn nhất gần với E

MATCH(“E“,B1:E1,-1) = #N/A

Chú ý:

Thông thường sử dụng đối số [match_type] là 0. Các tùy chọn khác rất mơ hồ, ít sử dụng trong thực tế.

II. Sử dụng hàm MATCH và hàm INDEX : INDEX

– Công dụng : Dò tìm trong bảng và trả về giá trị là giao của HÀNG và CỘT.

1. Cú pháp

INDEX(array, row_num, [column_num])

* array (bắt buộc) : Là một bảng gồm:

– Nhiều hàng, nhiều cột.

– Một hàng, nhiều cột.

– Nhiều hàng, một cột.

* row_num : Chỉ số hàng, từ 1, 2, …, n. Nếu array chỉ có một hàng, có thể bỏ qua đối số này.

* column_num : Chỉ số cột, từ 1, 2, …, n. Nếu array chỉ có một cột, có thể bỏ qua đối số này.

2. Ví dụ

INDEX(B1:E1,3) = C

array chỉ có một hàng (B1:E1), row_num không cần thiết. 3 là column_num.

INDEX(A1:A5,5) = T

array chỉ có một cột (A1:A5), 5 là row_num. column_num không cần thiết.

INDEX(A1:E5,2,4) = 500; hàng 2, cột 4

INDEX(A1:E5,4,2) = 40; hàng 4, cột 2

III. Sử dụng hàm MATCH và hàm INDEX : Áp dụng

Dựa vào bảng đã cho, hãy điền SỐ LƯỢNG

Sử dụng hàm MATCH và hàm INDEX - Minh họa

Sử dụng hàm MATCH và hàm INDEX – Áp dụng

Phân tích yêu cầu:

Đối với yêu cầu này, bạn có thể sử dụng hàm VLOOKUP / HLOOKUP kết hợp với hàm IF sẽ đáp ứng được. Vấn đề đặt ra, nếu có nhiều cột / hàng thì sao? Vì vậy tốt nhất là sử dụng hàm INDEX và kết hợp với hàm MATCH để lấy chỉ số hàng / cột trong hàm INDEX.

– Mảng / Bảng dò tìm (bảng giá trị – array) :

+ Trường hợp 1: chọn từ B2:E5, tại ô B2 (có giá trị 20) là hàng 1, cột 1.

+ Trường hợp 2: chọn từ A1:E5, tại ô B2 (có giá trị 20) là hàng 2, cột 2.

Tùy theo đối số array (bảng dò tìm) trong hàm INDEX mà sử dụng hàm MATCH cho hợp lý.

– Đối số hàng (row_num) : Là một cột (A2:A5 – Trường hợp 1), (A1:A5 – Trường hợp 2).

– Đối số cột (column_num) : Là một hàng (A2:E5 – Trường hợp 1), (A1:E5 – Trường hợp 2).

+ Trường hợp 1:

Trả về chỉ số hàng: MATCH(RIGTH(A8,1),$A$2:$A$5,0)

RIGHT(A8,1) để được 1 ký tự phải (V, G, H, T)

Trả về chỉ số cột: MATCH(LEFT(A8,1),$B$1:$E$1,0)

LEFT(A8,1) để được 1 ký tự trái (A, B, C, D)

+ Trường hợp 2: Tương tự (xem nội dung hàm INDEX)

Kết quả:

+ 1:

=INDEX($B$2:$E$5, MATCH(RIGHT(A8,1),$A$2:$A$5,0), MATCH(LEFT(A8,1),$B$1:$E$1,1))

+ 2:

=INDEX($A$1:$E$5, MATCH(RIGHT(A8,1),$A$1:$A$5,0), MATCH(LEFT(A8,1),$A$1:$E$1,1))

Kết luận

Sử dụng hàm MATCH và hàm INDEX ít gặp trong những bài toán thông thường. Nhưng rất hữu ích nếu gặp những yêu cầu tham chiếu dạng bảng. Bạn có thể tham khảo thêm các hàm dò tìm hoặc bài tập củng cố để rèn kỹ năng phân tích và giải quyết một bài toán Excel. Chúc bạn thành công.

Bài tập áp dụng VLOOKUP và HLOOKUP : Minh họa trường hợp cụ thể

Bài tập áp dụng VLOOKUP và HLOOKUP là phân tích các tham số trong các hàm này để thực hiện đúng yêu cầu đề ra. Nếu sử dụng các tham số không đúng, bạn sẽ gặp khó khăn ngay các hàm này. Khi kết hợp với các hàm khác thì tính phức tạp càng tăng thêm. Vinaonline.net nêu bài tập áp dụng VLOOKUP và HLOOKUP là để củng cố thêm lý thuyết đã khảo sát Sử dụng hàm VLOOKUP và HLOOKUP.

Bài tập áp dụng VLOOKUP và HLOOKUP : Ví dụ minh họa

Bài tập áp dụng VLOOKUP và HLOOKUP : Ví dụ minh họa

Mô tả các ký tự trong MÃ HÀNG

– 1 là Sỉ (S), Lẻ (L) hoặc Khác (K) trong những trường hợp còn lại.

– 2 dùng để tham chiếu TÊN HÀNG.

– 3  cho biết loại hàng NHẬP hoặc XUẤT.

– Các ký tự còn lại là SỐ LƯỢNG.

Yêu cầu

1. Dựa vào BẢNG THAM CHIẾU để điền TÊN HÀNG tương ứng.

2. Lấy SỐ LƯỢNG dựa vào MÃ HÀNG.

3. Căn cứ BẢNG THAM CHIẾU, điền ĐƠN GIÁ tùy theo LOẠI HÀNG.

4. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ – GIẢM GIÁ (Kết hợp với BẢNG GIẢM GIÁ).

4. Tính tổng số tiền từng mặt hàng trong BẢNG THỐNG KÊ.

Nhắc lại cú pháp hàm VLOOKUP như sau:

VLOOKUP(Giá trị, Bảng tham chiếu, Số thứ cột cho kết quả, cách dò tìm)

I. Bài tập áp dụng VLOOKUP và HLOOKUP : Phân tích Giá trị

Khó nhất trong hàm VLOOKUP và HLOOKUP là xác định Giá trị. Khi không so khớp với CỘT / HÀNG đầu tiên của BẢNG THAM CHIẾU. Một số yếu tố cần xử lý như sau:

– Dùng hàm LEFT hoặc RIGHT hoặc MID để cắt chuỗi (MÃ HÀNG) cho phù hợp với CỘT / HÀNG trong BẢNG THAM CHIẾU.

– Nếu phù hợp NHƯNG báo lỗi #N/A, bạn tiếp tục dùng hàm VALUE để chuyển đổi kiểu dữ liệu. Chỉ thực hiện điều này khi gặp dạng ký tự số như “1”, “2”, “3”,…

– Khó hơn nữa, bạn phải kết hợp với hàm IF để cắt chuỗi.

Ví dụ:

Dựa vào MÃ HÀNG và BẢNG GIẢM GIÁ lấy % giảm giá

– Hàng 1 của BẢNG GIẢM GIÁ (từ C20:E20) có các ký tự “S”, “L”, “K”.

– So với ký tự 1 của MÃ HÀNG chỉ có “S”, “L”. Còn “K” thì sao?

– Trong trường hợp này, bạn không thể sử dụng hàm LEFT(B5,1).

– Muốn có “S”, “L”, “K” (trong những trường hợp còn lại), bạn sử dụng hàm:

IF(OR(LEFT(B5,1)=”S”,LEFT(B5,1)=”L”),LEFT(B5,1),“K”)

Nếu cắt MÃ HÀNG được “S” hoặc “L” thì để nguyên ký tự “S” hoặc “L”. Không phải một trong hai ký tự đó thì trả về ký tự “K”.

– Hàm lấy giảm giá như sau:

HLOOKUP(IF(OR(LEFT(B5,1)=”S”,LEFT(B5,1)=”L”),LEFT(B5,1),“K”),$C$20:$E$21,2,0)

II. Bài tập áp dụng VLOOKUP và HLOOKUP : Số thứ tự cột / hàng cho kết quả

Tham số này nhẹ nhàng hơn Giá trị, nếu không cho cụ thể Số thứ tự cột / hàng. Bạn cần kết hợp với hàm IF mà thôi. Khỏe lắm!

Ví dụ:

3. Căn cứ BẢNG THAM CHIẾU, điền ĐƠN GIÁ tùy theo LOẠI HÀNG.

Phân tích:

Nếu mặt hàng NHẬP thì chọn Số thứ tự cột 3, XUẤT thì cột 4.

Công thức như sau:

VLOOKUP(MID(B5,2,1),$B$15:$E$17,IF(MID(B5,3,1)=“N”,3,4),1)

III. Bài tập áp dụng VLOOKUP và HLOOKUP : Hoàn thành bảng tính

1. Dựa vào BẢNG THAM CHIẾU để điền TÊN HÀNG tương ứng.

C5 = VLOOKUP(MID(B5,2,1),$B$15:$E$17,2,1)

2. Lấy SỐ LƯỢNG dựa vào MÃ HÀNG.

D5 = VALUE(RIGHT(B5,LEN(B5)-3)) (3 ký tự đầu không phải ký tự số)

3. Căn cứ BẢNG THAM CHIẾU, điền ĐƠN GIÁ tùy theo LOẠI HÀNG.

Đã giải thích ở trên.

4. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ – GIẢM GIÁ (Kết hợp với BẢNG GIẢM GIÁ).

Phân tích:

SỐ LƯỢNG x ĐƠN GIÁ là một giá trị (số tiền) không thể trừ trực tiếp cho giảm giá (%) nên bạn tính như sau:

F5 = (100% – HLOOKUP(IF(OR(LEFT(B5,1)=”S”,LEFT(B5,1)=”L”),LEFT(B5,1),“K”),$C$20:$E$21,2,0))*D5*E5

5. Tính tổng số tiền từng mặt hàng trong BẢNG THỐNG KÊ.

– THÀNH TIỀN / DẦU LỬA

H15 = SUMIF($C$5:$C$10,G15,$F$5:$F10)

Bạn có thể thay G15=”Dầu lửa”, nhưng sao chép các ô còn lại phải chỉnh sửa thành “Xăng”,”Nhớt”.

Download dữ liệu mẫu

Lời kết

Bài tập áp dụng VLOOKUP và HLOOKUP cung cấp cho bạn một số dạng để rèn luyện. Nhưng CHƯA ĐỦ, bạn nên tìm thêm một số bài tập để luyện tập. Chúc bạn sử dụng thành thạo các hàm dò tìm VLOOKUP và HLOOKUP.

Sử dụng hàm VLOOKUP và HLOOKUP : Hướng dẫn áp dụng chi tiết

Sử dụng hàm VLOOKUP và HLOOKUP là thực hiện tính toán tương đối phức tạp đối với bảng tính Excel. Trong một số trường hợp, hàm IF không thể thực hiện được. Vì vậy, bạn phải sử dụng hàm VLOOKUP và HLOOKUP để tính toán.

I. Sử dụng hàm VLOOKUP và HLOOKUP : Công dụng

Hai hàm này dùng để dò tìm và trả về kết quả nếu dò tìm so khớp.

– Sử dụng VLOOKUP để dò tìm bảng tham chiếu theo dạng cột.

– Hàm HLOOKUP để dò tìm theo dạng hàng.

II. Sử dụng hàm VLOOKUP và HLOOKUP : Cú pháp

Vinaonline.net giới thiệu cú pháp của hàm VLOOKUP, hàm HLOOKUP thực hiện tương tự.

Cú pháp:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Chuyển thể tiếng Việt

VLOOKUP(Giá trị, Bảng tham chiếu, Số thứ cột cho kết quả, cách dò tìm)

* Giá trị: Là một ô (xét trong bảng tính). Nếu ô này chưa so khớp với cột đầu tiên trong Bảng tham chiếu, bạn có thể sử dụng các hàm xử lý chuỗi để tách cho phù hợp.

* Bảng tham chiếu: Là một bảng nhỏ (gồm nhiều cột) được đính kèm với bảng tính.

Chú ý:

– Bạn phải cố định Bảng tham chiếu, Nếu không, sao chép (copy) sẽ sai kết quả.

* Số thứ tự cột cho kết quả: Tùy theo yêu cầu, số thứ tự cột có thể 2, 3, 4,…

* Cách dò tìm: Phụ thuộc vào cột đầu tiên của Bảng tham chiếu.

+ 1 hoặc bỏ tham số này: Nếu được sắp thứ tự tăng dần.

+ 0: Ngược lại. TUY NHIÊN, 0 đúng mọi trường hợp.

Hàm HLOOKUP tương tự, chỉ khác Bảng tham chiếu gồm nhiều hàng.

HLOOKUP(Giá trị, Bảng tham chiếu, Số thứ hàng cho kết quả, cách dò tìm)

III. Sử dụng hàm VLOOKUP và HLOOKUP : Ví dụ áp dụng

1. Ví dụ 1

Yêu cầu

1. Điền STT

2. Dựa vào MÃ HÀNG và dò tìm trong BẢNG THAM CHIẾU, điền TÊN HÀNG tương ứng.

3. Cột ĐƠN GIÁ thực hiện tương tự cột MÃ HÀNG

4. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ

Giải gợi ý

1. Điền STT, bạn tự thực hiện

2. Điền cột TÊN HÀNG

Phân tích:

– Bảng tham chiếu có dạng cột (cột MÃ HÀNG, cột TÊN HÀNG, cột ĐƠN GIÁ) nên bạn sử dụng hàm VLOOKUP.

Giá trị (từ ô B5:B10) dùng để dò tìm đối với cột đầu tiên của BẢNG THAM CHIẾU giống nhau nên giữ nguyên.

– Cột TÊN HÀNG là cột 2 trong BẢNG THAM CHIẾU.

– Cột đầu tiên của BẢNG THAM CHIẾU tăng dần (A, B, C)

Công thức TÊN HÀNG (C5) = VLOOKUP(B5,$B$14:$D$16,2,1)

3. Điền cột ĐƠN GIÁ

ĐƠN GIÁ (E5) = VLOOKUP(B5,$B$14:$D$16,3,1)

4. Cột THÀNH TIỀN, bạn tự thực hiện

2. Ví dụ 2

Yêu cầu

 1. Dựa vào MÃ HÀNG và BẢNG THAM CHIẾU 1, điền TÊN HÀNG.

2. Dựa vào MÃ HÀNG và BẢNG THAM CHIẾU 2, điền LOẠI.

3. SỈ/LẺ: Nếu ký tự cuối cùng là S, ghỉ “Sỉ”. Ngược lại, ghi “Lẻ”.

4. Cột ĐƠN GIÁ thực hiện giống TÊN HÀNG.

5. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ

Giải gợi ý

1. TÊN HÀNG

– BẢNG THAM CHIẾU 1 dạng hàng nên sử dụng hàm HLOOKUP.

– Giá trị (từ B2:B10) không khớp với hàng đầu tiên của BẢNG THAM CHIẾU 1. Dùng hàm cắt chuỗi sao cho được “A”, “B”, “C”. => Sử dụng LEFT(B5,1) trong trường hợp này.

TÊN HÀNG (C5) = HLOOKUP(LEFT(B5,1),$D$13:$F$15,2,1)

4. Cột ĐƠN GIÁ tương tự

ĐƠN GIÁ (G5) = HLOOKUP(LEFT(B5,1),$D$13:$F$15,3,1)

2. LOẠI

– BẢNG THAM CHIẾU 2 dạng cột. Nên sử dụng hàm VLOOKUP.

– Giá trị (từ B5:B10) không khớp với cột đầu tiên của BẢNG THAM CHIẾU 2. Nên dùng hàm MID để cắt lấy ký tự thứ hai. NHƯNG phải sử dụng thêm hàm VALUE vì Giá trị và cột / hàng đầu tiên của BẢNG THAM CHIẾU phải cùng kiểu dữ liệu. => VALUE(MID(B5,2,1))

LOẠI (D5) = VLOOKUP(VALUE(MID(B5,2,1)), $C$19:$D$21,2,1)

3. SỈ/LẺ (E5) = IF(RIGHT(B5,1)=”S”,”Sỉ”,”Lẻ”)

4. THÀNH TIỀN (H5) = F5*G5

IV. Sử dụng hàm VLOOKUP và HLOOKUP : Một số lỗi thường gặp

1. Lỗi #N/A

Khi sao chép toàn bộ các ô:

– Tất cả đều cho lỗi này. Bạn kiểm tra Giá trị và cột đầu tiên của bảng tham chiếu.

– Một vài ô có lỗi #N/A. Bạn kiểm tra dữ liệu cột MÃ HÀNG.

2. Không cố định Bảng tham chiếu

– Ô đầu tiên vẫn đúng, nhưng sao chép sẽ báo lỗi.

Khắc phục bằng cách cố định Bảng tham chiếu (F4)

3. Số thứ tự cột / hàng cho kết quả

Một số đề bài không yêu cầu cụ thể. Trong trường hợp này, bạn sử dụng hàm IF để xét.

Download dữ liệu mẫu

Bạn có thể rèn luyện thêm Bài tập áp dụng VLOOKUP và HLOOKUP.

Kết luận

Sử dụng hàm VLOOKUP và HLOOKUP thành thạo là cả quá trình rèn luyện Excel. Nếu kết hợp tốt với hàm IF, bạn có thể giải mọi bài toán cơ bản. Vinaonline.net chúc bạn sử dụng hàm VLOOKUP và HLOOKUP tốt nhất có thể.

Xử lý lỗi trong Excel : Nhận biết và khắc phục các lỗi thường gặp

Xử lý lỗi trong Excel là tìm hiểu và chỉnh sửa công thức và dữ liệu trong bảng tính. Thực hiện công việc này nhanh hay chậm còn tùy thuộc vào kỹ năng phân tích của bạn. Để xử lý lỗi trong Excel thành công, bạn nên tuân thủ một số quy định của Excel như sau:

I. Xử lý lỗi trong Excel : Sử dụng hàm hay biểu thức

1. Hàm

– Bạn nên quan tâm một số yếu tố khi sử dụng hàm:

+ Hàm hay không tham số? Nếu có, tham số này sử dụng mặc định được không?

+ Tham số trong hàm phải đúng kiểu dữ liệu.

+ Khi có nhiều hàm lồng nhau, hàm trong cùng được thực hiện trước.

+ Khi gõ hàm nên tắt tiếng Việt, đặc biệt đang sử dụng kiểu gõ Telex.

2. Biểu thức

– Khi sử dụng biểu thức phức tạp trong công thức, bạn nên tuân theo quy tắc:

+ Số ngoặc đóng bằng số ngoặc mở “( )”.

+ Nên cẩn thận vị trí dấu ngoặc đóng, sử dụng sai vị trí đôi khi công thức không báo lỗi nhưng kết quả tính toán sai!

Bạn có thể tham khảo thêm

II. Xử lý lỗi trong Excel : Thông báo lỗi và cách khắc phục

1. Hiển thị #### hoặc 1E+n

Đây không phải là lỗi, mà do độ rộng cột (ô) không đủ lớn. Bạn chỉ cần kéo độ lớn cột là khắc phục được vấn đề này.

2. #DIV/0!

Lỗi này do bạn thực hiện phép chia cho 0 (không).

Khắc phục

+ Xem lại dữ liệu đã nhập đúng chưa, hoặc

+ Công thức tham chiếu đến giá trị 0.

3. #NAME?

Lỗi này do bạn gõ tên hàm không đúng hoặc địa chỉ ô không chính xác (Excel vẫn hiểu đó là hàm không đúng).

Khắc phục:

Kiểm tra chính tả của hàm hoặc các ô tham gia công thức.

4. #N/A

Lỗi này khi bạn dò tìm nhưng không có giá trị trùng khớp (thường gặp trong các hàm VLOOKUP(), HLOOKUP(), MATCH(), INDEX(),…)

Khắc phục

Xem lại dữ liệu trong bảng tính và các bảng tham chiếu. Một số yếu tố cần quan tâm:

– Số ký tự (giá trị) trong bảng tính và giá trị trong cột (hoặc hàng) của bảng tham chiếu bằng nhau chưa.

– Có cùng kiểu dữ liệu chưa (kiểu chuỗi và kiểu số chẳng hạn), nếu chưa thì nên chuyển đổi kiểu dữ liệu.

Bạn có thể tham khảo các hàm cắt chuỗi và đổi kiểu dữ liệu.

5. #NULL!

Lỗi này xảy ra khi thực hiện giao hai vùng dữ liệu, nhưng không có kết quả.

Khắc phục

Nên kiểm tra lại hai vùng dữ liệu.

6. #NUM!

Lỗi này thường gặp khi giá trị (số) không hợp lệ.

Ví dụ:

Bạn cần số nguyên tính căn bậc 2, nhưng bạn nhập số âm chẳng hạn.

7. #REF!

Lỗi này khi bạn tham chiếu đến một ô hoặc khối ô không hợp lệ hoặc ô đang sử dụng công thức.

Khắc phục

– Chỉ kiểm tra ô hoặc khối ô hợp lệ trong công thức mà thôi.

8. #VALUE!

Sử dụng kiểu dữ liệu trong các tham số không đúng.

Ví dụ:

Kiểu chuỗi và kiểu số.

Kiểu chuỗi và kiểu ngày tháng năm,…

Khắc phục

Kiểm tra lại kiểu dữ liệu (có thể định dạng) cho phù hợp.

Lời kết

Xử lý lỗi trong Excel là một là một phần của thao tác bảng tính. Nếu bạn kiểm soát lỗi tốt thì sẽ rút ngắn thời gian thực hiện bảng tính. Dù bạn xử lý bảng tính trong Excel tốt đến đâu thì mình vẫn khuyên bạn, hạn chế lỗi càng ít càng tốt. Chúc bạn thành công.

Sử dụng hàm SUMIF : Tính tổng có điều kiện, minh họa cụ thể

Sử dụng hàm SUMIF trong bảng tính Excel là để tổng cộng một hoặc nhiều cột nào đó theo điều kiện. Bạn không thể sử dụng hàm SUM để tính toán theo điều kiện nào đó, mà bắt buộc phải sử dụng hàm SUMIF.

I. Sử dụng hàm SUMIF : Cú pháp

SUMIF(range, criteria,[sum_range])

* Range: Một cột có chứa ĐIỀU KIỆN (criteria).

* Criteria: Điều kiện thuộc Range, nếu điều kiện không thuộc Range, hàm cho kết quả 0.

* Sum_range: Cột chứa giá trị số để TỔNG CỘNG.

Sử dụng hàm SUMIF : Bảng tính minh họa

Sử dụng hàm SUMIF : Bảng tính minh họa

II. Sử dụng hàm SUMIF : Ví dụ

– Yêu cầu thực hiện

1. THÀNH TIỀN = SỐ LƯỢNG * ĐƠN GIÁ

2. Tính bảng thống kêSử dụng hàm SUMIF : Bảng thống kê

 Sử dụng hàm SUMIF : Bảng thống kê

3. Định dạng ĐƠN GIÁ và THÀNH TIỀN như mẫu đã cho.

Giải gợi ý:

1. THÀNH TIỀN

E3=C3*D3

2. Tính bảng thống kê

* SỐ LƯỢNG / DẦU LỬA

– Số lượng là cột C (C3:C10).

– Dầu lửa có MÃ HÀNG là “A”, cột mã hàng là cột A (A3:A10)

H3=SUMIF(A3:A10,”A”,C3:C10)

Hoặc

– Dầu lửa có TÊN HÀNG là “DẦU LỬA”, cột tên hàng là cột B (B3:B10)

H3=SUMIF(B3:B10,”DẦU LỬA”,C3:C10)

H3=SUMIF(B3:B10,”D*”,C3:C10)

Chú ý:

– Điều kiện “DẦU LỬA” bạn có thể sử dụng ký tự đại diện “D*”, nhưng phải đảm bảo trong cột này (B3:B10) không có tên hàng nào bắt đầu bằng ký tự “D”.

Tương tự cho SỐ LƯỢNG (XĂNG, NHỚT).

* THÀNH TIỀN / NHỚT

– Phân tích tương tự trường hợp trên, bạn được công thức như sau:

=SUMIF(A3:A10,”C”,E3:E10)

Hoặc

=SUMIF(B3:B10,”NHỚT”,E3:E10)

=SUMIF(B3:B10,”N*”,E3:E10)

3. Tham khảo bài viết Định dạng tiền tệ.

III. Sử dụng hàm SUMIF : Tính toán nhanh

Trong trường hợp cần hoàn thành các đại lượng (giá trị) cho bảng thống kê. Bạn nên sử dụng địa chỉ tham chiếu để việc tính toán được nhanh chóng và hạn chế sai sót.

Ví dụ:

+  Tính SỐ LƯỢNG của DẦU LỬA

H3=SUMIF($B$3:$B$10,G3,$C$3:$C$10)

Chú ý:

– Tại ô G3 bạn đã có chuỗi DẦU LỬA. Không có cặp nháy kép (” “) cho G3.

– Nên cố định hai khối B3:B10 và C3:C10 để sao chép xuống các ô bên dưới H4 hoặc H5 sẽ không bị lệch khối ô. Bạn có thể tham khảo các loại địa chỉ ô.

+  Tính SỐ LƯỢNG của XĂNG

H4=SUMIF($B$3:$B$10,G4,$C$3:$C$10)

+  Tính SỐ LƯỢNG của NHỚT

H5=SUMIF($B$3:$B$10,G5,$C$3:$C$10)

– Bạn gõ sai chính tả các chuỗi DẦU LỬA, XĂNG, NHỚT tại các ô G3, G4, G5 thì kết quả bằng 0.

Các giá trị từ I3, I4, I5 bạn thực hiện tương tự.

Lời kết

Sử dụng hàm SUMIF trong Excel rất tốt khi bạn tính tổng có điều kiện. Tuy nhiên, nếu điều kiện ghép thì bạn phải thực hiện những hàm khác. Cài viết tiếp theo sẽ đề cập vần đề này. Chúc bạn sử dụng hàm SUMIF trong bảng tính tốt nhất.

Hàm chuỗi cơ bản trong Excel : Đơn giản, áp dụng hiệu quả

Hàm chuỗi cơ bản trong Excel là một số hàm xử lý chuỗi trong bảng tính Excel cho các phiên bản từ Excel 97 đến Excel 2016. Các hàm này ít sử dụng độc lập nhưng rất hiệu quả khi kết hợp với các hàm khác, như sử dụng hàm IF nâng cao, dò tìm,… Để thấy được công dụng của các hàm này, VinaOnline.net cùng bạn tìm hiểu hàm chuỗi cơ bản trong Excel nhé.

I. Hàm chuỗi cơ bản trong Excel : LEFT

Cú pháp:

LEFT(Chuỗi, n)

Công dụng:

Cắt chuỗi bên trái, lấy n ký tự. Nếu không có tham số n, hàm lấy 1 ký tự.

Ví dụ: A1=”ABCDEF”

=LEFT(“ABCDEF”) = “A” hoặc LEFT(A1) = “A”

=LEFT(“ABCDEF”,1) = “A” hoặc LEFT(A1,1) = “A”

=LEFT(“ABCDEF”,3) = “ABC” hoặc LEFT(A1,3) = “ABC”

II. Hàm chuỗi cơ bản trong Excel : RIGHT

Cú pháp:

RIGHT(Chuỗi, n)

Công dụng:

Giống hàm LEFT(), nhưng cắt bên phải.

Ví dụ:

=RIGHT(“ABCDEF”) = “F” hoặc RIGHT (A1) = “F”

=RIGHT (“ABCDEF”,1) = “F” hoặc RIGHT (A1,1) = “F”

=RIGHT (“ABCDEF”,3) = “DEF” hoặc RIGHT (A1,3) = “DEF”

III. Hàm chuỗi cơ bản trong Excel : MID

Cú pháp:

MID(Chuỗi, vị trí, n)

Công dụng:

Cắt giữa chuỗi từ “vị trí” qua phải, lấy n ký tự.

Ví dụ:

=MID(“ABCDEF”,1,1) = “A” hoặc MID (A1,1) = “A”

=MID(“ABCDEF”,2,1) = “B” hoặc MID (A1,2,1) = “B”

=MID(“ABCDEF”,4,3) = “DEF” hoặc MID (A1,4,3) = “DEF”

=MID(“ABCDEF”,2,2) = “CD” hoặc MID (A1,2,2) = “CD”

Chú ý:

Hàm MID là sự kết hợp giữa hàm LEFT và hàm RIGHT.

Ví dụ:

– Từ chuỗi “ABCDEFGH”, để được 2 ký tự “CD”

=MID(“ABCDEFGH”,3,2)=”CD”

Dùng hàm LEFT bên trong:

– Cắt “ABCDEFGH” bên trái qua, lấy 4 ký tự:

=LEFT(“ABCDEFGH”,4)=”ABCD”

– Lấy kết quả, tiếp tục cắt 2 ký tự bên phải:

=RIGHT(LEFT(“ABCDEFGH”,4),2)=”CD”

Dùng hàm RIGHT bên trong:

=LEFT(RIGHT(“ABCDEFGH”,6),2)=”CD”

Chú ý:

– Hàm bên trong thực hiện trước.

IV. Hàm chuỗi cơ bản trong Excel : LEN

Cú pháp:

LEN(Chuỗi)

Công dụng:

Đếm số ký tự trong chuỗi.

Ví dụ:

=LEN(“CD”) =2

=LEN(“ABCDEFGH”) =8

=LEN(“Hoc Excel”) =9  (Kể cả một khoảng trắng)

=LEN(” CD “) = 4          (Có khoảng trắng trước “C” và sau “D”)

 Hàm chuỗi cơ bản trong Excel : Minh họa hàm LEN và hàm VALUE

Minh họa hàm LEN và hàm VALUE

Dựa vào bảng tính, tách ký tự số ra khỏi chuỗi.

Phân tích:

– Số ký tự số nằm giữa nên phải sử dụng hàm MID hoặc kết hợp hàm LEFT và hàm RIGHT.

– Số ký tự chuỗi không thống nhất trong các ô. Nên không thể sử dụng một giá trị cụ thể cho hàm MID hoặc LEFT hoặc RIGHT.

– Dấu hiệu nhận biết:

+ Hai tự đầu là ký tự chữ cái.

+ Ký tự cuối là chữ cái.

+ Suy ra số ký tự số = Chiều dài của chuỗi (chiều dài ô) – 3 (Hai ký tự đầu và ký tự cuối).

Hàm cần thực hiện:

=MID(A4,3, LEN(A4)-3) = “324”

Sao chép sang các ô bên dưới:

=MID(A5,3, LEN(A5)-3) = “11569”

=MID(A6,3, LEN(A6)-3) = “09”

=MID(A7,3, LEN(A7)-3) = “111”

V. Hàm chuỗi cơ bản trong Excel : VALUE

Cú pháp:

VALUE(Chuỗi)

Công dụng:

Đổi chuỗi “dạng số” thành số.

Ví dụ:

=VALUE(“123”)=123

=VALUE(“09”)=9

=VALUE(“A12”)=#VALUE! (Vì A12 không phải dạng số).

Hãy đổi số ký tự thành số (trong bảng tính trên)

=VALUE(MID(A4,3,LEN(A4)-3)) = 324

Sao chép

=VALUE(MID(A5,3,LEN(A5)-3)) = 11569

=VALUE(MID(A6,3,LEN(A6)-3)) = 9

=VALUE(MID(A7,3,LEN(A7)-3)) = 111

Chú ý:

Một số bảng tính yêu cầu thực hiện đối với dữ liệu được tách ra từ kiểu chuỗi. Nếu không thực hiện hàm này, một số hàm sẽ không thực hiện được (trả về #/N/A – Not Available).

Ví dụ:

“1” và 1 hoặc “09” và 9 là hai kiểu dữ liệu khác nhau (chuỗi và số).

VI. Hàm chuỗi cơ bản trong Excel : Ghép chuỗi (&)

Cú pháp:

Chuỗi 1 & Chuỗi 2

Công dụng:

Ghép hai chuỗi thành chuỗi mới.

Ví dụ: A1=”Học”, A2=”Excel”

=A1 & A2=”HọcExcel”

=A1 & ” ” & A2=”Học Excel”

=”ABC” & “DEF”=”ABCDEF”

Lời kết

Như đã đề cập, hàm chuỗi cơ bản trong Excel ít được sử dụng độc lập trong bảng tính nhưng một số trường hợp không sử dụng các hàm này thì không giải quyết được yêu cầu.