جدول هادر اکسل

در نرم افزار اکسل نکات ارزنده ای پیرامون جدول ها وجود دارد

باماهمراه باشید تا با تعدادی از آنها آشنا شویم

 تغییر جهت اطلاعات در اکسل
گاهی اوقات نیاز است که اطلاعات را از حالت ستونی به سطری یا بالعکس تغییر دهیم. برای این منظور باید کل سلول‌های مورد نظر را انتخاب کرده و در جایی دیگر از فایل، گزینه زیر را انتخاب کنید.
Home->Paste->Transpose
خواهید دید که حالت سطر و ستون اطلاعات شما به صورت برعکس کپی می‌شود.

مخفی کردن حرفه ای اطلاعات در excel
به جز روش معمول کلیک‌راست و انتخاب Hide از منوی باز‌شده، می‌توان با روشی حرفه‌ای‌تر، اطلاعات سلول‌ها و ستون‌ها را مخفی کرد. کافی است سلول مورد نظر را انتخاب کرده و در قسمت Format Cells، تب Number را انتخاب کنید و سپس Custom را انتخاب کرده و در قسمت نام، عبارت ;;; را تایپ کنید. کلیه اطلاعات مورد نظر نامرئی می‌شوند و دوباره با تغییر فرمت سلول مورد نظر قابل بازیابی هستند

 انتقال اطلاعات چند سلول به یک سلول
برای این‌که اطلاعات چند سلول را در یک سلول کنار هم نشان دهیم از علامت & استفاده می‌کنیم. مثلا برای نمایش اطلاعات سلول‌های A2,B2,C2 در سلولی دیگر مثلا F2 عبارت زیر را تایپ می‌کنیم:
=A2&B2&C2

تغییر ساختار حروف کوچک و بزرگ
اگر بخواهیم اطلاعات انگلیسی یک سلول در سلول دیگر وارد شوند و ساختار حروف کوچک و بزرگ تغییر کند، از عبارت‌های UPPER، LOWER، و PROPER استفاده می‌کنیم. به طور مثال برای این‌که اطلاعات سلول A2 با حروف بزرگ در سلول F2 نوشته شوند، در سلول F2 می‌نویسیم: =UPPER(A2)

 وارد کردن عددهایی که با صفر شروع می‌شوند
در حالت پیش‌فرض، اکسل عدد صفر را از ابتدای اطلاعات عدد حذف می‌کند. اگر بخواهیم اطلاعاتی عددی که با صفر شروع می‌شوند وارد کنیم، کافی است در ابتدای آنها کاراکتر ‘ را اضافه کنیم.
(در اکثر کیبوردها کلید " گ " در حالت زبان انگلیسی، علامت مشخص شده در کادر قرمز رنگ است)

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

هر نوع داده‌ای که دارای ترتیب منطقی باشد این قابلیت را دارد که به صورت خودکار در سلول‌های اکسل درج شود، فرض کنید می‌خواهیم اعداد زوج 0 تا 30 را به صورت خودکار در اکسل درج کنیم، برای این کار باید مراحل زیر را طی کنیم:

• ابتدا داده‌ی اول و دوم را در سلول‌های دلخواه به ترتیب بنویسید (یعنی اعداد 0 و 2)
• حال این دو عدد را به کمک ماوس انتخاب کنید (درگ و دراپ کردن)
• مربع کوچکی در انتهای بخش انتخاب شده ظاهر شده است (قسمت پایین سمت چپ) که اگر ماوستان را بر روی آن قرار دهید نشانگر ماوس به صورت علامت + نمایش داده می‌شود.
• ماوس را در این قسمت قرار داده و به سمت پایین درگ کنید (کلیک کرده و همزمان ماوس را به سمت پایین حرکت دهید) مشاهده می‌کنید که سلول‌ها به ترتیب دوتایی و به صورت خودکار پر می‌شوند
• همانگونه که گفتیم داده‌هایی دارای ترتیب منطقی باشند این قابلیت را دارند که به صورت خودکار توسط اکسل تکمیل شوند،
• رای اینکه به چیزی مشابه با مثال‌های بالا دست یابید کافیست دو داده‌ی اول را بنویسید و طبق مراحل توضیح داده شده عمل کنید تا اکسل به طور خودکار لیست مورد نظر شما را آماده کند، نمونه‌ای از لیست‌های خودکار ساخته شده توسط اکسل را در مراحل زیر می‌بینید.
• نوشتن داده‌های مورد نظر و انتخاب آن‌ها (همانگونه که می‌بینید می‌توانیم چندین نوع داده‌ی مختلف نیز درج کنیم)
• برای درج لیست، به روش گفته شده در مراحل قبل ماوس را تا محل مورد نظر درگ می‌کنیم.

