Excel Operations

Excel.md

Excel

1. Each Function = New Column
2. Apply to 1 Cell , later extend
3. All Rows A:A
4. "this is string"

String

=LEN(C1)
=ISTEXT(C1)
=TRIM(C1)
=Upper(C1)
=REPLACE(old_text, start_num, num_chars, new_text)
=REPLACE(C1, 1, 2, "ABC")
=SUBSTITUTE(A20, "I like","He likes")
=CONCATENATE(A27," ",B27)

Int

=SUM(C2,D2)

Date and Time

=NOW()
=DAY(TODAY())
=TIME()
=HOUR(TIME())
=DATEDIF()

VLOOKUP (Searching / comparing 2 lists )

=VLOOKUP(find,range, returnvalue, exact)
=VLOOKUP(B3,Sheet2!A:B,2,FALSE)

IF

=IF(logical test, value if true, value if false)
=IF(D2>7,"Yes","no")
=COUNTIF(B:B,E3)  -- range , find
=SUMIF(search_range,search_value,same_row_add)
=SUMIF(B:B,E3,C:C)
=IFERROR(SEARCH("C",C1),"error")

Pivot (same GroupBY )

• Pivot Columns will be used as Column Name

    df.show()
    +-------+------+-------+                                                        
    |Product|Amount|Country|
    +-------+------+-------+
    | Banana|  1000|    USA|
    |Carrots|  1500|    USA|
    |  Beans|  1600|    USA|
    | Orange|  2000|    USA|
    | Orange|  2000|    USA|
    | Banana|   400|  China|
    |Carrots|  1200|  China|
    |  Beans|  1500|  China|
    | Orange|  4000|  China|
    | Banana|  2000| Canada|
    |Carrots|  2000| Canada|
    |  Beans|  2000| Mexico|
    +-------+------+-------+
  
    >>> df.groupBy("Product").pivot("Country").sum("Amount").show()
    +-------+------+-----+------+----+
    |Product|Canada|China|Mexico| USA|
    +-------+------+-----+------+----+
    | Orange|  null| 4000|  null|4000|
    |  Beans|  null| 1500|  2000|1600|
    | Banana|  2000|  400|  null|1000|
    |Carrots|  2000| 1200|  null|1500|
    +-------+------+-----+------+----+
  
    >>> >>> df.groupBy("Product").sum("Amount").show()
    +-------+-----------+
    |Product|sum(Amount)|
    +-------+-----------+
    | Banana|       3400|
    |  Beans|       5100|
    |Carrots|       4700|
    | Orange|       8000|
    +-------+-----------+