中國式的排名 函數(shù)應(yīng)用
RANK 是個排名函數(shù) 但有一個問題象上面有兩個100分 也就是兩并列第一 排名就從第三名開始沒有第二名 也許這個美國式的排名 中國式的排名 前面有兩個一樣的分數(shù)并列第一后是 第二名 怎樣用函數(shù)實現(xiàn).
解答：在B3中復(fù)制如下的數(shù)組公式，然后往右復(fù)制：
{=SUM(IF(B1=LARGE(IF(LARGE($B$1:$H$1,COLUMN($A$1:$G$1))=IF(ISERROR(LARGE($C$1:$I$1,COLUMN($A$1:$G$1))),0,LARGE($C$1:$I$1,COLUMN($A$1:$G$1))),0,LARGE($B$1:$H$1,COLUMN($A$1:$G$1))),COLUMN($A$1:$G$1)),COLUMN($A$1:$G$1),0))}
也可換個方式不用數(shù)組，在B3中復(fù)制如下的公式，然后往右復(fù)制：
=RANK(B1,$B1:$H1)+COUNTIF($B$1:B1,B1)-1
當(dāng)做日報表累計數(shù)自動加
當(dāng)做日報表時,怎樣讓月累計數(shù)自動加上? 就是要月累計自動加上今天的當(dāng)日收入數(shù),今天只輸入當(dāng)日收入,我想用用前一天的月累計數(shù)加上今天的當(dāng)日收入數(shù)為今天的月累計數(shù).情況是一月一個工作薄,每一個工作薄下30個工作表,用每一天的日期為報表名.我想把月累計的公式寫為 =sheet17!c5 中的 17 用 day(now())-1 的值去取代他,該怎么辦呢? 以下為報表格式,謝謝大家?guī)臀蚁胍幌?
部門-------當(dāng)日收入 -----------月累計
團隊收入 12.12 123.00 /寫字間收入 147,258.00 147.147/房內(nèi)吧收入 147,258,369.00
解答：=SUM('Sheet1:Sheet30'!C5)
在你需要月匯總的單元格填入上述公式，其作用是將工作表1到工作表30的“C5”單元格的值全部累加起來，而“C5”單元格應(yīng)填入當(dāng)日的收入數(shù)。
又問：月累計的公式寫為 =sheet17!c5 中的 17 用 day(now())-1 的值去取代他,該怎么辦呢? ”
答：用公式： =INDIRECT(ADDRESS(1,1,1,1,sheet&DAY(NOW())-1))
工資條問題
職工工資構(gòu)成非常復(fù)雜，往往超過10項，因此每月發(fā)工資時要向職工提供一包含工資各構(gòu)成部分的項目名稱和具體數(shù)值的工資條。打印工資條時要求在每個職工的工資條間有一空行便于彼此裁開。本模板就是用EXCEL函數(shù)根據(jù)工資清單生成一便于分割含有工資細目的工資條表格。
本工資簿包含兩張工資表。第1張工資表就是工資清單，稱為清單。它第一行為標題行包括職工姓名、各工資細目。
第2張工作表就是供打印的表，稱為工資條。它應(yīng)設(shè)置為每三行一組，每組第一行為標題，第二為姓名和各項工資數(shù)據(jù)，第三行為空白行。就是說整張表被3除余1的行為標題行，被3除余2的行為包括職工姓名、各項工資數(shù)據(jù)的行，能被3整除的行為為空行。
在某一單元格輸入套用函數(shù)=MOD(ROW(),3)，它的值就是該單元格所在行被3除的余數(shù)。因此用此函數(shù)能判別該行是標題行、數(shù)據(jù)行還是空行。
在A1單元格輸入公式=IF(MOD(ROW(),3)=0,,IF(MOD(ROW(),3)=1,清單!A$1,value-if-false))并往下填充，從A1單元格開始在A列各單元格的值分別為清單A1單元格的值即姓名、value-if-false、空白，姓名、value-if-false、空白，......。其中value-if-false表示MOD(ROW(),3)既不等于0又不等于1時，即它等于2時應(yīng)取的值。它可用如下函數(shù)來賦值：INDEX(清單!$A:$G,INT((ROW()+4)/3),COLUMN())。INDEX()為一查找函數(shù)它的格式為：INDEX(reference,row-num,col-num),其中reference為查找的區(qū)域，本例中為清單表中的A到G列，即函數(shù)中的清單!$A:$G，row-num為被查找區(qū)域中的行序數(shù)即函數(shù)中的INT((ROW()+4)/3)，col-num為被查找區(qū)域中的列序數(shù)即函數(shù)中的COLUMN()。第2、5、8.......行的行號代入INT((ROW()+4)/3)正好是2、3、4......，COLUMN()在A列為1。因此公式=INDEX(清單!$A:$G,INT((ROW()+4)/3),COLUMN())輸入A列后，A2、A5、A8......單元格的值正好是清單A2、A3、A4......,單元格的值。這樣，表的完整的公式應(yīng)為=IF(MOD(ROW(),3)=0,,IF(MOD(ROW(),3)=1,清單!A$1,INDEX(清單!$A:$G,INT((ROW()+4)/3),COLUMN())))。把此公式輸入A1單元格，然后向下向右填充得到了完整的工資條表。
為了表格的美觀還應(yīng)對格式進行設(shè)置，一般習(xí)慣包括標題、姓名等文字在單元格中要取中，數(shù)字要右置，數(shù)字小數(shù)點位數(shù)也應(yīng)一致，還有根據(jù)個人的愛好設(shè)置邊框。本表格只需對一至三行的單元格進行設(shè)置，然后通過選擇性格式設(shè)置完成全表的設(shè)置。
本工作簿的特點是1、不對清單表進行操作保持清單工作表的完整，2、全工作表只有一個公式通過填充得到全表十分方便。
例如：我的單位不大不小，有200多號人。最近領(lǐng)導(dǎo)要求把每個員工個人的工資情況打出來，分發(fā)給每位員工。每個員工的工資條上只能有兩行內(nèi)容：一行是分解的項目內(nèi)容，如基本工資、崗位工資、總計等等；另一行是對應(yīng)第一行的具體工資數(shù)額。
可以這樣解決：Sheet2
1.為A1命名為K
2.為A3:A250命名為XX
從A3貼上主索引,數(shù)據(jù)要連續(xù)中間不允許有空格 3.寫公式=VLOOKUP(K,DATA,2,0)
有幾個字段寫幾個,位置隨您高興擺
4.隱藏A欄
**************************************************************************************
Sub 打印()
Application.ScreenUpdating = False '屏幕不更新
Dim c As Object '宣告c為對象,請準備空間
[xx].Select '選取變量范圍
Set c = ActiveCell '設(shè)定c對象為作用單元格
Do Until IsEmpty(c.Value) '作Do循環(huán)直到無值時跳出
[k].Value = c.Value
Set c = c.Offset(1, 0) '設(shè)定c往下進一格再取主索引值
Sheets(Sheet2).PrintPreview '工作表直接打印改PrintOut
Loop
End Sub
另一回答：我是做人事管理的也遇到過你的問題,我用如下方法解決十分方便,而不用任何代碼.
方法的原理是調(diào)整打印機的自定義紙張大小到恰好顯示一個人的工資條的大小,請按如下：
如：我的excel工資表將項目內(nèi)容放在第一、二行，行高為20.1，用a4紙橫向打印
1、在頁面設(shè)置中將上、下邊距，頁眉、頁腳均設(shè)為零，
在頁面設(shè)置--工作表---頂端標題行 中輸入 $1:$2,即將放在
第 一、二行的項目內(nèi)容設(shè)成每頁標題行
打印方向為橫向
2、、在文件----打印---屬性---紙張----自定義中將紙張的
寬度=280 （單位：毫米）
長度=2970 （單位：毫米）
3、打印時可選1---200頁，即可打印200人的工資條，一張a4可打10人
確定后預(yù)覽，可調(diào)整下邊距至每頁顯示一張工資條
我的解決辦法：我只用了一個公式: if(mod(row(),3)=0,,if(mod(row)(),3)=1,sheet1!a$1,index(sheet1!$a:$g,int((row()+4/3),cllolumn())))你試一下
(解釋：int((row()+4/3) 是這個意思：一個工資表，有列標題，接下來是工資記錄。而我在此表基礎(chǔ)上，加一個自動生 成的工資 條表， mod(row(),3)=0，在此表上用這個表示第三行保留空白行； if(mod(row)(),3)=1,sheet1!a$1，表示是第一行取標題列； index(sheet1!$a:$g,int((row()+4/3),column()）這是關(guān)鍵的地方：是指它不是第一行，也不三倍數(shù)的行，是記錄 行的表示，你想第二行顯示記錄，則2+4/3=2 取工資 表的第二行記錄；第五行顯示記錄，則5+4/3=3 取工資 表的第三行記錄；第八行顯示記錄，則8+4/3=4 取工資 表的第四行記錄；第十一行顯示記錄，則11+4/3=5 取工資 表的第五行記錄；這個公式你可以根據(jù)具體情況變化：尤其是((row()+4/3),中的4這個數(shù)字，
定制單元格數(shù)字顯示格式
定制單元格數(shù)字顯示格式，先選擇要定制的單元格或區(qū)域，》單擊鼠標右鍵》單元格格式》選擇‘?dāng)?shù)字’選項》選擇‘自定義’》在“類型”中輸入自定義的數(shù)字格式。
如何輸入自定義的數(shù)字格式：需要先知道自定義格式中那些常用符號的含意，具體可以先不選擇‘自定義’，而選擇其它已有分類觀看‘示例’，以便得知符號的意義。
比如：先選擇‘百分比’然后馬上選擇‘自定義’，會發(fā)現(xiàn)‘類型’中出現(xiàn)‘0.00%’，這就是百分比的定義法，把它改成小數(shù)位3位的百分比顯示法只要把‘0.00%’改成‘0.000%’就好了，把它改成紅色的百分比顯示法只要把‘0.00%’改成‘[紅色]0.00%’就好了。
關(guān)于數(shù)據(jù)引用的問題
在一個工作簿中，假如A工作表中的單元格E8被B工作表中的某單元格引用，現(xiàn)在由于A工作表中插入了行，原來的E8現(xiàn)在可能是E28,結(jié)果造成B工作表引用數(shù)據(jù)錯誤，請問：如何可以使B工作表中的引用隨著A表的變化也作相應(yīng)的自動調(diào)整？
回答：利用“相對引用”來實現(xiàn)，“相對引用”是Excel中默認的引用方式
例：在工作表Sheet1中Ｃ２單元格為５，Ｄ２單元格為６，在工作表Sheet2中Ｃ３單元格中輸入“＝Sheet1!C2+Sheet1!D2”，如果把
Ｃ２單元格剪切到Ｃ３，那么在工作表Sheet2中Ｃ３單元格中的公式就自動變?yōu)椤埃絊heet1!C3+Sheet1!D2”
如何使EXCEL應(yīng)用程序鎖定不讓人打開
請問我以下的操作應(yīng)再如何修改成如密碼輸入錯誤則退出EXCEL.這是我編寫在PERSONAL.XLS中的一個模塊.代碼如下,請各高手幫忙為小弟指點一二,不甚感激!
Sub auto_Open()
MsgBox 熱列歡迎來海源,你吃飽了嗎？
If Application.InputBox(請輸入操作權(quán)限密碼:123) = 123 Then
Else
'.....(在這一步中,我想退出EXCEL,但無法實現(xiàn)請高手指點一二)
End If
End Sub
解答：Private Sub Workbook_Open()
MsgBox 熱列歡迎來海源,你吃飽了嗎？
If Application.InputBox(請輸入操作權(quán)限密碼:123) = 123 Then
Exit Sub
Else
Application.Quit
End If
End Sub
程香宙的修改：
Sub auto_Open()
MsgBox 程香宙歡迎你的到來, vbQuestion, 聯(lián)系電話：
If Application.InputBox(請輸入操作權(quán)限密碼:, 系統(tǒng)登陸) = 123 Then
Else
MsgBox 密碼錯誤,請重輸, vbCritical + vbOKOnly, 你還有兩次機會
If Application.InputBox(請輸入操作權(quán)限密碼:, 系統(tǒng)登陸) = 123 Then
Else
MsgBox 密碼錯誤,再給你一次機會!, vbCritical + vbOKOnly, 你還有一次機會
If Application.InputBox(請輸入操作權(quán)限密碼:) = 123 Then
Else
MsgBox 你無權(quán)進入本系統(tǒng)!請向程香宙申請密碼!, vbCritical + vbOKOnly, 你沒有機會啦!
Application.Quit
End If
End If
End If
End Sub
數(shù)組的運算法則
有excel表如下：
姓名 成績 /王娟 優(yōu) /永生 中 /閔生剛 優(yōu) /朱智銳 中 /胡強強 良 /金龍鱗 優(yōu) /張正梅 中 /汪欲生 良 /閔生剛 中 /王娟 優(yōu) /張正梅 優(yōu) /閔生剛 中 /永生 良 /王娟 良 /其中姓名在sheet1頁的a列，成績在c列。我想在sheet2頁中建立一個表如下：
姓名 統(tǒng)計優(yōu)數(shù) /胡強強 /金龍鱗 /閔生剛 /汪欲生 /王娟 /永生 /張正梅 /朱智銳 /同樣姓名在sheet1的a列，統(tǒng)計在c列，這里的姓名已經(jīng)整理為沒有重復(fù)的姓名。要統(tǒng)計出每人獲得優(yōu)的數(shù)目。請問用什么函數(shù)能解決。我用了if((sheet1!c2)=優(yōu),countif(sheet1!a:a,a2)),結(jié)果是統(tǒng)計的姓名數(shù)，而不是成績數(shù)。如果能統(tǒng)計出來，哪么就是一個動態(tài)的當(dāng)sheet1中數(shù)據(jù)變化時，sheet2中數(shù)據(jù)應(yīng)到跟著變化。
有位朋友指導(dǎo)采用下面的式子把上面的問題解決了。這里假設(shè)最大記錄數(shù)為100
=sum((sheet1!$a$2:$a$101=$a2)*(sheet1!$c$2:$c$101=優(yōu))*1) ，按Ctrl+Shift+Enter ，但是這是用到了數(shù)組運算，請問數(shù)組運算的規(guī)則是什么，看到許多地方都可以用數(shù)組解決，但不知其所以然。幫助文件中也沒說運算規(guī)則。如上式中為什么用*號？謝謝
解答：對于數(shù)組公式的含義
sum((sheet1!$a$2:$a$101=$a2)*(sheet1!$c$2:$c$101=優(yōu))*1)
我們來一部分，一部分的講：
1、(sheet1!$a$2:$a$101=$a2)
表示用 sheet1!$a$2:$a$101 區(qū)域中的每一個單元格中的內(nèi)容與 $a2 單元格的內(nèi)容進行比較，如果相同結(jié)果為“True”，否則為“False”。
2、(sheet1!$c$2:$c$101=優(yōu))
表示用 sheet1!$c$2:$c$101 區(qū)域中的每一個單元格中的內(nèi)容與 字符串優(yōu)進行比較，如果相同結(jié)果為“True”，否則為“False”。
3、最后一部分乘以1。是強制Excel將“True”或“False”轉(zhuǎn)換為數(shù)值“1”或“0”，以便sum函數(shù)可以求和。
4、至于第一部分和第二部分之間的乘號（*)的目的是，如果第一部分或者第二部分有一個的結(jié)果是“False”，那么Excel將其轉(zhuǎn)換為數(shù)值“0”，相乘結(jié)果為零，表示不在求和范圍內(nèi)。
替換數(shù)據(jù)
請教各位如何用將一組數(shù)據(jù)，如：6550894， 9852547， 2656032， 7461136， 0505867， 5564892， 7235580，0421077，我需要把數(shù)據(jù)中的數(shù)字1，3，5換為符號A表示，2，4，6換為符號B表示，依此類推將數(shù)據(jù)中的阿拉伯?dāng)?shù)字0~9分為幾類用其它符號替換。
解答：方法1。假設(shè)：B13值為9550894。在B14中輸入=IF(ISERROR(FIND(MID($B$13,1,1),135)),IF(ISERROR(FIND(MID($B$13,1,1),246)),IF(ISERROR(FIND(MID($B$13,1,1),79)),IF(ISERROR(FIND(MID($B$13,1,1),80)),,D),C),B),A) ?！14中MID()第二個參數(shù)為2，以此類推...最后在目標單元格中輸入：=CONCATENATE(B14,C14,D14,E14,F14,G14,H14)
方法2：表一:
AB...
11234567890
2ABABABCCCD
=SUBSTITUTE(A4,A$1,A$2)
說明:先列一個替換表,如表一,在A4處填如數(shù)據(jù),在B4處填如上述公式=SUBSTITUTE(A4,A$1,A$2),并向右拖動9個同樣的公式,最后一個便是結(jié)果.在將該10個相同的公式向下拖,便得到其它的結(jié)果.好處:可以修改表一,產(chǎn)生變化.
方法3：你可以把全部數(shù)據(jù)拷貝到WORD中，再用替換命令，想怎么換就怎么換，然后在拷貝回來。
幾個技巧
用“Ctrl+：”輸入時間 ；用“Ctrl+；”輸入日期 ；用“Ctrl+`”顯示當(dāng)前工作表的單元格引用情況（如果引用的話），再次按下“Ctrl+`”則回到正常的顯示狀態(tài)（別漏了那個點）；“Ctrl+1”：打開“單元格格式”對話框，按下ESC鍵關(guān)閉該對話框 ；“Ctrl+ -”：打開“刪除”對話框，按下ESC鍵則關(guān)閉該對話框；熱鍵ctrl+2：字體加粗或取消加粗； ctrl+3：字體加斜或取消加斜； ctrl+4：加下滑線或取消； ctrl+5：加刪除線或取消； ctrl+9：隱藏當(dāng)前行； ctrl+0：隱藏當(dāng)前列；在Cell里，輸入公式，比如 =trunc，按CTRL+SHIFT+A，出現(xiàn)函數(shù)參數(shù)說明；按CTRL+A，出現(xiàn)wizard ?？焖偬畛洌哼x擇要填充的單元，輸入公式或數(shù)值后，按CTRL+Enter。將圖形與某個cell的數(shù)據(jù)聯(lián)系在一起：a) 選擇繪圖菜單欄的任一圖形 b) 在公式欄里，或按F2，輸入到某個cell的聯(lián)接，比如=A6 c) 回車。粘貼鏈接圖片：a) 選擇某區(qū)域(比如A2:C7) b)復(fù)制 c)按住 SHIFT，點選編輯-->粘貼鏈接圖片 。對長公式進行錯誤查找：點擊公式，按F9，出現(xiàn)出錯的部分。ESC復(fù)原，CTRL+Z為undo。
如何在兩個工作表之間進行數(shù)據(jù)交換
我有兩個工作表，第一個表A列是姓名，B列是編號，第二個表A列也是姓名，但是順序和第一個表的A列不一樣，我想在第二個表的B列也加入編號 。
解答：用VLOOKUP函數(shù)： =VLOOKUP(A2,Sheet1!A:B,2,FALSE)，依次向下拖動
顯示數(shù)值所在的單元格號
假如有A列和B列兩列數(shù)字，如何找出A列的數(shù)字在B列中所在的位置， 并在第三列顯示單元格號。
解答：假設(shè)數(shù)據(jù)在A1:B10，則C列公式為：=MATCH(B1,$A$1:$A$10,0)
我想根據(jù)題意應(yīng)該為：=MATCH(A1,$B$10,0)
if 超過7層如何辦
1. 將七層之外的IF語句，放在另外的單元格內(nèi)來處理，例：C5=if(if,...,(if...),B5))),B5單元格就是存放七層之外的IF語句。依此類推，可以實現(xiàn)在數(shù)據(jù)庫語言中CASE語句的功能。
2. IF 函數(shù)的確有七層嵌套的限制。遇到七層嵌套還解決不了的問題，可以嘗試用其它的函數(shù)組合和數(shù)組公式來解決；有時用 VBA 方案可以有很好的效果。
這里給出一個解決IF函數(shù)嵌套超出范圍的方法，可能比較容易使初學(xué)者看懂。其思路是：一個單元格做不了的事，分給兩個或更多的單元格來做，文字內(nèi)容是這樣，函數(shù)內(nèi)容也是這樣。
例子：假如 A1＝1，則 B1＝A；A1＝2，則 B1＝B …… A1＝26，則 B1＝Z
解決方法如下：
B1 = IF(A1=1,A,IF(A1=2,B,IF(A1=3,C,IF(A1=4,D,IF(A1=5,E,IF(A1=6,F,IF(A1=7,G,IF(A1=8,H,C1))))))))
C1 = IF(A1=9,I,IF(A1=10,J,IF(A1=11,K,IF(A1=12,L,IF(A1=13,M,IF(A1=14,N,IF(A1=15,O,IF(A1=16,P,D1))))))))
D1 = IF(A1=17,Q,IF(A1=18,R,IF(A1=19,S,IF(A1=20,T,IF(A1=21,U,IF(A1=22,V,IF(A1=23,W,IF(A1=24,X,E1))))))))
E1 = IF(A1=25,Y,IF(A1=26,Z,超出范圍))
根據(jù)情況，可以將 C、D、E 這些從事輔助運算的單元格放在其它任何地方
3. 一個單元格也可以實現(xiàn)
=IF(A1=1,A,IF(A1=2,B,IF(A1=3,C,IF(A1=4,D,IF(A1=5,E,IF(A1=6,F,IF(A1=7,G,IF(A1=8,H,))))))))&IF(A1=9,I,IF(A1=10,J,IF(A1=11,K,IF(A1=12,L,IF(A1=13,M,IF(A1=14,N,IF(A1=15,O,IF(A1=16,P,))))))))&IF(A1=17,Q,IF(A1=18,R,IF(A1=19,S,IF(A1=20,T,IF(A1=21,U,IF(A1=22,V,IF(A1=23,W,IF(A1=24,X,))))))))&IF(A1=25,Y,IF(A1=26,Z,))(數(shù)組形式輸入)。
4. 以一例：a1=1,2,3,4,5,6,7,8,9,10
b=if(a1=1,一,if(a1=2,二,if(a3=3，三,.......if(a1=9,九,if(a1=10,十)))))),if超過7層不起作用，我該如何辦
解答：1、b=if(a1>5,if(a1=6,六。。。。。。))，明白意思？就是截為兩段再做判斷，這樣可以不超過7重。
2、可以用自定義數(shù)字格式。也可以用=CHOOSE(A1+1,一二三四五六七八九十)
問：實際上我的要求是現(xiàn)行高一成績統(tǒng)計中:b=if(a1=語文,語文,if(a1=數(shù)學(xué),數(shù)學(xué),if(a1=英語,英語,if(a1=物理,物理,if(a1=化學(xué),化學(xué),if（a1=歷史,歷史,if(a1=政治,政治,if(a1=生物,生物“，if(a1=地理,地理)))))))),這樣超過了7層。我不知如何處理。因為下面的公式要引用語、數(shù)、英、等。
答：新建一表，取名Data，找一區(qū)域設(shè)置名稱為SubjectTable：
語文 Chinese
英語 English
..
=vlookup(SubjectTable,a1,2,false)可以有65536個，夠了吧。其實，稍加改進，理論上，可以有達到你硬盤空間的個數(shù)?；蛴胕f和or的組合可以解決15個。
再舉個例子：
=IF(A16=,,IF(B16=,樣辦尚未交,IF(OR(B16=內(nèi)部檢查中,B16=數(shù)據(jù)查詢中,B16=數(shù)據(jù)查詢中),CONCATENATE(IF(B16=內(nèi)部檢查中,品質(zhì)檢測中,),IF(B16=數(shù)據(jù)查詢中,圖紙未確認,),IF(B16=為不合格,需要修正,)),CONCATENATE(IF(C16=客戶檢查中,待客回復(fù),),IF(C16=合格,待P/O生產(chǎn),),IF(C16=取消,客戶取消,),IF(C16=為客戶設(shè)變中,客戶設(shè)變中,),IF(C16=不合格,需要修正,)))))
一個單元格內(nèi)格式問題
如果我做了一個表某一列是表示重量的，數(shù)值很多在1--------------1524745444444之間的數(shù)不等。這些表示重量的數(shù)。如果我想次給他們加上單位，但要求是單位是>999999噸，之下>999是千克，其余的是克。如何辦
答：[>9999]###.00,噸;*,*.00千克
怎樣用函數(shù)求出一定范圍內(nèi)的數(shù)值的偶數(shù)的個數(shù)
解答：1設(shè)你的數(shù)據(jù)區(qū)域為A1：A30
{=COUNT(IF(A1:A30/2/2=INT(A1:A30/2),A1:A30))}
如果你的數(shù)據(jù)區(qū)域內(nèi)還包括空白格你不想計算在內(nèi)的話， {=COUNT(IF(A1:A30/2/2=INT(A1:A30/2),A1:A30))-COUNTBLANK(A1:A30)}
或：{=sum((even(a1:a30)=a1:a30)*1)}
如何使某列的數(shù)據(jù)不重復(fù)
1、 我做了個宏，可惜在數(shù)據(jù)量大時（超過1000時）速度狂忙，各位幫我修改修改
Sub 檢查重復(fù)項()
Dim i As Integer
Dim j As Integer
num = Selection.Cells.Count
For i = 1 To num
For j = i + 1 To num
If (Selection.Cells(i) = Selection.Cells(j)) Then
MsgBox Selection.Cells(i).Value
End If
Next j
Next i
MsgBox 檢索完畢
End Sub
2、 假設(shè)數(shù)據(jù)在B列
選中B列，[數(shù)據(jù)]-[數(shù)據(jù)有效性]-[自定義]
在“公式”輸入框中鍵入：=COUNTIF(B:B,B1)=1。
請問如何能使隨機數(shù)不重復(fù)
我在30個單元格里使用了randbetween(1,500),隨機抽取了1-500之間的任意數(shù),但問題是這30個數(shù)中仍有機會出現(xiàn)重復(fù)請問如何才能使這些隨機數(shù)不重復(fù)呢?
答：1、勾選迭代計算，A1輸入如下公式： =IF(COUNTIF($A$1:$E$6,A1)=1,A1,RANDBETWEEN(1,500))
如果某格出現(xiàn)0值，點選旁邊單元格拖一下即可。
2、用上面的方法得到的隨機數(shù)不會變化，如何做到想變就變？增加一個條件語句，即可達到此效果，公式如下： =IF(COUNTIF($A$1:$E$6,A1)=1,IF($G$1=1,RANDBETWEEN(1,500),A1),RANDBETWEEN(1,500)) G1為控制格，當(dāng)在G1輸入數(shù)字1，就開始變。變化后的數(shù)據(jù)有可能不惟一了，怎么辦？把1清除即可！
日期型編號
想在單元格里輸入1，產(chǎn)生0207121這樣一個數(shù)字，02是2002年，07是7月份，12是12日，1是輸入的數(shù)值
答：如a1輸入1，要在b1生成你要的數(shù)字，你可以在b1設(shè)公式=text(today(),yymmdd)&a1
如何將數(shù)字改變?yōu)樽址?br>答：=TEXT(A1,@)
在Excel中如何自定義數(shù)字格式
雖然Excel為用戶提供了大量的數(shù)字格式，但還是有許多用戶因為工作、學(xué)習(xí)方面的特殊要求，需要使用一些Excel未提供的數(shù)字格式，這時我們就需要利用Excel的自定義數(shù)字格式功能來幫助實現(xiàn)用戶的這些特殊要求。
一、在Excel中創(chuàng)建自定義數(shù)字格式的方法
1、選擇要設(shè)置格式的單元格或單元格區(qū)域。
2、單擊“格式”菜單中的“單元格”命令，然后單擊“數(shù)字”選項卡。
3、在“分類”列表中，單擊“自定義”選項。
4、在“類型”框中，編輯數(shù)字格式代碼以創(chuàng)建所需的格式。
在Excel自定義數(shù)字格式使用如下的表達方式：
正數(shù)的格式 負數(shù)的格式 零的格式 文本的格式
#,##0.00 [Red]-#,##0.00 0.00 TEXT@
在Excel自定義數(shù)字格式的格式代碼中，用戶最多可以指定四個節(jié)；每個節(jié)之間用分號進行分隔，這四個節(jié)順序定義了格式中的正數(shù)、負數(shù)、零和文本。如果用戶在表達方式中只指定兩個節(jié)，則第一部分用于表示正數(shù)和零，第二部分用于表示負數(shù)。如果用戶在表達方式中只指定了一個節(jié)，那么所有數(shù)字都會使用該格式。如果在表達方式中要跳過某一節(jié)，則對該節(jié)僅使用分號即可。
二、自定義數(shù)字格式實例
(一)自動添加文本
在日常財務(wù)工作中，常常需要在金額數(shù)字后加單位“元”，這時就可以使用“0.00元”的自定義數(shù)字格式，當(dāng)用戶在單元格中錄入數(shù)字后，Excel就會自動在數(shù)字后加上單位“元”。
如果需要將單元格中錄入的數(shù)字“32”自動轉(zhuǎn)換成“高三十二班”格式，只要將單元格的數(shù)字格式定義為“高[DBNum1]G/通用格式班”即可。
(二)在自定義數(shù)字格式中使用顏色
要設(shè)置格式中某一部分的顏色，只要在該部分對應(yīng)位置用方括號鍵入顏色名稱或顏色編號即可。Excel中可以使用的顏色名稱有[黑色]、[藍色]、[青色]、[綠色]、[洋紅]、[紅色]、[白色]、[黃色]八種不同的顏色，此外Excel還可以使用[顏色X]的方式來設(shè)置顏色，其中X為1-56之間的數(shù)字，代表了56種不同的顏色。
例如：當(dāng)用戶需要將單元格中的負數(shù)數(shù)字用藍色來表示，只要使用“#,##0.00;[藍色]-#,##0.00”自定義數(shù)字格式，用戶在單元格中錄入負數(shù)時，Excel就會將數(shù)字以藍色顯示。
(三)在自定義數(shù)字格式中使用條件格式
在Excel自定義數(shù)字格式中用戶可以進行條件格式的設(shè)置。當(dāng)單元格中數(shù)字滿足指定的條件時，Excel可以自動將條件格式應(yīng)用于單元格。Excel自定義數(shù)字格式中可以使用如下六種標準的比較運算符：
運算符 含義
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 不等于
在Excel中要想設(shè)置滿足指定條件數(shù)字的格式，在自定義數(shù)字格式代碼中必須加入帶中括號的條件，條件由比較運算符和數(shù)值兩部分組成。
例如：在學(xué)生成績工作表中，當(dāng)我們想以紅色字體顯示大于等于90分的成績，以藍色字體顯示小于60分的成績時，其余的成績則以黑色字體顯示，這時只需將自定義數(shù)字格式設(shè)置為“[紅色][>=90];[藍色][<60];[黑色]”即可。
值得注意的是，當(dāng)你在以后需要繼續(xù)使用剛才所創(chuàng)建的成績條件自定義數(shù)字格式時，你會發(fā)現(xiàn)在“單元格格式”的“自定義”分類類型中找不到“[紅色][>=90];[藍色][<60];[黑色]”格式，這是因為Excel自動將你所創(chuàng)建的“[紅色][>=90];[藍色][<60]”格式修改成“[[紅色][>=90]G/通用格式;[藍色][<60]G/通用格式;[黑色]G/通用格式”，你只需選擇此格式即可達到同樣的使用效果。
(四)隱藏單元格中的數(shù)值
在Excel工作表中，有時為了表格的美觀或者別的因素，我們希望將單元格中的數(shù)值隱藏起來，這時我們使用“;;;”(三個分號)的自定義數(shù)字格式就可達到此目的。這樣單元格中的值只會在編輯欄出現(xiàn)，并且被隱藏單元格中的數(shù)值還不會被打印出來,但是該單元格中的數(shù)值可以被其它單元格正常引用。
在單元格顯示負數(shù)為紅字后又緊跟漢字
解答：1、這個使用宏可以解決，但單元格開始請不要帶單位。單個單元格宏代碼如下：
Sub 宏1()
'
' 宏1 Macro
' Dent 記錄的宏 2002-2-16
'
Dim str1 As String
str1 = Str(ActiveCell.Value)
If Val(str1) < 0 Then
str1 = Abs(Val(str1)) & 文字
ActiveCell.Value = str1
ActiveCell.Font.Color = vbRed
End If
End Sub
2、用單元格格式解決： #,##0.00;[紅色]-#,##0.00文字
檢索問題
在一個工作表中，有客戶名稱、收款日期等字段名及相應(yīng)數(shù)據(jù)?，F(xiàn)在想為了檢驗收款日期是否到期，要求在工作表的某一空白單元格中鍵入某一日期 ，則此單元格以下的單元格可以顯示收款日期字段中所有在此日期前的日期清單，請問如何實現(xiàn)？并要求不使用篩選功能。
解答： Sheet2!A1命名為X,鍵入日期
Sheet1:客戶名稱、收款日期等字段名及相應(yīng)數(shù)據(jù)
插入名稱定義：XX=OFFSET(Sheet1!$A$2,,,MATCH(X,Sheet1!$B$2:$B$65536)-1,2)
XX即是您所需要之范圍
vba寫在This Workbook
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error GoTo 1: Target.Select
Range(Target.Value).Copy
ActiveSheet.Paste Link:=False
Application.CutCopyMode = False
1
End Sub
**********************************************************************************
Sheet2任意儲存, 格鍵入xx按ENTER
[比較一下并沒有比篩選功能快],反而復(fù)雜
SHEET2隨便貼張圖
鍵入公式=XX
微軟到底對我們隱藏了多少
NumberString 這個函數(shù)是否一直存在但沒有解釋？NumberString(1234567890,1)就像自定格式[DBNum1]一樣，它還有2的選擇。其實這些特別用途的函數(shù)是否中文板設(shè)計時特別隱藏。測試結(jié)果：
NumberString(1234567890,1) ＝ 一十二億三千四百五十六萬七千八百九十
NumberString(1234567890,2) ＝ 壹拾貳億叁仟肆佰伍拾陸萬柒仟捌佰玖拾
NumberString(1234567890,3) ＝ 一二三四五六七八九○
DateString()相信亦是一特別Lotus函數(shù)。
DATESTRING(23-Sep- 2002) = 2002年09月23日
DATESTRING(23Sep2002) = 2002年09月23日
DATESTRING(9/23/2002) = 2002年09月23日
DATESTRING(9-23-2002) = 2002年09月23日
求一組數(shù)中第一個出現(xiàn)的正數(shù)
單元格A1—G1中存放一組數(shù)，如何使A2的值等于這組數(shù)中第一個出現(xiàn)的正數(shù)（按A1—G1的順序），若無正數(shù)則A2＝0.
解答：=IF(A1<=0,IF(B1<=0,IF(C1<=0,IF(D1<=0,IF(E1<=0,IF(F1<=0,IF(G1<=0,,G1),F1),E1),D1),C1),B1),A1)
或：{=IF(COUNTIF(A1:G1,>0)=0,0,INDIRECT(ADDRESS(1,SMALL(IF(A1:G1>0,COLUMN(A1:G1)),1))))}
能否使表格不能打印
解答：Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub
(用法：打開Excel,按Alt+F11,調(diào)出VBE后,按Ctrl+r顯示出工程資源管理器(默認此窗口應(yīng)已顯示),雙擊ThisWorkbook,在模塊中粘入代碼即可!要想打印你表,用截圖軟件即可.無最保險辦法)。
有無這樣的函數(shù)
A1為一定范圍內(nèi)的隨機值，B1—B20為一組數(shù)，有無這樣的函數(shù)來確定A2的值？
當(dāng)A1=1時，A2=B1
當(dāng)A1=2時，A2=B2
當(dāng)A1=3時，A2=B3
…………
解答：=INDIRECT(ADDRESS(A1,2))(絕)
能實現(xiàn)兩欄數(shù)據(jù)中按特定條件進行比較并做出相應(yīng)記號嗎
具體意思如下：有四欄數(shù)據(jù)。A欄 B欄 C欄 D欄 F欄
對A欄及C欄數(shù)據(jù)進行查找對比，如果發(fā)現(xiàn)存在A=C，且B=D，則在符合條件的F欄顯示已找到，對于沒有查到的的顯示未找到。
解答：如果是一一對應(yīng)查找則f2=if(and(A2=C2,B2=D2),OK,No),復(fù)制.
如果整體查找：=IF(AND(COUNTIF($C$2:$C$5,A2)>0,COUNTIF($D$2:$D$5,B2)>0),找到,沒有)
如何對文本格式的數(shù)字進行篩選
如80/24；73/56……
解答：要對A列進行篩選，條件1:符號/左邊的數(shù)字>=50;條件2:符號/右邊的數(shù)字>=80,達到任一條件的即被選中。試試這個公式: B1=IF(OR(LEFT(A1,FIND(/,A1)-1)*1>=50,MID(A1,FIND(/,A1)+1,LEN(A1)-FIND(/,A1))*1>=80),A1,)
我想再請教一個問題:在自動篩選中有包含、不包含之類的選項,這類選項在高級篩選中能否實現(xiàn)?
提供以往一個函數(shù)公式,請你研究看是否有參考:
={SUM((ISNUMBER(SEARCH(廣東,$B$3:$B$13)))*($A$3:$A$13=股份制)*($C$3:$C$13))}
這是對B列單位名稱中包含廣東并且A列企業(yè)類型為股份制的,按C列匯總.
如何篩選出特定行
一份工作表，有幾萬行數(shù)據(jù)，現(xiàn)在想每隔50行打印一行，如何進行篩選
解答：插入一空列,輸入=MOD(ROW(),50)并復(fù)制到全數(shù)據(jù)列,再用篩選法把有“0”的單元格選出.
Word加啟動密碼
在Word模板中錄制如下宏文件保存即可:
Sub autoexec()
Dim user
user = InputBox(請輸入軟件啟動密碼)
If user = password Then
Else
MsgBox (非法用戶,將退出本程序.Wu)
Application.Quit
End If
End Sub
有關(guān)大小寫的轉(zhuǎn)換
在EXCEL單元格中有的單元格是小寫字母，有的是大小寫混在一起現(xiàn)在我想全部轉(zhuǎn)為大寫、或全部轉(zhuǎn)為小寫，有哪些方法呢？
利用upper()函數(shù)或還有LOWER。如：a1=aBcD，upper(a1)=ABCD
在Excel中打造自己的“公式保護”菜單
工作中經(jīng)常用到Excel的計算功能，有時一張工作表中需要設(shè)置很多公式，為了防止誤操作就將工作表保護起來，但是如果將含有公式的單元格保護，而其它單元格不鎖定，設(shè)置時需要一個個單元格選定，很麻煩而且容易出錯，就想到建立一個菜單項，將這項工作變成菜單。有幸的是這一切Excel都能完成，具體操作如下：
先將工作表保護，不必輸入密碼。
再錄制一個宏。方法是：打開“工具”欄，單擊“宏”-“錄制新宏”；將其命名為“公式保護”，單擊“保存在”的下拉箭頭，將新宏保存到“個人宏工作簿”。單擊“確定”錄制開始。（注意此后的每一動作都將被錄制，直至“停止錄制”。）
1. 將工作表取消保護；
2. 選定a1單元格，輸入=測試（注：黑體為實際輸入內(nèi)容，標點符號均為英文輸入法狀態(tài),下同）；
3. 單擊工作表左上角的方框選定整個工作表；
4. 單擊“格式”-“單元格”-“保護”項，將“鎖定”和“隱藏”前的方框里的對號去掉；
5. 單擊“插入”-“名稱”-“定義”，當(dāng)前工作表名稱輸入：公式保護，引用位置輸入：=get.cell(4,indirect(rc,false)), 單擊“添加”后“確定”；
6. 單擊“格式”-“條件格式”，在“單元格數(shù)值”下拉框選定“公式”，后面的框內(nèi)輸入=公式保護 ，單擊“格式…”，在圖案標簽內(nèi)選淺綠色或者其它顏色“確定”，再“確定”；
7. 單擊 “編輯”-“定位”-“定位條件…”，選定“公式”項“確定”再“確定”；
8. 重復(fù)步驟4，將“鎖定”和“隱藏”前的方框里的對號選定；
9. 選定a1單元格，取消其內(nèi)容，并重復(fù)步驟4，取消a1的鎖定和隱藏屬性；
10. 單擊工具-保護-保護工作表，將工作表保護（也不必設(shè)密碼）。再單擊“工具”-“宏”-“停止錄制”停止錄制。再打開“工具”-“自定義”項，再“命令”標簽欄內(nèi)“類別”欄中找到“新菜單”并指定，在其右側(cè)的命令框中的新菜單用鼠標按住拖到“工具欄”的“保護”項中的“保護工作表”下面放開，并單擊右鍵將其命名為“公式保護”，將剛才錄制名為“公式保護“的宏指定給它。至此，一個很有用的菜單項就作成了。此后，只要你將鼠標移動到“工具”-“保護”-“公式保護”的位置，工作表將執(zhí)行其所指定的宏，只要你工作表中輸入了公式（以=開頭），含有公式的單元格將自動變?yōu)闇\綠色，提醒你和別人此處有公式，小心編輯，十分醒目。編輯完公式后再次運行該命令就可以此保護工作表并鎖定公式，禁止改動。
如何讓EXCEL自動從頭統(tǒng)計到當(dāng)前單元格
情況如下： C列要根據(jù)A列的內(nèi)容來統(tǒng)計B列的數(shù)據(jù)，范圍從A1：An，即當(dāng)A列中An有數(shù)據(jù)時，Cn自動根據(jù)An的值，統(tǒng)計B1：Bn的數(shù)據(jù)。
解答：=SUM(INDIRECT(B1:B & LARGE((A1:A65535<>)*(ROW(A1:A65535)),1)))，按Ctrl+Shift+Enter。
請問想要取當(dāng)前單元格的列號，用什么函數(shù)
1、=CHAR(64+COLUMN())
2、Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Selection.Columns.Column > 26 Then
tt = Mid(ActiveCell.Address, 2, 2)
Else
tt = Mid(ActiveCell.Address, 2, 1)
End If
MsgBox (tt)
End Sub
3、1的公式就變化一下：=IF(COLUMN()>26,CHAR(64+INT(COLUMN()/26)) & CHAR(64+MOD(COLUMN(),26)),CHAR(64+COLUMN()))
SUMIF函數(shù)
a1至A4是10,24,30,12.B1至B4是8,15,25,35.D1至D4是25,4,3,5.用SUMIF 第一參數(shù)選取A1:B4,第二參數(shù)是>20,第三參數(shù)選取D1:D4.它得出是7。它判斷的是A1:A4 。我的要求是B1:B4 也跟著判斷 , 也就要得出來得是12
解答：{=SUM((A1:A4>20)*(B1:B4>20)*C1:C4)}
怎么能快速的將兩個單元格的內(nèi)容互換
1、
Sub ChangVal()
my1value = ActiveCell.Value
For Each a In Selection
If a.Address <> ActiveCell.Address Then
my2value = a.Value
a.Value = my1value
ActiveCell.Value = my2value
End If
Next a
End Sub
2、用鼠標先選定單元格，點住單元格邊框，并按住SHIFT鍵，然后托拽到隔壁單元格的后面一條邊框處。你能看到被托拽部分會變成灰色的“工”字形，然后放手后，單元格就互換了。
如何能到兩個時間段的17:00-8:00小時數(shù)?
1.已知E4為08011500(為文本格式﹐開始時間﹐意思為8月1日 下午15﹕00)﹐G4為08100900(為文本格式﹐結(jié)束時間﹐意思為8月10日 上午9點) 問﹕如何能得到開始時間到結(jié)束時間（17:00-08:00）的總小時數(shù)?
2. 如何得到開始時間到結(jié)束時間的節(jié)假日時數(shù)?(如5.1﹐10.1﹐星期六﹐星期天)
解答：方法1：=(DATE(YEAR(NOW()),MIDB(AF4,1,2),MIDB(AF4,3,2))-DATE(YEAR(NOW()),MIDB(E4,1,2),MIDB(E4,3,2))-1)*15+IF(MIDB(E4,5,2)+MIDB(E4,7,2)/60<=8,15-(MIDB(E4,5,2)+MIDB(E4,7,2)/60),IF(MIDB(E4,5,2)+MIDB(E4,7,2)/60>=17,24-(MIDB(E4,5,2)+MIDB(E4,7,2)/60),7))+IF(MIDB(AF4,5,2)+MIDB(AF4,7,2)/60<=8,MIDB(AF4,5,2)+MIDB(AF4,7,2)/60,IF(MIDB(AF4,5,2)+MIDB(AF4,7,2)/60>=17,MIDB(AF4,5,2)+MIDB(AF4,7,2)/60-9,8))
方法2：networkdays 需要你提供一個節(jié)假日列表作為參數(shù)。我猜你不會喜歡手工輸入這樣一個表。以下公式功能更為強勁，它可以算出兩個單元格（A2和C2）所儲存的日期之間的周末天數(shù)，乘以小時數(shù)即可計算出你所說的節(jié)假日時數(shù)。但是，它無法計算國慶節(jié)等公眾假期。
=IF(C2>A2,SUM(IF(WEEKDAY(C2-ROW(INDIRECT(1:& C2-A2)),2)>5,1,0)),SUM(IF(WEEKDAY(A2-ROW(INDIRECT(1:& A2-C2)),2)>5,1,0)))。這是一個數(shù)組公式，輸入完成后按CTRL+SHIFT+ENTER結(jié)束。
方法3：用格式定義E4,G4為日期時間格式：****-**-** **:**。然后：(G4-E4)*24 即可得兩時間內(nèi)的小時數(shù)。
如何在單元格返回工作表名稱
答：=RIGHT(CELL(filename),LEN(CELL(filename))-FIND(],CELL(filename)))
如何在輸入數(shù)字的加減乘除按ENTE后能在另一單元格自動出現(xiàn)計算數(shù)值
1、編了個宏:
Sub aa()
Cells(1, 1) = Mid(ActiveCell.Formula, 2, 13)
End Sub
先在單元格里輸入公式得出結(jié)果,選定得出結(jié)果的單元格運行宏就可以在A1出文本.
2、宏(測試通過)
Sub aa()
A = 2 '行'
B = 4 '列
LINE1:
If Cells(A, B) = Then
Exit Sub
Else
Cells(A, B + 1).Formula = = & Cells(A, B)
A = A + 1
GoTo LINE1:
End If
End Sub
有A1，B1，C1，D1四個單元格D1的值要隨著Ａ１的變化而變化
Ａ１有三種變化，一、二、三；當(dāng)Ａ１＝‘一’時，Ｄ１＝“ ”；?。粒保健瘯r，Ｄ１＝（Ｂ１＋Ｃ１）／３ ；?。粒保健瘯r，Ｄ１＝（Ｂ１＋Ｃ１）／６ 。以上可以通過ＩＦ來完成 ?？晌蚁Ｍ氖牵寒?dāng)Ｂ１或Ｃ１為空時，不論Ａ１為什么Ｄ１都為空，這樣做得到么？
解答：1、=IF(OR(B1=,C1=),,IF(A1=一,,IF(A1=二,(B1+C1)/3,IF(A1=三,(B1+C1)/6,))))
2、=IF(OR(B1=, C1=, A1=一), , IF(A1 = 二, (B1+C1)/3, (B1+C1)/6))
3、 如果考慮 A1 沒有數(shù)據(jù)的話：=IF(OR(B1=, C1=, A1=一,A1=), , IF(A1 = 二, (B1+C1)/3, (B1+C1)/6))
對A列不重復(fù)的數(shù)值計數(shù)
我只能做到新建一列，B列，然后第一個單元格countif（$A$1:$A$100,A1)，然后拖動到全部新列。最后在新列下面用sumif(B1:B100,1) 誰有更好地方法。
解答：1、{=SUM(IF($A$1:$A$100=,,1/(COUNTIF($A$1:$A$100,$A$1:$A$100))))}(又問：公式中的“1/(COUNTIF($A$1:$A$100,$A$1:$A$100))”像是一個倒數(shù)，怎么理解？答：用倒數(shù)是這個意思:如果只出現(xiàn)一次，數(shù)組中的相應(yīng)項統(tǒng)計為1，其倒數(shù)為1，Sum統(tǒng)計計1。如果出現(xiàn) N 次，其倒數(shù)為1/N，出現(xiàn)了N次，求和就是Nx1/N，最后Sum統(tǒng)計就只計1。)
永恒的求和
1、=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以對A列數(shù)值自動求和。
2、=SUM(INDIRECT(R2C:R[-1]C,FALSE))
3、=SUM(INDIRECT(A2:A&ROW()-1))
4、 堅持不用R1C1欄名列號表示法還有一個方法，不過又復(fù)雜了些。首先定義一個公式：COL=IF(COLUMN()>26,CHAR(INT((COLUMN()-1)/26)+64)&CHAR(IF(MOD(COLUMN(),26)=0,26,MOD(COLUMN(),26))+64),CHAR(COLUMN()+64))。后于欲求加總之儲存格輸入：=SUM(INDIRECT(COL&1:&COL&ROW()-1))。則此公式復(fù)制到任何一任皆可用，又不怕產(chǎn)生錯誤值。(注：COL=IF(COLUMN()>26,CHAR(MOD(COLUMN(),26)+64)&CHAR(INT(COLUMN()/26)+64),CHAR(COLUMN()+64))。暴露了一個為人不知的缺點，如果列數(shù)到了AA列以后就不行了，雖然可用ADDRESS()解決，比用CHAR()好多了，但公式還是太長，用在一個加法中實在不值)
5、 其實用ADDRESS更好，=SUM(INDIRECT(ADDRESS(1,COLUMN())&:&ADDRESS(ROW()-1,COLUMN())))，還是一句老話，為做一個加法不是太值，這只能是技術(shù)上的討論。
如何使用EXCEL進行動態(tài)跨表取數(shù)
有兩個文件，第一個文件有31張日報表，每天一個表單；第二個文件僅一個表單；如何在第二個文件中，輸入1時由函數(shù)動態(tài)取出第一張表單數(shù)（如SHEET1），輸入2時?。⊿HEET2）數(shù)，依次類推……。，如何設(shè)公式。在同一文件中可以用INDIRECT和ADDRESS組合，可是跨表好象不行，請各位指教一二！
解答：前提是兩個工作表都要打開：=INDIRECT([Book2]Sheet&A1&!$B$1)
如何使用對照表
設(shè)一對照表如下：如何使用函數(shù)當(dāng)輸入。當(dāng)a1=34 則a2顯示乙 b1=68 則b2顯示丁/甲 22 33 44 66..../乙 26 34 43 62.... /丙 28 39 41 67.... /丁 27 31 49 68.... /戊 23 32 46 64.... /己 29 38 47 61.... /更 21 37 48 69.... /辛 24 36 42 63....
解答：如A列輸入的數(shù)字在表中都存在，把你的數(shù)據(jù)放在D到G列，甲乙丙丁戊己辛放在最后一列，即H列。在B列輸入公式“=IF(A1=,,VLOOKUP(A1,INDIRECT(ADDRESS(1,INT(0.1*A1)+IF(A1>60,1,2))):INDIRECT(ADDRESS(8,8)),7-INT(0.1*A1)+IF(A1>60,1,0),FALSE))”。如甲乙丙丁戊己辛放在第一列，即列用公式“=IF(A1=,,INDEX(D:D,MATCH(A1,INDIRECT(ADDRESS(1,INT(0.1*A1)+IF(A1>60,2,3))):INDIRECT(ADDRESS(8,INT(0.1*A1)+IF(A1>60,2,3))),0)))”
解答2：如果數(shù)據(jù)無規(guī)律，可用以下方法：如你的數(shù)據(jù)表在D到H列，在I列輸入公式“=IF(ISERROR(MATCH(A$1,E1:H1,0))=TRUE,0,row())并向下填充，在B1單元格輸入公式“=IF(A1=,,INDEX(D:D,LARGE(I:I,1)))”
解答3：我將解答2公式做了小修改(I列公式刪除)，B1單元格輸入公式：=IF(A1=,,INDEX($D:$D, IF(ISERROR(MATCH(A1,$E$1:$H$1,0))=TRUE,0,ROW($E$1:$H$1))+ IF(ISERROR(MATCH(A1,$E$2:$H$2,0))=TRUE,0,ROW($E$2:$H$2))+ IF(ISERROR(MATCH(A1,$E$3:$H$3,0))=TRUE,0,ROW($E$3:$H$3))+ IF(ISERROR(MATCH(A1,$E$4:$H$4,0))=TRUE,0,ROW($E$4:$H$4))+ IF(ISERROR(MATCH(A1,$E$5:$H$5,0))=TRUE,0,ROW($E$5:$H$5))+ IF(ISERROR(MATCH(A1,$E$6:$H$6,0))=TRUE,0,ROW($E$6:$H$6))+ IF(ISERROR(MATCH(A1,$E$7:$H$7,0))=TRUE,0,ROW($E$7:$H$7))+ IF(ISERROR(MATCH(A1,$E$8:$H$8,0))=TRUE,0,ROW($E$8:$H$8))))。
解答4：在B1單元格輸入公式：{=IF(COUNTIF($E$1:$G$8,A1)=0,查無資料,INDEX($D$1:$D$8,SUM(IF($E$1:$H$8=A1,ROW($E$1:$H$8)))))}
又問：當(dāng)數(shù)據(jù)區(qū)域有重復(fù)數(shù)據(jù)時，就得不到正確結(jié)果。因為，在這里你的SUM()返回的只是對一個數(shù)據(jù)求和。如果有重復(fù)數(shù)據(jù)，怎樣才能得到正確結(jié)果呢？
答：在B1單元格輸入公式：=IF((COUNTIF($E$1:$H$8,$A$1)=0)
+(COUNTIF($E$1:$H$8,$A$1)
< ROW( )), ,
INDEX($D$1:$D$8,SMALL(IF($E$1:$H$8=$A$1,ROW($E$1:$H$8)),ROW())))
再往下拖曳，就可依序顯示了。真是快瘋了改了十幾次，有些莫名其妙，進來編輯看公式是完整的可是發(fā)表後又老是缺東缺西的，只好多加些空白或強迫分段處理，請使用者自行修改。
或：如有重復(fù)數(shù)據(jù),則顯示數(shù)據(jù)重復(fù)表示,代表要修改數(shù)據(jù)。{=IF(COUNTIF($E$1:$H$8,A1)=0,查無資料,IF(COUNTIF($E$1:$H$8,A1)>1,資料重複,INDEX($D$1:$D$8,SUM(IF($E$1:$H$8=A1,ROW($E$1:$H$8))))))}
如何在單元格返回工作表名稱
解答：=RIGHT(CELL(filename),LEN(CELL(filename))-FIND(],CELL(filename)))
何在一列數(shù)據(jù)中統(tǒng)計限定范圍的數(shù)據(jù)
請教各位：現(xiàn)A列有數(shù)字(包括絕對值為0的數(shù)字)、文本、空格,要統(tǒng)計數(shù)值為14到35(包括14與35)的個數(shù)；還要統(tǒng)計數(shù)值>35并<14的個數(shù)(不包括0),分別該用什么函數(shù)?
答：>13 and <36 公式=COUNTIF(A:A,>13)-COUNTIF(A:A,>35)
(>35 or <14) and <> 0 公式=COUNTIF(A:A,>35)+COUNTIF(A:A,<14)-COUNTIF(A:A,=0)
|
