Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 08:08:24
Pivot Vormonatsveränderung
26.04.2024 07:54:11
nur den ersten Eintrag in spalte zählen
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1752to1756
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1752to1756
1752to1756
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel gesucht

Formel gesucht
16.04.2020 12:47:38
Karin
Hallo ihr Experten,
ich möchte gerne eine Auswertung erstellen, in der mir die Anzahl der angesprochenen Abteilungen angezeigt wird.
Wenn eine Abteilung an 2 oder mehreren aufeinander folgenden Tagen vorkommt, soll dies jeweils als 1x gezählt werden.
In diesem Beispiel ist die Schlosserei vom 01.10.19 bis 04.10.19 vorhanden und vom 15.10.19 bis 16.10.19 vorhanden und einmal am 18.10.19
Das macht insgesamt 3x
Userbild
Ist das einigermaßen verständlich?
Hat jemand eine Idee wie man das macht?
Vielen Dank im Voraus.
Gruß
Karin

27
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel gesucht
16.04.2020 12:54:39
Hajo_Zi
Halo Karin,
Du bist im falschen Forum. Bildbearbeitung ist ein anderes.
Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.
Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.
Schau mal hier
Eine hochgeladene Arbeitsmappe erhöht die Wahrscheinlichkeit, dass Du eine Lösung für Dein Problem erhältst.
Erstelle folglich bitte eine Demomappe, aus der deine Aufgabenstellung klar erkennbar ist und lade diese hoch.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
http://www.ms-office-forum.de/forum/showthread.php?t=322895
änderrn.
Falls Du den Download des Forums nicht benutzen möchtest beachte bitte: von unsicheren Servern file-upload lade ich keine Datei herunter (lt. Einschätzung meines Virenprogramms)
Das ist nur meine Meinung zu dem Thema.

Anzeige
AW: so wie Du es darstellst...
16.04.2020 13:07:48
neopa
Hallo Karin,
... würde ich folgende Lösung vorschlagen. Formeln weit genug nach unten ziehend kopieren.
Arbeitsblatt mit dem Namen 'Tabelle2'
 ABCDE
1AbtDatum AbtAnzahl
2A101.10.2019 A13
3A102.10.2019 A21
4A103.10.2019 A32
5A104.10.2019 A41
6A205.10.2019   
7A206.10.2019   
8A207.10.2019   
9A208.10.2019   
10A209.10.2019   
11A210.10.2019   
12A311.10.2019   
13A312.10.2019   
14A313.10.2019   
15A314.10.2019   
16A115.10.2019   
17A116.10.2019   
18A417.10.2019   
19A118.10.2019   
20A319.10.2019   
21     

ZelleFormel
D2=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(ZÄHLENWENN(D$1:D1;A$2:A$99)=0);1))&"";"")
E2=SUMMENPRODUKT((A$2:A$22=D2)*(A$3:A$23>D2))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: so wie Du es darstellst...
16.04.2020 13:15:46
Karin
Werner !!!!!!!!!!!!!!!
Das ist ja fantastisch. Es klappt.
Wie kommt man auf so eine Formel?
Hut ab.
Super und vielen Dank für deine Hilfe.
Bis zum nächsten Mal.
Liebe Grüße
Karin
AW: so wie Du es darstellst...
16.04.2020 13:25:56
Karin
Hallo Werner,
ich habe eins nicht bedacht. Wenn das Datum mehrfach vorkommt, dann haut die Formel nicht mehr hin , oder?
Gruß
Karin
AW: swas meinst Du damit genau ...
16.04.2020 13:39:50
neopa
Hallo Karin,
... dazu zeig auch ein konkretes Beispiel auf. Dann sehen wir weiter.
Gruß Werner
.. , - ...
AW: Leere, doppelte, oder nicht aufsteigende Daten
16.04.2020 17:18:00
Sulprobil
in Spalte B zählt der Werner alle einzeln für jede Abteilung mit.
Wenn man dies nicht möchte, würde ich zu einem kleinen VBA Programm raten.
Viele Grüße,
Bernd P
Anzeige
AW: darauf hatte ich teils schon hingewiesen ...
16.04.2020 19:20:34
neopa
Hallo Bernd,
... nur nicht auf evtl. Leerzellen (die ich für unwahrscheinlich halte) und Abhilfemöglichkeiten gegen Duplikate benannt.
Die aufsteigende Sortierung ist einfach mit einem Mausklick zu beheben und die Duplikate sowie die Datumsleerzellen können bei Bedarf auch durch eine kleine Formelerweiterung in der Hilfsspaltenformel abgefangen werden. Doch Karin scheint das offensichtlich (bisher) nicht zu benötigen. Ich habe dies aber auch in dem Lösungsformeln eingebaut, die sich auf die "intelligente" Tabelle beziehen.
Gruß Werner
.. , - ...
AW: Unwahrscheinlich?
16.04.2020 20:28:56
Sulprobil
Hallo Werner,
Für Deine vorliegende Formel muss der unbedarfte Anwender sicherstellen, dass keine Leerdaten, keine doppelten und keine absteigend sortierten vorkommen.
Dass ein untrainierter Anwender Deine Formeln erweitert, halte ich für beliebig unwahrscheinlich.
Deine Annahmen, was unwahrscheinlich ist oder was Karin offensichtlich nicht benötigt, halte ich auch nicht für ungefährlich - ich frage da lieber nach.
Offen gestanden verwende ich Rasierklingen am liebsten mit einem Griff.
Oder ich bin einfach zu gern anderer Ansicht als Du :-)
Aber wenn Karin zufrieden ist, dann ist ja alles gut.
Viele Grüße,
Bernd P
Anzeige
Definition aufeinanderfolgende Tage
16.04.2020 13:29:27
WF
Hi,
wenn Deine ersten 4 so aussehen
Schlosserei 01.10.19
Schlosserei 02.10.19
Schlosserei 04.10.19
Schlosserei 05.10.19
Sind das jetzt aufeinanderfolgende Tage, oder nicht, da ja der 03.10.19 fehlt ?
WF
AW: Definition aufeinanderfolgende Tage
16.04.2020 13:44:48
Karin
Hallo WF,
ja, das sind aufeinanderfolgende Tage. Also würde die Schlosserei 2x vorkommen.
Jetzt kann es aber auch sein das andere Abteilungen am gleichen Tag vorkommen.
Schlosserei 01.10.19
Schlosserei 02.10.19
Schlosserei 04.10.19
Schlosserei 05.10.19
Versand 01.10.19
Versand 03.10.19
Versand 05.10.19
Danach würde der Versand 3 x vorkommen
War das einigermaßen verständlich?
Anzeige
demnach KEINE aufeinanderfolgenden Tage
16.04.2020 13:54:57
WF
jede Tagesunterbrechung ist also ein Neuanfang.
WF
AW: demnach KEINE aufeinanderfolgenden Tage
16.04.2020 14:07:34
Karin
ja, aber man muss auch immer die Abteilung sehen
AW: wie Du es jetzt darlegst ...
16.04.2020 14:29:34
neopa
Hallo Karin,
... und ich ich es interpretiere, dann wohl mit einer (ausblendbaren) Hilfsspalte (hier in Spalte C). Formeln wieder nach unten kopieren. Teste mal.
Arbeitsblatt mit dem Namen 'Tabelle3'
 ABCDEF
1AbtDatumHS AbtAnzahl
2A101.10.20191 A14
3A102.10.20191 A22
4A104.10.20192 A33
5A205.10.20191 A41
6A206.10.20191   
7A208.10.20192   
8A209.10.20192   
9A210.10.20192   
10A311.10.20191   
11A313.10.20192   
12A314.10.20192   
13A315.10.20192   
14A115.10.20193   
15A117.10.20194   
16A417.10.20191   
17A118.10.20194   
18A319.10.20193   
19      

