Supportnet / Forum / Tabellenkalkulation

Hallo, ich habe in Spalte A Datum zu Aufträge die in Spalte B sind. Also z.b. Eine auftragsnummer kommt des öfteren in Spalte B vor. Dazu brauche ich das anfangsdatum und enddatum. Geht das irgendwie dass man das kleinste Datum eines kriterium und das größte rauskopieren kann in eine neue Spalte. Will im grunde am ende eine neue Tabelle haben indem die Auftragsnummer nur einmal vorkommt. dazu das Anfangs und Enddatum und die summe der Werte aus spalte D. Danke

Hallo Fragenkatalog,

A1:A1000 und B1:B1000 sind Deine Spalten wie oben, das kannst Du auch auf die richtigen Größen anpassen, aber beide Bereiche müssen gleich groß sein. In C1 steht die Auftragsnummer, für die das größte Datum gesucht wird.

Für das größte Datum geht die Matrixformel (mit Strg+Umsch+Eingabe eingeben):

=MAX(A1:A1000*(B1:B1000=C1)),

aber für das kleinste schaffe ich es im Moment nur mit einer Hilfsspalte. Da denke ich noch mal drüber nach. Bis dann!

CaroS

Hallo CaroS.
Das geht irgendwie nicht.

Also meie Tabelle ist zur zeit so aufgebaut,
dass ich zwei Tabellenblätter habe.
In der 1. Stehen meine ganzen aufträge mit der Nummer in D und in A stehen das Datum.

und in der 2. Spalte. habe ich die Auftragsnummern ohne Dublikate in Spalte C. nun möchte ich in Spalte A in der 2. Tabelle das Anfangsdatum zu der jeweiligen nummer die in Spalte C steht.
Habe es mit DBMAX probiert. das geht auch, aber wenn ich es runter kopieren will dann werden die Bedingung nach der er suchen soll immer mehr, da er die mitkopiert.

Grüße

Hallo Fragenkatalog,

mir fällt einfach nichts ein, wie ich um die Hilfsspalte drumrum komme, deshalb hier erstmal das, was ich habe. Vielleicht fällt Dir oder jemand anderem dann noch was ein.
Also, in H wie Hilfsspalte die einfache Formel =50000-A1 von H1 runterkopieren, dann für das kleinste Datum wieder eine Matrixformel mit A1:A1000, B1:B1000, C1 wie oben:
{=50000 - MAX(H$1:H$1000*(B$1:B$1000=C1))}

Wenn sie runterkopiert werden soll, ist es auch bei der ersten Formel für das größte Datum besser, sie mit $-Bezügen zu schreiben:

{=MAX(A$1:A$1000*(B$1:B$1000=C1))}

Gruß,
CaroS

Hallo Fragenkatalog,

bist Du Dir sicher, dass Deine Auftragsnummern in Tabelle1 in Spalte D stehen? In Deiner Frage war es noch die Spalte B, wenn ich sie richtig gelesen habe.

Meinst Du hier das was Du schreibst (2. Spalte) oder meinst Du Tabelle2? Wahrscheinlich meinst Du Tabelle2, denn es ist ja sinnvoll, die Auftragsnummern ohne Dublikate in eine Spalte (C) zu schreiben, die beiden Datumswerte brauchen ja nur jeweils einmal ermittelt werden.

Ansonsten ist das weiter kein Problem, wenn Du die Formeln ein bisschen anpasst:

für das kleinste Datum:
{=50000 - MAX(Tabelle1!H$1:H$1000*(Tabelle1!B$1:B$1000=C1))}

für das größte Datum:
{=MAX(Tabelle1!A$1:A$1000*(Tabelle1!B$1:B$1000=C1))}

Aber wie gesagt, Du müsstest für das kleinste Datum eine Hilfsspalte anlegen (am besten in Tabelle1; in diesem Beispiel wieder Spalte H) und bei der Eingabe beachten: Matrixformel! Ohne {}-Klammern schreiben und mit Strg+Umsch+Eingabe eingeben!

Gruß,
CaroS

Natürlich Duplikate - sorry, kopiert ohne hinzugucken.

Hallo Caros,

Danke erstmal für die zahlreichen Vorschläge.
Habe jetzt noch einbisschen in der Datei etwas verschoben und experimentiert.

Ich habe eine Datei die wie folgt aufgebaut ist.

Tabellenblatt1:

Spalte B Datum
Spalte D Auftragnummer
Spalte E Kosten
und G bis J Bemerkungen.

Nun möchte ich in einem neuen Tabellenblatt eine zusätzliche neue Sturktur aufbauen.

