Xu Hướng 2/2024 # Tự Động Lọc Dữ Liệu Trước Khi Nhập Từ Sql Server Trong Excel # Top 7 Xem Nhiều

Bạn đang xem bài viết Tự Động Lọc Dữ Liệu Trước Khi Nhập Từ Sql Server Trong Excel được cập nhật mới nhất tháng 2 năm 2024 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.

Vì vậy, nếu tôi nhập dữ liệu vào bảng, hãy áp dụng bộ lọc cho, ví dụ: Quốc gia là Vương quốc Anh, đóng tệp và mở lại vào tuần tới, lý tưởng là chỉ nên truy vấn và nhập dữ liệu cho Vương quốc Anh. Và khi tôi chọn một quốc gia khác, chỉ nên nhập dữ liệu cho quốc gia đó. Tôi cần các lựa chọn để được liệt kê từ bảng.

Bây giờ, tôi không tin các bảng có khả năng đó. Nhưng làm thế nào về Máy cắt, PivotTable, PowerView hoặc PowerP Pivot?

Đánh giá cao bất kỳ ý tưởng.

Bạn hoàn toàn có thể làm điều này với Bảng, cũng như PowerpOLL và Data Explorer. Cách dễ nhất và phương pháp ưa thích của tôi hiện tại là tạo một truy vấn được tham số hóa trong Excel. Để thực hiện việc này, hãy sử dụng Truy vấn Wizard và ở cuối, thêm các ràng buộc cho từng tham số bạn muốn vượt qua. Khi điều này được thực hiện, bạn có thể chỉnh sửa Thuộc tính kết nối và gán Tham số được trả về dưới dạng giá trị tĩnh, hộp thoại hoặc ô bảng tính.

Trong Cửa sổ Truy vấn MS, nhấp vào Show/Hide Criteria button và chọn trường Tiêu chí. Đây sẽ là những gì gọi các tham số của bạn để quay trở lại SQL. [.__.]

Ví dụ: Nếu bạn có trường ngày và chọn nó trong trường Tiêu chí, sau đó thêm Giá trị giữa [bắt đầu] và [kết thúc]. Điều này sẽ tạo một tham số và MS Query sẽ yêu cầu bạn điền cả hai giá trị và trả về ngay bộ dữ liệu phù hợp. Nếu bạn kiểm tra câu lệnh SQL mà nó đã tạo, nó sẽ thêm mệnh đề WHERE (dataource.date giữa? Và?).

Đối với mỗi tham số, bạn có thể chọn tham số đó [.__.]

Nhắc bạn để cung cấp một giá trị; hoặc là

Sử dụng một giá trị cụ thể; hoặc là

Nhận giá trị từ một ô bảng tính, với tùy chọn tự động cập nhật dữ liệu bảng của bạn bất cứ khi nào giá trị ô đó thay đổi. [.__.]

Là một tùy chọn bổ sung, bạn có thể sử dụng Xác thực dữ liệu trên ô bạn chọn cho tham số của mình, cho phép bạn giới hạn các giá trị có thể chọn. Bạn thậm chí có thể có các ô xác thực đến từ một bảng được cung cấp từ các giá trị từ cơ sở dữ liệu của bạn, đảm bảo rằng chỉ các giá trị được giữ trong cơ sở dữ liệu có thể được chọn.

Đặt giá trị paramater của bạn cho từng tham số và nhấp OK thông qua tất cả các hộp thoại.

Tôi sẽ thường tạo Bảng Pivot dựa trên Bảng được cung cấp từ truy vấn được tối ưu hóa để trả về các giá trị cho các phạm vi ngày cụ thể, được cập nhật tự động (ví dụ: 6 tháng trước).

EDIT : Để trả về nhiều giá trị để sử dụng trong mệnh đề SQL IN, bạn chỉ cần sửa đổi cửa sổ Tiêu chí trong hộp Truy vấn MS. Ví dụ: nếu bạn có tên trường của Quốc gia, hãy chọn nó trong Trường Tiêu chí, sau đó cho Giá trị nhập IN ([Đầu tiên], [Thứ hai], [Thứ ba]). Chọn Trả về dữ liệu cho Excel và MS Query sẽ yêu cầu nhập các giá trị thủ công. Khi bạn quay lại Excel, bạn có thể sửa đổi lại các tham số và trỏ chúng vào ba ô trong bảng tính của bạn, mỗi ô có thể bị ràng buộc bởi Xác thực dữ liệu. Mặc dù có thể có giới hạn về số lượng tham số bạn có thể vượt qua như thế này, Excel dễ dàng xử lý ba tham số, do đó, một số ít có thể không nằm ngoài câu hỏi.

