خانه اکسل

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

خانه اکسل

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

خانه اکسل

* آموزش و تبادل اطلاعات در زمینه نرم افزار پرقدرت اکسل
* انجام تمام پروژه های تحت اکسل
* طراحی انواع سیستم هوشمند تحت اکسل
* ارائه کننده محصولات هوشمند تحت اکسل
* ایمیل : excelp4@gmail.com
* وبسایت جدید ما : https://excelit.ir/

تبلیغات

آخرین نظرات

  • ۱۹ بهمن ۰۲، ۱۸:۰۳ - مهرداد
    1

رنگی کردن یکی در میان سطرها یا ستون ها

سه شنبه, ۲۹ ارديبهشت ۱۳۹۴، ۰۳:۳۰ ب.ظ

دوستان خوبم سلام

گاهی اوقات خواندن اطلاعات در یک پایگاه اطلاعاتی وسیع خسته کننده و دشوار می شود برای حل این مسئله شما شروع به تغییراتی در پس زمینه صفحه خود می کنید اما این سوال پیش میاید که چطور می شود یک تغییر ثابت و سریع و موثر را به پایگاه اطلاعاتی خود اعمال کنیم ؟!!
برای این کار به سراغ   Conditional Formatting می رویم !!!

* ابتدا محدوده مورد نظر خود را انتخاب می کنیم و به مسیر زیر می رویم  home > styles > Conditional Formatting و گزینه new rules را انتخاب می کنیم .

*از صفحه مقابل به قسمت ......use a formula to می رویم و در کادر نمایش داده شده فرمول  mod(row();2)=0= را وارد می کنیم و از قسمت format رنگ مورد نظر را انتخاب می کنیم و ok را میزنیم مانند تصویر زیر :


mod


همانطور که میبینید سطرها به صورت یکی در میان به رنگ format تغییر زمینه داده اند برای اینکه این تغییر برای ستون ها اتفاق بیافتد مانند توضیحات پیش رفته و تنها از این فرمول استفاده کنید   :  mod(COLUMN();2)=0=


دریافت فایل حاوی فرمول
عنوان: رنگی شدن یک در میان سطر یا ستون
حجم: 9.25 کیلوبایت


شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.

نظرات  (۶۱)

سلام وخسته نباشی
میخواستم بگم یه جدول دارم حدود500ردیف میخوام درسطری که حرکت میکنم اون سطر رنگی بشه مثل سیستم مالی  که برای کنترل اسناد اون سطر نسبت به سطرهای دیگر متفاوت است تشکر
پاسخ:
با سلام دوست عزیز
این کار با استفاده از کد Vba انجام میشه و بمحض انتخاب یک سلول سطر و ستون مربوط به اون رنگی میشه.
اما دستور العمل :
1. ابتدا از تب developer وارد قسمت visual basic بشوید.(اگر تب developer را ندارید می بایست به مسیر file>option>customiza ribbon بروید و تیک آن را بزنید)
2.پس از وارد شدن به محیط vba بر روی نام شیت مورد نظر از قسمت سمت چپ دوبار کلیک کنید تا یک صفحه خالی مربوط به شیت باز شود.
3.در صفحه سفید نمایش داده شده کد زیر را وارد کنید :
4.سپس صغحه را save واز محیط vba خارج شوید. حال با انتخاب هر سلول سطر و ستون مربوط به آن نمایش داده می شود. پس از اقدمات انجام شده از قسمت save as فایل را به صورت excel macro ذخیره کنید.
کد:
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20140318
Static xRow
Static xColumn
If xColumn <> "" Then
With Columns(xColumn).Interior
.ColorIndex = xlNone
End With
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
With Rows(pRow).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
با عرض سلام و خسته نباشد 
من یک جدولی دارم که در یک ستون آن تاریخ به میلادی تایپ شده از اونجایی که تقریبا 4000 تا تاریخ میباشد و اصلاح یک یک آنها زمان بر است آیا فرمولی هست که تاریخ های تایپ شده را به شمسی تبدیل کند؟
خیلی خیلی سایت خوبی دارید . به امید موفقیت های بیشتر
پاسخ:
سلام خدمت شما همراه عزیز
فرمولی که به صورت مستقیم اینکار را انجام بدهد در اکسل موجود نیست اما با استفاده از ترفندهایی از جمله ترکیب فرمول ها و یا vba اینکار را انجام می دهند.
در زیر یک نمونه تبدیل تاریخ با ترکیب فرمول آورده شده که تاریخ روز را به شمسی تبدیل میکند.
برای تبدیل تاریخ میلادی موردنظر به تاریخ شمسی کافیست فرمول را در ستون مقابل ستون تاریخ میلادی کپی کنید سپس به جای ()TODAY آدرس سلول تاریخ میلادی را وارد کنید .

کد:
=CONCATENATE(INT((TODAY()-7385)/365.25)+1299;"/";MOD(IF(INT(MOD((TODAY()-7385)*100;36525)/100)<186;INT(INT(MOD((TODAY()-7385)*100;36525)/100)/31);IF(MOD(
INT((TODAY()-7385)/365.25);4)=0;INT((INT(MOD((TODAY()-7385)*100;36525)/100)-186)/30)+6;IF(INT(MOD((TODAY()-7385)*100;36525)/100)<336;INT((INT(MOD((TODAY()-7385)*100;36525)/100)-186)/30)+6;INT((INT(MOD((TODAY()-7385)*100;36525)/100)-336)/29)+11)));12)+1;"/";IF(INT(MOD((TODAY()-7385)*100;36525)/100)<186;MOD(INT(MOD((TODAY()-7385)*100;36525)/100);31)+1;IF(MOD(INT((TODAY()-7385)/365.25);4)=0;MOD(INT(MOD((TODAY()-7385)*100;36525)/100)-186;30)+1;IF(INT(MOD((TODAY()-7385)*100;36525)/100)<336;MOD(INT(MOD((TODAY()-7385)*100;36525)/100)-186;30)+1;MOD(INT(MOD((TODAY()-7385)*100;36525)/100)-336;29)+1))))
سلام من این کاری که گفتید برای تغییر رنگ یکی در میان انجام دادم ولی هیچ تغییر نکرد جدولم !!
جدولم خام وهنوز اطلاعاتش وارد نکردم ممکن علتش این باشه؟
مرسی
پاسخ:
با سلام به احتمال زیاد شما محدوده رو انتخاب نکردید.
قبل از انجام دادن عملیات محدوده رو مثلا از سطر یک تا 5 رو با ماوس درگ کنید تا انتخاب بشه سپس عملیات گفته شده رو انجام بدید.
سلام ممنون از سایت خوبتون

من یک جدول دارم که تشکیل شده از تاریخ های شمسی به ازای هر پیمانکار و میخوام تاریخ  هر سلول در صورتی بعد از یک تاریخ مشخص شمسی باشد رنگی شود (که هر سلول با سلول های دیگر تاریخش(شرطش )متفاوت است یعنی هر پیمانکار شرط متفاوتی دارد ممنون میشم یه راهکار بهم معرفی کنید.
پاسخ:
با سلام
به صورت زیر عمل کنید :
 * سه ستون به شکل : ستون a شامل پیمانکاران ، ستون b تاریخ ها و ستون c شرط ها را ایجاد کنید که برای هر ردیف اطلاعات این سه ستون موجود است.
*ستون تاریخ ها را انتخاب کنید و به conditional formating بروید new rule را انتخاب نمایید سپس به مورد آخر یعنی use a formula.... بروید و در کادر فرمول زیر را وارد کنید :
* if(b1>c1;1;2)=1
*در نهایت یک رنگ از format انتخاب کنید و ok کنید.
با آرزوی موفقیت
من کاری که فرمودید رو انجام دادم ولی تغییری اعمال نشد

اول اینکه باتوجه به اینکه فرمت سلول هارو تاریخ انتخاب کردم و تایپ کردم 1394/06/12 حدس میزنم  چون فرمت سلول ها تاریخ هست امکان تشخیص بزرگتر یا کوچکتر براساس فرمت 1394/06/12 وجود ندارد آیا حدسم درست هست؟و  اینکه چکار باید انجام دهم ؟
و دوم اینکه :1:2 برای چیست؟

ممنون از لطف و همکاری شما
پاسخ:
شما میتوانید از دو روش استفاده کنید منتهی این نکته مد نظر باشد که تاریخ ها دقیقا باید با یک فرمت و به یک شکل وارد شوند.
* روش اول : در بعضی از سیستم ها با تغییر فرمت سلول به general این مسئله حل خواهد شد.
* روش دوم : (استاندارد) در این روش شما تمام تاریخ ها را بدون "/" یعنی به شکل : 13940612 وارد خواهید کرد و در نهایت تمام ستون هایی که در آن تاریخ موجود هست را انتخاب و کلیک راست کرده و به format cells می روید و از custom فرمت جدید ##"/"##"/"#### ایجاد و اعمال می کنید.

ضمنا اگر شما تمام تاریخ ها را به شکل 1394/06/12 وارد کرده اید ستون تاریخ ها را انتخاب کنید و  ctrl+f را بزنید به تب replace بروید در کادر اول "/" را تایپ کنید و در کادر دوم چیزی تایپ نکنید سپس replace all را کلیک کنید.
پایدار باشید

واقعا از از اینکه پاسخگو هستید ممنونم از شما

فقط من نمی دونم اشکالم در کجاست ین فرمول هیچ تغییری در سلول ها ایجاد نکرد if(b2>c2:1:2)=1 با اینکه چندین بار امتحان کردم و ستون تاریخ هارو انتخاب کردم
بنظرتون ممکن هست اشکال از چه چیزی باشد ؟ و اون قسمت 1:2 برای چیست؟

و دوم اینکه وقتی از Format cell استفاده می کنیم و فرمت ##"/"##"/"#### را تایپ می نماییم امکان دوباره Find and Replace برای برداشتن / ها نیست برای اینکه دوباره بخواهیم / ها را برداریم باید در این حالت چکار کنیم؟

بازهم متشکرم از لطف شما

پاسخ:
با سلام فایل درخواستی برای شما تهیه شد بر روی "دریافت فایل" کلیک و آن را ذخیره نمایید. :

دریافت فایل

*نکات مهم : 1-محدوده کل ستون انتخاب شود 2- B2 را به B1 و C2 را به C1 تغییر دهید 3-  از فرمت موجود در پنجره conditional formating یک رنگ به دلخواه انتخاب شود 4-تاریخ دقیقا شبیه بهم باشد 5- قسمت "1:2" یک قرارداد در فرمول if است به این معنی که اگر تاریخ در ستون b بزرگ تر از تاریخ در ستون c بود عدد 1 و اگر کوچک تر بود 2 تایپ شود و در نهایت قسمت "1=" به این معنی است که اگر عدد 1 تایپ شد یعنی برای هر ردیفی که در آن شرط "1=1" بود ، سلول مربوط به آن در محدوده رنگی شود. 6- استفاده از ترفند فرمت سل برای تاریخ به این معنی است که تاریخ در ظاهر تنها با "/" نمایش داده می شود در حالی که در اصل بدون "/" است و اگر بروی سلول دابل کلیک شود مقدار بدون "/" است پس چون ظاهری می باشد دیگر در find قابلیت جستجو ندارد و نیازی هم به برداشتن "/" دیگر وجود ندارد چون مقدار سلول عددی است.

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

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


با سلام و خسته نباشید و ممنون از سایت پربارتون

می خواستم بپرسم برای گذاشتن کلید میانبر برای پیدا کردن داده ها در سلول ها چکار باید کرد مثلا لیستی دارم که می خواهم بازدن حرف الف به روی سلول شروع شده با حرف الف رود ممکن هست ؟
پاسخ:
با عرض سلام
ساده ترین روش ممکن استفاده از ctrl+f و جستجوی حرف موردنظر می باشد.

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

من یک سری تاریخ دارم که می خواهم این هارو به پنج یا 6 ماه بعد ببرم 

با این فرمت مثلا 940512 که فرمتشون General و بعد از رفتن به Format cell و قرار دادن ##"/"##"/"## فرمتشان Custom است آیا فرمولی هست که بتوان این کار را انجام داد؟

تشکر از شما
پاسخ:
با سلام
ساده ترین راه استفاده از find&replace می باشد.
ستون تاریخ را انتخاب کنید. ctrl+f را زده به تب replace بروید در کادر اول مثلا : 9405  و در کادر دوم 9410 را وارد کنید سپس replace کنید.


ممنون از شما

اما این تاریخ ها همگی متفاوت هستند به عنوان مثال یکی را مثال زدم 146 سطر تاریخ وجود دارد با تاریخ های متفاوت 
پاسخ:
بافرض اینکه سه ستون به ترتیب (تاریخ ؛ تعداد ماه ؛ تاریخ جدید ) در C , B , A ایجاد کرده ایم شما می توانید در سلول C2 یعنی دومین سلول ستون "تاریخ جدید" این فرمول را قرار دهید :
* این فرمول تا 18 ماه جمع را پوشش می دهد .

کد:
=IF(LEFT(A2;4)+B2>9412;LEFT(A2;2)+1&IF((LEFT(A2;4)+B2)-(LEFT(A2;2)&12)<10;0&(LEFT(A2;4)+B2)-(LEFT(A2;2)&12);(LEFT(A2;4)+B2)-(LEFT(A2;2)&12))&RIGHT(A2;2);(LEFT(A2;4)+B2)&RIGHT(A2;2))
سلام 

من سعی کردم این کاار رو انجام بدم اما چون منطق این فرمول رو متوجه نشدم برای همین عینا این فرمول رو کپی کردم اما Erorr داد به نظرم بعضی پرانتزها جابجا شده آیا ممکن هست؟

من سعی کردم این کاار رو انجام بدم اما چون منطق این فرمول رو متوجه نشدم برای همین عینا این فرمول رو کپی کردم اما Erorr داد به نظرم بعضی پرانتزها جابجا شده آیا ممکن هست؟ممنون میشوم راهنمایی کنید.

برگرفته شده از excelhouse.blog.ir
پاسخ:
با سلام. مشکلی در فرمول نیست احتمالا کامل کپی نکرده اید. فایل زیر را دانلود نمایید.

دریافت فایل


تعداد داده های خیلی زیادی دارم که برای رسم نمودار به داده های یکی در میان ستون ها نیاز دارم.داده ها رو چطور انتخاب کنم ک وقتم گرفته نشه.تعدادش خیلی زیاده
اگه اکسل نمیتونه اینکارو انجام بده نرم افزار دیگه ای نیست ک پیشنهاد بدین ب من.
سلام.ببخشید چند بار پیام دادم.من دارم با برنامه sap2000 کار میکنم و داده هایی که نیاز دارم رو به برنامه اکسل برای رسم نمودار export میکنم.ولی همه این داده ها رو نیاز ندام.من به داده های ستون ها به صورت یکی در میان نیاز دارم و البته تعدادشون هم زیاد هست حدود 12 تا فایل (1400 ردیف و2 ستون) الان چه پیشنهادی به من میدین .خیلی ب کمکتون نیاز دارم.مرسی
پاسخ:
سلام 
روزهای شنبه تا چهارشنبه در زمان های کاری معمولا پاسخ گویی با تاخیر کمتری انجام میشه.
فایل شما قطعا نیاز به بررسی داره اما راهی که انجامش ساده و عملی هست اینه که شما در یک ستون شماره ردیف ها رو یک در میان  وارد کنید که با فرمول اینکار شدنیه و در ستون بعدی با استفاده از index و یا hlookup مقادیر رو فراخوانی کنید و در نهایت نمودار موردنظر رو ترسیم کنید.

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

 دانلود فایل فراخوانی مقادیر یک ستون به صورت یک در میان
سلام.صبح بخیر .ببخشید اگه براتون زحمتی نیست میشه برای جمع یکی در میان ستون ها باید چه فرمولی استفاده کنم.مرسی.به جوابتون خیلی نیاز دارم
سلام و خسته نباشید.
بنده فایلی دارم با اطلاعات زیاد که بعضی سطرها و ستونها به صورت merge میباشد و میخاهم یکی در میان رنگ آمیزی شود ولی با فرمولی که فرمودید قسمتهای merge رنگ نمیشود. لطفا راهنمایی فرمائید.
با تشکر
پاسخ:
با سلام
شما وقتی از merge استفاده می کنید شماره ستون ها یا سطر ها دیگر پشت سر هم نیست و نمی شود از شماره ستون ها برای رنگی کردن یکی در میان استفاده کرد.
*اگر امکان دارد در دیتابیس خود از merge استفاده نکنید چون نه تنها در این مورد بلکه در بسیار موارد دیگر مشکل ساز است و استاندارد نیست.
* اگر محتوی سلول ها به صورت یک در میان است (مثلا اولی a و دومی b و به همین شکل تا انتها) شما می توانید از محتوی برای رنگی کردن یکی در میان استفاده کنید.
موفق باشید

با سلام و ممنون از زحمتتون
در مورد تابع VBA که برای رنگی کردن سطر ها فرمودید اگه امکانش هست آیا امکانش هست فقط رنگ سطر رو تغییر داد و رنگ ستون بدون تغییر بمونه؟ممنون اگه راهنمایی کنید
پاسخ:
با سلام از کد زیر استفاده کنید :

کد:
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
Static xColumn
If xColumn <> "" Then
With Columns(xColumn).Interior
.ColorIndex = xlNone
End With
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
With Rows(pRow).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
۰۸ ارديبهشت ۹۵ ، ۱۶:۱۷ احسان جهانبخش
سلام ، بابت سایت خوبی که دارید بهتون تبریک میگم و امیدوارم که همیشه موفق و پیروز باشید.
دوست عزیز دوتا سوال از خدمتتون داشتم
1. من یه فرم توی اکسل ایجاد کردم که یه سری اطلاعات مشتریانم رو داخل اون ثبت می کنم.
یکی از ستون های فرمم رو اختصاص دادم به آدرس ایمیل مشتریان ، 
آیا این امکان وجود داره که وقتی اطلاعات رو دارم به فارسی تایپ می کنم وقتی به ستون ایمیل رسیدم به صورت خودکار زبان نوشتاری به انگلیسی تبدیل بشه و بعد از گذشتن از این ستون دوباره به فارسی تایپ بشه؟
2. سوال دوم اینکه آیا امکان داره ورود اطلاعات در یک یا چند ستون رو اجباری کنم ؟ مثلا میخوام به شکلی باشه که تا شماره موبایل و تلفن تماس رو وارد نکردم نتونم به ستون بعدی برم.
ممنونم میشم اگر منو راهنمایی کنید.
منتظر جوابتون هستم.

پاسخ:
با سلام ممنون از شما
پاسخ سوال های شما در لینک زیر قرار گرفته می توانید آن را دانلود بفرمایید.
ضمنا در این فایل از کدهای vba استفاده شده بنابراین ماکرو های شما باید فعال باشد.

فایل تغییر اتوماتیک زبان کیبورد و محدود کردن ورود اطلاعات در ستون ها


۰۸ ارديبهشت ۹۵ ، ۱۹:۵۱ احسان جهانبخش
سلام مجدد
دوست گرامی ممنونم از لطفتون ، ولی متاسفانه من نتونستم کدهای مربوط به سوال تغییر اتومات زیابن کیبورد رو پیدا کنم.
اگر براتوم ممکنه با زبان ساده تر مسیر تنظیمات و کدهای اجرایی را توضیح بدید.
باز هم از لطف شما ممنونم.
پاسخ:
با سلام
در فایل ارسال شده پست قبلی کدهای vba به کار برده شده و استفاده از آن ها نیاز به حداقل دانش vba دارد که شما می توانید از قسمت vba وبلاگ و یا سایت های دیگر آموزش های ابتدایی رو مطالعه کنید.


۰۸ ارديبهشت ۹۵ ، ۲۰:۵۴ احسان جهانبخش
دوست عزیز آیا این امکان وجود داره که وقتی اطلاعات یک سطر رو ثبت کردم و به سطر پایین میام در اولین سلول تاریخ شمسی به صورت اتومات ثبت بشه؟
قصد دارم با این کار ، بعدها راحتت بتونم فیلتر رو انجام بدم.
پاسخ:
با سلام
بله اینکار امکان پذیر است منتهی به دلیل اینکه در اکسل تاریخ شمسی تعریف نشده شما می بایست از ستون ها یا توابع کمکی برای اینکار استفاده کنید.

۰۹ ارديبهشت ۹۵ ، ۲۰:۰۲ احسان جهانبخش
سلام و عرض خسته نباشید
دوست عزیز من یه فایل با موضوع (احسان جهانبخش) به ایمیل شما ارسال کردم و همونجا توضیحات لازم رو دادم خومتتون ، ممنون میشم اگر لطف کنید فایل رو بررسی و در صورت امکان من رو کمک کنید.
قبلا از حسن توجه و همکاری شما دوست گرامی ممنونمو کمال تشکر را دارم.
پاسخ:
با سلام
 فایل شما بررسی و پاسخ به ایمیل شما ارسال شد.

با سلام
فرمولی که گذاشتین واسه رنگی کردن یکی در میان رنگی کردن سلول ها وقتی وارد می کنی قبول نمی کنه
پاسخ:
با سلام
فرمول را در چه قسمتی وارد می کنید ؟ پیغام خطا چیست ؟

سلام
من یک فایل دارم که میخوام با وارد کردن مثلا ستاره در یک سلول کل اون سطر تا جایی که اطلاعات داخلش نوشتم رنگی بشه چند تا دستور اعمال کردم ولی نشده میشه راهنمایم کنید ؟؟
پاسخ:
با سلام به ترتیب زیر عمل کنید :
* ابتدا سطرهای مورد نظر را انتخاب کنید .
* به conditional formating بروید و از new rule به گزینه آخر یعنی use a formula.... بروید .
* در کادر مانند فرمول زیر را وارد کنید و به جای a2 آدرس سلولی که می خواهید با تغییر آن سطر رنگی شود را وارد کنید و از format یک رنگ را به دلخواه انتخاب نمایید  :
 if($A2="*";1;0)=1

* دقت کنید علامت $ فقط قبل از A باشد و اگر بین A و عدد بود آن را حذف کنید همچنین به هنگام انجام این فرایند حتما می بایست سطرها در حالت انتخاب باشند.
با سلام و خسته نباشید
واقعا از لطفتتون ممنون کمک خیلی زیادی بهم کرد فقط یه نکته میشه محدوده خاص از یک سطر رنگی بشه ؟
و یه سوال دیگه داخل یه سند یک سری افراد اجناس میگیرند ایا میشه به طور اتوماتیک سطر مربوط به هرکدوم از این افراد داخل یک سند دیگه که مربوط به خود این افراد کپی بشه ؟
بسمه تعالی
با سلام و تحیات الهی
ابتدا جا دارد از زحمات شما تقدیر و تشکر کنید و اینکه بدون هیچ چشمداشتی من بی سواد را با دنیای اکسل آشنا می کنید ممنونم.
خواهشمند است درخصوص مشکل زیر راهنمایی فرمائید و چون فوریت دارد اگر دراسرع وقت پاسخ داده شود بسیار بزرگواری کردید .
در یک شیت اکسل جدولی دارم حاوی مشخصات بیماران که این بیماران به دفعات مراجعه دارند لیکن بنده باید جهت اخذ آمار و گزارش مربوطه ، دفعات مراجعات لازم نیست چون بدست میارم ولی تعداد مراجعه کننده و تعداد زن و مرد بودن بر همین اساس مهم است یعنی در حال حاضر 1080 ردیف دارم که بیماران به دفعات مراجعه داشتند اما اینکه چند نفر مراجعه کردند و از این تعداد چند نفر زن و چند نفر مرد بودن لازمه گزارش گیری منه .
لطفاً در صورت امکان جواب رابه ایمیلم ارسال فرمائید
با تشکر

پاسخ:
با سلام به شما دوست گرامی
شما می بایست اطلاعات کامل هر بیمار را وارد کنید یعنی برای تمام 1080 ردیف نام و نام خانوادگی ، جنسیت و سایر مشخصات را وارد نمایید.
سپس اطلاعات را در شیت دیگر کپی کنید و تمام ستون ها را انتخاب کنید و به تب data رفته و گزینه remove duplicates را انتخاب کنید و ستون ها را انتخاب و اوکی کنید حال لیست شما منحصر بفرد شده پس می توانید با استفاده از یک countif ساده تعداد مراجعه کننده به تفکیک جنسیت را مشخص نمایید . البته روش های فرمولی نیز می شود استفاده کرد اما این روش ساده تر است .
توجه کنید که حتما اطلاعات را در شیت دیگر کپی کنید و بعد   remove duplicates را انجام دهید.
موفق باشید.

سلام و ممنون از پاسخ شما بابت گزارش گیری اطلاعات بیماران .
روشی که ذکر کردید خیلی ساده و پیش پا افتاده و وقت گیر است و از طرفی جنسیت فقط یک مورد بود و موارد دیگری هم هست که باید گزارش گیری شود لیکن در حال حاضر این مهمتر بود بنده با فرمول نویسی نسبتاً آشنایی دارم اما دیگه فکرم کار نمی کرد واسه همین می خوام از روش فرمول نویسی استفاده کنم چون این اطلاعات بصورت آنی مورد نیاز می باشد و در حجم زیاد و گزارشات متنوع ، بکارگیری آن مشکل است . واسه بیماران از راه حل هایی که واسه دوستان دیگر گفتید استفاده کردم خدا اموات شما را بیامرزد خیلی عالی بود اگر امکان داره واسه این مورد هم فرمولی در اختیار بنده قرار دهید اگر نیازی هست بفرمائید تا فایل را برایتان ارسال نمایم .
پاسخ:
با سلام
با فرض اینکه ستون اول که شامل اسامی می باشد B1:B5 است و ستونی که جنسیت ها در آن وارد شده C1:C5 است می توانید از فرمول های زیر به صورت آرایه ای استفاده کنید :
M=مرد
F= زن
توجه کنید حتما می بایست تمام سلول های ستون اسامی دارای مقدار باشد و محدوده دقیقا از ابتدا تا انتها بدون هیچ سلول خالی را شامل شود.
کد:
{=SUMPRODUCT(IF(C1:C5="M";1/(COUNTIFS(C1:C5;"M";B1:B5;B1:B5));0))}
{=SUMPRODUCT(IF(C1:C5="F";1/(COUNTIFS(C1:C5;"F";B1:B5;B1:B5));0))}
با سلام و تحیات الهی
ضمن تشکر باید باستحضار برسانم که مشکل همینجاست که چون روزانه ورود اطلاعات صورت می گیرد فرمول آرایه ای خوبه لیکن دائم باید سلول ها را تعریف کنی ببینید اگر فرمول دیگه ای میشه بهش بدیم که نیاز به تعریف کردن دائم فرمول نباشد بزرگواری می کنید وگرنه همین هم راهگشاست
پاسخ:
با سلام
برای حل این مشکل از محدوده پویا استفاده نمایید که آموزش آن در لینک زیر موجود است سپس نام محدوده ها را در فرمول قرار دهید  :

ایجاد محدوده پویا برای نمودارها


سلام

من همه اون مراحلی که توضیح دادین رو رفتم ولی پیغامی مبنی بر اینکه فرمول اشتباه است  را نمایش می دهد.

فرمول را عینا وارد می کنم ولی بازم جوابی نمی گیرم

 

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

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

سلام خسته نباشید

بنده میخواهم در هر ردیف که تایپ میکنم به صورت یک در میان ردیفها رنگی شوند. 
با سپاس
پاسخ:
با سلام با فرض اینکه می خواهید سلول های ستون A به صورت یک در میان رنگی شوند ، ابتدا ستون A را با موس در حالت انتخاب قرار دهید سپس فرمول زیر را به کاندیشنال فرمتینگ بدهید :
=IF($A1<>"";MOD(ROW();2);"")=0
سپاس
سلام خسته نباشید
 انبار خود را به صورت یک فایل اکسلی درآوردم،که در هر شیت یک سالن قرار دارد.
یک محصول را در چندین انبار دارم و میخواهم که در شیت آخر که مجموع کل محصولات است،جمع محصولات را از انبارها برایم در شیت آخر جلویه نام محصول بزند.
ممنون میشم راهنمایی کنید.
پاسخ:
با سلام
بستگی به ساختار قایل شما دارد .
ساده ترین راه جمع چند sumif است اما از تلفیق فرمول ها و یا کد می شود استفاده کرد.

ممنون
سلام خسته نباشید و ممنون که به سوالات پاسخ میدید.
بببخشید من یک جدول داده دارم که بازه اش b3:d22 هست.سر ستون ها شامل name و cod و  Invoice number میباشد.
در بازه ی g3: I4 هم همین سر ستون ها هست. با این تفاوت که قسمت  Invoice number خالی هست و در مرحله ی اول باید
فرمولی بنویسیم که با نوشتن اسم و کد، Invoice number تکمیل شود.
در مرحله دوم با هر روشی که می توانید لیستی برای سلول H4 بسازید که با وارد کردن نام، تنها کدهای مربوط به آن نام نمایش داده شود.
من کد مرحله ی اول رو نوشتم ولی در مرحله دوم حل سوال رو بلد نیستم.ممنون میشم راهنمایی کنید.
کد مرحله اول:( یک دیتا بیس برای جدول به نام ETELAAT در نظر گرفتم
G3:H4؛D3؛DGET(ETELAAT
پاسخ:
با سلام
 شما می بایست از فرمول های جستجوی اکسل مثل : VLOOKUP یا ترکیب INDEX و MATCH و... استفاده کنید که چند نمونه در سایت موجود می باشد.


Vlookup

جستجوی دوشرطی با استفاده از VLOOKUP



با سلام . میخواستم با پر کردن یک سل خاس در یک ستر کل آن ستر رنگی شود . خواهشمندم راهنمایی بفرمایید.
سلام و عرض خسته نباشید.
من یک شیت داده دارم در اکسل, که حدود 4000 سطر داره یکی در میون عدد هست و متن.
 هدف اصلیم این هست که میخوام دوتا دوتا سطر ها رو جابجا کنم به عنوان مثال:
1012929
تست شماره 1
 میخوام تبدیل بشه به :
تست شماره 1
1012929
چیزی که به ذهنم رسیده این هست که اعداد  یکی در میون انتخاب بشه به صورت کلی کات بشه و دوباره در زیر متن مرتبط با اون paste بشه.
سوال اول:
روش انتخابی من درست هست یا راه بهتری وجود داره؟
سوال دوم:
نمیدونم چطوری 4000 سطر رو یکی در میون انتخاب کنم.
روشهای مختلفی رو امتحان کردم..اول فیلتر کردم اعداد رو بعد کلی کات کردم paste کردم ولی  اطلاعات متناظر در نیومده.
 اگه لطف کنین پاسخ رو علاوه بر سایت برام ایمیل کنین بسپاسگذار خواهم بود.

پاسخ:
سلام با فرض اینکه مقادیر شما در ستون A هستند این فرمول را در ستون B1 وارد کنید و به سمت پایین درگ کنید :

=IF(MOD(ROW();2)=0;INDEX(A:A;ROW()-1);INDEX(A:A;ROW()+1))

سلام خسته نباشید و ممنون که به سوالات پاسخ میدید.

 من میخوام وقتی روی یک ردیف کلیک می کنم کل ردیف بزرگتر از بقیه ردیف ها دیده بشه
پاسخ:
سلام روی شیت مورد نظر کلیک راست کنید و گزینه view code را انتخاب کنید. در صفحه ظاهر شده کد زیر را پیست کنید.
ضمنا اعدادی که وارد شده 12 ، 14 و ... دلخواه است و می توانید براساس نیاز تغییر دهید.
در آخر فایل را به صورت ماکروی فعال(xlsm) ذخیره کنید .

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
a = ActiveCell.Row
Rows(a).Font.Bold = True
Rows(a).Font.Size = 12
Rows(a).Font.Size = 14
Rows(a).Font.Size = 16
End Sub
باعرض سلام و خسته نباشید
و تشکر ویزه بابت این فضای داغ اطلاعات
بسیاربسیارعالی
خواستم بگم من با نرم افزار excel خیلی کار کردم
بقول اون جمله معروف که میگه:خداوندا هرچه بیشتر دانستم نادانتر شدمپروردگارا بر نادانانیم بیفزا.
نبازم به فرمول های اکسل ذوزبه روز بیشتر میشه
الان یهمشکلی دارم و اینه که:
یه فایل دارم که دارای 12ستون و سطرهای بسیار دارد.فرض کنید من روزانه مشخصات فیش هایی رو در این لیست وارد میکنم.قصدم اینه که وقتی جلوی هر فیش در دوستون اخر وقتی مشخصات واریز(شخص و تاریخ)ثبت شد.بصورت اتومات پس از انتخاب یک سمبل خاص مثلا باعنوان بروزرسانی فایل کلیه اطلاعات فیش هایی که قبت واریزی دارند از فیش کات و در شیت دیگر باعنوان مثلا واریزی ها پیست شود.
پیشاپیش خیلی خیلی ممنون.خداقوت

پاسخ:
با سلام . برای انجام اینکار فایل شما می بایست بررسی بشه.
با سلام
میخواستم بدونم برای freeze کردن سطری که خودش از merge کردن چند سطر دیگر ایجاد شده چکار بایدکرد؟
وقتی این سطر رو فریز میکنم فقط قسمت بالایی اون( سطر اول موجود در اون سطر merge شده) فریز میشه
لطفأ فرمولی که جهت رنگی کردن یکی در میان سطر و ستون نوشته اید را اصلاح بفرمایید.

MOD(ROW(),2)=0=
ببخشید اشتباه نوشتم راهی میخوام که تاریخ رو به صورت دستی وارد نکنم
پاسخ:
با سلام اینکار هم با فرمول هم با vba انجام میشه که یک نمونه vba  منتشر شد.


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


سلام و تشکر بابت زحماتتون. دوتا سئوال داشتم. 1) میخوام یه فرمان بدم که ستونهای من همزمان و  به شکل یکی درمیان hide بشن.(به صورت تک تک hide نکنم) . 2) 50 تا شیت دارم که توی شیت 50 سلول a1 رو مساوی با سلول a1 شیت یک قرار دادم و a2 شیت 50 رو با a1 شیت 2 و a3 شیت 50 با a1 شیت سه و به همین منوال تا 49. میخوام با پایین کشیدن سلول در شیت 50 خودش اتومات تعداد شیتها اضافه بشه. ممنون میشم اگر به ایمیلم هم بفرستید
سپاسگذارم بابت توضیح خوبتون
من از یک فایل اکسل برای تطبیق داده ها روی یک طرح ثابت استفاده میکنم که برای منطبق شدن چاپ پشت آن باید داده ها سطرهای زوج و فرد فایل اکسل جابجا شود  یعنی سطر سوم به سطر دوم انتقال یابد و سطر دوم به سوم . به همین ترتیب تا اخر. من این کار رو دستی انجام میدم آیا فرمول یا روش سریعتری وجود دارد.

من از فرمولی که به سوال بهنام پاسخ دادید استفاده کردم برای جابجایی یکی در میان سطرهای یک ستون مناسب هست. ولی برای جابجایی همه ستونهای یک ردیف راه حلی هست؟
 چون هر ردیف من ستونهای زیادی داره و زمان زیادی می بره هر ستون رو جدا این فرمول رو براش به کار ببرم.
سلام 
با تشکر از مطالب خوب و آموزندتون
من میخوام ستون ها را 3 تا درمیان رنگی کنم 
از چه فرمولی باید استفاده کنم
سلام
ممنون از سایت خوبتون
من یک فایل اکسل دارم با 25 ردیف حالا می خوام 25 تا کپی از همین فایل داشته باشم که هر کدومش یک ردیفش خاکستری شده باشه (مثلا فایل 1 ردیف اولش خاکستری باشه - فایل 2 ردیف دومش خاکستری باشه - تا آخر که میشه فایل 25 ردیف بیست و پنجمش خاکستری شده باشه)آیا روش اتوماتیکی برای بوجود آوردن این فایلها هست؟(حتی اگه خروجیها فایلهای پی دی اف هم باشند خوبه)
سپاسگزارم
سلام. دوستانی که میگن هیچ تغییری اعمال نشد ، توی قسمت Preview روی دکمه Format کلیک کنن و در صفحه باز شده از بالا گزینه Fill را انتخاب و یک رنگ را انتخاب کنن.
سلام اگه تو قسمت آپدیت پایگاه داده بخوایم از قطعه p رنگ زردو به نارنجی تغییر بدیم باید چجوری بنویسیم ؟
۲۳ مرداد ۹۸ ، ۱۰:۵۹ محمد حسین عابدی

سلام و عرض ادب

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

ممنون میشم راهنمایی بفرمایید

خدایی دمت گرم

سلام و خسته نباشید.ممنون از سایت خیلی خوبتون.

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

سلام

من یک شیت دارم دارای 8000 تا سطر میخوام در ازای 22 سطر اول تاریخ امروز ثبت بشه و برای 22 تای بعدی تاریخ فردا و همین طور به ترتیب تا آخر.آیا امکانش هست.

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

۰۲ فروردين ۹۹ ، ۱۶:۴۹ محسن امیرخانلو

با سلام. این همه دردسر برای ثبت نام در یک سایت . . . !

امیدوارم ارزشش را داشته باشه.

یک سوال دارم: برای شمردن تعداد سلول های رنگی در یک سطر از فایل اکسل باید چکار کرد؟ هر سطر ممکن است 100 سلول داشته باشد یا بیشتر. با یک شرط ساده، بعضی از این سلول ها رنگی شده اند. حالا میخواهیم بدانیم سلول های رنگی موجود در هر سطر چند تاست؟  فرمولی، تابعی چیزی . . . ؟

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

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

خیلی ممنون میشم که راهنمایی کنین 

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

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

خیلی ممنون میشم که راهنمایی کنین 

۲۷ تیر ۹۹ ، ۱۸:۳۱ سیدمهدی قاسمی

سلام

برا اینکه یک سطر با شرط خاص مثلا تایپ حرف ش در بعضی از سلول های ستون A

اون سطر زرد رنگ بشه

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

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

سلام. وقت شما بخیر. 

من دبیر هستم و نام و نمرات دانش‌آموزان رو در جدول فایل وُردی شامل مثلا 40 خط (ردیف) می‌نویسم. نمرات به صورت خط یا ردیف هستن.

 

امّا هربار که می‌خوام از نمرات 40 نفر میانگین بگیرم، مجبورم اون‌ها رو خط به خط وارد اکسل کنم و Average بزنم. می‌خواستم ببینم راه ساده‌تری نیست؟

راهی که بشه میانگین 40 ردیف رو جدا جدا محاسبه کرد؟ سریع و آسون. 

 

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

با سلام من در اکسل فرمول روبرو را وارد می کنم  if(a1=0;10;99999999)=

ولی پی از اینتر کردن فرمول روبرو را نشان می دهد if(a1=99999999;10;0)=

علت چیست ؟

ارسال نظر

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی