Text to Columns در اکسل

اگر بدنبال جداکردن قسمتهای مختلف یک سلول  هستید و میخواهید از هر قسمت آن جداگانه استفاده کنید نگران نباشید. اکسل ابزاری فوق العاده در اختیار شما قرار داده است.                                                  بااین آموزش همراه باشید:                                                                                                           برای این کار کافیست به تب DATA سر بزنید و در منویData Tools نگاهی به Text to Columns بیندازید.

مهم نیست محتوای سلولتان ترکیبی از اعداد، متن و یا نماد و عملگرهای ریاضی باشد. براحتی می توانید اینکار را انجام دهید.                                                                                                                             

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

شاید از با خودتان بگویید می توانم از فرمول LEFT و یا RIGHT استفاده کنم اما محدودیت بزرگ این دو فرمول این است که از قبل باید برای آنها تعداد کاراکترها را مشخص کنید و لزوما ممکن است تمام اسامی یا شماره ها کاراکترهای مساوی نداشته باشند.

حالا کافیست روی گزینه Finish کلیک کنید تا تمام محتواهایی که بین آنها Space وحود دارد را از هم جدا کند:

اما فرض کنید میخواهید نام و نام خانوادگی باهم بیاید و فقط شماره ها جدا شوند برای اینکار بجای گزینه Finish باید Next را بزنید:

در پنجره باز شده درexcel دو خط در قسمت Data Preview مشخص است. این خطها نشان دهنده مکان دو Space موجود در سلول است یکی فاصیه بین نام و نام خانوادگی و دومی فاصله بین نام خانوادگی و شماره. برای اینکه میخواهید نام و نام خانوادگی با هم بیاید باید خط اول را پک کنید. برای اینکار می توانید روی خط اول دبل کلیک کنید.

نکته: اگر بخواهید محتوای سلول را به بخشهای بیشتری تقسیم کنید می توانید هرجای متن را که خواستید در قسمت Preview کلیک نمایید. همچنین اگر بخواهید جای برش محتوا را تغییر دهید می توانید با موس خطها را جابجا کنید.

حالا روی Next کلیک کنید تا به مرحله بعد بروید. در قسمت Destination باید مشخص کنید که محتواهای جدا شده در کجا قرار داده شود. در حالت پیش فرض روی همان ستونی است که محتوای اولیه وجود دارد و اگر finish را بزنید جایگزین متن اصلی می شود اما می توانید مثلا سلول B1 را انتخاب کنید تا نام و نام خانوادگی را در ستون B و شماره را در ستون C قرا دهد.

در قسمت data format نیز می توانید فرمت مورد نظرتان را انتخاب کنید.

 امید واریم ازاین آموزش اکسل لذت برده باشید

البته بجای تایپ حروف می‌توانید فرمول زیر را در اولین سلول کپی کرده (بجای تایپ حرف a) و سپس مشابه شکل بالا در سایر سلول‌ها اعمال کنید و بقیه مراحل را مشابه شکل بالا انجام دهید.

=MOD(ROW(A1)+n-1,n)

که برای مثال بالا بجای n باید عدد 3 قرار داد.

ابتدا داده‌های خود را در ستون A نرم‌افزار اکسل کپی کنید. سپس کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

SubOneCol2nCols()'n = 3
m = 9
k = 1
For i = 1 To m / n
For j = 1 To n
Cells(k, 1).Select
  Selection.Copy
   Cells(i, j + 2).Select
    ActiveSheet.Paste
    k = k + 1
    Next
Next
End Sub

SubTrsPose()
Dim Area As Range, i As Long
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each Area In Range("A1:A" & LR).SpecialCells(xlCellTypeConstants).Areas
 i = i + 1
 Range("B" & i).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
End Sub

SubOneCol2Cols()
k = 1
For i = 1 To 4
j = 1
   Do Until IsEmpty(Cells(k, 1))
   Cells(k, 1).Select
   Selection.Copy
   Cells(i, j + 2).Select
    ActiveSheet.Paste
    k = k + 1
    j = j + 1
    Loop
    k = k + 1
Next
End Sub

امید واریم ازاین آموزش نیز لذت برده باشید

اگر اعدادی که رقم اول آنها صفر است مثل (01234) تایپ کنیم Excel، صفر را حذف می کند زیرا صفر قبل از عدد معنی ندارد و یا اگر عدد مثبت را به همراه آن تایپ کنیم (مثل 23 +)، Excel، + آن را حذف می کند. همچنین اگر 2/6 را تایپ کنیم سلول 6 مارس را نمایش نمی دهد یعنی آن را تبدیل به فرمت تاریخ می کند                                                                                                                                                                                                                                                                              برای رفع این مشکلات باید عدد را تبدیل به متن کنیم برای این کار می توانیم یکی از علائم زیر را قبل از عدد بنویسیم:
' (آپستروف): این علامت باعث می شود عدد به همان صورت و با تراز چپ نوشته شود.
": این علامت باعث می شود عدد به همان صورت و با تراز راست نوشته شود.
^: این علامت باعث می شود عدد به همان صورت و با تراز وسط نوشته شود.
=: این علامت باعث می شود سلول با عدد تایپ شده پر شود.
1- داده های متنی: می توانیم هر نوع داده متنی را در سلولهای Excel وارد کنیم.
2- داده های نوع تاریخ: می توانیم بدون تنظیم خاصی داده های نوع تاریخ در سلولهای Excel وارد کنیم، کافی است در سلول تاریخی با فرمت yy/mm/dd یا yy-mm-dd وارد کنیم، در آینده با قالب بندی های تاریخ آشنا می شویم. با انجام این کار (-) تبدیل به (/) شده و سال به صورت چهار رقمی (19yy) نمایش داده می شود.
3- داده های نوع زمانی: می توانیم بدون تنظیم خاصی داده های نوع زمان در سلولهای Excel وارد کنیم، کافی است در سلول تاریخی با فرمت hh:mm:ss وارد کنیم، در آینده با قالب بندی های زمان آشنا می شویم.
2- اطلاعات متنی:می توانیم در سلولهای Excel هر متن دلخواهی را چه به فارسی و چه انگلیسی تایپ کنیم.
توانایی Excel برای در نظر گرفتن اعداد مثل متن:
اگر عددی مثل 2/6 را وارد کنیم، پس از Enter کردن، Excel، 6 مارس را نمایش می دهد. یعنی این اعداد را به تاریخ در نظر می گیرد. برای اینکه این اعداد با همین قالب نوشته شد قبل از آن علامت (') آپستروف قرار میدهیم. یعنی بنویسیم ('6/2)
3- اطلاعات از نوع تاریخ:
اگر در سلول Excel یک تاریخ به فرم dd-mm-yy یا dd/mm/yy وارد کنیم به طور اتوماتیک قالب سلول تبدیل به قالب تاریخ شده و خط فاصله ها تبدیل به (/) شده و سال در چهار رقم نمایش داده می شود.
4- اطلاعات از نوع زمان:
می توانیم در سلول Excel یک زمان را با فرمت H:M:S وارد کنیم که قالب سلول به طور اتوماتیک زمان می شود.
5- اطلاعات از نوع یادداشت:
این نوع اطلاعات توضیحات یا یادداشتهایی هستند که بر روی سلول ظاهر شده و در مورد آن توضیح می دهند. در مباحث بعدی با نحوه ایجاد توضیحات آشنا می شویم.
توجه:

 فرمول نویسی در اکسل
عملگرها در Excel:
1- عملگرهای محاسباتی: از عملگرهای ریاضی + (برای جمع)، - (برای تفریق)، * (برای ضرب) و / (برای تقسیم)، ^(برای توان) و % (برای درصد) در Excel استفاده کنیم.
ایجاد یک فرمول ریاضی
برای ایجاد یک فرمول ریاضی به یکی از روشهای زیر عمل می کنیم.
روش اول: در سلولی که می خواهیم نتیجه نوشته شود یا در خط فرمول علامت = را تایپ کرده، سپس آدرس عملوند اول را تایپ کرده عملگر را می نویسیم و بعد آدرس عملوند دوم و بهمین ترتیب ادامه می دهیم.
روش دوم: در سلولی که می خواهیم نتیجه نوشته شود علامت = را تایپ کرده، سپس بر روی سلولی که حاوی اولین عملوند است کلیک کرده، سپس عملگرد را تایپ کرده و بعد بر روی سلول حاوی در بین عملوند کلیک می کنیم و به همین ترتیب ادامه می دهیم.
* تقدم عملگرهای ریاضی:
اگر در یک فرمول چندین عملگر ریاضی داشه باشیم، این عملگرها دارای تقدم هستند که عبارتند از:
()
^
%
*و/
+و-
می بینیم که * و / در یک خط نوشته شده اند زیرا این دو دارای تقدم یکسان هستند و اگر در فرمولی هر دو عملگر بودند از سمت چپ اولین عملگر انجام می شود.
2- عملگر متنی: برای عبارتهای متنی می توانیم از عملگر & (الحاق) استفاده کنید. با قرار دادن این عملگر بین دو متن آن دو را به هم پیوند می دهید.
3- عملگر آدرس: برای بیان محدوه ای از آدرس می توانیم از عملگر: استفاده کنیم. کافی است آدرس ابتدای محدوده را نوشته، سپس عملگر آدرس را تایپ کنیم و بعد آدرس انتهای محدوده.

 امید واریم ازاین آموزش اکسل استفاده ی لازم رابرده باشید

جمع شرطی چیست؟

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

برای انتقال بهتر مفهوم جمع شرطی، در ادامه با ذکر چند مثال این مفهوم را شرح می‌دهیم.

مثال 1) جدولی را در نظر بگیرید که میخواهید:در محدودهA1:A6  از جدول زیر جمع اعداد کوچکتر از 28 را محاسبه کنید

برای محاسبه این جمع از تابع sum دراکسل نمی‌توان استفاده کرد زیرا یک شرط وجود دارد و آن این است که جمع اعدادی را محاسبه کنید که کوچکتر از 25 باشند. برای حل این مشکل می‌توان از تابع Sumifدر excel  استفاده کرد.

• ساختار تابع در  Sumif به شکل زیر می‌باشد:

=SUMIF(range, criteria,[sum_range])

range: همان محدوده‌ای می‌باشد که قصد داریم شرط را بر آن اعمال کنیم. این محدوده می‌تواند شامل عدد، نام، آرایه و یا محدوده‌ای از اعداد باشد. (سلول‌های خالی و کاراکترها محاسبه نمی‌شود). در مثال فوق محدوده A1:A6 است.

criteria: همان شرط ما می‌باشد. این شرط می‌تواند شامل عدد، عبارت، یک سلول، متن و تابع باشد. در مثال فوق شرط اعداد کوچکتر از 28 است.

نکته 1: کلیه عبارات و شرط‌های ریاضی باید داخل گیومه " " باشد مگر اینکه شرط شما عدد باشد.

نکته 2: در شرایط مشابه مثال فوق چنانچه بخواهید بجای نوشتن عدد 28 در تابع آدرس آن یعنی A6 را در تابع درج کرد باید شرط را بصورت زیر بنویسید:

"

sum_range: این گزینه اختیاری می‌باشد. زمانی از این آرگومان استفاده می‌کنیم که Range  ما شامل اعداد نباشد و یا اینکه اعدادی که قصد داریم مجموع آن‌ها را محاسبه کنیم در محدوده range قرار نداشته باشند که در مثال بالا هیچ یک از دو حالت فوق برقرار نیست. چون هم Range ما شامل اعداد است و هم اینکه اعدادی که قصد داریم مجموع آن‌ها را محاسبه کنیم در محدوده range قرار دارند. بنابراین در مثال 1 از آرگومان sum_range استفاده نمی‌کنیم.

بنابراین مطابق توضیحات بالا برای مثال 1 تابع Sumif به صورت زیر نوشته می‌شود.

=Sumif(A1:A6,")

خروجی فرمول بالا برابر است با 60.

در مثال بالا فقط از دو آرگومان range و criteria استفاده شده‌است. اگر بخواهیم از هر 3 آرگومان Range و Criteria و Sum_range استفاده کنیم مثال 2 را مشاهده کنید.

مثال 2) جدولی را در نظر بگیرید که مربوط به پخش اقلام در یک منطقه زلزله زده می‌باشد.  ومی خواهیم مجموع کل “آب معدنی”های توزیع شده در کلیه مناطق را محاسبه کنید

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

=Sumif(B2:B10,"آب معدنی",C2:C10)

=SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2],…)

=Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"جنوب")

=Sum(Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,{"جنوب","شمال"})

=Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"جنوب")+Sumifs(C2:C10,B2:B10,"آب معدنی",A2:A10,"شمال")

=SUMIFS(B2:B8,A2:A8,">=2014-03-04",A2:A8,")

 امید واریم این آموزش اکسل موردتوجه قرار گرفته شده باشد

اکثر کاربران نرم‌افزار اکسل برای یک بار هم که شده با برازش منحنی برای داده‌های x و y یا Trend Line برخورد داشته‌اند. در واقع Trend Line به شما کمک می‌کند تا علاوه بر تشخیص روند تغییر داده‌ها، بتوانید تا حدودی وضعیت داده‌ها را پیش‌بینی (Forecasting) کنید. در ادامه مطلب با آموزش همراه شوید تا علاوه بر جزئیات Trend Line، با توابع کاربردی اکسل برای برازش منحنی نیز آشنا گردید.

از Trend Line درEXCEL فقط می‌توان در منحنی‌های Area،Bar،Column،Line و XY استفاده کرد.به خاطر داشته باشید که نمی‌توانید در نمودارهای 3D،Radar،Pie،Doughnut و Bubble از Trend Line استفاده کنید.

برای اضافه‌ کردن Trend Line، پس از راست کلیک کردن روی منحنی داده‌ها، گزینه‌ی Add Trendline را انتخاب کنید تا پنجره Format Trendline باز شود.

در پنجره Format Trendline

در قسمت راست پنجره Format Trendline یعنی قسمت Trendline Options، بخش‌های زیر وجود دارد:

بخش اول: بخش Trend/Regression Type انواع Trendline‌ها را نشان می‌دهد که به شرح زیر است:

• Exponential / نمایی؛ با فرمول Y=C.e^bx که b و c اعداد ثابت هستند.

          - نکته: هنگامی که داده‌ها شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

• Linear / خطی؛ با فرمول Y=m.x+b که m شیب خط و b عدد ثابت (عرض از مبدا) است.

• Logarithmic / لگاریتمی؛ با فرمول Y=c.Lnx+b که c و b اعداد ثابت هستند. 

• Polynomial / چند جمله‌ای؛ با فرمول Y=b+c₁x+c₂x²+c₃x³+...+c_nxⁿ که در آن c عدد ثابت است.

• Power / توانی؛ با فرمول Y=C.x^b که b و c اعداد ثابت هستند.
  - نکته: هنگامی که داده‌ها شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

• Moving Average / میانگین متحرک؛ با فرمول F_t=(A_t+A_t-1+...+A_t-n+1)/n

بخش دوم، بخش TrendLine Name می‌باشد.

بخش سوم، بخش Forecast یا پیش‌بینی می‌باشد که بر اساس نوع معادلات انتخابی در بخش اول، yهای قبل و یا بعد متناظر با xهای داده شده را پیش بینی می‌کند.

Set Intercept هم برای تعیین عرض از مبداء دلخواه می‌باشد.

با تیک زدن دو گزینه آخر یعنی Display Equation on chart و Display R-squared value on chart، به ترتیب معادله و ضریب رگرسیون (R²) متناظر با نوع Trendline انتخاب شده، روی نمودار نمایش داده می‌شود. در رگرسیون خطی ضریب رگرسیون مجذور ضریب همبستگی (R) است.

* نکته: چنانچه می‌خواهید از معادله‌ی پیشنهادی اکسل جهت درون‌یابی یا برون‌یابی استفاده کنید باید به دو نکته زیر توجه کنید:

1- معادله‌ای مناسب است که ضریب رگرسیون آن نزدیک به یک باشد مثلا 0.99.

2- اکسل بصورت پیش فرض، ضرایب معادله‌ را تا 2 رقم اعشار نمایش می‌دهد. برای اینکه بتوانید با استفاده از معادله، y متناظر با یک x را محاسبه کنید برای دقت بیشتر باید از معادله‌ای استفاده کنید که تعداد ارقام اعشاری بیشتری داشته باشد. برای این کار مطابق شکل زیر روی معادله خط، راست کلیک کنید و گزینه Format trendline label را انتخاب کنید.

در پنجره باز شده زیر در قسمت Category گزینه Number را انتخاب و در قسمت Decimal places تعداد ارقام بعد از ممیز را افزایش دهید. دکمه Close را بزنید و از معادله جدید استفاده کنید.

علاوه بر استفاده گرافیکی از ابزار Trend Line، می‌توان از توابع اکسل نیز اطلاعات مفیدی بدون رسم نمودار به دست آورد.
1- تابع Slope: محاسبه شیب رگرسیون خطی.

=SLOPE(Known Y values, Known X values)

برای مثال زیر شیب خط تقریبا 2.15 می‌باشد.

=SLOPE(B2:B6,A2:A6) = 2.15

2- تابع Intercept: محاسبه عرض از مبدا رگرسیون خطی.که برای مثال بالا تقریبا 0.47- می باشد.

=INTERCEPT(Known Y values, Known X values)

=INTERCEPT(B2:B6,A2:A6) = -0.47

یعنی در واقع معادله رگرسیون خطی این مثال برابر است با:        y = 2.15*x -0.47

3- تابع Forecast: برای پیش‌بینی y متناظر با یک x جدید بر مبنای رگرسیون خطی.

=FORECAST(New X Value, Known Y values, Known X values)
=FORECAST(15,B2:B6,A2:A6) = 31.778

4- تابع GROWTH: برای پیش بینی y متناظر با یک x جدید بر مبنای رگرسیون نمائی.

=GROWTH(Known Y Values, Known X Values, New X Values, Const)
=GROWTH(B2:B6,A2:A6,15,TRUE) = 48.68

عبارت Const در تابع GROWTH، دارای دو حالت True (محاسبه b) و False (مقدار 1 برای b) می‌باشد.

شاید برای شما هم این موضوع پیش آمده است که بخواهید وضعیت تعداد زیادی داده را در نمودار مشاهده کنید اما بایدبرای هر داده نمودار مجزایی بکشید که وقت و فضای زیادی صرف این کار می شود مخصوصاً اگر بخواهید برای یک داده وضعیت را درچندنمودارمشاهده کنید.

آموزشی که آماده کردیم راه حل این مشکل است.
برای این کار مطابق دستور العمل زیر عمل می کنیم
1- ایجاد جدول ابتدا جدولی را طراحی کنید
مطابق معمول هدف از ایجاد اینچنین نمودار هایی زیبایی و جلب نظر مخاطب است.پس این بار هم پاره ای از اقدامات برای زیبایی نمودار خود انجام می دهیم.
2- از قسمت INSERT گزینه SHAPES را انتخاب می کنیم و شکل مربع را بر می گزینیم.و رنگ خاکستری را برای ان انتخاب می کنیم
3- از مسیر فوق شکل مثلث را انتخاب کرده (رنگ مشکی)
4- و در نهایت شکل یک مربع جهت دار را انتخاب می کنیم (رنگ سیز)


5- حالا از قسمت DEVELOPERدراکسل(برای اضافه کردن این قسمت در ریبون خود مسیر زیر را دنبال کنید:
FILE – OPTION – CUSTOMIZE RIBBON –
و از کادر سمت راست تیک مربوطه را بزنید)
وارد قسمت INSERT گزینه کامبو باکس را انتخاب می کنیم

روی باکس ایجاد شده کلیک راست کرده و گزینه FORMAT CONTROL را انتخاب و در کادر ظاهر شده در قسمت INPUT RANGE محدوده REGION را انتخاب می کنیم و در قسمت CELL LINK یک سلول خالی اختصاص دهید.در اینجا ما سلول C33 را اختصاص دادیم.سپس OK
حالا شما از کرکره ایجاد شده هر محدوده ای را انتخاب کنید عدد متناظر با آن محدوده در سلول C33 نمایش داده می شود

6-
دقت داشته باشید که در جدول اول عدد 4 مقابل SELECTED REGION همان خانه C33 است که در CELL LINK انتخاب کرده ایم.

در فرمول های فوق به دلیل اینکه هر محدوده از جدول در قسمت DEFINE برای اکسل تعریف شده نام آن نمایش داده شده است تا شما نیز بهتر متوجه محدوده انتخاب شده شوید.شما می توانید محدوده ای که  نام آن نمایش داده شده را به صورت دستی و یا با موس انتخاب کنید.
لازم به ذکر است که فرمولهای index و match از خانوانده lookup بوده و برای جستجو بکار می روند.در اینجا فرمول index به این معنی است که مثلاً از ستون then عدد واقع در ردیف 4 را بخوان..
در شکل های فوق جدول calculations برای معرفی و نمایش، جدول break-up و xy نیز که در ادامه برا آنها نمودار های bar و scatter ترسیم خواهد شد.
7- برای رسم نمودار bar برای جدول break – up محدوده جدول را انتخاب کرده

و برای رسم نمودار scatter برای جدول x,y ابتدا محدوده جدول را انتخاب کرده

حالا نمودار های ایجاد شده را در مربع خاکستری رنگ که اول ایجاد کردیم قرار می دهیم.
8- در نمودار scatter فوق در نقاط ابتدا و انتهای نمودار یک دایره و یک فلش مشاهده می شود.
این نمودار با توجه به جدول x,y از چهار نقطه تشکیل شده است شما لازم است روی نتقه ابتدایی و انتهایی جداگانه کلیک کرده به طوری که یکی از انها فقط انتخاب شود حالا  مسیر را دنبال کنید layout – format selection

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

9-
ما در فرمول ها هر چیز را که بخواهیم به عنوان متن به نمایش در بیاید بین " " مینویسیم.
علامت & برا ی اتصال متن و فرمول استفاده می شود.
فرمول text: اگر بخواهید عددی را به فرمت خاصی نمایش دهید استفاده می شود

• شما برای کسب اطلاعات بیشتر می توانید در قسمت Help نرم افزار text را تایپ کرده سپس text function را انتخاب کرده تا با کارایی بیشتر این فرمول آشنا شوید.
در اینجا ما می خواهیم عدد مورد نظر به شکل درصد نشان داده شود
فرمول ABS: این فرمول قدر مطلق عدد را بر می گرداند
حالا زمان اختصاص این فرمول به کادر سبز رنک است. روی کادر سبز رنک کلیک کرده تا به حالت انتخاب در بیاید سپس در نوار فرمول، فرمول زیر را بنویسید و اینتر را بزنید
=$C$46
حالا نمودار شما اماده است.                                                                 امیدوارم این آموزش اکسل  مورد استفاده شما قرار بگیرد و از آن لذت برده باشید

 نخستین مرحله‌ از رسم نمودار، وارد کردن داده‌ها در یک کاربرگ(worksheet) از برنامه‌ی اکسل می‌باشد. نام متغیرها یا گروه داده‌ها را نیز بالای ستون مربوط به آن بنویسید. معمولا متغیر یا گروه داده را در ستون اول و فراوانی یا مقدار هر گروه یا متغیر را در ستون بعد روبروی آن وارد می‌کنند.excelمتغیر یا گروه داده را به صورت خودکار، روی محور افقی و فراوانی یا مقادیر را روی محور عمودی نشان می‌دهد.   

انواع نمودارهای پیش‌فرض در برنامه‌ی اکسل وجود دارد که هر کدام دارای کاربرد ویژه‌ی خود می‌باشد. به عبارتی هر کدام از انواع نمودارها برای بیان داده‌های خاصی مناسب‌ترند. در  آموزش  امروزپرکاربردترین انواع نمودارها و نحوه‌ی رسم آن‌ها توضیح خواهیم داد.

نمودار میله ای:

هنگامی که داده ها یا متغیرهای گسسته یا طبقه‌بندی شده داریم، نمودار میله‌ای یک نمودار گویا برای نشان‌‌دادن داده‌هاست. برای مثال تعداد فرزندان خانواده یا تعداد افرادی که به یک سوال پاسخ مثبت داده‌اند، داده‌های گسسته‌اند. متغیر دما را می‌توان به عنوان مثال به سه دسته‌ی 34-25، 44-35 و 54-45 طبقه بندی کرد. برای تحلیل بسیاری از پرسش‌نامه‌ها نیز نمودار میله‌ای می‌تواند مناسب باشد.

برای رسم نمودار در اکسل داده‌ها را وارد کنید:

سپس ستون فراوانی‌ها را انتخاب کنید.

سپس از منوی بالا سربرگ Insert را کلیک کنید. انواع نمودار را در آن جا می‌بینید. برای رسم نمودار میله‌ای از الگوی Column یا Bar استفاده کنید.

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

در حالتی که بیش‌تر از یک متغیر وجود داشته باشد نیز می‌توان از نمودار میله‌ای استفاده کرد. مثلا اگر غلظت یک ماده را در سه دمای مختلف اندازه‌گیری کرده باشیم، برای رسم نمودار میله‌ای داده‌ها را وارد می‌کنیم:

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

مجددا با رفتن به قسمت insert و انتخاب Column یا Bar نمودار میله‌ای مورد نظرتان را انتخاب کنید. با کلیک روی نمودار مورد نظر، نمودار میله‌ای رسم می‌شود.

نمودار دایره‌ای:

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

 برای رسم نمودار در اکسل ابتدا داده‌ها را وارد کنید:

با رفتن به قسمت Insert و انتخاب Pie نمودار دایره‌ای یا قطاعی مورد نظرتان را انتخاب کنید. با کلیک روی آن، نمودار دایره ای رسم می‌شود.

نمودار پراکنش:

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

برای رسم نمودار ابتدا داده‌ها را در اکسل وارد می‌کنیم.

سپس ستون‌های مورد نظر را انتخاب می‌

با رفتن به قسمت Insert و انتخاب الگوی Scatter نمودار مورد نظرتان را انتخاب کنید. با کلیک روی آن، نمودار پراکنش رسم می‌شود.

هم‌چنین برای پراکندگی داده‌هایی که دارای دو صفت هستند و مقایسه‌ی فراوانی، این نمودار مناسب به نظر می‌رسد.

نمودار خطی:

این نمودار شبیه نمودار پراکنده است با این تفاوت که چند گروه دارای دو صفت در محور عمودی و افقی نشان داده می‌شوند و نقاط مربوط به یک گروه با خط به هم وصل می‌شوند. این نوع نمودار برای تاکید بر روند و تغییرات مقادیر در طول زمان به کار می‌رود. برای رسم این نوع نمودار کافی است مانند روش‌های قبل داده‌ها را وارد و انتخاب کنید و با رفتن به قسمت Insert و انتخاب الگوی Linear نمودار موردنظرتان را انتخاب کنید.

هنگام انتخاب کردن (مارک کردن) داده‌ها توجه داشته باشید که تنها ستون مربوط به متغیر وابسته را انتخاب کنید و نام ستون‌ها را انتخاب نکنید. برنامه به طور خودکار ستون مربوط به متغیر مستقل را روی محور افقی و ستون مربوط به متغیر وابسته را روی محور عمودی نشان خواهد داد.

این نمودار پرکاربردترین نوع نمودار در اکسل است.

برای تغییر مشخصات نمودار می‌توانید روی آن کلیک کنید. در بالای صفحه، قسمت منو، سربرگ‌های Design، Layout و Format پدیدار می‌شوند. از آن‌ها برای ایجاد تغییرات گوناگون در نمودارتان استفاده کنید.

امیدواریم آموزش اکسل امرو ز نیز مورد اسفاده شما عزیزان قرار بگیرد

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

تجزیه متن یک سلول به چند سلول

برای تجزیه محتویات یک سلول دو روش وجود دارد:

1- نوشتن فرمول 

2- ابزار در در

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

تجزیه متن یک سلول به چند سلول

برای تجزیه محتویات یک سلول دو روش وجود دارد:

1- نوشتن فرمول 

2- ابزار Text To Columns

 قبل از بیان این قسمت، لازم است با تعدادی از توابع جداسازی و یکپارچه سازی متون آشنا شوید.

• تابع Right:

این تابع تعداد مشخصی از آخرین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت راست و متن فارسی از سمت چپ). ساختار (Syntax) این تابع به شکل زیر است:

=RIGHT(text,num_chars)

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

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از انتهای رشته متن استخراج شود.

• تابع Left:

این تابع تعداد مشخصی از اولین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت چپ و متن فارسی از سمت راست). ساختار این تابع به شکل زیر است:

=LEFT(text,num_chars)

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

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از ابتدای رشته متن استخراج شود.

• تابع Mid:

این تابع تعداد مشخصی از کاراکترهای یک رشته متن را با توجه به موقعیت شروع در رشته متن و تعدادی که شما مشخص می‌کنید، استخراج می‌کند. ساختار این تابع به شکل زیر است:

=MID(text,start_num,num_chars)

ورودی اول رشته متنی است که می خواهید قسمتی از آن را استخراج کنید، ورودی دوم موقعیت اولین کاراکتر و ورودی سوم تعداد کاراکترهایی است که می‌خواهید از متن خارج کنید.

•  تابع Len:

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

=LEN(text)

این تابع یک آرگومان بیشتر ندارد و آن متن مورد نظر است که می‌خواهید تعداد کاراکترهای آن‌را محاسبه کنید.

تجزیه متن یک سلول

برای این کار می‌توان به دو روش عمل نمود:

روش اول) از طریق نوشتن فرمول

با استفاده از فرمولهایی که در بالا توضیح داده شد می توان محتوای یک سلول را تجزیه نمود. ممکن است دو حالت پیش آید:

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

در تصویر زیر نحوه انجام این کار برای حالت اول نشان داده شده است.

مثال) جداسازی نام و نام‌ خانوادگی

همانطور که در تصویر زیر مشاهده می‌کنید برای جداسازی نام و نام خانوادگی چند تابع درون تابعی دیگر نوشته شده است. جدا کردن نام از نام‌ خانوادگی بوسیله تابع LEFT انجام می‌شود. برای ورودی دوم این تابع (تعداد کاراکترهای مورد نظر که قرار است استخراج شوند یعنی تعداد کاراکترهای نام) از تابع FIND استفاده می‌شود که در اینجا توضیح داده شده است. این تابع موقعیت کاراکتر فاصله را درون رشته متن می‌یابد و سپس یک واحد از آن کم می‌گردد تا موقعیت آخرین حرف نام مشخص شود.
برای جدا کردن نام خانوادگی، تابع MID بکار گرفته می‌شود. برای ورودی دوم این تابع (موقعیت اولین کاراکتر نام خانوادگی) از تابع FIND استفاده می‌گردد. این تابع موقعیت کاراکتر فاصله را درون رشته متن می‌یابد و سپس یک واحد به آن اضافه می‌گردد تا موقعیت اولین حرف نام خانوادگی تعیین شود. برای ورودی سوم تابع MID می‌بایست به طریقی مشخص شود که از اولین حرف نام خانوادگی به بعد چه تعداد کاراکتر باید استخراج شود. این کار به راحتی با استفاده از تابع LEN امکانپذیر است.

