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

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

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

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

مقالات

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

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

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

هفته ۱۴

این هفته : نکاتی درباره متن. چگونگی پیوند متنی از دو ستون را با استفاده از کلید الحاق و سپس چگونگی استفاده از EFT(),MID(), FIND() و LEN() جهت جداسازی قسمت های یک شناسه حساب مطرح می‌شود.

چگونگی پیوند دو ستون متنی

مشکل : همانگونه که شکل ۲۵۷ نشان می‌دهد، شما داده هایی شامل نام کوچک در ستون A و نام خانواگی در ستون B در اختیار دارید. می‌خواهید این دو ستون را در یک ستون ادغام کنید.

استراتژی : در فرمولی در ستون C از علامت امرپسند (&) جهت الحاق استفاده نمایید. پیش از حذف ستون های A و B فرمول های ستون C را به مقادیر تبدیل کنید. گام های زیر را پیگیری نمایید.

۱) در سلول C2 فرمول =A2&B2 را مطابق شکل ۲۵۸ وارد نمایید.

۲) لازم است بین نام کوچک و نام خانوادگی یک فضای خالی درج نمایید. در صورتیکه سلول C2 را با یک فضای خالی به سلول B2 متصل کنید، پاسخ به نظر قابل قبول می‌رسد. این فرمول برابر است با :=A2&””&B2. این فرمول را در تمام سلول های پایین تر موجود در محدوده کپی نمایید.مطابق شکل ۲۵۹٫

جزئیات بیشتر : برای تبدیل BRITNEY SPEARS  به Britney Spears ، از تابع PROPER استفاده نمایید. فرمول =PROPER(A2&””&B2) اسامی‌را به حالت درست تبدیل می‌کند، مانند شکل ۲۶۰٫ این تابع برای تمام اسامی‌به استثنای اسامی‌دارای یک حرف بزرگ درونی مانند “Paul McCartney” یا “Dave VanHorn” قابل استفاده است. پس از استفاده از تابع PROPER باید به طور دستی هر نامی‌را با یک حرف بزرگ درونی اصلاح نمایید.

Gotcha : چنانچه ستون های A و B را هنگامی‌که ستون  C همچنان حاوی فرمول است را حذف کنید، آنگاه تمام فرمول ها به یک خطای #REF! تغییر می‌یابند.شکل ۲۶۱ را ملاحظه نمایید. این موضوع به شما میگوید که شما فرمولی در اختیار دارید که به سلول هایی اشاره می‌کند که دیگر در آنجا وجود ندارند. فورا از کلید های Ctrl+Z برای خنثی کردن عمل حذف کمک بگیرید.

برای کار درمورد این موقعیت، ابتدا تمام فرمول های ستون C را به مقادیر تبدیل کنید. مراحل زیر را پیگیری نمایید.

۱) داده های ستون C را انتخاب نمایید.

۲) Ctrl+C را جهت کپی داده ها به کلیپ برد به کار برید.

۳) بدون تغییر انتخاب، Edit – Paste Special … را انتخاب کنید.

۴) در کادر محاوره ای Paste Special گزینه  Values یا مقادیر را انتخاب نموده و سپس OK را کلیک کنید. مطابق شکل ۲۶۲٫

۵) این کار ستون C را از فرمول های زنده و پویا به مقادیر ثابت و استاتیک تبدیل می‌کند. اکنون می‌توانید ستون های A و B را حذف نمایید.

خلاصه : امپرسند (&) کاراکتر الحاق است که جهت الحاق سلول های  متنی با دیگر سلول های متنی  یا با مقادیر حرفی در یک فرمول به کار می‌رود.

دستورات بحث شده : Edit – Paste Special

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

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

چگونگی مرتب سازی بخشی از یک شناسه یا ID حساب

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

استراتژی : یک ستون درج کنید و از با کمک تابع LEFT ارقام اصلی را از فیلد حساب جدا نمایید.

