Now you can use Python in Microsoft Excel

Si eres fanático de Python y Excel, tenemos buenas noticias para ti. Microsoft anunció recientemente que ahora puede usar Python en Excel para crear funciones personalizadas, automatizar tareas y mejorar su análisis de datos.

Python is a popular programming language that is widely used for data science, machine learning, web development and more. It is known for its simplicity, readability and versatility. Excel is a powerful spreadsheet application that is widely used for data entry, calculation, visualization and report generation. It is known for its ease of use, flexibility and compatibility.

By combining Python and Excel, you can take advantage of the best of both worlds. You can use Python to write custom functions that can be called from Excel cells, just like the built-in functions. You can also use Python to automate repetitive tasks, such as importing data, formatting cells, or creating charts. And you can use Python to enhance your data analysis, using libraries such as pandas, numpy or matplotlib to manipulate, process and visualize your data.

To use Python in Excel, you must install the latest version of Excel and the Python add-in. The add-in allows you to write Python code in a dedicated editor inside Excel and run it in a separate process. You can also use your favorite IDE or Python editor outside Excel and connect it to the add-in via a local server.

Python is now part of Excel and you can use it to do amazing things with your data. This is a turning point for anyone who felt limited by Excel’s built-in functions and features. Python is a powerful and versatile programming language that can handle data analysis, visualization, machine learning and much more. And now you can use it directly within your spreadsheets, without any additional configuration.

How does it work? It’s simple: just use the new =PY() function to write Python code in any cell. The code runs in the cloud and returns the result to the worksheet. You can use Python to create pivot tables, charts, graphs and other data-driven elements that will make your spreadsheets more informative and interactive. You can also access popular Python libraries such as Matplotlib, pandas and scikit-learn, thanks to Microsoft’s partnership with Anaconda, the leading Python distribution.

This is a great opportunity for Excel users to learn and apply Python skills to their data projects. Whether you are a beginner or an expert, you will find Python to be a useful and expressive tool for working with data in Excel. To try it out, you must be part of the Microsoft 365 Insider program, which gives you access to preview features in Office applications. If you are not yet a member, you can sign up here. Then, open Excel and start writing Python code in your cells using the =PY() function. You’ll be amazed at what you can do with Python in Excel.

To create a custom function in Python, you must use the @xlfunc decorator to register it with the plugin. For example, the following code defines a function that returns the square of a number:

python

from xlwings import xlfunc

@xlfunc

def square(x):

back x**2

«`

To call this function from Excel, you must enter it in a cell with the prefix xlwings, followed by the function name and arguments. For example:

««surpass

=xlalas.square(5)

«`

This will return 25 in the cell.

To automate tasks in Python, you need to use the xlwings module to interact with Excel objects, such as workbooks, worksheets, ranges, charts, and so on. For example, the following code imports a CSV file into a new workbook and creates a chart from the data:

«`pitón

import xlwings as xw

import pandas as pd

# Create a new workbook

wb = xw.Book()

# Import CSV file to first sheet

df = pd.read_csv(‘data.csv’)

ws = wb.sheets[0]

ws.range(‘A1’).value = df

# Creates a chart from the data.

chart = ws.charts.add()

chart.set_source_data(ws.range(‘A1’).expand())

chart.chart_type = ‘line’.

«`

To run this code from Excel, you must use the Run Python button on the ribbon or press Alt+F8 to open the macro dialog box.

To improve your data analysis in Python, you need to use the pandas module to work with data frames, which are tabular data structures that can store and manipulate heterogeneous data. You can also use other modules, such as numpy for numerical computation or matplotlib for plotting. For example, the following code reads an Excel file into a data frame, performs some calculations and statistics on the data, and plots a histogram:

«`pitón

import pandas as pd

import matplotlib.pyplot as plt

# Read Excel file in a data frame

df = pd.read_excel(‘data.xlsx’)

# Calculate some metrics on the data.

average = df[‘Score’].average()

std = df[‘Score’].std()

median = df[‘Score’].median()

min = df[‘Score’].min()

max = df[‘Score’].max()

# Print metrics

print(f’Mean: {media}’)

print(f’Standard deviation: {std}’)

print(f’Median: {mediana}’)

print(f’Minimum: {min}’)

print(f’Maximum: {max}’)

# Plot a histogram of the scores.

plt.hist(df[‘Score’], bins=10)

plt.xlabel(‘Score’)

plt.ylabel(‘Frequency’)

plt.title(‘Histogram of scores’)

plt.show()

«`

To run this code from Excel, you must use the same methods as before.

As you can see, using Python in Excel can open up many possibilities for improving your productivity and creativity. You can create custom functions to suit your needs, automate time-saving tasks, and improve your analysis of data that reveals insights. If you would like to learn more about using Python in Excel, you can consult the official Python add-in documentation here: https://docs.microsoft.com/en-us/office/dev/add-ins/excel /complementos-python

We hope you enjoy using Python in Excel and share your feedback with us. Happy coding!