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

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

1. استفاده بیش از حد از توابع Volatileدر اکسل : توابع Volatile توابعی هستند که با هر بار محاسبه مجدد ورکبوک اونها هم مجدداً محاسبه میشوند. برخی از این توابع شامل RAND، RANDBETWEEN ،NOW ،TODAY ،OFFSET ،CELL ،INDIRECT میشوند. حالا اگر  از این توابع در فایل زیاد استفاده کرده باشید احتمالاً میتوانید حدس بزنید با هر بار تغییر دادن مقدار یک سلول چه اتفاقی می افتد. بنابراین بهتراست تا حد امکان از این توابع زیاد استفاده نشود و از توابع دیگر مثل Index، Sumif و … استفاده شود
2. استفاده زیاد از Conditional formattin در اکسل :  فرمول های موجود در Conditional formatting ها هم با هر بار محاسبه ورکشیت دوباره محاسبه میشه. بنابراین باید در استفاده از اونها هم دقت زیادی کرد.
3. وجود سلول های بدون استفاده در شیت ها: گاهی سلول هایی که توسط اکسل به عنوان سلول های فعال یک شیت لحاظ میشود با سلول هایی که در آن شیت فعال هستند متفاوت است. در
   برای اینکه بفمیم اکسل کدام سلول را به عنوان آخرین سلول فعال شیت در نظر گرفته است میتوانیم از کلید ترکیبی ctrl+end استفاده کنیم. اگر سلولی که اکسل به عنوان سلول آخر در نظر گرفته است اشتباه باشد باعث میشودحجم فایل بدون دلیل بالا برود. برای  حل این مشکل باید سطرها و ستون های اضافه را انتخاب و پاک کنید. بعد فایل را ذخیره کنید. این مساله بخصوص در مورد افزایش حجم فایل ها خیلی تاثیر گذار است
4. لینک کردن به سایر ورکبوک ها در اکسل : لینک کردن یک ورکبوک به ورکبوک های دیگر باعث کاهش سرعت میشود. بهتراست تا جایی که امکان دارد اطلاعات را در یک ورکبوک قرار دهید یا تا جایی که میشود به ورکبوک های خارجی کمتری لینک کنید.
5. وجود شیت های بیش از حد: بهتراست تا حد ممکن ورکبوک هایی که میسازیم تعداد شیت های کمتری داشته باشد. این مساله هم در افزایش سرعت فایل اکسل بی تاثیر نیست.

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

نکاتی پیرامون استفاده از قابلیت های حرفه ای Excel به منظور افزایش سرعت اکسل

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

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

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

2. ثابت کردن عنوان سطرها و ستون هادر excel

   در استفاده از جداول بزرگ مشکلی که اغلب افراد با آن سر و کله می زنند این است که وقتی سطرهای جدول بیش از یک صفحه می شود برای مشاهده عنوان ستون ها و یا سطرهای مختلف نیاز است دائما به ابتدای سطر و ستون مراجعه نمایند. یکی از امکاناتی که اکسل برای این منظور فراهم نموده است، استفاده از گزینه هایی به منظور ثابت نمودن سطر و ستون ابتدایی در بالای صفحه به صورت یک ریبون است، که به کاربر امکان می دهد در هرکجای صفحه جدول که باشد به سر تیتر های سطر و ستون خود مشابه صفحه نخست دسترسی داشته باشد.
   برای اینجام این کار کافی است از تب View گزینه Freeze Panes را در قسمت مربوط به ribbon انتخاب کنید. سپس در منوی کشویی بخش Freeze Panes، Freeze Top Row و سپس Freeze First Column را انتخاب کنید. در صورت لزوم می ‌توانید هر دو گزینه‌ را انتخاب کنید.
   
   با فعال سازی این گزینه در هرزمان و هر بخش از صفحه قادر به مشاهده عنوان های سطر و ستون مربوطه خواهید بود. در صورت تمایل به حذف این ویژگی نیز می توانید با مراجعه به گزینه‌ ی Freeze Panes روی عبارت Unfreeze Panes، کلیک کنید.
   نکته: در نسخه های پایین تر مایکروسافت اکسل روند کار به این صورت است که برای انجام این کار بایستی با انتخاب سلول مشترک بین سلول ‌های مربوط به سطر و ستونی که می‌ خواهید ثابت بماند، انتخاب کنید و گزینه‌ ی Freeze Panes را بزنید.

3. شماره گذاری یک ستون

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

4. جا به جا نمودن سطرها و ستون ها

   در صورتی که پس از ورود اطلاعات تصمیم گرفتید جای سطرها و یا ستون های خود را عوض کنید، نیازی نیست نگران باشید و یا وقت خود را برای پر کردن دوباره اطلاعات به هدر دهید. تنها کاری که لازم است طی کردن مراحل زیر خواهد بود:
   – سلول هایی که مربوز به عنوان های سط و ستون هستن انتخاب نمایید.
   – با راست کلیک نمودن بر روی آن ها عبارت Copy را برگزینید. همچنین می توانید با مرجعه به تب Home در قسمت ribbon، دکمه‌ ی کپی را انتخاب کنید.
   – سطر و یا ستون مورد نظر خود را برای شروع انتخاب نمایید.
   – راست کلیک کنید و Paste Special را انتخاب نمایید. بعلاوه می ‌توانید در تب Home قسمت ribbon، روی عبارت Paste و پس از آن Paste Special، کلیک کنید.
   – چک باکس مرتبط با Transpose  را انتخاب نموده و برای تایید دکمه Ok را بفشارید.
   
   

5. استفاده از ماشین حساب

   علاوه بر این که شما قادر خواهید بود از طریق یک افزونه ماشین حساب را در این برنامه به خدمت بگیرید، از دو روش نیز می توانید آن را به صفحه خود بیافزایید. برای این منظور می توانید با استفاده از ribbon و دیگری با استفاده از نوار ابزار Quick Access یک ماشین حساب را به خدمت بگیرید. روش انجام این کار به صورت زیر خواهد بود:
   ابتدا با مراجعه به گزینه Options  یکی از گزینه های Customize Ribbon یا Quick Access Toolbar را بر اساس جایی که می‌خواهید از آن استفاده کنید، انتخاب نمایید. در بخش Choose commands from موجود در لیست کشویی، All Commands را انتخاب کنید. در لیست مورد نظر به سمت پایی اسکرول نماید تا گزینه Calculator  مشاهده گردد، آن را انتخاب نموده و برای افزودن به نوار ابزار از دکمه ADD استفاده نمایید. توجه داشته باشید که اگر بخواهید آن را در ribbon اضافه کنید، باید گروهی سفارشی ایجاد و ماشین‌ حساب را در آنجا اضافه کنید.
   
   

6. لینک کردن به برگه ها و صفحه ها در اکسل

   یکی از روش هایی که به شما در افزایش سرعت و کارایی هنگام استفاده از اکسل کمک خواهد نمود، ایجاد لینک ها برای اتصال صفحات و گزارش های پیوسته به یکدیگر می باشد. برای این منظور می توانید مسیر زیر را دنبال نمایید.
   – سلولی که قصد دارید به منظور لینک مورد استفاده قرار دهید، انتخاب نمایید.
   – بر روی سلول مورد نظر راست کلیک نموده و گزینه Hyperlink  را انتخاب نمایید. همچنین می توانید برای این منظور از تب  insert اقدام نموده و گزینه Hyperlink  را در قسمت ribbon کلیک کنید.
   – گزینه ی Place in this Document را از پنجره ظاهر شده انتخاب نمایید.
   – اطلاعات مورد نظر شامل مطلبی که قصد دارید به عنوان لینک در آن سلول نمایش یابد و همچنین مرجع آن به همراه صفحات گزارش را مشخص نمایید.
   – کلید Ok را به منظور تایید تنظیمات کلیک نمایید.
   
   

7. استفاده از کلید های میانبر صفحه کلید در  اکسل

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

F4 (‌یا Fn+F4‌) تکرار آخرین دستور قالب بندی انجام گرفته مثلا رنگ، یا فونت

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

Ctrl + W، برای بستن یک صفحه و Ctrl + O برای باز کردن یک صفحه جدید مورد استفاده قرار می گیرد.

از Ctrl + Shift + Colon برای واردکردن زمان و از Ctrl + Shift + Semicolon برای ورود تاریخ جاری سیستم استفاده می‌ شود.

Shift + F9 به منظور محاسبه ی کاربرگ فعال مورد استفاده قرار می گیرد.

Shift + Tab برای انتقال به سلول یا گزینه ‌ی قبلی استفاده می ‌شود.

Ctrl + Home جهت انتقال به ابتدای صفحه، مورد استفاده قرار می ‌گیرد.

Ctrl + Page Down برای انتقال به صفحه‌ ی بعد و Ctrl + Page Up برای حرکت به صفحه‌ ی قبل استفاده می‌ شود.

نکاتی پیرامون خطاهای یی ناشناخته از اکسل

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

علائم ظاهری این خطا در Excel و نحوه بیان آن از طرف شما

* هر بار که Excel را باز می‌کنم یک پیغام می‌دهد و با OK کردن نیز رفع نمی‌شود و بار دیگر نشان داده می‌شود.
* یک فلش آبی رنگ روی صفحه Excel می‌آید و اصلا انتخاب و پاک نمی‌شود.
* نتیجه فرمول SUM عدد صفر می‌شود و این در حالی است که من کلی عدد در محدوده Sum دارم.

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

توضیح Circular Reference در Excel

برای توضیح این خطا یک مثال ساده آورده می‌شود تا دقیقا بفهمید که چه بر سر Excel  می‌آید. فرض کنید که در یک اداره رفته‌اید و باید نامه‌ای امضا شود، آقای کارمند الف می‌گوید " نامه شما ابتدا باید به  تایید آقای جیم برسید " و وقتی که به اتاق آقای "جیم" می‌روید، می‌گوید "این نامه ابتدا باید توسط آقای الف کارشناسی و تایید شود".
و نتیجه این کار پاس‌کاری شما یا قرار گرفتن در یک "دور باطل" یا به قول ما کامپیوتری‌ها Loop خواهد بود که در Excel به این حالت Circular Reference می‌گویند.

 

Circular Reference در Excel چگونه ایجاد می‌شود

فرمولی را بنویسید که اشاره به خانه E2 داشته باشد (مهم نیست چه فرمولی ، اما در آن حتما E2 آمده باشد) سپس در خانه E2 فرمولی را بنویسید که اشاره به خانه B2 داشته باشد.
بعد زدن کلید Enter در سلول دوم Excel بلافاصله پیغام زیر را به شما نشان می‌دهد و اگر کلید OK را بزنید Help نرم افزار Excel باز شده و Circular Reference را توضیح می‌دهد.

 

البته حالت‌های دیگری نیز می‌توان مثال زد، مثلا شما در سلول B2 فرمولی مانند زیر را بنویسید. چون خود سلول در محدوده تابع SUM است در نتیجه Circular Reference ایجاد خواهد شد.

 

آوردن فلش آبی رنگ

بعد اینکه این حالت را ایجاد کردید، اگر خانه E2 را ویرایش کنید (روی E2 کلید F2 را بزنید و سپس Enter) این فلش به صورت خودکار نمایش داده می‌شود و بیانگر ایجاد  Circular Reference در Excel است.

 

خطای هنگام باز شدن این فایل

این فایل را ذخیره کنید و ببنید و سپس باز نمایید، مشاهده می‌کنید که در هر بار بازشدن فایل پیغام خطای Circular Reference نمایش داده می‌شود.

روش دیگری برای تشخیص Circular Reference

هموار در Status Bar شما می‌توانید آدرس اولین سلولی که دچارCircular Reference  شده است را ببنید. که به محض رفع کردن خطای این سلول ، آدرس سلولهای بعدی را نشان می‌دهد.

 

روش پیدا کردن همه Circular Referenceدر اکسل

از ریبون (به نوار ابزار Excel 2007 ریبون می‌گویند) گزینه Formula و سپس مانند شکل زیر اقدام نمایید:

 

نتیجه فرمولهایی که به سلول‌های Circular Reference اشاره دارند، همواره صفر است

در مثال زیر سلول C5 دچار CR شده است (فرمول آن در سلول کناری برای راهنمایی به صورت متنی کمرنگ نوشته شده) و همانطور که می‌بینید نتیجه سلول C9 که جمع سلولهای بالایی است عدد صفر شده است و این به دلیل خطای CR است.

 

تا اینجا با مفهوم Circular Reference در Excel آشنا شده‌اید و حال به نحوه رفع این خطای آشنا خواهیم شد.

روش رفع خطا در اکسل

دو راه کلی برای رفع این خطا وجود دارد که راه اول پاک کردن یا اصلاح فرمولی است که باعث CR شده است که سلولی که باعث CR شده است را ویرایش کرده و فرمول آنرا طوری اصلاح می‌کنیم که باعث Loop نشود.
راه حل دیگر فعال کردن گزینه Iteration در Excel Options است. در این وضعیت ما CR را کنترل می‌کنیم و نه حذف. بدین ترتیب که به Excel می‌گوییم که اگر در جایی دچار Loop شد، این Loop را 100 بار اجرا کن (این حلقه را 100  بار ادامه بده) و سپس کار را تمام کن.

برای فعال کردن وضعیت Iteration در Excel Options گروه Formula و سپس Enable iterative Calculation را در حالت فعال قرار می‌دهیم و تعداد دفعات اجرای را تعیین می‌کنیم.

 

استفاده از این تکنیک برای ساختن Loop در اکسل اصلا توصیه نمی‌شود زیرا شما باید Calculation رادر حالت Manual قرار دهید و ... و در ضمن ابزارهای کنترلی برای خارج شدن از Loop به سختی ساخته می‌شوند و این در حالی است که در VBA این کار به سادگی با دستوراتی مانند DO-LOOP  /  FOR-NEXT قابل اجراست و اگر شما در کتابهای مرجع Excel و یا اینترنت جستجو کنید خواهید دید که از این قابلیت سخنی به میان نرفته است و دلیل آن همان است که ذکر شد.

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

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

 

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

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

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

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

 

توابع IS و توابع ترکیبی IF در اشکال زدایی فرمول هادر excel

 توابع IS در اکسل شامل توابع ISBLANK، ISERR، ISERROR، ISLOGICAL، ISNA، ISNONTEXT، ISNUMBER، ISREF و ISTEXT میباشند. تمامی این توابع ساختار یکسانی دارند و خروجی آنها Boolean میباشد، توابع IS در اکسل تنها یک ورودی میگیرند و آن را برای شرط خاصی بررسی میکنند و خروجی تمام این توابع تنها TRUE یا FALSE میباشد.

توصیه میگردد حتماً مقاله ی مربوط به معرفی توابع IS را مطالعه نمایید.

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

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

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

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

=IF(OR(ISBLANK(A1);TYPE(A1)<>1);”Wrong Data, Number Only please”; “Let’s Do Next Step”)

در ساختار فرمول بالا از تابع OR (توابع منطقی در اکسل) و توابع TYPE و ISBLANK استفاده شده، به این ترتیب در صورتی که سلول A1 خالی باشد و یا داده ای غیر عددی در آن وارد گردد پیامی مبنی بر خطا بودن نوع داده ی ورودی به کاربر نشان داده میشود. اگر قرار بود عدد وارد شده در A1 به عنوان ورودی تابع POWER (توابع ریاضی) باشد، مسلماً غیر عددی بودن A1 باعث بروز خطا در فرمول میگردید، در چنین موردی ساختاری مانند ساختار زیر را میتوان استفاده کرد:

=IF(OR(ISBLANK(A1);TYPE(A1)<>1);”Wrong Data in A1″;POWER(A1;2))

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

همانطور که از مثال های بالا متوجه شدیم از ترکیب تابع IF و توابع IS و دیگر توابع اطلاعات در اکسل میتوان ساختارهای بسیار مفید در جلوگیری از ورود خطاها و داده های اشتباه در فرمول ها تشکیل داد. در این میان دو تابع ISERR و ISERROR از اهمیت ویژه ای برخوردارند، زیرا این دو تابع در نهایت میتوانند از نشان داده شدن پیام های خطا در فایل اکسل شما جلوگیری کنند و به عبارتی خطاها را به دام اندازند. ساختارهای زیر کمک میکنند که با استفاده از این توابع و تابع IF از نشان داده شدن پیام خطا در فایل های اکسل خود جلوگیری نمایید:

=IF(ISERROR(A1);”This Formula Contains Error”; “Acceptable Outcome”)

در فرمول نویسی حرفه ای در اکسل  میتوان به کاربر با توجه به نوع خطای اتفاق افتاده پیام متناسب داد، مثلاً در صورت بروز خطای N/A# در خروجی تابع VLOOKUPحتماً میتوان این نتیجه را گرفت که عبارت مورد نظر در جدول مورد جستجو وجود نداشته، این پیام را میتوان از ساختاری مشابه ساختار زیر به کاربر داد:

=IF(ERROR.TYPE(A1)=7;”The Required Item is not in Table”;””)

در فرمول بالا در صورت بروز خطای خطای N/A# پیامی برای کاربر نمایش داده میشود و او را در جریان قرار میدهد، حتی میتوان ساختار زیر را مستقیماً در سلول A1 استفاده کرد:

=IF(ERROR.TYPE(VLOOKUP(“Excelpro”;A2:C13;2;FALSE))=7;”The Required Item is not in Table”;VLOOKUP(“Excelpro”;A2:C13;2;FALSE))

در ساختار بالا عبارت Excelpro در محدوده A2:C13 جستجو میشود و در صورت پیدا نشدن پیامی متناسب به کاربر داده میشود.

 

استفاده از گروه Formula Auditing در اشکال زدایی فرمول ها در  اکسل

گروه Formula Auditing در تب Formulas برای رصد کردن وضعیت فرمول ها در اکسل مورد استفاده قرار میگیرد،

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

در صورتی‌که در حال کاربر روی یک فایل اکسل شامل سطرها و ستون‌های زیاد هستید، ممکن است نیازمند این شوید تا کلیه‌ی سلول‌های خالی موجود در فایل اکسل خود را شناسایی کرده و حذف کنید. این کار در اکسل به سادگی امکان‌پذیر است. بدین منظور:
۱- ابتدا محدوده‌ای که قصد دارید سلول‌های خالی آن حذف شود را به حالت انتخاب دربیاورید.
۲- اکنون کلید F5 را فشار دهید تا پنجره Go To نمایان شود.
۳- سپس بر روی دکمه Special کلیک کنید. در پنجره باز شده گزینه Blanks را انتخاب کرده و بر روی دکمه OK کلیک کنید. خواهید دید که با این کار تمامی سلو‌ل‌های خالی به حالت انتخاب درمی‌آیند.

۴- در پایان برای حذف این سلول‌های خالی کافی است در تب Home، در قسمت Cells بر روی گزینه Delete کلیک کنید.

 

 

---

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

روش اول) مشابه آموزش بالا یعنی حذف سلول‌های خالی در اکسل مراحل را تا مرحله ۳ انجام دهید. پس از اینکه تمام سلو‌ل‌های خالی به حالت انتخاب درآمدند هیچ کاری نکنید فقط کاراکتر موردنظرتان را تایپ کنید مثلا 0 یا Null.

 

پس از تایپ، کلیدهای Ctrl + Enter را همزمان فشار دهید. مشاهده خواهید کرد که تمام سلول‌های خالی با کاراکتری که تایپ کردید پر خواهد شد.

 

روش دوم) با استفاده از کد ماکرو

ابتدا محدوده‌ای که می‌خواهید سلول‌های خالی را با یک کاراکتر مشخص جایگزین کنید انتخاب کنید. سپس کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basicدر اکسل  ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.                                                                                   امید واریم از آموزش اکسل  امروز نیز لذت برده باشید

Data table و Simulation مونت کارلو در اکسل

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

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

 

حالا میخواهیم بدانیم اگر قیمت را دست کاری کنیم چه اتفاقی برای سود نهایی محصول می افتد؟ قیمت را تا چه پایین بیاوریم که سودمان منفی نشود و تا چه حد بالا ببریم که سود مورد نظر مدیریت حاصل شود؟

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

 

سپس کل محدوده F3:G24 را انتخاب نموده به تب Data بروید در گزینه What-if Analysis روی گزینه Data table کلیک نمایید.

 

سپس پنجره ای باز میشود که دارای دو قسمت است. چون جدولتان ستونی است قسمت Row را خالی بگذارید و در دومین قسمت یعنی Column Input Cell سلول مربوط به قیمت یعنی B3 را مشخص کنید:

 

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

 

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

حالا اگر بخواهید بدانید با همین قیمت ۱۶۸۰۰ تومان باید چه تعداد محصول به فروش برسانید تا به سود برسید می توانید مانند روش بالا محاسبات را انجام دهید.

 

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

نکته: اگر داده هایتان را بصورت افقی می نویسید در پنجره Data table باید Row Input Cell را تکمیل کنید و دومین گزینه را خالی بگذارید.

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

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

یعنی میخواهید بدانید چند درصد باید در مقدار یا قیمت تغییر ایجاد کنید تا سودتان تغییر کند. کاری ندارد:

فرض کنید میخواهیم اینکار را برای سلول مقدار یعنی B2 انجام دهیم. در یک سلول خالی مثلا D2 میزان درصد پیش فرض را صفر میگذاریم و سلول مربوط به عدد مقدار را اصلاح میکنیم و مینویسیم ۱۰۰۰*(۱+D2)

 

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

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

 

حالا کافیست به تی Data بروید روی گزینه what-If Analysis کلیک نمایید و گزینه Data table را انتخاب نموده و این بار هر دو فیلد Row input cell و Column input cell را تکمیل نمایید. برای قسمت اول عدد مرتبط با قیمت یعنی B3 و برای فیلد دوم سلول B2 را انتخاب نمایید و Ok  کنید. آنگاه تصویر زیر را خواهید دید.

 

همانطور که مشخص است با نغییرات مختلف در دو متغیر مقدار و قیمت، سود نهایی حاصل شده متفاوت است. مثلا در قیمت ۱۵۰۰۰ تومان و مقدار فروش ۷۵۰ عدد سودمان برابر صفر خواهد بود و ۷۵۱ امین فروش برایمان سود ایجاد خواهد کرد.

مطالبی پیرامون تغییر رنگ ردیف در جداول اکسل با Conditional Formatting

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

 

۲- در یک سمت اطلاعات جدول ستونی را برای استفاده از قابلیت Conditional Formatting به نام ” وضعیت” ایجاد کنید. 

 ۳- محدوده اطلاعات جدول ( به استثنای عناوین ستون‌ها) را انتخاب کنید. 

 ۴- ابزار Conditional Formatting  در اکسل را از مسیر Home > Styles باز کنید.   

 

 ۵- گزینه … New Rule  را انتخاب کنید. 

 

 ۶-  در قسمت Select a Rule Type گزینه Use a formula to determine witch celles to format را انتخاب کرده و در بخش پائین آن در قسمت Edit the Rule Description در باکس Format values where this formula is true عبارت ذیل را وارد نمائید.  

“B3=”F$=  

 

۷-  سپس بر روی گزینه Format کلیک کرده و در پنجره Format Cell تب Fill را انتخاب کرده و رنگ مورد نظر را برای حالت اول انتخاب می‌کنیم ( به طور مثال رنگ آبی را انتخاب می‌کنیم) 

 

 

۸-  سپس برای تغییر رنگ در حالت دوم همان کارهای مرحله ۶ را انجام داده و فقط به جای حرف F حرف D را استفاده می‌کنیم و در آخر مجدداً‌ مرحله ۷ را این بار برای حالت D انجام دهید. ( رنگ مورد نظر برای حالت D ،‌ را زرد انتخاب می‌کنیم)

“B3=”D$=

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

 

نکات مهم :

• در مثال فوق فرض کردیم F مخفف Final و D مخفف Draft می‌باشد.
• حرف B نشانگر ستونی است که مبنای تغییر رنگ ردیف است.
• عدد ۳ نشانگر اولین ردیفی است که باید تغییر رنگ ردیف از آنجا شروع شود.
• حرف F و D نشانگر وضعیت آیتم مورد نظر است. ( استفاده از عناوین برای این قسمت کاملاً‌ سلیقه‌ای است چنانچه در ستون وضعیت از عناوین خاصی استفاده می‌کنید بهتر است برای اطمینان از صحت انجام کار عنوان مورد نظر را کپی کرده و به جای حرف F و D استفاده کنید)

نکاتی پیرامون رسم نمودار و یافتن نقطه روی آن وترسیم میله های خطا ها دراکسل

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

نمودار خطی:

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

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

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

بعد از رسم نمودار برای نقطه یابی در جایی خارج از ستون داده ها روی یک سل کلیک کرده و دستور trend را اجرا میکنیم توجه کنید که اول علامت = رابزنید و بعد trendرا تایپ کنید در مرحله بعد ابتدا دو سلی از متغیر وابسته را که جوابمان بین انهاست انتخاب کرده و بعد علامت , بعد دو سل متناظر با این دو متغیر را انتخاب میکنیم دوباره علامت ,  بعد نقطه مورد نظر بین این دو سل اخر را تایپ میکنیم با زدن اینتر جواب مورد نظر بدست میاید.                                                                             ترسیم میله های خطا ها دراکسل                                                                                          

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

 

 

 

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

 

 

 

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

 

 

 

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

 

 

 

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

 

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

 

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

 

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

 

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

 

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

             

ایجاد فایل جدیددر اکسل

برای ایجاد یك فایل جدید یا یك Bookجدید به یكی از روشهای زیر عمل می كنیم .

روش اول : كلیك بر روی آیكون New

روش دوم: Ctrl + N (یك Book خالی ) Blank Work Book – Task Pam ظاهر می شود – New – File

بستن یك Book باز :

روش اول : كلیك بر روی آیكون Close موجود در انتهای نوار منو

روش دوم : File – Close

ممكن است با بستن یك فایل ظاهر شود كه می پرسد می خواهید فایل را ذخیره كنید یا نه اگر No را بزنید فایل را دخیره نمی كند . آن را می بندد. اگر Yes را بزنیم فایل را ذخیره می كند و آن را می بندد

خروج از محیط Excel :

1- كلیك بر روی آیكون Close موجود ردر نوار عنوان

2- File – Exit

3- Alt+f4
ذخیره كردن Book ایجاد شده
1- به یكی از سه روش زیر Save را انتخاب می كنیم :
الف) Icon Save

ب) File – Save

ج) Ctrl+ S
2- پنجره ای باز شده كه شامل قسمتهای زیر است :

الف ) در سمت چپ صفحه تابلوی دیده می شود كه برای دسترسی سریع به قستهای اصلی Win ، مثل My Computer , My Document, Desktop است. كافی است بر روی گزینه مورد نظر كلیك كرده تا در صفحه سفید رنگ مقابل محتویات آن پوشه را ببینیم .

ب) Save in : در این قسمت مسیر ذخیره مكردن فایل را مشخص می كنیم ، به طور پیش فرض فایلها در My Document ذخیره می شوند .

ج) File Name : نام فایل را تایپ می كنیم . به طور پیش فرض فایلها با اسامی Book1 ، Book2 و … ذخیره می شوند .

د) Create New Folder : ممكن است بخواهیم فایل ها را در پوشه جدیدی قرار دهیم برای این كار ابتدا یا Save in مسیر مورد نظر را انتخاب كرده سپس برروی این آیكون كلیك می كنیم . پنجره ای باز می شود و نام پوشه را می خواهد . نام پوشه را تایپ كرده و Ok می كنیم . پس از انجام تنظیمات بالا بر روی دكمه Save پنجره كلیك می كنیم .

 

