۳۰ سوال رایج اکسل برای استخدام (۳۰ تمرین اکسل)

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

نحوه استفاده از Data Modeling برای ایجاد Pivot Table:

پس از ایجاد روابط بین جداول، از داده ها برای تجزیه و تحلیل استفاده کنید.

• بر روی هر خانه در کار برگ کلیک کنید
• روی Insert> PivotTable کلیک کنید

• در کادر محاوره ای Create PivotTable، در زیر Choose the data that you want to analyze، روی Use an external data source کلیک کنید

• روی Choose Connection کلیک کنید.
• در سربرگ Tables، در قسمت Workbook Data Model، Tables in Workbook Data Model انتخاب کنید.

• بر روی Open کلیک کرده و سپس OK را کلیک کنید تا یکField List شامل تمام جداول موجود در Data Model نشان داده شود.

ریبون در MS-Excel چیست؟

پاسخ: ریبون در اکسل از سر برگ هایی تشکیل شده که در بالای برنامه قرار دارد. این سربرگ ها به گروه هایی تقسیم می شوند که دکمه های مربوط به دستور را در زیر وظایف دسته بندی می کنند.

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

با این کار یک کادر محاوره ای باز می شود که شامل چندین گزینه اضافی است که می توانیم انتخاب کنیم.

طبق تنظیمات پیش فرض اکسل، ما ۸ سربرگ داریم که عبارتند از:

• File
• Home
• Insert
• Page Layout
• Formulas
• Data
• Review
• View

برای تنظیم متن درون سلول از کدام گزینه استفاده می کنیم و روش انجام آن چگونه است؟

پاسخ: برای تنظیم متن در سلول، از گزینه Wrap text استفاده می کنیم. به دو روش قابل استفاده است:

گزینه ۱: در سربرگ Home> Alignment> Wrap Text.

گزینه ۲:

• Ctrl + 1 را روی صفحه کلید خود فشار دهید
• کادر محاوره ای Format Cells ظاهر می شود
• در سر برگ Alignment
• روی Wrap text کلیک کنید
• سپس بر روی OK کلیک کنید

چگونه می توانم همه اشیا موجود در برگه را انتخاب کنیم؟

پاسخ: برای انتخاب شی از گزینه Go to Special استفاده می کنیم.

برای انتخاب اشیا مراحل زیر را دنبال کنید:

• کلید میانبر F5 را فشار دهید تا کادر گفتگوی Go to Special باز شود
• روی Special کلیک کنید > روی object کلیک کنید > OK را کلیک کنید
• همه اشیا انتخاب می شوند

تابع IF در اکسل چیست؟

پاسخ: ” تابع IF” یکی از توابع منطقی در اکسل است. ما از این تابع برای بررسی شرایط منطقی و تعیین مقدار درست یا نادرست استفاده می کنیم. ” تابع IF” دارای سه آرگومان است اما فقط اولین آرگومان اجباری است و دو آرگومان دیگر اختیاری است.

استفاده از Name Box چیست؟

پاسخ: Name Box در گوشه سمت چپ صفحه اکسل قرار دارد. معمولاً ما از Name Box برای بررسی ارجاع سلول به سلول فعال استفاده می کنیم اما چندین استفاده دیگر نیز دارد.

برای مثال: ما می توانیم نام محدوده را از طریق Box Name تعریف کنیم. در زیر مراحل آورده شده است:

• دامنه را انتخاب کنید
• Name Box را ویرایش کنید
• Weeks را تایپ کنید و Enter را فشار دهید

کاربرد VLOOKUP چیست و چگونه از آن استفاده می کنیم؟

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

چگونه می توانیم از آن استفاده کنیم؟

ما ۲ مجموعه داده منابع انسانی در اکسل داریم. در داده ی دوم، می خواهیم تاریخ عضویت هر کارمند را از اولین داده به روز کنیم. برای استفاده از تابع Vlookup، داده ها باید دارای مقدار مشترک باشند.

مراحل زیر را دنبال کنید:

• فرمول را در سلول G3 وارد کنید
• =VLOOKUP(F3,$A$3:$D$13,2,0)
• Enter را فشار دهید و فرمول مشابه را در محدوده F4: F13 کپی کنید

توضیح فرمول:

=VLOOKUP(F3,$A$3:$D$13,2,0)

• در این فرمول، F3 سلول با مقدار معمول یا مقدار جستجو است
• سپس دامنه $A$3:$D$13را در داده اول انتخاب کرده ایم
• ۲: انتخاب مقدار را از ستون ۲ تعریف کرده ایم
• ۰: برای مطابقت دقیق تعریف کرده ایم

چگونه می توان مقادیر را در ستون سمت راست در اکسل مشاهده کرد؟

پاسخ: می توانید مقدار را از ستون سمت راست از طریق تابع Index and Match مشاهده کنید.

مثال: ما ۲ مجموعه داده منابع انسانی در اکسل داریم. در داده دوم، می خواهیم تاریخ عضویت هر کارمند را از اولین داده به روز کنیم. برای استفاده از عملکرد Vlookup ، داده ها باید دارای مقدار مشترک باشند.

مراحل زیر را دنبال کنید:

• فرمول را در سلول G3 وارد کنید
• =INDEX($A$3:$D$13,MATCH(F3,$B$3:$B$13,0),1)
• Enter را فشار دهید
• فرمول را در محدوده G4: G13 کپی کنید.

توضیح فرمول:

=INDEX($A$3:$D$13,MATCH(F3,$B$3:$B$13,0),1)

• در فرمول =INDEX($A$3:$D$13 این نحو برای تعریف آرایه ای که می خواهیم مقدار را از آن انتخاب کنیم استفاده می شود.
• MATCH(F3,$B$3:$B$13,0) این نحو به جستجوی مقدار کمک می کند
• در آخر “۱” انتخاب مقدار به عنوان نتیجه است بنابراین ۱ به این معنی است که ما می خواهیم مقدار را از ستون ۱ انتخاب کنیم

چگونه می توانیم رشته های متنی چند سلول را در یک سلول ادغام کنیم؟

پاسخ: می توانیم با استفاده از تابع Concatenate و “&” چندین رشته متنی را ادغام کنیم.

مثال: ما سه نام داریم: نام، نام میانی، نام خانوادگی در ۳ ستون. برای ادغام نام ها و ایجاد نام کامل، مراحل زیر را دنبال کنید:

تابع Concatenate

• فرمول را در سلول D2 وارد کنید
• =CONCATENATE(A2,” “,B2,” “,C2)

برای ادغام متن از فرمول “&” استفاده کنید:

• فرمول را در سلول E2 وارد کنید
• =A2&” “&B2&” “&C2

تابع SUMIF چیست و چگونه از آن استفاده کنیم؟

پاسخ: از تابع Sumif برای افزودن سلول های مشخص شده توسط یک شرط یا معیار استفاده می کنیم.

چگونه از آن استفاده کنیم؟

ما داده های منابع انسانی داریم که در آن ها جزئیات حقوق و دستمزد هر کارمند وجود دارد. اکنون می خواهیم کل مبلغ حقوق و دستمزد را بازیابی کنیم.

این مراحل را دنبال کنید:

• فرمول را در سلول I2 وارد کنید
• =SUMIF($A$2:$E$17,$H2,$E$2:$E$17) و Enter را فشار دهید
• فرمول مشابه را در محدوده کپی کنید

توضیح فرمول:

• A$2:$E$17$ محدوده داده است
• H2$ ملاکی است که فرمول جمع آن را محاسبه می کند
• E$2:$E$17,$ محدوده مجموع داده ها است

تابع COUNTIF چیست و چگونه از آن استفاده کنیم؟

پاسخ: برای شمارش سلول های مشخص شده، با یک شرط یا معیار مشخص، از تابع Countif استفاده می کنیم.

مثال: ما داده های منابع انسانی با جزئیات حقوق و دستمزد هر کارمند، از نظر بخش را داریم. اکنون می خواهیم تعداد کارمندان بخش هوشمند را حساب کنیم.

• فرمول را در سلول I2 وارد کنید
• =COUNTIF ($ A $ 2 $: $ A $ 17,H2)
• فرمول مشابه را برای همه سازندگان کپی کنید

تابع Nested IF چیست؟

پاسخ: هنگامی که چندین شرط برای رسیدن به هدف داریم، می توانیم ۷ بار از تابع IF استفاده کنیم که به آن تابع Nested IF گفته می شود.

مثال: در سلول A1 ، لیست کشویی A ، B ، C و D وجود دارد. اگر A انتخاب شود، سلول B1 باید Excellent را برگرداند، در صورت انتخاب B نتیجه باید good باشد، برای C نتیجه باید Badبد باشد و D نتیجه باید Very Bad باشد.

Pivot table چیست و چرا از آن استفاده می کنیم؟

پاسخ: Pivot table امکان جمع بندی سریع داده های بزرگ را فراهم می کند. فقط در چند دقیقه می توانیم فیلد را محاسبه کرده و داده ها را به روشی قابل ارائه ترتیب دهیم. اکثر کارشناسان اکسل معتقدند که Pivot table قدرتمندترین ابزار است.

چرا از آن استفاده می کنیم؟

• Pivot table به ما انعطاف پذیری و قدرت تحلیلی می دهد
• یک منبع صرفه جویی در وقت در اکسل است
• مقادیر منحصر به فرد را در هر ستون جدول ذکر می کند
• یک نمودار محوری پویا ایجاد می کند
• منابع داده خارج از اکسل را پیوند می دهد و می تواند از چنین داده هایی گزارش محوری ایجاد کند

چگونه از Advanced filter استفاده کنیم؟

پاسخ: ما می توانیم از فیلترهای پیشرفته برای استخراج لیست منحصر به فرد آیتم ها استفاده کنیم یا می توانیم مورد خاص را از صفحه های مختلف استخراج کنیم. می توان گفت که Advanced filter نسخه پیشرفته Auto filter است.

مثال: در یک محدوده محصولات تکراری داریم و فقط می خواهیم یک لیست منحصر به فرد را فیلتر کنیم.

مراحل زیر را دنبال کنید:

• دامنه داده را انتخاب کنید
• به تب Data بروید و روی Advanced کلیک کنید
• کادر گفتگوی پیشرفته باز می شود
• روی copy to another location کلیک کنید
• مقصد را انتخاب کنید

• بر روی OK کلیک کنید

چگونه می توانیم قالب بندی سلول را تغییر دهیم؟

پاسخ: برای تغییر قالب بندی سلول از گزینه “Format cell” استفاده می شود.

مثال: در سلول A1، مقدار قرار است به درصد تبدیل شود، با پیروی از این مراحل، آن را تغییر دهید:

• کلید میانبر Ctrl + 1 را فشار دهید تا کادر گفتگوی Format cells باز شود
• در گروه اعداد، روی گزینه Percentage کلیک کنید
• بر روی OK کلیک کنید

قالب بندی شرطی چیست و چگونه از آن استفاده کنیم؟

پاسخ: قالب بندی شرطی ابزاری است که به ما امکان می دهد سلول ها یا دامنه را بر اساس چند شرط هایلایت کنیم و قالب بندی همیشه بر اساس مقادیر یا متنی است که می تواند به طور خودکار تغییر کند.

مثال: در سلول A1، یک لیست کشویی از A ، B ، C و D وجود دارد اگر A انتخاب شده باشد، سلول باید با رنگ سبز برجسته شود، اگر B1 انتخاب شده باشد، رنگ سلول باید آبی باشد، در مورد C باید زرد باشد و در صورت انتخاب D، باید با رنگ قرمز مشخص شود.

این مراحل را دنبال کنید:

• سلول A2 را انتخاب کنید
• مراحل روبرو را طی کنید: Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

• فرمول را در سربرگ وارد کنید
• بر روی Format > Format cells dialog box will appear > Fill tab > Choose color > Click on OK کلیک کنید
• برای بقیه نمرات نیز همین روش را دنبال کنید

چگونه می توان یک لیست کشویی ایجاد کرد؟

پاسخ: ما با استفاده از اعتبار سنجی داده ها در اکسل لیست کشویی را ایجاد می کنیم.

مثال: ما می خواهیم لیست روزهای هفته را در یک سلول ایجاد کنیم.

این مراحل را دنبال کنید:

لیست روزهای هفته را در ستون A ایجاد کنید.

سلولی را که می خواهیم در آن لیست کشویی ایجاد کنیم را انتخاب کنید.

• به سربرگ Data رفته Data Validation را انتخاب کرده تا کادر گفتگو باز شود
• در سربرگ Settings ، List (Allow) و (Source (Select the range A1:A8 را انتخاب کنید و روی ok کلیک کنید
• در Cell C1 ، لیست کشویی ایجاد می شود

چگونه لیست کشویی پویا ایجاد کنیم؟

پاسخ: برای افزودن آیتم در لیست، باید یک لیست پویا ایجاد کنید. این لیست مقدار افزوده را به طور خودکار انتخاب می کند و نیازی به ویرایش در لیست نیست. برای ایجاد یک لیست کشویی پویا، از تابع offset به همراه تابع Countif استفاده می کنیم.

مراحل ایجاد لیست پویا:

• سلول C1 را انتخاب کنید
• به تب Data بروید، Data Validation را انتخاب کنید تا کادر محاوره ای ظاهر شود

• در سر برگ (Settings ، List (Allow را انتخاب کنید.
• فرمول را در جعبه فرمول وارد کنید.
• =OFFSET(A:A,1,0,COUNTA(A:A)-1,1)
• بر روی OK کلیک کنید

چگونه می توانیم روز هفته را برای یک تاریخ خاص تعیین کنیم؟

پاسخ: با استفاده از تابع Weekday، می توانیم به روز هفته ی یک تاریخ خاص برگردیم.

مثال: در سلول A1، تاریخ امروز قرار دارد و می خواهیم روز هفته را برگردانیم و از روز یکشنبه حساب کنیم. این مراحل را دنبال کنید:

• فرمول را در سلول B1 وارد کنید
• =WEEKDAY (A1،۱) سپس Enter را فشار دهید
• فرمول ۳ را برمی گرداند، یعنی امروز سومین روز هفته است

نمودار چیست و چگونه می توانیم از آن استفاده کنیم؟

پاسخ: نمودار راهی است برای نمایش داده ها در تجسم گرافیکی. می توانیم داده ها را با استفاده از نمودار به صورت آموزنده تر و قابل فهم ارائه دهیم. در اکسل ۱۰ نوع نمودار داریم.

مثال: برای نمایش نمودار عملکرد فروش، نمودار میله ای مناسب است.

ما داده های تولیدکنندگان با قیمت خرید را داریم. می خواهیم سهم هر تولید کننده را ببینیم. بنابراین از نمودار pie استفاده خواهیم کرد.

• دامنه داده را انتخاب کنید
• به Insert> Charts> Select Pie Chart بروید

در تصویر بالا، به وضوح می توان دریافت که کدام تولیدکننده بیشترین سهم و کدام تولید کننده کمترین سهم را داشته است.

ترفندهای تکمیلی و پیشرفته تر