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