لوپ در اکسل ( پر کردن اطلاعات در شیت گزارش )

در شکل بالا در تصویر اول در یک شیت مجزا اطلاعاتی از قبیل تاریخ ،شماره شناسایی مشتری و یک ستون پرداخت ( بصورت Yes و No ) پر شده .... در تصویر زیر آن قرار است در ستون های عنوان هر شماره شناسایی مشتری طبق سالی که پرداخت کرده تعداد یا کانت Yes یا No درج گردد.

۱-گرفتن شماره ردیف آخرین داده واردشده در ستون ۱ ، اگر داده خالی وجود داشته باشد شماره ردیف ماقبل آنرا را باز می گرداند پس به این نکته دقت کنید تا ستونی را انتخاب کنید که حتما تمام ردیف های آن پر باشد.

lastRow=Sheets("Data").Cells(Rows.Count,1).End(xlUp).Row

۲-معرفی آرایه پویا :

Dim Arr()

ReDim Arr(lastRow-2,1)

۳-شماره رکورد اول در آرایه :

Num=0

۴-ذخیره داده در آرایه :( شیت تصویر اول در شکل)

For L=2 To lastRow

If Sheets("Data").Range("C" & L)=Yes Then

Arr(Num,0)=Year(Sheets("Data").Range("A" & L)

Arr(Num,1)=Sheets("Data").Range("B" & L)

Num=Num+1

End If

Counter=0

For i=0 To Num-1

If Arr(i,0)=YEAR And Arr(i,1)=ID Then

Counter=Counter+1

For YEAR=2011 To 2026

For ID=1 TO 30

'here use Counter

۷ دیماه ۱۴۰۲

۷ دیماه ۱۴۰۲ :

سید محمد شفیعی در یادداشتی برای دیپلماسی ایرانی می نویسد: ایران نیازی به دخالت مستقیم خود ندارد و می‌تواند با استفاده از نیروهای نیابتی خود (هدف قرار دادن بلندی‌های جولان از طریق خاک سوریه، تقویت انصارالله برای مختل کردن تجارت دریایی اسرائیل و تقویت حماس برای ایجاد تغییر در موازنه جنگ)، شرایط را برای اسرائیل در ادامه جنگ سخت‌تر کند و با صبر راهبردی می‌تواند این ترور را تلافی کند

۱۳ دیماه ۱۴۰۲ : عملیات تروریستی

ثارالله انکوتی در گفتگو با گروه اجتماعی پایگاه خبری تحلیلی راه آرمان که از شاهدان عینی موج انفجار ترویستی، گفت: مسیر ورودی به گلزار شهدا از سمت ورودی مسجد فروزی قتلگاه بود.

وی ادامه داد: در موکب مشغول خدمت‌رسانی بودیم که متأسفانه صدای بسیار مهیبی شنیده شد با سرعت خود را به سمت محل حادثه رساندم که زمین را از پیکر شهدا پوشیده دیدم.

دیماه ۱۴۰۲

رویا حشمتی براساس رأی شعبه ۲۶ دادگاه انقلاب با اتهام تبلیغ علیه نظام، به یک سال حبس محکوم شد و قرار بود که یک‌بیستم آن اجرا شود، اما در نهایت او دو روز هم بیشتر از حکم خود در زندان ماند.

رأی شعبه ۱۰۹۱ مجتمع قضائی ارشاد نیز چنین بوده است: «این دادگاه مجموعا خانم رویا حشمتی را به ۱۳ سال و ۹ حبس محکوم کرده بود. این شعبه، بابت اتهام حضور در معابر بدون حجاب شرعی، خانم حشمتی را به پرداخت یک میلیون و ۲۵۰ هزارتومان جزای نقدی، بابت اتهام تشویق به فساد و فحشا به تحمل ۱۲ سال و شش ماه حبس، بابت اتهام جریحه‌دارکردن عفت عمومی به تحمل ۷۴ ضربه شلاق تعزیری و بابت اتهام ساخت و انتشار آثاری که عفت عمومی را جریحه‌دار می‌کند، به تحمل یک سال و سه ماه حبس تعزیری، ۷۴ ضربه شلاق و ۱۰۰ میلیون ریال جزای نقدی محکوم کرد».

این حکم مورد اعتراض و تجدیدنظر خواهی قرار می‌گیرد و در نهایت با ارجاع به شعبه ۱۴ دادگاه تجدیدنظر استان تهران، رویا بابت اتهام تشویق به فساد و فحشا و ساخت و انتشار آثاری که عفت عمومی را جریحه‌دار کرده، تبرئه می‌شود. اما با اتهام حضور در معابر بدون حجاب شرعی، به پرداخت یک میلیون و ۲۵۰ هزار تومان جزای نقدی و به جهت اتهام جریحه‌دار کردن عفت عمومی به ۷۴ ضربه شلاق محکوم می‌شود و این حکم درست در روز مادر اجرا می‌شود.

لوپ در اکسل اکسل آموزش اکسل آموزش VBA آموزشت ناصرالدین ترور رویا حشمتی

پروفسور اکسس ایران دوشنبه 11 دی 1402 ساعت 16:58

0 نظر

لوکاپ مقدار در کالمن ها در اکسل با XLOOKUP

کاربرد تابع XLOOKUP برای پیدا کردن چیزهایی در یک جدول یا محدوده توسط ردیف.بعنوان مثال جستجو کردن قیمت یک قطعه توسط شماره قطعه یا پیدا کردن نام کارمند بر اساس آیدی کارمندیشون. با XLOOKUP می توان در یک ستون به دنبال عبارت جستجو بگردید و بدون توجه به اینکه ستون برگشتی در کدام سمت است،نتیجه ای را از همان ردیف در ستون دیگر بر می گرداند.

XLOOKUP در اکسل 2016 و 2019 موجود نیست

جایگزینی برای HLOOKUP است

در جدول زیر از دو XLOOKUP استفاده شده که با توجه به عنوان ستونی که می خواهید مقدار در ردیف های ستون Income بازگردانده شود

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

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

xlookup hlookup اکسل Excel

پروفسور اکسس ایران چهارشنبه 1 تیر 1401 ساعت 12:49

0 نظر

Remove Unprotect Sheet Of Excel File

Excel اکسل پسورد Unprotect Sheet Remove Unprotect Sheet

پروفسور اکسس ایران یکشنبه 13 بهمن 1398 ساعت 18:44

0 نظر

Scrolling Excel Chart

اکسل چارت

پروفسور اکسس ایران پنج‌شنبه 7 شهریور 1398 ساعت 15:27

0 نظر

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

Named Range / Indirect

اکسل کمبوباکس

پروفسور اکسس ایران سه‌شنبه 29 مرداد 1398 ساعت 22:10

0 نظر

Gantt Chart

(AND(date>=start,date<=end=

(AND(D$4>=$B5,D$4<=$C5=

اکسل Gantt Chart

پروفسور اکسس ایران سه‌شنبه 29 مرداد 1398 ساعت 16:13

0 نظر

جستجو در لیست باکس ( Developer/ActiveX Object / Control )

روش دوم :

استفاده از تابع SEARCH اگرنبود تابع FIND

استفاده از تابع IF و COUNTIF در ستون G به صورتیکه اگر سلی در ستون F برابر یک شده کانت 1 را در رنج $F$3:F3 در سل مربوطه درج کند.

نمونه ی استفاده از تابع SEARCH در تصویر زیر

فرمول همراه با تابع Index در ستون H

([MATCH(lookup_value, lookup_array, [match_type

(MATCH(ROWS($G$3:G3),$G$3:$G$22,0

عملکرد تابع ROWS (برگشت تعداد ردیف ها ) در رنج مشخص شده - وقتی فرمول به پائین کشیده شود ( DRAG ) قسمت اول که $ دارد ثابت میماند و قسمت دوم یعنی G3 اعدادش نسبت به قبلش یک واحد بصورت افزایشی تغییر میکند یعنی G3 میشود G4 و سل بعد G5 و ...

ROWS($G$3:G3) : 1
ROWS($G$3:G4) : 2
ROWS($G$3:G5) : 3
ROWS($G$3:G6) : 4
ROWS($G$3:G7) : 5
ROWS($G$3:G8) : 6
ROWS($G$3:G9) : 7

عملکرد تابع MATCH یعنی تعیین موقعیت ( اعداد برگشتی تابع بالا پارامتر آرگومان اول تابع MATCH شده اند )

MATCH(1,$G$3:$G$22,0)=1=
MATCH(2,$G$3:$G$22,0)=7=

همانطور که در بالا مشاهده می کنید موقعیت عدد یک را در ستون G نمایش میدهد که 1 است و همینطور موقعیت عدد 2 در ستون G که 7 است را برمی گرداند و برای اعداد بعدی ( حاصل تابع ROWS بالاتر ) که 3 و ... هست چون وجودندارند صفر است .

حال تابع بالا ( MATCH ) میشود آرگومان Row در تابع INDEX

([INDEX(array, row_num, [column_num

(INDEX($E$2:$E$20,1=
(INDEX($E$2:$E$20,7=

:Here are the steps to create it

Go to Formulas –> Name Manager.
In the name-manager dialogue box click New. It will open a New Name dialogue box.
In the Name Field enter DropDownList
In the Refers to Field enter the formula:
=$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),

درستون H از دوتابع MATCH و INDEX استفاده شده که تابع MATCH موقعیت موردجستجو را برمی گرداند .

Range("E3:E27").AdvancedFilter Action:=xlFilterCopy

CopyToRange:=Range("I3"), Unique:=True,

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub

اکسل کمبوباکس

پروفسور اکسس ایران سه‌شنبه 29 مرداد 1398 ساعت 13:23

0 نظر

کمبوباکس های وابسته با Named Range

اکسل کمبوباکس

پروفسور اکسس ایران سه‌شنبه 29 مرداد 1398 ساعت 12:03

0 نظر

کمبوباکس های وابسته در اکسل ( Advanced Filter Vba )

اکسل کمبوباکس

پروفسور اکسس ایران سه‌شنبه 29 مرداد 1398 ساعت 12:00

0 نظر

Row/Rows/Min/Indirect/RandBetween

برگشت تعداد ردیف ها در یک رفرنس

برگشت شماره ردیف یک رفرنس

برگشت کوچکترین مقدار در یک دسته

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

The Excel INDIRECT function returns a valid reference from a given text string

در اکسل اگر بخواهید در فرمول خاصی ، رنجی را بگنجانید از Indirect استفاد می کنید چون در حالت عادی نمی توانید در سل خاصی رفرنسی را بنویسید و در فرمول دیگر به آن ارجاع بدهید .

اکسل Min Row Rows Last Row LastRow RandBetween

پروفسور اکسس ایران دوشنبه 28 مرداد 1398 ساعت 23:48

0 نظر

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

اکسل DataValidation

پروفسور اکسس ایران دوشنبه 28 مرداد 1398 ساعت 23:04

0 نظر

خطای داده تکراری ( Data Validation Custom Formula )

2>(CountIf(C$2:C$7,C3=

اکسل Prevent Duplicate Value DataValidation

پروفسور اکسس ایران دوشنبه 28 مرداد 1398 ساعت 22:07

0 نظر

Unique Value ComboBox

A1 A B

تجربی 1001

ریاضی 1002

انسانی 1003

تجربی 1001

تجربی 1004

In Sheet 1

Create Named Name Range

Name Range : StuId

,OFFSET(Sheet1!$A$2, 0, 0=

((COUNTA(Sheet1!$A$2:$A$1000

Name Range : Course

,OFFSET(Sheet1!$B$2, 0, 0=

(( COUNTA(Sheet1!$B$2:$B$1000

Sheet 2

:Create Unique Value In Sheet2

In Cell A2

,IFERROR(INDEX(StuId, MATCH(0=

("",(COUNTIF($A$1:A,StuId), 0

Press Ctrl + Shift + Enter

In Cell B2

,IFERROR(INDEX(Course,MATCH(0=

+( COUNTIF($B$1:B1,Course)

("",0(StuId<>INDEX(Sheet2!$A$2:$A$1000,Sheet3!$B$1)

Press Ctrl + Shift + Enter

Create Named Range : UniqueStuId

,OFFSET(Sheet2!$A$2, 0, 0=

(COUNT(IF(Sheet2!$A$2:$A$1000="", "", 1)), 1

Create Named Range : UniqueCourse

,OFFSET(Sheet2!$B$2, 0, 0,=

( COUNT(IF(Sheet2!$B$2:$B$1000="", "", 1)), 1

: In Sheet3

( Create Combo box 1 ( Form Controls
Input Range : UniqueStuId
( Cell Link : B2 ( Sheet 3

( Create Combo Box2 ( Form Controls
Input Range : UniqueCourse
Cell Link : C2

اکسل Unique Unique Value

پروفسور اکسس ایران دوشنبه 28 مرداد 1398 ساعت 20:35

0 نظر

Array Formula

جمع تعداد کاراکترهای رنج مشخص شده را برمیگرداند .

=SUM({3;3;5;4;5;4;6;5;4;4})

=SUMPRODUCT(LEN(A1:A5))

اکسل آرایه

پروفسور اکسس ایران یکشنبه 27 مرداد 1398 ساعت 23:46

0 نظر

Last Row / Delete Row در اکسل

range.copy ' Copy To ClipBoard

range.pastespecial

range.interior ' Color

range.borders

اکسل Range

پروفسور اکسس ایران یکشنبه 27 مرداد 1398 ساعت 21:33

0 نظر

UserControl در اکسل ... کد نویسی

منبع زیر از سایت خارجی است و طبق نگاه اجمالی و کدنویسی VBA باید درست باشد.

درتابع زیر چنانچه در اولین کنترل که editstudent1 است اینتر زده شود تابع Findit فایرشده و مقدارآن در متغیر تعریف شده ی Search را در رنج A پیدا میکند ( متد Find ) ، اگر پیداشد با لوپ زدن در کنترل ها که شروع آن از 2 است و به 13 ختم میشود ( منظور نام کنترلها در UserForm از editstudent1 شروع شده تا editstudent13 ) مقدار کالمن ها در ردیف پیداشده در شیت را ( با متد Cells ) به آن کنترل ها انتقال میدهد.

Private Sub editstudent1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode=vbKeyReturn Then

Findit

End If

End Sub

Private Sub Findit() 'Find and populate the records with Excel VBA

Dim fnd As Range

Dim Search As String

Dim sh As Worksheet

Dim i As Integer

Set sh=Sheet2

Search=editstudent1.Text

(Set fnd=sh.Columns("A:A").Find(Search, , , xlWhole

If fnd Is Nothing Then

MsgBox "No Person Found", , "Error"

editstudent1.Text=""

frmeditrecord.Hide

Else

For i=2 To 13 'There are 13 items in the userform.

frmeditrecord.Controls("editstudent" & i).Text=sh.Cells(fnd.Row, i).Value

Next i

End If

End Sub

Private Sub cmdUpdate_Click() 'Push data back to the tab with Excel VBA
Dim fnd As Range
Dim Search As String
Dim sh As Worksheet
Dim i As Integer
Dim ctl As Object Set sh=Sheet2
Search=editstudent1.Text

(Set fnd=sh.Columns("A:A").Find(Search, , , xlWhole
For i=2 To 13
sh.Cells(fnd.Row, i).Value=frmeditrecord.Controls("editstudent" & i).Text
Next i

حذف کردن محتویات ادیت باکس کنترل های تیپ تکست باکس در UserForm.

'Clear Form controls
For Each ctl In Me.Controls
If TypeName(ctl)="TextBox" Then ctl.Value=Null
Next ctl
End Sub

(Range.Find method (Excel

مثال زیر که از متد بالا نشات گرفته محتویات رنج تعریف شده اگر 2 بود را به 5 تغییر میدهد.

This example selects cell A154 on Sheet1 and then scrolls through the worksheet to display the range

Application.Goto Reference:=Worksheets("Sheet1").Range("A154"),     scroll:=True

Jump

("Set ws2=Sheets("Sheet2

ws2.Range("A" & RowNum).Select

اکسل

پروفسور اکسس ایران یکشنبه 27 مرداد 1398 ساعت 19:32

0 نظر

CHOOSE - OFFSET - INDEX

(CHOOSE(position in list,item1,item 2, etc=

تابع Choose طبق عدد 3 ( موقعیت در لیست ) آیتم 3 را در نظر میگیرددکه C1:C3 است و تابع SUM هم این رنج را جمع میزند.

((SUM(CHOOSE(3,A1:A3,B1:B3,C1:C3,D1:D3=

returns the sum of cells C1 to C3

محتویات C1 را برمیگرداند که آرگومان دوم ردیف ( 0 ) به سمت پائین و آرگومان سوم ستون ( 2 ) به سمت راست است . در اینج پارامتر آرگومان Rows صفر است پس در همان ردیف میماند و با پارامتر آرگومان Cols که 2 است از سل A1 دو سل غیر از خودش به سمت راست میرود و محتویات سل C1 را نمایش میدهد درصورتیکه این نمایش چندین سل باشد VALUE# را بر میگرداند این تابع درون تابع های دیگر نظیر SUM استفاده میشود.

(OFFSET(A1,0,2 =

returns the contents of C1 * A1-> B1->C1

در مثال زیر تابع OFFSET ... از سل A1 آغاز میشود در همان سل میماند و بغیر از ستونی که قرار دارد 2 ستون به سمت راست میرود که میشود C1 و نهایتا به 3 سل پایینتر ختم میشود یعنی C3 پس رنج میشود C1:C3 و نهایتا در تابع SUM محاسبه میشود البته اگر محتویات عدد باشد.

SUM(OFFSET(A1,0,2,3)) will return the sum of the=

range C1 to C3, being offset by zero rows and two columns and having a height of 3

تابع زیر در رفرنس یا آرایه ی مشخص شده محتویات سل را برمی گرداند با توجه به آرگومانهای دوم ( شماره ردیف در همان محدوده ) و سوم ( شماره ستون در محدوده ی تعیین شده در آرگومان اول ) .

شماره ردیف اجباری و ستون انتخابیست اگر شماره ردیف درنظرگرفته شود ذکر شماره ستون الزامیست

INDEX(A1:C1,3) will return the contents of C1=

If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array

اگر آرایه بیشتر از یک ردیف یا بیشتر از یک ستون داشته باشد و فقط شماره ی ردیف یا فقط شماره ی ستون استفاده شده باشد این تابع تمام ردیف یا تمام ستون را در آرایه برمی گرداند . مثل مثال زیر INDEX Sales,,1 که شماره ردیف در آن استفاده نشده در نتیجه کل ستون یک را بر میگرداند یعنی G22:G25

اکسل

پروفسور اکسس ایران شنبه 26 مرداد 1398 ساعت 21:21

0 نظر

چارت پویا و استفاده از چک باکس ( مقایسه ی مالی با سال های قبل )

کپی کردن دیتای جدول بالا به پایین و نامگذاری چک باکس ها به 2011 و 2012 و لینک به سل هایی که بین دوتا جدول است و True نوشته شده در جدول پایین بجای دیتاهای 2011 و 2012 باید نوشته شود اگر C7=True,C3,False و برای 2012 هم همینطور و چارت کشیده شود که باید دیتا سورس هاعوض شود.

اکسل چارت Dynamic Chart نمودار نمودارپویا چارت پویا

پروفسور اکسس ایران شنبه 26 مرداد 1398 ساعت 19:08

0 نظر

تابع OFFSET (احصاء رفرنس جدید باتوجه به جابجایی آن توسط آرگومانها )

مرجع یا رفرنسی به محدوده ای که تعداد مشخصی از ردیف ها و ستون ها از یک سلول یا دامنه سلول است را برمی گرداند. مرجع برگشتی می تواند یک سلول واحد یا طیف وسیعی از سلول باشد. می توانید تعداد ردیف ها و تعداد ستون هایی که باید برگردانید را مشخص کنید.

([OFFSET(reference, rows, cols, [height], [width

آرگومان دوم و سوم که اجباریست میتواند هم عدد مثبت باشد و هم منفی ولی دو تای آخر انتخابیست یا به قولی آپشنال حتما باید عدد مثبت باشد در غیر اینصورت با REF# مواجه خواهید شد... فرضا اگر Cols عدد ۵ در نظر گرفته شود جابجایی از رفرنس تعریف شده 5 کالمن به سمت راست می باشد( بغیر از خود ردیفی که قرار است در نظر گرفته شود برای حرکت کردن افقی ) یا اگر منفی باشد 5 کالمن به سمت چپ .اگر Cols یا Rows هم وجود نداشته باشد منظور تعداد ردیف ها بیشتر از آنچه باشد که در نسخ قدیم و جدید است خطای REF# دوراز انتظار نیست یا Refrens فرضا D2 باشد و شما در Cols عدد 4- را ذکر کنید.البته امتحان کنید تا صحت یا عدم آن اثبات شود.

در کل منظور این است که سلی را جابجا نمیکند فقط مرجعی میدهد که در توابع دیگر استفاده شود.

SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a 3-row by 1-column range that is 1 row below and 2 columns to the right of cell C2