in einer Spalte soll die Auftragsnummer ohne Duplikate aufgezeigt werden. Dann brauche ich aus spalte Tabelle1 Spalte B das kleinste Datum zu der jeweiligen Auftragsnummer und in Spalte C das größte Datum.
Dann sollte auch in der neuen Tabelle in Spalte D die Summe der Kosten für die jeweiligen Auftragswerte ausgezeichnet werden und über ein Sverweis die Bemerkung.

Vielleicht hast du jetzt einen kleinen Überbleick.
Wie kann man das lösen oder kann man das mit einem Makro besser machen?

Danke

Hallo Fragenkatalog,

ich hoffe mal, dass Du es in den letzten 2 Stunden, als hier im SN nichts ging, einfach mal probiert hast, dann hast Du Deine Lösung jetzt bereits. Es steht nämlich schon alles da, bis auf die Anpassung der zuletzt geänderten Spalten in Tabelle1.

(Es wäre natürlich schön gewesen, wenn Du Dich mal festgelegt hättest, in welcher Spalte und auf welchem Tabellenblatt Du die Auftragsnummer ohne Duplikate hast, das spart (mir) Rückfragen und (Dir) Probleme, aber ich nehme mal an, dass es sich um die Spalte A handelt.)

Markiere in Tabelle1 die Spalte K von K1 beginnend so weit runter, wie Datumswerte in Spalte B stehen und schreibe (in K1) die Formel (1):
=50000 - B1
(Statt vorher zu markieren kannst Du auch hinterher die Formel runterziehen oder runterkopieren, Hauptsache, zu jedem Datum in Spalte B gibt es eine "Hilfszahl" in Spalte K. Die ganze Hilfsspalte kann dann auch ausgeblendet werden, und es würde auch mit jeder anderen Spalte funktionieren, Du müsstest in den beiden Formeln (1) und (2) nur das K gegen die andere Spaltenbezeichnung austauschen.)

Schreibe dann in die nächste Tabelle (wahrscheinlich Tabelle2, Name spielt erstmal keine Rolle) in B1die Matrixformel (2) für das kleinste Datum:
{=50000 - MAX(Tabelle1!K$1:K$1000*(Tabelle1!B$1:B$1000=A1))}

und in C1 die Matrixformel (3) für das größte Datum:
{=MAX(Tabelle1!A$1:A$1000*(Tabelle1!B$1:B$1000=A1))}

Matrixformeln ohne {}-Klammern schreiben und mit Strg+Umsch+Eingabe eingeben! Alles klar?

CaroS

die Summe der Kosten für die jeweiligen Auftragswerte ... und über einen Sverweis die Bemerkung

Welche Bemerkung(en)? Und welche Summe für welche Auftragswerte? Die Summe über die gesamte Spalte E in Tabelle1 oder nur irgendeine Summe, die sich aus den Aufträgen mit dem kleinsten und/oder größten Datum ergibt? Das müsstest Du mal genauer erklären. Und auch die Bemerkung(en).

Die Gesamtsumme aus Tabelle1 Spalte E ist:
=SUMME(Tabelle1!E:E),
sofern da nichts drin steht, was nicht mitaddiert werden darf,

ein Verweis auf die Bemerkung in Tabelle1 Spalte G für die erste Auftragsnummer in Tabelle2 Zelle A1 wäre:
=SVERWEIS(A1; Tabelle1!A:J; 7)

ein Verweis auf die Bemerkung in Tabelle1 Spalte H für die erste Auftragsnummer in Tabelle2 Zelle A1 wäre:
=SVERWEIS(A1; Tabelle1!A:J; 8)

Prinzip klar? I -> 9, J -> 10. Falls nötig, musst Du in den letzten Formeln noch E:E und A:J durch kontrete Zellbereiche z. B. E1:E275 und A1:J275 ersetzen.

Zur letzten Frage: Ein Makro halte ich nicht für nötig, da sich diese Dinge sehr gut mit Formeln realisieren lassen. Im Bereich vierstelliger Zeilenzahlen könnten Formeln vielleicht etwas langsamer bzw. ungünstiger sein als ein Makro, da sie ständig alle "mitrechnen" müssen, obwohl es meistens nur minimale Veränderungen gibt. Das ist aber eine sehr allgemeine Tatsache und hat nichts mit Deinem konkreten Problem zu tun.

Gruß,
CaroS

Hallo,
ich werde diesmal versuchen das alles bisschen hübscher zu schreiben.
ich gebe mir Mühe.

Die erste Tabelle schaut so aus. und darunter stehen z.b. folgende Angaben.

Spalte B Datum
02.03.06
02.04.06
05.05.06
04.06.07

Spalte D Auftragnummer
101
101
101
101

Spalte E Kosten
140 Euro
220 Euro
260 Euro
180 Euro

und G Bemerkungen
Hausbau
Hausbau
Hausbau
Hausbau

das sind angaben zu einer Auftragsnummer.
Es gibt natürlich auch noch mehrere Auftragsnummern.

nun ist es in erste Linie für mich wichtig von der Spalte D in ein neues Tabellenblatt eine Kopie ohne Duplikate zu machen. Das ist meine erste Frage.
Für diese Auftragsnummer will ich dann wie schon geklärt das Anfangs und Enddatum in je einer Spalte.
Dann sollen die Kosten für den Auftrag Summiert werden.
Und die Bemerkung in Spalte G soll auch noch dabei stehen.
Am Ende stelle ich mir das ungefähr so vor.

Tabelle2
Spalte A Auftragsnummer
101
Spalte B Anfangsdatum
02.03.06
Spalte C Enddatum
04.06.07
Spalte D Summ
800 Euro
Spalte E Bemerkung
Hausbau


Danke

Hallo Fragenkatalog,

danke auch erstmal für die gute Beschreibung, da sieht man doch gleich viel besser durch. Die Beispieldaten in Tabelle1 und speziell das fett Hervorgehobene in Tabelle2 helfen richtig weiter:

Spalte A Auftragsnummer
101
Spalte B Anfangsdatum
02.03.06
Spalte C Enddatum
04.06.07
Spalte D Summ
800 Euro
Spalte E Bemerkung
Hausbau

Daraus kann nämlich jeder "Bearbeiter" erkennen, dass man für Spalte D eine bedingte Summe (mit SUMMEWENN oder SUMMENPRODUKT) bilden muss. Das würde nie jemand von Dir verlangen, dass Du das mit angibst, weil Du es vielleicht nicht weißt oder nicht richtig einschätzen kannst, aber ein "Bearbeiter" braucht eben gewisse Angaben (Beschreibungen oder Beispiele), um sich ein richtiges Bild zu machen.

In den ersten 3 Formeln ändern sich gegenüber AW7 höchstens ein paar Spaltenangaben, für die Hilfsspalte Tabelle1!K bleibt:
=50000 - B1

in Tabelle2!B kommt:
{=50000 - MAX(Tabelle1!K$1:K$1000*(Tabelle1!D$1:D$1000=A1))}

in Tabelle2!C:
{=MAX(Tabelle1!B$1:B$1000*(Tabelle1!D$1:D$1000=A1))}

in Tabelle2!D:
=SUMMENPRODUKT((Tabelle1!E1:E4) * ((Tabelle1!D1:D4) = A1))

und in Tabelle2!E:
=SVERWEIS(A1; Tabelle1!D:J; 4)

Und dann kann´s mit den Auftragsnummern in Tabelle2!A losgehen!

Gruß,
CaroS

Ach so, ich hoffe mal, dass in

nur reine Zahlen stehen. Um damit rechnen zu können, dürfen die Werte der Zellen nur Zahlen sein. Die Anzeige der Zellen ist was anderes, die ergibt sich aus Wert und Formatierung der Zelle. Das stellst Du über Format -- Zellen... -- Währung (€) oder Format -- Zellen... -- Benutzerdefiniert (Euro) ein, je nachdem, ob Du das Euro-Zeichen oder Buchstaben haben willst.

Gruß,
CaroS

Danke,

ja die werte haben keine Eurobezeichnung.

Danke dir.

Wollte nur kurz noch einmal fragen, gibt es eine möglichkeit einfach die Duplikate aus eiiner Spalte rauszubekommen und das es dann gleich in die spalte A in das andere Tabellenblatt kopiert wird??

Danke

Hallo Fragenkatalog,

ich habe mir den gesamten Dialog durch gelesen und bin zu folgenden Alternativen gelangt.

A) die Tabelle 1 nach Auftragsnummer und Datum sortieren., die Autofilterfunktion benutzen und unter der verkürzten Ansicht per Teilsummenfuntion (=Teilsumme(9, D2:D1000) den entsprechenden Auftrag überprüfen.

B) Auf einem neuen Tabellenblatt ein Pivottabelle anlegen und die Auftragsnummer als Index benutzen. In der Pivottabelle können die Werte zur jeweiligen Auftragsnummer als Summe, Produkt, Anzahl oder Bezeichnung angezeigt werden.

