جدا کردن یک شیت به چند شیت بر اساس اطلاعات یک ستون

جدا کردن یک شیت به چند شیت بر اساس اطلاعات یک ستون

ادامه نوشته

جدا کردن یک شیت به چند شیت بر اساس اطلاعات یک ستون 2

' @mas76nrm 2023/11/26

Private Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
Dim sht As Worksheet

If wb Is Nothing Then Set wb = ThisWorkbook
On Error Resume Next
Set sht = wb.Sheets(shtName)
On Error GoTo 0
WorksheetExists = Not sht Is Nothing
End Function

'Function WorksheetExists(sName As String) As Boolean
' WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
'End Function

'Private Function getCity(cityPersianName As String)
' Dim cities As Variant
' cities = Array( _
' Array("مرکز استان", "Center"), _
' Array("اردب?ل", "Ardabil"), _
' Array("پارس آباد", "Parsabad"), _
' Array("بيله سوار", "BileSavar") _
' )
' Dim i As Integer
' getCity = "test"
' For i = 0 To 3
' If cities(i)(0) = cityPersianName Then
' getCity = cities(i)(1)
' Exit For
' End If
' Next i
'End Function

Private Function copyRow(sourceSheet As String, destinationSheet As String, rowNumber As Integer, Optional increaseRow As Boolean = False)
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRow As Long

Set ws1 = ThisWorkbook.Sheets(sourceSheet)
Set ws2 = ThisWorkbook.Sheets(destinationSheet)
lastRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
If increaseRow Then
lastRow = lastRow + 1
End If
ws1.Rows(rowNumber).EntireRow.copy ws2.Range("A" & lastRow)
copyRow = True
End Function

Public Sub extractCities()

Dim rowCounter As Integer
Dim city As String
Dim res As Boolean

ThisWorkbook.Sheets("Personels").Select

For rowCounter = 3 To 1000
city = Cells(rowCounter, 8).Value
If WorksheetExists(city) Then
Application.DisplayAlerts = False
ThisWorkbook.Sheets(city).Delete
Application.DisplayAlerts = True
End If
Next rowCounter

For rowCounter = 3 To 1000
ThisWorkbook.Sheets("Personels").Select
city = Cells(rowCounter, ThisWorkbook.Sheets("Personels").Cells(1, 5)).Value
If Not IsNull(city) And city <> "" Then
If Not WorksheetExists(city) Then
With ThisWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = city
.Sheets(.Sheets.Count).DisplayRightToLeft = True
End With
res = copyRow("Personels", city, 2)
End If
res = copyRow("Personels", city, rowCounter, True)
End If
Next rowCounter

MsgBox "Finished"
End Sub

تبدیل روز به سال و ماه و روز

 

 

    فرمول های زیر رو تست کنید :

    کد:

 

    =DATEDIF(0,A2,"y")&" سال " &DATEDIF(0,A2,"ym")&" ماه "&DATEDIF(0,A2,"md")&" روز"

 

    کد:

 

    =CONCATENATE(INT(A1/365)," سال ", INT(MOD(A1,365)/30)," ماه ", MOD(MOD(A1,365),30)," روز ")

 

    کد:

 

    =CONCATENATE(IF(INT(A2/365)>0,CONCATENATE(INT(A2/365)," سال "),""),CONCATENATE(IF(INT(MOD(A2,365)/30)>0,CONCATENATE(INT(MOD(A2,365)/30)," ماه "),""),CONCATENATE(IF(MOD(MOD(A2,365),30)>0,CONCATENATE(MOD(MOD(A2,365),30)," روز "),""),)))

 

فايل هاي پيوست شده فايل هاي پيوست شده

 

    نوع فایل: xlsx

ادامه نوشته

تبدیل روز به سال ماه و روز در اکسل

تبدیل روز به سال ماه و روز در اکسل

=CONCATENATE(IF(INT(K12/365)>0;CONCATENATE(INT(K12/365);" سال ");"");CONCATENATE(IF(INT(MOD(K12;365)/30)>0;CONCATENATE(INT(MOD(K12;365)/30);" ماه ");"");CONCATENATE(IF(MOD(MOD(K12;365);30)>0;CONCATENATE(MOD(MOD(K12;365);30);" روز ");"");)))

شمارش موارد تکراری در اکسل

(COUNTIF($D$3:$E$4440;$D14=

محاسبه فاصله بین دو تاریخ

Formula to sum the output from DATEDIF (X Years X Months X Days)

ادامه نوشته

کار با توابع تاریخ شمسی در اکسل

 

ابتدا یک شیت جدید باز کنید. حالا مثل تصویر زیر روی Sheet1 راست کلیک کنید و از منوی باز شده گزینه View Code رو انتخاب کنید.

ادامه نوشته

فرمول‌نویسی آرایه‌ای در اکسل

فرمول‌نویسی آرایه‌ای در اکسل

 

با استفاده از فرمول‌نویسی آرایه‌ای (Array formulas) می‏‌توانید فرمول‏‌ها را به صورت کلی بنویسید و روی چندین سلول اعمال کنید. ساده‌ترین کاربرد فرمول نویسی آرایه‌ای، فرمول نویسی روی ستون‏‌ها است. به این صورت که دیگر به جای استفاده از نشانی یک سلول برای نگارش فرمول، شما می‏‌توانید محدوده‌ی یک ستون را در فرمول مورد استفاده قرار دهید. در ادامه مطلب با پی‌سی‌ترفند همراه باشید با روش استفاده از فرمول نویسی آرایه‌ای آشنا شوید.

ادامه نوشته

how can I manually add a thumbnail to the new tab page?

1 Recommended Answer

I like the fact that chrome keeps up with pages I've visited, but it would be REALLY helpful if you could manually add a thumbnail to the new tab page (ala Safari). The thing I love about Chrome first and foremost is that I can simply type my search string into the address and google automatically searches for it. But I'd like to be able to use the new tab page (also set as my home page) for my "normal" browsing shortcuts.

Community content may not be verified or up-to-date. Learn more.

Recommended Answer

ادامه نوشته

How To Count Unique Values In A Filtered Column In Excel?

How To Count Unique Values In A Filtered Column In Excel?

If you have a list of names which populated with some duplicates in a filtered column, and now, you want to count the number of unique names only from this filtered list. Do you have any good solutions to quickly deal with this job in Excel?

Count unique values in a filtered column with array formula

Count unique values in a filtered column with Kutools for Excel

---

 Count Unique Values In A Filtered Column With Array Formula

Amazing! Using Efficient Tabs In Excel Like Chrome, Firefox And Safari!

Save 50% Of Your Time, And Reduce Thousands Of Mouse Clicks For You Every Day!

---

The following array formula may help you to solve this task quickly and easily, please do as follows:

Enter the following formula into a blank cell where you want to put the result:=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D2,ROW(D2:D22)-ROW(D2),,1)), IF(D2:D22<>"",MATCH("~"&D2:D22,D2:D22&"",0))),ROW(D2:D22)-ROW(D2)+1),1)), and then press Shift + Ctrl + Enter keys together to get the correct calculation, see screenshot:

ادامه نوشته