星期日, 十一月 06, 2011

(未知标题)

From Chandoo.org

(未知标题):

Last week Joyce asked a question on the Chandoo.org, Comment 24.


I’m wondering if there’s a way to count the number of occurrences of words when they’re all in a cell? Like this:

A1: “Windows NT, Networking, Firewalls, Security, TL, Training”

A2: “Networking, Networking, Training, Security, TL, Training”

A3: “Security, TL, Firewalls, Security, Networking, Windows NT”


Hui responded with an Array Formula:


=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)


As the formula is an Array Formula it is entered with Ctrl Shift Enter.




Setup the Problem


Copy the Data Above into Cells A1:A3 or download the example file here: Example File


Enter the value Security into cell C10


And array enter the formula


D10: =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)



Pull The Formula Apart


Lets take a look inside this and see how it works


We will break this formula apart and look at each section independently and then put the answers back together.


=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)


In a cell below the data


D13: =LEN(A1:A3) but don’t press Enter, Press F9


Excel displays ={57,56,57}


This is the number of characters in each cell A1:A3


ie: A1 has 57 characters, A2 has 56 characters, A3 has 57 characters,




=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)


In another cell below the data


D15: =LEN(SUBSTITUTE(A1:A3,C10,”")) but don’t press Enter, Press F9


Excel displays ={49,48,41}


What this section does is measure the length of each cell in A1:A3 but only after substituting the word being searched for from C10 with ””, which is a zero length string.


So the second array is shorter by X times the length of the word in C10


=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)


Next we add up the difference between the two arrays


So you can see we have two arrays of numbers


Array 1 = {57,56,57}


Array 2 = {49,48,41}


If we subtract Array 2 from Array 1


= {57-49, 56-48, 57-41}


= {8, 8, 16}


We can do this in Excel to Check


In Cell D17 enter


=LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,”")) and press F9


Excel displays: = {8, 8, 16}


=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)


The next part is to sum these up


Obviously the sum of 8, 8 & 16 is 32


We can check that


D21: =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,”"))) and press F9


Excel displays: 32



=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)


The final part of this is to divide the sum (32 in this case) by the length of the text in C10 “Security” = 8 Characters


=32 / 8


= 4



OTHER POSTS IN THIS SERIES:


You can learn more about how to pull Excel Formulas apart in the following posts


Formula Forensic 001 – Taruns Problem



WHAT FORMULAS WOULD YOU LIKE EXAMINED


If you have any formulas you would like explained please feel free to leave a post here or send me an email:


If the formula is already on Chandoo.org or Chandoo.org/Forums, simply send the link to the post and a Comment number if appropriate.


If sending emails please attach an Excel file with the formula and data





没有评论: