Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1468to1472
1468to1472
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel mit INDEX, ZEILEN & KKLEINSTE

Forumthread: Formel mit INDEX, ZEILEN & KKLEINSTE

Formel mit INDEX, ZEILEN & KKLEINSTE
19.01.2016 18:36:11
Björn
Hallo,
ich habe folgende Formel, die ich für eine Tabellenauswertung anpassen muss.
Da ich die Formel nicht selbst erstellt habe kann ich diese nicht ganz nachvollziehen.
Kann mir jemand die Bestandteile der Formel erklären, damit ich die Formel in meiner Tabelle anwenden kann.
=WENN(ZEILEN($1:1)>ZÄHLENWENN($E:$E;G$3);"";INDEX($A:$A;KKLEINSTE(WENN($E$1:$E$10=G$3;ZEILE($1:$10)); ZEILE($A1)))) In meiner Tabelle sind Bestellnummern hinterlegt (Spalte A), die zu einem bestimmten Datum (Spalte B) ausgelöst werden sollen. Man hat die Möglichkeit einen neuen Auslösetermin zu definieren (Spalte C).
Mit der Formel sollen die Bestellnummern des aktuellen Monats, in diesem Fall des Monats Januar, gesucht und in einer neuen Tabelle nach Tagen untereinander geschrieben werden.
Im Anhang findet Ihre eine Beispieldatei.
Ich bin davon ausgegangen, dass ein VBA Lösung einfacher und eleganter ist, bin daran aber kläglich gescheitert.
https://www.herber.de/bbs/user/102887.xlsm
Viele Grüße

Anzeige

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel mit INDEX, ZEILEN & KKLEINSTE
19.01.2016 19:20:07
Sepp
Hallo Björn,
erstens fehlt bei dir die Angabe des Jahres.
Und zweitens, warum sollte VBA eleganter sein?
Ich würde für die Termine eine Hilfsspalte einfügen.
Tabelle1

 ABCDEFGHIJKLM
1BestellnummerTerminTermin neuHilf  Januar Jahr2014   
2100001.01.2014 01.01.2014  Tage im Monat Januar      
3200020.03.201405.04.201405.04.2014  1234567
4300004.01.2014 04.01.2014  1000  3000   
5400002.01.201404.01.201404.01.2014     4000   
6500022.02.2014 22.02.2014         
7             
8             

