{"text_format":"1","workbook":"xlsx","n_sheets":20,"sheets":["Start","Vorlage","Zufallszahlen","Eine Rechnung (A)","Bevor Sie weitermachen … (1)","Text (1, A)","Text (2, A)","Text (3, A)","Datum & Zeit (1)","Datum & Zeit (2, A)","WENN (1, A)","WENN (2, A)","Bevor Sie weitermachen … (2)","WENN (3, A)","Bevor Sie weitermachen … (3)","WENN (4, A)","WENN (5, A)","Eine Lösung aus der Praxis","Noch eine Lösung aus der Praxis","Nützliche Formeln"],"not_rendered":{"charts":0,"images":16,"macros":1,"external_links":0,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Start","index":0,"n_rows":33,"n_cols":9,"rows_kept":33,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4],[5,6,33,6],[5,5,33,5]]}
[null,null,null,null,null,1,2,null,0]
[1,"Start"]
[2,"Start"]
[]
[null,null,null,2,0]
[null,null,"Start",null,null,"For many basic Excel techniques, which are used here in this file, there are detailed explanations in the file \"Excel-Basics\".\n\nTherefore - if here in this file it is said \"Do this … and that …\" and you don't know how to do it - please have a look into the file \"Excel-Basics\". Hopefully you will find the needed explanations there. \n\nIf not - send me a short notice to stern@jade-hs.de!\n\nBefore you start working with this file you should definitely work through the two sheets \"How to make notes\" and \"Help ...\" in the file \"Excel-Basics\"!\n\nIf the buttons for language switching don't work, please click the following:\n\nFile / Options / Trust Center / Trust Center Settings / Macro Settings / Enable All Macros \n\nThen close and re-open this file again.","Zu vielen grundlegenen Excel-Techniken, die hier in dieser Datei benutzt werden, gibt es detaillierte Erläuterungen in der Datei \"Excel-Basics\".\n\nWenn es daher in dieser Datei heißt \"Machen Sie dieses … und jenes …\" und Sie wissen nicht wie - sehen Sie bitte in der Datei \"Excel-Basics\" nach. Hoffentlich finden Sie dort die benötigten Erklärungen.\n\nWenn nicht - schicken Sie mir bitte einen Hinweis an stern@jade-hs.de!\n\nBevor Sie beginnen, mit dieser Datei zu arbeiten, sollten Sie aber auf jeden Fall zuerst die beiden Blätter \"Notizen machen\" und \"Hilfe ...\" in der Datei \"Excel-Basics\" durchgearbeitet haben!\n\nWenn die Buttons zur Sprachumschaltung nicht funktionieren, klicken Sie bitte folgendes an:\n\nDatei / Optionen / Trust-Center / Einstellungen für das Trust-Center / Makroeinstellungen / Alle Makros aktivieren\n\nDann diese Datei schließen und erneut öffnen."]
[null,null,"Vorlage"]
[null,null,"Zufallszahlen"]
[null,null,"Eine Rechnung (A)"]
[null,null,"Bevor Sie weitermachen … (1)"]
[null,null,"Text (1, A)"]
[null,null,"Text (2, A)"]
[null,null,"Text (3, A)"]
[null,null,"Datum & Zeit (1)"]
[null,null,"Datum & Zeit (2, A)"]
[null,null,"WENN (1, A)"]
[null,null,"WENN (2, A)"]
[null,null,"Bevor Sie weitermachen … (2)"]
[null,null,"WENN (3, A)"]
[null,null,"Bevor Sie weitermachen … (3)"]
[null,null,"WENN (4, A)"]
[null,null,"WENN (5, A)"]
[null,null,"Eine Lösung aus der Praxis"]
[null,null,"Nützliche Formeln"]
[]
[]
[]
[]
[]
[]
[]
[]
[0]

{"sheet":"Vorlage","index":1,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[[25,2,28,8],[1,1,1,4],[2,1,2,4],[4,2,10,8],[11,2,19,8],[21,2,24,8]]}
[null,null,null,null,null,0]
[1,"Template",null,null,null,"Back to Start"]
[2,"Vorlage",null,null,null,"Startseite"]
[0]
[1,"Attention","After having made a copy of this sheet, click again on the language button on the 'Start' sheet. You will get the message, that the copy was given a new name, because two sheets with the same name are not allowed.\nCertainly you want to give the copy another name. Type it into cell B2 and click again on the language button on the 'Start' sheet. Now the new name will appear in the list of sheets on the 'Start' sheet as well as on the bottom of the Excel window with the sheet names.\nAfterwards you can delete the rows with this text and use the sheet as usual."]
[1]
[1]
[1]
[1]
[1]
[1]
[2,"Achtung","Nachdem Sie eine Kopie dieses Blattes angelegt haben, klicken Sie noch einmal auf den Sprachbutton auf dem 'Start'-Blatt. Sie werden eine Meldung bekommen, dass das Blatt umbenannt wurde, weil es nicht zwei Blätter mit demselben Namen geben darf.\nSicherlich wollen Sie dem Blatt selber einen anderen Namen geben. Tippen Sie ihn in Zelle B3 ein und klicken Sie erneut auf den Sprachbutton auf dem 'Start'-Blatt. Der neue Name erscheint dann in der Liste der Blätter auf dem 'Start'-Blatt und am unteren Rand des Excel-Fensters mit den Blattnamen.\nAnschließend können Sie die Zeilen mit diesen Text löschen und dieses Blatt wie gewohnt benutzen."]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[]
[]
[1,"Attention","You really have to delete the ROWS with the info text - not only the CELLS! To do that, click on the row number 4 at the left window border, hold the left mouse button down and move downwards to the last row with text.\nThen click Home / Cells / Delete / Delete Sheet Rows"]
[1]
[1]
[1]
[2,"Achtung","Sie müssen wirklich die ZEILEN mit dem Infotext löschen - nicht nur die ZELLEN! Dazu müssen Sie am linken Fensterrand auf die Zeilennummer 4 klicken und dann mit gedrückter linker Maustaste bis zur letzten Textzeile herunter fahren.\nAnschließend klicken Sie Start / Zellen / Löschen / Blattzeilen löschen"]
[2]
[2]
[2]
[]
[]
[]
[]
[]
[]
[]
[0]

{"sheet":"Zufallszahlen","index":2,"n_rows":44,"n_cols":10,"rows_kept":44,"cut":false,"hidden":false,"merged":[[42,2,42,8],[1,1,1,4],[2,1,2,4],[41,2,41,8],[4,2,7,7],[9,2,11,7],[13,2,17,9],[18,2,21,9]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"Random numbers",null,null,null,"Back to Start"]
[2,"Zufallszahlen",null,null,null,"Startseite"]
[]
[2,null,"Diese Excel-Datei enthält ein Formular zur Generierung von Zufallszahlen.\nSie können es mit der Tastenkombination Strg-Umschalt-N öffnen.\nDann können Sie es benutzen, um sich schnell beliebige Mengen von Übungsdaten zu generieren!"]
[2]
[2]
[2]
[]
[1,null,"This Excel file contains a form for the generation of random numbers.\nYou can open it with the key combination Ctrl-Shift-N.\nThen you can use it to generate very quickly any number of trial data!"]
[1]
[1]
[]
[2,"Achtung","Wenn Sie nach dem Drücken von Strg-Umschalt-N eine Fehlermeldung bekommen, müssen Sie zunächst die Makros aktivieren:\n\nDATEI / \"Optionen\" / \"Trust Center\" / \"Einstellungen für das Trust Center\" / \"Makroeinstellungen\"\n\"Alle Makros aktivieren\" / \"OK\" / \"OK\""]
[2]
[2]
[2]
[2]
[1,"Attention","If you see an error message after pressing Ctrl-Shift-N you have to activate the macros first:\n\nFILE / \"Options\" / \"Trust Center\" / \"Trust Center Settings\" / \"Macro Settings\"\n\"Enable all macros\" / \"OK\" / \"OK\""]
[1]
[1]
[1]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[1,"Exercise","Try it!"]
[2,"Übung","Probieren Sie es aus!"]
[0]

{"sheet":"Eine Rechnung (A)","index":3,"n_rows":107,"n_cols":10,"rows_kept":107,"cut":false,"hidden":false,"merged":[[96,2,97,7],[98,2,99,7],[101,2,102,7],[103,2,104,7],[51,2,55,8],[57,2,61,8],[64,2,68,8],[70,2,75,8],[78,2,85,8],[87,2,94,8],[44,2,48,8],[1,1,1,4],[2,1,2,4],[4,2,5,7],[6,2,7,7],[38,2,42,8]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"An invoice (E)",null,null,null,"Back to Start"]
[2,"Eine Rechnung (A)",null,null,null,"Startseite"]
[]
[2,"Übung 1","Formatieren Sie die folgenden Daten so wie im Bild daneben zu sehen ist und geben Sie die erforderlichen Formeln ein! "]
[2,null,null,null,null,null,null,null,null,"Ziel:"]
[1,"Exercise 1","Format the following data as shown in the picture right to them and enter the necessary formulas!",null,null,null,null,null,null,"Objective:"]
[1]
[]
[null,"Computer Trading Ltd., Duxford, CB22 4RG, Port Av. 223"]
[]
[null,"Paul McBride"]
[null,"Bristol, BS42 7TV"]
[null,"Wall Str. 5"]
[null,null,null,null,null,null,"Duxford, "]
[]
[null,null,null,"Invoice Nr. 345-12/001"]
[]
[null,"Thank you for your order. Hereby we allow us to charge you the following positions:"]
[]
[null,"Position","Article nr.","Description","Amount","Price","Discount","Total price"]
[null,1,"55.456.23","Monitor",12,249]
[null,2,"55.567.43","Computer",12,999]
[null,3,"56.321.43","Mouse",12,9]
[null,4,"67.678.78","Printer",5,299]
[null,5,"56.345.21","Scanner",7,199]
[null,6,"45.567.67","Speaker",24,29]
[null,null,null,null,null,null,"Net"]
[null,null,null,null,null,null,"VAT"]
[null,null,null,null,null,null,"Gross"]
[]
[null,"Please transfer the amount invoiced within 30 days until"]
[null,"with a specification of the invoice number from above"]
[null,"to the bank account with the IBAN",null,null,null,"GB76  1122 4422 6688 2266 55"]
[null,"at the Royal Bank of England"]
[]
[]
[2,"Prozent"]
[2,null,"Platz für Notizen!"]
[2]
[2]
[2]
[2]
[1,"Percent"]
[1,null,"Make your notes here!"]
[1]
[1]
[1]
[1]
[]
[2,"Rabattformel"]
[2,null,"Platz für Notizen!"]
[2]
[2]
[2]
[2]
[1,"Discount formula"]
[1,null,"Make your notes here!"]
[1]
[1]
[1]
[1]
[]
[2,"Stammdaten"]
[2,null,"Platz für Notizen!"]
[2]
[2]
[2]
[2]
[1,"Base data"]
[1,null,"Make your notes here!"]
[1]
[1]
[1]
[1]
[1]
[]
[2,"Datum"]
[2,null,"Platz für Notizen!"]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[1,"Date"]
[1,null,"Make your notes here!"]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[]
[2,"Übung 2","Arbeiten Sie in der Datei \"Excel-Basics\" das Blatt \"Spur und Fehlerüberprüfung\" durch und wenden Sie diese Technik hier auf die Rechnung an!"]
[2]
[1,"Exercise 2","Work through the sheet \"Trace and Error Checking\" in the file \"Excel-Basics\" and apply this technique here to the invoice!"]
[1]
[]
[2,"Übung 3","Arbeiten Sie in der Datei \"Excel-Basics\" das Blatt \"Blattschutz\" durch und wenden Sie diese Technik hier auf die Rechnung an!"]
[2]
[1,"Exercise 3","Work through the sheet \"Protect Sheet\" in the file \"Excel-Basics\" and apply this technique here to the invoice!"]
[1]
[]
[0]

{"sheet":"Bevor Sie weitermachen … (1)","index":4,"n_rows":22,"n_cols":9,"rows_kept":22,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4],[4,2,7,9],[8,2,11,9]]}
[null,null,null,null,null,null,null,null,0]
[1,"Before you continue … (1)",null,null,null,"Back to Start"]
[2,"Bevor Sie weitermachen … (1)",null,null,null,"Startseite"]
[]
[2,null,"… arbeiten Sie bitte in der Datei \"Excel-Basics\" die Blätter \n   \"Notizen machen\" und \n   \"Hilfe …\" \ndurch!"]
[2]
[2]
[2]
[1,null,"… please work through the worksheets \n   \"How to make notes\" and \n   \"Help …\" \nin the file \"Excel-Basics\"!"]
[1]
[1]
[1]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[0]

{"sheet":"Text (1, A)","index":5,"n_rows":86,"n_cols":10,"rows_kept":86,"cut":false,"hidden":false,"merged":[[57,2,68,7],[70,2,81,8],[44,9,53,14],[23,2,28,7],[30,2,31,7],[33,2,39,7],[1,1,1,4],[2,1,2,4],[4,2,9,7],[11,2,12,7],[14,2,21,7]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"Text (1, E)",null,null,null,"Back to Start"]
[2,"Text (1, A)",null,null,null,"Startseite"]
[]
[2,"Übung","Finden Sie unter Benutzung der Excel-Hilfe heraus, wie Sie die Anschrift mit Hilfe der Funktionen \n   LINKS()\n   RECHTS()\n   LÄNGE()\nin PLZ und Stadt zerlegen können."]
[2]
[2]
[2]
[2]
[2]
[2]
[2,null,"Mit Hilfe des &-Operators können Sie Zellinhalte (z.B. PLZ und Stadt) zusammenfügen (\"Address 2\")."]
[2]
[2]
[2,null,"Mit der Funktion GLÄTTEN() können Sie führende und angehängte Leerzeichen beseitigen.\n\nAchtung: Daten, die Sie als Export aus anderen IT-Systemen bekommen (z.B. SAP), haben oft dieses Problem!\nWarum? Speicherung mit fester Datenlänge (z.B. 30 Zeichen für die Stadt, 50 Zeichen für die Straße, ... usw.) Der Rest wird mit Leerzeichen aufgefüllt!"]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[]
[1,"Exercise","Use the Excel help to find out how to separate the zip code and the city using the functions\n   LEFT()\n   RIGHT()\n   LEN()"]
[1]
[1]
[1]
[1]
[1]
[1]
[1,null,"Using the & operator you can join the content of different cells (e.g. zip code and city)."]
[1]
[1]
[1,null,"Using the function TRIM() you can remove leading and appended spaces.\n\nAttention: This problem is very common with data from other application systems (e.g. SAP).\nWhy? Storage with fixed data length (e.g. 30 characters for the city, 50 characters for the street, ...) The rest is filled with spaces!"]
[1]
[1]
[1]
[1]
[1]
[1]
[]
[]
[2,null,null,null,null,null,null,null,"Ziel:"]
[1,null,null,null,null,null,null,null,"Objective:"]
[null,null,"Address 1","ZIP","City",null,"Address 2"]
[null,null,"26125 Oldenburg"]
[null,null,"26121 Oldenburg"]
[null,null,"28195 Bremen"]
[null,null,"26180 Rastede"]
[null,null,"22119 Hamburg"]
[null,null,"26160 Bad Zwischenahn"]
[null,null,"10674 Berlin"]
[]
[]
[]
[]
[2,"Erkenntnisse:"]
[2,null,"Platz für Notizen!"]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[1,"Lessons learned:"]
[1,null,"Make your notes here!"]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[]
[]
[]
[0]

{"sheet":"Text (2, A)","index":6,"n_rows":53,"n_cols":10,"rows_kept":53,"cut":false,"hidden":false,"merged":[[26,2,27,7],[1,1,1,4],[2,1,2,4],[4,2,12,7],[14,2,15,7],[16,2,24,7]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"Text (2, E)",null,null,null,"Back to Start"]
[2,"Text (2, A)",null,null,null,"Startseite"]
[]
[2,"Übung","Finden Sie unter Benutzung der Excel-Hilfe heraus, wie Sie Vorname und Nachname mit Hilfe der Funktionen \n   LINKS()\n   RECHTS()\n   LÄNGE()\n   FINDEN()\n   SUCHEN()\n   TEIL()\ntrennen können."]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2,null,"Mit Hilfe des &-Operators können Sie Zellinhalte (z.B. Vorname und Nachname) zusammenfügen (\"Employee 2\")."]
[2]
[1,"Exercise","Use the Excel help to find out how to separate the first name and the last name using the functions\n   LEFT()\n   RIGHT()\n   LEN()\n   FIND()\n   SEARCH()\n   MID()"]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1,null,"Using the & operator you can join the content of different cells (e.g. first name and last name)."]
[1]
[]
[2,null,"Option 1:",null,null,null,null,null,"Ziel:"]
[1,null,"Option 1:",null,null,null,null,null,"Objective:"]
[]
[null,null,"Employee 1","First name","Last name",null,"Employee 2"]
[null,null,"Paul Müller"]
[null,null,"Petra Schmidt"]
[null,null,"Werner Oberhausen"]
[null,null,"Heinz Krause"]
[null,null,"Kevin Lehmann"]
[null,null,"Marie Schulze"]
[]
[null,null,"Option 2:"]
[]
[null,null,"Employee 1","First name","Last name",null,"Employee 2"]
[null,null,"Müller, Paul"]
[null,null,"Schmidt, Petra"]
[null,null,"Oberhausen, Werner"]
[null,null,"Krause, Heinz"]
[null,null,"Lehmann, Kevin"]
[null,null,"Schulze, Maria"]
[]
[]
[]
[0]

{"sheet":"Text (3, A)","index":7,"n_rows":30,"n_cols":6,"rows_kept":30,"cut":false,"hidden":false,"merged":[[17,2,17,8],[19,2,23,8],[1,1,1,4],[2,1,2,4],[4,2,5,8],[7,2,7,8],[9,2,13,8],[14,2,15,8]]}
[null,null,null,null,null,0]
[1,"Text (3, E)",null,null,null,"Back to Start"]
[2,"Text (3, A)",null,null,null,"Startseite"]
[]
[2,"Übung","Die Formel ZELLE(\"dateiname\";$A$1) liefert Ihnen als Ergebnis den vollständigen Pfad der akuellen Datei + den Dateinamen + den Blattnamen im folgenden Format:"]
[2]
[2]
[2,null,{"$f":"CELL(\"dateiname\",$A$1)","v":"E:\\000as\\Lehre_1\\109_Office\\Excel\\[Excel_201_Formulas_E_V230330_NEU.xlsm]Text (3, A)"}]
[2]
[2,null,"Benutzen Sie die Formeln LINKS(), RECHTS(), LÄNGE(), FINDEN() und TEIL(), um daraus\n   -  den Pfad\n   -  den Dateinamen\n   -  den Blattnamen\nzu ermitteln!"]
[2]
[2]
[2]
[2]
[1,"Exercise","The formula CELL(\"filename\";$A$1) gives you the complete path, the file name and the sheet name in the following format:"]
[1]
[1]
[1,null,{"$f":"CELL(\"dateiname\",$A$1)","v":"E:\\000as\\Lehre_1\\109_Office\\Excel\\[Excel_201_Formulas_E_V230330_NEU.xlsm]Text (3, A)"}]
[1]
[1,null,"Use the formulas LEFT(), RIGHT, LEN(), FIND() and MID() to find\n   - the path\n   - the file name\n   - the sheet name!"]
[1]
[1]
[1]
[1]
[]
[]
[]
[]
[]
[0]

{"sheet":"Datum & Zeit (1)","index":8,"n_rows":84,"n_cols":9,"rows_kept":84,"cut":false,"hidden":false,"merged":[[44,2,45,4],[46,2,47,4],[64,2,70,4],[72,2,77,4],[1,1,1,4],[2,1,2,4],[31,2,40,2],[31,3,40,3],[31,4,40,4],[21,2,30,2],[21,3,30,3],[21,4,30,4],[8,3,8,6],[12,3,12,6]]}
[null,null,null,null,null,null,null,null,0]
[1,"Date & Time (1)",null,null,null,"Back to Start"]
[2,"Datum & Zeit (1)",null,null,null,"Startseite"]
[]
[null,"Problem:"]
[2,null,"Bei der Erfassung von Uhrzeiten werden diese im Format hhmmss eingegeben."]
[2,null,"Diese Eingabe soll in eine \"richtige\" Excel-Uhrzeit (hh:mm:ss) umgewandelt werden."]
[2]
[2,null,"Formel:","=ZEIT(WERT(TEIL(C17;1;2));WERT(TEIL(C17;3;2));WERT(TEIL(C17;5;2)))"]
[1,null,"Time values are gathered by entering them in the format hhmmss."]
[1,null,"This input shall be transformed into a \"real\" Excel time (hh:mm:ss)."]
[1]
[1,null,"Formula:","=TIME(VALUE(MID(C17,1,2)),VALUE(MID(C17,3,2)),VALUE(MID(C17,5,2)))"]
[]
[2,null,"Eingabe","Umwandlung","Wertekopie"]
[1,null,"Input","Transformation","Paste Values"]
[null,null,231245,{"$f":"TIME(VALUE(MID(C17,1,2)),VALUE(MID(C17,3,2)),VALUE(MID(C17,5,2)))","v":0.96718749999999998},0.96718749999999998]
[null,null,"093455",{"$f":"TIME(VALUE(MID(C18,1,2)),VALUE(MID(C18,3,2)),VALUE(MID(C18,5,2)))","v":0.39924768518518516},0.39924768518518516]
[null,null,"090807",{"$f":"TIME(VALUE(MID(C19,1,2)),VALUE(MID(C19,3,2)),VALUE(MID(C19,5,2)))","v":0.38063657407407409},0.38063657407407409]
[null,null,"231435",{"$f":"TIME(VALUE(MID(C20,1,2)),VALUE(MID(C20,3,2)),VALUE(MID(C20,5,2)))","v":0.96846064814814825},0.96846064814814825]
[]
[2,"Lösung:","Diese Spalte dient der Dateneingabe. \n\nAchtung: Sie muß als \"Text\" und nicht als \"Zahl\" oder \"Standard\" formatiert sein. Ansonsten werden führende Nullen ignoriert.","Diese Spalte enthält eine Formel, die die sechs aufeinanderfolgenden Ziffern aus Spalte C in eine Zeitangabe umwandelt. Sie muß entsprechend formatiert sein (START / \"Zahl\" / \"Uhrzeit\")","Diese Spalte entsteht als Wertekopie der Spalte D: \n\nDazu die Werte in Spalte D markieren, Strg-C drücken, Klick in E17, START / \"Einfügen\" / \"Werte\" auswählen ! \n\nJetzt können Sie ggf. die Spalten C und D löschen"]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[1,"Solution:","This column is for data entry.\n\nAttention: It must be formatted as \"Text\" and not as \"Number\" or \"General\"!\n\nOtherwise the leading zeros would be ignored!","This column contains a formula, which transforms the six digits from column C into an Excel time value.\n\nIt must be formatted accordingly (HOME / \"Number\"/ \"Time\")","This column is filled by \"Paste Values\":\n\nMark the values in column D, press Ctrl-C, click into E17, select HOME / \"Paste\" / \"Values\".\n\nIf you want, you can delete the columns C and D now."]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[]
[]
[null,"Problem:"]
[2,null,"Eine Liste mit Zeitangaben enthält unterschiedlich formatierte Zeilen. Wir brauchen zwei Spalten mit dem Datum bzw. der Zeit."]
[2]
[1,null,"A list with date and time data contains differently formatted rows. We need two columns with date and time, respectively."]
[1]
[]
[2,null,"Wir haben:","Wir brauchen:",null,null,"Spalte D:","Spalte E:"]
[1,null,"We have:","We need:",null,null,"column D:","column E:"]
[null,null,"2018-10-14T23:58:36",{"$f":"IF(MID(C52,5,1)=\"-\",DATEVALUE(LEFT(C52,10)),C52)","v":"2018-10-14T23:58:36"},{"$f":"IF(MID(C52,5,1)=\"-\",TIMEVALUE(MID(C52,12,8)),C52)","v":43387.999027777776},null,{"$f":"D52","v":43387.999027777776},{"$f":"E52","v":43387.999027777776}]
[null,null,"2018-10-15T12:58:29","2018-10-15T12:58:29",43388.540613425925,null,{"$f":"D53","v":43388.540613425925},{"$f":"E53","v":43388.540613425925}]
[null,null,"2018-10-16T17:36:26","2018-10-16T17:36:26",43389.733634259261,null,43389.733634259261,43389.733634259261]
[null,null,"2018-10-17T20:44:21","2018-10-17T20:44:21",43390.864131944443,null,43390.864131944443,43390.864131944443]
[null,null,"2018-10-18 09:06:05.050","2018-10-18","09:06:05",null,43391,0.37922453703703707]
[null,null,"2018-10-19T14:27:33","2018-10-19T14:27:33",43392.602465277778,null,43392.602465277778,43392.602465277778]
[null,null,"2018-10-20T21:37:48","2018-10-20T21:37:48",43393.901250000003,null,43393.901250000003,43393.901250000003]
[null,null,"2018-10-21T08:13:59","2018-10-21T08:13:59",43394.343043981484,null,43394.343043981484,43394.343043981484]
[null,null,"2018-10-22T22:41:49","2018-10-22T22:41:49",43395.945706018516,null,43395.945706018516,43395.945706018516]
[null,null,"2018-10-23T08:09:03","2018-10-23T08:09:03",43396.339618055557,null,43396.339618055557,43396.339618055557]
[null,null,"2018-10-24 23:12:09.057","2018-10-24","23:12:09",null,43397,0.96677083333333336]
[null,null,"2018-10-25 17:23:43.057","2018-10-25","17:23:43",null,43398,0.72480324074074076]
[]
[2,"Lösung:","Zunächst erkennt eine WENN()-Formel, ob es sich beim Wert in Spalte C um die \"Langform\" handelt (mit einem Minuszeichen an fünfter Position). Wenn nicht, wird der Inhalt von Spalte C unverändert in die Spalten D und E übernommen. Wenn ja, werden die Formeln LINKS() und TEIL() benutzt, um das Datum bzw. die Zeit aus dem Wert in Spalte C \"herauszuschneiden\". Dann wandeln DATWERT() bzw. ZEITWERT() diese in Zahlen um (siehe Spalte G bzw. H). Diese werden dann mit Hilfe der entsprechenden Zellformatierung als Datum bzw. Zeit dargestellt (siehe Spalten D und E)."]
[2]
[2]
[2]
[2]
[2]
[2]
[]
[1,"Solution:","First an IF() formula recognizes if the value in column C has the \"long form\" (with a minus sign at position five). If not, the value from column C is taken unchanged into columns D and E. If yes, the formulas LEFT() and MID() are used to \"cut\" the date and the time out of the value in column C. Then the formulas DATEVALUE() and TIMEVALUE() are used to convert them into numbers (see columns G and H). Finally, they are displayed as date and time using the corresponding cell format (see columns D and E)."]
[1]
[1]
[1]
[1]
[1]
[]
[]
[]
[]
[]
[0]

{"sheet":"Datum & Zeit (2, A)","index":9,"n_rows":54,"n_cols":14,"rows_kept":54,"cut":false,"hidden":false,"merged":[[28,2,33,8],[1,1,1,4],[2,1,2,4],[5,2,5,9],[7,2,7,9],[22,2,27,8]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"Date & Time (2, E)",null,null,null,"Back to Start"]
[2,"Datum & Zeit (2, A)",null,null,null,"Startseite"]
[]
[2,"Übung"]
[2,null,"Sehr geehrter Herr Professor Stern,\nich befinde mich im Praktikum und bin hier auf eine Situation gestoßen, wo eine Excelfunktion sehr hilfreich sein koennte. Leider komme ich alleine nicht weiter. Ich muss von zwei Spalten, die Uhrzeit und Tag anzeigen, die Differenz kalkulieren, da das die Waiting Time ist (von Hafenankunft bis Loading). Ich weiss, dass Excel das ausrechnen kann, wenn es die Zeit als timevalue hat. Allerdings schaffe ich es nicht, unsere Spalten in eine serial number umzuwandeln, da es als hhmm/dd eingegeben wird, z.B. “1047/19”, was fuer 10:47 Uhr morgens am 19. steht. Unter Format Cells finde ich leider keine TimeDate Formatierung. Koennen Sie mir weiter helfen?\nFreundliche Gruesse aus Kanada"]
[1,"Exercise"]
[1,null,"Dear Professor Stern,\nI'm in an internship and encountered a situation here where an Excel function could be very helpful. Unfortunately, I'm not able to solve the problem by myself. I have to calculate the difference (=waiting time) from two columns displaying the time and day of the arrival in the port and the beginning of loading. I know that Excel can calculate that if it has the time as a time value. However, I am not able to convert our columns into a serial number, since it is entered as hhmm/dd - for example \"1047/19\", which is for 10:47 a.m. on the 19th. Unfortunately, I can not find a corresponding date&time format under Format Cells. Can you help me?\nGreetings from Canada\n"]
[]
[2,"einfache Version:",null,"beide Zeitangaben am selben Tag"]
[1,"simple version:",null,"both time values on the same day"]
[2,null,null,null,null,null,null,null,null,null,"Ziel:"]
[1,null,null,null,null,null,null,null,null,null,"Objective:"]
[null,null,null,"arrival","loading",null,"arrival","loading","waiting"]
[null,null,null,"1000/19","1245/19"]
[null,null,null,"1047/19","1350/19"]
[null,null,null,"1255/19","1430/19"]
[null,null,null,"1430/19","1845/19"]
[]
[2,"erweiterte Version:",null,"Zeitangaben an verschiedenen Tagen"]
[1,"advanced version:",null,"time values on different days"]
[]
[2,"Achtung","Hier arbeiten wir NICHT mit ZeitFORMATEN! Stattdessen behandeln wir die Tage, die Stunden und die Minuten einzeln als normale Zahlen. Das Ergebnis in Spalte N sieht mit den Doppelpunkten zwischen den Zahlen wie ein Zeitformat aus. Es wurde aber aus einzelnen Zahlen und Doppelpunkten mittels &-Operator zusammengesetzt!\nDie Funktion TEXT(... ; \"00\") sorgt dabei dafür, dass einstellige Zahlen mit führender Null dargestellt werden (\"3\" -> \"03\")."]
[2]
[2]
[2]
[2]
[2]
[1,"Attention","Here we do NOT work with time FORMATS! Instead of, he handle the days, hours and minutes individually as normal numbers. The result in column N looks like a time format - but it was constructed from numbers and double points using the & operator!\nUsing the function TEXT(... , \"00\") ensures, that single-digit numers are displayed with a preceeding zero (\"3\" -> \"03\")"]
[1]
[1]
[1]
[1]
[1]
[]
[null,null,null,"arrival","loading",null,"hours \nfirst day","hours between","hours second day","waiting","days","rest (number)","rest (hours:min)","dd:hh:mm"]
[null,null,null,"1000/19","1245/20"]
[null,null,null,"1047/19","1350/21"]
[null,null,null,"1255/19","1430/21"]
[null,null,null,"1430/19","1845/22"]
[]
[2,null,null,null,"Ziel:"]
[1,null,null,null,"Objective:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[0]

{"sheet":"WENN (1, A)","index":10,"n_rows":47,"n_cols":10,"rows_kept":47,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"IF (1, E)",null,null,null,"Back to Start"]
[2,"WENN (1, A)",null,null,null,"Startseite"]
[]
[null,"normal:"]
[null,null,"price","number","total price",null,"total price","number","price"]
[null,null,5,4,{"$f":"C7*D7","v":20},null,20,4,{"$f":"G7/H7","v":5}]
[null,null,12,null,{"$f":"C8*D8","v":0},null,null,null,{"$f":"G8/H8","v":{"$error":"#DIV/0!"}}]
[null,null,7,3,21,null,21,3,7]
[null,null,8,5,40,null,40,5,8]
[null,null,16,0,0,null,48,0,{"$error":"#DIV/0!"}]
[]
[2,"mit WENN-Funktion:",null,null,null,null,null,null,null,"Ziel:"]
[1,"with IF function:",null,null,null,null,null,null,null,"Objective:"]
[]
[null,null,"price","number","total price",null,"total price","number","price"]
[null,null,5,4,null,null,20,4]
[null,null,12]
[null,null,7,3,null,null,21,3]
[null,null,8,5,null,null,40,5]
[null,null,16,0,null,null,48,0]
[]
[]
[2,"Varianten der WENN-Funktion (in Zelle E17):"]
[2,null,"WENN(D17=0;\"\";C17*D17)"]
[2,null,"WENN(D17=\"\";\"\";C17*D17)"]
[2,null,"WENN(D17<>0;C17*D17;\"\")"]
[2,null,"WENN(D17<>\"\";C17*D17;\"\")",null,null,null,"<>\"\" bedeutet \"ungleich nichts\" - also: \"etwas\"!"]
[2,null,"WENN(ODER(D17=0;D17=\"\");\"\";C17*D17)",null,null,null,"ODER(D17=0;D17=\"\") bedeutet: D17=0 ODER D17=\"\""]
[2,null,"WENN(UND(D17<>\"\";D17<>0);C17*D17;\"\")"]
[2]
[2,"weitere Möglichkeiten:","WENN(ISTFEHLER(G17/H17);\"\";G17/H17)",null,null,null,"Hiermit wird auch berücksichtigt, dass der Nutzer"]
[2,null,"WENNFEHLER(G17/H17;\"\")",null,null,null,"evtl. keine Zahl, sondern einen Text eingibt!"]
[]
[1,"alternatives of the IF function (in cell E17):"]
[1,null,"IF(D17=0;\"\";C17*D17)"]
[1,null,"IF(D17=\"\";\"\";C17*D17)"]
[1,null,"IF(D17<>0;C17*D17;\"\")"]
[1,null,"IF(D17<>\"\";C17*D17;\"\")",null,null,null,"<>\"\" means \"unlike nothing\" - so: \"something\"!"]
[1,null,"IF(OR(D17=0;D17=\"\");\"\";C17*D17)",null,null,null,"OR(D17=0;D17=\"\") means: D17=0 OR D17=\"\""]
[1,null,"IF(AND(D17<>\"\";D17<>0);C17*D17;\"\")"]
[1]
[1,"other possibilities:","IF(ISERROR(G17/H17);\"\";G17/H17)",null,null,null,"This takes also into consideration, that the user may"]
[1,null,"IFERROR(G17/H17;\"\")",null,null,null,"enter a text instead of a number!"]
[]
[]
[0]

{"sheet":"WENN (2, A)","index":11,"n_rows":111,"n_cols":10,"rows_kept":111,"cut":false,"hidden":false,"merged":[[43,2,46,7],[48,2,52,7],[1,1,1,4],[2,1,2,4],[4,2,17,8],[19,3,22,8],[24,3,28,8],[30,2,41,8],[101,3,101,8],[102,3,102,8],[103,3,103,8],[95,3,95,8],[96,3,96,8],[97,3,97,8],[98,3,98,8],[100,3,100,8]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"IF (2, E)",null,null,null,"Back to Start"]
[2,"WENN (2, A)",null,null,null,"Startseite"]
[]
[2,"Das Problem:","Guten Morgen Herr Stern,\nich verbinge meine Zeit momentan damit, Quellen und Grundlagen für meine BA zu ermitteln. Nun ist mir eine Excel-Aufstellung in die Hände gefallen, welche durchaus Potential hat, aber leider etwas düsselig aufgebaut ist - zumindest für meine Zwecke.\nEs handelt sich um eine Aufstellung, welche (in einem Zeitraum von 2012 bis 2015) 11991 Positionen umfasst. Jede Zeile beinhaltet umfassende Informationen, nur das Datum wird von Tag zu Tag lediglich als Überschrift mehrerer Zeilen aufgeführt. Nun würde ich gerne das jeweilige Datum in die Zeilen übertragen. Da die tägliche Anzahl an Positionen varriert, komme ich bisher mit keiner Formel zum gewünschten Ergebnis \n\nIch würde mich freuen, wenn Sie Zeit und Lust hätten, mir bei diesem Problem behilflich zu sein und somit schon mal ein kleines Aufwärmprogramm für das nächste Semester zu absolvieren :)\n\nViele Grüße\n..."]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2,"Lösungshinweis 1:",null,"Versuchen Sie nicht, die Datumswerte sofort in der gewünschten Spalte D zu erzeugen. \nFinden Sie eine WENN-Formel, die die Werte DANEBEN in Spalte C erzeugt!"]
[2]
[2]
[2]
[2]
[2,"Lösungshinweis 2:",null,"Für eine Zelle in Spalte C gilt:\nWENN rechts daneben in Spalte D etwas steht, ...\nDANN ...\nSONST …"]
[2]
[2]
[2]
[2]
[]
[1,"The problem:","Good morning Mr. Stern,\nat the moment I'm collecting material for my bachelor thesis. Recently I found some Excel data which may be very useful - but they are unfortunately in a bad format, at least for my purposes.\nThe list contains 11.991 positions for the period from 2012 to 2015. Unfortunately the date is only displayed once for several rows. My objective is to write the date in each row - but up to now I was not successful, because the number of rows varies from day to day and I'm unable to find an appropriate formula.\n\nI would be very happy if you could help me with this problem. Surely, for you it is only a little warm up for the next semester :)\n\nBest regards\n..."]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1,"Hint 1:","Don't try to generate the dates immediatly in the column D.\n\nInstead of, find an IF formula, which generates the values in column C!"]
[1]
[1]
[1]
[1]
[1,"Hint 2:","For a cell in column C it applies:\nIF there is something in column D right to it, …\nTHEN …\nELSE …"]
[1]
[1]
[1]
[1]
[]
[2,null,null,null,null,null,null,"Ziel:"]
[1,null,null,null,null,null,null,"Objective:"]
[null,null,null,"Date","Product","Weight in kg"]
[null,null,null,"2014-01-01","A",25]
[null,null,null,null,"C",17]
[null,null,null,null,"B",21]
[null,null,null,"2014-01-02","B",5]
[null,null,null,null,"D",1]
[null,null,null,null,"A",5]
[null,null,null,null,"C",6]
[null,null,null,null,"B",8]
[null,null,null,"2014-01-03","D",7]
[null,null,null,null,"A",51]
[null,null,null,null,"A",23]
[null,null,null,null,"B",65]
[null,null,null,null,"C",47]
[null,null,null,null,"C",58]
[null,null,null,"2014-01-04","D",65]
[null,null,null,null,"D",22]
[null,null,null,null,"C",21]
[null,null,null,null,"B",14]
[null,null,null,null,"A",13]
[null,null,null,null,"A",31]
[null,null,null,null,"D",21]
[null,null,null,"2014-01-05","D",22]
[null,null,null,null,"C",23]
[null,null,null,null,"B",25]
[null,null,null,null,"D",26]
[null,null,null,null,"B",29]
[null,null,null,null,"C",27]
[null,null,null,null,"A",41]
[null,null,null,"2014-01-06","D",14]
[null,null,null,null,"C",12]
[null,null,null,null,"D",21]
[null,null,null,null,"A",23]
[null,null,null,null,"D",25]
[null,null,null,null,"C",26]
[null,null,null,null,"B",27]
[null,null,null,null,"D",22]
[]
[]
[2,"WICHTIG:","(1)","Formeln relativ lesen zu der Zelle, in der sie stehen"]
[2,null,"(2)","Hilfsspalten / -zeilen / -zellen benutzen"]
[2,null,"(3)","Wertekopie machen (Start / \"Einfügen\" / \" Werte einfügen\")"]
[2,null,"(4)","Gewünschtes Ergebnis ohne Formel einfach hinschreiben"]
[]
[1,"IMPORTANT:","(1)","Read a formula relatively to the cell, which contains it "]
[1,null,"(2)","Use auxiliary cells / rows / columns"]
[1,null,"(3)","Paste values (Home / \"Paste\" / \"Paste values\")"]
[1,null,"(4)","Write down the desired result without a formula"]
[]
[]
[]
[]
[]
[]
[0]

{"sheet":"Bevor Sie weitermachen … (2)","index":12,"n_rows":22,"n_cols":10,"rows_kept":22,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4],[4,2,7,9],[8,2,11,9]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"Before you continue … (2)",null,null,null,"Back to Start"]
[2,"Bevor Sie weitermachen … (2)",null,null,null,"Startseite"]
[]
[2,null,"… arbeiten Sie bitte in der Datei \"Excel-Basics\" die Blätter \n   \"Absoluter Zellbezug\" und\n   \"Bedingte Formatierung 1\"\ndurch!"]
[2]
[2]
[2]
[1,null,"… please work through the worksheets \n   \"Absolute cell reference\" and\n   \"Simple cond. format.\"\nin the file \"Excel-Basics\"!"]
[1]
[1]
[1]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[0]

{"sheet":"WENN (3, A)","index":13,"n_rows":75,"n_cols":17,"rows_kept":75,"cut":false,"hidden":false,"merged":[[66,2,66,5],[68,2,68,5],[12,3,12,7],[13,3,13,7],[51,2,51,6],[55,2,55,5],[57,2,57,5],[62,2,62,6],[11,3,11,7],[1,1,1,4],[2,1,2,4],[8,3,8,7],[9,3,9,7],[10,3,10,7]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"IF (3, E)",null,null,null,"Back to Start"]
[2,"WENN (3, A)",null,null,null,"Startseite"]
[]
[null,null,"IF","turnover >",20000,"THEN","A-customer",null,null,"IF","ZIP code","from",40000,"to",50000,"THEN","range A"]
[null,null,"IF","turnover >",10000,"THEN","B-customer",null,null,"IF","ZIP code","from",30000,"to",39999,"THEN","range B"]
[null,null,"IF","turnover <=",10000,"THEN","C-customer",null,null,"IF","ZIP code","from",20000,"to",29999,"THEN","range C"]
[null,null,null,null,null,null,null,null,null,"IF","ZIP code","from",10000,"to",19999,"THEN","range D"]
[2,"Achtung","(1)","Parameter in separaten Zellen (nicht \"turnover > 20.000€\" in einer Zelle)"]
[2,null,"(2)","bedingte Formatierung der Ergebniszellen"]
[2,null,"(3)","Keine Zahlen in Formeln! (auch nicht in bedingter Formatierung!)"]
[1,"Attention","(1)","Parameters in separate cells (not \"turnover > 20.000€\" in one cell)"]
[1,null,"(2)","Conditional formatting of the result cells"]
[1,null,"(3)","No numbers in formulas!! (also not in the conditional formatting!)"]
[]
[2,"mit geschachtelter WENN-Funktion (\"Dreifach-Alternative\"):",null,null,null,null,null,null,null,"Übung:",null,null,null,null,null,null,"Ziel:"]
[1,"with a nested IF function (\"Threefold alternative\")",null,null,null,null,null,null,null,"Exercise:",null,null,null,null,null,null,"Objective:"]
[]
[null,null,"last name","first name","turnover","A/B/C (V.1)",null,"A/B/C (V.2)",null,"last name","first name","ZIP code","range (v.1)",null,"range (v.2)"]
[null,null,"Krüger","Paula",29962.140619754791,{"$f":"IF(E20>$E$5,$G$5,IF(E20>$E$6,$G$6,$G$7))","v":"A-customer"},null,{"$f":"IF(E20>$E$5,$G$5,IF(AND(E20>$E$6,E20<=$E$5),$G$6,$G$7))","v":"A-customer"},null,"Krüger","Klaus",44388]
[null,null,"Krüger","Frieda",29782.33277797699,{"$f":"IF(E21>$E$5,$G$5,IF(E21>$E$6,$G$6,$G$7))","v":"A-customer"},null,{"$f":"IF(E21>$E$5,$G$5,IF(AND(E21>$E$6,E21<=$E$5),$G$6,$G$7))","v":"A-customer"},null,"Krüger","Otto",39999]
[null,null,"Schulz","Hermann",29237.877130508423,"A-customer",null,"A-customer",null,"Krüger","Paula",38617]
[null,null,"Mayer","Frieda",28885.57642698288,"A-customer",null,"A-customer",null,"Krüger","Udo",38371]
[null,null,"Meier","Paula",28740.875720977783,"A-customer",null,"A-customer",null,"Krüger","Paula",28926]
[null,null,"Scholz","Hermann",27290.624678134918,"A-customer",null,"A-customer",null,"Krüger","Frieda",19999]
[null,null,"Krüger","Paula",26079.535782337189,"A-customer",null,"A-customer",null,"Mayer","Klaus",49709]
[null,null,"Schulze","Frieda",24446.776807308197,"A-customer",null,"A-customer",null,"Mayer","Meike",45443]
[null,null,"Meier","Marianne",23456.640839576721,"A-customer",null,"A-customer",null,"Mayer","Frieda",26654]
[null,null,"Krüger","Otto",22946.478724479675,"A-customer",null,"A-customer",null,"Mayer","Otto",17169]
[null,null,"Krüger","Udo",22082.822322845459,"A-customer",null,"A-customer",null,"Mayer","Hermann",15672]
[null,null,"Neumann","Frieda",20819.504857063293,"A-customer",null,"A-customer",null,"Mayer","Paula",12558]
[null,null,"Neumann","Hermann",20739.139914512634,"A-customer",null,"A-customer",null,"Meier","Hermann",24981]
[null,null,"Krüger","Klaus",18777.40353345871,"B-customer",null,"B-customer",null,"Meier","Paula",23222]
[null,null,"Scholz","Anna",18178.645074367523,"B-customer",null,"B-customer",null,"Meier","Marianne",18258]
[null,null,"Mayer","Klaus",14496.34462594986,"B-customer",null,"B-customer",null,"Müller","Paula",44262]
[null,null,"Müller","Paula",12380.302250385284,"B-customer",null,"B-customer",null,"Müller","Paula",20378]
[null,null,"Scholz","Paula",11766.605377197266,"B-customer",null,"B-customer",null,"Neumann","Frieda",49566]
[null,null,"Neumann","Meike",10869.558453559875,"B-customer",null,"B-customer",null,"Neumann","Meike",45645]
[null,null,"Meier","Hermann",10119.512975215912,"B-customer",null,"B-customer",null,"Neumann","Frieda",31133]
[null,null,"Schulze","Klaus",10089.181661605835,"B-customer",null,"B-customer",null,"Neumann","Hermann",21047]
[null,null,"Neumann","Frieda",9598.7382531166077,"C-customer",null,"C-customer",null,"Scholz","Paula",38645]
[null,null,"Scholz","Frieda",9279.2469263076782,"C-customer",null,"C-customer",null,"Scholz","Anna",29999]
[null,null,"Mayer","Otto",8682.6342344284058,"C-customer",null,"C-customer",null,"Scholz","Frieda",21868]
[null,null,"Mayer","Hermann",8581.1111330986023,"C-customer",null,"C-customer",null,"Scholz","Hermann",12440]
[null,null,"Schulze","Anna",8187.6742839813232,"C-customer",null,"C-customer",null,"Schulz","Hermann",28411]
[null,null,"Mayer","Paula",7679.048478603363,"C-customer",null,"C-customer",null,"Schulze","Anna",44113]
[null,null,"Mayer","Meike",6927.9241561889648,"C-customer",null,"C-customer",null,"Schulze","Klaus",30303]
[null,null,"Schulze","Marianne",6516.1827206611633,"C-customer",null,"C-customer",null,"Schulze","Marianne",25346]
[null,null,"Müller","Paula",6408.5009694099426,"C-customer",null,"C-customer",null,"Schulze","Frieda",21100]
[]
[]
[2,"Variante 1:","=WENN(E20>$E$5;$G$5;WENN(E20>$E$6;$G$6;$G$7))",null,null,null,null,"Finden Sie heraus,"]
[2,null,null,null,null,null,null,"wie Variante 2"]
[2,null,"z.B. E20 = 17.000€",null,null,null,null,"funktioniert!"]
[2]
[2,null,"äußeres WENN: 17.000€ > 20.000€ ?"]
[2,null,"-> Nein!"]
[2,null,"-> \"Sonst\"-Zweig der äußeren WENN-Formel"]
[2]
[2,null,"inneres WENN: 17.000€ > 10.000€ ?"]
[2,null,"-> Ja!"]
[2,null,"-> \"Dann\"-Zweig der inneren WENN-Formel!"]
[1,"Variant 1:","=IF(E20>$E$5;$G$5;IF(E20>$E$6;$G$6;$G$7))",null,null,null,null,"Find out, "]
[1,null,null,null,null,null,null,"how variant 2"]
[1,null,"e.g.: E20 = 17.000€",null,null,null,null,"works!"]
[1]
[1,null,"outer IF: 17.000€ > 20.000€ ?"]
[1,null,"-> No!"]
[1,null,"-> \"Else\"-branch of the outer IF formula"]
[1]
[1,null,"inner IF: 17.000€ > 10.000€ ?"]
[1,null,"-> Yes!"]
[1,null,"-> \"Then\"-branch of the inner IF formula!"]
[]
[0]

{"sheet":"Bevor Sie weitermachen … (3)","index":14,"n_rows":22,"n_cols":10,"rows_kept":22,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4],[4,2,7,9],[8,2,11,9]]}
[null,null,null,null,null,null,null,null,null,0]
[1,"Before you continue … (3)",null,null,null,"Back to Start"]
[2,"Bevor Sie weitermachen … (3)",null,null,null,"Startseite"]
[]
[2,null,"… arbeiten Sie bitte in der Datei \"Excel-Basics\" das Blatt \n   \"Bedingte Formatierung 2\"\ndurch!"]
[2]
[2]
[2]
[1,null,"… please work through the worksheet \n   \"Advanced cond. format.\"\nin the file \"Excel-Basics\"!"]
[1]
[1]
[1]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[0]

{"sheet":"WENN (4, A)","index":15,"n_rows":50,"n_cols":7,"rows_kept":50,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4],[5,2,11,8],[12,2,16,8]]}
[null,null,null,null,null,0]
[1,"IF (4, E)",null,null,null,"Back to Start"]
[2,"WENN (4, A)",null,null,null,"Startseite"]
[]
[]
[2,"Das Problem:","Hallo Herr Stern!\nSie haben ja in der Vorlesung gesagt, wir können uns mit unseren Excel-Problemchen an Sie wenden. Also:\nIch habe eine Liste mit täglichen Einnahmen und Ausgaben. Es können pro Tag unterschiedlich viele sein. Aus der Differenz ergibt sich in jeder Zeile ein Plus- oder Minus-Betrag. Nun möchte ich aber die tägliche Bilanz haben - also die Summe aller Plus- und Minusbeträge pro Tag. Wie könnte ich das hinkriegen?"]
[2]
[2]
[2]
[2]
[2]
[2]
[1,"The problem:","Hi Mr. Stern!\nIn your lecture you've said, that we can ask you in case of an Excel problem. Well:\nI have a list with daily receipts and expenses. There can be a different number of them for each day. The difference gives either a plus or a minus value per row. Now I want to have a daily balance - that means the sum of all plus and minus value per day. How can I do that?"]
[1]
[1]
[1]
[1]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"date","receipts","expenses","difference","total"]
[null,null,"2018-09-12",107,98,{"$f":"D37-E37","v":9}]
[null,null,"2018-09-12",149,123,{"$f":"D38-E38","v":26}]
[null,null,"2018-09-12",127,136,-9]
[null,null,"2018-09-12",176,80,96]
[null,null,"2018-09-13",186,154,32]
[null,null,"2018-09-13",191,141,50]
[null,null,"2018-09-13",140,162,-22]
[null,null,"2018-09-14",177,163,14]
[null,null,"2018-09-14",147,105,42]
[null,null,"2018-09-14",179,155,24]
[null,null,"2018-09-14",188,83,105]
[null,null,"2018-09-14",199,112,87]
[]
[0]

