Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to write Vlookup function in Excel in Python". In daily operation, I believe that many people have doubts about how to write Vlookup function in Excel in Python. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "how to write Vlookup function in Excel in Python". Next, please follow the editor to study!

The Vlookup function, which can be regarded as a basic function that a data specialist must use, is really easy to use. But you may notice that once the amount of data of Excel is too large, it is difficult to open it, not to mention that after opening it, you have to enter a formula to calculate, which is even more difficult. Have you ever thought of Python, which is called "one-size-fits-all oil"? it seems that he can do anything. Isn't that awesome? It's a lot of data for Excel, but for Python, it should be a piece of cake. Today I'm going to take you to learn how to use the Vlookup function in Excel and Python respectively.

Introduction to data sources

As shown in the figure, there is a "vlookup.xlsx" file, "A1:F11" is our data source area, and "K1:L5" is our lookup source area. Our goal is to add a column of data to the G column of the data source area to find out the name representations under different types.

Introduction to Vlookup function

I think everyone should know this function, and you don't need me to introduce it in too much detail, so I'll simply introduce the syntax of the vlookup function to you.

Parameter description: vlookup (to find the target, search area, match the value of the column, exactly match the OR fuzzy search); use a popular sentence to explain the use of the vlookup function: for each target to be found, it can search from the specified search area to return the value you want to find. Using Vlookup function in Excel

After you understand the syntax of the Vlookup function for the data sources mentioned above, let's take a look at how to use the Vlookup function in Excel.

Look at the image above: first, we have added a "name" column to the G1 cell. Then, in the G2 cell, we write a vlookup formula, E2 represents each value to be found, K1:L5 represents the area to be found, we use the F3 shortcut key to change this area into an absolute reference, because our search area is this fixed range, 2 indicates the number of columns in the search area, and 0 indicates an exact match. Finally, use the filling handle to pull down the filling.

Using Vlookup function in Python

Using the openpyxl library in Python, you can fill in the formula. So before using openpyxl, you need to install the library using pip install openpyxl.

From openpyxl import load_workbook

Workbook = load_workbook ("vlookup.xlsx")

Sheet = workbook ["Sheet1"]

Sheet ["G1"] = "name"

For i in range (2) sheet.maxroomrow1:

Sheet [f "G {I}"] = f'=VLOOKUP (E {I}, $Kang 1v v 5m 2e 0)'

Workbook.save (filename = "vllokup1.xlsx")

In openpyxl, you read an existing Excel file and use the load_workbook class, so you need to import this class in advance. Next, instantiate the load_workbook ("vlookup.xlsx") object to get a workbook object. Then, use workbook ["Sheet1"] to activate the Sheet1 table in the workbook, indicating that we want to operate on the table. After completing the above operation, you can fill in the vlookup formula below.

First, we add a header to the G1 cell using sheet ["G1"] = "name". Then we write a loop, the second to the last line of the loop, and for each G column cell, we write the above vlookup formula. Finally, remember to save it.

At this point, the study on "how to write the Vlookup function in Excel in Python" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.