Formeln der Tabelle
ZelleFormel
D2=WENN(B2<>"";MAX(B2:C2); "")
G4=WENNFEHLER(INDEX($A$2:$A$1000;AGGREGAT(15;6;ZEILE($A$1:$A$999)/(TAG($D$2:$D$10000)=G$3)/(MONAT($D$2:$D$10000)=MONAT("1."&$G$1))/(JAHR($D$2:$D$1000)=$J$1); ZEILE($A1))); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp

Anzeige
AW: ohne INDEX ohne Hilfsspalte nur AGGREGAT() ...
20.01.2016 09:55:02
...
Hallo Börn,
... Formel G3 einfach nach unten und rechts ziehend kopieren. INDEX() wird erst notwendig, wenn die Bestellnummern alphanumerische Zeichen beinhalten.
 ABCDEFGHIJK
1BestellnummerTerminTermin neu 31.01.2014 Tage im Monat Januar 2014
2100001.01.2014    12345
3200020.03.201413.04.2014   1000  3000 
4300004.01.2014       4000 
5400002.01.201404.01.2014        
6500022.02.2014         
7           

Formeln der Tabelle
ZelleFormel
G1=E1
G3=WENNFEHLER(AGGREGAT(15;6;$A$2:$A$99/(TAG($C$2:$C$99+$B$2:$B$99*($C$2:$C$99=0))=G$2)/(MONAT($C$2:$C$99+$B$2:$B$99*($C$2:$C$99=0))=MONAT($G$1))/(JAHR($C$2:$C$99+$B$2:$B$99*($C$2:$C$99=0))=JAHR($G$1)); ZEILE(A1)); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
;

Forumthreads zu verwandten Themen

Zeilen Kopieren mit Formeln u. Formaten
Lemmi

Hallo zusammen,
ich möchte einen Block (5Zeilen) am Ende einer Tabelle einfügen!
Diese Zeilen sollen mit ihren Formaten unf Formel Kopiert und am Ende der Leiste eingefügt werden!
Die Formeln sind in den Roten Zellen.
Die Zellen sonst sind bis auf Spalte A und B ohne Inhalt!
11.08.2008 14:11:35
Thread-ID: 1000856
5 Beiträge
Spalten mit Formeln mit 1 multiplizieren
Susanne99

Hallo, kriege hier per Datenbankverlinkung teilweise Zahlen eingespielt, die Excel nicht als Zahl, sondern als Text identifiziert. Dem kann ich abhelfen, indem ich den Zellinhalt jeweils mit 1 multipliziere. Wie kann ich die
"*1" in die Formeln in ganzen Spaltenreihen einfügen, ohne jeden...
31.08.2008 10:34:22
Thread-ID: 1006147
8 Beiträge
wenn > Spalte I, dann neue Zeile
Dennis

Hallo Leute,
ich benötige einmal Hilfe eines Experten, da ich noch in den Anfängen stecke.
Ich habe folgendes Problem:
Ich habe eine Liste mit Datensätzen (jeder Datensatz steht für eine Person). Die Datensätze sind unterschiedlich lang. Grds. sind die Datensätze von Spalte A - Spal...
04.09.2008 16:41:00
Thread-ID: 1007458
8 Beiträge
Anzeige
Zeile kopieren wenn wert in Zelle
Andreas

hallo zusammen
in der recherche habe ich diesen code gefunden. da ich jedoch alle zeilen bei welchen in der zelle der spalte h ein "x" steht in ein anderes tabellenblatt kopieren möchte muss ich den code anpassen. nun weiss ich leider nicht was ich verändern muss. wäre super, wenn mir das j...
07.09.2008 20:32:00
Thread-ID: 1008007
3 Beiträge
Text (Datum) aus Zeile mit Zeilenbruch auslesen
Iro

Hallo,
ih habe folgendes Problem: ich muss aus einem vorgegebenen Sheet einen bestimmten Zinssatz zu einem bestimmten (Jahr / Monat) auslesen.
1. Problem: das vorgegebene, gesuchte Datum hat das Format TT.MM.JJJJ, das andere Datum, mit dem ich es vergleichen muss wird mit "Monat Jahr"....
01.10.2008 23:00:06
Thread-ID: 1013778
4 Beiträge
Werte aus PRN Datei einlesen über 65563 Zeilen
Andre´

Hallo alle zusammen,
Ich lese aus einer PRN-Datei die Werte ins Excel ein wie folgt:
Option Explicit
Sub Daten_einlesen() Dim iFree As Integer, strText As String, arrTmp, arrDaten() Dim lngCounter As Long, lngStart As Long, lngEnde As Long ReDim arrDaten(1 To 11, 1 To 1) iFree = Fr...
04.11.2008 08:39:00
Thread-ID: 1021464
12 Beiträge
Wenn Formel mit Spalte
Maris

Hi zusammen,
ist das möglich, dass ich eine Wenn-Formel auf eine Spalte und eine Zelle in der Zeile beziehe? Bsp.:
Tabelle A B C Ergebnis ERM VOLL OHNE 1 x ERM 2 x...
05.11.2008 16:30:00
Thread-ID: 1021984
6 Beiträge
Zeilen kopieren wenn wert in Zelle
Andreas

hallo zusammen
finde für meine herausforderung nichts passendes in der recherche. :-(
ich habe eine tabelle1 mit diversen Einträgen. die Spalten AC, AD, AE enthalten jeweils ein Datum (ist jedoch nicht in jeder Zeile der Fall)
Nun möchte ich folgende Schritte automatisieren
Erst...
24.11.2008 17:08:00
Thread-ID: 1027397
3 Beiträge
wenn zelle gelb, dann ganze zeile loeschen
maxx_1985

Hi zusammen
wie kann man excel das folgende sagen;
wenn in spalte A irgendwo eine gelbe zelle vorkommt, dann loesche mir die ganze ZEILE.
gelb sind naemlich die zwischensummen, die ich raus haben moechte.
danke euch vielmals
gruss
denis
10.12.2008 16:19:09
Thread-ID: 1031735
8 Beiträge
Anzeige
Formel mit VBA kopieren
Hartmut_M

Hallo, ich möchte mit VBA aus einem Bereich die Formeln in einen anderen Bereich kopieren.
Nutze hierfür folgenden code, wobei die variablen spalte und spalteneu für die Spaltennummern stehen:
Range(Cells(5, spalte), Cells(130, spalte)).Select
Selection.AutoFill Destination:=Range(C...
12.12.2008 10:02:26
Thread-ID: 1032307
3 Beiträge
Anzeige
nicht die Formel mitkopieren
TomTom

Hi,
Ich habe in meinem Sheet eine Funktion, mit der will ich verschiedene Zeilen kopieren:
MeineQuelle.Rows(lngZeile).Copy Destination:=MeinZiel.Cells(lngZeile, 1)
Wenn ich nun in einer gefundenen Zeile, die kopiert wird, irgendwo eine Formel drin habe, (die sich auf irgend was bezi...
30.01.2009 11:15:07
Thread-ID: 1045327
8 Beiträge
Zeile ausblenden falls kein Wert
Leon

Hi Zusammen
Habe untenstehenden Code versucht, komme aber nicht weiter.
Wenn in der Zelle A12 nichts steht, wollte ich erreichen,daß die ganze Zeile automatisch ausgeblendet wird ohne Commandbutton.
Private Sub Worksheets_activate() If Range("A12").Value = 0 Then Range("A12").En...
05.02.2009 21:01:00
Thread-ID: 1047510
13 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Formel mit INDEX, ZEILEN & KKLEINSTE in Excel anwenden

Schritt-für-Schritt-Anleitung

  1. Formel verstehen: Die Formel lautet:

    =WENN(ZEILEN($1:1)>ZÄHLENWENN($E:$E;G$3);"";INDEX($A:$A;KKLEINSTE(WENN($E$1:$E$10=G$3;ZEILE($1:$10)); ZEILE($A1))))
    • WENN prüft, ob die aktuelle Zeile größer ist als die Anzahl der Bestellungen.
    • ZEILEN($1:1) gibt die aktuelle Zeile zurück.
    • ZÄHLENWENN zählt die Anzahl der Bestellungen in Spalte E, die dem Kriterium in G3 entsprechen.
    • INDEX gibt den Wert aus Spalte A zurück, basierend auf der Position, die durch KKLEINSTE bestimmt wird.
    • KKLEINSTE findet die kleinste Zeilennummer, die dem Kriterium entspricht.

  2. Hilfsspalte hinzufügen: Füge eine Hilfsspalte ein, die die relevanten Daten filtert. Das kann die Handhabung der Formeln erleichtern.

  3. Formel anpassen: Stelle sicher, dass die Daten in den Spalten A, B und E korrekt sind, damit die Formel ordnungsgemäß funktioniert.

  4. Formel ziehen: Kopiere die Formel nach unten, um die Bestellnummern für jeden Tag im Januar zu erhalten.

Häufige Fehler und Lösungen

  • Fehler: "Bezug ist ungültig": Überprüfe die Zellreferenzen in der Formel. Achte darauf, dass sie korrekt sind.
  • Fehler: Falsche Ergebnisse: Vergewissere dich, dass die Daten in Spalte E die korrekten Kriterien erfüllen und dass die Zeilen korrekt gezählt werden.
  • Fehler: NULL-Werte: Wenn die Formel keine Werte zurückgibt, könnte es daran liegen, dass keine passenden Daten für das gewählte Kriterium vorhanden sind.

Alternative Methoden

  • Verwendung von AGGREGAT: Anstelle von INDEX und KKLEINSTE kannst du die AGGREGAT-Funktion verwenden, um ähnliche Ergebnisse zu erzielen. Beispiel: 
    =AGGREGAT(15;6;A$2:A$999/(B$2:B$999=G$3);ZEILE(A1))
  • VBA-Lösungen: Wenn du mit Formeln nicht weiterkommst, könnte eine VBA-Lösung eine Alternative sein. Diese kann komplexe Daten besser verwalten.

Praktische Beispiele

  • Beispiel 1: Angenommen, du hast folgende Daten: A (Bestellnummer) B (Termin) E (Kriterium)
    1000 01.01.2014 Januar
    2000 20.03.2014 März
    • Die Formel gibt die Bestellnummer 1000 zurück, wenn das Kriterium "Januar" in G3 steht.

  • Beispiel 2: Verwende die Hilfsspalte, um die Bestellungen nach Datum zu filtern und die Formel entsprechend anzupassen.

Tipps für Profis

  • Namen für Bereiche verwenden: Erstelle benannte Bereiche für deine Daten, um die Lesbarkeit der Formeln zu erhöhen.
  • Datenvalidierung: Setze Datenvalidierung ein, um sicherzustellen, dass nur gültige Daten in die Tabelle eingegeben werden.
  • Dynamische Arrays: Wenn du Excel 365 verwendest, kannst du die Funktionen für dynamische Arrays nutzen, um die Ergebnisse noch einfacher zu verwalten.

FAQ: Häufige Fragen

1. Wie kann ich die Formel anpassen, um ein anderes Datum zu berücksichtigen? Du musst das Kriterium in der ZÄHLENWENN-Funktion anpassen. Ändere einfach den Bezug auf die Zelle, die das neue Datum enthält.

2. Funktioniert diese Formel in älteren Excel-Versionen? Die gegebene Formel ist mit Excel-Versionen ab 2010 kompatibel. Achte darauf, dass die verwendeten Funktionen unterstützt werden.

3. Was ist der Vorteil von AGGREGAT gegenüber INDEX? AGGREGAT kann mehrere Optionen bieten, wie das Ignorieren von Fehlern und das Auswählen der k-kleinsten Werte, ohne dass eine Hilfsspalte erforderlich ist.

4. Wie kann ich sicherstellen, dass die Formel immer die neuesten Daten verwendet? Aktualisiere die Daten regelmäßig und stelle sicher, dass dein Datenbereich korrekt definiert ist.

Beliebteste Forumthreads (12 Monate)

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