• در پایان تمام داده‌ها به صورت خودکار درج می‌شوند.

  برخی از داده‌ها به صورت پیش‌فرض در اکسل موجود نیستند (مثلا حروف البفا) اگر قصد دارید داده‌ای مانند حروف الفبا را به صورت خودکار در اکسل درج کنید باید برای آن یک لیست دلخواه (Custom List) ایجاد کنید.

  ایجاد لیست خودکار دلخواه در اکسل

  برای ساخت یک لیست خودکار در اکسل مراحل زیر را طی کنید:

  • وارد سربرگ File شوید.
  • وارد قسمت Option شوید.
  • از سمت چپ گزینه‌ی Advanced را انتخاب کنید.
  • حال در قسمت General بر روی گزینه‌ی …Edit Custom Lists کلیک کنید.

  • در نسخه‌های قدیمی‌تر اکسل این گزینه در آدرس زیر قرار دارد:

    Tools → Option → Custom List (Tab) → New List

    با انجام این مراحل پنجره‌ای مانند تصویر زیر برای شما باز خواهد شد:

    • برای ایجاد لیست جدید در ابتدا گزینه‌ی NEW LIST را برگزینید.
    • حال در قسمت List entries داده‌های مورد نظر خود را به ترتیب وارد کنید، هر داده را در یک خط بنویسید (پس از نوشتن هر یک از داده‌ها اینتر بزنید)

    به عنوان مثال می‌خواهیم یک لیست جدید از حروف الفبای انگلیسی ایجاد کنیم، برای این منظور حروف الفبا را به ترتیب از حرف A تا Z در بخش List entries می‌نویسیم

     

    پس از درج داده‌ها بر روی گزینه‌ی Add کلیک می‌کنیم تا اطلاعات در لیست ثبت شوند و در نهایت نیز بر روی OK کلیک می‌کنیم.

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

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

  رسم نمودار با دو محور عمودی ودو محور افقی در اکسل                                                                                                                                        داده هاوفشردن کلید  F11 روی صفحه کلیدمی باشدکه در این روش، نمودار در sheet 

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

 می شود که می توانید آنرا به sheet نیز منتقل کنید ولی برای اینکه دستمان آزادتر

 باشد وبعضی دوباره کاریها را انجام ندهیم  نمودار را مستقیماً در sheet وبا استفاده 

از زبانه insert   رسم می کنیم.پس ابتدا برای رسم نمودار باید داده هامون را به طور 

صحیح انتخاب کنیم یعنی چارچوب انتخاب داده هامون باید صحیح باشد  جزئیتر

 می گویم برای یک نمودار معمولی دو محور x ,y داریم که برای انتخاب داده ها باید

 تعداد داده های محورهای x ,y یکسان باشند و یا عقب وجلونباشند.در محور x 

متغیر های ما قرار می گیرد ودر محور y مقدار این متغیرها قرار خواهد گرفت.حال ما 

میخواهیم دو نمودار با مقیاسهایی که بسیار با هم متفاوت هست را با هم

 ادغام کنیم یعنی در یک نمودار دو گروه مختلف داده ها را در سریهای جدا با 

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

انتخابکرده وبا استفاده اززبانهinsert  یک نمودار خطی رسم می کنیم

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

روی قسمت chart Area  یا همان زمینه نموداردوم وانتخاب copy  آن را کپی کرده وروی نمودار

