Learn Excel techniques by following our WeChat public account:excelperfect
Tags:PythonandExcel
Now,Excel has integrated Python, which means you can use Python to implement more flexible and powerful Excel solutions.
In the worksheet cell, just like entering a formula, type:
=py
Press the Tab key, and a Python cell will be created, as shown in the following image 1.
Figure 1
Now, you can enter Python statements in that cell or the formula bar. As indicated in Figure 1, after entering, press the Enter key to create a new line and continue entering Python commands; pressing Ctrl+Enter will submit and run the entered Python command.
After submission, you will see a refresh icon or BUSY text in that cell, and after a few seconds, the result will appear. Why is that?
This is because the Python integrated into Excel is hosted in the cloud on Azure (i.e., Microsoft’s cloud database), which requires an internet connection to work. The Python code is stored in the worksheet cell, and when you submit or update the Python cell, it communicates with the cloud, runs the code there, and returns the result to the Python cell. It is this cloud interaction that may cause the calculation time of the Python cell to be longer than that of a regular cell.
Most Python code is multi-line, and when entering code in the Python cell, pressing Enter will insert a new line for code input; if all code is entered, pressing Ctrl+Enter indicates submission of the final code.
Next, let’s demonstrate how Python code runs in Excel with an example.
In the worksheet cell A1, enter:
=py
After pressing the Tab key, enter:
# Generate virtual data
from faker import Faker
fake = Faker()
“Initialize Faker”
As shown in the following image 2.
Figure 2
After entering, press Ctrl+Enter to submit, and the result is shown in the following image 3.
Figure 3
In the above code:
# Generate virtual data
is similar to writing code in a professional Python editor; if a line starts with # symbol, it indicates that the line is a comment, not Python code, and is only used to describe the function of the code.
from faker import Faker
is used to import the Faker package, allowing access to its functionalities.
fake = Faker()
assigns Faker to a variable named fake. This means that all other Python cells in this worksheet can use the variable fake to access the Faker package and its functionalities. Any variable created in cell A1 can be accessed throughout the worksheet.
“Initialize Faker”
displays a message in cell A1, doing nothing but providing a text description. Sometimes Python cells may not display anything or may only show a word. By ending the code with text in quotes, you can describe the function of that cell, which also helps with documentation.
Similarly, in cell A2, enter:=py, press the Tab key, and enter Python code:
# Random name
fake.name()
Press Ctrl+Enter to submit, and the result is shown in the following image 4.
Figure 4
Code:
fake.name()
This code displays a virtual name. Note that every time this Python cell is recalculated, this name will change.
To display multiple names, you can use Python loop statements. In cell A4, enter Python code in the same way:
# Multiple virtual names
fake_names = [fake.name() for _ in range(20)]
Press Ctrl+Enter to see the result in the following image 5.
Figure 5
This code contains a loop to create 20 names. The brackets define a list, and the code inside the brackets is a loop that creates 20 individual names.
However, this code does not display the names, as shown in the following image 6, it only returns the text “list”. To see all the names, you need to click the dropdown icon to the left of the formula bar PY and select “ Excel values”, as shown in the following image 6.
Figure 6
This way, you will see all the randomly generated names, as shown in the following image 7.
Figure 7
Similarly, when this Python cell is recalculated, these names will also change.
In the above image 6, we see that after clicking the dropdown arrow, there are two options: “ Python object” and “ Excel values”. Note that the default is usually “ Python object”.
Additionally, on the right side of the Excel worksheet interface, there will be a “ Python editor” pane, as shown in the following image 8, where you can see all the code in the cells, edit the code, and view error messages.
Figure 8
Note: This article is compiled from a4accounting.com.au for reference for interested friends.
Feel free to leave comments below to improve this article and help more people learn better knowledge.Welcome to Knowledge Planet: Perfect Excel Community for technical exchanges and questions, to obtain more electronic materials, and join the dedicated WeChat discussion group through the community for easier communication.