Excel函數應用實例

  守護時間 2017-05-14 05:01

統計偶數單元格合計數值

解答:統計F4到F62的偶數單元格合計數值 。公式一{=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))}

公式二 (要有安裝’分析工具箱’){=SUM(IF(ISEVEN(ROW(F4:F62)),F4:F62))}

用EXCEL做統計

如:A1,B1單元格是時間類型,C1是數字類型,我要計算費用用(B1-A1)*C1得到的數據還是時間類型、怎麼辦 ?我希望把時間類型變為整數類型,如0:50(50分鐘)*3(元/H)=2.5元

解答:設A1為3:30,B1為4:30,C1為3,D1為下列輸入的函數: =HOUR(B1)*60+MINUTE(B1)-(HOUR(A1)*60+MINUTE(A1)) 。D1結果等於60(分鐘)

根據A1的內容,決定A2的數值是來自sheet1,sheet2還是sheet3.

解答:if(a1=1,sheet1!a1,if(a1=2,sheet2!a1,if(a1=3,sheet3!a1)))

直接輸入一組數如「20020213101235」後,自動轉換成日期格式

解答:A1中輸入,B1中轉換。。B1=Left(A1,4)&”—”&MID(A1,5,2)&”—”&MID(A1,7,2)&” “&MID(A1,9,2)&”:”&MID(A1,11,2)&”:”&MID(A1,13,2)

把sheet1到sheet200的a19這一格,依序貼到sheet0的a1到a200

解答:方法一:公式

可在A1儲存格輸入以下公式,再行拖曳至A200即可。

=INDIRECT(“Sheet”&ROW()&”!A19″)

方法二:VBA

Sub Macro1()

‘選擇工作表 sheet0

Sheets(“sheet0”).Select

For r = 1 To 200

‘將工作表 1~200 裡面的 D17 復制到 sheet0 的 A1~A200

Cells(r, 1) = Worksheets(CStr(r)).Range(“D17”)

On Error Resume Next

Next r

End Sub

如果有文本串”YY0115″,我想取第三、四的值”01″,應該用什麼函數

解答:1、=mid(“YY0115″,3,2)&””

在一個表中有兩列日期型數字請問如何在第三列中得到其差(兩日期間的天數)

答:=IF(A1>B1,DATEDIF(B1,A1,”d”),DATEDIF(A1,B1,”d”))

重要的EXCEL文件壞了(文件帶密碼)有沒有EXCEL修復工具

解答:Excel 2000

數據>>取得外部數據>>新增數據庫查詢>>Excel File*>>找到檔案>>

[選項]勾選所有選項>> 會找到所有未命名[區塊]如Sheet1$

>>找到字段>>其它跟著查詢精靈導引一步一步作

此中間層組件叫作Microsoft Query

可以用來拯救[毀損檔案]

怎樣用函數來顯示某月的最後一天是幾號

答:= DAY(DATE(年份,月份+1,1)-1)

請問如何在函數中同時使用兩個條件

例:在IF同時使用條件B1>0和B1<10

解答:and(B1>0,B1<10)

用TRIM把「中 心 是」中間的空格去掉

解答:用SUBSTITUDE()函數,多少空格都能去掉。如A1中有:中 心 是 則在B1中使用=SUBSTITUTE(A1,” “,””)就可以了。注意:公式中的第一個「 」中間要有一個空格,而第二個「」中是無空格的。

如何統計某個列有數字的個數

解答:=COUNT(A:A)

如何統計此次自動篩選 出來共有的記錄條數

解答:用 counta 統計

如何判斷某個單元格包含某個字符

解答:設A1=LOVE,查找字母L是否在A1中, =IF(ISERROR(SEARCH(“L”,A1)),”NO”,”YES”)

在單元格返回工作表名稱

解答:函數方法: =MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,100)

使用vlookup函數的問題

當時有兩千多人的考試成績要與花名冊掛接,考試成績放在sheet km1中,花名冊放在sheet hmc中,他們共有字段為準考證號,我的想法是根據準考證號,用vlookup函數查找相應的成績並放在相應的人員下。

sheet km的準考證號放在第一列,考試成績放在第二列,查找範圍是$a$2:$b$2265,sheet hmc的準考證號黨在第一列。

公式為:

vlookup(a2,km!$a$2:$b$2265,2,false)

公式應該沒什麼問題,但只能找到很少的紀錄(<60),究竟是什麼地方除了問題,請高手指點!

解答1:可以用SUMIF函數解決:

=SUMIF(km!$A$2:$B$2131,A2,km!$B2:$B$2131)

(作者註:將sheet km下的所有準考證號都轉化為文本,再使用vlookup函數,一切正常!vlookup函數查找區域必須轉化為文本!)

20列間隔3列求和

解答:假設a1至t1為數據(共有20列),在任意單元格中輸入公式:=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))

按ctrl+shift+enter結束即可求出每隔三行之和。

WORD向EXCEL格式轉換時一個單詞或漢字分別填入EXCEL中的一個單元格中

解答:在WORD中利用「替換」功能,把每個字符後面都加上一制表符,(在「查找」框中單擊「高級」按鈕,彈出「高級對話框」,把光標置入「查找」框內,在「特殊字符」中單擊「任意字符」,再把光標置入「替換」框內,在「特殊字符」中單擊「要查找的文字」,再單擊「制表符」,再全部替換)

然後復制或剪切,在EXCEL中黏貼即可。

用字母在單元格裡面填入平方米

解答1:在編輯狀態下,選中「2」,按滑鼠右鍵,選擇「設置單元格格式」,選「上標」

解答2:按[ALT]+數字鍵178[ENTER]

從身份證號碼中提取性別

Q: A1單元格中是15位的身份證號碼,要在B1中顯示性別(這裡忽略15位和18位身份證號碼的判別) B1=if(mod(right(A1,1),2)>0,”male”,”female”)請問這個公式有無問題,我試過沒發現問題。但在某個網站看到作者所用的是如下公式: B1=if(mid(A1,15,1)/2=trunc(mid(A1,15,1)/2),”female”,”male”)

用函數將輸入的月份轉換為這個月所包含的天數

假設A1單元格為月份:

=TEXT((DATE(YEAR(NOW()),A1+1,1)-1),”d”)

或:=DAY(DATE(YEAR(NOW()),A1+1,0))

如何自動填充內容

A1:A20是編號,B1:B20是姓名,C1:C20是性別,當我在A21單元格輸入A1:A20範圍內的任意一個編號時,B21出現對應的姓名,C21出現對應的性別。該如何做,請幫忙。

解答:B21單元格公式「=IF(A21=0,””,VLOOKUP(A21,A1:C20,2,FALSE))」;C21單元格公式「=IF(A21=0,””,VLOOKUP(A21,A1:C20,3,FALSE))」這個公式也適用於A列編號不排序的情況,如果升序的話會更簡單一點。

如何在excel中已有的數值前加零變成六位

比如說 25、369、1569等,操作後變成000025、000369、00156

解答:如果直接輸入的話,可以在數值前面加「’」,如「’002020」;

如果處理現成的數據,或者從別處(比如從A1單元格)鏈接來的數據,可以用公式:

=RIGHT(“00000″&A1,6)

一次刪完Excel裡面多出很多的空白行

解答:1、用分面預覽看看

2、用自動篩選然後刪除

3、用自動篩選,選擇一列用非空白,空白行就看不到了,列印也不會打出來。但是實際上還是在的,不算刪除。或者用自動篩選選擇空白將空白行全顯出來一次刪完也可以。

4、先插入一列,在這一列中輸入自然數序列,然後以任一列排序,排序完後刪除數據後面的空行,再以剛才輸入的一列排序,排序後刪除剛才插入的一列。

表1、表2分別有20個人的基本情況和其中10個人的名字,讓表1的數據自動填充到表2

答:1、用lookup函數即可。要保證20人不重名;

