غیر فعال کردن Auto Fill Auto Fill در Excel

ll بطور خودکار فعال است. جهت غیر فعال نمودن آن مراحل زیر را طی می کنیم:
1-    انتخاب منوی Tools
2-    انتخاب منوی Options
3-    انتخاب: Edit Tab
4-    کادر انتخاب Allow Cell Drag and Drop را غیر فعال می کنیم
 
آدرس دهی در اکسل:
آدرس دهی نسبی:
فرض کنید سلولهای Excel را به صورت زیر پر کرده ایم:
در سلول B1 فرمول A1 A2 را می نویسیم. اگر این فرمول را copy کرده و در سلول B2. Paste کنیم و یا با استفاده از Auto fill محتوای سلولهای B2 تا B4 را پر کنیم.اعداد 5و7و4 به ترتیب برای سلولهای B2 تا B4 ظاهر می شوند. حال میخواهیم بدانیم این اعداد از کجا بدست آمده اند. وقتی ما در سلول B1 فرمول A1 A2 را تایپ می کنیم، در حقیقت سلول سمت چپی و یک سلول پایین آن با هم جمع می شوند. پس برای سلول B2، سلول سمت چپی (A2) و سلول پایینی آن (A3) با هم جمع می شوند که جواب 5 می شود. برای سلولهای بعدی هم به همین ترتیب محاسبات انجام می شود. به این نوع آدرس دهی، آدرس دهی نسبی گفته می شود چون نسبت به مکان هر سلول، فرمول سلول عوض می شود و در حقیقت فرمول درون سلول B2، A2 A3 می شود.
 
 
آدرس دهی مطلق:
فرض کنید میخواهیم حقوق افراد یک اداره را حساب کنیم. حقوق با استفاده از فرمول زیر حساب می شود: حقوق پایه * 7% - حقوق پایه = حقوق حقوق پایه را در ستون B مینویسیم. برای محاسبه حقوق می توانیم در سلول C1 فرمول B1-B1*7% را بنویسیم و برای تمام افراد Auto fill کنیم. در این حالت در حقیقت از آدرس دهی نسبی استفاده کرده ایم.
ولی فرض کنید که درصد مالیات عوض شده و 10% شود. حالا باید دوباره فرمول جدیدی در سلول C1 نوشته و مجدد Auto fill کنیم. برای اینکه نخواهیم در هر بار عوض شدن درصد مالیات فرمول را عوض کنیم می توانیم درصد مالیات را در سلول جدا نوشته و از آدرس آن در فرمول استفاده کنیم. پس مثلاً در سلول A1 عدد 7% را مینویسیم و در سلول C1 فرمول B1-B1*A1 را مینویسیم حال Auto fill می کنیم. ولی می بینیم که برای بقیه سلولها جواب همان حقوق پایه می شود. چرا؟ چون با آدرس دهی نسبی میخواهد عمل کند و چون در سلول C1 فرمول B1-B1*A1 بوده، برای C2 فرمول B2-B2*A2 می شود که مقدار A2 صفر است. پس جواب همان مقدار حقوق پایه می شود. برای رفع این مشکل باید سلول A1 ثابت شود. یعنی در تمام فرمولها A1 در ستون حقوق پایه ضرب شود. در این حالت از آدرس دهی مطلق استفاده می کنیم. برای ثابت کردن سطر یا ستون در کنار حرف ستون یا عدد سطر علامت $ می گذاریم. اگر بخواهیم سلول را ثابت کنیم در کنار حرف ستون و عدد سطر هر دو علامت $ می گذاریم. یعنی در سلول C1 مینویسیم: B1-B1*$A$1
 
قالب بندی خانه‌ها در Excel
در Excel این توانایی را داریم که قالب بندی یا فرمت یک سلول را تغییر داده و فرمت آن سلول را مثلاً تاریخ یا درصد یا زمان یا متن یا.... کنیم. همچنین می توانیم دور سلول کادر بیاندازیم یا رنگ زمینه آنرا عوض کنیم.
برای قالب بندی خانه‌ها در Excel مراحل زیر را طی می کنیم:
روش اول:
1-    انتخاب منوی Format
2-    انتخاب گزینه Cells
روش دوم:
بر روی خانه‌های مورد نظرکلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.
روش سوم:
فشردن کلیدهای ctrl 1
با اجرای یکی از سه روش بالا پنجره ای باز می شود که دارای Tab‌های زیر است:
 
الف - Number:
توسط این Tab می توانیم نوع اطلاعات ورودی را تعیین کنیم. این Tab شامل گزینه‌های زیر می باشد:
Sample: هر فرمتی را که انتخاب کنیم، بر روی محتوای سلول انتخابی نمایش می دهد.
 
الف) Category: در این قسمت نوع داده را مشخص می کنیم که شامل انواع زیر می باشد:
1- General:       این گزینه عددها را به صورت رشته ای از رقمهای متوالی و بدون هر گونه قالب بندی نشان می دهد. و اگر عددی در سلول جا نشودآنرا به صورت نمایی نشان می دهد.
2- Number:       مقادیر را بصورت رشته ای از رقمهای متوالی نشان می دهد. در این حالت اگر عدد در سلول نگنجد سلول بزرگتر می شود. همچنین در این قسمت می توانیم تنظیمات زیر را انجام دهیم:
-      Decimal Places: در این قسمت می توانیم تعداد ارقام بعد از اعشار را تعیین کنیم.
-      Negative Number: در این حالت می توانیم مشخص کنیم که عدد منفی به همان صورت نشان داده شود یا به رنگ قرمز یا سیاه با علامت منفی نشان داده شود. یا به رنگ قرمز بدون علامت منفی نشان داده شود. (توجه داشته باشید که این گزینه فقط روی اعداد منفی عمل می کند.)
-      Use 1000 Separator: اگر در کنار بگذاریم اعداد را سه رقم، سه رقم از سمت راست این گزینه می گذارد.
3- Currency:     مقادیر را همراه با سمبل جدا کرده و علامت (،) پول رایج نشان می دهد. در این حالت می توان تنظیمات زیر را انجام داد:
-      Symbol: نوع واحد پول را مشخص می کنید. در این قسمت واحد پول کشورهای مختلف نمایش داده شده و می توانیم واحد دلخواه خود را انتخاب کنیم.
-      Negative Number: اعداد منفی چطور نمایش داده شوند.
4- Accounting:    مانند حالت Currency است. این قالب بندی، قالب بندی حسابداری می باشد و علامت پولی در انتهای سمت چپ آن نوشته می شود.
5- Date:           تاریخ را با قالب بندی خاص تاریخ نشان می دهد. و ما می توانیم انواع قالب بندی‌های تاریخ را دیده و انتخاب کنیم. مثلاً مدلی را انتخاب کنیم که فقط روز و ماه را نشان دهد. و یا مدلی را انتخاب کنیم که روز را به عدد و ماه را به حروف نشان دهد.
6- Time:           زمان را با قالب بندی‌های خاص زمان نشان می دهد. انواع قالب زمانی در این قسمت وجود دارند. ما می توانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد. و یا....
7- Percentage:   عددها را همراه با علامت درصد نشان می دهد.
 
