آموزش کار با اکسل با ۱۸ ترفند کاربردی در Excel

در آموزش کار با اکسل به نکات و ترفندهای ساده و کاربردی در Excel به صورت تصویری میپردازیم و در آخر با فیلم آموزش فرمول نویسی و تابع نویسی در اکسل سعی داریم تا شما را گام به گام به یادگیری اکسل حرفه ای نزدیک کنیم.

گاهی اوقات به نظر می‌رسد اکسل ‌بیش از حد خوب و کارآمد است. تنها کاری که باید انجام دهید وارد کردن یک فرمول به برنامه اکسل است تا هرچیزی که نیاز به حساب و کتاب دستی دارد به صورت خودکار انجام ‌شود. می‌خواهید دو صفحه با اطلاعات مشابه را با هم ترکیب کنید؟ اکسل این کار را انجام می‌دهد. به محاسبات ساده‌ی ریاضی نیاز دارید؟ اکسل این کار را انجام می‌دهد. میخواهید اطلاعاتتان را در چند سلول ادغام کنید؟ این کار را نیز به اکسل بسپارید.

اکسل در چه مواردی کاربرد دارد؟

اکسل یک برنامه‌ی سودمند است که می‌تواند بسیاری از داده‌ها را ذخیره و سازماندهی کند.
می‌توانید از ویژگی‌ها و فرمول‌های اکسل و همچنین ابزارهای آن برای فهمیدن داده‌های خود استفاده کنید.
برای مثال می‌توانید از یک صفحه گسترده (spreadsheet) برای بررسی داده‌ها استفاده کرده و میانگین‌ و مجموع  کل آنها را به صورت خودکار ببینید.

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

برای کارآیی موثرتر و اجتناب از انجام کارهای دستی خسته کننده، در این مقاله چند تکنیک اکسل را گردآوری کرده‌ایم که شما را برای کار با این نرم‌افزار آماده می‌کند. (و به همه‌ی طرفداران هری پاتر پیشاپیش خوش آمد می‌گوییم.)

مقدمات آموزش کار با اکسل

اگر تازه آموزش کار با برنامه‌ی اکسل را شروع کرده‌اید، در فهرست زیر چند دستور اولیه را مشاهده می‌کنید که پیشنهاد می‌کنیم با آنها آشنا شوید:

• ایجاد یک صفحه گسترده یا spreadsheet جدید
• انجام عملیات محاسباتی اصلی مثل جمع، تفریق، ضرب و تقسیم در صفحه گسترده
• نوشتن و قالب‌بندی متن سلول و عنوان‌ها.
• ویژگی تکثیر اتوماتیک در اکسل
• حذف یا اضافه کردن یک ستون، ردیف‌ و صفحه گسترده‌. در ادامه، نحوه‌ی اضافه کردن مواردی مثل چند ستون‌‌ و ردیف‌ را توضیح خواهیم داد.
• فعال کردن قابلیت مشاهده‌ی عنوان ستون‌ها و سطر‌ها زمانی که در گزاره صفحه آن‌ها را مرور می‌کنید. با انجام این کار هرچه جلوتر بروید می‌دانید چه اطلاعاتی را وارد کرده‌اید.

اگر اطلاعات کمی درباره Excel دارید و به دنبال آموزش اکسل مقدماتی هستید

پشنهاد میکنیم آموزش اکسل ۲۰۱۹ رایگان مقدماتی – برای مبتدیان را مشاهده نمایید

ترفندها و تکنیک‌های اکسل

۱.استفاده از  Pivot Tables (جداول پاشنه‌ای) برای شناسایی و درک اطلاعات

جدول پاشنه‌ای برای شناسایی داده‌ها در صفحه گسترده به کار می‌رود. این جدول‌ها داده‌ها را تغییر نمی‌دهند اما می‌توانند خلاصه‌ای از مقدارها ارائه کرده و اطلاعات متفاوت در صفحه گسترده را بر اساس کاری که می‌خواهید با این اطلاعات انجام شود باهم مقایسه کنند.

