Welcome to Office College, xiaoyang visit here waiting for you!
Today, we continue to learn together with you in all commonly used Excel spreadsheet formulas (functions) of the detailed usage and master the formula, many of our work will greatly improve the efficiency, we must learn yo, there do not understand can go to the Office College Questions (ask.officexy.com) here to ask Oh, we have progress, I will be pleased got:)
This lecture, we learn about Vlookup
The number of columns is not required, it is only described to facilitate the inconvenience
Column 1 column 2 3 4 5 6
元月 1020304050
February 8090100110120
March 9769455177
Enter search Month: March
Need to pick out the columns: 4
The result: 45
= VLOOKUP (G11, C6: H8, G12, FALSE)
Features
This function is the line in the table on the left to find specific content of the title
When found, it then pick out the line corresponding to the specified column of the cell contents.
Syntax
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Lookup_value for the need to find the first column of the array values. Lookup_value can be a value, reference or text string.
Table_array for the need to find data in which the data sheet. You can use the name of a reference area or areas, such as a database or data list.
If range_lookup is TRUE, then the first column of table_array value must be in ascending order: ..., -2, -1,0,1,2, ...,-Z, FALSE, TRUE; otherwise, VLOOKUP function can not return the correct value. If range_lookup is FALSE, table_array do not have to be sorted.
Through the "Data" menu "Sort" and select "Ascending", can be sorted in ascending numerical order.
Table_array values in the first column can be text, numbers or logical values.
Text is not case sensitive.
Col_index_num be returned to table_array matching value in column number. Col_index_num to 1, the return value in the first column table_array; col_index_num 2, the return value table_array the second column, and so on. If col_index_num less than 1, VLOOKUP returns the value of the # VALUE!; If col_index_num greater than the number of columns table_array, VLOOKUP returns the # REF!.
Range_lookup is a logical value that specifies the function VLOOKUP returns an exact match or approximate match. If TRUE or omitted, an approximate match is returned, that is, if you can not find an exact match, it returns the maximum value of less than lookup_value; if range_value is FALSE, VLOOKUP function will return an exact match. If not, it returns the error value # N / A.
Help
If the function VLOOKUP can not find the lookup_value, and range_lookup is TRUE, then use the maximum value less than or equal lookup_value.
If the first column of table_array lookup_value less than the minimum value, VLOOKUP returns the # N / A.
If the function VLOOKUP can not find the lookup_value and range_lookup is FALSE, VLOOKUP function returns the error value # N / A.
Format
There is no specific format
Example 1
The following examples are specified under the name and find a value based on the month.
= VLOOKUP () is used to look down along the first column name specified.
Difficulty is how to find the right month specified.
Solve this problem is to use = MATCH () function.
Function = MATCH () list of names by using the search to find the corresponding month. And projections for the month position in the list.
Unfortunately, because of the month search and find a list of the range of values ranging from wide.
Function = MATCH () function returns a number less than the number we need 1, so the +1 in the formula used to adjust.
Function = VLOOKUP () now use the function = MATCH () get adjusted n, in the name of the line corresponding to the line of the right to find the corresponding n-column input cell.
Function = VLOOKUP () in the final use of FALSE, so the left side do not sort the header row.
January February March
Cheng Hong-zhou 108097
Liu 209069
Cheng Long 3010045
Cheng-kun 4011051
chengxiang 50 120 77
Enter search name: Cheng Long
Enter search Month: March
The result: 45
= VLOOKUP (F60, C54: F58, MATCH (F61, D53: F53, 0) +1, FALSE)
3 = MATCH (F61, D53: F53, 0)
Example 2 This example uses the function = VLOOKUP () to find different car manufacturers in different parts of the value.
Function = VLOOKUP () scan down the column header row F and find the corresponding name in the C line of accessories.
Find the parts, the function VLOOKUP MATCH function to find the location according to the corresponding components to find the price.
Use absolute references in formulas, copying formulas in order to ensure that the function moves = HLOOKUP () and = MATCH () does not change the scope of reference.
Accessories manufacturers to find the value of the form
Toyota Spark Plug Toyota Ford Mercedes-Benz £ 50
£ 600 Mercedes-Benz transmission gearbox 500 450 600
Ford engine, the engine 10001200800 £ 1,200
Mercedes-Benz steering wheel 250 350 275 £ 275
Ford Spark Plug spark plug 507045 £ 70
Ford brake pads brake pads £ 290 300 290 310
Toyota gearbox £ 500
£ 1,200 Ford engine
= VLOOKUP (C80, F74: I78, MATCH (B80, G73: I73, 0) +1, FALSE)
Example 3
The following example is a building materials dealers offer a discount rate of the number of different procurement
Price list shows the brick, wood and glass unit.
Discount table provides a number of different products with different purchasing discount rate.
Procurement is the procurement budget table.
All results are displayed in the procurement budget table.
Name list in the C column.
The unit price is obtained from the price list.
FALSE option that product name in the table is not sorted in price order.
FALSE forced to use an exact match search. If not found, the function displays an error.
Discount is available from the discount table
If the purchase quantity discount table with a value match, the function = VLOOKUP will find in the discount table, the correct match discount.
TRUE option indicates the number of purchases through a discount table in ascending order sorting.
Use TRUE to allow fuzzy matching. If the purchase amount of the discount table does not find a match value, below it a smaller value will be used.
Such as the purchase quantity of 125 and 100 will be down to match the corresponding column and use a discount rate of 100.
Discount Table
Brick Wood Glass Price List
Brick £ 2 1 0% 0% 0%
Wood £ 1 100 6% 3% 12%
Glass £ 3 300 8% 5% 15%
Procurement Table
Project Procurement Quantity Unit Price Discount Total
Brick 125 £ 2 6% £ 235
Wood 200 £ 1 3% £ 194
Glass 150 £ 3 12% £ 396
Brick 225 £ 2 6% £ 423
Wood 50 £ 1 0% £ 50
Glass 500 £ 3 15% £ 1,275
The formula is:
Unit E118: = VLOOKUP (C118, C106: D108, 2, FALSE)
Discount F118: = VLOOKUP (D118, F106: I108, MATCH (C118, G105: I105, 0) +1, TRUE)
Total G118: = (D118 * E118) - (D118 * E118 * F118)
Example 4
The example uses an atmospheric pressure of the air value.
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)
2.17 in the A column to find 1 and B from the same row column return value (2.17) = VLOOKUP (1, B128: D136, 2)
100 in the A column to find 1, and C from the same row column return value (100) = VLOOKUP (1, B128: D136, 3, TRUE)
# N / A in the A column to find 0.746. A column because there is no exact match, it returns an error value (# N / A) = VLOOKUP (0.7, B128: D136, 3, FALSE)
# N / A in the A column to find 0.1. A column for the minimum value is less than 0.1, so return an error value (# N / A) = VLOOKUP (0.1, B128: D136, 2, TRUE)
1.71 Find in the A column 2 and column B from the same line in the return value (1.71) = VLOOKUP (2, B128: D136, 2, TRUE)
2010年12月16日星期四
How to use of timers in the Excel
Excel 97 used in the Add-Ins "time saving" it? Unfortunately, its source is encrypted, and now introduced to upload a document to achieve it.
There is a way in Office is application.ontime, specific functions are as follows:
expression.OnTime (EarliestTime, Procedure, LatestTime, Schedule)
If you would like further information, please see the Excel help.
This function is used to arrange for a process run at specific times in the future, (the date for a specified period of time, but also after the specified time period). Through this function in Excel where we can write your own regular program. Here are two examples to illustrate it.
1. In the afternoon, when a dialog box 17:00:00.
Sub Run_it ()
Application.OnTime TimeValue ("17:00:00"), "Show_my_msg"
'Set the timer activated in 17:00:00, activated running Show_my_msg.
End Sub
Sub Show_my_msg ()
msg = MsgBox ("Now is 17:00:00!", vbInformation, "Custom Information")
End Sub
2. Imitate Excel 97 in the "auto save the macro", where once in 5 seconds time
Sub auto_open ()
MsgBox "Welcome, in this document, once every 5 seconds to save the tips!", VbInformation, "Please pay attention!"
Call runtimer 'run automatically when you open a document
End Sub
Sub runtimer ()
Application.OnTime Now TimeValue ("00:00:05"), "saveit"
'Now TimeValue ("00:15:00") specified in the current time more than 5 seconds to start running Saveit the process.
End Sub
Sub SaveIt ()
msg = MsgBox ("Friends, you've worked a long time, save you now?" & Chr (13) _
& "Choice: immediately save" & Chr (13) _
& "Select No: not to save" & Chr (13) _
& "Choose to cancel: no longer appears this prompt", vbYesNoCancel 64, "take a break now!")
'Prompt the user to save the current active document.
If msg = vbYes Then ActiveWorkbook.Save Else If msg = vbCancel Then Exit Sub
Call runtimer 'If the user does not choose to cancel once again call Runtimer
End Sub
These are just two simple examples are interested, you can use Application.Ontime this function more useful time to write more programs.
There is a way in Office is application.ontime, specific functions are as follows:
expression.OnTime (EarliestTime, Procedure, LatestTime, Schedule)
If you would like further information, please see the Excel help.
This function is used to arrange for a process run at specific times in the future, (the date for a specified period of time, but also after the specified time period). Through this function in Excel where we can write your own regular program. Here are two examples to illustrate it.
1. In the afternoon, when a dialog box 17:00:00.
Sub Run_it ()
Application.OnTime TimeValue ("17:00:00"), "Show_my_msg"
'Set the timer activated in 17:00:00, activated running Show_my_msg.
End Sub
Sub Show_my_msg ()
msg = MsgBox ("Now is 17:00:00!", vbInformation, "Custom Information")
End Sub
2. Imitate Excel 97 in the "auto save the macro", where once in 5 seconds time
Sub auto_open ()
MsgBox "Welcome, in this document, once every 5 seconds to save the tips!", VbInformation, "Please pay attention!"
Call runtimer 'run automatically when you open a document
End Sub
Sub runtimer ()
Application.OnTime Now TimeValue ("00:00:05"), "saveit"
'Now TimeValue ("00:15:00") specified in the current time more than 5 seconds to start running Saveit the process.
End Sub
Sub SaveIt ()
msg = MsgBox ("Friends, you've worked a long time, save you now?" & Chr (13) _
& "Choice: immediately save" & Chr (13) _
& "Select No: not to save" & Chr (13) _
& "Choose to cancel: no longer appears this prompt", vbYesNoCancel 64, "take a break now!")
'Prompt the user to save the current active document.
If msg = vbYes Then ActiveWorkbook.Save Else If msg = vbCancel Then Exit Sub
Call runtimer 'If the user does not choose to cancel once again call Runtimer
End Sub
These are just two simple examples are interested, you can use Application.Ontime this function more useful time to write more programs.
Macros in Excel is what stuff it in the end
A macro is a set of instructions that tell EXCEL to complete the action specified by the user. Macro is similar to a computer program, but it is being fully operational in EXCEL, we can use a macro to complete the tedious, often repetitive work. Macro to complete the action faster than users themselves do much faster. For example, we can create a macro for each row of the worksheet, enter a date and in each cell within the center-aligned date and format of this border-line application. We can also create a macro, in the "Page Setup" dialog box, specify the print settings and print the document.
Because the impact of macro viruses and the fear of mental programming, many people dare not use the "macro", or do not know when to look for a macro to help. In fact, you may rest assured bold to use, if you only use the "record macro" approach, there are not any hard, just as some operations as recorded by tape recorder, to the use of the time, just run the macro, the system will put the operation and run it again.
Here are the macro applications, as long as the "Record Macro" to help you complete the task, without the need for programming. If you want the recorded macro and then edit it, we should have some knowledge of VBA.
* Set a table for each job requires a fixed form of the header;
* Set the cell into a form has its own style;
* Each print page setup are fixed;
* Frequently or repeatedly enter some fixed content, such as a good format for exclusive business address, personnel lists, etc.;
* Create a formatted table;
* Insert a worksheet or workbook and so on.
Should be noted that, EXCEL and WORD macros in the macro some differences, for the recording operation, it will remember the coordinates of the cell (ie, all references are absolute), so when it comes to position- The operation, to be extra attention. If the phase by using relative references, can be Offset by means of methods, such as the following statement: ActiveCell.Offset (1,0). Range ("A1"). Select the application macro is very wide, just a little bit above, If used together, you will find it to have more content and more flexible application methods.
Because the impact of macro viruses and the fear of mental programming, many people dare not use the "macro", or do not know when to look for a macro to help. In fact, you may rest assured bold to use, if you only use the "record macro" approach, there are not any hard, just as some operations as recorded by tape recorder, to the use of the time, just run the macro, the system will put the operation and run it again.
Here are the macro applications, as long as the "Record Macro" to help you complete the task, without the need for programming. If you want the recorded macro and then edit it, we should have some knowledge of VBA.
* Set a table for each job requires a fixed form of the header;
* Set the cell into a form has its own style;
* Each print page setup are fixed;
* Frequently or repeatedly enter some fixed content, such as a good format for exclusive business address, personnel lists, etc.;
* Create a formatted table;
* Insert a worksheet or workbook and so on.
Should be noted that, EXCEL and WORD macros in the macro some differences, for the recording operation, it will remember the coordinates of the cell (ie, all references are absolute), so when it comes to position- The operation, to be extra attention. If the phase by using relative references, can be Offset by means of methods, such as the following statement: ActiveCell.Offset (1,0). Range ("A1"). Select the application macro is very wide, just a little bit above, If used together, you will find it to have more content and more flexible application methods.
Improve the efficiency of Excel in VBA
Microsoft Office office suite as a wide range of applications, and continuously upgrade the software version, features continuous improvement platform office suite in the Office of the VBA to develop more and more applications, and VBA is a macro language, the operating speed There are very limited. VBA programming method thus directly related to the efficiency of VBA program is running, the paper cited a number of VBA procedures to improve operating efficiency.
Method 1: Try to use the original VBA properties, methods, and the Worksheet Functions
As many as hundred or more Excel objects, object properties, methods, events and more numerous, for starters, they may not all understand, which often resulted in the preparation of programming and Excel object properties, methods, functions the same VBA code, and the operating efficiency of the code is clear and Excel object properties, methods, task speed vary considerably. For example, with the Range property to return CurrentRegion Range object that represents the current area. (Refer to the current area any blank lines and blank columns on the composition of the border area). VBA code for the same functions to be dozens of lines. Therefore, before programming Excel objects as much as possible to understand the properties and methods.
Worksheet function is to increase take full advantage of the speed run extremely effective method. If the average wage demand example:
For Each c In
Worksheet (1). Range ("A1: A1000")
TotalValue = TotalValue + c.Value
Next
AverageValue = TotalValue / Worksheet (1). Range ("A1: A1000"). Rows.Count
The following example code much faster than the above procedures:
AverageValue = Application.WorksheetFunction.Average (Worksheets (1). Range ("A1: A1000"))
Other functions such as Count, Counta, Countif, Match, Lookup, etc., can replace the VBA code the same functionality to improve the running speed.
Method 2: minimize the use of object references, especially in the loop
The properties of each Excel object, method calls through the OLE interface requires one or more calls, the OLE calls takes time, reduce the use of object references to speed up VBA code to run. For example,
1. Use With statement.
Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.Name = "Pay"
Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.FontStyle = "Bold" ...
The following statement is faster than the above
With Workbooks (1). Sheets (1). Range ("A1: A1000"). Font
. Name = "Pay"
. FontStyle = "Bold"
...
End With
2. Using the object variable.
If you find an object reference to be used multiple times, then you can use this object to set the object variable Set to reduce access to the object. Such as:
Workbooks (1). Sheets (1). Range ("A1"). Value = 100
Workbooks (1). Sheets (1). Range ("A2"). Value = 200
The following code is faster than the above:
Set MySheet = Workbooks (1). Sheets (1)
MySheet.Range ("A1"). Value = 100
MySheet.Range ("A2"). Value = 200
3. In the loop to minimize access to the object.
For k = 1 To 1000
Sheets ("Sheet1"). Select
Cells (k, 1). Value = Cells (1,1). Value
Next k
The following code is faster than the above:
Set TheValue = Cells (1,1). Value
Sheets ("Sheet1"). Select
For k = 1 To 1000
Cells (k, 1). Value = TheValue
Next k
Method 3: Reduce the object's activation and selection
If you have to learn VBA by recording a macro, then your VBA program in a certain object activation and full of options, such as Workbooks (XXX). Activate, Sheets (XXX). Select, Range (XXX). Select and so on, but In fact in most cases these operations are not required. For example,
Sheets ("Sheet3"). Select
Range ("A1"). Value = 100
Range ("A2"). Value = 200
Can be changed to:
With Sheets ("Sheet3")
. Range ("A1"). Value = 100
. Range ("A2"). Value = 200
End With
Method 4: Turn off screen updates
If your VBA procedures done in front of three relatively poor, then close the VBA program is to increase the screen update speed of the most effective way to shorten the running time of 2 / 3. Way off the screen updates:
Application.ScreenUpdate = False
Please do not forget the end of VBA and then run back to set the value:
Application.ScreenUpdate = True
VBA is to improve the operating efficiency over the more effective of several methods.
Method 1: Try to use the original VBA properties, methods, and the Worksheet Functions
As many as hundred or more Excel objects, object properties, methods, events and more numerous, for starters, they may not all understand, which often resulted in the preparation of programming and Excel object properties, methods, functions the same VBA code, and the operating efficiency of the code is clear and Excel object properties, methods, task speed vary considerably. For example, with the Range property to return CurrentRegion Range object that represents the current area. (Refer to the current area any blank lines and blank columns on the composition of the border area). VBA code for the same functions to be dozens of lines. Therefore, before programming Excel objects as much as possible to understand the properties and methods.
Worksheet function is to increase take full advantage of the speed run extremely effective method. If the average wage demand example:
For Each c In
Worksheet (1). Range ("A1: A1000")
TotalValue = TotalValue + c.Value
Next
AverageValue = TotalValue / Worksheet (1). Range ("A1: A1000"). Rows.Count
The following example code much faster than the above procedures:
AverageValue = Application.WorksheetFunction.Average (Worksheets (1). Range ("A1: A1000"))
Other functions such as Count, Counta, Countif, Match, Lookup, etc., can replace the VBA code the same functionality to improve the running speed.
Method 2: minimize the use of object references, especially in the loop
The properties of each Excel object, method calls through the OLE interface requires one or more calls, the OLE calls takes time, reduce the use of object references to speed up VBA code to run. For example,
1. Use With statement.
Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.Name = "Pay"
Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.FontStyle = "Bold" ...
The following statement is faster than the above
With Workbooks (1). Sheets (1). Range ("A1: A1000"). Font
. Name = "Pay"
. FontStyle = "Bold"
...
End With
2. Using the object variable.
If you find an object reference to be used multiple times, then you can use this object to set the object variable Set to reduce access to the object. Such as:
Workbooks (1). Sheets (1). Range ("A1"). Value = 100
Workbooks (1). Sheets (1). Range ("A2"). Value = 200
The following code is faster than the above:
Set MySheet = Workbooks (1). Sheets (1)
MySheet.Range ("A1"). Value = 100
MySheet.Range ("A2"). Value = 200
3. In the loop to minimize access to the object.
For k = 1 To 1000
Sheets ("Sheet1"). Select
Cells (k, 1). Value = Cells (1,1). Value
Next k
The following code is faster than the above:
Set TheValue = Cells (1,1). Value
Sheets ("Sheet1"). Select
For k = 1 To 1000
Cells (k, 1). Value = TheValue
Next k
Method 3: Reduce the object's activation and selection
If you have to learn VBA by recording a macro, then your VBA program in a certain object activation and full of options, such as Workbooks (XXX). Activate, Sheets (XXX). Select, Range (XXX). Select and so on, but In fact in most cases these operations are not required. For example,
Sheets ("Sheet3"). Select
Range ("A1"). Value = 100
Range ("A2"). Value = 200
Can be changed to:
With Sheets ("Sheet3")
. Range ("A1"). Value = 100
. Range ("A2"). Value = 200
End With
Method 4: Turn off screen updates
If your VBA procedures done in front of three relatively poor, then close the VBA program is to increase the screen update speed of the most effective way to shorten the running time of 2 / 3. Way off the screen updates:
Application.ScreenUpdate = False
Please do not forget the end of VBA and then run back to set the value:
Application.ScreenUpdate = True
VBA is to improve the operating efficiency over the more effective of several methods.
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks
The information in this article applies to:
Microsoft Excel 2000
ActiveX Data Objects (ADO) 2.5
ActiveX Data Objects (ADO) 2.6
Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0
Microsoft Active Server Pages
This article was previously published under Q278973
SUMMARY
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
MORE INFORMATION
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe
Release Date: December 12, 2000
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.
Why Use ADO?
The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel:
Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.
Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.
ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format is maintained. If you require "conditional" formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.
Jet OLE DB Provider Specifics for Excel Workbooks
The Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks:
Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0
NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:
Dim oConn As New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Book1.xls"
'....
.Close
End With
-or-
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oConn.Close
Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:
Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.
oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
Use a range with a defined name (for example, [Table1]).
oRS.Open "Select * from Table1", oConn, adOpenStatic
Use a range with a specific address (for example, [Sheet1$A1:B10]).
oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic
Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to "guess" the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.
Files Included with the Sample
The ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project Files
ExcelADO.vbp
Form1.frm
Form1.frx
Active Server Pages
EmpData.asp
Orders.asp
Microsoft Excel Workbooks
OrdersTemplate.xls
EmpDataTemplate.xls
ProductsTemplate.xls
SourceData.xls
Microsoft Access Database
Data.mdb
How to Use the Sample
Extract the contents of the .exe file to a folder.
To use the Visual Basic project:
In Visual Basic, open the ExcelADO.vbp file.
On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.
Press the F5 key to run the program. A form for the demonstration appears.
Click Sample 1. This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:
On the Insert menu in Excel, select Names, and then select Define.
In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel's OFFSET function, to compute a total on the data added to the worksheet.
Quit Microsoft Excel and return to the Visual Basic application.
Click Sample 2. This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3. This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new "Products" worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any "conditional" formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4. This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet ("Sheet1$" or "Sheet2$") for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.
To use the Active Server Pages (ASP):
Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.
Copy the following files to the folder you created in the previous step:
EmpData.asp
Orders.asp
Data.mdb
EmpDataTemplate.xls
OrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.
Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.
Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application.
(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.
REFERENCES
For more information, see the white paper "Automating Office 97 and Office 2000" available on the following Microsoft Web site at:
http://support.microsoft.com/support/officedev/automation.asp
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
195951 HOWTO: Query and Update Excel Data Using ADO From ASP
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
193998 HOWTO: Read and Display Binary Data in ASP
247412 INFO: Methods for Transferring Data to Excel from Visual Basic
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Last Reviewed: 5/20/2003
Keywords: kbAutomation kbfile kbProgramming KB278973
Microsoft Excel 2000
ActiveX Data Objects (ADO) 2.5
ActiveX Data Objects (ADO) 2.6
Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0
Microsoft Active Server Pages
This article was previously published under Q278973
SUMMARY
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
MORE INFORMATION
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe
Release Date: December 12, 2000
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.
Why Use ADO?
The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel:
Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.
Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.
ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format is maintained. If you require "conditional" formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.
Jet OLE DB Provider Specifics for Excel Workbooks
The Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks:
Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0
NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:
Dim oConn As New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Book1.xls"
'....
.Close
End With
-or-
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oConn.Close
Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:
Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.
oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
Use a range with a defined name (for example, [Table1]).
oRS.Open "Select * from Table1", oConn, adOpenStatic
Use a range with a specific address (for example, [Sheet1$A1:B10]).
oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic
Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to "guess" the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.
Files Included with the Sample
The ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project Files
ExcelADO.vbp
Form1.frm
Form1.frx
Active Server Pages
EmpData.asp
Orders.asp
Microsoft Excel Workbooks
OrdersTemplate.xls
EmpDataTemplate.xls
ProductsTemplate.xls
SourceData.xls
Microsoft Access Database
Data.mdb
How to Use the Sample
Extract the contents of the .exe file to a folder.
To use the Visual Basic project:
In Visual Basic, open the ExcelADO.vbp file.
On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.
Press the F5 key to run the program. A form for the demonstration appears.
Click Sample 1. This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:
On the Insert menu in Excel, select Names, and then select Define.
In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel's OFFSET function, to compute a total on the data added to the worksheet.
Quit Microsoft Excel and return to the Visual Basic application.
Click Sample 2. This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3. This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new "Products" worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any "conditional" formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4. This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet ("Sheet1$" or "Sheet2$") for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.
To use the Active Server Pages (ASP):
Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.
Copy the following files to the folder you created in the previous step:
EmpData.asp
Orders.asp
Data.mdb
EmpDataTemplate.xls
OrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.
Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.
Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application.
(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.
REFERENCES
For more information, see the white paper "Automating Office 97 and Office 2000" available on the following Microsoft Web site at:
http://support.microsoft.com/support/officedev/automation.asp
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
195951 HOWTO: Query and Update Excel Data Using ADO From ASP
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
193998 HOWTO: Read and Display Binary Data in ASP
247412 INFO: Methods for Transferring Data to Excel from Visual Basic
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Last Reviewed: 5/20/2003
Keywords: kbAutomation kbfile kbProgramming KB278973
EXCEL was time to leave the cell values automatically * 100
Question:
In a cell enter a number, leave this cell was automatically when the number * 100, how to program solution?
Answer:
Use SelectionChange event, leave the cell to capture the value of the specified * 100 after the re-write
In a cell enter a number, leave this cell was automatically when the number * 100, how to program solution?
Answer:
Use SelectionChange event, leave the cell to capture the value of the specified * 100 after the re-write
EXCEL Tips: Batch modify data
In the EXCEL spreadsheet data have been completed, how easy for any one column (row) of data to modify it?
For instance, we do an EXCEL table, fill the data, and now want to modify a column (row), for example: like in the A column on the basis of the original data plus 8, there is no such formula? Not have to manually one by one, add the data to live? For this we naturally think of the use of formula, you use the public input A1 = A1 8, you will get a warning EXCEL: "MICROSOFT EXCEL the formula can not be calculated ... ..."Only we ourselves think of a way out, and here a Kinds of simple methods:
The first step:
You want to modify the column (assumed to be A column) next to insert a temporary new column (for the B column), and the first column in the B cell (B1), enter 8.
Step two:
B1 or the mouse on the bottom corner, wait until after the live cross down into the data until the desired length, then all of the data column B 8.
The third step:
In the B column, click the right mouse button, "Copy"B column.
Step four:
A column in the right-mouse click in the pop-up dialog box, click "Paste"in the pop-up dialog box, select "operation" in the operator you need, here we choose "add" This is the key to this method.
Step five:
The B column removed.
How? A column with each of the data is not all 8 it? The same approach can be achieved on a column (row) of multiplication, addition, subtraction, and other computing operations. The format of the original form has not changed.
At this point the end of the work, the use of skilled, it will take less than ten seconds.
For instance, we do an EXCEL table, fill the data, and now want to modify a column (row), for example: like in the A column on the basis of the original data plus 8, there is no such formula? Not have to manually one by one, add the data to live? For this we naturally think of the use of formula, you use the public input A1 = A1 8, you will get a warning EXCEL: "MICROSOFT EXCEL the formula can not be calculated ... ..."Only we ourselves think of a way out, and here a Kinds of simple methods:
The first step:
You want to modify the column (assumed to be A column) next to insert a temporary new column (for the B column), and the first column in the B cell (B1), enter 8.
Step two:
B1 or the mouse on the bottom corner, wait until after the live cross down into the data until the desired length, then all of the data column B 8.
The third step:
In the B column, click the right mouse button, "Copy"B column.
Step four:
A column in the right-mouse click in the pop-up dialog box, click "Paste"in the pop-up dialog box, select "operation" in the operator you need, here we choose "add" This is the key to this method.
Step five:
The B column removed.
How? A column with each of the data is not all 8 it? The same approach can be achieved on a column (row) of multiplication, addition, subtraction, and other computing operations. The format of the original form has not changed.
At this point the end of the work, the use of skilled, it will take less than ten seconds.
How to disable the screen refresh EXCEL
Question:
How to turn off the animation feature EXCEL?
That said, I EXCEL program to modify the data inside each one of his changes are shown on the screen,
I need to change him so I finished all the later show how to change?
Answer:
Application.ScreenUpdating = False
'Your code
Application.ScreenUpdating = True
How to turn off the animation feature EXCEL?
That said, I EXCEL program to modify the data inside each one of his changes are shown on the screen,
I need to change him so I finished all the later show how to change?
Answer:
Application.ScreenUpdating = False
'Your code
Application.ScreenUpdating = True
Using Excel to establish a database Dafa!
【Introduction】
Excel provides some useful feature that allows any one of us can easily get to these databases
Daily work, we often need to create some of regular database. For example, I order to manage the township's agricultural tax, the need to establish a database, which is called the first field name of the village, the second field named groups. I have a total of 19 rural villages, each village, groups ranging from 7 to 17, a total of 258 groups. The database with the database software (even the Visual FoxPro 6.0 or Access97 high grade) are not set up - you enter one by one child, have this idea that only a fool. Access or FoxPro programming with a macro to enter it, the data seems not enough rules (number of groups corresponding to each village is not necessarily the same), this procedure is not so easy to write, unless you are a programming expert and programming fan, or may have suspected the fuss.
In fact, Excel provides some useful feature that allows any one of us can easily get to these databases:
Step one: Open Excel97 (Excel2000 line of course), the cells in column A row 1 insert "name of the village", line 2 insert "East Village", line 19 insert "Village on the back" (Note : Dongshan 17 groups, calculated pursuant to 2 +17 = 19), line 28 insert "Wang Bo Village" (with the previous algorithm, Cattle Village Group 9: 19 +9 = 28), and so on to fill the 19 name of the village good.
Step Two: In line 1 column B put a "group", line 2 insert "Group 1" and press the right mouse button in this select "Copy" Clipboard Copy these three words, then each A name of the village filled with the line of the B column click of a mouse button select "Paste" fill in where a "Group 1."
The third step; with the mouse click on the selected A2 "East Village" cell, and then the bottom right corner the mouse cells (in this case the mouse into a single "Ten" shape), press and hold the mouse down, drag, drag over which automatically fill in the "East Village" is shown. Can use the same method name of the village and the other group with the mouse "a drag is over." Do not worry when you fill group group of Excel will fill all of the "Group 1" as long as you do not take "Group 1" instead of "first group", Excel will automatically recognize it as a sequence for processing. So drag the "Group 1", the complete results as "group 2", "Group 3" ... ... fill out these two fields, other data can continue to fill in Excel, can also be so in the future Fill in database software, anyway, about the labor intensity.
Step Four: Save the file. If you need to create the Access database, so do not ignore it, to use the Excel default ". Xls" format down. If you need to create a FoxPro database, please Dbase 4 (. Dbf) format files.
Step five: If you need the Access database, you must create a new Access database, in the "New Table" dialog box, you select the "Import Table" and then in the Import dialog box, select you just save the ". Xls "file. (What? You can not find?! This dialog box the default file type is Microsoft Access, Microsoft Excel will be able to as long as you have found), selected the import file, you just pay attention to a "first row contains column headings" the box core Tuoxin ㄈ quan sad suddenly playing Ping Tong D field, you can recommend to you the primary key Access refused - Select "Do not the primary key"), you can ignore the rest, just Click "Next" to completion. After the import is complete you can hit the database to use or modify. If you need a FoxPro database, then the more simple, you can directly save your previous step FoxPro open the ". Dbf" file, according to the need for some, such as field widths, field data type is set to use.
Here, you might say, "Ah, too simple, I will", well, then, I write this purpose is no longer possible to do all the things that a lot of trouble, "when the lazy lazy, "Well.
Excel provides some useful feature that allows any one of us can easily get to these databases
Daily work, we often need to create some of regular database. For example, I order to manage the township's agricultural tax, the need to establish a database, which is called the first field name of the village, the second field named groups. I have a total of 19 rural villages, each village, groups ranging from 7 to 17, a total of 258 groups. The database with the database software (even the Visual FoxPro 6.0 or Access97 high grade) are not set up - you enter one by one child, have this idea that only a fool. Access or FoxPro programming with a macro to enter it, the data seems not enough rules (number of groups corresponding to each village is not necessarily the same), this procedure is not so easy to write, unless you are a programming expert and programming fan, or may have suspected the fuss.
In fact, Excel provides some useful feature that allows any one of us can easily get to these databases:
Step one: Open Excel97 (Excel2000 line of course), the cells in column A row 1 insert "name of the village", line 2 insert "East Village", line 19 insert "Village on the back" (Note : Dongshan 17 groups, calculated pursuant to 2 +17 = 19), line 28 insert "Wang Bo Village" (with the previous algorithm, Cattle Village Group 9: 19 +9 = 28), and so on to fill the 19 name of the village good.
Step Two: In line 1 column B put a "group", line 2 insert "Group 1" and press the right mouse button in this select "Copy" Clipboard Copy these three words, then each A name of the village filled with the line of the B column click of a mouse button select "Paste" fill in where a "Group 1."
The third step; with the mouse click on the selected A2 "East Village" cell, and then the bottom right corner the mouse cells (in this case the mouse into a single "Ten" shape), press and hold the mouse down, drag, drag over which automatically fill in the "East Village" is shown. Can use the same method name of the village and the other group with the mouse "a drag is over." Do not worry when you fill group group of Excel will fill all of the "Group 1" as long as you do not take "Group 1" instead of "first group", Excel will automatically recognize it as a sequence for processing. So drag the "Group 1", the complete results as "group 2", "Group 3" ... ... fill out these two fields, other data can continue to fill in Excel, can also be so in the future Fill in database software, anyway, about the labor intensity.
Step Four: Save the file. If you need to create the Access database, so do not ignore it, to use the Excel default ". Xls" format down. If you need to create a FoxPro database, please Dbase 4 (. Dbf) format files.
Step five: If you need the Access database, you must create a new Access database, in the "New Table" dialog box, you select the "Import Table" and then in the Import dialog box, select you just save the ". Xls "file. (What? You can not find?! This dialog box the default file type is Microsoft Access, Microsoft Excel will be able to as long as you have found), selected the import file, you just pay attention to a "first row contains column headings" the box core Tuoxin ㄈ quan sad suddenly playing Ping Tong D field, you can recommend to you the primary key Access refused - Select "Do not the primary key"), you can ignore the rest, just Click "Next" to completion. After the import is complete you can hit the database to use or modify. If you need a FoxPro database, then the more simple, you can directly save your previous step FoxPro open the ". Dbf" file, according to the need for some, such as field widths, field data type is set to use.
Here, you might say, "Ah, too simple, I will", well, then, I write this purpose is no longer possible to do all the things that a lot of trouble, "when the lazy lazy, "Well.
Excel 12, the latest trick speed Dharma
Excel spreadsheet is an all-powerful, it is powerful, easy to operate, in addition to quickly generate, format of various forms, but also can do a lot of data in the table function of the database. Introduce you to fast for the next few ways to use Excel skills.
1 Quick Start Excel. If your daily work, often used to Excel, you can start it when you start Windows, set the method: (1) Start the "My Computer" to enter the Windows directory, in accordance with the path "Start Menu \ Programs \ Start" to open the "boot" file Folder: (2) Open the folder where Excel, Excel icon with the mouse to drag the "Start" folder, then Excel shortcut to be copied to the "start" folder, you can quickly start the next time you start Windows Excel has.
If Windows is on, you can use the following methods to quick start Excel. Method One: Double-click the "Start" menu in the "Document" command in any of the Excel workbook can be. Method Two: Use the mouse from the "My Computer" will drag the Excel application on your desktop, and then from the shortcut menu, select "Create Shortcut in the current location" to create its shortcut, just double-click the Startup shortcut can be.
2, quick access to help. For the tool bar or the screen area, you just press the key combination Shift + F1, then right click the toolbar button or the screen area, it will pop up a help window, the above elements will tell the detailed help information.
3, fast move or copy cells. First selected cell, and then move the mouse pointer to the cell borders, press the left mouse button and drag to a new location, then release the button you can move. To copy a cell, then press Ctrl before you can release the mouse.
4, quickly find the workbook. You can use the worksheet in any text search methods: (1) Click on the toolbar "Open" button in the "Open" dialog box, enter the file's full name or partial name, you can use through wildcards; to (2) in the "Text Properties" box, enter the text you want to search, it is best do you think is the only word or phrase to search more successful; (3) Select "Find" can be. Documents to meet the conditions found before the "Open" dialog box, the status bar will show "0 files found" message, you should be patient, only when the "Open" button from the ashes of the state become available only when show that the search ended.
5, fast printing sheet. If you select "File" menu, "Print" command to print, there will be "Print" dialog box allows you to choose from cumbersome procedures. To skip the dialog box, you can click the "common" tool bar on the "Print" button or press the Shift key and click the "Print Preview" button, Excel will use the "selected sheet" option to print .
6, faster switching worksheet. Press Ctrl + PageUp key combination to activate the previous worksheet, press Ctrl + PageDown key combination can be activated by a sheet. You can also use the mouse to control the sheet tab at the bottom scroll button to quickly move the worksheet name, then click the table switch.
7, quickly switch the workbook. Workbook for small change, click the workbook where the window. Multiple windows to work under the switch over, use the "Window" menu, the most convenient. "Window" menu at the bottom lists the name of the workbook open to switching to a workbook directly from the "Window" menu to choose its name. "Window" menu lists up to 9 workbook, if more than 9, the "window" menu contains a "multi-window" command, use the command, while there is an alphabetical list of all open workbook name dialog box, just click the name of one can be required.
8, quickly insert Word tables. Word Excel can handle the data listed in the table, you can quickly insert a Word table the following methods: (1) Open the Word document where the table; (2) Open the Excel table to handle Word documents, and adjust the position of the two windows, so as to form and to insert a table to see the area; (3) Select the Word in the form; (4) Hold down the left mouse button, drag the table to Excel window, release the left mouse button on the need to position the table can be.
9, quick links online data. You can use the following methods to quickly build and on-line data in the workbook links: (1) Open Internet on the need to link the data with the workbook, and selected data in the workbook, and then click the "Edit" menu "Copy" command ; (2) Open the need to create a linked Excel workbook, the data in the area need to show the link, click the upper-left cell; (3) Click "Edit" menu in the "Paste Special" command in the "Choice of Paste "dialog box, select" Paste Link "button. If you want to create links in the workbook is not open Internet, you can click the link at the needs of the cell, then type (=) and the URL address and the workbook location, such as: = http://www.Js.com/ [ filel.xls].
10, quickly create toolbar. The toolbar you can quickly access frequently used commands or custom macros, you can quickly create their own tools required field. Method: Click the "Tools" menu in the "custom" command, select "Toolbars" tab, click "New" button, type "New Toolbar" name, and then click "OK." Then the new toolbar appears in the window, you can use the mouse to drag the other new toolbar button in the toolbar, the button will be in this "settled." If pressing Ctrl key while dragging, then copied the button. Note: You can drag the button "Customize" dialog box or work table, otherwise the button will be deleted.
11, using a template to create the workbook. Template is a workbook used as a framework to create other forms, use it to quickly create a similar workbook. Create a template methods are: (1) Open a workbook to be used as a template; (2) Select "File" menu "Save As" command, open the "Save As" dialog box; (3) in the "File Name" Enter the name of the template from the "Save as type" list, select "Template (*. xlt)" option, then "Save" will automatically switch to the default Templates folder template folder; (4) " Save in "select" spreadsheet template "folder, click" Save "button. This way, you can quickly create the template of the new workbook.
12, with "super-connected" to quickly jump to other files. Use hyperlinks to jump between the various locations is very convenient, if you want to switch to another file, just use the mouse to point to the blue underlined hyperlinks to files, and then click a hyperlink to jump to the pointing up the sub-location, after reading To return, simply click on "Web" toolbar "back" button.
1 Quick Start Excel. If your daily work, often used to Excel, you can start it when you start Windows, set the method: (1) Start the "My Computer" to enter the Windows directory, in accordance with the path "Start Menu \ Programs \ Start" to open the "boot" file Folder: (2) Open the folder where Excel, Excel icon with the mouse to drag the "Start" folder, then Excel shortcut to be copied to the "start" folder, you can quickly start the next time you start Windows Excel has.
If Windows is on, you can use the following methods to quick start Excel. Method One: Double-click the "Start" menu in the "Document" command in any of the Excel workbook can be. Method Two: Use the mouse from the "My Computer" will drag the Excel application on your desktop, and then from the shortcut menu, select "Create Shortcut in the current location" to create its shortcut, just double-click the Startup shortcut can be.
2, quick access to help. For the tool bar or the screen area, you just press the key combination Shift + F1, then right click the toolbar button or the screen area, it will pop up a help window, the above elements will tell the detailed help information.
3, fast move or copy cells. First selected cell, and then move the mouse pointer to the cell borders, press the left mouse button and drag to a new location, then release the button you can move. To copy a cell, then press Ctrl before you can release the mouse.
4, quickly find the workbook. You can use the worksheet in any text search methods: (1) Click on the toolbar "Open" button in the "Open" dialog box, enter the file's full name or partial name, you can use through wildcards; to (2) in the "Text Properties" box, enter the text you want to search, it is best do you think is the only word or phrase to search more successful; (3) Select "Find" can be. Documents to meet the conditions found before the "Open" dialog box, the status bar will show "0 files found" message, you should be patient, only when the "Open" button from the ashes of the state become available only when show that the search ended.
5, fast printing sheet. If you select "File" menu, "Print" command to print, there will be "Print" dialog box allows you to choose from cumbersome procedures. To skip the dialog box, you can click the "common" tool bar on the "Print" button or press the Shift key and click the "Print Preview" button, Excel will use the "selected sheet" option to print .
6, faster switching worksheet. Press Ctrl + PageUp key combination to activate the previous worksheet, press Ctrl + PageDown key combination can be activated by a sheet. You can also use the mouse to control the sheet tab at the bottom scroll button to quickly move the worksheet name, then click the table switch.
7, quickly switch the workbook. Workbook for small change, click the workbook where the window. Multiple windows to work under the switch over, use the "Window" menu, the most convenient. "Window" menu at the bottom lists the name of the workbook open to switching to a workbook directly from the "Window" menu to choose its name. "Window" menu lists up to 9 workbook, if more than 9, the "window" menu contains a "multi-window" command, use the command, while there is an alphabetical list of all open workbook name dialog box, just click the name of one can be required.
8, quickly insert Word tables. Word Excel can handle the data listed in the table, you can quickly insert a Word table the following methods: (1) Open the Word document where the table; (2) Open the Excel table to handle Word documents, and adjust the position of the two windows, so as to form and to insert a table to see the area; (3) Select the Word in the form; (4) Hold down the left mouse button, drag the table to Excel window, release the left mouse button on the need to position the table can be.
9, quick links online data. You can use the following methods to quickly build and on-line data in the workbook links: (1) Open Internet on the need to link the data with the workbook, and selected data in the workbook, and then click the "Edit" menu "Copy" command ; (2) Open the need to create a linked Excel workbook, the data in the area need to show the link, click the upper-left cell; (3) Click "Edit" menu in the "Paste Special" command in the "Choice of Paste "dialog box, select" Paste Link "button. If you want to create links in the workbook is not open Internet, you can click the link at the needs of the cell, then type (=) and the URL address and the workbook location, such as: = http://www.Js.com/ [ filel.xls].
10, quickly create toolbar. The toolbar you can quickly access frequently used commands or custom macros, you can quickly create their own tools required field. Method: Click the "Tools" menu in the "custom" command, select "Toolbars" tab, click "New" button, type "New Toolbar" name, and then click "OK." Then the new toolbar appears in the window, you can use the mouse to drag the other new toolbar button in the toolbar, the button will be in this "settled." If pressing Ctrl key while dragging, then copied the button. Note: You can drag the button "Customize" dialog box or work table, otherwise the button will be deleted.
11, using a template to create the workbook. Template is a workbook used as a framework to create other forms, use it to quickly create a similar workbook. Create a template methods are: (1) Open a workbook to be used as a template; (2) Select "File" menu "Save As" command, open the "Save As" dialog box; (3) in the "File Name" Enter the name of the template from the "Save as type" list, select "Template (*. xlt)" option, then "Save" will automatically switch to the default Templates folder template folder; (4) " Save in "select" spreadsheet template "folder, click" Save "button. This way, you can quickly create the template of the new workbook.
12, with "super-connected" to quickly jump to other files. Use hyperlinks to jump between the various locations is very convenient, if you want to switch to another file, just use the mouse to point to the blue underlined hyperlinks to files, and then click a hyperlink to jump to the pointing up the sub-location, after reading To return, simply click on "Web" toolbar "back" button.
Quickly calculate the length of the rectangle diagonal
Known length and width of the rectangle, are now required to calculate the diagonal length, with Excel you can quickly calculate the answers. How does it work?
Let the rectangle length X, width Y, the diagonal length of Z, then, can be seen from this formula, requiring the Z, there are two steps. First, calculate the square value of X and Y value of the square, and add them up; Second, the first step to calculate the value of evolution, the resulting figure is the Z. Obviously, we want to use to Excel, the two functions, one is to calculate the square of a number, that number to a power; Second, calculate the square root. Here are the two mathematical functions are used.
■ Back to the given number of power: POWER
POWER function computing power of numbers, it's syntax is: POWER (number, power)
Number is the base which can be any real number.
Power is the exponent, the power base power by the index.
For example, we asked five of the secondary side, which is square, you can enter the cell: "= POWER (5,2)", you can get the answer 25.
■ Returns a given number is the square root: SQRT
Its syntax is: SQRT (number)
Number one is to calculate the square root of the number.
For example, we required a square root of 16, you can enter the cell: "= SQRT (16)", you can get the answer 4.
Know the usage of these two functions, we can easily come to Z's value.
Find the X and Y, respectively, before the square, and then add them up, then the value derived by adding the square root, we can deduce the answer.
If you still feel this way is slow, there is a better way. Here's a function: SUMSQ
■ Returns the sum of the squares: SUMSQ
Its syntax is: SUMSQ (number1, number2, ...)。 number1, number2, etc. within 30 the number of them can be calculated with this function and the square, so that, just enter the X and Y values to their sum of squares can be calculated, and then calculated as SQRT function Finally, Z values can be calculated.
Let the rectangle length X, width Y, the diagonal length of Z, then, can be seen from this formula, requiring the Z, there are two steps. First, calculate the square value of X and Y value of the square, and add them up; Second, the first step to calculate the value of evolution, the resulting figure is the Z. Obviously, we want to use to Excel, the two functions, one is to calculate the square of a number, that number to a power; Second, calculate the square root. Here are the two mathematical functions are used.
■ Back to the given number of power: POWER
POWER function computing power of numbers, it's syntax is: POWER (number, power)
Number is the base which can be any real number.
Power is the exponent, the power base power by the index.
For example, we asked five of the secondary side, which is square, you can enter the cell: "= POWER (5,2)", you can get the answer 25.
■ Returns a given number is the square root: SQRT
Its syntax is: SQRT (number)
Number one is to calculate the square root of the number.
For example, we required a square root of 16, you can enter the cell: "= SQRT (16)", you can get the answer 4.
Know the usage of these two functions, we can easily come to Z's value.
Find the X and Y, respectively, before the square, and then add them up, then the value derived by adding the square root, we can deduce the answer.
If you still feel this way is slow, there is a better way. Here's a function: SUMSQ
■ Returns the sum of the squares: SUMSQ
Its syntax is: SUMSQ (number1, number2, ...)。 number1, number2, etc. within 30 the number of them can be calculated with this function and the square, so that, just enter the X and Y values to their sum of squares can be calculated, and then calculated as SQRT function Finally, Z values can be calculated.
Voice Proofreading Excel "Text to Speech"function
【Introduction】
In Excel 2003 there is a "Text to Speech"function, this function can be completed using the language of proof, but in Word 2003, without this feature. In fact, we can use Word, Excel VBA code to call the "Text to Speech"function.
Article I: In 2003 the most proud of the talent workplace - the workplace of today's Shanghai, "gray-collar" in power
Preface: December 7, Shanghai, the first "gray-collar"professional contest awards ceremony was held at the Shanghai International Media Center, marking the well-organized by the relevant departments in Shanghai, "gray-collar" career promotion activities successfully completed.
Article II: In 2003, the most frustrated people workplace level - "returnees " to send a monthly salary of only thousands of job experience
Preface: If you are not informed in advance, you will not believe this is a special recruitment of overseas students. One thousand yuan - which is planting a Beijing technology company hit the post a monthly salary of a price tag.
Article III: 2003 the most workplace "cow"talent - this year's online gaming talent, "cow " is very
Introduction: Recently, campus recruitment Netease UESTC hot, just ask for the moon onto the double Hoogewerf and Forbes Rich List Ding personally took the top spot, open the Jobs list, online games, mobile phone research and development, wireless communications, and other hot talents.
Article IV: the most popular career in 2003 the network talent - what today's most popular network of talent
Preamble: "to do white-collar workers do not go on, how can? seek excellence, not how the Internet line? do you, do not go how the line? " these buzzwords reveal a new trend: touch all walks of life have "net", at all levels Talents towards the "network. " Network services, the most popular do it?
In Excel 2003 there is a "Text to Speech"function, this function can be completed using the language of proof, but in Word 2003, without this feature. In fact, we can use Word, Excel VBA code to call the "Text to Speech"function.
Article I: In 2003 the most proud of the talent workplace - the workplace of today's Shanghai, "gray-collar" in power
Preface: December 7, Shanghai, the first "gray-collar"professional contest awards ceremony was held at the Shanghai International Media Center, marking the well-organized by the relevant departments in Shanghai, "gray-collar" career promotion activities successfully completed.
Article II: In 2003, the most frustrated people workplace level - "returnees " to send a monthly salary of only thousands of job experience
Preface: If you are not informed in advance, you will not believe this is a special recruitment of overseas students. One thousand yuan - which is planting a Beijing technology company hit the post a monthly salary of a price tag.
Article III: 2003 the most workplace "cow"talent - this year's online gaming talent, "cow " is very
Introduction: Recently, campus recruitment Netease UESTC hot, just ask for the moon onto the double Hoogewerf and Forbes Rich List Ding personally took the top spot, open the Jobs list, online games, mobile phone research and development, wireless communications, and other hot talents.
Article IV: the most popular career in 2003 the network talent - what today's most popular network of talent
Preamble: "to do white-collar workers do not go on, how can? seek excellence, not how the Internet line? do you, do not go how the line? " these buzzwords reveal a new trend: touch all walks of life have "net", at all levels Talents towards the "network. " Network services, the most popular do it?
Select the hyperlink contains the cell excel
In Excel, a cell type an e-mail address, the default state of this cell will automatically be converted into a hyperlink, right click on it will open the hyperlink, this way, how to select this cell and Hyperlinks do not open this? Is simple, with the click of the mouse to live in this cell continued to hold a few seconds, can select it.
Do not use the arrow keys to move around can be selected up and down the cell excel
Because of their work, the author of multiple cells in Excel import large quantities of text, we all know, in Excel, if the selected cell to the right of the selected cell, press the arrow keys to move, you can in order to save Time for the higher speed of operation, you can press the Tab key, so you can quickly select the right cell has been selected cells, because both hands when typing in the main key area to work on, if you want to Arrow keys, you must vacate the right hand, which will undoubtedly reduce efficiency;
So if you want to the left of the selected cell is selected the cell how to do it? I tried that, you can press the "Shift Tab" key combination, so the selected cells will be left out.
Above, we know that a quick left and right movement of the selected cell method, that the selected cell is to press down the "Enter" key, the same token, if you want to up the selected cell is to press the combination of Key "Shift Enter", how about, so you enter the large amount of text in Excel, the speed will be faster.
So if you want to the left of the selected cell is selected the cell how to do it? I tried that, you can press the "Shift Tab" key combination, so the selected cells will be left out.
Above, we know that a quick left and right movement of the selected cell method, that the selected cell is to press down the "Enter" key, the same token, if you want to up the selected cell is to press the combination of Key "Shift Enter", how about, so you enter the large amount of text in Excel, the speed will be faster.
Continued to show the formula instead of the cell displays the calculated values
In Excel, a cell of arbitrary type a formula such as = 1 +1, press the Enter key, then this cell will no longer display the formula, and the results showed 2, and sometimes we do not want to Immediately see the results, but we hope this will continue to show the cell formulas, how to solve this problem? Very simple formula in the cell after the input, then click the menu bar "Tools"-"Formula Auditing"- "formula auditing mode ", so that you can keep the formula intact; and if you want to show the formula The results, you can again click on the "Formula Auditing Mode"button.
Sorting and filtering with Excel functions
Execl itself is very easy sorting and filtering capabilities, the drop-down "data" menu to select the sort or filter the data to sort or filter the list. But there are also inadequate, first of all regardless of sort or filter the list to change the original appearance of the original, especially the list of links to data from other worksheet changes to the source data, or list a new record entry must be from a new sort or filter. Second, there are limitations, such as sorting can only be a maximum of three keywords (three data) sorting, filtering, data available on the same column "and" or "or" conditions for screening, but can only be used for different columns of data "and" conditions of screening. Zhang Hua Mingce employees such as a workbook to require screening older than 25 years of age and less than 50 years of age or older than 50 years old or less than 25 years are feasible, such as male gender is also required or the woman is feasible. But requires selected woman aged from 22 to 45 years old, men aged 25 to 50 years old Execl the filtering itself is helpless. Furthermore, sorting and filtering can not be combined, that can not sort out under conditions selected to sort the records. For example, employees have a list of data, some of which employees have retired, the age for serving employees can not be removed to sort the data has been retired workers.
This paper attempts to function with Execl to address the issue.
First, sort by function implementation
Subject
If a payroll, A2: F501, a total of 6 500 3000 cell line. A1 is the code name of the header (1 to 500), B1 for the name, C1 for the allowance, D1 for the prize, E1 wage, F1 total revenue. Income of workers are now required on the order from more to less, and in the total income workers the same wages from more to less then sorted, and wages in total income workers the same bonus from more to less then sort, in the total income of workers and employees salaries, bonuses, allowances and then the same ranking from more to less.
Methods
G1 cells enter the formula "= if (F2 = 0,10 ^ 100, INT (CONCATENATE (999-f2 ,999-e2 ,999-d2 ,999-c2 )))", CONCATENATE is a mosaic function can be 30 the following units of data together such a data, these data are put together with commas. With f2, e2, etc. The data were put together to reduce by 999, the same number of bits to make them. (Assuming any of the employees total income of less than 899 yuan). Been together such a function is a text function, CONCATENATE and apply the INT function is to make the text is converted to digital. If the outermost layer of the sort function is used to sort the records are not removed, middle income is zero in this case the record. (The age of workers in the above-mentioned order, the formula changed to "if (f2 =" retire ", 10 ^ 100, ... ..)", that the exclusion of retired workers.)
The second step to drag the formula cell G1 to G500 cells (the most convenient way is to click the cell G1 G1 cells to the lower right corner after moving the mouse, double-click the mouse to see the black ten o'clock to finish filling G1 to G500 .)
The third step in the H2 unit fill in the formula "= MATCH (SMALL (G: G, ROW (A1)), G: G, 0)" and the second step, like drag and drop to the H501 cells. This formula is actually a synthesis of the three formulas a formula, ROW (A1) A1 is the number of rows is 1, with the drop down the order 2,3,4 ..., SMALL (G: G, ROW (A1 )) is the smallest number in the G column drop down the order as 2, 3, .. a small number, MATCH (SMALL (G: G, ROW (A1)), G: G, 0) is G column for the smallest of all the data rows, 2, 3 and small in the first few lines of data.
The fourth step the A1 to the F1 cell header copied to I1 to N1 cells, in I2 cells enter the formula "= INDEX ($ A $ 2: $ F $ 501, $ H2, COLUMN (A $ 1))" INDEX function is a reference function, that is $ A $ 2: $ F $ 501 $ H2 cell array of the first row COLUMN (A $ 1) out of the data into the I2 cell. I2 cell and then drag and drop the formula N2 cell, click the N2 to the N2 cells after cell to see the bottom right of the black ten o'clock move the mouse double-click to complete the cell I2 to fill this N501 completed.
The above description may seem complicated reality is very simple, as long as the A1 to the F1 copy of the table header cell I1 to N1, and then were in G1, H2, I2 cells enter the formula and drag down, even for unskilled EXCEL application Comrade Dr be finished within one minute.
Changes in these procedures can be a little more expense. The above example data is arranged from largest to smallest, such as a function of the column in the H SMALL to LARGE, the above example data is ranked from small to large. Such as the H2 cell formula changed to "= IF (O1 = 1, MATCH (SMALL (G: G, ROW (A1)), G: G, 0), MATCH (LARGE (G: G, ROW (A1)) , G: G, 0)) ", and the H2 cell formula down the drag and drop. This cell type 1 in O1 the example above the data is ordered from largest to smallest, O1 cells enter a number other than the example above, data on the arrangement of the small to large.
If the H column, insert several columns, such as insert a column, similar to the current G H column input column formulas, such as "= if (F2 = 0,10 ^ 100, d2)", now I changed the formula column "= IF (P1 = 1, MATCH (SMALL (G: G, ROW (A1)), G: G, 0), MATCH (SMALL (H: H, ROW (A1)), H: H, 0)) ) "is 1 in P cell input to achieve a value other than by order of prize money. so long as by changing the P1 (O1 original cell) can change the cell contents immediately sorted according to different requirements.
Second, with the screening function implementation
Subject
If an employee roster table, A2: F501, a total of 6 500 3000 cell line. A1 is the code name of the header (1 to 500), B1 for the name, C1 for gender, D1 for the ages, E1 for the education, F1 titles. Workers are now required on gender, age, education, job title interleaving filter, such as requiring a table in the same selected 1 woman aged from 22 to 45 years old, men aged 25 to 50 years of age, 2, female Dr, 3, male post-doctoral.
Methods
The first step in the G2 cells enter the formula "= IF (OR (AND (C2 =" Female ", D2> = 22, D2 <= 45), AND (C2 =" M "
D2> = 25, D2 <= 50)), ROW (A1), 0) ", in the H2 cell enter the formula" = IF (AND (C2 = "Female", E2 = "Doctor"), ROW (B1) , 0) ", enter the formula in cell I2" = IF (AND (C2 = "M", E2 = "doctoral"), ROW (B1), 0) ". enter the formula in cell J2" = IF (K $ 2 = 1, LARGE (G: G, ROW (A1)), IF (K $ 2 = 2, LARGE (H: H, ROW (A1)), IF (K $ 2 = 3, LARGE (I: I, ROW ( A1)), 0))) "and then drop down to the above-mentioned methods. G, H, I listed the meaning of the formula is where the records match the filter conditions down the line number zero otherwise, J out of the equation meaning based on the value selected K2 G, H, I sort a column in the rows and remove the substandard conditions.
The second step in the K1 cells lose the text "filter selection", A1 to the F1 table copied to the L1 to the first Q1, cell entry in the L2
The formula "= IF ($ J2 = 0,0, INDEX ($ A $ 2: $ F $ 501, $ J2, COLUMN (A $ 1 )))", then right onto Q2, and then drop down. INDEX function has explained the meaning above.
The third step in the P1 cell type 1 or 2 or 3 can achieve these three screening.
This paper attempts to function with Execl to address the issue.
First, sort by function implementation
Subject
If a payroll, A2: F501, a total of 6 500 3000 cell line. A1 is the code name of the header (1 to 500), B1 for the name, C1 for the allowance, D1 for the prize, E1 wage, F1 total revenue. Income of workers are now required on the order from more to less, and in the total income workers the same wages from more to less then sorted, and wages in total income workers the same bonus from more to less then sort, in the total income of workers and employees salaries, bonuses, allowances and then the same ranking from more to less.
Methods
G1 cells enter the formula "= if (F2 = 0,10 ^ 100, INT (CONCATENATE (999-f2 ,999-e2 ,999-d2 ,999-c2 )))", CONCATENATE is a mosaic function can be 30 the following units of data together such a data, these data are put together with commas. With f2, e2, etc. The data were put together to reduce by 999, the same number of bits to make them. (Assuming any of the employees total income of less than 899 yuan). Been together such a function is a text function, CONCATENATE and apply the INT function is to make the text is converted to digital. If the outermost layer of the sort function is used to sort the records are not removed, middle income is zero in this case the record. (The age of workers in the above-mentioned order, the formula changed to "if (f2 =" retire ", 10 ^ 100, ... ..)", that the exclusion of retired workers.)
The second step to drag the formula cell G1 to G500 cells (the most convenient way is to click the cell G1 G1 cells to the lower right corner after moving the mouse, double-click the mouse to see the black ten o'clock to finish filling G1 to G500 .)
The third step in the H2 unit fill in the formula "= MATCH (SMALL (G: G, ROW (A1)), G: G, 0)" and the second step, like drag and drop to the H501 cells. This formula is actually a synthesis of the three formulas a formula, ROW (A1) A1 is the number of rows is 1, with the drop down the order 2,3,4 ..., SMALL (G: G, ROW (A1 )) is the smallest number in the G column drop down the order as 2, 3, .. a small number, MATCH (SMALL (G: G, ROW (A1)), G: G, 0) is G column for the smallest of all the data rows, 2, 3 and small in the first few lines of data.
The fourth step the A1 to the F1 cell header copied to I1 to N1 cells, in I2 cells enter the formula "= INDEX ($ A $ 2: $ F $ 501, $ H2, COLUMN (A $ 1))" INDEX function is a reference function, that is $ A $ 2: $ F $ 501 $ H2 cell array of the first row COLUMN (A $ 1) out of the data into the I2 cell. I2 cell and then drag and drop the formula N2 cell, click the N2 to the N2 cells after cell to see the bottom right of the black ten o'clock move the mouse double-click to complete the cell I2 to fill this N501 completed.
The above description may seem complicated reality is very simple, as long as the A1 to the F1 copy of the table header cell I1 to N1, and then were in G1, H2, I2 cells enter the formula and drag down, even for unskilled EXCEL application Comrade Dr be finished within one minute.
Changes in these procedures can be a little more expense. The above example data is arranged from largest to smallest, such as a function of the column in the H SMALL to LARGE, the above example data is ranked from small to large. Such as the H2 cell formula changed to "= IF (O1 = 1, MATCH (SMALL (G: G, ROW (A1)), G: G, 0), MATCH (LARGE (G: G, ROW (A1)) , G: G, 0)) ", and the H2 cell formula down the drag and drop. This cell type 1 in O1 the example above the data is ordered from largest to smallest, O1 cells enter a number other than the example above, data on the arrangement of the small to large.
If the H column, insert several columns, such as insert a column, similar to the current G H column input column formulas, such as "= if (F2 = 0,10 ^ 100, d2)", now I changed the formula column "= IF (P1 = 1, MATCH (SMALL (G: G, ROW (A1)), G: G, 0), MATCH (SMALL (H: H, ROW (A1)), H: H, 0)) ) "is 1 in P cell input to achieve a value other than by order of prize money. so long as by changing the P1 (O1 original cell) can change the cell contents immediately sorted according to different requirements.
Second, with the screening function implementation
Subject
If an employee roster table, A2: F501, a total of 6 500 3000 cell line. A1 is the code name of the header (1 to 500), B1 for the name, C1 for gender, D1 for the ages, E1 for the education, F1 titles. Workers are now required on gender, age, education, job title interleaving filter, such as requiring a table in the same selected 1 woman aged from 22 to 45 years old, men aged 25 to 50 years of age, 2, female Dr, 3, male post-doctoral.
Methods
The first step in the G2 cells enter the formula "= IF (OR (AND (C2 =" Female ", D2> = 22, D2 <= 45), AND (C2 =" M "
D2> = 25, D2 <= 50)), ROW (A1), 0) ", in the H2 cell enter the formula" = IF (AND (C2 = "Female", E2 = "Doctor"), ROW (B1) , 0) ", enter the formula in cell I2" = IF (AND (C2 = "M", E2 = "doctoral"), ROW (B1), 0) ". enter the formula in cell J2" = IF (K $ 2 = 1, LARGE (G: G, ROW (A1)), IF (K $ 2 = 2, LARGE (H: H, ROW (A1)), IF (K $ 2 = 3, LARGE (I: I, ROW ( A1)), 0))) "and then drop down to the above-mentioned methods. G, H, I listed the meaning of the formula is where the records match the filter conditions down the line number zero otherwise, J out of the equation meaning based on the value selected K2 G, H, I sort a column in the rows and remove the substandard conditions.
The second step in the K1 cells lose the text "filter selection", A1 to the F1 table copied to the L1 to the first Q1, cell entry in the L2
The formula "= IF ($ J2 = 0,0, INDEX ($ A $ 2: $ F $ 501, $ J2, COLUMN (A $ 1 )))", then right onto Q2, and then drop down. INDEX function has explained the meaning above.
The third step in the P1 cell type 1 or 2 or 3 can achieve these three screening.
Excel, the relevant calculation days
In Excel, by subtract two date values, you can get the interval between these two dates the specific number of days; and a date value that the number of days with a constant phase addition and subtraction, you can get a date from the X days of the date of value. Such as: "2000/05/31" - "2000/05/01" be 31, "2000/05/01" and 90 are "2000/07/30." This is the Excel provides a simple but very useful although the date of computing.
However, this feature limitations in the practical work, making it unable to meet the needs of a higher level, especially the "working day" concept is increasingly widely used in various fields today, a lot of work need to be related calculations. The most common number of days such as staff attendance, delivery of the calculation, the calculation of the date of payment and so on. Excel users how these needs the solution?
A, networkdays (Start_date, End_date, Holidays) function
networkdays () function is designed to calculate the full two days between the date of numerical values. This value will not include weekend days and other specifically designated holidays.
networkdays () function takes three arguments: Start_date said the start date, End_date as the termination date, Holidays, said the holiday as one or more specific date. These values can either be entered manually, or you can reference the values of the cell. May the following year to calculate the number of working days, for example, specific description networkdays () functions:
In B1, enter the start date "2000/05/01", in B3, enter the date of termination, "2000/05/31", in E2, E3, type of statutory holidays, "2000/05/01" and "2000/05 / 02. " By the formula "= NETWORKDAYS (B2, B4, E2: E3)" we can easily calculate the number of working days in May was. Of course, we can directly enter the formula "= NETWORKDAYS (" 2000-5-1 "," 2000-6-1 ", {" 2000-5-1 "," 2000-5-2 "})" to get the results.
Second, workday (Start_date, Days, Holidays) function
workday () function is used to calculate a date (start date) working days before or after a date specified separated by a date value. It also takes three arguments: Start_date said the start date, Days before or after the Start_date excluding weekends and holidays the number of days. Days will have a positive future date; is negative over the past generation date, Holidays, said the holiday as one or more specific date.
Suppose we have May 1 with the customer signed a purchase and sales contracts, the contract 90 days after delivery. So, how should we calculate in the end day shipment?
In B1, enter the start date "2000/05/01", B3, enter the contract in time "90", in the E2, E3, type of statutory holidays, "2000/05/01" and "2000/05/02." By the formula "= WORKDAY (B2, B4, E2: E3)", we can know the delivery date is "2000/09/05." Above, we can directly enter the formula "= WORKDAY (" 2000-5-1 ", 90, {" 2000-5-1 "," 2000-5-2 "})" to be calculated.
The wait has been to try to open Excel's friends may be crying, and you say here how these two functions I do not ah? Do not worry, click "Tools" menu in the "add" and find "Analysis ToolPak" and give it a Komagari, and then determine, OK! It turned out that the two functions are embedded in the "Analysis ToolPak" add this extension function in Excel, but Excel is not in default load the add-in.
Another problem worth noting: the date that the cell number format set to "date" type, while the number of days the cells that do not set the "date" type, or you may see "1900/02 / 21 "or" 36774 "such a strange outcome yo!
Described above is the basic usage of these two functions, we can combine the best of the actual situation of their work, giving top priority to complete computing tasks more efficiently.
Click here to download the content described in this article related to the template file 13K "-" 2000/05/01 "be 31," 2000/05/01 "and 90 are" 2000/07/30. "This is to provide an Excel Although the simple but very useful date calculation functions.
However, this feature limitations in the practical work, making it unable to meet the needs of a higher level, especially the "working day" concept is increasingly widely used in various fields today, a lot of work need to be related calculations. The most common number of days such as staff attendance, delivery of the calculation, the calculation of the date of payment and so on. Excel users how these needs the solution?
A, networkdays (Start_date, End_date, Holidays) function
networkdays () function is designed to calculate the full two days between the date of numerical values. This value will not include weekend days and other specifically designated holidays.
networkdays () function takes three arguments: Start_date said the start date, End_date as the termination date, Holidays, said the holiday as one or more specific date. These values can either be entered manually, or you can reference the values of the cell. May the following year to calculate the number of working days, for example, specific description networkdays () functions:
In B1, enter the start date "2000/05/01", in B3, enter the date of termination, "2000/05/31", in E2, E3, type of statutory holidays, "2000/05/01" and "2000/05 / 02. " By the formula "= NETWORKDAYS (B2, B4, E2: E3)" we can easily calculate the number of working days in May was. Of course, we can directly enter the formula "= NETWORKDAYS (" 2000-5-1 "," 2000-6-1 ", {" 2000-5-1 "," 2000-5-2 "})" to get the results.
Second, workday (Start_date, Days, Holidays) function
workday () function is used to calculate a date (start date) working days before or after a date specified separated by a date value. It also takes three arguments: Start_date said the start date, Days before or after the Start_date excluding weekends and holidays the number of days. Days will have a positive future date; is negative over the past generation date, Holidays, said the holiday as one or more specific date.
Suppose we have May 1 with the customer signed a purchase and sales contracts, the contract 90 days after delivery. So, how should we calculate in the end day shipment?
In B1, enter the start date "2000/05/01", B3, enter the contract in time "90", in the E2, E3, type of statutory holidays, "2000/05/01" and "2000/05/02." By the formula "= WORKDAY (B2, B4, E2: E3)", we can know the delivery date is "2000/09/05." Above, we can directly enter the formula "= WORKDAY (" 2000-5-1 ", 90, {" 2000-5-1 "," 2000-5-2 "})" to be calculated.
The wait has been to try to open Excel's friends may be crying, and you say here how these two functions I do not ah? Do not worry, click "Tools" menu in the "add" and find "Analysis ToolPak" and give it a Komagari, and then determine, OK! It turned out that the two functions are embedded in the "Analysis ToolPak" add this extension function in Excel, but Excel is not in default load the add-in.
Another problem worth noting: the date that the cell number format set to "date" type, while the number of days the cells that do not set the "date" type, or you may see "1900/02 / 21 "or" 36774 "such a strange outcome yo!
Described above is the basic usage of these two functions, we can combine the best of the actual situation of their work, giving top priority to complete computing tasks more efficiently.
However, this feature limitations in the practical work, making it unable to meet the needs of a higher level, especially the "working day" concept is increasingly widely used in various fields today, a lot of work need to be related calculations. The most common number of days such as staff attendance, delivery of the calculation, the calculation of the date of payment and so on. Excel users how these needs the solution?
A, networkdays (Start_date, End_date, Holidays) function
networkdays () function is designed to calculate the full two days between the date of numerical values. This value will not include weekend days and other specifically designated holidays.
networkdays () function takes three arguments: Start_date said the start date, End_date as the termination date, Holidays, said the holiday as one or more specific date. These values can either be entered manually, or you can reference the values of the cell. May the following year to calculate the number of working days, for example, specific description networkdays () functions:
In B1, enter the start date "2000/05/01", in B3, enter the date of termination, "2000/05/31", in E2, E3, type of statutory holidays, "2000/05/01" and "2000/05 / 02. " By the formula "= NETWORKDAYS (B2, B4, E2: E3)" we can easily calculate the number of working days in May was. Of course, we can directly enter the formula "= NETWORKDAYS (" 2000-5-1 "," 2000-6-1 ", {" 2000-5-1 "," 2000-5-2 "})" to get the results.
Second, workday (Start_date, Days, Holidays) function
workday () function is used to calculate a date (start date) working days before or after a date specified separated by a date value. It also takes three arguments: Start_date said the start date, Days before or after the Start_date excluding weekends and holidays the number of days. Days will have a positive future date; is negative over the past generation date, Holidays, said the holiday as one or more specific date.
Suppose we have May 1 with the customer signed a purchase and sales contracts, the contract 90 days after delivery. So, how should we calculate in the end day shipment?
In B1, enter the start date "2000/05/01", B3, enter the contract in time "90", in the E2, E3, type of statutory holidays, "2000/05/01" and "2000/05/02." By the formula "= WORKDAY (B2, B4, E2: E3)", we can know the delivery date is "2000/09/05." Above, we can directly enter the formula "= WORKDAY (" 2000-5-1 ", 90, {" 2000-5-1 "," 2000-5-2 "})" to be calculated.
The wait has been to try to open Excel's friends may be crying, and you say here how these two functions I do not ah? Do not worry, click "Tools" menu in the "add" and find "Analysis ToolPak" and give it a Komagari, and then determine, OK! It turned out that the two functions are embedded in the "Analysis ToolPak" add this extension function in Excel, but Excel is not in default load the add-in.
Another problem worth noting: the date that the cell number format set to "date" type, while the number of days the cells that do not set the "date" type, or you may see "1900/02 / 21 "or" 36774 "such a strange outcome yo!
Described above is the basic usage of these two functions, we can combine the best of the actual situation of their work, giving top priority to complete computing tasks more efficiently.
Click here to download the content described in this article related to the template file 13K "-" 2000/05/01 "be 31," 2000/05/01 "and 90 are" 2000/07/30. "This is to provide an Excel Although the simple but very useful date calculation functions.
However, this feature limitations in the practical work, making it unable to meet the needs of a higher level, especially the "working day" concept is increasingly widely used in various fields today, a lot of work need to be related calculations. The most common number of days such as staff attendance, delivery of the calculation, the calculation of the date of payment and so on. Excel users how these needs the solution?
A, networkdays (Start_date, End_date, Holidays) function
networkdays () function is designed to calculate the full two days between the date of numerical values. This value will not include weekend days and other specifically designated holidays.
networkdays () function takes three arguments: Start_date said the start date, End_date as the termination date, Holidays, said the holiday as one or more specific date. These values can either be entered manually, or you can reference the values of the cell. May the following year to calculate the number of working days, for example, specific description networkdays () functions:
In B1, enter the start date "2000/05/01", in B3, enter the date of termination, "2000/05/31", in E2, E3, type of statutory holidays, "2000/05/01" and "2000/05 / 02. " By the formula "= NETWORKDAYS (B2, B4, E2: E3)" we can easily calculate the number of working days in May was. Of course, we can directly enter the formula "= NETWORKDAYS (" 2000-5-1 "," 2000-6-1 ", {" 2000-5-1 "," 2000-5-2 "})" to get the results.
Second, workday (Start_date, Days, Holidays) function
workday () function is used to calculate a date (start date) working days before or after a date specified separated by a date value. It also takes three arguments: Start_date said the start date, Days before or after the Start_date excluding weekends and holidays the number of days. Days will have a positive future date; is negative over the past generation date, Holidays, said the holiday as one or more specific date.
Suppose we have May 1 with the customer signed a purchase and sales contracts, the contract 90 days after delivery. So, how should we calculate in the end day shipment?
In B1, enter the start date "2000/05/01", B3, enter the contract in time "90", in the E2, E3, type of statutory holidays, "2000/05/01" and "2000/05/02." By the formula "= WORKDAY (B2, B4, E2: E3)", we can know the delivery date is "2000/09/05." Above, we can directly enter the formula "= WORKDAY (" 2000-5-1 ", 90, {" 2000-5-1 "," 2000-5-2 "})" to be calculated.
The wait has been to try to open Excel's friends may be crying, and you say here how these two functions I do not ah? Do not worry, click "Tools" menu in the "add" and find "Analysis ToolPak" and give it a Komagari, and then determine, OK! It turned out that the two functions are embedded in the "Analysis ToolPak" add this extension function in Excel, but Excel is not in default load the add-in.
Another problem worth noting: the date that the cell number format set to "date" type, while the number of days the cells that do not set the "date" type, or you may see "1900/02 / 21 "or" 36774 "such a strange outcome yo!
Described above is the basic usage of these two functions, we can combine the best of the actual situation of their work, giving top priority to complete computing tasks more efficiently.
Excel formula for calculating part
The next time you need to debug a complex Excel formulas, or just want to know that a reference in the formula for the value of the cell, try this: Select the cell containing the formula and press [F2] (or simply double-click The cell). Then, drag bright part of the equation or formula to check the cell references, press [F9] Excel will be dragged into the calculation of the brightest part of the replacement results. Press [Ctrl + Z] can be restored just replaced.
The same way you can replace the other parts of the error until you find the formula that results so far. (Press [F2] immediately after the press [F9], or drag over the entire formula by [F9].)
For example, suppose you selected contains the formula "Κ (12 × 12) / (1 + Rate) λA20 cell. press [F2], delayed light equation 12 × 12, and press [F9]. Excel that part of the formula Converted to 144. At this time, the formula becomes Κ (144) / (1 + Rate) λA20. Similarly, the delayed reference light cell A20, and press [F9], Excel will replace the values into the worksheet. When you are done Part of the formula calculation, if you want to retain the original formula, press [Esc], if you want to replace the formula, press [Enter]. You can also press [Ctrl + Z] to recover the changes.
The same way you can replace the other parts of the error until you find the formula that results so far. (Press [F2] immediately after the press [F9], or drag over the entire formula by [F9].)
For example, suppose you selected contains the formula "Κ (12 × 12) / (1 + Rate) λA20 cell. press [F2], delayed light equation 12 × 12, and press [F9]. Excel that part of the formula Converted to 144. At this time, the formula becomes Κ (144) / (1 + Rate) λA20. Similarly, the delayed reference light cell A20, and press [F9], Excel will replace the values into the worksheet. When you are done Part of the formula calculation, if you want to retain the original formula, press [Esc], if you want to replace the formula, press [Enter]. You can also press [Ctrl + Z] to recover the changes.
Excel function application tutorial
Excel is a very important office automation software, a lot of giant international companies are relying on Excel for data management. It not only can easily handle tables and graphical analysis, the more powerful automatically reflected in the data processing and computing, but many lack a strong science and engineering or data processing capabilities of Excel who do not know it is hard to further . Editor thought, do not understand the application of the Excel function is blocked the user complete control of Excel common stumbling block, however, the teaching of the present part of the contents of this article but is rare, it is particularly organized this "Excel function application"series, hope Advanced Excel who can help.
Fast input with a specific function
At work, whether for word processing or production of the report, may have to import large numbers of duplicate data.
Using Word's "AutoCorrect" command ("Tools" menu) can skillfully fast data input, but in Excel, but no similar menu command. But it comes with the VLOOKUP function can be clever solution to this problem. Such as processing sales data, a lot of the same unit often have to enter the name, you can use the VLOOKUP function, just type a letter so that we can quickly enter the name of achieving unit.
1. Create the source worksheet
Into Excel 7, click "File" menu, then click "New" command to create a new workbook.
Sheet1 worksheet to create sales reports, method is to enter the cell A1 "date" in cell B1 enter "code" in cell C1, enter the "Purchase Units" in cell D1, enter "Model" in cell E1, enter "quantity purchases", in the "F1 enter the cell," Price "in cell G1 enter" total. "
2. To create the code sheet
Sheet2 worksheet to create purchase units of the code table is to click the Sheet2, cell A1 enter the "code" in cell B1 enter "Name of purchases," in cell A2, enter "A "In cell B2, enter" Electrical and Mechanical Technology and Trade Co., Ltd. Shanghai Branch Coal Coal hydraulic and pneumatic technology center. " Input by the same methods were the names of all purchases of units and their corresponding code (the official holiday has 49 units).
3. To achieve fast data input
Click worksheet Sheet1, in A2, A3, A4 ... ... Enter the appropriate cell within the date, in the B2, B3, B4 ... ... cells enter the appropriate units within the code, D2, D3, D4 ... ... within the cell Enter the appropriate product type, in the E2, E3, E4 ... ... enter the cell number of the appropriate product purchases, in F2, F3, F4 ... ... Enter the appropriate cell products within the unit, within the cell in G2 enter the formula " = E2 * F2 ", enter in C2 cell function" = VLOOKUP (B2, Sheet2! $ A $ 2: $ B $ 50,2,0) ", C3 cells with a mouse click the fill handle and hold the bottom right corner downward drag to copy the formula, then in the "Purchase Unit" within each cell under have all entered the Name of the corresponding purchases. Drag down the same way G3 cells fill handle to the bottom right corner.
Using Word's "AutoCorrect" command ("Tools" menu) can skillfully fast data input, but in Excel, but no similar menu command. But it comes with the VLOOKUP function can be clever solution to this problem. Such as processing sales data, a lot of the same unit often have to enter the name, you can use the VLOOKUP function, just type a letter so that we can quickly enter the name of achieving unit.
1. Create the source worksheet
Into Excel 7, click "File" menu, then click "New" command to create a new workbook.
Sheet1 worksheet to create sales reports, method is to enter the cell A1 "date" in cell B1 enter "code" in cell C1, enter the "Purchase Units" in cell D1, enter "Model" in cell E1, enter "quantity purchases", in the "F1 enter the cell," Price "in cell G1 enter" total. "
2. To create the code sheet
Sheet2 worksheet to create purchase units of the code table is to click the Sheet2, cell A1 enter the "code" in cell B1 enter "Name of purchases," in cell A2, enter "A "In cell B2, enter" Electrical and Mechanical Technology and Trade Co., Ltd. Shanghai Branch Coal Coal hydraulic and pneumatic technology center. " Input by the same methods were the names of all purchases of units and their corresponding code (the official holiday has 49 units).
3. To achieve fast data input
Click worksheet Sheet1, in A2, A3, A4 ... ... Enter the appropriate cell within the date, in the B2, B3, B4 ... ... cells enter the appropriate units within the code, D2, D3, D4 ... ... within the cell Enter the appropriate product type, in the E2, E3, E4 ... ... enter the cell number of the appropriate product purchases, in F2, F3, F4 ... ... Enter the appropriate cell products within the unit, within the cell in G2 enter the formula " = E2 * F2 ", enter in C2 cell function" = VLOOKUP (B2, Sheet2! $ A $ 2: $ B $ 50,2,0) ", C3 cells with a mouse click the fill handle and hold the bottom right corner downward drag to copy the formula, then in the "Purchase Unit" within each cell under have all entered the Name of the corresponding purchases. Drag down the same way G3 cells fill handle to the bottom right corner.
Excel formula for some common error
The following is the process of teaching experience several common error, and the cause of the error, is to summarize for reference only.
# DIV / 0: appears in the formula is divided by 0 error.
# N / A: In some of the data referenced in the formula is not available.
# NAME?: the formula in the text, the system does not recognize.
# NULL!: the designated intersection and did not happen.
# NUM!: parameter input value is not correct.
# REF!: cell reference is invalid.
# VALUE!: supplied argument is not expected to use the system value type.
# DIV / 0: appears in the formula is divided by 0 error.
# N / A: In some of the data referenced in the formula is not available.
# NAME?: the formula in the text, the system does not recognize.
# NULL!: the designated intersection and did not happen.
# NUM!: parameter input value is not correct.
# REF!: cell reference is invalid.
# VALUE!: supplied argument is not expected to use the system value type.
From rookie to become a master of Excel Vlookup Function
Speaking of whether to use Excel, estimated to have used the computer as long as people have said they will use. Is it really? To the author's many years of experience in technical support for users, many users so-called "will use" is really just confined to "be with" and ourselves. Enter some content, plus some table formatting lines, points about archiving, see the document ready.
If only that, Microsoft would not have gone to great pains to develop any new version. Excel 5.0 for early had more than sufficient.
In fact, Excel is a very powerful data processing system, not just a form used to draw a table drawing tools. If you truly mastered all the features of Excel, you will find a lot of repetitive boredom of data processing, will become very beautiful, you just drag the mouse or drag bit, and then sat back and sip coffee port, would have busy dark halo to the task you have the system to help you deal better. At this time, still too busy looking at a collapse of Hutu colleagues, you have only one feeling: cool ... ...
The power of Excel to really play, you have the soul master Excel - function. Do not use Excel's functions, you are not a real user would use Excel. It is one function of different functions, the composition of different formulas to Excel the only heroes, with the martial arts masterpiece. Each function is a move of his secret, but only just learned to move his operation, it is still not enough, all recruited patients Lianshu only a, and integrated use, together, make move in move in order to really grasp His martial arts masterpiece, so that Excel can not automatically help us to complete the task.
Here, we start learning the heart of his secrets Excel heroes. I will first resort to introduce a move, followed the recipe, and then demonstrate how to apply their recipe integrated, creating new tactics. In practice, the enemy, we need to know, move is dead, people are living, move from the heart, endless.
The first one: needle in the haystack (Vlookup function)
Move to its name. This trick is used in a vast data source, automatically make you a computer to find relevant information and data, fill in the designated areas. Is that, you can let the computer in a table or a specified area to find a specified value, and thus should be returned to the relative value of the column before the row at the specified value. This move also the corresponding changes in, respectively, the two-type lookup and Hlookup. When the search data is horizontally, you can use instead of the function HLOOKUP function VLOOKUP. However, the situation is less used, does not describe here.
For example: you have a working table, above ten thousand items in the name of each item code, price, purchase date. If you need to do a separate report, and some of the goods which have been in this table have data, you can use this trick, just enter the name or code of goods, and the rest let the computer automatically finds and returns the corresponding price, date of purchase and so on.
Syntax
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Lookup_value the need to find value. Lookup_value can be a value, reference or text string.
Table_array for the need to find data in which the data sheet. You can use the name of a reference area or areas, such as a database or list.
If range_lookup is TRUE, then the first column of table_array value must be in ascending order: ..., -2, -1,0,1,2, ...,-Z, FALSE, TRUE; otherwise, VLOOKUP function can not return the correct value. If range_lookup is FALSE, table_array do not have to be sorted.
Through the "Data" menu "Sort" and select "Ascending", can be sorted in ascending numerical order.
Table_array values in the first column can be text, numbers or logical values.
Text is not case sensitive.
Col_index_num be returned to table_array matching value in column number. Col_index_num to 1, the return value in the first column table_array; col_index_num 2, the return value table_array the second column, and so on. If col_index_num less than 1, VLOOKUP returns the value of the # VALUE!; If col_index_num greater than the number of columns table_array, VLOOKUP returns the # REF!.
Range_lookup is a logical value that specifies the function VLOOKUP returns an exact match or approximate match. If TRUE or omitted, an approximate match is returned, that is, if you can not find an exact match, it returns the maximum value of less than lookup_value; if range_value is FALSE, VLOOKUP function will return an exact match. If not, it returns the error value # N / A.
Help
If the function VLOOKUP can not find the lookup_value, and range_lookup is TRUE, then use the maximum value less than or equal lookup_value.
If the first column of table_array lookup_value less than the minimum value, VLOOKUP returns the # N / A.
If the function VLOOKUP can not find the lookup_value and range_lookup is FALSE, VLOOKUP function returns the error value # N / A.
Application examples:
To facilitate comparison, I put the original data region in the same worksheet (E1: F5), the actual use, the original data can be in a different worksheet or even a different workbook (ie, different Excel files). When looking for original content in a different worksheet, table_array front need to add the sheet name, written as a "table name!" Regional context, such as "Sheet2! $ A $ 1: $ B $ 12", and if different workbook, have added the file name, such as "[file name] sheet1! $ A $ 1: $ B $ 12".
Explained
The formula "= Vlookup (A2, $ E $ 2: $ F $ 5,2, FALSE)" in A2 that to find the value of A2 cell content, that is "Apple", "$ E $ 2: $ F $ 5" tells the computer , should go to $ E $ 2: $ F $ 5 this data area to find, "2" means to find, it should return the region to the second column values, that is, the number of columns, the last "FALSE" parameter system, look for regional content have not been order, use the exact search, find even, and does not return an approximate match.
To note is that usually we are using the mouse to drag the method to fill the formula, and drag, Excel formulas in the area of the reference approach is not the same. If the reference is relative, that is ranked No. column before there is no "$" symbol, Excel for the relative displacement in the region, as indicated in column is E2: B5, onto the next column after that will automatically become E3: B6, which approach is necessary in many formulas, but in this formula is fatal, because it changed the look of the original data area, resulting in actually contains some data, because the area had not find the escape. This is also in the practical application of many users mistake, causing false search results. To solve this problem, we can use Excel's second approach to regional reference: the absolute reference. That is ranked No. column with a "$", so that the system will not be the relative displacement, no matter how delayed, the regional scope of the same. (In many cases, we will use the "name" instead of directly to the area in the manner specified, more convenient to use. The content will be introduced in other chapters)
Relative reference and absolute reference to the wording, you can let the computer for automatic conversion. Method is to first locate the current cell in the cell you want to modify, and then edit the row in the data, blackened with the mouse (in English theory called Highlight) to convert part of the press "F4" can be.
Can be found, the system has automatically filled the found value, such as Apple & cherry, to find the (Plum & Pear), shows # N / A.
If only that, Microsoft would not have gone to great pains to develop any new version. Excel 5.0 for early had more than sufficient.
In fact, Excel is a very powerful data processing system, not just a form used to draw a table drawing tools. If you truly mastered all the features of Excel, you will find a lot of repetitive boredom of data processing, will become very beautiful, you just drag the mouse or drag bit, and then sat back and sip coffee port, would have busy dark halo to the task you have the system to help you deal better. At this time, still too busy looking at a collapse of Hutu colleagues, you have only one feeling: cool ... ...
The power of Excel to really play, you have the soul master Excel - function. Do not use Excel's functions, you are not a real user would use Excel. It is one function of different functions, the composition of different formulas to Excel the only heroes, with the martial arts masterpiece. Each function is a move of his secret, but only just learned to move his operation, it is still not enough, all recruited patients Lianshu only a, and integrated use, together, make move in move in order to really grasp His martial arts masterpiece, so that Excel can not automatically help us to complete the task.
Here, we start learning the heart of his secrets Excel heroes. I will first resort to introduce a move, followed the recipe, and then demonstrate how to apply their recipe integrated, creating new tactics. In practice, the enemy, we need to know, move is dead, people are living, move from the heart, endless.
The first one: needle in the haystack (Vlookup function)
Move to its name. This trick is used in a vast data source, automatically make you a computer to find relevant information and data, fill in the designated areas. Is that, you can let the computer in a table or a specified area to find a specified value, and thus should be returned to the relative value of the column before the row at the specified value. This move also the corresponding changes in, respectively, the two-type lookup and Hlookup. When the search data is horizontally, you can use instead of the function HLOOKUP function VLOOKUP. However, the situation is less used, does not describe here.
For example: you have a working table, above ten thousand items in the name of each item code, price, purchase date. If you need to do a separate report, and some of the goods which have been in this table have data, you can use this trick, just enter the name or code of goods, and the rest let the computer automatically finds and returns the corresponding price, date of purchase and so on.
Syntax
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Lookup_value the need to find value. Lookup_value can be a value, reference or text string.
Table_array for the need to find data in which the data sheet. You can use the name of a reference area or areas, such as a database or list.
If range_lookup is TRUE, then the first column of table_array value must be in ascending order: ..., -2, -1,0,1,2, ...,-Z, FALSE, TRUE; otherwise, VLOOKUP function can not return the correct value. If range_lookup is FALSE, table_array do not have to be sorted.
Through the "Data" menu "Sort" and select "Ascending", can be sorted in ascending numerical order.
Table_array values in the first column can be text, numbers or logical values.
Text is not case sensitive.
Col_index_num be returned to table_array matching value in column number. Col_index_num to 1, the return value in the first column table_array; col_index_num 2, the return value table_array the second column, and so on. If col_index_num less than 1, VLOOKUP returns the value of the # VALUE!; If col_index_num greater than the number of columns table_array, VLOOKUP returns the # REF!.
Range_lookup is a logical value that specifies the function VLOOKUP returns an exact match or approximate match. If TRUE or omitted, an approximate match is returned, that is, if you can not find an exact match, it returns the maximum value of less than lookup_value; if range_value is FALSE, VLOOKUP function will return an exact match. If not, it returns the error value # N / A.
Help
If the function VLOOKUP can not find the lookup_value, and range_lookup is TRUE, then use the maximum value less than or equal lookup_value.
If the first column of table_array lookup_value less than the minimum value, VLOOKUP returns the # N / A.
If the function VLOOKUP can not find the lookup_value and range_lookup is FALSE, VLOOKUP function returns the error value # N / A.
Application examples:
To facilitate comparison, I put the original data region in the same worksheet (E1: F5), the actual use, the original data can be in a different worksheet or even a different workbook (ie, different Excel files). When looking for original content in a different worksheet, table_array front need to add the sheet name, written as a "table name!" Regional context, such as "Sheet2! $ A $ 1: $ B $ 12", and if different workbook, have added the file name, such as "[file name] sheet1! $ A $ 1: $ B $ 12".
Explained
The formula "= Vlookup (A2, $ E $ 2: $ F $ 5,2, FALSE)" in A2 that to find the value of A2 cell content, that is "Apple", "$ E $ 2: $ F $ 5" tells the computer , should go to $ E $ 2: $ F $ 5 this data area to find, "2" means to find, it should return the region to the second column values, that is, the number of columns, the last "FALSE" parameter system, look for regional content have not been order, use the exact search, find even, and does not return an approximate match.
To note is that usually we are using the mouse to drag the method to fill the formula, and drag, Excel formulas in the area of the reference approach is not the same. If the reference is relative, that is ranked No. column before there is no "$" symbol, Excel for the relative displacement in the region, as indicated in column is E2: B5, onto the next column after that will automatically become E3: B6, which approach is necessary in many formulas, but in this formula is fatal, because it changed the look of the original data area, resulting in actually contains some data, because the area had not find the escape. This is also in the practical application of many users mistake, causing false search results. To solve this problem, we can use Excel's second approach to regional reference: the absolute reference. That is ranked No. column with a "$", so that the system will not be the relative displacement, no matter how delayed, the regional scope of the same. (In many cases, we will use the "name" instead of directly to the area in the manner specified, more convenient to use. The content will be introduced in other chapters)
Relative reference and absolute reference to the wording, you can let the computer for automatic conversion. Method is to first locate the current cell in the cell you want to modify, and then edit the row in the data, blackened with the mouse (in English theory called Highlight) to convert part of the press "F4" can be.
Can be found, the system has automatically filled the found value, such as Apple & cherry, to find the (Plum & Pear), shows # N / A.
From rookie to become a master of Excel Len function
Ninth trick: Luban ruler of God (Len function)
This trick is used on the length of the cell contents were measured, drawn its length, do the appropriate treatment.
Syntax
LEN (text)
Text is to find the length of the text. Spaces count as characters.
Application examples:
Explained
The formula "= Len (A2)" in A2 that data to find the length of the contents of the cell A2 "****** 19,851,211 ****", system measurement, return the length of "18."
Well, we have finished school, "blind man feeling an elephant" and "Lu Ban foot of God", along with three strategies learned in the last (left head to tail-Left function to the end of the first left-Right functions, and break off both ends -Mid function), the treatment of some text, we will be able to play with hands like top.
Below we give an example, do a real operation. If you have a bunch of staff identity card number registration form, but no date of birth of employees in this column alone, but had the staff really need your birthday information. One by one to copy it? ? This time too it! Do not worry, using the above recipe, and within a minute you can be "production" out.
First idea is to use "break off both ends" function, before and after the contents of the identity card number removed, leaving the middle of the date of birth. ID number but because there are two lengths (15 and 18), directly applied, it will certainly cut wrong. So we have to add "Lu Ban foot of God", first measure out the length, coupled with the "best of both worlds," this mode, the length of the different numbers, we do different interception, the problem is solved.
The figure above, two colors of data length is inconsistent, but the use of formula, we are simply cut out the birthday data. The formula "= IF (LEN (A2) = 15,19 & MID (A2, 7,6), MID (A2, 7,8))", we use the IF function, with the Len function to determine the length of the A2, and if equal 15, returns "19 & MID (A2, 7,6)", said that if 15-bit ID number, just before the make up of its "19" (Note: "&" symbol in Excel, for the two data combined), then the data in cell A2 from 7 start, cut six out together just eight. If not 15, then return "MID (A2, 7,8))", said the A2 cell directly in the data, starting from 7, 8-bit out of the interception. Done after the first formula, whether there are thousands or tens of thousands of the following data can be delayed in the end.
For relatively simple requirements of the user, get this result has been good enough. But in fact, taken out of this data, not the date format. Therefore, we can not treat the date as to deal with it, such as changing the date format, or set conditional formatting so that the day of the birthday of the data is displayed as red.
To make the changes to date, it is actually very simple. Only one trick we have not introduced-Datevalue, at the same time, remove the data, add additional separators to allow system identification. I first formula listed here, interested users can try.
= DATEVALUE (IF (LEN (A2) = 15,19 & MID (A2, 7,2) & - & MID (A2, 9,2) & - & MID (A2, 11,2), MID (A2, 7,4) & - & MID (A2, 11,2) & - & MID (A2, 13,2)))
Remember: the result of the formula is a time serial number, date format, you can set your own. The figure, C3 and C5 cell "30720,30034," is because they can not set the date format displayed directly sequence value.
Here we look at a use of the "blind men feeling like" function example.
If there is such a string of data, format similar to Bill Gates (****) Bill.Gates @ hotmail.com or the Charles Peng (****) Charles.Peng @ sqtong.com, we need to remove part of one of the e-mail address. E-mail address because of its long-short, therefore, can not directly capture, Len function alone can not be achieved. But the investigation found that under the concept, e-mail address begins ")" after, so we can use the "blind men feeling like" Find function, first set in each data ")" position, then measure out the entire data length Len, After the subtraction, that is, the length of e-mail address, so use the "left tail to head" function will need the data can be removed. Formula is: = RIGHT (A2, LEN (A2)-FIND (), A2)).
This trick is used on the length of the cell contents were measured, drawn its length, do the appropriate treatment.
Syntax
LEN (text)
Text is to find the length of the text. Spaces count as characters.
Application examples:
Explained
The formula "= Len (A2)" in A2 that data to find the length of the contents of the cell A2 "****** 19,851,211 ****", system measurement, return the length of "18."
Well, we have finished school, "blind man feeling an elephant" and "Lu Ban foot of God", along with three strategies learned in the last (left head to tail-Left function to the end of the first left-Right functions, and break off both ends -Mid function), the treatment of some text, we will be able to play with hands like top.
Below we give an example, do a real operation. If you have a bunch of staff identity card number registration form, but no date of birth of employees in this column alone, but had the staff really need your birthday information. One by one to copy it? ? This time too it! Do not worry, using the above recipe, and within a minute you can be "production" out.
First idea is to use "break off both ends" function, before and after the contents of the identity card number removed, leaving the middle of the date of birth. ID number but because there are two lengths (15 and 18), directly applied, it will certainly cut wrong. So we have to add "Lu Ban foot of God", first measure out the length, coupled with the "best of both worlds," this mode, the length of the different numbers, we do different interception, the problem is solved.
The figure above, two colors of data length is inconsistent, but the use of formula, we are simply cut out the birthday data. The formula "= IF (LEN (A2) = 15,19 & MID (A2, 7,6), MID (A2, 7,8))", we use the IF function, with the Len function to determine the length of the A2, and if equal 15, returns "19 & MID (A2, 7,6)", said that if 15-bit ID number, just before the make up of its "19" (Note: "&" symbol in Excel, for the two data combined), then the data in cell A2 from 7 start, cut six out together just eight. If not 15, then return "MID (A2, 7,8))", said the A2 cell directly in the data, starting from 7, 8-bit out of the interception. Done after the first formula, whether there are thousands or tens of thousands of the following data can be delayed in the end.
For relatively simple requirements of the user, get this result has been good enough. But in fact, taken out of this data, not the date format. Therefore, we can not treat the date as to deal with it, such as changing the date format, or set conditional formatting so that the day of the birthday of the data is displayed as red.
To make the changes to date, it is actually very simple. Only one trick we have not introduced-Datevalue, at the same time, remove the data, add additional separators to allow system identification. I first formula listed here, interested users can try.
= DATEVALUE (IF (LEN (A2) = 15,19 & MID (A2, 7,2) & - & MID (A2, 9,2) & - & MID (A2, 11,2), MID (A2, 7,4) & - & MID (A2, 11,2) & - & MID (A2, 13,2)))
Remember: the result of the formula is a time serial number, date format, you can set your own. The figure, C3 and C5 cell "30720,30034," is because they can not set the date format displayed directly sequence value.
Here we look at a use of the "blind men feeling like" function example.
If there is such a string of data, format similar to Bill Gates (****) Bill.Gates @ hotmail.com or the Charles Peng (****) Charles.Peng @ sqtong.com, we need to remove part of one of the e-mail address. E-mail address because of its long-short, therefore, can not directly capture, Len function alone can not be achieved. But the investigation found that under the concept, e-mail address begins ")" after, so we can use the "blind men feeling like" Find function, first set in each data ")" position, then measure out the entire data length Len, After the subtraction, that is, the length of e-mail address, so use the "left tail to head" function will need the data can be removed. Formula is: = RIGHT (A2, LEN (A2)-FIND (), A2)).
From rookie to become a master of the Find function of Excel
Eighth trick: blind men feeling an elephant (Find function)
This trick is used on the original data string in a position to determine its location. Locating because the move, always starting from the specified location and returns the first matching string found in position, regardless of whether there followed a string of matches, a bit like the blind men feeling an elephant, which he said touched which hence the name "blind elephant."
Syntax
FIND (find_text, within_text, start_num)
Find_text is to find the text.
Within_text to find text that contains the text.
Start_num start looking for the specified character. within_text the first character is numbered 1 characters. If you ignore the start_num, it is assumed to be 1.
Note:
Use start_num to skip a specified number of characters. For example, suppose a text string "AYF0093.YoungMensApparel", if you want to find some of the text string that the first "Y" number, you can be start_num set to 8, so you do not find the serial number of the text part. FIND the first 8 characters from the beginning to find, but you can find at the next character find_text, then return to No. 9. FIND always returns the start within_text character number, if start_num greater than 1, will also skip the count of characters.
If find_text is empty text (), then FIND will return the value 1.
Find_text can not contain wildcards.
If there is no within_text find_text, then FIND returns the error value # VALUE!.
If start_num not greater than 0, then FIND returns the error value # VALUE!.
If start_num greater than the length of within_text, then FIND returns the error value # VALUE!.
Application example: the data containing different places, use "Find" function is very simple to determine the "province" to appear.
Explained
The formula "= FIND (province, A2)", the "Province" means the text you want to find a "province", (actual use, can also be very long string of characters). The object is to find to find the contents of cell A2 ", Dongcheng District, Dongguan City, Guangdong Province, ...", because there is no designated starting position, so the system from the first start. Back to the "3", said the "provincial" character in the third. The "Harbin, Heilongjiang Province ..." returns 4.
Find similar and, Search function also has the same function. The difference is, Find is case-sensitive, but Search is not case sensitive (when is the time to find the text is in English.)
In addition, Excel, text processing provides many functions to handle a particular double-byte characters (eg Chinese, Japanese) function, the function is generally added in the original "B", such as FIND, there a FINDB. Spoken before the LEFT, is the corresponding LEFTB and so on. In fact, the practical application, the use of non-"B" function would be sufficient. If you want to use with the "B" function, will have special attention, especially in the combination of the use function, which uses a function with a "B" form, the other has with "B" in the form of the function, all have to use The band "B" form, otherwise the result is likely to be wrong.
This trick is used on the original data string in a position to determine its location. Locating because the move, always starting from the specified location and returns the first matching string found in position, regardless of whether there followed a string of matches, a bit like the blind men feeling an elephant, which he said touched which hence the name "blind elephant."
Syntax
FIND (find_text, within_text, start_num)
Find_text is to find the text.
Within_text to find text that contains the text.
Start_num start looking for the specified character. within_text the first character is numbered 1 characters. If you ignore the start_num, it is assumed to be 1.
Note:
Use start_num to skip a specified number of characters. For example, suppose a text string "AYF0093.YoungMensApparel", if you want to find some of the text string that the first "Y" number, you can be start_num set to 8, so you do not find the serial number of the text part. FIND the first 8 characters from the beginning to find, but you can find at the next character find_text, then return to No. 9. FIND always returns the start within_text character number, if start_num greater than 1, will also skip the count of characters.
If find_text is empty text (), then FIND will return the value 1.
Find_text can not contain wildcards.
If there is no within_text find_text, then FIND returns the error value # VALUE!.
If start_num not greater than 0, then FIND returns the error value # VALUE!.
If start_num greater than the length of within_text, then FIND returns the error value # VALUE!.
Application example: the data containing different places, use "Find" function is very simple to determine the "province" to appear.
Explained
The formula "= FIND (province, A2)", the "Province" means the text you want to find a "province", (actual use, can also be very long string of characters). The object is to find to find the contents of cell A2 ", Dongcheng District, Dongguan City, Guangdong Province, ...", because there is no designated starting position, so the system from the first start. Back to the "3", said the "provincial" character in the third. The "Harbin, Heilongjiang Province ..." returns 4.
Find similar and, Search function also has the same function. The difference is, Find is case-sensitive, but Search is not case sensitive (when is the time to find the text is in English.)
In addition, Excel, text processing provides many functions to handle a particular double-byte characters (eg Chinese, Japanese) function, the function is generally added in the original "B", such as FIND, there a FINDB. Spoken before the LEFT, is the corresponding LEFTB and so on. In fact, the practical application, the use of non-"B" function would be sufficient. If you want to use with the "B" function, will have special attention, especially in the combination of the use function, which uses a function with a "B" form, the other has with "B" in the form of the function, all have to use The band "B" form, otherwise the result is likely to be wrong.
Excel from the rookie to become a master of the IS function
The third measure: testing the waters (IS function)
This trick is used on the current value of a cell to determine the type, in order to know its type, and then take next course of action, it is called testing the waters.
IS function, a total of nine worksheet functions. Broad class of functions for the IS, you can test the type of numerical values based on parameter returns TRUE or FALSE. For example, if the value is a reference to the blank cells, the function returns a logical value ISBLANK TRUE, otherwise returns FALSE.
Syntax
ISBLANK (value)
ISERR (value)
ISERROR (value)
ISLOGICAL (value)
ISNA (value)
ISNONTEXT (value)
ISNUMBER (value)
ISREF (value)
ISTEXT (value)
Value for the required test values. Are as follows: blank (empty cell), error values, logical values, text, numbers, reference values, or for any of the above parameters the name of the reference.
Application examples:
Explained
The formula "= ISBLANK (A1)", said a cell of A1 to judge whether it is empty. The case is empty, it returns "True" value, if not empty, it returns "False) value.
The above example, the figure, B1 and B2 in the cell A1 and A2 respectively function the cells to determine whether the blank. The results show a true and a false
This trick is used on the current value of a cell to determine the type, in order to know its type, and then take next course of action, it is called testing the waters.
IS function, a total of nine worksheet functions. Broad class of functions for the IS, you can test the type of numerical values based on parameter returns TRUE or FALSE. For example, if the value is a reference to the blank cells, the function returns a logical value ISBLANK TRUE, otherwise returns FALSE.
Syntax
ISBLANK (value)
ISERR (value)
ISERROR (value)
ISLOGICAL (value)
ISNA (value)
ISNONTEXT (value)
ISNUMBER (value)
ISREF (value)
ISTEXT (value)
Value for the required test values. Are as follows: blank (empty cell), error values, logical values, text, numbers, reference values, or for any of the above parameters the name of the reference.
Application examples:
Explained
The formula "= ISBLANK (A1)", said a cell of A1 to judge whether it is empty. The case is empty, it returns "True" value, if not empty, it returns "False) value.
The above example, the figure, B1 and B2 in the cell A1 and A2 respectively function the cells to determine whether the blank. The results show a true and a false
From rookie to become a master of Excel If function
The second measure: it both ways (If the function)
This trick is used to perform a condition to judge the true and false values, calculated according to the logical truth value, return different results. If the result is true, it returns a true, if false, it returns another value, can be described both ways.
Syntax
IF (logical_test, value_if_true, value_if_false)
Logical_test that evaluates to TRUE or FALSE any value or expression. For example, A1> = 60 is a logical expression, if the value in cell A1 is greater than or equal to 60, expression is the TRUE, otherwise FALSE. This argument can use any comparison operator.
Value_if_true logical_test is TRUE, the returned value. For example, if the parameter is the text string "budget"and logical_test parameter is TRUE, then the IF function displays the text "budget. " If TRUE and value_if_true logical_test is empty, this argument returns 0 (zero). If you want to show TRUE, please use the logical value-based argument TRUE. Value_if_true can be another formula.
Value_if_false logical_test is FALSE, the returned value. For example, if the parameter is the text string "within budget"and logical_test parameter is FALSE, then the IF function displays the text "within budget. " Logical_test is FALSE, and if ignored Value_if_false (ie there is no comma after value_if_true), will return the logical value FALSE. If FALSE and Value_if_false logical_test is empty (ie, after value_if_true comma, and followed by closing parenthesis), this argument returns 0 (zero). Value_if_false can be another formula.
Help
Up to seven nested IF functions with value_if_false and value_if_true parameters can construct complex test conditions.
Value_if_true and value_if_false in the calculation of parameters, the IF function returns the appropriate return value after statement execution
This trick is used to perform a condition to judge the true and false values, calculated according to the logical truth value, return different results. If the result is true, it returns a true, if false, it returns another value, can be described both ways.
Syntax
IF (logical_test, value_if_true, value_if_false)
Logical_test that evaluates to TRUE or FALSE any value or expression. For example, A1> = 60 is a logical expression, if the value in cell A1 is greater than or equal to 60, expression is the TRUE, otherwise FALSE. This argument can use any comparison operator.
Value_if_true logical_test is TRUE, the returned value. For example, if the parameter is the text string "budget"and logical_test parameter is TRUE, then the IF function displays the text "budget. " If TRUE and value_if_true logical_test is empty, this argument returns 0 (zero). If you want to show TRUE, please use the logical value-based argument TRUE. Value_if_true can be another formula.
Value_if_false logical_test is FALSE, the returned value. For example, if the parameter is the text string "within budget"and logical_test parameter is FALSE, then the IF function displays the text "within budget. " Logical_test is FALSE, and if ignored Value_if_false (ie there is no comma after value_if_true), will return the logical value FALSE. If FALSE and Value_if_false logical_test is empty (ie, after value_if_true comma, and followed by closing parenthesis), this argument returns 0 (zero). Value_if_false can be another formula.
Help
Up to seven nested IF functions with value_if_false and value_if_true parameters can construct complex test conditions.
Value_if_true and value_if_false in the calculation of parameters, the IF function returns the appropriate return value after statement execution
Excel from the rookie to become a master of the Right function
Sixth move: to the end of the first left (Right function)
This move and the move the opposite way of the interception of the last character from the beginning, back to front interception of the contents of a user-specified length.
Syntax
RIGHT (text, num_chars)
RIGHTB (text, num_bytes)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Num_chars specify that you want RIGHT number of characters extracted.
Note: Num_chars must be greater than or equal to 0.
If num_chars greater than the length of the text, then RIGHT to return all the text.
If you ignore the num_chars, is assumed to be 1.
Application examples:
Explained
The formula "= Right (A2, 8)", said to be intercepted in the A2 data contents of the cell A2 "... Dongguan, Guangdong Province Tel: 22222222, ""8 " that started from the last of the interception of 8 characters, Therefore, the system returns "22222222. " Despite the length of the raw data missing, but we only care about the last eight phone numbers.
This move and the move the opposite way of the interception of the last character from the beginning, back to front interception of the contents of a user-specified length.
Syntax
RIGHT (text, num_chars)
RIGHTB (text, num_bytes)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Num_chars specify that you want RIGHT number of characters extracted.
Note: Num_chars must be greater than or equal to 0.
If num_chars greater than the length of the text, then RIGHT to return all the text.
If you ignore the num_chars, is assumed to be 1.
Application examples:
Explained
The formula "= Right (A2, 8)", said to be intercepted in the A2 data contents of the cell A2 "... Dongguan, Guangdong Province Tel: 22222222, ""8 " that started from the last of the interception of 8 characters, Therefore, the system returns "22222222. " Despite the length of the raw data missing, but we only care about the last eight phone numbers.
Excel from the rookie to become a master of the Left function
Fifth move: stay head to tail (Left function)
This trick is used to intercept the raw data. Interception approach is a character from the beginning, the interception of the contents of a user-specified length.
For example: In a work table, a column address information is recorded with provincial, city, street and so on. If you want to insert more than one, adding provinces in the data for selected provinces, it can intercept the function automatically, without manual input.
Syntax
LEFT (text, num_chars)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Num_chars up to the LEFT of the specified number of characters extracted.
Num_chars must be greater than or equal to 0.
If num_chars greater than the length of the text, then LEFT returns all of the text.
If you omit num_chars, is assumed to be 1.
Application example: the data containing different places, the use of "Left" function is very simple separation from their provinces.
Explained
The formula "= Left (A2, 3)", said to be intercepted in the A2 data contents of the cell A2 ", Dongcheng District, Dongguan City, Guangdong Province, ... ", "3"indicates the beginning from the first of the interception of three characters, so The system returns "Guangdong province. "
This trick is used to intercept the raw data. Interception approach is a character from the beginning, the interception of the contents of a user-specified length.
For example: In a work table, a column address information is recorded with provincial, city, street and so on. If you want to insert more than one, adding provinces in the data for selected provinces, it can intercept the function automatically, without manual input.
Syntax
LEFT (text, num_chars)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Num_chars up to the LEFT of the specified number of characters extracted.
Num_chars must be greater than or equal to 0.
If num_chars greater than the length of the text, then LEFT returns all of the text.
If you omit num_chars, is assumed to be 1.
Application example: the data containing different places, the use of "Left" function is very simple separation from their provinces.
Explained
The formula "= Left (A2, 3)", said to be intercepted in the A2 data contents of the cell A2 ", Dongcheng District, Dongguan City, Guangdong Province, ... ", "3"indicates the beginning from the first of the interception of three characters, so The system returns "Guangdong province. "
Excel from the rookie to become a master of the MID function
Seventh move: break off both ends (MID functions)
With the above two different strokes, this move not start from the first interception, the interception did not start from the last, but by the beginning of user-specified location and characters in length. Therefore, if the user specified starting from the first, and the Left function will be the same.
Syntax
MID (text, start_num, num_chars)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Start_num is to extract the text of the first character position. Text start_num first character is 1, and so on.
Num_chars specify that you want MID to return from the text the number of characters.
Note:
If start_num greater than the length of the text, the MID returns empty text ().
If start_num less than the text length, but more than the text start_num num_chars with the length of the MID only returns up until the end of the text characters.
If start_num less than 1, MID returns the # VALUE!.
If num_chars is negative, MID returns the # VALUE!.
If num_bytes is negative, then the MIDB return an error value # VALUE!.
Application examples:
Explained
The formula "= MID (A2, 7,8)" expressed in the A2 A2 to intercept the data contents of the cell "****** 19,851,221 ****"," 7 "that started from 7, a total of Interception of 8 characters, so the system returns the user to intercept birthday time "19851221."
OK, we have studied three strategies, but the reader may have discovered, the actual work, the raw data does not appear so neat, so we are very easy to intercept with the above three strategies. For example, the third measure example, I use the ID number is 18, but in fact, many people still use 15-bit identification number, so that, due to the length of the original data are inconsistent, resulting in the interception will cut wrong. Another example is our first example, we cut the three, but in practice, and some provinces in the name itself is 3, and therefore this situation, simply can not get the right to apply to the content.
As I mentioned in the first, in the actual work use, a single formula often is not enough, the need to use the combination of tactics. For example, identity card numbers have different lengths, we can moves, add to the median of the judgments, if the length is 18 bits, then take an 8-bit, if it is 15, then take 6. Remember that we learned both ways in front of it? But this trick very useful Oh, we often used. In addition, I will introduce the following two strokes, is used to determine the contents of the cell. One is the "blind man feeling an elephant" (Find function), allowing users to specify the contents of the cell to locate the characters to confirm their location. When the position is confirmed, the interception is an easy thing. Another trick is the "Lu Ban foot of God" (Len function), the contents of the cell allows users to measure the length, obtained its length, do the corresponding intercept processing
With the above two different strokes, this move not start from the first interception, the interception did not start from the last, but by the beginning of user-specified location and characters in length. Therefore, if the user specified starting from the first, and the Left function will be the same.
Syntax
MID (text, start_num, num_chars)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Start_num is to extract the text of the first character position. Text start_num first character is 1, and so on.
Num_chars specify that you want MID to return from the text the number of characters.
Note:
If start_num greater than the length of the text, the MID returns empty text ().
If start_num less than the text length, but more than the text start_num num_chars with the length of the MID only returns up until the end of the text characters.
If start_num less than 1, MID returns the # VALUE!.
If num_chars is negative, MID returns the # VALUE!.
If num_bytes is negative, then the MIDB return an error value # VALUE!.
Application examples:
Explained
The formula "= MID (A2, 7,8)" expressed in the A2 A2 to intercept the data contents of the cell "****** 19,851,221 ****"," 7 "that started from 7, a total of Interception of 8 characters, so the system returns the user to intercept birthday time "19851221."
OK, we have studied three strategies, but the reader may have discovered, the actual work, the raw data does not appear so neat, so we are very easy to intercept with the above three strategies. For example, the third measure example, I use the ID number is 18, but in fact, many people still use 15-bit identification number, so that, due to the length of the original data are inconsistent, resulting in the interception will cut wrong. Another example is our first example, we cut the three, but in practice, and some provinces in the name itself is 3, and therefore this situation, simply can not get the right to apply to the content.
As I mentioned in the first, in the actual work use, a single formula often is not enough, the need to use the combination of tactics. For example, identity card numbers have different lengths, we can moves, add to the median of the judgments, if the length is 18 bits, then take an 8-bit, if it is 15, then take 6. Remember that we learned both ways in front of it? But this trick very useful Oh, we often used. In addition, I will introduce the following two strokes, is used to determine the contents of the cell. One is the "blind man feeling an elephant" (Find function), allowing users to specify the contents of the cell to locate the characters to confirm their location. When the position is confirmed, the interception is an easy thing. Another trick is the "Lu Ban foot of God" (Len function), the contents of the cell allows users to measure the length, obtained its length, do the corresponding intercept processing
Xiao Yang Sum function with an example to explain the conditions detailed usage excel SumIF
Welcome to Office College, xiaoyang visit here waiting for you!
Today, we continue to learn together with you in all commonly used Excel spreadsheet formulas (functions) of the detailed usage and master the formula, many of our work will greatly improve the efficiency, we must learn yo, there do not understand can go to the Office College Questions (ask.officexy.com) here to ask Oh, we have progress, I will be pleased got:)
This lecture, we learn about the conditions sum function SumIF
Features
Under the conditions specified sum on a number of cells.
Syntax
SUMIF (range, criteria, sum_range)
Range is the range of cells for the conditional.
Criteria for determining which cells will be added to the sum of the conditions, the form of a number, expression, or text. For example, the condition can be expressed as 32, "32", "> 32", "apples".
Sum_range sum of the actual need of the cell. Only when the corresponding cell in Range to meet the conditions, the fishes in the cell sum_range sum. If you omit sum_range. Directly on the Range in the sum of the cell.
Help
Microsoft Excel also provides a number of other functions, they can analyze data based on a condition. For example, if you want to calculate a range of cells, text strings or numbers within the number of occurrences, you can use the COUNTIF function. If you want the formula to return two values under a condition of a certain value (for example, sales bonuses based on the specified sales return), you can use the IF worksheet function
Example 1
B C D
1 Product Name Price (unit: yuan) of total sales commission
2 A 11 100
3 B 100 10
4 C 200 30
5 D 20 40
6 E 45 50
7 F 88 80
8
9 products price is greater than 50 the total sales commission 120 = SUMIF (C3: C8, "> 50", D3: D8)
Example 2
B C D
Math 13 gender language
Male 45 68 14
Female 91 81 15
Male 80 70 16
Male 65 87 17
18 M 7279
Female 77 87 19
20
The language scores of 21 boys total 304 = SUMIF (B14: B19, "Male", D14: D19)
The "male" and "women" because it is text-based, needs to be placed under the English double quotes ("man" and "female") in
Today, we continue to learn together with you in all commonly used Excel spreadsheet formulas (functions) of the detailed usage and master the formula, many of our work will greatly improve the efficiency, we must learn yo, there do not understand can go to the Office College Questions (ask.officexy.com) here to ask Oh, we have progress, I will be pleased got:)
This lecture, we learn about the conditions sum function SumIF
Features
Under the conditions specified sum on a number of cells.
Syntax
SUMIF (range, criteria, sum_range)
Range is the range of cells for the conditional.
Criteria for determining which cells will be added to the sum of the conditions, the form of a number, expression, or text. For example, the condition can be expressed as 32, "32", "> 32", "apples".
Sum_range sum of the actual need of the cell. Only when the corresponding cell in Range to meet the conditions, the fishes in the cell sum_range sum. If you omit sum_range. Directly on the Range in the sum of the cell.
Help
Microsoft Excel also provides a number of other functions, they can analyze data based on a condition. For example, if you want to calculate a range of cells, text strings or numbers within the number of occurrences, you can use the COUNTIF function. If you want the formula to return two values under a condition of a certain value (for example, sales bonuses based on the specified sales return), you can use the IF worksheet function
Example 1
B C D
1 Product Name Price (unit: yuan) of total sales commission
2 A 11 100
3 B 100 10
4 C 200 30
5 D 20 40
6 E 45 50
7 F 88 80
8
9 products price is greater than 50 the total sales commission 120 = SUMIF (C3: C8, "> 50", D3: D8)
Example 2
B C D
Math 13 gender language
Male 45 68 14
Female 91 81 15
Male 80 70 16
Male 65 87 17
18 M 7279
Female 77 87 19
20
The language scores of 21 boys total 304 = SUMIF (B14: B19, "Male", D14: D19)
The "male" and "women" because it is text-based, needs to be placed under the English double quotes ("man" and "female") in
订阅：
博文 (Atom)