Ví dụ về tệp excel
|
Thỉnh thoảng chúng ta hay gọi là mở một Workbook, thì Workbook đó là một cách gọi khác của việc mở một tệp Excel. Workbook bao gồm một hoặc nhiều worksheet khác nhau Show
Từ phiên bản 2016 trở về sau này, mặc định Excel chỉ dành cho 02 sheet trong workbook mà thôi Còn các phiên bản trước 2016 thì số lượng của sheet trong workbook là 03 sheet. Nhưng chúng ta hãy cố gắng, vì có phần chỉnh sửa trong Excel Option để chỉnh sửa phần đó theo ý muốn Nội dung
Open 01 workbook đã có sẵnĐể mở một file Excel đã có sẵn, mà trước đó chúng ta đã tạo rồi, thì có những cách sau đây
Sau đây, là hướng dẫn cách mở theo trình tự như trên Open from Menu of file ExcelTại Tab File, chúng ta Click vào File và chọn Open Tại mục Recent will show the list of the file excel, we are open at recent time. Do đó, nó có thể hiển thị nhiều tệp excel. Con số hiển thị bao nhiêu phụ thuộc vào thiết lập của chúng ta trong mục Cài đặt Excel Option Nhờ vậy, chúng ta có thể mở tệp một cách nhanh chóng Hoặc nếu file excel không có sẵn trong danh sách trên. Chúng ta nhấp vào Browse để hiển thị hộp thoại tìm đến tệp mà chúng ta muốn mở, vị trí của Browse xếp thứ 4 sau Recent Tìm đến trực tiếp file ExcelTìm đến vị trí thư mục chứa tệp Excel mà chúng ta muốn mở, nhấn đúp chuột vào tệp excel để mở trực tiếp Đóng tệp Excel đang mởTo close a Excel file we have the way as after
Tạo tệp Excel mớiThỉnh thoảng chúng ta muốn tạo tệp dữ liệu mới để bắt đầu 1 công việc nào đó, hoặc tạo ra để soạn thảo 1 dữ liệu, rồi đóng lại sau. Thì việc tạo một file excel mới là điều rất cần thiết Để tạo một tệp Excel mới, hãy thực hiện theo trình tự các bước sau
Mẹo. Một cách nhanh nhất, là chúng ta nhấn kết hợp 02 phím sau Ctrl+ N (N= Mới) Tại đây, hơi bất tiện là chúng ta phải nhấn vào Sổ làm việc trống một lần nữa, sổ làm việc mới được mở trống. Nhưng không sao, Excel đã cho phép chúng ta tắt màn hình chào này ở tùy chọn Excel Option như sau Tắt màn hình lời chào khi mở Tệp ExcelKhi mình mở chương trình Excel lên, thì Excel sẽ hiển thị danh sách các file excel sử dụng gần đây và các mẫu như Tài chính (Tài chính), Lịch (Lịch) về các chủ đề phổ biến khác nhau Để bỏ qua màn hình này và tiến tới tệp excel, chúng tôi thực hiện tùy chọn như sau
Tùy chọn thiết lập chọn Recent WorkbookCác tập tin excel có bao nhiêu nằm trong danh sách gần đây nhất, phụ thuộc vào việc chúng ta đã thiết lập trong mục Tùy chọn Excel như hình bên dưới Sau khi xác định số lượng tệp excel xuất hiện và nhấp vào OK, quay lại kiểm tra mục Recent Workbooks, bạn sẽ thấy điểm khác biệt Trong công việc hàng ngày, các bạn sẽ gặp trường hợp nhận được rất nhiều file Excel từ nhiều nguồn, hoặc phòng ban khác gửi để cập nhật trong báo cáo, hoặc phải tổng hợp báo cáo tháng theo từng ngày Hỗ trợ, làm sao để cập nhật dữ liệu từ nhiều tệp excel về một tệp excel duy nhất một cách tự động. Bài viết này sẽ chia sẻ giúp các bạn một trong những cách làm đơn giản nhất. Thay vì phải viết code VBA phức tạp, thì chỉ với vài thao tác thiết lập ban đầu với Power Query< . bạn có thể dự động toàn bộ file dữ liệu cho các lần tiếp theo. Trước tiên, chúng ta sẽ cùng tìm hiểu về cách lấy dữ liệu từ file excel khác thông qua VBA xem như thế nào nhé. Cách này khá phức tạp đối với người mới, cần biết mã VBA
Get data from file Excel other information through VBAThông thường, đối với nhiều người dùng Excel lâu năm sẽ nghĩ ngay đến VBA để giải quyết vấn đề ghép nhiều hoặc ghép 2 file excel thành 1. VBA là một ngôn ngữ thiết lập chương trình trên Excel, mục đích của VBA là Tự động hóa các thao tác mà người dùng Excel sử dụng bằng cách viết một đoạn mã Điểm mạnh của mã VBA đó là người dùng chỉ viết 1 lần duy nhất để thiết lập ban đầu, sau đó chỉ cần nhấp vào một nút được thiết lập để chạy – chạy đoạn mã đã thiết lập sẵn đó Tuy nhiên, điểm yếu của VBA đó là người dùng rất khó tiếp cận. Bản chất của VBA là ngôn ngữ lập trình, yêu cầu sự logic và người học vô cùng cần thiết, chuyên sâu mới có thể viết được. Thông thường mất ít nhất 3 tháng để các bạn có thể viết được ngôn ngữ này, nhưng chỉ sau 1 tháng không sử dụng các bạn có thể quên luôn cách viết Bên cạnh đó các tệp cần tổng hợp cần có cấu trúc giống nhau về tên cột, số lượng cột, dòng và vùng dữ liệu. Nếu tập tin có số lượng cột khác nhau thì mã VBA sẽ không hiểu và thông báo lỗi không thực hiện được Phía dưới là đoạn code VBA dành cho bạn nào mong muốn được biết Sub merge_all() Dim cnn As ADODB. Kết nối Làm mờ đầu tiên Là ADODB. Recordset Dim s As Worksheet Dim I As Long, d As Long, CountFiles As Long, J As Long SheetName = “Sheet1” & “$” — Sheet1 là tên sheet của tệp bạn cần tổng hợp RangeAddress = “A1. U1000” — đây là vùng dữ liệu của trang tính mà bạn cần tổng hợp Dim files As Variant files = Application. GetOpenFilename(, , , , True) Nếu VarType(files) = vbBoolean Then Exit Sub Set s = Sheets(“Master”) – tên trang tính này tùy bạn chọn For d = LBound(files) To UBound(files) Set cnn = GetConnXLS . ” & files(d) Thoát Sub End If Đặt rst = cnn. Execute(“SELECT *,””” & files(d) & “”” as [From File] FROM [” & SheetName & RangeAddress & “]”) CountFiles = CountFiles + 1 Nếu CountFiles = 1 Then For J = 0 To . Lĩnh vực. Đếm – 1 s. Ô(3, J + 1). Giá trị = đầu tiên. Trường(J). Tên Tiếp theo J Kết thúc Nếu I = I + s. Phạm vi("A" & 4 + I). CopyFromRecordset(rst) – A4 hiện tại là ô dán dữ liệu vào, chỉnh sửa nếu thay đổi rst. Đóng Đặt đầu tiên = Không có gì cnn. Đóng Đặt cnn = Không có gì Tiếp theo d MsgBox “hoan thanh”End SubKhông dễ dàng để viết đoạn mã phía trên, ghi nhớ cho lần sau đúng không các bạn. Do vậy, trong bài viết này mình sẽ chia sẻ cho các bạn biết thêm cách đơn giản hơn. Bạn sẽ biết cách tổng hợp dữ liệu từ nhiều tệp Excel bằng Power Query. Bạn có thể ghép 2 file excel thành 1 hoặc nhiều hơn một cách dễ dàng Power Query là một công cụ mới được phát triển bởi Microsoft trong khoảng 5 năm gần đây. Tuy nhiên, ở Việt Nam việc làm được nhắc đến, hay sử dụng còn rất hạn chế. Các tài liệu về Power Query chủ yếu là bằng tiếng anh Làm như vậy, một số lượng lớn người sử dụng vẫn chưa đến gần được Giống với VBA, thì Power Query giúp người dùng có thể tự động hóa dữ liệu thông qua việc thiết lập duy nhất 1 lần ban đầu Điểm khác của Power Query đó là người dùng không cần biết viết ngôn ngữ lập trình khó hiểu như VBA, bạn chỉ cần thao tác chọn – thả vô cùng đơn giản, tiết kiệm thời gian, đơn giản dễ học. Đây chính là điểm ưu việt của Power Query so với VBA Với cách làm này, bạn có thể xử lý rất nhiều công cụ giải quyết vấn đề trong công việc mà không cần biết đến mã hóa Microsoft Power Query phát triển trong thời gian gần đây, nhằm giúp nhiều người dùng có thể nâng cao hiệu quả công việc, thay vì lặp lại các thao tác hàng ngày. Thì bạn có thể sử dụng Power Query như một công cụ cứu cánh, tiết kiệm thời gian. Các bạn hãy tham khảo cách chia nhiều file excel thành 1 bằng ứng dụng power query bên dưới nhé Cách tổng hợp dữ liệu từ nhiều file Excel về một sheet Excel duy nhấtTrường hợp đầu tiên mình muốn giới thiệu các bạn cách tổng hợp dữ liệu từ các nguồn khác nhau với mẫu của mỗi tệp là giống nhau. Mục tiêu của bạn đó là tất cả các tệp nhận được sẽ tự động trở thành một trang tổng hợp cho mục đích cá nhân, ví dụ như được sử dụng để tổng hợp danh sách nhân viên theo từng tháng, hoặc báo cáo doanh thu theo từng số First start. tổng hợp, tải xuống toàn bộ tập tin mà các bạn nhận được đã bỏ chung vào 1 thư mục. Đối với cách này, sẽ không phân biệt tên của tệp, quan trọng là các tệp có mẫu chung, tức là có tiêu đề giữa các tệp giống nhau là được Bước 2. Mở 1 file excel mới dùng để tổng hợp toàn bộ các file mà các bạn nhận được và thao tác các lần như sau Dữ liệu -> Nhận dữ liệu -> Từ tệp -> Từ thư mục
Bước 3. Chọn thư mục nguồn dữ liệu mà bạn đã loại bỏ tất cả các tệp cần cập nhật theo các thao tác Duyệt -> Tìm kiếm tên thư mục bỏ qua các tệp đã tải xuống hoặc tệp tổng hợp (ở đây mình ví dụ là thư mục Nguồn dữ liệu) --> Ok --> Ok Bước 4. Sẽ xuất hiện 1 bảng có thể hiển thị tất cả các tệp mà bạn đã bỏ vào thư mục --> chọn Chuyển đổi dữ liệu Sau khi chọn Chuyển đổi dữ liệu, bạn sẽ vào bên trong môi trường của Power Query Tiếp theo đó mình sẽ lấy dữ liệu của tất cả các file vào cùng 1 sheet bằng cách sau Add Column --> Custom Column --> Viết câu lệnh (Excel. Workbook --> Select Content bên cột Available Column , true ) --> OK Bước 5. Sau đó bạn tìm cột có tên là Custom thực hiện các bước như hình vẽ bên dưới để kéo dữ liệu về Bước 6. You find the Custom column. Loại -> Chọn Trang tính -> OK Lý do là vì sao chọn trang tính, vì một số tệp bạn nhận được trong tương lai của các phòng ban không được định dạng bảng. Do vậy, tên sheet gần như không thay đổi theo thời gian. Nên đây là cách để tránh trường hợp dữ liệu không hiểu Bước 7. You find to the Custom column. Data --> Click chuột phải --> Remove Other Columns thì các cột không cần thiết sẽ bị xóa, chỉ để thiết lập lại cột để lấy dữ liệu
Bước 8. Sau đó bạn xổ dữ liệu từ con trỏ ngay tại B1 trong hình vẽ và nhấn OK. Mục đích của bước này để trích xuất toàn bộ dữ liệu từ tất cả các tệp theo các cột cần lấy. Bạn có thể bỏ chọn những cột mà bạn cho là không cần thiết, hoặc có thể loại bỏ cột ở bước tiếp theo Bước 9. Sau đó dữ liệu được lấy ra, bạn cần đưa dòng đầu tiên của dữ liệu vào cột tiêu đề. Bạn thực hiện các bước như trong hình Nhấp con trỏ vào biểu tượng bảng ở góc trái màn hình -> lựa chọn Use First Row as Headers There is all the data data you must be has a total in 1 sheet. Bây giờ mình cần đưa ra giao diện Excel thuần túy Bước 10. Chọn Trang chủ -> Đóng và tải -> Đóng và tải vào -> Bảng -> Bảng tính mới -> OK Sau 10 bước trên, bạn chỉ thiết lập 1 lần duy nhất. Nếu bạn có 1 tệp có mẫu giống như các tháng sau hoặc tuần sau. Bạn chỉ cần bỏ tệp mới vào thư mục và nhấn Dữ liệu --> làm mới tất cả. Toàn bộ dữ liệu của các bạn sẽ được cập nhật một cách tự động và nhanh chóng Cách tổng hợp dữ liệu từ nhiều file Excel thành một file Excel có nhiều sheetTrường hợp thứ 2. Tổng hợp dữ liệu từ các nguồn khác nhau với mẫu mỗi tệp là khác nhau. Mục tiêu Thiết lập công thức giữa các tệp với nhau để ra mục tiêu cụ thể Bước 1. Download file mà các bạn nhận được, bỏ chung vào 1 thư mục Mục đích dễ quản lý. Tệp tải xuống này phải có tên tệp hệ thống nhất để phần mềm có thể nhận diện cho lần tiếp theo Bước 2. Mở tệp mà bạn cần tổng hợp toàn bộ các tệp mà bạn nhận được thành các trang tính khác nhau để thiết lập công thức Dữ liệu -> Nhận dữ liệu -> Từ tệp -> Từ sổ làm việc Bước 3. Chọn tên tệp mà bạn muốn cập nhật hàng ngày. Lưu ý, tên tệp không thay đổi giữa các lần cập nhật. Nếu tệp mới bị bỏ vào thư mục thì tệp cũ phải xóa đi. The name of file must be between so with the first time setting Bước 4. Thực hiện thao tác tải tệp ra trang tính. Lưu ý, các tệp bạn chọn có thể là tệp có định dạng bảng hay không, do đó nên chọn tên trang tính để tải ra. Như hình bên dưới, bảng sẽ có ký hiệu là Table1 và tên sheet sẽ có ký hiệu là DATA Dữ liệu bạn chọn được tải ra trang tính của tệp Excel lặp lại các thao tác trên tương ứng với số lượng tệp cần nhập vào 1 trang tính. Các bước trên chỉ thực hiện 1 lần. Ngày hôm sau, nếu có các tập tin mới. Các bạn chỉ việc tải xuống và bỏ vào thư mục với tên tệp không đổi so với ngày hôm trước, xóa tệp cũ đi. To báo cáo đã được cập nhật Bước 5. Cập nhật các tệp mới nhận được hàng ngày bằng cách Dữ liệu -> Làm mới tất cả -> Làm mới tất cả Do đó là toàn bộ tập tin mà hàng ngày bạn phải sao chép thủ công vào từng trang tính đã được cập nhật tự động. Các thao tác thiết lập ban đầu chỉ làm 1 lần duy nhất. Việc làm này sẽ tiết kiệm rất nhiều thời gian cho các bạn Cách thực hiện công việc trên ứng dụng Power Query trong Excel để tự động hóa dữ liệu. Vì vậy để học Power Query ở đâu? . Vui lòng tham khảo tài liệu khóa học nền tảng của chúng tôi.
Cách tổng hợp dữ liệu từ nhiều tệp Excel vào 1 tệp không cần mở tệpTương tự như cách phía trên, bạn cũng có thể thực hiện các thao tác tương tự để có thể. Thay vì hàng ngày phải thực hiện các thao tác thủ công như mở tệp nhận được -> sao chép -> dán vào từng sheet của 1 tệp để chạy công thức. Thì việc thiết lập như những bước đi phía trên đã giúp bạn tiết kiệm điện rất nhiều thời gian |
