Ngôn ngữ SQL là gì? Chắc hẳn đối với những người làm nghiệp vụ thì ngôn ngữ SQL là một ngôn ngữ vô cùng lạ lẫm. Tuy nhiên Sức mạnh của ngôn ngữ này sẽ khiến bạn hoàn toàn choáng ngợp, ngôn ngữ này bạn sẽ dễ dàng hô biến toàn bộ bảng dữ liệu để tạo ra vô vàn các báo cáo từ đơn giản tới phức tạp trong một câu lệnh duy nhất
1. Giới thiệu về ngôn ngữ SQL trên Excel
Vậy ngôn ngữ SQL là gì? Ngôn ngữ SQL là từ viết tắt trong tiếng Anh là Structured Query Language, SQL là một ngôn ngữ truy vấn (trích lọc) dữ liệu mang tính cấu trúc giúp lấy dữ liệu từ một cơ sở dữ liệu (có thể là một bảng tính Excel, hoặc CSDL của các phần mềm kế toán, nhân sự, ngân hàng,..) từ đó phục phụ chon nhu cầu phân tích (lập các loại báo cáo) theo mục đích của người sử dụng.
2. Làm sao để sử dụng được loại ngôn ngữ đặc biệt này trên Excel?
Giá trị và sức mạnh của ngôn ngữ SQL trên bảng tính Excel là vô cùng to lớn. Tuy Excel vẫn cho phép khai thác các câu lệnh trong SQL, nhưng khá khó sử dụng hoặc để có thể khai thác được ngôn ngữ SQL này đòi hỏi người sử dụng phải nắm được kỹ thuật lập trình VBA. Nhưng hầu hết những người làm nghiệp vụ đều không hề biết về lập trình. Nếu đánh đổi bằng việc phải học thêm VBA để thực hiện các công việc hiện tại người sử dụng sẽ phải đánh đổi bằng rất nhiều chi phí thời gian, công sức và nếu có học được kỹ thuật lập trình thì việc việc lập các báo cáo cũng khá phức tạp, chỉnh sửa mỗi báo cáo sẽ chiếm rất nhiều thời gian và dễ xảy ra sai sót.
Vậy làm thế nào? Để có thể khai thác sử dụng ngôn ngữ SQL trên bảng tính Excel một cách đơn giản, dễ dàng. Bạn không cần bỏ ra 10 năm miệt mài nghiên cứu, mà bạn vẫn có thể trở thành chuyên gia phân tích số liệu chỉ trong vài giờ “Khó tin nhưng có thật”. Với những kỹ thuật sử dụng ngôn ngữ SQL trên Excel bằng phần mềm Add-In ATools mà chúng tôi chia sẻ. Chúng tôi cam kết sẽ giúp bạn rút ngắn 10 năm nghiên cứu, mà vẫn có thể trở thành chuyên gia hàng đầu về lĩnh vực các bạn làm việc “Đừng lãng phí thời gian và công sức, cho những việc vô ích, hãy để chúng tôi giúp bạn thực hiện nó”
3. Cách sử dụng ngôn ngữ SQL trên EXCEL
Bước 1: Tải File Excel thực hành: Tại Đây Bước 2: Cài đặt phần mềm Add-In ATools. Các bạn có thể tải ngay phần mềm: Tại Đây
3.1 Chuẩn hóa bảng dữ liệu nguồn trước khi sử dụng hàm BS_SQL.
Việc chuẩn hóa bảng dữ liệu là vô cùng quan trọng, đây là nền móng, kiến trúc của mọi báo cáo nếu bạn không chuẩn hóa bảng dữ liệu nguồn (CSDL) dù bạn có am hiểu kỹ thuật đến đâu thì bạn cũng rất khó có thể tạo ra các báo cáo hay phân tích dữ liệu. Việc chuẩn hóa bảng dữ liệu nguồn trên Excel sẽ tuân theo các quy tắc nhất định như sau:
+ Bảng dữ liệu/Table từ dòng tiêu đề đến các dòng dữ liệu không được có ô nào ở trạng thái Merge Cells: Là một bảng dữ liệu được định nghĩa bởi cột và dòng, giao của cột và dòng là ô. Việc Merge nhiều ô thành một ô sẽ làm việc truy vấn dữ liệu gặp lỗi.
+ Tiêu đề của bảng dữ liệu nên được đặt tên ngắn gọn, viết liền nhau, chỉ nên dùng các ký tự A-Z, _ , 0-9. Số luôn đứng sau chữ: Tiêu đề các cột có dấu dẫn đến khi làm công thức dễ bị sai do nhầm lẫn, lập công thức mất thời gian vì việc soạn thảo nhiều. Định dạng kiểu dữ liệu trong các cột của bảng trước khi nhập dữ liệu.
+ Định dạng kiểu dữ liệu trong các cột của bảng trước khi nhập dữ liệu: Trong bảng tính Excel, các ô ngầm định đặt kiểu General, kiểu dữ liệu được xác định khi dữ liệu được nhập vào. Trong bảng dữ liệu nguồn nên có dòng đầu tiên để nhập giá trị giả định mà các giá trị này không ảnh hưởng tới kết quả của báo cáo. Các cột là dạng văn bản (TEXT) nhập giá trị „ (nháy đơn). Các cột có các giá trị ngày tháng, số lượng, số tiền, phần trăm, tỷ lệ (số) thì nhập số 0.
+ Trong bảng dữ liệu nguồn nên có dòng đầu tiên để nhập giá trị giả định mà các giá trị này không ảnh hướng tới kết quả của báo cáo.
Các cột là dạng văn bản (TEXT) nhập giá trị „ (nháy đơn). Các cột có các giá trị ngày tháng, số lượng, số tiền, phần trăm, tỷ lệ (số) thì nhập số 0.
Việc nhập giá trị giả định như trên giúp cho A-Tools hay đối tượng ADO hiểu được kiểu dữ liệu của các cột dữ liệu trong bảng được rõ ràng hơn.
Lưu ý:
+ Đối với các cột dữ liệu dưới dạng Text nếu những hàng dữ liệu không phát sinh thì trong quá trình nhập liệu. Phải nhập ký tự ngầm định là nháy đơn trong cột dữ trổng để hiểu giá trị ngầm định cho kiểu dữ liệu cột này là Text
+ Đối với các cột dữ liệu kiểu số, ngày tháng, %, tiền tệ,… Nếu không phát sinh các bạn vẫn phải nhập số 0 và 8 dòng dữ liệu đầu tiền để xác định kiểu dữ liệu cho các dòng này là trường số
+ Đặt tên vùng dữ liệu trong bảng tính Excel (worksheet)
Trong bảng tính có nhiều vùng dữ liệu, chỉ một khu vực nào đó đảm bảo đó là bảng dữ liệu – Table. Vậy để A-Tools hiểu khu vực đó là bảng thì người dùng cần đặt tên – NAME cho vùng dữ liệu này là bảng.
3.2 Hướng dẫn sử dụng hàm BS_SQL.
Hàm BS_SQL là hàm đặc biệt sẽ sử dụng ngôn ngữ SQL để làm báo cáo hết sức linh hoạt, đối với loại báo cáo được tạo bời hàm BS_SQL kết quả của toàn bộ báo cáo sẽ được tạo ra trong chỉ một công thức duy nhất không có công thức và dữ liệu thừa trong báo cáo. Vì vậy tốc độ chạy báo cáo vô cùng nhanh dù dữ liệu nhiều, với độ chính xác tuyệt đối.
Cấu trúc hàm BS_SQL
BS_SQL(SQL [,OPTIONS])
+ SQL: Là một chuỗi khai báo câu lệnh truy vấn dữ liệu theo ngôn ngữ T-SQL. Ngoài ra câu lệnh SQL trong hàm BS_SQL được phối hợp thêm các hàm A-Tools tạo thêm: RECNO, CELL, RANGE, DATA, GetOnce, FORMULA, FieldIf, VTC,… giúp tăng cường sức mạnh tối đa khi sử dụng phân tích dữ liệu
+ OPTIONS: Là tham số tuỳ chọn (có thể không có), tham số này là một chuỗi, cho phép khai báo thêm các tham số cho hàm, các tham số được ngăn cách nhau bởi dấu chấm phảy (;). Nếu không có tham số này, hàm BS_SQL sẽ truy vấn dữ liệu trong chính tập tin Excel đang mở hiện tại và trả về bảng kết quả lọc.
3.2.1 Sử dụng từ khóa SELECT
SELECT select_list
select_list: Danh sách các cột trong bảng dữ liệu “table_source”, cũng có thể là
danh sách các giá trị.
Mở tập tin “Examble.xls” vào sheet “Thực hành” làm các ví dụ sau:
Ví dụ 1: Chọn (SELECT) tất cả các cột từ (FROM) bảng DATA
=BS_SQL(“SELECT * FROM DATA”)
Ví dụ 2: lấy ra các cột dữ liệu MA_VLSPHH, SLG, DON_GIA, THANH_TIEN trong bảng DATA
=BS_SQL(“SELECT MA_VLSPHH, SLG, DON_GIA, THANH_TIEN FROM DATA”)
+ Cách sử dụng từng khóa TOP:
Ví dụ 3: Chọn 10 dòng đầu của tất cả các cột từ bảng DATA
=BS_SQL(“SELECT TOP 10 * FROM DATA”)
Cách sử dụng SQL Distinct: Câu lệnh distinct được sử dụng cùng với Select, dùng để select các dữ liệu, bỏ qua các dữ liệu trùng nhau. Cú pháp là: Select distinct <column1>, <column2> ….
Ví dụ 4: Chọn danh sách số chứng từ duy nhất (DISTINCT) từ sổ DATA
=BS_SQL(“SELECT DISTINCT SO_CT FROM DATA”)
• Đặt Alias cho cột:
Ví dụ 5: lấy ra các cột dữ liệu MA_VLSPHH, SLG, DON_GIA, THANH_TIEN trong bảng DATA
=BS_SQL(“SELECT MA_VLSPHH AS [Mã Hàng], SLG AS [Số Lượng], DON_GIA AS [Đơn Giá], THANH_TIEN AS [Thành Tiền] FROM DATA”)
Ví dụ 6: Có thể thực thi các phép toán, mở thêm cột tính toán sau từ khóa SELECT
=bs_sql(“SELECT 1+1 AS BT1, MA_VLSPHH, SLG, DON_GIA, SLG*DON_GIA AS [Thành Tiền], ” AS [Ghi chú] FROM DATA”)
1 + 1 AS BT1 => Cột BT1 được tạo ra, kết quả của nó là phép cộng 1 + 1 = 2
” AS [Ghi chú]=> Cột “Ghi chú” được tạo ra, kết quả của nó là giá trị chuỗi trống. Giá trị chuỗi trong SQL là nằm trong hai dấu nháy đơn „Chuỗi giá trị„
Các phép toán sử dụng về cơ bản giống như các phép toán bạn lập trong công thức Excel.
3.2.1 Sử dụng từ khóa FROM
FROM table_source table_source: là một hay nhiều bảng dữ liệu nguồn chứa các thông tin cần trích lọc (lấy ra), làm điều kiện lọc. Trong Excel, bảng dữ liệu thường được định nghĩa (Define Name) là một NAME có cấu trúc dạng bảng. Như các ví dụ trên chính là các vùng KHO, DMVLSPHH, NKC. Nó có thể là tên một sheet cụ thể, khi đó cần đặt theo mẫu [Tên sheet$]. Các ví dụ sau: =BS_SQL(“SELECT * FROM [DATA$A3:K68]”) Nếu vùng dữ liệu KHO!A3:K68 được đặt tên (Define Name) là DATA thì công thức như sau =BS_SQL(“SELECT * FROM DATA”) Nếu lấy dữ liệu theo sheet thì công thức như sau = BS_SQL(“SELECT * FROM [DATA$]”) Công thức lấy theo sheet [DATA$] tuy chạy được nhưng chúng ta không nên dùng vì có thể sheet chứa nhiều loại dữ liệu khác nhau, không theo cấu trúc bảng/table dẫn đến lỗi trong quá trình truy vấn dữ liệu. Chúng ta thống nhất dùng phương pháp đặt tên vùng dữ liệu có cấu trúc dạng bảng để làm việc với hàm BS_SQL trong Add-in ATools.
3.2.3 Sử dụng từ khóa WHERE
WHERE search_condition
search_condition: là điều kiện – biểu thức logic/so soánh. Các bản ghhi được lọc ra nếu biểu thức so sánh trả về TRUE. Theo T-SQL/A-Tools Nếu giá trị so sánh là văn bản thì phải đặt trong hai dấu nháy đơn ( ‘ ) , trong Excel là nháy kép “.
Các toán tử so sánh là: > , >= , < , <= , <> , = , LIKE , IN, BETWEEN
Khi so sánh tập ký tự bất kỳ ta dùng ký tự % , ký tự này dùng với toán tử là LIKE
Toán tử kết hợp logic: AND , OR , NOT
Ví dụ 7: Lấy ra tất cả các cột từ bảng DATA ở đó mã khách hàng (MA_KH) là KH001
=BS_SQL(“SELECT * FROM KHO WHERE MA_KH=’KH001′ “)
Ví dụ 8: Lấy ra các cột SO_CT, NGAY_CT, DIEN_GIAI, THANH_TIEN từ sổ NKC ở đó ký tự đầu của NOTK là 6
=BS_SQL(“SELECT SO_CT, NGAY_CT, DIEN_GIAI, NOTK, COTK, THANH_TIEN FROM NKC WHERE NOTK LIKE ‘6%’ “)
Ký tự % được dùng làm đại diện để so sánh với nhóm ký tự bất kỳ, thường dùng với toán tử LIKE.
Ví dụ 9: Lấy ra các cột SO_CT, NGAY_CT, DIEN_GIAI, THANH_TIEN từ sổ NKC ở đó ký tự đầu của cột NOTK là 11 và COTK có ký tự đầu là 5
=bs_sql(“SELECT SO_CT, NGAY_CT, DIEN_GIAI, NOTK, COTK, THANH_TIEN FROM NKC WHERE NOTK LIKE ‘11%’ AND COTK LIKE ‘5%’ “)
Trong search_condition, bạn có thể sử dụng IN (tập danh sách giá trị) để làm điều kiện.
Sau IN ta có thể là một cột dữ liệu lấy ra bởi SELECT, hoặc danh sách các giá trị cách nhau bởi dấu phảy (,).
Ví dụ 10: Chọn tất cả các cột dữ liệu (*) từ bảng DATA ở đó mã hàng (MA_VLSPHH) thuộc danh sách các mã HH001, HH002, HH003 và LOAI_PHIEU là nhập (N)
=bs_sql(“SELECT * FROM DATA WHERE MA_VLSPHH IN (‘HH001′,’HH002′,’HH003′) AND LOAI_PHIEU=’N’ “)
Ví dụ 11: Lấy ra tất cả các cột dữ liệu (*) từ bảng DATA ở đó mã hàng (MA_VLSPHH) có trong danh sách mã có loại (DMVLSPHH.LOAI) là VL và và LOAI_PHIEU là nhập (N).
Trong tập tin “Examble.xls” có sheet “DMVLSPHH” chứa thông tin về danh mục hàng hóa.
(Vùng A3:D15 được đặt tên (Define Name) là “DMHH” )
=bs_sql(“SELECT * FROM DATA WHERE MA_VLSPHH IN (SELECT MA_VLSPHH FROM DMHHWHERE LOAI = ‘VL’) AND LOAI_PHIEU=’N’ “)
SELECT MA_VLSPHH FROM DMVLSPHH WHERE LOAI = ‘VL’
Qua ví dụ ta thấy sự linh hoạt troang ngôn ngữ T-SQL. Có thể làm điều kiện mà dữ liệu làm điều kiện nằm ở sheet khác. Ví dụ trên, dữ liệu lấy ra là bảng DATA nhưng dữ liệu làm điều kiện lấy ở DMHH so sánh với cột MA_VLSPHH ở sổ KHO.
Ví dụ 12: Lấy ra tất cả các cột dữ liệu (*) từ sổ DATA ở đó THANH_TIEN >= 19000000
=bs_sql(“SELECT * FROM KHO WHERE THANH_TIEN >= 19000000 “)
Ví dụ 13: Lấy ra tất cả các cột dữ liệu (*) từ sổ KHO ở đó THANH_TIEN trong khoảng từ 19000000 đến 25000000
=bs_sql(“SELECT * FROM KHO WHERE THANH_TIEN >= 19000000 AND THANH_TIEN <= 25000000 “)
Cách khác là dùng BETWEEN
=bs_sql(“SELECT * FROM KHO WHERE THANH_TIEN BETWEEN 19000000 AND 25000000 “fieldname BETWEEN a AND b
Tương đương với só sánh fieldname >= a AND fieldname <= b
Nếu fieldname so sánh trước a và sau b không có dấu bằng (= ) thì không được dùng BETWEEN
Ví dụ 14: Lấy ra tất cả các cột dữ liệu (*) từ bảng DATA ở đó ngày chứng từ (NGAY_CT) lớn hơn 24/06/2006
=bs_sql(“SELECT * FROM KHO WHERE NGAY_CT >= #10/24/2006# “)
(Trong T-SQL của Excel, giá trị ngày phải nằm trong hai dấu #, theo định dạng MM/dd/yy)
Lưu ý: các giá trị tham gia vào biểu thức trong SQL của Excel theo quy định như sau: Giá trị văn bản‟ – Có ký tự nháy đơn ( „ ) bao hai bên #Giá trị ngày tháng # – Có ký tự # bao hai bên Số, phần trăm, tỷ lệ (Number) – Không có ký tự bao hai bên
Qua bài viết cơ bản trên tôi đã hướng dẫn cho các bạn chi tiết các bước chuẩn hóa bảng dữ liệu nguồn cũng như chi tiết các bước khai thác 3 từ khóa trong bộ 6 từ khóa chính là SELECT, FROM, WHERE chỉ với 3 từ khóa các bạn thấy ngay việc tính toán, trích lọc tạo báo cáo theo bất kỳ chiều nào đã trở lên vô cùng đơn giản. Các bạn hoàn toàn đã có thể chủ động, tự tay thiết kế tạo ra hàng nghìn mẫu báo cáo theo hàng trăm điều kiện hết sức nhanh chóng, linh hoạt theo bất kỳ chiều nào. Đây mới là 1/10 phần sức mạnh của ngôn ngữ này, vậy bạn còn chần chờ gì nữa hãy gọi điện ngay cho chúng tôi, chúng tôi cam kết sẽ giúp bạn phát huy 100% sức mạnh của SQL trên Excel để tự tay tạo ra vô số báo cáo, ứng dụng tự động không ngừng sáng tạo tạo ra nhiều giá trị hơn cho doanh nghiệp, với sức mạnh gấp 10 lần so với người bình thường khi phân tích dữ liệu bằng phương pháp truyền thống.
