کاربردهای Data Validation در Excel

به طور خلاصه می توان کاربردهای Data Validation را بصورت زیر عنوان کرد:

• ایجاد لیستهای کشوئی یا Drop-Down که به واسطه آن کاربران داده های خود را سریعتر، دقیقتر و با اشتباه کمتر وارد می نمایند، در ضمن به واسطه آن کاربران ملزم به انتخاب یک داده مشخص و از پیش تعیین شده در لیست می باشند.
• ایجاد محدودیت برای ورود داده در سلولها بر اساس شروط مختلفی از قبیل (کوچکتر یا بزرگتر بودن از مقدار خاصی، دقیقاً برابر بودن با مقداری خاص، مابین دو مقدار خاص بودن و …)
• ایجاد راهنما برای ورود داده ها در سلولها
• نمایش پیغام خطا هنگام رعایت نکردن شروط مذکور و …                                                

  ---

  ایجاد لیستهای کشوئی یا Drop-Down دراکسل:
  برای تفیهم بهتر موضوع، کاربرگ یا Sheet زیر را در نظر بگیرید:

   

  در کاربرگ مذکور، ستونهائی از قبیل محل تولد، مدرک و رشته تحصیلی می توانند مواردی از پیش تعیین شده و مشخص باشند که برای آنها از لیستهای کشوئی یا Drop-Down List استفاده می نمائیم. بدین منظور ابتدا می بایست داده هایی که در هر لیست هستند در ستونی جداگانه از کاربرگ جاری و یا در کاربرگی دیگر درج گردند. به عنوان نمونه همانند تصویر زیر، لیست محل تولد را که دربرگیرنده نام استانهای کشور است در کاربرگی دیگر ایجاد می کنیم:

  لهای مورد نظر را برای ایجاد لیست انتخاب می کنیم و بر روی گزینه Data Validation کلیک می کنیم:

   

  در ویزارد Data Validation در تب Setting همانگونه که ملاحظه می نمائید در قسمت Allow گزینه Any value بصورت پیش فرض انتخاب شده است یعنی ورود هر داده ای در سلولهای انتخاب شده مجاز می باشد.
  حال برای ایجاد لیست کشوئی در قسمت Allow همانند تصویر زیر گزینه list را انتخاب می کنیم، با انجام این کار فیلدی با عنوان Source نمایان می شود که برای آدرس دهی به همان لیست ایجاد شده از قبل (استانهای ذکر شده برای محل تولد) می بایست مورد استفاده قرار گیرد، برای این منظور شما می توانید آدرس دادهها را در کادر تایپ نمائید و یا با استفاده از آیکن مشخص شده در کنار کادر، با استفاده از اشاره گر موس ناحیه داده ها را به حالت انتخاب درآورده و طبق تصویر زیرین بر روی آیکن مشخص شده کلیک کنید:

   

   

  
  با انجام این کار آدرس ناحیه انتخاب شده همانند تصویر در کادر Source قرار می گیرد:

   

  حال بر روی OK کلیک کلیک کنید، زین پس طبق تصویر زیر در کاربرگ خود و در ستون محل تولد، در کنار هر سلول شکل مثلثی ظاهر می گردد که با کلیک کردن بر روی آن، قادر به انتخاب یک استان از لیست کشوئی می باشید:

   

  ---

  گزینه In-cell DropDown :
  حال در ستون مدرک نیز مطابق موارد مذکور در بالا، لیستی شامل موارد (زیر دیپلم-دیپلم-کاردانی-کارشناسی-ارشد-دکتری) را ایجاد می کنیم، اما این بار تیک گزینه In-cell DropDown را بر می داریم:

   

  با انجام این کار طبق تصویر زیر، شکل مثلث جهت باز شدن لیست کشوئی نمایش داده نمی شود و شما می بایست مقدار مورد نظر خود را تایپ نمائید. حال اگر مقدار وارد شده با یکی از موارد موجود در لیست مطابقت داشته باشد، داده ثبت می شود، اما اگر در کوچکترین حرفی مطابقت نداشته باشد، خطای (The value you entered is not valid) ظاهر می گردد:

   

  نکته: پس از انجام تنظیمات فوق، در حالی که سلولهای مورد نظر در حالت انتخاب قرار دارند، با کلیک کردن مجدد بر روی گزینه Data Validation در Ribbon قادر به تغییر تنظیمات مذکور خواهید بود، به عنوان نمونه با فعال کردن گزینه In-cell DropDown در سلولهای ستون مدرک، مجدداً لیست کشوئی برای این سلولها همانند تصویر زیر نمایان خواهد شد:

   

   

  ---

  حال فرض کنید، ستونی با عنوان «کد ملی» دارید که می بایست در هر سلول دقیقاً 10 رقم جهت کاهش خطا درج گردد، بدین منظور همانند تصویر زیر ابتدای سلولهای مورد نظر در ستون کد ملی را انتخاب می کنیم و پس از انتخاب گزینه Data Validation از منوی Data، در تب Setting، گزینه Text Length را در قسمت Allow، گزینه Equal To را در قسمت Data و مقدار 10 را در قسمت Length تنظیم می نمائیم:

   

  پس از انجام تنظیمات فوق، در هریک از سلولهای ستون کد ملی، صرفاً داده ای با طول 10 کاراکتر می بایست وارد شود در غیر این صورت پیغام خطائی ظاهر می گردد:

   

  ---

  تب Input Message:
  حال برای جلوگیری از بروز خطا در وارد کردن اطلاعات می توانید با استفاده از تب Input Message راهنمائی لازم را انجام دهید به عنوان نمونه همانند تصویر زیر برای ستون کد ملی پیام زیر را در نظر می گیریم که با انتخاب هر یک از سلولهای این ستون ظاهر می گردد

   

  ---

  Circle Data Validation در اکسل :
  حال فرض کنید در برخی از ستونها از قبل داده هایی وارد شده داشته باشیم و پس از آن نسبت به اعمال Data Validation اقدام کنیم، به عنوان نمونه همانند تصویر زیر در ستون «رشته تحصیلی» از قبل داده هایی وارد می کنیم و سپس نسبت به اعمال محدودیت تعداد کاراکترهای مجاز بین 6 تا 20 اقدام می کنیم:

   

  همانگونه که ملاحظه می نمائید برخی از سلولها دارای شرط مذکور نمی باشند، برای مشخص شدن این سلولها کافیست طبق تصویر زیر گزینه Circle Data Validation را انتخاب کنید، با انجام این کار سلولهای فاقد شرط ذکر شده، با کادری قرمز رنگ مشخص می گردند:

   

  حال اگرزیر بر روی گزینه Clear Validation Circle کلیک کنید، این کادرها حذف خواهد گردید.

   

  علاوه بر موارد مذکور در این آموزش، همانند موارد مذکور، از طریق Data Validation قادر به ایجاد محدودیتهای دیگری در داده های ورودی در سلولهای اکسل خواهید بود از جمله؛ ایجاد بازه های عددی، زمان و تاریخ:

   

روش ساده تایپ عدد فارسی در اکسل که اکثر کاربران از آن استفاده می کنند، استفاده از Reginoal & Language می باشد. برخی از کاربران بر این باورند که تنظیم اکسل همانند تنظیم Word می باشد. ولی با اندکی دقت متوجه می شویم که راه فارسی کردن اعداد در برنامه Excel متفاوت می باشد.
اما یک راه کار بسیار ساده برای تایپ اعداد به شکل فارسی اینست که فونت خانه ای که درون آن عبارت خود را تغییر میدهیم را به: B lotus B Nazanin تغییر دهیم. و یا کلا از فونتهایی استفاده کنیم که با حرف B آغاز می شود.

راه حل کاملتر برای تنظیم اعداد فارسی در اکسل

برای انجام اینکار به قسمت Format Cell بروید و گزینه ی Custom را انتخاب کنید و کد زیر را برای فارسی کردن اعداد وارد کنید:
[$-3020429]0  در این روش نیاز به نصب فونت نیست.با فونت arial هم کاملا خوانا می شود.

حل مشکل حرف ی در اکسل

ممکن است حرف "ی" بروی کیبوردتان مدل های مختلفی داشته باشد یا فایل های موجود شما دارای چند مدل از حرف "ی" باشد که هر کدام از این مدل "ی" ها دارای کد خاصی باشند.
این مشکل راه حلهای مختلفی دارد از راههای وقت گیر و زمان بر باید راه کدنویسی را در پیش بگیریم.
یکی از راههای به وجود آمدن این مشکل فایلهای فارسی ساز ویندوز است که نسخه های مختلفی دارند. برای حل این مشکل می توانید از فونت های اصلی ویندوز مثل Tahoma استفاده کنید یا اینکه از فونت هایی که اول آنهاB است همچنین می توانید از فونتهای B zar و یا B zar , B Lotus , B Nazanin استفاده کنید.
اگر با اینکار مشکل حل نشد باید ویندوز خود را عوض کنید و در طی مراحل نصب ویندوز زبان فارسی را انتخاب کنید خوب است بدانید در اکثر مواقع این مشکل زمانی به وجود می آید که زبان فارسی را از داخل محیط ویندوز انتخاب می کنید.

اصطلاحات پر کاربرد نمودارها در اکسل  :
1-         Gird Line: خطوطی هستند که در امتداد مقادیر محور X , Y ظاهر می شوند. این خطوط در تعیین مقدار دقیق یک نقطه به ما کمک می کنند.
2-         Tick: خطوط کوتاهی هستند که با محور متقاطع بوده و قسمتهایی از یک مقیاس سری با گروه را جدا می کنند.
3-         Chart Title: متن بکار برده شده برای شناسایی عنوان نمودار می باشد.
4-         Axis: به محور‌ها در نمودار‌ها گفته می شود.
5-         Legend: هر نمودار دارای راهنمایی است که نشان دهنده این است که هر رنگ مربوط به کدام سری است.
 