اول paste می کنیم.با کمال تعجب چون نمودارها از نظر مقیاس زیاد با هم تفاوت دارند نموداری که 

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

مسئله روی خط یا سری نموداری که میخواهیم محورعمودی آن را به راست منتقل کنیم کلیک

کردهوسپس درزبانه  Layout  با کلیک روی Format Selection  وفعال کردن  Secondary Axis

 در وسپس با بستن پنجره، محور عمودی سری یا نمودار انتخابی (نمودار دوم) به راست 

منتقلخواهد شد ودو نموداربا مقدارهای خیلی متفاوت در یک نمودار به طور واضح دیده خواهند

شد.که محور y نمودار اول در سمت چپ ومحورy نمودار دوم در سمت راست قرار خواهد گرفت.

حالا برای اینکه محور افقی دو نمودار را جدا کنیم یعنی یکی پایین ودیگری بالا باشد به زبانه

Layout رفته وسپس به مسیز زیر بروید.

Axes / Secondary Horizontal Axis / Show Left to Right Axis

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

مقادیر ثابت
مقادیر ثابت مقادیری هستند که ثابت بوده و تغییر نمی کنند. این مقادیر می توانند عددی یا متنی باشند. در فرمول نویسی باید مقادیر ثابت متنی را بین دو علامت " قرار دهیم. در این صورت محاسبه ای روی این مقادیر انجام نمی شود.

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

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

عملگرهای محاسباتی: عملگرهای محاسباتی، عملگرهایی هستند که از آنها برای محاسبات عددی استفاده می شود. این عملگرها عبارتند از + و - و * و / و ٪ و ^ که به ترتیب و از راست به چپ برای محاسبه جمع، تفریق، ضرب، تقسیم، درصد و توان به کار می روند.

عملگرهای مقایسه ای: از عملگرهای مقایسه ای برای مقایسه مقادیر استفاده می شود. این عملگرها عبارتند از =،، => و. این عملگرها به ترتیب و از راست به چپ مساوی، بزرگ تر، بزرگ تر مساوی، کوچک تر، کوچک تر مساوی و نامساوی بودن دو عدد را مقایسه می کند. نتیجه حاصل از عملیات این عملگرها می تواند مثبت (درست) یا منفی (نادرست) باشد.

عملگرهای رشته ای: از این عملگر برای چسباندن دو رشته به هم استفاده می شود. استفاده از عملگر & در سلول ها، برای اتصال یا الحاق داده های متنی است.

عملگرهای آدرس: از عملگرهای آدرس برای تعیین محدوده آدرس استفاده می شود. این عملگرها عبارتند از: و ;. از عملگر نخست برای معرفی محدوده متوالی سلول ها و از عملگر دوم برای معرفی محدوده نامتوالی سلول ها استفاده می شود.

اولویت انجام محاسبات ریاضی: اگر با ریاضی آشنایی داشته باشید، عملگرها از اولویتی برای انجام محاسبات برخوردارند. این اولویت ها در نرم افزار اکسل نیز اجرا می شوند و در فرمول نویسی از اهمیت بالایی برخوردار هستند. اولویت ها در ریاضی به ترتیب پرانتز، درصد، توان، ضرب و تقسیم، جمع و تفریق و الحاق متن است که با علامت های زیر نشان داده می شوند:
()، ٪، ^، * /، + -، &
مثال: فرض کنید می خواهیم عبارت 4*2+10 را به دو حالت مختلف اجرا کنیم و نتیجه را بر اساس اولویت عملگرها مشاهده کنیم. در حالت اول حاصل عبارت 4*2+10 عدد 18 خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است. اما اگر این عبارت را به صورت (10+2)*4 بنویسیم، حاصل 48 خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

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

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (.... ; ورودی 3; ورودی 2; ورودی 1) نام تابع
مثال از یک تابع: برای اینکه با عملکرد یک تابع به صورت عملی آشنا شوید، بهتر است یک مثال را در اکسل اجرا کنید. برای اینکار از تابع SUM یا جمع استفاده کنید. این تابع، سلول های انتخاب شده را با یکدیگر جمع می کند.

