Dùng SQL kết hợp với công thức Excel - Giải pháp mềm dẻo! (4 người xem)

[Nguyễn Duy Tuân]

Dùng hàm BS_SQL để tạo báo cáo Excel bằng ngôn ngữ SQL rất linh hoạt và tốc độ nhanh, tuy nhiên một số tình huống cần dùng công thức Excel để xử lý trong mảng kết quả trả về sẽ là linh hoạt và giảm bớt các khâu xử lý trung gian, các phép tính được thực hiện dễ dàng...
Video giới thiệu việc tính số thứ tự, tính lũy kế, dùng hàm Vlookup trong câu lệnh SELECT của ngôn ngữ truy vấn CSDL SQL ngay trên bảng trên bảng tính Excel.

 

[PhanTuHuong]

Ngày xưa biểu Tuân biểu diễn cho bác Chung QN, bác trợn mắt vì quá nhanh phỏng?

 

[Nguyễn Duy Tuân]

Ngày xưa biểu Tuân biểu diễn cho bác Chung QN, bác trợn mắt vì quá nhanh phỏng?

Bác ý thâm niên làm Excel cũng phải 30 năm đấy anh

 

[Doanh7]

"For intColIndex = 0 To rs.Fields.Count - 1
rDanField.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next

rDanData.CopyFromRecordset rs
rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing"

A. Tuân ơi cho em hỏi, Thay vì dán dữ liệu truy vấn SQL vào excel, ta có thể truyền dữ liệu đó cho 1 mảng ảo không ạ ??

 

[Nguyễn Duy Tuân]

"For intColIndex = 0 To rs.Fields.Count - 1
rDanField.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next

rDanData.CopyFromRecordset rs
rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing"

A. Tuân ơi cho em hỏi, Thay vì dán dữ liệu truy vấn SQL vào excel, ta có thể truyền dữ liệu đó cho 1 mảng ảo không ạ ??

Có chứ. Bản thân biến rs (Recordset) là bảng trong bộ nhớ rồi. Em có thể dùng vòng lặp để nhặt dữ liệu theo điều kình mình muốn
I = 0
While not rs.Eof()
If điều kiện Ok Then
I = I + 1
for J = 1 to rs,Fields.Count
Mang(I,J) = rs.Fields(J).Value
next I
End If
rs.MoveNext
Wend

 

[Doanh7]

Có chứ. Bản thân biến rs (Recordset) là bảng trong bộ nhớ rồi. Em có thể dùng vòng lặp để nhặt dữ liệu theo điều kình mình muốn
I = 0
While not rs.Eof()
If điều kiện Ok Then
I = I + 1
for J = 1 to rs,Fields.Count
Mang(I,J) = rs.Fields(J).Value
next I
End If
rs.MoveNext
Wend

Dạ vâng! đúng như anh Tuân nói ạ.
Em muốn hỏi thêm nữa là: vậy ta có thể sử dụng câu lệnh SQL để truy vấn từ CSDL là 1 mảng ảo không ạ ?


Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
With cn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
linktex _
& ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
.Open
If .State <> 1 Then
MsgBox linktex & "Disconnected! PLZ check connection"
.Rollback
getSpeed (False)
Exit Sub
End If
End With
rs.Open sql, cn ' Có thể truy vấn từ 1 mảng ảo thay cho "cn" hay không ????

 

[Nguyễn Duy Tuân]

Ý bạn mảng ảo là table, vùng range chứ. Cnn là connection làm việc với file. Bạn không thể làm việc với mảng ảo. Khi dùng tới csdl excel thù bỏ rollback, commit đi nhé vì k có tác dụng.

 

[khanhhero]

Có chứ. Bản thân biến rs (Recordset) là bảng trong bộ nhớ rồi. Em có thể dùng vòng lặp để nhặt dữ liệu theo điều kình mình muốn
I = 0
While not rs.Eof()
If điều kiện Ok Then
I = I + 1
for J = 1 to rs,Fields.Count
Mang(I,J) = rs.Fields(J).Value
next I
End If
rs.MoveNext
Wend

A Tuân cho em hỏi là vậy bước copyfromrecordset mình phải viết ntn ạ ( vì e test là Mang(I, J).copyfromrecordset rs --> range("A1").resize(I, J) = Mang(I, J) thì không ra giá trị nào cả).

 

[Nguyễn Duy Tuân]

