Sổ tay về các hàm tự tạo

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
BÀI 1: TẠO DANH SÁCH KHÁCH HÀNG TÙY THUỘC VÀO VIỆC CHỌN ĐỊA FƯƠNG (QUẬN, HUYỆN)​

Giả dụ chúng ta có danh sách khách hàng ở nhiều quận huyện khác nhau của tỉnh Bà Rịa - Vũng tàu
Giờ ta muốn lập danh sách khách hàng ở thành phố Vũng tàu hay huyện Châu Đức, . . . thì ta thực thi như trong file đính kèm:

:cool:
 

File đính kèm

  • gpeUDF.rar
    12,4 KB · Đọc: 290

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
BÀI 2: Lập danh sách nhân viên theo từng đơn vị được chọn

/(/hư bài trên ta thấy, tác giả đã viết ra hàm mảng tự tạo để nó đưa ra danh sách theo iêu cầu;

Vì là hàm mảng, dù có tự tạo đi chăng nữa, chúng ta vẫn fải kết thúc bằng cách dùng tổ hợp 3 fím

{CTRL}+{SHIFT}+{ENTER}

Sau đây là nội dung của hàm tự tạo dùng để lập danh sách các cá nhân trong 1 đơn vị (bộ fận) của 1 cơ quan
PHP:
Option Explicit
Function LapDS(TenDV As String, Rng As Range)
 ReDim Arr(1 To 30, 1 To 1)
 Dim sRng As Range, Cls As Range, Rg0 As Range, J As Long
  
 Set sRng = Rng.Find(TenDV, , xlValues, xlWhole)
 If Not sRng Is Nothing Then
    Set Rg0 = sRng.Resize(Rng.Rows.Count)
    
    For Each Cls In Rg0
        If Cls.Value = "" Then Exit For
        If Cls.Value = TenDV Then
            J = 1 + J
            Arr(J, 1) = Cls.Offset(, 1).Value
        End If
    Next Cls
 Else
    LapDS = "Nothing"
 End If
 LapDS = Arr()
End Function
 

File đính kèm

  • gpeUDF.rar
    10,4 KB · Đọc: 212

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
BÀI 3: Tính thưởng theo sản lượng hàng bán & số ngày công trong tháng của từng nhân viên

Ở cơ quan (CQ) nọ có qui chế tính thưởng như sau:
Đinh mức bán hàng của tháng là 45 thùng & 26 ngày công; Thưởng theo định mức này là 2 triêu;

Nếu thực hiện dưới định mức:
Hệ số K1 sẽ là tỉ số thùng bán được /45 & K2 là tỉ số ngày công thực tế/26 đảo ngược
Lấy 2 hệ số này nhân với 2 triệu sẽ là tiền thưởng của người đó;
Ví dụ: NV 1 bán được 40 thùng trong 26 ngày công sẽ có mức thưởng 2 triệu * (40/45)
NV 2 bán được 45 thùng trong 24 ngày thì tiền thưởng sẽ là 26/24 * 2 triệu

Nếu thực hiện trên mức doanh số thì:
(+) Dưới 101 thùng thưởng 10.000đ/thùng
(+) Dưới 200 thùng thưởng 18.000đ/thùng
(+) trên số này thì 20.000đ/thùng

Hàm người dùng có nội dung như sau:

PHP:
Option Explicit
Function TinhLuong(SoLg As Double, Cong As Double)
 Dim TThuong As Double
 
 TThuong = 10 ^ 3 * SoLg * Switch(SoLg <= 45, 0, SoLg <= 100, 10, SoLg <= 200, 18, SoLg > 200, 20)
 If SoLg >= 45 And Cong >= 26 Then
    TinhLuong = 2 * 10 ^ 6
 Else

 End If
 TinhLuong = TinhLuong + TThuong
End Function
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
BÀI 4: Lập danh sách quà thưởng dịp vui xuân.

Ở CQ (cơ quan) nọ tổ chức sổ số vui xuân để nhận quà;
Danh sách sau sổ số có quà như sau

PHP:
MaNV |Ho Ten|...|Quà
NTX01|Lê Hòa|...|Áo mưa
NVH02|Ngô An|...|Mũ mềm
BXT00|Vũ Áng|...|Sửa
NTX01|Lê Hòa|...|Áo Sơ mi
NVH02|Ngô An|...|Găng tay
BXT00|Vũ Áng|...|Kẹo

Nhiệm vụ của ta là viết hàm người dùng để tại 1 ô Validation (như [I3]) ta chọn mã NV nào đó, thì hàm này sẽ liệt kê các món quà mà người ấy đã nhận qua đợt quay số vui xuân của CQ

Mời các bạn xem trong file sẽ rõ hơn.
 

File đính kèm

  • gpeUDF.rar
    7,5 KB · Đọc: 175
Last edited by a moderator:

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
/-(àm dò tìm nhiều kết quả thỏa cùng điều kiện

Giả dụ ta có dữ liệu tháng & năm ký kết hợp đồng với các công ti ở các tỉnh thành, vùng miền khác nhau như ví dụ sau

PHP:
Tháng| Năm|Mã CTi|'Địa fương'
  12 |2013|BGI056|Bặc liêu
   08|2013|GHF12 |Cao Bằng
   10|2013|MNM2Z |Đà Nẵng
   02|2013|GGfKA2|Tp. HCM
Giờ mình muốn dò tìm tên công ty và địa điểm dựa trên tháng và năm ký hợp đồng, thì mình phải dùng hàm nào ạ!
Ví dụ: cùng tháng, cùng năm ký hợp đồng có 2 công ty tên khác nhau và địa điểm khác nhau thì dùng hàm nào để dò ra được. Hoặc dùng cách nào được
Mong mọi người giúp đỡ!
Nội dung hàm người dùng sẽ như dưới đây:
PHP:
Option Explicit:                        Option Base 1
Function LietKe(CSDL As Range, Tháng As Byte, Nam As Integer)
 Dim Rws As Long, J As Long
 Dim Rng As Range, sRng As Range
 
 Rws = CSDL.Rows.Count
 ReDim Arr(Rws, 4)
 Set Rng = CSDL(1).Resize(Rws)
 Set sRng = Rng.Find(Tháng, , xlFormulas, xlWhole)
 If Not sRng Is Nothing Then
    Set sRng = Range(sRng, Rng(1).Offset(Rws))
    For Each Rng In sRng
        With Rng
            If .Value = Tháng And .Offset(, 1).Value = Nam Then
                J = J + 1
                Arr(J, 3) = .Offset(, 2).Value:     Arr(J, 1) = Tháng
                Arr(J, 4) = .Offset(, 3).Value:     Arr(J, 2) = Nam
            End If
        End With
    Next Rng
 End If
 LietKe = Arr()
End Function
 

File đính kèm

  • gpeUDF.rar
    8,6 KB · Đọc: 193

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Hàm tự tạo để tính giá trị của 1 biểu thức DValue


Đôi khi ta có những biểu thức có số lẫn chữ và ta muốn tính giá trị của biểu thức đó. Ta dùng hàm tự tạo DValue, cú pháp : DValue(Biểu_Thức)

Ví dụ :

- - - - -A - - - - - - - - - - - - - - - B - - - - - - - - giá trị trả về
1.- - "6 cái x 10đ" - - - - - - - - =DValue(A1) - - - - - - 60
2.- - "5m x 20m" - - - - - - - - - =DValue(A2) - - - - - 100
3.- - "(2hàng+3hàng)*10người" -- =DValue(A3) - - - - - 50
4.- - "60người : 2 xe" - - - - - - =Dvalue(A4) - - - - - - 30

Phép tính nhân có thể là dấu "*" hay "X"
Phép tính chia có thể là dấu "/" hoặc ":"

/(/ội dung hàm như sau:
PHP:
Public Function DValue(Expr)
    Char = Expr
    Sent = Space(0)
    ABC = "0123456789+-*/()." & Space(1)
    XYZ = "0123456789" & Space(1)
    For m = 2 To 3
        Met = "m" & m
        Temp = InStr(1, Char, Met)
        Do While Temp > 0
            If Temp > 0 Then
                Char = Left(Char, Temp) & Mid(Char, Temp + 2)
            End If
            Temp = InStr(1, Char, Met)
        Loop
    Next
    For i = 1 To Len(Char)
        KyTu = Mid(Char, i, 1)
        If InStr(1, ABC, KyTu) > 0 Then
            Sent = Sent & KyTu
        Else
            Select Case KyTu
                Case ":"
                    Left_ = Mid(Char, i - 1, 1)
                    Right_ = Mid(Char, i + 1, 1)
                    If InStr(1, XYZ, Right_) > 0 Then
                        Sent = Sent & "/"
                    End If
                Case ","
                    Sent = Sent & "."
                Case "%"
                    Sent = Sent & "/100"
                Case "x", "X"
                    Left_ = Mid(Char, i - 1, 1)
                    Right_ = Mid(Char, i + 1, 1)
                    If InStr(1, XYZ, Right_) > 0 Then
                        Sent = Sent & "*"
                    End If
            End Select
        End If
    Next
    DValue = Eval(Sent)
End Function
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Hàm tìm đơn giá gần đây nhất

Mình có 1 bảng tính Excel gồm 3 cột : Ngày nhập, tên hàng , Đơn giá nhập. Mình thường xuyên phải nhập hàng ,mà mổi khi nhập hàng thì đơn giá lại khác . Mình muốn biết dùng hàm gì để dò lấy được đơn giá nhập của ngày gần đây nhất . Nhưng lưu ý khi mình sort dữ liệu thì công thức vẫn đúng . Túm lại 1 cách ngắn gọn là làm sao dò tìm được đơn giá nhập gần đây nhất theo thơì gian nhập

( ví dụ 14/7/2014 12:30 nhập hàng với đơn giá nhập là 5000 đ, 14/7/2014 13:00 nhập hàng với đơn giá nhập là 6000 đ thì lấy là 6000đ )

PHP:
Option Explicit
Function MaxDay(TenHH As String, Rng As Range)
 Dim sRng As Range
 Dim Dat As Date, Tmp As Double
 
 Dat = Application.WorksheetFunction.Min(Rng.Offset(, -1))
 Set sRng = Rng.Find(TenHH, , xlFormulas, xlWhole)
 If Not sRng Is Nothing Then
    Set Rng = sRng.Resize(Rng.Rows.Count)
    For Each sRng In Rng
        If sRng.Offset(, -1).Value > Dat And sRng.Value = TenHH Then
            Dat = sRng.Offset(, -1).Value
            Tmp = sRng.Offset(, 1).Value
        End If
    Next sRng
 End If
 MaxDay = Tmp
End Function
 

File đính kèm

  • gpeUDF.rar
    7,8 KB · Đọc: 195

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm tổng thời gian nghĩ chế độ các loại

Ở CQ nó có thực hiện việc chấm công nghĩ chế độ theo dạng như trong hình kèm theo:

CongNghi.JPG

Nhiệm vụ đề ra là tính tổng số giờ công nghĩ loại B2.4 + B3.3 hay C2.3 + C2.3
(Ổ đây, do qui ước của CQ nó, B2.4 là nghĩ loại B với thời gian là 2g 40 fút; C2.3 là nghĩ loại C 2h 30 fút)

Như vậy B2.4 + B3.3 => 5g 70 fút =>> 6g10 fút Số này chia cho 8 giờ/ngày sẽ ra là 0.77 ngày

Sau đây là nội dung hàm:

PHP:
Option Explicit
Function GCong(Rng As Range, Optional Loai As String = "B") As Double
 Dim Cls As Range, VTr As Byte, Tmp As Double
  
 For Each Cls In Rng
    If Not IsNumeric(Cls.Value) Then
        If Left(Cls.Value, 1) = Loai And Len(Cls.Value) > 1 Then
            VTr = InStr(Cls.Value, ".")
            If VTr < 1 Then
                Tmp = Tmp + CDbl(Mid(Cls, 2, 5))    '?'
            Else
                Tmp = Tmp + _
                    TimeSerial(CInt(Mid(Cls.Value, 2, VTr - 2)), CInt(Mid(Cls.Value & "0", VTr + 1, 2)), 0)
            End If
        End If
    End If
 Next Cls
 GCong = (Hour(Tmp) * 60 + Minute(Tmp)) / 480
End Function
Các bạn xem cụ thể file đính kèm tại bài #7 tại http://www.giaiphapexcel.com/forum/showthread.php?95421-Sum-tổng-giờ-và-phút-sau-khi-tách-từ-chuỗi

Chúc ngủ ngon!
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Mã:
Em Có dãy mã số như dưới đây dùng để quản lí nhân viên trong 1 cơ quan;
MaNV | Mã ĐV
S0005|KHVT
S0007|TCKT
S0008|LĐTL
S0009|BGĐ
S????|KTh
Làm sao để em có thể tự động nhập được mã số tiếp theo sẽ là S0010, 
Nhờ các anh chị giúp em. Trân trọng cảm ơn

Hàm tự tạo của bạn đây:
PHP:
Function MaNV(Ma As String) As String
 MaNV = Left(Ma, 1) & Right("000" & CStr(CLng(Mid(Ma, 2, 5) + 1)), 4)
End Function

& xem thêm tại: http://www.giaiphapexcel.com/forum/showthread.php?95436-Xác-định-mã-số-tiếp-theo
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm học viên trong trường để đóng học fí

Hiện tại em đang có 1 vấn đề mong được các anh/chị hướng dẫn để em áp dụng vào công việc.
Trên file em đang cần tìm số phải thu khi nhập tên, SBD và Ngày sinh (nếu có).
Theo cách hiểu của em là khi nhập 3 dữ liệu trên sẽ tự đối chiếu để đưa ra kết quả chính xác nhất, tên học viên thì chắc chắn có nhưng SBD và ngày sinh có thể bị thiếu.
Do bảng dữ liệu em để 1 sheet và bảng nhập thông tin ở 1 sheet khác nên em chỉ copy 1 đoạn ngắn để làm ví dụ ạ !

Trích file có nội dung như sau:
PHP:
 STT| SBD |Ho Ten HV |Ngày Sinh|Fải thu
 001|N1789|Hồ Văn Ca |21/04/94 |150.000
 002|.. ..|Lê Hải Mĩ |04/11/94 |220.000
 003|N2358|Lê Thị Hoa|14/09/93 |200.000
 004|.. ..|Lê Thị Hoa|.  . . . |240.000  
 005|.. ..|Lê Thị Hoa|02/08/93 |  0 
 006|N3250|Lê Văn Ba |25/04/93 |240.000
 007|. ...|Lê Văn Ba |12/05/94 |150.000
 008|N8971|Võ An Thu | . . . . |150.000

/-(àm tự tạo các bạn có thể xem tại: http://www.giaiphapexcel.com/forum/showthread.php?95442-Em-cần-giúp-tìm-kiếm-và-tham-chiếu-dữ-liệu (bài 2)
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Fân tách 1 đoạn văn bản thành 2 có độ dài xấp xỉ bằng nhau

Mình có 1 đề bài yêu cầu thế này.
Giả sử mình có 1 chuỗi: "Hàm tự tạo chia đôi chuỗi ký tự"
Mình muốn tách chuỗi trên thành 2 chuỗi có độ dài tương đương nhau (độ dài chênh lệch nhau ít nhất)
Ví dụ sau khi tách được 2 chuỗi:
Chuỗi A: "Hàm tự tạo chia"
Chuỗi B: "đôi chuỗi ký tự"

/(/ội dung hàm tự tạo đó như sau:

PHP:
Option Explicit
Function ChiaDoi(StrC As String, Optional Dau As Boolean = True) As String
 Dim J As Integer, VT0 As Byte, Tmp As Byte
  
 VT0 = Len(StrC) \ 2
 If Mid(StrC, VT0, 1) = " " Then
    ChiaDoi = GPE(StrC, VT0, Dau)
 Else
    For J = 1 To Len(StrC)
        If Mid(StrC, VT0 - J, 1) = " " Then
            ChiaDoi = GPE(StrC, VT0 - J, Dau)
            Exit Function
        ElseIf Mid(StrC, VT0 + J, 1) = " " Then
            ChiaDoi = GPE(StrC, VT0 + J, Dau)
            Exit Function
        End If
    Next J
 End If
End Function
Mã:
[B]Function GPE(StrC As String, VTr As Byte, Optional Dau As Boolean = True)[/B]
 If Dau Then
    GPE = Left(StrC, VTr - 1)
 Else
    GPE = Mid(StrC, VTr + 1, Len(StrC))
 End If
[B]End Function[/B]

http://www.giaiphapexcel.com/forum/showthread.php?95722-Tách-1-chuỗi-thành-2-chuỗi-dài-tương-đương-nhau-(độ-dài-chênh-lệch-nhau-ít-nhất)
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tôi có dữ liệu tại cột [C] như dưới đây:
PHP:
 C               [B]'Kết quả mong muốn'[/B]
 Anh            Tên 1 Số nhập  Tên 2
 1                Anh   1      Bình
 Bình             Ca    2      Dương
                  Em    3      Giương          
 Ca               . . .   ..     . . .           
 2               
Dương          (Có nghĩa là:
                  'Nhập 1 số ở cột số nhập'
Em               'Thì ô bên trái & fải cùng dòng'      
 3                'sẽ hiện 2 tên trong cột [C],'
Giương          'mà ô bên trái chứa tên của dòng trên    '
. . . . .          'còn ô kia là tên thuộc dòng dưới nó)'
. . .

Nội dung của hàm tự tạo cho kết quả đáp ứng iêu cầu sẽ là:
PHP:
Function TimCap(Rng As Range, Num As Long, Optional Tren As Boolean = True)
 Dim sRng As Range
    Set sRng = Rng.Find(Num, , xlFormulas, xlWhole)
       If Not sRng Is Nothing Then
          If Tren Then
              TimCap = sRng.End(xlUp).Value
          Else
              TimCap = sRng.End(xlDown).Value
          End If
       End If
End Function
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Viết hàm tự tạo để tách 2 loại tên hàng & số lượng hàng trong chuỗi liên tục

Mình có các chuỗi
Tên hàng1 [SL1], Tên hàng2 [SL2],. . . .
đang ở cột [A], như sau:

Bưởi [1], đu đủ [5], nho [1]
nho [2], đu đủ [1], Xoài [1],
nho [2], Bưởi [1], đu đủ [1], táo [1]

Giờ mình muốn tách ra thành bảng 100 cột, trong đó cột đầu chứa tên hàng hóa (trái cây) & cột thứ hai là số lượng của mặt hàng đó, như
PHP:
Bưởi  | 1 |đu đủ | 5 | Nho  | 1 |      |   |
Nho   | 2 |đu đủ | 1 |Xoài  | 1 |      |   |
Nho   | 2 |Bưởi  | 1 |đu đủ | 1 | Táo  | 1 |

Việc này sẽ được thỏa mãn với hàm mãng tự tạo, như sau:

PHP:
Option Explicit
Function Tach(StrC As String)
 ReDim Arr(1 To 1, 1 To 100)
 Const MN As String = "[":          Const DN As String = "]"
 Dim j As Integer, VTr As Integer, VT2 As Integer
 
 StrC = Trim(StrC) & MN
 Do
    VTr = InStr(StrC, MN)
    
    If VTr > 1 Then
        j = j + 2
        Arr(1, j - 1) = Trim(Left(StrC, VTr - 1))
        VT2 = InStr(VTr, StrC, DN)
        Arr(1, j) = Mid(StrC, VTr + 1, VT2 - VTr - 1)
        StrC = Mid(StrC, VT2 + 2, 99)
    Else
        Exit Do
    End If
 Loop
 Tach = Arr()
End Function

Các bạn sẽ rõ hơn, khi xem tại http://www.giaiphapexcel.com/forum/showthread.php?97000-Giúp-code-lấy-tên-hàng-và-số-lượng
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
/_ập danh sách em học sinh của lớp có kết quả điểm cao nhất trong toàn trường

Ta có dữ liệu thống kê điểm kiểm tra theo từng môn học của các em trong toàn trường như sau:
PHP:
Lớp | HoTen |M1 | M2 | M3  | Tổng
6A1 |LT Lan |23 |  29| 37.5|10.14
6A1 |LV Lân |29 |  22| 31.5|20.45
6A1 |DT Vân |33 |  19| 27.6|15.14
6A1 |LV Cần |22 |22.1| 31  |26.45
.. . . .

Ta cần hàm người dùng lập cho ta danh sách như sau:
PHP:
Lớp | HoTen |M1 | M2 | M3  | Tổng
6A1 |LV Cần |22 |22.1| 31  |26.45
6A2 |NT Lan |23 |  29| 37.5|20.14
6A5 |L Uyên |29 |  22| 31.5|24.45
7A1 |NT Vân |33 |  19| 27.6|25.14
.. . . .

Kết quả ở bảng 2 là do hàm tự tạo này đem lại:
PHP:
Option Explicit
Function DiemCN(MaLop As String, Rng As Range, cRit As Range)
 ReDim Arr(1 To 1, 1 To 5)
 Dim WF As Object, Sh As Worksheet, Cls As Range
 Dim Max_ As Double
 
 Set Sh = ThisWorkbook.Worksheets("Diem")
 Set WF = Application.WorksheetFunction
 Max_ = WF.DMax(Rng, Rng(6), cRit)
 Arr(1, 5) = Max_
 For Each Cls In Rng(6).Resize(Rng.Rows.Count)
    If Cls.Value = Max_ And Cls.Offset(, -5).Value = MaLop Then
        Arr(1, 1) = Cls.Offset(, -4):   Arr(1, 2) = Cls.Offset(, -3)
        Arr(1, 3) = Cls.Offset(, -2):   Arr(1, 4) = Cls.Offset(, -1)
    End If
 Next Cls
 DiemCN = Arr()
End Function
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Em có một dãy tên hàng, em muốn tìm số lượng nhỏ nhất, nhưng số đố phải lớn hơn 0. Bác nào giúp em với nhé. mời các bác xem ví dụ trong file đính kèm. tại http://forum.webketoan.vn/threads/2744777-tim-gia-tri-lon-hon-0-gan-nhat-cua-mot-vung-trong-excel/
PHP:
Option Explicit
Function SoLonHon0(CSDL As Range, Ten As String)
 Dim J As Long, Max_ As Double
 Dim Rng As Range, Cls As Range
 
 Set Rng = CSDL(1).Offset(, 1).Resize(CSDL.Rows.Count)
 Max_ = Application.WorksheetFunction.Max(Rng) + 0.9
 For Each Cls In CSDL
    With Cls.Offset(, 1)
        If Cls.Value = Ten And .Value < Max_ And .Value > 0 Then
            Max_ = Cls.Offset(, 1).Value
        End If
    End With
 Next Cls
 SoLonHon0 = Max_
End Function
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Lập cột nhắc ngày sinh nhật của khách hàng trong tuần

Công ti nọ có danh sách những khách hàng quan trọng (VIP)
Người ta muốn lập hàm để nhắc người quản lí một khi khách hàng nào đó sắp đến ngày sinh nhật trong tuần theo biểu sau:
PHP:
CTi     Họ Tên       NgàySinh   Loại     SĐT    'Ngày'
CTi A Nguyễn Văn An 25/11/1976  VIP01  123456987   2
CTi B Trần Thành Bé 20/11/1967  VIP01  127496987   0
CTi A Ngô Thanh Ang 27/11/1956  VIP02  193456987   4
CTi B Huỳnh Văn Bay 26/11/1980  VIP01  127496987   3
CTi A Trần Lê Vy An 29/11/1984  VIP01  293456987   6
CTi B Nguyễn Thị Nữ 28/11/1985  VIP02  827496987   5

Để có số liệu trong cột [Ngày] đó, ta cần dùng hàm tự tạo với nội dung sau:
PHP:
Option Explicit
Function NgaySN(NSinh As Date)
 Dim J As Byte, Dat As Date
 
 For J = 1 To 7
    Dat = Date + J
    If SNTuan(NSinh) = SNTuan(Dat) Then
        NgaySN = J:         Exit Function
    End If
 Next J
End Function
Mã:
[b]
Function SNTuan(Dat As Date)[/b]
 Const Chu As String = "123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
 SNTuan = Mid(Chu, 10 + Month(Dat), 1) & Mid(Chu, Day(Dat), 1)
[b]End Function[/b]

http://www.giaiphapexcel.com/forum/showthread.php?98601-Giúp-code-nhắc-sinh-nhật-khách-hàng
 
Last edited by a moderator:

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Tìm fần đặt tính của nhóm các số liên tục

Giả sử ta có các trị số 0 & 1 rãi từ [B1] cho đến [AZ1] như dưới đây:
PHP:
B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA. . ..    AZ
0 0 0 1 0 0 0 0 1 1 0 1 1 0 0 0 1 1 0 0 1 1 1 . . . .     (Dòng 1)
            9 0 0 8 1 2 8 1    . . . . . . . . .  (Dòng 2: fần đặt tính thỏa điều kiện ghi bên dưới)
(Nhóm các số 0 liên tục cao nhất chỉ là 5 số)​

Nhiệm vụ đề ra là ta đi tìm fần đặt tính của nhóm số liên tục từ 7 ô liên tiếp nhau;
Fần đặc tính này chỉ gồm các kí số: 0, 8, 3, 9 & 1; Nhưng chúng thỏa các iêu cầu sau

Ví dụ ta xét con số đặt tính 9 ở [H2]:
Nó là con số 9 một khi tổng 7 các con số ở dòng 1 trên & về fía trái nó tạo thành như sau:
[B1]* 1000.000 + [C1]*100.000 + . . . . = 1000 (vì 3 số đầu là những con số 0)
Trường hợp tại [K2] có số đặt tính là 8 là do tổng tạo thành là 1000011
(Con số 1 hàng triệu ứng với con số đang ở [E1])

Các số đặt tính này thỏa điều kiện như sau:

Là 9, nếu tổng có số tận cùng là 1.001 hay 1.000;

Là 3 nếu có tận cùng của tổng là 101 hay 100;

Sẽ là 8 nếu tận cùng của tổng là 11

& là 1 nếu tồng có tận cùng là 10

Còn lại các trường hợp khác sẽ có số đặc tính là 0

Hàm tự tạo sau sẽ giúp ta làm việc đó:

PHP:
Option Explicit
Function TraSo(Rg7 As Range)
 Dim Cls As Range
 Dim J As Byte, Tmp As Long
 
 J = 1
 For Each Cls In Rg7
    Tmp = Tmp + Rg7(J) * 10 ^ (7 - J)
    J = J + 1
 Next Cls
 If Tmp Mod 10000 = 1001 Or Tmp Mod 10000 = 1000 Then TraSo = 9
 If Tmp Mod 1000 = 101 Or Tmp Mod 1000 = 100 Then TraSo = 3
 If Tmp Mod 100 = 11 Then TraSo = 8
 If Tmp Mod 100 = 10 Then TraSo = 1
 
End Function

http://www.giaiphapexcel.com/forum/...iúp-Công-thức-thay-thế-vì-dùng-hàm-IF-rất-dài
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
/-(àm tự tạo ra mã số hóa đơn theo qui luật tăng dần.

Ở cơ quan (CQ) nó có qui ước tạo ra mã số theo dạng #A#-DDMMYY
Ở đây # biểu thị cho 1 kí số nào đó, còn 'A' l1 1 chữ cái nào đó; DDMMYY là các con số chỉ ngày tháng năm lập hóa đơn;

Ví dụ ngày 1/12/2014 đã lập hóa đơn ngay trước đó là 0C3-011214
Thì hóa đơn kế tiếp trong ngày đó sẽ là 0C4-011214

Tiếp sau hóa đơn có mã 0A9-011214 sẽ là 0B0-011214
. . . .
Tiếp theo hóa đơn mã 0Z9-031214 sẽ là 1A0-031214

(Mã hóa đơn hóa đơn cứ tăng theo fần 3 kí tự (hay kí số) đầu & ghi nhận ngày ra hóa đơn)

Hàm tự tạo có nội dung sau:

PHP:
Option Explicit
Function SoTT(MaSo As String, Optional Dat As Date)
 Const Alf As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
 Dim SoT1 As String, SoT3 As String, SoT2 As String, Tmp As String
 Dim VTr As Byte
 
 If Dat = 0 Then Dat = Date
 Tmp = "-" & Right("0" & CStr(Day(Dat)), 2) & Right("0" & CStr(Month(Dat)), 2) & CStr(Year(Dat) Mod 100)
 SoT3 = Mid(MaSo, 3, 1)
 If SoT3 < "9" Then
    SoTT = Left(MaSo, 2) & CStr(CByte(SoT3) + 1) & Tmp
 Else
    SoT2 = Mid(MaSo, 2, 1)
    If SoT2 < "Z" Then
        VTr = InStr(Alf, SoT2)
        SoTT = Left(MaSo, 1) & Mid(Alf, VTr + 1, 1) & "0" & Tmp
    Else
        If Left(MaSo, 1) = "0" Then
            SoTT = "1A0" & Tmp
        Else
            SoT1 = Left(MaSo, 1)
            SoTT = CStr(CByte(SoT1) + 1) & "A0" & Tmp
        End If
    End If
 End If
End Function

Tài liệu tham khảo: http://www.giaiphapexcel.com/forum/...u-có-cấu-trúc-tự-động-theo-ngày-vd-0A1-011214
 

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
Hàm tự tạo đưa ra kết quả của ô lựa chọn (Validation) & làm nguồn cho ô lựa chọn tiếp theo

Giải thích tiêu đề theo file đính kèm:

Ở 1 trang tính ta có bảng liệt kê các mặt hàng theo chủng loại của nó, như

Lương thực | Thịt | Thủy sản | Rau | Củ . . .| Quả | Trái cây
Gạo tẻ . . . | Heo. .|Cá mè . . .|Muống |Đậu . . .|Ớt . .|Chôm chôm
Nàng thơm .|. Gà. .|Bông lau. .|Dền. . |Cải. . . . |Ổi.. .|Sầu riêng
Nếp cẩm . .| Vịt . .|Mực. . . . .|Bắp cải|Cà rốt . .|Khế |Nhót
Nếp than . .|Bò. . .|Ếch . . . . |Su su. .|Khoai tây|Gất | Mận
Chợ Đào. . .|Dê. . .|Lươn. .. . .|Mướp. .| . . . . . .|. . . | . . .

Ở đây hàng in đậm thể hiện chũng loại mặt hàng; Dưới nó là các mặt hàng thuộc chũng loại đó.
Hơn nữa, các loại mặt hàng được đưa vô làm nguồn cho hộp Validation.
Khi ta lực chọn 1 mặt hàng nào đó trong ô Validation thứ nhất, ta sẽ có những mặt hàng tương ứng với nó trong ô Validation thứ hai để chọn 1 trong các mặt hàng của nó trong chũng loại đó.

Xin mời các bạn xem file sẽ rõ.
 

File đính kèm

  • gpeUDF.rar
    12,2 KB · Đọc: 170

Sa DQ

Thành viên năng động
Tham gia
17/6/14
Bài viết
76
Điểm thành tích
8
. . . . . . . . . . . . . . . . . . HÀM TÌM TỔNG BÌNH QUÂN CỦA TẤT CẢ CÁC Ô


Giả sử ta có các trị số chứa trong các ô từ B3 đến ô b12;

Nhiệm vụ đề ra là ta viết hàm tự tạo để nó tính cho ta tổng các bình quân của lần lượt từng ô theo công thức như sau:

= B12/1 + (B12 + B11)/2 + (B12 + B11 + B10)/3 + . . . . + (B12 + B11+ . . . +B3)/10

Hàm này do thành viên có NICK MyGuitar tại diễn đàn "GiaiPhapExcel.COM" viết có nội dung như sau:

PHP:
Option Explicit
Public Function Sum_10(Arr As Range) As Double
Dim I As Long, J As Long, Tong As Double, Tmp As Double
J = Arr.Rows.Count
For I = J To 1 Step -1
  Tmp = Tmp + Arr(I, 1).Value
  Tong = Tong + Tmp / (J + 1 - I)
Next I
Sum_10 = Tong
End Function
 

Tải bộ cài phần mềm Dự toán GXD, Đấu thầu GXD, Thanh Quyết toán GXD, Quản lý chất lượng GXD. Dành cho người mua bản quyền
Kích để xem khóa học Dự toán công trình
Kích để xem khóa học Dự toán công trình
Phần mềm quản lý chất lượng công trình QLCL GXD
Tìm hiểu khóa học Thanh Quyết toán GXD

Các bài viết mới

Top