پس از درج تابع، اگر بر روی کلید Enter کلیک کنید، نتیجه تابع به شما نشان داده می شود.

روش درج فرمول با استفاده از Function Wizard: اکسل فرمول های زیادی دارد و همانطور که پیشتر اشاره شد، هر تابع نیاز به «ورودی هایی» برای محاسبه دارد. با استفاده از امکان Function Wizard می توانید از تمام توابع اکسل استفاده کنید و نتیجه محاسبات را در سلول مورد نظر مشاهده کنید.
برای این کار از روی نوار فرمول، بر روی fx کلیک کنید تا کادر Insert Function باز شود.

توضیح کادر Insert Function: در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید. در بخش Or select a category می توانید یکی از بخش های موجود را انتخاب کنید. این بخش ها توابع اکسل را دسته بندی کرده است. مثلا اگر بخش Financial را انتخاب کنید، توابع مالی در کادر Select a function نشان داده خواهند شد.
با انتخاب گزینه All همه توابع اکسل و با انتخاب گزینه Most Recently used نیز توابعی که اخیرا استفاده کرده اید نشان داده می شوند. اکنون در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید و از بخش Select a function آن را انتخاب کنید.
پس از انتخاب تابع SUM در اکسل  بر روی دکمه OK کلیک می کنیم. در پنجره جدید که Function Arguments نام دارد، باید ورودی های تابع را وارد کنیم.

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

نکاتی پیرامون تغییر سبک سلول ها در اکسل
 برای این کار از منوی Format گزینه Style و سپس در منوی Style name سبک Normal را انتخاب کنیدو در ادامه دکمه Modify را برگزینید. حال پنجره Format Cells باز خواهد شد. اکنون باید سبک خود را با استفاده از گزینه های موجود در زبانه های این پنجره اعمال کنید و سپس OK را بزنید.

خواهید دید این سبک به تمام سلول ها، برگه های قبلی و برگه هایی که در آینده می سازید، اعمال می شود. اما ممکن است برای یک سلول یک سبک را تنظیم کرده باشید و بخواهید که آن را ذخیره کنید تا در آینده برای سلول های دیگر، از آن استفاده کنید.

 برای این کار ابتدا در نوار ابزار راست کلیک و Customize را انتخاب کنید. سپس در زبانه Commands  در اکسل از سمت چپ گزینه Format را انتخاب کنید و در سمت راست نوار ابزار باز شونده، Style را به کمک ماوس در کنار نوار ابزارهای دیگر قرار دهید.

فرض کنید که قصد دارید سبک سلول A1 را ذخیره کنید؛ ابتدا سلول A1 را انتخاب و سپس در نوار ابزار Style نام سبک را تایپ کنید و کلید Enter را بزنید تا سبک ذخیره شود. حال می توانید بعد از انتخاب سلولی دیگر، سبک موجود در این نوار ابزار را انتخاب کنید تا روی سلول بعدی اعمال شود.

امید واریم از آموزش اکسل امروز نیز مانند گذشته لذت برده باشید

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

ردیابی تغییرات در فایل های اکسل

برای انجام این کار ابتدا فایل مورد نظر را ایجاد کرده و آن را ذخیره نمایید. در سربرگ review گزینه track changes را انتخاب نمایید.

پس از انتخاب پنجره ای با عنوان highlight changes باز می شود. در این پنجره همه گزینه ها در حالت غیر فعال قرار دارند. تیک کنار گزینه track changes while editing. This also shares your workbook را فعال نمایید.

این گزینه امکان ردیابی تغییرات را برای شما فعال می کند. همچنین با فعال کردن این گزینه فایل ایجاد شده توسط شما برای استفاده دیگران به اشتراک گذاشته می شود.

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

در زیر عنوان highlight which changes  سه گزینه با نام های when – who-where  قرار دارند. گزینه when به معنی” چه وقتی” که خود شامل تعدادی گزینه است به شما اجازه می دهد که تغییرات فایل را در زمانهای مشخصی پیگیری نمایید.