ذخیره كردن Book ذخیره شده با نام دیگر در اکسل :

در منوی File علاوه بر Save گزینه save as هم وجود دارد. تفاوت این دو گزینه چیست ؟

برای بار اول ذخیره سازی یك Book این در هیچ تفاوتی با هم ندارند. بر روی هر كدام كه كلیك كنیم پنجره Save as باز می شود. ولی برای دفعات بعد (بعد از اینكه Book را ذخیره كردیم) اگر Save را انتخاب كنیم، دیگر پنجره ای باز نمی شود، و تغییرات بر روی فایل با همان نامی كه انتخاب كرده ایم ذخیره می شود ولی اگر Save as را انتخاب كنیم مجدداً پنجره Save as باز شده و می توانیم فایل را با نام دیگر یا در مسیر دیگر یا با نام دیگر و در مسیر دیگر ذخیره كنیم.

تذكر : وقتی فایلی را با نامی ذخیره می كنیم این فایل در نوار عنوان ظاهر می شود.

ذخیره كردن Book روی دیسكت:

برای ذخیره كردن یك فایل بر روی دیكست باید در پنجره Save as ، در قسمت Save in ، :3.5 floppy A را انتخاب كنیم .

ذخیره كردن Book با فرمت مناسب برای صفحات وب :

اگر بخواهیم فایل Excel  را با فرمتی ذخیره كنیم كه مانند یك صفحه وب باشد و بتوان آن را در Browser دید، كافی است مراحل زیر را انجام دهیم:

1- انتخاب منوی File

2- انتخاب گزینه as a web page save

نكته :

در این حالت فایل با پسوند .htm ذخیره می شود .
بازسازی Recovery Document
اگر Excel با مشكلی مواجه شود، در صدد ذخیره كردن كارپوشه های باز در هنگام برخورد با مشكل بر می آید در این حالت اگر دوباره اكسل را شروع كنیم پنجره Document Recovery باز شده و كلیه كارپوشه هایی كه باز بوده اند را نمایش می دهد. كافی است بر روی كارپوشه مورد نظر كلیك كرده تا باز شود. در مقابل نام كار پوشه دو عبارت ممكن است دیده شود :
1- [Recover] : اگر این عبارت نشان داده شده باشد به این معناست كه Excel موفق به ذخیره كردن فایل شده است .

2- [original] اگر این عبارت نشان داده شده باشد به این معناست كه این فایل ، فایل با آخرین نسخه ذخیره شده است. و Excel در هنگام بروز مشكل آن را ذخیره نكرده است زیرا یا بعد از آخرین ذخیره ، تغییری نكرده است و یا تغییرات انجام شده ولی نتوانسته ذخیره كند.

تذكر : ممكن است با هر دو عنوان فایل دیده شود می توانیم حالت Recovery را باز كرده و با فایل اصلی مقایسه كنیم و سپس ذخیره كنیم .
باز كردن صفحه ذخیره شده
می خواهیم فایلی را كه قبلا ذخیره كرده ایم دوباره بیاوریم برای این كار :
1- پنجره Open را به یكی ار روشهای زیر باز می كنیم :

الف : Icon Open

ب: File – Open

ج: Ctrl + o

2- پنجره ای باز می شود كه پنجره Openبوده و مشابه پنجره save بوده و شامل قسمتهای زیر است:

الف : Look in در این قسمت مسیری كه فایل را در آن ذخیره كرده ایم انتخاب می كنیم .

ب: Icon View : كلیك بر روی این Icon منویی ظاهر می شود كه حالتهای مختلف نمایش فایلها و پوشه های موجود در مسیر انتخاب شده را نمایش می دهد و شامل حالت های زیر است :

1- Thumbnails : این حالت مناسب فایلهای تصویری بوده و تصویر را نمایش می دهد .

2- Tiles : پوشه ها و فایلهای موجود را به صورت كاشی وار در دو ستون نمایش می دهد .

3- Icons : پوشه ها و فایلهای موجود را در 6 ستون نمایش می دهد.

4- List : پوشه ها و فایلهای موجود را در 15 سطر نمایش می دهد .

5- Details : پوشه ها و فایلهای موجود را با جزئیاتشان نمایش می دهد . در حالتهای قبل فقط نام فایلها نمایش داده می شود ولی در این حالت علاوه بر نام فایل ، سایز آن ، نوع و تاریخ ویرایش آن نمایش داده می شود .

6- Properties : با انتخاب این گزینه پنجره ی Open به دو تابلو تقسیم شده در تابلو سمت چپ نام فایل و پوشه ها و در تابلو سمت راست خصوصیات فایل مثل شخص ایجاد كننده آن ، تاریخ تغییرات ، نام كمپانی و … نمایش داده می شود .

7- Preview : پیش نمایش از فایل ، نمایش داده می شود .

نكته :

برای بعضی از فایلها با انتخاب این گزینه در تابلو سمت راست پیام Preview not available نمایش داده می شود . برای رفع این مشكل باید بر روی فایل باز عملیات زیر را انجام دهیم :

File – Properties – انتخاب Tab: Summary – Save preview picture – ok

پس از انجام عملیات بالا ، مجددا برروی آیكن Save ، كلیك كنید .

تذكر : در صورتی كه بخواهیم هر روز از كار پوشه ای استفاده كنیم و بخواهیم به طور خودكار با باز كردن Excel این كار پوشه باز شود و كافی است آن را در پوشه XL start قرار دهیم. این پوشه در هنگام نصب Excel ساخته می شود و مسیر آن یكی از دو مسیر زیر است .

C:/document and setting\ئی كه در آن هستیم User \application data \ یا

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

[ آخرین بازنگری ۱۶ مرداد ۱۳۸۹ ] [ نسخه چاپی ]

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

روش اول : بر روی سلول كلیك كرده و سپس در نوار فرمول كلیك كرده ویرایش را انجام می دهیم .

روش دوم : بر روی سلول Double كلیك كرده و ویرایش را انجام می دهیم.

ویرایش صفحات كاری :

انتخاب یك یا چند خانه :

1- توسط صفحه كلید :

– برای انتخاب یك خانه كافی است با مكان نما بر روی آن برویم .

– برای انتخاب تعدادی خانه مجاور كافی است ئكمه Shift را پایین نگه داشته و با مكان نما بر روی آن حركت كنیم .

– برای انتخاب سطر جاری كافی است كلیدهای shift + spacebar را بفشاریم .

– برای انتخاب ستون جاری كافی است كلیدهای Ctrl + Spacebar را بزنیم .

برای انتخاب كاربرگ جاری كافی است كلیدهای Ctrl + shift + spacebar را بزنیم و یا Ctrl + A را می زنیم .

2- توسط ماوس :

– برای انتخاب یك خانه كافی است با ماوس بر روی آن كلیك كنیم .

انتخاب یك یا چند خانه :

1- توسط صفحه كلید :

– برای انتخاب یك خانه كافی است یا جهت نما (Arrow Key ) بر روی آن برویم .

– برای انتخاب تعدادی خانه مجاور كافی است دكمه Shift را پایین نگه داشته و با كلیدهای جهت نما بر روی آن حركت كنیم .

– برای انتخاب سطر جاری كافی است كلیدهای Shift + Spacebar را بفشاریم .

– برای انتخاب یك ستون كافی است كلیدهای Ctrl + Spacebar را بزنیم .

– برای انتخاب كاربرگ جاری كافی است كلیدهای Ctrl + Shift + Spacebar را بزنیم.

2- توسط ماوس

– برای انتخاب یك خانه كافی است با ماوس بر روی آن كلیك كنیم .

– برای انتخاب تعدادی خانه مجاور كافی است دكمه سمت چپ ماوس را پایین نگه داشته و روی خانه ها Drag كنیم .

– برای انتخاب یك سطر كافی است روی شماره سطر كلیك كنیم .

– برای انتخاب یك ستون كافی است روی حرف ستون كلیك كنیم .

– برای انتخاب یك كاربرگ كافی است بر روی سلول * كلیك كنیم .

نكته :

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

دستور بازگشت در اکسل:

می توانیم عمل انجام شده را برگردانیم . فرض كنید در سلول A1 عدد 10 را می نویسیم و سپس آن را پاك می كنیم . اگر در این زمان از دستور Undo استفاده كنیم. عدد 10 بر می گردد. برای استفاده از Undo به یكی از روشهای زیر عمل می كنیم .

روش اول :

1- انتخاب منوی Edit

2- انتخاب Undo

روش دوم :

فشردن همزمان كلیدهای Ctrl + Z

روش سوم :

استفاده از آیكون Undo در نوار ابزار Standard

دستور Redo :

با این دستور می توانیم عمل Undo را برگردانیم . برای اجرای این دستور به یكی ار روشهای زیر عمل می كنیم :

روش اول :

1- منوی Edit

2- انتخاب Redo

روش دوم :

فشردن همزمان كلیدهای Ctrl + Y

روش سوم :

استفاده ار آیكون Redo در نوار ابزار Standard

 

نسخه برداری و انتقال سلولهادر EXCEL

نسخه برداری از سلولها :

1- موضوعات مورد نظر را انتخاب می كنیم .

2- به یكی از روشهای زیر Copy را انتخاب می كنیم

الف – استفاده ار آیكون كپی

ب- انتخاب منوی Edit ، گزینه Copy

ج – راست كلیك بر روی موضوع و انتخاب گزینه Copy

د- فشردن همزان كلیدهای Ctrl + C

3- كلیك بر روی مكانی كه می خواهیم اطلاعات اضافه شوند.

4- با یكی از روشهای زیر Paste را انتخاب می كنیم :

الف- استفاده از آیكون Paste

ب- انتخاب منوی Edit ، گزینه Paste

ج- راست كلیك بر روی موضوع و انتخاب گزینه Paste

د- فشردن همزمان كلیدهای Ctrl + V

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

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

1-انتخاب موضوعات مورد نظر

2- به یكی از روشهای زیر Cut را انتخاب می كنیم :

الف – استفاده از آیكون Cut

ب- انتخاب منوی Edit ‌، گزینه Cut

ج- راست كلیك بر روی موضوع مورد نظر و انتخاب گزینه Cut

د- فشردن همزمان كلیدهای Ctrl + X

3- كلیك بر روی محل انتقال موضوعات

4- به یكی از روشهای گفته شده در حالت قبل گزینه Paste را انتخاب می كنیم .

تذكر : وقتی كه Copy یا Cut را انتخاب می كنیم، كادر چشمك زن به دور سلولهای انتخابی دیده می شود تا زمانی كه این كادر وجود واشته باشد ، Paste امكان پذیر است. و زمانی كه این كادر چشمك زن از بین رفت عمل Paste انجام نمی شود . (تفاوت Excel با Word ) زمانی این كادر چشمك زن از بین می رود كه ما عمل دیگیر انجام دهیم .

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

1- انتخاب منوی Edit

2- انتخاب Office clipboard

3- در Task Pan منوی Clipboard ظاهر می شود و ما می توانیم در یك لحظه 24 مورد را در Clipboard نگه داریم و از آن استفاده كنیم .

4- موردی را كه می خواهیم Paste كنیم انتخاب می كنیم (در پنجره Clip Board)

5- در محل مورد نظر روی Sheet كلیك می كنیم . 6- Paste می كنیم .

اگر بخواهیم همه موضوعات موجود در Clip board یك باره Paste شود كافی است در برنامه Clipboard روی Paste Cell ، كلیك كنیم .

تذكر : اگر بر روی یك موضوع دوبار Ctrl+c را پشت سر هم بفشاریم . پنجره Clipboard باز می شود .

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

1-پاك كردن سلول و حذف سلول :

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

2-پاك كردن محتویات خانه ها :

روش اول :

1- خانه های مورد نظر را انتخاب می كنیم .

2- از منوی Edit ، گزینه Clear را انتخاب می كنیم .

3- زیر منویی باز می شود كه از آن Content را انتخاب می كنیم .

روش دوم :

1- سلول را انتخاب كرده

2- دكمه del صفحه كلید را می زنیم .

روش سوم :

1- بر روی سلول كلیك راست كرده

2- Clear Content را انتخاب می كنیم .

حذف سلول :

1- انتخاب سلول های مورد نظر

2- انتخاب Delete به یكی از روشهای زیر :

الف – Edit – delete

ب – R.C – delete

3- باید دقت داشته باشیم در این مرحله سلول ها حذف می شوند و در نتیجه به جای آنها حفره ایجاد می شود كه باید سلولهای اطراف جایگزین این فضای خالی شوند. پس برای پر كردن فضای خالی در این مرحله متونی ظاهر شده كه شامل گزینه های زیر است.

الف – Shift Cell Left :

باعث انتقال خانه سمت راست خانه پاك شده به جای آن می شود .

ب- Shift Cells Up :

خانه زیرین خانه پاك شده را بجای آن منتقل می كند .

ج- Entire Row :

سطر زیرین خانه پاك شده را به جای سطری كه خانه پاك شده در آن قرار دارد منتقل می كند.

د- Entire Column :

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

نكته :

اگر محیط فارسی بجای ستون سمت راست ، ستون سمت چپ جایگزین می شود .

مطالب فوق العاده پیرامون كلیدهای میان بر قالب بندی متن و چاپ در اکسل

كلیدهای میان بر قالب بندی متن

تغییر قلم F+Shift+‍Ctrl

تغییر اندازه قلم P+Shift+‍Ctrl

پررنگ كردن قلم B+‍Ctrl

ایتالیك كردن قلم I+‍Ctrl

زیر خط دار كردن قلم U+‍Ctrl

دو زیر خط دار كردن قلم D+Shift+‍Ctrl

حذف قالب بندی كاراكتر Space Bar+‍Ctrl

فضای خطوط تنها پاراگراف 1+‍Ctrl

فضای دو برابر خطوط پاراگراف 2+‍Ctrl

فضای 5/1 برابر خطوط پاراگراف 5+‍Ctrl

تراز بندی وسط پاراگراف E+‍Ctrl

تراز بندی چپ پاراگراف L+‍Ctrl

تراز بندی راست پاراگراف R+‍Ctrl

تراز بندی از دو طرف پاراگراف J+‍Ctrl

برجسته كردن پاراگراف از چپ M+‍Ctrl

برجسته كردن پاراگراف ازراست M+shift+‍Ctrl

حذف قالب بندی پاراگراف Q+‍Ctrl

به روز رسانی فیلدها F9

تغییر روش نمایش فیلد منتخب Shift+F9

تغییر روش تمام فیلدهای سند Alt+F9

كاهش یا افزایش اندازه قلم به اندازه یك رقم [+Ctrlا]

تغییر اندازه قلم به اندازه پیش فرض قبلی یا بعدی <+Ctrl+Shiftا>

تبدیل حروف عادی به حالت دو زیر خط و برعكس D+Shift+‍Ctrl

تبدیل حروف عادی به اندیس دار پایین و برعكس =+Ctrl

تبدیل حروف عادی به اندیس دار بالا و برعكس =+Ctrl+Shift

تبدیل ابتدای كلمات به حروف كوچك یا بزرگ AK+shift+‍Ctrl

تغییر قلم جاری F+Shift+‍Ctrl

تغییر قلم جاری F+Shift+‍Ctrl

تغییر اندازه قلم جاری F3+Shift

ایجاد تو رفتگی از سطر بعدی P+Shift+‍Ctrl

حذف تو رفتگی سطر بعدی +T‍Ctrl

حذف تو رفتگی سطر دوم T+Shift+‍Ctrl

انتخاب همه سند A+‍Ctrl

درج یك شكستگی سطری Enter+Shift

انتقال به سطر یا صفحه بعدی Ctrl+Enter

اشكار یا پنهان نمودن علایم H+Shift+‍Ctrl

كلیدهای میان بر صدا در اکسل

خاموش/روشن نمودن میكروفن Winkey+V

انتقال بین دو حالت دیكته و فرمان صوتی Winkey+T

فعال كردن حالت تصحیح Winkey+C

كلیدهای میان بر منوهادر EXCEL

فعال شدن اولین عنوان نوار منو ALT

فعال شدن منوی Alt + F File

فعال شدن منوی Alt + E Edit

فعال شدن منوی Alt + V View

فعال شدن منوی Alt + I Insert

فعال شدن منوی Alt + O Format

فعال شدن منوی Alt + T Tools

فعال شدن منوی Alt + A Table

فعال شدن منوی Alt + W Windows

فعال شدن منوی Alt + H Help

چاپ در excel :

امکانات چاپ در excel یکی از قوی ترین و قدرتمند ترین امکانات چاپ در مجموعه office است . در excel کنترل کامل شما روی محدوده چاپ بسیار جذاب بوده و احساس راحتی به شما می دهد. حتما” در word تجربه کرده اید که محدوده چاپ شما همان صفحه سفید رنگی است که در اختیار شما قرار داردو این شما هستید که باید طوری با فونتها و فواصل باز ی کنید تامطلب مورد نظرتان در یک صفحه جا شود ولی در excel مرزهای چاپی هر صفحه را به راحتی میتوانید تعیین کنید. این مرزهای چاپ هستند که خود را با محدوده صفحاتی که شما تعیین میکنید وفق میدهند.

میتوانید هر صفحه را بایک ضریبی از اندازه آن چاپ کنید . در این صورت ممکن است فونت شماره 10 شما در اندازه فونت 14 چاپ شود.

از منو view گزینه Page Break Preview را انتخاب کنید تا محدودههای چاپ در قالب خطوط مرزی آبی رنگ مشخص شود . هر چه در این محدوده قرار بگیرد چاپ خواهد شد. با رنگ توسی کمرنگ در داخل صفحه شماره صفحه نوشته می شود که فقط محض اطلاع شماست و چاپ نمیشود , میتوانید اشاره گر ماوس را روی این مرزهای آبی رنگ ببرید و با تغییر شکل ماوس آنرا جابجا کنید. همیچنین با انتخاب یک سطر یا یک ستون به کمک گزینه Page Break از منوی Insert میتوانید یک صفحه جدید برای چاپ ایجاد کنید. خطوط ابی رنگ نمایش داده شده به وسیله خط چین خطوطی هستند که خود excel به شما برای چاپ پیشنهاد داده است و خطوط نهایی مورد نظر شما نیستند.

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

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

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

 بر روی دکمه Tools و سپس General Options کلیک کنید.

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

بعد از تایید (OK) پنجره قبلی، لازم است در کادر بازشده جدید مجدداً همان پسورد را وارد نمایید.

فقط توجه کنید که رمز را فراموش نکنید وگرنه شاید مجبور شوید فایل را هم به کلی فراموش کنید. : )

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

 

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

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

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

چسباندن چند متن با کاراکتر &در excel

 برای اتصال چند عبارت در سلول باید از کاراکتر & استفاده کنیم.

تابع LEFT هم برای استخراج کاراکترهایی که در سمت چپ یک متن هستند استفاده میگردد. مشابه همین رفتار را برای تابع RIGHT داریم.

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

 

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

 

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

 

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

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

 

منوها در نسخه‌های ۲۰۰۷ و ۲۰۱۰ اکسل، به صورت روبان (ribbon) هستند. روی هر منو که کلیک کنید، روبان مربوطه باز می‌شود. اگر روبان Data را انتخاب کنید، در بخش Data Tools می‌توانید روی Data Validation یا همان "اعبتارسنجی داده‌ها در اکسل " کلیک کرده و نوع و دامنه‌ی داده‌ی ورودی در خانه‌‌ی مربوطه از  اکسل را تعیین کنید.

 

منوی مربوط به Data Validation سه tab دارد. tabهای دوم و سوم به ترتیب برای تنظیم پیغام ورودی و هشدار خطا می‌باشند که در صورت تمایل می‌توان پیغام‌های لازم را در آنها درج کرد.

مثلا اگر نوع داده را از نوع list انتخاب کنید و در بخش source مقادیر ممکن برای ورودی را تایپ کنید، هنگام ورود اطلاعات با یک لیست محدود از داده‌ها روبرو خواهید شد.

 

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

 

از Data Validation می‌توان برای تعریف انواع دیگر داده نیز استفاده کرد. مثلا اگر در یک خانه‌ی اکسل قرار است طول قد بر حسب سانتی‌متر وارد شود، می‌توان تنظیم کرد که داده‌ی ورودی به صورت یک عدد صحیح بین ۱۰۰ تا ۳۰۰ باشد. در این صورت اگر به اشتباه عدد ۱.۸۰ (بر حسب متر) وارد شد، کاربر با پیغام خطا در EXCEL مواجه شود و این امکان را به دست می‌آورد که داده‌اش را تصحیح کند و ادامه‌ی محاسبات‌اش دچار اشکال نشود.                                                                                                                             امیواریم از آموزش اکسل امروز نیز لذت برده باشید 

مطالبی پیرامون ویژگی ها و ترفند های جالب کادر Find and Replace در اکسل

درنرم افزار اکسل  از  Find and replace فقط برای جستجوی کلمات یا عبارت و جابجایی آن با موارد مد نظرمان استفاده می کنیم. اما یکی از کاربرد های جالب این بخش ، استفاده از فرمت سلول ها و تغییر آن با حالات مورد نظر می باشد.بطور مثال فکر کنید در یک کاربرگ اکسل در سلول های مختلف نوع داده - فونت - رنگ - چینش - خطوط برداری و ... خاصی مورد استفاده قرار گرفته ،جستجوی یک به یک برای یافتن این سلول ها و اعمال تغییرات روی آنها ،کار زمان برو دشواری است ، در حالی که امکان بروز اشتباه در حجم بالای کار بسیار فروان می باشد.ما در این آموزش قصد انجام موارد بالا را با استفاده از Find and replace در اکسل  به راحتی داریم. ابتدا تغییری را در سلول های انتخاب شده انجام می دهیم . برای نمونه در اینجا با انتخاب بازه ای از خانه ها فرمت آنها را از حالت پیش فرض General به نوع Time تغییر می دهیم. سپس کادر Find and replace را از سربرگ Home و یا با فشردن کلید های ترکیبی Ctrl+H باز نموده و از سربرگ Replace روی گزینه Option کلیلک می نماییم. در کادر Find what فرمتی را که قرار هست مورد جستجو قرار گیرد ،از قسمت Format انتخاب می کنیم ، برای سهولت در این کار می توانید از گزینه Choose Format From Cell استفاده کنید ، بدین صورت که شکل ماوس به حالت یک مداد به همراه علامت مثبت تغییر شکل می دهدو ما یک بازه یا سلول را به عنوان نمونه ، جهت جستجو انتخاب می نماییم. سپس در کادر Replace Whit فرمت جدیدی را که مد نظرمان هست انتخاب می کنیم. در اینجا ما نوع داده Text را انتخاب می کنیم.پس از انجام اعمال مورد نظر پیغامی مبنی بر اینکه موارد جستجو و جابجایی انجام شده نمایان می شود و بر روی دکمه OK کلیک نمایید

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

پاک کردن فرمت های انتخاب شده دراکسل
در پایان نکته قابل ذکر پاک کردن فرمت های انتخاب شده بوسیله گزینه Clear Replace Format در کادر Find and replace می باشد. که در موارد بعدی باز کردن این پنجره و اعمال فرمت های جدید با مشکل خاصی روبرو نشوید.

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

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

 توابع حذف کاراکتر‌های اضافی
 CLEAN : حذف تمام کاراکتر‌های غیر قابل چاپ.
 TRIM : حذف فاصله‌های اضافی و فاصله‌ها در آغاز و پایان عبارت.

 توابع تبدیل حروف کوچک و بزرگ LOWER : تمامی کاراکتر‌ها را به حروف کوچک تبدیل می‌کند.
 PROPER : کاراکتر‌های موجود در یک متن را به حالت استاندارد تبدیل می‌کند.
 UPPER : تمامی کاراکتر‌ها را به حروف بزرگ تبدیل می‌کند.

 تبدیل حروف به اعداد به یکدیگر
 CHAR : تبدیل کد ASCII به حروف.
 CODE : تبدیل حروف به کد ASCII.

 جایگزین کردن قسمتی از یک رشته متن
 REPLACE : جای کاراکتر مورد نظر کاراکتر دیگری را قرار می‌دهد.
 SUBSTITUTE : جای عبارت درج شده در یک جمله عبارت دیگری را قرار می‌دهد.

 توابع دستیابی به قسمتی از یک رشته CONCATENATE : به هم چسباندن دو یا چند متن.
 LEFT : نمایش ‌تعداد حروف سمت چپ سلول مورد نظر.
 MID : نمایش بخشی از یک کلمه.
 RIGHT : نمایش ‌تعداد حروف سمت راست سلول مورد نظر
 REPT : تکرار عبارتی خاص را به تعداد مشخص.

 توابع اطلاعاتی 

 LEN : شمارش تعداد کاراکترهای موجود در یک سلول.
 FIND : پیدا کردن حروف مورد نظر خود در یک عبارت(حساس به کوچکی و بزرگی).
 SEARCH : پیدا کردن حروف مورد نظر خود در یک عبارت.
 EXACT : مقایسه عبارت موجود در دو سلول.
 T : تشخیص متن بودن.

 توابع تبدیل انواع داده‌ها در اکسل
 VALUE :
 TEXT :
 FIXED :

توابع منطقی 

 توابع عملگر بولی
 AND : گزاره‌های مختلف را چک کرده و در صورت درست بودن تمامی آنها مقدار درست و در غیر این صورت مقدار نادرست را برمی‌گرداند.
 OR : گزاره‌های مختلف را چک کرده و در صورت درست بودن یکی آنها مقدار درست و در غیر این صورت مقدار نادرست را برمی‌گرداند.
 NOT : اگر مقدار‌های وارد شده با هم برابر باشند مقدار نادرست و اگر نابرابر باشند مقدار درست را برمی‌گرداند.

 توابع شرطی
 IF : تابع شرطی.
 IFERROR : طرح یه معادله و درصورت اشتباه بودن پیغام مورد نظر به نمایش در بیاید.

 توابع بازگرداننده مقادیر ثابت منطقی
 TRUE : مقدار True را برمی‌گرداند.
 FALSE : مقدار False را برمی‌گرداند.

 توابع اطلاعاتی

 توابع اطلاعات خطا
 ISERROR : اگر مقدار داده شده بدون خطا باشد مقدار نادرست را بازمی‌گرداند.
 ERROR.TYPE : به جای نمایش error یک عدد نمایش می‌دهد.

 توابع اطلاعاتی عددی
 ISNUMBER : تشخیص عدد بودن یا متن بودن.
 ISEVEN : تشخیص زوج بودن.
 ISODD : تشخیص فرد بودن.
 N : تبدیل هر عبارتی به عدد.

 توابع دیگر انواع داده
 ISBLANK : تشخیص خالی بودن یک سلول.
 ISLOGICAL : اگر جواب یک معادله درستی یا نادرستی بود عبارت true را نمایش می‌دهد د ر غیر این صورت عبارت false را نشان می‌دهد.
 ISTEXT : شناسایی توابعی که دارای متن می‌باشد.
 ISNONTEXT : مخالف تابع ISTEXT می‌باشد.
 ISREF : مشخص می‌کند که عبارت وارد شده یک محدوده از سلول ها است یا خیر.
 TYPE : مشخص کرد در داخل هر سلول چه نوع کاراکتری وجود دارد.
 (text، number، error value و ...)

 تابع اطلاعات عمومی
 CELL : هر نوع اطلاعاتی که در مورد سلول مورد نظر بخواهیم در اختیار ما قرار می‌دهد.
 INFO : نمایش مشخصات سیستم.

 توابع بازگرداننده مقادیر ثابت
 NA : نمایش پیغام خطای #N/A .

توابع تاریخ و زمان

 توابع تاریخ و زمان
 DATE : تاریخ مورد نظر.
 TIME : زمان مورد نظر.

 توابع تاریخ و زمان فعلی
 NOW : نمایش تاریخ و زمان جاری سیستم.
 TODAY : نمایش تاریخ جاری سیستم.

 توابع سازنده زمان
 HOUR : از زمان فقط ساعت را نمایش می‌دهد.
 MINUTE : از زمان فقط دقیقه را نمایش می‌دهد.
 SECOND : از زمان فقط ثانیه را نمایش می‌دهد.

 توابع سازنده تاریخ
 DAY : از تاریخ فقط روز را نمایش می‌دهد.
 MONTH : از تاریخ فقط ماه را نمایش می‌دهد.
 YEAR : از تاریخ فقط سال را نمایش می‌دهد.
 WEEKNUM : مشخص می‌کند که در چندمین هفته از سال قرار داریم.
 WEEKDAY : مشخص می‌کند که در چندمین روز از هفته قرار داریم.

 توابع محاسباتی تاریخ
 EDATE : تاریخ را به عقب یا جلو ببریم.
 EOMONTH : مشابه تابع EDATE ولی با این تفاوت که وقتی تاریخ را به جلو می‌بریم آخرین روز آن ماه را به نمایش در می‌آورد.
 WORKDAY : شمارش روزهای کاری.
 WORKDAY.INTL : کاری مشابه با تابع WORKDAY را انجام می‌دهد، با این تفاوت که روزهای تعطیل را خودمان مشخص می‌کنیم.
 DAYS360 : شمارش روز‌های بین دو تاریخ.
 NETWORKDAYS : شمارش روز‌های کاری بین دو تاریخ.
 NETWORKDAYS.INTL : شمارش روز‌های کاری بین دو تاریخ.
 (روزهای تعطیل را خودمان مشخص می‌کنیم.)

 توابع آدرس دهی و جستجو

 توابع آدرس دهی به سلول
 ADDRESS : آدرس دهی در اکسل.
 INDEX : برای ارجاع از یک یا چند سلول استفاده می‌شود.
 INDIRECT : با وارد کردن یک رشته آن را تبدیل به یک مرجع سلول می‌کند.
 OFFSET : تعیین محدوده‌ای از سلول ها می‌باشد.

 اطلاعات سطر، ستون و محدوده
 ROW : مشخص می‌کند در چندمین ردیف قرار داریم.
 COLUMN : مشخص می‌کند در چندمین ستون قرار داریم.
 ROWS : همانند تابع ROW می‌باشد، ولی با این تفاوت که شما می‌توانید مقدار چندین سلول را وارد نمایید.
 COLUMNS : همانند تابع ROWS عمل می‌کند ولی با این تفاوت که مجموعه ردیف‌ها را شمارش می‌کند.
 AREAS : وظیفه شمارش محدوده‌ها را دارد.

 توابع جستجوی داده
 HLOOKUP : به صورت افقی به دنبال عبارت مورد نظر می‌گردد.
 VLOOKUP : به صورت عمودی به دنبال عبارت مورد نظر می‌گردد.
 LOOKUP : در یک مجموعه از داده‌ها به دنبال یک مقدار می‌گردد
 CHOOSE : در یک لیست به صورت رشته شما با وارد کردن یک عدد رشته مورد نظر در لیست را به نمایش در می‌آورد.
 MATCH : موقعیت نسبی موردی را در یک آرایه مشخص می‌کند.

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

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

 اطلاعات اولیه اعداد
 ABS : محاسبه عملیات قدر مطلق .
 SIGN : تشخیص مثبت یا منفی بودن عدد.
 GCD : بزرگترین مقسوم علیه دو یا چند عدد را بدست می‌آورید
 LCM : کوچکترین مضرب مشترک دو یا چند عدد را نمایش می‌دهد.

 عملگرهای ریاضی در excel
 SUM : محاسبه عملیات جمع.
 POWER : عدد اول را به توان عدد دوم می‌رساند.
 SQRT : محاسبه جذر عدد مورد نظر.
 QUOTIENT : استفاده از این تابع می‌توان قسمت صحیح تقسیم دو عدد را بدست آورد.
 MOD : محاسبه باقیمانده دو عدد.
 AGGREGATE : مجموعه چندین تابع.
 SUBTOTAL : مجموعه چندین برنامه کاربردی.

 توابع گرد کننده در اکسل
 CEILING : عدد ورودی اول را به ضریب بزرگتر یا مساوی عدد ورودی دوم در میاورد.
 EVEN : عدد را به اولین عدد بزرگ و زوج تبدیل می‌کند.
 FLOOR : عدد ورودی اول را به ضریب کوچکتر یا مساوی عدد ورودی دوم در میاورد.
 INT : عدد مورد نظر را گرد می‌کند و به صورت یک عدد صحیح نمایش می‌دهد.
 MROUND : عدد ورودی اول را به ضریب نزدیکترین عدد ورودی دوم در میاورد.
 ODD : تبدیل عدد به اولین عدد فرد.
 ROUND : گرد کردن اعداد.
 ROUNDDOWN : وظیفه گرد کردن در یک محدوده از تعداد رقم اعشار یا صحیح به سمت پایین را دارد.
 ROUNDUP : وظیفه گرد کردن در یک محدوده از تعداد رقم اعشار یا صحیح به سمت بالا را دارد.
 TRUNC : مقدار اعشار یا عدد صحیحی که می‌خواهیم نمایش بدهیم.

 جمع شرطی SUMIF : جمع اعداد با شرایط مورد نظر.

 عملیات‌های پیشرفته ریاضی در اکسل پیشرفته
 SUMPRODUCT : برای ضرب کردن اعداد استفاده می‌شود.
 SUMSQ : به توان دو رساندن اعداد.
 SUMX2MY2 : این تابع که علاوه بر این که عددها را به توان دو می‌رساند دو به دو از هم کم می‌کند و نتیجه را با هم جمع می‌کند.
 SUMX2PY2 : این تابع که علاوه بر این که عددها را به توان دو می‌رساند دو به دو با هم جمع می‌کند و نتیجه را با هم جمع می‌کند.
 SUMXMY2 : اعداد مورد نظر را اول از هم کم می‌کند و سپس به توان دو می‌رساند.
 SERIESSUM : مجموعه سری توانی.

 توابع ماترسی
 MDETERM : محاسبه دترمینال ماتریس.
 MINVERSE : معکوس یک ماتریس مربعی.
 MMULT : محاسبه ضرب دو ماتریس.

 محاسبه فاکتوریل
 FACT : محاسبه فاکتوریل.
 FACTDOUBLE : ضرب اعداد بین دو عدد مورد نظر( اگر عدد وارد شده فرد باشد فقط اعدا فرد را ضرب می‌‌کند و بالعکس).
 MULTINOMIAL : محاسبه فرمول

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

 

 اعداد تصادفی RAND : یک عدد تصادفی بین صفر تا یک
 RANDBETWEEN : یک عدد تصادفی بین دو عدد مورد نظر ما

 توابع مثلثات
 PI : نمایش عدد پی.
 SQRTPI : عدد مورد نظر را در عدد π ضرب می‌کند و سپس جذر آن عدد را نمایش می‌دهد.
 DEGREES : تبدیل زاویه برحسب رادیان به درجه.
 RADIANS : تبدیل درجه را به رادیان.
 COS : محاسبه مقدار کسینوس یک عدد.
 ACOS : محاسبه آرک کسینوس.
 COSH : محاسبه کسینوس هیپر‌بلیک.
 ACOSH : محاسبه آرک کسینوس هیپر‌بلیک.
 SIN : محاسبه سینوس یک عدد.
 ASIN : محاسبه آرک سینوس.
 SINH : محاسبه سینوس هیپر‌بلیک.
 ASINH : محاسبه آرک سینوس هیپر‌بلیک.
 TAN : محاسبه تانژانت.
 ATAN : محاسبه آرک تانژانت.
 TANH : محاسبه تانژانت هیپر‌بلیک.
 ATANH : محاسبه آرک تانژانت هیپر‌بلیک.

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

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

1-سلول یا منطقه ی مورد نظررا انتخاب می کنید .

2-در  اکسل ،روی نشانه برش (cut)،کلیک می کنید در این هنگام به دور منطقه ی انتخاب شده مرز متحرک ظاهر می شود که نشان میدهد ،منطقه ی مورد نظر بریده شده است.  

 

 

 

3-در سلول مورد نظر برای انتقال کلیک کنید تا انتخاب شود.

4-روی نشانه چسباندن (paste) در نوار ابزار اکسل ،کلیک کنید.منطقه ی سلول مورد نظر،در مکان جدید چسبانده واز جای قبلی ناپدید می شود. 

 

   

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

 

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

برای این کار، به روش زیر عمل کنید:

1-سلول یا منطقه مورد نظر را انتخاب کنید.

2- در نوار ابزار اکسل، روی نشانه برش (copy)، کلیک کنید. در این هنگام، به دور منطقه انتخاب شده مرز متحرکی ظاهر میشود که نشان میدهد منطقه مورد نظر، در تخته برش(clipboard) ویندوز ، قرار داده شده است 

 

3- دور سلول مورد نظر برای انتقال ، کلیک می کنید تا انتخاب شود.

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

 

مرجع مطلق و مرجع نسبی در اکسل   

    

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

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

 

   

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

برای اینکه مرجع مطلق رابه اکسل بشناسانیم بایداز علامت (($))استفاده کنیم.

علامت دلاررا درنشانی سلول یامنطقه،می توان پیش ازحرف نشانی،پیش از

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

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

 

اگرفرمول مثلابه این شکل واردشود	وقتی فرمول به جای دیگری کپی شوداتفاق زیرمی افتد.
C1	هم ستون وهم سطرعوض می شود.
C1$	ستون عوض نمی شودولی عرض عوض می شود.
C$1	ستون عوض می شودولی سطرعوض نمی شود.
C$1$	ستون وسطر،هیچ یک عوض نمی شوند.

 

 

شمامی توانید درحین وارد کردن نشانی سلول یامنطقه،مستقیماّ علامت $راتایپ کنیدویا می توانید پس ازتایپ آن،با زدن کلیدf4نشانی رابه صورت مطلق درآورید.

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

 

  

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

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

 

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

1 – در تمرین قبل ، در سلول B3 ، کلمه فروش را تایپ کنید که با سلول پایین آن فروش فروردین خوانده شود .

2 – اکنون دستگیره ی پرکردن سلول B3 را بکشید و تکمه ی ماوس را رها کنید . چه اتفاقی افتاد ؟ شرح دهی

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

 ( Drag & Drop)در ویرایش داده ها در excel

 

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

برای جابه جا کردن داده ها (مشابه بریدن و چسباندن)باید به طریق زیر عمل کنید:

1-منطقه مورد نظر را با ماوس یا صفحه کلید،انتخاب کنید.

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

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

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

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

 

«لغو کردن» [Undo] و «انجام مجدّد 

 عمل [Redo] در اکسل  

 

اکسل ، هر کاری را که شما انجام می دهید، یادداشت می کند. بنابراین همیشه می داند که در اثر کدام عمل شما ،چه تغییری در کار برگ حاصل شده است.پس،همیشه می توانید آخرین عمل خود را لغو کنید و به محض انجام کار، همه چیز به یک مرحله قبل بر میگردد.به این خصوصیّت اکسل «لغو عمل»[Undo] می گویند.

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

بنابراین ، اگر شما پشیمان شدید و خواستید عملی را که لغو کرده بوده اید دوباره انجام دهید،می توانید این کار را از طریق خصوصیّت«انجام مجدّد عمل»[Redo] صورت دهید. نشانه های «لغو عمل» و«انجام مجدّد عمل» ،در نوار ابزار استاندارد اکسل قرار گرفته است. 

 

    

در منوی Edit هم فرمان UndoوRedo وجود دارد

چاپ   

اکسل می تواند به سادگی کاربرگ شما را چاپ کند . اما همیشه توصیه می شودذ پیش از چاپ چیزی را که میس خواهید چاپ شود ، پیش نگری (Preview) کنید .

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

برای پیش نگری چاپ  با ماوس روی کلید « پیش نگری » یا همان Preview print در نوار ابزار استاندارد کلیک کنید . بالافاصله کاربرگ به صورتی که چاپ خواهد شد در صفحه ، نمودار خواهد شد .  

  

  

 

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

 

  

 

در بالای پنجره پیش نگری چند نشانه ی دیگر هست که بر نحوه ی چاپ یا پیش نگری ، تاثیر می گذارد . و بدین شرح اند .  

  نشانه ی Next ( صفحه بعدی ) : با کلیک کردن روی آن ، به صفحه ی قابل چاپ بعدی خواهیم رفت .

  نشانه ی Previous    ( صفحه ی قبلی ) :  با کلیک کردن روی این نشانه ، به صفحه ی قابل چاپ قبلی خواهیم رفت .

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

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

  نشانه   Close  ( بستن ) : این نشانه پنجره ی پیش نگری چاپ را می بندد .

  نشانه ی Help   ( کمک ) : با زدن این نشانه ، امکسل در زمینه ی چاپ به شما کمک می کند .  

 

 

مشخص کردن نحوه ی چاپ

 

یکی از نشانه های منوی پیش نگری preview، که Page set up   است ، منویی با همین نام را بر صفحه ظاهر می کند که از طریق آن نیز برخی از پارامترهای چاپ ،   تعیین می شود . در اینجا به آن منو پرداخته می شو

 

تعیین جهت صفحه:

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

 

 

 

تعیین دقیق حاشیه های چاپ:

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

 

  

 

اکنون میتوانید، اندازه حاشیه ها را در منوهای با عنوان top(بالا)،bottom (پایین)،left(چپ)،right(راست) تعیین کنید. اندازه حاشیه سر صفحه و پا صفحه را نیز میتوانید با تعیین عدد دقیق در زیر عنوان header(سر صفحه) وfooter (پا صفحه) تعیین کنید. 

 

 

  

