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

یکی از نکات بسیار کابردی در اکسل فرمت بندی سفارشی اعداد در اکسل است

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

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

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

فرمت سفارشی اعداد در اکسل

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

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

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

+43%

-54%

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

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

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

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و … در اکسل

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

 

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

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

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

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

 

 

مفاهیم پایه‌ای فرمت سفارشی اعداد در اکسل

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

1. بر روی محدوده مورد نظرتون که می‌خواهید فرمت اعداد اون رو عوض کنید راست کلیک کنید و آیتم Format Cells رو انتخاب کنید تا پنجره Format Cells باز شود.
2. بر روی زبانه یا تب Number بروید و از لیستی که در پایین نشان داده‌شده گزینه Number رو انتخاب کنید (البته ناگفته نماند این روشی که ارائه می‌شود محدود به انتخاب Number نمی‌شه و هر گزینه‌ای که در لیست قرار داره رو می‌شه انتخاب کرد و سپس به مراحل بعدی رفت).

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

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

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

فرمت سفارشی اعداد در اکسل

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

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

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

+43%

-54%

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

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

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

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و …

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

 

#,##0,

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

#,##0,,

برای درک بهتر مطلب به شکل زیر توجه کنید. در این شکل هزینه، درآمد و سود دیسیپلین مکانیکال در یک پروژه به نمایش در آمده است:

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

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

یه مدت پیش دنبال یک مطلب می‌گشتم که اتفاقی به کتاب Excel® Dashboards and Reports, 2nd Edition از نویسنده پرآوازه اکسل John Walkenbach و Mike Alexander برخورد کردم که حیفم اومد اون رو با شما در میان نگذارم.

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

 فرمت سفارشی اعداد در اکسل

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

1. بر روی محدوده مورد نظرتون که می‌خواهید فرمت اعداد اون رو عوض کنید راست کلیک کنید و آیتم Format Cells رو انتخاب کنید تا پنجره Format Cells باز شود.
2. بر روی زبانه یا تب Number بروید و از لیستی که در پایین نشان داده‌شده گزینه Number رو انتخاب کنید (البته ناگفته نماند این روشی که ارائه می‌شود محدود به انتخاب Number نمی‌شه و هر گزینه‌ای که در لیست قرار داره رو می‌شه انتخاب کرد و سپس به مراحل بعدی رفت).

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

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

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

فرمت سفارشی اعداد در اکسل

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

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

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

+43%

-54%

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

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

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

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و … در اکسل

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

 

#,##0,

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

#,##0,,

برای درک بهتر مطلب به شکل زیر توجه کنید. در این شکل هزینه، درآمد و سود دیسیپلین مکانیکال در یک پروژه به نمایش در آمده است:

گرد کردن اعداد به صورت ضریبی از میلیون در اکسل

قبل و بعد از گرد کردن اعداد به صورت ضریبی از میلیارد

 

پرواضح است که خواندن اعداد در حالت دوم برای کاربر، در مواقعی که واقعاً لازم نیست تمام ارقام با دقت زیاد نمایش داده شود بسیار راحت‌تر است.

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

خیلی از مواقع دیدم که دوستان برای این کار (گرد کردن اعداد به ضرایب هزارگان) اعداد را بر ۱,۰۰۰ و یا ۱,۰۰۰,۰۰۰ و … تقسیم می‌کند و این در حالی است که در هرجایی که بخواهند از این اعداد برای محاسباتشان استفاده کنند باید دوباره آن اعداد را به فرمت اولیه بازگردانند و به‌عبارت‌دیگر باید در هرجایی یکپارچگی محاسباتشان در اکسل را حفظ نمایند که می‌تواند بسیار وقت‌گیر و در بعضی مواقع باعث بروز اشتباه در محاسباتشان گردد.

در صورت نیاز می‌توان گرد کردن اعداد به ضرایب هزار، میلیون و … را با اضافه کردن یک حرف یا کلمه به انتهای فرمت مشخص‌تر نمود:

#,##0, “k”

با این کار اعداد بدین‌صورت نمایش داده می‌شود

188k

318k

از این تکنیک برای نمایش اعداد مثبت و منفی نیز می‌توان استفاده نمود:

#,##0, "k";(#,##0, "k")

بعد از اعمال این فرمت اعداد مثبت و منفی بدین‌صورت نمایش داده می‌شود:

(318k)

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

 

#,##0.00,, “m”

با ترفند قبلی و با اضافه کردن دو رقم اعشار، وقتی‌که اعداد به ضریب میلیون گرد می‌شوند، می توان با نمایش دو رقم اعشار دقت اعداد نمایش داده‌شده را بالا برد.

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

 

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

قابليت پر كردن داده هاي متوالي بصورت خودكاريكي از مفيدترين ويژگيهاي برنامه Excel ، مي باشد . كه به اين عمل Auto fill گويند . داده هاي متوالي مي توانند مجموعه اي از داده ها بصورت 1و2و3و4 ..... يا 10و20و30.... يا از حروف A,B,C,…. تشكيل شده باشند . همچنين مي تواند تاريخ يا روزهاي هفته يا ماههاي سال باشند. حتي مي توان مجموعه اي از داده اي خاص را تعريف كرد .

براي اين كار مراحل زير را انجام مي دهيم .

1- چند نمونه اوليه از مقادير متوالي مزبور را در سلولهای متوالی مي نويسيم .

2- اين مقادير را انتخاب مي كنيم .

3- مكان نماي ماوس را به گوشه پايين سمت راست قسمت انتخابي مي آوريم تا به شكل (+) در آيد. به اشاره گر ماوس در اين حالت Fill Handle مي گوييم .

4- حال به طرف پايين يا راست Drag مي كنيم .

5- به اين ترتيب با رها كردن كليد ماوس خانه هاي مورد نظر با مجموعه داده هاي متوالي پر مي شوند .

ايجاد ليست جديد خودكار در excel :

اگر براي حروف الفبا Auto fill را اجرا كنيم ، پر كردن خودكار انجام نمي شود . چون حروف الفبا جزء ليستهاي آماده نيست پس بايد ايجاد شود . براي ايجاد ليست جديد مراحل زير را انجام مي دهيم :

1- انتخاب منوي Tools

2- انتخاب گزينه Options

3- انتخاب Custom list : tab

4- انتخاب New list در كادر Custom list

5- تايپ اطلاعات در قسمت List entries (بعد از وارد كردن هر اطلاعات Enter مي كنيم . )

6- انتخاب دكمه add

7- Ok

ايجاد يك ليست خودكار بر اساس ليست موجود در صفحه در اکسل  :

اگر ليستي بر روي صفحه داشته باشيم و بخواهيم بصورت ليست دائمي در آوريم بايد مراحل زير را انجام دهيم :

1- انتخاب منوي Tools

2- انتخاب گزينه Option

3- انتخاب Custom list : tab

4- روي دكمه Collapse dialog () كه در انتهاي كادر فهرست Cells Import list form قرار دارد Click مي كنيم . تا پنجره مينيمايز شود.

5- انتخاب ليست در صفحه

6- روي دكمه Collapse Dialog () كه در انتهاي كادر فهرست Cells Import list form قرار دارد Click مي كنيم . تا Resizeشود.

7- انتخاب دكمه Import

8- OK

نكته :

اصطلاحا به icon اي که در انتهای برخی از text box ها قرار دارد callaps dialog گفته می شود. مي توانيم بجاي انجام مراحل 4و5و6 در كادر Import ، آدرس سلولهايي كه ليست در آنها قرار دارد را تايپ كنيم .

مطالبی پیرامون ایجاد جداول محوری در اکسل

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

جداول محوری در اکسل مبحث بسیار مفصلی است با آموزش امروز همرا ه باشید تا در ادامه  با کلیات جداول محوری در اکسل و کاربرد آن در حسابداری آشنا شویم

تحلیل اطلاعات برای تهیه گزارش

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

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

 

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

 

همین کار را برای محاسبه ماه با فرمول (left(A2;2  در ستون G انجام دهید. برای ساخت جدول محوری در اکسل کلیه ستونهای اطلاعاتی شما باید نام داشته باشند پس در F1 و G1 نام هر ستون را وارد کنید.

 

نحوه ایجاد جداول محوری در اکسل

ابتدا کل ناحیه اطلاعات را انتخاب کنید برای اینکا می توانید بر روی یکی سلولهای داری اطلاعات قرار بگیرید و کلید Ctrl+A را بزنید. سپس از منو Insert در اکسل اولین گزینه Pivot Table را انتخاب کنید.

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

 

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

نکاتی پیرامون رسم نمودار وقایع پروژه در اکسل

 د ر اکسل نمودار وقایع پروژه یا 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 را انتخاب نمایید.

 

مطالبی پیرامون اضافه کردن مراحل Undo در Excel

 

برنامه ی undo   به کاربر اجازه می دهد که کارهای قبلی را برگردانده یا در صورت انجام هر عمل اشتباه، به سادگی آن را اصلاح کند. متاسفانه، مراحل انجام Undo به صورت پیش فرض، 16 مورد است. با استفاده از روش (هک کردن) توضیح داده شده در این قسمت، می توانید با تغیر registry، تا 100 مرحله Undo را انجام دهید.

در زمان استفاده از ویژگی Undo در برنامه ی  Excel و زمانی که نیاز به برگرداندن بیش از 16 عمل انجام شده داریم، برگرداندن آخر به عنوان مرحله ی 17 اُم در نظر گرفته شده و به همین ترتیب ادامه پیدا می کند. همچنین بعد از ذخیره (Save) کردن، امکان برگرداندن وجود ندارد.

شاید با این مشکل مواجه شده باشید که 16 مرحله برای برگرداندن تغییرات کافی نیست. شما می توانید با ویرایش registry در  Windows، این مشکل را برطرف کنید. برای این کار، ابتدا به طور کامل از برنامه ی اکسل خارج شوید. از منوی Start، به قسمت Run رفته، Regedit.exe را تایپ و روی OK کلیک کنید. وقتی Regedit شروع شد، فولدر مربوط به HKEY_CURRENT_USER را باز کنید. در این قسمت، فولدر Software، سپس فولدر Microsoft، فولدر Office و فولدر 10.0 را باز کنید (این عدد ممکن است متفاوت باشد اما برای Excel 2002، همان 10.0 است). فولدر Excel را باز کرده و در آخر روی فولدر Options کلیک کنید.
با دنبال کردن مسیر Edit → New → DWORD، کلمه ی UndoHistoryرا وارد کرده و Enter را فشار دهید. روی UndoHistory که ایجاد کرده اید، دو بار کلیک کرده و عددی بین 16 و 100 را وارد کنید.
به احتمال زیاد و به نظرِ تعداد زیادی از کاربران، 100 مرحله برگرداندن کافیست اگرچه با ذخیره کردن، امکان برگرداندن وجود نخواهد داشت.

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

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

سلول فعال

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

آدرس دهی سلول ها

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

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

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

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

 

 

 انتخاب دسته ای از سلول های غیرمجاور

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

 

 

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

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

 

انتخاب دسته ای از ستون ها یا سطرهای مجاور

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

 

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

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

 

اضافه کردن سطر و ستون در EXCEL

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

 

حذف کردن سطر و ستون در اکسل

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

 

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

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

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

 

 

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

 

 

کپی کردن یک سلول

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

 

جایگزینی (Paste) یک سلول

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

 

انتقال سلول ها

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

 

استفاده از گزینه های Copy و Cut و Paste در زبانه Home

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

 

پاک کردن محتوا و حذف سلول ها

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

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

 

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

 

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

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

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

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

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

---

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

در نرم افزار اکسل در مورد جلوگیری از نمایش فرمول ها در Microsoft Excel 2013 ارائه شده است ،

 جلوگیری از نمایش فرمول ها در Microsoft Excel 2013

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

بدین منظور:
  ابتدا تمام سلول‌هایی که قصد پنهان سازی فرمول‌های موجود در آن را دارید انتخاب کنید.
در سربرگ Home بر روی Format کلیک کنید (ترفندستان) و Format Cells را انتخاب کنید.
به سربرگ Protection رفته و تیک گزینه‌ی Hidden را زده و بر روی OK کلیک کنید.
حال دوباره بر روی Format کلیک کرده و این‎‌بار Protect Sheet را انتخاب کنید.
یک رمز عبور در کادر Password to unprotect sheet وارد کرده و OK را بزنید.
یک بار دیگر نیز رمز عبور خود را وارد کنید.
اکنون مشاهده می‌کنید که دیگر اثری از فرمول‎‌ها نخواهد بود.
برای بازگشتن به حالت اولیه بر روی t کلیک کرده و Unprotect Sheet را بزنید.

 

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

در نرم افزار کسل گاهی ممکن است تجربه کار با فرمولهایی که دارای یک عملگر هستند را داشته باشید، مانند 7+9 . فرمولهای پیچیده تر ممکن است شامل چندین عملگر ریاضی باشند، مانند 5+2*8. هر وقت که تعداد عملگرهای یک فرمول بیش از یکی باشد، ترتیب عملگرها برای اکسل تعیین می کند که کدام عملیات را ابتدا انجام دهد. برای اینکه از فرمولهایتان نتیجه دلخواه را بگیرید لازم است تا با ترتیب عملگرها در اکسل آشنا شده باشید.

 

ترتیب انجام عملیات ریاضی در اکسل

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

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

➋ محاسبات مربوط به توان برای مثال 3^2

➌ ضرب و تقسیم، هر کدام که جلوتر در فرمول آمده باشند.

➍ جمع و تفریق، هر کدام که جلوتر در فرمول آمده باشند.

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

• P = parentheses : پرانتزها
• E = Exponential : توان
• MD = Multiplication and division : ضرب و تقسیم
• AS = Addition and subtraction : جمع و تفریق

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

PEMDAS = Please Excuse My Dear Aunt Sally

 

ایجاد فرمولهای پیچیده در excel

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

=(D3+D4+D5)*0.075

این فرمول جمع اقلام فاکتور را محاسبه می کند، و سپس مجموع آنها را بر نرخ مالیات 7.5% (که بصورت 0.075 نوشته می شود) ضرب می کند، تا در نهایت بتواند نتیجه فرمول را محاسبه نماید.



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

(45.80+68.70+159.60) = 274.10

سپس حاصل جمع بدست آمده را بر نرخ مالیات ضرب می کند.

274.10*0.075

نتایج فرمول نشان می دهد که هزینه مالیات $20.56 می باشد.

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

 

ایجاد یک فرمول پیچیده با استفاده از ترتیب عملگرها در اکسل

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

➊ سلول مربوط به فرمول را انتخاب کنید، در این مثال ما سلول D5 را انتخاب میکنیم.



➋ فرمول خود را وارد کنید. در این مثال ما فرمول زیر را وارد می کنیم :

=B3*C3+B4*C4

این فرمول از ترتیب عملگرها استفاده می کند و در ابتدا عملیات ضرب را محاسبه می کند:

2.79*35 = 97.65

2.29*20 = 45.80

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

97.65+45.80

➌ برای اطمینان از درستی کار مجددا فرمول خود را بررسی کنید، سپس اینتر را بفشارید. فرمول محاسبه شده و نتایج را نمایش می دهد. در مثال ما نتیجه $143.45 می باشد.

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

 

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

 

 

➊ ابتدا فایل اکسل زیر را دانلود کنید.

 

➋ برگه Challenge را انتخاب کنید.

➌ در سلول D7 فرمولی بسازید که مالیات فاکتور را با نرخ 7.5% محاسبه کند.

➍ در سلول D8 فرمولی بسازید که مجموع اقلام فاکتور را محاسبه کند. بعبارت دیگر این فرمول باید مجموع دامنه سلولی D3:D7 را محاسبه کند.

➎ در سلول D9 فرمولی بسازید که مجموع اقلام را با کسر 10% تخفیف محاسبه کند.

➏ وقتی کار شما تمام شد برگه اکسل شما باید چیزی شبیه تصویر زیر باشد.

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

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

 

” ستون های اکسل به صورت پیش فرض چپ به راست هستند. “

 

نحوه راست چین کردن و راست به چپ کردن ستون های اکسل

همانطور که می دانید به صورت پیش فرض ، شیت های اکسل به صورت چپ به راست هستند. یعنی همیشه ستون A در سمت چپ صفحه وجود داشته و به ترتیب B و C و سایر ستون ها در سمت راست آن پدیدار می شوند. اما در فایل های فارسی نیاز داریم که این ترتیب بر عکس شود یعنی شیت اکسل راست به چپ شده و ستون A در سمت راست قرار گیرد.
برای انجام این کار باید وارد سربرگ Page Layout شوید و گزینه Sheet Right To Left را  انتخاب کنید:

 

نحوه راست به چپ کردن نمودار ها در اکسل

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

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

نکاتی در مورد طراحی خطوط یک جدول و چگونگی تغییر خطوط جدول در اکسل

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

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

 

روش اول

۱- جدول مورد نظر رو انتخاب کنید
۲- روی منوی Home کلیک کنید
۳- در قسمت Font ابزار Border رو پیدا کنید
۴- روی فلش کنار ابزار کلیک کنین
۵- روی فلش کنار گزینه‌ی Line Style کلیک کنید
۶- خط نقطه چین مورد نظر رو انتخاب کنید
۷- دوباره روی فلش کنار ابزار کلیک کنید
۸- گزینه‌ای که همه‌ی خطوط جدول رو مشخص می‌کنه (All Borders) رو انتخاب کنید
۹- دوباره روی فلش کنار ابزار کلیک کنید
۱۰- روی فلش کنار گزینه‌ی Line Style کلیک کنید
۱۱- خط دوتایی رو انتخاب کنید
۱۲- دوباره روی فلش کنار ابزار کلیک کنید
۱۳- گزینه‌ای که خط دور جدول رو بدن خطوط داخلی رامشخص مي‌کند (Outside Borders) را انتخاب کنید

روش دوم

۱- جدول مورد نظر رو انتخاب کنید
۲- روی منوی Home کلیک کنید
۳- در قسمت Font ابزار Border را پیدا کنید
۴- روی فلش کنار ابزار کلیک کنید
۵- گزینه‌ی More Borders را انتخاب کنید
۶- در قسمت Line و Style خطوط دوتایی رااتخاب کنید
۷- از قسمت Presets گزینه‌ی Outline را انتخاب کنید
۸- حالا از قسمت Line و Style خطوط نقطه چین رو انتخاب کنید
۹- از قسمت Presets گزینه‌ی Inside را اتنخاب کنید
۱۰- OK کنین و تغییرات را ببینید

چگونگی تغییر  خطوط جدول در اکسل

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

 

جدول را انتخاب كنید  

 

 

نقطه درج را در جدول قرار دهید تا جدول برای ویرایش انتخاب شود.

 

 Table Properties را باز كنید

  

 

روی جدول كلیك راست كنید و گزینه ‏Table Properties را انتخاب كنید تا كادر محاوره‌ای Table Properties باز شود.

   

 اندازه خطوط جدول را مشخص كنید  

 

 

در قسمت Borders اندازه خطوط جدول را بر حسب پیكسل در كادر متنیSize وارد كنید. برای خطوط رنگی، باید حداقل یك پیكسل، در نظر بگیرید وگرنه نمی‌توانید رنگ خطوط را مشاهده كنید.

 

 انتخاب رنگ در excel

 

از منوی Color، یك رنگ برای خطوط جدول انتخاب كنید. برای ایجاد خطوط دو رنگی، از منوهای Light Border و Dark Border استفاده كنید. بعد از اتمام كار، روی OK كلیك كنید بخاطر داشته باشید كه اگر از یك زمینه برای صفحه استفاده كنید، این رنگ‌ها بصورت خودكار تنظیم می‌شوند.

  

 خطوط سلول را تغییر دهید  

 

 

همچنین می‌‌توانید خطوط تك تك سلول‌ها را تغییر دهید. برای این كار، داخل سلول مورد نظر كلیك كنید.

   

 Cell Properties را انتخاب كنید  

 

 

روی سلول انتخاب شده كلیك راست كنید و Cell Properties را انتخاب كنید. كادر محاوره‌ای Cell Properties كه شبیه كادر محاوره‌ای Table Properties در مرحله 3 است باز می‌شود.

   

 تغییر دادن خطوط جدول در اکسل

 

در قسمت Borders، منوهای رنگ، درست مانند منوهای رنگ در مرحله 4 كار می‌كنند. برای اینكه خطوط، فقط یك رنگ داشته باشند یكی از رنگ‌های منوی Color را انتخاب كنید. و برای اینكه خطوط، دو رنگی شوند، رنگ‌های منوهای Light Border و Dark Border را انتخاب كنید. بعد روی OK كلیك كنید.

 

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

در بسیاری از کشور های جهان  از نقطه به عنوان جداکننده اعداد اعشاری استفاده می کنند ( 19.75)  . این در حالیست که در بعضی از کشور های اروپایی و آسیایی از ویرگول به عنوان جداکننده اعداد اعشاری استفاده می شود ( 19,75 ) . بعضی از این علائم نیز به عنوان جداکننده هزارتایی اعداد استفاده می شود مانند عدد ( 1.000 ).
فرض کنید  می خواهید لیستی از قیمت های مختلف برای کشور های مختلف تهیه کنید . برای نشان دادن قیمت درست برای هر کشور  اگر قادر باشیم که علائمی را که برای برای جداکردن اعداد اعشاری استفاده می شود را از علائمی که برای جدا کردن هزار تایی اعداد استفاده می شود را متمایز و مشخص کنیم می تواند برای ما مفید واقع بشود . ما در این جا به شما نشان می دهیم که چگونه می توان علائم جداکننده اعداد اعشاری و علائم جداکننده هزار تایی اعداد را در اکسل تغییر داد .

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

برای تغییر نوع جدا کننده اعداد اعشاری و جداکننده هزارتایی اعداد در اکسل بر روی زبانه File کلیک کنید .

 

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

 
کادر محاوره ای Excel Options به نمایش در می آید. در سمت چپ این کادر بر روی Advanced کلیک کنید .

 
در سمت راست کادر در قسمت Editing options بر روی مربع کوچک Use system separators کلیک کرده تا به صورت خالی و بدون تیک در بیاید .

 
در پایین قسمت Use system separators دو گزینه Decimal separator ( جداکننده اعشاری ) و Thousands separator ( جداکننده هزار تایی ) به حالت ویرایش فعال می شوند . علائم مورد نظر خود را در کادر روبروی هر کدام از آن ها مشخص کنید . سپس بر روی ok کلیک کنید . برای مثال ما از ویرگول برای جداکننده اعشار و از نقطه برای جداکننده هزارتایی اعداد استفاده می کنیم .

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

 
اگر شما می خواهید جداکننده های اعداد اعشاری و جداکننده های هزارتایی اعداد را برای هر برنامه ای در ویندوز ( و نه فقط اکسل )  تغییر دهید  می توانید به کادر ( Customize Format) ) مربوط به آن برنامه رجوع کنید .

نکاتی در مورد تغيير شکل کادر توضيحات در اکسل

براي اين کار پس از باز کردن اکسل روي File در گوشه بالا، سمت چپ صفحه کليک و آخرين گزينه يعني Options را انتخاب کنيد تا پنجره Excel Option ظاهر شود. در قسمت سمت چپ اين پنجره، عنوان Customize Ribbon را انتخاب کنيد.

هنگام قراردادن توضيحات در سلول هاي اکسل (insert comment) هميشه يک مستطيل ساده ظاهر مي شود، اما شما با دنبال کردن ترفندي ساده، مي توانيد ظاهر اين کادر را تغيير دهيد.

براي اين کار پس از باز کردن اکسل روي File در گوشه بالا، سمت چپ صفحه کليک و آخرين گزينه يعني Options را انتخاب کنيد تا پنجره Excel Option ظاهر شود. در قسمت سمت چپ اين پنجره، عنوان Customize Ribbon را انتخاب کنيد.

در کادر سمت چپ تعدادي از دستورات و ابزارهاي اکسل فهرست شده است و در بالاي اين کادر عبارت Popular Commands ديده مي شود. روي مثلث کوچک سمت راست اين کادر کليک و عبارت All Commands را انتخاب کنيد. نوار لغزنده اين کادر را درگ کنيد تا به دستور Change Shape برسيد.

اکنون بايد اين دستور را به کادر سمت راست با استفاده از دکمه Add اضافه کنيد. قبل از اين که بتوانيد هر دستور يا ابزاري را به کادر سمت راست اضافه کنيد، بايد در يکي از زيرگروه هاي منوهاي فعلي (مثل Home-Insert-Design) يک گروه با تب جديد اضافه کنيد.

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

سپس روي دکمه New Group در پايين همين صفحه کليک کنيد تا يک گروه جديد در منوي Home ايجاد شود؛ در ادامه روي دکمه Add که بين 2 کادر قرار گرفته است، کليک کنيد تا گروه ابزار Change Shape به انتهاي منوي Home اضافه شود و سپس دکمه Ok را انتخاب کنيد تا اکسل بسته شود.

اکنون مشاهده مي کنيد که عبارت Change Shape در قسمتي به نام New Group در انتهاي منوي Home قرار گرفته است.

 

تغيير شکل و رنگ زمينه در اکسل 

حال براي تغيير شکل و رنگ زمينه توضيحات روي يک سلول دلخواه راست کليک و از کادر ظاهر شده عبارت Insert Comment را انتخاب کنيد. عبارتي به عنوان توضيح را وارد و سپس روي سلول ديگري کليک کنيد. مثلث کوچک قرمز رنگ، نشان مي دهد که سلول داراي توضيحات است. دوباره روي اين سلول راست کليک و اين بار عبارت Edit Comment را انتخاب کنيد.

روي کادر مستطيل توضيحات کليک کنيد و از گروه جديد که در منوي Home به نام Change Shape ايجاد کرديد، کليک کنيد و شکل دلخواه را برگزينيد.

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

ایجاد چک باکس در اکسل بسیار آسان است گزینه چک باکس در تب Developer و قسمت Tools option موجود است. چک باکس و موارد دیگر موجود در این قسمت مانند لیست کشویی، ابزار های مفیدی در هنگام طراحی یک فرم در اکسل می باشد.

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

 

 ایجاد یک چک باکس در اکسل :

 ابزار چک باکس در تب Developer موجود است اما این تب به صورت پیش فرض در اکسل پنهان می باشد جهت نمایش این تب (زبانه) میبایست مراحل زیر را دنبال کنید:

• پس از باز کردن یک صفحه اکسل به ترتیب این مراحل را دنبال کنید. File > Options > Customize Ribbon tab.

 

 

• سپس به صورت نمایش داده شده در شکل فوق تیک مربوط به تب Developer را بگذارید.

• پس از ظاهر شدن این تب آن را فعال کرده و با انتخاب گزینه Insert  از لیست باز شده چک باکس را انتخاب نمایید. توجه داشته باشید که از گزینه های زیر مجموعه Form Controls استفاده نمایید.

 

 

• اکنون می توانید چک باکس خود را در هرکجای صفحه که میخواهید ترسیم نمایید

• سپس روی چک باکس ایجاد شده کلیک راست کرده و گزینه Format Control را انتخاب نمایید.

 

• در پنجره Format Control می توانید حالت پیش فرض را برای فعال یا غیر فعال بودن چک باکس در اکسل  مشخص نمایید.همچنین در قسمت Cell link میتوانید سلولی که میخواهید نتیجه فرمان تیک گذاشتن در آن نمایش داده شود را مشخص نمایید. نتیجه فعال بودن تیک عبارت True و نتیجه غیر فعال بودن آن عبارت False خواهد بود.

 

ابزار استفاده از چک باکس در اکسل

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

 

 

یک لیست با عناوین اقلام انبار (Grocery Items) با یک چک باکس جلوی هر آیتم آن ایجاد شده است . این چک باکس تأیید کننده در دسترس بودن (Available) یا خارج از انبار بودن (Out of Stock) اقلام ذکر شده می باشد. به محض این که تیک مربوطه گذاشته شود عبارت در دسترس (Available) جلوی آیتم ظاهر میشود و اگر بدون تیک باشد نشان دهنده خارج از انبار بودن (Out of Stock) اقلام خواهد بود و این عبارت نشان داده خواهد شد.

در ادامه طریقه انجام این کار را توضیح می دهیم.

برای انجام این کار ابتدا باید برای هر یک از سطرها یک چک باکس به صورتی که توضیح داده شد ایجاد کنیم. و در Cell link آن را به سلول وضعیت آن (Abailability) که برای هر سطر خانه مجاور آن می باشد، مرتبط کنیم.

به عنوان مثال برای Cell link مربوط به چک باکس سطر دوم (B2) ، میبایست سلول متناظر در ستون E انتخاب گردد. و برای بقیه سطرها نیز همین روال را میتوانید ادامه دهید. این بدان معنی است که وقتی تیک چک باکس گذاشته می شود باید عبارت True در همان سطر در ستون E نمایش داده شود و در صورتی که تیک گذاشته نشود عبارت False نشان داده شود.(به صورتی که در تصویر مشاهده می کنید).

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

 =IF(E2=TRUE,"Available","Out of Stock")

این فرمول بدان معنی است که در صورتی که در ستون E که خود از طریق گذاشتن و برداشتن تیک ستون B تغییر میکند، اگر عبارت True بود در نتیجه در ستون C عبارت “Available” و در غیر این صورت عبارت “Out of Stock” نمایش داده شود.

برای مثال همانطور که در شکل زیر می بینید با گذاشتن تیک مربوط به سطر دوم در نتیجه عبارت True در ستون E ظاهر شده و همچنین با توجه به فرمول IF که قرار داده ایم در ستون C  عبارت Available ظاهر می شود.

سپس با استفاده از فرمول COUNTIF به صورتی که در زیر نمایش داده میشود میتوانید تعداد اقلام در دسترس (Items Available) و تعداد اقلامی که در دسترس نیست (Unavailable) را مشخص کنید.همچنین تعداد کل اقلام (Total Items) نیز با استفاده از تابع ساده Count مشخص خواهند شد.

فرمول مربوط به سلول D14 :

=COUNT(C2:C11)

فرمول مربوط به سلول D15 :

=COUNTIF(C2:C11,"Available")

فرمول مربوط به سلول D16 :

=COUNTIF(C2:C11,"Out of Stock")

نکات مهم:

• برای ایجاد چک باکس در چندین سطر این کار را در یک سلول انجام دهید و سپس با استفاده از ابزار Fill تا محدوده ای که میخواهید آن را بسط دهید. فقط دقت کنید که پس از این کار میبایست Cell link هر چک باکس را جداگانه آدرس دهی کنید.
• برای حذف همزمان چندین چک باکس میتوانید مراحل زیر را دنبال کنید.

‘Home’ تب> ‘Find and Select’ > ‘Go To Special’> ‘Objects’> Delete

نکته : برای حذف چند مورد از چک باکس ها می بایست به صورت زیر عمل نمایید:

‘Home’ تب> ‘Find and Select’ > ‘Selection Pane’. > انتخاب موارد> delete

 

نکاتی در رمورد تابع VLOOKUP و نحوه رفع محدودیت این تابع در اکسل

 

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

 

ساختار تابع:

= VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

اجزاء:

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

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

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

range_lookup
این قسمت تابع اختیاری می باشد. در صورتی که این قسمت را وارد نکنید مقدار پیش فرض (TRUE) در نظر گرفته خواهد شد.

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

اگر مقدار FALSE یا ۰ را وارد کنید دقیقا همان عبارت مورد جستجو را پیدا می کند و در صورتی که نتیجه ای در برنداشت خطای #N/A را نشان می دهد.

 

در آموزش اکسل  امروز مبحث جدیدی را مورد بررسی قرار میدهیم

 

رفع محدودیت تابع  Vlookupدر اکسل :

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

 

یکی از راه حل هایی که شما در مواجهه با این مشکل میتوانید انجام دهید جابجایی ستونی که lookup_value درون آن وجود دارد به ابتدای محدوده جدول انتخابی است اما این راه حل نیز چندان مناسب نیست و در مواردی که ما قصد این جابجایی را نداریم ما را با مشکل روبرو می کند.

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

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

 

روش کار:

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

=choose({1,2},lookup column,result column)

در فرمول فوق بجای lookup column ستونی که کد مورد جستجو درون آن قرار دارد را انتخاب می کنید و بجای result column ستونی که نتیجه درون آن قرار دارد را انتخاب می کنید.

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

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

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

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

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

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

 

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

 

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

 

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

 

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

 

 

نکاتی درمورد جستجو و جایگزینی در اکسل

یکی از قابلیتهای قوی نرم افزار اکسل قابلیت جستجو و جایگزین نمودن متن است. با استفاده از این قابلیت شما به آسانی می توانید متن یا عددی را در یک صفحه یا بین چندین صفحه از فایل اکسل را جستجو می نماید یا می توانیم یک متن یا عدد و... را جستجو نماییم و بعد آن را با متن یا ... دلخواه جایگزین نماییم.
با استفاده از ترکیب کلید Ctrl+F نیز میتوانید وارد Find & Replace گردید.
در قسمت Find what موضوعی را که می خواهیم جستجو کنیم، تایپ می کنیم.
در صورتیکه برروی Format کلیک کنید پنجره Format Cell باز خواهد شد. و در این پنجره هرنوع فرمتی که شما قصد دارید جستجو کنید را مشخص می کنید. حتی در این قسمت شما قادر خواهید رنگ سلول را نیز جستجو کنید.
در قسمت Within نوع جستجو را تعیین میکنیم اگر Sheet را انتخاب کنیم جستجو فقط در همان کاربرگ صورت میگیرد ولی اگر Workbook را انتخاب کنیم جستجو در کلیه کاربرگهای اکسل صورت میگیرد.
در قسمت Search مشخص میکنید که عملیات جستجو در ردیفها صورت گیرد یا در ستونهای اکسل. به عبارت دیگر وقتی By Rows را فعال کنید جستجو به ترتیب ردیفها صورت میگیرد و هنگامی که جستجو در ردیف اول صورت گرفت، اکسل به دنبال ردیف دوم می رود ولی هنگامی که By Columns را فعال کنید عملیات جستجو ستون به ستون صورت می گیرد.
در قسمت Look in مشخص می کنید که عملیات جستجو در فرمولها، مقادیر عددی و یا در کامنتهای اکسل صورت گیرد.
قسمت Match case به کوچک و بزرگ بودن حروف حساس است.
اگر قسمت Match entire cell contents را فعال نکنیم جستجو در موارد مشابه نیز صورت میگیرد ولی اگر فعال کنیم دقیقاً همان چیزی را جستجو میکنیم پیدا خواهد کرد.
به عنوان مثال اگر کلمه "حساب" را جستجو کنید اگر گزینه فوق را فعال نکنید اکسل کلماتی از قبیل "حسابداری" ، حسابرسی" و "حساب" را پیدا خواهد کرد اما در صورت فعال نمودن گزینه گفته شده فقط کلمه "حساب" پیدا خواهد شد.
و اما چند نکته هنگام جستجو:
جهت جستجو میتوانید از از 2 علامت (*) و (؟) استفاده کنید. ستاره به معنی هر تعداد کاراکتر و علامت سؤال به معنی یک کاراکتر میباشد.
مثال:
حساب* در این حالت اکسل کلماتی که با حساب شروع میشود و ادامه آن هرتعداد کاراکتر باشد را پیدا خواهد کرد.
حساب؟؟ در این حالت اکسل کلماتی که با حساب شروع میشود و ادامه آن فقط دو کاراکتر دیگر خواهد بود را پیدا خواهد کرد این کلمه میتواند کلمه حسابرس باشد.
کلیه موارد Replace شبیه به Find عمل می کند.

مطالبی ارزنده پیرامون کپی کردن جدول SQL در Excel

پایگاه داده یک سیستم کامل دیتابیس است که توسط شرکت مایکروسافت ارائه می گردد.  SQL Server یک پایگاه داده است که شا می توانید به عنوان یک مخزن داده از آن برای وب سایت ، برنامه و … استفاده کنید.

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

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

کپی کردن جدول SQL در Excel

1- ابتدا SQL Server Management را باز کنید و به سرور دیتابیس خود متصل شوید.

2- سپس بر روی جدول مورد نظر کلیک راست کرده و گزینه Select Top 1000 Rows را انتخاب نمایید.

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

 

4- بر روی جدول کلیک راست کرده و گزینه Copy را انتخاب نمایید.

برای کپی کردن از کلیدهای ترکیبی Ctrl + C نیز می توان استفاده کرد.

 

5- سپس یک سند اکسل ایجاد نمایید.

6- بر روی آن کلیک راست کرده و گزینه Paste را همانند تصویر انتخاب نمایید.

برای Paste کردن در فایل اکسل ، می توانید از کلیدهای ترکیبی Ctrl + V استفاده نمایید.

 

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

 

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

امیدواریم آموزش برای شما مفید واقع شده باشد.

چگونگی ترکیب متن موجود در چند سلول در داخل یک سلول در اکسل

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

 

 

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

=CONCATENATE(

 

 

، بعد ازخواندن تابع وقت آن رسیده که ورودی های آن را مشخص کنیم. قصد داریم ابتدا نام (ستون B) و سپس نام خانوادگی (ستون A) درج شوند. پس دو ورودی تابع برای این سلول به ترتیب B2 و A2 خواهند بود.

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

=CONCATENATE(B2,A2)

 همچنین می توانید بدون نیاز به تایپ و تنها با کلیک روی سلول های موردنظر خود آنها را انتخاب کنید. بنابراین در این مثال ما ابتدا پس از نوشتن نام تابع و علامت پرانتز باز، به ترتیب بر روی ستون های B2 و A2 کلیک کرده ایم. بعد از اضافه کردن سلول های خود، دکمه ی Enter را فشار دهید.

 

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

 

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

=CONCATENATE(B2," ",A2)

 

 

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

 

 

 

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

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

 

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

 

توجه داشته باشید که می توانید همچنین از عملگر & برای ترکیب کلمات سلول ها استفاده کنید. یعنی از دستور =B2&" "&A2 به جای =CONCATENATE(B2,” “,A2) استفاده کنید.

چگونگی ردیابی تغییرات و ثبت توضیحات در اکسل

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

ویژگی ردیابی تغییرات (Track Changes) در اکسل

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

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

 

اگر برگه اکسل شما دارای جدول باشد نمی توانید از ویژگی ردیابی تغییرات استفاده کنید. برای حذف جدول آن را انتخاب کنید، و سپس از تب Design دستور Convert to Range را انتخاب کنید.

 

روش فعال کردن ویژگی ردیابی تغییرات (Track Changes) در اکسل

➊ در تب Review دستور Track Changes را کلیک کنید، سپس از کادر باز شده گزینه Highlight Changes را انتخاب کنید.



➋ پنجره Highlight Changes نمایان می شود. گزینه Track changes while editing را تیک بزنید. گزینه Highlight changes on screen را هم بررسی کنید، اگر تیک نداشت آن را هم حتما تیک بزنید. سپس بر روی OK کلیک کنید.



➌ اگر اکسل پیغام زیر را دارد OK کنید.



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

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

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

 

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

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

➊ برگه اکسل خود را ذخیره کنید.

➋ در تب Review ، بر روی دستور Track Changes کلیک کنید و از کادر باز شده گزینه Highlight Changes را انتخاب کنید.



➌ صفحه Highlight Changes نمایان می شود. گزینه List changes on a new sheet را تیک بزنید و سپس ok کنید.



➍ تغییرات اعمال شده در یک برگه که نامش History می باشد فهرست می گردند.

برای حذف تاریخچه از فایل اکسل شما می توانید فایل اکسل را مجددا ذخیره کنید و یا گزینه List changes on a new sheet را که در بالا تیک زدید، اینبار بدون تیک کنید.

 

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

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

روش مرور ردیابی تغییرات در اکسل

➊ در تب Review دستور Track Changes را کلیک کنید، سپس از کادر باز شده گزینه Accept/Reject Changes (پذیرش یا رد تغییرات) را انتخاب کنید.



➋ اگر به شما هشدار ذخیره سازی داده شد، فایل اکسل خود را ذخیره کنید.

➌ یک صفحه ظاهر می شود. اول مطمئن شوید که گزینه When تیک خورده باشد و در کادر مقابل آن گزینه Not yet reviewed انتخاب شده باشد. حالا OK کنید.



➍ یک صفحه ظاهر می شود. به ازاء هر تغییری که در فایل اکسل داده شده است شما می توانید پذیرش (Accept) و یا رد کردن (Reject) را انتخاب کنید. اکسل بصورت اتوماتیک تک تک تغییرات را به شما نشان می دهد تا شما همه را بررسی کنید.



➎ حتی بعد از مرور تغییرات و تایید یا رد آنها، همچنان ردیابی تغییرات در برگه شما نمایان می باشد. برای اینکه آنها را نبینید می توانید ویژگی ردیابی تغییرات را غیر فعال کنید. در تب Review دستور Track Changes را کلیک کنید و از کادر باز شده گزینه Highlight Changes را انتخاب کنید.



➏ یک پنجره باز می شود. گزینه Track changes while editing را بدون تیک کنید و ok کنید.



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

برای اینکه تمامی تغییرات انجام شده را بصورت یکجا تایید و یا رد کنید، گزینه های Accept All برای پذیرش کلی و همینطور Reject All برای رد کردن کلی می توانند به شما کمک کنند.

 

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

 

نقش اکسل در رد یابی تغییرات وثبت توضیحات

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

افزودن توضیحات در اکسل

➊ سلولی را که قصد درج توضیحات برای آن را دارید، انتخاب کنید. در اینجا ما سلول D17 را انتخاب می کنیم.



➋ در تب Review دستور New Comment را کلیک کنید.



➌ یک کادر برای وارد کردن توضیحات نمایان می شود. توضیحاتتان را تایپ کنید، سپس در هر جایی به غیر از داخل کادر توضیحات کلیک کنید تا کادر توضیحات بسته شود.



➍ توضیحاتی که تایپ کرده اید به سلول اضافه می شود. وقتی یک سلول دارای توضیحات باشد در سمت راست و بالای آن یک مثلث قرمز کوچک نمایان می شود.



➎ اگر سلول را دوباره انتخاب کنید توضیحات را خواهید دید.

ویرایش توضیحات در اکسل

➊ ابتدا سلول دارای توضیحات را انتخاب کنید.

➋ در تب Review بر روی دستور Edit Comment کلیک کنید.



➌ کادر توضیحات نمایان می شود. توضیحات مورد نظرتان را وارد کنید و سپس در هر جایی از صفحه به غیر از کادر توضیحات کلیک کنید.

نمایش یا مخفی کردن توضیحات در اکسل

➊ ابتدا تب Review و سپس دستور Show All Comments را انتخاب کنید. اجرای دستور Show All Comments باعث می شود تا همه توضیحات نمایان شوند.



➋ برای مخفی کردن توضیحات مجددا بر روی همان دستور Show All Comments کلیک کنید تا همه توضیحات مخفی شوند.

همچنین اگر هر سلول را انتخاب کنید دستور Show/Hide Comment توضیحات مربوط به آن را نمایان یا مخفی می کند.

 

 حذف توضیحات درEXCEL

➊ ابتدا سلولی که دارای توضیحات باشد و قصد حذف توضیحاتش را دارید انتخاب نمایید. در اینجا ما سلول E13 را انتخاب می کنیم.


➋ حالا ابتدا تب Review و سپس دستور Delete را در گروه Comments کلیک کنید.



➌ توضیحات مربوطه حذف می گردد.

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

در اکسل ميتوانيم ليست  را بر اساس هر يك از ستونهاي آن به صورت صعودي يا نزولي مرتب كنيم. براي اين كار كافي است با ما همراه باشید تا با روش های آن آشنا شویم:
روش اول :
1- يك سلول از ستوني را كه ميخواهيم ليست بر اساس آن مرتب شود انتخاب ميكنيم.
2- براي مرتب سازي صعودي  در اکسل از آيكون Sort Ascending  و براي مرتب سازي نزولي  در اکسل از آيكون Sort Descending در نوار ابزار Standard استفاده ميكنيم.
نکته
بايد توجه داشته باشيم كه در اين حالت نبايد يك ستون انتخاب شود بلكه فقط يك سلول از ستوني كه ميخواهيم بر اساس آن ليست مرتب شود را انتخاب ميكنيم.
روش دوم :
1- كل جدول يا يك سلول از جدول را انتخاب ميكنيم.
2- منوي Data را انتخاب ميكنيم.
3- گزينه Sort را انتخاب ميكنيم.
4- پنجره اي باز ميشود كه بايد قسمتهاي زير را با توجه به نياز در آن پر كنيم :
الف- Sort By : در اين كادر ستوني را كه ميخواهيم ليست بر اساس آن مرتب شود ، انتخاب ميكنيم.
ب- Then By : در اين كادر ستوني را تعيين ميكنيم كه اگر اطلاعات ستوني که در کادر اول مشخص شده براي مرتب سازي مانند هم بود ليست بر اساس اين ستون مرتب شود.
ج- Ascending : با انتخاب اين گزينه ليست به صورت صعودي مرتب ميشود.
د- Descending : با انتخاب اين گزينه ليست به صورت نزولي مرتب ميشود.
هـ- My List Has : در اين قسمت دو گزينه Header Row ، و No Header Row وجود دارد. اگر Header Row انتخاب شده باشد هنگام باز شدن پنجره Sort كل ليست به غير از سطر اول كه سطر عنوان است انتخاب ميشود و در كادر هاي Sort By , Then By عناوين قرار ميگيرند. ولي اگر No Header Row را انتخاب كنيم هنگام باز شدن پنجره Sort كل جدول حتي سطر اول كه سطر عنوان است انتخاب ميشود و در كادر هاي Sort by , Then By بجاي عناوين, نام ستونها قرار ميگيرند. در حقيقت در اين حالت عناوين جزء اطلاعات در نظر گرفته شده و در مرتب سازي  در اکسل شركت داده ميشوند.

نکاتی ارزنده پیرامون نحوه ذخیره ماکرو در اکسل

هدف از آموزش اکسل امروز این است که چگونه میتوانیم  در نرم افزار اکسل ماکرو یا کد VBA خود را ینویسیم چطور باید فایل راذخیره کنیم.  اگر بخواهیم ماکرو رابه کامپیوتر دگری  انتقال دهیم  فرمت  اکسل برای ذخیره ورکبوک ها در نسخه های قدیمی تر XLS و در نسخه های جدیدتر XLSX است. این فرمت ها از ذخیره سازی ماکرو پشتیبانی نمیکنند به این معنی که اگر کد VBA یا ماکرویی را در فایل اکسل ن بنویسید و بعد با فرمت XLSX ذخیره کنید تمام کدهای VBA که ایجاد کردید از بین میرود و پس از اینکه فایل ر امجددا باز کنید اثری از کدهایی که نوشتید دیده نخواهد شد.

در اکسل برای ذخیره فایل هایی که دارای کد VBA و ماکرو هستنددر اکسل میتونیم از دو فرمت XLSM و XLSB استفاده کنیم. برای اینکار  در پنجره SAVE AS اکسل یکی از این دو فرمت رو برای ذخیره ماکرو انتخاب کنیم.

 

زمانی که فایل اکسل را با فرمت XSM یا XLSB ذخیره میکنیم ماکروها و کدهای VBA ما هم در فایل ذخیره میشود و زمانی که این فایل رو به سیستم دیگری منتقل کنیم میتوانیم از کدهایی که نوشتیم استفاده کنیم.

 فرض کنید بخواهیم کدی رابنویسیم که بتوانیم آن کد را به سیستم دیگه ای انتقال دهیم و در تمام فایل های اکسلی که ایجاد میکنیم (نه فقط فایل XLSM که ذخیره کردیم) از آن کد استفاده کنیم. به عنوان مثال اگر بخواهیم تابعی را وارد کنیم  که بتوانیم ازآن در  فرمول نویسی های در تمام فایل های اکسل استفاده کنیم. برای اینکار میتاونیم از فرمت XLAM اکسل استفاده کنیم که برای تهیه Add-In ها در اکسل استفاده میشود. این فرمت در واقع بسیار شبیه فرمت XLSM هست با تفاوت های جزئی. برای استفاده از این فرمت ابتدا باید فایل خودرا در پنجره SAVE AS اکسل با فرمت XLAM ذخیره میکنیم و به سیستم موردنظرانتقال بدهید. اکنون کافیست این فایل رادر نرم افزار اکسل به صورت یک Add-In اضافه کنید. در پنجره تنظیمات اکسل وارد بخش Add-Ins میشویم و در قسمت پایین پس از انتخاب Excel Add-Ins در بخش Manage رو دکمه Go کلیک میکنیم.

 

با انتخاب دکمه GO پنجره Add-Ins باز میشود.

 

حالا روی دکمه Browse کلیک میکنیم و فایل XLAM را انتخاب میکنیم. با اینکار فایل ما به عنوان یک Add-In به اکسل اضافه میشود و میتوانیم از توابع و کدهای آن استفاده کنیم.

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

در نرم افز ار Word این امکان وجود دارد که به راحتی یک متن تایپ شده را با یک سطر و ستون دیگر جابجا کنید اما اگر بخواهیم مقادیر وارد شده در سلول های جدول اکسل را بدون تغییر جابجا کنیم اعمال زیر را انجام میدهیم                                                                                                                              فرض کنید در یک سلول شماره تماس و در سلول دیگر آدرس را وارد کرده باشید . حال آدرس ها اشتباه وارد شده و مقادیر سلول آن به یک ردیف بالا یا پایین جدول باید انتقال پیدا کند . ساده ترین راهی که به نظر شما میرسد کپی دوباره متن یا استفاده از کلیدهای Ctrl + C و Ctrl+V می باشد . اما روش ساده تری نیز در برنامه وجود دارد که بعنوان یک ترفند مورد استفاده قرار می گیرد . در ادامه این روش را توضیح میدهم .

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

نکاتی پیرامون چگونگی پنهان کردن مطالب در اکسل

 

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

 

پنهان کردن یک برگه در اکسل

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

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

پنهان شدن یکی از چهار خانه‌های جدول (Cell) مثل یک برگه نیست که آن را پنهان کنید و سپس هر گاه خواستید آن را بازگردانید. اما واقعاً راهی ندارد چون اگر مخفی شود چیزی جای آن قرار نمی گیرد؟
در پایین بیشتر می آموزید اما بدانید شما می‌توانید هر تعداد ردیف یا ستون را که می‌خواهید خالی نمایش دهید در حالتی که آن‌ها واقعاً خالی نیستند.
برای این کار روی یک یا چند سلول راست کلیک کنید و Format Cells را انتخاب کنید.
در بخش Number، گزینه Custom را از پایین کلیک کنید و سه سمیکالن (,,,) بدون پرانتز در فضای Type وارد کنید.
سپس OK را بزنید و در کمال تعجب می‌بینید که همه بخش‌های انتخابی خالی شده اند! با کلیک کردن بر روی آن‌ها همچنان خالی هستند اما در نوار فرمول ها محتوای آن دیده خواهند شد.
برای دوباره نمایان کردن اطلاعات کافی است مراحل بالا را طی کرده و به جای Custom، گزینه Original را انتخاب کنید. دقت داشته باشید اگر در سلول‌ها هر متنی نوشته شده باشد، با زدن اینتر محو خواهد شد. همچنین با تایپ بر روی سلول‌های مخفی، هر چه پنهان کرده‌اند با متن جدید تایپ شده جایگزین خواهند شد.

پنهان کردن خطوط در اکسل

 

برای پنهان شدن خطوط فاصل، کافی است زبانه VIEW را انتخاب کرده و تیک Gridlines را بردارید. همچنین می‌توانید به Page Layout رفته و تیک View زیر بخش Gridlines را نیز بردارید.

پنهان کردن ستون‌ها و ردیف ها

اگر می‌خواهید یک ردیف یا ستون را کلاً پنهان کنید، بر روی سر برگ (Header) آن راست کلیک کرده و Hide را بزنید. برای مخفی کردن چندین ردیف نیاز به راست کلیک کردن بر روی شماره ردیف آن‌ها در سمت چپ دارید. برای مخفی کردن چند ستون این عمل را در بالای سربرگ آن‌ها انجام دهید.

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

پنهان کردن فرمول ها در excel

پنهان کردن فرمول ها کمی دشوار تر از بقیه مراحل است. اگر می خواهید فرمولی را پنهان کنید ابتدا باید آن را پنهان کنید و سپس از آن محافظت کنید.
برای این کار ابتدا، بر روی ستون F، راست کلیک کرده و Format Cells را انتخاب کنید.
اکنون روی زبانه Protection کلیک کنید و تیک گزینه Hidden را بزنید. سپس برای محافظت از سلول، بر روی Review کلیک کرده و Protect Sheet را انتخاب کنید.
اگر مایل هستید تا همه نتوانند فرمول های شما را از پنهانی خارج کنند، می‌توانید برای آن گذرواژه ای تعیین کنید. با فشردن دکمه های Ctrl+~ (همان پ بالای تب)  با کلیک بر روی Show Formulas در تب Formulas دیگر قابل مشاهده نخواهند بود.

پنهان کردن کامنت ها

کامنت ها یا همان اطلاعات اضافی مربوط به هر سلول، به طور خودکار با نوشته شدن،  به صورت یک نشانه قرمز در گوشه راست هر سلول قرار خواهند گرفت. برای تغییر حالت خودکار بر روی  File  و سپس Option،  و زبانه Advanced، روی No comments or indicators کلیک کنید. برای نمایش دادن کافی است Comments and indicators را مجدداً بزنید یا از Show All Comments استفاده کنید.

 

اگر هم در یک سلول یک متن بلند بالا نوشتید و به نوعی از اندازه خود خارج تر بود، کافی است روی آن راست کلیک کرده و Format Cells را بزنید. سپس از Alignment، در زیر Horizontal، گزینه Fill را انتخاب کنید. اکنون می بینیم که مشکل طولانی بودن برطرف شده است.

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

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

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

آشنایی با انواع داده در اکسل

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

 

 

اعداد در اکسل

اعداد در اکسل از اهمیت بسیاری برخوردارند، در مراتب حرفه ای تر کار با اکسل و مخصوصاً در یادگیری و استفاده از برنامه نویسی ویژوال بیسیک در اکسل، به اهمیت شناخت و تفکیک انواع داده در اکسل و کار با اعداد پی خواهیم برد. حتی اعداد نیز خود به چند دسته تعریف میشوند که در توصیف نوع متغیرها در ماکرو نویسی در اکسل بسیار مهم میباشد. داده های عددی در اکسل ممکن است حتی به فرمت متنی نیز ذخیره شوند که انجام اینکار گاهی لازم میباشد. برای توضیحات بیشتر فیلم آموزشی قرار داده شده در انتهای این مقاله را مشاهده نمایید. توابع ریاضی و مثلثاتی در اکسل (Math & Trig Functions) از جمله توابعی هستند که بر روی داده های عددی عملیات انجام میدهند.

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

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

مالی و حسابداری در اکسل

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

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

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

از جمله پر کاربردترین ابزارهای اکسل در حوزه های امور اداری، حسابداری و مستند سازی، توابع تاریخ و زمان (Date & Time Functions) می باشند، برای یادگیری کار با توابع تاریخ و زمان و البته انجام عملیات بر روی سلول های حاوی تاریخ و زمان، لازم است کاربران درک درستی از داده های نوع تاریخ و زمان داشته باشند. در مقالات آینده توابع تاریخ و زمان در اکسل مورد بررسی قرار خواهند گرفت.

 متن در اکسل

استفاده از توابع و داده های متنی چه در محیط اکسل و چه در زبان برنامه نویسی ویژوال بیسیک در اکسل همواره در سطوح بالاتر و حرفه ای تر قرار گرفته و این موضوع برای فایل های اکسل با زبان فارسی یادگیری مهارت های بیشتری را می طلبد. توابع متنی در اکسل (Text Functions In Excel) از جمله توابع بسیار مهم و عمومی در اکسل به شمار میروند که ماموریت آنها انجام عملیات بر روی داده های متنی میباشد. لذا شناخت داده های متنی و روش تعریف آنها از اهمیت بسیاری برخوردار است.

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

داده های Boolean در اکسل

داده های Boolean در اکسل داده هایی هستند که صرفاً میتوانند یکی از دو حالت True و False باشند. لازم به ذکر است در اکسل و ویژوال بیسیک، همواره عبارت False برابر با عدد صفر و عبارت True برابر با عدد یک میباشد. پس داده های Boolean داده هایی با دو مقدار صفر یا یک میباشند. البته داده ی نوع Null هم وجود دارد که به معنی Not True و Not False است. در آینده در مورد این نوع داده ها بیشتر اطلاعات کسب خواهیم کرد.

توابع منطقی در اکسل (Logical Functions) که از ارکان اکسل به شمار میروند و توابع اطلاعات در اکسل (Information Functions) از جمله توابعی هستند که با داده های نوع Boolean سر و کار دارند.

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

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

۱- عدم نمایش تبها و شیتها در قسمت پائینی اکسل

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

• روی گزینه file کلیک کنید و به قسمت option بروید.
• در پنجره باز شده به تب Advanced بروید.
• در قسمت Display option for this workbook تیک گزینه Show Sheets Tab را بزنید .

۲- حل مشکل اعدادی که به صورت متن وارد شده اند

فرض  کنید در ستون A1 الی A12 مجموعه ای از اعداد را وارد کرده اید و در A13 فرمولی می نویسید که A1:A12 را جمع کند و در کمال تعجب میبینید جواب اشتباه است و با کمی دقت متوجه می شوید که در گوشه بالا سمت راست برخی از سلولها رنگ سبزی جلب توجه می کند که می خواهد یادآوری کند که در این سلول داده عددی با قالب متن وارد شده و در نتیجه در حاصلجمع محاسبه نمی شود . راه حل چیست؟

• شما می توانید تک تک سلولهایی که این مشکل را دارند انتخاب کرده و گزینه Convert to number را بزنید که راه خسته کننده با درصد خطای بالایی است.
• راه دوم این است که در یک سلول که مطمئن هستید از نوع عددی است عدد ۱ را وارد کنید و روی سلول کلیک راست کرده و Copy را بزنید و سپس محدوده ای که مشکوک هستید در آن اعداد به صورت متنی ذخیره شده باشند راست کلیک کرده و گزینه Paste Special را بزنید و مانند تصویر زیر گزینه Multiply را انتخاب کرده و ok کنید.

۳- حذف فضای خالی ما بین نوشته ها در  اکسل

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

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

=Trim(a1)

۴- حذف سلولهای خالی موجود در محدوده در اکسل و یا تغییر نام آنها

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

• کلیدهای ترکیبی Ctrl+G را بزنید
• روی گزینه Special کلیک کنید تا پنجره Go To Special باز شود .
• حال گزینه Blank را انتخاب کرده و OK کنید .
• می بینید که سلولهای خالی به حالت انتخاب در آمده است که می توانید آنها را حذف کنید .
• اگر خواستید آنها را تغییر نام دهید در همان حالت انتخاب متن مورد نظر خود را تایپ کرده و کلیدهای ترکیبی Ctrl+Enter را بزنید.

۵- جدا کردن نام و نام خانوادگی که در یک ستون وارد شده و تقسیم به دو ستون

فرض می کنیم ستونی داریم که در آن وارد شده    “نادر بحری تهران” و شما می خواهید این ستون به سه ستون   “نادر” “بحری” “تهران” تبدیل شود.

• ستون مورد نظر خود را انتخاب کرده و کلیدهای ترکیبی Alt+A+E بزیند
• پنجره Convert Text To Column نمایش داده خواهد که مانند فیلم آموزشی عمل کنید

۶- مشاهده داده های تکراری در محدوده

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

• محدوده مورد نظر را انتخاب نمائید و کلیدهای ترکیبی Alt+H+L+H+D را بزنید ( در تب Home روی Conditional Formatting کلیک کنید ) که منوی مربوط به Conditional Formatting باز میشود که باید Duplicate values را انتخاب کرده و نتیجه را ببینید.

۷- جایگزین کردن عدد صفر با سلول خالی

• محدوده مورد نظر را انتخاب نمائید روی گزینه File کلیک کنید
• به Option بروید  و از تب Advanced بروید
• در قسمت Display Option for this worksheet تیک گزینه Show a Zero in cells… را بزنید

۸- پاک کردن فرمت و تغییراتی که در شیت اعمال کرده اید .

• محدوده مورد نظر را انتخاب نمائید و به تب Home بروید
• در قسمت Editing روی Clear کلیک کنید و گزینه Clear Formatting را انتخاب کنید.

نکاتی ارزنده پیرامون دستور Find و ۱۰ نکته کلیدی در مورد اکسل

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

برای استفاده از این ابزار کافی است از تب Home قسمت Find & Select دستور Find را انتخاب نموده (یا با استفاده از کلید میانبر Ctrl+f) تا پنجره Find  باز شود .

 

به منظور استفاده از این دستور کافی است عبارت مد نظر جهت جستو جو را در قسمت Find What ثبت کرده و با زدن کلیدهای Find Next یا Find All آن عبارت در بین سلولها جست و جو شود، برای مثال با ثبت کردن واژ] علی درون قسمت Find What، با هر بار کلیلک کردن بر روی گزینه Find Next سلول بعدی که شامل کلمه علی باشد (کافی است کلمه علی در آن سلول به کار رفته باشد) را فعال خواهد کرد و با زدن گزینه Find All، تمامی سلولهایی که در آن کلمه علی استفاده شده است پیشنهاد می شود و با انتخاب هر یک، سلول مربوطه فعال خواهد شد.

 

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

در استفاده از این دستور باید نکات ذیل را در نظر گرفت:

۱- در ابتدای کار، اگر تنها یک سلول انتخاب شده باشد، عمل جست و جو در کل آن شیت انجام می‌گیرد ولی در صورتی که بیش از یک سلول انتخاب شده باشد، عمل جست و جو تنها بر روی سلولهای انتخاب شده صورت میگیرد (اگر به دنبال واژه علی در ستون خاص می‌گردیم، کافی است قبل از انجام عمل سرچ؛ ان ستون را انتخاب نماییم)

۲- در حالت عادی با زدن کلید Find Next سلول بعدی که در آن واژه علی به کار رفته است را پیشنهاد میدهد، اگر بخواهیم سلول قبلی را پیشنهاد دهد، کافی است هنگام زدن کلید Find Next دکمه Shift را نگهداریم.

۳- در حالت عادی عمل جست و جو نسبت به حروف کوچک و بزرگ حساس نمی باشد، (واژه ali و Ali هیچ فرقی نمیکند)، ولی با فعال کردن گزینه Match Case، عمل جست و جو نسبت به حروف کوچک و بزرگ حساس می‌باشد.

۴- در حالتی که به دنبال کلمه علی درون سلولها می‌گردیم، در صورتی که درون سلو.لی عبارت “علی رضا” ثبت شده باشد، اکسل آن سلول را نیز پیشنهاد می‌دهد ولی اگر ما به دنبال سلولهایی باشیم که عینا واژه علی در آنها ثبت شده باشد (نه حالات علی رضا، محمد علی و ….) کافی است گزینه Match Entire Cell Content را فعال نموده.

۵- با تغییر دادن حالت With in از Worksheet به Workbook، عمل جست و جو در شیتهای دیگر نیز انجام می‌گیرد.

۶-حالت عادی عمل جست و جو به صورت ردیفی انجام می گیرد (ابتدا تمامی سلولهایی که در ردیف ۱ هستند جست و جو میشود سپس سلولهای ردیف ۲ و ….) که با تغییر دادن گزینه Search از حالت By Rows به By Columns، عمل جست و جو ستونی صورت می گیرد. (ابتدا سلولهای ستون A جست و جو میشود و سپس ستون B و….)

۷-میتوان حالت Look in را تغییر داده تا عمل جست و جو درون فمول یا کامنت نیز انجام شود.

۸- بعد از زدن گزینه Find All، میتوان کلید Ctrl+A را فشار داده تا تمامی سلولهای مشخص شده انتخاب گردند .(برای زمانی که میخواهیم تمامی سلولهای شامل عبارت علی را انتخاب کنیم و کار یکسانی روی آنها انجام دهیم، مثلا رنگ تمامی سلولها را قرمز نماییم)

۹-در مواردی که حرف یا کلمات مد نظر مجهول هستند؛ میتوان از علائم ؟ یا * استفاده نمود.اگر تعداد حروف مشخص باشد ولی نوع آنها مشخص نباشد، به ازای هر حرف یک بار از علامت ؟ استفاده می شود، ولی اگر علاوه بر نوع حرف تعداد نیز مجهول باشد از علامت * استفاده می شود، برای مثال عبارت A?i بیانگر تمامی سلولهایی می باشد که در آنها بعد از حرف A یک حرف دیگر آمده باشد و بعد از آن حرف مجهول حرف i آمده باشد، مانند حالات Ali, Ari, Ami, A2i و …. ولی حالت A*i برای زمانی استفاده میشود که حرف A امده باشد و بعد از آن بدون در نظر گرفتن تعداد حروف بین، حرف I امده باشد، مثل حالات Ali, Ami, Ahmadi و …..

۱۰- در حالتی که به دنبال حرف ؟ یا * درون سلول هستیم، باید قبل از آنها از علامت ~ استفاده نمود به صورت ?~ یا *~

 

با در نظر گرفتن نکات فوق در ادامه چند مورد را بررسی می‌نماییم. با فرض این که در ستون B از فایل اکسل کدهای کالا ثبت شده است:

• اگر به دنبال کدهای سه حرفی به بالا میگردیم: کافی است در قسمت Find What عبارت ؟؟؟ را تایپ نماییم.
• اگر تنها به دنبال کدهای سه حرفی میگردیم، کافی است گزینه Match Entire Cell Content را فعال نموده و در قسمت Find What عبارت ؟؟؟ را ثبت نموده.
• اگر تنها به دنبال کدهای سه حرفی میگردیم که با حرف F آغاز شده باشند، کافی است گزینه Match Entire Cell Content را فعال نموده و در قسمت Find What عبارت ؟؟F را ثبت نموده.
• اگر به دنبال تمامی کدهایی هستیم که با حرف F آغاز شده باشند، کافی است گزینه Match Entire Cell Content را فعال نموده و در قسمت Find What عبارت *F را ثبت نموده.
• اگر به دنبال تمامی کدهایی هستیم که به حرف G ختم شده باشند، کافی است گزینه Match Entire Cell Content را فعال نموده و در قسمت Find What عبارت G* را ثبت نموده.

نکاتی پیرامون ساختار Pivot Table یا گزارشات حرفه ای در اکسل

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

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

 

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

سپس به تب کمکی DESIGN که بواسطه ایجاد جدول در  اکسل ایجاد شده است میرویم و روی گزینه Summarize With PivotTable کلیک می نماییم.

 

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

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

برای اینکار فیلد ماه را در Rows قرار دهید و فروش را در Values می توانید سایر فیلدها را در Filters و یا Columns قرار دهید.

 

حالا روی یکی از سلولهای مرتبط با ستون Row Lables که تاریخهای روزانه در آن قرار دارد کلیک راست نمایید و گزینه Group را انتخاب کرده و هر یک از گروه های مورد نظرتان(ماه، فصل، سال) را انتخاب نمایید. می توانید همه موارد مذکور را هم انتخاب کنید.

 

اگر میخواهید گزارشات هفتگی بسازید باید گزینه Days را انتخاب کنید و قسمت Number of days را عدد هفت تعیین کنید. در نتیجه براحتی گزارشی به تفکیک سریهای زمانی مختلف ایجاد نموده اید.

 

نکته دوم:  نقش Filter در PivotTable در اکسل

تا بحال به نقش فیلتر در PivotTable دقت کرده اید؟ آیا تا بحال فکر میکردید که فقط می توانید از طریق فیلد فیلتر گزارشات Customize شده بسازید؟ آیا می دانستید که می توانید بدون اینکه نیاز به ساخت PivotTableهای جدید باشد گزارشات مورد نظرتان را از یک PivotTable بسازید. منظور چیست؟ فرض کنید می خواهید همین گزارش فروش سالهای مختلف را به تفکیک برای هر محصول، هر شهر و هر ویزیتور داشته باشید. آیا از اول PivotTable میسازید؟ اینکار را نکنید اجازه دهید راهی فوق العاده را به شما نشان بدهم:

هر فیلدی را که میخواهید از آن Pivottable مشابه بسازید در فیلد فیلتر قرار دهید. در اینجا ما هم محصولات، هم شهرها و هم ویزیتورها را در فیلتر قرار داده ایم. سپس در تب Analyze روی قسمت Options کلیک کرده و دومین گزینه یعنی Show Report Filter Pages را انتخاب نمایید.

 

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

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

 

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

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

فرض کنید می خواهید حاشیه سود را طی سه سال و در فصول مختلف محاسبه کنید برای اینکار باید میزان سود در هر بازه زمانی را بر فروش همان دوره تقسیم کنید برای اینکار به تب Analyze بروید روی گزینه Fields, Items & Sets کلیک نمایید. اولین گزینه Calculated Fields را انتخاب نمایید.

 

در پنجره ای که باز میشود باید دو قسمت را تکمیل نمایید. در قسمت Name برای شاخصی که میخواهید محاسبات آنرا انجام دهید یک نام بگذارید در اینجا ما “حاشیه سود” نوشتیم و در قسمت Formula باید با استفاده از فیلدهای موجود در لیست پایین آن محاسبات را انجام دهید که ما در اینجا سود/زیان را بر فروش تقسیم کردیم. برای تقسیم از علامن / روی کیبرد استفاده کنید. بعد از اینکه ok کردید فیلد جدید خودبخود به PivotTable اضافه میشود.

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

نکته چهارم: ساختار نمودارهای داینامیک در اکسل

آیا تا بحال با خودتان فکر کرده اید که چه میشد اگر میتوانستید نمودارهایی بسازید که با اعمال فیلتر روی دیتاها نمودارتان نیز تغییر میکرد؟ بله درست است PivotTable این امکان را به شما میدهد. برای اینکار کافیست در تب Insert روی PivotChart کلیک نموده و نمودار مورد نظرتان را ایجاد نمایید. سپس در همان تب روی گزینه Slicer کلیک کرده و اسلایسرهای مورد نیازتان را بسازید.

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

 

 

دلایل استفاده از خاصیت TABLE و ابزارهای افزایش سرعت کار با اکسل

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

خاصیت TABLE در اکسل

 

خاصیت TABLE در اکسل

 

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

۱- محدوده داده رو انتخاب کنید .

۲- به تب Insert میرید و روی گزینه Table کلیک میکنید.

راه دوم و ساده تر اینه که محدوده رو انتخاب کنید و کلیدهای CTRL + T رو بزنید .

با هر دو روش به تصویر زیر میرسید :

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

 

اگر تیک my table has headers رو بزنید . ردیف اول محدوده انتخابی رو به عنوان سر ستون جدولتون در نظر میگیره

 

 مزایای table

۱- به راحتی و با یک کلیک ساده میتوانید شکل جدولتان را تغییر دهید  .

۲- بلافاصله گزینه های فیلتر کردن به جدول اضافه میشود

۳- اگر داده جدیدی به سطر آخر اضافه کنید هم فرمولها به صورت خودکار اضافه میشود هم شکل دهی به صورت خودکار آپدیت میشوند   .

۴- محدوده داده برای شما ایجاد میشود  و فرمول نویسی را  ساده تر میکند.

۵- به راحتی میتوانید جمع و میانگین و کوچکترین و بزرگتیرن داده را در جدول  داشته باشید .

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

۷- سر ستون یعنی سطر اول جدول ثابت می ماند، یعنی اگر جدول خود را اسکرول کنید ، سرستون ثابت می ماند.

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

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

 ابزارهای افزایش سرعت کار با اکسل                                                                                         

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

Flash Fill  و  افزایش سرعت کار با اکسل

در اکسل ۲۰۱۳ به بعد قابلیتیدر  اکسل اضافه شده به اسم Flash Fill که در واقع با استفاده از هوش مصنوعی اکسل کاری را که شما میخواید انجام بدهید را حدس میزنپد و در سلولهای بعدی تکرا میکند 

اجازه بدید با مثال توضیح بدم که بیشتر قابل درک باشه ، فرض کنید در سلولهای A1:A5 یه سری نام و نام خانوادگی وارد کردید و میخواید توی سلول B1:B5 فقط نامها رو داشته باشید ، خب قبلا ما در مورد فرمولهای متنی صحبت کرده بودیم و در مورد ترفند Text To Columns هم مبحثی رو داشتیم اما امروز میخوایم از Flash Fill استفاده کنیم که خیلی سریع تر و ساده تر هست و فقط کافیه توی B1 نام موجود توی A1 رو بنویسید و اینتر رو بزنید و و در سلول پائینی یعنی B2 از کلیدهای ترکیبی Ctrl + E استفاده کنید. نتیجه فوق العادست!!!

 

مطالبی جامع پیرامون Data Table در اکسل

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

با استفاده ابزار Data Table اکسل میتوانیم تاثیر تغییرات یک یا دو متغیر رو روی متغیر وابسته مدنظرمون بررسی کنیم. در ادامه با چگونگی استفاده از ابزار Data Table اکسل برای تحلیل تک متغیره و دو متغیره آشنا میشویم.

 

تحلیل تک متغیره در Data Table اکسل:

از این حالت زمانی استفاده میکنیم که بخواهیم نتیجه تغییرات صرفاً یک متغیر رادر فرمول  مشاهده کنیم.فرض کنید هزینه سوخت خودرو به ازای هر کیلومتر 240 واحد هست و ما میخواهیم تاثیرات مسافت طی شده خودرو را بر هزینه سوخت آن به ازای مسافت های یک تا ده کیلومتر تحلیل کنیم. برای اینکار ابتدا فرمول A1*240= رو در سلول A2 مینویسیم. سپس اعداد 1 تا 10 رو در سلول های B1 تا K1 قرار میدهیم. و از تب DATA و بخش Data Tools رو گزینه What-If Analysis کلیک میکنیم و در منویی که باز میشود  گزینه Data Table را انتخاب میکنیم.

 

با انتخاب این گزینه پنجره زیر باز میشوذد.

 

حالا روی فلش قرمز رنگ گزینه Row Input Cell کلیک میکینم. با کلیک روی این گزینه پنجره زیر باز میشود که ما باید در  سلول مربوط به متغیر مورد نظررا  انتخاب کنیم که این سلول همون سلول A1 هست.

 

پس از اینکه سول A1 را انتخاب کردیم با کلیک مجدد روی فلش قرمز رنگ به پنجره اصلی برمیگردیم و OK رو کلیک میکنیم. همونطور که مشاهد میکنید هزینه مربوط به کیلومترهای مختلف در سلولهای B2 تا K2 نمایش داده میشود.

 

تحلیل دو متغیره در Data Table اکسل:

در بخش قبل حالت تک متغیره ابزار Data Table اکسل رو بررسی کردیم. حالا میخوایم حالت دو متغیره Data Table رو امتحان کنیم بنابراین به یک مثال کاملتر احتیاج داریم. فرض کنید رشد یا کاهش فروش سازمان ما تابع دو متغیر مقدار افزایش تبلیغات و میزان افت کیفی محصول باشه به طوری که با هر واحد افزایش تبلیغات 17درصد به فروش سازمان افزوده بشه و با هر واحد کاهش کیفیت محصول 30درصد  از فروش سازمان کم بشه. میخوایم اثرات تغییر این دو متغیر رو روی افزایش یا کاهش فروش سازمان بررسی کنیم. دامنه بررسی ما برای متغیر کاهش کیفیت محصول بین 0.5 تا 3 واحد و برای متغیر افزایش تبلیغات بین 0 تا 5 واحد هست. برای شروع ابتدا در سلول A3 فرمول افزایش فرمول تغییرات فروش سازمان رو که A1*-0.3+A2*0.17= مینویسیم. با توجه به فرمولی که ما نوشتیم سلول A1 معرف متغییر کاهش کیفیت و سلول A2 معرف متغییر افزایش تبلیغات هست.

 

حالا مثل حالت تک متغیره از تب DATA گزینه Data Table رو انتخاب میکنیم و در بخش Row Input Cell سلول A1 و در بخش  Column Input Cell سلول A2 رو قرار میدیم و پنجره Data Table رو OK میکنیم. با اینکار درصد تغییرات فروش برای مقادیر مختلف افزایش تبلیغات و کاهش کیفیت محصول در جدول نمایش داده میشه.

 

به عنوان مثال اگر کیفیت محصول یک واحد افت کنه و ما 4 واحد تبلیغاتمون رو افزایش بدیم فروش سازمان 38درصد افزایش پیدا میکنه یا اگر کیفیت محصول ما 3 واحد افت کنه، علیرغم افزایش 5 واحدی تبلیغات هم فروش سازمان 5درصد افت خواهد داشت

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

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

 

 

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

 

ابتدا تیترها را انتخاب کنید.

 

سپس به مسیر Home → Alignment → Orientation →Angle Counterclockwise  بروید تا تیترها زاویه 45 درجه در خلاف جهت عقربه ساعت پیدا کنند.

 

 

 

سپس به مسیر Home → Font → Borders →All Borders  رفته تا برای تیترها Border  اختصاص داده شود( البته می توانید به تمام فضای سلول ها Border  اختصاص دهید)

 

 

 

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

 

 

 

 

البته می توانستیم تمام مراحل بالا را در قسمت Format cells و با زدن کلیدهای ترکیبی Ctrl + 1 انجام دهیم.                                                                       

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

 

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

 

روش های پنهان کردن مطالب در اکسل

 

پنهان کردن یک برگه در اکسل

 

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

 

پنهان کردن یک سلول از جدول در EXCEL

 

پنهان شدن یکی از چهار خانه‌های جدول (Cell) مثل یک برگه نیست که آن را پنهان کنید و سپس هر گاه خواستید آن را بازگردانید. اما واقعاً راهی ندارد چون اگر مخفی شود چیزی جای آن قرار نمی گیرد؟
در پایین بیشتر می آموزید اما بدانید شما می‌توانید هر تعداد ردیف یا ستون را که می‌خواهید خالی نمایش دهید در حالتی که آن‌ها واقعاً خالی نیستند.
برای این کار روی یک یا چند سلول راست کلیک کنید و Format Cells را انتخاب کنید.
در بخش Number، گزینه Custom را از پایین کلیک کنید و سه سمیکالن (,,,) بدون پرانتز در فضای Type وارد کنید.
سپس OK را بزنید و در کمال تعجب می‌بینید که همه بخش‌های انتخابی خالی شده اند! با کلیک کردن بر روی آن‌ها همچنان خالی هستند اما در نوار فرمول ها محتوای آن دیده خواهند شد.
برای دوباره نمایان کردن اطلاعات کافی است مراحل بالا را طی کرده و به جای Custom، گزینه Original را انتخاب کنید. دقت داشته باشید اگر در سلول‌ها هر متنی نوشته شده باشد، با زدن اینتر محو خواهد شد. همچنین با تایپ بر روی سلول‌های مخفی، هر چه پنهان کرده‌اند با متن جدید تایپ شده جایگزین خواهند شد.

 

پنهان کردن خطوط

 

 

 

برای پنهان شدن خطوط فاصل، کافی است زبانه VIEW را انتخاب کرده و تیک Gridlines را بردارید. همچنین می‌توانید به Page Layout رفته و تیک View زیر بخش Gridlines را نیز بردارید.

 

پنهان کردن ستون‌ها و ردیف ها

 

اگر می‌خواهید یک ردیف یا ستون را کلاً پنهان کنید، بر روی سر برگ (Header) آن راست کلیک کرده و Hide را بزنید. برای مخفی کردن چندین ردیف نیاز به راست کلیک کردن بر روی شماره ردیف آن‌ها در سمت چپ دارید. برای مخفی کردن چند ستون این عمل را در بالای سربرگ آن‌ها انجام دهید.

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

 

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

 

پنهان کردن فرمول ها کمی دشوار تر از بقیه مراحل است. اگر می خواهید فرمولی را پنهان کنید ابتدا باید آن را پنهان کنید و سپس از آن محافظت کنید.
برای این کار ابتدا، بر روی ستون F، راست کلیک کرده و Format Cells را انتخاب کنید.
اکنون روی زبانه Protection کلیک کنید و تیک گزینه Hidden را بزنید. سپس برای محافظت از سلول، بر روی Review کلیک کرده و Protect Sheet را انتخاب کنید.
اگر مایل هستید تا همه نتوانند فرمول های شما را از پنهانی خارج کنند، می‌توانید برای آن گذرواژه ای تعیین کنید. با فشردن دکمه های Ctrl+~ (همان پ بالای تب)  با کلیک بر روی Show Formulas در تب Formulas دیگر قابل مشاهده نخواهند بود.

 

پنهان کردن کامنت ها

 

کامنت ها یا همان اطلاعات اضافی مربوط به هر سلول، به طور خودکار با نوشته شدن،  به صورت یک نشانه قرمز در گوشه راست هر سلول قرار خواهند گرفت. برای تغییر حالت خودکار بر روی  File  و سپس Option،  و زبانه Advanced، روی No comments or indicators کلیک کنید. برای نمایش دادن کافی است Comments and indicators را مجدداً بزنید یا از Show All Comments استفاده کنید.

 

 

اگر هم در یک سلول یک متن بلند بالا نوشتید و به نوعی از اندازه خود خارج تر بود، کافی است روی آن راست کلیک کرده و Format Cells را بزنید. سپس از Alignment، در زیر Horizontal، گزینه Fill را انتخاب کنید. اکنون می بینیم که مشکل طولانی بودن برطرف شده است.