ترفندخالی نشان دادن (تهی نشان دادن) سلول با فرمول

شما حتماً بارها فرمولی را در اکسل نوشته اید که نتیجه خطا

 میدهد نه به دلیل اینکه فرمول اشتباه باشد ،بلکه  به دلیل اینکه

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

مشکلهایی از اینقبیل.مثلاً فرمولی رانوشته اید که دو سلول

A1 و B1 را به هم تقسیم میکند اگر سلولهایی کهفرمول به آنها 

رجوع میکند خالی باشد نتیجه فرمول خطای !DIV/0.# خواهدبود 

وشما نمیخواهید اکسل این خطارا نمایش بدهد .

راههای زیادی برای حل این مشکل وجود دارد که دو راه فرمولی آن 

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

هرگونهخطا ویا خالی بودن سلولها را به دلخواه خود تغییر دهید 

 که نحوه نوشتن آن به صورت زیر هست  

  

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

           =IFERROR(A1/B1;"")   

            

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

          =IF(B1="";"";A1/B1)

                                                             

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

          =IFERROR(A1/B1;"لطفاً خانه های خالی را پرکنید")   

 

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

 قرار میگیرد           

          =IF(B1="";"لطفاً خانه های خالی را پرکنید";A1/B1) 

 

نکته: حتماً به این نکته توجه داشته باشید که در بعضی از نسخه های اکسل از کاما (,) 

 

  به جای سمیکلن  (;)  در فرمولها استفاده میشود

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

1-درس اول: ایجاد فرم در اکسل

  A) TextBox ,CommandButton

 

چرا گوشه سلولها سبز میشود؟(علامت سبز)

برای فهمیدن این مطلب به مسیر زیر بروید. 

                     Office Botton / Excel Options/Formulas

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

به چه علتهایی گوشه سلولها  سبز میشود وهمچنین متوجه 

خواهید شد که چگونه علامت سبز سلولها را بردارید را ویا رنگ

 آن را عوض کنید.

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

          شما حتماً با روش comment  که در زبانه Review قرار دارد آشنا هستید که با 

 

آن روش ، عکس را در comment قرار میدادیم .ولی عکس با تغییر اسم تغییر نمیکرد.

 

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

 

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

 

عکس پرچم آن هم نمایش داده خواهد شد.شما میتوانید با یادگیری این ترفند برای عکسهای

 

پرسنلینیز چنین کاری را بکنید  ،راهنمای ساخت در خود فایل هست.

 

 

 

خرید فایل

 

حجم 2349کیلوبایت

قیمت 1000 تومان

 

فارسی ساز آفیس 2007

ِدانلود رایگان فارسی ساز آفیس 2007

Download

حجم: 14.09 mb

منبع:www.yjc.ir

بعد از نصب به مسیر زیر رفته واز کادر کشویی اول فارسی را انتخاب کنید لازم به ذکر هست help را

 نمیتوان فارسی کرد.

 Start/All Programs/ Microsoft Office/ Microsoft Office 2007 Tools / Microsoft Office 2007 Language settings / Display language

دانلود فایل رنگی کردن سلولهای تکراری با Check Box

Duplicate Value by color

Download

لینک توضیحات:http://yazdexcel.blogfa.com/post/6

ضرب ساعت در عدد در اکسل 2007

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

 

مثلاً شما فرض کنید زمان 23:10:20 زمان کاری فردی هست وشما مبلغ هر ساعت کاری رو دارید ومیخواهید این زمان را به یک عدد یعنی فقط ساعت تبدیل کرده ودر مبلغ ضرب کنیدتا مبلغ کاری فرد را بدست آورید.در زیر با روشهای مختلف این کار آشنا خواهید شد.

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

1- در این روش که ساده ترین روش هم هست اینه که شما یک فرمول ضرب  ساده را  نوشته بعد با انتخاب ستون جوابها وسپس راست کلیک وانتخاب Format Cells بعد زبانه Number وسپس انتخاب Custom ودر آخر در کادر خالی کد [h] را تایپ کرده و ok کنید.این روش جنبه نمایشی دارد چون عدد های بدست آمده اون ارزش عددیی که مورد نظر شماست را ندارد اگر با عدد دیگری جمع کنید پاسخ درستی به شما نمیدهد من این روش را به شما توصیه نمیکنم.

 

2-این روش در Help اکسل هم آمده هست و شرح آن به این صورت هست که شما فرض کنید در خانه A1 زمان را تایپ کرده اید ودر خانه A2 مثلاً مبلغ ساعت کاری را یادداشت کرده اید        حالا در خانه   A3  با   فرمول              A1-INT(A1))*24)=    زمان موجود در خانه A1 را به عدد تبدیل میکنیم که همان ساعت کاری هست که به صورت عدد میباشد،اکنون که شما عدد ساعت را دارید در مبلغ ضرب کرده وبه جوابتون برسید. این روش تا زمان 23:59 جواب میدهد واز ساعت 24 به بالا چون یک روز به حساب میاید یک روز را از آن کم کرده وبقیه زمان را اعلام میکندمثلاً زمان کاری 24:10 با این فرمول فقط 10 دقیقه آن را به عدد تبدیل میکند که تقریباً 0.17 هست.

توجه: حتماً توجه داشته باشید که فرمت ستونی که شامل فرمول هست باید  Number ویا General باشد.

3- این روش مانند روش دستی هست یعنی ما از زمان اعلام شده ساعت ،دقیقه وثانیه را به صورت جدا بیرون کشیده ودقیقه وثانیه را به ساعت تبدیل میکنیم ودرآخر همه را با هم جمع میکنیم که نتیجه یک عدد هست که تقریباً شبیه  روش دوم میباشد فقط در فرمول نویسی متفاوت هست.

روش کار : شما فرض کنید در خانه A1 زمان را تایپ کرده اید ودر خانه A2 مثلاً مبلغ ساعت کاری را یادداشت کرده اید حالا در خانه A3 با  فرمول (SUM(HOUR(A1) ; MINUTE(A1)/60 ; SECOND(A1)/3600= زمان موجود در خانه A1 را به عدد تبدیل میکنیم که همان ساعت کاری هست که به صورت عدد میباشدوشرح آن به اینصورت هست که ابتدا با توابع ()HOUR() , MINUTE() , SECOND  به ترتیب ثانیه ،دقیقه وساعت را از زمان اعلام شده بیرون میکشیم وسپس با تقسیم ثانیه به عدد 3600 و تقسیم دقیقه بر 60 ساعت را بدست میاوریم ودر آخر همه را با تابع Sum جمع کرده وبه عدد مورد نظرمان میرسیم.وبرای بدست آوردت مبلغ این فرمول را با یک ضرب ساده در خانه A2 بدست میاوریم.

توجه:حتماً توجه داشته باشید که فرمت ستونی که شامل فرمول هست باید  Number ویا General باشد.

4- این روش نیز روش ساده ای هست که در مواردی کاربرد دارد که با بیشتر از 24 ساعت سروکار داشته باشیم،مثلاً اگر جمع ساعت کاری یک نفر  از 24 ساعت بیشتر باشد یعنی  24:10 شده باشد با روشهای قبلی به غیر از روش اول که جنبه نمایشی دارد یعنی روشهای دوم وسوم درست جواب نمیدهد.پس اگر با بیشتر از 24 ساعت سروکار دارید این روش جوابگوی شما خواهد بود.که فرمول آن اگر خانه A1 زمان و خانه A2  مبلغ ساعت کاری باشد A1*24*A2 خواهد بود.

توجه: حتماً توجه داشته باشید که فرمت ستونی که شامل فرمول هست باید  Number ویا General باشد.

 

 

 

دانلود نمونه فرم در اکسل

قبل از باز کردن فایل حتماًماکرو را به طریق زیر Enable کنید

Office Botton / Excel Options / Trust Center / Trust Center Settings / Macro Settings / Enable All Macros(…) 

 

Form With Vba

ِDownload

برای دیدن توضیحات روی لینک زیر کلیک کنید

http://yazdexcel.blogfa.com/post/9

 

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

راه اول : روی خانه ای که فرمول در آن نوشته اید کلیک کرده وبا نگاه کردن در نوار فرمول فرمولها را کنترل کنید.

راه دوم : با دابل کلیک روی خانه ای که شامل فرمول میباشد ویا کلیک در نوار فرمول کلیه خانه هایی که در این فرمول درگیر هستند با کادرهای رنگی مشخص میشوند که میتوانید فرمولهای خود رو کنترل کنید.

راه سوم : به زبانه Formula و بلوک Formula Auditing  رفته وبا کلیک روی گزینه Show Formulas کلیه فرمولهای داخل شیت نمایان میشودکه با کلیک روی هر خانه ای که شامل فرمول هست سلولهای که در این فرمول درگیر هستند مثل راه دوم با کادرهای رنگی مشخص خواهند شدکه شما به این طریق نیز میتوانید فرمولهای خود رو کنترل کنید.

راه چهارم : سلول شامل فرمول رو انتخاب کرده و  به زبانه Formula و بلوک Formula Auditing  رفته وروی گزینه Trace Precedents کلیک میکنیم ، که با این کار کلیه سابقه های این فرمول با خطهای آبی رنگ مشخص خواهد شد ،برای حذف این خطها روی سومین گزینه یعنی Remove Arrows  کلیک کنید.

راه پنجم : این راه یکی از بهترین راههای کنترل فرمول میباشد ، که در این راه سلول شامل فرمول رو انتخاب کرده و  به زبانه Formula و بلوک Formula Auditing  رفته وروی گزینه Evaluate Formula  کلیک کنید ،سپس در پنجره باز شده مراحل اجرای فرمول را با کلیکهای متوالی رو دکمه Evaluate مشاهده خواهید کرد،مثلاً اگر فرمول شما خطا داد میتوانید از این طریق بفهمید که اشکال از کجاست.

راه ششم  : دراین راه شما میتوانید خطاها یا  Errorها را چک کنید .این روش ، راههای دوم ،چهارم وپنجم را نیز شامل میشودبا این تفاوت که فقط برای کنترل Errorها میباشد .روش کار به اینصورت هست که مانند دو راه قبلی به زبانه Formula و بلوک Formula Auditing  رفته وروی فلش کوچک کنار گزینه Error Checking  کلیک کنید،حال از موی باز شده  اگر گزینه دوم را انتخاب کنید همان کار ی را انجام میدهد که در راه چهارم توضیح داده شدو اگر گزینه اول را انتخاب کنیم با پنجره ای مواجه میشوید که میتوانید تمام Errorهای داخل شیت رو پیمایش کنید.این پنجره شامل چندین دکمه هست که دکمه اول Help  اکسل را در مورد آن خطا باز میکند ودکمه دوم همان پنجره ای را باز میکند که در روش پنجم توضیح داده شدودکمه سوم خطا را نادیده گرفته و مثلث سبز رنگ گوشه سمت راست وبالای سلول را که نشانگرError  بود را ناپدید میکندودکمه چهارم  هم همان روش دوم بود که توضیح داده شد ودکمه های Next  و Previous  برای پیمایش خطاها ورفتن به خطای بعدی و قبلی میباشد.

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

 

کنترل ورود داده ها ،جلوگیری از ورود داده های غیر مجاز وکشف داده های غیر مجاز وارد شده

برای اینکه داد ه ها رو کنترل کنید راههای زیادی وجود دارد که دو تا از متداولترین راههای آن را توضیح میدهم یکی ازراهها استفاده از ابزار Conditional Formatting میباشد که با رنگی کردن داده ها میتوانید اشتباهها را پیدا کنید ، روش کار به این صورت هست که ابتدا ستون مورد نظر رو انتخاب کنید وسپس از زبانه Home وبلوک Styles روی ابزار Conditional Formatting کلیک کرده وروی اولین گزینه از منوی باز شده یعنی HighLight Cells Rules  رفته وسپس یکی از ابزارهای منوی باز شده با توجه به انتظاری که از آنها دارید را انتخاب کنید که در جدول زیر توضیح مختصری در مورد ابزارها داده شده است .

Greater Than	رنگی کردن اعداد بزرگتر از عدد مورد نظر
Less Than	رنگی کردن اعداد کوچکتر از عدد مورد نظر
Between	رنگی کردن اعداد بین ومساوی دو عدد مورد نظر
Eaual	رنگی کردن متن ،عدد و... مساوی با متن ،عدد و... مورد نظر
Text that Contains	رنگی کردن متون شامل حرف ،کلمه ویا جمله مورد نظر
A Date Occurring	رنگی کردن تاریخهای شامل تاریخ مورد نظر
Duplicate Values	رنگی کردن تکراریها

 فرض کنید یک ستون از اعداد دارید که در این ستون مجاز به وارد کردن اعداد بین 50 و 200 نبودید وحالا میخواهید کنترل کنید که عددی بین این دو عدد در این ستون وارد نشده باشد که برای این کار طبق بیان قبلی ابتدا ستون مورد نظر را انتخاب کرده وسپس به مسیر گفته شده یعنی

  Between    HighLight Cells Rules/  /  Conditional Formatting  /Styles /  Home  رفته ودر کادرهای خالی پنجره باز شده به ترتیب اعداد 50 و200 را وارد کرده ودر کادر آخر فرمت بندی دلخواه رو انتخاب کنید وسپس روی دکمه ok  کلیک کنید، بعد از این مراحل مشاهده خواهید کرد که  در ستون مورد نظر اعداد بین ومساوی اعداد مورد نظر رنگی شده اند که با یک فیلتر رنگی میتوانید اعداد رنگی شده را فیلتر کرده ودر کنار هم مشاهده کنید.که در شکل زیر مشاهده خواهید کرد.

وحالاراه دوم که جالبتر میباشد اینست که ابتدا محدوده اعدادتان را انتخاب کرده وسپس به مسیر Data/Data Validation/settings /Allow رفته واز منوی کشویی ظاهر شده اول گزینه Whole Number  را انتخاب کرده واز منوی کشویی دوم به طور مثال  not Between  را انتخاب کنید وسپس در کادرهای خالی  اول ودوم به ترتیب برای Minimum عدد 50 وبرای  Maximum  عدد 200 را تایپ کنید وسپس برای گذاشتن پیغام جهت هشدار به  وارد کننده اطلاعات به زبانه  Error Alert  در همین پنجره رفته و تیک Show error after invalid data is enterd را زده ودر کادر خالی اول عنوان اخطار ودر کادر بزرگ خالی دوم توضیحات اخطار را تایپ کنید وسپس ok کنید ،حالا شما دیگر نمیتوانید در محدوده انتخاب شده عددی بین  دو عدد 50 و200 را وارد کنید وبا پیغام هشدار مواجه خواهید شد،که اینکار از وارد کردن اعداد غیر مجاز جلوگیری میکند

 

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

برای پاک کردن خطهای قرمز رنگ روی منوی Data Validation کلیک کرده وگزینه سوم یعنی Clear Validation Circles را انتخاب کنید.