{"sheet":"WENN (5, A)","index":16,"n_rows":82,"n_cols":15,"rows_kept":82,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4],[4,1,28,5],[4,7,6,8],[30,1,51,5],[30,7,32,8]]}
[null,null,null,null,null,0]
[1,"IF (5, E)",null,null,null,"Back to Start"]
[2,"WENN (5, A)",null,null,null,"Startseite"]
[]
[2,"Guten Tag Herr Stern,\n\naktuell mache ich ein Praktikum bei einem Logistik Unternehmen und ich habe gerade ein Problem mit einer Formel - und zwar:\n\nIch habe eine Excel-Tabelle mit Kundendaten. Darin stehen für denselben Kunden (dieselbe Adresse) unterschiedliche Namen. Es ist aber derselbe Kunde.\nZum Beispiel \"Iroquois Gas Transmission Systems\", \"Iroquois Gas Transmission System\" und \"Iroquois Gas Transmission Syst\".\n\nIch versuche gerade eine Hilfsspalte \"Unique company name\" zu bauen, wo mit Hilfe einer Formel für alle unterschiedlichen Namen eines Kunden immer ein und derselbe Name genommen wird.\nIch möchte nämlich mit diesen Daten eine Pivot-Tabelle anlegen und dann hätte ich denselben Kunden mehrfach darin.\n \nAnbei finden Sie einen Teil meiner Tabelle.\nVielleicht haben Sie eine Lösungshilfe für mich?\n\nVielen Dank im Voraus!\n",null,null,null,null,"Achtung","Wegen des Problems unterschiedlicher Schreibweisen siehe auch \"Schreibfehler\" in der Excel-Übungsdatei \"DataQuality\"!"]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[]
[1,"Good afternoon Mr Stern,\n\nI am currently doing an internship with a logistics company and I have a problem with a formula - and that is:\n\nI have an Excel table with customer data. It contains different names for the same customer (same address). But it is the same customer.\nFor example \"Iroquois Gas Transmission Systems\", \"Iroquois Gas Transmission System\" and \"Iroquois Gas Transmission Syst\".\n\nI am currently trying to build an auxiliary column \"Unique company name\", where a formula is used for all the different names of a customer to always use the same name.\nI want to create a pivot table with this data and then I would have the same customer in it several times.\n \nEnclosed you will find a part of my table.\nPerhaps you have a solution for me?\n\nThanks in advance!",null,null,null,null,"Attention","Because of the problem of different spellings see also \"Misspelling\" in the Excel exercise file \"DataQuality\"!"]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[]
[]
[null,null,null,null,null,null,null,"Company Name","Address","City","Company Contact","Contact Title","Employees","Industry","Order date"]
[null,null,null,null,null,null,null,"Nardella's Turf Care Inc","276 Meadow St","Branford","Anthony Nardella","Owner",6,"Agricultural Services","2017-10-21"]
[null,null,null,null,null,null,null,"Morris Foundation Women & Children","79 Beacon St","Waterbury","Joan Huskins","Manager",13,"Membership Organizations","2017-11-16"]
[null,null,null,null,null,null,null,"Morris Foundation Women & Chil","79 Beacon St","Waterbury","Joan Huskins","Manager",13,"Membership Organizations","2017-11-17"]
[null,null,null,null,null,null,null,"R A Brooks Trucking Co","PO Box 17722","Little Rock","Charles Brooks","Owner",24,"Motor Freight Transportation","2017-11-26"]
[null,null,null,null,null,null,null,"Iroquois Gas Transmission Systems","1 Corporate Dr # 600","Shelton","Ej Holm","CEO",95,"Gas, Electric and Sanitary Services","2017-12-28"]
[null,null,null,null,null,null,null,"R A Brooks Trucking","PO Box 17722","Little Rock","Charles Brooks","Owner",24,"Motor Freight Transportation","2018-01-28"]
[null,null,null,null,null,null,null,"Northeast Thermography","10 Capitol Dr # B","Wallingford","Elliott Greenspan","Owner",11,"Publishing, Allied and Printing Industries","2018-05-23"]
[null,null,null,null,null,null,null,"Iroquois Gas Transmission Systems","1 Corporate Dr # 600","Shelton","Ej Holm","CEO",95,"Gas, Electric and Sanitary Services","2018-06-07"]
[null,null,null,null,null,null,null,"Iroquois Gas Transmission Systems","1 Corporate Dr # 600","Shelton","Ej Holm","CEO",95,"Gas, Electric and Sanitary Services","2019-01-07"]
[null,null,null,null,null,null,null,"Morris Foundation Women & Chil","79 Beacon St","Waterbury","Joan Huskins","Manager",13,"Membership Organizations","2019-01-20"]
[null,null,null,null,null,null,null,"Typing Unlimited","4154 Madison Ave","Trumbull","Carolyn Evans","President",26,"Business Services","2019-03-30"]
[null,null,null,null,null,null,null,"Christian Bernard","100 Greyrock Pl # 222","Stamford","Jennifer Collins","Manager",5,"Miscellaneous Retail","2019-07-12"]
[null,null,null,null,null,null,null,"Morris Foundation Women & Chil","79 Beacon St","Waterbury","Joan Huskins","Manager",13,"Membership Organizations","2019-08-19"]
[null,null,null,null,null,null,null,"Cognisa Security Svc","20 N Main St","Norwalk","Chris Sorgenferi","Manager",99,"Business Services","2019-08-25"]
[null,null,null,null,null,null,null,"Northeast Thermography","10 Capitol Dr # B","Wallingford","Elliott Greenspan","Owner",11,"Publishing, Allied and Printing Industries","2019-10-18"]
[null,null,null,null,null,null,null,"Northeast Thermography","10 Capitol Dr # B","Wallingford","Elliott Greenspan","Owner",11,"Publishing, Allied and Printing Industries","2019-11-24"]
[null,null,null,null,null,null,null,"Westport Twn Building Inspctr","515 Post Rd E","Westport","Steve Smith","Manager",5,"Legislative, Executive and General Government","2020-04-13"]
[null,null,null,null,null,null,null,"Iroquois Gas Transmission Syst","1 Corporate Dr # 600","Shelton","Ej Holm","CEO",95,"Gas, Electric and Sanitary Services","2020-05-23"]
[null,null,null,null,null,null,null,"Iroquois Gas Transmission System","1 Corporate Dr # 600","Shelton","Ej Holm","CEO",95,"Gas, Electric and Sanitary Services","2020-05-30"]
[]
[2,"Ziel"]
[1,"Objective"]
[]
[]
[]
[]
[0]

{"sheet":"Eine Lösung aus der Praxis","index":17,"n_rows":61,"n_cols":10,"rows_kept":61,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4],[4,2,22,7],[24,2,42,7]]}
[null,null,null,null,null,0]
[1,"A practical solution",null,null,null,"Back to Start"]
[2,"Eine Lösung aus der Praxis",null,null,null,"Startseite"]
[]
[2,null,"Sehr geehrter Herr Professor Stern,\n\nIch habe vor einem halben Jahr die Jade Hochschule abgeschlossen und unter Ihrer Betreuung meine Bachelorarbeit geschrieben. Zurzeit arbeite ich als Consultant bei ... in Bayern. Die Excel-Kenntnisse, die Sie mir während des Studium übermittelt haben, nutze ich jeden Tag und bin dafür sehr dankbar.\nBei einer Aufgabe bin ich jedoch sowohl auf meine als auch Google-Grenzen gestoßen und komme nicht weiter.\nDer Sachverhalt:\n\nEs gibt Zellen mit Text, wo Mitarbeiter ganz verschiedene Informationen zu einer Sendung hinein schreiben - Zahlen und Texte. Die Zeichen sind vermischt und oft ohne Leerzeichen etc.\nDie benötigte Formel muss erkennen, welche Zellen einen Code enthalten.\nDer Code hat das Format XXXX-XX-XXX, wobei X eine Zahl ist (z.B. 1234-12-1234)\n\nWenn Sie eine Lösung hätten, wäre es sehr schön, denn es würde mir viele Nerven und Zeit ersparen.\n\nIch hoffe, es ist noch sonnig in Elsfleth. Die Gegend vermisse ich.\n\nViele Grüße aus Bayern\nIhr ehemaliger Student ...\n"]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[2]
[]
[1,null,"Dear Professor Stern,\n\nI graduated from Jade University of Applied Sciences six months ago and wrote my Bachelor's thesis under your supervision. I am currently working as a consultant at ... in Bavaria. I use the Excel knowledge you passed on to me during my studies every day and am very grateful for it.\nHowever, in one task I have come up against both my and Google's limits and am getting nowhere.\nThe issue:\n\nThere are cells with text where staff write in quite different information about a delivery - numbers and text. The characters are mixed and often without spaces, etc.\nThe required formula must recognise which cells contain a code.\nThe code has the format XXXX-XX-XXX, where X is a number (e.g. 1234-12-1234).\n\nIf you have a solution, it would be very nice, because it would save me a lot of nerves and time.\n\nI hope it is still sunny in Elsfleth. I miss the area.\n\nMany greetings from Bavaria\nYour former student ..."]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[1]
[]
[2,null,null,"Position des Wortes \"tarif\" suchen","Position des ersten Minus rechts von \"tarif\" suchen","vier Zeichen links vom ersten Minus nach \"tarif\"","Position des zweiten Minus suchen (ab ein Zeichen rechts vom ersten Minus)","zwei Zeichen links vom zweiten Minus","vier Zeichen rechts vom zweiten Minus","Tariff Item Number"]
[1,null,null,"search the position of the word \"tarif\"","search the first minus right to \"tarif\"","four characters left to the first minus after \"tarif\"","search the second minus (starting at one character right to the first minus)","two characters left to the second minus","four characters right to the second minus","tariff item number"]
[null,null,"PLASTIC TOYS \n(GLOW IN DARK)\nHS CODE:5726.312\n1 PLT = 24 CTNS\n\nTariff Item#  1935-21-0917",{"$f":"SEARCH(\"tarif\",C48)","v":64},{"$f":"SEARCH(\"-\",C48,SEARCH(\"tarif\",C48)+1)","v":82},{"$f":"MID(C48,SEARCH(\"-\",C48,SEARCH(\"tarif\",C48)+1)-4,4)","v":"1935"},{"$f":"SEARCH(\"-\",C48,SEARCH(\"-\",C48,SEARCH(\"tarif\",C48)+1)+1)","v":85},{"$f":"MID(C48,SEARCH(\"-\",C48,SEARCH(\"-\",C48,SEARCH(\"tarif\",C48)+1)+1)-2,2)","v":"21"},{"$f":"MID(C48,SEARCH(\"-\",C48,SEARCH(\"-\",C48,SEARCH(\"tarif\",C48)+1)+1)+1,4)","v":"0917"},{"$f":"IFERROR(F48&\"-\"&H48&\"-\"&I48,\"\")","v":"1935-21-0917"}]
[null,null,"STAINLESS STEEL SINKS AND FIXING ELEMENTS\nAS PER ORDER 4501883296/22\nHS CODE:73241000\n420CTNS ON 24PLTS\n\nTARIFF ITEM NUMBER:9968-27",{"$f":"SEARCH(\"tarif\",C49)","v":106},{"$f":"FIND(\"-\",C49)","v":129},{"$f":"MID(C49,FIND(\"-\",C49)-4,4)","v":"9968"},{"$f":"FIND(\"-\",C49,FIND(\"-\",C49)+1)","v":{"$error":"#VALUE!"}},{"$f":"MID(C49,SEARCH(\"-\",C49,SEARCH(\"-\",C49,SEARCH(\"tarif\",C49)+1)+1)-2,2)","v":{"$error":"#VALUE!"}},{"$f":"MID(C49,SEARCH(\"-\",C49,SEARCH(\"-\",C49,SEARCH(\"tarif\",C49)+1)+1)+1,4)","v":{"$error":"#VALUE!"}},{"$f":"IFERROR(F49&\"-\"&H49&\"-\"&I49,\"\")","v":""}]
[null,null,"STAINLESS STEEL SINKS AND FIXING ELEMENTS\nAS PER ORDER 4501883299/22\nHS CODE:73241000\n192CTNS ON 11PLTS\n\nTARIFF ITEM NUMBER:9970-13-0077",106,129,"9970",132,"13","0077","9970-13-0077"]
[null,null,"ROAD WHEELS PARTS & ACCESSORIES THEREOF, WHEEL RIM ASSEMBLY, TRUCK & TRAILER WHEELS\nPUN294000853564\nMODEL:24.5X8.25\" HUB WHITE NOS.:432.00\nINVOICE NO:4301004126 DTD:16.08.2021\nNET WT:15,465.600 KGS\nHS-Code: 8708706060\nTRI:\n\nDELIVERY ADDRESS:\nADVANCED WHEEL SALES, LLC\n1100 SOUTH ROAD \nTALLAHASSEE FL 36344",{"$error":"#VALUE!"},201,"S\nHS",{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},""]
[null,null,"PISTON \nNCM:\n3926.90.69\n3926.90.90\n8412.90.80",{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},""]
[null,null,"Hydraulic Fittings\nNCM: 7307.99.00",{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},""]
[null,null,"PISTON \nNCM:\n3926.9069\n3926.90.90\n8412.90.80\n4016.93.00.",{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},""]
[]
[]
[]
[]
[]
[]
[0]

{"sheet":"Noch eine Lösung aus der Praxis","index":18,"n_rows":59,"n_cols":15,"rows_kept":59,"cut":false,"hidden":false,"merged":[[1,1,1,4],[2,1,2,4],[4,1,8,7],[9,1,13,7]]}
[null,null,null,null,null,0]
[1,"Another practical solution",null,null,null,"Back to Start"]
[2,"Noch eine Lösung aus der Praxis",null,null,null,"Startseite"]
[]
[2,"Sendungsgewichte sollen folgendermaßen gerundet werden:\nAlles bis \"Komma 5\" auf \"Komma 5\" und alles darüber auf die nächste ganze Zahl.\n\"Komma 0\" und \"Komma 5\" bleiben \"Komma 0\" und \"Komma 5\".\n\nBeispiel:"]
[2]
[2]
[2]
[2]
[1,"Shipment weights should be rounded as follows:\nAll up to \"point 5\" to \"point 5\" and everything above to the nearest integer number.\n\"point 0\" and \"point 5\" remain \"point 0\" and \"point 5\" .\n\nExample:"]
[1]
[1]
[1]
[1]
[]
[]
[null,null,244,"----->",244]
[null,null,244.2,"----->",244.5]
[null,null,244.5,"----->",244.5]
[null,null,244.7,"----->",245]
[]
[1,"Solution:",null,"simple",null,null,null,null,null,null,null,null,null,"brilliant"]
[2,"Lösung:",null,"einfach",null,null,null,null,null,null,null,null,null,"genial"]
[null,null,261.60000000000002,{"$f":"IF(C24=INT(C24),C24,IF(C24-INT(C24)<=0.5,INT(C24)+0.5,INT(C24)+1))","v":262},{"$f":"_xlfn.FORMULATEXT(D24)","v":"=WENN(C24=GANZZAHL(C24);C24;WENN(C24-GANZZAHL(C24)<=0,5;GANZZAHL(C24)+0,5;GANZZAHL(C24)+1))"},null,null,null,null,null,null,null,null,{"$f":"ROUNDUP(C24/5,1)*5","v":262},{"$f":"_xlfn.FORMULATEXT(N24)","v":"=AUFRUNDEN(C24/5;1)*5"}]
[null,null,244.6,{"$f":"IF(C25=INT(C25),C25,IF(C25-INT(C25)<=0.5,INT(C25)+0.5,INT(C25)+1))","v":245},null,null,null,null,null,null,null,null,null,{"$f":"ROUNDUP(C25/5,1)*5","v":245}]
[null,null,404.5,404.5,null,null,null,null,null,null,null,null,null,404.5]
[null,null,1596.3,1596.5,null,null,null,null,null,null,null,null,null,1596.5]
[null,null,625,625,null,null,null,null,null,null,null,null,null,625]
[null,null,150.6,151,null,null,null,null,null,null,null,null,null,151]
[null,null,1262.5999999999999,1263,null,null,null,null,null,null,null,null,null,1263]
[null,null,1322.1,1322.5,null,null,null,null,null,null,null,null,null,1322.5]
[null,null,350.6,351,null,null,null,null,null,null,null,null,null,350.99999999999994]
[null,null,1872.1999999999998,1872.5,null,null,null,null,null,null,null,null,null,1872.5]
[null,null,1893,1893,null,null,null,null,null,null,null,null,null,1893]
[null,null,3.1,3.5,null,null,null,null,null,null,null,null,null,3.5]
[null,null,1851,1851,null,null,null,null,null,null,null,null,null,1851]
[null,null,1458.8999999999999,1459,null,null,null,null,null,null,null,null,null,1459]
[null,null,295.40000000000003,295.5,null,null,null,null,null,null,null,null,null,295.5]
[null,null,44.2,44.5,null,null,null,null,null,null,null,null,null,44.5]
[null,null,1671.1,1671.5,null,null,null,null,null,null,null,null,null,1671.5]
[null,null,1308.1999999999998,1308.5,null,null,null,null,null,null,null,null,null,1308.5000000000002]
[null,null,410.20000000000005,410.5,null,null,null,null,null,null,null,null,null,410.5]
[null,null,515.70000000000005,516,null,null,null,null,null,null,null,null,null,516]
[null,null,1863.3,1863.5,null,null,null,null,null,null,null,null,null,1863.5000000000002]
[null,null,203.6,204,null,null,null,null,null,null,null,null,null,204.00000000000003]
[null,null,1262.8,1263,null,null,null,null,null,null,null,null,null,1263]
[null,null,1749.8999999999999,1750,null,null,null,null,null,null,null,null,null,1750]
[null,null,196.79999999999998,197,null,null,null,null,null,null,null,null,null,197]
[null,null,1864.8999999999999,1865,null,null,null,null,null,null,null,null,null,1865]
[null,null,326.20000000000005,326.5,null,null,null,null,null,null,null,null,null,326.5]
[null,null,177.29999999999998,177.5,null,null,null,null,null,null,null,null,null,177.5]
[null,null,884.4,884.5,null,null,null,null,null,null,null,null,null,884.5]
[null,null,124.8,125,null,null,null,null,null,null,null,null,null,125]
[null,null,929,929,null,null,null,null,null,null,null,null,null,929]
[null,null,1959.6999999999998,1960,null,null,null,null,null,null,null,null,null,1960]
[null,null,1109.1999999999998,1109.5,null,null,null,null,null,null,null,null,null,1109.5]
[null,null,556.4,556.5,null,null,null,null,null,null,null,null,null,556.5]
[null,null,1510.3,1510.5,null,null,null,null,null,null,null,null,null,1510.5]
[null,null,244.2,244.5,null,null,null,null,null,null,null,null,null,244.5]

