Gestern war ich sehr verblüfft und erstaunt. Ich habe eine Excelmappe mit einer intelligenten (strukturierten, dynamischen, formatierten) Tabelle. In einer ANDEREN Datei greife ich darauf zu – per SVERWEIS, INDEX und VERGLEICH oder XVERWEIS:
Beide Dateien, die sich im gleichen Ordner befinden, werden geschlossen. Nur die Zieldatei wird geöffnet. Das Ergebnis: Fehlermeldungen bei allen drei Formeln:
Ich ersetze die Bezüge, also
Datenquelle.xlsx!tbl_Schlumpf[Name]
durch
[Datenquelle.xlsx]Quelle!$A:$A
Das ist nicht schön! Aber – es funktioniert! Hat Microsoft vergessen DAS bei intelligenten Tabellen zu implementieren? DAS ist ja ein Schritt zurück! DAS will ich eigentlich nicht (mehr). Immerhin: es funktioniert. So kann ich die Zieldatei öffnen, die Werte werden aktuell angezeigt, ohne dass die Quelldatei offen sein muss.
Danke an Christa für den Hinweis, dass man die Verknüpfung über PowerQuery organisieren kann.
Und: vielen Dank an den Hinweis von Mourad Louha: DAS IST GEWOLLT!
Ich verwende beim Programmieren und auch oft in Excel gerne das Pipe-Zeichen (senkrechter Strich) „|“ ([Alt Gr] + [<] – auf der Schweizer Tastatur [Alt Gr] + [7]; [ALT] + 1 2 4) als Trennzeichen. Dabei bin ich ziemlich sicher, dass es in einem „normalen“ Text nicht vorkommt. Also beispielsweise:
Umso erstaunter war ich über das Ergebnis der folgenden Formel:
Warum liefert die Formel
=ZÄHLENWENNS(Tabelle2[@[Jan 20]:[Dez 20]];"I")
den Wert 6. Ich habe eine Weile überlegen müssen, bis ich verstanden habe, dass der Buchstabe „I“ nicht „|“ ist – bei einigen Schriften sehe die beiden Zeichen sehr ähnlich aus. Vielleicht sollten wir – analog dem Türkischen – auch ein Punkt auf das große I setzen: İ.
Die Originaldatenquelle ändert sich – sie wird kleiner. Die Verbindung wird aktualisiert:
Die Folge: Die Formel wird angepasst, beispielsweise in:
=KKLEINSTE($F$2:$F$15;ZEILE(A1))
Fehlermeldungen sind die Folge.
Ändert sich die Liste erneut und wird nun länger, werde diese Bezugsfehler natürlich nicht korrigiert …
Fehler in der Berechnung sind die Folge.
Heißt: Wenn schon (intelligente/formatierte/dynamische) Tabellen – dann bitte die Bezüge auf diese Tabellen in Tabellenschreibweise und nicht in Bezugsschreibweise! Sonst gibt es Ärger!
Seit ein paar Tagen habe ich in Microsoft 365 die neue Funktion LET. Damit kann man Formeln unter einem Namen ablegen, um sie (mehrmals) wieder zu verwenden. Beispielsweise so:
=ZELLE(„dateiname“)
oder besser:
=ZELLE(„filename“)
liefert den Namen und Speicherort der aktuellen Datei:
Benötigt man nun den Pfad, kann man das Zeichen „[“ suchen (oder finden):
=SUCHEN(„[„;D3)
liefert in unserem Beispiel 19. Bis ein Zeichen vor diesem Zeichen kann von links der Text herausgelöst werden:
Darin wird nun zwei Mal die Funktion ZELLE(„dateiname“) verwendet. Man kann sie an einen Namen in der Funktion LET übergeben, beispielsweise an „Dateiname“:
Das erhöht allerdings nicht gerade die Lesbarkeit!
Fazit: LET ist sicherlich eine nützliche und praktische Funktion. Allerdings kommt sie wohl erst bei sehr langen Formeln zum Einsatz. Und auch nur dann wenn Formelteile sich mehrmals wiederholen. Legt man diese Teil unter einem (langen) sprechenden Namen ab, wird das Ergebnis weder kürzer noch gut lesbar. Ich fürchte, sie wird wohl in den wenigsten Berechnungen in Excel in Microsoft 365 Einzug finden …
bin gerade ein wenig verwirrt aufgrund der Formel Nettoarbeitstage.INTL, vermutlich liegts bei mir und nicht an der Formel.
Ich nehme hier einfach mal den 12.10.2020 als Ausgangs- und den 19.10.2020 als Enddatum. Die Formel in E2 ist =NETTOARBEITSTAGE.INTL(A2;B2;11), mit dem Parameter 11 möchte ich die Sonntage ausklammern.
In D2 steht lediglich =TAGE(B2;A2).
Müssten sich die beiden Werte nicht eigentlich
unterscheiden, da ein Sonntag dazwischen liegt?
Wenn ich das Ausgangsdatum weiter in die Vergangenheit setze, dann gibt es eine größere Diskrepanz zwischen den beiden Formelergebnissen, z.B. beim 01.09.2020
Was mache ich denn falsch, weißt du das?
Viele Grüße, Dominic
Moin Dominic,
schau mal:
TAGE rechnet wie die Differenz wie DATEDIF: Ende – Anfang. Klar: 19 – 12 = 7.
NETTOARBEITSTAGE und NETTOSARBEITSTAGE.INTL ist die Anzahl
der Arbeitstage in einem Datumsbereich außer Sa/So (NETTOARBEITSTAGE) oder
außer dem gewählten Tag / den gewählten Tagen.
Heißt: wenn Anfang und Ende gleich sind liefert TAGE immer
0, NETTOARBEITSTAGE meistens 1 – eben: ein Tag liegt in diesem Zeitraum.
Für deinen größeren Zeitraum ist das auch korrekt:
vielleicht ist das so ein Ding, was du direkt weißt. Ich öffne via Makro eine Datei. In dieser Datei befinden sich Verknüpfungen zu externen Mappen, die zwar kein Mensch braucht, aber die nun mal drin sind, weil die Dateien immer vom Kunden kommen und der damit wer weiß was macht. 😉
Beim öffnen erscheint immer dieser Hinweis und das Makro läuft natürlich nicht weiter:
Kriegt man das irgendwie weg? Bzw. gibt es einen Befehlt der
automatisch „Aktualisieren“ oder „Nicht aktualisieren“ auswählt?
Application.DisplayAlerts = False greift hier nicht.
Danke dir und viele Grüße,
Dominic
####
Hat sich schon erledigt – UpdateLinks:=0 nach dem „Open“-Befehl. Manchmal ist der Makro-Rekorder schon ganz praktisch.
####
Hallo Dominic,
ich muss nur ein bisschen warten – und schon lösen die Leute
alleine ihre Probleme.
Ich hätte es trotzdem gewusst.
Hintergrund: Die IT einer großen Behörde beschließt im Frühjahr 2018 das Laufwerk P von allen Anwendern zu löschen. Ab jetzt soll es nur noch Q geben. Jeder Anwender soll seine Dateien von P nach Q kopieren, dabei anschauen, ob er die Dateien noch braucht …
Nach fünf Monaten haben sie festgestellt: ups – einige
Tausend Dateien haben ja Verknüpfungen auf andere Dateien auf Laufwerk P. Dumm!
Die Verknüpfungen funktionieren nicht mehr.
Ich habe ihnen ein VBA-Tool geschrieben:
* liste alle (Excel-)Dateien auf
* Anwender wählt einen Ordner und legt fest welcher Ordner durch welchen ersetzt werden all. Bspw.: P:\Eigene Dateien\Controlling\Excel\2017 durch Q:\Eigene Dateien\ Controlling\Excel\2017
* öffne alle Excelmappen in diesem Ordner (und Unterordner – kann ausgewählt werden)
* prüfe, ob Verknüpfungen drin sind (in Tabellenblättern,
Namen, Bedingten Formatierungen, Datenprüfungen) und putze
* speichere und schließe
Problem beim Öffnen:
* Verknüpfungen (wie du beschreibst)
* AutoOpen-Makros
* geschützte Dateien oder Blätter (mit oder ohne Kennwort)
uff!
Einige Programmierstunden, einige Nachbesserungen, … am Ende
habe ich nie mehr etwas gehört … wahrscheinlich konnten sie alle (?) Dateien
öffnen und putzen.
gekommen und habe folgende (aus meiner Sicht) falsche
Aussage gefunden:
zuerst der betreffende Textabschnitt:
———–
Text, leerer Text oder kein Text
Ähnlich wie bei Zahlen geht Excel bei Texten vor. Eine
Zelle kann leer sein, kann Text oder eine leere Zeichenkette beinhalten.
Letzteres kann das Ergebnis einer Funktion sein oder auch direkt eingegeben
werden:
=““
Die folgenden drei Abfragen liefern dann WAHR als
Ergebnis:
=WENN(ISTLEER(A1);…
=WENN(A1=““;…
=WENN(ISTTEXT(A1);…
———–
Wenn ich aber einer Zelle A1 den Wert „“ (also
leeren String) zugewiesen habe, liefert mir die Formel
=WENN(ISTLEER(A1);…
leider nicht den Wert wahr, sondern falsch. Die Zelle
wird nicht als „leer“ erkannt.
Hiermit habe ich übrigens genau ein Problem. Ich habe
nämlich das „ISTLEER“ oft benutzt und zu spät bemerkt, daß es nicht
wie erwartet funktioniert. Jetzt versuche ich, die Formeln zu ändern (z.B.
durch Abfrage auf Länge = 0) und die vorhandenen „“ aus Wertfeldern
zu eliminieren. Leider habe ich für beides bisher noch keine ganz einfache,
schnelle und sichere Vorgehensweise gefunden.
Mit freundlichen Grüßen
Michael Richter
Hallo Herr Richter,
ich freue mich
immer sehr, wenn Leserinnen und Leser auf meine Artikel reagieren – Kritik
äußern, Dinge korrigieren, die ich falsch behauptet habe oder Lob äußern.
Sie haben recht
– die Artikel, die Sie unter „über“ und „schade“ finden,
sind recht alt und sollten überarbeitet werden. Umgekehrt: wenn Sie
„ISTLEER“ in das Suchfeld eintragen, finden Sie weitere Artikel von
mir zu diesem Thema.
Liebe Grüße
Rene Martin
Hallo Herr Martin,
Und jetzt noch Lob:
Ihre Antwort auf meine Zuschrift find ich
vorbildlich. Außerdem freue ich mich
immer, wenn jemand im Internet nützliche Informationen zur Verfügung stellt,
und Ihre Seiten sehen „recht gut
gemacht“ aus (ich habe sie allerdings bisher noch nicht näher angesehen,
daher nur eine Beurteilung des Eindrucks auf den ersten Blick).
Vorn vorne: die Excel-Funktion HÄUFIGKEIT ist eine Matrixfunktion. Mit ihrer Hilfe (ebenso wie mit ZÄHLENWENN oder SUMMENPRODUKT) kann man die Häufigkeit von Daten in einer Liste bestimmen, beispielsweise, wie oft Notenwerte vorhanden sind. Dabei muss Die Funktion als Matrixfunktion verwendet werden, das heißt mit [Umschalt] + [Strg] + [Enter] beendet werden:
Das funktioniert bei exakt vorkommenden Werten, aber auch bei „Zwischenwerten“ – von – bis:
Man kann die Funktion aber auch als eine Funktion verwenden und runterziehen – dann werden die Werte kumuliert:
Nein – so konnte man das früher machen – vor den Spillfunktionen (den Arrayfunktionen SEQUENZ, SORTIEREN, EINDEUTIG, …) machen. Seit Microsoft diese Funktionen und diese Arbeitstechnik eingeführt hat, erzeugt HÄUFIGKEIT beim Markieren eines Wertes zwei Ergebnisse: Wie oft taucht dieser Wert auf und wie viele andere Werte sind vorhanden.
Und heute? – Man muss HÄUFIGKEIT mit einem @ entwerten, dann hat man wieder die gleiche Funktionalität wie früher:
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!“
Man schreibe in eine Zelle den Text ‚WAHR. Das Ergebnis wird linksbündig als Text angezeigt. Das Gleiche passiert, wenn man eine Zelle als Text formatiert und anschließend mit den Buchstaben WAHR füllt.
Soweit so gut. Die Überprüfung mit der Funktion ISTTEXT bestätigt, dass ein Text in der Zelle steht; ISTLOG verneint die Existenz eines booleschen Wertes (WAHR oder FALSCH). Auch die Überprüfung mit =Zelle=WAHR und =ZELLE=“WAHR“, verneint das erste und bestätigt das zweite:
Irritierend ist nun, dass die Funktion ZÄHLENWENN 0 liefert, wenn man den TEXT „WAHR“ zählt:
ich hatte schon lange kein Problem mehr mit Excel,
sicherlich auch dank Ihrer Kurse, die ich mir in LINDEDIN Learning immer wieder
einmal ansehe.
Heute nun habe ich ein Problem, bei dem Sie mir vielleicht
helfen können. Wenn es allerdings nicht so nebenher geht, dann können Sie mir
dies gerne mitteilen, dann muss ich weiter forschen.
Ich möchte mir hervorgehobene Zellen zählen lassen, genau
genommen, möchte ich beim Wahlverfahren D’Hondt sofort sehen, wie viele Sitze
hat Liste 1, 2…
Möglicherweise geht es auch nicht mit dem Zählen der
hervorgehobenen Zellen sondern anders.
Ich habe es mit SVERWEIS versucht, was leider dann ab der 5.
Zeile ein „NV“ brachte – mir leider unverständlich. Mit INDEX und
VERGLEICH komme ich auch nicht weiter, weil ich ja keine genaue Zeilen oder
Spalenzahl angeben kann. Diese kann ja – je nach Höchstwert – variieren.
Ich sende Ihnen die Datei einmal zu und freue mich auf Ihre
Antwort, kann aber auch verstehen, wenn Sie schreiben: Kann ich Ihnen nun
leider nicht mitteilen, dauert zu lange…
Trotzdem danke für Ihr offenes Ohr.
Ich wünsche Ihnen noch einen schönen Tag.
Hallo Frau P.,
das ist eine
hübsche Fingerübung.
Ein paar
Anmerkungen:
1. man kann
Farben in Excel nicht zählen. Und wenn ich jetzt von Ihnen die Funktion ZELLE
höre – mit Einschränkungen ja. Aber – diese Funktion reagiert nicht auf
Formatänderungen. Und: zeigt auch nicht alle Formate an!
2. Ich würde es klassisch mit SUMMEWENN lösen. SVERWEIS, INDEX & co greifen auf mehrere Spalten zu. Mit der neuen Funktion XVERWEIS kann man es auch lösen – aber ich weiß nicht, ob sie diese schon haben. Werfen Sie einen Blick auf meine Lösung in Spalten M:O, bzw. zusammengefasst in Spalte Q.
3. Für welches Beispiel/Land verwenden Sie das? Ich lese bei wikipedia, dass noch die Schweiz, Spanien, Portugal, Belgien, Polen und Finnland dieses Verfahren verwenden. Sie wissen, dass dies in Deutschland 1985 durch das Hare-Niemeyer-Verfahren abgelöst wurde. Ich hätte es nämlich fälschlicherweise in meinem Excel-Formelbuch erläutert … und dann gemerkt, das wir so (in Deutschland) gar nicht mehr rechnen.
zusammengefasst
Hallo Herr Martin,
herzlichen Dank für Ihre schnelle Rückmeldung und Ihren
Vorschlag der Berechnung.
Ich habe es jetzt noch einmal nachgebaut und verstanden,
obwohl ich niemals auf die Formel gekommen wäre.
Zu Nr. 3:
Ich weiß nur, dass die Hochschule Furtwangen den Vorschlag
unserer Justiziarin aus Stuttgart aufgegriffen hat und diesen in ihrer
Wahlordnung nun festgelegt hat. Wir haben bisher mit Hare-Niemeyer gewählt.
Aber leider soll es auch bei D’Hondt bleiben, obwohl wir eine Hochschule mit
drei Standorten sind und unserer kleinster Standort mit diesem Wahlverfahren
ernste Schwierigkeiten bekommen könnte. Ich hatte das (als Wahlleitung)
angemerkt, es bleibt trotzdem bei D’Hondt.
Ich wünsche Ihnen noch einen schönen Tag und weiterhin viel
Erfolg bei Ihrer Arbeit.
D1353 M1TT31LUNG Z31GT D1R, ZU W3LCH3N GRO554RT1G3N L315TUNG3N UN53R G3H1RN F43H1G 15T! 4M 4NF4NG W4R 35 51CH3R NOCH 5CHW3R, D45 ZU L353N, 483R M1TTL3W31L3 K4NN5T DU D45 W4HR5CH31NL1ICH 5CHON G4NZ GUT L353N, OHN3 D455 35 D1CH W1RKL1CH 4N5TR3NGT. D45 L315T3T D31N G3H1RN M1T 531N3R 3NORM3N L3RNF43HIGKEIT. 8331NDRUCK3ND, OD3R? DU D4RF5T D45 G3RN3 KOP13R3N, W3NN DU 4UCH 4ND3R3 D4M1T 83G315T3RN W1LL5T
Kann man / kann ich selbst solche Texte produzieren? Mit Excel natürlich! Klar die Funktion WECHSELN hilft dabei:
Ich trage einen Text in die Zelle A3 ein. Die Funktion GROSS verwandelt ihn in Großbuchstaben. In den Zelle C1:G2 stehen die Werte E, I, S, B und A, die durch 3, 1, 5, 8 und 4 ersetzt werden. In C3 befindet sich die Formel
Hallo Rene, ich mache zurzeit Ihre Ecel Schulung Tipps & Tricks, bis jetzt bin ich begeistert, vielen Dank. Eventuell können Sie mir mit einer Formel helfen? Ich habe zwei Tabellenblätter und möchte Emailadresse, die in Blatt 1 in Spalten aufgelistet sind, zu Blatt 2 einen automatischen Bezu herstellen, sodass dort in einer Zelle die EMailadressen automatisch erscheinen, wenn ich sie in Blatt 1 eingetippt habe. Ich danke bereits für Ihre Hilfe.
Hallo Anna, wenn sich der Cursor auf der Zelle befindet, wo die Email-Adresse stehen soll, genügt ein „=“, dann ein Klick auf das erste Tabellenblatt und ein zweiter Klick auf die Zelle, in der sich die Mailadresse befindet. Achtung: Nicht zurückklicken! Sondern mit [Enter] bestätigen! Dann steht in der Zielzelle beispielsweise =Tabelle1!C3 Hilft das? Oder denke ich zu einfach? schöne Grüße Rene Martin
Lieber Rene, danke für die supr schnelle Antwort. Es ist leider doch ein wenig komplizierter, aber ich denk für Sie ebenfalls problemlos zu lösen. Ich füge ein Beispieldokument ein zum besseren Verständnis. Aus Tabelle1 möchte ich automatische alle Email-adressen von Runde 1 in aufgelistet in ein Zelle im Tabellenblatt ‚Mailverteiler‘ auflisten. Wie lautet die Formel des Zellbezugs? Besten Dank für Ihre Hilfe.
Hallo Anna, hast du eine neueres Excel? Und dort die Funktion TEXTVERKETTEN? Damit geht es LG :: Rene
Aber kann diesen
Bug gerne noch einmal veröffentlichen.
Andererseits – ich bin leider kein MVP und habe auch keinen direkten Kontakt zu Microsoft. Es gibt eine Seite, über die man Fehler melden kann – allerdings bin ich bei der Anmeldung gescheitert …
Hallo Herr
Martin,
ich danke Ihnen
für Ihre schnelle Antwort.
Nun fürchte ich,
am nächsten Wochenende ihre komplette Web-Seite lesen zu müssen.
Die ersten dunklen Wolken am Horizont der neuen Funktion XVERWEIS.
Wenn die Funktion nicht nur einen Wert aus einer Spalte, sondern aus mehreren Spalten zurückgibt, kann man die Funktion leider nicht mit einem Doppelklick herunterziehen.
Dass hiner AutoSum die Funktion SUM steckt, verstanden die Teilnehmer. Aber warum erzeugt ein Klick auf die Schaltfläche „Count Numbers“ die Funktion COUNT?
Excelschulung. Wir diskutieren über den Unterschied zwischen Zellen formatieren (Nachkommastellen „wegformatieren“) und der Funktion RUNDEN. Ein Teilnehmer beschwert sich, dass dann die Summe der gerundeten Werte möglicherweise nicht mehr mit der urprünglichen Summe übereinstimmt. Ich zeige ihm ein Diagramm:
Manchmal ist es irritierend. Aufgabe in der Excelschulung: Erhöhen Sie den Jahresbeitrag der Blue-Mitgleider um 5 Euro, der anderen Mitglieder um 10 Euro. Ein Teilnehmer arbeitet mit einer Hilfsspalte:
=WENN(J2=“Blue“;5;10)
Ich sage, dass dieses Vorgehen völlig okay sei, dass er aber auch direkt in der WENN-Funktion rechnen könne. Ich korrigiere und klicke auf die Zelle J2:
=WENN(J2=“Blue“;H25;10)
Der Teilnehmer schaut mich irritiert an. Ich füge ein Pluszeichen ein – und schon wird klar, wie gerechnet wurde:
Der zweite Teil muss natürlich noch eingefügt werden:
In Excelschulungen erzähle ich immer, wenn ich den Assistenten „Daten / Text in Spalten“ vorstelle, dass es in Excel keine Tabulatoren gibt – man kann die Grundeinstellung (die beim Import von Textdateien verwendet wird) ruhig stehenlassen:
Ich werde eines Besseren belehrt: Wenn Sie ein Worddokument oder einen Mailtext aus Outlook nach Excel kopieren, werden die Tabstopps durch Spalten getrennt:
Ein Worddokument mit Tabulatoren… in Excel
Wenn Sie dagegen Texte IN Zellen kopieren (das heißt: die Zelle mit [F2] editieren) und dann den kopierten Text einfügen, stehen Tabstopps in Zellen. Merkwürdigerweise sieht man sie nicht, wenn der Cursor AUF der Zelle steht – lediglich IN der Zelle werden sie angezeigt:
Der Assistent „Daten / Text in Spalten“ interpretiert die Tabstopps als Leerzeichen!?!
Daten / Text in Spalten mit Tabstopps Daten / Text in Spalten mit Leerzeichen
Die Funktion CODE liefert den Wert 32 – den gleichen wie Leerzeichen!?!
Wenn man in den Ersetzen-Dialog einen Tabstopp hineinkopiert und durch ein Leerzeichen ersetzen lässt, funktioniert dies hervorragend.
Fazit: Finger weg von Tabstopps in Exceltabellen! Excel behandelt diese Zeichen nicht konsequent als eigene Zeichen – eine Unterscheidung durch Leerzeichen ist nicht an allen Stellen durchgeführt. Und wenn ein Anwender Texte in die Zellen eingefügt hat – dann bleibt nur noch der Ersetzen-Assistent oder die WECHSELN-Funktion.
Ein Dankeschön an Mourad Louha für seinen Vortrag beim Excelstammtisch Hessen zum Thema Internationalisierung bei Excel und worauf geachtet werden muss, wenn Dateien von einer Sprachregion in eine andere geschickt werden.
Amüsiert habe ich mich über seine Forlie „Kuriositäten“, in welcher er amüsante Dinge bei der Übersetzung einiger Funktionen zeigte: die längste Excelformel (AVRUND.GJELDENDE.MULTIPLUM.OPP.MATEMATISK im Norwegischen), SE.NON.DISP., die einen Punkt am Ende hat, CURRENCY – die Übersetzung von DOLLAR im Griechischen …
Ein Dankeschön an Mourad Louha, der mir seine Präsentzation zur Verfügung gestellt hat.
Heute in der Excelschulung. Ich zeige die Datenüberprüfung. Eine Artikelliste. In die Mengenspalte darf nur eine ganze Zahl eingetragen werden. Ein Teilnehmer fragt, ob man die Eingabe auch so begrenzen kann, dass die Gesamtmenge < 100 sein muss. Klar – kein Problem: Benutzerdefiniert – =SUMME(D:D)<100
Klappt.
Und dann möchte der Teilnehmer, dass die Eingabe nur ganze Zahlen akzeptiert. Leider kann man keine zwei Datenüberprüfungen miteinander verketten.
Man müsste die Formel erweitern, beispielsweise so:
Danke an die Einladung zum Excelstammtisch in Hessen. Auch in Frankfurt – wie bei uns in München – eine geballte Ladung an Wissen, Freude über Excel, Staunen und Spaß.
Klasse war es.
Danke auch an Andreas Thehos, der sich mit dem gleichen Problem rumgeschlagen hat wie ich gestern. Nur: die Aufgabenstellung war eine andere.
Gegeben sei eine Namensliste. Sie ist Basis für eine Pivottabelle. Es erstaunt, dass ein Name zwei Mal auftaucht. Und nein: es sind keine Leerzeichen hinter dem Namen.
Die Ursache: auch hier hat sich ein geschütztes Leerzeichen (ein non-breaking space) mit dem CODE 160 zwischen den Vornamen und Nachnamen geschummelt. Wie passiert so etwas? Entweder wurden die Daten aus einer Internetseite entnommen – oder von einem Word-Dokument. DORT kann man mit der Tastenkombination [Umschalt] + [Strg] + [Leertaste].
Im ersten Moment erstaunlich; allerdings konsequent, korrekt und nützlich: sind mehrere Zellen miteinander verbunden, liefert die Funktion SEQUENZ die Fehlermeldung #ÜBERLAUF – das heißt: die Werte werden nciht über die verbundenen Zellen geschrieben.
Gibt es eine Obergrenze für die Funktion SEQUENZ? Kann ich damit ein Tabellenblatt vollschreiben? Die Antworten: Obergrenze – ja; alle Zellen füllen – nein!
Ich verstehe es nicht: In der Zelle G1 steht die Funktion ZUFALLSBEREICH(1;5) und liefert folglich eine ganze Zahl zwischen einschließlich 1 und 5. Die Funktion SEQUENZ in Zelle 1 baut so viele Zeilen (1 bis 5) und zwei Spalten auf.
Bei einigen Zahlenwerten der Funktion ZUFALLSBEREICH kommt es zu einer Fehlermeldung: ÜBERLAUF:
Auch das Editieren mit [F9] liefert nicht die Lösung:
Die Zeilennummerierung wurde eine Zeile zu hoch angesetzt – Zeile 1 darf nicht die Zeile neben den Köpfen sein, sondern muss eine Zeile tiefer beginnen. Dann würde es stimmen!
Liebe Microsoftis: die neue Funktion EINDEUTIG ist – ebenso wie die anderen fünf neuen ARRAY-Funktionen klasse! Wirklich, ehrlich: super-spitzen Klasse! Brauchbar ohne Ende! Aber, bitte, bitte: übersetzt die Parameter ins Deutsche. Wie bei den anderen fünf Funktionen!
Boah – muss ich mich wieder ärgern! Ich schaue mir gerade die beiden neuen Funktionen XVERWEIS und XVERGLEICH an (wirklich klasse!) und werfe einen Blick auf die Hilfeseite von Microsoft:
Das Beispiel 1 ist völlig falsch! Es wird keine Platzhaltersuche (4) verwendet, sondern eine exakte Übereinstimmung oder das nächst größere Element. Deshalb liefert 1 das Ergebnis 2! Der Parameterwert 4 würde #NV liefern, weil Gra? nicht vorhanden ist. Nur Gra?? oder Gra*. Boah! Das Boah geht weiter: Beispiel 2: Der Satz „Beachten Sie, dass diese Methode erfordert, dass Ihre Daten in absteigender Reihenfolge sortiert sind.“ Nein – das erfordert sie nicht – es wird der Wert darüber ODER darunter zurückgegeben. Beispiel 3 ist korrekt; bei Beispiel 4 hätte ich mir eine vollständige Übersetzung gewünscht: =XVERGLEICH(4;{5;4;3;2;1}) Nun ja!
manchmal freue ich mich auch über Excel. Kennt ihr das? Probleme, die nicht lösbar scheinen, finden doch eine Lösung.
Kennt ihr die neuen Array-Funktionen in Excel 365? Beispielsweise ZUFALLSMATRIX:
=ZUFALLSMATRIX(10;1;1;10;WAHR)
generiert zehn zufällige ganze Zahlen zwischen einschließlich 1 und 10. Okay.
=SUMME(ZUFALLSMATRIX(10;1;1;10;WAHR))
summiert zehn zufällige Zahlen und liefert ein Ergebnis zwischen 10 und 100. Okay.
Vor einer Weile wollte ein Kunde aus einer Liste von zirka 100.000
Werten 100 Werte zufällig herausgreifen und von diesen den Durchschnitt
berechnen. Mit einer Hilfsspalte ist das kein Problem. Jedoch scheinen
die Matrixformeln zu versagen, weil {…ZUFALLSBEREICH …} 100 Mal die
gleiche Zufallszahl liefert und nicht 100 verschiedene. Die Lösung für
dieses Problem liefert ZUFALLSMATRIX:
berechnet einen Durchschnitt für diese 100 zufällig gefundenen Werte.
[F9] zum Neuberechnen liefert einen anderen Wert. Markiert man einen
Teil der Formel, dann zeigt [F9], dass tatsächlich zufällige Werte
ermittelt wurden. Die Zahl 100 kann ausgelagert und erhöht werden. Je
mehr man sich 100000 nähert, umso mehr nähert sich der
Zufalls-Mittelwert dem echten Mittelwert.
Und wer sich nun fragt:
„wer braucht denn so etwas?“ – Das Teilchen heißt
Monte-Carlo-Simulation und wird in Mathematik, Physik, Finanzwesen, …
seit über 60 Jahren angewandt. Weite Infos – beispielsweise Wikipedia.
Und ich bin begeistert – Excel rechnet so wie ich will!
Ich lasse die Werte summieren. Ich lasse den Mittelwert berechnen.
Eine Teilnehmerin meldet sich und sagt, dass sie ein anderes Ergebnis habe:
Ich schaue nach – klar – sie hat die Formel:
=WENN(B5>20;B5*750;0)
Dadurch wird zwar die Summe gleich berechnet; MITTELWERT (und SUMME) übergehen den Text – bei der Zahl 0 wird jedoch die ANZAHL anders berechnet – deshalb das unterschiedliche Ergebnis beim MITTELWERT (=SUMME/ANZAHL).
Und schon wieder eine merkwürdige Darstellung bei verschachtelten Excelfunktionen im Funktionsassistenten. Merkwürdig … aber ich glaube, dass ich nicht Excel, sondern dem Beamer, der Grafikkarte, der Auflösung, … die Schuld geben muss …
Mit Lorenz Hölscher (http://www.software-dozent.de/) haben wir für unsere Exceltage (www.munich-office-group.de) 2019 einen hervorragenden Dozenten gefunden. Er hat über Themen wie sichere Datenqualität und Dateneingabe, Verbesserungen im VBA-Code referiert. Und er stellte die neuen Array-Funktionen vor, die in Excel nun Einzug gefunden haben.
Auch er kann sich manchmal freche Bemerkungen nicht verkneifen, wenn er fragt, warum in Excel die Funktion TEXT in VBA Format genannt wurde – warum HEUTE() in Access Datum() heißt … Und er machte Witze über Praktikanten, die so etwas implementiert haben – solche Witze machen seine Vorträge nicht nur lehrreich, sondern auch amüsant.
Die Frage ist interessant: Aus einer Geburtstagsliste sollen all diejenigen angezeigt werden, die in dieser (laufenden Kalender-)Woche Geburtstag haben. Eine kleine Fingerübung, oder:
Zuerst wird das Geburtsdatum in ein Datum des aktuellen Jahres „transformiert“. Dann wird von diesem Datum und vom aktuellen Tag die ISOKALENDERWOCHE berechnet. Und schließlich beides miteinander verglichen:
Schau dir mal die Schritt in
der angefügten Tabelle an
Viel Spaß mit KW und
Geburtstag
Rene
Lieber René,
vielen Dank. Ich hatte es gleich gestern Abend noch nachgetüfftelt und bin zum Ergebnis gekommen, allerdings hatte ich das Datum anders umgewandelt.
Da sah dann so aus: =WENN(ISOKALENDERWOCHE(TAG(A1)&“.“&MONAT(A1)&“.“&JAHR(HEUTE()))=ISOKALENDERWOCHE(HEUTE());“Happy Birthday“;““) Viele Grüße Wolfgang
Ist auch richtig, Wolfgang –
meine Lösung ist natürlich besser *lach*
Im Ernst: ich mag es nicht,
wenn du ein Datum (intern eine Zahl) in einen Text umwandelst und diese
implizit wieder in ein Datum konvertieren lässt. Bei sehr vielen Datensätzen
dauert es länger als meine Lösung, die ein Datum als Datum lässt.
Microsoft Excel stellt im Insider-Programm die neue Funktion XVERWEIS vor. Andreas Thehos stellt sie auf seinem Kanal vor. Dumm nur, dass diese Funktion nicht abwärtskompatibel ist; das heißt: wer eine ältere Version von Excel hat, sieht leider nur #NAME!
Etwas verblüfft war ich in der letzten Excelschulung. Ich löse mit den Teilnehmern folgendes Problem: Es werden in zwei verschiedenen Zellen zwei Monate ausgewählt und die Kosten von – bis werden berechnet. BERICH.VERSCHIEBEN eignet sich hervorragend zur Lösung dieses Problems.
Meine Lösung:
BEREICH.VERSCHIEBEN:
Beginne bei A1.
Suche E1 im Datumsbereich mit der Funktion VERGLEICH und wandere so viele Zeilen nach unten.
Wandere eine Spalte nach rechts.
Ermittle die Höhe des aufzuspannenden Bereichs als Differenz beider Werte Ende – Anfang, die mit VERGLEICH berechnet werden.
Die Breite des Bereichs ist eine Spalte.
Klappt. Ein Teilnehmer präsentiert eine andere Lösung, die er parallel entwickelte:
Mich irritiert der Doppelpunkt. Dann wird mir klar, wie der Teilnehmer gedacht und wie die Formel gearbeitet hat:
Mit =C3 wird eine Referenz auf die Zelle C3 gesetzt. Diese Formel liefert den Wert der Zelle C3. Also steht „C3“ für zweierlei: die Zelle C3 als Objekt, als Bezug, aber auch der Inhalt der Zelle C3.
Und genau so arbeitet seine Formel – Während „meine“ Funktion BEREICH.VERSCHIEBEN den Wert der Zelle (beziehungsweise die Werte der Zellen) zurückgibt, setzt er einen Bezug auf die erste und die letzte Zelle und spannt zwischen ihnen einen Bereich auf, dessen Werte summiert werden.
In der Schulung zeige ich eine Funktion (INDEX). Ich zeige sie zuerst mit dem Funktionsassistenten. Der erste Parameter verlangt eine Matrix, die sich auf dem anderen Tabellenblatt befinden. Die anderen beiden Parameter befinden sich auf dem gleichen Tabellenblatt wie die Funktion – hier: D2 und E2. Kein Problem:
Anschließend zeige ich das Gleiche, indem ich die Funktion tippe. Erster Parameter: anderes Tabellenblatt. Zweiter und dritter Parameter – ich muss zurück zum ursprünglichen Tabellenblatt. Excel notiert den Namen des Tabellenblattes (hier: Tabelle2!). „Ärgerlich“ findet ein Teilnehmer. Ich gebe ihm recht …
Den Mittelwert Teilnahme über alle Spalten Teilnahme, die
>0 bekomme ich wie am schicksten? Mittelwertwenn geht nur über einen
zusammenhängenden Bereich, zählenwenn auch,…
Am Ende ist die Tabelle so:
Hallo Florian,
ja, das ist
richtig: ZÄHLENWENN, MITTELWERTWENN kann keine getrennten Bereiche verarbeiten.
Der Grund:
Wie soll man
ZÄHLENWENN(A2:A5;C2:C5;“>0“)
verstehen?
Also muss du den
MITTELWERT „nachbauen“. Mittelwert ist ja Summe/Anzahl
Natürlich ist Excel besser als google Tabellen. Keine Frage. Dennoch ist erlaubt bei der Konkurrenz zu schauen, was diese Spreadsheets so alles können. Beispielsweise übersetzen mit der Funktion googletranslate:
So eine Funktion wünsche ich mir in Excel:
Okay – ich gestehe: Perfekt übersetzt diese Funktion nicht. Aber immerhin – sie kann ja noch lernen!
Ich erstelle zwei Listen mit Schulnoten einer Klassenarbeit von verschiedenen Schülern. Ich berechne Maximum und Minimum der ersten Klassenarbeit – allerdings in nicht nebeneinanderliegenden Zellen.
Ich kopiere die beiden Zellen, in denen die Funktionen stehen.
Und füge sie in einer Zelle ein, wo sie die Ergebnisse für die zweite Klassenarbeit liefern sollen.
Erstaunlicherweise fügt Excel die Werte ein.
Und ja – ich weiß – über das Smarttag des Kontextmenüs kann man auf Formeln umschalten …
könntest Du mir bitte mit einer unserer Folien helfen?
Und zwar erscheint das Diagramm in Datenblatt 14 leer,
obwohl ich mir sicher bin, dass wir dort zusammen mit Dir eine Tabelle hatten.
Das ist die Folie, in der wir die Dauer des Verfahrens -10% Ausreißer oben und
unten darstellen.
Du kannst Dich gerne melden, wenn Du Fragen hast.
Liebe Grüße, Carmen
Was mache ich? Ich suche die Quelle des Diagramms. Fehler!
Mit dem Assistenten „Spur zum Fehler“ (in der Registerkarte „Formeln“) finde ich die Bösewichter:
Ich schreibe:
Hallo Carmen,
auf dem Blatt „Duration“ sind in K1378 ff. Bezugsfehler – ihr habt wahrscheinlich auf dem Overview-Blatt Zeilen eingefügt (oder gelöscht) – auf „Duration“ aber nicht. Das bewirkt, dass auf dem Blatt „14 average“ in den Zellen N1378 ein Bezugsfehler steht. Ich würde die Zeilen 1378:1383 löschen. Dann hast du in den Zellen X2:AA4 auch keine Fehler mehr und dann hast du ein korrektes Diagramm.
Kommste klar?
LG aus Graz
Rene
Carmen antwortet:
Wahnsinn – du bist ein Genie, René!! Tausend Dank für Deine schnelle Hilfe. Ich hab es tatsächlich geschafft 🙂
Anmerkung: Nö – ein Genie bin ich nicht … wirklich nicht … Ich kenne aber Excel ein bisschen …
Ich soll den Fehler in einer Formel finden. Genauer:
=GESTUTZTMITTEL(B:B;20%)
liefert die Fehlermeldung #BEZUG!
Kann die Funktion GESTUTZMITTEL keine Texte, wie beispielsweise in der Überschrift verarbeiten? Sind die Parameter richtig gefüllt? Stehen wirklich Zahlen in den Zellen der Spalte B? Sind die „Ränder“ so groß, dass kein MITTELWERT berechnet werden kann? Dann komme ich auf die Idee und lasse Excel mit dem Assistenten „Fehlerprüfung / Spur zum Fehler“ den Fehler finden (Registerkarte „Formeln“, Gruppe „Formelüberwachung“). Padautz: in Zelle B1373 steht ein Fehlerwert. Böse Menschen, die so etwas machen!
Ein Studienkollege meines Sohnes hat auf seinem PC Excel als
Programm nicht und benutzte Excel als Online-Version (one-drive). Er wollte
dort eine Tabelle transportieren. Er findet aber dort nicht die entsprechende
Funktion.
In Excel geht das ja über Einfügen > Inhalte einfügen à Transponieren oder über den rechten Mausklick. Aber in diesem Online-Excel scheint es diese Funktion nicht zu geben. Oder gibt es doch eine Möglichkeit einer Transponierung auf dieser Excel-Website? Vorausgesetzt, daß Sie diese mal benutzt haben?
Hallo Herr F.,
Ich habe nachgeschaut: Excel online hat viele Funktionen nicht – beispielsweise transponieren. Auch die Funktion MTRANS klappt nicht, weil Excel online keine Matrixfunktionen unterstützt.
Man kann die Tabelle natürlich mit Formeln transponieren.
Beispielsweise mit:
könntest Du uns bitte bei unserem gemeinsamen Meisterwerk
vom letzten Jahr helfen?
Einige Auswertungen für 2018 habe ich schon aktualisiert,
aber bei der Folie Nr. 12 (average duration) bin ich kläglich gescheitert.
Könntest Du mir hier bitte helfen?
Ich schicke Dir die gesamte Statistik-Datei anbei mit.
Liebe Grüße und schon vorab ganz lieben Dank!
Carmen
Hallo Carmen,
was bekomme ich, wenn ich dir die Lösung verrate? *lach*
Im Ernst: in dem Blatt „Overview“ steht in Zelle X1180
der Wert „11.07.2018?“. Das ist kein Datum! Deshalb stehen auf dem Blatt
„01 Duration“ in den Zellen K1180 und L1180 Fehlerwerte. Die werden in „12
average duration“ übernommen.
Auf dem Blatt „01 Duration“ habt ihr eine Zeile eingefügt oder gelöscht (ich kann das nicht genau erkennen. Die Folge sind Bezugsfehler, die sich durchziehen! Übrigens in den Zeilen 1181 und 1182 auch. Suche mal auf dem Blatt „01 Duration“ nach dem Text „#“ – du findest so die Fehler. Ich korrigiere sie nicht, weil ich weiß, welche Werte drinstehen sollen.
Deine Formel ist korrekt. Wenn die Fehler behoben sind,
bekommst du auch ein Ergebnis! Und ein Diagramm.
Liebe Grüße
Rene
PS: ich habe eine Weile gesucht, warum die Formel nicht
korrekt rechnet … manchmal muss man umgekehrt denken …
In Excel 2016 wurde das ausschließliche ODER (die Kontravalenz oder antivalente Disjunktion) eingeführt. In den seltensten kaufmännischen, mathematischen oder statistischen Berechnung wird diese Funktion, die im Bereich der Schaltalgebra in der Elektrotechnik und in der Verschlüsselungstheorie, eine große Rolle spielt, benötigt.
Zur Erläuterung: die Funktion ODER (eigentlich ein Konjunktor) bedeutet: das eine oder das andere oder beide. Umgangssprachlich häufig mit und/oder bezeichnet.
XODER meint dagegen ein ausschließliches ODER – also: entweder das eine oder das andere. Aber nicht beide gleichzeitig. Eine Verknüpfungstabelle erklärt dies:
WAHR XOR WAHR = FALSCH
WAHR XOR FALSCH = FALSCH XOR WAHR = WAHR
FALSCH XOR FALSCH = FALSCH
XOR ist also kommutativ. Und auch assoziativ:
WAHR XOR (WAHR XOR WAHR) = WAHR XOR FALSCH = WAHR
analog:
(WAHR XOR WAHR) XOR WAHR = FALSCH XOR WAHR = WAHR
Das erstaunt: drei mit XOR verknüpfte WAHR-Werte liefern also WAHR (und nicht FALSCH, wie man vielleicht vermuten würde)
Ebenso liefern vier mit XOR verknüpfte WAHR-Werte FALSCH, dagegen ist:
=XODER(3>0;2<9) liefert FALSCH. Nicht WAHR, wie auf der Hilfeseite zu lesen ist. Und der Satz:
„Da eine der beiden Prüfungen „Wahr“ ergibt, wird WAHR zurückgegeben.“
ist so auch nicht korrekt!
Der Satz
„Das Ergebnis von XODER ist gleich WAHR, wenn die Anzahl von Eingaben mit dem Ergebnis WAHR ungerade ist, und gleich FALSCH, wenn die Anzahl von Eingaben mit dem Ergebnis WAHR gerade ist.“
Zwei Pivottabellen – eine rechnet richtig, eine nicht. Gerechnet werden soll nicht:
(10+20)x(10+20) = 900
sondern
10×10 + 20×20 = 500
Wenn Sie genau hinschauen, sehen Sie den Unterschied:
In der ersten Pivottabelle wurde mit einem Berechneten Feld gearbeitet, das nicht so rechnet, wie ich es gerne hätte.
Im unteren Beispiel wurde die (formatierte/intelligente) Tabelle ins PowerPivot-Datenmodell gezogen – dort wurde gerechnet und das Ergebnis in Excel in einer Pivottabelle ausgegeben.
Vielleicht haben Sie auch eine Erklärung dafür warum sich Zeile 299906 das erste Feld ist gelb markiert zwar kopieren und als neue Zeile einfügen lässt aber die Spalte mit z.B. Vornamen sich nicht automatisch aktualisiert. Wenn sie das Beschriebene mit der ersten Zeile dieses gelben Bereich machen, funktioniert es.
hallo Julius,
lassen Sie sich über Registerkarte „Formeln“ die „Formeln anzeigen“. Dann stellen Sie fest, dass in Zeile 299906 keine Formeln stehen.
Ich weiß – es gibt Schlimmeres in Excel. Trotzdem musste ich schmunzeln: Die Cubefunktion CUBEMENGE verwendet als vorletzten Parameter „Sortier_reihenfolge“. Lustig geschrieben: Ich hätte ihn „Sortierreihenfolge“ genannt – der Unterstrich ist albern. Wahrscheinlich eine Analogie zum letzten Parameter „Sortieren_nach“.
Die Datentypen (oder auch nicht vorhandenen Datentypen) in Excel bringen mich um.
Hintergrund: Ich schreibe ich drei Zellen die Texte ‚1, ‚2 und ‚3. Die Formel =A1+A2+A3 wandelt die Texte in Zahlen um und rechnet richtig. Die Funktion =SUMME(A1:A3) interpretiert die Texte als 0. Ebenso schafft =JAHR(„28.01.2019“) den Text in ein Datum, also in eine Zahl, zu verwandeln und liefert die korrekte Jahreszahl.
In der Zelle C2 steht WAHR. Ein Vergleich =C2*1 liefert 1. =C2=1 liefert FALSCH – der Wahrheitswert WAHR entspricht also 1, ist aber nicht 1. Die Funktionen ISTZAHL und ISTLOG liefern FALSCH und WAHR. Wahr ist ein logischer Wert und keine Zahl, kann aber in eine Zahl verwandelt werden.
So weit, so gut. Deshalb kann die Funktion
=SUMME(C2:C12)
nicht korrekt rechnen, wenn in der Spalte C nur Wahrheitswerte stehen. Multipliziert man jeden Wert mit 1, dann klappt die Summe (als Matrixfunktion):
{=SUMME(C2:C12*1)}
Sie kennen sicherlich dieses Problem bei der Funktion SUMMENPRODDUKT.
Und jetzt mein Erstaunen:
=WAHR+WAHR+WAHR
liefert 3, ebenso wie =“1″+“2″+“3″ die Zahl 6 liefert.
=SUMME(WAHR;WAHR;WAHR)
liefert aber auch 3. Liegen die Werte in Zellen, klappt die Typkonvertierung nicht!
Nachtrag: Bei Programmiersprachen heißt die Umwandlung „casting“. Sollen wir mal Excel casten?
Einige Funktionen in Excel können nicht dateiübergreifend rechnen. Ist die Quelldatei geschlossen, stehen in der Zieldatei nach erneutem Öffnen Fehler in den Zellen.
Erstaunlicherweise kann Excel auch keine Bezüge auf intelligente/formatierte Tabellen in anderen Dateien zulassen. Hier ein Beispiel mit einem SVERWEIS:
Schließt man beide Dateien und öffnet die Zieldatei erneut, sieht das Ergebnis folgendermaßen aus:
Rechts stehen übrigens die Funktionen ZÄHLENWENN und SUMMEWENN.
Amüsant: Überprüft man, ob in einer leeren Zelle der Wert 0 steht, also =A1=0, so lautet das Ergebnis WAHR. Leer wird als 0 interpretiert. Das gleiche Ergebnis erhält man bei der Funktion SUMMENPRODUKT:
=SUMMENPRODUKT((B2:B17>=0)*1)
Die leeren Zellen werden als 0 interpretiert. Anders dagegen ZÄHLENWENN, SUMMEWENN & co – dort ist 0 etwas anderes, wie
Manchmal verblüfft mich Excel. Beziehungsweise Phänomene, die ich noch nie bemerkt habe.
Man erstelle eine Funktion, die einen leeren Wert zurückgibt, beispielsweise
=WENN(A1=“Januar“;1;““)
Man markiere die Spalte mit den Formeln, kopiere sie und fügen sie als Werte ein.
Mit [Strg] + [↓] springt Excel zu letzten (leeren?) Zelle. Stoppt nicht bei den Zahlen. Überspringt die vermeintlich leeren Zellen. Man sieht zwar nichts, aber die Funktion =ISTLEER liefert den Wert FALSCH. Diese Zellen sind nicht leer.
wahrscheinlich wirst du jetzt von mir das CALC Zeugnis zurück verlangen, aber mich macht der SVERWEIS noch wahnsinnig in EXCEL2016.
Es ist eine völlig simple Datei, aber trotzdem verweigert SVERWEIS den Dienst.
Ich hab dir die Datei im Original angehängt. Schau dir mal meine SVERWEIS Formal an und zeig mir bitte den Fehler. Die Boulesche Variable hab ich auch in allen Variationen ausprobiert à IMMER #NV.
Die Formel:
=SVERWEIS(A2;$I:$K;1)
Hi Jo,
SVERWEIS sucht immer in der ersten Spalte einer Liste.
Dein Pin steht aber in der dritten Spalte.
Du kann es lösen mit den Funktionen INDEX und VERGLEICH:
Wir verknüpfen zwei Arbeitsblätter. Ich ziehe den Bezug =ErstesBlatt!ErsteZelle herunter. Da ich nicht weiß wie weit, schieße ich weit übers Ziel hinaus. Das Ergebnis sind Nullen.
Ich frage die Teilnehmer, wie man die Nullen unterdrücken kann. Ich erhalte die Antwort: „in den Optionen“. Richtig: Dort kann man in Datei / Optionen / Erweitert in der Kategorie „Optionen für dieses Arbeitsblatt anzeigen“ die Einstellung „In Zellen mit Nullwert eine Null anzeigen“ ausschalten.
Ich erkläre, dass diese Option nicht immer geschickt ist, denn so werden SÄMTLICHE Nullen unterdrückt. Ich möchte es gerne lokal unterbinden.
Wenn ich solche Fragen stelle, erwarte ich eine Antwort. Erwarte ich WENN(=““. Ein Teilnehmer meldet sich und antwortet: „Mit der Funktion ISTLEER“. Ich bin verblüfft. Damit habe ich nicht gerechnet. Stimmt – ISTLEER prüft das Gleiche wie =““. Wirklich? Wir testen es und stellen natürlich kleine Unterschiede fest. Wenn in der Zelle ein Fehler ist. Oder beispielsweise ein leerer String:
=““
Wir lernen – was auf den ersten Blick gleich aussieht, muss nicht unbedingt gleich sein …
Excel lügt sogar manchmal, wenn es Zirkelbezüge meldet! Man erzeuge eine Arbeitsmappe mit 2 oder mehr Tabellenblätten. Blatt 1 wird eine Tabelle (Start-> als Tabelle formatieren) mit z.B. 10 Zeilen und 3 Spalten erzeugt. In den Spalten 2 und 3 (B2:C10) stehen irgendwelche korrekten Funktionen (z:B =Zufallsbereich(1;9), =Heute()+11)
Auf dem anderen Tabellenblatt werden irgendwo einige Zirkelbezüge eingegeben, möglichst einen anderer Adressbereich wählen, als den von der Tabelle belegten, z.B. von E20:F30 .
Wechselt man nun in die Tabelle auf Blatt 1 und ändert dort eine der Formeln (somit wird man eine Neuberechnung auslösen) , wird in der Statuszeile ein Zirkelbezug gemeldet, dessen Adressangabe nicht darauf hinweist, dass er auf einem anderen Tabellenblatt zu suchen ist. Vielmehr wird eine Zellenadresse innerhalb der korrekten Tabelle angezeigt, die ja garantiert nicht mit der tatsächlichen übereinstimmen kann.
Ich liebe Excel, aber ich hasse solche Nachlässigkeiten in einem Programm, das inzwischen 33 Jahre alt ist. Zirkelbezüge sind wahrhaftig nicht neu, das sollte MS doch im Griff haben.
Ich will eine Datei speichern und erhalte folgende Meldung:
„Auf die Datei kann nicht zugegriffen werden.“ Warum – ich will nicht auf die Datei „zugreifen“. Ich will sie speichern! Und: der Dateiname ist nicht länger als 218 Zeichen.
Des Rätsels Lösung: Pfad + Dateiname dürfen nicht länger als 255 Zeichen sein. Sehr unglücklich in dem Meldungstext ausgedrückt, wie ich finde …
Wenn man in (intelligenten / dynamischen / formatierten) Tabellen eine Ergebniszeile einfügt, kann man dort die aggregierenden Funktionen SUMME, ANZAHL, MAX, … verwenden. Hierfür benutzt Excel die Funktion TEILERGEBNIS, die ausgeblendete Zeilen übergeht:
Warum nicht die Funktion AGGREGAT, fragt man sich und reibt verwundert die Augen. Die Antwort ist einfach: Die Tabellen wurden mit Excel 2007 eingeführt. Damals gab es schon TEILERGEBNIS. Die Funktion AGGREGAT hielt allerdings erst in Excel 2010 Einzug in die Tabellenkalkulation. Und wenn etwas einmal drin ist, dann wird es so schnell nicht wieder geändert.
Die finanzmathematische Funktion zur Auflösung nach dem Zinssatz i lautet:
ZSATZINVEST(Zzr;Bw;Zw)
K0 = 10.000
K10 = 20.000
= 10 Jahre
Gesucht:
= ZSATZINVEST (10;10000;20000) = 7,18%
Hallo Christian,
Sag mal: ist das korrekt: löst die Funktion ZSATZINVEST die Funktion RMZ nach dem Zinssatz auf? (S. 5) Tut das nicht die Funktion Zins?
Hallo Rene,
Hier muss man unterscheiden, ZSATZINVEST ist für eine einmalige Anlage, Laufzeit, BW und Endkapital sind bekannt.
ZINS liefert Ergebnisse bei periodische Zahlungen z. B. den effektiven Jahreszins im Kreditbereich. Hier ist Zinssatz, (mtl.) Rate, Kreditbetrag und Laufzeit bekannt.
Traurig fragte mich eine Teilnehmerin der Excelschulung, was sie denn machen könne. Sie erhält von einem System regelmäßig Listen mit einer leeren Spalte. Jedoch befinden sich einige Zwischenüberschriften in dieser Liste.
Ihre Aufgabe ist es, eine fortlaufende Nummerierung zu erstellen, bei der die Zwischenüberschriften übergangen werden. Mein Vorschlag war folgender: Schreiben Sie in die erste Zelle eine 1. Markieren Sie ab der zweiten Zelle bis zum Ende der Liste. Wählen Sie nur die sichtbaren Zellen aus: Start / Suchen und Auswählen / Inhalte auswählen oder Tastenkombination [ALT] + [;] (also: [ALT] + [Umschalt] + [,].
Schreiben Sie in die erste Zelle die Formel
=MAX($H$5:H5)+1
und beenden die Eingabe mit [STRG] + [Enter]. Damit sie für alle Zellen übernommen wird.
Diese Formel kann man anschließend mit Kopieren / Inhalte einfügen: Werte in Zahlen umwandeln.
bin heute über Deine Webseite gestolpert und habe eine glatte Stunde (Arbeits-)Zeit hier verbracht. Köstlich. Als Trainer und VBA-Entwickler kann ich das nur zu gut nachvollziehen, habe mich sofort verstanden gefühlt.
Hier nun eine kleine Anekdote, die ich gerne Deiner Schatzkiste spenden möchte:
Habe für ein Projekt die Funktion =Zelle(„Adresse“) verwendet. So weit, so einfach. Dieses Sheet mit dieser Formel wird von Usern weltweit verwendet. Und =Zelle wird auch erwartungsgemäß übersetzt. Also „=Cell“. Auch noch alles gut. Aber natürlich wird das Argument „Adresse“ nicht übersetzt. Alle User weltweit beschweren sich bei mir.
Problemlösung: Alle Excel-Sprachpakete verstehen das englische Argument „address“. Also heißt die richtige Formel: =Zelle(„address“). Den Rest erledigte dann Suchen & Ersetzen.
Wieder was dazugelernt.
Viele Grüße aus dem Norden und ein Thumbs Up für Deine Website. Mach bitte weiter so!
Excel-Workshop. Wir üben wichtige und zentrale Themen. Beim Kapitel „Spur zum Vorgänger/Nachfolger“ fragt mich eine Teilnehmerin, warum diese beiden Symbole bei ihr ausgegraut (inaktiv) sind.
Ein bisschen habe ich suchen müssen – dann habe ich es gefunden: sie hat die Bilder ausgeblendet!
Wie hat sie das gemacht? Wir haben vorher das Thema „Zahlen Formatieren“ behandelt. Ich habe die Tastenkombination [Umschalt] + [Strg] + [6] für das Zahlenformat „Standard“ gezeigt. Wahrscheinlich hat sie [Strg] + [6] gedrückt – damit werden Bilder ausgeblendet.
ich bin auf deine Webseite gestoßen und hoffe, dass es ok ist, eine E-Mail zu schreiben.
ich habe folgendes Problem: Mir steht eine Exceltabelle mit einer Liste von verschiedenen Kostenarten in Spalte A zur Verfügung, z.B. „MasterCard, Adobe“, „Klipfolio 30€“ und „Juni Klipfolio VisaCard“. Diese möchte ich in die Kategorien „Adobe“ und „Klipfolio“ einteilen (nur als Beispiel, in Wirklichkeit sind es natürlich wesentlich mehr Kategorien) und suche deshalb nach genau diesen Stichwörtern im Text. Wird eines dieser Stichwörter gefunden, soll es entsprechend in Spalte B geschrieben werden, so dass ich das ganze später bequem in einer Pivottabelle zusammenfassen kann.
A B
Kostenart Stichwort
MasterCard, Adobe Adobe
Klipfolio 30€ Klipfolio
Juni Klipfolio VisaCard Klipfolio
Insgesamt handelt es sich um ca. 1.000 Datensätze und rund 50 verschiedene Stichwörter. Das „Problem“ ist zur Zeit über eine lange verschachtelete Wenn-Funktion gelöst. Ich dachte aber, es gäbe über VBA vllt eine schnellere und unkompliziertere Lösung. Zur Zeit bin ich so weit, dass ich nach einem bestimmten Stichwort (egal wo im Text es steht) suchen lassen kann.
Sub a()
Dim Rng As Range
Dim wks As Worksheet
Set wks = Worksheets(„data“)
Set Rng = wks.Range(wks.Cells(1, 1), wks.Cells(1000, 1)).Find(what:=“Adobe“, lookat:=xlPart, LookIn:=xlValues, MatchCase:=True)
If Not Rng Is Nothing Then Rng.Select
End Sub
Ich komme allerdings jetzt nicht mehr weiter bzw. bin mir auch nicht sicher, ob das überhaupt ein guter Ansatz ist.
Kannst du mir weiterhelfen?
Vielen Dank im Voraus,
#####
Hallo J.,
ich würde das mit einem SVERWEIS lösen. Oder mit der Funktion INDEX und VERGLEICH. Kannst du?
Liebe Grüße
Rene
#####
Hallo René,
vielen Dank für deine super schnelle Antwort.
Ja, beide Funktionen sind mir bekannt. Da ich ja aber ca. 50 verschiedene Suchkriterien habe, würde ja auch da wieder eine extrem lange und verschachtelte Formel in der Spalte B stehen- oder übersehe ich etwas? Aus diesem Grund dachte ich an eine VBA Abfrage. Diese wäre vllt ebenfalls lang und verschachtelt- würde ja aber quasi „im Hintergrund“ laufen- andere Nutzer müssten dann ja nur noch monatlich in der Kostenübersicht das Makro starten. Siehst du bzgl VBA auch eine Möglichkeit?
Danke und LG J.
#####
Wer nervt mehr? Excel oder die Menschen, die Excel benutzen?
ich habe eine Art Kalender, also beginnend mit dem 01.01.2018, endend mit dem 31.12.2018. Bei der Aufziehung des Datums lasse ich das Wochenende weg. In einer Extra-Spalte soll vor jedem Montag die Kalenderwochenzahl stehen. Wenn ich diese Funktion anwende, wird die Kalenderwochenzahl auch vor den Tagen Dienstag bis Freitag eingefügt. Gibt es eine Möglichkeit, z. B. über die Bedingte Formatierung, daß die Kalenderwochenzahl nur neben dem Montag gezeigt wird? Herzliche Grüße
####
Hallo Herr F., ich würde es mit einer WENN-Funktion lösen. Beispielsweise so (wenn in B1) das Datum steht: =WENN(WOCHENTAG(B1;2)=1;ISOKALENDERWOCHE(B1);„“) schöne Grüße Rene Martin
####
Hallo Herr Martin,
das ging aber recht schnell mit Ihrer Antwort. Vielen Dank.
Ich habe etwas herum experimentiert und diese Lösung benutzt.
Einzig beim Übergang von 2018 zu 2019 scheint dies nicht zu funktionieren, denn der 31.12.2018 ist zwar noch zum alten Jahr gehörig, müßte aber dennoch mit der 1 beginnen, macht es aber nicht:
Herzliche Grüße
####
Hallo Herr F., ich bin nicht sicher, ob in Excel 2010 bereits der Parameter 21 in der Funktion KALENDERWOCHE vorhanden war. Schauen Sie mal bitte nach – ich habe hier kein Excel 2010. Wenn das nicht der Fall ist, müssen Sie die Funktion ISOKALENDERWOCHE nachbauen. Anleitungen finden Sie im Internet. schöne Grüße Rene Martin
####
Hallo Herr Martin, vielen Dank für Ihre letzte Antwort. Sie haben natürlich recht: mit ISOKALENDERWOCHE läßt sich das Problem beheben. Wie ist es aber, wenn man Excel 2010 benutzt. Da gibt es diese Funktion nicht. Wie ließe ich hier das Problem lösen, also nur daß vor dem Montag die Kalenderwochenzahl steht? Herzliche Grüße
####
Hallo Herr Martin,
den Parameter 21 hat Excel 2010 nicht.
Mal sehen, was ich im Internet finde, bisher habe ich nichts gefunden.
Dennoch vielen Dank.
Herzliche Grüße
####
Hallo Herr Martin,
ich muß mich korrigieren. Der Parameter 21 ist in Excel 2010 vorhanden.
Ich habe wie folgt die Funktion geschrieben:
=WENN(WOCHENTAG(B1;2)=1;KALENDERWOCHE(C1;21);„“)
Und es funktioniert auch der Wechsel von der 52. KW 2018 zur KW 1 2019.
danke für den Hinweis.
Wir haben ein Problem! Ich habe es mal eingekreist:
In einer Datei habe ich fünf Verknüpfungen auf eine andere Datei:
Ich ersetze den Pfad durch einen anderen Pfad, in dem die Datei noch einmal liegt:
Klappt!
Ich ersetze den Pfad durch einen anderen Pfad, in dem die Datei nicht liegt oder durch einen anderen Ordner, der nicht existiert. Es öffnet sich das Suchfester, das abfragt, wo denn die Datei liegt:
Ich breche es ab.
Was passiert: Excel hat nur in der ausgewählten Zelle den Pfad geändert – in allen anderen nicht!
Das heißt: da Sie SUMMEWENNS-Formeln auf mehrere Dateien aufsetzen:
SUMMEWENNS( … \xyz\[2017-04-27_xyz_EHB_KoPrüf Gas 2015_Verpächter_1_SWM_überarbeitet Biogas.xlsx] … + \abc2\[2016-08-01_xyz_EHB_KoPrüf Gas 2015_Verpächter_3_GVG_versandt.xlsx]
„erkennt“ Excel den Gesamtpfad als nicht gültig und stoppt den Ersetzvorgang.
Nun haben wir ein Problem: die zirka 50.000 Formeln beziehen sich auf zwei Ordner, die es nicht mehr gibt. Sobald ich einen versuche zu reparieren, weigert sich Excel diesen Vorgang in allen Zellen durchzuführen.
Was machen wir? Haben Sie nur einige wenige solcher Dateien? Dann könnte man den Pfad so „hinbiegen“, dass er beim ersten Ersetzvorgang korrekt ist, beim zweiten immer noch, beim dritten auch …
Haben Sie „viele“ solcher Dateien? Dann muss ich noch einmal in mich gehen …
„Hübscher“ Bug zum Thema „Excel ersetzt nicht“. Oder „Excel ersetzt nur einmal“. Oder „Ersetzen in Excel klappt nicht.“
ich hoffe es geht dir soweit gut und du bist mit deiner 4ten Million nun endlich fertig
Sorry, dass ich dich wieder mal belästige, aber der SVERWEIS mag mich nicht mehr. Ich hatte jetzt damit jahrelang keinerlei Probleme mehr und nun sträubt sich das Ding wie ne keusche Jungfrau.
Ich hab dir mal die Datei angehängt. Wäre super wenn du da mal drüber schauen könntest.
Ich muss hier 2 Tabellen nach der PIN Nummer vergleichen.
Kriterium: PIN Nummer (Spalte F)
Matrix: Spalte K-M
Gesuchter Wert: Nachname (Spalte M)
Hallo J.,
du solltest richtig markieren. Dann klappt es auch.
Und: verwende besser den Parameter FALSCH – dann siehst du auch die PIN-Nummern, die nicht in der Liste vorhanden sind.
Liebe Grüße
Rene
Moing Rene,
zunächst einmal Danke für deine schnelle Hilfe.
Und was jetzt kommt ist die reine Wahrheit, ich hab Zeugen dafür. Bitte glaub mir, der SVERWEIS macht mir normalerweise keine Problem mehr, aber was hier abgelaufen ist, ist nicht erklärbar:
Ich hatte genau die gleiche Formel wie du auch, aber bei mir kamen völlig blödsinnige Werte, meistens ein #NV. Ich hab dann manuell eine Tabelle entworfen, nur um evtl. Zelltypenfehler zu vermeiden, aber auch da hat der SVERWEIS nicht funktioniert. Erst dann habe ich dir geschrieben. Als deine Antwort da war und in deiner Formel genau das gleiche stand wie in meiner, war ich völlig von den Socken . Darauf nahm ich meine Original Tabelle und hab einen SVERWEIS nochmal genauso eingegeben wie davor und …. JETZT LÄUFT DAS DING WIE GESCHMIERT.
Hast du sowas schon mal erlebt?
DA fällst doch vom Glauben ab oder hast du da eine Erklärung?
Eine „Monsterdatei“: 35 MByte, 18 Tabellenblätter, mehrere davon gefüllt mit bis zu 500.000 Datensätzen, 1.300.000 Formeln, mehrere Millionen gefüllte Zellen. Inquire hilft bei der Analyse der Datei, rechnet allerdings selbst sehr lange:
Ich suche einen Fehler. Zwei Stunden lang. Bis ich ihn finde:
Mein neues Excel-Buch – 544 Seiten zum Thema Zahlen, rechnen, Formeln, Funktionen, verknüpfen, knobeln, denken, Probleme in und um Excel lösen. Mein gesamtes Wissen (okay – ein großer Teil davon). Von Erklärungen SVERWEIS, verschachtelte WENN-Funktionen und Datumsberechnungen über die Funktionen der Kategorie Nachschlagen & Verweisen, Textfunktionen und Statistik bis hin zum numerischen Integrieren, Iterationen und Determinatenberechnungen. ich denke – da ist für jeden etwas dabei. Erhältlich in jedem Buchladen: Excel: Zahlen rechnen Formeln: Formeln, Berechnungen und Verknüpfungen in Excel Taschenbuch – 10. Januar 2018 von René Martin (Autor) ISBN-10: 3746064872 ISBN-13: 978-3746064871
Schon perfide: Ich verbinde zwei Zellen miteinander („verbinden und zentrieren“). Ich speichere die Datei unter dem Namen „RenesTest.xlsx“. Ich verweise von einer anderen Datei auf die verbunden Zelle(n) und erhalte als Bezug:
=[RenesTest.xlsx]Tabelle1!$B$5
Ich speichere die Datei unter dem Namen „Test Rene.xlsx“. Erneut ein Bezug auf die verbundenen Zellen. Nun erhalte ich:
='[Test Rene.xlsx]Tabelle1′!$B$5:$C$5
was nach Bestätigung mit dem Fehler #WERT! quittiert wird. Erstaunlich, dass bei einem Leerzeichen im Dateiname der Bezug auf die verbunden Zellen aufgelöst wird, während bei einem Dateinamen ohne Leerzeichen der Bezug auf eine Zelle erfolgt!
Fazit: Besser nicht „verbinden und zentrieren!“ Das bringt nur Ärger!
ich bin grad über Ihren Blog gestolpert und setze grad alle Hoffnung auf Sie!
Ich verzweifle grad an der Zählewenn/Zählewenns Funktion.
Folgendes Szenario: Siehe auch Anhang.
B3:B9 hat Datumswerte (11.11.2017, 02.04.2018 etc.)
Ich möchte alle Zellen dieses Bereichs zählen, deren Datumswert den Monat 11 (Zelle A2) und das Jahr 2017 (Zelle A1) hat (also im November 2017 liegt).
=ZÄHLENWENN($B$3:$B$9;UND(JAHR($B$3:$B$9)=A1;MONAT($B$3:$B$9)=A2)) Gibt 0 zurück.
Problem: Wenn ich die Formel Wert für Wert durch gehe, sehe ich, dass es immer nur eine Zelle abfragt, nämlich die, die die gleiche Zeile hat, in der auch die Formel steht, und gibt mir 0 zurück, weil das Ergebnis FALSCH ist.
Problem: Wenn ich die Formel in die gleiche Zeile verschiebe, in der der Monat 11 und Jahr 2017 in der Zelle vorkommt, gibt mir die Formel auch 0 zurück, wie in allen anderen Zeilen, obwohl das Ergebnis WAHR ist.
Manchmal stolpert man (ich) über seine eigenen Füße.
Heute. Excelschulung. Ich erkläre die Funktion INDEX. Ich verweise mit INDEX vom zweiten Tabellenblatt auf das erste Blatt „Diäten“. Ich ziehe mit der Maus den Bereich auf. Bei den ersten beiden Spalten lautet die Formel:
=INDEX(Diäten!A1:B24
füge ich die Spalte C hinzu lautet die Formel jedoch:
=INDEX(Diäten
Schließe ich Spalte D ein finde ich nun folgende Formel:
=INDEX(Diäten!A1:D24
Ich grüble eine Weile über das seltsame Phänomen, bis ich dahinter komme, dass ich ja den Bereich A1:C24 benannt habe: er heißt „Diäten“.
Man sollte nicht Tabellenblätter so beschriften wie die Namen, die in der Datei vergeben wurden. Das führt zu Chaos!
Excel-Schulung. Eine Teilnehmerin präsentiert eine Liste. In einer Spalte stehen Geldbeträge. Jeweils unter einer bestimmten Gruppe befinden sich Zwischensummen. Manche Summenbeträge sind positiv, manche negativ. Sie möchte gerne nur die positiven Zwischensummen und die dazugehörigen darüberstehenden Werte sehen. Das funktioniert mit einem einfachen Filter nicht. Man muss mit einer Hilfsspalte arbeiten. Dort wird überprüft, ob die Spalte eine Zwischenspalte ist – ob der Wert positiv oder negativ ist. Dann wird es als solches gekennzeichnet. Falls nicht, wird der untenstehende Wert übernommen:
Jede Formel bezieht sich auf die darunter stehende Zelle. Excel rechnet von links nach rechts, von oben nach unten. Eigentlich muss der letzte, unterste Wert zuerst berechnet werden, dann den zweiten Wert von unten. Auf diesen bezieht sich die Formel darüber … und so weiter bis zur zweiten Zeile.
Wie rechnet Excel? Iterativ? Wahrscheinlich!
Die Hummel besitzt 0,7 qcm Flügelfläche bei 1,2 Gramm Gewicht.
Nach den bisher bekannten physikalischen Gesetzen der Aerodynamik, sowie unserem physikalischen Verständnis ist es unmöglich, bei diesem Verhältnis zu fliegen!
Die Hummel weiß das aber alles nicht. Sie fliegt einfach!
Vielleicht weiß Excel das auch nicht – und rechnet einfach.
Eine Liste enthält Texte und Zahlen. Ich möchte herausfinden, wie viele Zellen gefüllt sind. Ich versuche ZÄHLENWENN. Wir wissen, das zwei Anführungszeichen innerhalb eines Textes als ein Anführungszeichen interpretiert wird. Also versuche ich:
=ZÄHLENWENN(C2:C15;“<>“““““)
Es scheitert!
Obwohl die Bedingung <>““ korrekt arbeitet, muss man die Bedingung folgendermaßen formulieren:
=ZÄHLENWENN(C2:C15;“<>“)
Analog werden die Anzahl der leeren Zellen ermittelt:
Heute in der Excelschulung. Wir berechnen die Spannwerte, also die Differenz zwischen Max und Minimum. Funktion Maximum auswählen, den Cursor hinter die Klammer setzen, Minus eintippen und über die Registerkarte „Formeln“ aus der Kategorie „Statistik“ die Funktion „MIN“ auswählen. Kein Problem:
Beschließt man jedoch aus der Dropdownsymbol „AutoSumme“ die Funktion „Min“ auszuwählen, beschließt Excel eigenmächtig die verkettete Funktion vorzeitig zu beenden und das Ganze auch noch mit einer Fehlermeldung zu quittieren:
Wir haben einen Rechtschreibfehler (?!?) in Ihrer Formel gefunden …
Heute in der Excel-Schulung habe ich den Assistenten „Teilergebnis“ gezeigt, den man in der Registerkarte „Daten“ in der Gruppe „Gliederung“ findet.
Traurig fragte mich ein Teilnehmer, warum er bei ihm „ausgegraut“, also inaktiv, sei.
Zwei Mal hingeschaut und einmal überlegt: Der Teilnehmer hatte eine (intelligente) Tabelle eingeschaltet. Eigentlich logisch, dass Excel nicht zulässt, dass in einem solchen Konstrukt Zwischensummen eingefügt werden.
Mein neues Excel-Buch ist erschienen – 544 Seiten zum Thema Zahlen, rechnen, Formeln, Funktionen, verknüpfen, knobeln, denken, Probleme in und um Excel lösen. Mein gesamtes Wissen (okay – ein großer Teil davon). Von Erklärungen SVERWEIS, verschachtelte WENN-Funktionen und Datumsberechnungen über die Funktionen der Kategorie Nachschlagen & Verweisen, Textfunktionen und Statistik bis hin zum numerischen Integrieren, Iterationen und Determinatenberechnungen. ich denke – da ist für jeden etwas dabei. Erhältlich in jedem Buchladen: Excel: Zahlen rechnen Formeln: Formeln, Berechnungen und Verknüpfungen in Excel Taschenbuch – 10. Januar 2018 von René Martin (Autor) ISBN-10: 3746064872 ISBN-13: 978-3746064871
Eigentlich schade. Wer den Konjunktor XOR aus dem Physikunterricht oder aus der bitweisen Verknüpfung aus dem Informatikunterricht kennt, der wird enttäuscht sein, dass man ihn in Excel dafür leider nicht einsetzen kann. Der Grund: Wandelt man eine Zahl mit der Funktion DEZINBIN um, so ist der Binärwert leider keine Zahl mehr, sondern ein Text. Und damit kann man SO nicht weiterrechnen. Dann halt umständlich …
Ein Mitarbeiter einer Firma erstellt in Excel 2007 eine Liste mit Bildern und Dropdownlisten, die über eine Datenüberprüfung gefüllt werden. Über 60 Namen „suchen“ mit der Funktion INDIREKT den Wert der Dropdownliste in einem anderen Bereich und geben die Zelle zurück, in der sich ein Bild befindet.
Diese Namen werden auf die Bilder angewendet: über die Bearbeitungszeile wird der Name mit bspw. =Bild24 zugewiesen.
Das Problem: Es funktioniert prima in Excel 2007. Öffnet man diese Datei jedoch in Excel 2010 oder höher, sind die Verweise weg. Alle! Das heißt: noch einmal die 60 Verweise setzen.
Was passieren kann, wenn man einen Text als Text formatiert – darauf habe ich schon hingewiesen. Auch was passiert, wenn man einen (langen) Text als Buchhaltung formatiert:
Amüsant ist dagegen auch folgendes Phänomen: Wenn man einen Text als Datum, Prozentwert oder Währung formatiert:
und dann mit der Funktion LÄNGE weiter rechnet – allerdings mit mindestens zwei Rechenoperationen (beispielsweise LÄNGE – 1 oder LÄNGE x 1), dann wird das Zahlenformat übernommen:
Erstaunlicherweise: FINDEN und SUCHEN liefern auch Zahlen – sie übernehmen jedoch nicht das Zahlenformat.
Schnell entdeckt man jedoch, dass auch Formatierungen übernommen werden. Meistens korrekt:
Steht in A1 eine Zahl, die als Währung oder Buchhaltung formatiert ist, dann wird eine Berechnung
=A1*19% als Währung oder Buchhaltung formatiert.
Stehen in A1 und A2 Datumsangaben, dann liefert die Differenz eine Zahl und kein Datum. Wird jedoch
=A1+30 berechnet, so ist das Ergebnis als Datum formatiert. Die Summe, Mittelwert, Max und Min von mit Währung oder Buchhaltung formatierten Zellen, werden korrekt wieder in diesem Format angezeigt. So weit, so gut.
Allerdings liefert
=WENN(WOCHENTAG(A1;2)>5;A1+3;A1+1)
leider kein Datum, sondern eine Zahl.
(Erklärung: Ermittle zu einem Datum den nächsten Arbeitstag)
Und schließlich: Steht in A1 ein Text, beispielsweise „Excel nervt“. Ist diese Zelle als Text formatiert (okay – nicht nötig; aber stört eigentlich nicht), so liefert:
=LÄNGE(A1)
die Zahl 11.
Jedoch liefert:
=LÄNGE(A1)*1
den Text „11“ (linksbündig). Editiert man die Zelle, so steht die Formel nun als Formel in der Zelle. Irgendwie doof …
ich befinde mich derzeit in einem Excel-Grundkurs und bin bei einer „verschachtelten Wenn-Funktion“ auf folgende Problematik gestoßen:
Excel rechnet nicht weiter, sobald der erste „Wahr“wert erreicht ist, was zu logischen Fehlern führt, sofern die Abfragewerte aufsteigend abgefragt werden.
Der „Sonst“Wert wird allerdings korrekt ausgegeben.
In meinem Beispiel habe ich dann die Abfragewerte 25% und 20% umgekehrt und es hat wie geplant funktioniert.
Wo liegt mein Fehler?
Haben Sie eventuell eine Idee zur Vereinfachung der Funktion?
Im voraus vielen Dank für Ihre Hilfe.
Mit freundlichen Grüßen
Jan
Hallo Herr S.,
Sie haben recht: Excel arbeitet Wenn-Funktionen baumartig ab. Also:
Wenn > 80% dann
wenn > 75% dann
sonst: Rest <= 75%
Sie können es aber auch „umbauen“:
wenn <= 75% dann
wenn <= 80% dann
Rest: sonst > 80%
Tipp: ich zeichne manchmal so eine Baumstruktur auf, um es besser zu verstehen.
Tipp 2: schreiben Sie 2 und 5 und nicht „2“ oder „5“. Sie möchten ja mit dem Zahle weiterrechnen. Sie können übrigens auch 2% und 5% verwenden
Hallo Herr Martin,
vielen dank für die schnelle Antwort.
Das ganze ist ja ganz schön verwirrend, gibt es dann noch eine andere Funktion die das Ziel einfacher erreicht?
Schöne Grüße
Jan
Hallo Herr S.
schauen Sie sich einmal die Funktion SVWEIS an – dort werden mehrere Fälle auf einer Ebene abgearbeitet.
Wer in Excel gerne mit Namen arbeitet, weiß die Funktionstaste [F3] zu schätzen. Man kann den Dialog, der die Namen anzeigt aus der bedingten Formatierung, der Datenüberprüfung, den Diagrammen, … heraus aufrufen.
Leider nicht aus den Steuerelementen heraus, wenn dort im Formatierungsdialog ein Wert in eine Zelle geschrieben wird, die einen Namen hat.
In einer Arbeitsmappe befindet sich ein Tabellenblatt – nennen wir es „Jena“. In dieser Mappe befindet sich ein zweites Tabellenblatt, das Bezug auf das erste Blatt nimmt. Die Formeln können dann beispielsweise so aussehen:
=Jena!H4
In einer zweiten Arbeitsmappe gibt es auch ein Tabellenblatt „Jena“. Wird nun das zweite Blatt aus der ersten Daten in die zweite kopiert oder werden die Zellen in die zweite Datei kopiert, so wird der Bezug auf die erste Datei „mitgenommen“ und nicht auf die zweite Datei:
Excel ist an vielen Stellen nicht konsequent. Der Text „1“ ist beispielsweise etwas anderes als die Zahl 1. Dennoch kann man den Text mit 1 multiplizieren.
Ebenso verhält es sich mit WAHR und 1. Eigentlich entspricht Wahr dem Wert 1 und Falsch dem Wert 0. Eigentlich. Multipliziert man die beiden Werte WAHR und FALSCH, beziehungsweise die Funktionen =WAHR() und =FALSCH() mit 1, erhält man 1 beziehungsweise 0. Intern handelt es sich jedoch um einen anderen Wert. Also
=WAHR=1
liefert FALSCH.
Und diese Unschärfe führt auch dazu, das Wahr-Werte nicht addiert werden können:
Eine Datei greift mit einer Formel auf eine zweite Datei zu. Kein Problem.
Doch: ist ein Problem – nämlich, wenn es sich bei der Formel um SUMMEWENN, ZÄHLENWENN & co handelt. Dann muss nämlich die Quelldatei offen sein, damit kein Fehler angezeigt wird. Sehr erstaunlich!
Wollt ihr wissen, wie man Excel zum Absturz bekommt? Man muss die Funktion AGGREGAT in einem Namen verwenden und diesen in einem Diagramm.
Das Ganze geht so:
Eine Tabelle holt sich Werte aus einer anderen Liste. Da einige Werte nicht gefunden werden, werden diese als #NV angezeigt. In einem Diagramm werden die entsprechenden Kategorien verwendet:
Unschön, denke ich mir. Die Jahreszahlen, die keinen Wert haben, sollen ausgeblendet werden. Und lege vier Namen an: „Bau“, „IT“, „Verwaltung“ und „sonstiges“. Sie haben die Form:
Eigentlich unterscheidet Excel zwischen Text und Zahl. Eigentlich. Sicherlich kennen Sie folgendes Phänomen:
Eine Spalte ist als Text formatiert:
In einer anderen Spalte stehen ZAHLEN. Diese werden mit der Funktion SVERWEIS als #NV (nicht vorhanden) quittiert:
Das habe ich schon mehrmals beschrieben – beispielsweise in: „Sverweis funktioniert nicht“
Ebenso werden sie bei der Überprüfung auf Gleichheit
{=ODER(K2=$G$2:$G$22)}
(als Matrixformel) korrekt als unterschiedliche Werte erkannt:
Verwendet man statt dem Gleichheitsoperator „=“ jedoch die Funktion IDENTISCH werden die Texte und Zahlen als gleich(wertig) eingestuft:
{=ODER(IDENTISCH(K2;$G$2:$G$22))}
Ebenso übergeht ZÄHLENWENN diesen Unterschied:
Auch hier gilt mal wieder – schade, dass Excel an so vielen unterschiedlich ist, beziehungsweise einfach nicht konsequent. Kein Verlass auf gar nichts!
Amüsante Fehlermeldung. Dabei wollte ich doch nur ein weiteres Feld in eine Pivottabelle einfügen:
Aber die Ursache ist schnell gefunden: Neben der Pivottabelle befand sich eine Formel. Excel kann nicht eine Spalte einfügen, verschiebt also nicht die Tabelle, sondern überschreibt die Formel:
Die Frage bleibt: Hätte man nicht „Tabelle2 enthält bereits Daten“ etwas anders formulieren können?
Kennen Sie das? Ich erstelle eine Liste mit Verkäufernamen, Monatsnamen und Umsatzzahlen. Über Formeln / Definierte Namen / Aus Auswahl erstellen werden die Spaltennamen und Zeilennamen zu Namen der entsprechenden Zeile und Spalte:
Nun kann man die Schnittmenge berechnen:
=Roth Umsatz
Leider kann man diese Werte nicht auslagern – das führt zu einem Fehler:
Das ist erstaunlich, denn folgende Formeln funktionieren problemlos:
=SUMME(INDIREKT(„C2:C7“))
=SUMME(INDIREKT(„Umsatz“))
Aber eben leider nicht:
=SUMME(INDIREKT(„Umsatz Roth“))
und auch nicht:
=SUMME(INDIREKT(„C2:C5 C3:C7“))
Schade, dass INDIREKT keine Schnittmenge verarbeiten kann.
Nachtrag: Danke an XLarium für den wertvollen Hinweis (⇓):
Kennen Sie das? Sie arbeiten mit Excel, Kinder oder Nichten und Neffen mit libreOffice, ein Freund mit Numbers oder Google Tabellen? Und Sie sind erstaunt, dass es in der einen Tabellenkalkulation Funktionen gibt, die in der anderen fehlt. Nervig und ärgerlich (gerade beim Austausch der Tabellen).
Der Zeitschriftenverlag Heise hat sich die Mühe gemacht, sämtliche Funktionen dieser vier Tabellenkalkulationen aufzulisten und zu vergleichen:
Beim Durchsehen der Liste fällt auf, dass auch in dem geliebten Excel einige (wichtige) Funktionen fehlen, die in einem der anderen Programme integriert sind:
Ostersonntag, Tagname, Monatsname, BasisInZahl (habe ich noch nie vermisst), Laufzeit, ZGZ, ISEMAIL, ISURL, AKTUEL, FORMEL (heißt in Excel: FORMELTEXT), BEREICH.ÜBERSCHNEIDEN, BEREICH.VERBINDEN, POLYNOM, COUNTUNIQUE, FARBE, UMRECHNEN (entspricht EUROKONVERT), B, KOVARIANZ (heißt KOVAR, KOVARIANZ.P und KOVARIANZ.S in Excel), SCHÄTZER.EXP.VOR.MULT, SCHÄTZER.EXP.MULT, KLARTEXT, REGEXEXTRACT, REGEXMATCH, REGEXREPLACE und 59 weitere Funktionen …
Gestern in der Excel-Schulung. Wir üben die WENN-Funktion. Standard-Beispiel: eine Provisionsberechnung. Ein Teilnehmer ruft mich, weil er eine Fehlermeldung erhält:
Ich gestehe: ich habe drei Mal hinschauen müssen, bis ich es entdeckt hatte: das schließende Anführungszeichen bei dem Sonst-Zweig (Wert_wenn_falsch) fehlt.
Habe unterschiedliche Mitarbeiter, die unterschiedliche Bonisätze bekommen sollen.
Die Bonitabelle liegt in anderem Tabellenblatt.
Damit ich in Pivot für jeden MA den richtigen Bonusbetrag anzeigen kann, habe ich mir mit SVerweis die Sätze in meine Ausgangstabelle geholt.
Und ein Feld berechnet. Soweit alles schön…
…nur, der Bonusbetrag ist um den Faktor 10 zu hoch!
Wer macht da was falsch : ich oder Excel?
Hiielfe! Kannst Du helfen?
Viele Grüße – Angelika
#####
Hallo Angelika,
der Knackpunkt in der Pivottabelle liegt im berechneten Feld. Das kann man leicht zeigen. Bau mal folgende Tabelle auf:
Setze eine Pivottabelle auf, gruppiere die Namen, summiere die Beträge:
Wenn du nun ein berechnetes Feld einfügst – Betrag * Provision – stellst du fest, dass die berechnete Provision doppelt so hoch ist, wie sie sein sollte:
Der Grund: die beiden Provisionssätze für Rene werden summiert (5% + 5% = 10%) und diese Zahl mit der Summe der Beträge multipliziert. Wenn du die Summe mit 5% multiplizieren möchtest, musst du den Provisionssatz (über einen SVERWEIS) reinholen und damit multiplizieren. Dann klappt es:
Der Problem mit dem Datum hat mich gestern noch beschäftigt.
In der Zelle D3 steht nichts. Erstaunlicherweise liefert =JAHR(D3) keinen Fehler (wie ich vermutet hätte), sondern 1900. Warum?
Die Antwort: Schreiben Sie in eine Zelle das Datum 05.01.1900. Subtrahieren Sie von diesem Datum die Zahl 1. Setzen die Formel fort. Nach dem 01. Januar 1900 folgt der 00. Januar 1900. Dann ein Fehler:
Und das ist der Grund, warum man von einer leeren Zelle die Jahresinformation auslesen kann. Auch JAHR(0) liefert das Jahr 1900. Steht in der Zelle allerdings ein leerer Text („“) oder #NV, dann ist ein Fehler die Folge:
Das heißt im Umkehrschluss: WENNFEHLER(JAHR(D3);““) fängt keinen Fehler ab, wenn die Zelle D3 nicht gefüllt ist. Lediglich wenn in der Zelle kein Datum, also Text steht. Mit WENNFEHLER kann man diese Information also nicht abfangen.
Ich habe mich heute sehr amüsiert. Ich war in einer großen Firma, die Sie auch kennen. Dort haben mir Mitarbeiter eine große Exceltabelle gezeigt, mit der Bitte, ihnen die Formeln zu erklären und möglicherweise zu verbessern. Das Grundproblem tauchte an sehr vielen Stellen auf: In zwei unterschiedlichen Spalten stehen Datumsangaben. Allerdings: nicht in jeder Zelle.
Es sollen die Datumsdifferenzen berechnet werden. Allerdings nicht Ende – Anfang, da die leeren Zellen ein Ergebnis verfälschen würden. Nun hat ein Kollege – wahrscheinlich über Jahre – verschiedene Formeln eingetragen:
=WENN(D2>=1;D2-C2;““)
Ist okay – hier habe ich nichts zu nörgeln.
=WENN(D2>=1;D2-C2;“ „)
Das Leerzeichen stört mich; würde ich nicht machen – besser: „“.
=WENN(D2>=1;DATEDIF(C2;D2;“d“);““)
Warum einfach, wenn es auch umständlich geht. D2-C2 entspricht DATEDIF(C2;D2;“d“).
=WENNFEHLER(D2-C2;““)
Ganz schlecht: D2-C2 liefert keinen Fehler, wenn eine der beiden Zellen leer ist. Das Ergebnis ist beispielsweise -42780.
=WENN(ISTFEHLER(D2-C2);““;D2-C2)
Bis Excel 2003 gab es noch nicht die Funktion WENNFEHLER – bis dahin musste man WENN(ISTFEHLER(… schreiben. Falsch und überflüssig!
=WENN(UND(C2<>““;D2<>““);D2-C2;““)
Gut: beide Zellen werden überprüft!
Die Funktion ISTZAHL habe ich in der Tabelle nicht gefunden.
Wir haben uns amüsiert, weil das gleiche Problem von einem Anwender auf verschiedene Arten gelöst wurde. Das ist nicht konsistent und auch nicht nachzuvollziehen. Aber man kann ja mal vermuten, was die Ursache des Formelwandels war …
Letzte Woche in der Excel-Schulung. Ich erkläre, dass es manchmal besser ist, den Funktionsassistenten zu verwenden, weil dort einige Informationen angeboten werden, manchmal ist die Eingabe über die Tastatur die bessere Wahl, weil Parameter aufgelistet und erklärt werden.
Ein Teilnehmer meldet sich und fragt traurig, warum er keine Formelvervollständigung hat:
och!
Die Lösung war schnell gefunden: In den Option war in der Kategorie „Formeln“ die Option „AutoVervollständigen für Formeln“ deaktiviert.
Berechne die inverse Matrix mit der Funktion MINV:
Multipliziere die beiden Matrizen mit der Funktion MMULT – das Ergebnis – na, ja: fast richtig. Ein bisschen Abweichung ist halt häufig in Excel:
Wenn ich die berechneten Zahle der inversen Matrix per Hand eingebe, erhalte ich eine korrekte Einheitsmatrix. Die Rechenungenauigkeit liegt also bei MINV.
Ich trage einige Zahlen in Excel ein. Schalte den Autofilter ein, filtere die Daten. Unter der Liste ein Klick auf das Summen-Symbol – die Funktion TEILERGEBNIS mit dem Parameter 9 wird verwendet. Nur die gefilterten Daten werden summiert.
Ich markiere eine Zeile und blende sie aus:
Ich bin irritiert: Die ausgeblendete Zeile wird nicht summiert.
Irritiert deshalb, weil die Hilfe erläutert, dass der Parameter 109 die ausgeblendeten und gefilterten Daten nicht summiert, der Parameter 9 jedoch nur die gefilterten.
Ich schalte den Filter aus, blende die Zeile aus – sie wird JETZT nicht mitsummiert.
Das heißt: der Parameter 9 summiert Werte von ausgeblendeten Zeilen, wenn kein Filter gesetzt ist, summiert sie jedoch nicht, bei einem eingeschalteten Filter.
Amüsiert war ich schon. In einer Firma sollte ich letzte Woche Excel-Formeln anpassen. Man erklärte mir, dass jeder Auditvorgang ein Revisionsdatum hat. Möglicherweise auch ein zweites und ein drittes. Wenn es ein drittes Datum gibt, gibt es auch ein zweites. Also: die Tabelle ist gefüllt: entweder 1. Datum oder 1. und 2. oder 1. und 2. und 3. oder alle vier Spalten sind mit einer Datumsinformation gefüllt.
Ein Kollege hatte eine Formel erstellt:
=WENN(H2=““;1;WENN(I2=““;2;WENN(J2=““;3;4)))
Meine Aufgabe war es diese Formel für weitere Datumsangaben anzupassen. Ich war etwas irritiert.
=ANZAHL(G2:J2)
hätte das Gleiche getan. Und ist leichter anzupassen. Die Teilnehmer waren begeistert und glücklich.
Heute in der Excel-Schulung. Ein Teilnehmer zeigt mir seine Lösung, wie er die Anzahl der Zahlen im 90er-Bereich ermittelt hat: Er berechnet die Differenz zweier ZÄHLENWENN-Funktionen:
Ich zeige seinen Ansatz der Gruppe. Mit dem Funktionsassistenten rufe ich Zählenwenn auf:
Schritt 1
Normalerweise trägt Excel im Funktionsassistenten um die Bedingung <100 automatisch die Anführungszeichen ein. Jedoch ein Klick in die Bearbeitungsleiste lehrt mich eines Besseren:
Schritt 2
Ich fahre fort: tippe ein Minus und rufe erneut den Funktionsassistenten auf:
Schritt 3
Der Blick in die Bearbeitungsleiste zeigt mir, dass die Anführungszeichen in der ersten Funktion fehlen. Und richtig: Das Bestätigen der Funktion wird mit einer Fehlermeldung quittiert:
Schritt 4
Schade – denn gerade der Funktionsassistent nimmt Anwendern und Anwenderinnen, die mit Formeln noch nicht so sehr geübt sind, die Arbeit an vielen Stellen ab …
Also doch: Ich zeige dem Teilnehmer ZÄHLENWENNS, die ihm sehr gut gefällt …
Excel-Schulung. Ich erkläre die Funktion SUMME. Anschließend eine Übung. Unter der Spalte mit den laufenden Nummern soll die Summe der Preise gezogen werden. Die Preise befinden sich jedoch in einer anderen Spalte.
Ich schaue einer Teilnehmerin über die Schulter: Sie klickt auf die leere Zelle, in der sich die Summe befinden soll:
SUMME
Der Vorschlag wird verworfen, indem sie auf der erste Zelle der Zahlenkolonne klickt:
Hier beginnt die SUMME
Anschließend tippt sie einen Doppelpunkt:
SUMME bis …
Und schaut mich nun erstaunt an, was Excel „tut“. Zugegeben: Excel schlägt eine Zelle vor bis zu der der Bereich ausgedehnt werden könnte. Jedoch ist die Anfängerin überfordert, weil nach der Eingabe eines Doppelpunktes etwas „anderes“ in der Formel steht.
Hallo Herr Martin,
ich habe eine Liste, bei der ich Auswertungen erstellen soll. Es geht dabei um eine Gewichtung. Folgender Schlüssel liegt der Tabelle zugrunde:
Vielleicht nicht clever – aber nachvollziehbar. Der Wert „Aw“ wurde auskommentiert – deshalb die Funktion „N“. Allerdings – an einer Stelle rechnet er nicht richtig – ich finde den Fehler nicht:
Die Antwort: Man muss schon genau hinschauen. Sie haben in die Matrix weitere Informationen eingetragen. Bei Frau Weiß finden sich die beiden Texte SPX. Dort wird das „x“ natürlich auch mitgezählt. Diese Texte dürfen Sie nicht in die Tabelle schreiben.
In einer gestalteten Tabelle wurden mehrere Zellen verbunden – hier: die Zellen E1:E3:
Wählt man nun das Werkzeug „Format übertragen“
und klickt (aus Versehen?) auf andere Zellen. Am besten solche, in denen Zahlen stehen:
Die Zellen werden nun nicht zu einer Zelle verbunden, sondern der Inhalt der unteren Zelle wird lediglich ausgeblendet. Davon kann man sich mit einem Blick auf die Summe überzeugen – das Überschlagen der sichtbaren der Spalte G Zahlen kann niemals die Summe 5538 ergeben:
Der Grund: Deaktiviert man die Option „Zellen verbinden“:
so taucht die verborgene (die verborgenen) Zelle(n) wieder auf – sie waren niemals gelöscht, sondern nur ausgeblendet:
Auf dieses unglaubliche Phänomen hat mich Andreas Thehos aufmerksam gemacht – danke dafür!
In einer Liste stehen Zahlenwerte. Daneben eine Spalte mit Berechnungen, beispielsweise die Mehrwertsteuer:
Trägt man nun statt der Zelle H2 den Tabellennamen und den Zellnamen ein (dies passiert, wenn man beispielsweise bei der Formeleingabe auf ein anderes Blatt wechselt), dann hat Excel kein Problem damit.
Jedoch: Sortiert man die Liste, so erstaunt das Ergebnis: Die Werte sind falsch. Schaut man sich die Formeln an, stellt man fest, dass die Bezüge nicht sortiert wurden. Anders wenn wenn man statt Blattname!Zellname nur Zellname eingegeben hätte.
Uuuuuaaaaahhhh!
Danke an Andreas Thehos für diesen wunderbaren Hinweis!
Microsoft hat in Excel 2013 das Analysewerkzeug „Inquire“ eingeführt, das in Excel 2016 nicht geändert wurde. Damit erspart man sich die umständlich Suche, ob es Verknüpfungen, ausgeblendete Zeilen, Spalten, Blätter gibt, ob Zahlen als Text formatiert wurden, wo Formeln stecken, die einen Fehler liefern, wo Zirkelbezüge zu finden sind, …
Inquire
Damit ist Microsoft auf dem richtigen Weg. Für alle, die fremde Dateien analysieren möchten („Was hat der Kollege denn da gemacht?“) oder die zwei Dateien miteinander vergleichen möchten – ein richtiger Schritt in die richtige Richtung. Jedoch mir fehlen:
eine Anzeige für „Genauigkeit wie anzeigen“
Überhaupt einige Optionen, die Anwender zur Verzweiflung bringen können: „in Zellen mit Nullwerten eine Null anzeigen“, „anstelle der berechneten Werte Formeln anzeigen“, „Dezimalkomma automatisch einfügen“, „1904-Datumswert, „Iterative Berechnung aktivieren“, „manuelle Arbeitsmappenberechnung“ und einige andere hübsche Optionen
ausgeblendete Zeilen und Spalten zu finden ist klasse – was aber, wenn die Zeilenhöhe auf 0,1 pt gesetzt wurde?
zu schmale Spalten – Zahlen werden mit dem Zahlenzeichen ########## dargestellt.
zu viele oder widersprüchliche bedingte Formatierungen
Rundungsfehler bei Zahlenformaten
Unsinnige Zahlenformate (beispielsweise 0,0 „%“). Allerdings: Wer entscheidet, was unsinnig ist?
Zahlenformate wie ;;
Zellen mit Leerzeichen
Zellen, die Text enthalten mit einem Leerzeichen am Ende: „Rene Martin“ ist etwas anderes als „Rene Martin „
Und schließlich: Objekte: Diagramme, die auf ein Pixel verkleinert wurden, Bilder, die auf Zellen liegen und so aussehen, als wären es Elemente der Zelle oder auch weiße Rechtecke, die auf einer Zelle liegen:
Wird leider nicht vom Inquire gefunden – unter dem Rechteck befindet sich die Zahl 3000
Fazit: Guter Ansatz, muss jedoch erweitert werden. Wenn Microsoft mich fragen würde – ich könnte Ihnen viele Dinge nennen, die Anwendern Probleme verursachen.
Und: ein dankeschön an Stefan, der mir geholfen hat, das Teilchen auseinanderzunehmen.
Ich verstehe es nicht. In einer Datei zeigt Excel im Projektfenster von VBA sämtliche Tabellenblätter als Worksheet und als Datei. Obwohl Intellisense beim Tabellenname alle Methoden und Eigenschaften anzeigt, führt das Ausführen zu einem „schwerwiegenden Fehler“. Ich stehe ratlos davor …
zu Ihrem obigen Buch habe ich eine kurze Frage. Auf Seite 125 habe ich die Aufgabe 7.2.1 gelöst. Mit der Zielwertsuche komme ich auf zwei Lösungen, wie Sie anhand der beigefügten Tabelle sehen können.
Gibt es zwei Lösungsmöglichkeiten?
Freundliche Grüße
CR
Zielwertsuche
Hallo Herr R.,
in D3 steht die Formel =B3+C3 // in E3 jedoch =(B4*2)+C4.
Deshalb kommen zwei unterschiedliche Ergebnisse raus. Die zweite Tabelle ist korrekt durchgerechnet.
schöne Grüße
Rene Martin
Wie kann denn so etwas sein? Ich erhalte einen Download aus SAP, bewegen mich mit [Strg] + [↓] ans Ende der Liste (Zeile 572), markiere die Spalte (C) und lese in der Statuszeile Anzahl: 636. Wer kann hier nicht zählen?
Zählt Excel falsch?
Die Antwort: Drücken Sie erneut [Strg] + [↓]. Weiter unten auf dem Tabellenblatt befindet sich ein weiterer Bereich, der auch mitgezählt wird, den Sie aber nicht sehen …
Kennen Sie das: Ich benötige eine Liste, die sich dynamisch fortsetzt. In einer Zelle wird ein Wert eingetragen – die Nummer eines Zählers soll nur bis nur eingetragenen Nummer angezeigt werden.
Die Formel:
=WENN(A9=$B$4;A9+1;““)
funktioniert problemlos –
=WENN(A9=$B$4;““;A9+1)
jedoch nicht.
Einer geht und einer nicht.
Der Grund ist offensichtlich. Befindet sich in einer Zelle ein leerer String („“), kann Excel zwar problemlos den Ausdruck
12 = „“
auswerte und FALSCH zurückgeben. Jedoch bei den Operatoren der Ungleichheit (<, >, <= und >=):
12 < „“
liefert Excel einen Fehler. Also aufpassen: man darf nicht immer einfach rumdrehen!
Ich gestehe: Es ist mir lange Zeit nicht aufgefallen. Wahrscheinlich deshalb, weil ich die Symbole der Bedingten Formatierung in Excel nicht besonders schätze. Während man bei den Hintergrundfarben, Schriftfarben und Zellrahmen beliebige Formeln verwenden darf, ist dies bei den Skalen, Datenbalken und Symbolsätzen eingeschränkt. Genau:
Werte sind kein Problem
Den Inhalt einer Zelle mit einem festen Wert abzugleichen ist kein Problem.
Absolute Bezüge geht auch
Den Wert auszulagern und mit einem absoluten Bezug darauf zuzugreifen ist auch kein Problem.
HEUTE() funktioniert
Auch Formeln wird HEUTE() stellen kein Problem dar.
Relative Bezüge leider nicht.
Das Erstaunen ist groß, wenn man relative Bezüge verwendet. DAS ist verboten. Noch einmal hinschauen – das Kombination bietet „Formel“ an und nicht „Formel mit absolutem oder keinem Bezug“.
Wir wissen, dass Excel eine Rechenungenauigkeit hat. Aber so ungenau?
Man nehme drei Zahlen (beispielsweise Körpergrößen) und berechne den Mittelwert (C6). Von jeder der drei Körpergrößen wird die Differenz zum Durchschnitt berechnet (D2:D4). Diese drei Zahlen werden summiert (D6) – das Ergebnis sollte eigentlich 0 ergeben. Eigentlich …
Bei unserem letzten Excel-Stammtisch haben wir uns darüber unterhalten, wann eine Formel einfacher zu verstehen ist: beim Tippen oder wenn man den Funktionsassistenten zu Hilfe nimmt. Es gibt eine Reihe von Funktionen, da erhalten Sie Informationen beim Tippen, beispielsweise SVERWEIS, TEILERGEBNIS oder WOCHENTAG:
Assi vs. Tippen
Bei anderen Funktionen ist es umgekehrt, beispielsweise RUNDEN:
Assi vs. Tippen
Heute habe ich mir die Funktion RÖMISCH angesehen und musste schmunzeln:
Assi vs. Tippen
„Typ ist eine Zahl, …“ ist völlig aussagelos.
„Knapper, knapper und knapper“ ist auch nicht sehr (vielver-)sprechend und wenig aussagekräftig.
Zur Ehrenrettung von Excel (und denen, die Hilfetexte erstellen) muss gesagt werden, dass das QuickInfo erläutert, dass:
„In dem Tool wird der Wert f einer F-Statistik (oder F-Verhältnis) berechnet. Ein f-Wert nahe 1 beweist, dass die Varianzen der Grundgesamtheiten gleich sind. In der Ausgabetabelle: Wenn f < 1 ist, gibt „P(F <= f) einseitig“ die Wahrscheinlichkeit an, dass ein Wert der F-Statistik beobachtet wird, der kleiner als f ist, wenn die Varianzen der Grundgesamtheiten gleich sind und „Kritischer F-Wert bei einseitigem Test“ einen kritischen Wert kleiner als 1 für die ausgewählte Irrtumswahrscheinlichkeit Alpha angibt. Wenn f > 1 ist, gibt „P(F <= f) einseitig“ die Wahrscheinlichkeit an, dass ein Wert der F-Statistik beobachtet wird, der größer als f ist, wenn die Varianzen der Grundgesamtheiten gleich sind und „Kritischer F-Wert bei einseitigem Test“ einen kritischen Wert größer als 1 für Alpha angibt.“
Conrad Carlberg kommentiert diese Erläuterungen in seinem Buch „Statistical Analysis: Microsoft Excel 2010“ auf Seite 157:
ich habe ein Problem. Ich habe in einer Spalte Zahlen. Ich möchten von diesen Zahlen die letzten drei Stellen, also die drei Ziffern vor dem Komma abschneiden. Der Rest soll gerundet werden. Mit welcher Formel mache ich das?
so?
Die Antwort: Dafür gibt es leider keine Formel. Aber wenn in G2 der Wert stehen, dann erhalten Sie den gewünschten Wert mit der Formel:
ich finde in Excel einfach keine Funktion QUARTALSENDE. Konkret: Ich benötige den letzten Tag (als Datum) des Quartals, in dem sich ein Datum befindet. Also beispielsweise:
1.1.2016 -> 31.03.2016
2.2.2016 -> 31.03.2016
5.5.2016 -> 30.06.2016
und so weiter.
Ist das Quartal nicht zu Ende?
Das ist richtig: DIESE Funktion gibt es in Excel leider nicht. Man muss sie zusammenbauen. Wenn in A1 das Datum steht, dann beispielsweise so:
(beide Funktionen suchen den letzten Monat des Quartals, addieren 1 (also verwenden den nächsten Monat) und von diesem Monat den ersten Tag. Davon wird 1 abgezogen. Oder auch so:
=MONATSENDE(A1;REST(3-MONAT(A1);3))
Man ermittelt die Anzahl der Monate, die zu dem Datum dazu gezählt werden muss.
Kennen Sie folgendes Phänomen? In Excel-Schulungen werde ich ab und zu gefragt:
Ich zeige die Funktion SUMMEWENNS, die Teilnehmer schauen mir zu. machen anschließend mit und fragen mich nun, wo denn die Eingabefelder bei Ihnen verborgen sind.
Richtig, wenn man mit dem Funktionsassistent die Funktion SUMMEWENNS öffnet, sieht sie folgendermaßen aus:
Start
Trägt man die Informationen ein, öffnet sich ein weiteres Pflichtfeld – allerdings erst dann:
und go!
Sehr unglücklich gemacht, dass nicht die ersten DREI Parameter angezeigt werden. Kein Trost: Während bei ZÄHLENWENNS die Parameter korrekt angezeigt werden, fehlen sie bei MITTELWERTWENNS ebenso.
Man kann einer Zelle einen Namen geben und verweist nun in Formeln auf diesen Namen und damit auf die Zelle.
Man kann einem Bereich einen Namen geben und kann nun mit dem Namen rechnen.
Man kann über den Namensmanager einem Namen einen festen Wert als Konstante zuweisen.
Man kann über den Namensmanager einem Namen einen dynamischen Bereich zuweisen, der mit einer Formel ermittelt wird.
Vor allem: Name sind sprechend und können so leicht verwendet werden, wenn man das Konzept verstanden hat. Jedoch das Konzept hat einen großen Haken:
Namen können sowohl an die Datei gebunden sein als auch an das Tabellenblatt. Wenn Sie VBA können, wissen Sie sicherlich, dass sowohl das Workbook-Objekt als auch das Worksheet-Objekt einen Namen haben können.
Kostprobe gefällig: Markieren Sie ein Zelle oder einen Bereich auf einem Tabellenblatt. Geben Sie ihm einen Namen. Kopieren das Blatt in eine andere Datei. Kopieren Sie das Blatt noch einmal in eine andere Datei. Was passiert? Nicht die Datei, sondern die beiden Tabellenblätter haben eine Zelle oder einen Bereich, die den gleichen Namen tragen. Wenn man nun eine Formel verwendet:
Wo sind test2 und test3?
ist nicht klar, von welchem Blatt sich Excel den Bereich zieht. Der Namensmanager zeigt es deutlich an:
Mehrere gleichlautende Namen
Die zuerst erstellten (hinüberkopierten) Namen gelten global für die Abreitsmappe (und darauf nimmt test1, test2 und test3 Bezug), die danach erstellten Namen gelten nur lokal für das Blatt.
Das heißt ganz einfach: Höllisch aufpassen mit den Namen! Und: bloß keine Namen mehrmals verwenden! Sonst ist Chaos vorprogrammiert.
Das heißt auch: gutes Konzept, aber leider nicht ganz durchdacht.
Wahrscheinlich haben Sie wesentlich schneller eine Lösung parat als ich, die ich nun schon stundenlang damit rummache und jetzt irgendwie aufgebe.
Es geht um dieselbe Tabelle, also diese Zeitberechnung.
Nun soll aber noch folgendes dazu berechnet werden.
Wenn die Stundenanzahl >= 6 Stunden sollen 0,5 Stunden abgezogen werden, wenn die Stundenanzahl >= 10 Stunden soll eine Stunde abgezogen werden.
Ich habe versucht, mich langsam heran zu tasten und habe sogar die > 6 Stunden und >10 Stunden einzeln herausbekommen. Allerdings bekomme ich nun beim besten Willen diese ganzen Schachteln nicht zusammengesetzt…(Das mit dem „=“ dazu hat auch nicht so funktioniert…)
Haben Sie eine (schnelle) Lösung oder eine passende Erklärung mit einem Link?
Die Tabelle mit meinen Versuchen ist angehängt. Wie ich gelesen hatte, muss man dazu die Uhrzeit wieder in eine normale Zahl formatieren, was ich getan habe.
Herzlichen Dank für Ihre Bemühungen.
####
Stundenberechnung
Hallo Frau P.,
Ich denke, Ihr Problem resultiert aus Folgendem: Sie sollten sich einen Baum malen, der die verschiedenen Fälle auflistet: tagsüber gearbeitet oder nachts? In beiden Fälle: mehr als 10 Stunden – > ja: eine Stunde abziehen. Nein: – > wenn mehr als sechs Stunden: -> ja, 0,5 Stunden abziehen – sonst nichts.
Und so müssen Sie die Formel eingeben.
Kommen Sie damit klar?
####
Hallo Herr Dr. Martin,
Jetzt habe ich gesehen, dass Sie die Sachen ja alle schon richtig ausgefüllt haben.
Das Resultat ist in „Zahl“ formatiert. Ich glaube, es wäre auch mit der „Uhrzeit 37:…“ gegangen. Allerdings nicht mehr in dieser Version.
Ich kann es nur hinnehmen und Ihnen danken, dass Sie es eingerichtet haben. Vom Verständnis bin ich leider noch etliches entfernt.
Aber ich denke, meine Kolleginnen werden sich freuen, endlich eine funktionierende Zeitberechnung zu haben. Bleibt zu hoffen, dass ihnen nicht noch etwas Neues einfällt…
Also noch einmal: Herzlichen Dank!
Ich wünsche Ihnen noch einen schönen Tag.
####
Gerne, Frau Pojer,
wenn Sie es verstehen möchten, machen Sie Folgendes:
Klicken Sie auf eine Zelle mit einer Formel. Klicken Sie auf den Funktionsassistenten.
Sie sehen die Bedingung. Klicken Sie nun in die Bearbeitungszeile auf den Teil den Sie sehen möchten. So können Sie sich den Baum entlanghangeln:
Der Funktionsassistent
Umgekehrt: gehen Sie an ein solches Problem mit einem Blatt Papier und einem Stift ran. Ich „mal gerne Bäume“. Sicherlich kennen Sie diese Ablaufdiagramme: sie helfen:
Ich weiß; ich bin ja auch nicht glücklich darüber. Wir haben eine Liste, in der in einer Spalte dummerweise Datumsangaben, Texte, Zahlen (Kennziffern) und gar nichts eingetragen wird. Ich habe nun die Aufgabe, über eine Hilfsspalte die Zellen herauszufinden, in denen ein Datum steht. Allerdings: Wenn ich die Funktion ISTZAHL verwende, werden auch die „echten“ Zahlen gefunden. Die will ich aber nicht. Gibt es wirklich keine Funktion „ISTDATUM“? Auch nicht in Excel 2016?
Wo ISTDATUM?
Die Antwort: Wenn es möglich ist, prüfen Sie die interne Zahl, also beispielsweise:
=UND(I4>40000;ISTZAHL(I4))
Oder Sie verwenden die Funktion ZELLE. Sie liefert mit dem Parameter „format“ den Wert D1, der anzeigt, dass es sich um ein Datum handelt:
Nein, konsequent ist Excel nicht. An einigen Stellen muss man bei Texten Anführungszeichen eintragen, bei einigen kann man an anderen Stellen wiederum darf man nicht.
darauf hingewiesen. Bei der Funktionseingabe ist Excel auch inkonsequent:
Während man bei Tippen einer Funktion immer die Anführungszeichen eingeben muss, muss man es im Funktions-Assistenten nicht. Das Wort Achtung wird beim Verlassen des Textfeldes umgewandelt in „Achtung“. Jedoch: Wenn man im Funktionsassistenten eine innere Funktion einträgt, beispielsweise
und(B2=Fotografie;G2>100)
wird dies mit einer Fehlermeldung quittiert. Nicht konsequent, oder?
Manchmal muss man, manchmal muss man eben nicht.
Meine Empfehlung: Wenn Sie nicht wissen, ob man muss oder nicht: Tragen Sie die Anführungszeichen ein.
Hallo. Ich brauche einen Tipp. Ich erhalten einige Male im Jahr eine Tabelle mit vielen Zahlen. Da die Zeilen keine eindeutige ID haben, habe ich mit der Funktion VERKETTEN mehrere Felder konkateniert (zusammengefasst), so dass ich eine ziemlich eindeutige ID habe. Ich wollte diese Formel weiter runterziehen, weil sich die Liste ab und zu erweitert. Jedoch – Excel zeigt in den leeren Zeilen Fehler. Das verstehe ich nicht: leer & leer & leer müsste doch leer sein, oder?
VERKETTEN – klappt gut, aber irgendwann mit Fehler
Die Antwort: Sie haben die Tabelle in eine „intelligente Tabelle“ verwandelt (Einfügen / Tabellen / Tabelle). Die Formel in der Zelle lautet:
also: Tabellenname Tabelle1[Spaltenname]. Das impliziert: gleiche Zeile wie Formelzelle.
Allerdings befindet sich keine Tabelle neben den neuen Zellen unterhalb der Tabelle. Wenn es Sie stört, können Sie um die Funktion die Formel WENNFEHLER bauen, also:
Heute in der Excel-Schulung. Eine Teilnehmerin zeigte mit eine Datei. Mit fiel auf, dass der Filter (hier: Black-Mitglieder) eine andere Zahl lieferte als die Funktion ZÄHLENWENN. Eine Pivottabelle lieferte das gleiche Ergebnis wie der Filter. Die Ergebnisse von ZÄHLENWENN waren um 1 zu groß.
Kann Excel nicht bis drei zählen?
Ich gestehe – ich habe eine Weile gesucht. Bis ich entdeckt hatte, dass über der Tabelle einige Zellen ausgeblendet waren. Und: richtig – dort stand die Liste, die als Bereich für die Datenüberprüfung verwendet wurde. Da ZÄHLENWENN die ganze Spalte zählte … Tja – halt einer zu viel.
Verblüffend – ich kann es nicht nachvollziehen. Es gibt Anwender, die tragen eine Formel in das Suchen-Feld des Funktionsassistenten ein. Manchmal findet er sie allerdings nicht. Ich kann das Phänomen nicht immer nachvollziehen; meisten klappt die Suche.
Wer suchet der findet – ich bin mir nicht so sicher …
Ich weiß, ich weiß – Excel hat Rundungsprobleme. Die Sache mit der Gleitkommadarstellung und -berechnung ist schon hinlänglich beschrieben worden, beispielsweise bei:
Aber ist jemandem schon Folgendes aufgefallen: Trägt man in Excel die Zahlen -0,3 und -0,2 untereinander in zwei Zellen ein, markiert diese und zieht sie nach unten, so klappt es. Ebenso bei -1,3 und -1,2. Jedoch bei -2,3 und -2,2 steht beim Herunterziehen in der entsprechenden Zelle nicht 0 sondern der Rundungsfehler -9,76996261670138E-15. Ebenso bei -3,3 und -3,2. Jedoch taucht dieser Fehler bei den Startwerten -2,4 und -2,3 nicht auf.
Ein seltsam Ding – ganz rund ist es ja nicht …
Übrigens: einen anderen Rundungsfehler erhält man auch bei der Berechnung:
Anwender reiben sich verwundert die Augen: In Excel 2013 wurde die Funktion TAGE eingeführt. Damit kann man die Differenz zweier Datumsangaben in Tagen berechnen. Warum diese Funktion? Keine Ahnung – Excel kann doch seit Beginn seiner Existenz Datum2 – Datum1 berechnen. Da Datumsangaben intern als serielle Zahlen verwaltet werden, war das nie ein Problem in Excel. Auch die Funktion =DATEDIF(Anfangsdatum;Enddatum;“D“) konnte dies und hat das Gleiche erledigt. Ein Blick in die Liste der Funktionen: Nein, Excel 2013 hat leider keine Funktion MONATE und auch keine JAHRE. Vielleicht haben sie es vergessen. Wir warten auf die Version 2016. Erstaunt reiben wir uns hier die Augen – nein, sorry, auch in der aktuellen Version 2016 gibt es keine Funktion MONATE oder JAHRE. Geduld … vielleicht in der nächsten Version.
Vor ein paar Tagen erreichte mich folgende Anfrage:
Sehr geehrte Damen und Herren,
zu dem in Betreff genannten Thema haben wir noch eine Frage: Die Videoanleitung zum Erstellen eines Internationalen Kalenders konnten wir gut nutzen. In dieser Anleitung wird u.a. geschildert, wie man Feiertage durch eine bedingte Formatierung farblich hervorhebt. Ganz schick wäre es noch, wenn zu diesem farblich markierten Feiertage auch automatisch der Feiertagsname mit angezeigt werden könnte. Ein entsprechendes Tabellenblatt mit diesen Informationen wurde im Verlaufe der Anleitung angelegt. Bei festen Feiertagen wie Neujahr. 1. Mai etc, könnte man dies händisch lösen, doch bei variablen Feiertagen wie Ostern und Pfingsten etc. wäre es wünschenswert, wenn diese gleich automatisch mit angezeigt werden. Leider wurde in dieser Videoanleitung nicht darauf eingegangen. Mit welcher Funktion kann der Feiertagsname automatisch angezeigt werden? Danke schon vorab für Ihre Hilfe.
Mit freundlichen Grüßen / with best regards
#####
Zur Info: Ich habe einen Kalender erstellt, der – nach Änderung des Jahres die Feiertage farblich kennzeichnet. Das klappt mit der bedingten Formatierung und der Funktion ZÄHLENWENN gut und einfach. Die Feiertage (hier: die bayrischen) habe ich auf ein zweites Tabellenblatt ausgelagert.
Die Feiertagsliste
Der Kalender
Ich habe zirka eine halbe Stunde benötigt, damit die Feiertage angezeigt werden – eine hübsche kleine Fingerübung:
Das Ergebnis
Wer knobelt mit? Den ersten Kalender könnt Ihr unter Kalender herunterladen.
Einfache Frage – einfache Antwort: Wenn Zellen markiert werden, in denen sich Datumsangaben befinden, summiert Excel die internen seriellen Zahlen. Das kann erstaunen und vielleicht verwirren …
ich habe Ihr o. g. Training sehr interessiert durchgearbeitet (fast alles) und finde es auch äußerst hilfreich.
Nun habe ich folgendes Problem bei Ihrem Punkt Excel-Rechnen mit Uhrzeiten.
Ich habe eine Tabelle, in die Studierende Ihre Arbeitsstunden dokumentieren müssen. Wenn ich alles genau so formatiere wie Sie es in Ihrem Film gezeigt haben, sind die noch nicht ausgefüllten Zellen bei 24:00 Stunden. Eine Summenberechnung führt zu sehr hohen Stundenwerten. Eigentlich müsste ja 0:00 drin stehen, doch tut es bei der Berechnung mit 1-C3+B2 (Beispiel) nicht.
Bei einer Tabelle wie der Ihren im Film ist das kein Problem, da sie vollständig ausgefüllt ist. Bei meiner nicht ausgefüllten Tabelle ist es leider ein Problem. Vielleicht könnten Sie mir hier helfen. Wahrscheinlich gibt es auch da einen kleinen Trick mit großer Wirkung.
Nix arbeiten – und Stunden abrechnen!
Sehr geehrte Frau P.,
habe ich das SO in meinem Video gezeigt? Dann ist mir ein Fehlerchen unterlaufen. Asche auf mein Haupt! Peinlich!
In der Zelle D2 stand die Formel:
=WENN(B2<C2;C2-B2;1-B2+C2)
Wenn B2 und C2 leer sind, dann ist B2 nicht kleiner als C2 – also wird gerechnet: 1 (Tag) – keine Uhrzeit – keine Uhrzeit → also: 1 Tag.
Ich habe eine Tabelle mit Abteilungsnummern. Da einige Abteilungen in der Form 4.2.2 oder 3.1.5 vorliegen, muss ich sie als Text formatieren oder ein Apostroph voranstellen. Soweit so gut.
Da ich das Apostroph nicht mag, ignoriere ich den „Fehler“ (es ja kein Fehler).
Fehler: ignorieren!
Sieht prima aus:
klasse!
Allerdings: Sobald ich die Abteilungsnummer ändere – beispielsweise von 4.1.1 zu 4.1.5 – erscheint das grüne Eck von Neuem. Ich wollte doch den Fehler ignorieren:
hallo – ich bin wieder da!
Die Antwort: Excel hat nur die globale Einstellung: „Fehlerüberprüfung nicht aktivieren“ in den Optionen oder kann lokal einen Fehler zu ignorieren. Sobald der Inhalt geändert wird, wird die Fehlerüberprüfung wieder aktiviert. DAS kann man leider nicht abstellen – das heißt: es gibt keine Option HIER bitte nicht mehr fragen. Das heißt: Sie müssen sich leider für eine der beiden Varianten entscheiden: IMMER deaktivieren oder lokal ausschalten, und wenn die Informationen geändert werden, dann erneut ausschalten.
Ist Ihnen folgender erklärbarer, aber auf den ersten Blick verwirrender Algorithmus aufgefallen?
In einer Exceltabelle befindet sich ein Zahlenblock. Klickt man in der er ersten Zelle daneben auf das Summensymbol, schlägt Excel die Reihe links daneben als Bereich vor. Eine Zelle tiefer wird ebenfalls die Zeile daneben vorgeschlagen. In der dritten Zeile jedoch die beiden Zahl (also die Summen) darüber.
Das Algorithmus, der dahinter steckt, ist klar: Excel überprüft zuerst die Zellen darüber. Befindet sich keine Zahlen darin, werden die Zellen links neben der aktuellen Zelle geprüft (F2). Bei F3 erkennt Excel die Summe darüber und schlägt die gleiche Formel erneut vor. Würde in der Zelle darüber keine Funktion, sondern eine Zahl stehen, würde diese vorgeschlagen werden. In der dritten Zelle F4 werden zwei Formeln über der aktuellen Zelle (mit Zahlen als Ergebnis) erkannt und nun diese vorgeschlagen.
Okay – übersichtlicher wird die Formel sicherlich nicht, aber man kann mit der Funktion N etwas hineinkommentieren. Zu Erläuterung: N(„Beliebiger Text“) liefert den Wert 0. Und an den entsprechenden Stellen in einer Funktion kann man so etwas kommentieren. Einem Teilnehmer meiner Schulung gefiel dies heute sehr.
Es ist schon ein seltsames Ding um Excel. Trägt man in eine Zelle den Text WAHR ein und multipliziert diese Zelle (genauer: den Wert dieser Zelle) mit 1, so erhält man 1. Bei FALSCH lautet das Ergebnis 0. Das bedeutet, dass WAHR = 1 und FALSCH = 0.
Eben. In vielen Funktionen, die die beiden Parameter 0 oder 1 verlangen, kann man auch WAHR und FALSCH eingeben. Und umgekehrt.
Beispielsweise SVERWEIS. Oder RMZ. Analog: ZINS, ZZR, BW.
Jedoch: =KUMZINSZ(2,5%/12;12*10;50000;1;10;1) berechnet korrekt -901,01. Allerdings liefert =KUMZINSZ(2,5%/12;12*10;50000;1;10;WAHR)
einen Fehler. Bei KAPZ und ZINSZ darf ich bei dem Parameter F die beiden Werte 1 oder WAHR (beziehungsweise 0 oder FALSCH) eintragen. Muss ich das verstehen?
Hallo – und ich würde gerne Formeln finden. Ich weiß, dass auf der Tabelle Formeln liegen – beispielsweise in den Zellen J6:J12. Aber Excel findet sie nicht!
Vom Suchen und Finden
Die Antwort: Sie dürfen nur eine Zelle auswählen! In Ihrem Beispiel haben Sie die Zellen A1:F1 ausgewählt (was man aufgrund der grünen Farbe nicht sehr deutlich sieht). Und DORT findet Excel KEINE Formeln. Also: Markierung auflösen – dann wird die Suche mit Erfolg gekrönt.
ich habe die Formel abgeschrieben, die Sie in Ihren Buch veröffentlicht haben. Ich wollte aus einer Entfernungsliste die Distanz zweier Orte ermitteln. Aber es klappt einfach nicht!
Ist da ein Fehler in der Formel?
Es ist doch alles richtig, oder?
Die Antwort: Sie müssen ganz genau hinschauen. Wahrscheinlich sind Sie beim Abtippen der Formel eine Taste zu weit nach links gerutscht und haben fälschlicherweise statt eines $-Zeichens ein %-Zeichen getippt. Excel quittiert das mit einem Fehler!
Ey – ich bin so genervt! Ich weiß, dass ich eine Spalte, in der sich Formeln befinden, kopieren kann und dann über „Inhalte einfügen“ die Formeln in Werte verwandeln kann. Aber was macht die Schweinebacke? – Richtig: Völliger Blödsinn!!! Schreibt einfach ANDERE Formeln rein. Das kanns doch wohl nicht sein“
Kopieren | Inhalte einfügen
Und dann das!!!
Die Antwort: Du hast aus Versehen die Option „Multiplizieren“ gewählt – DIE muss ausgeschaltet werden – auf „Vorgang: Keine“. Dann funktioniert es auch.
Ich will das auch sehen. Wenn mein Kollege die Funktion ZÄHLENWENN verwendet, sieht er bereits im Dialog das Ergebnis:
Das Ergebnis der Formel wird angezeigt.
Bei mir jedoch nicht:
Hier nicht!
Erst wenn ich den Assistenten mit OK bestätige, steht das richtige Ergebnis in der Zelle. Warum bei mir nicht?
Die Antwort: Ihr Kollege sucht den Ort, indem er dort bereits die Anführungszeichen setzt. Sie haben keine Anführungszeichen eingegeben – de Text – hier Ulm – wird noch nicht als Text erkannt. Erst beim Bestätigen interpretiert der Funktionsassistent die drei Buchstaben Ulm als Text und ermittelt dann das richtige Ergebnis (und schreibt die Anführungszeichen in die Formel).
Ich habe in der letzten Excelschulung gelernt, dass man mit Textfunktionen Texte „manipulieren“ kann. Das wollte ich ausprobieren.
Ich habe eine Liste mit Vor- und Zunamen. Die Vornamen löse ich mit:
=LINKS(A2;FINDEN(“ „;A2)-1)
heraus. Klappt prima. Bei den Nachnamen bei der Formel
=RECHTS(A2;FINDEN(“ „;A2)-1)
macht er aber bei einigen Namen Blödsinn. Warum?
RECHTS klappt nicht!
Das Problem: Die Funktion FINDEN (oder auch SUCHEN) findet einen Text innerhalb eines anderen VON LINKS. Das bedeutet: Sie schneiden aus dem Text VON RECHTS so viele Buchstaben heraus wie der Vorname lang ist. Das kann zufälligerweise funktionieren, normalerweise aber nicht. Sie lösen das Problem entweder mit der Gesamtanzahl der Buchstaben LÄNGE:
=RECHTS(A2;LÄNGE(A2)-FINDEN(“ „;A2))
oder mit der Funktion TEIL, die ab einem bestimmten Zeichen herausschneidet:
=TEIL(A2;FINDEN(“ „;A2)+1;999)
(zugegeben: die Zahl 999 ist „geschummelt“ – Sie schneiden nach dem Leerzeichen 999 (oder eine noch höhere Anzahl Buchstaben heraus) – aber es funktioniert)
Der erste Teil ist klar (=WENN(AJ2=““;““): Wenn die Zelle AJ2 leer ist, dann bleibe ich selbst auch leer. Prima.
Im zweiten Teil wird überprüft, ob in AJ2 ein Wert größer als 14 steht:
WENN((AJ2)>14;“zu lang“
Wenn das erfüllt ist, so liefert die Formel den Text „zu lang“. Nun wollte der Kollege überprüfen, ob der Wert kleiner oder gleich 14 ist. Er hat dies mit einer weiteren WENN-Funktion realisiert. Das ist nicht falsch, aber überflüssig.
Ich würde die Formel folgendermaßen schreiben:
=WENN(AJ2=““;““;WENN((AJ2)>14;“zu lang“;“ok“))
Sie liefert das gleiche Ergebnis und ist viel kürzer.
Warum finde ich die Funktion nicht? Ein Kollege hat in einer Abrechnungstabelle für Kopierer unserer Firma eine Funktion DATEDIF eingefügt, die offensichtlich die Anzahl der Monate zwischen Vertragsbeginn und Vertragsende berechnet. Allerdings – in der Liste der Funktionen taucht sie nicht auf. Wo ist sie denn?
DATEDIF – nicht da!
Die Antwort: Stimmt! Diese Funktion finden Sie nicht in der Liste der Funktionen im Funktionskatalog. Sie wurde aus Kompatibilitätsgründen zu Lotus 1-2-3 aufgenommen. Seit 2003 wurde diese Tabellenkalkulation nicht mehr weiterentwickelt, 2014 wurde der Support sogar eingestellt. Trotzdem finden sich noch immer ein paar Relikte von Lotus 1-2-3 in Excel. Zum Beispiel diese Funktion. Und: Wenn Sie auf das Symbol für den Funktionsassistenten f(x) klicken erhalten Sie die Funktionsargumente im Assistenten.
Warum klappt das nicht? Ich habe versucht – genau wie im Internet erklärt – einen Kalender zu erstellen. Ich habe die Feiertage berechnet und versuche auf meinem Kalender die Feiertage mit der Funktion ZAEHLENWENN anzeigen zu lassen. Aber er tut es nicht. Nicht einen einzigen Feiertag!
Die bedingte Formatierung funktioniert nicht.
Die Antwort: Sie haben statt ZÄHLENWENN ZAEHLENWENN geschrieben. Erstaunlicherweise übergeht die bedingte Formatierung Tippfehler in den Funktionsnamen und – liefert gar nichts!
TEILERGEBNIS. Ich versteh mal wieder gar nichts. Ich habe eine Liste. Darunter stehen drei Funktionen:
=SUMME(C2:C42)
=TEILERGEBNIS(9;C2:C42)
=TEILERGEBNIS(109;C2:C42)
Drei mal erhalte ich den Wert 20.205.490.
TEILERGEBNIS
So weit, so klar. Wenn ich nun filtere, liefert TEILERGEBNIS mit der 9 das Gleiche wie TEILERGEBNIS mit der 109. Natürlich einen anderen Wert wie die Summe.
TEILERGEBNIS bei gefilterten Daten
Ich schaue in die Hilfe, um den Unterschied zwischen der Konstante 9 und 109 – beides Mal die Funktion SUMME zu ermitteln. Dort lese ich:
„ist eine Zahl von 1 bis 11 (bezieht ausgeblendete Werte ein) oder von 101 bis 111 (ignoriert ausgeblendete Werte), die festlegt, welche Funktion bei der Berechnung des Teilergebnisses innerhalb einer Liste verwendet werden soll.“ HÄ? Ich habe doch ausgeblendet. Trotzdem ist das Ergebnis das Gleiche!
Die Hilfe – nicht wirklich eine Hilfe …
Die Antwort: „ausgeblendete Werte“ ist unglücklich formuliert. Excel meint die Zelle, die SIE ausgeblendet haben (beispielsweise mit der rechten Maustaste oder [Strg] + [9]). Er bezieht sich dabei nur auf „manuell“ ausgeblendete Zellen oder durch Gruppierung ausgeblendete Zellen, aber nicht auf ausgeblendete Zellen durch Filtern!
Beim Ausblenden von Zeilen wird der Unterschied zwischen 9 und 109 beim TEILERGEBNIS deutlich.
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!
Hallo. Ich habe eine Frage. Ich weiß, dass Excel nicht alles kann. Aber fragen kann man ja mal.
Ich habe für meinen Chef eine Liste in Excel erstellt. Habe über Erstellen / Tabelle eine Tabelle daraus gemacht. So weit, so gut.
Die Tabelle
Ich klicke in eine andere andere Zelle nebenan und erzeuge eine Summe:
Eine Spalte wird summiert.
Die Formel lautet:
=SUMME(Tabelle1[April])
Über ein Dropdownfeld kann man die Monate auswählen:
Die Monatsliste
Und nun meine Frage. Eigentlich liebe ich die Funktion INDIREKT. Eine prima Sache. Aber kann sie nicht diese Formel zusammenbauen? Etwas so:
=SUMME(„Tabelle1[„&INDIREKT(„O2″)&“]“)
INDIREKT liefert einen Fehler.
Die Antwort: Nein – nicht ganz – Sie müssen die Anführungszeichen anders setzen und INDIREKT VOR den gesamten Text stellen. Wenn Sie Formel wie folgt zusammenbauen, dann klappt es:
Hallo. Ich habe schon eine ganze Weile gesucht, bis ich herausgefunden habe, warum er die gallischen Tiere falsch zählt. Eigentlich müsste die Formel ANZAHL2 die Zahl 2 ergeben und nicht 4:
ANZAHL2 zählt falsch
Die Antwort habe ich nach langem Suchen gefunden: In einigen Zellen habe ich aus Versehen ein Leerzeichen eingegeben. Klar – das sehe ich nicht; das wird als Text mitgezählt.
Die Wurzel des Übels
Aber nun meine Frage: Wenn ich die Liste filtere – warum zeigt der Autofilter nicht an, dass einige Zellen Leerzeichen enthalten?
Die Antwort: Ja – Sie haben recht – der Autofilter übergeht zum Glück (oder leider?) die Leerzeichen. Der Vorteil: „Asterix“ und „Asterix “ (mit einem Leerzeichen am Ende) werden vom Filter als gleicher Text behandelt. Der Nachteil: Der Filter hilft nicht diese Leerzeichen, die an anderen Stellen Probleme verursachen, aufzufinden.
Der Autofilter übergeht Leerzeichen am Ende des Textes.
Man kann die Texte mit Suchen ([Strg]+[F]) auffinden. Oder mit Funktionen:
=LÄNGE(C2)
=WENN(LINKS(C2;1)=“ „;“x“;““)
Oder mit [Strg]+[↓] können Sie den Cursor nach unten versetzen; er springt nun zur ersten Zelle, in der etwas steht; stoppt also auch bei den Zellen, die mit einem Leerzeichen gefüllt sind.
Bei meinem Kollegen ist alles irgendwie anders. Ich weiß auch nicht warum!
Ich habe die Datei genauso nachgebaut wie bei ihm; aber er zeigt mir bei der Formel nicht als Ergebnis den Wert „0“, sondern gar nichts. Haben Sie eine Erklärung?
Anzeige ohne 0
Klar. Bei Ihnen ist mit Sicherheit die Option „In Zellen mit Nullwerten eine Null anzeigen“ ausgeschaltet. Deshalb wird beim Zahlenformat Standard, Zahl oder Währung nichts angezeigt, wenn die Formel den Wert 0 berechnet. Übrigens: „Buchhaltung“ würde hier „- €“ anzeigen.
Das ist schön, dass Excel ein Problem bei dieser Formel feststellt – aber das hilft mir leider nicht weiter. Haben Sie eine Erklärung?
Fehlermeldung
Hierfür kann es sicherlich mehrere Ursachen geben. Dummer Tipp: Versuchen Sie es noch einmal! Vielleicht haben Sie einfach [Enter] gedrückt oder mit der Maus auf [OK] geklickt ohne etwas einzugeben, beziehungsweise zu markieren:
Wenn ich eine Formel (per Tastatur) eingeben will, z. B. =Teilergebnis(…), dann zeigt mir Excel nach zwei Buchstaben auch schon die richtige Formel in einem Feld an, die ich dann mit den Coursor-Tasten auswählen kann. Wie kann ich diese Auswahl dann aber übernehmen (ohne Maus wohlgemerkt). Wenn ich nach =Te die ENTER drücke, weil dich die richtige Formel ausgewählt habe, speichert Excel nur eben =Te und erkennt es als Namen, den es natürlich nicht gibt, Ausgabe in Zelle : #Name? wie kann man denn per Tastatur die richtige Formel auswählen und bestätigen?
Nur TE und kein Teilergebnis
Die Antwort: Drücken Sie nicht die [Enter]-Taste, sondern die Tabulatortaste. Dann übernimmt Excel diese Funktion. Von Andreas Theos stammt noch der Tipp: „…und nach dem TAB noch STRG + A für den Formeldialog ;-)“ – danke!
Eine Kollegin von mir hat einen tollen Kalender erstellt. Wenn ich ihn mir jedoch genauer ansehe, dann finde ich dort seltsame Funktionen (_xlfn.ISOWEEKNUM). Sie sind nicht in einem Add-In vorhanden, hat mir die Kollegin gesagt. Ich finde die Funktionen auch nicht im Funktionsassitenten. Was passiert denn hier?
Seltsame Funktion
Die Antwort: Mit jeder Excel-Version kommen einige, wenige, neue Funktionen hinzu. Da Excel seit der Version 2007 das (fast) identische Dateiformat XLSX unterstützt, haben Sie ein kleines Problemchen. In Excel 2013 wurde beispielsweise die Funktion ISOKALENDERWOCHE eingeführt. Diese Funktion gab es in Excel 2007 und 2010 noch nicht. Wenn nun das entsprechende Servicepack auf Ihrem Rechner installiert ist oder wenn Excel 2013 installiert ist, kann diese neue Funktion verwendet werden, obwohl sie eigentlich gar nicht auf Ihrem Rechner installiert wird. Leider wird nicht der Name ISOKALENDERWOCHE angeziegt, sondern: _xlfn.ISOWEEKNUM.
Warum rechnet die Funktion ZÄHLENWENN (übrigens auch SUMMEWENN) manchmal nicht?
ZÄHLENWENN rechnet nicht.
Schauen Sie die Funktion genau an. ZÄHLENWENN (und auch SUMMEWENN) verlangt als Reihenfolge zuerst WO wird etwas gesucht und anschließend WAS wird gesucht. Wenn Sie die Reihenfolge vertauschen, zählt er, wie oft der Bereich in der einen Zelle vorkommt und liefert kein Ergebnis.
Bei meinem Kollegen funktioniert es – auf meinem Rechner aber nicht!
Ein Kollege hat auf seinem Rechner eine Formel programmiert, die bei einem Geburtstagskind „happy birthday“ anzeigt. Wenn ich diese Datei bei mir öffne, dann sehe ich zwar die Geburtstagskinder aber auch noch viele andere. Warum?
Wenn Sie genau hinschauen, fällt auf, dass das Datum in der Form DD-MM formatiert wurde, also day und month. Mit Sicherheit verwendet der Kollege eine englischsprachige Excelversion. Dort benutzt Excel DMY statt TMJ. Da diese Formatangabe als Text in Anführungszeichen in der Formel steht, wird sie nicht übersetzt (anders als beispielsweise die Formeln – aus SUM wird SUMME, aus VLOOKUP wird SVERWEIS, … Entweder Sie korrigieren die Formel in:
Warum rechnet Excel in der ersten Zelle richtig, in fast allen anderen Zellen falsch?
Excel rechnet fast überall falsch.
Wenn ich diese Frage höre, gibt es eigentlich nur eine Antwort – irgendetwas stimmt mit relativ/absolut nicht. Wenn Sie die Formel anschauen, die in C4 steht:
=B4*B1
dann müssten Sie erkennen, dass Sie eigentlich B1 fixieren müssen. Am besten, indem Sie den Cursor vor den Buchstaben B, zwischen B und 1 oder hinter die Zeilennummer 1 setzen und dann [F4] drücken. So wandelt Excel den relativen Bezug in einen absoluten (festen) Bezug um und schreibt:
Manchmal – wenn auch recht selten – erhalte ich die Fehlermeldung #ZAHL! Wann passiert denn das?
Vor allem bei mathematischen Funktionen kann dieser Fehler auftreten. Es gibt einige Funktionen, die lassen nur einen bestimmten Wertebereich zu – sonst können sie nicht rechnen, weil sie im reellen nicht definiert sind. Beispielsweise verlangt Wurzel, Logarithmus und Fakultät eine positive Zahl, Arcsin und Arccos sind für Zahlen im Bereich [-1;+1] definiert. GGT und KGV sind nur für positive, ganze Zahlen definiert.
Erhalten sie negative Werte (beispielsweise WURZEL(-1) so ist #ZAHL! das Ergebnis.
Ebenso wachsen einige Funktionen sehr schnell. Mit FAKULTÄT(171) sprengt die Grenzen von Excel – Fakultät(170) ergibt 7,2574 x 10306. Mehr geht nicht. Auch mit der Funktion Potenz kommt man an die Grenzen von Excel.
Ich habe eine Datei erhalten, die angeblich bei meinem Kollegen richtig rechnet. Bei mir sehe ich jedoch nur Fehler. Was habe ich falsch gemacht?
Fehler statt Formelergebnis
Sie haben alles richtig gemacht. Ein genauer Blick auf die Formeln zeigt, dass auf einem anderen Rechner mit der Funktion Ostersonntag gerechnet wurde. Diese Funktion gibt es nicht in Excel. Wenn Ihr Kollege sagt, dass diese Formel bei ihm funktioniert, kann es zwei Gründe haben:
1. Entweder er hat ein Add-In programmiert (oder programmieren lassen) oder zumindest installiert, das diese Formel (Ostersonntag) zur Verfügung stellt. Zwar rechnet die Formel korrekt auf seinem Rechnern, aber wird nun die Datei weitergegeben, rechnet die Funktion nicht mehr richtig auf einem anderen Rechner.
2. Er hat in einem anderen Programm gearbeitet, beispielsweise in openOffice.org oder in libreOffice. Dort existiert diese Funktion (einige der wenigen Funktionen die es in ooo oder libreOffice, aber nicht in Excel gibt). Die Datei kann als *.XLS gespeichert und weitergegeben werden. Beim Öffnen – Fehlermeldung!
Die Lösung: Sie müssen entweder das Add-In installieren oder die Formel auf Ihrem Rechner nachprogrammieren.
Seit einer Weile arbeite ich mit dem SVERWEIS. Ich habe ihn schon recht gut verstanden. Aber manchmal rechnet er nicht richtig. Warum?
SVERWEIS rechnet nicht richtig.
Dazu muss man sich die Formel genau ansehen:
=SVERWEIS(K2;$A$1:$A$32;3;FALSCH)
Sie suchen den Wert, der in der Zelle K2 steht in der Spalte A – genauer in den Zellen A1 bis A32. Soweit so gut. Sie möchten den Wert der dritten Spalte (3), also den Last Name wissen. Sie müssen den Bereich ändern: Es ist richtig – Sie suchen zwar in A1:A32, aber in der Spalte A steht nicht der Wert den Sie haben möchten. Sie müssen in der Matrix (in der Informationstabelle, in der die Daten gesucht werden), auch den Bereich einschließen, in dem sich die Daten befinden, also Spalte C. Sie können dabei gerne übers Ziel schießen, beispielsweise:
Warum ermittelt die Funktion ZÄhLENWENN (und SUMMEWENN) nicht die richtige Zahl?
Zählenwenn zählt falsch ?!
Man muss sich die einzelnen Daten ansehen. In der Bearbeitungsleiste stellt man fest, dass in einigen der Texte noch weitere Informationen („München“) stehen (sie wurden übrigens weiß formatiert). Man hätte auch durch ein Ändern der Ausrichtung auf rechtsbündig feststellen können, dass hinter dem angezeigten Text noch etwas steht.
Die Bearbeitungsleiste liefert das Ergebnis
Oder man formatiert die Zellen rechtsbündig.
Übrigens: =ZÄHLENWENN(D2:D10;“Rene Martin*“) hätte die korrekte Anzahl ermittelt.
Die Formel stimmt – aber warum rechnet die Summe nicht?
Die Summe rechnet nicht.
Erst ein Klick auf die Zellen liefert die Antwort: In den Zellen steht nicht 20, 40, 60, … sondern 20 EUR, 40 EUR, … – das heißt der Text „EUR“ wurde in die Zelle eingetragen und nicht hinzuformatiert.
Übrigens: Manche Anwender denken, dass durch ein Ändern der Ausrichtung in rechtsbündig aus einem solchen Text eine Zahl wird. Das ist natürlich nicht der Fall!
Falsche Werte in der Zelle verhindern das Rechnen.
Aber wenn man die Werte der Zellen genauer anschaut, dann fällt sofort auf, dass ein Datum als TT formatiert wurde. Oder genauer: Man sieht nur die Tageszahl des Datums:
In den ersten Zeilen rechnet der SVERWEIS noch richtig, aber dann gibt es Fehler.
SVERWEIS rechnet manchmal richtig.
Eigentlich müssten Sie den Fehler selbst finden können, wenn Sie die Formel anschauen. Der Bereich (hier: A1:B14) wurde nicht fixiert. Das bedeutet: er „wandert“ beim Herunterziehen mit nach unten. Was passiert, können Sie leicht überprüfen, wen Sie auf eine Zelle doppelklicken, die sich weiter unten befindet.
Der Bereich wurde nicht fixiert.
Wandeln Sie also A1:B14 in $A$1:$B$14 um (oder verwenden einen Namen für diesen Bereich)
Ich weiß nicht mehr, was ich gemacht habe. Ich sollte in einer Tabelle einer Kollegin die Formel für den Unterstützungsbeitrag unserer Firma anpassen. Irgendwann entdecke ich jedoch in einer Zelle die Fehlermeldung #BEZUG! Kann ich den Fehler lokalisieren? Oder die Formel wieder auf eine korrekte Form bringen?
#BEZUG!
Die Antwort: Leider nein! Wahrscheinlich haben Sie irgendwo etwas gelöscht (beispielsweise eine Zeile), die an anderer Stelle noch verwendet wurde. Menschen rechnen oft kreuz und quer in Excel; schreiben irgendwelche Konstanten in irgendwelche Zellen. Fremde Tabellenblätter zu analysieren ist schwierig und mühsam:
Tipp 1: Speichern Sie die Originaldatei unter einem anderen Namen ab.
Tipp 2: Bevor Sie etwas löschen, von dem Sie denken, dass es nicht mehr benötigt wird – überprüfen Sie mit der Spur zum Nachfolger, ob irgendwo eine andere Formel mit dieser weiter rechnet.
Im Excel-Kurs habe ich gelernt, wie man eine Summe bildet. Aber bei mir funktioniert das nicht:
Warum rechnet die Summe nicht?
Die Antwort ist einfach. Sie haben eine englischsprachige Oberfläche. Sie müssen natürlich SUM statt SUMME eingeben, AVERAGE statt MITTELWERT, IF statt WENN, VLOOKUP statt SVERWEIS und so weiter …
Nein – das war sehr ironisch. Jeder, der häufig Daten aus SAP exportiert, kennt sicherlich das Problem: Ab und zu werden Textinformationen unter die Zellen geschoben. Das sieht man erstaunlicherweise nicht – die Zellen sind als „Standard“ formatiert. Oft erkennt man es daran, dass die Zahlen linksbündig in der Zelle stehen. Spätestens wenn Sie mit den Zahlen weiterrechnen möchten oder wenn Sie die Zahlen sortieren oder filtern oder als zahlen formatieren möchten … stellen Sie fest, dass Excel Ihnen nun einen Strich durch die Rechnung macht.
Sieht aus wie Zahl, ist aber Text.
Ich habe für dieses Problem folgende Lösungen gefunden:
1. Wenn Sie Glück haben und das kleine grüne Dreieck sehen zur Fehlerüberprüfung, können Sie darüber die Texte in Zahlen zurückkonvertieren.
Manchmal geht es zurück.
2. Wenn Sie nur einige wenige Zellen haben, können Sie auf die Zelle einen Doppelklick machen (oder mit [F2] die Zelle editieren und anschließend wieder mit [Enter] beenden. Dann „greift“ sich Excel das korrekte Zahlenformat.
3. Sie können in einer Hilfsspalte daneben den Wert der Zelle mit 1 multiplizieren (=O2*1). Die Formel herunterziehen, kopieren und die Inhalte als Werte einfügen.
4. Das Gleiche erledigt auch die Funktion =WERT
5. Oder auch der Rechenoperator – –
6. Oder Sie markieren die Spalten und verwenden den Assistenten „Text in Spalten“, den Sie im Register „Daten“ finden. Geben Sie dort ein absurdes Trennzeichen ein (beispielsweise eine ~); ein Trennzeichen, das es natürlich in den Zahlen nicht gibt. Dann überschreibt er die Werte mit sich selbst und „greift sich“ das korrekte, das heißt das darunterliegende Zahlenformat.
7. Die Zahl 1 in eine leere Zelle schreiben. Die Zelle kopieren, den Text-Zahl-Bereich markieren und mit Inhalten einfügen / Multiplizieren (Kontextmenü) „darüberklatschen“. Das Ergebnis ist das Gleiche wie in Punkt 2 oder 3 oder 5 – Excel greift sich nun das korrekte Zahlenformat.
So geht es auch.
Zur Ehrenrettung von SAP sei angemerkt: Viele mir bekannte Datenbanksysteme, die da heißen DATEV, KISS, ORBIS, EBIS und andere „schieben“ manchmal (nicht immer!) Textformate unter Zahlen beim Export nach Excel.
Warum zählt die Funktion ZÄHLENWENN (und auch SUMMEWENN) manchmal falsch?
Zählt ZÄHLENWENN falsch?
Die Antwort auf diese Frage findet man, wenn man die Spalte rechtsbündig formatiert. Dann sieht man, dass „China“ beim zweiten Mal mit einem Leerzeichen eingegeben wurde.
Ein Leerzeichen am Ende bewirkt einen anderen Text.
Ein Blick in die Statuszeile hätte genügt: Dort steht, dass in D3 ein Zirkelbezug steht. Und wenn man sich die Formel genauer anschaut, wird klar, dass D3 auf D5 zugreift, D5 jedoch wiederum auf D3. Das darf nicht sein!
Der Zirkelbezug ist ausfindig gemacht.
Meine Empfehlung: Zirkelbezüge sind schwierig zu finden. Wenn Sie einen Zirkelbezug haben, erhalten Sie immer eine Fehlermeldung. Unterbrechen Sie die weitere Arbeit und machen sich auf die Suche nach der Quelle. Denn sonst resultieren weitere Fehler aus dem Zirkelbezug.
1. Entweder Sie kopieren eine Formel an eine Stelle wo sie nicht mehr „richtig rechnen kann“. Beispielsweise findet die Summe keine drei Zellen oberhalb und kann deshalb nicht drei Werte summieren:
Summe nicht möglich
2. Eine Formel greift auf eine Zelle zu (beispielsweise auf Zelle C1). Wird nun die Spalte C gelöscht, dann „findet“ die Formel keinen Wert mehr und meldet #BEZUG!
#BEZUG!
Das heißt: #BEZUG! wird immer dann angezeigt, wenn man eine Formel so kopiert oder verschiebt, dass sie nun nicht mehr rechnen kann. Oder eine Spalte so löscht oder einfügt, dass eine Formel nicht mehr „erkennen“ kann, wi der Wert liegt, mit dem sie ursprünglich gerechnet hat.
In der Excel Tabelle die im Anhang beigefügt ist, bekommen wir Daten von unsere EDV (sehe Sheet 1 vor Verarbeitung). Ich gehe dann in „DATA“ und „Text to Columns“ und spalte diese Tabelle so dass sie nach diese Schritte wie im 2. Sheet (Daten nach Verarbeitung) aussieht.
Das Problem liegt indem einige Zahlen immer noch so erscheinen „1 150,000“ und keine weitere Formatierung möglich ist. Da ich auch eine Summe daraus ziehen möchte.
Wie kann man diese Problem Lösen ?
Originaldaten
Daten nach dem Trennen
Die Antwort: Dummerweise liefert Ihr System die Spalte E so, dass nach dem Tausenderwert als Tausendertrennzeichen ein Leerzeichen verwendet wird. Diese müssen Sie löschen. Ich würde die Spalte (hier E) markieren und dann mit Home / Find & Replace (ganz rechts) das Leerzeichen (einfach ein Blank eintippen) durch nichts ersetzen.
Ich habe eine Formel genauso abgeschrieben, wie ich sie im Internet gefunden habe – aber sie rechnet falsch. Ich bin Widder – kein Stier!
Formel rechnet falsch ?!
Entweder ist ein Denkfehler in der Formel, aber wenn sie an anderer Stelle funktioniert, dann muss sie wohl korrekt sein. Möglicherweise wurden die Klammern falsch gesetzt. Wenn Sie auf die Zelle klicken, in der sich die Formel befindet und den Funktionsassistenten f(x) aufrufen, dann sehen Sie die Teile der Formel mit ihren Ergebnissen. Nun können Sie in der Eingabezeile auf die einzelnen Formeln klicken und sehen so, wo der Fehler steckt (in diesem Beispiel wurde die Klammer nach der Funktion TAG falsch gesetzt. Die korrekte Formel muss lauten:
Der Blick in die Gruppe „Zahl“ auf das Zahlenformat „Zahl“ macht stutzig. Ein genauer Blick darauf, wie die Zelle formatiert ist und welcher Wert eigentlich in der Zelle steht, ergibt, dass die Zahl 0,6 ohne Dezimalstellen formatiert wurde. Die Lösung: Lassen Sie sich (mehr) Dezimalstellen anzeigen. Excel rechnet mit dem Wert, der sich in der Zelle befindet und nicht mit dem Wert, den Sie sehen.
Ich habe mal nachgeschaut: Heute, am 07. Januar 2015 verwendet Excel intern die Zahl 42.011. Das erhalte ich, wenn ich die Zelle als Standard formatiere. In meinem openOffice und libreOffice genauso. Allerdings ist der 1. Januar 1900 bei Excel die Zahl 1, in openOffice und libreOffice die Zahl 2. Kann mir das mal einer erklären?
Fehlt ein Tag?
Das ist ganz einfach. Da hat einer nicht aufgepasst! Jedes Jahr, das durch vier teilbar ist, ist ein Schaltjahr. Alle Hundert Jahre ist kein Schaltjahr, alle 400 haben wir wieder ein Schaltjahr. Das heißt: 2016, 2020 und 2024 werden wir ein Schaltjahr haben, im Jahre 2000 hatten wir eines, aber nicht 1900. Die Macher von Excel haben das übersehen. Wenn Sie den 29.02.1900 eingeben, dann erhalten sie ein gültiges Datum, was Sie daran erkennen können, dass die Zahl rechtsbündig steht. Die Macher von openOffice/libreOffice haben dies richtig erkannt und dieses Datum weggelassen. Nun, da Excel im Jahre 1900 anfängt, sind also die ersten beiden Monate falsch. Also: geben Sie keine Datumsangaben zwischen dem 1.Januar 1900 und dem 28.Februar 1900 ein. Aber das haben Sie sowieso nicht vor, oder?
Warum erhalte ich eine Meldung nach der Kompatibilitätsprüfung?
Kompatibilitätsprüfung
Wenn eine Datei im Format *.xls (also in Excel 2003) erstellt wurde und Sie diese Datei im gleichen Format speichern möchten, müssen Sie ein paar Dinge beachten:
Excel unterstützt im Format *.xls nicht alle Funktionalitäten, die in *.xlsx vorhanden sind. Dazu gehören:
Manchmal taucht das Kästchen nicht auf, mit dessen Hilfe ich eine Formel nach unten ziehen kann. Warum ist es manchmal verschwunden?
Das Kästchen ist verschwunden.
Die Antwort: Wenn Sie getrennte Bereiche mit gedrückter [Strg]-Taste markieren, dann können sie nicht unabhängig voneinander runtergezogen werden. Excel kann nur einen einzigen zusammenhängenden Bereich runterziehen.
Warum rechnet Excel manchmal falsch mit Prozentwerten?
Das Ergebnis ist falsch!
Ein Blick auf die Zelle, in der die vermeintlichen 19% stehen, liefert die Antwort. In dieser Zelle stehen nicht 19%, sondern die Zahl 19. In der Gruppe „Zahl“ wird angezeigt, dass diese Zelle „benutzerdefiniert“ formatiert wurde. Ein Blick in die Zahlenformate liefert schließlich das Ergebnis: Der Anwender hat nicht 19% in die Zelle eingetragen oder die Zahl 0,19 als Prozent formatiert, sondern hat die Zahl 19 eingetragen und hinter diese Zahl benutzerdefiniert ein Prozentzeichen formatiert (so wie man m² oder kg formatieren kann). Deshalb rechnet Excel natürlich mit der Zahl 19 und „übergeht“ das Prozentzeichen.
wird mit einer Fehlermeldung quittiert. Alle Kunden, die entweder Platinum-Mitglieder sind oder jetzt schon mehr als 180 Euro bezahlen, sollen demnächst 20 Euro mehr Jahresbeitrag bezahlen – die übrigen erhalten eine Erhöhung von 10 Euro.
Die Antwort: UND und ODER sind in Excel keine Verknüpfungsoperatoren (wie beispielsweise in Programmiersprachen), sondern Funktionen. Und Funktionen müssen immer VOR den Argumenten geschrieben werden, also so:
Nur TE und kein Teilergebnis
Die Antwort: Drücken Sie nicht die [Enter]-Taste, sondern die Tabulatortaste. Dann übernimmt Excel diese Funktion. Von Andreas Theos stammt noch der Tipp: „…und nach dem TAB noch STRG + A für den Formeldialog ;-)“ – danke!