Auswertung einer Tabelle mit Excel in neues Blatt

Computer: Software & Programmierung Office Software
1

Hallo liebe Experten,

ich habe folgendes Anliegen. Ich habe mit Excel eine Tabelle erstellt, in der in der ersten Spalte Stellennummern aufgeführt sind. Unter Umständen ist dieselbe Nummer mehrmals vorhanden. In den Zeilen der Stellenummer sind Prozentangaben sowie Stellenkategorien angegeben.
Ich möchte nun eine automatische Auswertung in einem neuen Tabellenblatt, in der die Prozentangaben zu jeder Stellennummer und Stellenkategorie summiert werden.

Ich verdeutliche die Problematik mal an einem Beispiel:
Stellenummer 001, nächste Zeile 100%, nächste Zeile A1, nächste Zeile 50%
darunter befindet sich noch einmal dieselbe Stellennummer:
Stellenummer 001, nächste Zeile 100%, nächste Zeile A1, nächste Zeile 30%

In der Auswertung möchte ich die 100% nur einmal gezählt haben und der Kategorie A1 zugeordnet haben, die 50% und 30% sollen ebenfalls addiert werden und der Kategorie A1 in einer anderen Spalte zugeordnet werden. Das ganze soll für alle Zeilen der Tabelle automatisch funktionieren.

Ich hoffe ich konnte das Problem einigermaßen verständlich beschreiben.

Vielen Dank schon einmal für Eure Antworten.
Ingo

2

Grüezi Ingo

Wenn die ersten 100% immer 100% sind, dann könntest Du das Ganze mit einer Pivot-Tabelle lösen.

Dort dann die Kategroie in den Zeilenbereich und ebenso die 1 ersten 100%.
Dann die anderen %-Angaben im Wertefeld summieren lassen.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
3

Hallo Thomas,

vielen Dank für Deine Antwort. Die ersten 100% sind leider nicht immer 100% es gibt auch andere Werte.

Hast Du noch eine Idee?

Viele Grüße, Ingo

1 „Gefällt mir“
4

Grüezi Ingo

vielen Dank für Deine Antwort. Die ersten 100% sind leider
nicht immer 100% es gibt auch andere Werte.

Hast Du noch eine Idee?

Gegenfrage:
Hast Du das Ganze mal ausprobiert?.. :wink:

Wenn die ersten %-Werte mit den jeweiligen Kategorien stets übereinstimmen, dann passt das dennoch.

Bitte gib uns einige relevante Daten an und schildere dann exakt was Du als Ergebnis erwartest - die bisherigen Angaben waren offenbar mehr als rudimentär und entsprechen nicht deinen wirklichen Werten.

Am einfachsten dürfte es sein, wenn Du deine Demo-Mappe erstellst und dann gemäss FAQ:2606 auf einen WebSpace hochlädst und den Link dann hier nennst.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
5

Hallo Thomas,

eine Testtabelle habe ich unter folgendem Link abgelegt:
http://www.file-upload.net/download-2672894/Testtabe…

Ziel soll sein, dass in der Auswertung die Summen der jeweiligen Stellendotierungen erscheinen. Das Problem ist, dass auf manchen Stellennummern mehrere Personen „sitzen“ und diese in der Datentabelle daher mehrfach aufgeführt sind, jedoch im Umfang nur einmal gezählt werden dürfen.
Es soll demnach ausgewertet werden wieviele Stellen in Prozent sind geplant von Dotierung X und mit welchem Anteil und Dotierung sind die Stellen tatsächlich besetzt.

Ich bin mit Pivot-Tabellen leider bis jetzt völlig unerfahren…

Vielen Dank für Deine Hilfe.
Ingo

1 „Gefällt mir“
6

Hallo Ingo,

http://www.file-upload.net/download-2672894/Testtabe…

schau ich mir gleich an. Vorab etwas Anderes, ich habe die Beitragsfolge mitverfolgt aber nicht kapiert was genau du eigentlich möchtest.

Du sagst was von
Stellennummern
Stellenkategorien
Stellendotierungen
Es soll demnach ausgewertet werden wieviele Stellen in Prozent sind
geplant von Dotierung X und mit welchem Anteil und Dotierung sind die
Stellen tatsächlich besetzt.

