系列文章目錄

---

前言

一、excel公式應用

1.rand函數

用excel生成1-5的隨機數字，其中對應的概率分別為10%，20%，30%，10%，30%

選中A1：A50區域，輸入：
=MATCH(RAND(),{0,0.1,0.3,0.6,0.7,1})

解析：
1、用rand()函數生成0-1之間的隨機數；
2、MATCH()函數省略第三個參數時，會查找小于或等于第一個參數的最大值(前提條件：lookup_array參數中的值必須按升序排列)，返回這個值所在的位置。
3、0.1-0為10%的概率；0.3-0.1為20%的概率，依次類推；

2.rand函數隨機排序

如何將多行內容隨機重新排序？

假設數據在A列，可在B1單元格中輸入rand()，向下填充，排序；

3.rand函數提取數據

在1-35里任取6個不重復的數？

A1單元格留空，B1單元格輸入
=LARGE(IF(COUNTIF(A1:A1,ROW(1:35))=0,ROW(1:35)),RAND()*(36-COLUMN(B1))+1)
ctrl + shift + enter

4.correl函數

1、correl函數是一種用于計算變量之間的相關性的數學函數，用于分析兩個變量之間的聯系。correl函數的計算步驟是將兩個變量的數據集的所有數據點的x和y坐標作為輸入參數，然后計算每一對數據點之間的距離，并根據這個距離來計算兩個變量之間的相關性。距離越大，相關性越小；
2、另外，correl函數也會結合擬合曲線，通過計算回歸方程的參數，即斜率和截距，來確定變量之間的線性關系；，即當x變化時，變量y會如何變化。這些參數可以用來度量不同變量之間的相關性以及度量y受到變量x的影響程度；
3、此外，correl函數還可以幫我們計算一個變量的“預期值”，及預測該變量的未來值。因此，使用correl函數可以幫我們作出更精確的預測。
4、在excel中，correl函數和person函數提供了計算兩個變量之間的相關系數的方法；與相關系數有關的函數還有rsq(相關系數的平方)
5、correl(array1，array2)返回單元格區域之間的相關系數。

5.SUBSTITUTE函數

在文本字符串中用new_text替代old_text，如果需要在某一文本字符串中替換指定文本，使用substitute；如果需要在某一文本字符串中替換指定位置處的任意文本，使用Replace;

SUBSTITUTE(text,old_text,new_text,[instance_num])

• Text 不省略參數。為需要替換其中字符的文本，或對含有文本的單元格的引用。
• Old_text 不省略參數。為需要替換的舊文本。
• New_text 不省略參數,但有默認值空。用于替換 old_text 的文本。
• Instance_num 為一數值，用來指定以 new_text 替換第幾次出現的 old_text。如果指定了 instance_num，則只有滿足要求的 old_text 被替換；如果缺省則將用 new_text 替換 TEXT 中出現的所有 old_text。

=SUBSTITUTE(ADDRESS(1,COLUMN(B1),2)"1","")

6.MAX組合函數

=MAX(OFFSET(B$1,ROW($52:$102)-1,,(MATCH(AF$3,A:A,0)-MATCH(AE$3,A:A,0))))
{=IFERROR(MAX(OFFSET(B$1,($AJ3:$AK3)-1,,(MATCH($AF3,$A:$A,0)-MATCH($AE3,$A:$A,0))+1)),"")}

7.分析下班時間

1.需要按照崗位類型、班次統計下班打卡時間與規定下班時間的時間間隔

=IF(AND(E2="技能作業類",G2="B017B光學兩班組10-夜班"),ROUND(ABS(TIMEVALUE(TEXT(K2,"h:mm"))-TIME(7,30,0))*1440,0),
IF(AND(E2="技能作業類",G2="B013A光學兩班組5-白班"),ROUND(ABS(TIMEVALUE(TEXT(K2,"h:mm"))-TIME(19,30,0))*1440,0),
IF(AND(E2="技能作業類",G2="B013B光學兩班組5-夜班"),ROUND(ABS(TIMEVALUE(TEXT(K2,"h:mm"))-TIME(7,30,0))*1440,0),
IF(AND(E2="技能作業類",G2="B017A光學兩班組10-白班"),ROUND(ABS(TIMEVALUE(TEXT(K2,"h:mm"))-TIME(19,30,0))*1440,0),
IF(AND(E2="技能作業類",G2="B017E光學兩班組11-白班"),ROUND(ABS(TIMEVALUE(TEXT(K2,"h:mm"))-TIME(20,0,0))*1440,0),
IF(AND(E2="13薪",G2="B0DL1光學常白班1"),ROUND(ABS(TIMEVALUE(TEXT(K2,"h:mm"))-TIME(17,0,0))*1440,0),"班次格式錯誤"))))))

2.按分和秒顯示 崗位在E列，班次在G列，下班打卡時間在K列

=IF(AND(E2="技能作業類",G2="B017B光學兩班組10-夜班"),IF(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(7,30,0))*86400,60)=0,TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(7,30,0))*86400/60),"0分"),TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(7,30,0))*86400/60),"0分")&TEXT(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(7,30,0))*86400,60),"00秒")),
IF(AND(E2="技能作業類",G2="B013A光學兩班組5-白班"),IF(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(19,30,0))*86400,60)=0,TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(19,30,0))*86400/60),"0分"),TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(19,30,0))*86400/60),"0分")&TEXT(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(19,30,0))*86400,60),"00秒")),
IF(AND(E2="技能作業類",G2="B013B光學兩班組5-夜班"),IF(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(7,30,0))*86400,60)=0,TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(7,30,0))*86400/60),"0分"),TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(7,30,0))*86400/60),"0分")&TEXT(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(7,30,0))*86400,60),"00秒")),
IF(AND(E2="技能作業類",G2="B017A光學兩班組10-白班"),IF(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(19,30,0))*86400,60)=0,TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(19,30,0))*86400/60),"0分"),TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(19,30,0))*86400/60),"0分")&TEXT(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(19,30,0))*86400,60),"00秒")),
IF(AND(E2="技能作業類",G2="B017E光學兩班組11-白班"),IF(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(20,0,0))*86400,60)=0,TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(20,0,0))*86400/60),"0分"),TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(20,0,0))*86400/60),"0分")&TEXT(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(20,0,0))*86400,60),"00秒")),
IF(AND(E2="13薪",G2="B0DL1光學常白班1"),IF(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(17,0,0))*86400,60)=0,TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(17,0,0))*86400/60),"0分"),TEXT(INT(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(17,0,0))*86400/60),"0分")&TEXT(MOD(ABS(TIMEVALUE(TEXT(K2,"h:mm")) - TIME(17,0,0))*86400,60),"00秒")),"班次格式錯誤"))))))

8.柏拉圖自動排序

數據如圖：

=IF(COUNTIF($H$2:H2,H2)=1,RANK(H2,$H$2:$AN$2),RANK(H2,$H$2:$AN$2)+COUNTIF($H$2:H2,H2)-1)

---

總結

分享：
AA大書：接受可以讓我面對所有的問題，當我感到焦慮的時候，通常是因為我發現自己不能接受生活中的一些人、地方、事情，直到我完全接受了它們，我才能獲得心靈上的安寧。除非我完全的接受生活，否則我將無法獲得快樂。我不需要再糾結這個世界上有什么需要改變而是關注我自己的態度需要發生怎樣的改變；