روش دوم) از طریق ابزار Text To Columns

در اینجا نیز ممکن است همان دو حالت پیش آید.

حالت اول: در حالتی که تعداد کاراکترهای مورد نظر برای استخراج ثابت و معین هستند به طریق زیر عمل کنید:

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Fixed Width را انتخاب نمایید.
4. بوسیله کلیک کردن در نقاطی که می‌خواهید کاراکترها از یکدیگر جدا شوند، ستون‌ها را تجزیه کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده‌ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

حالت دوم: برای حالتی مشابه جدا کردن نام و نام خانوادگی، به طریق زیر عمل کنید:

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Delimited را انتخاب نمایید.
4. گزینه Space را انتخاب کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

ترکیب چند سلول در یک سلول دراکسل

برای این کار می توان به دو روش عمل نمود:

روش اول) در روش اول از کاراکتر & بین محتوای سلول‌ها استفاده می شود. فرض کنید می‌خواهیم نام و نام خانوادگی که در سلول‌های جداگانه درج شده‌اند را در یک سلول ترکیب کنیم. برای اینکه فاصله‌ای نیز بین نام و نام خانوادگی درج شود، فاصله را داخل گیومه قرار دهید و به فرمول بی‌افزایید. در تصویر زیر چگونگی انجام این کار نشان داده شده است.

روش دوم) از تابع CONCATENATE استفاده می‌شود. ورودی های این تابع داده‌هایی است که باید با یکدیگر ترکیب شوند. برای مثال زیر این تابع سه ورودی دارد؛ ورودی اول نام، ورودی دوم یک فاصله و ورودی سوم نام خانوادگی می باشد.

Text To Columns

 قبل از بیان این قسمت، لازم است با تعدادی از توابع جداسازی و یکپارچه سازی متون آشنا شوید.

• تابع Right:

این تابع تعداد مشخصی از آخرین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت راست و متن فارسی از سمت چپ). ساختار (Syntax) این تابع به شکل زیر است:

=RIGHT(text,num_chars)

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

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از انتهای رشته متن استخراج شود.

• تابع Left:

این تابع تعداد مشخصی از اولین کاراکترهای یک رشته متنی را استخراج می‌کند (متن انگلیسی از سمت چپ و متن فارسی از سمت راست). ساختار این تابع به شکل زیر است:

=LEFT(text,num_chars)

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

آرگومان Num_chars: تعداد کاراکترهایی است که می‌خواهید از ابتدای رشته متن استخراج شود.

• تابع Mid:

این تابع تعداد مشخصی از کاراکترهای یک رشته متن را با توجه به موقعیت شروع در رشته متن و تعدادی که شما مشخص می‌کنید، استخراج می‌کند. ساختار این تابع به شکل زیر است:

=MID(text,start_num,num_chars)

ورودی اول رشته متنی است که می خواهید قسمتی از آن را استخراج کنید، ورودی دوم موقعیت اولین کاراکتر و ورودی سوم تعداد کاراکترهایی است که می‌خواهید از متن خارج کنید.

•  تابع Len:

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

=LEN(text)

این تابع یک آرگومان بیشتر ندارد و آن متن مورد نظر است که می‌خواهید تعداد کاراکترهای آن‌را محاسبه کنید.

تجزیه متن یک سلول

برای این کار می‌توان به دو روش عمل نمود:

روش اول) از طریق نوشتن فرمول

با استفاده از فرمولهایی که در بالا توضیح داده شد می توان محتوای یک سلول را تجزیه نمود. ممکن است دو حالت پیش آید:

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

مثال) جداسازی نام و نام‌ خانوادگی

همانطور که در تصویر زیر مشاهده می‌کنید برای جداسازی نام و نام خانوادگی چند تابع درون تابعی دیگر نوشته شده است. جدا کردن نام از نام‌ خانوادگی بوسیله تابع LEFT انجام می‌شود. برای ورودی دوم این تابع (تعداد کاراکترهای مورد نظر که قرار است استخراج شوند یعنی تعداد کاراکترهای نام) از تابع FIND استفاده می‌شود که در اینجا توضیح داده شده است. این تابع موقعیت کاراکتر فاصله را درون رشته متن می‌یابد و سپس یک واحد از آن کم می‌گردد تا موقعیت آخرین حرف نام مشخص شود.
برای جدا کردن نام خانوادگی، تابع MID بکار گرفته می‌شود. برای ورودی دوم این تابع (موقعیت اولین کاراکتر نام خانوادگی) از تابع FIND استفاده می‌گردد. این تابع موقعیت کاراکتر فاصله را درون رشته متن می‌یابد و سپس یک واحد به آن اضافه می‌گردد تا موقعیت اولین حرف نام خانوادگی تعیین شود. برای ورودی سوم تابع MID می‌بایست به طریقی مشخص شود که از اولین حرف نام خانوادگی به بعد چه تعداد کاراکتر باید استخراج شود. این کار به راحتی با استفاده از تابع LEN امکانپذیر است.

روش دوم) از طریق ابزار Text To Columns

در اینجا نیز ممکن است همان دو حالت پیش آید.

حالت اول: در حالتی که تعداد کاراکترهای مورد نظر برای استخراج ثابت و معین هستند به طریق زیر عمل کنید:

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Fixed Width را انتخاب نمایید.
4. بوسیله کلیک کردن در نقاطی که می‌خواهید کاراکترها از یکدیگر جدا شوند، ستون‌ها را تجزیه کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده‌ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

حالت دوم: برای حالتی مشابه جدا کردن نام و نام خانوادگی، به طریق زیر عمل کنید:

1. سلول‌هایی که قرار است تجزیه شوند را انتخاب نمایید.
2. از تب Data، بر روی گزینه Text to columns کلیک کنید.
3. گزینه Delimited را انتخاب نمایید.
4. گزینه Space را انتخاب کنید.
5. در فیلد Destination آدرس سلولی که می‌خواهید داده ها درج شوند را وارد کنید و بر روی Finish کلیک نمایید.

ترکیب چند سلول در یک سلول

برای این کار می توان به دو روش عمل نمود:

روش اول) در روش اول از کاراکتر & بین محتوای سلول‌ها استفاده می شود. فرض کنید می‌خواهیم نام و نام خانوادگی که در سلول‌های جداگانه درج شده‌اند را در یک سلول ترکیب کنیم. برای اینکه فاصله‌ای نیز بین نام و نام خانوادگی درج شود، فاصله را داخل گیومه قرار دهید و به فرمول بی‌افزایید. در تصویر زیر چگونگی انجام این کار نشان داده شده است.

روش دوم) از تابع CONCATENATE استفاده می‌شود. ورودی های این تابع داده‌هایی است که باید با یکدیگر ترکیب شوند. برای مثال زیر این تابع سه ورودی دارد؛ ورودی اول نام، ورودی دوم یک فاصله و ورودی سوم نام خانوادگی می باشد.

امید واریم آموزش اکسل امروز نیز مورد توجه شما عزیزان قرار بگیرد

آدرس دهی در اکسل از مفاهیم پایه و اساسی است. درک آدرس و کاربرد صحیح آن از اصول اولیه آموزشی اکسل است. هر سلول در اکسل دارای دو مشخصه اصلی مقدار و نام است. به عبارت دیگر زمانی که یک داده‌ در سلولی در اکسل ذخیره می‌شود، دو موجودیت از آن برای ما اهمیت می‌یابد. اول این که مقدار داده چقدر است و دوم این که این مقدار در کجا ذخیره شده است. مقدار توسط کاربر به روش‌های مختلف و به صورت مستقیم یا غیر مستقیم وارد اکسل می‌شود. ولی نام (آدرس) سلول موجودیتی است که در نحوه تشکیل سلول نهفته است. همانطوری که در محدوده در اکسل گفته شد، هر سلول از تقاطع یک ستون و یک سطر تشکیل می‌شود. آدرس سلول نیز از نام ستون و سطر گرفته می‌شود. سلول A1 از تقاطع ستون A و سطر 1 تشکیل شده است.

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

آدرس دهی در اکسل و تقسیم بندی انواع داده

با توجه به مشخصه‌های مهم ذکر شده برای یک داده (مقدار و آدرس)، می‌توان دو نوع اصلی تقسیم بندی داده‌ها را به صورت زیر تعریف کرد. این تعاریف صرفاً برای استفاده در مفهوم آدرس دهی به کار برده می‌شوند.

داده متغییر: داده‌ای که مقدار یا آدرس آن در آرگومان فرمول تغییر کند داده متغییر گفته می‌شود. ممکن است در یک فرمول از دو داده با دو آدرس متفاوت ولی با مقدار مساوی استفاده شود، این داده‌ها نیز به واسطه تغییر کردن آدرسشان، متغییر محسوب می‌شوند.

 در سلول‌های B8 و B9 با اینکه مقدار سلول‌های C8 و C9 مساوی و برابر 2/47 است، ولی به واسطه تغییر در آدرس، داده متغییر محسوب می‌شوند.

داده ثابت: داده‌ای که فارغ از مقدار، صرفاً آدرسش در آرگومان فرمول ثابت باشد داده ثابت گفته می‌شود. ممکن است در فرمولی از یک داده با آدرس ثابت استفاده شود ولی مقدار داده به وسیله کاربر یا توابع دیگر (مانند تابع RAND)  تغییر کند، این داده نیز ثابت در نظر گرفته می‌شود. اعداد ثابتی که بعضاً در فرمول‌ها استفاده می‌شوند نیز، نوعی داده ثابت هستند.

آدرس دهی در اکسل چگونه انجام می‌شود؟

زمانی که فرمولی در یک سلول نوشته می‌شود و در آرگومان آن داده‌هایی فراخوانی می‌شوند، اکسل به صورت پیش فرض، آدرس سلول حاوی فرمول (سلول فرمول) و آدرس سلول حاوی داده (سلول داده) را نسبت به یکدیگر می‌سنجد. به عبارت دیگر به هنگام آدرس دهی در اکسل، صرفاً فاصله سطرها و ستون‌هایی که سلول‎ فرمول و سلول داده از هم دارند اندازه گیری می‌شود.

آدرس دهی در اکسل به صورت نسبی

 در سلول B6 نوشته شده است که وزن کل آرماتور شماره 16 را با استفاده از طول (سلول E6)، تعداد (سلول D6) و وزن واحد (سلول C6) آن محاسبه می‌کند. این یک فرمول ساده بوده و با استفاده از عملگر ضرب مقدار سلول‌های D6، E6 و C6 را در هم ضرب کرده و حاصل را در سلول B6 می‌نویسد. در این فرمول آدرس ها به صورت نسبی نوشته شده‌اند و اکسل برای اجرای این فرمول (با توجه به آدرسی که داده‌ شده است) به ترتیب مقادیرِ یک ستون، دو ستون و سه ستون بعد از سلول فرمول را در هم ضرب می‌کند. یعنی سطرها ثابت بوده و سلول‌ داده‌ها، فواصل یک، دو و سه ستونی از سلول فرمول دارند.

اگر این فرمول را در سلول B7 کپی کنید، با توجه به نسبی بودن آدرس ها، این بار اکسل مقدار سلول‌های D7، E7 و C7 را در هم ضرب کرده و حاصل را در سلول B7 می‌نویسد.

باز اگر این فرمول را در سلول A8 کپی کنید، با توجه به نسبی بودن آدرس ها، این‌ بار نیز اکسل، مقدار سلول‌های C8، D8 و B8 را در هم ضرب کرده و حاصل را در سلول A8 می‌نویسد. و چون سلول B8 مقداری ندارد، نتیجه صفر خواهد بود.

آدرس دهی در اکسل به صورت مطلق

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

اهمیت آدرس دهی مطلق زمانی آشکار می‌شود که بخواهید این چنین فرمول هایی را درگ کنید و در دیگر سلول ها نیز تعمیم بدهید. در این شکل از آدرس دهی مطلق استفاده شده و سپس فرمول نوشته شده در سلول B3 به سلول‌های دیگر تعمیم داده شده است.

