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

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

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

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

مقالات

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

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

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

 هفته ۱۷

این هفته : ۴ موضوع در مورد داده های ناقص. دو موضوع اول مفاهیم مفید برای حل مشکل در موضوع ۳ را معرفی می کنند.

بخش ۳ : داده های بحث بر انگیز

شماره گذاری هر رکورد برای یک مشتری، شروع از۱ برای یک مشتری جدید

مشکل : مطابق شکل ۸۱۰ لیستی از داده های صورت حساب در اختیار دارید. می خواهید رکورد ها را به گونه ای نام گذاری نمایید که شماره اولین صورت حساب برای Ford عدد ۱ باشد. صورت حساب بعدی Ford عدد ۲ و الی آخر. زمانی که به مشتری جدید رسیدید، می خواهید برای این مشتری هم از ۱ شروع کنید.

استراتژی : داده ها را به ترتیب مشتری مرتب نمایید. مطابق شکل ۸۱۱، ستون موقتی A را درج نموده و عنوان “Rec#” را به آن اضافه نمایید.

فرمول موجود در A2 برابر است با =IF(C2=C1,1+A1,1). به زبان ساده، این فرمول بدین معنی است ، ” اگر مشتری درC با مشتری بالایی  یکی باشد، آنگاه عدد ۱ را به سلول بالایی اضافه کن، در غیر این صورت از ۱ شروع کن”.

نتیجه : همانگونه که شکل ۸۱۲ نشان می دهد، هر گروه از صورت حساب های مشتری از یک ۱ تا N شماره گذاری می شوند.

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

مطابق شکل ۸۱۳، فرمول های ستون A را کپی و سپس از گزینه Paste Special – Values جهت تغییر اعداد به گونه ای که بتوانید داده ها را توسط شماره صورت حساب مجددا مرتب سازی نمایید، استفاده کنید.

استراتژی دیگر : فرمول COUNTIF($C$2:C2,C2) بدون مرتب سازی این کار را انجام می دهد.

خلاصه : تابع IF در این کار برای مقایسه رکورد فعلی با رکورد بالایی یک تابع کامل است.

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

افزایش یک عدد گروهی به هر مجموعه از رکورد ها با یک عدد منحصر به فرد برای مشتری

مشکل : لیستی از داده های صورت حساب را در اختیار دارید. می خواهید رکورد ها را به گونه ای شماره گذاری کنید که صورت حساب های اولین مشتری همه دارای شماره گروهی ۱ باشند. صورت حساب های مشتری بعدی همه شماره گروهی ۲ داشته باشند.

استراتژی : داده ها را به ترتیب مشتری مرتب نمایید. ستون جدید A را به همراه عنوان Group اضافه نمایید. در سلول A2، عدد ۱ را برای Group #1 وارد نمایید. در سلول A3،  فرمولی را وارد نمایید که برای بقیه رکورد ها استفاده شود: =IF(C3=C2,A2,1+A2) .

به زبان ساده، ” اگر مشتری این ردیف با مشتری ردیف بالایی برابر است، آنگاه از شماره گروه ردیف بالا استفاده کن. در غیر این صورت، یکی به شماره گروه بالا اضافه کن.” این فرمول را به تمام ردیف های  پایین ، مطابق شکل ۸۱۴ کپی نمایید.

نتیجه : به تمام رکورد ها یک شماره گروه تعلق می گیرد. هر مشتری شماره گروه مخصوص به خود را دارد.

جهت مرتب سازی بعدی، فرمول های ستون A را کپی نموده و با استفاده از گزینه Paste Special – Values فرمول ها را به اعداد تبدیل نمایید.

خلاصه : از تابع IF برای اضافه کردن یک شماره گروه به هر گروه از رکورد ها استفاده کنید.

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

کار با داده هایی که درآن هر رکورد ۵ ردیف فیزیکی می گیرد

مشکل : مدت زمانی پیش، در زمان استفاده از COBOL ، برنامه نویسی بر روی محدودیت های عرض فیزیکی یک صفحه کار می کرد. آن برنامه نویس گزارشی تهیه کرد که در آن هر رکورد در حقیقت ۵ خط گزارش را به خود اختصاص می داد، مطابق شکل ۸۱۵٫ اکنون می خواهید این داده ها را در اکسل بررسی کنید.

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

استراتژی : هدف شما این است که داده ها را به حالت یک ردیف به ازای هر رکورد باز گردانید. این کار امکان پذیر است. این فرایند شامل افزودن ۲ ستون جدید می باشد که یکی شماره گروهی و دیگری ترتیب نامیده می شود.

۱) ابتدا یک ردیف ۱ جدید اضافه نمایید. دو ستون جدید A و B را وارد نمایید. عنوان ها را مطابق آنچه در شکل ۸۱۶ نشان می دهد در A1:C1 وارد نمایید. عنوان ها باید Group یا گروه، Sequence یا ترتیب و Text یا متن باشند.

۲) در ستون A ، یک شماره گروهی به هر رکورد منطقی اختصاص دهید.

یک راه برای انجام این کار این است که بررسی نمایید که آیا ۴ کاراکتر اول ستون C “ACCT” هستند؟ اگر پاسخ بلی بود، به شماره گروه ۱ را اضافه کنید. در A2، عدد ۱ را وارد نمایید. در A3، فرمول =IF(LEFT(C3,4)=”ACCT”,1+A2,A2) را وارد نمایید. آن را به تمام ردیف های پایین کپی نمایید. این کار به سادگی به هر گروه منطقی از رکورد ها یک شماره گروهی اختصاص می دهد، مطابق شکل ۸۱۷٫

۳) سپس، فرمولی را برای عدد شماره ترتیب طراحی نمایید.

در سلول B2، فرمول =IF(A2=A1,B1+1,1)  وارد نمایید. این فرمول را تا پایین کپی نمایید. این فرمول  مطابق شکل ۸۱۸ هر رکورد در گروه را شمار گذاری می کند. باید مطمئن شد که تمام شماره حساب ها بر رکورد با شماره ترتیب ۱ قرار دارند.

۴) این مرحله بسیار حیاتی است. فرمول های موجود در ستون A و B را کپی نموده و آنها را با استفاده  از Paste Special Values به عقب Paste  کنید. این به شما تضمین می دهد که می توانید داده ها را با اطمینان مرتب نمایید.

۵) داده ها را بر اساس شماره ترتیب ستون B مرتب کنید. داده های شما شبیه شکل ۸۱۹ به نظر خواهند رسید. اکنون تصمیم دارید داده ها را به صورت هوشمندانه از هم جدا نمایید به گونه ای که تمام رکورد های مشابه با هم باشند. محدوده پیوسته ای از C2:C7 تمام ردیف های اول هر رکورد را بر می گیرد. تمام خطوط ۱ رکورد ها، ۳ فیلد دارند که در حقیقت باید  به سه ستون جداگانه تجزیه شوند. این کار را می توانید به راحتی به کمک Text to Column Wizard انجام دهید.

۶) سلول های C2:C7 را انتخاب نمایید. از منو، Data – Text to Columns را انتخاب نمایید. گزینه Fixed Width را انتخاب و سپس بر روی Next کلیک کنید. همانند شکل ۸۲۰٫

۷) اکسل باید بتواند به درستی مکان ستون های شما را مطابق شکل ۸۲۱ حدس بزند. Next را کلیک کنید.

۸) عنوان هر ستون را انتخاب نموده و یک فرمت داده ای برای آن انتخاب نمایید. در هر بار استفاده نیاز به واژه ACCT ندارید، بنابراین با انتخاب گزینه Skip فیلد های اول، سو م و پنجم را صرف نظر کنید. برای فیلد ششم تاریخی اختصاص دهید. زمانی که اطلاعات شما مشابه شکل ۸۲۲ شد، گزینه Finish را برای خاتمه کلیک کنید.

۹) داده ها را در ۳ ستون از گروه ۱ در اختیار خواهید داشت. همانگونه که شکل ۸۲۳ نشان می دهد، عنوان سلول C1 را به Acct ، عنوان D1  را به INV و عنوان E1 را به Date تغییر دهید.

۱۰) سپس، A8:C13 را انتخاب نمایید. آن ها را Cut و به F2 ، Paste نمایید. عنوان های F1:H1 را مطابق شکل ۸۲۴ به Group, Seq و Total تغییر دهید.

۱۱) H2:H6 را انتخاب و سپس Data – Text to Columns را انتخاب نمایید. در مرحله ۱، Fixed Width را انتخاب و سپس بر روی Next کلیک کنید. در مرحله ۲، اکسل پیشنهاد می دهد که داده های شما را به ۳ فیلد تقسیم کند. نیازی به داشتن یک ستون برای واژه صورت حساب و ستون دیگری برای واژه Total نیست. مطابق شکل ۸۲۵، خط بین Invoice و Total را دابل کلیک کنید تا این خط حذف شود.

۱۲) پس از دابل کلیک کردن خط اول، این خط ناپدید می شود. اکنون Next همانند شکل ۸۲۶ کلیک نمایید.

۱۳) در گام ۳، با انتخاب “Invoice Total” از فیلد صرف نظر کرده و مانند شکل ۸۲۷ دکمه Finish را برای خاتمه کلیک کنید.

۱۴) رکورد های گروه ۳ را مطابق آنچه شکل ۸۲۸ نشان می دهد انتخاب نمایید.

۱۵) آنها را به I2 کپی نمایید. عنوان های موجود در I1:K1  عبارتند از Group,Seq و Name . شکل ۸۲۹ را ملاحظه نمایید.

۱۶) رکورد های گروه ۴ را انتخاب نموده ، آنها را Cut و در I2 ، Paste کنید.

۱۷) رکورد های گروه ۵ را انتخاب نموده ، آنها را Cut و در O2،  Paste کنید.

۱۸) همانگونه که شکل ۸۳۰ نشان می دهد، رکورد های گروه ۶ هیچ داده ای ندارند – آنها تنها خطوط تیره هستند. می توانید این ردیف ها را حذف نمایید.

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

اکنون تمام فیلد ها به صورت یک خط به ازای هر رکورد هستند. همچنین واژه های Group و Sequence هرکدام ۵ ستون را در بر گرفته اند. پیش از حذف ستون های  گروه و ترتیب، اطمینان حاصل نمایید که همه چیز به درستی کار می کند. شماره های  گروهی در ستون های A,F,I,L و O باید با تمام گروه ها تطابق داشته باشند.

۱۹)  همانگونه که شکل ۸۳۱ نشان می دهد، در انتهای یک ستون خالی، یک تابع بزرگ AND را مانند زیر وارد نمایید. این فرمول را به تمام ردیف های پایین کپی نمایید:

=AND (A2=F2, F2=I2, I2=L2, L2=O2)

۲۰) یک مقدار True یعنی شما با موفقیت تمام رکورد های گروه ۱ را مجددا به درستی در کنار هم قرار دادید. برای بررسی اینکه همه ردیف ها درست باشند، فرمول =AND(R2;R99) را در سلول R1 قرار دهید. همانگونه که شکل ۸۳۲ نشان می دهد، اگر حاصل این فرمول مقدار True باشد، در آن صورت تمام ردیف های بالای آن را به صورت متقابل بررسی نمودید.

۲۱) در اینجا، می توانید ستون هایی را که به آنها نیازی ندارید را حذف نمایید. مطابق شکل ۸۳۳، ستون های R,P,O,M,L,J,I,G,F,B و A را حذف نمایید.

نتیجه : اکنون نسخه جورکردنی، تصفیه پذیر و گزارش دادنی از مجموعه داده های اولیه در اختیار دارید. هررکورد متشکل از یک ردیف در اکسل می باشد.شکل ۸۳۴ را ملاحظه نمایید.

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

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

دستورات بحث شده : Data – Text to Columns

توابع بحث شده : =IF(); =AND(); =LEFT()

افزودن یک شماره مشتری به هر رکورد جزئی

مشکل : شما مجموعه دادهایی وارد کردید که شماره مشتری را یکبار در ستون A لیست می کند و آنگه هر شماره ای از رکورد های جزئیات صورت حساب را دارند. در انتهای مشتری اول، شماره مشتری بعدی را در ستون A قرار می دهند و سپس رکورد جزئیات آن مشتری را دارند. شکل ۸۳۵ را ملاحظه کنید. این یک قالب رایج برای داده است، اما در اکسل خیلی وحشتناک است. نمی توانید این مجموعه داده ها را مرتب کنید. لازم است اطلاعات مشتری را به هر رکورد وارد نمایید.

استراتژی :

۱) ستون های جدید A و B را وارد نمایید. عنوان های Acct و Customer را مانند شکل ۸۳۶ اضافه کنید.

در اینجا منطق اولیه آنچه اکسل می خواهد انجام دهد به زبان ساده ارائه شده است :

