Excel

• 【図解】Excelでカーソル移動できない⁉︎ 対処法と便利な移動テクを徹底ガイド

• Convert cell string type force

  • =TEXT(date,format)
  • https://exceljet.net/formula/convert-date-to-text

• get End date from string type date

  • A1 : 20200501
  • A2 : =TEXT(EOMONTH(DATE(MID(A1, 1, 4), MID(A1, 5, 2), "01"),-1), "yyyymmdd")
    • result : 20200430
  • A3 : =TEXT(EOMONTH(DATE(MID(A1, 1, 4), MID(A1, 5, 2), "01"),0), "yyyymmdd")
    • result : 20200531
  • A4 : =TEXT(EOMONTH(DATE(MID(A1, 1, 4), MID(A1, 5, 2), "01"),1), "yyyymmdd")
    • result : 20200630

VLookUp

VLOOKUP(Findword, Range, ReturnFieldNumber)

MAX

MAX(A1:B50)

• if range contains mixed with number and character, then return 0

MOD

MOD(2198, 5)

left number of 2198 devide 5

• https://office-hack.com/excel/mod/

Pivot

• Excel pivot table range change

datetime

• excel 時間計算
  • https://www.tipsfound.com/excel/02207