Excel - Zeilen basierend auf der Startzeichenfolge filtern, Zahlen aus gefilterten Zeilen lesen und Rechenoperation ausführen, um eine Spaltenzusammenfassung zu generieren

Question

Ich habe einen Bericht zur Projekt- und Ressourcenzuweisung. Ich brauche Hilfe beim Erstellen der Formel zur Anzeige der Zusammenfassung im unteren Abschnitt meines Berichts.

Hier sind die Spezifikationen meines Berichts:

• Column AVon Row 2bis Row 15stellt die für jedes Projekt zugewiesene Ressource (Personen) dar

• Column B, Column C.... stellt den Prozentsatz der Ressourcen dar, die jedem Projekt pro Monat zugewiesen werden

• Jede Ressource column Awird in 2 Zellen zusammengeführt, da jede Ressource entweder in 1 oder 2 Projekten pro Monat arbeiten kann

• Bei einer Ressource, die in einem Monat an einem einzigen Projekt arbeitet, werden beide Zellen zusammengeführt. Bei einer Ressource, die an zwei Projekten arbeitet, werden zwei Zeilen angezeigt, die das Projekt darstellen.

• Für jedes Projekt gibt es eine Zahl, die den Prozentsatz der dem Projekt zugewiesenen Ressource darstellt.

Hier ist der Screenshot meines Berichts:

Im unteren Bericht habe ich einen Zusammenfassungsbereich, der die Anzahl der pro Projekt zugewiesenen Ressourcen darstellt. Derzeit füge ich diese Zahlen manuell hinzu. Ich brauche Hilfe beim Erstellen der Formel für diese Zusammenfassung. Folgendes versuche ich zu erreichen:

1. Zeichenfolge aus Zellen (A20 - A24) lesen
2. Führen Sie eine Suche in der Monatsspalte (B2 - B15) durch, um die Zellen zu erhalten, die mit der Projektzeichenfolge beginnen
3. Filtern Sie die Zahlen aus den obigen Zellen (aus Schritt 2)
4. Addieren Sie die Zahlen und dividieren Sie sie durch , 100um die Ressourcenanzahl zu erhalten

Bisher konnte ich diese Formel generieren, die die Zahl aus der ihr übergebenen Zeile liest:

=TEXTJOIN("",TRUE,IFERROR((MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1),""))

das gibt mir eine Nummer 50aus der Zelle, die den Text B2enthält "Project A 50%".

Answer 1

Wenn Sie O365 haben, können Sie die FILTERFunktion verwenden:

• Filtern Sie die Liste nach dem Inhalt von A20:Ann

• Gibt nur den letzten durch Leerzeichen getrennten Wert in jeder Zeichenfolge zurück (den Prozentsatz).

• Summieren Sie die Ergebnisse

• Verwenden Sie es IFERROR, wenn das Projekt nicht existiert

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")
  

oder""(je nachdem, ob Sie Null oder einen nicht vorhandenen Eintrag zurückgeben möchten )

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")

Bearbeiten:in den Kommentaren erwähnt der OP, dass Projekte ähnlich benannt werden können, aber mit einem vorangestellten Zeichen versehen sind, und diese sollten separat behandelt werden. Dies erfordert ein anderes Filterkriterium:

=IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,LEFT(B$2:B$15,LEN($A20))=$A20)," ",REPT(" ",99)),99))))),"")

Answer 2

Ihre Datendarstellung in den Zeilen 2-15 ist ein Durcheinander, weil Sie zwei Informationen in einer Zelle kombinieren. Dadurch werden grundsätzlich alle numerischen Zellfunktionen unbrauchbar. Teilen Sie die Projektkennung und den Ressourcenbetrag in zwei Spalten auf.

Bei einer gegebenen Projektkennung in Spalte B und einer Ressource in Spalte C würden Sie Zelle B20 einfach auf "=sumif(b$2:b$15;"A";c$2:c$15)" setzen. Sie würden diese Formel für Projekt B in "B" ändern.

Für eine schnellere Eingabe ohne Änderung der Formel erstellen Sie eine ausgeblendete Spalte mit ABCD … und lassen die feste Zeichenfolge aus der obigen Formel auf die ausgeblendeten Werte ABCD … verweisen.

Answer 3

Diese Antwort wird Ihnen wahrscheinlich nicht gefallen, aber bitteBleiben Sie aufgeschlossen

Dies wird vermutlich nicht das letzte Mal sein, dass Sie diese Tabelle analysieren müssen. Wenn Sie jetzt umstrukturieren, wird Ihnen das Leben später leichter fallen.

1. Zusammengeführte Zellen scheinen im Moment eine gute Idee zu sein, aber sie werden Ihnen später nur Kummer bereiten
2. Das Kombinieren von Variablen in einer einzigen Zelle „Projekt A 50 %“ erschwert die Analyse immer, da Sie diese Variablen nun trennen müssen, um sie zu verstehen
3. Das Eingeben von Daten in Kreuztabellen ist möglicherweise etwas einfacher, hat aber seinen Preis. Was wäre, wenn Sie jetzt einen Bericht erstellen müssen, in dem Sie nach den vierteljährlichen Ressourcen gefragt werden, die Projekt A im Jahresvergleich zugewiesen wurden? Und was passiert, wenn Sie zum nächsten Jahr übergehen? Eine neue Tabelle erstellen? Und wie vergleichen Sie sie dann?

Ich möchte hier nicht begriffsstutzig wirken, aber nach 25 Jahren Arbeit mit Daten in Excel und anderen Systemen möchte ich die Leute dazu ermutigen, darüber nachzudenken, was sie später vielleicht tun müssen.

So etwas wird Ihnen am besten weiterhelfen:

Beachten Sie, dass dies mit Strg+T oder „Als Tabelle formatieren“ auf der Registerkarte „Start“ des Menübands als Tabelle formatiert wird.

Diese Struktur bietet eine Reihe von Vorteilen:

1. Die Dateneingabe ist jetzt ganz einfach: Tippen Sie einfach in eine neue Zeile.
2. Mithilfe einer Pivot-Tabelle können Analysen für jede beliebige Dimension (Person, Monat, Projekt) durchgeführt werden – keine komplexen Formeln für eigentlich einfache Fragen
3. Das Erstellen von Diagrammen ist jetzt kinderleicht
4. Sie können jeder Zeile neue Informationen als Spalten hinzufügen. Vielleicht möchten Sie den Vorgesetzten oder den Teamnamen der Person als XLOOKUP aus einer anderen Tabelle zur Zeile hinzufügen, oder Sie möchten Notizen zur Leistung oder zum Urlaub dieser Person in diesem Monat hinzufügen.
5. Wenn Sie dies jemals in eine andere Tabelle kopieren oder als CSV an jemanden senden oder es zur Analyse in eine Tabelle in einer Datenbank oder einem Datenrahmen laden müssen, müssen Sie keine Zeit damit verbringen, die Zusammenführung aufzuheben und die leeren Zellen zu füllen, bevor Sie dies tun können.

BEARBEITEN:

Um Ihre Frage mit dieser überarbeiteten Datenstruktur zu beantworten, könnten Sie einfach eine Pivot-Tabelle erstellen: