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?

  • Thread starter Tuanktcdcn
  • Ngày gửi
T

Tuanktcdcn

Lão già ham vui
18/6/04
549
52
28
47
Hà Nội
www.bluesofts.net
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.
 
Khóa học Quản trị dòng tiền
adam_tran

adam_tran

Guitar inspiration
17/5/05
1,374
36
48
46
Goooogle
Công thức tính giá xuất theo LIFO và FIFO chẳng hạn...
 
L

Longlv

Sơ cấp
21/10/05
44
1
8
46
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é
 
HongViet

HongViet

Cao cấp
10/11/05
286
10
18
Đà 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?)
 
Sửa lần cuối:
S

SA_DQ

Thành viên Cố vấn - Webketoan Mentors
29/6/05
568
99
28
66
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!:
Nguyên văn bởi hai2hai
Translate functions from english to vietnames à?
DateAdd có khi đọc còn dễ hiểu hơn là CongTh

Bái phục cách học này! And no comment!..
 
Sửa lần cuối:
hai2hai

hai2hai

VNUNI Makes a difference
29/4/04
2,032
125
63
50
Hà nội
vnuni.net
Readonly - nhầm
 
Sửa lần cuối:
T

Tuanktcdcn

Lão già ham vui
18/6/04
549
52
28
47
Hà Nội
www.bluesofts.net
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.
 
T

Tuanktcdcn

Lão già ham vui
18/6/04
549
52
28
47
Hà Nội
www.bluesofts.net
hai2hai nói:
Translate functions from english to vietnames à?
DateAdd có khi đọc còn dễ hiểu hơn là CongTh

Bái phục cách học này! And no comment!

Đú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
 
adam_tran

adam_tran

Guitar inspiration
17/5/05
1,374
36
48
46
Goooogle
6 - Ý... cái này làm được bằng công thức đấy... đang tính đố bà con! :)
 
B

Bình_OverAC

Over Abnormal / Crazy
14/5/04
846
10
18
42
Nha Trang
adam_tran nói:
6 - Ý... cái này làm được bằng công thức đấy... đang tính đố bà con! :)
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).
 
HongViet

HongViet

Cao cấp
10/11/05
286
10
18
Đà 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)))
 
C

Cayman

Cao cấp
26/11/03
246
4
0
42
HCM
danketoan.com
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 !
 
L

luongvanluyen

Guest
2/9/05
27
0
0
HCM CITY
Cayman nói:
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 !
Có phải ý bạn là dùng hàm weekday() không?

Lê Văn Duyệt
 
C

Cayman

Cao cấp
26/11/03
246
4
0
42
HCM
danketoan.com
luongvanluyen nói:
Có phải ý bạn là dùng hàm weekday() không?

Lê Văn Duyệt

Ý 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:
 
T

Tuanktcdcn

Lão già ham vui
18/6/04
549
52
28
47
Hà Nội
www.bluesofts.net
Từ Ngày cho ra Tuần trong Tháng

Cayman nói:
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 !

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)
 
C

Cayman

Cao cấp
26/11/03
246
4
0
42
HCM
danketoan.com
Tuanktcdcn nói:
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)
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 ???
 
hai2hai

hai2hai

VNUNI Makes a difference
29/4/04
2,032
125
63
50
Hà nội
vnuni.net
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!
 
Sửa lần cuối:
HongViet

HongViet

Cao cấp
10/11/05
286
10
18
Đà nẵng
Cayman nói:
Nếu 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, chỉ giáo với !
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ẹ!
 

Xem nhiều

Webketoan Zalo OA