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

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

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

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

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

حذف سطرهای تکراری در اکسل

چنانچه در لیستی قصد داشته باشید، داده های تکراری را حذف نمائید، می توانید از زبانه Data و گذینه Remove Duplicates استفاده نمائید. در این صورت تمامی اطلاعات موجود تکراری در ستون انتخاب شده را حذف نموده و اطلاعات واحد و غیر تکراری در لیست باقی خواهند ماند.

در شکل فوق داده های آزمایشی را مشاده می کنین که دارای داده هایی تکراری هستند. پس از استفاده از گذینه RemoveDuplicate در زبانه Data نتیجه حاصل به صورت زیر خواهد بود.

توابع شرطی در اکسل

تابع IF

از این تابع برای تعیین مقدار سلول در صورت برقرار بودن یک شرط خاص استفاده می شود. شرایط در این تابع، با استفاده از عملگرهای مقایسه ای (رابطه ای) بررسی می شوند. ساختار این تابع به صورت زیر است:

IF (Logical_test,Value_if_true,Value_if_false)

ورودی های این تابع به این صورت است:

• Logical_test: شرط مورد نظر

• Value_if_true: مقدار سلول در صورت برقرار بودن شرط

• Value_if_false: مقدار سلول در صورت برقرار نبودن شرط

 

مثال: اگر در سلول B1 مقدار A1 بزرگ تر یا مساوی با ۱۰ باشد، مقدار «قبول» و در غیر اینصورت مقدار «مردود» قرار می گیرد.

اگر سلول A1 دارای مقدار ۱۸ باشد، مقدار B1 برابر با «قبول» خواهد بود.

A1=18

B1=IF(A1>=10; «قبول»;«مردود»);

تابع COUNTIF

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

COUNTIF (Range,Criteria)

در ساختار فوق، Range به معنای دامنه ارزیابی و Criteria به معنای شرط مورد نظر است.

در این تابع سلول های موجود در دامنه ارزیابی با شرط مورد نظر ارزیابی شده و در صورتی که حایز شرط باشند، شمرده می شوند.

 

مثال: در تصویر زیر تعداد سلول های A1 تا A10 که از 10 بزرگترند، شمارش شده اند.

 

 

توابع ریاضی در اکسل

تابع SUM

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

SUM (Number 1, Number 2,…)

ورودی های این تابع یک مقدار عددی است.

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

 

مثال برای سلول های مجاور:

SUM (A1:B3)

 

 

مثال برای سلول های غیر مجاور:

SUM (A1,A3,B2)

 

 

تابع Average

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

Average (Number1,Number2,Number3,….)

ورودی های این تابع مقادیر عددی است.

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

تابع MAX

این تابع بیشترین مقدار بین اعداد را مشخص می کند. ساختار این تابع به صورت زیر است:

MAX (Number1,Number2,Number3,….)

ورودی های این تابع مقادیر عددی است.

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

تابع MIN

این تابع کمترین مقدار بین اعداد را مشخص می کند. ساختار این تابع به صورت زیر است:

MIN (Number1,Number2,Number3,….)

ورودی های این تابع مقادیر عددی است.

با استفاده از این تابع می توان کمترین مقدار را در سلول های مجاور یا غیر مجاور پیدا کرد.

 

 

تابع Count

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

COUNT (VALUE1,VALUE2,VALUE3,….)

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

 

 

تابع ROUND

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

ROUND (Number,Num_digits)

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

 

مثال: در تصویر زیر، عدد اعشاری 12.12324 در سلول E6 وارد شده است. این عدد در سلول E7 با استفاده از تابع ROUND تا دو رقم اعشار گرد شده است.

 

 

تابع INT

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

INT (Number)

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

 

مثال: در تصویر زیر، جزء صحیح عدد اعشاری 12.12324 که در سلول E6 قرار دارد، در سلول E7 نشان داده شده است.

 

ایجاد و ویرایش نمودار در اکسل

برای اینکار ابتدا داده ها را در صفحه گسترده وارد کرده و آنها را انتخاب می کنیم. سپس از زبانه Insert و از بخش Chart، نمودار مورد نظرمان را انتخاب می کنیم. همانطور که می دانید، در ریاضی می توان برای داده ها نمودارهایی از قبیل میله ای، دایره ای، خطی، ستونی و... درج کرد. اکسل نیز این نمودارها را پشتیبانی می کند. ما برای این مثال از یک نمودار ستونی (Column) استفاده می کنیم.

 

 

