آموزش اکسل هفته ۱۱

آموزش اکسل هفته ۱۱ – ایران ترجمه – Irantarjomeh

مقالات ترجمه شده آماده گروه کامپیوتر

مقالات ترجمه شده آماده کل گروه های دانشگاهی

مقالات

چگونگی سفارش مقاله

الف – پرداخت وجه بحساب وب سایت ایران ترجمه(شماره حساب)ب- اطلاع جزئیات به ایمیل irantarjomeh@gmail.comشامل: مبلغ پرداختی – شماره فیش / ارجاع و تاریخ پرداخت – مقاله مورد نظر --مقالات آماده سفارش داده شده پس از تایید به ایمیل شما ارسال خواهند شد.

آموزش فراگیری اکسل از طریق آقای اکسل –

 هفته ۱۱

این هفته : ابتدا سؤال یکی  از دانشجویان کلاس را مطرح می‌کنم. او در اداره منابع انسانی کار می‌نماید و می‌خواهد فرمول مناسبی برای شمارش تعداد کارمندان بدست آورد.

موضوعات ۲ و ۴ با تابع =RANK سر و کار دارند و چگونگی کار با گره ها را بررسی می‌کند.

موضوع ۳ چگونه نتایج یک تابع RANK را بدست آوریم و چگونه به صورت یک لیست واقعی را با استفاده از فرمول ها مرتب کنیم.

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

مشکل : شما در منابع انسانی مشغول به کار هستید. شما با لیستی از کارمندان که بر اساس سازمان ها دسته بندی شده اند مواجه اید. همانگونه که شکل ۳۶۹ نشان می‌دهد، ترتیب عددی در ستون A و اسامی‌کارمندان در ستون B در اختیار دارید.هر زمان که کارمندی را به کار می‌گیرید یا اخراج می‌کنید، باید شماره تمام کارمندان را به طور دستی اصلاح کنید.

استراتژی : اعداد ستون A را با فرمولی که ورودی های ستون B را شمارش می‌کند جایگزین نمایید. فرمول باید از ردیف جاری تا ردیف ۱(تمام ردیف های بالای ردیف جاری) را شمارش کند.

تابع COUNT عملی نیست، زیرا تنها داده های عددی را شمارش می‌کند. شما باید از تابع COUNTA استفاده نمایید.

• محدوده ای که باید شمارش شود باید از B1 تا ردیف جاری گسترش یابد.

• نماد استفاده دائمی‌از B1، B$1 است.

۱) همانگونه که شکل ۳۷۰ نمایش می‌دهد، این فرمول را در سلول A2 وارد نمایید : =COUNTA(B$:B2).

زمانیکه این فرمول را تا پایین ردیف کپی می‌نمایید، محدوده ای که شمارش می‌شود ا B1 به B3 گسترش می‌یابد، همانگونه که شکل ۳۷۱ نشان می‌دهد. این بدین دلیل است که بخش B2 فرمول بالا یک مرجع وابسته است و زمانیکه فرمول کپی می‌شود قابل تغییر است. نماد دلار در عبارت B$1 به اکسل می‌گوید که اهمیتی ندارد که فرمول را کجا کپی می‌کند، زیرا همواره باید به سلول شماره ۱ اشاره نماید.

آموزش اکسل هفته ۱۱

۲) فرمول را به تمام اسامی‌تا پایین لیست خود کپی کنید. آنها از ۱ تا ۱۹ شماره گذاری می‌شوند، دقیقا به همان صورتی که اسامی‌را به صورت دستی وارد می‌کردید.

زمانیکه که کاردمکندی شرکت را ترک می‌کند، به راحتی می‌توانید ردیف مربوطه را حذف کنید. تمام اعداد دیگر تغییر می‌کنند تا شما بدانید که تعداد کل کارمندان ۱۸ نفر است، شکل ۳۷۲ را ملاحظه نمایید.

هنگامی‌که بازاریاب جدیدی استخدام می‌کنید، یک ردیف خالی درج کنید، نام او را وارد نموده و سپس تک تک فرمول ها را از A به ردیف جدید کپی کنید.

همانطور که شکل ۳۷۳ نشان می‌دهد، تمام کارمندان بعدی مجددا شماره گذاری می‌شوند.

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

توابع بحث شده : =COUNT(); =COUNTA()

درجات رتبه بندی

مشکل : شما چهار نویسنده بر روی یک پروژه کار می‌کنند در اختیار دارید. مطابق شکل ۳۷۴، هر هفته تعداد صفحاتی که در جهت رسیدن به هدف پروژه تایپ کرده اند را گزارش می‌کنید. با خود می‌گویید ای کاش فرمولی در اختیار داشتید که آنها را به ترتیب بالا به پایین رتبه بندی می‌کرد.

