آموزش کار با اکسل – معرفی ۱۸ ترفند ساده در Excel
تنها کاری که باید انجام دهید وارد کردن یک فرمول به برنامه اکسل است تا هرچیزی که نیاز به حساب و کتاب دستی دارد به صورت خودکار انجام شود. می‌خواهید دو صفحه با اطلاعات مشابه را با هم ترکیب کنید؟ اکسل این کار را انجام می‌دهد. می‌توانید از ویژگی‌ها و فرمول‌های اکسل و همچنین ابزارهای آن برای فهمیدن داده‌های خود استفاده کنید. برای مثال می‌توانید از یک صفحه گسترده (spreadsheet) برای بررسی داده‌ها استفاده کرده و میانگین و مجموع کل آنها را به صورت خودکار ببینید. اگر با موقعیتی مواجه شوید که باید داده‌ها را به صورت دستی به روز رسانی کنید، ممکن است فرصت استفاده از فرمولی که بتواند به صورت خودکار این عملیات را انجام دهد در اختیار نداشته باشید. اگر تازه آموزش کار با برنامه‌ی اکسل را شروع کرده‌اید، در فهرست زیر چند دستور اولیه را مشاهده می‌کنید که پیشنهاد می‌کنیم با آنها آشنا شوید: این جدول‌ها داده‌ها را تغییر نمی‌دهند اما می‌توانند خلاصه‌ای از مقدارها ارائه کرده و اطلاعات متفاوت در صفحه گسترده را بر اساس کاری که می‌خواهید با این اطلاعات انجام شود باهم مقایسه کنند. ممکن است فکر کنید داده‌های چندانی در اختیار نداریم اما برای مجموعه داده‌های بزرگتر این ویژگی مفیدتر خواهد بود. : این گزینه باعث می‌شود که شما به اطلاعات خود متفاوت نگاه کنید. به جای نوشتن همه‌ی مقدارهای عددی، می‌توانید این داده‌ها را جمع کنید، میانگین بگیرید، بیشترین و کمترین مقدار را بدست آورید، اعداد را بشمارید و چند کار دیگر با داده‌های خود انجام دهید. برای اضافه کردن چند سطر یا ستون به صفحه گسترده، به تعداد موردنظر سطر یا ستون‌هایی که اکنون وجود دارند را هایلایت کنید. برای انجام این کار، سه سطر را هایلایت کرده و سپس insert را انتخاب می‌کنیم. در اکسل، می‌توان فیلتر را به هر یک از ستون‌های داده اضافه کرد و با استفاده از آن می‌توانید انتخاب کنید که در آن زمان می‌خواهید کدام سلول‌ها را ببینید. زمانی که از یک فیلتر برای تحلیل بیشتر در صفحه گسترده‌ی دیگری استفاده می‌کنید، مقادیر را در صفحه گسترده اصلی کپی پیست کنید. برای حذف داده‌های تکراری، سطر یا ستونی که می‌خواهید داده‌های مشابه آن حذف شوند را انتخاب کرده و سپس به منوی Data رفته و گزینه «Remove Duplicates» را انتخاب کنید (پایین Tools). همچنین می‌توانید از این ویژگی برای حذف کل سطر بر اساس مقدار ستون تکرار شده استفاده کنید. بنابرین اگر شما سه سطر داده از اطلاعات هری پاتر دارید و تنها یکی از آنها را می‌خواهید ببینید، می‌توانید کل داده‌ها را انتخاب کرده و سپس داده‌های تکراری را بر اساس email حذف کنید. وقتی سطرهای کوتاهی از داده‌ها را در صفحه گسترده خود دارید ممکن است بخواهید آیتم‌های موجود در یکی از سطرها را به ستون تبدیل کنید (یا برعکس). کپی پیست کردن تک تک سطرها ممکن است بسیار وقت گیر باشد اما ویژگی Transpose به شما این امکان را می‌دهد که به راحتی داده‌های سطرها را به ستون‌ها انتقال دهید. سپس سلول‌هایی که می‌خواهید اولین سطر یا ستون از آنجا شروع شود را انتخاب کنید. بر روی سلول کلیک راست کرده و سپس گزینه «paste special» را انتخاب کنید. ۷. از این فرمول‌ها برای انجام محاسبات ساده استفاده کنید. برای عملیات تقسیم از علامت / استفاده کنید. ۹. از قالب‌بندی‌های شرطی برای تغییر رنگ خودکار سلول‌ها بر اساس داده استفاده کنید. برای مثال اگر می‌خواهید چند عدد را که بالاتر از میانگین یا بالاتر از ده درصد داده‌های صفحه گسترده هستند را علامت بزنید، می‌توانید اینکار را انجام دهید. برای شروع، گروهی از سلول‌هایی که می‌خواهید قالب‌بندی شرطی بر روی آنها انجام شود را انتخاب کنید. به جای آنکه کنار اسم هر دانش آموز گریفیندور عدد ۱۰ را تایپ کنیم، می‌توانیم از فرمول IF THEN اکسل استفاده کنیم تا بگوییم اگر ( این متغیر چیزی است که می‌خواهیم اگر شرط برقرار بود در سلول نمایش داده شود. : این متغیر همان مقداری است که می‌خواهیم اگر شرط برقرار نبود در سلول نشان داده شود. در این مثال، می‌خواهیم برای دانش‌آموزان دیگری که در گریفیندور نیستند مقدار ۰ در سلول وارد شود تا نمایانگر صفر امتیاز باشد. اگر می‌خواهید نتیجه به جای عدد به صورت متن باشد از علامت نقل قول استفاده کنید. اگر می‌خواهید نتیجه به جای عدد به صورت متن باشد از علامت نقل قول استفاده کنید. می‌توانید این کار را با نوشتن فرمول در سلولی که مرجع مطلق نام دارد انجام دهید. برای وارد کردن اطلاعات از یک کاربرگ به کاربرگ دیگر استفاده کنید. بیشتر اوقات شما نیاز خواهید داشت تا چند مجموعه از اطلاعات را باهم ترکیب کنید و در این زمان‌ها تابع VLOOKUP فرمول خوبی است. هرچند پیش از آنکه از این فرمول استفاده کنید، کاملا مطمئن شوید که حداقل یک ستون وجود دارد که در دو صفحه گسترده مشابه است. مجموعه داده‌ها را به دقت بررسی کنید تا از یکسان بودن ستون‌هایی که در ترکیب کردن اطلاعات از آنها استفاده می‌کنید مطمئن شوید. با توجه به مثال ما، فرمول تابع به این صورت خواهد شد: : این متغیر همان مقدار مشابهی است که در هر دو صفحه گسترده وجود دارد. : این متغیر محدوده‌ی ستون‌ها در کاربرگ ۲ است که می‌خواهید اطلاعات از آن استخراج شود. این محدوده شامل ستون دادهی مشابه برای lookup value (در این مثال آدرس‌های ایمیل) در کاربرگ ۱ و ستون داده‌ای که می‌خواهید در کاربرگ ۱ کپی گردد نیز می‌شود. A به معنی ستون A در کاربرگ ۲ است، یعنی همان ستون در کاربرگ ۲ است جایی که دادهی یکسان برای lookup value (آدرس ایمیل) در کاربرگ ۱ فهرست می‌شود. : این متغیر به اکسل می‌گوید که داده‌هایی که می‌خواهید در کاربرگ ۱ کپی شوند در کدام ستون قرار دارند (محدوده‌ی ستون‌هایی که مشحص کرده‌اید). برای مثال اگر در کاربرگ ۲ سه ستون وجود داشته باشد- شامل Email، Age و House – و شما همچنان بخواهید House را به کاربرگ ۱ انتقال دهید، باز هم می‌توانید از VLOOKUP استفاده کنید. فقط کافی است مقدار متغیر را از ۲ به ۳ تغییر دهید تا مقادیر ستون سوم استخراج شوند: =VLOOKUP (C۲: Sheet۲! : برای این متغیر از FALSE استفاده کنید تا مطمئن شوید که فقط مقادیر دقیق موجود استخراج می‌شوند. این ویژگی باعث بوجود آمدن محدودیت‌هایی می‌شود و علت آنکه افراد ترجیح می‌دهند بجای این تابع از توابع INDEX و MATCH استفاده کنید وجود همین محدودیت‌هاست. برای انتقال اطلاعات میان ستون‌ها به شکل افقی استفاده کنید. اگر با مجموعه داده‌های کلان کار می‌کنید که نیاز به هزاران جستجو دارند، استفاده از تابع INDEX MATCH به شکل کاملا قابل توجهی سرعت بارگذاری اطلاعات در اکسل را کاهش می‌دهند. بنابراین اگر بخواهیم اطلاعات موجود در کاربرگ ۱ و کاربرگ ۲ را در کاربرگ ۱ ادغام کنیم، اما مقدار ستون‌ها در کاربرگ ۱ و کاربرگ ۲ مشابه نباشند، برای کار با VLOOKUP، مجبور خواهیم بود ستون‌ها را جابجا کنیم. در این حالت، بهتر است از تابع INDEX MATCH استفاده شود. (برای آنهایی که طرفدار هری پاتر نیستند باید بگوییم که هر جادوگر یک سپر مدافع مرتبط با اسم خود به نام پاترونوس دارد.) اطلاعات موجود در هر دو کاربرگ ستونی است که دربردارنده‌ی آدرس ایمیل‌ها است، اما این ستون آدرس ایمیل در هر کاربرگ شماره ستون متفاوتی دارد. در این حالت بهتر است بجای VLOOKUP از فرمول INDEX MATCH استفاده کنیم تا مجبور نشویم ستون‌ها را جابجا کنیم. زمانی که مقادیر متغیرها را بدست آوردید، فرمول INDEX MATCH را در بالاترین سلول از ستون خالی پاترونوس در کاربرگ ۱ تایپ کنید، یعنی جایی که می‌خواهید اطلاعات ادغام شده قرار بگیرند. بجای آنکه خودتان حساب کنید تعداد دفعات تکرار مقادیر یا اعداد مشخصی چقدر است، اجازه دهید اکسل این کار را برایتان انجام دهد. اکسل می‌تواند با استفاده از تابع Function تعداد دفعاتی را محاسبه کند که یک کلمه یا عدد در محدوده‌ای از سلول‌ها تکرار می‌شود. فرمول کلی تابع به این صورت است: : این متغیر همان عدد یا متنی است که می‌خواهیم اکسل تعداد تکرار آن را بشمارد. اگر می‌خواهید که نتیجه بجای آنکه به صورت عدد نمایش داده شود، متن باشد از علامت نقل قول استفاده کنید. تنها کافی است فرمول COUNTIF را در یک سلول تایپ کرده و کلید Enter را بزنید تا تعداد دفعات تکرار کلمه‌ی Gryffindor در مجموعه‌ی داده نمایش داده شود. برای مثال، یک مجموعه داده ممکن است بجای آنکه اطلاعات مربوط به نام کامل افراد را دربربگیرد، ابتدا نام کوچک و سپس نام خانوادگی فرد را در ستون‌های جداگانه نمایش دهد. در اکسل، شما می‌توانید با استفاده از علامت ; در تابع سلول‌هایی با دادهی متفاوت را در یک سلول ادغام کنید. برای انجام این کار، مکان‌نما را در یک سلول خالی می‌گذاریم، یعنی جایی که می‌خواهیم نام کامل آنجا نمایش داده شود. اگر بخواهید این عملیات را برای چند سطر انجام دهید، کافی است گوشه‌ی اولین سلول را گرفته و آنطور که در مثال زیر نشان داده می‌شود آن را به سمت پایین بکشید. اگر از صفحه گسترده‌ی اکسل برای بررسی دادهی مشتریان استفاده می‌کنید و می‌خواهید به داده‌ای دست پیدا کنید که قابل سنجش نیست، می‌توانید چک باکس را در ستون وارد کنید. برای مثال، اگر از صفحه گسترده‌ی اکسل برای مدیریت مشتری‌های احتمالی خود استفاده می‌کنید و می‌خواهید بدانید که آیا در طی سه ماه اخیر با آنها تماس گرفته شده است یا خیر، می‌توانید ستون «called this quarter?» (یا «تماسی در طی سه ماه اخیر گرفته شده؟») داشته باشید و زمانی که با مشتری احتمالی تماس گرفته شد سلول‌های درون آن تیک می‌خورند. سپس سلول‌های دیگری که می‌خواهید چک‌باکس در آنها نیز وجود داشته باشد را هایلایت کرده و باکس کپی‌شده را پیست کنید. اگر یک آدرس URL را مستقیما به اکسل اضافه کنید، به صورت خودکار به لینکی تبدیل می‌شود که می‌توان بر روی آن کلیک کرد. اما اگر بخواهید میان کلمات مانند عنوان صفحه یا تیتر پست مورد بررسی هایپرلینک بسازید، روش کار به این صورت خواهد بود. اگر به هر دلیلی نتوانستید از کلیدهای میانبر استفاده کنید، می‌توانید این کار را با هایلایت کردن سلول و سپس کلیک بر روی Insert و Hyperlink انجام دهید. بجای آنکه کلمات پر تکرار- مانند «Yes» و «No» - را هربار در کاربرگ خود بنویسید، می‌توانید از منوهای کشویی (drop-down) استفاده کنید تا عبارات توصیفی درباره‌ی مشتریان یا هر آنچه که بررسی می‌کنید را سریعا انتخاب کنید. روش اضافه کردن منوهای کشویی به سلول‌ها به این صورت است: فیلم آموزش کار با اکسل (فرمول نویسی و استفاده از توابع Excel) در صورتی که این آموزش کار با اکسل برای شما مفید بوده است:
متن کامل نوشته در سایت فرانش
نظرات