28.4 C
Ho Chi Minh City
Wednesday, June 4, 2025
AIPHOGPT.COM
Trang chủ Blog Trang 200

VBA Excel Hộp thoại thông báo

Lê Quốc Thái
-
2
VBA Excel Hộp thoại thông báo
  1. Loạt các bài viết có liên quan chuyên đề VBA:
  2. VBA Excel Hộp thoại thông báo
  3. VBA Excel Biến trong VBA Excel
  4. VBA Excel sử dụng Scripting Dictionary
  5. VBA Excel Biến trong VBA Excel
  6. VBA Excel Workbook, worksheet
  7. VBA Excel Range, Cells
  8. VBA Excel Hàm trong Excel VBA
  9. VBA Excel Events, Application Object
  10. VBA Excel Function and Sub Excel VBA
  11. VBA Excel Array Mảng trong Excel VBA
  12. VBA Excel FileSystemObject trong Excel VBA
  13. VBA Excel Collection trong Excel VBA
  14. VBA Excel Hashtable trong Excel VBA
  15. VBA Excel Stack trong Excel VBA
  16. VBA Excel Queue trong Excel VBA
  17. VBA Excel SortedList Excel VBA
  18. VBA Excel ArrayList Excel VBA
  19. VBA Excel Ví dụ về Scripting Dictionary
  20. Sách VBA Excel 2016 power programming with vba (pdf)
1. MsgBox:

Hộp thoại thông báo msgbox trong VBA dùng để tạo thông báo thông tin nào cho người dùng khi cần thiết.
Cấu trúc của hàm msgbox trong VBA:

MsgBox(Prompt, [Buttons As VbMsgBoxStyle], [Title], [HelpFile], [Context]) As VbMsgBoxResult

Chú thích:

Prompt: Bắt buộc (phải có). Là nội dung của hộp thoại thông báo. Chiều dài tối đa của Prompt là 1024 ký tự. Nếu nội dung hộp thoại thông báo có nhiều dòng, để tách dòng bạn có thể dùng các ký tự tách dòng, gồm: Chr(13), Chr(10) hoặc từ khóa vbNewline.
Buttons: Không bắt buộc. Là hằng số quy định cụ thể số lượng và loại các nút hiển thị. Nếu không sử dụng, giá trị mặc định là số 0.

Title: Không bắt buộc. Là chuỗi được hiển thị trên thanh tiêu đề của hộp thoại. Nếu bỏ qua, tên ứng dụng được đặt trong thanh tiêu đề (ở đây, cụ thể ở đây là “Microsoft Excel”).
HelpFile: Không bắt buộc. Chuỗi xác định tên tập tin trợ giúp sử dụng để cung cấp các trợ giúp theo ngữ cảnh cho các hộp thoại.
Context: Không bắt buộc. Là số thứ tự tình huống trong HelpFile. Nếu HelpFile có thì Context phải có.
VbMsgBoxResult:

Câu lệnh đơn giản như sau:

MsgBox "Nội dung thông báo."

Ví dụ 1:

Sub msgbox1()
    MsgBox "Xin chào!"
End Sub

Có thể nối nội dung thông báo với nội dung trên bảng tính. 

Ví dụ 2: Với [A1]="Exl2Lab"
Sub msgbox2()
    MsgBox "Xin chào! " & Sheet1.Range("A1")
End Sub

Để tạo thông báo có nhiều dòng (xuống dòng trong bảng thông báo) ta dùng từ khóa vbNewLine.
Ví dụ 3:

Sub msgbox3()
    MsgBox "Xin chào! " & Sheet1.Range("A1") & vbNewLine & "Email: " & Sheet1.Range("A2")
End Sub
 

Ngoài ra, msgbox thường hay dùng để kiểm tra kết quả từng đoạn code khi viết, để tạo thông báo nhắc người dùng xác nhận trước một tác vụ quan trọng (như xóa dữ liệu, in ấn…).
Ví dụ 4:

Sub mgbDelete()
Dim ans As Integer
ans = MsgBox("Ban muon xoa du lieu tren sheet nay?", vbYesNo + vbQuestion, "Xoa du lieu")
If ans = vbYes Then
    'Chọn Yes thì thực hiện tác vụ
    Cells.ClearContents
Else
    'không làm gì
End If
End Sub

Chi tiết các ví dụ xem file đính kèm ở cuối bài.

2. InputBox:

Cấu trúc hàm InputBox:

InputBox(Prompt,[Title],[Default],[Xpos],[Ypos],[HelpFile,Context]) As String

Chú thích:
Prompt: Bắt buộc. Là nội dung hiển thị ở trong hộp thoại thông báo, tối đa 1024 ký tự. Để muốn xuống dòng trong hộp thoại thông báo thì dùng ký tự đặc biệt Chr(10) hoặc Chr(13) hoặc dùng vbNewLine chèn vào giữa các dòng.
Title: Không bắt buộc. Là tiêu đề của hộp thoại thông báo, nếu để trống thì tiêu đề là tên ứng dụng (ở đây là “Microsoft Excel”).
Default: Không bắt buộc. Là giá trị mặc định nhập sẵn trong ô để người dùng nhập nội dung cần thông báo vào.
XPos: Không bắt buộc. Là khoảng cách (đơn vị pixels) theo phương X (phương ngang) tính từ mép trái màn hình hiển thị. Nếu để trống (không nhập) giá trị này thì hộp thoại sẽ hiển thị ở giữa theo phương ngang.
YPos: Không bắt buộc. Là khoảng cách (đơn vị pixels) theo phương Y (phương đứng) tính từ mép trên màn hình hiển thị. Nếu để trống (không nhập) giá trị này thì hộp thoại sẽ hiển thị ở giữa theo phương đứng.
HelpFile: Không bắt buộc.Là chuỗi xác định tệp trợ giúp sẽ được sử dụng để cung cấp hỗ trợ giúp ngữ cảnh cho hộp thoại thông báo.
Context: Không bắt buộc. Một số để xác định số ngữ cảnh trợ giúp được ngữ cảnh trợ giúp gán cho chủ đề thích hợp. Nếu ngữ cảnh được cung cấp thì cũng phải cung cấp tài liệu trợ giúp (HelpFile).

 
Ví dụ: Chi tiết xem file đính kèm ở cuối bài.

Sub Vidu_Iputbox()
Dim inBox As String
inBox = InputBox("Chon:" & vbNewLine & "1-Yes" & vbNewLine & "2-No", "Tieu de Input Box", 1)
If inBox = "1" Or inBox = "2" Then
    MsgBox "Day la gia tri vua chon: " & inBox, , "Thong bao"
Else
    MsgBox "Chon sai!", , "Thong bao"
End If
End Sub
 
 

Tải file mẫu: MsgBox, Input

VBA Excel Ví dụ về Scripting Dictionary

Lê Quốc Thái
-
0
VBA Excel Ví dụ về Scripting Dictionary
  1. Loạt các bài viết có liên quan chuyên đề VBA:
  2. VBA Excel Hộp thoại thông báo
  3. VBA Excel Biến trong VBA Excel
  4. VBA Excel sử dụng Scripting Dictionary
  5. VBA Excel Biến trong VBA Excel
  6. VBA Excel Workbook, worksheet
  7. VBA Excel Range, Cells
  8. VBA Excel Hàm trong Excel VBA
  9. VBA Excel Events, Application Object
  10. VBA Excel Function and Sub Excel VBA
  11. VBA Excel Array Mảng trong Excel VBA
  12. VBA Excel FileSystemObject trong Excel VBA
  13. VBA Excel Collection trong Excel VBA
  14. VBA Excel Hashtable trong Excel VBA
  15. VBA Excel Stack trong Excel VBA
  16. VBA Excel Queue trong Excel VBA
  17. VBA Excel SortedList Excel VBA
  18. VBA Excel ArrayList Excel VBA
  19. VBA Excel Ví dụ về Scripting Dictionary
  20. Sách VBA Excel 2016 power programming with vba (pdf)

Bài viết liên quan:

VBA Excel sử dụng Scripting Dictionary

Dictionary (Dic) là một phần trong thư viện Microsoft Scripting Runtime (scrrun.dll), cho phép lưu trữ và truy xuất số lượng lớn Item theo Key duy nhất tương ứng.

1. Khai báo

1.1. Kiểu khai báo sớm

(Có Tooltip khi gọi Dic, phải thiết lập trong Tools/References)    

– Trong cửa sổ VBA, Tools menu, References.

– Tìm và check vào mục “Microsoft Scripting Runtime” trong cửa sổ References – VBAProject.

Khai báo trong code:

Dim Dic As Scripting.Dictionary

Set Dic = New Scripting.Dictionary

 1.2. Kiểu khai báo muộn

(Không có Tooltip khi gọi Dic, không cần thiết lập trong Tools/References).

Khai báo trong code: 

Dim Dic As Object

Set Dic = CreateObject("Scripting.Dictionary")

2. Các phương thức

2.1. Add

Dic.Add Key, Item

Thêm Item (đối tượng) vào Dic, yêu cầu Key của Item phải chưa tồn tại trong Dic.

Key: Nhận dữ liệu là kiểu số hoặc kiểu chuỗi, yêu cầu Key là duy nhất trong Dic.

Item: Nhận kiểu dữ liệu là chuỗi hoặc số, bao gồm cả rỗng. Item có thể là một giá trị đơn hoặc một mảng (Array).

Ví dụ:

Sub AddMethod()
    'Dic.Add Key, Item'
    Dim Dic As Object
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.Add "KeyA", 10
    Dic.Add "KeyB", "Item2"
    Dic.Add "KeyC", ""
    Dic.Add "KeyD", Array(20, 50)
End Sub

2.2. Exists

Dic.Exists(Key)

Kiểm tra sự tồn tại của một Key trong Dic. Trả về True nếu Key đó tồn tại trong Dic, ngược lại trả về False.

Ví dụ:

Sub ExistsMethod()
    'Dic.Exists(Key) '
    Dim Dic As Object
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.Add "KeyA", 10
    MsgBox Dic.Exists("KeyA")   'True'
End Sub

2.3. Remove

Dic.Remove(Key)

Xóa một Item trong Dic theo Key chỉ định. Nếu Key chỉ định chưa tồn tại trong Dic thì sẽ xảy ra lỗi.

Ví dụ: 

Sub RemoveMethod()
    'Dic.Remove(Key) '
    Dim Dic As Object
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.Add "KeyA", 10
    Dic.Remove ("KeyA")
    MsgBox Dic.Exists("KeyA")   'False'
End Sub

2.4. RemoveAll

Dic.RemoveAll

Xóa tất cả các Items có trong Dic.

Ví dụ:

Sub RemoveAllMethod()
    'Dic.RemoveAll'
    Dim Dic As Object
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.Add "KeyA", 10
    Dic.Add "KeyB", 20
    Dic.RemoveAll
    MsgBox Dic.Count    '0'
End Sub

2.5. Items

Dic.Items

Trả về một mảng một chiều gồm toàn bộ Items có trong Dic.

Mảng một chiều này luôn có cận dưới bằng 0, dù khai báo Option Base 1

Ví dụ:

Sub ItemsMethod()
    'Dic.Items'
    Dim Dic As Object, Arr()
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.Add "KeyA", 10
    Dic.Add "KeyB", 20
    Arr = Dic.Items     'LBound(Arr) = 0'
End Sub

2.6. Keys

Dic.Keys

Trả về một mảng một chiều gồm toàn bộ Keys tồn tại trong Dic.

Mảng một chiều này luôn có cận dưới bằng 0, dù khai báo Option Base 1

Ví dụ: 

Sub KeysMethod()
    'Dic.Keys'
    Dim Dic As Object, Arr()
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.Add "KeyA", 10
    Dic.Add "KeyB", 20
    Arr = Dic.Keys     'LBound(Arr) = 0'
End Sub

3. Thuộc tính

3.1. Item

Dic.Item(Key) 

'Hoặc:' 

Dic(Key)

– Gọi Item theo Key chỉ định. Nếu Key chỉ định chưa tồn tại trong Dic, thì Dic sẽ tự động thêm (Add) Key đó vào, và Item ứng với Key đó là rỗng.

– Thay đổi giá trị của Item theo Key chỉ định. Nếu Key chỉ định chưa tồn tại trong Dic, thì Dic sẽ tự động thêm (Add) key đó vào, và Item ứng với Key đó có giá trị vừa đưa vào.

Ví dụ: 

Sub ItemProperty()
    'Dic.Item(Key)'
    'Dic(Key)   '
    Dim Dic As Object, x, y, z
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.Add "KeyA", 10
    Dic.Add "KeyB", 20
    x = Dic.Item("KeyA") '10'
    y = Dic("KeyA") '10'
    z = Dic("KeyC")
    Dic("KeyC") = 100
    MsgBox Dic.Item("KeyC") '100'
    MsgBox Dic.Count    '3'
End Sub

3.2. Key

Dic.Key(Key) = NewKey

Dùng để thay đổi giá trị mới của một Key chỉ định đã tồn tại trong Dic. Yêu cầu:

– Key chỉ định phải đã tồn tại trong Dic

– Giá trị mới của Key đó phải là duy nhất trong Dic (tức là có thể vẫn là giá trị cũ).

Ví dụ:

Sub KeyProperty()
    'Dic.Key(Key)=NewKey'
    Dim Dic As Object
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.Add "KeyA", 10
    Dic.Key("KeyA") = "KeyB"
End Sub

3.3. Count

Dic.Count

Trả về số Items có trong Dic.

Ví dụ:

Sub CountProperty()
    'Dic.Count '
    Dim Dic As Object, i As Long
    Set Dic = CreateObject("Scripting.Dictionary")
    For i = 1 To 5
        Dic.Add "Key" & i, ""
    Next i
    MsgBox Dic.Count    '5'
End Sub

3.4. CompareMode

Dic.CompareMode = BinaryCompare 

Dic.CompareMode = TextCompare

Thiết lập thuộc tính phân biệt chữ hoa chữ thường cho giá trị của Key.

BinaryCompare: (Giá trị mặc định của Dic) Phân biệt chữ hoa chữ thường

TextCompare: Không phân biệt chữ hoa chữ thường

Lưu ý: Thiết lập CompareMode cho Dic khi Dic rỗng (chưa có item nào trong Dic).

Ví dụ:

Sub CompareModeProperty()
    'Dic.CompareMode = vbBinaryCompare'
    'Dic.CompareMode = vbTextCompare '
    Dim Dic As Object, i As Long
    Set Dic = CreateObject("Scripting.Dictionary")
    With Dic
      .CompareMode = vbBinaryCompare
      '.CompareMode = vbTextCompare '
      .Add "code", "lower"
      .Add "CODE", "UPPER"
    End With
End Sub

4. Ứng dụng

– Lọc loại trùng.

– Tạo dãy số ngẫu nhiên không trùng.

– …

4.1. Một số hàm

Hàm lọc loại trùng cột đầu tiên của một Range:

'//Loc loai trung mot cot'
Function UniqueColumn1D(ByVal Rng As Range) As Variant
    If Rng.Count = 1 Then UniqueColumn1D = Rng.Value: Exit Function
    Dim Dic As Object, i As Long, arr()
    arr = Rng.Value
    Set Dic = CreateObject("Scripting.Dictionary")
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, 1) <> "" And Dic.Exists(arr(i, 1)) = False Then
            Dic.Add arr(i, 1), ""
        End If
    Next i
    UniqueColumn1D = Dic.Keys
End Function

Hàm lọc loại trùng cột đầu tiên cho mảng 2 chiều: 

'//Loc loai trung mang 2 chieu'
Function UniqueArray(ByVal arr As Variant) As Variant
    If IsArray(arr) = False Then Exit Function
    Dim Dic As Object, i As Long
    Set Dic = CreateObject("Scripting.Dictionary")
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, 1) <> "" And Dic.Exists(arr(i, 1)) = False Then
            Dic.Add arr(i, 1), ""
        End If
    Next i
    UniqueArray = Dic.Keys
End Function

4.2. Ví dụ

Cho bảng dữ liệu như dưới. Yêu cầu, căn cứ vào cột [B] – Code để loại loại trùng, kết quả trả về gồm 4 cột dữ liệu:

[No.] là thứ tự danh mục Code,

[Code] là danh mục Code sau khi loại trùng,e

[Date] là ngày ứng với Code đầu tiên tìm thấy, xét từ trên xuống,

[Quantity] là tổng ứng với mỗi [Code] lọc được.

– Code trong Module: 

