Microsoft Excel 2007 shortcut and function keys

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

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

CTRL combination shortcut keys

Key
 Deion
 
CTRL+SHIFT+(
 Unhides any hidden rows within the selection.آشکار سازی سطرهای پنهان شده در محدوده انتخاب شده
 
CTRL+SHIFT+)
 Unhides any hidden columns within the selection.
 
CTRL+SHIFT+&
 Applies the outline border to the selected cells.
 
CTRL+SHIFT_
 Removes the outline border from the selected cells.
 
CTRL+SHIFT+~
 Applies the General number format.
 
CTRL+SHIFT+$
 Applies the Currency format with two decimal places (negative numbers in parentheses).
 
CTRL+SHIFT+%
 Applies the Percentage format with no decimal places.
 
CTRL+SHIFT+^
 Applies the Exponential number format with two decimal places.
 
CTRL+SHIFT+#
 Applies the Date format with the day, month, and year.
 
CTRL+SHIFT+@
 Applies the Time format with the hour and minute, and AM or PM.
 
CTRL+SHIFT+!
 Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
 
CTRL+SHIFT+*
 Selects the current region around the active cell (the data area enclosed by blank rows and blank columns).

In a PivotTable, it selects the entire PivotTable report.
 
CTRL+SHIFT+:
 Enters the current time.
 
CTRL+SHIFT+"
 Copies the value from the cell above the active cell into the cell or the Formula Bar.
 
CTRL+SHIFT+Plus (+)
 Displays the Insert dialog box to insert blank cells.
 
CTRL+Minus (-)
 Displays the Delete dialog box to delete the selected cells.
 
CTRL+;
 Enters the current date.
 
CTRL+`
 Alternates between displaying cell values and displaying formulas in the worksheet.
 
CTRL+"
 Copies a formula from the cell above the active cell into the cell or the Formula Bar.
 
CTRL+1
 Displays the Format Cells dialog box.
 
CTRL+2
 Applies or removes bold formatting.
 
CTRL+3
 Applies or removes italic formatting.
 
CTRL+4
 Applies or removes underlining.
 
CTRL+5
 Applies or removes strikethrough.
 
CTRL+6
 Alternates between hiding s, displaying s, and displaying placeholders for s.
 
 
 
 
CTRL+8
 Displays or hides the outline symbols.
 
CTRL+9
 Hides the selected rows.
 
CTRL+0
 Hides the selected columns.
 
CTRL+A
 Selects the entire worksheet.

If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the current region and its summary rows. Pressing CTRL+A a third time selects the entire worksheet.

When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog box.

CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.
 
CTRL+B
 Applies or removes bold formatting.
 
CTRL+C
 Copies the selected cells.

CTRL+C followed by another CTRL+C displays the Clipboard.
 
CTRL+D
 Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
 
CTRL+F
 Displays the Find and Replace dialog box, with the Find tab selected.

SHIFT+F5 also displays this tab, while SHIFT+F4 repeats the last Find action.

CTRL+SHIFT+F opens the Format Cells dialog box with the Font tab selected.
 
CTRL+G
 Displays the Go To dialog box.

F5 also displays this dialog box.
 
CTRL+H
 Displays the Find and Replace dialog box, with the Replace tab selected.
 
CTRL+I
 Applies or removes italic formatting.
 
CTRL+K
 Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
 
CTRL+N
 Creates a new, blank workbook.
 
CTRL+O
 Displays the Open dialog box to open or find a file.

CTRL+SHIFT+O selects all cells that contain comments.
 
CTRL+P
 Displays the Print dialog box.

CTRL+SHIFT+P opens the Format Cells dialog box with the Font tab selected.
 
CTRL+R
 Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
 
CTRL+S
 Saves the active file with its current file name, location, and file format.
 
CTRL+T
 Displays the Create Table dialog box.
 
CTRL+U
 Applies or removes underlining.

CTRL+SHIFT+U switches between expanding and collapsing of the formula bar.
 
CTRL+V
 Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an , text, or cell contents.
 
CTRL+W
 Closes the selected workbook window.
 
CTRL+X
 Cuts the selected cells.
 
CTRL+Y
 Repeats the last command or action, if possible.
 
CTRL+Z
 Uses the Undo command to reverse the last command or to delete the last entry that you typed.

CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.
 

 کلید های تابعی )F(

Function keys

Key
 Deion
 
F1
 Displays the Microsoft Office Excel Help task pane.

CTRL+F1 displays or hides the ribbon.

ALT+F1 creates a chart of the data in the current range.

ALT+SHIFT+F1 inserts a new worksheet.
 
F2
 Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.

SHIFT+F2 adds or edits a cell comment.

CTRL+F2 displays the Print Preview window.
 
F3
 Displays the Paste Name dialog box.

SHIFT+F3 displays the Insert Function dialog box.
 
F4
 Repeats the last command or action, if possible.

CTRL+F4 closes the selected workbook window.
 
F5
 Displays the Go To dialog box.

CTRL+F5 restores the window size of the selected workbook window.
 
F6
 Switches between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage This Window, Freeze Panes, Split Window command), F6 includes the split panes when switching between panes and the ribbon area.

SHIFT+F6 switches between the worksheet, Zoom controls, task pane, and ribbon.

CTRL+F6 switches to the next workbook window when more than one workbook window is open.
 
F7
 Displays the Spelling dialog box to check spelling in the active worksheet or selected range.

CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ENTER, or ESC to cancel.
 
F8
 Turns extend mode on or off. In extend mode, Extended Selection appears in the status line, and the arrow keys extend the selection.

SHIFT+F8 enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys.

CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.

ALT+F8 displays the Macro dialog box to create, run, edit, or delete a macro.
 
F9
 Calculates all worksheets in all open workbooks.

SHIFT+F9 calculates the active worksheet.

CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.

CTRL+F9 minimizes a workbook window to an icon.
 
F10
 Turns key tips on or off.

SHIFT+F10 displays the shortcut menu for a selected item.

ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.

CTRL+F10 maximizes or restores the selected workbook window.
 
F11
 Creates a chart of the data in the current range.

SHIFT+F11 inserts a new worksheet.

ALT+F11 opens the Microsoft Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
 
F12
 Displays the Save As dialog box.
 

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

Other useful shortcut keys

Key
 Deion
 
ARROW KEYS
 Move one cell up, down, left, or right in a worksheet.

CTRL+ARROW KEY moves to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells or datasheet borders.) in a worksheet.

SHIFT+ARROW KEY extends the selection of cells by one cell.

CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell.

LEFT ARROW or RIGHT ARROW selects the tab to the left or right when the ribbon is selected. When a submenu is open or selected, these arrow keys switch between the main menu and the submenu. When a ribbon tab is selected, these keys navigate the tab buttons.

DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open. When a ribbon tab is selected, these keys navigate up or down the tab group.

In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.

DOWN ARROW or ALT+DOWN ARROW opens a selected drop-down list.
 
BACKSPACE
 Deletes one character to the left in the Formula Bar.

Also clears the content of the active cell.

In cell editing mode, it deletes the character to the left of the insertion point.
 
DELETE
 Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.

In cell editing mode, it deletes the character to the right of the insertion point.
 
END
 Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.

Also selects the last command on the menu when a menu or submenu is visible.

CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column. If the cursor is in the formula bar, CTRL+END moves the cursor to the end of the text.

CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar, CTRL+SHIFT+END selects all text in the formula bar from the cursor position to the end—this does not affect the height of the formula bar.
 
ENTER
 Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).

In a data form, it moves to the first field in the next record.

Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.

In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).

ALT+ENTER starts a new line in the same cell.

CTRL+ENTER fills the selected cell range with the current entry.

SHIFT+ENTER completes a cell entry and selects the cell above.
 
ESC
 Cancels an entry in the cell or Formula Bar.

Closes an open menu or submenu, dialog box, or message window.

It also closes full screen mode when this mode has been applied, and returns to normal screen mode to display the Ribbon and status bar again.
 
HOME
 Moves to the beginning of a row in a worksheet.

Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.

Selects the first command on the menu when a menu or submenu is visible.

CTRL+HOME moves to the beginning of a worksheet.

CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.
 
PAGE DOWN
 Moves one screen down in a worksheet.

ALT+PAGE DOWN moves one screen to the right in a worksheet.

CTRL+PAGE DOWN moves to the next sheet in a workbook.

CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.
 
PAGE UP
 Moves one screen up in a worksheet.

ALT+PAGE UP moves one screen to the left in a worksheet.

CTRL+PAGE UP moves to the previous sheet in a workbook.

CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.
 
SPACEBAR
 In a dialog box, performs the action for the selected button, or selects or clears a check box.

CTRL+SPACEBAR selects an entire column in a worksheet.

SHIFT+SPACEBAR selects an entire row in a worksheet.

CTRL+SHIFT+SPACEBAR selects the entire worksheet.

                     If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the current region and its summary rows. Pressing CTRL+SHIFT+SPACEBAR a third time selects the entire worksheet.

                     When an is selected, CTRL+SHIFT+SPACEBAR selects all s on a worksheet.

ALT+SPACEBAR displays the Control menu for the Microsoft Office Excel window.
 
TAB
 Moves one cell to the right in a worksheet.

Moves between unlocked cells in a protected worksheet.

Moves to the next option or option group in a dialog box.

SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.

CTRL+TAB switches to the next tab in dialog box.

CTRL+SHIFT+TAB switches to the previous tab in a dialog box.
 

 منبع : راهنمای اکسل 2007

 1.            شروع برنامه نویسی

روشهای مختلفی برای شروع و ایجاد یک برنامه با VBA   وجود دارد. روش اصلی و مستقیم نوشتن کدها در داخل ویرایشگر VB   یا همان     ‌VBE   می‌باشد. یک روش ساده دیگر نیز وجود دارد و آن ایجاد یک command button   بر روی صفحه گسترده محیط اکسل و شروع برنامه نویسی با کلیک بر روی آن است. کار را با روش اول شروع می‌کنیم

     روش اصلی: ایجاد و اجرای یک برنامه در VBE

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

1- ورود به محیط برنامه نویسی یا همان Visual Basic Editor  

 برای انجام اینکار چند روش وجود دارد:

·        استفاده از کلیدهای میانبر: F11 + ALT   ‌

·        استفاده از منوی اکسل: Tools-> Macro-> Visual Basic Editor

·        استفاده از ToolBar   یا همان نوار ابزار Visual Basic   و فشردن آیکون Visual Basic Editor

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

2- ایجاد Module  : ورود به منوی Insert   و انتخاب گزینه Module 

3- ایجاد یک رویه یا Sub Procedure

نوشتن فرمان (کلمه کلیدی) Sub ‌ و سپس نام برنامه

فشردن دگمه Enter  

بلافاصله بطور اتوماتیک فرمان End Sub در یک خط جدید اضافه می‌شود.

4- نوشتن کدهای برنامه VBA: کدهای برنامه را خط به خط بین فرمانهای Sub و End Sub می‌نویسیم.

 به عنوان مثال:

Sub MyProgram()

Range("A1:A10").Value = "Visual Basic For Applications"

Range("A11")=10

Range("B11").Value = 20

Range("C11").Value = "=A11+B11"

End Sub

5- اجرای برنامه : برای اجرای برنامه چندین روش وجود دارد

·         فشردن کلید F5

·         فشردن آیکن Rub Sub/User Form از   Toolbar

·         بازگشت به محیط   Excel و استفاده از F11 + ALT 1.2       

استفاده از Solver :
چگونه Solver را در اکسل فعال کنیم؟
در ابتدا ممکن است این گزینه نصب نباشد.برای نصب در اکسل 2003 به منوی
Tools و Add-Ins رفته و تیک Solver Add-in را بزنید تا نصب شود و به گزینه های منوی
Tools اضافه شود.در اکسل 2007 این گزینه در منوی Data قرار دارد و برای نصب آن می بایست از دکمه Office Button(دکمه ای که در سمت چپ قسمت بالای اکسل و با آیکون آفیس وجود دارد) گزینه Excel Options را انتخاب کنید سپس در سمت راست Add-Ins
و در سمت چپ بعد از انتخاب Excel Add-ins در قسمت Manage روی  Go کلیک کرده
و در پنجره ای که باز می شود Solver Add-in را تیک دار کنید.
کاربرد ابزارSolver در حسابداری:
فرض کنید با بودجه ای معادل 100000 تومان قصد دارید کالاهایی را خریداری کنید که قیمت آنها را از قبل می دانید ولی دقیقا نمی دانید چه مقدار از هر کالا را خریداری کنید تا برای خرید بقیه ی کالاها با کمبود بودجه مواجه نشوید.در اینجا می توان از ابزار
Solver استفاده کرد.برای مثال در سلول A1 تایپ کنید نام کالا و در محدوده ی
A2:A5 به ترتیب وارد کنید:سیب،پرتقال،موز،شیرینی .سپس در سلول  B1تایپ کنید قیمت کالا و در محدوده ی B2:B5 به ترتیب وارد کنید:1000 ، 2000 ، 2000 ، 2000
در سلول C1 تایپ کنید "مقدار کالا" و در محدوده ی C2:C5 عدد 1 را وارد کنید.در سلول
D1 تایپ کنید"قیمت کل" و در محدوده ی  D2:D5 حاصلضرب ستون B در C را وارد کنید.در سلول D6 حاصل جمع محدوده ی D2:D5 را محاسبه کنید.از منوی Tools گزینه ی
Solver را انتخاب کنید.در پنجره ای که باز می شود در قسمت Set Target Cellآدرس سلول D6 را وارد کنید.Equal To را مقدار Value of تنظیم کنید و مقدار آن را 100000 تایپ کنید.در قسمت By Changing Cells  آدرس محدوده ی C2:C5 را وارد کنید.سپس روی دکمه ی Add کلیک کنید و در اکسل روی سلول C2 کلیک کنید و از منوی آبشاری در پنجره ی Add Constraint بزرگتر و مساوی ِ را انتخاب کرده و در کادر Constraintعدد 10 را تایپ کنید و در همان پنجره دکمه Add را زده سلول C3را کلیک کنید و همان کار قبلی را انجام دهید برای سلول های  C4 و C5 نیز هم چنین.بعد از اعمال این چهار شرط همچنانکه پنجره ی Add Constraint باز است محدوده ی C2:C5 را انتخاب کنید و از منوی آبشاریِ پنجره int را انتخاب کنید(برای گرد شدن مقدارها)و OKرا بزنید و سپس دکمه ی
Solve را بزنید تا اکسل محاسبات را انجام دهد.
استفاده ازGoal Seek
فرض کنید در امتحانات پایان ترم نمرات زیر را کسب کرده اید...
14،15،16،17،18،19
 و یک امتحان دیگر باقی مانده است و قصد دارید نمره ای را از آخرین امتحان کسب کنید که معدل نمراتتان 17 بشود.در اینجا می توانید از ابزارGoal Seek استفاده کنید.برای اینکار نمرات کسب شده را در سلولهایA1 تاA6 وارد کنید و سپس در سلولA8
فرمول میانگین را به صورت زیر وارد کنید...
=AVERAGE(A1:A7)
همانطور که مشخص است سلول A7 نیز که فاقد داده است و قرار است مقدار آن با ابزار
Goal Seek محاسبه شود نیز در فرمول میانگین وجود دارد.حالا از منویTools گزینه
Goal Seek را انتخاب کنید تا پنجره ی آن باز شود.در قسمت Set cell باید آدرسی را وارد کنید که فرمول میانگین را در آن وارد کرده اید بنابراین سلول A8 را وارد کنید.در قسمت
To value باید مقداری را که می خواهید مقدار میانگین به آن مقدار تغییر کند را وارد کنید که در اینجا میانگین مورد نظر ما 17 است که وارد می کنیم و در قسمت
By Changing cell باید آدرس سلولی را وارد کنید که هنوز داده ای در آن وارد نشده است که در اینجا سلول مورد نظر سلول $A$7 است.حال OK را بزنید تا معدل در سلولA8به عدد 17 تغییر پیدا کند و در سلول A7 نیز نمره ای را می بینید که شما باید کسب کنید تا معدلتان 17 بشود.یعنی با کسب نمره 20 معدلتان 17 می شود.
نکته:این گزینه را می توانید به غیر از معدل بر روی فرمول های دیگر نیز پیاده کنید.
استفاده از ابزار Data validation
از این ابزار وقتی استفاده می کنیم که بخواهیم به کاربر بفهمانیم که نمی تواند از یک محدوده ی از پیش تعیین شده تجاوز کند.فرض کنید در سلول A1باید توسط کاربر مقداری وارد شود که بین 100 و 200 باشد و کاربر از این محدودیت اطلاعی نداشته باشد در این هنگام می توانیم از ابزار Data validation استفاده کنیم.برای تمرین سلولA1 را انتخاب کرده و از منوی Data گزینه validation را انتخاب کنید در زبانه ی Settings در قسمت
Allow از منوی آبشاری Whole number را انتخاب کنید.سپس در قسمت Dataاز منوی آبشاری between را انتخاب کنید و مقدارMinimum و Maximum را به ترتیب100و200
بنویسید.به زبانه ی Input Message رفته و در صورت تمایل قسمت TitleوInput Message
را پر کنید.این قسمت زمانی نمایش داده می شود که کاربر این سلول را انتخاب می کند. در زبانه ی Error Alert در کادرTitle تایپ کنید"هشدار" و در کادر Error messeage
تایپ کنید"فقط مقادیر بین 100 تا 200 پذرفته می شوند"در قسمت  Style سه نوع سبک وجود دارد با انتخاب سبک Stop اگر داده ی کاربر بالاتر از 200 یا پایین تر از 100 بود پنجره ی هشدار باز می شود و از ورود داده ی اشتباه جلوگیری می کند و کاربر به هیچ وجه نمی تواند داده اشتباه وارد کند .با انتخاب سبک Warningبه کاربر هشدار داده و به کاربر اجازه می دهد تا مقدار خارج از محدوده را با انتخاب گزینه Yesدر سلول وارد کند.
با انتخاب سبک Information به کاربر اطلاع می دهد که چه محدودیتی برای این سلول برقرار است.سپس به کاربر اجازه می دهد که با انتخاب گزینه OK مقدار خارج از محدوده را وارد کند.
نکته : برای پاک کردن سلول از این محدودیت ها پس از انتخاب سلول از منوی Edit
 گزینه Clear | All را انتخاب کنید.

کاربرد ابزار Scenario در حسابداری:
فرض کنید خلاصه اطلاعات چندین شرکت را در اختیار دارید و می خواهید آن را به آسانی مشاهده کنید برای اینکار می توانید از ابزار سناریو استفاده کنید.برای مثال در سلول
A1 تایپ کنید نام شرکت .در سلول A2 تایپ کنید در آمد.در سلولA3 تایپ کنید هزینه.درسلول A4 تایپ کنید سود یا زیان سپس در سلول B4 اختلاف سلول B2 و
B3 را با استفاده از فرمول =B2-B3 محاسبه کنید.از منوی Tools گزینه ی Scenarios
را انتخاب کنید.در پنجره ای که باز می شود روی Add کلیک کنید سپس در کادر اول (کادر نام سناریو)تایپ کنید شرکت تضامنی احمد و شرکا و در قسمت Changing cells
محدوده ی B1:B3 را وارد کنید و OK را بزنید.در کادرها به ترتیب وارد کنید:
شرکت تضامنی احمد و شرکا
1000000
1500000
سپس روی  Add کلیک کنید و در کادر نام سناریو وارد کنید شرکت تضامنی حسنی و حسینی و  OK را بزنید و در کادرها به ترتیب وارد کنید:
شرکت تضامنی حسنی و حسینی
2000000
1750000
و روی Add کلیک کنید و در کادر نام سناریو وارد کنید شرکت تضامنی الف و ب و روی
OK کلیک کنید و در کادرها به ترتیب وارد کنید:
شرکت تضامنی الف و ب
3000000
2850000
و روی OK کلیک کنید.اکنون می توانید با انتخاب نام شرکت و کلیک روی دکمه ی
Show اطلاعات شرکت را در یک قسمت ثابت از اکسل مشاهده کنید.برای اینکه بتوانید از سناریو در شیت های دیگر استفاده کنید از دکمه ی Merge استفاده کنید.
استفاده از ابزار Advance Filter
ابتدا در سلول A1 تایپ کنید"اسامی"سپس در سلول A2 تا A21اسم علی را 5 بار در 5 سلول،اسم حسین را 5 بار در 5 سلول دیگر،اسم محمد را 5 بار در 5 سلول دیگر و اسم رضا را 5 بار در 5 سلول باقیمانده تایپ کنید.حالا از منوی Data در قسمت Filterگزینه ی
Advanced Filter را انتخاب کنید در قسمت List rang آدرس سلول A1:A21 را وارد کنید سپس در پایین پنجره Unique records only را تیک بزنید و  OKرا بزنید.همانگونه که مشاهده می کنید اسامی تکراری جمع شده و فقط یک نمونه از آنها نمایش داده می شود.برای برگشتن به حالت قبل از منوی  Data و قسمت Filter گزینه ی Show All
را انتخاب کنید.
استفاده از Advanced Filter برای فیلتر پیشرفته:
مانند تصویر زیر یک جدول با اطلاعات مشابه بسازید...( آدرس سلول ها نیز مشابه باشد)
برای مشاهده ی تصویر جدول کلیک کنید
یا می توانید جدول با اطلاعات بالا را از اینجا دانلود کنید

بعد از آن محدوده ی A4:D4 را کپی کنید و در محدوده ی A1:D1 بچسبانید.اکنون دو سطر عنوان دارید یکی در سطر اول و دیگری در سطر چهارم.درسلول C2 شرط فیلتر را برای حقوق ماهانه اعمال می کنیم برای مثال در سلول C2 تایپ کنید
>460000
یعنی بیشتر از 460000.سپس در سلول D2 تایپ کنید اصفهان .اکنون محدوده ی داده ها را که محدوده ی A4:D21 می باشد انتخاب کنید و کلید Crtl+F3 را بزنید در قسمت
Names in workbook تایپ کنید Information و در قسمت Refers to محدوده ی داده ها را انتخاب کنید که چون قبلا انتخاب کرده اید اتوماتیک وار این قسمت تکمیل شده است.اکنون OK را بزنید.تا حالا بر روی محدوده ی داده ها اسم گذاری کرده اید.اکنون می بایست بر روی محدوده ی شرط نیز اسم گذاری کنید.محدوده ی A1:D2را انتخاب کرده کلید Ctrl+F3 را بزنید.در کادر نام تایپ کنید Terms و OK را بزنید.اکنون از منوی Data
قسمت Filter گزینه ی Advanced Filter را انتخاب کنید.تا پنجره ی مربوطه باز شود در قسمت List range کلید F3 را بزنید و از پنجره ی Paste Name نامInformationرا انتخاب کرده وOK را بزنید.توجه داشته باشید در قسمت List range فقط نامInformationوجود داشته باشد و نه آدرس سلول.به قسمت Criteria range رفته سپس کلیدF3را زده
Terms را انتخاب کرده وOK را بزنید.در پایین پنجره ی Advanced Filter گزینه
Unique records only را تیک بزنید یا نزنید(فرقی نمی کند) وOK را فشار دهید.خواهید دید که در جدول داده ها فقط افرادی که ساکن اصفهان هستند و حقوق ماهانه ی آنها بیشتر از 460000 است نمایش داده می شود.برای برگشتن به حالت عادی از منوی
 Data قسمت Filter گزینه ی Show All را انتخاب کنید.می توانید با استفاده از کادر
Copy to در پنجره ی Advanced Filter اطلاعات فیلتر شده را در جایی دیگر کپی کنید.برای فعال شدن این کادر Action را روی Copy to another locationتنظیم کنید.
ضبط یک Macro و اجرای آن:
ابتدا از منوی Tools و قسمت Macro گزینه Record New Macro را انتخاب کنید تا پنجره
Record Macro باز شود در کادر  Macro name نامی را برای ماکرو تایپ کنید.مثلا تایپ کنید  MyMacro سپس هنگامی که نشانگر ماوس را داخل کادر کوچک Shortcut key
قرار داده اید کلید ترکیبی Shift+L را فشاردهید و Store macro in را روی This Workbook
تنظیم کنید و OK را بزنید.اکنون در محدوده ی A1:A5 ارقامی را وارد کنید و با استفاده از فرمول در سلول A6 جمع ارقام را محاسبه کنید.سپس از منوی Tools قسمت Macro
گزینه ی Stop Recording را بزنید.به شیت 2 رفته کلید Alt+F8 را فشار دهید تا پنجره ی ماکروها باز شود ماکرویی که ضبط کرده اید را انتخاب کنید و دکمه  Runرا در پنجره ی
Macro بزنید.یا به شیت 3 رفته کلید Crtl+Shift+Lرا بزنید.
نکته:این ماکرو آدرس مطلق را ضبط و اجرا کرد برای اینکه بتوان از ماکرو در سلولها(آدرس های) دیگر استفاده کرد باید قبل از ورود داده ها مرجع نسبی را در کادر کوچکی که پس از زدن گزینه ضبط ماکرو باز می شود فعال کنیم.

ساخت ماکرو و اجرای آن:
برای مثال ما قصد داریم ماکرویی بسازیم که با اجرای آن سلول  A1 با فونتی به سایز 20 نشان داده شود برای اینکار از منوی Toolsبه Macro رفته و گزینه ی Macrosرا انتخاب کنید(یا کلیدAlt+F8 را بزنید).در کادر Macro name تایپ کنید MyMacro2و سپس در سمت راست پنجره ی ماکرو دکمه Create را بزنید.به برنامه Visual Basicمایکروسافت هدایت می شوید.در پنجره ی Book1 دو خط به صورت زیر وجود دارد...

Sub MyMacro2()

End Sub

شما باید دستورات لازم را بین این دو خط بنویسید.برای مثالی که ذکر شد باید بین این دو خط دستور زیر را تایپ کنید...
Cells(1, 1).Font.Size=20
توجه داشته باشید در داخل پرانتز عدد 1 اول نشان دهنده ی سطر اول و عدد 1 دوم نشان دهنده ی ستون اول است که مختصات سلول A1 را می رساند.اکنون برنامه
Visual Basic را بازدن کلید Alt+Q (یا به صورت معمول)ببندید تا به اکسل برگردید.از منوی
Tools به Macro رفته و گزینه Macros را انتخاب کنید(یا کلید ترکیبیAlt+F8 را بزنید)در پنجره ی ماکرو MyMacro2 را انتخاب و دکمه Run را در آن پنجره بزنید.همانطور که مشاهده می کنید در شیتی که هستید سلول A1 به سایز 20 تغییر می کند.
یا با دستور زیر می توانیم داده های سلول A1 را با فونت بولد(پر رنگ) نشان دهیم...
Cells(1, 1).Font.Bold = True
یا با دستور زیر می توانیم سلولA1 را با فونت 20 و به صورت بولد(پر رنگ) نشان دهیم.
Cells(1, 1).Font.Size = 20
Cells(1, 1).Font.Bold = True
یا با دستور زیر می توانیم در سلولA1 کلمه Excel را به صورت پر رنگ و با سایز 20 وارد کنیم...
Cells(1, 1).Value = "Excel"
Cells(1, 1).Font.Size = 20
Cells(1, 1).Font.Bold = True
نکته : همانند سطر اول ، داده های متنی(مانند Excel را داخل کوتیشن می گذاریم ولی برای داده های عددی کوتیشن لازم نیست)
دستور زیر محدوده ی A1:B10 را به فونتی با سایز 18 تغییرمی دهد...
Range("A1:B10").Font.Size = 18
دستور زیر کلمه Excel2003 را در محدوده ی A1:A10 از شیت یک وارد می کند...
Sheets("SHEET1").Range("A1:A10").Value = "Excel2003"

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

پارامترهای بهره:
1.ارزش فعلی (اصل پول): که با نماد Pv نشان می دهند.
2.ارزش آینده(اصل و فرع):که با نماد Fv نشان می دهند.
3.نرخ بهره که با نماد Rate نشان می دهند.
4.تعداد دوره: که با نماد nper نشان می دهند.
5.مقدار دریافتها و پرداخت های مساوی در پایان هر دوره (اقساط)که با نماد Pmtنشان می دهند.

توابع بهره: (توجه:برای نمایش صحیح توابع آنها را در سلول های اکسل یا یک فایل متنی کپی کنید)
=Fv(Rate;Nper;Pmt)
در این تابع Fv که ارزش آینده ی یک سرمایه گذاری می باشد مجهول تابع است.به عبارت دیگر این تابع ارزش آینده یک سرمایه را وقتی که Pmt برای مدت Nper دوره با نرخ
Rate برای هر دوره سرمایه گذاری شده باشد را محاسبه می کند.
مثال:ارزش آینده یک سرمایه گذاری سالانه دو میلیون ریال با نرخ 10% به مدت 10 سال چقدر است؟
جواب:

=FV(10%;10;2000000)

=Pv(Rate;Nper;Pmt)

این تابع ارزش فعلی یک سرمایه گذاری که مقدار Pmt برای مدت Nper دوره با نرخ Rate
در هر دوره دریافت شود را محاسبه می کند.
مثال:چقدر در یک بانک با نرخ بهره ی 17.5 درصد به مدت 5 سال سرمایه گذاری کنیم تا سالانه مقدار یک میلیون ریال دریافت کنیم؟
جواب:

=PV(17.5%;5;1000000)

=Pmt(Rate;Nper;Pv)

این تابع میزان پرداخت های مساوی در پایان هر دوره (اقساط) را برای قرض گرفتن مبلغ
Pv با نرخ بهره ی Rate طی nper دوره محاسبه می نماید.
مثال:پرداخت های سالیانه یک وام دویست میلیون ریالی با نرخ 17.5% طی مدت 5 سال چقدر است؟
جواب:

=pmt(17.5%;5;200000000)

=Nper(Rate;Pmt;Pv)

این تابع تعداد دوره هایی را که  pv با اقساط pmt (ریالی،دلاری) و با نرخ بهره ی Rate
پرداخته می شود را محاسبه می کند.
مثال:
چند دوره طول می کشد تا وام بیست میلیون ریالی با نرخ بهره 20 درصدکه به اقساط یک میلیون ریالی پرداخته می شود تسویه گردد؟

جواب:
=Nper(20%;1000000;20000000)

=Rate(Nper;Pmt;Pv)

این تابع نرخ بهره را برای مبلغ pv که با اقساط Pmt (ریالی،دلاری و...) طی nperدوره پرداخته می شود را محاسبه می کند.

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

هر تابع از یک ترکیب سه قسمتی تشکیل می شود:
(شناسه)نام تابع=
که شناسه می تواند مقادیر عددی ، محدوده  یا رشته های کاراکتری باشد.
توابع ریاضی
توابع حسابی،توابع مثلثاتی(سینوس،کسینوس و ...)از توابع ریاضی هستند.
توابع حسابی(از توابع ریاضی)

توجه:برای نمایش صحیح توابع آنها را در سلول های اکسل کپی کرده و مقادیر را جاگذاری کنید.
=ABS(x)
این تابع قدر مطلق x را برمی گرداند.برای مثال اگر جای ایکس 20- قرار بدهیم،حاصل عدد 20 می شود.
=EXP(x)
این تابع مقدار ثابت E (عدد نپر) را به توان x می رساند.عدد نپر 2.7182 است.برای مثال اگر جای xعدد صفر قرار دهیم.حاصل یک می شود،چون هر عدد به توان صفر مساوی یک می شود.
=LN(x)
این تابع لگلریتم طبیعی x را برمی گرداند.
=Log10(x)
این تابع لگاریتم مبنای 10 را برای x باز می گرداند.
=log(x;b)
این تابع لگاریتم را برای عدد x باز می گرداند.b پایه یا مبنا می باشد و در حالت پیشفرض برابر با 10 می باشد.
=Mod(x;y)
این تابع x را برy تقسیم کرده و باقیمانده را برمی گرداند در این ترکیبx یک عدد و
y عددی مخالف صفر است.
=Rand()
این تابع یک عدد تصادفی بین صفر و یک تولید می کند.برای ایجاد اعداد تصادفی خارج از دامنه صفر و یک از فرمول زیر استفاده کنید:
=Rand()*(HadeBala-HadePaeen)+HadePaeen
که در آن بجای Hadebala بالاترین مقدار و بجای HadePaeen پایین ترین مقدار را می دهیم.
=SQRT(x)
جذر ایکس را می گیرد.
توابع مثلثاتی
=pi()
این تابع عددP را بازمی گرداند(عدد پی مساوی 3.14 است)
=DEGREES(x)
این تابع x را که برحسب رادیان است به درجه تبدیل می کند.
=RADIANS(x)
این تابع ایکس که برحسب درجه است را به رادیان تبدیل می کند.
=Average(number1;number2;...)
این تابع میانگین حسابی مقادیر را می گیرد.در این تابع number ها می توانند مقادیر یا آدرس سلول های حاوی مقادیر باشند.
=count(value1;value2;...)
این تابع مقادیر عددی (نه متن)را در محدوده ی تعیین شده شمارش می کند.در این تابع
value ها می توانند مقدار،متن یا آدرس یک سلول یا یک محدوده باشند که این تابع فقط مقدار مقادیر را(مانند 1،2و...) شمارش می کند.
=MAX(number1;number2,...)
این تابع بیشترین مقدار یا آخرین تاریخ را در محدوده ی انتخابی نشان می دهد.
=MIN(number1;number2,...)
کمترین مقدار را در محدوده انتخابی نشان می دهد.
توابع مالی برای استهلاک:
تابع محاسبه استهلاک به روش خطی(خط مستقیم)
=SLN(Cost;Salvage;Life)
که در این تابع
Cost:قیمت تمام شده
Salvage:ارزش اسقاطی و
Life:عمر مفید می باشد.
تابع محاسبه استهلاک به روش مجموع سنوات
=syd(Cost;Salvage;Life;Perial)
که Perial دوره ای است که استهلاک برای آن محاسبه می شود(برای مثال در سال دوم این مقدار برابر 2 می شود)
تابع محاسبه استهلاک به روش نزولی
=DDB(Cost;Salvage;Life;Perial)

مشاهده ی توابع موجود در اکسل فوق جلسه ششم
Shift+F3 با فشردن این کلید  یا مراجعه به منوی Insert و قسمتFunctionمی توانید تمامی توابع(مالی،ریاضی،آماری و...) را مشاهده کنید.

تغییر سبک پیشفرض کارپوشه های اکسل
هنگامی که یک فایل اکسل را باز می کنید تمامی سلول ها،شیت های موجود و شیت هایی که خواهید ساخت دارای یک سبک از پیش تعیین شده هستند.شما می توانید در هر کارپوشه (که به طور پیشفرض با نام Book1,2,3 نامگذاری شده اند) سبک پیشفرض اکسل را تغییر دهید تا بلافاصله بر روی تمامی سلول ها و شیت ها اعمال شود.برای این کار از منوی Format گزینه Style را انتخاب کنید و در منوی آبشاری
Style name سبک Normal را انتخاب کنید و دکمه ی Modify را بزنید.پنجره ی
Format Cells باز می شود.سبک خود را با استفاده از گزینه های موجود در زبانه های این پنجره اعمال کنید و OK را بزنید.همانگونه که خواهید دید این سبک به تمام سلول ها،شیت های قبلی و شیت هایی که در آینده می سازید اعمال می شود.(فقط در این کارپوشه؛کارپوشه شامل تمام شیت ها و سلول های یک فایل اکسل است)
اضافه کردن سبک یک سلول به مجموع سبک ها
این حالت در مواقعی به کار می آید که شما برای یک سلول یک سبک اعمال کرده اید و قصد دارید آن را ذخیره کنید تا در آینده برای سلولهای دیگر از آن استفاده کنید برای اینکار ابتدا در نوار ابزار راست کلیک کرده و  Customize... را انتخاب کنید.سپس در زبانه
Commands از قسمت طبقه بندی(سمت چپ) Format را انتخاب کنید و در سمت راست نوار ابزار ِ آبشاری(باز شونده) Style را به کمک ماوس در کنار نوار ابزارهای دیگر قرار دهید.برای مثال شما در سلول A1 سبکی را دارید که می خواهید آن را ذخیره کنید.ابتدا سلول A1 را انتخاب کنید.در نوار ابزار آبشاری Style نام سبک را تایپ کنید و کلید Enter را بزنید تا سبک ذخیره شود.حال می توانید بعد از انتخاب سلولی دیگر سبک موجود در این نوار ابزار را انتخاب کنید تا بر روی سلول اعمال شود.
نکته:این نوار ابزار همان نوار ابزار موجود در منوی Format گزینه Styleاست.

قالب بندی شرطی:
ابتدا محدوده ی A1 تا A20 را انتخاب کنید و مقادیری را بین صفر تا بیست در آن وارد کنید.سپس محدوده ی مذکور را انتخاب کنید و از منوی Format گزینه ی
Conditional Formatting...را انتخاب کنید.کادری باز می شود که در آن می توانید سه شرط را بر محدوده ی انتخابی اعمال کنید.در شرط اول (یعنی Condition 1)در فیلد اول
Cell Value Is در فیلد دوم less than را از منوی آبشاری انتخاب کنید در فیلد سوم عدد 10 را وارد کنید.دکمه ی Format...را زده و از زبانه ی  Patternsرنگ قرمز را انتخاب کرده و
OK را بزنید.برای اینکه شرط دوم و سوم را اعمال کنید روی دکمه Add دو مرتبه کلیک کنید.در شرط دوم در فیلد اول Cell Value Is در فیلد دوم between را انتخاب کنید و به ترتیب در فیلدهای بعدی اعداد 10 و 14 را وارد کنید.دکمه ی Format...شرط دوم را زده و از زبانه ی Patterns رنگ زرد را انتخاب کنید.در شرط سوم از فیلد اول Cell Value Is و از فیلد دوم greater than را انتخاب کنید و در فیلد سوم عدد 14 را وارد کنید.دکمه
Format شرط سوم را زده و این بار رنگ سبز را در زبانه Patternsانتخاب کنید.سپس
OKرا بزنید تا قالب بندی شرطی اعمال شود.همانگونه که خواهید دید اعداد کمتر از 10 با پس زمینه قرمز و اعداد بین 10 تا 14 و خود این دو عدد با پس زمینه زرد و اعداد بزرگتر از 14 با پس زمینه سبز نمایش داده می شوند.
ساخت سبک های سفارشی
در کادر Style موجود در منوی Format شما می توانید سبک  سفارشی خود را بسازید و آن را با نامی متفاوت از بقیه ی نام ها ذخیره کنید.برای اینکار از منوی Format گزینه
Style را انتخاب کنید.سپس در فیلد Style name نام سبک را MyStyle1 تایپ کنید و روی دکمه Modify کلیک کنید.پنجره ی Format Cells باز می شود.یک شکل بندی را به صورت دلخواه اعمال کنید.(در قسمت های مربوطه فونت،رنگ پس زمینه و ....را انتخاب کنید )و در آخر OK را بزنید.(2 بار).پس از انجام این عملیات در منوی Format و گزینه ی
Style سبکی با نام MyStyle1 در فیلد آبشاریِ Style name اضافه می شود که با انتخاب سلول یا محدوده و سپس انتخاب آن این سبک بر روی محدوده یا سلول انتخابی شما اعمال می شود.
کاربرد دکمه Merge در کادر Style موجود در منوی Format
ابتدا مانند عملیات ذکر شده در بالاتر یک سبک سفارشی ایجاد کنید.سپس از طریق کلید Ctrl+N کارپوشه جدیدی باز کنید.در کارپوشه جدید از منوی  Format گزینه Style
را انتخاب کنید و در فیلد Style name به دنبال نام سبک سفارشی خودتان یعنی
MyStyle1 بگردید.بدیهی است که آنرا نخواهید یافت.حال می خواهیم سبک سفارشی را، که در کارپوشه ی اول ایجاد کرده ایم به کارپوشه ی دوم کپی کنیم تا در این قسمت نمایش داده شود و بتوان از آن استفاده کرد.برای این کار در کارپوشه ی دوم  از منوی
Format گزینه ی Style را انتخاب کنید و سپس دکمه Merge را بزنید.در کادر
Merge Styles نام کارپوشه ی اول نشان داده می شود آن را انتخاب کنید و OK را بزنید دو مرتبه OK را بزنید.خواهید دید سبک سفارشی به کارپوشه دوم کپی می شود و می توان آن را در فیلد Style name دید و از آن استفاده کرد.برای استفاده یک سلول یا محدوده را درکارپوشه دوم انتخاب کنید و از منوی Format گزینه Style را انتخاب کنید و در کادر باز شده در قسمت فیلد آبشاریِ Style name نام سبک سفارشی خود را انتخاب کنید و OKرا بزنید.

ارجاعات در اکسل

 1-    ارجاع به همان کاربرگ فعال

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

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

B1=A1

 2- ارجاع به دیگر کاربرگهای یک کاربرگ دان

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

مثال: فرض کنید شما در sheet1 مانده حسابهای دفتر کل را نوشته اید و در sheet2 می خواهید ترازنامه را طراحی کنید برای این منظور مثلاً در sheet2 روبروی سلول صندوق کلیک می کنیم و کلید مساوی را فشار می دهیم و به sheet1 می رویم و در مانده حسابها در سلول مانده صندوق کلیک می کنیم و اینتر می زنیم با این کار هر رقمی در سلول مانده صندوق باشد در ترازنامه منعکس خواهد شد.

A1=Aheet2!A1

 تمرین: یک ترازنامه در sheet1  و یک ترازنامه در sheet2 با ارقام فرضی ایجاد کنید و در sheet3 این دو تا ترازنامه را با هم با استفاده از ارجاعات لینک کرده و جمع بزنید.

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

تابع ارزش آتی (FV)

غالباً تابع FV دارای 5 آرگومان بصورت زیر می باشد:

(FV(Rate,Nper,Pmt,Pv,Type

Rate: نرخ بهره هر دوره می باشد.

Nper: تعداد کل دوره های پرداخت سالیانه است.

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

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

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

Type: چگونگی پرداخت ها را مشخص می کند این آرگومان مقدار صفر و یک را می پذیرد. صفر یعنی پرداخت ها در انتهای دوره صورت می گیرد و یک یعنی پرداخت ها در ابتدای دوره انجام می پذیرد.

مثال1: نرخ بهره سالیانه 6% چنانچه یک میلیون سپرده گذاری نماییم بعد از یکسال چقدر پس انداز نمودیم؟

1060000=(0;1000000-;0;1;6%)=FV

مثال2: یک حساب پس انداز با مقدار 200000 تومان باز می کنیم هر ماه نیز مبلغ 50000 تومان به این حساب اضافه می کنیم نرخ بهره سالیانه 12% می باشد پس از 10 ماه چقدر پس انداز شده است؟ (پرداخت ها ابتدای هر دوره می باشند.)

749266=(1;200000-;50000-;10;1%)=Fv

·        در مثال فوق نرخ بهره سالیانه می بایست به نرخ بهره مامانه تبدیل شود.

 تابع ارزش فعلی (Pv)

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

قالب تابع بصورت زیر است:

(PV(Rate,Nper,Pmt,Fv,Type

Rate: نرخ بهره هر دوره می باشد.

Nper: تعداد کل اقساط هر دوره.

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

Pmt: اقساط هر دوره که در طول دوره ثابت است و تغییر نمی کند.

Fv: ارزش آتی یا میزان سرمایه بعد از آخرین پرداخت.

Type: چگونگی پرداخت ها را مشخص می کند این آرگومان مقدار صفر و یک را می پذیرد. صفر یعنی پرداخت ها در انتهای دوره صورت می گیرد و یک یعنی پرداخت ها در ابتدای دوره انجام می پذیرد.

تذکر1: واحد Nper و Rate باید یکسان باشد هر دو ماهانه یا هر دوسالیانه.

تذکر2: مقدارهایی که پرداخت می شوند با اعداد منفی نمایش داده می شود.

مثال1: قرار است 20000000 ریال 2 سال دیگر دریافت گردد با نرخ 10% ارزش فعلی مبلغ مذکور چقدر است؟

62/16528925= (0;20000000-;0;2;10%)=Pv

بالعکس عبارت مذکور را می توان چنین بیان کرد مبلغ 62/16528925 را با نرخ 10% سپرده گذاری گردد 2 سال بعد چقدر پس انداز نمودیم؟

2000000= (0; 62/16528925 -;0;2;10%)=Fv

 تابع محاسبه اقساط وام (PMT)

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

قالب تابع بصورت زیر است:

(PMT(Rate,Nper,Pv,Fv,Type

Rate: نرخ بهره هر دوره می باشد.

Nper: تعداد کل اقساط در یک دوره یکساله.

Pv: مقدار ارزش فعلی (جاری) کل پرداخت های آتی

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

Type: چگونگی پرداخت ها را مشخص می کند این آرگومان مقدار صفر و یک را می پذیرد. صفر یعنی پرداخت ها در انتهای دوره صورت می گیرد و یک یعنی پرداخت ها در ابتدای دوره انجام می پذیرد.

مثال: قسط ماهانه یک وام 100000 ریالی با نرخ بهره سالیانه 8% برای یک دوره 10 ماهه چقدر است؟

  10370= (0;.;100000-;0;10; )=Pmt

 انجام محاسبات با ایجاد ارجاعات در آرگومان ها

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

مثال: اطلاعات زبر در یک کاربرگ وارد گردد:

A
 
 
9%
 1
 
60
 2
 
0
 3
 
300000
 4
 
0
 5
 
=PMT(A1/12;A2;A3;A4;A5)
 6
 

     با استفاده از ارجاعات می توانیم از دوباره نویسی فرمول ها جلوگیری کنیم در مثال بالا می توانیم ستون A را مثلاً نرخ بهره یا مبلغ وام را عوض کنیم و با فشار دادن یک اینتر بطور اتوماتیک در ستون A6 مبلغ قسط وام مشخص می شود.
 تمرین:

1-   سهامی به ارزش 7000000 ریال خریداری کرده ایم چنانچه به مدت 20 سال در انتهای هر ماه 600000 ریال پرداخت نماییم با نرخ 12% آیا این یک سرمایه گذاری مناسبی بوده است یا خیر؟

2-     جدولی در اکسل طراحی کنید و با استفاده از ارجاعات انواع وام با نرخ بهره متفاوت را در آن بطور اتوماتیک محاسبه نمائید؟

    دوستان در صورت وجود مشکل یا سوال می توانید در قسمت پایین این کامنت سؤالات خود را بپرسید تا توضیحات بیشتری برایتان ارئه کنم.

نحوه ثابت کردن فرمول در اکسل

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

 به عنوان مثال فرض کنید می خواهیم مالیات حقوق پرسنل یک شرکت را محاسبه کنیم برای این منظور ما به چند پارامتر نیاز داریم 1

- حقوق پایه کارمند

2- میزان معافیت مالیاتی هر ماه و

3- نرخ مالیات ،

برای حل این مسئله ساده ما می توانیم میزان معافیت مالیاتی را که ماهانه 227000 تومان می باشد در خانه A1  تایپ کنیم و جدول حقوق را نیز به این صورت تنظیم نماییم:

در (ستون A به جز خانه (A1 خانه A2 میزان حقوق پایه ماهانه

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

(A2-$A$1)*0.1 =

    همان طور که می بینید A2  را می نوسیسم اما چون خانه A1 باید در همه خانه ثابت باشد در دوطرف A علامت دلار گذاشته شده است تا خانه A1 در همه فرمول ها یکسان باشد بعد شما می توانید عمل درگ کردن و رها کردن را برای خانه هایی که میخواهید مالیات را حساب کنید انجام بدهید.

نحوه گزارش گیری از نرم افزار اکسل

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

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

2-    گاهی اوقات لیست شما در یک صفحه جا نمی شود و می خواهید درهر صفحه مثلاً یک سطر (سطر اول) صفحه عنوان یا تیتر بیاید مانند نام و نام خانوادگی پرسنل که در هر صفحه در سطر اول ثابت بیاید برای این منظور شما می توانید فقط یکبار در سطر اول جدولتان که عناوین را نوشته اید با استفاده از منوی فایل گزینPage Setup   را انتخاب نمایید در صفحه ای که ظاهر خواهد شد در بالای صفحه دکمه Setup را بزنید در اینحالت پنجره کوچکی باز خواهد شد در این صفحه شما از گزینه هایی که در بالای پنجره قرار دارد  گزینه آخری Sheet را انتخاب نمایید در این پنجره در محل Print Titles سط اول را Row to Repeat at up: در این گزینه سطری را که می خواهید در تمام صفحه ها بیفتد را انتخاب کنید و دکمه Ok را بزنید اگر از جدولتان پرینت بگیرید خواهید دید که در تمام صفحات سطر اول یکسان است.

اطلاعات در Excel اطلاعات در محیط Excel میتوانند اعداد ، حروف ، تاریخ ، زمان و یادداشت باشند که در زیر به شرح تک تک آنها میپردازیم :

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

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

1- با استفاده از کلید های عددی موجود در بالای حروف

 2- با استفاده از کلید های موجود در سمت راست صفحه کلید در مد Num Lock. ( در صورتی که اعداد این قسمت کار میکنند که کلید Num Lock روشن باشد .) علاوه بر ارقام 0 تا 9 میتوان نمادهای خاص + و – و ، و . و $ و % و E و e را وارد نمود. نکته Excel به طور صحیح علائم کاما و دلار را قبول نموده و اعداد را در نماد علمی نیز دریافت کند. نکته هنگامی که از علائم دلار ، درصد یا کاما استفاده میشود، Excel قالب بندی اعداد را تغییر میدهد.

برخوردExcel با اعداد :

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

 2- اگر طول عدد به اندازه چند کاراکتر بزرگتر از سلول باشد اندازه سلول بزرگ شده تا عدد در آن بگنجد.

 3- اگر طول اعداد برای خانه مورد نظر بزرگ باشد یکی از اتفاقات زیر رخ میدهد :

 الف- اعداد در نماد علمی نمایش داده میشوند.

 ب- عدد گرد میشود.

 ج- سلول با یکسری از علائم # پر میشود.

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

 2- اطلاعات متنی : میتوانیم در سلولهای Excel هر متن دلخواهی را چه به فارسی و چه انگلیسی تایپ کنیم.

 توانایی Excel برای در نظر گرفتن اعداد مثل متن : اگر عددی مثل 2/6 را وارد کنیم ، پس از Enter کردن ، Excel، 6 مارس را نمایش میدهد. یعنی این اعداد را به تاریخ در نظر میگیرد. برای اینکه این اعداد با همین قالب نوشته شد قبل از آن علامت (") آپستروف قرار میدهیم. یعنی بنویسیم ( "6/2 )

 3- اطلاعات از نوع تاریخ : اگر در سلول Excel یک تاریخ به فرم dd-mm-yy یا dd/mm/yy وارد کنیم به طور اتوماتیک قالب سلول تبدیل به قالب تاریخ شده و خط فاصله ها تبدیل به ( / ) شده و سال در چهار رقم نمایش داده می شود.

4- اطلاعات از نوع زمان : میتوانیم در سلول Excel یک زمان را با فرمت H:M:S وارد کنیم که قالب سلول به طور اتوماتیک زمان میشود.

 5- اطلاعات از نوع یادداشت : این نوع اطلاعات توضیحات یا یادداشتهایی هستند که بر روی سلول ظاهر شده و در مورد آن توضیح می دهند. فرمول نویسی در Excel عملگر های Excel •عملگر های محاسباتی : عملگر های محاسباتی به ترتیب تقدم ها عبارتند از : ( ^ ) توان و ( % ) درصد و ( * , /) ضرب و تقسیم و ( + و -) جمع و تفریق •عملگر های رشته ای : تنها عملگر رشته ای ( & ) است. از این عملگر برای ترکیب رشته ها استفاده میشود. • عملگر های آدرس : از این عملگر زمانی استفاده میشود که بخواهیم محدوده ای را مشخص کنیم که ابتدا آدرس ابتدای محدوده را نوشته و سپس علامت ( : ) را میگذاریم و بعد آدرس انتهای محدوده را مینویسیم.

 ( از این عملگر در درسهای آتی استفاده میکنیم ) ایجاد فرمول و مشاهده نتیجه : در Excel به چند روش میتوان فرمولها را نوشت :

1- در خط فرمول ( یا در سلول ) قبل از فرمول علامت = را تایپ کرده سپس فرمول را تایپ میکنیم و بعد Enter میزنیم.

 2- فرمول را در خط فرمول یا و بعد علامت = در خط فرمول سلول نوشته ( بدون علامت = ) سپس بر روی علامت میکنیم.

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

3- در خانه ای که میخواهیم فرمول نوشته شود، Click میکنیم ، علامت تساوی را تایپ کرده ، سپس با ماوس بر روی خانه ای که میخواهیم عمل بر روی آن انجام شود Click میکنیم ، سپس عملگر را تایپ کرده و سپس بر روی عملوند بعدی Click میکنیم. فرض کنید در سلول A1 عدد 2 و در سلول A2 عدد 5 را نوشتیم و میخواهیم در سلول A3 حاصل جمع این دو را بدست آوریم ، برای این کار ابتدا در سلول A3 علامت = را تایپ کرده سپس بر روی سلول A1 ( اولین عملوند ) کلیک کرده تا آدرس آن در سلول A3 نوشته شود.

 سپس علامت + ( عملگر ) را تایپ کرده و بعد بر روی سلول A2 ( دومین عملوند) کلیک می کنیم و در انتها Enter می کنیم.

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

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

 روش دوم : بر روی سلول double Click کرده و ویرایش را انجام می دهیم.

 انتخاب یک یا چند خانه

 1- توسط صفحه کلید : - برای انتخاب یک خانه کافی است با جهت نما (Arrow key)بر روی آن برویم.

 - برای انتخاب تعدادی خانه مجاورکافی است دکمه Shift را پایین نگه داشته و با کلید های جهت نما ( Arrow Key)بر روی آن حرکت کنیم.

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

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

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

 2- توسط ماوس : - برای انتخاب یک خانه کافی است با ماوس بر روی آن کلیک کنیم.

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

 - برای انتخاب یک سطر کافی است روی شماره سطر Click کنیم.

 - برای انتخاب یک ستون کافی است روی حرف ستون Click کنیم.

 - برای انتخاب یک کاربرگ کافی است از منوی Edit ، گزینه Select All را انتخاب کنیم.

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

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

روش اول: 1- انتخاب منوی Edit 2- انتخاب Undo

 روش دوم : فشردن همزمان کلیدهای ctrl+z

 روش سوم : استفاده از آیکون Undo در نوار ابزار Standard ( ) دستور Redo : با این دستور می توانیم عمل Undo را برگردانیم .

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

 روش اول : 1- انتخاب منوی Edit 2- انتخاب Redo

 روش دوم : فشردن همزمان کلیدهای ctrl+y

 روش سوم : استفاده از آیکون Redo در نوار ابزار نسخه برداری و انتقال سلولها نسخه برداری از سلولها : 1- موضوعات مورد نظر را انتخاب میکنیم.

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

الف- استفاده از آیکون Copy ( )

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

ج- Right Click بر روی موضوع و انتخاب گزینه Copy

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

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

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

 الف- استفاده از آیکون Paste ( )

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

 ج- Right Click بر روی موضوع و انتخاب گزینه Paste

 د- فشردن همزمان کلید های Ctrl+V انتقال سلولها : 1- انتخاب موضوعات مورد نظر 2- به یکی از روشهای زیر Cut را انتخاب میکنیم : الف- استفاده از آیکون Cut ( ) ب- انتخاب منوی Edit، گزینه Cut ج- Right Click بر روی موضوع مورد نظر و انتخاب گزینه Cut د- فشردن همزمان کلیدهای Ctrl +X 3- Click بر روی محل انتقال موضوعات

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

 برای حذف یک سلول به یکی از روشهای زیر عمل میکنیم :

 روش اول : 1- انتخاب موضوعات 2- انتخاب منوی Edit 3- انتخاب گزینه Delete

 روش دوم : 1- انتخاب موضوعات 2- Right Click بر روی خانه 3- انتخاب گزینه Delete با انتخاب گزینه Delete پنجره ای ظاهر میشود که شامل چهار گزینه زیر است ( در این پنجره میتوانیم تعیین کنیم که محل خالی سلول حذف شده با کدام یک از سلولهای مجاور پر شود. ) :

 الف- Shift Cell Left : باعث انتقال خانه سمت راست خانه پاک شده به جای آن میشود.

 ب- Shift Cells Up : خانه زیرین خانه پاک شده را بجای آن منتقل میکند.

 ج- Entire Row : سطر زیرین خانه پاک شده را به جای سطری که خانه پاک شده در آن قرار دارد منتقل میکند.

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

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

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

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

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

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

 الف- All : هم محتوای خانه ، هم Format خانه و هم توضیحات را پاک میکند.

 ب- Format : فقط Format خانه های انتخابی را پاک میکند.

 ج- Comment : فقط توضیحات را پاک میکند.

 د- Content : فقط محتوای خانه های انتخابی را پاک میکند .