Função Python Excel – PY

Veja como usar a função Python no Excel PY, passo-a-passo com exemplos, download e vídeo.

Veremos ao longo do artigo o que é a função Python PY no Excel, como a utilizar, conceitos, atalhos, restrições e exemplos práticos.

O que é a Função Python Excel PY?

A função Python tem por finalidade permitir que códigos Python sejam executados em células do Excel.

Ela roda em um runtime seguro do Microsoft Cloud.

Uma questão importante é que este código deve ser estático, isso significa que ele não aceita por exemplo o uso de fórmulas para gerar o código.

Ela trabalha com os dados de intervalos, nomes, tabelas e também conexões power query no Excel para usar no dataframe, objeto que armazena os dados, muito semelhante à tabelas para que possam ser manipulados no Python.

Referência: Função PY Microsoft

DataFrame no Excel Função PY

Um DataFrame é uma estrutura de dados bidimensional semelhante à tabelas. No Python o DataFrame é um objeto da biblioteca pandas.

Esta é uma das principais bibliotecas do Python e são a estrutura principal para analisar dados com o Python no Excel.

Para usar a função Python no Excel, pode pressionar CTRL+SHIFT+ALT+P ou ir na guia Fórmulas->Inserir Python.

Para entrar dados de um intervalo e usá-lo como DataFrame basta selecionar um intervalo de dados, tabela, colocar um intervalo nomeado ou uma conexão do power query.

A função do Python que importa estes dados do Excel para o DataFrame é a xl.

Você poderá carregar o DataFrame em uma variável nomeada e usar as funções do próprio Pandas para realizar cálculos e operações que estão disponíveis nela e em outras bibliotecas previamente carregadas ou ainda importar outras.

Para carregar os resultados de um código Python no Excel pode clicar sobre ele e pré-visualizar os dados ou ainda colocar os dados à partir na própria célula do código Python clicando com o botão direito e escolhendo Saída em Python e a opção Objeto do Excel.

Abaixo um exemplo da função describe.

Atalhos Python Excel Função PY

Abaixo temos uma lista dos atalhos da função Python no Excel

Também pode copiar a imagem abaixo já formatada e usar em um word ou imprimir diretamente já formatado para colocar na sua escrivaninha até acostumar com eles.

Exemplo de Uso da Função Python Excel com Regex

Uma função muito importante no mundo da programação e tratamento de dados é a função REGEX.

A REGEX permite que textos muito complexos sejam extraídos usando esta sintaxe.

Neste exemplo usamos a biblioteca REGEX do Python importando ela e usando os comandos abaixo:

import re
txt=xl("B11")
p = re.findall('[0-9]{7}-[0-9]{2}.[0-9]{4}.[0-9]{1}.[0-9]{2}.[0-9]{4}', txt)
p[0]

No nosso exemplo, estamos extraindo o número da intimação de um processo.

Veja que ele fica em qualquer local do texto, não necessariamente no começo ou no final.

E temos então ela extraída usando este código REGEX que não existe uma função no Excel para tal, que antes era obrigatório usar um VBA para tal uso e agora pode usar a biblioteca Python Regex no Excel.

Veja mais sobre REGEX.

Exemplo Código Python para Conciliar Dados no Excel

Neste exemplo prático do uso da função Python PY no Excel estamos conciliando dados de duas fontes de dados.

No exemplo queremos identificar as diferenças entre as notas fiscais que estão lançadas no Financeiro e no contábil.

Para tal usamos o código abaixo:

df1=xl("B8:C13", headers=True)
df2=xl("E8:F13", headers=True)
df1.merge(df2, left_on='Nota fiscal', right_on='Nota fiscal', suffixes=('_financeiro', 'contabil'), how=('outer'))

Assim, temos no Excel a importação da lista de dados de notas fiscais do Financeiro e do Contábil nas primeiras duas linhas e usando o procedimento do DataFrame merge realizamos a comparação das duas listas pelo número da nota fiscal fazendo um outer apply.

Com isso temos o seguinte resultado facilmente:

No resultado conseguimos então ver as diferenças. Que a nota fiscal 1 foi lançada no financeiro com o valor de 100 e no contábil com o valor de 130, que a nota 4 não foi encontrada no contábil e a 6 não foi encontrada no financeiro.

Como Usar Gráficos Python PY no Excel

O Python no Excel permite também que façamos a criação de gráficos muito interessantes.

Veja este exemplo abaixo:

Neste código temos a crição de um gráfico Scatter.

df = pd.DataFrame([[5.1, 3.5, 0], [4.9, 3.0, 0], [7.0, 3.2, 1],
                   [6.4, 3.2, 1], [5.9, 3.0, 2]],
                  columns=['length', 'width', 'species'])
ax1 = df.plot.scatter(x='length',
                      y='width',
                      c='DarkBlue')
ax2 = df.plot.scatter(x='length',
                      y='width',
                      c='species',
                      colormap='viridis')

Ele está com dados fixos como exemplo, mas poderia importar os dados o dataframe do Excel e criar este ou outros muitos gráficos das biliotecas Python.

Além disso o gráfico pode ser plotado diretamente no Excel, bastando para isso clicar sobre ele e escolher a opção image.

Ela será colocada na célula ao lado da função, pode usar o mesclar células e aumentar seu tamanho ou senão apenas aumentar manualmente a largura da coluna e da linha para que fique mais visível.

Bibliotecas e Pacote

A função Python no Excel PY já vem com várias bibliotecas importadas, são elas:

Desta forma as bibliotecas do pacote Anaconda que estão acima listadas já estão disponíveis sem a necessidade de serem importadas e você também pode importar outras, como fizemos no caso do exemplo do REGEX usando import.

Permissões Python Excel

O uso do Python no Excel é restrito em algumas situações, as que identificamos e que vimos conforme o link de segurança do Python da Microsoft são os seguintes:

1. Consulta de sites por exemplo para webscrapping
2. Consulta de arquivos e fontes de dados
3. Criar arquivos

Para manipular arquivos de fora do Excel use o Power Query ou mesmo o VBA para retornar os dados para o Excel e então utilizar eles no Python.

Ele também não se mostrou capaz de criar arquivos, como vemos no código abaixo que resulta em um erro:

 from openpyxl import Workbook
 from openpyxl.utils.dataframe import dataframe_to_rows
wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=True, header=True):
    ws.append(r)

for cell in ws['A'] + ws[1]:
    cell.style = 'Pandas'

wb.save("pandas_openpyxl.xlsx")

Este código realiza a criação de uma planilha chamada pandas_openpyxl.xlsx, mas quando usamos ela no Python do Excel temos a seguinte mensagem de erro:

PermissionError: [Errno 13] Permission denied: pandas_openpyxl.xlsx

Ou seja, não conseguimos criar arquivos ela exibe o erro de permissão, o mesmo ocorreu ao tentar aplicar códigos para raspagem de sites usando webscrapping no Python do Excel.

Veja mais sobre segurança no uso da função Python no Excel neste link: https://support.microsoft.com/en-us/office/data-security-and-python-in-excel-33cc88a4-4a87-485e-9ff9-f35958278327

Download Planilha Função Python PY Excel

Clique no botão abaixo para realizar o  download do Excel de exemplo:

Baixe a planilha