Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

Tabellenausmaße dynamisch berechnen per Formel 1.0

Anleitung Microsoft Microsoft Office

Mitglied: BigWim

BigWim (Level 2) - Jetzt verbinden

17.07.2010, aktualisiert 19:55 Uhr, 9776 Aufrufe

Eine Frage zu einem Bestellformular brachte mich auf die Idee, mal zu zeigen, wie das Ausmaß einer Tabelle (Anzahl Spalten / Anzahl Zeilen) auch dynamisch per Formel berechnet werden kann.

Die Vorteile liegen darin, dass die Tabelle "mal eben" um Spalten und/oder Zeilen ergänzt werden kann, ohne seine Bezüge oder definierten Namen anzupassen. Oder wenn man Gültigkeitsbereich von Zellen beschränkt, diese nicht neu anzupassen sind bzw. die lästigen Leereinträge erst gar in Erscheinung treten.

So, nun zur Anleitung

Wir bauen uns eine komplexe Tabelle wie diese auf dem Arbeitsblatt "Inventar":
dcaca96e287b9ac90e65ccb8d02c4f70 - Klicke auf das Bild, um es zu vergrößern

Letztendlich benötige ich für Bereichsangaben den Standort und die Größe meine Tabelle oder wie Excel es ausdrückt:
Inventar!A1:B4 - wenn ich keine Überschrift habe
Inventar!A2:B4 - wenn ich doch mal eine Überschrift habe


Zwecks Übersicht erstelle ich immer ein Arbeitsblatt "Technik". Ich fang mal mit dem Ergebnis an und lasse die Erklärungen folgen:
(für die fortgeschritten Anfänger wie mich: Sicherlich lassen sich einige Formeln zusammenfassen, aber ist ja eine Anleitung ...)
f232395e700c78cce70f2435b3887889 - Klicke auf das Bild, um es zu vergrößern

Als erstes benötigen wir eine Zelle, die den Namen des Arbeitsblattes speichert. Also B1

Der Einfachheithalber gehe ich jetzt mal davon aus, das die Tabelle immer ab A1 beginnt. Geht auch anders, aber das wäre was für Version 2.0
Als müssen wir jetzt wissen, wieviele Zellen denn ausgehend von A1 nach rechts (Spalten) und unten (Zeilen) befüllt sind.
Die Schreibweise der Bereichsangabe dafür findet sich in B2 und B3 - die Berechnung übernimmt die Formel Anzahl2.

Aber wie mache ich einer Formel klar, dass die Zelle, die ich da angebe, nicht die Zelle ist, mit der etwas getan werden soll, sondern nur ein Verweis ist, in der der eigentliche Bereich genannt ist? Und hier kommen wir zu dem "Geheimnis" dieser Anleitung: Formel Indirekt. In den Zellen setze ich diese Formel zum ersten Mal ein. Die Formel Anzahl2 in B4 und B5 verwendet dank Indirekt die Bereichsgaben aus B2 (Anzahl der Spalten) und B3 (Anzahl der Zeilen).

B6 habe ich eingebaut, weil es ja manchmal entscheidend ist, ob die Tabelle Überschriften hat oder nicht. Wenn Eure Tabellen immer Überschriften hat, könnt ihr es natürlich weglassen; damit könnte dann auch B7 entfallen. Wäre ja im Falle von "immer Überschriften" immer A2....

Um die Formeln (oberflächlich betrachtet) nicht allzu kompliziert zu machen, habe ich die Berechnung Anfang der Tabelle (B7) und Ende der Tabelle (B8) ausgelagert. Ich weiß nicht, ob ich B8 erklären muss. Vorsichtshalber ein Stichpunkt. Formel Zeichen wandelt eine Zahl in einen Buchstaben um. 65 = A, 66 = B ...

So, damit habe ich alle Informationen zusammen. B9 ist der Beweis. Wenn Ihr jetzt eine Spalte anhängt und / oder eine Zeile, B9 wird es merken ...

Ein praktisches Beispiel, um den (kleinen?) Nutzen zu veranschaulichen.

bee58cb9f2a51441caa42a815f742db3 - Klicke auf das Bild, um es zu vergrößern
Den Gültigkeitsbereich von B14 habe ich auf die erste Spalte meiner Tabelle beschränkt. Sobald Zeilen hinzukommen oder entfernt werden, die Auswahlliste ist sofort aktuell.
Das es funktioniert, könnt ihr sehen, wenn ihr in B15 diese Formel einfügt: SVERWEIS(B14;INDIREKT(B9);2;FALSCH).

Ich hoffe, dem ein oder anderen habe ich mit dieser Anleitung auf neue Ideen bringen können.

Markus
Ähnliche Inhalte
Microsoft Office
gelöst Excel-Formel oder VBA (7)

Frage von nicki01 zum Thema Microsoft Office ...

Microsoft Office
Rechnungen in Word - zu lange Formel (2)

Frage von traller zum Thema Microsoft Office ...

Microsoft Office
Exel Formel einfügen (5)

Frage von lizzy123 zum Thema Microsoft Office ...

Microsoft Office
Excel verliert die Formel, behält aber den Wert (10)

Frage von derausgroenloh zum Thema Microsoft Office ...

Neue Wissensbeiträge
Windows Server

Umstellung SHA 1 auf SHA 2 - Migration der CA von CSP auf KSP

Tipp von Badger zum Thema Windows Server ...

Windows 10

Quato DTP94 unter Windows 10 x64 installieren und verwenden

Anleitung von anteNope zum Thema Windows 10 ...

Windows 10

Win10 1703 und Nutzerkennwörter bei Ersteinrichtung - erstaunliche Erkenntnis

(15)

Erfahrungsbericht von DerWoWusste zum Thema Windows 10 ...

Heiß diskutierte Inhalte
LAN, WAN, Wireless
CNC Maschinen verlieren Netzwerkverbindung (kurioser Fehler) (22)

Frage von NoHopeNoFear zum Thema LAN, WAN, Wireless ...

Webentwicklung
Aktuellen Mitarbeiter auf Homepage anzeigen (13)

Frage von alemanne21 zum Thema Webentwicklung ...

Windows Server
gelöst Parameter Übergabe Terminal Server (9)

Frage von ThomasKern zum Thema Windows Server ...

Batch & Shell
Anfängerfragen - Powershell - Mailboxvertretung im Pulk einrichten (8)

Frage von Yoshimitsu zum Thema Batch & Shell ...