Merge spreadsheet data

Spreadsheet data merge

"Lookup" in Excel means "find" in Chinese. There are three functions related to "Lookup" in Excel: VLOOKUP, HLOOKUP and LOOKUP. Among them, the VLOOKUP function searches by column. Its syntax is: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). lookup_value is the value to be looked up, table_array is the table area to be looked up, col_index_num is the number of columns where the value to be returned is, [range_lookup] is an optional parameter used to specify whether to perform approximate matching.

1. Function Search the specified data in the first column of the table and return the data in the specified column in the row where the specified data is located. 2. Grammar Standard format: VLOOKUP(lookup_value,table_array,col_index_num, range_lookup)

VLOOKUP (The data needs to be found in the first column, the data table in which the data needs to be found, the column number of a certain column value needs to be returned, the logical value True or False) 1.Lookup_value is "Needs to be in the first column of the data table The data to look for in the column", which can be a numeric value, a text string, or a reference. 2.Table_array is "the data table in which data needs to be found", and you can use cell ranges or range names, etc. ⑴If range_lookup is TRUE or omitted, the values ​​in the first column of table_array must be arranged in ascending order, otherwise, the function VLOOKUP cannot return the correct value. If range_lookup is FALSE, table_array does not need to be sorted. ⑵The values ​​in the first column of Table_array can be text, numbers or logical values. If it is text, the text is not case-sensitive. 3.Col_index_num is the column number of the matching value to be returned in table_array. When Col_index_num is 1, the value in the first column of table_array is returned; when Col_index_num is 2, the value in the second column of table_array is returned, and so on. If Col_index_num is less than 1, the function VLOOKUP returns the error value #VALUE! ; If Col_index_num is greater than the number of columns in table_array, the function VLOOKUP returns the error value #REF!. 4.Range_lookup is a logical value, indicating whether the function VLOOKUP returns an exact match or an approximate match

4. Application examples A B C D 1 Number Name Salary Department 2 2005001 Jay Chou 2870 Office 3 2005002 Elva Hsiao 2750 Personnel Department 4 2005006 Zheng Zhihua 2680 Supply Department 5 2005010 Tu Honggang 2980 Sales Department 6 2005019 Sun Nan 2530 Finance Department 7 20050 36 Meng Tingwei 2200 Union Column A has been Sorting (the fourth parameter is default or TRUE) VLOOKUP(2005001,A1:D7,2,TRUE) is equal to "Jay Chou" VLOOKUP(2005001,A1:D7,3,TRUE) is equal to "2870" VLOOKUP(2005001,A1: D7,4,TRUE) is equal to "office" VLOOKUP(2005019,A1:D7,2,TRUE) is equal to "Sun Nan" VLOOKUP(2005036,A1:D7,3,TRUE) is equal to "2200" VLOOKUP(2005036,A1:D7, 4,TRUE) is equal to "union" VLOOKUP(2005036,A1:D7,4) is equal to "union

How to merge data in two spreadsheets in excel

1. The fastest way to merge multiple worksheets into one table is to use macro processing:

For example, merge multiple tables into a total table:

Total table leaves only one title

Right-click the Summary Worksheet tab, view the code, copy the following code, and run F5:

Sub worksheet merge()

For Each st In Worksheets

If st.Name ActiveSheet.Name Then st.UsedRange.Offset(1, 0).Copy [a65536].End(xlUp).Offset(1, 0)

Next

End Sub

2. Multiple tables will be merged into the master table.

3. The following example: Run the above code in Sheet Total, and all monthly sub-tables will be summarized to facilitate subsequent processing without the need to paste them again and again.

How to merge data from multiple excel tables together

Expand All

1. Create a new folder, put the tables to be merged into it, create a new table, open it with excel, right-click Sheet1

2. Select to view the code (PS: Excel has one item, WPS does not)

3. Copy the following code into the text box:

Sub merges all worksheets in all workbooks in the current directory ()

Dim MyPath, MyName, AWbName

Dim Wb As workbook, WbN As String

Dim G As Long

Dim Num As Long

Dim BOX As String

Application.ScreenUpdating = False

MyPath = ActiveWorkbook.Path

MyName = Dir(MyPath & ＂\＂ & ＂*.xls＂)

AWbName = ActiveWorkbook.Name

Num = 0

Do While MyName "＂"

If MyName AWbName Then

Set Wb = Workbooks.Open(MyPath & "\＂" & MyName)

Num = Num 1

With Workbooks(1).ActiveSheet

.Cells(.Range("B65536").End(xlUp).Row 2, 1) = Left(MyName, Len(MyName) - 4)

For G = 1 To Sheets.Count

Wb.Sheets(G).UsedRange.Copy .Cells(.Range(＂B65536＂).End(xlUp).Row 1, 1)

Next

WbN = WbN & Chr(13) & Wb.Name

Wb.Close False

End With

End If

MyName = Dir

Loop

Range(＂B1＂).Select

Application.ScreenUpdating = True

MsgBox "A total of "& Num &" all worksheets under "& Num &" workbooks have been merged. As follows: "& Chr(13) & WbN, vbInformation, "Prompt"

End Sub

4. Click Run. After a period of time (depending on the size and number of tables), the merge is completed.

The above is the detailed content of Merge spreadsheet data. For more information, please follow other related articles on the PHP Chinese website!