اجازه دهید مثالی را بررسی کنیم. فرض کنید می‌خواهید بدانید در هر خانه در مدرسه هاگوارتز چند نفر زندگی می‌کنند. ممکن است فکر کنید داده‌های چندانی در اختیار نداریم اما برای مجموعه داده‌های بزرگتر این ویژگی مفیدتر خواهد بود.

برای ساختن یک جدول پاشنه‌ای، ابتدا به منوی Data و سپس Pivot Table می‌رویم. اکسل به طور خودکار جدول پاشنه‌ای را برایتان ایجاد می‌کند اما همیشه می‌توانید ترتیب قرارگیری اطلاعات را تغییر دهید. پس چهار گزینه برای انتخاب دارید.

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

Column Labels: این گزینه برای ایجاد هدر در مجموعه داده به کار می‌رود.

Row Labels: شامل سطرهای مجموعه داده‌ها است. لیبل سطر و ستون‌ها می‌تواند شامل داده‌های ستون‌ها باشد. برای مثال، نام کوچک می‌تواند در لیبل ستون یا سطر کشیده شود. این موضوع به این بستگی دارد که شما می‌خواهید داده‌های خود را چگونه ببینید.

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

از آنجایی که می‌خواهیم تعداد دانش آموزان در هر خانه را بشماریم، به جدول پاشنه‌ای رفته و ستون House را به سمت Row Labels و Values بکشید. با انجام این کار، مجموع تعداد دانش آموزان در هر خانه را خواهید دید.

۲. اضافه کردن چند سطر یا ستون جدید

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

برای اضافه کردن چند سطر یا ستون‌ به صفحه گسترده، به تعداد موردنظر سطر یا ستون‌هایی که اکنون وجود دارند را هایلایت کنید. سپس کلیک راست کرده و گزینه Insert را بزنید.

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

۳. استفاده از فیلتر برای ساده‌سازی داده‌ها

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

بیایید به مثال زیر نگاهی بیندازیم. با کلیک کردن روی زبانه‌ی Data گزینه‌ی Filter را انتخاب کنید. روی پیکان کنار عنوان ستون‌ها کلیک کنید. حالا می‌توانید انتخاب کنید که اطلاعات شما به صورت صعودی مرتب شوند یا نزولی. علاوه براین می‌توانید تعیین کنید که سطر خاصی نمایش داده شود.

فرض کنیم در مثال ما که درباره‌ی هری پاتر است، فقط می‌خواهید دانش‌آموزان گرینفیدور را ببینید. با انتخاب فیلتر Gryffindor بقیه سطرها ناپدید می‌شوند.

ترفند: زمانی که از یک فیلتر برای تحلیل بیشتر در صفحه گسترده‌ی دیگری استفاده می‌کنید، مقادیر را در صفحه گسترده اصلی کپی پیست کنید.

۴. حذف مجموعه یا نقاط داده‌ی تکراری

هرچه حجم داده‌ها بیشتر باشد احتمال تکراری بودن محتوا بیشتر می‌شود. ممکن است شما لیستی از چند تماس با یک شرکت را داشته باشید و فقط بخواهید تعداد شرکت‌هایی که دارید را ببینید. در چنین موقعیت‌هایی، حذف داده‌ی تکرار شده کاملا مفید خواهد بود. برای حذف داده‌های تکراری، سطر یا ستونی که می‌خواهید داده‌های مشابه آن حذف شوند را انتخاب کرده و سپس به منوی Data رفته و گزینه “Remove Duplicates” را انتخاب کنید( پایین Tools). پنجره‌ای نمایش داده می‌شود که باید نوع داده‌ای که می‌خواهید با آن کار کنید را انتخاب کنید. گزینه “Remove Duplicates” را انتخاب کنید.

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

دوره‌های مرتبط در دانشیار آی تی

۵. انتقال دادن سطرها به ستون‌ها

وقتی سطرهای کوتاهی از داده‌ها را در صفحه گسترده خود دارید ممکن است بخواهید آیتم‌های موجود در یکی از سطرها را به ستون تبدیل کنید( یا برعکس).
کپی پیست کردن تک تک سطرها ممکن است بسیار وقت گیر باشد اما ویژگی Transpose به شما این امکان را می‌دهد که به راحتی داده‌های سطرها را به ستون‌ها انتقال دهید.

با انتخاب ستونی که می‌خواهید به سطرها انتقال دهید شروع کنید. کلیک راست کرده و گزینه‌ی Copy را انتخاب کنید.
سپس سلول‌هایی که می‌خواهید اولین سطر یا ستون از آنجا شروع شود را انتخاب کنید. بر روی سلول کلیک راست کرده و سپس گزینه”paste special” را انتخاب کنید.
یک ماژول نشان داده خواهد شد. در قسمت پایین، گزینه‌ای برای انتقال مشاهده می‌کنید. تیک آن گزینه را فعال کرده و سپس Ok را انتخاب کنید. اکنون ستون شما به یک سطر تبدیل می‌شود یا برعکس.

۶. تقسیم اطلاعات متنی در میان ستون‌ها

اگر بخواهید اطلاعاتی که در یک سلول وجود دارد را در میان دو سلول متمایز تقسیم کنید چه می‌کنید؟
فرض کنید می‌خواهید اسم کمپانی شخصی را از آدرس ایمیل او استخراج کنید یا می‌خواهید نام کامل فردی را از نام کوچک و نام خانوادگی در ایمیل‌های بازاریابی خود جدا کنید.

خوشبختانه با اکسل هر دو این کارها امکان‌پذیر است. در ابتدا ستونی که می‌خواهید قسمت شود را انتخاب کنید. سپس به منوی Data  رفته و گزینه “text to Columns” را انتخاب کنید. یک ماژول با اطلاعات اضافه ظاهر می‌شود.

نخست باید انتخاب کنید که به “Delimited” نیاز دارید یا “Fixed Width” .

• “Delimited” به این معناست که می‌خواهید ستون بر اساس کاراکترهایی مانند ویرگول، فاصله یا تب شکسته شود.
• “Fixed Width” به این معناست که می‌خواهید مکان دقیق تمام ستون‌هایی که تقسیم می‌شوند را انتخاب کنید.

در مورد مثال زیر، با انتخاب گزینه “Delimited” می‌توانیم نام کامل فرد را به دو قسمت نام کوچک و نام خانوادگی تقسیم کنیم.

اکنون باید Delimiterها را انتخاب کنیم. Delimiter یا جداساز ممکن است تب، نقطه‌ویرگول، فاصله یا هر علامت دیگری باشد.(برای مثال علامت”@” در ایمیل‌ها).
در این مثال، ما فاصله را انتخاب می‌کنیم. سپس اکسل به شما پیش‌نمایشی از وضعیت ستون‌های جدید ارائه خواهد کرد.

اگر از نتیجه‌ی کاری که انجام شده راضی بودید، گزینه “Next” را انتخاب کنید. این صفجه امکان استفاده از گزینه‌ی Advanced Formats را نیز به شما می‌دهد. وقتی کارتان تمام شد، بر روی “Finish” کلیک کنید.

 

فرمول‌های اکسل

۷. از این فرمول‌ها برای انجام محاسبات ساده استفاده کنید.

اکسل علاوه بر انجام عملیات فوق‌العاده پیچیده، می‌تواند به شما در انجام کارهای محاسباتی ساده مثل جمع، تفریق، ضرب یا تقسیم هر داده‌ای کمک کند.

• برای عملیات جمع از علامت + استفاده کنید.
• برای عملیات تفریق از علامت – استفاده کنید.
• برای عملیات ضرب از علامت * استفاده کنید.
• برای عملیات تقسیم از علامت / استفاده کنید.

