Sổ tay sử sụng các hàm csdl với sự trợ giúp bằng vba

[SA_DQ]

BÀI 1: ĐẾM XEM 5 LOAI CÂY NÀO CÓ NHIỀU TRONG RỪNG​

Ví dụ chúng ta có bảng liệt kê các loại cây gỗ có trong 1 khu rừng, như được liệt kê trên cột [A] như sau:

Loại cây
Bứa
Lim
Máu chó
Dẻ
Trám
Trâm
Dẻ
Lim
Táu
Trắt
Máu chó
Dẻ
Trám
Trâm
Dẻ
. . .

Yêu cầu đề ra là thống kê 05 loại cây thường xuất hiện nhất trong khu rừng, từ cao đến thấp

Để thực hiện nhiệm vụ này, ta dùng macro như sau để trợ giúp:

Option Explicit
Sub Dém()
 Dim WF As Object, Cls As Range, Rng As Range
 
 Set WF = Application.WorksheetFunction
 Set Rng = Range([A1], [A1].End(xlDown))
 Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "D1"), Unique:=True
 For Each Cls In Range([d2], [d2].End(xlDown))
    [h2].Value = Cls.Value
    Cls.Offset(, 1).Value = WF.DCountA(Rng, [A1], [H1:H2])
 Next Cls
 [E2].Resize(Rng.Rows.Count).Interior.ColorIndex = 2
 Columns("D:E").Select
 Selection.Sort Key1:=Range("E2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1
 [D7].Resize(Rng.Rows.Count, 2).Font.ColorIndex = 2
 [E2].Resize(5).Interior.ColorIndex = 38
 [c2].Select
End Sub

Trình tự các bước đã tiến hành:

Bước chuẩn bị: Để áp dụng công thức/hàm DMAX(), ta cần lập ra vùng tiêu chuẩn (Criteria);
Vậy nên tại [H1] ta áp công thức =[A1]
& ta biết rằng vùng chuẩn thông thường tối thiểu là 2 ô;
Ở đây ô thứ 2 là ô [H2], mà dữ liệu ô này sẽ được macro cung cấp trước mỗi lần cần tính toán lượng lập lại của từng loại cây.

(1) Lọc ra danh sách duy nhất các loại cây gỗ đã được thống kê trong cơ sở dữ liệu tại cột [A]
(Danh sách này được cho hiện ra ở cột [D]

(2) Duyệt lần lượt từng loại cây theo danh sách duy nhất vừa lập (ở cột [D]) .Dùng hàm DMAX() trong bảng tính Excel để hiện kết quả tương ứng ở ô kề bên fải ô chứa loại cây đang duyệt.

(3) Xếp hai cột [D:E] theo số liệu giảm dần cột [E]

(4) Định dạng để người ngoài chỉ thấy những dữ liệu cần thiết. (Ẩn đi những gì không cần thiết)

 

[SA_DQ]

Chào các anh chị , em muốn được giúp ̀ về cách lọc giá trị Max và Min như sau:
Em có bảng dữ liệu ở 2 cột A và B (cột A là tên, cột B là trị số);
Xin được viết code xác định giá trị nhỏ nhất (Min) và giá trị lớn nhất (Max) ở cột B dựa theo tên cột A, các giá trị còn lại có thể xóa đi
hình minh họa:
dữ liệu: kết quả:
e chân thành cảm ơn.

Với macro này, kết quả sẽ hiện trên trang 'KetQua' tùy thuộc vô bạn chọn trị chuỗi trong ô validation [G1]
Macro có nội dung như sau:

Option Explicit
[B]Private Sub Worksheet_Change(ByVal Target As Range)[/B]
 If Not Intersect(Target, [G1]) Is Nothing Then
  Dim ShName As String
  Dim Rng As Range, Sh As Worksheet, Rg0 As Range, Cls As Range, WF As Object
 
  If Left(Target.Value, 1) = "K" Then
  ShName = "khac"
  Set Rg0 = Range("Khac")
  Else
  ShName = "SoLieu"
  Set Rg0 = Range("SL")
  End If
  Set Sh = ThisWorkbook.Worksheets(ShName)
  Set Rng = Sh.[b2].CurrentRegion
  Set WF = Application.WorksheetFunction
  [b2].CurrentRegion.Offset(1).ClearContents
  For Each Cls In Rg0
  If Cls.Value = "" Then Exit For
  Sh.[AB2].Value = Cls.Value
  With [A9999].End(xlUp).Offset(1)
  .Value = Cls.Value
  .Offset(, 1).Value = WF.DMin(Rng, Sh.[B1], Sh.[AA1:AA2])
  .Offset(1).Value = Cls.Value
  .Offset(1, 1).Value = WF.DMax(Rng, Sh.[B1], Sh.[AA1:AA2])
  End With
  Next Cls
 End If
[B] End Sub[/B]

 

[SA_DQ]

Một cửa hàng văn fòng fẩm nó có kinh doanh nhiều mặt hàng với nhiều xuất xứ khác nhau;
Để dễ trong quản lí, người ta đã fân ra các nhóm mặt hàng, như
Bang Keo (BK): BK giấy VN
BK 2 mặt HQ
Cắt BK nhỏ ĐL
. . . . . . .
Kim bấm (KB): KB số 10 VN
KB số 10 Nhật
Gỡ kim Nhật
. . . . . . .
Dĩa CD (CD): CD Bách Việt VN
DVD Nhật
Đĩa mền Nhật
. . . . . . .
. . . . . . . .

Cửa hàng có sổ thống kê theo dõi bán hàng trong các tháng; Sổ gồm các trường:

STT | Nhóm hàng | Tên hàng | Ngày bán | ĐVT | Xuất xứ | Số lượng | Đơn giá | Thành tiền

Yêu cầu đề ra là cần lập bảng thống kê tổng số tiền bán được trong tháng theo xuất xứ & theo nhóm hàng như bảng dưới đây:

Loại hàng | BK  | KB | CD | . . .
VN         | ?   | ? |? |..
TQ         | ?   | ? |? |..
ĐL         | ?   | ? |? |..
Nhật      | ?   | ? |? |..

Macro có nội dung sau:

Option Explicit
Sub HamCSDL()
Dim Sh As Worksheet, Rng As Range, WF As Object
Dim Cls As Range, Cll As Range
Dim Dong As Long, Cot As Byte
 
Cot = 2
Set Sh = ThisWorkbook.Worksheets("BaiThi")
Set Rng = Sh.[B4].CurrentRegion
Set WF = Application.WorksheetFunction
For Each Cls In Range("LoaiDong")
  Sh.[AD4].Value = Cls.Value
  Cot = Cot + 1:  Dong = 6
  For Each Cll In Range("XuatXu")
  Sh.[AE4].Value = Cll.Value
  Dong = Dong + 1
  Cells(Dong, Cot).Value = WF.DSum(Rng, Sh.[I3], Sh.[Ad3:AE4])
  Next Cll
Next Cls
Randomize
[b2].Interior.ColorIndex = 34 + 9 * Rnd() \ 1
End Sub

Các bạn thông cảm, mình không thể gởi file được, do mình chỉ có file loại *.rar

 

[SA_DQ]

Một trường PTCS nọ đã lập danh sách fát tiền hộ nghèo của năm học 2013-14 cho hơn 700 em học sinh trong các lớp;
Danh sách như được trích ra dưới đây:

Lớp | STT|TT  |  Ho & Ten |Ngày sinh |Nữ | Số tiền|. . .
6A   |001| 01 |Tòng V. Ba |10/13/02  |0  |3.500.000|. . .
6A   |003| 02 |Quảng T Vi |10/31/02  |1  |3.500.000|. . . 
. . . . . . . 
6E   |001| 01 |Tòng Vinh  |01/13/02  |0  |3.500.000|. . .
6E   |003| 02 |Quảng Thi  |09/31/02  |0  |3.200.000|. . . 
. . . . . . . 
9A   |001| 01 |Thòng V. Ba|05/3/02   |0  |3.500.000|. . .
. . . . . .
9E   |003| 02 |Vòng T Vi  |10/10/02  |1  |3.500.000|. . . 
. . . . . . .

Yêu cầu đề ra là danh sách sắp xếp theo từng lớp & sau mỗi lớp ta thêm dòng tổng cộng số tiền của lớp đó
Như bảng sau đây:

Lớp  | STT|TT  |  Ho & Ten |Ngày sinh |Nữ | Số tiền|. . .
6A   |001| 01 |Tòng V. Ba |10/13/02  |0  |3.500.000|. . .
6A   |003| 02 |Quảng T Vi |10/31/02  |1  |3.500.000|. . . 
. . .
 
TS6A |  Công theo lớp:              |    108.500.000
   
6E   |001| 01 |Tòng Vinh  |01/13/02  |0  |3.500.000|. . .
6E   |003| 02 |Quảng Thi  |09/31/02  |0  |3.200.000|. . . 
 
. . . . . . . 
TS6E |  Công theo lớp:              |   110.300.000
. . . . . . . . .
TS9E |  Công theo lớp:              |   109.350.000

Macro để thực hiện việc đó có nội dung sau:

Option Explicit
Sub TinhTienTheoLop()
 Dim Cls As Range, WF As Object, CSDL As Range, sRng As Range, dRg As Range
 Dim MyAdd As String
 Set WF = Application.WorksheetFunction
 Set CSDL = Range([A5], [A5].End(xlDown))
 Set sRng = CSDL.Find("GPE", , xlFormulas, xlPart)
 Set dRg = Rows("65525:65525")
 If Not sRng Is Nothing Then
  MyAdd = sRng.Address
  Do
  Set dRg = Union(dRg, sRng.EntireRow)
  Set sRng = CSDL.FindNext(sRng)
  Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
 End If
 dRg.Delete
 Set CSDL = [b5].CurrentRegion
 For Each Cls In Range([AA5], [AA5].End(xlDown))
  [ac5].Value = Cls.Value
  With [A65500].End(xlUp).Offset(1)
  .Value = Cls.Value & "GPE":  .Offset(, 2).Value = [AA1].Value
  Randomize
  .Interior.ColorIndex = 34 + 9 * Rnd \ 1
  .Offset(, 7).Value = WF.DSum(CSDL, [H4], [Ac4:AC5])
  End With
 Next Cls
 [b5].CurrentRegion.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
  , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
  , Orientation:=xlTopToBottom
End Sub

 

[SA_DQ]

File bài 3 có ở:
http://giaiphapexcel.com/forum/showthread.php?86749-điền-hàng-tính-tổng-sau-mỗi-lớp/page2

File bài 2 có ở: http://www.giaiphapexcel.com/forum/showthread.php?86230-giúp-mình-thống-kê-hàm-data-table-với

 

[SA_DQ]

BÀI TOÁN THỐNG KÊ SẢN LƯỢNG CỦA CÔNG NHÂN THEO TỪNG LOẠI SẢN FẨM​

Ở đơn vị nọ, người ta đã thống kê sản lượng theo ngày theo như bảng kê dưới đây:

TT | Họ Tên |Loại SF| Ngày|Sản lượng|. ..
01 |NT Hoa  |    A1    |01/13| 19.5  |
02 |TT Hòa  |  B1      |01/13| 21.7  |
03 |HV Hóa  |  A2      |01/13| 19.9  |
. . | .. . . . | . . .  | . . . | . . .|

Nhiệm vụ đề ra là ta cần lập bảng thống kê sản lượng từng người như bảng sau:

TT | Tên  | A1  | A2  | A3  | B | B1 | C   | C1 | D
01 |H Hoa|98.5|59.8| 12.0|50|75.1|12. |2.4 |.5
02 |L Hóa|88.5|9.08| 32.0|57|75.1|19. |9.4 |.9
. . . . . . .

Nhiệm vụ này sẽ được macro sau thực thi 1 cách toàn vẹn & nhanh chóng:

Option Explicit
Sub SanLuongTungNguoi()
Dim Sh As Worksheet, Rng As Range, WF As Object, Cls As Range
Dim Cot As Byte, J As Byte, Rws As Long, Tmr As Double
Set Sh = ThisWorkbook.Worksheets("GC TN")
Tmr = Timer():  Sheets("Sheet8").Select
Rws = Sh.[B2].CurrentRegion.Rows.Count
Set WF = Application.WorksheetFunction
Set Rng = Sh.[B2].Resize(Rws, 6)
For Each Cls In Range([E4], [E4].End(xlDown))
  Sh.[AA2].Value = Cls.Value
  For J = 6 To 13
  Sh.[AB4].Value = Cells(3, J).Value
  Cells(Cls.Row, J).Value2 = WF.DSum(Rng, Sh.[g2], Sh.[AA1:AB2])
  Next J
Next Cls
[N1].Value = Timer() - Tmr
End Sub

Các bạn có thể xem thêm tại: http://www.giaiphapexcel.com/forum/showthread.php?96965-hàm-tính-tổng-với-nhiều-điều-kiện

 

[SA_DQ]

Ở CQ nọ, người ta đã thống kê lịch trực trong tuần cho các nhân viên; Lịch này được thể hiện trên trang tính 'Job' & có dạng na ná như sau:

Date | Name | Type of Job
01    | Anh  | A
01    | IVan | A
02    | Hoa  | C
02    | Nga  | B
. . . ...

Có nghĩa là danh sách chục vị trong fòng chia ra trực với 3 loại công việc A, B & C
Nhiệm vụ của chúng ta là lập ra bảng thống kê có dạng sau:

Date           | 1 1 1 | 2 2 2
Type of Job| A B C| A B C
Anh            | ?  ?  ?| ? ? ?
Ivan            | ?  ?  ?| ? ? ?
Hoa            | ?  ?  ?| ? ? ?
Nga           | ?  ?  ?| ? ? ?

Macro sau đây sẽ thỏa mãn cho ta điều đó:

Option Explicit
Sub TinhTruc()
Dim Sh As Worksheet, Rng As Range, cRg As Range, WF As Object, Cls As Range
Dim Min_ As Integer, Max_ As Integer, J As Integer, Type_ As Byte, W As Byte
Set WF = Application.WorksheetFunction
Set Sh = ThisWorkbook.Worksheets("Job")
Set Rng = Sh.Range("CSDL")
Set cRg = Sh.Range("cRiteria")
Min_ = WF.Min(Rng(1).Resize(Rng.Rows.Count))
Max_ = WF.Max(Rng(1).Resize(Rng.Rows.Count))
For Each Cls In Range([A3], [A3].End(xlDown))
  Sh.[AC2].Value = Cls.Value
  For J = Min_ To Max_
  Sh.[AB4].Value = J
  For W = 65 To 67
  Sh.[ad2].Value = Chr(W)
  Cls.Offset(, 3 * J - 2 + W - 64).Value = WF.DCountA(Rng, Sh.[c1], cRg)
 
  Next W
  Next J
Next Cls
Randomize
[a1:a2].Interior.ColorIndex = 34 + 9 * Rnd() \ 1
End Sub

    ...

 

[kuldokk]

Bác tâm huyết quá, lặn lội sang đây để mở mang kiến thức VBA cho anh em . Cảm ơn bác

 

[SA_DQ]

Ở cơ quan nọ lập ra lịch trực trong tuần cho nhân viên gồm 3 loại công việc 'A', 'B' & 'C' như bảng dưới đây:

  Ngày |  Tên |Loại công việc
  01   |Hồng  |     A
  01   |Thắm  |     A
  02   | Thơm |     C
  02   |  Ivan|     B
   02  | Thắm |     A
  ..   | . . .|   ..

Yêu cầu đề ra là ta cần lập bảng thống kê có nội dung như sau:

 Tên   |Thứ trong tuần|  1  |  2  |. . .       
       |Loại công việc|A|B|C|A|B|C| . . . .
Hồng   |              |1| | | | | |. . .
Charles|              | | | | | |1|. . .
Ivan   |              | | | | |1| |. . .
Thơm   |              | | |1| | | |. . .
Thắm   |              |1| | | | | |. . .

Bảng thống kê này sẽ được macro sau đây trợ giúp:

Option Explicit
Sub TinhTruc()
 Dim Sh As Worksheet, Rng As Range, cRg As Range, WF As Object, Cls As Range
 Dim Min_ As Integer, Max_ As Integer, J As Integer, Type_ As Byte, W As Byte
 Set WF = Application.WorksheetFunction
 Set Sh = ThisWorkbook.Worksheets("Job")
 Set Rng = Sh.Range("CSDL")
 Set cRg = Sh.Range("cRiteria")
 Min_ = WF.Min(Rng(1).Resize(Rng.Rows.Count))
 Max_ = WF.Max(Rng(1).Resize(Rng.Rows.Count))
 For Each Cls In Range([A3], [A3].End(xlDown))
  Sh.[AC2].Value = Cls.Value
  For J = Min_ To Max_
  Sh.[AB4].Value = J
  For W = 65 To 67
  Sh.[ad2].Value = Chr(W)
  Cls.Offset(, 3 * J - 2 + W - 64).Value = WF.DCountA(Rng, Sh.[c1], cRg)
  Next W
  Next J
 Next Cls
 Randomize
 [a1:a2].Interior.ColorIndex = 34 + 9 * Rnd() \ 1
End Sub

http://www.giaiphapexcel.com/forum/...-việc-hoàn-thành-của-từng-nhân-viên-theo-ngày

 

[xuantham]

Bác Sa không dạy cho em học nhá