Hallo Excel Freunde!
Ich möchte die Spaltennummer in einem mit Namen vergebenen Bereich ermitteln.
z.B. möchte ich nach dem Text Mustermann im Bereich suchen und die Formel sollte mir dann die Spaltennummer liefern.
1.Der Name Mustermann, kann in jeder Zelle des mit Namen vergebenen Bereichs stehen, aber nur einmal.
2.Der Bereich befindet sich in einem anderen Tabellenblatt, gleiche Datei.
Gruß
Berry
Moin!
Mehr Infos bitte!
Wie sieht der Bereich aus?
Eine Zeile?
Dann mittels VERGLEICH("Suche";Bereich;0)
Ist der Bereich ein "echter" zweidimensionaler?
Dann am ehesten mittels VBA und Range("Bereich").Find … .Column
Soll die Spaltennummer die absolute oder die relative im Bereich sein?
Beispieldatei?
Gruß Ralf
Hallo Ralf!
Kein VBA, es muß eine Formel werden. Der Bereich sollte in seiner Größe variabel sein, der Hintergrund besteht darin anhand der Spaltennummer die Koordinaten für die nächste Formel zu liefern.
Der Benutzer markiert in der Tabelle „x“ einen Bereich mit dem Namen z.B. DB und anhand der Formel wird die Spaltennummer ausgegeben in der sich mein Suchkriterium befindet.
Ich hoffe jetzt ist es etwas verständlicher beschrieben.
Gruß
Berry
Lade doch einfach mal ein Beispiel hoch, Berry!
Ohne Beispiel verbleibt erstmal (wenn ich Dich richtig verstanden habe)
=MIN(WENN(A1:F20="x";SPALTE(A:F)))
Achtung! Matrixformel!
| A | B | C | D | E | F | G | H |
| 1 | y | y | y | y | y | y | | 3 |
| 2 | y | y | y | y | y | y | | |
| 3 | y | y | y | y | y | y | | |
| 4 | y | y | y | y | y | y | | |
| 5 | y | y | y | y | y | y | | |
| 6 | y | y | y | y | y | y | | |
| 7 | y | y | y | y | y | y | | |
| 8 | y | y | y | y | y | y | | |
| 9 | y | y | y | y | y | y | | |
| 10 | y | y | y | y | y | y | | |
| 11 | y | y | y | y | y | y | | |
| 12 | y | y | y | y | y | y | | |
| 13 | y | y | x | y | y | y | | |
| 14 | y | y | y | y | y | y | | |
| 15 | y | y | y | y | y | y | | |
| 16 | y | y | y | y | y | y | | |
| 17 | y | y | y | y | y | y | | |
| 18 | y | y | y | y | y | y | | |
| 19 | y | y | y | y | y | y | | |
| 20 | y | y | y | y | y | y | | |
| Formeln der Tabelle |
| Zelle | Formel | | H1 | {=MIN(WENN(A1:F20="x";SPALTE(A:F)))} |
|
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen! |
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Ralf
Hallo Ralf!
Konnte leider keine Datei hochladen, da Firmennetzwerk zurzeit.
Die Formel funktioniert genial und für die Zeilenabfrage ändere ich den letzten teil nur von Spalte auf Zeile.
Danke und allen ein schönes Wochenende !!!
Gruß
Berry
Hallo nochmal Nachgefragt!
Die Formel von Ralf funktioniert, mit der Einschränkung dass ich bei der Namensvergabe für den Bereich mit der Zelle A1 beginne.
Gibt es noch eine Möglichkeit die Formel dahingehend zu Verändern den Bereichsbeginn mit D4 oder X6 zu belegen und dann die Spaltennummer ausgehend vom Bereichsbeginn zu ermitteln?
Gruß
Berry
Hi,
Ralfs Formel adaptiert, biete ich dir folgende Lösung:
as_konvert (2) | X | Y | Z | AA | AB | AC | AD | AE |
| 6 | y | y | y | y | y | y | | 18(Z) 3 (S) |
| 7 | y | y | y | y | y | y | | |
| 8 | y | y | y | y | y | y | | |
| 9 | y | y | y | y | y | y | | |
| 10 | y | y | y | y | y | y | | |
| 11 | y | y | y | y | y | y | | |
| 12 | y | y | y | y | y | y | | |
| 13 | y | y | y | y | y | y | | |
| 14 | y | y | y | y | y | y | | |
| 15 | y | y | y | y | y | y | | |
| 16 | y | y | y | y | y | y | | |
| 17 | y | y | y | y | y | y | | |
| 18 | y | y | x | y | y | y | | |
| 19 | y | y | y | y | y | y | | |
| 20 | y | y | y | y | y | y | | |
| 21 | y | y | y | y | y | y | | |
| 22 | y | y | y | y | y | y | | |
| 23 | y | y | y | y | y | y | | |
| 24 | y | y | y | y | y | y | | |
| 25 | y | y | y | y | y | y | | |
| Formeln der Tabelle |
| Zelle | Formel | | AE6 | {=MIN(WENN(Datenbereich="x";ZEILE(6:25)))&"(Z) "&MIN(WENN(Datenbereich="x";SPALTE(A:F)))&" (S)"} |
|
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen! |
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Der Bereich X6:AC25 ist im Namensmanager als "Datenbereich" benannt.
Hallo WillWissen,
deine Darstellung ist genau richtig, nur aufgrund der Namensvergabe sollte als Ergebnis jetzt Zeile 13 Spalte 3 herauskommen.
Das wäre mein Wunsch, wenn das überhaupt mit einer Formel geht ???
Gruß
Berry
Moin!
Du hast die Zählvariablen noch nicht verinnerlicht!
Es geht bei ZEILE(6:25) um die Wahl zwischen 6 bis 25
Willst Du die relative Position im Bereich ermitteln, muss Zeile bei 1 beginnen.
(Genauso wie bei den Spalten, die bei Günter ja auch bei A beginnen, obwohl der Bereich erst bei X beginnt)
Gib der Formel ein wenig Puffer mit und dies funktioniert zunächst mal für 100 Zeilen/Spalten:
(den Puffer braucht man nicht unbedingt, hier kann man auch den tatsächlichen Bereich, wenn er gefüllt ist, errechnen)
Die Light-Variante:
=MIN(WENN(Datenbereich="x";ZEILE(A1:A100)))&"(Z) "&MIN(WENN(Datenbereich="x";SPALTE(A1:CV1)))&" (S)"
Gruß Ralf
Moment!
Ich habe es jetzt mal nachgestellt.
Die maximale Spalte/Zeile darf nicht überschritten werden, sonst gibt es ein #NV
Muss ich mir Gedanken machen …
Bis dahin, Ralf