Denn 95% deiner Angaben sind überflüssig, verwirrend usw.
Was du eigentlich willst muß ich mir mühsam zusammendenken/raten :frowning:
Schau bitte mal hier, dann weißt du was ich meine:

http://www.online-excel.de/fom/butout.php?f=1&b=3

Übrigens, ich bin nicht böse :smile:)) Ich schaue mir jetzt deine Mappe an und versuch eine Lösung zu finden.

Ich bin mit Pivot-Tabellen leider bis jetzt völlig
unerfahren…

Tja, nur manche Auserwählte arbeiten mit PTs *gg*
Aber, gibts eine Pivot-Lösung so erledigt das unsre Schweizer Abteilung samt Bedienungsanleitung für dich für die PT, ich kann das leider nicht.

Gruß
Reinhard

7

Sorry für die umständliche Formulierung. Du hast das aber genau richtig verstanden…

„Es soll demnach ausgewertet werden wieviele Stellen in Prozent sind
geplant von Dotierung X und mit welchem Anteil und Dotierung sind die
Stellen tatsächlich besetzt.“

Habe inzwischen auch versucht das mit der Pivot-Tabelle hinzubekommen, allerdings habe ich das Problem, dass die Stellen, die mit mehreren Personen besetzt sind, demnach auch in mehreren Zeilen auftreten auch mehrfach als geplant gezählt werden. Hier müsste ein Eingrenzung her, die die erste Zeile (Stellennummer) filtert.

Vielen Dank und viele Grüße
Ingo

1 „Gefällt mir“
8

Hallo Ingo,

Sorry für die umständliche Formulierung. Du hast das aber
genau richtig verstanden…

ich glaub nich :frowning:
Vielleicht ist es besser, du füllst das Blatt Auswertung aus, mein Ergebnis überzeugt mich nicht so:

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[Testtabelle\_Haerder.xls]!Auswertung
 │ A │ B │ C │
──┼───────────┼──────────┼─────┤
1 │ Dotierung │ lt. Plan │ IST │
──┼───────────┼──────────┼─────┤
2 │ E14 │ │ │
──┼───────────┼──────────┼─────┤
3 │ E13 │ 95 │ 200 │
──┼───────────┼──────────┼─────┤
4 │ E12 │ 100 │ 100 │
──┼───────────┼──────────┼─────┤
5 │ E10 │ 100 │ 100 │
──┼───────────┼──────────┼─────┤
6 │ E6 │ 50 │ │
──┼───────────┼──────────┼─────┤
7 │ E5 │ │ 50 │
──┼───────────┼──────────┼─────┤
8 │ A14 │ 90 │ 100 │
──┴───────────┴──────────┴─────┘
A1:C8
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

9

Hallo Reinhard,

in der Tat ist das Ergebnis noch nicht so wie gewünscht…
Die Daten von Plan und IST sind vertauscht und bei E13 wurde Plan doppelt gezählt (die Stelle mit gleichen Stellennummer).

Ich habe unter
http://www.file-upload.net/download-2673743/Testtabe…

die Auswertungstabelle mit dem gewünschten Ergebnis ausgefüllt.
Vielleicht fällt Dir noch was ein, das wäre großartig.

Viele Grüße
Ingo

10

Ich habe unter
http://www.file-upload.net/download-2673743/Testtabe…

die Auswertungstabelle mit dem gewünschten Ergebnis
ausgefüllt.

Hallo Ingo,

vielleicht so:

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[Testtabelle\_Haerder-1.xls]!Ausw
 │ A │ B │ C │
──┼───────────┼──────────┼─────┤
1 │ Dotierung │ lt. Plan │ IST │
──┼───────────┼──────────┼─────┤
2 │ E14 │ #NV │ 0 │
──┼───────────┼──────────┼─────┤
3 │ E13 │ 100 │ 95 │
──┼───────────┼──────────┼─────┤
4 │ E12 │ 100 │ 100 │
──┼───────────┼──────────┼─────┤
5 │ E10 │ 100 │ 100 │
──┼───────────┼──────────┼─────┤
6 │ E6 │ #NV │ 50 │
──┼───────────┼──────────┼─────┤
7 │ E5 │ 50 │ 0 │
──┼───────────┼──────────┼─────┤
8 │ A14 │ 100 │ 90 │
──┴───────────┴──────────┴─────┘
Benutzte Formeln:
B2: =INDEX(Datenmaterial!$C$2:blush:C$7;VERGLEICH(A2;Datenmaterial!$D$2:blush:D$7;0))
B3: =INDEX(Datenmaterial!$C$2:blush:C$7;VERGLEICH(A3;Datenmaterial!$D$2:blush:D$7;0))
B4: =INDEX(Datenmaterial!$C$2:blush:C$7;VERGLEICH(A4;Datenmaterial!$D$2:blush:D$7;0))
B5: =INDEX(Datenmaterial!$C$2:blush:C$7;VERGLEICH(A5;Datenmaterial!$D$2:blush:D$7;0))
B6: =INDEX(Datenmaterial!$C$2:blush:C$7;VERGLEICH(A6;Datenmaterial!$D$2:blush:D$7;0))
B7: =INDEX(Datenmaterial!$C$2:blush:C$7;VERGLEICH(A7;Datenmaterial!$D$2:blush:D$7;0))
B8: =INDEX(Datenmaterial!$C$2:blush:C$7;VERGLEICH(A8;Datenmaterial!$D$2:blush:D$7;0))
C2: =SUMMEWENN(Datenmaterial!$F$2:blush:F$7;A2;Datenmaterial!$E$2:blush:E$7)
C3: =SUMMEWENN(Datenmaterial!$F$2:blush:F$7;A3;Datenmaterial!$E$2:blush:E$7)
C4: =SUMMEWENN(Datenmaterial!$F$2:blush:F$7;A4;Datenmaterial!$E$2:blush:E$7)
C5: =SUMMEWENN(Datenmaterial!$F$2:blush:F$7;A5;Datenmaterial!$E$2:blush:E$7)
C6: =SUMMEWENN(Datenmaterial!$F$2:blush:F$7;A6;Datenmaterial!$E$2:blush:E$7)
C7: =SUMMEWENN(Datenmaterial!$F$2:blush:F$7;A7;Datenmaterial!$E$2:blush:E$7)
C8: =SUMMEWENN(Datenmaterial!$F$2:blush:F$7;A8;Datenmaterial!$E$2:blush:E$7)

A1:C8
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

11

Grüezi Ingo

in der Tat ist das Ergebnis noch nicht so wie gewünscht…
Die Daten von Plan und IST sind vertauscht und bei E13 wurde
Plan doppelt gezählt (die Stelle mit gleichen Stellennummer).

Ich habe unter
http://www.file-upload.net/download-2673743/Testtabe…

die Auswertungstabelle mit dem gewünschten Ergebnis
ausgefüllt.
Vielleicht fällt Dir noch was ein, das wäre großartig.

Mir fällt vor allem auf wie ‚unsinnig‘ die Daten für eine saubere Auswertung erfasst sind.
Mit einem solchen Datenstamm kannst Du das Problem nicht über eine Pivot-Tabelle lösen, da redundante Daten enthalten sind und auch nicht als saubere ‚flache‘ Tabelle vorliegen.

Allenfalls also per Monster-Formeln oder VBA…

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
12

Hallo Reinhard,

das sieht super aus, so habe ich mir das vorgestellt. Werde es gleich noch mit einer etwas komplexeren Tabelle testen, bin aber guter Dinge.

Herzlichen Dank dafür!

Viele Grüße
Ingo

13

Hallo Reinhard,

ich habe soeben mit meiner original-Tabelle getestet, dabei hat sich herausgestellt, dass die Formel leider doch nicht funktioniert. Es ist nämlich der Regelfall, dass eine Dotierung auch mehrmal geplant ist.

Ich habe das Problem in der Testtabelle verdeutlich:
http://www.file-upload.net/download-2675601/Testtabe…

Im Datenmaterial habe ich eine Zeile eingefügt, die ebenfalls E13 ist mit geplanten 99%. Diese wird in der Auswertung allerdings nicht erfasst. Ich denke die Funktion Index ist nicht die richtige.

Hast Du noch eine Idee???

Viele Grüße
Ingo

14

Hallo Ingo,

http://www.file-upload.net/download-2675601/Testtabe…

Im Datenmaterial habe ich eine Zeile eingefügt, die ebenfalls
E13 ist mit geplanten 99%. Diese wird in der Auswertung
allerdings nicht erfasst. Ich denke die Funktion Index ist
nicht die richtige.

Hast Du noch eine Idee???

Ideen hab ich reichlich aber ich tapp schon wieder im Dunklen.
Kannst du denn nicht aufgrund der neuen Urdaten die Auswrtung so zeigen wie sie sein soll?
Woher soll ich denn wissen wie/was/wo bei E13= 100,100,99 gezält werden soll.

Und wenn du den datenbereich vergrößerst mußt du das auch in der Formel tun, oder tausche gleich:
Datenmaterial!$F$2:blush:F$7
gegen
Datenmaterial!$F$2:blush:F$1000
o.ä. in den Formeln.

Gruß
Reinhard

15

Hallo Reinhard,

Datenbereich vergrößern ist klar. :smile:

„Woher soll ich denn wissen wie/was/wo bei E13= 100,100,99 gezält
werden soll.“

Das ergibt sich aus der Stellennummer. Pro Stellennummer soll jeweils einmal der Umfang der Stelle gezählt werden.

Viele Grüße, Ingo

16

hallo Ingo,

Datenbereich vergrößern ist klar. :smile:

warum hast du es dann nicht getan?

„Woher soll ich denn wissen wie/was/wo bei E13= 100,100,99
gezält
werden soll.“

Das ergibt sich aus der Stellennummer. Pro Stellennummer soll
jeweils einmal der Umfang der Stelle gezählt werden.

Umso notwendiger daß du aufzeigst wie die Auswertung aussehen soll.
Möglichst mit noch mehr Daten um möglichst viele Konstellationen durchzuspielen.

Gruß
Reinhard

17

Hallo Reinhard,

unter
http://www.file-upload.net/download-2675975/Testtabe…

habe ich noch mehr Daten eingetragen und die Auswertungstabelle ausgefüllt.

Viele Grüße, Ingo

18

http://www.file-upload.net/download-2675975/Testtabe…

habe ich noch mehr Daten eingetragen und die
Auswertungstabelle ausgefüllt.

Hallo Ingo,

sorry, ich klink mich aus. Die 399 für E13 gaben mir den Rest.

Gruß
Reinhard

19

Grüezi Ingo

http://www.file-upload.net/download-2675975/Testtabe…

habe ich noch mehr Daten eingetragen und die
Auswertungstabelle ausgefüllt.

Hmmm, ich meinte, dass Du eingangs geschrieben hast, dass für die Dotierung einer Stelle deren Umfang nur einmal in die Berechnung einfliessen soll?

In dieser Mappe hier kannst Du in der Auswertung ganz einfach die folgenden zwei Formeln verwenden, das ergibt exakt die Ergebnisse deiner Aufstellung:

B2: =SUMMEWENN(Datenmaterial!D:smiley:;Auswertung!$A2;Datenmaterial!C:C)
C2: =SUMMEWENN(Datenmaterial!F:F;Auswertung!$A2;Datenmaterial!E:E)

Die Auflistung der einzelnen Dotierungen erreichst Du am einfachsten indem Du aus dem Blatt ‚Datenmaterial‘ die beiden Dotierungs-Spalten untereinander kopierst und dann mit dem Spezialfilter die Duplikate entfernst.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
20

Grüezi Ingo

Hmmm, ich meinte, dass Du eingangs geschrieben hast, dass für
die Dotierung einer Stelle deren Umfang nur
einmal in die Berechnung einfliessen soll?

Ich habe das hier nochmals mit einer Pivot-Tabelle versucht zu lösen - wenn man auch die Stellen-Nummer mit hinzunimmt (die Du bei deinen Beispielen ‚grosszügig‘ weggelassen hast), dann kommt meines Erachtens etwas raus, das Hand und Fuss hat:

http://users.quick-line.ch/ramel/Demo-Daten/tr_Testt…

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -