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

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

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

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

 

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

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

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

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

 

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

 

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

 

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

 

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

 

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

 

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

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

 

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

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

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

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

 

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

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

 

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

 

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

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

 

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

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

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

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

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

 

 

 

 

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

 

 

ساخت نمودار ساده

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

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

در منوی Insert و در گروه Chart گزینه Line را انتخاب کنید و سپس Line with Markers را انتخاب کنید، ظاهر تمامی موارد از شکل یا آیکن آن ها پیداست.

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

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

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

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

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

تغییر مبنای جدول در excel

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

منو هایی به نوار ریبون اضافه می شوند.

در تب Design روی دکمه Switch Row/Column کلیک کنید تا مقادیر جا به جا شوند.

 

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

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

 

 

 

نمایش راهنمای نمودار (رنگ بندی نمودار)

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

 

برچسب داده ها (Data Labels)

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

 

 

 

 

  

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

به طور کلی عملگرها به دو دسته زیر تقسیم می شوند:
1٫ عملگرهای محاسباتی
2٫ عملگرهای متنی
3٫ عملگرهای مقایسه ای
4٫ عملگرهای ارجاعی یا رفرنس دهی

✅ 1- توضیح عملگرهای محاسباتی:
به عناصری مانند چهار عمل اصلی گفته می شود. عملگرهای محاسباتی اکسل به ترتیب اولویت در محاسبات به قرار زیر هستند:
?عملگر%         درصد             اولویت1
?عملگر ^         توان               اولویت1
?عملگر *         ضرب              اولویت2
?عملگر /         تقسیم          اولویت2
?عملگر +         جمع              اولویت3
?عملگر –         تفریق             اولویت3

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

Formulas:
A
1 = 1+2*3+4
2 =(1+2)*(3+4)
3 = 3^2*2
4 = 3^(2*2)
5 = 20%*500

Results:
A
1  11
2  21
3  18
4  81
5  100

✅ 2- توضیح عملگرهای متنی:
یک نوع عمگر متنی داریم که برای اتصال یک یا چند سلول یا متن به یکدیگر استفاده می شود و آن عبارت است از:

&    یا    و
Formulas:
A
1  =”SMITH” & “, ” & “John”

Results:
A
1  SMITH, John

✅ 3- توضیح عملگرهای مقایسه ای:
عملگرهای مقایسه ای وقتی که میخواهیم شرطی بگذاریم استفاده می شوند که در زیر لیست شده اند:
?عملگر =      مساوی
?عملگر?عملگر >      کوچکتر از
?عملگر =?عملگر =>    کوچکتر یا مساوی
?عملگر     نابرابر یا مخالف

✅ نکته1: خروجی عملگرهای مقایسه ای، نتیجه ای منطقی دارد. مثلا اگر در سلولی عبارت 3=5= را وارد کردیم،خروجی آن false (نادرست) خواهد بود و خروجی عبارت 90=>8= مقدار true (درست) است.
✅ نکته2: عمگرهای مقایسه ای معمولا در توابع شرطی مانند if کاربرد زیادی دارند.

✅ 4- توضیح عملگرهای ارجاعی:
عملگرهای ارجاعی عموما وقتی استفاده می شود که میخواهیم به یک محدوده در اکسل ارجاع دهیم و شامل موارد زیر می باشد.

?عملگر  :      جهت ارجاع به یک محدوده پیوسته استفاده می شود.
?عملگر   ,  یا  ;  (بسته به تنظیمات ویندوز)     ارجاع به دو یاچند محدوده منفصل یا غیر وابسته
?عملگر   Space  یا فاصله   جهت ارجاع به اشتراک دو  محدوده

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

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

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

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

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

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

 

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

• یکسان بودن فرمولهای مورد استفاده در یک دامنه
• کاهش احتمال رونویسی تصادفی فرمولها

معایب:

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

 

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

 

چند نکه در تعریف ثابت های برداری
1.    یک ثابت برداری می تواند از مقادیر عددی,عناصر متنی,مقادیر منطقی و حتی مقادیر خطا تشکیل شود.
مثال:     {4,7,5,”ali”,6,1,True,7}
2.    برای جدا کردن عناصر یک بردار افقی از کاما استفاده می‏شود.
مثال:     {4,6,5,9,8}
3.    برای جدا کردن عناصر یک بردار عمودی از سمی‏کولن استفاده می‏شود.
مثال:     {4;6;7;9;8}
4.    در بردارهای دو بعدی از کاما برای جدا کردن عناصر افقی و از سمی‏کولن برای جدا کردن عناصر عمودی استفاده می‏شود.
مثال:    {1,7,8;6,5,7;2,9,5}

نامگذاری ثابت های برداری
ثابت های برداری را می‏توان نامگذاری کرد و سپس از این بردار نامگذاری شده در یک فرمول استفاده کرد.
برای نامگذاری یک ثابت برداری روی دکمه Define Name در گروه Defined Names از روبان فرمول کلیک کنید و  ثابت برداری را تعریف کنید

نکته ها
•    برای وارد کردن یک فرمول برداری بعد از وارد کردن رابطه محاسباتی باید دکمه های Ctrl+Shift+Enter را فشار دهید.
•    هنگام وارد کردن یک فرمول برداری نیازی به تایپ علامت آکولاد نیست زیرا اکسل خود علامت آکولاد را دور فرمول شما می‏گذارد.
•    اگر نتیجه محاسبه یک فرمول برداری خود یک بردار می‏باشد, باید بردار مقصد را قبل از وارد کردن فرمول برداری انتخاب کنید.

•    برای تغییر جهت یک بردار یا ثابت برداری از تابع TRANSPOSE استفاده می‏شود.

•    برای ویرایش یک فرمول برداری چند خانه ای باید ابتدا کل بردار را انتخاب کنید وسپس فرمول را ویرایش کنید در غیر این صورت excel به شما اجازه ویرایش را نخواهد داد.

 

 

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

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

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

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

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

با این کار کاراکتر جداکننده پارامترها به کاراکتری که ما تعریف کردیم تغییر می کند.                                                               

 
P: بیان کننده این است که فرمول به صورت برداری عمل می‌کند.
N: بیان کننده این است که فرمول با محاسبه مجدد مقداری متفاوت خواهد داشت.(F9)
@: بیان کننده این است که برای استفاده از فرمول باید Analysis toolPak باید نصب شود.
 
