Eine Liebeserklärung an MS Excel
Amüsant.
Werden in PowerQuery Spalten mit einem Verkettungsoperator „&“ zusammengefügt und befindet sich in einer der Zellen der Wert null, dann ergibt Inhalt & null -> null:
Nur wenn alle Zellen mit Text gefüllt waren, wird das Ergebnis angezeigt.
Anders dagegen der Assistent „Spalten zusammenführen“.
Hier macht der Wert null keine Probleme.
*ups*
Die Aufgabe lautet: ich möchte die Anzahl der Zeilen wissen, in denen in mindestens einer der vier Spalten etwas steht. Oder Anzahl der Spalten, bei denen Wert Spalte A <> „“ oder Wert Spalte B <> „“ oder Wert Spalte C <> „“ oder Wert Spalte D <> „“.
Ich probiere. Mit & werden die vier Zellen verkettet:
Hier:
=tblMusketiere[D’Artagnan]&tblMusketiere[Aramis]&tblMusketiere[Porthos]&tblMusketiere[Athos]Während die Funktion
=ANZAHLLEEREZELLEN(G2#)
die Zahl 3 ergibt, liefert
=ANZAHL2(G2#)die Zahl 26. Die vermeintlich leeren Zellen werden mitgezählt.
Die Formel darf folglich NICHT lauten:
=ANZAHL2(tblMusketiere[D’Artagnan]&tblMusketiere[Aramis]&tblMusketiere[Porthos]&tblMusketiere[Athos])Die Formel
=G6=""liefert WAHR, die Formel
=ISTLEER(G6)liefert FALSCH.
Eine Lösung könnte so aussehen: die Funktion FILTER liefert die korrekte Liste, beispielsweise so:
=FILTER(tblMusketiere[Datum];(tblMusketiere[D’Artagnan]<>"")+(tblMusketiere[Aramis]<>"")+
(tblMusketiere[Porthos]<>"")+(tblMusketiere[Athos]<>""))
Und die kann man zählen:
=ANZAHL2(FILTER(tblMusketiere[Datum];(tblMusketiere[D’Artagnan]<>"")+(tblMusketiere[Aramis]<>"")+
(tblMusketiere[Porthos]<>"")+(tblMusketiere[Athos]<>"")))Letzte Woche in der PowerBI-Schulung. Wir schauen uns einige der DAX-Funktionen an. Beispielsweise CONCATENATE.
Es folgt die Frage, warum man mit der Excel-Funktion VERKETTEN ( CONCATENATE) mehrere Texte verketten kann:
Dagegen in DAX:
Zwei Texte sind kein Problem. Jedoch bei drei Texten:
=CONCATENATE(tbl_ottos_mops[ottos];" ";tbl_ottos_mops[mops])
Also verketten wir in DAX mehrere Texte mit dem et-Zeichen „&“. Das funktioniert!
=tbl_ottos_mops[ottos]&" "&tbl_ottos_mops[mops]&" "&tbl_ottos_mops[Spalte1]&" "&tbl_ottos_mops[Spalte2]
Hallo, i ch benötige kurze Hilfe, ich habe hier ein Makro, noch von meinem Vorgänger, das wohl in einer Excel Liste eine Zeile entfernt und wo anders
dann wieder einsetzen kann. Nun ist es aber so, das ich Zeilen habe, die einen Zeilenumbruch in der Zelle haben, und dann immer eine Fehlermeldung 1004 kommt. Können Sie mir hier BITTE helfen????
Danke
Jörg
Hallo Jörg,
so groß und schwierig kann das ja wohl nicht sein – wenn das Makro vorher gelaufen ist …
schick es mir doch mal, bitte, dann schreibe ich dir etwas dazu.
Liebe Grüße Rene
Hallo Rene,
hier mal so….
In einer Excel Zelle habe ich einen Textumbruch, mit sehr viel Text.
Den kann ich zwar über den Makro „löschen“, aber dann nciht
mehr an einer anderen Stelle einfügen…..
BITTE Um HILFE! DANKE
Sub FahrtAuswaehlenEinfach()
Dim aDlg As DialogSheet
Dim aRow As Range
Set aDlg = Sheets("Fahrtauswahl")
i = 1
aDlg.ListBoxes("Fahrten").RemoveAllItems
Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
astring = astring + " - " + Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
astring = astring + " " + Sheets("Tabelle2").Cells(i, 4).Value
astring = astring + " --- " + Sheets("Tabelle2").Cells(i, 8).Value
astring = astring + " / " + Sheets("Tabelle2").Cells(i, 11).Value
aDlg.ListBoxes("Fahrten").AddItem astring
i = i + 1
Loop
If aDlg.Show = True Then
i = aDlg.ListBoxes("Fahrten").ListIndex
Sheets("Tabelle2").Rows(i).EntireRow.Cut
Sheets("Linienpläne - quer").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
End If
End SubZuerst musst ich schlucken und mich auf die Suche machen. Was bitte ist ein DialogSheet? Ich werde fündig: in Excel 4.0 konnte man ein Dialogblatt einfügen. Und darauf Steuerelemente platzieren. Und die verwenden so etwas immer noch im Jahre 2020?
Egal. Meine Vermutung liegt beim Operator „+“. Wenn in den Zellen Zahlen stehen, hat VBA ein Problem beim Verketten von
astring = astring + " " + Sheets("Tabelle2").Cells(i, 4).ValueDas ist aber nicht die Lösung. Ich bitte um die Daten. Der Zeilenumbruch ist nicht das Problem, sondern:
Der Text ist zu lang! Excel lässt mehr als 8.000 Zeichen / Zelle zu, die ListBox leider nur 255
Also schnell den Code geändert:
Sub FahrtAuswaehlen()
Dim aDlg As DialogSheet
Dim aRow As Range
Set aDlg = Sheets("Fahrtauswahl")
i = 1
aDlg.ListBoxes("Fahrten").RemoveAllItems
Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
astring = astring & " - " & Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
astring = astring & " " & Left(Sheets("Tabelle2").Cells(i, 4).Value, 200)
astring = astring & " --- " & Sheets("Tabelle2").Cells(i, 11).Value
aDlg.ListBoxes("Fahrten").AddItem astring
i = i + 1
Loop
If aDlg.Show = True Then
For i = aDlg.ListBoxes("Fahrten").ListCount To 1 Step -1
If aDlg.ListBoxes("Fahrten").Selected(i) Then
Sheets("Tabelle2").Rows(i).EntireRow.Cut
Sheets("Linienpläne - quer").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
End If
Next
End If
End SubDie Antwort:
Hallo Rene,
JA, das wars wohl! Klappt SUPER!!!
TAusend DANK!!!
Wie kann ich mich revangieren?
D A N K E ! ! !
Gruß
Jörg
Das Telefon klingelt.
„Hallo René, ich bin’s: Angelika. Hast du mal nen Moment Zeit?“
Kennt ihr Gespräche, die so beginnen? „Hast du mal einen Moment Zeit?“ Das dauert normalerweise sehr, sehr lange. Egal – ich nehme mir die Zeit für Angelika.
„Jep, schieß los – was gibt’s?“
„Ach mein Excel nervt wieder! Ich bereite gerade eine Schulung vor. Excel zeigt mir immer nur die Formeln an, nicht die Ergebnisse. Ich bin völlig verzweifelt; ich weiß nicht, was ich machen soll!“
„Hast du die Formel-Anzeige eingeschaltet? – Formeln / Formeln anzeigen.“
„Ne, da habe ich schon geschaut.“
„Wie sind denn die Zellen formatiert? Wie lautet das Zahlenformat“
„Na – als Standard sind sie formatiert!“
„Mach mal einen Doppelklick auf die Zelle mit der Formel! Und dann [Enter]“
„Ach – jetzt geht es! Was war das?“
„Ich frag mal zurück: was hast du gemacht?“
„Also von vorne. Ich bereite gerade die Excelschulung für nächste Woche vor. Ich habe eine Liste mit Telefonnummern:
Ich füge Spalten ein und zeigen den Teilnehmern den Assistenten Daten / Text in Spalten.
Dann will ich die Teile wieder zusammenbauen. Und: egal, was ich verwendet habe: das &-Zeichen, VERKETTEN, TEXTKETTE, TEXTVERKETTEN … immer die Formel statt das Ergebnis!“
„Klar! Du hast die Telefonnummern als Text formatiert.“ Durch das Spalten Einfügen übernimmst du das Textformat. Und deshalb rechnet die Formel nicht mehr, sondern wird als Text eingefügt.“
„René – danke! Ich wusste doch, dass Excel nervt. Und dass du mir helfen kannst!“
Geht das nicht?
Ich habe eine Liste. darin befinden sich in einer Spalte Vornamen, in einer anderen Nachnamen. Das Verketten mit
=D2&E2
klappt hervorragend. Aber – darf ich denn kein Leereichen zwischen Vor- und Nachname schreiben. Ohne – das wäre schon ganz schön doof.
Verketten
Die Antwort: Natürlich geht es. Aber Sie dürfen nicht das Leerzeichen direkt eingeben, sondern müssen es als Leerzeichen kennzeichnen. Also so
=D2&“ „&E2
Dann funktioniert es. Und: Vergessen Sie nicht das zweite Verkettungszeichen „&“!
Übrigens: Ich finde es erstaunlich, dass Excel das Leerzeichen akzeptiert – eigentlich sind Leerzeichen in Formeln verboten und werden mit einer Fehlermeldung quittiert!