Có nhiều cách tốt để thực hiện điều này, mà những người khác đã đề xuất. Theo dõi "lấy dữ liệu Excel qua theo dõi SQL", đây là một số gợi ý
Excel có "Trình hướng dẫn kết nối dữ liệu" cho phép bạn nhập hoặc liên kết từ một nguồn dữ liệu khác hoặc thậm chí trong cùng một tệp Excel
Là một phần của Microsoft Office [và HĐH] là hai nhà cung cấp được quan tâm. "Microsoft" cũ. máy bay phản lực. OLEDB" và "Microsoft" mới nhất. ÁT CHỦ. OLEDB". Tìm chúng khi thiết lập kết nối [chẳng hạn như với Trình hướng dẫn kết nối dữ liệu]
Sau khi được kết nối với sổ làm việc Excel, một trang tính hoặc phạm vi tương đương với một bảng hoặc dạng xem. Tên bảng của trang tính là tên của trang tính có ký hiệu đô la ["$"] được nối vào nó và được bao quanh bởi dấu ngoặc vuông ["[" và "]"]; . Để chỉ định một phạm vi ô chưa được đặt tên làm nguồn bản ghi của bạn, hãy thêm ký hiệu hàng/cột Excel tiêu chuẩn vào cuối tên trang tính trong dấu ngoặc vuông
SQL gốc sẽ [ít nhiều là] SQL của Microsoft Access. [Trước đây, nó được gọi là JET SQL; tuy nhiên, Access SQL đã phát triển và tôi tin rằng JET là công nghệ cũ không được chấp nhận. ]
Ví dụ, đọc một trang tính.
SELECT * FROM [Sheet1$]Ví dụ, đọc một phạm vi.
SELECT * FROM MyRangeVí dụ, đọc một phạm vi ô chưa được đặt tên.
SELECT * FROM [Sheet1$A1:B10]Có rất nhiều rất nhiều sách và trang web có sẵn để giúp bạn tìm hiểu các chi tiết cụ thể
Ghi chú thêm
Theo mặc định, giả định rằng hàng đầu tiên của nguồn dữ liệu Excel của bạn chứa các tiêu đề cột có thể được sử dụng làm tên trường. Nếu không đúng như vậy, bạn phải tắt cài đặt này, nếu không hàng dữ liệu đầu tiên của bạn sẽ "biến mất" để được sử dụng làm tên trường. Điều này được thực hiện bằng cách thêm tùy chọn HDR= setting vào Thuộc tính mở rộng của chuỗi kết nối. Giá trị mặc định, không cần chỉ định, là HDR=Yes. Nếu bạn không có tiêu đề cột, bạn cần chỉ định HDR=No;
Thận trọng về việc chỉ định trang tính. Nhà cung cấp giả định rằng bảng dữ liệu của bạn bắt đầu bằng ô trên cùng, ngoài cùng bên trái, không trống trên trang tính đã chỉ định. Nói cách khác, bảng dữ liệu của bạn có thể bắt đầu ở Hàng 3, Cột C mà không gặp vấn đề gì. Tuy nhiên, bạn không thể nhập tiêu đề trang tính ở trên và bên trái dữ liệu trong ô A1 chẳng hạn
Thận trọng về việc chỉ định phạm vi. Khi bạn chỉ định một trang tính làm nguồn bản ghi của mình, nhà cung cấp sẽ thêm các bản ghi mới bên dưới các bản ghi hiện có trong trang tính khi không gian cho phép. Khi bạn chỉ định một phạm vi [có tên hoặc không có tên], Jet cũng thêm các bản ghi mới bên dưới các bản ghi hiện có trong phạm vi khi không gian cho phép. Tuy nhiên, nếu bạn truy vấn trên phạm vi ban đầu, tập bản ghi kết quả không bao gồm các bản ghi mới được thêm vào bên ngoài phạm vi
Kiểu dữ liệu [đáng thử] cho CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal
Đang kết nối với Excel "công nghệ cũ" [các tệp có phần mở rộng xls]. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0;. Sử dụng Excel5. 0 loại cơ sở dữ liệu nguồn cho Microsoft Excel 5. 0 và 7. 0 [95] sổ làm việc và sử dụng Excel 8. 0 loại cơ sở dữ liệu nguồn cho Microsoft Excel 8. 0 [97], 9. 0 [2000] và 10. 0 [2002] sách bài tập
Đang kết nối với Excel "mới nhất" [các tệp có phần mở rộng tệp xlsx]. ________số 8
Xử lý dữ liệu dưới dạng văn bản. Cài đặt IMEX coi tất cả dữ liệu là văn bản. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
[Thông tin chi tiết tại http. //www. dây kết nối. com/excel]
Thông tin thêm tại http. // msdn. Microsoft. com/en-US/library/ms141683[v=sql. 90]. aspx và tại http. //hỗ trợ. Microsoft. com/kb/316934
Bạn có thể thực hiện nhiều thao tác mạnh mẽ với Excel, chẳng hạn như kết nối với các nguồn dữ liệu khác. Trong bài viết này, chúng ta sẽ xem xét cách chúng ta có thể sử dụng SQL trong Excel
SQL là gì?
SQL là viết tắt của “Ngôn ngữ truy vấn có cấu trúc. ” Microsoft SQL Server chỉ là một trong nhiều cơ sở dữ liệu sử dụng nó. Trong một cơ sở dữ liệu
- bạn lưu trữ dữ liệu trong bảng
- bạn có thể chạy truy vấn SQL để truy xuất dữ liệu
Ưu điểm của việc sử dụng cơ sở dữ liệu như Microsoft SQL Server để lưu trữ dữ liệu của bạn bao gồm
- Dữ liệu được nhập mạnh, nghĩa là bạn không thể lưu số trong trường ngày. Điều này làm cho dữ liệu của bạn được xác thực ngay lập tức
- Nó có thể là kho lưu trữ dữ liệu trung tâm cho dữ liệu của bạn trên nhiều dự án
- Nhiều người có thể truy cập cùng một dữ liệu cùng một lúc. Điều này làm giảm sự trùng lặp và không nhất quán
- Nó cũng được bảo vệ tốt với bảo mật tích hợp trong Hệ thống quản lý cơ sở dữ liệu quan hệ. Microsoft SQL Server cung cấp một số lớp bảo mật
Microsoft Excel. Bảng Pivot, Power Query, Power Pivot/DAX
Cập nhật lần cuối vào tháng 10 năm 2022
- 226 bài giảng
- Tất cả các cấp
4. 6 [586]
Microsoft Excel Pivot Tables, Get và Transform [Power Query] và Power Pivot [DAX] để phân tích dữ liệu nâng cao. 70-779. . Tác giả Phillip Burton
Khám phá khóa họcĐể biết thêm thông tin, vui lòng xem bài viết Udemy của tôi “Excel vs SQL Server. ”
Làm cách nào để bạn truy cập Máy chủ SQL? . Nếu bạn đang sử dụng Máy chủ SQL hoạt động, thì bộ phận CNTT của bạn sẽ cung cấp thông tin chi tiết về máy chủ của bạn. Điều này sẽ bao gồm
- Tên máy chủ. Nó cũng có thể lấy cái này từ Chuỗi kết nối nếu bạn có nó
- Phương pháp xác thực. Bạn sẽ sử dụng một trong hai
- Xác thực Windows, sử dụng tên người dùng và mật khẩu Windows của bạn
- Xác thực máy chủ SQL, sử dụng tên người dùng và mật khẩu riêng
Nếu bạn có Microsoft SQL Server trên máy tính của mình thì tên máy chủ có thể là “localhost” hoặc “. ”, và có thể bạn sẽ sử dụng Windows Authentication
Bạn có thể sử dụng kết nối này để truy xuất dữ liệu Microsoft SQL Server
Có ba vị trí khác nhau trong Excel nơi bạn có thể tải dữ liệu SQL
- Trong cửa sổ Excel chính
- Trong cửa sổ Nhận và Chuyển đổi [còn được gọi là trình soạn thảo Power Query]
- Trong cửa sổ Power Pivot [còn được gọi là Mô hình Dữ liệu]
Chúng ta sẽ xem xét từng địa điểm này
Kết nối SQL với cửa sổ Excel chính
Cửa sổ Excel chính là cửa sổ bạn sử dụng mỗi khi mở Excel. Để tải dữ liệu từ SQL Server, vào Data – Get Data – From Database – From SQL Server Database. Điều này đã thay thế các phương pháp được sử dụng trước đó như Microsoft Query
Sau đó, bạn sẽ phải cung cấp Tên máy chủ
Có bốn nguồn dữ liệu SQL Server mà bạn có thể truy vấn để trả về kết quả
- Bạn có thể muốn dữ liệu từ một bảng. Đây là dữ liệu thô
- Bạn có thể muốn kết quả truy vấn từ chế độ xem đã tạo trước đó. Kết quả này từ phân tích dữ liệu SQL Server
- Bạn có thể muốn kết quả từ một thủ tục được lưu trữ. Đây có thể là một phân tích phức tạp hơn hoặc một phân tích liên quan đến các tham số. Ví dụ: bạn có thể chỉ muốn tất cả doanh số bán hàng từ bang Florida. Ở đây, 'Florida' sẽ là một tham số
- Bạn có thể muốn chạy một truy vấn SQL đặc biệt bằng cách sử dụng câu lệnh SELECT
Nếu bạn muốn chạy Thủ tục được lưu trữ hoặc truy vấn đặc biệt, thì ở giai đoạn này, bạn cần nhấp vào “Tùy chọn nâng cao” và viết truy vấn vào hộp được cung cấp. Bạn cũng sẽ cần nhập tên của cơ sở dữ liệu
Tiếp theo, bạn cần cung cấp chế độ Xác thực và mọi thông tin đăng nhập cần thiết
Nếu bạn muốn truy xuất kết quả của bảng hoặc truy vấn, bạn có thể chọn bảng hoặc truy vấn. Sau đó, nếu bạn nhấp vào "Tải", nó sẽ được tải vào Sổ làm việc Excel của bạn. Chúng tôi sẽ xem xét điều gì sẽ xảy ra nếu bạn nhấp vào “Chuyển đổi dữ liệu” trong phần tiếp theo của bài viết này
Khi bạn đã tạo liên kết, nó sẽ tải dữ liệu vào Bảng Excel. Sau đó, bạn có thể sử dụng nó giống như các dữ liệu khác được lưu trữ trong bảng
Bạn có thể làm mới dữ liệu bất cứ khi nào bạn muốn bằng cách nhấp chuột phải vào bên trong bảng và chọn Làm mới hoặc bằng cách đi tới Thiết kế bảng – Làm mới
Kết nối SQL để nhận và chuyển đổi
Cách thứ hai để kết nối với dữ liệu SQL là sử dụng cửa sổ Get and Transform
Điều này tuân theo quy trình tương tự để kết nối với SQL Server như đã đề cập ở trên, ngoại trừ việc bạn nhấn “Chuyển đổi dữ liệu” thay vì Tải
Khi bạn đã hoàn thành việc này, thì dữ liệu sẽ nằm trong cửa sổ Nhận và Chuyển đổi, còn được gọi là Trình soạn thảo Power Query.
Bạn cũng có thể tải dữ liệu trực tiếp từ Power Query Editor. Để thực hiện việc này, hãy truy cập Trang chủ – Nguồn mới
Sau đó, bạn có thể thực hiện thêm các thao tác trước khi chuyển dữ liệu vào Excel. Ví dụ, bạn có thể muốn
- Ẩn một số cột hoặc hàng [bằng cách vào Trang chủ – Chọn/Xóa cột]
- Thêm các cột bổ sung bằng công thức. [Tuy nhiên, Power Query sử dụng ngôn ngữ có tên là M, khác biệt đáng kể so với Excel. ]
- Tổng hợp dữ liệu bằng chức năng Group By
Nếu bạn làm điều này trong Power Query, nó sẽ giảm lượng dữ liệu đi vào Excel. Power Query giảm lượng dữ liệu mà nó nhận được từ SQL Server thông qua một quy trình được gọi là Truy vấn gấp. Ví dụ: bạn có thể truy xuất tất cả nội dung của bảng vào Power Query, giới hạn số lượng hàng chỉ còn 50 và giảm số lượng cột được sử dụng xuống chỉ còn hai.
Việc giảm này sẽ được kết hợp vào câu lệnh SQL để Excel chỉ truy xuất các hàng và cột cần thiết từ SQL Server. Điều này làm giảm lưu lượng mạng và tăng tốc độ truy xuất dữ liệu đó
Khi bạn rời khỏi cửa sổ Power Query bằng cách đi tới Trang chủ – Đóng & Tải, thì cửa sổ này sẽ tải dữ liệu vào Bảng Excel như trước đây.
Tuy nhiên, nếu bạn vào “Home – Close & Load To…” thay vào đó, bạn có thể
- Sử dụng nó trong Bảng Pivot hoặc Biểu đồ mà không cần tải dữ liệu trong Excel dưới dạng Bảng
- Lưu nó dưới dạng Kết nối [không tải dữ liệu vào Bảng Excel]
Nếu bạn lưu nó dưới dạng một kết nối, thì sau này bạn có thể sử dụng nó làm nguồn dữ liệu trong bất kỳ Bảng Pivot mới nào
Trong “Save & Load To…”, có hộp kiểm “Thêm dữ liệu này vào Mô hình Dữ liệu. ” Nếu bạn bấm vào đây, Excel sẽ xuất dữ liệu vào Power Pivot, còn được gọi là Mô hình Dữ liệu. Chúng ta sẽ xem xét Mô hình dữ liệu trong phần tiếp theo của Bài viết này
Kết nối SQL với Power Pivot
Cách thứ ba để kết nối trực tiếp SQL với Excel là sử dụng Mô hình Dữ liệu, còn được gọi là Power Pivot. Để mở Data Model, bạn cần vào Data – Manage Data Model.
Sau đó, bạn có thể nhập dữ liệu vào Power Pivot bằng cách đi tới Trang chủ – Lấy dữ liệu ngoài – Từ cơ sở dữ liệu – Từ SQL Server. Sau đó, bạn kết nối với SQL Server theo quy trình tương tự như trước đây
Khi bạn đã nhập dữ liệu, bạn có thể tạo các cột tính toán hoặc thước đo. Power Pivot sử dụng ngôn ngữ công thức có tên là DAX để xây dựng công thức. DAX là phiên bản mở rộng của các công thức Excel
Sau khi hoàn tất, bạn có thể tạo Bảng Power Pivot bằng cách đi tới Trang chủ – PivotTable – PivotTable
Điều này cho phép bạn tạo Bảng Pivot hoặc biểu đồ từ dữ liệu này
Đi đâu để biết thêm thông tin
Tôi hy vọng rằng bạn đã thích bài viết này
Bạn có quan tâm đến Power Query hoặc Power Pivot không?
Bạn có muốn học các câu lệnh SQL một cách nhanh chóng không? . Chúng ta sẽ xem xét sáu mệnh đề chính của Câu lệnh SQL SELECT. CHỌN, TỪ, Ở ĐÂU, NHÓM THEO, CÓ và ĐẶT HÀNG THEO
Trang được cập nhật lần cuối. Tháng 5 năm 2021
Phillip Burton
Người hướng dẫn Udemy
Hồ sơ Udemy của Phillip Burton
Phillip là một nhà tư vấn máy tính cung cấp các dịch vụ chuyên gia trong việc phát triển hệ thống máy tính và phân tích dữ liệu. Ông là Chuyên gia Công nghệ được Chứng nhận của Microsoft. Ông cũng đã được chứng nhận là Chuyên gia Giải pháp được Chứng nhận của Microsoft cho Business Intelligence, Microsoft Office 2010 Master và là Chuyên gia Microsoft Project 2013
Anh ấy thích điều tra dữ liệu, điều này cho phép anh ấy duy trì các hệ thống chủ động và cập nhật để giúp kiểm soát và giám sát các hoạt động hàng ngày. Ông cũng đã phát triển chuyên môn và các chương trình để lập danh mục, xử lý và kiểm soát dữ liệu điện tử, số lượng lớn giấy hoặc dữ liệu điện tử để phân tích và điều tra có cấu trúc
Anh ấy là một trong 9 Chuyên gia đã giành giải thưởng cho Giải thưởng Chuyên gia Thường niên lần thứ 11 của Expert Exchange và là một trong 10 chuyên gia hàng đầu của Expert Exchange trong quý đầu tiên của năm 2015
Sở thích của anh ấy là làm việc với dữ liệu, bao gồm Microsoft Excel, Access và SQL Server
Các bài báo gần đây của Phillip Burton
- Tham gia bên trong SQL. Liên kết các bảng cơ sở dữ liệu
- Sắp xếp SQL. Sử dụng lệnh ORDER BY
- Cách sử dụng SQL JOIN với nhiều bảng. Khái niệm cơ bản về tham gia trong SQL
Bài viết được đề xuất
Khoa học dữ liệu
SQL so với. Excel. SQL Server và Excel có thể làm việc cùng nhau như thế nào?
Năng suất văn phòng
Cách tạo bảng Pivot trong Excel. Hướng dẫn từng bước
Phát triển
Nghề nghiệp trong Khoa học dữ liệu. Nhà phân tích dữ liệu vs. Nhà khoa học dữ liệu
Khoa học dữ liệu
Cách sử dụng SQL JOIN với nhiều bảng. Khái niệm cơ bản về tham gia trong SQL
Kinh doanh
Phân tích dữ liệu với Excel. Hướng dẫn đầy đủ cho người mới bắt đầu
Năng suất văn phòng
Công thức Excel. 10 công thức đã giúp tôi giữ được công việc của mình
Steve Quatrani
Năng suất văn phòng
Excel HLOOKUP. Hướng dẫn từng bước về cách tra cứu dữ liệu
Năng suất văn phòng
Câu lệnh NẾU trong Excel. Cách sử dụng hàm IF trong Excel
Kasia Mikoluk
Năng suất văn phòng
Câu lệnh If Then trong Excel. Tận dụng tối đa chức năng IF
Năng suất văn phòng
Các ví dụ về VLOOKUP trong Excel để giúp bạn nắm vững các nguyên tắc cơ bản
Kinh doanh
Làm thế nào để trở thành một nhà phân tích kinh doanh mà không cần phá vỡ ngân hàng
CNTT & Phần mềm
Power BI là gì và tại sao bạn nên sử dụng nó?
Chia sẻ bài viết này
Sao chép
Các khóa học hàng đầu về Excel
Excel Essentials for the Real World [Khóa học Excel hoàn chỉnh]
Leila Gharani
4. 7 [15,170]
Người mới bắt đầu đến Pro trong Excel. Mô hình tài chính và định giá
365 việc làm
4. 6 [43,971]
Người bán hàng giỏi nhất
Microsoft Excel - Trực quan hóa dữ liệu, Biểu đồ & Đồ thị Excel
Phân tích Maven, Chris Dutton
4. 6 [35,569]
Người bán hàng giỏi nhất
Microsoft Excel - Excel từ cơ bản đến nâng cao
Kyle Pew, Văn phòng mới
4. 7 [346.780]
Người bán hàng giỏi nhất
Microsoft Excel - Phân tích dữ liệu với Excel Pivot Tables
Phân tích Maven, Chris Dutton
4. 6 [45,570]
Người bán hàng giỏi nhất
Microsoft Excel - Excel từ Sơ cấp đến Nâng cao 2022
Warrick Klimaytys
4. 6 [1,941]
Người bán hàng giỏi nhất
Mẹo chuyên nghiệp về Microsoft Excel. Đi từ Excel mới bắt đầu đến Excel nâng cao
Phân tích Maven, Chris Dutton
4. 6 [3,962]
Người bán hàng giỏi nhất
Khóa học Microsoft Excel cơ bản. Người mới bắt đầu đến Chuyên gia Excel
Alan Murray
4. 6 [626]
Microsoft Excel Bootcamp - Học cách thành thạo Excel trong 3 giờ
Joseph @ Wisdify
4. 6 [508]
Microsoft Excel cho quản lý xây dựng
James Gedney-Higham
4. 4 [379]
Người bán hàng giỏi nhất
Microsoft Excel - Power Query và PivotTable phân tích dữ liệu
Kyle Pew, Văn phòng mới
4. 7 [864]
Người bán hàng giỏi nhất
Microsoft Excel cho Tài chính, Kế toán & Phân tích tài chính
Irfan Sharif ACA ACCA
4. 3 [1,454]
>Các khóa học Excel khác
Sinh viên Excel cũng học
Phân tích Excel Excel VBA Biểu đồ Excel Công thức và hàm Excel Phân tích dữ liệu Bảng điều khiển Excel Bảng Pivot Phím tắt và mẹo Excel Microsoft Power BI Python Microsoft Word Trực quan hóa dữ liệu Excel Macro Microsoft Office
Trao quyền cho nhóm của bạn. Dẫn đầu ngành
Đăng ký thư viện các khóa học trực tuyến và công cụ học tập kỹ thuật số cho tổ chức của bạn với Udemy Business