Microsoft Excel Tutorial: Using a function in Python for Excel calculations.
The problem today: Count how many times a word occurs in a cell in Excel.
To download this workbook: https://www.mrexcel.com/youtube/6Ydb6rIls6M/
The first solution is a series of six formulas in Excel, including SUBSTITUTE, LEN, and more. While it is complicated in Excel, there is a much easier way in Python, using the .Count function. So, Python has a simpler version but how do you call the function from Excel?
After the Python solution, I used the Excel Labs add-in to convert the original six formulas to a LAMBDA.
Other topics here:
Saving Python function in a cell
Adding Text as line line of a Python script to appear in the cell.
Printing to the Python Console
Removing the Index column using two sets of square brackets
In the Out take, the Python Function can not be part of a table.
Table of Contents
(0:00) Python functions in Excel
(0:17) Problem Statement Count ThisWord in Phrase
(0:54) SUBSTITUTE function in Excel
(1:16) LEN function in Excel
(2:29) Python count function
(2:54) Storing Python function in A1
(3:29) Adding Text to Python to appear in cell
(4:15) Calling the Python function using Excel data
(4:45) Call per row or whole frame
(5:10) Printing to Python Console in Excel
(5:35) Does not work next to Ctrl+T tables
(5:56) Removing Index returned by Python in Excel
(6:52) Returning one column? Use double square brackets to prevent Index
(7:39) Writing a LAMBDA using Excel Labs add-in
(8:11) Add Function from Grid
(8:48) Testing the LAMBDA version
(9:33) Wrap-up
(9:59) Like, Subscribe, Ring the Bell
(10:06) Outtake Does not work with Tables
コメント
I would like to see you use this python in excel on Mega millions lottery to predict next draw winning numbers
Nice trick to calculate the length change. I paused the video before seeing that and created the following (A1 contains the transcript, A2 the word to find):
=LET(
Transcript, TEXTSPLIT(A1,,” “),
SearchForText, A2,
COUNT(SEARCH(SearchForText,Transcript))
)
What? I like Tables and their references in formulas. When you are used to it, it works.
You tried to enter PY function on the adjacent column. Normally, when data is entered on adjacent column or row below the last row, the table will expand to include them as table data. Have you tried entering PY formula by leaving a column gap? i.e. leave a blank column in between table and PY function. I can’t try it on my PC, because the PY feature hasn’t shown up for me yet, since signing up on Beta channel.