Nếu bạn không phiền khi sử dụng VBA thì cách ‘nhẹ’ để thực hiện điều này là bằng cách tự động tạo truy vấn SQL.

Ví dụ.

Bảng ẩn ‘Các biến’ chứa một bảng ‘Table_Query_SALES_EMPLOYEES’. Điều này được SQL đưa ra để chỉ trả về mức tối thiểu cần thiết cho lựa chọn param. Một cái gì đó như SELECT DISTINCT AccountDirector FROM InvoicedSales ORDER BY AccountDirector.

Sau đó, tôi có một phạm vi được đặt tên ‘Sales_Emp viên’ mà phạm vi của nó tham chiếu là =Table_SQL_SALES_EMPLOYEES[AccountDirector]. Điều này được ánh xạ tới hộp tổ hợp, có Phạm vi đầu vào được đặt thành ‘Sales_Emp viên’. Điều này sẽ gửi một số đến một ô bạn chọn (E5 trong trường hợp của tôi) của mục trong danh sách đã chọn. Để trả về giá trị thực tế theo trích xuất SQL gốc, bạn cần sử dụng =OFFSET(Table_SQL_SALES_EMPLOYEES[[#Headers],[AccountDirector]],$E$5,0). Cái này được đặt tên là ‘Slp’.

.: Bây giờ chúng tôi có một hộp lựa chọn thả xuống của tất cả các giám đốc tài khoản có thể. Cũng có một số khác cho quý tài chính, nhưng cùng một nguyên tắc :.

Khi lựa chọn được thay đổi trong hộp tổ hợp, nó sẽ gọi macro ‘changeFilter ()’

Sub changeFilter() Dim wb As Workbook Set wb = Excel.ActiveWorkbook Dim ws As Worksheet Set ws = Excel.ActiveWorkbook.Sheets("Lookups") Dim conn As Excel.WorkbookConnection Dim slp As Name Set slp = wb.Names("Slp") Dim qtr As Integer qtr = wb.Names("qtr").RefersToRange.Value2 Dim query As String ' Adjusted Sales Consolidated Set conn = wb.Connections("SQL_ADJUSTEDSALES_CONSOLIDATED") query = "SELECT * FROM InvoicedSales WHERE AccountDirector=" & "'" & slp.RefersToRange.Value2 & "'" conn.OLEDBConnection.CommandText = query conn.Refresh

Về cơ bản, tôi định cấu hình trước các bảng/pivots bằng cách nối chúng với cơ sở dữ liệu với các biến được mã hóa cứng và chọn các tùy chọn ‘Làm mới dữ liệu khi mở tệp’ và ‘Xóa dữ liệu khỏi phạm vi dữ liệu ngoài trước khi lưu sổ làm việc’ – điều này giữ cho tệp nhỏ như không có gì thực sự được cứu.

Sau đó, bộ lọc thay đổi ghi đè biến mã hóa cứng bằng truy vấn SQL được tạo động bằng cách sử dụng tham số đã chọn và sau đó làm mới bảng.

Hướng Dẫn “Lọc Tự Động Và Lọc Chi Tiết Dữ Liệu Trong Excel”

Tương tự như Text Filter, cửa sổ Number Filter xuất hiện khi trong cột cần lọc chứa các giá trị Number

Cách tách họ và tên trong excel không phải ai cũng biết Bạn biết cấu trúc và cách dùng hàm LEFT trong Excel chưa? TẠO BẢNG BIẾN ĐỘNG DOANH SỐ BÁN HÀNG BẰNG EXCEL CỰC HỮU ÍCH CHO KẾ TOÁN BÁN HÀNG Cách ẩn và hiện các dòng/ cột trong Excel vô cùng đơn giản.

Với mục tiêu “Sự thành công của học viên là niềm tự hào của Đức Minh”, Công ty đào tạo kế toán và tin học Đức Minh là nơi đào tạo kế toán thực tế và tin học văn phòng uy tín và chuyên nghiệp nhất Hà Nội hiện nay. Đức Minh luôn sẵn sàng hỗ trợ hết mình vì học viên, luôn đồng hành cùng học viên trên bước đường đi tới thành công.

Lịch học dạy kèm linh động từ thứ 2 đến thứ 7 hàng tuần cho tất cả các học viên:

Bảng giá khóa học

TỔ CHỨC THI VÀ CẤP CHỨNG CHỈ CỦA VIỆN KẾ TOÁN ĐỨC MINH

Mọi chi tiết vui lòng liên hệ:

HỌC VIỆN ĐÀO TẠO KẾ TOÁN – TIN HỌC ĐỨC MINH

Cơ Sở Đống Đa: Phòng 815, tòa 15 tầng – B14 đường Phạm Ngọc Thạch, Đống Đa, Hn. (tầng 1 là Techcombank và KFC- gửi xe đi vào ngõ 65 Phạm Ngọc Thạch) – 0339.418.980Cơ Sở Cầu Giấy: Tầng 2 – Tòa nhà B6A Nam Trung Yên – đường Nguyễn Chánh – Cầu Giấy HN – 0339.156.806Cơ Sở Linh Đàm: Phòng 404 – Chung cư CT4A1 – Đường nguyễn Hữu Thọ – Linh Đàm – Hoàng Mai Hà Nội. (Ngay đèn xanh đỏ cổng chào Linh Đàm, Tầng 1 siêu thị Bài Thơ, Highlands Cofee) – 0342.254.883Cơ Sở Hà Đông: Phòng 1001 tầng 10, CT2 tòa nhà Fodacon (tầng 1 là siêu thị Coopmart, đối diện Học Viện An Ninh) – Trần Phú – Hà Đông – 0339.421.606

Cách Đánh Số Thứ Tự Trong Excel Khi Lọc Dữ Liệu

Quay trở lại tiếp với sử dụng các hàm và chức năng trong excel, người dùng excel thường xuyên chắc cũng phải nắm được tương đối và cơ bản nhất những gì trong excel. Tuy nhiên rằng khi làm việc trên excel các bạn thường sử dụng số thự tự động mặc định trên excel, tiện lợi nhưng cũng sẽ có những hạn chế khi bạn làm việc với một loạt các danh sách và hàng trăm các dữ liệu khác nhau và khi bạn muốn xóa đi dữ liệu nào bạn sẽ không còn giữ được thứ tự ban đầu nữa. Vậy nên hàm subtotal sẽ giúp bạn làm công việc đánh số thứ tự trong excel khi lọc dữ liệu.

Hàm Subtotal là gì?

Trong Microsoft excel chứa một hệ thống các hàm chức năng riêng biệt và mỗi hàm chức năng lại kết hợp tạo ra một loạt các chức năng nâng cao khác. Nhưng không phải một hàm chỉ có một chức năng riêng, một hàm cũng có thể thực hiện được nhiều chức năng và cũng có những hàm thực hiện một chức năng cũng giống một số hàm khác nhưng có sự trội hơn rất rất nhiều của một số hàm khác kết hợp lại. Nói đến đây là hàm Subtotal, một hàm để thực hiện chức năng tính toán nhưng việc tính toán của hàm còn linh động và ưu việt hơn rất nhiều so với các hàm tính toán trong excel khác như Sum, Max, Min,…

Ngoài ra hàm còn được dùng để đánh số thứ tự tự động khi mà người dùng lọc, xóa hoặc bỏ một số các thứ tự dữ liệu làm mất thứ tự đi thì hàm sẽ thực hiện công việc duy trì số thự tự một cách chuẩn chỉ nhất.

Cú pháp hàm

Cách đánh số thứ tự khi xóa với hàm Subtotal ( hình 1)

Ref1: đây là những đối số và cũng có thể là các giá trị khác nhau tùy theo yêu cầu người dùng và dữ liệu trong bảng tính dùng để đưa vào công thức áp dụng với chức năng của hàm.

Cách sử dụng hàm subtotal để đánh số thứ tự trong Excel khi lọc dữ liệu

Cách đánh số thứ tự khi xóa với hàm Subtotal ( hình 3)

Enter và nhận được số thứ tự thứ nhất, kéo sao chép công thức xuống phía dưới và được các số thứ tự tiếp theo:

Cách đánh số thứ tự khi xóa với hàm Subtotal ( hình 5)

Hàm đã thành công trong việc tạo thứ tự, khi chúng ta thử xóa đi quất và thứ tự được làm lại.

Nhiều người khi học về Excel thường đánh giá đây là bộ môn “khó nhai” bởi một loạt các hàm tính rắc rối, thực tế việc học Excel sẽ nhẹ nhàng hơn rất nhiều nếu như bạn nắm được các thao tác cơ bản và thực hành chúng một cách thường xuyên. Đến với khóa học “Trở thành cao thủ Excel trong 10 giờ” sẽ giúp bạn thành thạo các hàm Excel thông dụng, ứng dụng vào thực tế để xử lý, phân tích, thống kê dữ liệu một cách nhanh chóng.

Chi tiết khóa học “Trở thành cao thủ Excel trong 10 giờ” có trên Unica

Nội dung khóa học Trở thành cao thủ Hàm Excel trong 10 giờ sẽ tập trung đi sâu vào giảng dạy cách sử dụng từng nhóm hàm Excel và ứng dụng của nó vào thực tế để xử lý, phân tích, thống kê dữ liệu một cách nhanh chóng. Một số hàm toán học – thống kê như: SUM, PRODUCT, SUBTOTAL, SUMIF, MAX, MIN- SMAL…., nhóm hàm xử lý chuỗi như: FIND, UPPER, LOWER và PROPER, TRIM…., nhóm hàng ngày tháng, hàm dò tìm dữ liệu…Khóa học còn bao gồm phần giải các bài tập tổng hợp, giúp bạn dễ dàng nắm bắt nội dung, kết hợp giữa lý thuyết và thực hành, giúp bạn ôn luyện chứng chỉ Excel một cách hiệu quả

Điểm đặc biệt của khóa học này là mua khóa học một lần sở hữu trọn đời. Bạn có thể học bất cứ mọi lúc mọi nơi khi điện thoại hoặc máy tính có kết nối Internet. Kết thúc khóa học ngoài việc học được cách đánh số thứ tự trong Excel khi lọc dữ liệu bạn còn có thể sử dụng thành thạo các nhóm Hàm Excel thông dụng trong thực tế. Qua đó tăng hiệu suất công việc và tự tin hoàn thành các chứng chỉ về tin học văn phòng.

XEM CHI TIẾT KHÓA HỌC

Như vậy sử dụng hàm Subtotal quả là điều tuyệt vời với người dùng khi đánh số thứ tự trong Excel khi lọc dữ liệu. Biết cách sử dụng hết các hàm chức năng trong excel, đó là điều không phải dễ nếu như bạn không được sự học bài bản và khoa học. Khóa học TRỞ THÀNH CAO THỦ EXCEL TRONG 10 GIỜ sẽ đem đến cho các bạn kiến thức excel từ cơ bản đến nâng cao, với các bài giảng dễ hiểu và khoa học, đảm bảo cho bạn lượng kiến thức chắc chắn giúp bạn có thể thành thạo về excel.

Lọc Dữ Liệu Trong Excel Theo Database Bằng Sql Vô Cùng Đơn Giản

Đây là bài số #26 trong loạt videos Hướng dẫn VBA trong Excel và ứng dụng của VBA trong công việc

Đã bao giờ các bạn phải xử lý một lượng dữ liệu rất lớn ở trong Excel từ vài chục ngàn cho đến hàng trăm ngàn dòng? Các công cụ filter hay bảng pivot có thể một phần nào xử lý được những công việc trích lọc dữ liệu theo điều kiện, sắp xếp dữ liệu nặng nhọc này trong Excel cho bạn nhưng với tốc độ khá chậm, và đôi khi chúng ta còn có thể gặp trường hợp treo máy tính hoặc Excel ngừng hoạt động bởi vì lượng dữ liệu này quá lớn thậm chí Excel có thể thoát luôn và không thể khôi phục được dữ liệu? Khi tất cả các kĩ thuật xử lý dữ liệu bình thường trong excel đã được thử qua, các bạn có thể nghĩ đến SQL. Vậy SQL là gì và chúng ta sử dụng SQL trong Excel như thế nào? Bài viết và video này sẽ giúp cho các bạn có thêm một công cụ rất hữu dụng nữa để xử lý dữ liệu trong Excel.

SQL là cái gì?

SQL là từ viết tắt tiếng Anh của Structured Query Language, là một ngôn ngữ được phát triển chuyên để truy vấn dữ liệu từ một hệ thống quản trị cơ sở dữ liệu ( Wikipedia).

Nói một cách đơn giản hơn thì SQL sẽ giúp chúng ta lấy dữ liệu từ bảng tính này sang bảng tính khác hoặc SQL sẽ giúp chúng ta lọc dữ liệu theo nhiều điều kiện khác nhau theo cách mà các công cụ truyền thống như Advanced Filter hay Bảng Pivot có thể gặp khó khăn khi xử lý rất nhiều dữ liệu.

Ví dụ về sử dụng SQL trong Excel Bảng dữ liệu trong Video

Trước khi xử lý bất kì dữ liệu nào, chúng ta cần đi tìm hiểu cấu trúc của bảng dữ liệu ở đây:

Bảng dữ liệu của chúng ta bao gồm 7 cột từ cột A cho đến cột G.

Vùng dữ liệu được lưu là A1:G20000

Mỗi cột chứa một thuộc tính của dữ liệu:

Sử dụng SQL để lọc hay truy cập dữ liệu trong bảng tính

Sau khi xác định được bảng dữ liệu của chúng ta như thế nào, chúng ta có thể sử dụng SQL để truy cập dữ liệu này với những câu lệnh SQL sau:

Truy cập dữ liệu từ bảng tính Excel bằng SQL

Dấu * ở trong câu lệnh này nghĩa là chúng ta muốn dữ liệu của tất cả các cột. Nếu các bạn sử dụng file Excel trong video để thực hiện những câu lệnh SQL này, bạn cần thêm mi_sql vào đằng trước câu lệnh: mi_sql SELECT * FROM [A1:G20000]

Truy cập dữ liệu của một số cột nhất định: chúng ta ghi tên cột ra sau từ khoá SELECT và viết các cột ngăn cách nhau bằng dấu phẩy ,

Lọc dữ liệu bảng tính Excel bằng SQL

Lọc dữ liệu từ bảng tính Excel với 1 điều kiện: Lọc dữ liệu của những đơn hàng từ Hanoi

Câu lệnh SQL trên có nghĩa là: từ vùng dữ liệu A1:G20000, lọc ra những dòng mà cột Region có giá trị là Hanoi

Từ khoá WHERE giúp chúng ta liệt kê điều kiện khi lọc dữ liệu.

Lọc dữ liệu từ bảng tính Excel với nhiều điều kiện: Lọc dữ liệu của những đơn hàng từ Hanoi hoặc Danang

Câu lệnh SQL trên có nghĩa là: từ vùng dữ liệu A1:G20000, lọc ra những dòng mà cột Region có giá trị là Hanoi hoặc Danang

Ngoài cách dùng từ khoá OR, chúng ta cũng có thể dùng từ khoá IN và câu lệnh SQL sẽ như sau:

Lọc dữ liệu của những đơn hàng bán sản phẩm Pencil từ Hanoi

Câu lệnh SQL trên có nghĩa là: từ vùng dữ liệu A1:G20000, lọc ra những dòng mà cột Region có giá trị là Hanoi và cột Item có giá trị là Pencil.

Lọc dữ liệu của những đơn hàng bán sản phẩm Pencil từ Hanoi và người bán là Jones

Lọc dữ liệu của những đơn hàng bán sản phẩm có đơn giá (Unit Cost) nhỏ hơn hoặc bằng $ 8.99

Lưu ý: Chúng ta có thể thấy được trong ví dụ này, tên cột của bảng tính này là “Unit Cost” có dấu cách ở trong. Tốt nhất để tránh xảy ra lỗi khi truy vấn dữ liệu thì chúng ta không nên viết tên cột có dấu cách hoặc kí tự đặc biệt (có nghĩa là không viết tên cột bằng tiếng Việt có dấu). {. :notice}

Lọc dữ liệu của những đơn hàng bán sản phẩm có đơn giá (Unit Cost) nhỏ hơn hoặc bằng $8.99 sắp xếp từ mặt hàng có đơn giá đắt nhất đến mặt hàng có đơn giá rẻ nhất

Ở trong ví dụ này, chúng ta có thể thay [Unit Cost] trong ORDER BY [Unit Cost] DESC bằng một cột khác. Nếu kiểu dữ liệu của một cột là dạng chuỗi thì cột đó sẽ được sắp xếp từ Z đến A, nếu kiểu dữ liệu của một cột là dạng số thì cột đó sẽ được sắp xếp nhỏ dần.

Lọc dữ liệu của những đơn hàng bán sản phẩm có đơn giá (Unit Cost) lớn hơn hoặc bằng $8.99 sắp xếp từ mặt hàng có đơn giá rẻ nhất đến mặt hàng có đơn giá đắt nhất

Ở đây, khi chúng ta sử dụng ORDER BY và không ghi DESC như ví dụ trên thì SQL sẽ tự động sắp xếp theo chiều thuận nghĩa là nếu dữ liệu là chuỗi, thì sẽ được sắp xếp từ A đến Z, nếu dữ liệu là số thì sẽ được sắp xếp tăng dần.

Lọc dữ liệu của những đơn hàng bán sản phẩm có đơn giá (Unit Cost) lớn hơn hoặc bằng $4.99 và nhỏ hơn hoặc bằng $15.99

Ngoài ra chúng ta cũng có thể sử dụng từ khoá BETWEEN để lọc dữ liệu trong khoảng giá trị

Hướng Dẫn Cách Tự Động Lọc Dữ Liệu Trong Excel Dùng Vba

Trong bài viết về Hướng dẫn cách lọc dữ liệu trong Excel có bài tập kèm theo, Gitiho đã hướng dẫn các bạn cách sử dụng bộ lọc Filter. Nhưng đó đều là các thao tác lọc hoàn toàn thủ công, bạn phải tác động trực tiếp vào bộ lọc. Việc đó có thể mất nhiều thời gian và kém hiệu quả nếu bạn phải sử dụng bộ lọc liên tục. Trong bài viết này Gitiho sẽ hướng dẫn các bạn cách tự động hóa cho bộ lọc Filter bằng cách sử dụng VBA.

Cách tự động hóa cho bộ lọc Filter bằng cách sử dụng VBA

Cách sử dụng VBA để tự động hóa cho các thao tác thường sử dụng trong Excel rất đơn giản. Bạn có thể áp dụng VBA với bộ lọc Filter như sau:

Bước 1: Mở thẻ View hoặc thẻ Developer trên thanh công cụ (nếu bạn chưa biết cách mở thẻ Developer thì xem hướng dẫn ở đây), sau đó bấm chọn mục Record Macro

Khi đó xuất hiện 1 cửa sổ thông tin về Macro được ghi, bạn có thể sửa lại tên macro tại mục Macro Name, hoặc để tên macro mặc định là Macro1 (số 1 có thể thay đổi sau mỗi lần bạn thực hiện record macro)

Bước 2: Thực hiện thao tác lọc dữ liệu trong cột Nhân viên với 1 tên nhân viên bất kỳ

Bước 3: sau khi lọc xong, bạn chọn lại tới vị trí Record Macro đã thực hiện ở bước 1, lúc này sẽ hiển thị là Stop recording. Bạn hãy bấm vào nút Stop recording đó.

Bước 4: mở cửa sổ VBA (phím tắt là Alt + F11 hoặc bấm vào mục Visual Basic trong thẻ Developer) để kiểm tra xem đã có đoạn code cho thao tác lọc chưa.

Trong cửa sổ Project, tại Workbook bài tập, kiểm tra xem có Module 1 không

Trong đoạn code này, chúng ta chú ý ở mục Criteria1:=”Mai”. Đây chính là điều kiện lọc mà chúng ta đã sử dụng ở bước 2.

Bước 5: Thay đổi điều kiện bằng giá trị tại ô E2

Bạn sửa lại chữ “Mai” bằng Range(“E2”).Value

Kết quả sau khi sửa như sau:

Bước 6: Tiếp tục thực hiện ghi Macro cho thao tác bỏ điều kiện lọc (Clear Filter):

Lúc này chúng ta thu được đoạn code cho thao tác bỏ lọc như sau:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range(“E2”)) Is Nothing Then If Range(“E2”).Value = “” Then Macro2 Else Macro1 End If End If End Sub

Ý nghĩa của đoạn code là:

Xét sự thay đổi trong Worksheet

Nếu có sự thay đổi trong ô E2 thì

Xét trường hợp ô E2 không có dữ liệu thì

Chạy Macro2 (là macro bỏ điều kiện lọc)

Nếu không thì (ô E2 có dữ liệu)

Chạy Macro1 (là macro lọc theo điều kiện giá trị tại ô E2)

kết thúc cấu trúc IF xét điều kiện ô E2 không có dữ liệu

Kết thúc cấu trúc IF xét sự thay đổi tại ô E2

Kết thúc Macro

Như vậy khi bạn thực hiện chọn 1 giá trị tại ô E2, ngay lập tức VBA sẽ chạy Macro1 – là macro lọc dữ liệu. Nếu bạn xóa nội dung trong ô E2, VBA sẽ chạy Macro2 – là macro bỏ điều kiện lọc. 2 việc này hoàn toàn tự động mỗi khi bạn có sự thay đổi tại ô E2.

Tải file kết quả theo địa chỉ: https://bit.ly/3e6Ahjx

Tổng kết

Ngoài ra nếu bạn muốn học VBA trong Excel một cách bài bản, có hướng dẫn cụ thể qua video, có giảng viên hỗ trợ… thì đừng bỏ lỡ khóa học VBAG01 – Tuyệt đỉnh VBA – Viết code trong tầm tay của Gitiho. Chắc chắn bạn sẽ hài lòng khi tham gia khóa học này.

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:

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,…

Tạo Thư Tự Động Từ Dữ Liệu Excel Sang Word

Trộn thư () là một công cụ vô cùng tiết kiệm thời gian khi phải gửi một lượng thư lớn. Nó giúp bạn nhanh chóng tạo một lá thư, email hay tựa thư trong Word bằng cách hợp nhất thông tin bạn có trong trang tính Excel. Học excel nâng cao Online sẽ cung cấp cho bạn tổng quan của đặc tính này và hướng dẫn trộn thư theo từng bước.

Trộn thư có thể trông khó khăn, nhưng trên thực tế quá trình này khá đơn giản.

Dể giúp bạn hiểu sâu hơn, quá trình trộn thư gồm 3 tài liệu:

Văn bản Word chứa mã ứng với từng phần cụ thể.

Tệp nguồn trộn thư Excel chứa thông tin người nhận, mỗi hàng là một người nhận.

Bản Word cuối cùng với thư, email, bì thư hoàn chỉnh.

Chuẩn bị trang tính Excel cho trộn thư:

Khi bạn trộn thư, tệp Excel sẽ được kết nối với tài liệu trộn thư của Word. Microsoft Word sẽ lấy tên, địa chỉ và các chi tiết khác của người nhận trực tiếp từ bảng tính Excel.

Vì vậy, trước khi bắt đầu, hãy chắc chắn rằng tệp Excel đã có tất cả thông tin bạn muốn bao gồm tên, họ, lời chào, mã bưu điện, địa chỉ… Nếu bạn muốn thêm nhiều địa chỉ liên hệ hoặc thông tin khác, bạn nên thay đổi hoặc bổ sung bên trang tính Excel trước khi trộn thư.

Những điều quan trọng cần kiểm tra:

Các cột trong bảng tính Excel phải khớp với các phần bạn muốn sử dụng khi trộn thư. Ví dụ: nếu bạn muốn xưng hô với người đọc bằng Tên, hãy chắc chắn đã tạo hai cột họ và tên riêng biệt. Nếu bạn muốn sắp xếp tên người nhận theo bang hoặc thành phố, xác định rằng bạn đã có hai cột Bang và Thành phố riêng biệt.

Nếu tệp Excel bao gồm ngày, giờ, giá trị tiền tệ hoặc mã bưu điện bắt đầu hoặc kết thúc bằng 0, hãy định dạng đúng số, số ngày và đơn vị tiền tệ.

Nếu bạn tạo trang tính Excel bằng cách nhập thông tin từ tệp .csv hoặc tệp .txt, hãy sử dụng Text Import Wizard.

Cách trộn thư từ Excel sang Word:

Khi hoàn thành và kiểm tra lại trang tính Excel, bạn đã sẵn sàng để trộn thư. Trong ví dụ này, chúng ta sẽ trộn thư với một danh sách người nhận trong Wrd 2010, tương tự cho Word 2013.

Nếu bạn đã soạn thư sẵn một lá thư, bạn có thể mở một tài liệu Word hiện có, hoặc tạo một tài liệu mới.

Kết nối trang tính Excel và bản Word. Bằng việc thiết lập kết nối giữa Excel và Word, hãy đảm bảo rằng nguồn dữ liệu trộn thư sẽ được tự động cập nhật mỗi lần bạn thay đổi dữ liệu người nhận trên tệp Excel.

Tìm hiểu thêm: Khóa học excel online

Trong hộp thoại Select Data Source, tìm trang tính Excel và nhấp . Nếu Word yêu cầu bạn chọn từ bảng, hãy chọn và nhấp OK.

Như vậy, chúng ta đã hoàn thành danh sách người nhận và sẵn sang điền chúng vào thư. Gõ thư mẫu vào Word hoặc sao chép/dán từ một nguồn khác.

Thêm chỗ điền. Bạn cần xác định vị trí điền địa chỉ Address Block và dòng chào hỏi Greeting Line. Tới phần Write & Insert Fields trong thẻ .

Trong nhiều trường hợp khác, bạn có thể đặt dữ liệu của người nhận vào nội dung thư để tùy chỉnh chúng theo cá nhân. Để thực hiện việc này, nhấp Insert Merge Field và chọn dữ liệu bạn muốn chèn từ danh sách tùy chọn.

Xem trước lá thư. Chắc chắn rằng dữ liệu người nhận xuất hiện chính xác trong lá thư, nhấn nút Preview Results trên thẻ .

Hoàn tất trộn thư. Khi đã hài lòng, nhấn nút Finish & Merge trong nhóm để chọn in thư hoặc gửi mail chúng.

Sao lưu văn bản trộn thư. Bạn sao lưu tệp trộn thư như tệp Word thông thường bằng cách nhấn nút hoặc nhấn phím .

Một khi đã sao lưu, tệp sẽ luôn kết nối với danh sách trong Excel. Khi bạn muốn sử dụng văn bản trộn thư lần nữa, mở nó ra và nhấn Yes khi Word yêu cầu bạn tiếp tục kết nối.

Khi trộn thư, bạn cần chú ý giá trị số như ngày tháng, tiền tệ và số. Trong phần này, chúng tôi sẽ hướng dẫn bạn cách định dạng những giá trị như vậy.

Định dạng zip codes và các giá trị khác chứa số 0 trong Excel

Để không làm mất số 0 đứng đầu khi trộn thư, bạn chỉ cần định dạng cột ZIP code thành dạng text trong Excel.

Chọn cột ZIP code, nhấp phải, chọn Format Cells… từ danh sách tùy chọn.

Nếu trang tính Excel chứa ngày tháng, số thập phân, đơn vị tiền tệ, bạn có thể sử dụng Dynamic Data Exchange để đảm bảo rằng các giá trị này được định dạng đúng sau khi trộn thư.

Khi Dynamic Data Exchange được kích hoạt, bạn có thể nhận được một vài yêu cầu, chỉ cần nhấn Yes hoặc Ok. điểm khác biệt duy nhất do với trộn thư thông thường là chọn nguồn dữ liệu, tiến hành theo các bước sau.

Dò tìm trên trang tính, nhấn đúp chuột chọn MS Excel Worksheets via DDE (*.xls) và nhấn OK.

Định dạng ngày tháng, giờ, số và tiền tệ khi trộn thư trong Word

Nếu bạn muốn định dạng số, ngày tháng hay tiền tệ theo nhiều cách khác với tệp nguồn trộn thư Excel, bạn có thể thực hiện trực tiếp trong văn bản Word.

Chọn vùng trộn thư muốn thay đổi định dạng. Chúng có thể là Date, Currency, Percent hoặc các vùng khác.

Nhấn để hiển thị mã vùng. Ví dụ, bạn chọn vùng , bạn sẽ thấy {MERGEFIELD CURRENCY}.

Định dạng số lượng, đơn vị tiền tệ và phần trăm

Chú ý. Thay vì kí hiệu $, bạn có thể thay bằng ký hiệu tiền tệ khác € hoặc £.

Bạn có thể sử dụng d, M, y cho ngày tháng và giờ, nhưng nhớ rằng M đại diện cho tháng còn m đại diện cho phút.

Thay đổi định dạng của ngày tháng và giờ hiện tại:

Nếu bạn thêm vùng DATE và TIME hiển thị thời gian hiện tại trong văn bản trộn thư, bạn có thể thay đổi định dạng của chúng.

Nhấn để hiển thị mã vùng. Nếu bạn chọn vùng , bạn sẽ thấy {DATE @ “M/d/yyyy”}

Nhấp phải vào vùng và chọn Edit Fields từ danh sách tùy chọn.

Alt + F9

Mở tất cả vùng cần điền và kết quả trong văn bản trộn thư

Shift + F9

Hiện ra mã của những chỗ trống

F9

Cập nhật chỗ trống được chọn. đặt con trỏ vào bất cứ ô trống nào và nhấn F9 để cập nhật

F11

Đến chỗ trống cần điền sau

Shift + F11

Đến chỗ trống cần điền trước

Alt + Shift + E

Chỉnh sửa văn bản trộn thư. Chú ý: điều này sẽ ngắt kết nối giữa tệp Excel và văn bản Word, nguồn trộn thư sẽ không tự động cập nhật nữa.

Alt + Shift + F

Chèn vùng trộn thư mới từ nguồn trộn thư

Alt + Shift + M

In văn bản trộn thư

Ctrl + F9

Chèn thêm một chỗ trống

Ctrl + F11

Khóa chỗ trống cần điền. Kết quả sẽ không được cập nhật khi thông tin nguồn của Excel thay đổi.

Ctrl + Shift + F11

Mở khóa chỗ trống cần điền. Kết quả chỗ trống sẽ được cập nhật lần nữa.

Ctrl + Shift + F9

Bỏ liên kết chỗ trống cần điền. Chỗ trống sẽ được xóa vĩnh viễn khỏi văn bản và thay thế bằng giá trị hiện tại hiển thị dưới định dạng bình thường.

Alt + Shift + D

Chèn ngày tháng hiển thị theo ngày tháng hiện tại

Alt + Shift + P

Chèn số trang

Alt + Shift + T

Chèn thời gian hiển thị theo thời gian hiện tại

Alt + Shift + L

Chèn số thứ tự

Mong rằng với bài viết này, sẽ giúp cho các bạn có thể tạo thư tự động từ dữ liệu Excel sang Word – Tính năng trộn thư Mail Merge một cách dễ dàng nhất. Để có thể trau dồi kiến thức từ cơ bản đến nâng cao về Excel các bạn hãy đăng kí tham gia ngay những khóa học word excel cấp tốc với các chuyên gia có nhiều năm kinh nghiệm về Excel sẽ hướng dẫn các bạn một cách tận tình nhất với giá thành cực hấp dẫn.

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel:

Một số hàm cơ bản thường gặp như:

SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện

COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện

Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…

Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

Định dạng theo điều kiện với Conditional formatting

Thiết lập điều kiện nhập dữ liệu với Data Validation

Cách đặt Name và sử dụng Name trong công thức

Lập báo cáo với Pivot Table…

Cập nhật thông tin chi tiết về Tự Động Lọc Dữ Liệu Trước Khi Nhập Từ Sql Server 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!