Top-Themen

Aktuelle Themen (A bis Z)

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, 9798 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 ..
Ähnliche Inhalte
VB for Applications
Zelle vergleichen
gelöst Frage von Gimli3311VB for Applications4 Kommentare

Hallo Zusammen, Hallo jodel32, Ich habe 2 Excel-Dateien mit Tabellen nun haben die Tabellen verschiedene Vorlagen. Ich hab also ...

VB for Applications
Vergleich von 2 Zellen scheitert
gelöst Frage von GundelputzVB for Applications2 Kommentare

Hallo, wie die Überschrift schon sagt habe ich ein Problem zwei Zellen zu vergleichen. Mein Code sieht so aus: ...

VB for Applications
Zelle Suchen und Vergleichen
gelöst Frage von Gimli3311VB for Applications5 Kommentare

Guten Morgen Zusammen, ich bin es mal wieder und komm seid gestern Mittag mal wieder nicht voran. Was hab ...

Basic
Zellen vergleichen und Inhalt aus dritte Zelle einfügen
gelöst Frage von Rabbit81Basic3 Kommentare

Hallo, ich habe schon sehr gute Erfahrungen in diesem Forum gemacht und deswegen wende ich mich wieder an euch. ...

Neue Wissensbeiträge
Batch & Shell

Open Object Rexx: Eine mittlerweile fast vergessene Skriptsprache aus dem Mainframebereich

Information von Penny.Cilin vor 21 StundenBatch & Shell9 Kommentare

Ich kann mich noch sehr gut an diese Skriptsprache erinnern und nutze diese auch heute ab und an noch. ...

Humor (lol)

"gimme gimme gimme": Automatischer Test stolpert über Easter Egg im man-Tool

Information von Penny.Cilin vor 23 StundenHumor (lol)6 Kommentare

Interessant, was man so alles als Easter Egg implementiert. Ist schon wieder Ostern? "gimme gimme gimme": Automatischer Test stolpert ...

MikroTik RouterOS

Mikrotik - Lets Encrypt Zertifikate mit MetaROUTER Instanz auf dem Router erzeugen

Anleitung von colinardo vor 1 TagMikroTik RouterOS8 Kommentare

Einleitung Folgende Anleitung ist aus der Lage heraus entstanden das ein Kunde auf seinem Mikrotik sein Hotspot Captive Portal ...

Sicherheit

Sicherheitslücke in HP-Druckern - Firmware-Updates stehen bereit

Information von BassFishFox vor 1 TagSicherheit1 Kommentar

Ein weiterer Grund, dass Drucker keinerlei Verbindung nach "auswaerts" haben sollen. Unter Verwendung spezieller Malware können Angreifer aus der ...

Heiß diskutierte Inhalte
Windows 10
Bitlocker nach Verschlüsselung nicht mehr aufrufbar!
gelöst Frage von alexlazaWindows 1013 Kommentare

Hallo, ich besitze ein HP ZBook 17 G4 mit einem Windows 10 Pro Betriebssystem. Bei diesem Problem handelt sich, ...

Off Topic
Fachkräftemangel in Deutschland? - Talentschmiede schreibt alle 2 Tage die gleichen Stellen aus
Frage von Penny.CilinOff Topic12 Kommentare

Hallo, haben wir in Deutschland Fachkräftemangel? Die Talentschmiede schreibt gefühlt alle zwei Tage dieselben Stellen aus. Und das schon ...

Windows 10
Windows 10 GPO Powershell Skript Registry bearbeiten
Frage von potshockWindows 1010 Kommentare

Hi Admins, Gegeben ist ein Windows 2012 R2 Server an welchen ich Gpos für Windows 10 Clients bereitstelle. U.a. ...

Microsoft
Erfahrungen mit Webcam over RDP gesucht
Frage von DerWoWussteMicrosoft10 Kommentare

Moin Kollegen. Bekanntlich kann man Webcams nur mit Drittanbietersoftware in RDP reinschleifen. Was nutzt Ihr dazu? Wie stabil funktioniert ...