Wir sollten endlich aufhören, weniger zu trinken.

Och nö! Wer macht den so etwas?

Eine Rechtsanwaltskanzlei zeigt mir ihre Excel-Vorlage, mit der sie arbeiten:

Darin befindet sich eine intelligente Tabelle mit dem Namen „Tabelle1“. So heißt auch das Tabellenblatt. Darin befinden sich Bereiche für Zahlen mit Zwischensummern (!), Text, Datum und Zahl wird in einer Spalte fröhlich durcheinander gewürfelt. Boah!

Ich vermute, dass sie die Idee der intelligenten Tabelle nicht ganz verstanden haben. Und diese nur für die lustigen Farben verwendet haben …

Brot trocknet nicht aus, wenn man es in einem Eimer mit Wasser lagert.

Verblüfft. Eine Teilnehmerin in einer Excelschulung sagt mir, dass eine intelligente Tabelle auf einem geschützten Arbeitsblatt liegt:

Sie möchte eine Pivottabelle erstellen, was mit Einfügen / Pivot-Tabelle nicht möglich ist.

Und umgekehrt:

Befindet sich der Cursor auf einem leeren, ungeschützten Tabellenblatt, klickt man dort auf Einfügen / Pivot-Tabelle , kann man den Namen der intelligenten Tabelle eintragen:

Padautz: SO kann man eine Pivottabelle erzeugen!

7 Uhr: Nudelsalat machen. 10 Uhr: Schauen, ob Nudelsalat schon durchgezogen ist. 10.15: Neuen Plan für Abendessen machen.

Auf einem Tabellenblatt befindet sich eine intelligente Tabelle. Sie heißt „Tabelle1“.

Sie wird in das Datenmodell eingefügt. Die Tabelle in Power Pivot heißt „Tabelle1“:

Die Ursprungstabelle wird umbenannt.

Leider wird die Tabelle in PowerPivot NICHT automatisch umbenannt:

Sehr schade, findet das ein Teilnehmer der letzten Power Pivot-Schulung.

Ich kreuze Hunde. Die letzte Kreuzung wurde zwischen einem Bullterrier und einem Shih Tzu durchgeführt. Wie wohl die neue Rasse heißt?

Auf einem Tabellenblatt befindet sich eine intelligente Tabelle mit dem Namen „Tabelle1“.

Auf ihr wird eine Abfrage aufgesetzt und als Tabelle nach Excel zurückgegeben.

Ändert man nun den Namen der Ursprungstabelle …

… wird diese Namensänderung nicht in der Abfrage mitgenommen:

Sehr schade, findet eine Teilnehmerin der letzten Power Query-Schulung.

Ich bin Hypochonder, Herr Doktor. – Ach was, das bilden Sie sich nur ein!

Hallo Herr Martin,

ich arbeite gerade an einer Excel-Datei (zum Üben).

In der Spalte A ab A2 bis A31 habe ich eine Liste hinterlegt, die sich auf die Nachnamen im Arbeitsblatt Belegung bezieht.

Nun würde ich gern die Matrix im Arbeitsblatt Belegung in eine formatierte Tabelle umwandeln. (Das ist kein Problem.) Nachdem ich dies getan, möchte ich gern folgendes erreichen:

wenn ich in die formatierte Tabelle einen neuen Namen einfüge, alle Nachnamen nochmals alphabetisch sortiere, soll der neu eingetragene Name in der Liste (Spalte A ab A2 bis A31) auftauchen.

Ist das möglich? Wenn ja, wie?

Vielen Dank im Voraus für Ihre Hilfe.

Mit freundlichen Grüßen

####

Hallo Herr F.,

der Trick ist, dass Sie dem Bereich der intelligenten Tabelle einen Namen geben müssen. Dann können Sie den Namen in der Dropdownliste verwenden.

Wird die Liste erweitert (oder verringert), passt sich die Liste an.

Liebe Grüße und: ein schönes Wochenende

Rene

Die Würde des Mannes ist unten antastbar.

Sehr seltsam! In einer (intelligenten) Tabelle (tbl_Kunden1) wurde zeilenweise gerechnet. Der Betrag aus der Spalte Rechnungsbetrag wurde um 100 erhöht.

Kopiert man nun diese Formel in eine andere (intelligente) Tabelle, in der sich auch eine Spalte Rechnungsbetrag befindet, wird leider der Bezug auf die erste Tabelle mitgenommen:

Schade!

Kleine Brandlöcher von der Zigarette entfernt man am besten mit einer Nagelschere.

Ob ich mal kurz Zeit hätte. Eine Excel-Frage. Ganz dringend. Und sehr kompliziert.

Ich hatte Zeit und schaute mir das Problem an.

In einer intelligenten Tabelle befinden sich in einer Spalte lange Texte. Leider kann man sie nicht über die Zellen daneben zentrieren:

Stimmt – das ist in der intelligenten Tabelle verboten. Und das ist auch gut so.

Aber das ist so hässlich und nimmt so viel Platz weg. Und man kann nicht gut lesen.

Meine Antwort: Markieren Sie die Zellen, wählen Zellen formatieren / Ausrichtung und dort „Über Auswahl zentrieren“

Leider bleibt der Text zentriert (man kann ihn nicht linksbündig formatieren); aber damit konnte sie leben. Und war begeistert.

Und war noch begeisterter, als ich ihr den Tipp gab: mit [Strg] + [1] wechseln Sie in den Dialog „Zellen formatieren“ und mit [Strg] + [Y] oder [F4] wiederholen sie den letzten Schritt. So kann man die Tabelle schnell formatieren:

Wir kochen Wasser immer nach Celsius, nicht nach Fahrenheit. Das spart 112 Grad.

Verständlich. Aber trotzdem doof.

Ich habe mehrere Tabellenblätter. Auf jedem Blatt befindet sich eine intelligente Tabelle mit einem identischen Aufbau:

Ich möchte nun alle Blätter auswählen (Gruppierungsmodus) und eine Information ändern (beispielsweise in der Überschrift):

Änderungen an einer Tabelleoder einer XML-Zuordnung können nicht vorgenommen werden, wenn mehrere Blätter ausgewählt wurden.

Schade!

Im nächsten Leben mache ich etwas ohne Wecker.

Dummerweise hat ein Teilnehmer eine intelligente Tabelle über das gesamte Tabellenblatt erstellt.

Ich möchte den Bereich auf die benötigte Größe verkleinern. Hierfür tut das Symbol „Tabellengröße ändern“ in der Registerkarte „Tabellenentwurf“ gute Dienste. Schneller als das grüne Eck nach oben zu ziehen ist sicherlich das Eintragen des Bereichs:

Allerdings: wir wundern uns, dass das Ergebnis nicht das gewünschte ist – die intelligente Tabelle ist verschwunden. Genauer: sie „hängt“ irgendwo oben:

Des Rätsels Lösung: der Bildschirm war so verschoben, dass die erste Ziffer der Zeilennummer nicht sichtbar war. Also auf ein Neues – beim zweiten Mal klappt es auch!

Heute habe ich mein erstes graues Sackhaar entdeckt. Ich bin aber nicht so ausgerastet, wie die anderen Leute im Fahrstuhl.

In ein Tabellenblatt wird eine Überschrift eingetragen. Sie wird formatiert:

Sie wird in eine (leere) intelligente Tabelle (mit einer Zeile) umgewandelt:

Fügt man nun eine Zeile ein, wird die Farbe der Überschrift übernommen:

Man darf also nicht, wenn man in einer intelligenten Tabelle die Überschrift per Hand formatiert (beispielsweise, wenn man unterschiedliche Farben für verschiedene Bereiche verwenden möchte) vor der ersten Datenzeile eine Zeile einfügen.

Für die leere Tabelle heißt das: man muss sie so anlegen, dass mehrere leere Zeilen vorhanden sind.

Ich habe heute den Salzstreuer aufgefüllt. Das dauert bei den kleinen Löchern ja immer ewig.

Blöde! Nur blöde!

Eine intelligente Tabelle. Eine bedingte Formatierung:

Excel schreibt als Bereich $G$2:$G$32 ein. Auch ein Ändern in

=tbl_Fahrräder[Anschaffungspreis]

wird wieder zurückgesetzt.

Das ist doof: wird nun eine Zeile ausgeschnitten und eingefügt, lautet nun der Bereich:

=$G$15:$G$38;$G$2:$G$13

Wiederholt man nun diese Aktion, sieht die bedingte Formatierung folgendermaßen aus:

Blöde!