A Tuân cho em hỏi là vậy bước copyfromrecordset mình phải viết ntn ạ ( vì e test là Mang(I, J).copyfromrecordset rs --> range("A1").resize(I, J) = Mang(I, J) thì không ra giá trị nào cả).

Hàm CopyFromRecordset là để nhận các dòng trong bảng recordset(hiểu như là table) vào vùng của bảng tính. Ví dụ đưa cả bảng trong recordset và bắt đầu điền vào ô A4
Range("A4").CopyFromRecordset(rs)

Còn em muốn một phần từ mảng MANG(i,j) nhận một giá trị trong Recordset thì phải dùng vòng lặp để tìm tới dòng cần lấy, sau đó nhận một hay nhiều giá trị ở từng cột. Ví dụ:

rs.MoveFirst '(có thể không cần) - Đưa về dòng đầu của Recordset
While not rs.Eof()
if rs.Fields[vị trí cột].Value = 'Cái gì đó' Then
MANG(i,j) = rs.Fields[vị trí cột].Value
'...
Exit Sub 'Thoát vòng lặp nếu không tìm nữa
End If
rs.MoveNext
WEnd

 

[Nguyễn Duy Tuân]

Dùng hàm BS_SQL để tạo báo cáo Excel bằng ngôn ngữ SQL rất linh hoạt và tốc độ nhanh, tuy nhiên một số tình huống cần dùng công thức Excel để xử lý trong mảng kết quả trả về sẽ là linh hoạt và giảm bớt các khâu xử lý trung gian, các phép tính được thực hiện dễ dàng...
Video giới thiệu việc tính số thứ tự, tính lũy kế, dùng hàm Vlookup trong câu lệnh SELECT của ngôn ngữ truy vấn CSDL SQL ngay trên bảng trên bảng tính Excel.

Toàn bộ nội dung hướng dẫn cùng file ví dụ ở bài số 1 tại đường dẫn sau:
Download file nguồn: http://bluesofts.net/su-dungg-sql-ket-hop-voi-cong-thuc-excel-ham-data-trong-add-in-a-tools.html

 

[khanhhero]

Nhờ a Tuân giải đáp giúp e, liệu có thể lấy dữ liệu từ table sql dựa vào những điều kiện được cho trước trên excel ko ạ?

Ví dụ em có sheet1.range("A1:B30").value với cột A header là "Dept code" và cột B với header là "Acc code" và em có câu truy vấn từ SQL như sau:

Sub TEST()
Set cnn = New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strcnn As String
Dim sqlstr As String
strcnn = "Driver={SQL Server}; Server=SERVER; database=DATABASE; UID=USER; PWD=PASSWORD;"
cnn.Open strcnn
sqlstr = "select GL.Dcode, GL.AccCode, sum(GL.Amount) FROM GL where GL.Period = 201708 GROUP BY GL.DCode, GL.AccCode"
rs.Open sqlstr, strcnn
Sheet2.Range("A2").CopyFromRecordset rs
rs.close
cnn.Close
Set cnn = Nothing
End Sub

Thay vì e phải export tất cả dữ liệu ra sheet2 rồi e sumifs về lại sheet1 theo Dept Code và Acc Code, nay em muốn nó đổ ra dữ liệu trực tiếp ra cột C của sheet1 theo như những điều kiện em đang để ở sheet1 thì có được không anh? Và nếu được thì cách làm ntn ạ. Em có nghĩ đến INNER JOIN nhưng ko biết phải làm thế nào vì khai báo nguồn là SQL Table chứ không phải là excel.
Em cám ơn anh ạ!

 

[Nguyễn Duy Tuân]

Dữ liệu điều kiện thì bạn để ở một ô, trong VBA bạn viết SQL thì câu điều kiện phải gắn với giá trị ở trên ô bạn nhập.

 

[namhandsome1]

Dùng hàm BS_SQL để tạo báo cáo Excel bằng ngôn ngữ SQL rất linh hoạt và tốc độ nhanh, tuy nhiên một số tình huống cần dùng công thức Excel để xử lý trong mảng kết quả trả về sẽ là linh hoạt và giảm bớt các khâu xử lý trung gian, các phép tính được thực hiện dễ dàng...
Video giới thiệu việc tính số thứ tự, tính lũy kế, dùng hàm Vlookup trong câu lệnh SELECT của ngôn ngữ truy vấn CSDL SQL ngay trên bảng trên bảng tính Excel.

Có cách nào dùng câu lệnh sql mà không phải cài phần mêm a tool của bluesoft ko bác?