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

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

 

قبل از بیان روش‌های تبدیل آدرس‌های نسبی به مطلق، ابتدا به صورت خلاصه با این دو مدل آدرس‌دهی آشنا می‌شویم:

• آدرس‌ نسبی (Relative Address):

آدرس نسبی یعنی آدرسی که هنگام کپی کردن و جابجایی ثابت نماند و تغییر کند. به طور مثال اگر درون سلولی فرمول C4*10 را بنویسیم و این سلول را چه بصورت دستی و چه با استفاده از قابلیت AutoFill، در سه سلول پایین‌تر کپی کنیم فرمول قبلی به C7*10 تغییر پیدا می‌کند. این تغییر به علت نسبی بودن فرمول است که آدرس C4 به علت جابجا شدن به سه سلول پایین‌تر به C7 تبدیل می‌شود.

 

• آدرس مطلق (Absolute Address):

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

=$C$4

نکته: علاوه بر فرم‌های فوق الذکر، آدرس‌های ترکیبی یا مختلط (Mixed Reference) نیز داریم که بصورت نیمه‌مطلق - نیمه‌نسبی هستند:

الف) حالت مطلق سطری (Row absolute) مثل C$4: در این حالت فقط سطر به صورت مطلق ارجاع داده شده است. یعنی در صورت کپی شدن این فرمول، نام ستون یعنی ستون C بسته به محل جدید کپی شدن، می‌تواند به صورت نسبی تغییر کند، اما شماره سطر یعنی سطر شماره 4 که قبل از آن $ آمده، همیشه ثابت خواهد ماند.

ب) حالت مطلق ستونی (Column absolute) مثل o$C4: در این حالت فقط ستون به صورت مطلق ارجاع داده شده است. یعنی در صورت کپی شدن این فرمول، شماره سطر یعنی سطر شماره 4، بسته به محل جدید کپی شدن، می‌تواند به صورت نسبی تغییر کند، اما نام ستون یعنی ستون C که قبل از آن $ آمده، همیشه ثابت خواهد ماند.

 

---

• روش‌های تبدیل آدرس‌های نسبی به مطلق و بالعکس دراکسل:

روش ۱: درج دستی علامت $

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

---------------------------------------

روش ۲: با استفاده از کلید F4

اگر قبل یا بعد یا حتی وسط یک آدرس نسبی (C4) کلیک کنید یا چنانچه آنرا انتخاب کنید و سپس کلید F4 را یک بار فشار دهید آدرس مورد نظر به آدرس مطلق (o$C$4) تبدیل می‌شود. اگر همین کلید را برای بار دوم فشار دهید از حالت مطلق به حالت مطلق سطری (C$4) تبدیل می‌شود و اگر برای بار سوم نیز این کلید را فشار دهید از حالت مطلق سطری به حالت مطلق ستونی (o$C4) در می‌آید. فشردن کلید F4 برای بار چهارم موجب می‌شود آدرس به حالت اولیه یعنی نسبی (C4) برگردد.

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

-----------------------------------------------------

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

استفاده از کلید F4 برای تبدیل آدرس‌های موجود در یک سلول بسیار کاربردی است اما اگر بخواهید اینکار را برای تعداد زیادی سلول انجام دهید نمی‌توانید چند سلول را انتخاب کرده و از کلید F4 استفاده کنید چون این کار عملی نیست. برای این حالت بهتر است از کد ماکرو استفاده کنید.

انجام عملیات ریاضی در یک محدوده حاوی خطا در اکسل

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

 

روش‌های زیر به طور نمونه برای انجام عملیات جمع توضیح داده شده است. هدف محاسبه مجموع اعداد محدوده B2:B6 در شکل زیر است که در سلول B3 خطا وجود دارد. همانطور که مشاهده می‌کنید خروجی تابع Sum بصورت خطا ظاهر شده است.

 

🔵 روش اول) استفاده از یک ستون کمکی در اکسل

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

=IF(ISNUMER(B2),B2,0)

 

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

---

🔵 روش دوم) استفاده از تابع  Sumifدراکسل

با استفاده از تابع Sumif که در این مطلب معرفی شده است، می‌توان کاری کرد که در حین محاسبه جمع از خطا صرف نظر شود. به طور مثال در مثال فوق خطای !VALUE# در سلول B3 وجود دارد. باید در تابع Sumif از این خطا صرف نظر شود مانند فرمول زیر:

=SUMIF(B2:B6,"<>#VALUE!")

خروجی این فرمول برابر با ۱۳ می‌شود.

فرمول فوق زمانی کاربرد دارد که فقط خطای !VALUE# وجود داشته باشد اما چون ممکن است چندین نوع خطا وجود داشته باشد باید از معیاری استفاده کرد که همه خطاها را صرف نظر کند مانند فرمول زیر که برای همه خطاها قابل استفاده است:

=SUMIF(B2:B6,"<="&9.99E+307)

 

---

🔵 روش سوم) استفاده از فرمول‌نویسی آرایه‌ای

روش بعد، استفاده از فرمول‌نویسی آرایه‌ای است که در این مطلب توضیح داده شده است. در این نوع فرمول‌نویسی پس از نوشتن فرمول باید به جای اینتر، کلیدهای Ctrl + Shift+ Enter را همزمان بفشارید. در فرمول زیر در حین جمع سلول‌ها تابع IFERROR خطاها را به صفر تبدیل می‌کند.

=SUM(IFERROR(B2:B6,0))

 

تابع IFFERROR در اکسل ۲۰۰۷ و بالاتر موجود است، اما اگر از اکسل ۲۰۰۳ استفاده می‌کنید باید از فرمول‌های زیر استفاده نمایید. (حتما در انتها کلیدهای Ctrl+Shift+Enter را فشار دهید)

=SUM(IF(ISERROR(B2:B6),0,B2:B6))

=SUM(IF(ISNUMBER(B2:B6),B2:B6,0))

---

🔵 روش جهارم) تابع AGGREGATE (اکسل ۲۰۱۰ به بعد)

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

=AGGREGATE(9,6,B2:B6)

 

---

🔵 روش پنجم) ماکرونویسی در excel

 

آماده سازي كاربرگها براي چاپ در excel

قبل از چاپ بايد تنظيماتي بر روي فايل مثلاً سايز كاغذ ، حاشيه ها ، سر صفحه و پا صفحه ، طريقه چاپ و ... را تعيين كنيم. براي انجام اين تغييرات از Page Setup استفاده ميكنيم. براي فعال كردن Page Setup كافي است از منوي فايل ، Page setup را انتخاب كنيم. با انتخاب اين گزينه پنجره اي باز ميشود كه شامل Tab هاي زير است :

• Tab Page :

اين Tab شامل قسمتهاي زير است :

1- Orientation : در اين قسمت جهت قرار گرفتن متن در كاغذ را تعيين ميكنيم. Portrait براي چاپ عمودي يا Landscape براي چاپ افقي است.

2- Scaling : توسط اين قسمت ميتوانيم اندازه كاربرگ خروجي را بدون هر گونه تغيير بر روي نمايشگر بزرگ يا كوچك كنيم. اين قسمت شامل گزينه هاي زير است :

الف- Adjust to : ميتوانيم ميزان بزرگي را بر اساس درصدي از بزرگي تعيين كنيم. حداقل اين ميزان 10 و حداكثر ان 400 ميباشد.

ب- Fit to : در اين قسمت ميتوانيم تعيين كنيم كه اطلاعات ما در چند صفحه گنجانده شود.

3- Paper Size : اندازه کاغذ را ميتوان تعيين کرد.

4- Print Quality : قدرت تفکيک پذيري يا كيفيت چاپ را تعيين ميكنيم که واحد آن DPI(Dot Per Inch) است.

5- First Page Number : شماره گذاري را از صفحه مشخص شده شروع ميکند.

• Tab Margins :

توسط اينTab ميتوان حاشيه هاي صفحه ، سر صفحه و پا صفحه را تعيين كرد. اين Tab شامل قسمتهاي زير است :

1- Tab , Bottom , Left, Right : فاصله حاشيه هاي راست , چپ , پايين و بالا را تعيين ميکند.

2- From Edge : محل سر صفحه يا پاصفحه را تعيين ميکند ( بايد کمتر از حاشيه هاي بالا يا پايين باشد)

3- Center On Page : مرکز فعلي را به را به طور افقي يا عمودي نسبت به حاشيه ها تعيين ميکند.

4- Preview : پيش نمايش تنظيمات در اين محدوده نمايش داده ميشود.

• Tab Header/ Footer:

توسط اين Tab ميتوانيم سر صفحه و پا صفحه ها را وارد کنيم.

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

اين Tab شامل قسمتهاي زير است :

1- کادرهاي Header , Footer :

در اين کادر يک سري Header ها و Footer هاي آماده و متداول مثل شماره صفحه , نام کاربرگ، نام کاربر، تاريخ و ترکيب اين موارد وجود دارد که ميتوانيم با توجه به نياز يکي از آنها را انتخاب کنيم.

2- دکمه هاي Custom Header , Custom Footer :

توسط اين دکمه ها ميتواتنيم Header ها و Footer هاي شخصي ايجاد کنيم. با کليک بر روي اين دکمه ها پنجره اي باز ميشود که بايد محل استقرار پاصفحه يا سر صفحه را بوسيله ماوس انتخاب کرده ( يکي از سه قسمت Left, Center, Right). قسمت سمت چپ، چپ چين خواهد شد، محتواي قسمت مياني نسبت به ميانه قسمت تنظيم خواهد شد.

و قسمت راست، راست چين خواهد شد. متن مورد نظر را در يکي از اين سه قسمت تايپ ميکنيم ( براي رفتن به خط جديد کليد Enter را بايد فشار داد).

دکمه هايي که در ميانه صفحه براي قالب بندي صفحه قرار داده شده اند عبارتند از :

Format Font : با فشردن اين دکمه پنجره Font باز شده و ميتوان فرمت نوشته ها را تغيير داد.

Insert Page Number : براي درج شماره صفحه

Insert Number Of Pages : براي درج تعداد صفحات

Insert Date : براي درج تاريخ جاري

Insert Time : براي درج زمان جاري

Insert Book Name :براي درج نام Book

Insert Sheet Name : براي درج نام کاربرگ

• Tab Sheet در اکسل:

اين Tab شامل قسمتهاي زير است :

1- Print Area : ميتوانيم ناحيه اي از کاربرگ را براي چاپ انتخاب کنيم يا آدرس آن را تايپ کنيم.

2- Print Titles : در اين قسمت ميتوانيم يک سطر يا یک ستون را به عنوان سطر يا ستوني که ميخواهيم در تمامي صفحات به عنوان سطر یا ستون عنوان تکرار شود انتخاب کنيم.

3- Print : اين قسمت شامل گزينه هاي زير است :

الف- Gridline : به طور پيش فرض خطوط شطرنجي محيط Excel در نسخه چاپي ظاهر نميشود. براي ظاهر شدن اين شبکه کافي است اين گزينه را تيک بزنيم.

ب- Black and White : همه چيز را با رنگ سياه و زمينه سفيد چاپ ميکند. و رنگها بدون سايه خواهند بود.

ج- Draft Quality : با انتخاب اين گزينه قدرت تفکيک پذيري کمتر شده و زمان چاپ کاهش مي يابد.

د- Row And Column Heading : به طور پيش فرض عنوان سطر ها ( 1و2و3و... ) و عنوان ستونها ( A, B, C, …)در نسخه چاپي ظاهر نميشود. ولي اگر اين گزينه را تيک بزنيم اين عناوين در نسخه چاپي , چاپ ميشوند.

هـ- Comments : در اين فسمت ميتوانيم نحوه چاپ توضيحات را تعيين کنيم :

• None : توضيحات چاپ نميشوند.

• At End Of Sheet : در انتهاي هر Sheet توضيحات چاپ ميشوند.

• As Display on Sheet : در محلي که در Sheet قرار دارند چاپ ميشوند.

4- Page Order : ترتيب چاپ صفحه ها را براي کاربرگهاي چند صفحه اي انتخاب ميکنیم.

نمودارهاي منحصر‌به ‌فرد و حرفه ای در اکسل

نرم‌افزار اكسل (Excel) يكي از بهترين ابزارهاي رسم نمودار و گراف به‌شمار مي‌رود و روش كار با آن نيز بسيار آسان است. انواع نمودار ميله‌اي، خطي، نقطه‌اي و... از جمله ساده‌ترين نمودارهايي است كه مي‌توانيد با استفاده از اين نرم‌افزار نسبت به رسم آنها اقدام كنيد.

 در excelاين نمودارها در حالت پيش‌فرض از رنگ‌بندي و طرح‌هاي پيش‌ساخته‌اي بهره‌مند است كه جذابيت بسياري دارد اما گاهي اوقات اين جذابيت‌ها با كمي خلاقيت، بيشتر هم مي‌شود. يكي از اين خلاقيت‌ها استفاده از تصاوير يا علائم به‌جاي رنگ‌هاي ثابت براي رسم نمودارهاست.

به‌عنوان مثال فرض كنيد در يك نمودار قصد داريد مشخص كنيد كه از تعداد صد مهره رنگي، چه ‌تعداد آبي، چه تعداد قرمز، چه تعداد زردو چه تعداد نارنجي و چقدر مشكي است. ساده‌ترين راه براي رسم نموداري شامل اين اطلاعات اين است كه دو ستون X,Y در اختيار داشته باشيد و روي ستون Y رنگ‌ها را مشخص كنيد و ستون X را نيز به تعداد مهره‌هاي هر رنگ اختصاص دهيد

اما با كمي خلاقيت مي‌توانيد به‌جاي استفاده از يك رنگ ثابت براي نمايش مهره‌ها، از شكل آنها كمك بگيريد و در نهايت نيز نمودار به‌نمايش درآمده را طبق اعداد مشخص شده در محور X‌ها مرتب كنيد.

براي استفاده از اين قابليت بايد به روش زير عمل كنيد:

1ـ ابتدا نرم‌افزار اكسل را اجرا كنيد (در اين آموزش ما اكسل 2013 را اجرا كرده‌ايم و گزينه‌هاي ذكر شده مطابق با منوهاي موجود در اين نرم‌افزار است؛ اما شما مي‌توانيد با كمي تغيير همين مراحل را در نسخه‌هاي ديگر اين نرم‌افزار نيز مورد استفاده قرار دهيد).

2ـ در ستون‌هايي مجزا، تنوع رنگ‌ها و تعداد مهره موجود براي هر رنگ را مشخص كنيد.

3ـ سلول‌هاي دربردارنده اطلاعات موردنظر براي رسم نمودار را انتخاب كنيد (تمامي سلول‌هاي موجود در دو ستون مشخص شده در مرحله دو را انتخاب كنيد)

4ـ از ريبون بالاي نرم‌افزار به تب INSERT برويد و پس از كليك روي Insert Bar Chart گزينهClustered Bar را از بخش 2-D Bar انتخاب كنيد.

5ـ تا اين مرحله از كار شما يك نمودار ساده در اختيار خواهيد داشت كه در محورهاي X,Y آن تعداد مهره‌هاي هر رنگ و نام رنگ‌ها مشخص شده است.

6ـ حال بايد روي يكي از نوارهاي به‌نمايش درآمده در نمودار كليك راست كرده و گزينه Format Data Series را انتخاب كنيد.

7- در بخش Series Options با كليك روي آيكون سطل رنگ (Fill and Line)، از منوي به ‌نمـــــــــايش درآمــده گزينه Picture or texture fill را انتخاب كنيد.

8ـ پس از انتخاب گزينه فوق از بخش Insert picture from روي يكي از گزينه‌هاي Online, File, Clipboard كليك كنيد. (مي‌توانيد تصاوير را از روي رايانه فراخواني كنيد، از حافظه موقت ويندوز آنها را به‌كار بگيريد يا حتي به‌صورت آنلاين تصوير موردنظرتان را پيدا كنيد)

9ـ در اين مرحله بايد تصوير موردنظرتان را در برنامه فراخواني كنيد. به‌عنوان مثال شما مي‌توانيد از گزينه Online كمك بگيريد و عبارت red ball را در كادر جستجو وارد كنيد تا تصاويري از مهره‌هايي به رنگ قرمز براي شما يافت شود. با انتخاب تصوير موردنظر و كليك روي گزينه Insert آن را در نرم‌افزار فراخواني كرده و براي رفع مشكل اندازه تصوير نيز گزينه Stack را فعال كنيد.

10ـ چنانچه مراحل را بدرستي به انجام رسانده باشيد بايد يكي از نوارهاي نمايش‌ دهنده مهره‌ها به تصويري از مهره‌هاي قرمز رنگ تغيير كرده باشد. اين نوار مي‌تواند مربوط به رنگ قرمز باشد. تصوير انتخاب شده براحتي بيننده را قادر مي‌سازد تا درك كند چه مهره‌اي، به چه رنگي و در چه تعدادي موجود است. با تكرار عمليات ذكر شده در مراحل 6 تا 9 مي‌توانيد نوارهاي ديگر را نيز با مهره‌هايي به همان رنگ‌ها مشخص كنيد.

11ـ معمولا يك pictograph (نمودار علائم تصويري) به‌صورت مرتب شده از مقادير بزرگ به كوچك نمايش داده مي‌شود. چنانچه شما هم مي‌خواهيد يك pictograph واقعي و مرتب را در اختيار داشته باشيد مي‌توانيد سلول‌هاي مربوط به تعداد مهره‌ها را در نرم‌افزار اكسل انتخاب كرده و از كليك راست روي آنها از بخش Sort گزينه Sort Largest to Smallest را انتخاب كنيد.

12ـ مشاهده مي‌كنيد كه pictograph شما از مقادير بزرگ به كوچك مرتب شده و با جذابيت بيشتري به‌نمايش درآمده است.

انواع نمودار در اکسل

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

نمودار ستونی Column Chart

نمودار ستونی به عنوان ساده ترین نوع نمودار شناخته می‌شود و جهت نمایش مقادیر یک ویژگی در بازه های زمانی متفاوت (مثلا تعداد ازدواج های صورت گرفته در ماههای متفاوت یک سال)، یا به تفکیک گروههای متفاوت (میزان ازدواجهای صورت گرفته در سال ۱۳۹۴ به تفکیک استانها) از آن استفاده می‌شود. نکته مورد توجه در این نوع نمودار آن است که تمامی ستونها باید هم واحد باشند و تعداد میله ها بیش از اندازه نباشند. در غیر این صورت نمودار شفافیت خود را از دست نمیدهد.

نمودار ستونی خود به دو حالت عمودی (Column Chart) و افقی (Bar Chart) تقسیم می‌شود که هر یک از آنها شامل حالات تجمعی (Stacked Chart) و خوشه‌ای (Clustered Chart) می‌باشند.                                                        

نمودار خطی Line Chart

 

در صورتی که نقاط ماکسیمم هر میله در نمودار میله ای را با یک خط به یکدیگر متصل کنیم، نمودار میله ای حاصل می‌شود که زمینه های کاربردی همچون نمودار ستونی دارد. در زمانهایی که بازه زمان مورد بررسی مربوط به داده ها بسیار زیاد می‌باشد (مثلا میزان نرخ تورم از سال ۱۳۵۰ تا ۱۳۹۰) این نمودار به عنوان بهترین گزینه می‌باشد.

 

در محیط اکسل حالتی از نمودار خطی وجود دارد که در آن، مساحت زیر خط به صورت رنگی نمایش داده می‌شود که به آن نمودار سطحی (Area Chart) می‌گویند.

 

نمودار دایره‌ای Pie Chart

 

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

 

لازم به ذکر است که در حالاتی که تعداد قسمتهای نمودار دایره‌ای از ۹ قسمت فراتر رود، این نمودار کارایی خود را از دست خواهد داد.

 

نمودار پراکندگی Scatter Plot

 

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

 

 
نمودار حبابیBubble Chart

 

این نمودار همانند نمودار پراکندگی‌می باشد، با این تفاوت که توانایی نمایش سه ویژگی به صورت همزمان را دارا می‌باشد به گونه‌ای که دو ویژگی X و Y بر روی محورها مشخص می‌گردد و ویژگی سوم با اندازه حباب مشخص می‌شود.

 

 

 

نمودار رادار Radar Chart

 

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

 

نمودار شمعی Candel Chart

 

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

 

نمودار جعبه ای Box and whisker

این نمودار به منظور بررسی و مقایسه وضعیت دو یا چند جامعه (گروه) متفاوت از داده‌ها از نظر پارامترهای مکانی (میانگین، میانه) و پراکندگی (واریانس) مورد استفاده قرا میگیرد. در نسخه های قبلی اکسل این نمودار به صورت آماده وجود ندارد ولی میتوان با افزودن Error Bar به نمودارهای ستونی آن را ایجاد نمود ولی در اکسل ۱۶ (اکسل ۲۰۱۶) این نمودار به صورت آماده در تب Insert و قسمت نمودارها اضافه شده است.

 

نمودار هیستوگرام Histogram Chart

 

این نوع نمودار جهت نموایش نوع پراکندگی مقادیر در یک جامعه (گروه) مورد استفاده قرار میگیرد و هدف از استفاده از ان تعیین مرکزیت دادهها و حالت پراکندگی آنها می‌باشد. این نوع نمودار در ورژنهای قدیمی اکسل از طریق افزونه Analysis Toolpak  قابل ترسیم است ولی در نسخه ۱۶ اکسل، این نمودار به مجموعه نمودارهای اکسل اضافه شده است.

 

نمودار نقشه درختی TreeMap Chart

 

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

 

نمودار خورشیدی SunBurst Chart

 

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

 

نمودار آبشاری Waterfall Chart

 

این نمودار به منظور بررسی مقادیر تجمعی از یک ویژگی به کار گرفته می‌شود که در نسخه های قدیمی اکسل با استفاده از حالت تجمعی نمودار ستونی، این نمودار ترسیم می‌شود ولی در نسخه ۲۰۱۶ این نمودار به مجموعه نمودارهای پیش فرض اکسل اضافه شده است.

 

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

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

 قالب بندی شرطی درEXCEL

• ابتدا سلول های مورد نظر خود را که قصد دارید قالب بندی بر روی آنها اعمال شوند را انتخاب نمایید .
• از تب Home و از گروه Style گزینه Coditional Formmating را انتخاب تا منوی کشویی نمایش داده شود .
• گزینه Highlight Cells Rules را انتخاب زیر منوی نمایش داده شده شامل چندین قانون میباشد . به عنوان مثال اولین قانون برای قالب بندی اعداد بزرگتر از یک مقدار خاص و یا دومین گزینه برای قالب بندی اعداد کمتر از یک مقدار خاص میباشد 

قانون Greater Than را انتخاب کنید . با کلیک بر روی آن پنجره ای نمایش داده خواهد شد . فرض کنید در این مثال قصد داریم اعداد بزرگتر از ۵٫۰۰۰ دلار را با رنگ زرد قالب بندی نماییم . بنابراین عدد ۵٫۰۰۰ دلار را در کادر سمت چپ و از سمت راست گزینه Yellow fill …. را انتخاب نمایید و بر روی Ok کلیک نمایید . خواهید دید که با انجام این کار اعداد بزرگتر از ۵٫۰۰۰ دلار با رنگ زرد قالب بندی خواهند شد .

 

 

الگوهای قالب بندی شرطی

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

DataBars

نوارهای افقی هستند که به هر یک از سلول ها اضافه خواهند شد .

 

Color Scales

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

 

Icon Sets

در این قالب بندی یک آیکون خاص بر اساس مقادیر موجود در انها اضافه خواهد شد .

 

نحوه استفاده از الگوهای از پیش تعریف شده

• ابتدا سلول های مورد نظر خود را که قصد دارید قالب بندی بر روی آنها اعمال شوند را انتخاب نمایید .
• از تب Home و از گروه Style گزینه Coditional Formmating را انتخاب تا منوی کشویی نمایش داده شود .
• یکی از گزینه های Databars یا Color Scales یا Icon Sets را انتخاب و از زیر منوی آن گزینه مورد نظر الگوی مورد نظر خود را انتخاب نمایید . قالب بندی شرطی بر روی سلول انتخاب شده اعمال خواهد شد .

 

ترسیم نمودارهای فراوانی یا هیستوگرام و میله های خطا یا Error Bar در نمودارهادراکسل

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

به منظور ترسیم نمودارهای فراوانی در اکسل، ابتدا باید افزونه مناسب را به این نرم افزار اضافه کنید. برای اینکار به منوی File رفته و گزینه Options را انتخاب کنید. در پنجره باز شده مانند شکل زیر، گزینه Analysis ToolPak  را انتخاب کنید.

 

با کلیک روی کلید OK، این ابزار به منوی Data در اکسل اضافه می شود.

 

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

 

اعداد اصلی را در ستون A وارد کرده ایم. در ستون B بازه های دلخواه برای محاسبه فراوانی آنها وارد شده است. منظور از ستون B در جدول فوق این است که می خواهیم فراوانی اعداد را در بازه های 0 – 10، 10 – 20، 20 – 30 و درنهایت 30 – 40 محاسبه کنیم. به ستون اول یعنی داده های اصلی Input range و به ستون دوم یا بازه های فراوانی bin گفته می شود.

اکنون به منوی Data رفته و گزینه Data Analysis (همان گزینه که به عنوان افزونه به اکسل اضافه کردید) و سپس گزینه Histogram  را انتخاب کنید.

 

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

 

روی باکس Input range کلیک کرده و ستون اصلی داده های خود  و برای باکس Bin Range ستون بازهاین  را انتخاب کنید. برای مشاهده خروجی به صورت نمودار، گزینه Chart Output را تیک بزنید. و اگر می خواهید جدول خروجی را در مکانی خاص مشاهده کنید، گزینه Output Range را تیک زده و در باکس جلوی آن یک خانه از اکسل را انتخاب کنید تا اکسل به طور اتوماتیک جدول خروجی را در آن محدوده نمایش دهد. اگر گزینه New worksheet Ply تیک زده باشد، excel به طور اتوماتیک یک صفحه جدید باز کرده و در آن داده های خروجی را نمایش می دهد.

 

با فشردن کلید OK، داده های خروجی و همچنین نمودار را مشاهده خواهید کرد:

 

حالت پیش فرض منحنی فراوانی، منحنی میله ای مانند شکل فوق است. می توانید با کلیک راست روی منحنی و انتخاب گزینه Change Series Chart type، نمودار دلخواه را انتخاب کنید. برای مثال در شکل زیر نمودار از نوع Line  انتخاب شده است.

 

همچنین می توانید، در جدول خروجی که توسط اکسل ایجاد شده است، در ستون Bin به دلخواه اعداد بازه (یعنی Bin یا همان اعداد محور Xها) را تغییر دهید.

ترسیم میله های خطا یا Error Bar در نمودارها در اکسل

با اینکار، فراوانی در محور Yها و بازه ها به طور مشخص در نمودار Xها مشاهده می شود.

میله خطا یا error bar  نشان دهنده میزان انحراف استاندارد موجود در سیگنال (مقدارy ) هر نقطه از یک نمودار می باشد.
ابتدا داده های خود را (مقادیر x  و y) وارد  کنید. سپس نمودار را ترسیم کنید:

 

حال برای ترسیم میله خطا، روی نقاط نمودار کلیک کرده تا نقاط نمودار انتخاب یا فعال شوند. سپس، وارد منوی Layout  شده و قسمت Error Bars  را انتخاب کنید.

 

منوی مربوط به Error Bars  دارای چند قسمت به صورت زیر می باشد:

 

با انتخاب اولین گزینه یعنی None، میله ابزار روی نمودار شما مشاهده نمی شود. پیشنهاد می شود مستقیماً به سراغ آخرین گزینه یعنی More Error Bars options  رفته تا پنجره زیر باز شود:

 

در قسمت بالایی این پنجره یعنیDisplay  می توانید مشخص کنید که آیا میله خطا دارای دو جهت باشد یا تک جهته به سمت منفی یا مثبت ترسیم شود. همچنین در قسمت End Style می توانید، پیکان انتهای خط میله خطا را مشخص کنید.

اگر میزان خطای مطلق در هر نقطه از داده های شما (در مقدار y) مقداری ثابت است، می توانید از گزینه Fixed value  استفاده کنید. اگر میزان خطای نسبی در داده های شما دارای مقدار ثابتی می باشد، گزینه دوم یعنی Percentage  را انتخاب کرده و درصد مناسب را وارد کنید.
ممکن است شما برای هر نقطه از داده ها، انحراف استاندارد ثابت و یکسانی داشته باشید، در این حالت گزینه سوم یعنی Standard deviation  برای شما مناسب است. آن را انتخاب کرده و میزان انحراف استاندارد را وارد کنید. اگر خطای استانداردی دارید، می توانید گزینه چهارم را انتخاب کنید.

اما معمولاً شما برای هر کدام از داده های خود انحراف استاندارد مجزایی دارید. برای اینکار باید هر کدام از نقاط را چندین بار تکرار کرده باشید و برای هر نقطه انحراف استاندارد مجزایی را به دست آورده باشید. در این حالت از گزینه آخر یعنی Custom  استفاده کنید. پس از انتخاب این گزینه، روی کلید Specify value  کلیک کنید تا پنجره ای کوچک مانند شکل زیر برای دریافت داده های مربوط به انحراف استانداردهای نقاط مختلف باز شود:

 

 برای انحراف استاندارد در جهت مثبت و در جهت منفی، ستون مربوطه (داده ها) را انتخاب کنید:

 

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

 

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

 

 

 

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

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

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

طریقه ایجاد TABLE

برای تبدیل یک محدوده به جدول می توان به ۲ طریق زیر عمل کرد:

1. محدوده ی سلول هایی که قصد تبدیل آنها به جدول دارید را انتخاب کنید. سپس از طریق Home > Styles > Format As Table با انتخاب یکی از حالات، استایلی برای محدوده انتخاب کنید. در اینجا پنجره ای باز خواهد شد و این سوال را از شما می پرسد که آیا این محدوده را تبدیل به جدول کنم که با زدن دکمه ok محدوده تبدیل به جدول خواهد شد.
2. محدوده را انتخاب کنید و از Insert > Tables > table ابزار table را انتخاب نمایید یا اینکه کلید ترکیبی Ctrl+T را فشار دهید.

چند تکنیک درباره جدولی که ایجاد کردیم:

1. برای جدول ایجاد شده به طور خودکار اکسل نامی در نظر می گیرد. از این نام در فرمول نویسی ها می توانیم استفاده کنیم.
2. اگر در زمان ایجاد جدول چک باکس مربوط به سربرگ را تیک زده باشید، ردیف اول داده ها به عنوان سربرگ جدول در نظر گرفته می شود. نام هر یک از ستون ها نیز با عنوان سربرگ شناخته می شود.
3. زمانی که به سمت پایین حرکت کنید دیگر حروف الفبایی مربوط به نام ستون ها نشان داده نمی شود و به جای آن عنوان سربرگ نمایان خواهد شد.
4. در پایین جدول می توانید ردیف خودکاری اضافه کنید که این ردیف می تواند جمع، شمارش تعداد سلول ها و… را به صورت خودکار انجام دهد. این ردیف با کلیک بر روی جدول و فعال شدن Table Tools از آدرس Design>Table Style Options>Total Row انجام می شود. با زدن تیک چک باکس Total Row ردیف Total اضافه خواهد شد. نوع خروجی که این ردیف قرار است نمایش بدهد از لیست مربوط به هر سلول قابل انتخاب می باشد.
5. جدول قابلیت بزرگ شدن دارد. زمانی که در آخرین سلول جدول مقداری را وارد کنید و بعد از آن بخواهید به ردیف بعدی بروید با نگارش داده ای در سلول، آن ردیف به جدول اضافه خواهد شد. حتی ردیف جمع که در شماره ۴ بیان کردیم هم یک ردیف به سمت پایین جابجا می شود.
6. در صورتی می خواهید داده های تکراری را از جدول حذف کنید می توانید با کلیک بر روی سلولی از جدول و فعال شدن Table Tools از Design>Tools>Remove Duplicates حذف کردن داده های تکراری را فعال کنید. پنجره ای باز خواهد شد و این موضوع را باید در این پنجره مشخص کنید که داده ها از کدام ستون بررسی و حذف شوند.
7.  مرتب سازی هوشمندانه در EXCEL  در جدول قابل انجام می باشد. در جدولی که ایجاد کردیم سربرگ آن در حالت عادی به صورت فیلتر در خواهد آمد. با زدن هر کدام از این فیلترها و انتخاب نوع آن می توانیم مواردی که قصد فیلتر آنها را داریم انتخاب کنیم.
8. در صورتی که  خواهیم دکمه فیلتر در سربرگ نمایش داده نشود با فعال کردن table Tools و بعد از آن از Design>Table Style Options>Filter Button چک باکس بودن یا نبودن فیلتر در سربرگ را انتخاب می کنیم.
9. در جداول لازم نیست فرمولی که می نویسیم را برای بقیه سلول ها کپی کنیم بلکه با نوشتن فرمول برای اولین سلول و زدن کلید اینتر بقیه سلول های جدول به صورت خودکار تکمیل خواهند گشت.
10. ابزاری که در جدول خیلی مورد توجه هست، Structure Reference ها هستند. در زمان فرمول نویسی با انتخاب هر سلول، نام عادی آن در نظر گرفته نمی شود بلکه نامی از ستون آن انتخاب می شود که به این حالت خواهد بود:

=[@عنوان سربرگ ستون]

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

 

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

 

فرمول در کسل

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

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

خطاهای اکسل

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

- تقسیم عدد بر صفر!!!

اگر با اکسل سروکار دارید حتما با عبارات Div/0#! روبرو شده اید. همانطوری که همه مان می دانیم تقسیم کردن عدد بر صفر در ریاضیات مفهمومی ندارد. این موضوع در نرم افزار اکسل که بر اساس ریاضیات بنا شده است نیز صادق است.

اگر به طور عمد در فرمول نویسی عبارتی را بر صفر تقسیم کنید یا اینکه این عمل سهوا انجام گیرد خروجی خطای #Div! خواهد بود. منظور از سهوا این است که در فرمول نویسی به سلولی ارجاع دهید که خالی هست یا به اصطلاح اکسل Blank cell باشد.

 

۲- رفرنس های نامعلوم!!

فرمول نویسی اکسل بدون ارجاع و رفرنس دادن که نمی شود. ولی خطایی که ممکن است بر اثر این رفرنس دادن ها رخ دهد مربوط به حذف این رفرنس ها می باشد. فرض کنید فرمولی نوشته اید بعد از نگارش فرمول، سلولی که به آن ارجاع داده شده است حذف شود یا اینکه جابجا شود خطای !Ref# به صورت خروجی نشان داده خواهد شد.

 

۳- غلط املایی!!!

۳ حالتی که ممکن است خطای !Name# رخ دهد را با هم بررسی کنیم:

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

این ۳ حالت معمولا بیشتر اتفاق می افتد.

۴- ورودی های عجیب!!

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

 

۵- موجود نیست!!

یک سری توابع با جستجو مقداری را از یک محدوده به عنوان خروجی نمایش می دهند. حال اگر این مقدار در محدوده ی مورد نظر ما نباشد خطای #N/A نمایش داده خواهد شد.

۶- خیلی بزرگ!!

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

 

۷- فراموشکاری!!

خطای Null# یکی از خطاهایی می باشد که براثر فراموش کردن جداگر میان کاراکترها یا عملگرها رخ می دهد. فرض نمایید فرمولی به این نحو بنویسید که

=sum( I A;By)

در این فرمول قرار است سه کاراکتر I,A,By با هم جمع شوند ولی کاراکتر جداکننده مابین I,A فراموش شده است و خطای Null# به عنوان خروجی نمایش داده خواهد شد. یا اینکه فرمولی را به این نحو بنویسیم I A*By= که کاراکتر ضرب را در بین I,A فراموش کرده باشیم.

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

=A1:A10 A3:E3

خروجی این فرمول سلول A3 که در هر دو ناحیه مشترک هست می باشد حال اگر این دو ناحیه تداخلی نداشته باشند خطای Null# را خواهیم داشت.

۸- زمان منفی!!

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

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

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

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

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

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

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

ساختار فرمول های اکسل

فرمول نویسی در اکسل دارای عناصر زیر است:

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

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

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

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

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

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

  • برای انتخاب داده ها در اکسل می توان از shift استفاده کرد.
  • برای کپی یک فرمول می توان دو بار بر روی گوشه سمت راست سلول کلیک کرد.
  • با استفاده از کلید F4 می توان یک سلول را قفل کرد.
  • با استفاده از علامت & می توان داده های متنی را با هم ترکیب کرد.
  • با استفاده از تابع RAND() می توان اعداد تصادفی بین صفر و یک ایجاد کرد و با فشردن کلید F9 می توان این عدد را تغییر داد.
  • با استفاده از توابع LEFT ، RIGHT و LEN می توان داده های مورد نظر را پاک کرد.
  • با استفاده از داده CountIF می توان تعداد دفعات تکرار هر داده را مشخص کرد.
  • با استفاده از کلید Ctrl + می توان بین سلول ها جا به جا شد.
  • با زدن کلید Alt و = پس از سطر یا ستون می توان جمع داده ها را مشاهده کرد.
  • برای جا به جایی شیت ها می توان از کلید CTRL+PGUP و یا CTRL+PGDN استفاده کرد.
  • برای تنظیم فرمت اعداد می توان از CTRL+SHIFT به همراه اعداد یک تا پنج استفاده کرد.
  • هنگام کار با چند فایل به جای جداگانه باز کردن فایل ها می توان تمام فایل ها را انتخاب کرد و با زدن Enter تمام آن ها را به صورت همزمان باز کرد.
  • با دوبار کلیک می توان اسم صفحات را تغییر داد.
  • با استفاده از AutoCorrect می توان ورود اطلاعات را سرعت بخشید.
  • علاوه بر استفاده از Ctrl+A ، با استفاده از دکمه ای در گوشه صفحه می توان تمام داده ها را انتخاب کرد.
  • یکی دیگر از نکات مهم تبدیل تاریخ شمسی به میلادی در اکسل است. با توجه به اینکه اکسل فقط از تاریخ میلادی پشتیبانی می کند ، برای تبدیل تاریخ می توان از توابع مختلف که به این منظور تهیه شده است استفاده کرد.
  • برای تغییر فونت اعداد در اکسل پس از انتخاب تمام داده ها می توان فونت آن ها را همزمان تغییر داد

نکات جدید اکسل 2016

       اکسل 2016 طیف گسترده‌ای از ویژگی‌ها را در بخش‌های مختلف تجربه کرده است. شاید ظهور شش چارت قدرتمند بزرگ‌ترین تغییر به وجود آمده در اکسل 2016 باشند. چارت‌هایی که بعد از این سال‌ها به اکسل 2016 اضافه شده‌اند، کمک می‌کنند تا تحلیل‌های دقیق‌تری از آمارها به دست آید. اما در کنار چارت‌های جدید، ویژگی‌های دیگری نیز به اکسل 2016 افزوده شده‌اند که حفاظت از داده‌ها (DLP) که به‌عنوان یک ویژگی سازمانی شناخته شده و اکنون به اکسل 2016 اضافه شده است،

 

ویژگی‌های جدید

1-شش چارت جدید
مجازی‌سازی و ترسیم دقیق اطلاعات و آمار باعث می‌شود تحلیل مناسبی از داده‌ها به دست آید. در اکسل  شش چارت جدید به‌صورت تعاملی با نرم‌افزارهای دیگر در خدمت کاربران قرار دارند. این چارت‌ها به شیوه بهینه‌سازی و کارآمد توانایی ارائه تصویر درست از آمارها را دارند. برای دسترسی به این چارت‌ها ابتدا به زبانه Insert بروید. در بخش Charts، نماد مربوط به این زبانه‌ها را مشاهده خواهید کرد . روش دیگری که دسترسی آسان به این چارت‌ها را امکان‌پذیر می‌سازد، با استفاده از گزینه Recommended Charts و انتخاب گزینه All Charts است. 

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

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

2-محاوره‌های کارآمد
قبل از آن‌که توانایی تجزیه و تحلیل داده‌ها را داشته باشید، ابتدا باید بتوانید به داده‌‌های تجاری خود دسترسی داشته باشید. اکسل 2016 همراه با یک سری از وظایف از پیش آماده در اختیار کاربران قرار گرفته است تا سرعت دسترسی و تبدیل داده‌ها افزایش یابد. این ابزارها به شما اجازه می‌دهند تا اطلاعات‌ شما همواره در هر مکانی که به آن‌ها نیاز دارید، در اختیار شما قرار داشته باشند. این قابلیت‌ جدید در گذشته تنها به‌صورت یک افزونه جداگانه و پیش ساخته به‌نام Power Query در اختیار کاربران قرار داشت، به‌ طوری که کاربران باید آن‌ را جداگانه دریافت می‌کردند. اکسل 2016 همراه با ویژگی جدیدی به‌نام Get & Transform  عرضه شده است. این ویژگی در زبانه Data و در قالب گزینه New Query در اختیار کاربران قرار دارد. ویژگی جدید به گونه‌ای در نظر گرفته شده است که استخراج داده‌ها از منابع مختلفی همچون فایل‌ها (Wrokbook، سی‌اس‌وی، اکس‌ام‌ال و متن)، بانک اطلاعاتی (اکسس، اس‌کیوال، اوراکل، بانک اطلاعاتی آی‌بی‌ام، PostgreSQL ،MySQL ،Sybase و Teradata)، آژر، منابع دیگر (وب، اکتیو دایرکتوری، Handoop ،Exchange و...) و محاوره‌های ترکیبی به دست آید .

3-پیش‌بینی قدرتمندتر
در نسخه‌های قدیمی اکسل، تنها پیش‌بینی خطی یک متغیر بر اساس مقادیر قبلی آن متغیر در دسترس بود، اما در اکسل 2016 تابع Forecast توسعه یافته و توانایی پیش‌بینی بر مبنای Exponential Smoothing (از قبیلFORECASE.EST ) را دارد. این تابع تنها با یک کلیک ساده در دسترس کاربران قرار دارد. برای این منظور به زبانه Data بروید و در گروه Forecast گزینه Forecast sheet را انتخاب کنید. با انتخاب این گزینه مجازی‌سازی قدرتمندی از مجموعه داده‌ها ساخته می‌شود .

4-نقشه‌های سه‌بعدی دراکسل
ابزار مجازی‌ساز سه‌بعدی جغرافیایی Power Map اکنون تغییر نام داده است و در اکسل 2016 به‌صورت پیش ساخته در اختیار مصرف‌کنندگان قرار دارد. ابزار جدید اکنون 3D Maps نامیده شده است و همراه با دیگر ابزارهای مجازی‌ساز در زبانه Insert در کنار دیگر ابزارهای تجسم‌کننده قرار دارد .

5-بهبود عملکرد  PivotTableدر اکسل
اکسل همواره به دلیل انعطاف‌پذیری و ابزارهای قدرتمند تحلیلی آن شناخته شده و نزد کاربران محبوب است. PivotTable یکی از این موارد است. این تجربه تحلیل قدرتمند همراه با اکسل 2010 و 2013 با معرفی ویژگی PivotTable و Data Model در اختیار کاربران قرار گرفت. این ابزارها این توانایی را دارند تا در سریع‌ترین زمان ممکن مدل‌های پیچیده‌ای از داده‌ها را تولید کنند و در سریع‌ترین زمان ممکن به محاسبه میلیون‌ها رکورد بپردازند. اما اکسل 2016 باز هم بهره‌وری و کارایی PivotTable را بهبود بخشیده است. در اکسل 2016 به‌جای آن‌که تمرکز بیش‌تری بر مدیریت داده‌ها داشته باشید، می‌توانید بر تحلیل دقیق‌تر داده‌ها متمرکز شوید. از جمله ویژگی‌های جدید اضافه شده به اکسل به موارد زیر می‌توان اشاره کرد:

Automatic relationship detection
اکسل 2016 می‌داند شما چه زمانی نیازمند تحلیل دو یا چند جدول هستید، به ‌طوری که این جداول را به یکدیگر پیوند می‌دهد و شما را از این موضوع آگاه می‌سازد. در نتیجه، دیگر به کشف و ساخت ارتباط میان جداول مورد استفاده برای مدل‌های داده‌ای Workbook نیاز نخواهد بود. 

Automatic time grouping
این ویژگی به شما کمک می‌کند، فیلد‌های مبتنی بر زمان (سال و ماه) را برای شناسایی خودکار و گروه‌بندی آن‌ها استفاده کنید. زمانی ‌که آن‌ها را با یکدیگر گروه‌بندی می‌کنید، به‌سادگی با کشیدن و رها کردن آن‌ها به PivotTable همه آن‌‌ها در تحلیل‌ها در دسترس خواهند بود. 

Creating, editing and deleting custom measures
ساخت، ویرایش و شناسایی مقیاس‌ها اکنون به‌طور مستقیم از درون فهرست فیلدهای PivotTable در دسترس کاربران قرار دارد. این ویژگی باعث می‌شود تا به میزان قابل توجهی در زمان صرفه‌‌جویی شود، به دلیل این‌که دیگر نیازی به زمان برای تحلیل‌ها وجود ندارد.

PivotChart drill-down buttons
به شما اجازه بزرگ‌نمایی و کوچک‌نمایی در ساختارهای سلسله مراتبی درون داده‌ها را می‌دهد.

Smart rename
ویژگی Smart rename به شما این توانایی را می‌دهد تا نام جداول و ستون‌ها را به مدل داده‌ای Workbook تغییر دهید. با هر تغییر اکسل 2016، جداول مرتبط و محاسبات روی Workbook را به‌طور خودکار به‌روزرسانی می‌کند. این به‌روزرسانی شامل همه فرمول‌های DAX و تمام برگه‌ها می‌شود

6-انتشار تحلیل‌های خود با استفاده از Power BI
یک گزارش زمانی به‌عنوان گزارشی کامل شناخته می‌شود که با افراد واجد شرایط به اشتراک گذاشته شده باشد. زمانی‌ که تحلیل‌های داده‌ای خود را کامل کردید، می‌توانید با استفاده از ابزار Power BI این گزارش را تنها با یک کلیک ساده با همکاران و گروه‌های کاری به اشتراک بگذارید. وقتی گزارشی با Power BI به اشتراک گذاشته شد، می‌توانید از مدل‌های داده‌ای برای ساخت سریع گزارش‌ها و داشبورد‌های تعاملی استفاده کنید .

7-فرمت‌بندی سریع شکل‌ها
این ویژگی تعداد سبک‌های پیش‌فرض را با معرفی سبک Preset در اکسل افزایش می‌دهد

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

Tell me
ویژگی Tell me در نوار ریبون اکسل 2016 کمک می‌کند تا در سریع‌ترین زمان ممکن به فرامین مورد نیاز خود دسترسی پیدا کنید. ما عملکرد این کادر متنی را در مقاله‌های قبلی مورد بررسی قرار دادیم.

Insights
Smooth Lookup به دلیل این‌که ویژگی سراسری به شمار می‌رود، در نتیجه در همه نرم‌افزارهای مجموعه آفیس در اختیار کاربران قرار دارد. ارائه محتوای غنی و ساخت یافته به شما کمک می‌کند در کم‌ترین زمان ممکن محتوای مورد نیاز خود را در پانل Insights مشاهده کنید. 

Ink Equations
همان ‌گونه که پیش‌تر اشاره کردیم، Equations برای نوشتن دقیق‌تر معادلات ریاضی مورد استفاده قرار می‌گیرد. اگر از یک دستگاه لمسی یا قلم لمسی استفاده می‌کنید، به‌راحتی توانایی نوشتن معادلات ریاضی را دارید. اکسل این توانایی را دارد تا معادلات نوشته شده را به متن تبدیل کند. البته اگر از یک دستگاه لمسی استفاده نمی‌کنید، ماوس راه‌حل دیگری در این زمینه به شمار می‌رود.

9-به اشتراک‌گذاری ساده
با کلیک روی دکمه Share، به‌سادگی توانایی به اشتراک‌گذاری صفحات گسترده را در شیرپوینت، وان‌درایو یا وان‌درایو ویژه کسب‌وکارها خواهید داشت. 

10-مجهز شده به ویژگی DLP
حفاظت از داده‌ها ویژگی ارزشمند سازمانی به شمار می‌رود. اکنون اکسل 2016 به ویژگی DLP (سرنام Data Loss Protection) تجهیز شده است. این ویژگی به گونه‌ای عمل می‌کند که یک اسکن بی‌درنگ را بر مبنای محتوا و مجموعه سیاست‌های از پیش تعریف شده روی داده‌های حساس (از قبیل شماره کارت‌های اعتباری، شماره تأمین اجتماعی و شماره حساب‌های مربوط به ایالات متحده) اجرا می‌کند. در نتیجه، دیگر نگرانی از بابت از دست رفتن داده‌ها نخواهید داشت.                                                                                         امید واریم از آموزش اکسل نیز لذت برده باشید

مفهوم آدرس دهی نسبی و آدرس دهی مطلق دراکسل (Excel)

هنگام کار با اکسل یکی از مفاهیمی که بسیار کاربردی ست و همیشه با انها سروکار داریم    آدرس دهی نسبی یا همون Relative Cell Reference شکل معمولی آدرس سلول ها مانند A1 - B2 و... می باشد.فرض کنید ما چند ستون داده داریم و قرار هست همه آنها را در همان ستون جمع بزنیم . معمولا فرمول جمع را برای ستون اول نوشته و همان فرمول را با استفاده از قابلیت Auto Fill برای سایر ستون ها کپی می کنیم. اما حالا می خواهیم اعداد یک ستون را با یک عدد مشخص که در یک سلول قرار دارد جمع کنیم . خوب طبق روش بالا ابتدا فرمول جمع رو برای سطر اول نوشته و با استفاده از قابلیت Auto Fill اون رو برای سایر سطرهای زیرینش اعمال می کنیم. اما اینجا یک مشکل بوجود میاد اونم اینکه پس از اعمال قابلیتAuto Fill از سطر دوم تا اخر همه اعداد رو صفر نشون می دهد.مشکل کار اینجاست که با هر بار کپی آدرس نسبی خانه ای که عددمشخص شده جهت جمع کردن در آن قرار داشت متناسب با مقدار جابجایی آدرس آن سلول نیز تغییر می کند.                                                                                                                                   اما راه حل اینکار استفاده ازآدرس دهی مطلق در اکسل   سلولی را که مطلق می نماید، آدرس آن خانه در هنگام کپی ثابت می ماند و تغییرنمی کند.اما برای مطلق کردن آدرس یک سلول از علامت $ استفاده می شود. که اینکار به سه روش قابل اجرا بر روی آدرس آن سلول می باشد.                                              همچنین می توانید آدرس سلولی را که می خواهید مطلق شود را انتخاب کنید های لایت کنید و کلید F4 را بزنید خواهید دید که علامت مطلق $ به آن اضافه می شود.

اطلاعات علمی درمورد ترفندهای اکسل

برای ساخت یک چارت، داده ها را همراه با هدرهای ستون(1) در اکسل وارد نمائید و Insert > Chart > Chart Type را انتنخاب نمائید(2). اکسل 2013 شامل بخش چارت های پیشنهادی (Recommended Charts) (3)همراه با طرح های مبتنی بر نوع داده هایست که با آن ها کار می کنید.

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

در صورتیکه بتوانید در کار کردن با این 11 ترفند اکسل مهارت پیدا کنید، به شما قول می دهیم جزء دسته دوم باشید.

 

 Vlookup  در اکسل

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

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

فرمول Vlookup را در منوی فرمول ها فرابخوانید و سلولی که شامل شماره ارجاع شماست (1) وارد کنید، سپس محدوده سلول های موجود در برگه یا کتاب کاری که لازم است داده ها را از آنها استخراج کنید، وارد نمایید (2) شماره ستون مربوط به نقطه داده ای که بدنبالش هستید را وارد کنید (3) و در صورتی که نزدیکترین آیتم به مرجع را می خواهید، True و در صورتی که آیتم دقیق را میخواهید، False را وارد نمائید.

ساخت چارت ها  در  اکسل

برای ساخت یک چارت، داده ها را همراه با هدرهای ستون(1) در اکسل وارد نمائید و Insert > Chart > Chart Type را انتنخاب نمائید(2).

اکسل  شامل بخش چارت های پیشنهادی (Recommended Charts) (3)همراه با طرح های مبتنی بر نوع داده هایست که با آن ها کار می کنید. پس از ساختن نسخه عمومی یک چارت، منوهای Chart Tools را برای اختصاصی کردن آن باز کنید. گزینه های جالبی را می بینید که میتوانید برای کارتان از آن ها استفاده کنید.

IF and IF ERROR در اکسل 

IF and IFERROR دو نوع از مفیدترین فرمول های IF در اکسل هستند. IF به شما امکان می دهد از فرمول های شرطی استفاده کنید که از یک سو صحیح (true) بودن یک چیز خاص را محاسبه می کند و از سوی دیگر غلط (false) بودن آن چیز خاص را. مثلاً میتوانید دانش آموزانی که امتیاز 80 یا بالاتر را در یک امتحان گرفته اند شناسایی کنید. به این ترتیب که اگر امتیاز درون ستون C بالای 80 باشد، سلول می باید ‘Pass’ را گزارش دهد و اگر امتیاز 79 یا زیر 79 باشد، باید «Fail» را گزارش نماید.

IFERROR نوع دیگری از فرمول استاندارد IF است و اگر فرمول مورد استفاده شما سعی در بازگرداندن خطا داشته باشد، به شما امکان می دهد تا یک مقدار خاص (یا مقدار خالی) را بازگردانید. مثلاً اگر Vlookup را در برگه یا جدول دیگری اجرا می کنید، فرمول IFERROR میتواند در هر نمونه که Vlookup قادر به پیدا کردن مرجع نیست، فیلد را خالی تحویل دهد.

 PivotTable

PivotTable یک جدول خلاصه یا summary است که به شما امکان شمارش، معدل گرفتن، جمع کردن و اجرای محاسبات دیگر را براساس نقاط ارجاعی که وارد کرده اید، می دهد. اکسل 2013، شامل Recommended Pivot Table است تا ساخت جدولی که داده های مورد نیاز شما را نمایش می دهد، آسانتر شود.

مثلاً، برای شمارش تعداد قبول شده ها و رد شده های یک آزمون در یک چارت، ستون Pass/Fail خود را در تب Row Labels و سپس دوباره در بخش Values از (PivotTable (2 خود قرار دهید. می توانید متغییرهای دیگر را از کادر کرکره ای Values انتخاب نمائید(3) . همچنین می توانید جدول های فرعی بسازید که داده ها را به صورت طبقه بندی شده ، مثلاً تعداد Pass/Fail براساس جنسیت خلاصه می کنند.

 

 PivotChart

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

اکسل 2013، شامل بخش Recommended PivotCharts است که در زیر آیکن Recommended Charts در قسمت Charts تب Insert لیست شده اند. با چرخاندن اشاره گر ماوس روی آن گزینه میتوانید پیش نمایش یک چارت را ببینید. برای ساخت دستی PivotChart ، آیکن PivotChart را در تب Insert انتخاب نمائید.

Flash Fill

Flash Fill بهترین ویژگی جدید اکسل2013 است که یکی از آزاردهنده ترین مشکلات این برنامه را حل می کند: چگونه بخشهایی از اطلاعات مورد نیاز خود را از یک سلول بهم پیوسته استخراح نماییم. مثلاً در گذشته، وقتی در ستونی با نام هایی در فرمت «Last ,First» کار می کردید، یا مجبور بودید همه چیز را به صورت دستی تایپ کنید یا یک workaround(پیچیده) بسازید.

فرض کنید که با فیلد نام اشخاص در اکسل 2013 کار می کنید. شما نام کوچک اولین شخص را در یک فیلد سریعا در کنار فیلدی که با آن کار می کنید (1)، تایپ می کنید و Home > Fill > Flash Fill را انتخاب می کنید. در پاسخ، اکسل به طور خودکار نام اول تمام افرادی که در فیلد موجود ثبت شده اند را استخراج می کند و فیلد جدید را با این نام ها پر می کند.

 Quick Analysis

ابزار جدید Quick Analysis اکسل 2013، مقدار زمان لازم جهت ساخت چارت های با مجموعه داده های ساده را کاهش می دهد. وقتی شما داده های خود را انتخاب می کنید، آیکنی در گوشه پائین راست صفحه ظاهر می شود (1)، آن آیکن را کلیک کنید تا منوی Quick Analysis ظاهر شود (2).

این منو ابزارهایی را برای Formatting، Charts، Totals، Tables و Sparklines ارائه می دهد. با چرخاندن اشاره گر ماوس روی هر گزینه، می توانید پیش نمایش از آن بسازید.

 Power View

Power view یک ابزار تعاملی کشف و تجسم سازی داده هاست که میتواند حجم بزرگی از داده ها را از فایل های داده ای خارجی، استخراج و تجزیه و تحلیل کند. در اکسل 2013، به Insert>Reports بروید(1) تا از Power View استفاده کنید (2).

گزارشات ساخته شده با Power View، آماده نمایش هستند همراه با مدهایی برای خواندن و نمایش تمام صفحه. شما می توانید حتی یک نسخه تعاملی را در PowerPoint صادر کنید. راهنمای آموزشی (tinyurl.com/nqyLvnl(BI در سایت مایکروسافت به شما کمک می کند تا در این زمینه یک کارشناس شوید.

 Conditional Formatting

عملکرد گسترده فرمتینگ شرطی اکسل، شناسایی نقاط داده ای مورد نظر را آسان می سازد. این ویژگی در تب Home در نوار وظیفه (1) قرار دارد. محدوده سلول هایی که میخواهید فرمت شوند را انتخاب نمائید و Conditional Formatting را کلیک نمائید (2). زیر منوی( Highlight Cells Rules (3 همانند یک میزبان برای ویژگی هایی که اغلب از آن ها استفاده می کنید، عمل می کند.

تبدیل ستون ها به سطرها (و بالعکس)

گاه ممکن است بخواهید داده ها را از فرمت ستون به فرمت سطر(یا بالعکس) تبدیل کنید. برای اینکار، سطر یا ستونی که میخواهید تبدیل در آن ها انجام دهید را کپی کنید، سلول مقصد را راست کلیک نمائید و Paste Special را انتخاب نمائید. سپس کادری با برچسب Transpose که در پائین پنجره ظاهر می شود را تیک بزنید و ok را کلیک نمائید. اکسل بقیه کارها را انجام می دهد.

 

نکاتی درمورد سلول اکسل در ماکرو نویسی

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

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

گستره استفاده از سلول اکسل در ماکروها

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

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

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

  سلول های اکسل

  برای اشاره به سلول اکسل در ماکرو نویسی ویژوال بیسیک میتوان از شی Range Object) Range) و یا از پروپرتی Cells استفده نمود.

  استفاده از شی Range در اشاره به سلول های اکسل

  از شی Range میتوان برای اشاره به سلول اکسل در ماکرو نویسی استفاده نمود،

  
  دقت کنید که عبارت قرار گرفته در درون پرانتز شی Range متغیری از نوع رشته میباشد.

  به صورت خلاصه تر هم میتوان از شی Range برای اشاره به سلول ها استفاده نمود، برای اینکار نیازی به نوشتن کلمه ی Range و گذاشتن علامت “Quotation Mark” قبل و بعد از نام محدوده نیست،

  استفاده از پروپرتی Cells در اشاره به سلول های اکسل

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

  استفاده از پروپرتی های Row و Column در اشاره به سلول های اکسل

  از پروپرتی های Row و Column هم برای اشاره کردن به سلول های اکسل میتوان استفاده نمود، به این تفاوت که این پروپرتی ها سطر ها و ستون ها را میتوانند مورد اشاره قرار دهند و نه سلول ها را، در ادامه مثال هایی از کاربرد Row و Column برای اشاره به سلول های اکسل توسط کدهای ویژوال بیسیک آورده شده است.

  اشاره به یک محدوده ی نامگذاری شده در محیط اکسل توسط کدهای ویژوال بیسیک

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

  در کدهای ویژوال بیسیک شما میتوانید از نام های اختصاص داده شده برای سلول ها در اکسل برای اشاره به آن ها استفاده نمایید. برای این کار محدوده ای از اکسل به نام ExcelRange که در فایل اکسل MyWorkBook و در Sheet1 قرار دارد را در نظر بگیرید، کدهای زیر نحوه ی اشاره به این محدوده را نمایش میدهند و همگی یک کار را انجام میدهند.

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

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

1-از تب Data بر روی گزینه Filter کلیک نمایید .

۲- بر روی علامت پیکان ستون مورد نظر کلیک نمایید . قصد داریم با فیلتر کردن ستون جزئیات تجهیزات تنها محصولات یک برند خاص را فیلتر کنیم .

۳- عبارت برند مورد نظر خود را در کادر جستجو وارد نمایید . در این مثال قصد داریم محصولات برند Saris را فیلتر کنیم بنابراین عبارت Saris را در کادر جستجو تایپ و بر روی Ok کلیک نمایید .

 

فیلتر کردن پیشرفتهText دراکسل

با استفاده از فیلتر کردن پیشرفته Text  می توانید اطلاعات مشخص تری جستجو و سپس نمایش دهید . در این مثال قصد داریم با استفاده از فیلتر کردن پیشرفته Text تمامی تجهیزاتی را که مرتبط را Camera هست را مخفی نماییم .

۱- از تب Data بر روی گزینه Filter کلیک نمایید .

۲- بر روی علامت پیکان ستون مورد نظر کلیک نمایید . در این مثال قصد داریم با فیلتر کردن ستون جزئیات تجهیزات تنها نوعی از تجهیزات را نمایش دهیم .

۳- از زیر منوی TextFilter گزینه Does Not Contain… را انتخاب نمایید .

 

۵- در پنجره Custom AutoFilter عبارتcam را همانند تصویر زیر در قسمت مربوطه وارد و بر روی Ok کلیک نمایید . با انجام این کار اطلاعاتی که حاوی عبارت cam نیستند نمایش داده خواهد شد .

 

فیلتر کردن پیشرفته Number

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

۱- از تب Data بر روی دکمه فرمان Filter کلیک نمایید .

۲- بر روی علامت پیکان ستون مورد نظر کلیک نمایید . در این مثال قصد داریم ستون شناسه را فیلتر کنیم بنابراین ستون ID را انتخاب نمایید .

۳- از منوی Number Filters بر روی گزینه Between کلیک نمایید .

۴- در این مثال قصد داریم محصولاتی که شناسه آنها بزرگتر از ۳۰۰۰ و کوچکتر از ۴۰۰۰ هست را فیلترنماییم پاعداد را وارد و بر روی Ok کلیک نمایید .

 

فیلتر پیشرفته در Excel

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

1- تنظیم مجموعه داده ها

1- اولین ردیف (A1:D1)  شامل سر ستونهاست

2- سپس داده را ذیل سر ستونها می نویسیم

3- هیچ ردیف خالی در بین داده ها وجود ندارد

4- ردیف خالی در انتهای مجموعه داده ها و ستون خالی نیز در انتهای ستون داده هاست                          2- تنظیم محدودهء شرط ها (اختیاری )

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

1- در این مثال سلولهای F1:F2  محدوده  شرط است که می تواند بیش از این تعداد نیز باشد

2- سر ستون F1  دقیقا با سر ستون D1  در مجموعه  داده ها یکی است

3- سلول F2  شامل شرط است . عملگر < (بزرگتر از ) مورد استفاده قرار گرفته است با عدد 500 (بدون بکارگیری علامت $)                                                                                                               

پس از اعمال فیلتر پیشرفته تنها رکوردهایی با total>500 قابل رویت خواهند بود  .

دیگر عملگرها شامل : <  کمتر از > بیشتر از <=   بزرگتر یا مساوی     >= کوچکتر یا مساوی <> نابرابر با

3- تنظیم محدوده استخراج شده بعد از اعمال فیلتر (اختیاری)

اگر هدف شما کپی کردن داده ها فیلتر شده در محل دیگریست ، می توانید ستونهائی که قصد استخراجشان را دارید مشخص کنید

( اگر قصد استخراج همه ستونها را دارید این مرحله را به خود excel  واگذارید.)

1- سلول بالا و چپ را برای داده های استخراج شده انتخاب کنید

2- سرستونهای (فیلدهائی) که قصد استخراجشان را دارید تایپ کنید آنها دقیقا همان فیلدهائی باید باشند که در مجموعه داده ها وجود دارند .

 

4- اجرای فیلتراسیون پیشرفته اکسل

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

2- در ریبون data  مجموعهء sort &filter  ، tab ِ "advance filter" را به منظور باز شدن پنجره advance filter کلیک کنید

 

3- انتخاب با شماست که خروجی فیلتراسیون در همین شیت یا در جای دیگری کپی شود.

4- پیشفرض اکسل بطور اتوماتیک نشان دادن لیست داده ها در همان sheet  یست که مجموعه داده ها وجود دارند وگرنه copy to anather lacation  را تیک کنید

5- محدوده شرط(یا شروط) را انتخاب کنید

6- اگر قصد دارید داده را در جای دیگری کپی کنید برای copy to  یک سلول در مکان دیگری انتخاب کنید  نوجه : اگر خروجی فیلتراسیون شما در مکان دیگری باشد همه سلول های زیر محدوده استخراج شده هنگام اعمال فیلتر پاک خواهند شد .

7- OK  را کلیک کنید

 

 

فیلتر کردن داده ها بصورت منحصر بفرد ( unique ) در اکسل

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

توجه : لیست باید شامل سرستون باشد وگرنه ممکن است اولین آیتم بصورت دوبل ظاهر شود

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

2- در ریبون data  مجموعهء sort &filter  ، tab ِ "advance filter" را به منظور باز شدن پنجره advance filter کلیک کنید

3- Copy to anather location  را انتخاب کنید

4- ستون (ستونها)ی را که می خواهید از آنها استخراج uniqe صورت گیرد انتخاب کنید

5- Criteria range  را خالی بگذارید

6- سلولی را برای شروع کپی در محلی دیگر انتخاب کنید

7- قسمت unique record only  را تیک کنید

8- OK  را کلیک کنید

 

استخراج داده ها در شیت‌ دیگر

اگر داده ها در برگه ای باشند شما می توانید در برگه ای دیگر داده های مورد نظرتان را زاآن اسنخراج کنید در این مثال داده ها در sheet1 و نتیجه در sheet2 کپی می شود.

1-    به sheet2  بروید

2-    سلولی را در بخش استفاده نشده برگه انتخاب کنید (در اینجا C4)

3-    Advance filter  را کلیک کنید

4-    Copy to anather location  را انتخاب کنید

5-    در جعبهء the list range کلیک کنید

6-     در  sheet1 محدوده داده ها را انتخاب کنید

7-    در بخش  criteria range  کلیک کنید (اختیاری)

8-    محدوده شرط را انتخاب کنید

9-    در باکس copy to  کلیک کنید

10-      درsheet2  یک سلول که می خواهید کپی داده ها از آنجا اغاز شود یا سرستونهائی که تایپ کرده اید را انتخاب کنید

11-      در صورت تمایل unique record only  نیز تیک کنید(اختیاری)

12-      OK  را کلیک کنید

 

چیدمان محدوده شرطها دراکسل

مقایسه گزینه AND و OR

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

 

در این مثال مشتری magamart  ، محصول cookies   و total  500< است اگر محدوده را بشکل زیر تعریف کنیم عملگر OR  در فیلتراسیون اعمال می شود

 

و اگر قصد استفادهء همزمان از عملگرهای AND  و OR  را دارید محدوده شروط را بشکل زیر تنظیم کنید

 

این شکل بیان گر این است که مشتری باید megamart  و محصول باید cookies  باشد یا 

محصول باید cookies  و total  باید 500< باشد

استفاده از wildcards

علامت (*)

جایگزین چند کاراکتر می شود در این مثال هر مشتری که نامش شامل "mart" باشد از فیلتر اکسل عبور می کند

 

علامت ؟

که بجای یک کاراکتر می نشیند در این مثال همه محصولاتی که شامل c?oke  هستند از فیلتر عبور می کنند coke  یا  cake

 

علامت ~

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

 

 

ملاحظه می کنید که در اولی تمام آنچه که با good  شروع  و به  eats  ختم میشود از فیلتر عبور کرده اما در دومی eats  از فیلتر عبور کرده و نه  treats

مثالی برای فیلتر کردن فیلد تاریخ .

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

 

استخراج آیتم ها با متنی خاص

وقتی شما یک متن را در شرط قرار می دهید اکسل همه آیتم هایئ که شامل این متن است را از فیلتر عبور می دهد مثلا اگر شرط "Ice"  باشد  "ice milk" "Ice" "Ice cream"استخراج می شوند . برای پرهیز از چنین اتفاقی در صورت نیاز مانند شکل عمل کنید

 

برازش منحنی (Trend Line) یا رگرسیون در اکسل

اکثر کاربران نرم‌افزار اکسل برای یک بار هم که شده با برازش منحنی برای داده‌های x و y یا Trend Line برخورد داشته‌اند. در واقع Trend Line به شما کمک می‌کند تا علاوه بر تشخیص روند تغییر داده‌ها، بتوانید تا حدودی وضعیت داده‌ها را پیش‌بینی (Forecasting) کنید. در ادامه مطلب با آموزش اکسل  امروز همراه شوید تا علاوه بر جزئیات Trend Line، با توابع کاربردی اکسل برای برازش منحنی نیز آشنا گردید.

 

از Trend Line فقط می‌توان در منحنی‌های Area ،Bar ،Column ،Line و XY استفاده کرد.به خاطر داشته باشید که نمی‌توانید در نمودارهای 3D ،Radar ،Pie ،Doughnut و Bubble از Trend Line استفاده کنید.

برای اضافه‌ کردن Trend Line، پس از راست کلیک کردن روی منحنی داده‌ها، گزینه‌ی Add Trendline را انتخاب کنید تا پنجره Format Trendline باز شود.

 

در قسمت راست پنجره Format Trendline یعنی قسمت Trendline Options، بخش‌های زیر وجود دارد:

بخش اول: بخش Trend/Regression Type انواع Trendline‌ها را نشان می‌دهد که به شرح زیر است:

   - Exponential / نمایی؛ با فرمول Y=C.e^bx که b و c اعداد ثابت هستند.

          * نکته: هنگامی که داده‌های y شامل اعداد منفی یا صفر باشند، قابل استفاده نیست!

   - Linear / خطی؛ با فرمول Y=m.x+b که m شیب خط و b عدد ثابت (عرض از مبدا) است.

   - Logarithmic / لگاریتمی؛ با فرمول Y=c.Lnx+b که c و b اعداد ثابت هستند. 

          * نکته: هنگامی که داده‌های x شامل اعداد منفی یا صفر باشند، خطا ظاهر می‌شود!

   - Polynomial / چند جمله‌ای؛ با فرمول Y=b+c₁x+c₂x²+c₃x³+...+c_nxⁿ که در آن c عدد ثابت است.

   - Power / توانی؛ با فرمول Y=C.x^b که b و c اعداد ثابت هستند.

* نکته: هنگامی که داده‌های y شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

   - Moving Average / میانگین متحرک؛ با فرمول F_t=(A_t+A_t-1+...+A_t-n+1)/n

بخش دوم، بخش TrendLine Name می‌باشد.

بخش سوم، بخش Forecast یا پیش‌بینی می‌باشد که بر اساس نوع معادلات انتخابی در بخش اول، yهای قبل و یا بعد متناظر با xهای داده شده را پیش بینی می‌کند.

Set Intercept هم برای تعیین عرض از مبداء دلخواه می‌باشد.

با تیک زدن دو گزینه آخر یعنی Display Equation on chart و Display R-squared value on chart، به ترتیب معادله و ضریب رگرسیون (R²) متناظر با نوع Trendline انتخاب شده، روی نمودار نمایش داده می‌شود. در رگرسیون خطی ضریب رگرسیون مجذور ضریب همبستگی (R) است.

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

۱- معادله‌ای مناسب است که ضریب رگرسیون آن نزدیک به یک باشد مثلا 0.99.

۲- اکسل ، ضرایب معادله‌ را تا ۲ رقم اعشار نمایش می‌دهد. برای اینکه بتوانید با استفاده از معادله، y متناظر با یک x را محاسبه کنید برای دقت بیشتر باید از معادله‌ای استفاده کنید که تعداد ارقام اعشاری بیشتری داشته باشد. راست کلیک کنید و گزینه Format trendline label را انتخاب کنید.

در پنجره باز شده زیر در قسمت Category گزینه Number را انتخاب و در قسمت Decimal places تعداد ارقام بعد از ممیز را افزایش دهید. دکمه Close را بزنید و از معادله جدید استفاده کنید.

 

---

علاوه بر استفاده گرافیکی از ابزار Trend Line، می‌توان از توابع اکسل نیز اطلاعات مفیدی بدون رسم نمودار به دست آورد.
۱- تابع Slope: محاسبه شیب رگرسیون خطی.

=SLOPE(Known Y values, Known X values)

برای مثال زیر شیب خط تقریبا 2.15 می‌باشد.

=SLOPE(B2:B6,A2:A6) = 2.15

 

۲- تابع Intercept: محاسبه عرض از مبدا رگرسیون خطی.که برای مثال بالا تقریبا 0.47- می باشد.

=INTERCEPT(Known Y values, Known X values)

=INTERCEPT(B2:B6,A2:A6) = -0.47

یعنی در واقع معادله رگرسیون خطی این مثال برابر است با:        y = 2.15*x -0.47

 

۳- تابع Forecast: برای پیش‌بینی y متناظر با یک x جدید بر مبنای رگرسیون خطی.

=FORECAST(New X Value, Known Y values, Known X values)
=FORECAST(15,B2:B6,A2:A6) = 31.778

۴- تابع GROWTH: برای پیش بینی y متناظر با یک x جدید بر مبنای رگرسیون نمائی.

=GROWTH(Known Y Values, Known X Values, New X Values, Const)
=GROWTH(B2:B6,A2:A6,15,TRUE) = 48.68

عبارت Const در تابع GROWTH، دارای دو حالت True (محاسبه b) و False (مقدار 1 برای b) می‌باشد.

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

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

 

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

روش اول) انتقال و یا ذخیره فایل اکسل حاوی کد ماکرو در پوشه XLStart

روش دوم) اضافه کردن فایل حاوی کد ماکرو به عنوان Add-in به اکسل

 

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

البته باید به این نکته دقت کنید که فایل‌های اکسل حاوی کدهای ماکرو را نمی‌توان مشابه فایل‌های معمولی اکسل ذخیره کرد بلکه باید آن‌ها را در قالب Macro-Enabled ذخیره نمود.

اما پوشه XLStart در کجا قرار دارد؟

در نسخه‌های مختلف ویندوز مسیر پوشه XLStart متفاوت است:

ویندوز XP

C:\Documents andSettings\<user name>\Application Data\Microsoft\Excel\XLStart

ویندوز Vista

C:\Users\<user name>\AppData\Local\Microsoft\Excel\XLStart

ویندوز 7، 8 و 10   

C:\Users\<user name>\AppData\Roaming\Microsoft\Excel\XLSTART

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

روش الف: در پائین پنجره Microsoft Visual Basic در بخش Immediate، عبارت زیر را کپی کنید. اگر این بخش قابل مشاهده نیست کلیدهای Ctrl + G را بفشارید تا بخش پنجره Immediate فورا ظاهر شود.

? application.StartupPath

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

 

روش ب: روش دوم برای یافتن آدرس پوشه XLStart این است که عبارت زیر در نوار آدرس ویندوز اکسپلورر کپی کنید و کلید اینتر را فشار دهید تا پوشه مورد نظر باز شود:

%APPDATA%\Microsoft\Excel\XLSTART

با باز شدن این پوشه می‌توانید آدرس آنرا در نوار آدرس مشاهده کنید.

 

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

البته اگر قصد دارید یک ماکرو را با استفاده از قابلیت ضبط ماکرو (مراجعه به تب View، قسمت Macros و انتخاب گزینه Record Macro) ایجاد کنید در این حالت می‌توانید در همان ابتدا و قبل از ساخت ماکرو، مسیر ذخیره آن‌را پوشه XLStart قرار داد تا پس از ذخیره کردن به صورت خودکار در این پوشه قرار گیرد.

 

 

برای اینکار پس از انتخاب گزینه Record Macro، پنجره Record Macro  ظاهر می‌شود. پس از وارد کردن نام و کلید میانبر، برای اینکه فایل اکسل در پوشه XLStart ذخیره شود در قسمت Store macro in گزینه Personal Macro Workbook را انتخاب و در پایان روی دکمه OK کلیک کنید.

 

با انتخاب گزینه Personal Macro Workbook، اکسل  فایل مخفی (hidden) که حاوی این ماکرو است ایجاد می‌کند. بدین ترتیب هر زمان که شما اکسل را باز کنید این ماکرو نیز قابلیت اجرا دارد.
نکته: البته با توجه به اینکه این ماکرو در یک فایل مخفی است برای اینکه بتوانید هر گونه تغییری در آن ایجاد کنید یا آنرا حذف کنید لازم است در ابتدا آنرا از حالت hiden خارج کنید. اینکار از مسیر View>Unhide امکان‌پذیر است. بعد از انجام تغییرات باید آنرا از همان مسیر Hide نمایید.

 

پس از ذخیره فایل ماکروی ایجاد شده، این فایل با نام Personal.xlsb در پوشه XLStart قرار می‌گیرد. تمام فایل‌های اکسلی که در پوشه XLStart باشند بطور خودکار با باز شدن هر فایل اکسل باز می‌شوند.

اگر از این روش برای  ماکرو نویسی دراکسل استفاده می‌کنید احتمالا با سایر مراحل آن آشنا هستید.

---

روش دوم) اضافه کردن فایل حاوی کد ماکرو به عنوان Add-in به اکسل

برای اضافه کردن یک فایل حاوی کد ماکرو به عنوان Add-in به اکسل باید از فرمت xlam اکسل استفاده کرد. این فرمت بسیار شبیه به فرمت xlsm هست و برای تهیه Add-Inها در اکسل استفاده می‌شود. برای ذخیره فایل‌ حاوی ماکرو با فرمت xlam باید از لیست انواع پسوندهای ذخیره سازی (Save as type) گزینه Excel Add-In را انتخاب نمود.

پس از ذخیره فایل با پسوند xlam باید آن‌را در نرم افزار اکسل به صورت یک Add-In اضافه کرد. برای اینکار مثل تصویر زیر در پنجره تنظیمات اکسل وارد بخش Add-Ins شوید و در قسمت پایین پس از انتخاب گزینه Excel Add-Ins در بخش Manage روی دکمه Go کلیک کنید.

با انتخاب دکمه GO پنجره Add-Ins باز می‌شود. حال روی دکمه Browse کلیک کنید و فایل xlam مد نظرتان را انتخاب نموده و روی دکمه Ok کلیک کنید. با اینکار این فایل به عنوان یک Add-In به اکسل اضافه می‌شود و می‌توان از ماکروی آن در تمام فایل‌های اکسل استفاده کرد.

 

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

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

 

نوار ابزار Formula Auditing را باز كنید  

 

 وقتی كه خطاها رخ می دهند، یكی از قدرتمند ترین ابزارها برای اصلاح آنها، نوار ابزار Formula Auditing است. برای باز كردن آن، از منوی Tools ، گزینه Formula Auditing و بعد گزینه Show Formula Auditing Toolbar را انتخاب كنید.

خطاها را مرور كنید 

 برای بررسی هر سلولی كه با مثلث خطا مشخص شده است، روی دكمه Error Checking در نوار ابزار Formula Auditing كلیك كنید،Excel اولین سلول خطا را متمایز می كند و گزینه هایی برای اصلاح آنها در اختیار شما قرار می دهد.

خطا را اصل

 در اینجا، Excel متوجه شده است كه یك سلول از فرمولی استفاده می كند كه با فرمول‌های سلول‌های پیرامون، متفاوت است. این ممكن است یك خطا باشد یا نباشد. اگر بخواهید می توانید به Excel بگویید كه این خطا را نادیده بگیرد. برای این كار، روی دكمه Ignore Error كلیك كنید. ولی، این یك خطاست. بنابراین روی Copy Formula from Above كلیك كنید. با كلیك كردن روی Edit in Formula Bar خودتان می توانید فرمول را تغییر دهید.

 خطاهای بعدی را نیز اصلاح كنید 

 Excel، سلول خطای بعدی را متمایز می كند. گزینه مورد نظر را از كادر محاوره ای Error Checking انتخاب كنید. آنقدر ادامه دهید تا تمام خطاها را مشاهده كنید. وقتی كه Excel خطاها را بررسی می كند، یك پیغام نشان می دهد. روی Ok كلیك كنید.

 

آدرس های مرجوعی را دنبال كنید 

تمام اشتباهات، منجر به خطا نمی شوند. اگر چیزی درست به نظر نرسد، (مانند میانگین فروش) می توانید با نمایش دادن آدرس های مرجوعی فرمول، مقادیر مورد استفاده در آن را ردیابی (دنبال) كنید. روی سلولی كه می خواهید ارزیابی كنید كلیك كنید و بعد روی دكمه Trace Precedents كلیك كنید. اگر لازم باشد، دوباره روی این دكمه كلیك كنید تا مقدار این سلول ها را دنبال كنید

 فلش‌های ردیابی را نمایش می دهد كه به منبع های فرمول اشاره می كنند و به شما كمك می كنند تا خطا را دنبال كنید. در اینجا، من اشتباهاً جمع كل در ستون G را در محدوده هایی كه در تابع AVERAGE بكار بردم وارد كردم! بعد از اینكه خطا را پیدا كردید، اصلاحات لازم را انجام دهید و با كلیك كردن روی Remove All Arrows ، فلش های ردیابی را حذف كنید.

*توجه*

 استفاده از Trace Error

اگر یك مثلث سبزرنگ، وجود خطا در فرمولی كه تایپ كرده اید را نشان دهد، با بردن اشاره گر ماوس به سمت چپ سلول و متمایز كردن دكمه Trace Error ، كلیك كردن روی فلشی كه ظاهر می شود و انتخاب یكی از گزینه ها می توانید فوراً آن را بررسی كنید.

ردیابی وابسته ها

با كلیك كردن روی یك سلول و كلیك كردن روی دكمه Trace Dependents درنوار ابزار Formula Auditing می توانید بررسی كنید كه آیا مقدار آن سلول در یكی از فرمول ها استفاده شده است یا خیر و چگونه.

ارزیابی فرمول

برای ارزیابی یك فرمول، روی دكمه Evaluate Formula در نوار ابزار Formula Auditing كلیك كنید تا یك كادر محاوره ای باز شود كه شما را در تك تك قسمت های فرمول جلو می برد. برای محاسبه قسمت خط كشی شده فرمول، روی Evaluate كلیك كنید. هر اندازه كه لازم است روی Evaluate كلیك كنید.

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

شاید برای شما نیز این مورد پیش آمده باشد که بخواهید در اکسل اطلاعات لیست شده را پس از مدتی بازبینی و تصحیح نموده و پس از این کار ردیف مورد نظر در اکسل را از مابقی ردیف‌ها با استفاده از رنگ‌ها متمایز کنید. البته این کار با استفاده از انتخاب سل‌های مورد نظر و استفاده از Fill Color در Home > Font در اکسل ۲۰۰۷ به راحتی انجام می‌شود. ولی نکته اینجاست که چنانچه سل‌های مورد نظر زیاد باشد و نیاز به وقت زیاد برای این کار باشد تکلیف چیست؟ راهکار ساده‌ای در اکسل با استفاده از قابلیت Conditional Formattingوجود دارد
با این آموزش اکسل همراه باشید


محافظت سلولها در برابر تغییرات در اکسل
1- قفل کردن یا از حالت قفل خارج کردن (lock / unlock) سلولهای مورد نظردر شیت

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


ناحیه مورد نظر یا سلول مورد نظر را انتخاب کنید و کلیک راست کنید.

به مسیر format cell>protection>lock بروید

تب lock * را تیک بزنید.

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


2- انجام عملیات محافظت شیت (Protect Sheet)
در excel 2007 برای اینکار باید ابتدا به منوی review بروید.(در اکسل 2003 به منوی tools بروید) protect sheet را انتخاب کنید

برای اینکه بعدا بتوانید از حالت protect sheet(unprotect)خارج شوید میتوانید پسورد بگذارید
گزینه هایی در زیر عبارت Allow all user of this worksheet وجود دارد که عملیات را درباره سلولهای قفل شده نمایش داده میشود و میتوانید آنها را هم با توجه به سلولهای قفل شده یا نشده انتخاب کنید و کاربر را محدود کنید.

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

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


به مسیر review>protection> allow users to edit range می رویم.(در excel 2003 اینکار از منوی tools امکانپذیر است)

با استفاده از گرینه new مناطقی را که میخواهیم اضافه می کنیم

و درنهایت password *میدهیم

هنوز کار تمام نشده و مثل حالت قبلی باید sheet * را محافظت کنیم.

برای دسترسی به این سلول از ما پسورد خواسته میشود.

محافظت فایلهای اکسل workbook می توانید review /protection/protect workbook (در اکسل 2003 tools/protection/protect را انتخاب کنید تا از فایلهای اکسل نیز محافظت کنید


محافظت از یک workbook با استفاده از کلمات عبور
وقتی که protect workbook, protect sheetیاprotect and share workbook را انتخاب میکنید یک کلمه عبور برای غیرفعال کردن محافظت(unprotect) باید استفاده شود. می توانید از تنها ازی یک کلمه عبور برای هر workbook یا worksheet که محافظت کرده اید استفاده کنید.
قفل کردن یک workbook در 2 حالت structure و window انجام می گیرد .فروش درب ضد سرقت (http://****************)




در حالت structure قابلیتهای زیر(درو اقعی بیشتر عملیات کار با شیت) از یک شیت گرفته می شود:



جابجا کردن sheet
تغییر نام و رنگ تب sheet
حذف و اضافه کردن sheet
hide و unhide کردنsheet 

Text to Columns ابزاری فوق العاده اما فراموش شده دراکسل

 برای جداکردن قسمتهای مختلف یک سلول  اکسل ابزاری فوق العاده در اختیار افراد قرار داده است. کافیست به تب DATA سر بزنید و در منویData Tools نگاهی به Text to Columns بیندازید.

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

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

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

 

 روی گزینه Finish کلیک کنید تا تمام محتواهایی که بین آنها Space وحود دارد را از هم جدا کند:

 

اما فرض کنید میخواهید نام و نام خانوادگی باهم بیاید و فقط شماره ها جدا شوند برای اینکار بجای گزینه Finish باید Next را بزنید:

 دو خط در قسمت Data Preview مشخص است. این خطها نشان دهنده مکان دو Space موجود در سلول است یکی فاصله بین نام و نام خانوادگی و دومی فاصله بین نام خانوادگی و شماره. برای اینکه میخواهید نام و نام خانوادگی با هم بیاید باید خط اول را پک کنید. برای اینکار میتوانید روی خط اول دبل کلیک کنید.

نکته: اگر بخواهید محتوای سلول را به بخشهای بیشتری تقسیم کنید میتوانید هرجای متن را که خواستید در قسمت Preview کلیک نمایید. همچنین اگر بخواهید جای برش محتوا را تغییر دهید میتوانید با موس خطها را جابجا کنید.

 

حالا روی Next کلیک کنید تا به مرحله بعد بروید. در قسمت Destination باید مشخص کنید که محتواهای جدا شده در کجا قرار داده شود. در حالت پیش فرض روی همان ستونی است که محتوای اولیه وجود دارد و اگر finish را بزنید جایگزین متن اصلی میشود اما میتوانید مثلا سلول B1 را انتخاب کنید تا نام و نام خانوادگی را در ستون B و شماره را در ستون C قرا دهد.

در قسمت data format نیز میتوانید فرمت مورد نظرتان را انتخاب کنید.

 

معرفی فرمول SUMIFS

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

 

همه ما می دانیم که فرمول SUMIF برای جمع بستن چندین داده با قید یک شرط بکار میرود. برای مثال اگر شما داده های زیر را داشته باشید و بخواهید بدانید که در منطقه “تهران” مجموعاً چقدر فروش داشته اید؟ و از آنجاکه فقط یک شرط برای محاسبات جمع مد نظر است پس در نتیجه باید از فرمول SUMIF استفاده کنید. حاصل عدد ۳۴۳ خواهد بود، یعنی:

 

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

=sumif(range;criteria;[sum_range])

=sumif(محدوده ای که شرط در آن قرار دارد; خود شرط; محدوده ای که میخواهید اعدادش را جمع کنید)

اما همانطور که می­دانید این فرمول محدودیتهای خاص خودش را دارد. یعنی فقط می توانیم یک شرط برای آن تعیین کنیم.

حال اگر اطلاعاتمان به شکل زیر بود و در نظر داشتیم بدانیم که از محصول شماره ۱ در تهران چقدر فروش داشته ایم باید از چه فرمولی استفاده می کردیم؟

تنها کافیست یک S جمع به فرمولتان اضافه نمایید یعنی SUMIFS. در نتیجه شما خواهید توانست شروط بیشتری برای فرمول جمع تان بکار ببرید.

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

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

=sumifs(sum_range;criteria_range1;criteria1;criteria_range2;criteria2;…)

 

 

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

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

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

طریقه ایجادTABLE در اکسل

برای تبدیل یک محدوده به جدول می توان به ۲ طریق زیر عمل کرد:

1. محدوده ی سلول هایی که قصد تبدیل آنها به جدول دارید را انتخاب کنید. سپس از طریق Home > Styles > Format As Table با انتخاب یکی از حالات، استایلی برای محدوده انتخاب کنید. در اینجا پنجره ای باز خواهد شد و این سوال را از شما می پرسد که آیا این محدوده را تبدیل به جدول کنم که با زدن دکمه ok محدوده تبدیل به جدول خواهد شد.
2. محدوده را انتخاب کنید و از Insert > Tables > table ابزار table را انتخاب نمایید یا اینکه کلید ترکیبی Ctrl+T را فشار دهید.

چند تکنیک درباره جدولی که ایجاد کردیم:

1. برای جدول ایجاد شده به طور خودکار اکسل نامی در نظر می گیرد. از این نام در فرمول نویسی ها می توانیم استفاده کنیم.
2. اگر در زمان ایجاد جدول چک باکس مربوط به سربرگ را تیک زده باشید، ردیف اول داده ها به عنوان سربرگ جدول در نظر گرفته می شود. نام هر یک از ستون ها نیز با عنوان سربرگ شناخته می شود.
3. زمانی که به سمت پایین حرکت کنید دیگر حروف الفبایی مربوط به نام ستون ها نشان داده نمی شود و به جای آن عنوان سربرگ نمایان خواهد شد.
4. در پایین جدول می توانید ردیف خودکاری اضافه کنید که این ردیف می تواند جمع، شمارش تعداد سلول ها و… را به صورت خودکار انجام دهد. این ردیف با کلیک بر روی جدول و فعال شدن Table Tools از آدرس Design>Table Style Options>Total Row انجام می شود. با زدن تیک چک باکس Total Row ردیف Total اضافه خواهد شد. نوع خروجی که این ردیف قرار است نمایش بدهد از لیست مربوط به هر سلول قابل انتخاب می باشد.
5. جدول قابلیت بزرگ شدن دارد. زمانی که در آخرین سلول جدول مقداری را وارد کنید و بعد از آن بخواهید به ردیف بعدی بروید با نگارش داده ای در سلول، آن ردیف به جدول اضافه خواهد شد. حتی ردیف جمع که در شماره ۴ بیان کردیم هم یک ردیف به سمت پایین جابجا می شود.
6. در صورتی می خواهید داده های تکراری را از جدول حذف کنید می توانید با کلیک بر روی سلولی از جدول و فعال شدن Table Tools از Design>Tools>Remove Duplicates حذف کردن داده های تکراری را فعال کنید. پنجره ای باز خواهد شد و این موضوع را باید در این پنجره مشخص کنید که داده ها از کدام ستون بررسی و حذف شوند.
7. فیلتر و مرتب سازی هوشمندانه تر در جدول قابل انجام می باشد. در جدولی که ایجاد کردیم سربرگ آن در حالت عادی به صورت فیلتر در خواهد آمد. با زدن هر کدام از این فیلترها و انتخاب نوع آن می توانیم مواردی که قصد فیلتر آنها را داریم انتخاب کنیم.
8. در صورتی که  خواهیم دکمه فیلتر در سربرگ نمایش داده نشود با فعال کردن table Tools و بعد از آن از Design>Table Style Options>Filter Button چک باکس بودن یا نبودن فیلتر در سربرگ را انتخاب می کنیم.
9. در جداول لازم نیست فرمولی که می نویسیم را برای بقیه سلول ها کپی کنیم بلکه با نوشتن فرمول برای اولین سلول و زدن کلید اینتر بقیه سلول های جدول به صورت خودکار تکمیل خواهند گشت.
10. ابزاری که در جدول خیلی مورد توجه هست، Structure Reference ها هستند. در زمان فرمول نویسی با انتخاب هر سلول، نام عادی آن در نظر گرفته نمی شود بلکه نامی از ستون آن انتخاب می شود که به این حالت خواهد بود:

=[@عنوان سربرگ ستون]

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

[عنوان سربرگ ستون] نام جدول=

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

 

فرمول در کسل

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

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

پیدا کردن سطرهای تکراری و حذف آنها در اکسل

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

 

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

پیدا کردن سطرهای تکراری در اکسل و حذف آنها با تابع داده DATA FUNCTION

از این روش زمانی استفاده کنید که:

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

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

در این پنجره اگر تیک My data has headers را بزنید، اکسل از عنوان ستون های شما در کادر مربوط به Columns استفاده خواهد کرد. در کادر Columns تمامی ستون هایی که باید تکراری بودن مقادیر آنها بررسی شود، لیست شده است. پس از اتمام عملیات، اکسل تعداد سطرهای تکراری و غیر تکراری را اعلام خواهد کرد.

پیدا کردن سطرهای تکراری در اکسل با قالب بندی شرطی

از این روش زمانی استفاده کنید که:

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

با استفاده از قالب بندی شرطی می‌توانید سطرهایی با داده های تکراری را به راحتی پیدا کرده و قالب آن را به دلخواه خود تغییر دهید. برای این منظور محدودهای که در آن داده‌های سطرها تکراری هستند را انتخاب کنید. از منوی HOME به زیر منوی Styles رفته در بخش Conditional Formatting کلیک کنید. در منوی باز شده قسمت Highlight Cells Rules را انتخاب کنید و در این قسمت به Duplicate Values بروید. در پنجره باز شده اگر مقدار Duplicate را انتخاب کنید، داده‌های تکرای قالب بندی خواهند شد و اگر Unique را انتخاب کنید، داده‌های بدون تکرار قالب بندی می‌شوند. قالب موردن نظرتان را از سمت راست و از منوی کره‌کره‌ای انتخاب کنید یا در Custom Format قالب دلخواه خود را تعریف کنید.

پیدا کردن سطرهای تکراری در اکسل با استفاده از جدول های پاشنه‌ای

از این روش زمانی استفاده کنید که:

• می‌خواهید لیستی از داده‌های بدون تکرار ایجاد کنید.
• در لیست اصلی داده‌هایتان دنبال داده‌های تکراری هستید و می‌خواهید مطمئن شوید که، همه داده‌ها غیر تکراری هستند.
• وجود داده‌های تکراری نادرست نیست و قصد جمع بندی و خلاصه کردن آنها را دارید

اگر می‌خواهید از داده‌های موجود به سرعت ستونی از داده‌های غیر تکراری تولید کنید، استفاده از جدول پاشنه‌ای مناسب است. برای ایجاد یک جدول پاشنه‌ای، از منوی INSERT به زیر منوی Tables رفته و روی Pivot Table کلیک کنید. در پنجره باز شده محدوده داده‌ها را انتخاب کنید. OK نمایید تا جدولتان ایجاد شود. به طور پیش فرض جدول در برگه‌ای جدید ایجاد خواهد شد، اگر می‌خواهید جدولتان در همان برگه داده‌ها ایجاد شود، دکمه رادیویی Existing Worksheet را انتخاب کرده و سلول مورد نظر، برای درج جدول را انتخاب کنید. اگر داده‌های شما در جای دیگری به غیر از برگه فعلی قرار دارد، دکمه رادیویی Use an external data source  را انتخاب نموده و آنها را فراخوانی کنید.

 پس از ایجاد جدول پاشنه‌ای، از قسمت Choose filed to add report عنوان ستونی که اضافه کرده‌اید را به قسمت ROWS درگ کنید.                                                                                                      با تنظیم Value Filed Settings بر روی شمارنده، تعداد داده‌های تکراری را می‌توانید مشاهده کنید.

 

تنظیمات بیشتر بر روی جداول پاشنه‌ای در اکسل

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

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

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

پیدا کردن سطرهای تکراری در اکسل با استفاده از مرتب کردن داده ها Data Sort

از این روش زمانی استفاده کنید که:

• تعداد داده‌هایتان کم است و می‌توانید با کنترل چشمی آنها را حذف کنید.
• می‌خواهید سطرهای تکراری را ببنید، آنالیز کنید و قبل از حذف، تکراری بودن آنها را تایید کنید.
• نوع داده‌هایتان ساده است و می‌توانید تکراری بودن آنها را تشخیص بدهید. (داده هایتان ۱۵ رقمی و ترکیبی از عدد و حرف نیست!)

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

برای مرتب کردن داده‌ها، کل داده‌هایتان را انتخاب کرده و از منوی DATA به زیر منوی Sort&Filter رفته و بر روی Sort کلیک کنید. در پنجره باز شده در قسمت Column ستونی را که می‌خواهید بر اساس آن مرتب سازی کنید، انتخاب نماید. OK کنید داده‌هایتان مرتب خواهند شد.

 

پیدا کردن سطرهای تکراری در اکسل با استفاده از فیلتر پیشرفته Advanced Filter

از این روش زمانی استفاده کنید که:

• فقط می‌خواهید داده‌های بدون تکرار را مشاهده کنید.
• قصد ندارید داده‌های تکراری را حذف کنید و پنهان کردن آنها کافی است.

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

برای اعمال فیلتر پیشرفته بر روی داده‌ها، از منوی DATA به زیر منوی Sort&Filter رفته و بر روی Advanced کلیک کنید. در پنجره باز شده محدوده داده‌ها را انتخاب کرده و دکمه رادیویی Unique records only را بزنید. OK کنید. اگر می‌خواهید داده‌های اصلی بدون تغییر باقی بماند، دکمه رادیویی Copy to another location را بزنید تا اکسل داده‌هایتان را به جای دیگری کپی کرده و سپس فیلتر نماید.

 

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

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

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

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

 

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

 

سطرهایی که داده‌های تکراری دارند را می‌توان در هم ترکیب کرد. بدین معنی که اگر شماره و طول آرماتور ها یکسان است، می‌توان تعداد آرماتورهای این دو سطر را با هم جمع کرده و داده‌های دو سطر را تنها در یک سطر نوشت. در ردیف ۱ آرماتور با شماره ۱۰ و طول ۲۴۸ سانتیمتر با ردیف ۹ تکراری است. تعداد آرماتور ردیف ۱ و ردیف ۹ را جمع کرده (۱۳۵=۲۳+۱۱۲) و کلاً در یک سطر بنویسید.

قالب بندی خانه ها دراکسل

در Excel اين توانايي را داريم که قالب بندي يا فرمت يک سلول را تغيير داده و فرمت آن سلول را مثلاً تاريخ يا درصد يا زمان يا متن يا .... کنيم. همچنين ميتوانيم دور سلول كادر بياندازيم يا رنگ زمينه آنرا عوض كنيم. براي قالب بندي خانه ها در Excel مراحل زير را طي ميکنيم :

روش اول :

1- انتخاب منوي Format
2- انتخاب گزينه Cells

روش دوم :

بر روي خانه هاي مورد نظرکليک راست کرده و گزينه Format Cells را انتخاب ميکنيم.
با اجراي يکي ازسه روش بالا پنجره اي باز ميشود که داراي Tab هاي زير است :

روش سوم :

فشردن کليدهاي ctrl +1

الف - Number :

توسط اين Tab ميتوانيم نوع اطلاعات ورودي را تعيين کنيم. اين Tab شامل گزينه هاي زير ميباشد :
- Sample : هر فرمتي را که انتخاب کنيم، بر روي محتواي سلول انتخابي نمايش ميدهد.
- Category : در اين قسمت نوع داده را مشخص ميکنيم که شامل انواع زير ميباشد :

1- General : اين گزينه عددها را به صورت رشته اي از رقمهاي متوالي و بدون هر گونه قالب بندي نشان ميدهد. و اگر عددي در سلول جا نشودآنرا به صورت نمايي نشان ميدهد.

2- Number : مقادير را بصورت رشته اي از رقمهاي متوالي نشان ميدهد. در اين حالت اگر عدد در سلول نگنجد سلول بزرگتر ميشود. همچنين در اين قسمت ميتوانيم تنظيمات زير را انجام دهيم :
- Decimal Places : در اين قسمت ميتوانيم تعداد ارقام بعد از اعشار را تعيين کنيم.
- Negative Number : در اين حالت ميتوانيم مشخص کنيم که عدد منفي به همان صورت نشان داده شود يا به رنگ قرمز يا سياه با علامت منفي نشان داده شود. يا به رنگ قرمز بدون علامت منفي نشان داده شود. ( توجه داشته باشيد كه اين گزينه فقط روي اعداد منفي عمل ميكند. )
- Use 1000 Separator : اگر در کنار بگذاريم اعداد را سه رقم ، سه رقم از سمت راست جدا کرده و علامت (، )اين گزينه ميگذارد.

3- Currency : مقادير را همراه با سمبل پول رايج نشان ميدهد. در اين حالت ميتوان تنظيمات زير را انجام داد :
- Symbol : نوع واحد پول را مشخص ميکنيد. در اين قسمت واحد پول کشورهاي مختلف نمايش داده شده و ميتوانيم واحد دلخواه خود را انتخاب کنيم.
- Negative Number : اعداد منفي چطور نمايش داده شوند.

4- Accounting : مانند حالت Currency است . اين قالب بندي ، قالب بندي حسابداري ميباشد و علامت پولي در منتهاالهيه سمت چپ آن نوشته ميشود.

5- Date : تاريخ را با قالب بندي خاص تاريخ نشان ميدهد. و ما ميتوانيم انواع قالب بندي هاي تاريخ را ديده و انتخاب کنيم. مثلاً مدلي را انتخاب کنيم که فقط روز و ماه را نشان دهد. و يا مدلي را انتخاب کنيم که روز را به عدد و ماه را به حروف نشان دهد.

6- Time : زمان را با قالب بندي هاي خاص زمان نشان ميدهد. انواع قالب زماني در اين قسمت وجود دارند. ما ميتوانيم مدلي را انتخاب کنيم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمايش دهد و يا مدلي را انتخاب کنيم که ساعت را از 1 تا 24 نمايش دهد. و يا ....

7- Percentage : عددها را همراه با علامت درصد نشان ميدهد.

نکته
در تمام قالب بندي ها بجز Percentage فرقي نميکند که ابتدا اطلاعات را در سلول وارد کنيم يا اول قالب بندي را تنظيم کنيم. ولي در حالت Percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage کنيم ، عدد را در 100 ضرب ميکند. ولي اگر ابتدا فرمت را Percentage کنيم، و بعد عدد را بنويسيم، تغييري در عدد نميدهد.

8- Fraction : مقادير را بصورت عدد صحيح نشان ميدهد که بدنبال آن نزديک ترين کسر به مقدار واقعي ظاهر ميشود. براي نوشتن يک عدد مخلوط کافي است ابتدا قسمت صحيح را نوشته سپس يک فاصله بدهيم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاريم و بعد مخرج کسر را بنويسيم. اگر عدد 6 5/10 را بنويسيم، پس از Enter کردن مقدار سلول 6 1/2 ميشود. اگر بخواهيم همان مقدار اوليه باقي بماند، کافي است، در قسمت Fraction مقدار 3/10 را انتخاب کنيم. در اين حالت نگاه ميکند ببيند مخرج اصلي بايد درچند ضرب شود تا مخرج انتخابي شود سپس صورت را هم در همان عدد ضرب ميکند.

9- Scientific : مقادير را با قالب بندي علمي نشان ميدهد.

10- Text : مقادير را به همان صورتي که وارد شده اند نشان ميدهد. اگر فرمولي را به صورت متن قالب بندي کرده باشيم، Excel آن را به صورت متن نشان ميدهد و مقادير آنرا محاسبه نميکند.
(کاردرکارگاه 7 تمرين 8 تا 13 )

11- Special : مقادير را با استفاده از قواعد قالب بندي خاص ( مانند كد پستي ، کد پستي به اضافه چهار رقم، يا شماره تلفن، شماره تامين اجتماعي) نشان ميدهد. مثلاً اگر در يک سلول يک شماره تلفن ( در حالت 10 رقمي) تايپ کنيم ، با انتخاب اين فرمت به صورت قالب بندي تلفن در مي آيد. مثلاً اگر شماره IT (3116681184) را وارد ميکنيم، به فرمت تلفن در آمده (1184-668 (311) ) و مي فهميم که 311 کد شهرستان ، 668 کد محله و 1184 شماره تلفن IT ميباشد.

12- Custom : براي ايجاد يک قالب بندي جديد از اين گزينه استفاده ميکنيم كه خارج از بحث ما ميباشد.

ب- Alignment :

توسط اين Tab ميتوانيم جهت قرار گرفتن اطلاعات در سلول را تعيين کنيم. اين Tab شامل قسمتهاي زير است :
1- Horizontal : محل قرار گرفتن افقي متن را تعيين ميکند. اين قسمت شامل گزينه هاي زير است :
• General : هم ترازي پيش فرض
• Left : هم ترازي داده ها را به سمت چپ ( استفاده براي ارقام)
• Center : هم ترازي داده ها در مرکز
• Right : هم ترازي داده ها به سمت راست ( براي متون )
• Fill : تمام سلول را با متني که در آن نوشته شده است ، پر ميکند.
• Justify : يک تراز مناسب براي سلول در نظر ميگيرد . معمولاً براي زماني است که اطلاعات در سلول نگنجد.
• Center Across Selection : وسط متن را در وسط سلولهاي انتخابي ميگذارد.

2- Vertical : محل قرار گرفتن اطلاعات را بطور عمودي تنظيم ميکند. اين گزينه شامل قسمتهاي زير است :
• Bottom : هم ترازي داده ها در پايين سلول
• Top : هم ترازي داده ها در بالاي سلول
• Center : هم ترازي داده ها در وسط يا مرکز سلول
• Justify : داده ها در داخل سلول هم تراز ميشوند. ( بدين معني که داده ها در داخل سلول بصورت مساوي جاسازي ميشوند. مانند متون موجود در روزنامه ها )

3- Orientation : شامل قسمتهاي زير است :

• زاويه متن را نسبت به افق بوسيله ماوس ميتوان تعيين کرد.
• Degrees : زاويه متن را نسبت به افق بوسيله تايپ زاويه يا با كليد هاي Increase و Decrease تعيين كرد.

4- Text Control : شامل قسمتهاي زير است :

• Wrap Text : اگر اندازه متن بيشتر از سلول باشد توسط اين گزينه ميتوان آنرا شکست. در نتيجه ارتفاع سطر افزايش مي يابد.
• Shrink to Fit : اگر اندازه متن از سلول بيشتر باشد با انتخاب اين گزينه متن به اندازه اي کوچک مي شود که در داخل سلول بگنجد.
• Merge Cells : اگر اندازه متن از سلول بيشتر باشد ميتوان سلولهايي را که متن اشغال کرده را انتخاب نمود و سپس در کنار اين گزينه تيک زد. اين عمل باعث ميشود که اين سلول ها بهم پيوسته و يک سلول شوند.

ج- Font :

توسط اين قسمت ميتوان نوع خط ، اندازه، رنگ و ... خطوط را تعيين نمود. اين Tab شامل گزينه هاي زير است :

1- Font : در اين قسمت نوع خط را تعيين ميکنيم.
2- Font Style : در اين قسمت ميتوانيم تعيين کنيم که خط کج ( Italic) يا تو پر(Bold) يا کج و تو پر (Bold Italic) و يا معمولي (Regular) باشد.
3- Size: در اين قسمت سايز قلم را تعيين ميکنيم.
4- Underline: در اين قسمت ميتوانيم تعيين کنيم که زير متن خط کشيده شود يا نه. و نوع خط را تعيين ميکنيم.
• None : خط نميکشد.
• Single : خط تکي زير متن ميکشد.
• Double : دو خطي زير متن ميکشد.
• Single Accounting : تک خط زير کشيده ترين حرف کشيده ميشود.( يعني تمام حروف بالاي خط نوشته ميشوند.)
• Double Accounting : دو خطي زير تمام حروف و زير کشيده ترين حرف کشيده ميشود.

5- Color : در اين قسمت رنگ خطوط را متن را تعيين ميکنيم.

6- Effect : شامل قسمتهاي زير است :
• Strike Through : بر روي متن خط ميکشد.
• Super Script : براي بالا نويسي استفاده ميشود. اگر بخواهيم A2 بنويسيم کافي است A2 را نوشته . سپس 2 را انتخاب کنيم و اين گزينه را فعال کنيم.
• Sub Script : براي زير نويسي استفاده ميشود.

7- Preview : پيش نمايشي از انتخابات را نشان ميدهد.
د- Border : توسط اين Tab ميتوانيم حاشيه بندي انجام دهيم. که شامل قسمتهاي زير است :
1- Style : در اين قسمت نوع خط حاشيه را مشخص ميکنيم.
2- Color : در اين قسمت رنگ حاشيه را مشخص ميکنيم.
3- Presets : در اين قسمت محل خطوط را مشخص ميکنيم.
• None : حاشيه نميگدارد.
• Outline : دور سلولها حاشيه ميگذارد.
• Inside : داخل سلولهاي انتخابي را حاشيه ميگذارد.
4- Border : در اين قسمت ميتوانيم تعيين کنيم که کدام قسمت از سلول حاشيه داشته باشد. نيز ميتوانيم تعيين کنيم که فقط سمت چپ سلول خط بيفتد و يا فقط بالا و يا پايين سلول حاشيه داشته باشد.

نکته
براي انداختن حاشيه در اکسل ابتدا رنگ و نوع خط را تعيين ميکنيم و سپس بر روي Icon هاي مورد نياز براي افتادن حاشيه Click ميکنيم.
هـ - Patterns : در اين قسمت ميتوانيم رنگ زمينه را تعيين کنيم. که شامل گزينه هاي زير است :
• Color : در اين قسمت رنگ مورد نظر را انتخاب ميکنيم.
• Pattern : در اين قسمت ميتوانيم از الگوها ( هاشورها) استفاده کنيم. همچنين رنگ هاشور را تعيين كنيم.

حذف قالب بندي سلولها :

همانطور كه در فصل سوم گفته شد براي پاك كردن قالب بندي سلول كافي است سلول يا سلولهاي مورد نظر را انتخاب كرده سپس از منوي Edit زير منوي Clear ، گزينه Formatting را انتخاب ميكنيم.

توسط اين دستور مي توانيم اعمال رياضي، منطقي و ديگر توابع را بر روي داده ها داشته باشيم. براي استفاده از توابع به يكي از روشهاي زير عمل ميكنيم :

روش اول :

1- بر روي Paste Function , Icon ( )كليك مي كنيم.
2- در پنجره باز شده در قسمت Function Category نوع تابع را مشخص مي كنيم. كليه توابع در 8 دسته طبقه بندي شده اند. در دسته All همه توابع قرار دارند و در دسته Most Recently Used توابعي كه به تازگي از آنها استفاده شده است قرار دارند. اگر گروه تابع را ندانيم ميتوانيم آنرا در All پيدا كنيم.
3- در قسمت Function name تابع را مشخص مي كنيم.
4- در پنجره اي كه باز مي شود به دو طريق مي توانيم عمل كنيم:

الف : در كادر Number 1 ،محدوده آدرس اعداد را وارد كنيم. براي وارد كردن محدوده آدرس كافي است ابتدا آدرس اولين خانه محدوده را وارد كرده سپس عملگر آدرس ( : ) را تايپ كنيم و بعد آدرس آخرين سلول محدوده را وارد كنيم. همچنين ميتوانيم بر روي Collapse Dialog اين كادر كليك كرده و محدوده را با Drag انتخاب كنيم.
ب : در هر كادر يك آدرس وارد كنيم. براي اين كار در كادر اول آدرس اولين سلول را تايپ كرده. بر روي كادر دوم كليك كرده ،آدرس دومين سلول را تايپ ميكنيم. و الي آخر .... معمولاً زماني از اين روش استفاده ميكنيم كه سلولها پراكنده باشند.

5- OK را مي زنيم.

روش دوم :

1- منوي Insert را انتخاب مي كنيم.
2- زير منوي Function را انتخاب مي كنيم.
3- بقيه مراحل مانند مرحله 2 به بعد روش اول است.

روش سوم:

1- در خانه تابع را همراه با آرگومانهاي آن تايپ مي كنيم. ( توابع و آرگومانهايش در قسمت بعد توضيح داده شده اند. )
2- اگر قبل از آن = گذاشته باشيم كه Enter مي كنيم در غير اين صورت بر روي = در نوار فرمول Click مي كنيم.

روش چهارم:

1- در يك خانه (خانه اي كه مي خواهيم حاصل نوشته شود) علامت = را تايپ مي كنيم يا علامت = در خط فرمول را مي زنيم.
2- در كادر اول نوار فرمول ( Name Box ) توابع ظاهر مي شوند، كافي است بر روي drop down آن Click كنيم تا همه توابع را ببينيم. تابع مورد نظر را انتخاب مي كنيم.
3- بقيه مراحل مانند مرحلة 4 به بعد روش اول است.

چند تابع معروف

1- تابع مجموع (SUM ) : براي فعال كردن اين تابع يا آنرا تايپ كرده يا به يكي از روشهاي گفته شده در بالا عمل مي كنيم. در پنجره Function Category گروه Math & Trig را انتخاب مي كنيم و در پنجره Function name ،SUM را انتخاب مي كنيم فرمت كلي اين دستور به شكل زير است: ( از اين دو فرمت زماني که بخواهيم فرمول را تايپکنيم استفاده ميکنيم. )

1- SUM -number 1 : number 2
2- SUM -number 1;number2;..

نکته
معمولاً براي زماني كه اطلاعات در سلولهاي مجاور باشند از فرمول اول و اگر اطلاعات در سلولهاي غير مجاور و پراكنده باشند از فرمول دوم استفاده ميكنيم.

2- تابع ميانگين(Average ) : از اين تابع براي محاسبة ميانگين استفاده ميشود .اين تابع جزء گروه Statistical است. شكل كلي آن بصورت زير است :
1- Average - number 1 ; number 2 ;...
2- Average - number 1 : number 2

3- تابع ماكزيمم (Max) در اکسل: ‌از اين تابع براي پيدا كردن ماكزيمم چند مقدار استفاده ميكنيم : اين تابع نيز جزء گروه Statistical است. و شكل كلي آن به صورت زير است :
1- Max - number 1 ; number 2 ; ….
2- Max - number 1 : number 2

4- تابع مينيمم (Min) : از اين تابع براي محاسبة ميانگين اعداد استفاده مي شود و جز توابع گروه Statistical ميباشد. شكل كلي آن به صورت زير است :
1- Min - number 1 ; number 2 ; ….

2- Min - number 1 : number 2

5- تابع شمارنده (Count) : از اين تابع براي شمارش تعداد خانه هاي پر شامل اعداد استفاده ميشود . اين تابع نيز جز توابع گروه Statistical ميباشد و فرم كلي آن به صورت زير است :
1- Count - value 1 ; value 2 ; ….
2- Count - value 1 : value 2

6- تابع Round : از اين تابع براي گرد كردن اعداد استفاده مي شود و جز توابع گروه Math & Trig مي باشد و شكل كلي آن به صورت زير است :
Round - number 1 ; num_digits
قسمت Number براي معرفي عدد و قسمت Num_digits معرف تعداد ارقامي است كه بايد گرد شوند،‌ Excel هنگام گرد كردن نگاه به اولين رقم بعد از رقم حذفي مي كند اگر كوچكتر از 5 بود كه خود اعداد را مي نويسد ولي اگر بزرگتر از 5 بود يك رقم به آخرين رقمي كه ميماند اضافه مي كند.

Auto sum در اکسل :

توسط اين دستور مي توانيم تعدادي خانه را با هم جمع كنيم در اين حالت كافي است در خانه اي Click كنيم، Excel خانه هاي در محدودة خانة انتخابي ما را در نظر مي گيرد و مجموع آنها را حساب مي كند و خانه هاي انتخابي را به ما نشان مي دهد. اگر اين خانه ها مورد نظر ما نبودند، خودمان محدوده را انتخاب مي كنيم. براي فعال كردن اين دستور كافي است آيكون Auto sum ( ) را از نوار ابزار Standard انتخاب كنيم.

برقراری ارتباط میان چند فایل (لینک کردن )وچرخاندن متون درون سلول‏ها در Excel

یکی از امکاناتی که این برنامه در اختیار کاربران قرار می‌دهد، برقراری ارتباط میان چند فایل اکسل، مقادیر موجود در سلول‌های ذخیره شده در هر فایل و دسترسی سریع به هر صفحه (Sheet) در فایل‌های مختلف اکسل است.

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

1- ابتدا عبارت مورد نظر به ‌عنوان گزینه‌ فهرست را در یکی از سلول‌های دلخواه وارد کنید.

2- سلول بالا را برگزیده و روی آن کلیک راست کنید سپس گزینه‌ hyperlink را انتخاب کنید.

3- اکنون می‌توانید به روش‌های مختلفی عملیات لینک ‌دهی را انجام دهید.

1- لینک به سلول مشخص در همان فایل: در پنجره‌ افزودن لینک به Place in This Document بروید و در کادرType the Cell reference نام سلول مورد نظر را وارد و از فهرست صفحات در کادر زیرین آن، همان صفحه‌ فعلی را انتخاب کنید.

2- لینک به صفحه‌ دیگر و سلول مشخص در همان فایل: در پنجره‌ افزودن لینک به Place in This Document مراجعه و در کادر Type the Cell reference نام سلول موردنظر را وارد کرده و از فهرست صفحات در کادر زیرین آن، هریک از صفحات دیگر را که قصد دارید به آنها لینک شوید، انتخاب کنید.

3- لینک به فایل دیگر: در پنجره‌ افزودن لینک، گزینه‌ Existing File and Web page را انتخاب کرده و با استفاده از ابزار مسیردهی، مسیر فایل اکسل موردنظر را برگزیده و در پایان روی Ok کلیک کنید.

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

’sheet’!A1#

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

برای این کار:
ابتدا با استفاده از ماوس، سلول‏هایی که قصد دارید متون درون آن‏ها را زاویه دهید را انتخاب کنید.
سپس بر روی یکی از سلول‏های انتخابی راست کلیک کرده و Format Cells را انتخاب کنید.
در پنجره باز شده به تب Alignment بروید.
در قسمت سمت راست یک نیم دایره مشاهده می‏کنید. اکنون با استفاده از قسمت Degrees و بالا و پایین کردن عدد موجود که در اصل زاویه قرار گیری متن شما است، می‏توانید زاویه‏ای بین 90- درجه تا 90 درجه برای متن درون سلول تعیین کنید.
با فشردن OK تغییرات اعمال می‏شود.

پرکردن اتومات خانه ها در اکسل

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

یکی از امکانات اکسل، ایجاد و مدیریت لیست ها است. منظور از لیست، جدول حاوی اطلاعات است که معمولا سطرهای آن را «رکورد» و ستون های آن را «فیلد» می نامند.

منظور از «رکورد» مجموعه داده های وارد شده در یک سطر  و منظور از فیلد نیز مجموعه داده های وارد شده در ستون ها است.

 

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

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

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

قابلیت نخست که AutoComplete نام دارد، برای وارد کردن داده های متنی تکراری یا مشابه به کار می رود. به عنوان مثال، در لیست زیر و در هنگام پر کردن داده ها، با وارد کردن حرف «م» اکسل کلمه «مرد» را که قبلا تایپ کرده اید، پیشنهاد می دهد. کافیست که کلید Enter را فشار دهید تا این کلمه در سلول درج شود.

 

قابلیت دیگر، کاربرد گزینه Pick From Drop-down List است. از این گزینه برای وارد کردن اقلام تکراری استفاده می شود. به عنوان مثال در تصویر زیر هنگام پرکردن سلول B6 بر روی آن کلیک راست می کنیم و سپس بر روی گزینه Pick From Drop-down List کلیک می کنیم. در این صورت منویی باز می شود که کلیه اقلامی که تاکنون در این ستون وارد شده، نشان داده می شود. با انتخاب گزینه مورد نظر از این منو داده مربوطه در سلول انتخاب شده وارد خواهد شد.

 

ايجاد فرم ورود اطلاعات
منظور از فرم ورود اطلاعات، امکانی است که اکسل برای سهولت در وارد کردن داده های لیست ارایه می دهد. برای استفاده از این قابلیت، باید از دستور Form استفاده کنید. این دستور از جمله دستوراتی است که در نوارهای اصلی اکسل وجود ندارد و باید یک دکمه میانبر از آن را در نوار Quick Access اضافه کرد. برای این منظور با کلیک راست بر روی Quick Access، گزینه  Customize Quick Access Toolbar را کلیک کنید. سپس از منوی باز شده گزینه More commands را انتخاب کنید.

 

در این صورت پنجره Excel Option باز می شود:

 

اکنون از منوی Choose commands from گزینه Commands Not in the Ribbon را انتخاب کنید تا دستوراتی که در نوار اکسل نیستند، در لیست نمایش داده شوند.

 

از لیست باز شده، گزینه Form را انتخاب کرده و روی دکمه Add کلیک کنید.

 

اکنون روی دکمه OK کلیک کنید. در این صورت دکمه Form به دکمه های نوار Quick Access اضافه می شود.

بازمی گردیم به مثال لیست. یکی از سلول های لیست را انتخاب کرده و روی دکمه Form کلیک کنید تا فرم ورود اطلاعات باز شود.

 

همانطور که مشاهده می کنید، فرم ورود اطلاعات به ازای هر فیلد، دارای یک کادر متنی یا عددی است که به راحتی می توان اطلاعات رکورد را در آن وارد کرد. در این فرم گزینه New برای ایجاد یک رکورد جدید، گزینه Delete برای حذف رکورد فعلی، گزینه Find Prev برای نمایش رکورد قبلی، گزینه Find Next برای نمایش رکورد بعدی، گزینه Criteria برای مرور رکوردهای خاص و گزینه Close برای بستن فرم ورود اطلاعات استفاده می شود.

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

برای مرتب کردن این لیست بر اساس ستون نام خانوادگی، ابتدا یکی از سلول های این ستون را انتخاب کرده و سپس زبانه 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 باز کرده و این بار گزینه «نام خانوادگی» را انتخاب می کنیم.

یک سلول می تواند یک «مقدار» یا یک «فرمول» را بپذیرد. برای وارد کردن داده در یک سلول، کافیست که بر روی آن سلول کلیک چپ کنید و سپس داده مورد نظر را تایپ کنید. برای اینکه داده مورد نظر را در نهایت به سلول اختصاص دهید، باید پس از وارد کردن، یکی از کلیدهای Enter یا Tab از صفحه کلید را فشار دهید.

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

 

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

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

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

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

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

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

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

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

برای رونویسی (کپی کردن) یک سلول، ابتدا آن را انتخاب کنید، سپس بر روی سلول انتخابی کلیک راست کنید و از گزینه های موجود، گزینه Copy را انتخاب کنید.

 

جایگزینی (Paste) یک سلول دراکسل
برای جایگزینی یک سلول کپی شده، کافیست سلول جدید را انتخاب، سپس با کلیک راست از گزینه های موجود، گزینه Paste را انتخاب کنید

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

نکته: برای انجام عملیات بالا، می توان از صفحه کلید نیز استفاده کرد. برای کپی کردن از کلیدهای Ctrl+C در ویندوز (Cmd+C در مک)، برای جایگزین کردن از کلیدهای Ctrl+V در ویندوز (Cmd+V در مک) و همچنین برای برش و انتقال یک سلول از گزینه های Ctrl+X در ویندوز (Cmd+X در مک) استفاده می شود.

استفاده از گزینه های Copy و Cut و Paste در زبانه Home
اگر به گزینه های زبانه Home توجه کنید، اولین بخش، بخشی است به اسم Clipboard که می توان عملیات رونویسی، برش و جایگزینی را از این طریق نیز انجام داد.

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

برای حذف محتویات یک سلول کافیست بر روی سلول مورد نظر کلیک راست کرده، و از گزینه های موجود، گزینه Clear Contents را انتخاب کنیم

اما اگر بخواهیم سلول انتخاب شده به همراه محتویاتش را به طور کامل حذف کنیم، از گزینه های موجود که در تصویر بالا نشان داده شده، به جای گزینه Clear Contents، گزینه Delete را انتخاب می کنیم. وقتی یک سلول را به طور کامل حذف می کنیم، به جای سلول مورد نظر یک حفره ایجاد می شود که باید سلول های اطراف جایگزین این فضای خالی شوند. برای پرکردن این فضای خالی، نرم افزار اکسل پیش از حذف سلول از ما می پرسد که سلول های اطراف چگونه جایگزین این فضای خالی شوند. به عبارت دیگر همزمان با انتخاب گزینه Delete پنجره ای مانند تصویر زیر نیز باز می شود. در زیر به بررسی گزینه های این پنجره می پردازیم:

گزینه Shift Cell Left سلول سمت  راست را جایگزین سلول پاک شده می کند.

گزینه Shift Cells Up سلول زیرین را جایگزین سلول پاک شده می کند.

گزینه Entire Row سطر زیرین سلول پاک شده را به جای سطری که سلول پاک شده در آن قرار دارد، منتقل می کند.

گزینه Entire Column ستون سمت راست سلول پاک شده را به جای ستونی که خانه پاک شده در آن قرار دارد، منتقل می کند.

قابلیت رونویسی سلول ها در سلول های همسایه
با کلیک بر روی هر سلول، یک مربع سیاه کوچک در گوشه سمت راست سلول دیده می شود. این مربع دستگیره Auto Fill نام دارد. اگر ماوس را بر روی این علامت نگه دارید، اشاره گر ماوس به شکل علامت مثبت دیده خواهد شد. در صورتی که این دستگیره را به جهت های مجاور (چپ- راست، بالا و پایین) بکشید (Drag کنید)، خواهید دید که محتویات سلول در در سلول های انتخاب شده کپی خواهند شد. این قابلیت زمانی کاربرد دارد که بخواهید داده های تکراری در سلول های مجاور وارد کنید.

دستگیره Auto Fill در مورد اعداد قابلیت ویژه ای دارد. از این قابلیت برای افزایش و یا کاهش عددها در یک صفحه گسترده استفاده می شود. اگر هنگام کشیدن دستگیره Auto Fill کلید CTRL را هم نگه دارید، این قابلیت اجرا خواهد شد. در صورتی که دستگیره را به سمت راست یا پایین بکشید، عددها افزایش پیدا خواهند کرد. در صورتی که دستگیره به سمت سلول های قبلی (سمت چپ) یا بالا کشیده شود، عددها کاهش می یابند.

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

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

آدرس دهی نسبی: در این روش آدرس دهی، ابتدا نام ستون و سپس شماره سطر قرار می گیرد. به طور نمونه سلولی که از برخورد ستون F و سطر ۹ ایجاد می شود دارای آدرس نسبی  F9 است.

آدرس دهی مطلق: در این نوع آدرس دهی، پیش از نام ستون و سطر به طور مجزا علامت $ قرار می گیرد. به طور نمونه سلولی که از ستون F و سطر ۹ ایجاد می شود دارای آدرس مطلق F$9$

ویرایش سلول ها
یک سلول را می توان به سه روش ویرایش کرد. روش اول این است که بر روی سلول دو بار کلیک کنید. در این صورت محتویات درون آن آماده تغییر می شود و می توانید داده ها را ویراش کنید. دومین روش استفاده از نوار فرمول است. برای کاربرد نوار فرمول رجوع کنید به در درس اول و شماره ۵ از بخش «معرفی بخش ها و زبانه های اصلی»؛ محتویات هر سلول را می توانید در نوار فرمول مشاهده کنید و آن را ویرایش کنید.

سومین روش ویرایش سلول ها، استفاده از کلید F2 است. اگر این کلید را فشار دهید، سلول مورد نظر در حالت ویرایش قرار می گیرد.

اضافه کردن سطر و ستون
برای اضافه کردن سطر و ستون از زبانه Home و در بخش Cells بر روی گزینه Insert کلیک می کنیم. عبارت Insert Sheet Rows برای اضافه کردن سطر و عبارت Insert Sheet Columns برای اضافه کردن ستون به کار می رود.

حذف کردن سطر و ستون
برای حذف کردن سطر و ستون از زبانه Home و در بخش Cells بر روی گزینه Delete کلیک می کنیم. عبارت Delete Sheet Rows برای حذف کردن سطر و عبارت Delete Sheet Columns برای حذف کردن ستون به کار می رود.

 

 

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

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

 

در روش عددی، ابتدا بر روی سطر یا ستون مورد نظر کلیک راست کنید. از گزینه های موجود، گزینه Row Height در excel را برای تغییر سطر و یا گزینه Column Width را برای تغییر ستون انتخاب کنید. در هر دو حالت پنجره ای باز می شود که اندازه فعلی را نشان می دهد. می توانید اندازه جدید را که دقیق تر از حالت قبلی خواهد بود، وارد کنید.

ذخیره پرونده اکسل
در پایان این درس به شیوه ذخیره سازی یک پرونده می پردازیم.

برای ذخیره یک پرونده بر روی دکمه Office «گوشه بالا و سمت چپ» کلیک کنید و از پنجره باز شده، و با کلیک بر روی گزینه Save as مکانی که می خواهید پرونده مورد نظرتان را ذخیره کنید  را انتخاب کنید. (از گزینه های مقابل Save as، نخستین گزینه را انتخاب کنید.)

از بخش File name نیز می توانید یک نام برای سند خود انتخاب کنید و در پایان گزینه Save را کلیک کنید.

توجه: کلیدهای میانبر ذخیره سازی در اکسل Ctrl+S در ویندوز و Cmd+S در مک هستند.

 

 

 

 

سرعت بخشیدن به تایپ توابع دراکسل

فرمولهای Excel در دسته های مختلفی دسته بندی شده اند كه برای كارهای ریاضی، مالی ، آماری و كار با رشته های متنی و كار با داده های زمان و …. به كار می روند.
در اکسل توابع ریاضی مشتمل است بر انواع و اقسام توابع مثلثاتی، قدر مطلق، تقسیم باقیمانده ، توان، مجموع مربعات و ….
توابع متنی شامل جستجو در یك متن، حذف و جایگزینی بخشی از یك متن و …. می باشد. توابع آماری برای محاسبه واریانس، انحراف از معیار و كلی چیزهای دیگر كه آرزویشان را داریم هم وجود دارد برای داشتن پیش زمینه ذهنی سعی كنید حتماً سری به لیست این فرمولهای بزنید تا در مواقع لزوم از امكانات تعبیه شده در خود Excel استفاده كنید. تابع نویسی از یك علامت = شروع می شود به محض وارد كردن یك علامت مساوی در یك خانه خالی Excel می فهمد مقدار این خانه یك داده تنها نیست بلكه باید آنرا حساب كند.
وقتی نیاز به یك تابع را احسای می كنید به عنوان نخستین سؤال این پرسش مطرح می شود كه از كجا تابع مورد نظر خود را بیاییم؟
ممكن است شما دقیقاً بدانید می خواهید چه كاری انجام دهید ولی اطلاعی نداشته باشید كه آیا در اكسل توابع مورد نیاز شما پیش بینی شده است یا خیر؟ از كجا بدانیم تابعی كه مورد نیاز ما است كجاست؟ شاید هم تابعی را چند ماه پیش به كار برده اید ولی هم اكنون آنرا فراموش كرده اید. فرض كنید می خواهید میزان بازپرداخت ماهانه برای یك وام 180 هزار دلاری را كه ظرف ده سال با بهره 6 درصد باید بازپرداخت شود محاسبه كنید. كافی است در خط فرمول قرار بگیرید و دكمه function insertرا بفشارید و در آنجا عبارت مورد نظر خود را در بخش جستجو وارد كنید. یا حتی می توانید نوع تابع مورد نظر خود را از بخش category انتخاب نموده و در لیست مربوطه تك تك روی اسامی هر یك از توابع بایستید و تعریف آنرا بخوانید. مثلاً تابع PMT همان تابعی است كه ما بدنبال آن هستیم و در تعریف آن آورده شده است، محاسبه میزان باز پرداخت ماهانه برای وام با اقساط ثابت و بهره ثابت با انتخاب این تابع كادری باز می شود كه آرگومانهای تابع را از ما دریافت می نماید.
قبل از این كار با كلیك روی گزینه This Topic Help on می توانید نكات راهنما در مورد این تابع را بخوانید. در این راهنما syntax یا به عبارتی فرم دقیق تابع و مشخصات آرگومانهای ورودی آن آورده شده است و همچنین یك مثال كامل از یك نمونه كاربرد تابع ذكر شده است. كه می توانید این مثال را كپی كرده و در فایل خود بچسبانید و نحوه كار آن را ببینید.
پنجره ای كه آرگومانهای تابع را دریافت می كند به تفكیك مقادیر ورودی تابع ما را نشان داده است و منتظر می ماند شما مقدار هر یك از وارد نمایید. در اینجا می توانید به هر یك از این آرگومانها یك مقدار عددی یا آدرس یك خانه تخصیص دهید. آرگومانهایی كه ارائه مقدار آنها ضروری است با رنگ مشكی تیره مشخص شده اند و حتماً باید مقدارشان را مشخص كنید. بقیه آرگومانها مقدارشان اختیاری است و اگر معلوم نكنید به صورت پیش فرض به آنها مقدار داده می شود.
در كادر مقابل هر آرگومان كه قرار بگیرید در پایین ترین بخش صفحه تعریفی از ویژگیها و مشخصات این آرگومان آورده می شود.
تابع 5 PMT(Pate. Noper.Pv.type)‌آرگومان دریافت می دارد كه سه تای اول آن اجباری هستند و دو تای آخری اختیاری.
برای محاسبه اقساط ماهانه وام ده ساله 180 هزاردلاری با بهره 6 درصد در بخش Rate كه نشانگر بهره است مقدار 12/6% را باید وارد كنید. بهره 6 درصد سالیانه برای هر ماه معادل نیم درصد است.
توجه داشته باشید كه نرخ سود را باید ماهانه وارد نمود نه سالانه. آرگومان Noper مبین تعداد كل اقساط وام است كه در اینا معادل 12*10=120 خواهد بود. Pv هم نشانگر كل میزان وام دریافتی بدون در نظر گرفتن بهره و معادل 180000 است. با فشردن دكمه ok نتیجه كار در كاربرگ شما پدیدار می شود ($.79 . 19) یعنی شما هر ماه تقریباً هزار و هفتاد و نه دلار باید بپردازید. چون این مقدار را باید بپردازید و به اصطلاح حسابداری پرداختنی است در داخل پرانتز به عنوان یك عدد منفی – و با رنگ قرمز نشان داده می شود.
مراحلی كه در بالا توضیح دادیم برای وقتی است كه شما خود تابع و یا آرگومانهای آنرا به درستی نمی شناسید. ولی در دفعات بعدی كه نام تابع را به خاطر می آورید كافی است در داخل خود كاربرگ نام تابع را وارد نمایید. بدیهی است باید محتوای خانه ای كه قرار است از طریق یك تابع محاسبه شود با یك علامت مساوی شروع شود. اكسل با نشان دادن نوشته هایی با زمینه زرد رنگ به نام tips به شما راهنمایی های لازم را در خصوص ترتیب و مشخصات آرگومانها می نماید. آرگومان جاری كه در حال وارد نمودن آن هستید پررنگ تر دیده می شود. پارامترهایی كه مقدار دادن به آنها اختیاری است در داخل كروشه نشان داده می شوند.                                                                                            امیدواریم این آموزش اکسل مورد علاقه شما عزیزان قرار گرفته باشد

نحوه درست کردن یک TREEMAP در اکسل

در Excel 2016 نمودار Treemap جدید راه های جالب بصری برای سلسله مراتب داده ها پیشنهاد  می کند . با این ویژگی شما می توانید اطلاعات را از دسته بندی های مختلف در یک نگاه با هم مقایسه کنید همانند درآمد یک کتابخانه بر اساس ژانر کتاب ها و زیرمجموعه های آن یا فروش لپ تاپ بر اساس تولید کننده و مدل .

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

 

۱ – اطلاعات مورد نظر خود را که می خواهید در Treemap استفاده کنید انتخاب کرده و آن ها را به صورت Highlight در بیاورید .
۲ – بر روی زبانه Insert کلیک کنید .

۳ -بر روی دکمه Insert Hierarchy و سپس Treemap کلیک کنید . Excel یک نمودار Treemap بر روی spreadsheet شما ایجاد می کند .

 

۴ – بر روی نمودار دو بار کلیک کنید تا حالت آن را مشخص کنید . شما می توانید موارد مختلف نمودار را مشخص کنید برای مثال عنوان نمودار را مشخص کنید رنگ بندی آن را مشخص کنید . و … .

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

 

رسم نمودار وقایع پروژه در اکسل- Milestone Chart

نمودار وقایع پروژه یا Milestone Chart نوعی نمودار با مقیاس زمانی است که زمانبندی رویدادهای مهم پروژه را نشان میدهد. از این نمودار معمولا برای برنامه ریزی و کنترل پروژه هایی که امکان کنترل بسیار دقیق و لحظه ای را ندارند- مانند خریدهای خارجی -استفاده میگردد. قرار است چنین نموداری بسازیم:

 

برای رسم نمودار وقایع پروژه در اکسل باید مراحل زیر را طی کنید:

گام اول:  دیتاهایتان را آماده کنید.

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

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

 

گام دوم: نمودار اولیه را رسم کنید

حالا از ستون تاریخ و ستون کمکی که تماما اعداد صفر در آن است یک نمودار خطی با مارکرز بسازید. Line with Markers Chart

 

سپس ستون ارتفاع را کپی کرده و روی نمودار Paste میکنیم تا خودبخود به نمودار اضافه گردد.

 

گام سوم: سپس در تب Design روی نمودار گزینه Change Chart Type کلیک نمایید و نمودار مربوط به ستون ارتفاع را به نمودار ستونی تغییر دهید و گزینه Secondary Axis را نیز تیک بزنید.

 

سپس روی نمودار ستونی کلیک راست کرده و گزینه Format Data Series را انتخاب نمایید و تنظیمات را مطابق شکل زیر انجام دهید.

 

گام چهارم: سپس تیک Error Bar را بزنید تا به نمودار اضافه گردد. مانند تصویر زیر :

 

سپس روی Error Bar کلیک راست نمایید و گزینه Format Error Bar را انتخاب نمایید. و تنظیمات را مطابق شکل زیر انجام دهید.

 

گام پنجم: حالا باید به نمودارتان Lable اضافه نمایید. برای اینکار کافیست در تنظیمات Lable گزینه Value From Cells را انتخاب نمایید و محدوده نام پروژه را انتخاب نمایید.

 

 

گام ششم: حالا زمان آن رسیده است که تنظیمات نهایی را انجام دهید. روی نمودار ستونی کلیک نمایید تا تب FORMAT  نمایان شود سپس روی گزینه Shape Fill کلیک کرده و گزینه No Fill را انتخاب نمایید.