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

gelöst Funktion WENN mit SUCHE und SVERWEIS kombinieren ?

Mitglied: Shaggy84

Shaggy84 (Level 1) - Jetzt verbinden

16.03.2007, aktualisiert 14:50 Uhr, 9271 Aufrufe, 4 Kommentare

Hallo allesamt,


ich habe ein Problem mit einer SUCHE Funktion. Ich habe einfach erzählt zwei Tabellenblätter.

In Tabellenblatt 1 gibt es eine erste Spalte A mit Namen, z.B. "Oracle-xyz" oder "test-SQL" oder "maxdbirgendwas".

Daneben eine Spalte B in die die Formel hineinsoll und einen Ausgabewert gibt.

In Tabellenblatt 2 gibt es ebenfalls zwei Spalten. In der ersten A steht "Oracle", "Sql", "Maxdb" und in der Spalte B daneben ein Code der passend in die Spalte B vom ersten Tabellenblatt hineinsoll. Falls er den Wert nicht findet soll er einen bestimmten anderen ausgeben z.B. A23.

Er soll also nach einem Teil des Namens suchen und wenn er den findet, den Wert der danebenliegenden Spalte ausgeben.

Ich habe jetzt schon mehrere möglichkeiten mit SVERWEIS, SUCHE, WENN und ODER durchgegangen, kam aber nie zu dem gewünschten Ergebnis. Manche Werte werden immer #WERT. Natürlich habe ich $ Zeichen gesetzt

Gruß, Shaggy
Mitglied: bastla
16.03.2007 um 12:21 Uhr
Hallo Shaggy84!

Versuch es mit folgendem VBA-Code (in ein Modul platzieren):
01.
Function TEILVERWEIS(Suchkriterium As Variant, Matrix As Range, Spaltenindex As Integer) As Variant 
02.
Dim Ergebnis As Variant, Key As Variant 
03.
Dim Z As Integer, S As Integer 
04.
Dim i As Integer, found As Boolean 
05.
If Spaltenindex > Matrix.Columns.Count Then 
06.
    'Spaltennummer außerhalb des übergebenen Bereichs 
07.
    TeilVerweis = 0 / 0 
08.
    Exit Function 
09.
End If 
10.
Application.Volatile 
11.
Ergebnis = "" 
12.
found = False 
13.
Z = Matrix.Row 
14.
S = Matrix.Column 
15.
For i = Z To Z + Matrix.Rows.Count - 1 
16.
    Key = Matrix.Parent.Cells(i, S).Value 
17.
    If LCase(Left(Suchkriterium, Len(Key))) = LCase(Key) Then 
18.
        Ergebnis = Matrix.Parent.Cells(i, S + Spaltenindex - 1).Value 
19.
        found = True 
20.
        Exit For 
21.
    End If 
22.
Next 
23.
If found Then 
24.
    TeilVerweis = Ergebnis 
25.
Else 
26.
    TeilVerweis = 0 / 0 
27.
End If 
28.
End Function
Die Fehlerbehandlung ist noch verbesserungswürdig, aber im Prinzip sollte es so funktionieren:
01.
=TEILVERWEIS(A3;Tabelle2!$A$2:$B$30;2)
Es wird immer nur überprüft, ob ein Eintrag in der ersten Spalte der Matrix dem Anfang des Suchkriteriums entspricht (Groß-/Kleinschreibung wird nicht beachtet).

Wird ein zu hoher Spaltenindex übergeben (also im Beispiel oben etwa 4 bei nur 2 markierten Spalten), liefert die Funktion den Fehler "#WERT".

Der gleiche Fehler wird (anstelle von "#NV") zurückgegeben, wenn das Suchkriterium nicht gefunden wurde.

Grüße
bastla
Bitte warten ..
Mitglied: Shaggy84
16.03.2007 um 13:04 Uhr
Hallo bastla,

bist ja unermüdlich

Also dein Code funktioniert soweit sehr gut, aber löst mein Problem leider noch nicht, da das Namensteilstück ja auch mittendrin oder am Ende stehen kann und nicht nur am Anfang.

Außerdem benötige ich noch einen Wert (aus einer Zelle), der Angezeigt wird, wenn er nichts davon finden kann.

Gruß, Gregor
Bitte warten ..
Mitglied: bastla
16.03.2007 um 13:19 Uhr
Hallo Shaggy84!

Stimmt, Du hattest Teil des Namens geschrieben ...

Na gut, dann so:
01.
Function TeilVerweis(Suchkriterium As Variant, Matrix As Range, Spaltenindex As Integer, Alternative As Range) As Variant 
02.
Dim Ergebnis As Variant, Key As Variant 
03.
Dim Z As Integer, S As Integer 
04.
Dim i As Integer, found As Boolean 
05.
If Spaltenindex > Matrix.Columns.Count Then 
06.
    'Spaltennummer außerhalb des übergebenen Bereichs 
07.
    TeilVerweis = 0 / 0 
08.
    Exit Function 
09.
End If 
10.
Application.Volatile 
11.
Ergebnis = "" 
12.
found = False 
13.
Z = Matrix.Row 
14.
S = Matrix.Column 
15.
For i = Z To Z + Matrix.Rows.Count - 1 
16.
    Key = Matrix.Parent.Cells(i, S).Value 
17.
    If InStr(LCase(Suchkriterium), LCase(Key)) Then 
18.
        Ergebnis = Matrix.Parent.Cells(i, S + Spaltenindex - 1).Value 
19.
        found = True 
20.
        Exit For 
21.
    End If 
22.
Next 
23.
If found Then 
24.
    TeilVerweis = Ergebnis 
25.
Else 
26.
    TeilVerweis = Alternative.Value 
27.
End If 
28.
End Function
Aufruf jetzt:
01.
=TEILVERWEIS(A3;Tabelle2!$A$2:$B$30;2;$A$23)
also zusätzlich mit Angabe der "Alternativzelle".

Grüße
bastla
Bitte warten ..
Mitglied: Shaggy84
16.03.2007 um 14:50 Uhr
Hey bastla,

das funktioniert perfekt. Auf Leute wie dich ist Verlass

Vielen Dank!
Bitte warten ..
Ähnliche Inhalte
Microsoft Office

Excel Sverweis mit Funktion Rechts() kombinieren

gelöst Frage von crinaXMicrosoft Office1 Kommentar

Edit: Hat sich alles erledigt. Guten Tag liebe Community, ich habe ein kleines Problem. Um das ganze mal zu ...

Microsoft Office

Excel - SVERWEIS mit ISTLEER Funktion kombinieren

Frage von Hobi84Microsoft Office10 Kommentare

Hallo, ich habe eine funktionierende SVERWEIS Formel, =SVERWEIS($J$11;TABELLE1!$D$5:$BP$100;SPALTEN(TABELLE1!$D:$AV);FALSCH) welche mir einen Suchwert aus einer Quell-Tabelle wiedergibt. Wenn in dem ...

Microsoft Office

Funktion SVERWEIS mit Excel

Frage von HevidarMicrosoft Office2 Kommentare

Hallo, Ich möchte mit Hilfe der Funktion SVERWEIS die Noten berechnen lassen. Leider klappt es nicht richtig. 1+ 15 1 14 ...

VB for Applications

Sverweis nach ID suchen

gelöst Frage von 123660VB for Applications2 Kommentare

Hallo Leute, Ich hoffe ihr könnt mir weiter helfen Ich habe 2 Tabellen die fast identisch sind, einziges unterschied ...

Neue Wissensbeiträge
Sicherheit

Oracle gibt kritische Updates für diverse Produkte frei (16. Oktober 2018)

Information von kgborn vor 6 StundenSicherheit

Oracle hat zum 16. Oktober 2018 eine ganze Reihe an kritischen Updates für seine Produkte freigegeben. Eine Kurzübersicht mit ...

Windows 10
FYI: Fristen beim Windows 10 Downgrade-Recht
Information von kgborn vor 6 StundenWindows 10

Möglicherweise ist das bei euch bekannt - ich habe es gerade aus einem Newsletter eines OEMs herausgefischt. Beim Downgrade-Recht ...

CMS
Freie Wähler Bayern MySQL PW online
Information von sabines vor 9 StundenCMS4 Kommentare

Die Typo3 Installation der Freien Wähler Bayern scheint wohl längere Zeit nicht mehr angefasst und/oder fehlkonfiguriert zu sein. Nach ...

Sicherheit
Adminrechte dank Bug in Intel HD Graphics Treiber
Information von DerWoWusste vor 1 TagSicherheit

Intel HD graphics 4200 und neuer (4400, 4600 520,530,620, 630,) sind auf jeden Fall betroffen und bereinigte Treiber sind ...

Heiß diskutierte Inhalte
Windows 10
Sysprep Fehler im Log kann nicht starten
Frage von grillinator95Windows 1020 Kommentare

Hallo, kann leider SYSPREP nicht mehr starten, Win10 64bit. Logfile sagt folgendes: 2018-10-17 13:44:56, Info SYSPRP 2018-10-17 13:44:56, Info ...

Internet
Ist diese URL denkbar (Syntax)?
gelöst Frage von departure69Internet16 Kommentare

Hallo. Der Sohn eines Arbeitskollegen hat im Gymnasium EDV-Unterricht. Leider hat er in der letzten Klassenarbeit einen Fünfer geschrieben. ...

Debian
Linux debian 9 Installation
Frage von Green14Debian15 Kommentare

Hallo zusammen, ich habe mich ein wenig mit Debian auseinandergesetzt und möchte mir eine Standard-Installation als Grundlage für andere ...

Windows Netzwerk
Gruppenrichtlinie für einen PC deaktivieren
gelöst Frage von Florian961988Windows Netzwerk14 Kommentare

Hallo, kleines Problem und immoment finde ich dazu keine Lösung oder mir fällt nicht ein, wie ich es suche ...