Top 10 # Sử Dụng Query Trong Excel Xem Nhiều Nhất, Mới Nhất 3/2023 # Top Trend

Microsoft Excel là phần mềm bảng tính mạnh mẽ và phổ biến giúp làm việc với dữ liệu đơn giản. Microsoft đã phát triển một số cách khá sáng tạo để làm việc với dữ liệu.

Nếu đang tìm kiếm một cách mới mạnh mẽ để quản lý dữ liệu, Microsoft Excel cho Windows hiện có phần mềm được tích hợp để trợ giúp đó là Microsoft Power Query. Đây là một công cụ mới giúp bạn kiểm soát bảng tính của mình nhiều hơn bao giờ hết.

Microsoft Power Query là gì?

Power Query (được biết đến trong Microsoft Excel 2016 với tên gọi Get & Transform) là một công cụ chuyển đổi dữ liệu được tích hợp ngay trong Microsoft Excel. Nó cho phép nhập dữ liệu từ nhiều nguồn khác nhau, thực hiện thay đổi và tải dữ liệu ngay vào workbook Excel.

Khi nhập vào Power Query, nó sẽ tạo kết nối đến dữ liệu. Sử dụng kết nối này, bạn có thể làm việc với dữ liệu trong trình chỉnh sửa và thực hiện các thay đổi trước khi lưu.

Power Query sẽ lưu thay đổi dưới dạng các bước trong một file được gọi là Query để có thể sử dụng bất cứ khi nào. Bạn có thể nghĩ nó như macro trong lập trình VBA.

Nếu thường xuyên làm việc với dữ liệu, đây là lý do tuyệt vời để sử dụng Power Query.

1. Microsoft Power Query được tích hợp trong Excel

Power Query rất đơn giản. Nếu sử dụng Excel 2016 trở lên trên máy tính, người dùng đã có Power Query. Bạn không cần phải tải bất cứ phần mềm bổ sung hoặc có kiến thức lập trình để sử dụng công cụ này.

Lưu ý: Đối với lập trình viên, Microsoft phát triển một ngôn ngữ mới cho Power Query được gọi là M. Người dùng nâng cao có thể viết code script sử dụng ngôn ngữ M. Đây là một khái niệm cao cấp hơn nhưng cung cấp thêm sức mạnh và giá trị cho người dùng chuyên nghiệp.

2. Dễ sử dụng trình chỉnh sửa

Power Query cung cấp giao diện đơn giản để làm việc với dữ liệu. Khi tải dữ liệu bạn có thể thực hiện thay đổi bên trong trình chỉnh sửa, không yêu cầu lập trình.

Nếu đã quen thuộc với Excel, việc sử dụng trình chỉnh sửa dễ dàng hơn nhiều vì nó trông rất giống với cửa sổ trong Excel.

Nếu cần chỉnh sửa dữ liệu, bạn có thể thực hiện ngay trong trình chỉnh sửa. Một số ví dụ bạn có thể thực hiện với dữ liệu:

Thêm hoặc xóa cột trong bảng

Lọc dữ liệu theo cột

Kết hợp hai bảng với nhau

Sắp xếp dữ liệu bằng cách sử dụng bộ lọc Excel

Đổi tên cột

Xóa lỗi từ bảng

Khi thực hiện xong thay đổi, chỉ cần xuất kết quả ngay vào workbook Excel. Thật dễ dàng để làm việc với nhiều trang tính mà không cần mở từng trang và thực hiện thay đổi.

3. Thực hiện các công việc lặp lại một cách đơn giản

Một việc phổ biến khi làm việc với workbook Excel là phải thực hiện cùng một số thay đổi cho các bảng tính khác nhau.

Giả sử bạn có báo cáo bán hàng tháng 7 và cần thực hiện một số thay đổi. Mở bảng tính, thay đổi và lưu. Sau một tháng, bạn lại có báo cáo tháng 8 và cũng cần thực hiện thay đổi tương tự.