ZelleFormel
C2=(ZÄHLENWENNS(INDEX(A:A;VERGLEICH(A2;A:A;0)):A2;A2;INDEX(B:B;VERGLEICH(A2;A:A;0)):B2;B2-1)=0)+WENNFEHLER(VERWEIS(9;1/(A$1:A1=A2);C$1:C1);0)
E2=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$97)/(ZÄHLENWENN(E$1:E1;A$2:A$97)=0);1))&"";"")
F2=VERWEIS(9;1/(A$1:A98=E2);C:C)
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: nachgetragen ...
16.04.2020 14:38:43
neopa
Hallo Karin,
... als Hilfsspaltenformel in C2 sollte auch folgende ausreichend sein:
=(ZÄHLENWENNS(A$1:A2;A2;B$1:B2;B2-1)=0)+WENNFEHLER(VERWEIS(9;1/(A$1:A1=A2);C$1:C1);0)
Unstimmig würde es sicherlich, wenn die Daten nicht aufwärts sortiert sind und Duplikate vorliegen können. Derartige am einfachsten vorher mit der der Funktion "Duplikate entfernen" eliminieren.
Gruß Werner
.. , - ...
AW: wie Du es jetzt darlegst ...
16.04.2020 14:46:20
Karin
Hallo Werner,
bei solchen Formeln blicke ich schon lang nicht mehr durch.
Aber das Ergebnis stimmt jetzt.
Hast du Excel studiert :-)
Vielen Dank nochmal.
Liebe Grüße
Karin
Anzeige
AW: bitteschön, aber ...
16.04.2020 15:07:27
neopa
Hallo Karin,
... wenn Du das Ergebnis meines Lösungsvorschlages für richtig befindest, hättest Du den thread nicht mehr als offen kennzeichnen müssen.
Zu Deiner Frage: ja, allerdings autodidaktisch hobbymäßig.
Meinen Nachtrag bzgl. der vereinfachten Formel für C2 hast Du zwischenzeitlich gesehen?
Übrigens, erst richtig interessant wird die Formellösung, wenn sie sich auf Deine "intelligente" Datentabelle bezieht. Das hätte dann den Vorteil, dass die Formel nicht mehr angepasst werden muss, wenn weitere Daten hinzukommen. Und die Formeln lassen sich sogar so definieren, dass die Datentabelle auch beliebig verschoben werden kann.
Gruß Werner
.. , - ...
Anzeige
Lösungen aus dem Internet die man nicht versteht
19.04.2020 21:35:58
Daniel
im beruflichen Umfeld anzuwenden, halte ich für gefährlich.
Schließlich bist du Karin für das richtige Ergebnis verantwortlich und nicht die Person, von der du die Lösung hast.
Deshalb hier nochmal ein anderer Ansatz, welcher zwar zusätzlichen manuellen Aufwand bedeutet, aber mit wesentlich einfacheren Formeln auskommt, die du vielleicht eher verstehst (die Lösung von Werner kannst du ja erstmal zur Seite legen, gelegentlich mal anschauen und dann zum Einsatz bringen, wenn du sie verstanden hast und selber schreiben könntest.)
Um die Aufgabe zu lösen, musst du zählen, wie oft bei einer Abteilung eine Lücke größer ein Tag vorkommt.
Dazu mache folgendes (ich beziehe mich hier auf die Darstellung von Werner)
1. Sortiere die Liste nach Abteilung und Datum, dh alle Datumswerte einer Abteilung müssen sortiert untereinander stehen.
2. füge in die Zelle C2 die Formel ein und ziehe sie nach unten: 
=Wenn(A2=A1;Wenn(B2-B1>1;1;"");1)

Die Formel trägt jedesmal 1 ein, wenn entweder eine neue Abteilung kommt, oder wenn ein Datumssprung größer ein Tag vorliegt.
3. die Auswertung in Zelle F2 machst du dann mit der Formel: 
=SummeWenn(A:A;E2;C:C)

