Excel formel

3 Antworten zur Frage

~ noch nicht online seitdem. Zu 1Also ganz normal wie immer in Excel halt) Zu 2 und 3 Es gibt mehrere Spalten mit Zahlen darin deshalb ~~ wenn es geht bleiben. Ich würde gerne als Endergebnis eine Formel von dir.
Bewertung: 7 von 10 mit 809 Stimmen

Videos zum Thema
Video Loading...

Excel Formel gesucht für mein Problem

Hallo Ich habe eine Frage zu einer Excel Formel die ich programieren will : 1 Jetzt wurden die Zahlen eingeteilt
Spalte A 0 2 1 1 0 4 3 2 1 2 0 6 4 1 0 0 0 Spalte B 7 0 0 0 9 0 0 0 0 0 8 0 0 0 7 9 9
Jetzt sollen alle Zellen gezählt werden die In Spalte A oder B keine Zahl von 1-10 enthalten also hier aber immer nur von einer Zahl bis zur nächsten eingetragenen Zahl der Spalte Excel soll diese Lücken zählen. und Excel soll wenn Excel die Leerzellen gezählt hat, diesen Leerzellen eine Hintergrundfarbe zuornen und diese Leerzellen mit dieser Hintergrundfarbe färben.
bei einer Lücke von 1 Zelle entspricht Rot bei einer Lücke von 2 Zellen entspricht Grün bei einer Lücke von 3 Zellen entspricht Blau bei mehr als 3 Zellen Schwarz
Zurück zu dem Beispiel hier:
Das Ergebnis soll dann wie folgt aussehen
Ergebnis Spalte A Rot,2,1,1,Rot,4,3,2,1,2,Rot,6,4,1,Blau zum Vergleich am Anfang 0 2 1 1 0 4 3 2 1 2 0 6 4 1 0 0 0
und das soll in Spalte B so aussehen: Ergebnis Spalte B 7,blau,9,schwarz,8,blau,7,9,9 Zum Vergleich am Anfang Spalte B 7 0 0 0 9 0 0 0 0 0 8 0 0 0 7 9 9
Habt ihr mir dafür eine Formel die ich verwenden kann könnt ihr die mir kurz erstellen?
Danii358
Du hast Dir da was ziemlich kompliziertes vorgenommen. Wenn Du ein paar Tage warten kannst, programmiere ich Dir ein Makro, das alles macht.
Vorher aber noch ein paar Fragen:
1. -Erfolgt die Eingabe immer in C1 und kann nach dem Versetzen sofort wieder gelöscht werden?
2. -kann ich davon ausgehen, dass unterhalb dieser Zahlen in A:A und B:B nichts weiteres steht? 3. -Sollen die Zahlen unten angehängt werden oder ginge es auch, sie oben einzufügen und alle anderen Zellen in A:A und B:B nach unten zu schieben?
4. -Würde es stören, wenn die Farben der neuen Lücken erst mal der Größe der bisher geschaffenen Lücke entspräche, sich also beim Einfügen einer weiteren LeerZelle nochmal von rot über grün und blau nach schwarz ändern ändern müsste? ich werde erst mit der Arbeit beginnen, wenn Du zu allen Punkten geantwortet hast.
Hallo, Sorry für meine späte Antwort, bin seit Montag geschäftlich unterwegs und war hier noch nicht online seitdem.
Zu 1Also ganz normal wie immer in Excel halt)
Zu 2 und 3
Es gibt mehrere Spalten mit Zahlen darin deshalb wäre es gut wenn ich für eine Spalte ne formel hätte die ich kinderleicht für die anderen geringfügig abändern könnte.
Hier mal ein Gedanke von mir :
A3=Wenn G3 =1 dann kopiere G3 sonst lasse die Zelle leer. Kannst du dies zu einer Formel umwandeln und anschließend folgendes machen:
-das mit den Leerzeilen und den dazu gehörigen Farben. Erklärung nochmals hierzu: Nehmen wir an in Zelle A3 steht die Zahl 5 und in Zelle A6 steht die Zahl 9 dann ist folglich die Zelle A4 und A5 leer. Das entspricht einer Lücke von zwei Zellen Diese Lücke soll dann Orange gefärbt werden. Da eine Lücke von 2 Zellen vorhanden ist.
Dazu habe ich mir folgende Regelung überlegt: Für eine Lücke von -1 und 2 Zellen Farbe Orange -3, 4, 5 Farbe Blau -6,7,8 Grau -9,10,11 hellblau -12,13,14 hellgrün -15,16,17 Weinrot -18,19,20,21.Schwarz
Die Farben sollten schon wenn es geht bleiben.
Ich würde gerne als Endergebnis eine Formel von dir bekommen, die ich leicht geringfügig abändern kann.
Könntest du mir sowas jetzt programmieren? Brauchst du noch mehr Informationen von mir über meine Idee?
für deine Bemühungen
Danii358
Die Zahlen werden immer mehr von oben nach unten aber obwohl in mehreren Spalten zählen stehen, steht pro Zeile immer nur eine Zahl Es kann also nicht zu überlappungen kommen

Durch Excel Formel ganze Zeilen kopieren

bei einer Excel Formel, wenn sich jemand gut mit Excel auskennt kann jemand mir eine Excel Tabelle mit einer Formel erstellen die von anderen Reitern ganze Zeilen kopiert wenn ein Wert in einer Spalte exact gleich ist?
Beispiel:
Name: ID-Nummer: Datum:
Peter 435646 01.01.2013 Peter 435646 01.01.2013 Hans 737096 01.01.2013 Peter 435646 01.01.2013 Müller 213045 01.01.2013
In der ersten tabelle kann man die ID-Nummer eingeben die die Formel benutzen soll um die Zeilen mit z.B. der ID-Nummer 435646 alle von Tabelle 2 zu Tabelle 1 zu kopieren oder verschieben, sodass in Tabelle 1 nur die Zeilen drinne sind wo die ID-Nummer 435646 ist. Ist das Möglich? wenn Ja kann das bitte jemand machen?
AzubiMan2014
Angenommen Deine Daten stehen in Tabelle1 ab Spalte A.
Dann z.B. in Tabelle2 in A1 die gesuchte ID schreiben und z.B in A3 diese Formel:
{=WENNFEHLERTabelle1!A$1:A$1000;KKLEINSTETabelle1!$A$1:$A$1000=$A$1;ZEILEA1)));"")}
ACHTUNG!
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER.
DAS erzeugt die {} und macht die Formel zu einer Matrixformel.
Immer darauf achten, dass es eine Matrix-Formel bleibt, wenn Du etwas änderst.
Also immer mit STRG & SHIFT & ENTER abschließen.
Diese Formel kopierst Du dann nach rechts , soweit wie nötig und anschließend die gesamte Zeile nach unten soweit wie nötig.
Immer darauf achten, dass es eine Matrix-Formel bleibt, wenn Du etwas änderst.
Also immer mit STRG & SHIFT & ENTER abschließen. Klappt es?
jetzt bist Du mir wieder mal zuvor gekommen!
Die ID steht aber ib Spalte B, wodurch wieder mal der Vorteil von Index gegenüber SVerweis bestätigt wird
Es zeigt die Daten an aber nicht alle nur eine Zeile und auch nicht die ID-Nummer die ich geschrieben hab
Ich korrigiere mich es Zeigt alle Daten an aber nicht von der ID- Nummer und nur eine Zeile wie kann ich das für mehrere machen?
Formel nach dem Abschliessen mit Steuerung+Umschalt+Eingabe einfach nach unten ziehen.
Tabelle1!$B$1:$B$1000=$A$1 liefert doch lauter Null-Zellen für alle nicht zutreffenden Zellen? Und vllt vllt sind auch Leerzellen dazwischen oder am Ende?.
Hab meine AW entsptrechend ergänzt.
Deine Liste beginne in A2:C2 und sei beliebig lang., die Suchzahl stehe in D2. Beim Auslesen erscheint der unterste Eintrag zuerst. Formel in E2:
{=INDEX* ZEILE+ZEILE* 10^-15;ZEILE-ZEILE+1))}
Achtung: Dies ist eine Matrixformel!
Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination Strg+Shift+Enter abschließen. Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!
Die Formel nach rechts kopieren und die Zellen nach Bedarf formatieren
Brauchst Du die Spalten D etc, den gesamten Block sant der Suchzahl beliebig nach rechts verschieben, dann runterkopieren, soviele gleiche Einträge Du erwartest. Sinds weniger, steht 0 drin.
zum Verständnis: Matrix-Formeln: das ist eine längere Geschichte. eine recht gut verständliche Einführung findest Du unter
http://www.online-excel.de/a_new_2012/singsel.php?f=26
KGrösste findet die Zeile des letzten Eintrags, die 1 ergibt sich aus dem Formelteil
=ZEILE-ZEILE+1), was nach unten kopiert eine immer größere Zahl ergibt
Index sucht in der gesamten Spalte A nach dem Eintrag in dieser Zeile. Beim kopieren nach rechts wird aus A:A B:B und C:C
#viel Erfolg
um zu überprüfen dass die Formel tatsächlich funktioniert, mässen natürlich unterscheidbare Daten vorliegen: also variiere das Datum in Sp.C!
Die Suchzahl kannst Du beliebig woandershin verschieben , denn beim Verschieben bleiben die Bezüge auf die ZELLEN erhalten
von anderen Reitern":
Schneide den Block aus und füge ihn an gleicher Stelle in der anderen Tabelle ("Reiter") ein, die Adressen passen sich dann automatisch entsprechend an!
Habs eben nachvollzogen, die Formel in E2 heißt dann:
{=INDEX* ZEILE+ZEILE* 10^-15;ZEILE-ZEILE+1
mit einem Trick kannst Du
das Ergebnisfeld vervielfältigen
  • Füge nochmals ein neues Blatt in die Datei ein.
  • Kopiere D2:G2
  • Füge das in D2 des neuen Blattes ein
  • verschiebe die eingefügten Zellen, wohin Du willst
  • kopiere sie dort erneut und
  • füge sie an gleicher Stelle eines beliebiigen anderen Blattes ein
  • kopiere die Formeln nach unten
  • Ändere die Suchzahl nach Erfordernis.
zugegeben etwas mühsam, aber du verlangst auch einiges von xl
Hab grad festgestellt, dass die Formel einen überflüssigen Teil hat.
Um diesen gekürzt funktioniert sie genauso:
die Formel in E2 heißt dann:
{=INDEX* ZEILE;ZEILE-ZEILE+1))}
so gekürzt lässt sie sich auch leichter verstehen:
$B:$B=$D$2 erstellt eine Liste aus vielen Nullen und wenigen Einsen, die Einsen dann, wenn der Wert wie D2 drinsteht.
*Zeile multipliziert dann jede Zeilennummer mit diesen Nullen/Einsen,
Ergibt wieder eine Liste aus vielen Nullen und dazwischen den Zeilenzahl der zutreffenden Einträge.
Kgrösste holt nun den grössten, zweitgrössten,. Eintrag als Zeilennummer n heraus.
Index sucht in Spalte A:A nach dieser Zeilennummer n und
liest den dort gefundenen Wert aus,
beim Kopieren nach rechts dann aus Spalte B:B, C:C,.
Kann sein, dass das bei sehr hohen Zeilenzahlen nicht mehr funktioniert.
Oubyis Formel mit KKleinste müsste so funktionieren :
{=WENNFEHLERTabelle1!A$1:A$1000;KKLEINSTETabelle1!$B$1:$B$1000;""&$D$1)));"-")}
kannst Du nach rechts und nach unten kopieren und- falls es an dieser Stelle stört- D1 auch beliebig verschieben
Geht mit Formeln alleine sicher nicht, da muß schon VBA ran
Ich hab leider nicht so erweiterte Kenntnisse in Excel haben Sie vielleicht ein Ratschlag/Formel?
Das ist wie mit Spiritualismus. Wenn der Mensch sich irgendwas nicht erklären konnte dann musste Gott herhalten. Nilüberschwemmung --> Gott; Sternschnuppen --> Gott Ebbe und Flut --> Gott, Krankheit --> Gott.
genauso ist das Formeln und VBA.
Alle schreien immer gleich nach VBA ohne die Formeln die Programm schon eingebaut hat auch nur im geringsten zu nutzen.
Die Grenze zwischen VBA und Formeln ist da, nur liegt sie bei jedem an ner anderen Stelle.
Ich suche lieber ne Formellösung ehe ich mit Kanonen auf Spatzen schieße.
Habe ich das richtig verstanden:
Du willst eine ID-Nummer in einer Zelle auswählen können und daraufhin den Namen und das Datum angezeigt bekommen?
Genau ich möchte das die Ganze Zeile kopiert wird wo die ID-Nummer die Nummer entspricht die ich eingeben z.B. so wie bei einer Suche ich gebe die ID-Nummer z.B. 435646 in einer Spalte ein und die Formel soll in der anderen Tabelle alle Zeilen wo diese Nummer in der Spalte ID-Nummer zu finden ist Kopieren und in der anderen Tabelle einfügen also alles Name ID-Nummer Datum usw.
Wenn die ID-Nummer in der zweiten Tabelle nur EINMAL vorkommt, kannst du sowas durch die Funktion "Sverweis" machen. Alternativ kannst du eine Pivot-Tabelle erstellen lassen und die ID-Nummer durch einen Berichtsfilter auswählen.