Với Excel, người dùng cần mở workbook và thực hiện tất cả các thay đổi để có kết quả tương tự. Tùy thuộc vào số lượng thay đổi, quá trình này có thể mất nhiều thời gian của bạn. Tuy nhiên Power Query có thể giải quyết vấn đề này.

Khi kết nối workbook sử dụng Power Query và thực hiện thay đổi, những thay đổi này được lưu thành các bước. Các bước chỉ là hướng dẫn bạn cung cấp cho Excel để thay đổi dữ liệu (ví dụ, xóa cột này hoặc sắp xếp bảng này theo màu sắc).

4. Không cần kiến thức lập trình

Excel có thể lập trình bằng Visual Basic for Application (VBA), nhưng có nhiều thứ cần phải học. Bạn cũng có thể sử dụng các ngôn ngữ lập trình khác với Excel nhưng đòi hỏi nhiều thiết lập.

5. Thu thập dữ liệu từ các nguồn khác nhau

Power Query có thể lấy dữ liệu từ nhiều nơi khác nhau. Nếu muốn làm việc với dữ liệu từ trang web, bạn có thể nhập nó dễ dàng. Power Query cũng có thể lấy dữ liệu từ nhiều nguồn khác nhau, chẳng hạn như:

Có nhiều nguồn khác bạn có thể sử dụng, nhưng đừng làm dụng.

Tại sao không chỉ sử dụng Microsoft Excel?

Excel khá mạnh mẽ, không có nghi ngờ gì về nó. Nhưng nếu sử dụng nhiều trang tính khác nhau, bạn sẽ có nhiều công việc cần quản lý.

Power Query không phải là bảng tính mà là bảng điều khiển để bạn có thể làm việc với dữ liệu. Bố cục tương tự như Excel để giúp bạn làm việc dễ dàng hơn. Bạn có thể làm việc rất hiệu quả bằng cách kết hợp các điểm mạnh của Excel và Power Query.

Có rất nhiều lý do tuyệt vời để bắt đầu sử dụng Power Query quản lý bảng tính. Nếu nắm vững những điều cơ bản của chương trình này, bạn có thể tạo các bảng và làm việc với dữ liệu dễ dàng hơn nhiều.

Ta có thể sử dụng được Name động trong Excel, vậy còn trong Power Query thì sao? Trong bài viết này Học Excel Online sẽ hướng dẫn cách để sử dụng được Name động trong Power Query.

Tạo Name động

Để tạo Name động trong Excel ta có một số hàm thường dùng như OFFSET, nếu bạn đang dùng các phiên bản Excel mới hơn như Excel 2019, 365 thì sẽ có khá nhiều cách nữa để tạo được Name động.

Để cho các phiên bản Excel khác đều có thể thực hiện thì trong bài viết này mình sẽ sử dụng hàm OFFSET để tạo name động.

Cụ thể, mình có 1 bảng dữ liệu như sau, ta sẽ dùng hàm OFFSET để tạo 1 Name động và Name này chính là toàn bộ dữ liệu của chúng ta

Hướng dẫn cách tạo danh sách động trong excel với hàm OFFSET

Bạn vào Define Name, tại hộp thoại New Name bạn làm như sau:

Refers to: Bạn gõ vào công thức tạo Name động với hàm OFFSET như sau:

Sau khi đã tạo được Name động với hàm OFFSET như trên, bạn vào Power Query, trong Get Data bạn chọn Launch Power Query Editor…. Lúc này cửa sổ tạo Power Query Editor sẽ hiện ra.

Bạn tạo 1 Blank Query , bạn có thể làm điều điều này trong Tab Home, nhóm New Query hoặc bạn chuột phải vào phần bên trái của cửa sổ Query Editor, cụ thể như sau:

= Excel.CurrentWorkbook(){[Name=”DATA”]}[Content]

Nếu như chưa hiểu về M – Code thì bạn chỉ cần quan tâm tới phần {[Name= “DATA”]}

Bạn gõ tên Name động của bạn vào phần này sau đó ấn Enter bạn sẽ có kết quả như sau:

Một vài lưu ý

Bạn có thể thấy chúng ta hoàn toàn có thể sử dụng được Name động trong Power Query. Tuy nhiên. Học Excel Online nghĩ bạn không nên quá lạm dụng việc này trong trường hợp không cần thiết, thay vào đó bạn nên tạo Table cho dữ liệu để sử dụng Power Query. Định dạng Table được khuyến khích sử dụng và cho hiệu suất tốt nhất so với Name Range thông thường và Name động này.

Hy vọng qua bài viết này của Học Excel Online bạn đã nắm được cách để có thể sử dụng được Name động trong Power Query.

Power Query là một công cụ rất mạnh được Microsoft hỗ trợ cho khá nhiều phiên bản Excel được sử dụng phổ biến hiện nay, việc nắm được Power Query trong Excel nó sẽ là cầu nối giúp cho bạn tiến tới 1 công cụ rất mạnh trong việc làm các báo cáo phân tích – Power BI

Hướng dẫn cách tạo danh sách động trong excel với hàm OFFSET Hàm OFFSET và các kết hợp hàm của nó trong excel, công thức ví dụ Hướng dẫn sử dụng Power Query để chuẩn hoá dữ liệu Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng power query Hàm DAX trong Power BI PBI101 – Microsoft Power BI Desktop, Business Intelligence trong tầm tay

Trong bài viết này, Học Excel Online sẽ hướng dẫn cách chuẩn hoá dữ liệu, nói cách khác là đưa dữ liệu về dạng “Chuẩn” với công cụ Power Query trong Excel.

Chuẩn bị dữ liệu tốt có lẽ là yếu tố thành công quan trọng nhất trong phân tích dữ liệu. Nếu bố cục dữ liệu không đúng, bạn sẽ tiếp tục chiến đấu với Excel; phải sử dụng các công thức Excel nâng cao, rất phức tạp; thêm các cột không cần thiết hoặc thậm chí sao chép dữ liệu,….

Ở đây, mình có bảng dữ liệu như sau:

Vậy làm như thế nào để ta có thể thiết kế lại dữ liệu như đã mô tả trên?

Bạn sẽ làm điều đó trong Excel như thế nào?. Bạn sẽ Copy và Paste một cách thủ công và rất dễ sai sót, nhầm lẫn,…và mỗi lần dữ liệu gốc thay đổi bạn lại Copy lại???. Hay là bạn sẽ xử lý nó với kỹ năng VBA mà mình có???

KHÔNG, Không nên một chút nào nếu như bạn đã có công cụ Power Query thì hãy để nó xử lý việc đó cho bạn một cách rất nhanh chóng và hiệu quả.

Đầu tiên bạn nên đưa dữ liệu trên về dạng Table (Ctrl + T or Ctrl + L) và đặt tên cho Table nó 1 cái tên (ở đây mình đã đặt tên là VD_1). Power Query hỗ trợ cho bạn khá nhiều kiểu để Input dữ liệu vào như Table, Name range (Define name), Name động hay thậm chí bạn chỉ cần quét chọn 1 vùng thì Power Query sẽ nhận vùng đó để đưa vào Power Query Editor. Tuy nhiên, với dữ liệu được lấy từ Excel thì tốt nhất là bạn nên tạo Table cho nó.

Tiếp theo, bạn chỉ cần chọn 1 ô bất kỳ trong bảng đó vào chọn From Table/Range

Source: Đây là nguồn dữ liệu mà bạn Input vào Power Query (trong bài viết này là từ Excel)

Changed Type: Đây là bước mà Power Query tự nhận kiểu định dạng cho mỗi trường thông tin (cột) khi nào Input dữ liệu vào. Nếu bạn không muốn Power Query Editor tự động thay đổi định dạng như vậy thì bạn có thể bỏ bước này bằng cách ấn vào dấu X phía trước bước đó. Mình sẽ làm điều đó.

