Các phép tính mà VBA mới là giải pháp tốt nhất? List lại các công thức khó của Excel?

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi Tuanktcdcn, 24 Tháng tư 2006.

16,790 lượt xem

  1. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
    Trên diễn đàn của chúng ta có rất nhiều bạn giỏi về lập công thức trên Excel, nhiều lúc chúng ta lạc quan Excel ( không phải VBA) đã làm được mọi thứ! Với tớ thì phải ứng dụng VBA thì mới có thể lạc quan như vậy.

    Tớ muốn toàn bộ EFC của chúng ta điểm lại những vấn đề từ trước về việc lập công thức mà giải quyết quá khó hoặc không thể làm nếu không có VBA. Tớ biết một số phép tính lập công thức chưa có giải pháp tốt đã bị để dở dang trôi đâu hết? Theo thời gian cùng EFC, trình độ về Excel trong chúng ta có thể khẳng định ai cũng đã khá hơn trước kia nhiều! Vậy chúng ta hãy thực hiện những phần còn dang dở trước kia và những vấn đề mới phát sinh.

    Các bạn có thể đưa ra một số các dạng yêu cầu tính toán trên Sheet mà nếu dùng công thức sẽ là quá khó nếu không dùng VBA?

    ======================================================
    Các vấn đề các bạn liệt kê tôi cập nhật thành danh sách dưới đây:

    1- Tính giá xuất theo LIFO và FIFO
    2- Công thức đổi Số thành Chữ
    3- Công htức tìm kiếm theo nhiều điều kiện
    4- Hay là công thức Đếm số ô màu, hay công thức đảo ngược chuỗi.. Các bạn bổ sung thêm nhé
    5- Địng dạng có điều kiện khi trên 3 tiêu chuẩn (Conditional Formatting...)
    6- Đếm số ngày (trong tuần) của tháng bất kỳ nhập vô ( VD tháng 07 Năm thứ 1919 có bao nhiêu CN?)
    7- Sử dụng hàm trong VBA trên Worksheet
    8- Vòng lặp cho công thức

    Khi ta diễn ta một công thức VD: =If(A1=100,0.2,If(A1=120,0.3,If(,...))) nhiều khi kết quả của loạt công thức này lại cần đưa vào để so sánh tiếp (IF(If(A1=100,0.2,If(A1=120,0.3,If(,...)))=200,...) để cho ra giá trị đúng. Với cách viết hàm trên Sheet thì rất khó!
    Các bạn hãy đọc kỹ bài tại đường link Vòng lặp cho công thức
    sẽ hiểu hơn về vấn đề này.


    9- ?


    Các vấn đề trên mời tất cả các bạn đưa ra giải pháp cụ thể của mình sau đó chúng ta sẽ tập hợp lại.
     
    #1
  2. adam_tran

    adam_tran Steel Partner

    Bài viết:
    1,373
    Đã được thích:
    32
    Nơi ở:
    Goooogle
    Công thức tính giá xuất theo LIFO và FIFO chẳng hạn...
     
    #2
  3. Longlv

    Longlv Thành viên hoạt động

    Bài viết:
    44
    Đã được thích:
    1
    Nơi ở:
    Hanoi
    Hay là công thức Đếm số ô màu, hay công thức đảo ngược chuỗi.. Các bạn bổ sung thêm nhé
     
    #3
  4. HongViet

    HongViet Thành viên thân thiết

    Bài viết:
    286
    Đã được thích:
    10
    Nơi ở:
    Đà nẵng
    Địng dạng có điều kiện khi trên 3 tiêu chuẩn.

    VD:angel: Cần tô màu 4 quí phân biệt cho ngày sinh nhật của một DS nhân viên;
    Hay: Tô màu khác nhau cho năm nâng/xếp lương của NV trong CTi trong mươi năm gần nhất!
    :angel: Đếm số ngày (trong tuần) của tháng bất kỳ nhập vô ( VD tháng 07 Năm thứ 1919 có bao nhiêu CN?)
     
    Last edited: 25 Tháng tư 2006
    #4
  5. SA_DQ

    SA_DQ Thành viên thân thiết

    Bài viết:
    428
    Đã được thích:
    34
    Nơi ở:
    HCM city
    7./ Sử dụng hàm trong VBA

    Một số hàm trong VBA không được nêu ra trong Excel; Để sử dụng nó như một hàm của excel, ta biến nó thành hàm tự tạo; VD trong VBA có hàm DateAdd( Interval As String, Number As Double, Date) Hàm trả về là ngày tháng được cộng thêm (/-) một lượng nêu trong 2 đối số trước nó: Interval chấp nhận 1 trong ~ chuỗi sau: "WW", "D", "M", "YYYY". Muốn vậy ta vô menu Tool > Macro > Visual Basic Editor; Chọn Insert -> Module. Nhập mã lệnh sau trong cửa sổ mới hiện:
    Function H2H_cCc(Khoang as String,Soluong as Double, Dat As Date) As Date
    1 H2H_cCc = DateAdd(Khoang, SoLuong, Dat)
    End Function
    :dzo:
    /(/hớ nha, Sa_DQ!:
     
    Last edited: 27 Tháng tư 2006
    #5
  6. hai2hai

    hai2hai VNUNI Makes a difference

    Bài viết:
    2,012
    Đã được thích:
    128
    Nơi ở:
    Hà nội
  7. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
    Cảm ơn các bạn đã đóng góp! Mong chúng ta tiếp tục đưa ra nhiều vấn đề hơn. Tôi nghĩ không phải chỉ có vậy đâu. Phần trả lời, chúng ta sẽ tìm lại các bài đã có hoặc nếu chưa tôi và các bạn khác sẽ cố gắng giải quyết.
     
    #7
  8. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
    Đúng vậy! Nếu chúng ta muốn dùng các hàm trong VBA (VBA6.DLL) trên Worksheet thì có lẽ theo tôi ta đặt tên hàm có chữ VBA_ ở đầu.
    Ví dụ: Trong VBA có hàm InstrRev để tìm chuỗi ký tự từ phải sang trái (ExcelSheet không có). Cấu trúc như sau:

    InstrRev(stringcheck, stringmatch[, start[, compare]])

    Vậy chúng ta viết lại như thế này:

    VBA_InstrRev(stringcheck, stringmatch[, start[, compare]])

    Tên các tham số ta để nguyên cho "chuẩn" và cũng để thống nhất chung.

    Tóm lại chúng ta muốn dùng các hàm * của VBA trên Worksheet thì chỉ cần viết lại hàm có tên VBA_* với các tham số giữ nguyên.


    Function VBA_InstrRev(ByVal stringcheck As String, ByVal stringmatch As String, Optional ByVal start As Long = -1, Optional ByVal compare As Long = vbBinaryCompare) As Long

    'vbUseCompareOption –1 Performs a comparison using the setting of the Option Compare statement.
    'vbBinaryCompare 0 Performs a binary comparison.
    'vbTextCompare 1 Performs a textual comparison.
    'vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.


    VBA_InstrRev = InstrRev(stringcheck, stringmatch, start, compare)
    End Function
     
    #8
  9. adam_tran

    adam_tran Steel Partner

    Bài viết:
    1,373
    Đã được thích:
    32
    Nơi ở:
    Goooogle
    6 - Ý... cái này làm được bằng công thức đấy... đang tính đố bà con! :)
     
    #9
  10. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    Nha Trang
    Bác ơi. Thì là anh Tuân đang cố gom các hàm lại để viết các hàm VBA thôi mà. Còn chuyện đó đó thì cũng hỏng có ảnh hưởng gì hết. Em cũng lười quá không có ý định đưa nó lên làm thách đố EFC nữa. (mất công theo dõi và vận động).
     
    #10
  11. HongViet

    HongViet Thành viên thân thiết

    Bài viết:
    286
    Đã được thích:
    10
    Nơi ở:
    Đà nẵng
    #11
  12. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
  13. HongViet

    HongViet Thành viên thân thiết

    Bài viết:
    286
    Đã được thích:
    10
    Nơi ở:
    Đà nẵng
    ừ hén! Nhưng hàm viết quá ư là hoành tráng!

    Xin tiện cho hỏi: nên chọn phương án nào sau đây, hay phương án khác tốt hơn, các bạn ơi!:

    ij = IIf(Temp < 1, 1, IIf(Temp < 2, 2, IIf(Temp < 3, 3, iI = IIf(Temp < 4, 4, IIf(Temp < 5, 5, 6)))))
    ij = IIf(Temp < 3, IIf(Temp < 1, 1, IIf(Temp < 2, 2, 3)), IIf(Temp < 4, 4, IIf(Temp < 5, 5, 6)))
     
    #13
  14. Cayman

    Cayman Thành viên thân thiết

    Bài viết:
    246
    Đã được thích:
    3
    Nơi ở:
    HCM
    Cho em hỏi chút : nếu em có input data là 1 ngày bất kỳ (dd/mm/yy) và muốn output được là ngày đó thuộc tuần thứ mấy trong tháng thì dùng hàm gì, hay phải dùng VBA (em không rành VBA, hic :wall: ). Các bác chỉ giáo với !
     
    #14
  15. luongvanluyen

    luongvanluyen Thành viên hoạt động

    Bài viết:
    27
    Đã được thích:
    0
    Nơi ở:
    HCM CITY
    Có phải ý bạn là dùng hàm weekday() không?

    Lê Văn Duyệt
     
    #15
  16. Cayman

    Cayman Thành viên thân thiết

    Bài viết:
    246
    Đã được thích:
    3
    Nơi ở:
    HCM
    Ý em là ra kết quả là ngày đó thuộc tuần thứ mấy trong tháng ? (1 tháng có 4 tuần ạ ).

    Em nhấn F1 đối với weekday() thì đuợc biết là kết quả chỉ cho ra được đó là ngày thứ mấy trong tuần thôi.

    Nếu thực hiện được trong Excel, có cách nào ứng dụng cả trong Access cơ bản không các bác ? Em hơi tham nhỉ :1luvu:
     
    #16
  17. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
    Từ Ngày cho ra Tuần trong Tháng

    A1 là giá trị ngày tháng năm bạn đưa vào. VD 05/04/06

    =ROUNDUP((DAY(A1)+WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-1-1)/7,0)
     
    #17
  18. Cayman

    Cayman Thành viên thân thiết

    Bài viết:
    246
    Đã được thích:
    3
    Nơi ở:
    HCM
    Nếu em thử với ngày 31/1/05 thì kết quả là 6, và ngày 1/5/05 thì kết quả là 0 ???
     
    #18
  19. hai2hai

    hai2hai VNUNI Makes a difference

    Bài viết:
    2,012
    Đã được thích:
    128
    Nơi ở:
    Hà nội
    Public Function WeekNumber(InDate As Date) As Integer

    '===========================================================================
    '## Public Function WeekOfMonth
    ' This code is in the form of a function that returns the week of the month. It can be simplified into a one-liner if need be

    Public Function WeekOfMonth(Optional strDate As String = "") As Integer

    If Not IsDate(strDate) Then strDate = Now
    Dim intStartOfMonthWeek As Integer, intThisWeek As Integer
    intStartOfMonthWeek = DatePart("ww", DateValue(Month(strDate) & "/01/" & Year(strDate)))
    intThisWeek = DatePart("ww", DateValue(strDate))
    WeekOfMonth = intThisWeek - intStartOfMonthWeek + 1
    End Function


    Hoặc đơn giản hơn:

    WeekOfMonth = DateDiff("ww", DateAdd("d", -Day(dDate) + 1, dDate), dDate) + 1

    Tuy nhiên nên dùng hàm DateSerial để tương thích với các kiểu format date khác nhau:

    Dưới đây là hàm tương tự hàm ở trên, cơ bản thì vẫn sử dụng DateDiff thay vì việc tách các phần giá trị của ngày.

    WeekOfMonth = DateDiff("ww", DateSerial(Year(dDate), Month(dDate), 1), dDate) + 1


    Listed here are some more samples about date & week functions:

    '===========================================================================
    '## Private Date Calculation Routines
    '
    Private Function Days(DayNo As Date) As Integer
    Days = DayNo - DateSerial(Year(DayNo), 1, 0)
    End Function

    '===========================================================================
    '## Public Date2WeekNumber
    '
    Public Function WeekNumber(InDate As Date) As Integer
    '
    ' Notes: (Microsoft KB Article http://support.microsoft.com/support/kb/articles/Q200/2/99.asp)
    ' ------
    '
    ' ISO 8601 "Data elements and interchange formats - Information interchange - Representation of dates and times"
    ' ISO 8601 : 1988 (E) paragraph 3.17:
    ' "week, calendar: A seven day period within a calendar year, starting on a Monday and
    ' identified by its ordinal number within the year; the first calendar week of the year
    ' is the one that includes the first Thursday of that year. In the Gregorian calendar,
    ' this is equivalent to the week which includes 4 January."
    '
    ' This can be implemented by applying these rules for Calendar weeks:
    ' - A year is divided into either 52 or 53 calendar weeks.
    ' - A calendar week has 7 days. Monday is day 1, Sunday is day 7.
    ' - The first calendar week of a year is the one containing at least 4 days.
    ' - If a year is not concluded on a Sunday, either its 1-3 last days belong to next
    ' year's first calendar week or the first 1-3 days of next year belong to the
    ' present year's last calendar week.
    ' - Only a year starting or concluding on a Thursday has 53 calendar weeks.

    Dim DayNo As Integer
    Dim StartDays As Integer
    Dim StopDays As Integer
    Dim StartDay As Integer
    Dim StopDay As Integer
    Dim VNumber As Integer
    Dim ThurFlag As Boolean

    DayNo = Days(InDate)
    StartDay = Weekday(DateSerial(Year(InDate), 1, 1)) - 1
    StopDay = Weekday(DateSerial(Year(InDate), 12, 31)) - 1
    ' Number of days belonging to first calendar week
    StartDays = 7 - (StartDay - 1)
    ' Number of days belonging to last calendar week
    StopDays = 7 - (StopDay - 1)
    ' Test to see if the year will have 53 weeks or not
    If StartDay = 4 Or StopDay = 4 Then ThurFlag = True Else ThurFlag = False
    VNumber = (DayNo - StartDays - 4) / 7
    ' If first week has 4 or more days, it will be calendar week 1
    ' If first week has less than 4 days, it will belong to last year's
    ' last calendar week
    If StartDays >= 4 Then
    WeekNumber = Fix(VNumber) + 2
    Else
    WeekNumber = Fix(VNumber) + 1
    End If
    ' Handle years whose last days will belong to coming year's first
    ' calendar week
    If WeekNumber > 52 And ThurFlag = False Then WeekNumber = 1
    ' Handle years whose first days will belong to the last year's
    ' last calendar week
    If WeekNumber = 0 Then
    WeekNumber = WeekNumber(DateSerial(Year(InDate) - 1, 12, 31))
    End If

    End Function


    ---------------------------------------

    Khi đã có WeekNumber rồi thì có thể biết được là tuần thứ mấy của tháng. Nhỉ?

    Bấm vào Trích dẫn để copy nhé.

    Nhưng mà, thực ra,... khó nói nhỉ. Tớ chỉ mất có 5 giây để giải quyết tất cả các vấn đề đại loại như vậy với google. Tất cả những hàm liên quan đến date, time. Chỉ cần biết 1 chút tiếng Anh là xong.

    Hope that helps!
     
    Last edited: 5 Tháng năm 2006
    #19
  20. HongViet

    HongViet Thành viên thân thiết

    Bài viết:
    286
    Đã được thích:
    10
    Nơi ở:
    Đà nẵng
    Nếu không thích VBA thì B. Tuan_ đã ra hàm rồi; nhưng có lẽ cần xác định thứ mấy là ngày đầu tuần; Nếuđúng như bài hát: "Thứ 2 là ngày đầu tuần, bé . . . " Theo mình thì phải làm tuần tự như sau:
    Xác định thứ 2 gần nhất trước đó (VD H) là ngày (VD ThuHai) nào:
    Xác định thứ của ngày nhập vô W= WEEKDAY(NgNhap)
    Thứ W phải trừ hay cộng một số ngày H = W +(n hay -m)
    Xác định Day(ThuHai) /7 & làm tròn số sẽ đúng thôi!
    Sau đó tiến hành cải tiến công thức cho gọn nhẹ!
     
    #20

Chia sẻ trang này