Cập nhật nội dung chi tiết về How To Create A Microsoft Query In Excel (Excel Query) mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Microsoft Query allows you use SQL directly in Microsoft Excel, treating Sheets as tables against which you can run Select statements with JOINs, UNIONs and more. Often Microsoft Query statements will be more efficient than Excel formulas or a VBA Macro. A Microsoft Query (aka MS Query, aka Excel Query) is in fact an SQL SELECT Statement. Excel as well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users!
What can I do with MS Query?
Excel Files – you can extract data from External Excel files as well as run a SELECT query on your current Workbook
Access – you can extract data from Access Database files
MS SQL Server – you can extract data from Microsoft SQL Server Tables
CSV and Text – you can upload CSV or tabular Text files
Step by Step – Microsoft Query in Excel
In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.
Open the MS Query (from Other Sources) wizard
Select the Data Source
Select Excel Source File
Select Columns for your MS Query
Return Query or Edit Query
Return Data to Microsoft Excel – this will return your query results to Excel and complete the Wizard
View data or edit query in Microsoft Query – this will open the Microsoft Query window and allow you to modify you Microsoft Query
Optional: Edit Query
Import Data
AS you can see there are quite a lot of steps needed to achieve something potentially pretty simple. Hence there are a couple of alternatives thanks to the power of VBA Macro….
MS Query – Create with VBA
Sub ExecuteSQL() Attribute ExecuteSQL.VB_ProcData.VB_Invoke_Func = "Sn14" 'AnalystCave.com On Error GoTo ErrorHandl Dim SQL As String, sConn As String, qt As QueryTable SQL = InputBox("Provide your SQL Query", "Run SQL Query") If SQL = vbNullString Then Exit Sub sConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;;Password=;User ID=Admin;Data Source=" & _ chúng tôi & "/" & chúng tôi & ";" & _ "Mode=Share Deny Write;Extended Properties=""Excel 12.0 Xml;HDR=YES"";" Set qt = ActiveCell.Worksheet.QueryTables.Add(Connection:=sConn, Destination:=ActiveCell) With qt .CommandType = xlCmdSql .CommandText = SQL .Name = Int((1000000000 - 1 + 1) * Rnd + 1) .RefreshStyle = xlOverwriteCells .Refresh BackgroundQuery:=False End With Exit Sub ErrorHandl: MsgBox "Error: " & Err.Description: Err.Clear End SubJust create a New VBA Module and paste the code above. You can run it hitting the CTRL+ SHIFT+ S Keyboardshortcut or Add the Macro to your Quick Access Toolbar.
Learning SQL with Excel
Creating MS Queries is one thing, but you need to have a pretty good grasp of the SQL language to be able to use it’s true potential. I recommend using a simple Excel database (like Northwind) and practicing various queries with JOINs.
Alternatives in Excel – Power Query
MS Query vs Power Query Conclusions
, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:
You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query
MS Query Cons: Microsoft Query falls short of the Power Query AddIn in some other aspects however:
Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s
Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations
Lý Do Sử Dụng Microsoft Power Query Cho Excel
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.
Power Query Overview: An Introduction To Excel’S Most Powerful Data Tool
Bottom line: Learn how this awesome feature of Excel and Power BI called Power Query will help you automate the process of importing, transforming, and cleansing your data to save a TON of time with your job.
Skill level: Beginner
Video Tutorial
Download the Sample Files
The CSV files I use in the video are available for download below. You will need to extract the files out of the zip file.
Introduction to Power Query
In this tutorial I provide an introductory explanation of Power Query. You will learn why this is my new favorite Excel tool for working with data, and how it can help automate processes and save you time!
The Power Query Data Machine
I was watching a TV show on how things are made, and they were explaining how a depositor machine worked in a pastry factory.
The basics of a depositor machine are:
You add ingredients to it.
Change some settings.
And it magically creates pastries (cookies, donuts, biscuits) that are ready for baking.
Once the dials are set, the process can be repeated over-and-over again to make perfect pastries every time. Getting hungry…? 🙂
You add your data sources (Excel tables, CSV files, database tables, webpages, etc.)
Press buttons in the Power Query Editor window to transform your data.
Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting.
If you have used macros to transform your data, you can think of this as a much easier alternative to VBA that does NOT require coding.
Common Data Tasks Made Easy
Do you work with data that has been exported from a system of record? This could be a general ledger, accounting, ERP, CRM, chúng tôi or any reporting system that contains data.
If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts.
These data transformations could include tasks like:
Remove columns, rows, blanks
Convert data types – text, numbers, dates
Split or merge columns
Sort & filter columns
Add calculated columns
Aggregate or summarize data
Find & replace text
Unpivot data to use for pivot tables
Do any of these tasks sound familiar? If so, then they probably also sound boring, repetitive, and time consuming. 🙂 Believe me, I’ve spent the better part of my career doing these tasks and trying to figure out faster ways to get them done.
Fortunately, Power Query has buttons that automate all these tasks!
Overview of the Power Query Ribbon
Starting in Excel 2016 for Windows, Power Query has been fully integrated into Excel. It is now on the Data tab of the Ribbon in the Get & Transform group.
In Excel 2010 and 2013 for Windows, Power Query is a free add-in. Once installed, the Power Query tab will be visible in the Excel Ribbon.
You use the buttons in the Data or Power Query tab to get your source data. Again, your data could be stored in Excel files, csv files, Access, SQL server database, SharePoint, chúng tôi Dynamics CRM, Facebook, Wikipedia, websites, and more.
Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data.
The buttons in the Power Query Editor Window allow you to transform your data.
Think about some of those tasks you do repeatedly as you browse the buttons in the image above. Each time you press a button your actions (steps) are recorded, and you can quickly re-apply the steps when you receive new data by refreshing the query.
You can also modify existing queries and refresh your output tables with the changes or updated data.
Data Transformation Examples
Here are a few examples of what Power Query can do with your data.
Unpivot Data for Pivot Tables
My favorite feature of Power Query is it’s ability to Unpivot data. This is a technique used to get your data ready for the source of a pivot table. This is also referred to as normalizing your data to get it in a tabular format.
The data might start out looking something like the following.
And you want the end result to look like this.
Here is an article and video on exactly How to Unpivot Your Data with Power Query.
Checkout my article on how to structure your source data for a pivot table if you are unfamiliar with why your data needs to look like this for a pivot table.
Append (Combine) Tables with Power Query
The Append feature of Power Query allows you to combine multiple tables (stack them vertically) to create one large table. It can do this with multiple tables in one file, or it can pull in data from a bunch of different files/sources.
Let’s say you have a folder that contains CSV or Excel files with report data for each month. Throw all those files in the Power Query machine, and it will spit out one nice table that you can then use to create pivot tables and charts.
If the data in those reports also needs to be transformed (remove rows, split columns, unpivot, etc.), then Power Query can handle that in the same process.
Once it is setup, all you have to do is hit the refresh button every month when a new file is added to the folder and the rows will be added to your output table.
How awesome is that! 🙂
Merge Tables – A VLOOKUP Alternative
Power Query has the ability to merge or join tables. This can be used as an alternative to VLOOKUP or INDEX/MATCH formulas.
Let’s say you have this data table of sales records, and you are using a VLOOKUP to bring in information about the product based on the name of the product sold. Your product group information is located in another table on a different sheet or workbook.
Using VLOOKUP formulas is great, but it can often mean adding thousands of formulas to your workbook. Which increases the file size and calculation time.
Create Custom Functions
However, Power Query can be programmed to create custom functions. This gives you seemingly unlimited potential to transform your data in just about any way possible.
It is based on the M language, and most of the functions are very similar to writing a formula in Excel. This also makes it more user friendly and easier to learn the code.
This new language and set of functions means there is a lot to learn, but I consider that the fun and challenging part. Plus, employers of the future will definitely be looking for employees with Power Query skills.
Power Query Records Your Steps & Automates Processes
Power Query not only makes all these tasks easier, but it also records your steps so you do NOT have to do them over-and-over again. It will save you a lot of time if you are preparing the same data every day, week, or month.
It also does a pretty good job of handling errors. If the structure of your source data changes, Power Query will tell you what step it broke at and allow you to fix it. This makes maintenance easy and you don’t have to completely redo your process when something changes.
You can use Power Query to get your data ready for use in pivot tables, charts, and dashboard reports. This is a critical step in the process of summarizing and analyzing data.
The Power Query Machine & Power BI
Well, it can’t exactly make cookies, but Power Query is a pretty awesome tool! It will save you a ton of time when transforming your data.
Power Query is just one piece of the suite of Power BI (Business Intelligence) products from Microsoft.
If we go back to the analogy of baking cookies in a factory, you can think of Power Query as the first step in the assembly line. Once the cookies are formed, we then need to bake them (Pivot Tables, PowerPivot) and then package them for presentation (Power View, Power Map, Charts, Dashboards, etc.)
How Do I Get Power Query?
The other nice part is that Power Query is now built into Excel starting with Excel 2016 for Windows. If you are on Excel 2010 or 2013 then Power Query is a free add-in.
I have a dedicated page that will help you determine if you have the right version of Excel to get Power Query. It also provides complete installation instructions and the download link.
Complete Guide to Installing Power Query
To give you an idea of the importance of this tool, Power Query was fully integrated into Excel in Excel 2016 for Windows, and is on the Data tab of the Ribbon.
It is also known as Get & Transform, although the term Power Query is most common.
Additional Resources
This article has provided an overview of the basics of Power Query that should help you understand some of the major features. Power Query has a ton of features and there is definitely a lot to learn.
I will be sharing more how-to articles and videos in the coming weeks. Here are a few resources that will help you get started.
How to Unpivot Your Data with Power Query + Video Tutorial
Free Training Webinar on the Power Tools
Right now I’m running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It’s called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
If not, do you think it would be useful for you? Are you doing any of the tasks I mentioned manually right now?
I will be creating more how-to articles and videos on Power Query in the future, so I’m interested to know what you want to learn.
Thank you! 🙂
How To Create Pivot Chart In Excel?
How to create pivot chart in Excel?
In Excel, you may usually insert a chart to help you analyze the data, but do you know in some cases, a pivot chart is better than the normal chart for analyzing in Excel? Now this tutorial is talking about pivot chart creating in Excel.
Create pivot chart in Excel
Create pivot chart in Excel
To create a pivot chart, you can do as the following steps:
2. In the popping Create PivotTable with PivotChart dialog, choose the location you want to place the new PivotTable and PivotChart in Choose where you want the PivotTable and PivotChart to be placed section. See screenshot:
4. Now drag the fields you want to show in the PivotChart to the areas as you need. Here I drag Saler and Order ID fields to Axis Fields (Categories) section, and drag Amount field to Values section. See screenshots:
Note: When you create a pivot chart, a pivot table is created at the same time.
Tip:
If you want the legend series shown as Saler, you can drag Saler field to the Legend Fields (Series) section. See screenshots:
Notes:
(1) The pivot table will change as the pivot chart changes.
(2) In Excel 2007, we cannot filter data in the Pivot chart, but we can filter data in the Pivot table and the data in the pivot chart will change automatically.
Relative Articles:
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse:
Quickly insert
complex formulas, charts
and anything that you have used before;
Encrypt Cells
with password;
Create Mailing List
and send emails…
Super Formula Bar
(easily edit multiple lines of text and formula);
Reading Layout
(easily read and edit large numbers of cells);
Paste to Filtered Range
…
Merge Cells/Rows/Columns
without losing Data; Split Cells Content;
Combine Duplicate Rows/Columns
… Prevent Duplicate Cells;
Compare Ranges
…
Select Duplicate or Unique
Rows;
Select Blank Rows
(all cells are empty);
Super Find and Fuzzy Find
in Many Workbooks; Random Select…
Exact Copy
Multiple Cells without changing formula reference;
Auto Create References
to Multiple Sheets;
Insert Bullets
, Check Boxes and more…
Extract Text
, Add Text, Remove by Position,
Remove Space
; Create and Print Paging Subtotals;
Convert Between Cells Content and Comments
…
Super Filter
(save and apply filter schemes to other sheets);
Advanced Sort
by month/week/day, frequency and more;
Special Filter
by bold, italic…
Combine Workbooks and WorkSheets
; Merge Tables based on key columns;
Split Data into Multiple Sheets
;
Batch Convert xls, xlsx and PDF
…
More than 300 powerful features
. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More… Free Download… Purchase…
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Enable tabbed editing and reading in Word, Excel, PowerPoint
, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by
Read More… Free Download… Purchase…
Bạn đang đọc nội dung bài viết How To Create A Microsoft Query In Excel (Excel Query) trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!