Quay lại với mục đích ban đầu là đưa dữ liệu về dạng “Chuẩn”. Tại cửa sổ Power Query Editor bạn vào Tab Transform và để ý tới mục Unpivot Columns (ta sẽ sử dụng nó xuyên suốt bài viết này).

Cách 1: Sử dụng Unpivot Columns hoặc Unpivot Only Selected Columns

Với cách này, bạn chọn các cột Quý 1, Quý 2, Quý 3, Quý 4. Sau đó thực hiện Unpivot Columns hoặc Unpivot Only Selected Columns.

Giờ bạn chỉ cần quay lại Tab Home chọn Close & Load vào Excel thôi là bạn đã có thể có được 1 bảng dữ liệu đúng “Chuẩn” rồi. Ở đây, mình chọc Close & Load To…. và chọn cách xem dữ liệu trả về là Table và đặt tại ô I3 trong Sheet VD_1.

Với cách này. Thay vì lựa chọn 4 cột Quý 1, Quý 2, Quý 3, Quý 4 như cách 1 thì bạn sẽ lựa chọn các cột còn lại trong bảng đó là cột STT và cột Hàng hoá. Vào Transform và lựa chọn Unpivot Other Columns

Sự khác biệt ở đây là: Nếu bạn làm theo cách 1 thì giả sử khi bạn có thêm 1 hoặc nhiều cột nữa vào dữ liệu gốc (VD: thêm cột Quý 5, Quý 6,….) thì khi bạn cập nhật, Power Query sẽ không tự hiểu để Unpivot cho những Columns quý mà bạn thêm mới đó.

Còn với cách làm thứ 2 lựa chọn Unpivot Other Columns sẽ khắc phục được điều đó. Tuy nhiên, bạn cũng cần hiểu rõ bản chất hơn nữa để lựa chọn cách Unpivot cho phù hợp.

Ở ví dụ này, bảng dữ liệu gốc lại được cấu trúc như sau:

Đầu tiền, bạn cần tạo 1 Table và đặt cho nó 1 cái tên (Mình đã đặt VD_2 là tên của Table này). Cũng với các bước như ví dụ 1, ta thực hiện Input dữ liệu vào Power Query Editor. Vẫn với Unpivot, và vấn đề là ta sẽ Unpivot như nào để dữ liệu này ra được dạng “Chuẩn” ?.

Bước 1: Thực hiện Unpivot

Chọn Unpivot Other Columns, sau bước này ta được kết quả như sau:

Ta thấy, những ký tự bên phải dấu _ nó chỉnh là tháng. Ta sẽ thực hiện tách cột Attribute thành 2 cột, bằng tính năng Split Columns với delimiter là dấu _

Trong trường hợp delimiter không có thì bạn có thể tạo ra bằng cách sửa tên cột cho phù hợp, để đáp ứng được cho việc xử lý dữ liệu.

Kết quả sau khi thực hiện bước này bạn sẽ được như sau, lúc cột Attribute.2 nó chính là cột Tháng mà ta cần.

Cột Attribute.1 đang chứa cả DT (doanh thu) và CP (chi phí), đúng ra thì nó phải là 2 cột phải không, 1 cột là DT và 1 cột là CP.

Bạn chọn cột Attribute.1 và cột Value để thực hiện Pivot. Kết quả sau bước này bạn sẽ có bảng dữ liệu đúng “Chuẩn” như hình

Cũng với dữ liệu giống như các ví dụ trước, nhưng lần này tiêu đề của bạn lại có Merger (một điều mà bạn nên hạn chế sử dụng).

Với trường hợp tiêu đề của bảng có Merger thì ta có một số hướng xử lý như sau:

Cách 1: Xử lý tiêu đề trên Excel luôn

Với cách này, bạn sẽ bỏ Merger ngay trong Excel luôn và đổi tên lại các tiêu đề đó phù hợp, để có thể đưa về được dạng như Ví dụ 2 mà Học Excel Online đã giới thiệu ở trên.

Cách 2: Xử lý bên trong Power Query Editor

Đầu tiên thì cũng như các Ví dụ trước là bạn tạo cho nó 1 Table và đặt tên (mình đã đặt tên ở đây là VD_3).

Lưu ý: Trường hợp này ta sẽ bỏ chọn phần “My table has headers” trong hộp thoại tạo Table.

Thực hiện Input dữ liệu vào Power Query và ta cũng sẽ cần một số bước để biến đổi dữ liệu này về dạng “Chuẩn”, cụ thể như sau:

Sau khi Input dữ liệu vào Power Query ta sẽ trông nó như này đây, những ô không có dữ liệu gì sẽ hiện n ull.

Ở bước này, ta sẽ đổi tên cho các cột này theo một quy tắc nhất định để có thể vận dụng được các bước trong Ví dụ 2, cụ thể như sau:

Bước 2: Thực hiện xoá dòng

Tiếp theo ta sẽ xoá 2 dòng đầu tiên trong dữ liệu bằng chức năng Remove Rows trong Tab Home.

Qua 3 ví dụ xử lý dữ liệu trên, ta có một vài lưu ý như sau:

– Các thao tác ta thực hiện trong Power Query Editor sẽ được M Code ghi lại (M Code là ngôn ngữ mà Power Query sử dụng để ghi lại các bước được áp dụng) gần giống như Code VBA trong Excel. Điều này rất thuận khi chúng ta cập nhật thêm, bớt, chỉnh sửa,…. dữ liệu ở DATA gốc thì bạn chỉ cần Refresh. Ngay lập tức những quy trình bạn thực hiện trong Power Query Editor sẽ thực thi và cập nhật kết quả cho bạn 1 cách nhanh chóng, chính xác.

– Trong bài viết này, ở Bước 1 và Bước 2 trong Vi dụ 3 này vừa rồi ta phải làm thủ công, điều này có thể sẽ có một số vấn đề như khi bạn thêm các trường (cột) dữ liệu khác vào DATA gốc thì việc đổi tên cột đó Power Query Editor sẽ không hiểu để đổi cho bạn thành tên gì. Do đó, tốt hơn hết là bạn nên tổ chức dữ liệu 1 cách hiệu quả hơn.

– Từ đây bạn có thể rút ra: Các thao tác mà ta thực hiện trong Power Query Editor cần có tính chung, thống nhất, ta phải tạo ra 1 quy trình chung trong xử lý dữ liệu để mỗi lần cập nhật dữ liệu gốc thì quy trình này vẫn hoạt động đúng và trả về kết quả chính xác cho chúng ta.

Học Excel Online hy vọng tới đây bạn đã nắm được cách xử lý dữ liệu, chuyển đổi dữ liệu về dạng “Chuẩn” với Power Query trong Excel. Power Query là một công cụ mạnh được Excel hỗ trợ giúp cho bạn xử lý dữ liệu, biến đổi dữ liệu một cách khá đơn giản, hiệu quả và hiệu suất cao. Với Power Query bây giờ việc chuẩn hoá dữ liệu, gộp file, gộp các Sheets trong Excel trở nên đơn giản hơn bao giờ hết

Power Query là một công cụ rất mạnh. Tuy nhiên, còn khá mới mẻ với chúng ta. Việc nắm được cách sử dụng công cụ này sẽ là cầu nối giữa Excel tới Power BI – Một công cụ để làm báo cáo tuyệt vời.

Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng power query PBI101 – Microsoft Power BI Desktop, Business Intelligence trong tầm tay

Việc tổ chức dữ liệu trên Excel phụ thuộc rất nhiều yếu tố: đặc thù của nguồn nhập dữ liệu, đặc thù của dạng dữ liệu… nên không phải lúc nào chúng ta cũng có được 1 bảng dữ liệu đúng ý muốn ngay từ đầu. Do đó để có thể phục vụ việc lập báo cáo trong Excel được tốt hơn thì chúng ta cần phải tổ chức lại dữ liệu. Một trong những công việc thường gặp đó là gộp dữ liệu từ nhiều bảng tính (có thể trên nhiều sheet) vào 1 bảng tính (trên 1 sheet). Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng Power Query.

Mục tiêu của chúng ta như sau:

1. Chuẩn bị dữ liệu

Các bạn có thể tải về file dữ liệu gốc để thực hành theo bài viết tại địa chỉ: http://bit.ly/2JBYRh1

Power Query là 1 công cụ mới có trên Office từ các phiên bản Office 2016, Office 365 (đã tích hợp sẵn). Nếu bạn sử dụng Office phiên bản thấp hơn như 2010, 2013 thì bạn có thể tải Add-in Power Query trên hệ thống của Microsoft Office

Link tải Power Query: https://www.microsoft.com/vi-vn/download/details.aspx?id=39379

2. Nguyên tắc của việc gộp dữ liệu

Để tránh các lỗi có thể xảy ra trong quá trình gộp dữ liệu thì chúng ta phải tuân thủ một số nguyên tắc sau:

Dữ liệu từ các bảng tính sẽ được nối tiếp nhau trong 1 bảng chung

Các bảng tính phải có cấu trúc giống nhau: số cột, tên các cột… để đảm bảo nguyên tắc nối tiếp nhau

Các bảng tính phải bao gồm dòng tiêu đề và hạn chế dòng trống xen kẽ dữ liệu (đảm bảo tính liên tục của dữ liệu)

3. Cách thực hiện gộp dữ liệu bằng Power Query

a. Chuyển các bảng cần gộp về dạng Table Data

Tại mỗi bảng dữ liệu thành phần chúng ta sẽ chuyển về dạng Table data bằng cách:

Chọn bảng dữ liệu đó

Sử dụng phím tắt Ctrl+T (phím tắt của chức năng Insert/Table)

Xác nhận việc chuyển đổi này

Table1 – Cửa hàng A

Table2 – Cửa hàng B

Table3 – Cửa hàng C

Tại sao lại cần chuyển về dạng Table? Bởi trong Power Query việc kết nối dữ liệu từ các Table sẽ dễ hơn, nhanh hơn. Sau khi kết nối các bảng thì chúng ta có thể trả dạng Table về dạng Range một cách dễ dàng, không bị ảnh hưởng gì.

b. Sử dụng Power Query kết nối các bảng Table

Cửa sổ Power Query sẽ mở ra. Trong cửa sổ này chúng ta chọn mục Append Queries ngay trong thẻ Home

Let / Source: là các bảng dữ liệu nguồn. Ở đây chú ý Name'”Table1″ chính là tên của các Table đã được tạo ở trên. Chúng ta sẽ thay đổi Source này thành 3 Source ứng với 3 Table

Let/ Appended: là lệnh nối các bảng dữ liệu. Đặt các Source vào trong câu lệnh = Table.combine({…})

in / Appended: trích xuất kết quả của Appended

Các lệnh khác có thể bỏ qua (ví dụ như changed type)

Do đó chúng ta sẽ sửa lại câu lệnh trên thành:

c. Xuất dữ liệu từ Power Query về 1 Sheet kết quả

Để trích xuất dữ liệu từ Power Query về Sheet thì chúng ta làm như sau:

Trong cửa sổ Close & Load to chọn:

Select how you want to view this data in your workbook: Chọn dạng dữ liệu muốn hiển thị. Thông thường có thể chọn Table

Where do you want to put the data? Chọn nơi bạn muốn đặt bảng dữ liệu kết quả này. Nếu đã có vị trí tại 1 sheet xác định thì chọn Existing worksheet, còn nếu muốn đưa vào 1 sheet mới thì chọn New worksheet.

Để chuyển từ dạng Table về dạng bảng thường (Range) thì chúng ta bấm vào mục Convert to range trong thẻ Table Tools