EXCEL問題大全(2)

  網易博客| 找朋友| 圈子| 拍拍| 搜尋

註冊| 登錄

我的博客

星辰閃爍,我心飛翔

首頁

日志

相冊

音樂

收藏

好友

關於我

日志

EXCEL問題大全(1)EXCEL問題大全(3)

字號: 大大 中中 小

EXCEL問題大全(2)

EXCEL裡取數值的一部分用什麼函數

打比方說有一個數字123456,我只想取它的後三位,也就是說只需要456,應該用什麼函數。

解答:假設A1的值為123456, 用=RIGHT(a1,3)可以取其後三位./?你如果想取其中的幾位,不妨試試mid函數。

用什麼函數可以插入字符

00020304T0239 0400020304T0239 /00020404T0211 0400020404T0211 /00020604T0199 0400020604T0199 /00020704T0216 0400020704T0216 /00021304T0241 0400021304T0241 /00021404T0222 0400021404T0222 /00021504T0222 0400021504T0222 /00021704T0139 0400021704T0139 /就像上表一樣,怎樣用第一列的數據形成第二列的數據,即在特定位置加上幾個相同字符串?

解答:?b1=”04″& a1

問:哪如果倒過來呢?

答:用公式A1=MID(B1,3,13)或A1=SUBSTITUTE(B1,”04″,””,1)

將文件保存為以某一單元格中的值為文件名的宏怎麼寫?

解答:假設你要以Sheet1的A1單元格中的值為文件名保存,則應用命令:

ActiveWorkbook.SaveCopyAs Str(Range(“Sheet1!A1”)) + “.xls”

A1是文本串的話,使用這條命令: ActiveWorkbook.SaveAs Range(“A1”) & “.xls” 。當然,如果你的A1是路徑,F1是文件名的話,可以這樣寫: ActiveWorkbook.SaveAs Range(“A1”) & Range(“F1”) & “.xls”

求餘數的函數

比如:A1=28,A2=(A1÷6)的餘數=4,請問這個公式怎麼寫??解答:=MOD(28,6)

評委評分

解答:用兩個函數可解決:rank(排高低)average(求平均)。也可以用:{=(SUM($B$1:$B$10)-SUM(LARGE($B$1:$B$10,ROW($A$1:$A$2)))-SUM(SMALL($B$1:$B$10,ROW($A$1:$A$2))))/(COUNT($B$1:$B$10)-4)}(可以解決有多個最高和最低分的問題)。

數據校對

已知:A2=SUM(A3:A20),B2=SUN(B3:B20),C2=SUM(C3:C20),D2=SUM(d3:d20),E2=SUM(E3:E20)、又知A2=B2+C2=D2+E2 。需要解決的問題:當B2+C2或者D2+E2不等於A2時,系統自動提示錯誤,請各位高手給予指點,是否一定要通過宏才可以解決,有沒有更簡單的辦法?