وسط در وسط کردن منطقه چاپی:  

 

وقتی چیزی در صفحه چاپ میشود از سمت راست حاشیه چپ تراز میشود. اگر میخواهید فاصله راست و چپ چیزی که چاپ میشود از گوشه راست و چپ صفحه به یک فاصله باشد، در قسمت center on pageاز زبانه margins ، گزینه horizotally(به طور افقی) را انتخاب کنید. اگر میخواهید فاصله بالا و پایین صفحه با آنچه در آن چاپ میشود مساوی باشد، در همین زبانه ، از منو گزینه vertically (به طور عمودی) را انتخاب کنید. بلافاصله در شکل کوچک موجود در منو ، تاثیر این انتخاب ها دیده خواهد شد و در صفحه چاپی نیز ، به هنگام چاپ اعمال خواهد شد.  

 

 

 

تعیین سر صفحه و پا صفحه چاپی: 

 برای تعیین سر صفحه و پا صفحه چاپی در منوی page set up ، زبانه header/footer را فعال میکنیم .  

 

  

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

شما می توانید یکی از این گزینه ها را انتخاب کنید ویا،ااگر هیچ یک را نپذیرفتید،خودتان سر صفحه وپا صفحه تیین کنید.برای این کار،دو کلید در این منو موجود است که یکی CUSTOM HEADER (سر صفحه ی سفارشی)ودیگریCUSTOM FOOTER (پا صفحه سفارشی)ست.هر یک از این دو کلید را بزنید                   

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

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

 

  

 

از چپ به راست ،این نشانه ها به شرح زیر عمل می کنند:

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

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

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

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

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

 وقتی این نشانه را کلیک کنید،کد &(FILE) به سر صفحه یا پا صفحه اضافه می شود.در هنگام چاپ به جای این کد،نام فایل چاپ خواهد شد.

با زدن این نشانه ،کد &(TAB) به سر صفحه یا پا صفحه افزوده می شود ودرهنگام چاپ ،نام کاربرگ به جای آن چاپ می شود.  

 

 

 

 

 

 

 

  تکرار سطر ها و ستون ها در نسخه 

 های  چاپی 

 

بیشتر وقت ها، کاربرگ بزرگ است ودر چند صفحه کنار هم چاپ می شود.در این مواقع،ممکن است بخواهید برخی سطر ها و ستونها در تمام نسخه های چاپی از کلیه ی مناطق کاربرگ چاپ شود. برای این کار از زبانه ی چهارم منوی page setup که زبانه ی sheet(صفحه ) است استفاده می کنید .  

 

 

 

در این منو بخشی بنام print titles وجود دارد که در شکل بالا مشخص است .دراین بخش دو گزینه داریم که یکی rowz to repeat at top(سطرهایی که در بالا تکرار می شوند) و دیگریcolumns to repeat at edge(ستونهایی که در لبه تکرار می شوند )است.

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

