کلید های میانبر در اکسل 2007 SHORTCUT IN EXCEL 2007

 

Microsoft Excel 2007 shortcut and function keys

 

---

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

CTRL combination shortcut keys

Key	Description
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 objects, displaying objects, and displaying placeholders for objects.
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 object, 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	Description
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	Description
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 object is selected, CTRL+SHIFT+SPACEBAR selects all objects 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

توابع آماری Statistical functions در excel 2007

Statistical functions

Function	Description
AVEDEV	Returns the average of the absolute deviations of data points from their mean انحراف متوسط داده ها- در این تابع ابتدا قدرمطلق فاصله هر متغییر از میانگین سنجیده سپس حاصل جمع به تعداد تقسیم می گردد .
AVERAGE	Returns the average of its arguments میانگین داده ها در این تابع سلول های خالی و داده های متنی محاسبه نمی شوند.
AVERAGEA	Returns the average of its arguments, including numbers, text, and logical values میانگین کل :  انواع داده ها ی متنی و عددی در محاسبه منظور می گردند.   TRUE 1 و FALSE  0
AVERAGEIF	Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria محاسبه میانگین با احتساب یک شرط
AVERAGEIFS	Returns the average (arithmetic mean) of all cells that meet multiple criteria. میانگین محدوده با احتساب چند شرط
BETADIST	Returns the beta cumulative distribution function
BETAINV	Returns the inverse of the cumulative distribution function for a specified beta distribution
BINOMDIST	Returns the individual term binomial distribution probability
CHIDIST	Returns the one-tailed probability of the chi-squared distribution
CHIINV	Returns the inverse of the one-tailed probability of the chi-squared distribution
CHITEST	Returns the test for independence
CONFIDENCE	Returns the confidence interval for a population mean
CORREL	Returns the correlation coefficient between two data sets
COUNT	Counts how many numbers are in the list of arguments شمارش تعداد سلول های حاوی عدد
COUNTA	Counts how many values are in the list of arguments شمارش سلول های غیر خالی
COUNTBLANK	Counts the number of blank cells within a range شمارش سلول های خالی
COUNTIF	Counts the number of nonblank cells within a range that meet the given criteria شمارش تعداد سلول های حاوی شرط
COVAR	Returns covariance, the average of the products of paired deviations محاسبه کوواریانس
CRITBINOM	Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
DEVSQ	Returns the sum of squares of deviations
EXPONDIST	Returns the exponential distribution
FDIST	Returns the F probability distribution
FINV	Returns the inverse of the F probability distribution
FISHER	Returns the Fisher transformation
FISHERINV	Returns the inverse of the Fisher transformation
FORECAST	Returns a value along a linear trend
FREQUENCY	Returns a frequency distribution as a vertical array
FTEST	Returns the result of an F-test
GAMMADIST	Returns the gamma distribution
GAMMAINV	Returns the inverse of the gamma cumulative distribution
GAMMALN	Returns the natural logarithm of the gamma function, Γ(x)
GEOMEAN	Returns the geometric mean
GROWTH	Returns values along an exponential trend
HARMEAN	Returns the harmonic mean
HYPGEOMDIST	Returns the hypergeometric distribution
INTERCEPT	Returns the intercept of the linear regression line
KURT	Returns the kurtosis of a data set محاسبه کشیدگی توزیع
LARGE	Returns the k-th largest value in a data set کا امین عدد بزرگ توزیع - اگر کا عدد یک باشد همان ماکس را نمایش می دهد
LINEST	Returns the parameters of a linear trend
LOGEST	Returns the parameters of an exponential trend
LOGINV	Returns the inverse of the lognormal distribution
LOGNORMDIST	Returns the cumulative lognormal distribution
MAX	Returns the maximum value in a list of arguments بزرگترین عدد محدوده
MAXA	Returns the maximum value in a list of arguments, including numbers, text, and logical values
MEDIAN	Returns the median of the given numbers
MIN	Returns the minimum value in a list of arguments
MINA	Returns the smallest value in a list of arguments, including numbers, text, and logical values
MODE	Returns the most common value in a data set
NEGBINOMDIST	Returns the negative binomial distribution
NORMDIST	Returns the normal cumulative distribution رتبه درصدی یک داده را در یک توزیع نرمال ،با میانگین و انحراف استاندارد مشخص نمایش می دهد
NORMINV	Returns the inverse of the normal cumulative distribution یافتن نمره خام یا استاندارد معادل با رتبه درصدی در یک توزیع نرمال( رتبه درصدی می گیرد ، داده را نمایش می دهد )
NORMSDIST	Returns the standard normal cumulative distribution رتبه درصدی یک داده را در یک توزیع نرمال استاندارد، نمایش می دهد
NORMSINV	Returns the inverse of the standard normal cumulative distribution یافتن نمره خام یا استاندارد معادل با رتبه درصدی در یک توزیع نرمال استاندارد
PEARSON	Returns the Pearson product moment correlation coefficient ضریب همبستگی پیرسون را نمایش می دهد این شاخص که ضریب معتبری برای تعیین رابطه بین متغییر هاست تنها جهت و شدت رابطه بین دو متغییر را نمایش می دهد و نباید تفسیر آن بر حسب درصد باشد.به عنوان مثال ضریب 70% ،هفتاد درصد از رابطه بین دو متغییر را تبیین نمی کند .0
PERCENTILE	Returns the k-th percentile of values in a range نقاط درصدی همانند رتبه های درصدی اندازه های ترتیبی هستند و با استفاده از آنها می توان تعیین کرد چه عددی توزیع را به درصدهای مختلف تقسیم می کند
PERCENTRANK	Returns the percentage rank of a value in a data set محاسبه رتبه درصدی یک داده در یک توزیع- این رتبه به ما میگوید که چند درصد نمره ها در توزیع در زیر آن قرار گرفته اند
PERMUT	Returns the number of permutations for a given number of objects
POISSON	Returns the Poisson distribution
PROB	Returns the probability that values in a range are between two limits
QUARTILE	Returns the quartile of a data set محاسبه چارک های توزیع
RANK	Returns the rank of a number in a list of numbers رتبه بندی داده های توزیع
RSQ	Returns the square of the Pearson product moment correlation coefficient
SKEW	Returns the skewness of a distribution میزان کجی منحنی توزیع را نمایش می دهد
SLOPE	Returns the slope of the linear regression line
SMALL	Returns the k-th smallest value in a data set کا امین عدد کوچک مجموعه را نمایش می دهد .0
STANDARDIZE	Returns a normalized value محاسبه نمره زی یا زد یا استاندارد سازی نمره فرض کنید در یک آزمون که میانگین و انحراف استاندارد آن به ترتیب 75 و 10 می باشد نمره شما 95 شده است .هنگامی که این نمره را به زد تبدیل می کنید ، تابع عدد 2 را نمایش می دهد که بیانگر عملکرد شما در آزمون با توجه به توزیع نمره هاست.یعنی نمره شما دو انحراف استاندارد بالاتر از میانگین کلاس قرار دارد. 0
STDEV	Estimates standard deviation based on a sample انحراف معیار نمونه
STDEVA	Estimates standard deviation based on a sample, including numbers, text, and logical values انحراف معیار نمونه با احتساب متن و عدد
STDEVP	Calculates standard deviation based on the entire population انحراف معیار کل جمعیت
STDEVPA	Calculates standard deviation based on the entire population, including numbers, text, and logical values انحراف معیار کل جمعیت با احتساب متن و عدد
STEYX	Returns the standard error of the predicted y-value for each x in the regression
TDIST	Returns the Student's t-distribution این تابع مقدار عددی ، درجه آزادی و دامنه را دریافت کرده و احتمال شانسی بودن اختلاف بین میانگین نمونه و جامعه را نشان می دهد
TINV	Returns the inverse of the Student's t-distribution عدد موجود در جدول t  را بر اساس درجه آزادی و p  یا احتمال شانسی بودن اختلاف بین میانگین نمونه و جامعه  نمایش می دهد.در حقیقت این تابع بجای جدول t عمل می کند یعنی کاربر درجه آزادی وp  را وارد کرده و سپس تابع عدد مورد نظر را نمایش میدهد.اگر عدد بدست آمده از  t  محاسبه شده کوچکتر باشد فرض صفر رد می شود .
TREND	Returns values along a linear trend
TRIMMEAN	Returns the mean of the interior of a data set
TTEST	Returns the probability associated with a Student's t-test
VAR	Estimates variance based on a sample محاسبه واریانس نمونه ای از مجموعه
VARA	Estimates variance based on a sample, including numbers, text, and logical values واریانس گیری از تمام عناصر ( متن و عدد)0
VARP	Calculates variance based on the entire population محاسبه واریانس کل مجموعه
VARPA	Calculates variance based on the entire population, including numbers, text, and logical values
WEIBULL	Returns the Weibull distribution
ZTEST	Returns the one-tailed probability-value of a z-test

 

