سه شنبه, ۲۹ ارديبهشت ۱۳۹۴، ۰۳:۳۰ ب.ظ
دوستان خوبم سلام
گاهی اوقات خواندن اطلاعات در یک پایگاه اطلاعاتی وسیع خسته کننده و دشوار می شود برای حل این مسئله شما شروع به تغییراتی در پس زمینه صفحه خود می کنید اما این سوال پیش میاید که چطور می شود یک تغییر ثابت و سریع و موثر را به پایگاه اطلاعاتی خود اعمال کنیم ؟!!
برای این کار به سراغ Conditional Formatting می رویم !!!
* ابتدا محدوده مورد نظر خود را انتخاب می کنیم و به مسیر زیر می رویم home > styles > Conditional Formatting و گزینه new rules را انتخاب می کنیم .
*از صفحه مقابل به قسمت ......use a formula to می رویم و در کادر نمایش داده شده فرمول mod(row();2)=0= را وارد می کنیم و از قسمت format رنگ مورد نظر را انتخاب می کنیم و ok را میزنیم مانند تصویر زیر :
همانطور که میبینید سطرها به صورت یکی در میان به رنگ format تغییر زمینه داده اند برای اینکه این تغییر برای ستون ها اتفاق بیافتد مانند توضیحات پیش رفته و تنها از این فرمول استفاده کنید : mod(COLUMN();2)=0=
دریافت فایل حاوی فرمول
عنوان: رنگی شدن یک در میان سطر یا ستون
حجم: 9.25 کیلوبایت
شما می توانید با کلیلک روی " نظر " در قسمت پایین این مطلب هرگونه سوال و نظری را با ما در میان بگذارید.
۱
۰
۹۴/۰۲/۲۹
پاسخ:
با سلام دوست عزیز
این کار با استفاده از کد 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
پاسخ:
سلام خدمت شما همراه عزیز
فرمولی که به صورت مستقیم اینکار را انجام بدهد در اکسل موجود نیست اما با استفاده از ترفندهایی از جمله ترکیب فرمول ها و یا 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 کنید.
با آرزوی موفقیت
پاسخ:
شما میتوانید از دو روش استفاده کنید منتهی این نکته مد نظر باشد که تاریخ ها دقیقا باید با یک فرمت و به یک شکل وارد شوند.
* روش اول : در بعضی از سیستم ها با تغییر فرمت سلول به general این مسئله حل خواهد شد.
* روش دوم : (استاندارد) در این روش شما تمام تاریخ ها را بدون "/" یعنی به شکل : 13940612 وارد خواهید کرد و در نهایت تمام ستون هایی که در آن تاریخ موجود هست را انتخاب و کلیک راست کرده و به format cells می روید و از custom فرمت جدید ##"/"##"/"#### ایجاد و اعمال می کنید.
ضمنا اگر شما تمام تاریخ ها را به شکل 1394/06/12 وارد کرده اید ستون تاریخ ها را انتخاب کنید و ctrl+f را بزنید به تب replace بروید در کادر اول "/" را تایپ کنید و در کادر دوم چیزی تایپ نکنید سپس replace all را کلیک کنید.
پایدار باشید
پاسخ:
با سلام فایل درخواستی برای شما تهیه شد بر روی "دریافت فایل" کلیک و آن را ذخیره نمایید. :
دریافت فایل
*نکات مهم : 1-محدوده کل ستون انتخاب شود 2- B2 را به B1 و C2 را به C1 تغییر دهید 3- از فرمت موجود در پنجره conditional formating یک رنگ به دلخواه انتخاب شود 4-تاریخ دقیقا شبیه بهم باشد 5- قسمت "1:2" یک قرارداد در فرمول if است به این معنی که اگر تاریخ در ستون b بزرگ تر از تاریخ در ستون c بود عدد 1 و اگر کوچک تر بود 2 تایپ شود و در نهایت قسمت "1=" به این معنی است که اگر عدد 1 تایپ شد یعنی برای هر ردیفی که در آن شرط "1=1" بود ، سلول مربوط به آن در محدوده رنگی شود. 6- استفاده از ترفند فرمت سل برای تاریخ به این معنی است که تاریخ در ظاهر تنها با "/" نمایش داده می شود در حالی که در اصل بدون "/" است و اگر بروی سلول دابل کلیک شود مقدار بدون "/" است پس چون ظاهری می باشد دیگر در find قابلیت جستجو ندارد و نیازی هم به برداشتن "/" دیگر وجود ندارد چون مقدار سلول عددی است.
پاسخ:
ممنون از لطف شما . قطعا توجه شما موجب قوت هرچه بیشتر مطالب آموزشی خواهد بود.
عرض کنم که در پیش فرض کاندیشنال فرمتینگ شروط متفاوتی موجود است و در بسیاری از موارد قابل استفاده است.
اما اینکه شخص بخواهد شرط جدیدی را تعریف کنید بستگی به میزان آشنایی با فرمول ها و خلاقیت در اکسل آن شخص دارد و خلاقیت هم می تواند بی نهایت باشد.
تا کنون مواردی از این خلاقیت چه در "خانه اکسل" چه در سایت های دیگر به نمایش گذاشته شده که سعی می شود نمونه های بیشتری قرار داده شود.
پاسخ:
با عرض سلام
ساده ترین روش ممکن استفاده از ctrl+f و جستجوی حرف موردنظر می باشد.
پاسخ:
با سلام
ساده ترین راه استفاده از find&replace می باشد.
ستون تاریخ را انتخاب کنید. ctrl+f را زده به تب replace بروید در کادر اول مثلا : 9405 و در کادر دوم 9410 را وارد کنید سپس replace کنید.
پاسخ:
بافرض اینکه سه ستون به ترتیب (تاریخ ؛ تعداد ماه ؛ تاریخ جدید ) در 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))
پاسخ:
با سلام. مشکلی در فرمول نیست احتمالا کامل کپی نکرده اید. فایل زیر را دانلود نمایید.
دریافت فایل
پاسخ:
سلام روزهای شنبه تا چهارشنبه در زمان های کاری معمولا پاسخ گویی با تاخیر کمتری انجام میشه.
فایل شما قطعا نیاز به بررسی داره اما راهی که انجامش ساده و عملی هست اینه که شما در یک ستون شماره ردیف ها رو یک در میان وارد کنید که با فرمول اینکار شدنیه و در ستون بعدی با استفاده از index و یا hlookup مقادیر رو فراخوانی کنید و در نهایت نمودار موردنظر رو ترسیم کنید.
پاسخ:
با سلام
شما وقتی از merge استفاده می کنید شماره ستون ها یا سطر ها دیگر پشت سر هم نیست و نمی شود از شماره ستون ها برای رنگی کردن یکی در میان استفاده کرد.
*اگر امکان دارد در دیتابیس خود از merge استفاده نکنید چون نه تنها در این مورد بلکه در بسیار موارد دیگر مشکل ساز است و استاندارد نیست.
* اگر محتوی سلول ها به صورت یک در میان است (مثلا اولی a و دومی b و به همین شکل تا انتها) شما می توانید از محتوی برای رنگی کردن یکی در میان استفاده کنید.
موفق باشید
پاسخ:
با سلام از کد زیر استفاده کنید :
کد:
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
پاسخ:
با سلام
در فایل ارسال شده پست قبلی کدهای vba به کار برده شده و استفاده از آن ها نیاز به حداقل دانش vba دارد که شما می توانید از قسمت vba وبلاگ و یا سایت های دیگر آموزش های ابتدایی رو مطالعه کنید.
پاسخ:
با سلام
بله اینکار امکان پذیر است منتهی به دلیل اینکه در اکسل تاریخ شمسی تعریف نشده شما می بایست از ستون ها یا توابع کمکی برای اینکار استفاده کنید.
پاسخ:
با سلام
فایل شما بررسی و پاسخ به ایمیل شما ارسال شد.
پاسخ:
با سلام
فرمول را در چه قسمتی وارد می کنید ؟ پیغام خطا چیست ؟
پاسخ:
با سلام به ترتیب زیر عمل کنید :
* ابتدا سطرهای مورد نظر را انتخاب کنید .
* به conditional formating بروید و از new rule به گزینه آخر یعنی use a formula.... بروید .
* در کادر مانند فرمول زیر را وارد کنید و به جای a2 آدرس سلولی که می خواهید با تغییر آن سطر رنگی شود را وارد کنید و از format یک رنگ را به دلخواه انتخاب نمایید :
if($A2="*";1;0)=1
* دقت کنید علامت $ فقط قبل از A باشد و اگر بین A و عدد بود آن را حذف کنید همچنین به هنگام انجام این فرایند حتما می بایست سطرها در حالت انتخاب باشند.
پاسخ:
با سلام به شما دوست گرامی
شما می بایست اطلاعات کامل هر بیمار را وارد کنید یعنی برای تمام 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 است اما از تلفیق فرمول ها و یا کد می شود استفاده کرد.
پاسخ:
با سلام
شما می بایست از فرمول های جستجوی اکسل مثل : VLOOKUP یا ترکیب INDEX و MATCH و... استفاده کنید که چند نمونه در سایت موجود می باشد.
پاسخ:
سلام با فرض اینکه مقادیر شما در ستون 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
پاسخ:
با سلام . برای انجام اینکار فایل شما می بایست بررسی بشه.
پاسخ:
با سلام اینکار هم با فرمول هم با vba انجام میشه که یک نمونه vba منتشر شد.
دریافت فایل حاوی فرمول
محمد
