Vertikale Spalte als Chart

[fdi]

Hallo zusammen:

Ich bin nicht sehr vertraut mit Excel und hoffe das mir hier jemand unter die Arme greift. Meine Frage:

Ich habe eine Spalte mit 5 verschiedenen Woertern (Institution, Business,...,....,....) die sich in unregelmaeßigen Abstaenden wiederholen. (z.B. Institution, Institution, Instituiton, Business, Business,.... - das alles vertikal in einer Spalte) Ich will nun diese fuenf Woerter in einer Grafik haben und wie oft sie in dieser Spalte vorkommen. Habe schon in verschiedenen "Hilfen" gesucht aber leider ohne Erfolg.

Benutze Excel 2003.

Danke Euch fuer die Hilfe!!!

 

[Greg Truby]

Example:
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0"> <tr valign="top"> <th width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Product</nobr></font></th> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gadgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Widgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gizmos</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Woozles</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Woozles</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Woozles</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gadgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gizmos</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gizmos</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gizmos</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Woozles</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gadgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Widgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Widgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Widgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Widgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Woozles</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gizmos</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Widgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Woozles</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gadgets</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gizmos</nobr></font></td> </tr> <tr valign="top"> <td width="63" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gizmos</nobr></font></td> </tr></table>

Use a pivot table and drag the field in as both a ROW field and a DATA field.

<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" > <tr valign="top"> <th width="104" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Count of Product</nobr></font></th> <th width="34" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr></nobr></font></th> </tr> <tr valign="top"> <td width="104" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Product</nobr></font></td> <td width="34" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Total</nobr></font></td> </tr> <tr valign="top"> <td width="104" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gadgets</nobr></font></td> <td width="34" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>4</nobr></font></td> </tr> <tr valign="top"> <td width="104" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Gizmos</nobr></font></td> <td width="34" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>7</nobr></font></td> </tr> <tr valign="top"> <td width="104" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Widgets</nobr></font></td> <td width="34" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>6</nobr></font></td> </tr> <tr valign="top"> <td width="104" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Woozles</nobr></font></td> <td width="34" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>6</nobr></font></td> </tr> <tr valign="top"> <td width="104" height="21" valign="bottom"><font face="Monospace" size="1"><nobr>Grand Total</nobr></font></td> <td width="34" height="21" align="right" valign="bottom"><font face="Monospace" size="1"><nobr>23</nobr></font></td> </tr></table>

 

[fdi]

Thanks Greg for your reply to my question but it does not yet really resolve my problem yet.

The product column is not the only column in the excel file. There are several other columns with different informations. To clarify it a little bit more: Each row in the excel file represents a different person and the Gadgets or Woozles are only one attribute of each row next to e.g. birthdate, address, etc. In the beginning of the excel list are new rows added every day. This makes it difficult to put all the data in a row and field data every day. What I need is especially this Gizmos column in a chart so I can keep visualize how many persons are Gizmos and Gadgets, etc.

The question is: Is there a way in excel to count several words (Gizmos, Gadgets, Woozles, Widgets) who are in one column? And also puts the new entries at the top of the excel list in the chart and the changes who are made within the column?

OK I think it's good for now and I hope someone out there understands what I was writing up there. Would like to add a example but didn't know how to. Best, fdi

 

[Greg Truby]

You could always just use the COUNTIF() function. That would be the simplest. However, I would still recommend using a pivot table because a pivot will automatically list any new items that suddenly appear (also a good way to spot typographical errors if data is hand-keyed); whereas using a group of COUNTIF()'s will miss that. As for handling a varying input range, just use a Dynamic Named Range (DNR) (see the first example in my standard DNR text below).
<hr />Dynamic Named Ranges
Dynamic named ranges can be used just like normal named ranges in formulas, or as sources for things like pivot tables and charts. However instead of a normal named range where the group of cells being referred to is static, dynamic ranges typically take advantage of the Offset() and CountA() [or Count()] functions to create a range that resizes to include new data appended to the bottom of the range.

To Create a Dynamic Range

1. From the menu Insert | Name | Define...
2. In the Names in Workbook textbox, type in the name for the Named Range - probably something like "Data" or "DataRange".
3. In the Refers to textbox type in a formula like the following (note: you may find it helpful to hit the F2 key while in the textbox to put it into "edit mode"):<ul type=square>
4. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),9) to start at A1, include all non-blank rows and 9 columns. (Good example for pivot tables where you want to include headers.)
5. =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1) to start at B2, include all numeric rows and 1 column. (Good example for a chart data series.)
6. =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) to start at A2, include one less than the count of all non-blank rows and 1 column. (Good example for a chart category series where values are not numeric.)

[*]You're ready to reference the Named Dynamic Range like you would a normal Named Range.[/list]