Hallo, liebe Wissende,
vielleicht weiß jemand guten Rat. In Zelle A1 bis A10 werden Daten errechnet. Aufgrund der entspr. Formel bleiben aber manche Zellen leer. In B1… sollen nur die nichtleeren Daten aus Spalte A untereinander dargestellt werden. Beispiel:
A1 Hund … B1 Hund
A2 Katze …B2 Katze
A3 …B3 Maus
A4 …B4 Elefant
A5 Maus …B5 usw.
A6 …B6 usw.
A7 Elefant …B7 usw.
Für Hilfe und guten Rat wäre ich herzlich dankbar.
Schönen Gruß
Ludwig
Hallo Ludwig,
schau dir diesen Link mal an
Gruß Holger
Hallo Holger,
danke für den Tipp. Ich hab die Formel eingesetzt, habe aber noch folgendes Problem: Was ist in der Formel was? Manches, z.B. (A1), darf nicht verändert werden. Wo genau bringe ich in der Formel aber meine Zeilen/Spalten unter, aus denen meine Daten zusammengesucht werden sollen? Irgendwas mache ich noch falsch. Ich krieg zwar tatsächlich die Daten richtig zusammengestellt. Aber anschließend steht noch eine ganze Anzahl #ZAHL! darunter.
Meine lückenhaften Daten stehen in LL7:LL36 und sollen per Formel in LM7:LM36 (oder weniger) zusammenhängend dargestellt werden. Wo muss dieses in meiner Formel auftauchen, und mit oder ohne $-Zeichen davor?
Wenn ich das noch erfahren würde …
Schönen Dank
Ludwig
Noch eine Ergänzung:
Meine derzeitige Formel schreibt immer dann die Fehlermeldung #ZAHL!, wenn die Quellspalte eine Lücke aufweist.
Ludwig
Meine derzeitige Formel schreibt immer dann die Fehlermeldung
#ZAHL!, wenn die Quellspalte eine Lücke aufweist.
Hallo Ludwig,
mach aus dem dritten Semikolon in deiner Formel einen Doppelpunkt, dann kommt sicher nicht mehr #ZAHL! 
Gruß
Reinhard
{=WENN(ZEILE(LL7)>ANZAHL2(LL$7:LL$36);"";INDEX(LL:LL;KKLEINSTE(WENN(LL$7:LL$36"";ZEILE($7:blush:36));ZEILE(LL7))))}
Die Datenspalte mit den Lücken ist LL, die Formel steht in LM.
Hallo Reinhard,
zunächst herzlichen Dank! Bei dir ist man bei Excel immer in den besten Händen - seit vielen Jahren!
Jetzt ist mir aber noch was unklar: Deinem Rat zufolge müsste ich das Semikolon vor KKLEINSTE ersetzen. Probiert: Fehlermeldung gleich beim versuchten STRG+SHIFT+ENTER.
Was mache ich falsch? Vielleicht kriegen wir es noch gebacken. Schönen Dank im voraus,
Ludwig
{=WENN(ZEILE(LL7)>ANZAHL2(LL$7:LL$36);"";INDEX(LL:LL;KKLEINSTE(
WENN(LL$7:LL$36"";ZEILE($7:blush:36));ZEILE(LL7))))}
Hallo Ludwig,
Jetzt ist mir aber noch was unklar: Deinem Rat zufolge müsste
ich das Semikolon vor KKLEINSTE ersetzen. Probiert:
Fehlermeldung gleich beim versuchten STRG+SHIFT+ENTER.
Was mache ich falsch?
du hast meinen Rat schon korrekt umgesetzt, aber vergiss den Rat.
Er war ein Ulk
Ich dachte dadurch kommst du vielleicht darauf daß dir keiner helfen
kann wenn du DEINE Formel nicht zeigst.
Und, ich habe ja auch nicht gelogen, der Fehler #ZAHL! kam nicht mehr *grins*
Vielleicht kriegen wir es noch gebacken.
Glaube ich sehr, jetzt sehen wir ja deine Formel.
Zur Wiedergutmachung meines Scherzes erkläre ich dir ein bißchen die Formel bzw. etwas was für viele Formeln auf der wunderbaren Seite
die Holger dir zeigte gilt bzw. grundsätzlich für Formeln.
Für jede Zelle eine eigene Formel zu entwickeln hat nur Nachteile.
Viel besser ist es eine Formel zu entwicklen die man in andere Zellen kopieren kann.
Als Negativbeispiel, du hast in A3:A10 Werte, jetzt willst du in B1 den ersten Wert aus A3:A10 mit der Indexformel, das wäre dann diese Formel:
=INDEX(A3:A10;1)
den zweiten Wert erhälst du mit dieser Formel in B2
=INDEX(A3:A10;2)
Du erkennst, das Runterkopieren der Formel in B1 bringt noch wenig,
da dann in jeder Zelle in B =INDEX(A3:A10;1) steht und du dann manuell von Zelle zu Zelle gehen müßtest um die 1 erst in 2 dann in 3 usw. zu wandeln.
Du müßtest also Excel dazu bringen daß es für dich da diese 1 hochzählt. Also schreibst du das dann so:
=INDEX(A3:A10;Zeile(A1))
wird das nun nach B2 kopiert so steht dort dann:
=INDEX(A3:A10;Zeile(A2))
usw.
So, in der Formel wird Zeile(A1) zweimal benutzt, beide male zum gleichen Zweck, es soll in der obersten Formelzelle an der Formelstelle wo es steht eine 1 erzeugen , in der Formelzelle darunter eine 2 usw.
Wenn du das alles kapiert hast so ist dir auch klar daß
Zeile(A1) oder Zeile(LL1) wurscht ist.
Lösung:
LM7: {=WENN(ZEILE(A1)>ANZAHL2(LL:LL);"";INDEX(LL:LL;KKLEINSTE(WENN(LL$7:LL$36"";ZEILE($7:blush:36));ZEILE(A1))))}
Gruß
Reinhard
{=WENN(ZEILE(A1)>ANZAHL2(LL:LL);"";INDEX(LL:LL;KKLEINSTE(WENN(LL$7:LL$36"";ZEILE($7:blush:36));ZEILE(A1))))}
Hallo Reinhard, jetzt hast du mich aber erschreckt. Zu Recht. Ich hatte freilich gedacht, der Holger’sche Link zur Originalformel würde genügen. Aber du hast recht: Und jetzt: Leider immer noch dasselbe Problem!
Vielleicht kommt es daher, dass die Quellspalte LL ja nicht leer ist, sondern Formeln enthält, die manchmal etwas anzeigen und manchmal nichts, also „leer“ aussehen.
Noch habe ich Hoffnung 
)
Vielen Dank und schönen Gruß,
Ludwig
Hallo Ludwig,
{=WENN(ZEILE(A1)>ANZAHL2(LL:LL);"";INDEX(LL:LL;KKLEINSTE(WENN(L
L$7:LL$36"";ZEILE($7:blush:36));ZEILE(A1))))}
Hallo Reinhard, jetzt hast du mich aber erschreckt. Zu Recht.
das ist schön Erstens lockert sowas auf was gut fürs Arbeitsklima ist, viel besser als bierernst stets nur ernsthaft an der Sache zu sein.
Und, beabsichtigter Nebeneffekt, das hat sich bei dir mehr eingeprägt als wenn ich geschrieben hätte, zeige bitte mal deine Formel.
Und jetzt Leider immer noch dasselbe Problem!
ECht? Immer noch #Zahl!? Seltsam. Bei mir nicht, schau mal meine nachstehende Tabelle, L oder LL ist wurscht.
Vielleicht kommt es daher, dass die Quellspalte LL ja nicht
leer ist, sondern Formeln enthält, die manchmal etwas anzeigen
und manchmal nichts, also „leer“ aussehen.
Okay. Also muß die Matrixformel geändert werden.
Noch habe ich Hoffnung
Ich bin zuversichtlich, Holger ist ja auch dabei und er ist fitter in Matrix-Formeln als ich.
Tabellenblatt: [Mappe1]!Tabelle1
│ K │ L │ M │
───┼───┼───┼───┤
7 │ a │ a │ a │
───┼───┼───┼───┤
8 │ │ 0 │ 0 │
───┼───┼───┼───┤
9 │ b │ b │ b │
───┼───┼───┼───┤
10 │ │ 0 │ 0 │
───┼───┼───┼───┤
11 │ │ 0 │ 0 │
───┼───┼───┼───┤
12 │ c │ c │ c │
───┼───┼───┼───┤
13 │ │ 0 │ 0 │
───┼───┼───┼───┤
14 │ │ 0 │ 0 │
───┼───┼───┼───┤
15 │ │ 0 │ 0 │
───┼───┼───┼───┤
16 │ d │ d │ d │
───┴───┴───┴───┘
Benutzte Formeln:
L7 : =K7
L8 : =K8
L9 : =K9
usw. in L
Benutzte Matrixformeln:
M7 : {=WENN(ZEILE(A1)\>ANZAHL2(L:L);"";INDEX(L:L;KKLEINSTE(WENN(L$7:L$36"";ZEILE($7:blush:36));ZEILE(A1))))}
M8 : {=WENN(ZEILE(A2)\>ANZAHL2(L:L);"";INDEX(L:L;KKLEINSTE(WENN(L$7:L$36"";ZEILE($7:blush:36));ZEILE(A2))))}
M9 : {=WENN(ZEILE(A3)\>ANZAHL2(L:L);"";INDEX(L:L;KKLEINSTE(WENN(L$7:L$36"";ZEILE($7:blush:36));ZEILE(A3))))}
usw. in M
(Matrixformeln nicht mit "Enter" sondern mit "Strg+Shift+Enter" eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von Excel erzeugt.)
K7:M16
haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Hallo Reinhard,
leider gibt es noch keine Entwarnung. Im Gegenteil, es ist schlimmer.
Wenn ich in LM7 die Formel eingebe, wie du sie mir schicktest, dann kann ich diese nicht nach unten ausfüllen (Fehlermeldung: „ARRAY kann nicht …“).
Ich kann aber auch nicht in Zeile 2 das (A1) ind (A2) ändern, denn dann wird es automatisch in allen Zeilen (7-36) abgeändert.
Kurz, was du mir empfiehlst zu
M7: …
M8: …
usw.
krieg ich gar nicht hin.
Und, beabsichtigter Nebeneffekt, das hat sich bei dir mehr
eingeprägt als wenn ich geschrieben hätte, zeige bitte mal
deine Formel.
Hätte aber auch so getan.
Okay. Also muß die Matrixformel geändert werden.
Tabellenblatt: [Mappe1]!Tabelle1
│ K │ L │ M │
───┼───┼───┼───┤
7 │ a │ a │ a │
───┼───┼───┼───┤
8 │ │ 0 │ 0 │
───┼───┼───┼───┤
9 │ b │ b │ b │
───┼───┼───┼───┤
10 │ │ 0 │ 0 │
───┼───┼───┼───┤
11 │ │ 0 │ 0 │
───┼───┼───┼───┤
12 │ c │ c │ c │
───┼───┼───┼───┤
13 │ │ 0 │ 0 │
───┼───┼───┼───┤
14 │ │ 0 │ 0 │
───┼───┼───┼───┤
15 │ │ 0 │ 0 │
───┼───┼───┼───┤
16 │ d │ d │ d │
───┴───┴───┴───┘
Benutzte Formeln:
L7 : =K7
L8 : =K8
L9 : =K9
usw. in L
Habe ich gemacht. Ich hab, wenn die Zelle „leer“ ist, versuchsweise auch 0 (Null) oder einen Leerstring (" ") anzeigen lassen, aber das hat erwartungsgemäß auch nicht geklappt. Wie soll ich die Nullen in deinem obigen Matrixbeispiel interpretieren?
Benutzte Matrixformeln:
M7 :
{=WENN(ZEILE(A1)>ANZAHL2(L:L);"";INDEX(L:L;KKLEINSTE(WENN(L$7:L
$36"";ZEILE($7:blush:36));ZEILE(A1))))}
M8 :
{=WENN(ZEILE(A2)>ANZAHL2(L:L);"";INDEX(L:L;KKLEINSTE(WENN(L$7:L
$36"";ZEILE($7:blush:36));ZEILE(A2))))}
M9 :
{=WENN(ZEILE(A3)>ANZAHL2(L:L);"";INDEX(L:L;KKLEINSTE(WENN(L$7:L
$36"";ZEILE($7:blush:36));ZEILE(A3))))}
usw. in M(Matrixformeln nicht mit „Enter“ sondern mit
„Strg+Shift+Enter“ eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von
Excel erzeugt.)
K7:M16
Das war mir bekannt, und ich hab’s auch so gemacht.
Ob du und Holger noch weiter was wissen? Wär ja schön. Ich tu’s übrigens nicht für mich, sondern für meine ehemalige Schule (bin jetzt im Ruhestand).
Hoffnungsfroh grüße ich,
Ludwig
Jetzt hab ich vorher aus lauter Schreck was Blödes gesagt: Die A1 werden beim Ausfüllen der Formel nach unten automatisch abgeändert in A2 usw. Das war mir kurzzeitig entgangen …
Ich hatte dich falsch interpretiert und dachte, dies müsse ich manuell tun. Und das ging dann nicht.
Aber sonst bleibt es bei den #ZAHL!
Leider,
Gruß
Ludwig
Hallo Ludwig,
Jetzt hab ich vorher aus lauter Schreck was Blödes gesagt: Die
A1 werden beim Ausfüllen der Formel nach unten automatisch
abgeändert in A2 usw. Das war mir kurzzeitig entgangen …
entspann dich bitte.
Ich garantiere dir das/dein Problem WIRD gelöst.
Wenn die leiben Mods mal auftauchen würden wenn man sie dringend braucht
hättest du ggfs. schon eine Matrixformel als Lösung.
In Matrixformeln bin ich nicht so fit.
Aber eine Lösung mit einer Hilfsspalte kann ich dir schon anbieten.
Und natürlich eine Vba-Lösung.
Aber sonst bleibt es bei den #ZAHL!
Nochmal zu Matrixformeln. Die kannst du nicht nach unten kopieren wie normale Formeln.
Du schreibst die Formel mit Strg+Shift+Enter in LM7. Dann markierst du LM7, Strg+c, dann markierst du LM8:LM36 und Strg+v.
Wegen 'Zahl!, um das zu klären da ich die meldung nicht bekomme,
lade eine Beispielmappe hoch.
Gruß
Reinhard
lade eine Beispielmappe hoch.
Lieber Reinhard,
wir sind beide ungefähr gleich lang bei w.w.w. Aber ich hab noch nie eine Datei hochgeladen. Ich könnte dir meine Excel-Datei problemlos zukommen lassen. Sie enthält bisher nur Dummy-Daten.
Nur: Wie lade ich sie hoch? Nochmals herzlichen Dank für deine Hilfe und Geduld.
Ludwig
wir sind beide ungefähr gleich lang bei w.w.w.
Ups, sorry, ich ging grad deshalb davon aus daß du weißt wie das geht.
Nimm fileupload, genauer Link steht in FAQ:2606
Gruß
Reinhard
http://www.file-upload.net/download-4084331/GTS.xlsx…
Die Musik spielt im Tabellenblatt DUMMY. Falls du übungshalber noch eine andere Kursnummer versuchen willst, gehe zum Blatt KuL.
Der Rest ist derzeit Baustelle.
Tausend Dank und Gruß,
Ludwig
Hallo Ludwig,
Die Musik spielt im Tabellenblatt DUMMY.
Matrisformel fand ich noch keine.
Eine Vba-Lösung habe ich.
Alt+F11, Einfügen Modul, kopier da nachfolgenden Code rein.
Schließe dann den Editor.
In LM7 vom Blatt Dummy trägst du ein:
=LP$2&Xte(LL$7:LL$36;ZEILE(A1))
und kopierst das bis LM36
Function Xte(Bereich As Range, Nummer As Integer) As String
Dim S As Range, Anz As Long, firstAddress As String
With Bereich
Set S = .Find("\*", , xlValues)
If Not S Is Nothing Then
firstAddress = S.Address
Do
Anz = Anz + 1
If Anz = Nummer Then Exit Do
Set S = .Find("\*", S, xlValues)
If S Is Nothing Then Exit Do
Loop While S.Address firstAddress
End If
End With
If Not S Is Nothing And Anz = Nummer Then Xte = S.Value
End Function
Falls du übungshalber
noch eine andere Kursnummer versuchen willst, gehe zum Blatt
KuL.
Mal schauen
Gruß
Reinhard
Lieber Reinhard,
ja, aber …
Ja, es funktioniert tadellos und ist auch an andere Stellen übertragbar (wird dich nicht wundern ))) - also ganz herzlichen Dank.
Und jetzt das bittere Aber: Im Schulnetz sind alle Makros prinzipiell gesperrt, also auch VBA. Ich werd es zwar morgen noch extra testen, bin mir aber fats sicher, dass es so ist.
Das heißt, eine Formellösung wäre nach wie vor (leider!!!) die bessere Variante. Ob wir (*grins* wg. dem ‚wir‘) das auch noch hinbekommen?
Nochmals herzlichen Dank für deine viele Mühe und die Bitte um Unterstützung, ob’s nicht doch auch ohne VBA geht.
Schöne Grüße von der Donau an den Main,
Ludwig
Hallo Ludwig,
Und jetzt das bittere Aber: Im Schulnetz sind alle Makros
prinzipiell gesperrt, also auch VBA. Ich werd es zwar morgen
noch extra testen, bin mir aber fats sicher, dass es so ist.
das interessiert mich. Wollte ich schon immer mal fragen wenn jmd. sagt in seinem Firmennetzt „gingen“ keine Makros.
Ist das ein Verbot von „oben“, also Makros würden schon laufen aber wer das macht muß mit Kündigung rechnen?
Oder funktionieren da generell keine Makros?
Das heißt, eine Formellösung wäre nach wie vor (leider!!!) die
bessere Variante. Ob wir (*grins* wg. dem ‚wir‘) das auch noch
hinbekommen?
Klar kriegen wir das hin
Eine Formel-Lösung mit Hilfsspalte kann ich dir stricken.
Die mistige Matrixformel wehrt sich leider NOCH dagegen von mir umgestrickt zu werden, mal schauen wer zäher ist *gg*
Und, es gibt nicht immer eine bessere oder schlechtere Lösung.
Sehr wichtig ist ein ganz anderer Aspekt.
Wenn du nicht in der Lage bist, ohne 3-Monatskurs, meine Vba-Lösung auf ggfs. andere Voraussetzungen umzuschreiben so bist du auf externe Hilfe angewiesen bei Änderungen.
Das gilt gleichermaßen für diese Matrixformel. Du siehst ja deine und meine Schwierigkeiten die Matrixformel anzupassen.
Eine Lösung mit Hilfsspalten ist damit verglichen sehr einfach zu verstehen wenn du normale Excel-Funktionen kennst.
D.h. du brauchst dann keine externe Hilfe um was abzuändern.
Ein wichtiger Aspekt *find*
Gruß
Reinhard
Tricksen, tarnen, täuschen
Das heißt, eine Formellösung wäre nach wie vor (leider!!!) die
bessere Variante. Ob wir (*grins* wg. dem ‚wir‘) das auch noch
hinbekommen?
Hallo Ludwig,
wie wäre es du markierst LM7:LM36, dann erstellst du eine bed. Formatierung mit der Formel:
=ISTFEHLER(LM7)
und weist dieser Regel die Schriftfarbe weiß zu?
Gruß
Reinhard
Hallo Reinhard,
an diese Lösung habe ich vor längerem auch gedacht. Aber dann bleiben ja die Leerzeilen erhalten.
Mehr im nächsten Posting.
Ludwig