منابع

1. راهنمای اکسل ۲۰۰۷
2. کتاب - احتمالات و آمار کاربردی در روانشناسی و علوم تربیتی - دکتر علی دلاور
3. جزوه توابع آماری اکسل - بهرام صمدیان AMAR80.BLOGFA.COM      

 

قالب بندی شرطی در اکسل 2007

برای ایجاد یک قالب بندی همراه با شرط در اکسل ۲۰۰۷ ابتدا محدوده مورد نظر را انتخاب کرده سپس از مسیر    HOME > CONDITIONAL FORMATTING  را فعال می کنیم . این ابزار دو بخش مجزای تعیین شرط را طراحی کرده است ؛ که اولی همان گزینه CUSTOM  و دومی گزینه TOP 10  در نسخه 2003 می باشند . در حقیقت در اولی کاربران شرط خاصی را برای قالب بندی مشخص می کنند و در دومی درصد یا تعداد خاصی را مشاهده خواهند کرد .

1- HIGHLIGHT CELLS RULES

2-  TOP/BUTTON RULES

 

 

در بخش اول شرط های بزرگتر ،کوچکتر، مساوی ، بین و ... و در بخش دوم درصد یا تعدادی از بالا یا پایین لیست را رنگی می کند . یکی از گزینه های جدید above , below average است که داده های بالاتر وپایین تر از شاخص میانگین را رنگی می کند.

یکی دیگر از ویژگی های جدید اکسل ۲۰۰۷، یافتن سلول های تکراری و غیر تکراری است.گزینه duplicate value  این وظیفه را به خوبی انجام میدهد.جالب اینجاست که بدانید دیگر مانند اکسل ۲۰۰۳ تعداد شرط ها به ۳ محدود نمی شود و کاربران اکسل ۲۰۰۷ قادرند به صورت نامحدود ، محدوده خود را ، با قالب بندی های شرطی ، مشخص کنند .

حذف داده های تکراری

 

قابلیت جدید اکسل 2007 در حذف داده های تکراری یکی از مزایای انکارناپذیر این صفحه گسترده است.

مسیر اجرا : انتخاب محدوده مورد نظر      DATA>REMOVE DUPLICATES حال کلید SELECT ALL>OK

 

 

 

تغییرات اساسی اکسل 2007 و ویژگی های محاسباتی آن

 

Worksheet and workbook specifications and limits

ویژگی های محاسباتی اکسل 2007

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

Feature	Maximum limit
Open workbooks	Limited by available memory and system resources
Worksheet size	1,048,576 rows by 16,384 columns
Column width	255 characters
Row height	409 points
Page breaks	1,026 horizontal and vertical تعداد کاراکترهای درون یک سلول
Total number of characters that a cell can contain	32,767 characters
Characters in a header or footer	255
Sheets in a workbook	Limited by available memory (default is 3 sheets)   اکسل 2007 از 16 میلیون رنگ برای نمودارهایش استفاده می کند
Colors in a workbook	16 million colors (32 bit with full access to 24 bit color spectrum)
Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook	Limited by available memory
Unique cell formats/cell styles	64,000
Fill styles	32
Line weight and styles	16
Unique font types	1,024 global fonts available for use; 512 per workbook
Number formats in a workbook	Between 200 and 250, depending on the language version of Excel that you have installed
Names in a workbook	Limited by available memory
Windows in a workbook	Limited by available memory
Panes in a window	4
Linked sheets	Limited by available memory
Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.)	Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario	32
Adjustable cells in Solver	200
Custom functions	Limited by available memory
Zoom range	10 percent to 400 percent
Reports	Limited by available memory
Sort references	64 in a single sort; unlimited when using sequential sorts
Undo levels	100
Fields in a data form	32
Workbook parameters	255 parameters per workbook
Filter drop-down lists	10,000

 

Calculation specifications and limits

Feature	Maximum limit
Number precision	15 digits
Largest number allowed to be typed into a cell	9.99999999999999E+307
Largest allowed positive number	1.79769313486231E+308
Smallest allowed negative number	-2.2251E-308
Smallest allowed positive number	2.229E-308
Largest allowed negative number	-1E-307
Length of formula contents	8,192 characters
Internal length of formula	16,384 bytes
Iterations	32,767
Worksheet arrays	Limited by available memory
Selected ranges	2,048
Arguments in a function	255
Nested levels of functions	64
User defined function categories	255
Number of available worksheet functions	341
Size of the operand stack	1,024
Cross-worksheet dependency	64,000 worksheets that can refer to other sheets
Cross-worksheet array formula dependency	Limited by available memory
Area dependency	Limited by available memory
Area dependency per worksheet	Limited by available memory
Dependency on a single cell	4 billion formulas that can depend on a single cell
Linked cell content length from closed workbooks	32,767
Earliest date allowed for calculation	January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation	December 31, 9999
Largest amount of time that can be entered	9999:59:59

 

Charting specifications and limits

Feature	Maximum limit
Charts linked to a worksheet	Limited by available memory
Worksheets referred to by a chart	255
Data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.) in one chart	255
Data points (data points: Individual values that are plotted in a chart. Related data points make up a data series. Data points are represented by bars, columns, lines, slices, dots, and other shapes. These shapes are called data markers.) in a data series for 2-D charts	32,000
Data points in a data series for 3-D charts	4,000
Data points for all data series in one chart	256,000

 

PivotTable and PivotChart report specifications and limits

Feature	Maximum limit
PivotTable reports (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) on a sheet	Limited by available memory
Unique items per field	1,048,576
Row (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) or column fields (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) in a PivotTable report	Limited by available memory
Report filters in a PivotTable report	256 (may be limited by available memory)
Value fields in a PivotTable report	256
Calculated item (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) formulas in a PivotTable report	Limited by available memory
Report filters in a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.)	256 (may be limited by available memory)
Value fields in a PivotChart report	256
Calculated item formulas in a PivotChart report	Limited by available memory
Length of the MDX name for a PivotTable item	32,767
Length for a relational PivotTable string	32,767

 

Shared workbook specifications and limits

Feature	Maximum limit
Users who can open and share a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.) at the same time	256
Personal views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a shared workbook	Limited by available memory
Days that change history (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.) is maintained	32,767 (default is 30 days)
Workbooks that can be merged at one time	Limited by available memory
Cells that can be highlighted in a shared workbook	32,767
Colors used to identify changes made by different users when change highlighting is turned on	32 (each user is identified by a separate color; changes made by the current user are highlighted with navy blue)

اکسل 2007 EXCEL 2007

What's new in Microsoft Office Excel 2007

یکی از شاهکار های جدید مایکروسافت را به جرات می توان اکسل ۲۰۰۷ از سری آفیس ۱۲ دانست. در این سری جدید تعداد سطرها از 65536 به 1048576 و تعداد ستون ها از 256 به 16384 تغییر یافته است.

این افزایش قابلیت های فراوانی را در اکسل ۲۰۰۷ برای کاربران مهیا می کند .محیط اکسل ۲۰۰۷ را در زیر مشاهده می کنید. 

 سه آیتم به محیط 2007 اضافه شده است که به ترتیب :

1- OFFICE BUTTON  که بخش فایل را پوشش می دهد :

 

کلید EXCEL OPTIONS  هم در این بخش قرار دارد:

 

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

 

 ۳- نوار ابزار ریبون RIBBON :

 این نوار ابزار ساختار، نتیجه محوری (RESULTS ORIENTED) داشته که بجای موضوع محوری در 2003 ،سرعت دسترسی به ابزارها را بیشتر می کند .

 

 

 

جزوه اکسل پیشرفته

دوستان برای گزارش گیری و تحلیل داده های لیست ها، در این جزوه چند روش ارائه شده است:

۱- استفاده از تابع SUBTOTAL  فیلتر ها

2- تابع SUMIF , COUNTIF

3- توابع پایگاه داده  DATABASE FUNCTIONS

DSUM DMAX DMIN ,...

4- استفاده از توابع آرایه ای  مثل FREQUENCY , TRANSPOSE,   SUM & IF

5- استفاده از جداول محوری PIVOT TABLE

استفاده از ابزار DATA VALIDATION , TEXT TO COLUMN,...

توابع روند سازی ROUND , ROUNDDOWN,ROUNDUP,ODD,EVEN,FLOOR,CEILING,TRUNC.,INT

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

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

علی توضیح اسفند ۸۶

 

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

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

مثال : در یک فرم سه شیء TEXTBOX , REFEDIT, COMMANDBUTTON قرار دارند فرض کنید قصد داریم آدرس انتخاب شده توسط REFEDIT   را با فشار دادن کلید در TEXTBOX نمایش دهیم در بخش کد مربوط به کلید متن زیر را وارد می کنیم :

()Private Sub CommandButton1_Click

TextBox1 = RefEdit1.Value
End Sub

راهنمای اکسل

RefEdit Control

On a user form, this control displays the address of a range of cells that you've entered or selected on one or more worksheets. To select a range, click the button in the control to collapse the user form, select the range, and then click the button in the control again to expand the user form.

If the RefEdit control is embedded on a worksheet, you can link the contents of the control to a cell on any worksheet in that workbook.

Remarks

The default property for a RefEdit control is Value.

The default event for a RefEdit control is BeforeDragOver.

You cannot use a RefEdit control on a modeless user form. You can use the ShowModal property to set a user form to modal.

Example

This example sets the control tip text for the RefEdit1 control in UserForm1, and then it displays the user form.

UserForm1.RefEdit1.ControlTipText = _
    "The selected range of data in the worksheet"
UserForm1.Show
		

Later, the user clicks CommandButton1 in UserForm1. This example of an event-handling procedure displays the address of the selected worksheet range and then ends the program.

Private Sub CommandButton1_Click

    MsgBox RefEdit1.Value
    End
End Sub

 

تمرین جداول محوری - گزارش گیری و ساخت گزارش

با توجه به فایل زیر به سوالات زیر پاسخ دهید :

تمرین

کاربرگ دریافتی :

۱- جمع دریافتی های هر فرد چقدر است ؟

۲- جمع دریافتی های هر فرد در هر ماه چقدر است ؟

۳- میانگین دریافتی های هر فرد در هر ماه در هر بانک چقدر است ؟ 

کاربرگ انبار :

۱- از هر کالا به هر اندازه و جنس چقدر وارد انبار شده و چقدر خارج شده است؟

۲- هر وارد کننده از هر کالا چه میزانی وارد کرده است؟

 

کاربرگ لوازم التحریر

۱- هر فروشگاه از هر کالا چقدر فروخته است ؟

۲- هر فروشگاه در هر شهر از هر کالا چقدر فروخته است ؟

جستجوی یک مقدار منتناظر در جدول VLOOKUP

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

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

=VLOOKUP(نام مورد نظر ، آدرس کل محدوده جدول ، شماره فیلد شماره ماشین ، عدد صفر)

VLOOKUP( "ALI",A1:B10;2;0)

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

VLOOKUP

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The V in VLOOKUP stands for "Vertical."

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value    is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array    is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

• If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

• You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

• The values in the first column of table_array can be text, numbers, or logical values.

• Uppercase and lowercase text are equivalent.

Col_index_num    is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup    is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

• If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
• If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
• If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

 

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic. Do not select the row or column headers.

    

   Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

The example uses values for air at 1 atm pressure.

1

2

3

4

5

6

7

8

9

10

A	B	C
Density	Viscosity	Temperature
0.457	3.55	500
0.525	3.25	400
0.616	2.93	300
0.675	2.75	250
0.746	2.57	200
0.835	2.38	150
0.946	2.17	100
1.09	1.95	50
1.29	1.71	0
Formula	Description (Result)
=VLOOKUP(1,A2:C10,2)	Looks up 1 in column A, and returns the value from column B in the same row (2.17)
=VLOOKUP(1,A2:C10,3,TRUE)	Looks up 1 in column A, and returns the value from column C in the same row (100)
=VLOOKUP(.7,A2:C10,3,FALSE)	Looks up 0.746 in column A. Because there is no exact match in column A, an error is returned (#N/A)
=VLOOKUP(0.1,A2:C10,2,TRUE)	Looks up 0.1 in column A. Because 0.1 is less than the smallest value in column A, an error is returned (#N/A)
=VLOOKUP(2,A2:C10,2,TRUE)	Looks up 2 in column A, and returns the value from column B in the same row (1.71)