روشهای ایجاد نمودار  در  excel:
روش اول:
1-                انتخاب منوی Insert
2-                انتخاب گزینه Chart
روش دوم:
1-    انتخاب آیکون Chart Wizard
نکته: در زمان ایجاد نمودار با کلیک بر روی دکمه Next به صفحات بعدی میرویم و کلیک بر روی دکمه Back باعث برگشت به صفحه قبلی می شود و با کلیک بر روی Finish کادر نمودار‌ها بسته می شود. نکته می توانیم اطلاعاتی را که میخواهیم بر اساس آن نمودار رسم کنیم ابتدا انتخاب کنیم بعد به یکی از دو روش بالا عمل کنیم. می توانیم هم در حین کار انتخاب را انجام دهیم.
با انجام یکی از دو راه بالا پنجرهای زیر به ترتیب باز می شود:
-          پنجره Chart Type: این پنجره شامل دو Tab زیر می باشد:
1)         Standard Type: که شامل قسمتهای زیر است:
الف)  Chart Type: که در این قسمت نوع اصلی نمودار را انتخاب می کنیم.
ب)   Chart Sub-Type: در این قسمت نوع فرعی نمودار را مشخص می کنیم.
ج)    Press and Hold to View Sample: در این قسمت پیش نمایشی از نمودار با اطلاعاتی که خودمان داده ایم نشان می دهد. برای این کار کافی است روی این دکمه Click کرده و نگه داریم.
2)         Custom Type: در این قسمت می توان از نمودارهای سفارشی استفاده کرد یا یک نوع نمودار ایجاد کرد.
 
-          پنجره Chart Source Data: این پنجره شامل دو Tab زیر می باشد:
1)         Data Range: که شامل قسمتهای زیر است:
الف) Data Range: در این قسمت آدرس داده هایی را که قرار است نمودار برای آنها کشیده شود، می توان تغییر داد همچنین می توانیم آدرس را با کلیک بر روی Collapse Dialog و با Drag کردن وارد کنیم. و یا می توانیم با استفاده از فرمت زیر آدرس را تایپ کنیم. شماره سطر پایان $ نام ستون پایان$ شماره سطر شروع$ نام ستون شروع$! نام Sheet =
ب) Series in: این قسمت جهت نمایش داده‌ها را مشخص می کند. یعنی کدام یک از این دو برچسب از داده‌ها در پایین نمودار قرار خواهد گرفت. اگر Row را انتخاب کنیم سطر‌ها به عنوان سری و اگر Column را انتخاب کنیم ستونها به عنوان سری قرار می گیرند.
2)         Series: که شامل قسمتهای زیر است:
الف) Series: در این قسمت نام سری‌های موجود نوشته شده است.
ب)   Name: توسط این قسمت می توانیم نام سری را عوض کنیم.
ج)    Value: در این قسمت می توانیم آدرس مقادیر هر سری را مشخص کنیم. همچنین می توانیم مقادیر را تایپ کنیم. برای تایپ مقادیر باید مقدار سری را در علامت { } وارد کنیم.
د)    Category(x) Axis labels: در این قسمت می توان آدرس خانه هایی را داد که محتوای آنها بر روی محور X نوشته می شوند.
ه)     دکمه Add: یک سری جدید ایجاد می کند.
و)     دکمه Remove: بر روی نام هر سری در قسمت Series کلیک کنیم و سپس این دکمه را بزنیم، سری حذف می شود.
-          پنجره Chart Option: این پنجره شامل 6 Tab زیر است:
1)             Titles: این قسمت شامل موارد زیر است:
الف) Chart Title: در این قسمت عنوان نمودار را مینویسیم.
ب)   Category(x) Axis labels: در این قسمت عنوان محور X را مینویسیم.
ج)    Value (Y) Axis: در این قسمت عنوان محور Y را مینویسیم.
2)             Axes: این قسمت شامل گزینه‌های زیر است:
الف)  Category(x) Axis اگر در کنار این کادر تیک خورده باشد، مقادیر روی محور X نشان داده می شوند و اگر تیک نخورده باشد نشان داده نمی شوند.
ب)   Value (Y) Axis اگر در کنار این کادر تیک خورده باشد، مقادیر روی محور Y نشان داده می شوند در غیر این صورت نمایش داده نمی شوند.
3)             Grid Line: که این Tab شامل قسمتهای زیر است:
الف)  Category(x) Axis که خود شامل دو قسمت است:
×      Major Gridline: اگر این قیمت تیک خورده باشد، خطوط شبکه رسم می شوند (تمامی خطوط رسم نمی شوند)
×      Minor Gridline: اگر این قسمت تیک خورده باشد خطوط دیگری بین خطوط بالا رسم می شوند. این دو قسمت برای خطوط شبکه موازی محور Y‌ها هستند.
ب)   Value (Y) Axis: که شامل دو قسمت زیر است:
×      - Major Gridline: خطوط شبکه را به موازات محور X رسم می کند.
×      - Minor Gridline: خطوط دیگری را به موازات محور X بین خطوط بالا رسم می کند.
4)             Legend: این Tab شامل قسمتهای زیر است:
الف)  Show Legend: اگر این گزینه تیک خورده باشد راهنما نمایش داده می شود.
ب)   Placement: مکان راهنما را نشان می دهد. Bottom: پایین، Corner: گوشه بالا سمت راست، Top: بالا، Right: سمت راست، Left: سمت چپ
5)             Data Label: شامل قسمتهای زیر است:
الف) None: هیچ مقداری را بر روی ستونها نمایش نمی دهد.
ب)   Show Value: مقدار هر سری رابر روی ستون آن نشان می دهد.
ج)    Show Label: برچسب هر سری را روی آن نشان می دهد.
د)    Legend Key Next to Label: اگر یکی از حالتهای ب و ج را انتخاب می کنیم. این گزینه فعال شده و در کنار هر مقدار رنگ سری نمایش داده می دهد.
6)              Data Table: شامل قسمتهای زیر است:
الف)  Show Data Table: اگر این گزینه فعال شود، جدولی مشابه آنچه که ما، در Sheet کشیده ایم در زیر نمودار ظاهر می شود.
ب)   Show Legend Keys: اگر گزینه الف فعال باشد، این گزینه نیز فعال می شود. و اگر در کادر آن تیک بزنیم، رنگ هر سری را در کنار اطلاعات آن سری در جدول نشان می دهد.
نکته:   باید توجه داشته باشیم که که با توجه به نوع نمودار گزینه‌های موجود در این پنجره می توانند متفاوت باشند.
-           پنجره Chart Location:
الف)  As New Sheet: این گزینه نمودار را در یک Sheet جدید با اسم دلخواه (اسم پیش فرض Chart 1 است) ایجاد می کند. نموداری که در این حالت ایجاد می شود، قابل جابجا شدن و تغییر سایز نیست ولی هر گونه تغییرات بر روی اطلاعات اصلی بر روی نمودار تاثیر دارد.
ب)   As Object in: این گزینه نمودار را در هر Sheet که ما انتخاب می کنیم، (از Sheet‌های موجود) رسم می کند و قابل جابجا کردن و تغییر سایز نیز می باشد.
 
تغییر بر روی نمودار ایجاد شده:
برای تغییر نمودار ایجاد شده باید ابتدا نمودار را انتخاب کرد. سپس مجدداً دکمه Chart Wizard راکلیک کرد. یا از روی Toolbar، Chart که باز می شود، تغییرات را اعمال کرد.
 
نوار ابزار Chart در اکسل:
این نوار ابزار را می توانیم در صورتی که فعال نبود با Right Click بر روی نوار ابزار‌ها و انتخاب گزینه Chart فعال کنیم این نوار ابزار دارای آیکونهای زیر است:
×      Chart Object: در این قسمت می توانیم قسمتی از نمودار را که میخواهیم بر روی آن تغییر دهیم انتخاب کنیم. با انتخاب هر گزینه قسمت مربوط به آن روی نمودار انتخاب می شود.
×      Format: با توجه به اینکه در Chart Object چه انتخاب شده باشد این آیکون پنجره Properties آن را باز می کند.
نکته: با Double کلیک کردن بر روی هر موضوع از نمودار نیز پنجره Properties آن باز می شود.
×      Chart Type: در این قسمت می توانیم نوع نمودار انتخاب شده را تغییر دهیم.
×      legend: توسط این آیکون کادر راهنما را ظاهر یا پنهان می کنیم.
×      Data Table: توسط این آیکون می توانیم جداول داده‌ها را فعال یا غیر فعال کنیم.
×      By Rows:با انتخاب این آیکون سطر‌ها به عنوان سری انتخاب می شوند.
×      By Column: با انتخاب این آیکون ستونها به عنوان سری انتخاب می شوند.
×      Angle Text up: جهت متن نمودار را تغییر می دهد. یعنی متن از بالا به پایین، کج نوشته می شود. به شرطی این آیکون فعال است که قسمتهای متنی نمودار انتخاب شده باشد.
×      Angle Text down: متن نمودار را تغییر جهت می دهد. یعنی متن از پایین به بالا و کج نوشته می شود. به شرطی این آیکون فعال است که قسمتهای متنی نمودار انتخاب شده باشد.
تغییر اندازه:
1)         روی نمودار کلیک می کنیم تا انتخاب شود.
2)         روی یکی از مربع‌های سیاه اطراف نمودار کلیک می کنیم.
3)         مربع را تا زمانی که به اندازه دلخواه تغییر اندازه دهد Drag می کنیم.

برای تغییر پهنای ستون به یکی از دو روش زیر عمل می کنیم:
روش اول:
کافی است به مرز بین ستون مورد نظر و ستون بعدی رفته تا مکان نما به صورت یک فلش دو سر در آید سپس مرز ستون را در جهت مورد نظر Drag می کنیم.
روش دوم:
در این روش پهنا را به صورت دقیق تنظیم می کنیم. برای اینکار مراحل زیر را طی می کنیم:
1-    انتخاب منوی Format
2-    انتخاب زیر منوی Column
3-    انتخاب گزینه Width
4-    در کادر باز شده مقدار دلخواه را وارد می کنیم.
نکته: به طور پیش فرض پهنای ستون 8.43می باشد.
 
تغییر پهنای استاندارد ستون:
میدانیم که پهنای استاندارد ستونها 8.43 می باشد اگر بخواهیم این پهنای استاندارد را کم یا زیاد کنیم به ترتیب زیر عمل می کنیم: (فقط به یاد داشته باشید این تغییر بر روی ستونهایی اعمال می شود که پهنای آنها را تغییر نداده باشیم.)
1-    انتخاب منوی Format
2-    انتخاب زیر منوی Column
3-    انتخاب گزینه Standard Width
4-    در کادر باز شده پهنای مورد نظر را وارد می کنیم.
 
جستجوی داده ها:
اگر بخواهیم رشته کاراکترهای خاصی را در کاربرگ خود پیدا کنیم، مشاهده هر یک از سلولها کار سختی خواهد بود. پیدا کردن یک رشته از کاراکتر‌ها که باید تغییر یابند نیز مشکل تر می باشد. اکسل ویژگیهایی دارد که به ما در یافتن داده‌ها و تعویض آنها کمک می کند.
 
استفاده از Find: برای فعال کردن Find به یکی از دو روش زیر عمل می کنیم:
روش اول:
1- انتخاب منوی Edit
2- انتخاب find
روش دوم:
فشردن همزمان کلید‌های Ctrl F
جهت جستجوی داده‌ها مراحل زیر را انجام میدهیم:
1-        لغتها یا عددهایی را که میخواهیم مکان یابی کنیم در کادر متن Find What وارد می کنیم.
2-        دکمه Find Next را کلیک می کنیم و Excel اولین مورد از شرط جستجو را پیدا می کند و آن را انتخاب می نماید. کادر مکالمه Find بر روی صفحه نمایش باقی می ماند تا بدین ترتیب بتواند جستجو برای متن یا اعداد مشخص شده را ادامه دهیم.
3-        جهت جستجو برای مورد بعدی داده‌های مشخص شده Find Next را مجدداً کلیک می کنیم.
4-        جهت برگشت به محیط دکمه Esc را فشرده و یا بر روی دکمه Close کلیک می کنیم. تا کادر مکالمه Find بسته شود و سپس با داده‌های داخل سلول به کارمان ادامه میدهیم.
5-        جهت جستجو پس از بستن کادر مکالمه، با فشار دادن Ctrl F کادر مکالمه را می توانیم مجدداً باز نماییم.
نکته: اگر محدوده ای از سلول‌ها را در یک کاربرگ , قبل از باز نمودن کادر مکالمه Find را انتخاب کنیم، جستجو را برای این سلولها محدودکرده ایم.
 
برقراری شروط بیشتر برای Find:
کادر مکالمه Find با ارائه گزینه‌های زیر جستجو را با تخصصی تر نمودن آن محدود می سازد.
-      Search: می توان جهت جستجو را مشخص کرد. جستجو به صورت سطری باشد (By Row) یا به صورت ستونی باشد
-      By Column: با استفاده از این گزینه می توانیم جستجو را به یک عنصر خاص محدود کنیم در این کادر گزینه‌های Formula , Values, Comments را خواهیم داشت.
-      Match Case: با انتخاب این گزینه محیط به حروف بزرگ و کوچک حساس شده و کلماتی را پیدا می کند که از نظر حروف بزرگ و کوچک مطابق متن تایپ شده در کادر Find What باشد.
-      Find Entire Cell Only: این گزینه از Excel میخواهد سلولهایی را که داده‌های اضافه بر رشته کاراکتر جستجو دارند نادیده بگیرد.
 
تعویض داد ه‌ها استفاده از Replace:
اگر بخواهیم داده پیدا شده توسط Find را با داده‌های دیگر جایگزین کنیم می توانیم پس از هر بار جستجو پنجره جستجو را بسته، ویرایش را انجام داده، دوباره پنجره Find را باز کنیم و عمل جستجو را ادامه دهیم که این کار وقت گیر می باشد. Excel دستوری ساده برای انجام این کار در نظر گرفته است. با استفاده از Replace می توانیم این کار را انجام دهیم. جهت انجام جایگزینی مراحل زیر را انجام میدهیم:
1-    متن جستجو را در کادر Find What وارد می کنیم.
2-    متن تعویضی را در کادر متن Replace With وارد می کنیم.
3-    جهت تایید هر یک از تعویض‌ها مراحل زیر را دنبال می کنیم.
الف)     Find Next را کلیک کرده، Excel به مورد بعدی داده جستجو حرکت می کند و سلول را انتخاب می کند.
ب)      در صورتی که بخواهیم تعویض را انجام دهد دکمه Replace را کلیک می کنیم.
ج)       Find Next را جهت حرکت به موردبعدی کلیک می کنیم.
د)       جهت تعویض تمام موارد داده‌های جستجو با با دادههای تعویضی، Replace All را کلیک می‌کنیم.
 
پر کردن خودکار چند خانه در  excel
پر کردن خودکار چند خانه یکی از مفید ترین ویژگیهای برنامه Excel، قابلیت پر کردن داده‌های متوالی بصورت خودکار می باشد. که به این عمل Auto fill گویند. داده‌های متوالی می توانند مجموعه ای از داده‌ها بصورت 1و2و3و... یا 4و5و.... یا از حروف A و B و C و... تشکیل شده باشند. همچنین می تواند تاریخ یا روزهای هفته یا ماههای سال باشند. حتی می توان مجموعه ای از داده‌های خاص را تعریف کرد. برای این کار مراحل زیر را انجام میدهیم:
1-    چند نمونه اولیه از مقادیر متوالی مزبور را مینویسیم.
2-    این مقادیر را انتخاب می کنیم.
3-    مکان نمای ماوس را به گوشه پایین سمت راست قسمت انتخابی می آوریم تا به شکل ()در آید. به اشاره گر ماوس در این حالت Fill Handle می گوییم.
4-    حال به طرف پایین یا راست Drag می کنیم.
5-    به این ترتیب با رها کردن کلید ماوس، خانه‌های مورد نظر با مجموعه داده‌های متوالی پر می شوند.
 
ایجاد لیست جدید خودکار:
اگر برای حروف الفبا Auto fill را اجرا کنیم، پر کردن خودکار انجام نمی شود. چون حروف الفبا جزء لیستهای آماده نیست پس باید ایجاد شود. برای ایجاد لیست جدید مراحل زیر را انجام میدهیم:
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب Custom list: Tab
4-    انتخاب New List در کادر Custom List
5-    تایپ اطلاعات در قسمت List Entries (بعد از وارد کردن هر اطلاعات Enter می کنیم.)
6-     انتخاب دکمه Add
7-    OK
ایجاد یک لیست خودکار بر اساس لیست موجود در صفحه در EXCEL:                                                 اگر لیستی بر روی صفحه داشته باشیم و بخواهیم بصورت لیست دائمی در آوریم باید مراحل زیر را انجام دهیم.:
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب Custom list: Tab
4-    روی دکمه Collapse dialog که در انتهای کادر فهرست Import List From Cells قرار دارد Click می کنیم. تا پنجره مینیمایز شود.
5-    انتخاب لیست‌ها در صفحه
6-     روی دکمه Collapse Dialog که در انتهای کادر فهرست Import list From Cell قرار دارد Click می کنیم. تا Resize شود.
7-    انتخاب دکمه Import
8-    OK
نکته: می توانیم بجای انجام مراحل 4و 5و 6، در کادر Import، آدرس سلولهایی که لیست در آنها قرار دارد را تایپ کنیم. ویرایش لیست خودکار می توانیم لیستهایی را که ایجاد کرده ایم ویرایش کنیم. یعنی اطلاعاتی را حذف کنیم، ویرایش کنیم یا اضافه کنیم. برای این کار مراحل زیر را انجام میدهیم:
1-    انتخاب منوی Tools
2-    انتخاب گزینه Option 3
3-    انتخاب:Custom list Tab
4-    انتخاب لیست مورد نظر در کادر Custom list
5-    کلیک در محل مورد نظر در کادر List Entries
6-     ویرایش متن
7-    OK
 
پاک کردن لیست خودکار در اکسل:
اگر بخواهیم یک لیست خودکار حذف کنیم تا دیگر پر کردن خودکار بر روی آن عمل نکند مراحل زیر را طی می کنیم:
1-    انتخاب منوی Tools
2-    انتخاب گزینه Options
3-    انتخاب:Custom list Tab
4-    انتخاب List مورد نظر در کادر Custom List
5-    انتخاب دکمه Delete
6-     OK

 در نرم افزار اکسل برای تنظیم سلول ها می توان از روروش های زیر اسیتفاده نمود                          برای قالب بندی خانه هادر اکسل  مراحل زیر را طی می کنیم:
 روش اول: از تب Home  منو Number  را انتخاب کنید.
روش دوم: بر روی خانه های مورد نظرکلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.
روش سوم: فشردن کلیدهای ctrl +

با اجرای یکی ازسه روش بالا پنجره ای باز می شود که  Tab Number را انتخاب می کنیم:

در Tab ذکر شده  Sample  و Category مشاهده می شود که هر کدام به شرح زیر می باشد.

•  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 آن را به صورت متن نشان می دهد و مقادیر آنرا محاسبه نمی کند. (کاردرکارگاه 7 تمرین 8 تا 13)
11- Special: مقادیر را با استفاده از قواعد قالب بندی خاص (مانند کد پستی، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) نشان می دهد. مثلاً اگر در یک سلول یک شماره تلفن (در حالت 10 رقمی) تایپ کنیم، با انتخاب این فرمت به صورت قالب بندی تلفن در می آید. مثلاً اگر شماره IT (3116681184) را وارد می کنیم، به فرمت تلفن در آمده (1184-668 (311)) و می فهمیم که 311 کد شهرستان

، 668 کد محله و 1184 شماره تلفن IT می باشد.

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

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

این لیست بر اساس اکسل 2016 تهیه شده است اما بیشتر آن‌ها بر روی اکسل 2013 نیز کار خواهد کرد.

1. دسترسی به منوی ابزار توسط صفحه کلید: اکسل مثل افزونه Vim-inspired در کروم و فایرفاکس قابلیتی به نام Key Tips دارند که وقتی کلید Alt را می‌فشارید، منوی Ribbon با یک سری حروف پر می‌شود. حرف مورد نظر بر روی صفحه کلید را بزنید تا آن گزینه‌ی مشخص فعال شود.
2. Ctrl + PgDn: سوئیچ میان سربرگ‌های صفحه گسترده. حرکت از چپ به راست.
3. Ctrl + PgUp: سوئیچ میان سربرگ‌های صفحه گسترده. حرکت از راست به چپ.
4. F12: نمایش کادر “Save As”
5. Ctrl + Shift + $: (اکسل 2016) تغییر فرمت سلول فعلی به پول با دو رقم اعشاری
6. Ctrl + Shift + %: (اکسل 2016) تغییر فرمت سلول فعلی به درصد بدون رقم اعشاری
7. Ctrl + Shift + #: (اکسل 2016) تغییر فرمت سلول فعلی به تاریخ با روز، ماه، سال
8. Ctrl + Shift + “:”: قراردادن زمان فعلی
9. Ctrl + Shift + “;”:قراردادن تاریخ فعلی
10. F4: تکرار آخرین فرمان و عمل، در صورت امکان
11. Shift + Arrow key: گسترش سلول انتخابی فعلی با یک سلول دیگر در جعت انتخابی
12. Ctrl + F1: نمایش یا مخفی کردن نوار ابزار
13. Alt + Shift + F1: ایجاد یک سربرگ صفحه گسترده جدید
14. Ctrl + F4: بستن صفحه گسترده فعلی
15. Ctrl + D: اجرای فرمان Fill Down برای سلول‌های انتخابی زیرین. Fill Down محتوا و فرمت بالاترین سلول را در کل ستون کپی می‌کند.

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

برای این منظور از ترکیب سه تابع sum، IF،Frequncey  در اکسل  استفاده می کنیم.

فرض کنید در ناحیه A2:A12 تعدادی عدد وارد کرده ایم. فرمول را به شکل زیر می نویسیم:

=SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0))

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

برای درک بهتر این فرمول ابتدا باید نتیجه تابع FREQUENCY را بررسی کنیم.

شکل کلی تابع FREQUENCY به این صورت است.

FREQUENCY(data_array,bins_array)

همانطور که در فرمول می بینید به جای data_array و bins_array ناحیه A2:A12 قرار گرفته است. توجه داشته باشید اگر از  تابع FREQUENCY به تنهایی (بدون ترکیب با فرمول دیگری) استفاده می کنید باید آن را به صورت آرایه ای بنویسید.

در این مثال ما تعداد 11 عدد را در ناحیه A2:A12 وارد کرده ایم. اگر بخواهیم با استفاده از تابع FREQUENCY تعداد تکرار هر عدد را حساب کنیم باید ابتدا تعداد 11 سلول را انتخاب نموده و سپس فرمول  (FREQUENCY(A2:A12,A2:A120 را در اولین سلول نوشته و کلیدهای ترکیبی ctrl+shift+enter را فشار دهیم تا فرمول به صورت آرایه ای ثبت شود.

تذکر: در فرمول های آرایه ای علامت های {    } در ابتدا و انتهای فرمول به صورت اتوماتیک نوشته می شوند.

در فرمول =SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0)) اگر تعداد تکرار در تابع FREQUENCY بیشتر از عدد صفر باشد آن عدد یک باردر محاسبه منظور می شود و اگر صفر بود یعنی تکرار آن عدد بیش از یک بار بوده و در جمع در نظر گرفته نمی شود.

در ستون A اعداد تکراری مشخص شده اند

فرمول ستون B به صورت آرایه ای ثبت شده است

در ستون C اگر مقدار ستون B از 0 بیشتر شود عدد واقع در ستون A قرار می گیرد و در غیر این صورت نتیجه تابع IF عدد 0 می باشد

در ستون D جمع نتیجه تابع IF محاسبه شده است.

یکی از توابع سودمند تابع “RANDBETWEEN” در اکسل  است. این تابع به ما امکان تولید اعداد تصادفی را می‌دهد. مقدار بازگشتی این تابع یک عدد دربازه انتخابی  است. برای اینکه ما بتوانبم عدد مورد نظر را در بازه مورد نظر تولید کنیم کافیست:

1 بر روی خانه مورد نظر کلیک کرده و عبارت “RANDBETWEEN=” را درج می نماییم.

2 پارامتر اول آن را با مقدار ابتدای بازه و پارامتر دوم آن را با مقدار انتهایی بازه مقدار دهی کنیم.به عنوان نمونه (RANDBETWEEN(1,100) که عددی تصادفی بین 1 تا 100 برای ما تولید می‌کند.

3 عدد به دست آمده را مشاهده می کنید.                                                                                     استفاده ازتابع  شرطی (if) در اکسل                                                                    

یکی از توابع منطقی سودمند دراکسل تابع “if” است. این تابع شرط مورد نظر را بررسی و با توجه به نتیجه شرط مقادیر مورد نظر را درج می‌کند. استفاده از تابع شرطی به ما امکان تصمیم گیری پویا بر اساس مقادیر ورودی را می‌دهد. برای استفاده از “if” موارد زیررا انجام می‌دهیم:

1 تایپ مساوی “=” در خانه مورد نظر و نوشتن عنوان “)if”.

2 با استفاده از دکمه “insert function” و باز کردن پنجره “function arguments”.

3 درج شرط در قسمت “Logical_test” (به عنوان مثال B3>30  که بررسی مقدار خانه B3 است).

4 درج مقداری که در صورت درستی شرط می‌خواهیم درج شود در قسمت “value_if_true”(مثلاً مقدار:گرم).

5 درج مقداری که در صورت نادرستی شرط می‌خوهیم درج شود در قسمت “value_if_false” (مثلاً: خنک).

6 همان طور که مشاهده می کنید دما پایین تر از 30 درجه هست به همین دلیل در قسمت وضعیت  شرط “خنک” نشان داده شده.

کاربرد تابع CONCATENATE

فرض کنید یک فایل اکسل دارید که در  ستون A نام و در  ستون B نام خانوادگی رانوشته اید و واکنون میخواهیدکه در ستون C نام و نام خانوادگی را با هم داشته باشید به صورتی که بین نام و نام خانوداگی هم فاصله وجود داشته باشد.                                                                                                       در ادامه باما   همراه باشید                     

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

=A1&” “&B1

این فرمول به راحتی برای ما مقادیر موجود توی سلول A1 و B1 را با در نظر گرفتن یک فاصله بین اونها به ما نمایش میده. ولی باید بدونید که برای این کار یک تابع مخصوص وجود دارد به نام CONCATENATE

کاربرد تابع  CONCATENATE در اکسل

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

جدا کردن نوشته های یک ستون و تقسیم آن به دو ستون در excel

فرض کنید در سلولی  ستون A1:A10 تعداد 10 تا نام نام خانوادگی وارد کردید که نام با فاصله از نام خانوادگی جدا شده اند ولی همراه سلول قرار دارند و هدف ما این است که  کاری کنیم که نامها در ستون B1:B10 قرار بگیرn و نام خانوادگی در ستون C1:C10 نوشته بشه برای این کار اکسل یک قابلیتی در نظر گرفته به نام Text To Columns که متن  را از ستونها جدا می کنه.

برای استفاده از این قابلیت باید به تب Data برید و گزینه Text To Columns رو انتخاب کنید  گزینه Next رو بزنید.

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

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

 قفل کردن سلول های در اکسل

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

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

 1- ابتدا با استفاده از Ctrl+A تمام سلول های کاربرگ را انتخاب کنید.

2- سپس بر روی کاربرگ کلیک راست کنید. و گزینه Format Cells  را انتخاب نمایید.

3- در پنجره Format Cells  بر روی سربرگ Protection کلیک کنید و تیک گزینه Locked را غیر فعال کنید و بر روی OK کلیک نمایید.

4- سلولهایی که حاوی فرمول هستند را انتخاب کنید و دوباره مرحله 3 را انجام دهید. ولی در این مرحله تیک گزینه Locked  را فعال کنید.

5- بر روی تب Review کلیک کنید. و بر روی گزینه Protect Sheet کلیک کنید.

6- در پنجره Protect sheet به جز گزینه ُSelect Lock Cells تیک تمام گزینه ها را فعال کنید و رمز عبور خود را وارد نمایید.

7- در پنجره بعدی هم رمز عبور خود را دوباره وارد کنید و بر روی Ok کلیک کنید.


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