Embedded Files
siehe: Google Docs-Editors Hilfe
Achtung oft sind die Formeln in der Dokumentation mit , statt ; dokumentiert.
Nützliche Spaltenformeln
Nützliche Spaltenformeln
Textspalten addieren (ergibt: Einladung für: … und … )
Texte addieren
Texte addieren
=CONCATENATE("Einladung für: ";Kontakt!A1;" und ";Kontakt!A2)
Textspalten und “ Hinzufügen (ergibt: Sonderangebot “...” )
Textspalten und “ Hinzufügen (ergibt: Sonderangebot “...” )
=CONCATENATE("Sonderangebot " & CHAR(34);D154; CHAR(34) )
Werte austauschen ( alle Komma durch Semikolon ersetzt )
Werte austauschen ( alle Komma durch Semikolon ersetzt )
=SUBSTITUTE( Kontakte!A1 ;",";";")
Buchstaben “lowercase” ( aus “A” wird “a” )
Buchstaben “lowercase” ( aus “A” wird “a” )
=LOWER(Kontakte!A1)
Wert bedingt setzen ( aus “D” wird “Deutschland” )
Wert bedingt setzen ( aus “D” wird “Deutschland” )
=IF( Kontakte!A1="D";"Deutschland";Kontakte!A1)
Textzellen verbinden und trimmen: ( aus “ D “ wird “D” )
Textzellen verbinden und trimmen: ( aus “ D “ wird “D” )
=TRIM(Kontakte!A1 & " " & Kontakte!A2 & " "&Kontakte!A3)
Einer der Werte entspricht dann ( aus A, B oder D wird “abc” sonst “...” )
Einer der Werte entspricht dann ( aus A, B oder D wird “abc” sonst “...” )
=IF(OR(Kontakte!A1="A" ; Kontakte!A1="B";Kontakte!A1;"C"); ”abc”; ”...”)
Bedingt Bereiche austauschen ( aus 05202 wird +495202 )
Bedingt Bereiche austauschen ( aus 05202 wird +495202 )
=SUBSTITUTE(IF( Kontakte!A1 = "D"; IF(LEN(Kontakte!A2)>0;"+49 " & RIGHT(Kontakte!A2;LEN(Kontakte!L2)-1);"");Kontakte!A2);",";";")
TABS entfernen
TABS entfernen
=SUBSTITUTE(CONCATENATE(A2);CHAR(9);";")
ZEILENUMBRUCH (harten Zeilenumbruch durch weichen Zeilenumbruch ersetzen)
ZEILENUMBRUCH (harten Zeilenumbruch durch weichen Zeilenumbruch ersetzen)
=SUBSTITUTE(A2;CHAR(10);CHAR(13))
Zeichenbereich von links bis zu einem Leerzeichen ( aus 1.1.2000 13:12:04 wird 1.1.2000)
Zeichenbereich von links bis zu einem Leerzeichen ( aus 1.1.2000 13:12:04 wird 1.1.2000)
=if (A2="";"";LEFT(A2;FIND(" ";A2;1)))
Zeilenzähler
Zeilenzähler
=ROW(A1) Schauen ob in Spalte B Texteinträgen z.B. Firmen doppelt vorkommen (unterscheidet Klein-Groß-Schreibung nicht)
=COUNTIF($B$2:$B$4182;B2)>1
Schauen ob in Spalte "C" der Text in der Spalte "A" im Tabellenblatt "Kundenliste" vorhanden ist. (Klein-Groß-Schreibung berücksichtigt)
=IF(SUMPRODUCT(EXACT(Kundenliste!$A$2:$A$1000;C3))=1;"ok";"KUNDE?")
=SUBSTITUTE(A2;CHAR(10);CHAR(13))
Vorname und Name aus einem Text
Vorname und Name aus einem Text
Vorname
=LEFT(EXPORT!AL2;LEN(EXPORT!AL2)-LEN(TRIM( RIGHT( SUBSTITUTE( EXPORT!AL2 ; " " ; REPT( " " ; 100 ) ) ; 100 ) )))
Nachname
=TRIM( RIGHT( SUBSTITUTE( EXPORT!AL2 ; " " ; REPT( " " ; 100 ) ) ; 100 ) )
Datum in Millisekunden & in einem Link per URL Parameter an ein Formular übergeben
Datum in Millisekunden & in einem Link per URL Parameter an ein Formular übergeben
Das Datum in Millisekunde (das Feld J2 ist ein Datumsfeld)
= ((J2-5,5/24-date(1970;1;1))*86400000)
Offsett hinzufügen
= ((J2-5,5/24-date(1970;1;1))*86400000) +(60000*270)
Link an ein Formular und F1640 ist das Datumfeld
= HYPERLINK(CONCATENATE("https://www.brandsmill.de/#webform?m=1&id=6670504487288832&bg=fffff&bgr=ffffff&bgh=66001f&bgt=ffffff&w=900&fh=600";"&f=F1640:";((J2-5,5/24-date(1970;1;1))*86400000)+(60000*270)
Schlüsselwerte aus einer Liste lesen
Schlüsselwerte aus einer Liste lesen
LOOKUP "ALLE WERTE MÜSSEN VORHANDEN SEIN, sonst wird der nächste nähere Wert genommen"
LOOKUP "ALLE WERTE MÜSSEN VORHANDEN SEIN, sonst wird der nächste nähere Wert genommen"
VLOOKUP "FALSE -> zeigt nicht den näheren Wert und ist dann exakter, wenn es um das sicher Auffinden eines Schlüsselwertes geht."
VLOOKUP "FALSE -> zeigt nicht den näheren Wert und ist dann exakter, wenn es um das sicher Auffinden eines Schlüsselwertes geht."
Du möchtest einen Wert aus einer Tabelle über einen Schlüssel ersetzten.
LOOKUP(Suchschlüssel; Suchbereich; [Ergebnisbereich])
Zusammenfassung
Sucht in einer sortierten Zeile oder Spalte nach einem Schlüssel und gibt den Wert der Zelle in einem Ergebnisbereich an der gleichen Position wie die Suchzeile oder -spalte zurück.
Suchschlüssel
Wert, nach dem in der Zeile oder Spalte gesucht werden soll, z. B. "42", "Katzen" oder "l24"
Suchbereich|Suchergebnisarray
Eine Möglichkeit der Verwendung dieser Funktion ist die Angabe eines "Suchbereichs" mit einer einzelnen sortierten Zeile oder Spalte, in der mit einem zweiten Argument "Ergebnisbereich" nach einem "Suchschlüssel" gesucht werden soll. Eine weitere Möglichkeit besteht darin, diese beiden Argumente in einem "Suchergebnisarray" zu kombinieren, wobei in der ersten Zeile oder Spalte gesucht und ein Wert aus der letzten Zeile oder Spalte im Array zurückgegeben wird. Wird "Suchschlüssel" nicht gefunden, wird möglicherweise eine ungenaue Übereinstimmung zurückgegeben.
Ergebnisbereich - [optional]
Bereich, aus dem ein Ergebnis zurückgegeben werden soll. Der zurückgegebene Wert entspricht der Position, an der der "Suchschlüssel" im "Suchbereich" gefunden wurde. Dieser Bereich darf nur aus einer einzelnen Zeile oder Spalte bestehen und sollte nicht verwendet werden, wenn die Methode "Suchergebnisarray" eingesetzt wird.
Praxisbeispiel
Praxisbeispiel
Wir haben aus dem Kontakt Export eine Tabelle mit den Feldern der Kundennummer. Die Spalte Company muss noch gefüllt werden mit dem Firmennamen. Dazu wird die Formel zur Company im 1. Tab "Order" je Zeile hinterlegt mit einem LOOKUP auf 2. Tab "Company List".
Mit Lookup wird der nächstgelegene Wert gefunden (in der Company List fehlt 1002. Der Rückgabewert ist dann 1001 Daimler
=LOOKUP(VALUE(B2);'Company List'!$A$2:$A$22;'Company List'!$B$2:$B$22)
Mit VLookup wird exakte Wert gefunden (in der Company List fehlt 1002. Der Rückgabewert ist dann #N/A.
=VLOOKUP(VALUE(D2);'Company List'!$A$2:$A$4;1;FALSE)
VLOOKUP "exakte Suche"
VLOOKUP "exakte Suche"
Wie beim LOOKUP sucht VLOOKUP auch in einem Bereich. Das Ergebnis liefert einen Fehler wenn der Schlüsselwert nicht gefunden wird.
VLOOKUP(Suchschlüssel; Suchbereich; index; FALSE)
index ist der zu liefernde relative Wert aus der horizontalen Liste.
Das FALSE steht für exakten Schlüssel finden setzt man TRUE ein wird wie beim LOOKUP der nächst mögliche gefunden.
Relative Bezüge und absolute Bezüge (Google Tabellen)
Relative Bezüge und absolute Bezüge (Google Tabellen)
In unserem Bespiel würde die Formel zunächst so aussehen:
=LOOKUP(VALUE(B2);'Company List'!A2:A22;'Company List'!B2:B22)
Wird diese Formel nun auf weitere Zeilen kopiert so werden die Spalten und Zeilen automatisch angepasst und ergeben:
=LOOKUP(VALUE(B3);'Company List'!A3:A23;'Company List'!B3:B23)
Damit wird der Suchbereich für das LOOKUP nicht mehr korrekt genutzt.
RICHTIG IST es die Werte über $ für das kopieren festzustellen:
=LOOKUP(VALUE(B2);'Company List'!$A$2:$A$22;'Company List'!$B$2:$B$22)
Genutzte Werte überprüfen "nicht Äpfel mit Birnen vergleichen"
Genutzte Werte überprüfen "nicht Äpfel mit Birnen vergleichen"
1. Der Suchbereich muss sortiert sein. Nur so wird das LOOKUP korrekt durchgeführt.
2. Die Werte müssen übereinstimmen. Beim Export aus brand's mill werden Zahlen als Zeichen exportiert damit diese nicht umformatiert werden so wird die Nr 1001 mit einem führenden ' übergeben ' 1003 . Damit wird es notwendig die Spalte "NumberCustomer" nicht über B3 sonder über VALUE(B3) zu verwenden.
Page updated
Report abuse