با کلیک بر روی دسته نمودارهای Column کادری باز می شود که در آن انواع نمودارهای ستونی دیده می شود. این نمودارها عبارتند از: نمودارهای ستونی دو بعدی، ستونی سه بعدی، استوانه ای، مخروطی و هرمی.

 

 

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

 

 

بعد از درج نمودار، سه زبانه به زبانه های اکسل اضافه می شوند. این زبانه ها عبارتند از: Design و Layout و Format.

 

کنترل اعتبار داده‌های ورودی

اکسل قادر است که اعتبار یا درستی داده ها را در هنگام وارد کردن آنها، کنترل کند.

برای اینکار باید قبل از ورود داده ها، برای سلول های مربوطه، «شرایط اعتبار» را تعریف کرد. برای کنترل اعتبار داده های ورودی، از زبانه Data بر روی گزینه Data Validation کلیک کرده و از منوی باز شده گزینه Data Validation… را انتخاب کنید.

اکنون کادر Data Validation باز می شود. با استفاده از این کادر می توان شرایط مجاز ورود داده ها، پیغام های ورودی و پیغام های خطا را تعریف کرد.

 

 

برای تعریف شرایط مجاز در پنجره فوق، از زبانه Settings استفاده می شود.

گزینه Allow در این قسمت، لیستی از شرایط مختلف را ارائه می دهد.

 

 

در این پنجره، از گزینه Any Value برای دریافت بدون قید و شرط تمام داده ها، از گزینه  Whole Number برای دریافت اعداد، از گزینه Decimal برای دریافت اعداد اعشاری، از گزینه List برای دریافت اقلام نامبرده، از گزینه  Date برای دریافت تاریخ، از گزینه Time برای دریافت ساعت، از گزینه Text length برای دریافت متن با تعداد کاراکترهای مشروط و از گزینه Custom نیز برای دریافت محتوای برابر با فرمول استفاده می شود.

 

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

 

 

پس از تعریف شرایط مجاز، می توانید یک پیغام تعریف کنید که در حین ورود داده ها، شرایط مجاز را به کاربر یادآوری کند.

برای این منظور بر روی زبانه Input Message کلیک کرده و در قسمت Title عنوان پیغام و در قسمت Input message متن پیغام را وارد کنید.

 

 

از زبانه آخر (Error Alert) نیز برای نوشتن پیغام خطا استفاده می شود. با تعریف محدوده مجاز داده ها، در صورت ورود داده اشتباه، این خطا توسط اکسل به کاربر اعلام خواهد شد. برای نوشتن پیغام خطا به دلخواه خود در قسمت Title عنوان پیغام و در قسمت Message Error متن پیغام را وارد کنید.

 

 

فیلتر کردن اطلاعات

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

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

برای اینکار یکی از سلول های لیست را انتخاب کرده و از زبانه Data بخش Sort & Filter بر روی دکمه Filter کلیک کنید.

 

اگر روی فلش پایین رونده در ستون رشته تحصیلی کلیک کنید، خواهید دید که در بخش Text Filters گزینه Select All انتخاب شده است. با کلیک در کادر انتخاب گزینه Select All انتخاب فعلی لغو شده و می توانید تنها گزینه «کامپیو‌تر» را انتخاب کنید.

 

پس از کلیک روی دکمه OK، داده های لیست فیلتر شده و تنها رکوردهایی که از «کامپیو‌تر» هستند، نمایش داده می شوند.

 

 

مرتب کردن سطر‌ها و ستون های لیست

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

 

برای مرتب کردن این لیست بر اساس ستون نام خانوادگی، ابتدا یکی از سلول های این ستون را انتخاب کرده و سپس زبانه Data و در بخش Sort & Filter روی دکمه Sort A to Z کلیک می کنیم.

از آنجا که اکسل به خوبی ساختار لیست را تشخیص می دهد، رکوردهای آن را براساس ستون «نام خانوادگی» از «الف» تا «ی» مرتب می کند و نتیجه آن لیست زیر خواهد بود. همانطور که در تصویر زیر می بینید، دکمه Sort Z to A نیز حروف را از «ی» تا «الف» مرتب می کند.

 

 

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

 

برای اینکار یکی از سلول های لیست را انتخاب کرده و از زبانه تب Data گروه Sort & Filter روی دکمه Sort کلیک می کنیم تا کادر Sort باز شود.

 

 

در تصویر فوق بخش Sort by ستونی است که مرتب سازی براساس داده های آن انجام می شود. بخش Values نیز به عنوان مبنای مرتب سازی (مقدار سلول، رنگ سلول و...) در نظر گرفته می شود و از بخش Order نیز برای صعودی یا نزولی بودن مرتب سازی استفاده می شود.

 

اکنون در بخش Sort by ستون مبنای مرتب سازی را مشخص می کنیم. در این منو به دلیل تشخیص لیست توسط اکسل، عناوین ستون ها دیده می شود. گزینه «رشته تحصیلی» را از منوی باز شده انتخاب می کنیم.

 

 

برای تعیین ستون دوم که باید مبنای مرتب سازی قرار گیرد، روی دکمه Add Level کلیک می کنیم. ردیف جدیدی با عنوان Then by به کادر Sort اضافه می شود.

 

 

بار دیگر منوی Column را از ردیف Then by باز کرده و این بار گزینه «نام خانوادگی» را انتخاب می کنیم.

 

 

در پایان روی دکمه OK کلیک می کنیم. نتیجه مرتب سازی براساس دو ستون «رشته تحصیلی» و «نام خانوادگی» مانند لیست زیر خواهد شد.

 

 

پرینت اطلاعات در اکسل

تعیین ناحیه چاپ

گاهی اوقات در یک برگه اطلاعات زیادی وارد می شود که در مواردی مایل به چاپ همه آنها نیستیم. با استفاده از یک امکان اکسل، می توانیم ناحیه ای را به عنوان «ناحیه چاپ» مشخص کرده و فقط آن را چاپ کنیم. برای اینکار ابتدا ناحیه مورد نظر را از برگه انتخاب کرده و سپس از زبانه Page Layout و از بخش Page Setup روی دکمه Print Area کلیک کنید. سپس از دو گزینه موجود، گزینه Set Print Area را انتخاب کنید. بدین ترتیب ناحیه انتخابی چاپ مشخص می شود.

 

 

حذف ناحیه چاپ

برای حذف ناحیه چاپ شده نیز کافیست که مسیری که در بالا توضیح داده شد را طی کرده و از منوی Print Area گزینه Clear Print Area را انتخاب کنید.

 

جدا کردن صفحات با استفاده از امکان Page Break

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

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

 

 

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

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

 

 

برای اینکار نخست سلولی را که قرار است به عنوان اولین سلول صفحه درج شود را انتخاب می کنیم. در این مثال باید سلول «LCD» انتخاب شود.

سپس از زبانه Page Layout و در بخش Page Setup روی دکمه Breaks کلیک کرده و از منوی باز شده، گزینه Insert Page Break را انتخاب می کنیم. به این ترتیب در این محل یک Page Break جدید ایجاد می شود.

 

 

برای حذف Page Break ایجاد شده، ابتدا سلول اول صفحه دوم را انتخاب می کنیم و سپس از منوی Breaks گزینه Remove Page Break را انتخاب می کنیم. گزینه Reset All Page Breaks کلیه Page Breakهای موجود را حذف می کند.

 

تنظیم حاشیه و جهت صفحات

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

 

 

با استفاده از اکسل می توان اندازه این فضا را تعیین کرد. برای اینکار از زبانه Page Layout بخش Setup Page روی دکمه Margins کلیک کنید. این منو سه حاشیه از پیش تعیین شده را نشان می دهد. گزینه اول (Normal) بیانگر حاشیه عادی، گزینه دوم (Wide) بیانگر حاشیه پهن و گزینه سوم (Narrow) بیانگر حاشیه باریک است.

با انتخاب آخرین گزینه (Custom Margins…) نیز می توان حاشیه را به اندازه دلخواه تنظیم کرد.

 

یکی دیگر از تنظیمات صفحات در اکسل، تعیین جهت صفحه است. صفحات یک برگه هم به صورت عمودی و هم به صورت افقی قابل چاپ هستند. برای تعیین جهت صفحه، در زبانه Page Layout و از بخش Page Setup روی دکمه Orientation کلیک کنید. گزینه Portrait کاغذ را عمودی و گزینه Landscape آن را افقی می کند.

 

 

چاپ عناوین و خطوط راهنما

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

به عنوان مثال، لیستی حاوی ستون های زیر را در نظر بگیرید:

 

 

اگر صفحه چاپی دوم این لیست فاقد عنوان ستون باشد، تشخیص اینکه اطلاعات مربوط به کدام ستون است، دشوار خواهد بود. برای اینکار از زبانه Page Layout و در بخش Page Setup روی دکمه Print Titles کلیک می کنیم. در این صورت پنجره Page Setup مانند تصویر زیر باز خواهد شد.

 

 

اکنون در کادر Rows to repeat at top کلیک کرده و سپس روی شماره سطر تیتر مورد نظر در برگه کلیک می کنیم. در این صورت آدرس سطر به طور خودکار در این کادر قرار می گیرد. بدین ترتیب یک سطر را به عنوان تیتر چاپ تعیین کرد‌ه ایم و هنگام چاپ برگه مشاهده می کنیم که این سطر در بالای لیست همه صفحات چاپ می شود.

 

خطوط راهنما

هنگام چاپ یک صفحه گسترده، خطوط خاکستری رنگی که به عنوان کادر اطراف سلول در برگه مشاهده می شوند، به طور پیش فرض چاپ نمی شوند. برای چاپ این خطوط از زبانه Page Layout گروه Sheet Options گزینه Print از قسمت Gridlines را انتخاب می کنیم.

 

 

تغییر اندازه کاغذ

نرم افزار اکسل به طور پیش فرض اندازه کاغذ را به حالتی که آن را «Letter» تعریف کرده، تعیین می کند. برای تغییر این اندازه و همچنین مشاهده اندازه های دیگر، از زبانه Page Layout و از بخش Page Setup گزینه Size را کلیک کنید.

 

 

 

پیش نمایش چاپ

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

 

 

برای خارج شدن از محیط Print Preview در زبانه Print Preview روی دکمه Close Print Preview کلیک کنید.

 

 

چاپ برگه

برای چاپ برگه، همان مسیر پیش نمایش را رفته و این بار به جای انتخاب گزینه Print Preview، گزینه Print را انتخاب کنید. در این صورت کادری مانند کادر زیر باز می شود.

 

 

در بخش Print range انتخاب گزینه All باعث می شود که تمام صفحات چاپ شود. اما با انتخاب بخش  Page(s) می توانید صفحات مورد نظر را با شماره مشخص کنید. برای اینکار شماره اولین صفحه را در From و شمار آخرین صفحه را در To وارد کنید.

 

در بخش  Print What می توان موضوع چاپ را تعیین کرد. بخش Selection برای چاپ سلول های انتخاب شده، بخش Active Sheet(s) برای چاپ برگه یا برگه های فعال، بخش Entire Workbook برای چاپ کل صفحه گسترده استفاده می شود.

 

با انتخاب گزینه Ignore print areas نیز می توان ناحیه چاپ تعیین شده را نادیده گرفت.

با استفاده از گزینه Copies نیز می توان تعداد نسخه های چاپی را مشخص کرد.

معرفی زبانه های اصلی در نرم افزار اکسل

معرفی زبانه های اصلی

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

 

زبانه Home

 

 

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

زبانه Insert

 

 

با استفاده از مجموعه گزینه های زبانه Insert، می توانید مواردی  از قبیل جدول، عکس، نمودار و... را در یک سند درج کنید. به طور کلی برای «درج» هر چیزی در یک سند اکسل، از این زبانه استفاده می شود.

زبانه Page Layout

 

 

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

زبانه Formulas 

 

 

از این زبانه برای درج فرمول ها و توابع ریاضی استفاده می شود. در این زبانه یکسری از توابع ریاضی به صورت پیش فرض قرار دارد که در پروژه های پیچیده ریاضی و حسابداری از آنها استفاده می شود.

زبانه Data

 

 

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

زبانه Review

 

 

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

زبانه View 

 

 

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