Sub FilterData()
'Sub loc loai trung theo cot [Code] - côt [B]'
Dim Dic As Object
Dim Rng As Range, i As Long, lRow As Long, ArrData(), Result(), iTmp As String, j As Long
Set Dic = CreateObject("Scripting.Dictionary")
'Gan doi tuong Dictionary vao bien Dic'
With Sheet1
'Xét sheet1'
    lRow = .Range("B" & Rows.Count).End(xlUp).Row
    'Tra ve dong cuoi cung co du lieu thuoc cot [B]'
    ArrData = .Range("B2:D" & lRow).Value2
    'Gan vung du lieu [B2:D & lRow] vao bien mang ArrData'
    lRow = UBound(ArrData, 1)
    'Tra ve kich thuoc chieu thu nhat cua mang ArrData'
    ReDim Result(1 To lRow, 1 To 4)
    'Khai bao cu the so chieu va kich thuoc chieu cho bien mang Result'
    For i = 1 To lRow
    'Xet vong lap bien i chay tu 1 toi lRow
        iTmp = ArrData(i, 1)
        'Gan phan tu (i,1) cua mang ArrData vao bien iTmp
        If iTmp <> "" Then
        'Xet iTmp, neu khac rong thi
            If Not Dic.Exists(iTmp) Then
            'Xet iTmp, neu chua ton tai trong Dic thi
                j = j + 1
                'Tang gia tri cua j len 1 don vi
                Dic.Add iTmp, j
                'Them item co gia tri = j ung voi key = iTmp
                'Truyen ket qua vao bien mang Result:
                Result(j, 1) = j
                Result(j, 2) = iTmp
                Result(j, 3) = ArrData(i, 2)
                Result(j, 4) = ArrData(i, 3)
            Else
            'Nguoc lai: iTmp da ton tai trong Dic thi
                Result(Dic.Item(iTmp), 4) = Result(Dic.Item(iTmp), 4) + ArrData(i, 3)
                'Cong don so luong vao phan tu cua mang Result co chi so (Dic.Item(iTmp), 4)
            End If
        End If
    Next i
    If j > 0 Then
    'Xet j >: Tuc la co ket qua loc
        .Range("H2").Resize(100, 4).ClearContents
        'Xoa du lieu trong vung gan ket qua
        .Range("H2").Resize(j, 4) = Result
        'Gan ket qua xuong bang tinh
    End If
End With
End Sub

Download file mẫu ví dụ nêu trên: Dictionary

Một số kinh nghiệm trong bảng tính Excel

Lê Quốc Thái
-
6
Một số kinh nghiệm trong bảng tính Excel

Trong Excel có nhất nhiều những thủ thuật hay mà chỉ những người đã từng làm mới phát hiện ra vì thế dưới đây https://lequocthai.com muốn giới thiệu với các bạn một số các kinh nghiệm trong Excel.

Chúng tôi sẽ liên tục cập nhật thêm những thủ thuật và kinh nghiệm để chia sẽ cho các bạn sử dụng Excel ngày một chuyên nghiệp hơn, tránh các lỗi lập đi lập lại.

1. Cách giấu số 0 trong Excel 

Tùy thuộc vào mục đích sử dụng, thói quen cũng như sở thích của mình,các bạn có thể sử dụng một trong các phương pháp sau đây để làm biến mất số 0 trong bảng Excel.

– Giấu tất cả số 0 trong bảng tính:

Nhấn vào menu Tools–>Options, chọn thẻ View. Xoá hộp kiểm Zero values.

Có phải tất cả các số 0 trên bàn tính sẽ biến mất? bạn đừng vội lo bởi chỉ có kết quả có giá trị là 0 thực sự mất thôi, còn những số 0 (ví dụ 10) tất nhiên là không sao rồi.

– Dùng dạng số để giấu các số 0, chúng ta thực hiện như sau:

+ Trong hộp Category, chọn Custom. Trong hộp Type, gõ 0;-0;;@.

– Dùng định dạng có điều kiện để giấu số 0 được trả về từ kết quả của công thức:

+ Chọn ô có số 0 cần giấu.

+ Nhấn menu Format–>Conditional Formatting. Ở hộp bên trái chọn Cell Value Is, hộp thứ hai tiếp theo chọn equal to, hộp kế tiếp gõ số 0.

+ Nhấn nút Format, chọn thẻ Font. Trong hộp Color, chọn màu trắng (hay trùng với màu nền của bảng tính). Bấm OK

– Dùng công thức để giấu số 0 hoặc thay bằng dấu gạch nối (-):

Giả sử tại ô A1, A2 bạn có các số tương ứng là 5, 5. Khi lấy A1-A2 thì kết quả sẽ là 0. Bạn có thể dùng các công thức sau để giấu số 0 hoặc thay bằng dấu gạch nối (-):

=IF(A1-A2=0,”-”,A1-A2)

2. Chèn nhiều dòng trong một ô (cell)

Mỗi lần nhấn Enter con trỏ nhảy một phát sang ô khác phía dưới. Làm thế nào để con trỏ nghe lời nằm yên trên ô đang nhập liệu đây? Thủ thuật hết sức đơn giản, bạn chỉ việc nhấn tổ hợp phím (Alt + Enter) thế là bạn có thể viết tiếp trong ô đang làm việc mà dòng lại được ngắt xuống dưới theo ý người dùng mong muốn.

3. Thêm màu, ảnh nền cho toàn bộ bảng tính

Để thêm màu hoặc ảnh nền cho bảng tính, bạn chọn Format –> Sheet –>Background. Tiếp đến, tìm tới ảnh nền (hoặc font màu) bạn thích và nhấn Open.

4. Đóng băng dòng tiêu đề

Trong mỗi bảng tính thường có dòng tiêu đề cố định vị trí cho từng cột . Nếu bạn nhập dữ liệu trong nhiều dòng và tràn quá màn hình, công việc nhập dữ liệu sẽ rất khó khăn vì dòng tiêu đề “trôi” mất. Để “đóng băng” dòng tiêu đề, nhấn chuột chọn một ô ngay dưới dòng tiêu đề; chọn Window > Freeze Panes. Giờ bạn có thể thoải mái nhập dữ liệu với thanh tiêu đề luôn hiển thị ở phía trên bảng tính…

Để thành thạo, sử dụng tốt và khai thác hết tính năng tuyệt vời của chương trình Excel chắc hẳn chúng ta cần phải sử dụng và làm thật nhiều trình soạn thảo này. Hi vọng, với những thao tác đơn giản này có thể giúp ích được cho các bạn trong công việc.

Bài viết liên quan:

DI CHUYỂN TRANG TÍNH EXCEL TRỰC TUYẾN, CHIA SẺ, NHÚNG MÃ TRÊN TRANG WEB VÀ LÀM CHÚNG CÓ KHẢ NĂNG TƯƠNG TÁC

Sách Programming Excel with VBA Flavio Morgado pdf

Lê Quốc Thái
-
6
Sách Programming Excel with VBA Flavio Morgado pdf

Learn to harness the power of Visual Basic for Applications (VBA) in Microsoft Excel to develop interesting, useful, and interactive Excel applications. This book will show you how to manipulate Excel with code, allowing you to unlock extra features, accuracy, and efficiency in working with your data. Programming Excel 2016 with VBA is a complete guide to Excel application development, using step-by-step guidance, example applications, and screenshots in Excel 2016.
In this book, you will learn:How to interact with key Excel objects, such as the application object, workbook object, and range object
Methods for working with ranges in detail using code
Usage of Excel as a database repository
How to exchange data between Excel applications
How to use the Windows API to expand the capabilities of Excel
A step-by-step method for producing your own custom Excel Ribbon Who This Book Is For: Developers and intermediate-to-advanced Excel users who want to dive deeper into the capabilities of Excel 2016 using code.

Download Sách Programming Excel with VBA pdf

Sách Excel 2007 VBA Programmer’s Reference pdf

Lê Quốc Thái
-
5
Sách Excel 2007 VBA Programmer’s Reference pdf
  1. Loạt các bài viết có liên quan chuyên đề VBA:
  2. VBA Excel Hộp thoại thông báo
  3. VBA Excel Biến trong VBA Excel
  4. VBA Excel sử dụng Scripting Dictionary
  5. VBA Excel Biến trong VBA Excel
  6. VBA Excel Workbook, worksheet
  7. VBA Excel Range, Cells
  8. VBA Excel Hàm trong Excel VBA
  9. VBA Excel Events, Application Object
  10. VBA Excel Function and Sub Excel VBA
  11. VBA Excel Array Mảng trong Excel VBA
  12. VBA Excel FileSystemObject trong Excel VBA
  13. VBA Excel Collection trong Excel VBA
  14. VBA Excel Hashtable trong Excel VBA
  15. VBA Excel Stack trong Excel VBA
  16. VBA Excel Queue trong Excel VBA
  17. VBA Excel SortedList Excel VBA
  18. VBA Excel ArrayList Excel VBA
  19. VBA Excel Ví dụ về Scripting Dictionary
  20. Sách VBA Excel 2016 power programming with vba (pdf)