解答:=IF(AND(B2+C2=SUM(A3:A20),D2+E2=SUM(A3:A20)),SUM(A3:A20),” : (“)

如何在一個單元格中自動輸入在另外一個單元格中輸入的文字

?解答:a2中輸入a1的文字,則a2=indirect(“a1”)

在表格中查找我需要的東西並把該行所有的數據反映到另外的表格中去

解答:比如找表A中B列數值等於5的數。在表B中的A1單元輸入:=VLOOKUP(5,A!B1:B4,1)

EXCEL表裡建立唯一索引在該列輸入重復的數值後自動提示輸入錯誤

解答:1、選定你要限制輸入的列(假設是A2:A20),選菜單的「數據」-「有效性」;

2、在許可條件中選定「自定義」,在公式一攔中,輸入你要求的限制, 例如:「=COUNTIF($A$2:$A$20,A2)=1 」。

3、你還可以在「輸入信息」和「出錯信息」輸入一些提示信息。

不過,你一定要注意!這個功能只能在你從鍵盤上鍵入數據時有效!拷貝和黏貼數據的操作是無效的。

發票小寫金額填充

我輸入123456.52它自動給拆成¥1 2 3 4 5 6 5 2 的形式並且隨我輸入的長度改變而改變?

?解答:我所知函數不多,我是這樣做的,如有更方便的方法,請指點

例如:

在A1輸入小寫金額,則:

千萬:B1=IF(A1>=10000000,MID(RIGHTB(A1*100,10),1,1),IF(A1>=1000000,”¥”,0))

百萬:C1=IF(A1>=1000000,MID(RIGHTB(A1*100,9),1,1),IF(A1>=100000,”¥”,0))

十萬:D1=IF(A1>=100000,MID(RIGHTB(A1*100,8),1,1),IF(A1>=10000,”¥”,0))

萬:E1=IF(A1>=10000,MID(RIGHTB(A1*100,7),1,1),IF(A1>=1000,”¥”,0))

千:F1=IF(A1>=1000,MID(RIGHTB(A1*100,6),1,1),IF(A1>=100,”¥”,0))

百:G1=IF(A1>=100,MID(RIGHTB(A1*100,5),1,1),IF(A1>=10,”¥”,0))

十:H1=IF(A1>=10,MID(RIGHTB(A1*100,4),1,1),IF(A1>=1,”¥”,0))

元:I1=IF(A1>=1,MID(RIGHTB(A1*100,3),1,1),IF(A1>=0.1,”¥”,0))

角:J1=IF(A1>=0.1,MID(RIGHTB(A1*100,2),1,1),IF(A1>=0.01,”¥”,0))

分:K1=IF(A1>=0.01,RIGHTB(A1*100,1),0)

(編者註:公式中最後一個0應改為””)

排列問題

已知1,2,3,4共4個數字符號構成的4位數的全排列有256個,如:1234,2341,3245等等,現在我需要將這256個數字全部列出,如果用手寫筆算的話既麻煩又容易出錯,不知可否用Excel來解決這個問題?

解答:在單元格輸入公式「=1111+1000*INT((ROW()-1)/64)+100*MOD(INT((ROW()-1)/16),4)+10*MOD(INT((ROW()-1)/4),4)+MOD(ROW()-1,4)」拖放到A256。

??在條件語句中如何做到符合某個時期的條件的記錄進行統計

比如有1-12月份的記錄單,需要做到對每個月裡些數據的統計匯總/(格式如何?)

?解答:以下公式,A列為日期列,B列為數據,要求計算1月份的累計:

=SUM(IF(MONTH(A:A)=1,B:B,0))

此為數組公式,在輸完公式後,不要ENTER,而要CTRL+SHIFT+ENTER.

行改列(不是轉置問題)

1. 原內容為行(每行5列內容共幾千行):

列1 列2 列3 列4 列5

行1 1 2 3 4 5 /行2 1 2 3 4 5 /行3 1 2 3 4 5 /以下類同… /…

2. 改為行排列(每12行5列內容共12組,排為一行)

列1 列2 列3 列4 列5 列6 列7 列8 列9 列10 ……(60列)

原1-12行 1 2 3 4 5 1 2 3 4 5 ……(60列) /原13-24行 1 2 3 4 5 1 2 3 4 5 ……(60列) /原25-36行 1 2 3 4 5 1 2 3 4 5 ……(60列) /以下類推 …

本人不會編寫宏,讓您見笑曾費勁手工移動錄制了一個取2400行(每行5列內容)改列的宏。因原有工作表行不斷增加,經常需要行改列作其它用途,錄制的宏不能滿足需要。煩請大家幫助寫一自動循環取12行5列內容排列為一行的宏。

規律:

1. 1-12行1-5列內容排在第1行(第一行1-5列、第二行1-5列、類推,完成為60列)

2. 13-24行1-5列內容排在第2行(第13行1-5列、第14行1-5列、類推)

3. 25-36行1-5列內容排在第3行(第25行1-5列、第26行1-5列、類推)

4. 以下類推 …

解答:Sub 轉換()

Dim numcol As Integer

Dim numrow As Long

Dim i As Long

Dim x As Integer

Dim numperrow As Integer

numperrow = InputBox(“請輸入每行要填的數據行的數目:”)

Range(“數據”).Select

numrow = Selection.Rows.Count ‘數據區的行數

numcol = Selection.Columns.Count ‘數據區的列數

x = numperrow * numcol

Range(“a1”).Select

For i = 1 To numrow ‘以數據的每一行為單位進行剪切

Range(“數據”).Rows(i).Cut

ActiveSheet.Paste

Selection.Offset(, numcol).Select

If (i Mod numperrow) Then ‘判斷是否要換行

Else: Selection.Offset(1, -x).Select

End If

Next i

End Sub

‘本程序需要把要變換的數據命名為”數據”(數據區的列數可以任意,不一定是5)方法:選中該區域,在名稱框內輸入”數據”即可,然後按程序要求輸入每行要填的數據行的數目(如本例中的12)

??如何給自動篩選中的自定義指定快捷鍵?

解答:Sub 自動篩選()

On Error GoTo xx

Selection.AutoFilter = True

xx:

End Sub

匯總問題

本人有以下一個問題需要解決,請諸位大蝦指點迷津。

如下表:

A   B     C

1 50  採購  (採購匯總) / 2 60  工程  (工程匯總) / 3 80  薪水  (薪水匯總) / 4 100  稅   (稅匯總) / 5 70  採購   / 6 50  薪水   / 7 60  工程 /. . . /請問:C1、C2、C3、C4中的公式如何設置?

?解答:方法1:如下表:

A   B     C

1 50  採購  =countif(a1:a7,”採購”) / 2 60  工程  =countif(a1:a7,”工程”) / 3 80  薪水  =countif(a1:a7,”薪水”) / 4 100  稅   =countif(a1:a7,”稅”) / 5 70  採購   / 6 50  薪水   / 7 60  工程 / . . .

方法2:用如下方法才對:

A   B     C

1 50  採購  =SUMIF(B1:B7,B1,A1:A7)

2 60  工程  =SUMIF(B1:B7,B2,A1:A7) / 3 80  薪水  =SUMIF(B1:B7,B3,A1:A7) / 4 100  稅   =SUMIF(B1:B7,B4,A1:A7) / 5 70  採購   / 6 50  薪水   / 7 60  工程

方法3:由於B欄屬於數據表的一部分,可能會隨時改變,故此最好把採購等項目名稱抄到另外一個地方(C欄)。

A   B     C    D

1 50  採購  採購  =SUMIF(B$1:B$7,C1,A$1:A$7) / 2 60  工程  工程  =SUMIF(B$1:B$7,C2,A$1:A$7) / 3 80  薪水  薪水  =SUMIF(B$1:B$7,C3,A$1:A$7) / 4 100  稅   稅   =SUMIF(B$1:B$7,C4,A$1:A$7) / 5 70  採購   / 6 50  薪水   / 7 60  工程 /還有,記著要適當地使用絕對參照地址 (Absolute referencing)。

方法4:

A   B     C    D

1 50  採購  採購  =SUMIF(B:B,C1,A:A) / 2 60  工程  工程  =SUMIF(B:B,C2,A:A) / 3 80  薪水  薪水  =SUMIF(B:B,C3,A:A) / 4 100  稅   稅   =SUMIF(B:B,C4,A1:A) / 5 70  採購   / 6 50  薪水   / 7 60  工

統計包含某個字符的個數

我想編的公式是: a/[84 – (b×4)] 。其中a是一個數值,小於或等於84;b是包含字符C的單元格的個數;C是一個符號。這個公式的關鍵是要統計出包含字符C的單元格的個數。

?解答:方法1:=a/(84-countif(b,”=c”)*4)

問題:我試了一下,不能運行,我想是因為沒有指定出現「c」的單元格的範圍。比如說「c」在D2—D30中隨機出現,在上述公式中要先統計出出現「c」的單元格的個數。這個公式如何做?

解答: =a/(84-COUNTIF(D3:D30,”c”)*4)

如何用if函數判斷,如果a1單元格大於0,b1單元格為0是錯誤,為非0是正確

?解答:1:if(and(a<=0,b=0),”錯誤”,”正確”)

2:=if(a1>0,if(b1=0,”錯誤”,”正確”),”條件一不滿足”)

我改進以下一個驗證先進先出法公式

=IF(B3<C1,IF(SUM(B3:B4)<C1,IF(SUM(B3:B5)<C1,IF(SUM(B3:B6)<B1,IF(SUM(B3:B7)<C1,”out of range”,(B3*C3+B4*C4+B5*C5+B6*C6+(C1-SUM(B3:B6))*C7)/C1),(B3*C3+B4*C4+B5*C5+(C1-SUM(B3:B5))*C6)/C1),(B3*C3+B4*C4+(C1-SUM(B3:B4))*C5)/C1),(B3*C3+(C1-B3)*C4)/C1),C4)

B3至B7為輸入數量,C3至C7為輸入單價,C1為輸入結餘數量,E1為輸出平均單價,即上面公式

?解答:公式太複雜,而且受限制。

把A列利用起來,a3=SUM(B3:$B$7),拖至7行(若為X行,則=SUM(B3:$B$X,下拖),A列數據為先進先出的累計數量(待Match的結存數量),=MATCH(C1,A3:A7,-1)找到結存批次。然後計算結存平均單價及發出平均單價。?

有無簡結一點的公式求如:a1*b1+a2*b2+b3*b3…的和

?解答:在B4中輸入公式”=SUM(A1:A3*B1:B3)”,按CTRL+SHIFT+ENTER結束.

?或:=SUMPRODUCT(A1:A10,B1:B10)

?工作中須將表格中大量的日期同時轉換為中英文的星期幾

?解答:轉成英文:

=TEXT(WEEKDAY(A1),”dddd”)

轉成中文:

=TEXT(WEEKDAY(A1),”aaaa”)

我想查詢表格中的某一列編號中有221這一編號時,引用其同行某列的數值

我現用 VLOOKUP(221,A1:E33,5,0)這一公式引用其同行第5列的數值時可以使用,但當查詢到沒有 221 時,想使其返回值為「0」不知如何處理。

解答:=if(iserror(vlookup())=true,0,vlookup())

如何給自定義函數寫上幫助信信息和參數說明

解答:目前自定義函數是不能做到完全象內置函數一樣的。但是可以給它添加一段說明。

打開帶有自定義函數的工作簿-〉按快捷鍵 Alt+F8—〉在光標處輸入函數名字—〉單擊「選項」—〉輸入說明文字。

如何在EXCEL中統計一個數在哪一行出現

解答:{=IF(ISERROR(SMALL(IF(R1C1:R16C1=R1C2,ROW(R1C1:R16C1),””),ROW()-1)),””,SMALL(IF(R1C1:R16C1=R1C2,ROW(R1C1:R16C1),””),ROW()-1))}

SHEET51的A1做公式加總SHEET1:SHEET50的A1怎麼做啊

解答:=SUM(Sheet1:Sheet50!A1)?

工作表間的數據輸入

我想在sheet1中輸入數據,例如我先在A1中輸入1,確定是輸入第一個單位的數據,然後從A2開始是第一個單位的數據。輸入完成後,在SHEET2中的第一個單位數據就顯示出來了,我的問題是,我想用SHEET1表再輸入第二個單位的數據,但是這時SHEET2中第一個單位的數據沒有了,顯示是第二個單位的數據,如何才能保存第一個單位的數據,又能用SHEET1再輸入第二個、第三個單位的數據?

解答:=IF(戶匯總!$B$3<>1,””,戶匯總!B5)

分享(0)

評論(0)

分類:默認分類

132次閱讀

2006-11-05 09:08

我要登錄->

參與2008精彩獵人,每周好禮等你拿!點擊查看>>

公司簡介- 聯繫方法- 招聘信息- 客戶服務- 相關法律- 用戶反饋- 舉報不良信息-

網易公司版權所有 ©1997-2008

JSWindow

EXCEL問題大全(2)
  正在加載中…