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

Zellen vergleichen mit Index....ich kriegs nicht hin.....

Frage Microsoft Microsoft Office

Mitglied: OleSchulze

OleSchulze (Level 1) - Jetzt verbinden

09.06.2009, aktualisiert 21:56 Uhr, 9274 Aufrufe, 14 Kommentare

Hallo,

ich sitz schon seit Stunden und bekomms nicht hin..... Liegt wohl auch daran, dass ich nicht gerade ein Excel Freak bin...... Habs schon mit einer Index Variante probiert, habe aber immer Fehlermeldungen bekommen.

Also zum Problem:

Ich habe eine Zelle mit einem bestimmten Wert. Dieser soll mit verschiedenen Werten einer Spalte A verglichen werden. Neben den Werten aus Spalte A stehen ebenfalls Werte aus Spalte B. Gibt es jetzt eine Übereinstimmung zwischen einem Wert aus Spalte A und meinem Bezugswert aus der einzelnen Zelle, soll der entsprechende Wert aus Spalte B als Ergebnis präsentiert werden.Übersteigt der Bezugswert alle vorhanden Werte aus Spalte A, so soll der letzte Wert aus Spalte B präsentiert werden.

War das verständlich??

Ich hoffe

Danke für eure Hilfe...

232415556d72fcfb3a364eff56d5100a-zelle - Klicke auf das Bild, um es zu vergrößern
Mitglied: 76109
09.06.2009 um 20:56 Uhr
Hallo OleSchulze!

Das sollte gehen: "=SVERWEIS(C1;A:B;2;WAHR)"

D1 = Formel
C1 = Eingabewert (> Suchwert A = Letzter Wert B)

A = Suchwert
B = Rückgabewert

Alternativ in der Art:

=WENN(ISTLEER(C1);"Bitte einen Wert in C1 Eingeben";SVERWEIS(C1;A:B;2;WAHR))

Gruß Dieter
Bitte warten ..
Mitglied: Pedant
09.06.2009 um 21:33 Uhr
Hallo OleSchulze,

ob's verständlich war?
Ich bin mir nicht sicher, aber so habe ich die Aufgabe verstanden und mit Excel 2007 umgesetzt:

A B
1 Eingabe: 20
2 Ausgabe: b
3
4 ZahlText
5 10 a
6 20 b
7 30 c
8 40 d
9 50 e

Formel für Zelle B2:
=WENN(B1>MAX(A6:A10);B10;INDEX(B6:B10;VERGLEICH(B1;A6:A10;1)))

Was Du nicht bedacht hast:
Was soll denn passieren wenn die Eingabe zwar nicht größer als die Zahlen im Bereich "Zahl" (A6:A10) ist, aber wenn keine Übereinstimmung gefunden wird, beispielsweite die 15 als Eingabe?

Gruß Frank
Bitte warten ..
Mitglied: Biber
09.06.2009 um 21:46 Uhr
Moin Pedant,

Was soll denn passieren wenn die Eingabe zwar nicht größer als die Zahlen im Bereich "Zahl" (A6:A10) ist, aber wenn keine Übereinstimmung gefunden wird, beispielsweite die 15 als Eingabe?
das hast Du schon als Regel vorgegeben durch den vierten dritten Parameter der Funktion VERGLEICH().

Dieser Parameter ("Vergleichstyp") bewirkt, dass
  • wenn =0 -> VERGLEICH den ersten Wert zurückgibt , der gleich Suchkriterium ist. Die Elemente der Suchmatrix dürfen in beliebiger Reihenfolge angeordnet sein.
  • wenn -1, gibt VERGLEICH den kleinsten Wert zurück, der größer gleich Suchkriterium ist
  • wenn =1 (wie bei Dir) dann der erste Größer-oder-Gleichwert bei einer aufsteigenden Liste

Wenn es nicht auf eine dieser Arten gefunden werden kann, knallt es halt ("#NV").

Grüße
Biber
Bitte warten ..
Mitglied: OleSchulze
09.06.2009 um 21:59 Uhr
Danke für die schnellen Antworten.

Hab mal nen Screenshot gemacht. Rot ist mein Bezugswert. Grün ist die Spalte mit den Vergleichswerten und in das grüne Feld oben rechts soll der Parallellwerte aus Spalte B eingetragen werde.
Habs damit versucht: =INDEX(A40:A52;KKLEINSTE(WENN(B40:B52=C40:C52;ZEILE(40:52));1))
Bitte warten ..
Mitglied: Pedant
09.06.2009 um 22:18 Uhr
Hallo Biber,

danke für die Erklärung zum Parameter ("Vergleichstyp").
Ich hatte zwar auf die Schnelle einen ausgewählt, aber die Alternativen und die Auswirkungen nicht verstanden.

