[VBA] 判斷Pvalue並上色

Sub 判斷Pvalue()
For x = 2 To 101
For y = 2 To 745
   Worksheets("output").Cells(y, x).Value = Worksheets("Corr").Cells(y, x).Value
   If Worksheets("Pval").Cells(y, x).Value < 0.05 And Worksheets("Corr").Cells(y, x).Value > 0 Then
   Worksheets("output").Cells(y, x).Interior.ColorIndex = 3
   ElseIf Worksheets("Pval").Cells(y, x).Value < 0.05 And Worksheets("Corr").Cells(y, x).Value < 0 Then
   Worksheets("output").Cells(y, x).Interior.ColorIndex = 5
   End If
Next
Next
End Sub

信末結尾語

信末結尾語的使用必需考量與email收件人的關係和收件人的互動情況:

Email 的對象或情況	可以用的結尾	中文意思
有交情的朋友或同事	Cheers,	祝你開心
請對方幫忙或協助	Many thanks.	多謝了
呼應信件的內容	Have a nice day.  Enjoy the holiday.  To your success.  keep up the good work.	祝你有美好的一天  假期愉快  祝你成功  繼續加油
關心與祝福的感性結尾	Best wishes,  All the best,  Good luck!  Take care & see you soon.	祝一切順心  祝萬事如意  祝一切順利  請保重期待再見

[SQL] Concat

SELECT * FROM `tmp` AS T1 ,  `pca_index` AS T2
WHERE T1.tchrom = T2.chrom AND
T2.gene_members like  concat("%",T1.tSYM,"%")

CONCAT(字串1, 字串2, 字串3, ...): 將字串1、字串2、字串3，等字串連在一起。

WampServer 修改預設開啟瀏覽器

修改檔案 wampmanager.ini  內的

[Menu.Left]
;WAMPMENULEFTSTART
Type: separator; Caption: "Powered by Anaska"
Type: item; Caption: "Localhost"; Action: run; FileName: "C:/Program Files/Mozilla Firefox/firefox.exe"; Parameters: "http://localhost/"; Glyph: 5
Type: item; Caption: "phpMyAdmin"; Action: run; FileName: "C:/Program Files/Mozilla Firefox/firefox.exe"; Parameters: "http://localhost/phpmyadmin/"; Glyph: 5

改成: C:/Users/Redlin/AppData/Local/Google/Chrome/Application/chrome.exe

[R] Hypergeometric Relationship to Fisher's exact test

通常用來做Enrichment analysis的方法不外乎是
(1) Binomial distribution probability

(2) Hypergeometric probability  P[X ≥ x] 的機率

ex. We have total 38574 probes, significance have 14558, then we take 53 probes and get 33 probes significance

[R code ]:   
P[X ≥ 33] = P[X = 33]+P[X > 33] = 1-P[X ≤ 32]
dhyper(33, 14558, (38574-14558), 53 ) +
phyper(33, 14558, (38574-14558), 53, lower.tail = FALSE)
= 1 - phyper(32, 14558, (38574-14558), 53)

而此Hypergeometric 機率又與Fisher's Exact Test 單尾檢定相同:

x <- matrix( c(33,20,14525,23996), ncol=2)
out <- fisher.test(x, alternative = "greater")
out$p.val = P[X ≥ 33]

	+	-
+	33	14558-33	14558
-	20	24016-56	24016
	53	38574-53	38574