۱) در ستون خالی، عنوانی مانند واژه “key” را وارد نمایید. در سلول G2 ، مطابق شکل ۲۶۴ فرمول =LEFT(A2,3) را وارد نمایید. این نشان می‌دهد که فیلد جدید باید تنها شامل سه کاراکتر سمت چپ فیلد شناسه باشد.

۲) دسته پر کنده سلول G2 را دابل کلیک نمایید تا این فرمول به تمام ردیف های پایین موجود در مجموعه داده کپی شود. دسته پر کننده نقطه ای مربع شکل به رنگ سیاه است که در گوشه پایین سمت راست سلول اشاره گر قرار دارد.

۳) فرمول های موجود در ستون G را به ارقام تبدیل کنید.

تمام سلول های ستون G را به حالت انتخاب در آورید. از Ctrl+C برای کپی نمودن استفاده کنید. همانگونه که در شکل ۲۶۵ نشان داده شده، از منو، Edit – Paste Special – Values – OK را انتخاب نمایید.

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

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

دستورات بحث شده : Edit – Paste Special

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

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

چگونگی جداسازی بخش مرکزی یک شناسه حساب

مشکل : شرکت شما شناسه حسابی با فرمت SSSS-XX-YYYY اختیار کرده است.شکل ۲۶۶ را ملاحظه نمایید. جهت انجام جمع زیر جزء و یا مرتب سازی داده ها لازم است بخش XX شناسه حساب را جداکنید.

استراتژی : ستون جدیدی درج نموده و با استفاده تابع MID ارقام اصلی را از فیلد حساب جدانمایید.

تابع MID دارای ۳ آرگومان است. اولین آرگومان سلولی حاوی یک مقدار متنی است. آرگومان دوم عدد کاراکتری است که می‌خواهید نتیجه از انجا شروع شود. آرگومان نهایی طول نتیجه است.

در یک شماره حساب با فرمت درست، شبیه ۱۲۳-۴۵-۶۷۸۹ می‌توانید پیش بینی کنید که شروع بخش دوم همیشه از مکان کاراکتر پنجم است. طول بخش دوم همیشه ۲ کاراکتر است.

در یک ستون خالی، عنوانی نظیر واژه “Key” وارد نمایید. در سلول G2 فرمول =MID(A2,5,2) را وارد نمایید. مطابق شکل ۲۶۷، این فرمول را به تمام ردیف ها کپی نمایید.

جزئیات بیشتر : جهت بدست اوردن ۴ رقم آخر شماره حساب، می‌توانید از یکی از توابع =MID(A2,8,4) یا =RIGHT(A2,4) استفاده نمایید تا ۴ رقم آخر شماره حساب را جدا کنید.

نتیجه : اکنون می‌توانید با استفاده از ستون جدید مرتب سازی را انجام دهید و زیر جمع ها را با این فیلد اضافه نمایید.

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

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

ارجاع متقابل : چگونگی مرتب سازی بخشی از یک شناسه حساب

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

مشکل : یک فروشنده کاربرگی از اکسل را ارائه می‌کند.همانگونه که شکل ۲۶۸ نشان می‌دهد، یک فیلد دارای یک کد ساخت، یک خط تیره، و یک شماره قطعه است. کدهای سازندگان همیشه به یک اندازه نیستند. باید کد سازنده را در یک ستون جدید جدا نمایید.

استراتژی : از تابع FIND برای قرار دادن خط تیره در شماره یک کالا استفاده کنید. این تابع مکان کاراکتر خط تیره را بازمی‌گرداند. می‌توانید نتیجه ی آن منهای ۱ را در تابع LEFT برای جداسازی کد سازنده به کار ببرید.

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

۱) فرمول =FIND(“-“,A2)  را در سلول F2 وارد نمایید. مانند شکل ۲۶۹ آن را به تمام سلول های پایین کپی نمایید.

شماره ۳ در سلول F2 نشان می‌دهد که علامت خط تیره در مکان کاراکتر سوم سلول A2 قرار دارد. عدد ۶ در سلول F3 نشان می‌دهد که خط تیره در مکان ششم سلول A3 قرار دارد. چنانچه بخواهید کد سازنده را جدا نمایید، باید یکی کمتر از این عدد از سمت چپ کد اختیار کنید.

۲)  در سلول G2، فرمول =LEFT(A2,F2-1) را مطابق شکل ۲۷۰ وارد نمایید. جهت کپی نمودن این فرمول به تمام سلول های پایین تر از دسته پر کننده استفاده نمایید.

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

استراتژی دیگر : نیازی نیست که فرمول ها را در دو ستون متفاوت وارد نمایید. به راحتی می‌توانستید از این فرمول در سلول F2 استفاده کنید : =LEFT(A2,FIND(“-“,A2)-1)

جزئیات بیشتر : زمانیکه فرمول کد سازنده را جدا کرد، فرمول ها را به مقادیر تغییر دهید. ارجاع متقابل را ببینید.

نتیجه : اکنون می‌توانید با این ستون جدید عمل مرتب سازی را انجام داده و جمع جزء ها را با این فیلد اضافه نمایید.

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

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

ارجاع متقابل : چگونگی مرتب سازی بخشی از یک شناسه حساب

چگونگی استفاده از توابع برای جداسازی تمام کاراکتر های بعد از خط تیره در یک ستون

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

استراتژی : از تابع MID برای استخراج بخشی از متن از وسط متن استفاده نمایید.این تابع به ۳ آرگومان نیاز دارد : (تعداد کاراکترها، شماره کاراکتر شروع، سلول متنی =MID(.می‌توانید از تابع FIND برای یافتن محل خط تیره در شماره کالا استفاده کنید و از کاراکتر بعدی از سمت راست آن شروع کنید. می‌توانید از تابع LEN جهت تشخیص طول متن کمک بگیرید.

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

۱) فرمول =FIND(“-“,A2) را در سلول F2 وارد نمایید. مانند شکل ۲۷۲ آن را به تمام سلول های پایین تر کپی نمایید.

عدد ۳ در سلول F2 نشان می‌دهد که خط تیره در مکان کاراکتر سوم سلول A2 قرار دارد. درصورتیکه می‌خواهید شماره قطعه را جدا نمایید، باید یک کاراکتر به سمت راست این مکان جابجا شده و سپس شروع نمایید. بنابراین، می‌دانید که شروع فرمول =MID(A2,F2+1) خواهد بود. تشخیص تعداد کاراکتر های موجود در شماره قطعه دشوار است.

۲) در سلول G2، فرمول =LEN(A2) را وارد نمایید. مانند شکل ۲۷۳ این فرمول را به تمام ردیف های پایین کپی نمایید.

تابع LEN تعدا کل کاراکتر های سلول A2 را به شما می‌گوید. سلول G2 به شما می‌گوید که ۱۰ کاراکتر در A2 وجود دارند. چنانچه خط تیره در مکان سوم قرار دارد،آنگاه شما کاراکتر های (G2-F2) یا (۳-۱۰)  را می‌گیرید. در نتیجه باید ۷ کارامتر را بگیرید.

۳) همانند شکل ۲۷۴، در سلول H2 فرمول =MID(A2,F2+1,G2-F2) را وارد نمایید. به زبان ساده، این به اکسل می‌گوید که کاراکتر ها را از A2 بیرون بکشد. اکسل باید از کاراکتر بعد از نتیجه در F2  شروع نماید و به اندازه (G2-F2) کاراکتر پیش رود.

در این حالت، وارد نمودن فرمول در یک ستون به جای سه ستون بسیار دشوار تر است. زیرا نتیجه تابع FIND دوبار در فرمول H2 استفاده می‌شود. برای ایجاد یک فرمول ساده، باید تابع FIND را دوبار در آن فرمول وارد نمایید. همانطور که  شکل ۲۷۵ نشان می‌دهد، می‌توانستید فرمول زیر را در H2 وارد نمایید : =MID(A2,FIND(“-“,A2)+1,LEN(A2)-FIND(“-“,A2)).

استراتژی دیگر : ممکن برای استفاده از فرمولی نظیر =MID(A2,F2+1,50) وسوسه شوید. پارامتر سوم، ۵۰، تضمین می‌کند که می‌توانید شماره کالایی با هر اندازه ممکن اختیار کنید. این ایده عملی خواهد بود، اما نتیجه حاصل،  ۴۴ جای خالی متصل شده به انتهای شماره قطعه خواهد بود. این کار استفاده موفقیت آمیز آن در فرمول VLOOKUP را غیر ممکن می‌سازد. با این وجود می‌توانید از فرمول =TRIM(MID(A2,F2+1,50)) جهت حذف فضاهای خالی بعد از ان استفاده نمایید.

خلاصه : از ترکیبی از توابع LEN و FIND استفاده نمایید تا به شما در تعیین مکان درست شروع و پایان توابع LEFT,MID و RIGHT کمک نماید.

توابع بحث شده : =FIND(); =MID(); =LEN(); =TRIM()

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

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

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

استراتژی : آرگومان سوم تابع FIND اختیاری است که می‌توانید از ان کمک بگیرید. این موضوع اکسل را متوجه مکان یک کاراکتر خاص در متن می‌نماید. در این حالت، جهت یافتن خط تیره دوم، از اکسل می‌خواهید که مکان خط تیره اول را بیابد.

۱) همانند مثال های پیشین، از =FIND(“-“,A2) در F2 جهت یافتن اولین خط تیره کمک بگیرید.

۲) همانند شکل ۲۷۷ فرمول =FIND(“-“,A2,F2+1) را در G2 وارد نمایید. پارامتر F2+1 به اکسل می‌گوید که شما می‌خواهید خط تیره ای در مکان کاراکتر چهارم سلول A2 پیدا کنید.

۳) فرمول =LEFT(A2,F2-1) را در H2 وارد نمایید. فرمول موجود در H2 مکان بخش اول شماره قطعه را تعیین می‌کند.

۴) تابع =MID(A2,F2+1,G2-F2) را در I2 وارد نمایید. فرمول موجود در I2 مکان بخش میانی شماره قطعه را مشخص می‌کند.

۵) برای بدست آوردن مکان بخش سمت راست شماره قطعه، می‌توانید از تابع RIGHT استفاده نمایید. همانند تابع LEFT ، تابع RIGHT به یک سلول و تعداد کاراکتر های سمت راست شماره کالا نیاز دارد. برای یافتن تعداد کاراکتر ها، از تابع =LEN(A2)-G2 استفاده نمایید.

فرمول حاصل،=RIGHT(A2,LEN(A2)-G2) را در J2 وارد نمایید. شکل ۲۷۸ را ملاحظه نمایید.

Gotcha : تمام این فرمول ها با این فرض کار می‌کنند که فروشنده همیشه از دو خط تیره در شماره کالا استفاده می‌کند. در صورتیکه شماره کالایی وجود داشته باشد که فاقد خط تیره دوم باشد، تابع FIND دوم خطای #VALUE! را باز می‌گرداند،که منجر به خطاهایی در محاسبه کالای دوم و سوم خواهد شد. پیش از تبدیل فرمول ها به مقادیر و حذف شماره قطعی ابتدایی، داده ها را با استفاده از ستون F مرتب نمایید و سپس آنها را توسط ستون G به صورت نزولی مرتب نمایید. همانگونه که در شکل ۲۷۹ نشان داده شده است، تمام خطاهای #VALUE! به بالای مجموعه داده مرتب می‌شوند، در این صورت شما می‌توانید خطاهای موجود در شماره قطعه را شناسایی و تصحیح نمایید.

