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, 9605 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
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ä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 ...

Netzwerke
HTTP Overhead Berechnen und Protokolstack

Frage von karlosss zum Thema Netzwerke ...

Festplatten, SSD, Raid
Speicher der nächsten Jahre berechnen (10)

Frage von Ravers zum Thema Festplatten, SSD, Raid ...

Heiß diskutierte Inhalte
LAN, WAN, Wireless
gelöst Server erkennt Client nicht wenn er ausserhalb des DHCP Pools liegt (28)

Frage von Mar-west zum Thema LAN, WAN, Wireless ...

Outlook & Mail
gelöst Outlook 2010 findet ost datei nicht (19)

Frage von Floh21 zum Thema Outlook & Mail ...

Microsoft
Ordner mit LW-Buchstaben versehen und benennen (19)

Frage von Xaero1982 zum Thema Microsoft ...

Netzwerkmanagement
gelöst Anregungen, kleiner Betrieb, IT-Umgebung (18)

Frage von Unwichtig zum Thema Netzwerkmanagement ...