Excel keng foydalanish doirasiga ega. Ushbu maqolada biz sizga makros yordamida ish varag'ida taqvim yaratishni ko'rsatamiz.
Excelda ma'lumotlarni saqlash va tahlil qilishdan tashqari, siz undan boshqa vazifalarni bajarish uchun ham foydalanishingiz mumkin. Misol uchun, siz Excel yordamida ma'lumot to'plashingiz mumkin yoki rejangizni ish varag'iga yozib qo'yishingiz mumkin. Bugun biz yangi foydalanishni topdik. Siz ishchi varaqda taqvim yaratishingiz mumkin. Quyidagi rasmda effektni ko'rishingiz mumkin.
Ushbu kalendarga ba'zi vazifalarni kiritishingiz mumkin. Va funksiya memorandum bilan bir xil. Exceldan foydalangan holda, bu vazifalar aniqroq bo'ladi. Endi keyingi qismdagi amallarni bajaring va uning qanday ishlashini ko'ring.
Kalendar yarating
- Ish varag'ini ochish uchun klaviaturadagi "Alt + F11" tugmasini bosing.
- Va keyin Visual Basic muharririga yangi modul qo'shing.
- Endi quyidagi kodlarni yangi modulga nusxalash:
Sub Create_Monthly_Calender()
Dim firstweekday As Integer, EndDay As Integer, _
FirstWeekColumnIndex As Integer, AssignmentDate As Integer, _
FirstCountNumber As Integer, SecondCountNumber As Integer, _
LastDay As Range, objRange As Range, RowIndexofLastday As Integer, FirstCountforTargetRange As Integer, SecondCountforTargetRange As Integer
firstday = InputBox("Input the year, month and the first day with this format: year/month/day")
If firstday = "" Then Exit Sub
Range("A1:G1").Merge
Range("A1") = Year(firstday) & "." & Month(firstday)
Range("A2") = "Sunday"
Range("A2").AutoFill Destination:=Range("A2:G2"), Type:=xlFillDefault
firstweekday = Application.WorksheetFunction.Weekday(firstday)
Cells(3, firstweekday) = 1
Select Case Month(firstday)
Case 1, 3, 5, 7, 8, 10, 12
EndDay = 31
Case 4, 6, 9, 11
EndDay = 30
Case 2
If (Year(firstday) Mod 4) = 0 And (Year(firstday) Mod 100) <> 0 Or ((Year(firstday) Mod 400) = 0) Then
EndDay = 29
Else
EndDay = 28
End If
End Select
For FirstWeekColumnIndex = 1 To (7 - firstweekday)
Cells(3, firstweekday).Offset(0, FirstWeekColumnIndex) = Cells(3, firstweekday).Offset(0, FirstWeekColumnIndex - 1) + 1
Next FirstWeekColumnIndex
AssignmentDate = Range("G3") + 1
For FirstCountNumber = 2 To 10 Step 2
For SecondCountNumber = 0 To 6
Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = AssignmentDate
AssignmentDate = AssignmentDate + 1
If Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = EndDay Then
Exit For
End If
Next SecondCountNumber
If Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = EndDay Then
Exit For
End If
Next FirstCountNumber
’set format for the range
With Range("A1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 16
.Font.Bold = True
.Interior.Color = RGB(196, 202, 201)
End With
With Range("A2:G2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
End With
For Each LastDay In ActiveSheet.UsedRange
If LastDay = EndDay Then
RowIndexofLastday = LastDay.Row
End If
Next
For FirstCountforTargetRange = RowIndexofLastday To 3 Step -2
With Range("A" & FirstCountforTargetRange, "G" & FirstCountforTargetRange)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.RowHeight = 20
End With
Next FirstCountforTargetRange
For SecondCountforTargetRange = RowIndexofLastday + 1 To 4 Step -2
With Range("A" & SecondCountforTargetRange, "G" & SecondCountforTargetRange)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
.RowHeight = 50
.ColumnWidth = 12
End With
Next SecondCountforTargetRange
Set objRange = Range("A1", "G" & (RowIndexofLastday + 1))
With objRange.Borders
.Color = vbBlack
.Weight = xlThin
.LineStyle = xlContinuous
End With
ActiveWindow.DisplayGridlines = False
Cells(3, firstweekday).Offset(1, 0).Select
End Sub
Shunday qilib, juda yolg'iz makro. Ammo siz uni to'g'ridan-to'g'ri ishlatishingiz mumkin. Va siz uni o'zgartirishingiz shart emas.
- Keyin klaviaturadagi "F5" tugmasini bosing.
- Qalqib chiquvchi oynada birinchi kunning sanasini “Yil/oy/kun” formatida kiriting. "Kun" bu oyning istalgan kuni bo'lishi mumkin, bu erda biz "1" ni kiritamiz.
- Va keyin "OK" tugmasini bosing. Keyin muharrirga qaytasiz. Natijani tekshirish uchun siz ish varag'iga qaytishingiz mumkin.
Kalendar allaqachon ish varag'ida paydo bo'lgan. Va kursor birinchi kunning bo'sh katagiga joylashtiriladi.
Boshqa tomondan, agar siz hujayralar formatidan qoniqmasangiz, kodlarni o'zingizning xohishingizga ko'ra o'zgartirishingiz mumkin. Ushbu taqvim yordamida siz ish samaradorligini oshirishga ishonchingiz komil.
Excel muvaffaqiyatsizlikka kamroq moyil emas
Agar siz Exceldan tez-tez foydalansangiz, Excelning har doim buzilib qolishini aniqlash siz uchun qiyin bo'lmaydi. Ko'pincha Exceldan chiqing va keyin uni qayta ishga tushiring, bu muammoni hal qilishi mumkin. Biroq, ba'zida siz Excelning jiddiy buzilishiga duch kelasiz. Va hozirda siz bizning kuchli vositamizdan foydalanishingiz mumkin, buning uchun Excel xlsx ma'lumotlar xatosini tuzatishUshbu vositadan foydalanish orqali deyarli barcha xatolarni osongina hal qilish mumkin.
Muallif kirish:
Anna Ma ma'lumotlarni qayta tiklash bo'yicha mutaxassis DataNumenMa'lumotlarni qayta tiklash texnologiyalari bo'yicha jahon yetakchisi bo'lgan , Inc Word docx faylini tuzatish va Outlook ta'mirlash dasturiy mahsulotlar. Qo'shimcha ma'lumot olish uchun tashrif buyuring www.datanumen.com