Kein Mensch mag Mon Chérie. Es gibt überhaupt nur 17 Packungen Mon Chérie weltweit, die permanent verschenkt werden und so weltweit rotieren.

ich möchte (mit [Strg] + [+]) eine weitere Zeilen einer intelligenten Tabelle einfügen. Und erhalte folgende Fehlermeldung:

Hierdurch wird ein gefilterter Bereich in Ihrem Arbeitsblatt geändert. Um diesen Vorgang abzuschließen, entfernen Sie bitte die AutoFilter.

Seltsam, denke ich: in meiner Tabelle habe ich noch nichts gefiltert. Auch das Entfernen des Autofilters bringt kein Licht ins Dunkel. Aber dann entdecke ich UNTER der Tabelle eine weitere Liste mit einer Filterung:

DAS ist des Rätsels Lösung. Obwohl noch Platz für eine weitere Zeile wäre, kann Excel, aufgrund des gesetzten Filters oben keine weitere Zeile einfügen.

Ein echter Pirat weint nicht. Ein echter Pirat heult Rum.

Hallo Herr Martin,
können Sie mir als Excel-Virtuose beim Umgang mit intelligenten Tabellen helfen? Es handelt sich um folgende Sache:

  • gegeben: ein Bereich mit insgesamt 14 Spalten und beliebig vielen Zeilen
  • Ziel: eine intelligente Tabelle bei der Spalte 1, Spalte 2-5, Spalte 6-10; Spalte 11-14 jeweils ein eigenes coloriertes Stripset besitzen (siehe Abbildung = 1. Versuch)
  • meine Versuche + aufgetretene Problematik:
  1. Versuch: mehrere intelligente Tabellen mit dem jeweiligen Stripset (für Zeilen und Spalten) aneinanderfügen
    –> beim Hinzufügen einer neuen Zeile in Spalte 1 (und ff. neu alphabetisch sortieren lassen), sortieren sich die Zeilen der anderen Tabellen nicht automatisch mit –> Chaos
    Idee: Verknüpfung der aneinanderliegenden Tabellen, damit jede auf die Veränderung bspw. in der ersten Spalte (bzw. Tabelle) reagiert
  2. Versuch: eine 14 Spalten umfassende intelligente Tabelle mit benutzerdefinierter Tabellenformatierung
    –> da die Tabellenabschnitte aus einer unterschiedlichen Anzahl aus Spalten bestehen, lässt sich nicht mal ansatzweise mein Ziel über das Formatieren des Stripsets „erste Spalte“ / „zweite Spalte“ verfolgen
    Idee: Hinzufügen der Auswahlfelder Stripset „dritte Spalte“, „vierte Spalte“, usw.
  3. Versuch: eine 14 Spalten umfassende intelligente Tabelle mit „überdeckender“ händisch eingestellten Formatierung
    –> händisch eingestellte Formatierung ist nicht intelligent, sodass bei Filterfunktion die farbliche Unterscheidung zwischen den Zeilen nicht mehr gegeben war (Bsp. Zeile 1 gelb, Zeile 2 grün, Zeile 3 gelb; Filter Z.2 ; Zeile 1 gelb, Zeile 3 gelb)

Idee: Anwenden der bedingten Formatierung. Hierbei habe ich schnell festgestellt, dass mir der Ansatz gänzlich fehlt –> an welche „Variable“ od. „Konstante“ binde ich die Formatierung, damit ich einfach eine normale intelligente Tabelle erhalte, bei der sich die Hintergrundfarben unterschiedlich vieler Spalten abschnittsweise unterscheiden, die sich zusätzlich mitverändern, sollte man filtern oder neue Zeilen an unterschiedlicher Stelle hinzufügen

Ich hoffe, ich konnte es einigermaßen verständlich machen, woran es bei mir scheitert. Weiterhin hoffe ich, dass Sie Zeit und Lust haben, sich mit dieser Problematik zu befassen.
Mit freundlichen Grüßen
Marcel

PS: Ihr Forum „Excel nervt …“ ist mega unterhaltsam und hilfreich und befasst sich verständlich mit atypischen Fragen im Umgang mit dem Programm. Einfach genial. Hat mir sehr gefallen!

#####

Hallo Herr Gröschel,

1.) ich würde nicht mehrere intelligente Tabellen verwenden. Das widerspricht dem Gedanken der Tabellen.

2.) Ich habe nachgeschaut: es geht nicht mit dem Stripset. DAS ist recht einfach aufgebaut und erlaubt nicht so viele Varianten, wie Sie es gerne hätten.

3.) Warum nicht bedingte Formatierung? Setze ich auch gerne ein. Beispielsweise um eine Zeile farblich zu hinterlegen.

Die Formel (bspw.):

=UND(SPALTE()>=11;SPALTE()<=14)

Hilft das?

Liebe Grüße

Rene Martin

####

Guten Abend Herr Martin,

vielen Dank, dass Sie so schnell geantwortet haben! Ihre Formel hat mir grundsätzlich weitergeholfen. Damit konnte ich das Problem der verschiedenen Spaltenfarben in meiner Tabelle lösen. Es war simpel und genial.

Dennoch blieb das Problem der farblichen Abgrenzung zwei aufeinanderfolgender Zeilen bestehen. Zuerst probierte ich es mit: 

=REST(ZEILE();2)

Hat soweit gut funktioniert, bis ich dann die Filterfunktion der intelligenten Tabelle verwendet habe. Im Ergebnis unterschied das Programm nicht zwischen eingeblendeten und ausgeblendeten Zeilen.

Basierend darauf kam ich durch weitere Recherche zur Ziellösung:

=REST(TEILERGEBNIS(3;$B$5:$B5);2)=0

(wobei die erste Spalte meiner Tabelle in Blattspalte B beginnt und ab Zeile 5 stets einen Wert beinhaltet)

Hierbei ist zu beachten, dass der gewünschte Effekt bzw. konkret die gewünschte farbliche Formatierung in der gesamten Tabelle nur dann funktioniert, wenn in Spalte B, in jeder Zelle der Tabelle auch ein beliebiger Wert steht. In meinem Fall befinden sich hier jeweils die Bezeichnungen.

Das wiederum bedeutet, dass beim Hinzufügen einer neuen Zeile vorerst nicht der gewünschte Effekt eintritt, sondern erst nach befüllen der neuen Zelle in Spalte B. Es ist unterm Strich noch nicht perfekt, aber es lässt sich damit arbeiten.

Mit freundlichen Grüßen

Marcel Gröschel

Ich habe im Homeoffice dem Paketboten aus Versehen nackt die Türe aufgemacht. Ich: „UPS.“ Er: „Nein, DHL.“

Menschen machen manchmal lustige Dinge.

Warum sie eine intelligente Tabelle nicht umbenennen dürfe, fragt eine Teilnehmerin in der Excelschulung.

Seltsam, denke ich: ein Tabellenblatt, eine intelligente Tabelle – Excel behauptet, dass dieser Name bereits vorhanden sei.

Ich werfe einen Blick in den Namensmanager:

Dort finde ich eine intelligente Tabelle und einen Namen. Was hat die Teilnehmerin gemacht?

Schritt 1: Wandle den Bereich in eine Tabelle um. Sie heißt nun Tabelle1:

Markiere die Liste und vergebe ihr einen Namen. Also nicht der Tabelle, sondern dem Bereich (hier: A1:F25)

Und so sieht man den Namen der Tabelle, aber nicht, dass ein anderer Bereich bereits mit dem Namen belegt ist, den man selbst gerne vergeben möchte … Perfide!

Heute habe ich gesehen, dass der Honig teurer geworden ist. Ich wusste gar nicht, dass Bienen mit Diesel fliegen.

Schöne Frage in der Excelschulung als wir (intelligente) Tabellen anschauen:

Wenn man hinter (oder unter) der Tabelle eine neue Spalte einfügt, wird diese in die Tabelle übernommen:

Wird jedoch VOR der Tabelle eine Spalte eingefügt, ist sie nicht Teil der Tabelle. Abhilfe schafft das Symbol „Tabellengröße ändern“, mit dessen Hilfe der Bereich der Tabelle erweitert werden kann:

Als mit klar wurde, wie lange es dauern würde, „Nana Mouskouri“ in die Schulbank zu schnitzen, habe ich beschlossen, lieber „Kiss“ zu hören.

Böse!

Eine Funktionen der intelligenten Tabellen sind „ausgegraut“. Warum?

Auch sortieren und filtern funktioniert nicht …

Man muss schon ganz genau hinschauen, um zu entdecken, dass es sich hier um ZWEI Tabellen handelt, die nebeneinander eingefügt wurden …