Meine erste Formel ist übrigens im Bereich etwas verrutscht.
Statt der Zeilen 6 bis 10 müssen es die Zeilen 5 bis 9 sein und statt der Zelle B10 die Zelle B9.

Korrigierte Formel für Zelle B2:
=WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;1)))

Jetzt könnte man nach einen exakte Übereinstimmung suchen (Parameter 0 statt 1):
=WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;0)))

und eventuelle Fehler abfangen:
=WENNFEHLER(WENN(B1>MAX(A5:A9);B9;INDEX(B5:B9;VERGLEICH(B1;A5:A9;0)));"keine Übereinstimmung")

Also wenn es keine exakte Übereinstimmung gibt,
dann kommt statt des Excel-Fehlers "#NV" der Text "keine Übereinstimmung".

Gruß Frank
Bitte warten ..
Mitglied: Biber
09.06.2009 um 22:44 Uhr
Moin Pedant,

ebenfalls danke.
ich musste auch bei mir korrigieren: Vergleichstyp ist der dritte und nicht der vierte Parameter.

Und noch eine Nachfrage.
Ich gehöre eher zu den nicht so Technikbesessenen und habe noch kein Excel 2007.
Da scheint eine neue Funktion WENNFEHLER() hinzugekommen zu sein?

Denn mit meinem Excel 2002/2003 muss ich noch mit Wenn() +IstFehler() etwas unleserlicher schachteln.

Grüße
Biber
Bitte warten ..
Mitglied: Pedant
09.06.2009 um 23:07 Uhr
Hallo OleSchulze,

Habs damit versucht:
=INDEX(A40:A52;KKLEINSTE(WENN(B40:B52=C40:C52;ZEILE(40:52));1))

das hat ja offensichtlich nicht funktioniert.
Bereiche vergleichen mit WENN(B40:B52=C40:C52;dann,sonst) funktioniert nicht.

Hast Du den Ansatz von didi1954 oder meine Formel mal ausprobiert?

@Biber,

ja, ich glaube die Funktion "WENNFEHLER()" ist neu.
Es kamen übrigens einige neue Funktionen hinzu und durchaus hilfreiche.

Gruß Frank
Bitte warten ..
Mitglied: Pedant
10.06.2009 um 08:30 Uhr
Hallo OleSchulze,

ich habe die Formel jetzt an die Tabelle aus Deinem Screenshot angepasst:

=WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0)))

und mit Fehlerbehandlung für Excel 2003 sieht sie so aus:

=WENN(ISTFEHLER(WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0))));"keine Übereinstimmung";WENN(F36>MAX(A40:A52);B52;INDEX(B40:B52;VERGLEICH(F36;A40:A52;0))))

Die Formel ist in F38 einzugeben.
Warum Du zwei rote Zellen (F36 und C40) hast ist mir allerdings nicht klar.
Ich habe F38 genommen und C40 komplett ignoriert.

Meine Formen nimmt den Wert aus F36, sucht ihn im Bereich A40:A52.
Wird eine exakte Übereinstimmung gefunden, dann wird der Wert angezeigt, der rechts daneben in Spalte B steht.
Ist der Vorgabewert aus F36 größer als alle Werte im Bereich A40:A52, dann wird der Wert aus B52 genommen, der der letzte Wert aus Spalte B ist.
Ist der Vorgabewert nicht größer und wird keine exakte Übereinstimmung gefunden, dann gibt es einen Fehler.

Dieser Fehler kann mit der Fehlerbehandlung abgefangen und durch einen Ausgabewert ersetzt werden.
In meinem Beispiel kommt dann der feste Text "keine Übereinstimmung", der allerdings auch durch anderen Text oder eine weitere Formel ersetzt werden kann.

Hat's was genutzt?

Gruß Frank
Bitte warten ..
Mitglied: OleSchulze
10.06.2009 um 08:56 Uhr
@Frank
Danke, bin gerade auf der Arbeit und kann es erst später eingeben.

Du hast recht, zweite rote Zelle ist nur eine Kopie und somit unwichtig.
Habe gestern doch noch etwas gefunden. Super kurz und funktioniert rechts gut gibt nur ein kleines Problem mit Rundungsfehlern. Weiß die Formel jetzt nicht mehr auswendig, gebe sienachher mal durch.


danke erst mal

greetz

OS
Bitte warten ..
Mitglied: OleSchulze
12.06.2009 um 20:17 Uhr
Hallo Frank,

das wäre ja genau das was ich gesucht habe.!!!!
Hab´s eingefügt bekomme aber immer= Keine Übereinstimmung) als Fehlermeldung
Habe Office 2003. Woran kann es liegen??? Zellpositionen stimmen überein. Das Ergebnis in F36 habe ich ohne Dezimalstellen berechnen lassen. Der Wert der herauskommt ist definitiv in Spalte A40/52 enthalten .....


