====== Excel ======

{{tag>business data_analysis office}}

[[:software_development:vba|]]

[[https://statwith.tistory.com/579|자신의 엑셀 수준은??]]




Cartesian Product 하기 [[https://stackoverflow.com/questions/26999604/is-there-a-way-to-perform-a-cross-join-or-cartesian-product-in-excel|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/
<code vb>
=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))
</code>

#DIVS 에러 처리
<code vb>
=LET( x, sumifs( A2/B2 ), IF(x = 0, "",x) )
=iferror(1/(1/sumifs( A2/B2 )) , "")
</code>


===== General =====

  * The Art of SpreadSheet [[http://john.raffensperger.org/ArtOfTheSpreadsheet/index.html|External Link]]
  * xlsx vs. xlsb [[http://analystcave.com/excel-working-with-large-excel-files-the-xlsb-format/|http://analystcave.com/excel-working-with-large-excel-files-the-xlsb-format/]]
  * 엑셀 사양 및 제안  [[https://support.office.com/ko-kr/article/excel-%EC%82%AC%EC%96%91-%EB%B0%8F-%EC%A0%9C%ED%95%9C-1672b34d-7043-467e-8e27-269d656771c3|https://support.office.com/ko-kr/article/excel-%EC%82%AC%EC%96%91-%EB%B0%8F-%EC%A0%9C%ED%95%9C-1672b34d-7043-467e-8e27-269d656771c3]]
      * 1,048,576행x16,384열
      * 셀 당 32,767자
  * Language Accesory Pack [[https://support.office.com/en-gb/article/language-accessory-pack-for-office-82ee1236-0f9a-45ee-9c72-05b026ee809f?ocmsassetID=fx010211366&ui=en-US&rs=en-GB&ad=GB#ID0EAADAAA=Office_2010|https://support.office.com/en-gb/article/language-accessory-pack-for-office-82ee1236-0f9a-45ee-9c72-05b026ee809f?ocmsassetID=fx010211366&ui=en-US&rs=en-GB&ad=GB#ID0EAADAAA=Office_2010]]
  * 한/영 자동고침 해제
      * 옵션 > 언어 교정 > '자동 고침 옵션' > 자동 고침 옵션 버튼 > 자동 고침 탭 > '한/영 자동 고침' 체크 해제
  *  [[http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/|http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/]]
      * [[http://www.exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm|http://www.exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm]]
  * txt로 복사할 때 쌍 따옴표(double quotes) 안붙게 : word 에 복사한 후 txt로 복사


==== Error Handling ====

  * "셀서식이 너무 많습니다" : 매크로 이용해서 셀서식을 모두 지워줘야 함  [[https://xlstory.tistory.com/entry/엑셀-셀서식이너무많습니다|link]]
      * <code vb> ActiveWorkbook.Styles(i).delete </code>


===== Reference =====

  * **조건에 맞는 모든 값 참조해오기 **: 배열수식으로 작성!
<code>
= index( 참조영역,
   small( if(조건영역 = 조건셀 , row(조건영역) ),  n)   )
  #조건영역=조건셀 이면 row가 그 열번호를 리턴--> 배열이됨
  #n은 몇번째인지.. small 함수는 배열에서 n번째 값을 리턴함.
</code>

  * **조건에 맞는 값 중 맨 아래쪽 리턴 :**  배열수식으로
<code>
=index( 참조영역,   max( if( 조건영역 = 조건 , row(조건영역), 0) )
</code>

===== Text =====

  * 셀 내 한글 포함 여부

<code>
len(a1)=lenb(a1)
</code>

  * 와일드카드 https://exceljet.net/glossary/wildcard
Asterisk (*) - zero or more characters
Question mark (?) - any one character
Tilde (~) - escape for literal character (~*) a literal question mark (~?), or a literal tilde (~~).
===== Aggregation =====

  * Category별 순위

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

===== Format =====

  * 억단위 셀서식

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

  * 값 또는 %

<code>
[>]#,###;[=0]#;0.0%
</code>



  * 메모, Comment 모양 Tip : https://www.contextures.com/xlcomments02.html#Colour


===== Pivot table ====
  * Get Pivot Data data_field 부분에 참조로 값 넣기 : <code> getpivotdata([참조]&"", ....) </code> 
    * https://stackoverflow.com/questions/3745782/how-to-use-a-reference-for-getpivotdatas-data-field-argument


===== Etc. =====

  * hyperlink 시 UTF-8 Encoding 문제
      * #, & 같은 것은 ASCII code로 변환해서 넣어줘야 함 [[https://www.w3schools.com/TAGS/ref_urlencode.asp|https://www.w3schools.com/TAGS/ref_urlencode.asp]]
      * 2013버전부터는 EncodeUrl 함수가 기본 내장 - 이전 버전은 VBA 로
      * 클릭시 자동으로 다시 %xx 가 한글로 바뀌는데, %를 &(%의 Ascii code) 으로 substitute 해주면 됨)
      * VBA 코드 [[http://cocosoft.kr/442|출처]]

<code>
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
</code>







===== Dynamic Analytic Tool =====

  * **(보안) 수정, 삭제 불가하게 만들기**
      * Sheet 보호
        * 셀서식 > 보호 > 숨김 : 수식 숨기기
        * 셀서식 > 보호 > 잠금 하면 하이퍼링크 안됨
        * ※하이퍼링크 잠금 해제 > 시트 보호> 선택불가 만들면, 임의 공간 더블클릭시 하이퍼링크가 더블클릭되므로, 임의의 하나 셀을 잠금 해제 할 필요가 있음
      * Sheet 숨기기 (Sheet name 알게 되면 다른 Workbook에서 참조로 끌어올 수 있음)
      * 검토Tab > 통합문서 보호
  * **(보안) 특정 환경에서만 실행되게 하기**
      * IP 대역을 통한
      * HDD 내에 특정 프로그램 설치시 (특정 파일 존재시)
  * 속도 느릴땐 숨겨진 개체가 없는지 확인하자

  * 메모 : 데이터 > 데이터 유효성 검사 > 설명 메시지

  * Macro에 password를 걸었더라도, Shape macro 설정을 통해서 실행할 수 있으니.. \\ 밖에서 실행하면 안되는 함수는<font inherit/inherit;;#c0392b;;inherit>: private으로 설정 or Sheet/Workbook 의 코드로 삽입</font>

~~DISCUSSION~~