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

۱۸ مهر ۱۴۰۱ | ۱۲:۳۱ کد : ۲۹۷۰۸ اخبار آموزش فناوری اطلاعات
تعداد بازدید:۲۷۰۷
تهیه کننده خبر: الهام طالبی
۳۰ سوال رایج اکسل برای استخدام (۳۰ تمرین اکسل)

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

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

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

C:\Users\Mr\Desktop\image-7.png

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

C:\Users\Mr\Desktop\image-8.png

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

C:\Users\Mr\Desktop\image-9.png

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

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

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

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

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

C:\Users\Mr\Desktop\image-16.png

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

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

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

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

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

C:\Users\Mr\Desktop\image-26.png

گزینه ۲:

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

C:\Users\Mr\Desktop\image-36.png

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

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

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

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

C:\Users\Mr\Desktop\image-151.png

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

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

C:\Users\Mr\Desktop\image-161.png

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

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

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

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

C:\Users\Mr\Desktop\image-17.png

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

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

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

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

C:\Users\Mr\Desktop\image-18.png

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

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

C:\Users\Mr\Desktop\image-19.png

توضیح فرمول:

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

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

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

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

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

C:\Users\Mr\Desktop\image-20.png

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

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

C:\Users\Mr\Desktop\image-211.png

توضیح فرمول:

=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 و “&” چندین رشته متنی را ادغام کنیم.

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

C:\Users\Mr\Desktop\image-221.png

تابع Concatenate

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

C:\Users\Mr\Desktop\image-231.png

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

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

C:\Users\Mr\Desktop\image-241.png

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

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

Sum_Range criteria range ساختار
دامنه ستونی که می خواهیم مجموع را از آن محاسبه کنیم برای آن می خواهیم مجموع داده ها را محاسبه کنیم دامنه داده ای که می خواهیم جمع را از آن بازیابی کنیم

=SUMIF(range, criteria,[sum_range])

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

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

C:\Users\Mr\Desktop\image-251.png

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

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

C:\Users\Mr\Desktop\image-261.png

توضیح فرمول:

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

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

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

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

C:\Users\Mr\Desktop\image-27.png

  • فرمول را در سلول 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 باشد.

C:\Users\Mr\Desktop\image-28.png

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

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

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

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

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

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

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

C:\Users\Mr\Desktop\image-29.png

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

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

C:\Users\Mr\Desktop\image-30.png

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

C:\Users\Mr\Desktop\image-311.png

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

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

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

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

C:\Users\Mr\Desktop\image-321.png

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

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

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

C:\Users\Mr\Desktop\image-331.png

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

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

C:\Users\Mr\Desktop\image-341.png

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

C:\Users\Mr\Desktop\image-351.png

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

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

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

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

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

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

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

C:\Users\Mr\Desktop\image-361.png

C:\Users\Mr\Desktop\image-37.png

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

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

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

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

C:\Users\Mr\Desktop\image-38.png

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

C:\Users\Mr\Desktop\image-39.png

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

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

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

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

C:\Users\Mr\Desktop\image-40.png

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

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

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

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

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

C:\Users\Mr\Desktop\image-411.png

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

منبع: sariasan.com

الهام طالبی

الهام طالبی
تهیه کننده خبر

کلیدواژه‌ها: آموزش اکسل تنظیم سلول

( ۳ )

نظر شما :