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 )) , "")

• The Art of SpreadSheet External Link
• xlsx vs. xlsb 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
  • 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
• 한/영 자동고침 해제
  • 옵션 > 언어 교정 > '자동 고침 옵션' > 자동 고침 옵션 버튼 > 자동 고침 탭 > '한/영 자동 고침' 체크 해제
• 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
• txt로 복사할 때 쌍 따옴표(double quotes) 안붙게 : word 에 복사한 후 txt로 복사

• “셀서식이 너무 많습니다” : 매크로 이용해서 셀서식을 모두 지워줘야 함 link

  •  ActiveWorkbook.Styles(i).delete 

• 조건에 맞는 모든 값 참조해오기 : 배열수식으로 작성!

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

• 조건에 맞는 값 중 맨 아래쪽 리턴 : 배열수식으로

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

• 셀 내 한글 포함 여부

len(a1)=lenb(a1)

• 와일드카드 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 (~~).

• Category별 순위

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

• 억단위 셀서식

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

• 값 또는 %

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

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

• Get Pivot Data data_field 부분에 참조로 값 넣기 :

   getpivotdata([참조]&"", ....) 

  • https://stackoverflow.com/questions/3745782/how-to-use-a-reference-for-getpivotdatas-data-field-argument

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

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

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

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

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