SVERWEIS IST JA EINE FEINE SACHE ABER AUCH SCHWER ZU VERSTEHEN.
Schaut mal rein und helft bitte weiter. https://www.herber.de/bbs/user/88286.xlsx
Gruß Horst
B | C | D | E | F | |
1 | ort | ||||
2 | 1 | AAAAAAAAAAAAAAA | |||
3 | 2 | GGGGGGGGGGGGGG | |||
4 | 3 | sssssss | 1 | AAAAAAAAAAAAAAA | |
5 | 4 | UUUUUUUUUUU | 2 | Axel | |
6 | 5 | TTTTTTTTTTTTTTTTTTT | 3 | GGGGGGGGGGGGGG | |
7 | 6 | JJJJJJJJJJJJJJJJ | 4 | sssssss |
Formeln der Tabelle | ||||
| ||||
Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! |
A | B | C | D | E | F | G | H | |
1 | Axel | Ort | ||||||
2 | AAAAAAAAAAAAAAA | |||||||
3 | AAAAAAAABBBBBBBBB | Ort1 | Ort2 | |||||
4 | GGGGGGGGGGGGGG | AAAAAAAAAAAAAAA | Axel | |||||
5 | sssssss | Axel | AAAAAAAABBBBBBBBB | |||||
6 | UUUUUUUUUUU | GGGGGGGGGGGGGG | Axel | |||||
7 | UUUUUUUUUUU | sssssss | GGGGGGGGGGGGGG | |||||
8 | TTTTTTTTTTTTTTTTTTT | Axel | sssssss | |||||
9 | JJJJJJJJJJJJJJJJ | UUUUUUUUUUU | Axel | |||||
10 | OOOOOOOOOOOOOO | Axel | UUUUUUUUUUU | |||||
11 | KKKKKKKKKK | DDDDDDDD | WWWWW | |||||
12 | RRRRRRRRRRRRRRRRRRRRR | TTTTTTTTTTTTTTTTTTT | Axel | |||||
13 | RRRRRRRRRRRRRRRRRRRRR | Otto | TTTTTTTTTTTTTTTTTTT | |||||
14 | Axel | JJJJJJJJJJJJJJJJ | ||||||
15 | Axel | OOOOOOOOOOOOOO | ||||||
16 | KKKKKKKKKK | Axel | ||||||
17 | OOOOOOOOOOOOOO | Fritz | ||||||
18 | RRRRRRRRRRRRRRRRRRRRR | Axel | ||||||
19 | Axel | RRRRRRRRRRRRRRRRRRRRR | ||||||
20 |
Formeln der Tabelle | ||||
| ||||
Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | ||||
Matrix verstehen |
BA | BB | BC | |
1 | Stolpe-Dreifurt e.V | ||
2 | 1 | DevilLegend | |
3 | 2 | Herta Aken 0815 | |
4 | 3 | FC Leknica | |
5 | 4 | PNK Raciborz RD | |
6 | 5 | BS Nike 1909 | |
7 | 6 | FC Pfulliwood | |
8 | 7 | KP Odra Wodzisław Śląski | |
9 | 8 | Dynamo Dortmund | |
10 | 9 | 1.FC Gran Canaria | |
11 | 10 | 1. Fc Dixikloo | |
12 | 11 | ESV Horrem | |
13 | 12 | Mc Poland | |
14 | 13 | Polonia Elsen | |
15 | 14 | Schlaatzer FC | |
16 | 15 | FC PICPIWO | |
17 | 16 | 1.FC Zgorcelez | |
18 | 17 | GD Ansk |
verwendete Formeln | |||
Zelle | Formel | Bereich | R1C1 für Add In |
BC2 | {=LINKS(INDEX($CE$2:$CE$35;KKLEINSTE(WENN(LINKS($CC$2:$CC$35;LÄNGE($BA$1))=$BA$1;ZEILE($1:$34));ZEILE(Z1)));FINDEN(" (";INDEX($CE$2:$CE$35;KKLEINSTE(WENN(LINKS($CC$2:$CC$35;LÄNGE($BA$1))=$BA$1;ZEILE($1:$34));ZEILE(Z1)))))} | $BC$2 | =LEFT(INDEX(R2C83:R35C83,SMALL(IF(LEFT(R2C81:R35C81,LEN(R1C53))=R1C53,ROW(R1:R34)),ROW(R[-1]C[-29]))),FIND(" (",INDEX(R2C83:R35C83,SMALL(IF(LEFT(R2C81:R35C81,LEN(R1C53))=R1C53,ROW(R1:R34)),ROW(R[-1]C[-29]))))) |
BA | BB | BC | CB | CC | CD | CE | |
1 | Stolpe-Dreifurt e.V | Heim | Gast | ||||
2 | 1 | DevilLegend | 1 | 1. Fc Dixikloo (110) | Stolpe-Dreifurt e.V (86) | ||
3 | 2 | Herta Aken 0815 | 2 | Stolpe-Dreifurt e.V (86) | DevilLegend (56) | ||
4 | 3 | FC Leknica | 3 | ESV Horrem (93) | Stolpe-Dreifurt e.V (86) |
verwendete Formeln | |||
Zelle | Formel | Bereich | R1C1 für Add In |
BC2 | {=LINKS(INDEX($CE$2:$CE$35;KKLEINSTE(WENN(LINKS($CC$2:$CC$35;LÄNGE($BA$1))=$BA$1;ZEILE($1:$34));ZEILE(Z1)));FINDEN(" (";INDEX($CE$2:$CE$35;KKLEINSTE(WENN(LINKS($CC$2:$CC$35;LÄNGE($BA$1))=$BA$1;ZEILE($1:$34));ZEILE(Z1)))))} | $BC$2 | =LEFT(INDEX(R2C83:R35C83,SMALL(IF(LEFT(R2C81:R35C81,LEN(R1C53))=R1C53,ROW(R1:R34)),ROW(R[-1]C[-29]))),FIND(" (",INDEX(R2C83:R35C83,SMALL(IF(LEFT(R2C81:R35C81,LEN(R1C53))=R1C53,ROW(R1:R34)),ROW(R[-1]C[-29]))))) |
CB3:CB4 | =CB2+1 | =R[-1]C+1 | |
CC2:CC4,CE2:CE4 | =dATEN!CE2 | =dATEN!RC[2] |
Die Datei https://www.herber.de/bbs/user/88311.xlsx wurde aus Datenschutzgründen gelöscht
AZ | |
2 | DevilLegend |
verwendete Formeln | |||
Zelle | Formel | Bereich | R1C1 für Add In |
AZ2 | =LINKS(BY2;FINDEN(" (";BY2)-1) | =LEFT(RC[25],FIND(" (",RC[25])-1) |