Excel-Problem, komplizierte Excel-Formel, Excel 2010

Hallo Ihr lieben,
ich sitze mal wieder vor Excel und hab eine harte Nuss zu knacken.
Hier die Ausgangssituation:
In der Zelle E8 können 3 verschiedene Ergebnisse erscheinen: "Standard", "Überbreite" und "Schmal".
Gleichzeitig können in der Zelle F2 2 verschiedene Ergebnisse stehen, nämlich "ja" und "nein".
Die Zelle F8 soll jetzt abgleichen was in den beiden Zellen steht und dann jeweils entsprechend was anderes eintragen. Es gib 6 Kombinationen:
F2 = "ja" und E8 = "Standard" DANN bitte den Zellinhalt von H2 übernehmen
F2 = "ja" und E8 = "Überbreit" DANN bitte den Zellinhalt von H3 übernehmen
F2 = "ja" und E8 = "Schmal" DANN bitte den Zellinhalt von H4 übernehmen
F2 = "nein" und E8 = "Standard" DANN bitte den Zellinhalt von I2 übernehmen
F2 = "nein" und E8 = "Überbreit" DANN bitte den Zellinhalt von I3 übernehmen
F2 = "nein" und E8 = "Schmal" DANN bitte den Zellinhalt von I4 übernehmen
Bekomme ich sowas in einer einzigen Formel unter?
schonmal
Dafür eigenet sich eher eine Liste statt eine Wenn Funktion.
Die Liste kann man dann mittels Sverweis oder durch die Kombination von Index und Vergleich auslesen=INDEXF2;H1:I1E8;G2:G4)) Kommentar von , 27.11.2014 MIt SVERWEIS kommst du in einer 2 Dimensionalen Tabelle aber nicht weit ggg
Eine etwas kürzere Alternative zu Hyp3rn0v4s Formel:
=INDIREKTF2="ja";"H";"I"E8;{"Schmal";"Standard";"Überbreit"};{4;2;3}))
Hinweise: Wenn in E8 nicht "ja" steht, gehe ich davon aus, dass "nein" drinsteht. Die Vektoren im Verweis musste ich anders sortieren, weil VERWEIS eine aufsteigende Reihenfolge im Suchvektor verlangt, in diesem Fall nach dem Alphabet.
Da ich als "Excel-Anfänger" auch gerne dazulerne, hätte ich noch eine kleine Frage:
Kann man das hier noch eindeutig lösen? Sprich, dass nur absolut richtige Eingaben akzeptiert werden, und nicht zum Beispiel auch "schmall" oder ähnliches.
Schöne Lösung
Ja, wobei man das sinnvollerweise direkt in der Zelle mit den Einträgen, in diesem Fall E8, macht. Dafür verwendet man die Datenüberprüfung. Mehr dazu hier Anwenden der Datenüberprüfung auf Zellen
Wenn man es dagegen in der Formel genau haben möchte, dann kann man eine Matrixformel mit Wahrheitswerten verwenden. Den Zeilenindex kann man z.b. mit =SUMMENPRODUKT((E8="Standard"E8="Überbreit"E8="Schmal" ermitteln. Dabei werden nur die genauen Zeichenketten akzeptiert.
Excel-Formel mit einer variablen aus einer anderen Zelle
Excel soll in einer Spalte mittels der Formel
=SUMMENPRODUKTB12:B1004WECHSELNB12:B1004);"t";)))
alle Buchstaben "T" zählen. Soweit so gut.
Nun soll aber zwischendrin ein Reset gemacht werden und es soll von neuem gezählt werden, zb jetzt erst ab Zeile 57 und nicht ab Zeile 12 wie in der Formel.
Kann man die Formel diesbezgl so ändern, das man für die "12" einen Platzhalter nimmt und in einer anderen Zelle dann die 57 eingibt, die von der Formel dann erkannt und entsprechend übernommen wird.
So das man praktisch jederzeit sagen kann, zähle ab Zeile xy
Kann man.
Schau Dir mal die Funktion INDIREKT an.
Die Lösung schicke ich Dir in 10 Minuten oder so, aber vielleicht kommst Du ja selber 'drauf
SUMMENPRODUKTINDIREKT("B"&C1&":B1004""B"&C1&":B1004"));"t";)))
In C1 steht die 12, oder was Du willst.
Wenn dort allerdings keine Zeilennummer steht, gibt es eine Fehlermeldung, die man ggf. abfangen müsste.
Freut mich.
für die Rückmeldung und das Kompliment.
Excel-Formel für SVERWEIS/WVERWEIS: Gesuchtes Ergebnis soll größer/gleich Suchkriterium sein.
ich habe schon wieder ein Excel-Problem und vielleicht könnt ihr mir ja so gut wie beim letzten Mal helfen. Es geht um eine Tabelle, die Kosten berechnet. Und zwar berechne ich zuerst ein Gesamtgewicht von Ware, die einer bestimmten Artikelgruppe zugeordnet ist. Beides zusammen ist ausschlaggebend für die Grundkosten. Für die Grundkosten gibt es eine Tabelle, deren Spaltenüberschriften mit Gewichtskosten versehen sind. Die Zeilen sind in die verschiedenen Artikelgruppen von I bis VII eingeteilt.
Sagen wir, das errechnete Gewicht beträgt 56 kg und die Artikelgruppe ist II.
Die erste Spalte der Tabelle enthält eine 50, die zweite eine 55, die dritte eine 70. Die in diesen Spalten angegebenen Kosten werden jeweils bis zu diesem Gewicht berechnet, also müsste ich bei 56 kg schon die Kosten aus der Spalte mit der Überschrift 70 nehmen und in dem Fall die zweite Zeile für die zweite Artikelgruppe.
Mein Problem beim WVERWEIS ist, dass er bei nicht mit den Überschriften übereinstimmenden Gewichten immer eine Spalte zu früh aufhört zu suchen und die Kosten damit zu niedrig sind. Kann ich Excel irgendwie sagen, dass das Suchergebnis nicht kleiner/gleich dem Suchkriterium, sondern größer/gleich sein muss? Geht das vielleicht gar nicht und es gibt eine ganz andere Funktion dafür?
Ich hoffe, das ist verständlich und schon mal für.
Ich denke, Du kannst die Tabelle mit den Vergleichswerten sich auch "rückwärts" sortieren, also den größten Wert zuerst.
Dann hilft Dir die Funktion VERGLEICH.
Zitat aus der Hilfe dazu:
"Vergleichstyp: -1:
VERGLEICH sucht nach dem kleinsten Wert, der größer oder gleich dem Wert für Suchkriterium ist. Die Werte im Argument Suchmatrix müssen in absteigender Reihenfolge angeordnet sein, z. B. WAHR, FALSCH, Z-A,.2, 1, 0, -1, -2,. usw."
Vergleich liefert Dir allerdings nur eine Zahl, an wievielter Stelle der Wert in der Matrix liegt.
Um den wirklich gesuchten Wert zu finden musst Du noch die Funktion INDEX "bemühen".
Angenommen Deine Vergleichswerte stehen in A1:C1 und zwar:
A1: 70
B1: 55
C1: 50
Darunter, in Zeile 2, also A2:C2 stehen die Werte, die ausgegeben werden sollen. In D1 steht der Suchwert, z.B. 56
Dann diese Formel:
=INDEXD1;A1:C1;-1)z.B. File-Upload.net - Ihr kostenloser File Hoster!in einer neuen Antwort) einen Screenshot
Wenn ich es richtig verstehe, dann soll die Tabelle, aus der die Information geholt wird, so aussehen?
X steht hier natürlich für die jeweiligen Preise in deiner Tabelle.
Ja, ganz genau. Hast du dafür eine Lösung?
Excel Formel für differenzen bei Uhrzeiten
Ich will gerade mir einen Lohnzettel gestalten und dabei zum Beispiel als Zeiten rein schreiben "08:00" als Arbeitsbeginn und 16:00 als Arbeitsende. Der Wert soll dann in Stunden ausgegeben werden. Aber wie geht das?
Und ich habe versucht mit dem Format "Zeit" das Feld so zu formatieren dass dann dort auch immer 08:00 etc. steht aber wenn ich "12" eingebe, taucht da immer "00:00" auf.
Die beiden Zellen mit der Uhrzeit musst du dementsprechend als Uhrzeit-Format definieren. Die Zelle für die Stunden als "Benutzerdefiniert" und dann :mm:ss wählen. Dann in dieser Zelle =Zelle mit Arbeitsende minus Zelle mit Arbeitsbeginn.
Wenn du da "12" eingibst, interpretiert Excel das als die Zahl 12 und nicht als Uhrzeit. Um 12 Uhr einzugeben, musst du die Minuten mit angeben, also "12:00".
Tag 1 = 01.01.1900, falls in den Optionen zu Excel nicht anders eingestellt
wie von PWolff hervoragend erklärt.
Es ist auch immer wichtig der kleinere Uhrzeit-Wert vom größerem abziehen, da im Zeitformat keine Minuszeiten berechnet werden können.
Das muß man anders lösen.
:mm genügt, da :ss die Sekunden darstellen.
Excel Formel mit 2 Suchkriterien
ich habe ein Problem mit einer Formel.
Ich will folgendes darstellen.
Die Formel soll zuerst "A" suchen, wenn er "A" gefunden hat soll er "B" suchen und wenn er "B" gefunden hat, mit den Wert in Spalte "3" rausgeben. Falls dort kein Wert steht soll er "0" rausgeben.
Wie schaffe ich es diese Suchkriterien in eine Formel einzubauen?
schonmal
Denke daran, dass du die Formel mit ++ eingibst =INDEXD1;WENN;0)) in meinem Beispiel ist in Zelle D1 das erste Suchkriterium und in E1 das Zweite
in Spalte B soll nach deinem zweiten Suchbebegriff gesucht werden und in Spalte C der Wert, der zurückgegeben werden soll
wenn in Spalte 3 Zahlen stehen geht es einfacher
=SUMMENPRODUKT((A2:A100="A"B2:B100="B"C2:C100



computer
scrollen funktioniert einfach nicht mehr

- gemacht und plötzlich hat das 2 finger scrollen nicht mehr funktioniert. ich hab auch schon den treiber aktualisiert und die -


zahlen
Wieso wird das PayPal Konto im PS-Store nicht akzeptiert?

- seien das es blockiert wurde (seid 2 monaten muss jedes password bei Paypal 1 Buchstaben, 1 Zahl, und 1 Großbuchstaben -


informatik
Warum verliert meine PS4 ständig die Internetverbindung?

- Problem. Ich wohne in Berlin und habe mit meiner PS4 dort nie Probleme mich mit dem Internet zu verbinden, sonder sie hat sich -