C) auf dem neuen Tabellenblatt mit folgenden Funktionen arbeiten:
Verweis bzw. Sverweis, Matrix, Bereich, Zählewenn und Summewenn.
Das würde natürlich eine Menge Handarbeit bedeuten.

Ich neige zur Pviottabelle. Diese Form der Auswertung habe ich bislang sehr stiefmütterlich behandelt. Lediglich zum Kennenlernen der Funktion habe ich das Anlegen und bearbeiten der Pivottabelle ausprobiert.

Grundsätzlich würde ich die Tabelle1 immer nach Auftragsnummer, Datum und Bezeichnung sortieren, um dadurch einen ersten frühen Überblick zu den einzelnen Aufträgen zu erhalten.

Es sind zwar nur ein paar Anregungen, hoffe trotzdem damit konstruktiv beigetragen zu haben.

Gruß
Joachim

Hallo Joachim,

Anregungen sind immer willkommen. Mit den Pivottabellen tun sich scheinbar viele schwer, vor allem mit der Überwindung, den ersten Schritt zu tun. Dann ist es sicherlich ein sehr gutes und leistungsfähiges Hilfsmittel, von dem man umso mehr hat, je besser man es beherrscht.

Es gibt aber auch die Situationen, und die werden nie aussterben, wo jemand in Excel etwas für andere anlegen will oder muss, bei denen es von vornherein nicht in Frage kommt, über eine interaktive Lösung nachzudenken. Die wären schon total überfordert, wenn sie nur zwei benachbarte Spalten (markieren und) nach einem Kriterium sortieren sollen. Für die muss man so viel wie möglich und das so gut und so "unkaputtbar" wie möglich vorbereiten.

Um die Formellösung zu vervollständigen, geht´s jetzt um die offene Frage aus AW12.

Hallo Fragenkatalog,

na klar geht da was, aber ob Du das auch als einfach (und schön) empfindest, ist eine andere Sache. Die einfachste Formellösung, die ich zu bieten habe, hat eine ernsthafte Einschränkung, einen Nebeneffekt und braucht auch wieder eine Hilfsspalte.
(Wie hieß es früher in der Werbung: Drei Dinge braucht der Mann!)

Sie funktioniert nur, solange die Auftragsnummern echte Zahlen sind; auch die Formatierung von Zahlen als Text stört manchmal schon. Und es dürfen bei
nicht genau angegebener Größe der Verweismatrix L$1:L$1000 (oder L:L) - in dieser Spalte nicht "zufällig" noch anderen Zahlen (Überschrift, Summe o. ä.) stehen!

Man muss außerdem den Nebeneffekt in Kauf nehmen, dass die Auftragsnummern in Tabelle2 aufsteigend sortiert werden. (In Tabelle1 brauchen sie nicht sortiert werden.)

Man muss eine weitere Hilfsspalte anlegen.

Am besten in Tabelle1 Spalte L, Formel für L1:
=WENN(ZÄHLENWENN(D$1:D1; D1) = 1; D1; "")

in Tabelle2!A:
=WENN(ZEILE(A1) > ANZAHL(Tabelle1!L$1:L$1000); ""; KKLEINSTE(Tabelle1!L$1:L$1000; ZEILE(A1)))

Außerdem sind noch ein paar kleinere Korrekturen nötig bzw. empfehlenswert:

in Tabelle2!D:
=SUMMENPRODUKT((Tabelle1!E$1:E$1000) * ((Tabelle1!D$1:D$1000) = A1))

und in Tabelle2!E:
=SVERWEIS(A1; Tabelle1!D:J; 4;FALSCH)
oder
=SVERWEIS(A1; Tabelle1!D$1:J$1000; 4;FALSCH)
Es genügt auch
=SVERWEIS(A1; Tabelle1!D:G; 4;FALSCH)
oder
=SVERWEIS(A1; Tabelle1!D$1:G$1000; 4;FALSCH)

Na dann viel Spaß, und schreib mal, ob Dir das was nützt.

Gruß,
CaroS

Hallo an alle,
Sorry, war unterwegs und schreibe aus dem Grund erst jetzt.

Vielen Dank für die Antworten.
Werde die Formel mal einsetzen und probieren.
Schaut aber gut aus.
Danke

• Problem mit Bedigungen bei Kreutabellen Abfrage
• Makro Excel
• Tage innerhalb eines Monats ermitteln
• Nettoarbeitstage zwischen 2 Daten
• Datumsbereich auswaehlen zur Berichterstellung unter Access