نکته: در تمام قالب بندی‌ها بجز Percentage فرقی نمی کند که ابتدا اطلاعات را در سلول وارد کنیم یا اول قالب بندی را تنظیم کنیم. ولی در حالت Percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage کنیم، عدد را در 100 ضرب می کند. ولی اگر ابتدا فرمت را Percentage کنیم، و بعد عدد را بنویسیم، تغییری در عدد نمی دهد.
8- Fraction:       مقادیر را بصورت عدد صحیح نشان می دهد که بدنبال آن نزدیک‌ترین کسر به مقدار واقعی ظاهر می شود. برای نوشتن یک عدد مخلوط کافی است ابتدا قسمت صحیح را نوشته سپس یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 می شود. اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در قسمت Fraction مقدار 3/10 را انتخاب کنیم. در این حالت نگاه می کند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب می کند.
9- Scientific:      مقادیر را با قالب بندی علمی نشان می دهد.
10- Text:         مقادیر را به همان صورتی که وارد شده اند نشان می دهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان می دهد و مقادیر آنرا محاسبه نمی کند.
11- Special:      مقادیر را با استفاده از قواعد قالب بندی خاص (مانند کد پستی، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) نشان می دهد. مثلاً اگر در یک سلول یک شماره تلفن (در حالت 10 رقمی) تایپ کنیم، با انتخاب این فرمت به صورت قالب بندی تلفن در می آید. مثلاً اگر شماره IT (3116681184) را وارد می کنیم، به فرمت تلفن در آمده (1184-668 (311)) و می فهمیم که 311 کد شهرستان، 668 کد محله و 1184 شماره تلفن IT می باشد.
12- Custom:     برای ایجاد یک قالب بندی جدید از این گزینه استفاده می کنیم که خارج از بحث ما می باشد.
 
ب) Alignment: توسط این Tab می توانیم جهت قرار گرفتن اطلاعات در سلول را تعیین کنیم. این Tab شامل قسمتهای زیر است:
1-     Horizontal: محل قرار گرفتن افقی متن را تعیین می کند. این قسمت شامل گزینه‌های زیر است:
-      General: هم ترازی پیش فرض
-      Left: هم ترازی داده‌ها را به سمت چپ (استفاده برای ارقام)
-      Center: هم ترازی داده‌ها در مرکز
-      Right: هم ترازی داده‌ها به سمت راست (برای متون)
-      Fill: تمام سلول را با متنی که در آن نوشته شده است، پر می کند.
-      Justify: یک تراز مناسب برای سلول در نظر می گیرد. معمولاً برای زمانی است که اطلاعات در سلول نگنجد.
-      Center Across Selection: وسط متن را در وسط سلولهای انتخابی می گذارد.
2-     Vertical: محل قرار گرفتن اطلاعات را بطور عمودی تنظیم می کند. این گزینه شامل قسمتهای زیر است:
-      Bottom: هم ترازی داده‌ها در پایین سلول
-      Top: هم ترازی داده‌ها در بالای سلول
-      Center: هم ترازی داده‌ها در وسط یا مرکز سلول
-      Justify: داده‌ها در داخل سلول هم تراز می شوند. (بدین معنی که داده‌ها در داخل سلول بصورت مساوی جاسازی می شوند. مانند متون موجود در روزنامه ها)
 
3-     Orientation: شامل قسمتهای زیر است:
-      زاویه متن را نسبت به افق بوسیله ماوس می توان تعیین کرد.
-      Degrees: زاویه متن را نسبت به افق بوسیله تایپ زاویه یا با کلید‌های Increase و Decrease تعیین کرد.
 
4-     Text Control: شامل قسمتهای زیر است:
-      Wrap Text: اگر اندازه متن بیشتر از سلول باشد توسط این گزینه می توان آنرا شکست. در نتیجه ارتفاع سطر افزایش می یابد.
-      Shrink to Fit: اگر اندازه متن از سلول بیشتر باشد با انتخاب این گزینه متن به اندازه ای کوچک می شود که در داخل سلول بگنجد.
-      Merge Cells: اگر اندازه متن از سلول بیشتر باشد می توان سلولهایی را که متن اشغال کرده را انتخاب نمود و سپس در کنار این گزینه تیک زد. این عمل باعث می شود که این سلول‌ها بهم پیوسته و یک سلول شوند.

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

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

اگر در مهلت زمان معین کار را به انجام نرسانید یا کاری را خارج از برنامه تمام کنید، این امر تاثیرات جانبی بر روند پروژه خواهد گذاشت. برای مثال، مدت زمان انجام پروژه یا هزینه اجرای آن بیشتر خواهد شد. در نتیجه اطلاع یافتن از کارهایی که باید انجام شود و پی بردن به زمان مشخص اتمام هر یک از آنها با یک نگاه اجمالی، بسیار سودمند است. 

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

در این آموزش  نگاهی به این موضوع خواهیم داشت که چرا گانت چارت‌ها سودمند هستند و اینکه چطور می‌توانید آنها را به کار گیرید تا پروژه‌های خود را سازماندهی کنید.

تاریخچه این ابزار

در اواخر سال 1800، یک مهندس لهستانی به نام کارل آدامیک نمودار گردش کار تصویری ایجاد کرد که نام آن «هارمونوگرام» بود.

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

چرا باید از گانت چارت استفاده کنیم؟ 

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

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

ساخت یک گانت چارت درEXCEL

برای ساخت گانت چارت برای پروژه خود، مراحل زیر را انجام دهید: 

قدم اول: وظایف ضروری را شناسایی کنید. 

در صورتی که به تمامی فعالیت‌های مورد نیاز برای اتمام یک پروژه یا مراحل اولیه پروژه اشاره نشود، گانت چارت اطلاعات مفیدی ارائه نخواهد داد.

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

مثال:

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

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

* قدم دوم: شناسایی ارتباط میان وظایف 

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

این وظایف، وظایف «زنجیری» یا «خطی» نامیده می‌شوند. 

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

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

گانت چارت را آغاز کنید.

نکته:

در گانت چارت سه رابطه اصلی بین وظایف زنجیره‌ای وجود دارد:

* پایان به شروع: وظایف نمی‌توانند قبل از به پایان رسیدن وظایف قبلی (مرتبط) به پایان برسند. اگرچه، می‌توانند بعدا شروع شوند. 

* شروع به شروع: وظایف نمی‌توانند قبل از شروع وظیفه قبلی شروع شوند. اگرچه می‌توانند بعدا شروع شوند. 

* پایان به پایان: وظایف نمی‌توانند قبل از به پایان رسیدن وظیفه قبلی به پایان برسند.

* نوع چهارم، شروع به پایان، بسیار نادر است.

تذکر 1: 

وظایف می‌توانند بطور همزمان زنجیره‌ای و موازی باشد- برای مثال، دو وظیف (ب و پ) وابسته و وظیفه‌ای دیگر (الف) است و ممکن است بطور همزمان به پایان برسند. وظیفه (ب) که به‌دنبال وظیفه (الف) انجام می‌شود، زنجیره‌ای و با توجه به وظیفه (ت) موازی است.

تذکر 2:

برای کاهش زمان تحویل، باید تا آنجایی که ممکن است به‌صورت موازی کار کنید. همچنین باید گستره پروژه را تا حد ممکن کوچک نگه دارید. (جدول شماره 2) 

* قدم سوم: وارد کردن اطلاعات

می‌توانید به‌صورت دستی گانت چارت بکشید یا از نرم‌افزارهای تخصصی مانند Gantto، Matchware و Microsoft Project استفاده کنید. برخی از این نرم‌افزارها قابلیت این را دارند که شما و همکارانتان بطور همزمان و از هر نقطه‌ای به آنها دسترسی پیدا کنید. 

می‌توانید از الگوهای گانت چارت موجود در اکسل استفاده کنید یا برای نمونه‌های بیشتر در اینترنت جست‌وجو کنید.

* قدم چهارم: پیشرفت چارت 

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

گانت چارت را برای انعکاس تغییرات به محض وقوع به روز‌رسانی کنید. این به شما کمک می‌کند تا برنامه‌ها، گروه و اسپانسر خود را به‌روز نگاه دارید.                                                                   

نکات کلیدی

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

خانه‌های اکسل مثل آدمها و سکه‌ اند، یعنی دو رو دارند. یک ظاهر و و یک باطن.  مانند خانه‌ای که تاریخ 7/27/2006 را نشان می‌دهد اما باطن آن عدد 38925 است.

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

در مثال زیر این امر به وضوح مشخص است که در خانه عدد 123.1 نشان داده می‌شود اما در نوار فرمول مقدار واقعی این خانه که 123.1233 است را می‌بینیم.

منظور از روند کردن یک عدد، یعنی تغییر دائمی در رقمهای آن عدد و می‌توان آنرا به انواع زیر دسته بندی کرد:

• روند کردن قسمت اعشار یک عدد
• روند کردن قسمت صحیح یک عدد

تابع  Roundدر اکسل 

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

ROUNDUP(Number,Num_digits)

Number: عددی که می‌خواهیم آنرا روند کنیم.

Num_digits: دقت اعشار

اگر پارامتر دوم عدد مثبت باشد، رقم داده شده را با آن دقت اعشار داده شده گرد می‌کند (عدد 5 بستگی به رقم بعدش دارد، اگر عدد بعد از 5 از 5 بیشتر باشد 5 به 6 گرد می‌شود)

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

تابع ROUNDUP 

فرض کنید که قرار است امتیاز افرادی را پس از یک سری محاسبات بدست آورید، اگر شما از تابع ROUND استفاده کنید، چون اعداد 12.44 را تا یک رقم اعشار می‌کند 12.4  ، ممکن است فردی اعتراض کند که امتیاز او نادیده گرفته شده است! آنهم در حد یکصدم! بنابراین بهتر است از ROUNDUP استفاده کنیم که در هر حال امتیازی بیشتر را محاسبه می‌کند.

تابع CEILING 

این تابع کار مثل راننده تاکسی‌ها عمل می‌کند، یعنی اگر کرایه شما 118 تومان شده باشد، می‌گویند 125 تومان یا مثلا اگر کرایه شما شده باشد 233 تومان می‌گویند 250 تومان. البته این به این دلیل نیست که می‌خواهند پول بیشتری بگیرند! بلکه به خاطر این است که در سیستم پولی ما 25 تومانی داریم اما 33 تومان نه. در واقع آنها عدد را به اولیل مضرب 25 بالاتر از کرایه گرد می‌کنند و ما در اکسل می‌نویسیم:

=CEILING(کرایه , 25)    

توجه: عکس این عمل یعنی راننده تاکسی منصف تر (عدد کمتر) را تابع Floor انجام می دهد.

تابع TRUNCدر اکسل

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

گرد کردن قسمت صحیح یک عدد نه قسمت اعشاری!

هر گاه بخواهید که از قسمت اعداد صحیح یک عدد، گرد کردن را انجام دهید! می توانید عدد منفی استفاده نمایید.

به اعداد صورت‌های مالی دست نزنید

در خیلی از موارد و به ویژه فایلهای اکسل صورت های مالی، برای خوانایی بیشتر آنها را بر 1000 یا 1000000 تقسیم می کنند. مثلا عدد 1357051700 را به صورت 1357 نشان می‌دهند. تا اینجا که مشکلی نیست اما مشکل آنجا پیدا می شود که دیگر حاصل جمع این اعداد اختلافی جزیی با جمع واقعی خواهد داشت و کارشناسان مالی با کلی زحمت این مشکلات را حل می کنند.

 یک راه بسیار ساده برای اینکار است. اصلا به اصل اعداد دست نزنید.

آنها را به همان صورت اصلیشان رها کنید.

حال اگر می خواهد که مثلا سه رقم صحیح و یا شش رقم آنرا نبینید کافیست که با فرمت سلول  اینکار را انجام دهید.

ما می توانیم لیست موجود را بر اساس هر یک از ستونهای آن به صورت صعودی یا نزولی مرتب کنیم. برای این کار کافی است به یکی از دو روش زیر عمل کنیم:
روش اول:
1- یک سلول از ستونی را که میخواهیم لیست بر اساس آن مرتب شود انتخاب می کنیم.
2- برای مرتب سازی صعودی از آیکون Sort Ascending  و برای مرتب سازی نزولی از آیکون Sort Descending در نوار ابزار Standard استفاده می کنیم.
نکته
باید توجه داشته باشیم که در این حالت نباید یک ستون انتخاب شود بلکه فقط یک سلول از ستونی که میخواهیم بر اساس آن لیست مرتب شود را انتخاب می کنیم.
روش دوم:
1- کل جدول یا یک سلول از جدول را انتخاب می کنیم.
2- منوی Data را انتخاب می کنیم.
3- گزینه Sort را انتخاب می کنیم.
4- پنجره ای باز می شود که باید قسمتهای زیر را با توجه به نیاز در آن پر کنیم:
الف- Sort By: در این کادر ستونی را که میخواهیم لیست بر اساس آن مرتب شود، انتخاب می کنیم.
ب- Then By: در این کادر ستونی را تعیین می کنیم که اگر اطلاعات ستونی که در کادر اول مشخص شده برای مرتب سازی درexcel  مانند هم بود لیست بر اساس این ستون مرتب شود.
ج- Ascending: با انتخاب این گزینه لیست به صورت صعودی مرتب می شود.
د- Descending: با انتخاب این گزینه لیست به صورت نزولی مرتب می شود.
ه- My List Has: در این قسمت دو گزینه Header Row، و No Header Row وجود دارد. اگر Header Row انتخاب شده باشد هنگام باز شدن پنجره Sort کل لیست به غیر از سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort By , Then By عناوین قرار می گیرند. ولی اگر No Header Row را انتخاب کنیم هنگام باز شدن پنجره Sort کل جدول حتی سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort by , Then By بجای عناوین, نام ستونها قرار می گیرند. در حقیقت در این حالت عناوین جزء اطلاعات در نظر گرفته شده و در مرتب سازی شرکت داده می شوند.ما می توانیم لیست موجود را بر اساس هر یک از ستونهای آن به صورت صعودی یا نزولی مرتب کنیم. برای این کار کافی است به یکی از دو روش زیر عمل کنیم:
روش اول:
1- یک سلول از ستونی را که میخواهیم لیست بر اساس آن مرتب شود انتخاب می کنیم.
2- برای مرتب سازی صعودی از آیکون Sort Ascending  و برای مرتب سازی نزولی از آیکون Sort Descending   در نوار ابزار Standard استفاده می کنیم.
نکته
باید توجه داشته باشیم که در این حالت نباید یک ستون انتخاب شود بلکه فقط یک سلول از ستونی که میخواهیم بر اساس آن لیست مرتب شود را انتخاب می کنیم.
روش دوم:
1- کل جدول یا یک سلول از جدول را انتخاب می کنیم.
2- منوی Data را انتخاب می کنیم.
3- گزینه Sort را انتخاب می کنیم.
4- پنجره ای باز می شود که باید قسمتهای زیر را با توجه به نیاز در آن پر کنیم:
الف- Sort By: در این کادر ستونی را که میخواهیم لیست بر اساس آن مرتب شود، انتخاب می کنیم.
ب- Then By: در این کادر ستونی را تعیین می کنیم که اگر اطلاعات ستونی که در کادر اول مشخص شده برای مرتب سازی مانند هم بود لیست بر اساس این ستون مرتب شود.
ج- Ascending: با انتخاب این گزینه لیست به صورت صعودی مرتب می شود.
د- Descending: با انتخاب این گزینه لیست به صورت نزولی مرتب می شود.
ه- My List Has: در این قسمت دو گزینه Header Row، و No Header Row وجود دارد. اگر Header Row انتخاب شده باشد هنگام باز شدن پنجره Sort کل لیست به غیر از سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort By , Then By عناوین قرار می گیرند. ولی اگر No Header Row را انتخاب کنیم هنگام باز شدن پنجره Sort کل جدول حتی سطر اول که سطر عنوان است انتخاب می شود و در کادر های Sort by , Then By بجای عناوین, نام ستونها قرار می گیرند. در حقیقت در این حالت عناوین جزء اطلاعات در نظر گرفته شده و در مرتب سازی شرکت داده می شوند.                                                                                  داده ها و عملگرهادر اکسل

برای اینکه داده ای را در سلولی وارد کنیم ابتدا باید روی آن سلول کلیک کرده و سپس داده مورد نظر را تایپ کنیم، یادتان باشد که هنوز این داده وارد شده پذیرفته نشده است برای پذیرفته شدن این داده پس از تایپ آن یکی از اعمال زیر را انجام می دهیم:
1- Enter: داده پذیرفته شده و سلول جاری به طور پیش فرض سلول زیرین می شود.

2- Tab: داده پذیرفته شده و سلول جاری سلول بعدی می شود.

نکته:

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

Arrow key: داده پذیرفته شده و سلول با توجه به جهت دکمه فشرده شده یکی از سلولهای اطراف می شود.

داده ها در Excel

داده ها در محیطExcel می توانند اعداد، حروف، تاریخ، زمان و یادداشت باشند که در زیر به شرح تک تک آنها می پردازیم:

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

انواع داده ها:

1- اطلاعات عددی در EXCEL

کارکردن با اعداد:

Excel تمام اعداد را یکسان فرض می کند. بنابراین دقت زیادی در نمایش اعداد به صورت مبلغ، تاریخ و کمیت یا هر شکل دیگری از اعداد ندارد. در Excel اعداد را به دو روش می توان وارد نمود:

1- با استفاده از کلیدهای عددی موجود در بالای حروف

2- با استفاده از کلیدهای موجود در سمت راست صفحه کلید (در صورتی که اعداد این قسمت کار می کنند که کلید Num Lock روشن باشد.)

علاوه بر ارقام 0 تا 9 نمادهای خاص + و – و، و. و $ و % و E و e را وارد نمود. که در آینده با مفهوم هر یک از این علائم بیشتر آشنا شویم.

عکس العمل Excel نسبت به عدد تایپ شده:

1- اگر طول عدد از سلول کوچکتر باشد تغییری در سلول داده نمی شود.

2- اگر طول عدد به اندازه چند کاراکتر بزرگتر از سلول باشد اندازه سلول بزرگ شده تا عدد در آن بگنجد.

3- اگر طول اعداد برای خانه مورد نظر بزرگ باشد ظاهراً عدد سلولهای بعدی را اشغال می کند ولی بعد از Enter کردن یکی از اتفاقات زیر روی می دهد:

الف – اعداد در نماد علمی نمایش داده می شوند.

ب- عدد گرد می شود.

ج- سلول با علامت # پر می شود. این حالت زمانی اتفاق می افتد که فرمت سلول General نباشد.

تبدیل داده عددی به متنی:

اگر اعدادی که رقم اول آنها صفر است مثل (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

عملگرها در Excel:
1- عملگرهای محاسباتی: از عملگرهای ریاضی + (برای جمع)، – (برای تفریق)، * (برای ضرب) و / (برای تقسیم)، ^(برای توان) و % (برای درصد) در Excel استفاده کنیم.

ایجاد یک فرمول ریاضی

برای ایجاد یک فرمول ریاضی به یکی از روشهای زیر عمل می کنیم.

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

روش دوم: در سلولی که می خواهیم نتیجه نوشته شود علامت = را تایپ کرده، سپس بر روی سلولی که حاوی اولین عملوند است کلیک کرده، سپس عملگرد را تایپ کرده و بعد بر روی سلول حاوی در بین عملوند کلیک می کنیم و به همین ترتیب ادامه می دهیم.

* تقدم عملگرهای ریاضی:

اگر در یک فرمول چندین عملگر ریاضی داشه باشیم، این عملگرها دارای تقدم هستند که عبارتند از:

()

^

%

* و /

+ و –

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

2- عملگر متنی: برای عبارتهای متنی می توانیم از عملگر & (الحاق) استفاده کنید. با قرار دادن این عملگر بین دو متن آن دو را به هم پیوند می دهید.

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

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

برای حل این مشکل 3 روش وجود دارد:

روش 1) با استفاده از اکسل:

فرض کنید لیستی مشابه لیست زیر (A1 تا A11) دارید و قصد دارید متن تمام سلول‌ها را با هم ترکیب کنید.

قبل از حل مشکل فوق ابتدا باید با تابع Transpose آشنا شویم.

تابع Transpose:
کلمه Transpose به معنای ترانهادن است و در اکسل با استفاده از تابع Transpose می‌توان مکان ردیف‌ها و ستون‌ها را تغییر داد. همانطور که در تصویر زیر مشاهده می‌کنید جدول زیر دارای 7 ردیف و 5 ستون می‌باشد.

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

=TRANSPOSE(A1:E7)

برای اینکه عمل ترانهادن برای همه 35 سلول جدول فوق انجام شود باید از فرمول‌نویسی آرایه‌ای استفاده کرد که آموزش داده شده است. بنابراین برای اینکه فرمول فوق بصورت آرایه‌ای عمل کند بعد از وارد کردن فرمول بالا، کلیدهای Ctrl+Shift+Enter را همزمان فشار دهید تا نتیجه زیر حاصل شود.

پس از آشنایی با تابع Transpose، برای ترکیب محتویات تعداد زیادی سلول در یک سلول، مراحل زیر را طی کنید:

1- در یک سلول خالی که می‌خواهید ترکیب سلول‌ها در آن قرار گیرد فرمول زیر را تایپ کنید.

=CONCATENATE(TRANSPOSE(A1:A11))

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

=CONCATENATE(TRANSPOSE(A1:A11&" "))

یا

=CONCATENATE(TRANSPOSE(A1:A11&&","))

2- پس از تایپ فرمول، کلید اینتر را فشار ندهید. قسمت (" "&TRANSPOSE(A1:A11 را انتخاب کنید و کلید F9 را فشار دهید. اگر به هر دلیلی نمی‌توانید از F9 استفاده کنید کلیدهای Ctrl و = را همزمان فشار دهید.

نکته: برای نمایش مقدار واقعی یک فرمول، کافیست فرمول مورد نظر را انتخاب کرده و کلید F9 را فشار دهید. در صورتیکه کلید Esc را فشار دهید مجدداً فرمول مربوط به آن سلول نشان داده می‌شود.

3- مشاهده می‌کنید که متن تمام سلول‌های A1 تا A11 ظاهر شده است درحالیکه بین‌ آن‌ها ویرگول و اطرافشان کروشه {} قرار دارد. کروشه‌های دو طرف را پاک کنید.

4- حالا کلید اینتر را فشار دهید و نتیجه را ببینید. تمام.

نکته: اگر سلول‌هایی که قصد دارید با هم ترکیب کنید بصورت ردیفی بودند مثلا A1 تا K1 دیگر نیازی نیست از دستور TRANSPOSE استفاده کنید. فقط پس از فشردن F9 کروشه‌ها را پاک کنید.

منبع: (2, 1) chandoo.org 

روش 2) با استفاده از Notepad و Word:

برای ترکیب سلول‌ها در خارج از اکسل، باید از Word استفاده کرد. اما ابتدا باید کلمات را از جدول خارج کنیم. می‌توان جدول را مستقیما در ورد از بین برد یا اینکه ابتدا در Notepad اینکار را انجام داد و سپس به ورد منتقل کرد. کلمات را اگر از لیست اشاره شده در روش 1 را کپی کنید و سپس در Notepad پیست کنید جدول از بین می‌رود و تبدیل به متن می‌شود بصورت زیر:

سپس می‌توان آن‌ها را به ورد انتقال داد تا سایر عملیات روی‌ آن‌ها انجام شود. البته بدون نیاز به Notepad هم می‌توان جدول را در ورد از بین برد. اگر بعد از کپی کلمات در اکسل، آن‌ها را بصورت معمولی (Ctrl+V) در ورد پیست کنید کلمات همراه با جدول در ورد پیست می‌شوند و نمی‌توان آن‌ها را با هم ترکیب کرد. برای از بین بردن جدول کافیست زمانیکه می‌خواهید لیست را در ورد پیست کنید در هنگام پیست گزینه Keep text only را انتخاب کنید. در اینصورت کلمات بصورت text و بدون جدول در ورد کپی می‌شوند.

در پایان کافیست خطوط را به هم بچسبانید تا کلمات کنار هم قرار بگیرند. ب

روش 3) با استفاده از ++Notepad

2. ستونهای موردنظر خود را انتخاب کنید.

 3. در منوی Data  زیر منوی  Pivot table and Pivot chart report… را کلیک نمایید

 4. در پنجره ظاهر شده کارهای درخواست شده را انجام دهید.

5. متغیرهای مورد نظر خود را در ستون و ردیف معرفی شده قرار دهید.

6. متغیری که قرار است شمارش شده و در جدول ثبت شود را به قسمت وسط جدول انتقال دهید. عمل انتقال متغیرها با Click and                                                                                                                                                                                                                                    محاسبه ی میانگین وانحراف معیار در اکسل                                                                                                                                                           1- ابتدا اعداد مورد نظر خود را در یک ستون در نرم افزار اکسل وارد نمایید.

2. اعداد نوشته شده را انتخاب نمایید.

3. Insert Function را در منوی Formulas کلیک نمایید.

4. Average و یا STDEV را انتخاب نمایید.

5. در خانه ای که می خواهید نتیجه محاسبه در آن قرار بگیرد، کلیک نمایید.

6. در پنجره ظاهر شده محدود اعداد دلخواه خود را انتخاب نمایید.

7. میانگین و یا انحراف معیار در جای مورد نظر ثبت می شود.

• توابع جستجودر excel
  

1-  تابع Find:

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

=FIND(find_text , within_text , start_num)

این تابع دارای 3 آرگومان (پارامتر یا شیء) است:

1-   find_text (آرگومان 1): عبارتی که بایستی جستجو شود که می‌تواند یک حرف یا یک کلمه یا متن باشد.

2-  within_text (آرگومان 2): متن مرجع که در آن به جستجو خواهیم پرداخت. این متن نیز می‌تواند یک حرف یا یک کلمه یا متن طولانی باشد.

-   Start_num (آرگومان 3): نقطه آغاز جستجو است. در واقع مشخص می‌کنیم که در آرگومان 2 از چندمین کاراکتر شروع به جستجو کند که در این صورت کاراکترهای قبلی جستجو نخواهند شد. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان 2 می‌تواند باشد.

توجه: درصورتیکه عدد صفر یا عددی بیش از تعداد کاراکتر موجود در آرگومان 2 به آن اختصاص یابد خطای #VALUE! را نشان خواهد داد.

توجه: اگر آرگومان 1 در آرگومان 2 موجود نباشد خطای #VALUE! را خواهیم دید.

2- تابع :Search

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

=Search(find_text , within_text , start_num)

• توابع جایگزینی در اکسل:

1- تابع REPLACE:

از تابع REPLACE برای جایگزین کردن قسمتی از یک رشته‌ی متنی توسط رشته‌ی متنی جدید استفاده می‌گردد، ساختار (Syntax) این تابع به شکل زیر است:

=REPLACE(old_text,start_num,num_chars,new_text)

این تابع دارای 4 آرگومان است:

1-   old_text (آرگومان 1): متن قدیمی که قصد دارید عملیات جایگزینی را در آن انجام دهید.

 -   Start_num (آرگومان 2): نقطه آغاز جایگزینی در متن قدیمی. در واقع مشخص می‌کنیم که در آرگومان 1 از چندمین کاراکتر جایگزینی شروع شود. این آرگومان از یک شروع و تا تعداد کاراکتر موجود در آرگومان 1 می‌تواند باشد.

 2-  num_chars (آرگومان 3): تعداد کاراکتری که در متن قدیمی باید با متن جدید جایگزین شوند.

      new_text (آرگومان 4): متن جدید که باید در متن قدیم جایگزین شود.

به مثال زیر توجه کنید:

=REPLACE(“09122048458″,1,1, “+98″)

فرمول بالا عدد صفر ابتدای شماره تماس را با عبارت 98+ جایگزین می‌کند.
 

2- تابع SUBSTITUTE:

تابع SUBSTITUTE همانند تابع REPLACE برای جایگزین کردن بخش از یک رشته‌ی متنی با رشته‌ی متنی جدید مورد استفاده قرار می‌گیرد با این تفاوت که در این تابع دقیقاً مشخص می‌کنیم چه بخشی (دقیقاً کدام کاراکترها) از یک رشته‌ی متنی با رشته‌ی متنی جدید باید جایگزین شود، ساختار این تابع مانند زیر است:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

این تابع دارای 4 آرگومان است:

1-   text (آرگومان 1): متن مرجع که قصد دارید عملیات جایگزینی را در آن انجام دهید.

1-   old_text (آرگومان 2): کاراکترهایی از رشته‌ی متنی مرجع می‌باشد که قرار است با کاراکترهای جدید جایگزین شوند.

      new_text (آرگومان 3): متن جدید که باید در متن قدیم جایگزین شود.

      [instance_num] (آرگومان 4): این آرگومان اختیاری است و زمانی استفاده می‌شود که آرگومان 2 چند مرتبه در متن مرجع تکرار شده باشد و بخواهیم تنها یک مورد از آن‌ها جایگزین شود. در این حالت با استفاده از آرگومان 4 مکان آن را مشخص می‌کنیم.

حال می‌خواهیم مانند مثال تابع REPLACE عدد صفر را با 98+ جایگزین کنیم:

=SUBSTITUTE(“09122048458″,”0″, “+98″)

خروجی این فرمول عبارت “989122+9848458+” خواهد بود، در واقع تمام صفرهای پیدا شده با عبارت “98+” جایگزین می‌شوند. در این حالت باید از آرگومان 4 استفاده کرد.اگر این آرگومان خالی باشد (مانند مثال بالا) به این معنی است که تمام صفرها باید جایگزین شوند و اگر به جای این آرگومان عدد 1 نوشته شود، تنها صفر اول (اولین صفر پیدا شده در رشته) با عبارت “98+” جایگزین می‌شود، مانند زیر:

=SUBSTITUTE(“09122048458″,”0″, “+98″, 1)

در نرم افزار اکسل  مقادیر عددی که در برگه های کاری خود وارد می کنید معمولاً فقط عدد نمی باشند بلکه نشان‌دهنده مقدار دلار، تاریخ، درصد و مقادیر واقعی دیگر می باشند. بصورت پیش فرض، وقتی که عددی را وارد می کنید، به فرمت General نمایش داده می شود که معمولاً به همان صورتی است که آن را تایپ می کنید. ولی بجای تایپ $12.95، می توانید تایپ کنید 12.95 و بعد یک فرمت عددی مانند فرمت Currency را روی آن اعمال کنید. برای مثال، 5.05 می‌تواند معانی گوناگون داشته باشد ولی اگر فرمت Currency آن را اعمال کنید به صورت $5.05 در‌می‌آید و اگر فرمت Percent age را روی آن اعمال کنید بصورت 505% در می آید. در این کار عملی، یاد می گیرید که چگونه با استفاده از دکمه های نوار ابزار و کادر محاوره ای Format Cells، فرمت اعداد را تغییر دهید.

 سلول یا محدوده مورد نظر را انتخاب کنید  

 سلول یا محدوده ای که می خواهید فرمت داده های عددی آنها را تغییر دهید را انتخاب کنید.

 رویCurrency کلیک کنید  

 برای تغییر فرمت به فرمت پولی (که $ را اضافه می کند، عدد را تا دو رقم اعشار گرد می کند و$ را طوری قرار می دهد که تمام علامت‌های دلار در ستون تراز شوند)، روی دکمه Currency Style در نوار ابزارFormatting کلیک کنید.

 روی Percent کلیک کنید  

برای تغییر فرمت به فرمت درصد (که علامت % را اضافه می کند و عدد را ازحالت کسری یا عدد صحیح به مقدار درصد تبدیل می کند)، روی دکمه Percent Style در نوار ابزارFormatting کلیک کنید.(می دانید که درصد یعنی صدم. بنابراین 0.12 بصورت 12% در می آید ولی 12 بصورت 1200% در می آید).

 رویComma کلیک کنید  

 برای بکار بردن فرمت کاما (که در ایالات متحده به این معنی است که عدد تا دو رقم اعشار گرد شود و بصورت سه رقم سه رقم با کاما جدا شود)، روی دکمه Comma Style در نوار ابزار Formatting کلیک کنید. برای کنترل نقطه اعشاری همچنین می‌توانید روی دکمه های Increase Decimal و Decrease Decimalدر نوار ابزار Formatting کلیک کنید تا نقطه اعشاری جابجا شود.

 از کادر محاوره ای Format Cells استفاده کنید  

 برای بکار بردن فرمت هایی که در نوار ابزار Formatting موجود نباشند، از منوی Format، گزینه Cells را انتخاب کنید تا کادر محاوره ای Format Cells باز شود. روی برگه Number کلیک کنید و از لیست Category، یکی از فرمت ها را انتخاب کنید. مقدار سلول انتخاب شده با آن فرمت در قسمت Sample نشان داده می شود.

 گزینه های مربوطه به Format را تنظیم کنید  

فرمت های مختلف در کادر محاوره ای Format Cells، گزینه متفاوتی را ارائه می کنند. یکی از گروه ها (Category) را انتخاب کنید و گزینه های مربوط به آن فرمت را تنظیم کنید. شرح گروه انتخاب شده در پایین کادر محاوره ای ظاهر می شود. برای بستن کادر محاوره ای و اعمال کردن فرمت جدید روی Ok کلیک کنید.

* توجه *

کپی کردن فرمت

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

Carrency واقعاً Carrency نیست

اگر چه یکی از دکمه های نوار ابزار، Carrency Style نامیده می شود فرمت Carrency را اعمال نمی کند، بلکه فرمتAccounting را اعمال می کند. برای اعمال کردن فرمت Carrency واقعی (که در آن، علامت بی‌توجه به اینکه ستون چه پهنایی دارد، بلافاصله در سمت چپ عدد قرار می گیرد) از کادر محاوره‌ای Format Cells استفاده کنید.

حذف فرمت دهی در اکسل

برای حذف تمام فرمت دهی ها (از جمله فرمت عددی)، بدون حذف داده ها از یک سلول، آن سلول یا محدوده را انتخاب کنید و بعد از منویEdit، گزینه Clear و بعد گزینه Formats را انتخاب کنید.

تابع، یک فرمول از پیش نوشته شده در برنامه است. آرگومان های (متغییرهای) مورد نیاز به تابع داده می شود و تابع، نتیجه را محاسبه می کند. برای تابعSUM، تنها چیزی که باید مشخص کنید محدوده ای است که می خواهید جمع کنید نوار ابزارStandard، امکان دستیابی سریع به SUM و نیز توابع پر استفاده دیگر مانند AVERAGE (محاسبه معدل)، MIN (کوچکترین عدد)، MAX (بزرگترین عدد) وCount (محاسبه تعداد آیتم ها) را فراهم می کند. در آموزش اکسل پیشرفته امروز   یاد می‌گیریم که چگونه از توابع استفاده کنید.

 یک محل برای نتیجه، انتخاب کنید  

سلولی که می خواهید نتیجه تابع در آن ظاهر شود را انتخاب کنید (معمولاً در پایان یک سطر یا ستون از اعداد).

 روی AutoSum کلیک کنید  

در نوار ابزار Standard، روی دکمه AutoSum کلیک کنید.

 فرمول SUM درج می شود  

دکمه AutoSum، تابع SUM را درج می کند و یک خط چشمک زن، پیرامون سلول هایی که با هم جمع می شوند قرار می دهد. یک Screen Tip زرد رنگ، آرگومان های مورد استفاده در تابع SUM را لیست می کند.

 درج داده ها را تکمیل کنید  

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

 با استفاده از AutoSum، معدل حساب کنید  

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

 ورود اطلاعات را کامل کنید  

یک نوار چشمک زن، دور محدوده ای که Excel فکر می کند می خواهید در این تابع استفاده کنید را فرا می گیرد. اگر لازم باشد با کشیدن ماوس، محدوده دیگری را انتخاب کنید وبعدEnter را بزنید. نتیجه در سلول مورد نظر نشان داده می شود و فرمولی که برای محاسبه این نتیجه بکار رفت در نوار فرمول ظاهر می شود.

*توجه  *

 جمع کردن کل سلول های یک جدول در اکسل

برای جمع کردن کل ستون های یک جدول، تمام سلول های ردیف زیرین آخرین ردیف در جدول را انتخاب کنید و روی دکمه AutoSum کلیک کنید. هر ستونی در سلول خالی زیر آن ستون، جمع بسته می شود. برای جمع کردن تمام ردیف های یک جدول، تمام سلول های ستون کنار جدول را انتخاب کنید و روی دکمه AutoSum کلیک کنید. هر سطر در انتهای آن سطر، جمع بسته می شود.

جمع کردن بیش از یک محدوده در excel

برای جمع کردن مقادیر بیش از یک محدوده، روی دکمه AutoSum کلیک کنید، محدوده اول را انتخاب کنید، کلید کاما (,) را بفشارید و بعد محدوده بعدی را انتخاب کنید. بین تمام محدوده های بعدی هم باید کاما تایپ کنید.

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

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

استفاده از این افزونه باعث صرفه جویی بسیار زیاد در وقت و زمان شما خواهد شد.

برخی از امکانات این افزونه به شرح زیر می باشد.

پاک کردن ردیف های خالی از طریق منوی فیلتر ستون در اکسل

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

تبدیل مقادیر زمانی به سال

این آپشن جدید تبدیل داده ها، امکان محاسبه مجموع سالها را طریق ستونی از نوع ” مدت زمان” فراهم می کند. (اکسل مجموع تعداد روزها را بر 365 تقسیم خواهد کرد) برای دسترسی به این امکان کاربر باید از طریق سربرگ Transform یا Add columns  گزینه ی Total years را انتخاب کند.

نگه داشتن تکراری ها

در مسیر Home > Remove Duplicates  گزینه ی جدیدی تحت عنوان Keep duplicates در زیر گزینه ی Remove duplicates اضافه شده است. گزینه ی جدید این امکان را به کاربر می دهد که تنها ردیف هایی را که در ستون حاضر دارای مقادیر مشترک و تکراری هستند نگه داشته و سایر ردیف های را حذف کند.

افزودن راهنما برای sample input values در پنجره ی Change Type with Locale در اکسل 

این قابلیت تبدیل جدید؛ به کاربر این امکان را می دهد تا فیلد های موجود در یک ستون را بر حسب تنظیمات محلی تغییر دهد.

برای استفاده از این امکان لازم است کابر مقدار محلی را برای تمام ورک بوک تنظیم کند.

(Data >New Query > Query Options > Current Workbook > Data Load)

با کلیک راست بالای ستون در Query Editor preview و انتخاب Change Type > Using Locale  پنجره ی Change Type with Locale به نمایش در خواهد آمد که این امکان را به کاربران می دهد که در زمان انتخاب نوع خاصی از داده یا محلی سازی؛ به تعدادی مقادیر نمونه دسترسی داشته باشند.

پشتیبانی از فضای سفید در Query Editor preview

Query Editor preview به کاربران اجازه خواهد داد تا نمایش هر نوع کاراکتر های سفید را در سلول ها مشاهده کنند

هرچند برخورد پیشفرض برنامه با این کاراکتر ها بر روی show whitespace تنظیم شده، اما کاربران همچنان این امکان را خواهند داشت از سربرگ View در نوار Query Editor این تنظیم را تغییر دهند

قابلیت غیرفعال سازی پیشنمایش در پنجره ی Navigator

در منوی Display options کاربر این امکان را خواهد داشت تا پیشنمایش را در پنجره ی Navigator غیرفعال کند

پشتیبانی از Technical name در پنجره ی Navigator

این قابلیت جدید به کاربر اجازه می دهد تا نحوه ی آدرس دهی را از physical name به technical name تغییر دهد.

حسن این شیوه ی آدرس دهی در آن است که برای کابر نهایی قابل فهم تر خواهد بود.

تغییر نام کوئری ها از کوئری موجود در Query Editor

درون Query Editor کاربر می تواند به سرعت با کلیک راست بر روی کوئری ها یا دابل کلید و انتخاب آنها و سپس فشردن کلید F2  کوئری ها را تغییر نام دهد.

پشتیبانی از تنظیم مقدار مجاز وقفه در اجرای یک دستور

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

امکان غیرفعال سازی تنظیمات حریم خصوصی موجود در سطح ماشین (شامل کلید های رجیستری)

از آنجا که Power Query به کاربران اجازه می دهد داده ها را از منابع متعدد در یک ورک بوک ادغام کنند تنظیمی تحت عنوان Privacy Levels وجود دارد که کاربران می توانند برای هر منبع که برای ادغام اطلاعات به آن وصل می شوند یکی از سطوح عمومی، سازمانی و یا خصوصی را تعیین کنند.

از آنجا که سطوح متفاوت privacy کاربران را از انواعی از ترکیبات و ادغام داده های که ناقض این تنظیمات است باز می دارد، تیم آفیس به کابران اجازه می دهند تا یکی از سه حالت زیر را از طریق پنجره ی Query Options انتخاب کنند.

• ترکیب داده های بر پایه ی سطوح Privacy برای هر منبع – این امکان جدید به کاربر اجازه می دهد تا سطوح Privacy موجود را بر روی تمام فایل های.XLSX موجود اعمال کند.
• ترکیب داده ها بر اساس سطوح Privacy هر فایل – عملکرد پیشفرض که با عملکرد موجود در نسخه های قبلی یکیست این گونه است.

• نادیده انگاشتن سطوح Privacy – این قابلیت جدید به کاربر اجازه می دهد تا همیشه بدون توجه به تنظیمات ورک بوک حاضر، از محدودیت Privacy برای هر کدام از فایلهای.XLSX عبور کنند.