2020年5月22日 星期五

Excel tips

[add analysis toolpak]
file -> option -> addon -> analysis toolpak -> execute -> check on 'analysis toolpak' & 'analysis toolpak-VBA' -> OK

[array to list]
idea: auto calculate the new index by mod function
template: https://drive.google.com/file/d/19USrMKCdxX2rjd_XbNw22r9lC0a7GZbL/view?usp=sharing

[histogram]
Method 1: analysis toolpak
Con: have to regenerate histogram manually every time, when the data changes
step 1: data -> analysis -> histogram ->

'input range' : data to be analysis; eg. sheet1!A1:LR259
'range' : the x-axis of the histogram eg. B4:B24
'output range' : the count results D1
check on the plot out
-> OK

Method 2: using frequency function
Pros: update the data automatically
Cons: additional techniques to plot
Steps:
1. evaluate the max/min of data
2. create the x-axis
3. using frequency function:
3.1 select the output array  eg. B2:B22
3.2 input '=FREQUENCY(HG_data!$A$1:$LR$259,$A$2:$A22)' in the function slot
3.3 press 'Ctrl+Shift+Enter' to generate the histogram data
4. plot the histogram
4.1 insert -> histogram plot
4.2 right click on the empty plot and choose (E)
4.3 Add new data & axis
4.4 add some labels as you like
4.5 add variable content: create a text box, select the text box, put '=A1' or some other text or variable, enter
Method 3: 數據透視圖
數據透視圖
not trying this

template:
https://drive.google.com/file/d/17q_Tm6EeQ5qmAQhTF-nGKRb8711FZ4-R/view?usp=sharing

ref.1 https://kknews.cc/zh-tw/code/peqojnp.html
ref.2 https://isvincent.pixnet.net/blog/post/37718302-excel-%E8%AE%93%E5%9C%96%E8%A1%A8%E7%9A%84%E6%A8%99%E9%A1%8C%E8%87%AA%E5%8B%95%E6%9B%B4%E6%96%B0
ref.3

[fill a long list]
Method 1: left click on the green square dot and drag down

Method 2: ctrl+G
step 1: select J1
step 2: ctrl+G and input 'J1:J6', then OK
step 3: type the command for J1, eg. =INDEX($A$1:$C$2, MOD(ROW(H1)-1, 2)+1, MOD( FLOOR((ROW(H1)-1)/2, 1), 3)+1)
step 4: press 'ctrl+enter'

template: https://drive.google.com/open?id=19USrMKCdxX2rjd_XbNw22r9lC0a7GZbL

ref.1 https://superuser.com/questions/948175/fill-a-large-range-with-a-formula-in-excel-without-mouse-dragging-to-extend

沒有留言:

張貼留言