Ich war sehr erstaunt. Warum zeigt die Matrix in PowerBI keine korrekten Werte bei den Zeilenzwischensummen?
Die Antwort finde ich im Aufgabenbereich „Daten hinzufügen“:
PowerBI hat als Funktion „Erstes Datum“ (?!?) verwendet. Warum nicht Summe?
Die Antwort finde ich in den Transformationsschritten: Dort war die Spalte, die später aggregiert wird, von Zahlentyp „beliebig („123 ABC“) festgelegt. Sie muss natürlich Zahl (beispielsweise Dezimalzahl) sein.
Noch besser: man verwendet nicht das Feld, sondern erstellt ein Measure, beispielsweise mit SUMX. Dann ist man auf der sicheren Seite, dann verwendet PowerBI genau DIESE Funktion und wählt nicht selbst eine aus.
Hier noch eine Frage über etwas was nicht so toll lauft.
Beim Starten dieser Datei durch ein Makro wird eine Formel mit @ geöffnet.
Logischerweise stimmen dann viele Zahlen, die hier rauskopieren will nicht und können nicht weiter berechnet werden.
Wenn ich die Excel von Hand starte, dann sieht alles normal aus.
Können Sie sich dazu einen Reim machen?
####
Hallo Herr S.,
ich hatte einmal den Fall, da hat Excel ein „@“ eingefügt – das war beim Übergang 2016, als die Array-Funktionen eingeführt wurden. In DER Datei hatte das „@“ nicht gestört. Es bedeutet ja: Hole den Wert aus der Spalte aus der gleichen Zeile.
Frage: Ihr Name „GVW“ ist ein Name, der sich auf eine Zelle bezieht? Wird er mehrmals verwendet? Falls nein, könnten Sie den Namen löschen und durch einen Zellname (bspw. R23) ersetzen.
Oder bezieht sich „GVW“ auf einen Bereich? Auch dann würde ich die Formel etwas anpassen.
In der Excelschulung stoßen wir auf folgendes Problem:
Ein Datenbanksystem liefert die Daten einer Spalte entweder als Jahreszahl – Q1 (oder Q2, Q3, Q4) oder < 2018 Q1.
Angenommen man möchte nach dem Quartal oder dem Jahr filtern. Dann wäre es sinnvoll, diese Informationen in eigenen Spalten zu haben. Ich zeige die drei Techniken:
Daten / Text in Spalten
Blitzvorschau
Formeln
Als Formel, um das Jahr zu extrahieren, verwende ich:
=WENN(LINKS(K2;1)="<";TEIL(K2;3;4);LINKS(K2;4))
Ein Teilnehmer fragt, was denn wäre, wenn statt einem „<“ ein „>“ als erstes Zeichen in der Zelle stünde.
„Nun“, so lautet meine Antwort, dann könnte man überprüfen, ob das erste Zeichen eine Ziffer ist. Beispielsweise so:
Auch ISTTEXT funktioniert nicht. Das Problem: LINKS(K2;1) liefert immer einen Text – egal, ob es sich um eine Ziffer oder ein anderes Zeichen handelt. Eine Lösung für dieses Problem wäre beispielsweise:
Man multipliziert das erste Zeichen mit 1. Ist das erste Zeichen ein Buchstabe oder ein anderes, nichtnumerische Zeichen, ist ein Fehler die Folge. Dieser kann abgefangen werden.
Ich habe eine Weile suchen müssen, bis ich die Ursache gefunden hatte. Dann haben wir beide gelacht,
Jürgen fragt, ob ich einen Moment Zeit hätte. Einige Werte würden nicht angezeigt werden:
Meine erste Frage: Was berechnet denn die Formel? Ein Verweis von einer anderen Zelle auf die Zelle D2 zeigt, dass sich in der Zelle ein Wert (<> 0) befindet.
Meine zweite Vermutung: wie ist denn die Zelle formatiert? Die Antwort: Die Schriftfarbe ist „Automatisch“, das Zahlenformat „Standard“
Meine dritte Vermutung: und die bedingte Formatierung?
Sie sieht folgendermaßen aus:
Die erste Bedingung
=$E2=80
wird erfüllt. Die Schriftfarbe wird auf automatisch gesetzt; der Rahmen wird eingeschaltet.
Die zweite Bedingung?
=F2=1400
Sie wird ja nicht erfüllt – in F2 steht 2800. Oder doch?
Der Bereich, auf den die Bedingung angewendet wird, lautet $B$2:$D$31. Da bei der Bedingung das Dollarzeichen vergessen wurde, wird diese Bedingung auf alle drei Spalten angewendet; das heiß: F2 verschiebt sich nach G2 und H2. Da in H2 der Wert 1400 steht, ist die Bedingung DORT korrekt.
Jedoch: warum wird keine rote Hintergrundfarbe angezeigt?
Der Grund: beide Bedingungen sind in der Spalte D korrekt (und nur dort). Die erste Bedingung lautet: weißer Hintergrund; automatische (!) Schriftfarbe. Die zweite Bedingung: roter Hintergrund und weiße Schriftfarbe.
Die erste Bedingung gewinnt für die Hintergrundfarbe. Hätte man schwarze (!) Schriftfarbe verwendet, würde diese auch gewinnen. Jedoch: automatische Schriftfarbe heißt, dass bei der zweiten Bedingung die weiße Schriftfarbe nun gewinnt (!?!), jedoch nicht die rote Hintergrundfarbe!
Also: Dollar reinsetzen – und dann klappt es auch!
Claudia kommt in der Pause zu mir. Zwei Stunden habe sie den Fehler gesucht und nicht gefunden. Ob ich ihr mit einer Formel helfen könne, die nicht richtig rechne:
Der Bereich A2:A13 trägt den Namen Feiertage_BY, B2:B10 heißt Feiertage_HE.
Darin befinden sich die bayrischen und die hessischen Feiertage.
Markus arbeitet in Bayern; Boris in Hessen.
Claudia möchte mit der Funktion NETTOARBEITSTAGE ausrechnen, wie viele Tage Markus und wie viele Boris arbeiten muss. Für Markus erhält sie die korrekte Zahl 248; Boris muss drei Tage länger arbeiten – eigentlich 251 Tage. Excel jedoch berechnet 253.
Ich kontrolliere die Namen, die Formatierungen, die Formel – alles ist korrekt. Ich sehe nach, ob alle Feiertage auf einen Wochentag fallen. Stimmt auch. Dann prüfe ich die Feiertage.
Haben Sie es gesehen? In der Liste Feiertage_HE befinden sich zwei Mal die Tage 07.04.2023 und 10.04.2023. Allerdings fehlen die Weihnachtsfeiertage. Wahrscheinlich ein Kopierfehler …
Und dann klappt es auch. Und Claudia ist glücklich.
ich würde gerne bei dir einen 2 bis 3 Minuten-Support in Anspruch nehmen. Die bedingte Formatierung zeigt eine „Lücken“ an.
Gruß Jürgen
Es dauert wirklich nur drei Minuten. EIn Blick auf die Formel
=A2<>""
genügt. Da diese Formel in den Spalten C, D und E verwendet wird, „wandert“ der relative Bezug nach rechts. Und so wird in Spalte D auf Spalte B zugegriffen:
Excelschulung. Wir üben rechnen. Zuerst eine einfache Subtraktion; Formel herunterziehen:
Wir lernen, wie man mit dem AutoSummen-Symbol eine Summe erzeugt:
Und jetzt machen Sie es mal alleine, lautet die Aufgabe. Ein Teilnehmer beschwert sich, dass eine Summe falsch rechnet:
Wir gehen der Sache auf den Grund. Klar – er hat die erste Summe nicht nach rechts gezogen, sondern drei Mal eine Summe erstellt. Und bei der dritten Summe hat er nicht aufgepasst und den Vorschlag von Excel übernommen:
Die neuen Textfunktionen sind noch nicht ganz bei mir angekommen:
Sie sprechen noch ein bisschen Englisch; aber – was schlimmer ist – sie arbeiten noch nicht korrekt. TEXTTEILEN sollte alle Textteile liefern.
Okay, okay, verstanden – diese Arrayfunktion darf nicht (anders als TEXTVERKETTEN) auf den ganzen Bereich angewendet werden, sondern nur auf eine Zelle. Und dann runterziehen!
Die Aufgabe lautet: ich möchte die Anzahl der Zeilen wissen, in denen in mindestens einer der vier Spalten etwas steht. Oder Anzahl der Spalten, bei denen Wert Spalte A <> „“ oder Wert Spalte B <> „“ oder Wert Spalte C <> „“ oder Wert Spalte D <> „“.
Ich probiere. Mit & werden die vier Zellen verkettet:
Ich gestehe, dass ich mich manchmal irre. Oder Dinge übersehe. Oder schlicht und einfach nicht weiß. Umso mehr freue ich mich darüber, dass andere meinen Blog mitlesen und mir korrigierende Antworten schreiben. Eine Berichtigung hat mich vorgestern erreicht – ich möchte sie hier gerne wiederholen, weil ich danke, dass sie wichtig ist.
Die Idee ist gut – sie funktioniert nur leider nicht.
Ein Teilnehmer einer Excelschulung möchte eine fortlaufende Reihe erzeugen. Er möchte, dass „Lücken übersprungen“ werden und dass die Reihe bequem fortgesetzt werden kann.
Kein Problem, oder:
Die Formel
=WENN(B2="";"";MAX($A$1:A1)+1)
hilft hierbei.
Damit unter der Liste neue Daten mit einer fortlaufenden Nummer eingetragen werden können, wandle ich die Liste in eine (intelligente) Tabelle um:
Ein neuer Name:Lücke und ein weiterer Name:
Klappt.
Wird eine Zeile gelöscht:
funktioniert der Mechanismus hervorragend:
Jedoch: wird eine Zeile eingefügt:
Dann versagt der Mechanismus leider:
Was man feststellen kann, wenn man einen Namen einträgt:
Guten Morgen zusammen,
vielleicht könnt Ihr mir helfen
Ich habe für meine
Masterarbeit Wetterdaten bekommen und würde jetzt gerne aus den Zahlen für die
Windrichtung den Text „Nord“, bzw. „Ost“ usw. generieren.
Dabei ist
0<=x<45 =>
„Nord“
45<=x<135 =>
„Ost“
135<=x<225 =>
„Süd“
225<=x<315 =>
„West“
315<=x<380 =>
„Nord“
Dafür hab ich folgende
WENN-Funktion verwenden wollen:
Excel ist nicht konsistent im Umgang mit leeren Zellen. In den meisten Funktionen wird eine leere Zelle als 0 interpretiert. Aber eben halt nicht immer. Gestern habe ich mich mal wieder geirrt.
Ich wollte die Funktion HÄUFIG mit der Funktion ZÄHLENWENNS nachbauen – wollte die Bereiche definieren.
Mit der Funktion HÄUFIGKEIT (als Matrixfunktion) kann man berechnen, wie viele Daten in den entsprechenden Klassen vorhanden sind:
Die Antwort ist einfach: du darfst „FALSCH“ nicht in Anführungszeichen setzen – es handelt sich hier nicht um einen Text, sondern um einen (booleschen) Wert. Also:
Excelschulung. Wir erstellen einen Kalender. Daran kann man einige Funktionen üben: die Funktion WENN, Datumsfunktionen, Textfunktionen. Um Funktionen aus der Kategorie „Nachschlagen und verweisen“ zu zeigen, erstelle ich einen mehrsprachigen Kalender. Über eine Auswahlliste (Datenüberprüfung) wird die Sprache gewählt:
Mit VERGLEICH wird die Zeilennummer ermittelt; INDEX „holt“ den Ländercode aus einer kleinen Tabelle:
Eine Teilnehmerin sagt, dass es bei ihr nicht funktioniere. Ich schaue auf ihren Bildschirm. Richtig: Sie hat Excel 2013. Das erkenne ich sofort an den Großbuchstaben der Texte im Menüband. Bis Excel 2013 wurde nicht der ISO-Sprach- und Ländercode verwendet, sondern ein anderer. Zum Glück finde ich ihn auf der Festplatte in einem älteren Beispiel:
Mit VBA wird eine Userform (eine Maske) erstellt zur bequemen Dateneingabe. Der Wert eines Textfeldes wird als String interpretiert und als solcher bei Dezimalzahlen in eine Excelliste eingetragen. Man erkennt es, weil die Zahlen linksbündig in der Zelle stehen:
Dummerweise wird ein Text immer größer als eine Zahl definiert, so dass eine Formel
mein Bekannter hat
sich die neue Version angeschaut.
Jetzt lässt sich der Datensatz zwar anlegen, aber es treten neue Fehler auf, die bisher noch nicht vorhanden waren.
In der Tabelle erscheint nach dem Löschen des Datensatzes ein Fehler.
Hallo Herr L.,
öffnen Sie mal bitte eine ältere Version und klicken Sie dort auf Datenverwaltung und löschen Sie einen Datensatz.
Klicken Sie anschließend auf den Datensatz direkt über dem Datensatz, den Sie gelöscht haben.
Sie erhalten den Fehler.
Heißt: DIESER
Fehler war schon lange drin, bevor Sie mir die Datei geschickt haben.
Woher kommt er?
Beim Löschen einer Zeile liefert die Zeile darüber einen Fehler:
Sie greifen auf den
VALUE dieser Zelle zu – das knallt!
Die Ursache des
Fehlers:
In der Spalte K (Membership) greift die Formel für den Wert „Silber“ auf die Zeile darunter zu! Die Formeln dieser Spalte sind falsch! Schon bevor Sie mir die Datei geschickt haben!
=WENN(UND(H2="Spain";J2>200);"Black";WENN(UND(H2="Spain";J2>180);"Platinum";WENN(UND(H2="Spain";J2>150);"Gold";WENN(UND(H3="Spain";J2>140);"Silber";WENN(UND(H2="Spain";J2>130);"Standard";WENN(UND(H2="Spain";J2>50);"Blue";"out of order"))))))
Fazit: es sind nicht neue Fehler – in Ihrem Programm sind einige alte Fehler, für die ich mich nicht verantwortlich zeichne.
An dieser Maske dlgKundenverwaltung habe ich nichts geändert.
schöne Grüße
Rene Martin
*) An Gott glauben wir; alle anderen müssen Daten zeigen!
Erstaunlich! Auf dem letzten Excelstammtisch, den Frank Arendt-Theilen organisiert hat, hat er angemerkt, dass die PowerQuery-Funktion Date.WeekOfYear, die man über Spalte hinzufügen / Datum / Woche / Woche des Jahres nach dem US-amerikanischen Modell rechnet. Zwar verfügt Excel seit vielen Versionen über die Funktion ISOKALENDERWOCHE, welche die KW korrekt nach ISO 8601 berechnet. Auch Outlook unterscheidet bei den Kalenderwochen zwischen USA und Europa. Jedoch nicht PowerQuery. „Haben die das vergessen?“, fragt Frank. Also muss man diese Funktion in PQ nachbauen …
ich zweifle an meinem Verstand – ich kann den Fehler in
der Differenzrechnung Zeile 38 nicht
entdecken, obwohl ich alle Formatierungen und Eingaben mehrfach gecheckt habe.
Der Fehler tritt nur in der Zelle i38 auf, die Formel habe ich mehrfach neu
eingegeben und Zeilen und Zellen gelöscht usw. der Fehler bleibt – HILFE!
Ich bin froh, dass es nur eine private Tabelle ist und
nichts Wichtiges, aber so was ist doch nicht zu ertragen…
Übrigens: Ihre Sprüche sind grandios! Wenigstens konnte
ich mehrfach laut lachen!
Mit freundlichen Grüßen Ulrike
Hallo Frau H.,
Danke für das Lob der Sprüche – manchmal befürchte ich,
dass einige nur auf meine Seite wegen der dummen Sprüche klicken. Die sollen
doch mit mir über Excel diskutieren! *lach*
Ich verstehe Ihr Problem nicht:
130,45- 150,31 = 19,86
SO haben Sie es in den anderen Zeilen auch gerechnet.
Wer von uns beiden „denkt falsch“?
Liebe Grüße
Rene Martin
Guten Tag Herr Martin,
erstmal danke für die schnelle Reaktion, die ich zuerst nicht verstanden habe. Beim erneuten Öffnen der gesendeten Mail konnte ich sehen, dass die Zeilen, um die es geht, darin nicht zu sehen sind, obwohl ich das ganze Blatt schicken wollte. Das liegt evtl. an der Druck-einstellung. Ich schicke die Mappe nochmal und stelle fest, dass es Zeile 39 ist, wo der Fehler auftritt (ich hab das mit der Nummerierung der Positionen verwechselt! – war ja auch schon etwas Gaga nach dem vielen rumprobieren) Die Formel heißt also: <=H39-G39> und müsste = ergeben, tut sie aber nicht.
Also noch mal meine Bitte, den Fehler zu identifizieren.
Liebe Grüße Ulrike
Hallo Frau H.,
Ich würde gerne helfen, aber Zeile 39 ist leer!
Liebe Grüße
Rene Martin
Dann bitte bei Zeile 40 nachschauen, folgende Beträge: 129,74 € – 61,67 € ergibt nicht -67,80€ sondern – 68,07€! Ulrike
Da ist ein Zahlendreher in Ihrem Kopf oder ihrer Wahrnehmung, liebe Frau H.
In der Zelle G40 steht der Wert 129,47 und nicht 129,74:
Vier – sieben – NICHT: sieben – vier!
Liebe Grüße
Rene Martin
Oh mein Gott— gut, dass ich niemand anders gefragt habe –
es ist zu peinlich!
Ganz lieben Dank für die Mühe! Ich werde die Seite
weiterempfehlen – nicht nur wegen der Sprüche!
Kannst Du mir sagen, wie ich, wenn in einem Tabellenblatt einen Wert aus einem anderen Reiter angegeben ist, in der daneben liegenden Spalte automatisch den Wert aus einer anderen Spalte der Tabelle in dem anderen Reiter ausgebe?
In A2 habe ich per Dropdown mit INDIREKT „<10%“ aus tab_AntwortKategorie[Kategorie].
In B2 möchte ich automatisch den dazugehörigen Wert aus tab_AntwortKategorie[Wert].
Was muss ich in B2 eingeben?
Ohne INDIREKT wüsste ich es, aber wie gesagt – ich stehe einfach auf dem Schlauch.
Momentan „fuhrwerke ich wild (und falsch) rum“, z.B.
=WVERWEIS($AE4;tab_AntwortKategorie[Kategorie];[Wert])
Bye
Michael
Hallo Michael,
wenn du den Wert DANEBEN haben möchtest, musst du den SVERWEIS verwenden, nicht den WVERWEIS. „S“ steht für „senkrecht“, heißt: die Liste ist von oben nach unten aufgebaut.
Hallo, ich habe auch so ein Problem in meinem Kalender zur Arbeitszeitberechnung mit den Rauten. Excel gibt mir aber den Hinweis, das entweder die Zelle zu klein ist, oder ein Negativ Wert darin enthalten ist. Bei mir scheint es wohl der Negativ Wert zu sein da eine Formel in der Zelle enthalten ist bei der ich zb. 8 von 9 in meinem Fall Stunden abziehen möchte. Da ich die Zeiten über Dropdown Listen eingebe, habe Rauten in den Zellen solange ich noch keine Werte eingegeben habe. Das sieht natürlich nicht so gut aus. Nun meine Frage. Kann man diese Rauten auch einfach nur ausblenden ohne das die darin enthaltenen Formeln auch ausgeblendet sind? Ich hoffe das ich mein Problem verständlich erklären konnte.
Gruß Uli.
Hallo Uli?
Ich würde um die Formel (wie sieht sie aus?) beispielsweise
=C2-B2
eine WENN-Funktion bauen:
=WENN(C2-B2<0;““;C2-B2)
Hilft das?
Liebe Grüße
René Martin
Hallo Herr Dr.Martin,
Danke Ihnen nochmal für ihre Hilfe, es hat geklappt mit
der Formel.
War für Sie bestimmt eine Logische und einfache Sache, aber für mich als Blutiger Anfänger ein Riesen Problem. Würde mich freuen wenn ich mal wieder so ein Problem hätte, mich an Sie wenden dürfte.
Mit freundlichen Grüßen U.
Klar, Uli,
einfach schreiben! Ich helfe gerne
Liebe Grüße aus dem verregneten München
Rene Martin
PS: ich mag den Begriff „logisch“ nicht. Es
läuft eine gewisse erlernbare Mechanik ab. Jeder von uns hat ein bestimmtes
Wissen und erkennt Lösungen „intuitiv“ und kann sie sicherlich auch
beschreiben, warum dieser oder jeder Weg der richtige (oder der geeignete) ist.
Also: Kopf hoch, dranbleiben, schmunzeln – ich halte Excel für wichtig – nicht,
weil ich häufig damit arbeite, sondern weil sehr, sehr viele Menschen häufig
damit arbeiten.
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:
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:
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:
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!
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).
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 …
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.
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
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 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:
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.
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:
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.
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:
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.
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.
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 …
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 …
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.
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:
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 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)
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.
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:
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.
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.
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)
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.
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.
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.
Kann ich mit der Summe nicht mehrere getrennte Bereiche addieren?
Doch. Aber die Bereiche werden nicht mit einem Leerzeichen, sondern mit einem Semikolon (;) getrennt.
Also – nicht so:
=SUMME(G5:G18 G30:G42 G56:G71 G80:G93)
sondern so:
=SUMME(G5:G18;G30:G42;G56:G71;G80:G93)
Übrigens: das Leerzeichen hat auch eine Funktion – es bedeutet: Schnittmenge. Man könnte berechnen:
=SUMME(C5:H21 G5:G26)
Damit würde die Summe über die Zelle G1:G21 gezogen werden.
Meine Empfehlung – tun Sie das nicht – das versteht kein Mensch, was Sie da tun! Und: wenn die Schnittmenge leer ist, quittiert Excel dies mit der Fehlermeldung #NULL!
Doch. Aber Excel schafft den Sprung in den neuen Tag, also über die 24-Stunden-Grenze nicht.
Die Lösung: Sie müssen die Uhrzeit vom Typ [h]:ss formatieren. Oder in der Kategorie „Uhrzeit“ finden Sie ein Beispiel „37:30:50“. Das macht das Gleiche.
Stunden werden falsch formatiert.
Die Summe ist korrekt; sie muss mit [h]:ss formatiert werden.
Man braucht schon ein sehr gutes Auge, um auf Anhieb erkennen zu können, warum Excel hier falsch rechnet.
Ein Tipp: Wenn man mit den Mauszeiger über den Bereich streicht, fällt auf, dass er seine Gestalt bei der Zahl 3 ändert. Ein Klick darauf ertappt den „Bösewicht“: Auf der Zelle liegt ein weißes Textfeld, in dem die Zahl 3 steht. Darunter steht natürlich eine andere Zahl.
Man muss in großen Dateien schon lange suchen, bis man diesen Fehler findet. Die Summe rechnet mit einer vermeintlich leeren Zelle. In der Zelle steht eine Zahl – sie ist allerdings mit einer weißen Schriftfarbe formatiert.
Was ist los – Excel will nicht mehr rechnen. Am Anfang hat es funktioniert …
Schauen Sie in der Registerkarte „Formeln“ nach, ob die Berechnungsoptionen auf „manuell“ gestellt wurde. Wenn ja, so wird die Berechnung zwar beim Erstellen der Formel durchgeführt, aber beim Ändern der Werte nicht aktualisiert. Ändern Sie diese Option auf „automatisch“ oder aktualisieren Sie das Ganze mit der Funktionstaste [F9].
Eigentlich müsste man es sofort sehen: Texte stehen in Zellen am linken Rand; Zahlen rechtsbündig. Wenn man sich vertippt, beispielsweise den Buchstaben „O“ statt die Ziffer „0“ oder den Buchstaben „l“ statt der Ziffer „1“ eingibt, kann Excel mit diesen Texten nicht rechnen.
Warum machen Menschen so etwas? Es gab einige Schreibmaschinen, auf denen gab es keine Ziffer „0“ oder keine Ziffer „1“. Dort musste man auf die Buchstaben „O“, beziehungsweise „l“ zurückgreifen.
Bitte kein „O“ statt der Ziffer „0“; bitte kein „l“ statt der Ziffer „1“ eingeben.
Ein Klick auf die Schaltfläche AutoSumme und – nichts passiert. Nun: Die Antwort ist schnell gefunden: Wenn sich der Cursor in der Zelle befindet, kann die Funktion AutoSumme nicht aktiviert werden. Man sieht es am blinkenden Cursor. Die Eingabe muss abgebrochen oder bestätigt werden – dann funktioniert es.
Ist Ihnen aufgefallen, dass der Rechenoperator + etwas anderes macht als die Funktion SUMME? Dass * anders rechnet als die Funktion PRODUKT? Dass die Funktion QUOTIENT etwas anderes berechnet als der Divisionsoperator / wird schnell klar – QUOTIENT liefert den ganzzahligen Anteil einer Division.
Bei Summe und +, beziehungsweise Produkt und * ist der Unterschied nicht ganz so offensichtlich:
Summe und Produkt übergeht Texte, während + und * einen Fehler (#WERT) liefern. Der Operator * interpretiert eine leere Zelle als Wert 0, während die Funktion Produkt diese übergeht.
Also Achtung: + ist nicht das Gleiche wie Summe in Excel, * nicht das Gleiche wie * und / schon gar nicht das Gleiche wie Quotient.
Übrigens – bei dem selten verwendeten Rechenoperator ^ rechnet Excel offensichtlich genauso wie mit der Funktion POTENZ.
Summe und +, beziehungsweise Produkt und * im Vergleich.
![Die Summe ist korrekt; sie muss mit [h]:ss formatiert werden.](http://www.excel-nervt.de/wp-content/uploads/2015/01/summeuhrzeiten20150104_2.jpg)
