Table of Contents

Excel

, ,

VBA

자신의 엑셀 수준은??

Cartesian Product 하기 stackoverflow
- Alt(바로 떼기)+D+P : 피벗테이블 마법사
- Multiple consolidation ranges“ –> create a single page.. –> Select all cells (including headers!)
- 값을 행으로 넣기

substitute text 여러개 한번에 하기 (LAMBDA & recursive function) : 이름정의 MultiReplace 로
https://www.ablebits.com/office-addins-blog/excel-find-replace-multiple-values/

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))

#DIVS 에러 처리

=LET( x, sumifs( A2/B2 ), IF(x = 0, "",x) )
=iferror(1/(1/sumifs( A2/B2 )) , "")

General

Error Handling

Reference

= index( 참조영역,
   small( if(조건영역 = 조건셀 , row(조건영역) ),  n)   )
  #조건영역=조건셀 이면 row가 그 열번호를 리턴--> 배열이됨
  #n은 몇번째인지.. small 함수는 배열에서 n번째 값을 리턴함.


=index( 참조영역,   max( if( 조건영역 = 조건 , row(조건영역), 0) )

Text

len(a1)=lenb(a1)

Asterisk (*) - zero or more characters
Question mark (?) - any one character
Tilde (~) - escape for literal character (~*) a literal question mark (~?), or a literal tilde (~~).

Aggregation

=sumproduct (  (A1:A100=A1)*(B1:B100>B1) )  1

Format

[>=100000000000]##","##0"."##,,;[<100000000000]##0"."##,,;


[>]#,###;[=0]#;0.0%

Pivot table

Etc.

Function ENCODEURL(varText As Variant, Optional blnEncode = True)
Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
   Set objHtmlfile = CreateObject("htmlfile")
   With objHtmlfile.parentWindow
        .execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
      End With
    End If
    If blnEncode Then
      ENCODEURL = objHtmlfile.parentWindow.encode(varText)
    End If
End Function

Function DECODEURL(varText As Variant, Optional blnEncode = True)
Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
      Set objHtmlfile = CreateObject("htmlfile")
      With objHtmlfile.parentWindow
        .execScript "function decode(s) {return decodeURIComponent(s)}", "jscript"
      End With
    End If
    If blnEncode Then
      DECODEURL = objHtmlfile.parentWindow.decode(varText)
    End If
End Function

Dynamic Analytic Tool