استراتژی : چنانچه نگران گره ها نیستید، از تابع RANK استفاده کنید. این تابع به ۲ آرگومان نیاز دارد. به زبان ساده، از تابع انتظار دارید تا مرتبه ای به مقدار موجود در H23 در برابر مقادیر موجود در H23:H26 اختصاص دهد.

همانگونه که شکل ۳۷۵ نشان می‌دهد، در سلول F23 از فرمول =RANK(H23,$H$23:$H$26 استفاده نمایید. توجه داشته باشید که محدوده H23:H26 در پارامتر دوم به خاطر وجود نماد های دلار،به حالت مرجع قطعی است. این امر به فرمول این امکان را می‌دهد تا به راحتی به هر نامی‌در لیست کپی شود.

جزئیات بیشتر : تابعی که در بالا بیان شد مقادیر را به ترتیب بالا به پایین مرتب می‌کند. گاهی نیازمند ان هستید که مقادیر را به صورت پایین به بالا مرتب کنید. یکی از این موارد بازی گلف است. می‌توانید پارامتر اختیاری سومی‌برای تعیین شیوه مرتب سازی، مشخص کنید. استفاده از ۱ برای پارامتر سوم نتایج را به صورت پایین به بالا مرتب می‌کند، شکل ۳۷۶ را مشاهده نمایید.

آموزش اکسل هفته ۱۱

خلاصه : تابع RANK برای رتبه بندی با استفاده از فرمول مفید خواهد بود.

توابع بحث شده : =RANK()

ارجاع متقابل : رتبه بندی یک لیست بدون گره؛ مرتب سازی از طریق یک فرمول

مرتب سازی از طریق یک فرمول

مشکل : در مثال قبلی با استفاده از تابع Rank آرایش مرتب شده نسبی ۴ نویسنده را پیدا کردید، شکل ۳۷۷ را ملاحظه نمایید. اکنون می‌خواهید از فرمولی استفاده کنید تا لیست مرتب شده ای از نویسنده ها را با ترتیب بالا به پایین را در اختیارتان قرار دهد. 

استراتژی : در سلول های F28 تا F31 اعداد ۱ تا ۴ را وارد نمایید. از تابع VLOOKUP برای باز گرداندن نام در ستون G و صفحات در ستون H کمک بگیرید. در اینجا چگونگی کار این تابع را ارائه می‌کنیم.

VLOOKUP برای مراجعه عمودی به کار می‌رود. این تابع دارای ۴ پارامتر است. به زبان ساده، شما از اکسل انتظار دارید تا مقدار موجود در F28 در اولین ستون F23:H26 را جستجو کند. هنگامیکه اکسل مقدار تطابقی مورد نظر را پیدا کرد، نام موجود در دومین ستون محدوده مورد جستجو را باز می‌گرداند.

اولین پارامتر مقداری است که قصد تطابق آن را دارید.در مورد سلول G28 به دنبال مقدار موجود در F28 خواهید بود. آن را به صورت $F28 بنویسید تا بتوانید فرمول را بدون نیاز به نوشتن مجدد آن پارامتر در ستون H کپی نمایید.

پارامتر دوم محدوده پایگاه داده ها شامل ردیف ها و ستون های داده است. مقدار اصلی که در جستجوی ان هستید، باید در ستون اول محدوده باشد. در این مورد، فرمول برابر خواهد بود با : $F$23:$H$26 . توجه داشته باشید که از نماد دلار قبل از حروف ستون و شماره ردیف استفاده می‌کنید تا هنگام کپی نمودن فرمول محدوده دپایگاه داده ها همچنان ثابت بماند.

پارامتر سوم، ستونی که قصد دارید پاسخ را از آن بر گردانید را در اختیار اکسل می‌گذارد. برای نام موجود در ستون G، ستون ۲ محدوده F23:H26 می‌باشد. برای شماره صفحه در ستون H ، ستون ۳ از محدوده F23:H26 می‌باشد.

پارامتر چهارم به اکسل اطلاع می‌دهد که آیا شما اجازه یک تطابق نزدیک را می‌دهید یا خیر. چنانچه داده های اولیه شما مرتب نباشند، لازم است یک تطابق دقیق را مشخص کنید. برای پارامتر چهارم، برای یک تطابق نزدیک واژه TRUE و برای یک تطابق دقیق واژه FALSE را وارد نمایید.

مراحل زیر را پیگیری کنید :

۱) همانگونه که شکل ۳۷۸ نشان می‌دهد، فرمول زیر را در G28 وارد نمایید :

                      = VLOOKUP ($F28, $F$23:$H$26, 2, FALSE.

۲) سلول G28 را به H28 کپی کنید. نتیجه آن در H23 نیز Josh خواهد بود، شکل ۳۷۹ را مشاهده نمایید.

۳) برای تغییر پارامتر سوم از ستون ۲ به ستون ۳، مطابق شکل ۳۸۰، فرمول موجود در نوار فرمول را اصلاح کنید.