1-پس از فعال کردن این بخش از منو در خانه ی مقابل عنوان rowz to repeat at top[ کلیک میکنید تا فعال شود .

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

3-در این منو روی سطر هایی که میخواهید در بالای هر صفحه ی چاپی تکرار شود کلیک می کنید.میتوانید یک یا چند سطر را انتخاب کنید.

4-اکنون روی تکمه کناری این منو که در شکل بالا نشان داده شده است کلید می زنید تا کل منو دوباره ظاهر شود

5-برای گزینه ی columns to repeat at edge نیز به همین ترتیب کار میکنید .

6-با استفاده از print preview حاصل کار را می بینید و اگر مناسب تشخیص دادید چاپ میکنید . 

 

 

 

مشخص کردن ترتیب چاپ

 

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

1-      در منوی page set up زبانه ی sheetرا فعال میکنید .

2- در بخشpage order دو انتخاب است:اگر مقابل گزینه یdown then over(اول ستون ،بعد سطر )را بزنید ، ابتدا کاربرگ به شکل عمودی می برد و بعد ،هر ستون را تکه تکه چاپ میکند و به سراغ ستون بعد می رود .حال ،اگر گزینه ی،over then down(اول سطر ،بعد ستون ) را انتخاب کنید ، کاربرگ را به شکل افقی تکه تکه میکند و بعد از چاپ هر تکه ،ان را جداگانه چاپ می کند. 

 

 

 

چاپ منطقه ی خاصی از یک کاربرگ

 

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

1 – در منوی page set up زبانه ی sheetرا فعال کنید .

2 – حالا در خانه ی مقابل عنوان print areae ( محدوده ی چاپ ) را کلیک کنید تا فعال شود . 

3 – روی نشانه ی پنهان کردن موقت منو ، در کگوشه ی راست ، خانه ی کذکور ، کلیک کنید تا منو موقتا پنهتان شود  .

4 – حالا منطقه ی مورد نظر را با ماوس یا صفحه کلید ، انتخاب کنید و مجدداً  روی همان نشانه کلیک کنید .

5 – با زدن نشانه ی   Preview print نتیجه را ببینید .

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

مطالبی درمورد تنظیمات اکسل و چاپ سرستون‌ها در تمام صفحات در اکسل

دو مورد از مواردی که در بهبود عملکرد ما در استفاده از فایلهای اکسل مهم میباشد را میتوان در حذف و کوتاه کردن فعالیتهای تکراری و خلاصه سازی و استاندارد سازی فعالیتها نام برد .
لذا موارد زیر عمدتا ارتباطی به یک فایل خاص ندارد و در همه فایلها یعنی نرم افزار اکسل تنظیم میگردد .
۱- فعال کردن autorecovery : کاربرد ان در مواقعی میباشد که میخواهیم اطلاعات ما اگر ذخیره نشده است و بنا بدلایلی برق کامپیوتر قطع گردید اطلاعات قابل دسترس باشد

۲-سایز و نوع فونت پیش فرض: فونتی را که تمایل دارید در اکثر قسمتهای فایل از ان استفاده نمایید میتوانید از اینجا تنظیم نمایید
مسیر : option >general > use this fon & font size
۳- تعداد sheet در یک فایل : اگر شما نیاز دارید در یک فایل جدید بیش از مثلا ۳ شیت داشته باشید میتوانید این تنظیم را انجام دهید .
مسیر : option >general > include this many sheets
۴- ایجاد لیست اتومات در اکسل : اگر نیاز دارید لیستهای را درست نمایید که با درگ کردن یکی از عناوین گزینه های دیگر لیست در سلولهای بعدی اتومات تایپ شود از مسیر زیر اقدام نمایید .
مسیر : option >advance>edit custom list >add>import
۵- autocorrect: بسیاری از فرمولها و متون طولانی را میتوان برای آن کلید واژه ای تعریف و سپس با استفاده از کلیدواژه آن فرمول و یا متن فوق را در صفحه ایجاد مینماید .
مسیر : option >proofing>auto correct option
۶- دستی کردن اتومات : اگر فایل شما سنگین شده است و نیاز دارید اطلاعات وارد نمایید در حالی که نیاز به روز شدن محاسبات ندارید میتوانید محاسبات را در حالت دستی قرار دهید . و با f9 فرمولها را به روز رسانی کنید .
مسیر : option >formula > manual
۷- save tempelate: در صورتی که فرمهایی را به دفعات زیاد نیاز دارید. میتوانید آن را در فرمت تمپلت ذخیره کنید
مسیر : file> save as>excel tempelate
۸ – نمایش خطا : در صورتی که میخواهید پیشنهاداتی در زمینه امکان خطاهای موجود دریافت کنید میتوانید نمایش و رنگ اخطار ان را در مسیر زیر نمایش دهید .
مسیر : option>formula> error cheking>enable background
۹- تنظیم جهت حرکت سل انتخابی: اگر میخواهید با هر بار اینتر زدن سلول انتخاب شما به جای پایین فرضا به سمت راست حرکت نماید مسیر زیر را تنظیم نمایید .
مسیر : option>advance>edit direction>right
۱۰- خطای امنیت ماکروها در اکسل: در صورتی که ماکرویی را در کامپیوتر دیگر ذخیره کرده باشید به صورت پیش فرض در کامپیوتر شما خطا میدهد .                                                                                                                                                                                                                            چاپ سرستون‌ها در تمام صفحات در اکسل

اگر در EXCEL بر روی فایلی کار کنید که دارای سرستون (Header) باشد و تعداد صفحاتی که قصد چاپ کردن آن‌ها را دارید بیش از 1 صفحه‌ی کاغذ باشد حتماً متوجه این نکته شده‌اید که سرستون تنها در صفحه‌ی اول چاپ می‌شود و در صفحات بعدی تنها اطلاعات هستند که چاپ می‌شوند. به عنوان مثال اگر فایلی شامل اطلاعات شخصی افراد در اختیار داشته باشید و در سطر اول عنوان‌های هر ستون درج شده باشد (به عنوان مثال «نام»، «نام خانوادگی»، «شماره ملی» و...)، این عناوین در صفحات بعدی چاپ نخواهند شد. این موضوع چندان مناسب به نظر نمی‌رسد. در این ترفند قصد داریم روشی را بازگو کنیم که از این پس می‌توانید به آسانی سرستون‌ها را در تمام صفحات چاپ کنید.

بدین منظور:
ابتدا  فایل مورد نظر را فراخوانی کنید.
اکنون به تب Page Layout رفته و در محدوده‌ی Page Setup بر روی Print Titles کلیک کنید.
در پنجره‌ی باز شده در قسمت Rows to repeat at top عبارت زیر را وارد کنید:

$1:$1

دقت کنید با این کار تنها سطر اول به سرستون انتخاب شده و در تمام صفحات تکرار می‌شود. اگر سطور دیگری مد نظرتان است می‌توانید بر روی دکمه‌ای که در روبروی این قسمت قرار دارد کلیک کرده و سطر مورد نظر خود را انتخاب کنید.
هم‌چنین اگر به جای سرستون دارای سرسطر هستید (یعنی عناوین به جای سطر در ستون قرار گرفته باشند) در همین پنجره بایستی قسمت Columns to repeat at left را تکمیل کنید.
با کلیک بر روی Print Preview نیز می‌توانید پیش‌نمایشی از چاپ با این نوع تنظیم را مشاهده کنید.
در نهایت بر روی دکمه‌ی OK کلیک نمایید و اقدام به چاپ فایل کنید.

teb developer>macro security > macro>seeting>enable all macro

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

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

در حالت خوش بینانه فایل اکسل ما شامل پنج شیت است که چهار شیت اول آن ببیانگر اطلاعات فصلی با اسامی Bahar، Tabestan، Paiez و Zemestan هستند (در حالی که در عموم مواقع هر فایل اکسل بیان گر یک ماه و هر شیت بیان گر یک روز از آن ماه است و تعداد شیتها در این حالت برابر با ۳۰ شیت خواهد شد) و درامد حاصله در هر فصل در  شیت مربوط به آن فصل ثبت شده باشد و درآمد کل هر فصل در سلول E2 آن شیت محاسبه شده باشد و بخواهیم سلول E2 در تمامی این شیتها را در سلول D3 از شیت پنجم به اسم FINAL محاسبه نماییم.

درحالت مبتدی، از آدرس دهی بین شیتها استفاده میشود و از فرمول ذیل درون سلول D3 استفاده میشود.

=Bahar!E2+ Tabestan!E2+ Paiez!E2+ Zemestan!E2

 

در این مثال شاید کار دشواری نباشد ولی در حالتی که فایل اکسل شامل ۳۰ شیت باشد، نوشتن فرمول به صورت فوق بسیار سخت و ناامید کننده خواهد بود، مخصوصا اگر بخواهیم به دفعات آن را تغییر دهیم.

اما در حالت پیشرفته از فرمول نویسی سه بعدی استفاده میکنیم.همانطور که علامت “:” در آدرس دهی درون شیت به معنی تا است و عبارت E2:E10 به معنی سلول E2 تا E10 میباشد، در آدرس دهی سه بعدی میتوان از این علامت به منظور آدرس دهی بین چند شیت استفاده نمود به گونه ای که عبارت Bahar:Zemestan به معنی شیت بهار و شیت زمستان و تمامی شیتهایی که بین این دو شیت قرار دارند می‌باشد، لذا عبارت

=SUM(Bahar:Zemestan!E2)

به معنی آن است که سلول E2 در شیت Bahar و Zemstan و هر شیت دیگری که بین این دو شیت قرار دارند جمع شوند. لذا به جای تابع قبلی به سادگی میتوان از تابع فوق استفاده نمود.

 

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

=SUM(Bahar:Zemestan!E:E)

 

گامهای آدرس دهی سه بعدی

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

 

1. بر روی سلولی که میخواهیم تابع را محاسبه نماییم رفته و عبارت =SUM( را وارد نموده.
2. سپس بر روی اولین شیت (شیت Bahar) کلیک کرده تا در قسمت فرمول بار فرمول به صورت =SUM(Bahar! نمایش داده شود.
3. بدون آن که بر روی سلولی کلیک کنید دکمه Shift از صفحه کلید را نگه داشته و بر روی آخرین شیت (شیت Zemestan) کلیک نموده تا فرمول در فرمول بار به صورت =SUM(Bahar:Zemestan! نمایش داده شود.
4. با رها کردن کلید شیف بر روی سلول E2 کلیک کرده تا فرمول به صورت =SUM(Bahar:Zemestan!E2 در بیاید و در نهایت با بستن پرانتز و زدن اینتر فرمول تکمیل میگردد.                                                                                                                    فرمت اعداد در اکسل                                                                                                                
   

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

    منظور بعد از انتخاب سلول یا محدوده اعداد، میتوان از تب Home تنظیمات مربوط به نحوه نمایش اعداد را انجام داد ولی بهتر است بعد از انتخاب محدوده بر روی یکی از سلولها راست کلیک کرده و گزینه Format Cell را انتخاب نموده (یا از صفحه کلید دکمه Ctrl+1 را زده) و سپس از تب Number فرمت مد نظر را انتخاب نموده. در این قسمت حالت های ذیل وجود دارد:

    

   General

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

    

   Number

   برخلاف حالت General در این حالت میتوان تنظیمات مربوط به تعداد رقم اعشار، جدا کننده سه رقم سه رقم اعداد و نحوه نمایش اعدا منفی را نیز انجام داد بدین صورت که در صورت تیک زدن چک باکس Use 1000 List Separator، در صورت بزرگتر از ۱۰۰۰ بودن عدد، ارقام آن سه رقم سه رقم جدا میشود
   

   به همین صورت عدد ثبت شده در قسمت Decimal Place بیانگر تعداد اعداد اعشاری عدد خروجی میباشد، برای مثال اگر در این قسمت عدد ۲ وارد شود، در صورتی که کاربر در آن سلول مقدار ۱۲۴ را وارد نماید خروجی به صورت ۱۲۴٫۰۰ نمایش داده میشود).

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

    

   Currency

   در حالت Currency علاوه بر تنظیمات مربوط به قسمت Number میتوان نماد پولی مد نظر را نیز از قسمت Symbol انتخاب نمود بدین صورت که با انتخاب کردن نماد ریال از این قسمت، در صورتی که کاربر عدد ۱۲۵ را درون سلول ثبت نماید، مقدار ۱۲۵ ریال درون سلول نمایش داده میشود.

    

   Accunting

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

    

   Date

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

    

   Time

   این قسمت مربوط به تنظیمات نحوه نمایش زمان (ساعت) است بدین صورت که نحوه نمایش به صورت ۲۴ ساعته باشد یا ۱۲ ساعت و با علامتهای AM/PM یا این که به صورت تجمیعی نمایش داده شود. در این قسمت بعد از انتخاب گزینه Time کافی است از بین حالاتپیشنهادی در قسمت Type حالت مد نظر را انتخاب نماییم.

    

   Percentage

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

   Fraction دراکسل
   

   فرمت Fraction به منظور نمایش اعداد به صورت کسر مورد استفاده قرار میگیرد بدین صورت که اگر بخواهیم عدد ثبت شده درون یک سلول را به جای ۰٫۵ به صورت ۱/۲ نمایش دهیم کافی است بعد از انتخاب آن سلول، از قسمت تنظیمات اعداد حالت Fraction را انتخاب نماییم.

    

   Scientific

   Scientific یا نماد علمی به منظور نمایش اعداد بسیار بزرگ یا بسیار کوچک مناسب میباشد. در نماد علمی اعداد به صورت مضربی از توان n ام ۱۰ نمایش داده میشود بدین صورت که تنها یک رقم قبل از اعشار نگه داشته میشود و سایر ارقام بعد از اعشار نمایش داده میشوند، و عدد حاصل در توان مثبت یا منفی از ۱۰ ضرب میشود برای مثال عدد ۱۲۵۶ به صورت نماد علمی به شکل ۱٫۲۵x10³ نمایش داده میشود.

   
   Text

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

    

   Special

   این حالت تنظیمات عمومی برای کدپستی یا Id-Number می‌باشد ولی فرمت تعریف شده مناسب برای کشور ایران نیست.

    

   Custome

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

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

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

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

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

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

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

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

اصطلاح: به اشاره گر ماوس در حالت auto fill, fill hand گفته می شود.

ایجاد لیست جدید خودكار دراکسل :

اگر برای حروف الفبا 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 ، آدرس سلولهایی كه لیست در آنها قرار دارد را تایپ كنیم .
ویرایش لیست خودكار
می توانیم لیستهایی را كه ایجاد كرده ایم ویرایش كنیم . یعنی اطلاعاتی را حذف كنیم . ویرایش كنیم یا اضافه كنیم . برای این كار مراحل زیر را انجام می دهیم :
1- انتخای منوی Tool

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

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

4- انتخاب لیست مورد نظر در كادر Custom list

5- كلیك در محل مورد نظر در كادر Entries List

6- ویرایش متن

7- OK

پاك كردن لیست خودكاردر excel :
اگر بخواهیم یك لیست خودكار راحذف كنیم تا دیگر پر كردن خودكار بر روی آن عمل نكند مراحل زیر را طی می كنیم :

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

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

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

4- انتخاب لیست مورد نظر در كادر Custom list

5- انتخاب گزینه delete

6- Ok

نكته :

لیست های آماده شده توسط email قابل حذف نیستند.

غیر فعال كردن Auto fill :

Auto fill بطور خودكار فعال است . جهت غیر فعال نمودن آن مراحل زیر را طی می كنیم:

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

2- انتخای منوی Options

3- انتخاب Tab: edit

4- كادر انتخاب Allow cell drag and drop را غیر فعال می كنیم .

5-ok

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

 

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

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

 

 

 

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

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

 

 

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

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

 

 

 

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

 

 

 

ايجاد فرم ورود اطلاعات

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

 

 

 

 

 

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

 

 

 

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

 

 

 

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

 

 

 

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

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

 

 

 

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

 

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

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

 

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

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

 

 

 

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

 

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

 

 

 

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

 

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

 

 

 

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

 

 

 

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

 

 

 

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

 

 

 

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

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

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

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

 

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

 

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

 

 

 

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

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

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

 

 

 

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

 

 

 

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

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

 

 

 

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

 

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

 

 

 

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

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

 

 

 

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

 

 

پرینت اطلاعات در اکسل و تنظیمات آن

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

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

 

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

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

 

 

 

حذف ناحيه چاپ

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

 

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

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

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

 

 

 

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

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

 

 

 

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

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

 

 

 

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

 

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

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

 

 

 

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

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

 

 

 

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

 

 

 

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

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

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

 

 

 

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

 

 

 

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

 

خطوط راهنما

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

 

 

 

تغيير اندازه كاغذ

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

 

 

 

 

 

پيش نمايش چاپ

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

 

 

 

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

 

 

 

چاپ برگه

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

 

 

 

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

 

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

 

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

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

 

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

کاراکتر جدا کننده پارامترها در توابع اکسل به صورت پیش فرض کاراکتر “;” یا semicolon هست. بعضی مواقع پیش می آید  که در سیستم ما این کاراکتر چیزی غیر از semicolon تعریف شده است . در این حالت دراکسل چون به کاراکتر “;” عادت کردیم این مساله خیلی آزار دهنده میشود . این مشکل به خصوص زمانی پیش می آید  که تنظیمات علائم در سیستم عامل روی زبان فارسی باشد.

 

به عنوان مثال در بعضی از سیستم ها  که هنگام نصب سیستم عامل تنظیم زبان اونها روی گزینه Persian بوده این کاراکتر به جای “;” روی کاراکتر “؛” تنظیم شده که خیلی باعث اشتباه میشه. برای تغییر کاراکتر جدا کننده پارامترها در توابع اکسل به کاراکتر دلخواه خودمون میتونیم از روش زیر استفاده کنیم.

ابتدا کنترل پانل سیستم رو باز میکنیم و مثل تصویر زیر روی گزینه Change date, time or number formats کلیک میکنیم.

 

در پنجره ای که باز میشه روی گزینه Additional settings…‎ کلیک میکنیم.

 

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

 

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

قابلیت های جدید اضافه شده به اکسل

1. شش مدل نمودار جدید

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

نمودار آبشاری:

 

نمودار هیستوگرام:

 

نمودار پارتو:

 

نمودار Box & Whisker:

 

نمودار Treemap:

 

نمودار Sunbrust:

 

2. قابلیت پیشرفته استفاده از Query

 

در اکسل 2016 Addon قدرتمند Power Query به هسته اصلی نرم افزار اکسل اضافه شده و به کاربر امکان اتصال به دیتابیس های مختلف و ورود اطلاعات از انواع مختلف دیتابیس ها مثل MySQL را میدهد.

3. افزایش امکانات پیش بینی

 

در اکسل 2016 برخلاف نسخه های قبلی اکسل که تنها قابلیت پیش بینی خط را داشتند، امکان پیش بینی نمایی و … نیز وجود دارد و توایعی برای این منطور به کتابخانه توابع اکسل اضافه شده است . همینطور ابزار one-click forcasting جهت نمایش پیش بینی داده ها به اکسل اضافه شد ه است . در این ابزار امکان تعیین پارامترهای پیش بینی مثل تغییرات فصلی، دامنه اطمینان و … هم وجود دارد.

4.نقشه سه بعدی

 

یکی از Addon های قدرتمند اکسل در نسخه های قبلی این نرم افزار PowerMap بود که امکان نمایش داده ها رو به صورت جغرافیایی داشت. حالا در نسخه جدید اکسل این Addon که نام اون به 3D Map تغییر کرده جزئی از هسته اصلی نرم افزار اکسل شده و به صورت پیش فرض در اختیار کاربر قرار گرفته است .

5. بهبود در ابزار Pivot Table

 

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

• شناسایی و گروه بندی خودکار فیلدهای تاریخ
• امکان جستجو در فیلدها
• اضافه شدن دکمه هایی جهت نمایش جزئیات بیشتر در PivotChart
• شناسایی خودکار رواط بین جداول مختلف موجود در Pivot Table

6. امکان ایجاد slicer با قابلیت انتخاب همزمان چند آیتم در excel

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

 

7. جستجوی سریع گزینه های نرم افزار با استفاده از قابلیت Tell me

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

 

به عنوان مثال با وارد کردن عبارت waterfall پزینه های مربوط به رسم نمودار آبشاری نمایش داده میشود :

 

8. جستجوی اطلاعات درباره موارد دلخواه در اینترنت

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

 

9. قابلیت نوشتن معادلات توسط قلم و ماوس دراکسل

 

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

10. بهبود اشتراک گذاری فایل

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

 

11. امکان دسترسی به تغییرات قبلی فایل ها

 

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

12. تم های جدید

 

در نسخه جدید اکسل سه تم به نام های Colorful، Dark Gray و White وجود داره که میتوانید بسته به سلیقه خود از آنها استفاده کنید.

13. ارسال اطلاعات به Power BIدر اکسل

 

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

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

وقتی كار پوشه ای را باز می كنیم . كاربرگها حاوی نامهای پیش فرض Sheet 1, Sheet 2 , … هستند . برای نامگذاری Sheet ها می توان تا حداكثر 21 كاراكتر استفاده كرد . به جز كاراكترهای / . \ . * . ؟ . [ . ] بقیه كاراكترها مجاز می باشند .

 

تغییر نام Sheet ها :

روش اول :

1- انتخاب Sheet

2- انتخاب منوی Format

3- انتخاب گزینه Sheet

4- انتخاب گزینه Rename

روش دوم :

1- راست كلیك بر روی Sheet

2- انتخاب گزینه Rename

روش سوم :

دابل كلیك بر روی Sheet

 

 

اضافه كردن Sheet ها :

روش اول :

1- انتخاب Sheet كه می خواهیم Sheet جدید قبل از آن قرار گیرد.

2- انتخاب منوی Insert

3- انتخاب گزینه Work Sheet

4- OK

روش دوم :

1- راست كلیك بر روی Sheet هایی كه می خواهیم Sheet جدید قبل از آن قرار گیرد .

2- انتخاب گزینه Insert

3- انتخاب گزینه Work Sheet

4- Ok

 

 

حذف Sheet ها  در اکسل :

روش اول :

1- انتخاب Sheet

2- انتخاب منوی Edit

3- انتخاب كزینه Delete Sheet

روش دوم :

1- راست كلیك بر روی Sheet

2- انتخاب گزینه Delete

 

انتقال Sheet ها در excel
روش اول :
1- انتخاب Sheet

2- انتخاب منوی Edit

3- انتخاب گزینه move or copy sheet

4- در پنجره باز شده در كادر Before sheet نام Sheet هایی را كه می خواهیم این Sheet قبل از آن قرار گیرد انتخاب می كنیم یا اگر می خواهیم به انتها انتقال دهیم ، Move to end را انتخاب می كنیم .

5- OK

روش دوم :

1- راست كلیك بر روی Sheet

2- انتخاب گزینه Move or Copy

3- در پنجره باز شده در كادر Before Sheet نام Sheet یی را كه می خواهیم این Sheet قبل از آن قرار گیرد انتخاب می كنیم .

4- OK

 

 

كپی كردن Sheet ها در یك پوشه :

مانند انتقال Sheet ها می باشد فقط كافی است در انتها در كنار Create a copy تیك بزنیم .

 

 

انتقال یک Sheet از book هائی به book دیگردر EXCEL :

1- راست كلیك بر روی Sheet

2- انتخاب گزینه move or copy sheet

3- در كادر To Bookنام فایل مورد نظر را كه می خواهیم Sheet را به آن انتقال دهیم انتخاب می كنیم .

4- در قسمت Before Sheet هم نام Sheet هایی راكه می خواهیم Sheet انتقالی قبل از آن قرار گیرد ، انتخاب می كنیم .

5- OK

 

 

كپی كردن Sheet های از یك كار پوشه به كار پوشه دیگر :

تمام مراحل ماند انتقال است فقط در كادر Create a copy تیك می زنیم .

 

 

تذكر : باید توجه داشته باشیم كه Book ئی كه می خواهیم فایل را به آن منتقل یا كپی كنیم حتما باز باشد .

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

می توانیم اعمال ریاضی ، منطقی و دیگر توابع را در Excel داشته باشیم . برای استفاده از توابع Excel به یكی از روشهای زیر عمل می كنیم :
1- بر روی ، Paste function در خط فرمول ، كلیك می كنیم .

2- در پنجره ی باز شده در قسمت Select a category نوع تابع را مشخص می كنیم . كلیه ی توابع در 8 دسته طبقه بندی شده اند و در دسته ی All همه توابع قرار دارند ( اگر گروه تابع را ندانیم می توانیم آنرا در All پیدا كنیم ) و در دسته ی Most Recently used توابعی كه به تازگی از آنها استفاده شده است قرار دارند .

8 گروه طبقه بندی شده عبارتند از :

– Financial : برای توابع

– Date & Time : برای توابع مربوط به تاریخ و زمان

– Math & trig : برای توابع مربوط به ریاضی

– Statistical : برای توابع مربوط به آماری

– Lookup & Reference : برای توابع جستجویی و توابعی كه نیاز به جداول مرجع دارند .

– Database : برای توابع مربوط به پایگاه داده

– Text : برای توابع متنی

– Logical : برای توابع منطقی

– Information : برای توابع اطلاعاتی

3- در قسمت Select a function تابع را مشخص می كنیم .

4- در پنجره ای كه باز می شود به دو طریق می توانیم عمل كنیم :

الف ) در كادر Number1 ، محدوده ی آدرس اعداد را وارد می كنیم . برای وارد كردن محدوده ی آدرس كافی است ابتدا آدرس اولین خانه ی محدوده را وارد كرده ، سپس عملگر آدرس ( : ) را تایپ كنیم و بعد آدرس آخرین سلول محدوده را وارد می كنیم . همچنین می توانیم بر روی Collapse Dialog این كادر كلیك كرده و محدوده را با Drag انتخاب كنیم .

ب ) در هر كادر یك آدرس وارد كنیم . برای این كار در كادر اول آدرس اولین سلول را تایپ كرده بر روی كادر دوم كلیك كرده ، آدرس دومین سلول را تایپ می كنیم و الی آخر … معمولا زمانی از این روش استفاده می كنیم كه سلولها پراكنده باشند .

5- بر روی دكمه ی OK كلیك می كنیم .

روش دوم :

1- منوی Insert را انتخاب می كنیم.

2- زیر منوی Function را انتخاب می كنیم.

3- بقیه مراحل مانند مرحله 2 به بعد روش اول است.

روش سوم:

1- در خانه تابع را همراه با آرگومانهای آن تایپ می كنیم. ( توابع و آرگومانهایش در قسمت بعد توضیح داده شده اند. )

2- اگر قبل از آن = گذاشته باشیم كه Enter می كنیم در غیر این صورت بر روی = در نوار فرمول Click می كنیم.

روش چهارم:

1- در یك خانه (خانه ای كه می خواهیم حاصل نوشته شود) علامت = را تایپ می كنیم یا علامت = در خط فرمول را می زنیم.

2- در كادر اول نوار فرمول ( Name Box ) توابع ظاهر می شوند، كافی است بر روی drop down آن Click كنیم تا همه توابع را ببینیم. تابع مورد نظر را انتخاب می كنیم.

3- بقیه مراحل مانند مرحلة 4 به بعد روش اول است.

 

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

 

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

الف : Sum-number1:number2

ب : Sum-number1;number2;…

نكته :

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

2- تابع میانگین Average در اکسل  :

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

الف : Average-number1;number2;…

ب : Average -number1:number2

3- تابع ماكزیمم (Max) :

از این تابع برای پیدا كردن ماكزیمم چند مقدار استفاده می كنیم . این تابع نیز جزء گروه Statistical است و شكل كلی آن به صورت زیر است :

الف : Max-number1;number2;…

ب : Max-number1:number2

4- تابع مینیموم (Min) :

از این تابع برای محاسبه ی میانگین اعداد استفاده می شود و جزء توابع گروه Statistical می باشد . شكل آن به صورت زیر است :

الف : Min-number1;number2;…

ب : Min-number1:number2

5- تابع شمارنده (Count) :

از این تابع برای شمارش تعداد خانه های پر شامل اعداد استفاده می شود . این تابع نیز جزء توابع گروه Statistical می باشد و فرم كلی آن بصورت زیر است :

الف : Count-value1;value2;…

ب : Count-value1:value2

6- تابع Round :

از این تابع برای گردكردن اعداد استفاده می شود و جزء توابع گروه Trig & Math می باشد و شكل كلی آن بصورت زیر است :

Round -&number;num_degits

قسمت Number برای معرفی عدد و قسمت Num_degitis معرف تعداد ارقامی است كه باید گرد شوند ، Excel هنگام گرد كردن نگاه به اولین رقم حذفی می كند اگر كوچكتر از 5 بود كه خود عدد را می نویسد ولی اگر بزرگتر از 5 بود یك رقم به آخرین رقم باقیمانده اضافه می كند.

Auto sum : توسط این دستور می توانیم تعدادی خانه را با هم جمع كنیم . در این حالت كافی است در خانه ای كه می خواهیم جواب درآن نوشته شود ، كلیك كرده و سپس بر روی آیكن Autosum از نوار ابزار Standard كلیك كنیم . Excel خانه های در محدوده ی خانه ی انتخابی برای جواب را در نظر می گیرد و مجموع آنها را حساب می كند و خانه های انتخاب را به ما نشان می دهد

راه های استفاده ازاکسل برای حسابداری

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

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

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

صفحات گسترده:

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

 داده های خارجی:

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

ادغام و یکپارچه سازی:

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

کاربرد فرمت مشروط در اکسل

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

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

 

 

حال میخواهیم مبلغ سفارشاتی که بیشتر از ۵۰۰۰۰ تومان هست را آبی کنیم.

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

 

سپس در تب Home گزینه Conditional Formatting را انتخاب می کنیم و از زیر منوی آن ، گزینه New Rule را می زنیم.

 

حال از پنجره ظاهر شده گزینه دوم را در قسمت  Select a Rule Type انتخای میکنیم.

 

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

 

between : بین این دو عدد

Not between  : بین این دو عدد نباشد

Equal To : دقیقا مساوی با این عدد

Not Equal To: مساوی با این عدد نباشد

Greater than: بزرگتر از این عدد

Less than : کمتر از این عدد

Greater than or equal to : بزرگتر و مساوی این عدد

less than or equal to : کوچکتر و مساوی این عدد

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

با توجه به مثال ما ، گزینه greater than را انتخاب نموده و عدد ۵۰۰۰۰ را در باکس جلوی آن می نویسیم. (همانطور که در مثال گفته شد میخواهیم سفارشات بالای ۵۰۰۰۰ تومان را به رنگ آبی در بیاوریم.) و همچنین در قسمت Format ، فرمت خروجی را تنظیم می کنیم. که در این مثال میخواهیم باکس های بالای ۵۰۰۰۰ تومان به رنگ آبی در بیایند.

 

 

 

 

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

 

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

فرمولهای برداری یکی از ویژگی های قدرتمند اکسل است و استفاده از فرمولهای برداری باعث می‏شود که توانایی شما در انجام محاسبات پیچیده, افزایش یابد.
مفهوم بردار
یک بردار در واقع همان مجموعه ای از مقادیر داده مرتبط با هم است که در انجام محاسبات از آنها استفاده می‏شود.
هر دامنه از سلولهای انتخاب شده در واقع یک بردار می‏باشند و بسته به ابعاد ستونها و ردیفها بردار انتخاب شده , یک بعدی یا دو بعدی می‏باشد. (در اکسل از بردارهای سه بعدی پشتیبانی نمی‏شود)

فرمولهای برداری در اکسل می‏توانید به دو صورت چند خانه ای و تک خانه ای مورد استفاده قرار دهید.
فرمول برداری چند خانه ای
در شکل زیر برای محاسبه مبلغ فروش هر جنس, می توانید در خانه F3 فرمول =E3*D3 را در ج کنید و آن را به خانه های پایین کپی کنید (محاسبه غیر برداری) اما در روش محاسبه برداری شما بردار قسمت را در بردار تعداد ضرب می‏کنید تا بردار فروش حاصل شود.

برای انجام محاسبه برداری فروش, مراحل زیر را دنبال کنید :
۱.    بردار فروش را انتخاب کنید (خانه های F3:F7)
۲.    در حالتیکه بردار فروش انتخاب شده است, درون نوار فرمول کلیک کنید و بعد از تایپ علامت مساوی(=) , بردار قیمت واحد را انتخاب کنید (خانه های D3:D7)
۳.    علامت ضرب (*) را تایپ کنید و سپس بردار تعداد فروخته شده را انتخاب کنید (خانه های E3:E7)
۴.    دکمه های Ctrl+Shift+Enter را فشار دهید تا محاسبهء برداری, به صورت ضرب تک تک عناصر بردار در هم حاصل شود. (نتیجه را در شکل زیر مشاهده می کنید)

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

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

 برای محاسبهء فروش کل, می‏توانید از یک فرمول برداری تک خانه ای استفاده کنید. برای انجام این کار ابتدا خانه E9 را انتخاب کنید و سپس فرمول =SUM(D3:D7:E3:E7) را درج کنید و سپس برای اینکه محاسبه به شکل برداری انجام شود, دکمه های Ctrl+Shift+Enter را فشار دهید.

 

 
مزایا و معایب فرمول های برداری
مزایا :

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

معایب :

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

 

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

 

چند نکه در تعریف ثابت های برداری
۱.    یک ثابت برداری میتواند از مقادیر عددی,عناصر متنی,مقادیر منطقی و حتی مقادیر خطا تشکیل شود.
مثال :     {۴,۷,۵,”ali”,۶,۱,True,7}
۲.    برای جدا کردن عناصر یک بردار افقی از کاما استفاده می‏شود.
مثال :     {۴,۶,۵,۹,۸}
۳.    برای جدا کردن عناصر یک بردار عمودی از سمی‏کولن استفاده می‏شود.
مثال :     {۴;۶;۷;۹;۸}
۴.    در بردارهای دو بعدی از کاما برای جدا کردن عناصر افقی و از سمی‏کولن برای جدا کردن عناصر عمودی استفاده می‏شود.
مثال :    {۱,۷,۸;۶,۵,۷;۲,۹,۵}

نامگذاری ثابت های برداری
ثابت های برداری را می‏توان نامگذاری کرد و سپس از این بردار نامگذاری شده در یک فرمول استفاده کرد.
برای نامگذاری یک ثابت برداری روی دکمه Define Name در گروه Defined Names از روبان فرمول کلیک کنید و  ثابت برداری را تعریف کنید

نکته ها
•    برای وارد کردن یک فرمول برداری بعد از وارد کردن رابطه محاسباتی باید دکمه های Ctrl+Shift+Enter را فشار دهید.
•    هنگام وارد کردن یک فرمول برداری نیازی به تایپ علامت آکولاد نیست زیرا اکسل خود علامت آکولاد را دور فرمول شما می‏گذارد.
•    اگر نتیجه محاسبه یک فرمول برداری خود یک بردار می‏باشد, باید بردار مقصد را قبل از وارد کردن فرمول برداری انتخاب کنید.

•    برای تغییر جهت یک بردار یا ثابت برداری از تابع TRANSPOSE استفاده می‏شود.

•    برای ویرایش یک فرمول برداری چند خانه ای باید ابتدا کل بردار را انتخاب کنید وسپس فرمول را ویرایش کنید در غیر این صورت excel به شما اجازه ویرایش را نخواهد داد.

 

 

عملگر ها و نقش آن ها در فرمول نویسی دراکسل

  

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

به طور کلی عملگرها به دو دسته زیر تقسیم می شوند:
۱٫ عملگرهای محاسباتی
۲٫ عملگرهای متنی
۳٫ عملگرهای مقایسه ای
۴٫ عملگرهای ارجاعی یا رفرنس دهی

✅ ۱- توضیح عملگرهای محاسباتی:
به عناصری مانند چهار عمل اصلی گفته می شود. عملگرهای محاسباتی اکسل به ترتیب اولویت در محاسبات به قرار زیر هستند:
?عملگر%         درصد             اولویت۱
?عملگر ^         توان               اولویت۱
?عملگر *         ضرب              اولویت۲
?عملگر /         تقسیم          اولویت۲
?عملگر +         جمع              اولویت۳
?عملگر –         تفریق             اولویت۳

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

Formulas:
A
۱ = ۱+۲*۳+۴
۲ =(۱+۲)*(۳+۴)
۳ = ۳^۲*۲
۴ = ۳^(۲*۲)
۵ = ۲۰%*۵۰۰

Results:
A
۱  ۱۱
۲  ۲۱
۳  ۱۸
۴  ۸۱
۵  ۱۰۰

✅ ۲- توضیح عملگرهای متنی:
یک نوع عمگر متنی داریم که برای اتصال یک یا چند سلول یا متن به یکدیگر استفاده می شود و آن عبارت است از :

&    یا    و
Formulas:
A
۱  =”SMITH” & “, ” & “John”

Results:
A
۱  SMITH, John

✅ ۳- توضیح عملگرهای مقایسه ای:
عملگرهای مقایسه ای وقتی که میخواهیم شرطی بگذاریم استفاده می شوند که در زیر لیست شده اند:
?عملگر =      مساوی
?عملگر <      بزرگتر از
?عملگر >      کوچکتر از
?عملگر =<    بزرگتر یا مساوی
?عملگر =>    کوچکتر یا مساوی
?عملگر <>    نابرابر یا مخالف

✅ نکته۱: خروجی عملگرهای مقایسه ای، نتیجه ای منطقی دارد. مثلا اگر در سلولی عبارت ۳=۵= را وارد کردیم ،خروجی آن false (نادرست) خواهد بود و خروجی عبارت ۹۰=>8= مقدار true (درست) است.
✅ نکته۲: عمگرهای مقایسه ای معمولا در توابع شرطی مانند if کاربرد زیادی دارند.

✅ ۴- توضیح عملگرهای ارجاعی:
عملگرهای ارجاعی عموما وقتی استفاده می شود که میخواهیم به یک محدوده در اکسل ارجاع دهیم و شامل موارد زیر می باشد.

?عملگر   :      جهت ارجاع به یک محدوده پیوسته استفاده میشود.
?عملگر   ,  یا  ;  (بسته به تنظیمات ویندوز)     ارجاع به دو یاچند محدوده منفصل یا غیر وابسته
?عملگر   Space  یا فاصله   جهت ارجاع به اشتراک دو  محدوده