Ich bin jetzt in einem Alter, in dem dir der Körper am nächsten Tag ganz leise ins Ohr flüstert: Mach das nie, nie wieder!

Excelstammtisch letzte Woche.

Volker präsentiert eine clevere Lösung für die Datenüberprüfung. Er weist auf folgendes Phänomen hin:

Liegt eine intelligente Tabelle auf dem gleichen Tabellenblatt, bewirkt eine Erweiterung, dass die Liste der Datenüberprüfung auch erweitert wird:

Liegt die intelligente Tabelle jedoch auf einem anderen Tabellenblatt, wird die Liste der Datenüberprüfung nicht erweitert.

Danke an Volker Pagel für den guten Hinweis.

Am kältesten ist es da, wo es am draußensten ist.

Hallo genervter Excel-Verliebter,

kennst Du das Problem beim Löschen von Zeilen bei ausgeblendeten Spalten?

Eine intelligente Tabelle mit Filter.

Hier sind auch die ganzen Zeilen und alle gefilterten Zeilen markiert und STRG + Minus funktioniert nicht. Spalte B ist ausgeblendet.

Zeilen in einem gefilterten Bereich oder in einer gefilterten Tabelle können nicht verschoben werden.

Sind nur die Daten markiert und nicht die ganzen Zeilen, funktioniert es doch wieder mit Meldung. Es sind dann aber wieder alle Daten der Zeile weg und nicht nur die markierten Tabellenwerte.

Grüßle

Andreas (Thehos)

Früher haben wir uns gegenseitig eingecremt – da war es erotisch – heute ist es eher rheumatisch

Einer gewinnt! Oder: es kann nur einen Highlander geben.

Schöne Frage in der Excel-Schulung: kann ich eine (intelligente) Tabelle mit einer bedingten Formatierung versehen? Wissen Sie es? Welche Farbe gewinnt?

Gegeben sei ein Listenbereich:

Er wird in eine (intelligente) Tabelle konvertiert:

Natürlich kann ich die Schriftfarbe festlegen (beispielsweise für alle Flüsse aus Asien):

Aber: wer gewinnt, wenn ich eine Hintergrundfarbe hinzufüge?

Wer wohl?

Richtig: die bedingte Formatierung!

Und: was passiert, wenn die Liste verlängert wird? Richtig: dann wird die bedingte Formatierung, wie auch andere Formatierungen, mitgenommen:

Ich empfehle dennoch: entweder intelligente Tabelle OHNE Formatierung oder bedingte Formatierung nur mit Schriftfarbe.

Hast du in den 15 Sekunden, die ich nicht im Raum war, die ganze Schokolade alleine gegessen? – Mach mich nicht für deine Fehler verantwortlich!

Excelschulung. Schwerpunkt: Listen.

Ich erkläre den Nutzen und die Vorteile von (intelligenten) Tabellen. Beispielsweise Diagramme. Setzt man auf eine intelligente Tabelle ein Diagramm auf:

so wird die Erweiterung der Liste sofort ins Diagramm aufgenommen:

Was mich jedoch irritiert: Warum zeigt das Diagramm weder im Diagrammbereich, in den Achsenbeschriftungen noch in den Legendeneinträgen den Namen der Tabelle an – sondern immer noch den Bereich?

Die Pivottabelle zeigt doch auch die „korrekte“ Datenquelle an:

Was für ein Gefühl muss der Tropfen haben, der das Fass zum Überlaufen bringt. (Nikolaus Cybinski)

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:

Schade!

Weihnachten ist vorbei – ich habe alle Cookies gelöscht!

Ein bisschen doof ist es schon:

Ich habe eine (intelligente) Tabelle.

Ich füge eine Ergebniszeile hinzu – dort wird gerechnet:

Ich entferne die Farben und wandle sie in eine Liste um:

Was passiert? Die Formeln
=TEILERGEBNIS(109;Tabelle1!$E$2:$E$10)

bleiben stehen. Ebenso die Beschriftung der ersten Zelle: „Ergebnis“:

Wandelt man diese Liste nun erneut in eine Tabelle um:

ist die Formelzeile nun Teil der Tabelle.

Man kann erneut eine Ergebniszeile hinzufügen – das Ganze ist nun recht verwirrend:

Nehmen Sie Ihren Köter hier weg, ich spür schon einen Floh! – Komm, Rex, gehn wir. Die Frau hat Flöhe.

Das ist mir ja noch gar nicht aufgefallen. Volker hat darauf aufmerksam gemacht:

In einer Excelmappe befindet sich ein Tabellenblatt („Kontinente“) mit sechs intelligenten Tabellen: tbl_Europa, tbl_Antarktis, tbl_Afrika, …

Die Datei wird einmal als Excel-Arbeitsmappe (XLSX) und einmal Excel-Binärarbeitsmappe (XLSB) gespeichert.

Greift man mit PowerQuery auf die XLSX-Mappe zu, ist das Ergebnis bekannt: angezeigt wird das Tabellenblatt und die sechs intelligenten Tabellen:

Beim Zugriff auf die XLSB-Datei dauert der Zugriff nicht nur sehr, sehr lange – angezeigt wird nur das Tabellenblatt:

Danke an Volker Pagel für diesen Hinweis.

Sein Fazit: Don’t use xlsb!

Seine Kollegen kommentieren es:

Andreas:

Vielleicht ist xlsb dem alten Format xls zu ähnlich. Die xls aus 2003 lässt sich auch nicht einlesen.

Jens:
Volle Zustimmung!

Über Excel.Workbook ([Content]) werden xlsb auch nicht erkannt.

Ist echt nen Problem…xlsb ist bei uns relativ beliebt, da xlsm nicht per Mail versendet werden kann. Makro sind BÖSE 

Aber xlsb kann auch Makros enthalten.

Rapper rappen, Rockstars rocken, aber was machen eigentlich Popstars?

Hallo in die Runde, Dies ist mein erster Post. Normalerweise, wenn ich einen Bereich als Tabelle formatiert habe, wurde eine die Formel mit dem Drücken der Enter-Taste automatisch bis zum Ende der Tabelle ergänzt. Seit kurzem funktioniert das nicht mehr. Ich muss wohl irgendwas verstellt haben. Könnt ihr mir bitte sagen, wie ich das zurück stelle? Unter einstellungen ist formel erweitern auf automatisch. Aber das löst das Problem nicht. Ich nutze Excel für Mac. Vielen dank für Eure Hilfe Gruß Stephen

Hallo Stephen,

schau mal in der Autokorrektur nach.

LG :: Rene

Gestern mehrere Krimis im Internet bestellt. Heute Angebot für Messer und Müllsäcke bekommen. Die denken echt mit!

Das ist mir ja noch nie aufgefallen! Eine Teilnehmerin in der letzten Excelschulung hat mich darauf aufmerksam gemacht.

Setzt man in einer (intelligenten) Tabelle den Mauszeiger zwischen Überschrift und erste Zeile, kann man nur die Überschrift markieren:

Ist allerdings die Überschrift markiert …

… führt ein Klick auf die Unterkante dazu, dass die ganze Spalte markiert wird.

Aha – wieder etwas gelernt!

Ich habe gerade versucht, den Mähroboter mit einem Grasbüschel vom Nachbarn an den Gartenzaun zu locken. War wohl schon satt.

Zuerst habe ich mich geärgert. In PowerQuery gab es früher ein Symbol „Von Tabelle“. Daraus wurde in der Gruppe „Daten abrufen und transformieren“ das Symbol „Aus Tabelle/Bereich“.

Seit ein paar Tagen heißt es nun „Vom Blatt“

Muss das sein? Ständiges Umbenennen?

Frank Arentd-Theilen hat mich auf den Grund hingewiesen (danke für den Hinweis):

Ja – denn nun kann man Listen in Excel, die mit den neuen Arrayfunktionen erstellt wurden, beispielsweise mit FILTER, SORTIEREN und SORTIERENNACH in PowerQuery importieren:

Das funktioniert auch mit der Funktion SEQUENZ:

Okay – zugegeben – leider nicht immer. Wenn diese Matrixfunktionen innerhalb einer Liste stehen, wie beispielsweise hier in diesem Monatskalender:

dann wandelt PowerQuery die gesamte Liste in eine (intelligente) Tabelle um und – scheitert! Klar: Tabellen dürfen keine Matrixfunktionen verwenden …

Wie ist dein Verhältnis zu deinen Eltern? – 1:2

