Video není dostupné.
Omlouváme se.
Excel Sentiment Analysis: Sentiment Analysis - Episode 2062
Vložit
- čas přidán 5. 08. 2024
- Microsoft Excel Tutorial: Sentiment Analysis in Excel.
Welcome to another episode of the MrExcel Podcast, where we explore all things Excel. In this episode, we will be discussing sentiment analysis in Excel. It all started on Thanksgiving night when a friend of ours mentioned wanting to do sentiment analysis on Twitter data. I immediately realized that Excel has a way to do this and decided to create a video on the topic.
But first, what exactly is sentiment analysis? Well, if you have a survey with multiple choice options, it's easy to analyze the data. However, when you have long answers with sentences or paragraphs, it becomes difficult to manually go through and determine the sentiment. This is where sentiment analysis comes in. There are two types - human supervised learning and machine learning. In the past, human supervised learning involved going through a portion of the data and categorizing positive and negative words. However, this method is limiting and requires constant re-categorization for different data sets.
This is where Excel's Azure Machine Learning comes in. It uses the MPQA Subjectivity Lexicon, a generic dictionary with over 5,000 negative words and 2,500 positive words. This method works well for short sentences, Tweets, and Facebook posts. However, it may struggle with double negatives. To use this tool, simply go to the Insert tab, click on Store, and search for Azure Machine Learning. Then, specify your input and output range, making sure to change the heading to match the Schema.
Once the analysis is complete, you will have two columns - Sentiment and Score. The Score represents the percentage of positivity or negativity, with 100% being extremely positive and 0% being extremely negative. You can use this data to create a pivot table and see the average score for each sentiment category. This is a quick and efficient way to analyze large amounts of data and determine the overall sentiment. So next time you have a lot of data to analyze, give sentiment analysis in Excel a try using the free Azure Machine Learning Add-in. Thanks for watching and be sure to tune in for more Excel tips and tricks from MrExcel.
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
#excel
#microsoft
#microsoftexcel
#exceladdin
#sentimentanalysis
#OpinionMining
#EmotionAnalysis
#CustomerSentiment
#SentimentScoring
#LanguageProcessing
#OpinionAnalysis
#SentimentAnalytics
This video answers these common search terms:
how to do sentiment analysis in excel
how to make a sentiment analysis excel
excel sentiment analysis tool
analyze sentiment using excel
sentiment analysis add-in for excel
text sentiment analysis in excel
sentiment analysis formula in excel
excel sentiment analysis tutorial
text mining for sentiment in excel
analyze emotions in excel
excel sentiment analysis template
using excel for text sentiment analysis
Table of Contents:
(00:00) Introduction to Sentiment Analysis in Excel
(00:29) Using Excel for sentiment analysis
(01:11) Limitations of human supervised learning
(02:08) Introduction to Azure Machine Learning
(03:06) Steps for using the Text Sentiment Analysis Excel Add-in
(03:43) Results and interpretation
(04:47) Using a pivot table for further analysis
(05:27) Recap
(06:15) Clicking Like really helps the algorithm
It is easy to quantify survey data when it is multiple choice
You can use a pivot table to figure out what percentage each answer has
But what about free-form text answers? These are hard to process if you have hundreds or thousands of them.
Sentiment Analysis is a machine-based method for predicting if an answer is positive or negative.
Microsoft offers a tool that does Sentiment Analysis in Excel - Azure Machine Learning.
Traditional sentiment analysis requires a human to analyze and categorize 5% of the statements.
Traditional sentiment analysis is not flexible - you will rebuild the dictionary for each industry.
Excel uses MPQA Subjectivity Lexicon (read about that at bit. ly/1SRNevt)
This generic dictionary includes 5,097 negative and 2,533 positive words
Each word is assigned a strong or weak polarity
This works great for short sentences, such as Tweets or Facebook posts
It can get fooled by double-negatives
To install, go to Insert, Excel Store, search for Azure Machine Learning
Specify an input range and two blank columns for the output range.
The heading for the input range has to match the schema: tweet_text
Companion article at: sfmagazine.com/post-entry/may-...
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...
thank you much you literally rescued my qualitative research with this video. No one in my supervision team has this skill and had me doing it by hand it was a nightmare.
That is Amazing. Excel is always growing! Thanks for sharing this.
Thank you.
Thanks for showing us the Excel Tool use for Sentimental Analysis using the Azure Machine Learning tool. Very helpful session. Thank!!🙏🙏👌👌
Very useful feature. Clear demonstration. Thank you very much.
thank you sir, this one of its kind vdo, very nice when analysis customer feedback
thanks for the video. simple yet powerful. i have list of potential customer to target now
You are a lifesaver!
Pretty darn cool! My sentiment is positive :)
Very interesting video. It would be great and much appreciated if you could do a video on how to pull in tweets, Facebook posts, etc. into Excel so we could do a Sentiment Analysis on them.
Thank You very much you saved my project
thanks a ton ,extremely helpful sir
Awesome!
Very good example
Now, this service is paid!
Thanks for the awesome, wonderful, helpful and useful sentiment trick!!!!!!!! Very Good. Tremendous!!!! Sentiment = 1?
thanks mrexcel !!!!
Excellent video!
I am wondering... If there are another machine learning stuff like this we can use on Excel?
Thanks, man can you tell me can I use this building my own dictionary?
How Score is calculated by Azure Machine learning tool? What is the logic of calculating the score?
Hello MrExcel
I have a question I hope you can help me with. Do you know how this Ad-in calculats the scores?
I would like to know how this Ad-in operates, but sadly I can't finde an explanaition on the Microsoft Azure Homepage.
Thank you for the great video.
Hello Bill!
I use excel on a daily basis to create reports.
Usually a report is presented in one sheet with 5 pivot tables placed one under another.
Additionally I use slicers that are connected with all pivots.
Whenever I use slicers, pivots "shrink" and there is some space created between them.
I could use some macro that hides/unhides those rows, every time slicers are used.
I'm not happy with one I am using now.
Could you help me with this problem or direct me where to look answer?
Have a great day!
Hello, in the second step, could you tell how to see accuracy if ı make labeling?
Is there way to go deeper in this analysis and find trends on common key phrases or text that shows up in relation to positive responses vs negative responses (without manually going through?
That functionality sounds great, but it is not currently available in the add-in.
@@MrXL thank you so much for your reply! I still love what you taught and showed it to my coworker for we can implement. I noticed that there is still a great chunk of it being analyzed incorrectly. My research showed that the azure machine learning is 80% correct of the time. Is there a way we can tie this in with VBA (and macros) or with something else to Increase the accuracy (or alter it a bit)?
@@victoriaa4211 The free add-in is going to be worth what you pay for it. I know that Frontline Systems has a better version that is paid. I tried it once 5-7 years ago. If I recall correctly, you had to train the system before you used it. I am not the expert on these high-end analytics tools. I know just enough to send you to: www.solver.com/about-our-company
@@MrXL thank you so much!!
Hi guys, I had issue making this run so thought I'd share how I got it to work. Firstly, it can't be a CSV. I didn't try every doc type but it works as a XLSX. Secondly, you can't select the entire row, you really have to only select the information you need (IE. A1:A20). For output, you just select 1 cell for it put information (IE: Sheet1!T1). Lastly, everything seems to be case sensitive. GL to those who had trouble like I did.
Thanks for these tips
I can't find Azure Machine Learning on the store!
How can I know if there's a double negative appearance? btw, what it is really actually so that the definition can be clear to me.
+James David Matoy if someone commented “this is not the worst way to paint a crime scene”, you have a lot of negative words, but the sentiment is sort of neutral. The algorithm would see all of those negative words and assume it is a very negative response.
@@MrXL S'il vous plait je sais pas pourquoi l'extention Azure ML ne fonctionne pas correctement pour moi quand je lance predict ça se lance pas merci pour votre aide
Hi Bill
Very Informative Video, Please help me to do the conditional formating on below scenario in an easy way,
Task is to find minimum and maximum value in a row and format those cells in Green and Red colour filled. ( conditional formating )
This formatting should be copied in ann easy , may be drag & drop to all rows with filler handle( I tried in Excel 2013, to copy formating to all rows by draging with filler handle(+) - with RH click, fill Formating only, but formating not happened.
Please help.
Thanks
can I do with other language than english?
Mr. Excel have a case of study for you:
I have multiple CZcams Channels (13 to be exact) and want to consolidate all data from the analytics module. They allow me to download a workbook for each one, all them have the same strcture but is very difficult to me to have all them in a single workbook.
It is also possible to make the consolidation dynamic? because I have to make a report every 1st of each month, and is a nightmare for me :(
Thank you in advance.
Wow Thanks :)
Hi. my add ins are not working. How can i fix this issue?
is it only for English? because i have data with Indonesia and it doesn't work
Thank you for this. I have followed your steps and nothing happens :-( I am sure it is something really simple.
hahaha
The same thing happened to me! I hope he responds so we know why
hi
i have formola
=CONCATENATE("=","VLOO","KUP(D2,A","1:B2,2,0)")
or
="="&"VLOO"&"KUP(D2,A"&"1:B2,2,0)"
the result is
=VLOOKUP(D2,A1:B2,2,0)
but it only text
="1"&"+"&"2"
or
=CONCATENATE("=","1","+","2")
the result is
=1+2
but it only text
so how to convert result to be formula not text
Error! {"error":{"code":"LibraryExecutionError","message":"Module execution encountered an internal library error.","details":[{"code":"TableSchemaColumnCountMismatch","target":" (AFx Library)","message":"data: The table column count (0) must match the schema column count (1)."}]}} - what should I do with that?
how do you solve this problem
@Regyn B. Avena Hi How did you solve this I arranged the sentences but couldn't get the prediction.
select predict as a batch under output
Nothing happens when I click on Predict - nothing generates on the two columns over
I figured it out!
how did you figure it out? I am having the same issue.
@@mukulagarwal9400 Did you manage to figure it out.
Hello, sir. I tried using this both as a batch or RRS but it gives me the error. Please help.
Error! {"error":{"code":"LibraryExecutionError","message":"Module execution encountered an internal library error.","details":[{"code":"ColumnNamesMustMatchSchema","target":" (AFx Library)","message":"inputColumnNames: Column names must match the schema. Column names only in input: Here's to our 17th. #WallpaperWednesday t.co/Xl59uuErpZ. Column names only in schema: tweet_text"}]}}