2、假設表1的D列對應表2的E列。E2的公式:=VLOOKUP(B2,Sheet1!B:D,3,FALSE)

使用vlookup函數返回#N/A符號時將此符號用0或空格來代替

答:這樣處理: =IF(ISNA(VLOOKUP(C13,A1:B10,2)),0,VLOOKUP(C13,A1:B10,2))

或:IF(ISERROR(vlookup(a1,e1:g10,2,0)),0,vlookup(a1,e1:g10,2,0))。

通過條件格式將小計和總計的行設為不同的顏色

答:輸入=RIGHT(RC,1)=”計”;設定字體、邊框、圖案;確定。

復制隱藏後的表格到一個新表格中使被隱藏的內容不顯示

答:crtl+g-選可見單位格-復制-黏貼。

如何將一個工作簿中的一個Sheet隱藏

答:1、選「格式」—「工作表」—-「隱藏」

2、使用VBA這樣隱藏後在使用工作表保護。

Alt+F11—-Ctrl+G—-出現立即執行窗口,在此窗口內執行

Sheet1.Visible = xlSheetVeryHidden

這樣隱藏後sheet在格式—工作表—-取消隱藏是看不見的。

薪水條問題

職工薪水構成非常複雜,往往超過10項,因此每月發薪水時要向職工提供一包含薪水各構成部分的項目名稱和具體數值的薪水條。列印薪水條時要求在每個職工的薪水條間有一空行便於彼此裁開。本模板就是用EXCEL函數根據薪水清單生成一便於分割含有薪水細目的薪水條表格。

本薪水簿包含兩張薪水表。第1張薪水表就是薪水清單,稱為”清單”。它第一行為標題行包括職工姓名、各薪水細目。

第2張工作表就是供列印的表,稱為”薪水條”。它應設置為每三行一組,每組第一行為標題,第二為姓名和各項薪水數據,第三行為空白行。就是說整張表被3除餘1的行為標題行,被3除餘2的行為包括職工姓名、各項薪水數據的行,能被3整除的行為為空行。

在某一單元格輸入套用函數”=MOD(ROW(),3)”,它的值就是該單元格所在行被3除的餘數。因此用此函數能判別該行是標題行、數據行還是空行。

在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時應取的值。它可用如下函數來賦值:”INDEX(清單!$A:$G,INT((ROW()+4)/3),COLUMN())”。INDEX()為一查找函數它的格式為:INDEX(reference,row-num,col-num),其中reference為查找的區域,本例中為清單表中的A到G列,即函數中的”清單!$A:$G”,row-num為被查找區域中的行序數即函數中的INT((ROW()+4)/3),col-num為被查找區域中的列序數即函數中的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……,單元格的值。這樣,表的完整的公式應為”=IF(MOD(ROW(),3)=0,””,IF(MOD(ROW(),3)=1,清單!A$1,INDEX(清單!$A:$G,INT((ROW()+4)/3),COLUMN())))”。把此公式輸入A1單元格,然後向下向右填充得到了完整的薪水條表。

為了表格的美觀還應對格式進行設置,一般習慣包括標題、姓名等文字在單元格中要取中,數字要右置,數字小數點位數也應一致,還有根據個人的愛好設置邊框。本表格只需對一至三行的單元格進行設置,然後通過選擇性格式設置完成全表的設置。

本工作簿的特點是1、不對清單表進行操作保持清單工作表的完整,2、全工作表只有一個公式通過填充得到全表十分方便。

例如:我的單位不大不小,有200多號人。最近主管要求把每個員工個人的薪水情況打出來,分發給每位員工。每個員工的薪水條上只能有兩行內容:一行是分解的項目內容,如基本薪水、崗位薪水、總計等等;另一行是對應第一行的具體薪水數額。

可以這樣解決:Sheet2

1.為A1命名為K

2.為A3:A250命名為XX

從A3貼上主索引,數據要連續中間不允許有空格

3.寫公式=VLOOKUP(K,DATA,2,0)

有幾個字段寫幾個,位置隨您高興擺