از پرانتزها جهت اطمینان از تقدم عملیات استفاده کنید. در مثال زیر، (۱۰*۱۰+ ۱۰)، ۱۰ دوم و سوم قبل از جمع شده با ۱۰ دیگر در هم ضرب می‌شوند.
ولی اگر آن را به این صورت بنویسیم ۱۰*(۱۰+۱۰)، ابتدا ۱۰ اول و دوم با هم جمع می‌شوند.

۸. میانگین اعداد سلول‌ها را بدست آورید.

اگر می‌خواهید میانگین مجمموعه‌ای از اعداد را بدست آورید از فرمول (=AVERAGE(Cell Range استفاده کنید. اگر میخواهید ستونی از اعداد را با هم جمع کنید از فرمول (=SUM(Cell Range استفاده کنید.

 

۹. از قالب‌بندی‌های شرطی برای تغییر رنگ خودکار سلول‌ها بر اساس داده استفاده کنید.

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

برای شروع، گروهی از سلول‌هایی که می‌خواهید قالب‌بندی شرطی بر روی آنها انجام شود را انتخاب کنید.
سپس “Choose Conditional” را از منوی “Home” انتخاب کرده و از منوی کشویی حالت موردنظر خود را انتخاب کنید( همچنین اگر به الگوی دیگری نیاز دارید می‌توانید مدل دلخواه خود را ایجاد کنید).
پنجره‌ای ظاهر خواهد شد که از شما می‌خواهد اطلاعات بیشتری برای مدل دلخواه خود ارائه دهید. وقتی اطلاعات را وارد کردید “OK” را بزنید. حال نتیجه به صورت خودکار ظاهر می‌شود.

۱۰. استفاده از فرمول IF THEN برای اتوماتیک کردن فرمول‌های خاص در اکسل

گاهی اوقات نمی‌خواهیم تعداد دفعاتی که یک مقدار وجود دارد را محاسبه کنیم. در عوض می‌خواهیم اطلاعات دیگری را در یک سلول وارد کنیم اگر سلولی متناظر با آن اطلاعات وجود دارد.

برای مثال، در موقعیت زیر، می‌خواهیم ۱۰ امتیاز به صاحبان خانه‌های گریفیندور بدهیم. به جای آنکه کنار اسم هر دانش آموز گریفیندور عدد ۱۰ را تایپ کنیم، می‌توانیم از فرمول IF THEN اکسل استفاده کنیم تا بگوییم اگر (IF) دانش‌آموزی در گریفیندور است، پس (THEN) ۱۰ امتیاز به او تعلق می‌گیرد. فرمول کلی به این صورت است:

(IF(logical_test, value_if_true, value of false

در مثال ما فرمول به این صورت است:

(“=IF(D2=”Gryffindor”,”۱۰″,”۰

به طور کلی فرمول به صورت (IF(Logical Test, value of true, value of false است. بیایید هر متغیر را بررسی کنیم.

Logical_Test: logical_test همان قسمت IF یا اگر در عبارت است. در مثال ما، این متغیر برابر است با D2=”Gryffindor” چون ما می‌خواهیم مطمئن شویم که سلول متناظر با دانش‌آموزان “Gryffindor” است. به یاد داشته باشید که حتما Gryffindor را درون علامت نقل قول قرار دهید.

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

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

تذکر: در مثال بالا، به هرکس در گریفیندور ۱۰ امتیاز داده شد. اگر بخواهیم بعدا مجموع امتیازات را بدست آوریم، نمی‌توانیم این کار را انجام دهیم چون اعداد ۱۰ در نقل قول قرار گرفته‌اند. بنابراین با تبدیل کردن آنها به متن، نه عدد، اکسل می‌تواند آنها را جمع کند.

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

آیا تا کنون علامت دلار ($) را در فرمول‌های اکسل دیده‌اید؟ زمانی که از این علامت در یک فرمول استفاده می‌شود نشان‌دهنده‌ی دلار آمریکا نیست؛ بلکه از طریق این علامت مطمئن می‌شویم که سطر و ستون ثابت نگه داشته می‌شوند حتی اگر همان فرمول را در سطرهای مجاور کپی کنیم.

مشاهده می‌کنید که یک سلول مرجع – زمانی که برای مثال از سلول C5 به سلول A5 ارجاع می‌دهید- به صورت پیشفرض نسبی است. در این حالت، در واقع به سلولی ارجاع می‌دهید که در سمت چپ و در سطر مشابه (۵) پنج ستون (C منهای A) دارد. به این فرمول، فرمول نسبی گفته می‌شود. زمانی که یک فرمول نسبی را از سلولی به سلول دیگر کپی می‌کنید، مقادیر موجود در فرمول بر اساس جایی که کپی شده‌اند تغییر می‌کند. اما گاهی اوقات می‌خواهیم مقادیر فرمول بدون در نظر گرفتن اینکه در کجا کپی می‌شوند ثابت و بدون تغییر باقی بمانند. می‌توانید این کار را با نوشتن فرمول در سلولی که مرجع مطلق نام دارد انجام دهید.

برای تبدیل کردن فرمول نسبی (=A5+C5) به فرمول مطلق، قبل از مقادیر سطر و ستون علامت دلار قرار می‌دهیم. یعنی به این صورت: (=$A$5+$C$5)

دوره‌های مرتبط در دانشیار آی تی

توابع اکسل

۱۲. از تابع VLOOKUP برای وارد کردن اطلاعات از یک کاربرگ به کاربرگ دیگر استفاده کنید.

آیا تا به حال دو مجموعه داده در دو صفحه گسترده‌ی متفاوت داشته‌اید که بخواهید آنها را در یک صفحه گسترده ترکیب کنید؟

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

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

فرمول تابع:

([VLOOKUP(lookup value, table array, column number, [range lookup

با توجه به مثال ما، فرمول تابع به این صورت خواهد شد:

(=VLOOKUP(C2,Sheet2!A:B,2,FALSE

در این فرمول چندین متغیر وجود دارد. زمانی که بخواهید اطلاعات موجود در کاربرگ ۱ و کاربرگ ۲ را در کاربرگ ۱ ادغام کنید، حالت زیر پیش می‌آید:

• Lookup Value: این متغیر همان مقدار مشابهی است که در هر دو صفحه گسترده وجود دارد. اولین مقدار در اولین کاربرگ را انتخاب کنید. در این مثال، اولین آدرس ایمیل در فهرست، یعنی سلول ۲ یا C2، را وارد کرده‌ایم.

 

• Table Array: این متغیر محدوده‌ی ستون‌ها در کاربرگ ۲ است که می‌خواهید اطلاعات از آن استخراج شود. این محدوده شامل ستون داده‌ی مشابه برای lookup value (در این مثال آدرس‌های ایمیل) در کاربرگ ۱ و ستون داده‌ای که می‌خواهید در کاربرگ ۱ کپی گردد نیز می‌شود. در مثال ما می‌شود Sheet2!A:B. A به معنی ستون A در کاربرگ ۲ است، یعنی همان ستون در کاربرگ ۲ است جایی که داده‌‌ی یکسان برای lookup value (آدرس ایمیل) در کاربرگ ۱ فهرست می‌شود. B به معنای ستون B و حاوی اطلاعاتی است که تنها در کاربرگ ۲ موجود است و می‌خواهید به کاربرگ ۱ منتقل شود.

 

• Column Number: این متغیر به اکسل می‌گوید که داده‌هایی که می‌خواهید در کاربرگ ۱ کپی شوند در کدام ستون قرار دارند (محدوده‌ی ستون‌هایی که مشحص کرده‌اید). در مثال ما، این متغیر ستونی با عبارت House است. ستون House دومین ستون در محدوده‌ی ستون‌ها (آرایه‌ی جدول) است. بنابراین شماره ستون ما ۲ است. [توجه: محدوده‌ می‌تواند بیش از دو ستون باشد. برای مثال اگر در کاربرگ ۲ سه ستون وجود داشته باشد- شامل Email، Age و House – و شما همچنان بخواهید House را به کاربرگ ۱ انتقال دهید، باز هم می‌توانید از VLOOKUP استفاده کنید. فقط کافی است مقدار متغیر را از ۲ به ۳ تغییر دهید تا مقادیر ستون سوم استخراج شوند:=VLOOKUP(C2:Sheet2!A:C,3,false) ]
• Range Lookup: برای این متغیر از FALSE استفاده کنید تا مطمئن شوید که فقط مقادیر دقیق موجود استخراج می‌شوند.

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

بنابراین زمانی که این فرمول را تایپ می‌کنیم

(=VLOOKUP(C2,Sheet2!A:B,2,FALSE

تمام اطلاعات موجود در خانه‌ها به کاربرگ ۱ منتقل می‌شوند.

به خاطر داشته باشید که VLOOKUP تنها مقادیری را از کاربرگ دوم استخراج می‌کند که در سمت راست ستونی قرار دارند که حاوی داده‌ی یکسان است. این ویژگی باعث بوجود آمدن محدودیت‌هایی می‌شود و علت آنکه افراد ترجیح می‌دهند بجای این تابع از توابع INDEX و MATCH استفاده کنید وجود همین محدودیت‌هاست.

۱۳. از فرمول INDEX MATCH برای انتقال اطلاعات میان ستون‌ها به شکل افقی استفاده کنید.

INDEX و MATCH، همانند VLOOKUP، توابعی هستند که داده را از یک مجموعه‌‌ی اطلاعاتی به جای دیگر انتقال می‌دهند. تفاوت‌های اصلی میان آنها عبارت است از:

• VLOOKUP فرمول بسیار ساده‌تری دارد. اگر با مجموعه‌ داده‌های کلان کار می‌کنید که نیاز به هزاران جستجو دارند، استفاده از تابع INDEX MATCH به شکل کاملا قابل توجهی سرعت بارگذاری اطلاعات در اکسل را کاهش می‌دهند.
• فرمول‌های INDEX MATCH با ترتیب راست به چپ کار می‌کنند، در حالی که فرمول‌های VLOOKUP تنها چپ به راست هستند. به عبارت دیگر، اگر بخواهید جستجویی انجام دهید که نیاز به جستجوی چپ به راست دارد، باید آن ستون‌ها را مجددا مرتب کنید تا بتوانید از تابع VLOOKUP استفاده کنید. انجام این کار می‌تواند در زمان کار با مجموعه‌های اطلاعاتی کلان دشوار و خسته‌کننده باشد یا به بروز خطا منجر شود.

بنابراین اگر بخواهیم اطلاعات موجود در کاربرگ ۱ و کاربرگ ۲ را در کاربرگ ۱ ادغام کنیم، اما مقدار ستون‌ها در کاربرگ ۱ و کاربرگ ۲ مشابه نباشند، برای کار با VLOOKUP ، مجبور خواهیم بود ستون‌ها را جابجا کنیم. در این حالت، بهتر است از تابع INDEX MATCH استفاده شود.

اجازه دهید مثالی را در نظر بگیریم. فرض کنید کاربرگ ۱ حاوی فهرستی از نام افراد و آدرس‌های ایمیل‌ هاگوارتز آنها است و کاربرگ ۲ حاوی فهرستی از آدرس ایمیل و پاترونوسی است که هر دانش‌آموز دارد. (برای آنهایی که طرفدار هری پاتر نیستند باید بگوییم که هر جادوگر یک سپر مدافع مرتبط با اسم خود به نام پاترونوس دارد.) اطلاعات موجود در هر دو کاربرگ ستونی است که دربردارنده‌ی آدرس‌ ایمیل‌ها است، اما این ستونِ آدرس ایمیل در هر کاربرگ شماره ستون متفاوتی دارد. در این حالت بهتر است بجای VLOOKUP از فرمول INDEX MATCH استفاده کنیم تا مجبور نشویم ستون‌ها را جابجا کنیم.

حال باید بدانیم فرمول این تابع به چه صورتی است. فرمول INDEX MATCH در حقیقت فرمول MATCH است که درون فرمول INDEX قرار گرفته است. همانطور که می‌بینید فرمول MATCH با رنگ دیگری متمایز شده است:

(=INDEX(table array, MATCH formula

این فرمول به صورت زیر در می‌آید:

((=INDEX(table array, MATCH (lookup_value, lookup_array

و با توجه به مثال ما، متغیرهای فرمول با مقادیر زیر جایگزین می‌شوند:

(((=INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0

متغیرهای فرمول عبارتند از:

• Table Array: محدوده‌ی ستون‌ها در کاربرگ ۲ است و حاوی اطلاعات جدیدی است که می‌خواهید آنها را به کاربرگ ۱ انتقال دهید. در مثال ما، A به معنای ستون A است که دربرگیرنده‌ی اطلاعات پاترونوس هر فرد است.
• Lookup Value: این متغیر ستونی در کاربرگ ۱ است که دربردارنده‌ی اطلاعاتی است که در هر دو کاربرگ موجود است. در مثال زیر، این ستون، ستون email در کاربرگ ۱ است، یعنی ستون C. بنابراین مقدار متغیر برابر است با Sheet1!C:C.
• Lookup Array: این متغیر ستونی در کاربرگ ۲ است که در بردارنده‌ی اطلاعاتی است که در هر دو کاربرگ وجود دارد. در مثال زیر، این متغیر ستون email در کاربرگ ۲ است، یعنی همان ستون C. بنابراین مقدار متغیر Sheet2!C:C است.

زمانی که مقادیر متغیرها را بدست آوردید، فرمول INDEX MATCH را در بالاترین سلول از ستون خالی پاترونوس در کاربرگ ۱ تایپ کنید، یعنی جایی که می‌خواهید اطلاعات ادغام شده قرار بگیرند.

۱۴. از تابع COUNTIF  برای شمارش کلمات یا اعداد در هر محدوده‌ای از سلول‌ها استفاده کنید.

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

برای مثال، فرض کنیم می‌خواهیم تعداد دفعات تکرار کلمه‌ی Gryffindor را در مجموعه داده‌های خود حساب کنیم. فرمول کلی تابع به این صورت است:

(=COUNTIF(range, criteria

فرمول برای مثال ما به این شکل خواهد شد:

(“=COUNTIF(D:D,”Gryffindor

در این فرمول، چند متغیر وجود دارد:

• Range: محدوده‌ای است که می‌خواهیم فرمول پوشش دهد. از آنجایی که در این مثال، ما تنها بر روی یک ستون تمرکز کرده‌ایم، از مقدار D:D استفاده می‌کنیم تا نشان دهیم که هم ستون اول و هم ستون آخر D است. اگر ستون‌های C و D مدنظر ما بود، باید از C:D استفاده می‌کردیم.
• Criteria: این متغیر همان عدد یا متنی است که می‌خواهیم اکسل تعداد تکرار آن را بشمارد. اگر می‌خواهید که نتیجه بجای آنکه به صورت عدد نمایش داده شود، متن باشد از علامت نقل قول استفاده کنید. مقدار criteria در مثال ما می‌شود “Gryffindor”.

تنها کافی است فرمول COUNTIF را در یک سلول تایپ کرده و کلید Enter را بزنید تا تعداد دفعات تکرار کلمه‌ی Gryffindor در مجموعه‌ی داده نمایش داده شود.

۱۵. ادغام سلول‌ها با استفاده از &

مجموعه‌ داده‌ها اغلب داده‌ها را تقسیم می‌کنند تا به اندازه‌ی ممکن دقیق باشند.
برای مثال، یک مجموعه داده ممکن است بجای آنکه اطلاعات مربوط به نام کامل افراد را دربربگیرد، ابتدا نام کوچک و سپس نام خانوادگی فرد را در ستون‌های جداگانه نمایش دهد.
گاهی ممکن است اطلاعات مربوط به مکان زندگی فرد بر اساس شهرستان، شهر و کد پستی در سلول‌های جداگانه قرار گرفته باشد.
در اکسل، شما می‌توانید با استفاده از علامت & در تابع سلول‌هایی با داده‌ی متفاوت را در یک سلول ادغام کنید.

در این حالت فرمول مثال ما به این صورت خواهد بود:  =A2&” “&B2

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

اما هنوز کار ما تمام نشده است. اگر چیزی که تایپ می‌کنید =A2&B2 باشد، فاصله‌ای میان نام کوچک و نام خانوادگی وجود نخواهد داشت.
برای آنکه این فاصله اضافه شود از =A2&” “&B2 استفاده کنید. علامت نقل قول به همراه یک فاصله به اکسل می‌گوید که فاصله‌ای میان نام کوچک و نام خانوادگی قرار دهد.

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

 

۱۶. اضافه کردن چک باکس‌ها

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

برای مثال، اگر از صفحه گسترده‌ی اکسل برای مدیریت مشتری‌های احتمالی خود استفاده می‌کنید و می‌خواهید بدانید که آیا در طی سه ماه اخیر با آنها تماس گرفته شده است یا خیر، می‌توانید ستون “called this quarter?” (یا “تماسی در طی سه ماه اخیر گرفته شده؟”) داشته باشید و زمانی که با مشتری احتمالی تماس گرفته شد سلول‌های درون آن تیک می‌خورند.

نحوه‌ی انجام این کار در اکسل ۲۰۱۸ و ۲۰۱۹ به این صورت است:

سلولی که می‌خواهید در صفحه گسترده به آن چک باکس اضافه شود را هایلایت کنید. سپس بر روی DEVELOPER کلیک کنید. در مرحله‌ی بعد، زیر FROM CONTROLS، بر روی چک باکس یا دایره‌‌ای که در تصویر زیر نشان داده شده کلیک کنید.

زمانی که باکس در سلول ظاهر شد، آن را کپی کنید. سپس سلول‌های دیگری که می‌خواهید چک‌باکس در آنها نیز وجود داشته باشد را هایلایت کرده و باکس کپی‌شده را پیست کنید.

۱۷. هایپرلینک کردن سلول‌ها به یک وبسایت

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

کلماتی که می‌خواهید هایپرلینک شوند را هایلایت کنید. سپس کلیدهای Shift و K را فشار دهید. با انجام اینکار باکسی ظاهر خواهد شد که می‌توانید در آن URL هایپرلینک را وارد کنید. URL را کپی و در این باکس پیست کنید و کلید Enter را بزنید.

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

۱۸. اضافه کردن منوهای کشویی

گاهی اوقات از صفحه گسترده برای نظارت بر فرآیندها یا دیگر موارد کیفی استفاده می‌کنید. بجای آنکه کلمات پر تکرار- مانند “Yes” و “No”- را هربار در کاربرگ خود بنویسید، می‌توانید از منوهای کشویی (drop-down) استفاده کنید تا عبارات توصیفی درباره‌ی مشتریان یا هر آنچه که بررسی می‌کنید را سریعا انتخاب کنید.

‌ روش اضافه کردن منوهای کشویی به سلول‌ها به این صورت است:

سلول‌هایی که می‌خواهید منوهای کشویی به آنها اضافه شود را هایلایت کنید. سپس  بر روی منوی Data در سمت بالا کلیک کرده و Validation را انتخاب کنید.

در این مرحله پنجره‌ی Data Validation Settings باز می‌شود. در میان گزینه‌های زبانه‌ی Allow، بر روی Lists کلیک و Drop-down List را انتخاب کنید. تیک گزینه‌ی In-Cell dropdown را فعال کرده و در نهایت OK را انتخاب کنید.

 

فیلم آموزش کار با اکسل (فرمول نویسی و استفاده از توابع Excel)

در صورتی که این آموزش  کار با اکسل برای شما مفید بوده است:

برای یادگیری بیشتر می‌توانید دوره های آنلاین آموزش Excel را مشاهده نمایید.

دوره‌های مرتبط در دانشیار آی تی