به ۴ کاراکتر اول ستون C نگاه کنید. اگر مساوی با “Acct” هستند، در نتیجه این ستون دارای اطلاعات مشتری است. داده ها را از آن سلول برداشته و آنها را به ستون A منتقل نمایید. در غیر اینصورت، چنانچه چهار کاراکتر اول چیزی غیر از “Acct” هستند، از همان اطلاعات صورت حساب از ستون A ردیف قبلی استفاده نمایید.

فرمول انجام این کار برای سلول A2 به صورت زیر است : =IF(LEFT(C2,4)=”Acct”,MID(C2,6,5),A1)

۲) این فرمول را در سلول A2 وارد نموده و آن را در ستون A تا پایین کپی نمایید.

همانگونه که شکل ۸۳۷ نشان می دهد، هنگامی که این فرمول را تا پایین کپی می کنید، این کار انجام می شود.  در سلول A2، تابع IF درست است و داده ها از C2 خارج می شوند. در سلول A3 ، تابع درست نیست، بنابراین از مقدار A2 استفاده می شود. در پایین در ستون A7، یک شماره مشتری جدید یافت می شود، بنابراین داده موجود در C7 برای A7 استفاده می شود. سلول های A8 تا A59 شماره مشتری را از A7 می گیرند.

منطق مشابهی برای ستون B مورد نیاز است. اما، در این مورد، باید نام مشتری را بگیرید. همانطور که می دانید واژه “Acct” و فضای خالی بعد از آن ۵ کاراکتر را به خود اختصاص می دهند. همچنین شماره حساب شما ۵ کاراکتر دیگری است و یک فضای خالی قبل از نام مشتری وجود دارد. بنابراین ۱۱ کاراکتر اول سلول C2 را نادیده می گیرید.

می توانید با استفاده از فرمول =MID(C2,12,50) از ۱۱ کاراکتر اول صرف نظر کرده و ۵۰ کاراکتر بعدی نام مشتری را بر گردانید. با این وجود، این فرمول فضاهای خالی به انتهای نام مشتری اضافه می کند تا شما حتما ۵۰ کاراکتر در نتیجه بدست آمده داشته باشید.  شما در حقیقت تمام آن فضاهای خالی انتهایی را نیاز ندارید. تابع =TRIM() فضاهای خالی قبل و بعد از متن را از بین می برد.

از =TRIM(MID(C2,12,50)) به عنوان فرمولی برای محاسبه نام مشتری کمک بگیرید. از این فرمول به عنوان بخش  درست تابع IF استفاده نمایید. همانگونه که در شکل ۸۳۸ نشان داده شده است، فرمول موجود در B2 که به تمام سلول های ستون B تا پایین کپی شده است ، به صورت زیر است :

=IF(LEFT(C2,4)=”Acct”,TRIM(MID(C2,12,50)),B1)

۳) این فرمول را به سلول B2 وارد نموده وآن را با کل ستون B کپی نمایید.

اکنون با موفقیت حساب و مشتری را جایگزین و پر کرده اید. اکنون باید این فرمول ها را به مقادیر تغییر دهید.

۴) ستون های A و B را به حالت انتخاب در آورید. کلید های Ctrl+C را برای کپی کردن فشار دهید.

Edit – Paste Values را برای تبدیل فرمول ها با اعداد انتخاب نمایید.

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

۵) عنوان موجود در D1 را انتخاب نمایید. دکمه مرتب سازی AZ را فشار دهید تا داده ها را به صورت نزویلی مرتب کند. هر ردیف بدون مقداری در ستون D به طور خودکار به انتهای مجموعه داده مرتب می شود. شکل ۸۳۹ را ملاحظه نمایید.

۶) از D1، کلید پایان و سپس کلید جهت پایین را دوبار فشار دهید. اشاره گر سلول در خانه عنوان اولین مشتری قرار می گیرد. تمام ردیف های واقع در زیر ردیف ۵۶۴ را حذف نمایید.

نتایج : مطابق شکل ۸۴۰، مجموعه داده تمیزی با اطلاعات مشتری در هر ردیف در اختیار دارید. می توانید این داده ها را مرتب نموده و یا در غیر این صورت از آن برای تحلیل داده ها استفاده نمایید.

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

خلاصه : تعدادی فرمول به همراه تابع IF به شما کمک می کنند تا این داده ها را به صورت شکل در آورید.

توابع بحث شده : =IF(); =LEFT(); =MID()