گزینه since I last saved در اکسل  تغییرات ایجاد شده بعد از آخرین ذخیره فایل را برای شما مشخص خواهد کرد. گزینه all هرگونه تغییر و در هر زمانی را مشخص می نماید. گزینه not yet reviewed  تغییراتی که تا کنون بررسی نشده اند را مشخص می کند. گزینه since date… از تاریخی که شما تعیین می کنید تغییرات را نمایش می دهد.

گزینه دیگر بخش “who”  به معنی چه کسی می باشد. در این قسمت می توانید لیست همکارانی که فایل را با آنها به اشتراک گذاشته اید ببینید و تغییرات توسط یکی و یا همه آنها را زیر نظر داشته باشد.

آخرین بخش این قسمت نیز “where” به معنی “کجا” می باشد و می توانید یک ناحیه از کاربرگ و یا کل آن را برای زیر نظر داشتن تغییرات انتخاب نمایید.

در پایین پنجره نیز دو گزینه وجود دارند. گزینه highlight changes on screen تغییرات صورت گرفته در فایل را روی خود ناحیه تغییر یافته مشخص نموده و گزینه list changes on a new sheet لیست تغییرات رادر یک صفحه  جدید برای شما مشخص می کند.

پس از تنظیم کادر ردیابی تغییرات، زیر عنوان track changes گزینه دیگری به نام accept/reject changes فعال می شود که با کلیک بر روی آن می توانید تغییرات را پذیرفته و یا آنها را نادیده بگیرید.

با کلیک بر روی گزینه accept/reject changes کادر دیگری ظاهر می شود که مانند کادرپنجره قبلی گزینه های when – who-where در آن قرار دارد و می توانید تغییرات دریک تاریخ مشخص، توسط  یک فرد مشخص و در یک ناحیه مشخص را پذیرفته و یا آن را نپذیرید.

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

ابتدا تغییرات دلخواهی درفایل انجام داده و آن را ذخیره کرده و آن را می بندیم. بعد از باز کردن مجدد فایل و کلیک بر روی highlight changes همانطور که در شکل می بینید سلول های B3 و E5 با حاشیه آبی رنگ و یک مثلث آبی رنگ در گوشه آنها نشان می دهد که مقادیر این دو سلول تغییر یافته اند.

برای رد یا قبول تغییرات بر روی  track changes و سپس accept/reject changes کلیک کرده و کلید ok را فشار می دهیم. پنجره دیگری باز شده و اولین سلول تغییر یافته و مقادیر تغییر یافته و همچنین نام کاربری که این تغییرات را ایجاد کرده نمایش داده می شود. برای قبول تغییرات دکمه accept و یا accept all و یا برای رد و عدم پذیرش دکمه reject و یا reject all را فشار می دهیم.

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

 در نرم افزار اکسل در نگاه اول ممکن است که فرمول SUMPRODUCT خیلی مفید به نظر نرسد اما یک بار که روش کار اکسل را در کار با لیست ها یا آرایه ای از داده ها را درک کنید، اهمیت آن برای شما آشکار می گردد.

ساختار فرمول SUMPRODUCT و روش استفاده از آن

این فرمول ساختار بسیار ساده ای دارد

=SUMPRODUCT(list1,list2,…)

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

برای درک این مطلب به مثال ساده زیر توجه نمایید.

اگر شما دارای داده هایی به شکل {2,3,4} در یک لیست و {5,10,20} در لیست دیگر باشید و فرمول SUMPRODUCT را روی این لیست ها به کار ببرید نتیجه تابع عدد 120 خواهد بود (زیرا 2*5+3*10+4*20 برابر 120 خواهد شد)

SUMPRODUCT (A1:A3,B1:B3)

=2*5+3*10+4*20

=10+30+80=120

در اینجا نیز به نظر می رسد که این تابع کارایی زیادی نداشته باشد. اما اگر به خواندن ادامه دهید، نظر شما عوض خواهد شد.

فرمول SUMPRODUCT و آرایه ها در excel

فرض کنید شما جدولی از اطلاعات فروش دارید و ستونها با عنوان های  (نام – منطقه –  مقدار فروش) نامگذاری شده اند. شما قصد دارید بفهمید که تعداد محصولات فروخته شده توسط فروشنده ای به نام “رضا” چقدر است؟

این یک مسئله ساده است. برای این کار می توانید از یک فرمول SUMIF استفاده کنید به طوری که در آن criteria range برابر “نام” و sum range آن برابر “مقدار فروش” باشد.

اگر با فرمول SUMIF آشنایی ندارید به جمع شرطی در اکسل مراجعه نمایید.

اما اگر بخواهید مقدار فروش فروشنده ای به نام “سعید” را در منطقه غرب پیدا کنید چکار خواهید کرد؟

شما در اینجا دو راه دارید.

1. استفاده از فرمول های آرایه ای
2. استفاده از جدول محوری
   

اما راه سومی نیز وجود دارد. استفاده از تابع SUMPRODUCT

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

استفاده از SUMPRODUCT به عنوان یک فرمول آرایه ای

فرض کنید اطلاعات فروش ما در ناحیه A1:C10 قرار داشته باشند. در ستون A اسم فروشنده، در ستون B نام منطقه و در ستون C مقدار فروش قرار دارند.

فرمول SUMPRODUCT به صورت زیر نوشته می شود.

=SUMPRODUCT(–(A1:A10=”سعید“),–(B1:B10=”غرب“),C1:C10)

توضیح روش کار فرمول

 (–(A1:A10=”سعید“)

این قسمت در ستون نام فروشنده به دنبل کلمه “سعید” می گردد. در صورتی که آن را پیدا کند نتیجه عدد 1 در غیر این صورت عدد 0 خواهد بود.

(–(B1:B10=”غرب“)

این قسمت نیز عملی مشابه مرحله قبل را انجام می دهد. اما این بار روی ناحیه B1:B10 به دنبال عبارت “غرب” می گردد.

C1:C10

قسمت سوم نیز مقدار فروش را محاسبه می کند.

این سه قسمت باهم کار می کنند و به صورت مجزا کاری انجام نمی دهند.

 در نرم افزار excel فهرست شماره تلفن، کد ملی، نام و نام‌خانوادگی افراد و… 

از جمله فهرست‌هایی است که تکراری بودن اطلاعات در آنها اشکال اساسی دارد.

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

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

اطلاعات تکراری در اکسل:

1 برای مشخص کردن اطلاعات تکراری در هر ستون یا سطر از نرم‌افزار اکسل باید ابتدا سلول‌های موردنظر (کل ستون یا سطر موردنظر) را به‌حالت انتخاب درآورید.

2 از ریبون بالای نرم‌افزار به تب Home بروید و روی گزینه Conditional Formatting کلیک کنید.

3 از منوی به‌نمایش درآمده گزینه Highlight Cells Rules را انتخاب کرده و در نهایت روی duplicate Values کلیک کنید.

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

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

اطلاعات منحصر‌به‌فرد در excel:

1 روش مشخص کردن این اطلاعات نیز همچون مراحل قبل است. تنها تفاوت این قابلیت در مرحله 4 است که باید در کادر اول از سمت چپ گزینه Unique را انتخاب کنید

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

تابع Right در اکسل:

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

= RIGHT(address or text, number of characters)

به عنوان مثال عبارت I Love ITPro را در سلول A1 وارد می کنیم. حالا به منظور استخراج کلمه ITPro،  از فرمول Right استفاده می کنیم، بدین معنی که از سمت راست سلول A1، تعداد 5 کاراکتر را استخراج می کند:

تابع Left در excel:

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

=LEFT(address or text, number of characters)

به عنوان نمونه اگر المانهای مثال فوق را در این تابع به کار ببریم؛

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

تابع  MID در EXCEL:

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

=MID(address or text, start number, number of characters)

به عنوان نمونه، کارکتر 8 از سلول A1، حرف I از کلمه ITPro است که با مشخص نمودن تعداد 5 کاراکتر، کلمه ITPro با استفاده از تابع MID استخراج می گردد:

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

لحظه هایتان آسمانی