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, 9720 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
Batch & Shell
gelöst Zeiten in .txt datei auslesen und Differenz berechnen (14)

Frage von meex87 zum Thema Batch & Shell ...

Microsoft Office
Formel-Funktion in Word produziert unsaubere Resultate bei Indizes (1)

Frage von Yauhun zum Thema Microsoft Office ...

Datenbanken
Access 2010 Entwicklung berechnen (1)

Frage von Dr.Cornwallis zum Thema Datenbanken ...

Microsoft Office
Exel Formel einfügen (5)

Frage von lizzy123 zum Thema Microsoft Office ...

Neue Wissensbeiträge
RedHat, CentOS, Fedora

Fedora, RedHat, Centos: DNS-Search Domain setzen

(13)

Tipp von Frank zum Thema RedHat, CentOS, Fedora ...

Drucker und Scanner

Samsung SL-M4025ND, firmware update und (kompatible) Tonerkassetten

(1)

Erfahrungsbericht von markus-1969 zum Thema Drucker und Scanner ...

Heiß diskutierte Inhalte
Windows 10
Windows für Privatanwender "nicht mehr handhabbar" (34)

Frage von FA-jka zum Thema Windows 10 ...

LAN, WAN, Wireless
Komplett neues Netzwerk, Ubiquiti WLAN, Router, Switch (15)

Frage von Freak-On-Silicon zum Thema LAN, WAN, Wireless ...

RedHat, CentOS, Fedora
Fedora, RedHat, Centos: DNS-Search Domain setzen (13)

Tipp von Frank zum Thema RedHat, CentOS, Fedora ...

Backup
Backup Wochen- Monats- Jahressicherung (13)

Frage von Meterpeter zum Thema Backup ...