Gestern auf dem Excelstammtisch stellte Volker folgendes Problem bei/mit intelligenten (dynamischen; strukturierten) Tabellen vor.

Wir haben eine Liste, die zu einer intelligenten Tabelle verwandelt wurde:

Die Tabelle heißt tbl_Planeten.

Auf einem zweite Tabellenblatt wird Bezug auf diese Tabelle genommen; genauer: auf jede Spalte:

=WENNFEHLER(tbl_Planeten[@Planet];"")

Erstaunt stellt man fest, dass der erste Planet – Merkur – fehlt. Die Antwort ist simpel: „@“ bezieht sich auf die Informationen der aktuellen Zeile. Da die zweite Tabelle erst ab Zeile zwei beginnt (die erste fängt in der ersten Zeile an), ist der Bezug versetzt. Man muss also bei Tabellen gleich positionieren. Das birgt Gefahren.

Fazit: besser SO nicht Tabellen miteinander verknüpfen. Es gibt bessere Lösungen: PowerQuery sei an dieser Stelle genannt. Oder relative Bezüge.

Danke an Volker für diesen amüsanten und wichtigen Hinweis!

Ich habe 25 Jeans im Schrank. Eine, die ich jeden Tag trage, eine hippe, die ich nie trage, weil ich eigentlich nicht hip bin und 23 ausrangierte aus den Jahren 2000 – 2020, die ich für potenzielle Maler-, Garten-, Renovierungs- oder Umzugsarbeiten aufgehoben habe.

Zugegeben: geschmunzelt habe ich schon:

Excelschulung. Ich erkläre und zeige (intelligente, dynamische, formatierte, strukturierte) Tabellen:

Ein Teilnehmer behauptet, dass diese Tabellen seine Überschriften löschen würden. Ich bin irritiert. Ich habe eine Weile gebraucht, um zu verstehen, dass die weiße Schriftfarbe, die Excel als Standard einsetzt, nicht sehr clever ist bei einer gelben Hintergrundfarbe …

Natürlich ist die Überschrift noch vorhanden. Nur eben – sehr schlecht lesbar!

Seit ich gesehen habe, wieviel schlanker ein Bär nach seinem Winterschlaf ist, kommt mir das Konzept noch viel attraktiver vor.

Hallo Rene,

Ich hätte da nochmal eine Frage zu den Matrixfunktionen. Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.

Gruß

Hannes

Hallo Hannes,

du musst den Namen im INDIREKT in einen Bezug umwandeln. Meinst du das?

Liebe Grüße :: Rene

Hallo Rene,

ja genau, da sind einige Haken drin.

Die Liste in der Datenüberprüfung übernimmt offensichtlich nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine Rückgabewerte aus Funktionen.

Aber wenn ich innerhalb einer „formatierten“ Tabelle einen benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.

Wenn ich über die Sequenz-Funktion Werte ermittle und die in einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben, innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.

Die Problematik ist wahrscheinlich, dass die Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher auch die Bezugsherstellung mit der Indirekt-Funktion.

Keine Ahnung, ob man das irgendwie austricksen kann.

Gruß

Hannes

PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte

Ich habe gerade „ach-da-brauchst-du-nichts-draufschreiben-man-sieht-ja-was-drin-ist“ aus der Gefriertruhe geholt und bin gespannt, was ich heute koche.

Hi Rene,

sag mal: kann man in intelligenten Tabellen keine Matrixfunktionen verwenden? Hintergrund: ich möchte gerne mit der Funktion SORTIEREN oder SORTIERNNACH eine Liste sortieren und diese sortierte Liste als Basis für eine Tabelle verwenden. Also so:

Und dann passiert:

Hallo Johannes,

eine kleine Überlegung: (Intelligente) Tabellen „denken“ nur zeilenweise oder in Bezug auf eine ganze Spalte. Also:
=[@Umsatz]*19%
oder:
=SUMME(Tabelle1[Umsatz])
analog:
=TEILERGEBNIS(109;[Umsatz])

DAS kollidiert mit einer Arrayfunktion (EINDEUTIG, SORTIEREN, SORTIENNACH, FILTER), die dynamisch einen Bereich definiert oder ZUFALLSMATRIX und SEQUENZ, in die die Größe eines Bereichs eingetragen wird.

Moin Rene,

Danke Dir für die ergänzenden Infos 🙂   Ich habe es jetzt so gelöst … So hat sich auch der Vorteil ergeben, dass es etwas übersichtlicher ist 🙂

Sweet dreams are made of cheese, who am I to dis a Brie?

In der letzten Excelschulung waren wir erstaunt. Wir verknüpfen mehrere Tabellen miteinander:

Warum dauert das Verknüpfen der Daten in PowerQuery so lange?

Die Ursache war schnell gefunden: die Teilnehmerin hatte den Cursor nicht in die Liste gesetzt und so aus der Liste eine (intelligente) Tabelle erzeugt, sondern die ganzen Spalten markiert und dann diese (mit den leeren Zeilen) in eine Tabelle umgewandelt.

Der Anfang der Tabelle:

Und das Ende:

Als wir den Fehler entdeckt hatten, wollte die Teilnehmerin den Bereich „per Hand“ nach oben ziehen:

Ich werde nervös, wenn Aktion SOOO lange dauern. Ein kurzer Blick … das muss doch schneller gehen … und wirklich: es geht schneller. Das Werkzeug „Tabellengröße ändern“ in der Registerkarte „Tabellenentwurf“ bietet eine schnelle Möglichkeit Tabellen zu vergrößern und verkleinern. Man muss nur $A$1:$E$2156 tippen – und schon ist die Tabelle kleiner. Und PowerQuery schneller!

Deutsche stellen den Löwenanteil der Migranten Österreichs. Sollte sich die Entwicklung fortsetzen, muss damit gerechnet werden, dass in 50 Jahren alle Österreicher Deutsch sprechen.

Grrrr. Eine intelligente Tabelle. Ich möchte eine Zeile löschen. Aus Gewohnheit setze ich den Cursor in eine Zelle:

drücke [Strg] + [-] (und normalerweise bewege ich die Auswahl mit der Pfeiltaste nach unten. Was passiert? Es wird gelöscht, der Bildschirm wird nach oben verschoben, so dass ich nicht sehe, was gelöscht wurde:

Und richtig: die Spalte wurde gelöscht! Grrrr … Muss man höllisch aufpassen …. Grrrrr

Zum Glück war Halloween. So konnte ich das alte abgelaufene Naschzeugs an die dicken Nachbarkinder verteilen.

Verblüffend! Kennen Sie das? Ich habe ein Tabellenblatt mit einer intelligenten Tabelle. In der Arbeitsmappe wird ein Name oder mehrere Namen definiert, die Bezug auf diese intelligente (dynamische, formatierte, strukturierte) Tabelle nehmen:

Auf einem zweiten Tabellenblatt wird mit einer Formel (oder einer Datenüberprüfung) Bezug auf die Tabelle genommen:

Wird nun dieses Tabellenblatt dupliziert, werden auch die Namen dupliziert – sie liegen nun als Arbeitsmappennamen und Tabellenblattnamen vor:

Dupliziert man nun ein weiteres Mal das Duplikat, erhält man die Frage, „wohin mit den Namen“:

Der Name ist bereits vorhanden. Klicken Sie auf „Ja“, um diese Version des Namens zu verwenden, oder klicken Sie auf „Nein“, um die Version, die Sie verschieben oder kopieren möchten, umzubenennen.

Ich möchte den Namen nicht verschieben oder kopieren!

Und dann? Ist der Name drei Mal vorhanden … einmal als Arbeitsmappenname und zwei Mal als Tabellenblattname.

Hast du schon wieder mein Deo benutzt? – Ich bin Robin Hood! Ich stehle und verteile es unter den Armen!

Ups, das ist mir noch nie aufgefallen! Warum? Weil ich SO nicht arbeite.

Aus einer Datenquelle wird mit PowerQuery eine Abfrage gestartet. Auf diese Liste wird eine Formel aufgesetzt, allerdings nicht in der Schreibweise

=KKLEINSTE(Artikel[Einzelpreis];Artikel[@Einzelpreis])

rechnet, sondern in der Bezugsschreibweise:

=KKLEINSTE($F$2:$F$78;ZEILE(A1))

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!

Ich hätte gerne den Schokoriegel mit dem Löwen. – Lion? – Nein, kaufen!

Ich bereite gerade eine Excelschulung vor. Dabei bin ich über einen Artikel von Andreas Thehos gestolpert. Er beschreibt folgenden Bug in Excel:

Angenommen man hat eine Tabelle mit Berechnungen und wandelt sie in eine intelligente Tabelle um. Dabei werden in Bezügen der eigene Tabellenblattname genannt.

Sortiert man nun diese Tabelle, beispielsweise nach Spalte C (Radius), so wird die berechnete Spalte nicht mitsortiert.

Für „normale“ Listen habe ich diesen Bug hier auf excel-nervt.de schon mehrfach beschrieben. Das Erstaunlich ist, dass dieses Phänomen auch bei intelligenten Tabellen eintritt, die doch eigentlich die Aufgabe haben sollten, die Zeilen zusammenzuhalten.

Danke an Andreas Thehos für diesen Hinweis.

Fische sind – statistisch gesehen – die an seltensten überfahrenen Tiere.

Hallo Herr Dauphin,

das ist sehr spannend, was da passiert. Ich habe die Ursache gefunden:

Die Überschrift Ihrer Tabelle ist länger als 255 Zeichen.

Sie speichern die Arbeismappe. Sie greifen mit PowerQuery auf diese Datei zu. PQ greift mit der Zeile

= Table.TransformColumnTypes(#“Höher gestufte Header“,{{„ID“, Int64.Type}, {„Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.“, type number}})

darauf zu. Wir schreiben die Tabelle als intelligente Tabelle zurück nach Excel. Tabellen dürfen maximal 255 Zeichen in der Überschrift haben. Excel schneidet die restlichen Zeichen ab.

Soweit klappt de Workflow. Wenn ich allerdings diese Datei schließe und öffne, erhalte ich die Fehlermeldung.

Die Ursache: die Überschrift endet jetzt nicht mehr auf „dolor sit amet“.

Eine weitere Fehlermeldung ist die Folge:

Sehr spannend.
Lösung: Mit einer intelligenten Tabelle arbeiten! Dann ist die maximale Anzahl der Buchstaben der Überschrift begrenzt.

Danke für den Hinweis.
Schöne Grüße
Rene Martin

Ist hier die Selbsthilfegruppe für Naive? – Ja: Wir lernen gerade giftige Pflanzen am Geschmack erkennen zu können.

Ich greife auf eine intelligente (formatierte/dynamische/strukturierte) Tabelle zu. Ich möchte in mehreren Zellen die gleiche Formel stehen haben, die ich danach leicht modifizieren kann. Also kann ich mehrere Zellen markieren und die Formel mit [Strg] beenden. Dann steht in allen Zellen die gleiche Formel, beispielsweise:

=SUMME(Tabelle1[Betrag]/Tabelle1[Prämienpunkte])

Wenn ich allerdings Formeln der Gestalt:

=[@[Quartal 1]]/@HP[[Gesamt]:[Gesamt]]

habe – also: die Spalte „Quartal 1“ wird verschoben, die Spalte „Gesamt“ bleibt fest – dann wüsste ich keine Möglichkeit, dies durch geschickte Tastatureingaben zu erzeugen. Die doppelte Klammer muss getippt werden. Oder? Wer kennt eine Lösung?

Weder innerhalb der Tabelle noch außerhalb.

In der Potsdamer Konferenz von 1945 beschlossen die Siegermächte des Zweiten Weltkriegs die Aufteilung von Deutschland in die Besatzungszonen der USA, UdSSR, Großbritannien, Aldi Nord und Aldi Süd.

Amüsant. Ist Ihnen das schon aufgefallen? Wenn ich in einer intelligenten (dynamischen/formatierten/strukturierten) Tabelle eine Überschrift lösche, wird sie – je nach Spracheinstellung – durch eine andere (beispielsweise „Spalte1“) ersetzt:

Wenn Sie allerdings eine Überschrift eingeben, die länger ist als 255 Zeichen, wird dieser Text gelöscht. DFie maximale Anzahl der Zeichen einer Überschrift beträgt 255 Zeichen.

Das hat Auswirkungen auf PowerQuery, der Daten als intelligente Tabelle zurückgibt. Ist eine Überschrift länger, wird der restliche Text abgeschnitten. Werden gleiche Überschriften importiert, werden sie durchnummeriert.

Ich bringe die Mülltonne an die Straße. Ich will, dass sie mal unter andere Tonnen kommt und Sozialverhalten lernt.

Gewundert habe ich mich schon: ich erstelle eine Formel mit einem Bezug auf eine (intelligente/formatierte/dynamische) Tabelle:

=XVERWEIS($F$2;tbl_Staaten[Staat];tbl_Staaten[Jahr der Unabhängigkeit];;;1)

Mein Erstaunen ist groß als ich die Formel nach rechts ziehe:

=XVERWEIS($F$2;tbl_Staaten[Jahr der Unabhängigkeit];tbl_Staaten[Staat];;;1)

Der absolute Bezug $F$2 bleibt. Die konstante Zahl 1 bleibt. Aber die beiden Spalten werden vertauscht. Klar – ein Bezug auf eine Spalte einer Tabelle ist immer relativ. Da die Formel beim nach Rechts-Ziehen nicht „weiterwandern“ kann, beginnen die Bezüge von vorne. Und wie macht man einen absoluten Bezug innerhalb einer Tabelle? Da war doch mal was?!

Richtig: Andreas Thehos hat es einmal gezeigt. Man muss die Formel folgendermaßen schreiben (mit doppelten eckigen Klammern).

https://www.youtube.com/watch?v=LM_neLGwCp8

=XVERWEIS($F$2;tbl_Staaten[[Staat]:[Staat]];tbl_Staaten[[Jahr der Unabhängigkeit]:[Jahr der Unabhängigkeit]];;;1)

Dann funktioniert es. Und jetzt kann ich schnell den letzten Parameter 1 durch -1 ersetzen:

Schluss mit Binsenweisheiten und anderen unlustigen Sprüchen in der Headline!!

Verständlich, aber dennoch erstaunlich: Wenn eine Liste Datumsangaben in der ersten Zeile hat und wenn man diese Liste in eine (intelligente/dynamische) Tabelle verwandelt, so werden diese Datumsangaben zu Text. Klaro – Feldnamen/Überschriften müssen Text sein.

Beim Zurückkonvertieren in einen Bereich bleiben natürlich die Datumsangaben als Text stehen:

Scheiss auf’s Pferd – echte Männer kommen auf dem Motorrad.

Bulgarian Excel Days 2019.

Großartig.

Masterclass bei Ken Puls über Power Query: https://www.exceldays.itraining.bg/en/about-masterclasses/

Klasse. Auch er nörgelt gerne: Beispielsweise darüber, dass man beim Erstellen einer Tabelle ([Strg] + [T] oder als Tabelle formatieren oder Einfügen / Tabelle nicht den Namen der (neuen) Tabelle eingeben kann:

Polizei frohlockt: Bullenhitze

Excelschulung. Wir üben die Datenüberprüfung (Gültigkeit).

Eine Teilnehmerin fragt, warum sie keine Datenüberprüfung einschalten darf:

Des Rätsels Lösung: sie hat nicht nur die (intelligente/formatierte) Tabelle markiert und Bereich außerhalb der Tabelle – sie hat auch die Ergebniszeile der Tabelle eingeschaltet. DORT kann man keine Datenüberprüfung einschalten:

Nur keine Hemmungen. Ich sag dann schon stop.

Auch mit Heike Hofert (http://www.der-lerncoach.de/) konnten wir für unsere Exceltage 2019 eine erfahrene und freundliche Referentin gewinnen. Sie referierte über dynamische Diagramme und „intelligente“ (formatierte, dynamische, Layout-)Tabellen, die sie scherzhaft „Tabellchen“ nannte.

Microsoft beschriftet in der deutschen Version die Register der Tabellenblätter mit Tabelle1, Tabelle2, Tabelle3, die Layouttabellen ebenso mit Tabelle1, Tabelle2, Tabelle3, … – nicht sehr intelligent!

Eine Stute kann auch ein Esel sein.

Exceltage 2019 in München. Letztes Wochenende. Auch Martin Weiß (https://www.tabellenexperte.de/) war dabei – als Referent hat er drei Referate über seine Spezial- und Lieblingsthemen: Pivottabellen, bedingte Formatierung und Kalender/Datumsfunktionen gehalten. Sie wurden mit Begeisterung besucht.

Interessant und verblüffend fand ich seine Bemerkung zum Unterschied zwischen SUMME([@Länge]) und SUMME([Länge]) in intelligenten Tabellen:

Warum hat die Spülmaschine keinen Schleudergang?

Ich bereite unsere Exceltage 2019 vor. Die Skripte zu den Vorträgen trudeln ein; ich lese sie Korrektur. Mit Lorenz Hölscher (http://www.software-dozent.de/) konnten wir einen hervorragenden Dozenten gewinnen, der vier Referate hält. In einem davon macht er einen Vorschlag einer Eingabemaske. Der Grund:

die „alte“ Datenmaske, die man immer noch über [ALT] / [N] / [M] aufrufen kann, ist „unzulänglich“. Seine Kritik fasst er in einem Bild zusammen:

Mein Schienbein hilft mir im Dunkeln Möbel zu finden.

In der letzten Excelschulung zeige ich Tabellen, die manche Trainer „formatierte Tabellen“ nennen. Ich zeige einen der vielen Vorteile: jede zweite Zeile bleibt dunkel, jede andere zweite Zeile hell – egal, ob man sortiert, filtert oder eine Zeile einfügt:

Ein Teilnehmer meldet sich und sagt, dass er dieses Verhalten in seiner Tabelle nicht feststellen kann:

Ich wusste, was er gemacht hat: er hat die Tabelle in einen Bereich konvertiert. Dadurch bleiben die Formatierungen bestehen und anschließend wieder in eine Tabelle verwandelt. Somit hat er noch die „alten“; „harten“ Farben …

Alles im Leben hat seinen Preis, auch Dinge, von denen man glaubt, man bekommt sie geschenkt!

In der letzten Excelschulung zeige und erkläre ich (intelligente/dynamische/formatierte) Tabellen. Ich zeige, dass beim Runterscrollen die Überschriftszeile als Spaltenkopf verwendet wird:

Eine Teilnehmerin meldet sich und zeigt mir, dass es bei ihr nicht funktioniert:

Ich habe eine Weile hinschauen müssen, um festzustellen, dass der Cursor außerhalb der Tabelle platziert wurde. Wenn man den Bereich außerhalb einer (intelligenten/dynamischen/formatierten) Tabelle herunterscrollt, werden nicht die Überschriften zu Spaltenköpfe:

Wer nackt badet, braucht keine Bikinifigur.

Versuchen Sie mal Folgendes: Erstellen Sie eine neue, leere Excelmappe mit zwei Tabellenblättern. Auf dem ersten Blatt befindet sich eine (intelligente/dynamische) Tabelle. Markieren Sie beiden Registerkarten der Tabellen und kopieren diese in eine andere Arbeitsmappe. Excel verweigert sich:

Eine Gruppe von Blättern, die eine Tabelle enthalten, kann nicht kopiert oder verschoben werden.

Hä?

Nach zwei Flaschen Wein habe ich endlich meine Steuererklärung gemacht. Ich bekomme 53 Millionen Euro zurück!

Erstaunlich. Wenn man eine Liste in eine (intelligente/dynamische) Tabelle verwandelt, wird der Name (hier: „Nordwind“) im Namensmanager angezeigt. Man kann damit arbeiten wie mit anderen Namen, beispielsweise

=ANZAHL2(Nordwind)

Leider wird der Name nicht auf dem Tabellenblatt angezeigt. Vergibt man dagegen selbst einen Namen, erscheint dieser auf dem Tabellenblatt, wenn der Zoom unter 40% liegt.

Im nächsten Leben mache ich etwas mit ohne aufstehen.

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.

Don’t waste your time on a guy, who isn’t willing to waste his time on you

Heute bin ich mal nicht genervt, sondern ziemlich begeistert.

Seit einigen Tagen befinden sich in Excel 365 in der Registerkarte „Daten“ zwei neue Symbole „Aktien“ und „Geografie“.

Probieren wir es aus.

Mit Power Query („Daten abrufen und transformieren“) greife ich auf Wikipedia zu:

https://de.wikipedia.org/wiki/Liste_der_Gro%C3%9F-_und_Mittelst%C3%A4dte_in_Deutschland

Wende nun das Symbol „Geografie“ an. Amüsiere mich, dass Munich, Cologne und Nuremberg übersetzt werden. Die Population (über das Smarttag auf der rechten Seite) wird sehr gut aufgelistet. Ebenso weitere Informationen: Bundesland, Fläche, …

Okay – für alle Nörgler – einige Städte werden nicht korrekt erkannt (Schwerin, Kempten, Aalen), Berlin und Hamburg werden nicht als Stadtstaaten erkannt (Bremen allerdings schon); viele Städte haben noch keinen Kommentar.

Hut ab – das ist mal was!

 

 

Habe selbst viele Fehler, daher darfst Du gern perfekt sein!

Ein Kommentar zu den Zikelbezügen von Michael:

Hallo ,

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.

Danke für den Hinweis, Michael – klasse!

Wie ich so im Bett bin? Ich schlafe auf dem Bauch, sabbere und rede ab und zu.

Ein bisschen merkwürdig ist es schon.

Wir erstellen eine (intelligente/dynamische/formatierte) Tabelle, wobei in den Spalten die Quartalswerte eingetragen werden:

Die Spalte „Gesamt“ berechnet die Summe der vier Quartale. Möchte man nun die Prozentwerte berechnen, bietet sich die Formel

=[Quartal 1]/[Gesamt]

an.

Der gewiefte Excelanwender wird sofort erkennen, dass Excel beim Nach-Rechts-Ziehen der Formel ein Problem haben wird. Und richtig: Ein Bezug wie [Quartal 1] ist ein relativer Verweis. Das bedeutet: [Gesamt] „wandert“ nach rechts. Aber wie fixiert man die Spalte?

Die Lösung:

=[Quartal 1]/[[Gesamt]:[Gesamt]]

wobei [[Gesamt]:[Gesamt]] ein weiteres Mal in geschweiften Klammern stehen muss.

Excel vervollständigt diese Formel mit dem Tabellennamen (Hier: „HP“):

Ein bisschen merkwürdig ist diese Schreibweise schon.

Reality is disappointing

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.

Meine Angst vor der Autokorrektur wichst von Tag zu Tag

Wer mit (intelligenten / dynamischen / formatierten) Tabellen arbeitet, weiß, dass Formeln automatisch nach unten ausgefüllt werden. Weiß aber auch, dass dieses Verhalten manchmal deaktiviert ist.

Aber wo befindet sich diese Option, mit der man es ein- oder ausschalten kann?

Nein – nicht in Optionen / Erweitert, auch nicht in Optionen / Formeln oder Optionen / Daten, sondern in der AutoKorrektur: Optionen / Dokumentprüfung / AutoKorrektur-Optionen. Und dort in der Registerkarte „AutoFormat während der Eingabe“.

Schon gut versteckt!

Danke an Andreas Thehos für diesen Hinweis.

Schuhe können Dein Leben verändern, denk an Cinderella!

Perfide! Wenn man in einer (intelligenten) Tabelle eine Formel verwendet, die den Tabellenblattnamen verwendet – beispielsweise

=WENN(Tabelle1!D7=“m“;20;10)

Wenn man anschließend die Tabelle in einen normalen Bereich konvertiert:

Wenn man schließlich die neue Liste sortiert:

So erhält man Chaos! Die Zeilenbezüge werden nach der Sortierung nicht mitgenommen!

Danke an Andreas Thehos, der auf den Excellent Days 2018 diesen Bug vorgestellt hat.

Männer sind wie guter Wein…sie werden meistens mit dem Alter besser!

Schon blöde: (Intelligente, dynamische, formatierte) Tabellen lassen sich nicht erweitern, wenn das Blatt geschützt ist:

Verständlich: Man entsperrt einen festen Bereich und schützt anschließend das Tabellenblatt ohne diesen fest definierten Bereich. Er wächst leider nicht dynamisch mit.

Danke an Andreas Thehos, der dieses Problem auf den Excellent Days 2018 vorgestellt hat.

Hallo, ich habe gleich 2 Fragen.

Hallo, ich habe gleich 2 Fragen.

 

1- Kann ich irgendwie einstellen, dass meine Tabelle, welche sich bei Bedarf automatisch vergrößert ( wenn man ganz unten was eingibt und enter drückt), sich auch weiterhin so verhält, wenn das Blatt schreibgeschützt ist? Es geht darum, die untere Zeile zu sperren, weil dort Unterschriftenfelder vorhanden sind. Die beste Lösung wäre, wenn die Unterschriftenfelder „mitwandern“ würden, wenn die Tabelle vergrößert wird, aber das hab ich schon aufgegeben…

 

2-Kann man in irgend einer Art und weise Überschriften (ähnlich wie bei Word) als solche definieren, sodass sie im Inhaltsverzeichnis mit angezeigt werden? Habe bislang nur die Möglichkeit gefunden, das Inhaltsverzeichnis der Tabellenblätter zu erstellen, welches sich immerhin schon automatisch aktualisiert:

=WENN(ZEILEN($1:13)>ANZAHL2(Alle);““;HYPERLINK(„#'“&INDEX(Alle;ZEILEN($1:13))&“‚!A1“;TEIL(INDEX(Alle;ZEILEN($1:13));FINDEN(„]“;INDEX(Alle;ZEILEN($1:13)))+1;31)))

 

Hallo Frau Roesch(?),

 

zu 1.) ich fürchte, das ist leider nicht möglich. Sie können zwar einen festen Bereich definieren, der nicht geschützt ist, aber leider keinen dynamischen. Müsste man programmieren.

 

zu 2.) die einzige Möglichkeit, die ich sehe, um Überschriften zu definieren, ist entweder über eine Hilfsspalte oder über ein bestimmtes Textkriterium, also beispielsweise alle Texte, die mit „Ü“ beginnen. Dann kann man mit geschickten KKLEINSTE oder AGGREGAT diese Texte „einsammeln“.

 

schöne Grüße

 

Rene Martin

Ich habe mich gerade bei parship angemeldet. Bin aufgeregt: nur noch 11 Minuten Single.

Amüsant.

Ich erstelle eine kleine Liste und trage unter ihr Daten ein:

Diese Daten werden gelöscht. Die Liste wird ein eine (intelligente/dynamische) Tabelle umgewandelt. Klappt hervorragend: die benutzten Zellen werden nicht verwendet:

Bemüht man nun den Assistenten Daten / Text in Spalten, so „erkennt“ dieser die ehemaligen Informationen …

und erweitert die Tabelle. Schön blöde!

HIP HOP hieß früher Stottern und war heilbar.

Amüsant. Excelschulung. Wir erstellen eine (intelligente / dynamische) Tabelle. Wir filtern diese Liste. Ich frage, wie viele gefilterte Datensätze diese Liste enthält. Die Antwort befindet sich links unten. Ich zeige, dass man die Anzahl der Datensätze auch so herausbekommen kann, indem man eine Spalte markiert und dann einen Blick unten rechts auf die Statuszeile wirft. Erkläre den Unterschied zwischen „Anzahl“ und „Numerische Zahl“. Wenn sich in einer Spalte Zahlen befinden, die Überschrift jedoch Text ist, dann liefert die markierte Zahlenspalte einen Wert mehr bei „Anzahl“ als bei „Numerische Zahl“.

Eine Teilnehmerin meldet sich und sagt, dass bei ihr bei „Anzahl“ der gleiche Wert steht wie bei „Numerische Zahl“. Verblüfft schaue ich nach: Tatsächlich!

Der Grund: sie hat die Tabelle nach unter gescrollt, so dass aus der ersten Überschriftszeile ein Spaltenkopf wurde. Excel markiert diesen nicht mit und somit wird er auch nicht gezählt.

Auf dem Boden der Tatsachen liegt eindeutig zu wenig Glitter.

Lustig: Heute in der Excel-Schulung: Thema: Listen, große Tabellen, Datenmengen.

Ich beginne den Unterricht mit ein paar nützlichen  Tastenkombinationen:

* [Strg] + [Ende] und [Strg] + [Pos1]: Bewegen zum Ende und Anfang und Ende der Tabelle

* [Strg] + [↓]: Bewegen zum letzten gefüllten Eintrag der Spalte (Analog die anderen Pfeiltasten)

* [Shift] + [Strg] + [Ende], [Shift] + [Strg] + [Pos1], [Shift] + [Strg] + [↓]: Markieren bis zum Ende der Spalte oder der Tabelle

* [Strg] + [*]: Markieren des zusammenhängenden Bereichs

Danach wollte ich die (intelligente) Tabelle zeigen und bat die Teilnehmer über Einfügen / Tabelle eine solche zu erstellen. Eine Teilnehmerin sagte, dass sie keine Tabelle erstellen kann – das Symbol sei „ausgegraut“ (inaktiv):

Die Lösung habe ich schnell gefunden: Beim Ausprobieren der Tastenkombinationen hatte sie aus Versehen

[Shift] + [Strg] + [Bild↓] gedrückt: mit [Strg] + [Bild↓] bewegt man sich zum nächsten Tabellenblatt; mit [Shift] + [Strg] + [Bild↓] markiert man bis zum nächsten Tabellenblatt. Erkennbar an den beiden weiß formatierten Registerkarten und an dem Text „Gruppe“ in der Titelzeile.

Ich habe eiserne Prinzipien! Wenn sie Ihnen nicht gefallen habe ich auch noch andere.

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.

Wenn der letzte Strohhalm, an dem man sich voller Verzweiflung klammert in einem Cocktail steckt, dann geht’s eigentlich.

Schade eigentlich. Microsoft verwendet in Excel immer stärker Tabellen (wir nennen sie „intelligente Tabellen“, „Formatierte Tabellen“ oder „dynamische Tabellen“). Leider kann man sie nicht auflisten lassen. Das wäre praktisch – denn wenn man beispielsweise mit der Datenüberprüfung auf eine solche Tabelle zugreift, muss man dies mit

=INDIREKT(„Tabellenname“)

machen. Hier wäre – ebenso wie beim Namensmanager – eine Auswahlliste praktisch.

Ich lese keine Anleitungen, ich drücke Knöpfe bis es klappt…

Es ist sehr vernünftig! Ein Lob an Microsoft.

Wenn auf einem Tabellenblatt zwei (intelligente / dynamische) Tabellen nebeneinander liegen, darf man nicht eine Zeile (über beide Tabellen hinweg) verschieben:

Über die Meldung

„Das wird nicht funktionieren, weil dadurch Zellen in einer Tabelle in Ihrem Arbeitsblatt verschoben würden.“

kann man sich streiten. Ich hätte den Hinweis etwas anders formuliert …

Ich bin nicht faul, ich bin grad im Energiesparmodus.

Unkaputtbar? Nicht ganz!

Gestern in der Excel-Schulung habe ich (intelligente/dynamische) Tabellen vorgestellt. Habe gezeigt, dass man sie nicht „kaputtsortieren“ kann, dass man nicht eine Zelle einfügen kann, sondern nur eine Zeile. Eine Teilnehmerin versucht es: markiert ein Stück Tabelle und verschiebt es mit Drag & Drop nach unten.

Klar kann man auch diese Tabellen kaputtmachen – allerdings erhält man immerhin einen Warnhinweis vorher.

Gott ist mit an Sicherheit grenzender Wahrscheinlichkeit ein tschechischer Schlagersänger.

Walter ist genervt. Er arbeitet gerne mit (intelligenten) Tabellen. Jedoch benötigt er manchmal Formeln der Form $A1:$F1. Das ist in (intelligenten) Tabellen jedoch nicht möglich.

Walter weiß, dass er diese Formeln eintragen kann. Walter möchte das aber nicht. Also geht Walter auf die Suche und findet in den Optionen in der Kategorie „Formeln“ die Einstellung „Tabellennamen im Formular verwenden“. Walter schaltet diese Option aus. Nun ist Walter glücklich – denn nun kann er markieren und erhält $A1:$F1 statt Tabelle1[Bundesland].

Alles, was ich will ist teuer, macht dick oder antwortet mir nicht

In der letzten Excel-Schulung habe ich die (intelligenten) Tabellen vorgestellt. Ich zeige, dass die Spaltenköpfe A, B, C, … durch die Überschriftszeile ersetzt werden:

Ein Teilnehmer fragt mich, warum es bei ihm nicht funktioniere.

Die Antwort ist schnell gefunden: er hatte eine Fixierung eingeschaltet.

Wenn Superkleber überall klebt, warum dann nicht an der Innenseite der Tube?

Heute in der Excel-Schulung haben wir folgendes interessante Phänomen festgestellt:

In einer (intelligenten) Tabelle befindet sich eine Reihe mit Datumsangaben, die nicht fortlaufend vorhanden sind. Auf der Reihe und den zugehörigen Werten (beispielsweise Geldbeträgen) wird nun ein Diagramm aufgesetzt. Das Ergebnis verblüfft: die Abstände der Datumsangaben werden gemäß ihrem Datumswert auf der Achse abgetragen.

Und was, wenn ich das nicht will?

Nun – Excel interpretiert bei intelligenten Tabellen die „automatische Auswahl basierend auf den Daten“ gemäß der Datumswerte. Kann man umschalten in „Textachse“.

Wenns nich im Regal steht hamwers nich

Auch hübsch. Wir erstellen eine intelligente Tabelle.

Tragen unterhalb einen neuen Datensatz ein:

bemerken, dass dieser Datensatz bereits vorhanden ist und drücken [Strg] + [-], um ihn zu löschen. Der Cursor bewegt sich an den Anfang der Tabelle. Ich drücke erneut [Strg] + [-] und stelle mit Entsetzen fest, dass Excel nicht die aktuelle Zeile, sondern die aktuelle Spalte ohne Nachfragen gelöscht hat:

Blöde Party, wenn ich meine Hose finde, gehe ich!

Gestern in der Excelschulung. Wir üben Tabellen (intelligente Tabellen, dynamische Tabellen, formatierte Tabellen). Eine Teilnehmerin fragt, warum bei ihr das Symbol grau unterlegt ist – warum sie keine Tabelle erstellen darf. Es gibt zu viele Möglichkeiten – ich gehe zu ihrem Rechner:

Die Ursache ist schnell gefunden – sie hat bereits eine Tabelle erstellt; diese allerdings wieder weiß eingefärbt. Das sieht man deutlich an den Tabellentools / Registerkarte „Entwurf“.

Bevor Du mit dem Kopf durch die Wand willst – überlege: was mache ich im Nebenzimmer!

Heute: eine Schulung „umsteigen von Office 2007 auf Office 2016“. In 3,5 Stunden. Viel zu wenig Zeit für vier Programme (natürlich kamen auch noch Fragen zu OneNote). Also die wichtigsten Dinge ansprechen.

Beispielsweise die Datenschnitte in Excel.

Eine Teilnehmerin meldete sich und fragte, warum bei ihr die Datenschnitte nicht funktionieren:

Merkwürdiger Datenschnitt

Die Antwort war schnell gefunden: Die Tabelle wurde nicht als „intelligente Tabelle“ formatiert. Das kann man schnell an der fehlenden Registerkarte „Tabellentools“ erkennen.

Wahrscheinlich hatte sie die Option Einfügen / Tabelle betätigt und dann die Tabelle wieder über Tabellentools / Entwurf / In Bereich konvertieren zurück verwandelt. Sieht man nicht auf den ersten Blick, weil die Formatierungen bleiben.

Wir lernen heute: Datenschnitte können nur auf Pivottabellen oder intelligenten Tabellen aufgesetzt werden.

Sport gibt dir das Gefühl nackt besser auszusehen. Tequila übrigens auch.

Zugegeben: ein bisschen suspekt sind mit die Tabellen, die intelligente Tabellen oder formatierte Tabellen immer noch. Wenn Sie beispielsweise eine fortlaufende Nummerierung erzeugen möchten, können Sie auf eine Zahlenspalte zugreifen und dort beispielsweise die Formel eintragen:

=ANZAHL($C$1:C2)

20161212tabelle01

Die Liste wird korrekt gefüllt:

20161212tabelle02

Wird jedoch ein neuer Datensatz eingetragen, dann „zerschießt“ Excel die vorletzte Formel:

20161212tabelle03

Abhilfe schafft bei der Eingabe der Formel

=ANZAHL($C$1:C2)

anstelle des Zellnamens C2 ein Klick auf die Zelle C2. Dann wird aus der Formel:

=ANZAHL($C$1:[@Alter])

Und damit funktioniert es.

20161212tabele04

Ein großes Dankeschön an Christian für diesen Hinweis.

Ich kann auch nett – bringt aber nix!

Doch, manche Dinge begeistern mich:

Wird in einer intelligenten Tabelle (in einer formatierten Tabelle) eine ganze Zeile markiert und diese am Rand heruntergezogen, wird die darunterliegende Zeile überschrieben. Abhilfe schafft das Drücken der Shift-Taste:

ganze Zeile verschieben

ganze Zeile verschieben

Markiert man dagegen nur die Zellen einer Zelle bis zum Ende der Tabelle, kann man ohne weitere Taste diese Zeile herunterziehen:

20161123tabelle02

Ein dummer Mensch macht zu allem eine Bemerkung … ein Kluger bemerkt alles.

Ich versuche mich mit den „intelligenten Tabellen“ (den formatierten Tabellen) anzufreunden. Aber sie machen es mir verdammt schwer.

Ich habe eine Artikelliste – umgewandelt als „intelligente Tabelle“. Soweit, so gut. Wenn ich neue Daten unterhalb der Liste eintrage, wird die Liste automatisch erweitert. Prima.

Wenn ich allerdings statt eines Wertes eine Formel eintrage – beispielsweise um aus dem Bruttowert den Nettowert zu berechnen, so beendet Excel die Liste und zeigt mir die Formale als Formel unterhalb der Tabelle an. Das will ich aber nicht!

DAS will ich nicht!

DAS will ich nicht!

Excel zwingt mich zuerst mindestens einen Wert in die neue Zeile einzutragen – DANN erst erlaubt er Formeln …

Können die nicht oder wollen die nicht?

Microsoft kann einfach nicht einheitlich:

Erstellt man aus einer Excelliste eine Tabelle (eine intelligente Tabelle; eine formatierte Tabelle) über Einfügen / Tabelle (um daraus anschließend mit PowerPivot zu arbeiten), schlägt Excel vor, dass die Liste eine Überschrift hat.

Wählt man den direkten Weg, erstellt also eine PowerPivot-Tabelle aus einer Liste (PowerPivot / Zu Datenmodell hinzufügen), fragt Excel nicht, ob die Liste eine Überschrift enthält.

Hätte man ja auch einheitlich machen können …

20160702PowerPivot0120160702PowerPivot02

„Bunt ist meine Lieblingsfarbe.“ (Walter Gropius)

Gestern in der Excelschulung wurde eine schöne Frage gestellt:

Warum formatiert er die Tabelle nicht mit der Farbe, dessen Schema ich auswähle? Beispielsweise Blau.

Kein bunt?

Kein bunt?

Die Antwort erhielt ich durch eine Gegenfrage:

„Haben Sie die Liste als Tabelle formatiert, dann wieder in einen Bereich konvertiert und anschließend wieder zu einer Tabelle gemacht?“ – „Ja“ – „Klar – beim Zurückkonvertieren bleiben die Farben erhalten – nun ist die Tabelle »hart« formatiert.

Gehen Sie zurück auf Los! Ziehen Sie keine 4.000 ein! – Oder: mehr als drei Athleten geht nicht.

Ich verstehe es nicht. Ich habe eine kleine Liste erstellt und ein Diagramm aufgesetzt. Damit das Diagramm sich die aktuellen Zahlen holt, habe ich den Datenbereich über Einfügen / Tabelle in eine intelligente Tabelle verwandelt. Normalerweise – wenn ich in der letzten Zelle rechts unten den Cursor platziere und dort die Tabulatortaste drücke wird eine neue Zeile angefügt und die Daten werden in das Diagramm übernommen. Bei mir jedoch nicht – nach dem dritten Athleten ist Schluss. Er hüpft wieder zurück in Zelle A97. Warum?

Keine neuen Daten möglich!

Keine neuen Daten möglich!

Die Antwort könnten Sie auch alleine finden. Unter Ihrer Liste ist Zeile 101 ausgeblendet. Da Excel vorhandene Zellen zur Liste (Tabelle) hinzufügt, kann er das in Ihrem Fall nicht, weil die Daten sichtbar sein müssen. Deshalb erfolgt der Sprung „zurück zum Anfang“ auf A97.

Excel bringt mich noch zum Wahnsinn! – Warum eigentlich „noch“?

Ich verstehe es nicht. Ich wollte in einer Liste eine Überschrift ändern und habe nun dort den Text „Datum“ eingetragen. Tippen darf ich ja, aber sobald ich die Zelle verlasse steht plötzlich „Datum2“ drin. Ich habe es nicht reingeschrieben. Ich schwör’s!

Ich tippe - Excel ändert!

Ich tippe – Excel ändert!

Liste20160205_2

Sie müssen genau hinschauen. Ihre Liste ist als Tabelle formatiert. Das erkenne ich an der Registerkarte Tabellentools / Entwurf. Und ich sehe, dass es bereits eine Spalte Datum gibt (Spalte A). Tabellen (oder intelligente Tabellen) erlauben jeweils nur eindeutige Feldnamen – das heißt: Sie dürfen nicht zwei Spalten mit der Überschrift „Datum“ versehen. Sonst greift Excel ein!

Daran habe ich es erkannt.

Daran habe ich es erkannt.