Dh hier zählst du einfach, wie oft die 1 für jede Abteilung vorkommt.
Gruß Daniel
Anzeige
Wenn sortiert auch ohne Hilfsspalte
19.04.2020 21:45:38
Daniel
Hi
man kann, wenn nach Abteilung und Datum sortiert ist, auch auf die Hilfsspalte verzichten und direktauswerten.
allerdigs muss man dann die Zellbereiche passend angeben und auf den Versatz achten dh die Vorteile einer intelligenten Tabelle kann man nicht nutzen.
die Formel für F2 in Werners Beispiel wäre: 
=SUMMENPRODUKT(($A$3:$A$18=E2)*($A$2:$A$17=E2)*(($B$3:$B$18-$B$2:$B$17)>1))+ISTZAHL(VERGLEICH(E2; $A$3:$A$18;0))
gruß Daniel
AW: beide Varianten berücksichtigen noch nicht ...
20.04.2020 10:14:29
neopa
Hallo Daniel,
... den Einwand den Bernd gemacht hat, dass keine Datenangabe für eine Abt. möglich sein soll.
In Deinen ersten Vorschlag müsste dazu lediglich in C2 die Formel wie folgt ergänzt werden:
=WENN((A2=A1);WENN(B2-B1&gt1;1;"");1*(B2&gt0))
Da Karin eine intelligente Datentabelle hat, scheidet Dein 2. Vorschlag mE aus.
Gruß Werner
.. , - ...
AW: beide Varianten berücksichtigen noch nicht ...
20.04.2020 10:27:39
Daniel
Hi
So ganz verstehe ich deinen Einwand nicht.
Und da es Karins Datei ist, interessieren mich Bernds Einwände und Anforderungen nicht, sondern die Vorgaben von Karin.
Das der zweite Vorschlag mit intTab Probleme haben könnte, habe ich ja geschrieben, allerdings müsste man mal testen, wie sich das genau auswirkt. Im schlimmsten Fall werden die Zellbereich nicht fortgeschrieben, wenn man die Tabelle erweitert.
Aber manchen sind ja Hilfspalten ein graus, daher eben die zweite Alternative für Menschen mit ähnlichem Problem, die lieber normale Tabellen verwenden und keine Hilfsspalten mögen.
Gruß Daniel
AW: Falls Du leere, doppelte oder
16.04.2020 20:12:22
Sulprobil
... nicht aufsteigende Daten hast:
https://www.herber.de/bbs/user/136792.xlsm
(Diese Datei wird ohne jede Gewährleistung zur Verfügung gestellt, aber ich verwende einen aktuellen Virenscanner).
Ich habe es nicht exzessiv getestet, Fehler bitte melden.
Es ist eine sehr einfache VBA Lösung mit einer "Schlangen-Sortierung". Bei mehreren Hundert Datensätzen kann es länger dauern.
Aber ich mag es einfach nicht, wenn leere Zellen oder doppelte / unsortierte Daten die gesamte Rechnung kaputt machen. Natürlich kann man alles manuell lösen, aber der nächste Anwender denkt vielleicht nicht dran.
Viel Spaß,
Bernd P
AW: wie schon geschrieben ...
17.04.2020 17:32:55
neopa
Hallo Bernd,
... es ist kein großes Problem auch Doppelte und leere Zellen formelmäßig abzufangen, wenn man das möchte. Das Vorliegen unsortierter Daten könnte man zwar auch formelmäßig abfangen, steht jedoch diesbzgl. in keinem vernünftigen Aufwands- und Nutzen-Verhältnis, zumal ein Mausklick ausreichend ist, die Daten vor der Auswertung zu sortieren. Alle Eventualitäten abzufangen gelingt selbst professioneller Software nicht und kann somit auch nicht von einer Forumshilfe nicht erwartet werden auch wenn es hier meist nur um im Verhältnis zu einer prof. Software nur minimale Teilprobleme sind.
Ich habe auch nie den Anspruch erhoben hier stets fehlerfreie Lösungen anzubieten bzw. überhaupt dazu in der Lage zu sein und erwarte das auch nicht von anderen, es sei denn sie behaupten solches.
Hier jetzt der Vollständigkeit halber noch meine Formelergänzungen, die in Deiner Datei eingesetzt (bei
entsprechend sortierten Daten) auch echte Duplikate und Leerzellen richtig berücksichtigen sollte.
In C2:
=(ZÄHLENWENNS(A$1:A2;A2;B$1:B2;B2-1)=0)*(ZÄHLENWENNS(A$1:A2;A2;B$1:B2;B2)=1) +WENNFEHLER(VERWEIS(9;1/(A$1:A1=A2);C$1:C1);0)*(B2&gt0)
und in F2:
=WENN(E2="";"";AGGREGAT(14;6;C$2:C99/(A$2:A99=E2);1))
Diese Auswertung weist dann auch mE korrekt A7 mit einer Anzahl 0 aus.
Gruß Werner
.. , - ...
AW: Bei Dir fehlt ein Sortierungs-Check
18.04.2020 12:49:38
Sulprobil
Hallo Werner,
niemand erwartet, dass Deine Formeln alle Eventualitäten abfangen, denke ich.
Da jedoch Deine Formeln in diesem Fall bei absteigend- oder unsortierten Daten falsche Ergebnisse liefern, wäre zumindest eine Prüfung auf Sortierung für alle Abteilungen angebracht. Dass Daten einer Abteilung nicht aufsteigend sortiert sind, könnte m. E. im Allgemeinen zu schnell vorkommen.
Ich stimme Dir auch nicht zu, dass für eine Sortierung bei Deinem Ansatz ein Mausklick ausreicht. Falls der Eingabebereich mit Tabellenfunktionen erstellt wurde, müsste man wohl vorsichtigerweise zunächst die Werte kopieren und dann Deine Formeln anwenden.
Viele Grüße,
Bernd P
AW: ein solcher ist kein großes Problem ...
19.04.2020 12:54:07
neopa
Hallo Bernd,
... allerdings würde ich in die Auswertung sowieso, wenn ich diese für mich vornehmen müsste, auch gleich noch ein paar weitere Prüfungen einbauen. So z.B. das diese auch teilweise für Daten mit korrekter Reihenfolge vorgenommen wird und die inkorrekten Werte visuell angezeigt werden, so dass man diese entsprechend schnell findet und korrigieren oder löschen kann. Auch würde ich eine analoge Auswertung für gefilterte Daten gleich mit ermöglichen und die Datenerfassung grundsätzlich in einer formatierten Tabelle vornehmen.
Letzteres ist von Karin Karin ja auch so angelegt gewesen. Ich sehe jedoch dafür keine Notwendigkeit eine Kopie dieser anzulegen, wenn man diese nachträglich sortiert. Natürlich kann es bei anderen Datenauswertungen notwendig sein, die ursprüngliche Datenreihenfolge wieder herstellen zu können. In so einem Fall arbeite ich mit fixen ID-Nummern und j´kann danach wieder sortieren
Da es mich nun interessiert hat, hab ich mal eine entsprechende Auswertung vorgenommen, die vorgenanntes alles berücksichtigt und dabei auch ermöglicht die Auswertungstabelle beliebig nachträglich innerhalb der Arbeitsmappe zu verschieben wie auch vor und oberhalb der Datentabelle Spalten/Zeilen einzufügen und das alles ohne das eine Änderung meiner Formeldefinition vorgenommen werden muss.
Dich dürfte solches wohl kaum interessieren, da Deine Lösungen schwerpunktmäßig VBA-basierend aufgebaut sind und werden, sollte es jedoch Karin noch interessieren, kann ich meine entsprechende Beispieldatei hier noch bereitstellen.
Gruß Werner
.. , - ...
AW: Sinnvolle Lösungen finde ich interessant
19.04.2020 17:39:02
Sulprobil
Hallo Werner,
Deine hier vorgestellte Formel finde ich bisher lückenhaft und nicht sinnvoll.
Und immer wieder scheibchenweise Deine Nachbesserungen nachzuvollziehen, ist auch wenig ergiebig.
Viele Grüße,
Bernd P
AW: das interpretiere ich nun so ...
19.04.2020 20:30:20
neopa
Hallo Bernd,
... das Du meinen spez. Lösungsansatz sehen möchtest. Dann stelle ich diesen nun hier bereit:
https://www.herber.de/bbs/user/136875.xlsx obwohl mir durchaus bewusst ist, dass es auch andere/bessere Lösungen dafür geben könnte. Aber ich arbeite bewusst ohne VBA, nicht weil ich VBA ablehne - ganz im Gegenteil - sondern weil mir derartiges persönlich mehr zusagt.
Unabhängig davon bin ich, nach wie vor der Meinung, dass diese Karin diese Art Lösung nicht wirklich benötigt, war sie ja bereits mit der weniger aufwendigen Formellösung scheinbar zufrieden. Ich würde ihr auch jetzt nicht zu der hier eingestellten raten, weil ein evtl. erforderliche Anpassung für sie schwieriger zu handhaben ist.
Hierzu nur soviel, die Auswertung ist momentan begrenzt auf bis zu Zeile 97 Datensätze. Die Auswertungstabelle kann beliebige innerhalb der Mappe verschoben werden. Die Berechnunsgshilfsspalte ist ausgeblendet.
Wie bereits geschrieben werden lediglich die Daten ausgewertet, die der Vorgabe entsprechen. Somit ergibt sich nach einer Sortierung der Daten auch andere Auswertungsergebnisse als zuvor. Die Ergebniswerte, mit nicht ausgewertenten Daten (sind durch bedingte Formatierung gekennzeichnet), werden ebenso durch bed. Formatierung gekennzeichnet.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige