◄◄◄ آموزش نكات كاربردي Microsoft Excel در اينجا
دوستان عزيزم سلام
هدف از ايجاد اين تاپيك ارائه نكات آموزشي و كاربردي نرمافزار قدرتمند اكسل ميباشد.
توضيحات:
1. دوستان لطفا سوالات و مشكلات خودشون در مورد اكسل
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
بپرسن و از مطرحكردن سوال در اين تاپيك خودداري كنن.
2. حتيالمقدور از گذاشتن آموزشهاي خود بصورت لينك يا آدرس پرهيز نموده و نيز هركدام از آموزشهاي خود را در پست جداگانه ارسال نمائيد تا بتوان در فهرستبندي موضوعات بصورت دقيقتري عمل كرد.
3. سعي كنيد آموزشهاي مبتدي يا حرفهاي خودتون رو بصورت جامع و كامل اينجا قرار بدين تا همه كاربران (مبتدي و ...) بتونن براحتي استفاده نمايند.
4. از دادن پستهاي بيهوده (اسپم) مانند تشكر و .... جدا خودداري نمائيد.
5. نقطه نظرات ، پيشنهادات و انتقادات خودشون رو نسبت به پستهاي اين تاپيك در پيغام خصوصي براي من يا فرستندگان ارسال نمائيد.
فهرست نكات آموزشي : در پست دوم
آموزش فرمول VLookup و نحوه استفاده از آن
فرض كنيد كه يه جدول داشته باشيم كه مي خواهيم در يه خونه از اون با زدن يك كد (كد كالا) قيمت متناظر اون از ستون يا ستونهاي بعديش بياد
تابع VLOOKUP : اين تابع به جستجو در ستون اول محدوده ديتاي شما پرداخته و پس از پيدا كردن مقدار موردنظر ، مقدار متناظر ان را از ستوني كه شما مشخص ميكنيد برميگرداند
مثال :
فرض كنيد قرار است قيمت كد كالايي كه در خانه B8 نوشته مي شود در خانه C8 قرار گيرد با توجه به جدول موجود بالايي
فرمولي كه در خانه
C8 بايد نوشته شود اين است :
کد:
=VLOOKUP(B8,A2:C5,3,FALSE)
مي توان با زدن fx در خانه مربوطه مطابق موارد زير تنظيمات رو انجام داد:
مقدار LOOKUP_VALUE : خانه B8
مقدار TABLE_ARRAY : محدوده A2:C5
مقدار COL_INDEX_NUM : عدد 3
مقدار RANGE_LOOKUP : در اين قسمت FALSE وارد ميگردد
توضيحات مطابق مقادير بالا:
ميخواهيم قيمت فلان كد كالا را
(مقدار LOOKUP_VALUE ) را در محدوده فلان
(مقدار TABLE_ARRAY ) از ستون سوم
(مقدار COL_INDEX_NUM : عدد 3 براي ستون 3) را پيدا كنيم
حالا اگه توي خانه B8 مثلا عدد 101 رو وارد كني بايد توي خانه C8 مقدار معادل قيمت رو بهت بده
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
نكته مهم :
اگر مقدار RANGE_LOOKUP برابر FALSE باشد دقيقا دنبال عدد مورد نظر ( در اين مثال دنبال كد كالا) مي گردد و اگر پيدا نكرد متناظري برنميگرداند و خطا ميدهد ولي اگر TRUE باشد دنبال بزرگترين عددي كه از آن كوچكتر است ميگردد و متناظر ان را برمي گرداند.
1 پيوست (پيوستها)
ماژول جهت تبديل تاريخ شمسي و ميلادي به يكديگر
سلام دوستان
اينم يه ماژول خوب براي تبديل تارخ شمسي و ميلادي به همديگه و قابليتهاي ديگه
جالبه و با طرز كار راحت دقيقا مانند مابقي فرمولها ميتونين از فرمولهاش استفاه بكنين
طرز استفاده :
با دقت كارهاي زير رو انجام بدين
فايلي كه حاوي ماژول تبديل تاريخ هست رو از ضميمه دانلود كنيد
اون رو از حالت زيپ شده خارج كنيد
بعد اكسل رو باز كرده و با زدن ALT+F11 به ويژوال بيسيك بريد
در اون محيط از منوي File گزينه Import File رو بزنيد و آدرس فايل اكستراكت شده رو بديد
حالا با اين كار يه ماژول به نام Module1 به ليست سمت چپ اضافه ميشه
با دابل كليك روي اون ماژول كدهاي نوشته شده ظاهر ميشه
در اخرين سطور موجود در قسمت كدها توضيحات هشت تابع ديده ميشه كه به رنگ سبز هست
اينم دو تا نمونه توضيح مربوط به دو تابع مفيد mtos و stom
mtos() : اين تابع تاريخ ميلادي رو بصورت y/m/d (روز/ماه/سال) ميگيرد و معادل شمسي ان را بصورت y/m/d بر ميگرداند
stom() : اين تابع تاريخ شمسي رو بصورت y/m/d (روز/ماه/سال) ميگيرد و معادل ميلادي ان را بصورت y/m/d بر ميگرداند
مابقي توضيحات توابع ديگه هم هست كه ميتونيد مطالعه كنيد
حالا كافيه در يكي از خانه هاي اكسل براي تبديل تاريخ ميلادي نوشته شده در خانه A1 به شمسي فرمول زير رو بنويسيد
موفق باشين
چگونه توابع نوشته شده بوسيله ويژوال بيسيك در اكسل را با اجراي اكسل بطور اتوماتيك فراخواني كنيد
در اينترنت ميتوان كدهاي مختلفي را در قالب ماژول پيدا كرد كه به زبان VBA نوشته شده و حاوي توابع به دردبخوري براي اكسل هست
با اضافه كردن اين توابع به اكسل ميتوان كارهاي زيادي انجام داد
از جمله اين ماژولها ميتوان به ماژولي كه من در
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
همين تاپيك گذاشتم و مربوط به تبديل تاريخ شمسي و ميلادي به يكديگر هست اشاره كرد
حال در اين مقاله قصد دارم اشاره كنم كه چگونه ميشه اين توابع رو جوري تنظيم كرد كه با هر بار باز شدن اكسل اتوماتيك وار اجرا بشه و نيازي نباشه هر دفعه كه اكسل رو باز ميكني اونا رو بصورت دستي وارد كني
فرض كنيد شما تابعي رو پيدا كرديد كه بصورت يك فايل متني هست (فايل ضميمه
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
)
حال اونو به همون روشي كه در
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
گفته شده ميتونيد در قسمت ويژوال بيسيك اكسل Import كنيد
پس از اين كار كافيه فايل اكسل خود را با نام دلخواه مثلا Function1 و با پسوند XLA در اكسل 2003 و با همين پسوند يا پسوند XLAS كه مختص 2007 هست و در مكاني كه خودش پيشنهاد ميده (يا مكان دلخواه) ذخيره كنيد
براي اينكه فايل شما با پسوند XLA ذخيره سازي بشه از باكس save as type گزينه Microsoft Office Excel Add-in رو انتخاب كنيد
تا اينجاي كاري تفاوت چنداني بين ورژن 2003 و 2007 وجود نداره ولي از اين پس براي فراخواني اتوماتيك اون يه كم مسير ها در اين دو ورژن متفاوته
2003 :
به منوي Tools رفته و گزينه Add-ins رو بزنيد و از كادر باز شده درصورتيكه فايلتون در ليست وجود داره تيك كنار اونو بذاريد و درصورتيكه فايلتون وجود نداره بااستفاده از Browse اونو پيدا كنيد و كادر را OK كنيد
2007 :
به كادر Excel Option بريد و در سربرگ Add-ins و از باكس Manage گزينه Excel Add-ins رو انتخاب كرده و دكمه Go رو بزنيد. حال از كادر باز شده درصورتيكه فايلتون در ليست وجود داره تيك كنار اونو بذاريد و درصورتيكه فايلتون وجود نداره بااستفاده از Browse اونو پيدا كنيد و كادر را OK كنيد
با انجام اين اعمال فوق از اين پس هر بار اكسل باز ميشود بصورت اتوماتيك تمامي كدهايي كه در فايل اشاره شده وجود دارد در دسترس خواهند بود
ذكر اين نكته هم ضروري است كه توابعي كه به اين صورت به اكسل اضافه ميشوند با تايپ كردن آن در نوار فرمول به آن دسترسي نداريد و بايد با زدن Fx و انتخاب گزينه ALL يا User Defined مطابق شكل به آنها دسترسي پيدا كنيد
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
موفق باشيد
افزایش کارآیی Excel 2010 و سرعت در آن
با تعداد Bit بیشتر
Excel 2010 به صورت ذاتی یک نسخه 64 بیتی است و این درحالی است که نسخه های قدیمی اکسل با محدودیت های 32 بیتی مواجه بودند.
معنی این 64 بیتی بودن Excel 2010 این است که می توان فایلهای بسیار عظیمی داشت. در تست های انجام شده توسط خود مایکروسافت اکسل 64 بیتی نزدیک به 1.2 تا 1.5 برابر سریعتر از اکسل 32 بیتی بوده است ( البته نمی توان بعنوان یک قانون کلی گفت که نسخه های 64 بیتی سریعتر از 32 بیتی هستند. )
در ضمن فایلهای Excel 2010 که 64 بیتی هستند هیچ تفاوتی با نسخه 32 بیتی ندارند و کاملا بایکدیگر سازگار می باشند تفاوت عمده در بزرگی Workbook هایی است که با نسخه 64 بیتی می توانیم داشته باشیم و اگر این فایل توسط اکسل 32 بیتی باز شود ممکن است پیغام "Out Of Memory" را ببینید.
بهینه سازی الگوریتم محاسبات در Excel 2010
در اکسل 2007 استفاده از محاسبات
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
باعث افزایش سرعت محاسبات فایلها شده بود اما تیم اکسل باز قصد بهبود این سرعت را در اکسل 2010 دارد. بهینه سازی عملیاتهای عمومی کاربران با داده ها مثل وارد کردن داده ها و پر کردن ، مرتب سازی ، فیلترینگ ، و کپی / چسباندن یکی از اساسی ترین کارهایی بوده است که بر روی اکسل 2010 انجام شده .
افزایش سرعت محاسبات برنامه های VBA
همانطور که در شکل زیر معلوم است در بیش از 70% موارد ، اکسل 2010 توانسته است سریعتر از نسخه قبلی کدهای VBA را اجرا نماید.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
استفاده از امکانات چند هسته ای (Multi Core)
در حال حاضر CPUهای چند هسته بسیار متدوال شده است و استفاده از امکانات این CPUها در دستور کار Excel 2010 قرار داشته است ، با مطالعات انجام شده در حیطه های زیر از این تکنولوژی بهره گرفته شده است:
شکل زیر نشان دهند این موضوع است که در هنگام باز شدن یک فایل دو عملیات به صورت همزمان انجام می گیرند، یک عملیات توسط یک CPU مختص به خواندن فایل از روی Hard است و عملیات دیگر مختص به قراردادن اطلاعات در حافظه می باشد که توسط یک CPU دیگر انجام می شود.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
در شکل زیر زمان باز شدن دو فایل در Excel 2010 و Excel 2007 مقایسه شده است.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
این مقاله ادامه خواهد داشت.
SparkLine قابلیت جدیدی در EXCEL 2010
در اکسل 2010 می توان دریک سلول نموداری رسم کرد که به صورت خلاصه وضعیت چند سلول را نشان دهد مثلا:
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
در شکل بالا هر کدام از کارمندها سعی دارند که مشتری بیشتری را جذب کنند مثلا سام تا بحال 223 مشتری را جذب کرده است که سابقه جذب مشتریهای او تا بحال در خانه جلوی آن نشان داده شده است.
در شکل زیر نمونه دیگری از sparkline excel 2010 را مشاهده می نمایید.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
در اکسل 2010 در ریبون آن Sparkline Tools اضافه می شود که می توان روی این نمودارها تنظیمات دقیق را انجام داد.
مثلا در شکل قبل نقاط مینیمم نشان داده شده است.
نقل قول:
منبع : سایت آموزشی فرساران به آدرس
جمع زدن سلولهای رنگی در Excel با برنامه VBA (منبع: پرتال فرساران، فرشیــد میـــدانی)
در اکسل 2003 راه مستقیمی برای انجام عملیاتهای ریاضی مانند جمع زدن، میانگین و ... بر روی خانههایی که دارای یک رنگ هستند وجود ندارد و با تکنیک Find و استفاده از Copy/Paste به سختی میتوان اینکار را انجام داد.
برای این منظور در اکسل ما باید کد رنگ خانه را پیدا کنیم و سپس بر اساس آن با استفاده از روشهایی مانند Filter و یا توابعی مانند SumIf بر اساس این کد (که در واقع یک عدد است) عملیات ریاضی را انجام دهیم.
برای اینکار در اکسل بایستی تابع جدید ایجاد کنیم که توسط آن کد رنگ هر خانه محاسبه شود بنابراین دست به کار میشویم و با زبان VBA اکسل یک تابع جدید تعریف میکنیم.
قدم اول: ایجاد تابع در محیط ویژوال بیسیک
یک فایل خالی اکسل را ایجاد کنید و با زدن کلید Alt+F11 وارد محیط ویژوال بیسیک اکسل شوید.
به مسیر Insert à Module بروید و یک ماژول ایجاد کنید، ماژول جایی است که میتوانید در آن دستورات ویژوال بیسیک را وارد نمایید.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
برای ایجاد یک تابع Insert à Procedure را انتخاب نمایید و در پنجرهای که باز میشود برای تابع جدید یک نام تایپ میکنیم مثلا در اینجا نام تابع را ColorCode میگذاریم در ضمن گزینه Function را انتخاب میکنیم.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
مطابق شکل زیر کد این تابع را کامل نمایید.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
قدم دوم: استفاده از این تابع برای جمع زدن خانههای نارنجی
حال تابع شما آماده است و در اکسل میخواهیم از آن استفاده کنیم.
از محیط ویژوال بیسیک خارج شوید (لازم نیست Save کنید) و به اکسل بگردید.
برای استفاده از این تابع محدودهای مانند شکل زیر را در نظر بگیرید که قرار است خانههای نارنجی رنگ را جمع بزنید و مطابق شکل از تابع ColorCode استفاده میکنیم.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
و در نهایت بایستی چیزی مانند شکل زیر بدست آید که در کنار هر سلول کد رنگ آن سلول نمایش داده میشود.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
و اینک توسط تابع SumIf خانههای نارنجی رنگ را جمع میزنیم عدد 46 که در تابع بکار برده شده است کد رنگ "نارنجی" میباشد.
[ برای مشاهده لینک ، با نام کاربری خود وارد شوید یا ثبت نام کنید ]
توجه: اگر بعد از اینکه فرمول را نوشتهاید رنگ خانه را تغییر دهید متوجه میشوید که تابع کد رنگ قبلی خانه را میدهد نه رنگ جدید و این موضوع به این دلیل است که اکسل محاسباتی را هنگام عوض کردن رنگ انجام نمیدهد و وقتی محاسبات انجام میشود که شما در محتوای سلول تغییری را اعمال کنید (بعنوان مثال یک عدد تایپ شود) البته میتوان از کلید F9 نیز برای اینکار استفاده کرد و بلافاصله تابع کد رنگ جدید را بر میگرداند.
منبع : پرتال فرساران
طرز ايجاد ليست در خانه اي از اكسل
سلام
براي ايجاد يك ليست در خانه اي از اكسل كه قرار است مقادير ثابتي را در اختيار كاربر قرار دهد (مثلا ماه هاي سال و ... ) ميتوانيد به مراحل زير عمل كنيد
1- ايتدا در خانههايي مقادير مورد نظر را تعريف كنيد (مثلا در A1 تا A12 ماه هاي سال)
2- حال در خانه اي كه قرار است آن را بصورت ليست به شما نمايش بدهد (مثلا B2) كليك كرده و از منوي Data گزينه Validation را انتخاب كنيد
3- از باكس Allow گزينه List را انتخاب كنيد
4- در باكس Source خانههاي مورد نظر (A1 تا A12 در مثال ما) را درگ كنيد تا آدرس مربوطه درج شود
5- كادر موردنظر را OK كنيد.
موفق باشيد
تذكر مهم: نكته مهم در اينجا اين است كه سورس اين ليست نميتواند در شيت ديگري باشد
کتاب آموزش فرمولهای برداری اکسل
فرمول نویسی در اکسل یکی از ستونهای بسیار مهم این نرم افزار است و حتی می توان توسط فرمول نویسی در محیط اکسل نرم افزاری جدیدی تولید کرد. اکسل بیش از 350 تابع دارد که ترکیب این توابع با یکدیگر می تواند فرمولهای پیچیده و متنوعی را تولید نماید.
در محیط اکسل شما می توانید با ماتریس ها نیز کار کنید، ماتریسی بسازید و روی آن محاسباتی را انجام دهید به فرمولهایی که روی این ماتریس ها کار می کنند فرمولهای برداری یا Array fomula می گویند.
درک این فرمولها و کار با آنها در اکسل واقعا پیچیده است و به همین دلیل اکثر کاربران حتی نامی از آن را نشنیده اند . برای آشنایی دوستان فارسی زبان حدود 2 سال پیش نگارش کتابی تحت عنوان "هزار تو های اکسل" و با موضوع " فرمولهای برداری اکسل" را شروع کردم که به تازگی کار آن تمام شده است . (البته اگر دل و توفیقی باشد کامل تر نیز خواهد شد).
شما میتوانید از لینک زیر دریافت کنید و همچنین فایل اکسل 2007 مثالهای کتاب نیز برای دانلود قرار داده شده است.
کد:
http://www.mediafire.com/?hzlzdzzqmud
منبع: پرتال فرساران
ويرايش شد
By wordist
طرز استفاده از دو تابع سودمند SUMIF و SUMIFS
سلام
در اكسل گاهي نياز مي شود كه جمع مواردي از يك ستون رو محاسبه كنيم كه معادل انها در ستونهاي قبلي يا بعدي يك آيتم خاص يا يك شرط خاص باشد.
مثال : جمع قيمتهاي آيتم " سيب " را محاسبه كنيد
B ----- A
1 سيب --- 100
2 پرتقال -- 500
3 سيب -- 1000
4 انگور --- 150
5 سيب -- 1100
...
اگر ستون اول را A و ستون دوم را B در نظر بگيريم طبق فرمول داريم :
کد:
=SUMIF(A1:A5,"سيب",B1:B5)
مثال : جمع قيمتهاي آيتم " سيب " را محاسبه كنيد كه بيش از 3 كيلو خريد شده است
C ------------- B ----- A
1 سيب --- 100 ---------- 2.5
2 پرتقال -- 500 ---------- 3
3 سيب -- 1000 -------- 4.7
4 انگور --- 150 --------- 5
5 سيب -- 1100 -------- 6
...
در اينجا چون دو شرط وجود دارد بايد از تابع SUMIF استفاده شود بصورت ذيل:
کد:
=SUMIFS(B1:B5,A1:A5,"سيب",C1:C5,">3")
نكته مهم:
فرمول SUMIFS در نسخه هاي قبل از 2007 آفيس وجود ندارد
موفق باشيد