رنگی کردن سلولهای تکراری

روش اول: 

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

Conditional Formatting / Highlight Cells Rules / Dublicate Values

بعد از انتخاب گزینه آخر پنجره ای باز می شود که شامل دو کادر کشویی است کادر اول شامل دو گزینه Dublicate تکراری ها ویا Unique یکی ها می باشد که Dublicate  را انتخاب میکنید ودر کادر روبرو نوع فرمت رنگی را میتوانید به دلخواه انتخاب کنیدوسپس در آخر ok کنید. 

روش دوم:

این روش جالبتر وحرفه ای تر از روش اول هست وروش کار به این صورت هست که ابتدا محدوده A2:A25 را انتخاب میکنیم سپس از مسیر زیر گزینه آخر را انتخاب میکنیم.

Conditional Formatting / Highlight Cells Rules / More Rules

سپس از پنجره ظاهر شده گزینه آخر یعنی Use a formula to determine which cells to format  را انتخاب کرده ودر کادر خالی ظاهر شده فرمول زیر را تایپ کرده و Ok کنید.

  =AND(COUNTIF($A$2:$A$25;$A2)>1;$B$1;$A2<>"")

با تمام این کارها هنوز سلولهای تکراری رنگی نمیشود زیرا به خانه B1 نیز وابسته هست که اگر در خانه B1 کلمه True تایپ شود مشکل حل میشود اما ما برای اینکه کارمان جالب شود یک Check Box به بالای ستونی که انتخاب کردیم اضافه کرده وسپس روی آن راست کلیک کرده وگزینه Format Control را انتخاب کرده ودر آخر از زبانه Control ودر کادر خالی Cell link آدرس خانه B1 را انتخاب کرده Ok میکنیم.حالا با تیک دار کردن Check Box سلولهای تکراری رنگی شده وبا برداشتن تیک از حالت رنگس خارج میشود. 

 لینک دانلود فایل : http://yazdexcel.blogfa.com/post/25

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

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

همانطور که می دانیدبرای ساخت لیست به مسیر  Data/Data Validation/Allow/List/Source رفته و با فرض اینکه منبع ما ستون A باشد در کادر خالی آخرپنجره باز شده فرمول زیر را تایپ وok  میکنیم.  (OFFSET(A1,0,0,COUNTA(A:A),1)=

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

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

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

حال برای ساخت لیست اول ،دراین شیت یا شیت دیگر سلول A2 را  انتخاب کرده وسپس ازتب data روی گزینه Data Validation  کلیک میکنیم.بعد در پنجره باز شده واز زبانه  setting وازمنوی کشویی Allow گزینه List   را انتخاب میکنیم بعد از آن در کادر خالی قسمت Source  کلید F3  را فشار داده تا لیست اسامی نام گذاری شده ظاهر شوداز میان این اسامی نام واحد را انتخاب کرده وسپس روی ok  کلیک میکنیم تا نام واحد در کادر خالی جایگذاری شود و در آخر روی ok  آخر کلیک میکنیم تا پنجره بسته شودبا این کار لیست ما که شامل نام واحدهای شرکت می باشد ساخته می شود،پس از ساخت لیست اول برای ساخت لیست دوم سلول B2 را انتخاب کرده وبه مسیر قبلی رفته ودر کادر خالی فرمول   =INDIRECT(A2)   راتایپ کرده و OK میکنیم.

جمع کردن ساعتهایی که جمعشان بیشتر از 24 ساعت می شود.

همانطور که می دانید وقتی زمان یعنی ساعتها را در اکسل جمع می زنیم ،تا زمانی که جمع آنها کمتر ۲۴ ساعت شود مشکلی یش نمیاید ولی زمانی که جمع ساعتها بیشتر از ۲۴ ساعت می شود این نرم افزار هر ۲۴ ساعت را یک روز حساب می کند و بقیه را به ساعت نشان می دهدکه این چیز جالبی نیست وما برای اینکه ساعتهای کاری یا غیره را با هم جمع بزنیم با مشکل مواجه می شویم .پس برای حل این مشکل ابتدا ناحیه مورد نظر را انتخاب کرده سپس با راست کلیک وانتخاب Format Cells این  پنجره را باز میکنیم بعد به تب Number رفته وروی Custom کلیک میکنیم ودر آخر کد فرمت زیر را از میان کدها انتخاب کرده وOk کنید.کد فرمت:                                                                     

[h]:mm:ss     : کد فرمت

پنهان کردن صفر،عددو یا کلیه داده های واردشده درسلولهای اکسل

 

 

۱-مخفی کردن صفر(1):

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

Office Bottom/Excel Options/Advanced/Display options for this worksheet:/Show a zero in cells that have zero value

۲-مخفی کردن صفر(2):

با این روش می توانیدصفرها را در سلولهای انتخابی پنهان کنید.برای انجام این کار سلولهای مورد نظرتان را انتخاب کنیدوسپس پنجره Format Cells را با راست کلیک روی ناحیه انتخابی باز کنیدوسپس به تب Number رفته وCustom را انتخاب میکنیم ودر آخر کد فرمت زیر را در کادر خالی وارد کرده و Ok کنید. 

 کد فرمت:  @;;0-;0

 

3-مخفی کردن کلیه داده های انتخابی (1):

پنجره Format Cells را با راست کلیک روی ناحیه انتخابی باز کنیدوسپس به تب Number رفته وCustom را انتخاب میکنیم ودر آخر کد فرمت زیر را در کادر خالی بالا وارد کرده و Ok کنید. 

 

;;;

این سه سمیکلن  باعث میشود اطلاعات واردشده در سلولها پنهان گرددو فقط در صورت انتخاب سلول داده های داخل آن در نوار فرمول نمایش داده میشود

 

4-مخفی کردن کلیه داده های انتخابی(2):

به وسیله همرنگ کردن داده های وارده با رنگ پس زمینه که معمولاً سفید هست.

 

5-مخفی کردن عدد :

کد فرمت  ;-;  (سمیکلن خط تیره سمیکلن ) هم برای پنهان کردن فقط اعداد کاربرد دارد

 

6-مخفی کردن object ها (chart;shape;smartart;picture;clipart) :

پس از انتخاب شیء مورد نظر به زبانه Pagelayout بلوک arrange رفته وگزینه Selection pane را انتخاب میکنیم ،در پنجره ای که بازمیشود میتونیم کلیه اشیائی را که وارد اکسل کرده ایم را مشاهده کنیم وبا کلیک رو چشمی کنار اشیاء میتونیم آنها را پنهان یا آشکار کنیم ویا با کلیک روی دکمه Hide all در قسمت پایین همین پنجره  میتوانیم کلیه اشیاء وارده رو پنهان کنیم.

 

فارسی کردن اعداد در نمودار

برای فارسی کردن اعداد در نمودار دو راه وجود دارد که در راه اول ابتدا اعداد مورد نظرمان را انتخاب میکنیم وبا استفاده از گزینه Right-to-Left در زبانه Home و بلوک Alighnment اعدادمان را فارسی میکنیم که در شکل بالا کاملاً مشخص است  گزینه بالادر مورد محور عمودی عمل نمیکندکه راه دوم را پیشنهاد میکنم ، در راه دوم ابتدا روی اعداد مورد نظرمان(محورها یا سریها) راست کلیک کرده وسپس فرمت مربوط به آن اعداد را انتخاب میکنیم وسپس در پنجره باز شده منوی نامبر را انتخاب کرده وروی گزینه کازتوم کلیک میکنیم وبعد در کادر خالی کد فرمت زیر را وارد کرده ودکمه Add را کلیک می کنیم.این ترفند در مورد اکسل 2007 جواب میده ،من ورژنهای دیگه رو امتحان نکردم.

کد فرمت:     اعدادصحیح      0[3010000-$]    

کد فرمت:  اعداداعشاری      0.0[3010000-$]

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

 

منبع یادتون نره