thx
OS
Bitte warten ..
Mitglied: Pedant
12.06.2009 um 21:01 Uhr
Hallo OleSchulze,

Das Ergebnis in F36 habe ich ohne Dezimalstellen berechnen lassen.
Der Wert der herauskommt ist definitiv in Spalte A40/52

um systematisch an die Sache ranzugehen, solltest Du Dir zunächst Deine Formel aus F36 sichern und statt der Formel in F36 feste Zahlen eingeben, das um sicherzustellen, ob die Formel aus F38 funktioniert, also Übereinstimmungen findet.

Wenn Du soweit bist, dann kannst Du Deine Formel wieder in F36 einsetzen.

Es macht einen Unterschied, ob keine Dezimalstellen zu sehen sind oder ob wirklich keine da sind.

Um sicherzugehen, dass Du tatsächlich ohne Dezimalstellen berechnen lässt, kannst Du das Ergebnis der Formel noch auf 0 Stellen runden lassen.
=RUNDEN(Deine Formel,;0)

Da die Vergleichswerte im Bereich A40:A52 aber bis zu einer Nachkommastelle haben, halte ich eine Berechnung ganz ohne Nachkommastellen für unzweckmäßig.
Ein Runden auf 1 Nachkommastelle erscheint mir hier angemessener.
=RUNDEN(Deine Formel,;1)

Damit das klar ist:
Hier werden Zahlen verglichen und keine Zeichenfolgen (Text), deswegen gilt:
2,0 = 2 = 2,0000
und 2,0 <> 2,00001
egal wieviele Stellen in den Tabellenzellen dargestellt werden.

Gruß Frank
Bitte warten ..
Mitglied: OleSchulze
14.06.2009 um 21:24 Uhr
hallo,

Hallo Frank,

schau mal hier: www.bikeboersepeine.de/test.xls

Kaum ein Problem gelöst, ist das nächste da.

Die Formel hat irgendwie ein Problem mit meinen Werten aus F34- F39.

Werte in F34 sollen in F35 nur berechnet werden (und zwar als positive Zahl) wenn sie negativ sind dh. wenn sie die Bezugssumme von 150 unterschreiten. Des Weiteren dient F34 als Bezugswert zu F37. Diese beiden ergeben dann F39 als Prozentwert. Welcher dann in deine Formel einfließt......

Puh.......

Hoffe du kannst mir helfen...

thx
Bitte warten ..
Mitglied: OleSchulze
14.06.2009 um 23:21 Uhr
Hab´s gelöst, mit =VERWEIS(F39;A39:A52;B39:B52).
funktioniert einwandfrei mit größeren und mit kleineren Zahlen als in A39-A52.


Danke noch mal.....


an alle


thx + greetz
OS
Bitte warten ..
Mitglied: Biber
15.06.2009 um 08:48 Uhr
Moin OleSchulze,

auch wenn es mittlerweile vielleicht obsolet ist....

Deine per Link bereitgestellte Excel-Tabelle hat in der Formel in Zelle F9 (von wo an die ganze Folgeberechnung in die Grütze geht) in der dort 2x enthaltenen Funktion VERGLEICH als dritten Parameter "Vergleichstyp" den Wert 0 (exakter Vergleich) statt 1 (nimm das erste kleiner/gleich Suchkriterium-Feld einer sortierten Liste). Ersetze diese beiden Parameter durch 1 wie oben beschrieben, dann klappt doch alles.

Was sich in Deiner test.xls nicht flicken lässt ist die marode Nebenrechnungsformel Formel in F15.
Da steht "=RUNDEN(#BEZUG!+F12;2)" ... tja. Kann Biber erraten nix.

Dieser Kommentar hat keinerlei Bezug zu Deinen Sätzen "Werte in F34 sollen in F35 nur berechnet werden.." ff.
Da weiß ich im Moment gar nicht, in welchem Tabellenblatt Du gedanklich bist.

Grüße
Biber
Bitte warten ..
Neuester Wissensbeitrag
Heiß diskutierte Inhalte
Windows Userverwaltung
Ausgeschiedene Mitarbeiter im Unternehmen - was tun mit den AD Konten? (27)

Frage von patz223 zum Thema Windows Userverwaltung ...

Viren und Trojaner
Aufgepasst: Neue Ransomware Goldeneye verbreitet sich rasant (20)

Link von Penny.Cilin zum Thema Viren und Trojaner ...

LAN, WAN, Wireless
FritzBox, zwei Server, verschiedene Netze (20)

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

Windows Netzwerk
Windows 10 RDP geht nicht (18)

Frage von Fiasko zum Thema Windows Netzwerk ...