This book is aimed squarely at Excel users who want to harness the power of the VBA language in their Excel applications. At all times, the VBA language is presented in the context of Excel, not just as a general application programming language.

The Primer has been written for those who are new to VBA programming and the Excel object model. It introduces the VBA language and the features of the language that are common to all VBA applications. It explains the relationship between collections, objects, properties, methods, and events and shows how to relate these concepts to Excel through its object model. It also shows how to use the Visual Basic Editor and its multitude of tools, including how to obtain help.

The middle section of the book takes the key objects in Excel and shows, through many practical examples, how to go about working with those objects. The techniques presented have been developed through the exchange of ideas of many talented Excel VBA programmers over many years and show the best way to gain access to workbooks, worksheets, charts, ranges, and so on. The emphasis is on efficiency—that is, how to write code that is readable and easy to maintain and that runs at maximum speed. In addition, the chapters devoted to accessing external databases detail techniques for accessing data in a range of formats.

The final four chapters of the book address the following advanced issues: linking Excel to the Internet, writing code for international compatibility, programming the Visual Basic Editor, and how to use the functions in the Win32 API (Windows 32-bit Application Programming Interface).

Download Excel 2007 VBA Programmer’s Reference

Cách dùng hàm SumProduct và Công thức mảng

Lê Quốc Thái
-
3
Cách dùng hàm SumProduct và Công thức mảng
  1. Loạt các bài viết có liên quan chuyên đề VBA:
  2. VBA Excel Hộp thoại thông báo
  3. VBA Excel Biến trong VBA Excel
  4. VBA Excel sử dụng Scripting Dictionary
  5. VBA Excel Biến trong VBA Excel
  6. VBA Excel Workbook, worksheet
  7. VBA Excel Range, Cells
  8. VBA Excel Hàm trong Excel VBA
  9. VBA Excel Events, Application Object
  10. VBA Excel Function and Sub Excel VBA
  11. VBA Excel Array Mảng trong Excel VBA
  12. VBA Excel FileSystemObject trong Excel VBA
  13. VBA Excel Collection trong Excel VBA
  14. VBA Excel Hashtable trong Excel VBA
  15. VBA Excel Stack trong Excel VBA
  16. VBA Excel Queue trong Excel VBA
  17. VBA Excel SortedList Excel VBA
  18. VBA Excel ArrayList Excel VBA
  19. VBA Excel Ví dụ về Scripting Dictionary
  20. Sách VBA Excel 2016 power programming with vba (pdf)

Hàm SumProduct:
Cấu trúc SUMPRODUCT(array1,array2,array3, …)
Array – Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng nào đó. VD A1:C1 hoặ A1:A10,…

Phép tính này cho phép chúng ta tính tổng của tích array1*array2*array3* …array30.
VD: A: Số lượng; B: Đơn giá
A1 =2 B1=20 C1=”Cam” D1=”Giống lai”
A2 =3 B2=10 C2=”Bưởi” D2=”Không”
A3 =4 B3=25 C3=”Cam” D3=”Không”

Bây giờ cần tính doanh thu của các loại hoa quả
array1=A1:A3
array2=B1:B3
Công thức =SumProduct(A1:A3, B1:B3) = 170
Bản chất công thức làm việc như thế này =A1*B1+A2*B2+A3*B3 kết quả là 170
Nhắc lại về phép tính logic:
Giá trị kiểu logic chỉ cho ra 1 trong 2 giá trị là TRUE/1, FALSE/0
Phép toán logic:<, >, <>, =, >=, <=, Not()
VD:
2>3=False
3>1=True
4>3=True
*) Logic và – AND
=(2>3)*(3>1)*(4>3)=False*True*True=0*1*1=False/0 tương đương với hàm AND(2>3,3>1,4>3). Ít nhất một logic=False thì kết quả sẽ là False hay 0.
* Logic hoặc – OR
=(2>3)+(3>1)+(4>3)=False+True+True=0+1=True/1 tương đương với hàm OR(2>3,3>1,4>3). Ít nhất một logic=True thì kết quả sẽ là True hay 1.
Lưu ý tổng của các giá trị là True=True=1).