{"sheet":"Nützliche Formeln","index":19,"n_rows":130,"n_cols":7,"rows_kept":130,"cut":false,"hidden":false,"merged":[[113,3,113,5],[75,4,75,12],[79,4,79,12],[83,4,83,12],[85,3,86,7],[111,3,111,5],[112,3,112,5],[71,4,71,12],[27,2,27,5],[31,2,31,5],[33,2,33,5],[35,2,35,5],[43,2,43,5],[47,2,47,5],[53,4,53,12],[57,4,57,12],[61,4,61,12],[65,4,65,12],[67,3,68,7],[19,2,19,5],[1,1,1,4],[2,1,2,4],[5,2,9,6],[10,2,14,6],[17,2,17,5]]}
[null,null,null,null,null,0]
[1,"Useful formulas",null,null,null,"Back to Start"]
[2,"Nützliche Formeln",null,null,null,"Startseite"]
[]
[]
[2,"SUMME()","Mit folgender Formel kann man den Inhalt derselben Zelle über mehrere Blätter hinweg summieren:\n=SUMME(Tabelle1:Tabelle5!$B$1)\nDabei wird über alle Blätter summiert, die zwischen \"Tabelle1\" und \"Tabelle5\" liegen - unabhängig davon, wie sie heißen!"]
[2]
[2]
[2]
[2]
[1,"SUM()","Using the following formula, you can sum up the content of one cell across several worksheets:\n=SUM(Table1:Table5!$B$1)\nThe sum is calculated across all sheets between \"Table1\" and \"Table5\" - idependent from the name of the sheets!"]
[1]
[1]
[1]
[1]
[]
[]
[2,"ADRESSE()","SPALTE()"]
[2,null,null,"=>",{"$f":"COLUMN()","v":5}]
[2,null,"ZEILE()"]
[2,null,null,"=>",{"$f":"ROW()","v":21}]
[2]
[2,null,"ADRESSE(1;SPALTE();1)",null,"=>",{"$f":"ADDRESS(1,COLUMN(),1)","v":"$F$1"}]
[2,null,"ADRESSE(1;SPALTE();2)",null,"=>",{"$f":"ADDRESS(1,COLUMN(),2)","v":"F$1"}]
[2,null,"ADRESSE(1;SPALTE();3)",null,"=>",{"$f":"ADDRESS(1,COLUMN(),3)","v":"$F1"}]
[2,null,"ADRESSE(1;SPALTE();4)",null,"=>",{"$f":"ADDRESS(1,COLUMN(),4)","v":"F1"}]
[2]
[2,null,"ADRESSE(ZEILE();SPALTE())"]
[2,null,null,"=>",{"$f":"ADDRESS(ROW(),COLUMN())","v":"$E$29"}]
[2,null,"WECHSELN(ADRESSE(1;SPALTE();4);1;)"]
[2,null,null,"=>",{"$f":"SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,)","v":"E"},null,"Spaltenbuchstabe!"]
[2,null,"WECHSELN(ADRESSE(ZEILE();SPALTE());\"$\";\"\")"]
[2,null,null,"=>",{"$f":"SUBSTITUTE(ADDRESS(ROW(),COLUMN()),\"$\",\"\")","v":"E33"}]
[1,"ADDRESS()","COLUMN()"]
[1,null,null,"=>",{"$f":"COLUMN()","v":5}]
[1,null,"ROW()"]
[1,null,null,"=>",{"$f":"ROW()","v":37}]
[1]
[1,null,"ADDRESS(1;COLUMN();1)",null,"=>",{"$f":"ADDRESS(1,COLUMN(),1)","v":"$F$1"}]
[1,null,"ADDRESS(1;COLUMN();2)",null,"=>",{"$f":"ADDRESS(1,COLUMN(),2)","v":"F$1"}]
[1,null,"ADDRESS(1;COLUMN();3)",null,"=>",{"$f":"ADDRESS(1,COLUMN(),3)","v":"$F1"}]
[1,null,"ADDRESS(1;COLUMN();4)",null,"=>",{"$f":"ADDRESS(1,COLUMN(),4)","v":"F1"}]
[1]
[1,null,"ADDRESS(ROW();COLUMN())"]
[1,null,null,"=>",{"$f":"ADDRESS(ROW(),COLUMN())","v":"$E$45"}]
[1,null,"SUBSTITUTE(ADDRESS(1;COLUMN();4);1;)"]
[1,null,null,"=>",{"$f":"SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,)","v":"E"},null,"Column letter!"]
[1,null,"SUBSTITUTE(ADDRESS(ROW();COLUMN());\"$\";\"\")"]
[1,null,null,"=>",{"$f":"SUBSTITUTE(ADDRESS(ROW(),COLUMN()),\"$\",\"\")","v":"E49"}]
[]
[]
[2,"ZELLE()","Pfad + Dateiname + Blattname"]
[2,null,"ZELLE(\"dateiname\";$A$1)"]
[2,null,null,"=>",{"$f":"CELL(\"dateiname\",$A$1)","v":"E:\\000as\\Lehre_1\\109_Office\\Excel\\[Excel_201_Formulas_E_V230330_NEU.xlsm]Nützliche Formeln"}]
[2]
[2,null,"Pfad"]
[2,null,"LINKS(ZELLE(\"dateiname\";$A$1);FINDEN(\"[\";ZELLE(\"dateiname\";$A$1))-1)"]
[2,null,null,"=>",{"$f":"LEFT(CELL(\"dateiname\",$A$1),FIND(\"[\",CELL(\"dateiname\",$A$1))-1)","v":"E:\\000as\\Lehre_1\\109_Office\\Excel\\"}]
[2]
[2,null,"Dateiname"]
[2,null,"TEIL(ZELLE(\"dateiname\";$A$1);FINDEN(\"[\";ZELLE(\"dateiname\";$A$1))+1;FINDEN(\"]\";ZELLE(\"dateiname\";$A$1))-FINDEN(\"[\";ZELLE(\"dateiname\";$A$1))-1)"]
[2,null,null,"=>",{"$f":"MID(CELL(\"dateiname\",$A$1),FIND(\"[\",CELL(\"dateiname\",$A$1))+1,FIND(\"]\",CELL(\"dateiname\",$A$1))-FIND(\"[\",CELL(\"dateiname\",$A$1))-1)","v":"Excel_201_Formulas_E_V230330_NEU.xlsm"}]
[2]
[2,null,"Blattname"]
[2,null,"RECHTS(ZELLE(\"dateiname\";$A$1);LÄNGE(ZELLE(\"dateiname;$A$1\"))-FINDEN(\"]\";ZELLE(\"dateiname\";$A$1)))"]
[2,null,null,"=>",{"$f":"RIGHT(CELL(\"dateiname\",$A$1),LEN(CELL(\"dateiname\",A$1))-FIND(\"]\",CELL(\"dateiname\",$A$1)))","v":"Nützliche Formeln"}]
[2]
[2,null,"Achtung:","Das Argument $A$1 in ZELLE(…) ist wichtig! Ohne $A$1 wird nicht das aktuelle Blatt ermittelt, sondern das zuletzt aktualisierte!"]
[2]
[1,"CELL()","path + file name + sheet name"]
[1,null,"CELL(\"filename\";$A$1)"]
[1,null,null,"=>",{"$f":"CELL(\"dateiname\",$A$1)","v":"E:\\000as\\Lehre_1\\109_Office\\Excel\\[Excel_201_Formulas_E_V230330_NEU.xlsm]Nützliche Formeln"}]
[1]
[1,null,"path"]
[1,null,"LEFT(CELL(\"filename\";$A$1);FIND(\"[\";CELL(\"filename\";$A$1))-1)"]
[1,null,null,"=>",{"$f":"LEFT(CELL(\"dateiname\",$A$1),FIND(\"[\",CELL(\"dateiname\",$A$1))-1)","v":"E:\\000as\\Lehre_1\\109_Office\\Excel\\"}]
[1]
[1,null,"file name"]
[1,null,"MID(CELL(\"filename\";$A$1);FIND(\"[\";CELL(\"filename\";$A$1))+1;FIND(\"]\";CELL(\"filename\";$A$1))-FIND(\"[\";CELL(\"filename\";$A$1))-1)"]
[1,null,null,"=>",{"$f":"MID(CELL(\"dateiname\",$A$1),FIND(\"[\",CELL(\"dateiname\",$A$1))+1,FIND(\"]\",CELL(\"dateiname\",$A$1))-FIND(\"[\",CELL(\"dateiname\",$A$1))-1)","v":"Excel_201_Formulas_E_V230330_NEU.xlsm"}]
[1]
[1,null,"sheet name"]
[1,null,"RIGHT(CELL(\"filename\";$A$1);LEN(CELL(\"filename\";$A1))-FIND(\"]\";CELL(\"filename\";$A$1)))"]
[1,null,null,"=>",{"$f":"RIGHT(CELL(\"dateiname\",$A$1),LEN(CELL(\"dateiname\",$A$1))-FIND(\"]\",CELL(\"dateiname\",$A$1)))","v":"Nützliche Formeln"}]
[1]
[1,null,"Attention:","The argument $A$1 in CELL(…) is important! Without $A$1 not the current sheet is found but that one, which was last updated!"]
[1]
[]
[]
[2,"TEXT()","TEXT(HEUTE();\"TTTT\")"]
[2,null,null,"=>",{"$f":"TEXT(TODAY(),\"TTTT\")","v":"Donnerstag"}]
[2,null,"TEXT(HEUTE();\"MMMM\")"]
[2,null,null,"=>",{"$f":"TEXT(TODAY(),\"MMMM\")","v":"März"}]
[2,null,"TEXT(JETZT();\"hh:mm\")"]
[2,null,null,"=>",{"$f":"TEXT(NOW(),\"hh:mm\")","v":"10:35"}]
[2,null,"TEXT(1234,567;\"#.##0,00 €\")"]
[2,null,null,"=>",{"$f":"TEXT(1234.567,\"#.##0,00 €\")","v":"1.234,57 €"}]
[1,"TEXT()","TEXT(TODAY();\"DDDD\")"]
[1,null,null,"=>",{"$f":"TEXT(TODAY(),\"TTTT\")","v":"Donnerstag"}]
[1,null,"TEXT(TODAY();\"MMMM\")"]
[1,null,null,"=>",{"$f":"TEXT(TODAY(),\"MMMM\")","v":"März"}]
[1,null,"TEXT(NOW();\"hh:mm\")"]
[1,null,null,"=>",{"$f":"TEXT(NOW(),\"hh:mm\")","v":"10:35"}]
[1,null,"TEXT(1234,567;\"#.##0,00 €\")"]
[1,null,null,"=>",{"$f":"TEXT(1234.567,\"#.##0,00 €\")","v":"1.234,57 €"}]
[]
[null,"INDEX()",null,5,2,8]
[null,null,null,8,1,4]
[null,null,null,2,9,3]
[null,null,null,1,6,7]
[]
[null,null,{"$f":"INDEX(D107:F110,2,3)","v":4},{"$f":"_xlfn.FORMULATEXT(C112)","v":"=INDEX(D107:F110;2;3)"}]
[null,null,{"$f":"SUM(INDEX(D107:F110,3,))","v":14},{"$f":"_xlfn.FORMULATEXT(C113)","v":"=SUMME(INDEX(D107:F110;3;))"}]
[null,null,{"$f":"SUM(INDEX(D107:F110,,2))","v":18},"=SUMME(INDEX(D107:F110;;2))"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[0]