خلاصه : با استفاده از ترکیبات FIND,LEN,MID,LEFT و RIGHT ،تقریبا هم داده قابل تصوری را می‌توان تجزیه کرد.

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

چگونگی تقسیم یک شماره قطعه به سه ستون

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

استراتژی : از دستور متن به ستون ها (Text To Columns) واقع در منوی Data جهت تجزیه شماره کالا استفاده نمایید. مراحل زیر را پیگیری نمایید.

۱) شماره کالا را در سمت راست داده های خود در ستون F کپی نمایید. دستور متن به ستون ها چندین ستون در سمت راست ستون اولیه را پر می‌نماید. از وجود تعداد زیادی از ستون های خالی اطمینان حاصل کنید.

۲) بقیه محدوده داده ها در ستون G را انتخاب نمایید. اشاره گر سلول را در G2 قرار دهید. کلید پایان را فشار دهید. همراه با پایین نگه داشتن کلید Shift ، کلید جهت پایین برای انتخاب کل محدوده فشار دهید.

۳) از منو، Data – Text To Columns را انتخاب نمایید. کادر محاوره ای Wizard بر روی داده ای که محدوده آن تعیین شده و یا داده ای که پهنای ثابتی برای هر بخش دارد کار می‌کند. محدوده داده ها با یک خط تیره تعیین می‌شود. همانگونه که شکل ۲۸۱ نشان می‌دهد، در مرحله ۱، دکمه رادیو در تنظیمات Delimited را رها کنید. دکمه Next را کلیک نمایید.

به طور پیش فرض، مرحله ۲ فرض می‌کند که داده ها با یک Tab از هم جدا شده اند. همانگونه که در شکل ۲۸۲ ملاحظه می‌کنید، گزینه های دیگر استفاده از کاما، فضای خالی و سمی‌کالن است. توجه کنید که خط تیره در لیست وجود ندارد.

۴) علامت جعبه Tab را بردارید. جعبه Other را انتخاب نمایید. در جعبه متن دیگر یک خط تیره وارد نمایید. مطابق شکل ۲۸۳، پنجره پیش نمایش داده ها ، داده ها را در ۳ ستون نشان می‌دهد. کلید Next را فشار دهید.

۵) در مرحله ۳، به طور اختیاری می‌توانید نوع داده ستون ها را تعیین کنید. نوع General  مناسب است، مگر اینکه داده هایی در اختیار داشته باشید. توجه داشته باشید، چنانچه بخواهید صفرهای جداکننده را نگه در بخش دوم شماره کالا نگه دارید، باید عنوان آن فیلد را انتخاب نموده و از General to Text تغییر دهید. شکل ۲۸۴ را مشاهده نمایید. کلید Finish را برای خاتمه کار فشار دهید.

نتیجه : ستون اولیه F با بخش اول نتیجه دوباره نویسی شده است. ستون های جدید G و H مطابق شکل ۲۸۵، شامل بخش های دوم و سوم شماره کالا می‌شود.

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

 Gotcha: بی نظمی‌عجیبی برای باقیمانده این بخش از اکسل ظاهر خواهد شد. در صورتیکه بعدا فایل Notepad را بازنمایید، داده های شامل خط تیره را کپی نموده و بخواهید که آنها را در اکسل قرار دهید (Paste) ، اکسل به طور خودکار داده ها را از محل وجود خطوط تیره به ستون های جداگانه ای تجزیه می‌کند، شکل های ۲۸۶ و ۲۸۷ را مشاهده نمایید. چنانچه انتظار آن را داشته باشید، این موضوع می‌تواند ویژگی ساده ای به نظر برسد و در صورتیکه این گونه نباشد، می‌تواند به یک موقعیت بسیار گیج کننده ای باشد.

خلاصه : چنانچه داده های شما با جدا کننده های ثابتی ایجاد شده باشد، کادر Text to Column Wizard راحتی بسیار ساده برای تجزیه داده ها به شمار می‌اید.

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