*) Tính tổng có nhiều điều kiện:
Cách 1: dùng SUMPRODUCT
Tính tổng doanh thu của loại là “Cam”
=SUMPRODUCT(A1:A3,B1:B3*(C1:C3=”Cam”)) hoặc =SUMPRODUCT(A1:A3*B1:B3*(C1:C3=”Cam”)) = 140
Công thức tính như sau:
=A1*B1*(C1=”Cam”)+A2*B2*(C2=”Cam”)+A3*B3*(C3=”Cam”)
=2*20*True+3*10*False+4*25*True
=2*20*1+3*10*0+4*25*1= 140
Cách 2: dung Công thức mảng – “Formula Array”
=Sum(IF(C1:C3=”Cam”,A1:A3*B1:B3,0))
Kết thức nhẫn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
Xét trên từng dòng trong mảng (array)
dòng1: (c1=”Cam”)=true nên lấy A1*B1=2*20
dòng2: (c2=”Cam”)=false nên lấy 0 (theo cách của lấy của hàm IF)
dòng3: (c3=”Cam”)=true nên lấy A3*B3=4*25
Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20+0+2*25=140. Nếu trong công thức là hàm khác hàm SUM thì cách tính sẽ theo hàm đó.

Như vậy có 2 cách tính:

=SUMPRODUCT(A1:A3*B1:B3*(C1:C3=”Cam”)) và
=Sum(IF(C1:C3=”Cam”,A1:A3*B1:B3,0))

*) Vậy tại sao không dùng là =SUM(A1:A3*B1:B3*(C1:C3=”Cam”))
mà phải dùng hàm =SUMPRODUCT(A1:A3*B1:B3*(C1:C3=”Cam”)) ?

Các bạn nhớ lại cấu trúc của SUM là
SUM(number1,number2, …)
Còn SUMPRODUCT là
SUMPRODUCT(array1,array2,array3, …)
number <> array

Nếu SUMPRODUCT(A1:A3*B1:B3*(C1:C3=”Cam”)) rồi ENTER là đúng vì đối số của nó phải là mảng – Array.

Nếu công thức =SUM(A1:A3*B1:B3*(C1:C3=”Cam”)) rồi ENTER kết quả là #VALUE! -lỗi vì A1:A3 là một array chứ không phải là một number.

Nếu nhấn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
Xét trên từng dòng trong mảng (array)
dòng1: A1*B1*(c1=”Cam”)=2*20*True=2*20*1
dòng2: A2*B2*(c2=”Cam”)=3*10*False=3*10*0
dòng3: A3*B3*(c3=”Cam”)=2*25*True=4*25*1

Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20*1+3*10*0
+4*25*1=140.

Vậy vẫn dùng được =SUM(A1:A3*B1:B3*(C1:C3=”Cam”)) với điều kiện nhấn tổ hợp phím CTRL+SHIFT+ENTER

Như vậy đến đây chúng ta có có 3 cách tính:

=SUMPRODUCT(A1:A3*B1:B3*(C1:C3=”Cam”)) nhấn phím ENTER
=SUM(IF(C1:C3=”Cam”,A1:A3*B1:B3,0)) nhấn phím CTRL+SHIFT+ENTER
=SUM(A1:A3*B1:B3*(C1:C3=”Cam”)) nhấn phím CTRL+SHIFT+ENTER

Chúng có thể kết hợp rất nhiều điều kiện vào trong hàm thông qua phép toán logic nhân-và- And, cộng – hoặc – Or.

*) Dùng hàm SUMPRODUCT hay dùng SUM kết hợp CTRL+SHIFT+ENTER đều cho ra được kết quả như nhau chính là do phép toán logic của bạn.
*) Hàm SUMPRODUCT chỉ có thể tính tổng theo nhiều điều kiện
*) Công thức mảng – Formula Array ngoài việc tính tổng có nhiều điều kiện còn làm rất nhiều phép tính khác do cách sử dụng hàm mà thôi.

Theo Nguyễn Duy Tuân – GPE

1...199200201...203Trang 200 của 203

TĂNG TỐC MẠNG KEY 1.1.1.1

TÔI CHỌN VBA EXCEL

CATEGORY

About Lê Quốc Thái

Yep! I am Le Quoc Thai famous as tnfsmith, one among of netizens beloved internet precious, favorite accumulate sharing all my knowledge and experience Excel, PC tips tricks, gadget news during over decades working in banking data analysis. I am founder AI era: aiphogpt.com , ainextbrain.com

Liên hệ: [email protected]|[email protected]|[email protected]

Follow Lê Quốc Thái

© Cared by LeQuocThai.Com - VBA Excel, APK Mode