Numbers Formel erstellen Verweis auf Tabelle

Question

Numbers Formel erstellen Verweis auf Tabelle

Ich habe folgendes Anliegen, welches ich nicht in einer Numbers Tabelle gelöst bekomme:

Tabelle 1 enthält folgende Infos

1. Mein Zeichen

2. Name Kunde

3. vereinbartes Honorar

4. offenes Honorar (soll mit Formel automatisch ausgerechnet werden - diese bekomme ich aber nicht hin)

Tabelle 2 hat:

1. Rechnungsnummer

2. Mein Zeichen

3. Rechnungssumme

4. Zahlungseingang

5. noch offener Rechnungsbetrag (wird durch Formel ausgerechnet: Rechnungssumme minus Zahlungseingang)

Wie soll es nun gehen, dass Tabelle 1 bei "offenes Honorar" erkennt, dass unter demselben Zeichen ("Mein Zeichen") eine oder häufig auch mehrere Rechnungen in Tabelle 2 hinterlegt sind und inwiefern diese schon bezahlt sind, um dann die offenen oder auch bereits beglichenen Rechnungssummen von dem vereinbarten Honorar (Tabelle 1) abzuziehen?

Formel müsste ja sein: vereinbartes Honorar (3. aus Tabelle 1) minus noch offener Rechnungsbetrag (5. aus Tabelle 2, allerdings immer wieder unterschiedliche Zeilen, da teilweise mehrere Rechnungen) aller Rechnungen zu meinem Zeichen (unbestimmte Zeilen aus Tabelle 2, nur bestimmbar über "mein Zeichen").

Danke

MacBook

Gepostet am 23. Juli 2019 09:09

Antworten

Answer 1

Höchstrangige Antwort

RichardB.

Stufe 7

27.694 Punkte

23. Juli 2019 11:53 als Antwort auf SaPaKo

Andersrum sieht es so aus:

Hier sieht die Formel in der ersten Zeile mit Werten wie folgt aus:

C2−SUMMEWENN(Tabelle 2::$B$2:$B$11;A2;Tabelle 2::$E$2:$E$11)

SUMMEWENN addiert alle Werte, die einer Bedingung erfüllen. Die Formel lautet

SUMMEWENN(Bereich mit den Werten die geprüft werden sollen;Bedienung,Bereich der aufsummiert werden soll)

Die beiden Bereiche müssen gleich groß sein.

In "Tabelle 2::$B$2:$B$11" stehen die Werte von "Mein Zeichen" in Tabelle 2. Wenn diese gleich A2 ("Mein Zeichen" für die erste Zeile in Tabelle 1), wird der entsprechende Wert in "Tabelle 2::$E$2:$E$11" aufaddiert.

Für die erste Zeile sieht es wie folgt aus:

A2 = AB1 => Wir gehen in Tabelle 2 und prüfen, wo in der Spalte B "AB1" steht (Rechnungsnummer 1, 2 und 9). Wenn "AB1" gefunden wurde, wird der Wert aus Spalte E genommen und aufaddiert

Rechnungsnummer 1 hat Wert 78,65 €

Rechnungsnummer 2 hat Wert 0,54 €

Rechnungsnummer 9 hat Wert 300,00 €

Die Summe ist 78,65 € + 0,64 € + 300,00 € = 379,19 €

Dieser Wert wird von C2 (aus Tabelle 1) = 2000,87 € subtrahiert:

2000,87 € - 379,19 € = 1621,68 €

Antworten

Answer 2

24. Juli 2019 00:28 als Antwort auf SaPaKo

"Test Werte" sind die Werte die auf Übereinstimmung geprüft werden sollen. In Deinem Fall ist es die Spalte mit "Mein Zeichen" aus Tabelle 2.

"Bedingung" ist der Wert auf den die "Test Werte" geprüft werden sollen. In Deinem Fall ist es (für die erste Zeile mit Werten in Tabelle 1) "Mein Zeichen" aus Tabelle 1. Jedoch noch die ganze Spalte, sondern nur der Wert für die erste Zeile mit Werten.

"Summe Werte" sind die Werte, die aufaddiert werden sollen, wenn die Bedingung übereinstimmt. In Deinem Fall ist es die Spalte mit "noch offener Rechnungsbetrag" aus Tabelle 2.

Antworten

Answer 3

23. Juli 2019 11:30 als Antwort auf RichardB.

Danke! Ich schaue es mir mal morgen in Ruhe an sobald ich wieder and Rechner bin und versuche es zu übertragen.

Meine Tabelle ist nämlich genau andersrum. Die Überschriften die ich genannte habe sind die Zeilenüberschriften.

Antworten

Answer 4

23. Juli 2019 11:34 als Antwort auf SaPaKo

Ich meinte selbstverständlich die Überschriften sind bei mir Spaltenüberschriften. Sprich alle Informationen eines Mandanten sind in einer Zeile.

Vielleicht kannst Du mir dazu nochmal erklären wie sich diese Formel zusammensetzt? Ich habe nämlich noch zahlreiche andere Spalten in meiner Tabelle, habe diese nur hier der Einfachheit nicht dargelegt. Danke und beste Grüße Sandra

Antworten

Answer 5

24. Juli 2019 00:15 als Antwort auf RichardB.

Lieber Richard, entschuldige die vielen Nachfragen, aber ich bin einfach zu dusselig, um es zu verstehen. Ich klicke in der Spalte "offenes Honorar" auf "Formel einfügen", dann "SUMMEWENN" dort gibt es "Test Werte" - ich klicke auf gesamte Spalte mit Zeichen aus Tabelle 1, "Bedingung" - hier klicke ich auf gesamte Spalte mit Zeichen aus Tabelle 2 und dann "Summe Werte" - hier klicke ich auf was? Ich habe verschiedene Varianten versucht, leider verstehe ich die Formel einfach nicht. Kannst Du mir das vielleicht nochmal für Dummies erklären? Sorry! Ich bin mit Formeln und Numbers einfach offensichtlich nicht gut.

Antworten

Answer 6

24. Juli 2019 00:42 als Antwort auf RichardB.

Es funktioniert nicht. Irgendwas mache ich wohl grundlegend falsch. Ich habe mal meine Tabelle - die grundsätzlich deutlich mehr Zeilen hat (ca. 500) vereinfacht und anonymisiert.

Antworten

Answer 7

24. Juli 2019 00:55 als Antwort auf SaPaKo

Hier nochmal meine Formel

Antworten

Answer 8

23. Juli 2019 11:24 als Antwort auf SaPaKo

Ich habe das ganze mal nachgebaut:

Die Formel in Tabelle1 für offenes Honorar (erste Spalte mit Werten) lautet:

B4−SUMMEWENN(Tabelle 2::$B$3:$E$3;B2;Tabelle 2::$B$6:$E$6)

Die Formel kann dann rübenkopiert werden.

Antworten

Answer 9

24. Juli 2019 01:15 als Antwort auf SaPaKo

Ich habe es geschafft. Ich durfte nicht den offenen Betrag nehmen, sondern habe eine neue Spalte mit insgesamt bezahlt eingefügt. Nur so kann ich erreichen, dass ich erkenne ob ich noch eine Rechnung stellen muss, um das Honorar voll auszuschöpfen. Das so durch die Formel ausgerechnete bezahlte Honorar vergleiche ich dann in Tabelle 1 mit dem vereinbarten Honorar. Jetzt muss ich mir nur noch eine Formel zur farblichen Hinterlegung suchen. Sprich rot, wenn vereinbartes Honorar > als gezahltes Honorar und grün, wenn vereinbartes Honorar = gezahltes Honorar ist

Antworten

Answer 10

24. Juli 2019 05:58 als Antwort auf SaPaKo

Dazu markiere einfach die Spalte und lasse Dir in Number das "Format" im rechten Bereich anzeigen.

Nun gehst Du ganz unten auf "Bedingte Markierung"

In dem sich dann öffnenden Menü gehst Du auf "Regel hinzufügen", wählst "Zahlen" aus und dann "kleiner als". Hier gibst Du eine 0 ein und änderst die Formatierung auf "Roter Text". Nun nur noch auf "Fertig" klicken.

Das Ergebnis sieht dann so aus:

Antworten