4.隱藏A欄

**************************************************************************************

Sub 列印()

Application.ScreenUpdating = False ‘螢幕不更新

Dim c As Object ‘宣告c為對象,請準備空間

[xx].Select ‘選取變量範圍

Set c = ActiveCell ‘設定c對象為作用單元格

Do Until IsEmpty(c.Value) ‘作Do循環直到無值時跳出

[k].Value = c.Value

Set c = c.Offset(1, 0) ‘設定c往下進一格再取主索引值

Sheets(“Sheet2”).PrintPreview ‘工作表直接列印改PrintOut

Loop

End Sub

另一回答:我是做人事管理的也遇到過你的問題,我用如下方法解決十分方便,而不用任何代碼.

方法的原理是調整列印機的自定義紙張大小到恰好顯示一個人的薪水條的大小,請按如下:

如:我的excel薪水表將項目內容放在第一、二行,行高為20.1,用a4紙橫向列印

1、在頁面設置中將上、下邊距,頁眉、頁腳均設為零,

在頁面設置–工作表—頂端標題行 中輸入 $1:$2,即將放在

第 一、二行的項目內容設成每頁標題行

列印方向為橫向

2、、在文件—-列印—屬性—紙張—-自定義中將紙張的

寬度=280 (單位:毫米)

長度=2970 (單位:毫米)

3、列印時可選1—200頁,即可列印200人的薪水條,一張a4可打10人

確定後預覽,可調整下邊距至每頁顯示一張薪水條

我的解決辦法:我只用了一個公式: 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) 是這個意思:一個薪水表,有列標題,接下來是薪水記錄。而我在此表基礎上,加一個自動生 成的薪水 條表, mod(row(),3)=0,在此表上用這個表示第三行保留空白行; if(mod(row)(),3)=1,sheet1!a$1,表示是第一行取標題列; index(sheet1!$a:$g,int((row()+4/3),column())這是關鍵的地方:是指它不是第一行,也不三倍數的行,是記錄 行的表示,你想第二行顯示記錄,則2+4/3=2 取薪水 表的第二行記錄;第五行顯示記錄,則5+4/3=3 取薪水 表的第三行記錄;第八行顯示記錄,則8+4/3=4 取薪水 表的第四行記錄;第十一行顯示記錄,則11+4/3=5 取薪水 表的第五行記錄;這個公式你可以根據具體情況變化:尤其是((row()+4/3),中的4這個數字,

定制單元格數字顯示格式

定制單元格數字顯示格式,先選擇要定制的單元格或區域,》單擊滑鼠右鍵》單元格格式》選擇‘數字’選項》選擇‘自定義’》在「類型」中輸入自定義的數字格式。

如何輸入自定義的數字格式:需要先知道自定義格式中那些常用符號的含意,具體可以先不選擇‘自定義’,而選擇其它已有分類觀看‘示例’,以便得知符號的意義。

比如:先選擇‘百分比’然後馬上選擇‘自定義’,會發現‘類型’中出現‘0.00%’,這就是百分比的定義法,把它改成小數位3位的百分比顯示法只要把‘0.00%’改成‘0.000%’就好了,把它改成紅色的百分比顯示法只要把‘0.00%’改成‘[紅色]0.00%’就好了。

關於數據引用的問題

在一個工作簿中,假如A工作表中的單元格E8被B工作表中的某單元格引用,現在由於A工作表中插入了行,原來的E8現在可能是E28,結果造成B工作表引用數據錯誤,請問:如何可以使B工作表中的引用隨著A表的變化也作相應的自動調整?

回答:利用「相對引用」來做到,「相對引用」是Excel中默認的引用方式

例:在工作表Sheet1中C2單元格為5,D2單元格為6,在工作表Sheet2中C3單元格中輸入「=Sheet1!C2+Sheet1!D2」,如果把

C2單元格剪切到C3,那麼在工作表Sheet2中C3單元格中的公式就自動變為「=Sheet1!C3+Sheet1!D2」