اکنون نتیجه موجود در H28 شامل تعداد صفحات نوشته شده توسط Josh خواهد بود.شکل ۳۸۱ را ملاحظه نمایید.

۴) G28:H28 را تا ۳ ردیف پایین تر کپی کنید. اکنون مطابق شکل ۳۸۲ لیست مرتبی از داده ها را در اختیار خواهید داشت.

جزئیات بیشتر : هدف شما همیشه وارد نمودن فرمولی است که بتوانید آن را به کل محدوده  داده کپی نمایید. در این مورد، فرمول موجود در G28 را می‌توانید به هر جایی در ستون G کپی نمایید. اما زمانیکه آن را به ستون H کپی نمایید، پارامتر سوم باید به صورت دستی اصلاح شود. شما باید از پیش ترکیب مناسبی از نماد دلار در مرجع را به کار برید به گونه ای که هنگامیکه فرمول را به ستون H کپی کردید، ۳ تا از ۴ پارامتر درست باشند.

درصورتیکه بدانید تنها چند ستون از داده در مثالی نظیر آن در اختیار دارید، می‌توانید پارامتر سوم را به صورت دستی اصلاح نمایید. چنانچه تعداد ستون داده ها بسیار زیاد باشد، این کار می‌تواند ملال آور باشد. تابع =CELL(“Col”,G28) شماره ستون سلول G28 را بر می‌گرداند. این شماره برای ستون G عدد ۷  و برای ستون H عدد ۸ خواهد بود. می‌توانید از فرمول ذیل در ستون G28 استفاده نماید:

= VLOOKUP ($F28, $F$23:$H$26, CELL (“Col”, G28)-5, FALSE)

چنانچه این فرمول را در سلول G28 وارد نمایید، می‌توانید آن را به تمام ستون ها و ردیف های جدول نتایجتان کپی نمایید.

خلاصه : پس از استفاده از تابع RANK برای به کارگیری مقادیر مرتب شده در یک لیست،از یک جدول دومی‌با شماره های ۱ تا n و مجموعه ای از فرمول های VLOOKUP استفاده نموده تا لیست مرتبی از داده ها را در اختیارتان قرار دهد.

توابع بحث شده : = VLOOKUP();=CELL();=RANK()

ارجاع متقابل : درجات رتبه بندی

رتبه بندی لیست بدون گره

مشکل : تابع RANK هنگام وجود گره ها رفتار عجیبی دارد. می‌توان لیستی داشت که در آن دو نفر رتبه دوم را داشته باشند و هیچ کس سوم نباشد. در شکل ۳۸۳، دورا و جری هر دو با ۹۰ واحد تولید شده رتبه دوم را کسب کردند. نفر بعد هری با ۸۶ واحد است.هری رتبه چهارم را کسب خواهد کرد.

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

در فرمول های ستون F و G، طراح صفحه گسترده به گونه ای عمل کرده که در هریک از رتبه های ۱ تا ۱۰ تنها ۱ کارمند وجود داشته باشد. از آنجاییکه اکسل به هیچ کس رتبه سوم یا هفتم را نداد، جری و بیل همانگونه که در شکل ۳۸۴ مشاهده می‌کنید، در لیست وجود ندارند.

استراتژی : در این مورد، قطعا می‌خواهید لیست موجود در A4:A13 بدن گره رتبه بندی شود. راه حل عمومی‌قابل قبول ممکن است تا حدی پیچیده به نظ برسد، اما عملی است. به زبان ساده، فرمول موجود در ستون C بیان می‌کند که ” رتبه یا RANK این مقدار را به من بدهید، به هر ردیف بالای من که رتبه مساوی دارد یکی اضافه کنید.” همانطور که شکل ۳۸۵ نشان می‌دهد، این کار می‌تواند با فرمول زیر کامل شود :

=RANK(B4,$B$4:$B$13)+COUNTIF(B$3:B3

آموزش اکسل هفته ۱۱

زمانیکه این فرمول را تا پایین کپی می‌کنید،اولین پارامتر COUNTIF از B3 به پایین تا ردیف بالای ردیف جاری را شامل می‌شود. بنابراین، در سلول C13، فرمول به صورت زیر خواهد بود:

=RANK(B13,$B$4:$B$13)+COUNTIF(B$4:B12,B13)

بخش COUNTIF تعداد ردیف های بالای ردیف جاری که رتبه یکسانی دارند را نشان می‌دهد. برای هر ردیف بالای آن یک گره وجود دارد، که کی از ردیف جاری بیشتراست. این باعث می‌شد تا بیل به جای رتبه ششم رتبه هفتم را کسب کند. ممکن است عادلانه نباشد که اشلی قبل از بیل ظاهر شود، اما در گزارش خلاصه ، هر کسی می‌تواند متوجه شود که آنها یک گره دارند.

خلاصه : یک تابع COUNTIF به تابع RANK اضافه کنید تا از گره ها جلوگیری کنید.

توابع بحث شده : =RANK();=COUNTIF()