EXCEL - Verschachtelte Bereiche für Referenzen

549
YAtreyu

Danke, dass Sie meine Frage gelesen haben.

Ich versuche, Preispunkte für verschiedene Größen von Produktpaketen zu finden. Ich habe einen Datenbereich auf Sheet1, A1: Z102, mit der oberen Reihe als Masse der Pakete (B1: Z1) und der ersten Spalte (A2: A102) als Verkaufspreis. Im Array habe ich die Gewinnspanne berechnet (B2: Z102)

Ich versuche, "Was-wäre-wenn" automatisch auf einem Blatt einzurichten, auf dem ich verschiedene Gewichte eingeben kann "Blatt2! B1: B10" - die entsprechende Spalte mit den Paketgewichten wird aus "Blatt1! A2: Z2" ausgewählt. Dann haben Sie die Funktion MATCH die nächste Zeile dieser Spalte, die meiner "Was-wäre-wenn" Gewinnspanne "Sheet2! A1" am nächsten kommt, und schließlich auf Sheet2 C1: C10 den Verkaufspreis zurückgeben, der dieser Gewinnspanne am nächsten kommt.

SHEET1 1lbs 2lbs 3lbs 4lbs $1 $0.10 -$0.80 -$1.80% -$2.80 $2 $1.74 $0.74 -$0.26 -$1.26 $3 $2.61 $1.61 $0.61 -$0.39 $4 ... ... ... $0.47 ...  SHEET2 $0.20 2lbs X 4lbs X

[Edit1] Die Funktion würde also C1 = $ 2 und C2 = $ 4 in drei iterativen Schritten zurückgeben (denke ich).

  1. Cell.Sheet2! B1.Value stimmt mit identischer Spalte in Tabelle1 überein Row1: Gibt Spalte Y zurück
  2. Suchen Sie für Spalte Y in Tabelle1 den Wert, der dem positiven Wert von Cell.Sheet2A1 am nächsten liegt: Gibt Spalte Y, Zeile Z zurück
  3. Für Tabelle1 Spalte Y gibt der Wert der Zeile Z in Tabelle1 Spalte 1 Zeile Z den Wert zurück. Wiederholen Sie diesen Wert in C1 Tabelle2
  4. Wiederholen Sie 1-3 für B2 ... Bn

Es fällt mir schwer, jeden Schritt zu lösen:

  1. MATCH kann die richtige Spalte identifizieren
  2. Ich kann nicht herausfinden, wie man MATCH in "OFFSET" verschachtelt, um nur Spalte Y zu suchen. Ich habe INDEX MATCH, INDEX MATCH MATCH und LOOKUPs (V & H) ausprobiert.
  3. Gibt den Wert von Spalte1 in RowZ zurück.

Mir ist klar, dass dies eine Makro-Lösung sein kann, aber ich weiß nicht, wie man VBA darauf bringt:

Dim profit, lbs, cost, reflbs, refprofit As Range  Set cost = Worksheets("Sheet1").Range("$A$2", "$A$5") Set profit = Worksheets("Sheet1").Range("$B$2", "$E$5") Set lbs = Worksheets("Sheet1").Range("$B$1", "$E$1") Set reflbs = Worksheets("Sheet2").Range("$B1") Set refprofit = Worksheets("zTest").Range("$H$39")  For Each profitCell In profit For Each costCell In cost For Each lbsCell In lbs If lbsCell.Value = reflbsCell.Value Then

Dann bin ich mir nicht sicher, wie ich eine Suche in der Spalte lenken soll, deren Kopfzeile lbs mit "reflbs" übereinstimmt, und dann würde ich Folgendes eingeben:

If profitCell.Value = refprofitCell.Value Then

Dann geben Sie die Zeile Z für das Spiel zurück und anschließend die Zeile Z, Spalte A, Blatt1! in Blatt2! C1.

Vielen Dank!

0
Sie scheinen eine gute Vorstellung davon zu haben, was zu tun ist. Können Sie uns mitteilen, welche Formel Sie bisher ausprobiert haben und wie sie das erwartete Ergebnis nicht erreicht? cybernetic.nomad vor 5 Jahren 0
Danke, dass Sie auch zu mir zurückgekehrt sind. Ich habe eine experimentelle Tabelle mit H38 als gewünschtem Sackgewicht (6 lbs, benutzerdefinierte formatierte Zahl, so dass die Zelle nur 6 hat, dh "$" 0,00 bei Dollars oder 0,0 "lbs" bei Gewicht). Zuerst habe ich eine Funktion, um die relative Spaltennummer der übereinstimmenden Gewichtung zu ermitteln: = MATCH (H38, G40: M40), die "7" zurückgibt. Ich habe LOOKUP (und H & V LOOKUP) ausprobiert, da es eine aufsteigende Liste benötigt. Ich habe auch INDEX MATCH und INDEX MATCH MATCH ausprobiert. Mein letzter Versuch war OFFSET, von "A1" und von einer ADRESSE, wobei die versetzten Spalten auf "7" gesetzt wurden, bevor -1 gefunden wurde, um zur Spalte "A" zu gelangen. YAtreyu vor 5 Jahren 0

1 Antwort auf die Frage

0
Forward Ed

Angenommen, Ihre Quelldaten sind wie folgt aufgebaut:

Dann auf Blatt2, wenn Ihre Daten wie folgt angelegt sind:

Dann können Sie die folgende Formel verwenden, um die Zahlen rot zu erhalten:

=INDEX(Sheet1!$B$2:$E$5,IFERROR(MATCH(Sheet2!$A$1,Sheet1!$A$2:$A$5,1),1),MATCH(Sheet2!$B1,Sheet1!$B$1:$E$1,0))

Wenn der von Ihnen gesuchte Dollarwert geringer ist als der erste Wert in Ihrer Spalte, haben Sie wahrscheinlich einen Fehler erhalten. Die Arbeitsweise von vlookup und match besteht darin, dass sie nicht nach dem nächstgelegenen Zahlenwert suchen. Sie suchen nach der letzten Zahl, die weniger war als die, nach der sie gesucht haben. Aus diesem Grund muss Ihre Spalte aufsteigend sortiert sein. Um mit einem legitimen Wert umzugehen, der niedriger als der erste Wert in der Liste ist, wird die Übereinstimmungsfunktion, die den Fehler auslöst, in eine iferror-Funktion eingeschlossen, die 1 zurückgibt, um die erste Zeile darzustellen. Wenn ein ungültiger Preiseintrag in Sheet2! A1 eingegeben wird, wird für die erste Zeile immer noch 1 zurückgegeben.

Für Ihre Gewichte sind dies höchstwahrscheinlich String-Werte und keine Zahlen. Es ist üblich, dass Ihre Einheiten in einer separaten Zelle aufgelistet werden und dann nur der Wert in einer eigenen Zelle, um das Arbeiten mit den Werten zu erleichtern. In diesem Fall ging ich davon aus, dass Ihr Gewicht immer genau mit einer Ihrer Spaltenüberschriften übereinstimmt. Ein Gewicht von 2,5 Pfund würde einen Fehler auslösen, da er nicht in Ihrer Header-Liste gefunden wird.

Hey, danke, dass du wieder bei mir bist. Ich habe versucht, Ihre Formel zu verwenden, und sie funktioniert genau so, wie Sie es vorgeschlagen haben. Ich brauche es, um einen weiteren Schritt auszuführen, nämlich wenn C2 die Formel in Sheet1 "findet"! um den Wert in Sheet1 zu melden! Spalte A für die gleiche Zeile, dh Bericht des idealen Paketpreises vs. Gewinn dieses Paketpreises. YAtreyu vor 5 Jahren 0
Statt $ 0,80 soll es also $ 1 sein? Forward Ed vor 5 Jahren 0
Jep! Ich schaute nach den Pfund, die ich für die Gewichtung mit der Spalte abgeglichen habe, dann schaue ich in dieser Spalte den von mir eingestellten Gewinn an, dann die Zeile, in der sich der Gewinn befindet, und den Preis zurückgeben, um das Produkt festzulegen. YAtreyu vor 5 Jahren 0
$ 0.80 ist also $ 1 von $ 0.20 entfernt, und $ 0.74 ist nur $ .54 entfernt. Was ist der erwartete Wert für X bei 2 lbs und 4 lbs, den Sie sehen möchten? Ich versuche immer noch, meinen Kopf um die Reihe zu legen, in der Sie versuchen zu wählen und warum. Forward Ed vor 5 Jahren 0

Verwandte Probleme