تابع قدر مطلق در اکسل
شرح تابع
مثال
ABS
قدر مطلق یک عدد را می دهد.
=ABS(-2)
ACOS
Arcos یک عدد را می دهد. (رادیان)
=ACOS(-0.5)*180/PI()
ACOSH
آرک کوسینوس هیپربولیک درexcel
=ACOSH(10)
ATAN2
Arctan نقطه ای به مختصات X,Y را  روی دایره مثلثاتی  می دهد.(تبدیل به درجه *180/pi()
=ATAN2(-1, -1)
CEILING
عددی را به مقداری بالاتر،نسبت به صف روند می کند، که این مقدار ضریب عددی است که پارامتر دوم تابع است.
=CEILING(2.4;2)
FLOOR
عددی را به مقداری پایین تر از خودش، نسبت به صف روند می کند، که این مقدار ضریب عددی است که پارامتر دوم تابع است.
 
COMBIN
احتمال ترکیب ریاضی دو عدد در اکسل
=COMBIN(4;2)
COUNTIF
تعداد خانه هایی که دارای شرط خاصی هستند را می دهد.
=COUNTIF(B2:B5,">55")
DEGREES
رادیان را به درجه تبدیل می کند.
توجه: تمام فرمولها برحسب رادیان هستند.
=DEGREES(PI())
EVEN
عددی را به نزدیکترین عدد زوج بعداز خودش گرد می کند.
=EVEN(2.5)
EXP
عدد e  را به توان x  می رساند.
=EXP(1)
FACT
فاکتوریل یک عدد را محاسبه می کند. (N!)
=FACT(3)
FACTDOUBLE
اگر n زوج: n!=n(n-2)(n-4)…(4)(2)
اگر n فرد: n!=n(n-2)(n-4)…(3)(1)
=FACTDOUBLE(6)
GCD
بزرگترین مقسوم علیه مشترک
Greatest Common Divisor
=GCD(24, 36) à12
INR
جزء صحیح یک عدد را می‌دهد.
=INT(5.4) à 5
LCM
کوچکترین مضرب مشترک
Least Common Multiple
=LCM(36;24) à 72
LN
لگاریتم در مبنای e
=LN(2.7182818) à 1
LOG
لگاریتم در مبنای 10 یا دلخواه
=LOG(8;2) à 3
LOG10
لگاریتم در مبنای 10
=LOG10(10^5) à 5
MDETERM
دترمینال یک ماتریس (بردار) را می‌دهد.
=MDETERM({3,6;1,1})à -3
MINVERSE P
ماتریس معکوس را می‌دهد
=MINVERSE(A2:C4) àP
MMULT P
حاصلضرب دو ماتریس را می‌دهد.
=MMULT(array1;aray2) àP
MOD د
باقی مانده تقسیم دو عدد برهم در EXCEL
MOD(n, d) = n - d*INT(n/d)
=MOD(10;3) à1
MROUND
عددی را به مضربی دلخواه از عدد دیگر، گرد می‌کند.
=MROUND(10;3) à9
MULTINOMIA
نسبت فاکتوریل جمع به فاکتوریل حاصلضرب
= (a+b+c)! / a! b! c!
=MULTINOMIAL(2,3,4)à1260
ODD
عددی را به نزدیکترین عدد فرد بعد از خودش گرد می‌کند.
=ODD(1.5) à 3
PI()
عدد پی را می‌دهد.
=PI() à3.14159265358979
POWER
عدد را به توان داده شده می‌رساند.
=POWER(2;10) à1024
PRODUCT
حاصلضرب اعداد
=PRODUCT(2,3,5) à 30
QUOTIENT
خارج قسمت یک تقسیم را می‌‌دهد.
=QUOTIENT(15;3) à 5
RADIANS
درجه را به رادیان تبدیل می‌کند.
=RADIANS(90) à1.570796
RAND() N
عددی تصادفی بین 0 تا 1 تولید می‌شود.
=RAND()*(b-a)+a
=TRUNC(RAND()*100(
RANDBETWEENN@
عددی تصادفی بین محدوده ورودی داده شده می‌دهد.
=RANDBETWEEN(1,100)
ROMAN
اعداد را به اعداد یونانی تبدیل می‌کند.
=ROMAN(8) à VIII
ROUND
برای گرد کردن اعداد با دقت در تعداد رقم اعشار
=ROUND(2.15;1) à 2.1
ROUNDDOWN
گرد کردن اعداد به سمت صفر
=ROUNDDOWN(2.578;2)à2.57
ROUNDUP
گرد کردن اعداد دور از صفر
=ROUNDUP(2.578;2)à2.58
SERIESSUM در  اکسل
تابع SERIES را می‌دهد. ر.ک به راهنمای اکسل
 
SIGN
علامت یک تابع را می‌دهد. (مثبت 1 منفی 0)
=SING(-5) à 0
SIN
مقدار سینوس یک زاویه (رادیان) را می‌دهد.
=SIN(30*PI()/180) à 0.5
SINH
مقدار سینوس هیپربولیک را  می‌دهد.
 
SQRT در اکسل
محاسبه جذر یک عدد
=SQRT(36) à 6
SQRTPI
جذر مضارب عدد پی را می‌دهد.
=SQRTPI(2) à2.50
SUBTOTAL
تابعی که مجموعه‌ای از عملیاتها را برای لیست فراهم می‌کند. (نتیجه تابع بنابر سطرهای مخفی می‌تواند کنترل شود...)
ر.ک به راهنمای اکسل
SUM
محاسبه  حاصل جمع (مقدار TRUE برابر 1 است)
 
SUMIF
حاصل جمع با توجه به شرط خاصی محاسبه می‌شود.
=SUMIF(A2:A5,">160000",B2:B5)
SUMPRODUCT
عناصر نظیر به نظیر دو آرایه را درهم ضرب و سپس مجموع آنها را محاسبه می‌کند..
=SUMPRODUCT(A2:B4, C2 4)
=SUM(A2:B4*C2 4) àP =SUM(A2:B4^2) àP
SUMSQ
محاسبه مجموع توان 2 ورودی‌ها
=SUMSQ(3;5) à 34
SUMX2MY2 P  
 
SUMX2PY2 P  
 
SUMXMY2 P  
 
 TAN
محاسبه تانژانت یک زاویه
=TAN(RADIANS(45))
TANH
محاسبه تانژانت هیپربولیک یک زاویه
=TANH(-2)
TRUNC
قسمت اعشاری را حذف می‌کند.
=TRUNC(8.9) à 8

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

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

مرحله اول: ایجاد و تنظیم باکس جستجو

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

در تب (سربرگ) developer  و در بخش ActiveX Control ابزار ComboBox را انتخاب می کنیم (اگر سربرگ Developer در نوار ریبون دیده نمی شود مسیر زیر را برای فعال کردن آن طی کنید.

File/Options/Customize Ribbon و چک باکس کنار گزینه Developer را فعال کنید)

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

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

در پنجره ای که ظاهر می شود تغییرات را به صورت زیر اعمال می کنیم.

AutoWordSelect: False

LinkedCell: B3

ListFillRange: DropDownList  ((در گام دوم یک یک نام برای این مرحله ایجاد خواهیم کرد

MatchEntry: 2 – fmMatchEntryNon

 

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

به سربرگ Developer رفته و بر روی Design mode کلیک می کنیم تا بتوانیم متن خود را در کامبوباکس وارد کنیم.

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

مرحله دوم: تنظیم اطلاعات در excel

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

برای انجام این کار از سه ستون کمکی و یک دامنه اسم پویا (dynamic name range) استفاده می کنیم.

ستون کمکی 1

فرمول زیر را در سلول F3 تایپ کرده و آن را تا F22 کپی می کنیم.

=,,ISNUMBER(IFERROR(SEARCH($B$3,E3,1),””))

 

این فرمول در صورتی که متن وارد شده در کامبوباکس در ستون نام کشورها وجود داشته باشد عدد 1 را نمایش می دهد. مثلاً اگر شما حرف UNI را تایپ نمایید فقط در مقابل نام های United stats و United kingdom عدد 1 و در مقابل نام سایر کشورها عدد 0 قرار خواهد گرفت

 

ستون کمکی 2

فرمول زیر را در سلول G3 وارد کرده و تا سلول G22 کپی می کنیم.

=IF(F3=1,COUNTIF($F$3:F3,1),””)

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

برای مثال اگر شما عبارت UNI را در کامبوباکس وارد کنید در سلول G3 عدد 1 که مطابق با United States و سلول G9  عدد 2 را که مطابق با نام United kingdom و دومین مورد یافته شده است نمایش می دهد.در این حالت اگر هیچکدام از کلمات نام کشوری در باکس جستجو واردنشده باشد در مقابل نام آن چیزی قرار نمی گیرد و سلول مقابل آن نام خالی خواهد بود.

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

در سلول H3 فرمول زیر را قرار داده و تا  H22  آن را کپی نمایید.

=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),””)

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

مثلاً اگر شما UNI را در کامبوباکس (جعبه جستجو) وارد نمایید نام های United States و United Kingdom لیست شده و نام  بقیه کشورها نمایش داده نمی شوند.

 

ایجاد نام دامنه پویا (Dynamic range Name)

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

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

تذکر: همانطور که در گام اول  نام DropDownList در مقابل فیلد ListFillRange در خواص کامبوباکس وارد کردیم در اینجا نام دامنه پویا را مشابه همان نام ایجاد می کنیم.

برای ایجاد این نام دامنه مطابق مراحل زیر عمل نمایید.

به سربرگ Formulas و سپس Name Manager بروید

در کادر Name Manager بر روی New کلیک کنید تا پنجره نام جدید ظاهر شود

در فیلد نام عبارت DropDownList را وارد نمایید

در کادر Refer to فرمول زیر را وارد کنید.

=$H$3:INDE $H$3:$H$22,MAX($G$3:$G$22),1)

مرحله سوم

استفاده از کد VBA برای تکمیل گام آخر

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

در سربرگ Developer بر روی Design کلیک کنید

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

در پنجره ظاهر شده کد های نوشته شده را پاک کرده کدهای زیر را قرار دهید.

Private Sub ComboBox1_GotFocus()

ComboBox1.ListFillRange = “DropDownList“

Me.ComboBox1.DropDown

End Sub

برای نتیجه و ظاهر بهتر کار می توانید سلول B3 را با کامبوباکس بپوشانید و ستونهای کمکی را نیز Hide نمایید.

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

 

 

 

کاربرد ترانهاده در اکسل

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

روش‌های ترانهاده در اکسل

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

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

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

ترانهاده در اکسل به روش اول

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

1- انتخاب داده‌های مورد نظر از سلول‌ها، به طور مثال سلول‌های A1 تا C1 که حاوی داده‌های مورد نظر هستند را انتخاب کنید.

2- برای کپی کردن دکمه Ctrl + C را فشار دهید. یا راست کلیک کرده و گزینه کپی را از بین گزینه‌های موجود انتخاب کنید.

 

3- سلول E2 را که سلول ابتدایی ستون مقصد است انتخاب کنید.

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

 

5- Paste Special باز می‌شود و در پایین پنجره Transpose را تیک بزنید.

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

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

 

همواره توجه داشته باشید، در تمامی عملیات که از گزینه های موجود در بخش Paste استفاده کرده و کارهای خاصی انجام می‌دهیم استفاده از کلید میانبر Ctrl+v کاربردی نداشته و عملیات مورد نظر را انجام نخواهد داد. کلید Ctrl+v، میانبر اولین گزینه از گزینه‌های موجود در بخش Paste است، که تقریباً بدون هیچ ویژگیِ خاصی فقط عمل الحاق کردن را انجام می‌دهد.

 

‌

ترانهاده در اکسل به روش دوم – تابع ترانهاده Transpose Function

برای وارد کردن تابع ترانهاده مراحل زیر انجام می‌شود.

1- محدوده‌ای از سلول‌هایی که قصد ترانهاده کردن داده‌ها به آن را دارید را انتخاب کنید.

 

2- تابع ()=TRANSPOSE را در Formula bar تایپ کنید. و برای آرگومان خواسته شده تابع، سلول‌هایی که داده‌های اولیه در آن قرار دارد را انتخاب نمایید

 

4-  دکمه CTRL + SHIFT + ENTER را فشار دهید تا تابع به صورت آرایه‌ای بر سلول‌های مقصد اعمال شود.

 

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

---

1 بر روی خانه مورد نظر کلیک کرده و عبارت “RANDBETWEEN=” را درج می نماییم.

 

---

2 پارامتر اول آن را با مقدار ابتدای بازه و پارامتر دوم آن را با مقدار انتهایی بازه مقدار دهی کنیم.به عنوان نمونه (RANDBETWEEN(1,100) که عددی تصادفی بین 1 تا 100 برای ما تولید می‌کند.

 

---

3 عدد به دست آمده را مشاهده می کنید.                                                                                     استفاده ازتابع  شرطی (if) در اکسل                                                                    

یکی از توابع منطقی سودمند دراکسل تابع “if” است. این تابع شرط مورد نظر را بررسی و با توجه به نتیجه شرط مقادیر مورد نظر را درج می‌کند. استفاده از تابع شرطی به ما امکان تصمیم گیری پویا بر اساس مقادیر ورودی را می‌دهد. برای استفاده از “if” موارد زیررا انجام می‌دهیم:

---

1 تایپ مساوی “=” در خانه مورد نظر و نوشتن عنوان “)if”.

 

---

2 با استفاده از دکمه “insert function” و باز کردن پنجره “function arguments”.

 

---

3 درج شرط در قسمت “Logical_test” (به عنوان مثال B3>30  که بررسی مقدار خانه B3 است).

 

---

4 درج مقداری که در صورت درستی شرط می‌خواهیم درج شود در قسمت “value_if_true”(مثلاً مقدار:گرم).

 

---

5 درج مقداری که در صورت نادرستی شرط می‌خوهیم درج شود در قسمت “value_if_false” (مثلاً: خنک).

 

---

6 همان طور که مشاهده می کنید دما پایین تر از 30 درجه هست به همین دلیل در قسمت وضعیت  شرط “خنک” نشان داده شده.

 (excel) برای انجام کارهای محاسباتی ریاضی نیازمند استفاده از فرمول نویسی در سلول ها هستند. در این آموزش اکسل  قصد داریم تا حدودی نحوه کار کردن با امکانات محاسباتی اکسل و کار کردن با فرمول های آن آموزش داده شود.

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

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

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

 

نحوه درج فرمول در سلول ها

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

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

 

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

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

مقادیر ثابت

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

آدرس سلول ها

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

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

عملگرها

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

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

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

این عملگرها عبارتند از =،، => و. این عملگرها به ترتیب و از راست به چپ مساوی، بزرگ تر، بزرگ تر مساوی، کوچک تر، کوچک تر مساوی و نامساوی بودن دو عدد را مقایسه می کند.

نتیجه حاصل از عملیات این عملگرها می تواند مثبت (درست) یا منفی (نادرست) باشد.

به طور مثال نتیجه حاصل از عملیات 5=4 یک نتیجه «نادرست» است چرا که عدد 5 مساوی با عدد 4 نیست.

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

به طور مثال اگر مقدار سلول B3  =”سلام” باشد و سلول C3 =”جهان”، می توانیم در سلول D3 عبارت B3&C3 را به عنوان فرمول یعنی پس از تساوی وارد کنیم تا در این سلول عبارت “سلام جهان” درج شود.

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

به طور مثال نتیجه عبارت A1:A10 سلول های A1 تا A10 است اما برای اشاره به سلول های خاص از عبارت A1;A5;A7 استفاده می شود.

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

()، ٪، ^، * /، + -، &

مثال: فرض کنید می خواهیم عبارت 4*2+10 را به دو حالت مختلف اجرا کنیم و نتیجه را بر اساس اولویت عملگرها مشاهده کنیم.

در حالت اول حاصل عبارت 4*2+10 عدد 18 خواهد شد. چرا که عملگر ضرب، از اولویت بالاتری نسبت به عملگر تقسیم برخوردار است.

اما اگر این عبارت را به صورت (10+2)*4 بنویسیم، حاصل 48 خواهد شد. چرا که عملگر پرانتز، اولویت بالاتری نسبت به عملگر ضرب دارد.

توابع

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

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

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

• توابع مالی

• توابع تاریخ و ساعت

• توابع ریاضی و مثلثاتی

• توابع آماری

• توابع جستجو و مرجع

• توابع پایگاه داده

• توابع متنی

• توابع منطقی

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

• توابع مهندسی

ساختار توابع: هر تابع دارای یک نام و معمولا تعدادی ورودی است و ساختاری به صورت زیر دارد: (…. ; ورودی 3; ورودی 2; ورودی 1) نام تابع

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

در تصویر زیر، نام تابع SUM است و ورودی های تابع نیز سلول های A1 و A2 هستند.

 

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

روش درج فرمول با استفاده از Function Wizard در اکسل

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

برای این کار از روی نوار فرمول، بر روی fx کلیک کنید تا کادر Insert Function باز شود.

 

توضیح کادر Insert Function: در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید.

در بخش Or select a category می توانید یکی از بخش های موجود را انتخاب کنید.

این بخش ها توابع اکسل را دسته بندی کرده است. مثلا اگر بخش Financial را انتخاب کنید، توابع مالی در کادر Select a function نشان داده خواهند شد.

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

اکنون در بخش Search for a function می توانید نام تابع مورد نظرتان را تایپ کنید و از بخش Select a function آن را انتخاب کنید.

حال فرض کنید می خواهیم در کادر Insert Function تابع جمع را درج کنیم. همانطور که در مثال درج فرمول از طریق وارد کردن علامت = نیز گفتیم، این تابع با نام SUM شناخته می شود.

پس از انتخاب تابع SUM بر روی دکمه OK کلیک می کنیم. در پنجره جدید که Function Arguments نام دارد، باید ورودی های تابع را وارد کنیم.

در این مثال سه عدد  12 و 10 و 8 را وارد می کنیم.

 

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