به هنگام استفاده از آدرس دهی مطلق، اکسل از آدرس ثابت داده‌ها استفاده می‌کند. در تعمیم فرمول، زمانی که سلول فرمول جابجا می‌شود، سلول داده به صورت هماهنگ با این جابجایی تغییری نکرده و آدرسی که برای اولین فرمول استفاده شده را به کار می‌گیرد. در این مثال از آدرس J3 در کل فرمول‌های ستون B استفاده شده است.

ولی در شکل زیر از آدرس دهی نسبی استفاده شده و سپس فرمول نوشته شده در سلول B3 به سلول‌های دیگر تعمیم داده شده است. به تفاوت دو نوع آدرس دهی نسبی و مطلق و نتایج حاصله از آنها در دو شکل بالا و پایین توجه فرمایید.

در کل، زمانی که بخواهید فرمولی را به سلول‌های مختلف تعمیم بدهید برای داده‌های ثابت از آدرس دهی مطلق استفاده نمایید. در صورتی که از این نوع آدرس دهی استفاده نکنید، باید فرمول هر سلول را به صورت دستی تایپ نمایید!

در آدرس دهی نسبی در اکسل چه اتفاق می‌افتد؟

اگر از آدرس دهی نسبی استفاده کنید، در تعمیم فرمول، زمانی که سلول فرمول جابجا می‌شود، سلول داده نیز به صورت هماهنگ با این جابجایی حرکت کرده و آدرس جدیدی برای هر فرمول به خود می‌گیرد. اگر جابجایی سلول فرمول فقط در طول ستون‌ها باشد، سلول داده نیز به همان مقدار در طول ستون‌ها حرکت خواهد کرد. اگر جابجایی سلول فرمول فقط در طول سطرها باشد، سلول داده نیز به همان مقدار در طول سطرها حرکت خواهد کرد. حرکت ترکیبی سلول فرمول، منجر به حرکت ترکیبی سلول داده به همان مقدار و در همان جهت خواهد شد. در این مثال فرمول‌ از B3 تا B13 (در طول ستون‌ها و به سمت پایین) حرکت کرده، هماهنگ با آن سلول داده نیز از J3 تا J13 حرکت کرده است.

چگونه یک آدرس دهی در اکسل را مطلق کنیم؟

به صورتی که در اشکال بالا نیز مشاهده می‌شود، از علامت دلار ($) برای مطلق کردن آدرس دهی در اکسل استفاده می‌شود. همانطوری که در قسمت ابتدایی اشاره شد، آدرس هر سلول از ترکیب آدرس یک ستون و یک سطر تشکیل شده است. اگر علامت $ قبل از آدرس ستون (قسمت حرف آدرس) گذاشته شود، حرکت در طول سطرها محدود شده و اگر این علامت قبل از آدرس سطر (قسمت عدد آدرس) گذاشته شود، حرکت در طول ستون‌ها محدود خواهد شد. بدیهی است، استفاده همزمان از علامت $ قبل از آدرس ستون و آدرس سطر، حرکت در هر دو جهت را محدود کرده و سلول کلاً ثابت خواهد ماند.

 آدرس دهی در اکسل به صورت مطلق و ترکیبی

 جدول محاسبه میزان مصالح مصرفی بتن ریزی در یک کارگاه ساختمانی است، از آدرس دهی مطلق و نسبی استفاده شده است. در این کارگاه از دو طرح اختلاط برای بتن ریزی های استفاده شده است. برای هر طرح، احجام بتن ریزی به طور جداگانه در سلول‌های C7 و C8 نوشته شده است.

در جدول فوق هر یک از مقادیر مصالح (سیمان، ماسه، نخودی و بادامی) در طرح اختلاط، باید به حجم بتن ریزی ضرب شوند تا مقدار کل مصالح مصرفی برای هر طرح بدست آید. با نوشتن فرمول در سلول D7 مقدار سیمان مصرفی به ازای 585 متر مکعب بتن ریزی محاسبه می‌شود. با تعمیم این فرمول به صورت سطری (در سلول‌های F7، E7 و G7 ) برای اینکه با جابجایی فرمول، آدرس سلول مقدار بتن ریزی (585 متر مکعب) تغییری نکند، این آدرس به صورت مطلق و با محدود کردن حرکت در جهت سطرها نوشته شده است. برای طرح شماره 2 طرح اختلاط، فرمول در سلول D8 نوشته شده با این تفاوت که آدرس حجم بتن ریزی کاملاً مطلق نوشته شده است. بسته به مورد از هر ترکیب آدرس دهی مطلق می‌توان استفاده کرد. با کنترل مناسب، نتایج یکسان خواهد بود.

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

این مفهوم کلی آدرس دهی در اکسل است. همواره در آدرس دهی نسبی، فاصله سطر و ستونِ سلولِ داده نسبت به سطر و ستون سلولِ فرمول سنجیده می‌شود، و با جابجایی یا تعمیم سلول فرمول، سلول داده نیز به همان نسبت و در همان جهت حرکت می‌کند. در آدرس دهی مطلق، سلول داده همواره آدرس ثابتی دارد و با جابجایی یا تعمیم سلول فرمول، سلول داده هیچ حرکتی نخواهد داشت.

برای تبدیل آدرس نسبی به مطلق از کلید میانبر F4 نیز می‌توان استفاده کرد. آدرس سلول مورد نظر را در فرمول انتخاب کرده با یک بار فشردن کلید F4 آدرس کلا مطلق می‌شود، با دومین بار فشردن این کلید حرکت در جهت ستون‌ها محدود شده و با سومین بار فشردن این کلید حرکت در جهت سطرها محدود می‌شود، چهارمین بار فشردن کلید منجر به نسبی شدن مجدد آدرس خواهد شد.

همواره باید توجه کرد که بدون دلیل هیچ آدرسی نباید مطلق شود، چون هم کنترل محدودیت حرکت در جهت مناسب زمان بر است و هم بعضاً مطلق سازی بی دلیل آدرس، در محاسبات حجیم و گستره باعث بروز خطاهای فاحش می‌شود.                                                                                                         امید واریم ازآموزش اکلسل امروز نیز لذت برده باشید