Excel tips

최근 편집: 2019년 8월 23일 (금) 07:21
Yonghokim (토론 | 기여)님의 2019년 8월 23일 (금) 07:21 판 (새 문서: '''Excel''' is a spreadsheet program developed by Microsoft. <br /> == Excel usage tips == {{Toolkit}}{{유튜브|pUXJLzqlEPk|In Excel 2007?|섬네일}} [https://www.excel-universit...)
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)

Excel is a spreadsheet program developed by Microsoft.


Excel usage tips

영상 불러오기
유튜브
In Excel 2007?

How to convert data from a row x col 2-dimensional pivot format back to a "flat" format

Frequency

=COUNTIF(Raw!B$2:Raw!B$94922,A2)

How to extract columns with information if a DB export has hundreds of columns that are empty

  1. Insert a row below the labels row
  2. =DATAROWS-COUNTBLANK(FULL COLUMN excluding the two top rows)
  3. copy & transpose paste in another spreadsheet
  4. delete the two rows
  5. select empty columns and delete them

=> didn't work in a spreadsheet with 35,000 entries

To use the numeric content of a cell to define the location of another cell within a formula

http://www.contextures.com/xlFunctions01.html#Offset

Last word in Cell

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

from http://www.exceltip.com/st/Extract_the_last_word_in_a_cell_in_Microsoft_Excel/368.html

원본 주소 "https://femiwiki.com/index.php?title=Excel_tips&oldid=149327"